MySQL 資料庫最佳化有哪些方式？

MySQL 資料庫最佳化有哪些方式？

回複內容：

MySQL 資料庫最佳化有哪些方式？

寫在前面的話：總是在災難發生後，才想起容災的重要性

其實資料庫最佳化有很多種方式，不過只有在具體的情境中才會發揮它的最大價值，這是我對前人的最佳化做的一些總結，希望對你有協助，以下最佳化針對mysql

設計原則

1、不在資料庫做運算：cpu計算務必移至業務層
2、控制單表資料量：單表記錄控制在1000w
3、控制列數量：欄位數控制在20以內
4、平衡範式與冗餘：為提高效率犧牲範式設計，冗餘資料
5、拒絕3B：拒絕大sql，大事務，大批量
6、表字元集使用UTF8
7、使用INNODB儲存引擎

資料表設計

1、儘可能地使用最有效(最小)的資料類型

tinyint(1Byte)smallint(2Byte)mediumint(3Byte)int(4Byte)bigint(8Byte)bad case：int(1)/int(11)

2、不要將數字儲存為字串，字元轉化為數字，用int儲存ip而非char(15)
3、優先使用enum或set，sex enum (‘F’, ‘M’)
4，避免使用NULL欄位

NULL欄位很難查詢最佳化NULL欄位的索引需要額外空間NULL欄位的複合索引無效bad case：`name` char(32) default null`age` int not nullgood case：`age` int not null default 0

5，少用text/blob，varchar的效能會比text高很多；實在避免不了blob，請拆表

6、不在資料庫裡存圖片

7、對於MyISAM表，如果沒有任何變長列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。即使你已經用CREATE選項讓VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定長度的行

8、使用sample character set，例如latin1。盡量少使用utf-8，因為utf-8佔用的空間是latin1的3倍。可以在不需要使用utf-8的欄位上面使用latin1，例如mail，url等

9、精確度與空間的轉換。在儲存相同數值範圍的資料時，浮點數類型通常都會比DECIMAL類型使用更少的空間。FLOAT欄位使用4 位元組儲存 資料。DOUBLE類型需要8 個位元組並擁有更高的精確度和更大的數值範圍，DECIMAL類型的資料將會轉換成DOUBLE類型

10、庫名表名欄位名必須有固定的命名長度，12個字元以內；庫名、表名、欄位名禁⽌止超過32個字元。須見名之意；庫名、表名、欄位名禁⽌止使⽤用MySQL保留字；臨時庫、表名必須以tmp為首碼，並以⽇日期為尾碼； 備份庫、表必須以bak為首碼，並以日期為尾碼

11、InnoDB表行記錄物理長度不超過8KB，InnoDB的data page預設是16KB，基於B+Tree的特點，一個data page中需要至少儲存2條記錄。因此，當實際儲存長度超過8KB（尤其是TEXT/BLOB列）的大列（large column）時會引起“page-overflow儲存”，類似ORACLE中的“行遷移”，因此，如果必須使用大列（尤其是TEXT/BLOB類型）且讀寫頻繁的話，則最好把這些列拆分到子表中，不要和主表放在一起儲存，如果不太頻繁，可以考慮繼續保留在主表中，如果將 innodbpagesize 選項修改成 8KB，那麼行記錄物理長度建議不超過4KB

索引類

1、謹慎合理使用索引

改善查詢、減慢更新索引一定不是越多越好（能不加就不加，要加的一定得加）覆蓋記錄條數過多不適合建索引，例如“性別”

2、字元欄位必須建首碼索引

3、不在索引做列運算，bad case：select id where age +1 = 10;

4、innodb主鍵推薦使用自增列

主鍵建立聚簇索引主鍵不應該被修改字串不應該做主鍵如果不指定主鍵，innodb會使用唯一且非空值索引代替

5、不用外鍵，請由程式保證約束

6、避免在已有索引的首碼上建立索引。例如：如果存在index（a，b）則去掉index（a）

7、控制單個索引的長度。使用key（name（8））在資料的前面幾個字元建立索引

8、要選擇性的使用索引。在變化很少的列上使用索引並不是很好，例如性別列

