MySQL字符串索引&脏页刷盘

字符串加索引的方式？

对于字符串进行添加索引，我们除了对整个字符串加索引以外，还可以添加前缀索引。

什么是前缀索引？

前缀索引的好处？

使用前缀索引，定义好长度，可以做到即节省空间，又不用额外增加太多的查询成本。

前缀索引的弊端？

前缀索引会使覆盖索引失效，额外增加回表的消耗，如果前缀索引的长度选择区分度不高，会额外导致扫描行数增加。

其他给字符串加索引的方式？

什么是脏页？

MySQL在更新数据的时候会写redo log并且更新内存以后就会返回，数据文件并不会立即更新，这就是所谓的WAL机制。

当内存被更新以后，内存中的数据页就会和磁盘上的数据页存在不一致的情况，该内存也就被称为 脏页 。

内存中的数据被写入磁盘以后，内容变为一致，此时该内存页就被称为干净页。

什么叫刷脏页？

内存数据页中的内容被写入磁盘数据页中的过程称为刷脏页。

什么时候会刷脏页？

InnoDB如何控制刷脏页的频率？

首先确认InnoDB所在主机的IO能力，此时需要用到数据库的innodb_io_capacity参数，该参数推荐设置为磁盘的IOPS。磁盘的IOPS可以通过fio工具进行测试。

InndoDB刷脏页主要考虑以下两个因素：

MySQL会根据F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

脏页比例

参数innodb_max_dirty_pages_pct是脏页比例的上限，MySQL 8.0中是90%。

当前脏页比例可以通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total计算获得，具体sql计算指令如下：

连坐机制

InnoDB在刷脏页的时候，如果该脏页旁边的是页也是脏页，会同时把相邻的脏页刷掉。

该刷脏页行为由参数innodb_flush_neighbors控制:

对于机械硬盘，开启连坐会减少随机IO的消耗，但对于SSD，没必要开启该参数。

作为一名程序员，在求职面试时，不知你有没有遇到类似这样的问题。

张工是一名java程序员，最近到一家软件公司应聘软件开发岗位，面试官问了他关于MySql索引这样的一个问题。

对于这个问题张工之前在做项目时也曾遇到，那时候字段明明是加了索引，可不明白为什么还是很慢。后加上引号就正常了，为了赶项目进度，张工也没有再去留意。

现在面试官突然这么一问，张工也说不出个所以然来。

面试官让他回去等通知。

我们知道MySql索引可以加快数据检索速度，这也是使用的索引的最主要原因。但有时候使用不当就会遇到索引失效问题，譬如在MySQL字符串类型查询时不加引号索引会失效，是因为MySQL内部进行了隐式转换。

那为什么会发生隐式转换？又是怎么转换的呢？

今天我们来聊聊关于MySql索引失效的话题。

先来看看一般导致索引失效的有哪些？

如果一张表的索引有多个，要遵守最佳左前缀法则，即查询从索引的最左前列开始并且不跳过索引中的列。

用户表tb_user字段 id，name，age，sex

创建索引为idx_user_name

执行语句：

这时候就会导致索引失效

在索引列上做加工 *** 作，查询时会导致索引失效，从而导致全表扫描。所以，建议不要在索引列上做任何 *** 作。

举个例子，例如订单表tb_order有个索引是dt（日期）， 字段数据存放的格式是这样的2021-12-10 这样的，如果有个需求需要根据dt，格式是20220207这样的来查询，这时候就不要对dt进行格式转换了，

这样索引就失效了。

而是应该对 20220207做格式处理

这样dt索引才不会失效。

例如我们在订单表tb_order建立了索引idx_order_id，order_id字段类型为varchar

在查询时如果使用where order_id= 20220207123654100，这样的查询方式会直接造成索引失效。

要让索引生效，正确的用法为

假如有张用户表tb_user，创建的索引为idx_user_name_age_sex_phone 其中name、age、sex都加了索引。

执行语句

上面这条sql语句只会命中name和age索引，sex索引会失效，复合索引失效需要查看key_len的长度。

