MySQL索引和优化

1 索引

1.1 索引算法

1.1.1 顺序查找

如果要在一组数据中找到对应的记录，通常是一个一个地扫描，直到找到对应的记录。

1.1.2 二分查找

二分查找是将记录顺序排列，查找时先将序列的中间元素作为比较对象 。如果要找的元素的值小于该中间元素的值 ，那么只需要在前一半元素中继续查找；如果要找的元素的值等于该中间元素的值，则匹配成功，查询完成；如果要找的元素的值大于该中间元素的值，那么只需要在后一半元素中继续查找。

1.1.3 二叉查找树

二叉查找树是将一组无序的数据构造成一查有序的树，其设计思想与二分查找的设计思想类似 。二叉查找树有如下几个重要的特性：

• 每个节点最多有两个子节点。
• 每个节点都大于自已的左子节点。
• 每个节点都小于自己的右子节点。

1.1.4 平衡二叉树

平衡二叉树是二叉查找树的改进版本，除了要满足二叉查找树的定义，还必须满足任意节点的平衡因子（两棵子树的高度差）的绝对值最大为1。

1.1.5 B树

B树可以理解为平衡二叉树的拓展，也是一棵平衡树，但是是多叉的。也可以把B树看成1个节点可以拥有多于2个子节点的多叉查找树。B树有如下几个特点：

• B树的每个节点存储的都是数据。
• B树的查询效率与键在B树的位置有关，最大时间复杂度与B+树的相同（数据在叶子节点上），最小的时间复杂度为1（数据在非叶子节点上）

1.1.6 B+树

B+树是B树的变体，其定义与B树的定义基本一致，与B树相比，B+树的具有以下的不同点

• B+树的键都出现在叶子节点上，可能在非叶子节点上重复出现。
• B+树的非叶子节点存储的都是键值，叶子节点才存储键值对应的数据。
• B+树的非叶子节点不存储数据，所以B+树比B树占的空间更多，但是B+树的非叶子节点具有索引的作用，所以B+树的查询效率比B树的查询效率更高。

1.2 索引类型

1.2.1 B+树索引

InnoDB引擎默认使用B+树索引，B+树索引是基于B+树发展起来的，通常在InnoDB上对某个字段添加索引，就是对这个字段构建一查B+树。

B+树索引，所有的数据都保存在叶子节点上，且叶子叶节是有序的，B+树索引使用指针把相临的叶子节点连接起来，最终所有的叶子节点形成了一个环形的双向链表，从而支持范围查找。

B+树索引，所有的数据都保存在叶子节点上，所以B+树索引的查询效率很稳定，所有数据的查询效率都是一样的。

B+树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层查找 。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义 了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

B+树索引支持以下类型的查找：

• 全值匹配：全值匹配指的是和索引中的所有列进行匹配。
• 匹配最左前缀 ：
• 匹配列前缀：也可以只匹配某一列的值的开头部分。
• 匹配范围值 ：
• 精准匹配某一列并范围匹配另外一列：

B+树索引也有如下的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引 。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则期右边所有列都无法使用索引优化查找。

1.2.2 哈希索引

Memory引擎默认使用哈希索引，哈希索引是基于哈希表实现的，只有精确匹配索引所有的列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键 值 的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用列的全部内容来计算哈希值。
• 哈希索引只支持等值比较查询，包括=、IN()、<>，也不支持任何范围查询。
• 访问哈希索引的数据非常快，除非有很多哈希冲突 。当出现哈希冲突时，存储引擎必须遍历链表中所有的行指针， 逐行进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代码也会很高。

1.3 索引策略

1.3.1 聚簇索引

聚簇索引一般是指主键索引，也称为一级索引，聚簇索引的叶子节点存储的是完整的数据行。

InnoDB通过主键聚簇数据，如果没有定义主键，那么InnoDB会选择第一个非空的唯一索引代替，如果没有非空的唯一索引，那么InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引占用的空间最大，因为它保存了全部数据。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

1.3.2 辅助索引

