MySQL語句的優(yōu)化方法

137次閱讀

共計 3636 個字符，預計需要花費 10 分鐘才能閱讀完成。

本篇內容主要講解“MySQL 語句的優(yōu)化方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“MySQL 語句的優(yōu)化方法”吧!

1. 建立基準，建立基準，建立基準！
如果需要做決定的話，我們需要數據說話。什么樣的查詢是最糟的？瓶頸在哪？我什么情況下會寫出糟糕的查詢？基準測試可以讓你模擬高壓情況，然后借助性能測評工具，可以讓你發(fā)現配置中的錯誤。這樣的工具有 supersmack, ab, SysBench。這些工具可以直接測試你的數據庫 (譬如 supersmack)，或者模擬網絡流量（譬如 ab）。

2. 性能測試，性能測試，性能測試!

那么，當你能夠建立一些高壓情況之后，你需要找出配置中的錯誤。這就是性能測評工具可以幫你做的了。它可以幫你發(fā)現配置中的瓶頸，不論是在內存中，CPU 中，網絡中，硬盤 I /O，或者是以上皆有。

你要做的第一件事就是開啟慢查詢日志 (slow query log)，裝上 mtop。這樣你就能獲取那些惡意的入侵者的信息了。有需要運行 10 秒的查詢語句正在破壞你的應用程序嗎？這些家伙會展示給你看他的查詢語句是怎么寫的。

在你發(fā)現那些很慢的查詢語句后，你需要用 MySQL 自帶的工具，如 EXPLAIN，SHOW STATUS，SHOW PROCESSLIST。它們會告訴你資源都消耗在哪了，查詢語句的缺陷在哪，譬如一個有三次 join 子查詢的查詢語句是否在內存中進行排序，還是在硬盤上進行。當然你也應該使用測評工具如 top，procinfo，vmstat 等等獲取更多系統性能信息。

3. 減小你的 schema

在你開始寫查詢語句之前，你需要設計 schema。記住將一個表裝入內存所需要的空間大概是行數 * 一行的大小。除非你覺得世界上的每個人都會在你的網站注冊 2 兆 8000 億次的話，否則你不需要采用 BITINT 作為你的 user_id。同樣的，如果一個文本列是固定大小的話（譬如 US 郵編，通常是”XXXXX-XXXX”的形式），采用 VARCHAR 的話會給每行增加多余的字節(jié)。

有些人對數據庫規(guī)范化不以為意，他們說這樣會形成相當復雜的 schema。然而適當的規(guī)范化會減少化冗余數據。（適當的規(guī)范化）就意味著犧牲少許性能，換取整體上更少的 footprint，這種性能換取內存在計算機科學中是很常見的。最好的方法是 IMO，就是開始先規(guī)范化，之后如果性能需要的話，再反規(guī)范化。你的數據庫將會更邏輯化，你也不用過早的進行優(yōu)化。（譯者注，這一段我不是很理解，可能翻譯錯了，歡迎糾正。）

4. 拆分你的表

通常有些表只有一些列你是經常需要更新的。例如對于一個博客，你需要在許多不同地方顯示標題（如最近的文章列表），只在某個特定頁顯示概要或者全文。水平垂直拆分是很有幫助的：

CREATE TABLE posts (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
author_id int UNSIGNED NOT NULL,
title varchar(128),
created timestamp NOT NULL,
PRIMARY KEY(id)
);

CREATE TABLE posts_data (
post_id int UNSIGNED NOT NULL,
teaser text,
body text,
PRIMARY KEY(post_id)
);
上面的 schema 是對讀數據進行的優(yōu)化。經常要訪問的數據存在一個表中，那些不經常訪問的數據放在另一個。被拆分后，不經常訪問的數據占據更少的內存。你也可以優(yōu)化寫數據，經常更新的數據放在一個表，不經常更新的放在另一個表。這可以使緩存更高效，因為 MySQL 不需要讓沒有更新過的數據移出緩存。

5. 不要過度使用 artificial primary key

artificial primary key 非常棒，因為他們使得 schema 更少的變化。如果我們將地理信息存在以美國郵編為基礎的表中，如果郵編系統突然改變了，那我們就會有大麻煩了。另一方面，采用 natural key 有時候也很棒，譬如我們需要 join 多對多的關系表時，我們不應該這樣：

