Mysql索引原理及优化

zzy
385 阅读9分钟

Mysql 索引原理及优化

认识索引

索引是什么

索引是帮助Mysql高效获取数据的数据结构，类似于一本书前面的目录，能加快数据库的查找速度。

  • 优点：
    • 可以提高数据检索的效率，降低数据库IO成本
    • 降低对数据排序的成本
  • 缺点：
    • 占用磁盘空间
    • 降低更新表数据的效率。对表的增删查改操作，不仅需要更新数据的信息，还要更新对应索引信息

Mysql常用索引类型

  • 普通索引 INDEX
  • 唯一索引
    • 主键索引 PRIMARY KEY
    • 唯一索引 UNIQUE
  • 联合索引
    • 主键联合索引PRIMARY KEY、
    • 唯一联合索引 UNIQUE
    • 普通联合索引 INDEX

索引数据结构

Hash表

任意值(key)通过哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构。

hash查找的时间复杂度为O(1)，检索速度非常快。

image-20210314215221680

select * from test where id = 10;

哈希算法实现的索引虽然可以做到快速检索数据，但是办法做数据高效范围查找，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

select * from test where id > 10;

二叉搜索树

定义

  1. 若任意结点的左子树不空，则左子树上所有结点的值均不大于它的根结点的值。

  2. 若任意结点的右子树不空，则右子树上所有结点的值均不小于它的根结点的值。

  3. 任意结点的左、右子树也分别为二叉搜索树。

image-20210314215505658

二叉查找树的时间复杂度是 O(log n)，比如针对上面这个二叉树结构，我们需要计算比较 3 次就可以检索到 id=7 的数据，相对于直接遍历查询省了一半的时间，从检索效率上看来是能做到高速检索，也可以

优点
  • 高效检索
  • 范围搜索
缺点
  • 极端情况下树退化为链表，查找速率急剧下降

image-20210314220027040

平衡二叉搜索树

性质

  1. 左子树与右子树高度之差的绝对值不超过1

  2. 树的每个左子树和右子树都是AVL树

image-20210314222115388

平衡二叉树查找的时间复杂度为O(log n)，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

就这个特点来看，可能会觉得这就很好，可以达到二叉树的理想的情况了。然而依然存在一些问题：

树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作，树的高度就等于每次查询数据时磁盘 IO 操作的次数，当数据量大的时候，IO操作数量就会增加，严重影响查询速率。

B-树

多路平衡查找树

主要特点
  1. 平衡查找树
  2. 节点中存储着多个元素
  3. 节点中的元素包含键值和数据，所有节点都存储数据
  4. 搜索有可能在非叶子结点结束；
  5. 所有叶子节点都位于同一层，叶子节点之间没有指针连接

image-20210319114643359

最大的有区别是一个节点可以存多个数据，树的高度比平衡二叉树小很多，节点的减少，从而减少了IO查找的次数，大大提高了搜索速率。

举个查询的例子，在平衡二叉树中查数据和在B树中查数据的区别

image-20210315221039621

在平衡二叉树中查找15，需要经过4次IO

image-20210319114643359

而在B树中查找15，只需要经过2次IO，

第一次IO：读取第一层节点的数据，在内存比较，找出下一个节点地址

第二次IO：读取第二层节点的数据，找到目标数据

相对于平衡二叉树，比较的次数没有明显减少，但是IO的次数会大大减少

到这里看觉得B树已经比较理想了，但是还有优化的地方

image-20210315222841671

  1. 不支持范围查询的快速查找，如果想要查找10和35之间的数据，查找到10之后，需要回到上面的节点继续往右查找，查询效率有待提

  2. 非叶子节点也存了数据，随着行数据的增多，一次IO能获取到的键就会减少，IO次数相对就会多

B+树

作为B树的升级版，比B树主要多了两个特性

  1. 叶子节点才存数据，非叶子节点不存数据
  2. 叶子节点之间用双向指针连成一个链表

image-20210315224132103

等值查询：只能在叶子节点才能搜索完毕

范围查询：比如查大于5的数据，在叶子节点找到5数据后，通过链表可以高效地查到大于5的数据

Mysql索引实现

聚集索引（Clustered Index）

每个InnoDB表都有一个聚集索引 ，聚集索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚集索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚集索引。InnoDB创建索引的具体规则如下：

  1. 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚集索引。
  2. 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚集索引。
  3. 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚集索引。该ROWID字段会在插入新行时自动递增。

image-20210316180036179

select * from user where id = 18;

经过三次IO找到id=18的叶子结点的行数据

B+树存储数据量

​ 在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k，而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K，innodb的所有数据文件（后缀为ibd的文件），他的大小始终都是16384（16k）的整数倍。

image-20210317113518256

​ Mysql中一个结点的大小为一页，假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16KB（16*1024=16384 byte）16384/14=1170（索引个数）。

​ 假设一条数据为1k，一个叶子结点的可以存16行数，

  1. 高度为2的B+树，能存放1170（索引个数）* 16（叶子结点行数）= 18720条这样的数据记录。
  2. 高度为3的B+树可以存放：**1170（第一层索引个数）*1170（第二层索引个数）*16（叶子结点行数）=21902400（2千万）**条这样的记录

​ 所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据

非聚集索引（Secondary Index）

除了聚集索引之外另外加的索引叫非聚集索引，跟聚集索引主要的区别是叶子结点存的数据是键值而不是行数据。

