場景

不廢話直接建表：

create table t_log
(
    id                 bigint auto_increment comment '主鍵id'
        primary key,
    user_id            bigint                                 null comment '用戶ID',
    eqp_number         varchar(255)                           null comment '設備號',
    type               char         default '1'               null comment '日志類型 0離線 1上線',
    title              varchar(255) default ''                null comment '日志標題',
    remote_addr        varchar(255)                           null comment '操作IP地址',
    current_project_id bigint                                 not null comment '當前項目ID',
    root_project_id    bigint                                 not null comment '根項目ID',
    create_time        datetime     default CURRENT_TIMESTAMP null comment '創建時間',
    eqp_name           varchar(50)                            null comment '設備名稱',
    eqp_model          varchar(50)                            null comment '設備型號',
    eqp_major_type     varchar(5)                             null comment '設備大類1:微斷主機;2';

)
    comment '日志' row_format = DYNAMIC;

現在有一個需求是需要根據項目類型(eqp_major_type) 和當前項目id（current_project_id）去查詢且需要按著創建時間排序以10個分頁。

OK

SELECT a.current_project_id AS projectId, a.eqp_name, a.eqp_model, a.eqp_number, a.type, a.create_time
FROM t_eqp_log a
WHERE a.current_project_id in (176)
and a.eqp_major_type = 1
order by a.create_time desc
LIMIT 10;

easy啊。

時間久了數據量變大 查詢變慢了，leader： 那個誰，你去把這個接口sql優化一下

你看都沒看直接加上索引

create index t_eqp_log_create_time_index
    on t_eqp_log (create_time);

create index t_eqp_log_current_project_id_index
    on t_eqp_log (current_project_id);

create index t_eqp_log_eqp_major_type_index
    on t_eqp_log (eqp_major_type);

條件字段都加上不就得了嗎

但是

目標索引只有createtime被擊中， 其他兩個字段都沒生效，

不對勁啊 （開始Google GPT

一句話總結就是說mysql優化器認為通過create_time索引效率更高，也就是三個字段都沒有索引的情況，create_time字段查詢的成本會更高，so嘎 那怎么解決呢。很簡單啊 直接聯合索引啊

create index t_eqp_log_current_project_id_eqp_major_type_create_time_index
    on t_eqp_log (create_time, current_project_id, eqp_major_type);

再次執行一下 不出意外就要出意外了 果然又沒生效

分析分析再分析

怎么這行sql越看越不對勁呢。淦 eqp_major_type是string類型的 也就是 eqp_major_type = 1 是存在一個強轉的過程

強轉就會導致聯合索引失效 服了 繼續再來

等會 有點對勁啊 趕緊看看文檔

官方文檔這大一堆 總結就一句話。index是全索引掃描 效率不如range 且重點：

性能從最好到最差：null > system > const > eq_ref > ref > range > index > all

繼續看看extra

總結一下就是說這次執行情況為：index 類型意味著 MySQL 使用了全索引掃描，盡管聯合索引被使用，但可能在過濾上不如 range 類型有效。Backward index scan 表示它在倒序掃描整個索引，這種掃描方式效率相對低。

這時候突然有靈感，既然選擇了聯合查詢肯定要考慮最左前導列原則啊，create_time作為第一導列 但是where條件卻沒有，也就是沒法用它很好的利用where條件來過濾大部分數據，自然會選擇全索引掃描 趕緊試試驗證一下

h h h 果然皇天不負??的心， 變成range了。依舊剛學到的 range是> index 成了。

讓我們總結一下 ：

1. 第一種情況：create_time 作為索引的第一列。
2. 第二種情況：current_project_id 作為索引的第一列。

為什么索引順序影響查詢效率

在 MySQL 中，索引的順序對于查詢的執行計劃有重要影響，因為 MySQL 是按索引的最左前導列（即索引的第一個字段）開始掃描的。查詢條件中的字段順序應與索引順序匹配，以便 MySQL 能夠充分利用索引來減少掃描的記錄數。

所以如我們當時猜想一樣。

create_time作為第一列的時候。where條件并不再索引的前置位置，那么 MySQL 可能只能利用索引的第一部分來進行范圍掃描，而不能有效地利用后續條件來進一步減少掃描的范圍。也就導致了index 類型的全索引掃描。

而第二種情況 當 current_project_id 是索引的第一列，并且 WHERE 子句中首先使用它來篩選數據時，MySQL 可以更好地利用索引。這通常會導致 range 掃描類型，因為它允許 MySQL 通過范圍條件直接跳過不相關的記錄。在這種情況下，MySQL 更容易使用索引的其余部分（如 create_time ）來進一步減少掃描范圍。

總結與比較

• 第一種情況：index 類型意味著 MySQL 使用了全索引掃描，盡管聯合索引被使用，但可能在過濾上不如 range 類型有效。Backward index scan 表示它在倒序掃描整個索引，這種掃描方式效率相對低。
• 第二種情況：range 類型表示 MySQL 進行了范圍掃描，這通常更高效，并且 Using index condition 進一步表明 MySQL 在掃描過程中使用了索引來過濾數據。總體來說，這種情況更能夠充分利用聯合索引，尤其是在處理 WHERE 子句條件時

既然說了最左前導列原則，這里復習一下八股文：

1. 計算 函數導致索引失效 where a+1=3; WHERE LEFT(emp.name,3) = 'abc';--索引失效
2. 模糊查詢 SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效
3. 類型轉換 select * from emp where type = 1; --type為string類型 且存儲數值 索引失效
4. 條件中存在or where a>100 or b<100; --條件查詢存在or
5. 不等于(!= 或者<>) SELECT * FROM emp WHERE emp.name <> 'abc' ;--索引失效
6. is not null EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL; EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效
7. 最左匹配原則：

調整一下剛才的索引

create index t_eqp_log_current_project_id_eqp_major_type_user_id_index
    on t_eqp_log (current_project_id, eqp_major_type, user_id);

? 正常情況下只要存在current_project_id即聯合索引生效，但：

1. 索引中范圍條件右邊的列失效

當 a=30 b>100 c='abc'. 只走a b索引。

注意：當b條件覆蓋絕大數數據時也可能不走索引 由優化器成本決定

當a=30 c='abc' b>100 正常走三個索引

2. 存在聯合索引和單一索引時 如果聯合索引效率高也不走單一索引