掘金后端 ( ) • 2024-05-17 10:12

文章目录一、存储引擎

1.1 MySQL体系结构
1.2 存储引擎介绍
1.3 存储引擎特点
- 1.3.1 InnoDB
- 1.3.2 MyISAM
- 1.3.3 Memory
- 1.3.4 区别及特点
- 1.3.5 面试题：InnoDB引擎与MyISAM引擎的区别
1.4 存储引擎选择

二、索引

2.1 索引是什么
- 2.1.1 相关数据结构：二叉树、红黑树、B-Tree、B+Tree、Hash...
- 2.1.2 演示
- 2.1.3 特点、优缺点
- 2.1.4 面试题：为什么InnoDB存储引擎选择使用B+Tree索引结构
2.2 MySQL索引有哪些类型
- 2.2.1 数据结构维度
- 2.2.2 物理存储维度：聚簇索引&非聚簇索引
- 2.2.3 逻辑维度
2.3 主键索引和唯一索引有什么区别
2.4 什么是聚簇索引和非聚簇索引？它们在InnoDB存储引擎中是如何工作的
2.5 复合索引和单列索引有何区别？
2.6 Hash 索引和 B+ 树索引区别是什么？如何选择
2.7 索引是否越多越好？为什么？
2.8 索引什么时候会失效？
2.9 哪些情况下适合建立索引
2.10 为什么要用 B+ 树，而不用二叉树？
2.11 什么是回表？如何减少回表？
2.12 能否解释什么是位图索引，以及它在MySQL中的使用场景？
2.13 如何查看MySQL表中已有的索引？
2.14 如何在MySQL中创建全文索引，并说明全文索引的使用场景？
2.15 当表中的数据量非常大时，如何有效地维护和管理索引，以确保查询性能？
2.16 假设你有一个包含大量数据的表，并且经常需要根据某个字段进行排序。你应如何优化这个字段的索引以提高排序操作的性能？
2.17 如何优化索引
2.18 请谈谈你对 MySQL 索引碎片化的理解，并说明如何检测和修复索引碎片化

一、存储引擎

1.1 MySQL体系结构

1）连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2）服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3）引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。

4）存储层

数据存储层，主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.2 存储引擎介绍

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

1）建表时指定存储引擎

CREATE TABLE 表名(
    字段1 字段1类型 [ COMMENT 字段1注释 ] ,
    ......
    字段n 字段n类型 [COMMENT 字段n注释 ] 
) ENGINE = INNODB [ COMMENT 表注释 ] ;

show create table 表名;   #查看建表语句

2）查询当前数据库支持的存储引擎

show engines;

1.3 存储引擎特点

上面我们介绍了什么是存储引擎，以及如何在建表时如何指定存储引擎，接下来我们就来重点介绍三种存储引擎 InnoDB、MyISAM、Memory的特点。

1.3.1 InnoDB

1）介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

2）特点

DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

3）文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的、sdi-新版的）、数据和索引。

参数：innodb_file_per_table

show variables like 'innodb_file_per_table';

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。我们直接打开MySQL的数据存放目录： C:\ProgramData\MySQL\MySQL Server 8.0\Data ，这个目录下有很多文件夹，不同的文件夹代表不同的数据库，我们直接打开jw（对应某个数据库）文件夹。

可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

4）逻辑存储结构

表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
段 : 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
区 : 区是表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。
页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

1.3.2 MyISAM

1）介绍

MyISAM是MySQL早期的默认存储引擎。

2）特点

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

3）文件

xxx.sdi：存储表结构信息

xxx.MYD: 存储数据

xxx.MYI: 存储索引

1.3.3 Memory

1）介绍

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

2）特点

内存存放
hash索引（默认）

3）文件

xxx.sdi：存储表结构信息【数据存放在内存中，xxx.sdi存放在D:\SoftwareInstall\mysql-5.7.42-winx64\data\databaseName\xxx.sdi】

1.3.4 区别及特点

