MySQL索引（底层结构/索引类型）

喜欢收藏+点赞👍 谢谢

概述

建立索引就是维护一棵 B+ 树，联合索引就是由多个字段组成，且满足最左匹配

索引列可以是主键，也可以是唯一键，还可以是6字节的rowid

优缺点

优点

提高数据的检索效率，减少IO次数

加快分组和排序，降低CPU消耗

将随机IO变成顺序IO

缺点

创建、维护索引要耗费时间，所以，索引数量不能过多

索引是一种数据结构，会占据磁盘空间

对表进行更新操作时，索引也要动态维护

数据结构角度

B+Tree

一个节点上可存多个元素（减少IO次数，提高查询效率）

B+树有单向指针，mysql优化为双向（利于范围查找）

数据全在叶子节点，非叶子节点只存索引的键（查询性能稳定）

Hash表

对索引的 key 进行一次 hash 计算就可以定位出数据存储的位置

优点：查询效率高

不支持范围查找和排序

hash算法不合适会影响查询效率

需要大量的内存空间

扩展

二叉树、AVL树、红黑树为什么不行？

分支少且高度不可控

为什么没有使用B-Tree？

Innodb_page_size=16384，每次读取16kb的数据

B树非叶子节点也存储数据，IO次数比B+树多

物理存储角度

聚集索引：索引和数据存储在一块

主键索引的叶子结点存储的是键值对应的数据本身

辅助索引的叶子结点存储的是键值对应的主键键值

非聚集索引：索引和数据分开存储

主索引和辅助索引类似，只是主索引不允许重复，不允许空值

他们的叶子结点的key都存储指向键值对应的数据的物理地址

逻辑角度

主键索引

特殊的唯一索引，也叫主索引，根据主键建立的索引，不允许重复，不允许空值

唯一索引

索引列的值必须唯一，允许有空值

普通索引

没有唯一性限制的索引

全文索引

组合索引

最左优先，以最左边的为起点任何连续的索引都能匹配上

遇到范围查询(>、<、between、like%)后面的字段就会停止匹配

原理：先按照第一个字段排序，第一个字段相同就按照第二个排序

重要概念

回表

从某一个索引的叶子节点中获取聚簇索引的id值，根据id再去聚簇索引中获取全量记录

主键索引或者聚簇索引叶子节点就存储着数据，不需要回表

索引覆盖

从索引的叶子节点中能获取到全量查询列的过程

比如把查询的列作为联合索引的列值（优化点）

最左匹配

根据索引的顺序判断查询是否走索引

特殊情况

mysql内部有优化器，选择合适的顺序来执行（索引列不断开且有头）

当表中的全部字段都是索引列的时候，无论怎么查询都会用到索引

索引下推

在没有索引下推之前，先根据name去存储引擎拿到全量数据到server层，在根据age做数据过滤

mysql5.7后有了索引下推，则根据name，age两个索引去存储引擎筛选数据，将最终结果返回给客户端

索引下推指的是本应该在server层做的过滤操作，下推到存储引擎执行，提升数据到检索效率

client ➡️ server ➡️ 存储引擎

server连接器：管理连接，验证权限分析器：词法分析，语法分析，ast优化器：优化执行过程（cbo：基于成本的优化  rbo：基于规则的优化）执行器：跟执行引擎交互，执行具体的sql语句

避免索引失效

1、全值匹配、最佳左前缀法则2、范围查询以后的索引字段会失效3、尽量使用覆盖索引，减少select *的使用4、mysql在使用不等于(!=或<>时索引失效)5、is null，is not null 也无法使用索引6、like不要以通配符开头('%abc...')7、少用 or，字符串记得加单引号8、不在索引列上做任何操作(计算、函数、类型转换)

问题

哪些情况需要创建索引？

主键、外键

经常需要搜索的列

查询中作为排序、分组的字段

哪些情况不需要创建索引？

表数据太少

经常增删的列

有大量重复值的列

一个表最多16个索引，一般一张表不建议超过6个索引字段，最大索引长度256字节

B+树有两种查找方式

第一种是从上往下通过索引查找

第二种是从根节点开始随机查找

无法使用索引时会进行全表扫描（第二种）

注意

最左前缀匹配原则

尽量选择区分度高的列作为索引

索引列不能参与计算

尽量的扩展索引，不要新建索引