mysql 虚拟列索引_使用MySQL 5.7虚拟列提高查询效率

原标题：使用MySQL 5.7虚拟列提高查询效率

导读

翻译团队：星耀队@知数堂

团队成员：星耀队-芬达，星耀队-顺子，星耀队-M哥

原文出处：https://www.percona.com/blog/2018/01/29/using-generated-columns-in-mysql-5-7-to-increase-query-performance/

原文作者：Alexander Rubin

在这篇博客中，我们将看看如何使用MySQL 5.7的虚拟列来提高查询性能。

In this blog post, we’ll look at ways you can use MySQL 5.7 generated columns (or virtual columns) to improve query performance.

说明

大约两年前，我发表了一个在MySQL5.7版本上关于虚拟列的文章。从那时开始，它成为MySQL5.7发行版当中，我最喜欢的一个功能点。原因很简单：在虚拟列的帮助下，我们可以创建间接索引(fine-grained indexes)，可以显著提高查询性能。我要告诉你一些技巧，可以潜在地解决那些使用了GROUP BY 和 ORDER BY而慢的报表查询。

About two years ago I published a blog post about Generated (Virtual) Columns in MySQL 5.7. Since then, it’s been one of my favorite features in the MySQL 5.7 release. The reason is simple: with the help of virtual columns, we can create fine-grained indexes that can significantly increase query performance. I’m going to show you some tricks that can potentially fix slow reporting queries with GROUP BY and ORDER BY.

问题

最近我正在协助一位客户，他正挣扎于这个查询上：

Recently I was working with a customer who was struggling with this query:

SELECT CONCAT(verb, ' - ', replace(url,'.xml','')) AS 'API Call', COUNT(*) as 'No. of API Calls', AVG(ExecutionTime) as 'Avg. Execution Time', COUNT(distinct AccountId) as 'No. Of Accounts', COUNT(distinct ParentAccountId) as 'No. Of Parents' FROM ApiLog WHERE ts between '2017-10-01 00:00:00' and '2017-12-31 23:59:59' GROUP BY CONCAT(verb, ' - ', replace(url,'.xml','')) HAVING COUNT(*) >= 1 ;

这个查询运行了一个多小时，并且使用和撑满了整个 tmp目录(需要用到临时文件完成排序)。

The query was running for more than an hour and used all space in the tmp directory (with sort files).

表结构如下:

The table looked like this:

CREATE TABLE `ApiLog` (`Id` int(11) NOT NULL AUTO_INCREMENT,`ts` timestamp DEFAULT CURRENT_TIMESTAMP,`ServerName` varchar(50) NOT NULL default '',`ServerIP` varchar(50) NOT NULL default '',`ClientIP` varchar(50) NOT NULL default '',`ExecutionTime` int(11) NOT NULL default 0,`URL` varchar(3000) NOT NULL COLLATE utf8mb4_unicode_ci NOT NULL,`Verb` varchar(16) NOT NULL,`AccountId` int(11) NOT NULL,`ParentAccountId` int(11) NOT NULL,`QueryString` varchar(3000) NOT NULL,`Request` text NOT NULL,`RequestHeaders` varchar(2000) NOT NULL,`Response` text NOT NULL,`ResponseHeaders` varchar(2000) NOT NULL,`ResponseCode` va