MySQL面试题

Kuoji

已于 2022-04-13 18:40:56 修改

阅读量697

点赞数

分类专栏： Java面试文章标签：面试

于 2020-07-23 19:12:05 首次发布

本文链接：https://blog.csdn.net/weixin_44048324/article/details/107545312

版权

Java面试专栏收录该内容

14 篇文章 0 订阅

订阅专栏

基础

mysql执行计划

连接器
分析器: 词法分析，分析输入的SQL语句里面的字符串分别代表什么。语法分析，根据语法规则判断输入SQL语句是否满足MySQL语法
优化器: 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
执行器: 开始执行的时候，要先判断一下对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

sql里面的执行顺序

sql注入

将sql代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法
在一些对server端发起的请求参数中植入一些sql代码，server端在执行sql操作时，会拼接对应参数，同时也将一些sql注入攻击的 sql 拼接起来，导致会执行一些预期之外的操作

select * from table_name where username='' or 1=1
// 反转就是where后面的条件使用or连接的是一个恒成立的条件。

char 和 varchar的区别

MySQL规定：一行数据的最大长度是65535 字节(byte)，text、blob等大字段类型除外

占用空间和使用字符集相关 :

gbk（每字符占用 2 字节）
utf8（每字符占用 3 字节）
utf8mb4（每字符占用 4 字节）

char 和 varchar

char是一种固定长度的类型，varchar则是一种可变长度的类型
char(n) 类型，占用 n 个字节，如果长度小于 n，MySQL就会在它的右边用空格字符补足
varchar(n) 类型，占用 实际长度 的字节，还需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节（是定义的最长长度，不是实际长度），则使用1个字节表示长度，否则使用2个字节来表示
适用场景：char 存储的数据长度基本一致，不需要空格，eg 手机号、UUID、密码加密后的密文
适用场景：varchar : 数据长度不一定，长度范围变化较大的场景
查询速度： char > varchar > text

三大范式

第一范式：保证每列的原子性，表中所有字段值都是不可分解的
第二范式：满足第一范式的前提下，保证表中的每列都和主键相关即一个表中只能保存一种数据，不可以把多种数据存在同一张表中
第三范式：确保每列都和主键直接相关，而不是间接相关

连接

inner join： 找出左右表都可匹配的记录
left join： 以左表为准，逐条去右表找可匹配字段，如果没有找到则为 NULL
right join: 以右表为准，逐条去左表找可匹配字段，如果没有找到则为 NULL
full outer join： 包含两个表的连接结果，如果左表缺失或者右表缺失的数据会填充NULL

left join 条件在where后面
只有满足条件的记录会返回

InnoDB建表可以不要主键吗？推荐使用整型的自增主键？

InnoDB采用B+树作为存储结构，那么必然需要一个列作为 key
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个 唯一的非空索引 代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引

一个B+树的节点可以存储key、地址、行数据(仅叶子节点)，key 就是不重复的值且可以比较(确保树进行分裂时，可以确定是左孩子还是右孩子)
主键的特定就是不可重复，也不可为空，正好符合B+树key的要求

为什么推荐使用整型的自增主键？

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的
如果主键不是自增id，不断地调整数据的物理地址、分页，也有其他措施来减少这些操作，但却无法彻底避免
如果是自增的，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高

datetime 和 timestamp

存储空间：datetime 占用 8个字节，timestamp 占用 4个字节
时区： datetime 不受时区影响，timestamp实际记录的是 1970-01-01 00：00:01到现在的数，受时区影响
时间范围： (1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59 ) (1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC)

Where和Having的区别

三者的执行顺序为 where > group by > having
Where是一个 约束声明，约束来自数据库的数据，在结果返回之前起作用，不能使用聚合函数
Having是一个 过滤声明，是在查询返回结果以后对查询结果进行的过滤操作，可以使用聚合函数

count

count(*) 的实现方式

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高
InnoDB 引擎执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的

引统计的值是通过采样来估算的。实际上，TABLE_ROWS 就是从这个采样估算得来的，因此它也很不准。有多不准呢，官方文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能直接使用

MyISAM 表虽然 count(*) 很快，但是不支持事务；
show table status 命令虽然返回很快，但是不准确；
InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题

不同的count用法

count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数

