mysql

三大范式

数据库在表设计的方法论，为了尽可能的降低表之间的耦合度，提高表的可读性。

第一范式(**确保每列保持原子性)**：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。==数据库表中的任何字段都是单一属性的，不可再分==。==单表（字段）拆分到不可拆分为止==
第二范式(**确保表中的每列都和主键相关)**：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。==每一行数据唯一性==，比如地址重复就在写一个地址表，也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
第三范式(**确保每列都和主键列直接相关,而不是间接相关)**：任何非主属性不依赖于其它非主属性。==表和表之间关联主键依赖==
鲍依斯-科得范式（BCNF）：在3NF的基础上，库表中任何字段对任一候选关键字段的传递函数依赖都不存在。

3NF很简单就是2NF之上再消除传递函数依赖

DDL和DML的区别

DDL（Data Definition Languages）：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

DML（Data Manipulation Language）：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

DCL（Data Control Language）：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

DDL 是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般开发人员很少使用。

非关系型数据库和关系型数据库的区别，谈谈优势比较？

关系型数据库

MySql，Oracle

关系型数据库指的是使用关系模型（二维表格模型）来组织数据的数据库。关系型数据库是基于表格的结构，使用 SQL 语言进行查询和操作，适用于处理结构化数据。

优势

采用二维表结构非常贴近正常开发逻辑（关系型数据模型相对层次型数据模型和网状型数据模型等其他模型来说更容易理解）；
支持通用的SQL（结构化查询语言）语句；
丰富的完整性大大减少了数据冗余和数据不一致的问题。并且全部由表结构组成，文件格式一致；
可以用SQL句子多个表之间做非常繁杂的查询；
关系型数据库提供对事务的支持，能保证系统中事务的正确执行，同时提供事务的恢复、回滚、并发控制和死锁问题的解决。
数据存储在磁盘中，安全可靠。

不足：

高并发读写能力差：网站类用户的并发性访问非常高，而一台数据库的最大连接数有限，且硬盘 I/O 有限，不能满足很多人同时连接。
海量数据情况下读写效率低：对大数据量的表进行读写操作时，需要等待较长的时间等待响应。
可扩展性不足：不像web server和app server那样简单的添加硬件和服务节点来拓展性能和负荷工作能力。
数据模型灵活度低：关系型数据库的数据模型定义严格，无法快速容纳新的数据类型（需要提前知道需要存储什么样类型的数据）。

非关系型数据库

Redis、Memcached、MongoDB

非关系型数据库则是以键值对、文档、图形等形式存储数据，不需要预定义表格结构，适用于处理半结构化和非结构化数据。非关系型数据库的优势在于具有更高的可扩展性、更好的性能和更灵活的数据模型，但相对于关系型数据库，缺乏事务支持和复杂查询能力。

优势：

非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
速度快，效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘。
海量数据的维护和处理非常轻松，成本低。
非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
可以实现数据的分布式处理。

不足:

非关系型数据库暂时不提供 SQL 支持，学习和使用成本较高。
非关系数据库没有事务处理，无法保证数据的完整性和安全性。适合处理海量数据，但是不一定安全。
功能没有关系型数据库完善。
复杂表关联查询不容易实现。

基础

Limit

limit x, y 两个参数是什么

x ：offset：指定第一个返回记录行的偏移量（即从哪一行开始返回），注意：初始行的偏移量为0。
y : rows：返回具体行数。

在查询中，经常要返回前几条或者中间某几行数据时，用到limit

