【金三銀四】Mysgl優化了解？什么情況下會導致SQL索引失效？如何寫出高效SQL與優化慢SQL

Mysgl優化

MySQL 優化是指對 MySQL 數據庫的配置、表設計、查詢語句等進行針對性的優化，以提高數據庫的性能和效率。這包括但不限于合理設計數據庫表結構、編寫高效的 SQL 查詢語句、創建合適的索引以及調整數據庫服務器的參數等。

當MySQL單表記錄數過大時，性能下降是一個常見問題。這是因為隨著數據量的增加，數據庫在執行增刪改查操作時需要處理更多的數據。

當涉及到 MySQL 數據庫優化時，可以從以下幾個方面進行詳細討論：

一、單表優化：

字段優化：
- 選擇合適的數據類型以減少存儲空間和提高查詢效率。
- 使用 TINYINT、SMALLINT、MEDIUM_INT 代替 INT，非負數加 UNSIGNED。
- VARCHAR 長度根據實際需要分配。
- 首選 TIMESTAMP 而非 DATETIME。
- 單表字段不超過 20 個。
- 盡量使用NOT NULL約束，避免NULL帶來的額外開銷。
- 枚舉（ENUM）或整數（INT）比字符串（VARCHAR）更高效，特別是有大量重復值時。
- 使用整數存儲IP地址以提高查詢效率。
索引優化：
- 根據查詢模式創建索引，重點關注WHERE和ORDER BY中的列，可根據EXPLAIN命令來查看是否用了索引還是全表掃描。
- 避免在 WHERE 子句中對字段進行 NULL 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。
- 稀少值的字段不適合建索引，如性別。
- 避免使用外鍵和 UNIQUE 約束。
- 對字符字段可以考慮創建前綴索引以減少索引大小。
- 避免對索引列進行運算，以免引起索引失效。
查詢SQL優化：
- 開啟慢查詢日志以定位性能瓶頸。
- 避免列運算，盡量簡化 SQL。
- 不使用 SELECT *。
- 將 OR 改寫成 IN。
- 避免函數和觸發器，推遲至應用程序層實現。
- 少用 JOIN，使用同類型比較。
- 使用索引避免全表掃描，提高查詢效率。
- 使用LIMIT進行分頁查詢，避免一次性獲取大量數據。
- 盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
存儲引擎選擇：
- MyISAM適用于讀多寫少的場景，提供全文索引和壓縮表功能。
- InnoDB適用于寫多讀少的場景，支持事務、行鎖和外鍵，提供更高的并發處理能力和數據保護。
  當然可以，以下是一個簡單的表格，列出了MyISAM和InnoDB存儲引擎的特點：

特點	MyISAM	InnoDB
讀寫特性	讀多寫少	寫多讀少
事務支持	不支持	支持
鎖定方式	表級鎖定	行級鎖定
外鍵支持	不支持	支持
全文索引	支持	不支持
壓縮表	支持	不支持
并發處理	較差	較好
數據保護	不提供數據保護機制	提供數據保護機制

總體來講，MyISAM適合SELECT密集型的表，而InnoDB適合INSERT和UPDATE密集型的表。
希望這個表格能夠清晰地展示出MyISAM和InnoDB存儲引擎的特點。

系統調優參數：

sysbench：一個模塊化，跨平臺以及多線程的性能測試工具。

https://github.com/akopytov/sysbench

iibench-mysql：基于Java的MySQL / Percona / MariaDB 索引進行插入性能測試工具。

https://github.com/tmcallaghan/iibench-mysql

tpcc-mysql：Percona開發的TPC-C測試工具。

https://github.com/Percona-Lab/tpcc-mysql
在優化數據庫時，建議定期監控性能并評估優化效果。在進行大規模優化前，應在測試環境中驗證，確保安全有效。

二、讀寫分離：

通過將讀操作和寫操作分開到不同的數據庫服務器上，提高系統的并發處理能力和讀取性能。寫操作集中在主庫上，讀操作則可以分攤到多個從庫上，有效減輕主庫的壓力。

讀寫分離是一種數據庫優化策略，通過將數據庫的讀操作和寫操作分別分配到不同的數據庫服務器上，以提高系統的并發處理能力和讀取性能。在這種策略下，寫操作集中在主數據庫服務器上，而讀操作則可以分攤到多個從數據庫服務器上。這樣做的好處是可以有效減輕主庫的壓力，提高系統的吞吐量。

