1.MySQL 中的数据排序是怎么实现的？

1.排序方法：索引排序和文件排序 (filesort) 2.索引排序：如果order by xxx的字段为索引字段，则利用索引进行排序。效率最高，索引默认有序。 3.文件排序 (filesort)：内存排序(单路排序和双路排序)和磁盘排序，具体取决于排序数据的大小。其中，内存排序使用单路排序或双路排序，取决于max_length_for_sort_data(默认为4096个字节) 4.双路排序：取row_id(如果有主键，则为主键)和select a,b,c order by xxx的xxx字段放入sort_buffer(排序缓存)中，将排序后的row_id回表查询a,b,c 5.单路排序: 直接把要查的所有字段放入sort_buffer里，排序后直接得到结果集合 6.磁盘排序（归并排序）:将数据分为多份文件，单独对文件进行排序，然后合并成一个有序的大文件

2. MySQL 的 Change Buffer 是什么？它有什么作用？

1.ChangeBuffer定义：Change Buffer是InnoDB缓冲当中的一块缓存区，用于暂存二级索引的修改，避免二级索引页修改产生的随机IO 2.ChangeBuffer注意事项：只能用于二级索引，不能用于其他任何索引，包括主键索引和唯一索引都不行。 3.如果ChangeBuffer挂了，更改操作未执行，是否会出现脏数据？ 首先，ChangeBuffer也会保存在磁盘空间里面，redo log会记录Change Buffer当中的修改操作，确保数据一致性。

知识拓展1：一级索引和二级索引区别

一级索引（聚簇索引）：数据表的主键索引，数据和索引存储在同一B+树的叶子节点中。每个表只能有一个一级索引。

二级索引（非聚簇索引）：除主键外的其他索引，叶子节点存储索引列的值和对应的主键值。通过二级索引查询时，需要先通过二级索引获取主键值，再通过主键值查询数据，这个过程称为“回表”。

知识拓展2: MySQL中有哪些常见索引？都有什么区别？

在MySQL中，索引是提高查询效率的关键工具。常见的索引类型包括主键索引、唯一索引、普通索引、全文索引和空间索引。

1. 主键索引（Primary Key Index）

  • 定义：主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行数据。每个表只能有一个主键索引，且主键列的值不能为空。
  • 特点：主键索引的叶子节点存储完整的数据行，因此查询效率高。在InnoDB存储引擎中，主键索引是聚簇索引，数据存储与索引结构合并。

2. 唯一索引（Unique Index）

  • 定义：唯一索引确保索引列的每个值都是唯一的，但允许有空值。与主键索引类似，不同之处在于唯一索引允许列值为NULL。
  • 特点：唯一索引的叶子节点存储索引列的值和对应的主键值。在InnoDB中，唯一索引是非聚簇索引，数据存储与索引结构分开。

3. 普通索引（Index）

  • 定义：普通索引是最基本的索引类型，没有任何限制。索引列的值可以重复，也可以为NULL。
  • 特点：普通索引的叶子节点存储索引列的值和对应的主键值。在InnoDB中，普通索引是非聚簇索引，数据存储与索引结构分开。

4. 全文索引（Fulltext Index）

  • 定义：全文索引用于对文本数据进行全文搜索，适用于MyISAM存储引擎。它允许对文本字段进行复杂的搜索，如查找包含特定单词的记录。
  • 特点：全文索引的叶子节点存储文档的词项信息。在MyISAM中，全文索引是非聚簇索引，数据存储与索引结构分开。

5. 空间索引（Spatial Index）

  • 定义：空间索引用于对地理空间数据进行索引，支持空间数据类型的快速查询。它适用于存储地理位置、地图等空间数据的表。
  • 特点：空间索引的叶子节点存储空间数据的索引信息。在MyISAM中，空间索引是非聚簇索引，数据存储与索引结构分开。

总结：

  • 主键索引：用于唯一标识每一行数据，值不能为空。
  • 唯一索引：确保索引列的值唯一，但允许有空值。
  • 普通索引：最基本的索引类型，允许重复和空值。
  • 全文索引：用于对文本数据进行全文搜索，适用于MyISAM存储引擎。
  • 空间索引：用于对地理空间数据进行索引，支持空间数据类型的快速查询。

