sql语法及效率

1、or还是in

应用场景：从一个表中，根据订单id，查询出n个订单的信息

 

select x1,x2,x3 from y1 where z=n1 or z=n2 or z=n3...

 

 

select x1,x2,x3 from y1 where z in (n1,n2,n3....)

首选是用where in，因为：

 

OR效率：O(n)
IN 效率：O(Log n)
当n很大时，OR会慢很多

注意1：n最好控制在200以内

注意2：最好不要用select *

 

参看：石展的《mysql数据库开发的三十六条军规》

 

2、where语句注意事项

1、where语句里面如果使用or则可能会放弃索引而使用全表搜索

 

解决方案是如果有两个可能性，那么就分别选取然后用UNION ALL连接起来

2、where语句里面如果使用>或者<可能会放弃索引而使用全表搜索

解决方案是如果这个大于或者小于里面只有几个选项，那么一一列出来

如下：

 

$sql = "(SELECT * FROM ".$this->table_name." WHERE SellerId=".$sellerId." AND OrderStatusCode=2 LIMIT ".$offset.",".$limit.") UNION ALL (SELECT * FROM ".$this->table_name." WHERE SellerId=".$sellerId." AND OrderStatusCode=6 LIMIT ".$offset.",".$limit.")";

（注意limit应该放在where语句之后）

 

3、limit对整个联合查询进行限制，应该放在所有语句的最后面

如下：

 

$sql = "(SELECT ".$this->select." FROM ".$this->table_name." WHERE SellerId=? AND OrderStatusCode=2) UNION ALL (SELECT ".$this->select." FROM ".$this->table_name." WHERE SellerId=? AND OrderStatusCode=6) LIMIT ?,?";

 

 

 

 

 

4、order by

 

如：SELECT * FROM mytable WHERE selllerId=? ORDER BY CreateTime DESC LIMIT 20,20

ORDER BY应该放在LIMIT的前面。

 

5、order by的索引优化

MySQL每次只使用一个索引，如果多个字段就要用联合索引，可以将order by作为字段之一

比如：SELECT * FROM mytable WHERE selllerId=? AND itemId=? ORDER BY CreateTime DESC LIMIT 20,20

那么索引就是(sellerId,itemId,CreateTime)

 

6、字段尽量不要允许null

null会增加运算的复杂度。所以应该默认给他一个0或者一个特殊值

 

7、MySQL并没有降序索引

MySQL并没有降序索引DESC，都是升序索引ASC，因此，如果要做一个ORDER BY 降序的话，需要建立的索引是一样的

 

8、理论上来说，只要索引建立，检索的时候升序和降序速度是一样的，但是MyISAM和InnoDB不同

我们常用的是InnoDB，他的反向检索速度是一样的，但是MyISAM的反向检索速度慢很多

 

9、除了上面那一条，MyISAM和InnoDB还有一些主要区别

比如，InnoDB不支持FULLTEXT类型的索引，不保存具体的行数。

但是InnoDB支持事务处理与外键和行级锁，但是MyISAM不支持。

10、where+order by+limit+offset

"SELECT * FROM %1 WHERE CatalogId=%2 order by Id DESC limit %3 offset %4"

 

 

 

 

参考文章：http://www.cnblogs.com/ShaYeBlog/p/3227244.html