但是有一天测试做了一个7万多条明细的盘点任务后

1.急需优化的sql

近日做贰个基于.net
mvc和MySQL的积累系统的优化工作,遇到了叁个实践非常慢的SQL语句,经过一番煎熬,终于化解啦,分享一下历程。难题正是下边那些东西:

create or replace view view_task_meter_info
as
select t1.TASK_ID,t1.task_no,t1.BINDBOX_BARCODE as box_barcode,t1.EQUIP_BAR_CODE,t1.METER_STATUS,t1.ENTITY_TYPE as RSLT_CODE,
-- 设备类别
(SELECT name from data_dictionary_info t01 where t01.domain ='设备类别' and t01.code = t3.EQUIP_CATEG) as T_Equip_categ,
-- 类别
(select name from data_dictionary_info t09 where t09.domain = '类型' and t09.code = t3.TYPE_CODE) as 
T_TYPE_CODE,
-- 类型
(select name from data_dictionary_info t09 where t09.domain = '类别' and t09.code = t3.RATED_CURRENT) as T_SORT_CODE,
t3.EQUIP_CATEG,t3.TYPE_CODE,t3.SORT_CODE
from
data_task_asset t1 left join data_meter_info t3 on t1.EQUIP_BAR_CODE=t3.BAR_CODE
union all 
select t1.CHK_TASK_ID as task_id,t1.TASK_NO,t1.BOX_BARCODE,t1.BAR_CODE as Equip_bar_code,'00' as METER_STATUS,t1.RSLT_CODE,
-- 设备类别
(SELECT name from data_dictionary_info t01 where t01.domain ='设备类别' and t01.code = t3.EQUIP_CATEG) as T_Equip_categ,
-- 类别
(select name from data_dictionary_info t09 where t09.domain = '类型' and t09.code = t3.TYPE_CODE) as 
T_TYPE_CODE,
-- 类型
(select name from data_dictionary_info t09 where t09.domain = '类别' and t09.code = t3.RATED_CURRENT) as T_SORT_CODE,
t3.EQUIP_CATEG,t3.TYPE_CODE,t3.SORT_CODE
from data_check_asset_info t1
LEFT JOIN data_meter_info t3 on t1.BAR_CODE = t3.BAR_CODE

解释一下业务:

储存系统业务分为三块:出入库、盘点。data_task_asset是出入库职务资金财产明细,data_check_asset_info是盘点任务明细,data_meter_info是资金档案表。data_task_asset和data_check_asset_info表都使用资金条码(EQUIP_澳门太阳集团城网址 ,BAR_CODE/BAR_CODE)和本钱档案表关联(BA瑞鹰_CODE是档案表主键)。那么些视图的作业意义正是展现出入库、盘点职责的本钱明细(包含档案新闻),同一时候须求把档案音信里面包车型客车豁达代码字段翻译成文字新闻。上边视图中只列出3个字段作为示范,实际上要求翻译的字段有二十二个。

这些视图刚开首并未有感觉慢,可是有一天测验做了叁个7万多条明细的盘点职责后,每趟查询三个职责的精心都要等上十几到几十秒,是在难以忍受。开工吧,先会诊一下。

2.查阅执市价况

上边是这么些视图在询问那一个7万多条明细的盘点任务的试行时间,太可怕了,三十多秒。

澳门太阳集团城网址 1

探问推行安顿

澳门太阳集团城网址 2

澳门太陽城集团登录网址 ,额滴神呀,那可一定特别呀,各个字段的翻译都要查询字典表的1207条记下,贰个记下供给翻译13次,7万条记下,供给查询字典表70万次,每一回搜寻记录一千多条,那一个本来特别啊。

3.在字典表上加索引!!!

传闻查询字典表的sql语句,大家在domain和code上加一道索引

SELECT name from data_dictionary_info t01 where t01.domain ='设备类别' and t01.code = t3.EQUIP_CATEG

澳门太阳集团城网址 3

来看今朝的执市价况

实行时间:
施行时间一晃降到了两三秒,效果显然呀!
澳门太阳集团城网址 4

推行安插:
寻访试行布署你可能就不吃惊啦,营造目录后历次查询字典表,只搜索一条记下。
澳门太阳集团城网址 5

但是有一天测试做了一个7万多条明细的盘点任务后。但是有一天测试做了一个7万多条明细的盘点任务后。4.再加多少个目录

既然索引这么厉害,那就此起彼落加索引呗,能够见见视图在询问出入库和盘点职责明细表时,也是全表查询。我们加个索引看看效果怎么样,作者分别在data_task_asset的task_id和task_no、data_check_asset_info的chk_task_id和task_no上加多了共同索引。上面看看执生势况:

但是有一天测试做了一个7万多条明细的盘点任务后。试行时间:
实施时间好像比刚刚还长了少数,这就不合心意啦。
澳门太阳集团城网址 4

实行安插:
从试行布置来看,查询根本就一向不接纳索引,why?
澳门太阳集团城网址 4

5.mysql视图算法及不利用索引的意况

普遍一些百度文化:

