蚊章汇 - 查询
type: channel
link: https://linuxcn.bwsl.wang/tag/%E6%9F%A5%E8%AF%A2/
items: 如老手一般玩转 MySQL 查询

如老手一般玩转 MySQL 查询
type: item
date: 2021-08-15T18:49:16+08:00
description: 优化查询语句不过是一项简单的工程，而非什么高深的黑魔法。许多人将数据库查询语句的调优视作哈利波特小说中某种神秘的“黑魔法”；使用错误的咒语，数据就会从宝贵的资源变成一堆糊状物。实际上，对关系数据库系统的查询调优是一项简单的工程，其遵循的规则或启发式方法很容易理解。查询优化器会翻译你发送给 MySQL 实例的查询指令，然后将这些启发式方法和优化器已知的数据信息结合使用，确定获取所请求数据的最佳方式。再读一下后面这半句：“优化器已知的数据信息。”查询优化器需要对数据所在位置的猜测越少（即已知信息越多），它就可以越好地制定交付数据的计划。为了让优化器更好地了解数据，你可以考虑使用索引和直方图。正确使用索引和直方图可以大大提高数据库查询的速度。这就像如果你按照食谱做菜，就可以得到你喜欢吃的东西；但是假如你随意在该食谱中添加材料，最终得到的东西可能就不那么尽如人意了。基于成本的优化器大多数现代关系型数据库使用 基于成本的优化器 cost-based optimizer 来确定如何从数据库中检索数据。该成本方案是基于尽可能减少非常耗费资源的磁盘读取过程。数据库服务器内的查询优化器代码会在得到数据时对这些数据的获取进行统计，并构建一个获取数据的历史模型。但历史数据是可能会过时的。这就好像你去商店买你最喜欢的零食，然后突然发现零食涨价或者商店关门了。服务器的优化进程可能会根据旧信息做出错误的假设，进而制定出低效的查询计划。查询的复杂性可能会影响优化。优化器希望提供可用的最低成本查询方式。连接五个不同的表就意味着有 5 的阶乘（即 120）种可能的连接组合。代码中内置了启发式方法，以尝试对所有可能的选项进行快捷评估。MySQL 每次看到查询时都希望生成一个新的查询计划，而其他数据库（例如 Oracle）则可以锁定查询计划。这就是向优化器提供有关数据的详细信息至关重要的原因。要想获得稳定的性能，在制定查询计划时为查询优化器提供最新信息确实很有效。此外，优化器中内置的规则可能与数据的实际情况并不相符。没有更多有效信息的情况下，查询优化器会假设列中的所有数据均匀分布在所有行中。没有其他选择依据时，它会默认选择两个可能索引中较小的一个。虽然基于成本的优化器模型可以制定出很多好的决策，但最终查询计划并不是最佳方案的情况也是有可能的。查询计划是什么？ 查询计划 query plan 是指优化器基于查询语句产生的，提供给服务器执行的计划内容。查看查询计划的方法是在查询语句前加上 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 子句从在国家表中查找变成了在城市表中查找。优化器认为这两个改动会提供比原始查询更快的结果。索引在 MySQL 世界中，你会听到索引或键的概念。不过，索引是由键组成的，键是一种识别记录的方式，并且大概率是唯一的。如果将列设计为键，优化器可以搜索这些键的列表以找到所需的记录，而无需读取整个表。如果没有索引，服务器必须从第一列的第一行开始读取每一行数据。如果该列是作为唯一索引创建的，则服务器可以直接读取该行数据并忽略其余数据。索引的值（也称为基数）唯一性越强越好。请记住，我们在寻找更快获取数据的方法。MySQL 默认的 InnoDB 存储引擎希望你的表有一个主键，并按照该键将你的数据存储在 B+ 树中。“不可见列”是 MySQL 最近添加的功能，除非在查询中明确指明该不可见列，否则不会返回该列数据。例如，SELECT * FROM foo; 就不会返回任何不可见列。这个功能提供了一种向旧表添加主键的方…