如何对于几百行SQL语句进行优化?

1.最近在开发中遇到的一些关于几百行SQL语句做查询的问题,需要如何的解决优化SQL这确实是个问题,对于当下的ORM 框架 EF 以及其他的一些的开源的框架例如Drapper ,以及Sqlite-Sugar 等等,对于查询的速度以及性能确实还不错,但是对于几百条的SQL语句那么可能就不行了这些轻量级的框架扛不住。当在写SQL语句需要注意的规则都无法提高速率的时候,个人认为还是需要传统的ADO.NET 参数化的SQL来进行解决问题。

下面是我最近开发当中遇到的一些复杂的SQL的语句如何处理以及优化查询我还在找确切的办法来进行解决。还在进行中,当然对于数据库确实我进行一定的处理,还是有一定的效果的。下面就进入正题吧!

2.花了2天时间写的SQL查询月结算历史的数据

  1  select                                                                                                                                                                                                            
  2              sum(case when indentdate >= '2015-11-28 00:00:00' and                                                                                                         
  3                   indentdate <= '2015-11-28 23:59:59' and                                                                                                             
  4                   indenttype = 0 and indent_step = '00' then 1 else 0 end) totalcount1, --本月总数量                                                                                                              
  5        convert(int,sum(case when indentdate >= '2015-11-28 00:00:00' and                                                                                            
  6                   indentdate <= '2015-11-28 23:59:59' and                                                                                                             
  7                   indenttype = 0 and indent_step = '00' then                                                                                                                                                      
  8               t1.totalpay else 0 end)) totalpay1 ,---本月总金额                                                                                                                                                      
  9        sum(case when indentdate >='2015-11-28 00:00:00' and                                                                                                       
 10                   t1.indentdate <= '2015-11-28 23:59:59' and                                                                                                          
 11                   t2.modifieddate >= '2015-11-28 00:00:00' and                                                                                                      
 12                   t2.modifieddate <= '2015-11-28 23:59:59' and                                                                                                        
 13                   t1.indentstatus='020' and indenttype = 0 and indent_step = '00' then                                                                                             
 14               1 else 0 end)                                                                                                                                                                                       
 15            + sum(case when   indentdate >='2015-11-28 00:00:00' and                                                                                                  
 16                   indentdate <= '2015-11-28 23:59:59' and t1.indentstatus='050'                                                                              
 17                    and financedate >= '2015-11-28 00:00:00' and                                                                                
 18                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
 19                   indent_step = '00' then                                                                                                                                                                         
 20               1 else 0 end)+sum(case when  indentdate >='2015-11-28 00:00:00' and indentdate <= '2015-11-28 23:59:59' and    financedate>='2015-11-28 00:00:00' and  financedate<='2015-11-28 23:59:59' and indentstatus IN ('111','112')  and  indenttype=1 then 1 else 0 end)  totalcount2,--本月失效数量                                                                                                                                                           
 21        convert(int,sum(case when  indentdate >='2015-11-28 00:00:00'  and                                                                                         
 22                   t1.indentdate <= '2015-11-28 23:59:59' and t2.modifieddate >= '2015-11-28 00:00:00' and               
 23                   t2.modifieddate <= '2015-11-28 23:59:59'   and t1.indentstatus='020'                                                                        
 24                     and indenttype = 0 and                                                                                                                                                    
 25                   indent_step = '00' then                                                                                                                                                                         
 26               t1.totalpay  else 0 end)                                                                                                                                                                               
 27            + sum(case when  indentdate >='2015-11-28 00:00:00' and                                                                                                 
 28                   indentdate <= '2015-11-28 23:59:59'   and t1.indentstatus='050'                                                                              
 29                     and financedate >= '2015-11-28 00:00:00' and                                                                                
 30                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
 31                   indent_step = '00' then                                                                                                                                                                         
 32               t1.totalpay  else 0 end))-sum(case when indentdate >='2015-11-28 00:00:00' and indentdate <= '2015-11-28 23:59:59' and financedate>='2015-11-28 00:00:00' and t1.financedate<='2015-11-28 23:59:59' and t1.indentstatus IN ('111','112')  and  indenttype=1 then t1.totalpay  else 0 end ) totalpay2, ---本月失效金额                                                                                                                                                   
 33                                                                                                                                                                                                                   
 34        sum(case when t1.indentdate<= '2015-11-28 00:00:00' and t2.modifieddate >= '2015-11-28 00:00:00' and           
 35                   t2.modifieddate <= '2015-11-28 23:59:59' and t1.indentstatus='020'                                                                         
 36                     and indenttype = 0 and                                                                                                                                                    
 37                   indent_step = '00' then                                                                                                                                                                         
 38               1 else 0 end)                                                                                                                                                                                       
 39            + sum(case when                                                                                                                                                                                        
 40                   indentdate <= '2015-11-28 00:00:00' and t1.indentstatus='050'                                                                            
 41                    and financedate >= '2015-11-28 00:00:00' and                                                                                
 42                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
 43                   indent_step = '00' then                                                                                                                                                                         
 44               1                                                                                                                                                                                                   
 45              else                                                                                                                                                                                                 
 46               0      
 47               --------以上                                                                                                                                                                                             
 48            end)+sum(case when   indentdate <= '2015-11-28 00:00:00' and  financedate>='2015-11-28 00:00:00' and  financedate<='2015-11-28 23:59:59' and indentstatus IN ('111','112')  and  indenttype=1 then 1 else 0 end) totalcount3 ,---历史失效数量                                                                                                                                                                      
 49        convert(int,sum(case                                                                                                                                                                                       
 50              when t1.indentdate<= '2015-11-28 00:00:00' and t2.modifieddate >= '2015-11-28 00:00:00' and              
 51                   t2.modifieddate <= '2015-11-28 23:59:59'   and t1.indentstatus='020'                                                                         
 52                     and indenttype = 0 and                                                                                                                                                    
 53                   indent_step = '00' then                                                                                                                                                                         
 54               t1.totalpay                                                                                                                                                                                            
 55              else                                                                                                                                                                                                 
 56               0                                                                                                                                                                                                   
 57            end)+                                                                                                                                                                                                  
 58        sum(case                                                                                                                                                                                                   
 59              when                                                                                                                                                                                                 
 60                   indentdate <= '2015-11-28 00:00:00'  and t1.indentstatus='050'                                                                            
 61                    and financedate >= '2015-11-28 00:00:00' and                                                                                
 62                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
 63                   indent_step = '00' then                                                                                                                                                                         
 64               t1.totalpay                                                                                                                                                                                            
 65              else                                                                                                                                                                                                 
 66               0                                                                                                                                                                                                   
 67            end) -sum(case when   indentdate <= '2015-11-28 00:00:00' and financedate>='2015-11-28 00:00:00' and  financedate<='2015-11-28 23:59:59' and t1.indentstatus IN ('111','112')  and  indenttype=1 then t1.totalpay  else 0 end )) totalpay3, --历史失效金额                                                                                                                                                                        
 68              sum(case                                                                                                                                                                                       
 69              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
 70                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
 71                   indent_step = '00' then                                                                                                                                                                         
 72               1                                                                                                                                                                                     
 73              else                                                                                                                                                                                                 
 74               0                                                                                                                                                                                                   
 75            end) -                                                                                                                                                                                                 
 76         (sum(case                                                                                                                                                                                                  
 77              when indentdate >= '2015-11-28 00:00:00' and indentdate <= '2015-11-28 23:59:59'and t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
 78                   t2.modifieddate <= '2015-11-28 23:59:59'                                                                           
 79                    and t1.indentstatus='020' and indenttype = 0 and                                                                                                                                                    
 80                   indent_step = '00' then                                                                                                                                                                         
 81               1                                                                                                                                                                                     
 82              else                                                                                                                                                                                                 
 83               0                                                                                                                                                                                                   
 84            end)                                                                                                                                                                                                   
 85            +sum(case                                                                                                                                                                                              
 86              when                                                                                                                                                                                                 
 87                   indentdate >= '2015-11-28 00:00:00' and indentdate <= '2015-11-28 23:59:59'   and                                                                              
 88                      t1.indentstatus='050' and financedate >= '2015-11-28 00:00:00' and                                                                                
 89                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
 90                   indent_step = '00' then                                                                                                                                                                         
 91               1                                                                                                                                                                                      
 92              else                                                                                                                                                                                                 
 93               0                                                                                                                                                                                                   
 94            end)+sum(case                                                                                                                                                                                          
 95              when                                                                                                                                                                                                 
 96                 indentdate >= '2015-11-28 00:00:00' and indentdate <= '2015-11-28 23:59:59'   and t1.indentstatus IN ('111','112')  and financedate >= '2015-11-28 00:00:00' and                                                                            
 97                   financedate <= '2015-11-28 23:59:59' and indenttype = 1  then                                                                                       
 98               1                                                                                                                                                                                     
 99              else                                                                                                                                                                                                 
100               0                                                                                                                                                                                                   
101            end)) totalcount4,---本月应结算订单数=本月总订单数-本月失效订单数                                                                                                                                      
102        convert(int,sum(case                                                                                                                                                                                       
103              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
104                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
105                   indent_step = '00' then                                                                                                                                                                         
106               t1.totalpay                                                                                                                                                                                            
107              else                                                                                                                                                                                                 
108               0                                                                                                                                                                                                   
109            end) -                                                                                                                                                                                                 
110         sum(case                                                                                                                                                                                                  
111              when indentdate >= '2015-11-28 00:00:00' and indentdate <= '2015-11-28 23:59:59'and t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
112                   t2.modifieddate <= '2015-11-28 23:59:59'                                                                           
113                    and t1.indentstatus='020' and indenttype = 0 and                                                                                                                                                    
114                   indent_step = '00' then                                                                                                                                                                         
115               t1.totalpay                                                                                                                                                                                            
116              else                                                                                                                                                                                                 
117               0                                                                                                                                                                                                   
118            end)                                                                                                                                                                                                   
119            -sum(case                                                                                                                                                                                              
120              when                                                                                                                                                                                                 
121                   indentdate >= '2015-11-28 00:00:00' and indentdate <= '2015-11-28 23:59:59'   and                                                                              
122                      t1.indentstatus='050' and financedate >= '2015-11-28 00:00:00' and                                                                                
123                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
124                   indent_step = '00' then                                                                                                                                                                         
125               t1.totalpay                                                                                                                                                                                            
126              else                                                                                                                                                                                                 
127               0                                                                                                                                                                                                   
128            end)+sum(case                                                                                                                                                                                          
129              when                                                                                                                                                                                                 
130                 indentdate >= '2015-11-28 00:00:00' and indentdate <= '2015-11-28 23:59:59'   and t1.indentstatus IN ('111','112')  and financedate >= '2015-11-28 00:00:00' and                                                                            
131                   financedate <= '2015-11-28 23:59:59' and indenttype = 1  then                                                                                       
132               t1.totalpay                                                                                                                                                                                            
133              else                                                                                                                                                                                                 
134               0                                                                                                                                                                                                   
135            end)) totalpay4, --应结算金额                                                                                                                                                                          
136                                                                                                                                                                                                                   
137                 sum(case                                                                                                                                                                                                   
138              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
139                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
140                   indent_step = '00' and status=2 then                                                                                                                                                            
141               1                                                                                                                                                                                                   
142              else                                                                                                                                                                                                 
143               0                                                                                                                                                                                                   
144            end) -                                                                                                                                                                                                 
145         sum(case                                                                                                                                                                                                  
146              when t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
147                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
148                   curstep_id = '2A9B4B' and status=2 and indenttype = 0 and                                                                                                                                       
149                   indent_step = '00' then                                                                                                                                                                         
150                1                                                                                                                                                                                                  
151              else                                                                                                                                                                                                 
152               0                                                                                                                                                                                                   
153            end)                                                                                                                                                                                                   
154            -sum(case                                                                                                                                                                                              
155              when                                                                                                                                                                                                 
156                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
157                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
158                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 0 and                                                                            
159                   indent_step = '00' then                                                                                                                                                                         
160               1                                                                                                                                                                                                   
161              else                                                                                                                                                                                                 
162               0                                                                                                                                                                                                   
163            end)-sum(case                                                                                                                                                                                          
164              when                                                                                                                                                                                                 
165                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
166                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 1  then                                                                          
167               1                                                                                                                                                                                                   
168              else                                                                                                                                                                                                 
169               0                                                                                                                                                                                                   
170            end) totalcount5,                                                                                                                                                                                      
171                                                                                                                                                                                                                   
172         convert(int,sum(case                                                                                                                                                                                      
173              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
174                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
175                   indent_step = '00' and status=2 then                                                                                                                                                            
176               cust_partner_value                                                                                                                                                                                  
177              else                                                                                                                                                                                                 
178               0                                                                                                                                                                                                   
179            end) -                                                                                                                                                                                                 
180         sum(case                                                                                                                                                                                                  
181              when t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
182                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
183                   curstep_id = '2A9B4B' and status=2 and indenttype = 0 and                                                                                                                                       
184                   indent_step = '00' then                                                                                                                                                                         
185                cust_partner_value                                                                                                                                                                                 
186              else                                                                                                                                                                                                 
187               0                                                                                                                                                                                                   
188            end)                                                                                                                                                                                                   
189            -sum(case                                                                                                                                                                                              
190              when                                                                                                                                                                                                 
191                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
192                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
193                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 0 and                                                                            
194                   indent_step = '00' then                                                                                                                                                                         
195               cust_partner_value                                                                                                                                                                                  
196              else                                                                                                                                                                                                 
197               0                                                                                                                                                                                                   
198            end)-sum(case                                                                                                                                                                                          
199              when                                                                                                                                                                                                 
200                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
201                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 1  then                                                                          
202               cust_partner_value                                                                                                                                                                                  
203              else                                                                                                                                                                                                 
204               0                                                                                                                                                                                                   
205            end))  totalpay5,                                                                                                                                                                                      
206        sum(case                                                                                                                                                                                                   
207              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
208                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
209                   indent_step = '00' then                                                                                                                                                                         
210               1                                                                                                                                                                                  
211              else                                                                                                                                                                                                 
212               0                                                                                                                                                                                                   
213            end) -                                                                                                                                                                                                 
214         sum(case                                                                                                                                                                                                  
215              when t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
216                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
217                   curstep_id = '2A9B4B' and indenttype = 0 and                                                                                                                                                    
218                   indent_step = '00' then                                                                                                                                                                         
219                1                                                                                                                                                                                 
220              else                                                                                                                                                                                                 
221               0                                                                                                                                                                                                   
222            end)                                                                                                                                                                                                   
223            -sum(case                                                                                                                                                                                              
224              when                                                                                                                                                                                                 
225                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
226                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
227                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
228                   indent_step = '00' then                                                                                                                                                                         
229               1                                                                                                                                                                                  
230              else                                                                                                                                                                                                 
231               0                                                                                                                                                                                                   
232            end)-sum(case                                                                                                                                                                                          
233              when                                                                                                                                                                                                 
234                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
235                   financedate <= '2015-11-28 23:59:59' and indenttype = 1  then                                                                                       
236               1                                                                                                                                                                                  
237              else                                                                                                                                                                                                 
238               0                                                                                                                                                                                                   
239            end) - (sum(case                                                                                                                                                                                       
240              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
241                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
242                   indent_step = '00' and status=2 then                                                                                                                                                            
243               1                                                                                                                                                                                                   
244              else                                                                                                                                                                                                 
245               0                                                                                                                                                                                                   
246            end) -                                                                                                                                                                                                 
247         sum(case                                                                                                                                                                                                  
248              when t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
249                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
250                   curstep_id = '2A9B4B' and status=2 and indenttype = 0 and                                                                                                                                       
251                   indent_step = '00' then                                                                                                                                                                         
252                1                                                                                                                                                                                                  
253              else                                                                                                                                                                                                 
254               0                                                                                                                                                                                                   
255            end)                                                                                                                                                                                                   
256            -sum(case                                                                                                                                                                                              
257              when                                                                                                                                                                                                 
258                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
259                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
260                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 0 and                                                                            
261                   indent_step = '00' then                                                                                                                                                                         
262               1                                                                                                                                                                                                   
263              else                                                                                                                                                                                                 
264               0                                                                                                                                                                                                   
265            end)-sum(case                                                                                                                                                                                          
266              when                                                                                                                                                                                                 
267                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
268                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 1  then                                                                          
269               1                                                                                                                                                                                                   
270              else                                                                                                                                                                                                 
271               0                                                                                                                                                                                                   
272            end)) totalcount6,                                                                                                                                                                                     
273        convert(int,sum(case                                                                                                                                                                                       
274              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
275                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
276                   indent_step = '00' then                                                                                                                                                                         
277               cust_partner_value                                                                                                                                                                                  
278              else                                                                                                                                                                                                 
279               0                                                                                                                                                                                                   
280            end) -                                                                                                                                                                                                 
281         sum(case                                                                                                                                                                                                  
282              when t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
283                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
284                   curstep_id = '2A9B4B' and indenttype = 0 and                                                                                                                                                    
285                   indent_step = '00' then                                                                                                                                                                         
286                cust_partner_value                                                                                                                                                                                 
287              else                                                                                                                                                                                                 
288               0                                                                                                                                                                                                   
289            end)                                                                                                                                                                                                   
290            -sum(case                                                                                                                                                                                              
291              when                                                                                                                                                                                                 
292                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
293                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
294                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
295                   indent_step = '00' then                                                                                                                                                                         
296               cust_partner_value                                                                                                                                                                                  
297              else                                                                                                                                                                                                 
298               0                                                                                                                                                                                                   
299            end)-sum(case                                                                                                                                                                                          
300              when                                                                                                                                                                                                 
301                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
302                   financedate <= '2015-11-28 23:59:59' and indenttype = 1  then                                                                                       
303               cust_partner_value                                                                                                                                                                                  
304              else                                                                                                                                                                                                 
305               0                                                                                                                                                                                                   
306            end)-(sum(case                                                                                                                                                                                         
307              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
308                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
309                   indent_step = '00' and status=2 then                                                                                                                                                            
310               cust_partner_value                                                                                                                                                                                  
311              else                                                                                                                                                                                                 
312               0                                                                                                                                                                                                   
313            end) -                                                                                                                                                                                                 
314         sum(case                                                                                                                                                                                                  
315              when t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
316                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
317                   curstep_id = '2A9B4B' and status=2 and indenttype = 0 and                                                                                                                                       
318                   indent_step = '00' then                                                                                                                                                                         
319                cust_partner_value                                                                                                                                                                                 
320              else                                                                                                                                                                                                 
321               0                                                                                                                                                                                                   
322            end)                                                                                                                                                                                                   
323            -sum(case                                                                                                                                                                                              
324              when                                                                                                                                                                                                 
325                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
326                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
327                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 0 and                                                                            
328                   indent_step = '00' then                                                                                                                                                                         
329               cust_partner_value                                                                                                                                                                                  
330              else                                                                                                                                                                                                 
331               0                                                                                                                                                                                                   
332            end)-sum(case                                                                                                                                                                                          
333              when                                                                                                                                                                                                 
334                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
335                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 1  then                                                                          
336               cust_partner_value                                                                                                                                                                                  
337              else                                                                                                                                                                                                 
338               0                                                                                                                                                                                                   
339            end))) totalpay6                                                                                                                                                                                       
340  from tabcindent t1 left join workorder2 t2 on t1.relation_id=t2.workorder_guid                
341  --left join customer t4 on  t1.customer_guid = t4.customer_guid                                 
342  left join tvmedia t3 on  t1.cust_media_id=t3.idkey --and t1.cust_partner_guid=t3.partner_guid   
343  where t1.web_flag=1 and  cust_media_id in ('67B3CB84-81F4-87AA-01EB-857EA1474223','CFC5A634-2375-1552-59B4-9A1263DCFCA4','673473E7-8079-68ED-3CB6-9A2256E34A67','E6192562-FCF8-415C-0AC4-9A22A6200706','542CF17F-374E-627D-389B-9A22F09BC4D3','A270E30B-368B-F962-F44F-AA0D76E8865E')                                                                    

