数据库调优原理及索引设计

夏の在りか

已于 2024-01-10 10:20:35 修改

阅读量80

点赞数 2

分类专栏：后端调优文章标签：数据库

于 2023-09-21 16:34:40 首次发布

本文链接：https://blog.csdn.net/Darlight/article/details/132691896

版权

后端调优专栏收录该内容

3 篇文章 0 订阅

订阅专栏

sql调优原理及设计

索引是什么
索引数据是如何储存的
- 索引的数据结构
为什么要了解索引的结构
数据库执行一条查询的逻辑
Explain的使用及分析
- explain介绍
- explain的字段含义
索引的设计原则
慢SQL的排查与优化
- - 总结

引言： 由于市面上的数据库种类繁多,其中以oracle,mysql,postgreSQL等为主流,这些数据库对于查询逻辑和索引设计都有着自己的特色,但基本上也都大同小异，这里我们就以相对比较简单且应用比较广泛的mysql为例，来讲解索引具体是怎么工作的

索引是什么

索引是一种数据结构，它按照一定的排序规则记录数据表中的特殊字段，在查询时通过仅查询索引字段提高查询效率，然后找到对应数据在表中的记录位置
常见的索引数据结构：不用多说，非常经典的mysql B+Tree,除此之外还有MyISAM、InnoDB等。还有其他数据库的的BTree、hash、BRIN、GIN、bitmap等
本篇以mysql B+Tree为例，这是我们最常用的索引类型，也是大多数数据库都具备的索引类型

索引数据是如何储存的

索引的数据结构
- 树形数据结构
  我们知道mysql索引数据结构是B+Tree而不是B-Tree,那么B-Tree和B+Tree有什么区别呢,mysql为什么要使用B+Tree作为索引的数据结构?
  首先我们来看一下这两个数据结构有何异同
  - B-Tree
    - 叶子节点都在同一深度,且叶子节点之间没有指针指向
    - 无论叶子节点还是根节点都储存数据
    - 索引数据从左到右递增排列
    - 所有索引不重复
  - B+Tree
    - B+Tree非叶子节点不储存data，只储存冗余索引（子节点和父节点的索引存在重合），可以存放更多的索引数据
    - 叶子节点之间都有指向下一个叶子节点的指针
    - 叶子节点包含所有的索引数据
      `
    - 拓展:联合索引在B+tree中存储的结构
      - 联合索引储存的结构相对单索引比较复杂,遵循最左原则
      - 拿个例子来看
      - 假设有以上多名学生的成绩数据,对age,math_score,total_score设置联合索引
      - 每一非叶子节点会储存三个数据的最小开始值,直到叶子节点储存所有数据直到下一索引开始前
      - 示例图如下
        
        这个图应该是比较清晰的能帮助你理解mysql的联合索引结构了。(非叶子节点也是储存二级及以下索引的)。可以看到二级及以下索引是依赖于上级索引创建的，就好比创建了age、age_mathScore、age_mathScore_totalScore三个索引，一级以下索引不单独排序，所以索引的查询遵循最左前缀原则。联合索引固然能提升查询的效率，但是如果数据过多过大会影响树的深度和复杂度，反而影响效率。

为什么要了解索引的结构

索引的高效查询与索引的数据结构设计关系密不可分,了解数据在索引中存储的方式才能理解索引的高效查询的原理
如果开发者不懂索引的存储结构和索引查询的原理,那么对于复杂sql的优化和索引设计将会是灾难性的
理解数据库使用索引的逻辑及索引的查找逻辑才会写出性能更高的sql

数据库执行一条查询的逻辑

以mysql为例
首先客户端发送连接请求到mysql，mysql查询用户，匹配用户权限，创建session，同时将权限储存在session中返回，所以这也会有一个小问题，就是如果有在连接着的用户，即使管理员修改了此用户的权限，在该用户断开重连之前，权限仍然是修改前的权限（而postgre则不允许修改还连接着的用户）
以select命令为例：客户端发送command到server，如果客户端长时间不操作，在一定时间后连接将会超时（mysql默认8小时）
如果你的数据库支持并开启了缓存的话，首先查询缓存，没错，不仅仅mybatis有缓存，数据库也有缓存，其原理也非常相似，对应的select语句为key，结果为value，如果查询的表没有更新记录，且命中缓存，数据库将直接返回查询结果，这对于某些改动较小的配置表来说能提高一定的查询效率，但总体来说比较鸡肋。
没有命中缓存则开启真正的查询
- 分析sql
  - mysql执行查询前会先由分析器完成对sql语句的分析
  - 分析查询语句的词法、语法等，如果没有错误，将select，<table>,from，where，<param>等关键字提取
  - 分析器最后会生成语法树
- 优化sql
  - sql分析结束，优化器将优化sql
  - 优化器会根据查询的字段和条件，尤其是多表和多字段，优化器会根据索引数据量和表情况重新调整查询顺序
- 执行sql
  - 优化器优化完sql就会交给执行器执行sql
  - 以以下sql为例,执行过程如下
```
	select name from user where user_id =23;
