MySQL 架构和性能优化

重点：

视图，函数，存储过程，触发器，事件（ 了解 ）

用户管理，密码管理

grant revoke 权限管理

MySQL 架构（ 了解 ）

存储引擎：MyISAM 和 InnoDB 的区别（ 重点 ）

MVCC 工作机制（ 了解 ）

索引 B+Tree 结构

管理索引，创建索引

explain profile

锁机制，事务 ACID 特性

事务隔离级别

事务日志，错误日志

4）MySQL 架构和性能优化

参考：Mysql架构组成和存储引擎介绍_wx5e5f969c34c82的技术博客_51CTO博客

麻雀虽小，五脏俱全。MySQL 虽然以简单著称，但其内部结构并不简单，本节主要介绍 MySQL 的整体架构组成。

学习 MySQL 就好比盖房子，如果想把房子盖的特别高，地基一定要稳，基础一定要牢固。学习 MySQL 数据库前要先了解它的内部结构，这是学好 MySQL 数据库的前提。

MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，可以分为三层，即 MySQL Server 层、存储引擎层和文件系统层。MySQL Server 层又包括连接层和 SQL 层。如下是官方文档中 MySQL 的基础架构图：

MySQL 是 C/S 架构的，connectors是连接器；可供 Native C API、JDBC、ODBC、NET、PHP、Perl、Python、Ruby、Cobol 等连接 MySQL；ODBC 叫开放数据库（系统）互联，open database connection；JDBC 是主要用于 java 语言利用较为底层的驱动连接数据库；以上这些，站在编程角度可以理解为连入数据库管理系统的驱动，站在 MySQL 角度称作专用语言对应的链接器。

任何链接器连入 MySQL 以后，MySQL 是 单进程多线程模型 的，因此，每个用户连接，都会创建一个单独的连接线程；其实 MySQL 连接也有长短连接两种方式，使用 MySQL 客户端连入数据库后，直到使用 quit 命令才退出，可认为是长连接；使用 mysql 中的 -e 选项，在 mysql 客户端向服务器端申请运行一个命令后则立即退出，也就意味着连接会立即断开；所以，mysql 也支持长短连接类似于两种类型；所以，用户连入 mysql 后，创建一个连接线程，完成之后，能够通过这个链接线程完成接收客户端发来的请求，为其处理请求，构建响应报文并发给客户端；由于是单进程模型，就意味着必须要维持一个线程池，跟之前介绍过的 varnish 很接近，需要一个线程池来管理这众多线程是如何对众多客户端的并发请求，完成并发响应的，组件connection pool就是实现这样功能；connection pool 对于 MySQL 而言，它所实现的功能，包括 authentication 认证，用户发来的账号密码是否正确要完成认证功能；thread reuse 线程重用功能，一般当一个用户连接进来以后要用一个线程来响应它，而后当用户退出这个线程有可能并非被销毁，而是把它清理完以后，重新收归到线程池当中的空闲线程中去，以完成所谓的线程重用；

connection limit线程池的大小决定了连接并发数量的上限，例如，最多容纳 100 线程，一旦到达此上限后续到达的连接请求则只能排队或拒绝连接；check memory 用来检测内存，caches 实现线程缓存；整个都属于线程池的功能。当用户请求之后，通过线程池建立一个用户连接，这个线程一直存在，然后用户就通过这个会话，发送对应的 SQL 语句到服务器端。

服务器收到 SQL 语句后，要对语句完成执行，首先要能理解 sql 语句需要有 sql 解释器或叫 sql 接口 sql interface就可理解为是整个 mysql 的外壳，就像 shell 是linux操作系统的外壳一样；用户无论通过哪种链接器发来的基本的 SQL 请求，当然，事实上通过 native C API 也有发过来的不是SQL 请求，而仅仅是对 API 中的传递参数后的调用；不是 SQL 语句不过都统统理解为 sql 语句罢了；对 SQL 而言分为 DDL 和 DML 两种类型，但是无论哪种类型，提交以后必须交给内核，让内核来运行，在这之前必须要告诉内核哪个是命令，哪个是选项，哪些是参数，是否存在语法错误等等；因此，这个整个SQL 接口就是一个完完整整的 sql 命令的解释器，并且这个 sql 接口还有提供完整的 sql 接口应该具备的功能，比如支持所谓过程式编程，支持代码块的实现像存储过程、存储函数，触发器、必要时还要实现部署一个关系型数据库应该具备的基本组件例如视图等等，其实都在 sql interface 这个接口实现的；SQL 接口做完词法分析、句法分析后，要分析语句如何执行让 parser 解析器或分析器实现。

parser是专门的分析器，这个分析器并不是分析语法问题的，语法问题在 sql 接口时就能发现是否有错误了，一个语句没有问题，就要做执行分析，所谓叫查询翻译，把一个查询语句给它转换成对应的能够在本地执行的特定操作；比如说看上去是语句而背后可能是执行的一段二进制指令，这个时候就完成对应的指令，还要根据用户请求的对象，比如某一字段查询内容是否有对应数据的访问权限，或叫对象访问权限；在数据库中库、表、字段、字段中的数据有时都称为 object，叫一个数据库的对象，用户认证的通过，并不意味着就能一定能访问数据库上的所有数据，所以说，mysql 的认证大概分为两过程都要完成，第一是连入时需要认证账号密码是否正确这是authentication，然后，验证成功后用户发来 sql 语句还要验证用户是否有权限获取它期望请求获取的数据；这个称为 object privilege，这一切都是由 parser 分析器进行的。

分析器分析完成之后，可能会生成多个执行树，这意味着为了能够达到访问期望访问到的目的，可能有多条路径都可实现，就像文件系统一样可以使用相对路径也可使用绝对路径；它有多种方式，在多种路径当中一定有一个是最优的，类似路由选择，因此，优化器就要去衡量多个访问路径中哪一个代价或开销是最小的，这个开销的计算要依赖于索引等各种内部组件来进行评估；而且这个评估的只是近似值，同时还要考虑到当前mysql内部在实现资源访问时统计数据，比如，根据判断认为是1号路径的开销最小的，但是众多统计数据表明发往1号路径的访问的资源开销并不小，并且比3号路径大的多，因此，可能会依据3号路径访问；这就是所谓的优化器它负责检查多条路径，每条路径的开销，然后评估开销，这个评估根据内部的静态数据，索引，根域根据动态生成的统计数据来判定每条路径的开销大小，因此这里还有statics；一旦优化完成之后，还要生成统计数据，这就是优化器的作用；如果没有优化器mysql执行语句是最慢的，其实优化还包括一种功能，一旦选择完一条路径后，例如用户给的这个命令执行起来，大概需要100个开销，如果通过改写语句能够达到同样目的可能只需要30个开销；于是，优化器还要试图改写sql语句；所以优化本身还包括查询语句的改写；一旦优化完成，接下来就交给存储引擎完成。

mysql 是插件式存储引擎，它就能够替换使用选择多种不同的引擎，MyISAM是MySQL 经典的存储引擎之一，InnoDB是由 Innobase Oy 公司所开发，2006年五月由甲骨文公司并购提供给MySQL的，NDB主要用于MySQL Cluster 分布式集群环境，archive做归档的等等，还有许多第三方开发的存储引擎；存储引擎负责把具体分析的结果完成对磁盘上文件路径访问的转换，数据库中的行数据都是存储在磁盘块上的，因此存储引擎要把数据库数据映射为磁盘块，并把磁盘块加载至内存中；进程实现数据处理时，是不可能直接访问磁盘上的数据的，因为它没有权限，只有让内核来把它所访问的数据加载至内存中以后，进程在内存中完成修改，由内核再负责把数据存回磁盘；对于文件系统而言，数据的存储都是以磁盘块方式存储的，但是，mysql在实现数据组织时，不完全依赖于磁盘，而是把磁盘块再次组织成更大一级的逻辑单位，类似于lvm中的PE或LE的形式；其实，MySQL的存储引擎在实现数据管理时，也是在文件系统之上布设文件格式，对于文件而言在逻辑层上还会再次组织成一个逻辑单位，这个逻辑单位称为mysql的数据块datablock 一般为16k ，对于关系型数据库，数据是按行存储的；一般一行数据都是存储在一起的，因此，MySQL 在内部有一个datablock，在datablock可能存储一行数据，也可能存放了n行数据；将来在查询加载一行数据时，内核会把整个一个数据数据块加载至内存中，而mysql存储引擎，就从中挑出来某一行返回给查询者，是这样实现的；所以整个存储是以datablock在底层为其最终级别的。

事实上，整个存取过程，尤其是访问比较热点的数据，也不可能每一次当用户访问时或当某SQL语句用到时再临时从磁盘加载到内存中，因此，为了能够加上整个性能，mysql的有些存储引擎可以实现，把频繁访问到的热点数据，统统装入内存，用户访问、修改时直接在内存中操作，只不过周期性的写入磁盘上而已，比如像InnoDB，所以 caches和 buffers组件就是实现此功能的；MySQL为了执行加速，因为它会不断访问数据，而随计算机来说io是最慢的一环，尤其是磁盘io，所以为了加速都载入内存中管理；这就需要MySQL 维护cache和buffer缓存或缓冲；这是由MySQL 服务器自己维护的；有很多存储引擎自己也有cache和buffer一个数据库提供了3种视图，物理视图就是看到的对应的文件系统存储为一个个的文件，MySQL的数据文件类型，常见的有redo log重做日志，undo log撤销日志，data是真正的数据文件，index是索引文件，binary log是二进制日志文件，error log错误日志，query log查询日志，slow query log慢查询日志，在复制架构中还存在中继日志文件，跟二进制属于同种格式；这是mysql数据文件类型，也就是物理视图；逻辑视图这是在mysql接口上通过存储引擎把mysql文件尤其是data文件，给它映射为一个个关系型数据库应该具备组成部分，比如表，一张表在底层是一个数据文件而已，里面组织的就是datablock，最终映射为磁盘上文件系统的 block，然后再次映射为本地扇区的存储，但是整个mysql需要把他们映射成一个二维关系表的形式，需要依赖sql接口以及存储引擎共同实现；所以，把底层数据文件映射成关系型数据库的组件就是逻辑视图；DBA 就是关注内部组件是如何运作的，并且定义、配置其运作模式，而链接器都是终端用户通过链接器的模式进入数据库来访问数据；数据集可能非常大，每一类用户可能只有一部分数据的访问权限，这个时候，最终的终端用户所能访问到的数据集合称作用户视图；

