关于java:性能优化MySQL性能优化之存储引擎调优

1 MySQL 总体架构介绍

1.1 MySQL 总体架构介绍

引言
MySQL 是一个关系型数据库
利用非常宽泛
在学习任何一门常识之前
对其架构有一个概括性的理解是十分重要的
比方索引、sql 是在哪个中央执行的
流程是什么样的
明天咱们就先来学习一下 MySQL 的总体架构

总的来说：MySQL 架构是一个客户端 - 服务器零碎。

MySQL 次要包含以下几局部:

Server 层：次要包含连接器、查问缓存、分析器、优化器、执行器等，所有跨存储引擎的性能都在这一层实现，比方存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

存储引擎： 次要负责数据的存储和读取，采纳能够替换的插件式架构，反对 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有本人的日志模块 redolog 模块。当初最罕用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了

连接器： 身份认证和权限相干(登录 MySQL 的时候)。

查问缓存: 执行查问语句的时候，会先查问缓存（MySQL 8.0 版本后移除，因为这个性能不太实用）mysql 的 server 层减少一层缓存模块，相似一个内存的kv 层，k 是 sql，value 是后果

分析器: 没有命中缓存的话，SQL 语句就会通过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再查看你的 SQL 语句语法是否正确。

优化器： 依照 MySQL 认为最优的计划去执行。

执行器: 执行语句，而后从存储引擎返回数据。

1.2 MySQL 存储引擎介绍

引言
和大多数的数据库不同, MySQL 中有一个存储引擎的概念

针对不同的存储需要能够抉择最优的存储引擎。存储引擎就是存储数据，建设索引，更新查问数据等等技术的实现形式。存储引擎是基于表的，而不是基于库的
所以存储引擎也可被称为表类型。

MySQL 提供了 插件式 的存储引擎架构。所以 MySQL 存在多种存储引擎，能够依据须要应用相应引擎，或者编写存储引擎。

​ MySQL5.0 反对的存储引擎蕴含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等

能够通过指定 show engines，来查问以后数据库反对的存储引擎：

SHOW ENGINES;

表含意:
  - support : 指服务器是否反对该存储引擎
  - transactions : 指存储引擎是否反对事务
  - XA : 指存储引擎是否反对分布式事务处理
  - Savepoints : 指存储引擎是否反对保留点（实现回滚到指定保留点）

• 查看 MySQL 数据库存储引擎配置

  SHOW VARIABLES LIKE '%storage_engine%';

1.2.1 如何更改数据库表引擎

• 建表语句前面退出引擎赋值即可 , 命令举例如下 ,

CREATE TABLE t1(
    id INT ,
    name VARCHAR(20)
) ENGINE = MyISAM;

• 批改已有的表引擎 , 命令举例如下 ,

ALTER TABLE t1 ENGINE = InnoDB;

1.2.2 罕用引擎及其个性比照

• 常见的存储引擎 :

  MyISAM 存储引擎 : 拜访快, 不反对事务和外键。表构造保留在.frm 文件中，表数据保留在.MYD 文件中，索引保留在.MYI 文件中。

  [root@linux-141 itcast]# ll
  -rw-r-----. 1 mysql mysql       8630 9 月  10 16:01 t_account_myisam.frm
  -rw-r-----. 1 mysql mysql         52 9 月  10 16:06 t_account_myisam.MYD
  -rw-r-----. 1 mysql mysql       2048 9 月  10 17:56 t_account_myisam.MYI
  [root@linux-141 itcast]#
  

innoDB 存储引擎(5.5 版本开始默认) : 反对事务 , 占用磁盘空间大 , 反对并发管制。表构造保留在.frm 文件中，如果是共享表空间，数据和索引保留在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，能够是多个文件。如果是多表空间存储，每个表的数据和索引独自保留在 .ibd 中。

[root@linux-141 itcast]# ll
-rw-r-----. 1 mysql mysql       8630 9 月  10 16:02 t_account_innodb.frm
-rw-r-----. 1 mysql mysql      98304 9 月  14 15:50 t_account_innodb.ibd
[root@linux-141 itcast]#

