MySQL优化(7):其他注意事项
DDL执行
线上服务器执行DDL,更新表结构,需要谨慎,结构更改会导致全表被独占锁定(新版本有改善)
避免这种情况,使用COPY策略,而不是直接执行ALTER TABLE语句
思路:创建一个新表,满足新要求,将旧表数据逐条导入新表,同时表上可以执行其他任务,导入的过程其他任务都记录在日志,导入完成后根据日志更新新表,最后新表替换旧表(Java等代码中实现)
数据导入语句
在恢复数据时,需要大量的数据导入
思路:导入时,先禁用索引和约束;导入完毕后,再开启索引和约束,一次性创建索引
有可能使用到的语句:禁用索引和开启索引
ALTERTABLE[table-name] DISABLE KEYS;ALTERTABLE[table-name] ENABLE KEYS;
对于InnoDB存储引擎,可以将多条SQL放入一个事务中完成
可以使用prepare预编译的方案执行导入操作,减少相同结构的SQL编译次数
大容量页码LIMIT
尽量不要使用大的OFFSET,比如LIMIT 10000,10
思路:尽量使用条件过滤,完成数据筛选,而不是通过OFFSET跳过已经查询到的数据
SELECT *
不要使用SELECT *,而是使用具体的字段;这一点对性能影响不是很大,不过这是规范
ORDER BY RAND()不要用
这句话是随机排序,如果业务要随机选择尽量在服务器代码中实现,随机生成主键进行检索
单表和多表查询
尽量使用单表查询来代替多表查询,原因有以下几点:
(1)单表计算的压力在java代码,多表计算的压力在数据库
(2)多表查询会一个一个表依次执行,最后再合并结果
(3)多表查询会增加锁定时间,降低程序并发性能
COUNT(*)
MYISAM会自动存储COUNT(*)的值,INNODB没有内部计数器,执行这句话相对来说较慢
解决思路:额外创建一张表,把COUNT(*)的值记录下来
另外:
COUNT(ID)表示的是:统计ID字段不为NULL的数量
COUNT(1)表示的是:统计记录数量,和COUNT(*)几乎一样
LIMIT 1
如果你确定查到的结果只有一个,那么加入LIMIT 1会提高性能
慢查询日志
用于记录执行时间超过某个临界值的SQL的日志,用户快速定位慢查询
开启该日志:
查询是否开启
SHOW VARIABLES LIKE"slow_query_log";
开启
SET GLOBAL slow_query_log=On;
开启后会在数据目录创建slow.log文件
查看临界时间,默认是十秒,很长
SHOW VARIABLES LIKE"long_query_time";
设置临界时间
SET long_query_time =0.5;
一旦超过了0.5秒,就会被自动记录
PROFILE信息统计
详细记录SQL语句执行时间的工具
开启PROFILE:
SET profiling=On;
使用命令查看结果
SHOW PROFILES;
具体某条的详情
SHOW PROFILE FOR QUERY [query_id]
典型的服务器配置
(1)MAX_CONNECTIONS:当前MySQL允许最大连接数151个
(2)table_open_cache:表句柄缓存2000
(3)KEY_BUFFER_SIZE:索引缓冲大小8388608
(4)innodb_buffer_pool_size:INNODB存储引擎缓存池大小,比较重要,尽可能地设置更大
(5)innodb_file_per_table:INNODB表的独立表文件,ON就表示一个表对应一个表文件,否则所有INNODB表文件存到一起
以上的配置全部取决于实际的运行环境
mysqlslap压力测试工具
这是一个mysql自带的工具,是一个EXE,直接运行即可,具体的百度,这是DBA的工作,不是程序员的工作
以上是 MySQL优化(7):其他注意事项 的全部内容, 来源链接: utcz.com/z/532606.html