MySQL索引与查询优化策略探索

新标题：《MySQL索引与查询优化策略深度剖析》

7. filesort算法：双路排序与单路排序

当排序的字段不在索引列上时，filesort会存在两种算法：双路排序和单路排序。

双路排序（效率较低）

在MySQL 4.1之前采用双路排序方式。其大致过程是两次扫描磁盘来获取数据，先读取行指针以及order by列，之后对这些内容进行排序，接着再依据已排好序的列表，重新从磁盘中读取对应的数据并输出。具体来说，是先从磁盘获取排序字段，在buffer中完成排序操作，之后再从磁盘获取其他字段。由于需要对磁盘进行两次扫描，而IO操作较为耗时，所以在MySQL 4.1之后出现了改进的单路排序算法。

单路排序（效率较高）

从磁盘读取查询所需的所有列，按照order by列在buffer中进行排序，随后扫描已排序的列表并输出结果。它相较于双路排序减少了一次IO操作，将随机IO转变为顺序IO，但会占用更多空间，因为它把每一行都存储在内存中。不过单路排序也存在问题：在sort_buffer中，单路排序会比多路排序占用更多空间，因为它要取出所有字段，有可能出现取出的数据总大小超过sort_buffer容量的情况，这样就需要多次取数据进行排序（创建临时文件并多路合并），反而导致更多的I/O操作，得不偿失。

结论及相关问题

总体而言，单路排序优于双路排序，但单路排序存在空间占用大等问题，可能引发多次I/O操作。

优化策略

1. 尝试提升sort_buffer_size参数的值。
2. 尝试提高max_length_for_sort_data参数的设置。
3. 在使用order by时，避免使用select *，最好只查询所需的字段。

8. GROUP BY优化

GROUP BY使用索引的原则与order by大致相同，即便没有过滤条件用到索引，也能够直接运用索引。GROUP BY是先进行排序再分组，遵循索引建立的最佳左前缀法则。当无法利用索引列时，可增大max_length_for_sort_data和sort_buffer_size参数的设置。where条件的效率比having高，能在where中限定的条件就不要放到having中。要减少order by的使用，与业务沟通能不排序就不排序，或者将排序操作放到程序端进行。因为order by、group by、distinct这些语句较为耗费CPU，而数据库的CPU资源较为宝贵。对于包含order by、group by、distinct等查询的语句，where条件过滤出的结果集最好保持在1000行以内，否则SQL执行会较慢。

9. 优化分页查询

优化思路一

在索引上完成排序分页操作，最后依据主键关联回原表来查询所需的其他列内容。例如：

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

优化思路二

该方案适用于主键自增的表，能够把Limit查询转换为某个位置的查询。例如：

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

10. 优先考虑覆盖索引

10.1 什么是覆盖索引？

理解方式一

索引是高效找到行的一种途径，通常数据库也能利用索引获取某一列的数据，所以无需读取整行。由于索引叶子节点存储了索引对应的数据，当通过读取索引就能获取所需数据时，就不需要再读取行。简单来说，一个索引包含了满足查询结果的数据就被称为覆盖索引。

理解方式二

非聚簇复合索引的一种形式，涵盖查询里的SELECT、JOIN和WHERE子句用到的所有列（也就是建索引的字段恰好是覆盖查询条件中涉及的字段）。简单来讲，就是索引列加上主键包含SELECT到FROM之间查询的列。
例如：

# 删除之前的索引
DROP INDEX idx_age_stuno ON student;
CREATE INDEX idx_age_name ON student(age, NAME);
EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

CREATE INDEX idx_age_name ON student(age, NAME);
EXPLAIN SELECT id,age,NAME FROM student WHERE NAME LIKE '%abc';

而下面这种情况则不会使用到索引，因为查询列多了classId：

EXPLAIN SELECT id,age,NAME,classId FROM student WHERE NAME LIKE '%abc';

10.2 覆盖索引的利弊（此处原文未详细展开，保留结构）

10.3 如何给字符串添加索引

有一张教师表，表结构定义如下：

