精华内容
下载资源
问答
  • MySQL - 视图 - 超查询
    千次阅读
    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:47
    flash` `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

    8b94a8559cd12f575c906bd4c92b3117.png

    解决方法:利用源表中的索引 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

    b147585aa950f77eb500bd68e88ffb94.png

    展开全文
  • 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 ...

    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> 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...
  • 2015 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> 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数据库: 两个库中有相同结构的两张表,我想将数据整合并显示到前台页面, 用视图查询速度很,有没有办法优化
  • mysql 视图 函数

    2021-03-11 16:43:35
    视图简介: 为什么要使用视图? 我们知道一张表只存一种实体的数据,但现实业务往往是,需要多个表的数据关联呈现的,并且某些固定的列会被频繁的访问,视图可以避免频繁的编写这些关联查询语句; 什么是视图? ...
  • 详细讲解MYSQL视图的作用及优缺点

    千次阅读 2021-04-25 00:25:35
    视图MySQL在5.0.1版本中加入的功能。它可以理解为一个虚表。2.之所以被称为虚表,是因为它只是存储了一个结构,并不存储真实的数据。行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只...
  • 在本教程中,您将了解一个叫作数据库视图的新数据库... 大多数数据库管理系统(包括MySQL)允许您通过具有一些先决条件的数据库视图来更新基础表中的数据。数据库视图是动态的,因为它与物理模式无关。数据库系统将...
  • 一个MySQL视图的优化过程

    千次阅读 2020-12-20 18:48:50
    1.需要优化的sql最近做一个基于.net mvc和MySQL的仓储系统的优化工作,遇到了一个执行特别的SQL语句,经过一番折腾,终于搞定啦,分享一下过程。问题就是下面这个家伙:create or replace view view_task_meter_...
  • <p>mysql 视图查询速度不,但排序和数据数量太了,请问有什么优化方法吗?</p>
  • mysql视图

    2021-02-01 17:31:57
    #概念数据库视图是虚拟表或逻辑表;因为数据库视图与数据库表类似,它由行和列组成,因此可以根据数据库表查询数据;允许通过先决条件的数据库视图来更新基础表中的数据;当基础表的数据发生变化时,视图也反映了...
  • 这个方案可以有效的规避count效率的问题,对于不同实效性要求的数据也可以很容易的设置不同的缓存周期,但面对要求实时准确的数据就无能为力了。 2.要求实时准确的行数 这一类数据的解决思路是,对数据表进行增删...
  • mysql> 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 ALL的视图.例如:CRATE VIEW myView as(SELECT col1, col2, col3FROM tab1)UNION ALL(SELECT col1, col2, col3FROM tab2)这些是大型表,每个表包含数百万行.如果我写:SELECT *FROM myViewLIMIT 1;...
  • 因为编写了一个Python程序,密集的操作了一个Mysql库,之前数据量不大时,没发现很,后来越来越,以为只是数据量大了的原因,但是后来到不能忍受了,查了半天,索引能用的都用上了,执行一次还是要3到4秒,不...
  • Mysql中的视图

    2021-02-05 05:48:44
    1、什么是视图通俗的讲,视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。视图是一种虚拟存在的表或逻辑表,视图并不在数据库中实际存在,行和列数据...
  • 浅谈mysql视图优缺点

    2020-12-07 14:25:28
    大多数数据库管理系统(包括MySQL)允许您通过具有一些先决条件的数据库视图来更新基础表中的数据。数据库视图是动态的,因为它与物理模式无关。数据库系统将数据库视图存储为具有连接的SQL SELECT语句。当表的数据...
  • 定义:视图是从一个或几个基本表导出的表,它与基本表不同,是一个虚表。作用:1.简化操作,不用进行多表查询。2.当不同种类的用用户共享同一个数据库时,非常灵活,(用户以不同的方式看待同一数据.3.视图对重构数据库...
  • mysql-视图及索引简介

    千次阅读 2021-01-20 18:05:19
    一、视图的创建、作用及注意事项1、创建:create view 视图名 as select ...性能:视图查询数据可能会很,特别是如果视图是基于其他视图创建的表依赖关系:每当更改与其相关的表结构时都必须更改视图二、索引的创...
  • Mysql视图和分区表

    2020-08-26 21:11:22
    Mysql视图和分区表 视图 ​ 视图(View)是一个命名的虚表,由一个SQL查询来定义,可以当作表使用。与持久表不同的是,视图种的数据没有实际的物理存储。 作用 ​ 主要用途是被用做一个抽象装置,程序不需要关心基础表...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 31,177
精华内容 12,470
关键字:

mysql视图慢

mysql 订阅