3. 详细描述一条 SQL 语句在 MySQL 中的执行过程。

1.连接器判断用户是否成功建立连接，数据库连接的权限校验 2.分析器分析SQL语法和词法是否有误 3.优化器生成SQL的执行计划，确定使用的索引和调整where的执行顺序（包括连表顺序） 4.执行器判断当前用户是否有权限查询该表，然后执行该SQL语句

MySQL架构图

[参考文献] 执行一条 select 语句，期间发生了什么？

[补充] 3. MySQL 日志：undo log、redo log、binlog 有什么用？

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。 redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复； binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

直接看参考文献当中的七个问题和其解决方案

[参考文献] MySQL 日志：undo log、redo log、binlog 有什么用？

4. MySQL 的存储引擎有哪些？它们之间有什么区别？

InnoDB : 支持事务、行锁、外键; 高并发性能、支持高负载的OLTP应用 (银行交易、电子商务订单、库存管理等); 聚集索引存储，检索效率高

MyISAM: 表锁、不支持事务和外键; 适用于读多写少的场景(数据仓库); 较高读性能和j较快的表级锁定

MEMORY: 存储在内存中，速度快，重启后数据丢失; 适用于临时数据存储和快速存储

MySQL常见存储引擎

5. MySQL 的索引类型有哪些？

划分方向 索引类型
数据结构 B+树索引、Hash索引、倒排索引 (全文索引)、R-树索引 (多维空间树)、位图索引(Bitmap)
物理存储 聚簇索引、非聚簇索引
字段特性 主键索引、唯一索引、普通索引(二级索引、辅助索引)、前缀索引
字段个数 单列索引、联合索引

MySQL索引类型

6. MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？

聚簇索引：就像是图书馆里按照书籍主题顺序摆放的书架。在这个书架（也就是聚簇索引）上，每本书（也就是数据库中的行数据）都是按照某个主题（通常是主键）来排列的。所以，当你想要找某一主题的书时，只要知道主题名（主键值），就能很快在书架上找到它，而且相邻主题的书也是挨在一起的，找起来很方便。但是，这种方式的缺点是，如果你想要改变某本书的主题（更新主键），可能就需要移动整本书到新的位置，甚至可能需要重新整理整个书架（数据页），这样就比较麻烦了。

非聚簇索引：则更像是图书馆里的一个索引卡片箱。在这个卡片箱里，每张卡片（也就是非聚簇索引的节点）上都写着书籍的主题（索引列的值）和书籍在书架上的位置（主键值或ROWID）。当你想要找一本书时，可以先在卡片箱里找到对应的卡片，然后根据卡片上的位置信息去书架上找书。这种方式的好处是灵活，你可以为不同的书籍主题制作多张卡片，方便从不同的角度查找书籍。但是，坏处是每次找书都需要两步：先在卡片箱里找卡片，再去书架上找书，这样可能会比直接在书架上找书要慢一些。

总的来说，聚簇索引和非聚簇索引的主要区别在于它们如何存储数据和索引，以及它们如何影响数据的查询和更新操作。聚簇索引将数据直接存储在索引上，查询效率高，但更新操作可能较复杂；而非聚簇索引则通过索引指向数据，提供了更多的灵活性，但查询时可能需要额外的步骤。在选择使用哪种索引时，需要根据具体的应用场景和查询需求来决定。

MySQL InnoDB的聚簇索引和非聚簇索引就像图书馆的两种找书的方式。 1.聚簇索引：图书馆在书架上(聚簇索引)摆放各种编号(主键名称)的书本(数据库中每一行的数据)。当你需要从图书馆找某一本书时，只需要知道书籍的编号(主键值)，就能够快速找到他。它的缺点是，如果需要换某一本书的编号(更新主键)，就需要移动整本书到新的位置，甚至重新整理书架(数据页)。这也是推荐使用select *的原因，因为如果需要查找索引列的数据，直接用二级索引就可以找到数据。例如通过姓名（二级索引）查询id(主键索引)，直接用二级索引就可以拿到对应的id.但是如果用select *,数据库就会回表查询其他的数据（性别，年龄等等）。 2.非聚簇索引：就像图书馆单独设置编号卡片箱，每张卡片(非聚簇索引)上包含了书籍名称(索引列的值)和书籍在书架上的编号位置(主键值或者ROWID)。当你想要找某本书的时候，可以根据卡片里面对应的编号进行查找。坏处是每次都需要两步走，查找起来没那么方便。 总结：聚簇索引是包含数据的，所以查找起来方便，但是更新操作开销大。非聚簇索引不包含数据，只包含索引列的值和其指向的数据索引，需要两步走才能查到数据。

