大厂都在用的MySQL优化方案（上）

1 SQL优化

通过show status查看各种SQL的执行效率

# 查看本session的sql执行效率
mysql> show status like 'Com_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Com_admin_commands                  | 0     |
...

# 查看全局的统计结果
SHOW GLOBAL STATUS LIKE 'Com_%'
# 查看服务器的状态
show global status;      

结果

• Com_select:执行select操作的次数，依次查询之累加1
• Com_insert:执行insert操作的次数，对于批量插入的insert操作，只累加依次
• Com_update:执行update操作的次数
• Com_delete:执行delete的次数

上面的参数是对所有存储引擎的表进行累计，下面参数是针对InnoDB存储引擎的，累加算法略有不同

• Innodb_rows_read

  SELECT查询返回的行数

• Innodb_rows_insered

  执行inser操作插入的行数

• Innodb_rows_updated

  执行UPDATE操作更新的行数

• Innodb_rows_deleted

  执行DELETE操作删除的行数

通过上述的参数可以了解当前数据库的应用是插入更新为主还是查询操作为主，以及各类的SQL的执行比例是多少。对于更新操作的计算，是对执行次数的计数，无论提交还是回滚都会进行累加对于事务形的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在的问题

• Connections:试图连接MySql服务器的次数
• Uptime：服务器工作时间
• Slow_queries:慢查询的次数

2 定位执行效率低的SQL语句

假设系统经过几个月运行，用户表增长约100万数据量，DBA接到告警，CPU升高，查询越来越慢，如何定位问题并给出解决方案呢？

定位问题的方法

慢查询日志

--log-show-queries[=file_name]

选项去启动

mysqlId写一个包含所有执行时间超过

long_querty_time

秒的sql语句的日志文件

哪些是重点?

• Rank
• Response time
• Rows examine
• min

show processlist

慢查询日志在查询结束后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题。

可使用

show processlist

命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时查看sql的执行情况，同时对一些锁表进行优化。

mysql> show processlist;
+----+-----------------+-----------+-----------------+---------+--------+------------------------+------------------+
| Id | User            | Host      | db              | Command | Time   | State                  | Info             |
+----+-----------------+-----------+-----------------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL            | Daemon  | 242579 | Waiting on empty queue | NULL             |
| 16 | root            | localhost | common_mistakes | Sleep   |    488 |                        | NULL             |
| 17 | root            | localhost | common_mistakes | Query   |      0 | starting               | show processlist |
+----+-----------------+-----------+-----------------+---------+--------+------------------------+------------------+
3 rows in set (0.00 sec)      

监控

一般使用 zabbix

监控3要素:

1、采集间隔

2、指标计算方法，最大值、最小值、平均值

3、数据来源

读出的信息:

1、TPS和延迟异常飙升

2、两指标的危害?持续延迟可能导致高可用失效

3、两指标的依赖关系? TPS推升导致延迟升高

4、推到出什么性能瓶颈? TPS15K时目前架构的性能瓶颈