创建高性能索引策略

前面介绍了MySQL的常用索引的原理MySQL索引原理总结 - 掘金。接下来我们将根据索引原理结合实际，使用索引针对数据和特定行为的优化。

在分析索引策略对数据查询性能提升前，先熟悉一下explain关键字，它能够帮助我们分析索引的使用情况。

Explain

explain命令是MySQL的查询语句分析命令，在其它数据库中也有这个关键字。

用法：explain select .....。

如：explain select * from tb_teacher where t_phone = '14539776850';

分析语句中有多少个select就会有多少条分析结果记录。

结果字段说明：

id：select的编号。

select_type：当前select查询类型，是简单查询还是复杂查询。主要有以下几种：

simple：简单查询

primary：复杂查询中最外层的select

subquery：包含在select中的子查询（不在from子句中）

derived：包含在from子句中的子查询

union：在union中的第二个和随后的select

union result：从union临时表检索结果的select

table：当前explain的目标表。

type：这一列表示关联类型或访问类型，即mysql决定如何查找表中的行

性能优先级依次从最优到最差分别为：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all 1.null:mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。 2.const、system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings的结果）。 3.eq_ref：primary key 或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。 4.ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到符合条件的行。 5.ref_or_null：类似ref，但是可以搜索值为null的行。 6.index_merge：表示使用了索引合并的优化方法。 7.range：范围扫描通常出现在in(),between,>,<,>=等操作中。 8.index：和all一样，不同就是mysql只需扫描索引树，这通常比all快一些。 9.all：全表扫描，意味着mysql需要从头到尾去查找所需要的行，通常情况下这需要增加索引来进行优化了。

possible_keys：这一列显示查询可能使用哪些索引来查找。

key：这一列显示mysql实际采用哪个索引来优化对该表的访问。

key_len：这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的那些列。

ref：mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

rows：估算要读取的行数。

extra：展示的是额外信息

1.distinct：一但mysql找到了与行相联合和匹配的行，就不再搜索了。 2.using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。（是性能高的表现） 3.using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按where条件进行检查，符合就留下，不符合就丢弃。 4.using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。 5.using filesort：mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

以上内容来自：MYSQL EXPLAIN字段含义 - 哈哈哈嗝 - 博客园

独立的列

独立的列指的是索引列不是表达式的一部分，不是函数的参数。

如我们要查询表中id=5的记录，我们写出如下sql：ps:t_id为主键

-- 写法一使用独立列
mysql> select * from tb_teacher where t_id  = 5;
+------+--------+------------+-------+-------------+----------+
| t_id | t_name | t_birth    | t_sex | t_phone     | t_height |
+------+--------+------------+-------+-------------+----------+
|    5 | 经全会 | 1980-10-29 | male  | 15538129793 |      176 |
+------+--------+------------+-------+-------------+----------+
1 row in set (0.00 sec)  

-- 使用表达式
mysql> select * from tb_teacher where t_id+1  = 6;
+------+--------+------------+-------+-------------+----------+
| t_id | t_name | t_birth    | t_sex | t_phone     | t_height |
+------+--------+------------+-------+-------------+----------+
|    5 | 经全会 | 1980-10-29 | male  | 15538129793 |      176 |
+------+--------+------------+-------+-------------+----------+
1 row in set (1.90 sec)

我们可以看到这两种写法的结果都是一致的，但是观察其执行时间；第一种耗时为0秒是毫秒级别的查询，第二种执行时间为1.9秒。

接下来分析其索引命中情况：

-- 使用独立列
mysql> explain select * from tb_teacher where t_id  = 5;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_teacher | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)  

-- 使用表达式
mysql> explain select * from tb_teacher where t_id+1  = 6;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | tb_teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6041275 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到使用独立列，它们都是简单查询，但是在type中显示第二种使用表达式查询类型是ALL意味着全表扫描，第一种为const常量级；并且在其key这一列中也看到使用了主索引，rows显示了其筛选范围。

结论：当索引列作为了表达式的一部分的时候，就会导致索引失效。

那么如果索引结果为表达式或者函数是否会导致索引失效呢？答案是不会的。

如：select * from tb_teacher where t_id = 4+1;这条语句索引是生效的。

原理分析：

前面分析中知道索引本质就是将索引列的所有值按照特定数据结构存储进行存储，方便后续的查找，如B+Tree。也就是说我们在进行索引查找的时候要明确下来索引的具体值，根据这个具体值再去索引树种搜索，如果使用表达式或者函数，性质都一样，索引就变成了一个变量f(idx)=y。此时在索引树上就无法精确检索，导致索引失效。

