算法面试专题课java版，“索引”并不是万能药

其次是空间代价。虽然二级索引不保存原始数据，但要保存索引列的数据，所以会占用更多的空间。比如，person表创建了两个索引后，使用下面的SQL查看数据和索引占用的磁盘：

SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_NAME=‘person’

结果显示，数据本身只占用了4.7M，而索引占用了8.4M。

最后是回表的代价。二级索引不保存原始数据，通过索引找到主键后需要再查询聚簇索引，才能得到我们要的数据。比如，使用SELECT * 按照name字段查询用户，使用EXPLAIN查看执行计划：

EXPLAIN SELECT * FROM person WHERE NAME=‘name1’

执行计划如下，可以发现：

• key字段代表实际走的是哪个索引，其值是name_score，说明走的是name_score这个索引。

• type字段代表了访问表的方式，其值ref说明是二级索引等值匹配，符合我们的查询。

把SQL中的*修改为NAME和SCORE，也就是SELECT name_score联合索引包含的两列：

EXPLAIN SELECT NAME,SCORE FROM person WHERE NAME=‘name1’

再来看看执行计划：

可以看到，Extra列多了一行Using index的提示，证明这次查询直接查的是二级索引，免去了回表。

原因很简单，联合索引中其实保存了多个索引列的值，对于页中的记录先按照字段1排序，如果相同再按照字段2排序，如图所示：

图中，叶子节点每一条记录的第一和第二个方块是索引列的数据，第三个方块是记录的主键。如果我们需要查询的是索引列索引或联合索引能覆盖的数据，那么查询索引本身已经“覆盖”了需要的数据，不再需要回表查询。因此，这种情况也叫作索引覆盖。我会在最后一小节介绍如何查看不同查询的成本，和你一起看看索引覆盖和索引查询后回表的代价差异。

最后，我和你总结下关于索引开销的最佳实践吧。

第一，无需一开始就建立索引，可以等到业务场景明确后，或者是数据量超过1万、查询变慢后，再针对需要查询、排序或分组的字段创建索引。创建索引后可以使用EXPLAIN命令，确认查询是否可以使用索引。我会在下一小节展开说明。

第二，尽量索引轻量级的字段，比如能索引int字段就不要索引varchar字段。索引字段也可以是部分前缀，在创建的时候指定字段索引长度。针对长文本的搜索，可以考虑使用Elasticsearch等专门用于文本搜索的索引数据库。

第三，尽量不要在SQL语句中SELECT *，而是SELECT必要的字段，甚至可以考虑使用联合索引来包含我们要搜索的字段，既能实现索引加速，又可以避免回表的开销。

不是所有针对索引列的查询都能用上索引

==================

在上一个案例中，我创建了一个name+score的联合索引，仅搜索name时就能够用上这个联合索引。这就引出两个问题：

• 是不是建了索引一定可以用上？

• 怎么选择创建联合索引还是多个独立索引？

首先，我们通过几个案例来分析一下索引失效的情况。

第一，索引只能匹配列前缀。比如下面的LIKE语句，搜索name后缀为name123的用户无法走索引，执行计划的type=ALL代表了全表扫描：

EXPLAIN SELECT * FROM person WHERE NAME LIKE ‘%name123’ LIMIT 100

把百分号放到后面走前缀匹配，type=range表示走索引扫描，key=name_score看到实际走了name_score索引：

EXPLAIN SELECT * FROM person WHERE NAME LIKE ‘name123%’ LIMIT 100

原因很简单，索引B+树中行数据按照索引值排序，只能根据前缀进行比较。如果要按照后缀搜索也希望走索引的话，并且永远只是按照后缀搜索的话，可以把数据反过来存，用的时候再倒过来。

第二，条件涉及函数操作无法走索引。比如搜索条件用到了LENGTH函数，肯定无法走索引：

EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7

