第 68.1 节：构建复合索引

在许多情况下，复合索引比单列索引性能更好。要构建最优的复合索引，请按以下顺序填充列：

  1. 首先放 WHERE 子句中的等值条件列（例如 INDEX(a,b,...) 用于 WHERE a=12 AND b='xyz' ...）
  2. IN 列——优化器可能能够跳过索引中的某些部分。
  3. 一个“范围”条件列（例如 x BETWEEN 3 AND 9、name LIKE 'J%'）。注意：索引在遇到第一个范围列后，后面的列将不会被使用。
  4. GROUP BY 中的所有列，按顺序。
  5. ORDER BY 中的所有列，按顺序。只有在所有列都是升序（ASC）或都是降序（DESC），或者你使用的是 MySQL 8.0 时才有效。

注意和例外：

  • 不要重复列。
  • 跳过不适用的任何情况。
  • 如果没用完 WHERE 中的所有列，就没有必要继续到 GROUP BY 等。
  • 存在某些情况下，仅索引 ORDER BY 列（忽略 WHERE）是有用的。
  • 不要将列“隐藏”在函数中（例如 DATE(x) = ... 不能使用 x 上的索引）。
  • “前缀索引”（例如 text_col(99)）不太可能有帮助，甚至可能有害。

更多详细信息和提示。

第 68.2 节：InnoDB 表的存储布局优化

  1. 在 InnoDB 中，主键过长（无论是单列的值很长，还是多列组成的复合值很长）会浪费大量磁盘空间。因为指向同一行的所有二级索引记录中都会重复该行的主键值。如果主键很长，可以创建一个 AUTO_INCREMENT 列作为主键。

  2. 使用 VARCHAR 代替 CHAR 来存储可变长度字符串或包含许多 NULL 值的列。CHAR(N) 列总是占用 N 个字符来存储数据，即使字符串更短或其值为 NULL。更小的表更适合放入缓冲池并减少磁盘 I/O。

当使用 COMPACT 行格式（InnoDB 默认格式）和可变长度字符集（如 utf8 或 sjis）时，CHAR(N) 列占用可变空间，但至少仍为 N 字节。

  1. 对于大型表或包含大量重复文本或数值数据的表，考虑使用 COMPRESSED 行格式。将数据读入缓冲池或执行全表扫描所需的磁盘 I/O 更少。在做出最终决定之前，请测量 COMPRESSED 与 COMPACT 行格式能达到的压缩比率。注意：基准测试很少显示超过 2:1 的压缩率，并且 COMPRESSED 在缓冲池中会有大量开销。

  2. 一旦数据达到稳定大小，或者增长的表增加了数十或几百兆字节，考虑使用 OPTIMIZE TABLE 语句来重组表并压缩浪费的空间。重组后的表执行全表扫描所需的磁盘 I/O 更少。当其他技术（如改进索引使用或调整应用程序代码）不切实际时，这是一种提高性能的简单方法。注意：无论表大小如何，OPTIMIZE TABLE 都只应很少执行。因为它成本高昂，并且很少能足够改善表性能以值得执行。InnoDB 在保持其 B+ 树免于大量浪费空间方面表现良好。

OPTIMIZE TABLE 会复制表的数据部分并重建索引。其好处来自于改进数据在索引中的打包，以及减少表空间和磁盘上的碎片。好处因每个表中的数据而异。你可能会发现某些表有很大改善，而其他表没有，或者这些好处会随着时间推移而减少，直到你再次优化表。如果表很大，或者重建的索引不适合缓冲池，此操作可能会很慢。在表添加大量数据后的第一次运行通常比后续运行慢得多。