MySQL 面试题

更新: 8/2/2025 字数: 0 字时长: 0 分钟

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

题解

MySQL 存储引擎有哪些？

想象一下，你开了一家图书馆，里面要放各种各样的书。这些书怎么摆、怎么管理、怎么借阅，就是咱们说的“存储引擎”。

MySQL 默认帮你准备了几种不同的“管理方式”，最常用的主要有两种：

InnoDB (音：因诺 DB)：这个是现在 MySQL 的“主力军”、“亲儿子”。绝大部分时候，你用 MySQL 就是用的它。
MyISAM (音：麦艾萨姆)：这个是以前 MySQL 的“老大哥”、“元老级人物”，现在用的比较少了，但有些老系统可能还在用它。

除了这两个，还有一些“小众”的，比如 Memory（内存）、CSV（逗号分隔值）等等，但它们的应用场景比较特殊，咱们先不展开，主要把精力放在 InnoDB 和 MyISAM 上。

它们之间有什么区别？

既然是两种不同的管理方式，那肯定有各自的特点和优缺点。咱们还是用图书馆的比喻来说：

InnoDB：高并发、安全、稳定，就像一个“现代化图书馆”
- 最大特点：支持事务（ACID）
  - 啥是事务？ 想象一下，你从银行取钱，这个过程包括“你的账户减少”、“ATM 吐钱给你”两步。事务就是要把这两步捆绑在一起，要么都成功，要么都失败（比如 ATM 没吐钱，你的账户也不能扣钱）。
  - InnoDB 的厉害之处：它能保证你做这些操作时，数据要么完全变了，要么一点没变，绝不会出现“半吊子”的情况。这对于银行、电商订单这种对数据一致性要求特别高的系统来说，简直是救命稻草。
  - 比喻：你的图书馆有一个超级智能的借阅系统。当一个读者借书，系统会先确认书架上有没有这本书，然后记录下借阅信息，最后把书取走。如果中途系统崩溃了，比如停电了，它能保证书架上的书数量和借阅记录是完全匹配的，不会出现书被拿走了，但系统没记录，或者系统记录了但书还在书架上的混乱情况。
- 支持行级锁（Row-level Locking）
  - 啥是行级锁？ 想象图书馆里有 100 本书，两个人同时想借不同的书。
  - InnoDB 的厉害之处：它允许两个人同时借不同的书，互不干扰，只要他们不是想借同一本书就行。这样，图书馆的效率就很高。
  - 比喻：两个人同时去借书，只要他们要的不是同一本书，就可以各自拿各自的，不需要等另一个人把书架逛完。
- 支持外键（Foreign Key）
  - 啥是外键？ 比如你的图书馆有一个“读者信息表”和一个“借阅记录表”。借阅记录表里会记录是哪个读者借了哪本书。外键就是能把这两个表关联起来，并强制你不能随便删除一个还在借书的读者信息，保证数据的完整性。
  - 比喻：如果一个读者还在借着书没还，你就不能把他的会员卡注销掉，必须等他还了书才能注销。
- 数据恢复能力强（Crash Recovery）
  - 比喻：即使图书馆突然停电、系统崩溃了，等电力恢复后，它也能通过之前记录的“日志”，把最后发生的所有操作重新检查一遍，确保数据回到崩溃前最完整、一致的状态。就像有自动修复功能。
- 缺点：相对来说，占用的磁盘空间会大一些，写入性能在某些极端情况下（比如大量插入不相关的短数据）可能会略低于 MyISAM。
MyISAM：读写快、简单粗暴，就像一个“传统图书馆”
- 最大特点：读写速度快（特别是读）
  - MyISAM 的厉害之处：它在数据读取方面通常更快，因为它结构比较简单，没有 InnoDB 那些复杂的事务和锁机制。
  - 比喻：就像一个老式图书馆，没有复杂的电脑系统，管理员直接去书架上拿书，效率很高。
- 只支持表级锁（Table-level Locking）
  - 啥是表级锁？ 还是借书的例子。
  - MyISAM 的局限性：两个人同时想借书，如果其中一个人在操作这个书架上的任何一本书，另一个人就必须等他操作完，才能去操作这个书架。也就是说，不管你借哪本书，只要有人在动这个书架，整个书架都被锁住了。这在并发访问多的时候，效率就比较低。
  - 比喻：两个人同时去借书，只要有一个人在书架前，其他人就得在旁边等着，直到他把书拿走、把书架整理好，另一个人才能上去选书。
- 不支持事务（ACID）
  - MyISAM 的局限性：如果银行取钱的例子用 MyISAM，就可能出现“钱扣了但 ATM 没吐钱”的悲剧。
  - 比喻：你的图书馆系统比较“原始”，借书操作可能只记录一半。如果中途停电了，可能你拿走了书，但系统没记录，或者系统记录了书被借走了，但书还在书架上，数据就混乱了。
- 不支持外键
  - 比喻：你可以直接删除一个还在借书的读者信息，系统不会管你，但这样就可能导致“借阅记录”里指向了一个不存在的读者。
- 缺点：数据安全性差（容易出现数据不一致），并发性差，不支持崩溃恢复（一旦崩溃，数据可能就丢失或损坏了）。

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

题解

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

聚簇索引：数据和索引“绑定”在一起。
- 比喻：《新华字典》本身
  - 字典的排序：字典里的字是按照拼音（或者部首）顺序排列的，对吧？比如查“啊”，你很快就能翻到它所在的那几页。
  - 字本身的内容：关键是，当你找到“啊”字，它的解释、组词、笔画等等所有详细信息，就直接写在这一页上，你不需要再翻到别的地方去找。
非聚簇索引：索引和数据“分离”，索引只存“位置信息”。
- 比喻：《作文选》的目录
  - 目录的排序：《作文选》前面有一个目录，比如按照作者姓名或者文章标题的拼音顺序排列。你查“鲁迅”，能在目录里找到“鲁迅”相关的文章。
  - 目录的内容：但是，目录里只告诉你“鲁迅”的文章在第 XX 页。你找到页码后，还需要再翻到第 XX 页去读那篇文章的详细内容。目录本身不包含文章的全文。

它们之间有什么区别？

聚簇索引
- MySQL InnoDB 中的聚簇索引：
  - 数据的物理排序：在 InnoDB 里，一张表的数据实际上是按照主键的顺序来物理存储的。这意味着，数据文件本身就是按照主键排序的。
  - 主键就是聚簇索引：当你为表定义了主键，这个主键就会被 InnoDB 自动创建为聚簇索引。
  - 如果没有主键怎么办？
    - InnoDB 会找一个唯一非空的列来充当聚簇索引。
    - 如果连唯一非空的列都没有，InnoDB 会自动创建一个隐藏的、自增的 row_id 来作为聚簇索引。
  - “索引即数据，数据即索引”：当你通过聚簇索引（也就是主键）来查询数据时，数据库直接就能找到这行数据的完整记录，因为数据行本身就存储在索引的叶子节点上。这就像你翻到字典的某一页，字和它的解释都在那里。
- 特点：
  - 每张表只能有一个聚簇索引。 因为数据在物理上只能按照一种方式排序。
  - 查询效率高：当通过主键查询时，性能非常好，因为直接就能定位到数据行。
  - 插入和更新有开销：如果频繁插入新的数据（特别是主键不是连续递增时），或者更新主键，可能需要移动大量数据来保持物理顺序，导致性能下降。
  - 主键选择很重要：好的主键（比如自增 ID）能减少页分裂和碎片，提高性能。
非聚簇索引
- MySQL InnoDB 中的非聚簇索引：
  - 索引结构：它也是一个 B+ 树结构。
  - 存储内容：非聚簇索引的叶子节点不存储完整的行数据，而是存储索引列的值和对应行的主键值。
  - 回表操作：当你通过非聚簇索引查询数据时，比如你通过“姓名”这个非主键列来查询一个人的所有信息：
    1. 先在“姓名”这个非聚簇索引里找到对应的姓名。
    2. 从这个索引中获取到这条记录的主键值（这就是回表操作）。
    3. 再利用这个主键值，去聚簇索引（也就是数据本身）中找到完整的行数据。
- 特点：
  - 可以有多个非聚簇索引。 你可以为表的多个列创建索引。
  - 查询效率：比直接通过主键查询（聚簇索引）要慢，因为它多了一步“回表”的操作。
  - 覆盖索引：如果你的查询语句，需要的所有列（select 后面的列）都能在某个非聚簇索引里直接取到，而不需要“回表”去聚簇索引拿，那么这个索引就叫做“覆盖索引”。这时它的查询效率就会很高，接近聚簇索引的查询效率。
    - 比喻：你的作文目录，除了页码，还把文章的字数也写在了目录里。如果你只想知道某篇文章的字数，你就不用翻到正文了，直接看目录就行。

