社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

mysql索引使用经验总结

小糖糖_ • 6 年前 • 216 次点击  
阅读 7

mysql索引使用经验总结

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

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

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 。反而增加维护成本。但是对于大数据量的写的操作,性能会有很大的提升。

隐式转换

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

最左前缀索引

暂无。



Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/32923
 
216 次点击