SQL优化(1)

9/1/2015来源:SQL技巧人气:1523

SQL优化(1)

背景:StoreNotifyMainTask为主表,StoreNotifySubTask为子表,应用几秒钟关联查询一下,根据主、子表的条件查出top 100;

目前主表记录数648W,单表符合条件的记录647W(基本全部符合条件)

子表记录数425W,单表符合条件的记录106W

主表id列与子表maintaskid为逻辑主外键关系

由于子表条件固定,于是创建筛选索引

1 CREATE NONCLUSTERED INDEX [idxw_StoreNotifySubTask_RetryNum_yn_MainTaskId_inc] ON [dbo].[StoreNotifySubTask]2 (3     [RetryNum] ASC,4     [YN] ASC5 )6 INCLUDE ( [MainTaskId])7 WHERE ([RetryNum]<(3) AND [NotifyState]=(0) AND [yn]=(1))
View Code

初始的SQL如下:

 1 SELECT TOP 100 2         sub.Id , 3         sub.SubscriberId , 4         sub.MainTaskId , 5         sub.Pin , 6         sub.BlogPin , 7         sub.SkuId , 8         sub.SkuName , 9         sub.Wpid1 ,10         sub.Wpid2 ,11         sub.Wpid3 ,12         sub.Email ,13         sub.PhoneNo ,14         sub.PRice ,15         sub.SendPrice ,16         sub.RetryNum ,17         sub.AddressId ,18         sub.CreateTime ,19         ISNULL(sub.MessageTag, 0) AS MessageTag ,20         sub.UpdateTime ,21         sub.SendTime ,22         sub.NotifyState ,23         sub.YN ,24         sub.Ext ,25         sub.SkuPicUrl ,26         sub.SubscriberTime27 FROM    StoreNotifySubTask sub WITH ( NOLOCK) 28         INNER JOIN StoreNotifyMainTask main ( NOLOCK ) ON sub.MainTaskId = main.Id29 WHERE   main.TaskState = 230         AND main.YN = 131         AND sub.NotifyState = 032         AND sub.RetryNum < 333         AND sub.YN = 1
View Code

执行计划:子表无法使用筛选索引

