本次主要聊一聊關于函數到底會不會導致索引失效呢？

很多人認為，在使用函數后就無法使用索引。

這主要是因為索引是按照列值的原始順序組織和存儲的。當對列應用函數時（如數學運算、字符串操作或日期函數等），函數會改變原始數據的值或格式，使得數據庫無法直接定位到這些經過函數轉換后的值。因此，數據庫不得不執行全表掃描，以確保能夠評估所有行上的函數操作，這導致查詢性能下降。

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

MySQL 8.0 引入了功能索引（Functional Indexes）的新特性，這種索引允許在創建時包含列上的表達式。這意味著可以對數據進行計算或轉換，并針對結果建立索引。即使在查詢中使用了函數操作，也可以利用這些索引來優化查詢性能。

功能索引不是直接在表的列上創建的，而是基于列的某個表達式創建的。這個表達式可以是簡單的數學運算，也可以是字符串函數、日期函數等。一旦創建了功能索引，MySQL 在執行涉及該表達式的查詢時能夠使用這個索引，從而提升查詢效率。

使用方式

在 MySQL 8.0 中，您可以創建一個基于 first_name 和 last_name 合并后的表達式的功能索引，示例如下：

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

這個例子中，我們使用了 CONCAT 函數將 first_name 和 last_name 合并成一個全名，并在創建索引時使用了這個表達式 (CONCAT(first_name, ' ', last_name))。這樣一來，即使在查詢中直接使用全名的合并結果，MySQL 也能夠利用 full_name_index 索引來優化查詢性能。

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

圖片

如上圖所示，執行計劃顯示我們成功利用了 idx_full_name索引！

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

注意事項

函數索引雖然能夠顯著提高涉及索引表達式的查詢性能，但其創建可能增加數據插入、更新和刪除的成本，因為數據庫需要維護更多的索引數據。因此，在創建函數索引時需要謹慎，不能盲目使用。

使用函數索引時，必須仔細考慮和測試，以確保性能提升符合預期。

此外，在創建函數索引時，需要確保表達式是確定的，即對于給定的輸入值始終產生相同的輸出值。非確定性表達式不適合用于函數索引。

常見函數索引用法

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

字符串處理

當您經常需要根據某個字符串列的某部分進行查詢時，可以考慮使用函數索引。例如，如果您希望根據郵箱地址的域名部分查詢用戶，可以創建如下的函數索引：

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

這樣，當您查詢特定域名的郵箱時（例如 qq 郵箱），可以利用這個索引：

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

日期和時間處理

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

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;

數學運算

如果查詢條件經常包含對數值列的數學運算，可以針對這些運算創建函數索引。例如，如果您希望根據產品折扣后的價格進行查詢，可以創建如下索引：

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

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

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

使用 JSON 函數

如果在 MySQL 中使用 JSON 數據類型，并且需要基于 JSON 屬性進行查詢，可以創建基于 JSON 函數的索引。例如，如果您有一個存儲 JSON 數據的列，并且希望根據 JSON 文檔中的某個鍵進行查詢，可以創建如下索引：

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

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

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

大小寫不敏感的搜索

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

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

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

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

在使用函數索引時，需要權衡索引的維護成本和性能提升。雖然函數索引可以顯著提升特定查詢的性能，但它們也會增加插入、更新和刪除操作的成本，因為數據庫需要維護更多的索引數據。因此，在實際應用中，建議僅對那些經常作為查詢條件的列和表達式創建函數索引。