一篇讲透MySQL的索引

T的笔记本
202 阅读11分钟

目录

  1. 思维导图
  2. 主要内容
    • 定义
    • 聚簇索引/非聚簇索引
    • 分类
    • 数据结构
    • 应用

1. 思维导图

MySQL索引.png

2. 主要内容

2.1 定义

  • 索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

2.2 聚簇索引/非聚簇索引

  • 聚簇索引指的是一种数据存储方式。索引的数据结构是树，聚簇索引指的是索引和数据存储在一棵树上（紧凑地存储在一起），树的叶子节点就是数据，非聚簇索引的索引和数据不在一棵树上。
  • MyISAM只有非聚簇索引，Innodb每张表只能有一个聚簇索引，其他都是非聚簇索引。
  • 聚簇索引命中同时也找到了数据，非聚簇索引命中后根据索引对应的键值二次查找对应数据。

聚簇索引的优势

  1. 用到主键查询的场景性能更快：行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。
  2. 对于有相关性的，一定范围内的数据的数据的查询，因为会有页加载Buffer，所以可以做到几次读取数据页就拿到所有相关数据，用非聚簇索引可能每次查询都导致一次磁盘IO。（注：《高性能MySQL》中举的例子是实现电子邮箱时，根据用户id聚集数据，可以从磁盘读取少量数据页拿到用户的全部邮件，感觉有点太理想化了）

聚簇索引的代价

  • 新增/更新引起的页分裂问题：因为要保证按唯一键（通常是主键）顺序排列，当一条新记录因为主键需要插入到一个已经满了的页面时，会引起页分裂，导致表占用更多的空间，同时页分裂涉及到把记录拷贝到新的页，所以也增加了处理的时间。
  • 因为上一点，所以插入速度依赖于插入顺序，插入速度最快的是主键顺序插入，

2.3 分类

  • 单列索引
    1. 普通索引:MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值 和空值，纯粹为了查询数据更快一点。
    2. 唯一索引:用关键字 UNIQUE 定义，索引列中的值必须是唯一的，但是允许为空值，
    3. 主键索引:是一种特殊的唯一索引，在一张表中只能定义一个主键索引，用于唯一标识一条记录，使用关键字 PRIMARY KEY 定义，不允许有空值。
  • 组合索引
    1. 多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使 用，使用组合索引时遵循最左前缀集合。
  • 空间索引:
    1. 空间索引是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有四种， GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用 SPATIAL 关键 字。要求，引擎为 MyISAM，创建空间索引的列，必须将其声明为 NOT NULL。

2.4 索引数据结构

2.4.1 B+树

  1. 根节点页（1层）：目录页，存储目录项记录， 字段是主键值 + 页号， 主键值用来二分查找下面的页，页号用来对应到下一页
  2. 目录页（2层）：同样储存目录项记录， 字段主键值+页号
  3. 叶子节点（3层）：用户记录页，存储主键值+表记录
根节点目录页叶子节点
主键IDID + 页号ID + 页号表数据
辅助索引（单字段 name）name + 页号name + 页号name + id
辅助索引（多字段 name age）name + age +页号name + age +页号name + age +id

联合索引会先根据第一列排序，第一列相同的再根据第二列排序

  • 数据查询流程：

    1. 通过InnoDb数据页里的page directory, 每个槽中next_record后的第一条就是这个槽中id最小的一条数据，根据这个id二分查找到对应的槽，然后链表next指针往后遍历，直到叶子节点
    2. 为什么用双向链表，因为二分查找的过程中可能需要往前也可能需要往后，只有查到对应槽后的查找才是一直用next指针往后

  • B+树能存储多少数据量的计算

  1. 一个数据页按默认16K算， 一行数据按1k大小算，叶子节点一个数据页存储16条数据
  2. 索引字段按bigint 8byte算，指针按默认6byte算，一个节点14byte
  3. 目录页： 16kb * 1024 / 14byte = 1170节点
  4. 按一颗树三层结构：根节点页 -> 目录页 -> 叶子节点页 1170 * 1170 * 16 = 21902400 即2000w级别的数据量

2.4.2 HASH

  • 适合 key-value 键值对查询，无论表数据多大，查询数据的复杂度都是O(1)，且直接通过 Hash 索引查询的性能比其它索引都要高。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度很慢（实际上全表扫描了）。所以，哈希表结构适用于只有等值查询的场景。

2.4.3 Full-Text 索引

  • MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

  • 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

  alter table table_a add fulltext index content_tag_fulltext(content,tag);
  select * from table_a where match(content,tag) against('xxx');

2.4.3 不用其他数据结构的原因

  • 不用普通二叉树 普通二叉树存在退化的情况(顺序插入数据)，如果它退化成链表，相当于全表扫描。平衡二叉树相比于二叉查找树来 说，查找效率更稳定，总体的查找速度也更快。
  • 不用平衡二叉树/红黑树 平衡二叉树/红黑树每个节点只存储一个键值和数据，会导致树的高度增加，磁盘IO次数变多，查询效率变低。如果是 B+ 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快。
  • 不用B树
    1. IO性能：B树的非叶子节点也存有数据，如果经常访问的数据离根节点很近，在查询这种数据检索的时候会要比B+树快。对于其他场景，非叶子节点存有数据使得存储目录项记录的页存储的节点数量变少了，变向增加了IO的次数。
    2. 查找稳定性：B+树每次都要到叶子节点才能访问到数据，所以查找性能更稳定。
    3. 全表扫描性能：另外B+树的全表扫描只需沿着叶子节点遍历即可，B树需要遍历每一层，全表扫描性能更好。
    4. 排序查找性能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。

