mysql 查找相似数据_MySQL索引原理与高性能索引策略

1.背景

索引的目标在于提高查询效率。我们经常通过字典来理解索引的原理，比如，现在我们需要查找“great”这个单词，如果使用英汉词典，我们首先需要先找到开头字母g，然后在g中继续查找r，继续缩小范围查找剩余的e-a-t。通过这种不断缩小范围的方式，最后查找到我们需要的单词。词典本来就是有序的，假设词典没有索引，也没有顺序，那么为了查找一个单词，我们就不得不从第一页开始查找，一页一页翻直到找到我们想要的单词，这种顺序查找效率实在是太低了。有了索引情况就大不一样，索引不仅完成了数据的分组，且有序，简单的命中命中查找可以在常量级、对数级搞定。

1.1.索引的类型

在MySQL中，索引在存储引擎层而不是服务层实现，因此，索引并没有统一的标准。我们这里探讨索引类型，限定的存储引擎是InnoDB。常见的索引有B-Tree索引、hash索引。

B-Tree索引

B-Tree索引意味着所有的值都是按顺序存储，至于B-Tree索引为什么高效，请间后文索引的数据结构部分。我们只需要直到B-Tree索引类型，以及对于B-Tree索引，索引列的顺序是多么重要。

哈希索引

哈希索引时基于哈希表实现，用于精确匹配所有列的查询才有效。正因为如此，哈希索引不支持排序

不支持部分索引列匹配。哈希索引支持等值比较查询，包括=、in、<=>，不支持范围查找。

2.索引原理

2.1.索引基本原理

不论是词典还是数据库，索引的原理都是一样的：通过不断缩小数据的范围来定位想要的结果集。索引把随机的事件变成了顺序事件。

了解索引的基本原理还不够，在真实数据库实现中可能会更复杂：

1. 数据库需要支持更加灵活、更加复杂的查询。
2. 数据库并非所有数据常驻内存，需要考虑磁盘IO和预热。

具体来说，数据库需要支持多种复杂的查询来满足各种查询需求，除了支持等值查询，还需要支持范围查询（> < between, in）、模糊查询(like)、并集查询(or)。按照字典索引的想法，我们可以对数据分区，比如总共1000条数据，查找第250个数据，我们只需要在第三个段201~300这个区间查找就好，有算法基础的同学肯定能想到二叉搜索树（Binary Search Tree），通过搜索树可以将查询的时间复杂度限制在lgN。事实上数据库的索引原理确实基于搜索树这种数据结构。

如果是内存数据库，就没有第二点啥事了。但在关系型数据库中，数据保存在磁盘上，为了提高性能，才将部分数据读入内存，所以数据库索引又不能不考虑磁盘IO和数据预热。

2.2.数据预读

为了更直观的感受耗时，我们从小到大排列一下不同操作的耗时，如下表所示。

学过计算机组成原理的同学都知道，磁盘读取数据靠的是机械运动，每次读取数据花费的时间包含：寻道时间、旋转延迟时间、传输时间3部分。主流磁盘寻道时间一般在5ms以内；对于一个7200转（每分钟7200转）的磁盘，旋转延迟大概4.17ms（60000ms / 7200 / 2）；而传输延迟则相对寻道时间和旋转时间小很多，一般在零点几毫秒。所以经过刚才的分析，一次磁盘IO大概需要9ms左右。但同时，对于一台500MIPS指令处理速度的机器来说，每秒可以执行5亿条指令，也就是说一次磁盘IO的时间，处理器可以执行40万条指令。依据程序局部性原理，提前对数据预读很有必要。

2.3.索引的数据结构

有了前两节的铺垫，我们直到索引是通过不断缩小范围来锁定目标的，而对数据库来说数据是存储在磁盘上的，为了减少磁盘IO，数据库有必要对正在用到或即将用到的数据进行预读，最关键的为了加快搜索，完成对数据的分区查找，我们会用到搜索树这种数据结构，而mysql的索引结构是一颗b+树。

