数据结构与索引-- mySql索引诡异事件

矫情吗；* 2023-01-13 14:57 208阅读 0赞

#### 什么时候使用B+树索引 ####

*  并不是所有查询条件下出现的列都需要添加索引。对于什么时候添加索引，我们通过经验判断，访问表中很少一部分行时候，使用B+树索引才有意义。
 *  对于性别字段，地区字段，类型字段，他们取值范围很少，即选择性低。如下sql

select * from moment where status = 1;

*  对于性别，状态，可取值范围局限性非常大。对于上述SQL得到的结果可能是该表50% 的数据（假设2中状态），这时候，添加B+树索引完全没有必要。相反，如果某个字段取值范围不固定，几乎没有重复，即高选择性，此时使用B+树索引是最合适的，例如nickName 昵称字段，基本上一个应用中都不给你重复出现。
 *  如上，当访问选择性高的字段并从表中取出很少一部分行时候，对这个字段添加B+树索引是非常有必要的，但是如果出现了访问字段是高选择性的，但是取出的行数据占表中大部分数据时候，MySql数据库可能就不会使用B+树索引了，我们看如下一个案例。

show index from New_UserBaseInfo;

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

*  表New\_userBaseInfo大约有450W条数据。encodePhone字段上有一个索引，这时候，我们查找 ‘89F5F342F1ABE260F4F3D728174CF379’ 这个加密手机号时候，得到如下执行计划：

explain select * from New_UserBaseInfo  where encodePhone = '89F5F342F1ABE260F4F3D728174CF379'

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

*  可以看到 使用了idx\_encodePhone这个索引，也符合我们前面提到的套选择性，选取表中很少行的原色，但是入座执行下面的这条语句：

explain select * from New_UserBaseInfo  where encodePhone > '89F5F342F1ABE260F4F3D728174CF379'

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

*  可以看到possible\_keys依然是idx\_encodePhone，但是实际上优化器使用的索引key是null， 而且type是ALL，说他他匹配到了对应的索引，但是他并没有使用索引去查下，还是全表查询。way？？？
 *  因为这不符合我们之前说的规则，虽然encodePhone 这个字段的值是高选择性，但是我们取出的行数据中占了表中的一大部分数据。可以看到rows显示的是44W+ 数据，包括99%的数据了。因此查询优化器并没有使用索引
 *  也许会有疑问，查找加密手机号大于 89F5F342F1ABE260F4F3D728174CF379 的字段，这种情况几乎不太可能出现。的确如此，但是我们考虑New\_UserBaseInfo上 的lastRegisterTime 字段（注册时间），改字段日期类型，字段上有一个idx\_lastRegisterTime 非唯一索引，看如下两条查询语句：

explain select * from New_UserBaseInfo where lastRegisterTime < '2019-06-24';

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

explain select * from New_UserBaseInfo where lastRegisterTime < '2019-06-25';

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

*  查找用户注册时间小于某个时间的sql语句。前后两条SQL只相差1天时间，2条SQL语句的执行计划竟然不一样。在第二条SQL执行的时候，虽然同样使用的idx\_lastRegisterTime索引，但是优化器却没有使用这个索引，而是对全表扫描。
 *  MySQL数据库的查询优化器会通过explain的rows字段预估查询可能的到的行，如果大于某个值，则B+树会选择全表扫描。这个值大概是在20% 左右数据总量的时候会触发。即当我们取出的数据占比超过全数据量的20% 的时候，优化器不会使用索引，而是全表扫描。
 *  但是rows中预估的数据并不是绝对准确的，可以看大优化器判断日期小于2019-06-24 的数据是：757912，但是实际值：462377
 *  实际值少了大概38%，这可能对查询优化器的选择产生一定的影响，如果对比强制使用索引和使用优化器选择的全表扫描来查询注册日期小于2019-06-25的数据，最终发现如下：

select * from New_UserBaseInfo force index(idx_lastRegisterTime)where lastRegisterTime < '2019-06-25';
     select * from New_UserBaseInfo where lastRegisterTime < '2019-06-25';

