MySql相关问题1

有了关系型数据库，为什么还需要NOSQL? #

nosql数据库无需提前设计表结构，数据可以根据需要自由存储和组织，相较于关系型数据库，nosql高效灵活，非常适合复杂化、高效化、高并发的场景中。

  • 关系型数据库

    • 数据以行和列的方式存储

    • 采用结构化查询语言来对数据进行查询

    • 强调ACID规则：原子性atomicity、一致性consistency、隔离性isolation、持久性durability

    • 强调数据一致性，因此牺牲了读写性能

    • 通常存储在硬盘中

      mysql也可以基于内存，即内存表技术。它运行将数据和索引存储在内存中，从而提高查询和修改效率。

      如何创建？

      与创建普通表一样，使用CREATE TABLE，需要将存储引擎设置为：ENGINE=MEMORY

  • 非关系型数据库

    • 以键值对来存储
    • 没有固定的要求和限制
    • 事务一般无法回滚（部分可以，MongoDB)
    • 需要通过key来查询
    • 基于内存存储（MongoDB基于磁盘）
    • 不支持范围查询

数据库存储引擎 #

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可以控制访问权限并快速处理事物，从而满足业务需求。

  • 查看当前使用什么引擎

    show engines

  • 查看当前默认存储引擎

    show variables like %storage_engine%

  • 查看某表用什么引擎

    show create table 表名

    结果参数中有

  • 如何指定引擎

    • 创建表时：create语句后面加上engine=“”
    • 修改表时：alter table 表名 engine=""

InnoDB和MyISM的区别 #

InnoDB和MyISM的区别

mysql存储引擎是基于表的吗 #

是，不是基于数据库

联合索引 和 mysql 调优的关系 #

mysql 调优的一个核心动作，就是通过联合索引实现索引覆盖。

在MySQL中，合理使用联合索引可以提高查询效率，通过 联合索引 实现 索引覆盖 ，常常需要注意一些技巧：

  • 选择合适的列：联合索引的列顺序非常重要。应该优先选择最频繁用于查询条件的列，以提高索引效率。其次考虑选择性高的列，这样可以过滤出更少的数据。
  • 避免冗余列：联合索引的列应该尽量避免包含冗余列，即多个索引的前缀相同。这样会增加索引的维护成本，并占用更多的存储空间。
  • 避免过度索引：不要为每个查询都创建一个新的联合索引。应该根据实际情况，分析那些查询是最频繁的，然后创建针对这些查询的索引。
  • 覆盖索引：如果查询的列都包含在联合索引中，并且不需要访问表的其他列，那么MySql可以直接使用索引来执行查询，不必访问表，这种索引称为覆盖索引，可以提高查询性能。
  • 使用EXPLAIN进行查询计划分析： 使用MySQL的EXPLAIN语句可以查看MySQL执行查询的执行计划，以便优化查询语句和索引的使用。
  • 定期优化索引： 随着数据库的使用，索引的效率可能会下降，因此需要定期进行索引的优化和重建，以保持查询性能的稳定性。
  • 分析查询日志： 监控数据库的查询日志，分析哪些查询是最频繁的，以及它们的查询模式，可以帮助确定需要创建的联合索引。
  • 避免过度索引更新： 避免频繁地更新索引列，因为每次更新索引都会增加数据库的负载和IO操作。

综上所述，联合索引是mysql 调优的一个核心动作， 通过 联合索引进行mysql 调优时，需要综合考虑列的选择、索引的覆盖、查询的频率和模式等因素，以提高MySQL数据库的查询性能。

MySQL索引机制 #

数据库索引，官方定义如下

在关系型数据库中，索引是一种单独的、物理的数据，对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合，以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。

通俗的理解为

在关系型数据库中，索引是一种用来帮助快速检索目标数据的存储结构。

索引的创建 #

MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。

  1. 使用CREATE语句创建
 CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
  1. 使用ALTER语句创建
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
  1. 建表时DDL语句中创建
CREATE TABLE tableName(  
     columnName1 INT(8) NOT NULL,   
     columnName2 ....,
     .....,
     INDEX [indexName] (columnName(length))  
   );

索引的查询 # 

  SHOW INDEX from tableName;

索引的删除 # 

  ALTER  TABLE  table_name   DROP  INDEX  index_name;
  DROP   INDEX  index_name   ON  table_name;

MySQL联合索引 #

什么是联合索引 #

联合索引（Composite Index）是一种索引类型，它由多个列组成。

MySQL的联合索引（也称为复合索引）是建立在多个字段上的索引。这种索引类型允许数据库在查询时同时考虑多个列的值，从而提高查询效率和性能。

  • 联合索引：也称复合索引，就是建立在多个字段上的索引。联合索引的数据结构依然是 B+ Tree。
  • 当使用(col1, col2, col3)创建一个联合索引时，创建的只是一颗B+ Tree，在这棵树中，会先按照最左的字段col1排序，在col1相同时再按照col2排序，col2相同时再按照col3排序。

联合索引存储结构 #

联合索引是一种特殊类型的索引，它包含两个或更多列。

在MySQL中，联合索引的数据结构通常是B+Tree，这与单列索引使用的数据结构相同。

当创建联合索引时，需要注意列的顺序，因为这将影响到索引的使用方式。

如下图所示，表的数据如右图，ID 为主键，创建的联合索引为 (a，b)，注意联合索引顺序，下图是模拟的联合索引的 B+ Tree 存储结构

最左前缀匹配原则 #

联合索引还是一颗B+树，只不过联合索引的健 数量不是一个，而是多个。

构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

假如创建一个（a,b)的联合索引，联合索引B+ Tree结构如下：

结合上述联合索引B+ Tree结构，可以得出如下结论：

1.a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。

所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

2.当a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。

所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

例如a = 1 and b = 2 ，a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

最左匹配原则：

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

下面我们以建立联合索引（a,b,c）为例，进行详细说明

1 全值匹配查询时 #

下述SQL会用到索引，因为where子句中，几个搜索条件顺序调换不影响查询结果，因为MySQL中有查询优化器，会自动优化查询顺序。

select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1'

2 匹配左边的列时 #

下述SQL，都从最左边开始连续匹配，用到了索引。

select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'

下述SQL中，没有从最左边开始，最后查询没有用到索引，用的是全表扫描。

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3'

下述SQL中，如果不连续时，只用到了a列的索引，b列和c列都没有用到

select * from table_name where  a = '1' and c = '3'

3 匹配列前缀 #

如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相通，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

如果a是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了

select * from table_name where  a like 'As%';   //前缀都是排好序的，走索引查询
select * from table_name where  a like '%As';   //全表查询
select * from table_name where  a like '%As%';  //全表查询

4 匹配范围值 #

下述SQL，可以对最左边的列进行范围查询

select * from table_name where  a > 1 and a < 3

多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引。

在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤。

select * from table_name where  a > 1 and a < 3 and b > 1;

5 精确匹配某一列并范围匹配另外一列 #

如果左边的列是精确查找的，右边的列可以进行范围查找，如下SQL中，a=1的情况下b是有序的，进行范围查找走的是联合索引

select * from table_name where  a = 1 and b > 3;

6 排序 #

一般情况下，我们只能把记录加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记录进行排序，有时候查询的结果集太大不能在内存中进行排序的话，还可能暂时借助磁盘空间存放中间结果，排序操作完成后再把排好序的结果返回客户端。

Mysql中把这种再内存中或磁盘上进行排序的方式统称为文件排序。文件排序非常慢，但如果order子句用到了索引列，就有可能省去文件排序的步骤

select * from table_name order by b,c,a limit 10;

因为b+树索引本身就是按照上述规则排序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了，order by的子句后面的顺序也必须按照索引列的顺序给出，比如下SQL：

select * from table_name order by b,c,a limit 10;

在以下SQL中颠倒顺序，没有用到索引

select * from table_name order by a limit 10;
select * from table_name order by a,b limit 10;

以下SQL中会用到部分索引，联合索引左边列为常量，后边的列排序可以用到索引

select * from table_name where a =1 order by b,c limit 10;

为什么要遵循最左前缀匹配？ #

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

如下，我们以age，name两个字段建立一个联合索引，非叶子节点中记录age，name两个字段的值，而叶子节点中记录的是age，name两个字段值及主键Id的值，在MySQL中B+ Tree 索引结构如下：

在上述联合索引存储数据过程中，首先会按照age排序，当age相同时则按照name排序。

  1. 结合上述索引结构，可以看出联合索引底层也是一颗B+Tree，在联合索引中构造B+Tree的时候，会先以最左边的key进行排序，如果左边的key相同时，则再依次按照右边的key进行排序。
  2. 所以在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。

一定要遵循最左前缀匹配吗？ #

最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。

因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

我们来看如下例子，理解一下索引跳跃式扫描如何实现的。

比如此时通过(A、B、C)三个列建立了一个联合索引，此时有如下一条SQL：

SELECT * FROM table_name WHERE B = `xxx` AND C = `xxx`;

按正常情况来看，这条SQL既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的。

但这条SQL中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏啊？

因此MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，比如上述这条SQL则会重构成如下情况：

SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";

通过MySQL优化器处理后，虽然你没用第一个字段，但我（优化器）给你加上去，今天这个联合索引你就得用，不用也得给我用。

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发等等，总之有很多限制条件，具体的可以参考《MySQL官网8.0-跳跃扫描》。

最后，可以通过通过如下命令来选择开启或关闭跳跃式扫描机制。

set @@optimizer_switch = 'skip_scan=off|on';

联合索引注意事项 #

  1. 选择合适的列：应选择那些经常用于查询条件的列来创建联合索引。

  2. 考虑列的顺序：在创建联合索引时，应该根据实际的查询需求来安排列的顺序，以确保索引能够被有效利用。

  3. 避免过长的索引：虽然联合索引可以包含多个列，但过长的索引可能会增加维护成本，并且在某些情况下可能不会带来预期的性能提升。

  4. 避免范围查询：如果查询中包含范围操作符（如BETWEEN, <, >, LIKE），则MySQL可能无法有效地利用联合索引，因为它需要检查索引中的每个范围边界。

  5. 考虑索引的区分度：如果某个列的值重复率很高，那么该列作为联合索引的一部分可能不会提供太大的性能提升，因为它不能有效地区分不同的记录。

    联合索引作为数据库中的一种索引类型，它由多个列组成，在使用时，一般遵循最左匹配原则，以加速数据库查询操作。

char和varchar的区别 #

char是一种定长的数据类型，它的长度固定且在存储时会自动在结尾添加空格来将字符串填满指定长度。char的范围是0-255

  • 优点：定长字符串类型，减少内存碎片，并且无需额外的磁盘空间去存储长度信息。
  • 缺点：丢失末尾空格信息。“abc “,“abc “,默认补空格

varchar是一种可变长度的数据类型，它只会存储实际字符串内容，不会填充空格。varchar的长度范围是0-2^16

  • 优点：变长字符串，兼容性好。
  • 缺点：可能会产生内存碎片，还需要额外1到2字节存储长度信息；update语句可能会导致页分裂。
  • 存储身份证、长度固定的订单号等

InnoDB页分裂，页合并 #

数据页是InnoDB存储引擎中用于存储数据的基本单位，通常为16KB。B+树的每个节点都对应着一个数据页，包括根节点、非叶子节点和叶子节点。B+树通过节点之间的指针连接了不同层级的数据页，从而构建了一个有序的索引结构。

页分裂是指将该页面中的一部分索引记录移动到一个新页面中，从而为新记录腾出空间。这样可以保持B+树的平衡和性能。

那么，当我们向Innodb中添加数据的时候，如果索引是随机无序的，那么就导致页分裂。而且分裂这个动作还可能会引起连锁反应，从叶子节点沿着树结构一路分裂到根节点。

当索引页面中的索引记录删除后，页面可能会变得稀疏。为了节省空间和提升性能，可能会发生页合并操作。

页合并是指将两个相邻索引页面合并成一个大的页面，减少B+树层级，提高查询性能。

页合并危害 #

页分裂和页合并涉及大量的数据移动和重组操作。频繁进行这些操作会增加数据库的I/O负担和CPU消耗，影响数据库整体性能。

分裂和合并可能导致B+树索引结构频繁调整，这个过程也会影响插入及删除操作的性能。

页分裂和合并可能会导致磁盘上存在较多的空间碎片，新分出的一个页一般会有很多空闲空间，使得数据库表占用更多的磁盘空间，而导致浪费。

如何避免页分裂 #

使用varchar或uuid作为主键，都会导致页分裂

尽量使用自增id作为索引，尤其是主键索引

如果要大量插入数据，尽量选择批量插入，而不是逐条插入。

频繁删除操作可能导致页合并，一般建议使用逻辑删除，而不是物理删除。

逻辑删除：即在记录中添加一个标记，来代表此记录被删除，而不是真的从数据库删除。

还可以根据实际情况，适当调整InnoDB的配置参数，如页大小、填充因子、叶子页合并阈值等，优化数据库性能。

填充因子：用于控制索引页（B+树节点）物理存储密度的参数，它决定了每个数据页在初始插入数据时的填充比例。

填充因子越低，页中预留空间越多，减少后续插入导致的页分裂。

填充因子越高，页中数据越紧密，存储效率更高，索引更紧凑，可能提升缓存命中率。

MySQL为什么会有存储碎片？有什么危害？ #

这种情况通常发生在频繁执行插入、删除和更新操作的数据库中。这些操作会导致表中的数据页部分空间未被有效利用或数据在物理存储上的排列不连续，从而形成碎片。

  • insert

    如果使用uuid，会导致页分裂现象，页分裂导致数据分散在磁盘多个不同的位置。新创建的页可能在物理存储上与原始页相距甚远，这些数据在物理上是不连续的，就会存在碎片。

  • update

    如果更新操作导致数据行大小增加，而原始位置没有足够的空间容纳更多的行，这些行数据会被移动到数据文件的其他部分，留下空闲位置，导致碎片

  • delete

    innoDB，只给数据做了删除标记，但空间不会立即释放。这导致数据页中可能存在大量未使用空间，增加了数据的分散程度，这就是碎片

  • varchar、text这种可变长度字段存储的时候

