在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化,本章将详细介绍在 MySQL 中优化 SQL 语句的方法。
定位慢查询
通过慢查询可以定位到已执行完的sql中哪些sql的执行效率较低,但请注意,他是执行完之后,才有结果
开启慢查询日志
查看MySQL数据库是否开了慢查询日志和慢查询日志文件的存储位置命令
SHOW VARIABLES LIKE 'slow_query_log%'
通过以下命令开启慢查询日志
SET GLOBAL slow_query_log = ON; // 打开慢查询日志 SET GLOBAL slow_query_log_file= 'slow.log'; //指定记录下来的sql地址 SET GLOBAL log_queries_not_using_indexes = ON;//记录没有使用索引的sql SET long_query_time= 10; //设置多长时间算慢 SHOW VARIABLES LIKE 'slow_query_log_file'
查看慢查询日志
建议用mysqldumpslow查看,mysqldumpslow是MySQL提供慢查询日志分析的工具
数据展示
time:日志记录的时间
User@Host:执行的用户及主机
Query_time:执行的时间
Lock_time:锁表时间
Rows_sent:发送给请求方的记录数,结果数量
Rows_examined:语句扫描的记录条数
SET timestamp:语句执行的时间点
select....:执行的具体的SQL语句
可以通过这个工具的参数获得访问记录最多,锁定时间最长的等等数据
具体分析
SQL语句是否使用了索引,可根据SQL语句执行过程中有没有用到表的索引,可通过 explain命令分析查看检查结果的key值是否为null
在使用索引时不要只关注是否起作用,不只要创建索引,还要考虑过滤性,当过滤性好了执行速度才会提高
案例
表:student 字段:id,name,sex,age 造数据:insert into student (name,sex,age) select name,sex,age from student; SQL案例:select * from student where age=18 and name like '张%';(全表扫 描)
优化一
alter table student add index(name); //追加name索引
优化二
alter table student add index(age,name); //追加age,name索引
优化三
可以看到,index condition pushdown 优化的效果还是很不错的。再进一步优化,我们可以把名 字的第一个字和年龄做一个联合索引,这里可以使用 MySQL 5.7 引入的虚拟列来实现。 //为user表添加first_name虚拟列,以及联合索引(first_name,age) alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age); explain select * from student where first_name='张' and age=18;
慢查询原因总结
全表扫描:explain分析type属性all
全索引扫描:explain分析type属性index
索引过滤性不好:靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销:尽量少用select *,使用覆盖索引