SQL語(yǔ)句優(yōu)化

一囱晴、單表優(yōu)化

除非單表數(shù)據(jù)未來(lái)會(huì)一直不斷上漲澳泵，否則不要一開始就考慮拆分实愚，拆分會(huì)帶來(lái)邏輯、部署兔辅、運(yùn)維的各種復(fù)雜度腊敲。一般以整型值為主的表在千萬(wàn)級(jí)以下，字符串為主的表在五百萬(wàn)以下是沒(méi)有太大問(wèn)題的维苔。事實(shí)上很多時(shí)候 MySQL 單表的性能依然有不少優(yōu)化空間碰辅，甚至能正常支撐千萬(wàn)級(jí)以上的數(shù)據(jù)量。

二介时、字段

1. 盡量使用 tinyInt没宾、smallInt、mediumInt 作為整數(shù)類型而非 int沸柔，如果非負(fù)則加上 unSigned循衰。
2. varchar 的長(zhǎng)度只分配真正需要的空間。
3. 使用枚舉或整數(shù)代替字符串類型褐澎。
4. 盡量使用 timeStamp 而非 dateTime会钝。
5. 單表不要有太多字段，建議在 20 個(gè)以內(nèi)工三。
6. 避免使用 NULL 字段迁酸，很難查詢優(yōu)化且占用額外索引空間。
7. 用整型來(lái)存 IP俭正。

三胁出、索引

1. 索引并不是越多越好，要根據(jù)查詢有針對(duì)性的創(chuàng)建段审，考慮在 where 和 order by 命令上涉及的列建立索引全蝶。可根據(jù) EXPLAIN 來(lái)查看是否用了索引寺枉。
2. 使用多列索引時(shí)注意順序和查詢條件保持一致抑淫，同時(shí)刪除不必要的單列索引。
3. 值分布很稀少的字段不適合建索引姥闪，例如“性別”這種只有兩三個(gè)值的字段始苇。
4. 盡量不用 unique，由程序保證約束筐喳。
5. 字符字段只建前綴索引催式。
6. 字符字段最好不要做主鍵函喉。
7. 不用外鍵，由程序保證約束荣月。
8. 盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷管呵，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

四哺窄、查詢 SQL

1. 可通過(guò)開啟慢查詢?nèi)罩?/a>來(lái)找出較慢的 SQL捐下。
2. 不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)萌业。
3. sql 語(yǔ)句盡可能簡(jiǎn)單：一條 sql 只能在一個(gè) cpu 運(yùn)算坷襟；大語(yǔ)句拆小語(yǔ)句，減少鎖時(shí)間生年；一條大 sql 可能堵死整個(gè)庫(kù)婴程。
4. or 改寫成 in：or 的效率是 n 級(jí)別，in 的效率是 log(n) 級(jí)別抱婉，in 的個(gè)數(shù)建議控制在 200 以內(nèi)档叔。
5. 避免 %xxx 式查詢。
6. 少用 JOIN授段。
7. 使用同類型進(jìn)行比較蹲蒲，比如用 '123' 和 '123' 比，123 和 123 比侵贵。
8. 列表數(shù)據(jù)不要拿全表届搁，要使用 limit 來(lái)分頁(yè)，每頁(yè)數(shù)量也不要太大窍育。

五卡睦、引擎：MyISAM 和 InnoDB

1??MyISAM

MyISAM 是 MySQL 5.1 及之前版本的默認(rèn)引擎，它的特點(diǎn)是：

1. 不支持行鎖漱抓，讀取時(shí)對(duì)需要讀到的所有表加鎖表锻，寫入時(shí)則對(duì)表加排它鎖
2. 不支持事務(wù)
3. 不支持外鍵
4. 不支持崩潰后的安全恢復(fù)
5. 在表有讀取查詢的同時(shí)，支持往表中插入新紀(jì)錄
6. 支持 BLOB 和 TEXT 的前 500 個(gè)字符索引乞娄，支持全文索引
7. 支持延遲更新索引瞬逊，極大提升寫入性能
8. 對(duì)于不會(huì)進(jìn)行修改的表，支持壓縮表仪或，極大減少磁盤空間占用

2??InnoDB

InnoDB 在 MySQL 5.5 后成為默認(rèn)引擎确镊，它的特點(diǎn)是：

