ReturnAC

Mysql索引

转载来源：字节跳动技术团队open in new window （部分截取）

一句话简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的数据结构

哈希、B树、B+树

哈希

哈希索引结构类似hashmap，仅能满足 等值查询，不支持范围查询。

B树

B树是一个平衡多路查找树，B为Blance，是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B树之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree的结构如下图：

特点

  • 关键字集合分布在整棵树中；
  • 任何一个关键字出现且只出现在一个结点中；
  • 搜索有可能在非叶子节点结束；
  • 其搜索性能等价于在关键字全集内做一次二分查找；

问题点

传统⽤来搜索的平衡⼆叉树有很多，如 AVL 树，红⿊树等。这些树在⼀般情况下查询性能⾮常好，但当数据⾮常⼤的时候它们就⽆能为⼒了。原因当数据量⾮常⼤时，内存不够⽤，无法将全部数据读入内存，⼤部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。⼀般⽽⾔内存访问的时间约为50 ns，⽽磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中⽐较的时间。这说明程序⼤部分时间会阻塞在磁盘 IO 上。而B树数据存储在各个节点上，那么每次读入内存的信息就比较有效，一次查询可能产生很多次IO， 那么我们如何减少磁盘 IO 次数，于是有B+树。

B+树

B+树是在B树基础上的一种优化，使其更适合实现存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+树相对于B树有几点不同：

  • 非叶子节点只存储键值信息。
  • 所有叶子节点之间都有一个链指针。
  • 数据记录都存放在叶子节点中。

B+树的结构如下图：

总结一下这种结构的优点：

  • B+ 树的层级更少： 相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快
  • B+ 树查询速度更稳定： B+ 所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定
  • B+ 树支持范围查询： 叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针
  • B+ 树天然具备排序功能： B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高
  • B+ 树全节点遍历更快： B+ 树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2 ~ 4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1 ~ 3次磁盘I/O操作。

索引的分类

索引主要有两种分类方式：物理分类和逻辑分类

物理分类：聚集索引与非聚集索引

B+树索引可以分为聚集索引和非聚集索引，这里不是指单独的索引类型，而是一种数据存储的方式。上面的B+树示例图为聚集索引。

聚集索引（聚簇索引）

存储记录是物理上连续存在，物理存储按照索引排序，所以一个表最多只能有一个聚集索引，Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚集索引。聚集索引的B+树中的叶子节点存放的是整张表的行记录数据。

非聚集索引（非聚簇索引）

非聚集索引是逻辑上的连续，物理存储并不连续，数据在物理存储不按照索引排序。

非聚集索引索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据，这个过程称为回表。

PS： Innodb里非主键索引又被称为二级索引、辅助索引，均属于非聚集索引

逻辑分类：主键索引、唯一索引、普通索引

主键索引： 一张表只能有一个主键索引，不允许重复、不允许为 NULL；

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

唯一索引： 数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是联合索引，则列值的组合必须唯一。

ALTER TABLE `table_name` ADD UNIQUE KEY key_name ( `column` )

普通索引： 一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

  • 单列索引： 一个索引只包含一个列
  • 联合/复合/组合索引： 一个组合索引包含两个或两个以上的列
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

Mysql中key和index的区别

在表的定义中经常看到key和index，但是使用中可能压根不会注意这个问题，因为大多数情况下他们展示出来的效果都差不多，但是还是不能将他们划等号（至少理论上是这样）

索引（index）和约束（key）的区别主要在于二者的出发点不同，索引(index)负责维护表的查找和操作速度，约束(key)负责维护表的完整性。

而有这个困惑的话，很可能是由于MySQL中有一个奇怪现象：

  • MySQL中的索引是约束性索引（即创建索引自动也会创建约束）
  • 并且MySQL中创建约束也会自动附带索引。

背后的原因：

MySQL中的约束效果是通过索引来实现的，MySQL数据库判断是否当前列是否unique就是通过unique索引判断的。

