BYT0723的博客

Record Programming

0%

SQL优化策略

发表于 更新于 分类于 Valine：

1. MYSQL 优化的几个原则

  • 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO
  • 返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘和网络 IO
  • 减少交互次数：批量 DML 操作，函数存储等减少数据连接次数
  • 利用更多资源：使用表分区，并增加并行操作，更大限度利用 CPU 资源

总结一下

  • 最大化利用索引
  • 尽可能避免全表扫描
  • 减少无效数据的查询

2. 优化策略

声明：以下 SQL 优化策略适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。

2.1 避免不走索引的场景

  1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引，进行全表扫描
1
2
3
4
# bad
SELECT * FROM t WHERE username LIKE '%王%'
# good
SELECT * FROM t WHERE username LIKE '王%'

如果需要在前面使用模糊查询，提供几个解决思路：

  • 使用 MySQL 内置函数INSTR(str, substr)来匹配
  • 使用 FullText 全文索引，用 match against 检索
  • 数据量较大的情况，建议引入 ElasticSearch、solr，亿级数据量检索速度秒级
  • 当表数据量较少（几千条左右），直接like '%xx%'
  1. 尽量避免使用in 和not in，当 in 的取值范围过大时，会导致索引失效，引擎走全表扫描
1
2
# bad
SELECT * FROM t WHERE id IN (2,3,4,5,6,7,8,9);
  • 如果是连续数据，可用between代替
  • 如果是子查询，可以用exists代替
1
2
3
4
5
6
7
SELECT * FROM t WHERE id Between 2 and 9;

# bad
SELECT * FROM t WHERE id IN (SELECT id FROM t2)
# good
SELECT * FROM t WHERE EXISTS (SELECT id FROM t2 WHERE t1.id = t2.id)
#

  1. 尽量避免使用 or 连接未使用索引的条件，或像 IN 这种过滤范围不大的条件，会导致引擎走全表扫描

  2. 尽量避免使用 NULL 值判断，NULL 判断会导致引擎走全表扫描

  3. 尽量避免在条件等式的左侧进行表示式或函数操作，会导致引擎走全表扫描

  4. 尽量避免使用<>或!=，会导致引擎走全表扫描。

  5. 使用联合索引检索时，MySQl 联合索引最左匹配原则, 即 ABC 索引，条件为 AC 或 BC 都无法使用索引，但 A,AB 可以

  6. 隐式类型转换造成不使用索引

  7. order by 的字段如果没有索引的话，将会进行全表扫描

2.2 SELECT 的其他优化