mysql优化-sql语句优化注意事项

1、避免SELECT *
2、避免在where 子句中使用 != 或 <> 操作符
3、尽量避免全表扫描

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

4、用UNION 来代替 OR

采用 UNION 语句，返回的结果同上面的一样，但是速度要快些：
我们来比较下重要指标，发现主要差别是 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 在恍惚中落后了。

5、like 语句避免前置百分号

前置百分号会导致索引失效
select * from person where fname like '%LVc1o%' ;

后置走索引

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

6、 避免where子句中使用参数

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

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

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而
进行全表扫描。如：
select id from t where num/2 =100
应改为:
select id from t where num=100*2

8、避免在 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′

9、 避免无意义查询

不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

10、用 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)

11、尽量使用数字型字段

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

12、使用 varchar/nvarchar 代替 char/nchar

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

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

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

14、临时表先 truncate table ，然后 drop table

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

15、存储过程使用set nocount on

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

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

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

17、避免在 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 判断也能使用索引了。

Join 语句的优化
1、尽可能减少 Join 语句中 Nested Loop 的循环总次数
我的理解是数据量少的放在前边，大的放在后边。

最有效的办法是让驱动表的结果集尽可能地小，“永远用小结果集驱动大结果集”。
比如，当两个表（表 A 和表 B）Join 时，如果表 A 通过 WHERE 条件过滤后有 10 条
记录，而表 B 有 20 条记录。如果选择表 A 作为驱动表，也就是被驱动表的结果集为 20，
那么我们通过 Join 条件对被驱动表（表 B）的比较过滤就会进行 10 次。反之，如果选择
表 B 作为驱动表，则须要进行 20 次对表 A 的比较过滤。

2、优先优化 Nested Loop的内层循环

不仅在数据库的 Join 中应该这样做，实际上在优化程序语言时也有类似的优化原则。
内层循环是循环中执行次数最多的，每次循环节约很少的资源，就能在整个循环中节约很
多的资源

3、 保证 Join 语句中被驱动表的 Join 条件字段已经被索引

其目的正是基于上面两点的考虑，只有让被驱动表的 Join 条件字段被索引了，才能保
证循环中每次查询都能够消耗较少的资源，这也正是内层循环的实际优化方法

4、不要太吝惜 Join Buffer 的设置

当无法保证被驱动表的 Join 条件字段被索引且内存资源充足时，不要太吝惜 Join
Buffer 的设置。在 Join 是 All、Index、range 或 index_merge 类型的特殊情况下，Join Buffer
才能派上用场。在这种情况下，Join Buffer 的大小将对整个 Join 语句的消耗起到非常关键
的作用

5、GROUP BY 关键字优化

1、group by 实质是先排序后分组，遵照索引的最佳左前缀。
2 、 当 无 法 使 用 索 引 列 ， 增 大 max_length_for_sort_data 参 数 的 设 置 + 增 大
sort_buffer_size 参数的设置
3、where 高于 having，能写在 where 限定的条件就不要去 having 去限定了。

6、大数据量的分页优化

使用 limit 进行分页，翻到 10000 多页后效率低。原因在于 limit offset 会逐行查找，
是先查询再跳过。
select * from person limit 999900,100; -- 慢了，大概需要 0.4 秒多

一、 从业务逻辑优化

不允许翻过 100 页，例如百度一般可以翻到 76页左右

二、 技术优化方法1

select * from person where id>999900 limit 100;

这样就非常快，0.001s 左右，因为使用了 id 索引
但这样用有前提，id 是连续的，中间的数据不能删，否则 id 为 999900 的并不是第
999900 个记录。

二、 技术优化方法2

如果必须用 limit offset 查询，就用延迟关联
select id from person limit 999900 ,100;
这样只查询 id 列，实现了索引覆盖，就会很快
select p.* from person p inner join (select id from person limit 999900 ,100) as tmp on
p.id=tmp.id;
通过内连接再获取分页后每条记录的详细信息

7、优化更须要优化的

这个问题须要从对整个系统的影响来考虑。哪个 Query 的优化能给系统整体带来更大
的收益，就更须要优化。一般来说，高并发低消耗的影响 > 低并发高消耗