求解，索引选择与索引长度

龙雪刚

sakila数据库两张表film_actor,acotr.表创建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       |
+-------------------+---------+----------+----------+



现在有条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                                  |
+----+-------------+------------+-------+---------------+---------------------+---------+-----------------------+------+----------------------------------------------+

疑问：对actor表做全索引扫描时为何选择的是idx_actor_last_name而不是主键？为什么key_len长度是137？怎么算出来的。

RogerZhuo

回复 1# 龙雪刚
使劲想了一下

1， 为何要用idx_actor_last_name， 而不用主键呢？

这里肯定全表（或者全索引扫描）， 想想idx_actor_last_name为二级索引， 它的结果里其实是包括了主键actor_id，而你的查询列表只有actor_id,count(*),
那么扫描idx_actor_last_name索引树，就能满足你的要求， 这个索引的大小肯定小于主键（其实就是聚集索引，也就是整个表的大小了），
对表actor的查询说白了，就是“覆盖索引了”，所以就有你这个结果了。

2 ， 长度为何是137呢

个人觉得：索引idx_actor_last_name对应的字段last_name类型是varchar(45), 你所用的又是utf8,
所以有45*3  + 2=137 .


   

龙雪刚

回复 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                                  |
+----+-------------+------------+-------+---------------+---------+---------+-----------------------+------+----------------------------------------------+

如果我强迫使用primary key ，显示的长度是2,也就是smallint的长度。这似乎比135又小。我觉得是索引左匹配原则，它只用到了actor_id这一列。但是如果这样成立就无法解释为何选择二级索引而不选择主键了。

RogerZhuo

回复 4# 龙雪刚
innodb的主键是和表数据放在一起（所谓聚集表), 你要读取所有主键值，并且强制查询走主键索引， 也就是要扫描全表。
扫描整个表的i/o肯定是大小扫描二级索引的。

这里的key-length只是索引字段定义的最大长度，而不是实际的长度，实则与i/o不相关。


   

龙雪刚

RogerZhuo 发表于 2012-04-12 16:33
回复 4# 龙雪刚
innodb的主键是和表数据放在一起（所谓聚集表), 你要读取所有主键值，并且强制查询走主键 ...

查询走主键，就一定会扫描剩下其他的列吗？这和联合索引只用到了前半部分有哪些地方不同？希望赐教。

RogerZhuo

回复 7# 龙雪刚
你这里要取出所有主键，因为主键是和表所有数据保存在B tree 的叶子节点的，这样就要查询了三。
组合索引使用前半部分也扫描这个组合索引全部（包括未使用列），但是ken-length只会计算前面列的定义长度。



   

RogerZhuo

这个图片的结构就很说明问题了，说明为什么只扫描二级索引，还有为何是覆盖索引，不会回表。

ooooldman