6-索引

PS：MySQL的存储引擎

MySQL 有很多数据引擎，也叫存储引擎，所谓的存储引擎是指用于存储、检索、更新和删除数据的服务。

MySQL 和引擎的关系就像汽⻋⼚商和汽⻋⻋型的关系，⼀个⼚商（MySQL）可以有多种⻋型（引擎）。

在 MySQL 中可以使用“show engines”来查询数据库的所有存储引擎，如下图所示：

在上述列表中，我们最常用的存储引擎有以下 3 种：

InnoDB
MyISAM
MEMORY

①InnoDB

InnoDB 是 MySQL 5.5 之后默认的存储引擎，它支持事务、支持外键、支持崩溃修复和自增列。

如果对业务的完整性要求较高，比如张三给李四转账，需要减张三的钱，同时给李四加钱，这时候只能全部执行成功或全部执行失败，此时可以通过 InnoDB 来控制事务的提交和回滚，从而保证业务的完整性。

优缺点分析

优点：支持事务、支持外键、支持崩溃修复和自增列。
缺点：读写效率较差、占用的数据空间较大。
适用场景：对业务的完整性要求较高。

②MyISAM

MyISAM 是 MySQL 5.5 之前默认的数据库引擎，读取效率较高，占用数据空间较少，但不支持事务、不支持行级锁、不支持外键等特性。

因为不支持行级锁，因此在添加和修改操作时，会执行锁表操作，所以它的写入效率较低。

优缺点分析

优点：MyISAM 引擎保存了单独的索引文件 .myi，且它的索引是直接定位到 OFFSET 的，而 InnoDB 没有单独的物理索引存储文件，且 InnoDB 索引寻址是先定位到块数据，再定位到行数据，所以 MyISAM 的查询效率是比 InnoDB 的查询效率要高。
缺点：但它不支持事务、不支持外键。
适用场景：读多写少，且对完整性要求不高的业务场景。

③MEMORY

内存型数据库引擎，所有的数据都存储在内存中，因此它的读写效率很高，但 MySQL 服务重启之后数据会丢失。

它同样不支持事务、不支持外键。

MEMORY 支持 Hash 索引或 B 树索引，其中 Hash 索引是基于 key 查询的，因此查询效率特别高，但如果是基于范围查询的效率就比较低了。

而前面两种存储引擎是基于 B+ 树的数据结构实现了。

优缺点分析

优点：MEMORY 读写性能很高。
缺点：但 MySQL 服务重启之后数据会丢失，它不支持事务和外键。
适用场景：读写效率要求高，但对数据丢失不敏感的业务场景。

④查看存储引擎

存储引擎的设置粒度是表级别的，也就是每张表可以设置不同的存储引擎。

可以使用以下命令来查询某张表的存储引擎：
show create table t;
可以使用以下命令查询当前数据库的存储引擎：

⑤设置存储引擎

在创建一张表的时候设置存储引擎：

修改一张已经存在表的存储引擎：

PS：MyISAM VS InnoDB

MySQL 最著名的引擎有两个：MyISAM和InnoDB。

区别具体如下：

