亚洲av成人无遮挡网站在线观看,少妇性bbb搡bbb爽爽爽,亚洲av日韩精品久久久久久,兔费看少妇性l交大片免费,无码少妇一区二区三区

<nav id="epdy8"><tfoot id="epdy8"></tfoot></nav>

Chinaunix

標題: 求解，索引選擇與索引長度 [打印本頁]

作者: 龍雪剛 時間: 2012-04-12 15:08
標題: 求解，索引選擇與索引長度
sakila數(shù)據(jù)庫兩張表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                               |
+----+-------------+------------+-------+---------------+---------------------+---------+-----------------------+------+----------------------------------------------+

疑問：對actor表做全索引掃描時為何選擇的是idx_actor_last_name而不是主鍵？為什么key_len長度是137？怎么算出來的。

作者: luodongxin 時間: 2012-04-12 15:26
提示: 作者被禁止或刪除內(nèi)容自動屏蔽

作者: RogerZhuo 時間: 2012-04-12 15:48
回復(fù) 1# 龍雪剛
使勁想了一下

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

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

2 ，長度為何是137呢

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

作者: luodongxin 時間: 2012-04-12 16:29
提示: 作者被禁止或刪除內(nèi)容自動屏蔽

作者: RogerZhuo 時間: 2012-04-12 16:33
回復(fù) 4# 龍雪剛
innodb的主鍵是和表數(shù)據(jù)放在一起（所謂聚集表), 你要讀取所有主鍵值，并且強制查詢走主鍵索引，也就是要掃描全表。
掃描整個表的i/o肯定是大小掃描二級索引的。

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

作者: 龍雪剛 時間: 2012-04-12 16:48

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

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

作者: RogerZhuo 時間: 2012-04-12 17:01
回復(fù) 7# 龍雪剛
你這里要取出所有主鍵，因為主鍵是和表所有數(shù)據(jù)保存在B tree 的葉子節(jié)點的，這樣就要查詢了三。
組合索引使用前半部分也掃描這個組合索引全部（包括未使用列），但是ken-length只會計算前面列的定義長度。

作者: RogerZhuo 時間: 2012-04-13 04:17
這個圖片的結(jié)構(gòu)就很說明問題了，說明為什么只掃描二級索引，還有為何是覆蓋索引，不會回表。

未命名.jpg (75.19 KB, 下載次數(shù): 32)

innodb索引結(jié)構(gòu)

作者: ooooldman 時間: 2012-04-13 13:33

作者: jmyychen 時間: 2012-05-03 12:58
引用

RogerZhuo 發(fā)表于 2012-04-13 04:17
這個圖片的結(jié)構(gòu)就很說明問題了，說明為什么只掃描二級索引，還有為何是覆蓋索引，不會回表。

作者: icer_repls 時間: 2012-05-03 20:28
好吧，我也來說幾句。

第一，LZ在1L問的為什么長度是137，原因是：你的字符集是utf8，每個字符占3個字節(jié)，所以是45*3+2。這個2是因為innodb里面所有的二級索引都包含了主鍵，主鍵是smallint，占2字節(jié)。
那么為什么不選擇走主鍵索引呢？說實話，我剛看你SQL語句也覺得該走主鍵索引，但是既然實際情況沒走，那么肯定就有它自己的原因了，我個人猜想是：1,因為你需要的字段都可以在二級索引idx_actor_last_name里面得到。2，主鍵索引比二級索引大得多，在優(yōu)化器看來還不如直接走二級索引來個掃描，掃描主鍵索引可能需要更多的IO。

第二，LZ在4L說的，你怎么扯出個135了？你強制使用主鍵索引，所以索引長度是2很正常啊，與135有什么關(guān)系？

最后說明的是：mysql的優(yōu)化器做得確實不是很到位，所以又時候沒有預(yù)期的也屬比較正常。另外關(guān)于explain的key_len這一列，我想說明一下，它表示實際使用到的索引長度，比如有一個索引idx_a_b(a,b)，優(yōu)化器在實際執(zhí)行中使用到了idx_a_b的那一部分，那么key_len 就是這部分，而不是這整個索引的長度。還有，如果你的字段允許為空，那么key_len要加1，這一個字節(jié)用來判斷是否為空。

歡迎光臨 Chinaunix (http://72891.cn/)