前言

MySQL和MariaDB是數(shù)據(jù)庫(kù)管理系統(tǒng)的流行選擇。兩者都使用SQL查詢語言來輸入和查詢數(shù)據(jù)。

盡管SQL查詢是簡(jiǎn)單易學(xué)的命令，但并不是所有的查詢和數(shù)據(jù)庫(kù)函數(shù)都具有相同的效率。隨著你存儲(chǔ)的信息量的增長(zhǎng)，如果你的數(shù)據(jù)庫(kù)支持一個(gè)網(wǎng)站，隨著網(wǎng)站的受歡迎程度的增加，這就變得越來越重要。

在本指南中，我們將討論一些可以提高M(jìn)ySQL和MariaDB查詢速度的簡(jiǎn)單方法。我們假設(shè)您已經(jīng)使用我們的指南安裝了MySQL或MariaDB，該指南適合您的操作系統(tǒng)。

表設(shè)計(jì)概述

提高查詢速度的最基本方法之一是從表結(jié)構(gòu)設(shè)計(jì)本身開始的。這意味著在開始使用軟件之前，就需要開始考慮組織數(shù)據(jù)的最佳方式。

下面是一些你應(yīng)該問自己的問題:

表的主要用途是什么?

預(yù)測(cè)如何使用表中的數(shù)據(jù)通常決定了設(shè)計(jì)數(shù)據(jù)結(jié)構(gòu)的最佳方法。

如果需要經(jīng)常更新某些數(shù)據(jù)，通常最好將它們放在單獨(dú)的表中。如果做不到這一點(diǎn)，就會(huì)導(dǎo)致查詢緩存(在軟件中維護(hù)的內(nèi)部緩存)被一次次地轉(zhuǎn)儲(chǔ)和重建，因?yàn)樗l(fā)現(xiàn)有新的信息。如果這發(fā)生在單獨(dú)的表中，其他列可以繼續(xù)利用緩存。

更新操作通常在較小的表上更快，而對(duì)復(fù)雜數(shù)據(jù)的深入分析通常是一項(xiàng)最好留給大型表的任務(wù)，因?yàn)檫B接操作的開銷很大。

需要什么樣的數(shù)據(jù)類型?

有時(shí)，如果您能夠預(yù)先對(duì)數(shù)據(jù)大小進(jìn)行一些限制，從長(zhǎng)遠(yuǎn)來看，它可以為您節(jié)省大量時(shí)間。

例如，如果某個(gè)字段的值為string，有效的條目數(shù)量有限，那么可以使用enum類型而不是varchar類型。這種數(shù)據(jù)類型很緊湊，因此查詢起來很快。

例如，如果用戶只有幾種不同的類型，可以在處理enum的列中設(shè)置admin、moderator、poweruser、user。

要查詢哪些列?

提前知道哪些字段會(huì)被重復(fù)查詢可以極大地提高速度。

為你希望用于搜索的列建立索引大有幫助。你可以使用以下語法在創(chuàng)建表時(shí)添加索引:

CREATE TABLE example_table ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(50), address VARCHAR(150)
, username VARCHAR(16), PRIMARY KEY (id), INDEX (username) );

如果我們知道用戶將根據(jù)用戶名搜索信息，這將很有用。這將創(chuàng)建一個(gè)表,這些屬性:

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) 

如你所見，我們的表有兩個(gè)索引。第一個(gè)是主鍵，在本例中是id字段。第二個(gè)是我們?yōu)?code>username字段添加的索引。這將改進(jìn)利用該字段的查詢。

雖然從概念的角度來看，在創(chuàng)建過程中考慮哪些字段應(yīng)該被索引是很有用的，但向現(xiàn)有的表添加索引也很簡(jiǎn)單。你可以像這樣添加一個(gè):

CREATE INDEX index_name ON table_name(column_name); 

另一種方法完成同樣的事情是這樣的:

ALTER TABLE table_name ADD INDEX ( column_name ); 

使用Explain在查詢中查找要索引的點(diǎn)

