MySQL 学习记录

基本常识

row-size-limits
blob：

BLOB and TEXT columns cannot have DEFAULT values.
Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types.Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns.

增加 varchar 类型的字段的长度
- Changing the maximum length of a varchar column?
- 修改表字段长度的操作，对业务是否有影响？
- varchar(n)、char(n) ：其中得 n 均表示字符数，而非字节数
1. The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length.
2. Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
NOT NULL：MySQL中的NULL其实是占用空间的。
官方文档说明:

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

可见为了表示某个字段是否为空是需要额外开辟空间存储Null值，不仅如此，不使用NULL可以提高索引效率，因为树形索引结构中将NULL也视作一般数据节点。所以：

尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

MySQL查看数据库表容量大小
MySQL中索引的长度的限制

联合索引的长度的限制：不能超过3072bytes。
我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。所以一个记录最多不能超过8k。又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，primay-key和某个二级索引都达到这个限制）。由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是 (1024bytes*3=3072bytes)。

MySQL中varchar类型的字段最大长度是多少

Performance of MySQL ALTER TABLE ADD COLUMN AFTER COLUMN - on a large table

not official You’d get more CPU usage since records would have to be shifted. From the memory usage point of view - it’d be the same with AFTER COLUMN option and without it. In most cases, a tmp table is created. There are MySQL engines that support hot schema changes (TokuDB being one) that don’t create the tmp table and waste tons of resources. However, if you’re doing this with MyISAM or InnoDB - I’d say that “AFTER COLUMN” option will take slightly more time due to record shifting.

字符集

utf8 字符集只是 Unicode 字符集的一种变长编码方案，使用 1-4 个字节存储字符，Unicode 字符集还可采用 utf16（两或四字节）、utf32（四字节）这几种编码方案。

我们常用的一些字符使用 1～3 个字节就可以表示了。而在 MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计MySQL 设计者定义了两个概念：

utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。
utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符

底层结构

为什么选择B+树

MySQL 为什么选择 B+ 树，而不选择 ~~（平衡二叉树）、~~ B 树：当数据量太大时，B 树太深，会因加载太多的页面产生太多的 IO，从而导致查询速度很慢。

在这里插入图片描述
当页面大小为 16kb，B+ 树高为 3 层时，如果每条记录的长度为 1kb，则 MySQL 可以存储 2.19kw+ 条记录。
数据节点使用双向链表链接，从而能很好地支持范围查询。

在这里插入图片描述
Hash 查询不支持范围查找，无法满足大多数业务场景。

存储引擎的区别

在这里插入图片描述

MyISAM

MySQL 5.5 之前默认的存储引擎
在这里插入图片描述
数据和索引分开存储。

InnoDB

MySQL 5.5 及之后默认的存储引擎
在这里插入图片描述
（主键/聚集/聚簇）索引即数据

为什么不使用 UUID 作为表的主键：1）字符串比较按字符逐位比较，查询性能过低；2）字符串太长，浪费存储资源；3）无序字段的插入容易导致页分裂影响性能（比如数据插入效率），磁盘页面利用率也不高。

InnoDB vs MyISAM

在这里插入图片描述

Memory

在这里插入图片描述

CSV

在这里插入图片描述

为什么不使用 SELECT *

MySQL 的连接查询一般是通过 NLJ（嵌套循环查询）来实现的，但当被驱动表不能有效的利用索引加快访问速度时，也可通过 BNLJ（基于块的嵌套循环查询）来进行优化，即 MySQL 会申请 join_buffer_size 大小的 join bufer 用来存储驱动表的记录。驱动表的记录并不是所有列都会被放到 join buffer 中，只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中，所以最好不要把 * 作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在 join buffer 中放置更多的记录，从而减少读取被驱动表的次数，从而提高查询性能。

实战问题

MySQL 使用 order by limit 分页排序会导致数据丢失和重复！

在 MySQL 关系型数据库中，往往会存在多种排序算法。通过 MySQL 的源码和官方文档介绍可以得知，它的排序规律可以总结如下：

