避免在sql的where中对字段进行转换(函数转换/操作符转换/隐式转换)------索引会失效从而导致慢查询

版权声明:本文为博主原创文章,转载时请务必注明本文地址, 禁止用于任何商业用途, 否则会用法律维权。 https://blog.csdn.net/stpeace/article/details/78585005

        看例子:

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也能获知结果。



       要避免, 杜绝。



展开阅读全文

没有更多推荐了,返回首页