MySql之索引

分手后的思念是犯贱 2023-10-14 12:45 5阅读 0赞

MySql之索引

1.索引概述  
**MySql官方对索引的定义为：索引是帮助MySql高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。**  
![在这里插入图片描述][6098f73d952d40abbda4cdb3d2498451.png_pic_center]

个人对于MySql索引的理解：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，包括B+树或者Hash表。由于存储引擎表示的是数据在磁盘上面的不同的组织形式，所以索引底层采用哪种数据结构是跟数据库的存储引擎相关的。如果是MyIsam或者是InnoDB存储引擎，那么对应的底层的数据结构为B+树，如果是Memory存储引擎，那么对应的底层的数据结构为Hash表。采用B+树的最根本的原因是由于二叉树的树太高，树太高则直接影响到磁盘IO的次数，影响数据查询的效率，采用B+树的数据结构，可以在某个数据节点里面尽可能多的存储数据，使树的高度尽量的变低，提高效率。日常开发过程中，遇到的比较多的可能就是聚簇索引和联合索引，里面又涉及到了覆盖索引，最左匹配，回表，索引下推等各方面的知识点，在编写SQL语句的时候，我们就可以利用这些点来进行优化，提高数据的查询效率。

> 索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySql的性能优化问题。

如下面的示意图所示：  
![在这里插入图片描述][76c1c07c958c49788e6c520336be684d.png]  
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和—个指向对应数据记录物理地址的指针,这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

索引的优势劣势

优势

类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。  
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。  
劣势

**实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的。**

虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行 INSERT、 UPDATE、 DELETE。**因为更新表时,MSQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。**

2.索引底层数据结构  
普通的查找有所不同，因为我们的数据有以下特征：

1.存储的数据是非常非常多的
    2.并且还不断的动态变化

实现索引时需要考虑到这两个特点。我们需要找一个最合适的数据结构算法来实现查找功能。索引是在MySql的储存引擎层中实现的，而不是在服务器层实现，所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。

既然索引底层原理是利用一些巧妙的数据结构维护我们的数据，使得查询效率很高，那索引底层使用的什么数据结构呢？又是怎样来维护我们的数据呢？下面就带着大家一起探索一下索引的底层数据结构。

从存储结构上来划分：B-Tree，B+Tree，Hash索引

从应用层次来分：普通索引，唯一索引，复合索引

从数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引

Hash索引  
哈希索引是一种基于哈希表的索引结构，它是一种需要精确匹配才生效的索引结构。

实现原理：对索引列计算哈希值把记录映射到哈希槽中，然后指向对应记录行的地址。因此，在查询的时候只要正确匹配到索引列，就能在O(1)的时间复杂度内查到记录。

相比于B-Tree索引而言，哈希索引有不少的局限性：

哈希索引不支持排序  
哈希索引不支持部分列索引查找  
哈希索引只支持等值查询，无法提供范围查询功能  
哈希索引的查找效率是非常高的，大多数时候都能在O(1)的时间内找到记录，除非哈希冲突很高。

二叉树

二叉树的特点：

一个节点只能有两个子节点，也就是一个节点度不能超过2  
左子节点 小于 本节点，右子节点大于等于 本节点  
![在这里插入图片描述][63eab2116c584421a23e546873b3398a.png]  
在二叉树结构，计算比较 3 次就可以检索到 id=7 的数据，相对于直接遍历查询省了一半的时间，从检索效率上能做到高速检索的。此外二叉树的结构还能提供的范围查找功能，上图二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也是比较容易实现。

缺点:  
主键自增情况下会退化为线性链表，二分查找也会退化为遍历查找（全盘扫描），检索性能急剧下降。id主键索引自增情况下二叉树会退化为线性链表，检索速度降低。此时检索 id=7 的数据的所需要计算的次数已经变为 7 了,因此 不能直接用于实现 Mysql 底层索引

![在这里插入图片描述][a2ce330c0ebb42749a1f563c3f8e84eb.png]  
二叉查找树存在不平衡问题，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。  
![在这里插入图片描述][628762d0be8d477696d7e55741dcf84d.png]  
平衡二叉树查找过程:  
![在这里插入图片描述][727bc8e9ecd04787a03b0134f6215e70.png]  
AVL 树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。