为了保证 MySQL 运作还提供了管理和服务工具，例如:备份恢复工具，安全工具，复制工具，集群服务，管理、配置、迁移、元数据等工具。

4.1）存储引擎

MySQL 中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力，此种技术称为存储引擎，MySQL 支持多种存储引擎其中目前应用最广泛的是 InnoDB和 MyISAM两种。

事务的四大特性

 数据库事务具有 ACID 四大特性。ACID 是以下4个词的缩写：

原子性（atomicity）：事务最小工作单元，要么全成功，要么全失败 。

一致性（consistency）： 事务开始和结束后，数据库的完整性不会被破坏 。

隔离性（isolation）：不同事务之间互不影响，四种隔离级别为 RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）。

持久性（durability）：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失 。

在 InnoDB 中，你可以使用 BEGIN开始一个事务，然后使用 COMMIT提交事务或者 ROLLBACK回滚事务，以确保数据库的一致性和可靠性。

表锁：顾名思义，表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。

行锁：顾名思义，行锁就是一锁锁一行或者多行记录，MySQL 的 行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引。（ 并发性更好 ）

MVCC 概念

参考：https://www.cnblogs.com/qdhxhz/p/15750866.html

官方参考资料

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/storage-engines.html

https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/storage-engines.html

4.1.1）MyISAM 存储引擎

面试题：MyISAM 存储引擎 与InnoDB 存储引擎 的区别

事务支持：MyISAM 不支持事务，InnoDB 支持事务。这意味着 MyISAM 在处理事务时可能存在性能问题，而 InnoDB 可以处理更复杂的事务操作。

锁定机制：MyISAM 使用 表级锁定，而 InnoDB 使用 行级锁定。这使得 InnoDB 在处理大量并发读写操作时更具优势。

崩溃恢复：InnoDB 具有崩溃恢复能力，能够在系统崩溃后恢复数据。而 MyISAM 在系统崩溃后可能会丢失数据。

外键支持：只有 InnoDB 支持外键。这使得 InnoDB 更适合需要维护数据完整性的应用。

存储空间：MyISAM 表存储成三个文件，而 InnoDB 表存储为一个表空间文件。在某些情况下，MyISAM 的存储空间可能会更小。

索引方式：MyISAM 使用非聚集索引，InnoDB 使用聚集索引。这意味着 InnoDB 的索引和数据是存储在一起的，提高了查询效率。

全文搜索：MyISAM 支持全文搜索，而 InnoDB 在早期版本中不支持，但在 MySQL 5.6 及更高版本中通过插件提供了支持。

数据压缩：MyISAM 支持数据压缩，而 InnoDB 在某些情况下可能不支持。

MVCC 支持：InnoDB 支持多版本并发控制（MVCC），这有助于提高并发性能。MyISAM 不支持 MVCC。

数据恢复：对于损坏的数据，MyISAM 可能需要从备份中恢复，而 InnoDB 的恢复能力更强。

综上所述，选择哪种存储引擎取决于具体需求和场景。如果需要事务支持、行级锁定和外键等高级功能，InnoDB 是更好的选择。如果主要关注的是高速检索和全文搜索，并且可以容忍可能的崩溃风险，那么 MyISAM 可能更适合。

MyISAM引擎特点

不支持事务

表级锁定

读写相互阻塞，写入不能读，读时不能写

只缓存索引

不支持外键约束

不支持聚簇索引

读取数据较快，占用资源较少（ 优势 ）–— 因为 MyISAM 功能相对弱

不支持 MVCC（多版本并发控制机制）高并发

崩溃恢复性较差

MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

只读（或者写较少）

表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

tbl_name.frm表格式定义

tbl_name.MYD数据文件

tbl_name.MYI索引文件

4.1.2）InnoDB 引擎（常用 ）

InnoDB引擎特点

行级锁

支持事务，适合处理大量短期事务

读写阻塞与事务隔离级别相关

可缓存数据和索引

支持外键

支持聚簇索引

崩溃恢复性更好

支持 MVCC 高并发

从 MySQL5.5 后支持全文索引

从 MySQL5.5.5 开始为 默认的数据库引擎

InnoDB 数据库文件

所有 InnoDB 表的数据和索引放置于同一个表空间中

数据文件: ibdata1, ibdata2, 存放在 datadir 定义的目录下

表格式定义: tb_name.frm, 存放在 datadir 定义的每个数据库对应的目录下

每个表单独使用一个表空间存储表的数据和索引

两类文件放在对应每个数据库独立目录中

数据文件(存储数据和索引): tb_name.ibd

表格式定义: tb_name.frm

启用：innodb_file_per_table=ON（ MariaDB 5.5 以后版是默认值）

参看：InnoDB System Variables – MariaDB Knowledge Base

4.1.3）其它存储引擎

// 默认使用 PERFORMANCE_SCHEMA 存储引擎 ( performance_schema 数据库 )

show table status from performance_schema\G;

// 默认使用 MEMORY 存储引擎 ( information_schema 数据库 )

// 将数据库数据存储在 RAM 内存中 ( 因此 /var/lib/mysql 下没有该数据库的目录 )

show table status from information_schema\G;

Performance_Schema：Performance_Schema 数据库使用

Memory：将所有数据存储在 RAM 中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为 HEAP 引擎。

MRG_MyISAM：使 MySQL DBA 或开发人员能够对一系列相同的 MyISAM 表进行逻辑分组，并将它们作为一个对象引用。适用于 VLDB（Very Large Data Base）环境，如数据仓库。

Archive：为存储和检索大量很少参考的存档或安全审核信息，只支持 SELECT 和 INSERT 操作；支持行级锁和专用缓存区。

Federated 联合：用于访问其它远程 MySQL 服务器一个代理，它通过创建一个到远程 MySQL 服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独 MySQL 服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境。

BDB：可替代 InnoDB 的事务引擎，支持 COMMIT、ROLLBACK 和其他事务特性。

Cluster/NDB：MySQL 的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

CSV：CSV 存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用 CSV 引擎以 CSV 格式导入和导出其他软件和应用程序之间的数据交换。

BLACKHOLE：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储。

example：”stub” 引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎。

4.1.4）管理存储引擎

// 查看 MySQL 支持的存储引擎MariaDB [(none)]> show engines;+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || PERFORMANCE_SCHEMA| YES | Performance Schema| NO | NO | NO || CSV | YES | Stores tables as CSV files | NO | NO | NO || ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO || MEMORY | YES | Hash based, storedin memory, useful for temporary tables | NO | NO | NO || FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES || Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+10 rows in set (0.00sec)// 查看当前默认的存储引擎MariaDB [(none)]> show variables like '%storage_engine%';+------------------------+--------+| Variable_name | Value |+------------------------+--------+| default_storage_engine | InnoDB || storage_engine | InnoDB |+------------------------+--------+2 rows in set (0.00sec)// 设置默认的存储引擎vim /etc/my.cnf[mysqld]default_storage_engine= InnoDB// 查看库中所有表使用的存储引擎MariaDB [(none)]> show table status from db_name;// 查看库中指定表的存储引擎show table status like 'tb_name';show create table tb_name;// 设置表的存储引擎CREATE TABLE tb_name(... ) ENGINE=InnoDB;ALTER TABLE tb_nameENGINE=InnoDB;

对比 存储引擎在 配置文件个数 上的一个小区别

基于 SQL 命令查看 某个数据库中的表 默认使用的 存储引擎

4.2）MySQL 中的 系统数据库

mysql数据库

是 mysql 的 核心数据库，类似于 Sql Server 中的 master 库，主要负责存储数据库的用户、权限设置、关键字等 mysql 自己需要使用的控制和管理信息。

information_schema数据库

MySQL 5.0 之后产生的，一个 虚拟数据库，物理上并不存在 information_schema 数据库类似与 “数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

performance_schema数据库

MySQL 5.5 开始新增的数据库，主要用于收集数据库服务器性能参数，库里表的存储引擎均为 PERFORMANCE_SCHEMA，用户不能创建存储引擎 PERFORMANCE_SCHEMA 的表

sys数据库

MySQL5.7 之后新增加的数据库，库中所有数据源来自 performance_schema。目标是把 performance_schema 的把复杂度降低，让 DBA 能更好的阅读这个库里的内容。让 DBA 更快的了解 DataBase 的运行情况。

4.3）服务器配置和状态

可以通过 mysqld 服务器选项，服务器 系统变量 和服务器 状态变量 进行 MySQL 的配置和查看状态。

官方帮助

MySQL :: MySQL 8.0 Reference Manual :: 7.1.4 Server Option, System Variable, and Status Variable Reference

MySQL :: MySQL 5.7 Reference Manual :: 5.1.3 Server Option, System Variable, and Status Variable Reference

Full List of MariaDB Options, System and Status Variables – MariaDB Knowledge Base

注意：

其中有些参数支持运行时修改，会立即生效

有些参数不支持动态修改，且只能通过修改配置文件，并重启服务器程序生效

有些参数作用域是全局的，为所有会话设置

有些可以为每个用户提供单独（会话）的设置

4.3.1）服务器选项

注意：服务器选项用横线，不用下划线

获取 mysqld的 可用服务器选项 列表：

// 查看 mysqld 可用选项列表及当前值mysqld --verbose --help/usr/libexec/mysqld --verbose --help// 获取 mysqld 当前启动选项mysqld --print-defaults/usr/libexec/mysqld --print-defaults

设置服务器选项 方法

在 命令行 中设置 服务器选项

