小蔡学Java
  1. 首页
  2. MySQL
  3. 正文

MySQL索引及SQL优化

2024-03-08 22:46 1115 0 MySQL MySQLSQL优化

一、索引

1.1、简单介绍

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

优点：

  1. 大大增加了查询速度. 缺点：

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

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

1.2、索引的分类

1.2.1、主键索引

实际上就是我们创建数据库时指定的主键（主键索引值不能为空、不能重复），会自动创建索引，叫做 “主键索引”，在 innodb 引擎中就是所谓的 “聚簇索引”.

例如，以 id 为主键建表

create table user(id int PRIMARY KEY, name varchar(20), age int);

然后通过以下命令查看 user 表的索引

show index from user;

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

就是为表中的某一列创建的索引，一个表中可以有多个单列索引.

例如，表中有字段 id、name、age，那么为 其中的 name 创建一个索引，就叫单列索引.

创建方式有以下两种：

a）建表时创建（注意，这种方式创建，索引名和字段名一致）

#给 name 单独创建索引
create table user(id int primary key, name varchar(20), age int, key(name));
 
#给 name 和 age 分别创建索引
create table user(id int primary key, name varchar(20), age int, key(name), key(age));
 
#创建索引时命名
create table user(
    id int primary key,
    name varchar(20),
    age int,
    key 自定义名称 (name)
);

b）建表后创建

create table user(id int primary key, name varchar(20), age int);
 
create index index_name on user(name);

c）删除索引

drop index 索引名 on 表名

1.2.3、唯一索引

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

亲测：唯一索引的性能比普通索引要好（将近 5 ~ 6 倍）

Ps：`允许有 null 值，并且可以有多个`.

创建方式有以下两种：

a）建表时指定

# 第一种写法
create table user(id int primary key, name varchar(20) unique, age int);
 
# 第二种写法
create table user(id int primary key, name varchar(20), age int, unique(name));

b）建表后创建

create unique index index_name on user(name);

1.2.4、复合索引

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

Ps：查询时，在 where 条件后，必须要使用 `and 连接复合索引字段`，否则不生效.

创建方式有以下两种：

a）建表时创建

create table user(id int primary key, name varchar(20), age int, key(name, age));

b）建表后创建

create index name_age_index on user(name, age);

1.2.5、复合索引经典问题

问题：有一个用户表，给 name、age、gender 三个字段创建了一个复合索引 key(name, age, gender)，以下场景，哪种查询索引会生效？

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

name                          	生效
name  age                     	生效
name  age  gender          	  生效
name  gender  age     		  生效
age  gender                	  失效
gender                        	失效
gender  age  name     		  生效

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

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

1.3、索引原理

1.3.1、主键自动排序

当我创建一个 user 表（含主键 id），然后按照无序 id 的方式插入数据，会发现查询结果尽然按照 主键 id 排序了

为什么会进行`排序`呢？

排序之后相对来说，查询更快. 例如有 10 个自增 id，现在查询 id = 3 的，那么只需要向下对比三次即可得到，而对于无序数据来说每次都需要遍历一遍数据才能得到.

这也就说明为啥主键不建议使用 uuid 去建立，而是使用 int 类型？因为在主键建立索引的时候，会先根据表中的主键去排序，排序后在查询效率会更高.

1.3.2、索引的底层原理

假设有如下表和信息

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

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

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

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

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

1.3.3、B 树和 B+树的区别

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

B树

B+树

  • B+树的磁盘读写代价更低：B+ 树非叶子节点只存储索引，B- 树 data 数据也需要存储，而每一页的存储空间是有限的（默认 16 kb），那么如果 data 数据较大时，每个节点能存储的 key 就很少，进而导致树的深度较大，增大了查询时的磁盘 IO 次数（每一层都进行一次 IO）.
  • B+树的数据都存储在叶子结点中， 分支结点均为索引，方便扫库 ，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

1.4、聚簇索引和非聚簇索引

分类 含义 特点
聚集索引 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有,而且只有一个
非聚集索引 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

1.4.1、innoDB 中的主键索引

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

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

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

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

查询过程：例如 where name='cyk'，步骤如下

在辅助索引树中检索 name，然后到达叶子节点获取对应的主键.
根据主键在聚簇索引 B+ 树种在及进行一次检索操作，最终到达叶子节点获取整行数据.

b）非聚簇索引：在 myisam（直译：“麦zen”）使用的是非聚簇索引，也由两颗 B+ 树构成（主键索引、辅助索引）.

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

辅助索引 B+ 树：存储了辅助键（非主键）. 叶子节点都是用一个地址指向真正的表的数据，因此辅助键无需像 innoDB 一样访问主键索引树.

