MySQL进阶

发表于 分类于 阅读次数：

存储引擎

MySQL体系结构

image-20220714150546597

  • 连接层

    最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。

    在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。

    同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

  • 服务层

    第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。

    所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。

    如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

  • 引擎层

    存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。

    不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

    数据库中的索引是在存储引擎层实现的。

  • 存储层

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

SQL执行流程

image-20220714162837039

从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除

查询缓存

引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。

存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

建表时指定存储引擎

1
2
3
4
5
CREATE TABLE 表名(
    字段1 字段1类型 [ COMMENT 字段1注释 ] ,
    ......
    字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

修改存储引擎

1
ALTER TABLE 表名 ENGINE = 存储引擎名称;

查询当前数据库支持的存储引擎

1
SHOW ENGINES;

image-20220714151323425

查询默认使用引擎

1
show variables like '%storage_engine%';

image-20220714161858612

查询建表语句 

1
SHOW CREATE TABLE account;
1
2
3
4
5
6
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `money` double(10,2) DEFAULT NULL COMMENT '余额',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='账户表'

引擎特点

介绍三种存储引擎 InnoDB、MyISAM、Memory的特点。

InnoDB

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

  • 特点

    • DML操作遵循ACID模型，支持事务；
    • 支持行级锁，提高并发访问性能；
    • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

  • 文件

    xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的数据和索引。

    xxx.frm: 存储该表的表结构

  • 文件生成规则

    1
    SHOW VARIABLES LIKE 'innodb_file_per_table';

    image-20220714154232882

    如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。

  • 逻辑存储结构

    image-20220714154417247

    1
    2
    3
    4
    5
    6
    7
    8
    9
    - 表空间(Tablespace): InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。

    - 段(Segment): 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。

    - 区(Extent): 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。

    - 页(Page): 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

    - 行(Row): InnoDB存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(最后一次操作事务的ID 和 指针)。

MyISAM

MyISAM是MySQL早期的默认存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。

  • 特点

    • 不支持事务，不支持外键
    • 支持表锁，不支持行锁
    • 访问速度快

  • 文件

    表名.frm : 存储表结构

    表名.MYD : 存储数据 (MYData)

    表名.MYI : 存储索引 (MYIndex)

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

  • 特点
    • 内存存放
    • 同时 支持哈希（HASH）索引 和 B+树索引 （默认）
    • MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。

区别及特点

特点 InnoDB MyISAM Memory
存储限制 64TB 有 有
事务安全 支持 - -
锁机制 行锁 表锁 表锁
B+tree索引 支持 支持 支持
Hash索引 - - 支持
全文索引 支持(5.6版本之后) 支持 -
空间使用 高 低 N/A
内存使用 高 低 中等
批量插入 速度 低 高
支持外键 支持 - - 
1
2
3
其他见文档:
https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html
https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
  • MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。(日志/评论)
  • MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

索引概述

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引特点

优势 劣势
提高数据检索的效率，降低数据库的IO成本 索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构 描述
B+Tree索引 最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引） 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引) 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

不同的存储引擎对于索引结构的支持情况:

索引 InnoDB MyISAM Memory
B+tree索引 支持 支持 支持
Hash 索引 不支持 不支持 支持
R-tree 索引 不支持 支持 不支持
Full-text 5.6版本之后支持 支持 不支持

MySQL中平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

二叉树

若MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：

image-20220720170414537

如果主键是顺序插入的，则会形成一个单向链表，结构如下:

image-20220720170441014

所以，如果选择二叉树作为索引结构，会存在以下缺点：

  • 顺序插入时，会形成一个链表，查询性能大大降低。
  • 大数据量情况下，层级较深，检索速度慢。

红黑树

红黑树是一颗自平衡二叉树，那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树, 结构如下:

image-20220720170557822

由于红黑树也是一颗二叉树，所以也会存在一个缺点：

  • 大数据量情况下，层级较深，检索速度慢。

B-Tree

B树是一种多叉路平衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：

image-20220720170743164

树的度数指的是一个节点的子节点个数。

特点：

  • 5阶的B树，每一个节点最多存储4个key，对应5个指针。
  • 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
  • 在B树中，非叶子节点和叶子节点都会存放数据。

