有時(shí)候需要對字段上加函數(shù)然后進(jìn)行GROUP BY。使用執(zhí)行分析，發(fā)現(xiàn)出現(xiàn) Using temporary， 分組條件并沒有走索引。因?yàn)閙ysql 5.7 的函數(shù)會(huì)導(dǎo)致索引失效。我們可以通過添加一個(gè)冗余字段來保存函數(shù)的計(jì)算結(jié)果，然后添加索引，這時(shí)候的GROUP BY就會(huì)走索引了。其實(shí)mysql 5.7 提供了一個(gè)新特性：虛擬列 Generated columns，我們可以使用虛擬列來方便的達(dá)到這個(gè)目的。

虛擬列

MySQL的表生成列通常又叫做虛擬列或計(jì)算列。這個(gè)生成列的值是在列定義時(shí)包含了一個(gè)計(jì)算表達(dá)式計(jì)算得到的，有兩種類型的生成列：

Virtual（虛擬）：這個(gè)類型的列會(huì)在讀取表記錄時(shí)自動(dòng)計(jì)算此列的結(jié)果并返回。
Stored（存儲）：這個(gè)類型的列會(huì)在表中插入一條數(shù)據(jù)時(shí)自動(dòng)計(jì)算對應(yīng)的值，并插入到這個(gè)列中，那么這個(gè)列會(huì)作為一個(gè)常規(guī)列存在表中。虛擬生成列有時(shí)候比存儲生成列更有用，因?yàn)樗粫?huì)占用存儲空間。

1、衍生列的定義可以修改，但virtual和stored之間不能相互轉(zhuǎn)換，必要時(shí)需要?jiǎng)h除重建
2、虛擬列字段只讀，不支持 INSRET 和 UPDATE。
3、只能引用本表的非 generated column 字段，不可以引用其它表的字段。
4、使用的表達(dá)式和操作符必須是 Immutable 屬性。
5、支持創(chuàng)建索引。
6、可以將已存在的普通列轉(zhuǎn)化為stored類型的衍生列，但virtual類型不行；同樣的，可以將stored類型的衍生列轉(zhuǎn)化為普通列，但virtual類型的不行。
7、MySQL可以在衍生列上面創(chuàng)建索引。對于stored類型的衍生列，跟普通列創(chuàng)建索引無區(qū)別。
8、對于virtual類型的衍生列，創(chuàng)建索引時(shí)，會(huì)將衍生列值物化到索引鍵里，即把衍生列的值計(jì)算出來，然后存放在索引里。如果衍生列上的索引起到了覆蓋索引的作用，那么衍生列的值將直接從覆蓋索引里讀取，而不再依據(jù)衍生定義去計(jì)算。

9、針對virtual類型的衍生列索引，在insert和update操作時(shí)會(huì)消耗額外的寫負(fù)載，因?yàn)楦卵苌兴饕龝r(shí)需要將衍生列值計(jì)算出來，并物化到索引里。但即使這樣，virtual類型也比stored類型的衍生列好，有索引就避免了每次讀取數(shù)據(jù)行時(shí)都需要進(jìn)行一次衍生計(jì)算，同時(shí)stored類型衍生列實(shí)際存儲數(shù)據(jù)，使得聚簇索引更大更占空間。

10、virtual類型的衍生列索引使用 MVCC日志，避免在事務(wù)rollback或者purge操作時(shí)重新進(jìn)行不必要的衍生計(jì)算。

注意，出于性能的考慮，選擇Virtual 而不是 Stored

一個(gè)使用虛擬列的優(yōu)化案例

表結(jié)構(gòu)如下

CREATE TABLE `iam`.`biz_cloudsign_login`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `business_system_code` int(11) NOT NULL,
  `user_department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `employee_num` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `identity_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `client_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `client_ip` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `random_num` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `cert_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `encrypted_token` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `updated_at` datetime(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 302 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

查詢語句

SELECT
    DATE_FORMAT(v.updated_at,   '%Y-%m-%d'),
    count(DATE_FORMAT(v.updated_at,   '%Y-%m-%d'))
FROM
    biz_cloudsign_login v
    INNER JOIN ( SELECT MAX( id ) 'id' FROM biz_cloudsign_login GROUP BY employee_num,DATE_FORMAT(updated_at, '%Y-%m-%d') ORDER BY NULL ) c ON v.id = c.id
    GROUP BY DATE_FORMAT(v.updated_at,   '%Y-%m-%d') ORDER BY NULL

執(zhí)行分析，出現(xiàn)Using temporary; 并且有兩個(gè)步驟中key為空

image.png

分別給updated_at 、 employee_num,updated_at 加上索引

ALTER TABLE `iam`.`biz_cloudsign_login` 
ADD INDEX `idx_updated_at`(`updated_at`),
ADD INDEX `idx_employee_num_updated_at`(`employee_num`, `updated_at`);

再次進(jìn)行查詢分析看看效果，extra出現(xiàn) using index，key也多出了索引名。但是 Using temporary 仍然存在， GROUP BY仍然沒走索引！其實(shí)也不難理解，因?yàn)檫@個(gè)查詢語句的GROUP BY條件包含了函數(shù)。

image.png

在mysql5.7版本之前可以增加一個(gè)冗余字段，為了不修改代碼，可以使用觸發(fā)器，維護(hù)這個(gè)字段的默認(rèn)值為DATE_FORMAT(updated_at, '%Y-%m-%d') ，然后在這個(gè)字段上group by。
5.7版本則可以使用虛擬列 Generated columns 虛擬列來實(shí)現(xiàn)，添加一個(gè)Virtual 類型的 虛擬列，指定表達(dá)式為date_format(updated_at,'%Y-%m-%d') ：

ALTER TABLE `iam`.`biz_cloudsign_login` 
ADD COLUMN `update_at_date` date GENERATED ALWAYS AS (date_format(`updated_at`,'%Y-%m-%d')) Virtual NULL AFTER `updated_at`;

將之前的索引干掉后重新添加索引

ALTER TABLE `iam`.`biz_cloudsign_login` 
ADD INDEX `idx_updated_at`(`update_at_date`),
ADD INDEX `idx_employee_num_updated_at`(`employee_num`, `update_at_date`);

查詢的sql修改如下

EXPLAIN
SELECT
    update_at_date,
    count(update_at_date)
FROM
    biz_cloudsign_login v force index(idx_updated_at) 
    INNER JOIN ( SELECT MAX( id ) 'id' FROM biz_cloudsign_login force index(idx_employee_num_updated_at) GROUP BY employee_num,update_at_date ORDER BY NULL ) c ON v.id = c.id
    GROUP BY v.update_at_date ORDER BY NULL

執(zhí)行分析，終于 Using temporary消失了

image.png

mysql 8 支持函數(shù)索引，直接使用函數(shù)會(huì)走索引的

這個(gè)有時(shí)間使用mysql8驗(yàn)證下