介绍
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 表和数据库。 本文仅包含一些可能有助于您入门的技巧。
这些数据库管理系统有关于如何优化和微调不同场景的大量文档。 具体细节很大程度上取决于您希望优化的功能类型,否则它们将被完全优化开箱即用。 一旦您确定了您的需求并掌握了将要重复执行的操作,您就可以学习调整这些查询的设置。