《一线数据库工程师带你深入理解MySQL》学习笔记

职业规划 慕课专栏
学习笔记
发布日期:   2021-03-11
更新日期:   2025-03-21
文章字数:   39.9k
阅读时长:   158 分

第1章 SQL优化

01 开篇词

3 提升 MySQL 水平的方式

如果你没有 MySQL 的基础，建议可以看下面两本书籍，看完之后，可以简单处理一些优化：

  • 《MySQL 必知必会》：主要讲 SQL 的写法；
  • 《深入浅出 MySQL》：比较全面的讲解了 MySQL 的基础知识，也涉及了一些优化。

如果已经对 MySQL 比较熟悉了，可以看下面的书籍，你会对索引和锁以及事务等有全新的看法：

  • 《高性能 MySQL》：里面讲了很多 MySQL 优化技巧；
  • 《MySQL 技术内幕》：讲解了很多 MySQL 原理，强力推荐给想深入学习 MySQL 的同学；
  • 《MySQL 内核：InnoDB 存储引擎》：想深入研究 MySQL 内核及原理的可以看看；
  • 《MySQL 运维内参》：对 MySQL 源码感兴趣，可以入手；
  • 《MySQL Internals Manual》https://dev.mysql.com/doc/internals/en/ ；
  • 《MySQL 5.7 Reference Manual》https://dev.mysql.com/doc/refman/5.7/en/ 。

4 学习本专栏前的提醒

本专栏默认使用的 MySQL 版本为 5.7.21，事务隔离级别为 RR，表的存储引擎为 InnoDB。

02 快速学会分析SQL执行效率（上）

1 定位慢 SQL

1.1 通过慢查询日志

MySQL 的慢查询日志用来记录在 MySQL 中响应时间超过参数 long_query_time（单位秒，默认值 10）设置的值并且扫描记录数不小于 min_examined_row_limit（默认值0）的语句。

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

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

使用慢查询日志，一般分为三步：

  1. 开启慢查询日志

    set global slow_query_log = on;

  2. 设置慢查询阀值

    set global long_query_time = 1;

  3. 确定慢查询日志路径

    show global variables like "datadir"; /* 慢查询日志的目录 */
show global variables like "slow_query_log_file";  /* 慢查询日志的文件名 */

1.2 通过 show processlist

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

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

2 使用 explain 分析慢查询

为了便于理解，先创建两张测试表

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`),
    KEY `idx_b` (`b`)
) 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<=1000)do                  /* 对满足i<=1000的值进行while循环 */
        insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
        set i=i+1;                      /* 将i加1 */
    end while;
end;;
delimiter ;                 /* 创建批量写入1000条数据到表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;   /* 将表t1的数据导入到t2 */

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

explain select * from t1 where b=100;

得到

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_b         | idx_b | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

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

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

这里介绍几个比较重要列常包含的值：

2.1 select_type

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

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 全表扫描

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

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;

03 快速学会分析SQL执行效率（下）

1 show profile 分析慢查询

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

可以通过配置参数 profiling = 1 来启用 SQL 分析。该参数可以在全局和 session 级别来设置。

使用 profile 分析慢查询的大致步骤如下：

1.1 确定是否支持 profile

select @@have_profiling;

1.2 查看 profiling 是否关闭的

select @@profiling;

1.3 通过 set 开启 profile

set profiling=1;

1.4 执行 SQL 语句

select * from t1 where b=1000;

1.5 确定 SQL 的 query id

show profiles;

结果

+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00982500 | select * from t1 where b=1000 |
+----------+------------+-------------------------------+

1.6 查询 SQL 执行详情

show profile for query 1;

结果

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.001746 |
| Executing hook on transaction  | 0.000089 |
| starting                       | 0.000107 |
| checking permissions           | 0.000120 |
| Opening tables                 | 0.001553 |
| init                           | 0.000254 |
| System lock                    | 0.000276 |
| optimizing                     | 0.000291 |
| statistics                     | 0.002640 |
| preparing                      | 0.000235 |
| executing                      | 0.001847 |
| end                            | 0.000133 |
| query end                      | 0.000031 |
| waiting for handler commit     | 0.000090 |
| closing tables                 | 0.000110 |
| freeing items                  | 0.000242 |
| cleaning up                    | 0.000062 |
+--------------------------------+----------+

2 trace 分析 SQL 优化器

从前面学到了 explain 可以查看 SQL 执行计划，但是无法知道它为什么做这个决策，如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序模式，可以使用 trace 查看优化器如何选择执行计划。

如果需要使用，先开启 trace，设置格式为 JSON，再执行需要分析的 SQL，最后查看 trace 分析结果（在 information_schema.OPTIMIZER_TRACE 中）。

开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。

下面一起来看下 trace 的使用方法。使用讲解 explain 时创建的表t1做实验。

首先构造如下 SQL (表示取出表 t1 中 a 的值大于 900 并且 b 的值大于 910 的数据，然后按照 a 字段排序)：

select * from t1 where a >900 and b > 910 order  by a;

我们首先用 explain 分析下执行计划：

mysql> explain select * from t1 where a >900 and b > 910 order  by a;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_a,idx_b   | idx_b | 5       | NULL |   90 |    10.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+

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

  1. 开启 trace

    /* optimizer_trace="enabled=on" 表示开启 trace；end_markers_in_json=on 表示 JSON 输出开启结束标记 */
set session optimizer_trace="enabled=on",end_markers_in_json=on;

  2. 执行需要分析的 SQL

    select * from t1 where a >900 and b > 910 order  by a;

  3. 查看 trace 分析结果

    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_buffer 的大小
                            "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)
------------------------------------------------

  4. 关闭trace

    set session optimizer_trace="enabled=off";

    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_key, additional_fields> ，表示使用的是单路排序，即一次性取出满足条件行的所有字段，然后在 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：使用索引的统计数值，进行估算；特点是速度快，但是值不一定准确。

3 总结

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

  • explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
  • profile：可以清楚了解到SQL到底慢在哪个环节；
  • trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。

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

下面会讲解几种有索引但是查询不走索引导致查询慢的场景。

1 函数操作

1.1 验证对条件字段做函数操作是否能走索引

首先创建测试表，建表及数据写入语句如下：

use muke;                       /* 使用muke这个database */

drop table if exists t1;        /* 如果表t1存在则删除表t1 */

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字段改为与其它行都不一样的数据，以便后面实验使用 */

对于上面创建的测试表，比如要查询测试表 t1 单独某一天的所有数据，SQL如下：

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

可以使用第 2 节学习的 explain 来分析这条SQL的执行计划，分析结果如下：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10302 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

查看图中的执行计划，type 为 ALL，key 字段结果为 NULL，因此知道该 SQL 是没走索引的全表扫描。

原因：对条件字段做函数操作走不了索引。

1.2 对条件字段做函数操作不走索引的原因

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

c字段普通索引的B+索引树

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

1.3 函数操作的 SQL 优化

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

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

再用 explain 分析下执行计划的结果：

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_c         | idx_c | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+

2 隐式转换

什么时隐式转换？

当操作符与不同类型的操作对象一起使用时，就会发生类型转换以使操作兼容。某些转换是隐式的。

关于隐式转换详情请参考MySQL官方手册

隐式转换估计是很多 MySQL 使用者踩过的坑，比如联系方式字段。由于有时电话号码带加、减等特殊字符，有时需要以 0 开头，因此一般设计表时会使用 varchar 类型存储，并且会经常做为条件来查询数据，所以会添加索引。

而有时遇到需要按照手机号码条件（比如 11111111111）去查询数据时，因为查询者看到条件是一串数字，而忽视表中对应手机号字段是 varchar 类型，因此写出了如下不合理的SQL：

select user_name,tele_phone from user_info where tele_phone =11111111111; /* SQL 1 */

2.2 验证隐式转换是否能走索引

实验过程分为：先创建测试表并写入数据；测试隐式转换的查询并查看执行计划；测试正常查询，再查看执行计划。

比如我们要查询 a 字段等于 1000 的值，SQL如下：

select * from t1 where a=1000;

看下 explain 结果：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_a         | NULL | NULL    | NULL | 10302 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

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

2.3 不走索引的原因

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

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

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

2.4 隐式转换的 SQL 优化

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

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

结果：

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a         | idx_a | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

通过 type 这列，可以看到是 ref（基于普通索引的等值查询，比 ALL 性能好很多），通过key这列，可以看到已经走了 a 字段的索引，通过rows这列可以看到通过索引查询后就扫描了一行。

3 模糊查询

3.1 分析模糊查询

很多时候我们想根据某个字段的某几个关键字查询数据，比如会有如下 SQL：

select * from t1 where a like '%1111%';

实际这种情况无法走索引，看下执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10302 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

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

3.2 模糊查询优化建议

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

select * from t1 where a like '1111%';

这种写法是可以用到索引的，explain分析如下：

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_a         | idx_a | 83      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+

如果条件只知道中间的值，需要模糊查询去查，那就建议使用ElasticSearch或其它搜索服务器。

4 范围查询

4.1 构造不能使用索引的范围查询

select * from t1 where b>=1 and b <=100000;

看下这条 SQL 的执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_b         | NULL | NULL    | NULL | 10302 |    97.07 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

发现并不能走b字段的索引。

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

4.2 优化范围查询

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

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

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

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_b         | idx_b | 5       | NULL | 1000 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+

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

经验分享：

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

5 计算操作

5.1 查询条件进行计算操作的 SQL 执行效率

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

select * from t1 where b-1 =1000;

查看执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10302 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

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

5.2 计算操作的 SQL 优化

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

select * from t1 where b =1000 + 1;

查看执行计划：

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_b         | idx_b | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

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

经验分享：

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

6 总结

在写 SQL 时应该注意这些点：

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

本节涉及到的一些SQL优化如下图：

MySQL条件字段有索引但是不走索引的场景

05 如何优化数据导入？

1 一次插入多行的值

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

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

可发现大部分时间耗费在客户端与服务端通信的时间，因此可以使用 insert 包含多个值来减少客户端和服务器之间的通信。

2 关闭自动提交

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

3 参数调整

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

3.1 参数解释

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次提交落盘一次。

06 让order by、group by查询更快

1 order by 原理

在优化 order by 语句之前，需要先了解 MySQL 中排序的相关知识点和原理，为了方便讲解过程举例说明，首先创建一张测试表，建表及数据写入语句如下：

use muke;                 /* 使用muke这个database */

drop table if exists t1;  /* 如果表t1存在则删除表t1 */

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 ;

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,c) values(i,i,i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
        set i=i+1;                       /* 将i加1 */
    end while;
end;;
delimiter ;
call insert_t1();               /* 运行存储过程insert_t1 */

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

1.1 MySQL 的排序方式

按照排序原理分，MySQL 排序方式分两种：

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

怎么确定某条排序的 SQL 所使用的排序方式？

使用 explain 来查看该排序 SQL 的执行计划，重点关注 Extra 字段：

如果该字段里显示是 Using index，则表示是通过有序索引直接返回有序数据。

如果该字段里显示是 Using filesort，则表示该 SQL 是通过 Filesort 进行的排序。

1.2 Filesort 是在内存中还是在磁盘中完成排序的？

MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。

  • 如果 “排序的数据大小” < sort_buffer_size: 内存排序
  • 如果 “排序的数据大小” > sort_buffer_size: 磁盘排序

怎么确定使用 Filesort 排序的 SQL 是在内存还是在磁盘中进行的排序操作？

此时就可以使用 trace 进行分析，重点关注 number_of_tmp_files，如果等于 0，则表示排序过程没使用临时文件，在内存中就能完成排序；如果大于0，则表示排序过程中使用了临时文件。

1.3 Filesort 下的排序模式

Filesort 下的排序模式有三种，具体介绍如下：（参考《MySQL 5.7 Reference Manual》8.2.1.14 ORDER BY Optimization）

  • < 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 这个参数让排序使用不同的排序模式呢？

接下来，我们一起分析下 max_length_for_sort_data 的重要性。比如下面这条 SQL：

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

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

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

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

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

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

2 order by 优化

2.1 添加合适索引

2.1.1 排序字段添加索引

首先我们看下对 d 字段（没有索引）进行排序的执行计划：

explain select d,id from t1 order by d;

结果：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10236 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+

发现使用的是 filesort（关注 Extra 字段）。

再看些对 c 字段（有索引）进行排序的执行计划：

explain select c,id from t1 order by c;

结果：

+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c | 5       | NULL | 10236 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+

可以看到，根据有索引的字段排序，在 Extra 中显示的就为 Using index，表示使用的是索引排序。

因此可以在排序字段上添加索引来优化排序语句。

2.1.2 多个字段排序优化

有时面对的需求是要对多个字段进行排序，而这种情况应该怎么优化或者设计索引呢？首先看下面例子：

对 a、c 两个字段进行排序的执行计划：

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

结果：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10236 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+

观察 Extra 字段，发现使用的是 filesort。

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

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

结果：

+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_a_b | 10      | NULL | 10236 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+

发现使用的是索引排序。

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

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

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

我们更多的情况是会先根据某个字段条件查出一部分数据，然后再排序，而这类 SQL 应该如果优化呢？看下面的实验：

表 t1中，根据 a=1000 过滤数据再根据 d 字段排序的执行计划如下：

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

结果：

+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a_b       | idx_a_b | 5       | const | 1001 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+

可以在 Extra 字段中看到 “Using filesort”，说明使用的是 filesort 排序。

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

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

结果：

+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a_b       | idx_a_b | 5       | const | 1001 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

可以在 Extra 字段中看到”Using index”，说明使用的是索引排序。

因此，对于先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化此类排序语句。

2.2 去掉不必要的返回字段

有时，我们其实并不需要查询出所有字段，但是可能因为习惯问题，就写成查所有字段的数据了。我们看下下面两条 SQL 的执行计划：

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

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

结果：

mysql> explain select * from t1 order by a,b;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10236 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,a,b from t1 order by a,b;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_a_b | 10      | NULL | 10236 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

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 几种无法利用索引排序的情况

2.4.1 使用范围查询再排序

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

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

结果：

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_a_b       | idx_a_b | 5       | NULL |    1 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+

这里对上面执行计划做下解释：首先条件 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;

结果：

+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_a_b | 10      | NULL | 10236 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+

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 禁止排序。

07 换种思路写分页查询

很多时候，业务上会有分页操作的需求，对应的 SQL 类似下面这条：

select a,b,c from t1 limit 10000,10;

表示从表 t1 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。本节内容就一起研究下，是否有办法去优化分页查询。

为了方便验证，首先创建测试表并写入数据：

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`),
    KEY `idx_b` (`b`)
) 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<=100000的值进行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 */

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

