SQL调优

外键和级联

• 好处

  • 1. 保证了数据库数据的一致性和完整性；
    2. 级联操作方便，减轻了程序代码量；

• 坏处

  • 性能

    • 每次对数据进行DELETE或UPDATE操作都必须考虑外键约束 数据库都会判断当前操作是否违反数据完整性，性能下降。

  • 锁竞争

    • 使用外键，外键关联的数据查询要去另一张表，获取额外的锁，容易造成死锁。

  • 分库分表中外键难以跨越不同数据库来建立关系

• 外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度

索引失效,索引区分度不高

其实就是答索引的使用原则

• 正确使用索引

  • 为合适字段创建索引

    • ​​

      where,group by,order by

  • 为频繁更新的字段慎重创建索引

  • 尽可能考虑联合索引而不是单列索引

  • 考虑在字符串类型的字段上使用前缀索引替代普通索引

  • 避免索引失效

    • ​​

多表join

• 减少多表join

• 如果非要连表尽量使用innerjoin ，不要使用用left join right join，如必须使用 一定要以小表为驱动

  1. 结果集更小
     ​INNER JOIN​ 只返回两个表中完全匹配的行，过滤掉不匹配的数据，减少了结果集的大小，降低内存和网络传输开销。

  2. 小表驱动时间复杂度更加低

     优化器自动选择（仅限INNER JOIN​）

     • 对于INNER JOIN​，优化器会根据统计信息自动选择驱动表，无需手动干预。

• join 的效率比较低，主要原因是因为其使用嵌套循环（Nested Loop）来实现关联查询，三种嵌套循环不同的实现效率都不是很高：

  • Simple Nested-Loop Join ：没有进行过优化，直接使用笛卡尔积实现 join，逐行遍历/全表扫描，效率最低。

  • Block Nested-Loop Join(本质是因为被驱动表没有加索引) ：利用 JOIN BUFFER(一个内存缓冲池) 进行优化，性能受到 JOIN BUFFER 大小的影响，相比于 Simple Nested-Loop Join 性能有所提升。不过，如果两个表的数据过大的话，无论如何优化，Block Nested-Loop Join 对性能的提升都非常有限。---减少磁盘读取---但性能提升有限,主要受制于 Join Buffer 的大小

  • Index Nested-Loop Join ：在必要的字段上增加索引（被驱动表），使 join 的过程中可以使用到这个索引，这样可以让 Block Nested-Loop Join 转换为 Index Nested-Loop Join，性能得到进一步提升。

    • 假设都有索引的情况下为什么小表驱动大表

      ​​

• hash join

  • ​​

    ​​

    ​​

    ​​

    ​​

• 实际业务场景避免多表 join 常见的做法有两种：

  1. 单表查询后在内存中自己做关联 ：对数据库做单表查询，再根据查询结果进行二次查询，以此类推，最后再进行关联。
  2. 数据冗余，把一些重要的数据在表中做冗余，尽可能地避免关联查询。很笨的一种做法，表结构比较稳定的情况下才会考虑这种做法。进行冗余设计之前，思考一下自己的表结构设计的是否有问题。

深度分页（查询偏移量过大）

查询字段太多

• 避免使用select*

聚合查询

如果是聚合查询，尽量用union all代替union ，union会多一次过滤，效率比较低；

主从复制读写分离(不是横向扩展,因为有副本)

单表数据量大--海量数据存储策略(分区分库分表)--横向扩展

在数据量及访问压力不是特 别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表 方案。

微服务项目中每个微服务对应一个数据库(根据业务进行拆分),这个是垂直拆分

