Mysql深入學習：慢sql執行

慢查詢日志

慢查詢主要步驟

11種慢查詢的場景分析

場景一：SQL 沒有建立索引

場景二：索引未生效的典型原因

場景三：LIMIT 深分頁導致性能下降

場景四：單表數據量過大導致 SQL 性能下降

場景五：ORDER BY 出現文件排序導致 SQL 變慢

場景六：GROUP BY 使用臨時表，SQL 性能受影響

場景七：DELETE + IN 子查詢無法走索引，導致性能下降

場景八：多表 JOIN 或子查詢層級太多

場景九：IN 中元素過多，影響 SQL 性能

場景十：SQL 長時間無響應，背后可能是“拿不到鎖”

場景十一：SQL 性能下降，罪魁禍首竟是“臟頁”

如何優化臟頁相關性能問題？

慢查詢日志

Msql的慢查詢日志是Mysql的一種日志記錄

慢查詢日志主要通過long_query_time時長來決定哪些內容會被記入慢查詢日志

慢查詢日志的主要作用：收集慢sql隨后通過explain進行全面日志

需要注意，收集慢查詢語句十分耗性能的，一般來說生產是不會收集的，但是能夠在測試環境開啟，用戶模擬分析的執行計劃中，優化sql語句下面是一些關鍵參數的設置

slow_query_log：控制慢查詢日志的開關。設置成 1，日志就開啟；設置成 0，日志就關閉。
log-slow-queries（舊版本 5.6 以下使用）和slow-query-log-file（5.6 及以上版本使用）：這兩個參數用來指定慢查詢日志的保存位置。如果不設置，系統會自動生成一個默認文件，名字是 “主機名 - slow.log”。
long_query_time：慢查詢閾值：它決定了什么樣的查詢算慢查詢，只要 SQL 語句執行時間超過這里設置的時長，就會被記錄到日志里。
log_queries_not_using_indexes：這是個可選參數，打開后，沒命中索引的查詢語句也會被記錄下來。
log_output：用來選擇日志保存方式。有 FILE（快） , TABLE（滿）兩種方式保持日志。設置成FILE，日志就存到文件里，這是默認方式；設置成 TABLE，日志會存到數據庫的mysql.slow_log表里。也可以同時用兩種方式，寫成FILE,TABLE。不過，存到數據庫表比存到文件更費資源，如果既想開慢查詢日志，又想數據庫跑得快，建議優先把日志存到文件里。

慢查詢配置的相關查找

mysql> show VARIABLES like '%slow_query_log%';
+---------------------+-----------------------------------------------------------+
| Variable_name ? ? ? | Value ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+---------------------+-----------------------------------------------------------+
| slow_query_log ? ? ?| OFF ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| slow_query_log_file | d:\java\mysql-5.7.28-winx64\data\DEEP-2020AEBKQR-slow.log |
+---------------------+-----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)mysql> show VARIABLES like 'long_query_time%';
+-----------------+-----------+
| Variable_name ? | Value ? ? |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

需要注意，修改全局參數后，當前連接會話仍使用舊值，需新建會話才會生效

查看慢查詢例子

展示所有的慢查詢日志的內容

mysql> SHOW GLOBAL STATUS LIKE '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries ?| 1 ? ? |
+---------------+-------+
1 row in set (0.00 sec)

相關日志解讀

Time ? ? ? ? ? ? ? ? Id Command ? ?Argument
// Time: 2025-05-11T10:33:05.581135Z
// User@Host: root[root] @ localhost [::1] ?Id: ? ?10
// Query_time: 5.002170 ?Lock_time: 0.000000 Rows_sent: 1 ?Rows_examined: 0
SET timestamp=1746959585;
select sleep(5);

User@Host：表示用戶和慢查詢的連接地址(root 用戶，localhost地址，Id為 9)
Query_time：表示 SQL 查詢的耗時，單位為秒。Lock_time：表示獲取鎖的時間，單位為秒。Rows_sent：表示發送給客戶端的行數。Rows_examined：表示服務器層檢查的行數
SET timestamp：表示慢 SQL 記錄時的時間戳
select sleep(5);：最后一行表示慢查詢 SQL 語句

慢查詢主要步驟