特点 InnoDB MyISAM Memory 存储限制 64TB 有有事务安全支持 - - 锁机制行锁表锁表锁 B+tree索引支持支持支持 Hash索引 - - 支持全文索引支持(5.6版本之后) 支持 - 空间使用高低 N/A 内存使用高低中等批量插入速度低高高支持外键支持 - -

1.3.5 面试题：InnoDB引擎与MyISAM引擎的区别

①InnoDB引擎, 支持事务, 而MyISAM不支持。

②InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。

③InnoDB引擎, 支持外键, 而MyISAM是不支持的。

主要是上述三点区别，当然也可以从索引结构、存储限制等方面，更加深入的回答，具体参考如下官方文档：

https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

1.4 存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。存储业务系统中对于事务、数据完整性要求较高的核心数据
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。 存储业务系统的非核心事务【MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。】（业务系统中的日志、电商系统中的足迹/评论）【被NoSQL--MongoDB替代】
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。【被NoSQL--Redis替代】

二、索引

2.1 索引是什么

索引是一种数据结构，用来帮助提升查询和检索数据速度。可以理解为一本书的目录，帮助定位数据位置。
索引是一个文件，它要占用物理空间。

索引概述：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2.1.1 相关数据结构：二叉树、红黑树、B-Tree、B+Tree、Hash...

二叉树缺点：
- 顺序插入时，会形成一个链表，查询性能大大降低。
- 大数据量情况下，层级较深，检索速度慢。
红黑树：大数据量情况下，层级较深，检索速度慢。

（红黑树是一颗自平衡二叉树，那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树，解决顺序插入形成链表的问题。但红黑树仍存在”大数据量情况下，层级较深，检索速度慢“）

所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，那么什么是B+Tree呢？在详解B+Tree之前，先来介绍一个B-Tree。
B-Tree（多路平衡查找树，也叫B树）：相对于二叉树，B树每个节点可以有多个分支，即多叉。特点如下
- 5阶的B树，每一个节点最多存储4个key，对应5个指针
- 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂
- 在B树中，非叶子节点和叶子节点都会存放数据
B+Tree 相对于B-Tree区别：
- 所有的数据都会出现在叶子节点
- 叶子节点形成一个单向链表。
- 非叶子节点不存储具体数据、只起到索引数据的作用，具体的数据都是在叶子节点存放的
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。
Hash索引：采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。Hash索引特点：
- Hash索引只能用于对等比较(=，in)，不支持范围查询(between,>,<,...)
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

二叉树、红黑树：

B-Tree（B树，多路平衡查找树）：

我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree

B+Tree是B-Tree的变种

我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的B+Tree。

Hash索引

MySQL中除了支持B+Tree索引，还支持一种索引类型---Hash索引。

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

Hash索引特点：

Hash索引只能用于对等比较(=，in)，不支持范围查询(between,>,<,...)
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持：在MySQL中，支持hash索引的是Memory存储引擎。而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

2.1.2 演示

表结构及其数据如下，针对无索引、有索引两种情况

备注：这里我们只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构，后面会详细介绍。

2.1.3 特点、优缺点

优势劣势提高数据检索的效率，降低数据库的IO成本索引列也是要占用空间的。通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

2.1.4 面试题：为什么InnoDB存储引擎选择使用B+Tree索引结构

相对于二叉树，层级更少，搜索效率高；
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
相对Hash索引，B+tree支持范围匹配及排序操作；

2.2 MySQL索引有哪些类型

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种【我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引】：

2.2.1 数据结构维度

B+tree 索引： B+树是最常用的索引类型，大部分引擎都支持 B+ 树索引，所有数据都会存储在叶子节点上，时间复杂度是 O(logn) ，擅长范围查询。
Hash 索引：哈希索引就是采用哈希算法，将键值换算成新的哈希值，映射到对应槽位，然后存储到哈希表中，擅长做对等比较（=，in），不支持范围查询。
Full-text 索引：全文索引是一种建立倒排索引，实现信息检索。在 MySQL 不同版本中支持程度不同。类似于Lucene,Solr,ES
R-Tree 索引：空间索引是MyISAM引擎的一个特殊索引类型，属于地理空间数据类型查询，通常使用较少。

2.2.2 物理存储维度：聚簇索引&非聚簇索引