*  查询时间分别是1.45s，5.8s，第一句SQL强制使用idx\_lastRegisterTime 索引，所用的时间是4.15s，根据优化器选择的全表扫方式，执行第二SQL确5.8s，***因此优化器的选择并不完全是正确的，有时候需要自己去判断。***

#### 顺序读，随机读与预读取 ####

*  之前介绍的规则中，索引使用原则，高选择，取出表中少部分数据。但是为什么只能是少部分数据？这就和InnoDB的顺序读和随机读取有关系
    
     *  顺序读：是指定顺序的读取磁盘上的快（Block）；随机读（Random Read）是指访问的快不是连续的，需要磁盘的磁头不断移动。当前传统机械磁盘的瓶颈之一就是随机读取的速度较低。
     *  在网上找的资料：同时对比RAID（磁盘阵列）开启write back 和write Through的性能差异。测试磁盘是由4块15000转的硬盘组成的RADI 10.测试文件大小2GB，块大小64KB。
     *  Write-through：CPU向cache写入数据时，同时向memory(后端存储)也写一份，使cache和memory的数据保持一致
     *  Write-back：cpu更新cache时，只是把更新的cache区标记一下，并不同步更新memory(后端存储)。只是在cache区要被新进入的数据取代时，才更新memory(后端存储)。

<table> 
 <thead> 
  <tr> 
   <th align="center"></th> 
   <th align="right">write back</th> 
   <th align="right">write Through</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">顺序读</td> 
   <td align="right">193.76</td> 
   <td align="right">65.333</td> 
  </tr> 
  <tr> 
   <td align="center">随机读</td> 
   <td align="right">82.117</td> 
   <td align="right">16.218</td> 
  </tr> 
 </tbody> 
</table>

*  可以看到，不管是否开启RAID卡的Write Back功能，磁盘的随机读性能都远远小于顺序读的性能。而上表中也说明了Write Back相对于Write Through 的性能提升。
 *  在数据库中，顺序读是指根据索引的叶节点数据就能顺序的读取所需要的行数据。这个顺序只是逻辑上的顺序读取，在物理磁盘上，行对应的数据可能还是随机分布在磁盘上的不同地址。但是相对来说，物理磁盘上的数据还是比较顺序的，因为B+树的构建是根据区来管理的，区是64个连续的页。如根据主键进行读取，或者通过辅助索引的叶节点就能读取到数据。
 *  随机读，一般指访问辅助索引叶节点不能完全得到的结果，需要根据辅助索引叶节点中的主键去找时机行数据。一般说来，辅助索引和主键所在的数据段是不同的，因此访问是随机的方式
 *  之前的sql lastRegisterTime < ‘2019-06-25’ 这条就是典型的随机读取。而正是因为读取的方式是随机的，并且随机读的性能会远低于顺序读取，因此优化器才会选择全表扫描的方式，而不是走 idx\_lastRegisterTime 这个索引。

##### 预读取 #####

*  InnoDB存储引擎为了提高读性能，引入了预读取技术。预读取是通过一次IO请求将多个页面预读取缓冲池中，并且估计预读取的多个页马上会被访问。传统的IO请求每次只读取一个页，在传统机械硬盘较低的IOPS下。预读取技术可以大大提高读取性能。
 *  InnoDB有两个预读取的方法，随机预读取（Random read ahead）和线性预读取（linear read ahead）
    
     *  随机预读取：指定一个区（64个连续的页）中的13个页面也在缓冲区中，并且在LRU列表的前端（即页是被频繁访问），则InnoDB存储引擎会将这个区中神域的所有页预读到缓冲区。
     *  线性预读取基于缓冲池中的页的访问模式，而不是数量。如果一个区中的24个页都被顺序访问了，则InnoDB存储引擎会读取下一个区的所有页。
     *  LRU页解析：Innodb为了加快对磁盘中数据的操作，在操作磁盘上的数据时，会先把数据存放到一块名为Buffer Pool的内存缓冲池中，但是内存的大小远小于磁盘的大小，因此需要一种机制来淘汰非热点数据，保证内存中存在的数据是较为频繁访问的数据。LRU是这种管理场景下最常用的算法，类似Redis中的LRU淘汰算法：
        
         *  新数据插入到链表头部；
         *  每当缓存命中（即缓存数据被访问），则将数据移到链表头部；
         *  当链表满的时候，将链表尾部的数据丢弃。
 *  InnoDB1.0.4 开始，缩进访问的预读取被取消了，而线性预读取还是保留了，并且加入了innodb\_read\_ahead\_threshold参数，改参数标识一个区中的多少个页面被顺序访问时候，InnoDB存储引擎才开启预读取，即预读下一个区中所有页。默认值是56，当一个区中56个页都被访问过，则预读下一个区的所有项。

