MySQL資料庫優化-架構優化-SQL優化

優化方向

1. 表結構設計優化
2. SQL優化
3. 增加緩存層
4. 資料庫參數配置優化
5. 大事務優化
6. 分庫分表分區
7. 主從複制，讀寫分離
8. 更新伺服器硬體

表結構優化

1. 為什麼資料庫表的設計會影響性能？

• 字段的資料類型：不同的資料類型的存儲和檢索方式不同，對應的性能也不同，是以說要合理的選用字段的資料類型。比如人的年齡用無符号的unsigned tinyint即可，沒必要用integer
• 資料類型的長度：資料庫最終要寫到磁盤上，是以字段的長度也會影響着磁盤的I/O操作，如果字段的長度很大，那麼讀取資料也需要更多的I/O, 是以合理的字段長度也能提升資料庫的性能。比如使用者的手機号11位長度，沒必要用255個長度。
• 表的存儲引擎：常用的存儲引擎有MyISAM、InnoDB、Memory，不同的存儲引擎擁有不同的特性，是以要合理的利用每種存儲引擎的長處和優點來提供資料的性能。MyISAM不支援事務，表級鎖，但是查詢速度快，InnoDB支援事務，行鎖。

1. 優化方法

• 設計時需要滿足資料庫設計三大範式，根據場景選用合适的資料類型及長度；
• 根據場景選用合适的資料庫存儲引擎；
• 有外鍵限制會影響插入和删除性能，如果程式能夠保證資料的完整性，那在設計資料庫時就去掉外鍵；
• 表中允許适當備援，譬如，主題帖的回複數量和最後回複時間等

SQL優化

1. 使用查詢緩存，并優化查詢SQL，盡量使查詢緩存生效。

• 大多數的 MySQL 伺服器都開啟了查詢緩存。這是提高性最有效的方法之一，而且這是被 MySQL 的資料庫引擎處理的。

1. 當隻要一行資料時使用 LIMIT 1。

• 當查詢表時，如果你已經知道結果隻會有一條，請盡量使用LIMIT 1。這樣，資料庫引擎會在找到一條資料後停止搜尋，而不是繼續向下查找，可以提高SQL性能。

1. 添加索引。

• 索引并不一定就是給主鍵或是唯一的字段。如果在你的表中，有某個字段你總要會經常用來做搜尋，那麼，請為其建立索引吧。
• 聯表查詢的關聯字段（類型必須相同）請建立索引。這樣，MySQL 内部會啟動為你優化 Join 的 SQL 語句的機制。詳細原因請查閱Join原理。

1. 避免使用 select * 。

• 從資料庫裡讀出越多的資料，那麼查詢就會變得越慢。并且，如果你的資料庫伺服器和 WEB 伺服器是兩台獨立的伺服器的話，這還會增加網絡傳輸的負載。

• [SELECT *] 和[SELECT 全部字段]的 2 種寫法有何優缺點?

  ①前者要解析資料字典，後者不需要。

  ②結果輸出順序，前者與建表列順序相同，後者按指定字段順序。

  ③表字段名稱如果改變，前者不需要修改，後者需要改

  ④後者可以建立索引進行優化，前者無法優化

  ⑤後者的可讀性比前者要高

1. 盡量避免使用 in 或者 not in。

• 類似 select phone from t1 where phone not in (select phone from t2) 這樣的查詢語句效率都非常慢，可以使用以下兩種方式進行調整替換：

  ① 使用 EXISTS 或 NOT EXISTS 代替；

  ② 使用聯表查詢替換，例如上述SQL改為：

  select phone from t1 left join t2 on t1.phone = t2.phone where t2.phone is null 。

• 并不是所有情況均不能使用in或者not in。如果是确定且有限的集合時，可以使用，如 IN （0，1，2）。

1. 盡量避免使用or 。

• in，not in，or等關鍵字都會使索引失效，效率很低，使用時請慎重。

1. 盡可能的使用 NOT NULL。

• 除非你有一個很特别的原因去使用 NULL 值，你應該總是讓你的字段保持NOT NULL。
• 原因：①NULL需要額外的空間進行存儲，而且在進行比較時，會使程式變得複雜；②在 Oracle 裡，NULL 和 Empty 的字元串是一樣的；

