数据库概念

关系型数据库（RDBMS）

概念

关系型数据库，如MySQL、Oracle、DB2、SQL Server等，是建立在关系模型的基础上的。这种数据库由多个相互关联的二维表组成。每个二维表，就像Excel的工作表一样，由行（代表记录）和列（代表数据字段）构成。表格中的每一行通常代表一个实体的实例，而列则表示实体的属性。关键的一点在于，这些表之间可以通过键（例如主键和外键）相互关联，使得不同表中的数据能够相互参照和整合。这样的设计允许数据库高效地组织、查询和维护大量 结构化 数据。

此外，关系型数据库与非关系型数据库（NoSQL）的主要区别在于数据存储和处理方式。关系型数据库依赖于预定义的模式和二维表来组织数据，而非关系型数据库可能使用更灵活的数据模型，如键值对、文档或图形结构，这使它们更适合处理非结构化或半结构化数据。

特点

结构化数据存储：使用规范化的表结构来组织数据，保证数据结构一致性和完整性。
SQL标准：通过统一的SQL语言进行数据查询和操作，实现高效的数据库管理。
事务处理支持：提供完整的事务处理机制，确保数据库操作的原子性和一致性。
索引和性能优化：利用索引来加速数据检索，采用各种优化策略提高数据库性能。

SQL语句分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限
TCL	Transaction Control Language	事务控制语言

MySql架构

连接层

客户端连接管理：负责管理客户端和服务器之间的连接。它接收客户端请求，并建立、管理、终止客户端到服务器的连接。
认证与安全：在此层进行用户认证，检查用户是否有权限连接到MySQL服务器，并对数据传输进行加密处理以保证安全。
连接池：优化资源使用，复用现有连接。

服务层

SQL解析：解析SQL查询，进行语法检查，生成解析树。
查询优化：优化器对查询进行优化，选择最有效的查询路径。
缓存：查询缓存可以存储SELECT语句及其结果，加速后续相同的查询过程。

引擎层

存储引擎：MySQL支持多种存储引擎，每种引擎都有其特点。例如，InnoDB支持事务处理、行级锁定和外键；MyISAM提供高速存储、全文搜索功能等。
事务管理：对于支持事务的存储引擎（如InnoDB），在此层进行事务的管理。
数据处理：执行具体的数据读写、缓存、锁定等操作。

存储层

数据文件：物理存储数据的文件，包括数据文件和索引文件。
数据组织：数据按照存储引擎的格式在磁盘上进行组织，例如InnoDB存储引擎使用表空间来存储数据。
数据备份与恢复：负责数据的备份和在系统崩溃后的数据恢复。

存储引擎

存储引擎是数据库管理系统中用于实现 数据存储、索引建立 以及 数据更新 和 查询 等功能的底层软件组件。存储引擎是基于表的，而非基于整个数据库（库）。这意味着在同一个数据库中，不同的表可以使用不同的存储引擎，根据它们各自的需求优化性能和功能。因此，存储引擎也可以被称为 表类型。

在创建表时，可以明确指定要使用的存储引擎。如果在创建表时没有指定存储引擎，MySQL将使用默认的存储引擎，这通常是InnoDB，因为它提供了全面的事务支持、数据完整性和性能优化。

指定存储引擎

MySql5.6以后，如果不指定存储引擎，则默认使用 InnoDB

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

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

1	`show engines;`

MyISAM

特性

不支持事务
不支持外键
仅支持表级锁
全文索引
数据压缩
高速读取：读取性能比写入性能好，特别是在读密集型的应用中

存储文件

.frm 存储表结构定义，如字段名称、数据类型和其他与表结构相关的细节。
.MYD 存储表的实际数据。.MYD 文件包含了表中的行数据，但不包含任何索引信息。
.MYI 专门用于存储表的索引。这个文件包含了所有用于加速查询的索引信息。

故障恢复

健康状态查询 check table table_name
表损坏修复 repair table table_name

存储格式

静态表（默认）：所有字段都有固定长度。常见的静态字段类型包括CHAR、INT、FLOAT等。
动态表：包含可变长度字段，如VARCHAR、BLOB和TEXT。
压缩表：MyISAM表可以转换为压缩表，以节省空间并提高读取效率，只能读取，不能插入。

读写互斥

写操作锁定：当执行写操作时，MyISAM 会锁定整个表。这意味着，在写操作完成之前，其他任何读或写操作都必须等待。
写锁优先级高于读锁：在默认情况下，MyISAM倾向于优先赋予写操作锁。这意味着，如果读写操作同时到达，写操作将优先获得锁，这是为了防止写锁饥饿。

全文索引

不支持中文

MyISAM存储引擎支持全文索引（Full-Text Indexes），这是一种特别为文本搜索设计的索引类型。全文索引提供了一种比传统的LIKE查询更快、更高效的方式来搜索大量文本数据。

特点

快速文本搜索：全文索引允许对文本内容进行快速搜索，尤其是在处理大量数据时，它比LIKE模糊查询更高效。
支持复杂查询：全文索引支持自然语言搜索、布尔搜索等复杂查询，能够处理词组、否定词等。
索引更新：当表中的文本数据被插入、更新或删除时，全文索引会自动更新。

# 查询最小和最大搜索长度
show variables like '%ft%';

ft_max_word_len 84
ft_min_word_len 4

最小搜索长度 (ft_min_word_len)：这个变量定义了全文索引中词的最小长度。长度小于这个值的词将不会被包含在全文索引中。默认值通常是4。
最大搜索长度 (ft_max_word_len)：这个变量定义了索引中词的最大长度。长度超过这个值的词将不会被包含在全文索引中。

CREATE TABLE `my_isam` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  `address` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into my_isam(name, age, email, address)
values ('test1', 18, 'test1@test.com', 'shenzhen'),
       ('test2', 19, 'test2@test.com', 'beijing'),
       ('test3', 20, 'test3@test.com', 'shanghai'),
       ('test4', 21, 'test4@test.com', 'shandong'),
       ('test5', 22, 'test5@test.com', 'hunan'),
       ('test6', 23, 'test6@test.com', 'changsha');

# 全文索引
create fulltext index idx_full on my_isam(address)

自然语言全文索引

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索

# match 可以多个字段，类似es的搜索
select *
from my_isam
where match(address) against('shen')

shen zhen

布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索

+ 必须包含该词
- 必须不包含该词
> 提高该词的相关性，查询的结果靠前
< 降低该词的相关性，查询的结果靠后
(*)星号 通配符，只能接在词后面

select *
from my_isam
where match(address) against('sh*' in boolean mode)

shan dong
shang hai
shen zhen

InnoDB

事务 行锁 间隙锁 外键 MVCC 自动生成hash索引

从MySQL 5.5版本开始，InnoDB成为默认的存储引擎。InnoDB提供全面的 ACID 事务支持，是MySQL中实现这一关键数据库特性的首要存储引擎之一。此外，InnoDB支持 行级锁定（Row-level Locking）和 多版本并发控制（MVCC），这两种机制共同提高了数据库在高并发环境下的读写性能和一致性。

特性

ACID兼容事务：InnoDB提供完整的 ACID 事务支持。这意味着它可以保证即使在系统崩溃或宕机的情况下，数据库操作也能保持一致性和完整性。
行级锁定：行级锁定允许数据库在执行事务时只锁定影响的数据行，而不是整个表，这显著提高了多用户环境中的并发性能。
MVCC：MVCC支持高效的读写分离，减少了读写操作之间的冲突（快照读和当前写）。
外键约束：InnoDB支持外键约束，这有助于维护不同表之间的参照完整性，自动处理相关表的更新和删除操作。
崩溃恢复：通过使用redo log（重做日志）和undo log（回滚日志），InnoDB能够在系统崩溃后恢复到一致状态，保证数据不丢失。
聚簇索引：InnoDB使用聚簇索引，将数据行直接存储在主键索引的叶节点中。这种方式可以加快主键查询的速度和便于范围查找。

ACID

Atomicity（原子性）
- 含义：原子性确保事务中的所有操作要么全部完成，要么全部不执行。事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败回滚。
- 例子：在银行系统中，从一个账户转账到另一个账户涉及两个操作：从第一个账户扣款和向第二个账户存款。这两个操作必须同时成功或同时失败。
Consistency（一致性）
- 含义：一致性确保事务从一个一致的状态转变到另一个一致的状态。一致状态的定义是基于业务规则，确保数据库的数据和结构在事务前后都保持一致。
- 例子：继续上面的银行转账例子，一致性确保在转账过程中账户的总额保持不变。
Isolation（隔离性）
- 含义：隔离性确保并发事务的执行彼此独立，事务之间不会互相影响。这意味着事务对数据的修改在提交之前，对其他事务是不可见的。
- 例子：当两个银行客户同时检查同一个账户余额时，他们看到的应该是不受对方事务影响的稳定视图。
Durability（持久性）
- 含义：持久性确保一旦事务提交，它对数据库的更改就是永久性的。即使发生系统故障，事务的结果也不会丢失。
- 例子：在银行转账事务提交后，即使系统崩溃，转账的结果（资金的移动）也会被永久保存。

存储文件类型

.frm ：用于存储表的结构信息，包括表的定义，如字段名称、类型、大小等。
.ibd：是一个完整的表空间文件，其中最基本的单位为页(Page)，一个Page通常为16k(存放聚簇索引)。
redo log（重做日志）：通常为.ib_logfile0和.ib_logfile1，这些文件用于记录事务操作，以便在数据库崩溃后进行数据恢复。
undo log（回滚日志）：记录了数据的旧版本，用于回滚事务和MVCC。
表空间
- inodb_data_file_path：用于定义InnoDB共享表空间文件的名称和大小，例如innodb_data_file_path=ibdata1:10M:autoextend
- innodb_data_home_dir：定义了InnoDB表空间文件的默认位置。

1	`show variables like 'innodb_file_per_table';`

文件-per-table 模式：当innodb_file_per_table被设置为ON（或1），InnoDB为每个新创建的表单独创建一个.ibd文件。这个文件包含了该表的数据和索引。
共享表空间：如果innodb_file_per_table被设置为OFF（或0），所有的InnoDB表都会共享一个大的表空间文件（通常是ibdata1）。

1	`idb2sid xxx.idb`

通过以上命令可以查看ibd文件内容，以JSON形式输出

逻辑存储结构

InnoDB存储引擎在MySQL中使用一种 层级化 的方式来组织数据，这种结构包括表空间、段、区、页和行。下面是这些组件的详细解释：

表空间（Tablespace）: 表空间是InnoDB存储结构的最顶层，可以看作是存储数据的容器。它可以是单个文件（如在文件-per-table模式下的.ibd文件），也可以是共享的大文件（如ibdata1）。表空间包含 数据、索引、插入缓冲区、undo log 等数据结构。
段 （Segment）: 段是表空间内的一个逻辑部分，用于管理特定类型的数据。例如，数据段用于存储实际的表数据，索引段用于存储索引数据。每个段由多个区（Extent）组成。
区（Extent） : 区是表空间的一个分配单元。在InnoDB中，一个区是一组连续的页，通常包含64个页。区的大小固定，由页的大小决定（默认为1MB，基于16KB大小的页）
页（Page） : 页是InnoDB存储结构中的基本单元，也是磁盘I/O操作的最小单位。每个页默认为16KB大小。页按照不同的类型存储不同的信息，如数据页、索引页、undo页等。
行（Row） : 行是InnoDB的最小数据单位。InnoDB是面向行的存储引擎，这意味着数据按行来组织和存储。每个数据页包含多个行记录。

三大特性

插入缓冲（Change Buffer ）
双写缓冲区（Double Write Buffer）
自适应哈希索引（Adaptive Hash Index）

MEMORY

Hash索引 字段定长 表级锁 不支持大字段 数据存储内存，重启丢失

Memory存储引擎使用存在内存中的内容来创建表，每个Memory表只实际对应一个磁盘文件，在磁盘中表现为.frm文件。Memory类型的表访问速度非常快，因为它的数据是放在内存中的，并且默认使用hash索引，但是一旦服务关闭，表中的数据就会丢失。

特性

基于内存的存储：MEMORY引擎将数据存储在内存中，提供快速的数据访问速度，尤其适用于需要高速读取的场景。
数据易失性：MEMORY表的内容在数据库服务器重启后会丢失，因此主要用于临时数据，如缓存或会话信息。
索引类型：
- 支持 HASH索引，默认使用，适合快速的等值查询。
- 支持 B+Tree索引，适用于范围查询。
固定长度：MEMORY引擎中所有字段都被视为固定长度。例如，即使定义为VARCHAR(10)，也会像CHAR(10)一样处理，占用固定空间。
不支持大型字段：不支持BLOB和TEXT类型的字段，因为这些类型的数据通常较大，不适合存储在内存中。
表级锁：MEMORY引擎使用表级锁定机制，这可能在高并发写操作场景中成为性能瓶颈。
表大小限制：表的最大大小由系统变量max_heap_table_size决定，默认值通常是16MB。这个限制只对新创建的表有效。

MERGE

MyISAM表组合

MERGE存储引擎提供了一种将多个相同结构的MyISAM表合并为一个虚拟表的机制。这种引擎对于处理大量数据或分区数据特别有用。

特性

组合多个表：MERGE引擎允许多个具有相同表结构（即相同的列和索引）的MyISAM表被合并为一个单一的虚拟表。这些实际的表被称为子表，而合并后的表被称为MERGE表。
查询优化：通过合并表，可以在一个查询中同时访问多个子表的数据，这对于执行大规模查询非常有效。
数据管理和维护：MERGE表本身不存储数据，数据仍然存储在子表中。这意味着对子表的更新会直接反映在MERGE表上。
只读或可写：默认情况下，MERGE表是只读的，但是如果所有子表都具有相同的结构，并且MERGE表被显式地声明为可写，则可以在MERGE表上执行插入、更新和删除操作。

数据库基础

数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
`TINYINT`	1 Bytes	(-128，127)	(0，255)	小整数值
`SMALLINT`	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
`MEDIUMINT`	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
`INT`或`INTEGER`	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
`BIGINT`	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
`FLOAT`	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
`DOUBLE`	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
`DECIMAL`	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	`DECIMAL(M，D)`表示列可以存储`D`位小数的`M`位数，DECIMAL(8,4)表示能存储8位数，其中包含四位小数	依赖于M和D的值	小数值

int(5)：对于 int(5) 这样的声明，数字5表示展示宽度，而不是最大值。展示宽度并不限制值的范围，而是影响值的显示格式。
zerofill：当字段声明为 ZEROFILL 时，MySQL会用零填充展示值，以使其符合指定的展示宽度。例如，INT(5) ZEROFILL会将数值123格式化为00123。
无符号UNSIGNED：UNSIGNED 属性用于指定整数类型为无符号。无符号整数只能存储正数和零。例如，INT UNSIGNED的范围是0到4294967295。

日期时间类型

类型	大小 ( bytes)	范围	格式	用途
`DATE`	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
`TIME`	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
`YEAR`	1	1901/2155	YYYY	年份值
`DATETIME`	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
`TIMESTAMP`	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
`CHAR`	0-255 bytes	定长字符串
`VARCHAR`	0-65535 bytes	变长字符串
`TINYBLOB`	0-255 bytes	不超过 255 个字符的二进制字符串
`TINYTEXT`	0-255 bytes	短文本字符串
`BLOB`	0-65 535 bytes	二进制形式的长文本数据数据空洞
`TEXT`	0-65 535 bytes	长文本数据数据空洞
`MEDIUMBLOB`	0-16 777 215 bytes	二进制形式的中等长度文本数据
`MEDIUMTEXT`	0-16 777 215 bytes	中等长度文本数据
`LONGBLOB`	0-4 294 967 295 bytes	二进制形式的极大文本数据
`LONGTEXT`	0-4 294 967 295 bytes	极大文本数据

CHAR：定长字符串，如果有空位则用空串补位，性能较好
VARCHAR：性能较差，需要计算长度

函数

MySQL中的函数可以被理解为一组预定义的操作，这些操作封装了特定的逻辑或计算过程，使得用户可以在SQL查询中直接调用它们。这意味着用户无需编写复杂的代码来执行常见的数据库任务。函数在数据库中被设计为易于使用，并且是为了解决特定的业务或数据处理需求。

数值函数

功能	描述
ABS	返回数字的绝对值
ACOS	返回数字的反余弦值
ASIN	返回数字的反正弦值
ATAN	返回一个或两个数字的反正切
ATAN2	返回两个数字的反正切
AVG	返回表达式的平均值
CEIL	返回> =到数字的最小整数值
CEILING	返回> =到数字的最小整数值
COS	返回数字的余弦值
COT	返回数字的余切
COUNT	返回select查询返回的记录数
DEGREES	将弧度值转换为度数
DIV	用于整数除法
EXP	返回e提升到指定数字的幂
FLOOR	返回<=到数字的最大整数值
GREATEST	返回参数列表的最大值
LEAST	返回参数列表的最小值
LN	返回数字的自然对数
LOG	返回数字的自然对数，或数字的对数到指定的基数
LOG10	返回数字的自然对数到10
LOG2	返回数字2的自然对数
MAX	返回一组值中的最大值
MIN	返回一组值中的最小值
MOD	返回数字的余数除以另一个数字
PI	返回PI的值
POW	返回被提升到另一个数的幂的数字的值
POWER	返回被提升到另一个数的幂的数字的值
RADIANS	将度数值转换为弧度
RAND	返回一个随机数
ROUND	将数字舍入到指定的小数位数
SIGN	返回数字的符号
SIN	返回数字的正弦值
SQRT	返回数字的平方根
SUM	计算一组值的总和
TAN	返回数字的正切值
TRUNCATE	将数字截断为指定的小数位数

日期函数

功能	描述
ADDDATE	将时间/日期间隔添加到日期，然后返回日期
ADDTIME	将时间间隔添加到时间/日期时间，然后返回时间/日期时间
CURDATE	返回当前日期
CURRENT_DATE	返回当前日期
CURRENT_TIME	返回当前时间
CURRENT_TIMESTAMP	返回当前日期和时间
CURTIME	返回当前时间
DATE	从日期时间表达式中提取日期部分
DATEDIFF	返回两个日期值之间的天数
DATE_ADD	将时间/日期间隔添加到日期，然后返回日期
DATE_FORMAT	格式化日期
DATE_SUB	从日期中减去时间/日期间隔，然后返回日期
DAY	返回给定日期的月中的某天
DAYNAME	返回给定日期的工作日名称
DAYOFMONTH	返回给定日期的月中的某天
DAYOFWEEK	返回给定日期的工作日索引
DAYOFYEAR	返回给定日期的一年中的某一天
EXTRACT	从给定日期提取部分
FROM_DAYS	从数字日期值返回日期
HOUR	返回给定日期的小时部分
LAST_DAY	提取指定日期的月份的最后一天
LOCALTIME	返回当前日期和时间
LOCALTIMESTAMP	返回当前日期和时间
MAKEDATE	根据年份和天数值创建并返回日期
MAKETIME	根据小时，分钟和秒值创建并返回时间
MICROSECOND	返回时间/日期时间的微秒部分
MINUTE	返回时间/日期时间的分钟部分
MONTH	返回给定日期的月份部分
MONTHNAME	返回给定日期的月份名称
NOW	返回当前日期和时间
PERIOD_ADD	在一段时间内添加指定的月数
PERIOD_DIFF	返回两个句点之间的差异
QUARTER	返回给定日期值的一年中的季度
SECOND	返回时间/日期时间的秒部分
SEC_TO_TIME	返回基于指定秒数的时间值
STR_TO_DATE	返回基于字符串和格式的日期
SUBDATE	从日期中减去时间/日期间隔，然后返回日期
SUBTIME	从日期时间中减去时间间隔，然后返回时间/日期时间
SYSDATE	返回当前日期和时间
TIME	从给定的时间/日期时间中提取时间部分
TIME_FORMAT	按指定格式格式化时间
TIME_TO_SEC	将时间值转换为秒
TIMEDIFF	返回两个时间/日期时间表达式之间的差异
TIMESTAMP	返回基于日期或日期时间值的日期时间值
TO_DAYS	返回日期和日期“0000-00-00”之间的天数
WEEK	返回给定日期的周数
WEEKDAY	返回给定日期的工作日编号
WEEKOFYEAR	返回给定日期的周数
YEAR	返回给定日期的年份部分
YEARWEEK	返回给定日期的年和周数

字符串函数

功能	描述
ASCII	返回特定字符的ASCII值
CHAR_LENGTH	返回字符串的长度（以字符为单位）
CHARACTER_LENGTH	返回字符串的长度（以字符为单位）
CONCAT	一起添加两个或多个表达式
CONCAT_WS	将两个或多个表达式与分隔符一起添加
FIELD	返回值列表中值的索引位置
FIND_IN_SET	返回字符串列表中字符串的位置
FORMAT	将数字格式化为“＃，###，###。##”等格式，舍入到指定的小数位数
INSERT	在指定位置的字符串中插入一个字符串，并插入一定数量的字符
INSTR	返回第一次出现在另一个字符串中的字符串的位置
LCASE	将字符串转换为小写
LEFT	从字符串中提取多个字符（从左开始）
LENGTH	返回字符串的长度（以字节为单位）
LOCATE	返回字符串中第一次出现子字符串的位置
LOWER	将字符串转换为小写
LPAD	左边用另一个字符串填充一个字符串到一定长度
LTRIM	从字符串中删除前导空格
MID	从字符串中提取子字符串（从任何位置开始）
POSITION	返回字符串中第一次出现子字符串的位置
REPEAT	按指定的次数重复一次字符串
REPLACE	使用新的子字符串替换字符串中所有出现的子字符串
REVERSE	反转字符串并返回结果
RIGHT	从字符串中提取多个字符（从右侧开始）
RPAD	右边用另一个字符串填充一个字符串到一定长度
RTRIM	从字符串中删除尾随空格
SPACE	返回指定数量的空格字符的字符串
STRCMP	比较两个字符串
SUBSTR	从字符串中提取子字符串（从任何位置开始）
SUBSTRING	从字符串中提取子字符串（从任何位置开始）
SUBSTRING_INDEX	在指定数量的分隔符出现之前返回字符串的子字符串
TRIM	从字符串中删除前导和尾随空格
UCASE	将字符串转换为大写
UPPER	将字符串转换为大写

聚合函数

函数	说明
COUNT()	没有数据时默认值是0
SUN()	没有数据时，默认值是null
MAX()	求最大值
MIN()	求最小值
AVG()	求平均值
GROUP_CONCAT()	这个函数把来自同一个组的某一列（或者多列）的数据连接起来成为一个字符串(逗号分隔)
JSON_ARRAYAGG()	select json_arrayagg(degree) from score group by cno 将结果集聚合为单个JSON数组
JSON_OBJECTAGG(key,value)	select JSON_OBJECTAGG(sno, degree) from score group by sno 第一个用作键，第二个用作值，并返回包含键值对的JSON对象
HAVING	对分组后的结果再进行条件过滤
WITH ROLLUP	select cno, count(0) from score group by cno with rollup 对分类聚合后的结果再汇总，再结果追加一条所有汇总数据,(count(0) 再汇总)

流程函数

功能	描述
IF	如果条件为TRUE则返回值，如果条件为FALSE则返回另一个值
IFNULL	如果表达式为NULL，则返回指定的值，否则返回表达式

其他常用函数

功能	描述
DATABASE	返回当前数据库的名称
VERSION	返回MySQL数据库的当前版本
USER	返回当前的MySQL用户名和主机名
UUID	生成UUID
MD5	MD5加密

