避免SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。
···
select * from person where lname='x8RJWmQX';
select id from person where lname='x8RJWmQX';
···

image.png

避免在where子句中使用!=或<>操作符

应尽量避免在 where 子句中使用!=或<>操作符，否则引擎放弃使用索引而进行全表扫描。

EXPLAIN select * from person where fname != 'sss’ ;

image.png

尽量避免全表扫描

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

用UNION来代替OR

采用 OR 语句：

select * from person where fname ='LVc1oJjd' or fname='bjRdlVo';

采用 UNION 语句，返回的结果同上面的一样，但是速度要快些：

select * from person where fname ='LVc1oJjd'

Union

select * from person where fname='bjRdlVo';

分别对这两个sql进行explain分析：

OR 语句的结果

image.png

UNION 语句的结果

image.png

我们来比较下重要指标，发现主要差别是 type 和 ref 这两项。type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

UNION 语句的 type 值为 一般为ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距。

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快。

这两项的差距就说明了 UNION 要优于 OR，从我们的直观感觉上也可以理解，虽然这两个方式都用到了索引，但 UNION 是用一个明确的值到索引中查找，目标非常明确，OR 需要对比两个值，目标相对要模糊一些，所以 OR 在恍惚中落后了。

image.png

like语句避免前置百分号

前置百分号会导致索引失效

select * from person where fname like '%LVc1o%' ;

image.png

下面走索引

select * from person where fname like 'LVc1o%' ;

image.png

避免where子句中使用参数

如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

避免在where子句中对字段进行表达式操作

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

避免在where子句中对字段进行函数操作

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’ –name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′)=0 –’2005-11-30′生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

避免无意义查询

不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

用exists代替 in

很多时候用 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)

尽量使用数字型字段

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

使用varchar/nvarchar代替char/nchar

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

大临时表使用select into代替create table

在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

临时表先truncate table，然后drop table

如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

存储过程使用SET NOCOUNT ON

在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送 DONEINPROC 消息。

避免向客户端返回大数据量

尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

避免在 where子句中对字段进行 null 值判断

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

在Mysql5.7版本中该条建议已经不用考虑了，因为null判断也能使用索引了。

image.png