MySQL8-查询性能调优教程-三-

MySQL8 查询性能调优教程（三）

原文：MySQL 8 Query Performance Tuning

协议：CC BY-NC-SA 4.0

十一、MySQL 工作台

MySQL Workbench 是 Oracle 的图形用户界面，用于查询和管理 MySQL 服务器。它可以被看作是使用 MySQL 的两把瑞士军刀之一，另一把是 MySQL Shell，将在下一章讨论。

MySQL Workbench 的主要特性是查询模式，在这种模式下可以执行查询。然而，还有几个其他功能，如性能报告、可视化解释、管理配置和检查模式的能力等等。

如果将 MySQL Workbench 与 MySQL Enterprise Monitor 进行比较，那么 MySQL Enterprise Monitor 专用于监控，是一个服务器解决方案，而 MySQL Workbench 是一个桌面解决方案，主要是一个与 MySQL 服务器协同工作的客户端。类似地，MySQL Workbench 中包含的监控都是特定的监控，而 MySQL Enterprise Monitor 作为一个服务器解决方案包含了对存储历史数据的支持。

本章将介绍 MySQL Workbench，并介绍其安装、基本用法以及如何创建 EER 图。性能报告和直观解释将在后面的章节中介绍。

Tip

如果你已经熟悉 MySQL Workbench，可以考虑跳过这一章或者略读。

装置

MySQL Workbench 的安装方式与其他 MySQL 程序相同，只是只支持使用软件包管理器(因此没有独立安装)。MySQL Workbench 版本号遵循 MySQL Server 版本，因此 MySQL Workbench 8.0.18 与 MySQL Server 8.0.18 同时发布。MySQL Workbench 版本支持在发布时仍在维护的 MySQL 服务器版本，因此 MySQL Workbench 8.0.18 支持连接到 MySQL 服务器 5.6、5.7 和 8。

Tip

最好使用最新的 MySQL Workbench 版本。你可以在 https://dev.mysql.com/doc/mysql-compat-matrix/en/ 看到 MySQL 工具的兼容性。

本节将展示如何在 Microsoft Windows、“Enterprise Linux 7”(Oracle Linux、Red Hat Enterprise Linux 和 CentOS)以及 Ubuntu 19.10 上安装 MySQL Workbench 的示例。其他 Linux 平台在概念上类似于这两个 Linux 示例。

Tip

如果您是 MySQL 客户，建议从 My Oracle Support (MOS)中的 Patches & Updates 下载 MySQL Workbench。这将使您能够访问商业版的 MySQL Workbench，它具有一些额外的功能，如审计日志检查器和 MySQL 企业备份(MEB)的图形用户界面。

微软视窗软件

在 Microsoft Windows 上，安装 MySQL Workbench 的首选方式是使用 MySQL Installer for Windows。如果您安装了其他 MySQL 产品，您可能已经安装了 MySQL 安装程序，在这种情况下，您可以跳过这些说明的第一步，而是点击主屏幕上的添加，这将带您进入图 11-5 的位置。

可以从 https://dev.mysql.com/downloads/installer/ 下载 MySQL 安装程序。图 11-1 显示下载部分。

图 11-1

MySQL 工作台下载页面

安装程序有两种选择。第一个称为 web 安装程序(mysql-installer-web-community-8.0.18.0.msi)的只是 MySQL 安装程序，而第二个(mysql-installer-community-8.0.18.0.msi)也包括 MySQL 服务器。如果您还打算安装 MySQL Server，那么选择包含 MySQL 安装程序和 MySQL Server 的下载文件是有意义的，因为这样可以避免以后等待安装程序下载 MySQL Server 安装文件。此示例假设您选择了 web 安装程序。

你点击下载按钮进入下载。如果您没有登录，它将带您到开始下载页面，您可以在登录和立即开始下载之间进行选择。如图 11-2 所示。

图 11-2

下载 MySQL Workbench 的第二步

如果您已经有帐户，您可以登录。否则，您可以选择注册 Oracle 帐户。您也可以选择不登录就下载安装程序，方法是单击不，谢谢，只需启动我的下载链接。

下载完成后，启动下载的文件。除了确认您将允许安装程序和 MySQL 安装程序修改已安装的程序之外，安装 MySQL 安装程序不需要任何操作。安装完成后，MySQL 安装程序会自动启动并检测已经使用 MSI 安装程序安装的 MySQL 程序，如图 11-3 所示。

图 11-3

MySQL 安装程序检测以前安装的 MySQL 程序

如果您没有安装任何 MySQL 程序，则会出现一个屏幕，要求您确认是否同意许可条款。请在继续之前仔细阅读许可条款。如果您可以接受许可，请勾选我接受许可条款复选框，然后点击标有下一个 ➤的按钮继续。

下一步是选择安装什么。设置类型选择屏幕如图 11-4 所示。

图 11-4

MySQL 安装程序安装类型选择器

您可以在几个包中进行选择，比如 developer bundle(称为 Developer Default )，它安装了开发环境中通常使用的产品。当您选择安装类型时，屏幕右侧的描述包括将要安装的产品列表。对于此示例，将使用自定义安装类型。

下一步是选择要安装的产品。使用如图 11-5 所示的选择器。

图 11-5

选择要安装的内容

您可以在应用下的可用产品列表中找到 MySQL Workbench。单击指向右侧的箭头，将 MySQL Workbench 添加到要安装的产品和功能列表中。随意选择附加产品；对于这本书，建议还包括 MySQL Shell。添加完所有需要的产品后，点击下一步 ➤继续。

以下屏幕提供了将要安装的产品的摘要。点击执行开始安装。如果 MySQL 安装程序没有本地副本，安装过程包括下载产品。安装可能需要一段时间才能完成。完成后，点击下一步 ➤继续。最后一个屏幕列出了已安装的程序，并让您选择启动 MySQL Workbench 和 MySQL Shell。点击完成关闭 MySQL 安装程序。

如果您稍后想要安装更多产品或执行升级或删除产品，您可以再次启动 MySQL 安装程序，这将带您进入 MySQL 安装程序主屏幕，如图 11-6 所示。

图 11-6

MySQL 安装程序主屏幕

您可以在屏幕的最右边选择想要执行的操作。这些行动是

• **添加:**安装产品和功能。

• **修改:**改变现有产品的安装。这主要对 MySQL 服务器有用。

• **升级:**升级已经安装的产品。

• **卸载:**卸载一个产品。

• **目录:**更新 MySQL 安装程序的可用 MySQL 产品列表。

这五个动作允许您执行 MySQL 产品生命周期中所需的所有步骤。

企业版 Linux 7

如果您使用的是 Linux，您可以使用包管理器安装 MySQL Workbench。在 Oracle Linux、Red Hat Enterprise Linux 和 CentOS 7 上，首选的软件包管理器是yum，因为它将帮助解决您安装或升级的软件包的依赖性。MySQL 为其社区产品提供了一个 yum 知识库。这个例子将展示如何安装它并使用它来安装 MySQL Workbench。

您可以在 https://dev.mysql.com/downloads/repo/yum/ 找到存储库定义的 URL。还有 APT 和 SUSE 的存储库。选择与您的 Linux 发行版相对应的文件，然后点击下载。图 11-7 显示了企业版 Linux 7 的文件。

图 11-7

Enterprise Linux 7 的存储库定义下载

如果您没有登录，它会将您带到第二个屏幕，就像在 Microsoft Windows 上安装 MySQL Workbench 的例子一样。这将允许您登录到您的 Oracle Web 帐户，创建一个帐户，或在不登录的情况下下载。或者下载 RPM 文件并保存在您想要安装的目录中，或者右键单击下载按钮(如果您已登录)或不，谢谢，只需启动我的下载链接并复制 URL，如图 11-8 所示。

图 11-8

将链接复制到存储库安装文件

您现在可以安装清单 11-1 中所示的存储库定义。

shell$ wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
...
HTTP request sent, awaiting response... 200 OK
Length: 26024 (25K) [application/x-redhat-package-manager]
Saving to: 'mysql80-community-release-el7-3.noarch.rpm'

100%[=========================>] 26,024      --.-K/s   in 0.001s

2019-08-18 12:13:47 (20.6 MB/s) - 'mysql80-community-release-el7-3.noarch.rpm' saved [26024/26024]

shell$ sudo yum install mysql80-community-release-el7-3.noarch.rpm
Loaded plugins: langpacks, ulninfo
Examining mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch
Marking mysql80-community-release-el7-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-3 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================
 Package
      Arch   Version
                   Repository                               Size
=================================================================
Installing:
 mysql80-community-release
      noarch el7-3 /mysql80-community-release-el7-3.noarch  31 k

Transaction Summary
=================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-3.noarch        1/1
  Verifying  : mysql80-community-release-el7-3.noarch        1/1

Installed:
  mysql80-community-release.noarch 0:el7-3

Complete!

Listing 11-1Installing the MySQL community repository

MySQL Workbench 需要 EPEL 仓库中的一些包。在 Oracle Linux 7 上，您可以像这样启用它

sudo yum install oracle-epel-release-el7

在 Red Hat Enterprise Linux 和 CentOS 上，您需要从 Fedora 下载存储库定义:

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

sudo yum install epel-release-latest-7.noarch.rpm

您现在可以安装 MySQL Workbench 了，如清单 11-2 所示。

shell$ sudo yum install mysql-workbench
...
Dependencies Resolved

================================================================
 Package        Arch   Version      Repository             Size
================================================================
Installing:
 mysql-workbench-community
                x86_64 8.0.18-1.el7 mysql-tools-community  26 M

Transaction Summary
================================================================
Install  1 Package

