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

来自高性能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 : 全表扫描