對(duì)索引使用左或者左右模糊匹配

在MySQL中，LIKE 模糊查詢可能會(huì)導(dǎo)致性能問題，特別是當(dāng)使用通配符 % 開頭時(shí)，因?yàn)檫@通常會(huì)導(dǎo)致全表掃描，也就是 like %xx 或者 like %xx% 這兩種方式 (左或者左右模糊匹配的時(shí)候) 都會(huì)造成索引失效。

• 前綴匹配：一般使用LIKE 'prefix%'的形式，這種情況MySQL能夠利用索引

SELECT * FROM users WHERE username LIKE 'seven%';

• 對(duì)于一定需要匹配后綴的情況（即LIKE '%suffix'），可以創(chuàng)建一個(gè)輔助列存儲(chǔ)反轉(zhuǎn)字符串，并基于此列進(jìn)行前綴匹配。

ALTER TABLE users ADD reversed_username VARCHAR(255); 
UPDATE users SET reversed_username = REVERSE(username); 
CREATE INDEX idx_reversed_username ON users(reversed_username);

計(jì)算(使用函數(shù))

因?yàn)樗饕４娴氖撬饕侄蔚脑贾?，而不是?jīng)過計(jì)算后的值，自然就沒辦法走索引了。

函數(shù)計(jì)算或者表達(dá)式計(jì)算都沒辦法走索引

//函數(shù)計(jì)算
select * from t_user where length(name)=6;

//表達(dá)式計(jì)算
select * from t_user where id + 1 = 10;

不過，從 MySQL 8.0 開始，索引特性增加了函數(shù)索引，即可以針對(duì)函數(shù)計(jì)算后的值建立一個(gè)索引，也就是說該索引的值是函數(shù)計(jì)算后的值，所以就可以通過掃描索引來查詢數(shù)據(jù)。

但是在索引范圍之外使用函數(shù)是不影響索引的： 即函數(shù)不在索引范圍（即WHERE或ON條件）使用時(shí)，索引仍然有效。例如，在 SELECT 列表、ORDER BY、GROUP BY等地方使用函數(shù)通常不影響索引的使用。

類型轉(zhuǎn)換

1. 如果索引字段是字符串類型，但是在條件查詢中，輸入的參數(shù)是整型的話，那么這條語(yǔ)句會(huì)走全表掃描。
2. 但是如果索引字段是整型類型，查詢條件中的輸入?yún)?shù)即使字符串，是不會(huì)導(dǎo)致索引失效，還是可以走索引掃描。

原因在于MySQL 在遇到字符串和數(shù)字比較的時(shí)候，會(huì)自動(dòng)把字符串轉(zhuǎn)為數(shù)字，然后再進(jìn)行比較。也就是說，如果索引字段是整型類型，查詢條件中的輸入?yún)?shù)是字符串，會(huì)自動(dòng)轉(zhuǎn)換成整型，所以索引不會(huì)失效。而索引字段是字符串，而輸入的是整型，由于是字符串轉(zhuǎn)數(shù)字，而索引不是整型類型，所以索引失效了。

因此在使用sql語(yǔ)句時(shí)：數(shù)值類型禁止加引號(hào)，字符串類型必須加引號(hào)

聯(lián)合索引非最左匹配

聯(lián)合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優(yōu)先的方式進(jìn)行索引的匹配，否則就會(huì)導(dǎo)致索引失效。

原因是，在聯(lián)合索引的情況下，數(shù)據(jù)是按照索引第一列排序，第一列數(shù)據(jù)相同時(shí)才會(huì)按照第二列排序。

比如，如果創(chuàng)建了一個(gè) (a, b, c) 聯(lián)合索引，如果查詢條件是以下這幾種，就可以匹配上聯(lián)合索引：

• where a=1；
• where a=1 and b=2 and c=3；
• where a=1 and b=2；

因?yàn)橛胁樵儍?yōu)化器，所以 a 字段在 where 子句的順序并不重要。

但是，如果查詢條件是以下這幾種，因?yàn)椴环献钭笃ヅ湓瓌t，所以就無法匹配上聯(lián)合索引，聯(lián)合索引就會(huì)失效:

