数据库性能优化

性能影响

抛开业务复杂度，影响程度依次是硬件配置 > MySQL配置 > 数据表设计 > 索引优化

数据库优化方向

• 数据库服务器内核优化
  • 由专业的数据库开发人员去做
• my.cnf配置，搭配压力测试进行调试
  • 由运维人员去做
• SQL调优
  • 由业务开发人员去做
• 代码层面减少对数据库的访问
  • 由业务开发人员去做

注：数据库服务器内核优化普通人一般接触不到，能够接触到的就只有从运维角度去进行的my.cnf配置和从业务开发人员角度的SQL调优与代码层面减少对数据库的访问

---

代码层面

• 减少同数据库的交互次数(即减少访问)
  • 削峰填谷采用的限流、消息队列等高并发场景下的性能优化
  • 使用缓存(如Redis)优化查询，经常查到的数据放入缓存，后续获取直接从缓存中取出
• 由应用程序来处理数据(例：数据格式化)，不推荐使用数据库(例：数据库函数)
• 由应用程序保证数据准确性，不推荐使用外键约束
• 写多读少场景，由应用程序保证唯一性，不推荐使用唯一索引
• 适当冗余字段，尝试建立中间表，用应用程序计算中间结果，用空间换时间
• 不允许执行极度耗时的事务，配合应用程序拆分成更小的事务
• 预估重要数据表(比如订单表)的负载和数据增长态势，提前优化(分库分表)

SQL调优

• 预编译语句(即预先完成sql语句检查、编译，使用时只传入参数)

  • 减少SQL编译所需要的时间，还可以解决动态SQL带来的SQL注入问题
  • 只传参数比传SQL语句更高效
  • 相同语句一次解析，多次使用，提高处理效率

• 永久连接(数据库连接是一次创建永久有效的)

  • 在一些极端的环境中，Apache会不断的发出HTTP请求，创建子进程去请求数据库，数据库压力过大

• 选择正确的数据库引擎

  • MyISAM对大数据量查询友好，对DELETE、UPDATE、INSERT等不够友好
  • InnoDB对DELETE、UPDATE、INSERT等友好，对大数据量查询不友好
  • 数据库主从存储引擎可以不一致

• 擅用EXPLAIN执行计划

• SQL语句大写

  • SQL语句在执行的时候,是先转化为大写字母然后执行，直接大写可省去转化这一步

• 数据类型使用尽可能小的

  • 硬盘上存储占用越小速度越快，但不是越小越好，如果不能支撑业务，后续保存所需数据都保存不了，则不可取

• 选择合适的数据类型

• 先执行的SQL语句能检索出的数据越少越好

  • 先执行的SQL语句检索出的数据越少，下一次检索的计算量越小，性能耗费越小
    • 为了提高GROUP BY、JOIN等的效率，可以在执行到该语句前，用WHERE把不需要的记录过滤掉

