-
2021-01-20 02:26:17
这是一个奇怪的。我试图在MySQL中使用视图(我对MySQL有相当的新意，并且有更多的Sybase和SQL Server的经验)。任何方式这个新项目我们使用MySQL，因为它似乎有良好的性能。然而，为了更简单地查询网络前端，我们决定创建一些视图，一切运行良好，但它们需要永远运行。MySQL - 视图 - 超慢查询
意见非常简单，只需选择语句(这些表确实有几百万行)。例如说这个查询：
SELECT CAST(classifier_results.msgDate as DATE) AS mdate
,classifier_results.objClass AS objClass
,COUNT(classifier_results.objClass) AS obj
,classifier_results.subjClass AS subjClass
,COUNT(classifier_results.subjClass) AS subj
FROM classifier_results
WHERE (classifier_results.msgDate >= (curdate() - 20))
GROUP BY
CAST(classifier_results.msgDate as DATE)
,classifier_results.objClass
,classifier_results.subjClass
ORDER BY classifier_results.msgDate DESC
当作为一个正常选择运行需要大约1.5秒返回结果。
然而，当这个查询放入视图(原样) - 即
CREATE VIEW V1a_sentiment_AI_current AS
SELECT CAST(classifier_results.msgDate as DATE) AS mdate
,classifier_results.objClass AS objClass
,COUNT(classifier_results.objClass) AS obj
,classifier_results.subjClass AS subjClass
,COUNT(classifier_results.subjClass) AS subj
FROM classifier_results
WHERE (classifier_results.msgDate >= (curdate() - 20))
GROUP BY
CAST(classifier_results.msgDate as DATE)
,classifier_results.objClass
,classifier_results.subjClass
ORDER BY classifier_results.msgDate DESC
查询需要大约10倍的时间(22-30秒)。所以我想也许有一些优化或查询缓存，不适用于视图或可能有一些我们已经错过了在MySQL配置设置。但是有什么办法可以加速这个视图，所以它只是这个查询的一个很好的占位符？
运行EXPLAIN在两个查询： 正常的选择给出了：
1，操作简便，classifier_results，ALL，idx_date，，，，594845，使用其中;使用临时;使用文件排序
的视图中选择给出：
1，PRIMARY，ALL，，，，，100，
2，衍生classifier_results，ALL，idx_date，，，，594845，使用哪里;使用临时;使用文件排序
+0
如果在查看和从视图中选择时使用EXPLAIN，你会得到不同的结果吗？ –
+0
已添加到问题中。查询计划看起来是一样的，即时假设eprimary只是从视图返回，因为它是嵌套在某种意义上，没有什么可以指示20秒+额外运行.... –
+1
我认为'DERIVED'意味着它正在使用一个临时表，这是杀害性能 –
更多相关内容 -
mysql 视图查询速度慢
2021-01-18 22:08:47flash` `t` “SELECT * FROM v_stockpooldata_flash where gscode = 'DXJJ' and ymd = 20190123” 语句查询速度太慢：耗时 256.445 s 解决方法：利用源表中的索引 KEY `index_formulaId_period_ymd` 在创建视图时，...展开全文场景：
表 stockpooldata_flash
CREATE TABLE `stockpooldata_flash` (
`id` bigint(15) NOT NULL AUTO_INCREMENT,
`formula_id` int(8) DEFAULT NULL,
`period_type` tinyint(3) DEFAULT NULL,
`gpMarket` int(4) DEFAULT NULL,
`gpcode` varchar(20) DEFAULT NULL,
`ymd` int(11) DEFAULT NULL,
`hms` int(11) DEFAULT NULL,
`f1` decimal(18,4) DEFAULT NULL,
`f2` decimal(18,4) DEFAULT NULL,
`f3` decimal(18,4) DEFAULT NULL,
`f4` decimal(18,4) DEFAULT NULL,
`f5` decimal(18,4) DEFAULT NULL,
`f6` decimal(18,4) DEFAULT NULL,
`f7` decimal(18,4) DEFAULT NULL,
`f8` decimal(18,4) DEFAULT NULL,
`f9` decimal(18,4) DEFAULT NULL,
`f10` decimal(18,4) DEFAULT NULL,
`f11` decimal(18,4) DEFAULT NULL,
`f12` decimal(18,4) DEFAULT NULL,
`f13` decimal(18,4) DEFAULT NULL,
`f14` decimal(18,4) DEFAULT NULL,
`f15` decimal(18,4) DEFAULT NULL,
`f16` decimal(18,4) DEFAULT NULL,
`buyPrice` decimal(18,4) DEFAULT NULL,
`rise_max` decimal(18,4) DEFAULT NULL,
`rise_signalDay` decimal(18,4) DEFAULT NULL,
`high_max` decimal(18,4) DEFAULT NULL,
`yClose_signalDay` decimal(18,4) DEFAULT NULL,
`iTime` bigint(15) DEFAULT NULL,
`MaxRise` decimal(18,4) DEFAULT NULL,
`Rise_NextDay` decimal(18,4) DEFAULT NULL,
`MaxRise_NextDay` decimal(18,4) DEFAULT NULL,
`Rise_FiveDays` decimal(18,4) DEFAULT NULL,
`MaxRise_FiveDays` decimal(18,4) DEFAULT NULL,
`Rise_TenDays` decimal(18,4) DEFAULT NULL,
`MaxRise_TenDays` decimal(18,4) DEFAULT NULL,
`dayNum` int(8) DEFAULT '-1',
`tag` varchar(1024) DEFAULT NULL,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `index_formulaId_gpcode_ymd` (`formula_id`,`period_type`,`ymd`,`gpMarket`,`gpcode`,`f10`) USING BTREE,
KEY `index_formulaId_period_ymd` (`formula_id`,`period_type`,`ymd`) USING BTREE,
KEY `index_formulaId_ymd` (`formula_id`,`ymd`) USING BTREE,
KEY `index_uptime` (`update_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=88197314 DEFAULT CHARSET=utf8;
建立了一个视图 v_stockpooldata_flash
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_stockpooldata_flash` AS select
case formula_id
when '4' then 'DXJJ'
else `t`.`formula_id`
end
AS `gscode`,concat(convert(if((`t`.`gpMarket` = 0),'SZ','SH') using utf8),`t`.`gpcode`) AS `gpcode`,`t`.`ymd` AS `ymd`,
( floor(`t`.`hms` / 10000) * 60 + floor(`t`.`hms` % 10000 / 100) ) AS `hms`,
`t`.`f1` AS `f1`, `t`.`f2` AS `f2`, `t`.`f3` AS `f3`, `t`.`f4` AS `f4`, `t`.`f5` AS `f5`, `t`.`f6` AS `f6`,
case
when formula_id = 4 then ( floor(`t`.`f7` / 10000) * 60 + floor(`t`.`f7` % 10000 / 100) )
else `t`.`f7` end
AS `f7`,
`t`.`f8` AS `f8`, `t`.`f9` AS `f9`, `t`.`f10` AS `f10`, `t`.`f11` AS `f11`, `t`.`f12` AS `f12`, `t`.`f13` AS `f13`, `t`.`f14` AS `f14`, `t`.`f15` AS `f15`, `t`.`f16` AS `f16`, `t`.`rise_max` AS `rise_max`, `t`.`rise_signalDay` AS `rise_signalDay`, `t`.`high_max` AS `high_max`, `t`.`yClose_signalDay` AS `yClose_signalDay`, `t`.`update_time` AS `update_time`
from `stockpooldata_flash` `t`
“SELECT * FROM v_stockpooldata_flash where gscode = 'DXJJ' and ymd = 20190123” 语句查询速度太慢：耗时 256.445 s
解决方法：利用源表中的索引 KEY `index_formulaId_period_ymd`
在创建视图时，select `t`.`formula_id` AS `formula_id`
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_stockpooldata_flash` AS select `t`.`formula_id` AS `formula_id`,
case formula_id
when '4' then 'DXJJ'
else `t`.`formula_id`
end
AS `gscode`,concat(convert(if((`t`.`gpMarket` = 0),'SZ','SH') using utf8),`t`.`gpcode`) AS `gpcode`,`t`.`ymd` AS `ymd`,
( floor(`t`.`hms` / 10000) * 60 + floor(`t`.`hms` % 10000 / 100) ) AS `hms`,
`t`.`f1` AS `f1`, `t`.`f2` AS `f2`, `t`.`f3` AS `f3`, `t`.`f4` AS `f4`, `t`.`f5` AS `f5`, `t`.`f6` AS `f6`,
case
when formula_id = 4 then ( floor(`t`.`f7` / 10000) * 60 + floor(`t`.`f7` % 10000 / 100) )
else `t`.`f7` end
AS `f7`,
`t`.`f8` AS `f8`, `t`.`f9` AS `f9`, `t`.`f10` AS `f10`, `t`.`f11` AS `f11`, `t`.`f12` AS `f12`, `t`.`f13` AS `f13`, `t`.`f14` AS `f14`, `t`.`f15` AS `f15`, `t`.`f16` AS `f16`, `t`.`rise_max` AS `rise_max`, `t`.`rise_signalDay` AS `rise_signalDay`, `t`.`high_max` AS `high_max`, `t`.`yClose_signalDay` AS `yClose_signalDay`, `t`.`update_time` AS `update_time`
from `stockpooldata_flash` `t`
查询语句改为“SELECT * FROM v_stockpooldata_flash where formula_id = 4 and ymd = 20190123” 查询效果如下：耗时 0.962 s
-
MySql视图非常慢。为什么？
2021-07-16 16:50:50Execution time: 600ms 解决方案 As soon as you mention DISTINCT or aggregation functions in a view MySQL selects TEMPTABLE algorithm for this view, and it means it will create a temporary table for ...展开全文My normal query:
SELECT
DISTINCT vt.id as id,
vtt.name as n,
vt.etxid as etx
FROM vt
LEFT JOIN vtt ON
(vtt.locale = "etx"
AND vtt.etxid = vt.etxid)
Execution time: 5ms
My view:
CREATE OR REPLACE VIEW myview AS
SELECT
DISTINCT vt.id as id,
vtt.name as n,
vt.etxid as etx
FROM vt
LEFT JOIN vtt ON
(vtt.locale = "etx"
AND vtt.etxid = vt.etxid)
My view query:
SELECT * from myview;
Execution time: 600ms
解决方案
As soon as you mention DISTINCT or aggregation functions in a view MySQL selects TEMPTABLE algorithm for this view, and it means it will create a temporary table for the view and then apply sorting, grouping, and aggregations to it. See more details here. Also, there are some recommendations here concerning view performance.
-
MySQL视图查询超慢，求解答
2021-01-21 17:00:02mysql> show create table t2\G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`rank1` int(11) DEFAULT NUL...展开全文mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rank1` int(11) DEFAULT NULL,
`rank2` int(11) DEFAULT NULL,
`rank3` int(11) DEFAULT NULL,
`log_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_rank1` (`rank1`),
KEY `idx_log_date` (`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=49140 DEFAULT CHARSET=utf8mb4 \
COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=1 STATS_AUTO_RECALC=0
1 row in set (0.00 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 30940 |
+----------+
1 row in set (0.00 sec)
同时对t2克隆了一张表t3
mysql> create table t3 like t2;
Query OK, 0 rows affected (0.13 sec)
mysql> insert into t3 select * from t2;
Query OK, 30940 rows affected (1.94 sec)
Records: 30940 Duplicates: 0 Warnings: 0
给表t3列rank1和log_date 添加histogram
mysql> analyze table t3 update histogram on rank1,log_date;+--------+-----------+----------+-----------------------------------------------------+| Table | Op | Msg_type | Msg_text |+--------+-----------+----------+-----------------------------------------------------+| ytt.t3 | histogram | status | Histogram statistics created for column 'log_date'. || ytt.t3 | histogram | status | Histogram statistics created for column 'rank1'. |+--------+-----------+----------+-----------------------------------------------------+2 rows in set (0.19 sec)
我们来看看histogram的分布状况
mysql> select json_pretty(histogram) result from information_schema.column_statistics where table_name = 't3' and column_name = 'log_date'\G*************************** 1. row ***************************result: { "buckets": [ [ "2018-04-17", "2018-04-20", 0.01050420168067227, 4 ], ... , [ "2019-04-14", "2019-04-16", 1.0, 3 ] ], "data-type": "date", "null-values": 0.0, "collation-id": 8, "last-updated": "2019-04-17 03:43:01.910185", "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 100}1 row in set (0.03 sec)
MySQL自动为这个字段分配了等高直方图，默认为100个桶。SQL A：
select count(*) from t2/t3 where (rank1 between 1 and 10) and log_date < '2018-09-01';
SQL A的执行结果：
mysql> select count(*) from t2/t3 where (rank1 between 1 and 10) and log_date < '2018-09-01';+----------+| count(*) |+----------+| 2269 |+----------+1 row in set (0.01 sec)
无histogram的执行计划
mysql> explain format=json select count(*) from t2 where (rank1 between 1 and 10) and log_date < '2018-09-01'\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2796.11" }, "table": { "table_name": "t2", "access_type": "range", "possible_keys": [ "idx_rank1", "idx_log_date" ], "key": "idx_rank1", "used_key_parts": [ "rank1" ], "key_length": "5", "rows_examined_per_scan": 6213, "rows_produced_per_join": 3106, "filtered": "50.00", "index_condition": "(`ytt`.`t2`.`rank1` between 1 and 10)", "cost_info": { "read_cost": "2485.46", "eval_cost": "310.65", "prefix_cost": "2796.11", "data_read_per_join": "72K" }, "used_columns": [ "rank1", "log_date" ], "attached_condition": "(`ytt`.`t2`.`log_date` < '2018-09-01')" } }}
有histogram的执行计划
mysql> explain format=json select count(*) from t3 where (rank1 between 1 and 10) and log_date < '2018-09-01'\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "0.71" }, "table": { "table_name": "t3", "access_type": "range", "possible_keys": [ "idx_rank1", "idx_log_date" ], "key": "idx_log_date", "used_key_parts": [ "log_date" ], "key_length": "4", "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "index_condition": "(`ytt`.`t3`.`log_date` < '2018-09-01')", "cost_info": { "read_cost": "0.61", "eval_cost": "0.10", "prefix_cost": "0.71", "data_read_per_join": "24" }, "used_columns": [ "rank1", "log_date" ], "attached_condition": "(`ytt`.`t3`.`rank1` between 1 and 10)" } }}1 row in set, 1 warning (0.00 sec)
我们看到两个执行计划的对比，有Histogram的执行计划cost比普通的sql快了好多倍。上面文字可以看起来比较晦涩，贴上两张图，看起来就很简单了。我这里举得例子相对简单，有兴趣的朋友可以更深入学习其他复杂些的例子。
-
mysql视图查询慢解决方法
2019-01-17 10:39:08执行下面语句发现查询数据非常慢，要30多s： ...(其中Vsyslog 是视图）这个查询很慢，一开始搞不懂为什么，后来发现message 是在视图里面经过复杂运算得到的。 原因解释：因为Vsyslog 查出的每... -
mysql如何提高视图查询速度？
2021-01-18 22:08:49最近数据库从5.5.18升级到了5.6.38，出现了一个问题，有个视图同样的数据量执行效率相比老数据库慢了20倍，应该怎么优化？如下是视图sql：SELECT`wz_demand`.`id` AS `id`,`wz_demand`.`operator` AS `operator`,`wz... -
李海翔 - MySQL视图优化
2015-11-27 09:57:322015 Oracle 技术嘉年华（OTN）分会场11李海翔 - MySQL视图优化 -
MySQL视图count速度优化
2021-03-03 21:51:19多次查询，然后合计，得出记录总数。怎么样？？(没测试过....)例如，每次查询的...// 查询批次$count = 1;// 总数据量$total_record = 0;while (true){$sql = 'select count(*) from tb_name limit ' . ($count - ... -
mysql视图优化，多表关联视图，我在查询的时候很慢，该怎么优化？？？？
2021-01-19 20:47:40mysql> show create table t2\G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`rank1` int(11) DEFAULT NUL... -
mysql数据库: 视图查询速度很慢,有没有办法优化
2020-06-16 17:14:17mysql数据库: 两个库中有相同结构的两张表,我想将数据整合并显示到前台页面, 用视图查询速度很慢,有没有办法优化 -
mysql 视图 函数
2021-03-11 16:43:35视图简介： 为什么要使用视图？ 我们知道一张表只存一种实体的数据，但现实业务往往是，需要多个表的数据关联呈现的，并且某些固定的列会被频繁的访问，视图可以避免频繁的编写这些关联查询语句； 什么是视图？ ... -
详细讲解MYSQL视图的作用及优缺点
2021-04-25 00:25:35视图是MySQL在5.0.1版本中加入的功能。它可以理解为一个虚表。2.之所以被称为虚表，是因为它只是存储了一个结构，并不存储真实的数据。行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只... -
MySQL视图简介及优缺点
2021-01-20 22:24:24在本教程中，您将了解一个叫作数据库视图的新数据库... 大多数数据库管理系统(包括MySQL)允许您通过具有一些先决条件的数据库视图来更新基础表中的数据。数据库视图是动态的，因为它与物理模式无关。数据库系统将... -
一个MySQL视图的优化过程
2020-12-20 18:48:501.需要优化的sql最近做一个基于.net mvc和MySQL的仓储系统的优化工作，遇到了一个执行特别慢的SQL语句，经过一番折腾，终于搞定啦，分享一下过程。问题就是下面这个家伙：create or replace view view_task_meter_... -
mysql 视图查询速度不慢，但排序和数据数量太慢了，请问有什么优化方法吗？
2021-02-03 09:42:28<p>mysql 视图查询速度不慢，但排序和数据数量太慢了，请问有什么优化方法吗？</p> -
mysql之视图
2021-02-01 17:31:57#概念数据库视图是虚拟表或逻辑表；因为数据库视图与数据库表类似，它由行和列组成，因此可以根据数据库表查询数据；允许通过先决条件的数据库视图来更新基础表中的数据；当基础表的数据发生变化时，视图也反映了... -
MySQL的count(*)执行慢的解决方案和不同count的比较
2021-01-21 08:53:04这个方案可以有效的规避count效率慢的问题，对于不同实效性要求的数据也可以很容易的设置不同的缓存周期，但面对要求实时准确的数据就无能为力了。 2.要求实时准确的行数 这一类数据的解决思路是，对数据表进行增删... -
查询表很快，查询相应的视图很慢为什么
2021-01-27 06:31:31mysql> show create table t2\G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`rank1` int(11) DEFAULT NUL... -
MYSQL视图查询
2019-03-26 13:48:17最近在优化项目页面响应时间，发现一处sql查询结构简单却非常慢，点进去发现是从视图进行查询的，刚开始不知道为什么，后来查询才明白原因，记录一下。 视图定义在有些时候方便很多，但是有些复杂情况定义就不适合。... -
包含UNION的MySQL视图不能很好地优化…换句话说,慢！
2021-04-20 10:28:28我有一个包含UNION ALL的视图.例如：CRATE VIEW myView as(SELECT col1, col2, col3FROM tab1)UNION ALL(SELECT col1, col2, col3FROM tab2)这些是大型表,每个表包含数百万行.如果我写：SELECT *FROM myViewLIMIT 1;... -
Mysql查询很慢卡在sending data的原因及解决思路讲解
2020-12-16 09:17:51因为编写了一个Python程序，密集的操作了一个Mysql库，之前数据量不大时，没发现很慢，后来越来越慢，以为只是数据量大了的原因，但是后来慢到不能忍受了，查了半天，索引能用的都用上了，执行一次还是要3到4秒，不... -
Mysql中的视图
2021-02-05 05:48:441、什么是视图通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。视图是一种虚拟存在的表或逻辑表，视图并不在数据库中实际存在，行和列数据... -
浅谈mysql视图优缺点
2020-12-07 14:25:28大多数数据库管理系统(包括MySQL)允许您通过具有一些先决条件的数据库视图来更新基础表中的数据。数据库视图是动态的，因为它与物理模式无关。数据库系统将数据库视图存储为具有连接的SQL SELECT语句。当表的数据... -
Mysql视图的作用及其性能分析
2021-01-18 21:02:16定义:视图是从一个或几个基本表导出的表，它与基本表不同，是一个虚表。作用：1.简化操作,不用进行多表查询。2.当不同种类的用用户共享同一个数据库时,非常灵活,(用户以不同的方式看待同一数据.3.视图对重构数据库... -
mysql-视图及索引简介
2021-01-20 18:05:19一、视图的创建、作用及注意事项1、创建：create view 视图名 as select ...性能：视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的表依赖关系：每当更改与其相关的表结构时都必须更改视图二、索引的创... -
Mysql视图和分区表
2020-08-26 21:11:22Mysql视图和分区表 视图 视图(View)是一个命名的虚表，由一个SQL查询来定义，可以当作表使用。与持久表不同的是，视图种的数据没有实际的物理存储。 作用 主要用途是被用做一个抽象装置，程序不需要关心基础表...
mysql
订阅