explain select * from t_user where username like '%胖';
结果:
+----+-------------+--------+-------+---------------+------------+---------+------+------+-----------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+--------+-------+---------------+------------+---------+------+------+-----------------------
| 1 | SIMPLE | t_user | index | NULL | ind_second | 62 | NULL | 5 | Using where; Using index |possible_keys 是可用的索引,key 是用到的索引
possible_keys 为null ,key 就不可能有值呀这是哪里出问题了?
结果:
+----+-------------+--------+-------+---------------+------------+---------+------+------+-----------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+--------+-------+---------------+------------+---------+------+------+-----------------------
| 1 | SIMPLE | t_user | index | NULL | ind_second | 62 | NULL | 5 | Using where; Using index |possible_keys 是可用的索引,key 是用到的索引
possible_keys 为null ,key 就不可能有值呀这是哪里出问题了?
show create table t_user;贴出来看一下。
CREATE TABLE `t_user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(20) NOT NULL,
PRIMARY KEY (`userid`),
KEY `ind_second` (`userName`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 || Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_user | 0 | PRIMARY | 1 | userid | A | 5 | NULL | NULL | | BTREE | |
| t_user | 1 | ind_second | 1 | userName | A | 5 | NULL | NULL | | BTREE | |如果没记错的话where username like '%胖';是不会用到索引的.