高性能MySQL

Progress

Learning Progress: Completed.🎊

Learning Source

• 《高性能 MySQL》（第四版）

1 操作系统与硬件优化

• 可以通过检查 CPU 使用率来确定工作负载是否受 CPU 限制， 但不要只查看 CPU 的总体负载，而是要查看最重要查询的 CPU 使用率和 I/O 之间的平衡， 并注意 CPU 负载是否均匀。

• 每个查询将只使用一个 CPU。

• 配置大内存的主要原因并不是为了在内存中保存大量数据， 而是为了避免磁盘 I/O，因为磁盘 I/O 比访问内存中的数据要慢几个数量级。

2 schema 设计与管理

• 尽量避免存储 NULL
  即使应用程序本身并不需要存储 NULL （缺失值），很多表也包含可为 NULL 的列， 这是因为 NULL 可以是列的默认属性。通常情况下最好指定列为 NOT NULL， 除非明确需要存储 NULL 值。 如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化， 因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。 可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要特殊处理。 通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小， 所以（调优时）没有必要首先在现有 schema 中查找并修改这种情况， 除非确定这回导致问题。

• 由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才是用 DECIMAL —— 例如，存储财务数据。但在一些大容量的场景，可以考虑使用 BIGINT 代替 DECIMAL， 将需要存储的货币单位根据小数的位数乘以相应的倍数即可。 假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万， 然后将结果存储在 BIGINT 里，这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。

• TIMESTAMP 只使用 4 字节的存储空间，所以它的范围比 DATATIME 小得多： 只能表示从 1970 年 到 2038 年 1 月 19 日。

• MySQL 会对 NULL 值进行索引，而 Oracle 则不会。

3 创建高性能的索引

3.1 索引基础

• B-tree 树的深度和表的大小直接相关。

• 索引对多个值进行排序的依据是 CREATE TABLE 语句中定义索引时列的顺序。

• InnoDB 存储引擎有一个被称为自适应哈希索引的特性。 当 InnoDB 发现某些索引值被非常频繁地访问时， 它会在原有的 B-tree 索引之上，在内存中再构建一个哈希索引。 这就让 B-tree 索引也具备了一些哈希索引的优势。

• B-tree 索引的限制：

  • 如果不是按照索引的最左列开始查找，则无法使用索引。

  • 不能跳过索引中的列。

  • 如果查询中有某列的范围查找，则其右边所有列都无法使用索引优化查找。

• 总结起来索引有如下三个优点：

  • 索引大大减少了服务器需要扫描的数据量。

  • 索引可以帮助服务器避免排序和临时表。

  • 索引可以将随机 I/O 变为顺序 I/O。

3.2 高性能的索引策略

• 有时候为了提升索引的性能，同时也节省索引空间， 可以只对字段的前一部分字符进行索引，这样做的缺点是，会降低索引的选择性。 索引的选择性是指， 不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值， 范围从 1/#T 到 1 之间。索引的选择性越高则查询效率越高， 因为选择性高的索引可以让 MySQL 在查询时过滤掉更多的行。 唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。 这里的关键点在于，既要选择足够长的前缀以保证较高的选择性， 同时又不能太长（以便节约空间）。前缀应该足够长， 以使得前缀索引的选择性接近于索引整列。换句话说， 前缀的“基数”应该接近于完整列的“基数”。

• 前缀索引是一种能使索引更小、更快的有效办法，但它也有缺点： MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY 操作， 也无法使用前缀索引做覆盖扫描。

• 如果一个索引包含（或者说覆盖）所有需要查询的字段的值， 我们就称之为覆盖索引。需要注意的是， 只有 B-tree 索引可以用于覆盖索引。

4 查询性能优化

• 如果我们想要知道结果中的行数，应该始终使用 COUNT(*)， 这样可以更清晰地传达意图，避免糟糕的性能表现。