辅助索引一般是指非主键索引，也称为二级索引，辅助索引的叶子节点存储的是索引字段的值和主键ID。

在使用二级索引时，因为它只存储了索引字段的值和主键ID，所以需要查询其它列的数据时，就需要先通过二级索引中的值找到对应的主键，再通过主键找到聚簇索引中的其它列的数据，这个过程称为回表。

为了减少回表次数，可以将语句中经常使用到的所有列以合适的顺序建议一个二级联合索引，这样所有需要的列都被这个二级联合索引覆盖，就不需要回表。

1.3.3 唯一索引

唯一索引是一个不包含重复值的二级索引，一般是指基于唯一键创建的索引。

1.3.4 单列索引

单列索引是指基于单列创建的索引

1.3.5 多列索引

多列索引是指基于多列创建的索引，又称为联合索引

1.3.6 覆盖索引

如果一个索引包含所有需要查询的字段的值 ，我们就称这个索引为覆盖索引。

通过覆盖索引使得查询只需要扫描索引无须回表，能够极大地提高性能。覆盖索引的好处如下：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于IO密集型的应用也是有帮助的，因为索引比数据更小，更容易全部放入内存中。
• 因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁般读取一行数据的IO要少得多。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对聚簇索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B+树索引做覆盖索引。

当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到“Using index”信息。

1.4 索引操作

1.4.1 查看索引

可以通过以下SQL查看数据表上创建了哪些索引：

-- 查看表t_order上创建的索引
show index from t_order;
1
2

在执行SQL时，可以在SQL前面加上explain关键字查看SQL语句在执行的时候是否使用索引，以及使用了哪些索引，示例如下：

-- 查看用户10001的所有订单
explain select * from t_order where user_id = '10001';
1
2

1.4.2 创建索引

-- 基于create语句创建唯一索引和普通索引
create unique index `uk_order_id` on t_order(`order_id`);
create index `idx_user_id_created_at` on t_order(`user_id`, `created_at`);

-- 基于alter语句创建唯一索引和普通索引
alter table t_order add unique index `uk_order_id`(`order_id`);
alter table t_order add index `idx_user_id_created_at`(`user_id`, `created_at`);
1
2
3
4
5
6
7

1.4.3 删除索引

-- 基于drop语句删除索引
drop index `uk_order_id` on t_order;

-- 基于alter语句删除索引
alter table t_order drop index `idx_user_id_created_at`;
1
2
3
4
5

1.5 索引和锁

InnoDB在二级索引上使用共享（读）锁，在主键索引上使用排他（写）锁。这消除了使用覆盖索引的可能性，并且使用select for update比lock in share mode或非锁定查询要慢很多。

索引可以让查询锁定更少的行。如果查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销。其次，锁定超过需要的行会增加锁争用并减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层面能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句，这时已经无法避免锁定行了。

2 优化

2.1 参数调优

2.1.1 参数加载顺序

MySQL的配置文件名称为my.cnf，在单实例支行模式下，配置文件my.cnf的加载顺序为：

• 第一：/etc/my.cnf
• 第二：/etc/mysql/my.cnf
• 第三：SYSCONFDIR/my.cnf
• 第四：basedir/my.cnf

其中，SYSCONFDIR表示在安装源码时指定的配置文件的选项，如果没有指定就不加载。在启动MySQL服务的过程中，先按照顺序扫描这些位置的my.cnf文件，然后逐个加载这些配置文件，后面的配置文件会覆盖前面的配置文件的配置项。因此，配置完MySQL之后，应该检查这些地方的配置文件，最好只保留/etc/my.cnf这一个位置的配置文件，否则容易导致配置文件的修改失效。

2.1.2 常用参数调优

2.1.2.1 innodb_buffer_pool_size

由于MySQL中的InnoDB缓冲池主要存放InnoDB的数据缓存页面、索引缓存页面、自适应哈希索引、change buffer、数据字典、join buffer、查询缓存数据等，因此，innodb_buffer_pool_size参数对整个MySQL的性能具有非常重要的影响。