1. 支持行鎖，采用 MVCC 來(lái)支持高并發(fā)
2. 支持事務(wù)
3. 支持外鍵
4. 支持崩潰后的安全恢復(fù)
5. 不支持全文索引

總體來(lái)講范删，MyISAM 適合 select 密集型的表蕾域，而 InnoDB 適合 insert 和 update 密集型的表。

六到旦、系統(tǒng)調(diào)優(yōu)參數(shù)

可以使用下面幾個(gè)工具來(lái)做基準(zhǔn)測(cè)試：

• sysbench：一個(gè)模塊化旨巷，跨平臺(tái)以及多線程的性能測(cè)試工具
• iibench-mysql：基于 Java 的 MySQL/Percona/MariaDB 索引進(jìn)行插入性能測(cè)試工具
• tpcc-mysql：Percona 開發(fā)的 TPC-C 測(cè)試工具

具體的調(diào)優(yōu)參數(shù)內(nèi)容較多巨缘，具體可參考官方文檔，這里介紹一些比較重要的參數(shù)：

1. back_log：back_log值指出在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)多少個(gè)請(qǐng)求可以被存在堆棧中采呐。也就是說(shuō)若锁，如果MySql的連接數(shù)據(jù)達(dá)到max_connections時(shí)，新來(lái)的請(qǐng)求將會(huì)被存在堆棧中懈万，以等待某一連接釋放資源拴清，該堆棧的數(shù)量即back_log靶病，如果等待連接的數(shù)量超過(guò)back_log会通，將不被授予連接資源÷χ埽可以從默認(rèn)的50升至500

2. wait_timeout：數(shù)據(jù)庫(kù)連接閑置時(shí)間涕侈，閑置連接會(huì)占用內(nèi)存資源∶罕妫可以從默認(rèn)的8小時(shí)減到半小時(shí)

3. max_user_connection:最大連接數(shù)裳涛，默認(rèn)為0無(wú)上限，最好設(shè)一個(gè)合理上限thread_concurrency：并發(fā)線程數(shù)众辨，設(shè)為CPU核數(shù)的兩倍

4. skip_name_resolve：禁止對(duì)外部連接進(jìn)行DNS解析端三，消除DNS解析時(shí)間，但需要所有遠(yuǎn)程主機(jī)用IP訪問(wèn)

5. key_buffer_size：索引塊的緩存大小鹃彻，增加會(huì)提升索引處理速度郊闯，對(duì)MyISAM表性能影響最大。對(duì)于內(nèi)存4G左右蛛株，可設(shè)為256M或384M团赁，通過(guò)查詢show status like'key_read%'，保證key_reads / key_read_requests在0.1%以下最好

6. innodb_buffer_pool_size：緩存數(shù)據(jù)塊和索引塊谨履，對(duì)InnoDB表性能影響最大欢摄。通過(guò)查詢show status like 'Innodb_buffer_pool_read%'，保證 (Innodb_buffer_pool_read_requests– Innodb_buffer_pool_reads)/ Innodb_buffer_pool_read_requests越高越好

7. innodb_additional_mem_pool_size：InnoDB存儲(chǔ)引擎用來(lái)存放數(shù)據(jù)字典信息以及一些內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存空間大小笋粟，當(dāng)數(shù)據(jù)庫(kù)對(duì)象非常多的時(shí)候怀挠，適當(dāng)調(diào)整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內(nèi)存中提高訪問(wèn)效率，當(dāng)過(guò)小的時(shí)候害捕，MySQL會(huì)記錄Warning信息到數(shù)據(jù)庫(kù)的錯(cuò)誤日志中绿淋，這時(shí)就需要該調(diào)整這個(gè)參數(shù)大小

8. innodb_log_buffer_size：InnoDB存儲(chǔ)引擎的事務(wù)日志所使用的緩沖區(qū)，一般來(lái)說(shuō)不建議超過(guò)32MB

9. query_cache_size：緩存MySQL中的ResultSet吨艇，也就是一條SQL語(yǔ)句執(zhí)行的結(jié)果集躬它，所以僅僅只能針對(duì)select語(yǔ)句。當(dāng)某個(gè)表的數(shù)據(jù)有任何任何變化东涡，都會(huì)導(dǎo)致所有引用了該表的select語(yǔ)句在Query Cache中的緩存數(shù)據(jù)失效冯吓。所以倘待，當(dāng)我們的數(shù)據(jù)變化非常頻繁的情況下，使用Query Cache可能會(huì)得不償失组贺。根據(jù)命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進(jìn)行調(diào)整凸舵，一般不建議太大，256MB可能已經(jīng)差不多了失尖，大型的配置型靜態(tài)數(shù)據(jù)可適當(dāng)調(diào)大啊奄。