首先来看一个根据自增且连续主键排序的分页查询的例子：

select * from t1 limit 9000,2;

该 SQL 表示查询从第 9001 开始的两行数据，没添加单独 order by，表示通过主键排序。我们再看表 t1，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 9001开始的两行数据，如下：

select * from t1 where id >9000 limit 2;

查询的结果是一致的。我们再对比一下执行计划：

mysql> explain select * from t1 limit 9000,2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9963 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where id >9000 limit 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1000 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

原 SQL 中 key 字段为 NULL，表示未走索引，rows 显示 9963，表示扫描的行数 9963行；

改写后的 SQL key 字段为 PRIMARY，表示走了主键索引，扫描了1000行。

显然改写后的 SQL 执行效率更高。

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

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

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

再看一个根据非主键字段排序的分页查询，SQL 如下：

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

看下这条 SQL 的执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9963 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

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

知道不走索引的原因，那么怎么优化呢？

其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下（这里参考了《深入浅出 MySQL》18.4.7 优化分页查询）：

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

看下这条 SQL 的执行计划：

+----+-------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 9002 |   100.00 | NULL        |
|  1 | PRIMARY     | f          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | g.id |    1 |   100.00 | NULL        |
|  2 | DERIVED     | t1         | NULL       | index  | NULL          | idx_a   | 5       | NULL | 9002 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+

原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

08 Join语句可以这样优化

为了方便理解，首先创建测试表并写入测试数据，语句如下：

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 */

SQL连接可视化

图片来源 Visual Representation of SQL Joins

1 关联查询的算法

MySQL 使用以下两种嵌套循环算法或它们的变体在表之间执行连接（参考 《MySQL 5.7 Reference Manual》8.2.1.6 Nested-Loop Join Algorithms）：

  • 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，如果没索引，就会使用 Block Nested-Loop Join，等下会细说这个算法。我们先来看下在有索引情况的情况下，使用 Nested-Loop Join 的场景（称为：Index Nested-Loop Join）。

因为 MySQL 在关联字段有索引时，才会使用 NLJ，因此本节后面的内容所用到的 NLJ 都表示 Index Nested-Loop Join。

如下例：

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

表 t1 和表 t2 中的 a 字段都有索引。

怎么确定这条 SQL 使用的是 NLJ 算法？

我们先来看下 sql1 的执行计划：

+----+-------------+-------+------------+------+---------------+-------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx_a         | NULL  | NULL    | NULL      |  100 |   100.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a         | idx_a | 5       | muke.t2.a |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-------+---------+-----------+------+----------+-------------+

从执行计划中可以看到这些信息：

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

在这个过程中会读取 t2 表的所有数据，因此这里扫描了 100 行，然后遍历这 100 行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行，这里也扫描了 100 行。因此整个过程扫描了 200 行。

在前面，我们有说到：如果被驱动表的关联字段没索引，就会使用 Block Nested-Loop Join(简称：BNL)，为什么会选择使用 BNL 算法而不继续使用 Nested-Loop Join呢？下面就一起分析下：

1.2 Block Nested-Loop Join 算法

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

我们一起看看下面这条 SQL 语句：

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

表 t1 和表 t2 中的 b 字段都没有索引

看下执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   100 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10225 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

在 Extra 发现 Using join buffer (Block Nested Loop)，这个就说明该关联查询使用的是 BNL 算法。

我们再看下 sql2 的执行流程：

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

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

下面我们来回答上面提出的一个问题：

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

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

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

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

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

1.3 Batched Key Access 算法

NLJ 的关键思想是：被驱动表的关联字段有索引。

BNL 的关键思想是：把驱动表的数据批量提交一部分放到 join_buffer 中。

从 MySQL 5.6 开始，确实出现了这种集 NLJ 和 BNL 两种算法优点于一体的新算法：Batched Key Access(BKA)。

其原理是：

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

这里补充下 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;

结果：

+----+-------------+-------+------------+------+---------------+-------+---------+-----------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref       | rows | filtered | Extra                                  |
+----+-------------+-------+------------+------+---------------+-------+---------+-----------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx_a         | NULL  | NULL    | NULL      |  100 |   100.00 | Using where                            |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a         | idx_a | 5       | muke.t2.a |    1 |   100.00 | Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+------+---------------+-------+---------+-----------+------+----------+----------------------------------------+

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

2 优化关联查询

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

2.1 关联字段添加索引

通过上面的内容，我们知道了 BNL、NLJ 和 BKA 的原理，因此建议在被驱动表的关联字段上添加索引，让 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 可以固定连接方式，让前面的表为驱动表。

2.3 临时表

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

这里提供一种创建临时表的方法。比如下面这条关联查询：

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

看下执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   100 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10225 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

由于表 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;

我们再看下执行计划：

+----+-------------+--------+------------+------+---------------+-------+---------+-----------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key   | key_len | ref       | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | t2     | NULL       | ALL  | NULL          | NULL  | NULL    | NULL      |  100 |   100.00 | Using where |
|  1 | SIMPLE      | t1_tmp | NULL       | ref  | idx_b         | idx_b | 5       | muke.t2.b |    1 |   100.00 | NULL        |
+----+-------------+--------+------------+------+---------------+-------+---------+-----------+------+----------+-------------+

Extra 没出现 “Block Nested Loop”，说明使用的是 Index Nested-Loop Join，并且扫描行数也大大降低了。

所以当遇到 BNL 的 join 语句，如果不方便在关联字段上添加索引，不妨尝试创建临时表，然后在临时表中的关联字段上添加索引，然后通过临时表来做关联查询。

09 为何count(*)这么慢?

老规矩，先创建测试表并写入数据。

use muke; /* 使用muke这个database */
drop table if exists t1; /* 如果表t1存在则删除表t1 */