1. EXPLAIN 你的 SELECT 查詢，更好地進行SQL優化。

• 使用 EXPLAIN 關鍵字可以讓你知道 MySQL 是如何處理你的 SQL 語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。
• EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的資料表是如何被搜尋和排序的……等等，等等。

1. 千萬不要 ORDER BY RAND() 。

• ORDER BY RAND() 目的：将傳回的資料行順序打亂。這樣使用隻讓你的資料庫的性能呈指數級的下降。這裡的問題是：MySQL 會不得不去執行 RAND()函數(很耗 CPU 時間)，而且這是為了每一行記錄去記行，然後再對其排序。就算是你用了 Limit 1 也無濟于事(因為要排序)。
• 如果真實場景的确需要随機打亂查詢結果，也不要屬于上述文法，有其他N種方法可以進行替代。

1. 永遠為每張表設定一個 ID。

• 我們應該為資料庫裡的每張表都設定一個 ID 做為其主鍵，而且最好的是一個 INT 型的(推薦使用 UNSIGNED)，并設定上自動增加的 AUTO_INCREMENT 标志。
• 使用 VARCHAR 類型來當主鍵會使用得性能下降。
• 在 MySQL 資料引擎下，還有一些操作需要使用主鍵，在這些情況下，主鍵的性能和設定變得非常重要，比如，叢集，分區……
• 在這裡，隻有一個情況是例外，那就是“關聯表”的多個“外鍵”共同組成主鍵。例如，“成績表”關聯了學生表和課程表，在成績表中，學生 ID 和課程 ID 共同組成主鍵。

1. 從 PROCEDURE ANALYSE() 取得建議。

• PROCEDURE ANALYSE() 會讓 MySQL 幫你去分析你的字段和表中的實際資料，最終給你一些有用的建議。隻有表中實際資料的資料量非常大時，這個建議才能盡可能地準确。
• 一定要注意，這些隻是建議，你才是最終做決定的人。

1. 使用 ENUM 替換 VARCHAR

• ENUM 類型是非常快和緊湊的，其實際上儲存的是 TINYINT，隻是外表上顯示為字元串。這樣一來，用這個字段來做一些選項清單變得相當的完美。如果你有一個字段，比如“性别”，“國家”，“民族”，“狀态”或 “部門”，你知道這些字段的取值是有限而且固定的，那麼，請使用 ENUM替代VARCHAR。

1. 把 IP 位址存成 UNSIGNED INT

• INET_ATON() 可以把一個字元串 IP 轉成一個整形；
• INET_NTOA() 可以把一個整形轉成一個字元串 IP；
• 兩者配合使用，可以使IP字段類型由Varchar(15)轉為UNSIGNED INT(10)，可以節省空間，提高查詢效率（定長），而且友善ip比較，例 IP between ip1 and ip2。

1. 固定長度的表會更快

• 如果表中的所有字段都是“固定長度”的，整個表會被認為是 “static”或 “fixed-length”。 例如，表中沒有如下類型的字段： VARCHAR，TEXT，BLOB。隻要你包括了其中一個這些字段，那麼這個表就不是“固定長度靜态表”了，這樣，MySQL 引擎會用另一種方法來處理。
• 固定長度的表會提高性能，因為這些固定的長度是很容易計算下一個資料的偏移量的，是以讀取的自然也會很快。而如果字段不是定長的，那麼，每一次要找下一條的話，需要程式找到主鍵。
• 并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要配置設定那麼多的空間。

1. 拆分大的 DELETE 或 INSERT 語句

• 如果你需要在一個線上的網站上去執行一個大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，别的操作都進不來了。這種情況下很容易導緻一個高通路量的站點，web服務Crash，甚至整台伺服器崩潰。

1. 列越小，越快

• 根據業務場景，每個字段選用适當的類型和适當的大小，盡量使資料變得緊湊，這樣可以減少對硬碟的通路。
• 但是，也要留有足夠的擴充空間，否則後期維護會非常麻煩。

1. UNION ALL 要比 UNION 快很多。