同样的原因，索引保存的是索引列的原始值，而不是经过函数计算后的值。如果需要针对函数调用走数据库索引的话，只能保存一份函数变换后的值，然后重新针对这个计算列做索引。

第三，联合索引只能匹配左边的列。也就是说，虽然对name和score建了联合索引，但是仅按照score列搜索无法走索引：

EXPLAIN SELECT * FROM person WHERE SCORE>45678

原因也很简单，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。尝试把搜索条件加入name列，可以看到走了name_score索引：

EXPLAIN SELECT * FROM person WHERE SCORE>45678 AND NAME LIKE ‘NAME45%’

需要注意的是，因为有查询优化器，所以name作为WHERE子句的第几个条件并不是很重要。

现在回到最开始的两个问题。

• 是不是建了索引一定可以用上？并不是，只有当查询能符合索引存储的实际结构时，才能用上。这里，我只给出了三个肯定用不上索引的反例。其实，有的时候即使可以走索引，MySQL也不一定会选择使用索引。我会在下一小节展开这一点。

• 怎么选择建联合索引还是多个独立索引？如果你的搜索条件经常会使用多个字段进行搜索，那么可以考虑针对这几个字段建联合索引；同时，针对多字段建立联合索引，使用索引覆盖的可能更大。如果只会查询单个字段，可以考虑建单独的索引，毕竟联合索引保存了不必要字段也有成本。

数据库基于成本决定是否走索引

==============

通过前面的案例，我们可以看到，查询数据可以直接在聚簇索引上进行全表扫描，也可以走二级索引扫描后到聚簇索引回表。看到这里，你不禁要问了，MySQL到底是怎么确定走哪种方案的呢。

其实，MySQL在查询数据之前，会先对可能的方案做执行计划，然后依据成本决定走哪个执行计划。

这里的成本，包括IO成本和CPU成本：

• IO成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的IO成本常数是1（也就是读取1个页成本是1）。

• CPU成本，是检测数据是否满足条件和排序等CPU操作的成本。默认情况下，检测记录的成本是0.2。

基于此，我们分析下全表扫描的成本。

全表扫描，就是把聚簇索引中的记录依次和给定的搜索条件做比较，把符合搜索条件的记录加入结果集的过程。那么，要计算全表扫描的代价需要两个信息：

• 聚簇索引占用的页面数，用来计算读取数据的IO成本；

• 表中的记录数，用来计算搜索的CPU成本。

那么，MySQL是实时统计这些信息的吗？其实并不是，MySQL维护了表的统计信息，可以使用下面的命令查看：

SHOW TABLE STATUS LIKE ‘person’

输出如下：

可以看到：

• 总行数是100086行（之前EXPLAIN时，也看到rows为100086）。你可能说，person表不是有10万行记录吗，为什么这里多了86行？其实，MySQL的统计信息是一个估算，其统计方式比较复杂我就不再展开了。但不妨碍我们根据这个值估算CPU成本，是100086*0.2=20017左右。

• 数据长度是4734976字节。对于InnoDB来说，这就是聚簇索引占用的空间，等于聚簇索引的页面数量*每个页面的大小。InnoDB每个页面的大小是16KB，大概计算出页面数量是289，因此IO成本是289左右。

所以，全表扫描的总成本是20306左右。

接下来，我还是用person表这个例子，和你分析下MySQL如何基于成本来制定执行计划。现在，我要用下面的SQL查询name>‘name84059’ AND create_time>‘2020-01-24 05:00:00’

EXPLAIN SELECT * FROM person WHERE NAME >‘name84059’ AND create_time>‘2020-01-24 05:00:00’

其执行计划是全表扫描：

只要把create_time条件中的5点改为6点就变为走索引了，并且走的是create_time索引而不是name_score联合索引：

我们可以得到两个结论：

• MySQL选择索引，并不是按照WHERE条件中列的顺序进行的；

• 即便列有索引，甚至有多个可能的索引方案，MySQL也可能不走索引。

