SQL 慢查询

慢查询避免

在实际项目中，数据库查询经常出现响应过慢或超时情况。那么怎么减少慢查询的出现呢？首先可以从数据表设计角度出发去进行避免，可以从以下方面进行考虑：

• 尽量使⽤整型表示字符串，如枚举值存code不存name，ip转换为数字。
• ⼩单位⼤数额避免出现⼩数。
• 尽可能选择⼩的数据类型和指定短的⻓度。
• 尽可能使⽤ not null。
• 单表字段不宜过多。
• 表设计合理，尽量避免出现多表联合查询。

慢查询处理

合理设计表，可以减少慢查询的出现，但是并不能完全避免。本文将慢查询可分为一般慢查询、深度分页慢查询和数据量大导致的慢查询。

一般慢查询

当出现一般慢查询时，可以按照以下步骤去进行 SQL 调优：

• 避免全表扫描。这⾥需要注意⼀些索引设计和使⽤的问题：
  • 使⽤复合索引，避免出现多个单列索引。
  • 索引不会包含有NULL值的列，在数据库设计时不要让索引字段的默认值为 NULL。
  • 注意排序的索引问题，如果where⼦句中已经使⽤了索引的话，那么order by中的列是不会使⽤索引的。
  • 不要在列上进⾏运算。
  • 不使⽤NOT IN和<>操作。
• 使⽤连接（JOIN）来代替⼦查询(Sub-Queries) 。JOIN 之所以更有效率⼀些，是因为MySQL不需要在内存中创建临时表来完成逻辑上的多个步骤的查询⼯作。
• 使⽤联合(UNION)来代替⼿动创建的临时表。

深度分页

在分页查询时，经常出现页数越大，查询越慢的情况。

MySQL 实现分⻚查询通常使⽤ LIMIT 和 OFFSET ⼦句实现，LIMIT n 表示每⻚查询n条记录，OFFSET m表示从第m条记录开始查询。 当OFFSET 值较⼤时，MySQL 可能会选择执⾏全表扫描⽽不是使⽤索引。此外分页查询在使⽤⼆级索引时，需要通过索引回表到主键索引去检索完整的⾏数据，这也会使查询效率变慢。那么如何进行优化呢？

• 使⽤主键索引优化。如果主键是⾃增的，可以通过主键进⾏优化。

SELECT * FROM table_name WHERE id > [last_id] ORDER BY id LIMIT [page_size]; 

• 使⽤覆盖索引：尽量使⽤覆盖索引以减少回表操作，查询列都在索引中，这样可以避免回表操作。
• 使⽤⼦查询优化：通过⼦查询先定位到接近⽬标结果的位置，然后外层查询获取具体数据。

SELECT * FROM table_name WHERE id >= ( 
    SELECT id FROM table_name WHERE column = 'value' 
    ORDER BY id DESC LIMIT 1 OFFSET [offset] 
) 
ORDER BY id ASC 
LIMIT [page_size]; 

• 使⽤INNER JOIN，减少回表次数。

SELECT a.* FROM table_name a 
INNER JOIN ( 
    SELECT id FROM table_name 
    WHERE column = 'value' 
    ORDER BY id DESC 
    LIMIT [offset], [page_size] 
) b ON a.id = b.id 

• 使⽤搜索引擎：对于极深的分⻚，可以考虑使⽤Elasticsearch等搜索引擎来处理分⻚查询。

大数据慢查询

在MySQL 中，单表数据量一般都限制在 2000w 以内，当超过后会出现严重性能问题。所以针对大表，可以进行⽔平分表。⽔平分表是⼀种将数据表按⼀定规则拆分为多个⼦表的技术。每个⼦表存储全表数据的⼀部分，所有⼦表共同组成完整的数据集。通过这种⽅式，可以减⼩单表的数据量，提⾼查询和操作的性能。水平分表一般可以按照以下几种策略进行：

• 按范围分表：将数据根据某个字段的值划分为多个范围，每个范围对应⼀个分表，⽐如按⽤户ID范围分表。 这种方式的优点是数据分布较为均匀，易于控制分表的⼤⼩，查询时可以直接定位到对应的分表，性能较⾼。 但是如果当数据增⻓或变化超过预期时，可能需要频繁调整分表范围，同时可能出现数据倾斜问题，如果某些范围的数据过多，仍然可能导致单表过⼤。
• 按hash分表：将数据通过哈希函数进⾏处理，将哈希值对应到不同的分表中。 这种方式数据分布较为均匀，不易出现数据倾斜问题，分表后的数据量更为均衡，有助于提⾼查询和写⼊性能。但是查询时⽆法直接定位到具体的分表，需要通过哈希计算确定分表，增加了查询和更新的复杂度。
• 按⽇期分表：根据时间字段，将数据按⽇期、⽉份或年份进⾏分表。 这种方式易于管理和查询，特别是针对时间相关的查询，如按⽉或年统计， 可以⽅便地进⾏历史数据归档和清理。 但是缺点也很明显，随着时间的推移，分表数量会不断增加，管理难度也会增加，如果数据分布不均匀，某些时间段的数据量可能会过⼤。
• 按区域分表：根据地理位置或区域信息，将数据按区域划分为多个分表。这种方式可以有效地根据地域进⾏业务划分，⽅便进⾏区域性数据分析（在分布式部署中，可以将不同地区的数据放在不同的物理服务器上，提⾼系统的可靠性）。但是不同区域的数据量可能会不均衡，而且在查询跨区域数据时，可能需要访问多个分表，增加了复杂性。

具体采用何种分表策略，还需要结合自身业务特点进行评估。

分表有一个很重要的问题，就是分表ID⽣成策略应该如何设计。下面是几种生成策略以及其优缺点。

• ⾃增 ID 与分表 ID 组合
  • 优点：实现简单，利⽤数据库的⾃增特性，确保每个分表内的 ID 唯⼀，且⽣成的 ID 是有序的，有助于索引性能的提升。
  • 缺点：当表数量较多时，ID 组合的⻓度可能较⻓，存储空间需求增加。因此不适⽤于需要在分表间跨表查询或合并表的场景，因为不同表的⾃增 ID 可能会重复。
• UUID
  • 优点：⽆需依赖数据库，可以在应⽤层⽣成，适合分布式环境。确保全球范围内的唯⼀性，不会发⽣冲突。
  • 缺点：⽣成的 UUID 较⻓（36 字符），可能导致索引性能下降。没有顺序性，插⼊数据时可能导致索引频繁重排，影响性能。
• Snowflake ID：是⼀种 Twitter 开发的分布式 ID ⽣成算法，通过时间戳、机器 ID 和 序列号⽣成唯⼀ ID，⽣成的 ID 是⼀个 64 位的⻓整数。
  • 优点：⾼性能，每秒可以⽣成⼤量唯⼀ ID，⽣成的 ID 有序性强，利于数据库索引优化。适⽤于分布式系统，可在多个节点上并⾏⽣成不重复的 ID。
  • 缺点： 需要引⼊第三⽅库或⾃⼰实现 Snowflake 算法，算法⽐较复杂，调试和维护成本较⾼。
• 分布式⾃增 ID：通过分布式锁或者特定的服务（如 Redis、Zookeeper）⽣成全局唯⼀的⾃增 ID。
  • 优点： 保证全局唯⼀性，⽣成的 ID 连续且有序，适合在分布式环境中使⽤，不会有冲突。
  • 缺点：需要依赖外部服务，如 Redis、Zookeeper，如果外部服务出现故障，会影响 ID 的⽣成。