• 如果可以确認合并的兩個結果集中不包含重複資料且不需要排序時的話，那麼就使用 UNION

  ALL。

• UNION 和 UNION ALL 關鍵字都是将兩個結果集合并為一個，但這兩者從使用和效率上來說都有所不同。 ① 對重複結果的處理：UNION 在進行表連結後會篩選掉重複的記錄，Union All 不會去除重複記錄。 ② 對排序的處理：Union 将會按照字段的順序進行排序；UNION ALL 隻是簡單的将兩個結果合并後就傳回。

增加緩存層

1. 增加緩存層，可以減少資料庫連接配接，降低資料庫壓力并提高效率。
2. 方式：可以使用redis、memcache等增加緩存層。

資料庫參數配置優化

mysql是一個高度定制化的資料庫系統，提供了很多配置參數(如最大連接配接數、資料庫占用的記憶體等)，這些參數都有預設值，一般預設值都不是最佳的配置，需要根據應用程式的特性和硬體情況對mysql的配置進行調整。

大事務優化

大事務：運作時間比較長，操作的資料比較多的事務。

風險：鎖定太多的資料，造成大量的阻塞和鎖逾時，復原時所需時間比較長，執行時間長容易造成主從延遲。

操作：避免一次處理太多的資料，移除不必要在事務中的select操作。

分庫分表分區

1. 分庫，可以按照業務分庫，分流資料庫并發壓力，使資料庫表更加有條理性。
2. 分表，當一個表的資料量很大的時候，查詢就變的很慢，是以減少表裡的記錄的數量是優化的一種方式。這種方式就是将一張表的資料拆分成多張表，這樣每張表的數量就減少了，查詢速度就相對來說就快了一些。

• 分表的方法有很多種：

  ① 如果這個業務是有流程的，那麼我們通常會設計一個曆史表或者歸檔表，用來存放曆史資料，這樣能保證明時資料效率比較高。

  ②針對某一張大表，可以根據查詢條件分成多張表，比如時間，我們可以将半個月或者10天的資料放到一張表裡(看具體資料量，個人認為3000W是個上限，最好控制到百萬級别)，每過10天，我們就自動建立一張資料庫表，然後将資料插入，如此，按照時間查詢，就要先定位去那種表中去取數，這樣，效率能夠得到大幅度提升，當然，這麼解決也有問題，比如跨表，需要union多張表，而且跨表沒法支援索引。

  ③上面的方法是我們直接通過程式和資料庫實作的最原始的分表解決方案，現在市面上有一些成熟的軟體如mycat，也是支援分表的。

  一般來講，資料庫中的大表畢竟隻是一少部分，僅需要對這少部分大表進行分表就可以了，沒必要小表也進行分表，增加維護開發難度

1. 分區，分區的實作道理和分表一樣，也是将相應規則的資料放在一起，唯一不同的是分區你隻需要設定好分區規則，插入的資料會被自動插入到指定的區裡，當然查詢的時候也能很快查詢到需要區，相當于是對外不透明的分表，出現跨表資料庫自動幫我們合并做了處理，使用起來比分表更加友善，但是分區也有自己的問題，每一個資料庫表的并發通路是有上限的，也就是說，分表能夠抗高并發，而分區不能，如何選擇，要考慮實際情況。

主從複制，讀寫分離

• 一台MySQL伺服器同一時間點支援的并發數是有限的，當大量并發(如秒殺活動等，很多使用者都同一時刻通路資料庫)時，一台資料庫處理不過來，是以增加MySQL伺服器的數量也是一種增強資料庫性能的方式。
• 通過使用MySQL主從複制，增删改操作走Master主伺服器，查詢走Slaver從伺服器，這樣就減少了隻有一台MySQL伺服器的壓力。

更新伺服器硬體

• 當所有優化手段都用了，性能仍需要優化，那麼隻有更新MySQL伺服器端硬體了，更快的磁盤IO裝置，更強的CPU，更大的記憶體，更大的網卡流量(帶寬)等。

參考資料：

https://baijiahao.baidu.com/s?id=1660232228855199630&wfr=spider&for=pc

https://blog.csdn.net/zhoupan301415/article/details/78257783

《MySQL性能優化的21個最佳實踐》