B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图：

image-20220720170909471

我们可以看到，两部分：

  • 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
  • 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree相比，主要有以下三点区别：

  • 所有的数据都会出现在叶子节点。
  • 叶子节点形成一个单向链表。
  • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL中的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

image-20220720171110939

Hash

MySQL中除了支持B+Tree索引，还支持一种索引类型-Hash索引。

结构

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

image-20220720171228366

特点

  • Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
  • 无法利用索引完成排序操作
  • 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

1
2
3
4
5
为什么InnoDB存储引擎选择使用B+tree索引结构?

- 相对于二叉树，层级更少，搜索效率高；
- 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
- 相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

分类 含义 特点 关键字
主键索引 针对于表中主键创建的索引 默认自动创建, 只能有一个 PRIMARY
唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个
全文索引 全文索引查找的是文本中的关键词，
而不是比较索引中的值		 可以有多个 FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类 含义 特点
聚集索引/聚簇索引(ClusteredIndex) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有,而且只有一个
二级索引/非聚簇索引(SecondaryIndex) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个 
1
2
3
4
5
聚集索引选取规则:

- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引

聚集索引和二级索引的具体结构如下：

image-20220720172132142

  • 聚集索引的叶子节点下挂的是这一行的数据 。
  • 二级索引的叶子节点下挂的是该字段值对应的主键值。

当执行SQL语句时，具体的查找过程:

image-20220720172245102

① 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

② 由于查询返回的数据是 *，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③ 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
InnoDB主键索引的B+tree高度为多高呢?

假设:
一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。

高度为2：
	n * 8 + (n + 1) * 6 = 16*1024 , 算出n为 1170
	1171* 16 = 18736
也就是说，如果树的高度为2，则可以存储 18000 多条记录。

高度为3：
	1171 * 1171 * 16 = 21939856
也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

索引语法

创建索引

1
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... );

查看索引

1
SHOW INDEX FROM table_name;

删除索引

1
DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。

通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

1
2
3
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

image-20220721103932087

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒)的所有SQL语句的日志。

查看系统变量 slow_query_log 确认MySQL的慢查询日志默认是否开启

1
SHOW VARIABLES LIKE 'slow_query_log';

image-20220721104940058

MySQL的慢查询日志默认没有开启，如果要开启慢查询日志，需要在MySQL的配置文件中配置信息：

1
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
1
2
3
4
5
6
# 开启MySQL慢日志查询开关
slow_query_log = 1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2
# 配置慢日志文件位置
slow_query_log_file     = /var/log/mysql/mysql-slow.log
1
$ sudo service mysql restart

image-20220721105637784

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

1
SHOW VARIABLES LIKE 'have_profiling';

image-20220721105804761

  • 开启配置

    可以通过set语句在 session/global 级别开启profiling：

    1
    SET profiling = 1;

    开关打开后，接下来所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。

  • 执行查询

  • 查看耗时

    执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

    1
    2
    3
    4
    5
    6
    7
    8
    -- 查看每一条SQL的耗时基本情况
    show profiles;

    -- 查看指定query_id的SQL语句各个阶段的耗时情况
    show profile for query query_id;

    -- 查看指定query_id的SQL语句CPU的使用情况
    show profile cpu for query query_id;

    示例:

    1
    show profiles;

    image-20220721110214454

    1
    show profile for query 17;

    image-20220721110307453

explain

EXPLAIN 或者 DESC 命令可以获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

1
2
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

image-20220721110551855

Explain 执行计划中各个字段的含义:

字段 含义
id select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
select_type 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。
possible_key 显示可能应用在这张表上的索引，一个或多个。
key 实际使用的索引，如果为NULL，则没有使用索引。
key_len 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
rows MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

索引使用

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

示例:

如在表tb_user中, 存在联合索引idx_user_pro_age_sta :

1
SHOW INDEX FROM tb_user;

image-20220721132803647