MySQL可以在线动态调整innodb_buffer_pool_size参数的大小，使用innodb_buffer_pool_resize_status参数可以查看调整innodb_buffer_pool_size参数的进度和状态。

show status like 'innodb_buffer_pool_resize_status;

set global innodb_buffer_pool_size = 4*1024*1024*1024;
1
2
3

针对单个实例来说，innodb_buffer_pool_size参数分配的内存占总实例内存的70%到80%为宜。调整innodb_buffer_pool_size参数值时必须遵循以下这种倍数关系，如果不满足下面的这种倍数关系，则MySQL会自适应地调整数倍数关系：

-- N为正整数
innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances * N
1
2

其中，innodb_buffer_pool_chunk_size 参数的默认值是128MB。当innodb_buffer_pool_instances参数的值大于1时，innodb_buffer_pool_size参数的值必须大于1GB。

通过以下公式计算innodb_buffer_pool_size参数的值设置得是否合理：

performance_read = innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%
1

其中，innodb_buffer_pool_read_requests参数表示从InnoDB缓冲池中读取的请求数，innodb_buffer_pool_reads参数表示未命中InnoDB缓冲池时从磁盘读取的请求数。如果performance_read < 90%，则可以考虑增加innodb_buffer_pool_size参数的值。

2.1.2.2 innodb_buffer_pool_instances

该参数表示InnoDB缓冲池被划分多少个不同的内存区，通过划分不同的内存区，可以减少读/写线程的锁资源争抢冲突，提高MySQL的并发性能。每个内存的页通过哈希算法分配 到这些内存区，每个内存区单独管理自己的LRU链接、free lists等。

综合各种测试结果，当innodb_buffer_pool_instances=8（默认值）时，吞吐量和稳定性较为理想。

2.1.2.3 character-set-server

该参数表示MySQL库表的默认字符符，不用特意指定，MySQL8.0已经默认为utf8mb4，utf8mb4是兼容utf8的。

2.1.2.4 connect_timeout

该参数作用于MySQL客户端和MySQL服务端建立连接阶段，即在建立三次握手之后，MySQL授权认证阶段。表示MySQL服务端等待MySQL客户端连接包的响应超时时间，建议设置为默认的10秒。

2.1.2.5 interactive_timeout

该参数作用于MySQL客户端和MySQL服务端建立连接后，是MySQL关闭交互连接前空闲等待的最长时间，默认为28800秒，由于交互式MySQL客户端一般也不需要这么久的保持会话，因此推荐设置为7200秒。

2.1.2.6 wait_timeout

该参数作用于MySQL客户端和MySQL服务端建立连接后，是MySQL关闭非交互连接前空闲等待的最长时间，在程序连接池模式下防止程序频繁的断开连接，建议设置为默认值28800秒。

2.1.2.7 net_read_timeout

该参数表示MySQL客户端在终止连接前，MySQL服务端等待传输数据的最长时间，建议设置为默认值30秒。

2.1.2.8 net_write_timeout

该参数表示MySQL客户端在终止连接前，MySQL服务端等待写入数据的最长时间，建议设置为默认值60秒。

2.1.2.9 lock_wait_timeout

该参数表示元数据锁的等待超时时间，在某些情况 下，元数据锁可能等待较长时间，所以建议设置为默认的3153600秒。

2.1.2.10 innodb_lock_wait_timeout

该参数表示InnoDB事务锁的等待超时时间，MySQL在做普通数据变更时，锁等待不宜过长，所以建议设置为默认的50秒。

2.1.2.11 lower_case_table_names

该参数表示表名是否启用区分大小写，1表示表名大小写不敏感，0表示大小写敏感，建议设置为1。

2.1.2.12 max_connections

该参数表示MySQL能够接受的最大连接数，超过最大连接数，就会报 “too many connections” 异常。需要指出的是，在MySQL8.0中增加额外端口参数admin_port，就可以通过在超过max_connections参数设置的最大连接数时，使用额外端口登录MySQL进行管理操作。

