查看explain中的key_len判斷究竟使用了哪個索引？
在一張表里有多個索引 , 我們where字段里條件有多個索引的值 , 那么究竟使用的哪個呢?
推薦：《mysql視頻教程》
我們可以使用explain來查看 , 其中的key_len字段可以看得出來
?
比如下面這條sql
explain?select?*?from?ent_calendar_diary?where?email='xxxx'?and?diary_id=1784; ? +----+-------------+--------------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+ |?id?|?select_type?|?table??????????????|?partitions?|?type??|?possible_keys???????????|?key?????|?key_len?|?ref???|?rows?|?filtered?|?Extra?| +----+-------------+--------------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+ |??1?|?SIMPLE??????|?ent_calendar_diary?|?NULL???????|?const?|?PRIMARY,idx_email_stime?|?PRIMARY?|?4???????|?const?|????1?|???100.00?|?NULL??| +----+-------------+--------------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+
?
?
possible_keys里面有兩個索引字段 , 但是看key_len? 是4個字節?
備注，key_len 只指示了WHERE中用于條件過濾時被選中的索引列，是不包含 ORDER BY/GROUP BY?
int類型并且not null 是4個字節 , 因此上面的sql是使用的主鍵索引
?
explain?select?*?from?ent_calendar_diary?where?email='xxxx'; +----+-------------+--------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+ |?id?|?select_type?|?table??????????????|?partitions?|?type?|?possible_keys???|?key?????????????|?key_len?|?ref???|?rows?|?filtered?|?Extra?| +----+-------------+--------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+ |??1?|?SIMPLE??????|?ent_calendar_diary?|?NULL???????|?ref??|?idx_email_stime?|?idx_email_stime?|?767?????|?const?|??111?|???100.00?|?NULL??| +----+-------------+--------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
?
這個是767個字節 , varchar(255)? not null? ?255 * 3 +2正好符合 , 因此是使用的email那個普通索引
?
CREATE?TABLE?`ent_calendar_diary`?( `diary_id`?int(10)?unsigned?NOT?NULL?AUTO_INCREMENT, `email`?varchar(255)?NOT?NULL, `title`?varchar(100)?NOT?NULL, `summary`?varchar(500)?NOT?NULL?DEFAULT?'', `stime`?bigint(11)?NOT?NULL?DEFAULT?'0', `ctime`?int(10)?unsigned?NOT?NULL?DEFAULT?'0', PRIMARY?KEY?(`diary_id`), KEY?`idx_email_stime`?(`email`,`stime`) )?ENGINE=InnoDB?AUTO_INCREMENT=1809?DEFAULT?CHARSET=utf8
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
