mysql 查询类型 index 和 all 查询效率上的区别

• 请不要在回答技术问题时复制粘贴 AI 生成的内容

This topic created in 2145 days ago, the information mentioned may be changed or developed.

表结构

CREATE TABLE `query_test` (
	`ID` INT NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR(64) DEFAULT '',
	PRIMARY KEY (ID)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

构造 50w 数据

DELIMITER $$
CREATE PROCEDURE insert_query_test(IN cnt INTEGER, IN tb VARCHAR(64))
BEGIN
	DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE return_str varchar(255) DEFAULT '';
	DECLARE i int;
	set i=1;
	while i<=cnt DO
		SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
		set @stmt = CONCAT('INSERT INTO ',tb,' (NAME) VALUES("',return_str,'");'); 
		PREPARE stmt_pre FROM @stmt;
		EXECUTE stmt_pre;
		DEALLOCATE PREPARE stmt_pre;
		SET i = i + 1;
	end while;
END$$
DELIMITER ;

对比

查询 1，扫了全表，type=all，耗时 4.775s

SELECT * FROM query_test;

查询 2，扫了主键索引树，type=index，耗时 1.901s 。extra 提示 using index，只

SELECT ID FROM query_test;

问题

从聚簇索引的结构上看，叶子节点实际存储了索引值和具体的行数据。有一点不解的是，两个查询应该都扫描了聚簇索引的叶子节点(包括索引键和行数据)，但是为什么查询 2 和快于查询 1 呢？

会不会是select *的查询数据量多于select id造成的？

15 replies • 2020-07-09 21:59:17 +08:00

flyfanc

Jul 9, 2020

查询 2 直接从索引中获取数据，查询 1 还要回表，获取除 id 外的其它值

limuyan44

Jul 9, 2020

id 不是 key 吗，二为什么会扫描叶子节点呢

JasonLaw

Jul 9, 2020 via iPhone

用 SELECT NAME FROM query_test 的结果如何呢？

JasonLaw

Jul 9, 2020 via iPhone

@flyfanc 两个都是使用 clustered index 吧

optional

Jul 9, 2020

mysql 不是只有一个 clustered index 吗。

这里的对比不公平吧，虽然扫描方式类似，但是第二个不用拷贝返回数据啊。

optional

Jul 9, 2020

clustered index 与 non-clustered 的区别是值的区别啊，一个存 id，一个存 offset，这里 select id 没必要去访问 leaf 吧。

mayday526

Jul 9, 2020

只有第二个用到了聚簇索引，第一个是全表扫描； InnoDB 的叶子节点是链表结构连起来的，所以并没有走索引，而是直接遍历链表全表扫描了；第二个会用到聚簇索引的原因是，所查的字段刚好是索引的 key，这叫覆盖索引，直接获取索引 key 返回就行了

chihiro2014

Jul 9, 2020

吐槽一句，你的 50w 数据，可能没插几条，就插不动了。

MySQL 索引我记得默认 B+ Tree，叶子节点上保存的是对应的索引 id （ record id ），你第一个相当于全表扫描不走索引，自然慢。
第二个走了索引，所以快，但是你返回的只是 id 也就是索引 key，又不是 id 所对应的数据。。这速度当然有问题

wangyzj

Jul 9, 2020

15hop

Jul 9, 2020

@mayday526 是说第二个查询也是走主键(聚簇)索引的叶节点链表，只是没有去取其他列数据吗

996635

Jul 9, 2020

只查 ID 覆盖索引,不用回表.

Risin9

Jul 9, 2020 via Android

楼上说的对，只查 ID，索引覆盖，不用回表取数据了

15hop

Jul 9, 2020 via iPhone

@chihiro2014 是哎本地插入 20 分钟，每秒差不多 400 条

JasonLaw

Jul 9, 2020

@flyfanc #1
@mayday526 #7
@chihiro2014 #8
@996635 #11
@Risin9 #12

以下是我做的一个测试。

1. 创建表（注意：id 和 value 的类型是一样的）

CREATE TABLE `t`
(
`id` int(11) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;

2. 插入数据（伪代码），id 和 value 的值是一样的

for (int i = 1; i < 500000; i++) {
insert into t values (i, i);
}

3. 使用命令行登录进数据库管理系统，并使用特定的数据库

mysql -u {user} -p{password}
use database;

4. 开启 profiling

SET profiling = 1;

5. 执行以下语句

select * from t;
select id from t;
select value from t;

6. `show PROFILES;`的结果如下：

+----------+------------+---------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------+
| 1 | 0.24099925 | select * from t |
| 2 | 0.15437950 | select id from t |
| 3 | 0.14546525 | select value from t |
+----------+------------+---------------------+

参考资料：
https://dev.mysql.com/doc/refman/5.6/en/show-profile.html
https://dev.mysql.com/doc/refman/5.6/en/performance-schema-query-profiling.html

JasonLaw

Jul 9, 2020

@optional #6
@15hop #10

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

Every InnoDB table has a special index called the clustered index where the data for the rows is stored.

Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data.

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.