MySQL调优 - 汇总

汇总下平时开发中对mysql的调优过程。

（一）mysql server的调优

首先需要知道哪些配置可以调整，通过 show variables可以查看配置项。

可以通过set方式更改配置项：

方式一：（例子）set long_query_time=1;

但仅对当前session生效，新连接更改无作用，如下

方式二：（例子）set global long_query_time=1;

之后新建连接的long_query_time更新为1s。

方式三：配置到my.cnf文件中再重启mysql服务（永久生效）

[mysqld]下增加或者更新参数，比如开启慢查询日志功能：

slow_query_log=1

slow_query_log_file=/var/lib/mysql/localhost-slow.log

long_query_time=3

log_output=FILE

回到调优，mysql server的配置不专业，也就调整下sort_buffer_size（用于排序）和 join_buffer_size（用于join，bnlj算法）

（二）sql调优

针对sql语句的调优，涉及比较杂，罗列如下：

1. "select * " 尽量换成select 必要字段

2. 批量insert/update并分段commit

3. delete all可以用truncate替换

4. join表不超过3个且用小表驱动大表

5. 频繁查询字段建立索引

6. 时间字段类型，若不考虑时区使用datetime否则timestamp

7. 未完待续

（三）索引调优

1. 先看有没有创建索引(show create table xxx)

2. 再看索引有没有生效(explain select)

若想看索引的选择过程，可以开启optimizer_trace

 

（四）慢查询调优

1. 开启慢查询日志

set global slow_query_log=1;

2. 设置慢查询阈值

set global long_query_time=2;

3. 分析慢查询日志

可以使用mysqldumpslow（windows环境需要安装perl，再运行perl mysqldumpslow ...）

比如：

  1. 得到返回记录集最多的前10个SQL：mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log  
  2. 得到访问次数最多的前10个SQL：mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log  
  3. 得到按照时间排序的前10条里面含有左连接的查询语句：mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log  
  另外，建议在使用这些命令时结合 | more 使用，否则有可能出现爆屏情况：mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more  

或者使用pt工具，参考pt-query-digest。