CASE 语法

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

CASE第一种用法，指定字段

select CASE site
WHEN 'MX' THEN '墨西哥'
WHEN 'ES' THEN '西班牙'
WHEN 'US' THEN '美国'
ELSE '其他' END
from site

CASE第二种用法，满足条件

select CASE
WHEN site_name = '墨西哥' THEN 'MX'
WHEN site_name = '西班牙' THEN 'ES'
ELSE '其他' END
from site

SQL语句优先级

from
join
on
where
group by
with rollup
having
select
distinct
order by
limit

WITH ROLLUP

WITH ROLLUP 用于在 GROUP BY 操作中生成额外的汇总行，它不仅返回常规的分组聚合结果，还会为每个分组级别以及总计提供聚合数据。使用WITH ROLLUP可以方便地获取数据的层级汇总，从而避免编写额外的查询来获取这些汇总信息。

WITH … AS …

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用

注意：

相当于一个临时表，但是不同于视图，不会存储起来，要与select配合使用。
同一个select前可以有多个临时表，写一个with就可以，用逗号隔开，最后一个with语句不要用逗号。
with子句要用括号括起来。

# 查看 3-105 课程的最高分和最低分
with score_temp as (select * from score where cno = '3-105')
select max(degree) as degree from score_temp
union all
select min(degree) as degree from score_temp

约束

约束（CONSTRAINT ）是数据库表中字段上应用的规则，用于确保数据库表中数据的有效性和完整性。

约束类型	描述	关键字
非空约束	确保字段中的值不能为NULL，即字段必须有值。	NOT NULL
唯一约束	保证表中一个字段或字段组合的值是唯一的，防止重复记录的产生。	UNIQUE
主键约束	同时具备非空和唯一性约束，用于唯一标识表中的每条记录。	PRIMARY KEY
默认约束	为字段指定一个默认值，如果插入记录时未提供值，则使用此默认值。	DEFAULT
检查约束	确保字段值必须满足特定的条件，只有满足条件的值才被允许插入或更新。	CHECK
外键约束	用于在两个表之间建立关联，确保一个表中的数据引用另一个表中的数据，从而维护跨表的数据完整性。	FOREIGN KEY

检查约束

检查约束在MySQL中直到8.0.16版本才被完全支持。

1 2	`ALTER TABLE students ADD CONSTRAINT age_check CHECK (age >= 18 AND age <= 30);`

添加一个检查约束，年龄只能在[18,30]之间。

DDL

DDL（Data Definition Language，数据定义语言）是SQL语言中用于定义和修改数据库结构的一部分。DDL主要包括用于创建、修改、删除 数据库、表、索引 等数据库对象的命令。

数据库操作

查询所有数据库
1
show databases;
查询当前数据库
1
select database();

创建数据库

#create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ];
# 排序规则决定了字符串之间如何比较
# 排序规则影响ORDER BY子句的结果
# 使用默认的utf8_general_ci，不区分大小写
# 若要区分大小写，需要修改为utf8_bin

create database test_db default charset utf8mb4_general_ci;

删除数据库

1	`drop database [ if exists ] 数据库名;`

切换数据库
1
use 数据库名;

表操作

查询当前数据库所有表
1
show tables;
查看指定表结构
通过这条指令，我们可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信息
1
desc 表名;
查询指定表的建表语句
1
show create table 表名;

创建表结构

CREATE TABLE 表名(
    字段1 字段1类型 [ COMMENT 字段1注释 ],
    ...
    ) [ COMMENT 表注释 ];

create table tb_user(
    id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别'
) comment '用户表';

修改表名

1	`ALTER TABLE 表名 RENAME TO 新表名;`

删除表
1
DROP TABLE [ IF EXISTS ] 表名;
删除指定表, 并重新创建表
1
TRUNCATE TABLE 表名;
TRUNCATE TABLE 命令用于快速删除表中的所有行，同时保留表结构。这个命令相比于使用 DELETE 命令删除所有行更为高效，特别是在处理大型表时。
和 DELETE 比较：
- 性能：对于大型表，TRUNCATE TABLE 比 DELETE 快。
- 事务和日志：DELETE 是事务安全的，可以回滚；而 TRUNCATE TABLE 不是事务安全的，不能针对单个行操作进行回滚。
- 自增计数器：DELETE 不会重置自增计数器，而 TRUNCATE TABLE 会。

字段操作

添加字段

1	`ALTER TABLE 表名 ADD 字段名类型 (长度) [ COMMENT 注释 ] [ 约束 ];`

1	`ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';`

修改字段类型

1	`ALTER TABLE 表名 MODIFY 字段名新数据类型 (长度);`

修改字段名和字段类型

1	`ALTER TABLE 表名 CHANGE 旧字段名新字段名类型 (长度) [ COMMENT 注释 ] [ 约束 ];`

1	`ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';`

删除字段
1
ALTER TABLE emp DROP username;

DML

DML（Data Manipulation Language，数据操纵语言）是SQL（Structured Query Language，结构化查询语言）的一部分，用于修改数据。

添加数据（INSERT）
修改数据（UPDATE）
删除数据（DELETE）

隐式事务：

默认行为：在InnoDB存储引擎中，默认情况下，事务是隐式的。这意味着当执行一个 INSERT、UPDATE 或 DELETE 操作时，如果当前没有活跃的事务，则MySQL自动为这个操作开始一个新的事务。
自动提交：每个独立的SQL语句都被视为一个事务。除非手动开始一个新的事务，否则执行完这些语句后，事务会自动提交。
自动回滚：如果在执行过程中遇到错误，事务可能会被自动回滚。

添加数据

给指定字段添加数据

1	`INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);`

给表所有的字段添加数据

1	`INSERT INTO 表名 VALUES (值1, 值2, ...);`

批量添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES 
(值1, 值2, ...), 
(值1, 值2, ...),
(值1, 值2, ...);

INSERT INTO 表名 VALUES 
(值1, 值2, ...),
(值1, 值2, ...),
(值1, 值2, ...);

查询添加

1 2	`INSERT INTO table2 SELECT * FROM table1;`

1 2	`INSERT INTO table2(field1,field2,...) SELECT field1,field2,... FROM table1 ...`

修改数据

修改数据的具体语法

1
2
3

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

联表修改

联表操作不建议使用，A join B B join A 会产生死锁

1
2
3

update course c join teacher t on (c.tno = t.tno)
set c.cname = '计算机导论'
where t.tname = '王萍'

删除数据

删除数据

1	`DELETE FROM table_name WHERE condition;`

联表删除

1
2
3

delete t # 定义起作用的表
from teacher t join course c on (t.tno = c.tno)
where c.cname = 'JAVA';

DQL

DQL（Data Query Language，数据查询语言）是SQL（Structured Query Language，结构化查询语言）的一个组成部分，专门用于查询数据库中的数据。在实际应用中，DQL基本上是由SELECT语句组成的，它允许用户从数据库中检索数据，而不会对数据本身进行任何修改。

查询关键字: SELECT

基本语法

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基础查询

在基本查询的DQL语句中，不带任何的查询条件，查询的语法如下：

查询多个字段

1	`SELECT 字段1, 字段2, 字段3 ... FROM 表名;`

1	`SELECT * FROM 表名 ;`

字段设置别名

1	`SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;`

1	`SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名; 1`

去除重复记录

1	`SELECT DISTINCT 字段列表 FROM 表名;`

条件查询

语法

1	`SELECT 字段列表 FROM 表名 WHERE 字段1 [条件1] [查询条件1] [逻辑运算符] 字段2 [条件2] [查询条件2];`

条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围之内，区间 [min, max]
IN(…)	多值查询
LIKE 占位符	模糊查询
IS NULL	判空查询

常用的逻辑运算符
逻辑运算符 功能
AND 或 && 并且 (多个条件同时成立)
OR 或或者 (多个条件任意一个成立)
NOT 或 ! 非 , 不是

聚合查询

使用聚合函数将一列数据作为一个整体，进行纵向计算。

1	`SELECT 聚合函数(字段列表) FROM 表名 ;`

1	`SELECT count(0) FROM user;`

分组查询

语法

1	`SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];`

where 与 having 区别
- 执行时机不同
  - where是分组之前进行过滤，不满足where条件，不参与分组
  - having是分组之后对结果进行过滤
- 判断条件不同
  - where不能对聚合函数进行判断
  - 而having可以对聚合函数进行判断

# 查询45元以下商品价位的总数，不展示少于3款产品的价位
select price, count(*) price_count 
from product 
where price < 45 
group by price 
having price_count >= 3;

注意事项
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
• 执行顺序: where > 聚合函数 > having
• 支持多字段分组, 具体语法为 : group by columnA，columnB

排序查询

语法

1	`SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2;`

排序方式
- ASC ：升序（默认值，可缺省）
- DESC：降序
- Rand()：随机排序

分页查询

1	`SELECT 字段列表 FROM 表名 LIMIT [offset, rows];`

注意事项:
offset：数据的偏移量，（查询页码 - 1）* 每页显示记录数
rows：要从偏移量开始取多少条数据
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

DCL

DCL（Data Control Language，数据控制语言）主要用于管理数据库中的访问权限和安全性。DCL包括一系列的命令，允许数据库管理员控制用户对数据库对象的访问权限。

查询用户

1	`select * from mysql.user;`

Host列代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

用户操作

在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户

主机名可以使用 % 通配

创建用户

1	`CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';`

1 2	`# 创建用户admin, 可以在任意主机访问该数据库, 密码123456; create user 'admin'@'%' identified by '123456';`

修改用户密码

1	`ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';`

删除用户
1
DROP USER '用户名'@'主机名' ;

权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

上述只是简单罗列了常见的几种权限描述，其他权限描述及含义，可以直接参考官方文档

查询权限

1	`SHOW GRANTS FOR '用户名'@'主机名';`

1	`show grants for 'admin'@'%';`

授予权限

1	`GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';`

1	`grant all on itcast.* to 'admin'@'%';`

撤销权限

1	`REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';`

1	`revoke all on itcast.* from 'admin'@'%';`

多个权限之间，使用逗号分隔

授权时，数据库名和表名可以使用 * 进行通配，代表所有

TCL

TCL（Transaction Control Language，事务控制语言）用于管理数据库中的事务。

开启事务
1
BEGIN;
提交事务
1
COMMIT;
回滚事务
1
ROLLBACK;

多表查询

多表查询是SQL中的一项关键技术，它允许从两个或多个表中组合数据。这种查询通常通过在这些表之间的共同字段上建立关联来实现。通过多表查询，可以获得跨表的综合视图。

在数据库设计中，表与表之间的关系大致分为三种：

一对一
- 描述：每个表中的一行与另一个表中的一行相关联。
- 实现：通常通过在一个表中包含另一个表的主键作为外键来实现。
- 示例：用户表和用户详细信息表，其中每个用户只有一组详细信息。
一对多
- 描述：一个表中的一行与另一个表中的多行相关联。
- 实现：在“多”的一方表中包含“一”的一方表的主键作为外键。
- 示例：客户表和订单表，其中一个客户可以有多个订单，但每个订单只能属于一个客户。
多对多
- 描述：一个表中的多行与另一个表中的多行相关联。
- 实现：通常通过一个单独的关联表（或桥接表）实现，该表包含两个表的主键作为外键。
- 示例：学生表和课程表，其中一个学生可以参加多个课程，一个课程可以有多个学生参加。

笛卡尔积

定义：当两个表进行交叉连接（CROSS JOIN）时，产生的结果集是这两个表的笛卡尔积。这意味着结果集中的每一行是由第一个表中的一行和第二个表中的一行组合而成的。
行为：如果表A有M行，表B有N行，它们的笛卡尔积将包含M×N个结果。

内连接

内连接查询的是两张表交集部分的数据

内连接用于从两个或多个表中选择符合连接条件的记录。内连接仅返回两个表中关联键匹配的行。如果在一个表中的行在另一个表中没有匹配的行，则这些行不会出现在查询结果中。

隐式内连接

1	`SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;`

1	`select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;`

显式内连接

1	`SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;`

1	`select e.name, d.name from emp e join dept d on (e.dept_id = d.id);`

外连接

外连接用于从两个表中检索数据，即使在另一个表中没有匹配的行也能返回结果。外连接可以是左连接（LEFT JOIN）或右连接（RIGHT JOIN）。

外连接是以自我为中心的

左连接

左连接返回左表（LEFT JOIN左边的表）的所有行，以及右表中匹配的行。如果在右表中没有匹配的行，则结果中右表的部分将为NULL。

1	`SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 条件 ... ;`

右连接

右连接与左连接相反，返回右表的所有行，以及左表中匹配的行。如果在左表中没有匹配的行，则结果中左表的部分将为NULL。

1	`SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 条件 ... ;`

自连接

自连接（Self Join）是一种特殊类型的连接，用于将一个表与自身进行连接。这在处理具有 层级 或 递归 关系的数据时特别有用，例如，当一个表中的记录引用了同一个表中的其他记录。自连接可以是内连接、左连接或右连接。

1	`SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;`

on 和 where 的区别

执行顺序
- ON 子句在进行表的连接操作时先执行。
- WHERE 子句在 JOIN 操作之后执行。
如果是 内连接 则条件放在 ON 和 WHERE 是一样的。
如果是 外链接 ON 过滤主表条件不成立，WHERE 过滤主表条件成立。

1
2
3

select *
from a left join b on(a.id=b.id and a.id > 10)
# 此时a.id > 10 条件不成立

联合查询

对于union查询，就是把多次查询的结果合组合起来，形成一个新的查询结果集，每个查询的列的数量、顺序、数据类型必须一致。

1
2
3

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

UNION ALL

UNION ALL 用于合并多个 SELECT 查询的结果集，但它包括所有重复的行。

假设 student 表有6个学生，直接合并结果集：

select sno
from student
union all
select sno
from student
# 返回12条 sno, 每条重复

UNION

UNION 用于合并多个 SELECT 查询的结果集，并且默认去除重复的行。

select sno
from student
union
select sno
from student
# 返回6条 sno, 不重复

子查询

在 SQL 语句中，嵌套的 SELECT 语句称为子查询。子查询可以嵌入在 SELECT、INSERT、UPDATE 或 DELETE 语句中。

例如：

1	`SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );`

子查询分类

标量子查询：
- 返回单个值的子查询。
- 通常用在条件表达式中，如 WHERE 子句或 SELECT 列表中。
列子查询：
- 返回一列数据的子查询。
- 常用于 IN 或 ANY/SOME 子句中。
行子查询：
- 返回一行数据的子查询。
- 可用于比较多个列，通常与行构造函数一起使用。
表子查询：
- 返回多行多列的子查询。
- 常见于 IN 子句、EXISTS 子句或者作为 FROM 子句中的一个表。

子查询位置

在 SELECT 之后：
- 作为计算列的一部分，或者在 SELECT 列表中为列赋值。
在 FROM 之后：
- 作为一个临时表使用，在主查询中用于联合、过滤或聚合。
在 WHERE 之后：
- 作为条件筛选，与主查询表的数据进行比较或关联。
在 INSERT/UPDATE/DELETE 语句中：
- 在 INSERT INTO ... SELECT 结构中用于提供要插入的数据。
- 在 UPDATE ... SET column = (SELECT ...) 结构中为列赋值。
- 在 DELETE FROM ... WHERE column1 in (SELECT ...) 结构中用于指定删除条件。

标量子查询

标量子查询在执行时返回单个值（即一个标量值）。这种子查询可以嵌入在更大的 SQL 查询中的多个位置，比如 SELECT、WHERE 或 HAVING 子句中。

特性

单值返回：
- 标量子查询的主要特点是它只返回一个单一的值，而不是一列或多列的数据集。
- 如果子查询返回多于一个值，则 SQL 会报错。
用途广泛：
- 可以用在几乎任何需要单一值的地方，例如在 SELECT 列表中，或者作为 WHERE 或 HAVING 子句中的条件。
与主查询的关系：
- 可以独立于主查询执行，也可以引用主查询中的数据。

用法

独立执行

1	`select * from emp where dept_id = (select id from dept where name = '销售部');`

引用主查询中的数据

SELECT 
    e.name, 
    e.salary, 
    (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS avg_department_salary
FROM 
    employees e;

列子查询

列子查询返回一个列的数据，而不是单个值或多行数据。这种子查询通常用于 IN、NOT IN、ANY、SOME 或 ALL 子句中，与主查询的条件进行比较。

操作符	描述
`IN`	检查某个值是否在子查询或提供的值列表中。常用于确定一个值是否属于列表中的某个值。
`NOT IN`	检查某个值是否不在子查询或提供的值列表中。用于排除列表中的值。
`ANY`	与比较运算符结合使用，检查是否至少有一个子查询返回的值满足比较条件。
`SOME`	功能与 `ANY` 相同，与比较运算符结合使用，用于检查是否有子查询返回的值中的某些值满足比较条件。
`ALL`	与比较运算符结合使用，确保子查询返回的所有值都满足比较条件。

根据部门ID, 查询员工信息

1	`select * from emp where dept_id in (select id from dept where name in ('销售部','市场部'));`

比财务部所有人工资都高的员工信息

1 2	`select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );`

比研发部其中任意一人工资高的员工信息

1 2	`select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );`

行子查询

行子查询比较一个值组合（即一行数据）与子查询返回的一行或多行数据。这种类型的查询通常用于处理涉及多个列的复杂条件。

常用的操作符：= 、<> 、IN 、NOT IN等。

查询与 “张三” 的薪资及直属领导相同的员工信息

1 2	`select * from emp where (salary,managerid) = (select salary, managerid from emp where name = 'wgf');`

表子查询

表子查询，也称为派生表（Derived Tables），是一种在SELECT语句的FROM子句中使用的子查询。这种子查询返回一个临时表，可以在外部查询中像普通表一样使用。

SELECT customer_id, COUNT(*) as total_orders, AVG(amount) as avg_amount
FROM (
    SELECT customer_id, amount
    FROM orders
) AS order_summary
GROUP BY customer_id;

in 和 exists 的区别

IN 子句用于检查某个值是否存在于由子查询或值列表提供的集合中。
当使用 IN 时，MySQL首先计算子查询，将所有结果存储在一个临时表中，外层查询数据的每行都需要判断是否包含在子查询结果中。
在子查询返回的结果集较小的情况下更高效，因为整个结果集需要被加载和存储在内存中。

1	`select * from A where id in (select id from B)`

EXISTS

EXISTS 子句用于检查子查询是否返回至少一行数据。
使用 EXISTS 时，MySQL对外层查询的每一行执行子查询。只要子查询返回第一行数据，就会停止检查，因为它只关心是否存在符合条件的行。
通常在子查询与外层查询紧密相关，或当子查询可能返回大量数据时更有效，因为它不需要处理整个子查询的结果集。

1	`select * from A where exists (select 1 from B where B.id = A.id)`

选择

子查询返回少量数据：IN
子查询返回大量数据：EXISTS

事务

MySQL中的事务是一系列操作，这些操作作为一个整体执行，要么全部成功，要么全部失败。事务是数据库管理的重要特性，确保数据的完整性和一致性。MySQL中的事务主要通过以下四个关键属性（ACID属性）来保障：

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。如果事务的一部分操作失败，整个事务会回滚到开始状态，如同这些操作从未发生过一样。
一致性（Consistency）：事务确保数据库从一个一致的状态转换到另一个一致的状态。
隔离性（Isolation）：事务的执行不应受其他事务的干扰。多个同时运行的事务之间是相互隔离的，每个事务对数据的修改在提交之前对其他事务不可见。
持久性（Durability）：一旦事务被提交，它对数据库的修改是永久性的，即使发生系统故障也不会丢失。

事务控制

查看/设置事务提交方式
1
2
SELECT @@autocommit; SET @@autocommit = 0;
自动提交事务：
- 默认 开启 自动提交事务。
- 每个单独的SQL语句（如 INSERT, UPDATE, DELETE 等）会立即执行并提交。这意味着，一旦语句执行，其更改就是永久性的，不能回滚。
- 如果你想在单个操作中执行多个语句，必须显式地开始一个新事务，并在所有操作完成后提交它。
开启事务
1
START TRANSACTION 或 BEGIN ;
提交事务
1
COMMIT;
回滚事务
1
ROLLBACK;

并发事务问题

脏读

事务B读取到事务A未提交的数据

定义：脏读发生在一个事务读取了另一个事务未提交的数据。
举例：事务A修改了一行数据但还未提交，这时事务B读取了这个未提交的数据。如果事务A回滚，事务B读取的数据就是不正确的，因为这些数据从未被实际提交到数据库中。

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

定义：不可重复读发生在一个事务读取了相同的行两次，但在两次读取之间，另一个事务修改了该行并提交，导致两次读取的数据不一致。
举例：事务A读取了一行数据，事务B修改了这行数据并提交。当事务A再次读取同一行数据时，数据已经改变。

幻读

插入检查时不存在，插入又冲突

定义：新增或删除操作，在一个事务内执行一个范围查询时，然后插入或删除满足条件的行，事务提交发现数据冲突。
举例：事务A根据某个条件查询数据范围，而事务B在这个范围内插入了新的数据并提交。当事务A再次执行相同的范围查询时，会发现之前不存在的“幻”数据。

事务隔离级别

隔离级别（+:允许出现，-:不允许出现）	脏读	不可重复读	幻读
未提交读	+	+	+
提已交读	-	+	+
可重复读	-	-	+
序列化读	-	-	-

事务隔离级别	实现方式
读未提交（RU）	事务可以读取未提交的数据。不使用行锁来防止对读取的数据的修改，允许脏读。
读已提交（RC）	事务只能读取已提交的数据。使用行锁，读取数据时加锁，读取完毕后立即释放锁，防止脏读，但允许不可重复读。
可重复读（RR）	事务读取数据时，对读取的数据加行锁直到事务结束，防止脏读和不可重复读。在MySQL中，还使用间隙锁来防止幻读。
串行化（S）	事务读取或修改数据时，使用表锁，直到事务结束。这避免了脏读、不可重复读和幻读，但可能导致性能问题。

数据库进阶

视图

视图是一个虚拟表，它基于一个或多个实际表的查询结果。视图本身不包含数据，而是保存了一个SQL查询，这个查询动态地生成数据。

狭义理解：视图就是对sql逻辑的封装。

优点：

安全性：视图可以隐藏底层表的某些列和行，仅展示必要的数据，这有助于增强数据的安全性。
简化SQL操作：如果你经常需要执行复杂的查询（例如包含多个表的JOIN操作），可以将这些复杂的查询封装在视图中，简化后续的查询操作。
逻辑数据独立：视图提供了一种逻辑上的数据独立。应用程序可以只依赖视图，而不是实际的表结构。这意味着即使底层数据结构发生变化，只要视图保持不变，应用程序也不需要修改。
重用SQL语句：视图允许重用SQL语句。你可以创建一个视图来封装复杂的SQL逻辑，然后在多处重用这个视图。

语法

创建

1 2	`CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED \| LOCAL ] CHECK OPTION ]`

查询

1 2	`查看创建视图语句：SHOW CREATE VIEW 视图名称; 查看视图数据：SELECT * FROM 视图名称 ...... ;`

修改

使用 OR REPLACE 对原视图进行覆盖

方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH
[ CASCADED | LOCAL ] CHECK OPTION ]

方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |
LOCAL ] CHECK OPTION ]

删除

1	`DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...`

索引
视图不能创建索引，但是能使用原表上的索引