客户端通过传递start(页码)，pageSize(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句

1 2	# start是页码，pageSize是每页显示的条数。 select * from table limit (start-1)*pageSize,pageSize;

左连接和内连接

char 和 varchar的区别

CHAR类型： ==固定长度==

CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

VARCHAR类型： ==可变长度==

VARCHAR(M) 定义时，必须指定长度M，否则报错。
MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）；MySQL5.0版本以上，varchar(20)：指的是20字符。
检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

int(1)和int(10)的区别

int(M)

M : 表示显示宽度，M的取值范围是(0, 255)。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合ZEROFILL使用，表示用“0”填满宽度，否则指定显示宽度无效。如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会截断或插入失败？

如果整数值超过M位，就按照实际位数存储。只是无须再用字符 0 进行填充。

int(1)和int(11)在实际使用中，如果不使用 zerofill 是没有任何区别的，而且int型最大只能存储4294967295这个整数，也就是只有10位。

备份

sql指令：备份select * from tb into outfile ‘/tmp.txt’
导入load data infile ‘/tmp.txt’ into table tb;

索引，B+树

讲讲索引

索引是一种特殊的数据结构，通过建立索引可以加快数据搜索速度，它就像书的目录一样，MySQL默认使用innodb作为存储引擎，它的底层索引结果是B+树，查询效率是非常高的，但是不一定创建索引就是非常好的，索引的创建本身是非常消耗资源的，在数据量比较大的时候创建索引需要花费大量时间，而且索引本身是非常占用内存空间的；在后期新增数据的时候可能需要进行数据的移动，后期维护也是需要大量时间的；当数据量比较小的时候没有必要建立索引，MySQL底层在判断索引和整表查询时间差不多的时候是不会使用索引的；有时候建立索引过多或者建立不当可能起到相反的作用

关于优化器的逻辑，使用何种索引的判断标准有三点：（1）扫描行数，并兼顾回表代价，（2）是否需要排序，（3）是否需要使用临时表

为什么要控制各个字段大小？控制单行数据大小

表设计，严格控制字段的大小，B+树的高出扇性

假设主键是bigint（自增，或者snowflake生成），占8个字节

假设id为1，存储为80000001(256)，id为3，存储为80000003(256)

存储器每页16KB
16KB / (8B + 6B) = 1170.29，以1100计算
第一层，指向1100二层节点
第二层，指向1100^2 三层节点，10^6，1百万
第三层：假设一行数据占16KB，则存储1百万数据（16KB/16KB * 1百万）；假设一行数据占1KB，则存储一千六百万数据（16KB/1KB\ * 1百万）；
因此需要严格控制存储字段大小

每次加载16KB内存页，二分查找索引，Olog(N)，再找下一层，共三次I/O索引

MySQL底层数据结构为什么采用B+树？为什么不用B树，二叉树，哈希表？

B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。非叶子节点存放的是索引键值和页指针
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

B+树可以在内存有限的情况下存储更多的节点。B+树的内部节点不保存数据，只保存索引，而且内部节点的大小通常要比数据节点小得多，因此可以在内存中缓存更多的节点。
B树非叶子节点和叶子节点都会存储数据和索引，这样导致一页中存储的键值减少，指针跟着减少，要保存大量数据只能增加树的高度，导致性能降低。假设参数规格不变，存储一半数据，一半索引，第三层550 + 550 ^ 2 + 550 ^2 * 16= 480万，大概是B+树的1/4；
二叉树存储相同的节点树的深度更高，则需要更多的I/O读取次数

哈希表，在没有冲突的情况下，时间复杂度为O(1)，桶位转换为链表/红黑树，时间复杂度为O(logN)，比B+树快，但：

**不支持模糊匹配%**，无法使用LIKE”%”，由于哈希计算没有局部特性，例如hash(皮卡丘)跟hash(皮卡)没有关系
不支持范围匹配， 不支持排序，没有顺序性，例如查询1~100，采用B+树查到1，再后遍历，直到大于100则结束，而且数据一般在连续内存页中
哈希冲突问题
不支持联合索引（最左匹配原则）

为什么B+树可以加快查询速度

InnoDB中B+索引能提高查询速度的原因其实就是通过引进B+树的结构将页的定位过程进行了优化。因为索引使用了B+树数据结构来存储，利用二分查询的原理O(logN)，有效的减少了磁盘IO的次数，所以查询会变快

B+树的数据节点形成有序链表，方便范围查找和排序。B+树的数据节点之间是通过指针形成有序链表的，因此可以很方便地进行范围查找和排序，比如查询一个范围内的记录或者按照关键字排序。B树虽然也可以进行范围查找和排序，但是需要进行中序遍历，效率较低。
B+树可以更好地利用磁盘预读。磁盘I/O是非常耗时的操作，为了提高查询效率，需要尽量减少磁盘I/O的次数。B+树的数据节点形成有序链表，因此可以很好地利用磁盘的预读特性，将多个相邻的数据块一次性读入缓存，减少磁盘I/O的次数。

B+树的非叶子节点为什么是双向链表

在MySQL中，B+树的非叶子节点被实现为一个双向链表，是为了加速索引的查找和维护。B+树是一种多叉树结构，每个节点中存储了多个关键字，它的节点分为两种类型：内部节点和叶子节点。内部节点存储了关键字的范围以及指向下一层子节点的指针，而叶子节点则存储了关键字以及指向数据记录的指针。

在MySQL中，B+树的双向链表设计使得节点间的访问更加高效。由于B+树的内部节点和叶子节点都可能会在查找的过程中被访问到，因此需要在内部节点和叶子节点之间建立连接，使得它们能够快速地互相查找。双向链表可以实现节点之间的双向遍历，因此可以在查找时快速定位到目标节点。

另外，MySQL的B+树中还有一个特殊的头节点和尾节点，它们分别指向B+树的第一个节点和最后一个节点，可以方便地进行范围查找和遍历操作。由于双向链表具有前驱和后继指针，因此可以通过头节点和尾节点进行快速的查找操作，这也是B+树采用双向链表的一个重要原因。

如何查看索引是否生效？

如果需要查看索引是否生效，可以使用MySQL提供的 EXPLAIN 命令来查看查询计划。在查询语句前加上 EXPLAIN 关键字，即可查看查询计划的详细信息。在查询计划中，可以查看到MySQL优化器选择的执行计划、使用的索引以及索引的类型等信息。如果查询计划中显示使用了合适的索引，则说明索引生效；否则，就需要考虑优化查询语句或索引的设计。

最左前缀原则

最左前缀原则是指在使用联合索引进行查询时，如果查询语句中使用了联合索引的一部分列，那么联合索引可以被用于优化查询。这个原则也可以称为“最左匹配原则”。
最左前缀原则是指在数据库中创建联合索引时，索引可以覆盖查询语句中最左侧的前缀字段。也就是说，在联合索引中，如果查询语句只涉及到索引最左侧的若干个字段，则该查询语句可以使用该联合索引进行优化

面试中常被提到的最左前缀匹配原则 - 知乎 (zhihu.com)
为什么可以模糊查询？
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，

最左前缀匹配原则，非常重要的原则，建立一个索引，对于索引中的字段，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的查询优化器会帮你优化成索引可以识别的形式

索引分类

主键索引
唯一索引（允许空值NULL，主键索引不允许）
普通索引
全文索引
联合索引（最左前缀原则）

根据物理存储的形式

聚簇索引，每张表==只有一个==聚簇索引B+树，以primary key构建，非叶子节点存储主键（主键类型大小，bigint为8字节）和下级索引（6个字节），叶子节点存储整行数据。==聚簇索引的叶节点存放表的的行记录数据。==
非聚簇索引也称辅助索引、二级索引，也是B+树，每张表可以有多个辅助索引，非叶子节点存储主键和索引，叶子节点存储主键和索引辅助属性（存储主键，用于回表查询）。==而非聚簇索引叶节点存的是指针，指向对应的数据块。==

聚簇索引的优点：

通过聚簇索引查找目标数据时理论上比非聚簇索引要快。因为聚簇索引能够在B+树索引的叶子节点上直接找到数据。而非聚簇索引查找目标数据需要多一次索引查询。
此外，由于定义了数据的逻辑顺序，聚簇索引能够特别快地访问一定范围内的数据。

聚簇索引的存储并不是物理上的连续，而是逻辑上的连续。数据页之间通过双向链表链接，按照主键的顺序排序。而数据页内的记录是通过单向链表进行维护的，物理存储上可以同样不按照主键的顺序存储。

如何定义索引？

经常被用作查询条件的列。
参与表的连接的列。
用于排序和分组的列。
用于唯一性约束的列。
经常用于子查询的列。
尽量扩展索引，不要新建索引

需要看字段内容的随机性，随机度越高作为索引的效率越高。
看离散度可以用show index，如果cardinality（随更新表实时变化）越接近1（离散度越高）越适合做索引，同时要结合实际开发的业务需求。

why？
这个就要从索引的数据结构 B+TREE 来说了。如果索引列没有重复的值，那在索引树搜索的时候查询到目标之后不会再进行搜索。如果有大量相同值，那么很多叶子节点磁盘块里面都是相同值，那么在搜索到目标之后还需要继续搜索（继续查询其他磁盘块，进行 IO 操作）

唯一标识符：通常建立在主键上，以确保表中每个记录都具有唯一的标识符。例如，可以在 ID 列上建立唯一索引。
外键关联：在连接两个表时，应在外键列上建立索引，以便快速查找连接的行。
经常查询的列：如果某个列是经常被用作查询条件的列，应该在该列上建立索引，以加快查询速度。例如，在一个产品表中，经常按照产品名称查询，可以在产品名称列上建立索引。
排序和分组的列：如果某个列经常用于排序和分组操作，应该在该列上建立索引，以加快这些操作的速度。例如，在一个订单表中，经常按照下单时间排序，可以在下单时间列上建立索引。
经常用于子查询的列：如果某个列经常用于子查询操作，应该在该列上建立索引，以加快子查询的速度。

什么时候适用索引？

字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

为什么不每个字段都建立索引？

哒咩，索引是一颗逻辑上的 B+TREE，需要维护，当对表中的数据进行增删改的时候，索引树也要动态维护（变化）。这样会降低数据增删改的速度，所以索引不能无脑建。
建立索引可能会增加数据的存储空间，并且在插入、更新和删除操作时会有一定的开销。因此，在建立索引时，需要权衡查询速度和数据操作的开销。建议在需要优化查询性能的场景下，选择具有较高查询频率的列建立索引，同时避免过度使用索引，以避免影响数据操作的性能。

在哪些场景下索引会变得越来越慢

SELECT *，索引没有覆盖；
InnoDB默认secondary找到primary，你这样很可能最后全部是随机IO；

当返回的结果较多时，非聚集索引效率可能比全表扫描还要低。因为非聚集索引必定会引发mark lookup操作。性能优化不可能做到每个字段的查询都能优化到最快，必须根据访问模式有针对的优化

单表可以建立多少索引？

索引一般不明显影响插入性能（大量小数据例外），因为建立索引的时间开销是O(1)或者O(logN)

官方文档InnoDB Limits ：1个表最大只能创建64个2级索引，加上主键索引共65个。复合索引最多只能16列，超过就会报错。

索引失效

like未使用最左前缀，where A like “%China”，即以==%开头会索引失效==
or会使索引失效，当==or两端其中一个条件没有使用索引==。如果查询字段相同，也可以使用索引。例如 where A = a1 or A = a2（生效），where A=a or B = b （失效）
联合查询未使用最左前缀，例如联合索引（A，B），例如：where A = a and B = b 会使用索引，where B = b 不会使用索引。即==当创建联合索引中where没有从联合索引的首个索引开始时==索引失效
+-*/运算，当==索引字段被运算时==索引失效
在索引列上的操作，or、!= （<>）,not in，is not 等，即==取非结果集==
is null、is not null；==索引本身不对null值的数据做处理==。理论上不要让字段为null，处理方法：字段强制不为null、字段默认值
使用==内置函数==upper()等，以及例如：使用索引时，数字和string==类型不统一==，varchar = 12345没有加引号，内部实际使用了convert函数导致失效。
版本：不同版本
1
SELECT * FROM tb WHERE a > 3; // a 辅助索引
MySQL<5.6：走全表扫描，不使用索引
**离散读**：a > 3 4 5 6 7 8 随机查 5 7 4 8 3, 5 * 3 次io
MySQL>=5.6 ：走索引
搜索一个索引而在另一个索引上做 order by， where A = a order by B，只会使用A上的索引，因为查询只使用一个索引。

like索引失效的原因

%号在右:由于B+树的索引顺序，是==按照首字母的大小进行排序==，%号在右的匹配又是匹配首字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。
%号在左:是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引.
两个%号:这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的.

回表，如何避免回表？

通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

这就是所谓的回表查询，==先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。==

避免回表：实现索引覆盖
覆盖索引，==不是索引，是一种效果==。覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

常见的方法是：==将被查询的字段，建立到联合索引里去。==

覆盖索引

就是==select的数据列只用从索引中就能够取得，不必从数据表中读取==，换句话说查询列要被所使用的索引覆盖。避免回表的产生从而减少IO，提升性能

1
2
3

# id 主键索引， age 普通索引
select * from user where age = 3; # 产生回表
select id from user where age = 3; # 直接再age索引中取到id

索引下推

服务层把查询工作下推到引擎层去处理。是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

效果：减少回表查询次数，提高查询效率，节约IO开销。

# id 主键索引， （name，age）联合索引
select id from user where name = "张%" and age = 3; 
# 5.6之前只会用到name索引，回表查询多次每个张%
# 5.6之后，通过索引下推，也会对age进行索引查询，只有一次回表

可用用explain语句查看是否使用了索引下推，explain的Extra列值为Using index condition

合并索引 index merge

通过索引合并机制，可以实现针对单表的一次查询中利用多个索引，好处是减少了回表查询的消耗。

一个表的多个索引的范围扫描可以对结果进行合并，合并方式分为三种：
intersection，union , Sort-Union。

Index Merge Intersection 索引合并-取交集

采用多索引AND等值查询。

Index Merge Union 索引合并-取并集

采用多索引OR等值查询。

Index Merge Sort-Union 索引合并-取有序并集

当 WHERE 子句转换为 OR 组合的多个范围条件时，可以采用排序联合算法Sort-Union。但 Index Merge 联合算法不适用。
排序联合算法Sort-Union和联合算法Union的区别在于，排序联合算法必须首先获取所有行的行 id，并在返回任何行之前对它们进行排序。

MRR多范围读取

MRR，全称「Multi-Range Read Optimization」。
我们知道二级索引是有回表的过程的，由于二级索引上引用的主键值不一定是有序的，因此就有可能造成大量的随机 IO，如果回表前把主键值给它排一下序，那么在回表的时候就可以用顺序 IO 取代原本的随机 IO。
简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

顺序读带来了几个好处：

1、磁盘和磁头不再需要来回做机械运动；
2、可以充分利用磁盘预读

比如在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理–当一个数据被用到时，其附近的数据也通常会马上被使用。

主键索引和普通索引的区别

主键索引是一种特殊的唯一索引，它要求被索引的列不允许有空值（NULL），并且每张表只能存在一个主键索引。主键索引的作用是保证表中每一行数据的唯一性，主键索引还能够优化表的查询性能。

普通索引是指对表中的某个列或多个列建立索引，它与主键索引的区别在于索引的字段不必是唯一的，而且可以有多个普通索引。普通索引的作用是加快查询速度，优化 SELECT 语句的执行效率。

主键索引也被称为聚簇索引,==叶子节点存放的是整行数据==; 而非主键索引被称为二级索引,==叶子节点存放的是主键的值==.
如果根据主键查询, 只需要搜索ID这颗B+树
而如果通过非主键索引查询, 需要先搜索k索引树, 找到对应的主键, 然后再到ID索引树搜索一次, 这个过程叫做回表.
总结, 非主键索引的查询需要多扫描一颗索引树, 效率相对更低.

单索引与联合索引在存储在存储上的区别

对于联合索引来说只不过比单值索引多了几列，而这些索引列全都出现在索引树上。

对于联合索引，存储引擎会首先根据第一个索引列排序，如果第一列相等则再根据第二列排序。这就是为什么要满足最左前缀的原因。

order by索引

当order by 字段出现在where条件中时，才会利用索引而无需排序操作。其他情况，order by不会出现排序操作。

Order By处理数据（排序）看第一部执行计划是不是用到索引，如果用到了就可以直接获得索引的顺序，从而避免再次排序。如果没用到就做排序（using filessort）。

搜索一个索引而在另一个索引上做 order by， where A = a order by B，只会使用A上的索引，因为查询只使用一个索引。

关于优化器的逻辑，使用何种索引的判断标准有三点：（1）扫描行数，并兼顾回表代价，（2）是否需要排序，（3）是否需要使用临时表

前缀索引

索引的长度限制

MySQL 官方手册索引的章节提到了，前缀索引长度限制是和引擎相关的，如果用的是 InnoDB ，前缀上限是 767 字节，当启用 innodb_large_prefix 时，上限可以达到 3072 字节。如果用的是 MyISAM，前缀上限是 1000 字节

我们知道，MySQL 和 Oracle 在索引上最大的一个区别，就是索引存在长度的限制。==如果是超长键值，可以支持创建前缀的索引，顾名思义，取这个字段的前多少个字符/字节作为索引的键值。==

之所以可以定义一个字段前缀作为键值，存储效率是考虑的一个因素，如果列名的前10个字符通常都是不同的，检索这10个字符创建的索引应该会比检索整个列作为索引的效率更高，使用列前缀作为索引会让索引树更小，不仅节省空间，还可能提高插入操作的速度。

前缀索引

MySQL前缀索引是一种索引技术，它允许在索引列上只使用列值的前缀部分来创建索引，而不是使用整个列值。这可以在某些情况下提供性能优势，特别是当索引列的数据量很大或者列的数据类型较长时。

==当索引是很长的字符序列（比如BLOB,TEXT,或者很长的VARCHAR）时，这个索引将会很占内存，而且会很慢，这时候就会用到前缀索引了。==所谓的前缀索引就是去索引的前面几个字母作为索引，但是要降低索引的重复率，索引我们还必须要判断前缀索引的重复率

以下是关于MySQL前缀索引的一些重要信息：

创建前缀索引：在创建索引时，可以通过指定索引长度来创建前缀索引。例如，可以使用以下语法创建一个前缀长度为10的索引：
1
CREATE INDEX index_name ON table_name (column_name(10));
这将在索引列的前10个字符上创建一个前缀索引。
索引选择性：前缀索引的选择性通常比完整列索引要低，因为它只考虑了列值的一部分。选择性是指索引中不同值的数量与总行数的比率。较低的选择性可能导致查询优化器选择不使用索引，而是执行全表扫描。
索引覆盖：前缀索引可以在某些情况下作为覆盖索引使用。当查询只需要索引列的前缀部分时，可以使用前缀索引来满足查询需求，而无需回到原始数据行。这可以提高查询性能，减少磁盘I/O。
索引长度选择：选择适当的前缀长度是使用前缀索引的关键。较小的前缀长度可以提高索引的选择性，但可能会导致索引范围较大，从而增加了索引的大小和存储需求。较大的前缀长度可以减小索引的大小，但可能会降低索引的选择性，导致性能下降。
查询限制：使用前缀索引时，需要注意查询中对索引列的条件限制。查询条件必须与索引的前缀匹配，否则索引将无法使用。如果查询需要使用索引列的后缀部分进行匹配，前缀索引将无法满足查询需求。

前缀索引在某些情况下可以提供性能优势，特别是在大型列或长文本列上。然而，它也存在一些限制和缺点，需要根据具体情况进行权衡和选择。在设计和使用前缀索引时，需要考虑数据的唯一性、查询模式、索引长度的选择以及与其他索引的比较等因素，以获得最佳的性能和存储效率。

mysql一次搜索的过程

客户端通过 TCP 连接发送连接请求到 MySQL 连接器，连接器会对该请求进行权限验证及连接资源分配
查缓存。（当判断缓存是否命中时，MySQL 不会进行解析查询语句，而是直接使用 SQL 语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。）==8.0后缓存特性删除了==。
语法分析（SQL 语法是否写错了）。如何把语句给到预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义。
优化。是否使用索引，生成执行计划。
交给执行器，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

更新语句执行会复杂一点。需要检查表是否有排它锁，写 binlog，刷盘，是否执行 commit。

MVCC

一句话总结： ==MVCC是mysql基于自己的回滚机制为并发场景下的读操作做的读取的优化。==

1.MVCC概念：MVCC全称Multi-Version Concurrency Control,即多版本并发控制。MVCC是为MySQL并发场景下无锁生成读视图进行读操作来进行多版本控制。

2.MVCC实现依赖undo log

3.不同隔离级别下MVCC表现
“读未提交”隔离级别：直接返回记录上的最新值，没有视图概念。
“读已提交”隔离级别下，视图是在每次SELECT时生成的。（违背了事务ACID里的隔离性）
“可重复读（默认）”隔离级别下：视图是在第一次SELECT时创建的，事务读取期间的SELECT都用这个视图。（此隔离级别下需要利用间隙锁来解决幻读问题）
“串行化”隔离级别下：直接用加锁的方式来避免并行访问，没有视图概念。

隐藏字段

在内部，InnoDB 存储引擎为每行数据添加了三个隐藏字段：

DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

Read View

Read View 主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

主要有以下字段：

m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
m_creator_trx_id：创建该 Read View 的事务 ID

undo log

undo log 主要有两个作用：

当事务回滚时用于将数据恢复到修改前的样子
另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

数据可见性算法

在 InnoDB 存储引擎中，创建一个新事务后，执行每个 select 语句前，都会创建一个快照（Read View），快照中保存了当前数据库系统中正处于活跃（没有 commit）的事务的 ID 号。其实简单的说保存的是系统中当前不应该被本事务看到的其他事务 ID 列表（即 m_ids）。当用户在这个事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件

使用

我们在使用MVCC的时候，一般是根据业务场景搭配使用MVCC和乐观锁悲观锁，通过MVCC来解决读写冲突，乐观锁或悲观锁用于解决写写冲突，从而最大程度的去提高数据库的并发性能。

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，从而提高数据库的并发读写的处理能力。
能实现读一致性，从而解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决数据更新丢失的问题。
采用乐观锁或者悲观锁用来解决写和写的冲突，从而最大程度地去提高数据库的并发性能。

举例：

假设ABC三个线程，读写同一条数据

C开启事务，然后睡觉
A同时开启事务，修改提交

后B开启事务，修改提交

C醒来，读取，读到的是那条数据？

此时MVCC一共有3个快照版本，A修改前，B修改前，B修改后

“可重复读（默认）”：读取到A修改前创建的快照，即最原始的数据
“读已提交”：读取到B修改后提交的版本，即最新的版本（违背了事务ACID里的I隔离性）
“读未提交”：即时读取，MVCC几乎不起作用，会脏读
“串行化”：读写都会锁定，因此不需要MVCC，没有并发访问，所有情况都能避免，但==速度慢、性能差==

脏读

==现在我们几乎碰不到脏读的情况，除非将Mysql的事务隔离级别定义为“读未提交”==，才会出现：当一个事务正在访问数据，并且对数据进行了修改，而这种数据还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据还没有提交那么另外一个事务读取到的这个数据我们称之为脏数据。

不可重复读

读取同一条数据，第一次读取和第二次读取内容不一样
对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。之后Session A 再次读取 同一个字段，值就不同了。那就意味着发生了不可重复读。

幻读

第一次读取和第二次读取读取的结果数量不一样，插入了数据，变多了。
对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插入 了一些新的行。之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。

脏写（ Dirty Write ）

对于两个事务 Session A、Session B，如果事务Session A修改了另一个未提交事务Session B修改过的数据，那就意味着发生了脏写

[[锁]]

讲一下MySql的锁

首先如果按照锁的粒度来分类有：行级锁，表级锁和页级锁。
行锁是mysql中锁定粒度最低的锁，表示只针对当前操作的行进行加锁。因此对于并发冲突最少，但加锁的开销也最大，行级锁分为共享锁和排他锁也就是读锁和写锁。
表锁是锁定粒度最大的锁，表示对当前操作的整张表进行加锁，它速度快但是造成的冲突多，并发度最低。
页级锁是mysql中粒度介于行锁和表锁之间的锁，因为表级锁速度快，但冲突多，行级冲突少，但速度慢，所以有了页锁一次锁定相邻的一组记录。

按锁级别的分类有：共享锁、排他锁和意向锁。
共享锁又称读锁，针对一份数据多个事务可以并发的去读取数据，但不能修改数据。如果数据已经有共享锁则其他事务只能再加共享锁不能加排他锁。
排他锁又称写锁、独占锁，如果事务A对数据加排他锁后，其他事务不能对该数据加任何类型的锁。只有获得排他锁的事务可以对该数据进行读写操作。
意向锁是表级锁，它的目的是为了在一个事务中揭示下一行要被请求锁的类型。意向锁分为意向共享锁（IS）、意向排他锁（IX）。意向锁是InnoDB自动添加的不需要用户去干预，对于insert、update、delete会自动给涉及的数据添加排他锁
意向共享锁，表示事务准备给数据行加入共享锁，也就是事务想要给一个数据行添加共享锁时必须先取得该表的意向共享锁。
意向排他锁，表示事务准备给数据行加入排他锁，也就是事务想要给一个数据行添加排他锁时必须先取得该表的意向排他锁。

InnoDB存储引擎实现了如下两种标准的行级锁：

共享锁（S Lock），允许事务读一行数据。
排他锁（X Lock），允许事务删除或更新一行数据。

1 2	select ... for update; select ... lock in share mode;

update是行锁还是表锁

最后要看优化器选择走索引还是全表扫描
InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

从数据操作的粒度划分：表级锁、页级锁、行锁

他们的加锁开销从大到小，并发能力也是从大到小。

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎 )和页级锁(BDB引擎 )。

