Ammar's Blog
微信号：aicjaish
首页 标签 分类 归档 留言 关于
SQL性能优化
|Java数据库
字数总计: 5.1k|阅读时长: 16 分钟

SQL 优化理论

后台程序员在写代码的时候，能做的只有对写 SQL 尽可能的做出优化，执行效率变高，有效的使用索引，重点放在写 SQL 上。

  1. 查询性能低
  2. 执行时间过长
  3. 等待时间过长(多线程)
  4. SQL 写的太差(多表查询)
  5. 索引失效
  6. 服务器参数(缓存、线程数)设置不合理
  7. 项目需求不合理等等

三高：高性能、高并发、高可用

MySQL 在收到客户端传入的 SQL 语句后，并不能马上对该 SQL 进行执行，需要经过一系列复杂的流程，最终转变成二进制的机器码，才能被执行，我们需要执行的 SQL 进行优化，就需要先了解一个 SQL 语句的执行有哪些主要环节，以查询的 SQL 举例：

虽然有查询缓存，但是查询缓存的效率比较低。大部分情况下，必须走到后面的步骤。

当通过第一个解析树的时候，占位符是不会被解析出来的，如果没有占位符，则完全匹配 SQL，就会执行检查权限然后需操作。如果包含占位符，还会进行后续步骤，把 SQL 交给预处理器。

预处理后，还会经过一个优化的解析树解析成优化后的 SQL 包括占位符预处理语句等等。查询优化器会对新解析树的 SQL 语句再进行调整，比如多表查询时 left join，一般是将表数据少的表在左边 left join 数据多的表，但是如果我们写 SQL 时，没有这样做，查询优化器也会帮我们进行优化。 查询优化器会对 SQL 进行按性能高的规则的调整。

写 SQL 不要依赖查询优化器，要养成写规范的性能高的 SQL 的习惯。经过查询优化器优化后，MySQL 认为已经是最优方案了，所以生成一个查询执行计划，然后交给查询执行引擎执行。

执行引擎是一个接口，如果内部选择哪个存储引擎，就会调用哪个引擎的查询方法查询。常见的有 MyiSAM，InnerDB等等。存储的数据最终都在硬盘上的文件中。

SQL 的执行顺序

写 SQL 的顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

执行顺序

1
2
3
4
5
6
7
8
9
10
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT 
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

B 树的概念

  • 每个结点最多有 m-1 个元素(可以存有的键值对)
  • 根节点最少可以只有1个元素
  • 非根节点至少有 m/2 个元素
  • 每个结点中的关键字都按照从小到大顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树的所有关键字都大于它
  • 所有叶子节点都位于同一层，根节点到每个叶子节点的长度都相同
  • 每个结点都存有索引和数据。

根节点的关键字数量范围：1 <= k <= m-1，非根节点的关键字数量范围 m/2 <= k <= m-1

m表示阶数，表示一个结点最多有多少个孩子节点，比如一个5阶的 B 树，根节点数量范围 1 <= k <= 4

B 树的插入

插入规则，判断当前节点 key 的个数是否小于等于 m-1，如果满足，直接插入，如果不满足，将节点中间的 key 这个节点分为左右两部分，中间的节点放到父节点中。

例子：5阶b树，节点最多4个key，

  • 插入 18,70,50,40

  • 插入22

此时，发现节点的关键字已经大于4了，所以需要进行分裂，分裂的规则在上面已经讲了，分裂后，如下图

  • 接着插入23,24,39，都比40小，因此放入左子树

分裂得到下面的平衡

B 树的删除操作

删除15，这种情况是删除叶子节点的元素，删除之后，节点数还是大于等于 m / 2，这种情况，直接删除即可，

接着删除22，此时，22是非叶子节点，对于非叶子节点的删除，需要用后继 元素 覆盖要删除的元素，然后在后继元素所在的子叶中删除后继元素，比如 要删除22，需要用 24 覆盖 22，然后再子叶中删除24。

此时发现26所在的节点只有一个元素，小于 (m/2) 个，这个节点不符合要求，这个时候的规则是：如果要删除叶子节点，删除后元素个数少于(m/2)，并且它的兄弟节点的元素大于(m/2)个，则先将兄弟节点的元素移到父节点，然后将父节点中最大的元素移到子节点：