mysql 如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，如查询 id=7 这个数据我们就要进行磁盘 IO 三次，这很消耗时间。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。因此 不能直接用于实现 Mysql 底层索引.。

为了保证平衡，在插入数据的时候必须要旋转，通过插入性能的损失来弥补查询性能的提升。

红黑树  
红黑树的特点：

每个节点的最长子树的只要不超过最短子树的两倍即可

递增插入过程红黑树会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态，也就保证了查找效率不会明显减低。如上图所示。红黑树下查找 id=7 的所要比较的节点数为 4，依然保持二叉树不错的查找效率。

红黑树很好的解决线性链表问题，但红黑树问题也比较大。  
每次插入都要检查规则，再把树进行重新平衡，这个是非常消耗时间,数据量大的话，红黑树的深度会比较深，并且产生“右倾”,树一旦深就代表着我们读取磁盘次数就会增加，因此 不能直接用于实现 Mysql 底层索引

B-Tree  
磁盘 IO 特点：从磁盘读取1B 数据和 1KB 数据所消耗的时间是基本一样的(空间局部性与时间局部性决定)，根据这个思路，可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就尽可能多的加载数据到内存，影响数据查询时间的是树的高度，高度越高，比较的次数越多，尽量把树的高度降低，这就是B树的设计原理了  
B-Tree特点:

叶节点具有相同的深度。  
节点中的元素从左向右递增排序  
所有的元素不重复  
B-Tree结构图:  
![在这里插入图片描述][b2a1009ad5db46bb956122354971fb3c.png]  
B树简单地说就是多叉树，每个叶子会存储数据，和指向下一个节点的指针。

例如要查找9，步骤如下

我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；  
按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；  
按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。  
总结来说，B 树用作数据库索引有以下优点：

优秀检索速度  
尽可能少的磁盘 IO，加快了检索速度；  
可以支持范围查找。

B+Tree  
有了B树知识铺垫，一个树节点我们应该尽可能的包含更多的子节点，但又不能超过一个磁盘页（16kb）的大小。发现B树的节点中还包含了一些关键字信息data，这个data也占据着一定的数据量，如果把data去掉，这样就又能多加很多子节点了。这也就是B+树的核心思想。

对于聚簇索引来说，data存的是数据行，对于非聚簇索引来说，data存的是主键的值

B+Tree特点：

非叶子节点不存储data,只存储索引(冗余),可以放更多的索引  
叶子节点包含所有索引字段  
叶子节点用双向指针相连，提高区间访问性  
B+树是通过二叉树，平衡二叉树，B树和索引顺序访问演化而来,是对B树的进一步优化。

B+Tree结构图:  
![在这里插入图片描述][eb99404136cb40aaa98e624e7c83d1af.png]  
B+树是B树的改进，简单地说是：只有叶子节点才存数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表

例如要查找关键字16，步骤如下

与根节点的关键字 (1，18，35) 进行比较，16 在 1 和 18 之间，得到指针 P1（指向磁盘块 2）  
找到磁盘块 2，关键字为（1，8，14），因为 16 大于 14，所以得到指针 P3（指向磁盘块 7）  
找到磁盘块 7，关键字为（14，16，17），然后我们找到了关键字 16，所以可以找到关键字 16 所对应的数据。  
B+树和B树有什么不同:

B+树非叶子节点不存储数据的，仅存储键值(索引地址)，而B树节点中不仅存储键值，也会存储数据。B+树之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快 。

B+树索引的所有数据均存储在叶子节点，且数据是按照顺序排列的。B+树使得范围查找，排序查找，分组查找以及去重查找变得简单高效

B+树各个页之间是通过双向链表连接，叶子节点中的数据是通过单向链表连接的。我们通过双向链表和单向链表连接的方式可以找到表中所有的数据。

MySql中 B+Tree  
在 mysql分别创建 以myisam 和 Innodb 作为存储引擎的数据表。  
Innodb 创建表后生成的文件有：

frm:创建表的语句  
idb:表里面的数据+索引文件  
Myisam 创建表后生成的文件有：

frm:创建表的语句  
MYD:表里面的数据文件（myisam data）  
MYI:表里面的索引文件（myisam index）  
**Myisam不支持事务原因索引与数据分开存储，两个文件无法做到一致性**

