MySQL---1.索引

本文主要是介绍MySQL---1.索引，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

• 一、答疑
• 二、普通索引和唯一索引
  • 1.查询过程
  • 2.更新过程
• 三、优化器的索引选择
  • 1.影响优化器选择索引的主要因素
  • 2.选择最合适的索引
• 四、字符串索引的设置
  • 1.整个字符串建立索引和前缀索引
  • 2.选择多长的前缀建立索引
  • 3.前缀索引对覆盖索引的影响
  • 4.前缀区分度不大的处理方式
• 五、order by工作流程
  • 1.全字段排序
  • 2.rowid排序（即主键）
  • 3.如何避免排序操作
• 六、索引失效
  • 1.对索引做函数操作
  • 2.隐式类型转换
  • 3.隐式字符编码转换

 

一、答疑

Q：如果插入的数据是在主键树叶子结点的中间，后面的所有页如果都是满的状态，是不是会造成后面的每一页都会去进行页分裂操作，直到最后一个页申请新页移过去最后一个值

A：不会不会，只会分裂它要写入的那个页面。每个页面之间是用指针串的，改指针就好了，不需要后面的全部挪动

 

Q：插入数据如果是在某个数据满了页的首尾，为了减少数据移动和页分裂，会先去前后两个页看看是否满了，如果没满会先将数据放到前后两个页上，不知道是不是有这种情况

A：对，为了增加空间利用率     

 

Q：为什么需要索引重建？

A：索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

 

二、普通索引和唯一索引

1.查询过程

例子：select id from T where k=5

这个查询语句在K索引树的查找过程，先从B+树的树根开始，按层搜索到叶子节点，即数据页，再在数据页记录内部通过二分法来定位记录

普通索引：查找到满足条件的第一个记录（5，500）后，需要继续查找下一个记录，直到碰到第一个不满足k=5的记录

唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止检索

由于innodb的数据是按照数据页为单位来读写的，当找到k=5的记录的时候，他所在的数据页基本上都在内存里了，所以对于普通索引来说，要多做的那一次查找和判断下一条记录，就只需要一次指针寻址和一次计算。

所以此时，普通索引和唯一索引差别不大

 

2.更新过程

例子：插入一个新纪录（4，400）

①这个操作要更新的数据页已经在内存中

对于唯一索引来说，找到数据页中是否有4的记录，如果没有直接插入，如果有则冲突，执行结束

对于普通索引来说，找到3和5之间的位置，插入这个值，执行结束

====>>>效率差不多

②这个操作要更新的数据页没有在内存中

对于唯一索引来说，从磁盘中找到该记录所在的数据页，加载到内存中，若没有冲突，则直接插入这个值，执行结束

对于普通索引来说，直接写入change buffer中，执行结束

====>>>将数据从磁盘读到内存，涉及到随机IO访问，是数据库中成本最高的操作之一，change buffer因为减少了磁盘随机访问，所以能够提高性能

 

注：

1.change buffer

change buffer基本使用:

①当需要更新一个数据页时，若当前数据页在内存中，则直接更新；

若这个数据页还没有在内存中，在不影响数据一致性的情况下，innodb会把这些更新操作缓存在change buffer，这样就不需要从磁盘读取数据页到内存中。

在下次查询需要访问这个数据页的时候，将数据页读入内存，将会执行change buffer中与这个数据页相关的操作。

②将change buffer中的操作应用到内存的数据页中并写入到redo log，得到最新结果的过程称为merge。

除了查询这个数据页会触发merge以外，系统后台还会有定时任务来触发merge，在数据库正常关闭的时候也会触发merge

 

change buffer的适用场景：

①对于唯一索引来说，所有的更新操作都会判断这个操作是否违反唯一性约束。比如，要插入（4，400）这个记录（4为主键），就要先判断现在表中是否存在k=4的记录，而这必须要将数据从磁盘读到内存中，

这样才能进行判断k=4是否存在，如果已经都读入到内存中，那直接更新内存会更快，就没必要写入change buffer了。

因此，唯一索引的更新就不能使用change buffer，只有普通索引才能够使用

②因为merge的时候是真正进行数据更新的时刻，而change buffer的作用就是将记录变更的动作缓存下来，所以在一个数据页做merge之前，change buffer记录该数据页上的变更越多越好

因此，对于写多读少的业务来说，写完数据页马上读到该数据页的可能性比较小，此时使用change buffer最好。

反之，如果写完以后马上会查到该数据页，会立即触发merge过程，如果频次较高，则change buffer的维护成本增大，所以对这这种业务模式来说，change buffer反而起到了反作用

 

