SQL性能优化

soulballad约 1230 字大约 4 分钟

MySQL 角度

设计阶段
- 数据库设计：
  - 选择合适的存储引擎，为不同的业务表选择不同的存储引擎
- 字段的设计：
  - 数据类型尽量用数字型，数字型的比较比字符型的快很多。
  - 数据长度尽量小，保持2的幂，这里的尽量小是指在满足可以预见的未来需求的前提下的。
  - 尽量不要允许 NULL，除非必要，可以用 NOT NULL+DEFAULT 代替。
  - 不要用数据库存储图片或者大文件。
  - 自增字段要慎用，不利于数据迁移。
  - 尽量做到冷热数据分离，减小表的宽度。
- 索引的设计：
  - 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
    雪花算法： 8 byte=64 bit=1(标记位)+41(时间戳)+5(机房id)+5(机器id)+12(序列号)
  - 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
  - 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
  - 一个表不要加太多索引，因为索引影响插入和更新的速度。
  - 离散度低的字段，不要创建索引。
编码阶段
- 只返回需要的字段：
  - 横向来看，不要写 SELECT * 的语句，而是选择你需要的字段。
  - 纵向来看，合理写 WHERE 子句，不要写没有 WHERE 的 SQL 语句。
  - 注意 SELECT INTO 后的 WHERE 子句，因为 SELECT INTO 把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE 子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。
  - 对于聚合查询，可以用 HAVING子句进一步限定返回的行。
- 尽量少做重复的工作：
  - 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。
  - 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。
  - 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。
  - 合并对同一表同一条件的多次 UPDATE，比如
    UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’ UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’ - 这两个语句应该合并成以下一个语句 UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’
  - UPDATE 操作不要拆成 DELETE+INSERT 操作的形式，虽然功能相同，但是性能差别是很大的。
  - 不要写一些没有意义的查询，比如 SELECT FROM EMPLOYEE WHERE 1=2
- 注意事务和锁：
  - 事务操作过程要尽量小，能拆分的事务要拆分开来。
  - 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。
  - 事务操作过程要按同一顺序访问对象，防止形成等待环路，造成死锁。
  - 提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。
  - 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。
- 子查询的用法：
  - NOT IN、NOT EXISTS 的相关子查询可以改用 LEFT JOIN 代替写法。比如：
  - 如果保证子查询没有重复，IN、EXISTS 的相关子查询可以用INNER JOIN 代替。比如：
  - IN 的相关子查询用EXISTS 代替，比如
  - 不要用 COUNT()的子查询判断是否存在记录，最好用 LEFT JOIN 或者 EXISTS，比如有人写这样的语句：
- 尽量使用索引：
  - 不要对索引字段进行运算
  - 不要对索引字段进行格式转换
  - 不要对索引字段使用函数
  - 不要对索引字段进行多字段连接
  - 模糊查询将 % 放后面
  - 使用 explain 分析索引执行情况

应用角度：

使用数据库连接池；
使用缓存或者分布式搜索，降低数据库压力；
主从集群，读写分离；
分库分表：水平分库（按照规则把单表数据分散到多个数据库），垂直分库（按照业务拆分不同数据库）；
业务设计：限流、使用MQ削峰、降级；