浅谈mysql优化及一次mysql慢查询sql优化过程

一、前言：

说到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所采取的具体的优化措施也就不能一概而论！
但是优化的大致思路是相同的！