/usr/bin/mysqld_safe --skip-name-resolve=1/usr/libexec/mysqld --basedir=/usr

在 配置文件my.cnf 中设置 服务器选项

vim /etc/my.cnf[mysqld]skip_name_resolve=1skip-grant-tables

范例：skip-grant-tables是服务器选项，而不是系统变量

[root@centos8 ~] mysqladmin variables | grep skip_grant_tables

4.3.2）服务器 系统变量

服务器 系统变量：可以分全局和会话两种

注意：系统变量用下划线，不用横线

获取 系统变量

SHOW GLOBAL VARIABLES; // 只查看 global 全局变量SHOW [SESSION] VARIABLES; // 查看所有变量 (包括 global全局和 session 会话)// 查看指定的系统变量SHOW VARIABLES LIKE 'VAR_NAME';SELECT @@VAR_NAME;// 查看选项和部分变量[root@centos8 ~] mysqladmin variables

修改服务器变量的值：

help SET

修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效。

SET GLOBAL system_var_name=value;

SET @@global.system_var_name=value;

修改 会话变量：

SET [SESSION] system_var_name=value;

SET @@[session.]system_var_name=value;

范例：character_set_results是 系统变量，并非服务器选项

// 列出所有系统变量MariaDB [(none)]> show variables;// 查找以 character 开头的系统变量MariaDB [(none)]> show variables like 'character%';MariaDB [(none)]> show variables like 'character_set_results';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| character_set_results | utf8 |+-----------------------+-------+1 row in set(0.001 sec)// 显示系统变量值MariaDB [(none)]> select @@character_set_results;+-------------------------+| @@character_set_results |+-------------------------+| utf8 |+-------------------------+1 row in set(0.00 sec)// 修改系统变量MariaDB [(none)]> set character_set_results="utf8mb4"; ( 会话 )MariaDB [(none)]> SET GLOBAL character_set_results="utf8mb4";( 全局 )Query OK, 0rows affected (0.000 sec)MariaDB [(none)]> show variables like 'character_set_results';+-----------------------+---------+| Variable_name | Value|+-----------------------+---------+| character_set_results | utf8mb4 |+-----------------------+---------+1 row in set(0.001 sec)// 将'系统变量'写入配置文件 ( 会导致服务无法正常启用 )// 将服务器选项写入配置文件不影响服务启用[root@centos8 ~] vim /etc/my.cnf[root@centos8 ~] vim /etc/my.cnf.d/mariadb-server.cnf[mysqld]character_set_results=utf8mb4// 注: 由于 character_set_results 不是服务器选项, 写入配置文件将导致服务无法启动[root@centos8 ~] systemctl restart mariadbJob for mariadb.service failed because the control process exited with error code.See "systemctl status mariadb.service" and "journalctl -xe" for details.// 我们可以基于官网链接// 查看关键项是否为服务器选项

范例：修改 MySQL 的最大并发连接数

CentOS 8.2 已无此问题，CentOS7 仍有此问题

注：max_connections即是 系统变量也是 服务器选项

// 默认值为 151[root@centos8~] mysqladmin variables | grep 'max_connections'MariaDB [(none)]> show variables like 'max_connections';+-----------------+-------+| Variable_name| Value |+-----------------+-------+| max_connections | 151|+-----------------+-------+1 row in set(0.001 sec)// 设置全局系统变量MariaDB [hellodb]> set global max_connections=2000;Query OK, 0 rows affected (0.000 sec)// 查看变量MariaDB [hellodb]> show variables like 'max_connections';+-----------------+-------+| Variable_name| Value |+-----------------+-------+| max_connections | 2000 |+-----------------+-------+1 row in set(0.001 sec)// 将系统变量写入配置文件// 注: max_connections 即是系统变量, 也是服务器选项[root@centos8~] vim /etc/my.cnf[root@centos8~] vim /etc/my.cnf.d/mariadb-server.cnf[mysqld]max_connections = 8000// 重启服务[root@centos8~] systemctl restart mariadb[root@centos8~] mysql -uroot -pMariaDB [(none)]> select @@max_connections; # 好像没有用~+-------------------+| @@max_connections |+-------------------+| 594 |+-------------------+1 row in set(0.000 sec)/// 方法 1[root@centos8~] vim /usr/lib/systemd/system/mariadb.service[Service]// 加下面一行LimitNOFILE=65535// 方法 2[root@centos8~] mkdir /etc/systemd/system/mariadb.service.d/[root@node3 ~] vim /etc/systemd/system/mariadb.service.d/limits.conf[Service]LimitNOFILE=65535// 重启 mariadb服务[root@centos8~] systemctl daemon-reload[root@centos8~] systemctl restart mariadb[root@centos8~] mysql -uroot -p -e "select @@max_connections"Enter password:+-------------------+| @@max_connections |+-------------------+|8000 | # 成功~+-------------------+

范例：修改页大小（ MySQL 8.0 ）

参看：InnoDB System Variables – MariaDB Knowledge Base

说明：初始化数据目录后，不能更改此系统变量的值。

在 MariaDB 实例启动时设置 InnoDB 的页面大小，此后保持不变。

[root@centos8 ~] mysqladmin variables | grep innodb_page_size| innodb_page_size| 16384MariaDB [(none)]> show variables like "innodb_page_size";+------------------+-------+| Variable_name| Value |+------------------+-------+| innodb_page_size | 16384 |+------------------+-------+1 row in set (0.001 sec)// 修改配置文件 ( 设置服务器选项 )[root@centos8 ~] vim /etc/my.cnf[root@centos8 ~] vim /etc/my.cnf.d/mariadb-server.cnf[mysqld]innodb_page_size=64k// 删库并重启服务[root@centos8 ~] rm -rf /var/lib/mysql/*[root@centos8 ~] systemctl restart mariadb// 验证[root@centos8 ~] mysqlMariaDB [(none)]> show variables like "innodb_page_size";+------------------+-------+| Variable_name| Value |+------------------+-------+| innodb_page_size | 65536 |+------------------+-------+1 row in set (0.001 sec)

4.3.3）服务器 状态变量

服务器状态变量：分全局和会话两种

状态变量（只读）：用于保存 mysqld 运行中的统计数据的变量，不可更改。

SHOW GLOBAL STATUS;

SHOW [SESSION] STATUS;

范例：

// 显示所有的服务器状态变量MariaDB [(none)]> show status;// 只显示那些名称类似于 "innodb_page_size" 的状态信息MariaDB [(none)]> show status like "innodb_page_size";+------------------+-------+| Variable_name | Value |+------------------+-------+| Innodb_page_size | 16384 |+------------------+-------+1 row in set (0.001 sec)// 只显示那些名称为 "com_select" 的状态信息// GLOBAL 全局MariaDB [hellodb]> SHOW GLOBAL STATUS like 'com_select';+---------------+-------+| Variable_name| Value |+---------------+-------+| Com_select| 5 |+---------------+-------+1 row in set (0.001 sec)

4.3.4）服务器变量 SQL_MODE

SQL_MODE：

对其设置可以完成一些约束检查的工作，可分别进行全局的设置或当前会话的设置。

参考：

SQL_MODE – MariaDB Knowledge Base

使用MySQL这么久,你了解sql_mode吗？-腾讯云开发者社区-腾讯云

mysql的sql_mode合理设置

MySQL :: MySQL 5.7 Reference Manual :: 5.1.6 Server Command Options

常见的 MODE：

NO_AUTO_CREATE_USER： 禁止 GRANT 创建密码为空的用户。

NO_ZERO_DATE：在严格模式，不允许使用 ‘0000-00-00’ 的时间。

ONLY_FULL_GROUP_BY： 对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么将认为这个 SQL 是不合法的。

NO_BACKSLASH_ESCAPES： 反斜杠 “” 作为普通字符而非转义字符。

PIPES_AS_CONCAT： 将 “||” 视为连接操作符而非 “或” 运算符。

范例：CentOS 8 修改 SQL_MODE变量实现分组语句控制

// 查询数据库的 SQL_MODE 设置MariaDB [hellodb]> select @@sql_mode;MariaDB [hellodb]> show variables like 'sql_mode';// 两个分组查询MariaDB [hellodb]> select classid,count(*) from students group by classid;+---------+----------+| classid | count(*) |+---------+----------+| NULL | 2 || 1 | 4 || 2 | 3 || 3 | 4 || 4 | 4 || 5 | 1 || 6 | 4 || 7 | 3 |+---------+----------+8 rows in set (0.00 sec)// 如下 SQL 命令在一些数据库系统中是错误的// 因为在 GROUP BY 子句中, 除了被 GROUP BY 的列和聚合函数外// 其他列必须出现在聚合函数中或者在 SELECT 列表中MariaDB [hellodb]> select stuid,classid,count(*) from students group by classid;+-------+---------+----------+| stuid | classid | count(*) |+-------+---------+----------+| 24 | NULL | 2 || 2 | 1 | 4 || 1 | 2 | 3 || 5 | 3 | 4 || 4 | 4 | 4 || 6 | 5 | 1 || 9 | 6 | 4 || 8 | 7 | 3 |+-------+---------+----------+8 rows in set (0.00 sec)// 修改 SQL_MODEMariaDB [hellodb]> setsql_mode="ONLY_FULL_GROUP_BY";// 确认修改已生效MariaDB [hellodb]> show variables like 'sql_mode';+---------------+--------------------+| Variable_name | Value |+---------------+--------------------+| sql_mode | ONLY_FULL_GROUP_BY |+---------------+--------------------+1 row in set (0.00 sec)MariaDB [hellodb]> select classid,count(*) from students group by classid;+---------+----------+| classid | count(*) |+---------+----------+|NULL |2 ||1 |4 ||2 |3 ||3 |4 ||4 |4 ||5 |1 ||6 |4 ||7 |3 |+---------+----------+8 rows in set (0.001 sec)// 由于现在的 SQL_MODE 要求所有SELECT 列必须是 GROUP BY 子句的一部分或者是聚合函数// 这个查询导致了错误MariaDB [hellodb]> select stuid,classid,count(*) from students group by classid;ERROR 1055 (42000): 'hellodb.students.StuID' isn't in GROUPBY

