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中。

!

个性比照 :

特点InnoDBMyISAMMEMORYMERGENDB
存储限度64TB有有没有有
事务平安==反对==
锁机制==行锁(适宜高并发)====表锁==表锁表锁行锁
B树索引反对反对反对反对反对
哈希索引反对
全文索引反对(5.6版本之后)反对
集群索引反对
数据索引反对反对反对
索引缓存反对反对反对反对反对
数据可压缩反对
空间应用高低N/A低低
内存应用高低中等低高
批量插入速度低高高高高
反对外键==反对==

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';

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

    SHOW VARIABLES LIKE 'query_cache_size';

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

    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 该参数的可取值有三个 ：

值含意
OFF 或 0查问缓存性能敞开
ON 或 1查问缓存性能关上，SELECT的后果合乎缓存条件即会缓存，否则，不予缓存，显式指定 SQL_NO_CACHE，不予缓存
DEMAND 或 2查问缓存性能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存；其它均不予缓存

在 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 restartShutting 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 ， 能够依据须要进行动静设置。对于须要疾速反馈的业务零碎来说，能够将行锁的等待时间调小，以防止事务长时间挂起； 对于后盾运行的批量处理程序来说， 能够将行锁的等待时间调大， 以防止产生大的回滚操作。

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

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