数据库MySQL-索引（含常见面试题）

目录

🍓 一，什么是索引

索引是一种特殊的文件，它包含了数据表中所有记录的引用指针，我们可以对列或多列创建索引并且指定对应的类型，每种类型有对应的数据结构去实现

MySQL 数据库有很多引擎，MySQL 和引擎的关系就像汽⻋⼚商和汽⻋⻋型的关系，⼀个⼚商
（MySQL）可以有多种⻋型（多个引擎）。
MySQL 最著名的引擎有两个：InnoDB 和 MyISAM，MyISAM 是 MySQL 5.5 之前默认的引擎，
MySQL 5.5 之后的默认引擎是 InnoDB，它们最⼤的区别是 MyISAM 不⽀持事务，所以有很⼤的数据不
完整性⻛险（也就是数据的业务执⾏了⼀半），⽽ InnDB ⽀持事务，但 MyISAM 的性能⽐ InnoDB ⾼，
然⽽对于使⽤者来说牺牲⼀些性能换取更⾼的稳定性是⾮常明智的选择

🌸二，索引的作用

数据库中数据存储在磁盘，磁盘的顺序查询速度是很慢的，所以的顺序查找指的是读取磁盘中的数据⼀条⼀条的进⾏查找。
索引就是为了避免顺序查询，提供查询速度的。
查询数据存储的目录：show variables like ‘%dir%’;

索引的作用

在数据库中表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。索引所起的作用类似书籍目录，可用于快速定位、检索数据。
索引对于提高数据库的性能有很大的帮助

🍎三，索引优缺点及场景

1，优点

我们为什么要创建索引呢，因为创建索引可以提高系统的性能，具体体现在以下几个方面：

（1）通过创建唯一索引时，可以确保每一行数据的唯一性
（2）当创建索引后，可以大大提高检索的速度，同时在分组和对数据排序后使用索引，可以减少排序和分组的时间
（3）在表和表之间连接时，在保证数据的完整性有很大作用， 另外通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

2，缺点

到现在所讨论的都是索引的优点，其实索引也有缺点，比如

(1)索引创建后，它的维护成本很高，因为它所使用的是B+树，每次在添加删除时，都会整理树结构，将这些操作写入到索引文件中
(2)索引要占用磁盘空间。通常情况下，这个问题不是很突出。但是，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件
(3)创建过多的索引，对优化器也会造成很多负担

3，使用场景

当了解完索引的优缺点后，不得不去考虑索引的使用场景，要注意下面两点：

(1)当数据库中的数据量比较大时，可以创建
(2)创建索引去查询经常用到的列

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

4，注意事项

(1)索引会占用额外的磁盘空间，或磁盘空间不足时，不考虑创建索引,如果是不常使用的列，或经常做插入、修改操作的列，不适合加索引
(2)不要在线上环境去创建索引，也就是再有用户在使用此系统时，创建索引会锁表，线上数量大会导致其他所有接口不可用，导致系统性崩溃

🍇四，索引的使用

1，索引分类

