关于java:为什么99的程序员都做不好SQL优化

1. 连贯层

最上层是一些客户端和链接服务，蕴含本地 sock 通信和大多数基于客户端 / 服务端工具实现的相似于 TCP/IP 的通信。次要实现一些相似于连贯解决、受权认证、及相干的平安计划。在该层上引入了线程 池的概念，为通过认证平安接入的客户端提供线程。同样在该层上能够实现基于 SSL 的平安链接。服务 器也会为平安接入的每个客户端验证它所具备的操作权限。

2. 服务层

第二层架构次要实现大多数的外围服务性能，如 SQL 接口，并实现缓存的查问，SQL 的剖析和优化，部 分内置函数的执行。所有跨存储引擎的性能也在这一层实现，如 过程、函数等。在该层，服务器会解 析查问并创立相应的外部解析树，并对其实现相应的优化如确定表的查问的程序，是否利用索引等，最初生成相应的执行操作。如果是 select 语句，服务器还会查问外部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中可能很好的晋升零碎的性能。

3. 引擎层

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通 信。不同的存储引擎具备不同的性能，这样咱们能够依据本人的须要，来选取适合的存储引擎。数据库 中的索引是在存储引擎层实现的。

4. 存储层

数据存储层，次要是将数据 (如: redolog、undolog、数据、索引、二进制日志、谬误日志、查问 日志、慢查问日志等) 存储在文件系统之上，并实现与存储引擎的交互。

和其余数据库相比，MySQL 有点不同凡响，它的架构能够在多种不同场景中利用并施展良好作用。次要 体现在存储引擎上，插件式的存储引擎架构，将查询处理和其余的零碎工作以及数据的存储提取拆散。这种架构能够依据业务的需要和理论须要抉择适合的存储引擎。

存储引擎介绍

大家可能没有据说过存储引擎，然而肯定听过引擎这个词，引擎就是发动机，是一个机器的外围组件。比方，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为外围的组件。而咱们在抉择 引擎的时候，须要在适合的场景，抉择适合的存储引擎，就像在直升机上，咱们不能抉择舰载机的引擎 一样。而对于存储引擎，也是一样，他是 mysql 数据库的外围，咱们也须要在适合的场景抉择适合的存储引 擎。接下来就来介绍一下存储引擎。存储引擎就是存储数据、建设索引、更新 / 查问数据等技术的实现形式。存储引擎是基于表的，而不是 基于库的，所以存储引擎也可被称为表类型。咱们能够在创立表的时候，来指定抉择的存储引擎，如果 没有指定将主动抉择默认的存储引擎。

1. 建表时指定存储引擎

CREATE TABLE 表名(字段 1 字段 1 类型 [ COMMENT 字段 1 正文] ,
    ......
    字段 n 字段 n 类型 [COMMENT 字段 n 正文]
) ENGINE = INNODB [COMMENT 表正文] ;

1. 查问以后数据库反对的存储引擎

SHOW ENGINES;

• 创立表 my_myisam , 并指定 MyISAM 存储引擎

CREATE TABLE my_myisam(
    `id` INT,
    `name` VARCHAR(10)
    )ENGINE = MYISAM;

• 创立表 my_memory , 指定 Memory 存储引擎

CREATE TABLE my_memory(
    `id` INT,
    `name` VARCHAR(10)
    )ENGINE = MEMORY;

存储引擎特点

下面咱们介绍了什么是存储引擎，以及如何在建表时如何指定存储引擎，接下来咱们就来介绍下来下面 重点提到的三种存储引擎 InnoDB、MyISAM、Memory 的特点。

InnoDB

1. 介绍

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 存储引擎。

2. 特点

• DML 操作 遵循 ACID模型，反对 事务；
• 行级锁，进步并发拜访性能；
• 反对 外键 FOREIGN KEY 束缚，保证数据的完整性和正确性；

3. 文件

xxx.ibd：xxx 代表的是表名，innoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结 构（frm- 晚期的、sdi- 新版的）、数据和索引。