查询时，最左变的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

  • 索引生效

    • 所有字段均存在

      1
      explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

      image-20220721133048017

    • 部分(前置)字段存在

      1
      explain select * from tb_user where profession = '软件工程' and age = 31;

      image-20220721133152961

    • 仅前置字段存在

      1
      explain select * from tb_user where profession = '软件工程';

      image-20220721133233056

    • 所有字段存在但乱序

      1
      explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';

      image-20220721133801011

  • 索引失效

    • 前置字段不存在

      1
      explain select * from tb_user where age = 31 and status = '0';

      image-20220721133342311

    • 仅后置字段存在

      1
      explain select * from tb_user where status = '0';

      image-20220721133501680

  • 索引部分生效

    • 中间字段缺失

      1
      explain select * from tb_user where profession = '软件工程' and status = '0';

      image-20220721133632546

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

示例:

如在表tb_user中, 存在联合索引idx_user_pro_age_sta :

1
SHOW INDEX FROM tb_user;

image-20220721132803647

示例: 

1
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

image-20220721134127293

当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字段是没有走索引的。

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1). use index() ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

2). ignore index() ： 忽略指定的索引。

3). force index() ： 强制使用索引。

示例:

如在表tb_user中, 存在联合索引idx_user_pro_age_sta 和单列索引idx_user_pro:

1
SHOW INDEX FROM tb_user;

image-20220721160823707

1
explain select profession,status from tb_user where profession = '软件工程';

image-20220721161314776

可能使用两个索引, 实际使用了联合索引

  • 建议使用单列索引idx_user_pro

    1
    explain select profession,status from tb_user use index(idx_user_pro) where profession = '软件工程';

    image-20220721161515384

  • 建议忽略联合索引idx_user_pro_age_sta

    1
    explain select profession,status from tb_user ignore index(idx_user_pro_age_sta) where profession = '软件工程';

    image-20220721161605443

  • 强制使用单列索引idx_user_pro

    1
    explain select profession,status from tb_user force index(idx_user_pro) where profession = '软件工程';

    image-20220721161906966

覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。尽量使用覆盖索引，减少select *。

1
2
3
4
5
explain select id, profession from tb_user where profession = '软件工程' and age =31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程'and age = 31 and status = '0' ;

explain select id,profession,age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status= '0';

image-20220721162458786

从上述的执行计划我们可以看到，这四条SQL语句的执行计划前面所有的指标都是一样的，看不出来差异。但是后面的Extra，前面两条SQL的结果为 Using where; Using Index ; 而后面两条SQL的结果为: Using index condition 。

Extra 含义
Using where; Using Index 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using index condition 查找使用了索引，但是需要回表查询数据 
1
2
3
4
在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id。 
所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据。 
如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据，这个过程就是回表。 
而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。

  • 覆盖索引查询

    image-20220721163323961

    根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

    image-20220721163425667

    虽然是根据name字段查询，查询二级索引，但是由于查询返回字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。

  • 回表查询

    image-20220721163334823

    由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。

此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

1
2
-- 语法
create index idx_xxxx on table_name(column(n)) ;

示例:

为tb_user表的email字段，建立长度为5的前缀索引。

1
create index idx_email_5 on tb_user(email(5));

image-20220721163843961

前缀长度确认方式:

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

1
2
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;

前缀索引的查询流程:

image-20220721164037661

单列索引与联合索引

  • 单列索引：即一个索引只包含单个列。

  • 联合索引：即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

联合索引结构示意图:

image-20220721164146041

索引失效情况

索引列运算

不要在索引列上进行运算操作， 索引将失效。

示例:

如在表tb_user中, 存在联合索引idx_user_phone :

1
SHOW INDEX FROM tb_user;

image-20220721134332039

  • 当根据phone字段进行等值匹配查询时, 索引生效。

    1
    explain select * from tb_user where phone = '17799990015';

    image-20220721134440077

  • 当根据phone字段进行函数运算操作之后，索引失效。

    1
    explain select * from tb_user where substring(phone,10,2) = '15';

    image-20220721134526343

字段格式隐式转换

当字段参数发生隐式格式转换时, 索引失效, 如字符串类型字段使用时，不加引号。

示例:

字段phone为字符串格式varchar(11)

