??????????????????
????select
????emp.id
????from
????cm_log cl
????inner join
????employee emp
????on cl.ref_table = 'Employee'
????and cl.ref_oid = emp.id 
????where
????cl.last_upd_date >='2013-11-07 15:03:00'
????and cl.last_upd_date<='2013-11-08 16:00:00'
????and emp.is_deleted = 0 
????union
????select
????emp.id
????from
????cm_log cl
????inner join
????emp_certificate ec
????on cl.ref_table = 'EmpCertificate'
????and cl.ref_oid = ec.id 
????inner join
????employee emp
????on emp.id = ec.emp_id 
????where
????cl.last_upd_date >='2013-11-07 15:03:00'
????and cl.last_upd_date<='2013-11-08 16:00:00'
????and emp.is_deleted = 0
????4.?????????????????????????????????????????????
????5.???????????????????????????
????6.???????????????￡?????10ms ???????200????
????+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
????| id | select_type  | table      | type   | possible_keys                   | key               | key_len | ref                   | rows | Extra       |
????+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
????|  1 | PRIMARY      | cl         | range  | cm_log_cls_id??idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where |
????|  1 | PRIMARY      | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 | Using where |
????|  2 | UNION        | cl         | range  | cm_log_cls_id??idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where |
????|  2 | UNION        | ec         | eq_ref | PRIMARY??emp_certificate_empid   | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 |             |
????|  2 | UNION        | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.ec.emp_id  |    1 | Using where |
????| NULL | UNION RESULT | <union1??2> | ALL    | NULL                            | NULL              | NULL    | NULL                  | NULL |             |
????+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
?????????ó???
?????????????????????????????е??????????????????????????????????У????????????????????????Щ?????????￡????????????о??????
????select
????*
????from
????stage_poi sp
????where
????sp.accurate_result=1
????and (
????sp.sync_status=0
????or sp.sync_status=2
????or sp.sync_status=4
????);
????0.????????ж????951??????6.22????????
????951 rows in set (6.22 sec)
????1.??explain??rows????361??type = ALL????????????
????+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
????| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
????+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
????|  1 | SIMPLE      | sp    | ALL  | NULL          | NULL | NULL    | NULL | 3613155 | Using where |
????+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
????2.??????ζ???ò?????????????????????? 0?????????951??
????3.??explain??rows ???????951
?????????accurate_result = 1??????
????select count(*)??accurate_result from stage_poi  group by accurate_result;
????+----------+-----------------+
????| count(*) | accurate_result |
????+----------+-----------------+
????|     1023 |              -1 |
????|  2114655 |               0 |
????|   972815 |               1 |
????+----------+-----------------+
???????????accurate_result?????ε????????????????????-1??0??1????????????????????????????????????
??????????sync_status??ε????
????select count(*)??sync_status from stage_poi  group by sync_status;
????+----------+-------------+
????| count(*) | sync_status |
????+----------+-------------+
????|     3080 |           0 |
????|  3085413 |           3 |
????+----------+-------------+
?????????????????????????????????????????
???????????????????ó?????????????????????????е??????????????????????????????????????????????????????????統(tǒng)sync_status 0??3??????????????????????????????
????4.?????????????????ó?????????????????????SQL????????????????????????????????????????sync_status?????α??1?????????????????????????????1000???????????????????ó???????????SQL??ü??????????????????????????????????????????????????????????
????5.????????????????????????佨??????
????alter table stage_poi add index idx_acc_status(accurate_result??sync_status);
????6.??????????????????200ms??????30????
????952 rows in set (0.20 sec)
?????????????????·??????????????????????????????????????????????where???????????????????????????????????????“????”????????????Щ???????????У?????ü???????????????????????????????????????????????????????п??????????????????????????4??????SQL????ó????????????????????????????????????????????????????????????????
???????????????
????select
????c.id??
????c.name??
????c.position??
????c.sex??
????c.phone??
????c.office_phone??
????c.feature_info??
????c.birthday??
????c.creator_id??
????c.is_keyperson??
????c.giveup_reason??
????c.status??
????c.data_source??
????from_unixtime(c.created_time) as created_time??
????from_unixtime(c.last_modified) as last_modified??
????c.last_modified_user_id 
????from
????contact c 
????inner join
????contact_branch cb
????on  c.id = cb.contact_id 
????inner join
????branch_user bu
????on  cb.branch_id = bu.branch_id
????and bu.status in (
????1??
????2) 
????inner join
????org_emp_info oei
????on  oei.data_id = bu.user_id
????and oei.node_left >= 2875
????and oei.node_right <= 10802
????and oei.org_category = - 1 
????order by
????c.created_time desc  limit 0 ??
????10;
???????????????
????0.?????????ж????10?????????13?????????????
????10 rows in set (13.06 sec)
????1.explain
????+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
????| id | select_type | table | type   | possible_keys                       | key                     | key_len | ref                      | rows | Extra                                        |
????+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
????|  1 | SIMPLE      | oei   | ref    | idx_category_left_right??idx_data_id | idx_category_left_right | 5       | const                    | 8849 | Using where; Using temporary; Using filesort |
????|  1 | SIMPLE      | bu    | ref    | PRIMARY??idx_userid_status           | idx_userid_status       | 4       | meituancrm.oei.data_id   |   76 | Using where; Using index                     |
????|  1 | SIMPLE      | cb    | ref    | idx_branch_id??idx_contact_branch_id | idx_branch_id           | 4       | meituancrm.bu.branch_id  |    1 |                                              |
????|  1 | SIMPLE      | c     | eq_ref | PRIMARY                             | PRIMARY                 | 108     | meituancrm.cb.contact_id |    1 |                                              |
????+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
????????м???????mysql???org_emp_info?????8849?????????????idx_userid_status????branch_user???????????idx_branch_id????contact_branch?????????????contact???