MySQL索引实践

索引使用的原则

1 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d 	= 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2 =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1	，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们		都要求是0.1以上，即平均1条扫描10条记录。

4 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值	，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6 如果表记录比较少，则可以不用使用索引。

使用联合索引的七种情况

1 全列索引
	mysql会使用索引，列顺序可以颠倒。

2 最左前缀索引
	mysql会使用索引

3 查询条件用到了索引中的精确匹配，但中间某个条件未提供
	mysql不会使用索引，但是如果把中间缺失的那个索引列补上，则可以有index优化。
	“IN”可以用于索引列。

4 查询条件未指定索引第一列
	mysql不会使用索引。

5 匹配某列的前缀字符串
	只要通配符%不出现在开头，mysql可以为此列使用索引。

6 范围查询
	范围列可以用到索引，必须是最左前缀，但范围列后面的索引则列无法用到索引。

7 查询条件列含有函数或者表达式
	mysql不会为此列使用索引。

order by语句索引优化

ORDER BY语句不用精确匹配索引，只要联合索引中其他的列在where语句中是常量即可。下面的带有ORDER BY的语句是会用到索引的情况：

SELECT * FROM t1
  ORDER BY key_part1,key_part2,...;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = 1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

有些时候，虽然利用索引查询到符合where语句的记录，但是mysql不会为ORDER BY语句进行索引优化，如下面所示：

在ORDER BY语句中用到不同的索引键：
	SELECT * FROM t1 ORDER BY key1, key2;

不按最左前缀原则，在ORDER BY语句中使用联合索引中的键：
	SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

混合使用ASC和DESC：
	SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

WHERE语句中的索引键和ORDER BY中的索引键不一样：
	SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

ORDER BY后的索引键用于某个表达式或者函数：
	SELECT * FROM t1 ORDER BY ABS(key);
	SELECT * FROM t1 ORDER BY -key;

在联合查询时，用于ORDER BY的列不全是来自第一个非常量表（用于查询记录）；

ORDER BY和GROUP BY包含不同表达式；

在ORDER BY语句中使用前缀索引；

group by语句索引优化

实现GROUP BY语句的一般方式是：扫描整个表；根据每个组的连续元素创建一个临时表；最后利用临时表发现组和应用聚集函数。在一些情况下，MySQL可以利用索引避免创建临时表。

GROUP BY语句使用索引的前提条件是GROUP BY的所有列都要来自相同的索引，而且索引集合应该按顺序保存索引键。临时表是否能够被索引替代还依赖于在查询中索引集合中哪个部分被使用，对索引列使用哪些条件以及聚集函数。

有两种方式通过索引执行GROUP BY语句。第一个方法是，分组操作被应用在所有的范围预测。第二个方法是，首先执行范围扫描，然后分组结果元组集。

在MySQL中，GROUP BY被用于排序，所以服务器也能将ORDER BY优化应用于分组。

Loose Index Scan

处理GROUP BY的最有效率的方式是，索引被用于直接检索分组列。用这个访问方法，MySQL可应用索引的最左前缀属性。这个属性使得where条件不用全部包含索引集合的全部索引键，这个属性使得访问方法可以只考虑索引集合的部分键，所以叫做loose index scan，最重要的一个前提条件是：查询必须在一个单独表上进行。

还有其他的一些条件是：

索引列要满足最左前缀；
只有MIN()和MAX()可以使用；
loose index scan不能应用前缀索引；

如果loose index scan被应用，EXPLAIN会在Extra列中输出Using index for group-by。

假设在表t1(c1, c2, c3, c4)上建立索引idx(c1, c2, c3)。loose index scan可以用于下面这些查询：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

下面这些查询不能应用loose index scan：

应用了除MIN()/MAX()之外的聚合函数：
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

不符合最左前缀索引：
SELECT c1, c2 FROM t1 GROUP BY c2, c3;

假设在表t1(c1, c2, c3, c4)上建立索引idx(c1, c2, c3)。loose index scan可以用于下面这些查询：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

下面这些查询不能应用loose index scan：

SELECT DISTINCT COUNT(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;

Tight Index Scan

假设在表t1(c1, c2, c3, c4)上建立索引idx(c1, c2, c3)，下面这些查询loose index scan不会起作用，但是tiht index scan访问方法可以起作用。

在GROUP BY中存在一个空白，但是可以用c2等于一个常量来填补：
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

GROUP BY语句中没有由索引集合第一部分开始，但是存在其它部分的常量：
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

前缀索引

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。