2.1.2.13 transaction_isolation

该参数表示事务的4种隔离级别设置，默认的隔离级别为REPEATABLE_READ，但是建议在生产环境中将隔离级别设置为READ_COMMITTED，因为在REPEATABLE_READ下存在较多的间隔锁，在READ_COMMITTED下，锁的范围更小，锁的时间更短，并且能够满足大部分生产业务的需要。

2.1.2.14 tmp_table_size

该参数表示临时表的缓存大小，一个线程中MySQL的临时表如果超过这个值，就会在磁盘上创建临时表。max_heap_table_size参数表示MEMORY内存引擎的大小，因为临时表也是属于内存表，所以也会受此参数的限制，如果要增加tmp_table_size参数的大小也需要同时增加max_heap_table_size参数的大小。因此，临时表的内存缓存大小取决于max_head_table_size参数和tmp_table_size参数的值中较小的那个。建议将tmp_table_size参数设置为默认值16MB，如果业务中有较多的group by聚合查询产生临时表，则可以相应地增大该值。

2.1.2.15 read_rnd_buffer_size

该参数表示在排序查询之后，保证以顺序的方式获取查询的数据。如果有很多order by查询语句，增加这个参数的值能够提升性能。在排序查询后，得到的是行数据指针，通过key-value的形式存在，对于MyISAM是数据的偏移量，对于InnoDB是主键或存储重新查询的全量数据。假设排序查询后的数据使用的是行指针，并且行中的数据能被转换成固定的大小，MySQL能够使用read_rnd_buffer_size参数优化数据读取。因为排序查询后的数据是以key-value的形式存在的，所以使用这些行指针读取数据，数据将以指针指向的顺序读取，导致在很大程序 采用随机方式读取数据的。MySQL先从sort_buffer中读取这些行指针数据，然后通过指针排序后存入read_rnd_buffer参数，后面再通过指针读取数据时，基本上都是顺序读取的。

2.1.2.16 sort_buffer_size

该参数表示每个连接在做排序时分配的一个内存。该参数面对的是所有的存储引擎，在业务排序操作中如果需要排序的字段和选择数据字段的数据量比较大，超过了sort_buffer_size参数的限制，就会使用磁盘文件进行排序。在通常情况下，建议设置为32MB。

2.1.2.17 slow_query_log

该参数表示Slow Log是否开启，一般建议将slow_query_log参数设置为1，表示开启。

2.1.2.18 long_query_time

该参数表示Slow Log慢查询时间阀值，一般建议设置为1秒。

2.1.2.19 log_queries_not_using_index

该参数表示SQL在未使用索引的情况 下会记录到慢日志中，建议打开。

2.1.2.20 expire_log_days

该参数表示Binlog的过期时间，建议设置为7天。

2.1.2.21 binlog_expire_logs_seconds

该参数表示Binlog的过期时间，建议设置为604800秒。

2.1.2.22 binlog_format

该参数表示Binlog记录行格式，有statement、row、mixed三种选择，建议选择row格式，因为不管从主从一致性角度来看，还是从数据恢复的角度来看，row格式都是比较合适的。

2.1.2.23 innodb_buffer_pool_dump_at_shutdown

该参数表示MySQL服务关闭时，是否记录InnoDB缓冲池中缓存的页面。

2.1.2.24 innodb_buffer_pool_load_at_startup

该参数表示MySQL服务启动时，InnoDB缓冲池通过加载它关闭时缓存的页面自动预热。

2.1.2.25 max-allowed-packet

该参数表示每行MySQL数据的最大值，当一行记录超过了限制的大小时就会报错。如果使用了长字符串类型，则建议将该值设置为1GB。

2.1.2.26 innodb_flush_neighbors

