MySQL性能调优

MySQL性能调优

MySQL性能调优流程从低到高分别为：

1. 查询及索引优化：SQL优化以及索引优化
2. 库表优化：设计优化以及分库分表等操作
3. MySQL参数/服务器优化：分为硬件调优和MySQL参数调优
4. 分库分表横向扩展优化

查询优化

SQL作为访问操作数据库的主要手段，采用一定的优化手段提升SQL性能，对于提高数据库整体性能具有重要意义，下面内容为对于SQL性能优化的学习和总结，主要内容来自：尚硅谷mysql教程，MySQL 8.0 Reference Manual Optimizing SELECT Statements。

SQL性能监控

写出在进行数据库性能优化前首先应通过一些性能监控手段，分析MySQL目前出现性能瓶颈的原因，针对原因采用对应的调优策略，性能监控手段主要包括：

1. 通过show status查看一些记录在全局变量中的性能参数，例如slow_queries，last_query_cost等。
2. 通过慢查询日志定位执行较慢的SQL。
3. 使用show profile查看SQL执行成本。
4. 使用EXPLAIN分析查询语句。
5. Performance Schema 以及 sys Schema两个参数数据库。

由于性能监控脱离实际的应用场景难以加以实践以达到深入了解，下面的总结偏向理论上的了解，在以后遇到实际问题时能够有一定的准备。

慢查询

MySQL会将执行时间缓慢的SQL记录在日志中，即慢查询日志，慢查询日志中的SQL时分析目前系统性能瓶颈的入口之一。

慢查询日志默认关闭，通过修改全局变量slow_query_log开启:

mysql> show variables like '%slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
mysql> show variables like '%slow_query_log%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /var/lib/mysql/hadoop1-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

慢查询SQL的阈值存储在全局变量long_query_time中，默认为10秒

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

通过slow_queries全局变量，即可获得慢查询SQL的数量：

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2     |
+---------------+-------+
1 row in set (0.00 sec)

对于慢查询日志的分析可使用mysqldumpslow工具

Show profiling

通过show profiling查看最近执行sql在不同执行阶段耗费的时间，能够较为详细的展示一条SQL的执行成本，默认关闭：

mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

开启后自动记录最近的几条sql，使用show profiles查看：

mysql> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration   | Query                                   |
+----------+------------+-----------------------------------------+
|        1 | 0.00060375 | select * from customers                 |
|        2 | 0.00121350 | show tables                             |
|        3 | 0.00014175 | show create employees                   |
|        4 | 0.01286675 | show create table employees             |
|        5 | 0.00246975 | select distinct(company) from employees |
+----------+------------+-----------------------------------------+
5 rows in set, 1 warning (0.00 sec)

使用show prfoile for query x查看某条sql的详细执行成本

mysql> show profile for query 5;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000051 |
| Executing hook on transaction  | 0.000003 |
| starting                       | 0.000005 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000026 |
| init                           | 0.000003 |
| System lock                    | 0.000006 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000028 |
| preparing                      | 0.000363 |
| executing                      | 0.001700 |
| end                            | 0.000005 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000212 |
| closing tables                 | 0.000012 |
| freeing items                  | 0.000036 |
| cleaning up                    | 0.000012 |
+--------------------------------+----------+

EXPLAIN分析查询语句

通过EXPLAIN能够查看某个SQL语句的具体执行计划，该执行计划为优化器选择的最优计划，基本使用方式如下：

EXPLAIN 增删改SQL语句

输出的参数含义如下：

• 其中较为重要的有type、Extra

type描述表连接的方式（我理解的是从表中取值的方式），常见的值有

1. system：表中只有一行数据

2. const：查询结果最多有一个匹配结果，一般为主键/唯一索引的等值匹配

   SELECT * FROM tbl_name WHERE primary_key=1;

3. eq_ref：连接时，当前表每条数据基于连接key能够确定引用表中一条数据，即连接的key为主键/唯一索引

   SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column=other_table.column;

4. ref：与eq_ref类似，只是连接/筛选key为普通索引，无法确定一条引用数据

   SELECT * FROM ref_table WHERE key_column=expr;
   SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column=other_table.column;

5. range：索引key上的范围查询： =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()

   SELECT * FROM tbl_name
     WHERE key_column IN (10,20,30);

6. index：遍历扫描整个索引树

   • 当索引为覆盖索引，即不涉及回表操作时，Extra中会提示using Index。