Total download size: 26 M
Installed size: 116 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/mysql-tools-community/packages/mysql-workbench-community-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Public key for mysql-workbench-community-8.0.18-1.el7.x86_64.rpm is not installed
mysql-workbench-community-8.0.18-1\.         |  31 MB  00:14
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql80-community-release-el7-3.noarch (@/mysql80-community-release-el7-3.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-workbench-community-8.0.18-1.el7.x86   1/1
  Verifying  : mysql-workbench-community-8.0.18-1.el7.x86   1/1

Installed:
  mysql-workbench-community.x86_64 0:8.0.17-1.el7

Complete!

Listing 11-2Installing MySQL Workbench on Enterprise Linux 7

您的输出看起来可能会有所不同，例如，根据您已经安装的包，可能会引入依赖项。第一次从 MySQL 数据库安装软件包时，会要求您接受用于验证下载的软件包的 GPG 密钥。如果您从 Fedora 安装了 EPEL 存储库，那么您还需要接受来自该存储库的 GPG 密钥。

Debian 和 Ubuntu

在 Debian 和 Ubuntu 上安装 MySQL Workbench 遵循与上例相同的原则。对于这里演示的步骤，将使用 Ubuntu 19.10。

Tip

参见 https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ 获取使用 MySQL APT 库的完整文档。

对于 Debian 和 Ubuntu，需要安装 MySQL APT 库，定义文件可以从 https://dev.mysql.com/downloads/repo/apt/ 下载。在撰写本文时，只有一个文件可用——见图11-9——它是独立于架构的，适用于所有支持的 Debian 和 Ubuntu 版本。

图 11-9

APT 存储库配置文件

如果您没有登录，您将被带到一个屏幕，您可以在登录和立即开始下载之间进行选择。要么下载 DEB 包，要么右键点击下载按钮(如果你已经登录)或者不用谢，直接启动我的下载链接，复制网址如图 11-10 所示。

图 11-10

将链接复制到存储库安装文件

您现在可以安装 MySQL 存储库，如清单 11-3 所示。

shell$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.deb
...
Connecting to repo.mysql.com (repo.mysql.com)|23.202.169.138|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 35564 (35K) [application/x-debian-package]
Saving to: 'mysql-apt-config_0.8.14-1_all.deb'

mysql-apt-config_0\. 100%[==================>]  34.73K  --.-KB/s    in 0.02s

2019-10-26 17:16:46 (1.39 MB/s) - 'mysql-apt-config_0.8.14-1_all.deb' saved [35564/35564]

shell$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb
Selecting previously unselected package mysql-apt-config.
(Reading database ... 161301 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.14-1_all.deb ...
Unpacking mysql-apt-config (0.8.14-1) ...
Setting up mysql-apt-config (0.8.14-1) ...
Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK

Listing 11-3Installing the DEB package definition

在第二步中(dpkg -i命令)，您可以选择哪些 MySQL 产品应该可以通过存储库获得。此设置的屏幕如图 11-11 所示。

图 11-11

MySQL APT 存储库的包配置

默认情况下，启用 MySQL 服务器和集群以及工具和连接器。对于 MySQL 服务器和集群，您还可以选择想要使用的版本，默认值为 8。为了安装 MySQL Shell，你需要确保 MySQL 工具&连接器被设置为启用。完成更改后，选择确定。

在开始使用存储库之前，您需要对apt-get执行更新命令:

shell$ sudo apt-get update
Hit:1 http://repo.mysql.com/apt/ubuntu eoan InRelease
Hit:2 http://au.archive.ubuntu.com/ubuntu eoan InRelease
Hit:3 http://au.archive.ubuntu.com/ubuntu eoan-updates InRelease
Hit:4 http://au.archive.ubuntu.com/ubuntu eoan-backports InRelease
Hit:5 http://security.ubuntu.com/ubuntu eoan-security InRelease
Reading package lists... Done

现在可以使用 apt-get 的 install 命令安装 MySQL 产品。清单 11-4 展示了一个安装 MySQL Workbench 的例子(注意包名是mysql-workbench-community——结尾的“-community”很重要)。

shell$ sudo apt-get install mysql-workbench-community
Reading package lists... Done
Building dependency tree
Reading state information... Done
...
Setting up mysql-workbench-community (8.0.18-1ubuntu19.10) ...
Setting up libgail-common:amd64 (2.24.32-4ubuntu1) ...
Processing triggers for libc-bin (2.30-0ubuntu2) ...
Processing triggers for man-db (2.8.7-3) ...
Processing triggers for shared-mime-info (1.10-1) ...
Processing triggers for desktop-file-utils (0.24-1ubuntu1) ...
Processing triggers for mime-support (3.63ubuntu1) ...
Processing triggers for hicolor-icon-theme (0.17-2) ...
Processing triggers for gnome-menus (3.32.0-1ubuntu1) ...

Listing 11-4Installing MySQL Workbench from the APT repository

输出非常详细，包括安装 MySQL Workbench 所需的其他包的更改列表。软件包列表取决于您已经安装了什么。

您现在可以开始使用 MySQL Workbench 了。

创建连接

第一次启动 MySQL Workbench 时，您需要定义到 MySQL 服务器实例的连接。如果您安装了 MySQL 通知程序 ¹ ，MySQL Workbench 将自动为 root 用户创建一个到 MySQL 通知程序所监控的每个实例的连接。

您也可以根据需要创建连接。一种选择是从 MySQL Workbench connections 屏幕上进行，如图 11-12 所示。

图 11-12

MySQL 工作台连接屏幕

点击左上角的图标可以访问连接屏幕，该图标显示了一个带有 dolphin 的数据库。下面的图标带有由线连接的表，将带您进入数据库建模功能，三个图标中的最后一个图标打开数据迁移功能的选项卡。

该屏幕截图显示了 connections 屏幕，其中包含欢迎消息和一个已经存在的连接。您可以右键单击连接来访问连接选项，包括打开连接(创建到 MySQL 实例的连接)、编辑连接、将其添加到组中等等。

您可以通过点击 MySQL 连接右侧的+来添加一个新连接。配置连接的对话框如图 11-13 所示。创建新连接和编辑现有连接的对话框非常相似。

图 11-13

用于创建新连接的对话框

您可以用自己选择的名称来命名连接。它是一个自由格式的字符串，只是用来更容易地识别连接的目的。剩下的选项都是常用的连接选项。

建立连接后，您可以在连接屏幕上双击它来创建连接。

使用 MySQL 工作台

MySQL Workbench 中最常用的特性是执行查询的能力。这是通过“查询”选项卡完成的，除了能够执行查询之外，该选项卡还包括几个功能。这些特性包括显示结果集、获得名为 Visual Explain 的查询计划的可视化表示、获得上下文帮助、重新格式化查询等等。本节将从概述开始，介绍一些特性。

概观

“查询”选项卡包含两个区域，一个是编辑器，您可以在其中编写查询，另一个是查询结果。还支持显示上下文帮助和查询统计。从技术上讲，这两个附加区域不是查询选项卡的一部分，但是由于它们通常与查询选项卡一起使用，所以这里也将对它们进行讨论。

图 11-14 显示了 MySQL Workbench 的查询选项卡和最重要的特性编号。

图 11-14

MySQL 工作台和查询选项卡

标记为①的区域是您编写查询的地方。您可以在这里保存几个查询，MySQL Workbench 会保存它们，所以当您再次打开连接时，它们会被恢复。这使得存储最常用的查询非常方便。

您可以使用标记为②的三个闪电图标之一来执行一个或多个查询。左侧图标是一个简单的闪电符号，用于执行在查询编辑器部件中选择的一个或多个查询。这与使用键盘快捷键 Ctrl+Shift+Enter 相同。带有闪电符号和光标的中间图标执行光标所在位置的查询。使用此图标等同于在编辑器中使用快捷键 Ctrl+Enter。第三个图标在闪电符号前面有一个放大镜，它为当前光标所在的查询创建表单中的查询计划。显示查询计划的默认方式是可视化的解释图。也可以使用快捷键 Ctrl+Alt+X 来获取查询计划。

结果显示在查询编辑器③的下方，您可以使用查询结果右侧的项目在几种格式之间进行选择。最后一项是执行计划 ④，如果您直接从查询编辑器中请求，它会以相同的方式显示查询的查询计划。

query 选项卡下面是 output frame ⑤，默认情况下，它显示上次执行的查询的统计信息。这包括执行查询的时间、查询、找到的行数以及执行查询需要多长时间。右边是一个带有 SQL 附加物⑥的框架，默认显示上下文帮助。您可以启用自动上下文帮助，或者使用帮助文本上方的图标手动请求帮助。

配置

MySQL Workbench 有几个设置可以更改，从颜色到行为和程序路径，比如 MySQL Workbench 依赖的mysqldump。

有几种方法可以达到如图 11-15 所示的设置。该图显示了 MySQL Workbench 窗口的左上和右上部分。

图 11-15

访问 MySQL 工作台首选项

在左侧，您可以通过使用编辑并转到底部的首选项项目，从菜单中获得首选项。或者，您可以单击窗口右侧的齿轮图标。无论哪种方式，您都会看到如图 11-16 所示的首选项弹出窗口。

图 11-16

MySQL 工作台首选项

通用编辑器设置包括诸如语法检查器考虑的 SQL 模式以及是否使用空格或制表符进行缩进的设置。 SQL 编辑器设置包括是否使用安全设置、是否保存编辑器、编辑器和查询页签的一般行为。如果您不想使用捆绑的二进制文件，则管理设置指定要使用的路径，包括用于mysqldump的路径。建模设置用于数据库建模功能。字体&颜色设置允许你改变 MySQL 工作台的视觉外观。当您使用需要 SSH 连接到远程主机的功能时，将使用 SSH 设置。最后，其他设置包括一些不适合其他类别的设置，例如是否应在连接的开始屏幕上显示欢迎消息。

这些设置包括安全设置。那些是什么？

安全设置

MySQL Workbench 默认启用了两个安全设置，以帮助防止更改或删除表中的所有行，并避免获取太多行。安全设置意味着UPDATE和DELETE语句如果没有WHERE子句就会被阻塞，而SELECT语句添加了LIMIT 1000(可以配置最大行数)。用于UPDATE和DELETE语句的WHERE子句不能是无关紧要的子句。

Caution

不要因为启用了安全设置就自满。使用WHERE子句时，UPDATE和DELETE语句仍然会造成很大损害，使用LIMIT 1000的SELECT查询仍然会要求 MySQL 检查更多的行。

通常最好是启用这些设置，但是对于某些查询，您需要更改这些设置，以便它们按预期工作。如前所述，可以在设置中更改SELECT限值。在 SQL 编辑器下的 SQL 执行子菜单下设置限制。或者，更简单的方法是使用编辑器上方的下拉框，如图 11-17 所示。

图 11-17

改变SELECT极限

以这种方式更改限制会更新相同的设置，就像您浏览首选项一样。

可以在最下面的 SQL 编辑器设置中更改UPDATE和DELETE安全设置。除非您真的需要更新或删除表中的所有行，否则建议保持该状态。请注意，禁用设置需要重新连接。

重新格式化查询

MySQL Workbench 的一个很好的特性是查询美化工具，它通常不会引起太多关注。这对于查询调优也很有用，因为格式良好的查询可以更容易理解查询正在做什么。

查询美化器接受一个查询，将选择列表、表和过滤器拆分成单独的行，并添加缩进。图 11-18 显示了一个例子。

图 11-18

查询美化功能

第一个查询是原始查询，整个查询在一行中。第二个查询是重新格式化的查询。对于像本例中这样的简单查询，美化没有什么价值，但是对于更复杂的查询，它可以使查询更容易阅读。

默认情况下，美化包括将 SQL 关键字改为大写。您可以在首选项中的 SQL 编辑器设置的查询编辑器子菜单中更改这是否应该发生。

能效比图表

最后一个要探讨的特性是对模式逆向工程和创建增强的实体关系(EER)图的支持。这是了解您正在使用的模式的一个有用的方法。如果已经定义了外键，MySQL Workbench 将使用这些定义将表链接在一起。

你可以从数据库菜单选项启动逆向工程向导，然后选择逆向工程。或者，Ctrl+R 键盘组合也可以带您去那里。如图 11-19 所示。

图 11-19

打开逆向工程特征

该向导将引导您完成导入模式的步骤，首先选择要使用的存储连接，或者手动配置连接。下一步是连接并导入第三步中显示的可用模式列表。在这里，您选择一个或多个模式进行逆向工程，如图 11-20 所示。

图 11-20

选择要实施反向工程的架构

在这个例子中，选择了world模式。接下来的步骤获取模式对象，并允许您过滤要包含的对象。最后，对象被导入并放置在图中，并显示确认信息。由此产生的能效比图如图 11-21 所示。

Tip

如果 MySQL Workbench 在创建图表时崩溃，尝试打开菜单中的编辑➤配置… ➤建模，并选中强制使用基于软件的 EER 图表渲染选项。

图 11-21

world数据库的 EER 图

该图显示了world数据库中的三个表。当鼠标悬停在某个表上方时，对于子表，与其他表的关系将以绿色突出显示，对于父表，以蓝色突出显示。这允许您快速探索表之间的关系，从而在您需要调优查询时获得至关重要的知识。

摘要

本章介绍了 MySQL Workbench，它是 MySQL 的图形用户界面解决方案。展示了如何安装 MySQL Workbench 和创建连接。然后给出了主查询视图的概述，并展示了如何配置 MySQL Workbench。默认情况下，如果没有真正的WHERE子句，就不能执行UDPATE和DELETE语句，并且SELECT查询被限制为 1000 行。

讨论的两个特性是查询美化和 EER 图。这些不是唯一的特性，后面的章节将展示性能报告和可视化解释查询计划图的例子。

下一章将讨论 MySQL Shell，它是 MySQL 提供的两把“瑞士军刀”中的第二把。

Footnotes [1](#Fn1_source)

www.mysql.com/why-mysql/windows/notifier/

 

十二、MySQL Shell

MySQL Shell 是第二代命令行客户端，与传统的mysql命令行客户端相比，它支持 X 协议以及 Python 和 JavaScript 语言。它还附带了几个实用程序，并且高度可扩展。这使得它不仅是日常任务的好工具，也是研究性能问题的好工具。

本章首先概述了 MySQL Shell 提供的功能，包括内置的帮助和丰富的提示。本章的第二部分介绍了如何通过使用外部代码模块、报告基础设施和插件来扩展 MySQL Shell 的功能。

概观

第一个正式发布的 MySQL Shell 版本是在 2017 年，所以它仍然是 MySQL 工具箱中非常新的工具。然而，它已经有了一大堆远远超过传统的mysql命令行客户端的功能。这些特性并不局限于使用 MySQL Shell 作为 MySQL InnoDB 集群解决方案的一部分所需的特性；还有几个特性对于日常数据库管理任务和性能优化非常有用。

MySQL Shell 相对于mysql命令行客户端的一个优势是，MySQL Shell 编辑器在 Linux 和 Microsoft Windows 上的行为是相同的，因此如果您在这两个平台上工作，您将获得一致的用户体验。这意味着 Ctrl+D 在 Linux、macOS 和 Microsoft Windows 上都存在 shell，Ctrl+W 删除前面的单词，依此类推。

Tip

查尔斯·贝尔(本书技术审稿人，MySQL 开发者)撰写了《介绍 MySQL Shell*(Apress)一书，全面介绍了 MySQL Shell: www.apress.com/gp/book/9781484250822 。此外，本书的作者已经发表了几篇关于 MySQL Shell 的博客。 https://mysql.wisborg.dk/mysql-shell-blogs/见。*

这一节将介绍 MySQL Shell 的安装、调用以及一些基本特性。然而，不可能详细介绍 MySQL Shell 的所有特性。由于您正在使用 MySQL Shell，建议您通过 https://dev.mysql.com/doc/mysql-shell/en 查阅在线手册以获取更多信息。

安装 MySQL Shell

MySQL Shell 的安装方式与其他 MySQL 产品相同(MySQL Enterprise Monitor 除外)。可以从 https://dev.mysql.com/downloads/shell/ 下载；它可用于 Microsoft Windows、Linux 和 macOS，并作为源代码提供。对于微软 Windows，也可以通过 MySQL Installer 安装。

如果您使用本机包格式和 MySQL Installer for Microsoft Windows 安装 MySQL Shell，除了名称之外，安装说明与 MySQL Workbench 相同。详情请见上一章。

您还可以在 Microsoft Windows 上使用 ZIP 归档文件，或者在 Linux 和 macOS 上使用 TAR 归档文件来安装 MySQL Shell。如果您选择该选项，您只需解压下载的文件，就大功告成了。

调用 MySQL Shell

MySQL Shell 使用安装目录的bin目录中的mysqlsh(或者微软 Windows 上的mysqlsh.exe)二进制文件调用。当您使用本地包安装 MySQL Shell 时，二进制文件将在您的PATH环境变量中，因此操作系统可以找到它，而无需您显式提供路径。

这意味着启动 MySQL Shell 最简单的方法就是执行mysqlsh:

shell> mysqlsh
MySQL Shell 8.0.18

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS>

该提示看起来与输出中的不同，因为默认提示不能完全用纯文本表示。与mysql命令行不同，MySQL Shell 不要求存在连接，默认情况下不创建任何连接。

创建连接

有几种方法可以为 MySQL Shell 创建连接，包括从命令行和 MySQL Shell 内部。

如果您在调用mysqlsh时添加了任何与连接相关的参数，那么 MySQL Shell 将创建一个连接作为启动的一部分。任何未指定的连接选项都将使用其默认值。例如，要作为root MySQL 用户使用默认端口(以及 Linux 和 macOS 套接字)值连接到本地主机上的 MySQL 实例，只需指定--user参数:

shell> mysqlsh --user=root
Please provide the password for 'root@localhost': ********
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.18

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 39581 (X protocol)
Server version: 8.0.18 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL  localhost:33060+ ssl  JS >

第一次连接时，会要求您输入该帐户的密码。如果 MySQL Shell 在路径中找到了mysql_config_editor命令，或者您在 Microsoft Windows 上，MySQL Shell 可以使用 Windows keyring 服务，MySQL Shell 将为您保存密码，因此您以后不需要输入密码。

或者，您可以使用 URI 来指定连接选项，例如:

shell> mysqlsh root@localhost:3306?schema=world

MySQL Shell 启动后，请注意提示符是如何变化的。MySQL Shell 有一个自适应的提示，它会根据您的连接状态而变化。默认提示包括您所连接的端口号。如果您连接到 MySQL Server 8，那么使用的默认端口是 33060，而不是端口 3306，因为当服务器支持 X 协议而不是传统的 MySQL 协议时，MySQL Shell 默认使用 X 协议。这就是端口号不是您所期望的原因。

您还可以在 MySQL Shell 中创建(或更改)连接。您甚至可以拥有多个连接，因此您可以同时处理两个或多个实例。创建会话有几种方法，包括表 12-1 中列出的方法。该表还包括如何设置和检索全局会话。语言命令栏显示根据使用的语言模式调用的命令或方法。

表 12-1

创建和使用连接的各种方法

|

方法

|

语言命令

|

描述

| | --- | --- | --- | | 全球会议 | 所有模式:\connect(或简称\c | 创建全局会话(默认会话)。这相当于在mysql命令行客户端中的连接。 | | 全体会议 | JavaScript:mysqlx.getSession()Python:mysqlx.get_session() | 返回会话，以便将其赋给变量。可用于使用 X 协议和经典协议的连接。 | | 经典会话 | JavaScript:mysql.getClassicSession()Python:mysql.get_classic_session() | 类似于常规会话，但总是返回经典会话。 | | 设置全局会话 | JavaScript:shell.setSession()Python:shell.set_session() | 从包含会话的变量设置全局会话。 | | 获取全局会话 | JavaScript:shell.getSession()Python:shell.get_session() | 返回全局会话，因此可以将其赋给一个变量。 | | 再连接 | 所有模式:\reconnect | 使用与现有全局连接相同的参数重新连接。 |

创建会话的所有命令和方法都支持格式为[scheme://][user[:password]@]<host[:port]|socket>[/schema][?option=value&option=value...]的 URI。这些方法还支持在字典中提供选项。如果您不包括密码，并且 MySQL Shell 没有存储该帐户的密码，系统将提示您以交互方式输入密码(与传统的命令行客户端不同，MySQL Shell 可以在命令执行期间提示输入信息)。例如，作为myuser用户连接到localhost

MySQL JS> \connect myuser@localhost
Creating a session to 'myuser@localhost'
Please provide the password for 'myuser@localhost': *******

语言模式提到过几次。下一小节将介绍如何使用它。

语言模式

MySQL Shell 的一个最大的特点是，您不局限于执行 SQL 语句。您拥有 JavaScript 和 Python 的全部能力，当然还有 SQL 语句。这使得 MySQL Shell 在自动化任务方面非常强大。

您一次只能在一种语言模式下工作，尽管可以通过 JavaScript 和 Python 中的 API 执行查询。表 12-2 总结了如何从命令行和 MySQL Shell 中选择想要使用的语言模式。

表 12-2

选择 MySQL Shell 语言模式

|

方式

|

命令行

|

MySQL Shell

| | --- | --- | --- | | Java Script 语言 | --js | \js | | 计算机编程语言 | --py | \py | | 结构化查询语言 | --sql | \sql |

默认模式是 JavaScript。提示符反映了您所处的语言模式，因此您总是知道您使用的是哪种模式。

Tip

在 MySQL Shell 8.0.16 和更高版本中，您可以在 Python 和 JavaScript 模式下为命令添加前缀\sql，这使得 MySQL Shell 将命令作为 SQL 语句执行。

在列出如何创建连接时，需要注意的一点是，MySQL Shell(如 X DevAPI)试图保持该语言通常使用的命名约定。这意味着在 JavaScript 模式下，函数和方法使用 camel case(例如，getSession())，而在 Python 模式下使用 snake case ( get_session())。如果您使用内置帮助，帮助将反映您所处的语言模式的名称。

内置帮助

很难掌握 MySQL Shell 的所有特性以及如何使用它们。幸运的是，有一个广泛的内置帮助功能，让您不必每次都回到在线手册就可以获得关于这些功能的信息。

如果您使用--help参数执行mysqlsh，您将获得关于所有支持的命令行参数的信息。启动 shell 后，您还可以获得关于命令、对象和方法的帮助。使用所有语言模式下可用的\h、\?或\help命令获得最顶层的帮助。这里列出了命令和全局对象，以及如何获得进一步的帮助。

第二级帮助是针对命令和全局对象的。您可以使用其中一个帮助命令来指定全局对象的命令名，以获取有关该命令或对象的更多信息。例如:

mysql-js> \h \connect
NAME
      \connect - Connects the shell to a MySQL server and assigns the global session.

SYNTAX
      \connect [<TYPE>] <URI>

      \c [<TYPE>] <URI>

DESCRIPTION
...

最终级别的帮助是针对全局对象的功能。全局对象和全局对象的模块都有一个为对象或模块提供帮助的help()方法。help()方法也可以将模块或对象的方法名作为字符串，该字符串将返回该方法的帮助。一些例子是(输出被省略，因为它非常详细，建议您自己尝试命令以查看返回的帮助文本):

MySQL JS> \h shell

MySQL JS> shell.help()

MySQL JS> shell.help('reconnect')

MySQL JS> shell.reports.help()

MySQL JS> shell.reports.help('query')

前两个命令检索相同的帮助文本。熟悉帮助特性是值得的，因为它可以大大提高您使用 MySQL Shell 的效率。

帮助的上下文感知比检测全局对象是否存在以及方法名称是否遵循 JavaScript 或 Python 约定更进一步。考虑一个关于“选择”的帮助请求你的意思有几种可能。它可以是 X DevAPI 中的select()方法之一，或者您可以想到SELECT SQL 语句。如果您在 SQL 模式下请求帮助，MySQL Shell 会认为您指的是 SQL 语句。然而，在 Python 和 JavaScript 模式下，你会被问到你指的是哪一个:

MySQL Py> \h select
Found several entries matching select

The following topics were found at the SQL Syntax category:

- SQL Syntax/SELECT

The following topics were found at the X DevAPI category:

- mysqlx.Table.select
- mysqlx.TableSelect.select

For help on a specific topic use: \? <topic>

e.g.: \? SQL Syntax/SELECT

MySQL Shell 可以在 SQL 模式下为SELECT语句提供帮助而不考虑 X DevAPI 的原因是 X DevAPI 方法只能从 Python 和 JavaScript 中访问。另一方面,“select”的三种含义在 Python 和 JavaScript 模式中都有意义。

如前所述，存在几个全局对象。那些是什么？

内置全局对象

MySQL Shell 使用全局对象对特性进行分组。使 MySQL Shell 如此强大的许多功能都可以在全局对象中找到。正如您将在“插件”部分看到的，也可以添加您自己的全局对象。

内置的全局对象包括

• db : 设置默认模式后，db保存默认模式的 X DevAPI 模式对象。X DevAPI 表对象可以作为db对象的属性找到(除非表或视图名称与现有属性相同)。会话对象也可以从db对象中获得。

• dba : 用于管理 MySQL InnoDB 集群。

• mysql : 用于使用经典 MySQL 协议连接到 MySQL。

• mysqlx : 用于使用 MySQL X 协议会话。

• session : 用于处理当前全局会话(连接到 MySQL 实例)。

• shell : 各种通用方法和属性。

• util : 升级检查器、导入 JSON 数据、将 CSV 文件中的数据导入关系表等各种实用程序。

MySQL Shell 的概述到此结束。接下来，您将了解关于提示以及如何定制它的更多信息。

提示

MySQL Shell 区别于传统命令行客户端的一个特性是丰富的提示，它不仅可以轻松查看您正在使用的主机和模式，还可以添加一些信息，例如您是否连接到生产实例、是否使用了 SSL 以及定制字段。

内置提示

MySQL Shell 安装附带了几个预定义的提示模板，您可以从中进行选择。默认情况下，使用提供连接信息并支持 256 种颜色的提示，但也有更简单的提示。

提示定义模板的位置取决于您安装 MySQL Shell 的方式。该位置的示例包括

• **ZIP 和 TAR 归档:**归档中的share/mysqlsh/prompt目录。

• Oracle Linux 7 上的 RPM:/usr/share/mysqlsh/prompt/

• 微软 Windows 上的 MySQL 安装程序: C:\Program Files\MySQL\MySQL Shell 8.0\share\mysqlsh\prompt

提示定义是 JSON 文件，MySQL Shell 8.0.18 中包含的定义是

• prompt_16.json : 一个彩色提示限于使用 16/8 色 ANSI 颜色和属性。

• prompt_256.json : 提示使用 256 种索引色。这是默认情况下使用的。

• prompt_256inv.json : 和prompt_256.json一样，但是有一个“不可见”的背景色(只是和终端用的一样)和不同的前景色。

• prompt_256pl.json : 与prompt_256.json相同，但带有额外的符号。这需要电力线修补字体，例如随电力线项目安装的字体。当您使用 SSL 连接到 MySQL 并使用“箭头”分隔符时，这将添加一个带有提示的挂锁。稍后显示了安装电力线字体的示例。

• prompt_256pl+aw.json : 与prompt_256pl.json相同，但带有“令人敬畏的符号”这额外需要在电力线字体中包含牛逼的符号。稍后显示了安装 awesome 符号的示例。

• prompt_classic.json : 这是一个非常基本的提示，只根据使用的模式显示mysql-js>、mysql-py>或mysql-sql>。

• prompt_dbl_256.json : 两行版本的prompt_256.json提示。

• prompt_dbl_256pl.json : 两行版本的prompt_256pl.json提示。

• prompt_dbl_256pl+aw.json : 两行版本的prompt_256pl+aw.json提示。

• prompt_nocolor.json : 给出了完整的提示信息，但是完全没有颜色。提示的一个例子是MySQL [localhost+ ssl/world] JS>。

如果您的 shell 窗口宽度有限，这种两行模板特别有用，因为它们将信息放在一行上，并允许您在下一行键入命令，而无需在命令前输入完整的提示符。

有两种方法可以指定您想要使用的提示。MySQL Shell 首先在用户的 MySQL Shell 目录中寻找文件prompt.json。默认位置取决于您的操作系统:

• Linux 和 MAC OS:~/.mysqlsh/prompt.json——即在用户主目录的.mysqlsh目录下。

• 微软 Windows:%AppData%\MySQL\mysqlsh\prompt.json——即在用户主目录的AppData\Roaming\MySQL\mysqlsh目录下。

您可以通过设置MYSQLSH_HOME环境变量来更改目录。如果您喜欢不同于默认的提示，您可以将该定义复制到目录中，并将文件命名为prompt.json。

指定提示定义位置的另一种方法是设置MYSQLSH_PROMPT_THEME环境变量，例如，在 Microsoft Windows 上使用命令提示符:

C:\> set MYSQLSH_PROMPT_THEME=C:\Program Files\MySQL\MySQL Shell 8.0\share\mysqlsh\prompt\prompt_256inv.json

在 PowerShell 中，语法略有不同:

PS> $env:MYSQLSH_PROMPT_THEME = "C:\Program Files\MySQL\MySQL Shell 8.0\share\mysqlsh\prompt\prompt_256inv.json";

在 Linux 和 Unix 上:

shell$ export MYSQLSH_PROMPT_THEME=/usr/share/mysqlsh/prompt/prompt_256inv.json

如果您临时想要使用不同于您通常使用的提示，这将非常有用。

正如已经暗示的，大多数提示定义有几个部分。最简单的方法是看一个提示的例子，如图 12-1 所示的默认(prompt_256.json)提示。

图 12-1

默认的 MySQL Shell 提示符

提示符有几个部分。首先，它在红色背景上显示PRODUCTION，这是为了警告您已经连接到生产实例。一个实例是否被视为生产实例取决于您所连接的主机名是否包含在PRODUCTION_SERVERS环境变量中。第二个元素是没有任何特殊含义的MySQL字符串。

第三是你连接的主机和端口，是否使用 X 协议，是否使用 SSL。在这种情况下，端口号后面有一个+,表示 X 协议正在使用中。第四个元素是默认模式。

第五个也是最后一个元素(不算最后的>)是语言模式。根据您是否启用了 SQL、Python 或 JavaScript 模式，它将分别显示SQL、Py或JS。该元素的背景颜色也会随着语言的变化而变化。SQL 用橙色，Python 用蓝色，JavaScript 用黄色。

一般来说，您不会看到提示的所有元素，因为 MySQL Shell 只包含那些相关的元素。例如，只有当您设置了默认模式时，才会包括默认模式，并且只有当您连接到实例时，连接信息才会出现。

在使用 MySQL Shell 时，您可能会意识到需要对提示定义进行一些修改。让我们来看看如何做到这一点。

自定义提示定义

提示定义是 JSON 文件，您可以根据自己的喜好编辑定义。最好的方法是复制最接近您想要的模板，然后进行更改。

Tip

创建自己的提示定义的最佳帮助来源是与模板文件位于同一目录下的README.prompt文件。

与其详细阅读规范，不如看一下prompt_256.json模板并讨论它的一些部分。清单 12-1 显示了文件的末尾是定义提示元素的地方。

  "segments": [
    {
      "classes": ["disconnected%host%", "%is_production%"]
    },
    {
      "text": " My",
      "bg": 254,
      "fg": 23
    },
    {
      "separator": "",
      "text": "SQL ",
      "bg": 254,
      "fg": 166
    },
    {
      "classes": ["disconnected%host%", "%ssl%host%session%"],
      "shrink": "truncate_on_dot",
      "bg": 237,
      "fg": 15,
      "weight": 10,
      "padding" : 1
    },
    {
      "classes": ["noschema%schema%", "schema"],
      "bg": 242,
      "fg": 15,
      "shrink": "ellipsize",
      "weight": -1,
      "padding" : 1
    },
    {
      "classes": ["%Mode%"],
      "text": "%Mode%",
      "padding" : 1
    }
  ]

Listing 12-1The definition of the elements of the prompt

这里有几件事值得注意。首先，请注意有一个类为disconnected%host%和%is_production%的对象。百分号中的名称是在同一个文件中定义的变量，或者来自 MySQL Shell 本身(它有主机和端口之类的变量)。例如，is_production被定义为

  "variables" : {
    "is_production": {
        "match" : {
            "pattern": "*;%host%;*",
            "value": ";%env:PRODUCTION_SERVERS%;"
        },
        "if_true" : "production",
        "if_false" : ""
    },

因此，如果主机包含在环境变量PRODUCTION_SERVERS中，它就被视为生产实例。

关于元素列表要注意的第二件事是，有一些特殊的字段，比如shrink，可以用来定义文本如何保持相对较短。例如，host 元素使用truncate_on_dot，因此如果完整主机名太长，则只显示主机名中第一个点之前的部分。或者，可以使用ellipsize在截断值后添加…。

第三，分别使用bg和fg元素定义背景色和前景色。这允许您根据自己的喜好完全自定义颜色提示。可以通过以下方式之一指定颜色:

• **按名称:**有几种颜色是按名称来命名的:黑色、红色、绿色、黄色、蓝色、品红色、青色和白色。

• **按索引:**0 到 255 之间的值(包括 0 和 255)，其中 0 表示黑色，63 表示浅蓝色，127 表示洋红色，193 表示黄色，255 表示白色。

• By RGB: 使用#rrggbb 格式的值。这要求终端支持真彩色。

值得举例说明的一组内置变量在某种程度上依赖于您所连接的环境或 MySQL 实例。这些是

• %env:varname% : 这使用了一个环境变量。确定您是否连接到生产服务器的方式就是使用环境变量的一个例子。

• %sysvar:varname% : 这使用了来自 MySQL 的一个全局系统变量的值，也就是SELECT @@global.varname返回的值。

• %sessvar:varname% : 与前面的类似，但使用了一个会话系统变量。

• %status:varname% : 这个使用了 MySQL 中一个全局状态变量的值，也就是SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'varname'返回的值。

• %status:varname% : 类似于前面的，但是使用了一个会话状态变量。

例如，如果您想在提示中包含您所连接的实例的 MySQL 版本，您可以添加一个元素，如

    {
      "separator": "",
      "text": "%sysvar:version%",
      "bg": 250,
      "fg": 166
    },

我们鼓励你尝试定义，直到你找到最适合你的配色方案和元素。改进 Linux 上提示符的另一种方法是安装 Powerline 和 Awesome 字体。

电力线和可怕的字体

如果你觉得普通的 MySQL Shell 提示符太方了，并且你在 Linux 上使用 MySQL Shell，你可以考虑使用依赖于 Powerline 和牛逼字体的模板之一。默认情况下不会安装字体。

这个例子将向你展示如何最小化安装电力线字体 ¹ 并使用 GitHub 上 gabrielelana 的 awesome-terminal-fonts 项目的补丁策略分支安装 Awesome 字体。²²

Tip

另一个选项是 Fantasque Awesome 电力线字体( https://github.com/ztomer/fantasque_awesome_powerline )，包括电力线和 Awesome 字体。这些字体看起来与本例中安装的略有不同。选择你喜欢的。

您可以通过克隆 GitHub 存储库来安装 Awesome 字体，然后切换到 patching-strategy 分支。然后就是把需要的文件复制到 home 目录下的.local/share/fonts/，重新构建字体信息缓存文件。步骤如清单 12-2 所示。该输出也可以在本书的 GitHub 资源库的listing_12_2.txt中找到，以便于复制命令。

shell$ git clone https://github.com/gabrielelana/awesome-terminal-fonts.git
Cloning into 'awesome-terminal-fonts'...
remote: Enumerating objects: 329, done.
remote: Total 329 (delta 0), reused 0 (delta 0), pack-reused 329
Receiving objects: 100% (329/329), 2.77 MiB | 941.00 KiB/s, done.
Resolving deltas: 100% (186/186), done.

shell$ cd awesome-terminal-fonts

shell$ git checkout patching-strategy
Branch patching-strategy set up to track remote branch patching-strategy from origin.
Switched to a new branch 'patching-strategy'

shell$ mkdir -p ~/.local/share/fonts/

shell$ cp patched/SourceCodePro+Powerline+Awesome+Regular.* ~/.local/share/fonts/

shell$ fc-cache -fv ~/.local/share/fonts/
/home/myuser/.local/share/fonts: caching, new cache contents: 1 fonts, 0 dirs
/usr/lib/fontconfig/cache: not cleaning unwritable cache directory
/home/myuser/.cache/fontconfig: cleaning cache directory
/home/myuser/.fontconfig: not cleaning non-existent cache directory
/usr/bin/fc-cache-64: succeeded

Listing 12-2Installing the Awesome fonts

这要求您已经安装了git。下一步是安装电力线字体，如清单 12-3 所示。该输出也可以在本书的 GitHub 资源库的listing_12_3.txt中找到，以便于复制命令。

shell$ wget --directory-prefix="${HOME}/.local/share/fonts" https://github.com/powerline/powerline/raw/develop/font/PowerlineSymbols.otf
...
2019-08-25 14:38:41 (5.48 MB/s) - '/home/myuser/.local/share/fonts/PowerlineSymbols.otf' saved [2264/2264]

shell$ fc-cache -vf ~/.local/share/fonts/
/home/myuser/.local/share/fonts: caching, new cache contents: 2 fonts, 0 dirs
/usr/lib/fontconfig/cache: not cleaning unwritable cache directory
/home/myuser/.cache/fontconfig: cleaning cache directory
/home/myuser/.fontconfig: not cleaning non-existent cache directory
/usr/bin/fc-cache-64: succeeded

shell$ wget --directory-prefix="${HOME}/.config/fontconfig/conf.d" https://github.com/powerline/powerline/raw/develop/font/10-powerline-symbols.conf
...
2019-08-25 14:39:11 (3.61 MB/s) - '/home/myuser/.config/fontconfig/conf.d/10-powerline-symbols.conf' saved [2713/2713]

Listing 12-3Installing the Powerline font

这并不需要完全安装电力线字体，但是如果您只是想在 MySQL Shell 中使用电力线字体，这就足够了。两个wget命令下载字体和配置文件，fc-cache命令重建字体信息缓存文件。您需要重启 Linux 以使更改生效。

重启完成后，您可以复制一个pl+aw模板作为您的新提示，例如:

shell$ cp /usr/share/mysqlsh/prompt/prompt_dbl_256pl+aw.json ~/.mysqlsh/prompt.json

结果提示如图 12-2 所示。

图 12-2

双线电力线+牛逼字体提示

此示例还显示了当您更改语言模式并设置默认模式时，提示是如何变化的。关于对多模块的支持，这就是为什么 MySQL Shell 是如此强大的工具，所以下一节将讨论如何在 MySQL Shell 中使用外部模块。

使用外部模块

对 JavaScript 和 Python 的支持使得在 MySQL Shell 中执行任务变得很容易。您不仅可以使用核心功能，还可以导入标准模块和您自己的定制模块。这一节将从使用外部模块的基础开始(与内置的 MySQL Shell 模块相对)。下一节将介绍报告基础设施，之后将介绍插件。

Note

本书的讨论重点是 Python。如果你更喜欢 JavaScript，用法非常相似。一个主要的区别是 Python 使用 snake case(例如import_table())，而 JavaScript 使用 camel case ( importTable())。参见 https://dev.mysql.com/doc/mysql-shell/en/mysql-shell-code-execution.html 了解 MySQL Shell 中代码执行的一般信息。

在 MySQL Shell 中使用 Python 模块的方式与使用交互式 Python 解释器的方式相同，例如:

mysql-py> import sys
mysql-py> print(sys.version)
3.7.4 (default, Sep 13 2019, 06:53:53) [MSC v.1900 64 bit (AMD64)]

mysql-py> import uuid
mysql-py> print(uuid.uuid1())
fd37319e-c70d-11e9-a265-b0359feab2bb

确切的输出取决于 MySQL Shell 的版本和您使用它的平台。

Note

MySQL Shell 8.0.17 和更早版本提供了 Python 2.7，而 MySQL Shell 8.0.18 和更高版本提供了 Python 3.7。

MySQL Shell 解释器允许您导入 Python 中包含的所有常用模块。如果您想要导入您自己的模块，您将需要调整搜索路径。您可以在交互式会话中直接执行此操作，例如:

mysql-py> sys.path.append('C:\MySQL\Shell\Python')

以这种方式修改路径对于模块的一次性使用来说很好；然而，如果你已经创建了一个你将经常使用的模块，这是不方便的。

当 MySQL Shell 启动时，它读取两个配置文件，一个用于 Python，一个用于 JavaScript。对于 Python，文件是mysqlshrc.py，对于 JavaScript 是mysqlshrc.js。MySQL Shell 在四个地方搜索文件。在 Microsoft Windows 上，路径按搜索顺序排列:

1. %PROGRAMDATA%\MySQL\mysqlsh\

2. %MYSQLSH_HOME%\shared\mysqlsh\

3. <mysqlsh binary path>\

4. %APPDATA%\MySQL\mysqlsh\

在 Linux 和 Unix 上:

1. /etc/mysql/mysqlsh/

2. $MYSQLSH_HOME/shared/mysqlsh/

3. <mysqlsh binary path>/

4. $HOME/.mysqlsh/

始终搜索所有四个路径，如果在多个位置找到文件，将执行每个文件。这意味着，如果文件影响相同的变量，则最后找到的文件优先。如果你做出对你个人有意义的改变，最好的地方是在第四个位置。步骤 4 中的路径可以用环境变量MYSQLSH_USER_CONFIG_HOME覆盖。

如果您添加想要定期使用的模块，您可以在mysqlshrc.py文件中修改搜索路径。这样，您可以像导入任何其他 Python 模块一样导入该模块。

Tip

支持外部模块的一个很好的例子是 Innotop 的 MySQL Shell 端口( https://github.com/lefred/mysql-shell-innotop )。它也揭示了两个局限性。因为 Innotop 的报告部分是使用curses库实现的，所以它不能在 Microsoft Windows 上工作，并且因为实现使用 Python，所以它要求您在 Python 语言模式下执行 Innotop。本章后面讨论的报告基础设施和插件避免了这些限制。

举个简单的例子，考虑一个非常简单的模块，它有一个滚动虚拟骰子并返回 1 到 6 之间的值的函数:

import random

def dice():
    return random.randint(1, 6)

本书的 GitHub 资源库中的文件example.py也提供了这个例子。如果将文件保存到目录C:\MySQL\Shell\Python，将以下代码添加到mysqlshrc.py文件中(根据保存文件的位置调整sys.path.append()行中的路径):

import sys
sys.path.append('C:\MySQL\Shell\Python')

下一次启动 MySQL Shell 时，您可以使用该模块，例如(由于dice()函数返回一个随机值，您的输出会有所不同):

mysql-py> import example
mysql-py> example.dice()
5
mysql-py> example.dice()
3

这是扩展 MySQL Shell 最简单的方法。另一种方法是将报告添加到报告基础结构中。

报告基础设施

从 MySQL Shell 8.0.16 开始，有了一个报告基础设施，您可以使用内置报告和自己的定制报告。当您遇到性能问题时，这是使用 MySQL Shell 监控 MySQL 实例和收集信息的一种非常强大的方法。

Tip

由于报告基础设施仍然非常新，建议检查每个新版本的新内置报告。

本节将首先展示如何获得关于可用报告的帮助，然后讨论如何执行报告，最后讨论如何添加您自己的报告。

报告信息和帮助

MySQL Shell 的内置帮助也扩展到了报告，因此您可以很容易地获得如何使用报告的帮助。您可以使用不带任何参数的\show命令来获取可用报告的列表。如果您将报告名称作为参数与--help选项一起添加，您将获得该报告的详细帮助。清单 12-4 展示了这两种用法的一个例子。

mysql-py> \show
Available reports: query, thread, threads.

mysql-py> \show query --help
NAME
      query - Executes the SQL statement given as arguments.

SYNTAX
      \show query [OPTIONS] [ARGS]
      \watch query [OPTIONS] [ARGS]

DESCRIPTION
      Options:

      --help, -h  Display this help and exit.

      --vertical, -E
                  Display records vertically.

      Arguments:

      This report accepts 1-* arguments.

Listing 12-4Obtaining a list of reports and help for the query report

\show命令的输出显示有三个报告可用。这些是从版本 8.0.18 开始的内置报告。第二个命令返回对query报告的帮助，显示它接受一个或多个参数，并有两个选项:--help用于返回帮助文本，--vertical或-E用于以垂直格式返回查询结果。

内置报告包括

• **查询:**执行作为参数提供的查询。

• **线程:**返回当前连接的信息。

• **线程:**返回当前用户、前台线程或后台线程的所有连接信息。

您应该在帮助输出中注意到的另一件事是，它列出了执行报告的两种方法。您可以使用同样用于生成帮助的\show命令，也可以使用\watch命令。您可以使用常用的内置帮助来获得关于每个命令的更多帮助:

mysql-py> \h \show

mysql-py> \h \watch

帮助输出相当详细，所以这里省略了。相反，下一小节将讨论如何使用这两个命令。

执行报告

有两种不同的方法来执行报告。您可以要求只执行一次报告，也可以要求以固定的时间间隔反复执行报告。

有两个命令可用于执行报告:

• **\显示:**执行一次报告。

• \watch: 像 Linux 上的watch命令一样，按照规定的时间间隔执行报告。

这两个命令都可以在两种语言模式下使用。\show命令本身没有任何参数(但报告可能会添加特定于它的参数)。\watch命令有两个选项，指定何时以及如何输出报告。这些选项是

• --interval=float 、 -i float : 每次执行报告之间等待的秒数。该值必须在 0.1–86400(一天)秒的范围内。默认值为 2 秒。

• --nocls : 输出报告结果时不清空屏幕。这会将新结果附加到以前结果的下方，并允许您查看报告结果的历史记录，直到最早的结果滚动到视图之外。

当您用\watch命令执行一个报告时，您用 Ctrl+C 停止执行。

作为执行报告的一个示例，考虑您给出将被执行的查询的查询报告。如果希望结果以垂直格式返回，可以使用--vertical参数。清单 12-5 显示了第一次执行报告的结果示例，使用\show命令从sys.session视图获取活动查询，然后使用\watch命令每 5 秒刷新一次，并且不清空屏幕。为了确保返回一些数据，例如，您可以在第二个连接中执行查询SELECT SLEEP(60)。

mysql-sql> \show query --vertical SELECT conn_id, current_statement AS stmt, statement_latency AS latency FROM sys.session WHERE command = 'Query' AND conn_id <> CONNECTION_ID()
*************************** 1\. row ***************************
conn_id: 34979
   stmt: SELECT SLEEP(60)
latency: 32.62 s

mysql-sql> \watch query --interval=5 --nocls --vertical SELECT conn_id, current_statement AS stmt, statement_latency AS latency FROM sys.session WHERE command = 'Query' AND conn_id <> CONNECTION_ID()
*************************** 1\. row ***************************
conn_id: 34979
   stmt: SELECT SLEEP(60)
latency: 43.02 s
*************************** 1\. row ***************************
conn_id: 34979
   stmt: SELECT SLEEP(60)
latency: 48.09 s
*************************** 1\. row ***************************
conn_id: 34979
   stmt: SELECT SLEEP(60)
latency: 53.15 s
*************************** 1\. row ***************************
conn_id: 34979
   stmt: SELECT SLEEP(60)
latency: 58.22 s
Report returned no data.

Listing 12-5Using the query report

如果您执行相同的命令，您的输出将取决于报告运行时其他线程中正在执行的语句。用于报告的查询添加了一个条件，即连接 id 必须不同于生成报告的连接的 id。带有查询报告的\show命令本身没有什么价值，因为您也可以执行查询。对于其他报告和在使用\watch命令之前检查查询更有用。

\watch命令更有趣，因为它允许您不断更新结果。在本例中，报告运行了五次才停止。前四次，有另一个连接执行查询，第五次报告不生成数据。请注意，在连续执行之间，查询的语句延迟增加了五秒以上。这是因为这 5 秒钟是 MySQL Shell 从显示一次迭代的结果到再次开始执行查询所等待的时间。因此，两次输出之间的总时间是间隔加上查询执行时间加上处理结果所需的时间。

报告基础结构不仅允许您使用内置报告。您也可以添加自己的报告。

添加您自己的报告

报告基础设施的真正强大之处在于它易于扩展，因此 MySQL 开发团队和您都可以添加更多的报告。虽然您可以使用对外部模块的支持来添加报告，就像使用 Innotop 一样，但是这种方法要求您自己实现报告基础结构，并且您必须使用模块的语言模式来执行报告。当您使用报告基础结构时，这一切都会为您处理，并且报告可用于所有语言模式。

Note

本节中的报告代码并不打算在 MySQL Shell 会话中执行(如果您照原样复制并粘贴它，将会导致错误，因为 MySQL Shell 在交互模式下使用块中的空行来退出该块)。相反，代码必须保存到调用 MySQL Shell 时加载的文件中。如何安装代码的说明在示例的最后。

讨论如何创建自己的报告的一个好方法是创建一个简单的报告，并讨论组成它的各个部分。清单 12-6 显示了创建查询sys.session视图的报告所需的代码。代码也可以从本书的 GitHub 库中的文件listing_12_6.py中获得。将代码保存在哪里，以便它可以作为 MySQL Shell 中的报告使用，这将在后面讨论。

'''Defines the report "sessions" that queries the sys.x$session view
for active queries. There is support for specifying what to order by
and in which direction, and the maximum number of rows to include in
the result.'''

SORT_ALLOWED = {
    'thread': 'thd_id',
    'connection': 'conn_id',
    'user': 'user',
    'db': 'db',
    'latency': 'statement_latency',
    'memory': 'current_memory',
}

def sessions(session, args, options):
    '''Defines the report itself. The session argument is the MySQL
    Shell session object, args are unnamed arguments, and options
    are the named options.'''
    sys = session.get_schema('sys')
    session_view = sys.get_table('x$session')
    query = session_view.select(
        'thd_id', 'conn_id', 'user', 'db',
        'sys.format_statement(current_statement) AS statement',
        'sys.format_time(statement_latency) AS latency',
        'format_bytes(current_memory) AS memory')

    # Set what to sort the rows by (--sort)
    try:
        order_by = options['sort']
    except KeyError:
        order_by = 'latency'

    if order_by in ('latency', 'memory'):
        direction = 'DESC'
    else:
        direction = 'ASC'
    query.order_by('{0} {1}'.format(SORT_ALLOWED[order_by], direction))

    # If ordering by latency, ignore those statements with a NULL latency
    # (they are not active)
    if order_by == 'latency':
        query.where('statement_latency IS NOT NULL')

    # Set the maximum number of rows to retrieve is --limit is set.
    try:
        limit = options['limit']
    except KeyError:
        limit = 0
    if limit > 0:
        query.limit(limit)

    result = query.execute()
    report = [result.get_column_names()]
    for row in result.fetch_all():
        report.append(list(row))

    return {'report': report}

Listing 12-6Report querying the sys.session view

代码首先定义一个字典，其中包含对结果进行排序的支持值。这将在后面的代码中使用，既用于sessions()函数内部，也用于注册报告。sessions()函数是创建报告的地方。该函数有三个参数:

• session : 这是一个 MySQL Shell Session对象(定义了与 MySQL 实例的连接)。

• args : 传递给报表的未命名参数列表。这是用于查询报告的，您只需指定查询，而无需在查询前添加参数名称。

• options : 为报表命名参数的字典。

会话报告使用命名选项，因此不使用args参数。

接下来的八行使用 X DevAPI 来定义基本查询。首先，从会话中获取sys模式的模式对象。然后从模式对象中获取sessions视图(使用get_table()来获取视图和表)。最后，创建一个 select 查询，其中的参数指定应该检索哪些列以及为这些列使用哪些别名。

接下来，处理--sort参数，它在options字典中作为sort键可用。如果键不存在，报告将退回到按延迟排序。如果根据等待时间或内存使用情况对输出进行排序，则排序顺序定义为降序；否则，排序顺序为升序。order_by()方法用于将排序信息添加到查询中。此外，当按延迟排序时，只包括延迟不为NULL的会话。

以类似的方式处理--limit参数，取值 0 表示所有匹配的会话。最后，执行查询。报告以列表形式生成，第一项是列标题，其余是结果中的行。报告返回一个字典，在report项中有报告列表。

此报告返回列表格式的结果。还有另外两种格式。总体而言，支持以下结果格式:

• **列表类型:**结果以列表的形式返回，第一项是标题，其余的是按显示顺序排列的行。标题和行本身就是列表。

• **报表类型:**结果是单个项目的列表。MySQL Shell 使用 YAML 来显示结果。

• **打印类型:**结果直接打印到屏幕上。

剩下的就是登记报告了。这是使用清单 12-7 中所示的shell对象的register_report()方法完成的(这也包含在文件listing_12-6.py中)。

# Make the report available in MySQL Shell.
shell.register_report(
    'sessions',
    'list',
    sessions,
    {
        'brief': 'Shows which sessions exist.',
        'details': ['You need the SELECT privilege on sys.session view and ' +
                    'the underlying tables and functions used by it.'],
        'options': [
            {
                'name': 'limit',
                'brief': 'The maximum number of rows to return.',
                'shortcut': 'l',
                'type': 'integer'
            },
            {
                'name': 'sort',
                'brief': 'The field to sort by.',
                'shortcut': 's',
                'type': 'string',
                'values': list(SORT_ALLOWED.keys())
            }
        ],
        'argc': '0'
    }
)

Listing 12-7Registering the sessions report

register_report()方法接受定义报告的四个参数，并提供由 MySQL Shell 的内置帮助特性返回的帮助信息。这些论点是

• name : 报表的名称。您可以相对自由地选择名称，只要它是一个单词，并且对所有报表都是唯一的。

• type : 结果格式:'list'、'report'或'print'。

• report : 生成报告的函数的对象，这里是sessions。

• description : 描述报表的可选参数。如果您提供了描述，您将使用下面描述的字典。

描述是最复杂的论点。它由带有以下关键字的字典组成(所有项目都是可选的):

• brief : 报告的简短描述。

• details : 作为字符串列表提供的报告的详细描述。

• options : 将命名的自变量作为字典列表。

• argc : 未命名的参数数量。您可以指定一个精确的数字(如本例所示)，一个星号(*)代表任意数量的参数，一个精确数字范围(如'1-3')，或者一个最小数量的参数范围('3-*')。

options元素用于定义报告的命名参数。列表中的每个 dictionary 对象都必须包含参数的名称，并且支持几个可选参数来提供关于参数的更多信息。表 12-3 列出了字典键及其默认值和描述。需要使用name键；其余的是可选的。

表 12-3

用于定义报表参数的字典键

|

钥匙

|

缺省值

|

描述

| | --- | --- | --- | | name |   | 调用报告时使用双破折号(如--sort)的参数名称。 | | brief |   | 参数的简短描述。 | | details |   | 以字符串列表形式提供的参数的详细说明。 | | shortcut |   | 可用于访问参数的单个字母数字字符。 | | type | string | 参数类型。编写时支持的值有 string、bool、integer 和 float。当选择一个布尔值时，该参数作为默认设置为False的开关。 | | required | False | 参数是否是强制的。 | | values |   | 字符串参数的允许值。如果未提供值，则支持所有值。这就是示例中用来限制允许的排序选项的内容。 |

导入报告的典型方式是将报告定义和注册码保存在用户配置路径下的目录下的init.d中，在 Microsoft Windows 上默认为%AppData%\MySQL\mysqlsh\，在 Linux 和 Unix 上默认为$HOME/.mysqlsh/(与搜索配置文件的第四个路径相同)。当启动 MySQL Shell 时，所有文件扩展名为.py的脚本都将作为 Python 脚本执行(对于 JavaScript，则为.js)。

Tip

如果脚本中有错误，有关问题的信息将被记录到 MySQL Shell 日志中，该日志存储在用户配置路径中的文件mysqlsh.log中。

如果您将listing_12_6.py文件复制到这个目录并重启 MySQL Shell(确保您使用 MySQL X 端口连接——默认端口 33060 ),您可以使用清单 12-8 中所示的会话报告。报告的结果会有所不同，因此如果您执行报告，您将不会看到相同的结果。

mysql-py> \show
Available reports: query, sessions, thread, threads.

mysql-py> \show sessions --help
NAME
      sessions - Shows which sessions exist.

SYNTAX
      \show sessions [OPTIONS]
      \watch sessions [OPTIONS]

DESCRIPTION
      You need the SELECT privilege on sys.session view and the underlying
      tables and functions used by it.

      Options:

      --help, -h  Display this help and exit.

      --vertical, -E
                  Display records vertically.

      --limit=integer, -l
                  The maximum number of rows to return.

      --sort=string, -s
                  The field to sort by. Allowed values: thread, connection,
                  user, db, latency, memory.

mysql-py> \show sessions --vertical
*************************** 1\. row ***************************
   thd_id: 81
  conn_id: 36
     user: mysqlx/worker
       db: NULL
statement: SELECT `thd_id`,`conn_id`,`use ... ER BY `statement_latency` DESC
  latency: 40.81 ms
   memory: 1.02 MiB

mysql-py> \js
Switching to JavaScript mode...

mysql-js> \show sessions --vertical
*************************** 1\. row ***************************
   thd_id: 81
  conn_id: 36
     user: mysqlx/worker
       db: NULL
statement: SELECT `thd_id`,`conn_id`,`use ... ER BY `statement_latency` DESC
  latency: 71.40 ms
   memory: 1.02 MiB

mysql-js> \sql
Switching to SQL mode... Commands end with ;

mysql-sql> \show sessions --vertical
*************************** 1\. row ***************************
   thd_id: 81
  conn_id: 36
     user: mysqlx/worker
       db: NULL
statement: SELECT `thd_id`,`conn_id`,`use ... ER BY `statement_latency` DESC
  latency: 44.80 ms
   memory: 1.02 MiB

Listing 12-8Using the sessions report

新的sessions报告以与内置报告相同的方式显示，并且您拥有与内置报告相同的特性，例如，支持在垂直输出中显示结果。支持垂直输出的原因是因为报告以列表形式返回结果，所以 MySQL Shell 处理格式。还要注意报告是如何在所有三种语言模式下使用的，即使它是用 Python 编写的。

还有一种导入报告的替代方法。您可以将报告作为插件的一部分，而不是将文件保存到init.d目录中。

插件

MySQL Shell 在 8.0.17 版本中增加了对插件的支持。一个插件由一个或多个代码模块组成，这些代码模块可以包括报告、实用程序或任何其他可能对您有用的东西，并且可以作为 Python 或 JavaScript 代码来执行。这是扩展 MySQL Shell 最强大的方法。代价是它也相对复杂，但好处是更容易共享和导入一个包的特性。插件的另一个好处是，不仅可以从任何语言模式执行报告；您的代码的其余部分也可以在 Python 和 JavaScript 中使用。

Tip

关于添加插件的所有细节，包括用于创建插件对象和注册它们的方法的参数描述，参见 https://dev.mysql.com/doc/mysql-shell/en/mysql-shell-plugins.html 。在 Mike Zinner(MySQL 开发经理，他的团队包括 MySQL Shell 开发者)的 GitHub 存储库中也有一个值得研究的示例插件: https://github.com/mzinner/mysql-shell-ex 。

您可以通过在用户配置路径下的plugins目录中添加一个带有插件名称的目录来创建一个插件，在 Microsoft Windows 上默认为%AppData%\MySQL\mysqlsh\，在 Linux 和 Unix 上默认为$HOME/.mysqlsh/(与搜索配置文件的第四个路径相同)。插件可以包含任意数量的文件和目录，但是所有的文件必须使用相同的编程语言。

Note

插件中的所有代码必须使用相同的编程语言。如果你需要同时使用 Python 和 JavaScript，你必须把代码分成两个不同的插件。

在本书的 GitHub 库的目录Chapter_12/myext中包含了一个名为myext的示例插件。它包括图 12-3 中描述的目录和文件。浅色(黄色)圆角矩形代表目录，深色(红色)文档形状是目录中的文件列表。

Note

示例插件非常简单，目的是演示插件基础设施是如何工作的。如果您在产品中使用插件，请确保在代码中添加适当的验证和错误处理。

图 12-3

myext插件的目录和文件

你可以像 Python 包和模块一样查看插件的结构。需要注意的两件重要事情是，每个目录中必须有一个__init__.py文件，并且当插件被导入时，只执行init.py文件(JavaScript 模块的init.js)。这意味着您必须在init.py文件中包含注册插件公共部分所必需的代码。在这个示例插件中，所有的__init__.py文件都是空的。

Note

插件不应该被交互地创建。确保将代码保存到文件中，最后重启 MySQL Shell 来导入插件。更多细节将在本节的剩余部分给出。

reports目录中的sessions.py文件与清单 12-6 中生成的sessions报告相同，除了报告的注册在reports/init.py中完成，并且报告被重命名为sessions_myext以避免两个报告同名。

utils目录包括一个具有get_columns()功能的模块，该模块由tools/example.py中的describe()功能使用。get_columns()功能也在utils/init.py中注册为util.get_columns()。清单 12-9 显示了来自utils/util.py的get_columns()函数。

'''Define utility functions for the plugin.'''

def get_columns(table):
    '''Create query against information_schema.COLUMNS to obtain
    meta data for the columns.'''
    session = table.get_session()
    i_s = session.get_schema("information_schema")
    i_s_columns = i_s.get_table("COLUMNS")

    query = i_s_columns.select(
        "COLUMN_NAME AS Field",
        "COLUMN_TYPE AS Type",
        "IS_NULLABLE AS `Null`",
        "COLUMN_KEY AS Key",
        "COLUMN_DEFAULT AS Default",
        "EXTRA AS Extra"
    )
    query = query.where("TABLE_SCHEMA = :schema AND TABLE_NAME = :table")
    query = query.order_by("ORDINAL_POSITION")

    query = query.bind("schema", table.schema.name)
    query = query.bind("table", table.name)

    result = query.execute()
    return result

Listing 12-9The get_columns() function from utils/util.py

该函数接受一个表对象，并使用 X DevAPI 来构造一个针对information_schema.COLUMNS视图的查询。请注意该函数是如何通过表对象获得会话和模式的。最后，返回执行查询的结果对象。

清单 12-10 展示了如何注册get_columns()函数，因此它在myext插件中作为util.get_columns()可用。注册发生在utils/init.py。

'''Import the utilities into the plugin.'''
import mysqlsh
from myext.utils import util

shell = mysqlsh.globals.shell
# Get the global object (the myext plugin)
try:
    # See if myext has already been registered
    global_obj = mysqlsh.globals.myext
except AttributeError:
    # Register myext
    global_obj = shell.create_extension_object()
    description = {
        'brief': 'Various MySQL Shell extensions.',
        'details': [
            'More detailed help. To be added later.'
        ]
    }
    shell.register_global('myext', global_obj, description)

# Get the utils extension

try:
    plugin_obj = global_obj.utils
except IndexError:
    # The utils extension does not exist yet, so register it
    plugin_obj = shell.create_extension_object()
    description = {
        'brief': 'Utilities.',
        'details': ['Various utilities.']
    }
    shell.add_extension_object_member(global_obj, "util", plugin_obj,
                                      description)

definition = {
    'brief': 'Describe a table.',
    'details': ['Show information about the columns of a table.'],
    'parameters': [
        {
            'name': 'table',
            'type': 'object',
            'class': 'Table',
            'required': True,
            'brief': 'The table to get the columns for.',
            'details': ['A table object for the table.']
        }
    ]
}

try:
    shell.add_extension_object_member(plugin_obj, 'get_columns',
                                      util.get_columns, definition)
except SystemError as e:
    shell.log("ERROR", "Failed to register myext util.get_columns ({0})."
              .format(str(e).rstrip()))

Listing 12-10Registering the get_columns() function as util.get_columns()

第一个重要的观察是mysqlsh模块是导入的。shell和session对象都可以通过mysqlsh模块获得，所以在 MySQL Shell 中使用扩展时，这是一个重要的模块。还要注意util模块是如何导入的。总是需要使用从插件名称开始的完整路径来导入插件模块。

为了注册该功能，首先检查mysqlsh.globals中是否已经存在myext插件。如果没有，它是用shell.create_extension_object()创建的，并用shell.register_global()方法注册。这个舞蹈是必要的，因为有多个init.py文件，你不应该依赖它们的执行顺序。

接下来，使用shell.create_extension_object()和shell.add_extension_object_member()方法以类似的方式注册utils模块。如果你有一个大的插件，有可能会重复代码和执行很多类似的步骤，所以你可以考虑创建实用函数来避免重复。

最后，使用shell.add_extension_object_member()方法注册函数本身。因为table参数接受一个对象，所以可以指定所需的对象类型。

对于模块和函数的注册，不要求代码中的名称与注册的名称相同。reports/init.py中的报告注册包括一个更改名称的示例，如果您感兴趣的话。然而，在大多数情况下，保持名称不变是更好的方法，这样可以更容易地找到特性背后的代码。

tools/example.py文件添加了两个都已注册的函数。还有前面的dice()函数和使用get_columns()获取列信息的describe()函数。与describe()功能相关的部分代码如清单 12-11 所示。

import mysqlsh
from myext.utils import util

def describe(schema_name, table_name):
    shell = mysqlsh.globals.shell
    session = shell.get_session()
    schema = session.get_schema(schema_name)
    table = schema.get_table(table_name)
    columns = util.get_columns(table)
    shell.dump_rows(columns)

Listing 12-11The describe() function in tools/example.py

需要注意的最重要的事情是，shell对象被作为mysqlsh.globals.shell获得，从那里可以获得session、schema和table对象。shell.dump_rows()方法用于生成结果的输出。该方法接受一个结果对象和可选的格式(默认为表格格式)。在输出结果的过程中，结果对象被消耗。

你现在已经准备好尝试这个插件了。你需要把整个myext目录复制到plugins目录，重启 MySQL Shell。清单 12-12 显示了帮助内容中的全局对象。

Tip

如果 MySQL Shell 在导入插件时遇到错误，启动 MySQL Shell 时会生成类似WARNING: Found errors loading plugins, for more details look at the log at: C:\Users\myuser\AppData\Roaming\MySQL\mysqlsh\mysqlsh.log的一行。

mysql-py> \h
...
GLOBAL OBJECTS

The following modules and objects are ready for use when the shell starts:

 - dba     Used for InnoDB cluster administration.
 - myext   Various MySQL Shell extensions.
 - mysql   Support for connecting to MySQL servers using the classic MySQL
           protocol.
 - mysqlx  Used to work with X Protocol sessions using the MySQL X DevAPI.
 - session Represents the currently open MySQL session.
 - shell   Gives access to general purpose functions and properties.
 - util    Global object that groups miscellaneous tools like upgrade checker
           and JSON import.

For additional information on these global objects use: <object>.help()

Listing 12-12The global objects in the help content

注意myext插件是如何作为一个全局对象出现的。您可以像使用任何内置的全局对象一样使用myext插件。这包括获得插件子部分的帮助，如清单12-13myext.tools的所示。

mysql-py> myext.tools.help()
NAME
      tools - Tools.

SYNTAX
      myext.tools

DESCRIPTION
      Various tools including describe() and dice().

FUNCTIONS
      describe(schema_name, table_name)
            Describe a table.

      dice()
            Roll a dice

      help([member])
            Provides help about this object and it's members

Listing 12-13Obtaining help for myext.tools

作为最后一个例子，考虑如何使用describe()和get_columns()方法。清单 12-14 在 Python 语言模式下对world.city表使用了这两种方法。

mysql-py> myext.tools.describe('world', 'city')
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
mysql-py> \use world
Default schema `world` accessible through db.

mysql-py> result = myext.util.get_columns(db.city)

mysql-py> shell.dump_rows(result, 'json/pretty')
{
    "Field": "ID",
    "Type": "int(11)",
    "Null": "NO",
    "Key": "PRI",
    "Default": null,
    "Extra": "auto_increment"
}
{
    "Field": "Name",
    "Type": "char(35)",
    "Null": "NO",
    "Key": "",
    "Default": "",
    "Extra": ""
}
{
    "Field": "CountryCode",
    "Type": "char(3)",
    "Null": "NO",
    "Key": "MUL",
    "Default": "",
    "Extra": ""
}
{
    "Field": "District",
    "Type": "char(20)",
    "Null": "NO",
    "Key": "",
    "Default": "",
    "Extra": ""
}
{
    "Field": "Population",
    "Type": "int(11)",
    "Null": "NO",
    "Key": "",
    "Default": "0",
    "Extra": ""
}
5

Listing 12-14Using the describe() and get_columns() methods

in Python

首先，使用describe()方法。模式和表使用它们的名称作为字符串提供，结果作为表打印出来。然后，当前模式被设置为world模式，这允许您将表作为db对象的属性进行访问。然后使用shell.dump_rows()方法将结果打印成漂亮的 JSON。

Tip

因为 MySQL Shell 会检测你是否交互使用了一个方法，如果你没有把get_columns()的结果赋给一个变量，MySQL Shell 会直接输出到控制台。

MySQL Shell 的讨论到此结束。如果您还没有利用它提供的特性，我们鼓励您开始使用它。

摘要

本章介绍了 MySQL Shell。它首先概述了如何安装和使用 MySQL Shell，包括使用连接；SQL、Python 和 JavaScript 语言模式；内置的帮助；和全局对象。本章的其余部分介绍了 MySQL Shell、报告和扩展 MySQL 的定制。

MySQL Shell 提示符不仅仅是一个静态标签。它根据连接和默认模式进行调整，您可以对其进行定制，以包含诸如您所连接的 MySQL 版本等信息，并且您可以更改所使用的主题。

MySQL Shell 的强大之处在于内置的复杂特性及其对创建复杂方法的支持。扩展特性的最简单方法是使用 JavaScript 或 Python 的外部模块。您还可以使用报告基础结构，包括创建自己的自定义报告。最后，MySQL Shell 8.0.17 和更高版本支持插件，您可以使用插件在全局对象中添加您的特性。报告基础设施和插件的优势在于，您添加的特性与语言无关。

除非另有说明，本书剩余部分中使用命令行界面的所有示例都是用 MySQL Shell 创建的。为了最小化所用的空间，提示符已经被替换为mysql>，除非语言模式很重要，在这种情况下，语言模式被包括在内，例如，mysql-py>表示 Python 模式。

关于性能转变工具的讨论到此结束。第四部分包括模式考虑和查询优化器，下一章讨论数据类型。

Footnotes [1](#Fn1_source)

https://powerline.readthedocs.io/en/latest/index.html

  2

https://github.com/gabrielelana/awesome-terminal-fonts/tree/patching-strategy

 

十三、数据类型

在 MySQL(和其他关系数据库)中创建表时，需要为每一列指定数据类型。为什么不把所有东西都存储成字符串呢？毕竟，当数字 42 在本书中被使用时，它被表示为一个字符串，那么为什么不直接对所有内容使用字符串，并允许每一列都有各种值呢？这个想法有一些优点。这是 NoSQL 数据库的部分工作方式(尽管不止如此)，本书的作者见过所有列都被定义为varchar(255)字符串的表格。为什么要为整数、小数、浮点数、日期、字符串等等而烦恼呢？这有几个原因，这就是本章的主题。

首先，将讨论对不同类型的值使用不同数据类型的好处。然后会有 MySQL 支持的数据类型的概述。最后，将讨论数据类型如何影响查询性能以及如何为列选择数据类型。

为什么是数据类型？

列的数据类型定义了可以存储什么类型的值以及如何存储这些值。此外，可能存在与数据类型相关联的元属性，例如大小(例如，用于数字的字节数和字符串中的最大字符数)以及用于字符串的字符集和校对。虽然数据类型属性看起来像是不必要的限制，但它们也有好处。这些好处包括

• 数据有效性

• 文件

• 优化存储

• 表演

• 正确排序

本节的其余部分将讨论这些好处。

数据有效性

在它们的核心，数据类型定义了什么样的值是允许的。定义为整数数据类型的列只能存储整数值。这也是一种保障。如果您犯了一个错误，并试图将一个值存储到一个与定义的数据类型不同的列中，则可能会拒绝该值或转换该值。

Tip

将错误数据类型的值分配给列是否会导致错误或数据类型被转换取决于您是否启用了STRICT_TRANS_TABLES(对于事务存储引擎)和STRICT_ALL_TABLES(对于所有存储引擎)SQL 模式，以及转换数据类型是否被认为是安全的。某些被认为是安全的转换总是被允许的，例如，将“42”转换为 42，反之亦然。建议始终启用严格模式，当试图进行不安全的转换或截断数据时，该模式会使 DML 查询失败。

当您可以确保存储在表中的数据总是具有预期的数据类型时，您的工作会更轻松。如果用整数查询列，那么对返回值进行算术运算是安全的。同样，如果您知道值是一个字符串，您可以安全地执行字符串操作。这需要提前多做一点规划，但是一旦完成，您将会发现自己了解数据的数据类型。

关于数据类型和数据验证还有一点需要考虑。通常，有一些属性与数据类型相关联。在最简单的情况下，你有最大的尺寸。例如，整数的大小可以是 1、2、3、4 或 8 个字节。这会影响可以存储的值的范围。此外，整数可以是有符号的，也可以是无符号的。一个更复杂的例子是字符串，它不仅对存储多少文本有限制，而且需要一个字符集来定义数据如何编码，还需要一个排序规则来定义数据如何排序。

清单 13-1 展示了 MySQL 如何根据数据类型验证数据的例子。

mysql> SELECT @@sql_mode\G
*************************** 1\. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.0003 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1\. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `val1` int(10) unsigned DEFAULT NULL,
  `val2` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0011 sec)

mysql> INSERT INTO t1 (val1) VALUES ('abc');
ERROR: 1366: Incorrect integer value: 'abc' for column 'val1' at row 1

mysql> INSERT INTO t1 (val1) VALUES (-5);
ERROR: 1264: Out of range value for column 'val1' at row 1

mysql> INSERT INTO t1 (val2) VALUES ('abcdef');
ERROR: 1406: Data too long for column 'val2' at row 1

mysql> INSERT INTO t1 (val1, val2) VALUES ('42', 42);
Query OK, 1 row affected (0.0825 sec)

Listing 13-1Data validation based on data type

SQL 模式被设置为默认模式，包括STRICT_TRANS_TABLES。除了主键之外，该表还有两列，其中一列是无符号整数，另一列是varchar(5)，这意味着它最多可以存储五个字符。当试图将字符串或负整数插入到val1列时，该值会被拒绝，因为它不能安全地转换为无符号整数。类似地，试图将一个包含六个字符的字符串存储到val2列中也会失败。然而，将字符串'42'存储到val1并将整数 42 存储到val2被认为是安全的，因此是允许的。

数据验证的一个副作用是，您还描述了您期望的数据——这是列的隐式文档。

文件

当您设计表格时，您知道表格的预期用途。然而，当您或其他人以后使用该表时，这不一定清楚。有几种方法记录列:使用描述值的列名、COMMENT列子句、CHECK约束和数据类型。

虽然不是记录列的最详细的方法——当然也不应该独立存在——但是数据类型确实有助于描述您期望的数据类型。如果您选择了date列而不是datetime，那么很明显您只打算存储日期部分。类似地，使用tinyint而不是int表明您只期望相对较小的值。这些都有助于你自己或他人理解什么样的数据是可以预期的。当您需要优化查询时，对数据的理解越好，您所做的更改就越成功，这样它就可以间接地帮助查询优化。

Tip

在表中提供文档的最佳方式是使用COMMENT子句和CHECK约束。然而，这些在表格图中通常是看不到的，在表格图中，数据类型有助于更好地理解预期的数据类型。

关于性能，显式选择数据类型也有好处。其中之一与值的存储方式有关。

优化存储

MySQL 并不以相同的方式存储所有数据。给定数据类型的存储格式选择得尽可能紧凑，以减少所需的存储空间。例如，考虑值 123456。如果将其存储为字符串，则至少需要 6 个字节加上可能的 1 个字节来存储字符串的长度。如果您选择一个整数，您只需要 3 个字节(对于整数，所有值总是使用相同数量的字节，这取决于该列允许的最大存储)。此外，从存储器中读取整数不需要对值 ¹ 进行任何解释，而对于字符串，则需要使用其字符集对值进行解码。

选择正确的最大列大小可以减少所需的存储量。如果您需要存储整数，并且知道您从不需要需要超过 4 个字节存储的值，那么您可以使用int数据类型，而不是使用 8 个字节存储的bigint。这是该列所需存储量的一半。如果您使用大数据，存储(和内存)节省可能会变得非常大。但是，注意不要过度优化。在许多情况下，更改列的数据类型或大小需要重新构建整个表，如果表很大，这可能是一个开销很大的操作。这样，最好现在就使用多一点的存储空间，以节省以后的工作。

Tip

与其他类型的优化一样，注意不要过度优化数据类型。现在相对较小的存储节省可能会导致以后的痛苦。

数据的存储方式也会影响性能。

表演

并非所有数据类型都是平等的。在计算和比较中使用整数非常便宜，而存储字节的字符串必须使用字符集解码，因此相对昂贵。通过选择正确的数据类型，可以显著提高查询的性能。特别是，如果您需要比较两列中的值(可能在不同的表中)，请确保它们具有相同的数据类型，包括字符集和字符串的排序规则。否则，必须先转换其中一列中的数据，然后才能与另一列进行比较。

虽然理解为什么整数比字符串的性能好很简单，但究竟是什么使一种数据类型的性能比另一种数据类型好或差却相对复杂，这取决于数据类型是如何实现的(存储在磁盘上)。因此，关于性能的进一步讨论将推迟到下一节介绍 MySQL 数据类型之后。

将讨论的最后一个好处是排序。

正确排序

日期类型对值的排序方式有很大影响。虽然人脑通常可以直观地理解数据，但计算机需要一些帮助来理解两个值如何相互比较。数据类型和字符串的排序规则是用于确保数据正确排序的关键属性。

为什么排序很重要？这有几个原因:

• 正确的排序需要知道两个值是否相等或者一个值是否在给定的范围内。这对于让WHERE子句和连接条件按预期工作是至关重要的。

• 当您创建索引时，排序用于确保 MySQL 能够快速找到具有您正在寻找的值的行。 ² 指标将在下一章详细介绍。

考虑值 8 和 10。它们是如何排序的？如果你认为它们是整数，8 在 10 之前。但是，如果您将它们视为字符串，那么“10”(ASCII:0x 3130)位于“8”(ASCII:0x 38)之前。您是否期望一个或另一个取决于您的应用，但是除非也有包含非数字部分的值，否则您可能期望 integer 行为，该行为要求数据类型为 integer 类型。

既然已经讨论了显式数据类型的好处，那么是时候了解 MySQL 支持的数据类型了。

MySQL 数据类型

MySQL 中有 30 多种不同的数据类型。其中几个可以根据大小、精度以及是否接受有符号值进行微调。乍一看，这似乎让人不知所措，但是如果您将数据类型分成不同的类别，您可以逐步为您的数据选择正确的数据类型。

MySQL 中的数据类型可被视为以下类别之一的一部分:

• **数值:**这包括整数、固定精度小数类型、近似精度浮点类型和位类型。

• Temporal: 这包括年份、日期、时间、日期时间和时间戳值。

• **字符串:**这包括二进制对象和带有字符集的字符串。

• **JSON:**JSON 数据类型可以存储 JSON 文档。

• Spatial: 这些类型用于存储描述坐标系中一个或多个点的值。

• Hybrid: MySQL 有两种数据类型，都可以作为整数和字符串使用。

Tip

《MySQL 参考手册》对 https://dev.mysql.com/doc/refman/8.0/en/data-types.html 中的数据类型以及其中的引用有全面的论述。

本节的其余部分将介绍数据类型并讨论它们的细节。

数字数据类型

数值数据类型是 MySQL 支持的最简单的数据类型。您可以在整数、固定精度十进制值和近似浮点值之间进行选择。

表 13-1 总结了数字数据类型，包括其存储要求(以字节为单位)和支持的取值范围。对于整数，您可以选择值是有符号的还是无符号的，这会影响支持的值的范围。对于支持的值，开始值和结束值都包括在允许值的范围内。

表 13-1

数字数据类型(整数、定点和浮点)

|

数据类型

|

存储的字节数

|

范围

| | --- | --- | --- | | tinyint | one | 签名:-128–127 无符号:0–255 | | smallint | Two | 签名:-32768–32767 无符号:0–65535 | | mediumint | three | 签名:-8388608–8388607 无符号:0–16777215 | | int | four | 签名:-2147483648–2147483647 无符号:0–4294967295 | | bigint | eight | 签名:-2⁶³–2⁶³-1 无符号:0–2⁶⁴-1 | | decimal(M, N) | 1–29 | 取决于 M 和 N | | float | four | 可变的 | | double | eight | 可变的 | | bit(M) | 1–8 |   |

整数数据类型是最简单的，具有固定的存储要求和固定的支持值范围。tinyint的同义词是bool(布尔值)。

decimal数据类型(numeric是同义词)有两个参数，M 和 N，它们定义了值的精度和小数位数。如果有decimal(5,2)，数值最多有五位，其中两位是小数(小数点右边)。这意味着允许值在-999.99 和 999.99 之间。最多支持 65 位数字。小数的存储量取决于位数，每个 9 位数的倍数使用 4 个字节，其余位数使用 0-4 个字节。

float和double数据类型存储近似值。这些类型对于数值计算很有效，但代价是它们的值存在不确定性。它们分别使用 4 字节和 8 字节进行存储。

Tip

不要使用浮点数据类型来存储精确的数据，如货币金额。请改用精确小数数据类型。对于近似浮点数据类型，永远不要使用等号(=)和不等号(<>)运算符，因为比较两个近似值通常不会返回相等的结果，即使它们应该相等。

最后一种数值数据类型是bit类型。它可以在一个值中存储 1 到 64 位。例如，这可以用于位屏蔽。所需的存储取决于所需的位数(M 值)；可以近似为FLOOR((M+7)/8)字节。

与数值类型相关的一类数据类型是时态数据类型，这是下一个将要讨论的类别。

时态数据类型

时态数据定义一个时间点。精度范围从一年到一微秒。除了 year 数据类型之外，值是以字符串形式输入的，但是在内部使用了优化的格式，并且值将根据值所代表的时间点正确排序。

表 13-2 显示了 MySQL 支持的时态数据类型，每种类型使用的存储量(以字节为单位),以及支持的值的范围。

表 13-2

时态数据类型

|

数据类型

|

存储的字节数

|

范围

| | --- | --- | --- | | year | one | 1901–2155 | | date | 3–6 | “1000-01-01”到“9999-12-31” | | datetime | 5–8 | “1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999” | | timestamp | 4–7 | “1970-01-01 00:00:01.000000”到“2038-01-19 03:14:07.999999” | | time | 3–6 | -838:59:59.000000 '到' 838:59:59.000000 ' |

datetime、timestamp和time类型都支持高达微秒分辨率的小数秒。小数秒的存储要求为 0-3 字节，具体取决于位数(每两位数一个字节)。

datetime和timestamp列略有不同。当您在datetime列中存储一个值时，MySQL 会按照您指定的方式存储它。另一方面，对于一个timestamp列，使用 MySQL 被配置为使用的时区——变量@@session.time_zone(默认为系统时区)将值转换为 UTC。同样，当您检索数据时，datetime值将按照您最初指定的方式返回，而timestamp列将被转换为在@@session.time_zone变量中设置的时区。

Tip

使用datetime列时，以 UTC 时区存储数据，并在使用数据时转换为所需的时区。通过始终以 UTC 格式存储值，如果操作系统时区或 MySQL 服务器时区被更改，或者您与来自不同时区的用户共享数据，出现问题的可能性就会降低。

当您使用字符串输入和检索日期和时间时，它们以专用格式存储在内部。实际的字符串呢？让我们来看看字符串和二进制数据类型。

字符串和二进制数据类型

字符串和二进制数据类型是存储任意数据的非常灵活的类型。二进制值和字符串的区别在于，字符串有一个与之关联的字符集，所以 MySQL 知道如何解释数据。另一方面，二进制值存储原始数据，这意味着您可以将它们用于任何类型的数据，包括图像和自定义数据格式。

虽然字符串和二进制数据非常灵活，但它们也有代价。对于字符串，MySQL 需要解释字节来确定它们代表哪些字符。就所需的计算能力而言，这是相对昂贵的。有些字符集，包括 MySQL 8 中默认的字符集 UTF-8，是可变宽度的，即一个字符使用可变数量的字节；对于 UTF-8，它的范围是每个字符 1 到 4 个字节。这意味着，如果您请求一个字符串的前四个字符，它可能需要读取 4 到 16 个字节，这取决于是哪些字符，因此 MySQL 将需要分析这些字节来确定何时找到了四个字符。对于二进制字符串，数据含义的解释被放回到应用中。

表 13-3 显示了 MySQL 中代表字符串和二进制数据的数据类型。该表包括可存储的最大数据量以及存储要求的描述。对于数据类型，(M)是该列必须能够存储的最大字符数，而在存储的字节数中，L 是表示用于编码的字符集中的字符串值所需的字节数。

表 13-3

字符串和二进制数据类型

|

数据类型

|

存储的字节数

|

最大长度

| | --- | --- | --- | | char(M) | M*char 宽度 | 255 个字符 | | varchar(M) | L+1 或 L+2 | utf8mb4的 16383 个字符和latin1的 65532 个字符 | | tinytext | L+1 | 255 字节 | | text | L+2 | 65535 字节 | | mediumtext | L+3 | 16777216 字节 | | longtext | L+4 | 4294967296 字节 | | binary(M) | M | 255 字节 | | varbinary(M) | L+1 或 L+2 | 65532 字节 | | tinyblob | L+1 | 255 字节 | | blob | L+2 | 65536 字节 | | mediumblob | L+3 | 16777216 字节 | | longblob | L+4 | 4294967296 字节 |

字符串和二进制对象的存储要求取决于数据的长度。l 是存储该值所需的字节数；对于文本字符串，字符集也必须考虑在内。对于可变宽度类型，1–4 个字节用于存储值的长度。对于char(M)列，当使用紧凑系列的 InnoDB 存储格式并且字符串用可变宽度字符集编码时，所需的存储空间可能小于字符宽度的 M 倍。

对于除 char 和 varchar 之外的所有类型，字符串支持的最大长度以字节为单位指定。这意味着字符串类型中可以存储的字符数取决于字符集。此外，char、varchar、binary和varbinary列计入行宽，行宽总计必须小于 64kb，这实际上意味着很少可能使用理论上的最大长度创建列。(这也是varchar和varbinary列最多可以存储 65532 个字符/字节的原因。)对于longtext和longblob列，应该注意的是，虽然它们原则上可以存储多达 4 GiB 的数据，但实际上存储受到max_allowed_packet变量的限制，该变量最多可以是 1 GiB。

对于存储字符串的数据类型，另一个需要考虑的问题是，您必须为列选择字符集和排序规则。如果没有明确选择，将使用表的默认值。在 MySQL 8 中，默认字符集是使用utf8mb4_0900_ai_ci排序规则的utf8mb4。utf8mb4和utf8mb4_0900_ai_ci是什么意思？

utf8mb4字符集是 UTF 8，支持每个字符多达 4 个字节(例如，对于一些表情符号是必需的)。最初，MySQL 只支持 UTF-8 的每个字符最多 3 个字节，后来添加了utf8mb4来扩展支持。今天，你不应该使用utf8mb3(每个字符最多 3 个字节)或其别名utf8(已弃用，所以后来可以改为表示utf8mb4)。当您使用 UTF-8 时，请始终选择 4 字节变体，因为 3 字节变体几乎没有什么好处，并且已经被弃用。在 MySQL 5.7 和更早的版本中，拉丁语 1 是默认字符集，但随着 MySQL 8 中 UTF-8 的改进，建议使用utf8mb4，除非你有特定的理由选择另一个字符集。

utf8mb4_0900_ai_ci排序规则是用于utf8mb4的通用排序规则。归类定义了排序和比较规则，因此当您比较两个字符串时，它们会正确地进行比较。这些规则可能相当复杂，包括某些字符序列与其他单个字符相等(例如，在某些归类中，德语中的 sharp 与“ss”相同)。排序规则名称由几个部分组成，它们是

• utf8mb4: 归类所属的字符集。

• 0900: 这意味着该排序规则是基于 Unicode 排序算法(UCA) 9.0.0 的排序规则之一。这些是在 MySQL 8 中引入的，与旧的 UTF-8 排序规则相比，提供了显著的性能改进。

• ai: 排序规则可以是不区分重音的(ai)或区分重音的(as)。当排序规则不区分重音时，带重音的字符(如à)被视为等于不带重音的字符 a。在这种情况下，它不区分重音。

• ci: 排序规则可以不区分大小写(ci)或区分大小写(cs)。在这种情况下，它不区分大小写。

名称也可以包含其他部分，其他字符集也有其他排序规则。特别是，有几个特定于国家的字符集需要考虑本地排序和比较规则；对于这些，国家代码被添加到名称中。建议使用 UCA 9.0.0 排序规则之一，因为与其他排序规则相比，这些排序规则具有更好的性能，并且更加现代化。information_schema.COLLATIONS视图包括 MySQL 支持的所有排序规则，支持按字符集过滤。从 8.0.18 开始，有 75 种归类可用于 utf8mb4，其中 49 种是 UCA 9.0.0 归类。

Tip

字符集和排序规则本身就是一个大而有趣的话题。如果你想深入这个话题，可以从本书作者的博客和其中的参考资料开始: https://mysql.wisborg.dk/mysql-8_charset 。

JSON 文档是一种特殊的字符串。MySQL 为它们提供了专用的数据类型。

JSON 数据类型

比关系表更灵活的一种流行的数据存储格式是 JavaScript 对象表示法(JSON)格式。这也是 MySQL 8 中可用的 MySQL 文档存储所选择的格式。MySQL 5.7 引入了对json数据类型的支持。

JSON 文档是 JSON 对象(键和值)、JSON 数组和 JSON 值的组合。下面是一个简单的 JSON 文档示例:

{
    "name": "Sydney",
    "demographics": {
        "population": 5500000
    },
    "geography": {
        "country": "Australia",
        "state": "NSW"
    },
    "suburbs": [
        "The Rocks",
        "Surry Hills",
        "Paramatta"
    ]
}

因为 JSON 文档也是一个字符串(或二进制对象)，所以它也可以存储在字符串或二进制对象列中。但是，通过拥有专用的数据类型，可以添加验证，并且存储为访问文档中的特定元素而优化。

MySQL 8 中 JSON 文档的一个很好的性能相关特性是支持部分更新。这使得更改就地进行，不仅减少了更新期间所做的工作量，还可以只将部分更改写入二进制日志。要使部分就地更新成为可能，需要满足一些要求。这些措施如下:

• 仅支持JSON_SET()、JSON_REPLACE()和JSON_REMOVE()功能。

• 仅支持列内的更新。也就是说，不支持将一列设置为对另一列起作用的三个 JSON 函数之一的返回值。

• 它必须是被替换的现有值。添加新的对象或数组元素会导致整个文档被重写。

• 新值最多必须与被替换的值大小相同。例外情况是先前部分更新释放的空间可以重用。

为了将部分更新作为部分更新记录到二进制日志中，您需要将binlog_row_value_options选项设置为PARTIAL_JSON。该选项可以在会话和全局级别动态设置。

在内部，文档存储为一个长二进制对象(longblob)，文本使用utf8mb4字符集进行解释。最大存储量限制为 1 GiB。存储需求与longblob相似，但是有必要考虑元数据和用于查找的字典的开销。

到目前为止，已经讨论了数字、时态数据、字符串、二进制对象和 JSON 文档。指定空间中一个点的数据呢？这是要涵盖的下一类数据类型。

空间数据类型

空间数据指定坐标系中的一个或多个点，可能形成一个对象，如多边形。例如，这对于在地图上指定项目的位置非常有用。

MySQL 8 增加了指定使用哪个参考系的支持；这被称为空间参考系统标识符(SRID)。支持的参考系统可以在information_schema.ST_SPATIAL_REFERENCE_SYSTEMS视图中找到(SRS_ID列有用于 SRID 的值)；有 5000 多种可供选择。每个空间值都有一个关联的参考系统，以便 MySQL 能够正确识别两个值之间的关系，例如，计算两点之间的距离。要使用地球作为参考系统，请将 SRID 设置为 4326。

支持八种不同的空间数据类型，其中四种是单值类型，四种是值的集合。表 13-4 总结了以字节为单位列出的所需存储的空间类型。

表 13-4

空间数据类型

|

数据类型

|

存储的字节数

|

描述

| | --- | --- | --- | | geometry | 可变的 | 任何类型的单个空间对象。 | | point | Twenty-five | 单点，例如，一个人的位置。 | | linestring | 9+16 * #点 | 形成一条线的一组点，也就是说，它不是一个封闭的对象。 | | polygon | 13+16 * #点 | 包围一个区域的一组点。一个多边形可以包括几个这样的集合，例如，创建环形对象的内环和外环。 | | multipoint | 13+21 * #点 | 点的集合。 | | multilinestring | 可变的 | linestring 值的集合。 | | multipolygon | 可变的 | 多边形的集合。 | | geometrycollection | 可变的 | 几何值的集合。 |

MySQL 使用二进制格式存储数据。geometry、multilinestring、multipolygon和geometrycollection类型的存储需求取决于值中包含的对象的大小。这些对象集合的存储比将对象存储在单独的列中稍大一些。您可以使用LENGTH()函数来获得空间对象的大小，然后添加 4 个字节来存储 SRID，以获得数据所需的总存储空间。

这就剩下一类数据类型需要讨论:数字和字符串数据类型的混合。

混合数据类型

有两种特殊的数据类型结合了整数和字符串的属性:enum和set。两者都可以被认为是可能值的集合，区别在于enum数据类型允许您选择其中一个可能值，而set数据类型允许您选择任何可能值。

使enum和set数据类型混合在一起的是，您可以将它们作为整数和字符串使用。后者是最常见的，也是最容易使用的。在内部，值被存储为整数，这提供了紧凑和有效的存储，同时仍然允许在设置或查询列时使用字符串。两种数据类型都可以使用查找表来实现。

enum数据类型是两者中最常用的。创建列时，可以指定允许值的列表，例如:

CREATE TABLE t1 (
   id int unsigned NOT NULL PRIMARY KEY,
   val enum('Sydney', 'Melbourne', 'Brisbane')
);

数值是列表中从 1 开始的位置。也就是说，悉尼的整数值为 1，墨尔本为 2，布里斯班为 3。根据列表中成员的数量，总存储需求只有 1 或 2 个字节，最多支持 65535 个成员。

除了您可以选择多个选项之外，set数据类型的工作方式与enum类似。要创建它，请列出您希望可用的成员，例如:

CREATE TABLE t1 (
   id int unsigned NOT NULL PRIMARY KEY,
   val set('Sydney', 'Melbourne', 'Brisbane')
);

根据成员在列表中的位置，列表中的每个成员都将获得 1、2、4、8 等系列中的一个数值。在本例中，悉尼的值为 1，墨尔本的值为 2，布里斯班的值为 4。那么值 3 代表什么呢？是悉尼和墨尔本。如果要包含多个值，可以对它们的单个值求和。这样，set 数据类型的工作方式与 bit 类型相同。当您将值指定为字符串时会更简单，因为您将值的成员包括在逗号分隔的列表中。清单 13-2 显示了两个插入set值的例子，每个例子使用数字和字符串值两次插入相同的值。

mysql> INSERT INTO t1
       VALUES (1, 4),
              (2, 'Brisbane');
Query OK, 2 rows affected (0.0812 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1
       VALUES (3, 7),
              (4, 'Sydney,Melbourne,Brisbane');
Query OK, 2 rows affected (0.0919 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT *
         FROM t1\G
*************************** 1\. row ***************************
 id: 1
val: Brisbane
*************************** 2\. row ***************************
 id: 2
val: Brisbane
*************************** 3\. row ***************************
 id: 3
val: Brisbane,Melbourne,Sydney
*************************** 4\. row ***************************
 id: 4
val: Brisbane,Melbourne,Sydney
4 rows in set (0.0006 sec)

Listing 13-2Working with set values

首先，插入'Brisbane'的值。因为它是集合中的第三个元素，所以它的数值为 4。然后插入悉尼、墨尔本和布里斯班的布景。这里你需要把 1，2，4 相加。注意在SELECT查询中，元素的顺序与set定义中的不同。

根据集合中成员的数量，set列使用 1、2、3、4 或 8 个字节的存储空间。一个集合中最多可以有 64 个成员。

对可用数据类型的讨论到此结束。数据类型如何影响查询的性能？可能很多，所以值得考虑一下。

表演

数据类型的选择不仅对于数据完整性和判断预期的数据类型很重要，而且不同的数据类型具有不同的性能特征。本节将讨论在比较数据类型时性能如何变化。

一般来说，数据类型越简单，性能越好。整数的性能最好，浮点(近似值)紧随其后。十进制(精确)值比近似浮点值有更高的开销。二进制对象比文本字符串执行得更好，因为二进制对象没有字符集的开销。

当谈到像 JSON 这样的数据类型时，您可能认为它的性能比使用二进制对象差，因为 JSON 文档有一些存储开销，如本章前面所述。然而，正是这种存储开销意味着 JSON 数据类型的性能要优于将相同的数据存储为 blob。开销包括元数据和用于查找的字典，这意味着访问数据更快。此外，JSON 文档支持就地更新，而 text 和 blob 数据类型会替换整个对象，即使只替换了单个字符或字节。

在给定的数据类型族中(例如，int对bigint)，较小的数据类型比较大的数据类型执行得更好；然而，在实践中，还需要考虑硬件寄存器内的对齐，因此对于内存中的工作负载，这种差异可以忽略不计，甚至相反。

那么应该使用哪些数据类型呢？这是本章的最后一个主题。

应该选择哪种数据类型？

在本章的开始，我们讨论了如何将所有数据存储在字符串或二进制对象中以获得最大的灵活性。在本章的过程中，我们已经讨论了使用特定数据类型的好处，并且在上一节中讨论了不同数据类型的性能。那么应该选择哪种数据类型呢？

您可以开始问自己一些关于需要存储在列中的数据的问题。一些问题的例子如下:

• 数据的本机格式是什么？

• 最初可以预期多大的值？

• 值的大小会随着时间增长吗？如果有，有多少，多快？

• 在查询中检索数据的频率是多少？

• 你期望有多少独特的价值观？

• 需要对值进行索引吗？特别是，它是表的主键吗？

• 您是否需要存储数据，或者是否可以通过另一个表中的外键(使用整数引用列)获取数据？

您应该为需要存储的数据选择本地数据类型。如果你需要存储整数，选择一个整数数据类型，通常是int或bigint，这取决于你需要多大的值。如果要限制值，可以选择较小的整数类型；例如，存储关于父母的数据的表的孩子数量不需要是一个bigint，但是一个tinyint就足够了。同样，如果你想存储 JSON 文档，使用json类型，而不是longtext或longblob。

对于数据类型的大小，您需要同时考虑当前需求和未来需求。如果您预计在 long 内需要更大的值，那么最好立即选择更大的数据类型。这样可以避免以后更改表定义。但是，如果预期的变化是几年后的事情，现在使用较小的数据类型并随着时间的推移重新评估您的需求可能会更好。对于varchar和varbinary，只要不改变存储字符串或字符集长度所需的字节数，也可以就地改变宽度。

当处理字符串和二进制对象时，还可以考虑将数据存储在单独的表中，并使用整数引用这些值。这将在您需要检索值时添加一个联接；但是，如果您只是很少需要实际的字符串值，那么保持主表较小可能是一个整体的优势。这种方法的好处还取决于表中的行数以及如何查询这些行；检索许多行的大型扫描将比单行查找受益更多，即使不需要所有列也使用SELECT *将比只选择需要的列受益更多。

如果只有几个唯一的字符串值，那么使用enum数据类型也是值得考虑的。它的工作方式类似于查找表，但它保存连接并允许您直接检索字符串值。

对于非整数数字数据，您可以选择精确的decimal数据类型和近似的float和double数据类型。如果您需要存储数据，比如必须精确的货币值，您应该总是选择decimal数据类型。如果需要进行相等和不相等的比较，这也是可以选择的类型。如果不需要精确的数据，那么float和double数据类型会表现得更好。

对于字符串值，那么char、varchar、tinytext、text、mediumtext和longtext数据类型需要一个字符集和一个排序规则。通常，建议选择带有基于 UCA 9.0.0 的排序规则之一的utf8mb4(名称中带有_0900_的排序规则)。默认的utf8mb4_0900_ai_ci是一个不错的选择，如果你没有特定的需求。Latin 1 的性能会稍微好一点，但不足以保证为不同的需求增加不同字符集的复杂性。UCA 9.0.0 排序规则还提供了比 Latin 1 更现代的排序规则。

当您需要决定允许多大的值时，选择支持您现在和不久的将来需要的值的最小数据类型或宽度。更小的数据类型也意味着更少的空间用于行大小限制(64 kiB ),更多的数据可以放入 InnoDB 页面。由于 InnoDB 缓冲池可以根据缓冲池和页面的大小存储一定数量的页面，这反过来意味着可以将更多的数据放入缓冲池，从而有助于减少磁盘 I/O。同时，请记住，优化还意味着知道何时已经进行了足够的优化。不要花很长时间来删除几个字节，结果却不得不在一年内进行昂贵的表重建。

最后要考虑的是值是否包含在索引中。值越大，索引也越大。这是主键的一个特殊问题。InnoDB 根据主键(作为聚集索引)组织数据，因此当您添加辅助索引时，主键会添加到索引的末尾，以提供到行的链接。此外，这种数据组织方式意味着通常单调递增的值最适合作为主键。如果主键列随时间随机变化和/或很大，那么最好添加一个带有自动递增整数的伪列，并将其用作主键。

索引本身是一个重要的大主题，将在下一章讨论。

摘要

本章介绍了数据类型的概念。使用数据类型有几个好处:数据验证、文档、优化存储、性能和正确排序。

MySQL 支持大范围的数据类型，从字符串和空间对象上的简单整数到复杂的 JSON 文档。我们讨论了每种数据类型，重点是支持的值、支持的值大小以及所需的存储量。

本章的最后部分讨论了数据类型如何影响性能，以及如何确定为列选择哪种数据类型。这包括考虑列是否会被索引，这也涉及到数据类型的一个好处:正确的排序。索引是一个非常重要的主题，实际上下一章将会涉及到它们。

Footnotes [1](#Fn1_source)

严格来说，这并不正确，但这种解释是在较低的级别上进行的，例如，所使用的字节顺序。

  2

有几种不同的索引类型，它们的实现有很大的不同。并非所有索引类型都使用排序；大多数著名的散列索引计算值的散列。