2.通过SQL语句进行查询当天结算历史的记录

  1 select                                                                                                                                                                         
  2        convert(int,sum(case when indentdate >= '2015-11-28 00:00:00' and                                                                                            
  3                   indentdate <= '2015-11-28 23:59:59' and                                                                                                             
  4                   indenttype = 0 and indent_step = '00' then                                                                                                                                                      
  5               t1.totalpay else 0 end)) pay1 ,                                                                                                                                         
  6        convert(int,sum(case when t1.indentdate>= '2015-11-28 00:00:00' and                                                                                          
  7                   t1.indentdate <= '2015-11-28 23:59:59' and t2.modifieddate >= '2015-11-28 00:00:00' and               
  8                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
  9                   curstep_id = '2A9B4B' and indenttype = 0 and                                                                                                                                                    
 10                   indent_step = '00' then                                                                                                                                                                         
 11               t1.totalpay  else 0 end)                                                                                                                                                                               
 12            + sum(case when t1.indentdate>= '2015-11-28 00:00:00' and                                                                                                
 13                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
 14                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
 15                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
 16                   indent_step = '00' then                                                                                                                                                                         
 17               t1.totalpay  else 0 end)) +                                                                                                                                               
 18        convert(int,sum(case                                                                                                                                                                                       
 19              when t1.indentdate<= '2015-11-28 00:00:00' and t2.modifieddate >= '2015-11-28 00:00:00' and              
 20                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
 21                   curstep_id = '2A9B4B' and indenttype = 0 and                                                                                                                                                    
 22                   indent_step = '00' then                                                                                                                                                                         
 23               t1.totalpay                                                                                                                                                                                            
 24              else                                                                                                                                                                                                 
 25               0                                                                                                                                                                                                   
 26            end)+                                                                                                                                                                                                  
 27        sum(case                                                                                                                                                                                                   
 28              when                                                                                                                                                                                                 
 29                   indentdate <= '2015-11-28 00:00:00' and workorderstatus = 'FAILED' and                                                                            
 30                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
 31                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
 32                   indent_step = '00' then                                                                                                                                                                         
 33               t1.totalpay                                                                                                                                                                                            
 34              else                                                                                                                                                                                                 
 35               0                                                                                                                                                                                                   
 36            end)-sum(case                                                                                                                                                                                          
 37              when                                                                                                                                                                                                 
 38                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
 39                   financedate <= '2015-11-28 23:59:59' and indenttype = 1  then                                                                                       
 40               t1.totalpay                                                                                                                                                                                            
 41              else                                                                                                                                                                                                 
 42               0                                                                                                                                                                                                   
 43            end)) pay2, --历史失效金额                                                                                                                                                                        
 44        convert(int,sum(case                                                                                                                                                                                       
 45              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
 46                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
 47                   indent_step = '00' then                                                                                                                                                                         
 48               cust_partner_value                                                                                                                                                                                  
 49              else                                                                                                                                                                                                 
 50               0                                                                                                                                                                                                   
 51            end) -                                                                                                                                                                                                 
 52         sum(case                                                                                                                                                                                                  
 53              when t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
 54                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
 55                   curstep_id = '2A9B4B' and indenttype = 0 and                                                                                                                                                    
 56                   indent_step = '00' then                                                                                                                                                                         
 57                cust_partner_value                                                                                                                                                                                 
 58              else                                                                                                                                                                                                 
 59               0                                                                                                                                                                                                   
 60            end)                                                                                                                                                                                                   
 61            -sum(case                                                                                                                                                                                              
 62              when                                                                                                                                                                                                 
 63                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
 64                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
 65                   financedate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                         
 66                   indent_step = '00' then                                                                                                                                                                         
 67               cust_partner_value                                                                                                                                                                                  
 68              else                                                                                                                                                                                                 
 69               0                                                                                                                                                                                                   
 70            end)-sum(case                                                                                                                                                                                          
 71              when                                                                                                                                                                                                 
 72                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
 73                   financedate <= '2015-11-28 23:59:59' and indenttype = 1  then                                                                                       
 74               cust_partner_value                                                                                                                                                                                  
 75              else                                                                                                                                                                                                 
 76               0                                                                                                                                                                                                   
 77            end)-(sum(case                                                                                                                                                                                         
 78              when indentdate >= '2015-11-28 00:00:00' and                                                                                                           
 79                   indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and                                                                                          
 80                   indent_step = '00' and status=2 then                                                                                                                                                            
 81               cust_partner_value                                                                                                                                                                                  
 82              else                                                                                                                                                                                                 
 83               0                                                                                                                                                                                                   
 84            end) -                                                                                                                                                                                                 
 85         sum(case                                                                                                                                                                                                  
 86              when t2.modifieddate>= '2015-11-28 00:00:00' and                                                                                                       
 87                   t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                         
 88                   curstep_id = '2A9B4B' and status=2 and indenttype = 0 and                                                                                                                                       
 89                   indent_step = '00' then                                                                                                                                                                         
 90                cust_partner_value                                                                                                                                                                                 
 91              else                                                                                                                                                                                                 
 92               0                                                                                                                                                                                                   
 93            end)                                                                                                                                                                                                   
 94            -sum(case                                                                                                                                                                                              
 95              when                                                                                                                                                                                                 
 96                   indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and                                                                              
 97                   curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and                                                                                
 98                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 0 and                                                                            
 99                   indent_step = '00' then                                                                                                                                                                         
100               cust_partner_value                                                                                                                                                                                  
101              else                                                                                                                                                                                                 
102               0                                                                                                                                                                                                   
103            end)-sum(case                                                                                                                                                                                          
104              when                                                                                                                                                                                                 
105                   workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and                                                                            
106                   financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 1  then                                                                          
107               cust_partner_value                                                                                                                                                                                  
108              else                                                                                                                                                                                                 
109               0                                                                                                                                                                                                   
110            end))) pay4                                                                                                                                                                                       
111  from tabcindent t1 left join workorder2 t2 on t1.relation_id=t2.workorder_guid                
112  --left join customer t4 on  t1.customer_guid = t4.customer_guid                                 
113  left join tvmedia t3 on  t1.cust_media_id=t3.idkey --and t1.cust_partner_guid=t3.partner_guid   
114  where t1.web_flag=1 and  cust_media_id in ('CFC5A634-2375-1552-59B4-9A1263DCFCA4')                                                                    

 