此时树达到稳定状态，如果再删除28，删除的是叶子节点，且删除后，不平衡，所以需要向兄弟节点借元素，但是兄弟节点也只有2个，无法借，这种情况，先将父节点元素移动到该节点，然后将当前节点和兄弟节点中的元素合并，形成新的节点

B + 树

B + 树与 B树非常相似，相同点：

  • 根节点至少有一个元素
  • 非根节点元素个数范围：m/2 <= k <= m-1

不同点

  • B+ 树有两种类型的节点，内部节点(也称索引节点)和叶子节点，内部节点就是非叶子节点，内部节点不存储数据，只存索引，数据都存储在叶子节点
  • 内部节点中的元素都按照从小到大顺序排列，对于内部节点中的元素，左树中所有的元素都小于它，右树中的元素都大于等于它。
  • 每个叶子节点都存有相邻叶子节点的指针，叶子节点本身根据关键字的大小自小而大顺序链接
  • 父节点存储右子树第一个元素的索引

插入操作

插入 5, 10, 15, 20

插入25，此时元素数量大于4个，开始分裂

接着插入 26, 30，继续分裂

删除操作

因为叶子节点有指针存在，向兄弟节点借元素时，不需要通过父节点，可以直接通过兄弟节点移动即可，然后更新父节点的索引，如果兄弟节点没有多余的元素，就和兄弟节点合并并且删除父节点中的索引。

初始状态

删除10，此时兄弟节点大于2个，就向兄弟节点借一个元素，并且修改父节点中的索引

再删除5，合并兄弟节点，并且删除父节点中的索引

但是父节点并不是根节点，必须满足大于2个元素的要求，因此，跟兄弟节点合并，

B+ 树，单一节点存储的元素更多，查询的 IO 次数更少，适合作为数据库 SQL 的底层数据结构。

所有的查询都要找到叶子节点，查询性能稳定，所有的叶子节点形成了一个有序链表，便于查找。

索引

SQL 优化重中之重是优化索引
索引是一种帮助数据库获得高效查询效率的数据库对象，该数据库对象使用了特殊的数据结构，以 B 树和 Hash 树最为常见，MySQL 中索引默认使用的是 B 树。

索引好比字典的目录，在查询某个字的时候，先从目录中查找，看我们需要找的字在字典中具体的页数是多少，然后直接翻到对应的页码。

如果没有索引，会一行一行查询，直到符合条件就从磁盘中读取，执行多次IO操作。如果有索引，比如普通树。建立索引:

在对比第一个值之后，就只会查左边或者右边的树了。瞬间将 IO 次数大幅度降低。

如果对 age 列建立B树(一般指B+树)索引，遵循树数据结构的特点，对比当前节点，小的在左，大的在右。在B树数据结构中，数据全部存放在叶子节点上，无论查找什么数据只跟树的层数有关，一个3层结构的 B+Tree 能容纳上百万的数据，在上百万的数据中查询数据只需要找3次，效率极高。

InnoDB 的索引实现

执行计划

1
2
3
4
5
6
7
8
EXPLAIN SELECT * FROM user

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.00 sec)

explain 各列含义

列名 含义
id SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type SELECT 查询的类型.
table 查询的是哪个表
partitions 匹配的分区
type join 类型
possible_keys 此次查询中可能选用的索引
key 此次查询中确切使用到的索引
ref 哪个字段或常数与 key 一起被使用
rows 显示此查询一共扫描了多少行. 这个是一个估计值
filtered 表示此查询条件所过滤的数据的百分比
extra 额外的信息

select_type

  • SIMPLE 表示此查询不包含 UNION 查询或子查询
  • PRIMARY 表示次查询是最外层的查询
  • UNION 表示次查询时 UNION 的第二或随后的查询
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句，取决于外面的查询
  • UNION RESULT, UNION 的结果
  • SUBQUERY 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY 子查询中的第一个 SELECT，取决于外面的查询，即子查询依赖于外层查询的结果

id 值大的先执行。值一样从上往下执行。

type 这列最重要