讀寫分離的關鍵在于使用數據庫代理程序，如MySQL Proxy、MyCAT，這些代理程序充當了數據庫服務器和應用程序之間的中介。讀操作可以通過代理程序進行緩存，而寫操作則被轉發到主數據庫服務器進行實際的數據存儲。這種分離使得讀操作可以輕松地從緩存中獲得高性能，而寫操作則可以確保數據的一致性和完整性。

除了提高性能外，讀寫分離還可以幫助組織實現高可用性。通過將讀和寫操作分離到不同的服務器上，組織可以在不影響讀操作的情況下進行數據庫的維護和升級。這使得組織能夠更輕松地保持系統的正常運行，并減少停機時間。

需要注意的是，讀寫分離并不總是適用于所有情況。在某些情況下，這種分離可能會導致數據不一致。例如，如果兩個用戶同時進行讀操作，可能會讀到舊的數據。因此，在實現讀寫分離時，需要采取一定的措施來保證數據的一致性。

三、緩存：

利用緩存技術（如 Memcached、Redis 等）緩存熱點數據，減少對數據庫的頻繁訪問，提高訪問速度和并發能力。
緩存技術是提高系統性能的關鍵手段之一，它通過存儲頻繁訪問的數據或結果，從而減少對數據庫或其他數據源的直接訪問，加快數據檢索速度，并提高系統的并發處理能力。

常用的緩存技術包括Memcached和Redis。Memcached是一個高性能、分布式內存對象緩存系統，主要用于緩存數據庫中的對象，以減少數據庫的訪問次數。而Redis是一個開源的、支持網絡、可基于內存也可以持久化的日志型、Key-Value數據庫，并提供多種類型的數據結構來適應不同場景下的緩存需求。

在實現緩存時，需要關注以下幾個關鍵點：

緩存命中率：緩存命中率是衡量緩存效果的重要指標，高命中率意味著更多的請求可以直接從緩存中獲取，減少了對后端數據庫的訪問。
緩存更新策略：包括過期時間、懶加載和主動更新等，選擇合適的更新策略可以確保緩存數據的及時性和有效性。
緩存穿透：為了避免緩存未命中時對數據庫的巨大壓力，需要采取措施如緩存空值或布隆過濾器等來防止緩存穿透。
緩存雪崩：在緩存失效時，大量請求直接打在數據庫上，可能導致系統癱瘓。可以通過設置不同的過期時間、限流、增加緩存實例等方式來避免緩存雪崩。
緩存預熱：在系統啟動或緩存數據初始化時，提前將熱點數據加載到緩存中，以避免系統上線初期大量請求直接訪問數據庫。
緩存粒度：根據業務需求，選擇合適的緩存粒度，如數據行、數據頁或數據塊，以平衡緩存效率和內存利用率。

通過合理使用緩存技術，可以有效降低系統響應時間，提高系統的并發處理能力，從而改善用戶體驗。

四、表分區：

將大表按照一定的規則分割成小的分區，可以加速查詢和提高性能。常見的分區方式有范圍分區、哈希分區、列表分區等。

五、垂直拆分：

將原本一個大表按列拆分成多個表，每個表包含部分列，可以提高查詢效率，降低數據冗余。

六、水平拆分：

將原本一個大表按行拆分成多個表，每個表包含部分行數據，可以分散數據存儲，提高并發能力和負載均衡。

七、水平拆分跟垂直拆分的區別

水平拆分和垂直拆分是在數據庫設計和優化中常用的兩種數據分割策略。它們的區別在于數據如何被拆分和存儲。

水平拆分（Horizontal Sharding）：
- 水平拆分是指將數據按照某種規則（比如按照用戶ID、時間范圍等）分割成多個部分，然后分別存儲到不同的數據庫節點或表中。
- 這種方式適合于需要橫向擴展的場景，可以更好地應對數據量大、并發讀寫壓力大的情況。
- 例子：將全國用戶的數據按照地理位置分割存儲到不同的數據庫節點上。
垂直拆分（Vertical Partitioning）：
- 垂直拆分是指將數據表按照字段的關系和訪問模式進行拆分，將不同的字段分別存儲到不同的表或數據庫中。
- 這種方式適合于減少單個表的寬度，提高數據讀取效率，同時可以根據不同的業務需求將數據存儲到不同的物理存儲介質上。
- 例子：將包含大量稀疏字段的表進行拆分，將常用的字段存儲到主表，將稀疏字段存儲到關聯表中。