该参数表示InnoDB在刷脏页的时候，是否会把相邻的也刷到磁盘中，因为一般来说机械硬盘时代磁盘寻道会花费较长时间，这样就减少磁盘寻道时间，提升了效率。当然，刷相邻页获取收益是有前提的，就是针对insert操作较多的业务场景，刚好需要把相邻页也刷到磁盘，然而updater操作较多的业务场景，因为会把刷到磁盘的脏页数据又重新加载回磁盘，反而会增加磁盘IO开销。在MySQL8.0中，该参数已经默认设置为0，因为现在都是SSD磁盘，刷新相邻页的收益并不大，所以建议将此参数设置为0。

2.1.2.27 innodb_log_file_size

该参数表示Redo Log文件的大小，建议设置为2GB。

2.1.2.28 innodb_thread_concurrency

该参数表示在同一时刻能够进入 InnoDB的最大线程数，超过此设置就会进入线程排队中，建议保持默认值0，即不进行限制。

2.1.2.29 innodb_print_all_deadlocks

该参数表示是否将死锁信息都记录在Error Log中，建议打开。

2.1.2.30 innodb_strict_mode

该参数表示创建表、修改表、创建索引等，如果有语法错误则直接抛出错误，建议开启。

2.1.2.31 innodb_buffer_pool_dump_pct

该参数表示每个缓冲池读出和转储的最近使用页的百分比，此参数影响MySQL刷脏页的速度，所以设置的值不宜太大，太大的值就会导致脏页太多，从而影响写入性能，每次写入会强制刷脏页，建议设置为40。

2.1.2.32 log_timestamps

该参数控制Error Log、Slow Log、Genera Log日志记录的时间的时区，建议设置为system，因为默认为UTC，所以会使日志记录的时间比北京时间慢8个小时。

2.1.2.33 sync_binlog

该参数表示Binlog同步到磁盘的频率，生产环境中建议设置为1，表示每个事务都会将Binlog同步到磁盘，以保证数据的可靠性。

2.1.2.34 innodb_flush_log_at_trx_commit

该参数表示将Redo Log日志缓冲区写入到日志文件的的频率，生产环境中建议设置为1，表示每次提交事务时，日志缓冲区都会写入日志文件中，并在日志文件上执行磁盘刷新操作，以保证数据的可靠性。但是，在特殊情况下，可以将此参数设置为0或2，如主从同步延迟时，为了加快从库的同步速度，临时把从库设置为0或2。

2.1.2.35 innodb_flush_method

该参数表示MySQL将数据刷新到InnoDB数据文件和日志文件的方法，这可能会影响 IO吞吐量，有fsync、O_DSYNC、O_DIRECT等选项。O_DIRECT表示不经过系统缓存的方式，直接刷写到磁盘，可以减少刷写文件系统的开销。需要指出的是，这种刷盘方式只适用于数据文件，如数据页刷脏的情况，但是Redo Log日志刷盘的情况不使用O_DIRECT方式，因为Redo Log在innodb_flush_log_at_tx_commit=1的情况下每次写磁盘都要刷盘，而Linux内核direct IO的使用条件就是要求缓存区的起始位置和数据读/写长度必须是磁盘逻辑块大小的整数倍，也就是512字节，否则会导致 direct IO失败；而Redo Log刷写文件大小不能精确到512字节的整数倍，因此Redo Log在innodb_flush_log_at_tx_commit=1模式下不能使用O_DIRECT方式。

2.1.2.36 sql_mode

该参数表示SQL语法校验，建议的值为 strict_trans_tables、no_engine_substitution、no_zero_date、no_zero_in_date、error_for_division_by_zero、no_auto_create_user。其中strict_trans_tables表示值不能插入事务表中，会抛弃这个SQL；no_engine_substitution是指如果在使用create table语句和alter table语句时指定的engine项不被支持就会报错；no_zero_date是指’0000-00-00’这样的时间是否允许；no_zero_in_date是指’2022-00-01’这样的时间是否允许；error_for_division_by_zero是指在insert和update语句中，如果0做除数则报错；no_auto_create_user是指MySQL在创建空密码的用户时会报错。

2.2 SQL优化

2.2.1 分页查询优化

