Mysql高性能索引须知

一、索引的优点：

1，大大减少服务器需要扫描的数量
2，可以帮助服务器避免排序和临时表
3，可以将随机I/O变为顺序I/O

以常见的B-tree索引为例，它是按照顺序存储数据的，mysql在使用group by 和order by的时候，可以直接使用索引数据，如果索引的列数据包含了查询数据则不需要回表查询数据就可以直接从索引中获取。

二、索引是最好的解决方案吗？

明显不是，并不是所有的情况下使用索引都是有效的。总的来说只有索引帮助查询记录带来的好处大于其额外的开销的时候，索引才是有效的。
对于比较小的表，直接扫描效果更好，对于中到大型的表索引一般效果较好，但也要考虑索引建立的开销也会很大。需要根据实际的应用场景来对比评估。

三、高性能索引策略

1，查询条件要是独立的列，不能是表达式，也不能是函数，否则无法使用索引

select a from table where a + 1 =10 // 这种就用不了索引了

2，需要索引的字段很长的时候，可以考虑前缀索引。

select count(*) as cnt ,a from table group by a order by cnt desc ;
// 查看所要索引的列的真实分布情况

select count(*) as cnt ,left(a,3) as pref from table froup by pref order by cnt desc;
// 使用前缀来查看列的真实分布，通过调整前缀长度找到最合适的前缀索引

alter table table add key(a(3)); 
// 创建前缀索引 ，缺点是无法使用前缀索引做group by和order by操作

3，不要忘了，索引可以建多列的联合索引。当查询语句中出现多个and
或者or的时候。
4，索引的三星系统

1. 索引将相关的记录放到一起，则获得一星
2. 如果索引中的数据顺序和查找中的排列顺序一致则获得二星
3. 如果索引中的列包含了查询中的需要的全部列则获得三星

5，联合索引的列顺序选择

 selec * from payment where staff_id = 1 and customer_id =1000;
//是应该创建staff_id,customer_id索引呢，还是应该颠倒呢？

select count(distinct staff_id)/count(*) as staff_id_choose,count(distinct customer_id)/count(*) as customer_id_choose,count(*) as cnt from payment;
//看看哪个字段的选择性最高，值高的作为索引的第一列。这样可以更快的过滤出所需的行

6，聚簇索引【一种数据存储方式】：在同一个结构中保存了B-tree索引和数据行。能够提高查询速度，但是插入，更新操作代价极高。
7，覆盖索引：不用回表，大大的好。mysql只能使用b-tree索引来做覆盖索引。
8，索引排序

1，只有当索引列顺序与order by子句的顺序完全一致时，并且所有列的排序方向都一样时，mysql才能够使用索引来对结果进行排序。
2，如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表的数据时才可以使用索引做排序
3，需要满足最左前缀匹配，但如果索引前导列为常量时可以不满足最左前缀。例如key(a,b,c) , where a=1 order by b,c 则即便order by子句不满足最左前缀也可以使用索引排序。

9，压缩索引，可以使用更少的索引空间，但是代价是某些操作可能更慢。可以通过建表的参数pack_keys来控制索引压缩的方式。
10，冗余和重复索引，mysql可以建多个相同列索引，需要尽量避免。如果创建了key（A,B）再创建了key（A）则是冗余索引。

QPS：每秒查询语句查询的次数。

11，索引和锁。innodb只有在访问行的时候才会对其加锁，索引能够减少访问的行数，从而减少锁的数量。