显示了连接使用了哪种类别，有无使用索引，使用 Explain 命令分析性能瓶颈的关键项之一

结果值从好到坏：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref，否则就可能会出现性能问题

type 值 情况
const 根据主键索引或者唯一索引查询到的结果，select id,name from employee where id = 1
ref 使用非唯一性索引做查询，返回匹配的记录行，常见于多表查询中 select * from department d join employee e on d.id = e.dept.id
range 索引做范围查询，常见于 <、<=、>、>=、between 等操作
index 索引全查询，MySQL 遍历整个索引来查找匹配的行，select age from employee where age >= 20

注意：SQL 优化的目标之一是吧 type 优化在 ref 到 index 之间，该值没有优化的情况下一般都是 all

SQL 优化实现

  1. 较为频繁的座位查询条件的字段，应该创建索引，如登录操作
  2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，作为索引的列，如果不能有效区分数据，这个列就不适合作为索引列。比如(性别、状态不多的状态列等)
  3. 更新非常频繁的字段不适合，索引需要维护
  4. 不会出现在 where 子句中的字段不应该创建索引

join原则

  1. 数据量小的表写在 join 左边，数据量大的表写在 join 的右边，MySQL 中的 join 都是通过 Nested Loop Join 实现，可以理解为嵌套循环，应该数据量少的表作为外层循环，数据量大的表作为内层循环，然后合并结果。
  2. 优先优化 Nested Loop 的内层循环
  3. 保证 join 语句中被作为连接条件的字段已经建立了 索引
  4. 扩大缓冲区的大小，容纳更大的查询数据(在配置文件中进行配置)

需求

  • 优化根据姓名查询员工信息
1
EXPLAIN SELECT id,name,email,age FROM employee WHERE name = ''

type = all、Extra = where，
在name列上添加普通索引后，type = ref、Extra = Using index condition
使用条件列为索引列。

  • 优化根据姓名和年龄查询员工信息

在 age 和 name 上建立复合索引 Extra = User index codition。如果只有一个用了索引， Extra 还会显示 where

  • 优化查询员工姓名、年龄以及所在部门名称的信息
1
EXPLAIN SELECT e.id,e.name,e.email,d.name FROM employee e JOIN department d ON e.dept_id = d.id

执行计划都是 all，先查员工表，再查部门表，
优化外键索引。添加 dept_id 单值索引，之后 type = ref。

  • 优化查询名叫 admin 员工姓名、年龄以及所在部门名称的信息
1
EXPLAIN SELECT e.id,e.name,e.email,d.name FROM employee e JOIN department d ON e.dept_id = d.id where e.name = 'admin'

没有先查询部门表，是因为有了过滤条件，拿到员工表的数据，对 name 过滤后，数据量就比 department 表中的数据少了，因此会先查员工表的数据。

  • 优化查询名叫 xx 的员工所拥有的的权限信息
1
2
3
4
5
EXPLAIN SELECT DISTINCT p.id,p.name,p.expression FROM permission p 
JOIN role_permission rp ON p.id = rp.permission_id 
JOIN employee_role er ON rp.role_id = er.role_id
JOIN employee e ON e.id = er.employee_id
WHERE e.name = 'xx'

出现了多表的问题。主键都有主键索引，所以我们需要对外键单独建立索引。优化后：在中间表的外键列分别建立单值索引。

避免索引失效

  1. where 语句中 索引列参与算数计算，该索引失效
  2. where 语句中 索引列参与函数运算， 该索引失效
  3. where 语句中 使用 in 运算符有时会让索引失效(根据当前运算量评估)
  4. where 语句中 做不等于(!=, <>)运算， 该索引失效
  5. where 语句中 发生类型转换，该索引失效
  6. where 语句中 模糊查询时以 % 开头，该索引失效
  7. 在符合索引的使用时跟声明时顺序不一致或者中间列有缺失，该索引失效
    • 如声明了 (a, b, c) 的复合索引，但是在用的时候 中间有列缺失 where a = xx and c = xx 缺失了 b,所以无法使用该复合索引，只要不是缺失中间列，其他情况索引均有效