再来看一个例子：

从这两条SQL执行的结果我们可以看出，执行第一条SQL没有使用到索引，而执行第二条SQL时使用到了索引。这是为什么呢？

我们需要先了解下mysql索引优化器工作的原理。选择索引是优化器工作，优化器工作有自己的一套规则，如果等号两边的数据类型不一致，则会发生隐式转换。

基于这条规则，我们回过头看看

这条SQL语句执行时就会变为

由于对索引列进行了函数 *** 作，所以才导致索引失效，从而全表扫描了。

那么问题来了，细心的你不知有没有留意到为什么是把左侧的列转为int类型，而不是把右侧的值转成字符串类型呢？

什么情况下把数字转为字符串，什么情况下把字符串转为数字，优化器它是根据什么规则来进行判断的？其实规则也并不复杂。

根据这个规则，我们再回过头看看之前的查询语句

select '12345678936' = 12345678936

返回1 所以这时候就把左侧的列值12345678936转成数字。

关于MySql索引失效的问题先简单写到这，建议平时在做项目时还是要多了解下原理，如果你了解其背后的原理，求职面试时和面试官交流起来就会很舒服了，相信能为这次面试加分，提高被录用的概率。

为什么MySQL字符串类型查询时不加引号索引会失效？这是因为要查询的字符串字段没有加引号时，MySQL内部进行了隐式转换，此次查询会导致全表扫描，所以慢了。

总结：

在索引列上进行了函数 *** 作，MySQL内部会进行了隐式转换，导致索引失效，从而产生全表扫描。

由于笔者知识及水平有限,文中错漏之处在所难免,如有不足之处，欢迎交流。

拓展

索引创建

1、主键索引：

2、唯一索引：

3、普通索引：

4、全文索引：

alter table table_name add fulltext (column)

5、联合索引：

索引删除

前缀索引顾名思义，定义字符串的一部分当做索引，而不是把整个字符串当做索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

假设一张表有 id,name,email 2个字段

1.创建email列的普通索引应该是： alter table T add index idx_email1( email )

2.前缀索引的创建规则为： alter table table T add index idx_email2( email(6) )

当然第一索引包含是的整个字符串，第二个是该字段前6个字节（注意是字节）

对于这2中索引，B+树怎么存储呢？

INSERT INTO T (email) VALUES ('瞎子','[email protected]'), ('剑圣','[email protected]'), ('露娜','[email protected]'), ('李白','[email protected]'), ('韩信','[email protected]'), ('百里玄策','[email protected]')

【谁还不是个野王啊】

普通索引存储为：

是的你没看错，前缀索引那颗树上的存储的是email的前6位字节，也就是你创建前缀索引时指定的前缀字节长度。2种树相比，前缀索引存储了更少的数据，那么他所耗费的空间也就相比较少，这正是他的一个优点。同样的也就相对的增加了扫描行数。

什么增加了扫描行数？？？？ 这是为什么呢？

那么小朋友咱们一起来看下吧。

假设SQL如此这般： select id,name,email from T where email = '[email protected]'

那么这2个SQL，应该怎么 *** 作呢。

idx_email1:

2.到主键上查到主键为ID1的，判断email值是否正确【为什么判断呢，其实我理解是为了二次判断保证数据一致性吧，比较官方的解释尚未找到】，正确放入结果集

3.取 idx_email1 索引树上刚刚查到的位置的下一条记录，如此往复。

循环过程中，需要回主键取1次数据，所以系统可以认为只扫描了一行【1次是数第一棵树数出来的】

idx_email2:

1.从 索引数上找到满足索引值为 'zhangs'的该记录，取得 ID1的值

2.到主键上查到主键值是 ID1 的行，判断出 email 的值是’ [email protected] ’，这行记录放入结果集【不是要的值，丢弃，进行下一步】

3.取 idx_email2 上刚刚查到的位置的下一条记录,重复以上步骤

在这个过程中，要回主键索引取 3 次数据，也就是扫描了 3 行。通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。