count(字段），则表示返回满足条件的数据行里面，参数 “字段” 不为 NULL 的总个数

count(主键 id)

InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加

count(1)

InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加

count(字段)

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加

count(*)

并不会把全部字段取出来，而是专门做了优化，MySQL 专门针对这个语句进行优化

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以我建议你，尽量使用 count()

视图

什么是视图

视图（view）是一种虚拟存在的逻辑表，本身并不包含数据，作为一个select语句保存在数据字典中的
通过视图，可以展现基表的部分数据，视图数据来自定义视图的查询中使用的表，使用视图动态生成
- 用来创建视图的表叫做基表 base table

为什么使用视图

为了保障数据安全性，提高查询效率
简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

OR REPLACE：表示替换已有视图
ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法；merge合并；temptable临时表
select_statement：表示select语句
[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内
- cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件
- local表示更新视图的时候，要满足该视图定义的一个条件即可
- 推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项，可以保证数据的安全性

-- 在单表上创建视图
mysql> create view v_F_players(编号,名字,性别,电话)
    -> as
    -> select PLAYERNO,NAME,SEX,PHONENO from PLAYERS
    -> where SEX='F'
    -> with check option;
Query OK, 0 rows affected (0.00 sec)

mysql> desc v_F_players;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 编号    | int(11)  | NO   |     | NULL    |       |
| 名字    | char(15) | NO   |     | NULL    |       |
| 性别    | char(1)  | NO   |     | NULL    |       |
| 电话    | char(13) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from  v_F_players;
+--------+-----------+--------+------------+
| 编号    | 名字      | 性别    | 电话        |
+--------+-----------+--------+------------+
|      8 | Newcastle | F      | 070-458458 |
|     27 | Collins   | F      | 079-234857 |
|     28 | Collins   | F      | 010-659599 |
|    104 | Moorman   | F      | 079-987571 |
|    112 | Bailey    | F      | 010-548745 |
+--------+-----------+--------+------------+
5 rows in set (0.02 sec)


--在多表上创建视图
mysql> create view v_match
    -> as 
    -> select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
    -> from 
    -> PLAYERS a,MATCHES b,TEAMS c
    -> where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_match;
+----------+-----------+---------+-----+------+--------+----------+
| PLAYERNO | NAME      | MATCHNO | WON | LOST | TEAMNO | DIVISION |
+----------+-----------+---------+-----+------+--------+----------+
|        6 | Parmenter |       1 |   3 |    1 |      1 | first    |
|       44 | Baker     |       4 |   3 |    2 |      1 | first    |
|       83 | Hope      |       5 |   0 |    3 |      1 | first    |
|      112 | Bailey    |      12 |   1 |    3 |      2 | second   |
|        8 | Newcastle |      13 |   0 |    3 |      2 | second   |
+----------+-----------+---------+-----+------+--------+----------+
5 rows in set (0.04 sec)

存储引擎

InnoDB引擎

InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据
InnoDB 的行级别锁定保证数据一致性提升了它的多用户并发数以及性能
InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销
为了保证数据的完整性，InnoDB 还支持外键约束。默认使用B+TREE数据结构存储索引

特点

支持事务，支持4个事务隔离（ACID）级别
行级锁定（更新时锁定当前行）
读写阻塞与事务隔离级别相关
既能缓存索引又能缓存数据，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
支持外键
InnoDB更消耗资源，读取速度没有MyISAM快
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上

MyISAM引擎

MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。默认使用B+TREE数据结构存储索引

特点

不支持事物，不支持外键
表级锁定（更新时锁定整个表）
读写互相阻塞（写入时阻塞读入、读时阻塞写入；但是读不会互相阻塞）
只会缓存索引（通过key_buffer_size缓存索引，但是不会缓存数据）
读取速度快

Memory引擎

在内存中创建表，每个MEMORY表只实际对应一个磁盘文件(frm 表结构文件)，MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引
在用完表格之后就删除表格，不然一直占据内存空间

特点

默认使用hash索引
支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈
支持的数据类型有限制，比如：不支持TEXT和BLOB类型（长度不固定），对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型
查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低
由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失，如果一个内部表很大，会转化为磁盘表

业务场景

内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果
目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小
数据是临时的，而且必须立即可用得到，那么就可以放在内存中
存储在Memory表中的数据如果突然间丢失的话也没有太大的关系

InnoDB和MyISAM的区别

索引存储方式不一样，InnoDB 的表是根据主键进行展开的 B+tree 的聚集索引。 MyISAM则非聚集型索引，MyISAM存储会有两个文件，一个是索引文件，另外一个是数据文件，其中索引文件中的索引指向数据文件中的表数据
InnoDB支持 事物，外键，MVCC，MyISAM都不支持
InnoDB支持表、行级锁，而MyISAM支持表级锁
InnoDB表必须有主键，而MyISAM可以没有主键
select count(*) from table时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描
Innodb表需要更多的内存和存储，而MyISAM可被压缩，存储空间较小
Innodb按主键大小有序插入，MyISAM记录插入顺序是按记录插入顺序保存
InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引

应用场景

InnoDB

支持事物，可靠性要求比较高
表更新和查询都相当的频繁，并且行锁定的机会比较大的情况

MyISAM

没有事物
做很多count的计算
插入不频繁，查询非常频繁

为什么MyISAM会比Innodb 的查询速度快

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多

数据块，InnoDB要缓存，MyISAM只缓存索引块，这中间还有换进换出的减少
INNODB还需要维护MVCC
INNODB寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快

日志系统

undo log

InnoDB实现事物回滚，靠的是undo log：

当事务对数据库进行操作时，InnoDB 会生成对应的 undo log。如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前的样子
undo log 属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据 undo log 的内容做与之前相反的工作：对于每个 insert，回滚时会执行 delete；对于每个 delete，回滚时会执行 insert；对于每个update，回滚时会执行一个相反的 update，把数据改回去
以update操作为例 ：当事务执行 update 时，其生成的 undo log 中会包含 被修改行的主键、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态

redo log

原因

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高

MySQL 中 WAL技术(Write-Ahead Logging)，先写日志，再写磁盘
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做
如果redo log满了，则会将redo log中部分记录同步到数据库，然后将这些记录从redo log中删除

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写
write pos 是当前记录的位置，一边写一边后移，写到第 n 号文件末尾后就回到 0 号文件开头
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
write pos 和 checkpoint 之间的是redo log上 还空着 的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先同步一些记录，把 checkpoint 推进一下

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe

脏页

redolog更新到DB中的情况

第一种场景是， InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。把checkpoint 位置从 CP 推进到 CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都 flush 到磁盘上。 write pos 到 CP’之间就是可以再写入的 redo log 的区域
第二种场景是，系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是 “脏页” ，就要先将脏页写到磁盘
- 难道不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿 redo log 出来应用不就行了？
- 从性能考虑的。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：
- 一种是内存里存在，内存里就肯定是正确的结果，直接返回
- 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。
  这样的效率最高
第三种场景是， MySQL 认为系统“空闲”的时候。只要有机会就刷一点“脏页”
第四种场景是， MySQL 正常关闭的情况。MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快

分析不同场景对性能的影响

1.redo log写满，需要flush脏页

这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0

2.内存不够，将脏页写到磁盘

InnoDB 用 **缓冲池（buffer pool）**管理内存，缓冲池中的内存页有三种状态：
- 1.还没有使用的
- 2.使用了并且是干净页
- 3.使用了并且是脏页
InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少
而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用

刷脏页影响性能的情况

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长
日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的

InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况

InnoDB 刷脏页的控制策略

innodb_io_capacity 该参数会告诉 InnoDB 你的磁盘能力。这个值建议设置成磁盘的 IOPS

#磁盘的 IOPS 可以通过 fio 这个工具来测试
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

刷脏页太慢会出现内存脏页太多，其次是 redo log 写满

InnoDB 的刷盘速度就是要参考这两个因素：脏页比例， redo log 写盘速度

参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字

#计算这个数字的伪代码类似这样
F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值

假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字，这个计算公式可以记为 F2(N)

F2(N) 算法比较复杂，你只要知道 N 越大，算出来的值越大就好了

InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。无论是查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因

bin log

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

(浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的)

执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务
执行器生成这个操作的 binlog，并把 binlog 写入磁盘
执行器调用引擎的提交事务接口，引擎把刚刚写入的 **redo log 改成提交（commit）**状态，更新完成

两阶段提交

两阶段提交的目的：为了让两份日志之间的逻辑一致

如果不使用两阶段提交，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致

怎么让数据库恢复到半个月内任意一秒的状态？

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库
然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻

为什么日志需要两阶段提交

redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序

update 语句来做例子。假设当前 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，与原库的值不同

日志相关问题

在两阶段提交的不同时刻，MySQL异常重启

写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃

binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚
binlog 还没写，所以也不会传到备库

binlog 写完，redo log 还没 commit 前发生 crash

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整
- 如果是，则提交事务
- 否则，回滚事务

MySQL 怎么知道 binlog 是完整的

一个事务的 binlog 是有完整格式的：

statement 格式的 binlog，最后会有 COMMIT
row 格式的 binlog，最后会有一个 XID event
binlog-checksum 参数，用来验证 binlog 内容的正确性

redo log 和 binlog 是怎么关联起来的

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log
如果碰到既有 prepare、又有 commit 的 redo log，就直接提交
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务

参数

redo log

innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失

sync_binlog

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失

事物的特性

ACID

原子性

事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态

一致性

事务执行前后数据的 完整性 必须保持一致
假如A账户给B账户转账，不管成功与否，A和B的总金额是不变的

隔离性

多个用户并发访问数据库时，一个用户的事物不会被其他用户的事物干扰。多个并发事物之间数据要相互隔离

持久性

一个事物一旦被提交，它对数据库中数据的改变是永久的

ACID的保证

原子性

InnoDB实现事物回滚，靠的是undo log：

当事务对数据库进行操作时，InnoDB 会生成对应的 undo log。如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前的样子
undo log 属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据 undo log 的内容做与之前相反的工作：对于每个 insert，回滚时会执行 delete；对于每个 delete，回滚时会执行 insert；对于每个update，回滚时会执行一个相反的 update，把数据改回去
以update操作为例 ：当事务执行 update 时，其生成的 undo log 中会包含 被修改行的主键、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态

一致性

一般由代码层面来保证

隔离性

由MVCC来保证

持久性

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，如果每次读写数据都需要 磁盘IO，效率会很低。InnoDB 提供了 缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool。当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证

当数据修改时，除了修改 Buffer Pool 中的数据，还会在 redo log 记录这次操作。当事务提交时，会调用 fsync 接口对 redo log 进行刷盘
如果MySQL 宕机，重启时可以读取 redo log 中的数据，对数据库进行恢复。redo log采用的是 WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到 Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：
（1）刷脏是随机 IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO
（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少

隔离性引发的并发问题

脏读：B事物读取到了A事物 尚未提交的数据
不可重复读 ： B事物读到了A事物 **已经提交 **的数据，即 B事物在 A事物 提交之前和提交之后 读取到的数据内容不一致 (AB事物操作的是同一条数据)
幻读 / 虚读 ：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

RR级别下，普通的查询是 快照读，是不会看到别人事物插入的数据。幻读在 当前读 下才会出现

事物的隔离级别

（由上至下隔离级别递增）默认隔离级别为可重复读
在这里插入图片描述

读未提交 (read uncommitted) ：一个事务还没提交时，它做的变更就能被别的事务看到
读已提交 (read committed)：一个事务提交之后，它做的变更才会被其他事务看到
可重复读 (repeatable read) ：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
串行化 (serializable) ：对于同一行数据，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事物必须等前一个事物执行完成，才能继续执行

serializable级别可以解决所有的数据库并发问题，会在读取的每一行数据上都加锁，这就可能导致大量的超时和锁竞争问题，从而导致效率下降

在实际应用中也很少使用serializable，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别

实现

数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准
读未提交 隔离级别下，直接返回记录上的最新值，没有视图概念
读以提交 隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的
可重复读 隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图
串行化 隔离级别下，直接用加锁的方式来避免并行访问

可重复读场景

个人银行账户表，一个表存了每个月月底的余额，一个表存了账单明细。要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。

事务的启动方式

显式启动事务语句， begin 或 start transaction。提交语句是 commit，回滚语句是 rollback
set autocommit=0，这个命令会将这个线程的自动提交关掉
- 即只执行一个 select 语句，这个事务就启动了，而且并不会自动提交
- 这个事务持续存在直到主动执行 commit 或 rollback 语句，或者断开连接

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务

建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务

对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 begin，减少了语句的交互次数

建议使用 commit work and chain 语法

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务
如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是 从程序开发的角度明确地知道每个语句是否处于事务中

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

事物的隔离

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pdvQm7rn-1649846429873)(D:\TyporaImage\MySQL\image-20210923134444447.png)]