7. MySQL 中的回表是什么？

  1. 回表：用二级索引中的主键取聚簇索引中查找数据行的过程
  2. 为什么需要回表：使用非聚簇索引的二级索引查询时，只能查到索引列的值和其主键值，无法获取其他数据
  3. 回表的缺点：回表会带来随机I/O, 频繁回表会导致效率非常低。所以不推荐使用 select *
  4. 回表的其他场景：当查询的部分列没有包含在索引中时，即便使用了索引，也需要会去获取缺失的列数据，称为覆盖索引缺失。
  5. 覆盖索引缺失发送场景：select 语句当中包含了非索引列; 索引的类型为Hash和full-text索引 （不存储列的值），不支持覆盖索引。
  6. 如何减少回表：MySQL5.6之后，引入了提高查询效率的优化技术，默认开启。允许MySQL用索引查找数据时，将部分查询条件下推到索引引擎层来过滤，减少了需要读取的数据行。

8. MySQL索引的最左前缀匹配原则是什么?

MySQL索引最左前后缀匹配原则

  1. 最左前缀匹配原则的定义：使用联合索引的时候，查询的条件必须从索引的最左侧开始匹配。如果联合索引包含多个列，查询条件必须包含第一个列，然后是第二个列，以此类推。
  2. 最左前缀匹配原则的原理：联合索引在B+树中的排列方式遵循从左到右的原则，例如联合索引(a, b, c)，在查询时，首先按照a的值进行排序，如果a的值相同，再查b的值，以此类推。
  3. 常见场景：= 、>= 、<= 、 BETWEEN 、like (xx%) 都包含等值的情况，可以定位到某个数，然后进行范围扫描，不会出现停止匹配的现象。但是 > 和 < 则不行。
  4. 部分不符合最左前缀匹配原则也能使用索引的原因：MySQL8当中引入了 Skip Can Range Access Method, 将缺失的左边的值查出来，如果左边缺失的列数据量少，则拼凑左边的索引，让SQL符合最左前缀匹配原则。

9. MySQL的覆盖索引是什么？

  1. 覆盖索引定义：查询的所有字段都是二级索引，从而使查询可以直接访问二级索引二不需要访问实习的表数据(主键索引)。
  2. 覆盖索引优点：减少I/O操作 ; 提高查询速度 (索引比表数据更加紧凑); 减少内存占用 (读取的索引页面而不是表数据页面)

10. MySQL的索引下推 (ICP) 是什么?

  1. 索引下推(ICP)定义: 减少回表查询，提高查询效率的行为。允许MySQL使用索引查找数据的时候，将部分查询条件下推到存储引擎层进行过滤，从而减少需要从表中读取的数据行，减少I/O。

  2. 应用场景：比如当前表建了一个联合索引(a, b, c)，使用where条件的时候，由于b用得是 like '%xxx%' 需要回表查询 (like 'xx%' 不需要)。即先查询a = '1' 的数据， 然后回表查询，最后进行where条件的过滤。如果使用索引下推之后 (MySQL 5.6)，在查询晚a = '1'的数据之后，可以先由存储引擎层进行where条件过滤，然后再回表查询， 减少回表查询的次数。

    SELECT * FROM people
  WHERE a='1'
  AND b LIKE '%123%'

    如联合索引index_name_age，假设数据库中有数据（张三，18）、（张三，28）、（张三，48）、（张三，8)

    【没有索引下推】查询name=‘张三’和age>30的数据时，会先匹配有四条数据name=‘张三’匹配成功，回表四次查询出带有name=‘张三’的四条数据，然后再根据age>30对这四条数据进行范围查找

    【使用索引下推】查询name=‘张三’和age>30的数据时，会先匹配有四条数据name=‘张三’匹配成功，然后age>30的数据，过滤完成后，再用主键索引去进行一次回表操作

