把握高性能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 公布！