MySQL 的索引类型有哪些？

题解

从索引的存储结构角度来分类：

聚簇索引
非聚簇索引

从索引的逻辑功能和约束性角度来分类：

普通索引
- 特点：最基本的索引类型，没有任何限制。
- 作用：仅仅是为了提高查询效率。它允许在索引列中有重复值和NULL 值。
- 比喻：你的图书管理员给图书馆里的所有书都按照书名做了个目录。你可以看到很多书名叫“小红帽”，没关系，这个目录就是让你能更快地找到所有叫“小红帽”的书，不管它们是不是同一本。
唯一索引
- 特点：保证索引列的所有数据都是唯一的，不允许有重复值。
- 作用：除了提高查询效率外，它还有一个重要作用是保证数据的唯一性。
- 比喻：你的图书管理员给所有书都按照ISBN 号（国际标准书号，每本书独有的）做了个目录。这个目录既能让你快速找到某本书，又保证了你输入的 ISBN 号肯定是独一无二的，不会有两本书用同一个 ISBN 号。
- 注意：允许有多个 NULL 值（因为 NULL 不等于 NULL）。
主键索引
- 特点：一种特殊的唯一索引。它不仅要求列中的值唯一，而且要求不能有 NULL 值。
- 作用：每张表最多只能有一个主键索引。它既用来唯一标识表中的每一行数据，也是 InnoDB 引擎中默认的聚簇索引。
- 比喻：你的图书管理员给所有书都分配了一个独一无二的图书编号，这个编号既不能重复，也不能是空的（每本书都必须有编号）。而且，所有书在书架上的摆放顺序，就是完全按照这个图书编号来的。
全文索引
- 特点：用于文本数据（如文章内容、评论等大段文字）的关键词搜索。
- 作用：传统索引是基于精确匹配或前缀匹配的，对于长文本的模糊搜索效率很低。全文索引可以让你像搜索引擎一样，快速查找包含特定关键词的文本。
- 比喻：你去一个大型电子图书馆，想要找所有提到“人工智能”的文章。你不可能逐字逐句地扫描每一篇文章。全文索引就是帮你建立了一个“词语字典”，告诉你哪些文章里包含了“人工智能”这个词，并且能高效地找出它们。
- 注意：只能应用于 CHAR、VARCHAR、TEXT 类型字段。以前只有 MyISAM 引擎支持，现在 InnoDB 也支持了。
组合索引
- 特点：在多个列上创建的索引。
- 作用：当你的查询条件经常涉及多个列的组合时，创建组合索引可以大幅提高查询效率。
- 比喻：你要找一本由“张三”写的、书名以“编程”开头的书。如果你只在“作者”上建索引，或只在“书名”上建索引，你可能要查两遍。但如果你建了一个“作者 + 书名”的组合索引，你就可以一次性快速定位。
- 最左前缀原则：这是组合索引的一个重要特性。如果你在 (col1, col2, col3) 上创建了组合索引，那么当你查询时只使用 col1，或者使用 (col1, col2)，或者使用 (col1, col2, col3)，这个索引都能生效。但如果你只使用 col2 或 col3，或者使用 (col2, col3)，则这个索引不会生效，或者只能部分生效。
  - 比喻：你的图书目录是按“作者 → 书名 → 出版社”的顺序来索引的。
    - 你找“张三”的书，能用这个索引。
    - 你找“张三写的《编程入门》”，也能用这个索引。
    - 但如果你只知道书名是“编程入门”，却不知道作者，那这个“按作者排序”的目录就帮不上忙了。

从索引的数据结构和算法角度来分类：

B+ 树索引
- 特点：这是 MySQL 中最常用、最重要的索引类型，无论是 InnoDB 还是 MyISAM 引擎，它们默认创建的普通索引、唯一索引、主键索引等，底层都是基于 B+ 树数据结构实现的。
- 数据结构：B+ 树是一种多路平衡查找树。
  - 优点：
    - 所有数据都存储在叶子节点上：且叶子节点之间通过指针相连，这样既能进行范围查询（比如查找年龄在 20 到 30 岁之间的人）也能进行高效的单点查询。
    - 树的高度较低：即使数据量非常大，树的层级也相对较少，减少了磁盘 I/O 次数。
    - 内部节点只存储键值和指针：不存储完整数据，可以容纳更多索引项，进一步降低树高。
  - 比喻：就像一个有层级、有目录、目录页之间有顺序链接的图书馆目录系统。
    - 一级目录（根节点）指引你去二级目录。
    - 二级目录（内部节点）指引你再去三级目录。
    - 最终的目录页（叶子节点）上，按照顺序记录着所有书籍的具体位置。而且这些目录页是相互连接的，你查完一本，可以很方便地按顺序找到下一本，直到找到你想查的所有书籍。
- 适用场景：几乎所有需要高效查找、范围查询、排序的场景。
哈希索引
- 特点：基于哈希表实现，提供极快的等值查询（=）。
- 数据结构：
  - 通过哈希函数将键值转换成一个哈希码（地址），然后直接通过这个地址找到对应的数据。
  - 优点：理论上查询速度最快，接近 O(1) 的复杂度。
  - 缺点：
    - 不支持范围查询：因为哈希值是无序的，你无法通过哈希值判断数据的相对大小。
    - 不支持模糊查询和排序：同样因为无序性。
    - 可能存在哈希冲突：不同的键可能计算出相同的哈希值，需要额外的处理机制（如链表），这会降低性能。
    - 只支持等值查询（=），不支持 <、>、BETWEEN 等操作。
- 适用场景：主要用于那些需要非常快速的精确查找的场景。在 MySQL 中，InnoDB 引擎的自适应哈希索引是一个特殊的概念，它不是由用户直接创建的，而是 InnoDB 内部为了优化查询性能，根据访问频率自动在 B+ 树索引之上创建的哈希索引。而 MEMORY 存储引擎可以显式创建哈希索引。
R-树索引 / 空间索引
- 特点：专门用于地理空间数据（如经纬度、地图上的点/线/面）的查询。
- 数据结构：R-树是一种多维索引结构，它将多维数据（如坐标）划分为矩形区域，并组织成树状结构。
- 作用：可以高效地执行“包含在某个区域内”、“与某个对象相交”、“距离某个点最近”等空间查询。
- 比喻：想象你在一个超大的城市地图上，想要找到某个区域内所有餐馆，或者找到离你当前位置最近的三个加油站。R-树索引就像是这个地图的“网格系统”和“查询导航”，它可以快速地帮你定位到地图上的特定区域或对象。
- 适用场景：GIS（地理信息系统）、LBS（基于位置的服务）应用，如地图、导航、定位服务等。MySQL 中，只有 MyISAM 引擎支持 SPATIAL 关键字来创建 R-树索引，InnoDB 引擎需要使用其他方式（如使用 B+ 树索引存储 Muti-Polygon）来模拟空间索引。
倒排索引
- 特点：主要用于全文搜索（Full-Text Search）。
- 数据结构：结构像一个字典，每个词条（Term）都指向包含这个词条的所有文档（Document）列表。
  - 正向索引：文档 → 词语列表
  - 倒排索引：词语 → 文档列表
- 作用：当你输入一个或多个关键词进行搜索时，倒排索引能迅速找到包含这些关键词的所有文档，而不需要扫描所有文档。
- 比喻：你的图书馆里有一墙的书，你想要找到所有提到“人工智能”这个词的书。
  - 正向索引：就像你拿着每本书，翻一遍，看看有没有“人工智能”。（效率很低）
  - 倒排索引：就像图书馆有一个专门的“关键词词典”，你查“人工智能”，这个词典就直接告诉你：“《AI 简史》第 5 页，第 100 页；《深度学习原理》第 20 页”等等。它直接指明了包含这个词的所有位置。
- 适用场景：搜索引擎、文档管理系统、站内搜索、日志分析等需要对大量文本内容进行关键词搜索的场景。MySQL 的全文索引底层就是基于倒排索引实现的。

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

题解

想象一下，你有一家巨大的图书馆（这就是你的数据库），里面藏着无数的书籍（这就是你的数据）。

为了方便读者（用户）快速找到书，你需要一个索引（目录）。

现在，我们来看看 MySQL 为什么选择了 B+ 树这个“目录”：

核心原因一：因为它找书又快又省力（查询效率高）

