MYSQL调优之思路----sql语句和索引调优

MySQL数据库性能优化包括综合多方面因素，应根据实际的业务情况制定科学、合理的调优方案进行测试调优

---

MySQL性能优化

1 优化介绍

​在进行优化讲解之前，先请大家记住不要听信你看到的关于优化的“绝对真理”，而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。给大家提供一些优化方面的方向和思路，而具体业务场景的不同，使用的MySQL服务版本不同，都会使得优化方案的制定也不同。

1.2 优化要考虑的问题

注意：优化有风险，涉足需谨慎

2.1 优化可能带来的问题

1、优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统！
2、优化手段有很大的风险，一定要意识到和预见到！
3、任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
4、对于优化来说调优而带来的问题,控制在可接受的范围内才是有成果。
5、保持现状或出现更差的情况都是失败

2.2 优化的需求

1、稳定性和业务可持续性,通常比性能更重要！
2、优化不可避免涉及到变更，变更就有风险！
3、优化使性能变好，维持和变差是等概率事件！
4、优化应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化

2.3 优化由谁参与

​在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与

2.4 优化的方向

在数据库优化上有两个主要方向：即安全与性能。

安全 : 数据安全性
性能 : 数据的高性能访问

2.5 优化的维度

从上图中可以看出，我们把数据库优化分为四个纬度：硬件，系统配置，数据库表结构，SQL及索引

硬件 ：CPU、内存、存储、网络设备等

系统配置： 服务器系统、数据库服务参数等

数据库表结构： 高可用、分库分表、读写分离、存储引擎、表设计等

Sql及索引： sql语句、索引使用等
**从优化成本进行考虑：硬件>系统配置>数据库表结构>SQL及索引
从优化效果进行考虑：硬件<系统配置<数据库表结构<SQL及索引**

1.2数据库使用优化思路

在多数时候，我们进行调优不需要进行这么全面、大范围的调优，一般情况下，我们进行数据库层面的优化就可以了，那我们该如何调优的呢？

应急调优的思路：

针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！

1、show processlist（查看连接session状态）

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  3 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+

2、explain(分析查询计划)，show index from tableName（分析索引）

explain select * from test;

mysql> explain select * from test;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

show index from test;

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

查询执行计划和索引的使用情况从而进行参数的优化

Mysql—explain详解: Mysql—explain详解参考进行相关参数和状态的调优，

3、show status like ‘%lock%’; 查询锁状态

查询库是否有锁	

常规调优的思路：

针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。

1、开启慢查询日志，运行一天
2、查看slowlog，分析slowlog，分析出查询慢的语句。
3、按照一定优先级，进行一个一个的排查所有慢语句。
4、分析top sql，进行explain调试，查看语句执行时间。
5、调整索引或语句本身

MySQl相关日志的详解: MySQL物理文件----日志文件（错误日志、通用查询日志、二进制日志、慢查询日志）