在业务场景中，经常会碰到翻页的需求，当遇到大的翻页查询时，如果使用 order by id limit m, n 的方式来进行分页，则偏移量m的值就会很大，因为这种方式会扫描前m行数据并丢弃，然后再返回后面n行数据，所以效率会很低。

例如下面的SQL语句会先扫描20000000行数据，然后再返回10条数据。

select * 
from t_order 
order by id 
limit 20000000, 10
1
2
3
4

优化方案1: 利用子查询

利用子查询也就是利用覆盖索引的方式先根据查询条件获取主键ID，然后再根据主键ID进行查询。

上面的SQL语句通过子查询的方式优化后的SQL如下。

select * 
from t_order 
where id >= (
	select id 
	from t_order 
	order by id 
	limit 20000000, 1
) 
order by id limit 10
1
2
3
4
5
6
7
8
9

虽然这里的子查询也使用了order by id limit m, n 的方式进行分页，但由于子查询中只涉及到id字段，所以只通过覆盖索引即可完成子查询，无需全表扫描，所以很快就能获取到分页起始id。然后父查询根据 id >= 分页起始id 就可以过滤掉前面的20000000行，从而不需要全表扫描前面的20000000行，所以速度很快。

优化方案2: 利用延迟join

利用延迟join也是利用覆盖索引的原理 ，先利用覆盖索引获取满足条件 的主键id，然后再和原表进行join。

上面的SQL通过延迟join的方式优化后的SQL如下。

select *
from t_order as a
join (
	select id 
	from t_order 
	order by id 
	limit 20000000, 10
) as b on a.id = b.id
1
2
3
4
5
6
7
8

虽然这里的子查询也使用了order by id limit m, n 的方式进行分页，但由于子查询中只涉及到id字段，所以只通过覆盖索引即可完成子查询，无需全表扫描，所以很快就能够获取到所要查询页的所有id，然后父查询根据这些id来进行join，从而不需要全表扫描前面的20000000行，所以速度很快。

优化方案3: 书签的方式

所谓的书签的方式，就是客户端记录上次查询分页时最后一行数据的id，下次分页查询时直接从这个id开始查询，从而避免MySQL扫描大量的数据再抛弃的操作。

2.2.2 not in优化

有的时候需要查询不包含某些条件的数据，这个时候可能会想到使用 not in 来实现，如果表中的数据量比较小，这种实现方式也是可以的，但如果数据量比较大，很容易导致慢SQL，因为使用了not in之后就不会走索引。

例如下面的SQL需要查询在 t_order 表中存在但是在t_order_fail 表中不存在的订单，即便是t_order表和t_order_fail表都在order_id字段上创建了唯一索引，但由于使用了not in，导致父查询不会走索引，而是全表扫描。

select id 
from t_order
where order_id not in (
	select order_id
	from t_order_fail
)
1
2
3
4
5
6

优化方式：利用left join

利用left join连接查询时，使用索引字段进行关联，并合理的利用覆盖索引，可以避免全表扫描。

上面的SQL通过left join优化后的SQL如下。

select o.id
from t_order as o
left join t_order_fail as f on o.order_id = f.order_id
where f.order_id is null
1
2
3
4

这里的连接查询使用唯一索引字段order_id进行关联，并利用覆盖索引，所以最终只需要扫描索引就可以完成查询，而不再需要全表扫描。

2.2.3 order by优化

order by 优化的核心就是尽量使用排序字段的索引，避免利用sort_buffer排序，在数据量较大以至于sort_buffer放不下的情况下，还要把数据从内存中转到磁盘中完成排序，性能会进一步恶化。尽可能使用表结构中的索引，其实就是MySQL排序优化的关键所在。

例如有以下表结构：

create table t_order (
	`id` int not null auto_increment,
	`a` int unsigned not null default 0,
	`b` int unsigned not null default 0,
	`c` varchar(32) not null default '',
	`d` int default null,
	primary key (`id`),
	key `idx_a_b_c` (`a`, `b`, `c`),
	key `idx_d` (`d`)
) engine=innodb default charset=utfmb4;
1
2
3
4
5
6
7
8
9
10