总结一下

key：

  • 等价普通索引 key 键名 (列)

primary key：

  • 约束作用（constraint），主键约束（unique，not null，一表一主键，唯一标识记录），规范存储主键和强调唯一性
  • 为这个key建立主键索引

unique key：

  • 约束作用（constraint），unique约束（保证列或列集合提供了唯一性）
  • 为这个key建立一个唯一索引；

foreign key：

  • 约束作用（constraint），外键约束，规范数据的引用完整性
  • 为这个key建立一个普通索引；

最左匹配原则、覆盖索引、索引下推

最左匹配原则

MySQL 建立联合索引有最左匹配的原则，即最左优先：

如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；

如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引。也就是会先以最左边的字段顺序建立索引，再依次建立索引。

以上面的（a，b，c）索引来说，对于最左边字段a来说，a是有顺序索引。b是无序的，但（a，b）是有序的，也就是在a有序的基础上看，b也是有序的。同样（a，b，c）也是这样。

不符合最左原则会导致索引失效

以（a，b，c）索引为例

  • 查询条件中没有第一个字段

比如 where b = 2 ，因为建立索引树的时候，a是第一个，没有最左边的字段，即使后面的字段建立了索引，也无法命中。

  • 查询条件中，缺少第二个字段

比如where a = 1 and c = 2，通过a 字段可以匹配出一部分数据，但是没有b字段，就无法向下进行匹配。

  • 索引顺序（查询优化器）

如果索引顺序是a,b 但是查询语句是 where b=2 and a = 1，这时候索引也能命中。这是由于mysql查询优化器会自动调整where 的条件顺序。

  • 范围查询

比如where a = 1 and b > 100 and c = 2，此时 a b会走索引，c 不会走。

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。like 要注意一下：如果通配符% 不出现在开头，则可以走索引。

覆盖索引

如果 select 后面查询的字段都可以从这个索引的树中获取，而不用回表，这种情况一般可以说是用到了覆盖索引。在执行计划的 extra列里会有using index。

假设你定义一个联合索引CREATE INDEX idx_name_age ON t(name,age);

SELECT name,age from t where name='张三'

查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中，这样的查询就是覆盖索引查询。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

索引下推

索引下推优化（index condition pushdown），是MySQL5.6引入的一个优化，它可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

还以t表的联合索引（name, age）为例。

如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写为：

select * from t where name like '张%' and age = 10 and ismale=1;

无索引下推执行流程为

索引下推执行流程

联合索引的优点

介绍了上述的最左匹配原则、覆盖索引、索引下推，共同点都是基于联合索引，由此总结一下联合索引的优点：

减少开销： 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引： 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

筛选效率高： 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w。

有序： 索引本身是有顺序的，当要对索引字段排序时，那么查询到的数据天然就是有顺序的，减少了排序的开销

索引的维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护，当插入入一个新记录时，如果新插入的ID值在数据中间，就需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能就会受影响。

页分裂会产生表空间碎片

  • 影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
  • 可能导致查询扫描的IO成本提升，效率查询降低；

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，可将数据页做合并。即通过 optimize table 来重组文件。

产生表空间碎片的其他常见的原因

  • 记录被Delete，且原空间无法复用；
  • 记录被Update（通常出现在变长字段中），原空间无法复用；

自增主键 VS 非自增主键

自增主键的插入数据模式，正符合了递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，由于每次插入主键的值近似于随机，则会产生很多移动数据，页分列，进而造成了大量的碎片，大大影响性能。

应该创建索引的列

  • 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
  • 在经常需要搜索的列上，可以加快搜索的速度
  • 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
  • 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
  • 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
  • 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

索引失效的场景

