Dave Stokes 发布的文章

优化查询语句不过是一项简单的工程，而非什么高深的黑魔法。

title=

许多人将数据库查询语句的调优视作哈利波特小说中某种神秘的“黑魔法”；使用错误的咒语，数据就会从宝贵的资源变成一堆糊状物。

实际上，对关系数据库系统的查询调优是一项简单的工程，其遵循的规则或启发式方法很容易理解。查询优化器会翻译你发送给 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; 就不会返回任何不可见列。这个功能提供了一种向旧表添加主键的方法，且无需为了包含该新列而重写所有查询语句。

更复杂的是，有多种类型的索引，例如函数索引、空间索引和复合索引。甚至在某些情况下，你还可以创建这样一个索引：该索引可以为查询提供所有请求的信息，从而无需再去访问数据表。

本文不会详细讲解各种索引类型，你只需将索引看作指向要查询的数据记录的快捷方式。你可以在一个或多个列或这些列的一部分上创建索引。我的医师系统就可以通过我姓氏的前三个字母和出生日期来查找我的记录。使用多列时要注意首选唯一性最强的字段，然后是第二强的字段，依此类推。“年-月-日”的索引可用于“年-月-日”、“年-月”和“年”搜索，但不适用于“日”、“月-日”或“年-日”搜索。考虑这些因素有助于你围绕如何使用数据这一出发点来设计索引。

直方图

直方图就是数据的分布形式。如果你将人名按其姓氏的字母顺序排序，就可以对姓氏以字母 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 Conferenc 的研讨会。查询调优是一个复杂的话题，每次我就索引和直方图作报告时，我都会找到新的可改进点。但是每次报告反馈也表明很多软件界中的人并不精通索引，并且时常使用错误。我想直方图大概由于出现时间较短，还没有出现像索引这种使用错误的情况。

via: https://opensource.com/article/21/5/mysql-query-tuning

作者：Dave Stokes 选题：lujun9972 译者：unigeorge 校对：wxy

本文由 LCTT 原创编译，Linux中国荣誉推出

不像 MySQL 的 MySQL：MySQL 文档存储介绍

MySQL 文档存储可以跳过底层数据结构创建、数据规范化和其它使用传统数据库时需要做的工作，直接存储数据。

MySQL 可以提供 NoSQL JSON 文档存储 Document Store 了，这样开发者保存数据前无需规范化 normalize 数据、创建数据库，也无需在开发之前就制定好数据样式。从 MySQL 5.7 版本和 MySQL 8.0 版本开始，开发者可以在表的一列中存储 JSON 文档。由于引入 X DevAPI，你可以从你的代码中移除令人不爽的结构化查询字符串，改为使用支持现代编程设计的 API 调用。

系统学习过结构化查询语言（SQL）、关系理论 relational theory 、集合 set 和其它关系数据库底层理论的开发者并不多，但他们需要一个安全可靠的数据存储。如果数据库管理人员不足，事情很快就会变得一团糟，

MySQL 文档存储允许开发者跳过底层数据结构创建、数据规范化和其它使用传统数据库时需要做的工作，直接存储数据。只需创建一个 JSON 文档集合 document collection ，接着就可以使用了。

JSON 数据类型

所有这一切都基于多年前 MySQL 5.7 引入的 JSON 数据类型。它允许在表的一行中提供大约 1GB 大小的列。数据必须是有效的 JSON，否则服务器会报错；但开发者可以自由使用这些空间。

X DevAPI

旧的 MySQL 协议已经历经差不多四分之一个世纪，已经显现出疲态，因此新的协议被开发出来，协议名为 X DevAPI。协议引入高级会话概念，允许代码从单台服务器扩展到多台，使用符合通用主机编程语言样式 common host-language programming patterns 的非阻塞异步 I/O。需要关注的是如何遵循现代实践和编码风格，同时使用 CRUD （Create、 Read、 Update、 Delete）样式。换句话说，你不再需要在你精美、纯洁的代码中嵌入丑陋的 SQL 语句字符串。

一个新的 shell 支持这种新协议，即所谓的 MySQL Shell。该 shell 可用于设置高可用集群 high-availability cluster 、检查服务器升级就绪状态 upgrade readiness 以及与 MySQL 服务器交互。支持的交互方式有以下三种：JavaScript，Python 和 SQL。

代码示例

下面的代码示例基于 JavaScript 方式使用 MySQL Shell，可以从 JS> 提示符看出。

下面，我们将使用用户 dstokes 、密码 password 登录本地系统上的 demo 库。db 是一个指针，指向 demo 库。

$ mysqlsh dstokes:password@localhost/demo
JS> db.createCollection("example")
JS> db.example.add(
      {
        Name: "Dave",
        State:  "Texas",
        foo : "bar"
      }
     )
JS>

在上面的示例中，我们登录服务器，连接到 demo 库，创建了一个名为 example 的集合，最后插入一条记录；整个过程无需创建表，也无需使用 SQL。只要你能想象的到，你可以使用甚至滥用这些数据。这不是一种代码对象与关系语句之间的映射器，因为并没有将代码映射为 SQL；新协议直接与服务器层打交道。

Node.js 支持

新 shell 看起来挺不错，你可以用其完成很多工作；但你可能更希望使用你选用的编程语言。下面的例子使用 world_x 示例数据库，搜索 _id 字段匹配 CAN. 的记录。我们指定数据库中的特定集合，使用特定参数调用 find 命令。同样地，操作也不涉及 SQL。

var mysqlx = require('@mysql/xdevapi');
mysqlx.getSession({             //Auth to server
        host: 'localhost',
        port: '33060',
        dbUser: 'root',
        dbPassword: 'password'
}).then(function (session) {    // use world_x.country.info
     var schema = session.getSchema('world_x');
     var collection = schema.getCollection('countryinfo');

collection                      // Get row for 'CAN'
  .find("$._id == 'CAN'")
  .limit(1)
  .execute(doc => console.log(doc))
  .then(() => console.log("\n\nAll done"));

  session.close();
})

下面例子使用 PHP，搜索 _id 字段匹配 USA 的记录：

<?PHP
// Connection parameters
  $user = 'root';
  $passwd = 'S3cret#';
  $host = 'localhost';
  $port = '33060';
  $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port;
  echo $connection_uri . "\n";

// Connect as a Node Session
  $nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// "USE world_x" schema
  $schema = $nodeSession->getSchema("world_x");
// Specify collection to use
  $collection = $schema->getCollection("countryinfo");
// SELECT * FROM world_x WHERE _id = "USA"
  $result = $collection->find('_id = "USA"')->execute();
// Fetch/Display data
  $data = $result->fetchAll();
  var_dump($data);
?>

可以看出，在上面两个使用不同编程语言的例子中，find 操作符的用法基本一致。这种一致性对跨语言编程的开发者有很大帮助，对试图降低新语言学习成本的开发者也不无裨益。

支持的语言还包括 C、Java、Python 和 JavaScript 等，未来还会有更多支持的语言。

从两种方式受益

我会告诉你使用 NoSQL 方式录入的数据也可以用 SQL 方式使用？换句话说，我会告诉你新引入的 NoSQL 方式可以访问旧式关系型表中的数据？现在使用 MySQL 服务器有多种方式，作为 SQL 服务器，作为 NoSQL 服务器或者同时作为两者。

via: https://opensource.com/article/18/6/mysql-document-store

作者：Dave Stokes 选题：lujun9972 译者：pinewall 校对：wxy

本文由 LCTT 原创编译，Linux中国荣誉推出