• where b=2；
• where c=3；
• where b=2 and c=3；

對(duì)于where a = 1 and c = 0 這個(gè)語(yǔ)句，前面的a = 1是會(huì)走索引的，后面的c不走索引。

where條件的順序影響索引使用嗎

在MySQL中，WHERE條件的順序確實(shí)可能影響索引的使用，尤其是在使用復(fù)合索引（多列索引）的情況下。但是，這種影響主要取決于MySQL查詢優(yōu)化器的工作方式，而不是直接由WHERE子句中條件的書寫順序決定的。

• MySQL的查詢優(yōu)化器通常會(huì)嘗試重新排列WHERE條件以最優(yōu)化索引的使用。
• 對(duì)于復(fù)合索引，索引的使用遵循"最左前綴"原則。
• 雖然WHERE條件的順序通常不會(huì)影響索引的使用，但將索引列的條件放在前面可能會(huì)使查詢計(jì)劃更易讀和理解。

不應(yīng)使用 or

在 WHERE 子句中or，如果在 OR 前的條件列是索引列，而在 OR 后的條件列不是索引列，那么索引會(huì)失效。

OR 的含義就是兩個(gè)只要滿足一個(gè)即可，因此只有一個(gè)條件列是索引列是沒有意義的，只要有條件列不是索引列，就會(huì)進(jìn)行全表掃描。

in

盡量使用IN代替OR。但是IN包含的值不應(yīng)過多，應(yīng)少于1000個(gè)。

因?yàn)?IN 通常是走索引的，當(dāng)IN后面的數(shù)據(jù)在數(shù)據(jù)表中超過30%的匹配時(shí)是全表的掃描，不會(huì)走索引

其實(shí)就是 Mysql優(yōu)化器會(huì)根據(jù)當(dāng)前表的情況選擇最優(yōu)解。 Mysql優(yōu)化器認(rèn)為走全表掃描 比 走索引+回表快 那就不會(huì)走索引

范圍查詢阻斷，后續(xù)字段不能走索引

索引

KEY `idx_shopid_created_status` (`a`, `b`, `c`)

SQL語(yǔ)句

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

所謂的停止四配指的是：a 可以用上聯(lián)合索引，但是 b和c 卻不行。因?yàn)?b 需要先經(jīng)過范國(guó)查詢，此時(shí)經(jīng)過飾選得到 c 的數(shù)據(jù)是無序的。比如a為1 和a為2數(shù)據(jù)中的 b和c是無序的，因此無法利用索引查詢。

如果遇到如>=、<=、BETWEEN、前綴like(xx%)的范圍查詢，則在等值處不會(huì)停止匹配。因?yàn)檫@些查詢包會(huì)一個(gè)等值判斷，可以直接定位到某個(gè)數(shù)據(jù)，然后往后掃描可。

相關(guān)原理可以看這篇文章 唯一索引范圍查詢

覆蓋索引優(yōu)化

覆蓋索引是指 SQL 中 查詢的所有字段，在這個(gè)二級(jí)索引 B+Tree 的葉子節(jié)點(diǎn)上都能找得到那些字段，從二級(jí)索引中查詢得到記錄，而不需要通過聚簇索引查詢獲得，就可以避免回表的操作。

asc和desc混用

select * from _t where a=1 order by b desc, c asc

desc 和asc混用時(shí)會(huì)導(dǎo)致索引失效

避免更新索引列值

每當(dāng)索引列的值發(fā)生變化時(shí)，數(shù)據(jù)庫(kù)必須更新相應(yīng)的索引結(jié)構(gòu)，更新索引列值可能導(dǎo)致這些樹結(jié)構(gòu)的重平衡或重新構(gòu)建，增加了額外的計(jì)算和I/O開銷。

不等于、不包含不能用到索引的快速搜索

select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1

在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

not in一定不走索引嗎？

答案是不一定。Mysql優(yōu)化器會(huì)根據(jù)當(dāng)前表的情況選擇最優(yōu)解。