3.对于这样的复杂的SQL进行大数据量的查询如何优化SQL确实是一个问题,但是我通常是这样做的,虽然没有办法解决根本问题但是,效果还是有的。

4.通常我会建立需要的索引,来增加查询的速度。尽量的避免内嵌的查询因为这真的是影响效率。

5.那么当这些工作都做完后优化的作用不大了,那么我通常会在数据库上面进行动手脚,建立数据库集群进行数据库的读写的分离,然后进行建立数据库快照进行数据库的数据的映射。

6.如果此时的方法不行那么创建分区,以及建立临时表倒是一个不错的选择。

7.尽量的避免表与表之间过多的交差,此时宁愿数据库中的表格的字段冗余一些,也不要太多的交差,JOIN ,LEFT JOIN 真的影响查询的效率。

8.通过上面描述的方法,优化后数据库的表的结构以及数据库几百行的SQL语句查询的效率确实变快了。只不过折磨多的SQL语句只能通过

创建存储过程了。然后在应用ADO.NET 参数化SQL 进行访问了。

9.如果您有好的方法可以随时的交流,毕竟我对于数据库方面的经验还不足。

以上内容,全部都是原创,如需转载,请标明!谢谢!

 

荔枝fm怎么选台 荔枝fm换频道方法

最近因为我是歌手的原因,荔枝fm的下载量颇多。如果你下载荔枝fm后不知道怎么选台换节目的话,就让小编来教教大家吧!这是听电台最重要的一步哦!