分层目录，快速定位（减少磁盘 IO）：
- B+ 树就像一本设计精良的图书馆目录，它不是平铺的，而是分层的。最顶层是总目录，它告诉你哪些区域放什么类型的书（比如“1-100 号书架是历史类，101-200 号书架是科学类”）。
- 你找到大致区域后，再去看那个区域的详细目录，最后才能找到具体的书。
- 这种“逐层缩小范围”的查找方式非常高效。你不需要把整个图书馆的书都翻一遍，也不需要把所有目录页都翻一遍。
- 重点：计算机在硬盘上找数据，比在内存里找数据要慢很多倍。B+ 树的这种分层结构，能确保每次找书（查询数据）时，需要从硬盘上读取的“目录页”（B+ 树的节点）非常少。读取次数少，自然就快！
只在最底层放书，目录更精简（非叶子节点不存数据，存储更多索引）：
- 想象一下，如果你的图书馆目录，每一页上除了写“书架号”，还硬生生印上了书的内容摘要，那这一页目录能放多少条目录信息呢？肯定很少！
- B+ 树的聪明之处在于，它只在最底层（叶子节点）存放真正的“书”（完整数据）。而上面的所有层级（非叶子节点），都只存放“书架号”和“大致范围”（索引键和子节点指针）。
- 好处：这样，每一页目录（B+ 树的非叶子节点）就能装下更多的“书架号范围”，从而让整个目录变得更扁平，层级更少。层级少，意味着你找书时需要翻的“目录页”更少，从硬盘读取的次数更少，速度自然也就更快。

核心原因二：因为它能连着找书，特别方便（范围查询友好）

假设你要找“所有科学类的书”（比如id BETWEEN 100 AND 200）。
B+ 树最厉害的地方是，它把所有真正的“书”（数据）都放在最底层，并且这些书之间是用链表串起来的！
这就像你在图书馆的科学书架上找到第一本科学书后，发现旁边有个小箭头指向下一本科学书，你就可以顺着这些箭头，一本一本，非常流畅地把所有科学书都找出来，而不需要每次都回到总目录重新找。
好处：对于数据库里经常出现的“查询某个范围内的数据”（比如“找所有出生在 1990 年到 2000 年之间的人”），B+ 树简直是神来之笔。它能迅速找到起始点，然后通过链表高效地读取连续的数据，这大大提升了范围查询的性能。

核心原因三：因为它存储效率高，充分利用硬件优势（磁盘页对齐）

计算机读取硬盘数据，不是一个字节一个字节读的，而是以“页”（Page）为单位，通常是 4KB 或 16KB。
B+ 树的设计，巧妙地让它的每个节点的大小正好是一个磁盘页的整数倍。
好处：这意味着，当 MySQL 需要读取一个 B+ 树节点时，它只需要进行一次磁盘 IO 操作，就能把整个节点的数据全部读到内存中。这样就最大化地利用了磁盘的读取效率，避免了多次零散的读取。

MySQL 索引的最左前缀匹配原则是什么？

题解

想象一下你有一本厚厚的电话簿（这就是你的索引），上面按姓名的顺序排好了：

张三
张四
张小明
李明
王刚
王小红
...

现在，你给电话簿建了一个“组合索引”，也就是你把姓名分成了好几部分来排序，比如：

INDEX(姓, 名, 小名)

这意味着电话簿是这样排序的：

先按姓的拼音/笔画排序
如果姓一样，再按名的拼音/笔画排序
如果名也一样，最后按小名的拼音/笔画排序

最左前缀匹配原则，说的就是：

你查电话簿的时候，只要你提供了最左边的一部分信息（或者说，你提供了从最左边开始，连续的几部分信息），电话簿（索引）就能帮你快速找到！

我们来举几个例子：

能用上索引的情况（符合最左前缀原则）：
- 你只知道“姓”：
  - WHERE 姓 = '张'
  - 电话簿直接跳到“张”开头的页面，然后从那里开始找。能用上索引。
- 你知道“姓”和“名”：
  - WHERE 姓 = '张' AND 名 = '小明'
  - 电话簿先跳到“张”开头的页面，然后在那里面继续找“小明”。能用上索引。
- 你知道“姓”、“名”和“小名”：
  - WHERE 姓 = '张' AND 名 = '小明' AND 小名 = 'Jack'
  - 电话簿先跳到“张”，再找“小明”，再找“Jack”。能用上索引。
不能完全用上索引的情况（不符合最左前缀原则）：
- 你只知道“名”：
  - WHERE 名 = '小明'
  - 电话簿是先按姓排序的，你只知道名，它无法直接跳到“小明”那里。它得从头到尾把所有姓都翻一遍，然后挨个看“名”是不是“小明”。索引没法直接帮你定位，或者说只能用很小一部分索引功能，效率很低，接近全表扫描。
- 你只知道“小名”：
  - WHERE 小名 = 'Jack'
  - 同理，电话簿根本不知道“Jack”这个小名在哪个“姓”和“名”的下面，所以只能全盘搜索。索引基本废了。
- 你跳过了中间的字段：
  - WHERE 姓 = '张' AND 小名 = 'Jack'
  - 电话簿能先跳到“张”开头的页面。但是，它接下来要找“小名”时，因为你没有提供“名”的信息，它不知道在“张”的内部，“Jack”这个小名是跟着哪个“名”的。所以，它只能在所有姓“张”的人里面，一个个地去检查小名是不是“Jack”。索引只能用到“姓”这一部分，后面就失效了。

为什么叫“最左前缀”呢？

就是说，你的查询条件，必须从你建立索引的字段列表的最左边开始，连续地提供信息。

比如 INDEX(A, B, C)：

A = ? ✔️ (最左边 A)
A = ? AND B = ? ✔️ (最左边 A，然后是 B)
A = ? AND B = ? AND C = ? ✔️ (最左边 A，然后 B，然后 C)
B = ? ❌ (没从最左边 A 开始)
C = ? ❌ (没从最左边 A 开始)
A = ? AND C = ? ❌ (跳过了 B，索引只能用到 A)

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

题解

我们来计算一下 MySQL 三层 B+ 树（假设指的是非叶子节点有两层，叶子节点一层，也就是总共 3 层）能够存储的数据量。

首先，需要明确几个前提和假设：

B+ 树节点大小：MySQL 的 InnoDB 存储引擎默认的 B+ 树节点（也就是数据页/索引页）大小是 16KB。
索引键的大小：我们假设索引键是 BIGINT 类型，占用 8 字节。
指针大小：指向子节点的指针（内存地址或文件偏移量）通常是 6 字节。
数据行大小：假设每行数据平均大小为 1KB（这只是一个估算，实际大小差异很大）。
每个节点内部的开销：每个数据页/索引页除了存储实际数据或索引外，还有页头、页尾、页目录等额外开销。为了简化计算，我们暂时忽略这些小开销，或者认为它们是包含在有效存储空间内的。

计算步骤：

第一层：叶子节点（存储实际数据行）

一个 16KB 的叶子节点，主要存储完整的数据行。
如果每行数据是 1KB，那么一个叶子节点大约能存储： 16 KB / 1 KB/行 = 16 行数据
注意：如果是辅助索引（非主键索引），叶子节点存储的不是完整数据行，而是索引键 + 主键值。这种情况下，叶子节点能存储的条目会更多。但我们这里通常讨论的是主键索引（聚簇索引），叶子节点直接包含数据。

第二层：非叶子节点（存储索引键和指向叶子节点的指针）

一个 16KB 的非叶子节点，存储的是索引键和指向下一层（叶子节点）的指针。
每个索引项占用空间：索引键大小 + 指针大小 = 8字节 (BIGINT) + 6字节 (指针) = 14字节
一个非叶子节点能存储的索引项数量： 16 KB / 14 字节/项 = 16 * 1024 字节 / 14 字节/项 ≈ 1170 项
这意味着，一个第二层的非叶子节点可以指向大约 1170 个叶子节点。

第三层：根节点（最顶层的非叶子节点，存储索引键和指向第二层的指针）

根节点也是一个非叶子节点，其结构与第二层的非叶子节点类似。
它同样能存储大约 1170 个索引项。
这意味着，一个根节点可以指向大约 1170 个第二层的非叶子节点。

总共能存储的数据量计算：

根节点 (1 个) 指向 1170 个第二层节点。
每个第二层节点指向 1170 个叶子节点。
每个叶子节点存储 16 行数据。

总数据行数 = 1170 × 1170 × 16 = 21,902,400 行

结论：

在理想情况下，一个 3 层的 B+ 树（根节点、中间层、叶子层）大约可以存储超过 2000 万（2 千多万）行数据！