主要在于如果 MySQL 認(rèn)為 全表掃描 比 走索引+回表效率高， 那么他會(huì)選擇全表掃描。

重要SQL必須被索引

update、delete的where條件列、order by、group by、distinct字段、多表join字段（on后面的字段）

例如：select id from table_a where name = 'seven' order by address ; 此時(shí)建立 name + address的聯(lián)合索引比較好(此處name條件必須是 = ,如果是范圍則無效)；如果是order by主鍵，則只需要在name字段建立索引即可，因?yàn)閚ame索引表中是包含主鍵的，也就是所謂了避免了回表操作。

避免使用子查詢

通常情況下，一般建議使用連接查詢代替子查詢，原因如下：

連接查詢（JOIN）	子查詢
在執(zhí)行連接查詢時(shí)，數(shù)據(jù)庫(kù)會(huì)根據(jù)查詢優(yōu)化器的策略將多個(gè)表的數(shù)據(jù)進(jìn)行合并，然后進(jìn)行過濾和選擇。	子查詢要先執(zhí)行內(nèi)部查詢，然后再使用其結(jié)果進(jìn)行外部查詢。嵌套子查詢需要多次掃描數(shù)據(jù)，并且每次子查詢都可能會(huì)觸發(fā)獨(dú)立的掃描操作，這增加了開銷。
數(shù)據(jù)庫(kù)優(yōu)化器在處理連接查詢時(shí)有更多的優(yōu)化手段，如排序合并連接、哈希連接和嵌套循環(huán)連接等。優(yōu)化器可以根據(jù)統(tǒng)計(jì)信息和查詢結(jié)構(gòu)進(jìn)行調(diào)整，選擇最優(yōu)的執(zhí)行計(jì)劃。	子查詢有時(shí)不能充分利用優(yōu)化器的優(yōu)化策略，特別是在嵌套子查詢的情況下，優(yōu)化器可能會(huì)生成次優(yōu)的執(zhí)行計(jì)劃。
由于連接查詢一次性掃描多個(gè)表并進(jìn)行合并，所以可以充分利用數(shù)據(jù)緩存，減少I/O操作。	子查詢可能會(huì)導(dǎo)致多次掃描相同的數(shù)據(jù)，特別是在嵌套子查詢和相關(guān)子查詢的情況下，子查詢每次執(zhí)行都可能觸發(fā)新的數(shù)據(jù)掃描，增加了I/O開銷。
可以通過JOIN條件有效地過濾數(shù)據(jù)，減少中間結(jié)果的大小。	可能會(huì)產(chǎn)生較大的中間結(jié)果集，需要多次篩選和處理，增加了內(nèi)存和計(jì)算的開銷。

order by的坑

已知存在 custom_id 和 order_date 的聯(lián)合索引。

在對(duì)數(shù)據(jù)進(jìn)行，custom_id 排序的情況下，再對(duì) order_date 進(jìn)行排序。

SELECT customer_id from orders order by customer_id, order_date

耗時(shí) 0.669 秒

當(dāng)調(diào)換排序順序，就無法走索引了，此時(shí)針對(duì)custom_id的索引排序就是失效了。

SELECT customer_id from orders order by order_date,customer_id

耗時(shí) 1.645 秒

即order by也需滿足聯(lián)合索引的最左匹配原則

同時(shí)默認(rèn)情況下，索引是升序的，如果需要降序排序，那么索引也會(huì)失效！！！

SELECT` `customer_id ``from` `orders ``order` `by` `customer_id ``desc``, order_date

MySQL查詢 limit 1000,10 和limit 10 速度一樣快嗎？

兩種查詢方式。對(duì)應(yīng) limit offset, size 和 limit size 兩種方式。

而其實(shí) limit size ，相當(dāng)于 limit 0, size。也就是從0開始取size條數(shù)據(jù)。

也就是說，兩種方式的區(qū)別在于offset是否為0。

先來看下limit sql的內(nèi)部執(zhí)行邏輯。

MySQL內(nèi)部分為server層和存儲(chǔ)引擎層。一般情況下存儲(chǔ)引擎都用innodb。