荔枝fm怎么选台

1)打开荔枝FM,注意页面中间靠右的旋转按钮,该按钮为调节频道用。(如下图)

荔枝fm怎么选台 荔枝fm换频道方法

2)用手指按住,顺时针旋转为下一频道,逆时针旋转为上一频道。(如下图)

荔枝fm怎么选台 荔枝fm换频道方法

利用多数据源实现分库存储

介绍一些更美观的办法:

spring中有一个AbstractRoutingDataSource的抽象类可以很好的支持多数据源,我们只需要继续它即可。

1
2
3
4
5
6
7
8
9
10
11
12
package com.cnblogs.yjmyzz.utils;
 
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
 
public class RoutingDataSource extends AbstractRoutingDataSource {
 
    @Override
    protected Object determineCurrentLookupKey() {
 
        return DBContext.getDBKey();
    }
}

很简单,就一个方法。其中DBContext的代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package com.cnblogs.yjmyzz.utils;
 
public class DBContext {
 
    //define count of database and it must match with resources/properties/jdbc.properties
    private static final int DB_COUNT = 2;
 
    private static final ThreadLocal<String> tlDbKey = new ThreadLocal<String>();
 
    public static String getDBKey() {
        return tlDbKey.get();
    }
 
    public static void setDBKey(String dbKey) {
        tlDbKey.set(dbKey);
    }
 
