如何在VPS上优化MySQL和MariaDB中的查询和表

MySQL和MariaDB是流行的数据库管理系统,可以与应用程序或网站结合使用。虽然查询语法易​​于学习并且非常强大,但是某些操作需要很长时间。本文将讨论一些

介绍


MySQL和MariaDB是数据库管理系统的流行选择。 两者都使用SQL查询语言来输入和查询数据。

尽管SQL查询是易于学习的简单命令,但并非所有查询和数据库功能都以相同的效率运行。 这变得越来越重要,因为你存储的信息量增加,如果你的数据库支持一个网站,随着你的网站的流行度的增加。

在本指南中,我们将讨论一些简单的措施,以加快您的MySQL和MariaDB查询。 我们假设您已经使用适合您的操作系统的一个指南安装了MySQL或MariaDB。

表设计总则


提高查询速度的最根本的方法之一是从表结构​​设计本身开始。 这意味着你需要开始考虑你开始使用软件之前 ,组织你的数据的最佳方式。

这些是一些问题,你应该问自己:

您的表如何主要使用?


预期如何使用表的数据通常决定了设计数据结构的最佳方法。

如果你经常更新某些数据,通常最好是在自己的表中。 不这样做可能导致查询缓存,在软件内维护的内部缓存,被转储和重建一遍又一遍,因为它认识到有新的信息。 如果这发生在单独的表中,其他列可以继续利用缓存。

通常,更小的表上的更新操作更快,而对复杂数据的深入分析通常是最好降级到大表的任务,因为连接可能是昂贵的操作。

需要什么类型的数据?


有时,如果您可以为您的数据大小提供一些约束,它可以节省您长期的重要时间。

例如,如果对于接受字符串值的特定字段有有限数量的有效条目,则可以使用“枚举”类型而不是“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)
);

如果我们知道我们的用户将通过用户名搜索信息,这将是有用的。 这将创建一个具有以下属性的表:

explain example_table;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | YES  |     | NULL    |                |
| address  | varchar(150) | YES  |     | NULL    |                |
| username | varchar(16)  | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

正如你所看到的,我们有两个索引。 第一个是主键,在这种情况下是id字段。 第二是我们增加了索引username外地。 这将提高使用此字段的查询。

虽然从概念的角度考虑在创建期间应该索引哪些字段是有用的,但是也可以很容易地将索引添加到预先存在的表中。 您可以添加如下:

CREATE INDEX index_name ON table_name(column_name);

完成同样的事情的另一种方法是:

ALTER TABLE table_name ADD INDEX ( column_name );

使用解释查找查询中的索引点

如果你的程序以一种非常可预测的方式进行查询,你应该分析你的查询,以确保他们尽可能使用索引。 这是很容易的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”的表,您可能需要在每个表中的类似的ingredient_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表和数据库。 本文包含一些可能有助于您入门的提示。

这些数据库管理系统有很多关于如何优化和微调不同场景的文档。 具体情况很大程度上取决于您希望优化哪种功能,否则它们将完全优化为现成的。 一旦你已经巩固了你的需求,并且想要重复执行什么操作,你可以学习调整这些查询的设置。

作者:Justin Ellingwood