碎片的危害 #

当表的碎片增多时，数据在物理磁盘上的存储变得不连续，将导致数据库在查询时需要更多的磁盘I/O操作，从而降低查询效率。

碎片导致数据库实际占用的存储空间比数据实际需要的空间大，这会大大浪费磁盘空间，还可能影响缓存效率。

碎片会增加备份文件的大小，同时也会使得备份和恢复过程变得更慢。

如何避免碎片 #

  • 使用自增ID，而不是uuid
  • 对固定长度的字符串，用char代替varchar
  • 避免在高度易变的列上创建索引，这样会频繁触发页分裂
  • 使用OPTIMIZE TABLE命令可以重新组织表和索引的物理存储，可以有效减少碎片，优化表的存储和访问速度。

OPTIMIZE TABLE是MySQL中的一个维护命令，用于重建表数据、整理碎片、回收空间，从而提升表性能和存储效率。

如何清理碎片 #

  • OPTIMIZE TABLE

    • MyISAM会锁表
    • InnoDB，会对表进行共享锁定，只能读，不能写

    索引多、数据量大、系统资源（CPU、磁盘I/O）不足会增加操作时间

如何查看碎片 #

  • show table status 或者 INFORAMTION_SCHEMA

    Data_free字段，表示未使用空间，通常表示表中的碎片空间。

什么是数据库范式，为什么要反范式？ #

1NF：属性不可再分 #

2NF：要有主键，其他字段都依赖主键 #

在一个成绩表中，我们使用“学号”和“课程号”作为联合主键来唯一确定一条成绩记录。

学号 课程号 姓名 课程名称 成绩
1001 C001 张三 数据库原理 90
1001 C002 张三 计算机网络 85
1002 C001 李四 数据库原理 92

在这个表中，“姓名”只依赖于“学号”，而“课程名称”只依赖于“课程号”。它们都只依赖于联合主键的一部分，这就产生了部分依赖。这种设计会导致数据冗余（“张三”和“数据库原理”被多次存储）和更新异常（如果课程名称变更，需要修改多条记录）。

修改如下：

学生表 (Student):

学号 (主键) 姓名
1001 张三
1002 李四

课程表 (Course):

课程号 (主键) 课程名称
C001 数据库原理
C002 计算机网络

成绩表 (Score):

学号 (外键) 课程号 (外键) 成绩
1001 C001 90
1001 C002 85
1002 C001 92

通过拆分，成绩表中的非主键字段“成绩”完全依赖于联合主键（“学号”和“课程号”）。学生姓名和课程名称不再冗余存储，数据一致性也得到了更好的保证。

3NF：非主键外的所有字段都互不依赖 #

让我们在学生表中加入“院系”和“院系主任”两个字段：

学号 (主键) 姓名 院系 院系主任
1001 张三 计算机科学与技术 王老师
1002 李四 计算机科学与技术 王老师
1003 王五 软件工程 赵老师

在这个表中，“院系主任”依赖于“院系”，而“院系”依赖于“学号”。这样就形成了传递依赖（学号 -> 院系 -> 院系主任）。这种设计同样会带来数据冗余和更新异常。如果某院系的系主任发生变动，就需要更新该院系所有学生的记录。

修改如下：

学生表 (Student):

学号 (主键) 姓名 院系ID (外键)
1001 张三 D01
1002 李四 D01
1003 王五 D02

院系表 (Department):

院系ID (主键) 院系名称 院系主任
D01 计算机科学与技术 王老师
D02 软件工程 赵老师

通过这样的拆分，学生表中的非主键字段只直接依赖于主键“学号”。院系信息被独立存储，消除了传递依赖，进一步减少了数据冗余，提高了数据的维护性。

我们在做表结构设计的时候，完全遵守数据库三范式，确实可以避免一些写时异常，提升写入性能，但是同时也会丢失一些读取性能。

因为在遵守范式的数据库设计中，表中不能有任何冗余字段，这就使得查询的时候会经常有多表关联查询，这无疑是比较耗时的。

反范式 #

是一种针对遵从设计范式的数据库的性能优化策略；反范式不等于非范式化，反范式化一定发生在满足范式设计的基础上。先遵守所有规定，再进行局部调整。

比如在表中增加一些冗余字段，方便数据查询，不需要做表join，本质上就是用空间换时间的概念。需要保证数据一致性。

为什么大厂不建议使用多表join？ #

主要原因是join效率比较低

MySQL是使用了嵌套循环的方式实现关联查询的，简单来说通过两层循环，用第一张表做外循环，第二张表做内循环，外循环的每条记录跟内循环的记录做比较，符合条件就输出。

两张表的话，复杂度O(n^2),O(n^3),。。。

join #

join有三种

  • inner join（内连接，或等值连接）：获取两个表中字段匹配关系的记录

    取两个表的交集部分

  • left join（左连接）：获取左表中的所有记录，即使右表中没有对应匹配的记录

    取两个表的交集部分+左表中的数据

  • right join（右连接）：获取右表中的所有记录，即使左表没有对应匹配的记录。

    取两个表的交集部分+右表中的数据

在配合join一起使用的还有on关键字，用来指明关联查询的一些条件。

嵌套循环算法 #

MySQL使用了嵌套循环（Nested-Loop join)的方式来实现关联查询的

具体算法：

  • simple nested loop join

    做法简单粗暴，就是全量扫描连接两张表进行数据的两两对比，所以复杂度可认为为：N*M

    N是驱动表的数量，M是被驱动表的数量

  • index nested loop

    当inner loop的表用到的字段有索引的话，可以用索引进行查询数据，因为索引是B+树的，复杂度可以近似认为是N*logM

  • block nested loop

    引入一个buffer，会提前把外循环的一部分数据提前放到buffer中，然后内循环的每一行都和整个buffer的数据比较。虽然比较次数还是N*M，但是因为buffer是基于内存的，所以效率高很多。

不做join如何关联查询 #

  • 在内存中自己作关联，即先从数据库中把数据查出来之后，我们在代码中再进行二次查询，然后再进行关联。

    categoryMap := make(map[uint]string)
for _, c := range categories {
    categoryMap[c.ID] = c.Name
}

// 为每本书添加分类名称
for i := range books {
    books[i].CategoryName = categoryMap[books[i].CategoryID]
}

  • 数据冗余，就是把一些重要数据在表中作冗余

  • 宽表，就是基于一定的join关系，把数据库中多张表的数据打平做一张大宽表，可以同步到ES或干脆直接在数据库中直接查询

    • 可在应用层自己构建宽表
    • 数据库也可直接创建宽表

    Elasticsearch（简称 ES） 是一个开源的 分布式搜索和分析引擎，基于 Apache Lucene 构建，专为处理大规模数据而设计。它提供 近实时（NRT, Near Real-Time） 的全文搜索能力，支持结构化、非结构化数据的快速检索和分析。

MySQL一条SQL语句的执行过程 # 

select * form users where age='18' and name='hool'

  • 使用连接器，通过客户端/服务器通信协议与MySQL建立连接，并查询是否有权限。
  • MySQL8.0之前检查是否开启缓存，开启Query Cache且命中完全相同的SQL语句，则将查询结果直接返回给客户端。
  • 由解析器（分析器）进行语法和语义分析，并生成解析数。如查询是select、表名users、条件是age=‘18’ and name=‘hool’,预处理器则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表或数据列是否存在等。
  • 由优化器生成执行计划。根据索引看看是否可以优化
  • 执行器来执行sql语句，这里具体执行操作MySQL的存储引擎来执行SQL语句，根据存储引擎类型，得到查询结果。若开启了 Query Cache，则缓存，否则直接返回。

