关于后端:掌握高性能SQL的34个秘诀🚀多维度优化与全方位指南

把握高性能 SQL 的 34 个秘诀🚀多维度优化与全方位指南

本篇文章从数据库表结构设计、索引、应用等多个维度总结出高性能 SQL 的 34 个秘诀，助你轻松把握高性能 SQL

表结构设计

字段类型越小越好

满足业务需要的同时字段类型越小越好

字段类型越小代表着记录占用空间可能就越小，页中存在的记录就可能越多，雷同 IO 次数加载的数据就可能更多

字段越小建设索引时消耗的空间就越小，如果该字段是主键那么它还会在二级索引上存储，因而主键也是越小越好

数字类型的抉择

数字类型包含 整形、浮点型、定点数类型

在不同的场景下能够抉择不同的类型

整形

整形通常是 tinyint ~ bigint

依据越小越好的准则，对于 存储枚举类型的字段应用 tinyint进行存储（0-x），而不用应用字符串进行存储

int(1) 和 int(10) 占用的空间无区别 ，只是 int(10) 在数据不满 10 位时进行补零

善用无符号 UNSIGNED 能够进步一倍的容量，比方一些不须要正数的场景（主键、人的年龄 tinyint unsigned 255 够用）

浮点型

浮点型罕用于存储有小数局部的数据

其中包含 float、double 类型

留神 应用浮点数类型时可能产生精度失落，如果不想失落精度能够抉择定点数类型

定点数类型

decimal 罕用于 存储有小数、须要计算且不能产生精度失落 的数据

字符类型的抉择

罕用的字符类型有 char 和 varchar

char 存储固定字符，当存储字符长度未满时应用空格填充，因而它无奈存储开端空格，在批改时它可能在原记录上进行批改

varchar 相当于 char 空间换工夫的版本，它是可变长字段会多应用 1 - 2 个字节记录可变长长度

varchar(255)前 1 个字节，255 后 2 个字节，但也不是长度不超过 255 就全副都应用 255，在某些存储引擎下会依据长度间接调配空间（如 memory），应用长期表默认应用 memory，因而在长期表排序时可能会导致占用空间太多

varchar 在面对频繁的批改时，还可能造成重建记录、页决裂等问题

固定长度、频繁的批改能够抉择 char

不定长、开端要存储空格时能够抉择 varchar，varchar 长度也要尽量小

留神 存表情应用 utf8mb4 字符编码

在某些场景下，整形替换字符存储会更省空间，也能够思考整形 比方存储 IP

具体内容感兴趣的同学能够查看这篇文章：千万数据下 varchar 和 char 性能居然相差 30%🚀

工夫类型的抉择

依据越小越好准则，只须要年、日期、工夫时抉择 year、date、time

须要具体日期时能够抉择 datetime 和工夫戳的形式

datetime 固定工夫、无时区、可视化较好

timestamp 工夫戳，有时区（依据服务端时区）、有工夫范畴限度、应用零碎时区并发下性能没那么好、可视化不好

应用整形存储工夫戳，性能好，能够自在转换时区，可视化不好

不思考时区、可视化要好大部分场景下能够应用 datetime

思考时区（须要自在转换时区）、谋求性能、不重视可视化能够抉择整形存储工夫戳（无符号 int 目前够用）

具体内容感兴趣的同学能够查看这篇文章：工夫类型该如何抉择？千万数据下性能晋升 10%~30%🚀

文本、文件类型的抉择

文本相干能够抉择 TEXT 相干类型，应用时最好 与罕用列进行垂直拆分，防止内容太多影响其余列的查问

文件相干能够存储到文件服务器后，在数据库中应用字符类型（varchar）进行存储文件所在地址

如果肯定要存则应用 BLOB 相干类型存储二进制数据

尽量满足主键递增

主键最好思考是递增的，因为聚簇索引须要保障主键值的有序

当主键递增时，只须要在开端减少记录即可

当入库的主键值无序时，可能会导致页决裂，须要保护有序性的开销

单机下能够应用主键自增，分布式下能够应用全局自增的算法（雪花算法等）

思考突破范式减少冗余

个别表构造的设计是遵循三范式的

在某些场景下，能够给一些 须要关联的表减少冗余，从而防止联表查问

比方一条记录是由某个设施生成的，该记录必定须要保留字段去关联设施，

需要是晓得该记录由哪个设施生成的即可，因而查问时只须要关联查问设施名称，而设施名称又不会常常扭转

对于谋求性能的场景，能够将设施名称冗余存储在记录上，从而防止联表查问

计算量太大思考两头表

对于须要大量计算的场景（比方统计数据、每日排行榜等），每次查问都通过大量计算来统计数据是不事实的

