如何优化sql语句
整体思路:
如何查看慢日志
查看慢日志设置
show variables like '%quer%'
其中:
show_query_log
:表示是否开启慢日志show_query_log_file
:慢日志存储的路径long_query_time
:超过多少s,才会将sql存入慢日志
可以直接进行设置,但重启数据库服务之后会复原
set global show_query_log = on;
set global long_query_time = 1; //需要重新连接客户端
如果想要永久保存设置,则需要在my.ini
文件中进行设置。
查看慢sql的条数
show status like '%show_queries%'
使用Explain工具分析慢sql
如,需要分析以下语句
select name from person_info_large order by name desc
在语句前在explain
关键字,此时sql语句不执行,只进行分析
explain select name from person_info_large order by name desc
Explain关键字段
type
:mysql找到数据行的方式,如果为index
或all
则进行了全表扫描
extra
key
:使用的是哪个键的索引
优化慢sql
由于这条语句没有走索引,查询时间达到了6s,所以我们进行添加索引或改变查询字段(字段有索引)的方式来优化慢sql
select name from person_info_large order by name desc
改变方式一:使用拥有索引的name字段,查询时间从6s变成了4s
select account from person_info_large order by account desc
改变方式二:给name字段添加索引,时间从6s变成了4s
alter table person_info_large add index idx_name(name)
补充
这句sql语句走的是哪个索引?
explain select count(id) from person_info_large
发现走的是account
的唯一键索引
原因:查询优化器为了排除数据行,更快找到跟条件匹配的数据行,根据其分析和判断决定走哪个索引,没有选主键索引的原因是因为B+树的密集索引将其他列的数据也放入了叶子节点,数据放在了一起,效率比稀疏索引低,因为稀疏索引只存放了键位和主键值,能方便进行count计算,节约性能,所以查询优化器选择了account索引。
如何强制使用主键索引?
explain select count(id) from person_info_large force index(primary)
注意:有时候查询优化器选择的索引并不是最优的,需要自己酌情考虑
最左匹配原则的成因
联合索引才有最左匹配原则
成因:mysql的联合索引会首先对第一个索引字段进行排序,再对第二个字段进行排序。所以第一个字段有序,而第二个字段就变成无序了。如果对第二个字段进行条件判断,就无法使用索引。
如下图,如果建立了col3,col2
的联合索引,就会以col3
的键作为索引形成B+树,此时使用col2作为条件判断就无法使用到索引