如何设计高性能的索引？

发表于 | 更新于 | 分类于
本文字数： 4.8k | 阅读时长 ≈ 4 分钟

思考并回答以下问题：

  • 回表就是通过辅助索引拿到主键id之后，要再去遍历聚集索引的B+树，这个过程就叫做回表。怎么理解？

上回我们主要研究了为什么使用索引，以及索引的数据结构。今天带你了解如何设计高性能的索引。

其中，有这么一个点，说的是InnoDB引擎中使用的是聚簇索引，其主索引的实现树中的叶子结点存储的是完整的数据记录，而辅助索引中存储的则只是辅助键和主键的值。

这样在用辅助索引进行查询时，会先查出主键的值，然后再去主索引中根据主键的值查询目标值。

比如，假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。

Id
Name
Company
5 Gates Microsoft
7 Bezos Amazon
11 Jobs Apple
14 Ellison Oracle

对于聚簇索引，若使用主键索引进行查询，select * from tab where id = 14;这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若使用辅助索引进行查询，对Name列进行条件搜索，则需要两个步骤：

  • 1、第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键值。
  • 2、第二步根据主键值在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

上面这个过程称为回表。

回表：在数据中，当查询数据的时候，在索引中查找索引后，获得该行的rowid，根据rowid再查询表中数据，就是回表。

显然，使用辅助索引出现了回表操作，这势必会影响查询性能，那有什么办法能够减少回表吗？

覆盖索引

上面，我们查询的是select *，如果是根据Name查询Id呢？即select Id from tab where Name=’Jobs’。

很明显，由于辅助索引Name上已经存储了Id的值，所以这时，查询便不会再次回表查询。

如果索引已经包含了所有满足查询需要的数据，这时我们称之为覆盖索引（Covering Index），这时就不再需要回表操作。

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点：

  • 1、索引条目通常远小于数据行大小，只需要读取索引，则 MySQL 会极大地减少数据访问量。
  • 2、因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
  • 3、覆盖索引对 InnoDB 表特别有用。因为 InnoDB 的辅助索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询；

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

联合索引/最左匹配原则

又名复合索引，由两个或多个列的索引。

它规定了MySQL从左到右地使用索引字段，对字段的顺序有一定要求。

另外，一个查询可以只使用索引中的一部分，更准确地说是最左侧部分（最左优先），这就是传说中的最左匹配原则。

即最左优先，如：

如果有一个2列的索引(col1,col2)，则相当于已经对(col1)、(col1,col2)上建立了索引；

如果有一个3列索引(col1,col2,col3)，则相当于已经对(col1)、(col1,col2)、(col1,col2,col3) 上建立了索引；

但是(col2,col3)上并没有。

假定数据表有一个包含2列的联合索引（a, b），则索引的B+树结构可能如下：

键值都是排序的，通过叶子节点可以逻辑上顺序的读出所有数据。

数据（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）是按照（a，b）先比较a再比较b的顺序排列。

所以从全局看，a是全局有序的，而b则不是。

基于上面的结构，对于以下查询显然是可以使用（a，b）这个联合索引的：

1
2
3
select * from table where a=xxx and b=xxx ;

select * from table where a=xxx;

但是对于下面的sql是不能使用这个联合索引的，因为叶子节点的b值，1,2,1,4,1,2显然不是排序的。

1
select * from table where b=xxx

只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

注意

1、主键字段其实跟所有非主键索引建立了联合索引，只是说如果主键字段没有在联合索引中明确声明，只会在其他索引中处于最右边；

2、最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。

比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引，则都可以用到，a,b,d的顺序可以任意调整。

3、= 和 in 的条件可以乱序

MySQL的查询优化器会帮你优化成索引可以识别的形式。MySQL查询优化器会判断纠正SQL语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

为什么要使用联合索引？

1、减少开销

“一个顶三个”。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3) 三个索引。

每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

2、覆盖索引

对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机IO操作。

减少IO操作，特别的随机IO其实是DBA主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

3、效率高

索引列越多，通过索引筛选出的数据越少。

有1000W条数据的表，有如下sql: select col1,col2,col3 from table where col1=1 and col2=2 and col3=3，假设假设每个条件可以筛选出10%的数据。