不合理的需求

  • (关系型数据库年代)实时更新，点赞数量、评论等都会挂，但是现在技术突破了，使用非关系型数据库可以实现

主从同步

将服务器责任分离，一些服务器只做查询，一些服务器只做增删改，因为增删改对索引是有很大影响的。此时，使用某种机制将增删改服务器中的数据同步到查询服务器就会极大的优化性能。

主从同步是只，数据可以从一个 MySQL 数据库服务器主节点复制到一个或多个从节点，MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

应用场景

读写分离、架构扩展

随着系统中业务访问量增大，如果是单机部署数据库，会导致 I/O 访问频率过高，有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘 I/O 访问的频率，提高单个机器的 I/O 性能。

原理

MySQL 主从赋值涉及到三个线程，一个运行在主节点(log dump thread)，其余两个(I/O thread，SQL thread) 运行在从节点。

log dum thread

当从节点连接主节点时，主节点会创建一个 log dump 线程，用于发送 bin-log 的内容，读取 bin-log 中的操作时，此线程会对主节点上的 bin-log 加锁，当读取完成，锁会被释放

I/O thread

当从节点上执行 start slave 命令，从节点会创建一个 I/O 线程用来连接主线程，请求主库中更新的 bin-log。I/O 线程接收到主节点 bin log dump 进程发来的更新后，保存在本地的 relay-log 中

SQL thread

SQL 线程负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。
对于每一个主从连接，都需要三个进程完成，当主节点有多个从节点时，主节点会为每一个当前连接的从节点建立一个 bin log dump 进程，每个从节点都有自己的 I/O 进程，SQL进程。从节点用两个线程将主库拉取更新和执行分成独立的任务。这样在执行同步数据任务的时候，不会降低读操作的性能。如果在 SQL 进程执行之前，从节点服务停止，I/O进程已经从主节点拉取到了最新的变更保存到本地的 relay 日志中，当服务器再次起来，就完成数据的同步。

要实施复制，必须打开 Master 端的 binary log 功能，否则无法实现，因为整个复制过程实际上是 Slave 从 Master 端获取日志然后再在自己身上完全顺序的执行日志中所记录的各种操作

复制过程如下：

  1. 从节点的 I/O 进程连接主节点，请求从指定日志文件的指定位置之后的日志内容
  2. 主节点通过 I/O 进程根据请求信息读取指定日志之后的信息，返回从节点。返回信息中除了日志包含的信息外，还包括本次返回的信息的 bin-log file 和 bin log position
  3. 从节点 I/O 进程 接收到内容后，将接收到的日志内容更新到本机的 relay log 中，并将读取到的 bin log 文件名和位置保存到 master-info 文件中，以便下一次读取的时候能够清楚的告知 Master 具体的读取位置。
  4. Slave 的 SQL 线程检测到 relay-log 中新增加了内容后，会将 relay-log 的内容解析成在主节点上执行过的操作，并在本数据库中执行。

MySQL 主从复制模式

异步模式

MySQL async-mode，异步模式下，主节点不会主动 push bin log 到从节点，这样有可能导致 failover 的情况下，也许从节点没有即时地将最新的 bin log 同步到本地

半同步模式

MySQL semi-sync 这种模式下主节点只需要接受到其中一台从节点的返回信息，就会 commit，否则需要等待直到超过时间然后切换成异步模式再提交，这样做的目的可以使主从数据库的数据延迟缩小，提高数据安全性。事务提交后，bin log 至少传入到了一个从节点上，不能确保从节点将此事务更新到 db 中，性能上会有一定降低，响应时间会变长：

从 MySQL 5.5 开始集成，需要 master 和 salve 安装插件开启半同步模式

全同步模式

全同步模式是指主节点和从节点全部执行了 commit 并确认才会向客户端返回成功

实现

准备工作：先让 Master 和 Slave 都拥有相同的数据库，并且数据库中的表数据一致。

  • 在Mster 配置文件中 mysqlId 部分做以下配置，开启 binary-log

读写分离

文章作者: Ammar
文章链接: http://lizhaoloveit.cn/2019/09/18/SQL%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Ammar's Blog！
数据库
打赏
  • 微信
  • 支付宝
评论
繁