一、前言:
说到sql优化其实其涉及范围比较大,如可以从:数据类型的指定、存储引擎的选择、sql语句如何编写、相关业务表如何设计等各个方面进行综合考量设计。本篇文章中不细谈这些内容,重点是讲解慢sql查询语句的大致优化思路。
二、优化过程:
大致思路:定位慢sql -> explain执行计划分析sql -> 根据sql分析结果采取相应的优化措施
1. 定位慢查询:
1.1. 根据页面的加载速度直接定位。
1.2. 通过mysql慢日志监控来定位。
参考:开启MYSQL慢查询日志,监控有效率问题的SQL
具体操作如下:
# 1.
show variables like '%quer%';
# 1)主要关注以下3个变量:show_query_log\show_query_log_file\long_query_time
# 3. 设置步骤1.中的变量:
set global slow_query_log = on; # 慢查询监控开启
set global long_query_time = 1; # 慢查询阈值 (set global long_query_time 命令执行完需要重新连接库的会话!)
# 4.
show status like '%slow_queries%'; # 查看慢查询sql个数
2. 通过explain执行计划分析sql(看懂explain)
主要关注explain返回信息中的:type、key、extra等关键几列:
执行本文中的慢sql的explain结果:(由于主要目的是讲解大致的优化流程,所以本次分析的慢sql没有贴出来!)
+--+------------------+-----+-------------------+------+-------------+-------+-------+----------------------------+----+--------+--------------------------------------------------+
|id|select_type |table|partitions |type |possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+------------------+-----+-------------------+------+-------------+-------+-------+----------------------------+----+--------+--------------------------------------------------+
|1 |PRIMARY |cwv2 |p20210301,p20210302|ALL |idx_day_id |NULL |NULL |NULL |40 |100 |Using where |
|1 |PRIMARY |cwv3 |NULL |ALL |NULL |NULL |NULL |NULL |1235|100 |Using where; Using join buffer (Block Nested Loop)|
|1 |PRIMARY |au |NULL |eq_ref|PRIMARY |PRIMARY|152 |promanageDB.cwv3.user_id |1 |100 |NULL |
|1 |PRIMARY |dp |NULL |eq_ref|PRIMARY |PRIMARY|152 |promanageDB.au.department_id|1 |100 |NULL |
|1 |PRIMARY |p |NULL |eq_ref|PRIMARY |PRIMARY|152 |promanageDB.cwv3.project_id |1 |100 |NULL |
|1 |PRIMARY |ppl |NULL |ALL |NULL |NULL |NULL |NULL |132 |100 |Using where; Using join buffer (Block Nested Loop)|
|1 |PRIMARY |ppm |NULL |eq_ref|PRIMARY |PRIMARY|152 |promanageDB.ppl.phase_id |1 |100 |NULL |
|3 |DEPENDENT SUBQUERY|t |NULL |ALL |NULL |NULL |NULL |NULL |132 |10 |Using where; Using filesort |
+--+------------------+-----+-------------------+------+-------------+-------+-------+----------------------------+----+--------+--------------------------------------------------+
如上面结果中的如下几条信息为本次优化的切入点:
type: ALL
Extra:Using where; Using join buffer (Block Nested Loop)
Extra:Using where; Using filesort
3. 根据步骤2分析结果采取相应的优化措施:
3.1. 一般采取如下措施:
- 修改sql,即优化sql语句,例如:某些情况下使用exists替换in效率更快等,可以结合具体sql自行查询相关优化方案。
- 该走索引的字段尽量走索引。
- 优化表结构,例如:
某个关键字段可否建在同一张表,而避免同另一张表关联查询。
当然这个例子需要结合具体业务场景以及 数据库表设计的范式等各方面考量的事情了。
等等。
3.2. 具体优化过程中遇到的问题:
3.2.1. where条件中的列有索引但实际查询中未走索引?
非也,请见:mysql当查询结果集中的数据比例超过全表数据一定比例时索引失效
3.2.2. 关于explain信息中的Extra:Using where; Using join buffer (Block Nested Loop)的理解!
mysql nest loop伪代码_学习Mysql的join算法:Index Nested-Loop Join和Block Nested-Loop Join
三、结语:
不同业务场景中的不同慢查询sql有不同的写法,所以针对每种sql所采取的具体的优化措施也就不能一概而论!
但是优化的大致思路是相同的!