begin / start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动

马上启动一个事务，使用 start transaction with consistent snapshot 这个命令

第一种启动方式，一致性视图是在第执行第一个快照读语句时创建的
第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的

事务 C 没有显式地使用 begin/commit，表示 update 语句本身就是一个事务，语句完成的时候会自动提交

事务 B 在更新了行之后查询 ; 事务 A 在一个只读事务中查询，并且时间顺序上是在事务 B 的查询之后

事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1

MySQL 里，有两个“视图”的概念：

一个是 view。用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样
另一个是 InnoDB 在实现 **MVCC 时用到的一致性读视图(**consistent read view)，用于支持 RC 和 RR 隔离级别的实现

快照在MVCC里的工作

在可重复读隔离级别下，事务在启动的时候就基于整个数据库拍了快照

快照的实现

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按 申请顺序严格递增
每行数据都是有多个版本的。每次事务更新数据的时候，生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id
同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id

例子

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8xKm8BHZ-1649846429874)(D:\TyporaImage\MySQL\image-20210923141234921.png)]

假设一个记录被多个事务连续更新，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25

V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的

比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。之后在这个事务执行期间，其他事务的更新对它不可见
在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前 启动了但还没提交 的所有事务 ID
数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图

数据版本的可见性规则，就是基于数据的 row trx_id 和一致性视图的对比结果得到的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EdWjm5J8-1649846429875)(D:\TyporaImage\MySQL\image-20210923142337811.png)]

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能

如果小于低水位，这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的
如果大于高水位，这个版本是由将来启动的事务生成的，是肯定不可见的
如果在低水位和高水位之间，那就包括两种情况
- 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见
- 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力

更新逻辑

事务 B 的 update 语句，如果按照一致性读，事务 B 的视图数组是先生成的，之后事务 C 才提交，不是应该看不见 (1,2) 吗，怎么能算出 (1,3) 来？

如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1

当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务 C 的更新就丢失了。因此，事务 B 此时的 set k=k+1 是在（1,2）的基础上进行的操作

更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读（current read）

在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的 row trx_id 是 101

在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3

如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。

下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）

select k from t where id=1 lock in share mode;
select k from t where id=1 for update;

事务的可重复读的能力

可重复读的核心就是一致性读，事务更新数据的时候，只能用 当前读
如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待

幻读

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);

如果只在 id=5 这一行加锁，而其他行不加锁