这个数字意味着什么？

极高的效率：即使面对千万级别的数据量，你查找任何一行数据，MySQL 最多也只需要进行 3 次磁盘 IO（从硬盘读取根节点 → 读取中间节点 → 读取叶子节点）。
这就是为什么 B+ 树能够支撑大型数据库高效运行的关键原因之一！ 大多数时候，我们日常使用的数据库表的数据量都在这个数量级以内，3 层 B+ 树足以满足性能需求。

MySQL 中的回表是什么？

题解

核心概念：回表是指当 MySQL 使用辅助索引进行查询时，发现辅助索引的叶子节点只存储了索引列的值和主键值，而没有存储查询所需的所有列数据时，需要根据辅助索引叶子节点获取到的主键值，再回到主键索引中去查找完整的行数据。

打个比方：想象你有一本大字典（这就是你的主键索引），里面包含了每个词语的所有详细信息（词性、解释、例句等等）。这本字典是按照词语的字母顺序排列的，所以你知道词语就能直接找到它的所有信息。

现在，你又有一本小册子（这就是你的辅助索引），这本小册子是按照“词语的笔画数”来排序的。但是，这本小册子里面只记录了：

词语的笔画数
词语本身
它在大字典中的页码（也就是主键值）

现在，我们来看两种查询场景：

场景一：不用回表（直接通过索引覆盖查询所需的所有列）

你的查询需求：“我想找所有笔画是 3 的词语，我只需要知道这些词语是啥就行，不需要它们的详细解释。”
SQL 查询：SELECT 词语 FROM 字典 WHERE 笔画数 = 3;
操作过程：
1. 你打开小册子（辅助索引），找到所有笔画是 3 的词语。
2. 因为小册子里直接记录了“词语”本身，你直接在小册子里就把所有需要的信息都拿到了。
3. 你不需要再翻阅那本大字典。
结果：效率很高，因为只用了一本小册子。

场景二：需要回表（辅助索引无法覆盖查询所需的所有列）

你的查询需求：“我想找所有笔画是 3 的词语，并且想知道它们的详细解释。”
SQL 查询：SELECT 词语, 解释, 例句 FROM 字典 WHERE 笔画数 = 3;
操作过程：
1. 你打开小册子（辅助索引），找到所有笔画是 3 的词语，并且也拿到了它们在大字典里的“页码”（主键值）。
2. 但是，小册子里没有记录“解释”和“例句”。
3. 所以，你需要根据小册子里找到的每一个“页码”，回到大字典（主键索引）里，去翻到对应的页码，才能找到这个词语的完整“解释”和“例句”。这个“回到大字典”的动作，就叫做“回表”。
结果：效率会相对低一些，因为除了查小册子，你还得多次翻阅大字典。每翻一次大字典，就是一次额外的磁盘 IO。

MySQL 中实际的对应关系：

主键索引（聚簇索引）：索引的叶子节点存储了整行数据。它是按照主键 ID 的顺序物理存储的。
辅助索引（非聚簇索引）：索引的叶子节点存储的是辅助索引列的值 + 主键值。

当发生回表时，流程是这样的：

根据辅助索引的 B+ 树，通过索引列找到对应的叶子节点。
从叶子节点获取到对应行的主键值。
拿着这个主键值，再到主键索引的 B+ 树中去查找，最终找到并读取完整的行数据。

为什么会有回表？

为了节省存储空间和提高查询效率。如果每个辅助索引都把所有列的数据都存一遍，那会占用大量的磁盘空间，而且更新数据时也需要更新所有索引，效率会很低。所以 MySQL 选择只在辅助索引中存储主键值，需要时再通过主键值去主键索引中获取完整数据。

如何避免回表（提高性能）？

覆盖索引：当查询的列，在辅助索引的叶子节点中已经全部包含时（即查询的列只是索引列或主键列），就不需要回表了。
- 例如：SELECT age, id FROM users WHERE age = 25; 如果 (age, id) 是一个联合辅助索引，那么查询只需要在这个索引中就能完成，不会回表。
只查询需要的列：尽量避免 SELECT *，只查询你真正需要的列。
- 例如：你只需要 age，就 SELECT age FROM users WHERE age = 25;。如果 age 是辅助索引，就不会回表。

理解回表对于优化 MySQL 查询性能非常重要，特别是当数据量很大时，减少回表次数能显著提升查询速度。

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

题解

MySQL 中使用索引不一定有效。 这是一个常见的误区，认为只要加了索引就万事大吉。实际上，索引是一把双刃剑，用得好能提升性能，用不好反而可能拖慢系统。

为什么使用索引不一定有效？

不符合最左前缀原则：对于联合索引，如果查询条件没有从最左边的列开始，或者跳过了中间的列，索引可能部分失效或完全失效。
- INDEX(a, b, c)，查询 WHERE b = 1 或 WHERE a = 1 AND c = 2 都无法充分利用索引。
查询条件中使用了函数或表达式：对索引列进行函数操作（如 YEAR(date_col) = 2023）、类型转换（隐式或显式）、数学运算等，会导致索引失效。
- WHERE TO_DAYS(date_col) > XXX
- WHERE id + 1 = 10
- WHERE name LIKE '%suffix' (左模糊匹配)
负向查询或范围查询：
- !=、< >、NOT IN、NOT LIKE 等负向查询有时会导致索引失效或效果不佳。
- OR 连接的条件如果索引列不同，也可能导致索引失效。
- 范围查询 (<, <=, >, >=, BETWEEN) 本身可以使用索引，但如果范围过大，MySQL 可能会认为全表扫描更快。
数据分布问题（低选择性）：如果索引列的区分度很低，比如一个性别列只有“男”和“女”两个值，那么对这个列建立索引的意义不大。当查询 WHERE gender = '男' 时，如果男性占了总人数的 90%，MySQL 可能会认为直接全表扫描更划算，从而放弃使用索引。
优化器判断失误：MySQL 的查询优化器会根据成本模型（IO 成本、CPU 成本等）来决定是否使用索引以及使用哪个索引。有时，它可能会做出错误的判断，选择不使用索引或者使用了效率较低的索引。
索引维护成本：索引会占用磁盘空间。在数据增删改时，索引也需要同步更新，这会增加写操作的负担，降低写入性能。过多的索引或不合理的索引会拖慢写操作。
内存不足：索引数据也需要加载到内存中才能高效使用。如果索引过大，内存无法容纳，频繁的磁盘 IO 会导致性能下降。

如何排查索引效果？

排查索引效果是 SQL 优化中最关键的一步。MySQL 提供了强大的工具 EXPLAIN 来帮助我们分析 SQL 执行计划。

核心工具：EXPLAIN 语句可以模拟优化器执行 SQL 查询，从而知道 MySQL 是如何处理你的 SQL 语句的。它会显示表的连接顺序、如何使用索引、扫描的行数等信息。

使用方法：在你的 SELECT 语句前加上 EXPLAIN 关键字即可：

sql

EXPLAIN SELECT id, name, age FROM users WHERE age > 25 AND gender = 'female';

EXPLAIN 结果中的关键字段及含义：

id: SELECT 查询的序列号，表示查询中每个 SELECT 语句的执行顺序。
select_type: SELECT 类型，如 SIMPLE (简单查询), PRIMARY (主查询), SUBQUERY (子查询), UNION (UNION 中的第二个或后面的查询)。
table: 查询的表名。
partitions: 匹配到的分区信息（如果使用了分区表）。
type: 最重要的字段之一！ 表示 MySQL 查找数据的方式，从差到好依次是：
- ALL: 全表扫描，最差，通常意味着没有用到索引或者索引失效。
- index: 全索引扫描，比ALL好一些，因为它扫描的是索引而不是数据行，但仍然扫描了整个索引。通常发生在查询的列都在索引中，但 where 条件无法进一步过滤时。
- range: 范围扫描，通过索引查找一个给定范围内的行，如WHERE id BETWEEN 1 AND 10。
- ref: 非唯一性索引扫描，例如使用非唯一索引或唯一索引的前缀来查找。
- eq_ref: 唯一性索引扫描，常用于联接查询，表示前一个表的每一行都匹配到当前表的一个唯一行。
- const, system: 查询优化器能将查询转换为一个常量。非常快，通常是主键或唯一索引等值查询。
- null: 优化器在优化阶段分解查询，不需要访问表或索引。
- 目标：ref, eq_ref, const 是最佳类型，range 也不错，应尽量避免 ALL 和 index。
possible_keys: MySQL 可能选择的索引。
key: 实际使用的索引。 如果为NULL，则表示没有使用索引。
key_len: 使用的索引的长度。对于联合索引，这个值可以帮助你判断索引使用了多少列。
ref: 表示哪个列或常量与key一起使用来查找行。
rows: 非常重要的字段！ 估算 SQL 语句会扫描的行数。这个数字越小越好。
filtered: 表示通过这个表条件过滤出的行百分比。
Extra: 非常重要的字段！ 额外信息，通常能提供很多优化线索：
- Using filesort: 出现了文件排序（在内存或磁盘上进行排序），通常说明ORDER BY的列没有索引，或索引无法用于排序。性能较差。
- Using temporary: 使用了临时表来处理查询，通常发生在GROUP BY或ORDER BY不同的列，或者复杂子查询中。性能较差。
- Using index: 表示使用了覆盖索引（Covering Index），查询的所有列都在索引中，无需回表。这是非常高效的，性能很好。
- Using where: 表示 MySQL 将通过WHERE子句来过滤结果。通常配合Using index或Using index condition出现。
- Using index condition: MySQL 5.6 引入的索引条件下推（Index Condition Pushdown, ICP）。表示 MySQL 会在存储引擎层（索引层）进行条件过滤，而不是将所有匹配的索引条目都读取出来再在 MySQL 服务器层过滤。可以减少回表次数。