MyISAM 引擎的底层实现（非聚集索引方式）  
MyISAM 是非聚集索引方式，即数据和索引落在不同的两个文件上。B+树索引的叶子节点并不存储数据，而是存储数据的文件地址，MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。下图所示：

![在这里插入图片描述][375a41857b5744399992868107a1ecc2.png]  
Myisam检索数据过程中有 “回表操作”

Innodb 引擎的底层实现（聚集索引方式）  
InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select \* from user\_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user\_name=‘Bob’。  
这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user\_name 这个字段加索引，那么 InnoDB 就会建立 user\_name 索引 B+树，节点里存的是 user\_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY！拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user\_name 索引树找到的主键 KEY 查找到对应的数据（回表）。

Inodb存储引擎特点:

表本身是按B+Tree组织的一个索引结构文件  
叶子节点包含了完整的数据记录  
非主键索引结构叶子节点存储的是主键的值，使其保持一致性和节省空间  
![在这里插入图片描述][8c03e56568cf48b68ba029fff1b63d5e.png]  
Inodb查询等于的查询 引擎会自动使用哈希索引进行查询,存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应哈希索引。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据 ?  
为节省存储空间，一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。

**为什么MySQL不推荐使用uuid作为主键?**  
1）使用自增id的内部结构

自增的主键的值是顺序的，所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16，会留出1/16的空间留作以后的修改)：
    
    ①下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费
    
    ②新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
    
    ③减少了页分裂和碎片的产生
    
     2）使用uuid的索引内部结构

![在这里插入图片描述][c3c1c7104bd3470aa24e7535c8e07f28.png]

对顺序的自增id来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以innodb无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。

这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

①写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO

②因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上

③由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片

在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后，有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。

**结论：使用innodb应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行**

3）使用自增id的缺点

那么使用自增的id就完全没有坏处了吗？并不是，自增id也会存在以下几点问题：

①别人一旦爬取你的数据库，就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况

②对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争

③Auto\_Increment锁机制会造成自增锁的抢夺，有一定的性能损失

附：Auto\_increment的锁争抢问题，如果要改善需要调优innodb\_autoinc\_lock\_mode的配置

**数据表的字段加索引原则：**

较频繁的作为查询条件的字段应该创建索引；

唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；

更新非常频繁的字段不适合创建索引。

联合索引底层数据结构

![在这里插入图片描述][379d7022fa8b45079faf207482e1b81b.png]

联合索引的数据结构 也是字典排序法则，将第一个 第二个进行排序，B+之所以高效是借助 叶子节点从左到右的排序，如果跳过 第一个字段，则第二三字段 在叶子节点中的排序 不是按顺序排序，则整个数据不一定是顺序递增的结构，就是说联合索引使用时遵循"最左前缀原则"

非主键索引存储的是主键索引位置，会扫描两棵树 （主键索引, 非主键索引）

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。因为InnoDB支持聚簇索引（主键索引），聚簇索引就是表，所以InnoDB不用像MyISAM那样需要独立的行存储。也就是说，InnoDB的数据文件本身就是索引文件。

3.常见索引类型  
主键索引

create table User(
    `name` varchar(50) not null,
    `uid` int(4) not null,
    `gender` int(2) not null,
     primary key(`uid`)
    );

主键索引是唯一的，通常以表的ID设置为主键索引,一个表只能有一个主键索引，这是他跟唯一索引的区别。  
聚簇索引与非聚簇索引  
MyISAM 中的索引详解

MyISAM 存储引擎的索引文件和数据文件是分开的，MyISAM 引擎按照数据插入顺序，将数据文件存储在磁盘上，例如下图中 99 条记录从上到下依次存储。MyISAM 引擎使用 B+ 树作为索引结构，叶节点存放的是数据记录的行指针，图中为了方便阅读以行号代替。  
![在这里插入图片描述][80ead0375079416496e434e36ffc3b61.png]  
在 MyISAM 引擎中，对主键列建立的主索引和对其他列建立的辅助索引在结构上没有区别，主键索引就是一个名为 Primary 的唯一非空索引。  
总结一下，MyISAM 引擎中索引查询的步骤为，先按照 B+ 树查询到叶子节点，如果指定的键值存在，则取出其对应的行指针的值，然后通过行指针，读取相应数据行的记录。

