(关系型)数据库优化总结

来自高性能MySQL, 数据库索引设计与优化, 和MySQL官方文档。

总的来说,数据库优化是一门艺术,需要不断的去探索,这篇博客是前段时间学习以上三份资料时所做的笔记。

合理设计

首先从大局考虑:

  • 检查代码和数据库模型的设计是否正确,合理。
  • 需要加索引的列是否加了索引,是否加对了索引。
  • 使用缓存是否可以减少大量不必要的重复。

索引及其优化步骤(代价由低到高)

  • 使用EXPLAIN检查查询语句的索引使用情况,尽量将其优化成“三星索引(参见<数据库索引设计与优化>)”。其中注意事项包括:

    • 连表时,表的编码是否相同,如果不同,则用不上索引
    • 索引类型是否正确
    • 是否能命中数据库缓存,如果是由数据库进行运算的,则一般都无法命中缓存
    • 三星索引,简单来说,就是所有查询的字段,以及where之后带筛选的字段,以及order by的字段都有索引,则是三星索引
  • 升级数据库配置,数据库内存太小,速度就不会太高。一般来说,这一步和上一步的等级可以看情况互换,这里所指的情况就是, 如果是因为SQL写的特别烂,那么上一步应该作第一步,如果SQL写的已经可以了,那么需要检查一下数据库配置是否跟得上。常见的 升级硬件方案有:

    • 加内存(如果内存是瓶颈)
    • 换CPU(如果CPU是瓶颈)
    • 换SSD(如果磁盘I/O是瓶颈)
    • 组合以上方案
  • 读写分离。采取一写,多读的方式,可以有效的应对读大于写的业务。

  • 垂直分表。垂直分表是指把一张大表的n列重新组合,把一些不必要的列拆出去,组成新的表。这样做能优化性能的主要原因是减少 每次所需要传输的数据量,减少I/O。

  • 水平分表。水平分表是指将表按照某个维度切割成多个表,例如时间。例如有些消息系统可能只需要保存三个月的数据,那么可以把 三个月以前的数据插入到另外一张表中。这样做能优化性能的主要原因是减少了表的行数,从而降低B树的深度或子节点数量, 减少数据量,减少I/O,提高查询速度。

  • 垂直分库。垂直分库,其实微服务中,不同的服务使用不同的数据库就是类似的效用。可以优化性能的主要原因是把不同的瓶颈分散到 不同的数据库。

  • 水平分库。即sharding。按照某个维度把数据存到多个不同的库里。

看懂MySQL的EXPLAIN输出

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

主要记录一下 type 的不同输出的意思(由好到差):

  • system: 表只有一行
  • const: 此查询只有一个匹配行,例如根据主键查询或者根据设置了唯一索引的键来查询时
  • eq_ref: 连表查询时,有一个索引是主键或者唯一键
  • ref: 查询时,有索引可用
  • fulltext: 连表查询时,使用的是 FULLTEXT 类型的索引
  • ref_or_null: 查询时,查询语句为 SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; 这样的
  • index_merge: 使用了索引合并技术。意思就是对多个索引条件分别进行条件扫描,然后合并他们的结果,通常是有AND或者OR等语句, 且查询条件作用于不同的列的时候。参考:https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html
  • unique_subquery: 子查询中查出来的数据有是主键或者唯一键时,会被优化,然后就显示这个
  • index_subquery: 子查询时查出来的数据有索引时,会被优化,然后就显示这个
  • range: 使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN时,往往是这种类型,因为要从一个范围里 扫描
  • index: 几乎和 ALL 一样,不过索引还是会被扫描
  • ALL: 全表扫描
微信订阅号
微信订阅号

 友情链接