技术总监夸我“索引”用的溜，我飘了......( 五 ) _索引

SELECT field_list FROM t WHERE id_card = reverse('input_id_card_string'); 这样就可以用身份证的后六位作前缀索引了，是不是很巧妙?
实际上上文所述的索引选择性同样适用于联合索引的设计，如果没有特殊情况，我们一般建议在建立联合索引时，把选择性最高的列放在最前面。
比如，对于以下语句：
SELECT * FROM payment WHERE staff_id = xxx AND customer_id = xxx; 单就这个语句而言， (staff_id ， customer_id) 和 (customer_id, staff_id) 这两个联合索引我们应该建哪一个呢，可以统计下这两者的选择性。
SELECTCOUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,COUNT(*) FROM payment 结果为：
staff_id_selectivity: 0.0001 customer_id_selectivity: 0.0373 COUNT(*): 16049 从中可以看出 customer_id 的选择性更高，所以应该选择 customer_id 作为第一列。
索引设计准则：三星索引
上文我们得出了一个索引列顺序的经验法则：将选择性最高的列放在索引的最前列，这种建立在某些场景可能有用，但通常不如避免随机 IO 和排序那么重要，这里引入索引设计中非常著名的一个准则：三星索引。
如果一个查询满足三星索引中三颗星的所有索引条件，理论上可以认为我们设计的索引是最好的索引。
什么是三星索引?

第一颗星：WHERE 后面参与查询的列可以组成了单列索引或联合索引。
第二颗星：避免排序，即如果 SQL 语句中出现 order by colulmn ，那么取出的结果集就已经是按照 column 排序好的，不需要再生成临时表。
第三颗星：SELECT 对应的列应该尽量是索引列，即尽量避免回表查询。

所以对于如下语句：
SELECT age, name, city where age = xxx and name = xxx order by age 设计的索引应该是 (age ， name ， city) 或者 (name ， age ， city) 。
当然了，三星索引是一个比较理想化的标准，实际操作往往只能满足期望中的一颗或两颗星，考虑如下语句：
SELECT age, name, city where age >= 10 AND age <= 20 and city = xxx order by name desc 假设我们分别为这三列建了联合索引，则显然它符合第三颗星(使用了覆盖索引) 。
如果索引是(city ， age ， name) ，则虽然满足了第一颗星，但排序无法用到索引，不满足第二颗星，如果索引是 (city ， name ， age) ，则第二颗星满足了，但此时 age 在 WHERE 中的搜索条件又无法满足第一星。
另外第三颗星(尽量使用覆盖索引)也无法完全满足，试想我要 SELECT 多列，要把这多列都设置为联合索引吗，这对索引的维护是个问题，因为每一次表的 CURD 都伴随着索引的更新，很可能频繁伴随着页分裂与页合并。
【技术总监夸我“索引”用的溜，我飘了......】综上所述，三星索引只是给我们构建索引提供了一个参考，索引设计应该尽量靠近三星索引的标准。
但实际场景我们一般无法同时满足三星索引，一般我们会优先选择满足第三颗星(因为回表代价较大)至于第一，二颗星就要依赖于实际的成本及实际的业务场景考虑。
总结
本文简述了索引的基本原理，索引的几种类型，以及分析了一下设计索引尽量应该遵循的一些准则，相信我们对索引的理解又更深了一步。
作者：码海
编辑：陶家龙
出处：转载自微信公众号码海(ID：seaofcode)