SQL语句中一个符号引发的“惨案”！
本帖最后由 squall 于 2012-12-19 10:56 编辑表结构和数据如下：DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(6) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
`score` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `IX_score` (`score`),
KEY `IX_class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> select * from student;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
| 1 | a | 1 | 56 |
| 2 | b | 1 | 61 |
| 3 | c | 2 | 78 |
| 4 | d | 2 | 45 |
| 5 | e | 3 | 76 |
| 6 | f | 3 | 89 |
| 7 | g | 4 | 43 |
| 8 | h | 4 | 90 |
+----+------+-------+-------+
8 rows in set (0.00 sec)
执行语句 select * from student where score = 60;
看看执行计划：mysql> explain select * from student where score = 60;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | student | ALL | IX_score | NULL | NULL | NULL | 8 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)没有用到索引？
其中的原因，虽然保存的是数字，但是列设置为了varchar，那么在MySQL中就用不到索引了，加个引号会怎么样呢：mysql> explain select * from student where score = '60' ;
+----+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | student | ref | IX_score | IX_score | 33 | const | 1 | Using index condition |
+----+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)另外，要不就把列类型改成integer，也可以用到索引。
楼主，貌似即使是int类型的字段，加上索引也会比较快点 天津治癫痫病医院排名 http://yyk.familydoctor.com.cn/20336/Content_23134.html
天津最专业癫痫病医院 http://yyk.familydoctor.com.cn/20336/Content_23144.html
北京癫痫病治疗多少钱 http://yyk.familydoctor.com.cn/20336/Content_15957.html
长春市癫痫病专科医院 http://yyk.familydoctor.com.cn/20336/Content_15956.html
癫痫医院排名前十 http://yyk.familydoctor.com.cn/20336/Content_15771.html
北京治疗癫痫的医院在哪里 http://yyk.familydoctor.com.cn/20336/Content_14018.html
北京治疗癫痫最好的医院 http://yyk.familydoctor.com.cn/20336/Content_14612.html
北京癫痫医院排行榜 http://yyk.familydoctor.com.cn/20336/Content_14754.html
北京癫痫医院哪家好 http://yyk.familydoctor.com.cn/20336/Content_14775.html
治癫痫哪个医院好 http://yyk.familydoctor.com.cn/20336/Content_14797.html
页:
[1]