mysql 视图 ROWID MySQL 视图 函数 优化

为了获取客户购买的设备通道，需要编写一个视图，视图用到的数据表共7个，表及其关联关系如下表所示。

实体	名称	缩写	实体关系	关联关系
CUST	客户	C
PU	前端设备	P
CAPABILITY	资源服务目录（业务能力）	CAP
VIDEO_IN_CHANNEL	视频输入通道	V	1、P	1、V.PU_SEQUENCE_ID = P.PU_SEQUENCE_ID
BUYCHANNEL	客户购买通道	BC	1、C 2、P 3、V 4、R	1、BC.CUST_ID = C.CUST_ID 2、BC.PU_EQUIP_CODE = P.PU_EQUIP_CODE 3、BC.PU_EQUIP_CODE = P.PU_EQUIP_CODE    BC.VIDEO_IN_PORT = V.VIDEO_IN_PORT    V.PU_SEQUENCE_ID = P.PU_SEQUENCE_ID 4、BC.REGION_ID = R.REGION_ID
CORP_CUST_CAPABILITY	企业客户业务能力	CCC	1、C	1、CCC.CUST_ID = C.CUST_ID 2、CCC.CAPABILITY_ID = CAP.CAPABILITY
CAPABILITY_ATTR	业务能力属性	CA	1、CCC	1、CA.CUST_CAPABILITY_ID = CCC.CUST_CAPABILITY_ID

在上表关联关系的指引下，很快搞定了视图，代码如下：

CREATE OR REPLACE VIEW QUERRY_DEVICEPORT_VIEW AS
SELECT C.CUST_CODE,
       C.CUST_NAME,
       BC.PU_EQUIP_CODE,
       BC.OPERRIGHT,
       P.DEVICE_NAME,
       COALESCE(P.STATE, 113) AS STATE,
       BC.VIDEO_IN_PORT,
       V.VIDEO_IN_NAME,
       COALESCE(C.ONLINE_STATE, '0') AS ONLINE_STATE,
       C.EXTEND_PASSWORD_FLAG,
       _gb2312'5' AS ORDER_STATE,
       CA.ATTR_VALUE
  FROM CUST                 C,
       BUYCHANNEL           BC,
       PU                   P,
       VIDEOINCHANNEL       V,
       CORP_CUST_CAPABILITY CCC,
       CAPABILITY           CAP,
       CAPABILITY_ATTR      CA
 WHERE C.CUST_TYPE = '0'
   AND C.STATE IN ('0', '1') 
   AND C.PARENT_ID IS NULL
   AND C.CUST_CODE IS NOT NULL  
   AND BC.CUST_ID = C.CUST_ID
   AND P.DEVICE_TYPE <> '04'
   AND P.PU_SEQUENCE_ID = V.PU_SEQUENCE_ID
   AND BC.PU_EQUIP_CODE = P.PU_EQUIP_CODE
   AND BC.VIDEO_IN_PORT = V.VIDEO_IN_PORT
   AND CAP.CAPABILITY_SEQUENCE = '5'
   AND C.CUST_ID = CCC.CUST_ID
   AND CCC.CUST_CAPABILITY_ID = CA.CUST_CAPABILITY_ID
   AND CCC.CAPABILITY_ID = CAP.CAPABILITY_ID
UNION ALL
SELECT C.CUST_CODE,
       C.CUST_NAME,
       BC.PU_EQUIP_CODE,
       BC.OPERRIGHT,
       P.DEVICE_NAME,
       COALESCE(P.STATE, 113) AS STATE,
       BC.VIDEO_IN_PORT,
       V.VIDEO_IN_NAME,
       COALESCE(C.ONLINE_STATE, '0') AS ONLINE_STATE,
       C.EXTEND_PASSWORD_FLAG,
       _gb2312'5' AS ORDER_STATE,
       CA.ATTR_VALUE
  FROM CUST                 C,
       CUST                 PC,
       BUYCHANNEL           BC,
       PU                   P,
       VIDEOINCHANNEL       V,
       CORP_CUST_CAPABILITY CCC,
       CAPABILITY           CAP,
       CAPABILITY_ATTR      CA
 WHERE C.CUST_TYPE = '0'
   AND C.STATE IN ('0', '1')
   AND C.PARENT_ID IS NOT NULL
   AND C.CUST_CODE IS NOT NULL
	AND PC.CUST_TYPE = '0'
   AND PC.STATE IN ('0', '1')   
   AND PC.PARENT_ID IS NULL
   AND PC.CUST_CODE IS NOT NULL
   AND C.PARENT_ID = PC.CUST_ID
   AND BC.CUST_ID = PC.CUST_ID
   AND P.DEVICE_TYPE <> '04'
   AND P.PU_SEQUENCE_ID = V.PU_SEQUENCE_ID
   AND BC.PU_EQUIP_CODE = P.PU_EQUIP_CODE
   AND BC.VIDEO_IN_PORT = V.VIDEO_IN_PORT
   AND CAP.CAPABILITY_SEQUENCE = '5'
   AND PC.CUST_ID = CCC.CUST_ID
   AND CCC.CUST_CAPABILITY_ID = CA.CUST_CAPABILITY_ID
   AND CCC.CAPABILITY_ID = CAP.CAPABILITY_ID

