新标题:《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操作。
优化策略
- 尝试提升
sort_buffer_size
参数的值。 - 尝试提高
max_length_for_sort_data
参数的设置。 - 在使用
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的使用条件
- 如果表的访问类型为
range
、ref
、eq_ref
或者ref_or_null
可以使用ICP。 - ICP可用于InnoDB和MyISAM表,包括分区表的InnoDB和MyISAM表。
- 对于InnoDB表,ICP仅适用于二级索引,其目的是减少全行读取次数,降低I/O操作。
- 当SQL使用覆盖索引时,不支持ICP优化方法,因为此时使用ICP不会减少I/O。
- 相关子查询的条件不能使用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的使用场景
- 普通索引和唯一索引的选择主要考虑对更新性能的影响,建议优先选择普通索引。
- 普通索引与
change buffer
配合使用,对数据量大的表的更新优化效果明显。 - 如果更新后马上伴随对记录的查询,应关闭
change buffer
;其他情况下,change buffer
能提升更新性能。 - 由于唯一索引用不上
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,可手动赋值字段做主键,如通过总部数据库管理信息表来保证各门店主键不冲突。
最后:
“在这篇文章的结尾,我要向每一位读者表达诚挚的感激。你们的关注和反馈是我持续创作的动力源泉,我从你们身上汲取了无穷的灵感与力量。我会将这份鼓励铭记于心,继续在相关领域努力探索。感谢你们,我们或许会在某个时刻再度相逢。”