数据库调优-04 （特定语句调优、表结构设计优化）

2021/10/4 19:12:56

编程Tag： select 索引 Buffer join 调优主键 04 emp 结构设计

本文主要是介绍数据库调优-04 （特定语句调优、表结构设计优化），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

五、特定语句调优
- 1、jion 语句
  - 1.1 jion语句种类：
  - 1.2 join 算法
    - 1.2.1 Nested-Loop Join (NLJ)
    - 1.2.2 Block Nested-Loop Join (BNUJ)
    - 2.2.3 Batched Key Access Join (BKA)
    - 2.2.4 HAHS JOIN
  - 1.3 JOIN调优原则
- 2、limit 语句
  - 优化方案
- 3、count 语句
  - count(*)
  - count(字段)
  - count(1)
  - count 语句优化方案
- 4、order by 语句
  - order by 实验
  - 优化结论
- 5、group by 语句
  - 5.1 松散索引扫描
  - 5.2 紧凑索引扫描
  - 5.3 临时表
六、表结构设计优化
- 1、三范式
  - 1.1 第一范式
  - 1.2 第二范式
  - 1.3 第三范式：
- 2、反模式设计
- 3、表设计原则

五、特定语句调优

1、jion 语句

1.1 jion语句种类：

左外连接：left join
右外连接：right join
内连接：inner join
交叉连接：cross join （结果是笛卡尔积）

1.2 join 算法

1.2.1 Nested-Loop Join (NLJ)

嵌套循环join：实际上就是简单粗暴的嵌套循环，如果table1有1万条数据，table2有1万条数据，那么数据比较的次数=1万 * 1万 = 1亿次，这种查询效率会非常慢。

1.2.2 Block Nested-Loop Join (BNUJ)

缓存块嵌套循环join：通过一次性缓存多条数据，把参与查询的列缓存到Join Buffer 里，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少了内层循环的次数（遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据）。

扫描次数计算公式:
(S* C)/join_buffer_size + 1

S:缓存的t1/t2表的一行数据
C：:缓存的行数
join buffer-size : join buffer的大小

使用join buffer的条件：

连接类型是ALL, index或range
第一个nonconst table（非常量表）不会分配join buffer ,即使类型是ALL或者index
join buffer只会缓存需要的字段,而非整行数据

可通过join_buffer-size变量设置join buffer大小

show variables like 'join_buffer_size';
set global join_buffer_size = 1024*1024*50;

每个能被缓存的join都会分配一个join buffer,一个查询可能拥有多个join buffer
'join buffer在执行联接之前会分配,在查询完成后释放。

explain查询结果中，Extra 列出现 'Using join buffer (Block Nested Loop)' 表示使用了BNLJ。

2.2.3 Batched Key Access Join (BKA)

批量键值访问，由MySQL5.6引入，BKA的基石: Multi Range Read ( MRR )

什么是MRR？
select * from salaries where from_date <='1980-01-01';
这条语句的查询结果可能会产生随机IO，应为数据是按照主键排列，而不是from_date字段排列的。如果开启MRR，那么会读取结果的主键索引，并按照主键索引排序，排序完成后再到表数据里读取数据，这样读出来的数据就是顺序IO，性能会比随机IO好很多。
: MRR核心:将随机IO转换成顺序1O,从而提升性能。

MRR参数：

optimizer_switch的子参数
- mrr:是否开启mrr, on开启, off关闭
- mrr_cost-based : 表示是否要开启基于成本计算的MRR，成本较高的情况下是不会使用MRR的，关掉此项就会直接使用，不去先判断成本大小了。
read_rnd_buffer-size :指定mrr缓存大小

show variables like '%optimizer_switch%';
show variables like '%read_rnd_buffer_size%';

set optimizer_switch ='mrr_cost_based=off';

一旦开启MRR，会在extra里面展示Using MRR

BKA流程；

参数：

optimizer_switch的子参数
- batched_key_access， on开启, off关闭

-- 默认是关闭的，开启语句：
set optimizer_switch ='batched_key_access=on';