7. ALL：无索引的扫描全部数据。

Extra作为补充信息，补充说明SQL的执行信息，取值较多，下面列举几个常见的值：

1. Using index：不需要回表的，index扫描。

2. Using index condition：索引下推，即首先通过索引判断是否需要扫描所有数据。

   • 索引定义为：INDEX (zipcode, lastname, firstname)

   • SQL基上述index首先在索引上使用WHERE zipcode='95054' AND lastname LIKE '%etrunia%'过滤数据，取得非过滤数据主键后，再访问真正的行，应用AND address LIKE '%Main Street%'

   SELECT * FROM people
     WHERE zipcode='95054'
     AND lastname LIKE '%etrunia%'
     AND address LIKE '%Main Street%';

3. Using filesort/Using temporary：使用文件排序/临时表，这是两种性能较差的SQL提示

Optimizer Trace

optimizer_trace 用来跟踪优化器做出的优化决策，并将追踪结果存储在INFORMATION_SCHEMA .OPTIMIZER_TRACE表中，通过变量optimizer_trace开启：

其中OPTIMIZER_TRACE表属性列为:

1. QUERY：监控的SQL语句
2. TRACE：以json形式存储的追踪数据
3. MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由于追踪信息大小存在限制，该字段存储省略的追踪信息大小
4. INSUFFICIENT_PRIVILEGES：表明用户是否有权限查看 trace，0表示无权限，对应TRACE字段为空

Performance Schema

The Performance Schema is a tool to help a DBA do performance tuning by taking real measurements instead of “wild guesses.”

性能数据库中的表主要用来帮助DBA定位性能问题，一般流程为：

1. 运行用例
2. 查看对应的性能数据表，分析造成性能问题的原因
3. 一旦确定原因，采取对应的操作解决问题
   • 修改系统参数（缓存、内存等）
   • 修改查询语句
   • 修改数据库关系模式(表、索引等)
4. 重复步骤直到完成修复

MySQL 8.0 Reference Manual提供了如何使用Performance Scherma的文档：

1. Using the Performance Schema to Diagnose Problems
2. Performance Schema Quick Start

sys Schema

MySQL 8.0 includes the sys schema, a set of objects that helps DBAs and developers interpret data collected by the Performance Schema

用来帮助理解Performance Schema收集到的性能数据，具体使用查看文档：Chapter 28 MySQL sys Schema

Optimizer的优化策略

1.可用index，但是使用全表扫描

optimizer会根据表大小、查询行数、I/O块大小等因素，确实是否使用索引，若使用索引的成本大于全表扫描的成本，则优化器会选择全表扫描

2. Index Dive

对于多值范围where条件，采用Index Dive估计实际需要访问的行数（通过估测行数确定执行计划的成本，用于后续不同查询计划的成本比较）

where col_name IN(val1, ..., valN)
where col_name = val1 OR ... OR col_name = valN

上述查询根据上index的不同分为两种情况

1. 当对应属性列定义了唯一索引，则对于每个值优化器估计每个值访问行数为1
2. 否则使用index dive或者index statistics即index使用的统计信息，估测当前条件需要的访问行数

index dive 索引下钻，将元组值转化为区间，使用区间内行数作为访问行数的估计

• 例如：in (1, 3, 6)，优化器估计的行数为：(1,3),(3,6)两个区间的行数和
• 相较于基于index statistics的行数估计更加精确，但是由于需要访问索引，引入了额外的IO成本

可通过全局变量eq_range_index_dive_limit设置可使用index dive 的值数量上限

• eq_range_index_dive_limit设置为0，表示关闭index dive

3. Index Merge Optimization

如下语句，对于多个索引上的检索条件求and,or的where子句，MySQL分别在对应索引上执行查询后，对查询结果进行求union,intersection并、交实现查询，该操作称为索引合并Index Merge

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;
SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

Index Merge适用范围限于：

1. 一张表的不同索引列
2. 不适用于全文索引

支持三种算法：

1. Using intersect(...)

2. Using union(...)

   • 1，2要求索引必须为等值查询，且联合索引必须全部覆盖，只能在主键索引上存在范围，即为了保证查询结果的有序性。

3. Using sort_union(...)

   • 3是对2的放宽，不要求等值查询，也不要求全部覆盖联合索引，导致返回结果可能按照索引顺序不满足逐渐顺序。由于求并集去重需要比较主键，此时要求两个集合返回数据是有序的，因为需要排序。