create table teacher(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;

若email字段没有索引，查询语句就只能进行全表扫描。

10.4 前缀索引

MySQL支持前缀索引，默认情况下，若创建索引时不指定前缀长度，索引会包含整个字符串。例如：

mysql> alter table teacher add index index1(email);
#或
mysql> alter table teacher add index index2(email(6));

使用index1（整个email字符串的索引结构）时，执行流程为：从index1索引树找到满足索引值的记录，获取ID值，再到主键上查到对应行，判断email值是否正确，将符合条件的记录加入结果集，直到索引树上的记录不满足条件为止。使用index2（email前6个字符的索引结构）时，流程是从index2索引树找到满足索引值的记录，获取ID值，到主键上查对应行并判断email值，重复操作直到不满足条件。前缀索引能在节省空间的同时，尽量不增加过多查询成本，且区分度越高越好。

10.5 前缀索引对覆盖索引的影响

结论：使用前缀索引就无法利用覆盖索引对查询性能的优化，这是选择是否使用前缀索引时需要考虑的因素之一。

10. 索引下推

10.1 使用前后对比

Index Condition Pushdown(ICP)是MySQL 5.6引入的新特性，是一种在存储引擎层利用索引过滤数据的优化方式。

10.2 ICP的开启/关闭

默认情况下索引条件下推是开启的，可通过设置系统变量optimizer_switch控制：

# 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

# 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

当使用索引条件下推时，EXPLAIN语句输出结果中Extra列内容会显示为Using index condition。

10.3 ICP使用案例（简图部分保留结构）

10.4 开启和关闭ICP性能对比（保留结构）

10.5 ICP的使用条件

1. 如果表的访问类型为range、ref、eq_ref或者ref_or_null可以使用ICP。
2. ICP可用于InnoDB和MyISAM表，包括分区表的InnoDB和MyISAM表。
3. 对于InnoDB表，ICP仅适用于二级索引，其目的是减少全行读取次数，降低I/O操作。
4. 当SQL使用覆盖索引时，不支持ICP优化方法，因为此时使用ICP不会减少I/O。
5. 相关子查询的条件不能使用ICP。

11. 普通索引 vs 唯一索引

从性能角度考虑，该选择唯一索引还是普通索引呢？依据是什么呢？假设存在一个主键列为ID的表，表中有字段k且在k上有索引，字段k上的值都不重复，建表语句如下：

mysql> create table test(
id int primary key,
k int not null,
name varchar(16),
index (k)
)engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。

11.1 查询过程

以执行查询语句select id from test where k=5为例，普通索引在查找到满足条件的第一个记录后，需要查找下一个不满足条件的记录；而唯一索引因为定义了唯一性，查找到第一个满足条件的记录后就会停止检索。不过，这种不同带来的性能差距非常小。

11.2 更新过程

为说明普通索引和唯一索引对更新语句性能的影响，引入change buffer的概念。当需要更新数据页时，若数据页在内存中则直接更新，若不在内存中，InnoDB会将更新操作缓存在change buffer中，无需从磁盘读入数据页，下次访问数据页时再将change buffer中的操作应用到数据页。但唯一索引的更新无法使用change buffer，只有普通索引可以。例如插入新记录(4,400)时，InnoDB的处理流程会因索引类型不同而有差异。

11.3 change buffer的使用场景

1. 普通索引和唯一索引的选择主要考虑对更新性能的影响，建议优先选择普通索引。
2. 普通索引与change buffer配合使用，对数据量大的表的更新优化效果明显。
3. 如果更新后马上伴随对记录的查询，应关闭change buffer；其他情况下，change buffer能提升更新性能。
4. 由于唯一索引用不上change buffer的优化机制，若业务可接受，从性能角度优先考虑非唯一索引。但业务若不能确保，需以业务正确性优先，若业务要求数据库约束则需用唯一索引。在归档库场景，若数据无唯一键冲突，可将唯一索引改为普通索引以提高归档效率。

12. 其它查询优化策略

12.1 EXISTS 和 IN 的区分（原文未详细展开回答，保留结构）

12.2 COUNT(*)与COUNT(具体字段)效率（原文未详细展开回答，保留结构）

12.3 关于SELECT(*)

在表查询中，建议明确字段，不要使用*作为查询字段列表，应使用SELECT <字段列表>查询。原因在于：①MySQL解析过程中会通过查询数据字典将*转换为所有列名，耗费资源和时间；②无法使用覆盖索引。

12.4 LIMIT 1 对优化的影响

针对会扫描全表的SQL语句，若能确定结果集只有一条，加上LIMIT 1可在找到一条结果时停止扫描，加快查询速度。若数据表已对字段建立唯一索引，通过索引查询不会全表扫描，无需加LIMIT 1。

12.5 多使用 COMMIT

尽可能在程序中多使用COMMIT，这样可提高程序性能，减少资源消耗。COMMIT释放的资源包括回滚段用于恢复数据的信息、程序语句获得的锁、redo/undo log buffer中的空间以及管理这些资源的内部花费。

13. 淘宝数据库，主键如何设计的？

探讨淘宝数据库主键的设计情况，以往关于MySQL主键设计的一些错误观点流传甚广，比如认为用8字节的BIGINT做主键更好，这是错误的，因为仅从数据库层面考虑，未从业务角度出发。

13.1 自增 ID 的问题

自增ID做主键存在诸多问题：可靠性不高，存在回溯问题；安全性低，对外暴露的接口易被猜测信息；性能差，需在数据库服务器端生成；交互多，需额外执行last_insert_id()函数获取自增值，增加网络交互开销；局部唯一，非全局唯一，不适合分布式系统。

13.2 业务字段做主键

以会员信息表为例，考虑业务字段做主键的情况。选择会员卡号做主键不可行，因为可能出现卡号重复使用导致数据查询错误的情况；选择会员电话或身份证号做主键也存在问题，如电话可能被回收、身份证号涉及隐私等。所以建议尽量不要用与业务相关的字段做主键，更改主键设置成本较高。

13.3 淘宝的主键设计

淘宝订单表的主键不是自增ID，推测其订单ID设计为时间+去重字段+用户ID后6位尾号等组合，以实现全局唯一且便于分布式系统查询。

13.4 推荐的主键设计

非核心业务表的主键可采用自增ID；核心业务表主键应全局唯一且单调递增，推荐使用有序UUID。UUID全局唯一，但无序，MySQL 8.0可通过uuid_to_bin函数将其转化为有序UUID，兼具全局唯一和单调递增特点，性能和存储空间表现较好。若不是MySQL 8.0，可手动赋值字段做主键，如通过总部数据库管理信息表来保证各门店主键不冲突。

最后：

“在这篇文章的结尾，我要向每一位读者表达诚挚的感激。你们的关注和反馈是我持续创作的动力源泉，我从你们身上汲取了无穷的灵感与力量。我会将这份鼓励铭记于心，继续在相关领域努力探索。感谢你们，我们或许会在某个时刻再度相逢。”