SQL编写技巧:
1.合理使用索引
- 索引少了查询慢
- 索引多了占用空间大,执行增删改查语句的时候需要动态维护索引,影响性能,选择率高(重复值少),且where频繁需要建立B树索引;
- 一般join列需要建立索引;
2. 使用UNION ALL 代替 UNION
- union all 的执行效率比union 高,union执行时需要排重
- union需要对数据进行排序
3.避免 selcet * 写法
- 执行SQL时优化器需要将 * 转成具体的列;
- 每次查询都要回表,不能走覆盖索引;
4.避免复杂SQL 语句
- 提升可阅读性;
- 避免慢查询的概率;
- 可以转换成多个端查询,用业务端处理;
5.避免where 1=1 的写法
6.避免order by rand() 类似的写法
- RAND()导致数据列被多次扫描
SQl 优化
- 利用 EXPLAIN sql 语句查看执行计划
**~~
|id|每个被独立之星的操作标识,表示对象被操作的顺序,id值越大,先被执行,如果相同,执行顺序从上到下|
|select_type |查询中每个select子句的类型 | | table |备操作的对象名称,通常是表名 |
|type|连接操作的类型|
|possible_key|可能用到的索引
|key|优化器实际使用的索引(最重要的列)从最好到最差的连接类型为const、eq_reg、ref、range、index和all。当出现all时表示当前sql出现了“坏味道”
|extra|执行计划的重要补充信息,当此列出现 Using filesort ,Using temporary字样时就要小心了,很肯恶搞SQL语句需要优化
~~ **
优化总结
总结一下完成一段SQL优化的思路与过程:
- 1、查看执行计划 explain
- 2、如果有告警信息,查看告警信息 show warnings;
- 3、查看SQL涉及的表结构和索引信息
- 4、根据执行计划,思考可能的优化点
- 5、按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
- 6、查看优化后的执行时间和执行计划
- 7、如果优化效果不明显,重复第四步操作
补充
- 一般有以下情况都需要优化
- 1.判断SQL是否有问题时可以通过两个表象进行判断:
- 系统级别表象:
- CPU 消耗严重
- IO等待严重
- 页面响应时间过长
- 应用的日志出现超时等错误
- 系统级别表象:
- 可使用 sar 命令,top 命令查看当前系统状态
- SQL 语句表象
- 冗长
- 执行时间过长
- 从全表扫描获取数据
- 执行计划中的rows 、cost很大
冗长的SQL都好理解,一段SQL太长阅读性肯定会差,而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手
例:
执行计划告诉我们本次查询走了全表扫描 Type=ALL ,rows很大(9950400)基本可以判断这是一段"有味道"的SQL。
- SQL 语句表象