行级锁

行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
==开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。==

表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
==开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。==
意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁
==开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般==

MyISAM和InnoDB存储引擎使用的锁：

MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

乐观锁和悲观锁

悲观锁：在所有操作前都上锁
乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。

悲观锁：

假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。==共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程==。
实现方式：使用数据库中的锁机制

乐观锁：

==假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性==。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
实现方式：乐一般会使用版本号机制或CAS算法实现。

乐观锁的版本号机制

在表中设计一个版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE … SET version=version+1 WHERE version=version 。此时如果已经有事务对这条数据进行了更改，修改就不会成功。

乐观锁的时间戳机制

时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。
你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或者时间戳），从而证明当前拿到的数据是否最新。

使用场景

乐观锁适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
悲观锁适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

间隙锁

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙也加上了间隙锁。这样就确保了无法再插入新的记录。

什么时候不用间隙锁

使用唯一索引进行查询

临键锁

间隙锁和行锁合称next-key lock临键锁，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。
需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列(包括主键列)上不存在临键锁。

优化锁方面的意见？防止死锁

使用较低的隔离级别
设计索引，尽量使用索引去访问数据，加锁更加精确，从而减少锁冲突
调整业务逻辑SQL执行顺序，避免update/delete长时间持有锁的SQL在事务前面。
避免大事务，尽量将达大事务拆成多个小事务来处理，小事务缩短锁定资源的时间，发生锁冲突的几率也更小。
选择合理的事务大小，给记录显示加锁时，最好一次性请求足够级别的锁。如：修改数据的话，最好申请排他锁，而不是先申请共享锁，修改时在申请排他锁，这样会导致死锁
不同的程序访问一组表的时候，应尽量约定一个相同的顺序访问各表，对于一个表而言，尽可能的固定顺序的获取表中的行。这样大大的减少死锁的机会。
尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
不要申请超过实际需要的锁级别
数据查询的时候不是必要，不要使用加锁。MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能：MVCC只在committed read（读提交）和 repeatable read （可重复读）两种隔离级别
对于特定的事务，可以使用表锁来提高处理速度活着减少死锁的可能。

