使用show status了解数据库的操作执行频率
通过show [session(当前连接)|global(从上次启动开始)] status like
‘Com_[select/insert/update/delete/commit/rollback]%’;查看当前库走插入多还是查询多
status like ‘Innodb_[rows_read/rows_inserted/rows_update/rows_deleted]%’;查看当前InnoDB引擎操作的累计次数
定位执行效率较低的SQL语句
- 开启记录数据库慢查询日志 应用使用Druid记录慢SQL日志
- 使用show full processlist查看当前MySQL在进行的线程,以及线程的状态、是否表锁,以及SQL的执行情况,同时对锁表情况优化。
- 确定慢SQL后,使用EXPLAN分析SQL执行计划,查询类型(type)是简单表查询(SIMPLE)、主查询(PRIMARY)、连接查询(UNION) 观察SQL的各个步骤的执行顺序
查看SQL的访问类型是否合理 const < eq_ref < ref < range < index<all - 查看是否能用到索引,实际用没有用索引
- 查看Extra额外字段信息描述,是否合理 Extra分析(转)
通过show profile分析SQL
使用select @@have_profiling;查看是否支持profile
开启profiling,set profiling=1;session级别开启profiling的支持
show profiles; 查看所有查询的耗时
show profile for query [query ID];查看单个SQL各个阶段耗时
大批量插入数据
- 在MyISAM导入数据时先DISABLE KEYS,导入之后再ENABLE KEYS打开索引
- 在InnoDB引擎中,按照主键顺序导入
- 在导入数据前SET UNIQUE_CHECKS = 0,关闭唯一性校验,导入结束后SET UNIQUE_CHECKS=1恢复唯一性校验
- 在导入前执行SET AUTOCOMMIT=0,关闭自动提交,导入之后再执行SET AUTOCOMMIT=1
优化INSERT语句
1.使用多值插入代替单个INSERT语句执行
2.通过文件载入表,LOAD DATA INFILE
优化ORDER BY语句
Mysql有两种排序方式
尽量减少额外的排序,通过索引直接返回有序数据
- 通过有序索引顺序扫描,Extra显示Using Index不需要额外排序
- 对返回数据进行排序(文件排序),Extra显示Using filesort不需要额外排序
##可以使用索引
#索引字段排序顺序一致
select * from tablename order by key_part1,key_part2,..
#索引字段与order字段按照索引顺序
select * from tablename where key_part1 = constant order by key_part1 desc,key_part2 desc
#索引字段排序顺序一致
select * from tablename order by key_part1 desc,key_part2 desc
##---------------------------------------------------------------
##不使用索引
#索引字段排序不是相同顺序
select * from tablename order by key_part1 asc,key_part2 desc,..
Mysql文件排序
两次扫描算法:先根据条件取出排序字段和行指针,之后在缓冲器区排序。缓冲区内存不够,在临时表中存储数据进行排序,完成后根据行指针取出数据。
优点:排序内存开销比较小 一次扫描算法: 一次取出索引字段,然后在缓冲区排序后直接返回,排序的内存开销比较大。
通过max_length_for_sort_data的大小和Query语句取出的数据字段大小。
使用group by做分组查询时,可以指定order by null来避免排序
优化嵌套查询
将子查询转换为关联查询,这样减少临时表的的创建
对OR进行优化
对OR修饰的字段分别添加单值索引,Extra:Using union(index_aaa,index_bbb)
分页优化
limit offset,num,Mysql先取出offset+num条记录,再返回offset+1到offset+num条记录
将limit m,n转换为limit n的查询
使用SQL提示
select * from tablename [keywos] where …
- 使用USE INDEX使引擎参考指定索列表
- 使用IGNORE INDEX使引擎忽略指定索引
- 使用FORCE INDEX使引擎强制使用指定索引