总体来说，这个案例突显了 SQL_MODE在 GROUP BY 查询中对列的选择和约束的重大影响，特别是在 “ONLY_FULL_GROUP_BY” 模式下，SELECT 列必须被包含在 GROUP BY 子句中，或者是聚合函数的一部分。

范例：CentOS 7 修改 SQL_MODE变量（ Linux 小技巧 ）

// 使用 CREATE TABLE 语句创建了一个表名为 test// 包含两列, 分别是 id (整数类型) 和 name (长度为3的字符串类型)MariaDB [hellodb]> create table test (id int ,name varchar(3));Query OK, 0 rows affected (0.04 sec)// 使用 INSERT 语句向 test 表插入了一行数据// 其中 name 列的值是 'abcde', 而这个值长度超过了name 列的定义长度 (3)// 在这种情况下, MariaDB 会发出一个警告, 提示数据在插入时被截断MariaDB [hellodb]> insert test values(1,'abcde');Query OK, 1 row affected, 1 warning(0.00 sec)// 查看警告信息MariaDB [hellodb]> show warnings;+---------+------+-------------------------------------------+| Level| Code | Message |+---------+------+-------------------------------------------+| Warning | 1265 | Data truncated for column 'name' at row 1 |+---------+------+-------------------------------------------+1 row in set (0.00 sec)// 显示表的内容, 可以看到 'abcde' 被截断为 'abc'MariaDB [hellodb]> select * from test;+------+------+| id| name |+------+------+|1 | abc |+------+------+1 row in set (0.00 sec)// 查看当前数据库的 SQL_MODE 设置 ( 显示为空 )MariaDB [hellodb]> show variables like 'SQL_MODE';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_mode ||+---------------+-------+1 row in set (0.00 sec)// 修改 SQL_MODE// 修改了 SQL_MODE 设置为 "TRADITIONAL" 模式// 这个模式包含了严格的数据长度限制MariaDB [hellodb]> SET SQL_MODE=TRADITIONAL;Query OK, 0 rows affected (0.00 sec)// 重新查看 SQL_MODE 设置, 确认修改已生效MariaDB [hellodb]> show variables like 'SQL_MODE';// 尝试再次使用 INSERT 语句向 test 表插入数据// 这次插入 'magedu' (长度为 6 的字符串)// 由于 "TRADITIONAL" 模式下对数据长度有更严格的限制, 导致错误MariaDB [hellodb]> insert test values(2,'magedu');ERROR 1406 (22001): Data too long for column 'name' at row 1

总体来说，这个案例强调了在数据库中定义列的长度，以及通过设置

SQL_MODE对数据插入的长度进行限制的影响。在 “TRADITIONAL” 模式下，数据长度超过列定义时会导致错误。

4.4）INDEX 索引

4.4.1）索引介绍

参考：MySQL索引15连问，扛住！-51CTO.COM

问：给字段创建索引就是将该字段的内容进行排序，方便查找吗” />答：创建索引可以加快查询速度，但并非简单地对字段内容进行排序。在数据库中，索引是一种数据结构，它帮助数据库系统迅速定位到表中的数据。当我们为某个字段创建索引时，数据库系统会为该字段的值构建一个有序的数据结构，通常采用B树或B+树形式。这样在进行查询时，数据库系统能直接利用这个有序结构快速找到对应的记录，而无需扫描整个表。

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键 key，索引通过存储引擎实现。

索引 是一种 能提高数据库查询效率 的数据结构。

它可以比作一本字典的目录，可以帮你快速找到对应的记录。

索引一般存储在磁盘的文件中，它是占用物理空间的。

正所谓水能载舟，也能覆舟。适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。

优点：

索引可以降低服务需要扫描的数据量，减少了 IO 次数。

索引可以帮助服务器避免排序和使用临时表。

索引可以帮助将随机 I/O 转为顺序 I/O。

缺点：

占用额外空间，影响插入速度。

索引类型：

参考：mysql的主键是自带索引的吗_mob649e8161738c的技术博客_51CTO博客

在 MySQL 中，主键是用于唯一标识表中每一行数据的字段。

主键的作用有很多，其中之一就是自带索引。索引是一种数据结构，它能够提高搜索和查询的速度。主键字段自带索引，可以帮助我们快速定位到指定的数据行，提高查询效率。

B+ TREE 索引

HASH 索引、

R TREE 索引、

FULL TEXT 索引。

聚簇索引、非聚簇索引：数据和索引是否存储在一起。

InnoDB：聚集索引（ 数据与索引 存储在一起 ）

MYISAM：非聚集索引（ 数据与索引 分开存储 ）

*.MYD数据

*.MYI索引

主键索引、二级索引（ 非主键索引 ）

主键索引：

是建立在表中主键字段上的索引。

数据按照主键字段的顺序进行排序。

叶子节点存放数据。在聚集索引中，数据行实际上是按照主键的顺序存储在叶子节点上的。

二级索引（ 次级索引 ）：

是建立在表中非主键字段上的索引。

数据按照非主键字段的顺序进行排序。

叶子节点不直接存放数据。叶子节点存储的是该字段对应的行 ID 或主键值，而不是实际的数据值。

这意味着，要获取完整的行数据，数据库系统需要进一步使用这些行 ID 或主键值去主键索引中查找。

非主键索引依赖于主键索引。当你查询一个非主键字段时，数据库系统首先使用非主键索引找到对应的主键值，然后再通过主键索引找到完整的行数据。

为什么需要非主键索引？虽然每个表只能有一个主键索引，但可能有多个非主键字段经常被用于查询条件。通过在这些非主键字段上建立索引，可以大大加速查询速度，尤其是在涉及多个字段的复杂查询中。

稠密索引、稀疏索引：是否索引了每一个数据项

简单索引、组合索引：是否是多个字段的索引

左前缀索引：取前面的字符做索引

覆盖索引：从索引中即可取出要查询的数据，性能高

MySQL 数据库 的 数据和表结构 通常被持久化地存储在磁盘文件系统中的文件里。

这些文件包括：

数据文件（Data Files）：包含实际的表数据。

索引文件（Index Files）：包含用于加速数据检索的索引结构。

日志文件（Log Files）：包含数据库系统的事务日志。

配置文件和元数据文件：包含数据库的配置信息和元数据，例如表的结构、字段类型等。

这些文件 通常存储在数据库管理系统的特定目录（ 如：/var/lib/mysql）中。

MySQL 默认 将数据库文件存储在该目录 下。

存放在磁盘上的文件系统结构中，使数据库能够持久化地存储数据，

即使数据库服务器关闭或重新启动，数据仍然可以被保留。

4.4.2）索引结构

参考：

B 树：终于把B树搞明白了(一)_B树的引入，为什么会有B树_哔哩哔哩_bilibili

B+ 树：终于把B树搞明白了(三)_B树的查找，B+树的引入_哔哩哔哩_bilibili

参考链接：Data Structure Visualization

二叉树（二分法 ）

参考链接：Binary Search Tree Visualization

二分法 是我们常用的一种查找算法，可以有效的提升数据找找的效率，其实现思路是：

1、首先对数据集进行排序。

2、找到数据集中间位置的节点。

3、用查找的条件和中间节点进行比较，等于则直接返回

4、中间节点数据小于查找条件则说明数据在排序列表的左边

5、大于则说明数据在排序列表的右边。

其基本思想是通过不断将搜索范围缩小一半来查找目标值

红黑树（平衡树 ）

参考链接：Red/Black Tree Visualization

B-Tree 索引（B 树 ）

参考链接：B-Tree Visualization

比如我们索引数值 36，在硬盘块 1 上找到 P3

通过 P3 找到 硬盘块 4，在硬盘块 4 上找到 P1

通过 P1 直接找到数值 36（ 仅通过两次节点就找到了对应数值 ）

注：数据量越大，层级越多，索引效率越低。

缺点 1 ：当我们需要索引特定数字，例如17，我们可以直接找到。然而，当我们查找 75时，需要经过两个节点

可以发现：我们的索引速率居然和数据的存放位置有关。这显然不够理想。

缺点 2 ：并且当我们进行 范围索引时，效率很低。

B+ Tree 索引（B+ 树 ）

参考链接：B+ Tree Visualization

B+ 树是在 B 树 的基础上进行了改进的数据结构，广泛应用于 数据库索引。

B+ 树 访问效率更加平衡：

优势 1 ：不管找哪个值，都是索引两次，而后找到结果。（ 访问速率更均衡 ）

优势 2 ：并且叶子节点的数据都是关联着的，无需反复从根节点进行索引，大大提升索引效率。

面试题：InnoDB 中一颗的 B+ 树可以存放多少行数据？

假设定义一颗 B+ 树高度为 2, 即一个根节点和若干叶子节点.

那么这棵 B+ 树的 存放总行记录数=根节点指针数*

单个叶子记录的行数.

这里先计算叶子节点, B+树中的单个叶子节点的大小为 16 K, 假设每一条目为 1 K, 那么记录数即为16(16k/1K=16), 然后计算非叶子节点能够存放多少个指针, 假设主键ID为bigint类型, 那么长度为 8 字节, 而指针大小在 InnoDB 中是设置为 6 个字节, 这样加起来一共是 14 个字节.