MEMORY 存储引擎 : 内存存储 , 速度快 , 不平安 , 适宜小量快速访问的数据。表构造保留在.frm 中。

!

个性比照 :

1.2.3 如何抉择不同类型的引擎

在抉择存储引擎时，应该依据利用零碎的特点抉择适合的存储引擎。对于简单的利用零碎，还能够依据理论状况抉择多种存储引擎进行组合。

以下是几种罕用的存储引擎的应用环境。

• InnoDB : 是 Mysql 的默认存储引擎，用于事务处理应用程序，反对外键。如果利用对事务的完整性有比拟高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查问以外，还蕴含更新、删除操作，那么 InnoDB 存储引擎是比拟适合的抉择。InnoDB 存储引擎除了无效的升高因为删除和更新导致的锁定，还能够确保事务的残缺提交和回滚，对于相似于计费零碎或者财务零碎等对数据准确性要求比拟高的零碎，InnoDB 是最合适的抉择。
• MyISAM：如果利用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么抉择这个存储引擎是十分适合的。
• MEMORY：将所有数据保留在 RAM 中，在须要疾速定位记录和其余相似数据环境下，能够提供极快的拜访。MEMORY 的缺点就是对表的大小有限度，太大的表无奈缓存在内存中，其次是要确保表的数据能够复原，数据库异样终止后表中的数据是能够复原的。MEMORY 表通常用于更新不太频繁的小表，用以疾速失去拜访后果。

1.3 SQL 的执行流程是什么样的

• 客户端发送一条查问给服务器。
• 服务器先查看查问缓存，如果命中了缓存，则立即返回存储在缓存中的后果。否则进入下一阶段。
• 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行打算。
• MySQL 依据优化器生成的执行打算，再调用存储引擎的 API 来执行查问。
• 将后果返回给客户端。

2 MySQL 存储引擎调优

2.1 MySQL服务器硬件优化

tips

硬件（cpu、内存等）相干

理解即可

对于晋升硬件设施性能：

例如抉择尽量高频率的 内存（频率不能高于主板的反对）、晋升网络带宽、应用 SSD 高速磁盘、晋升 CPU 性能等。

CPU 的抉择:

• 对于数据库并发比拟高的场景，CPU 的数量比频率重要。
• 对于 CPU 密集型场景和频繁执行简单 SQL 的场景，CPU 的频率越高越好

磁盘的抉择

影响数据库最大的性能问题就是磁盘 I /O
为进步数据库的 IOPS 性能，可应用 SSD 或 PCIE-SSD 高速磁盘设施

磁盘 IO 的优化

能够用 RAID 来进行优化

罕用 RAID（磁盘阵列）级别：

RAID0：也称为条带，就是把多个磁盘链接成一个硬盘应用，这个级别 IO 最好
RAID1：也称为镜像，要求至多有两个磁盘，每组磁盘存储的数据雷同
RAID5：也是把多个（起码 3 个）硬盘合并成一个逻辑盘应用，数据读写时会建设奇偶校验信息，并且奇偶校验信息和绝对应的数据别离存储在不同的磁盘上。当 RAID5 的一个磁盘数据产生损坏后，利用剩下的数据和响应的奇偶校验信息去复原被损坏的数据

RAID1+0（倡议应用）：就是 RAID0 和 RAID1 的组合。同时具备两个级别的优缺点，个别倡议数据库应用这个级别。

2.2 MySQL 数据库配置优化

tips：

以下为生产环境中最罕用的 DB 参数配置

• 示意缓冲池字节大小，大的缓冲池能够缩小磁盘 IO 次数。
  innodb_buffer_pool_size = 推荐值为物理内存的 50%~80%。

• 用来管制 redo log buffer 刷新到磁盘的策略。
  innodb_flush_log_at_trx_commit=1

  select @@innodb_flush_log_at_trx_commit;

  0 : 提交事务的时候，不立刻把 redo log buffer 里的数据刷入磁盘文件中，而是依附 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，后果 mysql 宕机了，而后此时内存里的数据全副失落。1 : 提交事务的时候，立刻把 redo log buffer 里的数据刷入磁盘文件中，只有事务提交胜利，那么数据就必然在磁盘里了。2 : 提交事务的时候，把 redo log buffer 日志写入磁盘文件对应的零碎缓存，而不是间接进入磁盘文件，这时可能 1 秒后才会把零碎缓存里的数据写入到磁盘文件。