出现死锁超时会怎么办？会一直等待吗？

锁等待：
MySQL有一个参数来控制获取锁的等待时间，默认是50秒。

死锁检测：
在第一个事务中，检测到了死锁，马上退岀了，第二个事务获得了锁，不需要等待50秒

为什么可以直接检测到呢？是因为死锁的发生需要满足一定的条件，所以在发生死锁时，InnoDB —般都能通过算法(wait-for graph)自动检测到。

死锁的产生条件（因为锁本身是互斥的）：

同一时刻只能有一个事务持有这把锁；
其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺；
当多个事务形成等待环路的时候，即发生死锁。

mysql死锁怎么排查

死锁检测：
在第一个事务中，检测到了死锁，马上退岀了，第二个事务获得了锁，不需要等待50秒

为什么可以直接检测到呢？是因为死锁的发生需要满足一定的条件，所以在发生死锁时，InnoDB —般都能通过算法(wait-for graph)自动检测到。

SHOW ENGINE INNODB STATUS; 其中status保存着最近一次死锁记录
MySQL 系统内部提供一个 innodb_print_all_deadlocks 参数，该参数默认是关闭的，开启后可以将死锁信息自动记录到 MySQL 的错误日志中。

线上错误日志报警发现死锁异常
查看错误日志的堆栈信息
查看 MySQL 死锁相关的日志
根据 binlog 查看死锁相关事务的执行内容
根据上述信息找出两个相互死锁的事务执行的 SQL 操作，根据本系列介绍的锁相关理论知识，进行分析推断死锁原因
修改业务代码

加锁分析

一条Update语句没有带where条件，加的是什么锁：

相当于表锁，锁住表格中的每一条记录和每个间隙，其它UPDATE、INSERT和DELETE语句都无法执行：

带了where条件没有命中索引，加的是什么锁：

相当于表锁，锁住表格中的每一条记录和每个间隙，其它UPDATE、INSERT和DELETE语句都无法执行：

两条更新语句更新同一条记录，加的是什么锁：
两条更新语句更新同一条记录的不同字段，加的是什么锁

MySQL的update语句在读的时候就直接加的X锁，会阻塞其它的DML语句的读(insert, update, delete等),所以不会发生死锁

通过索引更新：

如果当前有值，则只加当前行行锁
如果当前无值，或更新的为一个范围的值，那么会加行锁+间隙锁即临键锁

日志系统

MySQL事务日志介绍下？

innodb 事务日志包括 redo log 和 undo log。

重做日志（Redo Log）：记录所有数据修改操作的重做操作，包括 INSERT、UPDATE 和 DELETE 等操作，以便在发生故障时能够重新执行这些操作，以恢复数据的一致性。
回滚日志（Undo Log）：记录所有数据修改操作的撤销操作，主要用于事务回滚和 MVCC 等功能。

事务日志的目的：实例或者介质失败，事务日志文件就能派上用场。

redo log

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

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

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

重做日志（redo log）是InnoDB引擎层的日志，用来==记录事务操作引起数据的变化，记录的是数据页的物理修改==。
InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。
mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

好处：

redo日志降低了刷盘频率
redo日志占用的空间非常小
存储表空间ID、页号、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。

特点：

redo日志是顺序写入磁盘的
事务执行过程中，redo log不断记录

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？

（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

刷盘规则

开启事务，发出提交事务指令后是否刷新日志由变量innodb_flush_log_at_trx_commit 决定
- 0: 每次提交事务时，不会将 Log Buffer 中的日志写入 OS buffer, 而是通过一个单独的线程，每秒写入 OS buffer 并调用系统的 fsync() 函数写入磁盘的 Redo Log File, 这种方式不是实时写磁盘的，而是每隔 1s 写一次日志，如果系统崩溃，可能会丢失 1s 的数据。
- 1: 每次提交事务都会将 Log Buffer 中的日志写入 OS buffer 中，并且会调用 fsync() 函数将日志写入 Redo Log File 中，这种方式虽然不会再崩溃时丢失数据，但是性能比较差。也是这个变量的默认值。
- 2: 每次提交事务时，都只是将数据写入 os buffer 中，之后每隔 1s ,通过 fsync() 函数将 os buffer 中的数据写入 Redo Log 文件中。
每秒刷新一次，刷新日志的频率由变量 innodb_flush_log_at_timeout 的值决定，默认是 1s, 刷新日志的频率和是否执行了 commit 操作无关
当 Log Buffer 中已经使用内存超过一半时，会触发刷盘操作

写入机制

Redo Log 记录的是物理日志，其文件内容是以循环的方式写入的，一个文件写满了就写入另一个文件，最后一个文件写满了就会向第一个文件写入，并且是覆盖写。

Write Pos 是数据表中当前记录所在的位置，随着写入，这个位置逐渐向后移动，最后一个文件写满后，这个位置移动到第一个文件的开始处。
CheckPoint 是当前要擦除的位置，这个位置也是向后移动的，擦除之前要将数据更新到数据文件中。
Write Pos 和 CheckPoint 之间存在间隔，间隔表示还可以记录新的操作。如果 Write Pos 写入较快，追上了擦除的位置，则表示已经写满，不再向 Redo Log 文件中写数据了，此时需要停止写入，擦除一些数据。

怎么判断redolog是已提交的

当一个事务提交时，MySQL 会将该事务的所有修改操作记录在 redo log 中。这些记录不会直接写入磁盘，而是先写入 redo log buffer，等到 redo log buffer 填满或事务提交时才会将记录写入磁盘。redo log 中的记录只有在事务提交之后才是有效的。如果事务没有提交，则 redo log 中的记录会被回滚，不会对数据库产生影响。

在MySQL中，InnoDB引擎会维护一个LSN（Log Sequence Number）值，表示当前redo log写入的位置。当一个事务的redo log写入到磁盘时，该事务的commit信息会写入redo log的末尾，并记录一个commit标记。当MySQL重启或者发生crash恢复时，会从磁盘读取redo log，并通过commit标记判断哪些事务已经提交。如果一个事务的redo log中没有commit标记，则认为该事务未提交，需要进行回滚操作。

undo log

==原子性底层就是通过undo log实现的==。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。

undo log由两个作用，一是提供回滚，二是实现MVCC

redolog两阶段提交过程

写入redo log，处于prepare状态
写binlog
修改redo log状态为commit

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

为什么redo log要分两步写，中间再穿插写binlog呢？

因为*redo log影响主库的数据，binlog影响从库的数据，所以redo log和binlog必须保持一致才能保证主从数据一致。

