求解，索引選擇與索引長(zhǎng)度

龍雪剛

sakila數(shù)據(jù)庫(kù)兩張表film_actor,acotr.表創(chuàng)建SQL如下：
mysql> show create table actor \G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8



mysql> show create table film_actor \G
*************************** 1. row ***************************
       Table: film_actor
Create Table: CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select count(*) from film_actor;
+----------+
| count(*) |
+----------+
|     5462 |
+----------+


mysql> select count(*) from actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+


mysql> analyze local table actor;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| sakila.actor | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> analyze local table film_actor;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| sakila.film_actor | analyze | status   | OK       |
+-------------------+---------+----------+----------+



現(xiàn)在有條SQL用explain解析如下：
mysql> explain select sql_no_cache film_actor.actor_id,count(*) from film_actor inner join actor using(actor_id) group by film_actor.actor_id order by count(*) desc;
+----+-------------+------------+-------+---------------+---------------------+---------+-----------------------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key                 | key_len | ref                   | rows | Extra                                        |
+----+-------------+------------+-------+---------------+---------------------+---------+-----------------------+------+----------------------------------------------+
|  1 | SIMPLE      | actor      | index | PRIMARY       | idx_actor_last_name | 137     | NULL                  |  200 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | film_actor | ref   | PRIMARY       | PRIMARY             | 2       | sakila.actor.actor_id |   13 | Using index                                  |
+----+-------------+------------+-------+---------------+---------------------+---------+-----------------------+------+----------------------------------------------+

疑問(wèn)：對(duì)actor表做全索引掃描時(shí)為何選擇的是idx_actor_last_name而不是主鍵？為什么key_len長(zhǎng)度是137？怎么算出來(lái)的。

RogerZhuo

回復(fù) 1# 龍雪剛
使勁想了一下

1， 為何要用idx_actor_last_name， 而不用主鍵呢？

這里肯定全表（或者全索引掃描）， 想想idx_actor_last_name為二級(jí)索引， 它的結(jié)果里其實(shí)是包括了主鍵actor_id，而你的查詢列表只有actor_id,count(*),
那么掃描idx_actor_last_name索引樹(shù)，就能滿足你的要求， 這個(gè)索引的大小肯定小于主鍵（其實(shí)就是聚集索引，也就是整個(gè)表的大小了），
對(duì)表actor的查詢說(shuō)白了，就是“覆蓋索引了”，所以就有你這個(gè)結(jié)果了。

2 ， 長(zhǎng)度為何是137呢

個(gè)人覺(jué)得：索引idx_actor_last_name對(duì)應(yīng)的字段last_name類型是varchar(45), 你所用的又是utf8,
所以有45*3  + 2=137 .


   

龍雪剛

回復(fù) 3# RogerZhuo

mysql> explain select sql_no_cache film_actor.actor_id,count(*) from film_actor inner join actor force index(primary) using(actor_id) group by film_actor.actor_id order by count(*) desc;
+----+-------------+------------+-------+---------------+---------+---------+-----------------------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref                   | rows | Extra                                        |
+----+-------------+------------+-------+---------------+---------+---------+-----------------------+------+----------------------------------------------+
|  1 | SIMPLE      | actor      | index | PRIMARY       | PRIMARY | 2       | NULL                  |  200 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | film_actor | ref   | PRIMARY       | PRIMARY | 2       | sakila.actor.actor_id |   13 | Using index                                  |
+----+-------------+------------+-------+---------------+---------+---------+-----------------------+------+----------------------------------------------+

如果我強(qiáng)迫使用primary key ，顯示的長(zhǎng)度是2,也就是smallint的長(zhǎng)度。這似乎比135又小。我覺(jué)得是索引左匹配原則，它只用到了actor_id這一列。但是如果這樣成立就無(wú)法解釋為何選擇二級(jí)索引而不選擇主鍵了。

RogerZhuo

回復(fù) 4# 龍雪剛
innodb的主鍵是和表數(shù)據(jù)放在一起（所謂聚集表), 你要讀取所有主鍵值，并且強(qiáng)制查詢走主鍵索引， 也就是要掃描全表。
掃描整個(gè)表的i/o肯定是大小掃描二級(jí)索引的。

這里的key-length只是索引字段定義的最大長(zhǎng)度，而不是實(shí)際的長(zhǎng)度，實(shí)則與i/o不相關(guān)。


   

龍雪剛

RogerZhuo 發(fā)表于 2012-04-12 16:33
回復(fù) 4# 龍雪剛
innodb的主鍵是和表數(shù)據(jù)放在一起（所謂聚集表), 你要讀取所有主鍵值，并且強(qiáng)制查詢走主鍵 ...

查詢走主鍵，就一定會(huì)掃描剩下其他的列嗎？這和聯(lián)合索引只用到了前半部分有哪些地方不同？希望賜教。

RogerZhuo

回復(fù) 7# 龍雪剛
你這里要取出所有主鍵，因?yàn)橹麈I是和表所有數(shù)據(jù)保存在B tree 的葉子節(jié)點(diǎn)的，這樣就要查詢了三。
組合索引使用前半部分也掃描這個(gè)組合索引全部（包括未使用列），但是ken-length只會(huì)計(jì)算前面列的定義長(zhǎng)度。



   

RogerZhuo

這個(gè)圖片的結(jié)構(gòu)就很說(shuō)明問(wèn)題了，說(shuō)明為什么只掃描二級(jí)索引，還有為何是覆蓋索引，不會(huì)回表。

ooooldman