如何对MySQL和MariaDB中的查询和表进行优化-提升查询效率

前言

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

尽管SQL查询是简单易学的命令，但并不是所有的查询和数据库函数都具有相同的效率。随着你存储的信息量的增长，如果你的数据库支持一个网站，随着网站的受欢迎程度的增加，这就变得越来越重要。

在本指南中，我们将讨论一些可以提高MySQL和MariaDB查询速度的简单方法。我们假设您已经使用我们的指南安装了MySQL或MariaDB，该指南适合您的操作系统。

表设计概述

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

下面是一些你应该问自己的问题:

表的主要用途是什么?

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

如果需要经常更新某些数据，通常最好将它们放在单独的表中。如果做不到这一点，就会导致查询缓存(在软件中维护的内部缓存)被一次次地转储和重建，因为它发现有新的信息。如果这发生在单独的表中，其他列可以继续利用缓存。

更新操作通常在较小的表上更快，而对复杂数据的深入分析通常是一项最好留给大型表的任务，因为连接操作的开销很大。

需要什么样的数据类型?

有时，如果您能够预先对数据大小进行一些限制，从长远来看，它可以为您节省大量时间。

例如，如果某个字段的值为string，有效的条目数量有限，那么可以使用enum类型而不是varchar类型。这种数据类型很紧凑，因此查询起来很快。

例如，如果用户只有几种不同的类型，可以在处理enum的列中设置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在查询中查找要索引的点

如果你的程序以一种可预测的方式进行查询，你应该分析你的查询，以确保它们尽可能地使用索引。使用explain函数很容易做到这一点。

我们将导入一个MySQL sample数据库来看看其中的一些工作原理:

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类型。

然后，我们可以为这两个字段创建索引，我们的连接将加快速度。

优化查询以提高速度

在尝试加速查询时，等式的另一半是优化查询本身。某些操作比其他操作的计算量更大。通常有多种方法可以得到相同的结果，其中一些方法可以避免昂贵的操作。

根据你所使用的查询结果,你可能只需要一个有限数量的结果。例如，如果你只需要知道该公司是否有人年收入低于4万元，你可以使用:

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)

这个查询执行得非常快，因为它基本上在第一个正结果时短路。

如果你的查询使用“or”比较，并且两个组件测试不同的字段，你的查询可能会比必要的长。

例如，如果要搜索姓或名以“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表和数据库中对用例调整的方法有很多种。本文仅包含一些可能对您入门有用的技巧。

这些数据库管理系统提供很多的帮助文档教你如何优化和调整不同的场景。具体细节很大程度上取决于您希望优化的功能类型，否则它们将被完全优化，开箱即用。一旦你确定了你的需求，并掌握了执行的操作，你就可以学习调整这些查询的设置。