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.

你的索引c1c2排在最前面,查詢時(shí)where里又沒有,所以索引是用不上的.

EDITED:

你這結(jié)果之所以這樣,是因?yàn)榫鄞厮饕脑? 你的索引包含了全部數(shù)據(jù)(主鍵在所有的索引里都存在), 等于是和掃描全表一樣的.

以下是有點(diǎn)長(zhǎng)的解釋, 有興趣的可以執(zhí)行看看

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'
*/    ```

全字段覆蓋索引本身就包含了所有數(shù)據(jù)，所以會(huì)用到不奇怪，possible_keys是mysql判斷這個(gè)sql沒有有效的索引可用給你一個(gè)null，key是執(zhí)行計(jì)劃選擇一個(gè)代價(jià)較低的方式檢索而已（因?yàn)檫x擇全表代價(jià)近似），另外因?yàn)榫鄞厮饕退饕募臄?shù)據(jù)文件在磁盤上是一起存儲(chǔ)的，檢索聚簇索引可能要掃描這一整個(gè)數(shù)據(jù)文件，而檢索覆蓋索引就少些了，至于rows就是要掃描整個(gè)索引段，當(dāng)然就是全數(shù)據(jù)行檢索了。