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

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

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 ; 

SELECT * FROM query_test; 

SELECT ID FROM query_test; 

• 資深大佬 : flyfanc

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

• 資深大佬 : limuyan44

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

• 資深大佬 : JasonLaw

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

• 資深大佬 : JasonLaw

  @flyfanc 两个都是使用 clustered index 吧

• 資深大佬 : optional

  mysql 不是只有一个 clustered index 吗。

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

• 資深大佬 : optional

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

• 資深大佬 : mayday526

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

• 資深大佬 : chihiro2014

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

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

• 資深大佬 : wangyzj

  “`
  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)
  “`

• 主 資深大佬 : 15hop

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

• 資深大佬 : 996635

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

• 資深大佬 : Risin9

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

• 主 資深大佬 : 15hop

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

• 資深大佬 : JasonLaw

  @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

  @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.