MySQL-核心知识要点

1、索引的数据结构-B+tree

B+Tree的优势：

B+树的内节点无data，一个节点可以存储更多的K-V对。在构造树时，需要的内节点会更少，那么树的层级也会越低。查询一条数据时，1. 扫描的层级低，扫描过的节点更少，2. 过程更加稳定。
每一层的节点与相邻节点有指针相互连接（双向），数据可顺序逆序、跨页查找，对于范围查询更友好
B+Tree的缺点：内节点的K-V对会冗余，多占用空间。

B+树一般有两到三层，由于其高扇出，三层就能支持2kw以上的数据，且一次查询最多1~3次磁盘IO，性能也还行。

存储同样量级的数据，B树比B+树层级更高，因此磁盘IO也更多，所以B+树更适合成为mysql索引。

 

MySQL单表数据为何限制在千万级别，超过千万会有什么问题？

 

上面user表数据，在硬盘上其实也是类似，放在了user.ibd文件下。含义是user表的innodb data文件，又叫表空间。

虽然在数据表里，它们看起来是挨在一起的。但实际上在user.ibd里他们被分成很多小份的数据页，每份大小16k。

类似于下面这样。

也就是说

同样一个16k的页，非叶子节点里每一条数据都指向一个新的页，而新的页有两种可能。

• 如果是末级叶子节点的话，那么里面放的就是一行行record数据。
• 如果是非叶子节点，那么就会循环继续指向新的数据页。

假设

• 非叶子结点内指向其他内存页的指针数量为x
• 叶子节点内能容纳的record数量为y
• B+树的层数为z

那这棵B+树放的行数据总量等于 (x ^ (z-1)) * y。

x值计算：

非叶子节点里主要放索引查询相关的数据，放的是主键和指向页号。

主键假设是bigint（8Byte），而页号在源码里叫FIL_PAGE_OFFSET（4Byte），那么非叶子节点里的一条数据是12Byte左右。

整个数据页16k， 页头页尾那部分数据全加起来大概128Byte，加上页目录毛估占1k吧。那剩下的15k除以12Byte，等于1280，也就是可以指向x=1280页。

我们常说的二叉树指的是一个结点可以发散出两个新的结点。m叉树一个节点能指向m个新的结点。这个指向新节点的操作就叫扇出（fanout）。

而上面的B+树，它能指向1280个新的节点，恐怖如斯，可以说扇出非常高了。

y的计算：

叶子节点和非叶子节点的数据结构是一样的，所以也假设剩下15kb可以发挥。

叶子节点里放的是真正的行数据。假设一条行数据1kb，所以一页里能放y=15行。

行总数计算

回到 (x ^ (z-1)) * y 这个公式。

已知x=1280，y=15。

假设B+树是两层，那z=2。则是(1280 ^ (2-1)) * 15 ≈ 2w

假设B+树是三层，那z=3。则是(1280 ^ (3-1)) * 15 ≈ 2.5kw

这个2.5kw，就是我们常说的单表建议最大行数2kw的由来。毕竟再加一层，数据就大得有点离谱了。三层数据页对应最多三次磁盘IO，也比较合理。

单页16K为何这样设计？

为了提高索引查询效率和降低磁盘I/O的频率，MySQL设置了16KB的单页大小。这是因为在MySQL中：

内存大小限制：MySQL的索引需要放在内存中进行查询，如果页面过大，将导致索引无法完全加载到内存中，从而影响查询效率。

磁盘I/O限制：当需要查询一个索引时，MySQL需要把相关的页面加载到内存中进行处理，如果页面过大，将增加磁盘I/O的开销，降低查询效率。

索引效率限制：在B+树数据结构中，每个叶子节点存储着一个索引条目，因此如果每个页面能够存放更多索引条目，就可以减少B+树结构的深度，从而提高索引查询效率。

综上所述，MySQL索引单页大小设置为16KB可以兼顾内存大小、磁盘I/O和索引查询效率等多方面因素，是一种比较优化的方案。需要注意的是，对于某些特殊的应用场景，可能需要根据实际情况对单页大小进行调整。

2、MVCC

MVCC，全称 Multi-Version Concurrency Control，即多版本并发控制

MVCC机制具有以下优点：

• 提高并发性能：读操作不会阻塞写操作，写操作也不会阻塞读操作，有效地提高数据库的并发性能。
• 降低死锁风险：由于无需使用显式锁来进行并发控制，MVCC可以降低死锁的风险。

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。

参考：深入浅出：MVCC详解-CSDN博客

3、隔离级别

为了解决并发事务存在的脏读、不可重复读、幻读等问题，数据库设计了四种隔离级别。分别是读未提交，读已提交，可重复读，串行化（Serializable）。

1、读未提交

读未提交隔离级别，只限制了两个数据不能同时修改，但是修改数据的时候，即使事务未提交，都是可以被别的事务读取到的，这级别的事务隔离有脏读、重复读、幻读的问题；