心中那个得意啊，“我得意地笑，我得意地笑”，呵呵~~。赶紧把视图贴到海豚（MySQL的一个客户端，SQLyog）里面，F8刷入数据库。接下来，加设备，加客户，划设备，折腾了半天，终于轮到数据库中的视图小试牛刀了，俺心中那个期待啊~~，CTRL + T，在海豚里面打开一个新的查询窗口，瞬间敲入熟悉的SQL：

SELECT * FROM QUERRY_DEVICEPORT_VIEW

F8，期待结果列表的出现……1秒钟过去了，2秒钟过去了，……，平时欢快活跃的海豚，今天一反常态，变得出奇的安静！终于，结果出来了，81984条记录，用时3312毫秒！！！我不由倒抽一口凉气，如此的查询速度，等到结果出来，恐怕黄花菜都凉了，谁还愿意用呢？

CREATE OR REPLACE 
ALGORITHM = MERGE 
VIEW QUERRY_DEVICEPORT_VIEW AS
SELECT C.CUST_CODE,
       C.CUST_NAME,
       BC.PU_EQUIP_CODE,
       BC.OPERRIGHT,
       P.DEVICE_NAME,
       COALESCE(P.STATE, 113) AS STATE,
       BC.VIDEO_IN_PORT,
       V.VIDEO_IN_NAME,
       COALESCE(C.ONLINE_STATE, '0') AS ONLINE_STATE,
       C.EXTEND_PASSWORD_FLAG,
       _gb2312 '5' AS ORDER_STATE,
       CA.ATTR_VALUE
  FROM CAPABILITY           CAP,
       CORP_CUST_CAPABILITY CCC,
       CAPABILITY_ATTR      CA,
       CUST                 C,
       BUYCHANNEL           BC,
       PU                   P,
       VIDEOINCHANNEL       V
 WHERE CAP.CAPABILITY_SEQUENCE = '5'
   AND C.CUST_TYPE = '0'
   AND C.STATE IN ('0', '1')
   AND C.PARENT_ID IS NULL
   AND C.CUST_CODE IS NOT NULL
   AND P.DEVICE_TYPE <> '04'
   AND CAP.CAPABILITY_ID = CCC.CAPABILITY_ID
   AND CA.CUST_CAPABILITY_ID = CCC.CUST_CAPABILITY_ID
   AND CCC.CUST_ID = C.CUST_ID
   AND C.CUST_ID = BC.CUST_ID
   AND BC.PU_EQUIP_CODE = P.PU_EQUIP_CODE
   AND V.PU_SEQUENCE_ID = P.PU_SEQUENCE_ID
   AND BC.VIDEO_IN_PORT = V.VIDEO_IN_PORT
UNION ALL

SELECT C.CUST_CODE,
       C.CUST_NAME,
       BC.PU_EQUIP_CODE,
       BC.OPERRIGHT,
       P.DEVICE_NAME,
       COALESCE(P.STATE, 113) AS STATE,
       BC.VIDEO_IN_PORT,
       V.VIDEO_IN_NAME,
       COALESCE(C.ONLINE_STATE, '0') AS ONLINE_STATE,
       C.EXTEND_PASSWORD_FLAG,
       _gb2312 '5' AS ORDER_STATE,
       CA.ATTR_VALUE
  FROM CAPABILITY           CAP,
       CORP_CUST_CAPABILITY CCC,
       CAPABILITY_ATTR      CA,
       CUST                 PC,
       CUST                 C,
       BUYCHANNEL           BC,
       PU                   P,
       VIDEOINCHANNEL       V
 WHERE CAP.CAPABILITY_SEQUENCE = '5'
   AND PC.CUST_TYPE = '0'
   AND PC.STATE IN ('0', '1')
   AND PC.PARENT_ID IS NULL
   AND PC.CUST_CODE IS NOT NULL
   AND C.CUST_TYPE = '0'
   AND C.STATE IN ('0', '1')
   AND C.PARENT_ID IS NOT NULL
   AND C.CUST_CODE IS NOT NULL
   AND P.DEVICE_TYPE <> '04'
   AND CAP.CAPABILITY_ID = CCC.CAPABILITY_ID
   AND CCC.CUST_CAPABILITY_ID = CA.CUST_CAPABILITY_ID
   AND CCC.CUST_ID = PC.CUST_ID
   AND PC.CUST_ID = C.PARENT_ID
   AND PC.CUST_ID = BC.CUST_ID
   AND P.PU_EQUIP_CODE = BC.PU_EQUIP_CODE
   AND P.PU_SEQUENCE_ID = V.PU_SEQUENCE_ID
   AND BC.VIDEO_IN_PORT = V.VIDEO_IN_PORT

