MYSQL系列SQL查询

前面已经介绍了MYSQL的索引和锁机制，本文主要讲述MYSQL查询/更新语句该怎么写

建索引的原则

前面已经有章节讲述索引优化使用,本章节略有重复，主要讲述建立索引原则

最左前缀匹配

指在使用复合索引（即由多个列组成的索引）进行查询时，只有按照索引中最左边的列开始依次匹配，才能充分利用索引的优势。
比如有一个复合索引（a, b, c），那么在查询时，只有按照以下方式进行查询，才能充分利用该索引：

又比如下面查询是用不到索引

注意不要重复建立索引，有了复合索引（a, b, c），就不用再建立(a)、(a,b)索引

覆盖索引，避免回表

参考 覆盖索引

选择区分度高列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0

=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

编写SQL技巧

基本技巧

查询时禁止使用*

使用select * 除了写起来较为简单，其他方面都不友好

• 如果新加了字段，代码不改动可能会报错，不利于灰度升级
• 返回不必要的字段，增加了网络开销和内存占用
• 分析成本变高，分析器解析时需要查询表上*对应哪些字段

不建议使用like左模糊和全模糊查询

%xxx、%xxx% 会导致索引失效，2c接口尽量不要使用此查询条件
管理台等低频调用可以，否则就需要采用其他机制来查询，会增加额外硬件成本

查询时尽量不要对字段做空值判断

select * from xxx where yyy is null;
select * from xxx where yyy not is null;

当出现基于字段做空值判断的情况时，会导致索引失效，因为判断null的情况不会走索引，因此切记要避免这样的情况
一般在设计字段结构的时候，请使用not null来定义字段，同时如果想为空的字段，可以设计一个0、""这类空字符代替

禁止在条件查询=前对字段做任何运算

select * from zz_users where user_id * 2 = 8;
select * from zz_users where trim(user_name) = "熊猫";

也是不走索引

 !=、!、not in、not like、or...要慎用

也会导致索引失效
or可以使用union all来代替

联合索引查询确保字段的顺序性

遵循最左匹配原则

建议明确返回一条数据的语句使用limit 1

加上limit 1关键字后，当程序匹配到一条数据时就会停止扫描，如果不加的情况下会将所有数据都扫描一次。

多表查询优化

多表查询尽量不要关联太多表

主要原因如下：

• 数据量会随表数量呈直线性增长，数据量越大检索效率越低。
• 当关联的表数量过多时，无法控制好索引的匹配，涉及的表越多，索引不可控风险越大。
• 表越多，SQL逻辑越复杂，维护改造成本变大

多表查询时一定要以小驱大

以小驱大即是指用小的数据集去驱动大的数据集，说简单一点就是先查小表，再用小表的结果去大表中检索数据，其实在MySQL的优化器也会有驱动表的优化，当执行多表联查时，MySQL的关联算法为Nest Loop Join，该算法会依照驱动表的结果集作为循环基础数据，然后通过该结果集中一条条数据，作为过滤条件去下一个表中查询数据，最后合并结果得到最终数据集，MySQL优化器选择驱动表的逻辑如下：

• 如果指定了连接条件，满足查询条件的小数据表作为驱动表。
• 如果未指定连接条件，数据总行数少的表作为驱动表。
• 被驱动表字段尽量加上索引

JOIN查询相关参考 MYSQL系列-SQL查询之JOIN

业务侧优化

必要情况下可以强制指定索引

在表中存在多个索引时，有些复杂SQL的情况下，或者在存储过程中，必要时可强制指定某条查询语句走某个索引，因为MySQL优化器面对存储过程、复杂SQL时并没有那么智能，有时可能选择的索引并不是最好的，这时我们可以通过force index

select * from t1 force index(a) where a = "a";

这样就能够100%强制这条SQL走某个索引查询数据

尽量将大事务拆分为小事务执行

一个事务在执行时，如果其中包含了写操作，会先获取锁再执行，直到事务结束后MySQL才会释放锁。

而一个事务占有锁之后，会导致其他要操作相同数据的事务被阻塞，如果当一个事务比较大时，会导致一部分数据的锁定周期较长，在高并发情况下会引起大量事务出现阻塞，从而最终拖垮整个MySQL系统。