簇 cù

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚簇索引： InnoDB 引擎 要求必须有聚簇索引，也就是在主键字段建立聚簇索引。
非聚簇索引：非聚簇索引就是以非主键创建的索引，在叶子节点存储的是表主键和索引列。 InnoDB 引擎

分类含义特点聚集索引(Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个二级索引(Secondary Index) 也叫辅助索引，将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个

聚集索引之所以必须有是因为它是用来存储数据的，而二级索引更多地用来更快更高效地查询数据。

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引的叶子节点下挂的是这一行的数据。

二级索引的叶子节点下挂的是该字段值对应的主键值

分析执行SQL语句时，具体的查找过程是什么样子的：select * from user where name='Arm'; id为主键，name字段创建的有索引

具体过程如下：

①由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③最终拿到这一行的数据，直接返回即可。

回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。（先走二级索引找到主键值，再根据主键值到聚集索引中找到对应的行数据）

2.2.3 逻辑维度

主键索引：主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
普通索引：普通索引是 MySQL 中最基本的索引类型，允许在定义索引的列中插入重复值和空值。
联合索引：联合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用联合索引时遵循最左前缀集合。
唯一索引：唯一索引列的值必须唯一，允许有空值。
空间索引：空间索引是一种针对空间数据类型（如点、线、多边形等）建立的特殊索引，用于加速地理空间数据的查询和检索操作。

分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建 PRIMARY 唯一索引避免同一个表中某数据列中的值重复可以有多个 UNIQUE 常规索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个 FULLTEXT

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况。

索引 InnoDB MyISAM Memory B+tree索引支持支持支持 Hash索引不支持不支持支持 R-tree索引不支持支持不支持 Full-text 5.6版本之后支持支持不支持

注意：我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引

2.3 主键索引和唯一索引有什么区别

数量限制：唯一索引有多个，但是主键索引一张表只能有一个。
本质区别：被唯一索引约束的健可以为空，主键索引不可以。
外键引用：主键可以被其他表作为外键，从而建立表之间的关系。而唯一索引则不能被其他表用作外键

2.4 什么是聚簇索引和非聚簇索引？它们在InnoDB存储引擎中是如何工作的

聚簇索引是将表的数据按照索引顺序存储在磁盘上，聚簇索引的叶子节点直接存储了实际的数据行，而不是指向数据的指针。所以在查询的时候减少了磁盘的随机读取，无需进行多次磁盘I/O效率很高。

非聚簇索引是一种基于指针的索引，有时也叫它二级索引。非聚簇索引不直接存储实际的数据，seelec 语句在执行查询时，会先根据二级索引定位到数据所在的磁盘位置，然后再进行一次磁盘I/O操作，读取实际的数据行。

2.5 复合索引和单列索引有何区别？

顾名思义，单列索引就是在一个列上创建的索引，复合索引就是多个列上创建的索引。
当只涉及到一个字段查询，单列是非常快速的。当涉及到多个字段查询，WHERE 子句引用了符合索引的所有列或者前导列时，查询速度会非常快。
在复合索引中，列的顺序非常重要。MySQL会按照索引中列的顺序从左到右进行匹配。例如，对于复合索引(a, b, c)，它可以支持a、a,b和a,b,c三种组合的查询，但不支持b,c进行查询。因此，在创建复合索引时，应把最常被访问和选择性较高的列放在前面。

当然具体如何选择需要看查询需求、数据分布和性能要求。

2.6 Hash 索引和 B+ 树索引区别是什么？如何选择

哈希索引：

工作原理：通过哈希算法将被索引的列的值存储到一个固定长度的桶（Bucket）。使得在查询特定值的时候非常高效，因为可以直接计算出存储位置，快速定位到数据。
查询效率：在等值查询下，哈希查询效率极高，可以在常数时间复杂度内定位到目标数据。但是范围查询和排序操作时，哈希索引的效率较低，因为哈希算法会导致数据随机分布，无法保持原有的顺序。
磁盘存储：hash 索引的存储是随机的，可能导致磁盘的随机访问，从而降低磁盘的利用效率和查询效率。
插入和删除操作：Hash 索引在插入和删除操作方面相对简单，只需要通过哈希函数确定存储位置即可。

B+树索引

工作原理：B+树索引使用平衡树，将索引健的值按照顺序保存在树节点中，根据键值的大小关系，并通过节点之间的指针进行查找，快速定位存储了数据的叶子节点。
查询效率：B+树擅长范围查询和排序操作，因为他是按照顺序存储数据，可以高效的支持范围查询和排序操作。
磁盘存储：B+树索引的节点是有序存储的，有利于磁盘的顺序访问，从而减少磁盘的IO次数，提高查询效率。
插入和删除操作：B+树在索引删除和插入操作时，需要维护树的平衡，可能进行节点的拆分和合并，相对哈希索引来说操作更复杂。

所以在选择上：

查询维度：如果查询主要是等值查询，且对性能要求较高，Hash 索引可能是一个好的选择。然而，如果查询涉及到范围查询、排序操作或模糊查询，B+ 树索引则更为合适。
数据维度：如果索引列具有大量重复值，Hash索引的效率可能会下降，因为哈希碰撞会导致性能下降。在这种情况下，B+ 树索引可能更为稳定。
磁盘存储和I/O维度：由于 Hash 索引可能导致磁盘的随机访问，如果磁盘 IO 是性能瓶颈，那么 B+ 树索引可能更适合，因为它更有利于磁盘的顺序访问。

从这三个维度可以很好的应用在你的开发工作中，如果是小数据量的 web 网站查询、直接用 B+ 树就可以了。对于数据量的大小评估，后面单开一篇讲解。

2.7 索引是否越多越好？为什么？

不是。索引是建立在原数据上的数据结构，所以不论在查询还是更新维护、一定会带来开销。

比如一本书有 100 页，我构建了 50 页的目录，你觉查询起来还会方便吗？

数据量小的表不需要建立索引，建立索引反而会增加额外开销。
数据变更后索引也需要更新，更多的索引意味着更多的维护成本。
索引是放在磁盘的，更能的索引也意味着更多的存储空间。
数据重复且分布平均的字短没必要建立索引（比如：性别）

索引并非银弹，正确使用才能发挥奇效。

2.8 索引什么时候会失效？

慢 SQL 是数据库使用中最长遇见的问题，当遇到慢 SQL 时，首先我们就要去看是不是索引失效。一般会有以下几种常见的情况：

1.Where 条件中包含 OR：当查询条件中包含 OR，即使其中某些条件带有索引，也会全表扫描。下例中 username 没有索引，就算 id 走了索引也需要全表扫描，所以引擎大概率不会走索引。

失效索引： id 有索引， username 没有索引。

explain select * from t_user where id = 2 or username = 'jw';

2.多列索引没有最左匹配：对于复合索引，如果查询条件没有从索引的第一部分匹配，则不会使用索引。也就是我们在使用联合索引时，要正确使用最左匹配。

例如，如果你有一个(id, name)的多列索引，但查询条件只使用了name，那么索引不会被使用。

3.LIKE 查询以%开头：当使用LIKE操作符进行模糊查询，并且模式以%开头时，索引将不会生效。这是因为以%开头的模式匹配意味着匹配的字符串可以在任何位置，这使得索引无法有效定位数据。

4.索引列参与计算：当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。比如：

select * from t_user where id > age;

5.类型不匹配导致隐式转换：当表里存的是 varchar 类型的字段时，用 int 类型去查询，导致全表扫描。如下例子中：

explain select * from t_user where id_no = 1002;

表里的 id_no 是 varchar 类型。

出了这几种情况还有一些导致索引失效。 例如：

全表扫描效率更优：在某些情况下，MySQL 优化器可能认为全表扫描比使用索引更快。
数据分布不均：如果索引列的数据分布非常不均匀，MySQL 可能不会选择使用索引。
索引列包含 NULL 值：如果索引列包含 NULL 值，MySQL可能不会使用索引，因为 NULL 值的比较有特殊性。因为NULL值无法与其他值进行比较或匹配，所以无法使用索引。

2.9 哪些情况下适合建立索引

高频查询列：对于经常出现在查询条件中的列，建立索引可以加快查询速度。例如，经常根据username或email字段查询的用户表。
作为连接键的列：在执行表连接操作时，用于连接的列（通常在ON子句中指定）应该建立索引，以加快连接操作的速度。
具有唯一性约束的列：对于需要保证唯一性的列，如主键或具有唯一约束的列，建立索引是必要的，因为索引可以帮助快速检查重复的数据。
排序和分组操作的列：在ORDER BY、GROUP BY或DISTINCT操作中使用的列，通过建立索引可以加快排序和分组的处理速度。
具有高选择性的列：选择性是指不同值的数量与总行数的比率。具有高选择性的列（即列中的值分布广泛）适合建立索引，因为这样的索引可以更有效地缩小搜索范围。
多列查询的前导列：如果你经常执行涉及多个列的查询，可以在这些列上建立组合索引，其中最常用作查询条件的列应该放在索引的最前面。
数据量大的表：对于数据量较大的表，合理地建立索引可以大幅提高查询效率。但是，对于数据量小的表，由于数据量本身就少，索引可能不会带来太大的性能提升，反而可能增加插入、更新和删除操作的开销。

在考虑建立索引时，也需要考虑以下因素：

更新频率：频繁更新的列可能不适合建立索引，因为每次更新都可能导致索引的重新构建，增加开销。
索引的维护成本：索引不仅占用存储空间，还会增加数据插入、删除和更新操作的维护成本。
查询类型：需要分析查询类型，确保索引能够被有效利用。例如，对于只读或几乎只读的表，建立索引可能没有太大必要。

2.10 为什么要用 B+ 树，而不用二叉树？

查询性能稳定： B+树通过多层索引结构，使得查询性能更加稳定。在最坏的情况下，B+树的查询时间复杂度仍然是对数级别（O(log n)），而二叉树在最坏情况下（退化成链表）的时间复杂度为线性（O(n)）。这意味着即使数据分布极不均匀，B+树也能保持较高的查询效率。
空间局部性： B+树的叶子节点包含了所有数据记录，并且通过指针相互连接，形成了一个有序链表。这种结构使得范围查询和顺序访问更加高效，因为相邻的数据在物理存储上也是相邻的。而二叉树不具备这种空间局部性，数据的物理存储位置可能分散。
磁盘I/O优化：数据库操作经常涉及磁盘I/O，B+树的设计更适合减少磁盘访问次数。由于B+树的非叶子节点不存储实际数据，可以使得每个节点包含更多的键值，从而降低树的高度。这样，在一次磁盘I/O操作中可以读取更多的索引信息，减少了I/O次数。
高效的范围查询和排序： B+树的有序链表结构使得它在执行范围查询和排序操作时非常高效。而二叉树需要进行中序遍历才能得到有序的结果，效率较低。
节点分裂和合并的开销：在二叉树中，插入和删除操作可能导致频繁的节点分裂和合并，增加了操作的复杂性。B+树通过减少节点分裂和合并的次数，降低了维护开销。
非叶子节点的简洁性： B+树的非叶子节点仅用于索引，不存储实际数据，这样可以使得每个节点包含更多的键值对，进一步降低树的高度。
更新操作的效率：由于B+树的高度通常较低，更新操作（插入、删除）时需要遍历的节点数量较少，从而提高了更新操作的效率。

总的来说，B+树在数据库索引中提供了更稳定的查询性能、优化的磁盘I/O操作、高效的范围查询和排序，以及较低的维护成本。

2.11 什么是回表？如何减少回表？

回表定义： MySQL回表查询是指在使用索引进行查询时，MySQL数据库引擎在通过索引定位到数据行后，发现需要访问表中的其他列数据，而不是直接通过索引就能获取到所需的数据。这种情况下，MySQL需要再次访问表中的数据行，这个过程就称为回表查询（Referring to the table）。

覆盖索引：覆盖索引是指一个查询可以完全通过索引来得到结果，而不需要访问数据表的行。如果查询只需要索引中包含的字段，那么就无需回表。设计良好的覆盖索引可以显著减少回表操作。
**避免SELECT ***：在编写查询时，尽量指定需要的列，而不是使用SELECT *来选择所有列。这样可以减少不必要的数据访问，从而减少回表。
索引包含所需列：确保查询中涉及的列都被包含在索引中。如果索引包含了所有需要的列，那么查询可能不需要回表。
使用复合索引：如果查询经常根据多个列进行过滤，可以考虑创建一个包含这些列的复合索引。这样可以在一个索引中完成查询，减少回表。
优化查询逻辑：分析查询逻辑，尽量减少不必要的回表操作。例如，如果查询中的某些条件不太可能同时满足，可以考虑将它们分开处理，或者使用临时表来存储中间结果。
使用物化视图或汇总表：对于频繁执行的复杂查询，可以考虑使用物化视图或汇总表来存储查询结果。这样，当需要这些数据时，可以直接从物化视图或汇总表中获取，而无需进行回表操作。

当然，不是所有情况都不允许回表，有时候，适当的回表是必要的，因为索引的设计需要平衡查询性能和存储空间的利用。

2.12 能否解释什么是位图索引，以及它在MySQL中的使用场景？

位图索引是一种将数据列的所有可能值映射到二进制位上的索引。每个位表示某个值是否存在于该列中，从而帮助我们快速定位符合某个条件的行。与其他类型的索引相比，位图索引通常在低基数列（即列中有限的不同值）上表现更好。

可以参考 bitmap 数据结构来理解

例子：

在该示例中，我们为 age 和 country 列分别创建了位图索引。由于使用了位图索引，查询性能将大大提高。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    country VARCHAR(50)
);

