索引提供了快速的查询方案，同时增加了删除、修改的开销；续上一篇博客《MySQL索引基础》，根据索引的优缺点，本文列举一些高效的MySQL策略。
- 不参与计算
select id from table where id+1<10
select * from table where to_days(current_data)-to_days(date) <=10
类似上述查询，将索引列写进数学表达式，或者作为函数的一部分，都会引起索引失效。
- 前缀索引
上篇博客提到了模拟哈希索引在长值字段的应用。类似情况，还可以使用前缀索引，即只对字段开始的前n个字符建立索引。前缀索引是牺牲索引选择性，换取建立索引效率的方案。索引选择性是衡量索引对字段区分能力的指标，即 count(distinct(column))/count(*)；选择性越大，对数据的区分度越好，则查询效率越高。例如，对美国城市名做前缀索引，取三位，则San字段会有很多重复名（旧金山、圣塔芭芭拉），整体选择性会下降。除了索引的选择性，还要考虑前缀的分布情况，尽量均匀，或者在高频查询的数据行有良好的区分性。
MySQL无法基于前缀索引做ORDER BY和GROUP BY操作。逆序字符串的前缀索引就变成了后缀索引，在电子邮箱查询等特定场景有不错的效果。
- 多列索引
多条件查询很常见，此时就需要建立多列索引。尤其是OR操作，耗费大量资源用于缓存、排序、合并，这些都不会被优化器计算进查询成本，影响并发性。可以用 EXPLAIN ${SQL}查看，如果有索引合并，也说明这种场景需要多列索引。
多列索引需要合理的顺序，以满足：1. 尽可能快地查讯 2.满足ORDER BY和GROUP BY操作 3.尽量满足覆盖索引。
简单地，可以讲选择性最高的列放在最左。
mysql> SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G*************************** 1. row ***************************SUM(staff_id = 2): 7992SUM(customer_id = 584): 30mysql> SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584\G*************************** 1. row ***************************SUM(staff_id = 2): 17mysql> SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,> COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,> COUNT(*)> FROM payment\G*************************** 1. row ***************************staff_id_selectivity: 0.0001customer_id_selectivity: 0.0373COUNT(*): 16049
第一个查询表明，customer_id为584对应的行更少。第二个查询说明在customer_id=584时，staff_id具有良好的选择性。第三个查询显示，整体上，customer_id具有更好的选择性。所以这个例子符合选择性最高的列放在最左的原则。需要注意，查询1和2的结果，依赖于选定的具体值，可能会对其他值产生偏见。
mysql> EXPLAIN SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE-> FROM Message-> WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)-> ORDER BY priority DESC, modifiedDate DESCid: 1select_type: SIMPLEtable: Messagetype: refkey: ix_groupId_userIdkey_len: 18ref: const,constrows: 1251162Extra: Using wheremysql> SELECT COUNT(*), SUM(groupId = 10137),-> SUM(userId = 1288826), SUM(anonymous = 0)-> FROM Message\G*************************** 1. row ***************************count(*): 4142217sum(groupId = 10137): 4092654sum(userId = 1288826): 1288496sum(anonymous = 0): 4141934
第一个查询显示，该表有一个(groupId, userId)的联合索引。但第二个查询表明，这两列上的索引选择性都很低，基本失效。这类问题不能在数据库层面解决，需要在业务上特殊处理这些用户和组，比如禁止相关id查询这条SQL。这类问题比较普遍，比如对所有访客固定一个ID，或者拥有大量好友、评论等的用户。
还有一个重要的原则，考察该表上执行各类条件查询的频率，其相关字段是建立联合索引的重要依据。
- 聚簇索引
聚簇索引是一种存储方式，InnoDB中，在同一个结构中保存了索引和数据行。即非叶节点保存索引，叶子节点保存数据，与B+树的定义一致。InnoDB通过主键聚集数据，也就是对主键列聚簇索引。聚簇索引的优势如下：
- 相关数据连续存储（减少磁盘IO）。例如，邮箱数据，以用户id为主键，可以用较少的IO取出与某用户所有往来邮件。
- 访问速度更快。数据与索引在同一个树中。
- 可以直接使用叶子页中的主键值。
聚簇索引的缺陷如下：
- 如果所有数据都放进内存，那么访问顺序的影响就会变小。
- 插入顺序影响插入速度；以主键顺序的插入的速度最快。
- 更新代价很高。会把对应行移动到新位置，其他行的位置跟着改变。
- 插入或者更新，某列插入已满的页，则需要分裂页，占用更大的空间。
- 导致全表扫描变慢。
- 二级索引（即非聚簇索引）更大，因为在叶子节点包含了引用行的主键列。所以二级索引的工作顺序是：在二级索引中查找主键->在聚簇索引中查找行，所以做了两次B+树搜索（顾名思义？）。
简单说，聚簇索引只能索引一列，通常是主键；所有数据是按照聚簇索引列的顺序连续排列的。
以下表为例，对比一下InnoDB聚簇索引和MyISAM中数据存储的方式。
CREATE TABLE layout_test (col1 int NOT NULL,col2 int NOT NULL,PRIMARY KEY(col1),KEY(col2));
假设col1的值在1-10000，在磁盘上行的顺序随机；col2取值在1-100，有很多重复值。
MyISAM按照插入顺序存储。
这种方式可以直接根据行号查找到数据，索引中只需保存行号。主键索引和col2索引如下图：
MyISAM的主键索引与其他列的索引并无差异；不过是满足了唯一、为空的索引。
InnoDB支持聚簇索引，其索引和数据分布情况如下图。
一个显著的不同是 ，聚簇索引存储了整张表；即使主键是列前缀也如此。
回滚指针用于事务和MVCC。
InnoDB的二级索引与主键索引不同。
用主键值代替“行指针”的优势是，非主键列的插入、更新不会引起大量移动、也分裂；缺陷是占用了更大的存储空间。这两者在数据存储时的对比，可抽象为下图。
基于插入性能的考虑，InnoDB表的主键可以定义为自增列。一方面保证了顺序写入，另一方面在主键关联时性能也更好。同时，还能节约索引空间。
其他几类索引策略将在下一篇博客中介绍。