• show status like 'innodb_log_waits';查看是否有大事务由于redo_log_buffer不足，而在等待写入日志。

解决方案是将大事务改成小事务

从业务设计层面减少大量数据返回的情况

大量返回数据就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现，因此如果项目中存在这类业务，一定要记住拆分掉它，比如分批返回给客户端。

分批查询的方式也被称之为增量查询，每次基于上次返回数据的界限，再一次读取一批数据返回给客户端，这也就是经典的分页场景，通过分页的思想能够提升单次查询的速度，以及避免大数据量带来的一系列后患问题。

尽量避免深分页的情况出现

如下：

select xx,xx,xx from t1 limit 100000,10; 

可以改成

select xx,xx,xx from t1 where id in (select id from t1 limit 100000,10); 

如果字段有序并且连续，可以直接通过有序字段来判断

客户端的一些操作可以批量化完成

一些连续插入更新操作，可以采用适中事务批量完成

其他场景

避免频繁创建、销毁临时表

MySQL中的一些查询操作会产生临时表，主要包括以下情况：

慢查询优化-Explain

是MySQL自带的一个执行分析工具，可使用于select、insert、update、delete、repleace等语句上，需要使用时只需在SQL语句前加上一个explain关键字即可，然后MySQL会对应语句的执行计划列出

mysql> explain delete from t2 where a='a' and b='a' and c='c';
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
|  1 | DELETE      | t2    | NULL       | range | uniq_a_b_c    | uniq_a_b_c | 276     | const,const,const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------------+---------+-------------------+------+----------+-------------+
1 row in set (0.01 sec)

id

这是执行计划的ID值，一条SQL语句可能会出现多步执行计划，所以会出现多个ID值，这个值越大，表示执行的优先级越高，同时还会出现四种情况：

• ID相同：当出现多个ID相同的执行计划时，从上往下挨个执行。
• ID不同时：按照ID值从大到小依次执行。
• ID有相同又有不同：先从大到小依次执行，碰到相同ID时从上往下执行。
• ID为空：ID=null时，会放在最后执行。

select_type

当前执行的select语句其具体的查询类型:

• SIMPLE：简单的select查询语句，不包含union、子查询语句。
• PRIMARY：union或子查询语句中，最外层的主select语句。
• SUBQUEPY：包含在主select语句中的第一个子查询，如select ... xx = (select ...)。
• DERIVED：派生表，指包含在from中的子查询语句，如select ... from (select ...)。
• DEPENDENT SUBQUEPY：复杂SQL中的第一个select子查询（依赖于外部查询的结果集）。
• UNCACHEABLE SUBQUERY：不缓存结果集的子查询语句。
• UNION：多条语句通过union组成的查询中，第二个以及更后面的select语句。
• UNION RESULT：union的结果集。
• DEPENDENT UNION：含义同上，但是基于外部查询的结果集来查询的。
• UNCACHEABLE UNION：含义同上，但查询出的结果集不会加入缓存。
• MATERIALIZED：采用物化的方式执行的包含派生表的查询语句。

table

表示当前这个执行计划是基于哪张表执行的，这里会写出表名，但有时候也不一定是物理磁盘中存在的表名，还有可能出现如下格式：

• ：基于id=N的查询结果集，进一步检索数据。
• ：会出现在查询类型为UNION RESULT的计划中，表示结果由id=M,N...的查询组成。
• ：基于id=N的子查询结果，进一步进行数据检索。
• ：基于磁盘中已创建的某张表查询。

一句话总结就是：这个字段会写明，当前的这个执行计划会基于哪个数据集查询，有可能是物理表、有可能是子查询的结果、也有可能是其他查询生成的派生表。

partitions

用来显示分区,该列的值表示检索数据的分区

type

字段表示当前语句执行的类型:

• all：全表扫描，基于表中所有的数据，逐行扫描并过滤符合条件的数据。
• index：全索引扫描，和全表扫描类似，但这个是把索引树遍历一次，会比全表扫描要快。
• range：基于索引字段进行范围查询，如between、、in....等操作时出现的情况。
• index_subquery：和上面含义相同，区别：这个是基于非主键、唯一索引字段进行in操作。
• unique_subquery：执行基于主键索引字段，进行in操作的子查询语句会出现的情况。
• index_merge：多条件查询时，组合使用多个索引来检索数据的情况。
• ref_or_null：基于次级(非主键)索引做条件查询时，该索引字段允许为null出现的情况。
• fulltext：基于全文索引字段，进行查询时出现的情况。
• ref：基于非主键或唯一索引字段查找数据时，会出现的情况。
• eq_ref：连表查询时，基于主键、唯一索引字段匹配数据的情况，会出现多次索引查找。
• const：通过索引一趟查找后就能获取到数据，基于唯一、主键索引字段查询数据时的情况。
• system：表中只有一行数据，这是const的一种特例。
• null：表中没有数据，无需经过任何数据检索，直接返回结果。

访问数据的方式，性能从好到坏依次为：

• 完整的性能排序：null → system → const → eq_ref → ref → fulltext → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → all
• 常见的性能排序：system → const → eq_ref → ref → fulltext → range → index → all

一般在做索引优化时，一般都会要求最好优化到ref级别，至少也要到range级别，也就是最少也要基于次级索引来检索数据，不允许出现index、all这类全扫描的形式。

possible_keys

显示当前执行计划，在执行过程中可能会用到哪些索引来检索数据，但要注意的一点是：可能会用到并不代表一定会用，在某些情况下，就算有索引可以使用，MySQL也有可能放弃走索引查询。

key

前面的possible_keys字段表示可能会用到的索引，而key这个字段则会显示具体使用的索引，一般情况下都会从possible_keys的值中，综合评判出一个性能最好的索引来进行查询，但也有两种情况会出现key=null的这个场景：

• possible_keys有值，key为空：出现这种情况多半是由于表中数据不多，因此MySQL会放弃索引，选择走全表查询，也有可能是因为SQL导致索引失效。
• possible_keys、key都为空：表示当前表中未建立索引、或查询语句中未使用索引字段检索数据。

默认情况下，possible_keys有值时都会从中选取一个索引，但这个选择的工作是由MySQL优化器自己决定的，如果你想让查询语句执行时走固定的索引，则可以通过force index、ignore index的方式强制指定。

key_len

表示对应的执行计划在执行时，使用到的索引字段长度，一般情况下都为索引字段的长度，但有三种情况例外：

• 如果索引是前缀索引，这里则只会使用创建前缀索引时，声明的前N个字节来检索数据。
• 如果是联合索引，这里只会显示当前SQL会用到的索引字段长度，可能不是全匹配的情况。
• 如果一个索引字段的值允许为空，key_len的长度会为：索引字段长度+1。

ref

显示索引查找过程中，查询时会用到的常量或字段：

• const：如果显示这个，则代表目前是在基于主键字段值或数据库已有的常量（如null）查询数据。

  • select ... where 主键字段 = 主键值;
  • select ... where 索引字段 is null;

• 显示具体的字段名：表示目前会基于该字段查询数据。

• func：如果显示这个，则代表当与索引字段匹配的值是一个函数，如：

  • select ... where 索引字段 = 函数(值);

rows

代表执行时，预计会扫描的行数，这个数字对于InnoDB表来说，其实有时并不够准确，但也具备很大的参考价值，如果这个值很大，在执行查询语句时，其效率必然很低，所以该值越小越好。

filtered

该字段表示查询结果中满足WHERE条件的行数占总行数的比例，其值范围为0到1。
"filtered"字段是一个估算值，其准确性取决于MySQL对索引和数据的统计信息的准确性，以及查询语句的具体执行计划。

extra

执行查询语句时的一些其他信息，这个信息对索引调优而言比较重要，可以带来不小的参考价值，但这个字段会出现的值有很多种，如下：