InnoDB 中的索引详解

聚簇索引

同 MyISAM 引擎不同，InnoDB 的数据文件本身就是索引文件，表数据文件本身就是按 B+ 树组织的一个索引结构，其叶子节点的键值就是表的主键，这种数据存储方式也被称为聚簇索引。由此可见，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

聚簇索引的叶子节点都包含主键值、事务 ID、用于事务 MVCC 的回滚指针以及所有的剩余列。  
![在这里插入图片描述][02f4e780f4df4cf796d2d405ed3aaf84.png]  
辅助索引  
辅助索引也叫非聚簇索引，二级索引等。同 MyISAM 引擎的辅助索引实现不同，InnoDB 的辅助索引，其叶子节点存储的不是行指针而是主键值，得到主键值再要查询具体行数据的话，要去聚簇索引中再查找一次，也叫回表。这样的策略优势是减少了当出现行移动或者数据页分裂时二级索引的维护工作。

![在这里插入图片描述][6924ba1a6acc48cb889f0b8849d5972d.png]  
结论：

> 聚簇索引: 通常由主键或者非空唯一索引实现的，叶子节点存储了一整行数据  
> 非聚簇索引： 又称二级索引，就是我们常用的普通索引，叶子节点存了索引值和主键值，在根据主键从聚簇索引查

**唯一索引**

create table User(
    `name` varchar(50) not null,
    `uid` int(4) not null,
    `gender` int(2) not null,
     unique key(`name`)
    );

唯一索引主要用于业务上的唯一约束，他跟主键索引的区别是，一个表可以有多个唯一索引

**单列索引**

create table User(
    `name` varchar(50) not null,
    `uid` int(4) not null,
    `gender` int(2) not null,
     key(`name`)
    );

以某一个字段为索引

**联合索引**

create table User(
    `name` varchar(50) not null,
    `uid` int(4) not null,
    `gender` int(2) not null,
     key(`name`,`uid`)
    );

两个或两个以上字段联合组成一个索引。使用时需要注意满足最左匹配原则！

最左前缀原则  
最左前缀原则指的是，查询从联合索引的最左列开始，并且不跳过索引中的列。如下：

select * from user where name=xx and city=xx ;

可以命中索引这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

尽量使用联合索引，而少使用单列索引

创建联合索引

create index idx_name_sta_address on table(name,status,address);

相当于创建了三个索引：

> name;  
> name + status;  
> name + status + address;

创建单列索引

create index idx_name on table(name);
    create index idx_status on table(status);
    create index idx_address on table(address);

数据库只会选择一个最优的索引来使用，并不会使用全部索引。

覆盖索引  
覆盖索引就是指索引包含了所有需要查询的字段。

create table User(
    `name` varchar(50) not null,
    `uid` int(4) not null,
    `gender` int(2) not null,
     key(`uid`,`name`)
    );

假如表 User有三个字段 User (name,uid,gender),且有个联合索引 key(name,uid)那么 执行如下面这条sql查询时就用到了 覆盖索引。

select name,uid from User where name in ('a','b') and uid >= 98 and uid <=100 ;

上面这条sql语句使用了联合索引 key(name,uid),并且只需查找 name,uid两个字段，所以使用了覆盖索引。覆盖索引有什么好处呢？

如果我们只需查询(name,uid)两个字段的话，从索引树就能得到我们需要查的数据，不需要回表。

覆盖索引好处  
1.避免了对主键索引(聚簇)的二次查询  
2.由于不需要回表查询(从表数据文件)所以大大提升了Mysql缓存的负载

总之大大提升了读取数据的性能

索引下推  
索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加，用于优化数据的查询。  
当你不使用ICP,通过使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器再判断是否符合条件。  
使用ICP，当存在索引的列做为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。  
适用场景

当需要整表扫描，e.g.:range,ref,eq\_ref…  
适用InnoDB引擎和MyISAM引擎查询（5.6版本不适用分区查询，5.7版本可以用于分区表查询）。  
InnoDB引擎仅仅适用二级索引。（原因InnoDB聚簇索引将整行数据读到InnoDB缓冲区）。  
子查询条件不能下推。触发条件不能下推，调用存储过程条件不能下推。  
小示例

