優(yōu)化查詢語句的建議具體是什么

本篇文章為大家展示了優(yōu)化查詢語句的建議具體是什么，內(nèi)容簡明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。

我要做兩件事情，第一，指出為什么這個(gè)清單很糟糕，第二，列出我的清單，希望我的比較好些。繼續(xù)看吧，無畏的讀者們!
　　為什么那個(gè)清單很糟糕
　　1. 他的力氣沒使對(duì)地方
　 　我們要遵循的一個(gè)準(zhǔn)則就是如果你要優(yōu)化代碼時(shí)，應(yīng)該先找出瓶頸在哪。然而 Silverton 先生的力氣沒有用對(duì)地方。我認(rèn)為 60% 的優(yōu)化是基于清楚理解 SQL 和數(shù)據(jù)庫基礎(chǔ)的。你需要知道 join 和子查詢的區(qū)別，列索引，以及如何將數(shù)據(jù)規(guī)范化等等。另外的 35% 的優(yōu)化是需要清楚數(shù)據(jù)庫選擇時(shí)的性能表現(xiàn)，例如 COUNT (*)可能很快也可能很慢，要看你選用什么數(shù)據(jù)庫引擎。還有一些其他要考慮的因素，例如數(shù)據(jù)庫在什么時(shí)候不用緩存，什么時(shí)候存在硬盤上而不存在內(nèi)存中，什 么時(shí)候數(shù)據(jù)庫創(chuàng)建臨時(shí)表等等。剩下的5% 就很少會(huì)有人碰到了，但 Silverton 先生恰好在這上面花了大量的時(shí)間。我從來就沒用過 SQL_SAMLL_RESULT。
　　2. 很好的問題，但是很糟糕的解決方法
　　Silverton 先生提出了一些很好的問題。MySQL 針對(duì)長度可變的列如 TEXT 或 BLOB，將會(huì)使用動(dòng)態(tài)行格式(dynamic row format)，這意味著排序?qū)⒃谟脖P上進(jìn)行。我們的方法不是要回避這些數(shù)據(jù)類型，而是將這些數(shù)據(jù)類型從原來的表中分離開，放入另外一個(gè)表中。下面的 schema 可以說明這個(gè)想法：

　　3. 有點(diǎn)匪夷所思……
　 　他的許多建議都是讓人非常吃驚的，譬如“移除不必要的括號(hào)”。你這樣寫 SELECT * FROM posts WHERE (author_id = 5 AND published = 1)，還是這樣寫 SELECT * FROM posts WHERE author_id = 5 AND published = 1 ，都不重要。任何比較好的 DBMS 都會(huì)自動(dòng)進(jìn)行識(shí)別做出處理。這種細(xì)節(jié)就好像C語言中是i++快些還是++i快些。真的，如果你把精力都花在這上面了，那就不用寫代碼了。

