Mysql数据库索引_index key index filter table filter-CSDN博客

本文链接：https://blog.csdn.net/xingxinggua9620/article/details/109390300

数据库索引

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

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

一. 索引类型

索引类型：

普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。
唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key 创建。
全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text。
空间索引(SPATIAL): 空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

二. 索引存储模型推演

二叉查找树（BST Binary Search Tree）

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

二叉查找树既能够实现快速查找，又能够实现快速插入。但是二叉查找树有一个问题：就是它的查找耗时是和这棵树的深度相关的，在最坏的情况下时间复杂度会退化成 O(n)。

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

https://www.cs.usfca.edu/~galles/visualization/BST.html

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

造成它倾斜的原因是什么呢？因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡.

平衡二叉树（AVL Tree）（左旋、右旋）

AVL Trees (Balanced binary search trees) 平衡二叉树的定义：左右子树深度差绝对值不能超过 1。

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

https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

插入 1、2、3。因为它是右节点下面接一个右节点，右-右型，所以这个时候我们要把 2 提上去，这个操作叫做左旋。

同样的，如果我们插入 7、6、5，这个时候会变成左左型，就会发生右旋操作，把 6 提上去

所以为了保持平衡，AVL树在插入和更新数据的时候执行了一系列的计算和调整的操作。
平衡的问题我们解决了，那么平衡二叉树作为索引怎么查询数据？在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容？

它应该存储三块的内容：

第一个是索引的键值。比如我们在id上面创建了一个索引，我在用where id=1 的条件查询的时候就会找到索引里面的id的这个键值。
第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。
第三个，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于26的时候，走右边，到下一个树的节点，继续判断。

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

如果是这样存储数据的话，我们来看一下会有什么问题。首先，索引的数据，是放在硬盘上的。查看数据和索引的大小：当我们用树的结构来存储索引的时候，因为拿到一块数据就要在Server层比较是不是需要的数据，如果不是的话就要再读一次磁盘。访问一个节点就要跟磁盘之间发生一次IO。 InnoDB操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是16K(16384字节)。

一个树的节点就是16K的大小。
如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到16K的容量，所以访问一个树节点，进行一次IO的时候，浪费了大量的空间。
所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多。每次从磁盘读取数据需要寻址时间，交互次数越多，消耗的时间就越多。

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

第一个就是让每个节点存储更多的数据。
第二个，节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有更多的分叉（我们把它叫做“路数”）。因为分叉数越多，树的深度就会减少（根节点是0）。

这样，我们的树是不是从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子？这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

多路平衡查找树（B Tree）（分裂、合并）

Balanced Tree这个就是我们的多路平衡查找树，叫做B Tree（B代表平衡）。跟AVL树一样，B树在枝节点和叶子节点存储键值、数据地址、节点引用。

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

B Tree的查找规则是什么样的呢？比如我们要在这张表里面查找15。因为15小于17，走左边。因为15大于12，走右边。在磁盘块7里面就找到了15，只用了3次IO。

那BTree又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟AVL树有什么区别？

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

比如MaxDegree（路数）是3的时候，我们插入数据1、2、3，在插入3的时候，本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有4个指针，子节点会变成4路，所以这个时候必须进行分裂（其实就是B+Tree）。把中间的数据2提上去，把1和3变成2的子节点。
如果删除节点，会有相反的合并的操作。注意这里是分裂和合并，跟AVL树的左旋和右旋是不一样的。

我们继续插入4和5，B Tree又会出现分裂和合并的操作。

节点的分裂和合并，其实就是InnoDB页（page）的分裂和合并。

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这种特点带来的优势：

它是BTree的变种，BTree能解决的问题，它都能解决。BTree解决的两大问题是什么？（每个节点存储更多关键字；路数更多）
扫库、扫表能力更强（对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵B+Tree拿到所有的数据）
B+Tree的磁盘读写能力相对于B Tree来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
效率更加稳定（B+Tree永远是在叶子节点拿到数据，所以IO次数是稳定的）

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

在Navicat的工具中，创建索引，索引方式有两种。

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

哈希索引有什么特点呢？

