Skip to content


[转]关于select … where 的小技巧

大家在写sql 语句的时候, 如果是 select .. where 类型的语句, 有注意到条件的前后顺序吗?我今天做个小实验。
比如查询地址里 包含“海口市”及“振兴路” 两个关键字的数据, 一般时候可能会用
select * from dm_addr where addr like ‘%海口市%’ and addr like ‘%振兴路%’ 的形式,但其实这种查询效率不高,原因在于条件的前后顺序。以下是测试结果

mysql> select count(1) from dm_addr where addr like ‘%振兴路%’ and addr like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 96 |
+———-+
1 row in set (0.82 sec)

mysql> select count(1) from dm_addr where addr like ‘%海口市%’ and addr like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 96 |
+———-+
1 row in set (0.91 sec)

我做了很多次查询
where addr like ‘%海口市%’ and addr like ‘%振兴路%’ 总是比 where addr like ‘%振兴路%’ and addr like ‘%海口市%’
慢,原因是查询是先处理第一条件,然后在处理第二个条件,先查询出所有 包含“海口市”的记录,再在这些记录中查询包含”振兴路” 的记录。 对于地址情况, 包含“海口市”的记录可能大于”振兴路” 的记录,我做测试的表有2万条数据, 已经有大概0.1s的差别。如果碰到大数据,这个差别会更大。
按以上的分析结果, 我们写SQL语句的时候, 对于select …where … 类型的语句,应该把查询结果范围小的条件放在前面,查询范围大的条件放在后面,这样会提高效率。

对于OR的情况:
测试如下:

mysql> select count(1) from dm_addr where addr like ‘%海口市%’ or addr like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 39168 |
+———-+
1 row in set (0.84 sec)
mysql> select count(1) from dm_addr where addr like ‘%振兴路%’ or addr like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 39168 |
+———-+
1 row in set (0.95 sec)

OR语句的处理过程是这样, 对于每一条地址, 逐个分析条件,如果符合第一个条件,就不做第二条件的判断,那么我们应该把容易做判断的条件放在前面, 比如查询地址里 包含“海口市”或“振兴路” 个关键字的数据,按地址一般排法,XX市应该在XX路前。所以如果用第一种方式,那就效率高,判断有海口市就可以确认这行地址有效了。 所以在写OR类型的条件时, 应该把容易判断的条件写在前面。

再做一个NOT … OR .. NOT … 测试,查询不包含 海口市 或者不包含 振兴路 的数据
mysql> select count(1) from dm_addr where addr not like ‘%海口市%’ or addr not like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 38752 |
+———-+
1 row in set (0.92 sec)

mysql> select count(1) from dm_addr where addr not like ‘%振兴路%’ or addr not like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 38752 |
+———-+
1 row in set (0.86 sec)

因为每条数据都有海口市, 所以对于第一个查询,判断第一个条件都失败了, 都需要判断第二个。
对于第二个查询, 只要判断第一个条件为真就可以确定这个记录, 不需要判断第一个条件,
所以第二个查询效率高过第一个。

结论:对于select … where … 类型的语句。
对于OR条件, 需要把命中率高的条件放在前面。
对于AND条件,需要把条件限制范围小的条件放在前面。
希望这些实验能提高phper 的程序效率。以上测试是对于mysql 5.0 win 做的。好象记得ORACLE刚好相反, 是先判断最后的条件。

Posted in Mysql, 技术.


No Responses (yet)

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.



Some HTML is OK

or, reply to this post via trackback.