参数：innodb_file_per_table

show variables like 'innodb_file_per_table';

如果该参数开启，代表对于 InnoDB 引擎的表，每一张表都对应一个 ibd 文件。咱们间接关上 MySQL 的 数据寄存目录：D:\DevelopTools\mysql-5.7.19-winx64\data，这个目录下有很多文件 夹，不同的文件夹代表不同的数据库，咱们间接关上 frx_db02 文件夹。

能够看到外面有很多的 ibd 文件，每一个 ibd 文件就对应一张表，比方：咱们有一张表 account，就有这样的一个 account.ibd 文件，而在这个 ibd 文件中不仅寄存表构造、数据，还会寄存该表对应的索引信息。而该文件是基于二进制存储的，不能间接基于记事本关上，咱们能够应用 mysql 提供的一个指令 ibd2sdi，通过该指令就能够从 ibd 文件中提取 sdi 信息，而 sdi 数据字典信息中就蕴含该表的表构造。

ibd2sdi account.ibd

针对 MySQL8 无效

4. 逻辑存储构造

• 表空间 : InnoDB 存储引擎逻辑构造的最高层，ibd 文件其实就是表空间文件，在表空间中能够蕴含多个 Segment 段。
• 段 : 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB 中对于段的治理，都是引擎本身实现，不须要人为对其管制，一个段中蕴含多个区。
• 区 : 区是表空间的单元构造，每个区的大小为 1M。默认状况下，InnoDB 存储引擎页大小为 16K，即一个区中一共有 64 个间断的页。
• 页 : 页是组成区的最小单元，页也是 InnoDB 存储引擎磁盘治理的最小单元，每个页的大小默认为 16KB。为了保障页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
• 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行寄存的，在每一行中除了定义表时所指定的字段以外，还蕴含两个暗藏字段(前面会具体介绍)。

MyISAM

1. 介绍

MyISAM 是 MySQL 晚期的默认存储引擎。

2. 特点

不反对事务，不反对外键

反对表锁，不反对行锁

访问速度快

3. 文件

xxx.sdi：存储表构造信息

xxx.MYD: 存储数据

xxx.MYI: 存储索引

Memory

1. 介绍

Memory 引擎的表数据时存储在内存中的，因为受到硬件问题、或断电问题的影响，只能将这些表作为 长期表或缓存应用。

2. 特点

内存寄存

hash 索引（默认）

3. 文件

xxx.sdi：存储表构造信息

区别及特点

存储引擎抉择

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

• InnoDB: 是 Mysql 的默认存储引擎，反对事务、外键。如果利用对事务的完整性有比拟高的要 求，在并发条件下要求数据的一致性，数据操作除了插入和查问之外，还蕴含很多的更新、删除操 作，那么 InnoDB 存储引擎是比拟适合的抉择。
• MyISAM：如果利用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完 整性、并发性要求不是很高，那么抉择这个存储引擎是十分适合的。
• MEMORY：将所有数据保留在内存中，访问速度快，通常用于长期表及缓存。MEMORY 的缺点就是 对表的大小有限度，太大的表无奈缓存在内存中，而且无奈保障数据的安全性。

MySQL InnoDB 引擎

逻辑存储引擎

InnoDB 的逻辑存储构造如下图所示:

1. 表空间

表空间是 InnoDB 存储引擎逻辑构造的最高层，如果用户启用了参数 innodb_file_per_table(在 8.0 版本中默认开启)，则每张表都会有一个表空间（xxx.ibd），一个 mysql 实例能够对应多个表空间，用于存储记录、索引等数据。

2. 段

段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB 是索引组织表，数据段就是 B + 树的叶子节点，索引段即为 B + 树的非叶子节点。段用来治理多个 Extent（区）。

3. 区

区，表空间的单元构造，每个区的大小为 1M。默认状况下，InnoDB 存储引擎页大小为 16K，即一个区中一共有 64 个间断的页。

4. 页

