本次主要聊一聊關(guān)于函數(shù)到底會(huì)不會(huì)導(dǎo)致索引失效呢？

很多人認(rèn)為，在使用函數(shù)后就無(wú)法使用索引。

這主要是因?yàn)樗饕前凑樟兄档脑柬樞蚪M織和存儲(chǔ)的。當(dāng)對(duì)列應(yīng)用函數(shù)時(shí)（如數(shù)學(xué)運(yùn)算、字符串操作或日期函數(shù)等），函數(shù)會(huì)改變?cè)紨?shù)據(jù)的值或格式，使得數(shù)據(jù)庫(kù)無(wú)法直接定位到這些經(jīng)過(guò)函數(shù)轉(zhuǎn)換后的值。因此，數(shù)據(jù)庫(kù)不得不執(zhí)行全表掃描，以確保能夠評(píng)估所有行上的函數(shù)操作，這導(dǎo)致查詢性能下降。

在 MySQL 8.0 之后，引入了函數(shù)索引，這改變了以往對(duì)函數(shù)使用索引的限制。

MySQL 8.0 引入了功能索引（Functional Indexes）的新特性，這種索引允許在創(chuàng)建時(shí)包含列上的表達(dá)式。這意味著可以對(duì)數(shù)據(jù)進(jìn)行計(jì)算或轉(zhuǎn)換，并針對(duì)結(jié)果建立索引。即使在查詢中使用了函數(shù)操作，也可以利用這些索引來(lái)優(yōu)化查詢性能。

功能索引不是直接在表的列上創(chuàng)建的，而是基于列的某個(gè)表達(dá)式創(chuàng)建的。這個(gè)表達(dá)式可以是簡(jiǎn)單的數(shù)學(xué)運(yùn)算，也可以是字符串函數(shù)、日期函數(shù)等。一旦創(chuàng)建了功能索引，MySQL 在執(zhí)行涉及該表達(dá)式的查詢時(shí)能夠使用這個(gè)索引，從而提升查詢效率。

使用方式

在 MySQL 8.0 中，您可以創(chuàng)建一個(gè)基于 first_name 和 last_name 合并后的表達(dá)式的功能索引，示例如下：

CREATE INDEX full_name_index ON employees ((CONCAT(first_name, ' ', last_name)));

這個(gè)例子中，我們使用了 CONCAT 函數(shù)將 first_name 和 last_name 合并成一個(gè)全名，并在創(chuàng)建索引時(shí)使用了這個(gè)表達(dá)式 (CONCAT(first_name, ' ', last_name))。這樣一來(lái)，即使在查詢中直接使用全名的合并結(jié)果，MySQL 也能夠利用 full_name_index 索引來(lái)優(yōu)化查詢性能。

SELECT * FROM employees WHERE CONCAT(first_name, ' ', last_name) = 'Pai daxing';

圖片

如上圖所示，執(zhí)行計(jì)劃顯示我們成功利用了 idx_full_name索引！

因此，即使在查詢的 WHERE 子句中使用了 CONCAT 函數(shù)，我們依然能夠充分利用 idx_full_name 函數(shù)索引，從而有效提升了查詢的效率。

注意事項(xiàng)

函數(shù)索引雖然能夠顯著提高涉及索引表達(dá)式的查詢性能，但其創(chuàng)建可能增加數(shù)據(jù)插入、更新和刪除的成本，因?yàn)閿?shù)據(jù)庫(kù)需要維護(hù)更多的索引數(shù)據(jù)。因此，在創(chuàng)建函數(shù)索引時(shí)需要謹(jǐn)慎，不能盲目使用。

使用函數(shù)索引時(shí)，必須仔細(xì)考慮和測(cè)試，以確保性能提升符合預(yù)期。

此外，在創(chuàng)建函數(shù)索引時(shí)，需要確保表達(dá)式是確定的，即對(duì)于給定的輸入值始終產(chǎn)生相同的輸出值。非確定性表達(dá)式不適合用于函數(shù)索引。