image-20220721134824566

  • 参数带引号, 无格式转换, 索引生效

    1
    explain select * from tb_user where phone = '17799990015';

    image-20220721134941190

  • 参数不带引号, 发生隐式格式转换, 索引失效

    1
    explain select * from tb_user where phone = 17799990015;

    image-20220721135035956

如果字符串不加单引号，对于查询结果没什么影响，但是数据库存在隐式类型转换，索引将失效。

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

  • 尾部模糊匹配, 索引生效

    1
    explain select * from tb_user where profession like '软件%';

    image-20220721135455388

  • 头部模糊匹配, 索引失效

    1
    explain select * from tb_user where profession like '%工程';

    image-20220721135541859

  • 首尾模糊匹配, 索引失效

    1
    explain select * from tb_user where profession like '%工%';

    image-20220721135614784

or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例:

如在表tb_user中, 存在主键索引 PRIMARY :

1
SHOW INDEX FROM tb_user;

image-20220721135847472

  • 条件涉及的列存在无索引列, 索引失效

    1
    explain select * from tb_user where id = 10 or age = 23;

    image-20220721140012154

  • 涉及的列全部存在索引, 索引生效

    1
    2
    3
    -- 创建涉及字段索引
    create index idx_user_age on tb_user(age);
    show index from tb_user;

    image-20220721140219109

    1
    explain select * from tb_user where id = 10 or age = 23;

    image-20220721140256554

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

示例:

所有数据profession字段都有值不为null

  • 查询所有profession字段为空的数据, 使用索引

    1
    explain select * from tb_user where profession is null;

    image-20220721155959975

  • 查询所有profession字段不为空的数据, 不使用索引, 全表扫描

    1
    explain select * from tb_user where profession is not null;

    image-20220721160141808

索引设计原则

  • 针对于数据量较大，且查询比较频繁的表建立索引。
  • 针对于常作为查询 条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  • 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  • 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  • 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  • 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

批量insert优化

如果需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化:

  • 批量插入数据

    1
    Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

  • 手动控制事务

    1
    2
    3
    4
    5
    start transaction;
    insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
    insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
    insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
    commit;

  • 主键顺序插入

    主键顺序插入性能高于乱序插入

    1
    2
    主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
    主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

image-20220722155109029

1
2
3
4
5
6
7
8
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/path/of/file/bigrecord.txt' into table tb_user fields terminated by ',' lines terminated by '\n' ;

主键优化

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

image-20220722155346362

行数据，都是存储在聚集索引的叶子节点上的。nnoDB的逻辑结构图如下：

image-20220722155410563

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。

那也就意味着一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。

  • 主键顺序插入效果

    ①. 从磁盘中申请页空间， 主键顺序插入

    image-20220722155558778

    ②. 第一个页没有满，继续往第一页插入

    image-20220722155613822

    ③. 当第一个页写满之后，再写入第二个页，页与页之间会通过指针连接

    image-20220722155633307

    ④. 当第二页写满了，再往第三页写入

    image-20220722155648842

  • 主键乱序插入效果

    ①. 现在1#,2#页都已经写满了，存放了如图所示的数据

    image-20220722155722396

    ②. 此时再插入id为50的记录

    索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。

    image-20220722155827798

    但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。

    image-20220722155920478

    image-20220722155924764

    移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。

    image-20220722155956105

    上述的这种现象，称之为 页分裂，是比较耗费性能的操作。

页合并

目前表中已有数据的索引结构(叶子节点)如下：

image-20220722160047317

当我们对已有数据进行删除时，具体的效果如下:

①当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

image-20220722160112220

②当我们继续删除2#的数据记录。当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

image-20220722160155705

image-20220722160207264

image-20220722160214662

这里面所发生的合并页的这个现象，就称之为 页合并。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键索引设计原则

  • 满足业务需求的情况下，尽量降低主键的长度。

  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

  • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

  • 业务操作时，避免对主键的修改。

order by优化

MySQL的排序，有两种方式：

  • Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  • Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

order by优化原则:

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  • 尽量使用覆盖索引。
  • 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能：

  • 在分组操作时，可以通过索引来提高效率。
  • 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

1
select * from tb_sku limit 2000000,10;

当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 条记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路:

一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

1
explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

count()是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

用法：count（*）、count（主键）、count（字段）、count（数字）