• 每提交 1 次事务就同步写到磁盘中，能够设置为 1。
  sync_binlog=1

  0：默认值。事务提交后，将二进制日志从缓冲写入操作系统缓冲，然而不进行刷新操作（fsync()），此时只是写入了操作系统缓冲而没有刷新到磁盘，若操作系统宕机则会失落局部二进制日志。1：事务提交后，将二进制文件写入磁盘并立刻执行刷新操作，相当于是同步写入磁盘，不通过操作系统的缓存。N：每写 N 次操作系统缓冲就执行一次刷新操作。

• 脏页占 innodb_buffer_pool_size 的比例，触发刷脏页到磁盘。推荐值为 25%~50%。
  innodb_max_dirty_pages_pct=30

  脏页：内存数据页和磁盘数据页上的内容不统一

• 后盾过程最大 IO 性能指标。
  默认 200，如果 SSD，调整为 5000~20000

  PCIE-SSD 可调整为 5w 左右

  默认：innodb_io_capacity=200

• 指定 innodb 共享表空间文件的大小。
  innodb_data_file_path = ibdata:1G:autoextend：默认 10M，

  个别设置为 1GB

• 慢查问日志的阈值设置，单位秒。
  long_query_time=0.3

  正当设置区间 0.1s~0.5s，

• mysql 复制的模式，row 为 MySQL8.0 的默认模式。
  binlog_format=row

  倡议 binlog 的记录格局为 row 模式

  STATEMENT 模式：每一条会批改数据的 sql 语句都会记录到 binlog 中。ROW 模式：不记录每条 sql 语句的上下文信息，仅需记录哪条数据被批改了，批改成什么样了。MIXED 模式：以上两种模式的混合应用。

• 升高 interactive_timeout、wait_timeout 的值。

  交互等待时间和非交互等待时间，值统一，倡议 300~500s，默认 8 小时

  在用 mysql 客户端对数据库进行操作时，关上终端窗口，如果一段时间（8 小时）没有操作，再次操作时，会报错：以后的连贯曾经断开，须要从新建设连贯

• 数据库最大连接数 max_connections=200

• 过大，实例复原工夫长；过小，造成日志切换频繁。
  innodb_log_file_size= 默认

  redo log 空间大小

• 全量日志倡议敞开。
  默认敞开 general_log=0

  开启 general log 将所有达到 MySQL Server 的 SQL 语句记录下来，general_Log 文件就会产生很大的文件，倡议敞开

2.3 Mysql 中查问缓存优化

tips：

在 MySQL 8.0 之后废除这个性能

原理：简单、实用性不高

作为理解即可

1）查问缓存概述

开启 Mysql 的查问缓存，当执行完全相同的 SQL 语句的时候，服务器就会间接从缓存中读取后果，当数据被批改，之前的缓存会生效，批改比拟频繁的表不适宜做查问缓存。

2）操作流程

回顾

1. 客户端发送一条查问给服务器；
2. 服务器先会查看查问缓存，如果命中了缓存，则立刻返回存储在缓存中的后果。否则进入下一阶段；
3. 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行打算；
4. MySQL 依据优化器生成的执行打算，调用存储引擎的 API 来执行查问；
5. 将后果返回给客户端。

3）查问缓存配置

1. 查看以后的 MySQL 数据库是否反对查问缓存：

   SHOW VARIABLES LIKE 'have_query_cache';    

2. 查看以后 MySQL 是否开启了查问缓存：

   SHOW VARIABLES LIKE 'query_cache_type';

3. 查看查问缓存的占用大小：

   SHOW VARIABLES LIKE 'query_cache_size';

4. 查看查问缓存的状态变量：

   SHOW STATUS LIKE 'Qcache%';

各个变量的含意如下：

