优化步骤:(定位到需要优化的SQL)
1.查看SQL执行效率
目的:查看数据库是以什么操作为主。
show global status like ‘Com----’; 查看数据库全局的操作的次数,
show global status like ‘Innodb_rows_%’ 针对Innodb存储引擎
2、定位低效率执行SQL
- 慢查询日志
- show proceslist: 查看实时SQL执行情况
3、explain分析执行计划
explain 查询SQL语句的查询计划
索引的使用
索引是数据库优化的常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题
SQL优化
1、大批量插入数据
当使用load命令导入数据的数据时,适当的设置可以提升效率
对Innodb类型的表,可以有以下几种方式提升效率
- 主键顺序插入:
因为Innodb类型的表是按照主键的顺序保存的,所以将导入数据按照主键的顺序排列,可以有效的提高导入数据的效率。 - 关闭唯一性校验:
导入数据之前set unique_checks=0,关闭唯一性校验,导入结束后设为1恢复唯一性校验,可提升导入效率 - 手动提交事务:
导入前关闭自动提交,导入后开启自动提交
2、优化insert语句
- 如需要同时对一张表插入很多行数据时,应该尽量使用多个值表的insert语句,这种方式可以大大缩短客户端与数据库之间的连接、关闭等消耗。使效率比分开执行的单个insert语句块
- 在事务中进行数据插入: 手动开启事务提交
- 数据有序插入:按照主键顺序插入
3、 优化oder by 语句
两种排序方式
- Filesort排序
通过对返回数据进行排序,所有不是通过索引直接返回排序结果的排序都是Filesort排序
-using index
通过索引顺序扫描直接返回有序数据,不需要额外排序,操作效率高,推荐使用
优化目标:尽量减少额外排序,通过索引直接返回排序数据。where条件和order by使用相同的索引并且order by的顺序和索引和索引顺序相同,并且order by的字段都是升序或者都是降序。否则肯定需要额外的操作,这样就会出现Filesort。
Filesort优化
- 两次扫描算法:MySQL4.1之前使用该排序算法
- 一次扫描算法
3、 优化group by 语句
- 加order by null:只分组不排序
- 使用index排序,不使用临时表
3、 优化嵌套语句
- 避免使用子查询,可使用多表联查 替代子查询
3、 优化or 条件
or之间的每个条件列都必须使用到索引,而且不能使用复合索引;如果没有索引,则应该考虑增加索引。