以下查询是可以用到索引 idx_a_b_c 的，MySQL不用再利用sort_buffer排序：

select * from t_order where a = 100 order by a;
select * from t_order where a = 100 order by b;
select * from t_order where a = 100 and b = 200 order by b;
select * from t_order where a = 100 and b = 200 order by c;
select * from t_order where a = 100 order by b asc, c asc;
select * from t_order where a = 100 order by b desc, c desc;
select * from t_order where a > 100 order by a;
select * from t_order where a = 100 and b > 200 order by b;
select * from t_order where a = 100 and b = 200 order by c asc, id asc;
select * from t_order where a = 100 and b = 200 order by c desc, id desc;
1
2
3
4
5
6
7
8
9
10

需要指出的是，idx_a_b_c(a,b,c)等价于idx_a_b_c(a,b,c,id)，最后面的id在非主键索引中是隐藏排序好的，不需要 再对其进行排序。

而以下查询是无法利用索引idx_a_b_c来完成排序：

-- 使用索引排序时不遵守最左前缀的原则，中间路过字段
select * from t_order where a = 100 order by c;

-- order by中的升降和索引中的默认升降不一致，无法使用索引排序
select * from t_order where a = 100 order by b asc, c desc;

-- 索引的前半部分是范围查询，后半部分是排序，无法使用索引排序
select * from t_order where a > 100 order by b;

-- 排序的字段顺序和索引中的字段顺序不一致，无法使用索引排序
select * from t_order by b, a;

-- 排序的字段在不同的索引中，无法使用索引排序
select * from t_order order by a, d;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.2.4 group by 优化

group by优化的核心也是尽量利用group by字段的索引。group by 字段如果不使用索引，一般会在值班表扫描后建立临时表。其中，每个组中的所有行都是连续的。如果不仅有聚合函数（如min()、max()、sum()等），还使用建立的临时表发现组并应用聚合函数，就会导致SQL语句执行得非常慢。因此，应该尽量避免group by字段不使用索引的情况。

例如有以下表结构：

create table t_order (
	`id` int not null auto_increment,
	`a` int unsigned not null default 0,
	`b` int unsigned not null default 0,
	`c` varchar(32) not null default '',
	`d` int default null,
	primary key (`id`),
	key `idx_a_b_c` (`a`, `b`, `c`),
	key `idx_d` (`d`)
) engine=innodb default charset=utfmb4;
1
2
3
4
5
6
7
8
9
10

以下的查询是可以用到索引来进行排序：

select * from t_order where a = 100 group by a;
select * from t_order where a = 100 group by b;
select * from t_order where a = 100 and b = 200 order by b;
select * from t_order where a = 100 and b = 200 order by c;
select * from t_order where a > 100 order by a;
select * from t_order where a = 100 and b > 200 order by b;
1
2
3
4
5
6

2.2.5 索引hint优化

在实际的生产环境中，索引过多、数据选择过多、索引选择性不好等因素都可能会导致 MySQL 选错索引，甚至不使用索引而全表扫描，由此出现慢查询，此时可以通过在SQL语句中选择 hint 来帮助 MySQL 选择对的索引。

最常用的 hint 方式就是use index、 force index 和 ignore index。

-- 通过 use index 希望使用 idx_create_time_status 这个索引
select * from t_shop a use index(idx_create_time_status)
where date(create_time-interval 6 hour) > '2020-01-01 00:00:00' and status = 'normal';

-- 通过 force index 强制使用 idx_create_time_status 这个索引
select * from t_shop a force index(idx_create_time_status)
where date(create_time-interval 6 hour) > '2020-01-01 00:00:00' and status = 'normal';

-- 通过 ignore index 禁止使用 idx_create_time 这个索引
select * from t_shop a ignore index(idx_create_time)
where date(create_time-interval 6 hour) > '2020-01-01 00:00:00' and status = 'normal';
1
2
3
4
5
6
7
8
9
10
11