| 参数 | 含意 |
| ———————– | ———————————————————— |
| Qcache_free_blocks | 查问缓存中的可用内存块数 |
| Qcache_free_memory | 查问缓存的可用内存量 |
| Qcache_hits | 查问缓存命中数 |
| Qcache_inserts | 增加到查问缓存的查问数 |
| Qcache_lowmen_prunes | 因为内存不足而从查问缓存中删除的查问数 |
| Qcache_not_cached | 非缓存查问的数量（因为 query_cache_type 设置而无奈缓存或未缓存）|
| Qcache_queries_in_cache | 查问缓存中注册的查问数 |
| Qcache_total_blocks | 查问缓存中的块总数 |

4）开启查问缓存

MySQL 的查问缓存默认是敞开的，须要手动配置参数 query_cache_type，来开启查问缓存。query_cache_type 该参数的可取值有三个：

在 my.cnf 配置中，减少以下配置：

# 开启查问缓存
query_cache_type=1

配置结束之后，重启服务既可失效；

而后就能够在命令行执行 SQL 语句进行验证，执行一条比拟耗时的 SQL 语句，而后再多执行几次，查看前面几次的执行工夫；获取通过查看查问缓存的缓存命中数，来断定是否走查问缓存。

-- 执行 SQL 语句进行验证 查问缓存
SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
-- 将 SELECT 批改为小写，发现缓存生效
SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

5）查问缓存 SELECT 选项

能够在 SELECT 语句中指定两个与查问缓存相干的选项：

SQL_CACHE : 如果查问后果是可缓存的，并且 query_cache_type 零碎变量的值为 ON 或 DEMAND，则缓存查问后果。

SQL_NO_CACHE : 服务器不应用查问缓存。它既不查看查问缓存，也不查看后果是否已缓存，也不缓存查问后果。

例子：

SELECT SQL_CACHE id, name FROM xxx;
SELECT SQL_NO_CACHE id, name FROM xxx;

​

6）查问缓存生效的状况

tips

须要留神的问题

1）SQL 语句不统一的状况，要想命中查问缓存，查问的 SQL 语句必须统一。

SQL1 : select count(*) from xxx;
SQL2 : Select count(*) from xxx;

2）当查问语句中有一些不确定的值，则不会缓存。如：now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database()。

SQL1 : select * from xxx where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database();

3）不应用任何表查问语句。

select 'A';

4）查问 mysql，information_schema 或 performance_schema 数据库中的表时，不会走查问缓存。

select * from information_schema.engines;

5）在存储的函数，触发器或事件的主体内执行的查问。

6）如果表更改，则应用该表的所有高速缓存查问都将变为有效并从高速缓存中删除。这包含应用 MERGE 映射到已更改表的表的查问。一个表能够被许多类型的语句，如被扭转 INSERT，UPDATE，DELETE，TRUNCATE TABLE，ALTER TABLE，DROP TABLE，或 DROP DATABASE。

将查问缓存敞开，因为前面还须要进行索引的验证，所以不心愿走查问缓存

[root@linux-141 itcast]# vi /etc/my.cnf
[root@linux-141 itcast]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!

2.4. Mysql 内存治理及优化

1）内存优化准则

1）将尽量多的内存调配给 MySQL 做缓存，但要给操作系统和其余程序预留足够内存。

2）MyISAM 存储引擎的数据文件读取依赖于操作系统本身的 IO 缓存，因而，如果有 MyISAM 表，就要预留更多的内存给操作系统做 IO 缓存。

3）排序区、连接区等缓存是调配给每个数据库会话（session）专用的，其默认值的设置要依据最大连接数正当调配，如果设置太大，岂但浪费资源，而且在并发连贯较高时会导致物理内存耗尽。

2）MyISAM 内存优化

MyISAM 存储引擎应用 key_buffer 缓存索引块，减速 myisam 索引的读写速度。对于 myisam 表的数据块，mysql 没有特地的缓存机制，齐全依赖于操作系统的 IO 缓存。

key_buffer_size

key_buffer_size 决定 MyISAM 索引块缓存区的大小，间接影响到 MyISAM 表的存取效率。能够在 MySQL 参数文件中设置 key_buffer_size 的值，对于个别 MyISAM 数据库，倡议至多将 1 / 4 可用内存调配给 key_buffer_size。

