網上很多人說mysql一旦使用函數就不走函數，但是事實真的是如此嗎？我先說明，并不是如此的，本篇文章會通過 DAYOFWEEK() 和 substr() 兩個函數作為條件查詢，看看究竟是否會走索引（其他函數同理），使用函數不走索引的時候又應該如何做sql優(yōu)化，本篇文章重點是基于這兩點進行分析。

一、什么場景下使用函數索引會失效？

測試數據如下：

create_time和name列是都建立了索引的。

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `create_time` datetime NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `create_time`(`create_time`) USING BTREE,
  INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
INSERT INTO `demo` VALUES (1, '2023-04-28 10:41:16', 'zhangsan');
INSERT INTO `demo` VALUES (2, '2023-04-01 10:41:22', 'lisi');

DAYOFWEEK() ：函數返回日期的工作日索引值，即星期日為1，星期一為2，星期六為7。 這些索引值對應于ODBC標準。

通過下面會發(fā)現一個問題，假如是 select * 的情況下是不會走索引的，假如是只返回使用函數的列是會走索引的。

EXPLAIN SELECT * from  demo WHERE dayofweek(create_time) = 6 \G;
EXPLAIN SELECT dayofweek(create_time),create_time from  demo WHERE dayofweek(create_time) = 6 \G;

關于執(zhí)行計劃的解讀：

截取字符串語法： substr(obj，start，length)

參數：

• obj：從哪個內容中截取，可以是數值或字符串。
• start：從哪個字符開始截?。?開始，而不是0開始）
• length：截取幾個字符（空格也算一個字符）。

通過下面案例會發(fā)現，跟上面的案例是一樣的，同樣是 select * 的情況下是不會走索引的。

EXPLAIN SELECT *  from demo WHERE substr(name,1,3) = 'lis'\G;
EXPLAIN SELECT substr(name,1,3),name,id  from demo WHERE substr(name,1,3) = 'lis'\G;

二、索引失效了應該怎么處理？

1.通過【sql優(yōu)化】讓索引生效

那么問題來了遇到這種查詢所有數據使用函數不走索引的我們應該如何優(yōu)化。通過以下試驗發(fā)現可以攜帶id，id是主鍵的情況下不會導致索引失效！

EXPLAIN SELECT substr(name,1,3),name,id,create_time  from demo WHERE substr(name,1,3) = 'lis'\G;
EXPLAIN SELECT substr(name,1,3),name,id  from demo WHERE substr(name,1,3) = 'lis'\G;

通過以下試驗得出結論，假如使用函數作為條件查詢，只能返回條件的那一列跟id主鍵列，一旦返回其他的列就會索引失效！

由此優(yōu)化方案便出來了，假設我們要查name列當中前三個字母是lis的全行數據，然后我們想讓他使用到索引，可以使用嵌套查詢的方案：

這里進行提示以下：MySQL的 IN 運算符可以使用索引，但是，有一點需要注意。如果你的IN子句中包含的值很多，那么MySQL可能會選擇不使用索引，因為掃描大量的值可能比使用索引更快。這個閾值通常是1000個值，但這個值是可配置的。表內數據太少使用 IN 也不會使用索引！

EXPLAIN SELECT * FROM demo WHERE id in (SELECT id  from demo WHERE substr(name,1,3) = 'lis') \G;

如下案例顯示實際上并未使用到索引

上面測試的表當中就兩條數據所以顯示的in并沒有使用索引，如下表內共有一萬條數據，然后對主鍵使用in查詢，可以很明顯的看到，是使用了索引的。由此可證明in是會使用索引的，只不過mysql會根據權衡利弊到底使用索引快還是不使用索引快。

2.通過【虛擬列】讓索引生效

Mysql 5.7 中推出了一個非常實用的功能 虛擬列 Generated (Virtual) Columns

• InnoDB支持在虛擬生成的列上建立二級索引。不支持其他索引類型（主鍵索引）。在虛擬列上定義的二級索引有時也稱為“虛擬索引”。
• 二級索引可以在一個或多個虛擬列上創(chuàng)建，也可以在虛擬列與常規(guī)列或存儲生成列的組合上創(chuàng)建。包含虛擬列的二級索引可以定義為UNIQUE。
• 當在虛擬列上使用輔助索引時，由于在INSERT和UPDATE操作期間在輔助索引（輔助又叫二級索引）記錄中實現虛擬列值時執(zhí)行計算，因此需要考慮額外的寫成本。即使有額外的寫成本，虛擬列上的二級索引也可能比生成的存儲列更可取，生成的存儲列在集群索引中具體化，從而導致需要更多磁盤空間和內存的更大的表。如果沒有在虛擬列上定義二級索引，則會產生額外的讀取成本，因為每次檢查列的行時都必須計算虛擬列值。

語法： ALTER TABLE 表名稱 add column 虛擬列名稱 虛擬列類型 GENERATED ALWAYS as (表達式) [VIRTUAL | STORED];

MySQL 在處理 虛擬列存儲問題的時候有兩種方式：

• VIRTUAL（默認）：不存儲列值，在讀取表的時候自動計算并返回，不消耗任何存儲，這種存儲方式僅 InnoDB 支持設置索引。
• STORED：在插入或更新時計算存儲列值，存儲的虛擬列需要存儲空間，并且 MyISAM 也可以設置索引。

下面我們基于 substr(name,1,3) 函數來創(chuàng)建一個虛擬列：

ALTER TABLE demo add column virtual_name VARCHAR(5) GENERATED ALWAYS as (substr(name,1,3)) VIRTUAL;

對虛擬列添加索引：

ALTER TABLE `demo`.`demo` 
ADD INDEX `virtual_name`(`virtual_name`) USING BTREE;

這時候就可以直接通過虛擬列來完成查詢操作了

 EXPLAIN SELECT *  from demo WHERE virtual_name = 'lis';

三、總結

假如使用函數作為條件查詢，只能返回條件的那一列跟id主鍵列，一旦返回其他的列就會索引失效！針對于使用函數索引失效問題，可以使用嵌套查詢來解決，也可以使用虛擬列來解決！

到此這篇關于Mysql使用函數后不走索引怎么優(yōu)化的文章就介紹到這了,更多相關Mysql函數不走索引優(yōu)化內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論