一.分析MySQL服务器当前的状态信息

SHOW SESSION STATUS;

---当前会话下所有语句类型的执行次数

SHOW SESSION STATUS LIKE 'Com_%' ;

---可以了解当前应用是以插入更新为主,还是以查询操作为主，以及各种SQL类型的执行比例大概是多少

SHOW SESSION STATUS LIKE 'InnoDB_rows%';

二.定位执行效率比较低的SQL语句

1.慢查询日志

---查看当前慢查询日志功能是否开启

SHOW VARIABLES LIKE '%slow_query_log%';

---默认为10秒,超过会被记录到日志文件

SHOW VARIABLES LIKE '%slow_query_time%' ;

---测试慢查询日志功能

select SLEEP(11),first_name FROM actor where actor_id = 1; //语句会睡眠11秒

2.查看执行中的任务

---显示当前执行中的SQL语句

SHOW PROCESSLIST;

---睡眠100秒

select SLEEP(100),first_name FROM actor where actor_id = 1; 

3.通过Desc或者Explain命令查看计划执行

-通过慢查询日志中获取到慢的SQL中，通过语句查看执行计划

-两条语句的执行结构是一样的

DESC SELECT * FROM film_actor WHERE film_id = 1;

EXPLAIN SELECT * FROM film_actor WHERE film_id = 1;

三.最后确定问题并采取相应的优化措施#

# 慢查询分析

---开启profiling看到流程慢在哪

set profiling=ON;
show variables like 'profiling';

-然后正常执行sql语句

-这些SQL语句的执行时间都会被记录下来，此时你想查看有哪些语句被记录下来

show profiles;

| Query_ID | Duration   | Query                                             |

+----------+------------+---------------------------------------------------+

|        1 | 0.06811025 | select * from user where age>=60  

|        2 | 0.00151375 | select * from user where gender = 2 and age = 80

-关注下上面的query_id，比如select * from user where age>=60对应的query_id是1，如果你想查看这条SQL语句的具体耗时，那么可以执行以下的命令

show profile for query 1;

| Sending data         | 0.067701 |

-看出Sending data的耗时最大，这个是指执行器开始查询数据并将数据发送给客户端的耗时，因为我的这张表符合条件的数据有好几万条，所以这块耗时最大，也符合预期。

explain SQL

explain select * from user where age>=60 分析一下

# 走了索引还是很慢

第一种是索引区分度太低，比如网页全路径的url链接，这拿来做索引，一眼看过去全都是同一个域名，如果前缀索引的长度建得不够长，那这走索引跟走全表扫描似的，正确姿势是尽量让索引的区分度更高，比如域名去掉，只拿后面URI部分去做索引。

第二种是索引中匹配到的数据太大，这时候需要关注的是explain里的rows字段了。

它是用于预估这个查询语句需要查的行数的，它不一定完全准确，但可以体现个大概量级。

当它很大时，一般常见的是下面几种情况。

如果这个字段具有唯一的属性，比如电话号码等，一般是不应该有大量重复的，那可能是你代码逻辑出现了大量重复插入的操作，你需要检查下代码逻辑，或者需要加个唯一索引限制下。

如果这个字段下的数据就是会很大，是否需要全部拿？如果不需要，加个limit限制下。如果确实要拿全部，那也不能一次性全拿，今天你数据量小，可能一次取一两万都没啥压力，万一哪天涨到了十万级别，那一次性取就有点吃不消了。你可能需要分批次取，具体操作是先用order by id排序一下，拿到一批数据后取最大id作为下次取数据的起始位置。

# 连接数过小, 受数据库和客户端两侧同时限制

# 数据库连接数过小

show variables like 'max_connections';

set global max_connections= 500;

# 应用侧连接数过小

# buffer pool太小,  128Mb -> 512Mb

show global variables like 'innodb_buffer_pool_size';

set global innodb_buffer_pool_size = 536870912;

# buffer pool的缓存命中率

show status like 'Innodb_buffer_pool_%';

Innodb_buffer_pool_read_requests表示读请求的次数。

Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数。

buffer pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

-一般情况下buffer pool命中率都在99%以上，如果低于这个值，才需要考虑加大innodb buffer pool的大小。