当使用BKA的时候，会在extra里面展示Using join buffer (Batched Key Access)

2.2.4 HAHS JOIN

MySQL 8.0.18引入,用来替代BNLJ
join buffer缓存外部循环的hash表,内层循环遍历时到hash表匹配

注意：

MySQL 8.0.18才引入,且有很多限制,比如不能作用于外连接,比如leftjoin/rightjoin等等。从8.0.20开始,限制少了很多,建议用8.0.20或更高版本。
从MySQL 8.0.18开始, hash join的join buffer是递增分配的,这意味着,你可以为将join-buffer-size设置得比较大。而在MySQL 8.0.18中如果你使用了外连接,外连接没法用hash join ,此时join_buffer-size会按照你设置的值直接分配内存。因此join-buffer-size还是得谨慎设置。
从8.0.20开始, BNLJ已被删除了,用hash join替代了BNLJ

1.3 JOIN调优原则

1、用小表驱动大表

看上图，先执行的就是驱动表，explin的结果就是按执行顺序展示的。

注：用小表驱动大表一般无需人工考虑，MySQL关联查询优化器会自动选择最优的执行顺序。如果优化器抽风失效,可使用STRAIGHT-JOIN，强制先读取左边的表，再读取右边的表。

2、如果有where条件,应当要能够使用索引,并尽可能地减少外层循环的数据量

3、join的字段尽量创建索引
注意：当join字段的类型不同时，索引无法使用

4、尽量减少扫描的行数(explain-rows)

5、参与join的表不要太多
阿里编程规约建议不超过3张。如果业务需要join多张的表，可以根据代码逻辑适当拆分。

6、如果被驱动表的join字段用不了索引,且内存较为充足,可以考虑把join buffer设置得大一些。

2、limit 语句

有这样一条sql

select *
from employees
limit 300000,10;

查询第一页的时候，花费92ms，查询第300001页的时候，花费174ms

优化方案

方案1：覆盖索引 (108ms)

select emp_no
from employees
limit 300000,10;

方案2：覆盖索引+join(109ms)

select *
from employees e
inner join (select emp_no from employees limit 300000,10) t
on e.emp_no = t.emp_no; -- 连接字段都叫emp_no，这行可以简写成 using (emp_no);

方案3：覆盖索引+子查询（126ms）

select *
from employees
where emp_no >=
      (select emp_no from employees limit 300000,1)
limit 10;

方案4：范围查询+limit语句

select *
from employees
limit 10;

select *
from employees
where emp_no > 10010
limit 10;

本例是拿第二页数据，首先查询第一页数据，拿到最后一个数据id值比如说是10010，再根据这个id值去拿第二页数据。这种方式扫描的行数永远都只有10行。

方案5：如果能获得起始主键值 & 结束主键值

select *
from employees
where emp_no between 20000 and 20010;

方案6：禁止传入过大的页码
从业务层面解决。

3、count 语句

count(*)

当没有非主键索引时，会使用主键索引
如果存在非主键索引的话，会使用非主键索引
如果存在多个非主键索引，会使用一个最小的非主键索引

为什么？
innodb非主键索引：叶子节点存储的是：索引+主键；
主键索引叶子节点：主键+表数据；
在1个page里面，非主键索引可以存储更多的条目。

count(字段)

count(字段)只会针对该字段统计，使用这个字段上面的索引（如果有的话），并且会排除掉该字段值为null的行，而count(*)不会排除。

count(1)

count()和count(1)没有区别。
对于MyISAM引擎，如果count()没有where条件(形如 select count() from 表名)，查询会非常的快。
对于MySQL 8.0.13，InnoDB引擎，如果count()没有where条件(形如 select count(*) from 表名)，查询也会被优化，性能有所提升。

count 语句优化方案

方案1：创建一个更小的非主键索引
方案2：把数据库引擎换成MyISAM => 实际项目用的很少，一般不会修改数据库引擎
方案3：创建汇总表 table[table_name, count]
- 好处：结果比较准确
- 缺点：增加了维护的成本
方案4：缓存 select count(*) 结果存放到缓存
- 优点：性能比较高；结果比较准确，有误差但是比较小（除非在缓存更新的期间，新增或者删除了大量数据）
- 缺点：引入了额外的组件，增加了架构的复杂度
方案5：information_schema.tables

