mysql使用explain索引分析结果帮忙给看一下 谢谢

Question

表的相关情况如下:
结构：

数据：

联合索引：

分析语句如下:

EXPLAIN SELECT
    * 
FROM
    c_test 
WHERE
    c3 = 6 
    AND c4 = 8 
    AND c5 =3

结果如下:

不明白为什么possible_keys为null了, key却有值, 然后rows为7,这特么不是全表扫描么！！！

而Extra的Using index不是覆盖索引么, 懵逼了!!!

Answer 1

全字段覆盖索引本身就包含了所有数据，所以会用到不奇怪，possible_keys是mysql判断这个sql没有有效的索引可用给你一个null，key是执行计划选择一个代价较低的方式检索而已（因为选择全表代价近似），另外因为聚簇索引和索引文件的数据文件在磁盘上是一起存储的，检索聚簇索引可能要扫描这一整个数据文件，而检索覆盖索引就少些了，至于rows就是要扫描整个索引段，当然就是全数据行检索了。

Answer 2

你的索引c1c2排在最前面,查询时where里又没有,所以索引是用不上的.

EDITED:

你这结果之所以这样,是因为聚簇索引的原因, 你的索引包含了全部数据(主键在所有的索引里都存在), 等于是和扫描全表一样的.

以下是有点长的解释, 有兴趣的可以执行看看

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  `c5` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c12345` (`c1`,`c2`,`c3`,`c4`,`c5`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1;




delimiter #
create procedure fill_data()
begin

declare v_max int unsigned default 10000;
declare v_counter int unsigned default 0;

  truncate table mytable;
  start transaction;
  while v_counter < v_max do
    insert into mytable (c1,c2,c3,c4,c5) values(null,null,null,null,null);
    set v_counter=v_counter+1;
  end while;
  commit;
end #

delimiter ;



-- drop TRIGGER before_insert_mytable;

DELIMITER ;;
CREATE  TRIGGER before_insert_mytable
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
  IF new.c1 IS NULL THEN
    SET new.c1 = floor(0 + (rand() * 65535));
  END IF;
 IF new.c2 IS NULL THEN
    SET new.c2 = floor(0 + (rand() * 65535));
  END IF;
 IF new.c3 IS NULL THEN
    SET new.c3 = floor(0 + (rand() * 65535));
  END IF;
 IF new.c4 IS NULL THEN
    SET new.c4 = floor(0 + (rand() * 65535));
  END IF;
 IF new.c5 IS NULL THEN
    SET new.c5 = floor(0 + (rand() * 65535));
  END IF;
END
;;


ALTER TABLE `test`.`mytable` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;

ALTER TABLE `test`.`mytable` 
ADD INDEX `c12345` (`c1` ASC, `c2` ASC, `c3` ASC, `c4` ASC, `c5` ASC);

call fill_data();


EXPLAIN SELECT
    * 
FROM
    mytable 
WHERE
    c3 = 6 
    AND c4 = 8 
    AND c5 =3;


/*
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'mytable', 'index', NULL, 'c12345', '25', NULL, '10207', 'Using where; Using index'
*/

ALTER TABLE `test`.`mytable` 
drop INDEX `c12345` (`c1` ASC, `c2` ASC, `c3` ASC, `c4` ASC, `c5` ASC);

ALTER TABLE `test`.`mytable` 
add INDEX `c1234` (`c1` ASC, `c2` ASC, `c3` ASC, `c4` ASC);


EXPLAIN SELECT
    * 
FROM
    mytable 
WHERE
    c3 = 6 
    AND c4 = 8 
    AND c5 =3;
/*    
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'mytable', 'ALL', NULL, NULL, NULL, NULL, '10207', 'Using where'
*/


ALTER TABLE `test`.`mytable` 
drop INDEX `c1234`;

ALTER TABLE `test`.`mytable` 
add INDEX `c2345` ( `c2` ASC, `c3` ASC, `c4` ASC, `c5` ASC);


EXPLAIN SELECT
    * 
FROM
    mytable 
WHERE
    c3 = 6 
    AND c4 = 8 
    AND c5 =3;


/*
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'mytable', 'ALL', NULL, NULL, NULL, NULL, '10207', 'Using where'
*/    ```

Answer 3

explain-join-types (MySQL 5.7 Reference Manual)

If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.