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

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

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

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

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

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

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

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

7、SELECT *?會(huì)提取出一整行的數(shù)據(jù)，如果查詢條件中用的是組合索引進(jìn)行查找，還會(huì)導(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 索引會(huì)很快查詢出來(lái)，但實(shí)現(xiàn)了通過(guò) EXPLAIN 發(fā)現(xiàn)走的卻是 id 的索引（這里隱含了一個(gè)常見(jiàn)考點(diǎn)，在多個(gè)索引的情況下, MySQL 會(huì)如何選擇索引），而 id > 0 這個(gè)查詢條件沒(méi)啥用，直接導(dǎo)致了全表掃描， 所以在有多個(gè)索引的情況下一定要慎用，可以使用 force index 來(lái)強(qiáng)制走某個(gè)索引，以這個(gè)例子為例，可以強(qiáng)制走 post_id 索引，效果立桿見(jiàn)影。 這種由于表中有多個(gè)索引導(dǎo)致 MySQL 誤選索引造

9、成慢查詢的情況在業(yè)務(wù)中也是非常常見(jiàn)，一方面是表索引太多，另一方面也是由于 SQL 語(yǔ)句本身太過(guò)復(fù)雜導(dǎo)致， 針對(duì)本例這種復(fù)雜的 SQL 查詢，其實(shí)用 ElasticSearch 搜索引擎來(lái)查找更合適，有機(jī)會(huì)到時(shí)出一篇文章說(shuō)說(shuō)。 十、 使用 EXPLAIN 來(lái)查看 SQL 執(zhí)行計(jì)劃 上個(gè)點(diǎn)說(shuō)了，可以使用 EXPLAIN 來(lái)分析 SQL 的執(zhí)行情況，如怎么發(fā)現(xiàn)上文中的最左匹配原則不生效呢，執(zhí)行 「EXPLAIN + SQL 語(yǔ)句」可以發(fā)現(xiàn) key 為 None ,說(shuō)明確實(shí)沒(méi)有命中索引 我司在提供 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ù)讓日志減少了，降低日志刷盤(pán)的數(shù)據(jù)量和頻率，從而提高了效率 十二、 慢日志 SQL 定位 前面我們多次說(shuō)了 SQL 的慢查詢，那么該怎么定位這些慢查詢 SQL 呢，主要用到了以下幾個(gè)參數(shù) 這幾個(gè)參數(shù)一定要配好，再根據(jù)每條慢查詢對(duì)癥下藥，像我司每天都會(huì)把這些慢查詢提取出來(lái)通過(guò)郵件給形式發(fā)送給各個(gè)業(yè)務(wù)團(tuán)隊(duì)，以幫忙定位解決 總結(jié) 業(yè)務(wù)生產(chǎn)中可能還有很多 CASE 導(dǎo)致了慢查詢，其實(shí)細(xì)細(xì)品一下，都會(huì)發(fā)現(xiàn)這些都和 MySQL 索引的底層數(shù)據(jù) B+ 樹(shù) 有莫大的關(guān)系，強(qiáng)烈建議大家看一下我的另一篇介紹 B+ 樹(shù)的文章，好評(píng)如潮！相信大家看了之后，以上出現(xiàn)的問(wèn)題會(huì)有一個(gè)更深層次的理解，掌握底層，以不變應(yīng)萬(wàn)變!