CREATE TABLE `t1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) DEFAULT NULL,
    `b` int(11) NOT NULL,
    `c` int(11) DEFAULT NULL,
    `d` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_a` (`a`),
    KEY `idx_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=4 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,c,d) values(i,i,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 */

insert into t1(a,b,c,d) values (null,10001,10001,10001),(10002,10002,10002,10002);

drop table if exists t2; /* 如果表t2存在则删除表t2 */
create table t2 like t1; /* 创建表t2，表结构与t1一致 */
alter table t2 engine =myisam; /* 把t2表改为MyISAM存储引擎 */
insert into t2 select * from t1;  /* 把t1表的数据转到t2表 */

CREATE TABLE `t3` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) DEFAULT NULL,
    `b` int(11) NOT NULL,
    `c` int(11) DEFAULT NULL,
    `d` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  CHARSET=utf8mb4;
insert into t3 select * from t1;  /* 把t1表的数据转到t3表 */

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;

结果

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

在 Extra 字段发现 “Select tables optimized away” 关键字，表示是从 MyISAM 引擎维护的准确行数上获取到的统计值。

而 InnoDB 并不会保留表中的行数，因为并发事务可能同时读取到不同的行数。所以执行 count(*) 时都是临时去计算的，会比 MyISAM 引擎慢很多。

我们看下对 t1 表（存储引擎为 InnoDB）执行 count(*) 的执行计划：

mysql> explain select count(*) from t1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_b | 4       | NULL | 10147 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+

发现使用的是 b 字段的索引 idx_b，并且扫描行数是10109，表示会遍历 b 字段的索引树去计算表的总量。

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

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

在上面这个例子，InnoDB 表 t1 在执行 count(*) 时，为什么会走 b 字段的索引而不是走主键索引呢？下面我们分析下：

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(* ) 的执行计划：

mysql> explain select count(1) from t1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_b | 4       | NULL | 10147 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+

mysql> explain select count(*) from t1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_b | 4       | NULL | 10147 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+

执行计划一样，所以 count(1) 并不比 count(*) 快。

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

2.1 show table status

有时，我们只需要知道某张表的大概数据量，这种情况就可以使用 show table status，具体用法如下：

show table status like 't1'\G
...
Rows: 10147
...

Rows 这列就表示这张表的行数。这种方式获取 InnoDB 表的行数非常快。

但是，这个值是个估算值，可能与实际值相差 40% 到 50%。（对于 Rows 这个字段更详细的解释，可以参考官方手册：https://dev.mysql.com/doc/refman/5.7/en/show-table-status.html）

2.2 用 Redis 做计数器

2.3 增加计数表

增加计数表

第2章 MySQL索引

10 为什么添加索引能提高查询速度?

为了便于理解 MySQL 的索引，我们先了解一些与索引相关的算法。

1 跟索引相关的一些算法

对于 MySQL 而言，使用最频繁的就是 B+ 树索引，所以我们必须要知道 B+ 树的结构，而 B+ 树是借鉴了二分查找法、二叉查找树、平衡二叉树、B 树的一些思想构建的。因此我们首先通过了解这些算法，来一层一层拨开 B+ 树的神秘面纱。

1.1 二分查找法

二分查找法的查找过程是：将记录按顺序排列，查找时先以有序列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将查询范围缩小为左半部分；如果要找的元素值大于该中点元素，则将查询范围缩小为右半部分。以此类推，直到查到需要的值。
二分查找法

1.2 二叉查找树

二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值，并且每个节点最多只有两颗子树。
二叉查找树

1.3 平衡二叉树

平衡二叉树的定义：满足二叉查找树的定义，另外必须满足任何节点的两个子树的高度差最大为 1。
平衡二叉树

1.4 B 树

B 树可以理解为一个节点可以拥有多于 2 个子节点的平衡多叉查找树。

B 树中同一键值不会出现多次，要么在叶子节点，要么在内节点上。

比如用 1、2、3、5、6、7、9 这些数字构建一个 B 树结构，其图形如下：
B树

与平衡二叉树相比，B 树利用多个分支（平衡二叉树只有两个分支）节点，减少获取记录时所经历的节点数。

B 树也是有缺点的，因为每个节点都包含 key 值和 data 值，因此如果 data 比较大时，每一页存储的 key 会比较少；当数据比较多时，同样会有：”要经历多层节点才能查询在叶子节点的数据”的问题。这时，B+ 树站了出来。

1.5 B+ 树

B+ 树是 B 树的变体，定义基本与 B 树一致，与 B 树的不同点：

  • 所有叶子节点中包含了全部关键字的信息
  • 各叶子节点用指针进行连接
  • 非叶子节点上只存储 key 的信息，这样相对 B 树，可以增加每一页中存储 key 的数量。
  • B 树是纵向扩展，最终变成一个”瘦高个”，而 B+ 树是横向扩展的，最终会变成一个”矮胖子”（这里参考了《MySQL 运维内参》第 8 节 B+ 树及 B 树的区别中的比喻）。

在 B+ 树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上。B+ 树中的 B 不是代表二叉(binary) 而是代表（balance），B+ 树并不是一个二叉树。

还是根据前面提到的这组数字（1、2、3、5、6、7、9）举例，它的结构如下：

Bplus树

与 1.4 中 B 树的结构最大的区别就是：

它的键一定会出现在叶子节点上，同时也有可能在非叶子节点中重复出现。而 B 树中同一键值不会出现多次。

2 B+ 树索引

B+ 树索引就是基于本节前面介绍的 B+ 树发展而来的。在数据库中，B+ 树的高度一般都在 2 ~ 4 层，所以查找某一行数据最多只需要 2 到 4 次 IO。而没索引的情况，需要逐行扫描，明显效率低很多，这也就是为什么添加索引能提高查询速度。

B+ 树索引并不能找到一个给定键值的具体行，B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到缓冲池（buffer pool）中，在内存中通过二分查找法进行查找，得到需要的数据。

InnoDB 中 B+ 树索引分为聚集索引和辅助索引，我们再继续了解这两种索引的特点。

为了方便理解，我们先创建一张测试表并写入数据：

use muke; /* 使用muke这个database */
drop table if exists t8; /* 如果表t1存在则删除表t1 */
CREATE TABLE `t8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` char(2) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into t8(a,b) values (1,'a'),(2,'b'),(3,'c'),(5,'e'),(6,'f'),(7,'g'),(9,'i');

2.1 聚集索引

InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+ 树，它的叶子节点存放的是整行数据。

InnoDB 的主键一定是聚集索引。如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，也有可能是 row id。

由于实际的数据页只能按照一颗 B+ 树进行排序，因此每张表只能有一个聚集索引（TokuDB 引擎除外）。查询优化器倾向于采用聚集索引，因为聚集索引能够在 B+ 树索引的叶子节点上直接找到数据。

聚集索引对于主键的排序查找和范围查找速度非常快。

对于刚刚创建好的测试表 t8的聚集索引的大致结构如下：

表t8的聚集索引的大致结构

两点关键信息：

  • 根据主键值创建了 B+ 树结构
  • 每个叶子节点包含了整行数据

2.2 辅助索引

我们现在知道了聚集索引的叶子节点存放了整行数据，而 InnoDB 存储引擎辅助索引的叶子节点并不会放整行数据，而存放的是键值和主键 ID。

当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引树查找到对应记录的主键，然后通过主键索引来找到对应的行数据。

比如一颗高度为 3 的辅助索引树中查找数据，那需要对这颗辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度也为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此获取数据一共需要6次逻辑 IO 访问。

我们继续拿表 t8 分析，它的辅助索引 idx_a 结构如下：

表t8的辅助索引idx_a的大致结构

上图中两点关键点需要注意：

  • 根据 a 字段的值创建了 B+ 树结构
  • 每个叶子节点保存的是 a 字段自己的键值和主键 ID

对于表 t8，比如有下面这条查询语句：

select * from t8 where a=3;

它先通过 a 字段上的索引树，得到主键 id 为 3，再到 id 的聚集索引树上找到对应的行数据。

而下面这条 SQL:

select * from t8 where id=3;

查询到的结果是一样的，而执行过程则只需要搜索 id 的聚集索引树。我们能看出辅助索引的查询比主键查询多扫描一颗索引树，所以，我们应该尽量使用主键做为条件进行查询。

11 哪些情况需要添加索引？

首先创建测试表并写入数据：

use muke; /* 使用muke这个database */
drop table if exists t9_1; /* 如果表t9_1存在则删除表t9_1 */
CREATE TABLE `t9_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b_c` (`b`,`c`)
) ENGINE=InnoDB  CHARSET=utf8mb4;
drop procedure if exists insert_t9_1; /* 如果存在存储过程insert_t9_1，则删除 */
delimiter ;;
create procedure insert_t9_1() /* 创建存储过程insert_t9_1 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=100000)do /* 对满足i<=100000的值进行while循环 */
insert into t9_1(a,b,c,d) values(i,i,i,i); /* 写入表t9_1中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入100000条数据到表t9_1的存储过程insert_t9_1 */
call insert_t9_1(); /* 运行存储过程insert_t9_1 */

insert into t9_1(a,b,c,d) select a,b,c,d from t9_1;
insert into t9_1(a,b,c,d) select a,b,c,d from t9_1;
insert into t9_1(a,b,c,d) select a,b,c,d from t9_1;
insert into t9_1(a,b,c,d) select a,b,c,d from t9_1;
insert into t9_1(a,b,c,d) select a,b,c,d from t9_1;
/*  把t9_1的数据量扩大到160万 */

目前比较常见需要创建索引的场景有：数据检索时在条件字段添加索引、聚合函数对聚合字段添加索引、对排序字段添加索引、为了防止回表添加索引、关联查询在关联字段添加索引等。我们就一一分析这些需要创建索引的场景：

1 数据检索

用上面的表 t9_1 做测试，首先把没有索引的字段 d 作为条件进行查询：

select * from t9_1 where d = 90000;

发现查询时间需要0.44 秒

再把有索引的字段 a 作为条件进行查询

select * from t9_1 where a = 90000;

发现查询时间为 0.00 sec，表示执行时间不超过 10 毫秒，非常快。

我们再对比两条 SQL 的执行计划：

mysql> explain select * from t9_1 where d = 90000;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t9_1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3192096 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

mysql> explain select * from t9_1 where a = 90000;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t9_1  | NULL       | ref  | idx_a         | idx_a | 5       | const |   32 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

前者 type 字段为 ALL，后者 type 字段为 ref，显然后者性能更好

rows 这个字段前者是 3192096，而后者是 32，有索引的情况扫描行数大大降低。

因此建议数据检索时，在条件字段添加索引。

2 聚合函数

在测试表 t9_1 中，如果要求出无索引字段 d 的最大值，SQL 如下：

select max(d) from t9_1;

执行时间为 0.33 秒。

再看下求有索引的字段 a 的最大值：

select max(a) from t9_1;

执行时间为 0.00 秒，表示执行时间不超过 10 毫秒。

相比对没有索引的字段 d 求最大值（花费330毫秒），显然索引能提升 max() 函数的效率，同理也能提升 min() 函数的效率。

在第 7 节中的 1.3 小节中有介绍 MySQL 5.7.18 之后版本的 count(*) 特点：从 MySQL 5.7.18 开始，通过遍历最小的可用二级索引来处理 count(*) 语句，如果不存在二级索引，则扫描聚簇索引。原因是：InnoDB 二级索引树的叶子节点上存放的是主键，而主键索引树的叶子节点上存放的是整行数据，所以二级索引树比主键索引树小。因此优化器基于成本的考虑，优先选择的是二级索引。

因此索引对聚合函数 count(*) 也有优化作用。

3 排序

在第 4 节 2.1 小节，我们列出了几种通过添加合适索引优化 order by 的方法，这里再做一次总结（如果对下面的总结不是很理解，可以复习第 4 节的内容，有对每种情况举例说明）：

  • 如果对单个字段排序，则可以在这个排序字段上添加索引来优化排序语句；
  • 如果是多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句；
  • 如果是先等值查询再排序，可以通过在条件字段和排序字段添加联合索引来优化排序语句。

4 避免回表

比如下面这条 SQL：

select a,d from t9_1 where a=90000;

可以走 a 字段的索引，但是在学了第 8 节后，我们知道了辅助索引的结构，如果通过辅助索引来寻找数据，InnoDB 存储引擎会遍历辅助索引树查找到对应记录的主键，然后通过主键索引回表去找对应的行数据。

但是，如果条件字段和需要查询的字段有联合索引的话，其实回表这一步就省了，因为联合索引中包含了这两个字段的值。像这种索引就已经覆盖了我们的查询需求的场景，我们称为：覆盖索引。比如下面这条 SQL：

select b,c from t9_1 where b=90000;

可直接通过联合索引 idx_b_c 找到 b、c 的值（联合索引详细讲解将放在第 11 节）。

所以可以通过添加覆盖索引让 SQL 不需要回表，从而减少树的搜索次数，让查询更快地返回结果。

5 关联查询

在第 6 节中，我们讲到了关联查询的一些优化技巧，其中一个优化方式就是：通过在关联字段添加索引，让 BNL变成 NLJ 或者 BKA。

6 总结

本节讲解了常见需要添加索引的场景：

  • 数据检索时在条件字段添加索引
  • 聚合函数对聚合字段添加索引
  • 对排序字段添加索引
  • 为了防止回表添加索引
  • 关联查询在关联字段添加索引

12 普通索引和唯一索引有哪些区别？

对于普通索引和唯一索引的区别，也许你已经知道：有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值。其实对于 MySQL 来说，不止这一种区别。今天我们就再深入探究一下普通索引和唯一索引的区别。

在讨论两者的区别前，我们首先学习一下 Insert Buffer 和 Change Buffer。

1 Insert Buffer

对于非聚集索引的插入时，先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放入 Insert Buffer 中，然后再以一定频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge 操作。这时通常能将多个插入合并到一个操作中（因为在一个索引页中），就大大提高了非聚集索引的插入性能。

增加 Insert Buffer 有两个好处：

  • 减少磁盘的离散读取
  • 将多次插入合并为一次操作

但是得注意的是，使用 Insert Buffer 得满足两个条件：

  • 索引是辅助索引
  • 索引不是唯一

2 Change Buffer

InnoDB 从 1.0.x 版本开始引入了 Change Buffer，可以算是对 Insert Buffer 的升级。从这个版本开始，InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。

影响参数有两个：

  • innodb_change_buffering：确定哪些场景使用 Change Buffer，它的值包含：none、inserts、deletes、changes、purges、all。默认为 all，表示启用所有。
  • innodb_change_buffer_max_size：控制 Change Buffer 最大使用内存占总 buffer pool 的百分比。默认25，表示最多可以使用 buffer pool 的 25%，最大值50。

跟 Insert Buffer 一样，Change Buffer 也得满足这两个条件：

  • 索引是辅助索引
  • 索引不是唯一

为什么唯一索引的更新不使用 Change Buffer ?

原因：唯一索引必须要将数据页读入内存才能判断是否违反唯一性约束。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 Change Buffer 了。

3 普通索引和唯一索引的区别

通过上面对 Insert Buffer 和 Change Buffer 的了解，也许你已经知道了普通索引和唯一索引的另外一种区别：如果对数据有修改操作，则普通索引可以用 Change Buffer，而唯一索引不行。

在上面讲解 Change Buffer 时，也提到了修改唯一索引必须判断是否违反唯一性约束，其实在 RR 隔离级别（事务隔离级别将在第 4 章重点讲解）下，可能会出现一个比较严重的问题：死锁。

那么查询过程两者的区别呢？

对于普通索引，查找到满足条件的第一个记录，还需要查找下一个记录，直到不满足条件。

对于唯一索引来说，查找到第一个记录返回结果就结束了。

但是 InnoDB 是按页从磁盘读取的，所以很大可能根据该普通索引查询的数据都在一个数据页里，因此如果通过普通索引查找到第一条满足条件所在的数据页，再查找后面的记录很大概率都在之前的数据页里，也就是多了几次内存扫描，实际这种消耗可以忽略不计。

这里总结一下普通索引和唯一索引的隐藏区别：

  • 数据修改时，普通索引可以用 Change Buffer，而唯一索引不行。
  • 数据修改时，唯一索引在 RR 隔离级别下，更容易出现死锁。
  • 查询数据时，普通索引查到满足条件的第一条记录还需要继续查找下一个记录，而唯一索引查找到第一个记录就可以直接返回结果了，但是普通索引多出的查找次数所消耗的资源多数情况可以忽略不计。

4 普通索引和唯一索引如何选择

上面说了普通索引和唯一索引的区别，那么两者应该如何选择呢？

如果业务要求某个字段唯一，但是代码不能完全保证写入唯一值，则添加唯一索引，让这个字段唯一，该字段新增重复数据时，将报类似如下的错：

ERROR 1062 (23000): Duplicate entry '1' for key 'f1'

如果代码确定某个字段不会有重复的数据写入，则可以选择添加普通索引。 因为普通索引可以使用 Change Buffer，并且出现死锁的概率比唯一索引低。

5 总结

普通索引和唯一索引的区别：

  • 有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值。
  • 数据修改时，普通索引优于唯一索引，因为普通索引可以用 Change Buffer，并且 RR 隔离级别下，出现死锁的概率比唯一索引低。
  • 查询数据时，两者性能差别不大。

13 联合索引有哪些讲究?

1 认识联合索引

联合索引：是指对表上的多个列进行索引。适合 where 条件中的多列组合，在某些场景可以避免回表。

我们拿讲解 order by 时使用的联合索引 B+ 树图进行理解，如下图：

使用范围查询再排序

联合索引的键值数量大于 1（比如上图中有 a 和 b 两个键值），与单个键值的 B+ 树一样，也是按照键值排序的。对于 a、b 两个字段都做为条件时，查询是可以走索引的；对于单独 a 字段查询也是可以走索引的。但是对于 b 字段单独查询就走不了索引了。

联合索引的建议：

  • where 条件中，经常同时出现的列放在联合索引中。
  • 把选择性最大的列放在联合索引的最左边。

老规矩，创建测试表并写入数据：

use muke; /* 使用muke这个database */
drop table if exists t11; /* 如果表t11存在则删除表t11 */
CREATE TABLE `t11` ( /* 创建表t11 */
`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_c` (`a`,`b`,`c`)
) ENGINE=InnoDB CHARSET=utf8mb4 ;

insert into t11(a,b,c) values (1,1,1),(1,2,2),(1,2,1),(2,2,2),(2,1,2),(2,3,3),(2,4,4),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7),(8,8,8),(1,2,3),(1,2,4); /* 写入一些数据 */

2 联合索引使用分析

2 .1 可以完整用到联合索引的情况

下面我们列出几种可以完整用到联合索引的情况，并查看其执行计划，然后进行简短的分析：

explain select * from t11 where a=1 and b=1 and c=1; /* sql1 */

结果：

+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 15      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+

explain 中的 key_len 列用于表示这次查询中，所选择的索引长度有多少字节，常用于判断联合索引有多少列被选择了。下表总结了常用字段类型的 key_len：

列类型 KEY_LEN 备注
int key_len = 4+1 int 为 4 bytes，允许为 NULL，加 1 byte
int not null key_len = 4 不允许为 NULL
bigint key_len=8+1 bigint 为 8 bytes，允许为 NULL 加 1 byte
bigint not null key_len=8 bigint 为 8 bytes
char(30) utf8 key_len=30*3+1 char(n)为：n * 3 ，允许为 NULL 加 1 byte
char(30) not null utf8 key_len=30*3 不允许为 NULL
varchar(30) not null utf8 key_len=30*3+2 utf8 每个字符为 3 bytes，变长数据类型,加 2 bytes
varchar(30) utf8 key_len=30*3+2+1 utf8 每个字符为 3 bytes，允许为 NULL,加 1 byte,变长数据类型，加 2 bytes
datetime key_len=8+1 (MySQL 5.6.4之前的版本)；key_len=5+1(MySQL 5.6.4及之后的版本) 允许为 NULL，加 1 byte

因为 a、b、c 三个字段都是可以为 NULL 的 int 型。可以知道三个字段的 key_len 都是 5，所以如果完整使用索引 idx_a_b_c，则 key_len 对应的值为 15。再回到上面 sql1 的执行计划中：key_len 显示是 15，而 key 列对应的是 idx_a_b_c，所以 sql1 完整用到了联合索引 idx_a_b_c。

explain select * from t11 where c=1 and b=1 and a=1;  /* sql2 */
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 15      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+

跟 sql1 的执行计划一样，因此联合索引各字段都做为条件时，各字段的位置不会影响联合索引的使用。

explain select * from t11 where a=2 and b in (1,2) and c=2;  /* sql3 */
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t11   | NULL       | range | idx_a_b_c     | idx_a_b_c | 15      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

当联合索引前面的字段使用了范围查询，后面的字段做为条件时仍然可以使用完整的联合索引。

explain select * from t11 where a=1 and b=2 order by c;  /* sql4 */
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 10      | const,const |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+

联合索引前面的字段做为条件时，对后面的字段做排序可以使用完整的联合索引。

explain select * from t11 where a=1 order by b,c;  /* sql5 */
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 5       | const |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

与 sql4 相似，对联合索引第一个字段做条件筛选时，对后面两个字段做排序可以使用完整的联合索引。

explain select a,b,c from t11 order by a,b,c;  /* sql6 */
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t11   | NULL       | index | NULL          | idx_a_b_c | 15      | NULL |   15 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

对联合索引的字段同时做排序时（但是排序的三个字段顺序要跟联合索引中三个字段的顺序一致），可以完整用到联合索引。

2.2 只能使用部分联合索引的情况

有些场景只能用到部分联合索引，这里就列出几种情况。

explain select * from t11 where a=1 and b=1; /* sql11 */
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 10      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+

当条件只包含联合索引的前面部分字段时，可以用到部分联合索引。

explain select * from t11 where a=1 and c=1; /* sql12 */
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 5       | const |    5 |    10.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+

对于联合索引 idx_a_b_c（a,b,c） ，如果条件中只包含 a 和 c，则只能用到联合索引中 a 的索引。c 这里是用不了索引的。联合索引 idx_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c) 三种索引，称为联合索引的最左原则。

explain select * from t11 where a=2  and b in (3,4) order by c; /* sql13 */
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t11   | NULL       | range | idx_a_b_c     | idx_a_b_c | 10      | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+

这里可以复习第 4 节 2.4，当联合索引前面的字段使用了范围查询，对后面的字段排序使用不了索引排序，也就是只能用到联合索引前面两个字段 a 和 b 的索引。

2.3 可以用到覆盖索引的情况

什么是覆盖索引？

从辅助索引中就可以查询到结果，不需要回表查询聚集索引中的记录。

使用覆盖索引的优势：因为不需要扫描聚集索引，因此可以减少 SQL 执行过程的 IO 次数。

explain select b,c from t11 where a=3; /* sql21 */
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+

通过 a 字段上的条件，去联合索引 idx_a_b_c 的索引树上可以直接查找到 b 字段和 c 字段的值，不需要回表，因此 sql21 使用到了覆盖索引。

explain select c from t11 where a=1 and b=1 ; /* sql22 */
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 10      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+

跟 sql21 类似，在联合索引 idx_a_b_c 的索引树上，通过 a 和 b 的值可以直接找到 c 的值，因此 sql22 使用的也是覆盖索引。

explain select id from t11 where a=1 and b=1 and c=1; /* sql23 */
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | t11   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 15      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+

通过 a、b、c 三个字段的值，去联合索引树的叶子节点找到主键 id，不需要回表，因此 sql23 也使用了覆盖索引。

2.4 不能使用联合索引的情况

explain select * from t11 where b=1; /* sql31 */
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t11   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如果只使用联合索引后面的字段做为条件查询，则使用不了联合索引（联合索引最左匹配）。

explain select * from t11 order by b; /* sql32 */
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t11   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

与 sql31 相似，对联合索引后面的字段做排序操作，也使用不了联合索引。

explain select * from t11 where c=1; /* sql33 */
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t11   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

与 sql31 类似。c 字段单独做条件使用不了索引。

explain select * from t11 where b=1 and c=1; /* sql34 */
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t11   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |     6.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

联合索引中，如果第一个字段在条件中没有出现，那么联合索引的后面所有字段作为条件都无法使用这个联合索引。

14 为什么MySQL会选错索引？

在工作中，也许我们有时会遇到这种场景：某条 SQL 明明可以走 a 索引，却走了更慢的 b 索引。 今天我们就来讨论这种现象。

为了方便实验，首先创建测试表并写入测试数据，语句如下：

use muke;
drop table if exists t13;
CREATE TABLE `t13` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11),
    `b` int(11),
    PRIMARY KEY (`id`),
    KEY `idx_a` (`a`),
    KEY `idx_b`(`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_t13; /* 如果存在存储过程insert_t13，则删除 */
