避免mysql全表掃描的核心方法包括：1.使用explain分析查詢計劃，關(guān)注type列是否為all以識別全表掃描；2.創(chuàng)建合適的索引，如針對高頻查詢字段建立單列或復(fù)合索引；3.優(yōu)化where子句，避免使用函數(shù)或表達式導(dǎo)致索引失效；4.限制返回數(shù)據(jù)量，使用limit減少不必要的數(shù)據(jù)檢索；5.定期維護索引，通過optimize table整理碎片；6.考慮分區(qū)表以縮小掃描范圍；7.利用索引覆蓋，創(chuàng)建包含所有查詢字段的復(fù)合索引并驗證extra列是否顯示using index；8.重寫查詢語句，如用join替代子查詢、union all替代or、避免select *等；9.啟用慢查詢?nèi)罩静⑹褂?a>工具分析性能瓶頸；10.避免索引失效的常見問題，如隱式類型轉(zhuǎn)換、like以%開頭、不滿足最左前綴原則等；11.評估索引選擇性，選擇唯一值比例高的列建立索引。
避免mysql全表掃描，核心在于合理利用索引，并優(yōu)化你的查詢語句。索引就像一本書的目錄，能幫你快速定位到所需內(nèi)容，而不是一頁一頁地翻。
解決方案
-
分析查詢，識別瓶頸：使用 EXPLaiN 命令，查看你的SQL查詢計劃。關(guān)注 type 列，如果顯示 ALL，那就意味著全表掃描。possible_keys 列會告訴你哪些索引可能被使用，key 列告訴你實際使用的索引。
EXPLAIN SELECT * FROM users WHERE age > 30;
-
創(chuàng)建合適的索引：基于查詢條件創(chuàng)建索引。記住，索引并非越多越好，過多的索引會降低寫入性能，并占用額外的存儲空間。針對高頻查詢的字段建立索引。
CREATE INDEX idx_age ON users (age);
-
優(yōu)化WHERE子句：避免在 WHERE 子句中使用函數(shù)或表達式，這會導(dǎo)致索引失效。比如 WHERE YEAR(birthday) = 1990 應(yīng)該改為 WHERE birthday >= ‘1990-01-01’ AND birthday
-
限制返回的數(shù)據(jù)量：使用 LIMIT 子句限制返回的行數(shù)，特別是對于不需要所有數(shù)據(jù)的查詢。
-
定期維護索引：定期使用 OPTIMIZE TABLE 命令優(yōu)化表，整理碎片，提高索引效率。
-
考慮使用分區(qū)表：對于大型表，可以考慮使用分區(qū)表，將數(shù)據(jù)分割成更小的、易于管理的部分，從而減少全表掃描的范圍。
如何利用索引覆蓋優(yōu)化查詢？
索引覆蓋是指查詢所需的所有字段都包含在索引中，這樣MySQL可以直接從索引中獲取數(shù)據(jù)，而無需回表查詢。回表查詢是指在索引中找到符合條件的記錄后，需要根據(jù)主鍵ID再次回到數(shù)據(jù)表中查詢其他字段的值。
要實現(xiàn)索引覆蓋，你需要創(chuàng)建一個包含所有查詢字段的復(fù)合索引。例如，如果你的查詢是 SELECT name, age FROM users WHERE city = ‘Beijing’，那么你可以創(chuàng)建一個包含 city, name, age 的復(fù)合索引。
CREATE INDEX idx_city_name_age ON users (city, name, age);
使用 EXPLAIN 命令檢查查詢計劃，如果 Extra 列顯示 Using index，則表示使用了索引覆蓋。
查詢重寫有哪些常見技巧？
查詢重寫是指修改SQL查詢語句，使其更高效地執(zhí)行。這通常涉及到以下幾個方面：
-
子查詢優(yōu)化：盡量避免使用 IN 或 NOT IN 子查詢，可以使用 JOIN 替代。
-- 優(yōu)化前 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'Beijing'); -- 優(yōu)化后 SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = 'Beijing';
-
OR優(yōu)化：使用 UNION ALL 替代 OR，特別是當 OR 連接的條件涉及不同的索引時。
-- 優(yōu)化前 SELECT * FROM products WHERE price < 100 OR category = 'Electronics'; -- 優(yōu)化后 SELECT * FROM products WHERE price < 100 UNION ALL SELECT * FROM products WHERE category = 'Electronics' AND price >= 100;
注意：使用 UNION ALL 前，確保兩個子查詢沒有重復(fù)數(shù)據(jù)。
-
使用連接代替子查詢：子查詢通常會創(chuàng)建臨時表，影響性能。使用 JOIN 可以避免這種情況。
-
優(yōu)化count()查詢：COUNT(*) 會統(tǒng)計所有行，即使某些列為 NULL。如果只需要統(tǒng)計非 NULL 列的數(shù)量，可以使用 COUNT(column_name)。
-
*避免使用 `SELECT `**：只選擇需要的列，減少數(shù)據(jù)傳輸量。
如何監(jiān)控MySQL的慢查詢？
慢查詢?nèi)罩臼嵌ㄎ恍阅芷款i的重要工具。啟用慢查詢?nèi)罩荆梢杂涗泩?zhí)行時間超過指定閾值的sql語句。
-
啟用慢查詢?nèi)罩?/strong>：在MySQL配置文件（my.cnf 或 my.ini）中設(shè)置以下參數(shù)：
slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log # 日志文件路徑 long_query_time = 2 # 執(zhí)行時間超過2秒的SQL語句會被記錄 log_output = FILE # 日志輸出到文件
重啟MySQL服務(wù)使配置生效。
-
分析慢查詢?nèi)罩?/strong>：可以使用 mysqldumpslow 工具分析慢查詢?nèi)罩荆页鰣?zhí)行頻率高、執(zhí)行時間長的SQL語句。
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log # 找出執(zhí)行時間最長的前10條SQL語句
-
使用性能監(jiān)控工具：可以使用諸如 Percona Monitoring and Management (PMM)、prometheus + grafana 等工具，實時監(jiān)控MySQL的性能指標，包括慢查詢數(shù)量、CPU使用率、內(nèi)存使用率等。
如何避免索引失效？
索引失效會導(dǎo)致MySQL放棄使用索引，從而進行全表掃描。以下是一些常見的導(dǎo)致索引失效的原因及避免方法：
-
使用函數(shù)或表達式：在 WHERE 子句中使用函數(shù)或表達式，會導(dǎo)致索引失效。
-- 索引失效 SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 優(yōu)化 SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
-
隱式類型轉(zhuǎn)換：當查詢條件的數(shù)據(jù)類型與列的數(shù)據(jù)類型不匹配時，MySQL可能會進行隱式類型轉(zhuǎn)換，導(dǎo)致索引失效。
-- 假設(shè) user_id 是 VARCHAR 類型 SELECT * FROM users WHERE user_id = 123; -- 索引可能失效 -- 優(yōu)化 SELECT * FROM users WHERE user_id = '123';
-
使用 != 或 ：盡量避免使用 != 或 操作符，這通常會導(dǎo)致全表掃描。可以使用其他方式替代，例如使用 IN 或 NOT IN。
-
LIKE 查詢以 % 開頭：LIKE ‘%keyword’ 會導(dǎo)致索引失效，因為索引無法從中間開始匹配。可以使用全文索引或搜索引擎解決這個問題。
-
組合索引不滿足最左前綴原則：如果有一個組合索引 (a, b, c)，那么只有在查詢條件中包含 a 或 a, b 或 a, b, c 時，索引才會被使用。如果查詢條件只有 b 或 c，索引將失效。
-
數(shù)據(jù)分布不均勻：當某個索引列的值重復(fù)率很高時，MySQL可能會認為使用索引的效率低于全表掃描，從而放棄使用索引。
索引選擇性是什么，如何評估？
索引選擇性是指索引列中唯一值的比例。選擇性越高，索引的效率越高。一個選擇性很低的索引，比如性別（男/女），通常不如選擇性高的索引，比如用戶ID。
可以使用以下公式計算索引選擇性：
索引選擇性 = COUNT(DISTINCT column_name) / COUNT(*)
選擇性的值越接近 1，索引的效率越高。
評估索引選擇性可以幫助你判斷是否需要創(chuàng)建索引，以及應(yīng)該選擇哪些列作為索引。對于選擇性低的列，可以考慮與其他列組合成復(fù)合索引，或者不創(chuàng)建索引。
