MySQL框架
基本架构
连接器中的长连接累积
如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。
1.定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2.如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
查询缓存
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
MySQL也提供了这种“按需使用”的方式。可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样：
select SQL_CACHE * from T where ID=10；需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。
分析器
分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。
MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。
做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。
优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：
select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;- 既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
- 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。
执行器
开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误
select * from T where ID=10;示例语句执行顺序：
1.调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
MySQL有哪几种存储引擎
5.5版本前是默认MyISAM，之后是InnoDB
存储引擎 |
事务 |
MVCC |
崩溃恢复 |
热备份 |
压缩 |
空间数据 |
全文索引 |
锁级别 |
特点 |
InnoDB |
✅ |
✅ |
✅ |
✅ |
❌ |
❌ |
✅ |
行级 |
支持事务ACID、行级锁、外键约束、崩溃恢复 |
MyISAM |
❌ |
❌ |
❌ |
❌ |
✅ |
❌ |
✅ |
表级 |
查询速度快、支持压缩表、存储空间小 |
Memory |
❌ |
❌ |
❌ |
❌ |
❌ |
❌ |
✅ |
表级 |
数据存储在内存中、速度极快 |
Archive |
❌ |
❌ |
✅ |
✅ |
✅ |
❌ |
❌ |
行级 |
高压缩比、只支持INSERT和SELECT |
CSV |
❌ |
❌ |
❌ |
❌ |
❌ |
❌ |
❌ |
表级 |
以CSV格式存储数据 |
Blackhole |
❌ |
❌ |
✅ |
✅ |
❌ |
❌ |
❌ |
表级 |
接收数据但不存储 |
NDB/Cluster |
✅ |
✅ |
✅ |
✅ |
❌ |
❌ |
✅ |
行级 |
分布式存储、高可用性 |
B树和B+树区别
B树：
每个节点存储键值和数据-树更高
无叶子节点链表结构-范围支持不好
B+树：
数据只存储在叶子节点-树更矮
叶子节点通过双向链表相连-范围查询性能好
局部性原理
缓存下次大概率会查的数据
- 时间局部性：上次查了id=1的数据，下次大概率还会查id=1的
- 空间局部性：上次查了id=1的数据，下次大概率会查id=2，id=3的
B+树更适合取出相关范围的数据放入缓存
日志系统
MySQL整体有2块，Server层和引擎层。
redo log是InnoDB引擎特有的日志，
binlog（归档日志）是Server层的日志。
Redo log
当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。
write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
对比理解Binlog
- redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
- redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
- redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
示例更新语句流程
update T set c=c+1 where ID=2;
为什么要两阶段提交
如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
1.先写redo log后写binlog。
假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。
但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。
然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
2.先写binlog后写redo log。
如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。
重要性
当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用binlog来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。
为什么要2个日志
为什么不能只用 Redo Log
1.主从复制
Redo Log 是物理日志，不可读性差，不适合用于主从复制或审计。Binlog 是逻辑日志，适合用于复制和恢复，并且可以保留多个文件。
2.长期保留
Redo Log 是循环写，旧的日志会被覆盖，无法长期保留。
为什么不能只用 BinLog
1.事务性
Redo Log 是在事务执行过程中就写入的，即使事务未提交，也能在崩溃恢复时重放 Redo Log 来恢复数据页的状态。Binlog 记录的是 SQL 或行级别的变更，事务提交时才写入。
2.恢复效率
Redo Log 是物理日志，效率更高。Binlog 是逻辑日志，恢复时需要重新执行 SQL 或行操作，效率较低。
redolog写入数据库的场景
redolog满了
InnoDB的redo log写满了。这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
内存不足
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
如此可保证：
- 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
- 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。
MySQL认为系统“空闲”
MySQL正常关闭
影响性能的2种情况
1.一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
2.日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。
InnoDB刷脏页的控制策略
刷脏页速度控制逻辑
- innodb_io_capacity -> 磁盘能力
- innodb_max_dirty_pages_pct -> 脏页比例上限，默认值是75%
计算公式如下：
// 当前的脏页比例为M
F1(M)
{
if M>=innodb_max_dirty_pages_pct then
return 100;
return 100*M/innodb_max_dirty_pages_pct;
}
- InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，设为N，算法为F2(N)
根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。
连坐机制
对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。
innodb_flush_neighbors
- 为0，则只刷自己（MySQL 8.0默认）
- 为1，启动连坐机制
分库分表
分库分表下如何分页
首先，只有在跨库跨表下才需要解决，
- 利用ES的分布式分页能力
- ShardingSphere提供了一些内置的分页优化:自动改写SQL+全局查找法+流式处理(排序，过滤)全局查询法：limit 0，每页大小 + offset，（如每页10条数据，查第3页，limit 0，10+20）
禁止跳页查询法：where id > 上一页maxId order by id limit 每页大小
-
- 为啥从0开始，因为每个分表的查询是局部的，不能直接跳过全局的
offset，where条件过滤时可能第一条就是所需数据 - 解决全局查询法在深页查询时效率低的问题
- 为啥从0开始，因为每个分表的查询是局部的，不能直接跳过全局的
如何实现分库分表
将原本存储于单个数据库上的数据拆分到多个数据库，把原来存储在单张数据表的数据拆分到多张数据表中，实现数据切分，从而提升数据库操作性能。分库分表的实现可以分为两种方式:垂直切分和水平切分。
水平:将数据分散到多张表，涉及分区键
- 分库:每个库结构一样，数据不一样，没有交集。库多了可以缓解io和cpu压力
- 分表:每个表结构一样，数据不一样，没有交集。表数量减少可以提高sql执行效率、减轻cpu压力
垂直:将字段拆分为多张表，需要一定的重构
- 分库:每个库结构、数据都不一样，所有库的并集为全量数据
- 分表:每个表结构、数据不一样，至少有一列交集，用于关联数据，所有表的并集为全量数据
单表数据量多少要分表
计算3层B+树可以存多少行数据，假设一行数据大小100字节
大约可存储4亿2千万行，若每行1KB，也可存大约4千万行
实际场景可能还要考虑磁盘性能、备份风险，建议单表1000万行数据，如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
索引使用
索引优缺点
优：
- 索引能够提高数据检索的效率，降低数据库的IO成本通过创建唯一性索引
- 可以保证数据库表中每一行数据的唯一性，创建唯一索引
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
- 加速两个表之间的连接，一般是在外键上创建索引
缺：
- 需要占用物理空间,建立的索引越多需要的空间越大
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长
什么时候用索引
- 对于查询中使用的多的字段
- 对于数据密度大的列，因为InnoDB 中索引由 B+ 树所决定的。(但是也有例外，如枚举值(1，2，3)，头两个占比百分之1%，第三个占比99%，并且头两个搜索占比比第三个高很多，那么是可以建议加索引的)。
InnoDB和MyISAM索引区别
InnoDB有聚簇和非聚簇索引，聚簇索引的叶子节点存所有信息，非聚簇索引的叶子节点存索引和主键ID
MyISAM只有一种索引，叶子节点存信息行的地址，且MyISAM无事务、MVCC机制，可能更快
索引下推
（Index Condition Pushdown, ICP）是 MySQL 5.6 引入的一项优化技术，它允许存储引擎在存储引擎层（而不是服务器层）提前对索引中包含的字段进行条件过滤，从而减少回表次数和服务器层需要处理的数据量。如
INDEX idx_name_age_city (name, age, city) --联合索引
SELECT * FROM users
WHERE name LIKE 'J%'
AND age = 25
AND city = 'New York';
---使用 name LIKE 'J%' 在索引中查找所有以J开头的name
--在存储引擎层立即检查 age = 25 AND city = 'New York' 条件
--只返回同时满足所有索引条件的记录的id给服务器层
--对这些少量记录回表查询完整数据
范围查询为什么能走索引
如where id < 100，先查id=100的，再通过双向链表往前查
索引排序内部流程
首先 mysql 会为每一个线程都分配一个固定大小的 sort buffer 用于排序。它是一个具有逻辑概念的内存区域，我们可以通过 sort buffer_size 参数来控制，默认值是 256kb。
- 当待排序的数据量小于等于 sort buffer 时，那我们的 sort buffer 就能够容纳，MySQL 就可以直接在内存里面排序就行了，内部排序使用的排序算法是快排
- 当待排序的数据量大于 sort buffer 时，这个时候MySQL就得要借助外部文件来进行排序了。将待排序数据拆成多个小文件，对各个小文件进行排序最后再汇总成一个有序的文件，外部排序使用的算法是归并排序
row_id排序：
max_length_for_sort_data ，在MySQL 中专门控制用户排序的行数据长度参数。默认是4096，如果超过了这个长度 MySQL就会自动升级成row_id 算法。
在 sort buffer 里面进行排序的数据是select 的全部字段，所以当查询的字段越多，那么 sort buffer 能容纳的数据量也就越小。而通过 row_id 排序就只会存放row_id 字段和排序相关的字段。其余的字段等排序完成之后通过主键ID 进行回表拿。
有索引时的查询过程
普通索引：
查到第一个数据后，会往后继续查，直到不满足条件
唯一索引：
查到第一个数据后返回
有索引时的更新过程
概览
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。
细究
记录要更新的目标页不在内存中时：
- 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
- 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。
综上唯一索引的更新不用change buffer，普通索引才用
所以更新操作多的建议用普通索引
change buffer使用场景
效果优：
数据在写完以后马上被访问到的概率比较小
效果劣：
数据写入之后马上会做查询
对比redo log
就像有一本字典，有一群人要在不同页上改字：
- redo log = “大家把要改的内容临时写在一个顺序的小本子上” -> 只做顺序写、不需要立刻去字典的不同页乱翻（节省随机写）。
- change buffer = “把要改的内容记在便利贴上贴住，等有人用到那页时一起改” -> 暂时不用去字典的那一页上查，等有人翻到再补改（节省随机读）。
给字符串加索引
前缀索引
可以定义字符串的一部分作为索引
alter table SUser add index index2(email(6));但是，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是在选择是否使用前缀索引时需要考虑的一个因素。
倒序存储
如我国身份证，同一地市的人前几位相同，适合用后几位来做索引，如果用倒序存储，查询时如下
mysql> select field_list from t where id_card = reverse('input_id_card_string');可使用count(distinct)方法去做个验证
使用hash字段
在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以查询语句where部分要判断id_card的值是否精确相同。
// 创建索引
alter table t add id_card_crc int unsigned, add index(id_card_crc);
// 查询时要精确匹配
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'索引失效
优化器为什么会选错
索引基数
InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
示例分析
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
delete 语句删掉了所有的数据，然后再通过call idata()插入了10万行数据，看上去是覆盖了原来的10万行。
但是，session A开启了事务并没有提交，所以之前插入的10万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是delete之前的数据，新版本是标记为deleted的数据。
这样，索引a上的数据其实就有两份。
但是，主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是show table status的值。
所以优化器错误选择索引为主键索引
解决办法
对于由于索引统计信息不准确导致的问题，
- 可以用analyze table来解决
对于其他优化器误判的情况，
- 可以在应用端用force index来强行指定索引
- 也可以通过修改语句来引导优化器
- 还可以通过增加或者删除索引来绕过这个问题
最左前缀原则
对于联合索引(col1, col2, col3,...)，MySQL 会从左到右依次使用索引中的列，直到遇到第一个不能使用索引的查询条件为止。
例如where col1=1 and col3=1，在B+树的节点中比较完col1后无法比较col2大小，导致在非叶子节点中后面的索引失效（叶子节点除外）
范围查询失效
假设表中每行数据的b>1，此时sql中的where b > 1，如果走索引会先查到所有的主键，再回表查所有数据，比不走索引直接查全表数据还要慢
优化器会在执行sql判断需不需要走索引，如果全表扫描还要快，那么索引失效
order by失效
同理，如果order by a，b，c需要回表，优化器判断全表扫描还要快，那么索引失效
is null is not null ≠可能会导致索引失效
优化器会得到一个需要扫描的二级索引记录条数，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。
在行数据中是如何存储 NULL值的?
表中的某些列可能会存储 NULL值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以一般行格式把这些值为 NULL 的列存储到 NULL 值 列表中。如果存在允许 NULL 值的列，则每个列对应一个二进制位(bit)，二进制位按照列的顺序逆序排列。
二进制位的值为 1 时，代表该列的值为 NULL。二进制位的值为 0时，代表该列的值不为NULL。另外，NULL值列表必须用整数个字节的位表示(1字节8位)， 如果使用的二进制位个数不足整数个字节，则在字节的高位补8
索引是如何存储 NULL 值的?
值为 NULL 的二级索引记录都被放在了B+树的最左边，
对字段进行操作导致失效
数据类型转换：
字符与数字比较时，字符会转成数字，非数字字符转成0，数字字符转成数字，‘012’转成12，‘a12’转成0
select a=‘0’ --结果是1，表示相等
select ‘123’=123 --结果是1
若表头某字段type有值为0，where type=‘a’，可以查到type为0的那行数据
若表头某字段type为字符类型，where type=0，不仅索引里的字符要转换，可能还会破坏顺序要重建，如字符里‘1’<b，转换后1>0，所以优化器判断不走索引
在 where 条件里面对索引列使用运算或者使用函数。
在 where 条件中两列做比较会导致索引失效
使用 OR 且存在非索引列
LIKE以%开头可能导致索引失效（若不需要回表除外）
SQL优化
查看sql执行情况
使用explain命令查看语句执行情况
explain select * from t where a between 10000 and 20000;慢sql
介绍
慢查询是指执行时间超过预设阈值的SQL语句。数据库系统会将这些执行时间过长的查询记录下来，便于后续分析和优化。
开启慢sql日志
- slow_query_log: 是否开启慢查询日志（ON/OFF）
- long_query_time: 慢查询阈值（秒）
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
set long_query_time=0;-- 设置0秒为阈值查询方法
# 按查询时间排序，显示前20条
mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.logMysql慢查询该如何优化?
- 检查是否走了索引，如果没有则优化SQL利用索引
- 检查所利用的索引，是否是最优索引
- 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
- 检查表中数据是否过多，是否应该进行分库分表了
- 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源
explain语句结果中各字段含义
字段名 |
描述 |
优化关注点 |
|
查询的序列号，表示子查询或表操作的执行顺序（相同 |
关注子查询或派生表的执行顺序，避免不必要的复杂查询（如多层嵌套子查询）。 |
|
查询类型（如 |
检查是否存在 |
|
当前访问的表名或别名（如 |
确认是否访问了正确的表，避免全表扫描或错误表关联。 |
|
匹配的分区信息（如果表是分区表）。 |
如果表是分区表，检查是否只访问了必要的分区，避免全分区扫描。 |
|
访问类型（从优到差）： |
核心优化指标：避免 |
|
查询可能使用的索引（基于查询条件和表结构）。 |
检查是否遗漏了可用的索引，确保查询条件能利用索引。 |
|
实际使用的索引。 |
如果为 ``，表示未使用索引，需优化查询或索引设计。 |
|
使用索引的字节长度。 |
判断联合索引是否被充分利用（如是否只用了部分列）。 |
|
显示与索引比较的列或常量（如 |
检查是否使用了高效的等值匹配（如 |
|
预估需要扫描的行数。 |
核心指标：数值越小越好，过大可能需优化索引或查询条件。 |
|
存储引擎返回的数据在 Server 层过滤后，剩余数据占全部数据的百分比（100% 表示无过滤）。 |
如果值较低（如 10%），说明 Server 层过滤了大量数据，可能需要优化查询条件或索引。 |
|
额外信息（如 |
重点关注： - 避免 |
事务
隔离
隔离级别
SQL标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。
- 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
- 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
- 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
- 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
隔离实现
如图中看到的，在视图A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。
系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。
查询长事务
下面这个语句，用于查找持续时间超过60s的事务。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60事务的启动方式
1.显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。
2.set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。
Innodb是如何实现事务的
Innodb通过Buffer Pool，LogBuffer，RedoLog，Undo Log来实现事务，以一个update语句为例:
- Innodb在收到一个update语句后，会先根据条件找到数据所在的页并将该页缓存在Buffer Pool中
- 执行update语句，修改Buffer Pool中的数据，也就是内存中的数据
- 针对update语句生成一个RedoLog对象，并存入LoqBuffer中
- 针对update语句生成undoloq日志，用于事务回滚
- 如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Bufer Pool中所修改的数据页持久化到磁盘中
- 如果事务回滚，则利用undolog日志进行回滚
MVCC
通过保留数据的历史版本，使得读操作无需加锁即可获得一致性视图。
数据版本链与回滚日志
每当一行数据被更新，InnoDB 不仅记录新值，还会生成一条回滚日志（undo log），并将旧版本链接起来形成一个版本链。每个版本都包含一个 row trx_id，表示生成该版本的事务 ID。
一致性读视图
当事务启动时，InnoDB 会为其分配一个唯一的事务 ID（transaction id），并根据当前活跃事务列表构建一个“一致性读视图”。这个视图决定了该事务能看到哪些数据版本。
判断规则如下：
- 如果某版本的 row trx_id 在视图创建时已经提交，则可见；
- 如果 row trx_id 是当前事务自己产生的，则可见；
- 如果 row trx_id 在视图中仍处于活跃状态，则不可见；
读提交
每个语句执行前创建新快照
只能读取已提交的数据
避免脏读，但允许不可重复读
可重复读
事务开始时创建快照，整个事务期间使用同一快照
保证在同一事务中多次读取同一数据结果一致
避免脏读和不可重复读
快照读
普通 SELECT 语句属于快照读，依赖 MVCC 提供一致性视图，不加锁，性能高。
SELECT * FROM t WHERE id = 1;在可重复读下，这类查询始终使用事务启动时的视图。
当前读
带有锁的读操作（如 SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE）或更新操作（UPDATE、DELETE）属于当前读。它们必须读取最新已提交的数据版本，并加锁以防止并发修改。
SELECT * FROM t WHERE id = 1 FOR UPDATE;当前读会绕过 MVCC 的快照机制，直接读取最新的提交版本，并参与行锁竞争。
锁
锁的类型有哪些
锁层级结构图
锁机制总览
├── 按粒度分类 (Granularity)
│ │
│ ├── 表级锁 (Table-level Locks)
│ │ ├── 意向锁 (Intention Locks) ← 按状态分类
│ │ │ ├── 意向共享锁 (IS)
│ │ │ └── 意向排他锁 (IX)
│ │ ├── 共享锁 (S)
│ │ └── 排他锁 (X)
│ │
│ ├── 页级锁 (Page-level Locks)
│ │ └── 页锁 (Page Lock)
│ │
│ └── 行级锁 (Row-level Locks)
│ ├── 记录锁 (Record Lock)
│ ├── 间隙锁 (Gap Lock)
│ └── 临键锁 (Next-Key Lock)
│ └── 共享/排他变体
│ ├── 共享记录锁 (S Record Lock)
│ ├── 排他记录锁 (X Record Lock)
│ ├── 共享间隙锁 (S Gap Lock)
│ └── 排他间隙锁 (X Gap Lock)
│锁的属性分类
锁类型 |
英文名称 |
描述 |
特点 |
兼容性 |
共享锁 |
Shared Lock (S) |
允许多个事务同时读取同一资源 |
读锁、共享性 |
S锁与S锁兼容，与X锁不兼容 |
排他锁 |
Exclusive Lock (X) |
阻止其他事务读取或修改资源 |
写锁、独占性 |
X锁与任何锁都不兼容 |
锁的状态分类
锁类型 |
英文名称 |
描述 |
作用 |
触发条件 |
意向共享锁 |
IS (Intent Shared) |
表级意向锁，表示事务想要获得共享锁 |
提高锁检测效率 |
执行SELECT...LOCK IN SHARE MODE |
意向排它锁 |
IX (Intent Exclusive) |
表级意向锁，表示事务想要获得排它锁 |
提高锁检测效率 |
执行SELECT...FOR UPDATE |
锁的粒度分类
锁类型 |
英文名称 |
粒度 |
存储引擎 |
描述 |
优点 |
缺点 |
行级锁 |
Row Lock |
行级别 |
InnoDB |
锁定表中的特定行 |
并发度高、冲突少 |
开销大、可能出现死锁 |
表级锁 |
Table Lock |
表级别 |
InnoDB、MyISAM |
锁定整个表 |
开销小、获取快 |
并发度低、冲突多 |
页级锁 |
Page Lock |
页级别 |
BDB引擎 |
锁定数据页 |
平衡行锁和表锁 |
并发度一般 |
记录锁 |
Record Lock |
索引记录（行级别） |
InnoDB |
锁定索引记录 |
防止其他事务更新 |
需要索引支持 |
间隙锁 |
Gap Lock |
索引间隙 |
InnoDB |
锁定索引记录间的间隙 |
防止其他事务插入 |
可能影响并发 |
临键锁 |
Next-Key Lock |
记录+间隙 |
InnoDB |
记录锁+间隙锁组合 |
解决幻读问题 |
锁定范围较大 |
锁的兼容性矩阵
当前持有\请求锁 |
IS |
IX |
S |
X |
IS |
✅ |
✅ |
✅ |
❌ |
IX |
✅ |
✅ |
❌ |
❌ |
S |
✅ |
❌ |
✅ |
❌ |
X |
❌ |
❌ |
❌ |
❌ |
意向锁为行锁"开路"，共享锁定义访问权限
表级锁和行级锁是分开判定的
示例：
-- 事务A执行（读操作）：
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 获得：表级IS锁 + 行级S锁(id=1)
-- 事务B执行（写操作）：
UPDATE users SET name = 'new' WHERE id = 1;
-- 需要：表级IX锁 + 行级X锁(id=1)
-- 结果：事务B被阻塞，因为：
-- 1. IX与IS兼容 ✅（表级不冲突）
-- 2. X与S不兼容 ❌（行级冲突，id=1行被锁定）
-- 事务B条件改为id=2则不冲突死锁
定义
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
策略
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
优化策略
将一行改成逻辑上的多行来减少锁冲突。以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。
场景题
哪些索引可以去掉
实际上主键索引也是可以使用多个字段的。DBA小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：
CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;
公司的同事告诉他说，由于历史原因，这个表需要a、b做联合主键，这个小吕理解了。
但是，学过本章内容的小吕又纳闷了，既然主键包含了a、b这两个字段，那意味着单独在字段c上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？
同事告诉他，是因为他们的业务里面有这样的两种语句：
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;我给你的问题是，这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？
答案：
主键 a，b的聚簇索引组织顺序相当于 order by a,b ，也就是先按a排序，再按b排序，c无序。所以，结论是ca可以去掉，cb需要保留。
字符串加索引
如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号@gmail.com", 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。
系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？
答案：
可以用数字类型来存这9位数字。比如201100001，这样只需要占4个字节。