change buffer的大小：

change buffer用的是buffer pool中的内存，不能无限增大，change buffer的大小，可以通过innodb_change_buffer_max_size来动态设置。

这个参数设置为50时，表示最多只能占用buffer pool的50%空间

 

2.change buffer 和 redo log 区别

change buffer主要是节省随机读磁盘的IO消耗（唯一索引，需要随机读磁盘到内存中，再更新，非唯一索引只需要写到change buffer）

redo log主要是节省随机写磁盘的IO消耗，转成顺序写

change buffer 的操作也会记录到redo log 中，进行持久化磁盘

三、优化器的索引选择

1.影响优化器选择索引的主要因素

①扫描的行数

MySQL在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录的行数。

这个统计信息就是索引的区分度。一个索引上不同的值越多，这个索引的区分度越高。而一个索引上不同值的个数，称之为基数，也就是说，基数越大，索引的区分度越好

基数的获取：

innodb默认选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到这个索引的基数。当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计

索引统计的配置：innodb_status_persistent

设置为on时，表示统计信息会持久化存储。这时，默认N为20，M为10

设置为off时，表示统计信息会存储在内存中。这时，默认N为8，M为16

 

但是由于是估算，所以会出现统计信息不正确的情况，可以使用 analyze table t 命令，重新统计索引信息

知道了一个辅助索引上不同值的个数，即基数，又如何知道一个辅助索引值下面平均对应多少条记录？

 

②回表

使用辅助索引，每次从辅助索引拿到一个主键值，都要去主键索引上查出整行的数据，这个代价优化器也会算进去。

导致不走辅助索引，直接使用主键索引全表扫描

 

③是否排序

例子：mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

a,b均有索引，order by b 有可能导致优化器选择索引b进行查询，因为根据索引b查询的话，不需要再做排序，只需要遍历

 

④临时表

 

2.选择最合适的索引

优化器选择索引不能保证每次都是最优的，有可能选择别的索引，所花费时间更少！！！！

如果出现原本可以执行很快的SQL语句，由于索引选择错误却执行的比较慢，可以使用以下方法处理：

①使用force index强行选择一个索引

select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

②修改SQL语句，引到MySQL使用我们期望的索引（具体情况具体修改）

③新建一个更合适的索引，或者删除误用的索引

根据实际需要，删除优化器选择的错误索引

④使用 analyze table t 命令，重新统计索引信息

如果explain结果预估的rows值和实际情况差距比较大，可以采用这个方法来处理

 

四、字符串索引的设置

1.整个字符串建立索引和前缀索引

例子：邮箱建立索引

email：1234@qq.com、12345@qq.com

前者：alter table User add index index1(email);

后者：alter table User add index index2(email(4));

两种方式的对比，where email = "1234@qq.com"

①前者索引文件占用的磁盘空间比后者大，前者索引文件叶子节点存储全文，后者索引文件叶子节点存储前4个字符

②前者在索引记录查询到符合条件的，继续往下查，有不符合的直接结束

后者在索引记录查询到符合条件的，需要去主键索引中查询是否条件，无论符合，都需要继续往下查，直到索引记录不符合条件

因此，前者与后者相比，查询次数一般较少，但是占用空间较大。如果想使用后者，必须定义好长度，既减少额外的查询成本，又节省空间

2.选择多长的前缀建立索引

①查询这个列上不同值的数量

select count(distinct email) from User;

②依次选取不同长度的前缀值的数量

select count(distinct left(email,4)) as L4,

count(distinct left(email,5)) as L5,

count(distinct left(email,6)) as L6

from User;

总之，根据不同前缀占的比重来选择合适的前缀索引

3.前缀索引对覆盖索引的影响

select id,email from User where email='1234@qq.com';

如果email字段使用整个字符串建立的索引，则不需要回表，直接查询完成

如果email字段使用前缀索引，则需要回表，不能使用覆盖索引的优化

4.前缀区分度不大的处理方式

①倒序存储

如果字符串前缀区分度不大，最后几位区分度大，该字段可以使用倒序存储

使用之前，需要验证倒序完的前缀是否有足够的区分度（count(distinct)）

②使用hash字段

表中新增一个整数字段，并在该字段上增加索引

 

相同点：

①都不支持范围查找，只能支持等值查询

不同点：

①占用空间

前者不需要额外的存储空间，后者需要新增一个整数字段

后者索引只需要4个字节，如果前者的索引前缀长度大于4字节，那么其实两者占用的空间差不多

②CPU消耗

前者每次读和写需要调用reverse函数，后者需要需要额外调用crc32()函数，相比起来，前者消耗小