    public static String getDBKeyByUserId(int userId) {
        int dbIndex = userId % DB_COUNT;
        return "db_" + (++dbIndex);
    }
}

主要利用了ThreadLocal这个类在每个线程中保持自己私有的变量。

这里我模拟了一个分库的场景:假设一个应用允许用户注册,但是用户数量太多,全都放在一个数据库里,记录过多,会导致数据库性能瓶颈,比较容易想到的办法,把用户的数据分散到多个数据库中保存(注:可能马上有同学会说了,分开存了,要查询所有用户怎么办?这确实是分库带来的一个弊端,但也有相应的解决方案,本文先不讨论这个,以免跑题)。

假设我们有二个数据库,里面的表结构完全相同,有一张表T_USER用于保存用户数据,问题来了,如果有N个用户要注册,id分别是1、2、3…,服务端接到参数后,怎么知道把这些数据分别插入到这二个库中,必然要有一个规则 ,比较简单的办法就是取模,所以上面的getDBKeyByUserId就是干这个的。

 

然后是jdbc的属性配置文件:

1
2
3
4
5
6
7
8
9
10
11
jdbc-driver=com.mysql.jdbc.Driver
 
jdbc-key-1=db_1
jdbc-url-1=jdbc:mysql://default:3306/db_1?useUnicode=true&characterEncoding=utf8
jdbc-user-1=test
jdbc-password-1=123456
 