排查索引效果的步骤：

确定慢查询：通过慢查询日志、性能监控工具（如 Percona Toolkit 的pt-query-digest，或 MySQL Enterprise Monitor）找到耗时长的 SQL 语句。
使用 EXPLAIN 分析：对慢查询语句使用 EXPLAIN。
查看 type 字段：
- 如果是 ALL 或 index，说明索引效果很差或没用上。
- 如果是 range, ref, eq_ref, const，通常表示索引使用得不错。
查看 key 字段：
- 如果为 NULL，说明没有用到任何索引。
- 如果显示了索引名，说明用到了索引。
查看 rows 字段：
- rows 值应该尽可能小。如果 rows 很大，即使 type 不是 ALL，也可能意味着索引选择性差或范围过大。
查看 Extra 字段：
- 看到 Using filesort 或 Using temporary：尝试优化 ORDER BY 或 GROUP BY 字段，考虑添加索引或调整索引顺序。
- 看到 Using index：恭喜你，这是一个覆盖索引，效率非常高。
- 没有看到 Using index 但 type 是 ref/range：可能存在回表，如果查询列很多，考虑是否能调整为覆盖索引。
结合业务和数据特点：
- 思考查询的常见模式，是等值查询、范围查询还是模糊查询？
- 了解数据分布，索引列的唯一性如何？
- 是否有经常进行排序或分组的列？
调整索引或 SQL：
- 创建/调整索引：根据分析结果，添加新的联合索引、调整联合索引的列顺序，或删除不必要的索引。
- 修改 SQL 语句：避免在索引列上使用函数、表达式。调整WHERE条件，使其符合最左前缀原则。优化LIKE查询（避免左模糊）。
- 强制使用索引：极端情况下，如果优化器判断失误，可以使用 USE INDEX 或 FORCE INDEX 强制 MySQL 使用某个索引（但不推荐，因为可能未来数据变化后，强制索引反而变慢）。

在 MySQL 中建索引时需要注意哪些事项？

题解

在 MySQL 中建立索引是一项非常重要的优化工作，但绝不是越多越好，也不是随意建立。以下是在建立索引时需要注意的关键事项：

索引不是越多越好，要权衡利弊
- 优点：加速查询（主要是 SELECT 语句）。
- 缺点：
  - 占用磁盘空间：索引本身也需要存储空间。
  - 降低写入性能：INSERT、DELETE、UPDATE 操作时，除了修改数据，还需要同步更新所有关联的索引。索引越多，更新成本越高。
  - 查询优化器选择成本：索引过多会增加查询优化器选择索引的复杂性，有时甚至可能选错索引。
- 原则：精心挑选，建立必要的、高效的索引。
选择合适的列建立索引
- WHERE 子句中经常使用的列：这是最主要的考虑因素，因为索引的主要目的是加速过滤条件。
- JOIN (连接) 条件中使用的列：JOIN 操作中 ON 子句里的列通常需要索引，以加速连接过程。
- ORDER BY (排序) 和 GROUP BY (分组) 子句中使用的列：如果这些列有索引，可以避免使用文件排序（filesort）和临时表（Using temporary），大大提高效率。
- 高选择性（Cardinality）的列：
  - 选择性 = 不重复值数量 / 总行数
  - 选择性越高的列，索引效果越好。例如，身份证号（唯一）的选择性最高，性别（只有“男”、“女”）的选择性很低。
  - 对于选择性很低的列（如性别、状态字段），通常不建议单独建立索引，或者只作为联合索引的非首列。
- 较短的列：索引的长度会影响 B+ 树节点能存储的键值数量。索引越短，每个索引页能存储的键值越多，B+ 树就越扁平，IO 次数越少。
  - 对于 VARCHAR 类型的列，可以考虑使用前缀索引（如 INDEX(name(10))，只索引前 10 个字符），但需要权衡区分度。
- 避免在频繁更新的列上建立索引（如果不是主键）：如果一个列经常被修改，那么在这个列上的索引也会频繁更新，增加写入负担。
- 避免对大文本字段（TEXT, BLOB）建立完整索引：这些字段通常很大，直接索引会占用大量空间。如果需要，考虑使用前缀索引或全文索引。
理解联合索引（复合索引）和最左前缀原则
- 联合索引：对多个列一起建立的索引，例如 INDEX(col1, col2, col3)。
- 最左前缀原则：只有查询条件从联合索引的最左边列开始，并且连续匹配时，索引才能被有效利用。
  - WHERE col1 = ? (有效)
  - WHERE col1 = ? AND col2 = ? (有效)
  - WHERE col1 = ? AND col2 = ? AND col3 = ? (有效)
  - WHERE col2 = ? (无效)
  - WHERE col1 = ? AND col3 = ? (只能利用 col1 部分)
- 排序：联合索引的列顺序很重要。将最常用于等值查询或范围查询的列放在前面，将选择性最高的列放在前面。
  - 考虑覆盖索引：如果你的查询经常同时查询 col1, col2, col3，那么 INDEX(col1, col2, col3) 可能会成为一个覆盖索引，避免回表。
避免索引失效的情况
- 在索引列上使用函数或表达式：WHERE YEAR(date_col) = 2023 会使 date_col 上的索引失效。
- 类型转换：如果列类型和查询值类型不匹配，MySQL 可能进行隐式转换，导致索引失效。
- 左模糊匹配：WHERE name LIKE '%abc' 不会使用 name 上的索引，因为无法利用 B+ 树的顺序。'abc%' 可以。
- 负向查询：!=、NOT IN 通常会导致全表扫描。
- OR 连接条件：如果 OR 两边的条件涉及不同索引列，通常会导致索引失效（除非优化器可以合并索引）。
- 优化器选择：即使有索引，如果 MySQL 优化器认为全表扫描更快（例如，查询结果集占总行数比例很高时），也可能不使用索引。
主键索引（聚簇索引）的特殊性
- InnoDB 表的主键就是聚簇索引，它决定了数据行的物理存储顺序。
- 如果表没有显式定义主键，InnoDB 会选择一个唯一的非空索引作为主键。如果没有这样的索引，InnoDB 会隐式创建一个 6 字节的 ROWID 作为主键。
- 辅助索引的叶子节点会存储主键值。因此，一个短小、不变的主键对于所有辅助索引都是有利的。
考虑索引覆盖（Covering Index）
- 如果一个查询所需的所有列都在一个索引中（包括查询条件、SELECT 列、ORDER BY 列），那么就称这个索引为覆盖索引。
- 覆盖索引可以避免回表（不需要再访问主键索引去取完整数据），大大提高查询性能，因为所有信息都可以直接从辅助索引中获取。
- 在 EXPLAIN 结果中，Extra 字段出现 Using index 就表示使用了覆盖索引。
定期分析和优化索引
- 使用 EXPLAIN：这是分析 SQL 执行计划和索引使用情况最直接、最有效的方法。
- 监控慢查询日志：识别哪些 SQL 语句耗时过长，然后针对性地优化。
- ANALYZE TABLE：偶尔运行 ANALYZE TABLE <tablename> 可以更新表的统计信息，帮助查询优化器做出更准确的判断。
- 删除不必要的索引：定期检查并删除那些从未使用或很少使用的索引，以减少维护成本。
生产环境的注意事项
- 先测试，再上线：任何索引的添加或修改都应该在测试环境充分测试其效果（包括查询性能和写入性能），避免上线后出现新的性能问题。
- 在线 DDL：MySQL 5.6+ 提供了在线 DDL 功能，可以在不阻塞表的情况下添加或删除索引，大大降低了维护成本。

