MySQL索引深入剖析

　　本文链接：https://blog.csdn.net/weixin_43691723/article/details/105731224

　　收起

　　1. 索引是什么?

　　1.1 索引图解

　　数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

　　数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，我们要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据(循环调用存储引擎的读取下一行数据的接口)，直到找到这条数据。但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

　　1.2 索引类型

　　普通索引(Normal)：也叫非唯一索引，是最普通的索引没有任何的限制。

　　create index index_name on table(column(length));

　　alter table table_name add index index_name on column(length);

　　--创建表的时候直接指定

　　CREATE TABLE table(

　　ID INT NOT NULL,

　　username VARCHAR(16) NOT NULL,

　　INDEX [indexName] (username(length))

　　);

　　如果是CHAR，VARCHAR类型，length可以小于字段实际长度;如果是BLOB和TEXT类型，必须指定 length。

　　唯一索引(Unique)：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用primary key 创建。唯一索引的创建方式和普通索引的相似，只需加上在前面加上unique就行

　　全文索引(Fulltext): 针对比较大的数据，比如我们存放的是消息内容，有几KB的数据的这种情况，如果要解决like查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如char、varchar、text。MyISAM和InnoDB支持全文索引。

　　2.索引存储模型推演

　　2.1 二叉查找树(BST Binary Search Tree)

　　二叉查找树的左子树的所有节点都小于父节点，右子树所有的结点都大于父节点。投影到平面以后，就是一个有序的线性表。

　　二叉查找树既能够实现快速查找，又能够实现快速插入。

　　但是二叉查找树有一个问题：

　　就是它的查找耗时是和这棵树的深度相关的，在最坏的情况下时间复杂度会退化成O(n)。

　　什么情况是最坏的情况呢?

　　如果我们插入的数据刚好是有序的，2、6、11、13、17、22。它会变成链表(我们把这种树叫做“斜树”)，这种情况下不能达到加快检索速度的目的，和顺序查找效率是没有区别的。

　　造成它倾斜的原因是什么呢?

　　因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。

　　2.2 平衡二叉树(AVL Tree)

　　AVL Tree (Balanced binary search trees)

　　平衡二叉树的定义：任何一个父节点的左右子树深度差绝对值不能超过1。

　　按顺序插入1、2、3、4、5、6，一定是这样：

　　平衡二叉树在构建的时候，会对结点进行调整，保证平衡。

　　平衡的问题我们解决了，那么平衡二叉树作为索引怎么查询数据?

　　在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容?

　　它应该存储三块的内容：

　　第一个是索引的键值。比如我们在id上面创建了一个索引，我在用where id=1的条件查询的时候就会找到索引里面的id的这个键值。

　　第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。

　　第三个，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于26的时候，走右边，到下一个树的节点，继续判断。

　　如果是这样存储数据的话，我们来看一下会有什么问题。

　　首先，索引的数据，是放在硬盘上的。

　　当我们用树的结构来存储索引的时候，因为拿到一块数据就要在Server层比较是不是需要的数据，如果不是的话就要再读一次磁盘。访问一个节点就要跟磁盘之间发生一次IO。 InnoDB操作磁盘的最小的单位是一页(或者叫一个磁盘块)，大小是16K(16384字节)。

　　那么，一个树的节点就是16K的大小。

　　如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到16K的容量，所以访问一个树节点，进行一次IO的时候，浪费了大量的空间。

　　所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多。

　　每次从磁盘读取数据需要寻址时间，交互次数越多，消耗的时间就越多。

　　比如上面这张图，我们一张表里面有6条数据，当我们查询id=66的时候，要查询两个子节点，就需要跟磁盘交互3 次，如果我们有几百万的数据呢?这个时间更加难以估计。

　　所以我们的解决方案是什么呢?

　　第一个就是让每个节点存储更多的数据。

　　第二个，节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有更多的分叉(我们把它叫做“路数”)。

　　因为分叉数越多，树的深度就会减少(根节点是0)。

