许多人认为调整数据库查询就像哈利·波特小说中某种神秘的“黑魔法”；用错误的咒语，你的数据就会从宝贵的资源变成一堆糊状物。
实际上，关系数据库系统的查询调整是简单的工程，并遵循易于理解的规则或启发式方法。查询优化器会将你发送到 MySQL 实例的查询进行转换，然后它会根据这些启发式方法以及它对你数据的了解来确定获取请求数据的最佳方式。再读一遍最后一部分：“它对你数据的了解”。查询优化器对你的数据位置的猜测越少，它就越能更好地创建计划来交付你的数据。
为了让优化器更好地了解数据，你可以使用索引和直方图。如果使用得当，它们可以大大提高数据库查询的速度。如果你遵循食谱，你将得到你喜欢的东西。但是，如果你在食谱中添加自己的配料，你可能得不到你想要的东西。
基于成本的优化器
大多数现代关系数据库都使用基于成本的优化器来确定如何从数据库中检索你的数据。该成本基于尽可能减少非常昂贵的磁盘读取。数据库服务器内部的查询优化器代码会保留在遇到数据时获取该数据的统计信息，并构建获取数据的历史模型。
但是历史数据可能已过时。这就像去商店购买你最喜欢的零食，却震惊地发现价格突然上涨或商店关门了。你的服务器的优化过程可能会基于旧信息做出错误的假设，从而产生糟糕的查询计划。
查询的复杂性可能会阻碍优化。优化器希望交付可用选项中成本最低的查询。连接五个不同的表意味着有五阶乘或 120 种可能的组合来决定连接什么。代码中内置了启发式方法，试图缩短评估所有可能选项的时间。MySQL 希望在每次看到查询时都生成一个新的查询计划，而其他数据库（如 Oracle）可以锁定查询计划。这就是为什么向优化器提供有关你数据的详细信息至关重要。为了获得一致的性能，为查询优化器提供最新的信息以便在制定查询计划时使用确实很有帮助。
此外，优化器中还内置了规则，其中包含可能与你的数据现实不符的假设。查询优化器会假设列中的所有数据在所有行中均匀分布，除非它有其他信息。如果看不到其他选择，它将默认选择两个可能索引中较小的一个。虽然优化器的基于成本的模型可以做出很多好的决策，但你可能会遇到无法获得最佳查询计划的情况。
查询计划？
查询计划是优化器将为服务器从查询中执行的内容生成的计划。查看查询计划的方法是在你的查询前面加上单词 EXPLAIN。例如，以下查询要求从 city 表中获取城市名称，并从相应的 country 表中获取国家名称，这两个表通过国家的唯一代码链接。此案例仅对英国前五个按字母顺序排列的城市感兴趣
SELECT city.name as 'City',
country.name as 'Country'
FROM city
JOIN country ON (city.countrycode = country.code)
WHERE country.code = 'GBR'
LIMIT 5;在此查询前面加上 EXPLAIN 将给出优化器生成的查询计划。跳过除输出末尾之外的所有内容，很容易看到优化的查询
select `world`.`city`.`Name` AS `City`,
'United Kingdom' AS `Country`
from `world`.`city`
join `world`.`country`
where (`world`.`city`.`CountryCode` = 'GBR')
limit 5;最大的变化是 country.name as 'Country' 被更改为 'United Kingdom' AS 'Country'，WHERE 子句从在 country 表中查找更改为在 city 表中查找。优化器确定这两个更改将比原始查询提供更快的结果。
索引
在 MySQL 世界中，你将听到索引和键互换使用。但是，索引由键组成，而键是识别记录的一种方式，希望是唯一的方式。如果一列被设计为键，优化器可以搜索这些键的列表以找到所需的记录，而无需读取整个表。如果没有索引，服务器必须从第一列的第一行开始，并读取每一行数据。如果该列被创建为唯一索引，则服务器可以转到该一行数据并忽略其余行。索引的值越唯一（也称为其基数），效果越好。请记住，我们正在寻找更快地获取数据的方法。
MySQL 默认的 InnoDB 存储引擎希望你的表具有主键，并将你的数据按该键存储在 B+ 树中。最近添加的 MySQL 功能是不可见列——除非在查询中显式命名列，否则这些列不返回数据。例如，SELECT * FROM foo; 不提供任何指定为隐藏的列。此功能提供了一种向旧表添加主键的方法，而无需重新编码所有查询以包含该新列。
为了使事情更加复杂，索引有很多类型，例如函数索引、空间索引和复合索引。甚至在某些情况下，你可以创建一个索引，该索引将为查询提供所有请求的信息，从而无需访问数据表。
描述各种索引超出了本文的范围，因此只需将索引视为访问你想要的记录或记录的快捷方式。你可以在一列或多列或这些列的一部分上创建索引。我医生的系统可以通过我的姓氏的前三个字母和出生日期来查找我的记录。使用多列需要首先使用最唯一的字段，然后使用第二最唯一的字段，依此类推。年-月-日索引适用于年-月-日、年-月和年搜索，但不适用于日、月-日或年-日搜索。围绕你想要使用数据的方式设计索引会有所帮助。
直方图
直方图是你的数据的分布。如果你要按姓氏的首字母顺序排列人名，你可以为姓氏以字母 A 到 F 开头的人使用一个“逻辑桶”，然后为 G 到 J 使用另一个，依此类推。优化器假设数据在列中均匀分布，但这在实际使用中很少见。
MySQL 提供两种类型的直方图：等高直方图，其中所有数据在桶之间平均分配；以及单例直方图，其中一个桶中只有一个值。你最多可以有 1,024 个桶。为你的数据列选择的桶的数量取决于许多因素，包括你拥有的不同值的数量、数据的倾斜程度以及你的精度真正需要多高。在达到一定数量的桶之后，收益会递减。
此命令将在表 t 的列 c1 上创建 10 个桶的直方图
ANALYZE TABLE t UPDATE HISTOGRAM ON c1 WITH 10 BUCKETS;想象一下，你出售小号、中号和大号袜子，每种尺寸都有自己的存储箱。要找到你需要的尺寸，你可以去该尺寸的箱子。MySQL 自三年前 MySQL 8.0 发布以来就有了直方图，但它们不如索引那么广为人知。与索引不同，插入、更新或删除记录没有开销。要更新索引，必须更新 ANALYZE TABLE 命令。当数据不会频繁变动时，这是一个好方法，而频繁更改数据会降低效率。
索引还是直方图？
对于你需要直接访问数据的唯一项，请使用索引。更新、删除和插入存在开销，但如果你的数据架构合理，你将获得快速访问。对于不经常更新的数据，例如过去十年的季度结果，请使用直方图。
结束语
本文源于最近在 Open Source 101 会议 上的演示。该演示源于在 PHP UK 会议 上的研讨会。查询调优是一个复杂的主题，每次我介绍索引和直方图时，我都会找到改进演示的方法。但每次演示也表明，软件领域的许多人对索引并不熟悉，并且倾向于不正确地使用它们。直方图存在的时间还不够长（我希望如此），还没有被类似地误用。
评论已关闭。