mysql索引使用经验总结

栏目: 数据库 · 发布时间: 6年前

内容简介:比如邮箱或者身份证号的存储,使用前缀索引,前缀索引长度的选择。注意:对于区分度不太好的,例如身份证号。之后后面几位才有区分度。那么如何存储?

字符串创建索引(前缀索引)

比如邮箱或者身份证号的存储,使用前缀索引,前缀索引长度的选择。

select 
   count(distinct left(email,4)) as a,
   count(distinct left(email,4)) as b,
   count(distinct left(email,4)) as c,from table 复制代码

注意: 前缀索引会使覆盖索引失效 ,因为前缀索引只有部分数据,需要回表找到完整数据。

对于区分度不太好的,例如身份证号。之后后面几位才有区分度。那么如何存储?

  •   倒序存储之后取前缀索引
    • select * from table where card = reverse('card_string')复制代码
  • 使用hash字段存储 (查询的时候必须使用等于,精确匹配)
    • select * from t where card = crc32('card_string')复制代码

上面2个缺点是都不支持范围查询。

总结:

  • 直接创建索引,空间占用大。
  • 前缀索引,节省空间,但是覆盖索引会失效。
  • 对于前缀区分度不高的使用,倒序存储,不支持范围查询。
  • 创建hash字段索引,性能稳定,也不支持范围查询。

考虑一种场景:用户名+密码的登录。因为只有单数据查询没有范围查询场景,一般可以吧多个字段hash字后存储。

唯一索引和普通索引的选取

前提:在业务保证一致的情况下,如何选择?

查询

select * from where a = '1'; 复制代码

a为唯一索引:mysql会在a索引树上找到1这条数据的id,回表查出所有数据。

a为普通索引:mysql在a索引上找到1之后会接着找下一个,直到下一个不为1,然后同样回表查出数据返回。 如果查下一个数据的时候正好在下一页,会涉及到一次io,但是一个数据页一般会放进千个数据,索引概率相对较小。

更新

更新数据的时候,如果数据在数据页中就直接更新,否则存入change buffer中,就不会从磁盘中读取数据。下次查询访问数据也的时候,将数据也读入内存,执行change buffer 中与这个页面的merge操作。通过这种方式保证一致性。数据库也会定期执行merge操作。

update table set a = a+1  where xxx;复制代码

由上可知:

当更新的数据在内存中的话:

  • 唯一索引:判断数据唯一,直接更新内存数据。
  • 普通索引:直接更新内存数据。

更新数据不在内存:

  • 唯一索引:数据读入内存,(io)判断是否是否重复,不重复就插入
  • 普通索引:直接写change buffer  

场景:如果写入之后立马会读,就没必要使用change buffer 。反而增加维护成本。但是对于大数据量的写的操作,性能会有很大的提升。

隐式转换

由于数据库类型不匹配可能会涉及到隐式转换,隐式转换之后索引会失效。所以当创建了索引查询依旧很慢的时候,需要注意这一点。工作中也多次碰到过。

最左前缀索引

暂无。


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Android 源码设计模式解析与实战

Android 源码设计模式解析与实战

何红辉、关爱民 / 人民邮电出版社 / 2015-11 / 79.00元

本书专门介绍Android源代码的设计模式,共26章,主要讲解面向对象的六大原则、主流的设计模式以及MVC和MVP模式。主要内容为:优化代码的首步、开闭原则、里氏替换原则、依赖倒置原则、接口隔离原则、迪米特原则、单例模式、Builder模式、原型模式、工厂方法模式、抽象工厂模式、策略模式、状态模式、责任链模式、解释器模式、命令模式、观察者模式、备忘录模式、迭代器模式、模板方法模式、访问者模式、中介......一起来看看 《Android 源码设计模式解析与实战》 这本书的介绍吧!

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具