CREATE BITMAP INDEX idx_age ON users(age);
CREATE BITMAP INDEX idx_country ON users(country);

SELECT * FROM users WHERE age = 20 AND country = 'China';

2.13 如何查看MySQL表中已有的索引？

两种方式：

使用 SHOW INDEX，也是最常用的。

SHOW INDEX FROM your_table_name;

查询 information_schema 数据库，information_schema 是 MySQL 中包含元数据的特殊数据库。我可以查询其中的 TABLES 和 STATISTICS 表来获取索引信息。

SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    NON_UNIQUE, 
    INDEX_NAME, 
    INDEX_TYPE, 
    INDEX_COMMENT, 
    SEQ_IN_INDEX, 
    COLUMN_NAME, 
    CARDINALITY, 
    SUB_PART, 
    PACKED, 
    NULLABLE, 
    INDEX_DIR, 
    INDEX_DISC 
FROM 
    information_schema.STATISTICS 
WHERE 
    TABLE_SCHEMA = 'your_database_name' AND 
    TABLE_NAME = 'your_table_name';

2.14 如何在MySQL中创建全文索引，并说明全文索引的使用场景？

正例：

CREATE TABLE articles (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    PRIMARY KEY (id),
    FULLTEXT INDEX (title, content) -- 创建联合全文索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

当已经建好表结构，使用 ALTER TABLE 创建：

ALTER TABLE articles
ADD FULLTEXT INDEX ft_index (title, content);

全文索引一般用于内容管理平台（CMS），问答社区等检索场景，然而，全文索引也有一些限制，比如它只能用于MyISAM或InnoDB存储引擎（在MySQL 5.6及以上版本中），并且全文索引的列不能是NULL值。

实际应用中其实很少会使用到，现在多数使用 ElasticSearch 来搭建全文搜索引擎。

2.15 当表中的数据量非常大时，如何有效地维护和管理索引，以确保查询性能？

索引主要是为了优化查询性能而设计的。如果一个字段的查询频率远低于更新频率，那么为该字段创建索引可能不会带来预期的性能提升，反而可能因为维护索引而降低整体性能。

性能开销：索引的维护需要额外的计算和存储资源。当对一个字段进行大量的更新操作时，数据库系统不仅需要更新数据本身，还需要更新所有相关的索引。这会导致性能开销增加，尤其是在高并发的写操作环境中。
存储空间：索引本身占用存储空间。对于经常更新的字段，如果创建了索引，那么每次数据更新都可能导致索引的页面分裂，进而需要更多的存储空间来维护索引结构。
索引失效：频繁的更新操作可能导致索引的页变得碎片化，从而降低索引的效率。索引页的碎片化意味着索引中的数据不再按照顺序存储，这会增加数据库在执行查询操作时的磁盘I/O次数，因为数据库可能需要读取多个不连续的页面来满足查询条件。
更新锁竞争：在高并发的更新操作中，索引可能会成为锁竞争的瓶颈。当多个事务尝试更新同一索引页时，可能会发生锁等待，这会降低并发性能。

2.16 假设你有一个包含大量数据的表，并且经常需要根据某个字段进行排序。你应如何优化这个字段的索引以提高排序操作的性能？

当你尝试为一个已经存在大量数据的表添加索引时，可能会遇到什么问题？如何解决这些问题？

首先：如果是亿级大表，在建表时就要添加必要的索引，否则存入过多数据可能会出现加不成功的现象。

垂直拆分：按照业务维度拆分。
水平拆分：按照不同的行进行分片，分散到不同的物理表中。
创建索引
分区：根据实际情况进行数据分区，但是要注意分区后可能影响写入性能。
优化查询语句
分布式数据库

2.17 如何优化索引

当你遇到查询性能问题时，如何分析和优化索引的使用？开放性问题。

评估索引的必要性，不是所有字段都要走索引。
选择正确的索引类型，例如，B-tree索引适合范围查询和排序操作，Hash索引适合等值查询，Bitmap索引适合低基数（不同值的数量较少）的列。
优化索引的列顺序：在创建多列索引时，考虑列的访问模式和查询类型。通常，将最常用作查询条件的列放在索引的前面，因为数据库可以更有效地使用这些列来过滤数据。
使用覆盖索引：如果查询只访问索引中包含的列，使用覆盖索引可以避免访问数据行本身，从而提高查询性能。
分析数据分布：对于列的值分布进行分析，避免在高度重复的列上创建索引，因为这样的索引可能不会带来显著的性能提升。
避免过度索引：过多的索引会增加数据库的维护成本，尤其是在数据插入、更新和删除时。确保每个索引都有其明确的用途，并定期审查和清理不再需要的索引。

2.18 请谈谈你对 MySQL 索引碎片化的理解，并说明如何检测和修复索引碎片化

**如何检测索引碎片化？**两个方法

使用SHOW TABLE STATUS命令：通过执行 SHOW TABLE STATUS LIKE 'table_name'; 可以获取表的状态信息，其中包括 Data_free 字段，它表示表中未使用的空间百分比。如果这个值相对较高，可能表明表存在碎片化问题。
使用 INFORMATION_SCHEMA.TABLES 表：查询 INFORMATION_SCHEMA.TABLES 可以获取表的碎片化信息。例如：

SELECT table_name, table_schema, Data_free / Data_length * 100 AS碎片化百分比
FROM information_schema.TABLES
WHERE table_schema = 'your_database_name' AND Data_free > 0;

如何修复索引碎片化？

优化表的存储引擎：

对于 MyISAM 存储引擎，可以使用 OPTIMIZE TABLE 命令来重新组织表的数据，减少碎片化。对于 InnoDB 存储引擎，这个命令也会尝试优化表，但效果可能不如 MyISAM 明显。

OPTIMIZE TABLE table_name;

重建索引：

对于 InnoDB 存储引擎，可以通过 ALTER TABLE 命令来重建表的索引，这通常比 OPTIMIZE TABLE 更有效。

ALTER TABLE table_name ENGINE=InnoDB;

定期维护：

定期执行 OPTIMIZE TABLE 或 ALTER TABLE 命令可以帮助维持索引的健康状况，减少碎片化。

需要注意的是，优化表的操作可能会消耗大量的系统资源，并且可能需要较长的时间来完成，特别是对于大型表。因此，在执行这些操作之前，最好在测试环境中进行评估，并在业务低峰时段进行。此外，确保在执行优化操作之前备份数据，以防万一出现问题。

参考黑马程序员mysql相关视频、MySQL索引18连问，谁能顶住