MySQL优化方案

下面从5个角度介绍一下MySQL优化的一些策略。

一、连接配置优化

1.服务端配置

• 增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

• 及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

• 另外，MySQL有非常多的配置参数，并且大部分参数都提供了默认值！完全可以满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。

2.客户端优化

• 尽量减少和服务端建立连接的次数， 使用连接池来复用连接。
• 常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari，前两者用得很少了，后两者目前如日中天。
• 但是需要注意的是连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么？
• 对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。
• Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的

二、架构优化

1.使用缓存

• 系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。
• 如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。
  

2.做读写分离（集群、主从复制）

• 项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。
• 针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。
  

• 使用集群必然面临一个问题，就是多个节点之间怎么保持数据的一致性。毕竟写请求只往master节点上发送了，只有master节点的数据是最新数据，怎么把对master节点的写操作也同步到各个slave节点上呢？
• 这个时候就要用到主从复制技术了
  binlog是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。

• 这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据，单表查询性能差，说的自然也是所有节点性能都差。
• 这时我们可以把单个节点的数据分散到多个节点上进行存储，那就是分库分表。

3.分库分表

• 分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库；如果把单张表作为节点，那就是分表。
• 分库分表可以分成垂直分库、垂直分表、水平分库和水平分表。

3.1.垂直分库

• 在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这就是垂直分库。
• 如下图所示
  

3.2.垂直分表

• 垂直分表就是在单表的基础上垂直切一刀（或几刀），将一个表的多个字短拆成若干个小表，这种操作需要根据具体业务来进行判断，通常会把经常使用的字段（热字段）分成一个表，不经常使用或者不立即使用的字段（冷字段）分成一个表，以此来提升查询速度。
  
• 拿上图举例：通常情况下商品的详情信息都比较长，而且查看商品列表时往往不需要立即展示商品详情（一般都是点击详情按钮才会进行显示），而是会将**商品更重要的信息（价格等）**展示出来，按照这个业务逻辑，我们将原来的商品表做了垂直分表。

3.3.水平分表

• 把单张表的数据按照一定的规则（行内话叫分片规则）保存到多个数据表上，横着给数据表来一刀（或几刀），就是水平分表了。
  
• 水平分表后的效果
  

3.4.水平分库

• 水平分库就是对单个数据库水平切一刀，往往伴会随着水平分表。
  

• 水平分库的效果
  

• 跟垂直分库的区别是

  • 水平分库（Sharding）

    • 水平分库是按照数据行（记录）的方式进行分片。在水平分库中，数据根据某个特定的分片键（如用户ID、时间戳等）被均匀地分布到多个数据库实例中的不同分片中。每个分片负责存储一部分数据行。
    • 水平分库通常适用于数据量特别大的场景，通过将数据分散到多个数据库实例中，提高了系统的扩展性和并发性能。每个分片可以在不同的物理服务器上，各自独立地处理查询请求。

  • 垂直分库（Vertical Partitioning）

    • 垂直分库是按照数据列（字段）的方式进行分片。在垂直分库中，将数据库中的表按照列的逻辑关系进行拆分，将不同的列分布到不同的数据库实例中。通常是根据数据的访问模式、关联性和重要性等因素进行划分。
    • 垂直分库可以将高频访问的列和低频访问的列分别存储在不同的数据库实例中，以提高查询效率和资源利用率。垂直分库通常适用于数据模型复杂、表结构庞大的场景。

  • 总结来说，水平分库是按照数据行进行分片，将数据分散到多个独立的数据库实例中；而垂直分库是按照数据列进行分片，将表的不同列分布到不同的数据库实例中。水平分库适用于大规模数据和高并发场景，而垂直分库适用于复杂数据模型和表结构的优化。在某些情况下，水平分库和垂直分库可以结合使用，以满足特定的业务需求。

4.消息队列削峰

• 通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。
• 这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。
  

三、优化器——SQL分析与优化

• 处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。
• 只要你的语法不出问题，解析器就不会有问题。
  ?- 此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。

1.慢查询优化

• 慢查询就是执行地很慢的查询，但是只有知道MySQL中有哪些语句才算慢查询，我们才能针对性地进行优化。
• 因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态

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

• slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。
• 除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

1.1.配置慢查询日志

有两种打开慢日志的方式：

• 第一种修改配置文件my.cnf，此种修改方式系统重启后依然有效

# 是否开启慢查询日志
slow_query_log=ON
long_query_time=2
slow_query_log_file=/var/lib/mysql/slow.log

• 第二种动态修改参数（重启后失效）

mysql> set @@global.slow_query_log=1;
Query OK, 0 rows affected (0.06 sec)

mysql> set @@global.long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

1.2.慢查询日志分析

• MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：

mysql> SELECT sleep(5);

• 然后我们查询用时最多的1条慢查询：