MySQL 中的索引数量是否越多越好？为什么？

题解

MySQL 中的索引数量不是越多越好。

索引虽然能提高查询效率，但它也带来了额外的成本，这些成本会随着索引数量的增加而显著上升。

增加磁盘空间占用：
- 每个索引都是一张数据结构（通常是 B+ 树），它需要占用磁盘空间来存储。索引越多，占用的空间就越大。对于大型数据库，这可能意味着需要更多的存储硬件。
降低写入（INSERT, UPDATE, DELETE）性能：
- 插入数据：当向表中插入新行时，MySQL 不仅要将数据写入数据页，还要为所有相关的索引添加新的索引项。这涉及到修改多个 B+ 树结构（插入新节点、分裂节点等），操作越多，开销越大。
- 更新数据：如果更新的列是某个索引的一部分，那么该索引也需要被更新。这可能涉及到删除旧的索引项并插入新的索引项，或者直接修改索引项。
- 删除数据：删除行时，所有关联索引中的对应索引项也需要被删除。
- 这些操作都涉及到额外的磁盘 IO 和 CPU 开销，索引越多，这些操作的性能下降越明显。
增加查询优化器的复杂性：
- 当执行一个查询时，MySQL 的查询优化器需要决定如何执行这个查询（例如，使用哪个索引，表的连接顺序等）。如果索引数量过多，优化器需要评估更多的执行路径，这会增加优化器本身的工作量和时间。在某些情况下，优化器甚至可能做出错误的决策，选择了一个效率较低的索引，导致查询变慢。
增加内存消耗：
- 为了提高查询效率，MySQL 会尽可能地将索引数据缓存到内存中（如 InnoDB 的 Buffer Pool）。索引越多，需要缓存的数据量越大，这会占用更多的内存。如果内存不足以缓存所有热点索引，就会导致频繁的磁盘 I/O，反而降低性能。
维护成本：
- 索引的创建、重建、删除等操作都需要时间和资源。索引越多，管理和维护它们的复杂性就越高。

总结：

索引是一种空间换时间的策略，用额外的存储空间和写入性能开销来换取查询性能的提升。当索引数量过多时，这些成本会超出它带来的收益，导致系统整体性能下降。因此，建立索引时需要精心设计，只创建那些真正能带来显著性能提升的、有用的索引。

如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

题解

EXPLAIN 语句是 MySQL 提供的一个强大的工具，用于分析 SQL 查询语句的执行计划。通过它可以了解 MySQL 如何处理查询，包括表连接顺序、索引使用情况、扫描行数等，从而发现潜在的性能瓶颈。

使用方法：

在任何 SELECT, INSERT, UPDATE, DELETE 语句（MySQL 5.6.3 及更高版本也支持 EXPLAIN FOR CONNECTION）的前面加上 EXPLAIN 关键字即可。

sql

EXPLAIN SELECT column1, column2 FROM your_table WHERE column3 = 'value' ORDER BY column4;

EXPLAIN 结果中的关键字段及其含义：

理解这些字段是分析执行计划的关键。

id:
- 含义：SELECT 查询的序列号，表示查询中每个 SELECT 语句的执行顺序。
- 规则：ID 越大越先执行；ID 相同则从上到下顺序执行。
select_type:
- 含义：SELECT 查询的类型。
- 常见值：
  - SIMPLE：简单查询（不包含 UNION 或子查询）。
  - PRIMARY：最外层的 SELECT 查询。
  - SUBQUERY：子查询中的第一个 SELECT 查询。
  - DERIVED：派生表查询（FROM 子句中的子查询）。
  - UNION：UNION 中的第二个或后面的 SELECT 查询。
  - UNION RESULT：UNION 的结果。
table:
- 含义：正在访问的表名。
partitions: (MySQL 5.7+ 新增)
- 含义：查询将访问的分区（如果表使用了分区）。
type: 最重要的字段之一！
- 含义：表示 MySQL 查找数据的方式（访问类型），从最优到最差排序通常是：const > eq_ref > ref > range > index > ALL。
- 常见值及优化建议：
  - const / system: 查询优化器能将查询转换为一个常量。非常快，通常是主键或唯一索引的等值查询。
  - eq_ref: 唯一性索引扫描，表示前一个表的每一行都匹配到当前表的一个唯一行。通常用于联接查询。
  - ref: 非唯一性索引扫描，例如使用非唯一索引或唯一索引的前缀来查找。
  - range: 范围扫描，通过索引查找一个给定范围内的行（如 BETWEEN, >, <, IN）。
  - index: 全索引扫描，MySQL 扫描了整个索引来查找匹配的行。比 ALL 好，因为只扫描索引，不需要回表，但仍然是全表级别的。通常发生在覆盖索引查询，但没有 where 条件过滤。
  - ALL: 全表扫描。 这是最差的类型，意味着 MySQL 将遍历整个表来找到匹配的行。通常表明没有使用到索引，或者索引失效。应极力避免。
possible_keys:
- 含义：MySQL 在执行查询时可能选择的索引列表。
key:
- 含义：MySQL 实际使用的索引。 如果为NULL，则表示没有使用索引。这是你判断索引是否生效的关键。
key_len:
- 含义：使用的索引的长度（字节数）。对于联合索引，这个值可以帮助你判断索引使用了多少列。
ref:
- 含义：表示哪个列或常量与 key 一起使用来查找行。例如 const 表示常量，db.col_name 表示一个列。
rows: 非常重要的字段！
- 含义：估算 SQL 语句会扫描的行数。这个数字越小越好，它直接反映了查询效率。
filtered: (MySQL 5.1+ 新增)
- 含义：表示通过这个表条件过滤出的行百分比。rows * filtered / 100 表示最终将有多少行与下一张表进行连接。
Extra: 非常重要的字段！
- 含义：额外信息，通常能提供很多优化线索。
- 常见值及优化建议：
  - Using filesort: 警告！ MySQL 需要对结果集进行外部排序（在内存或磁盘上），通常是因为 ORDER BY 的列没有索引，或索引无法用于排序。性能较差。
  - Using temporary: 警告！ MySQL 使用了临时表来处理查询，通常发生在 GROUP BY 或 DISTINCT 与 ORDER BY 不同的列，或者复杂子查询中。性能较差。
  - Using index: 非常棒！ 表示使用了覆盖索引（Covering Index），查询所需的所有列都在索引中，无需回表。效率极高。
  - Using where: 表示 MySQL 将通过 WHERE 子句来过滤结果。通常配合 Using index 或 Using index condition 出现。
  - Using index condition: MySQL 5.6 引入的索引条件下推（ICP）。表示 MySQL 会在存储引擎层（索引层）进行条件过滤，而不是将所有匹配的索引条目都读取出来再在 MySQL 服务器层过滤。可以减少回表次数。
  - Using join buffer (Block Nested Loop) / Using join buffer (Batched Key Access): 表示使用了连接缓冲区，通常发生在没有索引的连接条件上。

分析步骤：

看 type：首要目标是避免 ALL 和 index。争取达到 ref、eq_ref、const。range 也是可以接受的。
看 key：确保实际使用了你期望的索引。
看 rows：这个值越小越好，它直接关联到性能。
看 Extra：特别关注是否有 Using filesort 或 Using temporary。如果有，表明排序或分组效率低，需要考虑添加索引或调整 SQL。出现 Using index 则是好兆头。

MySQL 中如何进行 SQL 调优？

题解

SQL 调优是一个系统性的过程，通常涉及以下几个方面：

找出慢查询
- 慢查询日志 (Slow Query Log)：MySQL 自带的功能，记录执行时间超过 long_query_time 阈值的 SQL 语句。这是最常用的方法。
  - 配置：slow_query_log = 1，slow_query_log_file = /path/to/slow.log，long_query_time = 1 (表示 1 秒)。
- 性能监控工具：
  - SHOW PROCESSLIST：查看当前正在执行的 SQL 语句，了解哪些查询耗时。
  - pt-query-digest (Percona Toolkit)：对慢查询日志进行分析，统计和汇总最慢的 SQL。
  - MySQL Enterprise Monitor：商业工具，提供图形化界面和更丰富的监控功能。
  - 第三方 APM 工具：如 SkyWalking, Pinpoint 等，可以追踪 SQL 调用链路。