select *
from `information_schema`.TABLES
where TABLE_SCHEMA = '库名'
  and TABLE_NAME = '表名';

-- 好处：不操作salaries表，不论salaries有多少数据，都可以迅速地返回结果
-- 缺点：估算值，并不是准确值

方案7：

show table status where Name = 'salaries';
-- 好处：不操作salaries表，不论salaries有多少数据，都可以迅速地返回结果
-- 缺点：估算值，并不是准确值

方案8：

explain select * from salaries;
-- 好处：不操作salaries表，不论salaries有多少数据，都可以迅速地返回结果
-- 缺点：估算值，并不是准确值

4、order by 语句

最好的做法是:利用索引避免排序,利用索引本身的有序性,让MySQL跳过排序过程

order by 实验

下面做个实验，employees表有组合索引index(first_name, last_name):

竟然是全表扫描，没有使用索引，我们在来给语句加个limit来看下

有变成了index，使用上了索引，这是为什么呢？
因为当MySQL优化器发现全表扫描开销比使用索引的开销更低时，会直接用全表扫描。

那这条语句到底能不能使用索引避免排序呢？我们来看下Extra的值：

Extra是null，说明是可以使用索引避免排序的，如果Extra是using filesort时，说明不可以。

按照上面这种方法，我又试了好几种排序语句，列在下面并给出结论：

/*
 * 可以使用索引避免排序
 * [Bader,last_name1, emp_no]
 * [Bader,last_name2, emp_no]
 * [Bader,last_name3, emp_no]
 * [Bader,last_name4, emp_no]
 * [Bader,last_name5, emp_no]
 * ..
 */
explain
select *
from employees
where first_name = 'Bader'
order by last_name;

/*
 * 可以使用索引避免排序
 * ['Angel', lastname1, emp_no1]
 * ['Anni', lastname1, emp_no1]
 * ['Anz', lastname1, emp_no1]
 * ['Bader', lastname1, emp_no1]
 */
explain
select *
from employees
where first_name < 'Bader'
order by first_name;

/*
 * 可以使用索引避免排序
 */
explain
select *
from employees
where first_name = 'Bader'
  and last_name > 'Peng'
order by last_name;

/*
 * 无法利用索引避免排序【排序字段存在于多个索引中】
 * - first_name => (first_name,last_name)
 * - emp_no => 主键
 */
explain
select *
from employees
order by first_name, emp_no
limit 10;

/*
 * 无法利用索引避免排序【升降序不一致】
 */
explain
select *
from employees
order by first_name desc, last_name asc
limit 10;

/*
 * 无法利用索引避免排序【使用key_part1范围查询，使用key_part2排序】
 * ['Angel', lastname1, emp_no1]
 * ['Anni', lastname1, emp_no1]
 * ['Anz', lastname1, emp_no1]
 * ['Bader', lastname1, emp_no1]
 */
explain
select *
from employees
where first_name < 'Bader'
order by last_name;

优化结论

利用索引,防止filesort的发生
如果发生了filesort,并且没办法避免,想办法优化filesort
- 调大sort-buffer-size ,减少/避免临时文件、归并操作
  - optimizer trace中num_initial-chunks_spilledto_disk（表示归并操作）的值非常大时，需要调sort-buffer-size
```
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;

select *
from employees
where first_name < 'Bader'
order by last_name;

select * from `information_schema`.OPTIMIZER_TRACE
where QUERY like '%Bader%';
```
  - sort_merge_passes（执行归并的次数）变量的值，值非常大时，需要调sort-buffer-size
```
show status like '%sort_merge_passes%'
```
- 调大read-rnd_buffer-size ,让一次顺序IO返回的结果更多
- 设置合理的maxlength_forsort-data的值(这个一般不随意调整)
- 调小max_sort_length(排序时最多取多少字节)