可以通過(guò)命令show status like 'Qcache_%'查看目前系統(tǒng)Query catch使用大小

10. read_buffer_size：MySql讀入緩沖區(qū)大小。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū)掀潮，MySql會(huì)為它分配一段內(nèi)存緩沖區(qū)菇夸。如果對(duì)表的順序掃描請(qǐng)求非常頻繁，可以通過(guò)增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能

11. sort_buffer_size：MySql執(zhí)行排序使用的緩沖大小仪吧。如果想要增加ORDER BY的速度庄新，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能薯鼠，可以嘗試增加sort_buffer_size變量的大小

12. read_rnd_buffer_size：MySql的隨機(jī)讀緩沖區(qū)大小择诈。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)出皇，將分配一個(gè)隨機(jī)讀緩存區(qū)羞芍。進(jìn)行排序查詢時(shí)，MySql會(huì)首先掃描一遍該緩沖郊艘，以避免磁盤搜索荷科，提高查詢速度，如果需要排序大量數(shù)據(jù)暇仲，可適當(dāng)調(diào)高該值步做。但MySql會(huì)為每個(gè)客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值奈附，以避免內(nèi)存開銷過(guò)大全度。

13. record_buffer：每個(gè)進(jìn)行一個(gè)順序掃描的線程為其掃描的每張表分配這個(gè)大小的一個(gè)緩沖區(qū)。如果你做很多順序掃描斥滤，可能想要增加該值

14. thread_cache_size：保存當(dāng)前沒(méi)有與連接關(guān)聯(lián)但是準(zhǔn)備為后面新的連接服務(wù)的線程将鸵，可以快速響應(yīng)連接的線程請(qǐng)求而無(wú)需創(chuàng)建新的

15. table_cache：類似于thread_cache_size，但用來(lái)緩存表文件佑颇，對(duì)InnoDB效果不大顶掉，主要用于MyISAM

七、升級(jí)硬件

Scale up挑胸，根據(jù) MySQL 是 CPU 密集型還是 I/O 密集型痒筒，通過(guò)提升 CPU 和內(nèi)存、使用 SSD，都能顯著提升 MySQL 性能簿透。

八移袍、讀寫分離

目前常用的優(yōu)化，從庫(kù)讀主庫(kù)寫老充，一般不要采用雙主或多主引入很多復(fù)雜性葡盗，盡量采用文中的其他方案來(lái)提高性能。同時(shí)目前很多拆分的解決方案同時(shí)也兼顧考慮了讀寫分離啡浊。

九觅够、緩存

緩存可以發(fā)生在這些層次：

1. MySQL 內(nèi)部：在系統(tǒng)調(diào)優(yōu)參數(shù)介紹了相關(guān)設(shè)置
2. 數(shù)據(jù)訪問(wèn)層：比如 MyBatis 針對(duì) SQL 語(yǔ)句做緩存，而 Hibernate 可以精確到單個(gè)記錄巷嚣，這里緩存的對(duì)象主要是持久化對(duì)象 Persistence Object
3. 應(yīng)用服務(wù)層：可以通過(guò)編程手段對(duì)緩存做到更精準(zhǔn)的控制和更多的實(shí)現(xiàn)策略喘先，這里緩存的對(duì)象是數(shù)據(jù)傳輸對(duì)象Data Transfer Object
4. Web層：針對(duì) web 頁(yè)面做緩存
5. 瀏覽器客戶端：用戶端的緩存

可以根據(jù)實(shí)際情況在一個(gè)層次或多個(gè)層次結(jié)合加入緩存。這里重點(diǎn)介紹下服務(wù)層的緩存實(shí)現(xiàn)涂籽，目前主要有兩種方式：

• 直寫式(Write Through)：在數(shù)據(jù)寫入數(shù)據(jù)庫(kù)后苹祟，同時(shí)更新緩存砸抛，維持?jǐn)?shù)據(jù)庫(kù)與緩存的一致性评雌。這也是當(dāng)前大多數(shù)應(yīng)用緩存框架如 Spring Cache 的工作方式。這種實(shí)現(xiàn)非常簡(jiǎn)單直焙，同步好景东，但效率一般。