image-20210316182246898

select * from user where age=19;

  1. 先在非聚集索引中经过三次IO

  2. 在非聚集索引中找到主键后，再从聚集索引中找到行数据，也是经过三次IO，所以总共经过6次IO，这个过程叫回表

联合索引

image-20210317215929012

select * from t_test where a=13 and b=16 and c =4;

image-20210317220547071

查找acd的过程

  1. 在第一节点比较， 前两个字符，ab < ac < ad，走中间的节点

  2. ab < ac，走右边的节点，在叶子节点比较最终找到acd

Mysql索引优化

explain关键字介绍

explain能解释mysql如何处理SQL语句，表的加载顺序，表是如何连接，以及索引使用情况，是SQL优化的重要工具。

explain select * from user where name ='name';

image-20210326101030762

1. id

​ id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

​ 执行顺序：

  1. id相同，执行顺序从上之下
  2. id不同，执行顺序从大到小
2. select_type

​ select_type 表示对应行是是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种

1）simple：简单查询。查询不包含子查询和union

2）primary：复杂查询中最外层的 select

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

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

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

6）union result：从 union 临时表检索结果的 select

3. table

查询的表

4. type

这个字段是我们优化要重点关注的字段，这个字段直接反映我们SQL的性能是否高效。

这个字段值较多，这里我只重点关注我们开发中经常用到的几个字

性能由好到差依次为：system>const>eq_ref>ref>range>index>all

  1. system：表只有一行记录，这个是const的特例，一般不会出现，可以忽略

  2. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。

    image-20210326152820729

  3. eq_ref：唯一性索引查找，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引。

    image-20210329104833017

  4. ref：非唯一行索引查找，返回匹配某个单独值的所有行

    image-20210326153548291

  5. range：检索给定范围的行，一般条件查询中出现了>、<、in、between等查询

    image-20210326154125715

  6. index：扫描索引树

    image-20210326154416592

  7. all：全表扫描，通常情况下这需要增加索引来进行优化了

5. possible_keys

​ 这一列显示查询可能使用哪些索引来查找。

6. key

​ 这一列表示Mysql实际用哪个索引去查询

7. key_length

​ 这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

image-20210329105843097

image-20210329105928180

8. ref列 这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的值有：const（常量），func，NULL，字段名

9. rows列 这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

10. Extra列 这一列展示的是额外信息。

  1. Using Index :

    这发生在对表的请求列都是同一索引里面字段的部分时，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录，是性能高的表现。

image-20210328173102307

  1. Using temporary

    mysql需要创建一张临时表来处理查询。

    remark字段没有索引，要用一个临时表要去重。

    image-20210328173817445

  2. Using where

    表示Mysql将对存储引擎检索的结果再进行过滤；

    根据索引name提取的结果还要根据remark字段过滤，过滤条件字段无索引

    image-20210328174414480

  3. Using filesort

    表示Mysql将会对结果使用一个外部排序，而不是按照索引排序，explain不会说明是在内存排序还是磁盘排序，两种方式都有可能

    image-20210331101535539

索引列的类型尽量小

索引也占一部分存储空间，比如id，一般用BIGINT自增类型，而不用varchar的uuid做主键，整数类型比字符串比较消耗的cpu小，整数类型比字符串占用空间小

基数小的列不必加索引

基数：单个列去重之后唯一键的数量叫做基数。

SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;

比如user有100条数据，name不同的值有100个，性别不同的值有2个，性别这列基数小，可以不加索引

加了索引搜索效率没有提高，mysql可能也不会走索引

image-20210317225823861

最左匹配原则

  1. 组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配

    在联合索引中，如果我们对（a, b, c)加了联合索引，就相当于加了，(a) (a, b) (a, b, c)三个索引

select * from t_test where a=13 and b=16 and c=4;  --走索引

select * from t_test where a=13 and b=16;  --走索引

select * from t_test where a=13;   --走索引


select * from t_test where a=13 and b=16 and c>4;  --走索引

select * from t_test where a=13 and b>16 and c=4;  --a b 走索引

select * from t_test where a>13 and b=16 and c=4;  --a走索引


select * from t_test where b=16 and c=4;  --不走索引

select * from t_test where c=4;  --不走索引
  1. 字符串的like用法也同理，在右边加%才能使用索引，可以在索引上从左到有开始查找
SELECT * FROM t_test WHERE d LIKE 'aa%'  --a走索引

SELECT * FROM t_test WHERE d LIKE '%aa%'  --不走索引

覆盖索引

当一个索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引.

设想有一个user表有其中有两个字段加了联合索引，name，age 有如下的查询语句:

select name,age from user where name = 'xxx'  --不用回表
select * from user where name = 'xxx' --要回表

当在非聚集索引上已经包含了所有的需要返回的列，就不用去主键索引上找到数据行返回了，可以提高搜索速率

索引失效场景

  1. 索引列使用了函数或计算

    select * from user where age+1=10;

  2. 使用了左模糊

    select * from user where name like '%xx';

  3. 使用OR查询的部分字段没有索引

    select * from user where name = 'xx' or remark = 'aa';

  4. 不符合最左前缀原则的查询

    select * from user where age = 1;

  5. 索引范围查询可能导致不走索引

    select * from user where age > 0;
select * from user where name in (......)
avatar
文章
阅读
粉丝