使用 EXPLAIN 分析执行计划
这是 SQL 调优的核心步骤。
优化索引
- 创建合适的索引：
  - WHERE 条件列：优先考虑。
  - JOIN 条件列：确保连接列有索引。
  - ORDER BY 和 GROUP BY 列：考虑创建联合索引以覆盖这些操作，避免 filesort 和 temporary。
  - 选择性高的列：索引效果更好。
  - 覆盖索引：如果可能，让查询的所有列都在索引中，避免回表（Using index）。
- 优化联合索引：
  - 遵循最左前缀原则，将最常用的查询条件放在联合索引的最左边。
  - 将选择性高的列放在前面，但也要考虑查询模式。
- 避免索引失效：
  - 不要在索引列上使用函数或进行表达式运算。
  - 避免隐式类型转换。
  - 避免左模糊查询 (%keyword)。
  - 尽量避免 OR 连接不同索引列的条件。
- 删除不必要的索引：减少维护成本和磁盘占用。
优化 SQL 语句本身
1. 只查询必要的列 (SELECT 列)：避免 SELECT *，特别是当表很大，或者查询结果集很大时。这可以减少网络传输、内存消耗和回表操作。
2. 小批量操作：对于大批量的数据修改，可以考虑分批进行，减少锁时间。
3. 避免子查询（尤其是相关子查询）：很多子查询可以用 JOIN 或者 EXISTS / NOT EXISTS 来优化，通常 JOIN 性能更好。
4. 优化 JOIN 语句：
  - 确保 JOIN 条件列有索引。
  - 小表驱动大表（优化器通常会自动做，但了解其原理有益）。
  - 选择合适的 JOIN 类型（INNER JOIN, LEFT JOIN 等）。
5. 优化 COUNT(*)：
  - 对于 InnoDB，COUNT(*) 通常需要全表扫描。
  - 如果只需大概值，可以从统计信息中获取。
  - 如果需要精确值，并且有 WHERE 条件，确保 WHERE 条件有索引。
  - 如果 COUNT(*) 是为了判断是否存在记录，用 EXISTS 或 LIMIT 1 更高效。
6. 优化 LIMIT 分页：
  - LIMIT offset, rows 当 offset 很大时效率会很低。
  - 优化方法：SELECT * FROM table_name WHERE id > (SELECT MAX(id) FROM table_name WHERE condition LIMIT offset, 1) LIMIT rows; 或者通过覆盖索引优化 LIMIT。
7. 避免 HAVING：HAVING 是在 GROUP BY 之后进行过滤，如果过滤条件可以提前到 WHERE 中，就尽量提前。
8. 使用 UNION ALL 代替 UNION：如果不需要去重，UNION ALL 效率更高。
9. 避免 SELECT DISTINCT 大量数据：DISTINCT 会增加额外的排序和去重开销。
10. 使用批量插入：INSERT INTO table_name VALUES (...), (...), (...); 比单条插入效率高。
调整数据库配置
- innodb_buffer_pool_size：最重要的参数，决定了 InnoDB 缓存数据和索引的内存大小。越大越好（在服务器内存允许的情况下）。
- innodb_log_file_size：影响事务日志写入性能。
- tmp_table_size / max_heap_table_size：影响内存中临时表的大小，过小可能导致临时表写入磁盘。
- sort_buffer_size：排序缓冲区大小。
- join_buffer_size：连接缓冲区大小。
数据库结构优化（Schema Design）
- 数据类型选择：选择合适且尽可能小的数据类型（例如 INT 而不是 BIGINT，CHAR 而不是 VARCHAR(255) 如果长度固定）。
- 范式与反范式：适当的反范式化（冗余数据）可以减少 JOIN 操作，但会增加数据一致性的维护成本。
- 垂直分表：将大表按列拆分成小表，减少每行数据的大小，提高查询效率。
- 水平分表/分库：当单表数据量非常大时，进行分库分表可以分散压力。

请详细描述 MySQL 的 B+ 树中查询数据的全过程

题解

MySQL 的 InnoDB 存储引擎使用 B+ 树作为其索引结构。查询数据时，MySQL 会根据查询条件（特别是 WHERE 子句）来决定如何利用 B+ 树索引来定位数据。

B+ 树的结构回顾：

特点：B+ 树是一种多路搜索树，所有的数据都存储在叶子节点，非叶子节点只存储键值和子节点的指针。叶子节点之间通过双向链表连接，方便范围查找。
节点大小：InnoDB 默认的 B+ 树节点（页）大小是 16KB。
索引类型：
- 聚簇索引：InnoDB 表的主键索引就是聚簇索引。它的叶子节点存储了整行数据。数据行是按照主键的顺序物理存储的，因此一张表只能有一个聚簇索引。
- 辅助索引（非聚簇索引）：除了主键索引之外的其他索引。它的叶子节点存储的是索引列的值和主键值。

查询数据的全过程（根据查询条件分类）:

场景一：通过主键查询（使用聚簇索引）
假设表 user 有主键 id，查询 SELECT * FROM user WHERE id = 123;
1. 从根节点开始搜索：
  - MySQL 会从聚簇索引的根节点开始（根节点通常在内存中，或通过一次磁盘 I/O 加载）。
  - 根节点是一个非叶子节点，包含多个键值和指向子节点的指针。MySQL 会比较查询的 id 值 123 与当前节点中的键值，通过二分查找法（或类似高效查找算法）快速定位到下一个子节点的指针。
  - 例如，根节点有键值 100 和 200，123 在 100 和 200 之间，则指向对应的子节点。
2. 逐层向下搜索：
  - 重复第一步的过程，逐层向下遍历 B+ 树，直到达到叶子节点层。
  - 每一层通常只需要一次磁盘 I/O（如果该页不在 Buffer Pool 中）。对于 3 层 B+ 树，这最多是 2 次磁盘 I/O（根节点到叶子节点）。
3. 定位到叶子节点并获取数据：
  - 在叶子节点层，根据 id = 123 精确找到包含该主键值的叶子节点。
  - 由于叶子节点存储的就是完整的行数据，MySQL 直接从该叶子节点中读取 id = 123 对应的那一行所有数据。
4. 返回结果：将查询到的数据返回给客户端。
特点：效率非常高，通常只需要很少的几次磁盘 I/O（因为 B+ 树层数很少，且直接获取完整数据）。
场景二：通过辅助索引查询，且需要回表（非覆盖索引）
假设表 user 有辅助索引 idx_name 在 name 列上，查询 SELECT * FROM user WHERE name = 'Alice';
1. 从辅助索引的根节点开始搜索：
  - MySQL 从辅助索引 idx_name 的根节点开始。
  - 通过比较 name 值 'Alice'，逐层向下遍历辅助索引的 B+ 树，直到达到叶子节点层。
  - 此过程与通过主键查询类似，每层最多一次磁盘 I/O。
2. 定位到辅助索引的叶子节点，获取主键值：
  - 在辅助索引的叶子节点，找到所有 name = 'Alice' 的条目。
  - 这些叶子节点条目中不包含完整的行数据，它们只存储了 name 值和对应的主键值（假设是 id）。
  - 例如，如果有多条记录叫 'Alice'，会得到多个 id 值，如 id = 101, id = 205, id = 310。
3. 回表（二次查找）：
  - 对于每一个在辅助索引叶子节点中获取到的主键值（例如 101, 205, 310），MySQL 需要再次进行一次查询操作。
  - 它会拿着这些主键值，回到聚簇索引（主键索引）中，按照场景一中描述的通过主键查询的过程，去查找并获取这些主键值对应的完整行数据。
  - 这个“回到聚簇索引”的过程，就叫做回表（Look-up / Index Lookup）。
4. 返回结果：将所有回表获取到的完整数据返回给客户端。
特点：比主键查询慢，因为需要两次查找（一次辅助索引，一次聚簇索引）且可能多次回表 I/O。回表次数越多，性能越差。
场景三：通过辅助索引查询，且不需要回表（覆盖索引）
假设表 user 有辅助索引 idx_name_age 在 (name, age) 列上，查询 SELECT name, age FROM user WHERE name = 'Alice';
1. 从辅助索引的根节点开始搜索：
  - MySQL 从辅助索引 idx_name_age 的根节点开始。
  - 通过比较 name 值 'Alice'，逐层向下遍历辅助索引的 B+ 树，直到达到叶子节点层。