select * from t where d=5 for update。查所有 d=5 的行，而且使用的是当前读，并且加上写锁
Q3 读到 id=1 这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现

上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”

产生幻读的原因：行锁只能锁住行，但是新插入记录的这个动作，要更新的是记录之间的间隙，InnoDB引入了 间隙锁

间隙锁和行锁不一样，跟间隙锁存在冲突关系的是 往这个间隙中插入一个记录的操作，间隙锁之间都不存在冲突关系

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]

因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的“都是前开后闭区间

MVCC

LBCC是基于锁的并发控制，因为锁的粒度过大，会导致性能的下降，因此提出了比 LBCC 性能更优越的方法 MVCC

MVCC是基于多版本的并发控制协议，通过版本号，避免同一数据在不同事务间的竞争，只存在于InnoDB引擎下，主要是为了提高数据库的并发读写性能，不用加锁就能让多个事务并发读写
MVCC的实现依赖于：三个隐藏字段、Undo log和Read View，其核心思想就是：
- 只能查找 事务id <= 当前事务ID的行
- 只能查找 删除时间 >= 当前事务ID的行，或未删除的行

隐藏列

MySQL中会为每一行记录生成隐藏列

DB_TRX_ID：事务ID， 根据事务产生时间顺序自动递增的，唯一的。如果某个事务执行过程中对该记录执行了 增、删、改 操作，那么InnoDB存储引擎就会记录下该事务的 Id
DB_ROLL_PTR：回滚指针，指向记录对应 undo log 的一个指针，大小为 7 个字节，InnoDB 便是通过这个指针找到之前 版本的数据。该行记录上所有旧版本，在 undo log中都通过链表的形式组织
DB_ROW_ID：行标识（隐藏单调自增 ID），如果表没有主键，InnoDB 会自动生成一个隐藏主键，大小为 6 字节。如果数据表没有设置主键，会以它产生聚簇索引
实际还有一个 删除 flag 隐藏字段，既记录被更新或删除并不代表真的删除，而是删除flag变了

undo log

要对一条记录做改动时都需要把回滚时所需的东西记录下来

Insert undo log ：插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了
Delete undo log：删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了
Update undo log：修改一条记录时，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。InnoDB把这些为了回滚而记录的这些东西称之为undo log。由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo log

每次对记录进行改动都会记录一条 undo 日志，每条 undo 日志也都有一个 DB_ROLL_PTR 属性，可以将这些undo日志都连起来，串成一个链表，形成版本链。版本链的头节点就是当前记录最新的值

undo log是 逻辑日志，只是将数据库逻辑的恢复到原来的样子。因为在多并发系统中，把一个页中的数据物理的恢复到原来的样子，可能会影响其他的事务

Read View
在 RR 隔离级别下，可以把每一次普通的select查询（不加for update语句）当作一次快照读，而快照便是进行 select 的那一刻，生成的当前数据库系统中所有未提交的事务id数组（数组里最小的id为min_id）和已经创建的最大事务id（max_id）的集合，即所说的一致性视图 readview 。在进行快照读的过程中要根据一定的规则将版本链中每个版本的事务id与 readview 进行匹配查询需要的结果

快照读是不会看到别的事务插入的数据的。幻读在 “当前读” 下才会出现。快照读的实现是基于MVCC(可以认为MVCC是行锁的一个变种) 但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。MVCC只在 RC 和 RR 两个隔离级别下工作，其他两个隔离级别不和MVCC不兼容。因为 RU 总是读取最新的数据行，而不是符合当前事务版本的数据行，而SERIALIZABLE 则会对所有读取的行都加锁。事务的快照时间点（即下文中说到的Read View的生成时间）是以第一个select来确认的。所以即便事务先开始，但是select在后面的事务的update之类的语句后进行，那么它是可以获取前面的事务的对应的数据

RC和RR隔离级别下的快照读和当前读：

RC隔离级别下，快照读和当前读结果一样，都是读取已提交的最新
RR隔离级别下，当前读结果是其他事务已经提交的最新结果，快照读是读当前事务之前读到的结果。RR下创建快照读的时机决定了读到的版本

Read View 就是事务进行快照读（普通select查询）操作的时候生产的一致性读视图，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，它由执行查询时所有未提交的事务id数组（数组里最小的id为min_id）和已经创建的最大事务id（max_id） 组成，查询的数据结果需要跟 read view 做对比从而得到快照结果

在这里插入图片描述

版本链比对规则

如果落在绿色部分（trx_id < min_id），表示这个版本是已经提交的事务生成的，这个数据是可见的
如果落在红色部分（trx_id > max_id），表示这个版本是将来启动的事务生成的，是肯定不可见的
如果落在黄色部分（min_id <= trx_id <= max_id），那就包含两种情况：
1. 若row 的 trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；如果是自己的事务，则可见；
1. 若row 的 trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见

undo log什么时候删除？

系统会判断，没有比这个 undo log 更早的 read view 的时候，undo log会被删除。所以这里也就是为什么我们建议你尽量不要使用长事务的原因。长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间

如何避免长事务对业务的影响

首先，从应用开发端来看：

确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，目标就是把它改成 1
确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉
业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间

其次，从数据库端来看：

监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill
Percona 的 pt-kill 这个工具不错，推荐使用
在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题
如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

锁

全局锁

全局锁就是对 整个数据库实例加锁，需要让整个库处于 只读状态 的时候

以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)

使用场景

做全库逻辑备份。通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。在备份过程中整个库完全处于只读状态

如果在主库上备份，那么在备份期间都 不能执行更新，业务基本上就得停摆
如果在从库上备份，那么备份期间从库 不能执行主库同步过来的 binlog，会导致主从延迟

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的

**一致性读是好，但前提是引擎要支持这个隔离级别。**比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。则需要使用 FTWRL 命令了

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用
二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

**表锁的语法是 lock tables … read/write。**与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 则其他线程写 t1、读写 t2 的语句都会被阻塞。线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大

**另一类表级的锁是 MDL（metadata lock)。**MDL 不需要显式使用，在访问一个表的时候会被自动加上

MDL 的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上

在 MySQL 5.5 版本中引入了 MDL

当对一个表做增删改查操作的时候，加 MDL 读锁

当要对表做结构变更操作的时候，加 MDL 写锁

读锁之间不互斥，可以有多个线程同时对一张表增删改查
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

安全的给小表加字段

首先要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务
如果要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁。这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁

不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Aw95wi55-1649846429880)(D:\TyporaImage\MySQL\image-20210923125607057.png)]