比如上面的例子，我们一眼就可以看出 t_id + 1 =6那么t_id的值就为5，但是数据库不知道呀，而且这只是简单的表达式，要是我们整个二次函数的表达式啥的，还能一眼看出吗，数据库引擎不可能针对这个函数或者表达式还去化简成idx = 6-1的形式。因此如果对索引列进行计算是无法应用到索引特性的。那么为什么select * from tb_teacher where t_id = 4+1;这条语句索引能生效的原因也就呼之欲出了，因为4+1是能够在进行索引查找前进行计算得出来的具体常量，也就符合了索引值必须是个常量的特性，索引也就生效了。

如果确实需要在检索的时候对索引进行表达式计算，一般处理方案有两种，一是在应用程序中按照表达式逻辑，先计算得出常量值，再进行查询；二是增加冗余列，在索引列的基础上新增一列，按照查询表达式冗余出表达式计算结果值，但是这种方式需要有一套合理的更新冗余数据逻辑，通常触发器可以很好的做到这点。

字符串检索

在日常查询业务使用中，字符串应该是使用得最多，但是字符串查询优化也是变化最多的，因此在对字符串索引优化方面要从多方面考虑。

右模糊匹配

对字符串索引查询最常见的使用就是右模糊匹配，右模糊匹配指的是使用 like 关键字查询的时候，左边为常量，右边为模糊匹配。如：``like '张%'`。

接下来我们在一张，对name字符串建立了索引的，六百多万的表中来查询姓张的信息。

mysql> select * from tb_teacher where  t_name like '张%';
+----------+
+ ........ +
+----------+
31895 rows in set (2.40 sec)

mysql> select * from tb_teacher where  t_name like '%张%';
+----------+
+ ........ +
+----------+
1 row in set (5.25 sec)

可以看到使用了右模糊的语句，查询时间为2.40 sec，而使用全模糊的查询结果为5.25 sec，它们之间性能相差很大。继续看它们explain。

ps：我刚开始用了台性能很好的电脑测试，发现它们时间都差不多，甚至有时候，使用全模糊还要更快，后面换了台性能较差的电脑，结果就很明显了，其实应该把数据量再造大一点，比如两千万以上就明显了。所以说，在数据量不大或者性能很好的时候，索引提速也不是很明显。

mysql> explain select * from tb_teacher where t_name like '张%';
+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | tb_teacher | NULL       | range | idx_name      | idx_name | 82      | NULL | 51918 |   100.00 | Using index condition; Using MRR |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)1 sec)  


mysql> explain select * from tb_teacher where t_name like '%张%';
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | tb_teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6041275 |    11.11 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这里可以看到，使用了左模糊的语句，使用了索引，从Extra中的Using index condition;可以证明，索然查询类型是rang但是也比全表全表扫描的rows少很多，能够有效的提高速度。

原理分析：

在上面独立的列里面有说道，索引查找就是拿一个常量值，然后去索引树中搜索结果的一个过程。在这里我们的需求是模糊查询，可能有的人认为就不能作为常量值，但是在这里我们的左边的值是一个常量，因此可以进行范围查询的，它可以等于 value >= 张开头的值 and value<= 第一个不以张开头的字符串

我们知道索引的底层是一颗B+Tree，底层肯定会进行编码，转为有序的编码存储，为了方便查看，得出以下索引树：

当我们要查询以张开头的，首先从根节点，找到page3，然后从page3里面找到具体的数据即可：value >= 张开头的值 and value<= 第一个不以张开头的字符串。

那么我们继续思考，为什么只有常量开头才能检索，'%张' '%张%'为什么不行？

ps：我们再次强调，要在索引树上检索要以常量作为条件才能进行检索。

%张：在上面又说到，我们通过常量才能到索引树中找，而我们这里只知道以张结尾，但是数据字符串长度不固定，如果我们要去以张结尾的字符串，就必须先获取到数据进行计算比对得出，需要进行计算了转换为表达式函数就为：%张=right(name, 1)。这样就违反了独立列的要求。%张%同理%张%=locate('张',name)也变成了表达式。

总结就是：要使得索引生效要先拿条件去找数据，而不是找到数据来判断是否符合条件。

这个就相当于你去拿快递，有明确的取件号，快递站的快递按照取件号一次排列好，就可以很快的找到，而你不知道取件号，你说我只知道它是一个电话号码，那么快递站就得挨着挨着去找包裹，找到一个比对一次，然后比对成功再给你，这样肯定性能低下了。而'张%'这种检索条件，至少可以过滤掉一定范围内的数据，比如在快递站，你说我记得是以2-2开头的快递号，但是具体的我记不住了，那么快递员就可以很快的直接找2号货架2层的快递给你，缩小了很大范围，提高了性能。

注意：range类型的查询不一定就一定比全表扫描快，比如上面快递站，它拿到第一个就是你要包裹，直接就给你了，不用再到货架上找。实际中查找一次IO就解决了问题，但是如果走索引，得经历两次索引IO，回表再获取到数据，反而降低了性能。

冗余子串

上面使用右模糊，可以在不改动原本字符串的情况下优化查询效率，但是有很大局限性，只能进行右模糊查询，通常在前缀搜索的时候表现的不错，那么如果我们想判断结尾，获取子串，应该怎么办呢。

答案就是冗余子串，空间换时间。

当我们对字符串检索有特定要求，如结尾查询，规定子串判断等就可以通过在原来的字段基础上上建立冗余字段，并将原来的字段的记录进行处理，然后对冗余字段建立索引。

如：我们要找到以“abc”结尾的字符串，那么我们只需要截取原来字符串中的最后三位赋值给新字段即可，并建立索引。我们以前有针对某站用户填写的url中包含了用户在该站中的账号，要获取用户该站中的账号，则可以对url进行切分，账号单独建立一列，并创建索引。

这些都是很好的实践，并且往往这种子串的模式，它都要比原来的字符串短，创建索引占用空间更小，查询更快，但是因为冗余了记录，所占用的总空间也变大了，还有个最最要命的问题就是子串的更新，通常我们会以触发器的形式来实现该功能。

Hash

如果我们要对一个很长的字符串，进行等值查询，比如软件授权码，通常为一个128或者256的随机字符串，该授权码要先在系统注册生成后，然后给用户，用户填写进行验证，这时候如果我们对授权码进行创建唯一索引，毫无疑问是个不错的选择，首先它具备唯一性，而且通常在这种场景下都是等值查询，可以直接命中是const类型的查询。但是针对这种很长的字符串进行创建索引，难免索引也会占用很大的空间，使得索引能够存储的更少因为一页就16KB，能加载的数据有限，索引长度长了，一次性能加载的数据就更少了，性能就有一定消耗。

那么正确的做法是什么呢，就是对这个很长的字符串做hash运算，将运算结果单独存储在一列中，然后用hash值+实际值比对的方式，从而提高检索效率，具体可以参考MySQL索引原理总结 - 掘金](MySQL索引原理总结 - 掘金)中的模拟hash索引介绍实现。

联合索引

联合索引指的是对多个字段建立索引，组成联合索引通常应用于多条件查询，排序等场景。

索引合并

在正式介绍联合索引的使用之前，先来看一个由多个单列索引组成的合并索引。

表定义：

CREATE TABLE `tb_teacher`  (
  `t_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `t_birth` date NOT NULL COMMENT '出生年月',
  `t_sex` enum('male','female') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  `t_phone` bigint(20) NOT NULL COMMENT '手机号',
  `t_height` int(11) NOT NULL COMMENT '身高',
  PRIMARY KEY (`t_id`) USING BTREE,
  UNIQUE INDEX `uk_phone`(`t_phone`) USING BTREE,
  INDEX `idx_name`(`t_name`) USING BTREE,
  INDEX `idx_birth`(`t_birth`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11000000 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表' ROW_FORMAT = Dynamic;

上边表定义中分别对tb_teacher的t_name和t_phone建立了独立的BTree索引，在我们对其两个索引进行合并查询。

执行以下SQL：

select * from tb_teacher where t_name = '董秋晖' or t_phone = '17372379378';

按照索引原理，这个应该会执行全表扫描，因为它们不存在于同一颗索引树上，无法完成一次搜索就得到值，比如符合了name条件后，还得继续扫描t_phone索引树，这样对于这两个独立列的索引就起不到任何作用。但是MySQL在5.0以后引入了索引合并，这种模式会对其单列索引进行扫描，并将结果合并。这种算捷有三个变种： OR 条件的联合（ union) ，AND 条件的相交（ intersection)，组合前两种情况的联合及相交。

