介绍
MySQL 和 MariaDB 是数据库管理系统中流行的选择,两者都使用 SQL 查询语言来输入和查询数据。
虽然 SQL 查询是易于学习的简单命令,但并非所有查询和数据库函数都以相同的效率工作,因为您存储的信息量越来越重要,并且如果您的数据库支持网站,则您的网站的受欢迎性会增加。
在本指南中,我们将讨论一些您可以采取的简单措施来加速您的MySQL和MariaDB查询,我们将假设您已经安装了MySQL或MariaDB,使用我们适用于您的操作系统的指南之一。
桌面设计一般
提高查询速度的最基本方法之一是从表结构设计开始,这意味着您需要开始考虑在开始使用软件之前组织数据的最佳方式。
以下是你应该问自己的一些问题:
你的桌子主要是如何使用的?
预测如何使用表中的数据通常决定了设计数据结构的最佳方法。
如果您要经常更新某些数据,通常最好把它们放在自己的表中。 未能做到这一点,可能会导致查询缓存,该软件内部维护的内部缓存,被卸载并重建一次又一次,因为它认识到有新信息。
在较小的表中,更新操作通常更快,而对复杂数据的深入分析通常是最适合大表的任务,因为合并可以是昂贵的操作。
需要哪些类型的数据?
有时,如果您可以提前为数据大小提供一些限制,那么从长远来看,它可以节省大量的时间。
例如,如果某个特定字段具有字符串值的有效条目数量有限,则可以使用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字段. 第二是我们为用户名字段添加的索引. 这将改善使用该字段的查询。
虽然从概念角度来看,考虑在创建过程中应该索引哪些字段是有用的,但也可以简单地将索引添加到现有表中。
CREATE INDEX index_name ON table_name(column_name);
另一种实现同样的方法是:
ALTER TABLE table_name ADD INDEX ( column_name );
** 使用解释以在查询中索引点**
如果你的程序以非常可预测的方式进行查询,你应该分析你的查询,以确保它们在可能时使用索引。
我们将导入一个MySQL样本数据库,看看其中一些是如何工作的:
1wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
2tar xjvf employees_db-full-1.0.6.tar.bz2
3cd employees_db
4mysql -u root -p -t < employees.sql
我们现在可以重新登录MySQL,这样我们就可以运行一些查询:
1mysql -u root -p
2use employees;
首先,我们需要说明MySQL不应该使用其缓存,这样我们就可以准确地判断这些任务需要多长时间来完成:
1SET GLOBAL query_cache_size = 0;
2SHOW VARIABLES LIKE "query_cache_size";
3
4+------------------+-------+
5| Variable_name | Value |
6+------------------+-------+
7| query_cache_size | 0 |
8+------------------+-------+
91 row in set (0.00 sec)
现在,我们可以在大数据集上运行一个简单的查询:
1SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
1+----------+
2| count(*) |
3+----------+
4| 588322 |
5+----------+
61 row in set (0.60 sec)
要查看MySQL如何执行查询,您可以直接在查询之前添加解释关键字:
1EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
1+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
2| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
3+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
4| 1 | SIMPLE | salaries | ALL | NULL | NULL | NULL | NULL | 2844738 | Using where |
5+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
61 row in set (0.00 sec)
如果您查看密钥字段,您将看到它的值为NULL,这意味着该查询没有使用索引。
让我们添加一个,然后再次运行查询,看看它是否加速:
1ALTER TABLE salaries ADD INDEX ( salary );
2SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
1+----------+
2| count(*) |
3+----------+
4| 588322 |
5+----------+
61 row in set (0.14 sec)
正如您所看到的,这大大提高了我们的查询性能。
使用索引的另一个一般规则是注意表合并,您应该创建索引,并在将用于合并表的任何列上指定相同的数据类型。
例如,如果您有一个名为奶酪的表和一个名为成分的表,您可能希望在每个表中加入类似的 ingredient_id 字段,这可能是 INT。
然后我们可以为这两个领域创建索引,我们的合并将加速。
优化速度请求
当尝试加速查询时,方程的另一半是优化查询本身. 某些操作比其他更为计算密集。
例如,如果您只需要查明公司是否有任何人赚不到 40,000 美元,则可以使用:
1SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;
1+--------+--------+------------+------------+
2| emp_no | salary | from_date | to_date |
3+--------+--------+------------+------------+
4| 10022 | 39935 | 2000-09-02 | 2001-09-02 |
5+--------+--------+------------+------------+
61 row in set (0.00 sec)
此查询执行非常快,因为它基本上在第一个正面的结果下缩短电路。
如果查询使用或比较,并且两个组件部分正在测试不同的字段,则查询可能比必要更长。
例如,如果您正在寻找一个名为Bre的员工,则需要搜索两个单独的列。
1SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';
如果我们在一个查询中搜索前名,在另一个查询中搜索匹配后名,然后将输出组合起来,这个操作可能会更快。
1SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';
在某些情况下,MySQL会自动使用联盟操作。上面的例子实际上是一个例子,MySQL会自动这样做。你可以通过再次使用解释来检查类型的排序来确定这是否的。
结论
您可以根据您的使用案例精简您的MySQL和MariaDB表格和数据库的非凡方式。
这些数据库管理系统有很好的文档如何优化和精简不同的场景. 细节很大程度上取决于您想要优化的功能类型,否则它们将完全优化。