count用法 含义
count(主键) InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
count(字段) 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(数字) InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count(*) InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。

update优化

注意一下update语句执行时的注意事项。

1
update course set name = 'Tom' where id = 1 ;

当我们在执行上述的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时:

1
update course set name = 'Tom' where name = 'Jerry' ;

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

锁

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

在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

  • 全局锁：锁定数据库中的所有表。
  • 表级锁：每次操作锁住整张表。
  • 行级锁：每次操作锁住对应的行数据。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

image-20220723162259552

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。

1
2
3
4
5
6
7
8
9
-- 语法
-- 1). 加全局锁
flush tables with read lock ; 

-- 2). 数据备份
$ mysqldump -uroot –pxxxxxx ni9nes_DB > backup.sql 

-- 3). 释放锁
unlock tables ;

特点

数据库中加全局锁，存在以下问题：

  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本停摆。
  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。

1
$ mysqldump --single-transaction -uroot –pxxxxxx ni9nes_DB > backup.sql

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。

对于表级锁，主要分为以下三类：

  • **表锁 **
  • 元数据锁（meta data lock，MDL）
  • 意向锁

表锁

1
2
3
4
5
-- 语法：
-- 加锁：
lock tables 表名... read/write。
-- 释放锁：
unlock tables / 客户端断开连接 。

表锁分为两类：

  • 表共享读锁（read lock）

    image-20220723170341400

    image-20220723172030003

左侧为客户端一，对指定表加了读锁，不会影响右侧客户端二的读，但是会阻塞右侧客户端的写。

  • 表独占写锁（write lock）

    image-20220723172210146

    image-20220723172939442

    左侧为客户端一，对指定表加了写锁，不会影响左侧客户端一的读写，但是会阻塞右侧客户端二的读写。

结论:

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁

元数据锁 (meta data lock)，简写MDL。

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据(表结构)进行写入操作。避免DML与DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

常见的SQL操作时，所添加的元数据锁：

对应SQL 锁类型 说明
lock tables xxx read / write SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select … lock in share mode SHARED_READ 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert 、update、delete、select … for update SHARED_WRITE 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table … EXCLUSIVE 与其他的MDL都互斥

  • 当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ / SHARED_WRITE），之间是兼容的。

    加表锁

    image-20220723175819441

    事务select

    image-20220723180603661

    事务update/insert

    image-20220723181118939

  • 当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。

    image-20220723182042145

1
2
3
4
5
6
7
8
-- mysql默认没有开启查看MDL的表performance_schema.metadata_locks,执行以下命令开启记录
SELECT * FROM performance_schema.setup_instruments;  

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';

select * from performance_schema.metadata_locks;

意向锁

为了避免DML语句在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

1
2
3
4
5
6
7
假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢：
- 首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。
- 当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。

有了意向锁之后:
- 客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。
- 其他客户端在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

意向锁分为以下两种:

  • 意向共享锁(IS):

    由语句select ... lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。

  • 意向排他锁(IX):

    由insert、update、delete、select...for update添加 。与表锁共享读锁(read)及排他写锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交，意向共享锁(IS)、意向排他锁(IX)，都会自动释放。

  • 意向共享锁与表读锁是兼容的

    image-20220724234304083

  • 意向排他锁与表读锁、写锁都是互斥的

    image-20220724234546059

1
2
-- 可以通过以下SQL，查看意向锁及行锁的加锁情况：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

  • 行锁（Record Lock）：

    锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete。在RC、RR隔离级别下都支持。

    image-20220724232422025

  • 间隙锁（Gap Lock）：

    锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行 insert ，产生幻读。在RR隔离级别下都支持。

    image-20220724232430145

  • 临键锁（Next-Key Lock）：

    行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

    image-20220724232444047

行锁 REC_NOT_GAP

InnoDB实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
  • 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

image-20220724232604995

常见的SQL语句，在执行时，所加的行锁如下：

SQL 行锁类型 说明
INSERT … 排他锁 自动加锁
UPDATE … 排他锁 自动加锁
DELETE … 排他锁 自动加锁
SELECT … 不加任何锁
SELECT … LOCK IN SHARE MODE 共享锁 需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE 排他锁 需要手动在SELECT之后加FOR UPDATE 
1
2
-- 可以通过以下SQL，查看意向锁及行锁的加锁情况：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  • 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  • InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

