最近收到一個慢sql工單,慢sql大概是這樣：“select xxx from tabel where type = 1”。
咦，type字段明明有索引啊，為啥是慢sql呢？
通過執行explain，發現實際上數據庫執行了全表掃描，從而被系統判定為慢sql。這時有一定開發經驗的同事會說：“字段區分度不夠，這種字段作單獨索引是沒有意義的”。那么為什么會產生索引失效這種情況呢？索引失效都有哪些情況呢？
下面總結了若干索引失效的情況：
假如表中有個組合索引，idx_start_org_code_start_province_id_trans_type,它的索引順序如下：
start_org_code,
start_province_id,
trans_type
??當我們從第二個索引字段開始查詢時就不會走索引：
??因為索引是BTree結構的，不能跳過第一個索引直接走第二個索引
當我們用主鍵做條件時，走索引了：
??而當id列上面有計算，比如：
??可以看到走了全表掃描
有時候我們在某條sql語句的查詢條件中，需要使用函數，比如：截取某個字段的長度：
??你有沒有發現，在使用該函數之后，該sql語句竟然走了全表掃描，索引失效了
在sql語句中因為字段類型不同，而導致索引失效的問題，很容易遇到，可能是我們日常工作中最容易忽略的問題。
到底怎么回事呢？
我們看下表里的start_org_code字段，它是varchar字符類型的
在sql語句查詢數據時，查詢條件我們可以寫成這樣：
?從上圖中看到，該字段走了索引
但如果在寫sql時，不小心把引號丟了：
??咦，該sql語句居然變成全表掃描了，為什么索引失效了？
答：因為這個索引列是varchar類型，而傳參的類型是int，mysql在比較兩種不同類型的字段時會嘗試把這兩個轉化為同一種類型，再進行比較。這樣就可以理解為在字段上加了函數，根據上面分析，索引列加了函數會索引失效。
比較有意思的是，如果int類型的id字段，在查詢時加了引號條件，卻還可以走索引：
??從圖中看出該sql語句確實走了索引。int類型的參數，不管在查詢時加沒加引號，都能走索引。
這還科學嗎？有沒有王法了？
答：MySQL發現如果是int類型字段作為查詢條件時，它會自動將該字段的傳參進行隱式轉換，把字符串轉換成int類型。
MySQL會把上面列子中的字符串12348，轉換成數字12348，所以仍然能走索引。
事實上，索引列上對字段做任何操作都會導致索引失效，因為mysq認為任何計算或者函數都會改變索引的實際效果，如果繼續使用索引可能會造成結果不準確。
這個相信有點編程經驗的同學都知道,這里就不舉例說明了。但是為什么索引會失效呢？
答：其實很好理解，索引就像字典中的目錄。一般目錄是按字母或者拼音從小到大，從左到右排序，是有順序的。
我們在查目錄時，通常會先從左邊第一個字母進行匹對，如果相同，再匹對左邊第二個字母，如果再相同匹對其他的字母，以此類推。
通過這種方式我們能快速鎖定一個具體的目錄，或者縮小目錄的范圍。
但如果你硬要跟目錄的設計反著來，先從字典目錄右邊匹配第一個字母，這畫面你可以自行腦補一下，你眼中可能只剩下絕望了，哈哈
假如我們現在有這樣一個需求：過濾出表中某兩列值相同的記錄。例如：
??索引失效了吧？驚不驚喜？
答：表里create_time和update_time都建了索引，單獨查詢某一字段時都會走索引。但如果把兩個單獨建了索引的列，用來做列對比時索引會失效。這其實和在索引列上加函數一個原理，MySQL認為索引無法滿足需求。
這幾個有異曲同工之處，就放一起說了。這里就不像上面幾種情況100%不走索引了，而是有時候會走索引，有時候不走索引。到底走不走？成本計算說了算。
查詢優化器是 MySQL 的核心子系統之一，成本計算又是查詢優化器的核心邏輯。
全表掃描成本作為參照物，用于和表的其它訪問方式的成本做對比。任何一種訪問方式，只要成本超過了全表掃描成本，就不會被使用。
基于全表掃描成本的重要地位，要講清楚 MySQL 的成本計算邏輯，從全表掃描成本計算開始是個不錯的選擇。
我們先來看一下Mysql源碼里成本計算的定義：
class Cost_estimate {
private:
// cost of I/O operations
double io_cost;
// cost of CPU operations
double cpu_cost;
// cost of remote operations
double import_cost;
// memory used (bytes)
double mem_cost;
......
}
從上面代碼可以看到，MySQL 成本計算模型定義了四種成本：
全表掃描的成本就只剩 IO 成本、CPU 成本這兩項了
我們先從整體計算公式開始，然后逐步拆解。
全表掃描成本 =io_cost+ 1.1 +cpu_cost+ 1。
io_cost 后面的1.1是硬編碼直接加到 IO 成本上的；cpu_cost 后面的1也是硬編碼的，直接加到 CPU 成本上。代碼里長這樣：
int test_quick_select(...) {
......
double scan_time =
cost_model->row_evaluate_cost(static_cast<double>(records))
+ 1 /* cpu_cost 后面的 + 1 */;
Cost_estimate cost_est = table->file->table_scan_cost();
// io_cost 后面的 + 1.1
cost_est.add_io(1.1);
......
}
關于這兩個硬編碼的值，代碼里沒有注釋為什么要加，不過它們是個固定值，不影響我們理解成本計算邏輯，先忽略它們。
io_cost =cluster_page_count*avg_single_page_cost。
cluster_page_count 是主鍵索引數據頁數量，從表的統計信息中得到，在統計信息小節會介紹。
avg_single_page_cost 是讀取一個數據頁的平均成本，通過計算得到，公式如下：
avg_single_page_cost =pages_in_memory_percent* 0.25 +pages_on_disk_percent* 1.0。
pages_in_memory_percent 是主鍵索引已經加載到 Buffer Pool中的葉結點占所有葉結點的比例，用小數表示（取值范圍 0.0 ~ 1.0），例如：80% 表示為 0.8。數據頁在內存中的比例小節會介紹具體計算邏輯。
pages_on_disk_percent 是主鍵索引在磁盤文件中的葉結點占所有葉結點的比例，通過1 - pages_in_memory_percent計算得到。
0.25是成本常數 memory_block_read_cost的默認值，表示從 Buffer Pool 中的一個數據頁讀取數據的成本。
1.0是成本常數io_block_read_cost的默認值，表示把磁盤文件中的一個數據頁加載到 Buffer Pool 的成本，加上從 Buffer Pool 中的該數據頁讀取數據的成本。
cpu_cost = n_rows * 0.1。
n_rows 是表中記錄的數量，從表的統計信息中得到，在統計信息小節會介紹。
0.1是成本常數row_evaluate_cost的默認值，表示訪問一條記錄的 CPU 成本。
有了上面這些公式，我們通過一個具體例子走一遍全表掃描成本計算的過程。
假設一個表有 15228 條記錄，主鍵索引數據頁的數量為 739，主鍵索引數據頁已經全部加載到 Buffer Pool（pages_in_memory_percent = 1.0），下面我們開始計算過程：
全表掃描成本計算過程中，用到了主鍵索引數據頁數量、表中記錄數量，這兩個數據都來源 InnoDB 的表統計信息。
??clustered_index_size就是主鍵索引數據頁數量，n_rows是表中記錄數量。
avg_single_page_cost =pages_in_memory_percent* 0.25 +pages_on_disk_percent* 1.0。
上面的公式用于計算讀取一個數據頁的平均成本，pages_in_memory_percent 是主鍵索引已經加載到 Buffer Pool 中的葉結點占所有葉結點的比例。
計算代碼如下：
inline double index_pct_cached(const dict_index_t *index) {
// 索引葉結點數量
const ulint n_leaf = index->stat_n_leaf_pages;
......
// 已經加載到 Buffer Pool 中的葉結點數量
const uint64_t n_in_mem =
buf_stat_per_index->get(index_id_t(index->space, index->id));
// 已加載到 Buffer Pool 中的葉結點 [除以] 索引葉結點數量
const double ratio = static_cast<double>(n_in_mem) / n_leaf;
// 取值只能在 0.0 ~ 1.0 之間
return (std::max(std::min(ratio, 1.0), 0.0));
}
InnoDB 在內存中維護了一個哈希表（buf_stat_per_index->m_store），key 是表名，value 是表的主鍵索引已經加載到 Buffer Pool 中的葉子結點數量。
每次從磁盤加載某個表的主鍵索引的一個葉子結點數據頁到 Buffer Pool 中，該表在buf_stat_per_index->m_store中對應的 value 值就加一。
從 Buffer Pool 的 LRU 鏈表淘汰某個表的主鍵索引葉子結點時，該表在buf_stat_per_index->m_store中對應的 value 值就減一。
還有其它場景，buf_stat_per_index->m_store 中的 value 值也會發生變化，不展開了。
memory_block_read_cost 和 io_block_read_cost 這兩個成本常數從系統表mysql.engine_cost中讀取，默認分別是1.0和0.25
以如下sql為例
??列resource_type的搜索條件是 BETWEEN 1 AND 2，形成的掃描區間就是[1,2]。**優化器規定，讀取二級索引的一個掃描區間的IO成本，和讀取一個頁面的IO成本相同，無論它占用多少頁面。(這個是規定，大家記住就好了)因此二級索引頁的IO成本就是1.0。
接下來就是估算二級索引過濾后的記錄數量了，也就是滿足resource_type BETWEEN 1 AND 2的記錄數量。MySQL是這樣預估的：
索引頁的Page Header部分有PAGE_N_RECS屬性記錄了頁中的記錄數，因此不用遍歷每個頁里的記錄
又帶來一個新的問題，如何計算這個區間的頁面數量呢？還記得B+樹的結構嗎？該區間的第0層的葉子節點數雖然很多，難以統計，但是我們可以看它們的父節點啊，這兩個索引頁的目錄項大概率是會在同一個父節點頁中的，在父節點頁中統計區間內有多少頁面就非常容易了，其實就是統計兩個目錄項之間隔了多少個目錄項記錄。
這里，我們假設滿足resource_type BETWEEN 1 AND 2的記錄數是15000個，則二級索引記錄的CPU成本是15000 * 0.2 + 0.01 = 3000.01
接下來就是這15000條記錄回表的IO成本了，MySQL規定，每次回表的IO成本相當于讀取一個頁面的IO成本，二級索引過濾出的記錄數量就是回表的次數。因此，回表的IO成本是15000 * 1.0 = 15000.0。
綜上所述，使用索引的執行成本是：
通過和全表掃描對比，孰優孰劣一目了然。這下是不是清楚多了？
MySQL執行一條查詢語句的流程是這樣的，先找到所有可能用到的索引，然后計算全表掃描的成本，然后分別計算使用不同索引的成本，最終選擇成本最低的方案來執行查詢。這里說的成本其實是由IO成本和CPU成本組成的，對于InnoDB引擎來說，讀取一個頁的IO成本是1.0，讀取一條記錄并檢測是否符合搜索條件的CPU成本是0.2。全表掃描的成本計算非常簡單，根據表的統計數據即可預估出聚簇索引占用的頁面數和表的總記錄數。對于二級索引的輔助查詢，除了過濾二級索引本身的IO成本+CPU成本，還有回表的IO成本+CPU成本，
作者：京東物流 劉海運
來源：京東云開發者社區 自猿其說Tech 轉載請注明來源
索引是 MySQL 數據庫中優化查詢性能的重要工具，通過對查詢條件和表數據的索引，MySQL可以快速定位數據，提高查詢效率。但是，在實際的數據庫開發和維護中，我們經常會遇到一些情況，導致索引失效，從而使得查詢變得非常緩慢，甚至無法使用索引來優化查詢，這會嚴重影響系統的性能。那么，是什么原因導致了索引失效呢？
常見的情況有：
下面我通過實際的例子來具體說說。假設現在我們有一張人物表，建表語句如下：
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`score` int(11) NOT NULL,
`age` int(11) NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;在使用聯合索引進行查詢時，如果聯合索引中的某一個列出現了索引中斷的情況，那么整個聯合索引都會失效，無法繼續使用索引來優化查詢。
例如：對于聯合索引 （name, score），如果條件中如果只有 score，則會導致索引失效。
CREATE INDEX idx_name_score ON person (`name`,`score`);
select * from person where score = 90而下面的情況都會使用索引：
select * from person where name = '31a'
select * from person where score = 90 and name = '31a'
select * from person where name = '31a' and score = 90如果我們在查詢條件中使用了一個不匹配索引的數據類型的值，那么 MySQL 將無法使用該索引來優化查詢，從而導致索引失效。
例如：如果列類型是字符串，那一定要在條件中將數據使用引號引用起來，否則會導致索引失效。
CREATE INDEX idx_name ON person (`name`);
-- 這里 name 是 varchar 類型
select * from person where name = 31但是如果索引是 int 類型，而查詢參數是 varchar 類型，因為字符串隱式轉為數值，不存在歧義，所以會走索引。
CREATE INDEX idx_age ON person (`age`);
-- 這里 age 是 int 類型
select * from person where age = '90'MySQL 為什么不把 31 隱式轉換字符串呢？這個問題在 MySQL 官方文檔中給出了答案。
針對數值1，與字符串'1', '1a', '001', '1 '等多種情況均相等，會存在歧義。不妨看個例子：
我們插入兩條數據：
INSERT INTO test.person (id, name, score, age, create_time) VALUES(1, '00031', 90, 18, '2023-04-15 16:29:39');
INSERT INTO test.person (id, name, score, age, create_time) VALUES(2, '31a', 96, 19, '2023-04-15 16:29:39');然后執行查詢操作：
select * from persion where name = 31;當我們在查詢條件中使用函數操作時，這將導致索引失效。例如：
CREATE INDEX idx_name ON person (`name`);
select * from person where UPPER(name) = '31A';如果我們在查詢條件中使用了前模糊查詢，那么 MySQL 將無法使用 B-Tree 索引的前綴匹配查詢，從而導致索引失效。例如：
CREATE INDEX idx_name ON person (`name`);
select * from person where name LIKE '%a';當我們在查詢條件中使用 OR 連接多個條件時，OR 前后條件都包含索引則走索引，OR 前后有一個不包含索引則索引失效。例如：
CREATE INDEX idx_age ON person (`age`);
select * from person where name = 'John' OR age > 20;如果在建立索引時使用了函數操作，即使使用了索引列，索引也不會生效。例如：
CREATE INDEX idx_name ON person (LOWER(name));
-- 如果使用 LOWER(name) 函數建立索引，那么下面查詢將導致索引失效
select * from person where name = 'John';如果索引列的值區分度不高，MySQL 可能會放棄使用索引，選擇全表掃描，導致索引失效。例如我們創建了下面兩條索引：
CREATE INDEX idx_name ON person (`name`);
CREATE INDEX idx_create_time ON person (`create_time`);然后插入 100000 條數據：
create PROCEDURE `insert_person`()
begin
declare c_id integer default 3;
while c_id <= 100000 do
insert into person values(c_id, concat('name',c_id), c_id + 100, c_id + 10, date_sub(NOW(), interval c_id second));
set c_id = c_id + 1;
end while;
end;
CALL insert_person();接著執行：
explain select * from person where NAME>'name84059' and create_time>'2023-04-15 13:00:00'結果如下：
通過上面的執行計劃可以看到：type=All，說明是全表掃描。