事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行

事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的

行锁就是针对数据表中行记录的锁。比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新

两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上，但并不是不需要了就立刻释放，而是要等到事务结束时才释放

对事物的帮助

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

例子

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票

从顾客 A 账户余额中扣除电影票价
给影院 B 的账户余额增加这张电影票价
记录一条交易日志

完成这个交易，需要 update 两条记录，并 insert 一条记录。为了保证交易的原子性，我们要把这三个操作放在一个事务中

根据两阶段锁协议，不论怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的

如果把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少

这就最大程度地减少了事务之间的锁等待，提升了并发度

死锁和死锁检测

死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L9VKGCWg-1649846429880)(D:\TyporaImage\MySQL\image-20210923130828734.png)]

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态

死锁的策略

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的

但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤

正常情况下采用第二种策略：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁

那如果是所有事务都要更新同一行的场景，每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作

虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务

解决热点行更新导致的性能问题

死锁检测要耗费大量的 CPU 资源

**如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。**但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的

**另一个思路是控制并发度。**如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题

基本思路：对于相同行的更新，在进入引擎之前排队，即InnoDB 内部就不会有大量的死锁检测工作了

并发控制要做在数据库服务端。考虑在中间件实现，如果团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面

在客户端做并发控制，你会很快发现这个方法不太可行，因为客户端很多。见过一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000

从设计上优化

将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和

每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗

这类方案 需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理

索引

索引是什么

索引是一种特殊的文件 (InnoDB数据表上的索引是表空间的一个组成部分) ，它们包含着对数据表里所有记录的引用指针
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。而且索引是一个文件，它是要占据物理空间的
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了

索引的优缺点

优点

加快数据的检索速度
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点

空间方面：索引需要占物理空间
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率

创建索引的原则

注意点

非空字段：应该指定列为NOT NULL，除非想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替空值
取值区分度大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高

创建索引的原则

最左前缀匹配原则 : mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

不应该索引

对于那些在查询中很少使用或者参考的列不应该创建索引
若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求
对于那些只有很少数据值或者重复值多的列也不应该增加索引
这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度
当该列修改性能要求远远高于检索性能时，不应该创建索引（修改性能和检索性能是互相矛盾的）
对于那些定义为text, image和bit数据类型的列不应该增加索引
这些列的数据量要么相当大，要么取值很少

索引的底层实现

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，不支持范围查询 (Memory存储引擎使用Hash)
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针

B-Tree索引

关键字集合分布在整颗树中，任何一个关键字出现且只出现在一个结点中
搜索有可能在非叶子结点结束，其搜索性能等价于在关键字全集内做一次二分查找，B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复直到 所对应的儿子指针为空 或已经是叶子结点

B+Tree索引

B+Tree性质:

n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引
所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接
所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字
B+ 树中，数据对象的插入和删除仅在叶节点上进行。有2个头指针，一个是树的根节点，一个是最小关键码的叶节点

和B树的区别

数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址
相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高

为什么用 B+ 做索引？

哈希表

哈希表是一种以 键 - 值 存储数据的结构，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表
假设身份证信息和姓名的表，需要根据身份证号查找对应的名字。首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2
但是ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的
哈希表这种结构适用于只有等值查询的场景

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀，用二分法就可以快速得到，这个时间复杂度是 O(log(N))
在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高
有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据

AVL 和 B+ 树比较

用AVL平衡二叉树，树高度太高，索引查询需要访问磁盘，每次访问以节点为单位进行磁盘I/O ，需要尽量减少数据读取的I/O操作，所以树高度一定不能太高。一棵 100 万节点的平衡二叉树，树高 20，一次查询可能需要访问 20 个数据块

B树和 B+树比较

B+树所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的，对于范围查找，比如15～50，B树需要中序遍历二叉树，但是B+树直接在叶子节点顺序访问就可以了

B+树中的查找

各个数据页组成一个双向链表，每个数据页中的记录会按照 主键值从小到大的顺序组成一个单向链表

每个数据页都会为存储在它里面的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用 二分法 快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dKBT60Zt-1649846429884)(D:\Work-Space\TyporaImage\image-20210901144317726.png)]

先从 目录项 中根据 二分法 快速确定出主键值为 20 的记录在目录项中(因为 12 < 20 < 209 ) .它对应的页是页 9

没有索引时进行查找

在一个页之间查找

目前表中的记录比较少，所有的记录都可以存放到一个页中。在查找记录时可以根据搜索条件的不同分为两种情况

以主键为搜索条件 ：可以在页目录中使用 二分法 快速定位到对应的槽，然后再遍历该槽对应分组中的记录，即可快速找到指定的记录
对非主键列的查找：数据页中并没有为 非主键列 建立所谓的页目录，无法通过 二分法 快速定位相应槽。只能从 Infimum 记录开始依次 遍历单向链表 中的每条记录，然后对比每条记录是否符合搜索条件

在多页中查找

定位到记录所在的页
从所在的页内查找相应的记录

在没有索引的情况下，无论是根据主键列还是其他列的值进行查找，不能快速定位到记录所在的页，只能从第一页沿着双向链表一直往下找

索引逻辑分类

按应用划分

主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL
普通索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
**联合索引: ** 一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 最左前缀 原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效

主键索引
ALTER TABLE TableName ADD PRIMARY KEY(column_list);

唯一索引
CREATE UNIQUE INDEX IndexName ON TableName(字段名(length));
ALTER TABLE TableName ADD UNIQUE (column_list);

普通索引
CREATE INDEX IndexName ON TableName(字段名(length));
ALTER TABLE TableName ADD INDEX IndexName(字段名(length));

普通索引和唯一索引(查询、更新、change buff)

查询过程

执行查询的语句是 select id from T where k=5 这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，可以认为数据页内部通过二分法来定位记录

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k = 5 条件的记录
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索

性能差距

对性能的差距微乎其微
InnoDB 的数据是按 **数据页 **为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB
因为引擎是按页读写的，即当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。对于普通索引来说，要多做的那一次 查找和判断下一条记录 的操作，就只需要一次指针寻找和一次计算
如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些
对于整型字段，一个数据页可以放近千个 key，出现这种情况的概率会很低。计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计

更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了
在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性

change buffer

change buffer 实际上它是可以持久化的数据。即在内存中有拷贝，也会被写入到磁盘上
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作
如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率

什么条件下使用change buffer

只有普通索引可以使用。唯一索引所有的更新操作都要先判断这个操作是否违反唯一性约束，需要判断表中是否有将要插入的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%

InnoDB插入数据的处理流程