show VARIABLES like 'innodb_read_ahead_threshold'

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

*  固态硬盘的情况，固态硬盘没有读写磁头，读取不需要旋转，因此随机读取性能得到质的提高。因为固态硬盘现在并没有全面普及，所InnoDB存储引擎中没有见到对固态硬盘相关的一些优化。

#### 辅助索引的优化 ####

*  辅助索引的叶子节点包含主键，但是辅助索引的叶子节点不包含完整的行信息，因此，InnoDB存储引擎总是会先从辅助索引的叶节点判断是否能得到所需要的数据。用如下案例解释：

drop table if EXISTS t
    
    create table t(a int not null, b varchar(20), PRIMARY key(a), key(b));
    
    insert into t select 1, 'k';
    
    insert into t select 2, 'do';
    
    insert into t select 3, 'dr';
    
    insert into t select 4, 'an';
    select * from t;

*  如上我们插入的数据，我们执行如下查询语句：
 *  顺序如下：  
    ![在这里插入图片描述][20210419155446310.png]
 *  加入我们插入的数据如下：

insert into t select 1, 'a';
    
    insert into t select 2, 'b';
    
    insert into t select 3, 'c';
    
    insert into t select 4, 'd';
    select * from t;

*  查询结果如下：  
    ![在这里插入图片描述][20210419155554200.png]
 *  我们可以看到，他的排序规则是按照b的顺序排列的，并不是根据主键a的顺序排列，这也就是我们上面提到的，因为辅助索引中包含了主键的值，因此访问b列上的辅助索引就能得到a的值，这样就可以得到表中所有数据的值。 ***通常情况，一个辅助索引页中，能存放的数据比主键索引页上存放的数据多，因此优化器选择了辅助索引*** ，如果我们解释这句查询语句得到如下结果：

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

*  可以看到，优化器最终选择b索引，如果想得到对列a的排序结果，还需要对他进行Order By 操作，这样优化器才会走主键，避免在查询b列后又发生对a的排序操作。如下图：  
    ![在这里插入图片描述][20210419160108455.png]
 *  或者可以强制使用主键索引  
    ![在这里插入图片描述][20210419160258655.png]

#### 联合索引 ####

*  联合索引是指对表上的多个列做索引。之前说的情况，都对表上的某个列进行索引。联合索引类似：

alter table t add key idx_a_b(a,b);

*  什么时候该使用联合索引？在这个问题之前我们应该弄清楚联合索引内部的结构，本质上，联合索引还是 B+树，不同的是联合索引的键值数量不是1个，二手大于等于2个。我们用简单的两个key的情况说明问题，如上a，b两个key，我们用如下图表示：

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

*  如上图中看到多个key情况的B+树，和我们之前讨论的单个键值没有什么区别，键值都是排序的，通过叶子节点可以逻辑上顺序的读出所有数据，就上面的例子来说：（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）数据按照（a，b）的顺序存放。
 *  例如对于查询 select \* from table where a= xxx and b = xxx，这种情况显然可以用（a，b）联合索引。对应单个a的查询 select \* from table where a= xxx也可以使用（a，b）联合索引。但是对于select \* from table where b= xxx单个b的查询不可以用这个B+树索引。可以看到叶子节点上b的值 1,2，1,4,1，2，显然不是按排序的，因此对于b列的查询使用不到（a，b）的联合索引。
 *  联合索引的第二个好处：可以对第二个键值进行排序，例如，很多情况，我们都只查询某个用户订单信息，并按照时间排序，取出最近一段时间的购买记录，这个时候使用联合索引可以避免多一次的排序操作。因为索引本身的叶子节点已经排序了。如下测试案例：