③查询效率

前者本质还是前缀索引的方式，还是会增加扫描行数

后者查询更加稳定，虽然crc32()会有冲突概率，但是非常小

 

五、order by工作流程

例子：city字段为普通索引

select city,name,age from t where city='杭州' order by name limit 1000 ;

1.全字段排序

MySQL会给每个线程分配一块内存用于排序，称为sort buffer（server层）

①初始化sort buffer，确定放入name、city、name这三个字段

②根据索引city字段找到第一个满足city=‘杭州’条件的主键id，即ID_X

③根据上一步找到的主键id去主键索引中找到整行数据，取出city、name、age三个字段的值，存入到sort buffer

④重复2-3步骤，直到找不到满足city=’杭州‘条件

⑤对sort buffer中的数据根据name进行排序

⑥取出前1000行返回客户端

按照name排序这件事情，可能在sort buffer中进行，也可能需要使用外部排序，这取决于排序所需要的内存和参数sort_buffer_size

即所需要的内存大于sort_buffer_size，则使用磁盘临时文件辅助排序，反之，使用sort_buffer进行排序

外部排序一般使用归并排序算法，比如，将需要排序的数据分成12份，每一份单独在sort_buffer排序后存在这些临时文件中，然后把这12个有序文件合并成一个大的有序文件（归并排序）

2.rowid排序（即主键）

如果要查询的字段很多，sort_buffer中需要存放的字段太多，这样内存中同时存放的行数很少，要分成很多的临时文件，导致排序的性能下降

set max_length_for_sort_data=16（sort_buffer中单条记录最大字节数）

此时，如果单行的长度超过了上面这个配置，MySQL会采取另外一种策略。

①初始化sort_buffer，确定放入name和id（需要使用name进行排序、使用id最后再查询）

②从索引city字段中找到第一个满足第一个city='杭州'条件的主键id，即ID_X

③根据找到的主键id，去主键索引中找到整行数据，取出name、id两个字段，存入到sort_buffer中

④重复2-3步骤，知道找不到满足city='杭州'条件

⑤对sort buffer中的数据根据name进行排序

⑥排序完成后，取出前1000行，并按照id去主键索引中查询出city、name、age三个字段，返回客户端

总的来说，如果排序内存太小，才会使用rowid排序或者是使用外部文件；如果内存足够大，则会优先选择全字段排序，不需要去原表取数据。

3.如何避免排序操作

①建立city和name的联合索引，这样就不需要额外的排序操作，直接查询出前1000条记录就可以了

②建立city、name、age的联合索引，不仅避免了额外的排序操作，还避免了回表查询age的操作

 

六、索引失效

1.对索引做函数操作

对索引做函数操作，可能会破坏索引值的有序性，因此优化器就会决定放弃走树搜索功能。但是，并不意味着优化器放弃使用索引，可能走遍历主键索引，也可能遍历普通索引

例子：select count(*) from t where month(create_time)=7;（create_time索引图如下）

但是，对于select count(*) from t where id +1 = 10000这个SQL语句，加一操作并不会改变有序性，但是MySQL优化器还是不能用id索引来快速定位到9999这一行，

所以，需要写成where id = 10000 - 1才可以。

2.隐式类型转换

字符串和数字作比较的时候，会将字符串转换成数字

例子：select * from tradelog where traceid = 110;（traceid字段类型为varchar，普通索引）

===>select * from tradelog where CAST(traceid AS signed int) = 110;

本质上还是对索引做函数操作，因此优化器放弃走树搜索功能

例子：select * from tradelog where id = "110";（id字段类型为bight，主键索引）

===>select * from tradelog where id = CAST("110" AS signed int);

没有对索引字段做函数操作，因此优化器选择走树搜索功能

3.隐式字符编码转换

字符集utf8mb4是utf8的超集，当这两个类型的字符串做比较时，会先将utf8字符串转换成utf8mb4字符集，再进行比较

例子：trade_detail的traceid为utf8编码，trade_log的traceid为utf8mb4编码，两个表的traceid和id均为索引

①mysql> select detail.* from trade_log log, trade_detail detail where detail.tradeid=log.traceid and log.id=2; 

此时log表可以根据索引查询到id为2的数据，此时拿到了log表的traceid（utf8mb4编码），

由于 utf8 = utf8mb4，导致左边的detail.traceid需要进行函数转换编码，从而detail不走索引

===>where CONVERT(detail.tradeid USING utf8mb4) = log.traceid

②where detail.tradeid=log.traceid and detail.id=2;

此时走detail的id索引和log的traceid索引

这篇关于MySQL---1.索引的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！