2. 定位到辅助索引的叶子节点，获取所有所需数据：
  - 在辅助索引的叶子节点，找到所有 name = 'Alice' 的条目。
  - 由于查询的 SELECT 列表只包含 name 和 age 这两列，而这两列的值都在 idx_name_age 这个辅助索引的叶子节点中可以直接获取到（辅助索引的叶子节点存储了 name, age 以及主键 id）。
  - 因此，MySQL 可以直接从辅助索引的叶子节点中获取 name 和 age 的值，而无需再回表到聚簇索引去查找。
3. 返回结果：将所有从辅助索引直接获取到的数据返回给客户端。
特点：效率非常高，接近主键查询，因为它只需要遍历一次辅助索引的 B+ 树，避免了回表操作。EXPLAIN 结果中 Extra 字段会显示 Using index。

MySQL 中 `count(*)`、`count(1)` 和 `count(字段名)` 有什么区别？

题解

这三个 COUNT 函数在功能上都用于计算行数，但在执行效率和统计范围上有一些细微的区别。

count(*)
- 含义：统计表中所有行的数量，包括包含 NULL 值的行。
- 执行方式：
  - InnoDB 存储引擎：count(*) 会找到一个最小的（通常是主键）辅助索引进行遍历，然后计数。因为它不需要读取实际数据行，只需要读取索引的叶子节点。如果没有任何辅助索引，会选择聚簇索引（全表扫描），效率最低。
  - Myisam 存储引擎：Myisam 引擎维护了一个精确的行数计数器，所以 count(*) 的执行速度非常快，是 O(1) 操作。
- 推荐：在大多数情况下，特别是对于 InnoDB 表，COUNT(*) 是官方推荐的统计行数方式。 MySQL 的优化器会对其进行优化，选择最高效的索引来计数，不关心列值。
count(1)
- 含义：统计表中所有行的数量，包括包含 NULL 值的行。这里的 1 只是一个常量，表示每找到一行就计数一次。
- 执行方式：
  - 与 count(*) 类似，对于 InnoDB，它也会选择一个最小的辅助索引（或者主键索引）进行遍历计数，因为它也不关心具体的列值。
  - 在 MySQL 8.0 之前，很多人认为 count(1) 比 count(*) 效率更高，但实际上，MySQL 优化器对 count(*) 有特殊优化，两者的性能几乎没有区别，甚至在某些版本 count(*) 可能略优。
- 总结：在绝大多数情况下，count(1) 和 count(*) 性能相同。
count(字段名)
- 含义：统计指定字段不为 NULL的行数。
- 执行方式：
  - MySQL 会遍历指定字段所在的索引（如果该字段有索引的话）。如果该字段没有索引，MySQL 会进行全表扫描，然后对每一行读取该字段的值，判断是否为 NULL，最后计数。
  - 如果字段有索引，并且这个索引是单列索引或者联合索引的第一列，那么 MySQL 会利用这个索引进行扫描计数。
  - 如果该字段没有索引，或者不是联合索引的最左列，或者查询优化器判断走索引不如全表扫描，那么它会进行全表扫描，并读取每一行的数据来检查该字段是否为 NULL。
- 区别：
  - 与 count(*) / count(1) 的主要区别在于对 NULL 值的处理。 count(字段名) 不会统计 NULL 值，而 count(*) 和 count(1) 会。
  - 通常情况下，count(字段名) 的效率会低于 count(*) 或 count(1)，因为它需要去读取字段值来判断是否为 NULL，这可能导致更多的 IO 操作（回表），特别是当这个字段没有索引时。

总结：

最佳实践：绝大多数情况下，使用 COUNT(*) 来统计行数。它最简洁，且 MySQL 优化器对其有最好的优化。
特殊场景：如果你确实需要统计某一列非 NULL 值的行数，才使用 COUNT(字段名)。但要注意性能影响。

MySQL 中 varchar 和 char 有什么区别？

题解

VARCHAR 和 CHAR 是 MySQL 中用于存储字符串的两种主要数据类型，它们在存储方式、空间占用、性能以及用途上都有显著区别。

CHAR(M) (定长字符串)
- 存储方式：定长存储。M 表示字符数，无论实际存储的字符串有多长，它都会占用 M 个字符的存储空间。不足 M 的字符会用空格填充到 M 长度。
- 空间占用：固定占用 M * 字符集最大字节数 的空间。例如，CHAR(10) 使用 UTF8 字符集，将占用 10 * 3 = 30 字节（因为 UTF8 一个字符最大 3 字节），即使只存了一个字符。
- 存储与检索：
  - 存储：写入时，如果实际字符串长度小于 M，会在右侧填充空格直到 M 长度。
  - 检索：读取时，MySQL 会删除尾部填充的空格（但如果原始数据本身就有尾部空格，这些空格也会被删除，可能导致数据丢失语义）。
- 性能：
  - 写入：相对较快，因为存储空间是固定的，不需要额外的长度计算或动态分配。
  - 读取：相对较快，因为长度固定，可以直接定位和读取。
- 适用场景：
  - 存储长度固定或近似固定的字符串，如 MD5 哈希值（CHAR(32)）、国家代码（CHAR(2)）、性别（CHAR(1)）。
  - 如果字段长度变化不大，并且查询频繁，CHAR 可能提供更好的性能。
VARCHAR(M) (变长字符串)
- 存储方式：变长存储。M 表示字符数，它只存储实际需要的字符数，外加 1 或 2 个字节来记录字符串的实际长度。
- 空间占用：实际字符串长度 + 1 或 2 字节（取决于 M 的大小）。
  - 如果 M <= 255，需要 1 个字节记录长度。
  - 如果 M > 255，需要 2 个字节记录长度。
  - 例如，VARCHAR(100) 使用 UTF8 字符集，存储 'hello' (5 个字符) 只占用 5 * 3 + 1 = 16 字节。
- 存储与检索：
  - 存储：只存储实际字符，不填充空格。
  - 检索：读取时，根据记录的长度精确读取，不会丢失尾部空格。
- 性能：
  - 写入：相对较慢，因为需要额外的长度计算和动态空间分配。当数据修改后长度变化时，可能会导致行迁移（Row Migration），影响性能。
  - 读取：相对较慢（与 CHAR 相比），因为需要先读取长度字节，然后根据长度读取数据。
- 适用场景：
  - 存储长度不固定或变化较大的字符串，如姓名、地址、评论、文章标题等。
  - 这是最常用的字符串类型。

主要区别总结表格：

特性	`CHAR(M)`	`VARCHAR(M)`
存储方式	定长，M 个字符	变长，实际字符长度 + 1 或 2 字节用于记录长度
空间占用	固定占用，`M * 字符集最大字节数`	弹性占用，`实际字符长度 * 字符集最大字节数 + (1或2)`
尾部空格	存储时填充，检索时自动去除（可能丢失）	存储和检索都保留
最大长度	M (字符数)	M (字符数)
存储上限	M 最大为 255 字符	M 最大为 65535 字符，但受限于行最大长度 65535 字节
写入性能	相对快	相对慢（可能导致行迁移）
读取性能	相对快	相对慢
适用场景	长度固定或接近固定，例如 MD5、身份证号	长度不固定，例如姓名、地址、文章标题
内部处理	不会产生碎片（若 M 不变）	可能产生碎片，导致行迁移

选择建议：

优先使用 VARCHAR：在大多数情况下，如果字符串长度不固定，或者你无法确定确切的固定长度，VARCHAR 是更好的选择，因为它能节省大量的存储空间。存储空间的节省通常比 CHAR 带来的微小性能提升更重要，尤其是在大表中。
何时使用 CHAR：
- 当字符串长度总是固定时，例如 MD5 校验和（32 个字符）。
- 当存储非常短的字符串，且经常被更新，且长度固定。
- 存储较少的字符，例如 CHAR(1) 表示性别，虽然 VARCHAR(1) 更节省空间，但其额外的长度字节可能导致实际占用空间一样。

贡献者

LI SIR

页面历史

最后编辑于大约 2 小时前

查看完整历史

MySQL 面试题 ​

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

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

MySQL 的索引类型有哪些？ ​

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

MySQL 索引的最左前缀匹配原则是什么？ ​

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

MySQL 中的回表是什么？ ​

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

在 MySQL 中建索引时需要注意哪些事项？ ​

MySQL 中的索引数量是否越多越好？为什么？ ​

如何使用 MySQL 的 EXPLAIN 语句进行查询分析？ ​

MySQL 中如何进行 SQL 调优？ ​

请详细描述 MySQL 的 B+ 树中查询数据的全过程 ​

MySQL 中 count(*)、count(1) 和 count(字段名) 有什么区别？ ​

MySQL 中 varchar 和 char 有什么区别？ ​

贡献者