delimiter ;;
create procedure insert_t13() /* 创建存储过程insert_t13 */
begin
    declare i int; /* 声明变量i */
    set i=1; /* 设置i的初始值为1 */
    while(i<=10000)do /* 对满足i<=10000的值进行while循环 */
        insert into t13(a,b) values(i,i); /* 写入表t13中a字段，值为i当前的值 */
        set i=i+1; /* 将i加1 */
    end while;
end;;
delimiter ; /* 创建批量写入10000条数据到表t13的存储过程insert_t13 */
call insert_t13(); /* 运行存储过程insert_t13 */

在分析 MySQL 选错索引的情况之前，先讲 show index 的使用，因为后面会用到。

1、show index 的使用

当你需要查看某张表的索引详情时，可以使用命令：

show index from t13;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t13   |          0 | PRIMARY  |            1 | id          | A         |        9192 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t13   |          1 | idx_a    |            1 | a           | A         |        9212 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t13   |          1 | idx_b    |            1 | b           | A         |        9212 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

对上面几个重要的字段做一下解释：

  • Non_unique：如果是唯一索引，则值为 0，如果可以有重复值，则值为 1
  • Key_name：索引名字
  • Seq_in_index：索引中的列序号，比如联合索引 idx_a_b_c(a,b,c) ，那么三个字段分别对应 1,2,3
  • Column_name：字段名
  • Collation：字段在索引中的排序方式，A 表示升序，NULL 表示未排序
  • Cardinality：索引中不重复记录数量的预估值，该值等会儿会详细讲解
  • Sub_part：如果是前缀索引，则会显示索引字符的数量；如果是对整列进行索引，则该字段值为 NULL
  • Null：如果列可能包含空值，则该字段为 YES；如果不包含空值，则该字段值为 ‘ ‘
  • Index_type：索引类型，包括 BTREE、FULLTEXT、HASH、RTREE 等

show index 各字段的详细描述可以参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/show-index.html。

2、Cardinality 取值

Cardinality 表示该索引不重复记录数量的预估值。如果该值比较小，那就应该考虑是否还有必要创建这个索引。比如性别这种类型的字段，即使加了索引，Cardinality 值比较小，使用性别做条件查询数据时，可能根本用不到已经添加的索引（可以参考第 3 节的第 4 部分：范围查询）。

Cardinality 统计信息的更新发生在两个操作中：INSERT 和 UPDATE。当然也不是每次 INSERT 或 UPDATE 就更新的，其更新时机为：

  • 表中 1/16 的数据已经发生过变化
  • 表中数据发生变化次数超过 2000000000

Cardinality 值是怎样统计和更新的呢？

InnoDB 表取出 B+ 树索引中叶子节点的数量，记为 a；随机取出 B+ 树索引中的 8 个（这个数量有参数 innodb_stats_transient_sample_pages 控制，默认为 8）叶子节点，统计每个页中不同记录的个数（假设为 b1，b2，b3，…，b8）。则 Cardinality 的预估值为：

（b1 + b2 + b3 + … b8）* a/8

所以 Cardinality 的值是对 8 个叶子节点进行采样获取的，显然这个值并不准确，只供参考。

下面我们来看下统计 Cardinality 涉及到的几个参数：

  • innodb_stats_transient_sample_pages：设置统计 Cardinality 值时每次采样页的数量，默认值为 8。
  • innodb_stats_method：用来判断如果对待索引中出现的 NULL 值记录，默认为 nulls_equal，表示将 NULL 值记录视为相等的记录。另外还有 nulls_unequal 和 nulls_ignored。nulls_unequal 表示将 NULL 视为不同的记录，nulls_ignored 表示忽略 NULL 值记录。
  • innodb_stats_persistent：是否将 Cardinality 持久化到磁盘。好处是：比如数据库重启，不需要再计算 Cardinality 的值。
  • innodb_stats_on_metadata：当通过命令 show table status、show index 及访问 information_chema 库下的 tables 表和 statistics 表时，是否需要重新计算索引的 Cardinality。目的是考虑有些表数据量大，并且辅助索引多时，执行这些操作可能会比较慢，而使用者可能并不需要更新 Cardinality。

3、统计信息不准确导致选错索引

在 MySQL 中，优化器控制着索引的选择。一般情况下，优化器会考虑扫描行数、是否使用临时表、是否排序等因素，然后选择一个最优方案去执行 SQL 语句。

而 MySQL 中扫描行数并不会每次执行语句都去计算一次，因为每次都去计算，数据库压力太大了。实际情况是通过统计信息来预估扫描行数。这个统计信息就可以看成 show index 中的 Cardinality。

而从上面说到 Cardinality 的更新原理可以看出，它的值不一定准确的，因此有时可能就是因为它的值不精准导致选错了索引。这种情况可以使用下面的命令重新统计信息：

analyze table t13;

4、单次选取的数据量过大导致选错索引

有时，我们也会遇到这种情况，如果单次选取的数据量过大，可能也会导致”选错”索引。

第3章 MySQL锁

15 全局锁和表锁什么场景会用到

根据加锁的范围，MySQL 中的锁可分为三类：

  • 全局锁
  • 表级锁
  • 行锁

本节来重点讲解一下全局锁和表锁。

1 全局锁

MySQL 全局锁会关闭所有打开的表，并使用全局读锁锁定所有表。其命令为：

FLUSH TABLES WITH READ LOCK;

简称：FTWRL，可以使用下面命令解锁：

UNLOCK TABLES;

我们来通过实验理解一下全局锁：

use muke;
drop table if exists t14;
CREATE TABLE `t14` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) NOT NULL,
    `b` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t14(a,b) values(1,1);

进行 FTWRL 实验：

session1 session2
FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
select from t14 limit 1;
…
1 row in set (0.00 sec)
*（能正常返回结果）		 select from t14 limit 1;
…
1 row in set (0.00 sec)
*（能正常返回结果）
insert into t14(a,b) values(2,2);
ERROR 1223 (HY000): Can’t execute the query because you have a conflicting read lock
（报错）		 insert into t14(a,b) values(2,2);/sql1/
（等待）
UNLOCK TABLES; insert into t14(a,b) values(2,2);/sql1/
Query OK, 1 row affected (5.73 sec)
（session1 解锁后，在等待的 sql1 马上执行成功）

上面的实验中，当 session1 执行 FTWRL 后，本线程 session1 和其它线程 session2 都可以查询，本线程和其它线程都不能更新。

原因是：当执行 FTWRL 后，所有的表都变成只读状态，数据更新或者字段更新将会被阻塞。

那么全局锁一般什么时候会用到呢？

全局锁一般用在整个库（包含非事务引擎表）做备份（mysqldump 或者 xtrabackup）时。也就是说，在整个备份过程中，整个库都是只读的，其实这样风险挺大的。如果是在主库备份，会导致业务不能修改数据；而如果是在从库备份，就会导致主从延迟。

好在 mysqldump 包含一个参数 —single-transaction，可以在一个事务中创建一致性快照，然后进行所有表的备份。因此增加这个参数的情况下，备份期间可以进行数据修改。但是需要所有表都是事务引擎表。所以这也是建议使用 InnoDB 存储引擎的原因之一。

而对于 xtrabackup，可以分开备份 InnoDB 和 MyISAM，或者不执行 —master-data，可以避免使用全局锁。

2 表级锁

表级锁有两种：表锁和元数据锁。

2.1 表锁

表锁使用场景：

  1. 事务需要更新某张大表的大部分或全部数据。如果使用默认的行锁，不仅事务执行效率低，而且可能造成其它事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务执行速度；
  2. 事务涉及多个表，比较复杂，可能会引起死锁，导致大量事务回滚，可以考虑表锁避免死锁。

其中表锁又分为表读锁和表写锁，命令分别是：

表读锁：

lock tables t14 read;

表写锁：

lock tables t14  write;

下面我们分别用实验验证表读锁和表写锁。

表读锁实验：

session1 session2
lock tables t14 read;
Query OK, 0 rows affected (0.00 sec)
select id,a,b from t14 limit 1;
…
1 row in set (0.00 sec)
（能正常返回结果）		 select id,a,b from t14 limit 1;
…
1 row in set (0.00 sec)
（能正常返回结果）
insert into t14(a,b) values(3,3);
ERROR 1099 (HY000): Table ‘t14’ was locked with a READ lock and can’t be updated
（报错）		 insert into t14(a,b) values(3,3);/sql2/
（等待）
unlock tables;
Query OK, 0 rows affected (0.00 sec)		 insert into t14(a,b) values(3,3);/sql2/
Query OK, 1 row affected (10.97 sec)
（session1 解锁后，sql2 立马写入成功）

