<em id="pn7p8"><acronym id="pn7p8"><u id="pn7p8"></u></acronym></em>

    <th id="pn7p8"></th>

    <button id="pn7p8"></button>

      <dd id="pn7p8"></dd>
      <progress id="pn7p8"><track id="pn7p8"></track></progress>

      Linux培訓
      達內IT學院

      400-111-8989

      MySQL開發規范之我見

      • 發布:佚名
      • 來源:網絡
      • 時間:2015-08-06 17:50

      大多數MySQL規范在網上也都能找得到相關的分享,在這里要分享的是老葉個人認為比較重要的,或者容易被忽視的,以及容易被混淆的一些地方。

      1、默認使用InnoDB引擎

      【老葉觀點】已多次呼吁過了,InnoDB適用于幾乎99%的MySQL應用場景,而且在MySQL 5.7的系統表都改成InnoDB了,還有什么理由再死守MyISAM呢。

      此外,頻繁讀寫的InnoDB表,一定要使用具有自增/順序特征的整型作為顯式主鍵。

      【參考】:[MySQL FAQ]系列 — 為什么InnoDB表要建議用自增列做主鍵。

      2、字符集選擇utf-8

      【老葉觀點】若為了節省磁盤空間,則建議選擇latin1。建議選擇utf-8通常是為了所謂的“通用性”,但事實上用戶提交的utf-8數據也一樣可以以latin1字符集存儲。

      用latin1存儲utf-8數據可能遇到的麻煩是,如果有基于中文的檢索時,可能無法100%準確(老葉親自簡單測試常規的中文完檢索全不是問題,也就是一般的中文對比是沒問題的)。

      用latin1字符集存儲utf-8數據的做法是:在web端(用戶端)的字符集是utf-8,后端程序也采用utf-8來處理,但 character_set_client、character_set_connection、character_set_results、character_set_database、character_set_server 這幾個都是 latin1,且數據表、字段的字符集也是latin1。或者說數據表采用latin1,每次連接后執行 SET NAMES LATIN1 即可。

      【參考】:小談MySQL字符集。

      3、InnoDB表行記錄物理長度不超過8KB

      【老葉觀點】InnoDB的data page默認是16KB,基于B+Tree的特點,一個data page中需要至少存儲2條記錄。因此,當實際存儲長度超過8KB(尤其是TEXT/BLOB列)的大列(large column)時會引起“page-overflow存儲”,類似ORACLE中的“行遷移”。

      因此,如果必須使用大列(尤其是TEXT/BLOB類型)且讀寫頻繁的話,則最好把這些列拆分到子表中,不要和主表放在一起存儲。如果不太頻繁,可以考慮繼續保留在主表中。

      當然了,如果將 innodb_page_size 選項修改成 8KB,那么行記錄物理長度建議不超過4KB。

      【參考】:[MySQL優化案例]系列 — 優化InnoDB表BLOB列的存儲效率。

      4、是否使用分區表

      【老葉觀點】在一些使用分區表后明顯可以提升性能或者運維便利性的場景下,還是建議使用分區表。

      比如老葉就在zabbix的數據庫采用TokuDB引擎的前提下,又根據時間維度使用了分區表。這樣的好處是保證zabbix日常應用不受到影響前提下,方便管理員例行刪除過去數據,只需要刪除相應分區即可,不需再執行一個非常慢的DELETE而影響整體性能。

      【參考】:遷移Zabbix數據庫到TokuDB。

      5、是否使用存儲過程、觸發器

      【老葉觀點】在一些合適的場景下,用存儲過程、觸發器也完全沒問題。

      我們以前就是利用存儲完成游戲業務邏輯處理,性能上不是問題,而且一旦需求有變更,只需修改存儲過程,變更代價很低。我們還利用觸發器維護一個頻繁更新的表,對這個表的所有變更都將部分字段同步更新到另一個表中(類似物化視圖的變相實現),也不存在性能問題。

      不要把MySQL的存儲過程和觸發器視為洪水猛獸,用好的話,沒有問題的,真遇到問題了再優化也不遲。另外,MySQL因為沒有物化視圖,因此視圖能不用就盡量少用吧。

      6、選擇合適的類型

      【老葉觀點】除了常見的建議外,還有其他幾個要點:

      6.1、用INT UNSIGNED存儲IPV4地址,用INET_ATON()、INET_NTOA()進行轉換,基本上沒必要使用CHAR(15)來存儲。

      6.2、枚舉類型可以使用ENUM,ENUM的內部存儲機制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR),性能一點都不差,記住千萬別用CHAR/VARCHAR來存儲枚舉數據。

      6.3、還個早前一直在傳播的“常識性誤導”,建議用TIMESTAMP取代DATETIME。其實從5.6開始,建議優先選擇DATETIME存儲日期時間,因為它的可用范圍比TIMESTAMP更大,物理存儲上僅比TIMESTAMP多1個字節,整體性能上的損失并不大。

      6.4、所有字段定義中,默認都加上NOT NULL約束,除非必須為NULL(但我也想不出來什么場景下必須要在數據庫中存儲NULL值,可以用0來表示)。在對該字段進行COUNT()統計時,統計結果更準確(值為NULL的不會被COUNT統計進去),或者執行WHERE column IS NULL 檢索時,也可以快速返回結果。

      6.5、盡可能不要直接 SELECT * 讀取全部字段,尤其是表中存在 TEXT/BLOB 大列的時候。可能本來不需要讀取這些列,但因為偷懶寫成 SELECT * 導致內存buffer pool被這些“垃圾”數據把真正需要緩沖起來的熱點數據給洗出去了。

      8、關于索引

      【老葉觀點】除了常見的建議外,還有幾個要點:

      8.1、超過20個長度的字符串列,最好創建前綴索引而非整列索引(例如:ALTER TABLE t1 ADD INDEX(user(20))),可以有效提高索引利用率,不過它的缺點是對這個列排序時用不到前綴索引。前綴索引的長度可以基于對該字段的統計得出,一般略大于平均長度一點就可以了。

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

      8.3、有多字段聯合索引時,WHERE中過濾條件的字段順序無需和索引一致,但如果有排序、分組則就必須一致了。

      比如有聯合索引 idx1(a, b, c),那么下面的SQL都可以完整用到索引:

      SELECT ... WHERE b = ? AND c = ? AND a = ?; --注意到,WHERE中字段順序并沒有和索引字段順序一致

      SELECT ... WHERE b = ? AND a = ? AND c = ?;

      SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;

      SELECT ... WHERE a = ? AND b = ? ORDER BY c;

      SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;

      SELECT ... WHERE a = ? ORDER BY b, c;

      SELECT ... ORDER BY a, b, c; -- 可利用聯合索引完成排序

      而下面幾個SQL則只能用到部分索引:

      SELECT ... WHERE b = ? AND a = ?; -- 只能用到 (a, b) 部分

      SELECT ... WHERE a IN (?, ?) AND b = ?; -- 只能用到 (a, b) 部分

      SELECT ... WHERE a = ? AND c = ?; -- 只能用到 (a) 部分

      SELECT ... WHERE a = ? AND b IN (?, ?); -- 只能用到 (a, b) 部分

      SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?; -- 只能用到 (a) 部分,注意BETWEEN和IN的區別

      SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- 只能用到 (a, b) 部分

      下面的幾個SQL完全用不到該索引:

      SELECT ... WHERE b = ?;

      SELECT ... WHERE b = ? AND c = ?;

      SELECT ... WHERE b = ? AND c = ?;

      SELECT ... ORDER BY b;

      SELECT ... ORDER BY b, a;

      從上面的幾個例子就能看的出來,以往強調的WHERE條件字段順序要和索引順序一致才能使用索引的 “常識性誤導” 無需嚴格遵守。

      此外,有些時候查詢優化器指定的索引或執行計劃可能并不是最優的,可以手工指定最優索引,或者修改session級的 optimizer_switch 選項,關閉某些導致效果反而更差的特性(比如index merge通常是好事,但也遇到過用上index merge后反而更差的,這時候要么強制指定其中一個索引,要么可以臨時關閉 index merge 特性)。

      9、其他

      9.1、哪怕是基于索引的條件過濾,如果優化器意識到總共需要掃描的數據量超過30%時(ORACLE里貌似是20%,MySQL目前是30%,沒準以后會調整),就會直接改變執行計劃為全表掃描,不再使用索引。

      9.2、多表JOIN時,要把過濾性最大(不一定是數據量最小哦,而是只加了WHERE條件后過濾性最大的那個)的表選為驅動表。此外,如果JOIN之后有排序,排序字段一定要屬于驅動表,才能利用驅動表上的索引完成排序。

      9.3、絕大多數情況下,排序的大家通常要來的更高,因此如果看到執行計劃中有 Using filesort,優先創建排序索引吧。

      9.4、利用 pt-query-digest 定期分析slow query log,并結合 Box Anemometer 構建slow query log分析及優化系統。

      【參考】:[MySQL FAQ]系列 — EXPLAIN結果中哪些信息要引起關注。

      備注:若無特別說明,以上規范建議適用于MySQL 5.6及之前的版本。5.7及之后的版本可能會有些變化,個別規范建議需要相應調整。

      預約申請免費試聽課

      填寫下面表單即可預約申請免費試聽!怕錢不夠?可就業掙錢后再付學費! 怕學不會?助教全程陪讀,隨時解惑!擔心就業?一地學習,可全國推薦就業!

      上一篇:Linux資源管理之cgroups簡介
      下一篇:使用Git和遠程代碼庫教程
      • 掃碼領取資料

        回復關鍵字:視頻資料

        免費領取 達內課程視頻學習資料

      • 視頻學習QQ群

        添加QQ群:1143617948

        免費領取達內課程視頻學習資料

      Copyright ? 2021 Tedu.cn All Rights Reserved 京ICP備08000853號-56 京公網安備 11010802029508號 達內時代科技集團有限公司 版權所有

      選擇城市和中心
      黑龍江省

      吉林省

      河北省

      湖南省

      貴州省

      云南省

      廣西省

      海南省

      高清特黄a大片,日本真人真做爰,特级做人爱C级,免费a级毛片 百度 好搜 搜狗
      <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <文本链> <文本链> <文本链> <文本链> <文本链> <文本链>