Mysql篇——索引

stonethrow
831 阅读10分钟

索引的优缺点

优点：

  • 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

底层数据结构

MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样

实现区别

MyISAM 引擎中，一律使用“非聚簇索引（非聚集索引） ”，也就是说叶子节点中的data存放的不是主键值，而是对应数据的地址，有回表的性能损失。

InnoDB 引擎中，以主键为索引时，叶子节点的data直接存放数据库主键值，称为“聚簇索引（聚集索引） ”，而其余的非主键索引都作为 辅助索引 ，辅助索引的 data 域存储主键值而不是当前索引列的值；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在辅助索引中也会存在回表（覆盖索引可解决）。

注意： Mysql官方文档中介绍，聚集索引其实就是指的主键索引，也就是说只有Innodb中的主键索引才能叫做聚集索引，其余均为非聚集索引。

B树& B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

B树& B+树两者有何异同呢？

  • B树所有节点都存放key+value

  • B+树只有叶子节点存放key+value，其余节点存放key

  • B+树在叶子节点间有一条引用链，在进行范围查找时只需要对链表进行遍历，不再需要从根节点到叶子节点进行二分查找

注意： 二叉树为什么这么快-> 使用二分查找，时间复杂度：O(logn)。

索引的类别

按照底层存储方式角度划分：

  • 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
  • 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分：

  • 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
  • 普通索引：仅加速查询。
  • 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
  • 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
  • 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

聚簇索引一定回表查询吗(覆盖索引)?

非聚簇索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

 SELECT name FROM table WHERE name='guang19';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是！如果 SQL 查的就是主键呢?

SELECT id FROM table WHERE id=1;

总结：一般索引运用于”where“条件，使用非聚集索引查询具体字段时，会发生回表。但如果将select后的字段也建立索引，就可以直接返回key里的值，无需根据主键回表

详情可见：MySQL 覆盖索引详解 - 掘金 (juejin.cn)

联合索引

考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

最左匹配原则

你可以认为联合索引是闯关游戏的设计 

例如你这个联合索引是 state/city/zipCode 

那么 state 就是第一关 city 是第二关， zipCode 就是第三关 

你必须匹配了第一关，才能匹配第二关，匹配了第一关和第二关，才能匹配第三关

索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

假设我们有一个名为 user 的表，其中包含 id, username, zipcode和 birthdate 4 个字段，创建了联合索引(zipcode, birthdate)。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `zipcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `birthdate` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username_birthdate` (`zipcode`,`birthdate`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;

# 查询 zipcode 为 431200 且生日在 3 月的用户
# birthdate 字段使用函数索引失效
SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;

没有索引下推之前：

  • 存储引擎层先根据 zipcode 索引字段找到所有 zipcode = '431200' 的用户的主键 ID，然后二次回表查询，获取完整的用户数据；
  • 存储引擎层把所有 zipcode = '431200' 的用户数据全部交给 Server 层，Server 层根据MONTH(birthdate) = 12这一条件再进一步做筛选。

有了索引下推之后：

  • 存储引擎层先根据 zipcode 索引字段找到所有 zipcode = '431200' 的用户，然后直接判断 MONTH(birthdate) = 12，筛选出符合条件的主键 ID；
  • 二次回表查询，根据符合条件的主键 ID 去获取完整的用户数据；
  • 存储引擎层把符合条件的用户数据全部交给 Server 层。

总结：索引下推可以减少回表次数，根据where里的查询条件筛选，避免回表的全盘扫描

索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

  • 最左匹配原则：建立联合索引时，索引数会对数据进行排列，abc的索引列如果只查询bc，相当于查询*bc， 索引失效
  • 扫描范围大导致失效：mysql会对扫描进行优化，如果索引多次回表，直接会优化为全表扫描
    • select * 可以走索引，当可能发生多次回表导致失效
    • Like% 匹配范围大，索引失效
    • order by和in 这两个就是对数据进行排序，也可能发生失效
  • 计算或者使用函数：索引树中存放的是列值，进行计算后值不匹配，但mysql8.0后添加了函数索引，解决了这个问题
  • 使用or：a or b，如果a走索引，但b没有，索引会失效，mysql5.6后引入索引下推，解决。

推荐阅读这篇文章：美团暑期实习一面：MySQl 索引失效的场景有哪些？open in new window。

MySQL执行计划分析

MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息。

需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN + SELECT 查询语句；

，执行计划结果中共有 12 列，各列代表的含义总结如下表：

列名含义
idSELECT 查询的序列标识符
select_typeSELECT 关键字对应的查询类型
table用到的表名
partitions匹配的分区，对于未分区的表，值为 NULL
type表的访问方法
possible_keys可能用到的索引
key实际用到的索引
key_len所选索引的长度
ref当使用索引等值查询时，与索引作比较的列或常量
rows预计要读取的行数
filtered按表条件过滤后，留存的记录数的百分比
Extra附加信息

如何分析 EXPLAIN 结果

type（重要）

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

  • system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
  • const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
  • eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
  • ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
  • index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
  • range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
  • index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
  • ALL：全表扫描。

key（重要）

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

Extra（重要）

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

  • Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
  • Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
  • Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
  • Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
  • Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
  • Using join buffer (Block Nested Loop) ：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

avatar
文章
阅读
粉丝