3 执行计划

3.1 执行计划说明

3.1.1 查看执行计划

MySQL执行计划描述的是SQL语句真实执行的步骤和细节，要想查看一条SQL语句的执行计划，只需要在执行SQL的时候在SQL前面加上MySQL关键字explain即可，语法如下：

explain SQL语句;
1

示例如下：

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

mysql>
1
2
3
4
5
6
7
8
9

从上面的结果中可知，执行计划会返回 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra等字段。

3.1.2 执行计划作用

执行计划作为最常使用的慢日志分析工具，可以模仿MySQL优化器的执行计划，从而获取SQL的执行计划，用户可以从执行计划中得到如下信息：

• 多张表join时表的读取顺序
• 每张表的预估扫描行数
• SQL的查询类型
• 可能用到的索引情况
• 实际用到的索引
• 额外的一些信息，包括是否使用排序，以及是否使用覆盖索引等

3.2 执行计划解析

为了演示本节中查看Exlpain执行计划，需要先准备好以下2张数据表

create table `test1` (
	`id` int not null auto_increment,
	`video_id` int not null default 0,
	`media_id` int not null default 0,
	primary key (`id`),
	key `idx_video_id` (`video_id`)
) engine=innodb default charset=utf8mb4;

create table `test2` (
	`id` int not null auto_increment,
	`video_id` int not null default 0,
	`task_id` int not null default 0,
	primary key (`id`),
	key `idx_video_id` (`video_id`)
) engine=innodb default charset=utf8mb4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.2.1 id

执行计划结果中的id字段是select单个语句标识符，是select语句查询的有序序号，如果是union语句，那么这个结果可能为null。
如果id相同，那么执行顺序 为由上至下。
如果是子查询，那么id的序号会递增。id值越大优先级越高，越先被执行。

示例：

explain select * 
from test1 aa 
join test2 bb on aa.video_id = bb.video_id limit 1\G;
1
2
3

3.2.2 select_type

执行计划结果中的select_type字段是select子句的类型，主要有以下几种类型：

示例：

explain select * 
from test1 
where video_id in (
	select video_id from test2 where task_id = 1 
	union 
	select video_id from test1 where media_id = 2
)\G;
1
2
3
4
5
6
7

3.2.3 table

执行计划结果中的table字段是输出行所指向的表的名称，也可能是别名

3.2.4 partitions

执行计划结果中的partitions字段表示分区的名字

3.2.5 type

执行计划结果中的type字段表示join的类型，大概有以下几种类型，这些类型从All到NULL的性能越来越好

3.2.6 possible_keys

执行计划结果中的possible_keys字段表示可能选择的索引

3.2.7 key

执行计划结果中的key字段表示选择使用的索引

3.2.8 key _len

执行计划结果中的key_len字段表示索引字段的长度。常用字段类型的key_len如下所示

3.2.9 ref

执行计划结果中的ref字段表示列与索引的比较，即哪些列或常量被用于查找索引列上的值

3.2.10 rows

执行计划结果中的rows字段表示扫描出的行数，是个估算的行数

3.2.11 filtered

执行计划结果中的filters字段表示根据条件过滤后的百分比

3.2.12 extra

执行计划结果中的extra字段表示其它参考信息，这个参考信息其实也是比较重要的，如果想要查询高效，在出现Using temporary类型和Using filesort类型时应该格外关注，尽量不要出现这两种类型。

示例：

-- Using temporary示例
explain select * from test1 group by media_id\G;

-- Using filesort示例
explain select * from test1 order by media_id\G;

-- Using where示例
explain select * from test1 where media_id = 1\G;

-- Using index示例
explain select video_id from test1 where video_id = 1\G;

-- Using join buffer(Block Nested Loop)示例
explain select * from test1 aa join test2 bb on aa.media_id = bb.task_id limit 10\G;

-- Select tables optimized away示例
explain select min(video_id) from test2\G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17