其原因就是，MySQL并不是猜拳决定是否走索引的，而是根据成本来判断的。虽然表的统计信息不完全准确，但足够用于策略的判断了。

不过，有时会因为统计信息的不准确或成本估算的问题，实际开销会和MySQL统计出来的差距较大，导致MySQL选择错误的索引或是直接选择走全表扫描，这个时候就需要人工干预，使用强制索引了。比如，像这样强制走name_score索引：

EXPLAIN SELECT * FROM person FORCE INDEX(name_score) WHERE NAME >‘name84059’ AND create_time>‘2020-01-24 05:00:00’

我们介绍了MySQL会根据成本选择执行计划，也通过EXPLAIN知道了优化器最终会选择怎样的执行计划，但MySQL如何制定执行计划始终是一个黑盒。那么，有没有什么办法可以了解各种执行计划的成本，以及MySQL做出选择的依据呢？

在MySQL 5.6及之后的版本中，我们可以使用optimizer trace功能查看优化器生成执行计划的整个过程。有了这个功能，我们不仅可以了解优化器的选择过程，更可以了解每一个执行环节的成本，然后依靠这些信息进一步优化查询。

如下代码所示，打开optimizer_trace后，再执行SQL就可以查询

information_schema.OPTIMIZER_TRACE表查看执行计划了，最后可以关闭optimizer_trace功能：

SET optimizer_trace=“enabled=on”;

SELECT * FROM person WHERE NAME >‘name84059’ AND create_time>‘2020-01-24 05:00:00’;

SELECT * FROM information_schema.OPTIMIZER_TRACE;

SET optimizer_trace=“enabled=off”;

对于按照create_time>'2020-01-24 05:00:00’条件走全表扫描的SQL，我从OPTIMIZER_TRACE的执行结果中，摘出了几个重要片段来重点分析：

• 使用name_score对name84059<name条件进行索引扫描需要扫描25362行，成本是30435，因此最终没有选择这个方案。这里的30435是查询二级索引的IO成本和CPU成本之和，再加上回表查询聚簇索引的IO成本和CPU成本之和，我就不再具体分析了：

{

“index”: “name_score”,

“ranges”: [

“name84059 < name”

],

“rows”: 25362,

“cost”: 30435,

“chosen”: false,

“cause”: “cost”

},

• 使用create_time进行索引扫描需要扫描23758行，成本是28511，同样因为成本原因没有选择这个方案：

{

“index”: “create_time”,

“ranges”: [

“0x5e2a79d0 < create_time”

],

“rows”: 23758,

“cost”: 28511,

“chosen”: false,

“cause”: “cost”

}

• 最终选择了全表扫描方式作为执行计划。可以看到，全表扫描100086条记录的成本是20306，和我们之前计算的一致，显然是小于其他两个方案的28511和30435：

{

“considered_execution_plans”: [{

“table”: “person”,

“best_access_path”: {

“considered_access_paths”: [{

“rows_to_scan”: 100086,

“access_type”: “scan”,

“resulting_rows”: 100086,

“cost”: 20306,

“chosen”: true

}]

},

“rows_for_plan”: 100086,

“cost_for_plan”: 20306,

“chosen”: true

}]

},

把SQL中的create_time条件从05:00改为06:00，再次分析OPTIMIZER_TRACE可以看到，这次执行计划选择的是走create_time索引。因为是查询更晚时间的数据，走create_time索引需要扫描的行数从23758减少到了16588。这次走这个索引的成本19907小于全表扫描的20306，更小于走name_score索引的30435：

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

这份清华大牛整理的进大厂必备的redis视频、面试题和技术文档

祝大家早日进入大厂，拿到满意的薪资和职级~~~加油！！

感谢大家的支持！！

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！**

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

这份清华大牛整理的进大厂必备的redis视频、面试题和技术文档

祝大家早日进入大厂，拿到满意的薪资和职级~~~加油！！

感谢大家的支持！！

[外链图片转存中…(img-aZkcaZ25-1713470708745)]

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！