10大sql書寫規(guī)范實(shí)戰(zhàn)技巧sql書寫優(yōu)化建議

《10大sql書寫規(guī)范實(shí)戰(zhàn)技巧sql書寫優(yōu)化建議》由會員分享，可在線閱讀，更多相關(guān)《10大sql書寫規(guī)范實(shí)戰(zhàn)技巧sql書寫優(yōu)化建議（10頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、SQL調(diào)優(yōu) | SQL 書寫規(guī)范及優(yōu)化技巧 10 個(gè)sql書寫規(guī)范及優(yōu)化技巧： 一、 使用延遲查詢優(yōu)化 limit [offset], [rows] 經(jīng)常出現(xiàn)類似以下的 SQL 語句: SELECT * FROM film LIMIT 100000, 10 offset 特別大! 這是我司出現(xiàn)很多慢 SQL 的主要原因之一，尤其是在跑任務(wù)需要分頁執(zhí)行時(shí)，經(jīng)常跑著跑著 offset 就跑到幾十萬了，導(dǎo)致任務(wù)越跑越慢。 LIMIT 能很好地解決分頁問題，但如果 offset 過大的話，會造成嚴(yán)重的性能問題，原因主要是因?yàn)?MySQL 每次會把一整行都掃描出來，掃描 offset

2、遍，找到 offset 之后會拋棄 offset 之前的數(shù)據(jù)，再從 offset 開始讀取 10 條數(shù)據(jù)，顯然，這樣的讀取方式問題。 可以通過延遲查詢的方式來優(yōu)化 假設(shè)有以下 SQL,有組合索引（sex, rating） SELECT FROM profiles where sex='M' order by rating limit 100000, 10; 則上述寫法可以改成如下寫法 這里利用了覆蓋索引的特性，先從覆蓋索引中獲取 100010 個(gè) id，再丟充掉前 100000 條 id，保留最后 10 個(gè) id 即可，丟掉 100000 條 id 不是什么大的開銷

3、，所以這樣可以顯著提升性能 二、 利用 LIMIT 1 取得唯一行 數(shù)據(jù)庫引擎只要發(fā)現(xiàn)滿足條件的一行數(shù)據(jù)則立即停止掃描，，這種情況適用于只需查找一條滿足條件的數(shù)據(jù)的情況 三、 注意組合索引，要符合最左匹配原則才能生效 假設(shè)存在這樣順序的一個(gè)聯(lián)合索引“col_1, col_2, col_3”。這時(shí)，指定條件的順序就很重要。 前面兩條會命中索引，第三條由于沒有先匹配 col_1，導(dǎo)致無法命中索引， 另外如果無法保證查詢條件里列的順序與索引一致，可以考慮將聯(lián)合索引 拆分為多個(gè)索引。 四、使用 LIKE 謂詞時(shí)，只有前方一致的匹配才能用到索引（最左匹配原則） 上例中，只有第三條會

4、命中索引，前面兩條進(jìn)行后方一致或中間一致的匹配無法命中索引 五、 簡單字符串表達(dá)式 模型字符串可以使用 _ 時(shí)， 盡可能避免使用 %, 假設(shè)某一列上為 char(5) 不推薦 推薦 六、盡量使用自增 id 作為主鍵 比如現(xiàn)在有一個(gè)用戶表，有人說身份證是唯一的，也可以用作主鍵，理論上確實(shí)可以，不過用身份證作主鍵的話，一是占用空間相對于自增主鍵大了很多，二是很容易引起頻繁的頁分裂，造成性能問題（什么是頁分裂，請參考這篇文章） 主鍵選擇的幾個(gè)原則：自增，盡量小，不要對主鍵進(jìn)行修改 七、如何優(yōu)化 count(*) 使用以下 sql 會導(dǎo)致慢查詢 原因是會造成全表掃描，有

5、人說?COUNT(*)?不是會利用主鍵索引去查找嗎，怎么還會慢，這就要談到 MySQL 中的聚簇索引和非聚簇索引了，聚簇索引葉子節(jié)點(diǎn)上存有主鍵值+整行數(shù)據(jù)，非聚簇索葉子節(jié)點(diǎn)上則存有輔助索引的列值 + 主鍵值，如下 所以就算對 COUNT(*) 使用主鍵查找，由于每次取出主鍵索引的葉子節(jié)點(diǎn)時(shí)，取的是一整行的數(shù)據(jù)，效率必然不高，但是非聚簇索引葉子節(jié)點(diǎn)只存儲了「列值 + 主鍵值」,這也啟發(fā)我們可以用非聚簇索引來優(yōu)化，假設(shè)表有一列叫 status, 為其加上索引后，可以用以下語句優(yōu)化: SELECT COUNT(status) FROM SomeTable 有人曾經(jīng)測過（見文末參考鏈接），假

