一、快速学会分析SQL执行效率
二、条件字段有索引，为什么查询也这么慢?
三、如何优化数据导入
四、让order by、group by查询更快
五、换种思路写分页查询
六、Join语句可以这样优化
七、为何count(*)这么慢?
- 1 重新认识 count()
- 2 哪些方法可以加快 count()

一、快速学会分析SQL执行效率

优化 SQL 的前提是能定位到慢 SQL 并对其进行分析

1 定位慢 SQL

定位慢 SQL 有如下两种解决方案：

查看慢查询日志确定已经执行完的慢查询
show processlist 查看正在执行的慢查询

1.1 通过慢查询日志

如果需要定位到慢查询，一般的方法是通过慢查询日志来查询的。
MySQL 的慢查询日志用来记录

响应时间超过参数 long_query_time（单位秒，默认值 10）
并且扫描记录数不小于 min_examined_row_limit（默认值 0）的语句

能够帮我们找到执行完的慢查询，方便我们对这些 SQL 进行优化。

知识扩展：默认情况下，慢查询日志中不会记录管理语句，可通过设置 log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询日志中。

默认情况下，也不会记录查询时间不超过 long_query_time 。但是不使用索引的语句，可通过配置 log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中（即使查询时间没超过 long_query_time 配置的值）。

如果需要使用慢查询日志，一般分为四步：开启慢查询日志、设置慢查询阀值、确定慢查询日志路径、确定慢查询日志的文件名。

首先开启慢查询日志，由参数 slow_query_log 决定是否开启，在 MySQL 命令行下输入下面的命令：

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

默认环境下，慢查询日志是关闭的。

设置慢查询时间阀值

mysql> set global long_query_time = 1; 
Query OK, 0 rows affected (0.00 sec)

知识扩展：
MySQL 中 long_query_time 的值如何确定呢？ 线上业务一般建议把 long_query_time 设置为 1 秒，如果某个业务的 MySQL 要求比较高的 QPS，可设置慢查询为 0.1 秒。发现慢查询及时优化或者提醒开发改写。

一般测试环境建议 long_query_time 设置的阀值比生产环境的小，比如生产环境是 1 秒，则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的 SQL。

甚至某些重要业务测试环境 long_query_time 可以设置为 0，以便记录所有语句。并留意慢查询日志的输出，上线前的功能测试完成后，分析慢查询日志每类语句的输出，重点关注 Rows_examined（语句执行期间从存储引擎读取的行数），提前优化。

确定慢查询日志路径

慢查询日志的路径默认是 MySQL 的数据目录

mysql> show global variables like "datadir";
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| datadir       | E:\tools\mysql-8.0.15-winx64\data\ |
+---------------+------------------------------------+
1 row in set, 1 warning (0.03 sec)

确定慢查询日志的文件名

mysql> show global variables like "slow_query_log_file";
+---------------------+------------------------------------------------------------+
| Variable_name       | Value                                                      |
+---------------------+------------------------------------------------------------+
| slow_query_log_file | E:\tools\mysql-8.0.15-winx64\data\DESKTOP-QCE7G9G-slow.log |
+---------------------+------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

查看 E:\tools\mysql-8.0.15-winx64\data\DESKTOP-QCE7G9G-slow.log 文件

E:\tools\mysql-8.0.15-winx64\bin\mysqld, Version: 8.0.15 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2021-10-15T03:37:27.921446Z
# User@Host: root[root] @ localhost [::1]  Id:    10
# Query_time: 42.900958  Lock_time: 0.001285 Rows_sent: 0  Rows_examined: 0
use muke;
SET timestamp=1634269047;
call insert_t1();

这里对上方的执行结果详细描述一下：

Time：慢查询发生的时间
User@Host：客户端用户和 IP
Query_time：查询时间
Lock_time：等待表锁的时间
Rows_sent：语句返回的行数
Rows_examined：语句执行期间从存储引擎读取的行数

1.2 通过 show processlist;

有时慢查询正在执行，已经导致数据库负载偏高了，而由于慢查询还没执行完，因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。如果有 PROCESS 权限，则可以看到所有线程。否则，只能看到当前会话的线程。

知识扩展：如果不使用 FULL 关键字，在 info 字段中只显示每个语句的前 100 个字符，如果想看语句的全部内容可以使用 full 修饰（show full processlist）。

执行结果如下：