第一种情况是，这个记录要更新的目标页在内存中

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间

第二种情况是，这个记录要更新的目标页不在内存中

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的

change buff的使用场景

并不是所有的普通索引使用 change buffer 都可以起到加速作用

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统
假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用

change buff 和 redo log

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

字符串字段加索引

--例如维护某邮箱系统
mysql> select f1, f2 from SUser where email='xxx';

--在 email 字段上创建索引的语句
mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

index1(email整个字符串的索引结构)

从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值
到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集
取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行

index2(email(6)索引结构)

从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1
到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃
取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集
重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束
在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行

对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了

select id,email from SUser where email=‘zhangssxyz@xxx.com’;

如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从 index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。如果使用 index2（即 email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值

即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

使用前缀索引就用不上覆盖索引对查询性能的优化了

建立索引时关注的是区分度，区分度越高越好

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

--使用下面这个语句，算出这个列上有多少个不同的值
mysql> select count(distinct email) as L from SUser;

--依次选取不同长度的前缀来看这个值，比如要看一下 4~7 个字节的前缀索引，可以用这个语句：
mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

当然，使用前缀索引很可能会损失区分度，所以需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，可以选择前缀长度为 6

前缀区分度不够好的情况

使用倒序存储

--存储身份证号的时候把它倒过来存，每次查询的时候
mysql> select field_list from t where id_card = reverse('input_id_card_string')

--由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度
--实践中你不要忘记使用 count(distinct) 方法去做个验证

使用 hash 字段

--表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

--然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段
--由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同
mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

倒序存储和使用hash字段的异同点

相同点，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地，hash 字段的方式也只能支持等值查询

它们的区别，主要体现在以下三个方面：

从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了
在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些
从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数

聚簇索引和非聚簇

聚簇索引和非聚簇索引

聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块

聚簇索引

聚簇索引就是按照 表的主键 构造一棵B+树，同时 叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页，每个数据页都通过一个双向链表 来进行链接
实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描

聚簇索引是按照顺序物理地存储数据吗？

如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。聚簇索引是逻辑上连续的
页通过双向链表链接，页按照主键的顺序排序。每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储

聚簇索引的好处

对于主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查找的数据

非聚簇索引

叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签
书签用来告诉 InnoDB存储引擎 哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，即非聚簇索引的书签就是相应行数据的聚集索引键
非聚簇索引的存在不影响数据在聚集索引中的组织，因此每张表上可以有多个非聚簇索引
当通过非聚簇索引来寻找数据时，InnoDB 存储引擎会遍历非聚簇索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录

聚簇索引优缺点

优点

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

覆盖索引和回表

在 InnoDB 里，主键索引也被称为聚簇索引，非主键索引也被称为二级索引

回表：

聚簇索引只需要在 主键索引树上搜索 主键字段。普通索引查询方式，则需要先要在 索引树上搜索 主键字段的值，再到 主键索引树 搜索一次

select * from T where ID=500

select * from T where k=5

覆盖索引：

查询的字段(主键) 已经在 k索引树上，因此可以直接提供查询结果，不需要回表

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

select ID from T where k between 3 and 5

最左前缀原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配
索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了
第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

既有联合查询，又有基于 a、b 各自的查询呢？

查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的。需要同时维护 (a,b)、(b) 这两个索引

考虑的原则就是空间，name 字段是比 age 字段大的，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引

假设 student表，根据 学院编号+班级 建立了一个联合索引，index_magor_class(magor，class)，联合索引的底层也是一颗B+树，只不过联合索引的B+树节点中存储的是逗号分隔的多个值。它是根据 学院编号 排序，再根据班级排序，如果索引后面还有字段，继续以此类推
查询的where条件如果只转入了班级，是走不到联合索引的。如果只传入学院编号，可能会走到联合索引。(MYSQL的执行计划和查询的实际执行过程并不完全吻合，比如你数据库数据量很少，可能直接全量遍历速度更快，就不走索引了)

在这里插入图片描述

mysql> create table t(
    -> a int not null,
    -> b char(10) not null,
    -> c int not null,
    -> d varchar(20) not null,
    -> index abc(a,b,c)
    -> )engine=innodb charset=utf8;
    
mysql> insert into t values(1,'hello',1,'world');
mysql> insert into t values(2,'hello',2,'mysql');

以下均为筛选条件不包含主键索引情况下：（主键索引优先级最高）

筛选条件中含有组合索引最左边的列但不含有主键搜索键的时候，至少会构建包含组合索引最左列的索引树。（如：index(a)）
查询列都是组合索引列且筛选条件全是组合索引列时，会构建满列组合索引树 （index(a,b,c) ）【覆盖索引】

-筛选条件包含普通搜索键但没包含组合索引列最左键，不会构建组合索引树
在这里插入图片描述

如果筛选条件全是组合索引最左连续列作为搜索键，将构建连续列组合索引树。（比如：index(a,b)却不能index(a,c)）
MySQL查询优化器会优化and连接，将组合索引列规则排号。（比如：b and a 等同于 a and b）

在这里插入图片描述

有null字段可以加索引吗？

not null能省了行头null标记位的空间

col1 varchar(10) null来说，改成col1 varchar(10) not null default ''确实是省了行头标志位的空间
col1 int null来说，改成col1 int not null default 0，省了行头那1 bytes的空间（就当做这一行只有一个null，那么ceiling(1/8)=8 bit），而要存储默认值0，是需要4 bytes，这就不是省空间了

不建议默认是null

null并不能等值对比，例如上面的SQL，要判断col2是null和1的行，就要拆分写成col2 is null or col2=1；另外在一些join查询里，null的不相等也会造成一些麻烦
count()不计算null，也就是count(col2)，所得的计数，是不包括有null值的行，这跟count(*) 就有区别了

给字符串加索引(前缀索引)

假设，现在维护一个支持邮箱登录的系统

create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb; 

select f1, f2 from SUser where email='xxx';

alter table SUser add index index1(email);

alter table SUser add index index2(email(6));

如果 email 这个字段上没有索引，那么这个语句就只能做全表扫描

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4Exi7ytu-1649846429886)(D:\TyporaImage\MySQL\image-20210923164124049.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UIZsqrNa-1649846429887)(D:\TyporaImage\MySQL\image-20210923164129320.png)]

第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串
第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：

从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值
到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集
取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1
到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃
取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集
重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束
在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行

通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多

对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

确认前缀

在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。可以通过统计索引上有多少个不同的值来判断要使用多长的前缀

