优化成本:硬件>系统配置>数据库表结构>SQL 及索引。
优化效果:硬件<系统配置<数据库表结构<SQL 及索引。
对于MySQL层优化我一般遵从五个原则:
*
减少数据访问:设置合理的字段类型,启用压缩,通过索引访问等减少磁盘 IO。
*
返回更少的数据:只返回需要的字段和数据分页处理,减少磁盘 IO 及网络 IO。
*
减少交互次数:批量 DML 操作,函数存储等减少数据连接次数。
*
减少服务器 CPU 开销:尽量减少数据库排序操作以及全表查询,减少 CPU 内存占用。
*
利用更多资源:使用表分区,可以增加并行操作,更大限度利用 CPU 资源。
总结到 SQL 优化中,就如下三点:
*
最大化利用索引。
*
尽可能避免全表扫描。
*
减少无效数据的查询。
理解 SQL 优化原理 ,首先要搞清楚 SQL 执行顺序。
SELECT 语句,语法顺序如下:
1.SELECT
2.DISTINCT <select_list>
3.FROM <left_table>
4.<join_type> JOIN <right_table>
5.ON <join_condition>
6.WHERE <where_condition>
7.GROUP BY <group_by_list>
8.HAVING <having_condition>
9.ORDER BY <order_by_condition>
10.LIMIT <limit_number>
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 优化策略适用于数据量较大的场景下,如果数据量较小,没必要以此为准,以免画蛇添足。
1.避免不走索引的场景
①尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描
②尽量避免使用 in 和 not in,会导致引擎走全表扫描
③尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描
④尽量避免进行 null 值的判断,会导致数据库引擎放弃索引进行全表扫描
⑤尽量避免在 where 条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描
⑥当数据量大时,避免使用 where 1=1 的条件
⑦查询条件不能用 <> 或者 !=
⑧where 条件仅包含复合索引非前置列
⑨隐式类型转换造成不使用索引
⑩order by 条件要与 where 中条件一致,否则 order by 不会利用索引进行排序
⑪正确使用 hint 优化语句
2.SELECT 语句其他优化
①避免出现 select *
②避免出现不确定结果的函数
③多表关联查询时,小表在前,大表在后
④使用表的别名
⑤用 where 字句替换 HAVING 字句
⑥调整 Where 字句中的连接顺序
3.增删改 DML 语句优化
①大批量插入数据
②适当使用 commit
③避免重复查询更新的数据
④查询优先还是更新(insert、update、delete)优先
4.查询条件优化
①对于复杂的查询,可以使用中间临时表暂存数据
②优化 group by 语句
③优化 join 语句
④优化 union 查询
⑤拆分复杂 SQL 为多个小 SQL,避免大事务
⑥使用 truncate 代替 delete
⑦使用合理的分页方式以提高分页效率
5.建表优化
①在表中建立索引,优先考虑 where、order by 使用到的字段。
②尽量使用数字型字段(如性别,男:1 女:2),若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
③查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段分页进行查询,循环遍历,将结果合并处理进行展示。
④用 varchar/nvarchar 代替 char/nchar。