• Using index：表示目前的查询语句，使用了索引覆盖机制拿到了数据。
• Using where：表示目前的查询语句无法从索引中获取数据，需要进一步做回表去拿表数据。
• Using temporary：表示MySQL在执行查询时，会创建一张临时表来处理数据。
• Using filesort：表示会以磁盘+内存完成排序工作，而完全加载数据到内存来完成排序。
• Select tables optimized away：表示查询过程中，对于索引字段使用了聚合函数。
• Using where;Using index：表示要返回的数据在索引中包含，但并不是索引的前导列，需要做回表获取数据。
• NULL：表示查询的数据未被索引覆盖，但where条件中用到了主键，可以直接读取表数据。
• Using index condition：和Using where类似，要返回的列未完全被索引覆盖，需要回表。
• Using join buffer (Block Nested Loop)：连接查询时驱动表不能有效的通过索引加快访问速度时，会使用join-buffer来加快访问速度，在内存中完成Loop匹配。
• Impossible WHERE：where后的条件永远不可能成立时提示的信息，如where 1!=1。
• Impossible WHERE noticed after reading const tables：基于唯一索引查询不存在的值时出现的提示。
• const row not found：表中不存在数据时会返回的提示。
• distinct：去重查询时，找到某个值的第一个值时，会将查找该值的工作从去重操作中移除。
• Start temporary, End temporary：表示临时表用于DuplicateWeedout半连接策略，也就是用来进行semi-join去重。
• Using MRR：表示执行查询时，使用了MRR机制读取数据。
• Using index for skip scan：表示执行查询语句时，使用了索引跳跃扫描机制读取数据。
• Using index for group-by：表示执行分组或去重工作时，可以基于某个索引处理。
• FirstMatch：表示对子查询语句进行Semi-join优化策略。
• No tables used：查询语句中不存在from子句时提示的信息，如desc table_name;。
• ......

具体的可参考《explain-Extra字段详解》，其中介绍了Extra字段可能会出现的所有值，最后基于Extra字段做个性能排序：

• Using index → NULL → Using index condition → Using where → Using where;Using index → Using join buffer → Using filesort → Using MRR → Using index for skip scan → Using temporary → Strart temporary,End temporary → FirstMatch

索引优化参考项

explain工具中的每个字段值，字段数量也比较多，但在做索引优化时，值得咱们参考的几个字段为：

• key：如果该值为空，则表示未使用索引查询，此时需要调整SQL或建立索引。
• type：这个字段决定了查询的类型，如果为index、all就需要进行优化。
• rows：这个字段代表着查询时可能会扫描的数据行数，较大时也需要进行优化。
• filtered：这个字段代表着查询时，表中不会扫描的数据行占比，较小时需要进行优化。
• Extra：这个字段代表着查询时的具体情况，在某些情况下需要根据对应信息进行优化。

在explain语句后面紧跟着show warings语句，可以得到优化后的查询语句，从而看出优化器优化了什么。

慢查询优化案例

慢查询优化思路

1.慢查询日志记录慢SQL

涉及到3个命令