• 回寫式(Write Back)：當(dāng)有數(shù)據(jù)要寫入數(shù)據(jù)庫(kù)時(shí)奔誓，只會(huì)更新緩存斤吐，然后異步批量的將緩存數(shù)據(jù)同步到數(shù)據(jù)庫(kù)上。這種實(shí)現(xiàn)比較復(fù)雜厨喂，需要較多的應(yīng)用邏輯和措，同時(shí)可能會(huì)產(chǎn)生數(shù)據(jù)庫(kù)與緩存的不同步，但效率非常高蜕煌。

十派阱、分片原則

能不分就不分，參考單表優(yōu)化斜纪。分片數(shù)量盡量少贫母，分片盡量均勻分布在多個(gè)數(shù)據(jù)結(jié)點(diǎn)上。因?yàn)橐粋€(gè)查詢 SQL 跨分片越多盒刚，總體性能越差腺劣，雖然要好于所有數(shù)據(jù)在一個(gè)分片的結(jié)果，只在必要的時(shí)候進(jìn)行擴(kuò)容因块，增加分片數(shù)量橘原。

分片規(guī)則需要慎重選擇做好提前規(guī)劃，分片規(guī)則的選擇，需要考慮數(shù)據(jù)的增長(zhǎng)模式趾断，數(shù)據(jù)的訪問(wèn)模式寨辩，分片關(guān)聯(lián)性問(wèn)題，以及分片擴(kuò)容問(wèn)題歼冰，最近的分片策略為范圍分片靡狞，枚舉分片，一致性 Hash 分片隔嫡，這幾種分片都有利于擴(kuò)容甸怕。

盡量不要在一個(gè)事務(wù)中的 SQL 跨越多個(gè)分片，分布式事務(wù)一直是個(gè)不好處理的問(wèn)題腮恩。

查詢條件盡量?jī)?yōu)化梢杭，盡量避免select *的方式，大量數(shù)據(jù)結(jié)果集下秸滴，會(huì)消耗大量帶寬和 CPU 資源武契，查詢盡量避免返回大量結(jié)果集，并且盡量為頻繁使用的查詢語(yǔ)句建立索引荡含。

通過(guò)數(shù)據(jù)冗余和表分區(qū)賴降低跨庫(kù) Join 的可能

這里特別強(qiáng)調(diào)一下分片規(guī)則的選擇問(wèn)題咒唆，如果某個(gè)表的數(shù)據(jù)有明顯的時(shí)間特征，比如訂單释液、交易記錄等全释，則他們通常比較合適用時(shí)間范圍分片，因?yàn)榫哂袝r(shí)效性的數(shù)據(jù)误债，我們往往關(guān)注其近期的數(shù)據(jù)浸船，查詢條件中往往帶有時(shí)間字段進(jìn)行過(guò)濾，比較好的方案是寝蹈，當(dāng)前活躍的數(shù)據(jù)李命，采用跨度比較短的時(shí)間段進(jìn)行分片，而歷史性的數(shù)據(jù)箫老，則采用比較長(zhǎng)的跨度存儲(chǔ)封字。

總體上來(lái)說(shuō)，分片的選擇是取決于最頻繁的查詢 SQL 的條件槽惫，因?yàn)椴粠魏?Where 語(yǔ)句的查詢 SQL周叮，會(huì)遍歷所有的分片，性能相對(duì)最差界斜，因此這種 SQL 越多仿耽，對(duì)系統(tǒng)的影響越大，所以要盡量避免這種 SQL 的產(chǎn)生各薇。

十一项贺、NoSQL

在 MySQL 上做 Sharding 是一種戴著鐐銬的跳舞君躺，事實(shí)上很多大表本身對(duì) MySQL 這種 RDBMS 的需求并不大，并不要求 ACID开缎，可以考慮將這些表遷移到 NoSQL棕叫，徹底解決水平擴(kuò)展問(wèn)題，例如：

1. 日志類奕删、監(jiān)控類俺泣、統(tǒng)計(jì)類數(shù)據(jù)
2. 非結(jié)構(gòu)化或弱結(jié)構(gòu)化數(shù)據(jù)
3. 對(duì)事務(wù)要求不強(qiáng)，且無(wú)太多關(guān)聯(lián)操作的數(shù)據(jù)