create table buy_log(userid int unsigned not null, buy_date date);
    
    insert into buy_log values
    (1,'2021-01-19'),
    (2,'2021-01-19'),
    (3,'2021-01-19'),
    (1,'2021-02-19'),
    (3,'2021-02-19'),
    (1,'2021-03-19'),
    (1,'2021-04-19');
    
    ALTER table buy_log add key(userid);
    alter table buy_log add key(userid, buy_date);

*  如上建立两个索引，都包含userid字段，对userid进行查询，看优化器的选择，如下：  
    ![在这里插入图片描述][2021041916412019.png]
 *  如上，possible\_keys中有两个索引，分别是单个userid和userid，buy\_date的联合索引。优化选择的是userid，因为改叶节点包含单个键值，因此一个页能存放的记录更多，接着，看一下的查询，我们假定要取出userid= 1的最近三次购买记录，并分析使用单个索引和符合索引区别：  
    ![在这里插入图片描述][20210419164409474.png]
 *  同样的都可以用两个索引，但是这次优化器选择了符合索引，因为这个联合索引中buy\_date已经排序好了，如果我们强制使用userid的单个索引，会有如下结果：  
    ![在这里插入图片描述][20210419164750748.png]
 *  如上extra信息中，看到Using filesort，filesort指排序，但是不是文件中完成，我们可以对比执行：

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

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

*  如上看到增加了排序操作，但是如果使用userid， buy\_date的联合索引userid\_2，就不会有这一次额外的操作，如下：

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

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

[上一篇：数据结构与索引-- B+树索引][-- B]  
[下一篇：mysql技术分享-- 视图是什么][mysql_--]

[20210419115905758.png]: /images/20221022/31cc219f9d52497d9e7ce7f0d28ee443.png
[20210419120246870.png]: /images/20221022/9f6d30f130ca488180404898b6bba0bf.png
[20210419120429195.png]: /images/20221022/7f3c32b37105479483ec4692fa68ddd4.png
[20210419121432461.png]: /images/20221022/6bed40168a154d268993be591758d2f7.png
[20210419121559482.png]: /images/20221022/60fe06fd5dbc4bf7be04eeb1e2e3379b.png
[20210419152857376.png]: /images/20221022/855e84773fbf48cab5a35a670b3fcf76.png
[20210419155446310.png]: /images/20221022/5b7c5a7f34194c84b3297242744f437a.png
[20210419155554200.png]: /images/20221022/d2d00ef7aa1e459f98a0135098c415fc.png
[20210419155923360.png]: /images/20221022/b1978dc4f701415e8c9fc2dc2b947465.png
[20210419160108455.png]: /images/20221022/ccf04f99395a46e48c08ae3ce8f617fb.png
[20210419160258655.png]: /images/20221022/15a12caefa4848a6928a7c41ce2844e7.png
[20210419162422803.png]: /images/20221022/0a3355e843df4423b5fced8438a7f12a.png
[2021041916412019.png]: /images/20221022/6686f97573bb46b3ac2ab88847c8fcf1.png
[20210419164409474.png]: /images/20221022/17276a4dc3d24d57a430b760c12882fd.png
[20210419164750748.png]: /images/20221022/14bfd3968e28492da244cabe818f9915.png
[20210419165024405.png]: /images/20221022/4555b224b0aa4113a0db3c49e333ce68.png
[20210419165028192.png]: /images/20221022/d379744a889648da82ebb649820a7473.png
[20210419165040373.png]: /images/20221022/f1abd3ea8edc4829aeedc6125da6a7f6.png
[20210419165224562.png]: /images/20221022/a85ad092b80a4c4f8773ca0c416666cd.png
[20210419165250902.png]: /images/20221022/0deaf96116ff4d0495aedc7f3e7cfbce.png
[-- B]: https://blog.csdn.net/liaojiamin0102/article/details/115724427
[mysql_--]: https://blog.csdn.net/liaojiamin0102/article/details/118089940