9、Optimize table可以壓縮和排序index，注意不要頻繁運行

10、Analyze table可以更新資料

11、索引選擇性是不重複的索引值也叫基數（cardinality）表中資料行數的比值，索引選擇性=基數/資料行，count(distinct(username))/count(*) 就是索引選擇性，高索引選擇性的好處就是mysql尋找匹配的時候可以過濾更多的行，唯一索引的選擇性最佳，值為1

12、不要用重複或多餘索引，對於INNODB引擎的索引來說，每次修改資料都要把主鍵索引，輔助索引中相應索引值修改，這可能會出現大量數 據遷移，分頁，以及片段的出現

13、超過20個長度的字串列，最好建立首碼索引而非整列索引（例如：ALTER TABLE t1 ADD INDEX(user(20))），可以有效提高索引利用率，不過它的缺點是對這個列排序時用不到首碼索引。首碼索引的長度可以基於對該欄位的統計得出， 一般略大於平均長度一點就可以了

14、定期用 pt-duplicate-key-checker 工具檢查並重複資料刪除的索引。比如 index idx1(a, b) 索引已經涵蓋了 index idx2(a)，就可以刪除 idx2 索引了

sql語句設計類

1、sql語句儘可能簡單,一條sql只能在一個cpu運算，大語句拆小語句，減少鎖時間，一條大sql可以堵死整個庫(充分利用QUERY CACHE和充分利用多核CPU)

2、簡單的事務,事務時間儘可能短,bad case：上傳圖片事務

3、避免使用trig/func,觸發器、函數不用,用戶端程式取而代之

4、不用select *,消耗cpu，io，記憶體，頻寬,這種程式不具有擴充性

5、OR改寫為IN()

or的效率是n層級in的訊息時log(n)層級in的個數建議控制在200以內select id from t where phone=’159′ or phone=’136′ =>select id from t where phone in (’159′, ’136′);

6、OR改寫為UNION

mysql的索引合并很弱智select id from t where phone = '159' or name = 'john';=>select id from t where phone='159' union  select id from t where name='jonh';

7、避免負向%，如not in/like

8、慎用count(*)

9、limit高效分頁

limit越大，效率越低select id from t limit 10000, 10;=>select id from t where id > 10000 limit 10;

10、使用union all替代union，union有去重開銷

11、少用串連join

12、使用group by，分組、自動排序

13、請使用同類型比較

14、使用load data導資料，load data比insert快約20倍

15、對資料的更新要打散後批次更新，不要一次更新太多資料

16、使用效能分析工具

Sql explain  /  showprofile   /    mysqlsla

17、使用--log-slow-queries –long-query-time=2查看查詢比較慢的語句。然後使用explain分析查詢，做出最佳化

show profile;mysqlsla;mysqldumpslow;explain;show slow log;show processlist;show query_response_time(percona)

optimize 資料在插入，更新，刪除的時候難免一些資料移轉，分頁，之後就出現一些片段，久而久之片段積累起來影響效能， 這就需要DBA週期性最佳化資料庫減少片段，這就通過optimize命令。如對MyISAM表操作：optimize table 表名

18、禁止在資料庫中跑大查詢

19、使⽤先行編譯語句，只傳參數，比傳遞SQL語句更高效；一次解析，多次使用；降低SQL注入機率

20、禁止使⽤order by rand()

21、禁⽌單條SQL語句同時更新多個表

22、避免在資料庫中進⾏數學運算(MySQL不擅長數學運算和邏輯判斷)

23、SQL語句要求所有研發，SQL關鍵字全部是大寫，每個詞只允許有一個空格

24、能不用NOT IN就不用NOTIN，坑太多了。。會把空和NULL給查出來

留一個思考題吧，效能狀態關鍵計量該怎麼計算？

QPS，Queries Per Second：每秒查詢數，一台資料庫每秒能夠處理的查詢次數
TPS，Transactions Per Second：每秒處理事務數

注意
1、哪怕是基於索引的條件過濾，如果最佳化器意識到總共需要掃描的資料量超過30%時（ORACLE裡貌似是20%，MySQL目前是30%，沒準以後會調整），就會直接改變執行計畫為全表掃描，不再使用索引