兼容情况:

  • select...lock in share mode 加共享锁，共享锁与共享锁之间兼容。

    image-20220724235441614

  • 共享锁与排他锁之间互斥。

    image-20220724235608802

    客户端一获取的是id为1这行的共享锁，客户端二是可以获取id为3这行的排它锁的，因为不是同一行数据。行锁类型为记录锁(LOCK_REC_NOT_GAP), 所以没有阻塞

    image-20220724235744548

  • 排它锁与排他锁之间互斥

    image-20220725000054162

  • 不通过索引条件检索数据, 无索引行锁升级为表锁

    image-20220725000531033

    客户端一根据name字段进行更新时，name字段是没有索引的，如果没有索引，此时行锁会升级为表锁(因为行锁是对索引项加的锁，而name没有索引)。

间隙锁&临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  • 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 (GAP)。

    image-20220725002728436

  • 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。

    image-20220725030316992

    1
    2
    3
    4
    InnoDB的B+树索引，叶子节点是有序的双向链表。 
    假如，要根据这个二级索引查询值为user6的数据，并加上共享锁，只锁定user6这一行就可以了吗？ 
    并不是，因为是非唯一索引，这个结构中可能有多个user6的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是user7）。
    此时会对user6加临键锁，并对user7之前的间隙加锁。

  • 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止。

    image-20220725031316490

    1
    2
    3
    4
    5
    查询的条件为id>=6，并添加共享锁。 此时可以根据数据库表中现有的数据，将数据分为三个部分：
    [7]
    (7,15]
    (15,+∞]
    所以数据库数据在加锁是，就是将7加了行锁，15加了临键锁（包含15及15之前的间隙），正无穷加了临键锁(正无穷及之前的间隙)。

间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

InnoDB引擎

逻辑存储结构

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

image-20220714154417247

  • 表空间

    表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

  • 段

    段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），

    InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。段用来管理多个区。

  • 区

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

  • 页

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

  • 行

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

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

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

系统架构

概述

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

image-20220725165655143

内存结构

在左侧的内存结构中，主要分为四块： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 接下来介绍一下这四个部分:

  • Buffer Pool 缓冲池 

    1
    2
    3
    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’;

  • Change Buffer/Insert Buffer 更改缓冲区

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

    1
    2
    3
    Change Buffer的意义:
    与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。
    有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

  • Adaptive Hash Index 自适应hash索引

    用于优化对Buffer Pool数据的查询。

    MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了自适应hash索引。

    hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。

    InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

    自适应哈希索引，无需人工干预，是系统根据情况自动完成。
    参数： show variables like ‘adaptive_hash_index’;

  • Log Buffer 日志缓冲区

    用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

    1
    2
    3
    4
    5
    6
    参数:
    	innodb_log_buffer_size：缓冲区大小
    	innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：
    		1: 日志在每次事务提交时写入并刷新到磁盘，默认值。
    		0: 每秒将日志写入并刷新到磁盘一次。
    		2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

磁盘结构

image-20220725170906878

  • System Tablespace 系统表空间

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

    1
    2
    参数：
    	innodb_data_file_path

  • File-Per-Table Tablespaces

    如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中。即每创建一个表，都会产生一个表空间文件。

    1
    2
    参数：
    	innodb_file_per_table ，该参数默认开启。

  • General Tablespaces 通用表空间

    通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

    1
    2
    3
    4
    -- 创建表空间
    CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name; 
    -- 创建表时指定表空间
    CREATE TABLE xxx ... TABLESPACE ts_name;

  • Undo Tablespaces 撤销表空间

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

  • Temporary Tablespaces 临时表空间

    InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

  • Doublewrite Buffer Files 双写缓冲区

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

  • Redo Log 重做日志

    重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。

    当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。以循环方式写入重做日志文件，涉及两个文件：

    1
    2
    ib_logfile0
    ib_logfile1