第一个，它的时间复杂度是O(1)，查询速度比较快。但是哈希索引里面的数据不是按顺序存储的，所以不能用于排序
第二个，我们在查询数据的时候要根据键值计算哈希码，所以它只能支持等值查询（= IN），不支持范围查询（> < >= <= between and）。
第三：如果字段重复值很多的时候，会出现大量的哈希冲突（采用拉链法解决），效率会降低。

需要注意的是，InnoDB不能显示地创建一个哈希索引（所谓的支持哈希索引指的是AHI）。memory存储引擎可以使用Hash索引。

因为B Tree和B+Tree的特性，它们广泛地用在文件系统和数据库中，例如Windows的HPFS文件系统，Oracel、MySQL、SQLServer数据库。

B+Tree 落地形式

MySQL 数据存储文件不同的存储引擎文件不一样。

show VARIABLES LIKE  'datadir';

每张 InnoDB 的表有两个文件（.frm 和.ibd），MyISAM 的表有三个文件（.frm、.MYD、.MYI）。

MyISAM

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

一个是.MYD文件，D代表Data，是MyISAM的数据文件，存放数据记录，比如我们的user_myisam表的所有的表数据。
一个是.MYI文件，I代表Index，是MyISAM的索引文件，存放索引，比如我们在id字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。

也就是说，在MyISAM里面，索引和数据是两个独立的文件。那我们怎么根据索引找到数据呢？

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

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

ALTER TABLE user_innodb DROP INDEX index_user_name; 
ALTER TABLE user_innodb ADD INDEX index_user_name(name);

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

InnoDB

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

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

聚集索引（聚簇索引）：就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）。
在InnoDB里面，它组织数据的方式叫做叫做（聚集）索引组织表（clusteredindexorganize table），所以主键索引是聚集索引，非主键都是非聚集索引。

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

InnoDB中，主键索引和辅助索引是有一个主次之分的。辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。比如我们用 name 索引查询 name=‘青山’，它会在叶子节点找到主键值，也就是id=1，然后再到主键索引的叶子节点拿到数据。

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

https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

1、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。
2、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。
3、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

三. 索引使用的原则

列的离散（sàn）度

第一个叫做列的离散度，我们先来看一下列的离散度的公式：

count(distinct(column_name)) : count(*) ---- 列的全部不同值和所有数据行的比例。

数据行数相同的情况下，分子越大，列的离散度就越高。

如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。当我们用在gender上建立的索引去检索数据的时候，由于重复值太多，需要扫描的行数就更多。例如，我们现在在gender列上面创建一个索引，然后看一下执行计划。这里需要扫描的行是X行。

ALTER TABLE user_innodb DROP INDEX idx_user_gender; 
ALTER TABLE user_innodb ADD INDEX idx_user_gender(gender); -- 耗时比较久 
EXPLAIN SELECT * FROM `user_innodb`  WHERE  gender=0;

而name的离散度更高，比如“张三”的这名字，只需要扫描一行。

ALTER TABLE user_innodb DROP INDEX idx_user_name; 
ALTER TABLE user_innodb ADD INDEX idx_user_name(name); 
EXPLAIN SELECT * FROM `user_innodb`  WHERE name='青山';

联合索引最左匹配

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

单列索引可以看成是特殊的联合索引。比如user表给name和phone建立了一个联合索引。

ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
ALTER TABLE user_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是第一个比较因子，所以用不到索引。

什么时候用到联合索引

如何创建联合索引

CREATE INDEX idx_name on user_innodb(name); 
CREATE INDEX idx_name_phone on user_innodb(name,phone);

当我们创建一个联合索引的时候，按照最左匹配原则，用左边的字段name去查询的时候，也能用到索引，所以第一个索引完全没必要。相当于建立了两个联合索引(name),(name,phone)。如果我们创建三个字段的索引index(a,b,c)，相当于创建三个索引：

index(a)
index(a,b)
index(a,b,c)

用 where b=? 和 where b=? and c=? 是不能使用到索引的。

覆盖索引

回表

回表：非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。例如：select * from user_innodb where name = ‘张三’;

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

select * ，此处用不到覆盖索引。

假如一个主键索引，其他3个字段建立辅助索引，select *也属于覆盖索引。Extra里面值为“Using index”代表使用了覆盖索引。