jdbc-key-2=db_2
jdbc-url-2=jdbc:mysql://default:3306/db_2?useUnicode=true&characterEncoding=utf8
jdbc-user-2=test
jdbc-password-2=123456

接下来是spring的配置文件:

利用多数据源实现分库存储

利用多数据源实现分库存储
 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <beans xmlns="http://www.springframework.org/schema/beans"
 3        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4        xmlns:context="http://www.springframework.org/schema/context"
 5        xsi:schemaLocation="http://www.springframework.org/schema/beans
 6        http://www.springframework.org/schema/beans/spring-beans.xsd
 7          http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
 8 
 9 
10     <context:annotation-config/>
11 
12     <context:component-scan base-package="com.cnblogs.yjmyzz"/>
13 
14     <bean id="propertiesFactoryBean"
15           class="org.springframework.beans.factory.config.PropertiesFactoryBean">
16         <property name="locations">
17             <list>
18                 <value>classpath:properties/jdbc.properties</value>
19             </list>
20         </property>
21     </bean>
22 
23     <context:property-placeholder properties-ref="propertiesFactoryBean" ignore-unresolvable="true"/>
24 
25     <bean id="parentDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
26           destroy-method="close">
27         <property name="driverClassName" value="${jdbc-driver}"/>
28         <property name="url" value="${jdbc-url-1}"/>
29         <property name="username" value="${jdbc-user-1}"/>
30         <property name="password" value="${jdbc-password-1}"/>
31         <property name="filters" value="stat"/>
32         <property name="maxActive" value="20"/>
33         <property name="initialSize" value="1"/>
34         <property name="maxWait" value="60000"/>
35         <property name="minIdle" value="1"/>
36         <property name="timeBetweenEvictionRunsMillis" value="3000"/>
37         <property name="minEvictableIdleTimeMillis" value="300000"/>
38         <property name="validationQuery" value="SELECT 'x'"/>
39         <property name="testWhileIdle" value="true"/>
40         <property name="testOnBorrow" value="false"/>
41         <property name="testOnReturn" value="false"/>
42         <property name="poolPreparedStatements" value="true"/>
43         <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
44         <property name="connectionInitSqls" value="set names utf8mb4;"/>
45     </bean>
46 
47     <bean id="dataSource1" parent="parentDataSource">
48         <property name="url" value="${jdbc-url-1}"/>
49         <property name="username" value="${jdbc-user-1}"/>
50         <property name="password" value="${jdbc-password-1}"/>
51     </bean>
52 
53     <bean id="dataSource2" parent="parentDataSource">
54         <property name="url" value="${jdbc-url-2}"/>
55         <property name="username" value="${jdbc-user-2}"/>
56         <property name="password" value="${jdbc-password-2}"/>
57     </bean>
58 
59     <!-- config switch routing db -->
60     <bean id="dataSource" class="com.cnblogs.yjmyzz.utils.RoutingDataSource">
61         <property name="targetDataSources">
62             <map key-type="java.lang.String">
63                 <entry key="${jdbc-key-1}" value-ref="dataSource1"/>
64                 <entry key="${jdbc-key-2}" value-ref="dataSource2"/>
65             </map>
66         </property>
67     </bean>
68 
69     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
70         <property name="configLocation" value="classpath:mybatis-config.xml"></property>
71         <property name="dataSource" ref="dataSource"/>
72         <property name="mapperLocations">
73             <array>
74                 <value>classpath:mybatis/*.xml</value>
75             </array>
76         </property>
77     </bean>
78 
79     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
80         <property name="basePackage" value="com.cnblogs.yjmyzz.mapper"/>
81     </bean>
82 
83 </beans>
利用多数据源实现分库存储

关键的是parentDataSource,dataSource1,dataSource2,dataSource这几个bean的配置,一看就懂。

 

服务端的核心代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.cnblogs.yjmyzz.service.impl;
 
import com.cnblogs.yjmyzz.entity.UserEntity;
import com.cnblogs.yjmyzz.mapper.UserEntityMapper;
import com.cnblogs.yjmyzz.service.UserService;
import com.cnblogs.yjmyzz.utils.DBContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
/**
 * Created by yangjunming on 2/15/16.
 * author: yangjunming@huijiame.com
 */
 
