MySQL 5.6 和 5.7 中的高级查询调优

感谢您参加我的 7 月 22 日题为“MySQL 5.6 和 5.7 中的高级查询调优”的网络研讨会（我的幻灯片和重播可在此处获得）。正如这里所承诺的那样，这里是问题列表和我的答案（感谢您提出的重要问题）。

问：这是解释示例：


 mysql> explain extended select id, site_id from test_index_id where site_id=1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index_id
         type: ref
possible_keys: key_site_id
          key: key_site_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

考虑到 a) 我们选择“id”，b) key_site_id 只包含 site_id，为什么 site_id 是查询的覆盖索引？

由于该表是 InnoDB，因此所有辅助键将始终包含主键（“id”）；在这种情况下，二级索引将包含满足上述查询所需的所有信息，并且 key_site_id 将是“覆盖索引”

问：应用程序随时间变化。您是否建议定期分析正在使用的索引并删除未使用的索引？如果是，有什么解决这个问题的建议吗？

是的，这是个好主意。通常可以使用 Percona 工具包或 MySQL 5.6 中的 Performance_schema 轻松完成

启用慢查询日志并记录每个查询，然后使用 Pt-index-usage 工具
或使用以下查询（如 FromDual 博客文章所建议）：


 mysql> explain extended select id, site_id from test_index_id where site_id=1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index_id
         type: ref
possible_keys: key_site_id
          key: key_site_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

Q：在5.6/5.7上发现重复索引会不会在查询时对db造成性能影响？

重复键会对选择产生负面影响：

MySQL 可能会感到困惑并选择错误的索引
总索引大小可能会增长，这会导致 MySQL 耗尽 RAM

问：建议使用什么方法来衡量查询（慢查询日志除外）的性能，以便知道在何处创建索引？

慢查询日志是最常用的方法。在 MySQL 5.6 中，您还可以使用 Performance Schema 并使用 events_statements_summary_by_digest 表。

问：我不确定网络研讨会是否涵盖了这一点，但是……是否有全文索引的最佳实践？

本次网络研讨会并未涵盖这些内容，但是，我已经做了很多关于全文索引的演示。例如：使用 MySQL 5.6 创建支持地理的应用程序

问：您可以为每个表定义的索引大小或索引数量的限制是多少？

磁盘上的 索引大小没有限制，但是，让活动索引适合 RAM 会很好（性能方面）。

InnoDB 中有一些索引限制，即一个表最多可以包含 64 个二级索引。

问：如果一个表有两列你想求和，你能否将该总和作为计算索引进行索引？除此之外，计算出的指数是否可以有“case when”？

澄清一下，这只是 MySQL 5.7（尚未发布）的一个特性。

是的，现在记录在案：


 mysql> explain extended select id, site_id from test_index_id where site_id=1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index_id
         type: ref
possible_keys: key_site_id
          key: key_site_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

问：我注意到您在像 DayOfTheWeek 这样基数非常低的列上创建了索引。通常这不应该是一种不好的做法吗？

是的你是对的！除非，您正在执行诸如“select count(*) from … where DayOfTheWeek = 7”之类的查询，否则这些索引可能不是很有用。

问：我看到一篇文章说如果你不预先指定主键，mysql / innodb 会在后台创建一个（隐藏）。如果大多数使用的字段不在主/半主键中，它与主键本身不同吗？有没有办法识别具有隐藏主键索引的表？

“隐藏”的主键将是 6 个字节，它也将附加（复制）到所有辅助键。您可以创建一个 INT 主键 auto_increment，它会更小（如果您不打算存储超过 40 亿行）。此外，您将无法在查询中使用隐藏的主键。

以下查询（针对 information_schema）可用于查找所有未声明主键（具有“隐藏”主键）的表：


 mysql> explain extended select id, site_id from test_index_id where site_id=1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index_id
         type: ref
possible_keys: key_site_id
          key: key_site_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

您还可以使用 mysql.innodb_index_stats 表来查找具有隐藏主键的行：

例子：


 mysql> explain extended select id, site_id from test_index_id where site_id=1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index_id
         type: ref
possible_keys: key_site_id
          key: key_site_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

Q：你是用alter table创建索引，但是mysql是如何对创建索引的数据进行排序的？它不是为此使用临时表吗？

这是一个很好的问题：“alter table … add index”的行为随着时间的推移而改变。如在线 DDL 概述中所述：

从历史上看，对 InnoDB 表的许多 DDL 操作都很昂贵。许多 ALTER TABLE 操作的工作方式是创建一个用请求的表选项和索引定义的新空表，然后将现有行逐一复制到新表，并在插入行时更新索引。复制原始表中的所有行后，删除旧表并使用原始表的名称重命名副本。

MySQL 5.5 和带有 InnoDB 插件的 MySQL 5.1 优化了 CREATE INDEX 和 DROP INDEX 以避免表复制行为。该功能被称为快速索引创建

当 MySQL 使用“快速创建索引”操作时，它会在 MySQL 的 tmpdir 中创建一组临时文件：

为了向现有表添加二级索引，InnoDB 扫描表，并使用内存缓冲区和临时文件按二级索引键列的值对行进行排序。然后按键值顺序构建 B 树，这比以随机顺序将行插入索引更有效。