2、多表JOIN時，要把過濾性最大（不一定是資料量最小哦，而是只加了WHERE條件後過濾性最大的那個）的表選為驅動表。此外，如果JOIN之後有排序，排序欄位一定要屬於驅動表，才能利用驅動表上的索引完成排序

3、絕大多數情況下，排序的代價通常要來的更高，因此如果看到執行計畫中有 Using filesort，優先建立排序索引吧

4、利用 pt-query-digest 定期分析slow query log，並結合 Box Anemometer 構建slow query log分析及最佳化系統

最佳化大致可以分為以下方面，按照執行難易程度和對當前項目影響排序：
1. MySQL參數最佳化：可以通過show variables;命令和show status;命令組合來綜合分析，可調整的項目根據使用的儲存引擎和項目瓶頸具體情況千差萬別，需要具體問題具體分析，如果想從這方面入手，建議把問題提得更具體一點；
2. SQL查詢最佳化和索引最佳化：你可以開啟慢日誌記錄，將需要消耗太多時間的查詢記錄下來，然後分析相應的SQL語句是否寫的不合理，不合理就改了；再到資料庫中查表結構，看是否索引設定不合理（一般where語句中的常用欄位和排序欄位應該加上合適的索引）；
3. 增加緩衝層：可考慮在MySQL與應用程式層中間加一個緩衝層，如APC、Memcached、Redis等等，將經常使用而更新較少的資料放到緩衝層中，可以很好的減輕資料庫壓力；
4. 最佳化表結構：首先這個代價稍大，可能要重新灌資料之類的，代碼修改可能也會比較多，看之前的封裝性好不好了。主要是根據業務需要，看是否之前的表結構有不合理的地方，比如你使用了很多但是又無法排除的join查詢；
5. 分庫、分表、主從分離：分庫是把資料庫從1個邏輯庫拆分到多個邏輯庫，或從1個伺服器拆分到多個伺服器，分表是將一個表拆分為多個表，甚至是多個物理伺服器的不同表；主從分離是將讀、寫完全分離到不同的資料庫伺服器；這個方案跟4一樣，也是代價比較大，但是可持久性很好，項目到達一定的數量級，必須走這一步；
6. 自己定製MySQL：開源的，可以根據自己特殊業務需要定製，太高端了點點，總之有這種可能，沒搞過...

1.不要 select * 按需查詢2.重構查詢，根據需要控制索引的使用。因為MySql在一個where子句中只能匹配一條索引（一般來說就是第一個條件），所以在查詢中要盡量的縮小條件的範圍,盡量使用 = 而不是> < 這樣的條件，因為當>\<匹配的資料量超過一定限制的時候查詢器會放棄使用索引。盡量不使用會導致索引失效的條件，比如in，用exists子查詢代替，或者in的條件少時可以用union all來代替3.好好學習天天向上吧，坑很多，這裡說不完

一本書都寫不完，別寄希望讓別人直接告訴你答案，找本書好好讀幾遍。

高效能MySQL 中文 第3版

這本書有很多地方都是講最佳化的，樓主可以去看看，電子版的也有，我就不給連結了：）

最基礎的也是最常用的 explain

太多了。我就給你說影響速度最大的幾個原因吧。1.資料庫選用2.資料庫的設計3.sql語句的拼字細說2和3.首先設計肯定很重要，這個不能教你。你自己多問那些行家。一般情況下，不屬於同一個範疇的東西不要放在一張表中，除非他們經常同時被查詢，而且多的一方的數量很少，比如3個以內。如果說是一個多的時候，多的一方個數比較多，那麼，肯定不能融合成一張表。其次，索引什麼的必須的吧，還有就是範圍的優先縮小，比如說where後面把結果集小的放前面，大的放後面，就好比說你從100個人，80個男的，20個女的，其中有60個人都比較高。叫你找10個比較高的女人。你說你是去女的中找10個高的，還是去所有人中先找高的，然後再從裡面選10個女的。如果是大資料，除開索引外，那麼分庫，分表肯定就是很必要的。多看書，多看部落格，這種問題沒人幫得了你，太多了，在實際項目中再學吧。