server層有很多模塊，其中需要關(guān)注的是執(zhí)行器是用于跟存儲(chǔ)引擎打交道的組件。

執(zhí)行器可以通過調(diào)用存儲(chǔ)引擎提供的接口，將一行行數(shù)據(jù)取出，當(dāng)這些數(shù)據(jù)完全符合要求（比如滿足其他where條件），則會(huì)放到結(jié)果集中，最后返回給調(diào)用mysql的客戶端。

以主鍵索引的limit執(zhí)行過程為例：

執(zhí)行select * from xxx order by id limit 0, 10;，select后面帶的是星號(hào)，也就是要求獲得行數(shù)據(jù)的所有字段信息。

server層會(huì)調(diào)用innodb的接口，在innodb里的主鍵索引中獲取到第0到10條完整行數(shù)據(jù)，依次返回給server層，并放到server層的結(jié)果集中，返回給客戶端。

把offset搞大點(diǎn)，比如執(zhí)行的是：select * from xxx order by id limit 500000, 10;

server層會(huì)調(diào)用innodb的接口，由于這次的offset=500000，會(huì)在innodb里的主鍵索引中獲取到第0到（500000 + 10）條完整行數(shù)據(jù)，返回給server層之后根據(jù)offset的值挨個(gè)拋棄，最后只留下最后面的size條，也就是10條數(shù)據(jù)，放到server層的結(jié)果集中，返回給客戶端。

可以看出，當(dāng)offset非0時(shí)，server層會(huì)從引擎層獲取到很多無用的數(shù)據(jù)，而獲取的這些無用數(shù)據(jù)都是要耗時(shí)的。

因此，mysql查詢中 limit 1000,10 會(huì)比 limit 10 更慢。原因是 limit 1000,10 會(huì)取出1000+10條數(shù)據(jù)，并拋棄前1000條，這部分耗時(shí)更大。

深分頁(yè)怎么優(yōu)化？

還是以上面的SQL為空：select * from xxx order by id limit 500000, 10;

方法一：延遲關(guān)聯(lián)（子查詢）

從上面的分析可以看出，當(dāng)offset非常大時(shí)，server層會(huì)從引擎層獲取到很多無用的數(shù)據(jù)，而當(dāng)select后面是*號(hào)時(shí)，就需要拷貝完整的行信息，拷貝完整數(shù)據(jù)相比只拷貝行數(shù)據(jù)里的其中一兩個(gè)列字段更耗費(fèi)時(shí)間。

因?yàn)榍懊娴膐ffset條數(shù)據(jù)最后都是不要的，沒有必要拷貝完整字段，所以可以將sql語(yǔ)句修改成：

select * from xxx  where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;

先執(zhí)行子查詢 select id from xxx by id limit 500000, 1, 這個(gè)操作，其實(shí)也是將在innodb中的主鍵索引中獲取到500000+1條數(shù)據(jù)，然后server層會(huì)拋棄前500000條，只保留最后一條數(shù)據(jù)的id。

但不同的地方在于，在返回server層的過程中，只會(huì)拷貝數(shù)據(jù)行內(nèi)的id這一列，而不會(huì)拷貝數(shù)據(jù)行的所有列，當(dāng)數(shù)據(jù)量較大時(shí)，這部分的耗時(shí)還是比較明顯的。

在拿到了上面的id之后，假設(shè)這個(gè)id正好等于500000，那sql就變成了

select * from xxx  where id >=500000 order by id limit 10;

這樣innodb再走一次主鍵索引，通過B+樹快速定位到id=500000的行數(shù)據(jù)，時(shí)間復(fù)雜度是lg(n)，然后向后取10條數(shù)據(jù)。

方法二：根據(jù)id主鍵進(jìn)行排序

記錄上次查詢的最大ID（或其他唯一標(biāo)識(shí)符），并以此為起點(diǎn)進(jìn)行下一次查詢。這種方法需要有連續(xù)的、唯一的列（如自增ID）以用于分頁(yè)。