从上面的实验我们可以看出，在 session1 中对表 t14 加表读锁，session1 和 session2 都可以查询表 t14 的数据；而 session1 执行更新会报错，session2 执行更新会等待（直到 session1 解锁后才更新成功）。

总结：对表执行 lock tables xxx read （表读锁）时，本线程和其它线程可以读，本线程写会报错，其它线程写会等待。

我们再来看一下表写锁实验：

session1 session2
lock tables t14 write;
Query OK, 0 rows affected (0.00 sec)
select id,a,b from t14 limit 1;
…
1 row in set (0.00 sec)
（能正常返回结果）		 select id,a,b from t14 limit 1;/sql3/
（等待）
unlock tables;
Query OK, 0 rows affected (0.01 sec)		 select id,a,b from t14 limit 1;/sql3/
…
1 row in set (7.16 sec)
（session1 解锁后，sql3 马上返回查询结果）
lock tables t14 write;
Query OK, 0 rows affected (0.00 sec)
delete from t14 limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
（能正常执行删除语句）		 delete from t14 limit 1;/sql4/
（等待）
unlock tables;
Query OK, 0 rows affected (0.00 sec)		 delete from t14 limit 1;/sql4/
Query OK, 1 row affected, 1 warning (14.94 sec)
（session1 解锁后，sql4 立马执行成功）

总结：对表执行 lock tables xxx write （表写锁）时，本线程可以读写，其它线程读写都会阻塞。

2.2 元数据锁

在 MySQL 中，DDL 是不属于事务范畴的。如果事务和 DDL 并行执行同一张表时，可能会出现事务特性被破坏、binlog 顺序错乱等 bug（比如 bug#989）。为了解决这类问题，从 MySQL 5.5.3 开始，引入了元数据锁（Metadata Locking，简称：MDL 锁）（这段内容参考《淘宝数据库内核月报》MySQL · 特性分析 · MDL 实现分析）。

从上面我们知道，MDL 锁的出现解决了同一张表上事务和 DDL 并行执行时可能导致数据不一致的问题。

但是，我们在工作中，很多情况需要考虑 MDL 的存在，否则可能导致长时间锁等待甚至连接被打满的情况。如下例：

session1 session2 session3
select id,a,b,sleep(100) from t14 limit 1;/sql5/
alter table t14 add column c int;/sql6/
（等待）		 select id,a,b from t14 limit 1;/sql7/
（等待）
select id,a,b,sleep(100) from t14 limit 1;/sql5/
…
1 row in set (1 min 40.00 sec)
（100秒后 sql5 返回结果）		 alter table t14 add column c int;/sql6/
Query OK, 0 rows affected (1 min 33.98 sec) Records: 0 Duplicates: 0 Warnings: 0
（session1 的查询语句执行完成后，sql6 立马执行完毕）		 select id,a,b from t14 limit 1;/sql7/
…
1 row in set (1 min 26.65 sec)
（session1 的查询语句执行完成后，sql7 立马执行完毕）

上面的实验中，我们在 session1 查询了表 t14 的数据，其中使用了 sleep(100) ，表示在 100 秒后才会返回结果；然后在 session2 执行 DDL 操作时会等待（原因是 session1 执行期间会对表 t14 加一个 MDL，而 session2 又会跟 session1 争抢 MDL）；而 session3 执行查询时也会继续等待。因此如果 session1 的语句一直没结束，其它所有的查询都会等待。这种情况下，如果这张表查询比较频繁，很可能短时间把数据库的连接数打满，导致新的连接无法建立而报错，如果是正式业务，影响是非常恐怖的。

当然如果出现这种情况，假如你还有 session 连着数据库，可以 kill 掉 session1 中的语句或者终止 session2 中的 DDL 操作，可以让业务恢复。但是出现这种情况的根源其实是：session1 中有长时间未提交的事务。因此对于开发来说，在工作中应该尽量避免慢查询、尽量保证事务及时提交、避免大事务等，当然对于 DBA 来说，也应该尽量避免在业务高峰执行 DDL 操作。

16 行锁：InnoDB替代MyISAM的重要原因

MySQL 5.5 之前的默认存储引擎是 MyISAM，5.5 之后改成了 InnoDB。InnoDB 后来居上最主要的原因就是：

  • InnoDB 支持事务：适合在并发条件下要求数据一致的场景。
  • InnoDB 支持行锁：有效降低由于删除或者更新导致的锁定。

在讲解行锁之前，我们首先来看一下两阶段锁协议。

1 两阶段锁

传统的关系型数据库加锁的一个原则是：两阶段锁原则。

两阶段锁：锁操作分为两个阶段，加锁阶段和解锁阶段，并且保证加锁阶段和解锁阶段不相交。

我们可以通过下面这张表理解两阶段锁：

序号 MySQL 操作 解释 锁阶段
1 begin; 事务开始
2 insert into …; 加 insert 对应的锁 加锁阶段
3 update table …; 加 update 对应的锁 加锁阶段
4 delete from …; 加 delete 对应的锁 加锁阶段
5 commit; 事务结束，同时释放 2、3、4 步骤中加的锁 解锁阶段

2 InnoDB 行锁模式

InnoDB 实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁；
  • 排他锁（X）：允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享读锁和排他写锁。

对于普通 select 语句，InnoDB 不会加任何锁，事务可以通过以下语句显式给记录集加共享锁或排他锁：

  • 共享锁（S）：select * from table_name where … lock in share mode;
  • 排他锁（X）：select * from table_name where … for update。

3 InnoDB 行锁算法

InnoDB 行锁的三种算法：

  • Record Lock：单个记录上的索引加锁。
  • Gap Lock：间隙锁，对索引项之间的间隙加锁，但不包括记录本身。
  • Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

InnoDB 行锁实现特点意味着：如果不通过索引条件检索数据，那么 InnoDB 将对表中所有记录加锁，实际效果跟表锁一样。

4 事务隔离级别

不同事务隔离级别对应的行锁也是不一样的，因此在讲解行锁的锁定范围之前，先简单聊聊事务隔离级别。事务隔离级别的详细介绍放在下一章。

MySQL 的 4 种隔离级别：

  • Read uncommitted（读未提交）: 在该隔离级别，所有事务都可以看到其它未提交事务的执行结果。可能会出现脏读。
  • Read Committed（读已提交，简称： RC）：一个事务只能看见已经提交事务所做的改变。因为同一事务的其它实例在该实例处理期间可能会有新的 commit，所以可能出现幻读。
  • Repeatable Read（可重复读，简称：RR）：这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。消除了脏读、不可重复读，默认也不会出现幻读。
  • Serializable（串行）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
  • 脏读：读取未提交的事务。
  • 幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。

5 RC 隔离级别下的行锁实验

有时我们可能会思考，某条语句（类似 select * from table_name where a=… for update;）是怎么加锁的？

要想分析某条 SQL 是怎么加锁的，如果其他信息都不知道，那就得分几种情况了，不同情况加锁的方式也各不一样，比较常见的一些情况如下：

  • RC 隔离级别，a 字段没索引。
  • RC 隔离级别，a 字段有唯一索引。
  • RC 隔离级别，a 字段有非唯一索引。
  • RR 隔离级别，a 字段没索引。
  • RR 隔离级别，a 字段有唯一索引。
  • RR 隔离级别，a 字段有非唯一索引。

Read uncommitted 和 Serializable 这两种隔离级别在生产环境基本不用，就不做分析了。

我们先验证 RC 隔离级别下的几种情况，RR 隔离级别的实验放在下节：

首先创建测试表及写入数据:

use muke;

drop table if exists t16;

CREATE TABLE `t16` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) NOT NULL,
    `b` int(11) NOT NULL,
    `c` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_a` (`a`) USING BTREE,
    KEY `idx_c` (`c`)
) ENGINE=InnoDB  CHARSET=utf8mb4;

insert into t16(a,b,c) values (1,1,1),(2,2,2),(3,3,3),(4,4,3);

让我们开始实验吧！

5.1 通过非索引字段查询

我们首先来看一下条件字段不使用索引的例子：

session1 session2
set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/ set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/
begin;
use muke;
select * from t16 where b=1 for update;
…
1 row in set (0.00 sec)
use muke;
select from t16 where b=2 for update;
*（等待）
commit; select from t16 where b=2 for update;
…
1 row in set (6.81 sec)
*（session1 执行 commit 后立马返回结果）

这里解释一下为什么要用 for update？

我们常使用的查询语句，比如 select * from t16 where b=1 属于快照读，是不会看到别的事务插入的数据的。

而在查询语句后面加了 for update 显式给记录集加了排他锁，也就让查询变成了当前读。插入、更新、删除操作，都属于当前读。其实也就可以理解 select … for update 是为了让普通查询获得插入、更新、删除操作时所获得的锁。

表面看起来 session1 只给了 b=1 这一行加了排他锁，但 session2 在请求其它行的排他锁时，却出现了锁等待。看下图：

RC隔离级别和条件字段无索引

由于 b 字段没有索引，因此只能走聚簇索引，进行全表扫描。从上图中可以看到，满足条件的记录有一条，但是聚簇索引上的所有记录，都被加上了 X 锁。

为什么不是只在满足条件的记录上加锁呢？

这是因为在 MySQL 中，如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由 server 层进行过滤。因此也就把所有记录都锁上了。

当然 MySQL 在这里有一些改进的，在 server 层过滤掉不满足条件的数据后，会把不满足条件的记录放锁。保证了最后只会持有满足条件的锁，但是每条记录的加锁操作还是不会省略。

总结：没有索引的情况下，InnoDB 的当前读会对所有记录都加锁。所以在工作中应该特别注意 InnoDB 这一特性，否则可能会产生大量的锁冲突。

5.2 通过唯一索引查询

我们再来看一下条件字段有唯一索引的例子：

session1 session2
set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/ set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/
begin;
use muke;
select * from t16 where a=1 for update;
…
1 row in set (0.00 sec)
use muke;
select * from t16 where a=2 for update;
…
1 row in set (0.00 sec)
select from t16 where a=1 for update;
*（等待）
commit; select from t16 where a=1 for update;
…
1 row in set (4.70 sec)
*（session1 提交后，马上返回结果）

session1 给了 a=1 这一行加了排他锁，在 session2 中请求其他行的排他锁时，不会发生等待；但是在 session2 中请求 a=1 这一行的排他锁时，会发生等待。看下图：

RC隔离级别和条件字段有唯一索引

由于 a 是唯一索引，因此 select * from t16 where a=1 for update；(后面称为 SQL2) 语句会选择走 a 列的索引进行条件过滤，在找到 a=1 的记录后，会将唯一索引上 a=1 索引记录上加 X 锁，同时，会根据读取到的 id 列，回到聚簇索引，然后将 id=1 对应的聚簇索引项加 X 锁。

为什么聚簇索引上的记录也要加锁呢？

比如，并发的一条 SQL，是通过主键索引来更新：update t16 set b=10 where id =1; 如果 SQL2 没有将主键索引上的记录加锁，那么并发的 update 并不知道 SQL2 在执行，所以如果 update 执行了，就违背了同一记录上的更新或者删除需要串行执行的约束。

总结：如果查询的条件是唯一索引，那么 SQL 需要在满足条件的唯一索引上加锁，并且会在对应的聚簇索引上加锁。

5.3 通过非唯一索引查询

我们再来看一下条件字段有非唯一索引的例子：

session1 session2 session3
set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/ set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/ set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/
begin;
use muke;
select * from t16 where c=3 for update;
…
2 rows in set (0.00 sec)
use muke;
select * from t16 where a=1 for update;
…
1 row in set (0.00 sec)		 use muke; select * from t16 where a=2 for update;
…
1 row in set (0.00 sec)
select from t16 where a=3 for update;
*(等待)		 select from t16 where a=4 for update;
*(等待)
commit; select from t16 where a=3 for update;
…
*(session1 提交后，马上返回结果)		 select from t16 where a=4 for update;
…
*(session1 提交后，马上返回结果)

我们在满足条件 c=3 的数据上加了排他锁，如上面结果，就是第 3、4 行。因此第 1、2 行的数据没被锁，而 3、4 行的数据被锁了。如下图：

RC隔离级别和条件字段有非唯一索引

通过上图可以看到，在 a 字段的非唯一索引上，满足 c=3 的所有记录，都被加了锁。同时，对应的主键索引上的记录也都加上了锁。与通过唯一索引查询的情况相比，唯一索引查询最多有一行记录被锁，而非唯一索引将会把满足条件的所有记录都加上锁。

总结：如果查询的条件是非唯一索引，那么 SQL 需要在满足条件的非唯一索引上都加上锁，并且会在它们对应的聚簇索引上加锁。

17 间隙锁的意义

1 揭晓上一节课后问题的答案

拿上节的测试表 t16，进行如下实验：

session1 session2
set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/ set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/
begin; begin;
use muke;
select from t16 where c=3 for update;
*Result1
use muke; insert into t16(a,b,c) values (5,5,3);
commit;
select from t16 where c=3 for update;
*Result2
commit;

我们看一下上面的实验结果：Result 1 和 Result 2 结果是不同的。在 session2 中，同一个事务中，按相同的查询条件重新读取以前检索过的数据，却发现了 session1 插入的满足查询条件的新数据，这也就是上一节讲到的幻读情况。

为什么上面的实验中会出现幻读呢？

我们来看看下面这张图：

RC隔离级别和条件字段有非唯一索引（幻读）

从图中可以看出，RC 隔离级别下，只锁住了满足 c=3 的当前行，而不会对后面的位置（或者说间隙）加锁，因此导致 session1 的写入语句能正常执行并提交。

那么应该怎样降低幻读出现的概率呢？

从上面的分析我们可以知道，产生幻读的原因是：行锁只能锁住当前行，但是新插入的记录，是在被锁住记录之前的间隙。因此，为了降低幻读出现的概率，InnoDB 在 RR 隔离级别下配置了间隙锁（Gap Lock）。

2 RR 隔离级别下的非唯一索引查询

我们继续看上面的实验，这里不同点是我们把隔离级别设置成 RR。

我们再单独建一张表，表结构与上节的表结构一致，为了方便后面分析，数据稍微有改动，语句如下：

use muke;

drop table if exists t17;

CREATE TABLE `t17` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) NOT NULL,
    `b` int(11) NOT NULL,
    `c` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_a` (`a`) USING BTREE,
    KEY `idx_c` (`c`)
) ENGINE=InnoDB  CHARSET=utf8mb4;

