MySQL优化的步骤详解

步骤一、通过show status 命令了解各种sql执行的效率

show [session|gobal] status

session级别表示统计当前连接的结果。

global级别表示统计自数据上次启动至今的结果。

如果不写级别，默认的是session级别

eg：SHOW GLOBAL STATUS;

主要参数描述

Connections：视图连接mysql服务器的次数

Uptime：服务器工作时间

Slow_queries:慢查询的次数

Com_xxx表示每个xxx语句执行的次数

Com_select 执行select次数

Com_insert 执行insert次数，批量插入时候，只累加1次

Com_update 执行Update操作次数

Com_delete 执行删除操作次数

innodb_rows_xxx类型的参数只对innodb存储引擎有效

解析：通过上面一些参数，可以容易了解当前数据库的应用时插入更新为主还是查询为主，以及执行比例。对应更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。

步骤二：定位执行效率较低的sql语句

通过慢查询日志定位哪些是执行效率的sql语句。用--log-slow-queries[=file_name]选项启动，mysqld写一个包含所有执行超过long_query_time秒的sql语句的日志文件。关于如何定位慢查询可以点解这里（http://www.gxlcms.com/）

步骤三：通过EXPLAIN分析低效率SQL的执行计划

在步骤二中，我们可以查询到低效率的sql语句，在此我们通过explain或desc可以获取mysql执行select信息。

eg:

EXPLAIN 
SELECT 
  t0.* 
FROM
  t3 AS t0
  LEFT JOIN `t2` AS t1 
    ON t0.`id1` = t1.`id1` 
WHERE t0.id1 = 5 ;

执行结果如下：

解析：select_type:表示select类型。常见的取值有SIMPLE（简单表，即不使用连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（union中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
talbe：输出结果集的表。

type:表的连接类型。性能由高到底：system（表中仅有一行）、const（表中最多有一个匹配行）、eq_ref、ref、ref_null、index_merge、unique_subquery、index_subquery、range、idnex等

possible_keys:查询时，可能使用的索引

key:实际使用的索引

key_len:索引字段的长度

rows：扫描行的数量

Extra：执行情况的说明和描述

步骤四：确定问题并采取相应的优化措施

经过上面的步骤，可以确定问题出现的原因，此时我们可以根据情况，采取相应的措施。常见的措施有1.建立相应的索引2.优化sql语句3.分表等。

备注：如果索引正在工作，handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表名增加索引得到的性能改善不高，因为索引并不经常使用。handler_read_rnd_next值高则意味着查询运行低效，并且应该建立索引补救。如果正在进行大量的表扫描，handler_read_rnd_next值较高，则通常说明表索引不正确或写入的查询没有利用索引，如下图。

我们应当定期分析表盒检查表

检查表使用如下命令（检查t3表）

ANALYZE TABLE t3;
CHECK TABLE t3;

定期优化表使用命令如下

optimize table 表名

常见语句优化

1.优化Insert语句

（1）如果从同一个客户端插入数据，尽力使用多个字表的insert语句和多行插入，减少单行插入，这种方式大大减少客户端与数据库直接的连接、关闭等消耗。eg：

INSERT INTO t3 VALUES(1,2),(8,5),(6,5),(4,3)

（2）如果从不同的客户插入很多行，能通过使用inset delayed语句得到更高的速度。

（3）如果进行批量插入，可以增加bulk_insert_buffer_size变量方法，提高速度。

2.优化group by语句

默认情况下使用group by col1,col2....会对查询进行相应的排序，如果用户想要避免排序结果的消耗，可以指定order by null 禁止排序。通过查询结果中extra字段可以看出：

3.优化order by语句

在某些情况中，mysql可以使用一个索引来满足order by子句，而不需要额外的排序，where条件和order by使用相同的索引。

4.优化含有or语句

对于含有or的查询子句，如果要使用索引，则or之间的每个条件列必须用到索引；否则，应该考虑添加索引。

5.使用sql提示

以上就是MySQL优化的步骤详解的内容，更多相关内容请关注PHP中文网（www.gxlcms.com）！