• 分区分库分表

  • qps--缓存(但只能解决读请求过高的问题);数据量--索引(不能完全解决)

  • 分库降低qps(数据库连接),分表降低单表数据量(存储和查询到达性能瓶颈)

    • 水平分库--降低qps和单表数据量
    • 水平分表--降低单表数据量
    • 垂直分表--提高查询性能
    • 垂直分库--降低qps

  • 优先考虑分区,然后才是分表

    • 补充--对于数据量大可以先进行

      • 数据库优化--索引,数据冗余,sql语句
      • 缓存
      • 数据归档
      • 分区

    • ​​

      ​​

      ​​

      ​​

    • MyIsam和InnoDB区别

  • 分表字段的选择不当可能会导致数据倾斜问题

    • 尽量避免有聚焦性的字段做分表字段,如订单表,尽量选择买家ID

    • ​​

    • 复合分片键/hash分片+盐值/二次哈希分表

      ​​

      ​​

  • 水平分表/分库算法

    • ​​

    • • 本质就是取模,根据关键字还有范围

        • 一种分表方式是通过对id取模进行分表，这种要求递增就好，不要求严格自增，因为取模后数据会被分散到多个分表中，就算id是严格自增的，在分散之后，都只能保证每个分表里id是递增的。

          • 但是取模(哈希)带来了一个问题

            扩容的数据迁移问题--一致性哈希

        • 另一种分表方式是根据id的范围进行分表（分片），它会划出一定的范围，比如以2kw为一个分表的大小，那02kw就放在这张分表中，2kw4kw放在另一张分表中，数据不断增加，分表也可以不断增加，非常适合动态扩容，但它要求id自增，如果id递增，数据则会出现大量空洞。举个例子，比如第一次分配id=2，第二次分配id=2kw，这时候第一张表的范围就被打满了，后面再分配一个id，比如是3kw，就只能存到2kw~4kw（第二张）的分表中。那我在0~2kw这个范围的分表，也就存了两条数据，这太浪费了。

  • 分表后全局ID如何生成--分布式ID解决方案

    • 分布式ID介绍

      • 分布式ID是分布式系统中生成的唯一标识符

      • 分布式ID，也称为全局ID，是在分布式系统中生成的唯一标识符。 它必须满足以下条件：全局唯一、高性能、高可用、易于接入以及趋势递增（非严格要求）。

      • 如何设计一个分布式ID

        • 安全

        • 递增(加快数据库插入性能)

        • 高可用

        • 高性能

        • 独立部署

        • ID有明确业务含义

        • 基因法--利于分库分表

          ​​

        • 不占内存

        • 时间回拨问题

        • 机器依赖问题

    • 分布式ID应用--设计一个订单生成服务
    • ​​

    解决性能瓶颈

    • 数据库号段模式--每次从数据库取id时一次取出一批放在缓存或者内存里面，需要用到的时候，直接从内存里面拿

      思考一下这个自增短链 id 在机器上该怎么实现呢， 可以用 redis, 不过更简单的方案是用 AtomicLong，单机上性能不错，也保证了并发的安全性，当然如果并发量很大，AtomicLong 的表现就不太行了，可以考虑用 LongAdder，在高并发下表现更加优秀。

    但即便这样还存在单点故障问题

    • 这里的方案是集群(同时解决性能和单点故障问题)

    但集群会带来id重复的问题

    • 所以就是下面的普通集群(普通集群还要解决id不重复问题--配合上步长做自增(因为普通集群各个节点间信息不共享))/ 主从集群

      ​​

      设计一个专门的发号表，每插入一条记录，为短链 id 预留 （主键 id * 1000 - 999） 到 (主键 id * 1000) 的号段，如下

      发号表：url_sender_num

      如图示：tmp_start_num 代表短链的起始 id，tmp_end_num 代表短链的终止 id。

      当长链转短链的请求打到某台机器时，先看这台机器是否分配了短链号段，未分配就往发号表插入一条记录，则这台机器将为短链分配范围在 tmp_start_num 到 tmp_end_num 之间的 id。从 tmp_start_num 开始分配，一直分配到 tmp_end_num，如果发号 id 达到了 tmp_end_num，说明这个区间段的 id 已经分配完了，则再往发号表插入一条记录就又获取了一个发号 id 区间。

      ​​

    但还是存在ID 没有具体业务含义、安全问题（比如根据订单 ID 的递增规律就能推算出每天的订单量，商业机密啊！ ）--就只能用算法来解决

    • 以及NoSQL-redis(和上面的数据库解决方法是并列的

      通过 Redis 的 incr​ 命令即可实现对 id 原子顺序递增,然后通过redis集群解决单点故障问题

    但redis存在数据丢失问题

    • 雪花算法

      如果你想要使用 Snowflake 算法的话，一般不需要你自己再造轮子。有很多基于 Snowflake 算法的开源实现比如美团 的 Leaf、百度的 UidGenerator（后面会提到），并且这些开源实现对原有的 Snowflake 算法进行了优化，性能更优秀，还解决了 Snowflake 算法的时间回拨问题和依赖机器 ID 的问题。

      • 雪花算法

        • 雪花算法通过64位有特殊含义的数字来组成id。

          ​
          雪花算法

          首先第0位不用。

          接下来的41位是时间戳。精度是毫秒，这个大小大概能表示个69年​左右，因为时间戳随着时间流逝肯定是越来越大的，所以这部分决定了生成的id肯定是越来越大的。

          再接下来的10位是指产生这些雪花算法的工作机器id，这样就可以让每个机器产生的id都具有相应的标识。

          再接下来的12位，序列号（防止一毫秒(最小单位)(时间戳的最小单位)同时生成多个ID），就是指这个工作机器里生成的递增数字。

          可以看出，只要处于同一毫秒内，所有的雪花算法id的前42位的值都是一样的，因此在这一毫秒内，能产生的id数量就是 2的10次方✖️2的12次方​，大概400w​，肯定是够用了，甚至有点多了。

          • 雪花算法它算出的数字动不动就比上次的数字多个几百几万的，也就是它生成的id是趋势递增的，并不是严格+1自增的，也就是说它并不太适合于根据范围来分表的场景。这是个非常疼的问题。

            还有个小问题是，那10位工作机器id，我每次扩容一个工作机器，这个机器怎么知道自己的id是多少呢？是不是得从某个地方读过来。

            那有没有一种生成id生成方案，既能让分库分表能做到很好的支持动态扩容，又能像雪花算法那样并不依赖redis这样的第三方服务。

      • 优缺点

        • 优点：高性能(不依赖数据库,完全在内存中生成),高吞吐(生成速度比较快,每秒数百万)、生成的 ID 有序递增、比较灵活（可以对 Snowflake 算法进行简单的改造比如加入业务 ID）

        • 缺点：需要解决重复 ID 问题（ID 生成依赖时间，在获取时间的时候，可能会出现时间回拨的问题，也就是服务器上的时间突然倒退到之前的时间，进而导致会产生重复 ID）、依赖机器 ID 对分布式环境不友好（当需要自动启停或增减机器时，固定的机器 ID 可能不够灵活）。

          • 还有一个是在集群中没法保证全局顺序递增,因为每个节点都是趋势递增,得用zk协调

            这里的趋势递增不利于分库分表（范围的扩展分表(趋势递增数字跳转大),B+树的页分裂(不是全局递增)）

          • 读取机器id需要去其他地方读--例如redis这样的第三方服务

      • Leaf-Snowflake--美团开源的分布式ID生成服务，通过ZK协调节点ID，解决时钟回拨问题。

    • UUIDV7

      ​​

      ​​

      时间戳,mac地址,随机数(所以无序（不利于范围查询,大量页分裂问题）)等,uuidv7利用时间戳实现局部有序

      • 优缺点

        • 优点：生成速度比较快、简单易用
        • 缺点：存储消耗空间大（32 个字符串，128 位）、 不安全（基于 MAC 地址生成 UUID 的算法会造成 MAC 地址泄露)、无序（非自增）、没有具体业务含义、需要解决重复 ID 问题（当机器时间不对的情况下，可能导致会产生重复 ID）

      • UUID改造

        • 适合分库分表的uuid算法

          我们可以参考雪花算法的实现，设计成下面这样。注意下面的每一位，都是十进制，而不是二进制。

          ​
          适合分库分表的uuid算法

          开头的12位依然是时间，但并不是时间戳，雪花算法的时间戳精确到毫秒，我们用不上这么细，我们改为yyMMddHHmmss​，注意开头的yy是两位，也就是这个方案能保证到2099年之前，id都不会重复，能用到重复，那也是真·百年企业。同样由于最前面是时间，随着时间流逝，也能保证id趋势递增。

          接下来的10位，用十进制的方式表示工作机器的ip，就可以把12位的ip转为10位的数字，它可以保证全局唯一，只要服务起来了，也就知道自己的ip是多少了，不需要像雪花算法那样从别的地方去读取worker id了，又是一个小细节。

          在接下来的6位，就用于生成序列号，它能支持每秒钟生成100w个id。

          最后的4位，也是这个id算法最妙的部分。它前2位代表分库id，后2位代表分表id。也就是支持一共100*100=1w​张分表。

          举个例子，假设我只用了1个分库，当我一开始只有3张分表的情况下，那我可以通过配置，要求生成的uuid最后面的2位，取值只能是[0,1,2]，分别对应三个表。这样我生成出来的id，就能非常均匀的落到三个分表中，这还顺带解决了单个分表热点写入的问题。

          如果随着业务不断发展，需要新加入两张新的表(3和4)，同时第0张表有点满了，不希望再被写了，那就将配置改为[1,2,3,4]，这样生成的id就不会再插入到对应的0表中。同时还可以加入生成id的概率和权重来调整哪个分表落更多数据。

          • 有了这个新的uuid方案，我们既可以保证生成的数据趋势递增，同时也能非常方便扩展分表（之前的雪花算法是趋势递增但不方便针对范围扩展分表,现在UUID可以通过关键字来扩展分表 关键字,范围方便扩展分表 哈希不方便,只有一致性哈希方便）。非常nice。w
          • ​​

    • UUIDV4和雪花算法对比

      场景	雪花算法	UUIDV4(随机数)
      数据库主键	✅ 优先选择（有序、高效,占用空间小）	❌ 慎用（无序（页分裂,影响写入性能）、占用空间大（索引效率低下））
      分布式系统唯一标识	✅ 需确保机器ID不冲突	✅ 天然适合（全局唯一）,无需中心化协调
      可读性要求	❌ 纯数字，无直接意义	✅ 字符串形式更易读（如日志追踪）
      安全敏感场景	❌ ID可能暴露业务时间信息	✅ 随机性强，不易推测

  • 分库分表带来的问题

    • 不支持跨库事务--数据一致性问题

    • 不能跨表分页排序--分页查询,排序

    • 二次分表的问题--涉及到分表算法的更新,数据迁移

      • 一开始提前多点表

      • 想办法减少数据量--数据归档

      • 当最初的预估数据不够准确导致后续数据增长很快

        • 但如果一开始分表算法就用的是一致性哈希那就没那么多问题了

  • ​​

    • mysql最好三层==>每次查询两次索引页,一次数据页 ==>1280* 1280* 15约等于2000万数据

    • 在文章的开头已经介绍了页的结构，索引也也不例外，都会有 File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概 1k 左右。

      ​​

      我们就当做它就是 1K, 那整个页的大小是 16K, 剩下 15k 用于存数据，在索引页中主要记录的是主键（索引值）与页号，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte。

      所以 x=15*1024/12≈1280 行。

    • 叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。

      但是叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少。

      这边我们暂时按一条行数据 1k 来算，那一页就能存下 15 条，Y = 15*1024/1000 ≈15。

  • 垂直/水平 分库/分表

    • 垂直分表

      通常我们按以下原则进行垂直拆分:

      1. 把不常用的字段单独放在一张表;
      2. 把text，blob等大字段拆分出来放在附表中;
      3. 经常组合查询的列放在一张表中;

      垂直分表是将一个表按照字段分成多表，每个表存储其中一部分字段

      下面的是按照冷热字段进行拆分

      ​​

    • 垂直分库

      库内垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。(即性能瓶颈变为了机器性能)

      ​​

      垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上

    • 水平分库

      ​​

      水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上，比如：单数订单在db_orders_0数据库，偶数订单在db_orders_1数据库。

    • 水平分表

      ​​

      水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中，比如：0到500万的订单在orders_0数据、500万到1000万的订单在orders_1数据表。

锁竞争的长时间等待

数据库连接数不够,数据库IO或CPU比较高,数据库参数

• 批量操作--减少请求数据库的次数

分析慢SQL日志找到执行速度慢的SQL