binlog与redolog对比

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。在MySQL数据库的上层产生的，并且二进制日志不仅仅针对于InnoDB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。
虽然他们都属于持久化的保证但侧重点不同：
- redolog 让部分InnoDB引擎有了崩溃恢复的能力
- binlog保证了mysql主从架构的数据一致性，以及作为pit恢复数据

为什么binlog只能作为pit（时间恢复点）和主从同步，做不了持久化？

redo log 记录的是mysql内存页的修改逻辑(物理修改)，binlog记录的是mysql上层执行语句的log日志。

优化，单次insert多条数据，然后在事务里做一次性提交

一个表超大数据，分页查询怎么才能尽可能的快，如何优化大分页查询？

对于超大数据的分页查询，为了尽可能地快速和高效地查询数据，可以考虑以下优化措施：

使用合适的索引：确保使用正确的索引可以大大提高查询速度。在分页查询中，应该将索引按照分页查询的列进行优化，以便能够快速地找到要查询的数据。
使用LIMIT子句：使用LIMIT子句可以限制返回的行数，避免将整个表扫描，提高查询效率。同时，使用LIMIT子句的时候需要根据实际情况设置合适的偏移量和返回行数，以免查询过多的数据。
使用缓存：使用缓存可以避免每次查询都要重新从磁盘读取数据。在MySQL中，可以使用查询缓存和应用程序缓存等方式来实现数据的缓存。
分批次查询：将大分页查询拆分为多个小分页查询可以降低数据库负载和查询时间。在实现分批次查询时，需要根据实际情况合理设置每次查询的偏移量和返回行数。
记录下上次查询到的值，下次直接从该值后进行分页查询
避免使用子查询：尽量避免在分页查询中使用子查询，因为子查询会增加查询的复杂度和执行时间。
使用水平分表：如果数据表非常大，可以考虑将表水平分割成多个子表，每个子表存储一定数量的数据。这样可以将数据分布到多个表中，降低单个表的查询负担，提高查询效率。
使用分区表：MySQL提供了分区表的功能，将数据分区存储，可以显著提高查询效率。分区表将数据划分为多个区域，每个区域可以独立地进行查询和维护。

delete、drop和truncate区别

delete是数据操纵语言（DML），其按行删除，支持where语句，执行操作采用行锁，执行操作时会将该操作记录在redo和undo中，因此支持回滚。

truncate是数据定义语言（DDL），其操作隐式提交，不支持回滚，不支持where，删除时采用表级锁进行删除

drop也是DDL，不支持回滚，从数据库中删除整张表，其所有数据行，索引和权限也会被删除。

事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

四个特征ACID

事务就是一组原子性的操作，这些操作要么全部发生，要么全部不发生。事务把数据库从一种一致性状态转换成另一种一致性状态。

原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
一致性。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的//操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

实现原理

原子性：
InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

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