事务支持不同：MyISAM 是非事务型存储引擎，它不支持事务操作；而 InnoDB 是事务型存储引擎，所以它是支持事务操作的，它可以保证数据的完整性和一致性。
聚集索引的支持不同：MyISAM 不支持聚集索引，而 InnoDB 支持聚集索引。
数据缓存支持不同：MyISAM 不支持数据缓存，而 InnoDB 支持数据缓存。
外键支持不同：MyISAM 不支持外键，而 InnoDB 支持外键。
锁粒度不同：InnoDB 支持行级锁定，可以对数据表的某行进行锁定，而不会锁定整个表，这样可以提高并发读写的能力；而 MyISAM 只支持表级锁定，只能对整个表进行锁定，这样容易造成大量的读写冲突和性能瓶颈。
存储限制不同：MyISAM 支持 256TB 的数据存储；而 InnoDB 只支持 64TB 的数据存储。
MVCC 支持不同：MVCC （多版本并发控制）是一种数据库管理系统中的并发控制方法，它允许多个事务同时读取数据库中的同一数据，而不会相互干扰。MVCC通过为每个事务创建一个独立的“视图”来实现这一点，该视图显示了数据库在该事务开始时的状态。当事务读取数据时，它只能看到该视图中的数据，而不是实际的数据库状态。这意味着即使其他事务正在修改相同的数据，该事务也不会受到影响，因为它只能看到它开始时的数据状态。MyISAM 是不支持 MVCC 的；而 InnoDB 支持。
版本号：MySQL 5.5 之后的默认引擎是 InnoDB；MySQL 5.5 之前的默认引擎是 MyISAM。
稳定性：InnDB ⽀持事务，保证数据的稳定性；MyISAM 不⽀持事务，所以有很⼤的数据不完整性⻛险（也就是数据的业务执⾏了⼀半）。MyISAM 的稳定性不如 InnoDB 好。
性能：MyISAM 的性能⽐ InnoDB ⾼。然⽽对于使⽤者来说牺牲⼀些性能换取更⾼的稳定性是⾮常明智的选择。

2.为什么需要索引？

索引可以避免顺序查询，直接将查询的范围定位出来。
索引可以将数据库中的关键索引信息存储到内存中，内存的操作速度远比磁盘快。
so索引能快速找到数据，提高MySQL查询性能。所以每个表中都会有索引。（索引也不要建的太多）

数据库中数据存储在磁盘，磁盘的顺序查询速度是很慢的。顺序查询指的是读取磁盘中的数据⼀条⼀条地进⾏查找。

PS：存储数据模组

磁盘：容量大、价格低廉、操作速度慢、可以持久化【重启后数据还存在】（现在的磁盘已经不是机械磁盘，是SSD固态磁盘）
内存：容量小、价格比较贵、操作速度快、不可持久化。
CPU缓存：【L1/L2/L3：即一级缓存（速度最快）、二级缓存（速度次之）、三级缓存（速度最慢）】容量更小、价格更贵、操作速度极快、不可持久化。

PS：查询数据存储的目录：

3.索引的作用

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

PS：索引 VS 书的目录

从宏观的角度来看，可以认为索引就是书的目录。
从微观的角度来看，索引并不等于书的目录。因为一本书的目录只有一个，而一张表可以有多个索引。

4.索引的优缺点和使用场景

4.1.索引优点

提高查询效率：索引可以加速数据的检索速度，对于大量数据的表而言，使用索引可以大幅提高查询效率。
避免全表扫描：当没有索引时，数据库会进行全表扫描，而索引可以帮助避免全表扫描，加速查询。
增强数据的唯一性和完整性：可以通过在列上创建唯一索引和主键索引来确保表中的数据唯一性和完整性。
减少主从复制从库的延迟时间。

4.2.索引缺点

占用额外存储空间（磁盘和内存）：每个索引都对应⼀个 B+ 树，都会占用额外的存储空间，因此在设计索引时需要权衡存储空间和查询效率之间的平衡。
降低写操作效率：索引的维护需要额外的写操作，因此在大量写操作的情况下可能会降低写操作的效率。
可能出现索引失效：索引并不是万能的，有些情况下使用索引可能会导致查询效率降低甚至出现索引失效的情况。例如，当对于一个非常小的表或者一个稠密的索引列进行查询时，使用索引可能并不会提高查询效率。
索引维护成本很⾼：因为它使⽤的是 B+ 树，每次新增、删除数据都需要整理树结构。随着表数据的不断变化，索引也需要不断维护以保持其效率。因此，在使用索引时需要注意索引的维护成本。
索引过多会对 MySQL 的优化器造成⼀定的负担。（在查询时MySQL会自动帮我们选择使用哪个索引查询效率更高）

4.3.适合建索引的使用场景

对数据库表的某列或某⼏列创建索引要考虑的因素：

数据量是否足够大，查询速度是否比较慢。
创建索引的列是否是经常使用的查询条件。

满⾜以上条件可对表中的这些字段创建索引，以提⾼查询效率。