[root@localhost ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log
 
Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  SELECT sleep(N)

• 其中，
  • Count：表示这个SQL执行的次数
  • Time：表示执行的时间，括号中的是累积时间
  • Locks：表示锁定的时间，括号中的是累积时间
  • Rows：表示返回的记录数，括号中的是累积数
• 更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。

2.查看运行中的线程

我们可以运行show full processlist;查看MySQL中运行的所有线程

其中：

• Id：线程的唯一标志，可以使用Id杀死指定线程
• User：启动这个线程的用户，普通账户只能查看自己的线程
• Host：哪个ip和端口发起的连接
• db：线程操作的数据库
• Command：线程的命令
• Time：操作持续时间，单位秒
• State：线程的状态
• Info：SQL语句的前100个字符

3.查看服务器运行状态

使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。

--- 查看select的次数
mysql> SHOW GLOBAL STATUS LIKE 'com_select';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 168241 |
+---------------+--------+
1 row in set (0.05 sec)

4.查看存储引擎运行信息

• SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；

• 事务的锁等待情况；线程信号量等待；

• 文件IO请求；

• Buffer pool统计信息等等数据。

• 例如：

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2023-08-25 22:03:29 0x7f2f70324700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 8038 srv_idle
srv_master_thread log flush and writes: 8039
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3
OS WAIT ARRAY INFO: signal count 3
RW-shared spins 0, rounds 4, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1795
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421317158082400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
230 OS file reads, 53 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1317085
Log flushed up to   1317085
Pages flushed up to 1317085
Last checkpoint at  1317076
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 103406
Buffer pool size   8191
Free buffers       7956
Database pages     235
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 201, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 235, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1745, Main thread ID=139841881446144, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 9
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

---上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。

5.EXPLAIN执行计划

• 通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？
• MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划
• 所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询…）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么？等等。
• EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。
• 这里在宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节。

6.SQL与索引优化

6.1 SQL优化

SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：

• 使用小表驱动大表；用join改写子查询；or改成union
• 连接查询中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本；被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低
• 大偏移量的limit，先过滤再排序
  针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB）：

-- 1、大偏移量的查询
mysql> SELECT * FROM user_innodb LIMIT 9000000,10;
Empty set (8.18 sec)
 
-- 2、先过滤ID（因为ID使用的是索引），再limit
mysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;
Empty set (0.02 sec)

6.2.索引优化

为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。

四、存储引擎与表结构的优化

1.存储引擎的选择

一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。
建议根据不同的业务选择不同的存储引擎，例如：

• 查询操作、插入操作多的业务表，推荐使用MyISAM；
• 临时表使用Memory；
• 并发数量大、更新多的业务选择使用InnoDB；
• 不知道选啥直接默认。

2.字段优化

字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。

2.1.整数类型

MySQL提供了6种整数类型，分别是tinyint、smallint、mediumint、int、integer、bigint
不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。
例如，是否被删除的标识，建议选用tinyint，而不是bigint。

2.2.字符类型

• 如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；
• 因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。

2.3.非空类型

• 非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。
• 因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。

2.4.尽量少用外键、触发器和视图功能

原因有三个：
降低了可读性，检查代码的同时还得查看数据库的代码；
把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。

2.5.图片、音频、视频存储

不要直接存储大文件，而是要存储大文件的访问地址！！！

2.6 大字段拆分和数据冗余

• 大字段拆分其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *的情况下，列数多和数据量大导致的问题会被严重放大！
• 字段冗余原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。

五、一些别的mysql优化

1.硬件优化

首先，硬件优化是MySQL优化的首要步骤。因为无论配置和查询如何优化，如果硬件不满足要求，性能还是会无法提高。
选择性能更好的硬件，如选专用服务器、SSD固态硬盘、高速内存等硬件可以显著提升MySQL的性能。

2.使用RAID技术

RAID技术即“独立磁盘冗余阵列”，它可以提高MySQL系统的数据安全性和硬盘读写性能。常用的有RAID0、RAID1、RAID5和RAID10，选择不同的RAID技术可以根据实际情况做出相应的选项。

3.合理分配磁盘空间

为MySQL分配合理的磁盘空间，避免磁盘空间使用过度或不足的问题。建议将MySQL安装在一个单独的磁盘分区中，避免与其他应用程序冲突。

4.网络设备优化

与MySQL网络通讯相关的设备和配置需要优化。例如，网络带宽越大，数据传输速度也就越快，一定程度上会提高性能。

5.Mysql本身的配置优化

• 修改MySQL配置参数
• 在MySQL服务器上，有许多配置参数可以调整，比如缓存设置、连接数量、线程池大小、临时文件目录位置等。通过调整这些参数，可以使MySQL运行更加稳定和高效。
• 下面列出一些常见的MySQL配置参数：
  • innodb_buffer_pool_size ：InnoDB缓冲区大小。这个参数通常设置为总内存的70%～80%。
  • key_buffer_size：MyISAM索引缓冲区的大小。
  • max_connections ： MySQL服务器同时处理的最大连接数。
  • query_cache_size：查询缓存大小，通常设置为总内存的5%。

6.数据库、数据表设计调优

数据库设计的好坏能够明显影响MySQL的性能。因此，我们需要在设计数据表时，考虑到实际应用场景，采用合适的数据类型和约束条件、适当的前缀长度以及多表、多列索引等方式，提高查询效率和响应速度。

7.服务器参数调优

服务器参数调优必不可少，可以调整服务器的并发处理能力，提高吞吐量和性能。

• 关闭不需要的服务。禁止不用的网络服务和服务进程，可以减少系统负载和内存消耗。
• 调整Linux内核参数。如果MySQL运行在Linux上，可以调整Linux内核参数，以更好的协调系统资源。
• 使用连接池和利用缓存。