通过减少两头表的形式先进行统计，后续查问时间接查两头表

比方定时工作统计每天数据量、每日排行，计算后，将后果（不同类型的数据量、排行榜 TOP100）记录在两头表上，后续有申请则间接查两头表

索引

为罕用于查问的列建设索引

索引带来的益处是在大数据量下可能 疾速检索 到满足查问条件的记录

索引会依据抉择的列构建成一颗索引列有序的 B + 树，比方依据 age，student_name 建设索引

索引 (age，student_name) 中只存储索引列 (age、student_name) 和主键(id)

并且索引中须要把 age、student_name、id 保护成有序

（整体上 age 有序，age 相等时 student_name 有序，student_name 相等时 id 有序）

列有较多 where 条件查问的语句时思考为其建设索引

为常要排序（order by、group by）列创立索引

索引会保护列的有序性，为 order by 的列建设索引时，在索引上列自身就是放弃有序的，不会再应用长期表进行排序

group by 也会进行排序（应用索引的益处同上），在其根底上还会进行去重

如果无奈创立索引会应用 sort buffer 进行排序，能够思考调大 sort buffer 加快速度

如果数据量太大的排序还会借助磁盘辅助排序，这种场景下最好还是建设索引

对排序感兴趣的同学能够查看这篇文章：怎么解决排序⭐️如何优化须要排序的查问？

思考为联表查问中被驱动表关联列适当建设索引

在联表查问中关联的表越多，工夫复杂度会呈指数型增长

其中每拜访一次驱动表，就可能拜访屡次被驱动表，须要适当为被驱动表关联列建设索引，放慢查问被驱动表的速度

SELECT
    s1.*,
    s2.seat_code 
FROM
    student s1
    left JOIN seat s2 ON s1.id = s2.student_id 
WHERE s1.student_name = 'caicai'

如这段 SQL 中，s1 应用左连贯为驱动表，s2 为被驱动表

s1 应用 (student_name) 索引，s2 临时没索引

能够思考为 s2 的 student_id 建设索引，因为只查问 s2 的 seat_code，也能够思考建设 (student_id,seat_code) 联结索引，应用笼罩索引防止查 s2 时回表再查 seat_code

对连表查问感兴趣的同学能够查看这篇文章：连贯的原理⭐️4 种优化连贯的伎俩性能晋升 240%🚀

思考为字符串长度太长、结尾可能辨别的列建设前缀索引

为太长的字符串列间接建设索引时会导致占用空间太大

当列中存储的值，后面局部为区别度较高的值时，能够思考为其建设前缀索引

例如某产品编码长度 20，其中前面 15 个字符重复性很高，前 5 个字符重复性低区分度高，就能够思考为前 5 个字符建设前缀索引

须要留神的是，前缀索引只存储该列前缀局部的值，如果要获取列的残缺信息就要进行回表

列中反复值太多，不倡议建设索引

当列中反复值太多时，它在查问时的区分度不够

其次在应用该索引时（反复值太多 cardinality 太低），如果要回表 MySQL 会认为回表开销太大（反复值多、回表数量多），从而导致它不偏差应用该索引

（回表开销：回表须要查问聚簇索引，因为二级索引中的主键值不肯定有序，因而回表时可能产生随机 IO）

业务惟一要加惟一索引

业务上有唯一性的要求时要加惟一索引

惟一索引的特点是记录惟一，在进行写操作时须要保障记录唯一性，不能应用 change buffer 等优化，在频繁写的场景下性能会比非惟一二级索引略差

（change buffer：当索引页不在缓冲池时，记录下本次写操作的内容，等后续读到该记录时，再将内容合并加载到缓冲池，防止写的随机 IO)

但在查问时惟一索引等值查问会比非惟一索引更快（因为它不容许反复值，而非惟一索引存在反复值）

在业务层通过先读再新增的形式保障惟一时，在并发场景下还是会呈现反复值（除非读加锁，然而加锁又会影响性能 ….）

不能因为惟一索引无奈应用 change buffer 的优化就不应用惟一索引

防止创立过多索引

创立索引是须要思考老本的，并不是索引越多越好

1. 索引须要 占用空间
2. 在进行写（增 / 删 / 改）操作时，还要 保护索引的有序性
3. 在进行查问时优化器还要 基于应用不同的索引对老本进行估算

防止冗余索引

当存在 (name)、（age）、(name,age) 三个索引时，(name)就成为了冗余索引

因为应用 (name) 索引的益处 (查问条件过滤、有序)，应用(name,age) 也能够达到

须要留神的是，如果查问 SQL 中没有 age 独自查问的（where age = 18），都是基于先查 name 再查问 age 的（where name >= 'caicai' and age >= 18），那么 age 也相当于冗余索引，因为这种场景下应用 (name,age) 就足够