　　这样，我们的树是不是从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子?

　　这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

　　2.3 多路平衡查找树(B Tree)

　　跟AVL树一样，B树在枝节点和叶子节点存储键值、数据地址、结点引用。

　　它有一个特点：分叉数(路数)永远比关键字数多1。比如我们画的这棵树，每个结点存储两个关键字，那么就会有三个指针指向三个子节点。

　　B Tree的查找规则是什么样的呢?

　　比如我们要在这张表里面查找15，因为15小于17，走左边，因为15大于12，走右边。在磁盘块7里面就找到了15，只用了3次IO。

　　2.4 B+树(加强版多路平衡查找树)

　　B Tree 的效率已经很高了，为什么MySQL 还要对 B Tree 进行改良，最终使用了B+Tree呢?总体上来说，这个B树的改良版本解决的问题比B Tree更全面。

　　我们来看一下InnoDB里面的B+树的存储结构：

　　MySQL中的B+Tree有几个特点：

　　1)它的关键字的数量是跟路数相等的;

　　2)B+ Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。

　　目前的认知：我们这要存放的数据是什么?是不是真实数据的地址?

　　搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索id=28，虽然在第一层直接命中了，但是数据地址在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。

　　3)B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

　　InnoDB中的B+ Tree这种特点带来的优势：

　　1)它是B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题是什么?(每个结点存储更多的关键字;路数更多)

　　2)扫库、扫表能力更强(对表进行全表扫面，只需要遍历叶子结点就可以了，不需要遍历整颗B+ Tree拿到所有的数据)

　　3)B+ Tree的磁盘读写能力相对于B Tree 来说更强(根节点和枝节点不保存数据，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多)

　　4)排序能力更强(因为叶子节点上有下一个数据区的指针，数据形成了链表)

　　5)效率更加稳定(B+ Tree 永远是在叶子节点拿到数据，所以IO次数是稳定的)

　　2.5 索引方式：真的是用的 B+Tree 吗?

　　在Navicat的工具中，创建索引，索引方式有两种 Hash、B Tree(底层是B+ Tree)。

　　HASH：以KV的形式检索数据，也就是说，它会根据索引字段生成哈希码和指针，指针指向数据。

　　哈希索引有什么特点呢?

　　第一个，它的时间复杂度是O(1)，查询速度比较快。但是哈希索引里面的数据不是按顺序存储的，所以不能用于排序。

　　第二个，我们在查询数据的时候要根据键值计算哈希码，所以它只能支持等值查询(= IN)，不支持范围查询(> < >= <= between and)。

　　第三：如果字段重复值很多的时候，会出现大量的哈希冲突(采用拉链法解决)，效率会降低。

　　需要注意的是，InnoDB，不能显示地创建一个哈希索引(所谓的支持哈希索引指的是AHI)。

　　memory存储引擎可以使用Hash索引。

　　CREATETABLE`user_memory`(

　　`id`int(11)NOTNULL AUTO_INCREMENT,

　　`name`varchar(255) DEFAULT NULL,

　　`gender`tinyint(1) DEFAULT NULL,

　　`phone`varchar(11) DEFAULT NULL,

　　PRIMARYKEY(`id`),

　　KEY `idx_name`(`name`) USINGHASH

　　)ENGINE=MEMORY AUTO_INCREMENT=1 DEFAULT CHARSET = utf8mb4;

　　3.B+ Tree 落地形式

　　3.1 MySQL数据存储文件

　　不同的存储引擎，磁盘中的文件不一样。

　　show variables like 'datadir';

　　每张InnoDB的表有两个文件(.frm 和.ibd),MYISAM的表有三个文件(.frm、.MYD、.MYI)

　　3.2 MyISAM

　　在MyISAM里面，另外有两个文件

　　一个是.MYD文件，D代表是Data，是MYISAM的数据文件，存放表中的所有数据。

　　一个是.MYI文件，I代表的是Index，是MYISAM的索引文件，存放索引，比如我们在id字段上创建了一个主键索引，那么主键索引就是在这个索引文件里面。