后台线程

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

  • Master Thread 核心后台线程

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

  • IO Thread

    在InnoDB存储引擎中大量使用了异步IO(AIO)来处理IO请求, 这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。

    可以通过以下的这条指令，查看到InnoDB的状态信息，其中就包含IO Thread信息。

    1
    show engine innodb status \G;

    image-20220725171925735

    线程类型 默认个数 职责
    Read thread 4 负责读操作
    Write thread 4 负责写操作
    Log thread 1 负责将日志缓冲区刷新到磁盘
    Insert buffer thread 1 负责将写缓冲区内容刷新到磁盘

  • Purge Thread

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

  • Page Cleaner Thread

    协助 Master Thread 刷新脏页到磁盘的线程，可以减轻 Master Thread 的工作压力，减少阻塞。

事务原理

事务基础

1). 事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

2). 特性

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

对于这四大特性，实际上分为两个部分。 其中的原子性、一致性、持久性，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而隔离性是通过数据库的锁，加上MVCC来保证的。

image-20220725172358339

redo log 重做日志

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

redolog解决的问题: 

1
2
3
4
在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。 
当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。 
而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。 而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来。
这就出现问题了，没有保证事务的持久性。

image-20220725172726041

1
2
3
有了redo log之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。
过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。 
而如果脏页成功刷新到磁盘 或 或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的。

image-20220725172834250

1
2
3
4
5
那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢?

因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。 
而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。 
这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

undo log 回滚日志

回滚日志，用于记录数据被修改前的信息 , 作用包含 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制) 。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：

undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：

undo log采用段的方式进行管理和记录，存放在 rollback segment回滚段中，内部包含1024个undo log segment。

MVCC

基本概念

MVCC 全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。

MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

1
2
3
4
5
6
7
8
9
- 当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ...for update、update、insert、delete(排他锁)都是一种当前读。

- 快照读
简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
• Read Committed：每次select，都生成一个快照读。
• Repeatable Read：开启事务后第一个select语句才是快照读的地方。
• Serializable：快照读会退化为当前读。

隐藏字段

InnoDB引擎中, 当创建一张表时，除了显式声明的字段以外，InnoDB还会自动添加三个隐藏字段:

隐藏字段 含义
DB_TRX_ID 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段。

undolog 回滚日志

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

版本链

有一张表原始数据为：

image-20220725173850252

DB_TRX_ID : 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是自增的。
DB_ROLL_PTR ： 代表回滚指针, 由于这条数据是才插入的，没有被更新过，所以该字段值为null。

然后，有四个并发事务同时在访问这张表。生成版本链

image-20220725173926748

image-20220725173954540

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readview 读视图

ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。

ReadView中包含了四个核心字段：

字段 含义
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id ReadView创建者的事务ID

在readview中规定了版本链数据的访问规则：

  • trx_id == creator_trx_id 可以访问该版本, 说明数据是当前这个事务更改的。
  • trx_id < min_trx_id 可以访问该版本 ，说明数据已经提交了。
  • trx_id > max_trx_id 不可以访问该版本，说明该事务是在ReadView生成后才开启。
  • min_trx_id <= trx_id<= max_trx_id 如果trx_id不在m_ids中，是可以访问该版本的，说明数据已经提交。

trx_id 代表当前undolog版本链对应事务ID。

不同的隔离级别，生成ReadView的时机不同：

  • READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
  • REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