insert into t17(id,a,b,c) values (1,1,1,1),(2,2,2,2),(4,4,4,4),(6,6,6,4);

开始RR隔离级别下的实验：

session1 session2
set session transaction_isolation=’REPEATABLE-READ’;/设置会话隔离级别为 RR/ set session transaction_isolation=’REPEATABLE-READ’;/设置会话隔离级别为 RR/
begin; begin;
use muke;
select from t17 where c=4 for update;
*Result1
use muke;
insert into t17(a,b,c) values (7,7,4); /SQL 1/
select from t17 where c=4 for update;
*Result2
insert intot17(a,b,c) values (7,7,4); /SQL 1/
Query OK, 1 row affected (20.73 sec)
（等session2 执行commit; 后，SQL1马上返回结果）		 commit;
commit;

根据实验情况，我们在 session2 中，对满足条件 c=4 的数据加上了排他锁，然后在 session1 写入一条 c=4 的记录，此时会出现等待，直到 session2 对事务进行提交后，session1 才会执行成功。这是为什么呢？我们来看下图：

RR隔离级别和条件字段有非唯一索引

与 RC 隔离级别下的图相似，但是有个比较大的区别是：RR 隔离级别多了 GAP 锁。

如上图，首先需要考虑哪些位置可以插入新的满足条件 c=4 的项：

  • 由于 B+ 树索引是有序的，因此 [2,2]（代表 c 和 id 的值，后面就不一一说明了）前面的记录，不可能插入 c=4 的记录了；
  • [2,2] 与 [4,4] 之间可以插入 [4,3]；
  • [4,4] 与 [4,6] 之间可以插入 [4,5]；
  • [4,6] 之后，可以插入的值就很多了：[4,n] (其中 n>6) ；

为了保证这几个区间不会插入新的满足条件 c=4 的记录，MySQL RR 隔离级别选择了 GAP 锁，将这几个区间锁起来。

而上面实验中，语句 insert into t17 (a,b,c) values (7,7,4) 其对应插入 c 和 id 的值为 [4,7]，是在最后这个被 GAP Lock 锁住的区间，因此如上面实验，insert 操作会等待。

3 RR 隔离级别下的非索引字段查询

上一节中，我们测试了 RC 隔离级别下，非索引字段做条件的当前读会对所有记录都加锁。

这一节，我们测试一下 RR 隔离级别下，非索引字段做条件的当前读加锁情况。

首先对 t17 表中的数据做初始化：

use muke;

drop table if exists t17;

CREATE TABLE `t17` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) NOT NULL,
    `b` int(11) NOT NULL,
    `c` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_a` (`a`) USING BTREE,
    KEY `idx_c` (`c`)
) ENGINE=InnoDB  CHARSET=utf8mb4;

insert into t17(id,a,b,c) values (1,1,1,1),(2,2,2,2),(4,4,4,4),(6,6,6,4);
session1 session2 session3
set session transaction_isolation=’REPEATABLE-READ’;/设置会话隔离级别为 RR/ set session transaction_isolation=’REPEATABLE-READ’;/设置会话隔离级别为 RR/ set session transaction_isolation=’REPEATABLE-READ’;/设置会话隔离级别为 RR/
begin;
use muke;
select * from t17 where b=1 for update;
…
1 rows in set (0.00 sec)
use muke;
select from t17 where b=2 for update;
*(等待)		 insert into t17(a,b,c) values(10,10,10);
(等待)
commit; select from t17 where b=2 for update;
…
*(session1 提交后，马上返回结果)		 insert into t17(a,b,c) values(10,10,10);
…
(session1 提交后，马上写入)

可能你会问？为什么 session3 的 insert 会出现等待？

我们看看下图：

RR隔离级别和条件字段无索引

如图，所有记录都有 X 锁，除此之外，每个 GAP 也被加上了 GAP 锁。因此这张表在执行完 select * from t17 where b=1 for update; 到 commit 之前，除了不加锁的快照读，其它任何加锁的 SQL，都会等待，如果这是线上业务表，那就是件非常恐怖的事情了。

总结：RR 隔离级别下，非索引字段做条件的当前读不但会把每条记录都加上 X 锁，还会把每个 GAP 加上 GAP 锁。再次说明，条件字段加索引的重要性。

4 RR 隔离级别下的唯一索引当前读是否会用到 GAP 锁

GAP 锁的目的是：为了防止同一事务两次当前读，出现幻读的情况。如果能确保索引字段唯一，那其实一个等值查询，最多就返回一条记录，而且相同索引记录的值，一定不会再新增，因此不会出现 GAP 锁。

因此以唯一索引为条件的当前读，不会有 GAP 锁。所以 RR 隔离级别下的唯一索引当前读加锁情况与 RC 隔离级别下的唯一索引当前读加锁情况一致。这里就不再实验了。

18 为什么会出现死锁？

1 认识死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

InnoDB 中解决死锁问题有两种方式：

  1. 检测到死锁的循环依赖，立即返回一个错误（这个报错内容请看下面的实验），将参数 innodb_deadlock_detect 设置为 on 表示开启这个逻辑；
  2. 等查询的时间达到锁等待超时的设定后放弃锁请求。这个超时时间由 innodb_lock_wait_timeout 来控制。默认是 50 秒。

一般线上业务都建议使用的第 1 种策略，因为第 2 种策略锁等待时间是 50 秒，对于高并发的线上业务是不能接受的。
但是第 1 种策略，也会有死锁检测时的额外 CPU 开销的，比如电商中的秒杀场景。这种情况就可以根据业务开发商量优化程序，如果可以确保业务一定不会出现死锁，可以临时把死锁检测关掉，以提高并发效率。

2 为什么会产生死锁

2.1 同一张表中

不同线程并发访问同一张表的多行数据，未按顺序访问导致死锁。

2.2 不同表之间

不同线程并发访问多个表时，未按顺序访问导致死锁。

2.3 事务隔离级别

RR隔离级别下，由于间隙锁导致死锁。

3 如何降低死锁概率

那么应该怎样降低出现死锁的概率呢？这里总结了如下一些经验：

  1. 更新 SQL 的 where 条件尽量用索引；
  2. 基于 primary 或 unique key 更新数据；
  3. 减少范围更新，尤其非主键、非唯一索引上的范围更新；
  4. 加锁顺序一致，尽可能一次性锁定所有需要行；
  5. 将 RR 隔离级别调整为 RC 隔离级别。

4 分析死锁的方法

尽管在上面介绍了降低死锁概率的方法，但是在实际工作中，死锁很难完全避免。因此，捕获并处理死锁也是一个好的编程习惯。

InnoDB 中，可以使用 SHOW INNODB STATUS 命令来查看最后一个死锁的信息。我们可以尝试用下这个命令获取一些死锁信息，如下：

show engine innodb status\G

另外设置 innodb_print_all_deadlocks = on 可以在 err log 中记录全部死锁信息。

第4章 事务

19 数据库忽然断电会丢失数据吗？

1 什么是事务？

根据《高性能 MySQL》第 3 版 1.3 事务一节中定义：

事务就是一组原子性的 SQL 查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。

一个良好的事务处理系统，必须具备 ACID 特性：

  • atomicity（原子性） ：要么全执行，要么全都不执行；
  • consistency（一致性）：在事务开始和完成时，数据都必须保持一致状态；
  • isolation（隔离性） ：事务处理过程中的中间状态对外部是不可见的；
  • durability（持久性） ：事务完成之后，它对于数据的修改是永久性的。

InnoDB 采用 redo log 机制来保证事务更新的一致性和持久性。

2 Redo log

Redo log 是 InnoDB 才会记得 log 。

Redo log 称为重做日志，用于记录 事务 操作变化，记录的是数据被修改之后的值。

Redo log 采用 Write Ahead Log 策略，即：事务提交时，先写重做日志再修改页；当由于发生宕机而导致数据丢失时，就可以通过重做日志来完成数据的恢复。

Redo log 由两部分组成：

  • 内存中的重做日志缓冲（redo log buffer）
  • 重做日志文件（redo log file）

每次数据更新会先更新 redo log buffer，然后根据 innodb_flush_log_at_trx_commit 来控制 redo log buffer 更新到 redo log file 的时机。innodb_flush_log_at_trx_commit 有三个值可选：

0：事务提交时，每秒触发一次 redo log buffer 写磁盘操作，并调用操作系统 fsync 刷新 IO 缓存。

1：事务提交时，InnoDB 立即将缓存中的 redo 日志写到日志文件中，并调用操作系统 fsync 刷新 IO 缓存；

2：事务提交时，InnoDB 立即将缓存中的 redo 日志写到日志文件中，但不是马上调用 fsync 刷新 IO 缓存，而是每秒只做一次磁盘 IO 缓存刷新操作。

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 参数的默认值是 1，也就是每个事务提交的时候都会从 log buffer 写更新记录到日志文件，而且会刷新磁盘缓存，这完全满足事务持久化的要求，是最安全的，但是这样会有比较大的性能损失。

将参数设置为 0 时，如果数据库崩溃，最后 1秒钟的 redo log 可能会由于未及时写入磁盘文件而丢失，这种方式尽管效率最高，但是最不安全。

将参数设置为 2 时，如果数据库崩溃，由于已经执行了重做日志写入磁盘的操作，只是没有做磁盘 IO 刷新操作，因此，只要不发生操作系统奔溃，数据就不会丢失，这种方式是对性能和安全的一种折中处理。

3 Binlog

Binlog 是所有存储引擎都会记的 log 。

二进制日志（binlog）记录了所有的 DDL（数据定义语句）和 DML（数据操纵语句），但是不包括 select 和 show 这类操作。Binlog 有以下几个作用：

  • 恢复：数据恢复时可以使用二进制日志
  • 复制：通过传输二进制日志到从库，然后进行恢复，以实现主从同步
  • 审计：可以通过二进制日志进行审计数据的变更操作

可以通过参数 sync_binlog 来控制累积多少个事务后才将二进制日志 fsync 到磁盘。

  • sync_binlog=0，表示每次提交事务都只write，不fsync
  • sync_binlog=1，表示每次提交事务都会执行fsync
  • sync_binlog=N(N>1)，表示每次提交事务都write，累积N个事务后才fsync

比如要加快写入数据的速度或者机器磁盘 IO 瓶颈时，可以将 sync_binlog 设置成大于 1 的值，但是如果设置为 N(N>1)时，如果数据库崩溃，可能会丢失最近 N 个事务的 binlog。

4 怎样确保数据库突然断电不丢数据？

通过上面的讲解，只要 innodb_flush_log_at_trx_commit 和 sync_binlog 都为 1（通常称为：双一），就能确保 MySQL 机器断电重启后，数据不丢失。

因此建议在比较重要的库，比如涉及到钱的库，设置为双一，而你的测试环境或者正式业务不那么重要的库（比如日志库）可以将 innodb_flush_log_at_trx_commit 设置为0，sync_binlog 设置成大于100 的数值，提高更新效率。

20 MVCC怎么实现的?

本节跟大家一起聊聊 MVCC（Multi-Version Concurrency Control，多版本并发控制）。为了方便理解，在讲解 MVCC 之前，我们先来聊聊隐藏列、Undo log 和 Read View。

1 隐藏列

对于 InnoDB ，每行记录除了我们创建的字段外，其实还包含 4 个隐藏的列：

  • ROW ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动以 ROW ID 产生一个聚集索引树。
  • 事务 ID：记录最后一次修改该记录的事务 ID。
  • 回滚指针：指向这条记录的上一个版本。
  • 删除标记：表示当前记录是否被删除。

2 Undo log

上节我们讲到了 redo log，它记录了事务操作变化。但是事务有时是需要回滚的，这时，Undo log 就发挥了作用。Undo log 是逻辑日志，将数据库逻辑地恢复到原来的样子，所有修改都被逻辑的取消了。

  • 如果是 insert 操作，其对应的回滚操作就是 delete；
  • 如果是 delete，则对应的回滚操作是 insert；
  • 如果是 update，则对应的回滚操作是一个反向的 update 操作。

Undo log 的作用除了回滚操作，Undo log 的另一个作用是 MVCC，InnoDB 存储引擎中 MVCC 的实现是通过 Undo log 来完成的。当用户读取一行记录时，若该记录已经被其它事务占用，当前事务可以通过 Undo log 读取之前的行版本信息，因为没有事务需要对历史的数据进行修改操作，所以也不需要加锁，以此来实现非锁定读取。

3 Read View

Read View 是指事务进行快照读操作的那一刻，产生数据库系统当前活跃事务列表的一个快照。

Read View 中大致包含以下内容：

  • trx_ids：数据库系统当前活跃事务 ID 集合；
  • low_limit_id：活跃事务中最大的事务 ID +1；
  • up_limt_id：活跃事务总最小的事务 ID；
  • creator_trx_id：创建这个 Read View 的事务 ID。

比如某个事务，创建了 Read View，那么它的 creator_trx_id 就为这个事务的 ID，假如需要访问某一行，假设这一行记录的隐藏事务 ID 为 t_id，那么可能出现的情况如下：

  • 如果 t_id < up_limt_id，说明这行记录在这些活跃的事务创建之前就已经提交了，那么这一行记录对该事务是可见的。
  • 如果 t_id >= low_limt_id，说明这行记录在这些活跃的事务开始之后创建的，那么这一行记录对该事物是不可见的。
  • 如果 up_limit_id <= t_id < low_limit_id，说明这行记录可能是在这些活跃的事务中创建的，如果 t_id 也同时在 trx_ids 中，则说明 t_id 还未提交，那么这一行记录对该事物是不可见的；如果 t_id 不在 trx_ids 中，则说明事务 t_id 已经提交了，那么这一行记录对该事物是可见的。

对于不可见的记录，都是通过查询 Undo log 来查询老的记录。

了解了上面的原理，我们知道了，Read View 规则帮我们判断当前版本的数据是否可见。下面，我们分析下当查询一条记录时，大致的步骤：

  • 获取事务本身的事务 ID；
  • 获取 Read View；
  • 查询得到的数据，然后与 Read View 中的事务版本号进行比较；
  • 如果能查询，则直接查询对应的记录；如果不能直接查询，则通过 Undo Log 中获取历史快照；
  • 最终返回结果。

另外需要补充的一点就是，在 RR 和 RC 隔离级别下，获取 Read View 的时机也是不一样的：

  • 在可重复读隔离级别（RR）下，同一个事务中，查询语句只是在第一个读请求发起时获取 Read View，而后面相同的查询语句都会使用这个 Read View。
  • 在读已提交隔离级别（RC）下，同一个事务中，同样的查询语句在每次读请求发起时都会获得 Read View。

4 什么是 MVCC？

在说 MVCC 之前，大家先看看下面这个例子。

首先创建表并写入测试数据：

use muke;
drop table if exists t20;

CREATE TABLE `t20` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_c` (`a`)
) ENGINE=InnoDB CHARSET=utf8mb4;
insert into t20(a,b) values (1,1),(2,2);