11. MySQL建索引需要注意哪些事项？

【索引适合场景】

  1. 频繁使用where 、order by 、group by、distinct 的字段 (加快操作速度)
  2. 关联字段 (如果没有索引，连接的过程中，每个只都会进行一次全表扫描)

【不适合场景】

  1. 字段频繁更新 (更新除了修改数据外，还需要维护索引信息 => 调整B+树会降低性能)
  2. 字段值重复率高（区分度低，建立索引更加消耗资源）
  3. 参与列计算的字段 (索引会失效)
  4. 长字段 (text、 longtext) ：长字段占据的内存大，提升性能不明显。

【注】索引不是越多越好，因为每次修改都需要维护索引数据，消耗资源

12. MySQL中使用索引一定有效吗？如何排查索引效果？

【索引失效的情况】

  • 联合索引不符合最左匹配原则
  • 对索引列使用了运算(where id + 3 = 8)、函数 (lower()、count())、like '%xx%' 等操作
  • 对索引列和非索引列使用 or 操作 (where name = "swimmingliu" or age = 34)
  • 索引列类型不匹配导致的强制转换 (where name = 1 ==> where CAST(name AS signed int) = 1)

【如何查看失效】

利用explain命令 (前面最好加上analyse table xxx)

EXPLAIN 的 type 表示查询的访问类型，影响查询的效率。常见的值：

  1. ref: 使用索引，查找匹配某个单一列的值（比如通过外键查找）。比 range 更高效。
  2. range: 使用索引扫描某个范围内的值，适用于 BETWEEN、> < 等条件。
  3. index: 全索引扫描，扫描整个索引结构，不读表数据，通常效率比全表扫描好。
  4. all: 全表扫描，没有使用索引

总结：ref > range > index > all。

13. MySQL的索引数是否越多越好？why?

索引不是越多越好，因为对索引字段进行更新操作，需要调整B+树的结构，会导致数据库增加开销。

【注】阿里巴巴规范上表示索引一般不超过16个

**【时间开销】**进行增删改操作的时候，索引也必须更新。索引越多，需要修改的地方就越多，时间开销大。B+树可能会出现页分裂、合并等操作，时间开销更大。

【空间开销】 建立二级索引，都需要新建一个B+树，每个数据页面都是16KB。如果数据大，索引又多，占用的空间不小。

14. 为什么 MySQL 选择使用 B+ 树作为索引结构？

【B+树的优势】

  • 高效的查找性能：B+树是一种自平衡树，每个叶子结点到根节点的路径长度相同。增删改查的事件复杂度都是O(logn)，且具有一定的冗余节点，删除节点的时候，树的结构变化较小。
  • I/O次数相对较少：首先，B+树不会像红黑树一样，随着数据的增多树变得越来越高，它是多叉树。计算机访问数据时，往往具有局部性原理。当读取一个节点时，B树和B+树会将多个相关的数据加载到内存中，后续直接从内存反问，减少了磁盘的I/O。另外，相较于B树来说， B+树所有的数据都存放在叶子节点，而不像B树会在非叶子节点存储数据。B+树的非叶子节点仅存储索引值/主键和页面指针。
  • 对范围查询友好：B+树的叶子节点之间通过链表链接。当使用between语句时，会从根节点找到满足条件的起始记录。然后从起始记录，沿着叶子结点的链表进行顺序遍历。

【B+树存在的部分缺点】

当插入和删除节点，会触发分裂和合并操作，保持树的平衡，有一定的开销。

【跳表】

跳表其实就是一个多级链表，为了让链表更高效的查询。在不同的部分插入高级索引，让其能够缩小查找范围。有一种二分的思想在里面。其中，Redis的有序集合(sorted set)底层的结构就是跳表结构。

img