# 创建视图
create view score_view as (select * from score where degree >= 90);

# 使用视图
select *
from score_view;

# 修改视图
create or replace view score_view as (select * from score where degree >= 90);

检查选项

WITH CHECK OPTION 是在创建或更改视图时使用的子句，用于确保所有通过视图进行的数据修改操作（如INSERT、UPDATE、DELETE）都符合视图定义的条件。

目的：保证视图的完整性，防止通过视图进行的数据修改操作破坏视图定义的规则。

1 2	`# 创建视图 create view v1 as (select * from score where degree >= 90);`

这个视图中可以插入 degree 大于90 的数据，但是视图查询不出来，因为这个视图没有声明检查选项。

1 2	`# 创建视图 create view v2 as (select * from score where degree >= 90) WITH CASCADED CHECK OPTION;`

这个视图中不可以插入 degree 大于90 的数据，在插入时会进行检查，因为声明了检查选项。

CASCADED 级联

这是默认选项。当设置为 CASCADED 时，WITH CHECK OPTION 会检查所有基础视图的约束。换句话说，不仅当前视图的定义被用于检查数据的合法性，所有依赖的视图（如果当前视图是基于其他视图创建的）也会进行检查。

LOCAL 本地

当设置为 LOCAL 时，WITH CHECK OPTION 仅检查当前视图的定义条件，不考虑任何基础视图的约束。

视图的更新

为了使视图可更新，需要视图中的每一行都与基础表中的行存在一对一的关系。这意味着对视图的更新可以无歧义地映射到基础表上。以下是影响视图可更新性的一些关键因素：

聚合函数或窗口函数：使用了如 SUM() , MIN() , MAX() , COUNT() 等函数的视图不可更新，因为这些操作改变了行与基础表的直接对应关系。
DISTINCT：使用 DISTINCT 关键字的视图不可更新，因为 DISTINCT 可能会消除重复行，从而破坏行与基础表行之间的一对一关系。
GROUP BY 和 HAVING：这些子句通常与聚合操作一起使用，它们改变数据的展示方式，使得视图中的行不再直接对应于基础表中的行，因此这样的视图不可更新。
UNION 或 UNION ALL：由于 UNION 或 UNION ALL 合并了来自多个表或多个查询的结果，这可能导致行与基础表中行之间的对应关系不明确，因此包含这些操作的视图通常不可更新。

触发器

自动化操作：触发器是一种特殊类型的存储过程，它不是由直接调用执行的，而是在特定的数据库表事件发生时自动触发。这些事件通常包括INSERT、DELETE和UPDATE操作。
数据完整性和自动化：触发器常用于保持数据的完整性和一致性，例如自动更新表中的数据，或检查插入/更新的数据是否满足特定的条件。
事件驱动：触发器是事件驱动的。当与触发器关联的表发生特定操作时，触发器中定义的SQL语句会被执行。

触发阶段

BEFORE：在数据操作之前。
AFTER：在数据操作之后。

触发条件

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发

条件	NEW 和 OLD
INSERT	NEW表示将要或者已经新增的数据
UPDATE	OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据
DELETE	OLD表示将要或者已经删除的数据

使用

语法

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt ;
END;

查看
1
SHOW TRIGGERS;

删除

1	`DROP TRIGGER [schema_name.]trigger_name;`

INSERT

CREATE TRIGGER test_insert AFTER INSERT ON test1 FOR EACH ROW
BEGIN
    INSERT INTO test2 ( id, test_name )
    VALUES( new.id, new.test_name );
END;

UPDATE

CREATE TRIGGER test_update AFTER UPDATE ON test1 FOR EACH ROW
BEGIN
    UPDATE test2 set test_name = new.test_name where id = old.id;
END;

DELETE

CREATE TRIGGER test_delete AFTER DELETE ON test1 FOR EACH ROW
BEGIN
    DELETE FROM test2 WHERE id = old.id;
END;

分区

分区键必须是主键的一部分

MySQL从5.1版本开始支持表的分区。分区是一种数据库架构技术，它允许将一个大型表或索引分解为多个较小、更易于管理的部分，这些部分称为分区。

特点：

物理分离：从应用程序的角度来看，仍然只操作一个逻辑表或索引。但在底层，这个逻辑表或索引实际上可能被分成许多物理分区。
独立性：每个分区可以独立于其他分区进行处理。这意味着可以单独优化、备份或维护每个分区，而不影响整个表。

分区的好处

提高查询性能：对于大型表，特别是当查询可以限制在特定的几个分区中时，分区可以显著提高查询性能。这是因为查询操作只需要扫描相关分区的数据而不是整个表。
并行处理：不同的分区可以在不同的处理器上并行进行查询处理，特别是针对分区键进行的大型扫描或聚合查询。这种并行性能够显著提高查询的处理速度，从而增加吞吐量。
提高数据管理的效率：数据管理任务，如删除和归档数据，可以通过简单地删除整个分区来高效完成，这比逐行删除数据要快得多。

分区的限制和缺点

分区键限制：如果表有主键或唯一键，则分区键必须包含主键的一部分和唯一键所有列。这意味着你不能随意选择分区键，必须考虑表的主键或唯一键设计。
跨分区操作：跨多个分区的查询和事务会比在单个分区内的操作性能差。
数据分布：不均匀的数据分布可能导致分区效果不理想。例如，某些分区可能数据量非常大，而其他分区则数据量很小，这可能会影响查询和维护操作的性能。
NULL值处理：如范围分区，NULL值的处理可能会导致数据分配到不预期的分区，需要特别注意。

分区和分表的区别

库的限制：分区只能在一个数据库内进行；分表可以在同一个库分表，也可以跨库分表。
水平扩展：跨库分表还提供水平扩展能力，水平扩展 CPU, I/O 资源。
实现方式：分区由数据库层实现，使用简单；分表由服务层实现，增加系统复杂性。

对已存在表创建分区

添加分区

RANGE 分区

分区范围：左开右闭

RANGE 分区允许根据指定列的值范围来分配行到不同的分区。这种方法非常适合于那些数据值分布在一个可预知范围内的场景，如日期、时间、状态码等，这个范围遵循左开右闭的规则。

CREATE TABLE sales (
    id INT NOT NULL AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id, sale_date)			   -- 分区键必须是主键的一部分
)
PARTITION BY RANGE( YEAR(sale_date) ) (
    PARTITION p0 VALUES LESS THAN (1991),  -- 包括1990年及之前的数据
    PARTITION p1 VALUES LESS THAN (1992),  -- 包括1991年的数据
    PARTITION p2 VALUES LESS THAN (1993),  -- 包括1992年的数据
    PARTITION p3 VALUES LESS THAN MAXVALUE -- 包括1993年及之后的数据
);


# 查看表的分区信息
select * from information_schema.partitions where table_schema=database() and table_name='sales'

使用场景

适用于数据有明确的连续范围，如 日期、时间、数值 等。
常用于时间序列数据，例如日志、交易记录。
便于数据归档和删除，例如，快速删除某个日期范围的数据。

LIST 分区

分区列不能插入分区不存在的值 适合分区键不会变的场景

LIST分区允许根据列值的列表将数据行分配到不同的分区中。LIST 分区非常适合于列值是离散的且 预先已知 的情况，例如状态码、地区代码、部门ID等。

工作原理：定义了一组值的列表，并为每组值指定一个分区。每行数据根据分区键的值被分配到相应的分区中。

# 上商品信息按照店铺分区
CREATE TABLE product (
  id INT NOT NULL,
  store_id INT not null,
  primary key(id, store_id)
)
PARTITION BY LIST(store_id)(
  PARTITION p0 VALUES IN (1,2),
  PARTITION p1 VALUES IN (3,4),
  PARTITION p2 VALUES IN (5,6)，
  PARTITION p3 DEFAULT  # 默认分区，5.7支持。其他数据会添加到此分区
)

假设上面未设置默认分区：在 LIST 分区中，如果尝试插入一个行，其分区键的值不在任何已定义的分区值列表中，该插入操作将失败，并显示错误。

1
2
3

insert into product(id, store_id)
values (1, 20);
#[1526] 找不到分区错误

适用场景

当数据的分区键列具有一组明确且离散的值，如 状态、类型、地区代码 等。
用于分类数据，便于管理特定类别或组的数据。
当数据行可以基于列值的列表明确分组时。

HASH 分区

支持自定义函数 PARTITION BY HASH(customer(id))

Hash分区根据用户定义的表达式将数据行分配到分区中。在 HASH 分区中，MySQL使用用户提供的表达式对每行数据的分区键值进行哈希处理，然后基于哈希值将数据行分配到特定的分区。

原理：在HASH分区中，分区键的哈希值是通过一个用户定义的表达式计算得出的。MySQL将这个哈希值对分区的数量取模，得到的结果决定了数据行应该存储在哪个分区。

create table store
(
    id      int not null,
    area_id int not null,
    primary key (id, area_id)
)
    PARTITION BY HASH (area_id)
        PARTITIONS 4;

优缺点

优点

均匀的数据分布：HASH分区通常能够确保数据在各个分区之间较为均匀地分布。
简化查询：对于基于分区键的查询，HASH分区可能提高查询性能。

缺点

不适合范围查询：对于涉及分区键的范围查询，HASH 分区没有性能优势，因为数据行可能分布在所有分区中。
依赖分区表达式：分区的有效性高度依赖于哈希表达式。如果表达式不合适，分区可能不会均匀。

适用场景

当没有明显的分区范围或列表，且需要均匀分布数据时。
对于分区键的值分布均匀，且查询模式不依赖于特定范围或列表的场景。
通常用于负载均衡，尤其是在数据行没有明显的逻辑分组时。

KEY 分区

KEY分区是一种基于哈希的分区策略，它使用MySQL内置的哈希函数对一列或多列（分区键）的值进行哈希处理，然后根据这些哈希值将数据行分配到不同的分区。与 HASH分区相比，KEY 分区的特点是它使用MySQL的哈希函数而不是用户定义的表达式。

create table tb_user (
    id int not null,
    area varchar(20) not null ,
    primary key (id, area)
)
PARTITION BY KEY(area)
PARTITIONS 5;

适用场景

类似于HASH分区，但专用于MySQL环境，使用MySQL的哈希函数。
适用于需要均匀数据分布但不希望自定义哈希函数的场景。
常用于分布式场景中，以均衡存储和查询负载。

COLUMNS 分区

COLUMNS分区许根据一个或多个列的值直接进行分区。这种分区类型适用于无法使用普通的范围（RANGE）或列表（LIST）分区处理的数据类型，如字符串或日期时间类型。

工作原理：

基于一列或多列的值进行分区。与 RANGE 和 LIST 分区类似，COLUMNS 分区允许你为每个分区指定一个 值范围 或 值列表。
不同于 RANGE 和 LIST 分区只能用于整数类型（Hash值），COLUMNS 分区可以用于非整数类型的列，如字符串、日期等。

create table user_log(
    id int not null,
    create_time datetime
)
PARTITION BY RANGE COLUMNS (create_time) (
  PARTITION p0 VALUES LESS THAN ('2017-01-01 00:00:00'),
  PARTITION p1 VALUES LESS THAN ('2018-01-01 00:00:00'),
  PARTITION p_max values less than  maxvalue
);

适用场景

当需要基于多个列进行分区，特别是非整数类型的列（如字符串、日期等）时。
适用于复合键分区，尤其是当这些键是不同类型的数据时。
当RANGE或LIST分区的列类型不是整数类型时。

查看分区数据量

1
2
3

SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = tablename;

分区性能参考

操作类型	记录数	非分区表	分区表
插入性能	500万	2693 秒	3084 秒
插入性能	1000万	5440 秒	6277 秒
插入性能	2000万	12753 秒	14175 秒
查询性能	2000万记录，分区键索引，查询100万次	126 秒	90 秒
查询性能	2000万记录，非分区键索引，查询100万次	691 秒	727 秒
DDL性能	新增索引	66 秒	56 秒
存储空间	500万(数据+索引)	255+384 MB	351+555 MB
存储空间	1000万(数据+索引)	511+900 MB	551+900 MB
存储空间	2000万(数据+索引)	1000+1900 MB	1000+2100 MB

索引

索引是用于加速数据检索的数据结构，类似于书籍的目录。以下是索引的详细描述：

数据结构：索引是一种特殊的数据结构，存储在数据库的文件系统中，用于快速查找表中的特定行。常见的数据结构包括B+Tree和哈希表。
查询加速：索引主要用于加速查询操作，尤其是对大数据量的表进行查询时。可以显著减少数据库需要扫描的数据量。

优缺点

优点

提高检索效率：能够快速定位所需数据，特别是在大型数据集或其连接查询中。
优化排序和分组：加快了ORDER BY和GROUP BY子句的处理速度，因为索引是有序的。
支持唯一约束：唯一索引可以确保表中每行数据的唯一性，保证数据的正确性。
索引覆盖：只针对加了索引的字段进行查询，能够减少回表次数，提高查询效率。

缺点

降低写操作性能：当表中的数据被修改时，索引也需要被更新。这会降低INSERT、UPDATE和DELETE语句的性能。
占用额外空间：索引需要占用物理存储空间。对于拥有大量数据的表，可能会造成磁盘压力。
设计和维护成本：设计有效的索引策略需要对数据的使用模式有深入的了解。不合理的索引反而会带来性能问题。

索引结构

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

索引结构	描述
B+ Tree索引	最常见的索引类型，适用于全值匹配、范围查询和排序操作，大部分存储引擎都支持
Hash索引	基于哈希表实现，适用于精确匹配索引列的查询，不支持范围查询和排序，主要由Memory引擎使用
R-tree索引（空间索引）	专门用于空间数据类型，如地理数据存储，主要由MyISAM引擎支持
Full-text索引	用于全文搜索，建立倒排索引以快速匹配文档，MySQL 5.6及更高版本的InnoDB支持

存储引擎对索引的支持：

索引类型	InnoDB支持情况	MyISAM支持情况	Memory支持情况
B+tree索引	支持	支持	支持
Hash索引	自适应哈希索引（特定情况下）	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text索引	5.6版本之后支持	支持	不支持

二叉树

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

如果主键是顺序插入的，那么二叉树就会退化成为一个链表，结构如下：

二叉树退化链表

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

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

防止树退化为单向链表的解决方法是 红黑树。

红黑树

一个节点存储一个元素 一个节点只能拥有两个子节点 左子树和右子树有序（左小右大），不是完全有序

红黑树是一种自平衡的二叉查找树，它通过左旋和右旋来保持树的平衡，从而防止树过度倾斜。在红黑树中，每个节点带有颜色属性，颜色为红色（Red）或黑色（Black）。

红黑树的特性：

节点颜色：每个节点要么是黑色，要么是红色。
根节点：根节点总是黑色。
叶子节点：所有叶子节点（NIL节点）是黑色。
红色节点的子节点：如果一个节点是红色的，则它的直接子节点都是黑色的。
黑色路径：从任一节点到其每个叶子的所有路径都包含相同数量的黑色节点。

特征：红黑树的关键特性是它的自平衡能力，确保树不会退化成链表，这避免了普通二叉查找树在最坏情况下可能出现的性能下降。

在MySQL中的应用及局限性：

树的高度：海量数据下树的高度非常高，影响查询性能。
范围搜索：无法支持范围搜索。

B树

一个节点存储多个元素 一个节点可以拥有2个以上的子节点 按递增次序排列，并遵循左小右大原则 节点存储键值和数据

多路平衡查找树

B树是一种多路平衡查找树，用于高效的数据存取，特别是在磁盘等外部存储上。以下是B树的主要特性和应用：

多路平衡树：B树是一种多叉树，每个节点可以有多个子节点，这减少了树的高度，并优化了数据的读取速度。
排序和子节点数：节点中的键值按递增顺序排列，每个非叶节点的子节点数介于2到M之间（M是树的阶数）。
叶子节点：所有叶子节点都位于同一层。
数据存储：节点存储键值和数据，其中键值用于指导搜索和排序，数据则是与键值相关联的实际信息。

B树和红黑树最大的区别是B树是完全有序的，且B树是一颗多叉树（一个节点可以存储多个子节点）。

Mysql为什么不用B树

B树通过自平衡解决了退化链表问题。
B树是一颗多叉树，解决树高度问题，解决查询效率问题。

不选择B树是因为B树无法支持高效的范围查询。

B+树

一个节点存储多个元素 按递增次序排列，并遵循左小右大原则 非叶子节点存储键值，叶子节点存储键值和数据

叶子节点通过指针两两相连，便于实现范围查找和排序

B+树是一种自平衡的多路查找树，是B树的变体，用于数据库和文件系统中的索引和数据组织。其特点包括：

多路平衡查找树：B+树是一颗多叉树，其中每个节点有多个子节点。这种结构减少了树的高度，提高了查找效率。
存储结构：非叶子节点存储数据的键，叶子节点存储数据的键值。
叶子节点之间相连：B+树的所有叶子节点通过指针相连，形成了一个有序链表，这使得对数据的范围查询和顺序访问变得非常高效。

聚簇索引

B+树结构：在nnoDB存储引擎中，聚簇索引使用B+树结构。这意味着表的数据实际上存储在B+树的叶子节点中。
排序和范围查找：由于数据在叶子节点中按键值排序，聚簇索引特别适合于范围查找和排序操作。叶子节点之间的链接使得范围扫描变得高效。

MySql对B+树的优化

相邻叶子节点添加指针

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

B树和B+树的区别

B树节点存储键值和数据，B+树非叶子节点存键值，叶子节点存键值和数据
B+树叶子节点两两相连形成一个有序的链表，可以支持高效的范围查询；而B树每个节点的键值存储在一起，无法行程一个有序列表。

Hash

原理类似HashMap,通过列的值转成hashCode,hash冲突就用链表

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

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

hash索引

创建hash索引后，会为每个键值通过特定的算法计算出一个哈希码（hash code)，需要注意的是不同的键值计算出来的hash值可能是相同的，上图金庸和杨逍就存在hash冲突，当冲突时使用链表存储多个冲突的数据，然后再次遍历这条链表，找到需要的数据，这就解释了为啥hash冲突严重了，hash索引效率降低的原因。

优缺点

优点

快速查找：对于等值查询，哈希提供了快速的访问速度。由于哈希函数的直接计算，它能够快速定位到数据的存储位置。
空间效率：哈希通常比其他类型的（如B+树）更加紧凑，占用更少的空间。

缺点

不支持范围查询：哈希不适用于范围查询。由于哈希函数的特性，无法用哈希索引来有效地处理大于、小于或介于某两个值之间的查询。
不支持排序操作：哈希不保证顺序性，因此无法支持基于索引的数据排序。
Hash冲突：当Hash冲突严重时，使用链表存储冲突的Hash数据，会严重影响查询性能。

索引语法

创建索引

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;

索引分类

MySql索引按叶子节点存储的是否为完整表数据分为：聚簇索引、二级索引（辅助索引）。全表数据存储在聚簇索引中，聚簇索引以外的其他索引叫做二级索引，也叫辅助索引。

在MySQL数据库，将索引的具体类型主要分为以下几类：

分类	含义	特点	关键字
主键索引	针对表中的主键创建的索引	只能有一个，不可为空，性能最高	PRIMARY
唯一索引	保证某列或列组合的数据唯一性	可以有多个，列值可以为空，确保数据的唯一性	UNIQUE
普通索引	用于快速定位特定数据	可以有多个，提升查询效率，不强制唯一性	INDEX
前缀索引	在字符串列上的索引，只对值的前缀部分建立索引	节省空间，适用于长文本字段
组合索引	在多个列上建立的索引，用于涵盖多个列的查询	提高多列查询的效率，顺序敏感
全文索引	用于全文搜索，特别是在文本数据中搜索关键词	针对大文本内容的快速搜索，不适用于精确匹配	FULLTEXT

聚簇索引

也叫主键索引

InnoDB存储引擎使用聚簇索引存储数据（B+树），它有以下特点：

数据与索引的存储：在聚簇索引中，表数据直接存储在索引的叶子节点上，非叶子节点是主键索引。这意味着数据行和它的索引是紧密结合在一起的，索引结构实际上就是数据表。
主键顺序存储：聚簇索引按照主键的顺序存储数据。如果表中定义了主键，InnoDB会使用这个主键作为聚簇索引的键。
主键选择：
- 默认创建表会定义一个主键。
- 没有主键，寻找第一个非空唯一列作为聚簇索引的键。
- 不存在非空唯一列，InnoDB会自动创建一个隐含的主键 ROWID。
页分裂和插入顺序：由于数据是按主键顺序存储的，非顺序插入（如随机插入）可能导致页分裂，影响性能。顺序插入，如使用自增主键，会有更好的性能。
页合并和数据删除：当大量数据删除时，相邻的两个页数据达到合并的阈值时，会进行合并以提高查询性能。
主键更新：在聚簇索引中，更新主键的代价较高，因为它涉及到实际数据的物理移动。

InnoDB表设计要有自增主键的原因

聚簇索引：如果表有一个主键，InnoDB就会使用这个主键作为聚簇索引。当主键是自增的，新记录会被顺序地添加到索引的末端，这有助于减少 页分裂，提高写入效率。
插入性能：当使用自增主键时，每个新插入的行都会被顺序地添加到表的末尾。这种末尾追加与随机插入相比，减少了磁盘I/O，提高了插入速度。
数据排序：聚簇索引（B+树）是有序的，需要根据主键对数据进行有序排序。
主键选择：如果不指定索引则会选择第一个非空唯一列，否则会生成一个自增的 ROWID。

聚簇索引优缺点

优点

主键查询效率高：对于通过主键访问的查询，因为索引和数据是同时存储的，所以检索数据不需要额外的回表操作。
优化范围查询：由于数据是按索引键顺序存储的，因此对数据进行范围查询会更快。
索引覆盖查询：查询的列都是聚簇索引的键时，可以直接在索引中完成查询，无需进行回表查询。

缺点

需要顺序插入：如果插入数据的主键不是有序的，会导致数据页分裂，影响插入性能。
数据碎片化：数据的频繁插入和删除，聚簇索引可能导致数据在物理存储上的碎片化，影响数据库性能。
- OPTIMIZE TABLE table_name; 重建表消除磁盘碎片。
非主键查询的性能影响：非主键查询需要额外的回表操作才能获取查询数据，性能不如主键查询。

非聚簇索引

MyISAM表中的主键索引和非主键索引的结构是一样的。在这两种类型的索引中，索引的叶子节点并不直接存储表数据本身，而是存放指向表数据的物理地址。这种设计意味着MyISAM表的索引主要用于快速定位数据，而不是直接存储数据内容。

二级索引

B+树

二级索引与聚簇索引分开存储，它自己是独立的一颗B+树，索引的键就是创建二级索引指定的字段，叶子节点不是存储完整的表数据，而是其对应的ID。

优缺点

优点

提高查询效率：对于非主键的查询，二级索引可以显著提高检索速度。
灵活的查询：可以基于表中任意列组合创建索引，增加多字段查询的灵活性。

缺点

额外的存储开销：每个二级索引都需要占用额外的存储空间。
性能损耗：数据的插入、更新、删除操作都需要同时更新聚簇索引和所有相关的二级索引，降低数据修改效率。

回表查询

回表查询也称为"二次查找"或"索引跳跃"，发生在使用二级索引检索数据时，二级索引不能完全提供所检索的数据，因此需要进一步访问聚簇索引来获取完整的数据行。

工作原理：

