MySQL的索引总结2

编程入门行业动态更新时间:2024-10-22 14:03:29

MySQL的<a href=https://www.elefans.com/category/jswz/34/1771159.html style= 索引总结2"/>

MySQL的索引总结2

硬盘存储结构

MyISAM

MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指定文件类型

.frm[frame]文件存储表的定义
.MYD(MYData)为数据文件扩展名
.MYI(MYIndex)为索引文件

InnoDB

InnoDB存储引擎存储数据为2个文件，没有专门保存数据的文件

frm文件: 表的定义文件
Idb文件: 数据和索引存储的文件。数据以主键进行聚集存储，吧真正的数据保存在叶子节点

索引

聚集索引(InnoDB的主键索引)

将数据和索引放在一起存储，索引结构的叶子节点保存了行数据，每个表只有一个主键索引(B+树结构)，叶子节点同时保存了主键的值也保存了数据记录，其它节点只存储主键的值。

聚簇索引默认是主键
如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替
如果没有这样的索引，InnoDB会在内部生成一个名为GEN_CLUST_INDEX的隐式聚簇索引

非聚集索引(MyISAM/非主键索引/辅助索引)

每个表可以有多个 b+树结构，叶⼦节点保存了索引字段的值以及主键的值，其他节点只存储索引指向的值。将数据与索引分开存放，表数据存储顺序与索引顺序无关。

面试问题1：
在InnnoDB中，索引B+ Tree的叶子节点存储了整行数据，是主键索引–聚簇索引。
而索引B+Tree的叶子节点存储了主键的值，是非主键索引–非聚簇索引。
面试问题2：
一般情况下，主键索引树的叶子节点直接就是要查询的整行数据。非主键索引树的节点是主键的值，查到主键的值以后还需要一次回表操作。
特殊情况–覆盖索引

联合索引

联合索引是一个索引，单列索引是特殊的联合索引
规则：
联合索引中如果不是按照索引最左列开始查找，无法使用索引；
联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
联合索引中如果查询中某个列的范围查询，则其右边的所有列都无法使用索引；

选取原则

经常使用的列优先【最左匹配原则】
离散性高的列优先【离散性原则】

计算公式：count(列中不同的列) / count(列)
离散性越高，选择性就越好[不需要做过多的IO]，离散性差的索引会被优化器pass掉。

宽度小的列优先【最少空间原则】

实例：
系统中使用较多的查询语句是：
select * from users where name = ?;
select * from users where name = ? and tel = ?;
解决方案：
create index idx_name on users(name); X
create index idx_name_tel on users(name, tel);
第二个包含了第一，存在冗余索引

覆盖索引

如果查询的列，通过索引项的信息可以直接返回，则该索引称之为查询SQL的覆盖索引。

示例：teacher
索引：PK(id):主键索引 key(name, tel):联合索引 unique(teacherNo):唯一索引
下面那些SQL使用了覆盖索引：

select teacherNo from teacher where teacherNo = ?; 是，基于B+树去查询，叶子节点就是teacherNo【辅助索引】,不需要回表操作
select id, teacherNo from teacher where teacherNo = ?;是，由于id【主键】和teacherNo【辅助索引】都是在teacherNo这个B+树的叶子节点上
select id,tel from teacher where teacherNo = ?; 不是，由于用的索引是teacherNo，叶子节点为(id, teacherNo)，tel需要回表查询
select tel from teacher where name = ?;是，联合索引包括(id, name, tel)的信息，所以不需要回表

最左前缀匹配

对索引中关键字进行计算(对比)，一定是从左往右进行的，且不能跳过。

根据业务需求，where子句中使用最频繁的一列放到最左边，MySQL索引查询时会遵循最左前缀匹配原则(最左优先)。
eg:
当我们创建一个联合索引时，如(key1, key2, key3)，相当于创建了3个索引，分别是：(key1)、(key1, key2)和(key1, key2, key3)，这就是最左匹配原则。

面试问题1：什么时候创建了索引，但是执行的时候并没有通过索引？
查询优化器：一条SQL语句的查询，可以有不同的执行方案，最终选择的方案是选择成本最低的方案。
优化过程：
1.根据搜索条件，找出所有可能使用的索引；
2.计算全表扫描的代价；
3.计算使用不同执行查询的代价；
4.对比各种执行方案，找出成本最低的那个。