原理分析

  • RC隔离级别

    RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

    在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下:

    image-20220725175207478

    那么这两次快照读在获取数据时，就需要根据所生成的ReadView以及ReadView的版本链访问规则，到undolog版本链中匹配数据，最终决定此次快照读返回的数据。

    • 第一次快照读具体的读取过程：

      image-20220725175305169

      image-20220725175310951

      在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

      1. . 先匹配 30, 10, A3, 4, 0x0003这条记录

      这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中:

      ​ ①不满足 ②不满足 ③不满足 ④也不满足 ，

      都不满足，则继续匹配undo log版本链的下一条。

      1. . 再匹配第二条 0x0003| 30, 3, A3, 3, 0x0002

      这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中:

      ​ ①不满足 ②不满足 ③不满足 ④也不满足 ，

      都不满足，则继续匹配undo log版本链的下一条。

      1. . 再匹配第三条 0x0002| 30, 3, A30, 2, 0x0001

      这条记录对应的trx_id为2，也就是将2带入右侧的匹配规则中:

      ​ ①不满足 ②满足 终止匹配，

      此次快照读，返回的数据就是版本链中记录的这条数据。

    • 第二次快照读具体的读取过程:

      image-20220725175825200

      image-20220725175829186

      在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

      1. . 先匹配 30, 10, A3, 4, 0x0003这条记录

      这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中:

      ​ ①不满足 ②不满足 ③不满足 ④也不满足 ，

      都不满足，则继续匹配undo log版本链的下一条。

      1. . 再匹配第二条 0x0003| 30, 3, A3, 3, 0x0002

      这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中:

      ​ ①不满足 ②满足 终止匹配，

      此次快照读，返回的数据就是版本链中记录的这条数据。

  • RC隔离级别

    RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。

    image-20220725175956274

    在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返回的结果也是一样的。

MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。
MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。

MySQL管理

系统数据库

Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：

数据库 含义
mysql 存储MySQL服务器正常运行所需要的各种信息 （时区、主从、用户、权限等）
information_schema 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
performance_schema 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys 包含了一系列方便 DBA 和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图

常用工具

mysql客户端工具

1
2
3
4
5
6
7
8
语法 ：
	mysql [options] [database]
选项 ：
 -u, --user=name #指定用户名
 -p, --password[=name] #指定密码
 -h, --host=name #指定服务器IP或域名
 -P, --port=port #指定连接端口
 -e, --execute=name #执行SQL语句并退出

-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。

1
$ mysql -uroot –p123456 ni9nes_DB -e "select * from stu";

mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

1
2
3
4
5
6
7
8
9
10
通过帮助文档查看选项：
	mysqladmin --help
	
语法:
	mysqladmin [options] command ...
选项:
 -u, --user=name #指定用户名
 -p, --password[=name] #指定密码
 -h, --host=name #指定服务器IP或域名
 -P, --port=port #指定连接端口
1
2
$ mysqladmin -uroot –p123456 drop 'test01';
$ mysqladmin -uroot –p123456 version;

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。

1
2
3
4
5
6
7
8
9
语法 ：
	mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
 -d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
 -o, --offset=# 忽略掉日志中的前n行命令。
 -r,--result-file=name 将输出的文本格式日志输出到指定文件。
 -s, --short-form 显示简单格式， 省略掉一些信息。
 --start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
 --start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。
1
$ sudo mysqlbinlog -s -d ni9nes_DB /var/lib/mysql/binlog.000002

mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

1
2
3
4
5
语法 ：
	mysqlshow [options] [db_name [table_name [col_name]]]
选项 ：
 --count 显示数据库及表的统计信息（数据库，表 均可以不指定）
 -i 显示指定数据库或者指定表的状态信息
1
2
3
4
#查询test库中每个表中的字段书，及行数
$ mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
$ mysqlshow -uroot -p2143 test book --count

mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
语法 ：
	mysqldump [options] db_name [tables]
	mysqldump [options] --database/-B db1 [db2 db3...]
	mysqldump [options] --all-databases/-A
连接选项 ：
 -u, --user=name 指定用户名
 -p, --password[=name] 指定密码
 -h, --host=name 指定服务器ip或域名
 -P, --port=# 指定连接端口
输出选项：
 --add-drop-database 在每个数据库创建语句前加上 drop database 语句
 --add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
 -n, --no-create-db 不包含数据库的创建语句
 -t, --no-create-info 不包含数据表的创建语句
 -d --no-data 不包含数据
 -T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件(并不是记录一条条的insert语句，而是按照一定的格式记录表结构中的数据)
1
$ mysqldump -uroot -p1234 db01 > db01.sql

mysqlimport/source

  • mysqlimport

    mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。

    1
    2
    语法 ：
    	mysqlimport [options] db_name textfile1 [textfile2...]
    1
    $ mysqlimport -uroot -p2143 test /tmp/city.txt

  • source

    如果需要导入sql文件,可以使用mysql中的source 指令 :

    1
    source /root/xxxxx.sql

视图/存储过程/触发器/函数