扫描二级索引：查询首先在二级索引中进行，以查找符合条件的索引条目。
获取主键：二级索引的叶子节点存储对应数据行的聚簇索引键（通常是主键）。
访问聚簇索引：数据库使用从二级索引中获取的聚簇索引键，去聚簇索引中检索完整的数据行。
返回数据：最后，从聚簇索引中检索到的数据行被返回给用户。

回表查询需要额外的聚簇索引搜索，必然会降低查询效率。

索引覆盖

定义：二级索引包含了所有的查询字段，查询结果直接由二级索引返回，不需要回到聚簇索获取完整的数据行。

工作原理：

查询的列在索引中：查询所涉及的所有列都包含在一个索引中。
索引满足查询需求：查询可以完全通过查看索引来得到所需的结果，而不需要查找表中的实际数据行，提高查询效率。

Example

create table tb_test
(
id        int         not null primary key,
user_name varchar(20) not null,
age       int         not null,
sex       int,
birthday  datetime
);
# 建立组合索引
create index idx_user_name_age on tb_test(user_name, age);
# 进行索引覆盖查询
explain
select id, user_name, age   # 所有查询的字段都在二级索引中
from tb_test;

索引覆盖解决%在左边的like查询索引失效

索引下推

思想：尽早地利用索引信息来过滤掉不符合条件的行，减少数据访问和处理的开销。

具体实现：查询涉及多个条件时，InnoDB 可以在索引的叶子节点上执行部分查询条件，将不符合条件的行过滤掉，只将符合条件的行返回给查询引擎。

原理：

索引遍历：查询优化器确定使用索引后进行索引遍历。
条件下推：遍历索引时，尽可能利用 Where 子句条件过滤索引数据。比如查询条件不包含组合索引中间字段，依然使用后面字段过滤索引数据。
减少数据返回Server层：经过条件下推过滤后，减少返回Server层数据。
提高查询效率：减少回表检索完整数据工作量。

特点：

索引条件下推：在索引扫描阶段，即使索引条件被中断，存储引擎仍能够在索引内部应用WHERE子句的一部分条件，从而减少返回的数据行数。
优势：在索引层面提前过滤掉不满足条件的行，减少了对聚簇索引的访问次数。

EXAMPLE

使用一张用户表tuser，表里创建联合索引（name, age）

1	`select * from tuser where name like '张%' and age=10;`

最左匹配原则，那么就知道这个语句在搜索索引树的时候，只能用 张，找到的第一个满足条件的记录id为1。

没有使用索引条件下推

由于组合索引被 like 中断，引擎层在扫描索引时只扫描 姓张 的用户，即使 WHERE 子句还有其他条件用于索引过滤。
将粗略扫描的数据返回 SERVER 层。
根据返回数据回表到聚簇索引取出完整数据，然后再进行条件判断。
将筛选后的数据返回客户端。

使用索引条件下推

条件下推：在存储引擎扫描索引时，虽然使用了 like 查询，但是 WHERE 子句中有可用于当前索引的过滤条件，会继续取出过滤条件对索引进行数据过滤。
减少SERVER层数据返回：通过下推条件对索引进行过滤后，返回少量数据到SERVER层；
减少回表次数：由于 SERVER 层数据减少，需要回表的次数也相应减少。取出数据后继续过滤非索引条件。
将筛选后的数据返回客户端。

主键索引

主键唯一，不能为空 聚簇索引的key,不用回表，性能最佳

建立在主键上的索引被称为 主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。

特点：

唯一性：作为聚簇索引的键，保证每一行的主键都唯一。
非空性：主键字段不能包含NULL值。
查询优化：作为聚簇索引的键，使用主键查询不需要回表操作，查询效率最高。

create table test
(
 id        int         not null primary key # 建立主键时自动创建主键索引
);

唯一索引

能为空 建立聚簇索引Key的备选方案

唯一索引用于保证一个表中的每一行在指定列上的值是唯一的，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

特点：

保证唯一：唯一索引在表的一列或列组合上创建，确保在这一列或列组合中的每个值都是唯一的。
约束检查：当增删改数据时，会检查新的值是否违反了唯一约束，违反则抛出异常。
允许NULL：NULL 不会引发唯一索引冲突。

1	`create unique index un_idx_user_name on tb_test(user_name);`

普通索引

最常用

在MySQL中，建立在表的普通字段上的索引被称为 普通索引 或 非唯一索引。这种索引允许字段中包含重复的值。如果查询仅使用到了索引中的字段，这称为 索引覆盖查询，因为索引本身就足够满足查询需求，无需访问表中的实际数据。在非覆盖索引的情况下，数据库引擎需要进行 回表查询，即先通过索引找到对应的行标识（如行ID），然后再到数据表中检索完整的行数据。

特点：

优化查询。
索引覆盖查询。

1	`create index idx_user_name on tb_test(user_name);`

前缀索引

前缀索引是通过在创建索引时指定列值的 前几个字符 来建立的，而不是在整个字段上建索引。如果前几个字符基本就能确定数据唯一性，前缀索引可以大大减少索引占用的存储空间，也能提升索引的查询效率。

特点：

长文本字段：适合char、varchar、binary、varbinary、text的列上使用。
减少空间占用：前缀索引只索引部分字符，占用的空间比完整索引小。
提高效率：通过扫描字符前缀便能查找数据，比全字符查询效率高。
字段选择：如果字段的字符前缀重复度很高，索引效果会很差。

1 2	`# 前5个字符加索引 create index idx_user_name on tb_test(user_name(5));`

组合索引

查询优化 索引覆盖

建立在多个列上的索引被称为组合索引（也称为复合索引或多列索引），它可以同时涵盖多个字段，使得基于这些字段的查询更加高效。

原理：

有序结构：组合索引中的数据是根据索引列的顺序存储的。首先是第一列的数据排序，然后是第二列的数据排序，依此类推。
索引查找：如果查询条件包含索引的首列，索引最为有效，跳过首列索引不生效。

特点：

优化多列查询：对于经常同时在多个列上进行查询的场景，组合索引可以显著提高查询效率。
排序和分组：组合索引可以加速涉及多个列的排序（ORDER BY）和分组（GROUP BY）操作。
索引覆盖查询：如果查询的选择列完全包含在索引中，则无需访问表中的行，这称为索引覆盖扫描。

1	`create index idx_name_age on tb_user(name, age);`

组合索引的使用需要遵循最左匹配原则。

ORDER BY 优化

假设 employees 表创建了department_id, last_name, first_name 这三个字段的索引。

1	`SELECT * FROM employees ORDER BY department_id, last_name, first_name;`

即使 WHERE 子句没有使用组合索引，ORDER BY 或 GROUP BY 仍有可能利用该索引进行优化。

SELECT * 
FROM employees 
WHERE department_id = 5
ORDER BY last_name, first_name;

GROUP BY 优化

1
2
3

SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id, last_name, first_name;

SELECT department_id, job_title, AVG(salary) AS average_salary
FROM employees
WHERE department_id > 5
GROUP BY department_id, job_title;

索引使用注意事项

有效地使用 MySQL 中的索引可以显著提高数据库查询的性能，滥用索引反而会导致数据库性能下降。

使用短索引

当长字符串字段的前几位字符区别很大时，可以指定前缀长度来创建前缀索引。这种方法意味着只对字符串数据的前部分进行索引，而不是整个字段。

优势：

提高查询性能：短索引的扫描速度更快，能提升查询效率。
减少磁盘空间占用：短索引占用的磁盘空间更少。
优化I/O操作：通过减少索引大小，MySQL可以更高效地维护和访问索引。

create table test(
 id int not null primary key ,
 business_no varchar(50)
 ....             
);
# 使用短索引
create index idx_business_no on test(business_no(5));

频繁更新的字段不使用索引

频繁更新的字段会导致索引经常重新构建，从而增加数据库的工作负担。

额外性能开销：每次对表中的数据进行更新操作时，都需要更新索引结构，这会导致额外的性能开销。
影响优化器选择：频繁更新的字段加了索引，查询优化器可能会在选择执行计划时受到影响，导致查询性能下降。

索引列存在隐式类型转换

如果索引列的数据类型和查询条件的数据类型不一致时，MySQL会进行 隐式的类型转换。使得优化器无法准确估计索引的查询成本。，从而降低查询效率。

create table tb_user
(
 id      int         not null primary key,
 name    varchar(20) null,
 age     int         null,
 adderss varchar(50) null
);
create index idx_address on tb_user (adderss);

不要在索引列上调用函数或数学运算

在索引列上直接应用函数或进行数学运算可能导致MySQL查询优化器无法有效利用索引。

在MySQL中，如果在查询中对一个已经被索引的列直接使用函数调用或进行数学运算，例如 SELECT * FROM table WHERE YEAR(date_column) = 2021，这样的操作会使得优化器无法直接利用该列的索引。这是因为索引是针对列原始值建立的，而函数或数学运算改变了列的内容，从而使得索引失效。

like使用

LIKE查询中百分号（%）位置对索引的影响：

场景一：当 % 出现在模式的左边或者两边时，索引失效。

1 2	`EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%'; EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom%';`

这两种情况下，由于MySQL无法从索引中快速定位起始匹配位置，必须对整个字段进行扫描来查找匹配项，因此索引不会被有效利用。

场景二：当%仅出现在模式的右边时，索引可以生效。

1	`EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%';`

在这种情况下，查询能够利用索引，因为MySQL可以利用索引快速定位到以’tom’开头的记录。

利用索引覆盖优化like的末尾匹配

当使用LIKE操作符进行模糊查询且模式以%开始时，如 '%value%' 或 '%value'，一般情况下索引会失效。这是因为搜索模式的起始部分不固定，导致MySQL无法利用索引进行高效搜索。然而，可以通过索引覆盖（Covering Index）来优化这类查询。

例如，如果在user_name, user_age, user_level上创建了组合索引，即使LIKE查询的模式以%开头，查询也可以更高效，因为MySQL会进行 全索引扫描 而非全表扫描。

EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%';
EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack';
EXPLAIN SELECT user_name, user_age, user_level FROM users WHERE user-name LIKE '%jack%';
EXPLAIN SELECT user_name, user_age, user_level FROM users WHERE user-name LIKE '%jack%';

like失效原因

%号在右（如 LIKE 'value%'）：
- 原理：当%号在右侧时，搜索条件固定了字符串的开始部分。由于B+树索引是按字典顺序排序的，可以有效地在索引中定位到以指定前缀开头的字符串。
- 效果：这种模式可以高效地利用索引进行范围查找，因为它限定了字符串的开始部分。
%号在左（如 LIKE '%value'）：
- 原因：当%号在左侧时，搜索条件固定了字符串的结束部分。由于B+树索引是基于整个字符串的字典顺序建立的，而不是基于字符串的尾部，这使得索引在这种情况下无法有效地定位匹配。
- 效果：索引在这种模式下无法被有效利用，因为它没有限定字符串的开始部分。
两个%号（如 LIKE '%value%'）：
- 原因：当两边都有%号时，这表示在字符串的任意位置匹配给定的模式。由于B+树索引是基于整个字符串值的排序，而非其子部分，这种模式不利于在索引中定位匹配。
- 效果：在这种情况下，索引通常也无法被有效利用。

谨慎使用or

如果OR连接的条件中，某些列有索引而另一些列没有，那么这通常会导致整个查询无法有效利用索引。

优化建议：

建立合适的索引：确保所有在OR条件中涉及的列都有索引。
使用UNION替代：可以使用UNION来重写含有OR的查询，因为UNION可以单独优化每个查询分支，并且可能更高效地利用索引。

索引列排序

排序处理方式	执行计划extra列信息	排序性能比较
利用索引进行排序	Using index	高
临时表排序没有适合排序的索引,在内存创建临时表并在其中排序	Using temporary	低
文件排序将排序数据存储在磁盘上的临时文件中，并进行排序操作	Using filesort	低

当MySQL可以利用索引的有序性来执行 ORDER BY 操作时，这是最高效的排序方式。因为数据在索引中已经是有序的，所以MySQL可以直接按索引顺序检索数据，无需额外的排序操作。

数据分布影响

数据的分布特性会影响优化器是否选择使用索引。MySQL优化器在决定是否使用索引时，会评估使用索引的成本与全表扫描的成本，并选择更高效的方案。

使用索引的成本考虑：
- 查询数据量少：使用索引通常更高效，因为索引可以快速定位到符合条件的少量数据。
- 索引数据区分度高：扫描索引能高效检索数据。
全表扫描的成本考虑：
- 查询数量多：如果查询条件涵盖了表中的大部分数据，索引的效益就会降低。在这种情况下，索引的使用可能不如全表扫描高效。
- 索引数据区分度低：扫描索引收益不高可能选择全表扫描。

比如一张表只有10万条数据，并且id是自增的，使用以下Sql则会放弃索引走全表扫描，放弃索引的原因是因为表中大多数数据都满足条件。

1
2
3

select *
from test
where id < 95000

最左匹配原则

一个查询通常只使用一个索引来优化 WHERE 子句、ORDER BY 子句和 GROUP BY 子句。这意味着，如果WHERE子句已经使用了一个索引，那么在ORDER BY子句中使用另一个不同的索引来排序可能不会生效。为了优化含有多列排序的查询，可以考虑以下策略：

避免多列非索引排序：尽量避免在ORDER BY子句中包含没有被索引的多个列。
使用复合索引：复合索引可以提高那些同时使用WHERE子句和ORDER BY子句的查询的效率。
索引覆盖：查询字段和WHERE子句和ORDER BY子句中的列都属于同一个索引时，使用这个索引进行查询，排序和返回数据。

最佳左前缀底层原理：

键值排序：在复合索引中，数据首先按照索引的第一个列的值排序，对于第一个列值相同的行，再按照第二个列的值排序，以此类推。
连续匹配：当执行查询时，MySQL会在索引中查找与查询条件匹配的行。为了高效地利用索引，查询条件需要从复合索引的最左边开始连续匹配索引中的列。这意味着，如果查询条件跳过了复合索引中的某个列，索引的后续部分就无法被有效利用。

create table myTest(
a int,
b int,
c int,
KEY a(a,b,c)
);

1 2	`select * from myTest where a=3 and b=5 and c=4; -- abc顺序 abc三个索引都在where条件里面用到了，而且都发挥了作用`

1 2	`select * from myTest where c=4 and b=6 and a=3; where里面的条件顺序在查询之前会被自动优化，效果跟上一句一样`

1 2	`select * from myTest where a=3 and c=7; a用到索引，b没有用，所以c是没有用到索引效果的`

1
2

select * from myTest where a=3 and b>7 and c=3;   -- b范围值，断点，阻塞了c的索引
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

1 2	`select * from myTest where a=3 and b>=7 and c=3; -- abc全部使用索引 b是大于等于包含等值，因此不会阻断查询`

1 2	`select * from myTest where b=3 and c=4; --组合索引必须遵循最左匹配原则因为a索引没有使用，所以这里 bc都没有用上索引效果`

1 2	`select * from myTest where a=3 order by b; a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的(索引列排序)`

1
2

select * from myTest where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort (索引列排序不生效，使用文件排序)

1 2	`select * from myTest where b=3 order by a; b没有用到索引，排序中a也没有发挥索引效果 (没有遵循最左匹配原则)`

1 2	`select * from myTest a=3 and b like '7%' and c=3; -- 左前缀模糊匹配，索引生效 abc都使用了索引`

1 2	`select * from myTest a=3 and b like '%7' and c=3; -- 非左前缀模糊匹配，索引失效 a使用索引，b右匹配索引阻断`

Index Skip Scan

索引跳跃扫描（Index Skip Scanning）是在一个复合索引中（MySql 8.0），如果查询条件 没有包含组合索引的第一列，但包含了后续的某些列，传统的索引扫描方法可能无法直接使用这个索引。索引跳跃扫描的原理是在索引的第一列上“跳跃”查找，然后在找到的每个区间内，进一步检查是否满足查询的其他条件。

原理：

跳跃动作：在扫描索引过程中，优化器会“跳过”不符合查询条件的索引部分，定位到下一个可能匹配的索引点。
重复跳跃：这个跳跃过程可能会发生多次，直到找到满足所有查询条件的数据。
避免全索引扫描：通过跳跃不满足查询条件的条目，提高索引扫描效率。

官方例子

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

索引跳跃过程：

获取f1字段第一个唯一值，也就是f1=1。
构造f1=1 and f2 > 40，进行范围查询。
获取f1字段第二个唯一值，也就是f1=2。
构造f1=2 and f2 > 40，进行范围查询。
一直扫描完f1字段所有的唯一值，最后将结果合并返回。

索引创建原则

在 经常需要搜索 的列上创建索引，可以加快搜索的速度
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
在经常用在 连接的列 上，这些列主要是一些外键，可以加快连接的速度
尽量选择 区分度高 的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
在经常需要根据 范围进行搜索 的列上创建索引，因为索引已经排序，其指定的范围是连续的
在经常需要 排序的列 上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以 覆盖索引 ，节省存储空间，避免回表，提高查询效率
要 控制索引的数量 ，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
如果索引列不能存储NULL值，请在创建表时使用 NOT NULL约束 它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

InnoDB: 最多 64个二级索引 和 一个主键索引, 单个索引最多包含16列。

SQL提示

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

USE INDEX

USE INDEX 提示可以在 SELECT 查询中使用，让MySQL参考使用指定的索引。

1	`explain select * from city use index(idx_city_name) where city_name = '深圳';`

需要注意的是，虽然 USE INDEX 提示可以用于指定要使用的索引，但它并不能保证 MySQL 实际会使用指定的索引。MySQL 查询优化器会在执行查询时根据统计信息和查询成本进行决策。如果查询优化器认为其他索引更适合执行查询，它可能会忽略 USE INDEX 提示。

IGNORE INDEX

IGNORE INDEX 用于告诉查询优化器在执行查询时忽略特定的索引。通过使用 IGNORE INDEX，可以让查询优化器绕过指定的索引，而选择其他索引或执行全表扫描来处理查询。

1	`explain select * from city ignore index(idx_user_pro) where city_name = '深圳';`

通常情况下，MySQL 的查询优化器会根据统计信息和索引选择算法选择最佳的索引，不需要手动干预。只有在特定的情况下，当你确定优化器选择了错误的索引时，才考虑使用 IGNORE INDEX 提示进行干预。

FORCE INDEX

FORCE INDEX 是 MySQL 中用于强制查询优化器选择特定索引的语句。当你使用 FORCE INDEX 语句时，查询优化器将会忽略其他可能的索引并且只使用指定的索引来执行查询。

1	`explain select * from city force index(idx_user_pro) where city_name = '深圳';`

需要注意，强制使用索引可能导致查询性能下降，因为查询优化器无法选择更有效的索引。

SQL性能分析

show status 运行状态

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次。

1
2
3

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com%';

Com_delete: 删除次数。
Com_insert: 插入次数。
Com_select: 查询次数。
Com_update: 更新次数。

状态名	作用域	详细解释
Aborted_clients	Global	由于客户端没有正确关闭连接导致客户端终止而中断的连接数
Aborted_connects	Global	试图连接到MySQL服务器而失败的连接数
Binlog_cache_disk_use	Global	使用临时二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
Binlog_cache_use	Global	使用临时二进制日志缓存的事务数量
Bytes_received	Both	从所有客户端接收到的字节数。
Bytes_sent	Both	发送给所有客户端的字节数。
com*		各种数据库操作的数量
Compression	Session	客户端与服务器之间只否启用压缩协议
Connections	Global	试图连接到(不管是否成功)MySQL服务器的连接数
Created_tmp_disk_tables	Both	服务器执行语句时在硬盘上自动创建的临时表的数量
Created_tmp_files	Global	mysqld已经创建的临时文件的数量
Created_tmp_tables	Both	服务器执行语句时自动创建的内存中的临时表的数量。如果Created_tmp_disk_tables较大，你可能要增加tmp_table_size值使临时表基于内存而不基于硬盘
Delayed_errors	Global	用INSERT DELAYED写的出现错误的行数(可能为duplicate key)。
Delayed_insert_threads	Global	使用的INSERT DELAYED处理器线程数。
Delayed_writes	Global	写入的INSERT DELAYED行数
Flush_commands	Global	执行的FLUSH语句数。
Handler_commit	Both	内部提交语句数
Handler_delete	Both	行从表中删除的次数。
Handler_discover	Both	MySQL服务器可以问NDB CLUSTER存储引擎是否知道某一名字的表。这被称作发现。Handler_discover说明通过该方法发现的次数。
Handler_prepare	Both	A counter for the prepare phase of two-phase commit operations.
Handler_read_first	Both	索引中第一条被读的次数。如果较高，它建议服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引。
Handler_read_key	Both	根据键读一行的请求数。如果较高，说明查询和表的索引正确。
Handler_read_next	Both	按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev	Both	按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
Handler_read_rnd	Both	根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。
Handler_read_rnd_next	Both	在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
Handler_rollback	Both	内部ROLLBACK语句的数量。
Handler_savepoint	Both	在一个存储引擎放置一个保存点的请求数量。
Handler_savepoint_rollback	Both	在一个存储引擎的要求回滚到一个保存点数目。
Handler_update	Both	在表内更新一行的请求数。
Handler_write	Both	在表内插入一行的请求数。
Innodb_buffer_pool_pages_data	Global	包含数据的页数(脏或干净)。
Innodb_buffer_pool_pages_dirty	Global	当前的脏页数。
Innodb_buffer_pool_pages_flushed	Global	要求清空的缓冲池页数
Innodb_buffer_pool_pages_free	Global	空页数。
Innodb_buffer_pool_pages_latched	Global	在InnoDB缓冲池中锁定的页数。这是当前正读或写或由于其它原因不能清空或删除的页数。
Innodb_buffer_pool_pages_misc	Global	忙的页数，因为它们已经被分配优先用作管理，例如行锁定或适用的哈希索引。该值还可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。
Innodb_buffer_pool_pages_total	Global	缓冲池总大小（页数）。
Innodb_buffer_pool_read_ahead_rnd	Global	InnoDB初始化的“随机”read-aheads数。当查询以随机顺序扫描表的一大部分时发生。
Innodb_buffer_pool_read_ahead_seq	Global	InnoDB初始化的顺序read-aheads数。当InnoDB执行顺序全表扫描时发生。
Innodb_buffer_pool_read_requests	Global	InnoDB已经完成的逻辑读请求数。
Innodb_buffer_pool_reads	Global	不能满足InnoDB必须单页读取的缓冲池中的逻辑读数量。
Innodb_buffer_pool_wait_free	Global	一般情况，通过后台向InnoDB缓冲池写。但是，如果需要读或创建页，并且没有干净的页可用，则它还需要先等待页面清空。该计数器对等待实例进行记数。如果已经适当设置缓冲池大小，该值应小。
Innodb_buffer_pool_write_requests	Global	向InnoDB缓冲池的写数量。
Innodb_data_fsyncs	Global	fsync()操作数。
Innodb_data_pending_fsyncs	Global	当前挂起的fsync()操作数。
Innodb_data_pending_reads	Global	当前挂起的读数。
Innodb_data_pending_writes	Global	当前挂起的写数。
Innodb_data_read	Global	至此已经读取的数据数量（字节）。
Innodb_data_reads	Global	数据读总数量。
Innodb_data_writes	Global	数据写总数量。
Innodb_data_written	Global	至此已经写入的数据量（字节）。
Innodb_dblwr_pages_written	Global	已经执行的双写操作数量
Innodb_dblwr_writes	Global	双写操作已经写好的页数
Innodb_log_waits	Global	我们必须等待的时间，因为日志缓冲区太小，我们在继续前必须先等待对它清空