频繁用于条件查询的列：如果一个列经常用于 WHERE、JOIN、ORDER BY 或 GROUP BY 子句中，那么可以考虑在该列上创建索引。
唯一性约束：对于需要保证唯一性的列，应该在该列上创建唯一索引或主键索引。
经常用于排序的列：对于需要经常进行排序的列，如 ORDER BY 子句中的列，应该在该列上创建索引。
经常用于聚合函数的列：对于经常用于聚合函数（如 COUNT、AVG、SUM、MIN、MAX）的列，应该在该列上创建索引，可以加速聚合查询。

4.4.不适合建索引的使用场景

对于小表：如果表的数据量比较小，那么建立索引并不能带来很大的性能提升，反而会增加查询时间和占用存储空间。
对于多写少读的表：索引会增加写操作的时间，需要重新整理索引，速度很慢，比如日志表。并占用更多的存储空间，因此对于频繁进行 INSERT、UPDATE 或 DELETE 操作的表，不宜过多地建立索引。
MySQL服务器本身安装的电脑上磁盘空间或内存空间不足的情况下，不要用索引。索引会占⽤额外的磁盘空间。

4.5.注意事项

如果对已经存在很多数据的表新增索引时，不要在线上生产环境随意创建索引，创建索引会锁表，线上数量⼤，会导致其他所有接⼝不可⽤，从⽽造成重⼤事故。（其他业务场景只能排队等待，造成雪崩。）可以在半夜没人用时，创建索引。
创建索引需要根据具体的情况进行权衡和取舍，只有在真正需要提高查询效率时才应该创建索引，否则会浪费存储空间和增加写操作的时间。

5.索引使用

注：一个表中的索引名不能重复。

5.1.索引分类

索引的分类有以下⼏种：

从功能逻辑上说，索引主要有4类：普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为2种：聚簇索引和非聚簇索引。
按照作用字段个数进行划分，分成：单列索引和联合索引。

主键索引（聚簇索引/聚集索引）：是一种特殊的索引类型，它是用于唯一标识每一行数据的索引，每个表只能有一个主键索引。不允许有空值，⼀般是在建表的时候同时创建主键索引（通过 primary key）。
非主键索引（非聚簇索引/非聚集索引/二级索引）：除主键索引之外的其他索引。
外键索引：使用外键，数据库会自动创建外键索引。
唯⼀索引：是用来保证列的唯一性的索引，一个表可以有多个唯一索引。
普通索引（非唯一索引）：它是最常见的一种索引类型，可以加速查询和排序操作。可以重复，也可以为 NULL 的索引。
全文索引：是一种用于全文搜索的索引类型，能够对文本数据进行快速的模糊搜索和关键字搜索。
联合索引（复合索引/多列索引）：它是包含多个列的索引类型，能够加速多列查询和排序操作。使⽤一个表中的多个字段联合组成的索引。
哈希索引：是基于哈希表实现的索引类型，能够对等值查询进行高效的处理，但不支持范围查询和排序，MySQL 中 Memory 引擎中支持哈希索引。

注意：

创建主键约束（PRIMARY KEY）、唯⼀约束（UNIQUE）、外键约束（FOREIGN KEY）时，会⾃动创建对应列的索引。
创建主键索引、唯一索引、外键索引时，会自动创建对应列的约束。

5.2.查看索引

show index from 表名;

5.3.创建索引

在创建 primary key/unique/foreign key 时会⾃动创建索引，⽽对于⾮主键约束、⾮唯⼀约束、⾮外键约束的字段，可以⼿动创建索引。

注意：每个索引都会对应⼀个 B+ 树。

假设，我们有⼀个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。这个表的建表语句是：

create table T (
   id int primary key,
   k int not null,
   name varchar(16),
   index (k)
) engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下：

5.3.1.创建普通索引（联合索引）

create index 索引名 on 表名(字段名 [,字段名...]);
-- 若创建联合索引，添加多个字段名即可

--->PS：索引命名规则

以 idx 为前缀，后跟"_"，再加字段名，若有多个字段名，之间以"_"相连。

如：idx_id_name

索引命名规则参考：

idx_字段名
idx_表名_字段名
idx_类型_字段名
idx_类型_表名_字段名

