GreatSQL用户手册

§ SQL 开发优化

本节介绍在使用和基于 GreatSQL 数据库进行业务系统应用开发时建议遵循的 SQL 开发优化参考。

§ 高效SQL应用开发参考

  • 想要在应用开发中让业务 SQL 获得更高性能，注意遵循几个核心原则：

    • 窄表好：数据类型够用就好，保持高效。
    • 小表好：数据冷热分离，只留最新热数据。
    • 请求快：每个事务/请求效率足够高，不阻塞。
    • 请求少：从产品需求端削减不必要的请求，动态请求静态化或降频；也要减少对数据库的额外请求，例如在数据库层做额外分组、排序。

  • 默认不要采用 SELECT * 一次取出所有列，只取必要的列，如果确实需要读取所有列则考虑在只读从节点上查询。

  • 在一条 SQL 或一个事务中，不要一次性读写太多数据，通常建议不要超过1万行，降低每次请求的代价。

  • 需要随机取值时，不要直接运行 ORDER BY RAND()，改用优化后的方法，可参考文章：提高MySQL RAND随机排序效率 (opens new window)。

  • 不要执行 LIKE '%search%' 全模糊匹配搜索，改成 LIKE 'search%' 搜索，才能用上索引。

  • 对 InnoDB 表执行 COUNT(*) 效率低，尤其是不带WHERE条件时，要少用，可以改成求概数，或者采用其他外部计数器。

  • 统计查询 COUNT(*) 和 COUNT(N) 并没本质区别，但后者不是常见写法，因此不推荐使用，容易造成混淆；而 COUNT(fieldN) 则不同，尤其是当列定义允许NULL时，会逐行扫描，跳过值为NULL的行。

  • 分页查询中当页码很大时，不要简单 LIMIT M,N，而是改用延迟关联优化思路，可参考文章：高性能分页查询方法 (opens new window)、分页优化 (opens new window)。

  • 多表 JOIN 时，务必注意驱动表选择是否最优，分组/排序列是否属于驱动表。

  • 多表 JOIN 时，注意规避类型隐式转换造成无法使用索引。

  • 多个查询结果如果不需要去重的话，可以采用 UNION ALL 代替 UNION，因为用 UNION ALL不需要产生外的临时表。

  • 大部分时候，分库分表并不是必须的，也不存在超过一千万就要分表的硬性要求，关键还是要优化应用请求效率。

§ 索引应用参考

  • 索引创建和使用尽可能满足三星索引原则（即：查询条件列有索引、排序列所有索引、读取的列都在索引里），但也不能机械教条执行，要根据实际情况灵活变通。

  • 索引创建和使用要符合最左匹配原则，并且把基数较高（区分度高）的列放在联合索引的左边，提高索引效率。

  • 业务上线前，需要先在开发测试环境采集最常用的 SQL 请求（可以设置 long_query_time=0 记录所有SQL，并用 pt-query-digest 工具分析），针对这些请求创建合适的索引。

  • 通常来说，多列联合索引的使用率会比单列索引更高，可能可以满足更多应用场景。例如：针对查询 WHERE a=? AND b=?，创建联合索引 idx_a_b(a,b) 就比分别创建两个单列索引 idx_a(a), idx_b(b) 的用处更大，后者通常没办法同时被用到。

  • 在对 CHAR/VARCHAR/TEXT/BLOB 等类型列创建索引时，总是只创建部分索引，而不是整个列都加上索引，例如：idx_user(user(20))。

  • 有业务用途的列不要设计作为表的主键列，而是加上唯一约束，表的主键列推荐使用自增整型。

  • 基数较低（区分度低）的列通常不要创建单列索引（多列联合索引除外），因为效率一般也较低，例如：性别属性、状态值、逻辑是否等常见只有少数几个可选值的列。

  • 多表 JOIN 关联查询时，要确保被关联的列上总是有索引。

  • 不要创建重复索引，可以通过查询 sys.schema_redundant_indexes 来确认是否有重复索引。不过要注意一种情况，索引 idx_a_b(a, b) 和索引 idx_a(a)，看起来后者是冗余索引，但如果有个 SQL 是 WHERE a=? ORDER BY id 这样的，其中 id 是表的主键列，这时候只有第二个索引能用于消除 ORDER BY，而第一个索引则不支持，所以也要看具体情况决定是否删除冗余索引。

  • 注意规避几种可能用不上索引的场景：

    • 当预估扫描记录数超过 10% ~ 20% 时，即便有索引，也很可能会改成全表扫描。
    • 使用联合索引时不符合前导匹配原则。
    • 模糊查询条件列最左以通配符 "%" 开始。
    • 多表 JOIN 时，排序列不属于驱动表，就无法利用索引消除排序。
    • 条件过滤和排序的列分属不同索引，则只能用上一个索引，不能两个索引都用上。
    • 多表 JOIN 时，关联列数据类型不一致，就可能导致类型隐式转换而无法用上索引。

  • 可以定期查询 sys.schema_unused_indexes，找出那些长期不使用的索引，将它们设置为不可见索引，一段时间后如果对业务没影响，再正式删除。

  • 不要执行 LIKE '%search%' 全模糊匹配搜索，改成 LIKE 'search%' 搜索，才能用上索引。

  • 可以在负载较低的期间执行 ANALYZE TABLE 更新统计信息，提高索引选择准确性。

  • 关键业务 SQL 上线前都要经过审核，避免出现高频且无索引的请求。

  • 尽量不使用外键约束，因为会增加更多行锁、死锁概率，以及数据库复杂度。

  • 问题反馈 gitee (opens new window)

  • 扫码关注微信公众号

greatsql-wx

← 存储引擎选择 →