SQL优化小结

1.关于select * from t

尽量不要使用 select * from t，用具体的字段列表代替 “*”，不要返回用不到的任何字段；

2.建表时应注意

（1）查询应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引；

（2）尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些；

（3）尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3.好的习惯要养成

（1）in 和 not in 要慎用，能用 between 就不要用 in，否则会导致全表扫描（有网友提醒新版本的in会走索引，待确认）

如：select id from t where num in(1,2,3)对于连续的数值，可改写为：select id from t where num between 1 and 3

（2）大多情况，用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

（3）大多情况，用union all 代替 or 是一个很好的选择。如查询结果集没有去重需求，尽量用union all，不要用union，因为union要进行重复值扫描，效率低。

如：select id from t where num=10 or num=20

可以改为这样查询：select id from t where num=10 union allselect id from t where num=20

4.关于索引使用要注意

（1）在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

（2）尽量选择区分度高的列作为索引（区分度高即字段不重复的比例大）。如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

（3）索引列不能参与计算，保持列“干净”。如：from_unixtime(create_time)='2014-05-29'，索引列create_time将会失效，而且检索时会把所有元素都用函数后才能比较，显然成本太大。

（4）索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑视具体情况而定，一个表的索引数最好不要超过6个。

（5）应避免where子句中  对字段进行null值判断、使用!=或<>操作符、使用 or 来连接条件，这些操作都会导致引擎放弃使用索引而进行全表扫描。

（6）模糊查询，除非必要，否则不要在关键词前加‘%’，否则会导致全表扫描。

如：select id from t where name like '%abc%'，索引将会失效

（7）索引字段类型或字符集不一致也会导致索引失效。

5.关于连接查询

理论上，尽量使用多表连接（join）查询（避免子查询，如果一定使用，把查询条件往子查询中推送）