当我们创建一个用户表(userinfo),其中有字段：id,name,age,addr。我们将name,age建立联合索引。

当我们执行：

select * from userinfo where name like "ming%" and age=20;

对于MySQL5.6之前：我们在索引内部首先通过name进行查找，在联合索引name,age树形查询结果可能存在多个，然后再拿着id值去回表查询，整个过程需要回表多次。

对于MySQL5.6之前：我们在索引内部首先通过name进行查找，在联合索引name,age树形查询结果可能存在多个，然后再拿着id值去回表查询，整个过程需要回表多次。![在这里插入图片描述][1fe2b2a01f4b4aeaa337dcdded0be9ae.png]  
对于MySQL5.6之后：我们是在索引内部就判断age是否等于20，对于不等于20跳过。因此在联合索引name,age索引树只匹配一个记录，此时拿着这个id去主键索引树种回表查询全部数据，整个过程就回一次表。  
![在这里插入图片描述][ce2afc22fa6445bb8e7cfb7d840c5c2f.png]  
![在这里插入图片描述][d3dc0177c47942aaa7b469c38ddf100d.png]  
当Extra值为：Using index condition.表示使用索引下推。  
通过索引下推对于非主键索引进行优化，可有效减少回表次数，从而提高效率。

关闭索引下推命令

set optimizer_switch='index_condition_pushdown=off';

4.最佳索引使用策略  
索引失效的情况  
范围查询右边的列，不能使用索引

select * from t where name ='test' and status >'1' and address='北京市'

前面的两个字段name，status查询是走索引的，都是最后一个条件address没有用到索引

不要在索引列上进行运算操作，索引将失效

select * from t where substring(name,3,2)='科技'

字符串不加单引号，造成索引失效

select * from t where name ='test' and status =1

用or分隔的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

select * from t where name ='test' or createtime='2020-04-05 12:00:00'

name是索引列，createtime不是索引列，之间or进行连接，那么会导致name列也不走索引

以%开头的like模糊查询，索引失效  
如果仅仅是尾部的模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效，但是如果使用覆盖索引，那么索引仍然会生效

select name from t where name like '%test'

如果MySql评估使用索引比全表扫描更慢，则不使用索引  
is null，is not null 有时索引失效  
is null，如果数据库中，该字段为空值的记录数更多，那么MySql评估使用索引比全表扫描更慢，则不使用索引

is not null 如果数据库中，该字段不为空值的记录数更多，那么MySql评估使用索引比全表扫描更慢，则不使用索引

in 走索引，not in 索引失效  
使用不等于(!=或者<>)的时候，索引失效，会导致全表扫描

select name from t where name != 'test'

MYSQL针对函数或存储过程中传递进的参数,如果是varchar类型时则默认会进行转换字符集校对规则与数据库保持一致，这个时候如果数据库编码和表编码不一致时（比如utf8和utf8mb4），就会出现索引失效的情况  
客户端直接发sql查询的话，不会存在这种问题，因为这个时候默认的是表字段的编码：

借助网上的一个完整的用户请求的字符集转换流程来更好的理解上述几个变量：

mysql Server收到请求时将请求数据从 character\_set\_client 转换为 character\_set\_connection  
进行内部操作前将请求数据从 character\_set\_connection 转换为内部操作字符集,步骤如下  
A. 使用每个数据字段的 CHARACTER SET 设定值；  
B. 若上述值不存在，则使用对应数据表的字符集设定值  
C. 若上述值不存在，则使用对应数据库的字符集设定值；  
D. 若上述值不存在，则使用 character\_set\_server 设定值。  
最后将操作结果从内部操作字符集转换为 character\_set\_results  
![在这里插入图片描述][b80f3fb275764d289eceb06c918a6dcf.png]  
**最佳索引使用策略**

对查询频次较高,且数据量比较大的表建立索引  
索引字段的选择,最佳候选列应当从 where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合  
索引可以有效的提升查询数据的效率,但索引数昰不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DM操作的效率,增加相应操作的时间消耗。另外索引过多的话, MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价  
独立的列  
独立的列不是指单列索引，而是指索引列不能是表达式的一部分或者是函数的一部分。