　　也就是说，在MYISAM里面，索引和数据是两个独立的文件。

　　那么我们怎么根据索引找到数据呢?

　　MYISAM的B+ Tree里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。

　　如果是辅助索引，有什么不一样呢?

　　在MYISAM里面，辅助索引也在.MYI文件里面。

　　辅助索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据。

　　这个就是MyISAM里面的索引落地的形式。但是在我们的InnoDB里面是不一样的。

　　我们来看一下。

　　3.3 InnoDB

　　在InnoDB里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd文件里面。

　　在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据。

　　聚集索引(聚簇索引)：就是索引键值的逻辑顺序跟表数据行的物理顺序是一致的。(比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引)。

　　在InnoDB里面，它组织数据的方式叫做叫做(聚集)索引组织表(clusteredindex organize table)，所以主键索引是聚集索引，非主键都是非聚集索引(辅助索引)。

　　主键之外的索引，比如在name字段上面建的普通索引，又是怎么存储和检索数据的呢?

　　InnoDB中，主键索引和辅助索引是有一个主次之分的。

　　辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。

　　比如我们用 name 索引查询 name=‘青山’，它会在叶子节点找到主键值，也就是id=1，然后再到主键索引的叶子节点拿到数据。

　　如果一张表没有主键怎么办?

　　1)如果我们定义了主键(Primary Key)，那么InnoDB会选择主键作为聚集索引。

　　2)如果我们没有显示定义主键，则InnoDB会选择第一个不包含空值的唯一索引作为聚集索引。

　　3)如果也没有这样的唯一索引，则InnoDB会选择内置的字节长的ROWID作为隐藏的聚集索引，他会随之行记录的写入而主键递增。

　　4.索引使用原则

　　4.1 列的离散度

　　如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。如果数据重复值多的时候，查询的时候，需要扫描的行数就越高，所以，建立索引的时候，一般选择离散度高的字段。

　　4.2 联合索引的最左匹配

　　前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引，举例：查询成绩的时候必须同时输入身份证和考号。单列索引可以看成是特殊的联合索引。

　　比如user表给name和phone建立了一个联合索引。

　　ALTER TABLEuser_innodb DROP INDEX comidx_name_phone;

　　ALTER TABLEuser_innodb add INDEX comidx_name_phone(name,phone);

　　联合索引在B+ Tree中是符合的数据结构，它是按照从左到右的顺序来建立搜索树的(name在左边，phone在右边)。

　　从这张图可以看出来，name是有序的，phone是无序的。当name相等的时候，phone才是有序的。

　　这个时候我们使用where name=‘青山’ and phone = '136xx’去查询数据的时候，B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name相同的时候再比较phone。但是如果查询条件没有name，就不知道第一步应该查哪个节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。

　　4.3 什么时候会用到联合索引

　　查询条件只要包含最左边的字段，并且中间连续，都会使用联合索引，比如创建一个联合索引(a,b,c),包含三个字段

　　alter table add index comdix(a,b,c)

　　当条件中包含a、ab、abc 的时候都会用到联合索引，可以想象成一个索引桥。

　　4.4 覆盖索引

　　回表：非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

　　在辅助索引里面，不管是单列索引还是联合索引，如果select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

　　创建一个包含name、phone字段的联合索引，下面三个查询语句都用到了覆盖索引：

　　select name，phone fron table where name='张三' and phone='1111111111';

　　select name from table where name='张三' and phone='1111111111';

　　select phone from table where name='张三' and phone='1111111111';

　　select *，用不到覆盖索引。

　　5 索引的创建与使用

　　因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索引。

　　5.1 索引的创建

　　1、在用于where判断order排序和join的(on)字段上创建索引

　　2、索引的个数不要过多。

　　——浪费空间，更新变慢。

　　3、过长的字段，建立前缀索引。

　　4、区分度低的字段，例如性别，不要建索引。

　　——离散度太低，导致扫描行数过多。

　　5、频繁更新的值，不要作为主键或者索引。