索引下推

索引下推是mysql 5.6版本后出现的,https://www.cnblogs.com/Terry-Wu/p/9273177.html

Index Condition Pushdown (ICP)是MySQL 5.6版本中的新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。

我对Using index condition的理解是，首先mysql server和storage engine是两个组件，server负责sql的parse，执行； storage engine去真正的做数据/index的读取/写入。以前是这样：server命令storage engine按index key把相应的数据从数据表读出，传给server，然后server来按where条件（index filter和table filter）做选择。

而在MySQL 5.6加入ICP后，Index Filter与Table Filter分离，Index Filter下降到InnoDB的索引层面进行过滤，如果不符合条件则无须读数据表，减少了回表与返回MySQL Server层的记录交互开销，节省了disk IO，提高了SQL的执行效率。

原理

a. 当关闭ICP时，index仅仅是data access的一种访问方式，存储引擎通过索引回表获取的数据会传递到MySQL Server层进行where条件过滤，也就是做index filter和table filter。

b. 当打开ICP时，如果部分where条件能使用索引中的字段，MySQL Server会把这部分下推到引擎层，可以利用index filter的where条件在存储引擎层进行数据过滤，而非将所有通过index access的结果传递到MySQL server层进行where过滤。

优化效果：ICP能减少引擎层访问基表的次数和MySQL Server访问存储引擎的次数，减少io次数，提高查询语句性能。

关闭ICP时，存储引擎通前缀index first_name（视为index key）访问表中数据，并在MySQL server层根据where条件last_name like ‘%nta’（视为index filter）进行过滤。

开启ICP时，MySQL server把index filter（last_name like ‘%nta’）推到存储引擎层，在存储引擎内部通过与where条件last_name like ‘%nta’的对比，直接过滤掉不符合条件的数据，然后返回最终数据给MySQL server层。该过程减少了回表操作，只访问符合条件的1条记录并返回给MySQL Server ，有效的减少了io访问和各层之间的交互。

ICP关闭时，仅仅使用索引作为访问数据的方式。

ICP索引下推的使用限制

当sql需要全表访问时，ICP的优化策略可用于range, ref, eq_ref, ref_or_null类型的访问数据方法。
支持InnoDB和MyISAM表。
ICP只能用于二级索引，不能用于主索引。
并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
MySQL 5.6版本的不支持分表的ICP功能，5.7版本的开始支持。
当sql使用覆盖索引时，不支持ICP优化方法。

四. 索引的创建与使用

索引的创建

在用于where判断order排序和join的（on）字段上创建索引
索引的个数不要过多。——浪费空间，更新变慢。
过长的字段，建立前缀索引。比如要在url上面创建索引可以截取前几位去长索引
区分度低的字段，例如性别，不要建索引。——离散度太低，导致扫描行数过多。
频繁更新的值，不要作为主键或者索引。——页分裂
随机无序的值，不建议作为主键索引，例如身份证、UUID。——无序，页分裂
组合索引把散列性高（区分度高）的值放在前面
创建复合索引，而不是修改单列索引

什么时候用不到索引？

https://www.runoob.com/mysql/mysql-functions.html

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

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

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

ALTER TABLE user_innodb DROP INDEX comixd_name_phone;

ALTER TABLE user_innodb add INDEX comixd_name_phone(name,phone);

explain SELECT * FROM `user_innodb` where name=136;

explain SELECT name FROM `user_innodb` where name ='136';

3、like条件中前面带%where条件中 like abc%，like %2673%，like %888都用不到索引吗？为什么？

explain select * from user_innodb where name like 'wang%'; -- 用到索引

explain select * from user_innodb where name like'%wang';  -- 索引失效

过滤的开销太大。这个时候可以用全文索引。

4、负向查询

NOT LIKE 不能：

explain select * from user_innodb where name not like 'wang'; -- 全表扫描

!= （<>）和NOT IN 在某些情况下可以：

explain select * from user_innodb where name not in('星星') ; -- 全表扫描

explain select * from user_innodb where name <>'星星';   -- 全表扫描

注意跟数据库版本、数据量、数据选择度都有关系。