Innodb_log_write_requests	Global	日志写请求数。
Innodb_log_writes	Global	向日志文件的物理写数量。
Innodb_os_log_fsyncs	Global	向日志文件完成的fsync()写数量。
Innodb_os_log_pending_fsyncs	Global	挂起的日志文件fsync()操作数量。
Innodb_os_log_pending_writes	Global	挂起的日志文件写操作
Innodb_os_log_written	Global	写入日志文件的字节数。
Innodb_page_size	Global	编译的InnoDB页大小(默认16KB)。许多值用页来记数；页的大小很容易转换为字节。
Innodb_pages_created	Global	创建的页数。
Innodb_pages_read	Global	读取的页数。
Innodb_pages_written	Global	写入的页数。
Innodb_row_lock_current_waits	Global	当前等待的待锁定的行数。
Innodb_row_lock_time	Global	行锁定花费的总时间，单位毫秒。
Innodb_row_lock_time_avg	Global	行锁定的平均时间，单位毫秒。
Innodb_row_lock_time_max	Global	行锁定的最长时间，单位毫秒。
Innodb_row_lock_waits	Global	一行锁定必须等待的时间数。
Innodb_rows_deleted	Global	从InnoDB表删除的行数。
Innodb_rows_inserted	Global	插入到InnoDB表的行数。
Innodb_rows_read	Global	从InnoDB表读取的行数。
Innodb_rows_updated	Global	InnoDB表内更新的行数。
Key_blocks_not_flushed	Global	键缓存内已经更改但还没有清空到硬盘上的键的数据块数量。
Key_blocks_unused	Global	键缓存内未使用的块数量。你可以使用该值来确定使用了多少键缓存
Key_blocks_used	Global	键缓存内使用的块数量。该值为高水平线标记，说明已经同时最多使用了多少块。
Key_read_requests	Global	从缓存读键的数据块的请求数。
Key_reads	Global	从硬盘读取键的数据块的次数。如果Key_reads较大，则Key_buffer_size值可能太小。可以用Key_reads/Key_read_requests计算缓存损失率。
Key_write_requests	Global	将键的数据块写入缓存的请求数。
Key_writes	Global	向硬盘写入将键的数据块的物理写操作的次数。
Last_query_cost	Session	用查询优化器计算的最后编译的查询的总成本。用于对比同一查询的不同查询方案的成本。默认值0表示还没有编译查询。默认值是0。Last_query_cost具有会话范围。
Max_used_connections	Global	服务器启动后已经同时使用的连接的最大数量。
ndb*		ndb集群相关
Not_flushed_delayed_rows	Global	等待写入INSERT DELAY队列的行数。
Open_files	Global	打开的文件的数目。
Open_streams	Global	打开的流的数量(主要用于记录)。
Open_table_definitions	Global	缓存的.frm文件数量
Open_tables	Both	当前打开的表的数量。

Opened_files	Global	文件打开的数量。不包括诸如套接字或管道其他类型的文件。也不包括存储引擎用来做自己的内部功能的文件。
Opened_table_definitions	Both	已经缓存的.frm文件数量
Opened_tables	Both	已经打开的表的数量。如果Opened_tables较大，table_cache 值可能太小。
Prepared_stmt_count	Global	当前的预处理语句的数量。 (最大数为系统变量: max_prepared_stmt_count)
Qcache_free_blocks	Global	查询缓存内自由内存块的数量。
Qcache_free_memory	Global	用于查询缓存的自由内存的数量。
Qcache_hits	Global	查询缓存被访问的次数。
Qcache_inserts	Global	加入到缓存的查询数量。
Qcache_lowmem_prunes	Global	由于内存较少从缓存删除的查询数量。
Qcache_not_cached	Global	非缓存查询数(不可缓存，或由于query_cache_type设定值未缓存)。
Qcache_queries_in_cache	Global	登记到缓存内的查询的数量。
Qcache_total_blocks	Global	查询缓存内的总块数。
Queries	Both	服务器执行的请求个数，包含存储过程中的请求。
Questions	Both	已经发送给服务器的查询的个数。
Rpl_status	Global	失败安全复制状态(还未使用)。
Select_full_join	Both	没有使用索引的联接的数量。如果该值不为0,你应仔细检查表的索引
Select_full_range_join	Both	在引用的表中使用范围搜索的联接的数量。
Select_range	Both	在第一个表中使用范围的联接的数量。一般情况不是关键问题，即使该值相当大。
Select_range_check	Both	在每一行数据后对键值进行检查的不带键值的联接的数量。如果不为0，你应仔细检查表的索引。
Select_scan	Both	对第一个表进行完全扫描的联接的数量。
Slave_heartbeat_period	Global	复制的心跳间隔
Slave_open_temp_tables	Global	从服务器打开的临时表数量
Slave_received_heartbeats	Global	从服务器心跳数
Slave_retried_transactions	Global	本次启动以来从服务器复制线程重试次数
Slave_running	Global	如果该服务器是连接到主服务器的从服务器，则该值为ON。
Slow_launch_threads	Both	创建时间超过slow_launch_time秒的线程数。
Slow_queries	Both	查询时间超过long_query_time秒的查询的个数。
Sort_merge_passes	Both	排序算法已经执行的合并的数量。如果这个变量值较大，应考虑增加sort_buffer_size系统变量的值。
Sort_range	Both	在范围内执行的排序的数量。
Sort_rows	Both	已经排序的行数。
Sort_scan	Both	通过扫描表完成的排序的数量。
ssl＊		ssl连接相关
Table_locks_immediate	Global	立即获得的表的锁的次数。
Table_locks_waited	Global	不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
Threads_cached	Global	线程缓存内的线程的数量。
Threads_connected	Global	当前打开的连接的数量。
Threads_created	Global	创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。缓存访问率的计算方法Threads_created/Connections。
Threads_running	Global	激活的（非睡眠状态）线程数。
Uptime	Global	服务器已经运行的时间（以秒为单位）。
Uptime_since_flush_status	Global	最近一次使用FLUSH STATUS 的时间（以秒为单位）。

慢查询日志

慢查询日志是一种记录执行时间超过指定阈值的SQL语句的日志。它可以帮助我们定位执行效率较低的查询，并进行性能优化。

1 2	`# 查看是否开启慢查询日志 show variables like 'slow_query_log'`

作用：

性能优化：通过分析慢查询日志，可以找出执行效率较低的SQL语句，针对性地进行优化，提升数据库性能。
查询分析：慢查询日志记录了执行时间超过指定阈值的SQL语句，可以用于查询分析、性能瓶颈定位和故障排查等工作。

开启慢查询日志

如果要开启慢查询日志，需要在MySQL的配置文件（my.cnf）中配置如下信息：

# 设置为1表示开启慢查询日志
slow_query_log = 1

# 指定慢查询日志的文件路径和文件名
slow_query_log_file = /path/to/slow-query.log

# 指定一个阈值，单位为秒，超过该阈值的SQL语句将被记录到慢查询日志中
long_query_time = 2

配置完毕之后，通过以下指令重新启动MySQL服务器即可。

慢查询日志的格式：

执行时间：Time字段，以秒为单位。
锁等待时间：Lock_time字段，以秒为单位。
返回行数：Rows_sent字段，表示查询结果返回的行数。
扫描行数：Rows_examined字段，表示查询过程中扫描的行数。
SQL语句：Query字段，记录执行的SQL语句。

profile详情

MySQL的慢查询日志是一种记录执行时间超过指定阈值的SQL语句的日志，而Profile是另一种用于分析SQL查询性能的工具。

# 是否支持profile
SELECT @@have_profiling;

# 是否开启profile
SELECT @@profiling;

开启 profile：

在执行SQL查询语句时，可以使用 SET PROFILING = 1; 命令来开启Profile功能。
开启Profile后，MySQL会记录查询的执行信息，包括每个阶段的耗时和资源消耗。

查询并获取Profile信息：

执行一系列Sql的操作，然后通过如下指令查看指令的执行耗时。

-- 查看每一条SQL的耗时基本情况
show profiles;

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

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

explain 执行计划

EXPLAIN 是一种用于分析查询执行计划的工具。通过EXPLAIN命令，我们可以获取MySQL优化器在执行查询时选择的查询执行计划，从而了解查询的执行方式、索引使用情况以及可能存在的性能问题。

id	Columns	JSON Name	Meaning
1	id	select_id	查询的标识符，每个查询都有一个唯一的标识符
2	select_type	None	查询的类型，常见的类型有SIMPLE、PRIMARY、SUBQUERY、DERIVED等
3	table	table_name	查询涉及的表名
4	partitions	partitions	查询涉及的分区信息
5	`type`	access_type	访问类型，表示MySQL在访问表时使用的策略，常见的类型有ALL、INDEX、RANGE、REF等
6	`possible_keys`	possible_keys	可能使用到的索引
7	`key`	key	经过优化器评估最终使用的索引
8	key_len	key_length	使用到的索引长度
9	ref	ref	与索引比较的列或常数
10	rows	rows	rows_examined，要得到最终记录索要扫描经过的记录数
11	filtered	filtered	通过条件过滤后的行百分比
12	`Extra`	None	额外的信息说明

select_type

表示查询中每个select子句的类型

id	select_type value	JSON name	含义
1	SIMPLE	None	简单的SELECT语句（不包括UNION操作或子查询操作）
2	PRIMARY	None	PRIMARY：最外层的SELECT语句
3	UNION	None	UNION：UNION操作中的内层SELECT语句（内层的SELECT语句与外层的SELECT语句没有依赖关系）
4	DEPENDENT UNION	dependent(true)	DEPENDENT UNION：UNION操作中的内层SELECT语句（内层的SELECT语句与外层的SELECT语句有依赖关系）
5	UNION RESULT	union_result	UNION RESULT：UNION操作的结果，id值通常为NULL
6	SUBQUERY	None	SUBQUERY：子查询中的第一个SELECT语句（如果有多个子查询存在）
7	DEPENDENT SUBQUERY	dependent(true)	DEPENDENT SUBQUERY：子查询中的第一个SELECT语句，但依赖于外层的表（如果有多个子查询存在）
8	DERIVED	None	DERIVED：作为驱动表的SELECT子查询（子查询位于FROM子句）
9	MATERIALIZED	materialized_form_subquery	MATERIALIZED：被物化的子查询
10	UNCACHEABLE SUBQUERY	cacheable(false)	UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
11	UNCACHEABLE UNION	cacheable(false)	UNCACHEABLE UNION：UNION操作中的不可被物化的内层子查询（类似于UNCACHEABLE SUBQUERY）

type

type 字段描述了查询执行计划中MySQL选择的访问方法，也称为访问类型（access type）。它指的是MySQL在执行查询时选择的如何访问表中数据的方式。

性能由好到坏排序：

id	type value	Meaning
1	system	表示使用系统表，通常用于内部操作，不涉及具体的数据表。
2	const	表示通过常量条件（例如主键或唯一索引的等值查询）来访问表的一行数据。这是最快的访问方法之一。
3	eq_ref	表示在多表连接中，被驱动表的连接列上有主键或唯一索引的检索，用于精确匹配。
4	ref	表示使用非唯一索引或唯一索引的非唯一部分进行的等值查询。
5	range	表示使用索引进行范围查询，通常用于 BETWEEN、>、< 等条件的查询。
6	index	表示索引扫描，与"ALL"类型类似，但只扫描索引树，而不读取实际数据行。
7	all	表示全表扫描，即访问表的所有数据。这是最慢的访问方法之一，应尽量避免。

Extra

id	type value	Meaning
1	const row not found	所要查询的表为空
2	Distinct	mysql正在查询distinct值，因此当它每查到一个distinct值之后就会停止当前组的搜索，去查询下一个值
3	Impossible WHERE	where条件总为false，表里没有满足条件的记录
4	Impossible WHERE noticed after reading const tables	在优化器评估了const表之后，发现where条件均不满足
5	no matching row in const table	当前join的表为const表，不能匹配
6	Not exists	优化器发现内表记录不可能满足where条件
7	Select tables optimized away	在没有group by子句时，对于MyISAM的select count(*)操作，或者当对于min(),max()的操作可以利用索引优化，优化器发现只会返回一行。
8	`Using filesort`	`使用filesort来进行order by操作,出现filesort就说明拍序列没使用上索引`
9	`Using index`	`覆盖索引`
10	Using index for group-by	对于group by列或者distinct列，可以利用索引检索出数据，而不需要去表里查数据、分组、排序、去重等等
11	Using join buffer	之前的表连接在nested loop之后放进join buffer，再来和本表进行join。适用于本表的访问type为range，index或all
12	Using sort_union,using union,using intersect	index_merge的三种情况
13	Using temporary	使用了临时表来存储中间结果集，适用于group by，distinct，或order by列为不同表的列。
14	Using where	在存储引擎层检索出记录后，在server利用where条件进行过滤，并返回给客户端
15	`Using index condition`	这是MySQL 5.6出来的新特性，叫做`索引条件下推`

SQL优化

Insert 优化

单条插入效率问题

insert into test values(...);
insert into test values(...);
insert into test values(...);
insert into test values(...);

当对MySQL数据库进行 大量 的单条数据插入时，每条 INSERT 语句都被视作一个独立的操作。这意味着：

效率低下：每条 INSERT 语句都被视为一个独立的隐式事务，这意味着每次插入都涉及事务的开启和提交。
占用连接资源：大量的单条插入语句占用更多的数据库连接资源，从而影响数据库的整体性能。
网络延迟累积：单条插入语句占用一个数据库连接，单独与数据库交互。在大批量插入的情境下，会话延迟会累积，增加总的数据插入时间。

批量插入

insert into test values
(...),
(...),
(...);

优势：

减少网络延迟：多条记录作为单个操作发送到数据库的，减少了与数据库的网络交互次数。
减少事务开销：批量插入允许多条记录在一个事务中被插入，减少了事务提交和日志写入的次数，提高写入效率。
优化索引更新：量插入减少了索引更新的次数，因为它在插入多条记录后一次性更新索引。
减少CPU占用：批量插入一次性处理事务和索引的维护，避免频繁提交事务和维护索引带来的额外性能开销。

注意：

批次大小：通常建议的批次大小在500至1000条记录之间，过大的批次可能导致内存消耗增加或事务超时，而过小的批次则无法充分利用批量插入的优势。
大小限制：单个INSERT语句的长度可能受到SQL语句大小限制（由max_allowed_packet参数控制）。

手动控制事务

begin;
insert into test values(...);
insert into test values(...);
insert into test values(...);
commit;

在进行批量插入时，还有一种优化手段就是手段控制事务，将多条 Insert 语句在一个事务中提交。

优势：

减少事务开销：批量插入允许多条记录在一个事务中被插入，减少了事务提交和日志写入的次数，提高写入效率。
灵活性：可以在单独的 INSERT 语句间进行更复杂的操作，如条件逻辑、错误处理等。
适应性：对于不能一次性装入一个INSERT语句的大量数据，可以分批进行插入。

load data infile

Mybatis 使用 load data infile

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;
-- 意思是文件中每个字段用 `,` 分割，每行用 `\n` 分割

LOAD DATA INFILE 是用于高效地从文件中（CSV）批量加载数据到数据库表的命令。它特别适用于快速导入大量数据，比如在数据迁移或大规模数据处理场景中。

优势：

导入高效：LOAD DATA INFILE 是导入大量数据到 MySQL 表中最快的方法之一。它比逐行使用 INSERT 语句或通过客户端脚本导入数据快得多。
支持大数据集：对于非常大的数据集，使用 LOAD DATA INFILE 可以有效地处理和导入数据，而不必担心内存溢出或其他资源限制。
事务性：整个 LOAD DATA INFILE 操作可以作为一个单独的事务进行处理，这有助于保持数据一致性。

Order By 优化

MySQL的排序，有两种方式：

Using filesort :
- 当无法直接通过索引来满足排序要求时，会使用一种叫做 Filesort 的算法来进行排序，可以在内存中或者在磁盘上进行。
- Filesort 排序会比索引排序效率低，尤其是当需要处理的数据量大而无法完全放入内存时。
Using temporary :
- 没有适合排序的索引,在内存创建临时表并在其中排序。
Using index :
- 当排序要求可以通过已有的索引顺序直接满足时，MySQL 会使用 Using index 方式来直接从索引中读取有序数据。这种情况下，MySQL 利用了索引的有序性质，无需额外进行排序操作。

排序优化：

利用索引进行排序：根据排序字段建立合适的索引，多字段排序时，也遵循 最左前缀法则，避免了额外的排序步骤。
索引覆盖排序：直接使用索引中的数据来完成查询，包括排序操作。
对索引字段指定排序方式：多字段排序, 需要按照实际需求，可为每个字段指定升降序（ASC/DESC）。
调整排序缓冲区大小：可以调整MySQL的排序缓冲区大小（sort_buffer_size参数，256KB），以适应特定的查询需求。

索引列排序

如果 ORDER BY 子句中的列完全匹配索引列，并且查询条件允许使用该索引，MySQL就会利用索引的有序性进行排序。

create table test2 (
 id int primary key auto_increment,
 user_name varchar(20),
 age int,
 address varchar(30)
);
create index idx_age_user_name on test2(age, user_name);

explain
select age, user_name
from test2
order by age; # 使用索引列排序

遵循最左匹配原则

explain
select user_name
from test2
order by  address desc, user_name desc

这时候索引排序不生效，没有遵循最左匹配原则。

升降序排序

explain
select age, user_name
from test2
order by age asc, user_name desc

此时 age 字段使用索引排序，user_name 字段使用 FileSort，因为索引在创建的时候默认是按照 ASC 排序的，优化此SQL可以重新创建一个索引，指定不同字段的排序。

1	`create index idx_age_username_2 on test2(age asc, user_name desc)`

再次执行SQL执行计划

Group By 优化

对 GROUP BY 子句的优化主要目的是提高数据聚合查询的效率，以下是一些针对GROUP BY查询的优化技巧：

使用索引：如果GROUP BY子句中的列已经被索引，MySQL可以直接使用索引进行分组操作，从而提高效率。
索引覆盖：如果查询中的过滤条件列和 GROUP BY 列全部包含在一个索引中，可以实现索引覆盖。
防止滥用HAVING子句：HAVING子句可以用来过滤分组后的结果，但如果能通过 WHERE 子句提前过滤数据，则更为高效。

create table test
(
    id        int auto_increment
        primary key,
    user_name varchar(20) null,
    age       int         null,
    address   varchar(30) null
);

explain
select count(0)
from test
group by age, user_name

在额外信息中我们看到 Using temporary，意思是使用临时表进行分组或排序，其过程比较耗时。

使用索引分组

前提：满足索引覆盖

创建组合索引。

1	`create index idx_age_user_name on test(age, user_name);`

再次执行执行计划。

索引分组

说明使用了索引分组。

最左匹配原则

explain
select count(0)
from test
group by user_name;

因为没有遵循索引的最左匹配原则，使用到了临时表排序

explain
select count(0)
from test
where age = 20
group by user_name;

因为where条件使用到了age,遵循了最左匹配原则

Limit 优化

在使用 LIMIT 进行分页查询时，尤其是深度分页（例如：LIMIT 100000, 20），MySQL需要先检索出前100020条记录，然后丢弃前100000条，这导致效率低下。

索引覆盖和表子查询优化

利用索引覆盖和表子查询可以有效地优化深度分页的性能：

先在子查询中使用 LIMIT 和 索引覆盖 定位到所需的数据行的索引或主键。
然后在外层查询中根据这些索引或主键检索完整的数据行。

1 2	`select a.* from sys_user a join (select id from sys_user order by id limit 9000000, 20) b on a.id = b.id;`

注意：确保子查询中的ORDER BY字段是索引覆盖的，这样可以提高定位效率。子查询的 ORDER BY 确保了数据的正确排序。

主键索引优化

对于深度分页：

记录上一页的最大ID值。
在后续查询中使用这个ID作为过滤条件，避免了 LIMIT 中过大的偏移量。

select a.*
from sys_user a join
where id > #{maxId} 
limit 20

注意：使用这种方法时，确保查询保持了逻辑上的连续性和一致性。这种方式适用于基于递增ID的连续分页，但可能不适用于需要复杂排序的场景。

Count 优化

如果数据量很大，在执行count操作时，是非常耗时的

MyISAM存储引擎：在处理简单的 COUNT(*) 查询时效率更高，因为它可以直接读取存储的行数。然而，在处理带条件的 COUNT()查询时，MyISAM的效率也会下降，因为需要遍历符合条件的数据行。
InnoDB存储引擎：在执行任何类型的 COUNT() 操作时通常效率较低，无论是带条件还是不带条件的查询。这是因为InnoDB需要遍历数据行来确保事务的可见性，而不是直接读取表的总行数。

Count用法

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

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

Update 优化

1	`update sys_user set name = 'test' where name = 'test1'`

锁升级问题：在执行上述SQL时，如果 name 字段没有索引，InnoDB可能无法有效地定位需要更新的行，导致MySQL对更多的行甚至整个表加锁，从而影响性能。
行锁与索引：InnoDB的行锁是基于索引的。如果WHERE子句中使用的字段没有索引，InnoDB可能无法使用行锁，而是使用更粗粒度的锁，如表锁。

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

因此在进行 Update Delete 操作时尽量优先使用 主键，没有主键也要使用索引字段操作，防止行锁升级表锁。

优化策略：

使用索引：为 UPDATE 操作中的WHERE子句中的字段添加索引。在本例中，为 name 字段添加索引可以帮助InnoDB更高效地定位到需要更新的行，减少锁的粒度。
优先使用主键：在可能的情况下，使用主键进行 UPDATE 和 DELETE 操作。主键索引是最高效的，可以最大程度地减少锁竞争。
避免大范围更新：避免一次性更新大量行，这可能导致锁竞争和性能下降。如果需要，可以分批次执行更新。
监控锁等待：使用性能监控工具观察锁等待情况，以识别和解决性能瓶颈。
考虑查询效率：确保UPDATE查询尽可能高效，以减少锁持有时间。
事务管理：合理管理事务，避免长事务，因为长事务会持有锁更长时间，增加锁竞争。

NOT IN 优化

查询 address 表中不存在于 user 表的地址。

原始SQL示例：

1
2
3

SELECT *
FROM address
WHERE id NOT IN (SELECT address_id FROM user);

优化方法：

使用 LEFT JOIN：使用 LEFT JOIN 代替 NOT IN 可以提高查询效率，特别是当子查询返回的结果集较大时。

优化后的SQL：

SELECT a.*
FROM address a
LEFT JOIN user b ON a.id = b.address_id
WHERE b.id IS NULL;

使用索引：确保 address 表的 id 列和 user 表的 address_id 列都有索引。这有助于提高 JOIN 操作的效率。

OR 优化

原始SQL：

1	`SELECT * FROM TB1 WHERE c1 = 'xxx' OR c2 = 'xxx';`

优化方法：

使用UNION代替OR：当 OR 条件中的每个列都有索引时，可以使用 UNION 来代替 OR，以提高查询效率。这是因为 OR 可能导致索引失效，而 UNION 可以分别利用各个列上的索引。

优化后的SQL：

1
2
3

SELECT * FROM TB1 WHERE c1 = 'xxx'
UNION
SELECT * FROM TB1 WHERE c2 = 'xxx';

确保 c1 和 c2 上各自有索引，这样每个分开的查询都能高效地利用索引。

主键优化

