优化SQL语句的一般步骤:
1. SHOW status like 'com_%';命令了解各种SQL的执行频率
Com_xxx 表示每个xxx语句执行的次数。
例如:
Com_select
Com_insert
Com_update
Com_delete
后面的值表示执行的次数
Com_commit
Com_rollback
可以了解事务提交和了解的情况
Connections -----视图连接mysql数据库的次数
Uptime ----服务器工作时间以秒为单位
Slow_queries ----慢查询次数
2.定位执行效率较低的SQL语句
》通过慢日志进行查看
》show PROCESSLIST;命令查看当前mysql在进行的线程
3.通过explain分析低效SQL的执行计划
id: 1 ----》
select_type: SIMPLE ----》表示select的类型:SIMPLE简单表 primary主查询
table: users ----》输出结果集的表
partitions: NULL ----》
type: ALL ----》访问类型 ALL:全表扫描 index:索引全扫描 range:索引范围扫描 ref:使用非唯一索引扫描 eq_ref:唯一索引扫描 const/system:主键或唯一索引进行的查询 null:不用访问表或索引,直接能得到的结果
possible_keys: NULL ----》查询时可能用到的索引
key: NULL ----》实际使用的索引
key_len: NULL ----》使用到索引字段的长度
ref: NULL ----》
rows: 28567 ----》扫描行的数量
filtered: 100.00 ----》
Extra: NULL ----》执行情况的说明和描述
4.EXPLAIN EXTENDED;再执行show WARNINGS命令 可以迅速获取清晰易读的SQL语句
5.通过show profile分析SQL P262
》SELECT @@have_profiling;是否支持profile
》SELECT @@profiling;是否开启
》set profiling=1;开启profiling
6.通过trace分析优化器如何选择执行计划 P266
---------------------------------------------------
索引是数据库优化中最常用也是最重要的手段之一。P272
两个简单实用的优化方法:
》定期分析表和检查表
分析表:
analyze table 表名;
检查表:
check table 表名;
》定期优化表
optimize table 表名;将表中的空间碎片进行合并,消除空间浪费
-----------------------------------------------------
常用的SQL优化:
》大批量插入数据
MyISAM存储引擎的表 P287
InnoDB存储引擎的表:
》将导入的数据按照主键的顺序排序
》导入前关闭唯一性校检,导入后再恢复 SET UNIQUE_CHECKS=0;
》导入前关闭自动提交,导入后再恢复SET AUTOCOMMIT=0;
》优化INSERT语句
》使用多个值表的INSERT语句
》使用INSERT DELAYED语句
》文本文件装载表时,使用load data infile
》优化order by语句
》优化group by语句
》优化嵌套查询(join)
》优化分页查询
》在索引上完成排序分页的操作 SELECT users.id,users.create_time from users INNER JOIN(SELECT id from users ORDER BY id LIMIT 17000,5)b ON users.id=b.id;
》转化成某个位置的查询
》优化数据库对象
》优化表的数据类型
》拆分表
》做中间表