常見函數(shù)索引用法

給大家列舉一些常見的函數(shù)索引的使用。

字符串處理

當(dāng)您經(jīng)常需要根據(jù)某個(gè)字符串列的某部分進(jìn)行查詢時(shí)，可以考慮使用函數(shù)索引。例如，如果您希望根據(jù)郵箱地址的域名部分查詢用戶，可以創(chuàng)建如下的函數(shù)索引：

CREATE INDEX idx_email_domain ON users ((SUBSTRING_INDEX(email, '@', -1)));

這樣，當(dāng)您查詢特定域名的郵箱時(shí)（例如 qq 郵箱），可以利用這個(gè)索引：

SELECT * FROM users WHERE SUBSTRING_INDEX(email, '@', -1) = 'qq.com';

日期和時(shí)間處理

對(duì)于涉及日期和時(shí)間處理的查詢，函數(shù)索引也非常有用。假設(shè)您需要頻繁查詢基于訂單日期的年份或月份，可以創(chuàng)建如下的索引：

CREATE INDEX idx_order_year ON orders ((YEAR(order_date)));
CREATE INDEX idx_order_month ON orders ((MONTH(order_date)));

這允許您高效地查詢特定年份或月份的訂單：

SELECT * FROM orders WHERE YEAR(order_date) = 2022;
SELECT * FROM orders WHERE MONTH(order_date) = 12;

數(shù)學(xué)運(yùn)算

如果查詢條件經(jīng)常包含對(duì)數(shù)值列的數(shù)學(xué)運(yùn)算，可以針對(duì)這些運(yùn)算創(chuàng)建函數(shù)索引。例如，如果您希望根據(jù)產(chǎn)品折扣后的價(jià)格進(jìn)行查詢，可以創(chuàng)建如下索引：

CREATE INDEX idx_discounted_price ON products ((price * (1 - discount_rate)));

然后，您可以高效地查詢特定范圍的折扣價(jià)格：

SELECT * FROM products WHERE price * (1 - discount_rate) BETWEEN 50 AND 100;

使用 JSON 函數(shù)

如果在 MySQL 中使用 JSON 數(shù)據(jù)類型，并且需要基于 JSON 屬性進(jìn)行查詢，可以創(chuàng)建基于 JSON 函數(shù)的索引。例如，如果您有一個(gè)存儲(chǔ) JSON 數(shù)據(jù)的列，并且希望根據(jù) JSON 文檔中的某個(gè)鍵進(jìn)行查詢，可以創(chuàng)建如下索引：

CREATE INDEX idx_json_key ON orders ((JSON_UNQUOTE(JSON_EXTRACT(order_info, '$.status'))));

這樣，您可以高效地查詢具有特定狀態(tài)的訂單：

SELECT * FROM orders WHERE JSON_UNQUOTE(JSON_EXTRACT(order_info, '$.status')) = 'shipped';

大小寫不敏感的搜索

如果您需要執(zhí)行大小寫不敏感的字符串搜索，可以創(chuàng)建一個(gè)基于 LOWER() 或 UPPER() 函數(shù)的索引：

CREATE INDEX idx_lower_case_name ON customers ((LOWER(name)));

這樣可以讓您執(zhí)行大小寫不敏感的搜索，同時(shí)不影響性能：

SELECT * FROM customers WHERE LOWER(name) = LOWER('John Doe');

在使用函數(shù)索引時(shí)，需要權(quán)衡索引的維護(hù)成本和性能提升。雖然函數(shù)索引可以顯著提升特定查詢的性能，但它們也會(huì)增加插入、更新和刪除操作的成本，因?yàn)閿?shù)據(jù)庫(kù)需要維護(hù)更多的索引數(shù)據(jù)。因此，在實(shí)際應(yīng)用中，建議僅對(duì)那些經(jīng)常作為查詢條件的列和表達(dá)式創(chuàng)建函數(shù)索引。