目录

?? 一，什么是索引??二，索引的作用??三，索引优缺点及场景 1，优点2，缺点3，使用场景4，注意事项 ??四，索引的使用 1，索引分类2，查看索引3，创建索引（重点）4，索引和约束的区别（容易混淆） ??五，索引实现原理-索引失效 2，实现原理 （1）二叉树阶段（2）B树阶段（3）B+树阶段 2，索引失效场景（面试常考） （1)联合索引不满足最左匹配原则（前缀匹配原则）（2）使用错误的模糊查询（3）列使用运算操作(±*/)（4）列使用函数（5）列存在类型转换（6）使用is not null ?? 六，常见面试题 1.聚簇索引和二级索引的区别2，MySQL InnoDB 为什么设计B+树，而不是B树，Hash，二叉树，红黑树？3，什么是MySQL的最左前缀原则4， 事务ACID的特性5，索引失效场景有哪些

?? 一，什么是索引

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

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 上有索引

create table T(id int primary key,k int not null,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

（1） 如果建立（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，那么索引就会失效

如果觉得《数据库MySQL-索引（含常见面试题）》对你有帮助，请点赞、收藏，并留下你的观点哦！