@Service("userService")
public class UserServiceImpl implements UserService {
 
    @Autowired
    UserEntityMapper userEntityMapper;
 
 
    @Override
    public void addUser(UserEntity userEntity) {
        //switch db
        DBContext.setDBKey(DBContext.getDBKeyByUserId(userEntity.getUserId()));
        userEntityMapper.insertSelective(userEntity);
    }
 
    @Override
    public UserEntity getUser(int userId) {
        //switch db
        DBContext.setDBKey(DBContext.getDBKeyByUserId(userId));
        return userEntityMapper.selectByPrimaryKey(userId);
    }
}

注意:25,32行在调用mybatis操作数据库前,先根据需要切换到不同的数据库,然后再操作。 

运行完成后,可以看下db_1,db_2这二个数据库,确认数据是否已经分散存储到每个库中:

利用多数据源实现分库存储  

 

如果不喜欢在代码里手动切换db,也可以用注解的方式自动切换,比如:我们又增加了一个db_main

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
jdbc-driver=com.mysql.jdbc.Driver
 
jdbc-key-1=db_1
jdbc-url-1=jdbc:mysql://default:3306/db_1?useUnicode=true&characterEncoding=utf8
jdbc-user-1=test
jdbc-password-1=123456
 
jdbc-key-2=db_2
jdbc-url-2=jdbc:mysql://default:3306/db_2?useUnicode=true&characterEncoding=utf8
jdbc-user-2=test
jdbc-password-2=123456
 
jdbc-key-main=db_main
jdbc-url-main=jdbc:mysql://default:3306/db_main?useUnicode=true&characterEncoding=utf8
jdbc-user-main=test
jdbc-password-main=123456

