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

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

測(cè)試數(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。 這些索引值對(duì)應(yīng)于ODBC標(biāo)準(zhǔn)。

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

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í)行計(jì)劃的解讀：

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

參數(shù)：

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

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

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.通過(guò)【sql優(yōu)化】讓索引生效

那么問(wèn)題來(lái)了遇到這種查詢(xún)所有數(shù)據(jù)使用函數(shù)不走索引的我們應(yīng)該如何優(yōu)化。通過(guò)以下試驗(yàn)發(fā)現(xiàn)可以攜帶id，id是主鍵的情況下不會(huì)導(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;

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

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

這里進(jìn)行提示以下：MySQL的 IN 運(yùn)算符可以使用索引，但是，有一點(diǎn)需要注意。如果你的IN子句中包含的值很多，那么MySQL可能會(huì)選擇不使用索引，因?yàn)閽呙璐罅康闹悼赡鼙仁褂盟饕?。這個(gè)閾值通常是1000個(gè)值，但這個(gè)值是可配置的。表內(nèi)數(shù)據(jù)太少使用 IN 也不會(huì)使用索引！

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

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

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

2.通過(guò)【虛擬列】讓索引生效

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

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

語(yǔ)法： ALTER TABLE 表名稱(chēng) add column 虛擬列名稱(chēng) 虛擬列類(lèi)型 GENERATED ALWAYS as (表達(dá)式) [VIRTUAL | STORED];

MySQL 在處理 虛擬列存儲(chǔ)問(wèn)題的時(shí)候有兩種方式：

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

下面我們基于 substr(name,1,3) 函數(shù)來(lái)創(chuàng)建一個(gè)虛擬列：

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

對(duì)虛擬列添加索引：

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

這時(shí)候就可以直接通過(guò)虛擬列來(lái)完成查詢(xún)操作了

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

三、總結(jié)

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

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

最新評(píng)論