看例子:
mysql> show create table tb_test;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_test | CREATE TABLE `tb_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',
`name` varchar(32) NOT NULL COMMENT 'test',
`score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'test',
PRIMARY KEY (`id`),
KEY `id_score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=10005 DEFAULT CHARSET=utf8 COMMENT='测试表, 无实际作用' |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可见, score是索引, 我们继续看:
mysql> select * from tb_test where score = 1;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | n1 | 1 |
+----+------+-------+
1 row in set (0.00 sec)
mysql> select * from tb_test where score + 1 = 2;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | n1 | 1 |
+----+------+-------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------+
| 10061 | 0.00076248 | select * from tb_test where score = 1 |
| 10062 | 0.00316482 | select * from tb_test where score + 1 = 2 |
+----------+------------+---------------------------------------------------+
15 rows in set (0.00 sec)
可以看到, 时间差距较大, 为什么呢? 因为对sql的where字段进行转换(函数转换/操作符转换/隐式转换), 会导致索引失效。来explain看看:
mysql> explain select * from tb_test where score = 1;
+------+-------------+---------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | tb_test | ref | id_score | id_score | 4 | const | 1 | |
+------+-------------+---------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from tb_test where score + 1 = 2;
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | tb_test | ALL | NULL | NULL | NULL | NULL | 10224 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)
看type, 很显然, 后者进行了全表搜索, 也就是索引失效了。 看看 key和rows也能获知结果。
要避免, 杜绝。