4. Hash Join Optimization

MySQL (8.0.18 and later) 添加的新的连接优化方式，当表连接基于等值连接且对应值上不存在索引时，MySQL会自动采用Hash Join实现连接性能优化，基本步骤如下：

1. 首先扫描小表，构建对应字段->行数据的hash table

2. 遍历大表中的每一条数据，查询hash table获得连接行，生成结果

   mysql> EXPLAIN FORMAT=TREE
       -> SELECT *
       ->     FROM t1
       ->     JOIN t2
       ->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
       ->     JOIN t3
       ->         ON (t2.c1 = t3.c1)\G
   *************************** 1. row ***************************
   EXPLAIN: -> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)
       -> Table scan on t3  (cost=0.35 rows=1)
       -> Hash
           -> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)
               -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
                   -> Table scan on t2  (cost=0.35 rows=1)
                   -> Hash
                       -> Table scan on t1  (cost=0.35 rows=1)

相较于Nested-Loop Join Algorithm和Block Nested-Loop Join Algorithm，Hash Join由于事先需要构建hash表，对于CPU和内存的要求较高

• 通过变量join_buffer_size控制Hash Join所能占用的缓存空间
• 当Hash table size > join_buffer_size 时，MySQL会将使用磁盘上的文件存储溢出内容

简单介绍上面提到的两外两种连接方法

1. Nested-Loop Join Algorithm：直觉上的连接方法，双层嵌套循环，外层遍历表1行，内层遍历表2行，逐个连接判断

   for each row in t1 matching range {
     for each row in t2 matching reference key {
       for each row in t3 {
         if row satisfies join conditions, send to client
       }
     }
   }

2. Block Nested-Loop Join Algorithm：在方法1上的优化，外层循环按照block遍历，每个block中包含多条数据，减少了内层循环数据IO次数， MySQL 8.0.18以后默认使用Hash Join，MySQL 8.0.20不再使用该方法

5. Index Condition Pushdown（ICP）

索引条件下推优化，将索引列上的where检索条件下推到搜索引擎层面，减少检索涉及到的数据行数

• 一般的查询过程：遍历数据项，根据Index获取数据项后，根据数据项内容和where条件判断是否过滤数据。
• 开启ICP后：遍历数据行，根据where条件+index判断数据项是否过滤，若满足where条件，则获取数据项返回。

能够引用ICP的条件为：

1. ICP只能用于range, ref, eq_ref, and ref_or_null 等访问类型
2. 只针对InnoDB和MyISAM表
3. ICP目的为了减少数据行的访问，对于InnoDB来说，只用于次级索引，聚簇索引由于数据已经加载到内存中，没有使用ICP的意义

ICP通过优化器参数参数index_condition_pushdown设置

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

6.ORDER BY Optimization

由于索引中的数据已经排好序，可以基于索引的顺序实现ORDER BY要求返回的排序顺序，如下情况所示：

• 由于二级索引检索后，如果需要非索引上的数据，则需要进行回表操作，引入了额外的访问成本，所以即使在可以使用特定索引避免排序时，优化器如果在会全表扫描+排序的成本比较时，效果较差，不会使用该优化策略。

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

如果无法使用上述优化测试的情况下，MySQL基于filesort对数据进行排序（涉及到在临时磁盘文件上的外部排序），相关控制参数为

• sort_buffer_size：排序时可以用到的缓存大小

7. GROUP BY Optimization

分组操作在没有优化的情况下的执行过程为：扫描整个表，创建一个同组数据相邻的临时表，在此临时表上进行分组聚集操作，其中临时表可能会占用大量的内存，因此提出了基于index的优化手段

1. Loose Index Scan：借助索引的顺序，避免临时表的创建，直接在索引上进行Group By操作，必须满足的条件为：

   • 检索一个表的数据

   • Group by 属性列满足索引最左前缀匹配原则

   • 检所涉及到的值要被索引覆盖，且非Group by 属性列必须为常量

   • 聚集函数只能包括Min(),MAX()，且如果同时应用两个聚集函数，必须指向同一个列

     SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

2. Tight Index Scan：放宽了最左前缀原则，Where key = const+ Group key 满足最左前缀匹配即可

   • 类似于索引下推，首先使用where条件过滤后，再在剩余数据上进行Group By

   # C2 = 'a' 填补了不满足最左前缀匹配
   SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
   SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

