溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇文章為大家展示了優化查詢語句的建議具體是什么，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。
我要做兩件事情，第一，指出為什么這個清單很糟糕，第二，列出我的清單，希望我的比較好些。繼續看吧，無畏的讀者們!
為什么那個清單很糟糕
1. 他的力氣沒使對地方
我們要遵循的一個準則就是如果你要優化代碼時，應該先找出瓶頸在哪。然而 Silverton 先生的力氣沒有用對地方。我認為 60% 的優化是基于清楚理解 SQL 和數據庫基礎的。你需要知道 join 和子查詢的區別，列索引，以及如何將數據規范化等等。另外的 35% 的優化是需要清楚數據庫選擇時的性能表現，例如 COUNT (*)可能很快也可能很慢，要看你選用什么數據庫引擎。還有一些其他要考慮的因素，例如數據庫在什么時候不用緩存，什么時候存在硬盤上而不存在內存中，什 么時候數據庫創建臨時表等等。剩下的5% 就很少會有人碰到了，但 Silverton 先生恰好在這上面花了大量的時間。我從來就沒用過 SQL_SAMLL_RESULT。
2. 很好的問題，但是很糟糕的解決方法
Silverton 先生提出了一些很好的問題。MySQL 針對長度可變的列如 TEXT 或 BLOB，將會使用動態行格式(dynamic row format)，這意味著排序將在硬盤上進行。我們的方法不是要回避這些數據類型，而是將這些數據類型從原來的表中分離開，放入另外一個表中。下面的 schema 可以說明這個想法：
| 1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE posts ( id int UNSIGNED NOT NULL AUTO_INCREMENT, author_id int UNSIGNED NOT NULL, created timestamp NOT NULL, PRIMARY KEY(id)); CREATE TABLE posts_data ( post_id int UNSIGNED NOT NULL. body text, PRIMARY KEY(post_id)); |
3. 有點匪夷所思……
他的許多建議都是讓人非常吃驚的，譬如“移除不必要的括號”。你這樣寫 SELECT * FROM posts WHERE (author_id = 5 AND published = 1)，還是這樣寫 SELECT * FROM posts WHERE author_id = 5 AND published = 1 ，都不重要。任何比較好的 DBMS 都會自動進行識別做出處理。這種細節就好像C語言中是i++快些還是++i快些。真的，如果你把精力都花在這上面了，那就不用寫代碼了。
我的列表
看看我的列表是不是更好吧。我先從最普遍的開始。
1. 建立基準，建立基準，建立基準!
如果需要做決定的話，我們需要數據說話。什么樣的查詢是最糟的?瓶頸在哪?我什么情況下會寫出糟糕的查詢?基準測試可以讓你模擬高壓情況，然后借助性能 測評工具，可以讓你發現數據庫配置中的錯誤。這樣的工具有 supersmack, ab, SysBench。這些工具可以直接測試你的數據庫(譬如 supersmack)，或者模擬網絡流量(譬如 ab)。
2. 性能測試，性能測試，性能測試!
那么，當你能夠建立一些高壓情況之后，你需要找出配置中的錯誤。這就是性能測評工具可以幫你做的了。它可以幫你發現配置中的瓶頸，不論是在內存中，CPU 中，網絡中，硬盤I/O，或者是以上皆有。
你要做的第一件事就是開啟慢查詢日志(slow query log)，裝上 mtop。這樣你就能獲取那些惡意的入侵者的信息了。有需要運行 10 秒的查詢語句正在破壞你的應用程序嗎?這些家伙會展示給你看他的查詢語句是怎么寫的。
在你發現那些很慢的查詢語句后，你需要用 MySQL 自帶的工具，如 EXPLAIN，SHOW STATUS，SHOW PROCESSLIST。它們會告訴你資源都消耗在哪了，查詢語句的缺陷在哪，譬如一個有三次 join 子查詢的查詢語句是否在內存中進行排序，還是在硬盤上進行。當然你也應該使用測評工具如 top，procinfo，vmstat 等等獲取更多系統性能信息。
3. 減小你的 schema
在你開始寫查詢語句之前，你需要設計 schema。記住將一個表裝入內存所需要的空間大概是行數*一行的大小。除非你覺得世界上的每個人都會在你的網站注冊 2 兆 8000 億次的話，否則你不需要采用 BITINT 作為你的 user_id。同樣的，如果一個文本列是固定大小的話(譬如 US 郵編，通常是”XXXXX-XXXX”的形式)，采用 VARCHAR 的話會給每行增加多余的字節。
有些人對數據庫規范化不以為意， 他們說這樣會形成相當復雜的 schema。然而適當的規范化會減少化冗余數據。(適當的規范化)就意味著犧牲少許性能，換取整體上更少的 footprint，這種性能換取內存在計算機科學中是很常見的。最好的方法是 IMO，就是開始先規范化，之后如果性能需要的話，再反規范化。你的數據庫將會更邏輯化，你也不用過早的進行優化。(譯者注，這一段我不是很理解，可能翻 譯錯了，歡迎糾正。)
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 是對讀數據進行的優化。經常要訪問的數據存在一個表中，那些不經常訪問的數據放在另一個。被拆分后，不經常訪問的數據占據更少的內存。你也可以優化寫數 據，經常更新的數據放在一個表，不經常更新的放在另一個表。這可以使緩存更高效，因為 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 = ‘Goldstein’，而 last_name 沒有索引的話，那么 DBMS 將會查詢每一行，看看是否等于“Goldstein”。索引通常是B-tree(還有其他的類型)，可以加快比較的速度。
你需要給你要 select,group,order,join 的列加上索引。顯然每個索引所需的空間正比于表的行數，所以越多的索引將會占用更多的內存。而且寫數據時，索引也會有影響，因為每次寫數據時都會更新對應 的索引。你需要取一個平衡點，取決每個系統和實施代碼的需要。
上述內容就是優化查詢語句的建議具體是什么，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
