11 | 索引：怎么提高查询的速度？

朱晓峰

你好，我是朱晓峰。
在我们的超市信息系统刚刚开始运营的时候，因为数据量很少，每一次的查询都能很快拿到结果。但是，系统运转时间长了以后，数据量不断地累积，变得越来越庞大，很多查询的速度就变得特别慢。这个时候，我们就采用了 MySQL 提供的高效访问数据的方法—— 索引，有效地解决了这个问题，甚至之前的一个需要 8 秒钟才能完成的查询，现在只用 0.3 秒就搞定了，速度提升了 20 多倍。
那么，索引到底是啥呢？该怎么使用呢？这节课，我们就来聊一聊。
索引是什么？如果你去过图书馆，应该会知道图书馆的检索系统。图书馆为图书准备了检索目录，包括书名、书号、对应的位置信息，包括在哪个区、哪个书架、哪一层。我们可以通过书名或书号，快速获知书的位置，拿到需要的书。
MySQL 中的索引，就相当于图书馆的检索目录，它是帮助 MySQL 系统快速检索数据的一种存储结构。我们可以在索引中按照查询条件，检索索引字段的值，然后快速定位数据记录的位置，这样就不需要遍历整个数据表了。而且，数据表中的字段越多，表中数据记录越多，速度提升越是明显。
我来举个例子，进一步解释下索引的作用。这里要用到销售流水表（demo.trans），表结构如下：
mysql> describe demo.trans;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| itemnumber    | int      | YES  | MUL | NULL    |       |
| quantity      | text     | YES  |     | NULL    |       |
| price         | text     | YES  |     | NULL    |       |
| transdate     | datetime | YES  | MUL | NULL    |       |
| actualvalue   | text     | YES  |     | NULL    |       |
| barcode       | text     | YES  |     | NULL    |       |
| cashiernumber | int      | YES  | MUL | NULL    |       |
| branchnumber  | int      | YES  | MUL | NULL    |       |
| transuniqueid | text     | YES  |     | NULL    |       |
+---------------+----------+------+-----+---------+-------+
9 rows in set (0.02 sec)

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

本文深入介绍了提高查询速度的关键——索引，重点讨论了单字段索引和组合索引的创建和使用。通过实例演示了添加索引后查询速度的显著提升，并通过EXPLAIN关键字展示了索引对SQL语句执行细节的优化作用。文章还解释了组合索引的原理，强调了筛选条件的顺序对索引的利用影响。此外，总结了索引的成本和注意事项，提醒读者在创建索引时需要综合考虑存储空间和数据操作开销。最后，提出了一个思考题，引导读者思考如何对销售统计表建立合适的索引。整体而言，本文内容丰富，适合技术人员深入了解索引的创建和使用原理。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《MySQL 必知必会》，新⼈⾸单¥59

立即购买

登录后留言

全部留言(17)

最新
精选

朱晓峰
置顶
你好，我是朱晓峰，下面我就来公布一下上节课思考题的答案：上节课，我们学习了数学函数、字符串函数和条件判断函数。下面是思考题的答案：计算会员积分，我们可以假设actualvalue>=0，这样的话就可以使用求余函数MOD来代替FLOOR: FLOOR(acutalvalue)替换成actualvalue - MOD(actualvalue,1)
2021-04-21

5
giteebravo
CREATE INDEX index_trans ON demo.trans (transdate(10)); ——括号里面的 10 有什么具体含义吗？
作者回复: 这里的10，表示用日期时间的前10位数据创建索引
2021-04-01
4
10
Devo
我会选择（商品编号，[门店编号]，销售日期）的联合索引，主要依据是商品编号一般是等值查询且区分度较高，门店编号这个字段我觉得可以看具体查询场景选择，销售日期一般是范围，放在末位直接扫链表效果较好，请老师指正，谢谢！
作者回复: 建议还是先创建单字段索引，使用率比较高，如果创建联合索引，可以考虑把门店编号放在最前面，原因是一般查询会遵循从大范围到小范围逐步递进的原则
2021-04-01
2
6
lesserror
总结一下这一讲的收获吧。 MySQL 最多支持由 16 个字段组成的组合索引。组合索引的所有组成字段都被查询条件用到，且符合最左匹配原则，查询效率有可能会比单字段索引快！关于索引这块的知识，建议去了解一下B+树的相关概念，会更容易理解索引的内部运行机制。比单纯背结论更加有效，并且会根深蒂固！
作者回复: 说得好
2021-04-01
2
2
giteebravo
在为字段 itemnumber 创建索引 index_trans_itemnumber 后，实际读取的记录数下降了 80% (与使用索引 index_trans 相比)，但为什么查询速度反而下降了 0.08 秒呢？
作者回复: 你是不是说课上的例子，使用index_trans索引比使用index_trans_itemnumber要更快呢？课上的例子中，优化器除了使用索引之外，使用index_trans索引的时候，还使用了MRR（顺序磁盘读取策略），这样综合的效果比单纯使用index_trans_itemnumber更快一些。
2021-04-01

2
彭彬
CREATE INDEX index_trans ON demo.trans (transdate(10)); 执行上述语句出错，提示transdate不是字符串
作者回复: 这个地方确实错了，应该是“CREATE INDEX index_trans ON demo.trans (transdate); ”，索引应该使用字段名。我已经请后台进行改正，非常感谢你指出我的错误。
2021-09-22
2
1
重洋
对字段a、b、c建立组合索引，选择a、b为查询条件时，此时组合索引的效果相当于只对a、b建立组合索引吗？
作者回复: 是的，这里要注意的是组合索引与字段的顺序有关。
2021-08-02

1
SharpBB
单字段索引 create index 索引名 on table 表名(字段); 注意创建主键约束/唯一约束会自动创建主键索引唯一性索引经常被筛选的字段当索引组合索引 create index 索引名 on table 表名(字段1,字段2); 删除索引 DROP INDEX 索引名 ON 表名; 如果是主键不能直接删除得先去掉主键 ALTER TABLE 表名 DROP PRIMARY KEY；
作者回复: 好的
2022-02-10


青石雨巷
CREATE INDEX 索引名 ON TABLE 表名 (字段); 这里TABLE字段要删掉，容易让别人产生误解
作者回复: 说得很对，这里“TABLE”不是关键字，容易产生误解，不够严谨，我已经请后台去掉“TABLE”，感谢你的指正。
2021-12-01


木木
老师你好，咱课程中提到的数据有提供吗？方便跟着操作练习
作者回复: 课程中用到的数据都是实际项目中的数据，是无法提供的。
2021-11-07
2


收起评论