隔离性：
(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

一致性：
保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

隔离级别

Read Uncommitted（读取未提交内容）

在该隔离级别，==所有事务都可以看到其他未提交事务的执行结果==。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

不能避免脏读、不可重复读、幻读。

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

可以避免脏读，但不可重复读、幻读问题仍然存在。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

可以避免脏读、不可重复读，但幻读问题仍然存在。 这是MySQL的默认隔离级别。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

分库分表

分库分表是一种将数据分散到多个数据库或表中的技术，用于处理大规模数据存储和查询的问题。通常情况下，当单个数据库或表无法满足大量数据的存储和查询需求时，可以采用分库分表的方式来扩展数据库性能和容量。

优点：

提高性能：分库分表可以将负载分散到多个数据库或表中，提高数据存储和查询的性能。
扩展容量：分库分表可以将数据存储到多个数据库或表中，扩展数据库的容量。
增强可用性：当某个数据库或表发生故障时，可以将负载转移到其他数据库或表中，保证系统的可用性。
降低成本：通过分库分表可以采用廉价的硬件进行数据存储和查询，降低系统成本。

缺点

系统复杂性增加：分库分表需要考虑分片策略、数据一致性、跨库查询等问题，增加了系统的复杂性和开发难度。
数据一致性难以保证：当数据跨越多个数据库或表时，需要确保数据一致性，这可能会增加开发难度和系统复杂性。
查询复杂性增加：跨库查询需要将查询请求发送到多个数据库或表中，增加了查询复杂性和查询时间。

方式

分库分表可以分为水平分片和垂直分片两种方式：

水平分片：将数据按照某个规则分散到多个数据库或表中，每个数据库或表存储一部分数据。水平分片通常基于数据的某个属性，如时间、地理位置等进行分片，以确保每个分片的数据量大致相同。
垂直分片：将数据库或表按照功能或属性进行分割，将不同的数据存储到不同的数据库或表中。例如，可以将用户信息、订单信息、商品信息等分别存储到不同的数据库或表中。

水平拆分

水平拆分的意思，就是把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来抗更高的并发，还有就是用多个库的存储容量来进行扩容。

垂直拆分

垂直拆分的意思，就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多··访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。

两种分库分表的方式：

一种是按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了。
或者是按照某个字段hash一下均匀分散，这个较为常用。

range 来分，好处在于说，扩容的时候很简单，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据。实际生产用 range，要看场景。

hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表

跨库查询如何做优化？

对于常用字段可以做数据冗余
全局表，就是有可能系统中所有模块都可能会依赖到的一些表。
使用缓存

分库分表后如何保证全局唯一的主键id

UUID：不适合作为主键，因为太⻓了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
数据库自增 id : 两台数据库分别==设置不同步⻓==，生成不重复 ID 的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
雪花算法

uuid自增id 字符串做主键区别

UUID：不适合作为主键，因为太⻓了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。

针对B+树叶子节点，如果主键是自增的，那它产生的id每次都比前一次要大，所以每次都会将数据加在B+树尾部，B+树的叶子节点本质上是双向链表，查找它的首部和尾部，时间复杂度O(1)。而如果此时最末尾的数据页满了，那创建个新的页就好。

如果主键不是自增的，比方说上次分配了id=7，这次分配了id=3，为了让新加入数据后B+树的叶子节点还能保持有序，它就需要往叶子结点的中间找，查找过程的时间复杂度是O(lgn)，如果这个页正好也满了，这时候就需要进行页分裂了。并且页分裂操作本身是需要加悲观锁的。总体看下来，==自增的主键遇到页分裂的可能性更少，因此性能也会更高==。

InnoDB和MyISAM的区别

InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。
MyISAM 不支持外键，而 InnoDB 支持外键。
MyISAM 不支持 MVCC，而 InnoDB 支持MVCC。
虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。
MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持,redo log。
InnoDB 的性能比 MyISAM 更强大。

何时使用myisam

多读场景
如果你的应用程序对查询性能要求较高，就要使用MYISAM了。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。

mysql是如何解决幻读问题的

快照读

在默认隔离级别RR下，select 语句默认是快照读

当前读

select 语句加锁是当前读， update 语句是当前读

当前读：select…lock in share mode，select…for update， update，delete，insert

幻读

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

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

解决

即使把所有的记录都加上锁，还是阻止不了新插入的记录，所以“幻读”问题要单独拿出来解决。没法依靠MVCC或者行锁机制来解决。这就引出“间隙锁”，是另外一种加锁机制。

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙也加上了间隙锁。这样就确保了无法再插入新的记录。

间隙锁和行锁合称next-key lock临键锁，每个next-key lock是前开后闭区间（间隙锁开区间，next-key lock前开后闭区间）：它的封锁范围，既包含索引记录，又包含索引区间。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

为了解决幻读问题可以采用读可提交隔离级别，间隙锁是在可重复读隔离级别下才会生效的。所以如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row，也就是说采用“RC隔离级别+日志格式binlog_format=row”组合。

总结

RR隔离级别下间隙锁才有效，RC隔离级别下没有间隙锁；
RR隔离级别下为了解决“幻读”问题：“快照读”依靠MVCC控制，“当前读”通过间隙锁解决；
间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间；
间隙锁的引入，可能会导致同样语句锁住更大的范围，影响并发度。

表结构优化

进行表结构优化的目的是为了提高数据库查询效率、减少数据库存储空间的使用、降低数据库的锁竞争等。

提高效率角度：
1. 使用缓存：将查询结果缓存到缓存中，以减少数据库的查询操作，提高查询效率。
2. 使用索引：合理的索引可以极大地提高查询效率。
3. 分库分表：有水平拆分和垂直拆分两种形式，
  可以按照时间、地区进行水平拆分，比如每个月一张表，每个地区一张表，通过业务层拆分拆卸请求
  可以将表中的频率较高的字段分成一张表，或者大文本字段分成一张表，提高查询的效率
4. 适当合并表或者适当冗余：合并多个表减少表的联接操作，从而提高查询效率。
减少存储空间的角度：
1. 合理定义数据类型，字段大小。比如区分开tinyint(1),int(4),bigint(8)，固定长度（或长度几乎相等）的字符串用char不用verchar(实际长度+1)
2. 使用数据压缩算法，将数据压缩存储，以减少存储空间的使用。

字段优化

尽量使用TINYINT、SMALLINT、MEDIUM INT 作为整数类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
使用枚举或整数代替字符串类型
尽量使用TIMESTAMP 而DATETIME
单表不要有太多字段，建议在20以内
避免使用 NULL字段，很难查询优化且占用额外索引空间
用整型来存IP

分页优化

对于有大数据量的mysql表来说，使用LIMIT分页存在很严重的性能问题。

一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。

优化：

# SQL代码1：平均用时6.6秒 
SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

# SQL代码2：平均用时0.6秒 
SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM  
`cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30

因为要取出所有字段内容，第一种需要跨越大量数据块并取出，而第二种基本通过直接根据索引字段定位后，才取出相应内容，效率自然大大提升。对limit的优化，不是直接使用limit，而是首先获取到offset的id，然后直接使用limit size来获取数据。

为什么说mysql数据库单表最大行数不能超过2000万

为什么说mysql数据库单表最大行数不能超过2000万_mysql表最大行数_CoreDump1024的博客-CSDN博客

IO

MySQL一次IO的最小单位是页（page），也可以理解为一次原子操作都是以page为单位的，默认大小16k。刚刚列出的所有物理文件结构上都是以Page构成的，只是page内部的结构不同。
mysql的IO是指数据库文件的读写，也就是检索数据和插入数据。

2kw

B+树叶子和非叶子结点的数据页都是16k，且数据结构一致，区别在于叶子节点放的是真实的行数据，而非叶子结点放的是主键和下一个页的地址。
B+树一般有两到三层，由于其高扇出，三层就能支持2kw以上的数据，且一次查询最多1~3次磁盘IO，性能也还行。
存储同样量级的数据，B树比B+树层级更高，因此磁盘IO也更多，所以B+树更适合成为mysql索引。
索引结构不会影响单表最大行数，2kw也只是推荐值，超过了这个值可能会导致B+树层级更高，影响查询性能。
单表最大值还受主键大小和磁盘大小限制。

行数超一亿就慢了吗？

上面假设单行数据用了1kb，所以一个数据页能放个15行数据。
如果我单行数据用不了这么多，比如只用了250byte。那么单个数据页能放60行数据。
那同样是三层B+树，单表支持的行数就是 (1280 ^ (3-1)) * 60 ≈ 1个亿。
你看我一个亿的数据，其实也就三层B+树，在这个B+树里要查到某行数据，最多也是三次磁盘IO。所以并不慢。

总结

MySQL 的表数据是以页的形式存放的，页在磁盘中不一定是连续的。
页的空间是 16K, 并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等。
在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。
索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

数据库的主键为什么是自增的

主键可以唯一标识这一行数据，从而保证在删除更新操作时，只是操作这一行数据。
索引需要，每个 InnoDB 表又有一个特殊的索引，即聚簇索引，用来存储行数据。通常，聚簇索引和主键同义。
针对B+树叶子节点，如果主键是自增的，那它产生的id每次都比前一次要大，所以每次都会将数据加在B+树尾部，B+树的叶子节点本质上是双向链表，查找它的首部和尾部，时间复杂度O(1)。而如果此时最末尾的数据页满了，那创建个新的页就好。

声明主键，InnoDB 会将主键作为聚簇索引。
未声明时，会在 UNIQUE 所有键列所在位置找到第一个索引，NOT NULL 并将其作为聚簇索引
未声明且找不到合适的 UNIQUE 索引，则内部生成一个隐藏的聚簇索引 GEN_CLUST_INDEX，这个隐藏的行 ID 是 6 字节且单调增加。

数据页大小是固定16k
数据页内，以及数据页之间，数据主键id都是从小到大排序的
由于数据页大小固定了是16k，当我们需要插入一条新的数据，数据页会被慢慢放满，当超过16k时，这个数据页就有可能会进行分裂。

总结

建表sql里主键边上的AUTO_INCREMENT，可以让主键自增，去掉它是可以的，但这就需要你在insert的时候自己设置主键的值。
建表sql里的 PRIMARY KEY 是用来声明主键的，如果去掉，那也能建表成功，但mysql内部会给你偷偷建一个 ROW_ID的隐藏列作为主键。
由于mysql使用B+树索引，叶子节点是从小到大排序的，如果使用自增id做主键，这样每次数据都加在B+树的最后，比起每次加在B+树中间的方式，加在最后可以有效减少页分裂的问题。
在分库分表的场景下，我们可以通过redis等第三方组件来获得严格自增的主键id。如果不想依赖redis，可以参考雪花算法进行魔改，既能保证数据趋势递增，也能很好的满足分库分表的动态扩容。
并不是所有数据库都建议使用自增id作为主键，比如tidb就推荐使用随机id，这样可以有效避免写热点的问题。而对于一些敏感数据，比如用户id，订单id等，如果使用自增id作为主键的话，外部通过抓包，很容易可以知道新进用户量，成单量这些信息，所以需要谨慎考虑是否继续使用自增主键。

如何保证自增

插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

MySQL中varchar最大长度是多少？

导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。

varchar最多能存储65535个字节的数据。varchar 的最大长度受限于最大行长度（max row size，65535bytes）。

字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766;
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。
若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。

列数限制

MySQL 对每个表有 4096 列的硬限制，但是对于给定的表，有效最大值可能会更少。确切的列限制取决于几个因素：

表的最大行大小限制了列的数量（可能还有大小），因为所有列的总长度不能超过该大小
个列的存储要求限制了给定最大行大小内的列数。某些数据类型的存储要求取决于存储引擎，存储格式和字符集等因素
存储引擎可能会施加其他限制表列计数的限制。例如， InnoDB每个表的限制为 1017 列
功能键部分被实现为隐藏的虚拟生成的存储列，因此表索引中的每个功能键部分都计入表的总列数限制。

数据库脑裂

脑裂(split-brain)：指在一个高可用(HA)系统中，当联系着的两个节点断开联系时，本来为一个整体的系统，分裂为两个独立节点，这时两个节点开始争抢共享资源，结果会导致系统混乱，数据损坏。

裂脑通常用于描述集群中的两个或多个节点彼此失去连接但随后继续彼此独立运行（包括获取逻辑或物理资源）的场景，错误假设其他进程（es ) 不再运作或使用上述资源。简单来说，“大脑分裂”意味着有 2 个或更多不同的节点集或“队列”，两个队列之间没有通信。

为什么禁止使用count(列名)

count(常量)：InnoDB会遍历整张表，但不取值，server层对返回的每一行放一个常量进去，包含值为NULL的行数，返回累加结果

count(字段)：返回SELECT语句检索的行中不为NULL的数量，和字段的类型无关

count(*)：统计所有的列，相当于行数，包含值为NULL的行数。并且count(*)是SQL92定义的标准统计行数的语法，mysql对于count(*)做了优化：MyISAM会直接把表的总行数单独记录返回给count(*)，InnoDB会选择最小的索引来降低成本。

分布式事务

InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的ACID要求又有了提高。另外，在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。

分布式事务使用两段式提交（two-phase commit）的方式。在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次的PREPARE操作，待收到所有节点的同意信息后，再进行COMMIT或是ROLLBACK操作。

主从复制

原理：Slave 会从Master 读取binlog 来进行数据同步。
作用：读写分离，数据备份，高可用性（性能、扩展），负载均衡
缺点：存在瞬时数据不一致问题（网络延时）

流程

MySQL 的主从复制是一个异步的复制过程（一般情况下感觉是实时的），数据将从一个 MySQL 数据库（Master）复制到另外一个 MySQL 数据库（Slave），在 Master 与 Slave 之间实现整个主从复制的过程是由三个线程参与完成的，其中有两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线程（ I/O 线程）在 Master 端。

Master 端：打开二进制日志（binlog ）记录功能 —— 记录下所有改变了数据库数据的语句，放进 Master 的 binlog 中；
Slave 端：开启一个 I/O 线程 —— 负责从 Master上拉取 binlog 内容，放进自己的中继日志（Relay log）中；
Slave 端：SQL 执行线程 —— 读取 Relay log，并顺序执行该日志中的 SQL 事件。

redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。
为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。prepare和commit阶段

使用两阶段提交后，写入binlog时发生异常也不会有影响，当mysql根据redolog恢复数据时，发现redolog还处于prepare阶段，并且没有对应的binlog日志，就会回滚该事务。

如何解决一致性问题

异步复制
半同步复制（一个从库收到了才返回）
组复制MGR（加了一层一致性协议层，判断从库响应的个数）

主库和从库出现比较严重的主从延迟如何处理

原因：

网络延迟：如果主从之间的网络传输速度慢，或者出现丢包、抖动等问题，那么就会影响 binlog 的传输效率，导致从库延迟。解决方法是优化网络环境，比如提升带宽、降低延迟、增加稳定性等。
从库的压力较大，从库承受了大量的请求。从库需要执行主库的所有写操作，同时还要响应读请求，如果读请求过多，会占用从库的 CPU、内存、网络等资源，影响从库的复制效率（也就是 T2-T1 和 T3-T2 的值会较大，和前一种情况类似）。解决方法是引入缓存（推荐）、使用一主多从的架构，将读请求分散到不同的从库，或者使用其他系统来提供查询的能力，比如将 binlog 接入到 Hadoop、Elasticsearch 等系统中。
执行大事务。因为主库上必须等事务执行完成才会写入 binlog，再传给备库。运行时间比较长，长时间未提交的事务就可以称为大事务。由于大事务执行时间长，并且从库上的大事务会比主库上的大事务花费更多的时间和资源，因此非常容易造成主从延迟。解决办法是避免大批量修改数据，尽量分批进行。类似的情况还有执行时间较长的慢 SQL ，实际项目遇到慢 SQL 应该进行优化。
从库机器性能比主库差：从库接收 binlog 并写入 relay log 以及执行 SQL 语句的速度会比较慢，进而导致延迟。解决方法是选择与主库一样规格或更高规格的机器作为从库，或者对从库进行性能优化，比如调整参数、增加缓存、使用 SSD 等。
从库太多：主库需要将 binlog 同步到所有的从库，如果从库数量太多，会增加同步的时间和开销（也就是 T2-T1 的值会比较大，但这里是因为主库同步压力大导致的）。解决方案是减少从库的数量，或者将从库分为不同的层级，让上层的从库再同步给下层的从库，减少主库的压力。
复制模式：MySQL 默认的复制是异步的，必然会存在延迟问题。全同步复制不存在延迟问题，但性能太差了。半同步复制是一种折中方案，相对于异步复制，半同步复制提高了数据的安全性，减少了主从延迟（还是有一定程度的延迟）。MySQL 5.5 开始，MySQL 以插件的形式支持 semi-sync 半同步复制。并且，MySQL 5.7 引入了 增强半同步复制 。

半同步复制、实时性操作强制走主库、并行复制。

半同步复制

「异步复制」：MySQL 默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理。这样就会有一个问题，一旦主库宕机，此时主库上已经提交的事务可能因为网络原因并没有传到从库上，如果此时执行故障转移，强行将从提升为主，可能导致新主上的数据不完整。

「全同步复制」：指当主库执行完一个事务，并且所有的从库都执行了该事务，主库才提交事务并返回结果给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

「半同步复制」：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库接收到并写到 Relay Log 文件即可，主库不需要等待所有从库给主库返回 ACK。主库收到这个 ACK 以后，才能给客户端返回 “事务完成” 的确认。

实时性操作强制走主库

如果某些操作对数据的实时性要求比较苛刻，需要反映实时最新的数据，比如说涉及金钱的金融类系统、在线实时系统、又或者是写入之后马上又读的业务，这时我们就得放弃读写分离，让此类的读请求也走主库，这就不存延迟问题了。

为什么不建议使用外键

外键(FK) 是用于在两个表中的数据之间建立和加强链接的一列或多列的组合，可控制可在外键表中存储的数据。在外键引用中，当包含一个表的主键值的一个或多个列被另一个表中的一个或多个列引用时，就在这两个表之间创建了链接。这个列就成为第二个表的外键。

首先我们明确一点，外键约束是一种约束，这个约束的存在，会保证表间数据的关系“始终完整”。因此，外键约束的存在，并非全然没有优点。

保证数据的完整性和一致性
级联操作方便
将数据完整性判断托付给了数据库完成，减少了程序的代码量
RESTRICT 外键会在更新和删除关系表中的数据时对外键约束的合法性进行检查，保证外键不会引用到不存在的记录；
CASCADE 外键会在更新和删除关系表中的数据时触发对关联记录的更新和删除，在数据量较大的数据库中可能会有数量级的放大效果；

但是：每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦,测试数据极为不方便。

慢SQL

定位

首先GORM可以添加一个logger来打印慢SQL错误。
mysql也可以打开慢查询日志
explain命令可以查mysql具体执行语句时的顺序

一般通过“慢日志明细”功能进行分析，排在Top的SQL需要重点关注。根据执行次数、执行时间、返回行数、解析行数进行分析

set profiling = on通过show profiles查看性能记录，可以看到整个执行过程每个状态的耗时情况。然后定位到具体是哪个状态最耗时，然后针对性的排查原因。

慢查询日志

慢查询日志记录了执行时间超过 long_query_time（默认是 10s，通常设置为1s）的所有查询语句，在解决 SQL 慢查询（SQL 执行时间过长）问题的时候经常会用到。
找到慢 SQL 是优化 SQL 语句性能的第一步，然后再用 EXPLAIN 命令可以对慢 SQL 进行分析，获取执行计划的相关信息。

可以通过 show variables like "slow_query_log";命令来查看慢查询日志是否开启，默认是关闭的。可以通过 SET GLOBAL slow_query_log=ON 命令将其开启。

long_query_time 参数定义了一个查询消耗多长时间才可以被定义为慢查询，默认是 10s，通过 SHOW VARIABLES LIKE '%long_query_time%'命令即可查看

可能的原因

SQL解析行数比返回行数多得多：很可能是SQL没有合理使用索引导致。
SQL执行次数比较多，而解析行数和返回行数都比较少：一般是并发太大导致SQL堆积，而使整个系统响应变慢
SQL解析行数和返回行数都比较多：一般SQL本身没有太大优化空间，需要业务层面进行优化。
业务流量太大导致系统响应变慢：写流量比较大，可考虑分库分表，利用分片扩展特性，提升整个集群的写能力和存储容量,读流量比较大，可考虑使用代理实例，利用代理实例的读写分离和负载均衡特性，分摊主库的读流量压力。

解决

首先查一下查询是否用到了索引，是否出现索引失效的情况

lol， +-*/，not，null，函数，版本
不要使用select *。
排序请尽量使用升序。
or的查询尽量用union代替（Innodb）。
复合索引高选择性的字段排在前面。
order by/group by字段包括在索引当中减少排序，效率会更高。
注意避免冗余索引，以及长期未使用的索引

用了索引还是慢什么原因，如何解决

避免使用select *
索引失效
查询不符合最左前缀匹配
查询字段特别多或进行了多表join：优化查询条件（用小表去驱动大表）
索引区分度不高：优化查询条件和索引
数据库自身的锁等待，cpu占用高，网络问题：偶发的，结合日志业务改
优化器选错了索引：force index
数据量太大：分库分表

explain字段[[#explain]]

Explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。

==关心type，和extra==

type：查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

extra里Using index 使用了覆盖索引；Using index condition索引下推

InnoDB三大特性

自适应哈希、buffer pool、两次写

自适应哈希索引

自适应即我们不需要自己处理，当lnnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点，就会给其建立一个hash索引

InnodB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index,AHI)。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引，只是对热点页建立hash索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

hash索引底层的数据结构是散列表(Hash表)，其数据特点就是比较适合在内存中使用，自适应Hash索引存在于InnoDB架构中的缓存中(不存在于磁盘架构中)，见下面的InnoDB架构图。

自适应hash索引==只适合搜索等值的查询==，如select * from table where index_col=’xxx，而对于其他查找类型，如范围查找，是不能使用的;

优点

无序，没有树高；
降低对二级索引树的频繁访问资源，查询消耗 O(1)；
自适应，不用人为创建。

缺点

自适应hash索引会占用innodb buffer pool；
自适应hash索引只适合搜索等值的查询，而对于其他查找类型，如范围查找、模糊查询，是不能使用的；
自适应哈希索引无法对order by进行优化；
只有在某些负载情况下，通过哈希索引查找带来的性能提升才能远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销，此时自适应hash索引才有比较大的意义，可以降低逻辑读。

Buffer Pool

Bufer Pool: 缓冲池，简称BP。其作用是用来缓存表数据与索引数据，减少磁盘I0操作，提升效率。

Buffer Pool由缓存数据页(Page)和对缓存数据页进行描述的控制块组成。
控制块中存储着对应缓存页的所属的表空间、数据页的编号、以及对应缓存页在Buffer Pool中的地址等信息

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

如何判断一个页是否在BufferPool中？

MySQI中有一个哈希表数据结构，它使用表空间号+数据页号，作为一个key，然后缓冲页对应的控制块作为value。

当需要访问某个页的数据时，先从哈希表中根据表空间号+页号看看是否存在对应的缓冲页。

如果有，则直接使用。
如果没有，就从free链表中选出一个空闲的缓冲页，然后把磁盘中对应的页加载到该缓冲页的位置

Buffer pool的单位，frame的概念？

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等。

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。

控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页

如何管理脏页

设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。

那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。==有了 Flush 链表后，后台线程就可以遍历 Flush 链表，将脏页写入到磁盘。==

两次写（double write）

提高innodb的可靠性，用来解决部分写失败(partial page write页断裂)。

redolog中存的是物理页的修改：如偏移量600，写’xxxx’记录。

脏刷时宕机：磁盘文件不完整因此redolog失效，从double write buffer里恢复数据
写double write buffer时宕机：直接从redolog恢复数据

写缓冲 Change Buffer

非聚集索引也是一颗B+树，只是叶子节点存的是聚集索引的主键和name 的值。因为不能保证name列的数据是顺序的，所以非聚集索引这棵树的插入必然也不是顺序的了。

可以看出非聚集索引插入的离散性导致了插入性能的下降，因此InnoDB引擎设计了 Insert Buffer来提高插入性能。

Insert Buffer 就是用于提升非聚集索引页的插入性能的，其数据结构类似于数据页的一个B+树，物理存储在共享表空间ibdata1中。

首先对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中。
若在，则直接插入；若不在，则先放入到一个Change Buffer对象中。
给外部的感觉好像是树已经插入非聚集的索引的叶子节点，而其实是存放在其他位置了

以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，通常会将多个插入操作一起进行merge，这就大大的提升了非聚集索引的插入性能。

Double write解决了什么问题

当数据库正在从内存想磁盘写一个数据页是，数据库宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

因为存储引擎缓冲池内的数据页大小默认为16KB，而文件系统一页大小为4KB，所以在进行刷盘操作时，就有可能发生如下场景：数据库准备刷新脏页时，需要四次IO才能将16KB的数据页刷入磁盘。但当执行完第二次IO时，数据库发生意外宕机，导致此时才刷了2个文件系统里的页，这种情况被称为写失效（partial page write）。此时重启后，磁盘上就是不完整的数据页，就算使用redo log也是无法进行恢复的。

redo log无法恢复数据页损坏的问题，恢复必须是数据页正常并且redo log正常。
这里要知道一点，redo log中记录的是对页的物理操作，如偏移量600，写’xxxx’记录。
如果这个页本身已经发生了损坏，再对其进行重做是没有意义的

过程

其实就是在重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write。

doublewrite由两部分组成，一部分为内存中的doublewrite buffer，其大小为2MB，另一部分是磁盘上共享表空间(ibdata x)中连续的128个页，即2个区(extent)，大小也是2M。

当一系列机制触发数据缓冲池中的脏页刷新时，并不直接写入磁盘数据文件中，而是先拷贝至内存中的doublewrite buffer中；
接着从两次写缓冲区分两次写入磁盘共享表空间中(连续存储，顺序写，性能很高)，每次写1MB；然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题
待第二步完成后，再将doublewrite buffer中的脏页数据写入实际的各个表空间文件(离散写)；(脏页数据固化后，即进行标记对应doublewrite数据可覆盖)

doublewrite的崩溃恢复

如果操作系统在将页写入磁盘的过程中发生崩溃，在恢复过程中，innodb存储引擎可以从共享表空间的doublewrite中找到该页的一个最近的副本，将其复制到表空间文件，再应用redo log，就完成了恢复过程。

因为有副本所以也不担心表空间中数据页是否损坏。

存在问题

Double write buffer 它是在物理文件上的一个buffer, 其实也就是file，所以它会导致系统有更多的fsync操作，而因为硬盘的fsync性能问题，所以也会影响到数据库的整体性能。

Double write页是连续的，因此这个过程是顺序写的，开销并不是很大。

在完成Double write页的写入后，再将Double write buffer中的页写入各个数据文件中，此时的写入则是离散的

为什么log write不需要doublewrite的支持？

因为redolog写入的单位就是512字节，也就是磁盘IO的最小单位，所以无所谓数据损坏。

总结

当commit 一个修改语句时，如果redo log有空闲区域，直接写redo log，如果redo log没有空闲区域，那么需要把被覆盖的redo log对应的数据页刷新到data file 中，最后改pool buffer中的记录
innodb的redo log 不会记录完整的一页数据，因为这样日志太大，它只会记录那次（sequence）如何操作了（update,insert）哪页(page)的哪行(row)
因为数据库使用的页（page，默认16KB）大小和操作系统对磁盘的操作页（page，默认4KB）不一样，当提交了一个页需要刷新到磁盘，会有多次IO，此时刷了前面的8k时异常发生宕机。在系统恢复正常后，如果没有double write机制，此时数据库磁盘内的数据页已损坏，无法使用redo log进行恢复。
如果有double write buffer，会检查double writer的数据的完整性，如果不完整直接丢弃double write buffer内容，重新执行那条redo log，如果double write buffer的数据是完整的，用double writer buffer的数据更新该数据页，跳过该redo log。

第一步，对数据页数据进行更新
第二步，向redo log buffer中记录redo log
第三步，将缓存中的redo log写入磁盘
第四步，将脏页复制到double write buffer中
第五步，将数据页写入到共享表空间（维护的是128个连续页，最小16KB）
第六步，马上调用fsync函数同步磁盘
第七步，当第六步执行一半时发生宕机，执行恢复操作，InnoDB存储引擎从共享表空间中doublewrite中找到该页的一个副本，将其复制到表空间文件。
如果在执行第五步时，有些数据页还没写入共享表空间就宕机了，那么此时磁盘中就丢失了该数据页，这时就需要靠redo log来恢复数据了。
第八步，重启服务，根据redo log文件向缓存池中加载数据页，以一个数据页LSN=1000为例子，该数据页时更新之前的，在redo log中该数据页LSN=1100。
第九步，比较redo log与数据页的LSN大小 redo log lsn > page lsn，需要更新数据页，更新完成后该数据页为脏页。
此时重复第四步，将脏页复制到double write buffer中
继续重复后面所有步骤。

100000条数据怎么插入比较快

先说第一种方案，就是用 for 循环循环插入：

这种方案的优势在于，JDBC 中的 PreparedStatement 有预编译功能，预编译之后会缓存起来，后面的 SQL 执行会比较快并且 JDBC 可以开启批处理，这个批处理执行非常给力。
劣势在于，很多时候我们的 SQL 服务器和应用服务器可能并不是同一台，所以必须要考虑网络 IO，如果网络 IO 比较费时间的话，那么可能会拖慢 SQL 执行的速度。

再来说第二种方案，就是生成一条 SQL 插入：

这种方案的优势在于只有一次网络 IO，即使分片处理也只是数次网络 IO，所以这种方案不会在网络 IO 上花费太多时间。
当然这种方案有好几个劣势，一是 SQL 太长了，甚至可能需要分片后批量处理；二是无法充分发挥 PreparedStatement 预编译的优势，SQL 要重新解析且无法复用；三是最终生成的 SQL 太长了，数据库管理器解析这么长的 SQL 也需要时间。

所以我们最终要考虑的就是我们在网络 IO 上花费的时间，是否超过了 SQL 插入的时间？这是我们要考虑的核心问题。

从程序层面上看：

使用事务会比较快一些。
多连接插入会快很多，当读写成为瓶颈的时候，效果就不太明显。
一次插入多条数据也会快很多。
高并发大量插入请求，mysql服务的应对措施是宕机，而不是拒绝请求，mysql在高并发场景，如果承受不住会宕机，这点在设计上需要注意。

2、数据库插入优化基础
1）插入无索引表会比插入有索引的表快，毕竟建立索引总是要增加一些额外操作
2）插入小表比插入大表快，业务一般插入速度是以条数计算，大表一条记录比较大，需要IO的时间比较长。
3）多个连接一起插入会比单连接快，因为mysql不是单线程。
4）日志缓存增大可以加快插入速度，因为减少了IO访问次数。
5）一次插入多条数据可以加快插入速度。

having和where的区别？都能实现同样的过滤结果？

having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合

HAVING子句可以让我们直接筛选成组后的各组数据，也可以在聚合后对组记录进行筛选，而WHERE子句在聚合前先筛选记录，也就是说作用在GROUP BY 子句和HAVING子句前。

explain

当Explain 与 SQL语句一起使用时，MySQL 会显示来自优化器关于SQL执行的信息。也就是说，MySQL解释了它将如何处理该语句，包括如何连接表以及什么顺序连接表等。

表的加载顺序
sql 的查询类型
可能用到哪些索引，哪些索引又被实际使用
表与表之间的引用关系
一个表中有多少行被优化器查询

具体字段

id

id： ：表示查询中执行select子句或者操作表的顺序，**id的值越大，代表优先级越高，越先执行**。

select_type

表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。

SIMPLE：表示最简单的 select 查询语句，也就是在查询中不包含子查询或者 union交并差集等操作。
PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
SUBQUERY：当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY 。
DERIVED：表示包含在from子句中的子查询的select，在我们的 from 列表中包含的子查询会被标记为derived 。
UNION：如果union后边又出现的select 语句，则会被标记为union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived。
UNION RESULT：代表从union的临时表中读取数据，而table列的<union1,4>表示用第一个和第四个select的结果进行union操作。

table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表

partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

type

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。
const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。
eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。
ref：区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。
ref_or_null：这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
unique_subquery：替换下面的 IN子查询，子查询返回不重复的集合。
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。只有对设置了索引的字段，做范围检索 type 才是 range。
index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。
ALL：将遍历全表以找到匹配的行，性能最差。

possible_keys

表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。具体请参考上边的例子。

key

key：区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL。具体请参考上边的例子。

当 type 为 index_merge 时，可能会显示多个索引。

key_len

key_len：表示查询用到的索引长度（字节数），原则上长度越短越好。

单列索引，那么需要将整个索引长度算进去；
多列索引，不是所有列都能用到，需要计算查询中实际用到的列。

ref

ref：常见的有：const，func，null，字段名。

当使用常量等值查询，显示const，
当关联查询时，会显示相应关联表的关联字段
如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
其他情况null

rows

rows：以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。

这是评估SQL 性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。

filtered

filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

在MySQL.5.7版本以前想要显示filtered需要使用explain extended命令。MySQL.5.7后，默认explain直接显示partitions和filtered的信息。

Extra

Extra ：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。

Using index：我们在相应的 select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。
Using where：查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。
Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。
Using filesort：表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的。
Using join buffer：在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。
Impossible where：表示在我们用不太正确的where语句，导致没有符合条件的行。
No tables used：我们的查询语句中没有FROM子句，或者有 FROM DUAL子句。

mysql删除数据是真的删除了吗

在内部，InnoDB 存储引擎为每行数据添加了三个隐藏字段：

DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。==此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除==
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

在 MySQL 中，当你执行 DELETE 命令删除一条数据时，默认情况下并不会真正地删除数据，而是将其标记为已删除。这是由于 MySQL 使用了一种称为 MVCC（多版本并发控制）的机制来处理并发访问和事务的一致性。

在 MVCC 中，每条记录都会包含一个被称为版本号或时间戳的字段。当你执行 DELETE 命令时，MySQL 会为该记录创建一个新的版本，并将其标记为已删除。这样，已删除的数据仍然存储在磁盘上，但对于普通查询操作，已删除的数据将被隐藏。这种方式称为逻辑删除。

逻辑删除的优点是可以轻松地进行数据恢复和回滚操作。如果有需要，你可以通过特定的查询条件找回已删除的数据。另外，逻辑删除可以更好地保护数据的完整性，并提供审计功能，因为你可以追踪删除操作的历史记录。

然而，逻辑删除也存在一些缺点。首先，已删除的数据仍然占用存储空间，特别是对于大型表来说，可能会导致存储资源的浪费。其次，当进行范围查询时，已删除的数据可能会干扰结果的正确性，因为它们仍然存在于表中。

如果你希望完全删除数据，可以使用 TRUNCATE TABLE 命令或执行物理删除（物理删除是指直接从磁盘上删除数据文件）。但在执行物理删除之前，请务必确保你不再需要这些数据，并且已经备份了必要的数据。此外，物理删除可能会导致性能开销，因为它需要更多的磁盘操作和资源消耗。