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

隐式转换

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

最左前缀索引

暂无。