接下来看explain分析结果：

mysql> explain select * from tb_teacher where t_name = '董秋晖' or t_phone = '17372379378';
+----+-------------+------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | tb_teacher | NULL       | index_merge | uk_phone,idx_name | idx_name,uk_phone | 82,8    | NULL |   12 |   100.00 | Using union(idx_name,uk_phone); Using where |
+----+-------------+------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到最后该查询类型为index_merge，其中Extra中后对其索引命中相关信息说明，使用了union合并索引。

类似于，以下语句使用常量值去比较并合并，但是这种写法比上面效率更高，是一个ref类型的查询，因为明确指定了索引值，少了让MySQL引擎去判断合并操作：

select * from tb_teacher where t_name = '董秋晖' ;
union 
select * from tb_teacher where t_name <> '董秋晖' and  t_phone = '17372379378';

使用联合索引

联合索引是一个索引包含多个字段的策略如：

ALTER TABLE `test_2`.`tb_teacher` 
ADD INDEX `idx_name_phone`(`t_name`, `t_phone`) USING BTREE;

使用联合索引的好处有：

减少索引开销

可能有的人觉得，创建联合索引为什么会减少开销，它因为有多个字段，对磁盘开销应该更大才对。事实也是如此，一个联合索引往往比单列索引的磁盘开销和内存开销更大，但是如果联合索引的联合列是常用的列的话，那么开销就小得多了。比如现在有(a,b,c)三列的联合索引，实际它创建了(a)、(a,b)、(a,b,c)三个索引，注意这里有顺序要求，比如从左到右，这是因为最左匹配原则，原理可以参考上面的字符串检索。