那么通过页大小/(主键ID大小+指针大小）, 即16384/14=1170个指针, 所以一颗高度为2的B+树能存放16*1170=18720条这样的记录. 根据这个原理就可以算出一颗高度为3的B+树可以存放16*1170*1170=21902400条记录.

所以在 InnoDB 中B+树高度一般为 2-3 层, 它就能满足千万级的数据存储.

可以使用 B+Tree 索引的查询类型：( 假设前提：姓，名，年龄三个字段建立了一个复合索引 ）

全值匹配：精确所有索引列，如：姓 wang，名 xiaochun，年龄 30

匹配最左前缀：即只使用索引的第一列，如：姓wang

匹配列前缀：只匹配一列值开头部分，如：姓以w开头的记录

匹配范围值：如：姓ma和姓wang之间

精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的记录

只访问索引的查询

B+Tree 索引的限制：

如不从最左列开始，则无法使用索引，如：查找名为 xiaochun，或姓为 g 结尾

不能跳过索引中的列：如：查找姓 wang，年龄 30 的，只能使用索引第一列

特别提示：

索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引

为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash 索引

Hash 索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好。

Memory 存储引擎支持显式 hash 索引，InnoDB 和 MyISAM 存储引擎不支持

适用场景：只支持等值比较查询，包括 =, , IN()

不适合使用 hash 索引的场景

不适用于顺序查询：索引存储顺序的不是值的顺序

不支持模糊匹配

不支持范围查询

不支持部分索引列匹配查找：如 A，B 列索引，只查询 A 列索引无效

地理空间数据索引 R-Tree（ Geospatial indexing ）

MyISAM 支持地理空间索引，可使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多

InnoDB 从 MySQL5.7 之后也开始支持

全文索引（FULLTEXT）

在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎

InnoDB 从 MySQL 5.6 之后也开始支持

聚簇 和 非聚簇索引

MySql 中的聚簇索引、非聚簇索引、唯一索引和联合索引-CSDN博客

主键 和 二级索引

一级索引：索引和数据存储在一起，都存储在同一个 B+tree 中的叶子节点。一般主键索引都是一级索引。

二级索引：叶子节点存储的是主键而不是数据。也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录。

冗余 和 重复索引

冗余索引：（A），（A，B），注意如果同时存在，仍可能会使用(A)索引

重复索引：已经有索引，再次建立索引

4.4.3）索引优化

参考资料：阿里的《 Java开发手册 》

泰山版Java开发手册-阿里云开发者社区

独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在 where 条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）

左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度

多列索引：AND 操作时更适合使用多列索引，而非为每个列创建单独的索引

选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

只要列中含有 NULL 值，就最好不要在此列设置索引，复合索引如果有 NULL 值，此列在使用时也不会使用索引

对于经常在 where 子句使用的列，最好设置索引

对于有多个列 where 或者 order by 子句，应该建立复合索引

对于 like 语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引

尽量不要使用 not in 和 操作，虽然可能使用索引，但性能不高

不要使用 RLIKE 正则表达式会导致索引失效

查询时，能不要* 就不用*，尽量写全字段名，比如：select id,name,age from students;

大部分情况连接效率远大于子查询

在有大量记录的表分页时使用 limit

对于经常使用的查询，可以开启查询缓存

多使用 explain 和 profile 分析查询语句

查看慢查询日志，找出执行时间长的 sql 语句优化

4.4.4）管理索引

创建索引

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],…);

ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);

help CREATE INDEX;

// 举例: 创建索引

# idx_name给这个索引起的名字

# ON students 指定了要在哪个表上创建索引

# (name(5))指定了要在哪个字段上创建索引

# 括号中的数字 5 表示索引将只包含每个名字的前 5 个字符

create index idx_name on students(name(5));

// 查看索引

SHOW INDEX FROM students;

//

EXPLAIN select * from students where name=’Xu Xian’;

删除索引

DROP INDEX index_name ON tbl_name;

ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引

SHOW INDEX FROM [db_name.]tbl_name;

// 举例

SHOW INDEX FROM students;

优化表空间

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1; // MySQL 无此变量

SHOW INDEX_STATISTICS;

范例：

MariaDB [hellodb]> SET GLOBAL userstat=1;Query OK, 0 rows affected (0.000 sec)MariaDB [hellodb]> SHOW INDEX_STATISTICS;Empty set (0.000 sec)MariaDB [hellodb]> desc students;+-----------+---------------------+------+-----+---------+----------------+| Field | Type| Null |Key | Default | Extra |+-----------+---------------------+------+-----+---------+----------------+| StuID | int(10) unsigned| NO|PRI | NULL| auto_increment || Name | varchar(50) | NO|| NULL||| Age| tinyint(3) unsigned | NO|| NULL||| Gender| enum('F','M')| NO|| NULL||| ClassID| tinyint(3) unsigned | YES || NULL||| TeacherID | int(10) unsigned| YES || NULL||+-----------+---------------------+------+-----+---------+----------------+6 rows in set (0.001 sec)MariaDB [hellodb]> show index from students\G*************************** 1. row *************************** Table: students Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: StuID Collation: ACardinality: 25Sub_part: NULL Packed: NULLNull: Index_type: BTREEComment:Index_comment:1 row in set (0.000 sec)MariaDB [hellodb]> SHOW INDEX_STATISTICS;Empty set (0.000 sec)MariaDB [hellodb]> select * from students where stuid=10;+-------+--------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+--------------+-----+--------+---------+-----------+|10 | Yue Lingshan | 19 | F | 3 | NULL |+-------+--------------+-----+--------+---------+-----------+1 row in set (0.000 sec)MariaDB [hellodb]> SHOW INDEX_STATISTICS;+--------------+------------+------------+-----------+| Table_schema | Table_name | Index_name |Rows_read |+--------------+------------+------------+-----------+| hellodb | students| PRIMARY|1 |+--------------+------------+------------+-----------+MariaDB [hellodb]> select * from students where stuid=10;+-------+--------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+--------------+-----+--------+---------+-----------+|10 | Yue Lingshan | 19 | F | 3 | NULL |+-------+--------------+-----+--------+---------+-----------+1 row in set (0.000 sec)MariaDB [hellodb]> SHOW INDEX_STATISTICS;+--------------+------------+------------+-----------+| Table_schema | Table_name | Index_name |Rows_read |+--------------+------------+------------+-----------+| hellodb | students| PRIMARY|2 |+--------------+------------+------------+-----------+1 row in set (0.000 sec)

举例：使用索引 与 不使用索引的区别（Linux 小技巧：MySQL 索引 ）

// 导入数据表 ( 该表存在 100000 条记录 )[16:12:58 root@blog ~] mysqlhellodb  call sp_testlog;Query OK, 1 row affected (8.47 sec)// 查看数据表MariaDB [hellodb]> show tables;+-------------------+| Tables_in_hellodb |+-------------------+| classes || coc || courses || scores || students || teachers || testlog | # 新增表| toc |+-------------------+8 rows in set (0.00 sec)// "创建索引"MariaDB [hellodb]> create index idx_name on testlog(name);Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0// 查看索引MariaDB [hellodb]> show indexfrom testlog;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |Sub_part | Packed | Null | Index_type| Comment | Index_comment |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| testlog | 0 | PRIMARY | 1 | id | A | 100286 |NULL | NULL | | BTREE | | || testlog | 1 | idx_name | 1 | name | A | 200 |NULL | NULL | YES | BTREE | | |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)// 查询记录 ( 准备对比速率 )MariaDB [hellodb]> select * from testlog where name='wang1234';Empty set (0.00 sec) #耗时 0 秒// 分析 SQL 语句是否有利用索引 ( 该查询语句利用了我们创建的索引 )MariaDB [hellodb]> explain select * from testlog where name='wang1234';+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+| id | select_type | table| type | possible_keys | key | key_len | ref | rows| Extra |+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+| 1 | SIMPLE | testlog | ref | idx_name | idx_name | 11 | const | 1| Using index condition |+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+1 row in set (0.00 sec)----// "删除自建的 name 字段索引"MariaDB [hellodb]> drop indexidx_name on testlog;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0// 查看索引 ( 成功删除了我们自建的 name字段索引 )MariaDB [hellodb]> show indexfrom testlog;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |Sub_part | Packed | Null | Index_type| Comment | Index_comment |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| testlog | 0 | PRIMARY | 1 | id | A | 100286 |NULL | NULL | | BTREE | | |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)// 再次查询记录 ( 对比速率 )// ( 没有可利用的索引,因此查询速率降低 )// 进行了全表查询MariaDB [hellodb]> select * from testlog where name='wang1234';Empty set (0.02 sec) #耗时 0.02 秒// 分析 SQL 语句是否有利用索引 ( 没有可利用的索引,因此查询速率降低,进行了全表查询 )MariaDB [hellodb]> explain select * from testlog where name='wang1234';+------+-------------+---------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table| type | possible_keys | key | key_len | ref |rows | Extra |+------+-------------+---------+------+---------------+------+---------+------+--------+-------------+| 1 | SIMPLE | testlog | ALL | NULL | NULL | NULL | NULL |100286 | Using where |+------+-------------+---------+------+---------------+------+---------+------+--------+-------------+1 row in set (0.00 sec)

注意点：

（ 左前缀匹配规则 ）索引是按照一定的排序规则进行存储的。

// 以字母 “s” 开头的名字 ( 可以利用索引 )

explain select * from students where name like ‘s%’;

// 以字母 “s” 结尾的名字 ( 不可利用索引 )

explain select * from students where name like ‘%s’;

// 包含字母 “s” 的名字 ( 不可利用索引 )

explain select * from students where name like ‘%s%’;

4.4.5）EXPLAIN工具

可以通过 EXPLAIN来分析索引的有效性。分析 SQL 语句是否有利用索引。

获取查询执行计划信息，用来查看查询优化器如何执行查询。

参考资料：MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

语法

EXPLAIN SELECT clause

// 举例 ( stuid 是主键, 默认存在主键索引 )

// 并且主键拥有唯一值特性, 因此假如扫描到值 ( 会停止扫描 )

EXPLAIN select * fromstudents where stuid=20;

// 举例 ( name 非主键, 不存在索引 )

// 并且 name 非主键, 没有唯一值特性 “因此会全表扫描”

// 而主键拥有唯一值特性, 因此假如扫描到值 (会停止扫描 )

EXPLAIN select * from students where name=’Xu Xian’;

EXPLAIN 输出信息说明：

说明： type 显示的是访问类型，是较为重要的一个指标。

结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到 range 级别，最好能达到 ref

范例：

MariaDB [hellodb]> explain select * from students where stuid not in (5,10,20);