当用户创设视图时,mysql暗中认可使用一种undefine的处清理计算法,就是会自动在统一和一时表内实行抉择

  • 对此ME逍客GE,会将援用视图的语句的文本与视图定义合併起来,使得视图定义的某一部分代表语句的对应部分。

  • 对此TEMPTABLE,视图的结果将被放到有的时候表中,然后使用它施行语句。

  • 但是有一天测试做了一个7万多条明细的盘点任务后。对此UNDEFINED,MySQL将选拔所要使用的算法。即便或许,它偏向于ME途乐GE并不是TEMPTABLE,那是因为MEGL450GE常常更使得,况兼一旦运用了有时表,视图是不行更新。

对于使用ME奥迪Q5GE算法管理的视图,能够选择索引。可是,对于利用有时表算法管理的视图,不可能在其基表上使用索引提供的长处。ME凯雷德GE算法须要视图中的行和基表中的行具有一对一的关系。假设不享有该关系。必须运用有时表代替他。借使视图满含下述结构中的任何一种,将失去一定的关联:

  • 聚合函数(SUM(),MIN(),MAX(),COUNT()等)
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION或UNION ALL

但是有一天测试做了一个7万多条明细的盘点任务后。一般来说的不使用索引的询问

  • 借使MySQL估摸应用索引比全表扫描更加慢,则不选用索引。比如,若是列key均匀布满在1和100之间,下边的询问利用索引就不是很好:select *
    from table_name where key>1 and key<90;

  • 一旦接纳MEMORAV4Y/HEAP表,何况where条件中不接纳“=”进行索引列,那么不会用到目录,head表独有在“=”的口径下才会利用索引

  • 用or分隔断的法规,假使or前的法规中的列有索引,而背后的列未有索引,那么涉及到的目录都不会被用到,比如:select *
    from table_name where key1=’a’ or
    key2=’b’;假设在key1上有索引而在key2上尚未索引,则该查询也不会走索引

  • 复合索引,若是索引列不是复合索引的第一有些,则不使用索引(即不符合最左前缀),比如,复合索引为(key1,key2),则查询select *
    from table_name where key2=’b’;将不会选择索引

  • 若是like是以‘%’起首的,则该列上的目录不会被利用。举个例子select * from
    table_name where key1 like
    ‘%a’;该查询就算key1上存在索引,也不会被应用

  • 一旦列为字符串,则where条件中必须将字符常量值加引号,不然就是该列上设有索引,也不会被使用。举例,select *
    from table_name where
    key1=1;如若key1列保存的是字符串,即便key1上有索引,也不会被运用。

6.干掉union all

为了验证的确是union all影响了目录的施用,大家去掉视图中的union
all,让视图只承担查询盘点职务的绵密及档案新闻,看看效果怎样。

-实行时间:
岁月又比刚刚短了一分钟,不错,不错。
澳门太阳集团城网址 8

-推行安排
从实行安插可以观望,此番运用了明细表的目录。
澳门太阳集团城网址 8

7.存款和储蓄进度采纳

大家看出取到union
all之后,明细表的目录在询问中被运用。固然我们从询问的大运上以为明细表使用索引和不采纳索引没有太大不一致。但那实际上只是数额太少反映不出难点,随着明细表数据的加码,有索引时各样职责寻找的记录数只与精心数量有关;而无索引时,各类职明显细查询是全表搜索。所以,union
all必须去掉。

那正是说难点来啦,程序结构已经主导定型,单个视图必须运用union
all。这里有两种方案:

  • 分手为五个视图,出入库明细二个视图,盘点明细一个视图,在程序中决定使用差别视图;
  • 行使存款和储蓄进程,在存款和储蓄进程中推断查询出入库明细照旧盘点明细。

在Entity Framework中利用存款和储蓄进程还没尝试过,就用存款和储蓄进程啦:

mysql存款和储蓄进度代码:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_task_meter_info`(IN `taskId` DECIMAL(16,0), IN `taskNo` VARCHAR(32), IN `ioFlag` VARCHAR(8))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
if ioFlag = '盘点' then
-- 返回盘点明细
select * from ...
where ...
else
-- 返回出入库明细
select * from ...
where ...
end if;
END

Entity Framework调用存款和储蓄进程(看上去也挺方便的):

var dataListProc =
                DbContext.Database.SqlQuery<view_task_meter_info>(
                    string.Format("CALL `sp_task_meter_info`({0}, '{1}','{2}')",searchModel.task_id,searchModel.task_no,searchModel.task_type)).ToList();

8.疑问

  • 总认为到温馨的翻译代码字段有一点太费时,不知诸位园友是怎么管理这种难点的。

好了,就到此处呀。那篇博客其实早该发出来的,因为部分耽误,前些天总算赶出来啦。笔者以为每一回要写一篇博客前,总感到有为数非常多东西要说,不过比非常多时候赶紧作品都写不通畅啦。动不动就想分条陈说,动不动就想来个难点由来,消除方案。而且只要无法聚焦时间写完,再回首已是食之无味。成了个整日宥在温馨项目、职责圈里的技士啦。那特别!

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图