当 order by 不使用索引进行排序时，将使用排序算法进行排序；
若排序内容能全部放入内存，则仅在内存中使用快速排序；
若排序内容不能全部放入内存，则分批次将排好序的内容放入文件，然后将多个文件进行归并排序；
从 MySQL 5.6 版本开始，优化器在使用 order by limit 时，使用堆排序优化排序过程。

语法

连接查询

UNION

SQL UNION 操作符

NATURAL JOIN（SQL 99 新特性）

在这里插入图片描述

USING （SQL 99 新特性）

在这里插入图片描述

WHERE vs ON

在外连接查询中，放在不同地方的过滤条件是有不同语义的：

WHERE子句中的过滤条件

WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

ON子句中的过滤条件

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。
需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

数据库优化

表结构优化

拆分表：冷热数据分离

在这里插入图片描述

引入中间表

在这里插入图片描述

使用非空约束

在这里插入图片描述

读写优化

限定查询范围

禁止不带任何限制数据范围的查询语句。比如：查询商品订单时，限制只查询历史一个月的数据。

读写分离

主库负责写操作，从库负责读操作
在这里插入图片描述

垂直拆分

在这里插入图片描述

水平拆分

在这里插入图片描述

应用场景：DPA 的商品库

事务

在这里插入图片描述

特性：ACID

原子性

在这里插入图片描述

一致性

在这里插入图片描述

隔离性

在这里插入图片描述

事务并发执行可能遇到的问题

在这里插入图片描述

脏写

对于两个事务 sessionA、sessionB，如果 sessionB 修改了 sessionA 未提交的修改过的数据，那么就发生了脏写。
在这里插入图片描述

脏读

对于两个事务 sessionA、sessionB，如果 sessionB 读取了 sessionA 回滚过的修改过的数据，那么就发生了脏读。

不可重复读

如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读。

幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

在这里插入图片描述
注意：

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

隔离级别

在这里插入图片描述
注意：

MySQL在 REPEATABLE READ 隔离级别下，是可以禁止幻读问题的发生的（MySQL MVCC 的 ReadView 实现保证了可重复读不会出现幻读）。
MySQL 的默认隔离级别为 REPEATABLE READ。

设置隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
-- 其中的 level 可选值有4个：
-- 1. REPEATABLE READ
-- 2. READ COMMITTED
-- 3. READ UNCOMMITTED
-- 4. SERIALIZABLE

查看隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';
-- 或者
SELECT @@transaction_isolation;

隔离级别实现原理方案

在这里插入图片描述

持久性

在这里插入图片描述

总结

在这里插入图片描述

undo vs redo vs binlog

在这里插入图片描述

MySQL整体来看，其实就有两块：一块是 Server 层，它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。 redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。

为什么会有两份日志呢？
因为最开始MySQL里并没有InnoDB引擎。 MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力， binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL
的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统— — 也就是redo log来实现crash-safe能力。

redo log 与 binlog 有以下三点不同。

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
redo log是循环写的，空间固定会用完； binlog是可以追加写入的。 “追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

redolog 与 undolog 之间的区别

redolog 记录的是数据页的物理变化，服务宕机可用来同步数据； undolog 记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据。
redolog 保证了事务的持久性，undolog 保证了事务的原子性、和隔离性（MVCC）。

binlog 不具备 crash-safe 能力？

当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经刷盘，哪些数据还没有。

举个栗子，binlog 记录了一条日志：UPDATE test_table SET a = a + 1 WHERE id = 1

数据库崩溃重启后，无法通过 binlog 日志判断 id = 1 这条记录的改动是否落入到磁盘上，直接通过 binlog 重放两条 update 操作，若这条记录的改动之前已写入到磁盘中，那这条记录会被多加 1。但 redo 日志不一样，只要被刷入到磁盘的数据，都会从 redo log 中抹掉，且 redo 日志是物理日志，数据库重启后，直接读取 redo 日志文件中的日志定位到相应的页面的偏移位置，就可以将崩溃前的提交事务数据恢复出来。