mysql> show variables like 'slow_query_log%';
+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| slow_query_log      | ON                                 |
| slow_query_log_file | /home/mysql/mysql3306/log/slow.log |
+---------------------+------------------------------------+
2 rows in set (0.01 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

可以通过慢查日志，定位那些执行效率较低的SQL语句，重点关注分析

2.explain查看分析SQL的执行计划

利用上述explain查看SQL的执行计划

3.profile 分析执行耗时

explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling。
开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。

mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from t1;
+----+------+------+------+
| id | b    | c    | d    |
+----+------+------+------+
|  1 |    1 |    1 | NULL |
|  5 |    5 |    5 | NULL |
| 10 |   10 |   10 | NULL |
| 15 |   15 |   15 | NULL |
| 30 |   30 |   30 | NULL |
| 40 |   40 |   40 |   50 |
| 41 |   50 |   50 |   40 |
+----+------+------+------+
7 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+------------------+
| Query_ID | Duration   | Query            |
+----------+------------+------------------+
|        1 | 0.00037025 | select * from t1 |
+----------+------------+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000099 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000025 |
| init                 | 0.000013 |
| System lock          | 0.000006 |
| optimizing           | 0.000002 |
| statistics           | 0.000008 |
| preparing            | 0.000007 |
| executing            | 0.000001 |
| Sending data         | 0.000163 |
| end                  | 0.000002 |
| query end            | 0.000006 |
| closing tables       | 0.000004 |
| freeing items        | 0.000020 |
| cleaning up          | 0.000010 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000099 | 0.000032 |   0.000039 |            0 |             0 |
| checking permissions | 0.000005 | 0.000002 |   0.000003 |            0 |             0 |
| Opening tables       | 0.000025 | 0.000011 |   0.000014 |            0 |             0 |
| init                 | 0.000013 | 0.000006 |   0.000007 |            0 |             0 |
| System lock          | 0.000006 | 0.000003 |   0.000003 |            0 |             0 |
| optimizing           | 0.000002 | 0.000001 |   0.000001 |            0 |             0 |
| statistics           | 0.000008 | 0.000004 |   0.000005 |            0 |             0 |
| preparing            | 0.000007 | 0.000003 |   0.000003 |            0 |             0 |
| executing            | 0.000001 | 0.000000 |   0.000001 |            0 |             0 |
| Sending data         | 0.000163 | 0.000074 |   0.000090 |            0 |             0 |
| end                  | 0.000002 | 0.000001 |   0.000001 |            0 |             0 |
| query end            | 0.000006 | 0.000002 |   0.000003 |            0 |             0 |
| closing tables       | 0.000004 | 0.000002 |   0.000003 |            0 |             0 |
| freeing items        | 0.000020 | 0.000009 |   0.000010 |            0 |             0 |
| cleaning up          | 0.000010 | 0.000004 |   0.000006 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

profilesing_history_size设置profiles数量，可用show profile cpu,block io for query id查看CPU 和IO

4.Optimizer Trace分析详情

profile只能查看到SQL的执行耗时，但是无法看到SQL真正执行的过程信息，即不知道MySQL优化器是如何选择执行计划。这时候，我们可以使用Optimizer Trace，它可以跟踪执行语句的解析优化执行的全过程。

使用set optimizer_trace="enabled=on"打开开关，接着执行要跟踪的SQL，最后执行select * from information_schema.optimizer_trace跟踪

mysql> show variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.01 sec)

