技术总监夸我“索引”用的溜，我飘了......( 四 ) _索引

mysql> SELECT count(*) FROM tradelog WHERE month(t_modified)=7; 由于索引列是函数的参数，所以显然无法用到索引，我们可以将它改造成基本字段区间的查找如下：
SELECT count(*) FROM tradelog WHERE-> (t_modified >= '2016-7-1' AND t_modified<'2016-8-1') or-> (t_modified >= '2017-7-1' AND t_modified<'2017-8-1') or-> (t_modified >= '2018-7-1' AND t_modified<'2018-8-1'); 前缀索引与索引选择性
之前我们说过，如于长字符串的字段(如 url) ，我们可以用伪哈希索引的形式来创建索引，以避免索引变得既大又慢。
除此之外其实还可以用前缀索引(字符串的部分字符)的形式来达到我们的目的，那么这个前缀索引应该如何选取呢，这叫涉及到一个叫索引选择性的概念。
索引选择性：不重复的索引值(也称为基数， cardinality)和数据表的记录总数的比值，比值越高，代表索引的选择性越好，唯一索引的选择性是最好的，比值是 1 。
画外音：我们可以通过 SHOW INDEXES FROM table 来查看每个索引 cardinality 的值以评估索引设计的合理性。
怎么选择这个比例呢，我们可以分别取前 3 ， 4 ， 5 ， 6 ， 7 的前缀索引，然后再比较下选择这几个前缀索引的选择性，执行以下语句：
SELECTCOUNT(DISTINCT LEFT(city,3))/COUNT(*) as sel3,COUNT(DISTINCT LEFT(city,4))/COUNT(*) as sel4,COUNT(DISTINCT LEFT(city,5))/COUNT(*) as sel5,COUNT(DISTINCT LEFT(city,6))/COUNT(*) as sel6,COUNT(DISTINCT LEFT(city,7))/COUNT(*) as sel7 FROM city_demo 得结果如下：

文章插图

可以看到当前缀长度为 7 时，索引选择性提升的比例已经很小了，也就是说应该选择 city 的前六个字符作为前缀索引，如下：
ALTER TABLE city_demo ADD KEY(city(6)) 我们当前是以平均选择性为指标的，有时候这样是不够的，还得考虑最坏情况下的选择性。
以这个 demo 为例，可能一些人看到选择 4 ， 5 的前缀索引与选择 6 ， 7 的选择性相差不大，那就得看下选择 4 ， 5 的前缀索引分布是否均匀了：
SELECTCOUNT(*) AScnt,LEFT(city, 4) AS prefFROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5 可能会出现以下结果：

文章插图

可以看到分布极不均匀，以 Sant ， Toul 为前缀索引的数量极多，这两者的选择性都不是很理想，所以要选择前缀索引时也要考虑最差的选择性的情况。
前缀索引虽然能实现索引占用空间小且快的效果，但它也有明显的弱点， MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY ，而且也无法使用前缀索引做覆盖扫描，前缀索引也有可能增加扫描行数。
假设有以下表数据及要执行的 SQL：

文章插图

SELECT id,email FROM user WHERE email='zhangssxyz@xxx.com'; 如果我们针对 email 设置的是整个字段的索引，则上表中根据「zhangssxyz@163.com」查询到相关记记录后,再查询此记录的下一条记录，发现没有，停止扫描。
此时可知只扫描一行记录，如果我们以前六个字符(即 email(6))作为前缀索引，则显然要扫描四行记录，并且获得行记录后不得不回到主键索引再判断 email 字段的值，所以使用前缀索引要评估它带来的这些开销。
另外有一种情况我们可能需要考虑一下，如果前缀基本都是相同的该怎么办，比如现在我们为某市的市民建立一个人口信息表，则这个市人口的身份证虽然不同，但身份证前面的几位数都是相同的，这种情况该怎么建立前缀索引呢。
一种方式就是我们上文说的，针对身份证建立哈希索引，另一种方式比较巧妙，将身份证倒序存储，查的时候可以按如下方式查询：