留神左含糊匹配

字符串的二级索引是依据该列字符排序规定进行排序

当应用左含糊匹配 like '%xx' 时，因为起始字符不确定导致不便在二级索引中进行检索

对于这种场景，如果数据量小思考建设全文索引进行检索，如果数据量大思考应用其余长于全文检索的中间件如 ES 等（MySQL 全文索引耗内存）

留神最左匹配准则

当应用联结索引时，须要前一个索引列等值的状况下，后一个索引列才会有序

比方 (a,b,c) 中，当 a 相等时 b 才有序，当 b 相等时 c 才有序

where b<=9 时无奈应用联结索引，因为 b 不肯定是有序的，只有当 a 相等时 b 才有序

where a>=1 and b<=9中，能够应用上索引中 a、b 两个列

where a>=1 and c<=9中，只能应用上索引中的 a，因为 b 没有查问条件导致 c 不肯定有序，于是 c 无奈应用索引

然而在 8.0 高版本中，推出索引跳跃扫描的优化

在 where a>=1 and c<=9 中无奈应用 c 的起因是 c 不是有序，想要 c 有序就要让 b 相等，于是索引跳跃扫描在这种场景下，将遍历 a>=1 中有序的 b（因为 b 可能反复于是会对 b 去重）在此基础上 c 就是有序的，就可能应用上索引，最初将每个遍历的 b 中满足 c <= 9 的记录进行合并，从而失去最终后果

尽管有索引跳跃扫描的优化，但开销还是大的，须要优化

留神表达式或隐式函数

索引列不要应用表达式，比方：where age + 2 = 10，存储引擎层应用 age 索引时，不意识 age + 2 就会导致索引生效

同理，索引列也不能应用函数，CAST(age AS CHAR) = '8'也会导致索引生效

须要留神：有时 容易隐式给索引列加函数导致索引生效

code = 10 code 为字符串，字符串会隐式应用函数向数字转换 CAST(code AS UNSIGNED) = 10 从而导致索引生效

对索引生效感兴趣的同学能够查看这篇文章：完蛋！😱 我被 MySQL 索引生效突围了！

留神回表

当应用二级索引时，如果应用的 查问条件不够有区别度 is null、is not null、or（NULL 默认状况下被认为反复值），又或者该 反复值太多 （cardinality 太低）， 都会导致 MySQL 认为要回表的记录太多，从而不偏差应用索引，导致索引生效

留神优化器可能用错索引

优化器会估算计算每个索引的老本，当扫描数据量较大并且更新数据太频繁时，会影响计算的老本，从而导致优化器应用错索引

这种状况下能够在闲暇时手动更新统计 analyze table

或者强制应用索引 force index

应用

防止 select *

select * 不便书写 SQL，易于偷懒

尽管平时的开发中也会用到，然而要晓得它会带来开销:

1. 占用网络带宽（读取不必要的列通过网络返回给客户端，数据量大的状况下是一笔不小的开销）
2. 无奈应用笼罩索引，应用二级索引时会回表（如果须要的列正好都在二级索引上，那么就能够应用笼罩索引不必回表）
3. 联表查问应用 join buffer 时会占用 join buffer 的空间（join buffer 是联表查问被驱动表无奈应用索引时的优化，占用其空间会导致联表性能降落）

罕用 explain

每次在书写业务的 SQL 时能够应用 explain 查看执行打算

依据业务需要、执行打算判断该 SQL 是否满足以后场景的性能要求

explain 中须要留神的几局部：

1. type 避免出现全表扫描 ALL，最好应用 const、ref、range 等
2. possible_keys 可能用到的索引 和 key 理论用到的索引，留神查看优化器是否选错索引
3. 联表查问时留神查看 key_len 应用索引长度，防止局部索引列未应用到
4. 留神附加信息 extra 中的 排序、长期表等

查问时少用 is null、is not null、or、!=…

null 默认被认为反复值，is null、is not null、or、!= 会被认为反复值太多

当反复值太多（回表开销大）MySQL 会不偏差应用索引，导致索引生效

留神联表性能

留神 联表查问的工夫复杂度是呈指数模式增长的，联表越多性能越差，然而有的 B 端又必须进行联表查问

提供以下几点计划优化联表：

1. 适当为被驱动表关联列建设索引（如果应用索引后随机 IO 关联被驱动表是瓶颈，思考开启 BKA）
2. 无奈建设索引的状况会应用 join buffer 优化，尝试 调大 join buffer 空间或缩小 select 查问的列
3. 应用 小表驱动大表，能用内连贯就用内连贯（让 MySQL 抉择驱动表）
4. 不常常变动的列 做冗余，防止联表