5、group by 语句

group by 语句优化有三种方式，分别是（性能从高到低）：
松散索引扫描( Loose Index Scan ) > 紧凑索引扫描( Tight Index Scan) > 临时表( Temporary table )

5.1 松散索引扫描

无需扫描满足条件的所有索引键即可返回结果。

explain的extra展示Using index for group-by 说明使用了松散索引扫描。

使用条件：

查询作用在单张表上
GROUP指定的所有字段要符合最左前缀原则,且没有其他字段
如果存在聚合函数,只支持MINO/MAX) ,并且如果同时使用了.MIN()和MAX),则必须作用在同一个字段。聚合函数作用的字段必须在索引中,并且要紧跟GROUP BY所指定的字段
如果查询中存在除GROUP BY指定的列以外的其他部分,则必须以常量的形式出现
索引必须索引整个字段的值，不能是前缀索引

举例：
假设有index(c1,c2,c3)作用在表t1 (c1,c2,c3,c4)上,下面这些SQL都能使用松散索引扫描:

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;SELECT C1, MIN (c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY cl, c2;
SELECT MAX(c3), MIN (c3), cl, c2 FROM t1 WHERE c2 > const GROUP BY cl, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY cl, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

下面这些SQL不能使用松散索引扫描:

-- 聚合函数不是MIN()或MAX()
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

-- 不符合最左前缀则
SELECT c1, c2 FROM t1 GROUP BY c2, c3;

-- 查询了c3字段,但是c3字段上没有等值查询
-- 改成 SELECT c1, c3 FROM t1 WHERE c3 = const GROUP BY c1, c2; 则可以使用
SELECT c1, с3 FROM t1 GROUP BY с1, c2;

5.2 紧凑索引扫描

需要扫描满足条件的所有索引键才能返回结果，性能一般比松散索引扫描差,但一般都可接受。

explain-extra没有明显的标识。

5.3 临时表

紧凑索引扫描也没有办法使用的话, MySQL将会读取需要的数据,并创建一个临时表,用临时表实现GROUP BY操作。

explain-extra显示Using temporary。

如果发现group by语句出现临时表，可是适当加索引，让其使用松散索引扫描或紧凑索引扫描。

六、表结构设计优化

1、三范式

遵循三范式可以很好的防止冗余。

1.1 第一范式

字段具有原子性，即数据库表的每一个字段都是不可分割的原子数据项，不能是集合、数组、记录等非原子数据项，当实体中的某个属性有多个值时，必须拆分为不同的属性。

例如：

不符合第一范式，address字段还可以拆分成省、市、区等
修改：

1.2 第二范式

满足第一范式的基础上，要求每一行数据具有唯一性，并且非主键字段完全依赖主键字段。

例如：

不符合第二范式，课程学分字段不依赖于学号字段，而依赖于课程字段，只依赖了部分主键
修改：

1.3 第三范式：

满足第二范式的基础上，不能存在传递依赖。

例如：

不符合第三范式，学校地址和学校电话字段依赖了学校字段，学校字段又依赖主键字段，存在传递依赖
修改：

2、反模式设计

有时为了提升查询效率，我们也会不遵从三范式，适当增加冗余。

例如下面的两张表

这两张表是符合三范式的，假设两张表的数据量非常大，而业务要求每次查询学生信息时都要携带学校地址，这种情况下，我们就可以在学生表里冗余存储学校地址，这样就不用联表查询，提升效率。

3、表设计原则

字段少而精，建议20个以内(经验之谈)，超过可以拆分
- 把常用的字段放到一起
- 把不常用的字段独立出去大
- 字段(TEXT/BLOB/CLOB等等)独立出去
尽量用小型字段，
- eg用数字替代字符串
避免使用允许为NULL的字段
- 允许NULL字段很难查询优化
- 允许为NULL字段的索引需要额外空间
合理平衡范式与冗余
如果数据量非常大，考虑分库分表

这篇关于数据库调优-04 （特定语句调优、表结构设计优化）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！