(1)主键索引：(聚簇/集索引）⼀种特殊的唯⼀索引，不允许有空值，⼀般是在建表的时候同时创建主键
索引（通过 primary key）
(2)非主键索引：(二级索引）除了主键索引之外的索引
(3)唯一索引：不能重复的索引，也就是唯一的
(4)普通索引：可以重复也可以为null的索引，外键索引也是
(5)联合索引：多个字段的普通索引组成的索引

2，查看索引

查看索引语法：

show index from 表名;
案例：查询学生表是索引：
show index from student;

3，创建索引（重点）

在创建主键 primary key/unique/foreign key 时会自动创建索引，而对于非主键、非唯⼀约束、非外键的字段，可以手动创建索引
例如：有⼀个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引

createtable T(
id intprimarykey,
k intnotnull,
name varchar(16),index(k));

(1)添加主键索引

语法
alter table table_name add primary key (id);

(2)添加唯一索引

语法
create unique index 索引名 on 表名(字段名);

(3)添加普通索引

语法
create index 索引名 on 表名(字段名[,字段名2…]);

(4)删除索引

语法
drop index 索引名 on 表名;

4，索引和约束的区别（容易混淆）

(1)定义和作用不同：约束是保证数据的可靠性的；索引是加速查询的，不是⼀回事
(2)可以相互创建：当创建了主键、外键、唯⼀约束也就创建了相应的索引；当创建了唯⼀索引，也就
创建了唯⼀约束

🍌五，索引实现原理-索引失效

2，实现原理

（1）二叉树阶段

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高,多叉树是在二分查找树的基础上，增加单个节点的数据存储数量，同时增加了树的子节点数，一次计算可以把查找范围缩小更多

（2）B树阶段

B-tree 已经大大改进了树家族的性能，它把多个数据集中存储在一个节点中，本身就可能减少了 I/O 次数或者寻道次数。
但是仍然有一个致命的缺陷，那就是它的索引数据与业务绑定在一块，而业务数据的大小很有可能远远超过了索引数据，这会大大减小一次 I/O 有用数据的获取，间接的增加 I/O 次数去获取有用的索引数据。

（3）B+树阶段

B+ 树中，非叶子节点只保存索引数据，叶子节点保存索引数据与业务数据。这样即保证了叶子节点的简约干净，数据量大大减小，又保证了最终能查到对应的业务数。既提高了单次 I/O 数据的有效性，又减少了 I/O 次数，还实现了业务。

2，索引失效场景（面试常考）

（1)联合索引不满足最左匹配原则（前缀匹配原则）

在联合索引查询时，查询语句必须要从联合索引最左侧开始查询，联合查询才能生效，否则失效
比如联合查询为A+B+C
当B+C不符合最左匹配原则，索引就会失效

（2）使用错误的模糊查询

当使用了错误的模糊查询时

like的模糊查询方式
①前面确定 -> 字段名 like ‘张%’ 才可以触发有效索引
②中间确定 -> 字段名 like ‘%张’
③后面确定 -> 字段名 like ‘张%’

（3）列使用运算操作(±*/)

（4）列使用函数

（5）列存在类型转换

（6）使用is not null

🍒 六，常见面试题

1.聚簇索引和二级索引的区别

⭐聚簇索引存储的是行数据，也就是对应的地址，只要查到相对应的id，就可以查到主键对应的行数据，但二级索引中非叶子节点存储的是二级索引的值，叶子节点存储的是主键id

⭐1， 执行效率：聚簇索引查询速度更快，因为聚簇索引存储的是数据，而非聚簇索引存储的是主键 ID，需要进⾏回表查询，性能低
⭐2，数量上：聚簇索引⼀个表只能有⼀个，⽽⾮聚簇索引可以有多个

2，MySQL InnoDB 为什么设计B+树，而不是B树，Hash，二叉树，红黑树？

（1）哈希索引能够以 O(1) 的速度处理单个数据行的增删改查，但是面对范围查询或者排序时就会导致全表扫描的结果。
（2）B树可以在非叶结点中存储数据，由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量的随机 I/O，造成性能下降。

（3）B+树所有的数据行都存储在叶节点中，而这些叶节点可以通过指针依次按顺序连接，当我们在如下所示的 B+ 树遍历数据时可以直接在多个子节点之间进行跳转，这样能够节省大量的磁盘 I/O 时间。

（4）二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。红黑树：树的高度随着数据量增加而增加，IO代价高。

3，什么是MySQL的最左前缀原则

🍒（1）最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4
🍒（2） 如果建立（a，b，c，d）顺序的索引，d是用不到索引的，如果建立（a，b，d，c）的索引则都可以用到，a，b，d的顺序可以任意调整。
🍒（3）=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立（a，b，c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

4， 事务ACID的特性

（1) 原子性：又称不可分割性，事务里所有的操作要么全部成功，要么全部失败原子性是事务最重要的特性，全部执行失败并不是不执行，而是通过逆操作 rollback（回滚）数据。
实现原理：回滚是逆 SQL 操作
（2）持久性：当时事务执行后，MySQL中的数据是持久的，不会因为事务的进行而改变
（3）隔离性：当有很多事务并发进行时，事务之间是互相不影响的，又称为独立性
（4）一致性：事务在执行前后都会保持一种合法的状态，从一种状态到另一种状态

5，索引失效场景有哪些

（1）当联合索引不满足最左匹配原则，相当于创建多列索引，没有最左优先，那么联合查询也就失效，
（2）在查询时，使用错误的模糊查询
（3）当列使用运算操作和函数时，索引就失效了
（4）列使用了类型转换，也会导致索引失效
（5）使用了is not null，那么索引就会失效