• 索引的使用

  • WHERE子句、JOIN子句、ORDER BY、GROUP BY、HAVING子句、DISTINCT等里出现的列需要建索引
  • 索引种类：普通索引、组合索引、唯一索引、组合唯一索引、主键索引、全文索引
    • 全文索引由于查询精度以及扩展性不佳，更多企业选择Elasticsearch
  • 索引不要建立在有大量重复数据的列上
    • 索引有助于快速访问到符合条件的数据，该列数据大量重复，则建立索引没有意义
  • 善用覆盖索引
    • 即索引已囊括所查数据，无需回表查询，仅访问索引即可查到所需数据
      • 回表查询即根据索引查到聚簇索引即主键，又根据聚簇索引查到其他所需要的列数据
  • 善用联合索引
    • 遵循最左匹配原则，查询条件顺序如果不遵循最左匹配原则会失效
      • 当我们创建了一个联合索引(k1,k2,k3)时，相当于创建了(k1)、(k1,k2)、(k1,k2,k3)三个索引
    • 查询条件是多个单列索引时，会对两个单列索引查到的结果做一个并集的操作，联合索引的区分度(同时满足两个条件的记录数量更少)与性能是高于多个单列索引的，且随着数据量的增加，索引不能全部加载到内存，而是要从磁盘去读，这样索引的个数越多，读磁盘的开销就越大，因此联合索引的建立是必要的
    • 区分度最高的列放在联合索引最左处
    • 字段长度小的列放在联合索引的最左侧
      • 字段长度越小，一页能存储的数据量越大，IO性能越好
    • 使用最频繁的列放在联合索引的最左侧
      • 可以较少的建立一些索引
  • 删除冗余和重复索引
    • 如某一个索引包含在另一个联合索引的生效范围内，则可删除该索引
    • 重复的索引需维护，且优化器查询时也要逐个考虑
  • 索引不宜太多
    • 索引越多，虽查询效率越高，但插入、修改、删除时因维护索引的缘故效率越低
      • 索引是需要存储的，相当于数据库记录的目录，插入、修改、删除数据时还需维护该目录的内容
    • 索引消耗磁盘和CPU，索引越多，消耗越大，造成不必要的浪费
      • 索引需要存储，消耗磁盘
      • 维护和读取索引都占用很多资源(包括CPU)
    • 不建议建索引的情况
      • 频繁增删改的表不要建索引
      • 频繁更新的字段不要建索引
  • 每张表都要有主键
    • 无论通过什么方式去查询，最后都会通过主键定位到数据(因为MySQL是先走非聚集索引，然后走聚集索引，主键即聚集索引)
    • 主键对集群、分区非常重要
    • 主键使用BIGINT，避免使用VARCHAR
  • 避免在索引列上进行表达式操作，或对索引列使用MySQL的内置函数
    • 对索引进行运算会使索引失效
    • 可以使用索引 = 表达式或内置函数，例：可time = 10 +1，不可time + 1 = 10
  • 避免SQL语句中查询变量与字段定义类型不匹配
    • 数据类型不匹配，MySQL会做隐式的转换，函数作用于表字段，不仅浪费性能，如果是索引列，还会导致索引失效
    • 例如：字符串不加单引号，日期使用字符串
  • 列中尽量不要null，应用默认值代替
    • null值更新到非null值无法做到原地更新，容易发生索引分裂影响性能
      • 比如索引存储中，某一页刚好放满数据，其中一条数据某一列本来为空，改成有值的，那一页就放不下了(格式：主键id,索引列)
    • null值没有索引(有些数据库引擎有)
  • 避免用!=、<>、not in、is not null、is null等操作符
    • 因为null值没有索引，其次带有非的操作符也不太好命中索引，一般情况下，查询的成本高，优化器会自动放弃索引的
  • 避免用OR
    • 使用OR可能会使索引失效，从而全表扫描
    • OR两边一个加了索引，一个没加，即使第一个条件走了索引，第二个条件还是要全表扫描，也就是全表扫描+索引扫描+合并，如果它一开始就走全表扫描，直接一遍扫描就完事。MySQL自身有优化器，出于成本和效率考虑，遇到OR条件，索引失效合情合理
  • 模糊查询避免进行左侧模糊查询
    • 字符串(如CHAR、VARCHAR、TEXT、BLOB等)使用的是前缀索引
    • 左侧模糊查询不走索引(索引遵循最左匹配原则)
    • 如果有需要左侧模糊查询的业务，可以存一个字段是该字段的颠倒值，或者考虑全文索引(不推荐)以及Elasticsearch
  • 避免修改clustered索引数据列(聚簇索引，且一个表最多只有一个)
    • 因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦调整会耗费相当大的资源

• 避免使用SELECT *，尽量使用SELECT 具体字段

  • 查得越多速度越慢
  • 消耗更多的CPU、IO、网络带宽资源
  • 无法使用覆盖索引
  • 减少表结构变更带来的影响

• 避免嵌套子查询，尽量用JOIN

  • IN适合主表大子表小，EXIST适合主表小子表大
    • 由于查询优化器的不断升级，很多场景，这两者性能差不多一样了
    • IN的值不要超过500个

• 多表连接时，使用表的别名，并前缀于列上

  • 减少解析时间，并减少来自于那些由列歧义引起的语法错误

• 不要有超过5个以上的表连接

  • 连接表越多，编译的时间和开销也就越大，单次查询涉及数据量也会很大
  • 把连接表拆开成较小的几个执行，可读性更高
  • 如果一定要连接很多表才能得到数据，那么意味着糟糕的设计了

• INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、CROSS JOIN，优先使用INNER JOIN，如果是LEFT JOIN，左边表结果尽量小，如果是RIGHT JOIN，右边表结果尽量小，避免使用FULL JOIN、CROSS JOIN

  • 如果内连接是等值连接、不等值连接、自然连接(因为内连接可以没有ON，此时相当于交叉连接，要避免)，或许返回的行数比较少，所以性能相对会好一点
  • 使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少，同理，右连接则右边表数据结果尽量小，条件尽量放到右边处理
  • 全连接即左连接加右连接，交叉连接是两表的笛卡尔积

• 连接表时尽量保持两个字段一致