但是，对于这个查询语句来说，如果你定义的 idx_email2 不是 email(6) 而是 email(8），也就是说取 email 字段的前 8 个字节来构建索引的话，即满足前缀’zhangsh’的记录只有一个，也能够直接查到 ID1，只扫描一行就结束了。也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

那么问题来了，到底定义多长才算是合理呢？

一般的定义原则是 count(distinct(columnName))/count(*) ,当前缀索引【count(distinct(columnName(length)))，length是你想要创建列的前缀字节长度】越接近此值越好，当有多个前缀字节都一样且都等于这个值时怎么选择呢，当然是 字节越少越好了哈，字节越少越省空间。索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

count(distinct(columnName(length))) 翻译到SQL 为： count(dictinct(left(colunmName, length)))

前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能。其实，前缀索引的影响不止如此，我们再看一下另外一个场景。

来呀，上SQL： select id,email from T where email='[email protected]'

如果按照email全字段索引，那么此SQL 是不需要回表的【为什么不需要回表？兄嘚，这个相当于覆盖索引了哈】

那么如果按照前缀索引是否需要回表呢？答案是的。

因为当判断前6个字节相等后，需要拿到id 回表拿到email的全部内容进行比较，如果不相同，丢弃这行，否则加入结果集。

那么有人会问了，我把长度放大点，包含所有字节不就好了吗？

那么此时会有如下问题。

1.当你此时的长度是囊括了全字段，但是系统是不知道的，他还是需要回表再次判断的，去确定前缀索引的定义是否截断了完整信息。

2.此时长度是够了，那么能肯定因为业务日后不会增加长度吗？

3.尽可能的加长长度，还不如直接建立全字段索引呢

综上，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

前面说到的是，可以根据字段前面几个字节进行查询的，那么对于身份z这种，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份z号前 6 位一般会是相同的。

或许你会说，多弄几个字节不就好吗？那么请问下自己为什么使用前缀索引呢，不就是为了节省空间吗？

那么这么做合适吗？ 不合适对吗？ 乖~，快去反省下吧

那么采用前缀索引显示是不行的，那么如果用前缀索引怎么办呢，聪明的你应该已经猜到了，采用倒叙存储，然后建立前缀索引。

放到SQL 中就应该是这样的： select field_list from t where id_card = reverse('id_card_string')

当然了，这种逻辑建议放到业务逻辑中实现，而不是放到SQL 中。

按照上述第4节的内容，有人或许会有另一个想法，还倒叙建立前缀索引复杂不，hash索引或者hash字段不香吗？

有人会问了，为什么要在创建一个值来存储hash值呢，如果不存储你知道原值是什么吗？ 同时hash算法是有一定重复可能的（hash值碰撞）

【可以了解下partition算法哦:[ https://selfboot.cn/2016/09/01/lost_partition 】。如果重复了，不存储原值，你是无法判断出正确数据的。

注：【hash字段不代表hash索引，hash索引原理正在快马加鞭】，简单说下hash索引，hash索引不需要创建一个值来存储hash值，而是有hasn表来存储【hash值碰撞时，由一个链表来搞定了】，存储的内容为 hash值和每行的行指针 。

说回来啊，跑题了

查询时： select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string'

不过有个问题相信你也想到了，不管是hash存储值还是hash索引都是不支持范围查询的。

来总结下这2个优缺点吧

1.从占用空间来看呢，倒叙索引不需要额外开辟存储空间，而hash字段需要额外的一个字段，所以从这点上看倒叙索引更胜一筹，NO!并不准确,如果前缀长度过长，那么这2个情况额外的空间也就相差无几了

3.从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数

1.全字段完整索引比较占空间，但是而走覆盖索引

2.前缀索引，节省空间，但会增加扫描 次数 并且不能使用覆盖索引【每次都需回表校验】

3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。【倒叙方法建立放到业务逻辑中】

4.hash字段索引，相比前缀索引性能较为稳定，但是有额外的存储空间和计算消耗，同时也 不 支持范围查询

---