在InnoDB中，表数据按主键顺序存储。如果主键插入顺序是随机的，可能导致页频繁分裂，进而影响性能。这是因为每次插入都可能需要重新整理页中的数据以保持顺序。

页分裂

乱序插入

上图是一个主键乱序插入的场景，1#page 和 2#page 此时已经存满了，但是此时需要插入一条id为50的数据，由于聚簇索引（B+树）数据是根据id排序的，50应该插入到47后面的位置，页如果存储不了则会进行分裂。

页分裂

页分裂是一种耗费性能的操作，因为它涉及到数据移动和页指针的更新。在主键乱序插入的场景中，页分裂尤为频繁。

页合并

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

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

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

页合并

页合并通常在页中大量数据被删除后发生，这可以减少存储空间的浪费。页合并也是一个性能密集型操作，因为它可能涉及数据移动和页重新组织。

主键选择

使用自增主键：自增主键是最佳选择，特别是对于InnoDB存储引擎。它们保证新插入的数据总是添加到索引的末尾，避免页分裂并提高插入效率。
降低主键长度：在满足业务需求的情况下，应尽量减少主键长度。较短的主键可以减少索引占用的空间，提高数据处理效率。例如，选择INT而非BIGINT可以节省空间。
单调递增的主键策略：如果使用自定义生成的主键，应确保它是单调递增的。这样可以减少随机插入导致的页分裂，从而优化插入性能。
避免使用UUID作为主键：尽管UUID在全局唯一性方面表现优秀，其高度随机性可能导致频繁的页分裂，并增加索引大小。在InnoDB中，尤其要注意这一点。如果必须使用UUID，考虑优化其生成方式以减少对性能的影响。

锁

在MySQL中，锁是用来管理多个用户或进程同时访问数据库时数据一致性和完整性的机制。MySQL支持多种类型的锁，不同的存储引擎支持不同的锁机制。

MySql中的锁

全局锁（Global Locks）
- 描述：全局锁会锁定整个数据库实例，阻止对数据库实例的所有修改操作。它通常用于全库导出或其他维护任务，以确保数据一致性。
- 用途：例如，FLUSH TABLES WITH READ LOCK 是设置全局读锁的命令，用于创建数据库的全备份。
表锁（Table Locks）
- 描述：表锁会锁定整张表，阻止对该表的并发写操作。在MyISAM和InnoDB使用非索引修改数据中常见。
- 用途：适用于读多写少的场景。读操作不阻塞其他读操作，但写操作会阻塞所有其他读写操作。
行锁（Row-Level Locks）
- 描述：行锁会锁定单个或多个数据行。InnoDB存储引擎支持行锁，它允许高度并发的数据访问。
- 用途：适用于事务性操作，减少锁定资源的数量，提高并发访问性能。
间隙锁（Gap Locks）
- 描述：间隙锁不锁定实际的数据行，而是锁定索引之间的间隙，或者是一个索引与范围末尾之间的间隙。主要用于防止幻读。
- 用途：保证可重复读（Repeatable Read）隔离级别下的一致性，阻止其他事务在间隙内插入行。

数据准备

create table test2
(
    id        int auto_increment
        primary key,
    user_name varchar(20) null,
    age       int         null,
    address   varchar(30) null
);

INSERT INTO test.test2 (id, user_name, age, address) VALUES (1, 'test', 18, 'shenzhen');
INSERT INTO test.test2 (id, user_name, age, address) VALUES (2, 'test2', 21, 'guangzhou');
INSERT INTO test.test2 (id, user_name, age, address) VALUES (4, 'test1', 20, 'shenzhen');
INSERT INTO test.test2 (id, user_name, age, address) VALUES (5, 'test2', 30, 'shanghai');
INSERT INTO test.test2 (id, user_name, age, address) VALUES (13, 'test3', 22, 'beijing');
INSERT INTO test.test2 (id, user_name, age, address) VALUES (19, 'test4', 24, 'guangzhou');
INSERT INTO test.test2 (id, user_name, age, address) VALUES (25, 'test5', 26, 'zhongshan');
INSERT INTO test.test2 (id, user_name, age, address) VALUES (37, 'test', 18, 'shenzhen');

全局锁

全局锁会对整个MySQL数据库实例加锁。在加锁期间，整个数据库实例将处于只读状态。这意味着任何数据修改语句（DML）、数据定义语句（DDL）以及试图提交更新操作的事务都将被阻塞。

作用：全局锁的典型使用场景包括进行全库逻辑备份。通过对所有表加锁，可以确保获得一致性的数据库快照，从而保证备份数据的完整性和一致性。

语法

加全局锁

1	`FLUSH TABLES WITH READ LOCK`

数据备份

1	`mysqldump -uroot -pPassword [database name] > [dump file]`

mysqldump 也可用于远程连接备份。

释放锁

1	`unlock tables;`

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

主库备份的问题：在主库上进行带全局锁的备份会阻止所有更新操作，这可能会导致业务暂停或性能下降。
从库备份的问题：在从库上进行备份时，由于全局锁的存在，从库可能无法应用主库同步过来的二进制日志（binlog），导致主从复制的延迟。

InnoDB的备份策略优化

对于InnoDB引擎，可以使用 mysqldump --single-transaction 参数来进行不加锁的一致性数据备份。
此选项会启动一个新的事务，并将隔离级别设置为REPEATABLE READ，确保在备份过程中数据的一致性。由于InnoDB的多版本并发控制（MVCC）特性，这种备份方式不会锁定表。

1	`mysqldump --single-transaction -u root -p[Password] [database name] > [dump file]`

注意：在使用--single-transaction参数进行备份时，应确保备份期间没有执行DDL操作（如ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE等），因为这些操作可能会影响事务的一致性读。

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中

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

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

表锁

对于表锁，分为两类：

读锁（共享锁, Read Lock）
- 特点：允许多个会话对表进行读取操作，但不允许任何会话进行写操作。
- 适用场景：适合在数据不需要被修改的情况下进行大量的读取操作，如数据分析或报告生成。
- 加锁方式：可以通过LOCK TABLES table_name READ命令显示加读锁。
写锁（排他锁, Write Lock）
- 特点：只允许一个会话对表进行读写操作，阻止其他会话的所有读写操作。
- 适用场景：适合需要修改数据且要防止其他会话读取或修改同一数据的情况。
- 加锁方式：可以通过LOCK TABLES table_name WRITE命令显示加写锁。
释放锁：unlock tables

元数据锁

作用：

自动加锁：MDL（Meta Data Lock）的加锁过程是由MySQL系统自动控制的，无需用户显式操作。在访问或修改表的结构时，MySQL会自动加上相应的MDL。
数据一致性：MDL的主要作用是维护表的元数据（如表结构）的一致性。当一张表上有活动事务时，不允许对该表的元数据进行修改。
防止冲突：MDL用于避免DML（数据修改语句）和DDL（数据定义语句）之间的冲突，保证数据库操作的正确性。

应用：

表的结构修改：当表涉及到未提交的事务时，不允许修改该表的结构（例如，通过ALTER TABLE）。
版本引入：MDL在MySQL 5.5中被引入，用于提高数据库操作的安全性和一致性。
锁类型：
- 对于非结构性的表操作（如查询、插入、更新、删除），MySQL加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互斥

操作

查看元数据锁

1 2	`select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;`

元数据排它锁

1
2
3

begin;
alter table test2
add column test int;

下面操作将阻塞

1 2	`begin; update test set user_name='test1' where id = 1`

意向锁

没有意向锁时的上锁检查

上锁前先获取意向锁

定义：意向锁是一种表级锁，它表明事务打算在表中的某些行上加行级锁。

两种类型：

意向共享锁（Intention Shared Lock, IS）：表明事务打算在表中的某些行上加共享锁。
意向排他锁（Intention Exclusive Lock, IX）：表明事务打算在表中的某些行上加排他锁。

作用：

兼容性检查：意向锁使得InnoDB能够快速判断是否可以在表上加表级锁。例如，如果有事务持有意向排他锁，InnoDB知道不能在该表上加共享表级锁，意向锁是一个标识，为了解决表锁和行锁冲突而设计的锁。
避免死锁：通过在表级别上快速检查锁的兼容性，意向锁有助于避免死锁的发生。

意向锁实践

意向共享锁(IS): 由语句select … lock in share mode添加。与表锁共享(read)兼容，与表锁排他锁(write)互斥
意向排他锁(IX): 由insert、update、delete、select…for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥

事务的提交或回滚意向锁都会释放

意向共享锁

# 上行共享锁
begin;

select *
from test2
where id = 1 lock in share mode;

1 2	`# 此时上表读锁可以兼容，因为意向共享锁兼容表共享锁 lock tables test2 read;`

意向排它锁

# 上行排它锁
begin;
update test2
    set user_name = 'test2'
where id = 1;

1 2	`# 此时不能上锁，意向排它锁和表排他锁互斥 lock tables test2 write;`

查看意向锁和行锁加锁情况

1 2	`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 隔离级别下都支持

间隙锁

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

临键锁

临键锁（Next-Key Lock）：行锁 和 间隙锁 组合，同时锁住数据，并锁住数据前面的间隙Gap，在 RR 隔离级别下支持

行锁

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

针对 唯一索引 进行检索时，对 已存在的记录进行等值匹配时，将会自动优化为 行锁

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

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

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

SQL	行锁类型	说明
INSERT …	排他锁	自动加锁
UPDATE …	排他锁	自动加锁
DELETE …	排他锁	自动加锁
SELECT（正常）	不加任何锁
SELECT … LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

共享锁

begin;
select *
from test2
where id = 1 lock in share mode; # 唯一索引加共享锁，行锁

begin;
select *
from test2
where id = 1 lock in share mode; # 唯一索引加共享锁，行锁

在两个会话中分别开启事务，对id为1的数据进行加锁，结果是两个会话都可以将数据查询出来

查看加锁情况，发现id=的数据被加上了两把共享锁

1 2	`select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;`

行共享锁

排它锁

排它锁与排他锁之间互斥

当会话一执行 id=1 的 update 语句后，如果事务没有提交，那么会话一的事务会一直持有 id=1 这条数据的锁，其他会话事务的上锁操作将被阻塞，除非会话一的事务提交或回滚

begin;

update test2
set user_name = 'test99'
where id = 1;

begin;

select *
from test2
for update;

此时会话二被阻塞，查看行锁情况，id=1 的排他锁确实没有释放

begin;

select *
from test2;

打开会话三开启新事务进行查询，查询不会阻塞（快照读），因为 select 不加锁

升级表锁

InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁

begin;

update test2
set user_name = 'test99'
where user_name = 'test';

此时我们先查询锁的占用情况，发现表的所有id都被加了排他锁，原因就是行锁是对索引加锁的，user_name 字段没有加锁则升级为全局锁

间隙锁

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

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

产生间隙锁原因：

RR 隔离级别
对不存在的记录上锁
对索引列进行范围查询（in，between）

begin;

select *
from test2
where id between 10 and 20
for update;

这里看到间隙锁标志，此时对 (5，12]，（13，18]，（19，24] 上间隙锁

begin;

insert into test2 (id, user_name, age)
values (8, 'test18', 18);

由于间隙锁的缘故，id = 8 的插入操作将被阻塞

临键锁

RR级别解决幻读：在进行 范围查询 或 索引扫描 时，InnoDB会在涉及的索引范围内设置间隙锁和行锁，防止其他事务在这些范围内插入或删除记录。

# 创建普通索引
create index idx_age on test2(age);

select *
from test2
where age between 18 and 21
for update;

场景：

范围查询：当对非唯一索引执行范围查询（如WHERE column < 10）时，InnoDB会使用临键锁锁定匹配范围内的所有记录以及这些记录之间的间隙。
非唯一索引上的等值查询：对于非唯一索引的等值查询（如WHERE column = 5），由于可能有多条记录具有相同的索引值，InnoDB同样会使用临键锁来锁定这些记录和间隙。
索引扫描：在执行索引扫描时（如全表扫描或使用索引进行部分表扫描），InnoDB会对扫描到的记录以及记录之间的间隙加临键锁。
插入操作：当插入一条记录时，InnoDB会对新插入的记录前的间隙加锁，以防止幻读。
更新和删除操作：在执行更新或删除操作时，如果这些操作涉及到非唯一索引上的范围查询或是非唯一值匹配，InnoDB也会使用临键锁。

锁优化

避免行锁升级为表锁

控制事务大小，减少锁定的资源量和锁定时间长度,比如分批
合理设计索引，尽量缩小锁的范围(DML一定要使用索引列)
条件是大范围，可以按照范围分批

死锁

相互等待

T1	T2
set autocommit = 0;	set autocommit = 0;
select * from user where id = 1 for update;	select * from user where id = 2 for update;
select * from user where id = 2 for update;
此时T1等待T2释放锁

update join 多表加锁

T1
set autocommit = 0;
update user a join address b on a.address_id = b.id
set a.user_name = 'kkk'
where b.id = 1;

T2
update address
set address_name='HN'
where id = 1
此时T2会等待T1释放 address id=1 行锁

delete join 多表加锁

T1
set autocommit = 0;
delete a
from user a join address b on a.address_id = b.id
where a.id = 1;

T2
set autocommit  = 0;
update address
set address_name='HN'
where id = 1
T2等待T1释放 id = 1 的行锁

解决死锁

查看是否锁表： show OPEN TABLES where In_use > 0
查询进程： show processlist
杀死进程id（就是上面命令的id列）： kill id

Innodb 底层原理

架构图

从MySQL 5.5版本开始，InnoDB成为了默认的存储引擎。这一变化标志着对事务安全和性能的重视。Innodb引擎具有以下特点：

事务处理：专为事务处理设计，支持ACID（原子性、一致性、隔离性、持久性）事务，确保数据库操作的可靠性和完整性。
行级锁定和MVCC：支持行级锁定和多版本并发控制（MVCC），优化了并发操作，减少了锁争用，提高了性能。
外键约束：支持外键，允许在表之间创建参照完整性约束，这是其他一些存储引擎（如MyISAM）不支持的功能。
缓冲池：拥有缓冲池（buffer pool）机制，用于缓存数据和索引，减少磁盘I/O操作，提升查询性能。
数据存储：使用聚簇索引来存储表数据，这意味着表数据实际上存储在索引的叶节点上。
崩溃恢复：具备崩溃恢复能力，通过日志（如重做日志，即redo log）来保证数据在系统崩溃后的完整性和一致性。

外存架构

表空间 table space

定义：表空间是数据库中用于存储数据的逻辑单元。它定义了数据存储的位置（物理文件）和方式。表空间可以包含一个或多个文件，这些文件位于文件系统上。

如果用户启用了参数 innodb_file_per_table (在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd）一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。表空间存储的对象是段，由一个或多个段组成

表空间

默认情况下，MySql的表空间目录在 /var/lib/mysql 目录下，每个数据库对应一个目录，每张表对应一个 ibd文件

分类

系统表空间
独立表空间
通用表空间
- 通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间
undo表空间
- MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储 undo log日志
临时表空间
- InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据

作用

数据组织：表空间允许将数据库数据分割成不同的部分，每部分可以单独管理和优化。
存储管理：例如，可以将不同的表空间放置在不同的磁盘或存储设备上，以优化性能和空间使用。
备份和恢复：可以单独备份或恢复特定的表空间。

语法

1 2	`# 创建表空间 CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;`

1 2	`# 创建表时指定表空间 CREATE TABLE xxx ... TABLESPACE ts_name;`

段 segment

段是一组具有相似特性的连续页（pages）的集合，每个段都用于特定类型的数据存储，对存储数据进行分类，段用来管理多个Extent（区）。

分类

数据段（Data Segment）：用于存储表的行数据。
索引段（Index Segment）：用于存储B+树索引结构，包括主键索引和辅助索引。
回滚段（Rollback Segment）：存储事务回滚时所需的数据。
临时段（Temporary Segment）：用于存储临时数据，如排序操作或哈希表。

作用

空间管理：每个段负责管理其内部的页，包括分配新页和回收不再使用的页。
责任分离：不同类型的段支持不同功能，如数据存储、索引维护和事务处理。
数据分类：通过将数据划分到不同的段中，InnoDB可以更有效地组织数据。

区 extent

一个区是由连续的页（Page）组成的数据块。在InnoDB中，默认情况下，一个区包含 1MB 的空间，这相当于连续的 64 个 16KB 大小的页。

作用

优化空间管理：需要分配或回收大量空间时，InnoDB可以按区而不是页来操作，从而提高效率。
提升访问效率：在处理大量数据时，区能提高数据访问效率。

页 page

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

1 2	`查看页大小 SHOW GLOBAL STATUS like 'Innodb_page_size';`

数据结构

页头 PageHeader：记录页面控制信息；56个字节。包括页的左右兄弟页指针、页面空间使用情况等
虚记录：用于确定当前页的记录范围
- 最小虚记录：最小记录是数据页上最小的记录，比页内最小主键小
- 最大虚记录：最大记录是这个数据页中逻辑上最大的记录，比页内最大主键还大
记录堆 ：行记录存储区，包含已删除记录的空间（大链表）
自由空间链表: 把被删除的记录空间链起来。便于重复利用空间
未分配空间: 还未使用的记录堆
Slot区 （槽位）：槽位的作用是将页内的数据链表拆分成多个子链表（hashTable思想），类似跳表
页尾: 8个字节，主要存储页面的校验信息

页内记录维护

顺序保证

物理有序插入删除需要移动数据（连续的存储空间），IO操作较多，插入效率不理想。基本不会使用
逻辑有序使用链表实现，但是查询效率没有物理有序好（物理有序可以使用二分法）

InnoDB必然是使用逻辑有序，物理有序写操作没有优化空间，逻辑有序的查询可以通过数据结构和算法进行优化（槽位）

插入策略

优先使用自由空间链表，减少空洞，其次使用未分配空间

即使是优先使用自由空间链表也不能保证表没有空洞，因为每条数据的长度不一致

页内查询

页内槽位定位：每个槽位对应于页内某个位置的记录，通过二分查找快速定位到接近目标记录的槽位。
链表遍历：每个槽位都有一个链表，包含了所有映射到该槽位的记录。
找到目标记录：根据槽位定位到链表后，在链表中顺序查找直到找到匹配的记录。

类型

数据页：存储表的实际行数据。
索引页：存储B+树索引结构，包括主键索引和辅助索引。
Undo页：存储事务的undo信息，用于数据恢复和MVCC。
系统页：存储表空间的元数据和其他系统信息。

行 row

行（Row）是数据存储和处理的基本单位。

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

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

聚簇索引

数据结构：B+树

聚簇索引并不是一种单独的索引类型，而是一种 InnoDB 数据存储方式

内存架构

InnoDB会将近期使用的数据先加载到内存中，当内存满了则根据LRU算法将内存中的数据写盘或者释放。释放出内存给其他热点数据使用。

主要分为这么四大块：

Buffer Pool
Change Buffer
AdaptiveHash Index
Log Buffer

Buffer Pool

缓冲池（Buffer Pool）：一个内存中的区域，用于缓存InnoDB存储引擎最频繁访问的数据，包括数据页（Data Pages）和索引页（Index Pages）。

作用：

减少I/O操作：通过在内存中缓存数据和索引，缓冲池减少了对磁盘的读写次数，从而提高了数据访问速度。
数据页缓存：存储表数据的数据页在被访问时加载到缓冲池中。
索引页缓存：用于表索引的B+树结构的索引页同样被缓存，以加快索引查找和维护速度。

管理：

LRU算法：InnoDB使用优化后的 双段LRU算法 来管理缓冲池中的页面。确保最活跃的数据保持在缓冲池中。
脏页刷新：当缓冲池中的数据页被修改后，它们最终会被写回到磁盘以保持数据的持久性。这个过程称为“刷新”（Flush）。

Buffer Pool默认大小是 128M，以 Page 页为单位，Page页默认大小 16K，而控制块的大小约为数据页的5%，大概是 800字节。

1 2	`# 查看 buffer pool 信息 show global variables like 'innodb_buffer%';`

变量	说明
`innodb_buffer_pool_chunk_size`	定义InnoDB缓冲池大小调整操作的块大小
`innodb_buffer_pool_size`	缓冲池大小
`innodb_buffer_pool_instances`	InnoDB 缓冲池划分为的区域数

缓冲池中页的类型

free page ：
- 定义：空闲页是尚未被分配用来存储数据的页。
- 位置：这些页位于Free List中，等待被分配用于存储新的数据或索引信息。
- 特点：它们是完全空白的，不包含任何有效数据。
clean page ：
- 定义：干净页是已被分配并包含数据的页，但自上次读入缓冲池或写回磁盘后未被修改。
- 位置：这些页位于LRU List中，表示它们是最近被访问过的，但并不需要写回磁盘，因为它们与磁盘上的数据一致。
- 特点：在需要为新的数据页或更“脏”的页腾出空间时，干净页可以被较快地从缓冲池中移除，因为它们不需要额外的磁盘写入操作。
dirty page：
- 定义：脏页是已被分配并包含数据的页，且自上次读入缓冲池后已被修改。
- 位置：这些页同时位于LRU List和Flush List中。LRU List表示它们是最近被访问过的，而Flush List用于跟踪需要被写回磁盘的脏页。
- 特点：脏页需要在适当时机写回磁盘以保证数据的持久性和一致性。InnoDB会定期将脏页刷新回磁盘，特别是在事务提交或检查点（Checkpoint）发生时。

Page List

Free List：
- 定义：包含当前未使用的页，即那些没有分配任何数据的空白页。
- 用途：当InnoDB需要新的页来存储数据或索引时，它会从Free List中分配页。
LRU List ：
- 定义：LRU列表用于存储最近被访问过的页。
- 干净页（Clean Page）：已分配且包含数据，但与磁盘上的数据一致，未被修改过。
- 脏页（Dirty Page）：已分配且包含数据，且自上次读入后已被修改。
Flush List：
- 定义：用于跟踪那些标记为脏的页，即需要被写回磁盘的页。
- 用途：Flush List确保数据的持久化和一致性，特别是在事务提交或达到检查点（Checkpoint）时。

Page Hash表

映射关系维护：
- 描述：Page Hash维护了磁盘上数据页和Buffer Pool中页的映射关系。
- 快速查找：这种映射机制允许InnoDB快速确定某个特定的数据页是否已经在Buffer Pool中，以及它在Buffer Pool中的具体位置。
- 判断缓存：通过Page Hash，InnoDB可以快速判断某个页是否已经在Buffer Pool中，从而避免不必要的磁盘I/O操作。
数据页加载单位：
- 以页为单位：InnoDB从磁盘加载数据到内存的操作是以页为单位进行的，每个页通常为16KB。
- 不以行为单位：虽然基于行的加载可能提高内存利用率，但以页为单位可以优化I/O效率，尤其是对于排序、分页查询等操作。

页面装载流程

页面装载单位：数据库从磁盘加载数据到内存时，是以页（默认是16KB）为单位进行的。当数据库需要读取磁盘上的数据时，它会检查该数据是否已经在Buffer Pool中。如果不在，则从磁盘加载相应的页。
使用Free List：当有新的页需要加载到Buffer Pool时，InnoDB首先会尝试使用Free List中的空闲页。
LRU List淘汰：Free List中没有可用的空闲页，InnoDB会从LRU List的冷数据端开始驱逐，腾出空间加载新的数据页。
脏页刷新：当LRU List驱逐数据后还是无法加载新的数据页，则将脏页进行刷新，保存到磁盘。

Change Buffer

定义：