然后在spring配置文件里,要做些调整:

利用多数据源实现分库存储
 1     <bean id="parentDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
 2           destroy-method="close">
 3         <property name="driverClassName" value="${jdbc-driver}"/>
 4         <property name="url" value="${jdbc-url-1}"/>
 5         <property name="username" value="${jdbc-user-1}"/>
 6         <property name="password" value="${jdbc-password-1}"/>
 7         <property name="filters" value="stat"/>
 8         <property name="maxActive" value="20"/>
 9         <property name="initialSize" value="1"/>
10         <property name="maxWait" value="60000"/>
11         <property name="minIdle" value="1"/>
12         <property name="timeBetweenEvictionRunsMillis" value="3000"/>
13         <property name="minEvictableIdleTimeMillis" value="300000"/>
14         <property name="validationQuery" value="SELECT 'x'"/>
15         <property name="testWhileIdle" value="true"/>
16         <property name="testOnBorrow" value="false"/>
17         <property name="testOnReturn" value="false"/>
18         <property name="poolPreparedStatements" value="true"/>
19         <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
20         <property name="connectionInitSqls" value="set names utf8mb4;"/>
21     </bean>
22 
23     <bean id="dataSource1" parent="parentDataSource">
24         <property name="url" value="${jdbc-url-1}"/>
25         <property name="username" value="${jdbc-user-1}"/>
26         <property name="password" value="${jdbc-password-1}"/>
27     </bean>
28 
29     <bean id="dataSource2" parent="parentDataSource">
30         <property name="url" value="${jdbc-url-2}"/>
31         <property name="username" value="${jdbc-user-2}"/>
32         <property name="password" value="${jdbc-password-2}"/>
33     </bean>
34 
35     <bean id="dataSourceMain" parent="parentDataSource">
36         <property name="url" value="${jdbc-url-main}"/>
37         <property name="username" value="${jdbc-user-main}"/>
38         <property name="password" value="${jdbc-password-main}"/>
39     </bean>
40 
41     <!-- method 1:  config switch routing db -->
42     <bean id="dataSource" class="com.cnblogs.yjmyzz.utils.RoutingDataSource">
43         <property name="targetDataSources">
44             <map key-type="java.lang.String">
45                 <entry key="${jdbc-key-1}" value-ref="dataSource1"/>
46                 <entry key="${jdbc-key-2}" value-ref="dataSource2"/>
47                 <entry key="${jdbc-key-main}" value-ref="dataSourceMain"/>
48             </map>
49         </property>
50     </bean>
51 
52     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
53         <property name="configLocation" value="classpath:mybatis-config.xml"></property>
54         <property name="dataSource" ref="dataSource"/>
55         <property name="mapperLocations">
56             <array>
57                 <value>classpath:mybatis/*.xml</value>
58             </array>
59         </property>
60     </bean>
61 
62     <bean id="userScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
63         <property name="basePackage" value="com.cnblogs.yjmyzz.mapper.user"/>
64         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
65     </bean>
66 
67     <!-- method 2: config annotation auto switch-->
68     <bean id="sqlSessionFactoryMain" class="org.mybatis.spring.SqlSessionFactoryBean">
69         <property name="configLocation" value="classpath:mybatis-config.xml"></property>
70         <property name="dataSource" ref="dataSourceMain"/>
71         <property name="mapperLocations">
72             <array>
73                 <value>classpath:mybatis/*.xml</value>
74             </array>
75         </property>
76     </bean>
77 
78     <bean id="orderScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
79         <property name="basePackage" value="com.cnblogs.yjmyzz.mapper.order"/>
80         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryMain"/>
81     </bean>
利用多数据源实现分库存储

注意:67-81行,主要是增加了一个单独的sqlSessionFactoryMain,然后将一个新的MapperScannerConfigurer关联到它。

新库里对应表的Mapper类可以这么写:

1
2
3
@Resource(name = "orderScannerConfigurer")
public interface OrderEntityMapper extends Mapper<OrderEntity> {
}

注解里name对应的值,必须与刚才spring文件里新增的MapperScannerConfigurer对应。

这样,服务层就可以省去手动切换的代码了,即:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
public class UserServiceImpl implements UserService {
 
    @Autowired
    UserEntityMapper userEntityMapper;
 
    @Autowired
    OrderEntityMapper orderEntityMapper;
 
 
    @Override
    public void addUser(UserEntity userEntity) {
        //switch db
        DBContext.setDBKey(DBContext.getDBKeyByUserId(userEntity.getUserId()));
        userEntityMapper.insertSelective(userEntity);
    }
 
    @Override
    public UserEntity getUser(int userId) {
        //switch db
        DBContext.setDBKey(DBContext.getDBKeyByUserId(userId));
        return userEntityMapper.selectByPrimaryKey(userId);
    }
 
    @Override
    public void addOrder(OrderEntity orderEntity) {
        //since orderEntityMapper can auto switch db by annotation
        //so we don't need to switch db manually
        orderEntityMapper.insertSelective(orderEntity);
    }
 
    @Override
    public OrderEntity getOrder(int orderId) {
        //since orderEntityMapper can auto switch db by annotation
        //so we don't need to switch db manually
        return orderEntityMapper.selectByPrimaryKey(orderId);
    }
 
}