出自:http://wenzengliu.blog.51cto.com/9378751/1789389
一个慢查询引发的思考
1
2
3
4
5
6
7
8
9
10
|
select
count(*)
from
task
where
status=2
and operator_id=20839
and operate_time>1371169729
and operate_time<1371174603
and
type
=2;
|
MySQL索引原理
慢查询优化
建索引的几大原则
回到开始的慢查询
select * from task where status = 0 and type = 12 limit 10;
select count(*) from task where status = 0 ;
查询优化神器 - explain命令
慢查询优化基本步骤
几个慢查询案例
复杂语句写法
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
|
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)
1
2
3
4
5
6
7
8
|
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
|
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 |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 unionselect
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
|
1
2
3
4
5
6
7
8
9
10
11
|
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
|
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)
|
明确应用场景
1
2
3
4
5
6
7
8
9
10
11
|
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)
无法优化的语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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)
1
2
3
4
5
6
|
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+|
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 | |
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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)
|
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
39
40
41
42
43
|
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;
|
验证一下效果 预计在1ms内,提升了13000多倍! ```sql 10 rows in set (0.00 sec)
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
|
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)
|