掌握高性能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下持续关注喔~

有什么问题可以在评论区交流，如果觉得菜菜写的不错，可以点赞、关注、收藏支持一下~

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