页，是 InnoDB 存储引擎磁盘治理的最小单元，每个页的大小默认为 16KB。为了保障页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

5. 行

行，InnoDB 存储引擎数据是按行进行寄存的。

在行中，默认有两个暗藏字段：

• Trx_id：每次对某条记录进行改变时，都会把对应的事务 id 赋值给 trx_id 暗藏列。
• Roll_pointer：每次对某条引记录进行改变时，都会把旧的版本写入到 undo 日志中，而后这个暗藏列就相当于一个指针，能够通过它来找到该记录批改前的信息。

架构

概述

MySQL5.5 版本开始，默认应用 InnoDB 存储引擎，它善于事务处理，具备解体复原个性，在日常开发中应用十分宽泛。上面是 InnoDB 架构图，左侧为内存构造，右侧为磁盘构造。

内存架构

在左侧的内存构造中，次要分为这么四大块儿：Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。下来介绍一下这四个局部。

1. Buffer Pool

InnoDB 存储引擎基于磁盘文件存储，拜访物理硬盘和在内存中进行拜访，速度相差很大，为了尽可能补救这两者之间的 I / O 效率的差值，就须要把常常应用的数据加载到缓冲池中，防止每次拜访都进行磁盘 I /O。

在 InnoDB 的缓冲池中不仅缓存了索引页和数据页，还蕴含了 undo 页、插入缓存、自适应哈希索引以及 InnoDB 的锁信息等等。

缓冲池 Buffer Pool，是主内存中的一个区域，外面能够缓存磁盘上常常操作的实在数据，在执行增 删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），而后再以肯定频 率刷新到磁盘，从而缩小磁盘 IO，放慢处理速度。

缓冲池以 Page 页为单位，底层采纳链表数据结构治理 Page。依据状态，将 Page 分为三种类型：

• free page：闲暇 page，未被应用。
• clean page：被应用 page，数据没有被批改过。
• dirty page：脏页，被应用 page，数据被批改过，也中数据与磁盘的数据产生了不统一。

在专用服务器上，通常将多达 80%的物理内存调配给缓冲池。参数设置：show variables like ‘innodb_buffer_pool_size’;

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

2. Change Buffer

Change Buffer，更改缓冲区（针对于非惟一二级索引页），在执行 DML 语句时，如果这些数据 Page 没有在 Buffer Pool 中，不会间接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer 中，在将来数据被读取时，再将数据合并复原到 Buffer Pool 中，再将合并后的数据刷新到磁盘中。

Change Buffer 的意义是什么呢?

先来看一幅图，这个是二级索引的结构图：

与汇集索引不同，二级索引通常是非惟一的，并且以绝对随机的程序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘 IO。有了 ChangeBuffer 之后，咱们能够在缓冲池中进行合并解决，缩小磁盘 IO。

3. Adaptive Hash Index

自适应 hash 索引，用于优化对 Buffer Pool 数据的查问。MySQL 的 innoDB 引擎中尽管没有间接反对 hash 索引，然而给咱们提供了一个性能就是这个自适应 hash 索引。因为后面咱们讲到过，hash 索引在进行等值匹配时，个别性能是要高于 B + 树的，因为 hash 索引个别只须要一次 IO 即可，而 B + 树，可能须要几次匹配，所以 hash 索引的效率要高，然而 hash 索引又不适宜做范畴查问、含糊匹配等。

InnoDB 存储引擎会监控对表上各索引页的查问，如果察看到在特定的条件下 hash 索引能够晋升速度，则建设 hash 索引，称之为自适应 hash 索引。

自适应哈希索引，无需人工干预，是零碎依据状况主动实现。

参数：adaptive_hash_index

4. Log Buffer

Log Buffer：日志缓冲区，用来保留要写入到磁盘中的 log 日志数据（redo log、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果须要更新、插入或删除许多行的事务，减少日志缓冲区的大小能够节俭磁盘 I/O。

参数:

innodb_log_buffer_size：缓冲区大小

