mysql使用explain索引分析結(jié)果幫忙給看一下 謝謝

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排在最前面,查詢時where里又沒有,所以索引是用不上的.

EDITED:

你這結(jié)果之所以這樣,是因為聚簇索引的原因, 你的索引包含了全部數(shù)據(jù)(主鍵在所有的索引里都存在), 等于是和掃描全表一樣的.

以下是有點長的解釋, 有興趣的可以執(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ù)，所以會用到不奇怪，possible_keys是mysql判斷這個sql沒有有效的索引可用給你一個null，key是執(zhí)行計劃選擇一個代價較低的方式檢索而已（因為選擇全表代價近似），另外因為聚簇索引和索引文件的數(shù)據(jù)文件在磁盤上是一起存儲的，檢索聚簇索引可能要掃描這一整個數(shù)據(jù)文件，而檢索覆蓋索引就少些了，至于rows就是要掃描整個索引段，當(dāng)然就是全數(shù)據(jù)行檢索了。