CREATE TABLE posts_tags (
relation_id int UNSIGNED NOT NULL AUTO_INCREMENT,
post_id int UNSIGNED NOT NULL,
tag_id int UNSIGNED NOT NULL,
PRIMARY KEY(relation_id),
UNIQUE INDEX(post_id, tag_id)
);
artificial key 完全是多余的，而且 post-tag 關系的數量將會受到整形數據的系統最大值的限制。

CREATE TABLE posts_tags (
post_id int UNSIGNED NOT NULL,
tag_id int UNSIGNED NOT NULL,
PRIMARY KEY(post_id, tag_id)
);
6. 學習索引

你選擇的索引的好壞很重要，不好的話可能破壞數據庫。對那些還沒有在數據庫學習很深入的人來說，索引可以看作是就是 hash 排序。例如如果我們用查詢語句 SELECT * FROM users WHERE last_name = lsquo;Goldstein rsquo;，而 last_name 沒有索引的話，那么 DBMS 將會查詢每一行，看看是否等于“Goldstein”。索引通常是 B -tree（還有其他的類型），可以加快比較的速度。

你需要給你要 select,group,order,join 的列加上索引。顯然每個索引所需的空間正比于表的行數，所以越多的索引將會占用更多的內存。而且寫數據時，索引也會有影響，因為每次寫數據時都會更新對應的索引。你需要取一個平衡點，取決每個系統和實施代碼的需要。

7.SQL 不是 C

C 是經典的過程語言，對于一個程序員來說，C 語言也是個陷阱，使你錯誤的以為 SQL 也是一種過程語言（當然 SQL 也不是功能語言也不是面向對象的）。你不要想象對數據進行操作，而是要想象有一組數據，以及它們之間的關系。經常使用子查詢時會出現錯誤的用法。

SELECT a.id,
(SELECT MAX(created)
FROM posts
WHERE author_id = a.id)
AS latest_post
FROM authors a
因為這個子查詢是耦合的，子查詢要使用外部查詢的信息，我們應該使用 join 來代替。

SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN posts p
ON (a.id = p.author_id)
GROUP BY a.id
8. 理解你的引擎

MySQL 有兩種存儲引擎：MyISAM 和 InnoDB。它們分別有自己的性能特點和考慮因素。總體來講，MyISAM 適合讀數據很多的情況，InnoDB 適合寫數據很多的情況，但也有很多情況下正好相反。最大的區(qū)別是它們如何處理 COUNT 函數。

MyISAM 緩存有表 meta-data，如行數。這就意味著，COUNT(*) 對于一個結構很好的查詢是不需要消耗多少資源的。然后對于 InnoDB 來說，就沒有這種緩存。舉個例子，我們要對一個查詢來分頁，假設你有這樣一個語句 SELECT * FROM users LIMIT 5,10，而運行 SELECT COUNT(*) FROM users LIMIT 5,10 時，對于 MyISAM 很快完成，而對 InnoDB 就需要和第一個語句相同的時間。MySQL 有個 SQL_CALC_FOUND_ROWS 選項，可以告訴 InnoDB 運行查詢語句時就計算行數，之后再從 SELECT FOUND_ROWS()來獲取。這是 MySQL 特有的。但使用 InnoDB 有時候是非常必要的，你可以獲得一些功能（如行鎖定，stord procedure 等）。

9.MySQL 特定的快捷鍵

MySQL 提供了許多擴展，方便使用。譬如 INSERT hellip; SELECT, INSERT hellip; ON DUPLICATE KEY UPDATE, 以及 REPLACE。

我能用到它們時是毫不猶豫的，因為它們很方便，能在許多情況下發(fā)揮不錯的效果。但是 MySQL 也有一些危險的關鍵字，應該少用。例如 INSERT DELAYED, 它告訴 MySQL 不需要立即插入數據(例如在寫日志的時候)。但問題是如果在很高數據量的情況下，插入可能會被無限期延遲，導致插入隊列爆滿。你也可以使用 MySQL 的索引提示來指出哪些索引是需要使用的。MySQL 大部分時間運行是不錯的，但如果 schema 設計不好的話或語句寫得不好的話，MySQL 的表現可能很糟糕。