事务状态

在这里插入图片描述

日志

Redo

在这里插入图片描述

刷盘过程

在这里插入图片描述

阿里云：调整实例innodb_flush_log_at_trx_commit和 sync_binlog参数

三种刷盘方式性能对比测试

在这里插入图片描述

注：sync_binlog这个参数设置成1，表示每次事务提交，都会将binlog 持久化到磁盘，保证MySQL异常重启之后binlog不丢失。

特点

在这里插入图片描述

redo log 写入机制

在这里插入图片描述

MySQL中的Redo Log(三)

【聊聊MySQL】八.MySQL-InnoDB的数据库事务的执行之REDO_LOG

Redo 日志无锁化设计并发写入 log buffer

源码 | 解析 Redo Log 实现方式

InnoDB 的 Redo Log 是一组文件的集合，默认是两个。每个日志文件又由一组 512 Byte 大小的日志块组成。每个日志文件前 4 个日志块保留。其中第一个日志文件里的前 4 块保存着 Redo 日志的元数据信息。日志文件大小在初始化就已经确定，日志块逻辑上组成一个环，循环使用。checkpoint 是崩溃恢复过程中应用日志的起点。如果 checkpoint 块写入如果出现故障或者掉电，InnoDB 就无法找到日志的起点。
所以每个日志文件中有两个块（分别为 checkpoint1、checkpoint2）来存储 checkpoint_lsn，避免因为介质失败而导致无法找到可用的 checkpoint 的情况。两个 checkpoint 块轮换写入，遇到写入 checkpoint 块失败，可以在另一个 checkpoint 块上取得上次的 checkpoint_lsn 做恢复。

checkpoint

当数据库发生崩溃后，可利用 redo log 进行数据实例的恢复，但是如果 Redo 文件太大，在做实例恢复时所需要的时间就会变得太长，显然这是不可被容忍的，因此，在数据库引入了 checkpoint 机制。

作用

缩短数据库的恢复时间：checkpoint 之前的数据都已经刷新到磁盘，故数据库故障恢复数据，只需执行 redo 日志文件 checkpoint 后的日志，这样就大大地减少了恢复时间。
Buffer Pool 不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强行执行checkpoint,将脏页也就是页的新版本刷回到磁盘。
redo日志文件没有空间可写入时，将脏页刷新到磁盘

流程概述

提交给 MySQL InnoDB 的更新操作，会对缓存在 Buffer Pool 中的页面做修改，被修改的页面会被放入到 flush 链表（使用头插法插入，所以越靠近链尾的页面，其 File Header 中的 oldest_modification_lsn 值就越小，就越先被刷新到磁盘）中，同时调整该页面在 LRU 链表中的位置（当 Buffer Pool 不够用时，将链表中尾部的页面从 Buffer Pool 中移出）。一次更新操作可能由多个 mtr（mini-transaction）组成，每个 mtr 包含了多条 redo 日志，日志将以 mtr 为组（为每组日志申请一个 lsn）写入到以 buf_free 为起始地址的 redo log buffer 中。redo log buffer 中的日志将会被以一定的策略（策略由 MySQL 的 innodb_flush_log_at_trx_commit 变量控制，当 redo-log-buffer 的使用量超过一半时也会触发刷新）刷新到操作系统缓存 page cache 中，并更新 buf_next_to_write，操作系统定期（一般为 1s）将其刷新到磁盘上后，会更新 flushed_to_disk_lsn。

buffer pool 中的 flush 链表或 LRU 中的脏页被刷新到磁盘后，会将被刷页面 File Header 中的 oldest_modification_lsn 值去更新磁盘 redo 日志文件前四块中的 checkpoint 块（第二、四块都是 checkpoint 块，保证容错性）中的数据，即 redo 日志文件中 lsn 小于 checkpoint 的数据可以被覆盖写，以达到 redo_log_file_group 中的日志文件可以被循环利用的目的。

