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

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

測試數(shù)據(jù)如下：

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() ：函數(shù)返回日期的工作日索引值，即星期日為1，星期一為2，星期六為7。 這些索引值對應(yīng)于ODBC標(biāo)準(zhǔn)。

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

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;

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

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

參數(shù)：

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

通過下面案例會發(fā)現(xiàn)，跟上面的案例是一樣的，同樣是 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;

二、索引失效了應(yīng)該怎么處理？

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

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

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;

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

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

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

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

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

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

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

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

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

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

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

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

下面我們基于 substr(name,1,3) 函數(shù)來創(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';

三、總結(jié)

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

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

最新評論