56,677
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `index_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`fn` varchar(16) CHARACTER SET latin1 NOT NULL,
`ln` varchar(16) CHARACTER SET latin1 NOT NULL,
`bd` date NOT NULL,
`fn_crc` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fn_ln_bd` (`fn`,`ln`,`bd`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
-------------------------------------------------------------------------------------------------------------------
mysql> explain select id from index_test where fn = 'xx' and ln in ('aa', 'bb', 'cc') and bd = '2012-12-23';
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | index_test | ref | fn_ln_bd | fn_ln_bd | 18 | const | 3 | Using where; Using index |
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from index_test where fn = 'xx' and ln in ('aa', 'bb', 'cc') and bd = '2012-12-23';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | index_test | ALL | fn_ln_bd | NULL | NULL | NULL | 1 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select id, fn, ln, bd from index_test where fn = 'xx' and ln in ('aa', 'bb', 'cc') and bd = '2012-12-23';
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | index_test | ref | fn_ln_bd | fn_ln_bd | 18 | const | 3 | Using where; Using index |
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select fn_crc from index_test where fn = 'xx' and ln in ('aa', 'bb', 'cc') and bd = '2012-12-23';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|