select id from T where k=5
查询过程使用普通索引和唯一索引带来的性能差距微乎其微
当更新一个数据页时, 若这个数据也在内存中, 就直接更新, 但是如果这个数据页不在内存中, 在不影响数据一致性的前提下. innodb会将这些更新操作缓存在change buffer中, 这样就不需要从磁盘中读入这个数据页了. 下次查询的时候, 将数据页读入内存, 然后执行change buff中与这个页相关的操作.
虽然叫做change buff, 实际上他是可持久化的数据. change buffer 在内存中有拷贝. 也会被写入到磁盘上.
将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作.
如果能够将更新操作先记录在 change buffer,减少读磁盘,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避免占用内存,提高内存利用率.
只有普通索引可以使用change buffer
因为merge的时候是真正进行数据更新的时刻, 而change buffer的主要目的就是将记录的表更动作缓存下来,所以在一个数据也merge之前, change buffer 记录的变更越多, 收益越大
对于写多读少的业务, change buffer使用效果更好. eg 账单类 日志类系统.
反之一个业务更新后会立刻会做查询, 虽然更新会先记录会记录到change buffer 中, 但是由于查询会立刻触发merge过程. 这样随机访问io的次数不会减少, 反而增加了change buffer 的维护代价.
简单对比两个机制在提升性能上的收益话. redo log 主要节省的是随机写磁盘的io消耗, 而change buffer 主要节省的是随机读磁盘的io消耗.
mysql> alter table SUser add index index1(email);
mysql> alter table SUser add index index2(email(6));
查看索引的命令是:
show index from TABLE;
两个索引的区别是:
由于index2索引结构中每个字符串只取前六个字节. 所以占用空间会小, 这是前缀索引的优势.
eg. 分别看看两个索引定义下, 查询是怎么执行的.
select id,name,email from SUser where email='zhangssxyz@xxx.com';
if use index1 (整个字符串):
这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行。
if use index2(前6个字符):
所以说使用前缀索引, 定义好长度, 就可以做到既节省空间, 又不用额外增加太多的查询成本.
计算列区分度.
mysql> select count(distinct email) as L from SUser;
前缀索引除可能会增加扫描行数, 影响到性能还有什么影响?
看个例子
select id,email from SUser where email='zhangssxyz@xxx.com';
相比之前的例子, 这个语句只要求返回id 和 email.
如果使用index1. 可以利用覆盖索引, 从index查到结果后, 直接返回, 不需要回表.
如果使用index2. 取到id之后, 还需要再回表判断email的值.
即使将index2的定义修改为email(18)的前缀索引. 还是会需要回表. 因为系统并不能确定前缀索引的定义是否截断
如果使用了前缀索引就用上覆盖索引对查询性能的优化了.
有什么方法可以既占用空间小, 也能达到相同的效率?
如果你存储身份证号的时候把它倒过来存,每次查询的时候,你可以这么写:
mysql> select field_list from t where id_card = reverse('input_id_card_string');
由于身份证号的最后 6 位没有地址码这样的重复逻辑,所以最后这 6 位很可能就提供了足够的区分度。当然了,实践中你不要忘记使用 count(distinct) 方法去做个验证。
以在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
然后每次插入新记录的时候,都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突,也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
索引的长度变成了 4 个字节,比原来小了很多。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
扫码关注腾讯云开发者
领取腾讯云代金券
Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号:粤B2-20090059 深公网安备号 44030502008569
腾讯云计算(北京)有限责任公司 京ICP证150476号 | 京ICP备11018762号 | 京公网安备号11010802020287
Copyright © 2013 - 2025 Tencent Cloud.
All Rights Reserved. 腾讯云 版权所有