innodb_flush_log_at_trx_commit：日志刷新到磁盘机会，取值次要蕴含以下三个：

1: 日志在每次事务提交时写入并刷新到磁盘，默认值。

0: 每秒将日志写入并刷新到磁盘一次。

2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

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

磁盘构造

接下来，再来看看 InnoDB 体系结构的左边局部，也就是磁盘构造：

1. System Tablespace

零碎表空间是更改缓冲区的存储区域。如果表是在零碎表空间而不是每个表文件或通用表空间中创立的，它也可能蕴含表和索引数据。(在 MySQL5.x 版本中还蕴含 InnoDB 数据字典、undolog 等)

参数：innodb_data_file_path

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

零碎表空间，默认的文件名叫 ibdata1。

2. File-Per-Table Tablespaces

如果开启了 innodb_file_per_table 开关，则每个表的文件表空间蕴含单个 InnoDB 表的数据和索引，并存储在文件系统上的单个数据文件中。

开关参数：innodb_file_per_table，该参数默认开启。

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

那也就是说，咱们每创立一个表，都会产生一个表空间文件，如图：

3. General Tablespaces

通用表空间，须要通过 CREATE TABLESPACE 语法创立通用表空间，在创立表时，能够指定该表空间。

A. 创立表空间

CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;

mysql> CREATE TABLESPACE ts_itheima ADD DATAFILE 'myitheima.ibd' ENGINE = innodb;
Query OK, 0 rows affected (0.00 sec)

B. 创立表时指定表空间

CREATE TABLE xxx ... TABLESPACE ts_name;

mysql> create table a(id int primary key auto_increment,name varchar(10)) engine=innodb tablespace ts_itheima;
Query OK, 0 rows affected (0.01 sec)

4. Undo Tablespaces

撤销表空间，MySQL 实例在初始化时会主动创立两个默认的 undo 表空间（初始大小 16M），用于存储 undo log 日志。

5. Temporary Tablespaces

InnoDB 应用会话长期表空间和全局长期表空间。存储用户创立的长期表等数据。

6. Doublewrite Buffer Files

双写缓冲区，innoDB 引擎将数据页从 Buffer Pool 刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于零碎异样时复原数据。

7. Redo Log

重做日志，是用来实现事务的持久性。该日志文件由两局部组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）, 前者是在内存中，后者在磁盘中。当事务提交之后会把所有批改信息都会存到该日志中, 用于在刷新脏页到磁盘时, 产生谬误时, 进行数据恢复应用。

以循环形式写入重做日志文件，波及两个文件：

-rw-r-----. 1 mysql mysql  50331648 10 月  2 22:52 ib_logfile0
-rw-r-----. 1 mysql mysql  50331648 10 月  2 22:52 ib_logfile1

后面咱们介绍了 InnoDB 的内存构造，以及磁盘构造，那么内存中咱们所更新的数据，又是如何到磁盘中的呢？此时，就波及到一组后盾线程，接下来，就来介绍一些 InnoDB 中波及到的后盾线程。

后盾线程

在 InnoDB 的后盾线程中，分为 4 类，别离是：Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。

1. Master Thread

外围后盾线程，负责调度其余线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包含脏页的刷新、合并插入缓存、undo 页的回收。

2. IO Thread

在 InnoDB 存储引擎中大量应用了 AIO 来解决 IO 申请, 这样能够极大地提高数据库的性能，而 IOThread 次要负责这些 IO 申请的回调。

咱们能够通过以下的这条指令，查看到 InnoDB 的状态信息，其中就蕴含 IO Thread 信息。

show engine innodb status;

3. Purge Thread

次要用于回收事务曾经提交了的 undo log，在事务提交之后，undo log 可能不必了，就用它来回收。

4. Page Cleaner Thread

帮助 Master Thread 刷新脏页到磁盘的线程，它能够加重 Master Thread 的工作压力，缩小阻塞

本文由 传智教育博学谷狂野架构师 教研团队公布。

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

转载请注明出处！