MySQl 5.x和8.0有什么区别 #

  • 性能：8.0比5.x快约2倍

  • NoSQL：5.7版本开始提供NoSQL存储功能，8.0在其上面做了进一步改进。

  • 窗口函数：8.0开始，新增一个窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与SUM()、COUNT()这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行中。即窗口函数不需要group by(分组）。

    统计每个部门的员工数量

    SELECT 
    department, 
    COUNT(*) AS employee_count
FROM 
    employees
GROUP BY 
    department;

  • 隐藏索引：8.0中，索引可以被隐藏和显示。隐藏时，它不会被优化器所使用。可以用来进行性能调试。索引隐藏后， 若性能没变化，证明索引多余。

  • 取消查询缓存：8.0出于性能和可维护方面取消了查询缓存，通过使用索引、更好的查询计划优化、以及缓存结果集而不是整个查询。

  • select for update支持no wait：如果另一个事物已经锁定了一些行，当前事物将等待直到哪些锁被释放。加上NOWAIT关键字后，如果尝试锁定的行被其他事物锁定，立即抛出错误，不再等待。

  • 降序索引：8.0为索引提供按降序方式进行排序的支持，在这种索引中的值也会按降序的方式进行排序

  • UTF-8编码：从8.0开始，utf8mb4作为mysql默认字符集

  • json:8.0大幅改进了对json的支持

  • 可靠性增加

  • 高可用性：innoDB集群集成了原生高可用性解决方案

  • 安全性增强

InnoDB支持哪几种行格式？ #

行格式 版本支持 特点描述
COMPACT compact(小型的、袖珍的) 所有版本 默认格式，紧凑存储，减少空间占用
DYNAMIC dynamic（动态的、动力的） 5.7+ (默认) 处理大对象(BLOB/TEXT)更高效，支持大索引键
COMPRESSED compressed(压缩的) 所有版本 提供表和索引数据压缩
REDUNDANT redundant(多余的，不需要的) 兼容保留 旧格式，兼容性保留

数据库事务 #

事务

InnoDB的一次更新事务过程是怎样的？ #

  • 在buffer pool中读取数据：当InnoDB需要更新一条记录时，首先会在buffer pool中查询该记录是否在内存中。如果没有在内存中，则从磁盘读取该页到buffer pool中。

    Buffer Pool 是 InnoDB 存储引擎的 核心内存组件，主要用于缓存表数据和索引数据，是 MySQL 性能优化的关键所在。

    Buffer Pool（缓冲池） 是 InnoDB 在内存中开辟的 数据缓存区域，用于：

    • 缓存磁盘上的数据页（减少磁盘 I/O）
    • 加速读写操作（内存访问比磁盘快几个数量级）
    • 管理脏页（Dirty Page）（待写入磁盘的修改数据）

  • 记录Undo log 回滚日志:在修改操作前，InnoDB会在Undo log中记录修改前的数据。Undo log的写入最开始写入到内存中，然后由一个后台线程定时刷新到磁盘中。

    若未刷盘，会回滚事务，超时重试、强制刷盘等机制

  • 在Buffer Pool中更新：当执行update语句时，InnoDB会先更新已经读取到Buffer Pool中的数据，而不是直接写入磁盘。同时，InnoDB会将修改后的数据页状态设置为脏页状态，表示该页已经被修改但尚未写入磁盘。

  • 记录RedoLog buffer 重做日志：InnoDB在Buffer Pool中记录修改操作的同时，InnoDB会先将修改操作写入到redo log buffer中

  • 提交事务：在执行完所有修改操作后，事务被提交。在提交事务时，InnoDB会将Redo log写入磁盘，以保证事务持久性。

  • 写入磁盘：在提交过程中，InnoDB会将Buffer Pool的脏页写入磁盘，以保证数据的持久性。但这个写入过程并不是立即执行的，是后台线程异步执行的，所以会延迟写入。

  • 记录bin log :在提交过程中，InnoDB会将事务提交的信息记录到Binlog中。binlog是mysql用来实现主从复制的一种机制，用于将主库上的事务同步到从库上。在bin log中记录的信息包括：事务开始时间、数据库名、表名、事务ID、sql语句等。

需要注意的是，在binlog和redo log的写入过程中，其实是分了两阶段的，通过两阶段提交的方式保证一致性。

什么是事务的2阶段提交 #

过程是：

  • Prepare (准备）阶段

    这个阶段SQL已经成功执行并生成redolog写入磁盘，出于Prepare阶段

  • Binlog持久化

    binlog提交，将binlog写入磁盘

  • commit

    在执行引擎内部执行事务操作，写入redolog，处于commit阶段。

write和fsync是与文件系统和磁盘I/O相关的两个不同操作

write操作将数据写入文件的缓冲区，这意味着write操作完成后，并不一定立即将数据持久化到磁盘上，而是将数据暂时存储在内存中。

fsync用于强制将文件的修改持久化到磁盘上。它通常与write配合使用，以确保文件的修改在fsync操作完成后被写入磁盘。

除了这两个之外，还有一个fdatasync，它将指定文件等数据部分从操作it缓存同步到磁盘中，但可能不会刷新元数据（如文件修改时间、权限等）

二阶段提交主要为了保证bin log和redo log的提交一致性

若先写redo log，系统崩了，bin log没写，重启后，事务状态可恢复，但从数据库无法同步

若先写bin log，系统崩了，redo log没写，重启后，事务不可恢复，从数据库多一条记录。

  • 第一阶段提交后崩了，即写入redo log，处于prepare状态的时候

    此时已经写入了redolog，处于prepare状态，binlog还没写，崩了直接回滚事务

  • 第二阶段崩了

    此时redolog处于prepare状态，binlog写完了，这时候检测binlog事务是否存在并且完整，如果存在且完整，则直接提交事务，否则回滚事务

  • 第三阶段崩了

    同阶段二类似处理

如何判断binlog和redolog达成一致了？ #

mysql写完redo log并将它改为prepare状态时，会在redo log中记录一个 XID，全局唯一标识这个事务。

第二阶段binlog刷新磁盘后，binlog结束位置上也会有一个XID。

对比XID确保一致性。

MySQL能保证数据100%不丢失吗？ #

不能

InnoDB有基于内存的，基于内存的如果断电，就会丢失

InnoDB设置了二次提交防止数据丢失。InonDB会默认设置将日志数据立即刷入磁盘，有两个参数，值设置为1。但这样还可能会丢失：

  • fsync只请求刷盘，不一定会真的落到磁盘介质

    有些硬盘会欺骗fsync

  • 写缓存未关闭

    如果设备层还启用了缓存，操作系统或数据库会认为写入完成，但实际上数据还在硬件缓存中。

  • 断电或非正常宕机

    如果没用使用UPS（不间断电源）或BBU（电池保护），哪怕你调用了fsync，掉电依旧可能导致数据丢失

  • 磁盘损坏等

什么是脏读、幻读、不可重复读、丢失修改？ #

脏读、丢失修改、不可重复读、丢失修改

不可重复读是读到的不一样，幻读是多了几行数据

MySql中的事务隔离级别？ #

隔离级别

可串行化解决了丢失修改

隔离级别 是否解决丢失修改 实现机制 性能影响
读未提交（Read Uncommitted） ❌ 不解决 无任何锁 最高
读已提交（Read Committed） ❌ 不解决 仅防止脏读 高
可重复读（Repeatable Read） ✅ 在InnoDB中解决（通过MVCC+间隙锁） 快照读 + 写冲突检测 中
可串行化（Serializable） ✅ 完全解决 全表锁 最低

InnoDB如何解决脏读、不可重复读、幻读的？ #

通过mvcc机制解决脏读和不可重复读，通过mvcc+间隙锁解决幻读

  • 脏读

    脏读读解决依赖了Read View,Read View会告诉我们本次事务应该看到那个快照，不应该看到那个快照。

    简单来说：当事务在“读已提交”隔离级别下执行读取操作时，InnoDB获取当前最新的全局事务ID，这个ID表示在当前时刻所有已提交事务的最新状态。InnoDB会检查每个数据行的版本，如果该版本是由一个小于或等于当前事务ID的事务修改的，并且该事务已提交，则这个版本是可见的。这保证了事务只能看到在它开始之前已经提交的数据版本。

  • 不可重复读

    InnoDB使用mvcc来解决不可重复读问题。在RR可重读读隔离级别下，当我们使用快照进行数据读取的时候，只会在第一次读取的时候生成一个Read VIew，后续的所有快照读都使用同一个快照。

  • 幻读

    基于MVCC+间隙锁，在某种程度可以避免幻读发生，但没有办法完全避免。

    事务1先进行快照读，事务2插入数据并提交，事务1再进行update新插入的这条记录是可以成功的。

    事务1先进行快照读，事务2插入数据并提交，事务1再进行当前读，会发生幻读。

MVCC解决幻读 #

mvcc有两种读，一种是快照读，一种是当前读

  • 快照读

    读取的是快照生成的那一刻的数据，像普通select 语句在不加锁的情况下就是快照读。

    在RC（读已提交）中，每次读取都会重新生成一个快照，总是读取行的最新版本。

    在RR（可重复读）中，快照会在事务中第一次select语句执行时生成，只有在本事务中对数据进行更改才会更新快照。

    也就是说在快照读情况下，mvcc可以解决幻读问题。

  • 当前读

    读取的是最新数据，所以加锁的select，或者对数据进行增删改都会进行当前读。

    在RR级别下，使用select … for update的时候，会进行加锁，不仅仅对行记录加锁，还会对记录之间的间隙进行加锁，这就叫间隙锁。

    因为记录之间的间隙被锁住了，所以事务2无法插入数据，一直到事务1把锁释放掉它才能执行成功。

    所以在RR级别中，通过加入间隙锁的方式，就避免了幻读的发生。

解决不了的幻读 #

  • 未来得及加间隙锁

  • 事务1第一次查询不加锁，用快照读，然后事务2插入数据并提交；事务1再加锁查询第二次，发生了当前读。

根据快照读的定义，如果本事务中发生了数据修改，就会更新快照。

如何避免幻读 #

最高隔离级别，可串行化

如果一定要加锁，一定要在事务一开始就加锁，这样就会有间隙锁，有效避免幻读发生。

如何理解MVCC？ #

多版本并发控制系统，和数据库锁一样，它是一种解决方案。

快照读是MVCC实现的基础，而当前读是悲观锁实现的基础。

UndoLog #

undo log是回滚日志，在事务未提交之前，MySQl会先提交undo log，当事务崩溃时，利用undo log进行回退。

一条记录在同一时刻可能有多个事务在执行，那么，undo log会有一条记录的多个快照，那么在这一时刻发生select要进行快照读的时候，要读那个快照呢？

行记录的隐式字段 #

数据库中的每行记录中，除了保存我们自定义的一些字段外，还有一些重要的隐式字段：

  • db_row_id:隐藏主键，如果我们没有给这个表创建主键，那么会以这个字段创建聚簇索引。
  • db_trx_id：对这条数据做出修改的最新事务ID
  • db_roll_ptr:回滚指针，指向这条记录的上一个版本，就是undo log的上一个版本的快照地址

**注意：**以上字段，只有在聚簇 索引的行记录中才有，而在普通二级索引中是没有这些值的。

二级索引的MVCC支持

因为每一次记录变更前都会先存储一份快照到undo log中，那么这几个隐式字段也会跟着记录一起保存在undo log中。因此，就形成了一个快照链表。

要读那个快照呢？

Read View #

见下方什么是Read View，什么样的ReadView可见？

一个事务，能看到的是在他开始之前就已经提交的事务结果，而未提交的事务结果都是不可见的。

如果不可见怎么办？使用undo log

当事务ID不符合Read View规则的时候，那就需要从undo log里面获取数据的历史快照，然后数据快照的事务ID再和Read view进行可见性比较，如果能找到一条快照则返回，否则返回空。

总结：在InnoDB中，MVCC就是通过Read View+Undo log来实现的，undo log中保存了历史快照，而Read View用来判断具体哪一个快照是可见的。

什么是Read View，什么样的ReadView可见？ #

Read View主要来帮我们解决可见性的问题，即他会来告诉我们本次事务应该看到那个快照，不应该看到那个快照。

RR要求在一个事务中，多次读取的结果是保持一致的，RC要求在一个事务中，每次读取最新的结果。

在可重复读级别下，快照在事务开始后第一次查询时创建，并在整个事务期间保持不变。

在读已提交级别下，快照会在每次查询时重新创建，以反映数据库中的最新提交更改。

在Read View中有个几重要的属性：

  • trx_ids:表示在生成Read View时当前系统中活跃的读写事务的事务id列表
  • low_limit_id：应该分配给下一个事务的id值
  • up_limit_id：未提交事务中最小的事务ID
  • creator_trx_id：创建这个Read View的事务ID

假如一个ReadView的内容为：

trx_ids = [5,6,8)
low_limit_id = 8
up_limit_id = 5
creator_trx_id = 7

假设当前事务要读取某一个记录行，该记录行的 db_trx_id（即最新修改该行的事务ID）为 trx_id，那么，就有以下几种情况了：

  • trx_id<up_limit_id，即小于5的事务，说明这些事务在生成ReadView之前就已经提交了，那么该事务的结果就是可见的。
  • trx_id>=low_limit_id，即大于8的事务，说明该事务在生成 ReadView 后才生成，所以该事务的结果就是不可见的。
  • up_limit_id<trx_id<low_limit_id，即大于等于5，小于8，这种情况下，会再拿事务ID和Read View中的trx_ids进行逐一比较。
    • 如果，事务ID在trx_ids列表中，如6，那么表示在当前事务开启时，这个事务还是活跃的，那么这个记录对于当前事务来说应该是不可见的。
    • 如果，事务id不在trx_ids列表中，如7，那么表示的是在当前事务开启之前，其他事务对数据进行修改并提交了，所以，这条记录对当前事务就应该是可见的。
    • 当然这里有个例外情况，那就是这个trx_id=creator_trx_id，那么就肯定是可见的

总之，一个事务，能看到的是在他开始之前就已经提交的事务结果，而未提交的事务结果都是不可见的。

mysql的select * 会用到事务吗？ #

会，在innoDB中，所有的修改操作都必须在事务中进行

即使没有明确的开启事务的语句，InnoDB存储引擎也会为查询自动开启一个隐式事务

查询语句事务不会持有任何锁，在查询结束后立即提交。这种隐式事务通常被称为自动事务。

为什么MySQl默认使用RR隔离级别？ #

Mysql定位是提供一个稳定的关系型数据库。为了解决单点故障问题，主要采用主从复制。就是搭建Mysql集群，整体对外提供服务，集群中的机器分为主服务器和从服务器，主服务器提供写服务，从服务器提供读服务。

MySql在主从复制的过程中，数据的同步是通过bin log进行的。

早期bin log只有statement这一种格式，里面记录的就是SQL语句原文。

如果使用读未提交和读已提交这两种隔离级别会出现以下问题： #

事务1开始 事务2开始
删除小于10的数据
插入一条数据5
提交事务
提交事务

在主数据库中，如果是RC隔离级别，事务2插入操作看不到事务1删除操作，所以数据库中后面仍然会留下5

但由于事务提交顺序不同，在bin log中会留下 插入数据5 删除小于10的数据 的SQL语句。这样bin log被同步到从数据库中，会导致数据不一致。

为了避免上述情况，才讲数据库默认隔离级别设置成了RR。

在RR隔离级别下，事务1在更新数据的时候，不仅对更新的行加行级锁，还会增加GAP锁和临键锁。所以事务2执行时会被卡住，等事务1结束后才能继续执行。

因此Mysql还禁止在使用statement格式的bin log情况下，使用RC 事务隔离级别。会报错。

MySql的binlog有几种格式？ #

  • statement

    用的很少，binlog记录的SQL原文，会导致主从数据库数据不同步情况

    • 一种是RC隔离级别下的不同步
    • 一种是delete或update时，指定了limit，但并没有使用order by。

  • row

    binlog会记录每个数据更改的具体细节。意味着二进制日志中的每个条目都会详细列出发生变更的行的内容和修改。

    • 优点：不会发生主从不一致情况
    • 缺点：会记录更多的数据，在数据恢复的时候比较慢，也会导致磁盘IO和网络IO比较高。

  • mixed

    结合row 和 statement 的优点，MySQL 会根据情况自动选择切换。

MySQL的limit+order by什么会数据重复？ #

如果order by的列中，多行具有相同的值，服务器可以自由地以任何顺序返回这些行，并且根据整体执行计划的不同可能会以不同的方式返回它们。

可能结果1：

id name score
1 Alice 85
2 Bob 85
3 Charlie 85

可能结果2（相同查询，不同执行计划）：

id name score
5 Eve 85
3 Charlie 85
2 Bob 85

因此进行limit+order by时，一定要避免使用可能重复的字段，如时间、名称、分数等。应该选择唯一性的字段，如主键ID。

为什么默认RR，大厂要改成RC？ #

主要是为了提高并发度，降低死锁发生概率。

  • 一致性读

    又称快照读。

    在mysql中，只有RR和RC两种隔离级别才会使用快照读。

    在RR中，快照会在事务第一次select语句执行时生成，只有在本事务中对数据进行更改才会更新快照。

    在RC中，每次读取都会重新生成快照，总是读取行的最新版本。

    在RC这种隔离级别中，还支持**“半一致读”**，一条update语句，如果where条件匹配到的记录已经加锁，那么InnoDB会返回记录最近提交的版本，由MySQL上层判断是否需要真的加锁。

  • 锁机制

    在MySQL中，有三种类型锁

    • Record Lock：记录锁，锁的是索引记录

    • Gap Lock:间隙锁，锁的是索引记录之间的间隙

    • Next-key Lock:是记录锁和间隙锁的组合，同时锁索引记录和间隙。它的范围是左开右闭的。

      为什么左开右闭？

    在RC中，只会对索引加记录锁，不会添加间隙锁和Next-key Lock

    在RR中，为了解决幻读问题，都支持

    在RR中，什么时候加Record lock，什么时候加Gap lock，什么时候加Next-key lock?

  • 主从同步

    在RC隔离级别下，可以支持row格式日志

为什么使用？ #

  • 提升并发

    RC只会对索引加记录锁，使得并发比RR高，RC还支持**“半一致读”**，可以大大减少更新语句时行锁的冲突；对于不满足更新条件的记录，可以提前释放锁，提升并发度。

  • 减少死锁

    在RR中，为了解决幻读问题，支持三种锁，使得锁粒度变大，死锁概率变大

通过其他方式解决不可重复读问题，或者不可重复读问题并不严重。商品大促时，突然修改了商品价格，部分用户按原价格下单，又能怎？

介绍一下InnoDB的锁机制？ #

在InnoDB中，在锁的级别上分为两种，共享锁（s锁）、排他锁（X锁）

共享锁、排他锁 #

共享锁又称读锁，是读操作时创建的锁。只能读，不能改。

select ... lock in share mode;

在查询语句后面增加上面语句，mysql会对查询结果中的每行都加共享锁。

当这一行数据获取了共享锁，那么其他事务依然可以对这一行数据添加共享锁，但不能添加排他锁

排他锁又称写锁，其他事务不能读，也不能改。

select ... for update;

在查询语句后面增加上面语句，mysql会对查询命中的每条记录都加排他锁。

当这一行数据获取了排他锁，那么其他事务就不能在对这一行数据添加共享锁或者排他锁。

意向锁 #

意向锁（Intention Lock） 是一种“提前声明”，告诉别人：“我可能要锁整张表，你们锁行的时候注意点！”

它不直接锁数据，而是标记一个意向，避免其他事务不小心锁表，导致冲突。

所以，意向锁是数据库管理系统中用于实现锁协议的一种锁机制，旨在处理不同锁粒度（如行锁和表锁）之间的并发性问题。（相同锁粒度（如多个行级锁）之间的并发性问题通过行级互斥锁解决。）

**当一个事务请求获取一个行级锁或表级锁时，MySQL会自动获取相应的表的意向锁。**这样，其他事务请求获取表锁时，就可以先基于这个意向锁来发现是否有人加过锁，并根据该锁的类型（意向共享锁/意向排他锁）来判断自己是否可以获取锁。这样可以在不阻塞其他事务的情况下，为当前事务锁定资源。

意向锁是一个表级锁，并且他会在触发意向锁的事务提交或者回滚后释放。

IX锁意向排他锁、IS锁 意向共享锁

  • 意向共享锁（IS Lock）：
    • 相当于你在桌子上贴个纸条：“我可能要读这张表里的某些行，别删整张表！”
    • 其他事务可以继续读，但不能直接锁整张表（比如不能加排他锁）。
  • 意向排他锁（IX Lock）：
    • 相当于你贴纸条：“我可能要改这张表里的某些行，别动整张表！”
    • 其他事务可以读某些行，但不能直接锁整张表。

记录锁 #

Record Lock,是加在索引记录上的锁。

Record Lock是一个典型的行级锁，Record Lock锁的不是这行记录，而是锁索引记录。并且Record Lock锁且只锁索引。

**如果没有索引怎么办？**InnoDB会创建一个唯一的 隐藏的 非空聚簇索引，并使用这个索引进行记录锁定。如果没有适合的非空唯一索引，则会创建一个隐藏主键（row_id)作为聚簇索引。

插入意向锁 #

插入意向锁是一种由插入操作在行插入之前设置的间隙锁。这种锁表明了插入的意图，以这样一种方式，如果多个事务插入同一索引间隙中但不在间隙内的相同位置插入，则他们不需要相互等待。

假设有索引记录的值为 4 和 7。分别尝试插入值为 5 和 6 的不同事务，在获取插入行的独占锁之前，各自用插入意向锁锁定 4 和 7 之间的间隙，但由于行不冲突，所以它们不会相互阻塞。但是如果他们的都要插入6，那么就会需要阻塞了。

AUTO- INC锁 #

auto-inc锁是一种特殊的表级锁，由插入带有AUTO_INCREMENT列的表的事务获取。在最简单的情况下，如果一个事务正在向表中插入值，任务其他事务都得等待，以便执行它们自己的插入操作，这样第一个事务插入的行就会接收到连续的主键值。

innodb_autoinc_lock_mode变量控制用于自增锁定的算法。它允许你选择如何在可预测的自增值序列和插入操作的最大并发性之间进行权衡。

InnoDB中的表级锁、页级锁、行级锁 #

在数据库中，按锁的粒度划分，可分为全局锁、表级锁、行级锁、页级锁。

InnoDB中不支持页级锁。

全局锁 #

全局锁，是一种影响整个MySQL实例的锁。

例如，FLUSH TABLES WITH READ LOCK 命令会锁定整个数据库实例的所有表，主要用于全局备份等操作。这个命令是全局读锁定，执行了命令之后库实例中的所有表都被锁定为只读。

页级锁 #

✅ 比表锁灵活，只锁一部分数据（InnoDB 的"页"通常是 16KB 数据块）。

❌ MySQL 默认不用页锁（InnoDB 主要用行锁）。

行级锁 #

✅ 并发度最高，只锁一行，其他人可以随便操作其他行。

锁的是索引

update时没有用到索引，加什么锁？

当update和where条件中没有用到索引的话，他会做全表扫描，但也不是全部锁定。而是将符合条件的记录锁定。

字典锁 #

字典锁：MetaData Lock，也叫MDL锁，它是一种用于管理元数据的锁机制，而不是数据本身的锁。

MDL锁用于控制对数据库对象的元数据的并发访问，数据库会在执行DDL（Data Defination Language）操作时加上字典锁。字典锁的主要目的是保护数据库中的元数据对象，如表、列、索引、视图等，以确保在DDL操作期间，不会出现数据一致性问题和竞争条件。

触发数据库加字典锁的一些情况：

  • 创建/修改/删除表结构
  • 创建/修改/删除索引
  • 修改列定义
  • 创建/修改/删除视图
  • 其他DDL操作：创建、删除、修改存储过程、触发器、事件等也能设计到元数据的锁的。
字典锁升级 #

在数据库中， 通常有两种锁级别，即共享锁和排他锁。字典锁也有两种锁级别：

  • 共享字典锁
  • 排他字典锁

表级锁 #

InnoDB中的表级锁并不是没有用，而是因为他划分的太细了，意向锁、AUTO- INC锁、字典锁等。而剩下的普通的排他锁和共享锁，确认很少才能用的上。

**InnoDB会倾向于选择行级锁来进行并发控制。**但如果在一些极端情况下，比如说UPDATE操作需要扫描整个表且对表中许多行进行更新，InnoDB可能会评估行级锁的成本过高，而采用表级锁。

高并发情况下自增主键会不会重复，为什么？ #

单库单表情况下，不会重复，

Mysql通过AUTO-INC锁机制确保了自增主键的唯一性。

AUTO-INC是InnoDB存储引擎专门用来管理自增长列（AUTO_INCREMENT）increment 值分配的一种内部机制。

AUTO-INC的工作原理是：**当一个事务尝试向一个包含自增列的表中插入一条或多条新记录时，InnoDB会为该表上的自增列申请一个AUTO- INC锁。**这个锁确保在事务插入记录期间，没有其他事务可以插入新记录到同一个表中。

mysql5.1之前，它是一个表级锁，在插入开始的时候被获取，一直等到事务结束被释放。5.1开始，引入了一种新的AUTO-INC锁策略；AUTO-INC锁在插入操作完成后就被释放，不用等事务结束。这种锁被称为“轻量级AUTO-INC锁”。

乐观锁和悲观锁如何实现？ #

锁按使用方式划分为乐观锁和悲观锁。

悲观锁 #

在数据库中，悲观锁的流程如下：

  • 在对记录进行修改前，先尝试为该记录加上排他锁。
  • 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或抛出异常。具体响应方式由开发者根据实际需要决定。
  • 如果加锁成功，那么就可以对记录做出修改，事务完成后就可以解锁了。
  • 其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。
//0.开始事务
begin; 
//1.查询出商品信息
select quantity from items where id=1 for update;
//2.修改商品quantity为2
update items set quantity=2 where id = 1;
//3.提交事务
commit;

乐观锁 #

MySQL中的乐观锁主要通过CAS的机制来实现，一般通过version版本号来实现。

CAS是项乐观锁技术，当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试。

//查询出商品信息，quantity = 3
select quantity from items where id=1
//根据商品信息生成订单
//修改商品quantity为2
update items set quantity=2 where id=1 and quantity = 3;

我们在更新之前，先查询一下库存表中当前库存数（quantity），然后在做update的时候，以库存数作为一个修改条件。当我们提交更新的时候，判断数据库表对应记录的当前库存数与第一次取出来的库存数进行比对，如果数据库表当前库存数与第一次取出来的库存数相等，则予以更新，否则认为是过期数据。

CAS（Compare-And-Swap，比较并交换）是一种无锁（lock-free） 的原子操作机制，用于实现多线程环境下的并发控制。它是一种乐观锁的实现方式。

CAS 操作包含三个基本操作数：

  • •内存位置（V）
  • •预期原值（A）
  • •新值（B）
CAS(V, A, B) 的操作逻辑：
1. 比较内存位置 V 的当前值是否等于预期值 A
2. 如果相等，则将内存位置 V 的值更新为新值 B
3. 如果不相等，则不进行任何操作（或者返回失败）

可能导致的问题：

  • 中间态丢失：数据从100-101-100，用户无法感知
  • 高竞争下的性能问题：大量重试操作失败；悲观锁操作前置，在执行之前就知道会失败。乐观锁完整执行后才发现失败。
  • 监控和调试困难，冲突发生在数据库层面，应用层难以追踪。

如何选择 #

  • 乐观锁在使用时，并未显示的加锁，效率高。适用于读操作频繁，写操作相对较少的场景。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
  • 悲观锁依赖数据库锁，效率低。更新失败的概率比较低。适用于写操作较为频繁，且并发写入的概率较高的场景。

MySQL的行级锁锁的到底是什么？ #

Record Lock # 

select c1 from t where c1=10 for update;

会对c1=10这条记录加锁，为了防止任何其他事务插入、更新或删除c1=10的行。

Gap Lock #

**指的是在索引记录之间的间隙上的锁。**或者在第一个索引记录之前或最后一个索引记录之后的间隙上的锁。

Gap指的是InnoDB的索引数据结构中可以插入新值的位置。

Gap lock 只在可重复读（RR)隔离级别中生效。

  • 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不会锁定间隙。
  • 对于其他搜索条件，InnoDB锁定扫描的索引范围。使用gap lock 或 net-key lock来阻塞其他事务插入范围覆盖的间隙。

