MySQL在线加索引-亲试ok

 
 
在线加索引
mysql> alter table pj_zzspdz_fpmxzb algorithm=inplace,lock=none ,add index PJ_ZZSPDZ_FPMXZB_FPDMHM(fpdm,fphm);
Query OK, 0 rows affected (49 min 7.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table pj_zzspdz_fpmxzb algorithm=inplace,lock=none ,add index PJ_ZZSPDZ_FPMXZB_MXID(mxid);
Query OK, 0 rows affected (17 min 23.88 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
 
对表做优化操作，如下：
mysql> optimize table pj_zzspdz_fpmxzb;
+-------------------------+----------+----------+-------------------------------------------------------------------+
| Table                   | Op       | Msg_type | Msg_text                                                          |
+-------------------------+----------+----------+-------------------------------------------------------------------+
| skdata.pj_zzspdz_fpmxzb | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| skdata.pj_zzspdz_fpmxzb | optimize | status   | OK                                                                |
+-------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 hours 36 min 32.27 sec)
 
 
主表数据量：
mysql> select count(1) from pj_zzspdz_fpmx;
+----------+
| count(1) |
+----------+
| 50356365 |
+----------+
1 row in set (2 min 8.11 sec)
 
 
主表每天数据量：
mysql> select t1.kprq,count(1) from pj_zzspdz_fpmx t1 group by t1.kprq;
+----------------+----------+
| kprq           | count(1) |
+----------------+----------+
| 20180412000000 |   199380 |
| 20180413000000 |   400005 |
| 20180414000000 |   400005 |
| 20180415000000 |   400005 |
| 20180416000000 |   400005 |
| 20180417000000 |   400005 |
| 20180418000000 |   400005 |
| 20180419000000 |   400005 |
| 20180420000000 |   247360 |
| 20180421000000 |   198104 |
| 20180422000000 |   400005 |
| 20180423000000 |   400005 |
| 20180424000000 |   400005 |
| 20180425000000 |   400005 |
| 20180426000000 |   400005 |
| 20180427000000 |   400005 |
| 20180428000000 |   400005 |
| 20180429000000 |   400005 |
| 20180430000000 |   400005 |
| 20180501000000 |   400005 |
| 20180502000000 |   400005 |
| 20180503000000 |   400005 |
| 20180504000000 |   400005 |
| 20180505000000 |   400005 |
| 20180506000000 |   400005 |
| 20180507000000 |   400005 |
| 20180508000000 |   400005 |
| 20180509000000 |   400005 |
| 20180510000000 |   400005 |
| 20180511000000 |   400005 |
| 20180512000000 |   400005 |
| 20180513000000 |   400005 |
| 20180514000000 |   400005 |
| 20180515000000 |   400005 |
| 20180516000000 |   400005 |
| 20180517000000 |   400005 |
| 20180518000000 |   400005 |
| 20180519000000 |   400005 |
| 20180520000000 |   400005 |
| 20180521000000 |   400005 |
| 20180522000000 |   400005 |
| 20180523000000 |   400005 |
| 20180524000000 |   400005 |
| 20180525000000 |   400005 |
| 20180526000000 |   400005 |
| 20180527000000 |   400005 |
| 20180528000000 |   400005 |
| 20180529000000 |   400005 |
| 20180530000000 |   400005 |
| 20180531000000 |   400005 |
| 20180601000000 |   400005 |
| 20180602000000 |   400005 |
| 20180603000000 |   400005 |
| 20180604000000 |   400005 |
| 20180605000000 |   400005 |
| 20180606000000 |   400005 |
| 20180607000000 |   350015 |
| 20180608000000 |   400005 |
| 20180609000000 |   400005 |
| 20180610000000 |   400005 |
| 20180611000000 |   400005 |
| 20180612000000 |   400005 |
| 20180613000000 |   400005 |
| 20180614000000 |   400005 |
| 20180615000000 |   400005 |
| 20180616000000 |   400005 |
| 20180617000000 |   400005 |
| 20180618000000 |   400005 |
| 20180619000000 |   400005 |
| 20180620000000 |   400005 |
| 20180621000000 |   400005 |
| 20180622000000 |   400005 |
| 20180623000000 |   400005 |
| 20180624000000 |   400005 |
| 20180625000000 |   400005 |
| 20180626000000 |   400005 |
| 20180627000000 |   679470 |
| 20180628000000 |   476910 |
| 20180629000000 |   400005 |
| 20180630000000 |   400005 |
| 20180701000000 |   400005 |
| 20180702000000 |   400005 |
| 20180703000000 |   400005 |
| 20180704000000 |   400005 |
| 20180705000000 |   400005 |
| 20180706000000 |   400005 |
| 20180707000000 |   400005 |
| 20180708000000 |   400005 |
| 20180709000000 |   400005 |
| 20180710000000 |   400005 |
| 20180711000000 |   400005 |
| 20180712000000 |   400005 |
| 20180713000000 |   400005 |
| 20180714000000 |   400005 |
| 20180715000000 |   400005 |
| 20180716000000 |   400005 |
| 20180717000000 |   400005 |
| 20180718000000 |   400005 |
| 20180719000000 |   400005 |
| 20180720000000 |   400005 |
| 20180721000000 |   400005 |
| 20180722000000 |   400005 |
| 20180723000000 |   400005 |
| 20180724000000 |   400005 |
| 20180725000000 |   400005 |
| 20180726000000 |   400005 |
| 20180727000000 |   400005 |
| 20180728000000 |   400005 |
| 20180729000000 |   400005 |
| 20180730000000 |   400005 |
| 20180731000000 |   400005 |
| 20180801000000 |   400005 |
| 20180802000000 |   400005 |
| 20180803000000 |   400005 |
| 20180804000000 |   400005 |
| 20180805000000 |   400005 |
| 20180806000000 |   400005 |
| 20180807000000 |   400005 |
| 20180808000000 |   400005 |
| 20180809000000 |   400005 |
| 20180810000000 |   364551 |
| 20180811000000 |   400005 |
| 20180812000000 |   400005 |
| 20180813000000 |   400005 |
| 20180814000000 |   400005 |
| 20180815000000 |   194995 |
| 20180815010000 |    94990 |
| 20180815020000 |    50000 |
| 20180815030000 |    50000 |
| 20180815040000 |    50000 |
| 20180815050000 |    50000 |
| 20180815060000 |    50000 |
| 20180815070000 |   100000 |
+----------------+----------+
133 rows in set (3 min 55.39 sec)
 
子表数据量：
mysql> select count(1) from pj_zzspdz_fpmxzb;
+----------+
| count(1) |
+----------+
| 45182011 |
+----------+
1 row in set (3 min 16.65 sec)
 
子表每天数据量：
mysql> select t1.kprq,count(1) from pj_zzspdz_fpmxzb t1 group by t1.kprq;
 
+----------------+----------+
| kprq           | count(1) |
+----------------+----------+
| 20180414000000 |   400005 |
| 20180415000000 |   400005 |
| 20180416000000 |   400005 |
| 20180417000000 |   400005 |
| 20180418000000 |   400005 |
| 20180419000000 |   400005 |
| 20180420000000 |   400005 |
| 20180421000000 |   400005 |
| 20180422000000 |   400005 |
| 20180423000000 |   400005 |
| 20180424000000 |   400005 |
| 20180428000000 |   400005 |
| 20180429000000 |   400005 |
| 20180430000000 |   400005 |
| 20180501000000 |   400005 |
| 20180502000000 |   400005 |
| 20180503000000 |   400005 |
| 20180504000000 |   400005 |
| 20180505000000 |   400005 |
| 20180506000000 |   800010 |
| 20180507000000 |   400005 |
| 20180508000000 |   400005 |
| 20180509000000 |   400005 |
| 20180510000000 |   400005 |
| 20180511000000 |   400005 |
| 20180513000000 |   400005 |
| 20180514000000 |   400005 |
| 20180515000000 |   800010 |
| 20180516000000 |   400005 |
| 20180517000000 |   400005 |
| 20180518000000 |   400005 |
| 20180519000000 |   400005 |
| 20180520000000 |   400005 |
| 20180521000000 |   400005 |
| 20180522000000 |   400005 |
| 20180523000000 |   400005 |
| 20180524000000 |   400005 |
| 20180528000000 |   400005 |
| 20180529000000 |   400005 |
| 20180530000000 |   400005 |
| 20180601000000 |   400005 |
| 20180602000000 |   400005 |
| 20180603000000 |   400005 |
| 20180604000000 |   400005 |
| 20180605000000 |   400005 |
| 20180606000000 |   800010 |
| 20180607000000 |   350015 |
| 20180608000000 |   400005 |
| 20180609000000 |   400005 |
| 20180610000000 |   400005 |
| 20180611000000 |   400005 |
| 20180613000000 |   400005 |
| 20180614000000 |   400005 |
| 20180615000000 |   800010 |
| 20180616000000 |   400005 |
| 20180617000000 |   400005 |
| 20180618000000 |   400005 |
| 20180619000000 |   400005 |
| 20180620000000 |   400005 |
| 20180621000000 |   400005 |
| 20180622000000 |   400005 |
| 20180623000000 |   400005 |
| 20180624000000 |   400005 |
| 20180628000000 |   476910 |
| 20180629000000 |   400005 |
| 20180630000000 |   400005 |
| 20180701000000 |   400005 |
| 20180702000000 |   400005 |
| 20180703000000 |   400005 |
| 20180704000000 |   400005 |
| 20180705000000 |   400005 |
| 20180706000000 |   800010 |
| 20180707000000 |   400005 |
| 20180708000000 |   400005 |
| 20180709000000 |   400005 |
| 20180710000000 |   400005 |
| 20180711000000 |   400005 |
| 20180713000000 |   400005 |
| 20180714000000 |   400005 |
| 20180715000000 |   800010 |
| 20180716000000 |   400005 |
| 20180717000000 |   400005 |
| 20180718000000 |   400005 |
| 20180719000000 |   400005 |
| 20180720000000 |   400005 |
| 20180721000000 |   400005 |
| 20180722000000 |   400005 |
| 20180723000000 |   400005 |
| 20180724000000 |   400005 |
| 20180728000000 |   400005 |
| 20180729000000 |   400005 |
| 20180730000000 |   400005 |
| 20180801000000 |   400005 |
| 20180802000000 |   400005 |
| 20180803000000 |   400005 |
| 20180804000000 |   400005 |
| 20180805000000 |   400005 |
| 20180806000000 |   800010 |
| 20180807000000 |   400005 |
| 20180808000000 |   400005 |
| 20180809000000 |   400005 |
| 20180810000000 |   364551 |
| 20180811000000 |   400005 |
| 20180813000000 |   400005 |
| 20180814000000 |   400005 |
| 20180815000000 |   389990 |
+----------------+----------+
106 rows in set (1 hour 35 min 54.08 sec)
 
 
 
 
 
 
select count(t2.fpdm)   from pj_zzspdz_fpmxzb t2 ,pj_zzspdz_fpmx t1  where   t1.fpdm=t2.fpdm and t1.fphm=t2.fphm and     t1.kprq between '20180527000000' and '20180528000000';
 
 
SELECT count(1) from pj_zzspdz_fpmxzb t2 ,pj_zzspdz_fpmx t1 where t1.kprq >=  '20180815000000' and t1.kprq <= '20180815235959' and scbz= '0';
 
 
创建时间：2021-12-22 10:22
넶浏览量：0
ꄴ前一个： 无
ꄲ后一个： 无