mysql> show processlist\G`
`......`
`*************************** 10. row ***************************`
     `Id: 7651833`
   `User: one`
   `Host: 192.168.1.251:52154`
     `db: ops`
`Command: Query`
   `Time: 3`
  `State: User sleep`
   `Info: select sleep(10)`
`......`
`10 rows in set (0.00 sec)`

这里对上面结果解释一下：

Time：表示执行时间
Info：表示 SQL 语句

我们这里可以通过它的执行时间（Time）来判断是否是慢 SQL。

2 使用 explain 分析慢查询

我们可以通过 explain、show profile 和 trace 等诊断工具来分析慢查询。

Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用方法：在查询语句前面加上 explain 运行就可以了。
这也是分析 SQL 时最常用的，也是作者最推荐的一种分析慢查询的方式。

下面尝试使用 explain 分析一条 SQL，例子如下：

mysql> explain select * from t1 where b=100;

Explain 的结果各字段解释如下：
加粗的列为需要重点关注的项。

列名	解释
id	查询编号
select_type	查询类型：显示本行是简单还是复杂查询
table	涉及到的表
partitions	匹配的分区：查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表，该值为 NULL。
type	本次查询的表连接类型
possible_keys	可能选择的索引
key	实际选择的索引
key_len	被选择的索引长度：一般用于判断联合索引有多少列被选择了
ref	与索引比较的列
rows	预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确
filtered	按条件筛选的行的百分比
Extra	附加信息

表 1-explain 各字段解释

其中 explain 各列都有各种不同的值，这里介绍几个比较重要列常包含的值：包含 select_typ、type 和 Extra。
下面将列出它们常见的一些值，可稍微过一遍，不需要完全记下来，在后续章节分析 SQL 时，可以返回查询本节内容并对比各种值的区别。

2.1 select_type

select_type 的值	解释
SIMPLE	简单查询 (不使用关联查询或子查询)
PRIMARY	如果包含关联查询或者子查询，则最外层的查询部分标记为 primary
UNION	联合查询中第二个及后面的查询
DEPENDENT UNION	满足依赖外部的关联查询中第二个及以后的查询
UNION RESULT	联合查询的结果
SUBQUERY	子查询中的第一个查询
DEPENDENT SUBQUERY	子查询中的第一个查询，并且依赖外部查询
DERIVED	用到派生表的查询
MATERIALIZED	被物化的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外层查询的每一行
UNCACHEABLE UNION	关联查询第二个或后面的语句属于不可缓存的子查询

表 2-select_type 各项值解释

2.2 type

type 的值	解释
system	查询对象表只有一行数据，且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况
const	基于主键或唯一索引查询，最多返回一条结果
eq_ref	表连接时基于主键或非 NULL 的唯一索引完成扫描
ref	基于普通索引的等值查询，或者表间等值连接
fulltext	全文检索
ref_or_null	表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	利用索引进行范围查询
index	全索引扫描
ALL	全表扫描

表 3-type 各项值解释

上表的这些情况，查询性能从上到下依次是最好到最差。

2.3 Extra

Extra 常见的值	解释	例子
Using filesort	将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序	explain select * from t1 order by create_time;
Using temporary	需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时	explain select * from t1 group by create_time;
Using index	使用覆盖索引	explain select a from t1 where a=111;
Using where	使用 where 语句来处理结果	explain select * from t1 where create_time=‘2019-06-18 14:38:24’;
Impossible WHERE	对 where 子句判断的结果总是 false 而不能选择任何数据	explain select * from t1 where 1<0;
Using join buffer (Block Nested Loop)	关联查询中，被驱动表的关联字段没索引	explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time);
Using index condition	先条件过滤索引，再查数据	explain select * from t1 where a >900 and a like “%9”;
Select tables optimized away	使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是	explain select max(a) from t1;

表 4-Extra 常见值解释及举例

3 show profile 分析慢查询

有时需要确定 SQL 到底慢在哪个环节，此时 explain 可能不好确定。在 MySQL 数据库中，通过 profile，能够更清楚地了解 SQL 执行过程的资源使用情况，能让我们知道到底慢在哪个环节。

知识扩展：可以通过配置参数 profiling = 1 来启用 SQL 分析。该参数可以在全局和 session 级别来设置。对于全局级别则作用于整个MySQL 实例，而 session 级别仅影响当前 session 。该参数开启后，后续执行的 SQL 语句都将记录其资源开销，如 IO、上下文切换、CPU、Memory等等。根据这些开销进一步分析当前 SQL 从而进行优化与调整。

使用 profile 分析慢查询，大致步骤是：

确定这个 MySQL 版本是否支持 profile；
确定 profile 是否关闭；
开启 profile；
执行 SQL；
查看执行完 SQL 的 query id；
通过 query id 查看 SQL 的每个状态及耗时时间。

3.1 确定是否支持 profile

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

YES，表示支持profile的。

3.2 查看 profiling 是否关闭的

进行第二步，用下面命令判断 profiling 参数是否关闭（默认 profiling 是关闭的）：

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

0 表示 profiling 参数状态是关闭的。

3.3 通过 set 开启 profile

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Tips：set 时没加 global，只对当前 session 有效。

3.4 执行 SQL 语句

mysql> select * from t1 where b=1000;

3.5 确定 SQL 的 query id

通过 show profiles 语句确定执行过的 SQL 的 query id：

mysql> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00063825 | select * from t1 where b=1000 |
+----------+------------+-------------------------------+
1 row in set, 1 warning (0.00 sec)

3.6 查询 SQL 执行详情

通过 show profile for query 可看到执行过的 SQL 每个状态和消耗时间：

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000115 |
| checking permissions | 0.000013 |
| Opening tables       | 0.000027 |
| init                 | 0.000035 |
| System lock          | 0.000017 |
| optimizing           | 0.000016 |
| statistics           | 0.000025 |
| preparing            | 0.000020 |
| executing            | 0.000006 |
| Sending data         | 0.000294 |
| end                  | 0.000009 |
| query end            | 0.000012 |
| closing tables       | 0.000011 |
| freeing items        | 0.000024 |
| cleaning up          | 0.000016 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

通过以上结果，可以确定 SQL 执行过程具体在哪个过程耗时比较久，从而更好地进行 SQL 优化与调整。

4 trace 分析 SQL 优化器

explain 可以查看 SQL 执行计划，但是无法知道它为什么做这个决策。
从 MySQL 5.6 开始，可以使用 trace 查看优化器如何选择执行计划。

通过trace，能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，或者知道某个排序使用的排序模式，帮助我们更好地理解优化器行为。

如果需要使用：

先开启 trace，设置格式为 JSON
再执行需要分析的 SQL
最后查看 trace 分析结果（在 information_schema.OPTIMIZER_TRACE 中）
及时关闭trace ```plsql mysql> set session optimizer_trace=”enabled=on”,end_markers_in_json=on;

mysql> select a,d from t1 order by d; / 查询表t1的id、a、d三个字段的值，按照字段d进行排序 /

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G

mysql> set session optimizer_trace=”enabled=off”;


