MySQL索引结构
在开始MySQL索引相关的内容之前, 有必要先介绍一下MySQL的索引结构和索引的实现原理. 在理解这些内容之后, 很多MySQL索引的特性都是显然的.
B+树
总所周知, 数据库中一般采取B+树的结构来存储数据. 一颗B+树一般具有如下的结构:
B+树是一种多分支的树, 除去B+树常规的定义以外(即什么是B+树, 可以具有多少个分支等等), B+树最显著的特点是仅叶子节点具有数据, 而中间节点只存储索引值.
相比于平衡二叉树和红黑树之类可以自动调整的树, 由于B+树每一个节点的分支更多, 因此同样数量的节点时, B+树的深度更低. 由于磁盘的IO操作速度远远低于内存的读写次数, 因此更低的树深度能够减少IO次数, 从而提高数据的读取速度.
B+树在每个叶子节点上添加了一个指向下一个数据的指针, 可以根据这个指针顺序的遍历数据, 这有助于数据库进行范围遍历操作.
主键索引(聚族索引)
InnoDB采取聚族索引的方式组织数据, 即数据行和索引的键紧凑的存储在B+树的叶子节点中, 这也就是聚族的含义. 由于InnoDB的这种数据存储方式, 每个数据表都必须要有一个主键, 如果没有规定, MySQL也会自动生成一个主键来组织数据.
二级索引(非聚族索引)
因为不可能把全部数据同时放到两个B+树中, 因此一个表只能有一个聚族索引, 其他的索引称为二级索引. 二级索引也是一颗B+树, 只不过其叶子节点仅包含索引值和此数据的主键值. 根据二级索引查询数据时, MySQL首先查询二级索引获得主键值, 然后根据主键值在聚族索引上再次查询.
InnoDB采取这一架构的主要原因是, 当数据位置发生变化时, 只需要修改聚类索引, 而不需要修改二级索引, 从而降低索引维护成本.
覆盖索引
由于二级索引中包含了索引的列的数据, 因此如果只需要这一列数据, 则MySQL可以跳过根据聚族索引再次查询的操作, 从而提高查询效率. 这种情况称为覆盖索引. 当MySQL能够使用覆盖索引时, 使用EXPLAIN指令可以看到Extra: Using Index的提示.
与此相反, 如果MySQL需要根据索引获取一段范围内的数据, 且需要的数据没有被索引覆盖, 则MySQL需要不断重复的从索引获取主键值, 再根据主键值获取数据的过程. 这一操作造成了大量的随机I/O, 因此有可能性能还不如直接全表遍历.
由于索引存储了一部分的数据, 因此如果建立太多的索引, 会导致维护成本和存储成本快速上升
索引下推
索引下推技术主要是为了减少数据库的回表操作次数. 例如给定如下的语句
1 | SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a' |
在不使用索引下推时, 只有key1 > 'z'这个条件可以使用索引快速定位, 之后存储引擎需要根据索引取出从z开始的所有记录, 然后每一条记录都需要在主表中进行回表操作取出所有字段.
如果使用索引下推, 则存储引擎可以拿到另外一个条件key1 LIKE '%a', 由于索引中本来就包含key1的全部信息, 因此可以在索引上直接判断是否满足条件, 从而对于不满足条件的数据, 可以直接避免一次回表操作.
联合索引
对多个列建立的索引称为联合索引. 此时索引中的数据会按照字段的先后顺序依次的排序. 例如存在三个字段A, B, C, 则先按照A进行排序, A字段值相同的多条记录按照B字段排序; A, B 字段值都相同的记录再按照C字段排序.
基于上面的这一特性, 就会产生左前匹配原则, 例如以下操作可以通过索引完成
- 可以使用全值匹配, 即给定A,B,C三个字段的值
- 范围匹配A的值
- 给定A的值的同时, 范围匹配B的值
而下面的这些操作无法通过索引完成
- 单独匹配C的值
- 单独匹配A和C的值
本质上, 就是操作能否选择一段连续的区域, 连续存储的区域才可以加速
唯一索引
当数据表中的一个字段要求唯一时, MySQL就会对该字段建立一个唯一索引. 唯一索引也是一颗B+树, MySQL在插入数据时, 会通过这个B+树来检查插入数据是否具有唯一性.
因为唯一索引也是一个索引, 因此在此字段上的查询操作也能够被索引加速或者获得覆盖索引的效果.
参考资料
索引使用技巧
使用独立的列
在Where语句中使用列的表达式将会导致无法使用索引, 例如
1 | SELECT * FROM user WHERE age + 1 > 10 |
如果在age上存在索引, 则上述语句将会导致索引无法被使用. 显然, 对上述表达式进行简单的代数变换就可以消除表达式.
前缀索引
如果索引中包含长度很大的字符串, 则维持这样的索引会带来很大的开销, 此时可以选择将字段的部分内容作为索引, 从而建立前缀索引.
多列索引
一个常见的错误是创建多个单列索引. 如果一个查询涉及多个条件, 但每个条件上都是单列索引, 那么实际上就等于只有一列使用了索引. 即使MySQL的优化器可以使用合并索引技术使用多个索引, 也还是会带来额外的性能开销, 甚至导致优化器错误估计代价, 使得性能还不如直接全表遍历.
涉及多个查询条件的情况下, 创建一个有合适顺序的多列索引才是正确的做法. 这里的顺序主要考虑B+树的结构, 使得精确匹配和范围匹配能够最大限度的利用索引.
冗余索引
能否索引覆盖将会极大的影响查询性能. 但过大的索引也会降低索引性能. 例如一个包含VARCHAR字段的索引就可能严重降低索引查询性能. 针对这一问题, 可以引入冗余索引, 即分别创建几个不同的索引替代一个单一的索引. 冗余索引虽然导致空间消耗和维护索引的成本提高, 但查询时可以分别使用不同的索引, 从而保证了查询效率不会降低.
查询性能优化
为什么查询会慢
不要获取不需要的列和行. 先获取数据在抛弃不需要数据的方式会浪费很多性能, 并且可能导致相关的语句无法被优化. (例如原本可以被索引覆盖, 但因为获取了不需要的列而导致了更多的无效操作)
不要重复查询同样的数据. 这样的场景可以使用缓存来降低数据库的压力.
如果一个查询需要扫描大量数据, 但只返回少量数据, 那么就应该考虑对这种查询进行优化.(MySQL并不能提供扫描了多少数据的精确指标, 因此关键是理解查询背后的行为). 可以考虑三种方式对这种查询进行优化
- 使用覆盖索引. 如果查询的列被覆盖, 则可以直接从索引获得数据, 减少扫描量
- 改变表结构, 增加单独的汇总表
- 重写查询
拆分查询
以往的开发实践认为, 在数据库层应该尽可能完成多的工作, 因为网络通信和优化的代价比较高. 但实际上, MySQL的网络开销并没有高到不可接受, 在合适的情况下, 将一个复杂的查询拆分为若干个子查询可能性能更好.
以删除数据为例, 如果一条指令需要删除大量数据, 那么很有可能需要锁定大量数据行, 导致其他查询受到影响. 此时将任务拆分为若干相同的小任务, 就可以降低这一操作对其他查询的影响.
将关联查询分割为几个单独的子查询, 并且在应用程序中执行连接操作, 有如下的一些好处
- 程序可以缓存数据, 从而减少了查询数据量
- 单独的子查询减少了锁竞争
- 应用层做连接可以使数据库更容易拆分处理
- 数据库连接操作可能对某些数据重复访问, 拆分为子查询能减少冗余查询
EXPLAIN语句详解
MySQL查询语句的性能可以通过EXPLAIN语句分析. 以下是一个查询语句的实例, 经过EXPLAIN语句后, 产生了如下图所示的结果
以下分别解释其中出现的各个字段的含义
| 列名 | 含义 | 列名 | 含义 |
|---|---|---|---|
| id | 选择标识符 | select_type | 表示查询的类型 |
| table | 输出结果集的表 | partitions | 匹配的分区 |
| possible_keys | 表示查询时, 可能使用的索引 | key | 表示实际使用的索引 |
| key_len | 索引字段的长度 | ref | 列与索引的比较 |
| type | 表示表的连接类型 | rows | 扫描出的行数(估算的行数) |
| filtered | 按表条件过滤的行百分比 | Extra | 执行情况的描述和说明 |
id字段
ID表示查询顺序, 具有如下特点
- 如果是子查询, ID递增, 因此ID越大, 越先执行
- ID相同时, 从上到下顺序执行
select_type字段
select_type表示查询类型, 具有如下的一些取值
| 名称 | 含义 | 名称 | 含义 |
|---|---|---|---|
| SIMPLE | 简单SELECT, 不使用UNION或子查询等 | PRIMARY | 子查询中最外层查询 |
| UNION | UNION中的第二个或后面的SELECT语句 | DEPENDENT UNION | UNION且操作取决于外面的查询 |
| UNION RESULT | UNION的结果 | DERIVED | 派生表的SELECT, FROM子句的子查询 |
| SUBQUERY | 子查询中的第一个SELECT, 结果不依赖于外部查询 | DEPENDENT SUBQUERY | 子查询中的第一个SELECT, 依赖于外部查询 |
| UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存, 必须重新评估外链接的第一行 |
type字段
对表的访问类型, 具有如下的一些取值
| 名称 | 含义 |
|---|---|
| all | 遍历全表以找到匹配的行 |
| index | 只遍历索引树(索引覆盖) |
| range | 根据索引值范围查询并回表查询 |
| ref | 根据索引获得主键值并回表查询 |
| eq_ref | 类似ref, 区别就在使用的索引是唯一索引 |
| const | 一次读取就可以获得数据 |
| system | const且只查询结果一行 |
| NULL | MySQL在优化过程中分解语句, 执行时不用访问表或索引 |
Extra字段
查询过程中使用的额外信息. 包括如下的一些取值
| 名称 | 含义 |
|---|---|
| Using where | 无法直接利用索引来完成查询, 需要在存储引擎层面以外再进行一次额外的过滤来获取所需的数据 |
| Using temporary | 表示MySQL需要使用临时表来存储结果集, 常见于排序和分组查询 |
| Using filesort | 查询中包含排序操作, 且无法利用索引完成 |
| Using join buffer | 在获取连接条件时没有可用索引, 需要连接缓冲区来存储中间结果 |
| Impossible where | where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果） |
| Select tables optimized away | 仅通过使用索引, 优化器可能仅从聚合函数结果中返回一行 |
| No tables used | 查询语句中使用from dual 或不含任何from子句 |
注意: 如果出现了Using join buffer, 那么可以考虑是否需要添加索引.
Extended Explain
有时候使用了EXPLAIN语句后, 会显示有warning, 此时如果执行
1 | SHOW WARNINGS |
会给出具体的细节, 其中可能包含MySQL优化器重写的SQL语句, 这些SQL语句有助于我们理解优化器如何对我们的查询语句进行优化.
参考资料与扩展阅读
以下的几篇文章中, 第一篇文章比较精炼的概括的EXPLAIN返回的各字段的取值与含义. 第二篇文章更为详细的介绍了各字段的取值和含义, 可以作为第一篇文章的补充.
最后更新： 2024年04月18日 13:26
版权声明：本文为原创文章，转载请注明出处
原始链接： https://lizec.top/2021/01/21/MySQL%E7%AC%94%E8%AE%B0%E4%B9%8B%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96/