算出这个列上有多少个不同的值
select count(distinct email) as L from SUser

依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索
select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7, from SUser;

使用前缀索引很可能会损失区分度，所以需要预先设定一个可以接受的损失比例，比如 5%

然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6

前缀索引对覆盖索引的影响

如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从 index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用 index2（即 email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值
index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息
使用前缀索引就用不上覆盖索引对查询性能的优化了在选择是否使用前缀索引时需要考虑的一个因素

区分度不高的情况

假设你维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为 6 的前缀索引的话，这个索引的区分度就非常低了

可能需要创建长度为 12 以上的前缀索引，才能够满足区分度要求。但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低

select field_list from t where id_card = reverse('input_id_card_string');

第一种方式是使用倒序存储，存储身份证号的时候把它倒过来存

由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。实践中不要忘记使用 count(distinct) 方法去做个验证

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'

**第二种方式是使用 hash 字段。**可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引

然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以查询语句 where 部分要判断 id_card 的值是否精确相同

倒序存储和hash 字段的异同点

相同点：

都不支持范围查询，倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了
hash 字段的方式也只能支持等值查询

不同点：

从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了
在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些
从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数

调优

索引优化

索引优化的步骤是：

先用慢查询日志定位具体需要优化的sql
使用 explain 查看sql执行计划
- 需要重点关注 type、extra、rows、filtered、
- type（查看索引类型）
- key（查看有没有使用索引）
- key_len（查看索引使用是否充分）
- rows (表示MySQL认为执行查询必须检查的行数)
- filtered (表示按表条件过滤的表行的估计百分比)
- extra（查看附加信息：排序、临时表、where条件为false等）
判断哪些索引使用不当
优化sql

explain

EXPLAIN 可以使用于 SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句
EXPLAIN与 可解释 的语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息
- 如何连接表以及以何种顺序连接表的信息
EXPLAIN与 非可解释 的语句一起使用时，显示在命名连接中执行的语句的执行计划

type列

该列的值表示连接类型，是查看索引执行情况的一个重要指标

执行结果从好到坏的顺序是从上到下 :

system / const > eq_ref > ref > range > index > ALL

system / const : 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
**eq_ref : ** 对于每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描
**ref : ** 使用非唯一索引或非唯一索引前缀进行的查找
**range : ** 常用于范围查询，比如：between … and 或 In 等操作
**index : ** 索引全扫描
**ALL : ** 全表扫描

rows列

表示MySQL认为执行查询必须检查的行数，对于InnoDB表，此数字是估计值，可能并不总是准确的

filtered列

表示按表条件过滤的表行的估计百分比。最大值为100，这表示未过滤行。值从100减小表示过滤量增加
rows显示了检查的估计行数，rows× filtered显示了与下表连接的行数
如果 rows为1000且 filtered为50.00（50％），则与下表连接的行数为1000×50％= 500

Extra列

包含有关MySQL如何解析查询的其他信息
- Impossible WHERE : 表示 WHERE 后面的条件都是 false
- Using WHERE : 表示使用了 WHERE 条件过滤
- **Using index : ** 表示使用了 覆盖索引，如果同时出现 Using WHERE 则无法直接通过索引查找来查询到符合条件的数据
- Using temporary : ** 表示是否使用了临时表**，性能特别差，需要重点优化，一般多见于order by 和 group by语句
- Using filesort : 表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现
- **Using index condition : ** 使用了 索引下推，在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据
- **Using join buffer : ** 表示是否使用连接缓冲

key列

表示实际用到的索引，可能会出现possible_keys列为NULL，但是key不为NULL的情况

key_len列

表示使用索引的长度，可以更进一步看出索引使用是否充分

决定key_len值的三个因素

字符集、长度、是否为空

字符编码	占用字节数
GBK	2
UTF8	3
ISO8859-1	1
GB2312	2
UTF16	2

mysql常用字段占用字节数

字段类型	占用字节数
char(n)	n
varchar(n)	n + 2
tinyint	1
smallint	2
int	4
bigint	8
date	3
timestamp	4
datetime	8

如果字段 允许为空 则加 1 个字节

explain select code from test1;

183 = 30 * 3 + 1 + 30 * 3 + 2

id列

select查询中的序号，比如：1、2、3、4等，它决定了表的执行顺序

某条sql的执行计划中一般会出现三种情况：

id相同：从上到下执行
id不同: 序号大的先执行
id相同和不同都有: 序号大的先执行，在依次从上到下

select_type

常用类型

类型	含义
SIMPLE	简单SELECT查询，不包含子查询和UNION
PRIMARY	复杂查询中的最外层查询，表示主要的查询
SUBQUERY	SELECT或WHERE列表中包含了子查询
DERIVED	FROM列表中包含的子查询，即衍生
UNION	UNION关键字之后的查询
UNION RESULT	从UNION后的表获取结果集

id列允许为空吗？

SELECT类型为 UNION RESULT时，允许为空

table列

该列的值表示输出行所引用的表的名称
- <unionM,N>：具有和id值的行的M并集N
- <derivedN>：用于与该行的派生表结果id的值N。派生表可能来自（例如）FROM子句中的子查询
- <subqueryN>：子查询的结果，其id值为N

partitions列

该列的值表示查询将从中匹配记录的分区

possible_keys列

该列表示可能的索引选择，如果此列是NULL，则没有相关的索引

ref列

表示索引命中的列或者常量

索引失效

普通索引使用 负向查询 (!=，NOT，<>，NOT IN，NOT LIKE) 索引失效，主键索引没影响
联合索引违背最左匹配原则
字段类型是字符串，where 时一定用引号括起来，否则索引失效
or条件除了同时是主键，否则索引失效
like通配符 % 开头 (like ‘%abc’)，索引失效直接全表扫描；若只是以%结尾，则不影响索引构建
在索引列上使用mysql的 内置函数或对索引列运算，索引失效

选错索引

优化器的逻辑

选择索引是优化器的逻辑，目的是 找到一个最优的执行方案，并用最小的代价去执行语句
- 扫描行数是影响执行代价的因素之一，扫描的行数越少则访问磁盘数据的次数越少，消耗的CPU资源越少
- 扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断

判断扫描行数

MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，只能根据统计信息来估算记录数
这个统计信息就是索引的 区分度，一个索引上不同的值越多，也就是基数越大，这个索引的区分度就越好
show index from t 查看索引的基数

MySQL得到索引的基数

把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，选择 采样统计
采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数
- 数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计

存储索引统计的方式

通过设置参数 innodb_stats_persistent 的值来选择，
- 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10
- 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16
- 由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。

