一个联合索引的问题 这个效率是很好的,你也可以explain看看查询计划如果没问题,type应该是const 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 explain,的确用到了这个索引,但是用索引的方式是“range” type 不是 const,而是 range。貌似 range 性能不是最好的。 贴出 show index from ...explain ...以供分析。 我发现,当select索引中包含的列,或者主键时,会使用 到索引。但当查询“非索引列且非主键列”时,explain显示会进行全表扫描。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 |+----+-------------+------------+------+---------------+------+---------+------+------+-------------+| 贴出你的 show index from index_test;怀疑你的表中可能只有几条数据。 的确是的!!!请问这是mysql的一个什么feature啊? 请教一条SQL,结果返回两个统计数字~~~~~~~~~~~ HELP!Ubuntu下MySQL Query Browser无法登陆! 这个sql查询语句怎么写? mysql奇怪的问题!!! 关于创建数据库的问题(在线等) 有没有办法取一个表的建表SQL 如何用PHP存取SQL Server mysql查询后更新前10条数据问题。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。 mysql函数查询返回more than one row 大数据量下更新中文出错,何解? mysql workbench 插入中文问题 MySQL怎么载入文件
type 不是 const,而是 range。貌似 range 性能不是最好的。
explain ...
以供分析。
`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 |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|
怀疑你的表中可能只有几条数据。
请问这是mysql的一个什么feature啊?