也就是说，对于SELECT FOR UPDATE、LOCK IN SHARE MODE、UPDATE和DELETE等语句处理时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁，即锁住其扫描的范围。

Next-key Lock #

Next-key Lock是索引记录上的记录锁和间隙锁的组合。

假设一个索引包含值10、11、13和20。此索引可能的next-key锁包括以下区间:

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, ∞ ]

对于最后一个间隙，∞不是一个真正的索引记录，因此，实际上，这个next-key锁只锁定最大索引值之后的间隙。

所以，Next-Key 的锁的范围都是左开右闭的。

Next-Key Lock和Gap Lock一样，只有在InnoDB的RR隔离级别中才会生效。

Next-key Lock 的 左开右闭 设计是 InnoDB 在 效率（减少不必要的锁冲突）和 正确性（彻底解决幻读）之间的平衡：

  • 左开：避免锁住不相关的记录（如前驱记录）。
  • 右闭：确保查询范围的终止点被严格锁定，防止幻读。

MySql的加锁原则 #

两个原则、两个优化、一个bug

原则1:加锁的基本单位是next-key lock。

原则2:查找过程中访问到的对象才会加锁。

优化1:索引上的等值查询，给唯一索引加锁的时候，next-key Lock退化为行锁。

优化2:索引上的等值查询，向右遍历时最后一个值不满足等值条件的时候，next-key Lock退化为间隙锁。

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

  • 当我们执行update t set d=d+1 where id = 7的时候，由于表 t 中没有 id=7 的记录，所以：

    • 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
    • 根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

  • 当我们执行select * from t where id>=10 and id<11 for update的时候：

    • 根据原则 1，加锁单位是 next-key lock，会给 (5,10]加上 next-key lock，范围查找就往后继续找，找到 id=15 这一行停下来
    • 根据优化 1，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
    • 根据原则 2，访问到的都要加锁，因此需要加 next-key lock(10,15]。因此最终加的是行锁 id=10 和 next-key lock(10,15]。

  • 当我们执行select * from t where id>10 and id<=15 for update的时候：

    • 根据原则 1，加锁单位是 next-key lock，会给 (10,15]加上 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
    • 但是，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

  • 当我们执行select id from t where c=5 lock in share mode的时候：

    • 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。
    • 根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。
    • 根据优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
    • 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁。

  • 当我们执行select * from t where c>=10 and c<11 for update的时候：

    • 根据原则 1，加锁单位是 next-key lock，会给 (5,10]加上 next-key lock，范围查找就往后继续找，找到 id=15 这一行停下来
    • 根据原则 2，访问到的都要加锁，因此需要加 next-key lock(10,15]。由于索引 c 不是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

InnoDB加索引，这个时候会锁表吗？ #

在 MySQL 5.6 之前，InnoDB 索引构建期间会对表进行排它锁定，这意味着在索引构建期间，其他会话不能读取或修改表中的任何数据。

自 MySQL 5.6 开始，InnoDB 使用一种称为“Online DDL”的技术，允许在不阻塞其他会话的情况下创建或删除索引

因为DDL有很多种操作，比如创建索引、增加字段、增加外键等，所以不同的操作支持的类型也不一样，具体支持方式可以在mysql官方可以看到（https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html ）

需要注意的是，虽然Online DDL 可以减少锁定时间和对性能的影响，但在索引构建期间仍然可能出现锁定和阻塞。例如，在添加索引时，如果表中有许多未提交的事务，则需要等待这些事务提交后才能开始索引构建。因此，**建议在非高峰期进行此类操作，以避免影响用户的正常使用。**在进行任何DDL操作之前，最好进行充分的测试和规划，并且确保有备份和回滚计划，以防意外情况的发生。

InnoDB为什么使用B+树实现索引？ #

  • 。。。。

  • **在节点分裂和合并时，IO操作少。**B+树的叶子节点的大小是固定的，而且节点的大小一般都会设置为一页的大小，这就使得节点分裂和合并时，IO操作很少，只需读取和写入一页。

  • **有利于磁盘预读。**由于B+树的节点大小是固定的，因此可以很好地利用磁盘预读特性，一次性读取多个节点到内存中，这样可以减少IO操作次数，提高查询效率。

B+树索引和Hash索引有什么区别？ #

  • B+ 树索引将索引列的值按照大小排序后存储，因此B+ 树索引适合于范围查找和排序操作；而哈希索引是将索引列的值通过哈希函数计算后得到一个桶的编号，然后将桶内的记录保存在一个链表或者树结构中。因此，哈希索引适合于等值查询，但不适合范围查询和排序操作。

  • B+ 树索引在插入和删除数据时需要调整索引结构，这个过程可能会涉及到页分裂和页合并等操作，因此B+ 树索引的维护成本比较高；而哈希索引在插入和删除数据时只需要计算哈希值并插入或删除链表中的记录，因此哈希索引的维护成本相对较低。

  • B+ 树索引在磁盘上是有序存储的，因此在进行区间查询时可以利用磁盘预读的优势提高查询效率；而哈希索引在磁盘上是无序存储的，因此在进行区间查询时可能会需要随机访问磁盘，导致查询效率降低。

  • B+ 树索引在节点中存储多个键值对，因此可以充分利用磁盘块的空间，提高空间利用率；而哈希索引由于需要存储哈希值和指针，因此空间利用率相对较低。