select * FROM test where col1 + 1 =100; // 不能是表达式一部分

select * FROM test where ABS(col1) =100; // 不能是函数一部分

最左匹配原则  
假如有个联合索引 key (col1,col2)。那么以下查询是索引无效的

select * from test where col2 = 3;
    
    select * from test where col1 like '%3';

对于最左匹配原则，大家想一下B+树的叶子节点的关联就差不多知道为啥需要最左匹配原则了，因为B+的叶子结点，从左到右以链表的形式关联的，索引我们查询的时候要么范围查询，要么有明确的左边一个开始的索引值，不能跳过或者不明确如 like '%XYZ’这种查询。

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 col2= xx and col1 ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的

使用聚簇索引和覆盖索引大大提升读取性能  
因为聚簇索引和覆盖索引的索引树上就有了需要的字段，所以不需要回表文件查询，所以提升了查询速度

使用短索引  
如果很长的字符串进行查询，只需匹配一个前缀长度，这样能够节省大量索引空间

尽量使用覆盖索引，避免使用select \*

[6098f73d952d40abbda4cdb3d2498451.png_pic_center]: https://img-blog.csdnimg.cn/6098f73d952d40abbda4cdb3d2498451.png#pic_center
[76c1c07c958c49788e6c520336be684d.png]: https://img-blog.csdnimg.cn/76c1c07c958c49788e6c520336be684d.png
[63eab2116c584421a23e546873b3398a.png]: https://img-blog.csdnimg.cn/63eab2116c584421a23e546873b3398a.png
[a2ce330c0ebb42749a1f563c3f8e84eb.png]: https://img-blog.csdnimg.cn/a2ce330c0ebb42749a1f563c3f8e84eb.png
[628762d0be8d477696d7e55741dcf84d.png]: https://img-blog.csdnimg.cn/628762d0be8d477696d7e55741dcf84d.png
[727bc8e9ecd04787a03b0134f6215e70.png]: https://img-blog.csdnimg.cn/727bc8e9ecd04787a03b0134f6215e70.png
[b2a1009ad5db46bb956122354971fb3c.png]: https://img-blog.csdnimg.cn/b2a1009ad5db46bb956122354971fb3c.png
[eb99404136cb40aaa98e624e7c83d1af.png]: https://img-blog.csdnimg.cn/eb99404136cb40aaa98e624e7c83d1af.png
[375a41857b5744399992868107a1ecc2.png]: https://img-blog.csdnimg.cn/375a41857b5744399992868107a1ecc2.png
[8c03e56568cf48b68ba029fff1b63d5e.png]: https://img-blog.csdnimg.cn/8c03e56568cf48b68ba029fff1b63d5e.png
[c3c1c7104bd3470aa24e7535c8e07f28.png]: https://img-blog.csdnimg.cn/c3c1c7104bd3470aa24e7535c8e07f28.png
[379d7022fa8b45079faf207482e1b81b.png]: https://img-blog.csdnimg.cn/379d7022fa8b45079faf207482e1b81b.png
[80ead0375079416496e434e36ffc3b61.png]: https://img-blog.csdnimg.cn/80ead0375079416496e434e36ffc3b61.png
[02f4e780f4df4cf796d2d405ed3aaf84.png]: https://img-blog.csdnimg.cn/02f4e780f4df4cf796d2d405ed3aaf84.png
[6924ba1a6acc48cb889f0b8849d5972d.png]: https://img-blog.csdnimg.cn/6924ba1a6acc48cb889f0b8849d5972d.png
[1fe2b2a01f4b4aeaa337dcdded0be9ae.png]: https://img-blog.csdnimg.cn/1fe2b2a01f4b4aeaa337dcdded0be9ae.png
[ce2afc22fa6445bb8e7cfb7d840c5c2f.png]: https://img-blog.csdnimg.cn/ce2afc22fa6445bb8e7cfb7d840c5c2f.png
[d3dc0177c47942aaa7b469c38ddf100d.png]: https://img-blog.csdnimg.cn/d3dc0177c47942aaa7b469c38ddf100d.png
[b80f3fb275764d289eceb06c918a6dcf.png]: https://img-blog.csdnimg.cn/b80f3fb275764d289eceb06c918a6dcf.png