MySql学习笔记（四） - Sql优化

Sql每天都在查，但是sql优化的边界你了解吗？、在一般的认识里数据库就是一个黑箱，我把sql扔进去，它把结果返回来，至于sql优化貌似很遥远的地方，直到系统好慢的时候才会怀疑sql出了毛病。

那么sql优化都有哪些具体的步骤？

1.通过show status命令了解各种sql执行的频率

show session/global status 

可以查询服务器提供的状态信息。其中session表示当前连接，global表示自服务启动至今的统计数据。如果不写默认为session级别。

查询服务启动之后所有操作的统计数量

show global status like ‘Com_%’

这里的Com_xxx表示每个xxx语句执行的次数，我们比较关心的几个统计参数是Com_select、Com_insert、Com_update、Com_delete这些参数的统计会对所有的表类型都会统计，但是如果只想统计InnoDB的话就只需要show global status like ‘innodb_%’即可。通过这些统计值我们可以很容易的得出各种sql的比列是多少，对于事务型的sql，回滚和提交都会进行累计到统计值里。

对于事务型应用，可以使用Com_commit和Com_rollback来了解事务的提交和回滚情况，如果回滚比较多那么就说明应用的逻辑存在问题

此外还有Connections（连接mysql的次数）、uptime（mysql启动的时间）、slow_queries（慢查询的次数）。

2.定位执行效率低下的sql语句

可以通过以下两种方式定位执行效率较低的sql语句。

1.通过慢查询日志定位执行效率较低的sql语句，具体步骤如下：

一、设置需要设置慢查询的数据库

use pymysql;

二、查看慢查询的设置

show variables like 'slow_query%';

三、查看慢查询日志的上限时间

show variables like 'long_query_time'

四、设置慢查询日志为开启状态

set global slow_query_log=’ON’

五、设置慢查询日志文件地址

set global slow_query_log_file='C:\\Users\\Administrator\\Desktop\\man.txt';

六、设置上限时间，有时候设置不生效，需要关闭当前会话重新连接即可

set global long_query_time=0.0005;

所有设置

use pymysql;
show variables like 'slow_query%';
show variables like 'long_query_time';
set global slow_query_log='ON';
set global slow_query_log_file='C:\\Users\\Administrator\\Desktop\\man.txt';
set global long_query_time=0.0005;

2.慢查询日志在查询结束后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前mysql正在执行的线程，包含线程的状态，是否锁表等，可以实时的查看sql执行情况，同时对一些锁表的操作进行优化。

3.通过explain分析低效的sql执行计划

通过上边的慢查询日志，我们就就可以知道那句sql慢了。那么接下来就靠explain来进一步突破sql的具体慢的原因了。

我们在慢查询日志中找到需要分析的sql；

比如：

使用explain进行查看

其中的selectType表示查询的类型，常见的有simple简单表，primary主查询或者最外层查询、union第二个或者后边的查询、subquery子查询的第一个select等。

table表示表名，type表示所需要行的方式，也叫做访问类型：主要有all（全表扫描）、index（索引全扫描）、range（范围）、ref（使用非唯一索引）、eq_ref（唯一索引）、const、ststem（单表最多有一个匹配行，查询起来特别迅速）、NULL等值。以上的访问类型从左到右性能由差到好！

prossible_keys表示可能使用的索引，key表示实际使用的索引，key_len表示使用的索引的长度，rows表示本次查询涉及的记录数，filtered表示最终筛选的记录数，extra表示使用的何种查询方式和相关描述。

partitions表示分区。