mysql索引

tim-qtp...大约 10 分钟MySQL数据库索引

1、MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？

  • InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。
  • MyISAM 存储引擎：引文件和数据文件是分离的，叶子节点保存数据的物理地址。

其他区别呢？

特性MyISAMInnoDB
事务支持❌ 不支持事务✅ 支持 ACID 事务
外键支持❌ 不支持外键✅ 支持外键
锁机制表级锁（并发性能低）行级锁（并发性能高）
查询性能读性能较快，适合大量 SELECT 查询读写性能均衡，适合 INSERT/UPDATE/DELETE
崩溃恢复❌ 容易数据丢失，需要手动修复✅ 崩溃后自动恢复
存储方式索引和数据分开存储，查询速度快索引和数据存储在一起，支持聚簇索引
全文索引✅ 支持（MySQL 5.6 之前）✅ MySQL 5.6+ 开始支持
适用场景查询多，写少（日志、数据分析）读写混合、高并发（金融、交易系统），数据安全要求高

2、一个表中如果没有创建索引，那么还会创建B+树吗？

仍然会创建 B+ 树，因为 InnoDB 默认使用 主键作为聚簇索引，如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键，上面两个都没有的情况下，InnoDB 也会自动创建一个隐藏的 row_id 作为主键，并建立 B+ 树索引。

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

①、聚簇索引：聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说，聚簇索引的叶子节点中，包含了一个完整的记录行。

聚簇索引
聚簇索引

②、非聚簇索引：它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。

聚簇索引，以 age 为索引
聚簇索引，以 age 为索引

InnoDB 存储引擎的主键使用的是聚簇索引，MyISAM 存储引擎不管是主键索引，还是二级索引使用的都是非聚簇索引。

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引)，它也是利用了B+树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

  • 非聚簇索引可以优化 ORDER BY 和 GROUP BY 操作
  • 覆盖索引（Covering Index）：避免回表，提高查询速度
  • 适用于非主键列的查询， 表有多个查询场景（如经常按 Name、Score、Age 过滤数据）

1. 举个实际的例子

① 创建一个 Student 表

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,  -- 聚簇索引
    Name VARCHAR(50),
    Age INT,
    Score INT
);

默认情况下，StudentID 作为 PRIMARY KEY，它就是聚簇索引。

② 创建非聚簇索引

我们希望加速按 Score 查询学生信息，因此创建一个 非聚簇索引：

CREATE NONCLUSTERED INDEX idx_Student_Score ON Student(Score);

2. 回表查询的情况

假设我们执行以下查询：

SELECT Name, Age FROM Student WHERE Score > 80;

查询过程：

  1. 先在 idx_Student_Score 非聚簇索引中查找 Score > 80 的记录，得到 StudentID（或指向数据行的 Row Locator）。
  2. 再回到 Student 表（聚簇索引）中，通过 StudentID 找到 Name 和 Age。

问题：

  • idx_Student_Score 只存储了 Score，但查询需要 Name 和 Age，所以必须回表查询完整数据。
  • 回表操作增加了 I/O 和查询时间，影响性能。

3. 使用覆盖索引优化查询

① 创建覆盖索引

为了避免回表，我们可以创建一个包含 Name 和 Age 的非聚簇索引：

CREATE NONCLUSTERED INDEX idx_Student_Score ON Student(Score) INCLUDE(Name, Age);

覆盖索引的作用：

  • 索引不仅包含 Score，还包含 Name 和 Age，因此查询时可以直接从索引中获取数据，不需要回表。

4. 使用覆盖索引后的查询

再次执行：

SELECT Name, Age FROM Student WHERE Score > 80;

查询过程（优化后）：

  1. 直接在 idx_Student_Score 索引中查找 Score > 80 的记录。
  2. 索引已经包含 Name 和 Age，所以数据库不需要回表，直接从索引中返回结果。

4、有哪些索引类型？

  • B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。
  • 哈希索引: 适合等值查询，检索效率高，一次到位。
  • 全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。
  • R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

5、B树和B+树的区别？

  • 数据存储位置
  • B树搜索路径可能更短就能查到目标数据
  • B+树支持范围查询
  • 树的高度更低，意味着磁盘 I/O 更少
  • B+树插入或删除只影响叶子节点，内部索引节点不存数据，调整较简单；B树插入或删除可能影响所有节点，调整较复杂。

所以B树适用于纯内存操作的情况，比如说MongoDB，而B+树适用于数据库和文件系统，对磁盘 I/O 友好，范围查询高效（如 MySQL 索引结构）。

6、为什么不用二叉树？或者平衡二叉树？

二叉树坏的情况下可能退化成链表，失去了BST的优势（不能像二分查找那样减少搜索范围）。

平衡二叉树虽然可以避免 BST 退化成链表，保持平衡；但是也是二叉的结构，一个结点存储一个值，树依然很高，不利于IO操作。

7、一次B+树索引树查找过程

