MYSQL教程美团网技术团队分享的MySQL索引及慢查询优化教程

select
  count(*) 
from
  task 
where
  status=2 
  and operator_id=20839 
  and operate_time>1371169729 
  and operate_time<1371174603 
  and type=2;

create_time = unix_timestamp('2014-05-29');

select * from task where status = 0 and type = 12 limit 10;
select count(*) from task where status = 0 ;

select
  distinct cert.emp_id 
from
  cm_log cl 
inner join
  (
   select
     emp.id as emp_id,
     emp_cert.id as cert_id 
   from
     employee emp 
   left join
     emp_certificate emp_cert 
      on emp.id = emp_cert.emp_id 
   where
     emp.is_deleted=0
  ) cert 
   on (
     cl.ref_table='Employee' 
     and cl.ref_oid= cert.emp_id
   ) 
   or (
     cl.ref_table='EmpCertificate' 
     and cl.ref_oid= cert.cert_id
   ) 
where
  cl.last_upd_date >='2013-11-07 15:03:00' 
  and cl.last_upd_date<='2013-11-08 16:00:00';

53 rows in set (1.87 sec)

+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| 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; Using temporary  |
| 1 | PRIMARY   | <derived2> | ALL  | NULL              | NULL         | NULL  | NULL       | 63727 | Using where; Using join buffer |
| 2 | DERIVED   | emp    | ALL  | NULL              | NULL         | NULL  | NULL       | 13317 | Using where          |
| 2 | DERIVED   | emp_cert  | ref  | emp_certificate_empid      | emp_certificate_empid | 4    | meituanorg.emp.id |   1 | Using index          |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+

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

+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| 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 |       |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
53 rows in set (0.01 sec)

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
  );

951 rows in set (6.22 sec)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows  | Extra    |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE   | sp  | ALL | NULL     | NULL | NULL  | NULL | 3613155 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

select count(*),accurate_result from stage_poi group by accurate_result;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
|   1023 |       -1 |
| 2114655 |        0 |
|  972815 |        1 |
+----------+-----------------+

select count(*),sync_status from stage_poi group by sync_status;
+----------+-------------+
| count(*) | sync_status |
+----------+-------------+
|   3080 |      0 |
| 3085413 |      3 |
+----------+-------------+

alter table stage_poi add index idx_acc_status(accurate_result,sync_status);

952 rows in set (0.20 sec)

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;

10 rows in set (13.06 sec)

+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
| 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 |                       |
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+

select
 count(*)
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 

+----------+
| count(*) |
+----------+
|  778878 |
+----------+
1 row in set (5.19 sec)

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
where
exists (
select
1
from
contact_branch cb
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
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,
10;

```sql
10 rows in set (0.00 sec)

select
  sql_no_cache  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  
where
  exists (
   select
     1    
   from
     contact_branch cb     
   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 <= 2875              
        and oei.org_category = - 1        
     where
      c.id = cb.contact_id      
   )    
  order by
   c.created_time desc limit 0 ,
   10;

Empty set (2 min 18.99 sec)