select * from xxx where id > start_id order by id limit 10;

通過主鍵索引，每次定位到start_id的位置，然后往后遍歷10個(gè)數(shù)據(jù)，這樣不管數(shù)據(jù)多大，查詢性能都較為穩(wěn)定。

大表查詢慢怎么優(yōu)化？

某個(gè)表有近千萬(wàn)數(shù)據(jù)，查詢比較慢，如何優(yōu)化？

當(dāng)MySQL單表記錄數(shù)過大時(shí)，數(shù)據(jù)庫(kù)的性能會(huì)明顯下降，一些常見的優(yōu)化措施如下：

• 合理建立索引。在合適的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來查看是否用了索引還是全表掃描
• 索引優(yōu)化，SQL優(yōu)化。索引要符合最左匹配原則等
• 建立分區(qū)。對(duì)關(guān)鍵字段建立水平分區(qū)，比如時(shí)間字段，若查詢條件往往通過時(shí)間范圍來進(jìn)行查詢，能提升不少性能
• 利用緩存。利用Redis等緩存熱點(diǎn)數(shù)據(jù)，提高查詢效率
• 限定數(shù)據(jù)的范圍。比如：用戶在查詢歷史信息的時(shí)候，可以控制在一個(gè)月的時(shí)間范圍內(nèi)
• 讀寫分離。經(jīng)典的數(shù)據(jù)庫(kù)拆分方案，主庫(kù)負(fù)責(zé)寫，從庫(kù)負(fù)責(zé)讀
• 通過分庫(kù)分表的方式進(jìn)行優(yōu)化，主要有垂直拆分和水平拆分
• 數(shù)據(jù)異構(gòu)到es
• 冷熱數(shù)據(jù)分離。幾個(gè)月之前不常用的數(shù)據(jù)放到冷庫(kù)中，最新的數(shù)據(jù)比較新的數(shù)據(jù)放到熱庫(kù)中
• 升級(jí)數(shù)據(jù)庫(kù)類型，換一種能兼容MySQL的數(shù)據(jù)庫(kù)（OceanBase、TiDB等）

什么時(shí)候索引失效反而提升效率

• 小表查詢： 對(duì)于非常小的表，MySQL可能會(huì)選擇全表掃描（忽略索引），因?yàn)槿頀呙璧拈_銷可能比通過索引逐行查找還要低。在這種情況下，索引失效不會(huì)損害性能，反而簡(jiǎn)化了查詢。
• 讀取大部分或所有行： 當(dāng)一個(gè)查詢返回表中很大比例的行（如 30% 或更多）時(shí)，使用索引查找可能會(huì)耗時(shí)更多，因?yàn)閿?shù)據(jù)庫(kù)必須跳回主數(shù)據(jù)頁(yè)以讀取完整記錄。全表掃描可能更有效，因?yàn)樗梢灾鹦许樞蜃x取數(shù)據(jù)。
• 低選擇性索引： 如果索引列的選擇性非常低，例如一個(gè)布爾型字段，許多行有相同的值，那么依賴索引可能會(huì)產(chǎn)生不必要的開銷。全表掃描可以避免索引的搜索和回表開銷。
• 頻繁更新的表： 對(duì)于包含大量更新操作的表，索引的維護(hù)成本可能相對(duì)較高。尤其是在頻繁更新索引列時(shí)，通過避免使用或減少?gòu)?fù)雜的索引可以減輕寫操作的負(fù)擔(dān)。
• 復(fù)雜查詢的優(yōu)化選擇： 對(duì)于復(fù)雜的多表聯(lián)接查詢，優(yōu)化器有時(shí)可以選擇執(zhí)行計(jì)劃中不使用某個(gè)索引（或部分失效）以提高整體聯(lián)接和計(jì)算效率。
• 數(shù)據(jù)分布與優(yōu)化器誤判： 在某些特定情況下，如果索引導(dǎo)致MySQL錯(cuò)誤地估計(jì)數(shù)據(jù)分布或行數(shù)，手動(dòng)禁用索引或提示優(yōu)化器使用不同策略可能提升性能。