• 清空表数据用TRUNCATE，而不用DELETE

  • TRUNCATE会重置auto_increment的值，释放磁盘空间；不走事务，不锁表；不产生大量日志写入日志文件
  • DELETE不重置auto_increment的值，但插入的数据会覆盖在之前删除的数据上；走事务，会锁表，清空数据量过大会导致长时间内业务无法使用；会产生大量日志写入日志文件
  • 不过使用TRUNCATE会有无法回滚，数据无法恢复的风险，但是它快呀，慎用

• INSERT、UPDATE数据过多，考虑批量INSERT、UPDATE

  • 批量INSERT、UPDATE性能好，更加省时间

• DELETE、UPDATE、SELECT后加LIMIT

  • 明确确定有几条数据要DELETE、UPDATE、SELECT时，加LIMIT 数量
    • 命中后可避免继续全表扫描，若表中数据量过大时避免因锁表时间长将CPU打满，以致用到相关表的业务系统长时间内无法使用
  • DELETE、UPDATE的数据量过大时进行拆分处理，不要一次LIMIT太多
    • 原因同第一条
    • 误DELETE、UPDATE代价小
    • DELETE、UPDATE走事务，大事务会导致主从延迟
    • 拆分方案
      • 循环删除、更新
      • 人为并发(即多线程)删除更新(会导致死锁且无法回滚)
  • SELECT用LIMIT进行分页
    • 原因同第一条
    • 查询数据越多，速度越慢，用户等待时间长
  • SELECT 1 FROM xxx WHERE xxx = ? LIMIT 1 判断是否存在
    • 原因同第一条
  • 仅针对第一条，可不加LIMIT的前提是搜索条件加了索引，MySQL中加锁都是基于索引的，如果是以某个加了索引的字段为条件DELETE、UPDATE、SELECT，如果该字段没索引就会扫描到主键索引上，那么就算符合搜索条件的记录只有一条，也会锁表(加了索引后，除了被锁的记录，其他记录仍可操作)

• 优化LIMIT分页

  • 例如：LIMIT 10000,10，虽然只取10条，且有索引，但还是要重新查询计算偏移量，效率一样很慢
  • 方案
    • ORDER BY + 索引字段(注：不适用于混合排序，混合排序还是会全表扫描)
    • 以上一次查询的记录的排序字段最大值(即最后一条记录的排序字段)作为条件
    • 使用游标

• ORDER BY必须和LIMIT联用，否则会被优化掉

  • LIMIT以后可根据索引只取少量数据，不加LIMIT，ORDER BY 索引没有任何意义

  • 如果你将LIMIT row_count子句与ORDER
    BY子句组合在一起使用的话，MySQL会在找到排序结果的第一个row_count行后立即停止排序，不会对结果集的任何剩余部分进行排序。这种行为的一种表现形式是，一个ORDER BY查询带或者不带LIMIT可能返回行的顺序是不一样的，甚至多次查询的顺序也可能是不一样的

• 只更新必要字段，减少binlog日志

• 使用UNION ALL代替UNION，如果结果集允许重复的话或已知不可能出现重复

  • 不管检索结果有没有重复，都会尝试合并，并在输出之前进行排序，允许重复或已知不可能出现重复，UNION ALL比UNION效率更高

• 慎用DISTINCT

  • 查询一个或几个很少字段时，会带来优化效果，但很多时，却会大大降低查询效率，因为使用DISTINCT，数据库引擎会对数据进行比较，过滤掉重复数据，但这个比较、过滤的过程会占用系统资源、CPU时间

my.cnf配置

• 修改max_connections、max_used_connections
  • 并发数指同一时刻数据库能处理多少个请求，由max_connections、max_used_connections决定
    • max_connections是指MySQL实例的最大连接数，上限值是16384
    • max_used_connections是指每个数据库用户的最大连接数
  • MySQL会为每个连接提供缓冲区，意味着消耗更多的内存，如果连接数设置太高硬件吃不消，太低又不能充分利用硬件，一般要求两者比值超过10%，计算公式如：max_used_connections / max_connections * 100% = 3 / 100 * 100%
  • 查看max_connections、max_used_connections

show variables like '%max_connections%'
show variables like '%max_used_connections%'

• 将单次查询耗时控制在0.5秒内
  • 0.5秒是个经验值，源于用户体验的3秒原则。如果用户的操作3秒内没有响应，将会厌烦甚至退出。响应时间=客户端UI渲染耗时+网络请求耗时+应用程序处理耗时+查询数据库耗时，0.5秒就是留给数据库1/6的处理时间