避免 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
来源: http://www.jianshu.com/p/b72359aba709