像专业人士一样玩MySQL查询一堆糊状物。事实上，关系数据库系统的查询调优是一个简单的项目，它遵循的规则或启发式方法很容易理解。查询优化器翻译您发送给MySQL实例的查询指令，然后使用这些启发式方法，结合优化器已经知道的有关数据的信息，来确定获取所请求数据的最佳方式。再读一遍后半句：“优化器已知的数据信息”。查询优化器需要知道的关于数据位置的猜测越少（即它知道的信息越多），它就可以更好地计划交付数据。为了让优化器更好地理解数据，可以考虑使用索引和直方图。正确使用索引和直方图可以大大提高数据库查询的速度。就像按照食谱去做，你可以得到自己喜欢的东西，但是如果你随意在食谱中添加食材，最终的结果可能不会如你所愿。基于成本的优化器大多数现代关系数据库都使用基于成本的优化器来确定如何从数据库中检索数据。此成本方案基于最小化资源密集型磁盘读取。数据库服务器内的查询优化器代码会在数据获取时对其进行计数，并构建获取数据的历史模型。但历史数据可能已经过时。就好像你去商店买自己喜欢的零食，突然发现零食涨价了，或者商店关门了。服务器的优化过程可能会根据旧信息做出不正确的假设，从而导致查询计划效率低下。查询的复杂性会影响优化。优化器希望提供可用的最低成本查询。连接五个不同的表意味着有5（或120）种可能的连接组合的阶乘。启发式方法内置于代码中以尝试快速评估所有可能的选项。MySQL期望每次看到查询时生成一个新的查询计划，而其他数据库（如Oracle）可以锁定查询计划。这就是为什么向优化器提供有关数据的详细信息至关重要的原因。为了获得一致的性能，在制定查询计划时向查询优化器提供最新信息是非常有效的。此外，优化器中内置的规则可能与数据的实际情况不符。在没有更多可用信息的情况下，查询优化器假定列中的所有数据均匀分布在所有行中。当没有其他选择依据时，它默认为两个可能索引中较小的一个。虽然基于成本的优化器模型可以做出很多好的决策，但最终的查询计划可能不是最优的。什么是查询计划？查询计划是指优化器根据查询语句生成并提供给服务器执行的计划内容。查看查询计划的方法是在查询语句前加上EXPLAIN关键字。例如，下面的查询从城市表（city）和相应的国家表（country）中获取城市名称（和国家名称），它们由国家的唯一代码连接。本例只查询英国字母顺序前5个城市：SELECTcity.nameAS'City',country.nameAS'Country'FROMcityJOINcountryON(city.countrycode=country.code)WHEREcountry.code='GBR'LIMIT5;在查询语句前加上EXPLAIN就可以看到优化器生成的查询计划。跳过输出末尾以外的所有内容以查看优化的查询：SELECT`world`.`city`.`Name`AS`City`,'UnitedKingdom'AS`Country`FROM`world`.`city`JOIN`world`.`country`WHERE(`world`.`city`.`CountryCode`='GBR')LIMIT5;看比较大的变化，country.nameas'Country'改为'UnitedKingdom'AS'Country'，WHERE子句从看country表变成了看city表。优化器认为这两个更改将提供比原始查询更快的结果。索引在MySQL世界中，您会听到索引或键的概念。然而，索引是由键组成的，键是一种识别记录的方式并且很可能是唯一的。如果将列设计为键，优化器可以通过搜索这些键的列表来找到所需的记录，而不必读取整个表。如果没有索引，服务器必须从第一列的第一行开始读取每一行数据。如果该列被创建为唯一索引，则服务器可以直接读取该行并忽略其余部分。索引的值（也称为基数）应尽可能唯一。请记住，我们正在寻找更快获取数据的方法。MySQL的默认InnoDB存储引擎希望您的表有一个主键，并根据该键将您的数据存储在B+树中。“不可见列”是MySQL最近新增的特性，除非在查询中明确指定不可见列，否则不会返回该列数据。例如，SELECT*FROMfoo；不会返回任何不可见的列。此功能提供了一种将主键添加到旧表而无需重写所有查询以包含新列的方法。更复杂的是，有各种类型的索引，例如功能、空间和复合。在某些情况下，您甚至可以创建一个可以为查询提供所有请求信息的索引，从而无需访问数据表。本文不会详细介绍各种索引类型，您只需将索引视为您要查询的数据记录的快捷方式即可。您可以在一个或多个列或这些列的一部分上创建索引。然后我的医生系统可以通过我姓氏和出生日期的前三个字母来查找我的记录。使用多个列时，请注意首先选择最独特的字段，然后是第二个最独特的字段，依此类推。年月日索引可用于年月日、年月和年检索，但不适用于日检索、月日检索或年日检索。考虑这些因素可以帮助您围绕数据的使用方式设计索引。直方图直方图是数据的分布。如果按姓氏的字母顺序对人名进行排序，则可以将姓氏以字母A到F开头的人放入一个“逻辑桶”，然后将姓氏以G到J开头的人放入另一个，依此类推。优化器假定数据在列中均匀分布，但实际上大多数情况并非如此。MySQL提供两种类型的直方图：等高（所有数据均匀分布在桶中）和等宽（单个值在单个桶中）。最多可设置1,024个桶。数据桶数量的选择取决于许多因素，包括去重值的数量、数据倾斜和所需的结果准确性。如果桶的数量超过某个阈值，桶机制的好处开始减少。以下命令将在表t的c1列上创建一个10桶直方图：ANALYZETABLEtUPDATEHISTOGRAMONc1WITH10BUCKETS；想象一下，您销售小号、中号和大号袜子，每种尺寸都放在一个单独的箱子里。如果您正在寻找特定尺码的袜子，可以直接到相应尺码的盒子里寻找。MySQL自三年前发布MySQL8.0以来就有了直方图，但该功能并不像索引那样广为人知。与索引不同，使用直方图插入、更新或删除记录没有开销。如果更新索引，则必须更新ANALYZETABLE命令。当数据变化不大并且频繁的数据变化会降低效率时，直方图是一种很好的方法。选择指数还是直方图？对需要直接访问的唯一数据项使用索引。虽然修改、删除和插入操作会产生额外的开销，但如果数据结构正确，索引可以为您提供快速访问。建议对不经常更新的数据使用直方图，例如过去十年左右的季度结果。结束语本文源于最近在OpenSource101会议上的一次演讲。该报告的介绍来自PHPUKConferenc的一个研讨会。查询调优是一个复杂的话题，每次我报告索引和直方图时，我都会发现需要改进的新领域。但每一份报告反馈也都表明，软件界有很多人对索引并不精通，而且经常使用不当。我觉得柱状图大概是出现时间短的缘故，一直没有出现索引使用上的错误。