另外DISTINCT由于通常与GROUP BY，其优化手段与GROUP BY相同，无GROUP BY的DISTINCT相当于一个组

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

8. LIMIT Query Optimization

如下SQL语句，在没有优化情况下，访问了200万条数据，却只返回了10条数据

select * from stuent where nation = 'cn' limit 2000000,10;

针对LIMIT查询，MySQL有以下几条优化方案：

1. 对于LIMIT限定数据条数较少的情况，查询基于聚簇索引而不是全表扫描
2. 对于需要file sort的LIMIT查询语句，一旦满足LIMIT数量，MySQL不会继续排序
3. LIMIT 0直接返回空数据集，不会执行

库表优化

库表优化可以分为三种类型：

1. 在数据库设计阶段，根据ER建模和范式，设计出合理的数据库表，选择合适的表字段类型
2. 在数据库使用过程中，根据实际需求进行反范式化操作，例如为了方便查询，增加冗余中间表
3. 当表数据量扩张到一定程度，考虑进行表拆分满足性能需求

范式化vs反范式化（私货总结）

范式化本质上是通过拆分表中具有依赖关系的数据列到不同的表中，从而减少数据冗余，降低表空间占用。但是由于查询往往涉及到不同表的关联，一张表的冗余越少，查询涉及到的关联次数越多，导致查询成本较高，因此产生了对应的概念-反范式化，通过在表中增加冗余列，减少查询需要的关联次数。

• 在设计时根据范式化规则，确定数据库表
• 在数据库使用中，根据查询的实际需求，通过反范式化优化数据库查询性能

字段类型选择优化

在选择表字段数据类型时，应选择满足需求的空间占用最小的数据类型，下面列举我了解到的常见的选择策略：

1. 对于既能使用文本类型，又能使用数字类型的字段，使用数字类型，降低存储和比较成本
   • IP地址以数字形式存储，MySQL提供转化数字和ip地址的方法：inet_aton和inet_ntoa
2. 能使用TIMESTAMP，就不使用DATATIME，前者以数字形式存储（4byte），后者以字符串形式存储（8byte）
3. 当存储非负数时，可使用UNSIGNED整数
4. 涉及到浮点数的精确计算时，使用DECIMAL类型

表拆分

1. 读写分离
2. 垂直拆分：分库/垂直分表
3. 水平拆分：水平拆分（客户端绑定：Sharding-JDBC，中间件：MyCat）

主从复制

通过主从复制操作实现主服务器提供读写操作、从服务器只读，通过分散读请求到不同服务器，降低主服务器性能瓶颈，从而提升整体性能。

下面使用两台服务器建立一个简单的主从架构，熟悉主从架构构建过程：

1. 修改配置文件，标识主服务器和从服务器（以及一些其他的配置信息）

   • 主配置

     [mysqld]
     #[必选]主服务器唯一ID
     server-id=1
     #[必选] binlog位置
     log-bin=/var/lib/mysql/my_binlog
     #[可选] 可选的参数
     read_only=0
     # binlog_expire_logs_seconds
     # 指定同步的数据库，默认为所有
     binlog-do-db= test_sync_db
     # 设置binlog格式
     binlog_format=STATEMENT

   • 从配置，server-id不同即可（没有把主服务器IP配置进来有点反直觉）

     # 从服务器ID
     server-id=2
     read-only=1

2. 主服务器上创建从服务器连接使用的用户，并分配对应权限

   mysql> create user 'readonly_slave'@'%' identified by '123456';
   Query OK, 0 rows affected (0.00 sec)
   mysql> grant replication slave on *.* to 'readonly_slave'@'%';
   Query OK, 0 rows affected (0.01 sec)

3. 从服务器配置master信息

   • 配置IP，同步binlog起始点，连接用户名密码等信息

     CHANGE MASTER TO MASTER_HOST='192.168.190.100', MASTER_PORT=3306, MASTER_USER='readonly_slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='my_binlog.000001', MASTER_LOG_POS=701;

   • 通过show master status展示获取主服务器此时的binlog信息

     