```
    - 如果user表无索引
      - 执行查询sql时,InnoDB引擎会从表的第一行开始查找user_id字段判断是否为23,如果是,将此行name字段数据存到结果集中
      - 继续遍历user表,直到查询完所有数据,找到所有id为23的数据存入结果集
      - 返回结果集
    - 如果user_id为主键
      - 主键的特性是具有唯一性的索引,所以mysql首先会查找索引中id为23的数据
      - 由于我们只设置了一个索引,但是我们需要查到这个用户的name信息,所以mysql就根据这个索引记录的该条数据在表中的位置取出该条记录并获取name字段返回给客户端
    - 如果user_id和name是联合主键, idx_userId_name
      - 根据上面的索引结构我们知道,索引满足最左前缀原则, user_id存在树的最表层节点,name字段按照排序存在对应user_id的内部
      - InnoDB同样先在索引树中找到user_id为32的子节点,此时联合索引中覆盖了需要查询的name字段,mysql会将找到的索引中所有的name数据放入结果集
      - 最后将结果集返回给客户端
    通过上面一个简单的例子我们可以发现,对于数据库尽量设置一个唯一性索引,其实如果我们不设置这个索引,mysql内部也会维护一个类似于主键的索引结构。其次，如果是查询频繁的个别字段，可以设置联合索引，但联合索引最好不要超过三个字段，否则会增加索引树的维护难度，尤其对于变动频繁的字段，索引树频繁更换结构反而会影响数据库性能
- 执行器执行完后生成一条与执行sql语义相反的sql存入到undolog中，比如insert一条记录，undolog中就记录一条delete语句
- 如果开启了bin-log日志，那么同时会将执行的sql记录到bin-log中

Explain的使用及分析

explain介绍
- EXPLAIN是数据库中的关键字，可以分析查询语句的索引使用情况，查询效率，优化建议和执行计划等，但这仅仅是模拟执行，并不会真正执行此sql
- explain extended:
```
explain extended select * from actor where id=1;
```
  mysql8.0以后这个api已经被废弃
  mysql8.0之前的版本extended关键字会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。其中filtered 列，是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中id值小的表进行连接的行数。
- explain partitions:
  
  相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
```
	explain partitions select * from actor where id=1;
```
explain的字段含义

- id列：
- id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
sql explain select * from actor where id in ( select actor_id from film_actor where film_id in ( select film_id from film where name='film0' ) );
- select type
  - simple: 简单查询。不包含简单子查询和union，上一个示例中的子查询只有一个结果，外层嵌套select根据结果直接走主键索引查询，无需生成临时表保存结果，所以在mysql中判定为简单查询
  - Materialization：物化（英译），在SQL执行过程中，第一条执行的select数据较多，mysql将结果集保存为临时表，后续对子查询结果集的访问将直接通过临时表获得
```
	explain 
	select * from actor where id in (
		select actor_id from film_actor where film_id in
			(
			-- 这里结果集数据较多，mysql会生成一张临时表进行查询
				select film_id from film 
			)
		);
```
  - primary：
    复杂查询中最外层的 select
```
	EXPLAIN select name from film f where id=1 UNION all select name from actor where id=1
```
  - subquery
    包含在 select 中的子查询（不在 from 子句中）
  - derived
    包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义)
```
 set session optimizer_switch='derived_merge=off'; -- 关闭mysql(5.7以后)新特性对衍生表的优化
 explain select (select 1 from student where id = 1) from (select * from sys_user where user_id = 1) der;
```
  - union：在 union 中的第二个和随后的 select
```
explain select 1 union all select 1;
```
- table
  - 这一列表示 explain 的一行正在访问哪个表。
    当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
    当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的select 行id。
- partitions:
  - 匹配的分区表
- type
  - null: 直接通过索引就能查找所有的数据,不需要在访问表
```
	-- id是主键索引
	explain select max(user_id) from sys_user;
```
  - const, system: mysql对索引或主键字段进行等值查询,匹配的索引和表结果只有一条,system是const的特例,单表只有一条数据
```
	 explain 
	 select * from 
	 -- 这里student表能确定只有一条数据,所以上层select是system
	 (select * from student where id=1 limit 1) t;
```
  - eq_ref： 连接查询使用唯一索引或主键索引且最多只会返回一条符合条件的记录。属于连接查询中效率最高的类型,注意这里的索引只能是唯一索引或主键索引,不是普通索引。
  - ref: 相对于eq_ref来说,使用的不是唯一索引,而是普通索引或联合索引的部分前缀,结果集可能不止一条数据
```
 -- 这里我给sys_user_role的user_id添加了一个普通索引
 explain select * from sys_user u left join sys_user_role r on u.user_id = r.user_id where u.user_id=1