开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。<br />首先构造如下 SQL (表示取出表 t1 中 a 的值大于 900 并且 b 的值大于 910 的数据，然后按照 a 字段排序)：
```plsql
select * from t1 where a >900 and b > 910 order  by a;

explain 分析下执行计划：

该语句使用的是 b 字段的索引 idx_b。实际表 t1 中，a、b 两个字段都有索引
为什么条件中有这两个索引字段却偏偏选了 b 字段的索引呢？这时就可以使用 trace 进行分析。大致步骤如下：

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
/* optimizer_trace="enabled=on" 表示开启 trace；end_markers_in_json=on 表示 JSON 输出开启结束标记 */
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a >900 and b > 910 order  by a;
+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
......
| 1000 | 1000 | 1000 |
+------+------+------+
1000 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: select * from t1 where a >900 and b > 910 order  by a    --SQL语句
TRACE: {
  "steps": [
    {
      "join_preparation": {                --SQL准备阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {            --SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {    --条件处理
              "condition": "WHERE",
              "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",        --原始条件
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"         --等值传递转换
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"       --常量传递转换
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"        --去除没有的条件后的结构
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */   --替换虚拟生成列
          },
          {
            "table_dependencies": [        --表依赖详情
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [    --预估表的访问成本
              {
                "table": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1000,       --扫描行数
                    "cost": 207.1       --成本
                  } /* table_scan */,
                  "potential_range_indexes": [    --分析可能使用的索引
                    {
                      "index": "PRIMARY",
                      "usable": false,       --为false，说明主键索引不可用
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_a",      --可能使用索引idx_a
                      "usable": true,
                      "key_parts": [
                        "a",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_b",      --可能使用索引idx_b
                      "usable": true,
                      "key_parts": [
                        "b",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": { --分析各索引的成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_a",    --使用索引idx_a的成本
                        "ranges": [
                          "900 < a"            --使用索引idx_a的范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true, --是否使用index dive（详细描述请看下方的知识扩展）
                        "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,      --是否使用mrr
                        "index_only": false,    --是否使用覆盖索引
                        "rows": 100,            --使用该索引获取的记录数
                        "cost": 121.01,         --使用该索引的成本
                        "chosen": true          --可能选择该索引
                      },
                      {
                        "index": "idx_b",       --使用索引idx_b的成本
                        "ranges": [
                          "910 < b"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 90,
                        "cost": 109.01,
                        "chosen": true             --也可能选择该索引
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": { --分析使用索引合并的成本
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {  --确认最优方法
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_b",
                      "rows": 90,
                      "ranges": [
                        "910 < b"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 90,
                    "cost_for_plan": 109.01,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [  --考虑的执行计划
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {          --最优的访问路径
                  "considered_access_paths": [ --决定的访问路径
                    {
                      "rows_to_scan": 90,      --扫描的行数
                      "access_type": "range",  --访问类型：为range
                      "range_details": {
                        "used_index": "idx_b"  --使用的索引为：idx_b
                      } /* range_details */,
                      "resulting_rows": 90,    --结果行数
                      "cost": 127.01,          --成本
                      "chosen": true,           --确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 90,
                "cost_for_plan": 127.01,
                "sort_cost": 90,
                "new_cost_for_plan": 217.01,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {  --尝试添加一些其他的查询条件
              "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`t1`.`a`",
              "items": [
                {
                  "item": "`t1`.`a`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`t1`.`a`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`t1`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "idx_b",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [          --改进的执行计划
              {
                "table": "`t1`",
                "pushed_index_condition": "(`t1`.`b` > 910)",
                "table_condition_attached": "(`t1`.`a` > 900)"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {             --SQL执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`t1`",
                "field": "a"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,             --未使用优先队列优化排序
              "cause": "not applicable (no LIMIT)"     --未使用优先队列排序的原因是没有limit
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {           --排序详情
              "rows": 90,
              "examined_rows": 90,          --参与排序的行数
              "number_of_tmp_files": 0,     --排序过程中使用的临时文件数
              "sort_buffer_size": 115056,
              "sort_mode": "<sort_key, additional_fields>"   --排序模式（详解请看下方知识扩展）
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0    --该字段表示分析过程丢弃的文本字节大小，本例为0，说明没丢弃任何文本
          INSUFFICIENT_PRIVILEGES: 0    --查看trace的权限是否不足，0表示有权限查看trace详情
1 row in set (0.00 sec)
------------------------------------------------
------------------------------------------------
mysql> set session optimizer_trace="enabled=off";
/* 及时关闭trace */

对上方的执行字段详细描述一下：

TRACE 字段中整个文本大致分为三个过程。

准备阶段：对应文本中的 join_preparation
优化阶段：对应文本中的 join_optimization
执行阶段：对应文本中的 join_execution

使用时，重点关注优化阶段和执行阶段。

由此例可以看出：

在 trace 结果的 analyzing_range_alternatives 这一项可以看到：使用索引 idx_a 的成本为 121.01，使用索引 idx_b 的成本为 109.01，显然使用索引 idx_b 的成本要低些，因此优化器选择了 idx_b 索引；
在 trace 结果的 filesort_summary 这一项可以看到：排序模式为，表示使用的是单路排序，即一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序。

知识扩展： 知识点一：MySQL 常见排序模式：

< sort_key, rowid >双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
< sort_key, additional_fields >单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
< sort_key, packed_additional_fields >打包数据排序模式：将 char 和 varchar 字段存到 sort buffer 中时，更加紧缩。

三种排序模式比较：

第二种模式相对第一种模式，避免了二次回表，可以理解为用空间换时间。由于 sort buffer 有限，如果需要查询的数据比较大的话，会增加磁盘排序时间，效率可能比第一种方式更低。

MySQL 提供了一个参数：max_length_for_sort_data，当“排序的键值对大小” > max_length_for_sort_data 时，MySQL 认为磁盘外部排序的 IO 效率不如回表的效率，会选择第一种排序模式；否则，会选择第二种模式。

第三种模式主要解决变长字符数据存储空间浪费的问题。

知识点二：优化器在估计符合条件的行数时有两个选择：

index diver：dive 到 index 中利用索引完成元组数的估算；特点是速度慢，但可以得到精确的值；
index statistics：使用索引的统计数值，进行估算；特点是速度快，但是值不一定准确。

5 总结

如何定位慢 SQL:

一种方法是查看慢查询日志
另一种方法是 show process 查看正在执行的 SQL

对比一下三种分析 SQL 方法的特点：

explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；（explain 分析慢 SQL，explain 会返回很多字段，其中 select_type、type、key、rows、Extra 是重点关注项。）
profile：可以清楚了解到SQL到底慢在哪个环节（大表格显示）；
trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。

二、条件字段有索引，为什么查询也这么慢?

讲解几种有索引但是查询不走索引导致查询慢的场景。
建表语句

CREATE TABLE `t1` (             /* 创建表t1 */
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,
  `b` int(20) DEFAULT NULL,
  `c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE,
  KEY `idx_c` (`c`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
delimiter ;;
create procedure insert_t1()        /* 创建存储过程insert_t1 */
begin
  declare i int;                    /* 声明变量i */
  set i=1;                          /* 设置i的初始值为1 */
  while(i<=10000)do                 /* 对满足i<=10000的值进行while循环 */
    insert into t1(a,b) values(i,i);  /* 写入表t1中a、b两个字段，值都为i当前的值 */
    set i=i+1;                        /* 将i加1 */
  end while;
end;;
delimiter ;
call insert_t1();                    /* 运行存储过程insert_t1 */
update t1 set c = '2019-05-22 00:00:00';  /* 更新表t1的c字段，值都为'2019-05-22 00:00:00' */
update t1 set c = '2019-05-21 00:00:00' where id=10000;     /* 将id为10000的行的c字段改为与其它行都不一样的数据，以便后面实验使用 */

1 函数操作

mysql> explain select * from t1 where date(c) ='2019-05-21';

type 为 ALL，key 字段结果为 NULL，因此知道该 SQL 是没走索引的全表扫描。

1.1 不走索引原因：

c 字段普通索引的 B+ 索引树如下：

索引树中存储的是列的实际值和主键值。如果拿 ‘2019-05-21’ 去匹配，将无法定位到索引树中的值。因此放弃走索引，而选择全表扫描。

1.2 函数操作的 SQL 优化

改成 c 字段实际值相匹配的形式。因为 SQL 的目的是查询 2019-05-21 当天所有的记录，因此可以改成范围查询，

mysql> explain select * from t1 where c>='2019-05-21 00:00:00' and c<='2019-05-21 23:59:59';

根据上面的结果，可确定，走了 c 字段的索引（对应关注字段 key），扫描行数 1 行（对应关注字段 rows）。

经验分享：
类似求某一天或者某一个月数据的需求，建议写成类似上例的范围查询，可让查询能走索引。避免对条件索引字段做函数处理。我在工作中就曾经遇到过这类慢查询，如下：

SELECT tml_num_id, status_num_id FROM sd_bl_so_tml_hdr WHERE  
tenant_num_id = 6 AND data_sign = 0 
AND sub_unit_num_id = 100004 AND  channel_num_id = 91 
AND date_format(order_date, '%Y%m%d') =  date_format('2019-06-02', '%Y%m%d') 
AND status_num_id < 3 LIMIT 100;

如果明白了上面的优化技巧，可以尝试着改写优化这条 SQL。

2 隐式转换

如：varcher类型查询时没有加 ‘’ , 所以建议在写SQL时，先看字段类型，然后根据字段类型写SQL。

表中对应手机号字段是 varchar 类型，因此写出了如下不合理的SQL：

select user_name,tele_phone from user_info where tele_phone =11111111111;

比如我们要查询 a 字段（varcher类型）等于 1000 的值

mysql> explain select * from t1 where a=1000;

通过 type 这列可以看到是最差的情况 ALL，通过 key 这列可以看到没走 a 字段的索引，通过 rows 这列可以看到进行了全表扫描。

2.1 不走索引原因

a 字段类型是 varchar(20)，而语句中 a 字段条件值没加单引号，导致 MySQL 内部会先把a转换成int型，再去做判断，相当于实际执行的 SQL 语句如下：

mysql> select * from t1 where cast(a as signed int) =1000;

因此又回到上面说的：对索引字段做函数操作时，优化器会放弃使用索引。

2.2 隐式转换的 SQL 优化

索引字符串列条件添加单引号，查看执行计划：

mysql> explain select * from t1 where a='1000';

通过 type 这列，可以看到是 ref（基于普通索引的等值查询，比 ALL 性能好很多），通过key这列，可以看到已经走了 a 字段的索引，通过rows这列可以看到通过索引查询后就扫描了一行。
因此在联系方式这个例子中的 sql 1 可以这样优化：

select user_name,tele_phone from user_info where tele_phone ='11111111111';

3 模糊查询

最左匹配原则

很多时候我们想根据某个字段的某几个关键字查询数据，实际这种情况无法走索引，看下执行计划：

mysql> explain select * from t1 where a like '%1111%';

重点留意type、key、rows、Extra，发现是全表扫描。

3.1 模糊查询优化建议

修改业务，让模糊查询必须包含条件字段前面的值，然后落到数据库的查询为：

Tips：这个优化方式必须结合业务，如果只是这样改SQL，可能会导致查询的结果不正确。
这种写法是可以用到索引的，explain分析如下：

mysql> explain select * from t1 where a like '1111%';

4 范围查询

范围太大是走索引的，具体多大？这个就是看服务器配置了

首先看下这条 SQL 的执行计划：

mysql> explain select * from t1 where b>=1 and b <=2000;

发现并不能走b字段的索引。
原因：优化器会根据检索比例、表大小、I/O块大小等进行评估是否使用索引。比如单次查询的数据量过大，优化器将不走索引。

4.1 优化范围查询

降低单次查询范围，分多次查询：

mysql> select * from t1 where b>=1 and b <=1000; 
mysql> select * from t1 where b>=1001 and b <=2000;

查看执行计划（就只看第一条的，第二条同理）：

mysql> explain select * from t1 where b>=1 and b <=1000; 预览

因此，降低查询范围后，能正常使用索引。

经验分享：实际这种范围查询而导致使用不了索引的场景经常出现，比如按照时间段抽取全量数据，每条SQL抽取一个月的；或者某张业务表历史数据的删除。遇到此类操作时，应该在执行之前对SQL做explain分析，确定能走索引，再进行操作，否则不但可能导致操作缓慢，在做更新或者删除时，甚至会导致表所有记录锁住，十分危险。

5 计算操作

有时我们与有对条件字段做计算操作的需求，在使用 SQL 查询时，就应该小心了。先看下例：

mysql> explain select * from t1 where b-1 =1000;

原因：对索引字段做运算将使用不了索引。

5.1 计算操作的 SQL 优化

将计算操作放在等号后面：

mysql> explain select * from t1 where b =1000 + 1;

发现将计算操作放在等号后，能正常使用索引。

经验分享：

一般需要对条件字段做计算时，建议通过程序代码实现，而不是通过MySQL实现。如果在MySQL中计算的情况避免不了，那必须把计算放在等号后面。

6 总结

本节讲解几种条件字段有索引，但是使用不了索引的场景。因此在写 SQL 时应该注意这些点：

应该避免隐式转换
like查询不能以%开头
范围查询时，包含的数据比例不能太大
不建议对条件字段做运算及函数操作

三、如何优化数据导入

1 一次插入多行的值

有大批量导入时，推荐一条insert语句插入多行数据。

插入行所需的时间由以下因素决定（参考MySQL 5.7参考手册：8.2.4.1优化INSERT语句）

连接：30%
向服务器发送查询：20%
解析查询：20%
插入行：10% * 行的大小
插入索引：10% * 索引数
结束：10%

可发现大部分时间耗费在客户端与服务端通信的时间，因此可以使用 insert 包含多个值来减少客户端和服务器之间的通信。我们通过实验来验证下一次插入多行与一次插入一行的效率对比。

两类插入

1.1 一条SQL只包含多行数据的数据文件

INSERT INTO `t1` VALUES 
(1,'1',1,'2019-05-24 15:44:10'),
(2,'2',2,'2019-05-24 15:44:10'),
(3,'3',3,'2019-05-24 15:44:10')......

1.2 一条SQL只包含一行数据的数据文件

......
INSERT INTO `t1` VALUES (1,'1',1,'2019-05-24 15:44:10');
INSERT INTO `t1` VALUES (2,'2',2,'2019-05-24 15:44:10');
INSERT INTO `t1` VALUES (3,'3',3,'2019-05-24 15:44:10');
......

1.3 结论

一次插入多行花费时间0.2秒，一次插入一行花费了31秒，对比效果明显，因此建议有大批量导入时，推荐一条insert语句插入多行数据。

2 关闭自动提交

Autocommit 开启时会为每个插入执行提交。可以在InnoDB导入数据时，关闭自动提交。

SET autocommit=0;
INSERT INTO `t1` VALUES (1,'1',1,'2019-05-24 15:44:10');
INSERT INTO `t1` VALUES (2,'2',2,'2019-05-24 15:44:10');
INSERT INTO `t1` VALUES (3,'3',3,'2019-05-24 15:44:10');
......
COMMIT;

关闭自动提交，让多条 insert 一次提交，可以大大提升导入速度。

2.1 原因分析

与本节前面讲的一次插入多行能提高批量插入速度的原因一样
因为批量导入大部分时间耗费在客户端与服务端通信的时间，所以多条 insert 语句合并提交可以减少客户端与服务端通信的时间，并且合并提交还可以减少数据落盘的次数。

3 参数调整

影响MySQL写入速度的主要两个参数：innodb_flush_log_at_trx_commit、sync_binlog。

mysql> set global innodb_flush_log_at_trx_commit=1;
Query OK, 0 rows affected (0.01 sec)
mysql> set global sync_binlog=1;
Query OK, 0 rows affected (0.00 sec)

3.1 参数解释

MySQL中innodb_flush_log_at_trx_commit的设置
 mysql 参数调优(7) binlog的写入优化 sync_binlog

innodb_flush_log_at_trx_commit：控制重做日志刷新到磁盘的策略，有0 、1和2三种值。

0：master线程每秒把redo log buffer写到操作系统缓存，再刷到磁盘；
1：每次提交事务都将redo log buffer写到操作系统缓存，再刷到磁盘；
2：每次提交事务都将redo log buffer写到操作系统缓存，由操作系统来管理刷盘。

备注：具体原理会在后续的事务这章进行详细描述。

sync_binlog：控制binlog的刷盘时机，可配置0、1或者大于1的数字。

0：二进制日志从不同步到磁盘，依赖OS刷盘机制；
1：二进制日志每次提交都会刷盘；
n(n>1) : 每n次提交落盘一次。

3.2 结论

innodb_flush_log_at_trx_commit设置为0、同时sync_binlog设置为0时
写入数据的速度是最快的。如果对数据库安全性要求不高(比如你的测试环境)，可以尝试都设置为0后再导入数据，能大大提升导入速度。

4 总结

今天一起研究了怎样提高 MySQL 批量导入数据的速度。根据测试，总结了加快批量数据导入有如下方法：

一次插入多行的值；
关闭自动提交，多次插入数据的 SQL 一次提交；
调整参数，innodb_flush_log_at_trx_commit 和 sync_binlog 都设置为0（当然这种情况可能会丢数据）。

四、让order by、group by查询更快

1 order by 原理

主键 id
联合索引 a,b
普通索引 c ```plsql

CREATE TABLE t1 ( / 创建表t1 / id int(11) NOT NULL AUTO_INCREMENT, a int(20) DEFAULT NULL, b int(20) DEFAULT NULL, c int(20) DEFAULT NULL, d datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_a_b (a,b), KEY idx_c (c) ) ENGINE=InnoDB CHARSET=utf8mb4 ;

update t1 set a=1000 where id >9000; / 将id大于9000的行的a字段更新为1000 /


<a name="Bs6xx"></a>
### 1.1 MySQL 的排序方式
按照排序原理分，MySQL 排序方式分两种：
- 通过有序索引直接返回有序数据
- 通过 Filesort 进行的排序
如何确定某条排序的 SQL 所使用的排序方式？<br />**explain分析执行计划，关注 Extra 字段**
- Using index：通过有序索引直接返回有序数据
- Using filesort ：通过 Filesort 进行的排序
<a name="jB3yD"></a>
### 1.2 Filesort 是在内存中还是在磁盘中完成排序的？
而Filesort 又分为在**磁盘中排序和内存排序，**那又如何确定？<br />取决于排序的数据大小和 `sort_buffer_size` 配置的大小。
- “排序的数据大小” < sort_buffer_size: 内存排序
- “排序的数据大小” > sort_buffer_size: 磁盘排序
> **怎么确定使用 Filesort 排序的 SQL 是在内存还是在磁盘中进行的排序操作？**
> 此时就可以使用 trace 进行分析，重点关注 number_of_tmp_files
> - number_of_tmp_files= 0，则表示排序过程没使用临时文件，在内存中就能完成排序；
> - number_of_tmp_files> 0，则表示排序过程中使用了临时文件。
> 
> 如下图，因为 number_of_tmp_files 等于 0，表示未使用临时文件进行排序，所以是内存排序。
> ![image.png](https://cdn.nlark.com/yuque/0/2021/png/1656653/1635864637042-257f62b2-2fbd-40de-adb2-228892252af6.png#clientId=u65755e97-c4c4-4&from=paste&id=ubf46bdd4&margin=%5Bobject%20Object%5D&name=image.png&originHeight=145&originWidth=841&originalType=url&ratio=1&size=12135&status=done&style=none&taskId=u950717a3-a7d0-455d-b94b-f1e246da285)
> 
> 这里解释一下上面一些参数的含义：
> - rows：预计扫描的行数
> - examined_rows：参与排序的行
> - number_of_tmp_files：使用临时文件的个数
> - sort_buffer_size：sort_buffer 的大小
> - sort_mode：排序模式
> 
> 再看一个用到临时文件的例子，如下图，因为 number_of_tmp_files 等于 7，所以表示使用的是磁盘排序。
> 对于 number_of_tmp_files 等于 7 表示该 SQL 将需要排序的数据分为 7 份，然后每份单独排序，再存放在 7 个临时文件中，最后把 7 个临时文件合并成一个大的有序文件。<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/1656653/1635864636889-3b893a22-93ff-4fdc-875e-e792ea5d3ee8.png#clientId=u65755e97-c4c4-4&from=paste&id=u48ffcc86&margin=%5Bobject%20Object%5D&name=image.png&originHeight=146&originWidth=783&originalType=url&ratio=1&size=11848&status=done&style=none&taskId=ub8756f24-819d-42cd-a0a5-1d7a0bec621)
下面再重点介绍 sort_mode。
<a name="LJhRx"></a>
### 1.3Filesort 下的排序模式
Filesort 下的排序模式有三种
- < sort_key, rowid >双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
- < sort_key, additional_fields >单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
- < sort_key, packed_additional_fields >打包数据排序模式：与单路排序相似，区别是将 char 和 varchar 字段存到 sort buffer 中时，更加紧缩。
MySQL 通过比较系统变量 `max_length_for_sort_data `的大小和需要查询的字段总大小来判断使用哪种排序模式。
- 查询字段的总长度< max_length_for_sort_data ，那么使用 < sort_key, additional_fields >排序模式(单路排序)；
- 查询字段的总长度> max_length_for_sort_data，那么使用 <sort_key, rowid> 排序模式(双路排序)。
下面一起来通过实验验证参数 max_length_for_sort_data 对排序模式的影响：
```plsql
set session optimizer_trace="enabled=on",end_markers_in_json=on; 
SET max_length_for_sort_data = 20; 
select a,d from t1 order by d;    /* 查询表t1的id、a、d三个字段的值，按照字段d进行排序  */ 
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

OPTIMIZER_TRACE 结果中排序信息如下图：
发现使用的排序模式是 < sort_key, additional_fields >
怎么让这条 SQL 的排序模式变成呢？下面我们来试验下：
因为 a、d 两个字段的总长度为 12，可以尝试把 max_length_for_sort_data 改为小于 12 的值

知识扩展： MySQL 常见字段类型及所占字节：

字段类型	字节
INT	4
BIGINT	8
DECIMAL(M,D)	M+2
DATETIME	8
TIMESTAMP	4
CHAR(M)	M
VARCHAR(M)	M

set session optimizer_trace="enabled=on",end_markers_in_json=on;
set max_length_for_sort_data = 4;
select a,d from t1 order by d;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

OPTIMIZER_TRACE 结果中排序信息如下图：
发现使用的排序模式确实变成了。
可能讲到这里，你会有个疑问，为什么要添加 max_length_for_sort_data 这个参数让排序使用不同的排序模式呢？限定只用一种排序模式不行吗？
下来，我们一起分析下 max_length_for_sort_data 的重要性。比如下面这条 SQL：

select a,c,d from t1 where a=1000 order by d;

我们先看单路排序的详细过程：

a索引中找到满足条件数据的id，根据id取出整行数据，取出a,c,d 存入到sort_buffer中，重复查找。最后对sort_buffer 中的数据按照字段 d 进行排序

先在a索引中查询，然后回主键索引取出需要的字段，最后排序。

从索引 a 找到第一个满足 a = 1000 条件的主键 id
根据主键 id 取出整行，取出 a、c、d 三个字段的值，存入 sort_buffer 中
从索引 a 找到下一个满足 a = 1000 条件的主键 id
重复步骤 2、3 直到不满足 a = 1000
对 sort_buffer 中的数据按照字段 d 进行排序
返回结果给客户端

我们再看下双路排序的详细过程：

a索引中找到满足条件数据的id，根据id取出整行数据，取出排序 d,id放到 sort buffer中，重复查找。对 d和id根据d排序，最后在根据排序好的数据id，从原表中取出a、c、d字段

先在a索引中查询，然后回主键索引取出排序字段和id，然后排序，再根据排序好的数据id，从主键索引中获取需要的字段

从索引 a 找到第一个满足 a = 1000 的主键 id
根据主键 id 取出整行，把排序字段 d 和主键 id 这两个字段放到 sort buffer 中
从索引 a 取下一个满足 a = 1000 记录的主键 id
重复 3、4 直到不满足 a = 1000
对 sort_buffer 中的字段 d 和主键 id 按照字段 d 进行排序
遍历排序好的 id 和字段 d，按照 id 的值回到原表中取出 a、c、d 三个字段的值返回给客户端

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

如果 MySQL 排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用 rowid 排序算法，可以在 sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。

如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。

所以 MySQL 通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

2 order by 优化

上面我们分析了 order by 的原理，小伙伴们应该会有些优化 order by 的思路了，下面我们就一起来总结 order by 的一些优化技巧。

2.1 添加合适索引

2.1.1 排序字段添加索引

explain select c,id from t1 order by c;

Extra 中显示的就为 Using index，表示使用的是索引排序。如果数据量比较大，显然通过有序索引直接返回有序数据效率更高。
在排序字段上添加索引来优化排序语句。

2.1.2 多个字段排序优化

对 a、b（a、b 两个字段有联合索引）两个字段进行排序：

explain select id,a,b from t1 order by a,b;

发现使用的是索引排序。
多个字段排序的情况，如果要通过添加索引优化，得注意排序字段的顺序与联合索引中列的顺序要一致。

因此，如果多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句。

2.1.3 先等值查询再排序的优化

根据 a=1000 过滤数据在根据 b 字段排序的执行计划（a、b 两个字段有联合索引）：

explain select id,a,b from t1 where a=1000 order by b;

可以在 Extra 字段中看到“Using index”，说明使用的是索引排序。
因此，对于先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化此类排序语句。

2.2 去掉不必要的返回字段

select * from t1 order by a,b;           /* 根据a和b字段排序查出所有字段的值 */ 
select id,a,b from t1 order by a,b;      /* 根据a和b字段排序查出id,a,b字段的值 */

根据执行计划的结果，可以看到，查询所有字段的这条 SQL 是 filesort 排序，而只查 id、a、b 三个字段的 SQL 是 index 排序，为什么查询所有字段会不走索引？

这个例子中，查询所有字段不走索引的原因是：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。

2.3 修改参数

在本节一开始讲 order by 原理的时候，接触到两个跟排序有关的参数：max_length_for_sort_data、sort_buffer_size。

max_length_for_sort_data：如果觉得排序效率比较低，可以适当加大 max_length_for_sort_data 的值，让优化器优先选择全字段排序。当然不能设置过大，可能会导致 CPU 利用率过低或者磁盘 I/O 过高；
sort_buffer_size：适当加大 sort_buffer_size 的值，尽可能让排序在内存中完成。但不能设置过大，可能导致数据库服务器 SWAP。

2.4 几种无法利用索引排序的情况

如果要写出高效率的排序 SQL，几种无法利用索引排序的情况应该熟记于心，在写 SQL 是就应该规避掉。

2.4.1 使用范围查询再排序

对于先等值过滤再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化；
但是如果联合索引中前面的字段使用了范围查询，对后面的字段排序是否能用到索引排序呢？下面我们通过实验验证一下：

explain select id,a,b from t1 where a>9000 order by b;

这里对上面执行计划做下解释：首先条件 a>9000 使用了索引（关注 key 字段对应的值为 idx_a_b）；在 Extra 中，看到了“Using filesort”，表示使用了 filesort 排序，并没有使用索引排序。所以联合索引中前面的字段使用了范围查询，对后面的字段排序使用不了索引排序。

原因是：a、b 两个字段的联合索引，对于单个 a 的值，b 是有序的。而对于 a 字段的范围查询，也就是 a 字段会有多个值，取到 a，b 的值 b 就不一定有序了，因此要额外进行排序。联合索引结果如下图（为了便于理解，该图的值与上面所创建的表 t1 数据不一样）：
如上图所示，对于有 a、b 两个字段联合索引的表，如果对 a 字段范围查询，b 字段整体来看是无序的（如上图 b 的值为：1，2，3，1，2，3······）。

2.4.2 ASC 和 DESC 混合使用将无法使用索引

对联合索引多个字段同时排序时，如果一个是顺序，一个是倒序，则使用不了索引，如下例：

explain select id,a,b from t1 order by a asc,b desc;

3 group by 优化

默认情况，会对 group by 字段排序，因此优化方式与 order by 基本一致，如果目的只是分组而不用排序，可以指定 order by null 禁止排序。

4 总结

首先说到 MySQL 的两种排序方式：

通过有序索引直接返回有序数据
通过 Filesort 进行排序

建议优先考虑索引排序。
而Filesort又分为两种：

内存排序
磁盘文件排序

优先考虑内存排序。
Filesort 有三种排序模式：

< sort_key, rowid >
< sort_key, additional_fields >
< sort_key, packed_additional_fields >

order by 语句的优化，这个是本节的重点：

通过添加合适索引
去掉不必要的返回字段
调整参数：主要是 max_length_for_sort_data 和 sort_buffer_size
避免几种无法利用索引排序的情况

最后说到 group by 语句的优化，如果只要分组，没有排序需求的话，可以加 order by null 禁止排序。

五、换种思路写分页查询

本节会分享两种分页场景的优化技巧：

根据自增且连续主键排序的分页查询(了解)
查询根据非主键字段排序的分页查询
1 根据自增且连续主键排序的分页查询
```
select * from t1 limit 99000,2;
```
该 SQL 表示查询从第 99001开始的两行数据，没添加单独 order by，表示通过主键排序。我们再看表 t1，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 99001开始的两行数据，如下：
```
select * from t1 where id >99000 limit 2;
```
查询的结果是一致的。

**这条 SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，

如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：

主键自增且连续
结果是按照主键排序的

2 查询根据非主键字段排序的分页查询

select * from t1 order by a limit 99000,2;

我们来看下这条 SQL 的执行计划：

发现并没有使用 a 字段的索引（key 字段对应的值为 null），具体原因可以复习第 4 节 2.2 小节：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。

知道不走索引的原因，那么怎么优化呢？
其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录

select * from t1 f inner join (select id from t1 order by a limit 99000,2)g on f.id = g.id;

需要的结果与原 SQL 一致，执行时间 0.02 秒，是原 SQL 执行时间的四分之一，我们再对比优化前后的执行计划：
原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

3 总结

本节讲到了两种分页查询场景的优化：

根据自增且连续主键排序的分页查询优化
查询根据非主键字段排序的分页查询优化

对于其它一些复杂的分页查询，也基本可以按照这两个思路去优化，尤其是第二种优化方式。
第一种优化方式需要主键连续，而主键连续对于一个正常业务表来说可能有点困难，总会有些数据行删除的，但是占用了一个主键 id。

4 问题

对于本节生成的测试表 t1，如果主键是自增的，但是中间有部分记录被删了，也就是主键不连续，下面这条 SQL 应该怎么优化？

select * from t1 limit 99000,2;

执行计划

改写为：

select * from t1 
join (select id from t1 limit 99000,2) a on t1.id = a.id;

执行计划

六、Join语句可以这样优化

测试表
t1表：索引 id、索引 a、10000条数据
t2表：索引 id、索引 a、100条数据

CREATE DATABASE muke; /* 创建测试使用的database，名为muke */
use muke; /* 使用muke这个database */
drop table if exists t1; /* 如果表t1存在则删除表t1 */
CREATE TABLE `t1` ( /* 创建表t1 */
`id` int(11) NOT NULL auto_increment,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
delimiter ;;
create procedure insert_t1() /* 创建存储过程insert_t1 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=10000)do /* 对满足i<=10000的值进行while循环 */
insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入10000条数据到表t1的存储过程insert_t1 */
call insert_t1(); /* 运行存储过程insert_t1 */
drop table if exists t2; /* 如果表t2存在则删除表t2 */
create table t2 like t1; /* 创建表t2，表结构与t1一致 */
insert into t2 select * from t1 limit 100; /* 将表t1的前100行数据导入到t2 */

1 关联查询的算法

MySQL 使用以下两种嵌套循环算法或它们的变体在表之间执行连接

Nested-Loop Join 算法
Block Nested-Loop Join 算法

另外还有一种算法 Batched Key Access，其实算对 Nested-Loop Join 算法的一种优化。

1.1 Nested-Loop Join 算法

一个简单的 Nested-Loop Join(NLJ) 算法一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

如果在被驱动表中这个关联字段没有索引，那么每次取出驱动表的关联字段在被驱动表查找对应的数据时，都会对被驱动表做一次全表扫描，成本是非常高的（比如驱动表数据量是 m，被驱动表数据量是 n，则扫描行数为 m * n ）。

MySQL 在关联字段有索引时，才会使用 NLJ，Nested-Loop Join 的场景（称为：Index Nested-Loop Join）。
没索引，就会使用 Block Nested-Loop Join。

例子（a字段都有索引）：

select * from t1 inner join t2 on t1.a = t2.a;

怎么确定这条 SQL 使用的是 NLJ 算法？
我们先来看下 sql1 的执行计划：
从执行计划中可以看到这些信息：

驱动表是 t2，被驱动表是 t1。原因是：explain 分析 join 语句时，在第一行的就是驱动表；选择 t2 做驱动表的原因：如果没固定连接方式（比如没加 straight_join）优化器会优先选择小表做驱动表。所以使用 inner join 时，前面的表并不一定就是驱动表。
使用了 NLJ。原因是：一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer （*）；则表示使用的 join 算法是 NLJ。

sql1 的大致流程如下：

在t2表中读取一行数据，取出关联的a字段，到t1表（有索引）中去查找，获取结果

从表 t2 中读取一行数据；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。

在这个过程中会读取 t2 表的所有数据，因此这里扫描了 100 行，然后遍历这 100 行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行，这里也扫描了 100 行。因此整个过程扫描了 200 行（因为是有索引树的，所以直接就能扫描到t1表中对应的a字段）。

1.2 Block Nested-Loop Join 算法

Block Nested-Loop Join(BNL) 算法的思想是：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，如果满足 join 条件，则返回结果给客户端。

例子（b字段都没有索引）：

select * from t1 inner join t2 on t1.b = t2.b;

看下执行计划：
在 Extra 发现 Using join buffer (Block Nested Loop)，这个就说明该关联查询使用的是 BNL 算法。
我们再看下 sql2 的执行流程：

把 t2 的所有数据放入到 join_buffer 中
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据

10100：t2表中100条数据扫描后会直接加到 join_buffer，因此t2表扫描次数是100。然后遍历t2的数据，与t1表的每行数据进行判断。因此1表扫描次数是10000。

在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。
下面我们来回答上面提出的一个问题：

如果被驱动表的关联字段没索引，为什么会选择使用 BNL 算法而不继续使用 Nested-Loop Join 呢？

BNL扫描完数据行后直接添加到 **join_buffer**，以后就不用每次都扫描t2表了，而NLJ每次都会进行t2表的扫描，然后然后遍历t1表（因为没有索引，只能全表扫描，挨个判断）

在被驱动表的关联字段没索引的情况下，比如 sql2：

如果使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。

如果使用 BNL，那么磁盘扫描是 100 + 10000=10100 次，在内存中判断 100 * 10000 = 100万次。

显然后者磁盘扫描的次数少很多，因此是更优的选择。因此对于 MySQL 的关联查询，如果被驱动表的关联字段没索引，会使用 BNL 算法。

1.3 Batched Key Access 算法

在学了 NLJ 和 BNL 算法后，你是否有个疑问，如果把 NLJ 与 BNL 两种算法的一些优秀的思想结合，是否可行呢？
比如 NLJ 的关键思想是：被驱动表的关联字段有索引。
而 BNL 的关键思想是：把驱动表的数据批量提交一部分放到 join_buffer 中。
从 MySQL 5.6 开始，确实出现了这种集 NLJ 和 BNL 两种算法优点于一体的新算法：Batched Key Access(BKA)。

其原理是：

将驱动表中相关列放入 join_buffer 中
批量将关联字段的值发送到 Multi-Range Read(MRR) 接口
MRR 通过接收到的值，根据其对应的主键 ID 进行排序，然后再进行数据的读取和操作
返回结果给客户端

这里补充下 MRR 相关知识：

当表很大并且没有存储在缓存中时，使用辅助索引上的范围扫描读取行可能导致对表有很多随机访问。

而 Multi-Range Read 优化的设计思路是：查询辅助索引时，对查询结果先按照主键进行排序，并按照主键排序后的顺序，进行顺序查找，从而减少随机访问磁盘的次数。

使用 MRR 时，explain 输出的 Extra 列显示的是 Using MRR。

optimizer_switch 中 mrr_cost_based 参数的值会影响 MRR。

如果 mrr_cost_based=on，表示优化器尝试在使用和不使用 MRR 之间进行基于成本的选择。

如果 mrr_cost_based=off，表示一直使用 MRR。

更多 MRR 信息请参考官方手册：https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html。

下面尝试开启 BKA ：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

这里对上面几个参数做下解释：

mrr=on 开启 mrr
mrr_cost_based=off 不需要优化器基于成本考虑使用还是不使用 MRR，也就是一直使用 MRR
batched_key_access=on 开启 BKA

然后再看 sql1 的执行计划：

explain select * from t1 inner join t2 on t1.a = t2.a;

在 Extra 字段中发现有 Using join buffer (Batched Key Access)，表示确实变成了 BKA 算法。

2 优化关联查询

通过上面的知识点，我们知道了关联查询的一些算法，下面一起来讨论下关联查询的优化：

2.1 关联字段添加索引

通过上面的内容，我们知道了 BNL、NLJ 和 BKA 的原理，因此让 BNL变成 NLJ 或者 BKA，可以提高 join 的效率。我们来看下面的例子
我们构造出两个算法对于的例子：
Block Nested-Loop Join 的例子：

select * from t1 join t2 on t1.b= t2.b;

需要 0.08 秒。
Index Nested-Loop Join 的例子：

select * from t1 join t2 on t1.a= t2.a;

只需要 0.01 秒。
再对比一下两条 SQL 的执行计划：

前者扫描的行数是 100 和 9963。
后者扫描的行数是 100 和 1。

对比执行时间和执行计划，再结合在本节开始讲解的两种算法的执行流程，很明显 Index Nested-Loop Join 效率更高。

因此建议在被驱动表的关联字段上添加索引，让 BNL变成 NLJ 或者 BKA ，可明显优化关联查询。

2.2 小表做驱动表

小表数据量小，拿到关联字段后，只需要在大表中扫描较少的次数就能获取全部数据。

前面说到，Index Nested-Loop Join 算法会读取驱动表的所有数据，首先扫描的行数是驱动表的总行数（假设为 n），然后遍历这 n 行数据中关联字段的值，根据驱动表中关联字段的值索引扫描被驱动表中的对应行，这里又会扫描 n 行，因此整个过程扫描了 2n 行。当使用 Index Nested-Loop Join 算法时，扫描行数跟驱动表的数据量成正比。所以在写 SQL 时，如果确定被关联字段有索引的情况下，建议用小表做驱动表。

我们来看下以 t2 为驱动表的 SQL：

select * from t2 straight_join t1 on t2.a = t1.a;

这里使用 straight_join 可以固定连接方式，让前面的表为驱动表。
再看下以 t1 为驱动表的 SQL：

select * from t1 straight_join t2 on t1.a = t2.a;

我们对比下两条 SQL 的执行计划：
明显前者扫描的行数少（注意关注 explain 结果的 rows 列），所以建议小表驱动大表。

2.3 临时表

多数情况我们可以通过在被驱动表的关联字段上加索引来让 join 使用 NLJ 或者 BKA，但有时因为某条关联查询只是临时查一次，如果再去添加索引可能会浪费资源，那么有什么办法优化呢？
这里提供一种创建临时表的方法。
我们一起测试下：
比如下面这条关联查询：

select * from t1 join t2 on t1.b= t2.b;

我们看下执行计划：
由于表 t1 和表 t2 的字段 b都没索引，因此使用的是效率比较低的 BNL 算法。
现在用临时表的方法对这条 SQL 进行优化：
首先创建临时表 t1_tmp，表结构与表 t1 一致，只是在关联字段 b 上添加了索引。

CREATE TEMPORARY TABLE `t1_tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (b)
) ENGINE=InnoDB ;

把 t1 表中的数据写入临时表 t1_tmp 中：

insert into t1_tmp select * from t1;

执行 join 语句：

select * from t1_tmp join t2 on t1_tmp.b= t2.b;

我们再看下执行计划：
Extra 没出现 “Block Nested Loop”，说明使用的是 Index Nested-Loop Join，并且扫描行数也大大降低了。
所以当遇到 BNL 的 join 语句，如果不方便在关联字段上添加索引，不妨尝试创建临时表，然后在临时表中的关联字段上添加索引，然后通过临时表来做关联查询。

3 总结

本节首先讲到了 NLJ 、BNL、和 BKA 这几种 join 算法的原理，然后通过认识这些算法，从而引申出 join 语句的一些优化技巧，比如关联字段添加索引、小表做驱动表和创建临时表等方法。

4 问题

哪种情况下，小表做驱动表跟大表做驱动表的执行效率是一样的？

关联字段没有索引时，也就是采用了BNL算法的时候，他们扫描的总行数是驱动表行数 * 被驱动表行数，不管谁驱动谁，最终结果都是一样的，所以执行效率也是一样的。

七、为何count(*)这么慢?

1 重新认识 count()

1.1 count(a) 和 count(*) 的区别

当 count() 统计某一列时，比如 count(a)，a 表示列名，是不统计 null 的。
而 count(*) 无论是否包含空值，都会统计。

1.2 MyISAM 引擎和 InnoDB 引擎 count(*) 的区别

对于 MyISAM 引擎，如果没有 where 子句，也没检索其它列，那么 count() 将会非常快。因为 MyISAM 引擎会把表的总行数存在磁盘上。
首先我们看下对 t2 表（存储引擎为 MyISAM）不带 where 子句做 count() 的执行计划：

explain select count(*) from t2;

在 Extra 字段发现 “Select tables optimized away” 关键字，表示是从 MyISAM 引擎维护的准确行数上获取到的统计值。
而 InnoDB 并不会保留表中的行数，因为并发事务可能同时读取到不同的行数。所以执行 count() 时都是临时去计算的，会比 MyISAM 引擎慢很多。
我们看下对 t1 表（存储引擎为 InnoDB）执行 count() 的执行计划：
发现使用的是 b 字段的索引 idx_b，并且扫描行数是10109，表示会遍历 b 字段的索引树去计算表的总量。

对比 MyISAM 引擎和 InnoDB 引擎 count(*) 的区别，可以知道：

MyISAM 会维护表的总行数，放在磁盘中，如果有 count(*) 的需求，直接返回这个数据
但是 InnoDB 就会去遍历普通索引树，计算表数据总量

1.3 MySQL 5.7.18 前后 count(*) 的区别

在 MySQL 5.7.18 之前，InnoDB 通过扫描聚簇索引来处理 count(*) 语句。

从 MySQL 5.7.18 开始，通过遍历最小的可用二级索引来处理 count() 语句。如果不存在二级索引，则扫描聚簇索引。但是，如果索引记录不完全在缓存池中的话，处理 count() 也是比较久的。
新版本为什么会使用二级索引来处理 count(*) 语句呢？

原因是 InnoDB 二级索引树的叶子节点上存放的是主键，而主键索引树的叶子节点上存放的是整行数据，所以二级索引树比主键索引树小。因此优化器基于成本的考虑，优先选择的是二级索引。所以 count(主键) 其实没 count (*) 快。

1.4 count(1) 比 count(*) 快吗？

在前面我们知道 count() 无论是否包含空值，所有结果都会统计。
而 count(1)中的 1 是恒真表达式，因此也会统计所有结果。
所以 count(1) 和 count() 统计结果没差别。
我们来对比 count(1) 和 count( ) 的执行计划：
执行计划一样，所以 count(1) 并不比 count() 快。

2 哪些方法可以加快 count()

show table status：能快速获取结果，但是结果不准确；
用 Redis 做计数器：能快速获取结果，比 show table status 结果准确，但是并发场景计数可能不准确；
增加 InnoDB 计数表：能快速获取结果，利用了事务特性确保了计数的准确，也是比较推荐的方法。

SQL优化

一、快速学会分析SQL执行效率

1 定位慢 SQL

1.1 通过慢查询日志

1.2 通过 show processlist;

2 使用 explain 分析慢查询

2.1 select_type

2.2 type

2.3 Extra

3 show profile 分析慢查询

3.1 确定是否支持 profile

3.2 查看 profiling 是否关闭的

3.3 通过 set 开启 profile

3.4 执行 SQL 语句

3.5 确定 SQL 的 query id

3.6 查询 SQL 执行详情

4 trace 分析 SQL 优化器

5 总结

二、条件字段有索引，为什么查询也这么慢?

1 函数操作

1.1 不走索引原因：

1.2 函数操作的 SQL 优化

2 隐式转换

2.1 不走索引原因

2.2 隐式转换的 SQL 优化

3 模糊查询

3.1 模糊查询优化建议

4 范围查询

4.1 优化范围查询

5 计算操作

5.1 计算操作的 SQL 优化

6 总结

三、如何优化数据导入

1 一次插入多行的值

1.1 一条SQL只包含多行数据的数据文件

1.2 一条SQL只包含一行数据的数据文件

1.3 结论

2 关闭自动提交

2.1 原因分析

3 参数调整

3.1 参数解释

3.2 结论

4 总结

四、让order by、group by查询更快

1 order by 原理

2 order by 优化

2.1 添加合适索引

2.1.1 排序字段添加索引

2.1.2 多个字段排序优化

2.1.3 先等值查询再排序的优化

2.2 去掉不必要的返回字段

2.3 修改参数

2.4 几种无法利用索引排序的情况

2.4.1 使用范围查询再排序

2.4.2 ASC 和 DESC 混合使用将无法使用索引

3 group by 优化

4 总结

五、换种思路写分页查询

1 根据自增且连续主键排序的分页查询

2 查询根据非主键字段排序的分页查询

3 总结

4 问题

六、Join语句可以这样优化

1 关联查询的算法

1.1 Nested-Loop Join 算法

1.2 Block Nested-Loop Join 算法

1.3 Batched Key Access 算法

2 优化关联查询

2.1 关联字段添加索引

2.2 小表做驱动表

2.3 临时表

3 总结

4 问题

七、为何count(*)这么慢?

1 重新认识 count()

1.1 count(a) 和 count(*) 的区别

1.2 MyISAM 引擎和 InnoDB 引擎 count(*) 的区别

1.3 MySQL 5.7.18 前后 count(*) 的区别

1.4 count(1) 比 count(*) 快吗？

2 哪些方法可以加快 count()