EXPLAIN、百度、谷歌……，经过一番折腾，性能的调整似乎有了眉目……

首先，对视图中关键的JOIN POINT建立索引，然后，调整表的查询顺序，记录少的表尽量往前提，再然后，把常量条件，能够减少记录的条件统统提到前面……

 

 

 

将修改过的视图刷入数据库，再次SELECT *测试，性能有所提高，时间缩短到1703毫秒。心中那个欣喜啊~~呵呵~无以言表，但是，转眼间，不免又紧张起来——查询的时间仍然大于1秒，这样的性能还是不符合要求的。

优化的初步成功，给我强烈地信心，仿佛问题的终极解决近在咫尺。把视图仔细的看了一遍，直觉告诉我问题的关键在于那个刺眼的UNION ALL操作，根据MySQL操作手册中的叙述，但凡包含UNION ALL的视图，MySQL几乎是无法做优化的，另外，UNION的第二部分SQL中对PC的关联似乎多余，视图可以进一步简化，甚至可以用一个SQL搞定。

基于上面的直觉，我决定乘胜追击，对视图再次进行调整，这里，要感谢特别原来做湖北电信经分项目开发过程中的知识积累，呵呵~

CREATE OR REPLACE
ALGORITHM = MERGE 
VIEW QUERRY_DEVICEPORT_VIEW AS
SELECT C.CUST_CODE,
       C.CUST_NAME,
       BC.PU_EQUIP_CODE,
       BC.OPERRIGHT,
       P.DEVICE_NAME,
       COALESCE(P.STATE, 113) AS STATE,
       BC.VIDEO_IN_PORT,
       V.VIDEO_IN_NAME,
       COALESCE(C.ONLINE_STATE, '0') AS ONLINE_STATE,
       C.EXTEND_PASSWORD_FLAG,
       _gb2312 '5' AS ORDER_STATE,
       CA.ATTR_VALUE
  FROM CAPABILITY           CAP,
       CORP_CUST_CAPABILITY CCC,
       CAPABILITY_ATTR      CA,
       CUST                 C,
       PU                   P,
       VIDEOINCHANNEL       V,
       BUYCHANNEL           BC
 WHERE CAP.CAPABILITY_SEQUENCE = '5'
   AND C.CUST_TYPE = '0'
   AND C.STATE IN ('0', '1')
   AND C.CUST_CODE IS NOT NULL
   AND CAP.CAPABILITY_ID = CCC.CAPABILITY_ID
   AND CA.CUST_CAPABILITY_ID = CCC.CUST_CAPABILITY_ID
   AND (CASE WHEN C.PARENT_ID IS NULL THEN CCC.CUST_ID = C.CUST_ID ELSE
        CCC.CUST_ID = C.PARENT_ID END)
   AND (CASE WHEN C.PARENT_ID IS NULL THEN C.CUST_ID = BC.CUST_ID ELSE
        C.PARENT_ID = BC.CUST_ID END)
   AND P.PU_SEQUENCE_ID = V.PU_SEQUENCE_ID
   AND BC.PU_EQUIP_CODE = P.PU_EQUIP_CODE
   AND BC.VIDEO_IN_PORT = V.VIDEO_IN_PORT

“没有最快，只有更快”，赶紧把视图刷入数据库，回到刚才的查询窗口，按下F8，执行SELECT *查询，

这次优化，主要是根据EXPLAIN来分析SQL语句的执行计划，对关键的条件加上索引。另外，MySQL操作手册中有一段话让我决定把小表放在JOIN的前面：“MySQL resolves all joins using a single-sweep multi-join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.”。再有的就是，能够不用UNION、GROUP　BY的地方，尽量不用。