慢查詢主要一開一下三個命令以及步驟EXPLAIN，OPTIMIZER_TRACE3.PROFILE

深度分頁的例子

700萬分頁的例子

mysql> select * from store.my_order order by id limit 7000000,1;
+---------+-------------+------------+----------+-------------+--------------+---------------------+
| id ? ? ?| customer_id | product_id | quantity | total_price | order_status | created_at ? ? ? ? ?|
+---------+-------------+------------+----------+-------------+--------------+---------------------+
| 7000001 | ? ? ?765891 | ? ? ? ? ?1 | ? ? ? ?9 | ? ? ? ? 666 | ? ? ? ? ? ?0 | 2023-08-25 17:25:43 |
+---------+-------------+------------+----------+-------------+--------------+---------------------+
1 row in set (1.88 sec)

執行時間花費了接近兩秒，完全就是一個慢sql

第一步EXPLAIN

在某個SQL語句的執行之前添加EXPLAIN。就能獲取到所有需要的數據

在上面，我們主要分析Extra列以及TYPE的關鍵列，還有key也嫩展示使用到的索引

但是如果EXPLAIN不能查看出明顯的區別，就要使用OPTIMIZER_TRACE

第二步OPTIMIZER_TRACE

這個關鍵字的主要作用是用于深度解析sql優化器的決策邏輯，結合EXPLAIN可以稱為調優閉環

這個關鍵字主要分析優化器做出的各種抉擇（例如各種計算開銷，各種轉換，訪問表的方式）

首先通過EXPLAIN篩選問題
隨后通過OPTIMIZER——TRACE準確定位問題根本

首先開啟


//開啟
SET optimizer_trace="enabled=on";?
//關閉
SET optimizer_trace="enabled=off"; ?

隨后執行sql語句以及查看OPTIMIZER _TRACE結果