进行实验：

session1 session2
1 set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/ set session transaction_isolation=’READ-COMMITTED’;/设置会话隔离级别为 RC/
2 select from t20;
*Result1
3 begin;
4 update t20 set b=666 where a=1;
5 begin;
6 select from t20;
*Result2
7 commit;
8 select from t20;
*Result3
9 commit;

其中

Result1：

+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
|  2 | 2 | 2 |
+----+---+---+

Result2：

mysql> select * from t20;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
|  2 | 2 | 2 |
+----+---+---+

Result3：

mysql> select * from t20;
+----+---+-----+
| id | a | b   |
+----+---+-----+
|  1 | 1 | 666 |
|  2 | 2 |   2 |
+----+---+-----+

在 session1 更新了 a=1 这行记录，但还没提交的情况下，在 session2 中，满足 a=1 这条记录，b 的值还是原始值 1，而不是 session 1 更新之后的 666，那么在数据库层面，这是怎么实现的呢？

其实 InnoDB 就是通过 MVCC 和 Undo log 来实现的。

什么是 MVCC 呢？

MVCC， 即多版本并发控制。MVCC 的实现，是通过保存数据在某个时间点的快照来实现的，也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

也就是上面实验第 6 步中，为什么 session2 查询的结果还是 session1 修改之前的记录。

5 MVCC 的实现原理

我们拿上面的例子，对应解释下 MVCC 的实现原理，如下图：

MVCC实现原理

如图，首先 insert 语句向表 t20 中插入了一条数据，a 字段为 1，b 字段为 1， ROW ID 也为 1 ，事务 ID 假设为 1，回滚指针假设为 null。当执行 update t20 set b=666 where a=1 时，大致步骤如下：

  • 数据库会先对满足 a=1 的行加排他锁；
  • 然后将原记录复制到 undo 表空间中；
  • 修改 b 字段的值为 666，修改事务 ID 为 2；
  • 并通过隐藏的回滚指针指向 Undo log 中的历史记录；
  • 事务提交，释放前面对满足 a=1 的行所加的排他锁。

在前面实验的第 6 步中，session2 查询的结果是 session1 修改之前的记录，也就是那个点的 Read View，根据上面将的 Read View 原理，被查询行的隐藏事务 ID 就在当前活跃事务 ID 集合中。因此，这一行记录对该事物（session2 中的事务）是不可见的，可以知道 session2 查询的 a=1 这行记录实际就是来自 Undo log 中。我们看到的现象就是同一条记录在系统中存在了多个版本，这就是 MySQL 的多版本并发控制（MVCC）。

需要注意的是，MVCC 只在 RC 和 RR 两个隔离级别下工作。因此在上面的实验中，改成 RR 隔离级别，第 6 步中，得到的结果还是 session1 修改之前的记录（但是在第 8 步，结果不一样哦，感兴趣的可以把上面操作放在 RR 隔离级别下实验一下）

6 MVCC 的优势

MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性。

通过 MVCC，保证了事务 ACID 中的 I（隔离性）特性。

7 总结

我们知道了，MySQL 是通过 Read View 判断是否能直接查询到对应的记录，如果需要查询一些被其它事务正在更新的行，则要取出 Undo log 中历史版本的记录。

MVCC 实现的原理大致是：

InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 Undo log 中。如果要执行更新操作，会将原记录放入 Undo log 中，并通过隐藏的回滚指针指向 Undo log 中的原记录。其它事务此时需要查询时，就是查询 Undo log 中这行数据的最后一个历史版本。

MVCC 最大的好处是读不加锁，读写不冲突，极大的增加了 MySQL 的并发性，通过 MVCC，也保证了事务 ACID 中的 I（隔离性）特性。

8 问题

MVCC 为什么只在 RC 和 RR 两个隔离级别下工作？

解答：

从查询方面看，MVCC是解决在并发条件下，同时可能会产生多个值，如何从多个值中选择一个值的技术。

但在RU和串行的隔离级别下，只会存在一个值。

  • RU：每次只取最新的值，不存在多个值的情况。
  • 串行：每次查询都会加读锁，当有更新时会阻塞住，只有等到查询完锁释放后，才会做更新操作。所以也不存在多个值的情况。

21 不同事务隔离级别有哪些区别？

1 通过基本定义认识事务隔离级别

MySQL 有四种隔离级别，我们来看一下这四种隔离级别的基本定义：

  • Read uncommitted（读未提交，简称：RU）: 在该隔离级别，所有事务都可以看到其它未提交的事务的执行结果。可能会出现脏读。
  • Read Committed（读已提交，简称： RC）：一个事务只能看见已经提交事务所做的改变。因为同一事务的其它实例在该实例处理期间可能会有新的 commit，所以可能出现幻读。
  • Repeatable Read（可重复读，简称：RR）：这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。消除了脏读、不可重复读，默认也不会出现幻读。
  • Serializable（串行）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问
    题。

4 如何选择合适的事务隔离级别

在上面的内容中，我们认识了事务隔离级别，那么应该怎样选择合适的事务隔离级别呢？

对于 RU 隔离级别，会导致脏读，从性能上看，也不会比其它隔离级别好太多，因此生产环境不建议使用。

对于 RC 隔离级别，相比 RU 隔离级别，不会出现脏读；但是会出现幻读，一个事务中的两次执行同样的查询，可能得到不一样的结果。

对于 RR 隔离级别，相比 RC 隔离级别，解决了部分幻读（这个在第 17 节详细讲了，RR 隔离级别通过间隙锁解决了部分幻读），但是相对于 RC，锁的范围可能更大了。

对于 Serializable 隔离级别，因为它强制事务串行执行，会在读取的每一行数据上都加锁，因此可能会导致大量的超时和锁争用的问题。生成环境很少使用。

因此总的来说，建议在 RC 和 RR 两个隔离级别中选一种，如果能接受幻读，需要并发高点，就可以配置成 RC，如果不能接受幻读的情况，就设置成 RR 隔离级别。

隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能

Innodb中的事务隔离级别和锁的关系

22 养成好的事务习惯

1 不好的事务习惯

1.1 在循环中提交

在大多数情况下，MySQL 都是开启自动提交的，如果遇到循环执行 SQL，则相当于每个循环中都会进行一次提交，实际这算一个不好的事务习惯了。下面我创建一张测试表，并定义两个循环写入数据的存储过程：一个是自动提交，另一个是在循环前开启一个事务，在循环后一次性提交。

use muke; /* 使用muke这个database */
drop table if exists t22; /* 如果表t22存在则删除表t22 */
CREATE TABLE `t22` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB  CHARSET=utf8mb4;

drop procedure if exists insert_t22_1; /* 如果存在存储过程insert_t22_1，则删除 */
delimiter ;;
create procedure insert_t22_1() /* 创建存储过程insert_t22_1 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=10000)do /* 对满足i<=10000的值进行while循环 */
insert into t22(a,b,c,d) values(i,i,i,i); /* 写入表t22中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入10000条数据到表t22的存储过程insert_t22_1 */

drop procedure if exists insert_t22_2; /* 如果存在存储过程insert_t22_2，则删除 */
delimiter ;;
create procedure insert_t22_2() /* 创建存储过程insert_t22_2 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
start transaction;
while(i<=10000)do /* 对满足i<=10000的值进行while循环 */
insert into t22(a,b,c,d) values(i,i,i,i); /* 写入表t22中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
commit;
end;;
delimiter ; /* 创建批量写入10000条数据到表t22的存储过程insert_t22_2 */

我们来对比两个存储过程的速度：

mysql> call insert_t22_1(); /* insert_t22_1 */
Query OK, 1 row affected (28.36 sec)

mysql> call insert_t22_2(); /* insert_t22_2 */
Query OK, 0 rows affected (0.37 sec)

明显第二种方式快的多。因为 insert_t22_1 每一次提交都要写一次重做日志，实际写了 10000 次重做日志，而存储过程 insert_t22_2 只写了 1 次重做日志。

因此，在类似这种循环写入的情况，如果循环次数不是太多，建议在循环前开启一个事务，循环结束后统一提交。

1.2 不关注同一个事务里语句顺序

比如 A 在超市购买 100 元的商品，付款操作可以简化为：

序号 操作
1 A 的账户中扣除 100
2 超市的账户增加 100
3 在超市系统中记录一条日志

很多时候我们会按上面的 SQL 步骤放入一个事务里执行，不关注里面语句的顺序。实际可以优化的。

根据两阶段锁，整个事务里面涉及的锁，需要等到事务提交时才会释放。因此我们在同一个事务中，可以把没锁或者锁范围小的语句放在事务前面执行，而锁定范围大的语句放在后面执行。

这里来回顾一下第 16 节中提到的两阶段锁：锁操作分为两个阶段，加锁阶段和解锁阶段，并且保证加锁阶段和解锁阶段不相交。在执行语句的时候加上锁，但并不是语句执行完就立刻释放锁，而是要等到事务结束时才释放。

因此上面 A 购买商品的例子中，可能很多人同时在超市付款，那么存在锁竞争的最可能是超市账户增加 100 元的操作。

那么付款操作可以这么优化：

序号 操作
1 在超市系统中记录一条日志
2 A 的账户中扣除 100
3 超市的账户增加 100

把可能存在锁竞争的操作放在最后执行，从而优化整个事务。

因此在写程序时，应该去关注事务里的语句顺序。

1.3 不关注不同事务访问资源的顺序

各位是否还记得，在第 18 节中，讲到了几种产生死锁的原因，其中有两条就跟不同事务访问资源顺序有关，我们来回顾一下：

  • 不同线程并发访问同一张表的多行数据，未按顺序访问导致死锁。
  • 不同线程并发访问多个表时，未按顺序访问导致死锁。

如果不关注并发访问的不同事务中访问资源的顺序，就会增大出现死锁的概率。

因此，为了降低死锁，我们需要去关注不同事务访问资源的顺序。

1.4 不关注事务隔离级别

在上一节中，我们详细聊到了事务隔离级别，因此也知道，不同事务隔离级别加锁的情况也是不同的。

如果完全不关注自己业务使用的 MySQL 是什么隔离级别，可能会降低程序的并发能力或者导致死锁。

比如业务场景完全能接受幻读，如果要求更高的 QPS，使用 RR 隔离级别显然不是最好的选择，因此可以改为 RC 隔离级别。

而如果业务使用的是 RR 隔离级别，可能由于间隙锁导致死锁（可参考第 18 节 2.3 中的例子），因此也应该在程序编写时关注 RR 隔离级别下是否会有间隙锁。

因此，为了更高的并发和降低死锁概率，在创建事务前，也应该去关注自己业务的数据库是什么事务隔离级别。

1.5 在事务中混合使用存储引擎

