MySQL-索引的使用及其长度计算的问题

MySQL-索引的使用及其长度计算的问题

泛泛之交 发布于 2017-08-31 字数 2531 浏览 1116 回复 4
CREATE TABLE `pp_actor` (
`pp_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 (`pp_actor_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

CREATE TABLE `pp_film_actor` (
`pp_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 (`pp_actor_id`,`film_id`),
KEY `idx_fk_film_id` (`film_id`),
CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`pp_actor_id`) REFERENCES `pp_actor` (`pp_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 pp_film_actor;
+----------+
| count(*) |
+----------+
| 5462 |
+----------+

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

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

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

瑾兮 2017-10-15 4 楼

我测试的结果是使用的主键
至于key_len的计算方式
ken_len表示索引使用的字节数,根据这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段都被查询用到。

(1).索引字段的附加信息:可以分为变长和定长数据类型讨论,当索引字段为定长数据类型,比如char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节;对于变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节;

(备注:当字段定义为非空的时候,是否为空的标记将不占用字节)

(2).同时还需要考虑表所使用的字符集,不同的字符集,gbk编码的为一个字符2个字节,utf8编码的一个字符3个字节;

详细的计算方式见mysql explain 中key_len的计算

晚风撩人 2017-10-06 3 楼

这里肯定是全表扫描, idx_actor_last_name为二级索引, 它的结果里其实是包括了主键actor_id,而你的查询列表只有actor_id,count(*),那么扫描idx_actor_last_name索引树,就能满足你的要求。
索引idx_actor_last_name对应的字段last_name类型是varchar(45), 你所用的又是utf8,
所以有45*3+2=137。

归属感 2017-09-08 2 楼

顶一下, 是个好问题, 楼上各位关于索引长度的计算回答也很到位, 学习了.

楼主的另一个问题:"对pp_actor表做全索引扫描时为何用的是idx_actor_last_name而不是主键?"

我认为是这样, 因为InnoDB是索引聚集表, 主键索引叶节点实际就是 表记录节点; 所以虽然我们explain的时候可以看到 使用了主键的 覆盖索引, 其实这里也就是全表扫描(个人理解,欢迎指正!).

而InnoDB中, 用二级索引来做覆盖索引, 取得主键id, 这里是真正的只扫描了二级索引, 而没有去读数据表.

我做了一些实验, InnoDB只建了主键, 执行:

explain select count(distinct id) from t1;

则走主键的覆盖索引. 而如果我一旦建立了二级索引, 则上述sql改为走二级索引的覆盖索引.

另外, 把上述表改为MyISAM,

 alter table t1 engine=MyISAM;

则上述sql会一直走主键的覆盖索引.

浮生未歇 2017-08-31 1 楼

在 disk storage 上,varchar(255)比char(255)好,当你只有5个字符时,比如"hello",vchar(255)只会用5个,而char(255)会填充250个字符,空间上有浪费。
但在索引上,要考虑最坏情况,所以你上面的例子,他会考虑last_name(45),45个字符的情况,然后才好分配好内存空间。在utf8下,一个字符占3个字节,因此,

ken_len = 45*3 +2(small_int 点两个bytes)....
这个文章,推荐。
About using UTF-8 fields in MySQL