5.3.2.创建唯一索引

唯一索引在创建时，要确保原先的数据符合唯一约束，才能成功创建唯一索引。

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

5.3.3.添加主键索引

alter table 表名 add primary key(字段名);

5.4.删除索引

drop index 索引名 on 表名;

删除对应的索引，也会删除对应的约束。

6.索引实现原理

索引的实现经历了 3 个阶段的升级：

⼆叉树
B 树（B-树，不是B减树！多叉树）
B+ 树（MySQL5.7之后版本多使用）

①二叉树阶段

二叉树的根节点上半部分存储id，要查的数据和根节点数据比较，若比根节点小，去左子树继续查询，否则。去右子树继续查询。

找到id后，去二叉树下半部分（整行的数据）查到数据。

缺点：数据⼤之后树层级很⾼，维护和查询的性能不好。

②B树阶段

缺点：所有节点都保存了数据，加载需要很⻓的时间。

③B+树阶段

优化：非叶子节点不再存储数据，只有叶⼦节点才存储数据；且数据和索引是分离的；所谓的存储的数据，其实是指向数据的地址，数据量变的⾮常⼩。

7.索引失效的场景

索引效验——确认创建的索引是否生效：

而以上查询结果的列中，我们最主要观察 key 这一列，key 这一列表示实际使用的索引，如果为 NULL 则表示未使用索引（没有索引或索引无效），反之则使用了索引。

以上所有结果列说明如下：

id — 选择标识符，id 越大优先级越高，越先被执行；
select_type — 表示查询的类型；
table — 输出结果集的表；
partitions — 匹配的分区；
type — 表示表的连接类型；
possible_keys — 表示查询时，可能使用的索引；
key — 表示实际使用的索引；
key_len — 索引字段的长度；
ref— 列与索引的比较；
rows — 大概估算的行数；
filtered — 按表条件过滤的行百分比；
Extra — 执行情况的描述和说明。

其中最重要的就是 type 字段，type 值类型如下：

all — 扫描全表数据；
index — 遍历索引；
range — 索引范围查找；
index_subquery — 在子查询中使用 ref；
unique_subquery — 在子查询中使用 eq_ref；
ref_or_null — 对 null 进行索引的优化的 ref；
fulltext — 使用全文索引；
ref — 使用非唯一索引查找数据；
eq_ref — 在 join 查询中使用主键或唯一索引关联；
const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

创建测试表和数据

为了演示和测试哪种情况下会导致索引失效，先创建一个测试表和相应的数据：