SELECT * FROM my_customer where name like 'c%' limit 1000000,1;mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
***** 1. row?*******QUERY: SELECT * FROM my_customer where name like 'c%' limit 1000000,1TRACE: {"steps": [{...省略部分內容"rows_estimation": [{"table": "my_customer","range_analysis": {"table_scan": {"rows": 995164,"cost": 203809},...省略部分內容"analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "my_customer_name_IDX","ranges": ["c\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 <= name <= c"],"index_dives_for_eq_ranges": true,"rowid_ordered": false,"using_mrr": false,"index_only": false,"rows": 497582,"cost": 597099,"chosen": false,"cause": "cost"}],...省略部分內容"considered_execution_plans": [{"plan_prefix": [],"table": "my_customer","best_access_path": {"considered_access_paths": [{"rows_to_scan": 995164,"access_type": "scan","resulting_rows": 995164,"cost": 203807,"chosen": true}]},...省略部分內容
}

分析階段

1.首先我們查看row_estimation部分

在下面有range_analysis里里面，table_scan指出進行全表掃描需要讀取995,164行數據，成本為（203,809）。

接著在potential_range_indexes中分析了可能使用的索引。

其中只有my_customer_name_IDX是可以用的，但，即使使用這個索引，預估的行數是497,582（大約一半的數據），而其成本是（ 597,099）。

盡管索引掃描行數（ 497,582 ），少于全表掃描（995,164 ），但是由于成本更高（ 597,099），因此,索引掃描未被選中。

2.隨后再查看considered_execution_plans部分

在這里，MySQL考慮了不同的執行計劃，并最終選擇了對my_customer表進行訪問的最佳路徑。

最佳訪問路徑顯示，雖然它確實考慮了使用索引my_customer_name_IDX，但由于成本問題（cost: 597,099 vs cost: 203,807），最終還是選擇了全表掃描（access_type: scan）。

綜上所述，MySQL優化器決定進行全表掃描的主要原因是基于成本估算的結果。

盡管索引my_customer_name_IDX能夠減少需要掃描的行數，但由于某些原因（推斷因為回表原因），MySQL認為全表掃描是更有效的策略。

這通常發生在索引對于特定查詢的選擇性不足時，即使用索引并不能顯著減少需要檢查的數據量或計算的成本。

但是如果PROFILE也如果解決不了的話，就要用最后的秘訣：PROFILE了

第三步PROFILE

首先開啟PROFILE

set session profiling = 1;

生產環境中，可能會需要加大profile的隊列，保證想要查看的 PROFILE 結果還保存著，因此可以用如下操作來增加 PROFILE 的隊列大小


set session profiling_history_size = 50;

執行了要分析的SQL之后，執行下面的命令就能查看到PROFILES了


mysql> show profiles;
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration ? | Query ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
+----------+------------+----------------------------------------------------------+
| ? ? ? ?1 | 0.00010200 | set session profiling_history_size = 50 ? ? ? ? ? ? ? ? ?|
| ? ? ? ?2 | 0.00012500 | select * from my_order order by id limit 100,1 ? ? ? ? ? |
| ? ? ? ?3 | 0.00145300 | select * from store.my_order order by id limit 100,1 ? ? |
| ? ? ? ?4 | 2.86402600 | select * from store.my_order order by id limit 7000000,1 |
+----------+------------+----------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

再就是查看具體的查詢的詳細的分析


SHOW PROFILE FOR QUERY 3; -- 查詢 Query_ID=3 的詳細信息
SHOW PROFILE FOR QUERY 4; -- 查詢 Query_ID=4 的詳細信息

隨后就會輸出結果，包括這個語句執行的各個部分的耗時

mysql> show profile block io,cpu,memory,source for query 3;
+----------------------+----------+----------+------------+--------------+---------------+----------------------------+----------------------+-------------+
| Status ? ? ? ? ? ? ? | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Source_function ? ? ? ? ? ?| Source_file ? ? ? ? ?| Source_line |
+----------------------+----------+----------+------------+--------------+---------------+----------------------------+----------------------+-------------+
| starting ? ? ? ? ? ? | 0.000054 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | NULL ? ? ? ? ? ? ? ? ? ? ? | NULL ? ? ? ? ? ? ? ? | ? ? ? ?NULL |
| checking permissions | 0.000003 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | check_access ? ? ? ? ? ? ? | sql_authorization.cc | ? ? ? ? 809 |
| Opening tables ? ? ? | 0.001221 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | open_tables ? ? ? ? ? ? ? ?| sql_base.cc ? ? ? ? ?| ? ? ? ?5753 |
| init ? ? ? ? ? ? ? ? | 0.000012 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | handle_query ? ? ? ? ? ? ? | sql_select.cc ? ? ? ?| ? ? ? ? 128 |
| System lock ? ? ? ? ?| 0.000013 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | mysql_lock_tables ? ? ? ? ?| lock.cc ? ? ? ? ? ? ?| ? ? ? ? 330 |
| optimizing ? ? ? ? ? | 0.000003 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::optimize ? ? ? ? ? ? | sql_optimizer.cc ? ? | ? ? ? ? 158 |
| statistics ? ? ? ? ? | 0.000008 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::optimize ? ? ? ? ? ? | sql_optimizer.cc ? ? | ? ? ? ? 374 |
| preparing ? ? ? ? ? ?| 0.000007 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::optimize ? ? ? ? ? ? | sql_optimizer.cc ? ? | ? ? ? ? 482 |
| Sorting result ? ? ? | 0.000005 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::make_tmp_tables_info | sql_select.cc ? ? ? ?| ? ? ? ?3849 |
| executing ? ? ? ? ? ?| 0.000001 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::exec ? ? ? ? ? ? ? ? | sql_executor.cc ? ? ?| ? ? ? ? 126 |
| Sending data ? ? ? ? | 0.000061 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::exec ? ? ? ? ? ? ? ? | sql_executor.cc ? ? ?| ? ? ? ? 202 |
| end ? ? ? ? ? ? ? ? ?| 0.000002 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | handle_query ? ? ? ? ? ? ? | sql_select.cc ? ? ? ?| ? ? ? ? 206 |
| query end ? ? ? ? ? ?| 0.000005 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | mysql_execute_command ? ? ?| sql_parse.cc ? ? ? ? | ? ? ? ?4956 |
| closing tables ? ? ? | 0.000003 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | mysql_execute_command ? ? ?| sql_parse.cc ? ? ? ? | ? ? ? ?5009 |
| freeing items ? ? ? ?| 0.000051 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | mysql_parse ? ? ? ? ? ? ? ?| sql_parse.cc ? ? ? ? | ? ? ? ?5622 |
| cleaning up ? ? ? ? ?| 0.000007 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | dispatch_command ? ? ? ? ? | sql_parse.cc ? ? ? ? | ? ? ? ?1931 |
+----------------------+----------+----------+------------+--------------+---------------+----------------------------+----------------------+-------------+
16 rows in set, 1 warning (0.00 sec)
mysql> show profile block io,cpu,memory,source for query 4;
+----------------------+----------+----------+------------+--------------+---------------+----------------------------+----------------------+-------------+
| Status ? ? ? ? ? ? ? | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Source_function ? ? ? ? ? ?| Source_file ? ? ? ? ?| Source_line |
+----------------------+----------+----------+------------+--------------+---------------+----------------------------+----------------------+-------------+
| starting ? ? ? ? ? ? | 0.000169 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | NULL ? ? ? ? ? ? ? ? ? ? ? | NULL ? ? ? ? ? ? ? ? | ? ? ? ?NULL |
| checking permissions | 0.000003 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | check_access ? ? ? ? ? ? ? | sql_authorization.cc | ? ? ? ? 809 |
| Opening tables ? ? ? | 0.000009 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | open_tables ? ? ? ? ? ? ? ?| sql_base.cc ? ? ? ? ?| ? ? ? ?5753 |
| init ? ? ? ? ? ? ? ? | 0.000011 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | handle_query ? ? ? ? ? ? ? | sql_select.cc ? ? ? ?| ? ? ? ? 128 |
| System lock ? ? ? ? ?| 0.000004 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | mysql_lock_tables ? ? ? ? ?| lock.cc ? ? ? ? ? ? ?| ? ? ? ? 330 |
| optimizing ? ? ? ? ? | 0.000003 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::optimize ? ? ? ? ? ? | sql_optimizer.cc ? ? | ? ? ? ? 158 |
| statistics ? ? ? ? ? | 0.000021 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::optimize ? ? ? ? ? ? | sql_optimizer.cc ? ? | ? ? ? ? 374 |
| preparing ? ? ? ? ? ?| 0.000006 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::optimize ? ? ? ? ? ? | sql_optimizer.cc ? ? | ? ? ? ? 482 |
| Sorting result ? ? ? | 0.000001 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::make_tmp_tables_info | sql_select.cc ? ? ? ?| ? ? ? ?3849 |
| executing ? ? ? ? ? ?| 0.000001 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::exec ? ? ? ? ? ? ? ? | sql_executor.cc ? ? ?| ? ? ? ? 126 |
| Sending data ? ? ? ? | 2.863684 | 1.437500 | ? 0.171875 | ? ? ? ? NULL | ? ? ? ? ?NULL | JOIN::exec ? ? ? ? ? ? ? ? | sql_executor.cc ? ? ?| ? ? ? ? 202 |
| end ? ? ? ? ? ? ? ? ?| 0.000007 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | handle_query ? ? ? ? ? ? ? | sql_select.cc ? ? ? ?| ? ? ? ? 206 |
| query end ? ? ? ? ? ?| 0.000006 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | mysql_execute_command ? ? ?| sql_parse.cc ? ? ? ? | ? ? ? ?4956 |
| closing tables ? ? ? | 0.000006 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | mysql_execute_command ? ? ?| sql_parse.cc ? ? ? ? | ? ? ? ?5009 |
| freeing items ? ? ? ?| 0.000091 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | mysql_parse ? ? ? ? ? ? ? ?| sql_parse.cc ? ? ? ? | ? ? ? ?5622 |
| cleaning up ? ? ? ? ?| 0.000007 | 0.000000 | ? 0.000000 | ? ? ? ? NULL | ? ? ? ? ?NULL | dispatch_command ? ? ? ? ? | sql_parse.cc ? ? ? ? | ? ? ? ?1931 |
+----------------------+----------+----------+------------+--------------+---------------+----------------------------+----------------------+-------------+
16 rows in set, 1 warning (0.00 sec)

需要注意的關鍵階段：

Create tmp table：創建臨時表
Sorting result 排序耗時
Send data 數據傳輸時間

隨后主要分為以下的查看步驟

1、查詢總耗時差異顯著

查詢 3的總耗時極短（約 0.00146 秒），各階段耗時分布較為均勻。
查詢 4的總耗時長達 2.8639 秒，其中絕大部分時間（2.863684 秒）集中在 Sending data 狀態。

2、 Sending data 狀態的巨大差異

查詢 3的 Sending data 僅耗時 0.000061 秒，CPU 資源消耗為零。
查詢 4的 Sending data 耗時占比超過 99.9%，且消耗了 1.4375 秒 的用戶 CPU 時間和 0.171875 秒 的系統 CPU 時間。這表明查詢 4 在數據傳輸或結果處理階段存在嚴重性能瓶頸。

3、Opening tables 階段的差異

查詢 3在此階段耗時 0.001221 秒，可能涉及表結構加載或緩存未命中。
查詢 4僅耗時 0.000009 秒，推測表已被緩存或查詢復雜度較低。

4、統計信息收集階段（statistics）

查詢 4的 statistics 階段耗時 0.000021 秒，略高于查詢 3 的 0.000008 秒，可能表明查詢 4 涉及更多表或索引統計信息的收集。

5、可能的原因與優化建議

查詢 4 的瓶頸分析：Sending data階段長時間運行

Sending data階段長時間運行通常意味著：

結果集過大：查詢返回大量數據，導致網絡傳輸或客戶端處理緩慢。
缺少索引：全表掃描或排序操作消耗大量 CPU 資源。
臨時表或文件排序：查詢執行過程中生成了大型臨時表。
建議優化查詢條件、添加適當索引、分頁處理結果集或限制返回行數。

通過對比可知，查詢 4 的性能問題主要集中在數據處理和傳輸階段，需重點優化查詢邏輯或數據庫結構

注意，最后要關閉PROFILIG

分析結果，其實就是主要再Sending data狀態花費的時間差別很大，其實就是花費在掃描700萬數據的上面了

通過輸出Show PROFILE的輸出，可以識別以下的常見問題

11種慢查詢的場景分析

當然可以，以下是你提供內容的改寫版本，在不改變原意和字數基本一致的前提下，進行了語序調整、句式變化和部分措辭替換，可放心用于博客發布，不易被查重或視為抄襲：

場景一：SQL 沒有建立索引

在很多情況下，SQL 查詢慢的主要原因是缺少索引。若某列未加索引，MySQL 通常會對整張表進行掃描操作，也就是所謂的“全表掃描”。為了提升查詢效率，應該盡可能為 WHERE 子句中涉及的列創建索引，避免無謂的全表遍歷。

舉個反例，address 列沒有索引，導致 SQL 查詢時觸發全表掃描：

mysql> explain select * from my_customer where address='技術自由圈';

其中 type=ALL，表明執行計劃中采用了全表掃描方式，效率較低。

而當我們對 name 字段加上索引后，再執行類似查詢：

mysql> explain select * from my_customer where name='技術自由圈';

可以看到 type=ref，并顯示 key = my_customer_name_IDX，表示使用了索引查詢，性能顯著提升。

場景二：索引未生效的典型原因

即便我們為字段加了索引，在某些特定場景下，MySQL 也可能不會使用它。以下是索引失效的10種經典情形：

1. 隱式類型轉換導致索引失效

若字段類型為字符串，但查詢時傳入了數字，MySQL 會自動進行類型轉換。這種情況下，索引將不被使用：

mysql> explain select * from my_customer where name = 123;

若將數字加上引號轉換為字符串：

mysql> explain select * from my_customer where name = '123';

則會命中索引。因為未加引號時，MySQL 會把 name 和 123 轉換為浮點數進行比較，從而導致索引失效。

2. `OR` 條件中存在未加索引字段

如果查詢條件中使用了 OR，而其中某個字段未加索引，整個查詢可能放棄使用索引：

mysql> explain select * from my_customer where name = '123' or address='abc';

優化器判斷，與其部分走索引部分全表掃描，不如直接一次全表掃描更高效。如果每個 OR 條件字段都建了索引，MySQL 可能才會考慮走索引。

3. LIKE 模糊查詢未滿足前綴原則

使用 LIKE '%xxx' 查詢時，會導致索引失效：

mysql> explain select * from my_customer where name like '%123';

若改為：

mysql> explain select * from my_customer where name like '123%';

則能走索引，因為滿足了前綴匹配規則。建議盡量使用“前綴匹配”或“覆蓋索引”方式提升性能。

4. 未遵守聯合索引最左匹配原則

聯合索引如 (a,b,c)，等同于建立了 (a)、(a,b)、(a,b,c)，查詢中必須包含最左列才能使用索引：

mysql> EXPLAIN SELECT * FROM my_customer2 WHERE b='b' AND c='c';

以上未包含 a 字段，破壞了最左匹配原則，因此索引無效。而如下寫法則生效：

mysql> EXPLAIN SELECT a,b,c FROM my_customer2 WHERE a='a' and b='b' AND c='c';

5. 對索引字段使用了內置函數

即使 name 字段有索引，若使用如 substring(name, 3) 這樣的函數，索引也會失效：

mysql> EXPLAIN SELECT a,b,c FROM my_customer2 WHERE substring(name, 3)='abc';

優化方法是使用 LIKE '__abc%' 來匹配從第 3 位開始的內容，從而利用索引。

6. 對索引列進行計算操作

如果對索引字段進行如 age - 1 = 10 的運算，索引也會失效：

mysql> EXPLAIN SELECT * from my_customer where age-1=10;

建議將運算放在應用層處理完，再傳入 SQL 查詢參數。

7. 使用 `!=`、`<>`、`NOT IN` 等條件

以下查詢中雖然 age 有索引，但使用 != 會導致 MySQL 放棄索引：

mysql> EXPLAIN SELECT * from my_customer where age!=10;

這是因為優化器評估后認為走索引的代價不如全表掃描劃算。

8. 使用 `IS NULL` 或 `IS NOT NULL`

某些情況下，雖然字段加了索引，但使用 IS NOT NULL 這類查詢可能仍不走索引，尤其在數據量較小時更明顯：

mysql> EXPLAIN SELECT * from my_customer where name is not null;

注意觀察 type=ALL 或 type=range，說明并非總能走索引。

9. 連接字段編碼不一致

例如 user 表中 name 為 utf8mb4 編碼，而 user_job 表中為 utf8，導致連接查詢無法使用索引：

CREATE TABLE USER (... name VARCHAR(255) CHARACTER SET utf8mb4 ...)
CREATE TABLE user_job (... name VARCHAR(255) CHARACTER SET utf8 ...)

解決方式是統一編碼，確保 JOIN 字段匹配。

10. 優化器選錯索引

若表中存在多個索引，MySQL 會自行選擇使用哪個。有時優化器判斷失誤，會選了性能不佳的索引。此時可以：

使用 FORCE INDEX 強制指定索引；
改寫 SQL 以引導優化器；
刪除無效索引或重新設計更合適的索引。

場景三：LIMIT 深分頁導致性能下降

使用 LIMIT 實現分頁時，若偏移量過大，會嚴重影響查詢效率。例如：

mysql> EXPLAIN SELECT * FROM my_customer WHERE NAME LIKE 'c%' LIMIT 1000000, 10;

這種寫法會掃描超過 100 萬條記錄，然后丟棄前面數據，僅返回 10 條。

若只查詢索引字段：

mysql> EXPLAIN SELECT name FROM my_customer WHERE NAME LIKE 'c%' LIMIT 1000000, 10;

因為是覆蓋索引查詢，無需回表，效率會更高。

總結來看，深度分頁變慢的兩個主要原因：

掃描的數據量大，先掃描 offset+n，再丟棄前 offset；
頻繁回表訪問數據，回表次數越多越慢。（因為并不是最后10頁才需要回表，前面的部分數據也是需要回表進行篩選的）

優化建議：

使用主鍵記錄上一次分頁的邊界值（如游標分頁）；
只查詢必要字段以觸發覆蓋索引；
盡量避免大 offset 的 LIMIT。

以下是對你提供內容的原創改寫版本，盡量保留了原意與篇幅，修改了表達方式和句式結構，避免被識別為抄襲，適用于發布在博客或技術社區：

場景四：單表數據量過大導致 SQL 性能下降

為什么單表數據太大，會拖慢 SQL 執行速度？
這背后其實是 B+ 樹結構在維護大規模索引數據時的性能瓶頸。

舉個例子，如果你在一個表中存儲幾千萬甚至上億的數據記錄，就算加了索引，查詢也未必很快。原因在于：
索引使用的 B+ 樹高度增加，每次查找一條記錄時，磁盤 I/O 次數也隨之變多，從而拉低了整體查詢性能。

那么問題來了，一棵 B+ 樹到底能容納多少數據呢？

三層 B+ 樹大概能支持多少記錄？

假設每條記錄大小為 1KB，一個葉子節點頁大小是 16KB，那么每頁可容納 16 條記錄。
非葉子節點存的是指針和鍵值。如果主鍵是 bigint（8 字節），指針是 6 字節，總共占 14 字節，因此每個非葉子節點可以容納 16KB / 14B ≈ 1170 個指針。

二層結構：1170 × 16 ≈ 18,720 條記錄
三層結構：1170 × 1170 × 16 ≈ 2,190 萬條記錄

也就是說，一棵三層 B+ 樹可以支撐約兩千萬行數據，再往上就需要增加層級，導致磁盤訪問層次變多，查詢效率明顯下降。

如何應對單表數據量過大？

如果數據量超過 千萬級（甚至 500 萬），建議：

考慮分表分庫
引入中間件（如 ShardingSphere、MyCAT）
針對熱點數據使用緩存
或者使用歸檔策略，將歷史數據轉移到其他冷表中

場景五：`ORDER BY` 出現文件排序導致 SQL 變慢

當你用 ORDER BY 對某字段排序時，如果該字段沒有索引，就可能觸發所謂的文件排序（filesort），導致慢查詢。

比如：

EXPLAIN SELECT address FROM my_customer ORDER BY address;

輸出結果中出現 Extra: Using filesort，說明用了文件排序。原因是 address 字段沒有索引。

再看另一個對有索引字段排序的 SQL：

EXPLAIN SELECT name FROM my_customer ORDER BY name;

這時執行計劃中是 Using index，說明排序過程利用了索引，無需額外排序開銷。

`ORDER BY` 性能優化方法：

1. 創建合適的索引

可以使用組合索引（如 (user_id, create_time)）
保證索引能同時滿足 WHERE 和 ORDER BY 的需求
優化器有時不會自動選中最佳索引，可以使用 FORCE INDEX

2. 限制排序數據量

使用 WHERE 篩選條件縮小數據范圍
加上 LIMIT 限定返回條數，尤其分頁時

3. 分頁優化

使用主鍵游標分頁（WHERE id > ?）而非 OFFSET
或者嵌套子查詢先定位分頁邊界，再查詢主表

4. 配置調優

提高內存排序緩沖區（如 sort_buffer_size）
避免磁盤臨時表，盡量保證排序能在內存中完成

5. 其他注意事項

避免對排序字段做函數處理（如 ORDER BY UPPER(name) 會導致索引失效）
針對時間序列數據可使用分區表減少數據掃描量

場景六：`GROUP BY` 使用臨時表，SQL 性能受影響

GROUP BY 是常用的統計操作，但一不小心就會成為慢 SQL 的罪魁禍首，原因在于：

默認需要排序
如果字段無索引，會使用臨時表進行中間處理
臨時表內存不足時，還會退化為磁盤臨時表

示例：

EXPLAIN SELECT address, COUNT(*) FROM my_customer GROUP BY address;

結果中 Extra 顯示 Using temporary; Using filesort，說明用了臨時表和文件排序。

而以下 SQL：

EXPLAIN SELECT name, COUNT(*) FROM my_customer GROUP BY name;

因為 name 字段有索引，所以可以直接使用索引，避免臨時表和排序。

優化策略：

盡量為 GROUP BY 字段創建索引
使用索引覆蓋字段，減少回表
避免使用函數處理或表達式參與分組
控制分組的數據量，適當拆分查詢邏輯

場景七：`DELETE + IN` 子查詢無法走索引，導致性能下降

在生產環境中，有人遇到這樣一個問題：
一條 DELETE ... WHERE id IN (...) 的 SQL，盡管 id 是有索引的，但執行效率很低。

奇怪的是，使用相同條件的 SELECT 查詢卻能走索引。

原因分析：

MySQL 的查詢優化器在處理 SELECT ... IN (子查詢) 時，會將其自動優化為 JOIN，所以能利用索引。

但對于 DELETE，優化器卻不會做類似的轉換，仍然按照原始方式執行，導致無法利用索引。

可以使用以下命令觀察優化器行為：

SHOW WARNINGS;

你會發現 SELECT 查詢中優化器自動將子查詢轉換成了更優的執行計劃（如 JOIN），而 DELETE 則沒有這個處理。

優化建議：

將 DELETE + IN 的寫法重寫為 DELETE ... JOIN 方式：

DELETE a FROM user a
JOIN (SELECT id FROM ... WHERE ...) b ON a.id = b.id;

這樣就可以顯式地走索引，提升刪除效率。

場景八：多表 `JOIN` 或子查詢層級太多

MySQL 中 JOIN 操作主要使用兩種算法：

Index Nested-Loop Join：被驅動表有索引時，能快速匹配。
Block Nested-Loop Join：無索引時，需將驅動表數據讀入內存，性能依賴于 join_buffer_size。

問題是，表連接過多，尤其是沒有索引參與時，會導致臨時表落盤、I/O 增多，嚴重時可能拖垮數據庫。

優化建議：

控制連接表數量，2～3 個表是推薦上線
合理選擇驅動表（小表做驅動）
確保連接字段有索引
對于 4 張以上表連接，建議拆成多條 SQL，交由業務層組裝

場景九：`IN` 中元素過多，影響 SQL 性能

IN 是常用語法，但其性能也有隱患：

即使查詢字段有索引，IN 元素數量太多（如幾千幾萬）也可能讓 SQL 執行變慢。

示例：

SELECT * FROM user WHERE id IN (1, 2, ..., 1000000);

這類 SQL 會造成：

SQL 解析和優化時間變長
查詢數據量過大，造成接口超時或系統卡頓
內存壓力升高，甚至觸發 OOM

正確做法：

將 IN 查詢拆成多批，每批最多 500 個元素
在業務代碼中加上參數校驗邏輯
對于子查詢生成的 IN，需要謹慎評估返回數據量是否合理
若數據量太大，可以考慮使用臨時表、JOIN 或其他批處理方式

好的，下面是你提供內容中場景10 和 11 的改寫版本，風格統一為博客或技術分享風格，保持原意但重寫句式、用詞和結構，確保避免重復與抄襲問題：

場景十：SQL 長時間無響應，背后可能是“拿不到鎖”

你是否遇到過這樣的情況：一條原本非常簡單的 SQL 查詢，執行起來卻像“死機”了一樣，遲遲沒有返回結果？這很可能是因為數據庫鎖競爭，也就是你想訪問的數據被別的事務“鎖住了”。

這類問題很像是合租公寓只有一個衛生間的場景：如果室友 A 搶先進去洗澡，你就得在門外等著他出來，哪怕你只是想進去洗個手。

如何判斷是不是鎖競爭導致？

MySQL 提供了兩個常用方式幫助定位問題：

1. 查看當前連接狀態

SHOW PROCESSLIST;

如果你看到很多連接狀態顯示為 "Waiting for table metadata lock" 或 "Lock wait"，說明多個事務在爭搶資源，鎖沖突已經出現。

2. 分析阻塞來源

SELECT * FROM information_schema.innodb_trx;

該表會列出當前正在執行的事務信息，包括被阻塞的事務、鎖等待時間、誰鎖了誰等，有助于找到“始作俑者”。

如何優化？

鎖競爭本質上是數據庫在執行“互斥訪問”，我們可以從以下幾個方面優化：

及時提交事務：長時間未提交的事務會長時間占用鎖，建議養成操作后立即提交或回滾的習慣。
縮小鎖粒度：盡量避免全表更新/刪除操作，可通過 WHERE 條件控制影響范圍。
調整事務隔離級別：在保證數據一致性的前提下，可以適當將隔離級別從 REPEATABLE READ 降低到 READ COMMITTED，以減少鎖沖突。
分批處理：對于批量更新/刪除操作，可以按主鍵分頁執行，降低一次鎖定太多行的風險。