　　——页分裂

　　6、随机无序的值，不建议作为主键索引，例如身份证、UUID

　　——无序，分裂

　　7、组合索引把散列性高(区分度高)的值放在前面

　　8、创建复合索引，而不是修改单列索引

　　5.2 什么时候用不到索引

　　1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式计算(+ - * /)

　　explain SELECT * FROM `t2` where id+1=4;

　　2、字符串不加引号，出现隐式转换

　　explain SELECT * FROM `user_innodb`wherename=136;

　　explain SELECT * FROM `user_innodb`wherename='136';

　　3、负向查询

　　NOT LIKE 不能

　　其实，用不用索引，最终都是优化器说了算。

　　优化器是基于什么的优化器?

　　基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。使用索引有基本原则，但是没有具体细则，没有什么情况一定用索引，什么情况一定不用索引的规则。

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

　　通常,通过索引查询数据比全表扫描要快，但是我们也必须注意到它的代价.

　　索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.

　　索引不但会使得插入和修改的效率降低，而且在查询的时候，有一个查询优化器，太多的索引会让优化器困惑，可能没有办法找到正确的查询路径，从而选择了慢的索引。

　　索引范围查询(INDEX RANGE SCAN)适用于两种情况:

　　1.基于一个范围的检索,一般查询返回结果集小于表中记录数的30%

　　2.基于非唯一性索引的检索

　　3.直接晋升为覆盖索引，避免多次查表

　　哪些情况下设置了索引但是无法使用

　　根本原因是查询优化器决定不使用索引：

　　一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

　　1、根据搜索条件，找出所有可能使用的索引

　　2、计算全表扫描的代价

　　3、计算使用不同索引执行查询的代价

　　4、对比各种执行方案的代价，找出成本最低的那一个

　　有时候查询语句没有按照索引的要求来也会导致无法使用索引，如下：

　　对单字段建了索引，where条件多字段。

　　建立组合索引，where条件单字段。与上面情况正好相反。INDEX(a,b,c)，当条件为a或a,b或a,b,c或a,c时都可以使用索引，但是当条

　　件为b,c时将不会使用索引。也就是说不是使用的第一部分，则不会使用索引。如果是INDEX(a,b)，即使查询的where是b,a，由于sql优化器的优化作用，会把b,a换成a,b，这样就可以走索引了。如果是index(a,b,c),查询是(a,b,c,d)不会走索引

　　条件中用or，即使其中有条件带索引，也不会使用索引查询(这就是查询尽量不要用or的原因，用in)(注意：使用or，又想索引生效，只能将or条件中的每个列都加上索引，这样查询时每个列都会单独使用它们自己的索引)

　　like的模糊查询的模糊词在字符串前面，比如以%或_开头，索引失效。

　　在使用不等于(is null、is not null、!= 、<>)的时候无法使用索引会导致全表扫描。

　　类型错误，如字段类型为varchar，where条件用number。

　　对索引应用内部函数，这种情况下应该建立基于函数的索引。

　　索引列不能是表达式(id+1=5)的一部分，也不能是函数的参数

　　如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

　　哪些情况下需要设置索引、哪些情况下不需要

　　需要：

　　1).主键自动建立唯一索引

　　2).频繁作为查询条件的字段应该创建索引

　　3).查询中与其它表关联的字段，外键关系建立索引

　　4).单键/组合索引的选择问题(在高并发下倾向创建组合索引)

　　5).查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

　　6).查询中统计或者分组字段

　　不需要：

　　1).表记录太少

　　2).经常增删改的表(因为不仅要保存数据，还要保存一下索引文件) 索引本来是一种事先在写的阶段形成一定的数据结构，从而使得在读的阶段效率较高的方式，但是如果一个字段是写多读少，则会降低写的速度。

　　3).数据重复且分布平均的表字段(比如性别)，因此应该只为最经常查询和最经常排序的数据列建立索引。

　　4).where条件里用不到的字段不创建索引

　　————————————————