總的來說，水平拆分注重的是數據的分布和擴展能力，而垂直拆分注重的是數據的結構和訪問模式。在實際應用中，通常會綜合考慮兩種拆分方式，根據具體的業務需求和系統特點來選擇最合適的拆分策略。

綜合來看，MySQL 數據庫優化是一個綜合性的工作，需要結合具體業務場景和需求，從表結構設計、索引優化、查詢語句編寫、緩存應用、分區與拆分等多個方面綜合考慮，以提高數據庫性能和系統穩定性。

什么情況下會導致SQL索引失效?

SQL索引失效的情況包括：

使用OR條件：當查詢條件中包含OR時，即使部分條件涉及索引列，也可能導致索引失效。

SELECT * FROM users WHERE age = 30 OR name = 'Alice';

類型轉換：對索引列進行類型轉換，如使用函數，可能導致索引失效。

SELECT * FROM users WHERE UPPER(name) = 'ALICE';

范圍條件右側列：在復合索引中，范圍條件（如>、<、BETWEEN等）右側的列無法使用索引。

SELECT * FROM users WHERE age > 25 AND name = 'Alice';

不等于條件：使用!=或<>會導致索引失效。

SELECT * FROM users WHERE age != 25;

IS NULL/IS NOT NULL：這些條件會導致索引失效。

SELECT * FROM users WHERE name IS NULL;

LIKE通配符開頭：如果LIKE查詢以通配符%開頭，會導致索引失效。

SELECT * FROM users WHERE name LIKE '%Alice';

字符串未加引號：字符串類型字段未加單引號可能導致索引失效。

SELECT * FROM users WHERE name = Alice;

索引列上計算：在索引列上進行計算或其他操作，會導致索引失效。

SELECT * FROM users WHERE age + 10 = 30;

最佳左前綴法則：在復合索引中，必須從最左邊的列開始查詢，并且不能跳過中間列。

SELECT * FROM users WHERE name = 'Alice' AND age = 25;

存儲引擎限制：某些存儲引擎對索引的使用有限制。
優化器選擇：優化器可能根據成本考慮不使用索引。
數據分布不均：索引列數據分布不均，可能導致索引失效。
表數據量小：對于小表，全表掃描可能比索引更快。
查詢條件覆蓋索引：盡量使用覆蓋索引，避免回表查詢。

如何使用EXPLAIN查看SQL有沒有走索引

要使用EXPLAIN來查看SQL語句是否使用了索引，可以按照以下步驟進行：

在待查詢的SQL語句前加上EXPLAIN關鍵字，例如：

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

執行該帶有EXPLAIN關鍵字的SQL語句，數據庫系統將返回一個結果集，其中包含了查詢的執行計劃。
查看執行計劃中的type列，該列表示查詢使用了哪種類型的訪問方法：
- 如果type列顯示為index，表示查詢使用了索引。
- 如果type列顯示為range，表示查詢使用了范圍索引。
- 如果type列顯示為ALL，表示查詢進行了全表掃描，沒有使用索引。
可以查看key列，該列表示查詢實際使用的索引名稱。
如果查詢涉及多個表，還可以查看possible_keys列，表示查詢可能使用的索引列表。
最后，查看rows列，表示查詢預計需要檢查的行數，行數越少表示查詢效率可能越高。

通過分析EXPLAIN的結果，可以判斷SQL語句是否使用了索引，以及索引的使用是否合理。如果查詢未使用索引或使用了不合適的索引，可以考慮修改查詢語句或創建更合適的索引以提高查詢性能。

下面是一個表格格式的展示，包含了在使用EXPLAIN命令時通常可以查看的參數：

參數	說明
id	每個查詢的序號，如果有子查詢則會有多行記錄，父查詢的id值會與子查詢相關聯
select_type	查詢的類型，如SIMPLE（簡單查詢）、PRIMARY（主查詢）、SUBQUERY（子查詢）等
table	正在訪問的表
partitions	匹配的分區信息
type	連接類型，包括system、const、eq_ref、ref、range、index等
possible_keys	可能應用在表中的索引，但不一定被查詢使用
key	實際使用的索引
key_len	索引字段的長度
ref	使用的索引的哪一列，通常是常數或字段
rows	數據庫系統認為必須檢查的行數
filtered	表的過濾行百分比
Extra	提供關于執行查詢的額外信息，如Using index、Using temporary、Using filesort等