　　我的列表
　　看看我的列表是不是更好吧。我先從最普遍的開始。
　　1. 建立基準(zhǔn)，建立基準(zhǔn)，建立基準(zhǔn)!
　 　如果需要做決定的話，我們需要數(shù)據(jù)說話。什么樣的查詢是最糟的?瓶頸在哪?我什么情況下會(huì)寫出糟糕的查詢?基準(zhǔn)測試可以讓你模擬高壓情況，然后借助性能 測評(píng)工具，可以讓你發(fā)現(xiàn)數(shù)據(jù)庫配置中的錯(cuò)誤。這樣的工具有 supersmack, ab, SysBench。這些工具可以直接測試你的數(shù)據(jù)庫(譬如 supersmack)，或者模擬網(wǎng)絡(luò)流量(譬如 ab)。
　　2. 性能測試，性能測試，性能測試!
　　那么，當(dāng)你能夠建立一些高壓情況之后，你需要找出配置中的錯(cuò)誤。這就是性能測評(píng)工具可以幫你做的了。它可以幫你發(fā)現(xiàn)配置中的瓶頸，不論是在內(nèi)存中，CPU 中，網(wǎng)絡(luò)中，硬盤I/O，或者是以上皆有。
　　你要做的第一件事就是開啟慢查詢?nèi)罩?slow query log)，裝上 mtop。這樣你就能獲取那些惡意的入侵者的信息了。有需要運(yùn)行 10 秒的查詢語句正在破壞你的應(yīng)用程序嗎?這些家伙會(huì)展示給你看他的查詢語句是怎么寫的。
　 　在你發(fā)現(xiàn)那些很慢的查詢語句后，你需要用 MySQL 自帶的工具，如 EXPLAIN，SHOW STATUS，SHOW PROCESSLIST。它們會(huì)告訴你資源都消耗在哪了，查詢語句的缺陷在哪，譬如一個(gè)有三次 join 子查詢的查詢語句是否在內(nèi)存中進(jìn)行排序，還是在硬盤上進(jìn)行。當(dāng)然你也應(yīng)該使用測評(píng)工具如 top，procinfo，vmstat 等等獲取更多系統(tǒng)性能信息。
　　3. 減小你的 schema
　　在你開始寫查詢語句之前，你需要設(shè)計(jì) schema。記住將一個(gè)表裝入內(nèi)存所需要的空間大概是行數(shù)*一行的大小。除非你覺得世界上的每個(gè)人都會(huì)在你的網(wǎng)站注冊 2 兆 8000 億次的話，否則你不需要采用 BITINT 作為你的 user_id。同樣的，如果一個(gè)文本列是固定大小的話(譬如 US 郵編，通常是”XXXXX-XXXX”的形式)，采用 VARCHAR 的話會(huì)給每行增加多余的字節(jié)。
　　有些人對(duì)數(shù)據(jù)庫規(guī)范化不以為意， 他們說這樣會(huì)形成相當(dāng)復(fù)雜的 schema。然而適當(dāng)?shù)囊?guī)范化會(huì)減少化冗余數(shù)據(jù)。(適當(dāng)?shù)囊?guī)范化)就意味著犧牲少許性能，換取整體上更少的 footprint，這種性能換取內(nèi)存在計(jì)算機(jī)科學(xué)中是很常見的。最好的方法是 IMO，就是開始先規(guī)范化，之后如果性能需要的話，再反規(guī)范化。你的數(shù)據(jù)庫將會(huì)更邏輯化，你也不用過早的進(jìn)行優(yōu)化。(譯者注，這一段我不是很理解，可能翻 譯錯(cuò)了，歡迎糾正。)
4. 拆分你的表
　　通常有些表只有一些列你是經(jīng)常需要更新的。例如對(duì)于一個(gè)博客，你需要在許多不同地方顯示標(biāo)題(如最近的文章列表)，只在某個(gè)特定頁顯示概要或者全文。水平垂直拆分是很有幫助的：

　 　上面的 schema 是對(duì)讀數(shù)據(jù)進(jìn)行的優(yōu)化。經(jīng)常要訪問的數(shù)據(jù)存在一個(gè)表中，那些不經(jīng)常訪問的數(shù)據(jù)放在另一個(gè)。被拆分后，不經(jīng)常訪問的數(shù)據(jù)占據(jù)更少的內(nèi)存。你也可以優(yōu)化寫數(shù) 據(jù)，經(jīng)常更新的數(shù)據(jù)放在一個(gè)表，不經(jīng)常更新的放在另一個(gè)表。這可以使緩存更高效，因?yàn)?MySQL 不需要讓沒有更新過的數(shù)據(jù)移出緩存。
　　5. 不要過度使用 artificial primary key
　 　artificial primary key 非常棒，因?yàn)樗麄兪沟?schema 更少的變化。如果我們將地理信息存在以美國郵編為基礎(chǔ)的表中，如果郵編系統(tǒng)突然改變了，那我們就會(huì)有大麻煩了。另一方面，采用 natural key 有時(shí)候也很棒，譬如我們需要 join 多對(duì)多的關(guān)系表時(shí)，我們不應(yīng)該這樣：

　　artificial key 完全是多余的，而且 post-tag 關(guān)系的數(shù)量將會(huì)受到整形數(shù)據(jù)的系統(tǒng)最大值的限制。

　　6. 學(xué)習(xí)索引
　 　你選擇的索引的好壞很重要，不好的話可能破壞數(shù)據(jù)庫。對(duì)那些還沒有在數(shù)據(jù)庫學(xué)習(xí)很深入的人來說，索引可以看作是就是 hash 排序。例如如果我們用查詢語句 SELECT * FROM users WHERE last_name = ‘Goldstein’，而 last_name 沒有索引的話，那么 DBMS 將會(huì)查詢每一行，看看是否等于“Goldstein”。索引通常是B-tree(還有其他的類型)，可以加快比較的速度。
　　你需要給你要 select,group,order,join 的列加上索引。顯然每個(gè)索引所需的空間正比于表的行數(shù)，所以越多的索引將會(huì)占用更多的內(nèi)存。而且寫數(shù)據(jù)時(shí)，索引也會(huì)有影響，因?yàn)槊看螌憯?shù)據(jù)時(shí)都會(huì)更新對(duì)應(yīng) 的索引。你需要取一個(gè)平衡點(diǎn)，取決每個(gè)系統(tǒng)和實(shí)施代碼的需要。

上述內(nèi)容就是優(yōu)化查詢語句的建議具體是什么，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注億速云行業(yè)資訊頻道。