QQuk51DR/roSNJYM/qmkrllNfnv+9iyTkokEvYnS1AwJ/n2U1tRk8vJHOVylOujZHIqpJLQrrL57kzfAH4OPyC0fjulR9KpP07V9mhISEhISEhISE4iooTmJxa1OvXiMvUtJ/MzeSKyM7VVTbmo5KZ4etGRPhzB5FFpnCJB/dSi0IscFasx8k6wlG+R0LcBvYiEhISEhITU0EREiQj9RfWSmptSc8tbaUsvHknSkaR6SU0eSXL0oqAn5+vqBVl9HL3oTk3/NTQiQS8iISEhISEhRZIi9heTR/p67+9NHumzk60X85es3LKam1JzU2p+Js8fj7YTKwdZRciOVovrxd2QoBeRkJCQkJCQIklR+otsstUhm+J8vKa/4mYl6EUkJCQkJCSkSFKU/mILpnbpZyMS9CISEhISEhJSJKlqf5Hajaa/4mYlWy82+/UDAAAAYCdQnb9Iu1iBISEhISEhISHtwkQ1+ItOYd9dEgEAAAAAwE6iLn8xbicUAAAAAAA0g9r9xfCqEwnxgNDgpipeQzaSU9a5VdXbqLkzIj2ptgnBCvkBIs2xMTfddNPm5iYb6c53rtkLAAAAAIgQmb8obmm6i/DLNv7xq6BBzyWoF2t4vSLVircYVLCeHlb1onw7ydGLdg1ugej+DH0cAAAAADg0xF+0/113f4YGi+c3i3r6E6Tk3MLIV9iFSkBOi1XpNk5nBFtk4yP/AgjqRRHTEQAAAADiRD9/kdU6IoohVLI0nZr7Iyj+WL0YqjI5VlxVnfc0LSLURGqL/Asgohd9c9j6IRwBAAAAcSL2F1nRw16TSx8E3WUzQx4jOnHJV3WhZT16i/MSPJkeqcQRizV0ryq9WENAaGQkXwARvUjbBaLvAHT9RqNIuyL5kK0AAADagojnL/JaYgIi0YusRKsTjlDj4FsDJ8Y3OLQP/FvVPPTv3dD+cB6kQV+A0JfPl2LR6kXPzMigOoPW1njyIRkBAAC0BdH7iyKKygkOKkXCetFTFdsZ8UqqbTSoFFtJkDAiVyf5mixUPLF1hhIUH9ptzlsSaU7kofgvwUFwfTRbP+v/VaXbQjVoqF7kdB4AAABoNSKevxikJELlApvPESXi9VdVSbWNBpXi/Oir0vhhIj3kdzv0fXJ66Ft/td0Lyq/2CyCiFz3qUGQ8ull6sebWAQAAgJhpFX+Rza9fL9aAr0bhw5YKlV++wS2oF4My+VU17gsgohfZ/HjmL9YwHu0o2np6AgAAAMRA8/1FQREW8hgN04s1lKpKAtagFwXVWA19CIqpSi+K51f1BfCtIZLx6BqI1l+EZAQAANDiNNlf5EjD+vUiX0iJ11NtKbZdkcd0x/iKSE4PxVVaaPH69WKDvgA16MWgNSWtMx6NVS8AAADagmaeH80XZ4LSjSNKmqgXg37kCDh3byPRi1WJyFBhJ1JztYh/AYIi+ee7BJ3+V49eZOdH+t4Syec/AgAAANA6ROYvcowlvhbx6VP1Oi9Cqmqa31VfCeu5GyQNg96YSFjoC/QEcJQZ/xF8exL5F4BTqtr5i5g1CAAAANRAM/3F1qSJUrUtwpsBAAAAdhsRz18EAAAAAAA7DPiLAAAAAACAB/xFAAAAAADAA/4iAAAAAADgAX8RAAAAAADwgL8IAAAAAAB4wF8EAAAAAAA8ovQX3Xsyu7ehDtqxmbMlNWeTZza/hn2h+UU4OeKbTrvLcloXyW8W1fYk6PdSfzd886vafNsdjCNVqqK28xLdX+aNjY3Gdc/DzTff7G7U8+Nu45ZbbkkkEteuXauhlF3QuXawa3PHNKbv3v7U0NCePXvcPa9UKr4BbH6c3HrrrYlE4urVq7UVtMveeuutnBpqbqI2PvShD33wwQfuHxOJhDunzsrdv1PfaqNtsTZuu+22RCLx/vvvV1vELuW+Fim7d+9ey7JEwhIMIgVtojzfxX2dED4+xL9bAsXduiToIgjBsoJPwWnF8yrYskF9Fmyi0VTbDc6vPsJWqMpj9NgjXnxPf4m83R1DDU998803OxLNfS1YpB5sjVht6zuVW265pQal5S7lXHt0W20110Y9be3Zs4ejCPl344Gv9kKLiMjBGpqIEI+CjKHCyFusgdtuu60qvUgulVmD3BTBkZXshQiR+Yucf/IT2x1HkVK+RcRVoDihTYTqyDqbiFkvev5j4cn3vaYA4RtUP3EfKqjpIG3NZnq8K4/4c3JEDMVqD4PmxAcVaU2CHsRz7nZi+xncVRm6HL3oeH6J7arO3SVP/sbGBsc4ZG/Zme4fabuO9FTrG9+y+L4r9ukckee2CcVVl1PQIxDd8rEevehxLllJ6rm+du2a4xdWpfBYReiup0F60dfadAs7z7XtEdrBIsIuSP/V1oST77nrsTBtBea4d47V5ygzNsfJ942n7X6hp56gIhQgB93BjdCLjudn44g5t7bzXFfrFHL0ort1t8JzLEM207Is58LTkK9MdLuPbG0U7fxF96v0zfct4vvWgurxDRYPCC3C1pDYQXpRsCeeXlX14IlgvSjSnEi3bWo+OTrB6EK2Hk9mUJ2CRVoNfld9H7YGUzZIL7LaMdQI9PiFgk346j/fa452bEEEX2D9pmBD9aKnrK+dyT6FI+yqEnmeYPePDRqP5rTo8QVZYUfCXqBb5Lnjq23C05y7iEc4snrOt2OsVvPEOwHsyHU9TQiWrQ2PWcj50bn2aD4Ru9GRmM6nb1lW7fnmOLJPMN6jEdmyjVofzaqBavUie7fpepEjfGtuoh69mGDg5/OfIkgvVtUrT8HW0Ys1xLjfVbXxiTATLii+2npqg2OFBunFatWwr4lIjDeWEBg4FskPEoIe8ccJ870QeTr3U3CeLkI479ZXLwYps1CCyrJSsgbc/mKodvS0FZVerLYqcdz+orv+IDEXJPgECdJ/gk34Sk9ON/juHV/M0Xa96PnzwmrKOpuIELfD51F+QXqRIzGDmvDVi8S4m1XpPxG9SMzsRnfxROT+opuEmBoICvMtwqlHMCC0CFtDIkA5RdVEUP01NxdJx2ruVaLZetG5FRrAqad+f7GNEPQXa9CLvtqLI8Wq1YvkpwjZphuhF1sHEX+xNfViUEM7Qy8GdaA19aJvVfHoRXEHsbYmGoSgv1ibXuRfU/V+YT3+opso5y+KiD9B0VBDVb45ofArEdc04k00US+K98T32rcI/xda568+KIeq1Iuc4dRQeVRt2WYhvopZcDw6aC6jbxG2acHx6EiK8IdoE34Wpme2X3P1YtCAe1Bw6Hi0Z/5igjsezVl9zB81TjC+YFA91dYcNJcxaDyaP6wcyXh0VSPXIuPRQRMNyU+osStaRMaXBZsQGY+2f6zH/EsIjEfzKwltoobxaPFlJYLj0UFzGX2LsE2L6EXfKYnR6sWgsezI/EXHT2JVgofQW5wivk3w8zmIV1VD5eyD+OYHxVfVUJ398QSwPeG/KLZ+37tstb63QjssMmLrq4GClmv45rNLZ3xX1fjWE9SrhiIuWEP76TyvZ9ia/+C+by9I/7k74DtaLRLM1szWwEoxdgGNE9+s+YtVNcd/Ic4jeEZ7OetdgnSep4jvj/U4l+6nCJKwnqcIWu8SJOaC9tPxrHfxLctWVZWj6duujspdPKPAnPUuvnoxdP2KYBNBi108t5x8zo42vrf4i1c4RcSb8Nxy2gqVjOJLmIMWu3gC3AtcEtydcTgrWnxLOTmeVSy+g8juRTDsghjfJTK+pdzFqXHzFwFoIk13+2Kmhi0SdwxtNHbsS1XmYstS8xaJbUErbNMIIqdBe9bsYBo1fxGAplDVni87ht2mj212xl7c7a53beLcizF+WmGPRhA5NeyPuMuBvwgAAAAAAHjAXwQAAAAAADzgLwIAAAAAAB7wFwEAAAAAAI/a/cUEAAAAAADY6RD8RQAAAAAAwAfzFwEAAAAAAA/4iwAAAAAAgAf8RQAAAAAAwAP+IgDRI69R77TRPa51T+jVfvbNmXq52Q8AdhCnX6GhRRq6QEMXqP889c9T3xz1zVq9M2bPtNk9ZXZPGt3jemrM6BrVuka05LCaPKl1HlM6j6kdA3LHgLLvc6deVd7YhWcIgZh54OnSE391ZWjaOvCEUX7/xvfNqlgl01osWRvX8175hfW5oZd+/X7bn43URsBf3IFohqUbpBmWZljqmqWtWWrJ/jTVkv1pKkVTLV7/XDXszxtpxVBWjWY/RxuTXzKHLlLnsNI5oiRH1eSY2jWmJce11ISemtBTk0ZqyuieMrunzZ4Zs2fW7Jsz++bN/vPm4IKZGlflFbx8EBlnXt3Si4MLNLhA/fPUP0e9s1bfrNUzbfZMmd2TRs+EkRrTU6N617CWHNGSJ9XkCbXzmNpxTOkYkPd/4dSPoBdB43n4zDtPTFvpGeuzj5euXtv2fSutb+lFs0JLBp39sfV7X3vxagV6MT7gL+5A+ufMwXmzf97qmzX7ZsyeKaNnyuiZNGwXoXtc6xrTUqNq17DaNawmTyjJE3LnMblzQOo4InUclfYfLuw/XNj7yb5mP0cbk18yBhdp79259Jw6NKum59T0rCpynTyp7B+QXoZeBNEx8KLVP2f1zpi902b3pNE9YaTG9K4xvWtU6xrVkyNa0vEUT6idJ9SOLWdR6RhQ9h+1/cXj0IsgBh54uvTUX10ZmjEPPL5acfmLVKHSurWwam5sUqlMSwaN/sj8RN8LVy3oxfiAv7gD6Z8z0xddjsJ56rvuKPTOXHcUJozucZejMKx1nrAdBcV2FPb+dlezn6ONyS+ZvXPW3nvy1f4Le9e41nFMhl4EEbJcIqVI8ipJK5a8QoVlq3DJyl+yCpes/JKVX7KyF63cRStrp0Urs2hlLpiZC2Z6wcxcsIYWzAOPnn1ZeQ16ETSaG/7iY6sef3H1HWth1XrvKq2atFCkUy+Znzg0ebVyrVld3YXAX9yB9M+Z6UVKL14ffrJnLM1avbNWz4zV43gM43pqTO8a0bqGteRJNXlSdc9Y2vvbyWY/RxuTXzL2H5b23p2r9l/YAxN6xzHpNehFEBsVsipklsm0SDdJN0kzSFsndY2UEslFkop06Mmzr8FfBI3Hmb9412PLnvmLq4Y1/xPztTXa3KSHv1O6a1z96MHRX1u/aWJvdxvwF3cgfTPG0LwxOGf0zxh903rvpN4zofWMa91jWveYlrI/R7c+U2NaalRNjW59dl3/hL8YOca6aZRMvWR4Pk3TcmIOTOidJxX4iyBaTIsWVqiwTJtE88skrdC7Fs2vUGH5Rspforz9eYkKyyQVKbdEuSWSVumhJ8+9DL0IGs/DT7/zxDkrPWN99pGlq5Vt3zerQi++aR55bun2r8wmD+fu+2+F/nOvVTbwnYwP+Is7kMwF07LIqpBlkWWRaacymRYZZCSSm4apGWVVNyXdLOjrWd1Ma+tD2npaXRtS19JqaUgppeEv1oNVsQrLpiezMK3kJuXchJybkPNTSm5KKUwr+RklPyE5MQcm9I4TGI8GEWNVaKlE6hptEi2vkVKiKxVaKpFSIqVISpHkIslFklZJWqXCCsn2xTLlL5G0SoeeOAu9CGLg