```
  - range：一般in(), between ,> ,<, >= 查询条件中会出现,使用一个索引来检索给定范围的行。
```
explain select * from sys_user where user_id >2;
```
  - index： 扫描全表索引，通常是查询的字段已经包括在索引中的,即使是包含在联合索引中,效率比all要高
```
-- 这个表设计了一个联合索引 idx_userId_userName
explain select user_name from sys_user ;
```
  - ALL： 全表扫描，数据库会遍历表的所有数据进行查找,效率非常差,一般情况下需要增加索引来优化了
```
	-- nick_name没有添加到索引字段中
	explain select nick_name from sys_user ;
```
- possible_key
  该列表示mysql可能使用该索引进行查询, 但不是所有情况下这个列都有值, 当这个值为null时可能是没有创建和查询条件相关的索引,这种情况可以建立一个相关的索引查看是否能提升效率
- key
  该列表示mysql实际使用到的索引,如果没有使用索引显示为null, 如果possible_key有值,但key没值说明mysql认为使用索引查询的代价比不使用大,如果需要强制使用索引就使用force index
- key_len
  这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。比如 idx_a_b,这个联合索引, a,b都是int类型, key_len为4, int类型占用4个字节,所以mysql只使用到了a索引
  注意,每个数据类型占用的大小是不同的不一定都是4个字节
- ref
  这一列表示使用key列记录的索引进行查找时用的参数类型,最常用的就是const(常量)和字段名
- rows
  这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
- filtered
  这一列表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比
- extra
  这一列表示的是额外信息,有以下几个重要的值
  - Using index：使用覆盖索引
  - Using where：使用 where 语句来处理结果，查询的列未被索引覆盖
  - Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
  - Using temporary：需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。
  - Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
  - Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

索引的设计原则

索引的本质还是一种利于查询的数据结构,对于树形数据结构来说,查找快,但是更新相对来说比较耗费性能和时间,因为涉及到树形的变化,所以对于频繁更新的字段不要不要设计索引
数据库在查找的时候如果需要查找的字段在索引中,那么数据库可以直接返回索引中存储的结果, 但是如果查询的字段在索引中没有,那么数据库就会在拿到索引中存储的数据位置去表中查找数据返回对应字段,这个过程称为回表。所以对于查询频繁的字段,索引设计尽量覆盖常用字段,减少回表情况
联合索引不要过长,尽量控制在三个字段,字段过多会增加B树的深度,影响查询效率增加维护困难
经常需要排序的字段尽量设计索引,索引其天然就具有排序属性,没有索引的字段排序,数据库就会取出全表数据进行排序或者可定位行数的字段以及排序字段排序后回表查询(取决于数据长度), 无论那种情况对于查询都是不利的
枚举类型少的字段尽量不要设计索引,比如一张1千万的表,设计字段性别只有两个,这种字段单独设计索引没有意义
联合索引最左字段不要是小枚举类型的字段
where条件的优化和order by的优化,优先优化where条件

慢SQL的排查与优化

慢sql无非就是三种情况,
一:表没设计好
二:sql没写好
三:索引没优化
实际上sql的影响比索引大的多,一个好的索引设计可以让sql从5秒提升到0.5秒, 但一条好sql可以让60秒的sql提升到5秒

表结构性问题
- 有些数据库表在设计时没有考虑过预留字段和数据增量,在随着需求的增加中不断新增功能,添加字段,添加关联表,导致仅仅查询一个不重要的字段就需要关联外表进行查询,且主表数据庞大,副表过多导致数据库查询效率过低
- 对于数据库表结构设计问题优化比较困难, 情况也非常不同,有的是由于字段设计有问题,比如有设计字段中存储json字符串的,比如字段类型设计不合理的,比如主副表数据拆分不合理的
- 但是设计表有几个通用的规范遵循:
  1. 字段设计必须满足第一范式,即字段最小颗粒化,不可再分割,比如表设计地址一栏,如果设计地址不满足第一范式,可拆分为省,市,县,具体地址(当然这里不要过于死板,针对的是需要拆分查询的字段,如果你不对地址有省市县的查询要求可以不需要这么设计)
  2. 尽量不要把表设计的过大,一些与主业务无关的字段可以拆分到副表中,需要时再关联查询
  3. 但是上面一条优化并不能过于死板的理解,因为查询语句关联的表越多查询效率越低,如果为了查询极少数的字段而关联过多的表反而得不偿失,所以有时候也需要违反规范设计,在主表中设计冗余字段, 比如用户和用户属性表,用户表记录了用户的Id,用户名,权限等,用户属性记录了用户的头像,地址,套餐类型等,查询的时候往往需要展示用户套餐类型,这个字段不常变动且查询频繁,那么可以将套餐类型这个字段设计到主表中,在查询的时候可以减少关联的表
  4. 为每一个表设计易于排序的主键,如果你不设计,部分数据库也会自己维护一个,所以为何不自己设计一个呢。但是切记，这个主键一定要是易于排序的，比如数据库序列或者自增id，对于分库分表可以使用根据时间戳生成的id如snowId
  5. 必要时对于大数据表进行分割
sql结构问题
- 大部分超慢sql都是由于开发者对于数据库查找数据的逻辑不理解导致写了有问题的sql,尤其是针对复杂sql,开发者没有理清查询的层次和逻辑,使用多个无效子查询,关联查询
- sql书写不规范,查询sql应遵循最少字段查询,只查询需要的字段,而很多开发者习惯select *
- 过多在sql中使用函数计算,sql中进行聚合运算会使数据库服务器cpu飙高,数据库会生成临时表,整合运算结果,如果没有必要,尽量在代码中完成计算
- 如果不需要那么多字段,尽量减少关联查询, 如果必须要关联表查询,尽量不要大表关联大表查询
- sql的结构问题很多,具体还需要根据自己的sql去分析,但是统一的优化原则就是将sql越简化越好,能用一张表不用多张表, 尽量少关联,关联用小表, 小表驱大表
索引使用优化
- 索引使用的优化点非常的多,结合上面的查询逻辑,主要有以下几点
  - 查询字段尽量被索引覆盖,这一点的原因上面已经分析
  - where,order by,group by条件尽量满足最左前缀原则
  - in和or查询并不一定都不走索引,数据量大的情况下数据库会使用索引
  - in和exist：
```
select * from A where id in (select id from B) 
```
    子查询数据集小于外层查询数据集时,即B的id小于A时，in优于exists, A小于B时,exist由于in,
  - 对于模糊查询,开发者应该都知道只有s%这种情况才会走索引,%s和%s%都不会走索引,如果你的业务中有过多的模糊查询,那么建议更换技术路线,使用es倒排索引分词查询以提升查询效率,如果是联想查询或者关联查询你可能需要更多的时间开发并训练契合自己业务的大模型了,这些查询都不适用于关系型数据库查询
- 关联查询优化
  关联查询优化我们需要知道两个概念：
  - Nested-Loop Join （NLJ）算法
    - 这套算法称为嵌套循环算法，嵌套循环算法发生在关联查询时关联字段在被关联表中有索引时
    - 需要明确一个基本概念,关联查询有驱动表和被驱动表,左查询左表是驱动表,右表是被驱动表,右关联查询则相反, 只使用join时,mysql会选择较小的表作为驱动表
    - 假设有两个表,小表有100条数据, 大表有10000条数据,两表关联查询
    - 查询过程中,mysql会先查询驱动表满足的条件的记录，然后取出一条数据,获取到和被驱动表关联的字段,在被驱动表的索引中扫描符合的记录, 被驱动表索引查询到记录的位置回表(如果索引未覆盖),获取到的字段会和驱动表数据合并,等待全部查询完返回给客户端
    - 整个过程中最多只扫描了100次驱动表 ,查询100次被驱动表的索引, 如果回表最多再扫描100次被驱动表
  - Block Nested-Loop Join （BNL）算法
    - 同样还是刚才的的两张表,只不过大表的被关联字段没有索引了。如果还是按照刚才的逻辑查询，我们来看看会发生什么
    - 小表的100条还是逐条取出对比大表，此时没有索引，所以数据库只能逐行扫描，一条记录需要扫描10000行， 100条数据需要扫描100万行，而数据库读表操作是读磁盘，不仅会使查询缓慢，还会导致磁盘io飙高
    - 那这种情况显然不能再采取NLJ算法，mysql采用了BNL算法，不同与刚才，mysql首先将所有满足条件的驱动表数据全部存入到join-buffer中，然后读取一条被驱动表（大表中的）的数据到内存中与join-buffer中的数据进行对比，如果满足条件就与buffer中的数据进行合并。
    - 这种算法扫描了100次小表，10000次大表，在内存中进行了100万次计算对比，相对于100万次磁盘读写来说，内存计算100万次已经非常快速了
  - 优化建议
    - 关联字段加索引，不过需要根据具体的数据库和字段来处理，还是那句话，调优思路不可固化，比如关联字段如果是小枚举字段那么添加索引的概率可能并不高，如果你不知道该不该加上索引，可以先加上，再测试查询时间是否提升
    - 如果你明确知道关联查询的表哪个更大，可以手动指定驱动表和被驱动表，做到小表驱动大表，无论是BNL还是NLJ，小表驱动大表磁盘的io次数都可能小于大表驱动小表