【为什么MySQL不用跳表而用B+树】

  • 跳表的I/O效率低：B+树通常只有3~4层，可以存储海量的数据。B+树的节点大小设计适配磁盘页的大小，磁盘页能够顺序存储大量数据。一次磁盘I/O操作就能读取节点的数据，减少I/O。跳表是多级索引的结构，虽然可以加速查找，但是其查找的过程当中会涉及到多次随机的I/O。
  • 范围查询： B+树的叶子节点是有序链表，在采用between时，能够找从叶子结点按照链表顺序遍历即可。跳表虽然支持范围查询，但是实现起来很复杂， 而且其多层的索引结构，范围查询时不能像B+树那样直接高效。
  • 跳表维护成本高：B+树在增删改的时候，又高效的算法平衡树结构，确保性能稳定。而跳表在新增和删除操作的时候，涉及多层链表的调整，开销较大，容易出现性能波动。
  • 跳表内存占用大：B+树的节点紧凑，非叶子节点只存储索引项和页面指针。而跳表除了每个节点存储数据以外，还需要额外的开销存储多层索引。相同数据量下，跳表的开销比B+树大得多。

15. MySQL 三层 B+ 树能存多少数据？

算法名称 数据页大小 叶子节点存储的数据记录大小 (假设) 节点的索引值(主键大小) 节点的页面指针大小
B+树 16KB 1KB 8B （bigint） 6B

【三层B+树存储数据计算】

nodesCount = 16 * 1024 / (6 + 8) = 1170 // 每个节点可以存多少个子节点
recordCount = 16KB / 1KB = 16 // 每个节点可以存多少条数据记录
dataCount = nodesCount * nodesCount * recordCount = 1170 * 1170 * 16 = 21,902,400 
所以如果一条数据为1KB大小，B+树大约能存2000w条数据

【拓展】

MySQL的InnoDB引擎中，B+树m每个节点的数据页大小可以通过调整innodb_page_size来修改 (一般为 4KB / 8KB / 16KB)

16. MySQL如何进行SQL调优

分为预防和解决慢查询两个角度阐述。总结起来就三点，命中索引、减少回表、减少I/O.

【预防】

  • 合理设计索引，减少回表次数，减少I/O
  • 避免 select * 操作。因为正常情况下，部分字段是没有二次索引的，它会用主键id或者rowid 进行回表查询，会增加系统的I/O。
  • 避免让索引失效，比如对索引字段进行计算、聚合函数、非同类型比较 (强制转换)和范围查询 (>、<、like %xxx%)。还有联合索引不匹配最左前缀原则
  • 避免对非索引字段，使用group by、order by、dinstinct等函数
  • 连表查询的是否需要保持不同字段的字符集一致，不然也会导致全表扫描。比如A表用utf-8，B表用latin1，查询的是否需要进行字符集转换，需要额外的计算，不能使用索引。

【解决慢查询】

  • 开启慢SQL日志记录功能，使用set global slow_query_log = "ON"， 默认是关闭的。设置一个查询延迟的阈值，把超过规定时间的SQL查询找出来。
  • 利用explain关键字分析慢SQL的原因，比如看看是否有索引失效、select *等情况

17. 如何使用MySQL的EXPLAIN语句进行查询分析?

【EXPLAIN查询结果解释】

名称 id select_type type key rows Extra
中文名称 查询的执行顺序 查询的类型 访问类型 关键索引 扫描行数 额外信息
说明 值越大优先级越高 SIMPLE简单查询、PRIMARY主查询、SUBQUERY 子查询 const > eq_ref > ref > range > index > ALL 实际用到的索引 值越小越好 Using index 表示覆盖索引、Using where 表示where条件过滤、Using temporary 表示临时表、Using filesort 表示需要额外的排序步骤

【type说明】

  • system: 表明查询的表只有一行 (系统表)

  • const : 表明查询的表最多只有一行匹配结果。通常是查询条件为主键或唯一索引， 并且是常量比较。

  • eq_ref: 表明对于每个来自钱一张表的行，MySQL只访问一次该表，通常发生在链接查询中使用主键或唯一索引的情况下。

  • ref：MySQL 使用非唯一索引查询。查询的条件是非唯一的

  • range: MySQL 会扫描表的一部分，不是全部行。通常出现在索引的范围查询中 (比如>=、<=、BETWEEN)

  • index: 表示MySQL扫描索引中的所有行，但不是扫描表的所有行。

  • all：表示需要扫描表的所有行，全表扫描。一般出现在没有索引的查询条件中。