假设有以下表结构，并且初始化了这几条数据‘

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into employee values(100,'小伦',43,'2021-01-20','0');
insert into employee values(200,'俊杰',48,'2021-01-21','0');
insert into employee values(300,'紫琪',36,'2020-01-21','1');
insert into employee values(400,'立红',32,'2020-01-21','0');
insert into employee values(500,'易迅',37,'2020-01-21','1');
insert into employee values(600,'小军',49,'2021-01-21','0');
insert into employee values(700,'小燕',28,'2021-01-21','1');

执行这条查询SQL，需要执行几次的树搜索操作？可以画下对应的索引树结构图~

select * from Temployee where age=32;

idx_age普通索引的索引结构图：

id主键索引，聚族索引结构图：

img
img

这条 SQL 查询语句执行大概流程是这样的：

  • 搜索idx_age 索引树，将磁盘块1加载到内存，由于32<43,搜索左路分支，到磁盘寻址磁盘块2。
  • 将磁盘块2加载到内存中，由于32<36,搜索左路分支，到磁盘寻址磁盘块4。
  • 将磁盘块4加载到内存中，在内存继续遍历，找到age=32的记录，取得id = 400.
  • 拿到id=400后，回到id主键索引树。
  • 搜索id主键索引树，将磁盘块1加载到内存，因为300<400<500,所以在选择中间分支，到磁盘寻址磁盘块3。
  • 虽然在磁盘块3，找到了id=400，但是它不是叶子节点，所以会继续往下找。到磁盘寻址磁盘块8。
  • 将磁盘块8加载内存，在内存遍历，找到id=400的记录，拿到R4这一行的数据，好的，大功告成。
image-20250204114048506

这个图也挺好。

8、什么是回表？如何减少回表？

当查询的数据在索引树中，找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表。

就像上面的SQL

select * from Temployee where age=32;

需要查询所有列的数据，idx_age普通索引不能满足，需要拿到主键id的值后，再回到id主键索引查找获取，这个过程就是回表。

8、什么是覆盖索引？

如果查询SQL的select * 修改为 select id, age的话，其实是不需要回表的。因为id和age的值，都在idx_age索引树的叶子节点上。

覆盖索引是select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。

9、聊聊索引的最左前缀原则

在联合索引，也就是在多个列上创建的索引 的基础上遵循在查找时会优先使用下面的第一个a作为匹配依据的规则。

比如你建立一个组合索引（a,b,c），其实可以相当于建了（a），（a,b）,(a,b,c)三个索引，大大提高了索引复用能力。

SELECT * FROM users WHERE name = 'Alice';  -- ✅ 可以用索引，因为 name 是最左列
SELECT * FROM users WHERE name = 'Alice' AND age = 25;  -- ✅ 可以用索引
SELECT * FROM users ORDER BY name, age;  -- ✅ 排序字段索引
SELECT * FROM users WHERE age = 25;  -- ❌ 不能用 idx_name_age，因为 age 不是最左列

这个SQL: select * from employee where name like '小%' order by age desc; 也是命中索引的。

10、什么是索引下推

给你这个SQL：

select * from employee where name like '小%' and age=28 and sex='0';

其中，name和age为联合索引（idx_name_age）。

如果是Mysql5.6之前，在idx_name_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去对比年龄和性别等其他字段。如图：

有些可能会觉得奇怪，idx_name_age（name,age)不是联合索引嘛？为什么选出包含“小”字后，不再顺便看下年龄age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

因此，MySQL5.6版本之后，选出包含“小”字后，顺表过滤age=28

11、Hash 索引和 B+树区别是什么？

  • B+树可以进行范围查询，Hash 索引不能。
  • B+树支持 order by 排序，Hash 索引不支持。
  • Hash 索引在等值查询上比 B+树效率更高。（但是索引列的重复值很多的话，Hash冲突，效率降低）。
  • B+树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

12、哪些操作会导致索引失效？

索引失效是指查询优化器没有使用已创建的索引，而是选择了全表扫描（FULL TABLE SCAN）或其他低效的查询方式。

  • like错误使用，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。相当于全表扫描

  • 索引中使用了运算 例如这个SQL select*from user where id+3=8。这样会导致全表扫描计算id的值再进行比较，使得索引失效。

  • 索引上使用了函数也会失效 例如：select*from user where LOWER(name) like'cong%';。这样也会导致索引失效，索引参与了函数处理，会导致去全表扫描。

  • 使用了联合索引却不符合最左前缀

    SELECT * FROM users WHERE age = 25 AND name = 'Alice';

    SELECT * FROM users WHERE name = 'Alice'; //失效

  • 隐式类型转换

    select * from t_user where phone = 1300000001; //失效，phone 字符串转化成数字 

    select * from t_user where id = "1"; //生效，"1"自动转换成数字1

  • WHERE 子句中的 OR

在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

举个例子，比如下面的查询语句，id 是主键，age 是普通列，从执行计划的结果看，是走了全表扫描。

select * from t_user where id = 1 or age = 18;
默认页脚
Copyright © 2025 tim-qtp