当 MySQL（崩溃）重启后，会先去读 redo 日志文件中 checkpoint 之后的日志去更新相应页面的数据，以保证在崩溃时，还未来得及刷新 flush 链表中的已经被 commit 的页面数据能正确地被更改为 commit 时的值，以保证事务的持久性。

Redo 日志和 binlog 日志协调工作

说到这里就要提到两阶段提交，当事务 commit 时，先在 Redo 日志上打上 prepare 标记，随后会调用 fsync 系统调用将 binlog 写入到磁盘，最后在 Redo 日志记录上打上 commit 标记表示记录提交完成。　
在这里插入图片描述
MySQL 架构:

事务的两阶段提交是为了保证两份日志的逻辑一致。

假设当前ID=2的行，字段c的值是0，再假设执行update语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，会出现什么情况呢？

先写redo log后写binlog。假设在redo log写完， binlog还没有写完的时候， MySQL进程异常重启。由于我们前面说过的， redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

Undo

执行更新操作之前，会先写 undo 日志。

MVCC

版本链

为了实现事务的原子性， InnoDB 存储引擎在实际进行增、删、改一条记录时，都需要先把对应的 undo日志记下来。一般每对一条记录做一次改动，就对应着一条 undo日志，但在某些更新记录的操作中，也可能会对应着2条 undo日志。

在这里插入图片描述
每次对记录进行改动，都会记录一条 undo日志，每条 undo日志也都有一个 roll_pointer 属性（ INSERT 操作对应的 undo日志没有该属性，因为该记录并没有更早的版本），可以将这些 undo日志都连起来，串成一个链表。
版本链条

ReadView

对于使用 READ UNCOMMITTED 隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；对于使用 SERIALIZABLE 隔离级别的事务来说，设计 InnoDB 的大叔规定使用加锁的方式来访问记录；对于使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。为此，设计 InnoDB 的大叔提出了一个 ReadView 的概念，这个 ReadView 中主要包含4个比较重要的内容：

m_ids ：表示在生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表。
min_trx_id ：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务 id，也就是 m_ids 中的最
小值。
max_trx_id ：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。注意 max_trx_id 并不是 m_ids 中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三
个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。
creator_trx_id ：表示生成该 ReadView 的事务的事务id 。

注意：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务 id，否则在一个只读事务中的事务 id 值都默认为0。

有了这个 ReadView ，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 属性值大于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下
trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；
如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成ReadView的
时机不同。

READ COMMITTED：每次读取数据前都生成一个ReadView
REPEATABLE READ：在第一次读取数据时生成一个 ReadView

binlog

只记录了对数据库的 DDL 和 DML 操作的二进制日志。

主从同步原理

在这里插入图片描述

注意

XA(分布式事务)规范主要定义了(全局)事务管理器(TM: Transaction Manager)和(局部)资源管理器(RM: Resource Manager)之间的接口。XA为了实现分布式事务，将事务的提交分成了两个阶段：也就是2PC (tow phase commit)，XA协议就是通过将事务的提交分为两个阶段来实现分布式事务。在MySQL中binlog与InnoDB存储引擎之间的redo log也使用了内部XA事务两阶段提交保证一致性。

第一阶段：1. Server prepare > 不作任何操作；2. InnoDB prepare > write/sync redo log
第二阶段：1. Server commit > write/sync binlog；2. InnoDB commit > commit in memory

当第二阶段的第1步执行完成，binlog落盘即可被订阅者拉取到，这个时刻如果MySQL异常crash重启，判断binlog中是否包含XID(内部全局事务标识)来决定回滚或继续commit。走到第二阶段第2步时，事务最终一定会持久化，即使出现异常crash恢复后也会继续commit不回滚。只有完成最后一步事务产生的变更才能通过客户端查询出来。

简而言之就是，先写redo log，再写binlog，并以binlog写成功为事务提交成功的标志，crash恢复是以binlog中的XID和redo log中的XID进行比较，XID在binlog里存在则提交，不存在则回滚。

