Mysql sql优化步骤

1.查看当前sql执行频率

show status like 'Com_______';

查看全局sql执行频率

show global status like 'Com_______';

 

针对InnoDB引擎查询sql执行效率

show global status like 'Innodb_row_%';

 

2.定位低效率的sql语句

        （1）.慢查询日志

        （2）.查看实时sql执行情况

show processlist;

 3.explain 分析执行计划（常用）

explain select * from table where id=1;

 

(1).explain 之id (表结构的执行顺序)

id值越大，优先级越高；

id值相同，按顺序执行；

id有相同，也又不同，同时存在。id相同可认为是一组，从上往下执行。值越大，优先级越高；

(2).explain之select_type

 (3).explain之table（执行表）

 (4).explain之type

 (5).explain之key

       possible_keys:可能用到的索引

        key：实际使用的索引

        key_len：索引中的长度（并非实际使用长度，在不损失精确性的前提下，长度越短越好）

(6).explain之rows (扫描的行数)

(7).explain之extra（其他的额外的执行计划信息）

 using filesort和using temnporary比较消耗性能，需要考虑sql优化，建立索引

4.show profile 分析 （mysql >5.0.37支持）记录sql执行耗时

查询是否支持 profile

select @@have_profiling;

查询是否开启

select @@profiling;

  默认@@profiling为关闭状态；

设置开启：

set profiling=1;//开启profiling

查看sql执行耗时

show profiles;

 

分析sql执行耗时

show profile for query 1;

 

分析更多耗时情况

show profile all query 1;

5.trace分析优化去执行计划

 开启trace,设置格式为json，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够展示完整。

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

 开启后，执行sql语句：

select * from table where id<=10;

然后检查information_schema.optimizer_trace表，就知道MySQL是如何执行sql的：

select * from information_schema.optimizer_trace\G; //\G为格式化数据展示

注：部分截图来至黑马程序员课件