关于mysql下索引使用的一点心得

最近接到了一个需求，要对后台的一个查询功能进行优化，其实功能是非常简单的，就是从异步数据库拉取数据总量再分页显示，主要就两句sql，一个是计算总量，另一个是分页拉取，进入到数据库分别执行了两句sql后，发现其中一句拉取数据的耗时基本都在0.2s之内，而另一句就出问题，由于这张表的数据量在百万左右，发现

SELECT COUNT(*) AS count FROM xxx.xxx WHERE  `begin_time` between '2016-08-02 00:00:00' AND '2016-08-12 19:00:00' AND task_stat IN (2,3,4);

这一句的执行时间普遍在2s以上，而为了不影响线上数据库，拉到本地后执行时间更是在4s以上，而相比1s以内的优化要求，这无疑是无法忍受的，毫无疑问后台查找的性能瓶颈就在这句sql上，通过使用explain（或desc）分析发现，性能瓶颈的原因是这句sql中没有使用任何的索引，而通过show create table xxx发现，这张表中只有两个作为id的字段存在索引，而对于这句sql来说，原本存在的索引基本没用，只能添加新的索引来解决这个问题

对业务进行了分析过后发现，在拼接where条件的过程中，使用最频繁的就是begin_time和task_stat这两个字段，一开始的思路是对begin_time做单列索引，发现并没有生效，发现原来是mysql中使用转换函数如UNIX_TIMESTAMP这种对数据处理后是不会使用索引的（上文贴出来是我修改后的sql，原版sql对字段进行了UNIX_TIMESTAMP处理后与时间戳进行比较），然而如上图进行修改后却发现依然在查询中没有使用到索引，查阅了一些资料后发现在mysql索引的另一个机制，理论上来讲，在使用索引进行搜索时，得出的结果中数据量越小那么使用索引的效率也就越高，如对主键id进行的搜索，而如果结果集的数据量过大，那么索引的效率其实是非常低，如存在一个性别字段上的索引，对于这个字段来说数值只有男女两种，那么索引其实是没有什么意义的，效率基本和扫描整表没什么区别，因而结果集过大的情况下，mysql会默认放弃使用索引，改为全表搜索，不过这似乎也和字段类型以及mysql的配置、版本有关，在经过了反复测试后，发现在begin_time上的单列索引大体情况如下：

可以看到，大体上在结果集数量大于总数据1/10以上时，mysql就会放弃使用索引，改为全表搜索，导致效率又重新变低，不过在sql使用force index强制使用索引测试时发现，在这种情况索引下搜索速度依然是要快一些，会随着数据的增加在1s基础上增长，可以说性能已经比较糟糕了

最终真正解决问题的方法是在begin_time和task_stat上建立联合索引来实现的，注意联合索引的最左匹配原则，在这里我是将begin_time作为左列建立的索引，原因是这个字段在查询中使用最频繁，不过比较费解的是使用联合索引是，不管多大的数据量，查询时都会使用索引并且性能良好，同事给出的说法是是否使用索引和索引的选择率有关，也就是不重复的索引值和总数的比值，越接近1，就越适合索引，大概是联合索引下，两个字段联合的索引值不重复的情况更多，因而索引使用的效果更好