B+树是一颗高度可控的平衡的多路搜索树，其高效查找的想法也很好理解：每次查找数据时把磁盘IO控制在一个非常小的数量级，最好是常熟数量级。这样就能满足我们高效查找的需求（以后有机会分析分析高效查找算法）。b+树如下图所示。

浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

了解了B+树的结构，我们来看下B+树的查找过程。

如图所示，如果要查找数据项29，那么：

1. 首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计。
2. 通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO。
3. 29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实情况是3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。这也充分说明了B+树查找的高效。

2.4.索引的最左匹配特性

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

4.高性能索引策略

正确地创建和使用索引是实现高性能查询的基础。

4.1.请保持列的干净

不要在列中使用表达式或函数，如果这样是无法命中索引的。

错误用法

select 

4.2.为什么索引字段要尽量小

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

4.3.前缀索引与列的区分度

使用前缀索引的场景一般是对字符较长的列建有索引，这会让索引变得大且长，原因就是导致了树的高度变大。为了解决这个问题可以考虑建立前缀索引来缩短索引的大小。这样可以大大节约索引空间，从而提高索引效率。

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

关于前缀索引长度设置多少合适，可通过统计来确认合适的前缀大小。

以mysql官网提供的sakila db中city为例（下载地址：https://downloads.mysql.com/docs/sakila-db.zip），随机构造一个19200大小的测试表city_demo。构造方法如下。

create 

• 了解一下该表的数据分布情况。

select 

• 计算完整列的选择性

select 

• 计算截取city字段前缀4~7的列选择性

select 

可以看出，当前缀长度为7，再增加前缀长度，选择性提升的幅度已经很小了。

基于以上分析，我们可以在city_demo表上创建一个长度为7的前缀索引。

alter 

4.4.建索引的几大原则

前面几节我们讨论了索引的最左匹配特性、列的区分度、索引字段为什么要尽可能小些。一般，在建索引时需要遵循以下原则：

1. 最左前缀匹配原则：mysql会一直向右匹配直到遇到范围查找就停滞匹配。比如：a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。索引的建立需要考虑常用查询的字段顺序。
2. 选择区分度高的列作为索引。
3. 索引列不参与计算。比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
4. 尽可能去扩展索引，而不是新建索引：当需要通过增加索引来提高性能时，考虑结合现有索引判断是否可以在已有索引上做扩展。

5.实践：慢查询优化

5.1.慢查询优化的基本步骤

1. 首先直观感受一下是不是真的很慢，注意设置SQL_NO_CACHE。
2. 如果是真慢，则执行explain语句查看执行计划、查看现有索引。explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快。所以优化语句基本上都是在优化rows。
3. 参照建索引的几个原则，比如最左匹配原则、扩展原则，参考现有索引做索引优化，比如扩展已有索引建联合索引、建前缀索引等等。
4. order by limit 形式的sql语句让排序的表优先查.
5. 观察结果，不符合预期继续分析。

5.2.慢查询优化案例

案例一：

系统使用者反应有一个功能越来越慢，于是工程师找到了对应的SQL，如下。

select
   

然后兴奋的告诉DBA：”给我把每个字段都加上索引”。

DBA很惊讶问道：“为什么需要每个字段都加上索引？”

“把查询的字段都加上索引会更快”，工程师信心满满地说。

“这种情况完全可以建一个联合索引，因为是最左前缀匹配，所以operate_time需要放到最后，而且还需要把其他相关的查询都拿来，需要做一个综合评估。”

“联合索引？最左前缀匹配？综合评估？”工程师不禁陷入了沉思。

优化：

根据最左匹配原则，需要建立联合索引的顺序应该是：status、operator_id、type、operate_time。其中可以调整status、operator_id、type三个字段的顺序。那怎么安排这2个字段的顺序呢？我们建联合索引当然希望可以服务更多的查询，除了咱们需要服务的这个慢SQL，还需要应用程序员思考一下常见的查询，比如会不会有如下的查询。

select 

如果存在这两个查询，且场景挺多的，那么建立(status,type,operator_id,operate_time)就非常正确。