4. 刷新权限，并重启服务器，即可看到同步成功

   • 在从服务器上调用show slave status查看是否同步成功

     mysql> show slave status\G
     *************************** 1. row ***************************
                    Slave_IO_State: Waiting for source to send event
                       Master_Host: 192.168.190.100
                       Master_User: readonly_slave
                       Master_Port: 3306
                     Connect_Retry: 60
                   Master_Log_File: my_binlog.000002
               Read_Master_Log_Pos: 891
                    Relay_Log_File: localhost-relay-bin.000005
                     Relay_Log_Pos: 326
             Relay_Master_Log_File: my_binlog.000002
                  Slave_IO_Running: Yes
                 Slave_SQL_Running: Yes
                 	  ...........(省略)
                       Master_UUID: 60f3b167-4f9e-11ed-9e94-000c29502aa1
                  Master_Info_File: mysql.slave_master_info
                         SQL_Delay: 0
               SQL_Remaining_Delay: NULL
           Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates

在配置同步的过程中遇到了不少的问题，下面是简单的记录

1. 普通密码无法通过caching_sha2_password验证的问题

   • 错误提示如下：

     mysql> show slave status\G
     *************************** 1. row ***************************
                    Slave_IO_State: Connecting to source
                 	  ...........(省略)
                     Last_IO_Error: error connecting to master 'readonly_slave@192.168.190.100:3306' - retry-time: 60 retries: 2 message: Authenon plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

   • 原因：MySQL8.0 将默认身份验证的插件从mysql_native_password换为了caching_sha2_password

   • 解决方案：修改密码类型

     mysql> alter user 'readonly_slave'@'%' identified with sha256_password by '123456';
     Query OK, 0 rows affected (0.01 sec)

2. 出现问题

   • 错误提示如下：

     mysql> show slave status\G
     *************************** 1. row ***************************
                    Slave_IO_State: Waiting for source to send event
                    ...........(省略)
                    Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 faxecuting transaction 'ANONYMOUS' at master log my_binlog.000002, end_log_pos 891. See error log and/or performance_schema.replication_applitus_by_worker table for more details about this failure or others, if any.
     mysql> select * from performance_schema.replication_applier_status_by_worker\G
     *************************** 1. row ***************************
                    ...........(省略)
     		LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log my_binlog.000002, g_pos 891; Error 'Table 'test_table' already exists' on query. Default database: 'test_sync_db'. Query: 'create table test_table(id int)'

   • 原因：我之前已经在从库中创建了对应的table，同步执行create table test_table时，创建失败导致无法失败

   • 解决方案

     • 简单方式：删除从库中的对应表

     • 复杂方式：重新设置同步，将同步binlog位置设置到binlog末尾位置，跳过创建表步骤

       # 相关命令
       stop slave;
       reset slave;

简单总结：

1. 主从同步对于主服务器来说相当于增加了一个特殊的客户端（从库），该客户端访问的是binlog
2. 对于statement格式的binlog，主从同步就是简单的SQL重放，因此在从库上执行写入操作会导致主从不一致，但不一定会导致同步失败，只有在重放失败的情况（遇到的问题2）下才会导致同步失败

binlog补充

binlog用来记录数据库中的修改操作，例如修改数据项，创建表等操作，因此并不关心例如select，show等不会修改数据的检索语句，按照记录信息的不同分为：

1. --binlog-format=STATEMENT：statement-based logging，基于SQL语句的binary log，主从复制在涉及到非确定性SQL语句（例如：now()，uuid()）时，可能出现数据不一致问题。
2. --binlog-format=ROW（默认）： row-based logging，日志记录每一行数据如何被修改，相较于STATEMENT，粒度更细，规避了不一致的问题，但是会导致日志量较大。
3. --binlog-format=MIXED：混合模式，上述两种模式的组合，当MySQL能够保证操作的确定性时，采用statement-based logging，无法保证时则会采用row-based logging。

binlog有两个主要的应用场景：

1. 主从复制
2. 数据恢复

类似于redolog，binlog产生伴随着事务中的语句执行，不断的写入binlog cache，直到事务提交，binlog是否刷入磁盘由sync_binlog参数控制。

由于redo log控制主服务器的持久性，binlog 控制从服务器与主服务器的同步性，当redo log提交成功，binlog提交失败或者相反时，就会导致主从服务器之间的不一致问题，对此解决方案为-两阶段提交：

• 在事务提交前，首先将redo log写入磁盘，此时为预提交状态
• 将binlog写入磁盘，写入完成后，提交事务

总结

综上所述，MySQL主从同步原理并不复杂，反而是binlog的实现值得深入研究，因为需要保证主从之间的一致性。