MySQL是如何保证唯一性索引的唯一性的？ #

B+树的特殊结构保证索引唯一

MySQL通常使用B树（或变种如B+树）作为唯一性索引的数据结构。这种结构允许高效的数据检索和插入操作。当插入一个新行或更新现有行的索引列时，MySQL首先在索引中检查是否已经存在相同的键值。如果发现索引列的新值已经存在于唯一性索引中，MySQL将阻止该插入或更新操作，并返回一个错误。

唯一索引允许为NULL值吗？ #

主键是一种特殊的唯一索引，不允许为NULL

其他唯一索引允许为NULL，多数数据库只允许一个NULL值存在，**但InnoDB存储引擎在MySQL中支持在唯一索引中有多个NULL值。**因为在MySQL中，NULL被认为是“未知”的，每个NULL值都被视为互不相同。

唯一索引查询更快吗？ #

唯一性索引查询通常会比非唯一性索引查询更快（有差异，但是不大。），因为唯一性索引能够快速定位到唯一的记录，而非唯一性索引则需要扫描整个索引，从找到匹配的数据之后，要继续执行匹配，直到找到不匹配的数据之后。

缺点 #

首先，唯一性索引需要保证索引列的唯一性，因此在插入数据时需要检查是否存在相同的索引值，这会对插入性能产生一定的影响。

什么是聚簇索引和非聚簇索引？ #

聚簇索引，简单点理解就是将数据与索引放到了一起，找到索引也就找到了数据。也就是说，对于聚簇索引来说，他的非叶子节点上存储的是索引字段的值，而他的叶子节点上存储的是这条记录的整行数据。

非聚簇索引，就是将数据与索引分开存储，叶子节点包含索引字段值及指向数据页数据行的逻辑指针，通常为主键。

没有创建主键怎么办？ #

如果我们没有给这个表创建主键，会选择一个不为空的唯一索引来作为聚簇索引，但是如果没有合适的唯一索引，那么会以这个隐藏主键来创建聚簇索引。

什么是回表，怎么减少回表的次数？ #

当我们根据非聚簇索引查询的时候，会先通过非聚簇索引查到主键的值，之后，还需要再通过主键的值再进行一次查询才能得到我们要查询的数据。而这个过程就叫做回表。

在InnoDB 中，使用主键查询的时候，是效率更高的， 因为这个过程不需要回表。另外，依赖覆盖索引、索引下推等技术，我们也可以通过优化索引结构以及SQL语句减少回表的次数。

什么是索引覆盖、索引下推？ #

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。

索引下推是 MySQL 5.6引入了一种优化技术，默认开启。查询 A like %三 AND B=1，先找到A，再去判断是否满足B，然后返回数据。而不是找到A就返回，在服务层判断。

索引下推不止like #

索引下推其实是解决索引失效带来的效率低的问题的一种手段。

所以当联合索引中，某个非前导列因为索引失效而要进行扫表并回表时，就可以进行索引下推优化了。

如，有a,b联合索引，类型都是varchar，以下SQL也可以用到索引下推：

select d from t2 where a = "ni" and b = 1;

因为b字段因为类型不匹配导致索引失效了，但是通过下推优化其实是可以减少回表的次数的。

设计索引时有哪些原则（考虑哪些因素）？ #

  • 考虑查询的频率和效率

    在决定创建索引之前，需要分析查询频率和效率。对于频繁查询的列，可以创建索引来加速查询，但对于不经常查询或者数据量较少的列，可以不创建索引。

  • 选择适合的索引类型

    MySQL提供了多种索引类型，如B+Tree索引、哈希索引和全文索引等。不同类型的索引适用于不同的查询操作，需要根据实际情况选择适合的索引类型。

  • 考虑区分度

    尽量不要选择区分度不高的字段作为索引，比如性别。但是也并不绝对，对于一些数据倾斜比较严重的字段，虽然区分度不高，但是如果有索引，查询占比少的数据时效率也会提升。

  • 考虑联合索引

    当多个列一起被频繁查询时，可以考虑创建联合索引。

  • 考虑索引覆盖

    联合索引可以通过索引覆盖而避免回表查询，可以大大提升效率，对于频繁的查询，可以考虑将select后面的字段和where后面的条件放在一起创建联合索引。

  • 避免创建过多的索引

    创建过多的索引会占用大量的磁盘空间，影响写入性能。并且在数据新增和删除时也需要对索引进行维护。所以在创建索引时，需要仔细考虑需要索引的列，避免创建过多的索引。

  • 避免使用过长的索引

    索引列的长度越长，索引效率越低。在创建索引时，需要选择长度合适的列作为索引列。对于文本列，可以使用前缀索引来减少索引大小。

  • 合适的索引长度

    虽然索引不建议太长，但是也要合理设置，如果设置的太短，比如身份证号，但是只把前面6位作为索引，那么可能会导致大量锁冲突。

什么是最左前缀匹配？为什么要遵守？ #

左前缀匹配是指在查询中利用索引的最左边的一部分来进行匹配。指你执行查询时，如果查询条件涉及到了组合索引的前几个列，MySQL 就可以利用这个复合索引来进行匹配。

在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。

索引一定遵循最左前缀匹配吗？ #

索引跳跃扫描 #

因为索引底层是一个B+树，如果是联合索引的话，在构造B+树的时候，会先按照左边的key进行排序，左边的key相同时再依次按照右边的key排序。

所以，在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。这也就是最左前缀匹配。

MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，没有任何毛病。但是在MySQL 8.0中，就不一定了。因为8.0.13中引入了索引跳跃扫描。

MySQL 8.0.13 版本中，对于range查询（什么是range后面会提到)，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。

Range（范围查询）指的是在WHERE条件中使用范围操作符进行的查询，这类查询对数据库性能有显著影响。

Range查询是指使用以下操作符的查询条件：

>   -- 大于
>=  -- 大于等于
<   -- 小于
<=  -- 小于等于
BETWEEN ... AND ...  -- 区间范围
LIKE 'prefix%'      -- 前缀匹配（非通配符开头）
IN (...)            -- 多值匹配（本质是多个等值查询）

-- 索引：INDEX(gender,age)
SELECT * FROM users WHERE age = 20;
-- MySQL 8.0+可能使用跳跃扫描
-- 相当于自动补全gender的不同值：
-- WHERE (gender='F' AND age=20) OR (gender='M' AND age=20)

这种查询优化比较适合于f1的取值范围比较少，区分度不高的情况（如性别），一旦f1的区分度特别高的话（如出生年月日），这种查询可能会更慢。

真正要不要走索引跳跃扫描，还是要经过MySQL的优化器进行成本预估之后做决定的。

覆盖索引 # 

-- 索引：INDEX(a,b,c)
SELECT b,c FROM table WHERE b = 2 AND c = 3;
-- 虽然条件没包含a列，但查询的列都在索引中
-- 可能使用索引扫描（type=index）

主键一定是自增的吗？ #

不一定

自增好处：

  • 索引大小更小：使用自增主键可以确保主键的递增性，使得新插入的数据都会在索引的末尾处，减少了数据页的分裂和页分裂导致的IO操作，使得索引大小更小，查询速度更快。
  • 索引顺序和插入顺序相同：使用自增主键可以保证索引顺序和插入顺序相同，减少了插入新数据时索引的重新排序，提高了插入速度。
  • 安全性：使用自增主键可以避免主键重复的情况，确保数据完整性和唯一性。
  • 减少页分裂及内存碎片

虽然自增，但不一定连续，自增锁，事务回滚、插入失败、数据删除等导致

没有设置主键怎么办？ #

  • 默认使用唯一索引
  • 自动创建一个隐藏的主键（row_id）作为聚簇索引

UUID和自增ID做主键那个好？ #

  • UUID 是一个 128 位长的唯一标识符，通常以字符串形式表示。它可以使用不同的算法生成，比如基于时间戳的 UUID（version 1）和随机数生成的 UUID（version 4）等。

    优点：

    • 全局唯一：几乎可保证全球范围内唯一
    • 不可预测：随机生成
    • 分布式：由于可以在不同的机器上生成 UUID，因此可以用于分布式系统中。

    缺点：

    • 存储空间比较大：UUID 通常以字符串形式存储，占用的存储空间比较大。
    • 不适合范围查询：因为不是自增的，所以在做范围查询的时候是不支持的
    • 不方便展示：主键ID有的时候会需要在系统间、或者前台页面展示，如果是UUID的话，就因为比较长、并且没有任何业务含义，所以不方便展示。
    • 查询效率低：插入页分裂等

  • 自增ID

    优点：

    • 存储空间小
    • 查询效率高
    • 方便展示
    • 分页方便

    一些问题：

    • 分库分表：当我们做分库分表的时候，就没办法依赖一张表的自增主键来做主键ID了，这样就会发生重复导致冲突的问题
    • 可预测
    • 可能用尽：自增id的话可能是int、bigint等，但是他们都是有范围的，有可能会用尽

order by是怎么实现的？ #

具体怎么排序取决于优化器的选择，如果优化器认为走索引更快，那么就会用索引排序，否则，就会使用filesort (执行计划中extra中提示：using filesort），在内存中排序。sort_buffer

如果字段长度不是特别长，就会使用全字段排序的方式直接在sort_buffer中排序后返回结果集。如果字段长度特别长，那么就基于空间考虑，采用row_id排序，这样就会在排序后进行二次回表后返回结果集。

以下几种情况，走索引的概率很高：

  • 查询的字段和order by的字段组成了一个联合索引，并且查询条件符合最左前缀匹配，查询可以用到索引覆盖。如select a,b,c from t2 order by a;
  • 查询条件中有limit，并且limit的数量并不高。（我测试的表数据量80万，在limit超过2W多的时候就不走索引了），如order by a limit 100
  • a,b,c有联合索引，虽然没有遵循最左前缀匹配，但是前导列通过常量进行查询，如 where a = "Hollis" order by b

filesort排序 #

在进行排序时，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，它的大小是由sort_buffer_size控制的。

而根据sort_buffer_size的大小不同，会在不同的地方进行排序操作：

  • 如果要排序的数据量小于 sort_buffer_size，那么排序就在内存中完成。
  • 如果排序数据量大于sort_buffer_size，则需要利用磁盘临时文件辅助排序。

临时文件排序采用归并排序的算法，首先会把需要排序的数据拆分到多个临时文件里同步进行排序操作，然后把多个排好序的文件合并成一个结果集返回给客户端。

在磁盘上的临时文件里排序相对于在内存中的sort buffer里排序来说，会慢很多。

max_length_for_sort_data是 MySQL 中控制<用于排序的行数据的长度>的一个参数，默认值是1024字节。如果单行的长度超过这个值，MySQL就认为单行太大，那么就会采用rowid 排序，否则就进行全字段排序。

row_id 排序（也叫双路排序）

在构建sort_buffer的时候，不要把所有的要查询字段都放进去，只把排序字段这主键放进去就行了。

速度优先，内存优先、一次回表优先。

count(1)、count(*)与count(列名)的区别 #

COUNT(1) 和 COUNT(*)表示的是直接查询符合条件的数据库表的行数。而COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数。

注意count(1)不是指一行，数据库引擎会对每一行返回一个常量值 1，然后统计这些 1的数量

除了查询得到结果集有区别之外，在性能方面COUNT(*)是约等于COUNT(1)的。

COUNT(*)的目的只是为了统计总行数，所以，他根本不关心自己查到的具体值，所以，他如果能够在扫表的过程中，选择一个成本较低的索引进行的话，那就可以大大节省时间。

InnoDB中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。

所以，相比之下，非聚簇索引要比聚簇索引小很多，所以MySQL会优先选择最小的非聚簇索引来扫表。

所以，当我们建表的时候，除了主键索引以外，创建一个非主键索引还是有必要的。

至此，我们介绍完了MySQL数据库对于COUNT(*)的优化，这些优化的前提都是查询语句中不包含WHERE以及GROUP BY条件。

limit 0，100和limit 10000000，100一样吗？ #

不一样，这是mysql典型的深度分页问题。

MySQL的limit m n工作原理就是先读取前面m+n条记录，然后抛弃前m条，然后返回后面n条数据，所以m越大，偏移量越大，性能就越差。

所以，limit 10000000,100要比limit 0,100的性能差的多，因为他要先读取10000100条数据，然后再抛弃前面的10000000条。

limit优化 #

  • mysql通常更愿意执行全表扫描，但是如果你用limit只查询几行记录的话，mysql在某些情况下可能使用索引。

  • **如果你将 limit row_count子句与order by子句组合在一起使用，mysql会在找到排序结果的row_count行数据后立即停止排序，而不是对整个结果进行排序。**如果使用索引来完成排序，这将非常快。

    当ORDER BY子句使用了索引时，MySQL就能够基于已经排好序的索引树快速找到所需的前N行数据，而不需要对整个表进行全表扫描和排序了

    优先队列排序(堆排序)：

    • MySQL 使用一种特殊的数据结构（优先队列/堆）
    • 只需要维护一个大小为 LIMIT N的堆结构
    • 每次处理一行数据时，只与当前堆中的最差值比较

  • 如果LIMIT row_count与DISTINCT一起使用，一旦找到row_count惟一的行，MySQL就会停止。

    ? distinct一般需要处理所有符合条件的行才能确保正确去重，在没有索引优化的情况下，必须扫描所有的数据才能确定哪些行是唯一的。

    当distinct列上有合适的索引时，优化器可能利用索引的有序性提前终止。

  • LIMIT 0 可以快速返回一个空的结果集，这是用来检测一个查询是否有效的一种很有用的方法。

  • 如果ORDER BY不走索引，而且后面还带了LIMIT的话，那么优化器可能可以避免用一个合并文件，并使用内存中的filesort操作对内存中的行进行排序。

limit和order by #

当我们在查询的时候，对某个字段order by时，如果这个字段有一些重复值，那么MySQL会是可以自由的以任意顺序返回这些行记录的，并且根据总体的执行计划而有有不同的表现。也就是说，排序结果可能是不固定的。

sql语句中如何实现insertOrUpdate的功能？ #

ON DUPLICATE KEY UPDATE

这是 MySQL 特有的语法，用于实现"存在则更新，不存在则插入"的操作（通常称为 upsert）。

INSERT INTO table_name (id, column1, column2)
VALUES (1, 'value1', 'value2')
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1),
column2 = VALUES(column2);