在事务中混合使用事务型（比如 InnoDB）和非事务型（比如 MyISAM）表，如果是正常提交，到没什么问题。

但是，如果该事务回滚了，事务型的表可以正常回滚，而非事务型的表的变更就无法回滚了。这种情况就会导致数据不正常，并且事务最终的结果也难以确定。

因此，在事务中混合使用存储引擎也是一个不好的事务习惯。

值得一提的是：如果开启 GTID，那么当同一个事务中使用不同存储引擎的表时，会出现如下报错：
ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.
因此，开启 GTID 的情况，可以避免同一个事务中混合使用存储引擎的情况。

2 总结一下好的事务习惯

在本节中，我们列举了几种不好的事务习惯，这里总结一下好的事务习惯：

  • 循环写入的情况，如果循环次数不是太多，建议在循环前开启一个事务，循环结束后统一提交。
  • 优化事务里的语句顺序，减少锁时间。
  • 关注不同事务访问资源的顺序。
  • 创建事务之前，关注事务隔离级别。
  • 不在事务中混合使用存储引擎。

23 细聊分布式事务

比如你在网上买了一本书，可以简化为在订单库增加订单，在库存库减掉这本书的 1 个库存。这里订单库和库存库是在不同的机器上，如果这两步放在两个事务里，增加订单这一步成功了，但是减库存这里失败了。那岂不是就乱了。

这里就要引出分布式事务了。什么是分布式事务？

1 认识分布式事务

分布式事务是指一个大的事务由很多小操作组成，小操作分布在不同的服务器上或者不同的应用程序上。分布式事务需要保证这些小操作要么全部成功，要么全部失败。MySQL 从 5.0.3 开始支持分布式事务。

分布式事务使用两阶段提交协议：

  • 第一阶段：所有分支事务都开始准备，告诉事务管理器自己已经准备好了；
  • 第二阶段：确定是 rollback 还是 commit，如果有一个节点不能提交，则所有节点都要回滚。

与本地事务不同点在于：分布式事务需要多一次 prepare 操作，等收到所有节点的确定信息后，再进行 commit 或者 rollback。

MySQL 中分布式事务按实现方式可以分为两种：MySQL 自带的分布式事务和结合中间件实现分布式事务。下面来详细介绍一下这两种分布式事务。

2 MySQL 自带的分布式事务

MySQL 有自带的分布式事务实现方法，具体语法如下：

启动分支事务：

xa start 'a', 'a_1';

‘a’,’a_1’ 表示 xid，
a 表示 gtrid，为分布式事务标识符，相同的分布式事务使用相同的 gtrid。
a_1 表示 bqual，为分支限定符，分布式事务中的每一个分支事务的 bqual 必须不同。

结束分支事务：

xa end 'a', 'a_1';

进入准备状态：

xa prepare 'a', 'a_1';

提交分支事务：

xa commit 'a', 'a_1';

回滚分支事务：

xa rollback 'a', 'a_1';

返回当前数据库中处于prepare状态的分支事务的详细信息：

xa recover;

我们来看一个例子：

session1 session2
use muke1; use muke2;
create table t23_1(id int); create table t23_2(id int);
xa start ‘test’,’muke1’; xa start ‘test’,’muke2’;
insert into t23_1 select 1; insert into t23_2 select 1;
xa end ‘test’,’muke1’; xa end ‘test’,’muke2’;
xa prepare ‘test’,’muke1’; xa prepare ‘test’,’muke2’;
xa recover \G xa recover \G
xa commit ‘test’,’muke1’; xa commit ‘test’,’muke2’;

上面的例子就演示了一个分布式事务，事务在 muke1 库中的 t23_1 表中插入一条记录，同时在 muke2 库中的 t23_2 表中插入一条记录，两个操作作为同一个事务提交。在进入准备状态之前，如果 session2 中某一步没执行成功而回滚了，则 session1 和 session2 整个分布式事务的操作都会回滚。

但是 MySQL 5.7 之前的版本，自带的分布式事务存在以下问题：

比如某个分支事务到达 prepare 状态时，此时数据库断电，重启后，可以继续对分支事务进行提交或者回滚，但是提交的事务不会写 binlog，如果有从库，会导致主从数据不一致的情况。

如果分支事务的客户端连接异常中止，那么数据库会自动回滚当前分支未完成的事务，如果此时分支事务已经到 prepare 状态，那么这个分布式事务的其他分支可能已经成功提交，如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务的内容。

还有一种情况，如果分支事务在执行到 prepare 状态时，数据库出现故障，并且无法启动，需要使用全备和 binlog 来恢复数据，那么这些在 prepare 状态的分支事务因为没有记录到 binlog，所以也不能通过binlog 进行恢复，在数据库恢复后，将丢失这部分数据。

所以，MySQL 5.7 之前的版本自带的分布式事务还存在比较严重的缺陷，在有些场景下，会导致数据丢失。如果业务对数据完整性要求不改，可以考虑使用，如果对数据完整性要求比较高，需要考虑先升级到 5.7 版本。

3 结合中间件实现分布式

上面说了 MySQL 自带的分布式事务，这里再介绍一下借助中间件实现分布式的情况。

具体实现方式可以拿上面网上购书的例子来说：

订单业务程序处理完增加订单的操作后，将减库存操作发送到消息队列中间件中（比如：Rocketmq），订单业务程序完成提交。然后库存业务程序检查到消息队列有减对应商品库存的信息，就开始执行减库存操作。库存业务执行完减库存操作，再发送一条消息给消息队列中间件：内容是已经减掉库存。具体步骤如下：

MQ实现增加订单和减库存操作

当然，为了确定最终已经完成减库存操作，还可以加一步对数据库中该商品库存的判断。

第5章 MySQL的一些其他经验

27 使用读写分离需要注意哪些？

对于高访问量的业务场景，MySQL 读写分离显得格外重要。

通常我们说的 MySQL 读写分离是指：对于修改操作在主库上执行，而对于查询操作，在从库上执行。主要目的是分担主库的压力。

但是读写分离有时也会存在问题，比如：主从延迟时，读取的从库数据不是最新的，对应的业务场景比如：

你网购的一个商品，付完款之后，因为主从延迟，第一时间还查询不到订单（查询的从库），即使等一段时间能看到订单，但是相信这种情况很多用户是不能接受的。

本节就一起来讨论一下：读写分离需要注意哪些问题。

通常情况下，读写分离都是依赖主从复制，因此，我们先来看看主从复制的原理，也能方便我们理解为什么会出现主从延迟的现象。

1 主从复制的原理

1.1 MySQL 异步复制

传统的 MySQL 主从复制是异步的，因此也称为异步复制，MySQL 异步复制的原理如下：

  • 在主库开启 binlog 的情况下
  • 如果主库有增删改的语句，会记录到 binlog 中
  • 主库通过 IO 线程把 binlog 里面的内容传给从库的中继日志（relay log）中
  • 主库给客户端返回 commit 成功（这里不会管从库是否已经收到了事务的 binlog）
  • 从库的 SQL 线程负责读取它的 relay log 里的信息并应用到从库数据库中

实现原理如下图：

MySQL异步复制

在上图中，有一个地方不能忽视：

在主库上并行运行的更新 SQL，由于从库只有单个 SQL 线程去消化 relay log，因此更新的 SQL 在从库只能串行执行。这也是很多情况下，会出现主从延迟的原因。

当然，从 5.6 开始，MySQL 支持了每个库可以配置单独的 SQL 线程来消化 relay log，在 5.7 又增加了基于组提交的并行复制，大大改善了主从延迟的问题。

1.2 MySQL 半同步复制

在 MySQL 异步复制的基础上，又出现了一种改进的复制方式，称为：半同步复制。其原理如下：

  • 在主库开启 binlog 的情况下
  • 如果主库有增删改的语句，会记录到 binlog 中
  • 主库通过 IO 线程把 binlog 里面的内容传给从库的中继日志（relay log）中
  • 从库收到 binlog 后，发送给主库一个 ACK，表示收到了
  • 主库收到这个 ACK 以后，才能给客户端返回 commit 成功
  • 从库的 SQL 线程负责读取它的 relay log 里的信息并应用到从库数据库中

实现原理如下图：

MySQL半同步复制

跟传统的异步复制相比，半同步复制保证了所有给客户端发送过确认提交的事务，从库都已经收到这个日志了。

2 常见的读写分离方式

2.1、通过程序

开发通过配置程序来决定修改操作走主库，查询操作走从库。这种方式直连数据库，优点是性能会好点，缺点是配置麻烦。

但是需要注意的是：从库需要设置为 read_only，防止配置错误在从库写入了数据。

这里提醒一点：
程序连接的用户建议不要给 super 权限，因为 super 权限的用户，即使整个库设置了 read_only ，也能写入数据。

2.2、通过中间件

通过中间件实现读写分离，目前算是一种主流的方式。拿 MyCAT 举例：

在 schema.xml 文件中，dataHost 标签 balance 属性的值，决定了是否启用读写分离。

balance 各个值及对应的读写方法如下：

  • 0：不开启读写分离，读操作发送到 writehost
  • 1：全部的 readhost 与 stand by writehost 参与 select 语句的负载均衡
  • 2：所有读操作都随机在 writehost、readhost上分发
  • 3：所有读请求随机分发到 writerhost 对应的 readhost 执行，writehost 不负担读压力

因此可以根据实际情况选择上面合适的读写分离策略。

3 什么情况下会出现主从延迟

在本节的开始，我们说到，对于读写分离场景，最大的问题就是：主从延迟。那么在哪些情况下会出现主从延迟呢？这里大致总结一下可能导致主从延迟的场景：

  • 大表 DDL
  • 大事务
  • 主库 DML 并发大
  • 从库配置差
  • 表上无主键
  • 等等

因此，如果存在读写分离的情况，应尽量避免上诉情况在业务高峰出现。

当然，我们不能完全杜绝主从延迟。因此再介绍几种读写分离场景下应对延迟的方法。

4 读写分离怎样应对主从延迟

读写分离场景应该怎样应对主从延迟呢？这里来讨论一下几种常见的应对主从延迟的方法：

4.1 判断主从是否延迟

有些业务场景，如果所有请求都落在主库，主库压力会很大，但是在读写分离的情况，又不希望主从存在延迟的时候去读取从库。这种情况，就可以考虑查询时，先判断主从是否存在延迟，如果存在延迟，则查询落在主库，如果没延迟，则查询语句落在从库。

这里介绍几种判断主从延迟的方法：

第一种方法：判断 Seconds_Behind_Master 是否等于 0。

如果 Seconds_Behind_Master =0，则查询从库，如果大于 0，则查询主库。

这里补充一下 Seconds_Behind_Master。
Seconds_Behind_Master 是在从库上执行 show slave status 时返回的其中一项，表示从库延迟的秒数。
其计算方法是：
从库服务器当前的时间戳与二进制日志中的事件的时间戳（在主库上的写入时间）相对比得到的。

但是某些情况下，Seconds_Behind_Master 并不一定准确。比如网络中断时，Seconds_Behind_Master = 0 ，并不能代表主从无延迟。因此，有比这个更准确的一种方法：对比位点或 GTID。

第二种方法：对比位点或 GTID

如果 Master_Log_File 跟 Relay_Master_Log_File 相等，

并且 Read_Master_Log_Pos 跟 Exec_Master_Log_Pos 相等，

则可以把读请求放到从库，否则读请求放到主库。

补充一下上面几个参数的意义：
几个参数均是通过 show slave status 返回的参数，用来查询主从复制的状态。
Master_Log_File：IO 线程正在读取的主库 binlog 文件名
Relay_Master_Log_File：SQL 线程最近执行的事务对应的主库 binlog 文件名
Read_Master_Log_Pos ：IO 线程正在读取的主库 binlog 文件中的位点
Exec_Master_Log_Pos ：SQL 线程最近读取和执行的事务对应的主库 binlog 文件中的位点

如果开启了 GTID 复制，则可以对比 Retrieved_Gtid_Set 和 Executed_Gtid_Set 是否相等，相等则把读请求放到从库，有差异则读请求放到主库。

同样补充下两个参数的意义：
前提是需要开启 GTID 两个参数才会有值，解释如下：
Retrieved_Gtid_Set：从库收到的所有日志的 GTID 集合
Executed_Gtid_Set：从库已经执行完的 GTID 集合

4.2 采用半同步复制

在本节的前面，我们讲解了半同步复制的原理，跟传统的异步复制相比，半同步复制保证了所有给客户端发送过确认提交的事务，从库都已经收到这个日志了。因此出现延迟的概率会小很多，当然实际生产应用时，建议结合上面讲的位点或 GTID 判断。

4.3 等待同步完成

依然采用 4.1 中介绍的几种判断是否有延迟的方法，只是应对方式不一样，比如存在延迟，则将情况反馈给程序，在前端页面提醒用户数据未完全同步，如果没有延迟，则查询从库。

有人可能会觉得：这种方式谁会用啊？实际可以应用在内部人员看的报表业务上。因为报表可能涉及的 SQL 都比较复杂，存在延迟就考虑去查询主库，可能会对其它线上业务有影响，因此可以等待从库同步完成，再查询从库。

文章作者: Kiba Amor
文章链接: https://kibazen.cn/yi-xian-shu-ju-ku-gong-cheng-shi-dai-ni-shen-ru-li-jie-mysql-xue-xi-bi-ji/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY-NC-ND 4.0 许可协议。转载请注明来源 Kiba Amor !
职业规划 慕课专栏
  目录