MySQL索引（二）：建索引的原则

MySQL索引（二）：建索引的原则

在了解了索引的基础知识及B+树索引的原理后（如需复习请点这里），这一节我们了解一下有哪些建索引的原则，来指导我们去建索引。

建索引的原则

1. 联合索引

我们可能听一些数据库方面的专业人士说过：“把 Where 条件里面的列都建上索引”，从而给每个列给每个列建独立的索引，这个理解是非常错误的。

如果 Where 条件里有多种组合的查询条件，可以尝试建联合索引来减少索引数量，同时提升查询性能。

2. 覆盖索引

普通索引查到主键后，回到主键索引搜索的过程，称为回表。

当使用普通索引查询时，普通索引有我们所需结果的所有信息（字段），就可以直接提供查询结果，而不需要回表。也就是说，在这个查询里，索引已经被“覆盖了”查询需求，称为覆盖索引。

mysql> create table T (ID int primary key,k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '',index k(k))engine=InnoDB;select ID from T where k between 3 and 5

上面的例子中，k索引树上已经包括了ID的值，就不需要回表了。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

在使用覆盖索引时，要注意如果需要返回的字段较多，就要权衡空间和时间。因为覆盖索引要覆盖较多字段就需要更多的空间。

3. 最左前缀原则

假设我们有2个字段，例如身份证号和地址，现在有三种查询需求：按身份证号查询、按地址查询，以及按身份证号和地址查询。

如果每种需要都要创建一个索引，就显得有利浪费。

从上一篇文章里，我们了解到B+树的特点是有序的，因此我们可以只创建两个索引即满足上面的三种需求，分别是（身份证号，地址）和（地址）。这就是B+树索引的“最左前缀”原则。

在建立联合索引的时候，如何安排索引内的字段顺序：

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。第二原则是，空间。当两个字段存在一个明显比另一个大时，例如name和age，明显name要比age要大，此时应该建一个（name，age）的联合索引和一个（age）的单字段索引。

4. 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一种策略是模拟哈希索引。

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（T）的比值，范围从1/T到1之间。

如何确定前缀索引的长度及创建前缀索引

假设有个订单表，其中有包括城市名称的字段（city，且城市名称为英文），下面来介绍一下如何确定前缀索引的长度。

mysql> create table orders (ID int primary key,city varchar(16) NOT NULL)engine=InnoDB;

一种方法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

计算完整列的选择性：

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM orders;

在同一个查询中计算不同前缀长度的选择性：

mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3, COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4, COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5, COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6, COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7 FROM orders;

创建前缀索引的方法：

mysql> ALTER TABLE orders ADD KEY (city(7));

5. 选择合适的索引顺序

在联合索引中，索引列的顺序是按照从左到右逐列进行排序的。因此索引可以按照升序或降序进行扫描，以满足精确符合列顺序的 ORDERY BY、GROUP BY 和 DISTINCT 等子句的查询需求。

因此联合索引的列顺序很重要。

如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。

这个经验法则在某些场景下有用，但可能在另外场景下，可能就没效了，这要根据具体情况进行分析。

参考资料

高性能MySQL(第3版) : 第3版MySQL技术内幕 : InnoDB存储引擎(第2版)05 | 深入浅出索引（下）