MySQL 4种基本索引、聚簇索引和非聚索引、索引失效情况、SQL 优化

一、索引

1. 概述

索引就是一种帮助 MySQL 提高查询效率的数据结构.

优点：

大大增加了查询速度.

缺点：

索引实际上是一张表，因此需要消耗一部分空间资源.

对表中的数据进行增删改的时候，需要更新索引，因此速度会受到一定影响

2. 索引分类

主键索引

实际上就是我们创建数据库时指定的主键，会自动创建索引，叫做 “主键索引”，在 innodb 引擎中就是所谓的 “聚簇索引”。

CREATE TABLE `nba_new_csv` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Team` varchar(255) DEFAULT NULL,
  `Number` varchar(255) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=458 DEFAULT CHARSET=utf8;

其中，id就是主键，通过命令查看nba_new_csv表的索引

show index from nba_new_csv;

单值索引（单列索引、普通索引）

就是为表中的某一列创建的索引，一个表中可以有多个单列索引。例如，表中有字段 id、Name、Team，那么为其中的 Name 创建一个索引，就叫单列索引。

-- 创建索引
create index index_name on nba_new_csv(Name);
-- 语法格式：
create index [索引名] on [表名](字段名);


-- 删除索引
drop index index_name on nba_new_csv;
-- 语法格式：
drop index [索引名] on [表名];

唯一索引

在创建表的时候，有时候我们会通过 unique 指定某个字段唯一，这个时候就会创建唯一索引。

-- 创建索引
create unique index index_uq_name on nba_new_csv(Name);
-- 语法格式：
create unique index [索引名] on [表名](字段名);

复合索引

我们为表中的多个字段一起创建一个索引.

-- 创建索引
create index name_team_index on nba_new_csv(Name, Team);
-- 语法格式：
create unique index [索引名] on [表名](字段名);

复合索引失效问题：

以下是 where 查询后通过 and 拼接的字段.

Name 生效
Name Team 生效
Team Name 生效
该怎么判断呢？符合索引生效只要满足以下任意一个原则即可：

最左前缀原则：必须包含做前缀，也就意味着 Name、 Name Team 是生效的.
mysql 引擎为了更好的利用索引，在查询过程中会动态调整查询字段顺序，便于利用索引，也就意味着只要包含所有索引字段即可（任意的组合都可以）。

索引原理

主键自动排序

排序之后相对来说，查询更快. 例如有 10 个自增 id，现在查询 id = 1 的，那么只需要向下对比一次即可得到，而对于无序数据来说每次都需要遍历一遍数据才能得到。这也就说明为啥主键不建议使用 uuid 去建立，而是使用 int或者bigint 类型？因为在主键建立索引的时候，会先根据表中的主键去排序，排序后再查询，效率会更高。

索引的底层原理

假设有如下表和信息

索引的数据结构就是一个 b+ 树，原理如下

a）排序，形成链表：表中的每一条数据组织成一个链表中的一个节点，结构由三部分构成：“主键 + 数据 + 指针”，数据就是表中的非主键字段（name, age），指针就是用来指向下一个节点，这些节点会现经过主键 id 的排序，最后组织成一个链表的结构，得到b+树的叶子节点如下

b）页管理：将链表进行分页管理，每一页的大小默认存储 16kb，假设如下图（真实情况一页存放的数据有很多）

c）页目录管理：将每一页最左边节点的主键和指针拿出来存放到页目录中，页目录的默认大小也是 16kb

d）如果页目录的大小占满了，那么可能还会继续向上生成页目录（父节点），不过一般开发存储的数据，树的高度都不会超过 4 的，也就是说，当需要查找某一数据时，最多只需要 1~3 次 I/O 操作（注意：顶层的根节点是在内存中的）

B树和B+树的区别

B+ 树相当于是在 B 上的一种优化，主要区别如下：

B+ 树非叶子节点只存储键值对信息，B 树 data 数据也需要存储，而每一页的存储空间是有限的（默认 16 kb），那么如果 data 数据较大时，每个节点能存储的 key 就很少，进而导致树的深度较大，增大了查询时的磁盘 IO 次数（每一层都进行一次 IO）.
B+ 树的叶子节点保存全集数据，是一个链表结构，而非叶子节点只存储 key，大大增加了非叶子节点存储 key 的数量，降低了树高.

聚簇索引和非聚簇索引
- innoDB 中的主键索引
a）聚簇索引：由主键索引和辅助索引构成（两颗 B+ 树）.

主键索引：主键索引就是刚刚讲到的 B+ 树结构，当需要查找例如 where Id = 14，就会去 B+ 树顺着目录找到对应的叶子节点，然后直接获取节点中的数据（myisam 非聚簇索引中的主键索引的叶子节点这里存的是数据的地址，而非数据）.

Ps：如果没有定义主键，就会选择唯一且非空的索引代替，如果非空索引也没有，就会自己隐式定义一个主键形成聚簇索引

辅助索引（innoDB 中的非聚簇索引就是辅助索引）：就是在聚簇索引之上建立的索引，一般来说就是表中给其他字段建立的索引（非主键索引），也就是复合索引、普通索引、唯一索引，并且的叶子节点存储的不再全集数据，而是主键值.

查询过程：例如 where name=‘cyk’，步骤如下
1. 在辅助索引树中检索 name，然后到达叶子节点获取对应的主键.
2. 根据主键在聚簇索引 B+ 树种在及进行一次检索操作，最终到达叶子节点获取整行数据.
b）非聚簇索引：在 myisam（直译：“麦zen”）使用的是非聚簇索引，也由两颗 B+ 树构成（主键索引、辅助索引）.

主键索引 B+ 树：非叶子节点存储了主键和指针，叶子节点存储的是主键、数据真实地址、指针.

辅助索引 B+ 树：存储了辅助键（非主键）. 叶子节点都是用一个地址指向真正的表的数据，因此辅助键无需像 innoDB 一样访问主键索引树.
- 使用聚簇索引的优势
每次使用辅助索引检索都需要经过两次 B+ 树查询，看上去聚簇索引的效率明显低于非聚簇索引，这不是多此一举么，聚簇索引优势在哪？
1. 缓存机制：访问同一页的不同记录时，会把页加载到缓存中，再次访问的时候，会在内存中完成访问，不必访问磁盘，而主键和数据又是一起被载入内存的，因此按照主键 id 来组织数据（排好序的），获取更快.
2. 叶子节点存储：innoDB 中的辅助索引叶子节点存储主键值，而不是物理地址，因此当行数据发生改变时（对表进行增删改），叶子节点也无需像 myisam 非聚簇索引的索引树叶子节点一样更新物理地址，只需要维护索引树即可.
3. 空间上：innoDB 中的辅助索引叶子节点存放的是主键值，而 myisam 中存储的是物理地址，因此空间占用更小.
- 使用聚簇索引需要注意什么
主键最好不要使用 uuid，因为 uuid 值过于离散，不适合排序，并且有可能生成的 uuid 插入在索引树的中间位置，导致树调整复杂度变大，查询时消耗更多的时间.

建议使用 int 或者 bigint 类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小.
- 为什么主键通常建议使用自增 id
使用自增ID作为主键，可以确保每次插入新记录时，记录都会顺序添加到当前索引节点的后续位置。当一页写满时，会自动开辟一个新的页。这种顺序插入的方式可以减少磁盘I/O的次数，从而提高插入性能。相比之下，如果使用非自增的主键，可能会导致插入操作变得随机，从而增加磁盘I/O的开销。
- 索引失效的场景
  1. like 查询：如果匹配字符串的第一个字符为 “%”（匹配一个或多个字符），索引不会被使用；如果 “%” 不是在第一个位置，索引就会被使用.
  2. or 查询：如果 or 前后的两个条件都是索引，那么就会使用索引，如果任意一个不是索引，那么查询中不使用索引.

二、SQL优化

避免使用 select *

很多查询场景，为了方便，喜欢直接使用 select *，一次性查出所有列的数据.  但是可能我们真正使用到的只有其中的 一两列，造成以下问题：

浪费内存 和 cpu 资源.
多余的数据会占用 网络带宽，增加数据传输时间.
最重要的一点是，select* 不会走“覆盖索引”（一种复合索引，同时复合最左前缀原则，并且 select 子句中也包含了索引列，这个查询就会用到 覆盖索引），会出现大量的 回表（根据索引找到指定行数据后，还需要再次根据主键到数据块里取数据） 操作.

尽量避免多表联查

一般多表联查都会涉及到笛卡尔积，然后再从中挑选出数据，一旦联查的两张表中数据都比较大的时候，效率就会非常低.

因此一般建议的的做法是，先调用一次接口从表一中拿到数据，然后再拿着表一中的数据调用一次数据库，拿到表 2 的数据，然后在内存中通过 Stream 流的方式来处理数据（group by...）

避免索引失效场景
批量操作

当有一个业务是需要你批量插入数据，如何实现？

a）错误的实现如下：

for(user in userList) {
    userRepo.inser(user)
}
 
// 对应的sql: insert into user(...) values(...)
由于数据库也是客户端服务器的结构，因此我们每次和数据通信都是需要一定的网络开销，因此，如果我们能够将这种大量请求打包成一个请求，就可以减少更多的性能损耗.

好比运输快递，是一个个快递运好呢，还是一个车里装一堆快递，一次运输好呢~

b）正确实现：

userRepo.insertBatch(userList)
 
// 对应的sql: insert into user(...) values(...),(...),(...)
当然这里提一点，如果数据量特别大的情况下，一次搬运的效率反而会更低（亲测），因此可以来分批次梳理，例如第一次插入 10000 条数据，第二次插入 10000 条数据这样.

limit 优化

a）错误实现：例如文章分页查询业务，我们可以通过 sql 语句一次性的拿到所有文章，然后在内存中控制，拿出我们想要的分页片段.

这样做，功能上讲确实没什么问题，但是每次都是查询出所有文章数据，sql 写起来是方便，但是性能大大下降.

b）正确实现：使用 offset + limit 的方式来限制拿到的文章，这样就大大减少了网络和系统开销.

in 中值过多

a）有时候我们会根据 id列表 来限制查询哪些用户的信息，就会通过 in 来指定.

但是如果我们不加以限制，可能会导致一次查询很多数据，导致接口超时，例如：

select id,name from user where id in (1,2,3...100000000);
b）因此我们应该在业务代码中加以限制，如果超出了 500 条记录，就抛异常.

如果一定要超出的话，也可以使用多线程的方式，最后把查询到的数据汇总.

高效分页查询

a）一般我们的分页查询都是如下实现：

select id, name from user limit 10,20
上述是表中数据量比较少的时候没有什么问题.  如果表中数据量很多，就可能出现性能上的问题.

例如：

select id, name from user limit 10000000,20
MySQL 会先查到 10000020 条数据，然后丢弃前面的 10000000 条，只查询后 20 条，这样也是非常浪费资源的.

b）这种海量数据，我们就可以利用 id 主键索引，去查询：

select id,name from user where id > 1000000 limit 20;
 Ps：但是这种情况要求 id 一定要是连续的.

索引优化、并控制索引的数量

索引能提高 SQL 的查询性能，但是索引数量并不是越多越好，原因如下：

空间开销：索引也是需要占用一定的存储空间，同时还会带来一定的性能损耗
插入、更新和删除的开销：当表中的数据发生变化（如插入、更新或删除）时，索引也需要进行相应的更新。这意味着每次数据变化时，数据库不仅要修改数据本身，还要修改相关的索引。如果索引过多，这些操作会变得非常耗时，降低数据库的性能。
阿里巴巴开发手册中规定，单表索引数量尽量控制在 5 个以内.  当然也不是绝对的，需要根据项目具体的项目而定.   

另外如果经常使用 模糊查询，还是将其转移到 ES 中.

group by 分组的高效写法

group by 的主要作用就是分组 + 去重，常常也会搭配 having 一起配合使用，表示分组后进行条件过滤.

例如场景：要求查询 id <= 200 的用户数据，并且根据 sex 分组.

a）一种不太好的写法如下：

select id,name,age from user group by sex having id <= 200
因为这种写法是先分组后，再去过滤 id 大于 200 的用户. 也就是说，分组是按照你表中有多少用户数据就对多少用户进行分组.  效率低下.

b）正确写法如下：

select id,name,age from user where id <= 200 group by sex
使用  where 条件过滤在前，就已经把很多多余的数据过滤掉了，这样分组时就会更高效.

目录CONTENT

MySQL 基本索引、聚簇索引、非聚索引、索引失效、SQL 优化

MySQL 4种基本索引、聚簇索引和非聚索引、索引失效情况、SQL 优化

一、索引

1. 概述

2. 索引分类

二、SQL优化

评论区