-- 创建表 
drop table if exists student; 
create table student( 
    id int primary key auto_increment comment '主键', 
    sn varchar(32) comment '学号', 
    name varchar(250) comment '姓名', 
    age int comment '年龄', 
    sex bit comment '性别', 
    address varchar(250) comment '家庭地址', 
    key idx_address (address), 
    key idx_sn_name_age (sn,name,age) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

-- 添加测试数据 
insert into student(id,sn,name,age,sex,address) 
    values(1,'cn001','张三',18,1,'高老庄'), 
    (2,'cn002','李四',20,0,'花果山'), 
    (3,'cn003','王五',50,1,'水帘洞');

当前表中总共有 3 个索引，如下图所示：

PS：内容基于 MySQL 5.7 InnoDB 数据引擎下。

①非最左匹配

PS：联合索引有什么问题？

联合索引一定要注意字段创建的先后顺序，先后顺序会影响索引的执行。

联合索引想要触发索引一定要遵循最左匹配原则。

最左匹配原则https://www.cnblogs.com/-mrl/p/13230006.html

最左匹配原则，也叫前缀匹配原则，指的是以最左边的为起点字段查询可以使用联合索引，否则将不能使用联合索引。

此处的联合索引的字段顺序是 sn + name + age，假设它们的顺序是 A + B + C，以下联合索引的使用情况如下：

如果是以最左边开始匹配的字段都可以使用联合索引：A+B+C、A+C+B、A+B、A+C、A、B+A（MySQL后期版本的优化器会优化成A+B，省略B，直接用A）、C+A。
否则：B+C、C+B、B、C 不能使用联合索引。
只要有A都满足最左匹配原则。

联合索引的值是最左边列的值。会先匹配最左边列的索引值，再去依次匹配右边列的索引值。

②错误模糊查询

模糊查询 like 的常见用法有 3 种：

模糊匹配后面任意字符：like '张%'
模糊匹配前面任意字符：like '%张'
模糊匹配前后任意字符：like '%张%'

而这 3 种模糊查询中只有第 1 种查询方式可以使用到索引，具体执行结果如下：

③列运算

如果索引列使用了运算，那么索引也会失效，如下图所示：

④列使用函数

查询列如果使用任意 MySQL 提供的函数就会导致索引失效，比如以下列使用了 ifnull 函数之后的执行计划如下：

数据库的资源比程序的资源稀缺，数据库很难实现主从复制，且会有延迟，且备份的数据库只是为了防止主数据库发生磁盘永久性损坏，并未提升并发能力。

所以不要在MySQL中做业务函数，在程序中去实现扩容，在不同的电脑上做一个负载均衡，提升并发能力。

⑤类型转换

如果索引列存在隐式类型转换，那么也不会走索引，比如 address 为字符串类型，而查询的时候设置了 int 类型的值就会导致索引失效，如下图所示：

新增的话无所谓，可以有类型转换。但查询就不行。

⑥使用is not null

当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的，如下图所示：

总结：导致 MySQL 索引失效的常见场景有以下 6 种：

联合索引不满足最左匹配原则。
模糊查询最前面的为不确定匹配字符。
索引列参与了运算。
索引列使用了函数。
索引列存在类型转换。
索引列使用 is not null 查询。

PS：索引和约束的区别

①定义和作用不同：

约束是保证数据的可靠性的；
索引是加速查询的。
不是⼀回事。

②可以相互创建和删除：

当创建了主键、外键、唯⼀约束，也就创建了对应的索引；当创建了索引，也就创建了对应的约束。
当删除了索引，也会删除对应的约束。
部分索引和约束是共生的关系，如主键、外键、唯一。创建非空约束是不会创建非空索引的。

PS：聚簇索引和非聚簇索引有什么区别？

索引的存储数据不同，⽐如以下建表语句：

create table T (
    -- 创建主键（索引）
    id int primary key,
    k int not null,
    name varchar(16),
    -- 创建普通索引
    index (k)
) engine=InnoDB;

每一个索引都会对应一棵B+树。

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下：

聚簇索引：非叶子节点存储的是主键 id，叶⼦节点存储的是表的⾏数据（宏观来看），所以可以直接返回结果；
⾮聚簇索引：非叶子节点存储的是二级索引的值，叶⼦节点存储的是主键 id，需要使⽤主键 id 再去聚簇索引中获取表的相关信息，所以执⾏效率没有聚簇索引⾼，⽽这个查询的过程就叫做回表查询。

如查找k=1，在非聚簇索引中查得id=100，再去聚簇索引中查找id=100对应的R1，R1存的是一行数据的内存地址，去这个内存地址中查到对应数据。

只有主键索引的叶子节点存储的是数据的内存地址，其他索引的叶子节点存储的都是主键信息。

一张表只需要给一个主键索引的B+树存储内存地址，这个就可以作为其他索引的辅助搜索条件了，每个字段都可以找到主键索引的id，不需要重复存储数据的内存地址了。

聚簇索引和⾮聚簇索引的区别主要体现在两⽅⾯：

执⾏效率：聚簇索引查询速度更快，因为聚簇索引存储的是数据，⽽⾮聚簇索引存储的是主键 ID，需要进⾏回表查询。聚簇索引对比的是主键，如果主键能够对应得上，那么就能直接查询到主键对应的行数据；但是二级索引的叶子节点存储的是主键，因此当二级索引能够匹配上之后，只能拿到主键的信息，然后根据主键的信息，去聚簇索引里面找到叶子节点对应的行数据，这样才能完成二级索引的数据查询。把二级索引进行查询数据的过程叫回表查询。
数量上：聚簇索引⼀个表只能有⼀个，⽽⾮聚簇索引可以有多个。