1.4.2、使用聚簇索引的优势

问题：每次使用辅助索引检索都需要经过两次 B+ 树查询，看上去聚簇索引的效率明显低于非聚簇索引，这不是多此一举么，聚簇索引优势在哪？

缓存机制：访问同一页的不同记录时，会把页加载到缓存中，再次访问的时候，会在内存中完成访问，不必访问磁盘，而主键和数据又是一起被载入内存的，因此按照主键 id 来组织数据（排好序的），获取更快.
叶子节点存储：innoDB 中的辅助索引叶子节点存储主键值，而不是物理地址，因此当行数据发生改变时（对表进行增删改），叶子节点也无需像 myisam 非聚簇索引的索引树叶子节点一样更新物理地址，只需要维护索引树即可.
空间上：innoDB 中的辅助索引叶子节点存放的是主键值，而 myisam 中存储的是物理地址，因此空间占用更小.

1.4.3、使用聚簇索引需要注意什么

主键最好不要使用 uuid，因为 uuid 值过于离散，不适合排序，并且有可能生成的 uuid 插入在索引树的中间位置，导致树调整复杂度变大，查询时消耗更多的时间.

建议使用 int 或者 bigint 类型的自增 ，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小.

1.4.4、为什么主键通常建议使用自增 id

使用自增ID作为主键，可以确保每次插入新记录时，记录都会顺序添加到当前索引节点的后续位置。当一页写满时，会自动开辟一个新的页。这种顺序插入的方式可以减少磁盘I/O的次数，从而提高插入性能。相比之下，如果使用非自增的主键，可能会导致插入操作变得随机，从而增加磁盘I/O的开销。

1.5、索引失效的场景

  1. like 查询：如果匹配字符串的第一个字符为 "%"（匹配一个或多个字符） ，索引不会被使用；如果 "%" 不是在第一个位置，索引就会被使用.

  2. or 查询：如果 or 前后的两个条件都是索引，那么就会使用索引，如果任意一个不是索引，那么查询中不使用索引.

3.复合索引：需要满足匹配原则才可以（上面讲到过了）。

二、SQL 优化

2.1、避免使用 select *

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

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

2.2、尽量避免多表联查

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

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

2.3、避免索引失效场景

这一点上面已经详细讲到过了，这里不再赘述

2.4、批量操作

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

a）错误的实现如下：

for(user in userList) {
    userRepo.inser(user)
}
 
// 对应的sql: insert into user(...) values(...)

由于数据库也是客户端服务器的结构，因此我们每次和数据通信都是需要一定的网络开销，因此，如果我们能够将这种大量请求打包成一个请求，就可以减少更多的性能损耗.

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

b）正确实现：

userRepo.insertBatch(userList)
 
// 对应的sql: insert into user(...) values(...),(...),(...)

当然这里提一点，如果数据量特别大的情况下，一次搬运的效率反而会更低（亲测），因此可以来分批次梳理，例如第一次插入 10000 条数据，第二次插入 10000 条数据这样.

2.5、limit 优化

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

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

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

2.6、in 中值过多

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

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

select id,name from user where id in (1,2,3...100000000);

b）因此我们应该在业务代码中加以限制，如果超出了 500 条记录，就抛异常.

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

2.7、高效分页查询

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 一定要是连续的.

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

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

  • 空间开销：索引也是需要占用一定的存储空间，同时还会带来一定的性能损耗
  • 插入、更新和删除的开销：当表中的数据发生变化（如插入、更新或删除）时，索引也需要进行相应的更新。这意味着每次数据变化时，数据库不仅要修改数据本身，还要修改相关的索引。如果索引过多，这些操作会变得非常耗时，降低数据库的性能。

阿里巴巴开发手册中规定，单表索引数量尽量控制在 5 个以内 . 当然也不是绝对的，需要根据项目具体的项目而定.

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

2.9、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 条件过滤在前，就已经把很多多余的数据过滤掉了，这样分组时就会更高效.

评论( 0 )

  • 博主 Mr Cai
  • 坐标 河南 信阳
  • 标签 Java、SpringBoot、消息中间件、Web、Code爱好者

热门文章

标签云

MybatisJVM堆栈抽象工厂模式synchronized / lock适配器模式去重KafkaCASCPU过高排查消息丢失ElasticSearch优化RT(响应时间)组合模式原型模式Redis数据结构号段模式宕机微服务项目架构HashMap单例模式合并写请求开发规范BigDecimal异步建造者模式延时队列工厂模式踩坑

文章目录

    友情链接

    百度