MySQL性能优化指南-优快云博客

本文链接：https://blog.youkuaiyun.com/wrs120/article/details/80716574

1.优化的流程

但是通常我们不会是上来就Explain，通常都用于以下一个流程：

检测到mysql有问题
开启慢查询日志，设置阙值，比如超过2秒的就是慢SQL，并将其抓取出来 .explain + 慢sql，进行分析
show profile查询sql在mysql服务器里面执行细节和声明周期情况
SQL数据库服务器参数调优

下面对每步分别做介绍

2.慢查询日志的设置

mysql默认慢查询日志是关闭状态：

查看状态：SHOW VARIABLES LIKE '%slow_query_log%';OFF表示慢查询日志是禁用状态

- 临时开启：`set global slow_query_log=1;`（只对当前数据库生效，mysql重启失效） - 永久开启：修改my.cnf文件，[mysqld]节点下增加或修改参数：

show_query_log=1
slow_query_log_file=var/lib/mysql/xxx-slow.log
(指定慢查询日志文件的存放路径，系统默认给一个缺省的文件hostname-slow.log)

查看阙值：即多少秒算慢 SHOW VARIABLES LIKE'long_query_time';
设置阙值

set global long_query_time=3;

或在my.cnf的[mysqld]节点下配置：

long_query_time=2;
log_output=FILE

如果运行时间真好等于long_query_time，并不会被记录下来
设置后可能看不出变化，可以重新连接或打开一个会话，或使用 SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';

3.优化的原则

3.1 永远小表驱动大表

现有2张表A和B:

若A表数据集大于B表数据集，用in优于exists：

select * from A where id in (select id from B)
等价于：
select id from B
select * from A where A.id = B.id

若A表数据集小于B表数据集，用exists优于in：

select * from A where exists （select 1 from B where B.id = A.id）
等价于
select * from A
select * from B where B.id = A.id

select …from mytable exists(subquery)

exists：将主查询的数据，放到子查询中做条件验证，根据验证结果（true/false）来决定主查询的数据结果是否得以保留
exists(subquery)只返回TRUE或FALSE,因此子查询中的select * 也可是select 1 或select
‘任意值’，官方说是实际执行时会忽略select清单，因此没有区别
exists子查询的实际执行过程可能经过了优化而不是我们理解的逐条对比

3.2 order by 关键字优化

order by子句，尽量使用index方式排序，避免使用FileSort排序
- mysql支持两种排序FileSort和index，Index,Index效率高，它指mysql扫描索引本身完成排序，FileSort方式效率较低
- Order By使用Index排序的情况：@1.oreder by使用索引最左前列；@2.使用where子句与order by子句条件组合满足索引最左前列
- 但有一点，当有两个或多个排序字段，排序规则不一致时，会使用using FileSort
尽可能在索引上完成排序操作，遵循索引最佳左前缀
若order by不在索引列上，FileSort有两种算法：双路排序（mysql4.1之前是使用双路排序，即连词扫描磁盘，最终得到数据，读取行指针和orderby列，在buffer进行排序，再从磁盘取其它字段。对磁盘进行了2次I\O，耗时）和单路排序（从磁盘读取查询需要的列，按照order by列在buffer对它们进行排序，然后扫描排序后的列进行输出，这样效率更高，避免了第二次读数据。这样把随机I/O变成了顺序I/O，但它使用了更多空间，因为把每一行都保存在了内存中，所以存在可能取出的数据总大小超出sort_buffer的容量，导致每次只能去sort_buffer容量大小的数据进行排序[创建tmp文件，多路合并]，排完再取sort_buffer容量大小，再排…从而造成所次I/O）,未解决单路出现的问题，可采用以下解决方案：
order by时select * 是大忌,即减少不必要返回字段
提高sort_buffer_size（此参数不能决定让mysql使用单路排序，只是减少mysql排序分段的次数，较少I/O）
提高max_length_for_sort_data(当所有返回字段的最大长度小于这个参数值时，mysql就会选择单路排序，提高这个参数，会增加使用改进算法的概率，但如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率)

3.3 group by 关键字优化

group by 实质是先排序后分组，遵循索引建的最佳左前缀
当无法使用索引列，增大sort_buffer_size和max_length_for_sort_data参数设置
where高于having，能写在where限定的条件就不要去having限定

4. mysqldumpslow工具

在生产环境下，日志文件是很大的，如果手工分析日志，查找，分析mysql，不太现实，MySQL提供了日志分析工具mysqldumpslow：

常用工具如下：

5. show profile

5.1 是什么
是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可用于SQL的调优测量
5.2 查看mysql是否支持show profile
5.3 查看状态Show variables like ‘profiling’;

默认是关闭状态，并保持最近15次运行结果

5.4 开启 set profiling=on;
5.5 查看执行的sql结果:show profiles;
这里写图片描述

5.6 诊断sql：show profile cpu,block io for query 查询id号（show profiles得到的Query_ID）
这里写图片描述
其中参数即要查看的内容可以是：

5.7 什么情况表示该条sql语句有问题？
当stauts出现以下语句时，表明有问题：

converting HEAP to MyISAM :查询结果过大，内存都不够用了往磁盘上搬了
creating tmp table:创建临时表，拷贝数据到临时表，然后在删除
copying to tmp table on disk:把内存中临时表复制到磁盘，这时就非常危险了！！！
locked：加锁了

6.全局查询日志

只在测试环境下开启，一定不能在生产环境下开启

使用配置文件开启：修改my.cnf文件：

# 开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE

使用命令开启：

# 开启
set general_log=1;
# 输出形式
set global log_output='TABLE';

此后编写的sql语句都将记录到mysql库里的general_log表

查看日志文件：select * from mysql.general_log;

当发现mysql某个时间段执行出现问题时，可以通过全局查询日志查看，因为它记录了时间，这样就可以找到出现问题时间段执行的sql语句，而show prifiles是查询sql语句执行的时间，侧重有没有问题的排查