2.5应用

2.5.1 回表/索引覆盖

  • 回表: 从辅助索引中查到了主键，回主键索引中找到对应的数据列
  • 索引覆盖: 从辅助索引中查到的数据列足够满足查询的需求，直接返回不需要再回主键索引

2.5.2 匹配

  • 全值匹配: 查询条件中的列和索引中的列一致
假设联合索引 (card, name, balance) 
SELECT * FROM account WHERE card = 'A' AND name = 'A' and balance = 0;
//WHERE 子句中的几个搜索条件的顺序对查询结果是没有什么影响的，MySQL查询优化器会自动优化SQL语句，然后根据要使用的索引，来决定先使用哪个查询条件，后使用哪个查询条件
  • 匹配最左前缀: 对于联合索引，可以只使用左边的部分列，可以不用包含全部联合索引中的列，但只能是左边连续的列。如果只使用了中间的列，则用不上这个联合索引。
SELECT * FROM account WHERE card = 'A' AND name = 'A';

SELECT * FROM account WHERE name = 'A';//不会命中联合索引
SELECT * FROM account WHERE card = 'A' AND balance = 0; //只会使用到联合索引的第一列，会将所有 card=A 的数据查询到内存后再筛选出 balance=0 的数据
  • 匹配列前缀: 只匹配某一列的值的开头部分。
SELECT * FROM account WHERE card LIKE 'A%';//匹配 card 为 A 开头的记录
SELECT * FROM account WHERE card LIKE '%A'; //无法使用索引，因为并不知道 A 结尾之前的顺序，所以就没办法使用索引。
  • 匹配范围值: 利用索引的有序性，可以非常方便的查找在某个范围内的记录。
SELECT * FROM account WHERE card > 'A' AND card < 'H';

如果对联合索引多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用上索引。因为第一列使用范围查询后，第二列并不是有序的，要知道是在第一列值相同的情况下，才用第二列排序。 例如下面的查询，先查询了 card 在 (A，B) 之间的记录，此时可能会有多条 card 不同的记录，所以这些记录中的 name 并不是有序的。所以需要先找到 card 在 (A, B) 之间的记录，再一条条过滤出 name > A 的记录。所以这个查询只用到了 idx_cnb 索引的 card 列，没用到 name 列。

SELECT * FROM account WHERE card > 'A' AND card < 'H' AND name > 'A';

如果左边的列是精确匹配的，后面的列是范围查询则可以用上索引，因为左边的列精确匹配后，后边的列就是排好序的。

SELECT * FROM account WHERE card = 'A' AND name > 'A'; //用上card、name 两列。
  • 排序和分组: 使用 ORDER BY 子句来对记录排序，索引不命中时，数据库只能把记录都加载到内存中，再用一些排序算法在内存中对这些记录进行排序。有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能要使用磁盘空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（filesort），文件排序的性能一般就比较低了。 如果 ORDER BY 子句里使用到了索引列，就有可能省去在内存或文件中排序的步骤。 例如下面的查询就会使用到索引，因为 card，name 已经排好序了，这个查询就可以直接从联合索引中提取数据，然后回表查询。
SELECT * FROM account ORDER BY card, name;

ORDER BY 也可以只使用部分的B+树索引列，当联合索引左边列的值为精确匹配时，也可以使用后边的列进行排序。例如下面的查询：

SELECT * FROM account ORDER BY card, balance;
SELECT * FROM account WHERE card='A' ORDER BY name;

使用联合索引进行排序时，要求各个排序列的排序顺序是一致的，要么各个列都是ASC升序，要么都是DESC降序。因为如果一个按 ASC 升序，一个按 DESC 降序，这与索引中的顺序始终都是反的，而且如果加上 LIMIT 之类的限制条件，只能排好序之后才能确定具体的记录。所以 MySQL 认为这种情况还不如文件排序来的快，就不会使用索引。

SELECT * FROM account ORDER BY name ASC, card DESC;

如果用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序，原因跟上面的是类似的。假设 acount 表还有其它列，例如下面的查询，country 列不属于 联合索引，所以这个查询排序也命中不了索引。

SELECT * FROM account ORDER BY name, country;

2.5.3 索引失效

  1. 查询的时候使用is null / not exists / not in
  2. 不适合重复数据较多的列
  3. 通过前缀模糊查询 like '%XX'或者like '%XX%'
  4. 如果MySQL预计全表更快的话，也不会使用索引
  5. 如果条件中有 or ，只要 or 条件中有一个条件没有创建索引，也不会使用索引（因为没有索引的那个列按理说是要进行全表扫描的），如果想用 or，又想让索引生效，那就在条件上都加上索引。
  6. 索引列使用了函数也会使索引失效
  7. 索引列参与计算也会使索引失效
  8. 类型隐式转换
  9. 两列做比较或是不等于比较（!= <>）也会使索引失效
  10. order by导致索引失效，排序是会扫描全表排序
avatar
文章
阅读
粉丝