在 my.cnf 中做如下配置：

key_buffer_size=512M

read_buffer_size

如果须要常常程序扫描 MyISAM 表，能够通过增大 read_buffer_size 的值来改善性能。但须要留神的是 read_buffer_size 是每个 session 独占的，如果默认值设置太大，就会造成内存节约。

read_rnd_buffer_size

对于须要做排序的 MyISAM 表的查问，如带有 order by 子句的 sql，适当减少 read_rnd_buffer_size 的值，能够改善此类的 sql 性能。

但须要留神的是 read_rnd_buffer_size 是每个 session 独占的，如果默认值设置太大，就会造成内存节约。

3）InnoDB 内存优化

innodb 用一块内存区做 IO 缓存池，该缓存池不仅用来缓存 innodb 的索引块，而且也用来缓存 innodb 的数据块。

innodb_buffer_pool_size

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保障操作系统及其他程序有足够内存可用的状况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，拜访 InnoDB 表须要的磁盘 I /O 就越少，性能也就越高。

innodb_buffer_pool_size=512M

innodb_log_buffer_size

决定了 innodb 重做日志缓存的大小，对于可能产生大量更新记录的大事务，减少 innodb_log_buffer_size 的大小，能够防止 innodb 在事务提交前就执行不必要的日志写入磁盘操作。

innodb_log_buffer_size=10M

2.5. Mysql 并发参数调整

从实现上来说，MySQL Server 是多线程构造，包含后盾线程和客户服务线程。多线程能够无效利用服务器资源，进步数据库的并发性能。在 Mysql 中，管制并发连贯和线程的主要参数包含 max_connections、back_log、thread_cache_size、table_open_cahce。

1）max_connections

最大可反对的连接数

采纳 max_connections 管制容许连贯到 MySQL 数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且始终增长，则阐明一直有连贯申请因数据库连接数已达到容许最大值而失败，这时能够思考增大 max_connections 的值。

Mysql 最大可反对的连接数，取决于很多因素，包含给定操作系统平台的线程库的品质、内存大小、每个连贯的负荷、CPU 的处理速度，冀望的响应工夫等。在 Linux 平台下，性能好的服务器，反对 500-1000 个连贯不是难事，须要依据服务器性能进行评估设定。

2）back_log

积压申请栈大小

back_log 参数管制 MySQL 监听 TCP 端口时设置的积压申请栈大小。如果 MySql 的连接数达到 max_connections 时，新来的申请将会被存在堆栈中，以期待某一连贯开释资源，该堆栈的数量即 back_log，如果期待连贯的数量超过 back_log，将不被授予连贯资源，将会报错。5.6.6 版本之前默认值为 50，之后的版本默认为 50 +（max_connections / 5），但最大不超过 900。

如果须要数据库在较短的工夫内解决大量连贯申请，能够思考适当增大 back_log 的值。

3）table_open_cache

执行线程可关上表缓存个数

该参数用来管制所有 SQL 语句执行线程可关上表缓存的数量，而在执行 SQL 语句时，每一个 SQL 执行线程至多要关上 1 个表缓存。该参数的值应该依据设置的最大连接数 max_connections 以及每个连贯执行关联查问中波及的表的最大数量来设定：

​ max_connections x N；

4）thread_cache_size

缓存客户服务线程的数量

为了放慢连贯数据库的速度，MySQL 会缓存肯定数量的客户服务线程以备重用，通过参数 thread_cache_size 可管制 MySQL 缓存客户服务线程的数量。

5）lock_wait_timeout

innodb_lock_wait_timeout

事务期待行锁的工夫

该参数是用来设置 InnoDB 事务期待行锁的工夫，默认值是 50ms，能够依据须要进行动静设置。对于须要疾速反馈的业务零碎来说，能够将行锁的等待时间调小，以防止事务长时间挂起；对于后盾运行的批量处理程序来说，能够将行锁的等待时间调大，以防止产生大的回滚操作。

本文由传智教育博学谷 – 狂野架构师教研团队公布，转载请注明出处！

如果本文对您有帮忙，欢送关注和点赞；如果您有任何倡议也可留言评论或私信，您的反对是我保持创作的能源