如果你的程序以一種可預(yù)測(cè)的方式進(jìn)行查詢，你應(yīng)該分析你的查詢，以確保它們盡可能地使用索引。使用explain函數(shù)很容易做到這一點(diǎn)。

我們將導(dǎo)入一個(gè)MySQL sample數(shù)據(jù)庫(kù)來看看其中的一些工作原理:

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

現(xiàn)在我們可以登錄到MySQL，以便運(yùn)行一些查詢:

mysql -u root -p
use employees;

首先，我們需要指定MySQL不應(yīng)該使用它的緩存，這樣我們就可以準(zhǔn)確地判斷這些任務(wù)完成所需的時(shí)間:

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)

現(xiàn)在，我們可以在大型數(shù)據(jù)集上運(yùn)行一個(gè)簡(jiǎn)單的查詢:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.60 sec)

要查看MySQL如何執(zhí)行查詢，你可以直接在查詢之前添加explain關(guān)鍵字:

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字段，你會(huì)發(fā)現(xiàn)它的值是NULL。這意味著此查詢沒有使用索引。

讓我們添加一個(gè)并再次運(yùn)行查詢，看看它是否加快了速度:

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)

如你所見，這顯著提高了我們的查詢性能。

另一個(gè)使用指數(shù)一般規(guī)則是注意表連接。您應(yīng)該創(chuàng)建指數(shù)和任何列上指定相同的數(shù)據(jù)類型,將用于連接表。

例如，如果你有一張名為cheeses的表和一張名為“ingredients”的表，你可能希望對(duì)這兩張表的ingredient_id字段進(jìn)行聯(lián)結(jié)操作，這兩個(gè)字段可以是INT類型。

然后，我們可以為這兩個(gè)字段創(chuàng)建索引，我們的連接將加快速度。

優(yōu)化查詢以提高速度

在嘗試加速查詢時(shí)，等式的另一半是優(yōu)化查詢本身。某些操作比其他操作的計(jì)算量更大。通常有多種方法可以得到相同的結(jié)果，其中一些方法可以避免昂貴的操作。

根據(jù)你所使用的查詢結(jié)果,你可能只需要一個(gè)有限數(shù)量的結(jié)果。例如，如果你只需要知道該公司是否有人年收入低于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)

這個(gè)查詢執(zhí)行得非常快，因?yàn)樗旧显诘谝粋€(gè)正結(jié)果時(shí)短路。

如果你的查詢使用“or”比較，并且兩個(gè)組件測(cè)試不同的字段，你的查詢可能會(huì)比必要的長(zhǎng)。

例如，如果要搜索姓或名以“Bre”開頭的員工，則必須搜索兩個(gè)獨(dú)立的列。

SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';

如果在一個(gè)查詢中搜索名字，在另一個(gè)查詢中搜索姓氏，然后合并輸出，那么這個(gè)操作可能會(huì)更快。我們可以使用union操作符:

SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';

在某些情況下，MySQL會(huì)自動(dòng)使用union操作。上面的例子實(shí)際上是MySQL自動(dòng)執(zhí)行此操作的一種情況。你可以通過再次使用explain檢查排序的類型來查看是否是這種情況。

總結(jié)

在MySQL和MariaDB表和數(shù)據(jù)庫(kù)中對(duì)用例調(diào)整的方法有很多種。本文僅包含一些可能對(duì)您入門有用的技巧。

這些數(shù)據(jù)庫(kù)管理系統(tǒng)提供很多的幫助文檔教你如何優(yōu)化和調(diào)整不同的場(chǎng)景。具體細(xì)節(jié)很大程度上取決于您希望優(yōu)化的功能類型，否則它們將被完全優(yōu)化，開箱即用。一旦你確定了你的需求，并掌握了執(zhí)行的操作，你就可以學(xué)習(xí)調(diào)整這些查詢的設(shè)置。文章來源地址http://www.zghlxwxcb.cn/news/detail-678940.html

到了這里，關(guān)于如何對(duì)MySQL和MariaDB中的查詢和表進(jìn)行優(yōu)化-提升查詢效率的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！