如何在VPS上优化MySQL和MariaDB中的查询和表
介绍
MySQL 和 MariaDB 是数据库管理系统的流行选择。 两者都使用 SQL 查询语言来输入和查询数据。
尽管 SQL 查询是易于学习的简单命令，但并非所有查询和数据库函数都以相同的效率运行。 随着您存储的信息量的增长，以及如果您的数据库支持网站，随着您的网站受欢迎程度的提高，这变得越来越重要。
在本指南中，我们将讨论一些可以用来加速 MySQL 和 MariaDB 查询的简单措施。 我们将假设您已经使用适合您操作系统的指南之一安装了 MySQL 或 MariaDB。
表设计通用性
提高查询速度的最基本方法之一是表结构设计本身。 这意味着您需要在 开始使用该软件之前开始考虑组织数据的最佳方式 。
这些是您应该问自己的一些问题：
您的桌子将主要如何使用？
预测您将如何使用表的数据通常决定了设计数据结构的最佳方法。
如果您要经常更新某些数据，通常最好将它们放在自己的表中。 不这样做可能会导致查询缓存（软件中维护的内部缓存）被一次又一次地转储和重建，因为它识别出有新信息。 如果这发生在单独的表中，其他列可以继续利用缓存。
一般来说，更新操作在较小的表上更快，而对复杂数据的深入分析通常是最好归结为大型表的任务，因为连接可能是昂贵的操作。
需要什么样的数据类型？
有时，如果您可以预先为数据大小提供一些限制，从长远来看，它可以为您节省大量时间。
例如，如果对于采用字符串值的特定字段的有效条目数量有限，则可以使用“enum”类型而不是“varchar”。 这种数据类型很紧凑，因此可以快速查询。
例如，如果您只有几种不同类型的用户，您可以使用可能的值创建处理该“枚举”的列：admin、moderator、poweruser、user。
您将查询哪些列？
提前知道您将重复查询哪些字段可以显着提高您的速度。
您希望用于搜索的索引列有很大帮助。 您可以使用以下语法在创建表时添加索引：
CREATE TABLE example_table ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(50), address VARCHAR(150), username VARCHAR(16), PRIMARY KEY (id), INDEX (username) );
如果我们知道我们的用户将通过用户名搜索信息，这将很有用。 这将创建一个具有以下属性的表：
解释example_table；
±---------±-------------±-----±----±--------±----- ----------+ | 领域 | 类型 | 空 | 钥匙 | 默认 | 额外 | ±---------±-------------±-----±----±--------±----- ----------+ | 编号 | 整数(11) | 否 | 优先级| 空 | 自动增量 | | 姓名 | varchar(50) | 是 | | 空 | | | 地址 | varchar(150) | 是 | | 空 | | | 用户名 | varchar(16) | 是 | 穆尔| 空 | | ±---------±-------------±-----±----±--------±----- ----------+ 4 行（0.00 秒）
如您所见，我们的表有两个索引。 第一个是主键，在本例中是 id 字段。 第二个是我们为 username 字段添加的索引。 这将改进使用该字段的查询。
尽管从概念的角度考虑在创建期间应该对哪些字段进行索引很有用，但将索引添加到预先存在的表也很简单。 您可以像这样添加一个：
CREATE INDEX index_name ON table_name ( column_name );
完成同样事情的另一种方法是：
ALTER TABLE table_name ADD INDEX ( column_name );
使用 Explain 在查询中查找要索引的点
如果您的程序以非常可预测的方式进行查询，您应该分析您的查询以确保它们尽可能使用索引。 使用 explain 功能很容易做到这一点。
我们将导入一个 MySQL 示例数据库，以了解其中一些是如何工作的：
wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2 tar xjvf employees_db-full-1.0.6.tar.bz2 cd employees_db mysql -u root -p -t < employees.sql
我们现在可以重新登录 MySQL，以便我们可以运行一些查询：
mysql -u root -p use employees;
首先，我们需要指定 MySQL 不应该使用它的缓存，这样我们才能准确地判断这些任务完成的时间：
SET GLOBAL query_cache_size = 0; SHOW VARIABLES LIKE "query_cache_size"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_size | 0 | +------------------+-------+ 1 row in set (0.00 sec)
现在，我们可以在大型数据集上运行一个简单的查询：
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+ | count(*) | +----------+ | 588322 | +----------+ 1 row in set (0.60 sec)
要查看 MySQL 是如何执行查询的，可以在查询前直接添加 explain 关键字：
EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | salaries | ALL | NULL | NULL | NULL | NULL | 2844738 | Using where | +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)
如果查看 key 字段，您会看到它的值为 NULL。 这意味着该查询没有使用任何索引。
让我们添加一个并再次运行查询，看看它是否加快了速度：
ALTER TABLE salaries ADD INDEX ( salary ); SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+ | count(*) | +----------+ | 588322 | +----------+ 1 row in set (0.14 sec)
如您所见，这显着提高了我们的查询性能。
与索引一起使用的另一个一般规则是注意表连接。 您应该在将用于连接表的任何列上创建索引并指定相同的数据类型。
例如，如果您有一个名为“cheeses”的表和一个名为“ingredients”的表，您可能希望在每个表中加入一个类似的成分_id 字段，该字段可能是一个 INT。
然后我们可以为这两个字段创建索引，我们的连接会加快。
优化查询以提高速度
试图加快查询速度的另一半是优化查询本身。 某些操作比其他操作的计算密集度更高。 通常有多种方法可以获得相同的结果，其中一些方法可以避免昂贵的操作。
根据您使用查询结果的目的，您可能只需要有限数量的结果。 例如，如果您只需要了解公司中是否有人收入低于 40,000 美元，您可以使用：
SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;
+--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10022 | 39935 | 2000-09-02 | 2001-09-02 | +--------+--------+------------+------------+ 1 row in set (0.00 sec)
这个查询执行得非常快，因为它基本上在第一个肯定结果时短路。
如果您的查询使用“或”比较，并且两个组件部分正在测试不同的字段，则您的查询可能会比必要的更长。
例如，如果您要搜索名字或姓氏以“Bre”开头的员工，则必须搜索两个单独的列。
SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';
如果我们在一个查询中搜索名字，在另一个查询中搜索匹配的姓氏，然后合并输出，则此操作可能会更快。 我们可以使用联合运算符来做到这一点：
SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';
在某些情况下，MySQL 会自动使用联合操作。 上面的示例实际上是 MySQL 会自动执行此操作的情况。 您可以通过再次使用 explain 检查排序类型来查看是否是这种情况。
结论
根据您的用例，您可以通过多种方式微调 MySQL 和 MariaDB 表和数据库。 本文仅包含一些可能有助于您入门的技巧。
这些数据库管理系统有关于如何优化和微调不同场景的大量文档。 具体细节很大程度上取决于您希望优化的功能类型，否则它们将被完全优化开箱即用。 一旦您确定了您的需求并掌握了将要重复执行的操作，您就可以学习调整这些查询的设置。