CREATE TABLE `t` (
  `id` INT ( 11 ) NOT NULL,
  `city` VARCHAR ( 16 ) NOT NULL,
  `name` VARCHAR ( 16 ) NOT NULL,
  `age` INT ( 11 ) NOT NULL,
  `addr` VARCHAR ( 128 ) DEFAULT NULL,
  `id_card` INT ( 11 ) NOT NULL,
  PRIMARY KEY ( `id` ),
  KEY `city_name_age` ( city,name,age ) ,
  UNIQUE KEY `unique_id_card` ( id_card )
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4
  • 不满足最左匹配原则
  • 使用了select *
  • 索引列上有计算
  • 索引列使用了函数
  • 字段类型不同
  • like左边包含%
  • 列对比
  • 使用or关键字
  • Not in和not exists
  • Order by的坑

EXPLAIN

作用

Explain可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析所查询的语句或者表结构的性能瓶颈。 explain中的列

id列

该列为执行的顺序，每个号码，表示一趟独立的查询，id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

select_type列

查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。 复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、 union 查询

SIMPLE：简单查询。不包含子查询和union

PRIMARY：复制查询中的最外层的select DERIVED：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

SUBQUERY：包含在 select 中的子查询（不在 from 子句中）

UNION：在 union 中的第二个和随后的 select

UNION RESULT：从union临时表检索结果的result

union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL。

table列

这一列表示 explain 的一行正在访问哪个表。 当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL 性能优化的目标，得保证查询至少达到range级别，最好达到ref

NULL： mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

const： 表示通过索引一次就找到了，用于primary key 或者unique key的列与常量比较时，所以表中只有一条记录，查询速度快

system： 表只有一行记录，const类型的特例，一般很少出现，可以忽略

eq_ref：唯一性索引扫描，primary key 或者unique key 索引的所有部分被连接使用，最多只返回一条符合条件的记录。

ref： 非唯一索引说明，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行

range： 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index： 扫描全索引，一般是扫描某个二级索引，比all会快一些（index是从索引中读取的，而all是从磁盘中读取）

all： 全表扫描

possible_keys列

显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key列

实际使用的索引。如果为NULL，则没有使用索引。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

key_len列

这表示用到的索引字段的字节数，通过这个值可以算出具体使用了索引中的哪些列。 计算规则如下：

  1. 先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
  2. 如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘3，utf8mb4 要乘4，GBK要乘2
  3. varchar这种动态字符串要加2个字节
  4. 允许为空的字段要加1个字节
  5. 列类型key_len备注int4+1=5允许null 要+1int not null4varchar(30)not null utf830*3+2=92动态列类型 +2 *

如下city_user为city列和name列构成的联合索引，key_len=66（即416+2）可推断出查询使用了第一个列：city列来执行索引查找。

key_len=66（即416+2+416+2）可推断出查询使用了第一列和第二列：city、name列来执行索引查找。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名，指的是 “=”号后面的东西。

rows列

检查的行数，读取的行数越少越好。

filterd列

表示存储引擎返回的数据在server层（及其他过滤条件）过滤后，剩下多少满足查询的记录数量的比例 例如如下表数据：

name上无索引，需要扫描全表，共3行，rows=3，过滤后剩下1条，filtered=1/3 一个比较低filtered值表示可能需要有一个更好的索引

Extra列

这一列展示的是额外信息。常见的重要值如下：

  • Using index： 查询的列被索引覆盖，及覆盖索引的场景，不用回表

  • Useing where： 查询的where条件列未被索引覆盖

  • Using filesort： mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种无法利用索引完成的排序操作称为“文件排序”。这种情况下一般也是要考虑使用索引来优化的。

如按照age排序时，age无索引，会文件排序，按照city则不会产生文件排序

  • NULL： 查询的列未被索引覆盖，查询的where条件走了索引

  • Useing index condition： 查询的列不完全被索引覆盖，条件使用索引，是一个范围

  • Using temporary：mysql需要创建一张临时表来处理查询。
备案号：鄂ICP备2023006484号-1浏览量: 0
Last Updated: