Mysql-高性能的索引策略

高效地选择和使用索引有很多方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。使用哪个索引，以及如何评估选择不同索引的性能影响的技巧则需要持续不断地学习。以下是如何高效使用索引的技巧。

• 索引列必须是独立的列。也即索引列不能是表达式的一部分，也不能是函数的参数
• 使用前缀索引索引开始的部分字符，这样可以大大节省索引空间，从而提高索引效率。但这样也会降低索引的选择性（选择性是指，索引中不重
复的记录数-基数 和总记录数的比值）。索引的选择性越高则查询效率越高，可以让mysql在查询时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。另外，前缀索引不能做order by 和 group by,也无法使用前缀索引做覆盖扫描
• 覆盖索引（covering index）指一个查询语句的结果只用从索引中就能够取得，不必从数据表中读取.覆盖索引可以极大的提升性能.首先索引条目远远小于数据行大小,因此如果只需要读取索引,那Mysql就能极大地减少数据访问量.其次索引是按照列值顺序存储的.所以对于I/O密集型的应用,范围查询会比随机从磁盘读取数据的I/O要少得多.
另外,覆盖索引不支持hash,r-tree和全文索引.
• 一般来说，在需要考虑到排序和分组的情况下，将选择性较高的放在前列可以提高索引效率，这时候索引的作用只是用于优化where条件的查找，可以更快的过滤出需要的行。然而，性能不只是依赖于索引的选择性，也和查询时的具体值有关，也就是和值的分布有关。因此，可能需要根据那些使用频率最高的查询来调整索引的顺序.
• 对于高并发负载,在InnoDB中按照主键顺序插入可能造成明显的竞争.因为所有的插入都发生在这里.所以并发插入可能导致间隙锁竞争和自增锁竞争.可以通过修改innodb_autoinc_lock_mode参数进行优化 :
1.tradition(innodb_autoinc_lock_mode=0) 向后兼容,在这个模式下,所有的insert语句在开始执行时都要得到一个auto_inc(自增锁).在语句结束时释放锁.由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入.
2.consecutive(innodb_autoinc_lock_mode=1) 连续模式.由于普通insert一次性插入的个数可以立刻得到确定,所以mysql可以一次性生成几个连续的值,用于这个语句.这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁
3.interleaved(innodb_autoinc_lock_mode=2)交叉存取. 由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。
• Mysql有两种方式可以生成有序的结果. 一种是通过排序操作,另一种是通过索引扫描.因此,如果可能的话,设计索引时应该尽量同时满足这两种任务,这样是最好的.
在使用索引排序时,有以下限制 :
1.索引的顺序必须和order by字句的顺序一致,而且列的排序方向(正序 or 倒序)也必须一样.否则mysql无法使用索引对结果做排序.
2.如果查询时需要关联多张表,那么order by 语句引用的字段必须全部为第一个表.
3.需要满足最左前缀原则.但是有一种情况例外,那就是左列为常量的时候,如果在where或者join 中对这些列指定了常量,就可以弥补索引的不足.
• 对于特别常用但选择性比较低的列,可以考虑在每个索引里加上它.而且即使查询里没有使用到它,也可以通过in(‘xx’ ,‘yy’)来让Mysql选择该索引.这样写不会过滤掉任何行,和没有这个条件时的返回结果相同,但是加上这个列条件就可以匹配索引的最左前缀.
• mysql无法同时使用两个索引.因此,应该尽量避免多个范围条件.
• 对于大偏移量的查询,有两种解决方法.一个方法是限制用户能够翻页的数量.因为用户很少会真正在乎搜索结果的第1w页.
还有一种策略是使用延迟关联.通过覆盖索引查询返回需要的主键,再根据这些主键关联原表获取需要的行.(
举个例子,下面的查询 select b.id,b.book_name from book_store b order by b.title limit 15000,50 会导致mysql查询15050条数据然后只返回最后50条
可以改写成 select b.id,b.book_name from book_store join (select id from from book_store order by title limit 15000,50) lim using(id)
)
• 在关联查询时,需要特别注意以下几点 :
1.确保on 或者using 子句列上有索引.因此,在创建索引时就要考虑关联的顺序.没有用到的索引只会带来额外的负担,一般来说,除非有特别的理由,否则只需要在关联顺序的第二张表上建立索引.
2.确保任何的group by或者 order by的表达式中只涉及到一张表的索引,这样mysql才能使用索引来优化这个过程.