通过key_len分析联合索引的使用

The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses.

数据库版本及数据表结构如下

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.10-log |
+------------+
1 row in set

mysql> show create table usercasinoresult_11;
+---------------------+-------------------------------------------------+
| Table               | Create Table                                    |
+---------------------+-------------------------------------------------+
| usercasinoresult_11 | CREATE TABLE `usercasinoresult_11` (
  `CasinoID` varchar(64) NOT NULL,
  `UserID` bigint(20) NOT NULL DEFAULT '0',
  `NickName` varchar(45) NOT NULL,
  `LastUpdateTime` datetime NOT NULL,
  `GameSpecified` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`CasinoID`,`UserID`),
  KEY `IX_UserID_LastUpdateTime` (`UserID`,`LastUpdateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------------------+-------------------------------------------------+
1 row in set

View Code

对于联合索引IX_UserID_LastUpdateTime，我们可以计算key_len的最大可能长度为13 bytes(BIGINT NOT NULL(8 bytes)+DATETIME NOT NULL(5 bytes + fractional seconds storage))
两个语句对应的Explain

mysql> explain select CasinoID, unix_timestamp(LastUpdateTime) as LastUpdateTime from usercasinoresult_11 
where UserID = 108375 and  unix_timestamp(LastUpdateTime) < 1500981297 
and unix_timestamp(LastUpdateTime) > 1500376497 
order by LastUpdateTime desc limit 30;

mysql> explain select CasinoID, unix_timestamp(LastUpdateTime) as LastUpdateTime from usercasinoresult_11 
where UserID = 108375 and  LastUpdateTime < '2017-07-25 19:14:57'
and LastUpdateTime > '2017-07-18 19:14:57'
order by LastUpdateTime desc limit 30;

View Code

第1个查询的key_len=8，它仅使用的联合索引中的UserID，从语句中我们也可以看到where条件在联合索引的第2列使用的函数unix_timestamp(LastUpdateTime)，得到UserID后只能Scan所有记录。
第2个查询的key_len=13，它使用了整个联合索引，得到UserID后，再使用联合索引的第2列进行范围查找。