这是一个创建于 1633 天前的主题，其中的信息可能已经有所发展或是发生改变。
表结构
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 条回复 • 2020-07-09 21:59:17 +08:00
 |
1
flyfanc 2020-07-09 13:04:51 +08:00
查询 2 直接从索引中获取数据，查询 1 还要回表，获取除 id 外的其它值
|
 |
2
limuyan44 2020-07-09 13:05:31 +08:00
id 不是 key 吗，二为什么会扫描叶子节点呢
|
 |
3
JasonLaw 2020-07-09 13:08:23 +08:00 via iPhone
用 SELECT NAME FROM query_test 的结果如何呢？
|
 |
5
optional 2020-07-09 13:26:02 +08:00
mysql 不是只有一个 clustered index 吗。
这里的对比不公平吧，虽然扫描方式类似，但是第二个不用拷贝返回数据啊。 |
|
6
optional 2020-07-09 13:30:25 +08:00
clustered index 与 non-clustered 的区别是值的区别啊，一个存 id，一个存 offset，这里 select id 没必要去访问 leaf 吧。
|
 |
7
mayday526 2020-07-09 13:46:26 +08:00
只有第二个用到了聚簇索引，第一个是全表扫描； InnoDB 的叶子节点是链表结构连起来的，所以并没有走索引，而是直接遍历链表全表扫描了；第二个会用到聚簇索引的原因是，所查的字段刚好是索引的 key，这叫覆盖索引，直接获取索引 key 返回就行了
|
 |
8
chihiro2014 2020-07-09 13:51:27 +08:00
吐槽一句，你的 50w 数据，可能没插几条，就插不动了。
MySQL 索引我记得默认 B+ Tree，叶子节点上保存的是对应的索引 id （ record id ），你第一个相当于全表扫描不走索引，自然慢。 第二个走了索引，所以快，但是你返回的只是 id 也就是索引 key，又不是 id 所对应的数据。。这速度当然有问题 |
 |
9
wangyzj 2020-07-09 14:05:03 +08:00
```
mysql> explain SELECT name FROM query_test; +------+-------------+------------+------+---------------+------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+--------+-------+ | 1 | SIMPLE | query_test | ALL | NULL | NULL | NULL | NULL | 497500 | | +------+-------------+------------+------+---------------+------+---------+------+--------+-------+ 1 row in set (0.02 sec) mysql> explain SELECT id FROM query_test; +------+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | query_test | index | NULL | PRIMARY | 4 | NULL | 497500 | Using index | +------+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+ 1 row in set (0.02 sec) mysql> explain SELECT * FROM query_test; +------+-------------+------------+------+---------------+------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+--------+-------+ | 1 | SIMPLE | query_test | ALL | NULL | NULL | NULL | NULL | 497500 | | +------+-------------+------------+------+---------------+------+---------+------+--------+-------+ 1 row in set (0.00 sec) ``` |
 |
11
996635 2020-07-09 16:50:45 +08:00
只查 ID 覆盖索引,不用回表.
|
 |
12
Risin9 2020-07-09 17:20:05 +08:00 via Android
楼上说的对，只查 ID，索引覆盖，不用回表取数据了
|
 |
13
15hop OP @chihiro2014 是哎 本地插入 20 分钟，每秒差不多 400 条
|
|
14
JasonLaw 2020-07-09 21:55:39 +08:00
@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 |
|
15
JasonLaw 2020-07-09 21:59:17 +08:00
@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. |
Select Language