【深入浅出Mysql】索引

奔跑向未来
188 阅读6分钟

这篇文章主要介绍mysql索引的相关知识。在介绍索引的数据结构时，以InnoDB引擎为例

索引数据结构：B+树

B+树是一种树形的数据结构，非叶子节点只存储索引值，叶子节点才存储数据的值，叶子节点从小到大有序，形成链表。

原来你是这样的B+树

这种结构有两个优点：

  1. 磁盘读取是以数据块为单位读的，B+树的结构刚好与磁盘的访问模式相适配。（这种结构能够让查询过程读尽可能少的数据块 即一次IO能读取更多的数据）

  2. 平衡区间查询和等值查询，写入和读取的时间。

基本概念

聚簇索引

聚簇索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚簇索引。聚簇索引的叶子节点存储的是实际的数据（也就是某一行数据）

非聚簇索引

表中的顺序与实际物理顺序是不一样的，叶子节点存储的也不是实际的数据，而是主键

覆盖索引（covering index）

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。

在覆盖索引的场景下，一条查询只需要访问索引就能够返回需要的字段，不必回表。

联合索引

1. 多个字段组成的索引

# 联合索引语法
create index 索引名 on 表名（字段名1，字段名2）

2. 遵循最左匹配原则

检索数据时从联合索引的最左边开始匹配，如果遇到范围查询，就会停止匹配。

3. 索引失效的几种场景

1. 如果不是按照索引的最左列开始査找,则无法使用索引。假如有两个字段A和B，对A和B做了联合索引 index(A,B),查询条件为 where B = XX，在这种场景下，无法利用改索引。

2. 跳过索引中的列。假如有三个字段A,B,C，对A,B,C建联合索引 index (A,B,C)，查询条件为where A = XX AND C = XX，在这种场景下，无法完全利用该索引。

3. 联合索引中的某个列存在范围查询，其右边的列无法使用索引查找。三个字段A,B,C，对A,B,C建联合索引 index (A,B,C)，查询条件为where A = XX AND B in (xx,xxx) AND C = XX，在这种场景下，该场景只能利用索引的前两列。而对于C的处理，取决于mysql的版本，mysql 5.6 之前的版本会直接回表查出所有的数据行，用字段C对数据行做过滤；mysql 5.6 以后的版本会利用索引下推，用字段C过滤掉不满足条件的索引，再回表。

4. 对字段做函数计算，这种场景不仅针对联合索引，普通索引也会失效。对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。如下面的case

# 对字段t_modified做来函数计算
select count(*) from tradelog where month(t_modified)=7;

# 这种情况对id字段mysql也用不上索引 需要你手动改成 id = 1000 -1 
select * from tradelog where id + 1 = 10000

# 假如tradeid是字符串类型，这个语句也不会使用索引 
# mysql 字符串和数字做比较的话，是将字符串转换成数字 即CAST(tradid AS signed int) = 110717;
select * from tradelog where tradeid=110717;

# 两个表字符集不同也会导致索引失效，因为会触发字符串的转换

4. 索引下推

如果索引包含了查询条件中（where）的字段， MySQL服务器层将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

5. order by

如果有order by的场景，且排序字段是联合索引的一部分，尽可能将排序字段放在联合索引的最后，避免出现file_sort。

正例：where a=? and b=? order by c; 索引：a_b_c

反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。

前缀索引

对字段的前缀进行索引，一般使用场景为某个字段长度太长，希望节省索引空间。 使用前缀索引注意区分度的问题，所选前缀长度区分度越高越好。

mysql> alter table table_name add index index_name(field_name(prefix_len));

全文索引

这种索引的使用场景是需要通过关键字的模糊匹配来进行查询。一般模糊匹配我们使用like + % 即可，但是文本较大时效率较低，这时应该使用全文索引。

alter table table_name
    add fulltext index index_name(filed_name);

主键索引与普通索引

主键索引的叶子结点存储的是数据的行，普通索引的叶子结点存储的是主键。唯一区别在于普通索引需要多回表一次

优化器对索引的选择

  1. 影响因素：如扫描行数，是否排序，索引区分度，回表代价等
  2. 索引的基数：采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
  3. 选错索引：
    1. 可能是索引的统计信息（扫描行数，基数）出现错误，可以使用 analyze table table_name 命令重新统计表的索引信息。统计出现错误的一个原因：delete一堆数据然后又马上insert mysql 来不及统计。
    2. 带有order by 查询，优化器尝试使用order by 字段上的索引进行优化

索引的重建

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

1. 重建普通索引

# 第一步
alter table T drop index k;
# 第二步
alter table T add index(k);

2. 重建主键索引

alter table T engine=InnoDB
//不论是删除主键还是创建主键，都会将整个表重建。

常见面试题

1. B+树和B树的区别

2. 行外存储

avatar
文章
阅读
粉丝