如：tb_teacher中只存在联合索引(t_name,t_phone)，那么我们执行sql，select * from tb_teacher where t_phone='111'，会导致联合索引失效，但是如果where后面使用：where t_phone='111' and name = '张三' 或者 where name = '张三' and t_phone='111'都是可以使用到联合索引的，有的人可能好奇为什么第一个where顺序都颠倒了还能使用到索引，这是因为在MySQL引擎中会对这种调整顺序后不改变结果的查询进行优化，让其优化成第二个where一样，使得能够很好的使用联合索引。

覆盖索引

联合索引最适合的运用场景就是覆盖索引。覆盖索引指的是，查询所选择的列全部命中了联合索引，在这种情况下，通过索引检索就能查询出结果，从而减少了在聚簇索引中回表操作，极大的减少了随机IO操作。

索引的扫描是非常快的，因为它是有序的，但是如果索引不能覆盖查询所需的所有列，就得扫描一条索引记录进行回表查询一次数据，这种操作都是随机IO。

索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL 就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于1/0 密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中

索引排序

MySQL主要有两种方式生成有序的结果，也就是说有两种方式进行排序。一种是常规的排序操作，还有一种就是按索引顺序扫描。如果EXPLAIN出来的type值为index，则说明了MySQL使用了索引顺序排序。

如现在有索引：

UNIQUE KEY rental_date (rental_date,inventory_id,customer_id)

使用查询：

where rental_date= '2005-05-25'  ORDER BY inventory_id, customer_id

在这种情况下即使ORDER BY 子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。从where开始计算，它们符合联合索引要求。

同理使用最左匹配原则，以下sql同样可以使用索引排序

WHERE rental_date ;’2005-05-25 ’ ORDER BY inventory_id DESC;

以下是不能使用索引排序的例子：

WHERE rental_date =’2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;

从索引原理来看，索引是按照顺序存储的，联合索引同样符合这个要求，而在这里指定了两个不同的排序方向打破了索引存储结构，导致索引失效。如联合索引存储：(1,a,张),(2,a,长),(2,b,张)。查询的时候，第一列等于2，后面第2列和第三列培训就是自然排了，如果颠倒了顺序就破坏了索引的存储规则，导致得单独对列进行排序。

WHERE rental_date =’ 2005-05-25' ORDER BY inventory_id, staff_id;

这条语句按照索引查询条件来看，符合最左匹配原则，如果放在where里面毫无疑问这三个字段是可以命中索引的，但是错就放在了order by里面，这里的道理和上面的是一样的，联合索引要看做一个整体，它可以缺少右边列，但是不能掺杂多余的列，否则就得为了这多余的列排序而耗费更多的性能。

索引策略总结

索引列并不是越多越好，应该在满足业务情况下尽量的少，因为当索引数据更新的时候除了需要更新记录本身外还得额外维护索引存储。

索引列应该有序的类型，并且长度越短越好，如自增的id，因为在InnoDB中，叶子节点索引需要有序排列，使用有序的数据类型可以使得索引更加紧凑，同样在插入或者更新的时候，能够减少索引定位的性能消耗。索引的长度过长会导致每次加载内存中的索引数据变少，增加IO次数。

对于长字符串的字段，进行检索的时候可以采用子串冗余，hash等方式，避免对长字符串创建索引，会造成大量的磁盘空间被占用，并且长字符串也会使得索引加载数量减少。

对于常用的查询显示字段，可以采用联合索引，能够有效的避免回表，还有利于排序。

适合创建索引的列：不经常更新的列，唯一性较好的列，经常在where中使用的列。

对索引列不能使用表达式函数运算。

少用or，可以通过union来优化or条件。

in和not in，!=，<>这种不确定范围的条件也要注意，它们会导致全表扫描，索引失效。in和not in可以使用exists优化，如果是连续的值可以通过between，使用>=进行优化。

总的来说，就是在保证功能的前提下减少对数据库实际数据的访问，利用索引数据结构的优势进行检索，从而提高查询效率。