案例二：

很多情况下，我们写SQL只是为了实现功能，这只是第一步，不同的语句书写方式对于效率往往有本质的差别，这要求我们对mysql的执行计划和索引原则有非常清楚的认识，请看下面的语句：

select
   

优化：

1. 这时一个复杂的SQL，直接运行感受一下这个慢SQL

53 rows in set (1.87 sec)

53条记录 1.87秒，比较慢。

2. explain查看执行计划。

+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| id | select_type | table      | type  | possible_keys                   | key                   | key_len | ref               | rows  | Extra                          |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
|  1 | PRIMARY     | cl         | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date     | 8       | NULL              |   379 | Using where; Using temporary   |
|  1 | PRIMARY     | <derived2> | ALL   | NULL                            | NULL                  | NULL    | NULL              | 63727 | Using where; Using join buffer |
|  2 | DERIVED     | emp        | ALL   | NULL                            | NULL                  | NULL    | NULL              | 13317 | Using where                    |
|  2 | DERIVED     | emp_cert   | ref   | emp_certificate_empid           | emp_certificate_empid | 4       | meituanorg.emp.id |     1 | Using index                    |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+

简述一下执行计划，首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录；然后查表扫描了63727条记录，分为两部分，derived表示构造表，也就是不存在的表，可以简单理解成是一个语句形成的结果集，后面的数字表示语句的ID。derived2表示的是ID = 2的查询构造了虚拟表，并且返回了63727条记录。我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据，首先全表扫描employee表13317条记录，然后根据索引emp_certificate_empid关联emp_certificate表，rows = 1表示，每个关联都只锁定了一条记录，效率比较高。获得后，再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据，返回的数据绝大部分cm_log都用不到，因为cm_log只锁定了379条记录。

如何优化呢？可以看到我们在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢？仔细分析语句不难发现，其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表，如果ref_table是Employee就关联employee表，我们完全可以拆成两部分，并用union连接起来，注意这里用union，而不用union all是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。如果原语句中没有distinct不需要去重，我们就可以直接使用union all（）（不需要去重）了，因为使用union需要去重的动作，会影响SQL性能。

优化过的语句如下：

select
   

3.用优化语句查看执行计划

+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| id | select_type  | table      | type   | possible_keys                   | key               | key_len | ref                   | rows | Extra       |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
|  1 | PRIMARY      | cl         | range  | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where |
|  1 | PRIMARY      | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 | Using where |
|  2 | UNION        | cl         | range  | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where |
|  2 | UNION        | ec         | eq_ref | PRIMARY,emp_certificate_empid   | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 | |
|  2 | UNION        | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.ec.emp_id  |    1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL                            | NULL              | NULL    | NULL                  | NULL | |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
53 rows in set (0.01 sec)

改造后的语句只需要10ms 降低了近200倍！

总结

mysql有两种索引类型B-Tree索引和hash索引，本文主要分析的索引原理是B-Tree的索引原理。

索引的基本原理是通过不断缩小查找数据的范围来锁定目标，为了更全面掌握得掌握B-Tree索引原理，咱们需要直到数据库的全部数据是存储在硬盘上的，为了提高查询性能并依据程序的局部性原理有必要提前对数据进行预读来减少磁盘IO次数。最后使用B-Tree这种数据结构来实现高效的数据查询。

高性能的索引策略要求依据索引的最左匹配原则来设计索引，建立联合索引来充分提供索引的有效覆盖，避免多索引列进行计算，如果这样做会导致无法命中索引。

慢SQL查询是一个细心活、需要掌握高性能索引创建技巧，分析慢SQL语句找到可能的瓶颈，推理、验证的过程。业务场景越复杂遇到的慢SQL也会千奇百怪，有时候DBA也有无能为力的时候，这时只有从业务上思考如何去做优化规避问题了。

总之，掌握索引原理和高性能索引策略能够很好地指导你写出更好更快的SQL语句并针对慢SQL有法可依。

The end.

转载请注明来源，否则严禁转载。