6、設(shè)有 100 萬行數(shù)據(jù)，使用聚簇索引來查找行數(shù)的，比使用 COUNT(*) 查找速度快 10 幾倍。不過需要注意的是通過這種方式無法計(jì)算出 status 值為 null 的那些行 如果主鍵是連續(xù)的，可以利用 MAX(id) 來查找，MAX 也利用到了索引，只需要定位到最大 id 即可，性能極好，如下，秒現(xiàn)結(jié)果 SELECT MAX(id) FROM SomeTable 說句題句話，有人說用 MyISAM 引擎調(diào)用 COUNT(*) 非?？?，那是因?yàn)樗崆鞍研袛?shù)存在磁盤中了，直接拿，當(dāng)然很快，不過如果有 WHERE 的限制 八、避免使用 SELECT * ，盡量利用覆蓋索引來優(yōu)化性能

7、SELECT *?會提取出一整行的數(shù)據(jù)，如果查詢條件中用的是組合索引進(jìn)行查找，還會導(dǎo)致回表（先根據(jù)組合索引找到葉子節(jié)點(diǎn)，再根據(jù)葉子節(jié)點(diǎn)上的主鍵回表查詢一整行），降低性能，而如果我們所要的數(shù)據(jù)就在組合索引里，只需讀取組合索引列，這樣網(wǎng)絡(luò)帶寬將大大減少,假設(shè)有組合索引列 (col_1, col_2) 推薦用 SELECT col_1, col_2 FROM SomeTable WHERE col_1 = xxx AND col_2 = xxx 不推薦用 SELECT * FROM SomeTable WHERE col_1 = xxx AND col_2 = x

8、xx 九、 如有必要，使用 force index() 強(qiáng)制走某個(gè)索引 業(yè)務(wù)團(tuán)隊(duì)曾經(jīng)出現(xiàn)類似以下的慢 SQL 查詢 post_id 也加了索引，理論上走 post_id 索引會很快查詢出來，但實(shí)現(xiàn)了通過 EXPLAIN 發(fā)現(xiàn)走的卻是 id 的索引（這里隱含了一個(gè)常見考點(diǎn)，在多個(gè)索引的情況下, MySQL 會如何選擇索引），而 id > 0 這個(gè)查詢條件沒啥用，直接導(dǎo)致了全表掃描， 所以在有多個(gè)索引的情況下一定要慎用，可以使用 force index 來強(qiáng)制走某個(gè)索引，以這個(gè)例子為例，可以強(qiáng)制走 post_id 索引，效果立桿見影。 這種由于表中有多個(gè)索引導(dǎo)致 MySQL 誤選索引造

9、成慢查詢的情況在業(yè)務(wù)中也是非常常見，一方面是表索引太多，另一方面也是由于 SQL 語句本身太過復(fù)雜導(dǎo)致， 針對本例這種復(fù)雜的 SQL 查詢，其實(shí)用 ElasticSearch 搜索引擎來查找更合適，有機(jī)會到時(shí)出一篇文章說說。 十、 使用 EXPLAIN 來查看 SQL 執(zhí)行計(jì)劃 上個(gè)點(diǎn)說了，可以使用 EXPLAIN 來分析 SQL 的執(zhí)行情況，如怎么發(fā)現(xiàn)上文中的最左匹配原則不生效呢，執(zhí)行 「EXPLAIN + SQL 語句」可以發(fā)現(xiàn) key 為 None ,說明確實(shí)沒有命中索引 我司在提供 SQL 查詢的同時(shí)，也貼心地加了一個(gè) EXPLAIN 功能及 sql 的優(yōu)化建議，建議各大公司

10、效仿 ^_^,如圖示 十一、 批量插入，速度更快 當(dāng)需要插入數(shù)據(jù)時(shí)，批量插入比逐條插入性能更高 推薦用 -- 批量插入 INSERT INTO TABLE (id, user_id, title) VALUES (1, 2, 'a'),(2,3,'b'); 不推薦用 INSERT INTO TABLE (id, user_id, title) VALUES (1, 2, 'a'); INSERT INTO TABLE (id, user_id, title) VALUES (2,3,'b'); 批量插入 SQL 執(zhí)行效率高的主要原因是合并后日志量 MySQL 的 binl

11、og 和 innodb 的事務(wù)讓日志減少了，降低日志刷盤的數(shù)據(jù)量和頻率，從而提高了效率 十二、 慢日志 SQL 定位 前面我們多次說了 SQL 的慢查詢，那么該怎么定位這些慢查詢 SQL 呢，主要用到了以下幾個(gè)參數(shù) 這幾個(gè)參數(shù)一定要配好，再根據(jù)每條慢查詢對癥下藥，像我司每天都會把這些慢查詢提取出來通過郵件給形式發(fā)送給各個(gè)業(yè)務(wù)團(tuán)隊(duì)，以幫忙定位解決 總結(jié) 業(yè)務(wù)生產(chǎn)中可能還有很多 CASE 導(dǎo)致了慢查詢，其實(shí)細(xì)細(xì)品一下，都會發(fā)現(xiàn)這些都和 MySQL 索引的底層數(shù)據(jù) B+ 樹 有莫大的關(guān)系，強(qiáng)烈建議大家看一下我的另一篇介紹 B+ 樹的文章，好評如潮！相信大家看了之后，以上出現(xiàn)的問題會有一個(gè)更深層次的理解，掌握底層，以不變應(yīng)萬變!