SQL语句优化
原则
减少数据访问(设置合理字段类型,启用压缩,通过索引减少磁盘IO)
返回更少的数据(只返回需要的字段和数据分页处理,减少磁盘io及网络io)
减少交互次数(批量DML,函数存储等减少数据连接次数)
减少服务器CPU开销(减少数据库排序及全表查询)
利用更多资源(表分区,增加并行)
总结: 最大化利用索引 尽可能避免全表扫描 减少无效数据查询
select执行顺序
FROM
<表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...>
<join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>
SQL优化策略
避免不走索引的场景
尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描(%陈% 》陈%)
尽量避免使用in 和not in,会导致引擎走全表扫描(用between用exists)
尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描(可以用union代替or)
尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描(可以给字段添加默认值0,对0值进行判断)
尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描
当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描(用代码拼装sql时进行判断,没 where 条件就去掉 where,有where条件就加 and。)
查询条件不能用 <> 或者 !=
where条件仅包含复合索引非前置列
隐式类型转换造成不使用索引(select col1 from table where col_varchar=123;)
order by 条件要与where中条件一致,否则order by不会利用索引进行排序
(-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;)
正确使用hint优化语句
SELECT语句其他优化
避免出现select *
避免出现不确定结果的函数:原理上从库复制的是主库执行的语句,使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。
多表关联查询时,小表在前,大表在后。
使用表的别名
用where字句替换HAVING字句:1.HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。2.where和having的区别:where后面不能使用组函数
调整Where字句中的连接顺序