如何在 VPS 上优化 MySQL 和 MariaDB 中的查询和表
介绍
MySQL 和 MariaDB 是数据库管理系统的热门选择。两者都使用 SQL 查询语言来输入和查询数据。
尽管 SQL 查询是简单易学的命令，但并非所有查询和数据库函数都具有相同的效率。随着您存储的信息量不断增长，以及如果您的数据库支持网站，随着您网站受欢迎程度的提高，这一点变得越来越重要。
在本指南中，我们将讨论您可以采取的一些简单措施来加速 MySQL 和 MariaDB 查询。我们假设您已经使用适合您操作系统的指南之一安装了 MySQL 或 MariaDB。
表格设计概述
提高查询速度的最基本方法之一就是从表结构设计本身开始。这意味着在开始使用该软件之前，您需要开始考虑组织数据的最佳方式。
您应该问自己以下一些问题：
您的桌子主要有何用途？
预测如何使用表的数据通常决定了设计数据结构的最佳方法。
如果您经常更新某些数据，最好将它们放在自己的表中。如果不这样做，可能会导致查询缓存（软件中维护的内部缓存）被一次又一次地转储和重建，因为它会识别出有新信息。如果这种情况发生在单独的表中，其他列可以继续利用缓存。
一般来说，在较小的表上更新操作会更快，而对复杂数据的深入分析通常最好交给大表来完成，因为连接可能是成本高昂的操作。
需要什么类型的数据类型？
有时，如果您可以预先对数据大小提供一些限制，那么从长远来看可以节省大量时间。
例如，如果某个采用字符串值的特定字段的有效条目数有限，则可以使用“enum”类型而不是“varchar”。此数据类型紧凑，因此查询速度快。
例如，如果您只有几种不同类型的用户，则可以创建处理该“枚举”的列，其可能的值如下：管理员、主持人、高级用户、用户。
您将查询哪些列？
提前知道您将重复查询哪些字段可以显著提高您的速度。
对您希望用于搜索的列进行索引非常有帮助。您可以在创建表时使用以下语法添加索引：
<pre> CREATE TABLE example_table ( id INTEGER NOT NULL AUTO_INCREMENT, 名称 VARCHAR(50), 地址 VARCHAR(150), 用户名 VARCHAR(16), PRIMARY KEY (id), <span class=“highlight”>INDEX (用户名)</span> ); </pre>
如果我们知道用户将按用户名搜索信息，这将很有用。这将创建一个具有以下属性的表：
<pre> 解释 example_table; </pre> <pre> ±---------±-------------±-----±--------±---------------+ | 字段 | 类型 | 空 | 键 | 默认 | 额外 | ±---------±-----±----±--------±---------------+ | id | int(11) | 否 | <span class=“highlight”>PRI</span> | NULL | auto_increment | | 名称 | varchar(50) | 是 | | NULL | | | 地址 | varchar(150) | 是 | | NULL | | | 用户名 | varchar(16) | 是 | <span class=“highlight”>MUL</span> | NULL | | ±---------±-------------±-----±----±--------±---------------+ 集合中的 4 行（0.00 秒）</pre>
如您所见，我们的表有两个索引。第一个是主键，在本例中是字段id。第二个是我们为该字段添加的索引username。这将改进利用此字段的查询。
虽然从概念角度考虑在创建期间应该索引哪些字段很有用，但向现有表添加索引也很简单。您可以像这样添加一个：
<pre> 在 <span class=“highlight”>表名</span>(<span class=“highlight”>列名</span> 上创建索引 <span class=“highlight”>索引名</span>； </pre>
完成同样的事情的另一种方法是：
<pre> 更改表 <span class=“highlight”>表名</span> 添加索引 ( <span class=“highlight”>列名</span> ); </pre>
使用 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”的表，您可能希望在每个表中连接类似的ingration_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%';
如果我们在一个查询中搜索名字，在另一个查询中搜索匹配的姓氏，然后合并输出，则此操作可能会更快。我们可以使用 union 运算符来执行此操作：
SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';
在某些情况下，MySQL 会自动使用 union 操作。上面的示例实际上是 MySQL 会自动执行此操作的一个例子。您可以通过再次使用检查所执行的排序类型来查看是否是这种情况explain。
结论
您可以通过多种方式根据用例来微调 MySQL 和 MariaDB 表和数据库。本文仅包含一些可能对您有所帮助的提示。
这些数据库管理系统有关于如何优化和微调不同场景的大量文档。具体细节很大程度上取决于您希望优化哪种功能，否则它们会完全开箱即用地进行优化。一旦您确定了需求并掌握了要重复执行的操作，您就可以学习如何调整这些查询的设置。