MariaDB [hellodb]> explain select * from students where age > (select avg(age) from teachers);

范例：创建索引和使用索引

MariaDB [hellodb]> create index idx_name on students(name(10));Query OK, 0 rows affected (0.009 sec)Records: 0 Duplicates: 0 Warnings: 0MariaDB [hellodb]> show indexes from students\G*************************** 1. row *************************** Table: students Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: StuID Collation: ACardinality: 25Sub_part: NULL Packed: NULLNull: Index_type: BTREEComment:Index_comment:*************************** 2. row *************************** Table: students Non_unique: 1Key_name: idx_nameSeq_in_index: 1Column_name: Name Collation: ACardinality: 25Sub_part: 10 Packed: NULLNull: Index_type: BTREEComment:Index_comment:2 rows in set (0.000 sec)MariaDB [hellodb]> explain select * from students where name like 'w%';MariaDB [hellodb]> explain select * from students where name like 'x%';

范例：复合索引

mysql> desc students;+-----------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------------+------+-----+---------+----------------+| StuID | int(10) unsigned| NO| PRI | NULL| auto_increment || Name | varchar(50) | NO| | NULL||| Age| tinyint(3) unsigned | NO| | NULL||| Gender| enum('F','M')| NO| | NULL||| ClassID| tinyint(3) unsigned | YES | | NULL||| TeacherID | int(10) unsigned| YES | | NULL||+-----------+---------------------+------+-----+---------+----------------+6 rows in set (0.00 sec)// 创建复合索引mysql> create index idx_name_age on students(name,age);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates:0 Warnings: 0mysql> desc students;+-----------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------------+------+-----+---------+----------------+| StuID | int(10) unsigned| NO| PRI | NULL| auto_increment || Name | varchar(50) | NO| MUL | NULL||| Age| tinyint(3) unsigned | NO| | NULL||| Gender| enum('F','M')| NO| | NULL||| ClassID| tinyint(3) unsigned | YES | | NULL||| TeacherID | int(10) unsigned| YES | | NULL||+-----------+---------------------+------+-----+---------+----------------+6 rows in set (0.00 sec)mysql> show indexes from students\G*************************** 1. row *************************** Table: students Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: StuID Collation: ACardinality: 25Sub_part: NULL Packed: NULLNull: Index_type: BTREEComment:Index_comment:Visible: YES Expression: NULL*************************** 2. row *************************** Table: students Non_unique: 1Key_name: idx_name_ageSeq_in_index: 1Column_name: Name Collation: ACardinality: 25Sub_part: NULL Packed: NULLNull: Index_type: BTREEComment:Index_comment:Visible: YES Expression: NULL*************************** 3. row *************************** Table: students Non_unique: 1Key_name: idx_name_ageSeq_in_index: 2Column_name: Age Collation: ACardinality: 25Sub_part: NULL Packed: NULLNull: Index_type: BTREEComment:Index_comment:Visible: YES Expression: NULL3 rows in set (0.01 sec)// 跳过查询复合索引的前面字段, 后续字段的条件查询无法利用复合索引mysql> explain select *from students where age=20;

范例：复合索引和覆盖索引

mysql> desc testlog;+--------+----------+------+-----+---------+----------------+| Field | Type |Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| id | int(11) |NO| PRI | NULL| auto_increment || name| char(10) |YES | | NULL||| salary | int(11) |YES | | 20 ||+--------+----------+------+-----+---------+----------------+3 rows in set (0.00 sec)// 创建复合索引mysql> create index idx_name_salary on testlog(name,salary);Query OK, 0 rows affected (0.25 sec)Records: 0 Duplicates:0 Warnings: 0mysql> desc testlog;+--------+----------+------+-----+---------+----------------+| Field | Type |Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| id | int(11) |NO| PRI | NULL| auto_increment || name| char(10) |YES | MUL | NULL||| salary | int(11) |YES | | 20 ||+--------+----------+------+-----+---------+----------------+3 rows in set (0.01 sec)mysql> show indexes from testlog\G*************************** 1. row *************************** Table: testlog Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: id Collation: ACardinality: 90620Sub_part: NULL Packed: NULLNull: Index_type: BTREEComment:Index_comment:Visible: YES Expression: NULL*************************** 2. row *************************** Table: testlog Non_unique: 1Key_name: idx_name_salarySeq_in_index: 1Column_name: name Collation: ACardinality: 95087Sub_part: NULL Packed: NULLNull: YES Index_type: BTREEComment:Index_comment:Visible: YES Expression: NULL*************************** 3. row *************************** Table: testlog Non_unique: 1Key_name: idx_name_salarySeq_in_index: 2Column_name: salary Collation: ACardinality: 99852Sub_part: NULL Packed: NULLNull: YES Index_type: BTREEComment:Index_comment:Visible: YES Expression: NULL3 rows in set (0.00 sec)// 覆盖索引mysql> explain select* from testlog where salary=66666;

4.4.6）使用 profile工具

参考：MySQL中Profiling功能有哪些-阿里云帮助中心

Profile工具可以用于 查看每一条 SQL 语句具体的执行时间（MySQL 小技巧 ）

// 查看 profiling 变量的状态MariaDB [hellodb]> select @@profiling;+-------------+| @@profiling |+-------------+| 0 |+-------------+1 row in set (0.00 sec)// 打开 profile 工具// 开启后 该工具可以显示 SQL 语句执行的详细过程MariaDB [hellodb]> setprofiling = ON;Query OK, 0 rows affected (0.00 sec)// 查看语句MariaDB [hellodb]> show profiles;// 可以看到每一条 SQL 语句具体的执行时间MariaDB [hellodb]> show profiles;+----------+------------+-------------------------------------+| Query_ID | Duration | Query |+----------+------------+-------------------------------------+|1 | 0.00019238 | select @@profiling||2 | 0.00115590 | select * from students where age=20||3 | 0.00006616 | show profiles for query 2 ||4 | 4.00319568 | select sleep(1) from teachers |+----------+------------+-------------------------------------+4 rows in set (0.000 sec)// 显示某条 SQL 语句的详细执行步骤和时长MariaDB [hellodb]> show profile for query 4;+------------------------+----------+| Status| Duration |+------------------------+----------+| Starting | 0.000157 || Checking permissions | 0.000009 || Opening tables| 0.000025 || After opening tables | 0.000005 || System lock | 0.000004 || Table lock| 0.000006 || Init | 0.000017 || Optimizing| 0.000009 || Statistics| 0.000018 || Preparing| 0.000028 || Executing| 0.000003 || Sending data | 0.000070 || User sleep| 1.001128 || User sleep| 1.000313 || User sleep| 1.000834 || User sleep| 1.000348 || End of update loop| 0.000032 || Query end| 0.000003 || Commit| 0.000014 || Closing tables| 0.000004 || Unlocking tables | 0.000003 || Closing tables| 0.000012 || Starting cleanup | 0.000003 || Freeing items| 0.000056 || Updating status | 0.000024 || Logging slow query| 0.000069 || Reset for next command | 0.000004 |+------------------------+----------+27 rows in set (0.000sec)// 显示 CPU 使用情况MariaDB [hellodb]> Show profile cpu for query 4;+------------------------+----------+----------+------------+| Status| Duration | CPU_user | CPU_system |+------------------------+----------+----------+------------+| Starting | 0.000157 | 0.000090 |0.000065 || Checking permissions | 0.000009 | 0.000005 |0.000004 || Opening tables| 0.000025 | 0.000014 |0.000010 || After opening tables | 0.000005 | 0.000003 |0.000002 || System lock | 0.000004 | 0.000002 |0.000002 || Table lock| 0.000006 | 0.000004 |0.000002 || Init | 0.000017 | 0.000010 |0.000007 || Optimizing| 0.000009 | 0.000005 |0.000004 || Statistics| 0.000018 | 0.000010 |0.000007 || Preparing| 0.000028 | 0.000016 |0.000012 || Executing| 0.000003 | 0.000002 |0.000002 || Sending data | 0.000070 | 0.000059 |0.000000 || User sleep| 1.001128 | 0.000665 |0.000000 || User sleep| 1.000313 | 0.000716 |0.000000 || User sleep| 1.000834 | 0.000379 |0.000100 || User sleep| 1.000348 | 0.000319 |0.000231 || End of update loop| 0.000032 | 0.000017 |0.000012 || Query end| 0.000003 | 0.000002 |0.000002 || Commit| 0.000014 | 0.000008 |0.000005 || Closing tables| 0.000004 | 0.000002 |0.000002 || Unlocking tables | 0.000003 | 0.000002 |0.000001 || Closing tables| 0.000012 | 0.000007 |0.000005 || Starting cleanup | 0.000003 | 0.000001 |0.000001 || Freeing items| 0.000056 | 0.000034 |0.000024 || Updating status | 0.000024 | 0.000013 |0.000010 || Logging slow query| 0.000069 | 0.000040 |0.000029 || Reset for next command | 0.000004 | 0.000002 |0.000001 |+------------------------+----------+----------+------------+27 rows in set (0.000sec)

4.5）并发控制

4.5.1）锁机制

参考：一张图彻底搞懂 MySQL 的锁机制 | MySQL 技术论坛

锁的解释：计算机协调多个进程或线程并发访问某一资源的机制

锁类型：

读锁：共享锁，也称为 S锁，只读不可写（包括当前事务），多个读互不阻塞。

其他人可读不可写

写锁：独占锁，排它锁，也称为 X锁，写锁会阻塞其它事务（不包括当前事务）的读和写。

其他人不可读也不可写

S 锁和 S 锁是 兼容 的。

X 锁和其它锁都 不兼容。

举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为 锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为 锁冲突。

锁粒度：

表级锁：MyISAM

行级锁：InnoDB

实现

存储引擎：自行实现其锁策略和锁粒度

服务器级：实现了锁，表级锁，用户可显式请求

分类：

隐式锁：由存储引擎自动施加锁

显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制

4.5.2）显式使用锁

帮助：LOCK TABLES – MariaDB Knowledge Base

加锁

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] …

lock_type:

READ# 读锁

WRITE# 写锁

// 举例

lock tables students read; // 读锁

select * from students; // 可读不可写

update students set teacherid=2 where stuid=6; // 可读不可写

解锁

UNLOCK TABLES;

关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

FLUSH TABLES [tb_name[,…]] [WITH READ LOCK]

// 举例

flush tables with read lock; // 全局读锁

unlock tables; // 解锁

查询时加写或读锁

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

范例：加读锁

// 加锁

mysql>lock tables students read;

QueryOK, 0 rows affected (0.00 sec)

mysql>update students set classid=2 where stuid=24;

ERROR1099 (HY000): Table ‘students’ waslocked with a READ lock and can’t beupdated

// 解锁

mysql>unlock tables ;

mysql>update students set classid=2 where stuid=24;

QueryOK, 1 row affected (1 min 45.52 sec)

Rows matched: 1 Changed: 1 Warnings: 0

范例：同时在两个终端对同一行记录修改

// 同时对同一行记录执行 update// 在第一终端提示 1 行成功MariaDB [hellodb]> update students set classid=1 where stuid=24;Query OK, 1 row affected (0.002 sec)Rows matched: 1 Changed: 1 Warnings: 0// 在第二终端提示 0 行修改MariaDB [hellodb]> update students set classid=1 where stuid=24;Query OK, 0 rows affected (0.000 sec)Rows matched: 1 Changed: 0 Warnings: 0

4.5.3）事务

事务 Transactions：一组原子性的 SQL 语句，或一个独立工作单元。

事务日志：记录事务信息，实现 undo，redo 等故障恢复功能

锁机制：当你执行一个事务时，数据库系统会通过自动加锁来保护相关数据，确保在事务执行期间，其他并行事务无法对相同数据进行修改。

4.5.3.1）事务特性

参考：数据库ACID详细解析-CSDN博客

ACID 特性

A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚。

C：consistency 一致性；数据库总是从一个一致性状态转换为另一个一致性状态，类似于能量守恒定律 （ N50 周启皓语录 ）

I：Isolation 隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发。

D：durability 持久性；一旦事务提交，其所做的修改会永久保存于数据库中。

原子性

针对同一个事务，这个过程 包含两个步骤

A：800 – 200 = 600

B：200 + 200 = 400

原子性表示：这两个步骤一起成功，或者一起失败，不能只发生其中一个动作。

一致性

针对一个事务操作前与操作后的状态一致

操作前 A：800，B：200

操作后 A：600，B：400

一致性表示事务完成后，符合逻辑运算。

隔离性

针对多个用户同时操作，主要是排除其他事务对本次事务的影响。

持久性

表示事务结束后的数据 不随着外界原因导致数据丢失。

操作前 A：800，B：200

操作后 A：600，B：400

如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为 A：800，B：200。

如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为 A：600，B：400。

Transaction 生命周期

START TRANSACTION：启动事务

INSERT UPDATE DELETE：执行 DML 语句

COMMIT：提交，相当于 vi 中的 wq 保存退出

ROLLBACK：回滚,，相当于 vi 中的 q! 不保存退出

4.5.3.2）管理事务

显式 启动事务

BEGIN

BEGIN WORK

START TRANSACTION

结束事务

注意：只有事务型存储引擎中的DML 语句 方能支持此类操作

// 提交, 相当于 vi 中的 wq 保存退出

COMMIT

// 回滚, 相当于 vi 中的 q! 不保存退出

ROLLBACK

自动提交

默认为 1，为 0 时设为 非自动提交（ 不自动提交，需执行 COMMIT才提交 ）

建议：显式请求和提交事务，而不要使用 “自动提交” 功能

set autocommit={1|0}

演示：（MySQL 小技巧：配置 MySQL 事务 非自动提交）

oracle数据库：默认就是这种 非自动提交形式

// 默认为自动提交MariaDB [hellodb]> select @@autocommit;+--------------+| @@autocommit |+--------------+| 1 |+--------------+1 row in set (0.01 sec)// 修改为非自动提交MariaDB [hellodb]> set autocommit=0;Query OK, 0rows affected (0.01 sec)// 执行 DML 语句 ( 插入数据 )// 并未执行 COMMIT 提交操作MariaDB [hellodb]> insert teachers values(5,'wangj',25,'M');Query OK, 1row affected (0.00 sec)// 看似记录插入成功MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | LinChaoying | 93 | F || 5 | wangj | 25 | M |+-----+---------------+-----+--------+5 rows in set (0.00 sec)// 实则关闭当前终端后exit// 记录就不存在了MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | LinChaoying | 93 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)

// 修改为非自动提交MariaDB [hellodb]> set autocommit=0;Query OK, 0rows affected (0.00 sec)// 执行 DML 语句( 删除数据 )// 并未执行 COMMIT 提交操作MariaDB [hellodb]> delete from teachers;Query OK, 4rows affected (0.00 sec)// 数据表已被删除MariaDB [hellodb]> select * from teachers;Empty set (0.00 sec)// 执行撤销操作 (rollback )MariaDB [hellodb]> rollback;Query OK, 0rows affected (0.00 sec)// 数据表又回来了.MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name| Age | Gender |+-----+---------------+-----+--------+| 1 | SongJiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | LinChaoying | 93 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)// 退出数据库exit

// 开启一个事务begin;// 插入一条记录MariaDB [hellodb]>insert teachers values(5,'wangj',25,'M');Query OK,1 row affected (0.00 sec)// 不 COMMIT 提交记录(其他终端是无法看到该插入数据的 )// 这个就是事务的特性之一 (隔离性 )MariaDB [hellodb]>select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age| Gender |+-----+---------------+-----+--------+| 1 | Song Jiang| 45| M || 2 | Zhang Sanfeng | 94| M || 3 | Miejue Shitai | 77| F || 4 | Lin Chaoying | 93| F |+-----+---------------+-----+--------+4 rows inset (0.00 sec)// COMMIT提交MariaDB [hellodb]>COMMIT;Query OK,0 rowsaffected (0.00 sec)// 提交后, 其他终端即可看到其插入的记录了.MariaDB [hellodb]>select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age| Gender |+-----+---------------+-----+--------+| 1 | Song Jiang| 45| M || 2 | Zhang Sanfeng | 94| M || 3 | Miejue Shitai | 77| F || 4 | Lin Chaoying | 93| F || 5 | wangj | 25| M |+-----+---------------+-----+--------+5 rows inset (0.00 sec)

注意：并不是所有操作都可撤销。

仅能撤销 DML 语句：INSERT，UPDATE，DELETE

无法撤销 DDL 语句：CREATE，DROP，ALTER

// 开启一个事务MariaDB [hellodb]> begin;Query OK,0 rows affected (0.00 sec)// 使用 DDL语句 删除某张表MariaDB [hellodb]> drop table students;Query OK,0 rows affected (0.00 sec)// 显示表 ( students 表已被删除 )MariaDB [hellodb]> show tables;+-------------------+| Tables_in_hellodb |+-------------------+| classes|| coc || courses|| scores || teachers|| testlog|| toc |+-------------------+7 rows inset (0.00 sec)// 执行事务撤销操作MariaDB [hellodb]> rollback;Query OK,0 rows affected (0.00 sec)// 并没有撤销成功MariaDB [hellodb]> show tables;

演示：

事务的提交操作对性能有显著影响, 特别是在涉及到大量数据操作的情况下.

// 清空表MariaDB[hellodb]> truncate table testlog;Query OK, 0 rows affected (0.01 sec)// 验证表MariaDB[hellodb]> select * from testlog;Empty set (0.00 sec)// 调用一个存储过程 sp_testlog 这个存储过程执行了大约 10 秒MariaDB[hellodb]> call sp_testlog;Query OK, 1 row affected (9.42 sec) // 耗时 10秒// 再次清空表MariaDB[hellodb]> truncate table testlog;Query OK, 0 rows affected (0.01 sec)// 开始一个事务执行同样的操作// 由于在事务开始后立即提交, 这个操作只用了 1 秒, 远快于单独调用存储过程的时间.// 这表明事务的提交操作对性能有显著影响, 特别是在涉及到大量数据操作的情况下.begin;call sp_testlog;commit;select count(*) from testlog; // 仅耗时 1 秒

事务支持保存点：

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

查看事务：

// 查看当前正在进行的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;// 注: 以下两张表在 MySQL8.0 中已取消// 查看当前锁定的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;// 查看当前等锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

死锁：

参考：MySQL – 死锁的产生及解决方案_mysql死锁的原因和处理方法-CSDN博客

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态。

死锁是指 2+的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

当然，涉及死锁并非一个严重的事故。数据库系统通常会自动解除其中一方的死锁重新执行该事务，这意味着数据库系统会自动选择其中一方撤销事务重新执行。

范例：找到未完成的导致阻塞的事务

// 在第一会话中执行MariaDB [hellodb]> begin;Query OK, 0 rows affected (0.000 sec)MariaDB [hellodb]> update students set classid=10;// 在第二个会话中执行MariaDB [hellodb]> update students set classid=20;// 在第三个会话中执行MariaDB [hellodb]> show engine innodb status;MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;// 查看正在进行的事务MariaDB [hellodb]> SELECT * FROM information_schema.INNODB_TRX\G*************************** 1. row ***************************trx_id: 123 trx_state: LOCK WAITtrx_started: 2019-11-22 19:17:06trx_requested_lock_id: 123:9:3:2trx_wait_started: 2019-11-22 19:18:50trx_weight: 2 trx_mysql_thread_id: 15 # 线程ID trx_query: update students set classid=20 trx_operation_state: starting index readtrx_tables_in_use: 1trx_tables_locked: 1trx_lock_structs: 2trx_lock_memory_bytes: 1136 trx_rows_locked: 2trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READtrx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_is_read_only: 0trx_autocommit_non_locking: 0*************************** 2. row ***************************trx_id: 120 trx_state: RUNNINGtrx_started: 2019-11-22 19:08:51trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 29 trx_mysql_thread_id: 13 # 线程ID trx_query: NULL trx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 1trx_lock_structs: 2trx_lock_memory_bytes: 1136 trx_rows_locked: 28trx_rows_modified: 27 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READtrx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_is_read_only: 0trx_autocommit_non_locking: 02 rows in set(0.000 sec)MariaDB [hellodb]> show processlist;// 杀掉未完成的事务MariaDB [hellodb]> kill 13;Query OK, 0 rows affected (0.000 sec)// 查看事务锁的超时时长, 默认 50sMariaDB [hellodb]> show global variableslike 'innodb_lock_wait_timeout';+--------------------------+-------+| Variable_name| Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50|+--------------------------+-------+1 row in set(0.001 sec)

