MySQL笔记（5）深入浅出索引（下）
154 浏览量
2020-12-14
17:02:40
上传
评论
收藏 442KB PDF 举报
MySQL笔记（笔记（5）深入浅出索引（下））深入浅出索引（下）
在下面这个表 T 中，如果我执行 select * from T where k between 3 and 5 ，需要执行几次树的搜索操作，会扫描多少行？
mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
现在，我们一起来看看这条 SQL 查询语句的执行流程：
`select * from T where k between 3 and 5`
在 k 索引树上找到 k=3 的记录，取得 ID = 300；
再到 ID 索引树查到 ID=300 对应的 R3；
在 k 索引树取下一个值 k=5，取得 ID=500；
再回到 ID 索引树查到 ID=500 对应的 R4；
在 k 索引树取下一个值 k=6，不满足条件，循环结束。
在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k
索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。
在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？
覆盖索引覆盖索引
如果执行的语句是 select ID from T where k between 3 and 5 （敲黑板，敲黑板，前面是*，这里是ID，where都是K），这时只需要查 ID 的值，而ID 的值已经在 k 索引树上了，因此可以直
接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的索引k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，
因此MySQL 认为扫描行数是 2。
备注：关于如何查看扫描行数的问题，后面有缘再见
基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
假设这个市民表的定义是这样的：
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联
合索引，是不是浪费空间？
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名和年龄，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的
执行时间。
当然，索引字段的维护总是有代价的。
最左前缀原则最左前缀原则
看到这里你一定有一个疑问，如果为每一种查询都设计一个索引，索引是不是太多了。如果我现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概
率不高，但总不能让它走全表扫描吧？反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该这么做呢？
这里，我先和你说结论吧。B+ 树这种索引结构，可以利用索引的树这种索引结构，可以利用索引的“最左前缀最左前缀”，来定位记录。，来定位记录。
为了直观地说明这个概念，我们用（name，age）这个联合索引来分析。
weixin_38659955
- 粉丝: 4
- 资源: 915
最新资源
- 学生成绩管理系统（C语言）.txt
- 学生成绩管理系统（C语言）.txt
- Screenshot_2023-12-10-12-52-34-127_net.csdn.csdnplus.jpg
- Screenshot_2023-12-10-12-50-49-374_net.csdn.csdnplus.jpg
- mxnet-cu111-1.9.0b20230903-py3-none-manylinux2014-x86-64.whl
- mxnet-cu110-2.0.0b20220324-py3-none-manylinux2014-x86-64.whl
- mxnet-cu110-1.9.1b20220823-py3-none-manylinux2014-x86-64.whl
- mxnet-cu110-1.9.0-py3-none-manylinux2014-x86-64.whl
- mxnet-cu110-1.9.0b20230903-py3-none-manylinux2014-x86-64.whl
- mxnet-cu110-1.8.0b20210324-py2.py3-none-manylinux2014-x86-64.whl
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
评论0