mysql 优化 视图_如何优化MySQL视图的性能

我有一个视图定义如下：

CREATE VIEW vw_data_summary AS

SELECT

a.b_id AS b_id

b.txt_field AS b_txt_field

a.txt_field AS a_txt_field

SUM(a.amount) AS amount

FROM

a JOIN b

WHERE

a.b_id = b.id

GROUP BY

a.b_id, b.txt_field, a.txt_field

表a有650,000条记录,而表b有3,200条记录. a.b_id上有一个索引.这些表格在表格a上以每天1,500条记录的速度增长,在表格b上每天增加50条记录.

当我在视图上运行选择时,大约需要25秒.我担心随着表的大小不断增加,这个查询的性能会迅速降低.

我可以对单个表或视图本身做些什么来提高性能？

说明

在上面的选择上运行说明会产生以下输出：

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE b ALL PRIMARY NULL NULL NULL 3078 Using temporary; Using filesort

1 SIMPLE a ref b_id b_id 4 b.id 134

表定义

CREATE TABLE IF NOT EXISTS `b` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`txt_field` varchar(50) NOT NULL,

PRIMARY KEY (`id`),

KEY `txt_field` (`txt_field`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `a` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`b_id` int(11) NOT NULL,

`txt_field` varchar(255) NOT NULL,

`amount` decimal(10,2) NOT NULL,

PRIMARY KEY (`id`),

KEY `txt_field` (`txt_field`),

KEY `b_id` (`b_id`),

KEY `new_index` (`txt_field`,`b_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

解决方法:

在(b_id,txt_field,amount)上添加索引并使用以下命令创建视图：

CREATE VIEW vw_data_summary_2 AS

SELECT

a.b_id AS b_id

b.txt_field AS b_txt_field

a.txt_field AS a_txt_field

SUM(a.amount) AS amount

FROM

a JOIN b

ON

a.b_id = b.id

GROUP BY

a.b_id, a.txt_field ;

如果你在(b_id)中有一个引用b(id)的外键,你也可以尝试将JOIN更改为LEFT JOIN并比较性能.在这种情况下,视图也可以定义为：

CREATE VIEW vw_data_summary_4 AS

SELECT

a.b_id AS b_id

(SELECT b.txt_field FROM b WHERE a.b_id = b.id) AS b_txt_field

a.txt_field AS a_txt_field

SUM(a.amount) AS amount

FROM

a

GROUP BY

a.b_id, a.txt_field ;

标签：performance,mysql,innodb

来源： https://codeday.me/bug/20190831/1776512.html