可以看出：由于binlog落盘后MySQL就会认为事务的持久化已经完成，但MySQL client需要等待事务产生的变更被全部commit完成后才能查询出来。所以订阅binlog变更后立即发起的回查操作是有一定的几率查不到该事务产生的任何变更。

Relay log

在这里插入图片描述

锁

前置知识

对于 MyISAM 、 MEMORY 、 MERGE 这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。比方说在 Session 1 中对一个表执行 SELECT 操作，就相当于为这个表加了一个表级别的 S锁，如果在 SELECT 操作未完成时， Session 2 中对这个表执行 UPDATE 操作，相当于要获取表的 X锁，此操作会被阻塞，直到 Session 1 中的 SELECT 操作完成，释放掉表级别的 S锁后， Session 2 中对这个表执行 UPDATE 操作才能继续获取 X锁，然后执行具体的更新语句。因为使用MyISAM、MEMORY、MERGE这些存储引擎的表在同一时刻只允许一个会话对表进行写操作，所以这些存储引擎实际上最好用在只读，或者大部分都是读操作，或者单用户的情景下。

InnoDB 存储引擎既支持表锁，也支持行锁。表锁实现简单，占用资源较少，不过粒度很粗，有时候你仅仅需要锁住几条记录，但使用表锁的话相当于为表中的所有记录都加锁，所以性能比较差。行锁粒度更细，可以实现更精准的并发控制。

原理

锁其实是一个内存中的结构，在事务执行前本来是没有锁的，也就是说一开始是没有锁结构和记录进行关联的。当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的锁结构，当没有的时候就会在内存中生成一个锁结构与之关联。
在这里插入图片描述

意向锁

IS（Intention Shared Lock）、IX（Intention eXclusive Lock）锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。

InnoDB 行锁

Record Lock

仅仅把一条记录锁上。

Gap Lock

gap锁的提出仅仅是为了防止插入幻影记录而提出的。给一条记录加了 gap锁只是不允许其他事务往这条记录前边的间隙插入新记录，那对于最后一条记录之后的间隙该咋办呢？这时候应该想起我们在前边唠叨数据页时介绍的两条伪记录：

Infimum 记录，表示该页面中最小的记录。
Supremum 记录，表示该页面中最大的记录。

Next-Key Lock

既能锁住某条记录，又能阻止其他事务在该记录前边的间隙插入新记录。

Insert Intention Locks

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了所谓的 gap锁，如果有的话，插入操作需要等待，直到拥有 gap锁的那个事务提交。但是设计 InnoDB 的大叔规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。
在这里插入图片描述

索引

在这里插入图片描述

索引设计原则

字段值有唯一性限制
经常作为查询/更新/删除的 where 的字段
经常作为 group by、order by 的字段
distinct 类型字段
多表连接查询
优先使用小类型字段
使用字符串前缀作为索引

通过截取字符串长度的区分度来选择前缀长度。一般对于字符串类型字段，20 字符长度就能达到 90%+ 的区分度。

注意：前缀索引会对排序结果产生较大的影响。
选择区分度（散列度）高的列
多个列都要创建索引的情况下，优先使用联合索引进行替代。
控制单表的索引数量
避免对经常更新的列创建索引

Explain

输出字段含义

在这里插入图片描述

id

在这里插入图片描述

扩展

explain 并不会实际执行 SQL，仅仅是评估 SQL 可能的执行方式，这就导致 rows（扫描行数）有时评估的不准确。explain analyze 则会实际执行 SQL，并将索引使用情况、各阶段耗时、扫描行数都打印出来。

原理

在这里插入图片描述

SQL 执行计划

可以通过查看生成的执行计划来确定 MySQL 底层的执行过程。

查看是否开启计划

show variables like 'profiling';

开启计划

set profiling=1;

查看执行计划

-- 列举了计划开启期间，执行过的 SQL
show profiles;
-- 查看最近一条 SQL 执行计划
show profile;
-- 查看指定 query id 的执行计划
show profile for query ${QUERY_ID}

在这里插入图片描述

函数

日期相关

在这里插入图片描述