2、读已提交

读已提交隔离级别，当前事务只能读取到其他事务提交的数据，所以这种事务的隔离级别解决了脏读问题，但还是会存在不可重复读、幻读问题；

3、可重复读

可重复读隔离级别，限制了读取数据的时候，不可以进行修改，所以解决了重复读的问题，但是读取范围数据的时候，是可以插入数据，所以还会存在幻读问题；

4、串行化

事务最高的隔离级别，在该级别下，所有事务都是进行串行化顺序执行的。可以避免脏读、不可重复读与幻读所有并发问题。但是这种事务隔离级别下，事务执行很耗性能。

幻读

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还存在没有修改的数据行，就好象发生了幻觉一样。

A 事务查询一个表,表里只有一条记录,id为1,但是这个时候 B 事务插入了一条数据,id为2,A 事务因为不知道有id为2的数据,所以这个时候A也插入了一条id为2的数据,这个时候肯定会插入失败.这种情况就是幻读。

4、ACID特性

ACID 是数据库事务的四个基本特性的首字母缩写，具体含义如下：

1. 原子性（Atomicity）：

   • 事务是数据库操作的最小单位，要么全部执行成功，要么全部失败回滚，不会出现部分执行的情况。这确保了数据库的一致性，即事务中的所有操作要么全部提交，要么全部取消。

2. 一致性（Consistency）：

   • 在事务开始和结束时，数据库的完整性约束没有被破坏。这意味着事务将使数据库从一个一致性状态转移到另一个一致性状态，不会导致数据的损坏或矛盾。

3. 隔离性（Isolation）：

   • 多个事务并发执行时，每个事务的操作应该互不干扰，即使是并发执行的情况下，每个事务看到的数据状态也应该是一致的。这通过各种并发控制技术来实现，例如锁机制、多版本并发控制等。

4. 持久性（Durability）：

   • 一旦事务提交成功，其对数据库的更改应该永久保存在数据库中，即使系统发生故障或崩溃，修改的数据也不应丢失。

这些特性确保了数据库在处理事务时能够保持数据的完整性、一致性和可靠性，是数据库系统设计中非常重要的概念。

5、MySQL性能优化

MySQL性能优化十个实用技巧_mysql 性能优化-CSDN博客

建立索引：

1、离散度大的字段；

2、小字段；

3、联合索引最左匹配原则；

4、索引覆盖提升查询效率；

5、经常修改的数据不适合建立索引；

7、可以在where及order by涉及的列上建立索引；

8、索引并非越多越好，最好不要超过5个；

9、索引字段不要使用函数，会造成索引失效；

6、聚簇索引跟非聚簇索引

聚簇索引和非聚簇索引是数据库索引的两种主要类型，它们在实现和性能上有所不同。

聚簇索引（Clustered Index）

1. 定义：

   • 聚簇索引是按照索引的顺序来组织表中的行数据的索引。换句话说，聚簇索引决定了数据在磁盘上的物理存储顺序。

2. 特性：

   • 数据行按照聚簇索引的顺序存储在磁盘上。
   • 每张表只能有一个聚簇索引。
   • 聚簇索引通常提供较快的数据访问速度，因为它们可以通过索引直接访问数据行，而不需要再次查找到数据行的物理位置。

3. 适用场景：

   • 经常用于频繁进行范围查找和排序操作的列。
   • 通常应用于主键列，因为主键是唯一的且非空的。

非聚簇索引（Non-clustered Index）

1. 定义：

   • 非聚簇索引中索引条目的逻辑顺序与实际数据行的物理存储顺序不同。

2. 特性：

   • 索引条目指向实际数据行的物理位置。
   • 一张表可以有多个非聚簇索引。
   • 非聚簇索引的叶子节点保存索引字段的值和指向数据行的指针。

3. 适用场景：

   • 适合用于频繁进行查询但不一定是范围查询的列。
   • 通常应用于非唯一、允许空值的列。

性能比较

• 查询性能：

  • 聚簇索引通常比非聚簇索引更快，因为它们直接定位到数据行而无需再次查找。

• 插入和更新性能：

  • 非聚簇索引相对更好，因为插入新行时不需要重新排列数据行的物理存储顺序。

• 空间利用：

  • 非聚簇索引占用的空间通常比聚簇索引少，因为它们不需要在索引中包含整个数据行。

综合考虑

选择使用聚簇索引还是非聚簇索引取决于具体的数据库设计和查询模式。在大多数情况下，合理的索引设计是混合使用聚簇索引和非聚簇索引，以最大化查询性能和整体数据库性能。

总结：

• 查询性能方面，聚簇索引通常更快。
• 插入和更新性能方面，非聚簇索引可能更有优势。
• 具体应用需要根据具体情况进行综合考虑，灵活设计索引以优化性能。