优化写操作：Change Buffer是一个内存中的缓冲区域，用于暂时存储对非唯一索引的修改操作。这些修改操作随后会被批量地合并到磁盘上的实际索引页中。
减少I/O需求：对于非唯一索引的修改，如果每次修改都直接写入磁盘，将会产生大量的随机I/O。Change Buffer通过合并这些操作，减少了I/O操作的次数。

工作机制：

缓存修改操作：当对一个非唯一索引进行插入、删除或更新操作时，如果相应的 索引页 不在缓冲池中，InnoDB会将这些修改操作记录到Change Buffer中，而不是立即从磁盘加载索引页。
延迟合并：随后，在后台，当这些被修改的索引页因其他查询或操作而被加载到缓冲池时，InnoDB会将Change Buffer中的修改与这些页中的数据合并。
优化I/O：这种延迟合并操作减少了对磁盘的即时I/O需求，因为它避免了为了执行每个小的索引修改而频繁地从磁盘读取和写入索引页。

为什么只针对非唯一索引优化

唯一约束校验：对于具有唯一性约束的索引，任何插入和更新操作都直接在Buffer Pool中进行，确保唯一性约束在修改时被即时验证，它们不能被缓存在Change Buffer中。

Log Buffer

定义：

缓存事务日志：Log Buffer是一个在内存中的缓存区域，用于暂时存储事务的日志信息（Redo Log），比如数据的修改操作。
减少磁盘I/O：通过首先将日志信息写入到Log Buffer，InnoDB减少了直接写入到磁盘的操作，提高I/O性能。
提高事务性能：Log Buffer允许事务快速完成，因为事务的日志信息不需要即时写入磁盘。

工作原理：

记录事务日志：当执行事务时，所有的修改操作（如INSERT、UPDATE、DELETE）会生成日志记录，并首先被写入Log Buffer。
异步刷新到磁盘：Log Buffer中的内容会定期或在特定事件（如事务提交）时被刷新到磁盘上的重做日志文件（Redo Log）中。
日志序列号（LSN）：每个日志记录都有一个唯一的日志序列号，用于跟踪和管理日志记录。

参数配置

innodb_log_buffer_size：设置Log Buffer的大小。根据事务的大小和频率进行适当调整。
1
2
# 查看 Log Buffer 大小 SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';
innodb_flush_log_at_trx_commit
控制重做日志写入和刷新到磁盘的时机：
- 0：
  - 事务提交时不会立即将日志写入磁盘，而是将日志缓存在操作系统的页缓存中，之后由操作系统决定何时将其刷新到磁盘。
  - 这种模式提供了最高的性能，但在崩溃的情况下，会丢失数据。
- 1（默认值）：
  - 每次事务提交时，InnoDB都会将日志刷新到磁盘。
  - 这提供了最高的数据持久性保证，因为即使发生崩溃，最近提交的事务也不会丢失。
  - 因为每次事务都需要磁盘I/O而降低性能。
- 2：
  - 日志只在每个事务提交时写入日志缓冲区，每秒钟将日志刷新到磁盘一次。
  - 这提供了折中的性能和持久性保证，但在发生崩溃时，最近一秒的事务可能会丢失。

性能考虑：如果存在大量的更新、插入或删除操作，增加Log Buffer的大小可以减少I/O操作，特别是在高并发的OLTP系统中。

自适应Hash索引

InnoDB会自动监控对表上索引的查询模式。当它发现某些查询符合使用哈希索引的效率优势时，就会为这些查询自动创建哈希索引。这个过程是完全自动的，不需要用户干预。

使用场景：

频繁等值查询：当表上的特定数据行被频繁查询时，例如通过主键或唯一索引的等值查询。
读密集型应用：适用于读操作远多于写操作的应用场景。

创建条件：当InnoDB检测到对某些 索引 的等值查询非常频繁时，它会自动在内存中为这些索引创建哈希索引。

配置

默认情况下，自适应哈希索引功能是开启的。
可以通过SET GLOBAL innodb_adaptive_hash_index=OFF/ON;来关闭或开启这一功能。
使用SHOW VARIABLES LIKE '%adaptive_hash_index';来查看当前设置。

Hash索引的优缺点

双写缓冲区

MySQL Buffer 一页的大小是 16K，文件系统一页的大小是 4K，也就是说，MySQL将 Buffer 中一页数据刷入磁盘，要写4个文件系统里的页。

注意：Innodb Page 默认不是 4K 是因为考虑I/O效率，较大的页大小可以减少数据库在读写操作时需要的I/O次数，更大尺寸的页适合于表扫描和大范围查询。

如上图所示，MySQL里page=1的页，等于物理磁盘的四个页，刷盘的这个操作并非原子，如果执行到一半断电或宕机，就会出现 页数据损坏。

页数据损坏

如上图所示，MySQL内page=1的页准备刷入磁盘，才刷了3个文件系统里的页，断电了。重启后，page=1的页，物理上对应磁盘上的1+2+3+4四个格，数据完整性被破坏。

解决页数据损坏

如上图所示，当有页数据要刷盘时：

预写入：当脏页被刷新到磁盘时，InnoDB首先将这些页的副本写入双写缓冲区（一个特定的磁盘区域）。
确认写入：只有双写缓冲区数据成功写入磁盘文件后，InnoDB才会将这些页写入实际的表空间文件。
恢复使用：如果发生崩溃，InnoDB在重启时会检查双写缓冲区，以确保页的完整性。

内外存数据交换

内存数据淘汰

触发条件

Buffer Pool空间不足。
需要加载新的数据页到内存。

内存淘汰算法 LRU

LRU： (Least recently used) 最近最少使用。

使用全表扫描对 普通LRU 算法的影响：

如果全表扫描一张大表，根据 普通LRU 算法，会淘汰内存中所有数据。
全表扫描的数据可能就只使用一次，造成 缓存污染。

MySql 改良的URL

InnoDB实际上使用一个单一的LRU链表，但这个链表被分为两个部分：热数据区域（LRU列表的前端）和冷数据区域（LRU列表的尾部）。这种分区有助于保护频繁访问的热点数据不被轻易淘汰。

页面淘汰时机：当Buffer Pool中的Free List没有空闲页，并且需要加载新的数据页时，InnoDB会根据LRU算法从LRU List中淘汰页。
保护热数据：为了减少全表扫描等操作对Buffer Pool的影响，InnoDB引入了中段插入策略。新加载的页不是直接被放到LRU列表的最前端，而是被放置在列表的 中间位置。

淘汰流程：

淘汰LRU列表尾部的页：优先淘汰LRU列表尾部的数据，是较少访问的冷数据。
脏页的处理：如果需要淘汰的页是脏页（即已修改但未写回磁盘的页），则这些页需要先被刷新（写回）到磁盘。
锁定页的处理：如果LRU列表尾部的某些页被锁定或无法淘汰（例如，由于正在参与活跃事务），则向前移动，寻找可淘汰的页。
重新分配到Free List：一旦页被淘汰，它们的空间被释放回Free List，供新的页使用。

冷数据变为热数据

当数据页被首次读取到Buffer Pool时，它们被放入LRU链表的中间部分，即所谓的 冷数据 区域。这是为了防止大规模的非顺序读取（如全表扫描）迅速淘汰掉已经存在于LRU链表前端的热点数据。

冷数据晋升热数据条件：

初次加载：当数据页首次加载到Buffer Pool时，被放置在LRU链表的冷数据区域（冷数据区域头部）。
存活时间：数据页在冷数据区域中必须存活一定时间，这个时间由参数innodb_old_blocks_time设定（单位是毫秒）。默认值通常是1000毫秒（1秒）。
再次访问：如果在存活时间过后，这个页再次被访问，它将从冷数据区域移动到热数据区域（LRU链表的前端）。这标志着数据页从冷数据变成了热数据。
访问频率：只有那些被重新访问的页才会从冷数据区晋升到热数据区，这意味着频繁访问的页更有可能成为热数据。

1 2	`# 必须在冷数据区停留多长才有资格晋升热数据区 show variables like 'innodb_old_blocks_time'`

总结：在冷数据区存活一定时间，并且再次访问。

好处：进行全表扫描频繁淘汰的区域是冷数据区，热数据区域的热点数据不会受影响。

热数据淘汰

Midpoint

定义：Midpoint是LRU链表中的一个指针，标识着热数据区域和冷数据区域的分界点。
指针移动：
- 向左移动：热数据区域将数据淘汰到冷数据区域。
- 向右移动：冷数据区域的数据晋升到热数据区域。
位置保持：热数据区域占整个LRU链表的大约 5/8，而冷数据区域占 3/8。数据的晋升和淘汰让指针一直保持这个比例。

数据淘汰优化：

free_page_clock：是一个全局计数器，当缓冲池中的页被访问时 free_page_clock 会递增。
快照计数器的值：一个页从冷数据区域晋升到热数据区域时，会记录当前的 free_page_clock 值。
数据移动：当前的 free_page_clock 与页晋升时的快照差超过热区域长度的 1/4 时，数据页才向左移动。
降低频率：这种机制降低了页移动的频率，从而减少了由频繁的页移动引起的性能开销。

后台线程

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

Master Thread

职责：Master Thread是InnoDB的核心后台线程，负责多种调度和维护任务。
主要功能:
- 异步刷新：将缓冲池中的脏页异步刷新到磁盘。
- 合并Change Buffer：合并Change Buffer中的数据到实际的索引页。
- Undo页的回收：清理不再需要的Undo页。

IO Thread

AIO处理：InnoDB大量使用异步I/O（AIO）来提高性能，IO Thread主要负责处理这些异步I/O请求。
职责：
- Read Thread：处理读取操作。
- Write Thread：处理写操作。
- Log Thread：负责将重做日志（Redo Log）刷新到磁盘。
- Insert Buffer Thread：处理Change Buffer的合并操作。

Purge Thread

Undo日志回收：回收事务提交后不再需要的Undo日志，释放空间。

Page Cleaner Thread

辅助刷新脏页：主要职责是协助Master Thread将脏页刷新到磁盘，减轻Master Thread的负担，降低刷新操作对性能的影响。

事务原理

事务是数据库执行过程中的一个逻辑单位，由一系列的操作组成。这些操作要么全部成功，要么全部失败。事务处理保证了即使在系统故障的情况下，操作的完整性也不会被破坏。

事务的四大特性：

原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败，不会停留在中间状态。
一致性（Consistency）：事务必须保证数据库从一个一致性状态转移到另一个一致性状态。
隔离性（Isolation）：事务的执行不会被其他事务干扰，多个并发事务之间的数据库操作是隔离的。
持久性（Durability）：一旦事务完成，其对数据库的修改应该是永久性的，即使出现系统故障。

原理：

Redo Log：保证事务的 持久性，即使在系统崩溃后，已提交事务的修改不会丢失。
Undo Log：支持 原子性 和 一致性，用于在事务失败时回滚操作，并支持MVCC中的读一致性。
隔离性保证
- 写隔离：InnoDB通过锁定机制来维护事务的隔离性，防止数据在并发环境下被破坏。
- 读隔离：允许多个事务同时读取同一数据而无需等待其他事务完成（MVCC快照读取）。

redo log

重做日志是事务持久性的关键，通过记录数据页的物理修改日志，它确保了即使在数据库崩溃的情况下，所有已提交事务的修改都能被恢复。

组成：

重做日志缓冲（Log Buffer）：
- 功能：位于内存中，用于临时存放生成的重做日志记录。这些记录表示了事务对数据页的物理修改。
- 作用：使用内存缓冲区可以提高日志记录的写入效率，减少对磁盘的直接写操作。
重做日志文件（Redo Log File）：
- 持久存储：位于磁盘上，用于持久化存储事务的修改。这些文件确保了即使在系统崩溃的情况下，所有已提交的事务修改都可以被恢复。
- 循环写入：包含多个文件，以循环方式写入，当一个文件写满后，日志写入移动到下一个文件。
  - 当前重做日志文件（例如，ib_logfile0）写满后，日志写入操作会转移到下一个文件（例如，ib_logfile1）。
  - 当所有的重做日志文件都被写满后，系统会回到第一个文件并开始覆盖旧的日志记录，从而形成一个循环。

redo log作用

背景：InnoDB对数据的修改在缓冲池中会产生 脏页。脏页不是实时刷新到磁盘的，而是在特定时机进行刷新，那么内存数据和磁盘数据就有一段时间不一致。

作用：

保证事务持久性：当事务执行数据修改操作时，这些更改首先被记录到 Redo Log Buffer 中，然后根据策略刷新到磁盘的Redo Log文件中。
支持数据恢复：在数据库崩溃后重启时，InnoDB使用Redo Log中的信息来恢复崩溃时尚未刷新到磁盘的脏页数据，恢复未刷盘的事务操作。

工作流程：

修改数据页：当事务执行数据修改操作时，这些更改首先在内存中的缓冲池（Buffer Pool）上进行，修改后的数据页变成了所谓的“脏页”。
记录Redo Log：与此同时，对这些脏页的修改会生成相应的重做日志记录，这些记录被写入到内存中的 Log Buffer。
刷新Redo Log：Log Buffer中的内容会根据特定的策略定期或在事务提交时刷新到磁盘上的Redo Log文件中。
脏页刷新：脏页的刷新到磁盘是一个独立的过程，它可能发生在事务提交之后的某个时间点，由InnoDB的后台进程管理。

事务提交为什么要先写redo log

Write-Ahead Logging（WAL，先写日志）：是一种在数据库管理系统中广泛使用的日志技术，核心原则是在任何数据库数据被永久写入到磁盘之前，先将其修改操作写入到日志中。

提高事务执行效率：直接写入数据文件涉及随机I/O操作，这在大多数存储介质上效率较低。相比之下，写入Redo Log文件是顺序I/O操作，效率更高（WAL）。
简化崩溃恢复过程：使用WAL，数据库恢复只需重放Redo Log文件中记录的操作，而无需检查和重写每个数据文件。

undo log

回滚日志是用来记录数据变更前的状态。当事务进行修改操作时，会同时记录相应的Undo Log，以便在事务回滚时使用。

作用

支持事务回滚：如果事务失败或被显式回滚，用来恢复数据到事务开始之前的状态。
实现MVCC：多版本并发控制（MVCC）中，Undo Log被用来为不同事务提供历史数据的快照。即使数据被一个事务修改了，其他事务仍然可以看到修改前的数据版本。

生命周期

生成：当事务执行数据修改操作（如INSERT、UPDATE、DELETE）时，InnoDB会生成相应的Undo Log。
存储：Undo Log以段（segment）的形式存储在 表空间 中，每个Undo段包含多个Undo日志页。
销毁：当没有任何事务需要访问Undo Log中的旧数据版本时，这些日志记录才会被清理。

MVCC

定义：

全称：Multi-Version Concurrency Control（多版本并发控制）。
作用：MVCC允许在数据库中维护数据的多个版本，这样就可以在读写操作中减少冲突，提高并发性能。

实现机制：

隐式字段：每条数据库记录包含隐式字段。
- DB_TRX_ID（事务ID）：记录了最后修改该记录的事务ID。
- DB_ROLL_PTR（回滚指针）：指向该记录对应的undo log记录。
Undo Log：存储了数据的旧版本信息，用于在读取时提供历史数据，以及在需要时回滚事务。
Read View：在事务开始时创建的一致性视图，用于保证事务在执行期间看到数据库的一个一致性状态。

当前读

定义：当前读（Current Read）确保读取数据的最新版本，同时在读取过程中对数据加锁，防止其他并发事务对这些数据进行修改。
加锁目的：加锁机制防止了数据在读取过程中被其他并发事务所更改。
当前读的操作类型：
- 加共享锁的读：SELECT ... LOCK IN SHARE MODE，这个操作在读取数据的同时对其加上共享锁，允许其他事务读取但阻止修改。
- 加排他锁的读：
  - SELECT ... FOR UPDATE：对所选记录加排他锁，阻止其他事务读取或修改这些记录。
  - UPDATE、INSERT、DELETE：这些DML操作不仅修改数据，而且还会对所涉及的记录加排他锁。

快照读

定义：指的是在执行普通的SELECT查询（不加锁）时，读取的数据版本取决于事务开始的时间点。它不会看到在事务开始之后其他事务所做的修改。
基于MVCC：快照读的实现基于MVCC机制，MVCC通过维护数据的不同版本来允许多个读写操作并发执行，而不直接影响彼此。

特点：

一致性视图：快照读提供了一个事务开始时的数据视图，避免了读取过程中的数据变动。
非阻塞操作：由于快照读不加锁，它允许其他事务并发地修改数据。
避免非重复读：在Repeatable Read级别下，快照读避免了非重复读的问题，即在同一事务中多次读取同一数据集返回的结果始终保持一致。

事务隔离级别的影响：

Read Committed：每次SELECT查询都会读取最新提交的数据。这意味着如果其他事务在两次查询之间提交了更改，后续的查询将看到这些更改。。
Repeatable Read：事务中的第一个SELECT创建一个快照，后续的SELECT查询在事务内将读取这个快照，即使其他事务提交了更改。
Serializable：最高的事务隔离级别，它要求事务之间完全串行执行，退化为当前读。

隐藏字段

在创建表时，InnoDB会自动创建两个隐藏字段用来实现 MVCC。

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

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

测试

快照读

RR 隔离级别

T1事务	T2事务
begin; 开启事务
select * from test2 where id = 100; //查询不到	begin; 开启事务
	insert into test2(id, user_name, age, address) values(100, ‘test100’, 20, null);
	commit;
select * from test2 where id = 100; //快照读不可见
select * from test2 where id = 100 for update; //当前读可见
commit;

版本链

工作机制

版本链构建：InnoDB通过DB_ROLL_PTR字段在undo log中构建了一个版本链，每个版本代表了数据行在历史上的某个状态。
读取操作：当进行快照读（如普通SELECT查询）时，InnoDB会根据事务的版本（由DB_TRX_ID和Read View确定）在版本链中查找相应的数据版本。
版本可见性：根据当前事务的ID和Read View，判断各个历史版本是否对当前事务可见。

作用

支持MVCC：版本链是MVCC实现的核心，允许事务访问数据的一致性历史版本，而不影响其他事务的操作。
高效并发控制：通过维护数据的多个版本，InnoDB能够支持高并发的读写操作，减少锁的需求。

Read View

定义：Read View（读视图）是一个用于实现多版本并发控制（MVCC）的内部数据结构，它定义了在一个给定事务中哪些数据版本是可见的。

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

字段	含义
m_ids	当前活跃的事务列表，包括ReadView创建时刻所有已开始但未提交的事务ID
m_up_limit_id	最小活跃事务ID，`m_ids`集合中的最小事务ID，小于这个事务ID的数据都是可见的
m_low_limit_id	下一个将要被分配的事务ID，是ReadView可见事务的上界
m_creator_trx_id	ReadView创建者的事务ID，标识创建此ReadView的事务

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

READ COMMITTED ：在每个SQL语句执行前生成ReadView。
REPEATABLE READ：只在事务的第一个SELECT语句执行时生成。一旦生成，整个事务期间都会使用这个ReadView。

事务可见性判断

RR 隔离级别：

创建快照这一刻，还未提交的事务不能读。
创建快照之后创建的事务不能读。

ReadView判断过程

RR 隔离级别：

生成ReadView：当事务进行第一次SELECT查询时，InnoDB为该事务生成一个ReadView。ReadView中包含当前活跃的事务ID集合（m_ids），最小活跃事务ID（min_trx_id），以及下一个将要分配的事务ID（max_trx_id）。
读取判断：
- 如果数据的创建事务ID小于 最小活跃事务id，说明这个事务在当前事务开始之前已提交，因此数据行对当前事务是可见的。
- 如果数据的创建事务ID 在活跃事务id中，说明当前事务是活跃的，数据对当前事务是不可见。
- 如果数据的创建事务ID大于或等于 下一个要分配的事务ID，说明这个事务在当前事务开始之后开始，因此数据行对当前事务是不可见的。

查询流程

查询缓存（Query Cache）：
- 在解析之前，MySQL会检查查询缓存。如果相同的查询之前已执行并且结果存储在查询缓存中，则直接返回缓存结果。
解析器（Parser）：
- 当用户发出SQL查询时，MySQL首先通过解析器对SQL语句进行语法分析。解析器检查SQL语句的语法是否正确，并将其转换为一种内部格式的查询表示（解析树）。
预处理器（Preprocessor）：
- 在解析后，预处理器进一步检查解析树的语义正确性，比如表和列的存在性、数据类型的匹配等。
优化器（Optimizer）：
- 优化器负责查询的优化。它分析多种可能的执行计划，选择一种成本最低的计划来执行查询。这包括决定使用哪些索引，如何连接表等。
执行查询执行（Execution）：
- MySQL根据执行计划对数据库进行操作。在InnoDB存储引擎中，这涉及到如下几个步骤：
  - a. 行锁定：如果是当前读，会对数据行进行锁定。
  - b. 数据检索：访问存储在磁盘上的数据页面，可能涉及到索引的使用。
  - c. 缓冲池（Buffer Pool）：InnoDB会首先检查数据是否已在缓冲池中。如果不在，从磁盘读取数据并放入缓冲池。
  - d. ReadView：在RR隔离级别下，如果进行的是一致性读操作，InnoDB会创建一个ReadView来保证数据的一致性。
结果返回：
- 执行完查询后，将结果返回给用户。对于SELECT查询，这包括了从数据行中检索出的数据；对于UPDATE、INSERT或DELETE查询，这包括了影响的行数等信息。

查询缓存

工作机制：
- 当一个SELECT查询执行时，MySQL首先检查查询缓存。
- 如果找到一个与当前查询完全相同（文本匹配，包括空格和注释）的缓存条目，MySQL就会直接返回缓存中的结果。
- 如果没有找到匹配的缓存，查询将继续进行SQL解析、优化和执行。
缓存失效：
- 查询缓存对数据变动非常敏感。当任何涉及缓存查询的表被修改（INSERT、UPDATE、DELETE等），所有相关的缓存条目都会立即失效。
局限性：
- 查询缓存在高并发和高更新场景下成为性能瓶颈，因为频繁的缓存失效和重建会消耗大量资源。

1 2	`# 查询查询缓存是否开启 show variables like '%query_cache%';`

查询缓存被移除：在MySQL 8.0及以后的版本中，查询缓存功能被完全移除。这是因为查询缓存在现代高并发数据库系统中往往带来更多的性能问题而非益处。
性能优化：MySQL 8.0集中优化了查询执行器和优化器，提供更高效的查询处理机制，尤其是在高并发场景下。

语法解析和预处理

语法解析

分析SQL语句：检查SQL语句的语法是否正确。这包括识别关键字、运算符、表名、列名等。
生成解析树：如果语法没有错误，解析器将SQL语句转换为一种内部表示形式，是一种解析树（也称为语法树）。这个树结构表示了SQL语句的组成部分及其关系。
错误处理：如果解析器发现语法错误，它会停止处理并返回错误消息。

预处理

语义检查：确保SQL语句在语义上是有效的。这包括检查引用的表和列是否存在，数据类型是否匹配，解析名称等。
权限验证：检查执行该查询的用户是否具有相应的权限。
变量替换：处理SQL语句中的变量和参数。
解析表达式：评估SQL语句中的表达式。

查询优化

优化器

查询重写：优化器可能会改写查询，例如简化查询条件、去除冗余的条件等。
索引选择：优化器根据可用的索引和统计信息来决定是否使用索引，以及使用哪个索引。
连接顺序：在涉及多个表的查询中，确定连接这些表的最有效顺序。
连接策略：选择适合的连接算法，如嵌套循环连接（Nested-Loop Join）或哈希连接（Hash Join）。

