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