如果只有单值索引，那么通过该索引能筛选出1000W_10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；

如果是联合索引，通过索引筛选出1000w_10% * 10% * 10%=1w，效率提升可想而知！

索引下推

索引条件下推（ICP：index condition pushdown）是MySQL中一个常用的优化，尤其是当MySQL需要从一张表里检索数据时。

ICP（index condition pushdown）是MySQL利用索引（二级索引）元组和筛字段在索引中的WHERE条件从表中提取数据记录的一种优化操作。

ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的where条件，如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。

ICP（优化器）尽可能的把index condition的处理从server层下推到存储引擎层。

存储引擎使用索引过滤不相关的数据，仅返回符合index condition条件的数据给server层。也是说数据过滤尽可能存储引擎层进行，而不是返回所有数据给server层，然后后再根据where条件进行过滤。

下推过程

优化器没有使用ICP时，数据访问和提取的过程如下：

①：MySQL Server发出读取数据的命令，调用存储引擎的索引读或全表表读。此处进行的是索引读。

②、③：进入存储引擎，读取索引树，在索引树上查找，把满足条件的（红色的）从表记录中读出（步骤④，通常有IO）。

⑤：从存储引擎返回标识的结果。

以上，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要进行进行步骤④，通常有IO。

⑥：从存储引擎返回查找到的多条数据给MySQL Server，MySQL Server在⑦得到较多的元组。

⑦—⑧：依据WHERE子句条件进行过滤，得到满足条件的数据。

注意在MySQL Server层得到较多数据，然后才过滤，最终得到的是少量的、符合条件的数据。

在不支持ICP的系统下，索引仅仅作为data access使用。

优化器使用ICP时：

①：MySQL Server发出读取数据的命令，过程同图一。

②、③：进入存储引擎，读取索引树，在索引树上查找，把满足已经下推的条件的（红色的）从表记录中读出（步骤 ④，通常有 IO）；

⑤：从存储引擎返回标识的结果。

此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要在③这个阶段依据下推的条件进行进行判断，不满足条件的，不去读取表中的数据，直接在索引树上进行下一个索引项的判断，直到有满足条件的，才进行步骤④，这样，较没有ICP的方式，IO量减少。

⑥：从存储引擎返回查找到的少量数据给MySQL Server，MySQL Server在⑦得到少量的数据。

因此比较图一无ICP的方式，返回给MySQL Server层的即是少量的、 符合条件的数据。

在ICP优化开启时，在存储引擎端首先用索引过滤可以过滤的where条件，然后再用索引做data access，被index condition过滤掉的数据不必读取，也不会返回server端。

举例

比如：

1
SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';

在没有ICP时，首先通过索引前缀从存储引擎中读出所有first_name为Mary的记录，然后在server端用where筛选last_name的like条件；

而启用ICP后，由于last_name的like筛选可以通过索引字段进行，那么存储引擎内部通过索引与where条件的对比来筛选掉不符合where条件的记录，这个过程不需要读出整条记录，同时只返回给server筛选后条记录，因此提高了查询性能。

注意事项

有几个关于ICP的事情要注意：

ICP只能用于二级索引，不能用于主索引；

也不是全部where条件都可以用ICP筛选，如果某where条件的字段不在索引中，当然还是要读取整条记录做筛选，在这种情况下，仍然要到server端做where筛选；

ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例；

总结建索引的几大原则

1、最左前缀匹配原则，非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询 （>、<、between、like）就停止匹配；

2、=和in的条件可以乱序；

3、尽量选择区分度高的列作为索引，区分度表示字段不重复的比例，比例越大我们扫描的记录数越少；

4、索引列不能参与计算，保持列「干净」。原因很简单，B+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

5、尽量的扩展索引，不要新建索引。

索引是最好的解决方案吗？

索引不是最好的，但已经是相当好的了。

当表非常小时，没必要使用索引，直接全表查询好了；

当表是中大型时，比较适合使用索引，来快速定位目标数据；

当表是超大型时，创建和维护索引都是不小的代价，需要专业的DBA来分析，这种情况下可以尝试使用分表技术；

0%