需要注意：on duplicate key update会在前一个索引到当前值加临键锁，极容易造成死锁。

要使用 INSERT INTO … ON DUPLICATE KEY UPDATE 语句，需要满足以下条件：

  • 表必须有主键或唯一索引；
  • 插入的数据必须包含主键或唯一索引列；
  • 主键或唯一索引列的值不能为 NULL。

注意8.0版本之后已经修复主键跳跃问题

实现原理 #

  • 首先尝试执行普通的 INSERT 操作

  • 如果插入导致主键或唯一键冲突（即数据已存在）

  • 转而执行 UPDATE 操作，修改指定的列

替代方案 #

  • replace into ：存在则替换，不存在则插入（会导致主键跳跃）

binlog、redolog和undolog的区别？ #

binlog主要用来对数据库进行数据备份、崩溃恢复和数据复制等操作，redolog和undolog主要用于事务管理，记录的是数据的修改操作和回滚操作。redolog用来做恢复，undolog用来做回滚。

在mysql中，redolog和undolog只适用于innoDB存储引擎，因为要支持事务。而不适用于MyISAM和其他存储引擎。而binlog适用于所有存储引擎。

binlog是MySQL用于记录数据库中的所有DDL语句和DML语句的一种二进制日志。它记录了所有对数据库结构和数据的修改操作，如INSERT、UPDATE和DELETE等。binlog主要用来对数据库进行数据备份、灾难恢复和数据复制等操作。binlog的格式分为基于语句的格式和基于行的格式。

走索引还是很慢，可能是什么原因？ #

  • 索引选错了：走了索引也可能走错，当一个SQL可能走多个索引的时候，MySQL的优化器会选择一个，但是这个选择是可能选错的，如果选错了，那就可能会比较慢。

  • 数据分布不均匀：索引的效率与数据的分布密切相关。如果数据分布不均匀，就可能导致某些索引节点的数据量很大，而另外一些节点的数据量很少，从而使查询性能下降。

  • SQL语句存在问题：查询语句的优化非常重要，一些常用的优化技巧包括避免使用SELECT *、尽量避免多表join等。如果查询语句没有进行优化，就可能导致查询性能较差。

    性能问题

    • 数据传输量大：返回不必要的列会增加网络传输量
    • 内存消耗高：数据库服务器和应用服务器都需要处理更多数据
    • 索引利用率低：无法有效利用覆盖索引(covering index)
    • I/O压力大：需要读取更多数据页，特别是对于包含BLOB/TEXT等大字段的表

  • 数据库设计不合理：数据库结构的设计也是影响查询性能的一个重要因素。如果表的结构设计不合理，就可能导致查询需要扫描大量的数据才能得到结果，从而影响查询性能。

  • 系统硬件或者网络环境问题：最后，查询性能还可能受到系统硬件或者网络环境等因素的影响。如果系统硬件或者网络环境存在问题，就可能导致查询性能较差。

SQL执行计划分析的时候，要关注哪些信息？ #

下面是一次explain返回的一条SQL语句的执行计划的内容：

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+                                           
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |                                           
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+                                           
|  1 | SIMPLE      | t2    | NULL       | index | NULL          | idx_abc | 198     | NULL |    5 |    20.00 | Using where; Using index |                                           
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

一个执行计划中，共有12个字段，每个字段都挺重要的，先来介绍下这12个字段

  • id：执行计划中每个操作的唯一标识符。对于一条查询语句，每个操作都有一个唯一的id。但是在多表join的时候，一次explain中的多条记录的id是相同的。
  • select_type：操作的类型。常见的类型包括SIMPLE、PRIMARY、SUBQUERY、UNION等。不同类型的操作会影响查询的执行效率。
  • table：当前操作所涉及的表。
  • partitions：当前操作所涉及的分区。
  • type：表示查询时所使用的索引类型，包括ALL、index、range、ref、eq_ref、const、system等。
  • possible_keys：表示可能被查询优化器选择使用的索引。
  • key：表示查询优化器选择使用的索引。
  • key_len：表示索引的长度。索引的长度越短，查询时的效率越高。
  • ref：用来表示哪些列或常量被用来与key列中命名的索引进行比较。
  • rows：表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。
  • filtered：表示此操作过滤中保留的的行数占扫描行数的百分比。
  • Extra：表示其他额外的信息，包括Using index、Using filesort、Using temporary等。

是否支持emoji表情存储，如果不支持，如何操作？ #

MySQL中是可以存储emoji表情的，但是要使用UTF8MB4的字符编码才可以。如果是UTF8MB3的话，存储这些扩展字符的话会无法解析导致报错。

如何优化一个大规模的数据库系统？ #

  • 硬件优化：升级硬件是提高数据库性能的一种有效方式，包括增加内存、磁盘、CPU和网络带宽等。

  • 数据库设计：

    • 表结构优化：避免过度的多表join。将频繁联合查询的数据进行合理冗余
    • 数据归档：定期归档旧数据，减少表的大小

  • 查询优化：

    • SQL优化：优化查询语句，避免复杂子查询，避免多表join，避免深度分页。

      深度分页不是指数据量很大，而是指需要跳过大量记录才能到达目标页的查询场景。

      解决：

      • 基于游标分页
      -- 第一页
SELECT * FROM orders ORDER BY id DESC LIMIT 20;

-- 下一页（记住最后一条的id=12345）
SELECT * FROM orders WHERE id < 12345 ORDER BY id DESC LIMIT 20;

      • 基于覆盖索引优化

        SELECT t.* FROM (
SELECT id FROM orders
ORDER BY created_at DESC
LIMIT 10000, 20
) AS tmp
JOIN orders t ON tmp.id = t.id;

      • 业务限制

        限制最大页码（如最多显示100页）

        按时间或其他维度分区

        集成Elasticsearch等专业搜索工具

    • 查询计划分析：利用数据库的查询分析器，分析并优化执行计划。

  • 索引优化：

    • 创建合适的索引
    • 避免过多索引降低写入性能
    • 使用覆盖索引

  • 缓存机制

    • 将热点数据放入缓存中
    • 对象缓存：将经常访问的并且变化不频繁的对象（如用户信息）进行缓存。

  • 负载均衡：

    读写分离

    数据库集群

  • **分区和分片：**将数据分成多个区域或分片，可以降低单个节点的压力，提高整个系统的可扩展性和性能。

    • 分库分表

  • 数据备份和恢复：

  • 性能监控和调优：

    定期对数据库系统进行性能监控和调优，可以及时发现并解决性能问题。可以使用性能监控工具、数据库性能分析工具等来实现。

mysql只操作同一条记录，也会发生死锁吗？ #

会，因为数据库的锁锁的是索引，并不是记录。

当我们在事务中，更新一条记录的时候，如果用到普通索引作为条件，那么会先获取普通索引的锁，然后再尝试获取主键索引的锁。

那么这个时候，如果刚好有一个线程，已经拿到了这条记录的主键索引的锁后，同时尝试在该事务中去拿该记录的普通索引的锁。

这时候就会发生死锁。

为了避免这种死锁情况的发生，可以在应用程序中设置一个规定的索引获取顺序，例如，只能按照主键索引->普通索引的顺序获取锁，这样就可以避免不同的线程出现获取不同顺序锁的情况，进而避免死锁的发生（靠SQL保证）。

BEGIN;
SELECT * FROM accounts WHERE id = 100 FOR UPDATE; -- 先锁主键
SELECT * FROM accounts WHERE account_no = 'ACC123' FOR UPDATE; -- 再锁唯一索引
COMMIT;

FOR UPDATE是 SQL 中的一个关键锁定语句，主要用于在事务中获取行的排他锁​（X锁）。

数据库死锁如何解决？ #

数据库的死锁的发生通常由以下原因导致：

  • 资源竞争：多个事务试图同时访问相同的资源，如数据库表、行、页或锁。但是它们请求资源的顺序不同，导致互相等待。
  • 未释放资源：事务在使用完资源后未及时释放资源，导致其他事务无法获得所需的资源。这可能是由于程序中的错误或异常情况引起的。
  • 不同事务的执行速度不同：如果一个事务在获取资源后执行速度很慢，而其他事务需要等待该事务释放资源，那么可能会导致其他事务超时，从而发生死锁。
  • 操作的数据量过大：在持有锁的同时，又请求获取更多的锁，导致互相等待。

如何解决？ #

  • 大部分现代数据库管理系统在检测到死锁时会自动干预。它们通常选择回滚一个或多个事务来打破死锁。
  • 除了自动干预外，很多 DBMS 也支持手动强制回滚某些事务来解决死锁。比如 navicat 解决死锁的办法：https://www.cnblogs.com/xbdeng/p/16541111.html
  • 还有就是如果你什么都不做，MySQL 自己也可以解决死锁，一种是立刻解决，一种是延迟解决。
    • 如果MySQL开启了死锁检测（innodb_deadlock_detect = on ），那么他会定时的检测死锁，在检测到死锁后，MySQL将自动选择并终止事务中的一个或多个事务来解决死锁。
    • 如果设置事务等待锁的超时时间（innodb_lock_wait_timeout）。当一个事务的等待获取锁的时长超过这个阈值的时候，会对这个事务进行回滚，这样也能解决死锁。

如何避免死锁？ #

  • 减少锁的数量：比如使用RC来代替RR来避免因为gap锁和next-key锁而带来的死锁情况。
  • 减少锁的时长：加快事务的执行速度，降低执行时间，也能减少死锁发生的概率。
  • 固定顺序访问数据：事务在访问同一张表时，应该以相同的顺序获取锁，这样可以避免死锁的发生。
  • 减少操作的数据量：尽量减少事务操作的数据量，尽量减少事务的持有时间，这样可以降低死锁的发生几率。

索引失效问题如何排查？ #

一般在出现慢SQL时考虑

  • 找到相关SQL语句

  • 使用explain查看它的执行计划，关注：type、key和extra字段

    • key一定要有值，不能是NULL

      表示实际使用的索引

    • type应该是ref、eq_ref、range、const等这几个

      type：访问类型

      • system：表只有一行记录（等于系统表）
      • const：通过主键或者唯一索引就能找到，如：where id =1
      • eq_ref:联表查询时，使用主键或唯一索引关联
      • ref:使用唯一索引查找
      • range:使用索引范围查找
      • index:全索引扫描
      • ALL：全表扫描

    • extra的话,如果是NULL，或者using index，using index condition都可以的

      表示额外的信息

如果通过执行计划之后，发现一条SQL没有走索引，比如 type = ALL， key = NULL , extra = Using where

那么就要进一步分析没有走索引的原因了。我们需要知道的是，到底要不要走索引，走哪个索引，是MySQL的优化器决定的，他会根据预估的成本来做一个决定。