解决方案

索引统计信息不准确

例子： explain select * from t where a between 10000 and 20000 和 explain select * from t where a between 10000 and 20000 ，前者 type为ALL、key为NULL、rows为104620、Extra为 Using WHERE，后者 type为RANGE、key为a、rows为 37116、Extra为 Using index condition。为什么选择执行扫描行数为 100000的计划呢？
- 如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的，而选择扫描 10万行是直接上主键索引上扫描的，没有额外代价
- 优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的
索引统计信息不准确 ，没有能准确地判断出 扫描行数 ，导致MySQL 选错索引
analyze table 重新统计索引信息
force index 强行指定索引

优化器误判的情况

例子：select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1
- 如果使用索引 a 进行查询，那么就是扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行
- 如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行
- explain 结果显示：key选择了索引b，rows为50198。扫描行数的估计值依然不准确、这个例子里 MySQL 又选错了索引
force index 强行指定索引
- 如果索引改了名字，这个语句也得改、迁移到别的数据库的话，这个语法还可能会不兼容
修改语句来引导优化器，order by b limit 1 改为 order by b,a limit 1
- 优化器选择使用索引 b，因为它认为使用索引b 可以避免排序，所以即使扫描行数多，也判定为代价更小，现在 order by b,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引 a
- 当然，这种修改并不是通用的优化手段，只是刚好在这个语句里面有 limit 1，因此如果有满足条件的记录， order by b limit 1 和 order by b,a limit 1 都会返回 b 是最小的那一行，逻辑上一致，才可以这么做
通过增加或者删除索引来绕过这个问题，删除索引 b

SQL调优

加索引
避免返回不必要的数据
优化sql结构
适当分批量进行
分库分表
读写分离

书写高质量SQL

查询SQL尽量不要使用 select * ，而是select具体字段

反例：

select * from employee;

正例：

select id，name from employee;

只取需要的字段，节省资源、减少网络开销
select * 进行查询时，可能不会使用到覆盖索引了，就会造成回表查询

小表驱动大表

假如有order和user两张表，其中order表有10000条数据，而user表有100条数据，查一下所有有效的用户下过的订单列表

sql语句中包含了in关键字，则它会优先执行in里面的 子查询语句 ，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快
sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了
- in 适用于左边大表，右边小表
- exists 适用于左边小表，右边大表

批量操作

反例:

在循环中逐条插入数据

优化：

提供一个批量插入数据的方法，只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大

不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理

多用limit

例如：查询某个用户下的第一个订单，想看看他第一次的首单时间

反例:

根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合

优化:

使用limit 1，只返回该用户下单时间最小的那一条数据即可

in中值太多

例如：想通过指定的一些id，批量查询出用户信息

该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时，可以在sql中对数据用limit做限制，不过我们更多的是要在业务代码中加限制
还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回

优化limit分页
做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下

反例：

select id，name，age from employee limit 10000，10

正例：

//方案一 ：返回上次查询的最大记录(偏移量)
select id，name from employee where id > 10000 limit 10

//方案二：order by + 索引
select id，name from employee order by id  limit 10000，10

//方案三：在业务允许的情况下限制页数：

偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量 + 要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的
方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少
方案二，order by + 索引，也是可以提高查询效率
方案三，建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页

使用where条件限定要查询的数据，避免返回多余的行

假设业务场景是这样：查询某个用户是否是会员

反例：

List <Long> userIds = sqlMap.queryList("select userId from user where isVip = 1");

booleanisVip =userIds.contains(userId);

正例：

Long userId = sqlMap.queryObject("select userId from user where 
userId='userId' and isVip='1' ")
boolean isVip = userId！= null;

需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销

Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录
right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录

都满足SQL需求的前提下，推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理

反例：

select * from tab1 t1 left join tab2 t2 on t1.size = t2.size 
where t1.id> 2

正例：

select * from (select * from tab1 where id > 2)
 t1 left join tab2 t2 on t1.size = t2.size

如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点
使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少

删除冗余和重复索引

反例：

KEY `idx_userId`(`userId`)
KEY `idx_userId_age` (`userId`,`age`)

正例:

//删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引
KEY `idx_userId_age`(`userId`,`age`)

重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能的

尽量避免在where子句中使用or来连接条件 (索引失效)
userid为索引

反例：

select * from user where userid = 1 or age =18

正例：

//使用union all
select * from user where userid = 1
union all
select * from user where age = 18

//或者分开两条sql写：
select * from user where userid = 1
select * from user where age = 18

使用or可能会使索引失效，从而全表扫描

对于or + 没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描 + 索引扫描 + 合并
如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理

优化like语句 (索引失效)

反例：

select userId，name from user where userId like '%123'

正例：

select userId，name from user where userId like '123%'

把%放前面，并不走索引，放关键字后面，还是会走索引的

避免在索引列上使用mysql的内置函数 (索引失效)

业务需求：查询最近七天内登陆过的用户(假设loginTime加了索引)

反例：

select userId, loginTime from loginuser 
  where Date_ADD(loginTime,Interval 7 DAY) >= now();

正例：

select userId, loginTime from loginuser 
where loginTime >= Date_ADD(NOW(), INTERVAL - 7 DAY)

尽量避免在where子句中对字段进行表达式操作 (索引失效)

反例：

select * from user where age - 1 = 10

正例：

select * from user where age =11

age加了索引，但是因为对它进行运算，索引直接迷路了，将导致系统放弃使用索引而进行全表扫描

尽量避免在where子句中使用!=或<>操作符 (索引失效)

反例：

select age,name from user where age <> 18

正例：

//可以考虑分开两条sql写
select age, name from user where age < 18

select age,name from user where age > 18

使用!=和<>很可能会让索引失效，将引擎放弃使用索引而进行全表扫描

使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则 (索引失效)

KEY idx_userid_age(userId,age)

反例：

select * from user where age = 10

正例：

//符合最左匹配原则
select * from user where userid = 10 and age = 10

//符合最左匹配原则
select * from user where userid = 10

创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关

对查询进行优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描

反例：

select * from user where address ='深圳' order by age

正例：

添加索引
alter table user add index idx_address_age (address,age)

主从同步

Master 数据库只要发生变化，立马记录到 Binary log 日志文件中
Slave 数据库启动一个 I/O thread 连接 Master 数据库，请求Master变化的二进制日志
Slave I/O 获取到的二进制日志，保存到自己的 Relay log 日志文件中
- Slave 有一个 SQL thread 定时检查 Realy log是否变化，变化那么就更新数据