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_datasort_buffer_size参数的设置。where条件的效率比having高,能在where中限定的条件就不要放到having中。要减少order by的使用,与业务沟通能不排序就不排序,或者将排序操作放到程序端进行。因为order bygroup bydistinct这些语句较为耗费CPU,而数据库的CPU资源较为宝贵。对于包含order bygroup bydistinct等查询的语句,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值是否正确,将符合条件的记录加入结果集,直到索引树上的记录不满足条件为止。使用index2email前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. 如果表的访问类型为rangerefeq_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,可手动赋值字段做主键,如通过总部数据库管理信息表来保证各门店主键不冲突。

最后:

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

版权声明:程序员胖胖胖虎阿 发表于 2025年7月10日 上午11:34。
转载请注明:MySQL索引与查询优化策略探索 | 胖虎的工具箱-编程导航

相关文章

暂无评论

暂无评论...