MySQL---1.索引

2021/4/17 19:26:37

本文主要是介绍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.索引的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程