成本估算和执行计划选择

基于表的统计信息和索引特性，优化器会估算不同执行计划的成本，包括I/O成本、CPU成本等。
最终选择总成本最低的执行计划。

查询执行引擎

查询执行：执行引擎根据优化器提供的执行计划执行查询，包括从存储引擎检索数据、执行联接、处理排序和聚合等操作。
存储引擎接口：查询执行过程中，执行引擎会调用存储引擎（如InnoDB）提供的接口，这些接口被称为handler API。它们用于完成数据的实际读取、写入以及处理事务等任务。

返回客户端结果

缓存结果：如果查询可以被缓存，mysql会在这个阶段将结果存放到查询缓存中，8.0后移除。
流式返回：一旦查询生成了第一条结果，就开始向客户端逐步发送结果，而不需要等待整个结果集生成完毕。

写入流程

开启新的事务
数据修改：执行INSERT、UPDATE或DELETE操作时，首先在缓冲池中修改数据页。如果所需页不在缓冲池中，则先从磁盘读取该页到缓冲池。
日志记录：
- undo log：对每项数据进行修改前都会记录数据修改前的状态（undo log），用于事务回滚。
- redo log：对每项数据进行修改都会生成重做日志条目（Redo log）并暂存到 Log Buffer 中。
索引处理：
- 唯一索引检查：如果修改涉及到唯一索引，必须检查新数据是否违反唯一性约束。
- 非唯一索引：如果索引页不在缓冲池，先将索引的修改操作暂存到 Change Buffer 中，等待索引被加载后进行索引页合并。
事务提交：
- Prepare（准备）：将 Log Buffer 中的 Redu Log 刷新到磁盘日志文件，用于数据异常恢复。
- Commit（提交）：在所有 Redo Log 成功写入磁盘后，在重做日志中添加一个 事务提交 的记录，标志着事务已经成功提交。
  - bin log：事务的binlog事件首先写入系统缓冲区，并根据 sync_binlog 的配置决定何时刷新到磁盘。

sync_binlog：

值为 0：Mysql不主动刷新缓冲区，由操作系统进行刷盘，效率最高。
值为 1（默认）：事务提交后马上对 bin log 进行刷盘，增加数据安全。
值大于 1：指定在这么多次事务提交后刷新 bin log 到磁盘，可在数据安全性和性能之间做出权衡。

bin log

binlog，或称为二进制日志，用于记录数据库中所有修改数据的操作，如INSERT、UPDATE、DELETE和DDL（数据定义语言）操作如CREATE TABLE、ALTER TABLE。

作用：

数据复制：binlog是MySQL主从复制的基础。主服务器上的binlog记录了所有的数据变更，这些记录会被复制到从服务器并重放，实现数据的一致性。
数据恢复：在发生数据丢失时，可以使用 binlog 来恢复操作，将数据库恢复到特定的时间点。

三种模式：

Statement：基于语句的复制。这种模式下，binlog记录了执行的SQL语句。
Row（8.0默认）：基于行的复制。记录了更改后行的内容。
Mixed：混合模式，结合了语句和行模式。
- 语句复制：正常情况下使用。
- 行复制：使用了自定义函数。

SQL语句复制 Statement

把所有的修改语句都记录在bin log中。

优点：由于只记录了SQL语句本身，而非行级别的变化，减少I/O开销，从而提高性能。
缺点：特殊场景可能导致主从数据不一致，尤其是当使用一些非确定性的函数（如NOW()、RAND()）或依赖于数据库状态的查询（如LAST_INSERT_ID()）时。

行复制 ROW

记录的是每行数据变更的具体内容，包括修改前和修改后的数据

优点：

数据一致性：由于记录了具体的行变化，减少由于执行上下文差异引起的数据不一致问题。

缺点：

日志体积：较大的日志体积，特别是在进行大量数据变更的操作时（如批量插入或更新）。
复制效率：进行大量数据变更的操作会产生大量日志，复制效率低。

混合复制

根据上下文自动选择语句复制或行复制。

优点：

效率和一致性：通过自动转换模式找到复制效率和数据一致性的平衡点。
灵活性：自动选择最合适的复制方式，根据不同类型的操作动态调整。

缺点：

复杂性：自动切换复制模式可能导致复制过程的复杂性增加，需要更细致的监控和管理。

常见问题

主键使用自增还是UUID

auto_increment

优点：

性能提升：自增主键新数据插入在表的末尾，减少了页分裂，提升写入性能。
空间效率：数字型主键占用空间小，比如INT或BIGINT类型，有利于提高检索性能。
易于索引和排序：数字主键对索引和排序操作更为高效，因为数字比较操作通常比字符串或其他类型的比较更快。
简化设计：自增主键由数据库自动管理，简化了数据插入过程，无需手动生成主键。

缺点：

业务量暴露：自增主键可能暴露业务量等敏感信息，存在安全隐患。
自增锁竞争：在高并发场景下，自增锁竞争可能降低数据库的吞吐能力。
扩展性限制：在数据迁移、分库分表场景中，自增主键的全局唯一性维护困难。

UUID

UUID泛指自动生成单调递增的ID。

优点：

全局唯一性：在全局范围内确保唯一性，适合 分布式 系统和需要唯一标识符的场景。
提升插入性能：在应用层生成，无需数据库支持。

缺点：

实现复杂：增加系统复杂性，要保证全局唯一，单调递增。
性能问题：如果非数字类型，会占用更大的存储空间，主键检索效率降低。
数据页分裂：如果无法保证单调递增将导致数据库中的数据页分裂，影响插入操作的性能。

B树、B+树的区别

数据存储：B树在每个节点中存储键和数据。而B+树叶子节点存储键和数据，非叶子节点仅存储键。
范围查询：B+树叶子节点使用指针两两相连，便于支持范围查询和排序。
I/O效率：B+树的非叶子节点不存储数据，可以存储更多的键，从而树的高度更低，在相同数据量的情况下减少磁盘I/O次数。

MyISAM 和 Innodb的区别

使用Innodb于MyIsam特性做比较

MyISAM

MyISAM 存储引擎

5.5版本之前的默认引擎。
支持全文索引。
不支持事务。
锁级别是表锁，不支持行级锁。
不支持外键约束。
索引和数据分开存储。

Innodb

InnoDB存储引擎

支持事务，MVCC。
使用聚簇索引，索引和数据一起存储。
支持行级锁，间隙锁。
支持外键约束。

mysql的索引有哪些，聚簇和非聚簇索引又是什么

参考索引

按数据结构分

B+树索引
hash索引

按特性分

主键索引
唯一索引
普通索引
组合索引
前缀索引

聚簇索引：聚簇索引是一种数据组织方式，非叶子节点存储数据的键，叶子节点存储数据的键和值，叶子节点两两相连。

非聚簇索: 也叫二级索引，非聚簇索引是表的一个单独的索引结构，包含了索引列的值和对应行的主键值，但不包含行的完整数据。

回表查询:

当通过非聚簇索引检索数据时，如果需要的数据不全部包含在索引中，就会进行回表查询。
回表查询是一个两步过程：首先，通过非聚簇索引找到对应行的主键；然后，使用这个主键去聚簇索引中检索完整的行数据。

什么是索引下推

索引下推

在索引扫描阶段，MySQL能够在索引内部应用WHERE子句的一部分条件，从而仅检索那些可能符合所有搜索条件的行。

什么是执行计划

explain 执行计划

锁的类型有哪些

数据库进阶锁

按粒度分

全局锁
表级锁
行级锁

按类型分

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
排他锁（X）：写锁是排他的，它会阻塞其他的写锁和读锁

按种类分

全局锁
表级锁
行级锁

事务的基本特性和隔离级别

事务原理

基本特性

原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败，不会停留在中间状态。
一致性（Consistency）：事务必须保证数据库从一个一致性状态转移到另一个一致性状态。
隔离性（Isolation）：事务的执行不会被其他事务干扰，多个并发事务之间的数据库操作是隔离的。
持久性（Durability）：一旦事务完成，其对数据库的修改应该是永久性的，即使出现系统故障。

隔离级别

隔离级别（+:允许出现，-:不允许出现）	脏读	不可重复读	幻读
未提交读	+	+	+
提交读	-	+	+
可重复读	-	-	+
序列化读	-	-	-

问题	说明
脏读	在一个事务中读取到了另一个事务未提交的数据。这可能导致读取到错误的信息。
不可重复读	在同一个事务中，对同一数据的多次读取结果不一致。这通常是由于在两次读取之间，另一个事务修改了数据。
幻读	在同一个事务中，两次执行相同的查询，返回的结果集不一致。通常是因为在两次查询之间，另一个事务插入或删除了数据行。

可重复读级别解决幻读：在可重复读隔离级别下，InnoDB通过使用临键锁（结合行锁和间隙锁）防止了幻读问题。在进行 范围查询 或 索引扫描 时，InnoDB会在涉及的索引范围内设置间隙锁和行锁，防止其他事务在这些范围内插入或删除记录。

ACID靠什么保证

事务原理

什么是幻读，什么是MVCC

幻读

MVCC

什么是间隙锁

间隙锁是可重复读级别下才会有的锁，用于解决幻读问题。

间隙锁

分库分表怎么做

首先分库分表分为垂直和水平两个方式，拆分的顺序是先垂直后水平。

垂直分库

定义：垂直分库是指按照业务模块或服务功能将数据库分成独立的部分，每个部分包含相关的表和数据。这种分库策略通常与微服务架构相结合，实现服务的数据隔离。

实现方式：在微服务架构中，每个服务通常对应一个专用的数据库。这样，服务间的数据库操作互不干扰，实现了数据和业务的解耦。

优点：

提高性能：由于数据库较小，查询效率更高。
故障隔离：故障影响范围限于单个服务，不会波及整个系统。

缺点：

数据一致性：跨服务的数据一致性维护变得更加复杂。
资源冗余：每个服务都需要单独的数据库实例，导致资源使用上的冗余。

垂直分表

定义：垂直分表是指将一个数据表按照字段进行拆分，将不同的字段划分到不同的表中。这种拆分通常基于字段的使用频率、数据大小或相关性

实现方式：

常用字段和不常用字段分离：将频繁访问的字段和较少访问的字段分离到不同的表中。
大字段分离：将数据量大的字段（如文本、BLOB等）从主表中分离出去，以优化主表的访问性能。

优点:

提高性能：减少单个查询的数据量，提高查询速度和效率。
提高可维护性：简化了表结构，使得维护和管理更加容易。

缺点：

增加复杂性：查询涉及多个表时，需要进行表间的关联查询，增加了查询的复杂性。

水平分表

定义：水平分表指的是将一个表中的行分割成多个较小的表，每个表包含相同的列，但只存储原始表中的一部分行。这种分割通常基于某些列的值，如时间戳、地理位置或其他业务相关的键。

时间分割法：是一种基于时间维度来进行水平分表的方法。比如按照月、季度、年或其他时间单位将数据分割到不同的表中。
Hash取模法：是根据分片键的hash值对数据进行分表。选取一个或多个具有业务意义的字段作为分片键，对这个字段的值取hash，然后根据分表的数量进行取模运算来决定数据存储在哪个分表中。

映射法

定义：分表映射法是将数据从一个大表分散到多个小表中，并使用一个额外的 映射表 来记录数据在这些分表中的位置（比如ID）。映射表根据关键查询字段来指向相应的分表和行，从而优化数据的检索过程。

实现过程：

数据分散：根据一定的规则，如范围、哈希值或其他业务逻辑，将原始大表中的数据分散到多个小表中。
创建映射表：建立一个映射表，包含用于查询的字段和子表的主键以及数据对应表的位置。
查询流程：
- 当需要查询特定数据时，首先在映射表中查询以确定目标数据存储在哪个分表的哪个位置（ID）。
- 然后直接访问该分表来获取详细数据。

优点：

高效查询：映射表通过索引覆盖快速定位子表位置。

缺点：

跨子表查询的性能下降：如果查询需要涉及多个子表（尤其是跨表查询），会导致性能下降，因为这相当于在多个表上进行查询和汇总。
关联查询：通过子表字段实现关联困难。

业务后缀法

定义：业务后缀法是一种将大表按照业务逻辑分割成多个小表的方法。每个小表都具有相同的结构，但表名通常会附加一个与业务相关的后缀，以标识它包含特定业务或数据段的数据。

实现过程：

确定分表依据：根据业务逻辑选择适当的分表依据，如用户地区、用户类型、产品类别等。
创建分表：对于每个业务维度或类别，创建一个单独的表。表名通常包含一个后缀，如orders_us、orders_uk等，表示不同地区的订单数据。
数据分配：根据业务规则将数据插入到相应的分表中。

优点：

容易实现：根据业务快速实现水平分表，比如初始化。
业务清晰：每个表对应特定的业务逻辑，使得数据组织更加清晰。

缺点：

数据分布不均：如果业务分布不均衡，某些表可能会比其他表数据量大很多，导致性能瓶颈。
维护难度：当业务逻辑变更时，可能需要调整分表策略，增加了维护的难度

基因注入法

定义：通过一个或多个业务字段生成一个key，把该key作为分片键。

实现过程：

选择分片键：根据业务需求选择一个或多个字段，这些字段具有将数据合理分散的特性。
生成分片键：通过这些字段计算得出一个分片键，可以是哈希值、组合键或其他形式的唯一标识符。
数据分配：根据分片键的值将数据分散到不同的分表中。例如，可以根据分片键的哈希值对分表数量取模来决定数据应该存储在哪个表中。

优点：通过算法将数据更均匀分配到各个子表中。

缺点：需要按照业务场景实现pub字段算法。

时效分表法

数据具有时效性

在某个时间段内双写表，某个时间后自动删除旧表

使用场景：

数据具有时效性。
比如系统消息。

定义：基于时间属性对数据进行分表，将数据插入到两张表中。在数据达到某个时间阈值后，通过删除或归档旧表来管理历史数据。

要求：数据不会被频繁更新。

实现过程：

时间段表：比如根据创建时间按月，按年分表。
双写策略：将新数据同时写入当前活跃表和即将成为活跃的新表。这确保了在切换到新表时，数据的连续性和完整性。
旧表处理：在切换到新表后的某个时间点，自动删除或归档旧表

优点：

防止成为大表：通过时间不段创建和删除表，控制数据规模。
历史数据查询：通过双写策略，既保证了历史数据的可查询性，也保持了查询高效。

缺点：

增加写入负载：在双写阶段，对两个表的写入会增加数据库的负载。

分表后的ID怎么保证唯一性

主键的选择要符合单调递增，有效减少页合并和分裂

号段模式
- 描述：使用数据库记录当前最大的全局ID，并每次分配一个固定范围的号段（如1到1000）给应用服务器。服务器在本地内存中缓存这些ID，并逐个使用。
- 适用场景：适合高并发环境。
- 注意事项：需要处理号段分配和回收，防止号段耗尽或冲突。
雪花算法（Snowflake）
- 描述：结合时间戳、机器标识和序列号生成唯一ID。只要服务器时间保持向前，就能保证ID的唯一性。
- 适用场景：分布式系统。
- 注意事项：需要确保服务器时钟的准确性和同步。
Redis的INCR命令
- 描述：使用Redis的单线程特性，通过INCR命令生成递增的唯一数字ID。
- 适用场景：轻量级应用或非极端高唯一性要求的场景。
- 注意事项：在Redis故障重启或数据丢失时可能存在风险。
美团Leaf
- 描述：美团的Leaf是一个分布式ID生成服务，结合号段模式和雪花算法等多种策略来生成唯一ID。
- 适用场景：需要高度定制化的大型分布式环境。
- 注意事项：提供了高可用性和可扩展性，适合服务化部署。

分表后非sharding_key的查询怎么处理

使用索引表（映射表）：建立一个额外的索引表（或映射表），记录非sharding key与所在分表的映射关系。查询时，先查询索引表确定数据所在的分表，再对这些分表执行查询。
使用搜索引擎ES：将分表数据同步给搜索引擎，使用搜索引擎对数据进行检索。
使用中间件：使用支持分表的数据库中间件，如 MyCAT、Shardingsphere，这些中间件提供跨分表查询能力。

MySql主从同步怎么做的

事务提交与二进制日志（Binlog）：在主服务器（master）上，提交的事务被写入二进制日志（binlog）。这个日志记录了改变数据库状态的所有操作。
从服务器与主服务器建立连接：从服务器（slave）上的I/O线程连接到主服务器，并请求从上一次已知的binlog位置开始的binlog内容。
主服务器的Dump线程：主服务器创建一个dump线程来处理来自从服务器的请求。该线程将binlog的内容发送到从服务器。
中继日志（Relay Log）：从服务器上的I/O线程读取从主服务器接收到的binlog，并将其记录到本地的中继日志（relay log）中。
执行同步操作：从服务器上的SQL线程读取中继日志中的事件，并在从服务器数据库上执行这些操作，从而实现数据的同步。
从服务器的二进制日志：如果启用了从服务器的binlog（在进行链式复制或备份时），则从服务器上的操作也会被记录到其自己的binlog中。

同步模式

异步复制（Asynchronous Replication 默认）

工作机制：在异步复制中，主服务器在事务提交后不等待从服务器确认即完成事务。主服务器的二进制日志事件（binlog events）被发送到从服务器，但主服务器不会等待从服务器确认它们已接收和处理这些事件。
特点：
- 性能：更高的性能，因为主服务器不需要等待从服务器的响应。
- 数据延迟：会出现数据延迟，从服务器可能落后于主服务器。
适用场景：适用于对数据实时性要求不高的场景。

半同步复制（Semi-Synchronous Replication）

工作机制：在半同步复制中，主服务器在事务提交后会等待至少一个从服务器确认已接收到二进制日志事件后才视为事务完成。如果在指定时间内没有从服务器确认，主服务器将退回到异步复制模式。
特点：
- 数据安全性：提高了数据一致性和可靠性，减少了数据丢失的风险。
- 性能影响：影响写入性能，因为主服务器需要等待从服务器的确认。
适用场景：适用于需要平衡数据可靠性和系统性能的场景。

全同步复制（Fully Synchronous Replication）

工作机制：全同步复制意味着所有的事务在主服务器和从服务器上几乎同时提交。比如使用多主节点复制系统（如Galera Cluster）实现。
特点：
- 数据一致性：提供强数据一致性保证。
- 性能影响：写入性能最差，需要在所有节点上确认。
适用场景：适用于对数据一致性有严格要求的高可用性集群环境。

binlog同步支持哪些格式

binlog

主从的延迟怎么解决

硬件和网络优化
并行复制
调整复制格式
- 比如使用行格式（ROW）的二进制日志可以减少从服务器应用更改所需的时间，因为它不需要解析SQL语句。

并行复制是指从服务器（slave）上并行（多线程）应用主服务器（master）的二进制日志（binlog）中的事件，以提高复制性能和减少延迟的机制。在传统的串行复制中，从服务器上的SQL线程会按照二进制日志中的事件顺序逐个执行这些事件。

并行可选粒度：

数据库：
- 适用版本：MySQL 5.6及以上。
- 粒度：以数据库为单位进行并行复制。这意味着来自不同数据库的事务可以在从服务器上并行执行。
- 限制：同一数据库内的事务仍然会串行执行，这限制了并行度。
组提交：
- 适用版本：MySQL 5.7及以上。
- 粒度：通过对事务进行分组，如果组内事务能同时提交成功，那么它们就不会共享任何锁（冲突）。
- 优势：组内事务并行复制，提供了更高的并行度。
事务写集：
- 适用版本：MySQL 8.0及以上。
- 写集：用行级别粒度判断事务之间的依赖关系，在有主键或唯一键的表中，只要两个事务没有更新同一行，就能并行回放。
- 粒度：基于每个事务的写集（即事务修改的数据集合）来判断事务之间是否存在冲突，从而实现更细粒度的并行复制。
- 优势：进一步提高并行复制的效率，特别是在事务修改不同行或列时。

读写分离延迟导致数据不一致解决

读取主库
- 策略：对于需要立即读取最新写入数据的操作，直接从主库读取。
- 实施：在应用层逻辑中判断，如果是刚刚写入的数据或对实时性要求高的查询，则直接连接到主数据库执行查询。
监控主从延迟
- 策略：定期或在关键操作前检查主从同步的延迟。
- 实施：使用 SHOW SLAVE STATUS 命令获取从服务器 Seconds_Behind_Master 的值，判断主从同步是否有显著延迟后动态切换。
同步模式
- 策略：异步复制模式修改为半同步或全同步模式。
- 实施：使用组复制或Galera Cluster集群方案。
使用缓存
- 策略：使用缓存系统来存储最新写入的数据标记。
- 实施：
  - 在写入数据后，将相关数据或标记存入缓存系统（如Redis、本地缓存、客户端缓存等）。
  - 读取操作首先查询缓存，如果缓存中存在最新数据，则使用缓存数据；如果没有，则从数据库读取。

训练脚本

create table student
(
sno       varchar(3) primary key,
sname     varchar(8),
sex       varchar(2),
sbirthday datetime,
class     varchar(5)
) default charset = utf8;

insert into student
values ('108', '曾华', '男', '1977-09-01', '95033'),
 ('105', '匡明', '男', '1975-10-02', '95031'),
 ('107', '王丽', '女', '1976-01-23', '95033'),
 ('101', '李军', '男', '1976-02-20', '95033'),
 ('109', '王芳', '女', '1975-02-10', '95031'),
 ('103', '陆君', '男', '1974-06-03', '95031');

create table course
(
cno   varchar(5) primary key,
cname varchar(10),
tno   varchar(3)
) default charset = utf8;

insert into course
values ('3-105', '计算机导论', '825'),
 ('3-245', '操作系统', '804'),
 ('6-166', '数字电路', '856'),
 ('9-888', '高等数学', '831');

create table score
(
sno    varchar(3),
cno    varchar(5),
degree decimal(4, 1)
) default charset = utf8;

insert into score
values ('103', '3-245', 86),
 ('105', '3-245', 75),
 ('109', '3-245', 68),
 ('103', '3-105', 92),
 ('105', '3-105', 88),
 ('109', '3-105', 76),
 ('101', '3-105', 64),
 ('107', '3-105', 91),
 ('108', '3-105', 78),
 ('101', '6-166', 85),
 ('107', '6-166', 79),
 ('108', '6-166', 81);

create table teacher
(
tno       varchar(3),
tname     varchar(8),
tsex      varchar(2),
tbirthday datetime,
prof      varchar(6),
depart    varchar(10)
) default charset = utf8;

insert into teacher
values ('804', '李诚', '男', '1958-12-02', '副教授', '计算机系'),
 ('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系'),
 ('825', '王萍', '女', '1972-05-05', '助教', '计算机系'),
 ('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

# 等级
create table grade
(
low    int(3),
upp    int(3),
`rank` char(1)
);
insert into grade
values (90, 100, 'A');
insert into grade
values (80, 89, 'B');
insert into grade
values (70, 79, 'C');
insert into grade
values (60, 69, 'D');
insert into grade
values (0, 59, 'E');

Mysql

https://wugengfeng.cn/2022/02/26/Mysql/

作者

wugengfeng

发布于

2022年2月26日

许可协议

Sort 上一篇

Kafka 下一篇

逻辑运算符	功能
AND 或 &&	并且 (多个条件同时成立)
OR 或	或者 (多个条件任意一个成立)
NOT 或 !	非 , 不是