统计全副数量尽量应用 count(*)

在统计数量时都会应用 count 函数

count(主键 /1/*)都会基于空间最小的二级索引进行统计（统计快）

全局数量统计时尽量应用 count(主键)/count(1)/count(*)等，不要应用 count(二级索引列)，可能当初该列的索引的确是空间最小的，但前面还可能建设比它空间更小的二级索引（除非是指定统计该列行数）

count(*)是定义的数据库规范统计行数的语法，尽管几个写法应用起来都差不多，但它会标准些

留神优化深分页

深分页问题是因为分页偏移量太大导致的问题

select * from student where age = 18 limit 5000,10 应用二级索引 age 偏移量太多（要回表的数据量太大）导致索引生效

能够应用以下六种形式优化深分页

1. 业务需要沟通，避免出现深分页
2. 应用笼罩索引优化
3. 应用游标分页
4. 应用子查问
5. 应用 in、子查问
6. 应用内连贯、子查问

对深分页感兴趣的同学能够查看这篇文章：深分页怎么导致索引生效了？提供 6 种优化的计划！

读写善用 limit

查问时携带 limit 能够更快的返回后果，防止额定的查问

比方我只须要查问一条记录时limit 1（不是指 limit 10000,1 这种深分页哈）

在写操作 (批改 / 删除) 时携带 limit 会限度写的行数，防止误操作数据

数据量小且要查屡次思考冗余查问

对于一些数据量小，然而又要屡次查问的场景，能够思考 Java 服务 先冗余查问再进行解决，防止屡次查问的网络 IO 开销

比方一些权限的树级目录，无论是通过队列来广度优先搜寻还是递归来深度优先搜寻，都须要屡次查库

频繁写思考批处理

客户端频繁的进行单次批改 / 删除 / 新增的操作 不仅有网络 IO 开销还耗损 MySQL 服务端资源、无奈应用批处理优化

这种场景下调整为批量解决能够节约资源增大性能

比方一些异步日志记录须要入库，但又会频繁触发，能够思考改为异步的批量入库

须要留神 如果批处理操作中的数量很多，思考分批解决，每批处理一部分，防止成为长事务

避免出现长事务

在应用 spring 的申明式事务时，用的很爽但略微不留神就可能导致长事务

比方一些没必要存在事务中的读操作

或者在同一个事务中，先进行写操作而后又去读数据（一顿操作后才提交事务），这可能导致写操作获取的行锁因为后续的读操作拉长事务导致获取锁的工夫变长

又或者一些读大量数据、写大量数据的操作，能够将整个长事务拆分为多个小事务进行解决

思考事务中写操作执行程序

平台上有 1W 积分，用户支付积分时，是先对平台的积分进行扣减，还是先对用户持有积分进行减少呢？

在对于事务中写操作的执行程序，应该 让共享、竞争更大的资源靠后执行 （提交事务前）， 尽可能的缩短它持有资源的工夫

应该把平台扣减积分放在提交事务前，因为平台积分相当于共享资源，大家都能够支付扣减

思考调整事务隔离级别

MySQL 默认的事务隔离级别为 RR（可反复读），在该隔离级别下可能避免脏读、不可反复读、大部分幻读

但加的行锁和持有工夫会比 RC（读已提交）级别下要多和更久

因而当业务只须要满足避免脏读的状况下能够 调整隔离级别为 RC 增大并发性能

具体加锁规定后续文章再进行探讨

留神调整架构

当业务上应用缓存、异步等多种优化伎俩后，对于一些须要实时读写的操作还是要走 DB，而此时 DB 面对大量这种操作可能产生瓶颈

当 DB 遇到瓶颈时，咱们须要剖析分明瓶颈并布局 DB 的架构，比方瓶颈是因为并发量大连接池不够？还是数据量太多查问太慢？

能够先将架构布局为读写拆散架构，从节点分摊主节点的压力

当读写拆散架构仍旧无奈满足业务时思考分库分表（提前剖析好瓶颈再布局拆分策略）

罕用的伎俩是：并发量大分库，数据量大分表，而分库分表又会带来一系列须要解决的问题，如：分布式事务，如何路由、联表、聚合等

最初（不要白嫖，一键三连求求拉~）

本篇文章被支出专栏 MySQL 进阶之路，感兴趣的同学能够继续关注喔

本篇文章笔记以及案例被支出 gitee-StudyJava、github-StudyJava 感兴趣的同学能够 stat 下继续关注喔~

有什么问题能够在评论区交换，如果感觉菜菜写的不错，能够点赞、关注、珍藏反对一下~

关注菜菜，分享更多干货，公众号：菜菜的后端私房菜

本文由博客一文多发平台 OpenWrite 公布！