可能的情况：

  • 没有正确创建索引

  • 索引区分度不高：mysql认为走索引效率比不走差

  • 表太小：也认为不走索引快

  • 查询语句中，索引字段因为用到了函数、类型不一致等导致了索引失效

    CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `age` (`age`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    • select * from mytable where age +1 = 12;   不走索引
select * from mytable where age = 12 - 1;   走索引

    • SELECT * FROM mytable WHERE create_time = '2023-04-01 00:00:00'; 走索引
SELECT * FROM mytable WHERE YEAR(create_time) = 2022; 不走索引

    • SELECT * FROM mytable WHERE name = 'Hollis' and age > 18; 走索引
SELECT * FROM mytable WHERE name = 'Hollis' OR age > 18;  不走索引

可能的执行计划：
MySQL 需要找出 name = 'Hollis'或 age > 18的所有记录
虽然 name和 age都有索引，但 MySQL 通常不会同时使用两个单列索引
优化器可能的选择：
使用 name索引找出 name = 'Hollis'的记录（1条）
使用 age索引找出 age > 18的记录
合并两个结果集（需要去重）
这种"索引合并"操作成本可能高于全表扫描，因此优化器可能选择全表扫描

mysql> explain SELECT * FROM mytable WHERE name = 'Hollis' OR age = 18;  走索引

    • SELECT * FROM mytable WHERE name like '%Hollis%'; 不走
SELECT * FROM mytable WHERE name like '%Hollis';  不走
SELECT * FROM mytable WHERE name like 'Hollis%';  走
SELECT * FROM mytable WHERE name like 'Holl%is';  走

    • select * from mytable where name = 1;   不走，字段类型转换
select * from mytable where age = '1';  走，值的类型转换

    • SELECT * FROM mytable WHERE age != 18;
可能走，也可能不走，根据数据分布情况等有关

    • SELECT * FROM mytable WHERE name is not null    索引失效
name是唯一索引，这意味着表中所有记录的 name字段都不为 NULL，每一行都满足 name IS NOT NULL条件。虽然存在索引，但由于查询条件对每行都成立，使用索引没有意义。

    • SELECT * FROM mytable order by age    可能走，可能不走
如果数据库中数据量少，则会走全表在内存中排序，不适用索引

    • select * from mytable where name in ("Hollis");  走
select * from mytable where name in ("Hollis","hsh");  走
select * from mytable where name in ("Hollis","hsh","s");  不走
使用in的时候，有可能走索引，也有可能不走，一般在in中的值比较少的时候可能会走索引优化，但是如果选项比较多的时候，可能会不走索引：

如何进行SQL调优？ #

一般一个SQL慢，可能有以下几种原因：

  • 索引失效
  • 多表join
  • 字段太多
  • 表中数据量太大
  • 索引区分度不高
  • 数据库连接数不够
  • 数据库的表结构不合理
  • 数据库IO或者CPU比较高
  • 数据库参数不合理
  • 事务比较长
  • 锁竞争导致的等待
  • 深分页问题

区分度不高的字段建索引一定没用吗？ #

不一定

如果有一种特殊情况，如男女比例是95:5，那么，这时候，如果我用"女’作为性别的查询条件的话，还是可以走索引，并且有很大的性能提升的，原因就是因为他可以过滤掉大部分数据。走索引可以大大提升效率。

这种一般在任务表中比较多，比如任务表中有状态，两种情况：INIT和SUCCESS，大多数情况下，任务都是SUCCESS的，只有一少部分是INIT，这时候就可以给这个字段加索引。这样当我们扫描任务表执行任务的时候，还是可以大大提升查询效率的。

慢SQL的问题如何排查？ #

  • 先打印慢SQL日志
  • 定位问题，使用explain
  • 解决问题

MySQL的主从复制过程 #

mysql的主从复制，是基于binlog实现的，主要流程如下：

  • 从服务器开启主从复制后，会创建出两个线程：I/O线程和SQL线程

  • 从服务器的I/O线程会尝试和主服务器建立连接，相对应主服务器中也有一个binlog dump线程，专门来和从服务的I/O线程做交互。

  • 从服务器的I/O线程会告诉主服务的dump线程自己要从什么位置开始接收binlog

  • 主服务器在更新过程中，将更改记录保存到自己的binlog中，根据不同的binlog格式，记录的内容可能不一样。

  • 在dump线程检测到binlog变化时，会从指定位置开始读取内容，然后会被slave的I/O线程把他拉取过去。

    这里需要注意，有些资料上面说这里是主服务器向从服务器推的，但是，实际上是从服务器向主服务器拉的。（https://dev.mysql.com/doc/refman/8.0/en/replication-implementation.html ）

    拉的模式，从库可以自行管理同步进度和处理延迟。

  • 从服务器的I/O线程接收到通知事件后，会把内容保存在relay log中。

  • 从服务器还有一个SQL线程，他会不断地读取他自己的relay log中的内容，把他解析成具体的操作，然后写入到自己的数据表中。

复制方式 #

  • 异步复制：这是MySQL默认的复制方式，在异步复制的方式中主库在执行完事务操作以后，会立刻给客户端返回。他不需要关心从库是否完成该事务的执行。

    这种方式会导致一个问题，那就是当主库出现故障时，主库虽然事务执行完了，但是可能还没来得及把数据同步给从库，就挂了。那么当从库升级为主库之后，他会丢失了这次事务的变更内容。

  • **全同步复制：**全同步复制的这个方式中，当主库执行完一个事务之后，他会等待所有的从库完成数据复制之后，才会给客户端反馈。

    这种方式安全性可以保障了，但是性能很差。如果从库比较多的话，会导致整个过程更加长。

  • **半同步复制：**半同步复制是介于全同步复制和异步复制之间的一种方案。他在执行完一个事务之后，也不会立刻给客户端反馈，但是也不会等所有从库都完成事务，而是等其中一个从库完成接收到事件之后，再反馈给客户端。

    在半同步复制这个方案中，会在事务提交的2阶段都完成之后，等待从库接收到binlog，然后再返回成功。

介绍一下InnoDB的数据页，和B+树的关系是什么？ #

InnoDB的数据页是InnoDB存储引擎中用于存储数据的基本单位。它是磁盘上的一个连续区域，通常大小为16KB当然，也可以通过配置进行调整。16KB就意味着Innodb的每次读写都是以 16KB 为单位的，一次从磁盘到内存的读取的最小是16KB，一次从内存到磁盘的持久化也是最小16KB。

B+树的每个节点都对应着一个数据页，包括根节点、非叶子节点和叶子节点。B+树通过节点之间的指针连接了不同层级的数据页，从而构建了一个有序的索引结构。（文中都以聚簇索引为例。）

所以，数据页是存储数据行的实际物理空间，以页为单位进行磁盘读写操作。B+树通过节点和指针的组织，构建了一个层次结构的索引，用于快速定位和访问数据行。

B+树的非叶子节点对应着数据页，其中存储着主键+指向子节点（即其他数据页）的指针。B+树的叶子节点包含实际的数据行，每个数据行存储在一个数据页中。

通过这种方式，InnoDB利用B+树和数据页的组合，实现了高效的数据存储和检索。B+树提供了快速的索引查找能力，而数据页提供了实际存储和管理数据行的机制。它们相互配合，使得InnoDB能够处理大规模数据的高效访问。

Mysql的驱动表是什么？Mysql怎么选的？ #

驱动表是表连接中的基础表，也就是通过驱动表的数据结果集作为循环基础数据，然后一条一条的通过这个结果集的数据作为过滤条件到被驱动表中查询数据，然后再做合并。那么，也就意味着：驱动表在SQL语句执行的过程中先读取。而被驱动表在SQL语句执行的过程中后读取。

当我们知道MySQL的join的原理之后，其实就可以很容易的知道，驱动表的选择会决定着一条SQL的执行效率。所以，一条SQL中，该使用哪张表作为驱动表，其实是优化器决定的。

MySQL的优化器选择驱动表的原则是：

  • 表大小：这个很容易理解，小表作为驱动表可以更快地被扫描和匹配。所以优化器倾向于选择较小的表作为驱动表。

    如果两个表都没有索引，那么都是全表扫描，在nested loop join下就是笛卡尔积， 那么小表驱动大表就是差不多的。

    前提是大表有索引

    小表不是数量最少，因为还有筛选条件，筛选后的数据最少。

  • 索引：在MySQL中，索引能大大的影响SQL的查询效率，所以选择可以利用索引进行加速访问的表作为驱动表可以提升效率。

  • where条件：如果查询中包含过滤条件，优化器会选择能够使用过滤条件进行筛选的表作为驱动表，以减少后续的匹配操作。

  • 连接类型：根据连接类型，INNER JOIN、LEFT JOIN、RIGHT JOIN等，优化器可能会做一些选择。比如left join会选择左表作为驱动表，主要是因为LEFT JOIN要返回左表中的所有记录，而右表中的匹配记录是可选的。通过以左表作为驱动表，可以确保返回左表中的所有记录。

    • left join： 左表是驱动表，右表是被驱动表
    • right join： 右表是驱动表，左表是被驱动表
    • inner join： 表数据量较小的表会由mysql自动选择作为驱动表

如何判断那张表是驱动表 #

explain看一下SQL执行计划，在输出的执行计划中，排在第一行的表是驱动表，排在第二行的表是被驱动表。

left join一定是左作为驱动表吗？ #

不一定，由优化器决定。

也可以强制制定表连接顺序，不允许优化器优化。

MySQL的hash Join是什么？ #

hash join是mysql 8.0.18版本新推出的一种多表join算法。

在这之前，mysql 使用嵌套循环的方式去关联查询。而嵌套循环的算法其实性能是比较差的，而Hash Join的出现就是要优化Nested-Loop Join的。

嵌套循环工作原理

  1. 外层循环：遍历第一个表（通常称为外部表或驱动表）的每一行
  2. 内层循环：对于外层表的每一行，遍历第二个表（内部表）的所有行，寻找匹配的行

所谓Hash join，其实是因为它底层用到了hash表。他的基本思想是将驱动表数据加载到内存，并建立 hash 表，这样只要遍历一遍非驱动表，然后再去通过哈希查找在哈希表中寻找匹配的行 ，就可以完成 join 操作了。

先查询驱动表数据，建立哈希表；再遍历非驱动表，若哈希存在，则匹配。

基于磁盘的hash join #

如果驱动表中的数据量比较大， 没办法一次性的加载到内存中，就需要考虑把这些数据存储在磁盘上。通过将哈希表的一部分存储在磁盘上，分批次地加载和处理数据，从而减少对内存的需求。

在这样的算法中，为了避免一个大的hash表内存中无法完全存储，那么就采用分表的方式来实现，即首先利用 hash 算法将驱动表进行分表，并产生临时分片写到磁盘上。

这样就相当于把一张驱动表，拆分成多个hash表，并且分别存储在磁盘上。

接下来就是做join了，在这个过程中，会对被驱动表使用同样的 hash 算法进行分区，确定好在哪个分区之后，先确认下这个分区是否已经加载到内存，如果已经加载，则直接在内存中的哈希表中进行查找匹配的行。

如果哈希值对应的分区尚未加载到内存中，则从磁盘上读取该分区的数据到内存中的哈希表，并进行匹配。

重点在驱动表和非驱动表都要进行分区。

MySQL执行大事务会存在什么问题？ #

一般指事务中要执行的SQL很多，事务的时间比较长。

  • 占用数据库连接
  • 难以回滚：由于大事务涉及的数据量较大，执行回滚操作可能会变得非常耗时。如果事务需要回滚或失败，可能需要花费很长时间才能完全回滚所有修改，这会对数据库的可用性和性能造成负面影响。
  • 导致主从延迟：主数据库执行时间长，从数据库肯定也长。
  • 锁竞争：大事务的话，写操作多了就可能要锁定许多数据。这可能导致其他并发事务在访问相同资源时遇到锁竞争，从而导致性能下降和延迟增加。长时间的锁定还可能导致其他事务的等待和阻塞。
  • 日志空间占用：大事务会生成大量的日志，尤其是binlog，当单个事务最大允许使用的Binlog文件的大小超过了max_binlog_cache_size时，会导致报错
  • 对MVCC影响：大事务会导致MVCC中旧版本的数据持续存在，影响数据库的Purging，从而影响整体性能。
  • 影响索引覆盖：当一个长事务修改一个表时，可能会导致其他事务对该表的查询不使用覆盖索引技术。

MySQL怎么做热点数据高效更新？ #

拿库存扣减举例

  • 库存拆分，把一个大的库存拆分成多个小库存，拆分后，一次扣减动作就可以分散到不同的库、表中进行，降低锁粒度提升并发。

    优点：实现较简单

    缺点：存在碎片问题、库存调控不方便

  • 请求合并（缓冲记账），把多个库存扣减请求，合并成一个，进行批量更新。

    优点：简单

    缺点：适用于异步场景，或者经过分析后认为可以合并的场景

  • 把update转换成insert，直接插入一次占用记录，然后异步统计剩余库存，或者通过SQL统计流水方式计算剩余库存。

    优点：没有update，无锁冲突

    缺点：insert时控制不好容易超卖、insert后剩余库存不好统计

改造MySQL #

主要思路就是，针对于频繁更新或秒杀类业务场景，大幅度优化对于热点行数据的update操作的性能。当开启热点更新自动探测时，系统会自动探测是否有单行的热点更新，如果有，则会让大量的并发 update 排队执行，以减少大量行锁造成的并发性能下降。

也就是说，他们改造了MySQL数据库，让同一个热点行的更新语句，在执行层进行排队。这样的排队相比update的排队，要轻量级很多，因为他不需要自旋，不需要抢锁。

这个方案的好处就是开发不需要做额外的事情，只需要开启热点检测就行了。缺点就是改造MySQL数据库有成本。不过现在很多云上数据库都支持了。

就是手动让update排队，减少锁竞争。

SQL中的PK、UK、CK、FK、DF是什么意思？ #

K是Key的意思，就是代表约束的，所以PK、UK这些都是代表不同类型的约束：

PK：Primary Key ，主键约束

UK：Unique Key， 唯一约束

CK： check()， 检查约束

FK：Foreign Key， 外键约束

DF：default ，默认约束

什么是buffer pool？ #

我们都知道，MySQL的数据是存储在磁盘上面的（Memory引擎除外），但是如果每次数据的查询和修改都直接和磁盘交互的话，性能是很差的。

于是，为了提升读写性能，Innodb引擎就引入了一个中间层，就是buffer pool。

buffer是在内存上的一块连续空间，他主要的用途就是用来缓存数据页的，每个数据页的大小是16KB。

页是Innodb做数据存储的单元，无论是在磁盘，还是buffe pool中，都是按照页读取的，这也是一种’预读’的思想。

有了buffer pool之后，当我们想要做数据查询的时候，InnoDB会首先检查Buffer Pool中是否存在该数据。如果存在，数据就可以直接从内存中获取，避免了频繁的磁盘读取，从而提高查询性能。如果不存在再去磁盘中进行读取，磁盘中如果找到了的数据，则会把该数据所在的页直接复制一份到buffer pool中，并返回给客户端，后续的话再次读取就可以从buffer pool中就近读取了。

当需要修改的时候也一样，需要先在buffer pool中做修改，然后再把他写入到磁盘中。

但是因为buffer pool是基于内存的，所以空间不可能无限大，他的默认大小是128M，当然这个大小也不是完全固定的，我们可以调整，可以通过修改MySQL配置文件中的innodb_buffer_pool_size参数来调整Buffer Pool的大小。

就是一块 innodb和磁盘之间的一个缓冲区，默认128M大小

buffer pool和query cache的区别 #

  • Buffer Pool用于缓存表和索引的数据页，从而加速读取操作；
  • Query Cache用于缓存查询结果，减少重复查询的执行时间。

Query Cache是位于Server层的优化技术，而Buffer Pool 是位于引擎层的优化技术。

buffer pool的读写过程是怎么样的？ #

MySQL的Buffer Pool是一个内存区域，用于缓存数据页，从而提高查询性能。读写过程涉及到数据的从磁盘到内存的读取，以及在内存中的修改和写回磁盘。

读过程 #

  • MySQL首先检查Buffer Pool中是否存在本次查询的数据。如果数据在Buffer Pool中，就直接返回结果。
  • 如果数据不在Buffer Pool中，MySQL会从磁盘读取数据。
  • 读取的数据页被放入Buffer Pool，同时MySQL会返回请求的数据给应用程序。

写过程 #

当我们执行一次更新语句，如INSERT、UPDATE或DELETE等时，会进行以下过程

  • 当应用程序执行写操作时，MySQL首先将要修改的数据页加载到Buffer Pool中。
  • 在Buffer Pool中，对数据页进行修改，以满足写请求。这些修改只在内存中进行，不会立即写回磁盘。
  • 如果Buffer Pool中的数据页被修改过，MySQL会将这个页标记为“脏页”（Dirty Page）。
  • 脏页被写回磁盘，此时写入操作完成，数据持久化。

但是需要注意的是，脏页写回磁盘是由一个后台线程进行的，在MySQL服务器空闲或负载较低时，InnoDB会进行脏页刷盘，以减少对用户线程的影响，降低对性能的影响。

当脏页的百分比达到innodb_max_dirty_pages_pct_lwm变量定义的低水位标记时，将启动缓冲池刷新。缓冲池页的默认低水位标记为10%。innodb_max_dirty_pages_pct_lwm值设为0会禁用这种提前刷新行为。

InnoDB还使用了一种适应性刷新算法，根据redo log的生成速度和当前的刷新率动态调整刷新速度。其目的是通过确保刷新活动与当前工作负载保持同步，来平滑整体性能。

当然，我们也可以手动触发脏页的刷新到磁盘，例如通过执行SET GLOBAL innodb_buffer_pool_dump_now=ON 来进行一次脏页刷新。

还有一种情况，就是在MySQL服务器正常关闭或重启时，所有的脏页都会被刷新到磁盘。这样才能保证数据可以持久化下来。

MySQl自增主键用完了会怎么样？ #

如果是我们自己显式定义的一个自增ID，如果已经达到了上限，那么下一次申请ID的时候，得到的值就是那个最大值，后续也不会再增加。这时候我们会拿到一个已经用过的主键，如果继续插入的话，会报主键冲突。

那如果我们没有自定义自增ID，那么就会默认使用row_id，如果已经达到了上限，那么下一次申请ID的时候，得到的值会从0开始，然后继续重新自增。但是，这种情况如果我们因为没有设置主键，所以他不会报主键冲突，他会直接把这个row_id = 0的数据插入到数据库中，并且会把之前的row_id=0的数据给直接覆盖了。

如何处理 #

归档旧数据（推荐）：

如果表中的一些数据是历史数据，不再经常访问，可以将其归档到另一个表中，然后从原表中删除这些数据。这可以为新数据释放主键空间。

执行计划中，key有值，还是很慢怎么办？ #

执行计划中，key有值，并且type=index，这时候很多人认为是走了索引的。

当我们执行执行计划查看一个SQL的执行过程的时候，通常会见到以下这样的执行计划：

+----+-------+---------------+----------+--------------------------+                                           
| id | type  | possible_keys | key      | Extra                    |                                           
+----+-------+---------------+----------+--------------------------+                                           
|  1 | index | NULL          | idx_abcd | Using where; Using index |                                           
+----+-------+---------------+----------+--------------------------+

这个执行计划中，type=index,key=idx_abcd很多人会认为这表示这条SQL走了索引，但是其实这么理解是不对的。

如果是走了索引Extra中的内容应该是Using index 而不是Using where; Using index

以上的这个执行计划表明，这个SQL确实用到了idx_abcd的这个索引树，但是他并没有直接通过索引进行匹配或者范围查询，而是扫描了整颗索引树。

所以，type=index 意味着进行了全索引扫描， 会遍历索引树来查找匹配的行，这个效率比扫表扫描快一些，但是很有限，和我们通常意义上理解的走了索引 是两回事儿。

遇到这种情况，大概率是因为没有遵守最左前缀匹配导致的索引失效了。所以需要调整查询语句，或者修改索引来解决。

数据库乐观锁的过程中，完全没有加任何锁吗？ #

在使用乐观锁的时候，我们没有显式的加锁，也没有用到对他的相关锁机制。但是乐观锁是使用update语句过程中实现的，update的过程是有锁的。数据库在更新时，会根据where条件对索引添加行级锁（可能还有gap 或者 next key）

所以，乐观锁的过程中，并不是完全无锁的。

那么，乐观锁既然也有锁，那么他相比悲观锁意义在哪里呢？

乐观锁最大的好处就是通过CAS的方式做并发校验，这个过程不需要提前加锁，只需要在更新的那一刻加一个短暂的锁而已，而悲观锁的话，需要你先select for update，锁的时长要长得多。

  • 乐观锁：只在UPDATE语句执行时数据库内部短暂加锁（通常几毫秒）锁持有时间 = UPDATE语句执行时间

    适合读多写少

  • 悲观锁：从SELECT FOR UPDATE到COMMIT的整个事务期间都持有锁锁持有时间 = 整个事务执行时间（包括应用层处理时间，可能几百毫秒到几秒）

    适合写多读少

MySQL的binlog有几种格式？ #

binlog主要用来对数据库进行数据备份、灾难恢复和数据复制等操作。

  • statement

    记录的是SQL原文，也就是说在数据库中执行的SQL会原封不动的记录到binlog中。

    问题：会导致主从不一致

    例如：比如说，当我们使用DELETE或者UPDATE的时候，指定了LIMIT，但是并没有使用order by，那么最终这条语句在主库和从库上的执行结果可能是不一样的（即使同一个库上面，你执行多次结果可能也不一样）。

  • row

    记录每个数据更改的具体行的细节。

    问题：占用空间

  • mixed

    这种其实就是把statement和row结合了，MySQL会根据SQL的情况，自动在row和statement中互相切换选择一个他认为合适的格式进行记录。

    但是，在RR（可重复读）下，row和statement都可以生效，但是在RC（读已提交）下，只有row格式才能生效。

MySQL的并行复制原理 #

在MySQL的主从复制中，我们介绍过MySQL的主从复制的原理，在复制过程中，主库的binlog会不断地同步到从库，然后从库有一个SQL线程不断地拉取并重放这些SQL语句，那么，一旦日志内容太多的话，一个线程执行就会有延迟，就会导致主从延迟。

待补充

MySQL的深度分页如何优化？ #

深度分页问题是指在数据库查询中，当你尝试访问通过分页查询返回的结果集的后面部分（即深层页码）时遇到的性能问题。

  • 使用子查询和JOIN优化

    SELECT c1, c2, cn... FROM table WHERE name = "Hollis" LIMIT 1000000,10

    我们可以基于子查询进行优化，如以下SQL：

    SELECT c1, c2, cn...
FROM table
INNER JOIN (
    SELECT id
    FROM table
    WHERE name = "Hollis"
    ORDER BY id
    LIMIT 1000000, 10
) AS subquery ON table.id = subquery.id

    这个查询由两部分组成：

    1. 主查询：从 table中选择列 c1, c2, cn...
    2. 子查询：从同一张 table中筛选 name = "Hollis"的记录，按 id排序，跳过前 100 万条，取接下来的 10 条

    • 首先执行子查询：筛选所有 name = "Hollis"的记录按 id排序跳过前 1,000,000 条记录只保留接下来的 10 条记录的 id值

    • 然后主查询：将主表与子查询结果（10 个 id）进行内连接返回匹配行的指定列

  • 使用子查询和ID过滤优化

    和上面的方法类似，我们还可以把SQL优化成：

    SELECT c1, c2, cn...
FROM table
WHERE name = "Hollis"
  AND id >= (SELECT id FROM table WHERE name = "Hollis" ORDER BY id LIMIT 1000000, 1)
ORDER BY id
LIMIT 10

    这个查询由两部分组成：

    1. 子查询：获取第1,000,000条记录的id值
    2. 主查询：基于该id值获取接下来的10条记录

  • 记录上一个ID

    还有一种方式，是上面这个方式的变种，就是如果能提前预估要查询的分页的条件的话，是可以很大程度提升性能的。比如记住上一页的最大ID，下一页查询的时候，就可以可以根据id >　max_id_in_last_page 进行查询。

什么是数据库的主从延迟，如何解决？ #

数据库的主从延迟是指在主从数据库复制过程中，从服务器（Slave）上的数据与主服务器（Master）上的数据之间存在的时间差或延迟。

原因：

  • 网络延迟
  • 从节点性能问题
  • 复制线程不够：当从节点只有一个线程，或者线程数不够的时候，数据回放就会慢，就会导致主从节点的数据延迟。
  • 大事务：如果出现了一个事务，特别长，执行过程特别慢，那么这个SQL在slave节点上重放的时候也会比较慢，也可能会出现延迟。

如何解决：

  • 优化网络
  • 提高从服务器性能
  • 并行复制
  • 避免大事务

为什么MySQL8.0要取消查询缓存？ #

MySQL的查询缓存是一种数据库性能优化技术，它允许MySQL在执行SELECT时将查询结果缓存起来，以便在以后相同的查询被再次执行时，可以直接返回缓存的结果，而不必再次重新解析和执行该语句。

缺点 #

  • 频繁失效

    查询缓存是以表级别为单位进行管理的，这意味着如果任何表中的数据发生变化，与该表相关的所有查询缓存都将被清除。这导致了缓存的频繁失效，减少了其效用。

  • 内存开销

  • 不一致性：

    有时查询结果可能会因为数据库中的数据更改而不再与缓存的结果匹配，这可能导致不一致性的问题。

  • 查询分布不均匀

    在某些情况下，查询缓存可能会导致性能下降，因为它不能很好地应对不均匀的查询分布。（部分频繁查询需要频繁更新缓存，不频繁的查询又无法命中缓存）

MyISAM 的索引结构是怎么样的，它存在的问题是什么？ #

和InnoDB最大的不同，MyISAM是采用了一种索引和数据分离的存储方式，也就是说，MyISAM中索引文件和数据文件是独立的。

因为文件独立，所以在MyISAM的索引树中，叶子节点上存储的并不是数据，而是数据所在的地址。所以，MyISAM 存储引擎实际上不支持聚簇索引的概念。在 MyISAM 中，所有索引都是非聚簇索引。

也就是说，在MyISAM中，根据索引查询的过程中，必然需要先查到数据所在的地址，然后再查询真正的数据，那么就需要有两次查询的过程。

MySQL中like的模糊查询如何优化？ #

在MySQL中，使用like进行模糊查询，在一定情况下是无法使用索引的。

  • 当like值前后都有匹配符时%abc%，无法使用索引

  • 当like值前有匹配符时%abc，无法使用索引

    • 字段逆序去查询

    • 虚拟列（记得有这么个东西就行）

      虚拟列(Generated Column)：是一种不实际存储数据的列，它的值是根据表中其他列的值计算得出的。

      两种类型：

      • STORED：计算结果实际存储在表中
      • VIRTUAL：不存储，只在读取时计算（默认类型）

  • 当like值后有匹配符时abc%，可以使用索引

数据库如何做加密解密？ #

很多时候，我们的数据库表中会存储很多敏感信息，如用户的手机号、身份证号、密码之类的，这些数据如果不做好加密的话，一旦数据泄漏就会导致重要信息泄露。

一般来说都需要对敏感字段进行加密，然后再在数据库中保存加密后的数据，这样即使被拖库也没关系，比如攻击者拿到的只是加密后的密码，并不知道真实密码是什么。

  • 服务端加解密

    服务端加解密指的就是数据库在存入数据库之前就加密好，然后再从数据库取出之后进行解密。这样可以保证数据库的数据绝对安全，因为数据库也不知道明文到底是什么。

  • 数据库加密函数

    MySQL提供了一些内置的加密函数，我们可以直接使用这些加密函数进行数据加密

    • AES_ENCRYPT 和 AES_DECRYPT，这两个函数是对称加密算法
    • ENCRYPT： ENCRYPT(str, salt) 函数使用UNIX crypt()函数对字符串 str 进行加密，其中 salt 是一个2字符的随机数。
    • MD5 和 SHA1
    • PASSWORD： PASSWORD(str) 函数将字符串 str 加密为MySQL原生密码散列。
    • DES_ENCRYPT 和 DES_DECRYPT，需要注意：DES算法不够安全，通常不建议使用

  • InnoDB静态加密

    待补充

加密缺点：

  • 性能开销
  • 复杂性
  • 查询和检索困难
  • 密钥管理问题

数据库加密后怎么进行模糊查询？ #

  • 先解密再查询

  • 数据库解密函数

    加密的时候如果用了函数的话，解密的时候我们也可以借助函数来做解密，同时做模糊查询

    这个方案适合于表中数据量不大，或者查询条件中还有其他查询字段可以走索引的情况。

  • 明文分词

    分词加密，分词查询

where条件的顺序影响使用索引吗？ # 

SELECT * FROM my_table WHERE a = 'value' AND b = 'value2';
SELECT * FROM my_table WHERE b = 'value2' AND a = 'value';

没啥影响的，也就是说WHERE 子句后面多个字段的先后顺序通常不会影响查询的结果。MySQL查询的WHERE子句只是用来过滤满足指定条件的行，而不涉及字段之间的顺序。

什么是MySQL的字典锁？ #

字典锁，英文名叫Meta Data Lock，也叫MDL锁，**他是一种用于管理元数据的锁机制，而不是数据本身的锁。**由数据库引擎（如 InnoDB）自动加锁/释放，开发者无需手动操作。

  • 作用：保护数据字典（表结构、索引等元数据）的并发一致性。
  • 场景：当执行 ALTER TABLE、DROP TABLE等 DDL 语句时自动触发。