4.5.3.3）事务隔离级别

MySQL 支持 四种隔离级别，事务隔离级别从上至下更加严格。

READ UNCOMMITTED

可读取到未提交数据，产生 脏读

READ COMMITTED

可读取到提交数据，未提交数据不可读，产生 不可重复读，即可读取到多个提交数据，导致每次读取数据不一致。

REPEATABLE READ

可重复读，多次读取数据都一致，产生 幻读，在读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为 MySQL 默认设置。

SERIALIZABLE

串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致 并发性能差。

参考：图解mysql事务的四个隔离级别 – 墨天轮（ MySQL 小技巧：事务的隔离级别）

脏读

比如有下图的用户表，表中只有一条数据，

A 事务第一次读这条数据，amount 为 100，没问题，

然后在这中间，有一个 B 事务修改了其中一条信息，将 amount改为 98，但是这时候 B 事务还没提交数据，

在这间隙里，A 事务又查询了一次数据，查到了 B 事务未提交的数据，

这就是 脏读；如果 A 事务将这个未提交的数据拿来使用，而事务 B 又回滚的话，将会出现很大的问题；会出现数据不一致的问题。

不可重复读

还是那一行数据；

A 事务第一次读这条数据，amount 为 100，没问题，

然后在这中间，有一个 B 事务修改了其中一条信息，将 amount改为 98，并且提交了 B 事务

A 事务在读这条数据时，得到的结果是 98，

同一个事务，两次查询到的数据不一样，如果有多次查询同一条数据，每次查询到的数据都不一样，你是不是会很抓狂？不可重复读的危害其实也不大，因为 oracle 默认的隔离级别就是不可重复读；所以 oracle 的效率要比 MySQL 高。

幻读（ MySQL 默认的事务隔离级别 ）

A 事务第一次查询所有的数据，结果只有一行，没问题。

然后在这中间，有一个 B 事务插入了一条新的数据；

A 事务再次查询所有数据，返回结果还是只有之前的一行数据，就像出现了幻觉一样；

可重复读，多次读取数据都一致，产生幻读，在读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为 MySQL 默认设置。

行锁 可以解决不可重复读的问题，但是不能解决幻读问题；如果要解决幻读，有 2 种方法

使用间隙锁

使用串行化隔离级别

幻读事务隔离级别的一个优势：

我可以基于该事务隔离级别做 MySQL 数据备份。即使备份需要一个周期。

并且这个周期之内 数据可能会被增删改，也没有关系。

这个事务的备份永远是以开始事务时的一个数据库状态进行备份。

串行化

Serializable安全性最高的隔离界别，效率也最慢，这种隔离级别就相当于是 表锁，每次修改数据时都会将整张表锁住，哎，那这不就是 myisam了吗？没错，串行化就是和 myisam一样了；串行化的隔离级别，在 java 中就和 synchronized 关键字一样了；每次修改的时候只能有一个线程进行修改，其他线程只能在外面排队等着；那么多人排成串在那等，效率肯定是最慢的了。

MySQL 设置可串行化隔离级别的 SQL 语句如下

setsessiontransactionisolationlevelserializable;// 查看隔离级别MariaDB [(none)]> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)

举例：

演示 MySQL 事务的默认隔离级别（ 幻读 ）

// 查看当前事务的隔离级别 ( 默认为: REPEATABLE-READ )MariaDB [hellodb]> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)// 会话 1 开启事务 AMariaDB [hellodb]> begin;Query OK, 0 rows affected (0.00 sec)// 会话 2 开启事务 B ( 需进入 hellodb 数据表 )MariaDB [hellodb]> begin;Query OK, 0 rows affected (0.00 sec)// 事务 A 查看数据表MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)// 事务 A 插入数据 ( 并没有 COMMIT 提交 )MariaDB [hellodb]> insert teachers(name,age)values('b',23);Query OK, 1 row affected, 1 warning (0.00 sec)// 事务 A 查看数据 ( 验证已正常插入成功 )MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 0 | b | 23 | NULL || 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F |+-----+---------------+-----+--------+5 rows in set (0.00 sec)// 事务 B 查看数据 ( 无法看到其插入记录 )MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)// 事务 A 提交记录MariaDB [hellodb]> COMMIT;Query OK, 0 rows affected (0.00 sec)// 事务 B 再次查看数据 ( 还是无法查看到其插入记录 )// 此时, 事务 B 其实就是经历了幻读 ( 其实数据已经发送了变化, 只是事务 B 看到的还是原始数据 )MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)

// 直到事务 B 执行 COMMIT 事务提交操作MariaDB [hellodb]> COMMIT;Query OK, 0 rows affected (0.00 sec)// 事务 B 才可正常看到事务 A 正常插入的数据啦.MariaDB [hellodb]> select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age| Gender |+-----+---------------+-----+--------+| 0 | b | 23| NULL || 1 | Song Jiang | 45| M || 2 | Zhang Sanfeng | 94| M || 3 | Miejue Shitai | 77| F || 4 | Lin Chaoying | 93| F |+-----+---------------+-----+--------+5 rows in set (0.01 sec)

举例：

修改 MySQL 事务的默认隔离级别

// 修改 MySQL 事务的默认隔离级别vim /etc/my.cnf[mysqld]transaction-isolation=READ-UNCOMMITTED// 重启 MySQL 服务systemctl restart mariadb// 验证 MySQL 事务的隔离级别MariaDB [hellodb]> select @@tx_isolation;+------------------+| @@tx_isolation |+------------------+| READ-UNCOMMITTED |+------------------+1 row in set (0.00 sec)

演示 MySQL 事务的隔离级别（ 脏读 ）

// 会话 1 开启事务 AMariaDB [hellodb]> begin;Query OK, 0 rows affected (0.00 sec)// 会话 2 开启事务 BMariaDB [hellodb]> begin;Query OK, 0 rows affected (0.00 sec)// 事务 A 查询表记录MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 0 | b | 23 | NULL || 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | MiejueShitai | 77 | F || 4 | Lin Chaoying | 93 | F |+-----+---------------+-----+--------+5 rows in set (0.00 sec)// 事务 A 删除一条行数据( 不执行 COMMIT 提交 )MariaDB [hellodb]> delete from teachers where tid=0;Query OK, 1 rowaffected (0.00 sec)// 事务 B 查看表内容 (可以看到被 A 事务删除且未 COMMIT 提交的执行结构 )// 即使 A 事务没有 COMMIT 提交, 事务 B 也能看到其结果 ( 隔离性差,不安全 )MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | MiejueShitai | 77 | F || 4 | Lin Chaoying | 93 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)

举例：

修改 MySQL 事务的默认隔离级别

// 修改 MySQL 事务的默认隔离级别vim /etc/my.cnf[mysqld]transaction-isolation=SERIALIZABLE// 重启 MySQL 服务systemctl restart mariadb// 验证 MySQL 事务的隔离级别MariaDB [hellodb]> select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| SERIALIZABLE |+----------------+1 row in set (0.00 sec)

演示 MySQL 事务的隔离级别（ 串行化 ）

串行化：并发性差，只能一个人进行读写操作。（ 少用 ）

// 会话 1 开启事务 AMariaDB [hellodb]> begin;Query OK, 0 rows affected (0.00 sec)// 会话 2 开启事务 BMariaDB [hellodb]> begin;Query OK, 0 rows affected (0.00 sec)// 事务 A ( 读取某张表数据 )MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 0 | b | 23 | NULL || 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | MiejueShitai | 77 | F || 4 | Lin Chaoying | 93 | F |+-----+---------------+-----+--------+5 rows in set (0.00 sec)// 事务 B ( 无法再对该表进行写操作, 因为该表已被事务 A 自动上锁 )select * from teachers; // 可读delete from teachers where tid=0; // 不可写// 现在事务 A ( 也准备删除该表的某行记录 )delete from teachers where tid=0; // 怎么事务 A 也无法删除啦~// 该命令执行完成后, 出现死锁状态 ( 刚才事务 B 也在读该表, 也上了一把锁导致出现了死锁状态 )( 不过没关系, MySQL数据库会随机将其中一个事务执行 ROLLBACK 操作"完成其中某个事务的请求" )// 事务 A ( 此时事务A 也无法读取数据 )MariaDB [hellodb]> select * from teachers;// 事务 B ( 执行 ROLLBACK 释放 )ROLLBACK;// 事务 A 才可正常执行操作.

MVCC 和事务的隔离级别：

MVCC（多版本并发控制机制）

只在 READ COMMITTED和 REPEATABLE READ两个隔离级别下工作。（ 不可重复读，幻读 ）

其他两个隔离级别 都和 MVCC 不兼容，因为 READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。（ 脏读，串行化 ）

指定事务隔离级别：

服务器变量 tx_isolation（ MySQL8.0 改名为 transaction_isolation ）指定

默认为 REPEATABLE-READ，可在 GLOBAL 和 SESSION 级进行设置。

// MySQL 8.0 之前版本SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'// MySQL 8.0SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'

服务器选项 中指定

vim /etc/my.cnf[mysqld]transaction-isolation=SERIALIZABLE

范例：MySQL8.0 事务隔离级别系统变量 tx_isolation 取消

mysql> select @@tx_isolation;ERROR 1193 (HY000): Unknown system variable 'tx_isolation'mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ |+-------------------------+1 row in set (0.00 sec)