7、explain 执行计划

type                                       

表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下:

 ALL, index,  range, ref, eq_ref, const, system, NULL

从左到右，性能从最差到最好

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

EXPLAIN 执行计划命令详解-CSDN博客

8、show full process list 及 kill pid

在 MySQL 中，可以通过 SHOW FULL PROCESSLIST; 命令查看当前所有活动的数据库连接和运行的查询。而 KILL <pid>; 命令则用于终止指定的数据库连接或查询进程。

1. SHOW FULL PROCESSLIST;

这条命令用于显示当前 MySQL 服务器上所有活动的连接和它们执行的查询。返回的结果通常包括以下列：

• Id：连接的唯一标识符。
• User：连接的用户名。
• Host：连接的主机名。
• db：当前连接在使用的数据库（如果有的话）。
• Command：连接当前正在执行的 SQL 命令。
• Time：连接执行当前命令的时间（以秒为单位）。
• State：连接的当前状态（例如正在执行查询、正在锁等待等）。
• Info：连接当前正在执行的 SQL 语句。

使用场景：

• 监控数据库活动：可以帮助了解哪些连接正在执行哪些查询，以及这些查询的执行时间和状态。
• 识别长时间运行的查询：可以用来发现执行时间过长或者被长时间阻塞的查询，从而进行优化或解决问题。
• 查找占用资源过多的连接：可以用来识别消耗大量资源或者不必要占用连接池的连接，及时释放资源或终止连接。

2. KILL <pid>;

这条命令用于终止指定连接的数据库会话或者一个正在执行的查询进程。

用法：

• <pid> 是要终止的连接的 Id，可以通过 SHOW FULL PROCESSLIST; 查看。

注意事项：

• 权限：执行 KILL 命令需要 PROCESS 权限。
• 影响：终止连接或查询可能会导致客户端程序出现错误，因此应谨慎使用。
• 可能的后果：对于长时间运行的查询，特别是在 InnoDB 存储引擎下，可能会回滚正在进行的事务，这可能会导致数据丢失或不一致性。

何时使用 KILL 命令：

• 长时间运行的查询：如果一个查询正在占用数据库资源并且执行时间过长，可以考虑终止该查询，以释放资源并减少数据库负载。
• 错误处理：当某个连接执行了错误的查询或者导致数据库性能下降时，可以通过 KILL 命令来中止这些操作，以恢复数据库的正常运行。

总结

SHOW FULL PROCESSLIST; 和 KILL <pid>; 是 MySQL 中管理和监视数据库连接和查询的重要工具。合理使用这些命令可以帮助你识别和解决数据库性能问题，确保数据库运行平稳和安全。在使用 KILL 命令时，务必确保明确目标并了解可能的影响，避免误操作导致数据丢失或不一致。

9、索引覆盖跟回表查询

索引覆盖和回表查询都是与数据库查询性能优化相关的概念，它们通常与索引的使用方式和查询的执行计划有关。

索引覆盖（Index Covering）

索引覆盖是指一个查询可以完全通过索引来获取所需的数据，而不需要访问实际的数据行。具体来说，当一个查询的列在查询条件或者返回结果中都涵盖了索引的所有列时，我们称之为索引覆盖。

优势：

• 减少了对实际数据行的访问，从而减少了磁盘 I/O 操作。
• 提升了查询性能，因为只需操作索引数据结构而无需读取实际数据行。

示例： 假设有一张表 users，有索引 (last_name, first_name, age)。如果一个查询 SELECT last_name, first_name FROM users WHERE last_name = 'Smith';，则可以直接利用 (last_name, first_name) 的索引来完成查询，而不需要回到实际的数据行中去获取 age 字段。

回表查询（Index Lookup）

回表查询是指当数据库引擎利用索引定位到数据行后，仍然需要进一步访问数据页（即磁盘上的实际数据行）来获取完整的数据。这种情况通常发生在查询中需要获取的列不全在索引中，或者查询中包含的条件无法完全由索引覆盖。

特点：

• 虽然索引可以加速数据行的定位，但最终还是需要访问数据页来获取完整的数据。
• 如果回表次数过多，会增加额外的 I/O 操作，影响查询性能。

示例： 假设有一张表 orders，有一个索引 (order_id)。如果执行查询 SELECT order_id, customer_id FROM orders WHERE order_id = 123;，数据库引擎首先可以利用 (order_id) 索引定位到相应的行，然后需要回表获取 customer_id 字段的值，因为 customer_id 不在索引中。

总结

• 索引覆盖：查询可以完全通过索引获取需要的数据，无需额外访问实际数据行。
• 回表查询：索引定位到数据行后，仍需进一步访问实际数据行获取完整数据。

在实际的数据库设计和查询优化中，尽量通过合适的索引设计和查询调整，最大化利用索引覆盖，从而提升查询性能和减少系统资源的消耗。