mysql> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id = '30';
+----+------+------+------+
| id | b    | c    | d    |
+----+------+------+------+
| 30 |   30 |   30 | NULL |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> select * from information_schema.optimizer_trace;
+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| QUERY                            | TRACE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |
+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| select * from t1 where   | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t1`.`d` AS `d` from `t1` where (`t1`.`id` = '30')"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`t1`.`id` = '30')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`t1`.`id` = '30')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`t1`.`id` = '30')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`t1`.`id` = '30')"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t1`",
                "field": "id",
                "equals": "'30'",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ]
          },
          {
            "condition_on_constant_tables": "('30' = '30')",
            "condition_value": true
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "('30' = '30')",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
              ]
            }
          },
          {
            "refine_plan": [
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
} |                                 0 |                       0 |
+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
1 row in set (0.00 sec)

可以查看分析其执行树，会包括三个阶段：

• join_preparation：准备阶段
• join_optimization：分析阶段
• join_execution：执行阶段

5.确定问题并采用相应的措施

• 多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，我们可以 优化索引。
• 我们还可以优化SQL语句，比如一些in元素过多问题（分批），深分页问题（基于上一次数据过滤等），进行时间分段查询
• SQl没办法很好优化，可以改用ES的方式，或者数仓。
• 如果单表数据量过大导致慢查询，则可以考虑分库分表
• 如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数，跟DBA讨论优化方案
• 如果存量数据量太大，考虑是否可以让部分数据归档

慢SQL的原因参考 盘点MySQL慢查询的12个原因

慢SQL案例

案例1：隐式转换

mysql> CREATE TABLE t3 (

    ->   id int(11) NOT NULL AUTO_INCREMENT,
    ->   userId varchar(32) NOT NULL,
    ->   age  varchar(16) NOT NULL,
    ->   name varchar(255) NOT NULL,
    ->   PRIMARY KEY (id),
    ->   KEY idx_userid (userId) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> INSERT INTO t3 VALUES(1,'1','1','a'),(2,'2','2','b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> explain select * from t3 where userId = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t3    | NULL       | ALL  | idx_userid    | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> explain select * from t3 where userId = '2';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t3    | NULL       | ref  | idx_userid    | idx_userid | 98      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

userId字段为字串类型，是B+树的普通索引，如果查询条件传了一个数字过去，会进行隐式转换，从而导致索引失效

为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。

案例2：深分页问题

limit深分页问题，会导致慢查询

mysql> CREATE TABLE t4 (
    ->   id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
    ->   name varchar(255) DEFAULT NULL COMMENT '账户名',
    ->   balance int(11) DEFAULT NULL COMMENT '余额',
    ->   create_time datetime NOT NULL COMMENT '创建时间',
    ->   update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    ->   PRIMARY KEY (id),
    ->   KEY idx_name (name),
    ->   KEY idx_create_time (create_time)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE insert_data()
    -> BEGIN
    ->   DECLARE i INT DEFAULT 1;
    ->   WHILE i      INSERT INTO t4 (name, balance, create_time, update_time)
    ->     VALUES (CONCAT('user_', i), FLOOR(RAND() * 100000), NOW(), NOW());
    ->     SET i = i + 1;
    ->   END WHILE;
    -> END$$
Query OK, 0 rows affected (0.04 sec)

mysql> DELIMITER ;
mysql> CALL insert_data();
mysql> explain select id,name,balance,create_time from t4 where create_time> '2020-09-19' limit 90000,10;
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | t4    | NULL       | range | idx_create_time | idx_create_time | 5       | NULL | 50042 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

这个SQL的执行流程如下：

因此，limit深分页，导致SQL变慢原因有两个：

• limit语句会先扫描 offset+n行，然后再丢弃掉前 offset行，返回后 n行数据。也就是说 limit 100000,10，就会扫描 100010行，而 limit 0,10，只扫描 10行。
• limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

如何优化深分页问题?
标签记录法

就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

select id,name,balance FROM t4 where id > 90000 limit 10;

延迟关联法

把条件转移到主键索引树，然后减少回表。

select acct1.id,acct1.name,acct1.balance FROM t4 acct1 INNER JOIN (SELECT a.id FROM t4 a WHERE a.create_time > '2020-09-19' limit 90000, 10) AS acct2 on acct1.id= acct2.id;

优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。

案例3：in元素过多

如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过200个，如果超过了，建议分组，每次200一组进行

in查询为什么慢呢？

这是因为in查询在MySQL底层是通过n*m的方式去搜索，类似union。

in查询在进行cost代价计算时（代价 = 元组数 * IO平均值），是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MySQL设置了个临界值(eq_range_index_dive_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200，即in条件超过了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。

案例4.order by 走文件排序导致的慢查询

如果order by 使用到文件排序，则会可能会产生慢查询

mysql> explain select id,name,balance from t4 where create_time> '2020-09-19' order by name limit 90000,10;
+----+-------------+-------+------------+------+-----------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t4    | NULL       | ALL  | idx_create_time | NULL | NULL    | NULL | 100085 |    50.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+-----------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+

查看explain执行计划的时候，可以看到Extra这一列，有一个Using filesort，它表示用到文件排序。

order by文件排序效率为什么较低

order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。

rowid排序

rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。
select id,name,balance from t4 where create_time> '2020-09-19' order by name limit 10;

全字段排序

同样的SQL，如果是走全字段排序是这样的：
select id,name,balance from t4 where create_time> '2020-09-19' order by name limit 10;

sort_buffer的大小是由一个参数控制的：sort_buffer_size。

• 如果要排序的数据小于 sort_buffer_size，排序在 sort_buffer内存中完成
• 如果要排序的数据大于 sort_buffer_size，则借助磁盘文件来进行排序。

借助磁盘文件排序的话，效率就更慢一点。因为先把数据放入sort_buffer，当快要满时。会排一下序，然后把sort_buffer中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件。

如何优化order by的文件排序

order by使用文件排序，效率会低一点。我们怎么优化呢？

• 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化 order by语句。
• 我们还可以通过调整 max_length_for_sort_data、sort_buffer_size等参数优化；

案例5.左右连接，关联的字段编码格式不一样

新建两个表，一个user，一个user_job

mysql> CREATE TABLE `user_job` (
    ->   `id` int(11) NOT NULL,
    ->   `userId` int(11) NOT NULL,
    ->   `job` varchar(255) DEFAULT NULL,
    ->   `name` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_name` (`name`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> drop table user;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `user` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
    ->   `age` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_name` (`name`) USING BTREE
    -> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> -- 插入user表数据
mysql> INSERT INTO `user` (`id`, `name`, `age`) VALUES
    -> (1, 'John', 20),
    -> (2, 'Tom', 25),
    -> (3, 'David', 30);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> -- 插入user_job表数据
mysql> INSERT INTO `user_job` (`id`, `userId`, `job`, `name`) VALUES
    -> (1, 1, 'Engineer', 'John'),
    -> (2, 2, 'Manager', 'Tom'),
    -> (3, 3, 'Sales', 'David');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> explain select u.name,j.name,j.job from user u left join user_job j on u.name = j.name;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | index | NULL          | idx_name | 1023    | NULL |    3 |   100.00 | Using index                                        |
|  1 | SIMPLE      | j     | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

编码不一致走了全表扫描

案例6.group by使用临时表

使用案例2的t4表

mysql> explain select balance,count(*) from t4 group by balance;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
|  1 | SIMPLE      | t4    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100085 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

• Extra 这个字段的 Using temporary表示在执行分组的时候使用了临时表
• Extra 这个字段的 Using filesort表示使用了文件排序

group by是怎么使用到临时表和排序了呢？我们来看下这个SQL的执行流程
select balance,count(*) from t4 group by balance;

• 判断临时表中是否有为 balance='X'的行，没有就插入一个记录  (X,1);
• 如果临时表中有 balance='X'的行，就将X这一行的num值加 1；

临时表的排序是怎样的呢？

就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分全字段排序和rowid排序

• 如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回
• 如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。

group by可能会慢在哪里？

group by使用不当，很容易就会产生慢SQL问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

• 如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是 tmp_table_size），会把内存临时表转成磁盘临时表。
• 如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。

如何优化group by呢

从哪些方向去优化呢？

• 方向1：既然它默认会排序，我们不给它排是不是就行啦。
• 方向2：既然临时表是影响group by性能的X因素，我们是不是可以不用临时表？

我们一起来想下，执行group by语句为什么需要临时表呢？group by的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦?

可以有这些优化方案：

• group by 后面的字段加索引
• order by null 不用排序
• 尽量只使用内存临时表
• 使用SQL_BIG_RESULT

案例7.delete + in子查询不走索引！

mysql> create table t5 like t4;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t5 select * from t4 limit 100;
Query OK, 100 rows affected (0.03 sec)
Records: 100  Duplicates: 0  Warnings: 0

mysql> explain delete from t4 where name in (select name from t5);
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+--------+----------+-------------+
| id | select_type        | table | partitions | type           | possible_keys | key      | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+--------+----------+-------------+
|  1 | DELETE             | t4    | NULL       | ALL            | NULL          | NULL     | NULL    | NULL | 100085 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t5    | NULL       | index_subquery | idx_name      | idx_name | 768     | func |      1 |   100.00 | Using index |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+--------+----------+-------------+
2 rows in set (0.00 sec)

但是如果把delete换成select，就会走索引

mysql> explain select * from t4 where name in (select name from t5);
+----+-------------+-------+------------+-------+---------------+----------+---------+--------------+------+----------+-------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref          | rows | filtered | Extra                               |
+----+-------------+-------+------------+-------+---------------+----------+---------+--------------+------+----------+-------------------------------------+
|  1 | SIMPLE      | t5    | NULL       | index | idx_name      | idx_name | 768     | NULL         |  100 |   100.00 | Using where; Using index; LooseScan |
|  1 | SIMPLE      | t4    | NULL       | ref   | idx_name      | idx_name | 768     | toby.t5.name |    1 |   100.00 | NULL                                |
+----+-------------+-------+------------+-------+---------------+----------+---------+--------------+------+----------+-------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `toby`.`t4`.`id` AS `id`,`toby`.`t4`.`name` AS `name`,`toby`.`t4`.`balance` AS `balance`,`toby`.`t4`.`create_time` AS `create_time`,`toby`.`t4`.`update_time` AS `update_time` from `toby`.`t4` semi join (`toby`.`t5`) where (`toby`.`t4`.`name` = `toby`.`t5`.`name`) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

实际执行的时候，MySQL对select in子查询做了优化，把子查询改成join的方式，所以可以走索引。但是很遗憾，对于delete in子查询，MySQL却没有对它做这个优化。

相关参数配置

JOIN 相关配置

临时表设置

参考