Guadazi
0%

MySQL慢查询优化

Posted on Edited on In

慢查询优化

1
mysqldumpslow -s t -t 10  ~/Downloads/慢日志/mysql-slow.log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Count: 2  Time=1238.48s (2476s)  Lock=0.00s (0s)  Rows=90750.0 (181500), cdw[cdw]@2hosts
  select t3.PRODUCT_CHANNEL_CODE,t3.MAIN_PROJECT_NAME ,
  t3.MAIN_PROJECT_CODE ,t3.SUB_PROJECT_NAME,t3.SUB_PROJECT_CODE,
  t3.CHANNEL_TYPE,t3.CHANNEL_NAME,t3.CHANNEL_ID,t3.CHANNEL_SUB_CODE,
  t3.CHANNEL_SUB_TYPE,
  t4.FRIST_CHANNEL,
  t4.SECOND_CHANNEL,
  t4.thrid_channel
  from (SELECT t1.PRODUCT_CHANNEL_CODE,t1.MAIN_PROJECT_NAME ,
  t1.MAIN_PROJECT_CODE ,t1.SUB_PROJECT_NAME,t1.SUB_PROJECT_CODE,
  t1.CHANNEL_TYPE,t1.CHANNEL_NAME,t1.CHANNEL_ID,t2.CHANNEL_SUB_CODE,
  t2.CHANNEL_SUB_TYPE FROM qd_dz_db.qd_prochannel t1 left join qd_channel t2
  on t1.CHANNEL_ID=t2.CHANNEL_ID ) t3 join
  (
  SELECT
  a.PRODUCT_CHANNEL_CODE,
  b.FRIST_CHANNEL,
  b.SECOND_CHANNEL,
  b.THIRD_CHANNEL AS thrid_channel
  FROM
  qd_dz_db.qd_prochannel a
  JOIN
  qd_dz_db.qd_channel b ON a.CHANNEL_ID = b.CHANNEL_ID
  )t4 on t3.PRODUCT_CHANNEL_CODE=t4.PRODUCT_CHANNEL_CODE
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
44
45
46
47
explain
select
    t3.PRODUCT_CHANNEL_CODE,
    t3.MAIN_PROJECT_NAME ,
    t3.MAIN_PROJECT_CODE ,
    t3.SUB_PROJECT_NAME,
    t3.SUB_PROJECT_CODE,
    t3.CHANNEL_TYPE,
    t3.CHANNEL_NAME,
    t3.CHANNEL_ID,
    t3.CHANNEL_SUB_CODE,
    t3.CHANNEL_SUB_TYPE,
    t4.FRIST_CHANNEL,
    t4.SECOND_CHANNEL,
    t4.thrid_channel
  from (
      SELECT
          t1.PRODUCT_CHANNEL_CODE,
          t1.MAIN_PROJECT_NAME ,
          t1.MAIN_PROJECT_CODE ,
          t1.SUB_PROJECT_NAME,
          t1.SUB_PROJECT_CODE,
          t1.CHANNEL_TYPE,
          t1.CHANNEL_NAME,
          t1.CHANNEL_ID,
          t2.CHANNEL_SUB_CODE,
          t2.CHANNEL_SUB_TYPE
      FROM
          qd_dz_db.qd_prochannel t1
      left join
          qd_channel t2
      on t1.CHANNEL_ID=t2.CHANNEL_ID
  ) t3
  join
  (
      SELECT
          a.PRODUCT_CHANNEL_CODE,
          b.FRIST_CHANNEL,
          b.SECOND_CHANNEL,
          b.THIRD_CHANNEL AS thrid_channel
      FROM
          qd_dz_db.qd_prochannel a
      JOIN
          qd_dz_db.qd_channel b
      ON a.CHANNEL_ID = b.CHANNEL_ID
  )t4
  on t3.PRODUCT_CHANNEL_CODE=t4.PRODUCT_CHANNEL_CODE
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE b null ALL PRIMARY,index_qd_channel null null null 212 100.0 null
1 SIMPLE a null ref index_qd_prochannel index_qd_prochannel 4 qd_dz_db.b.CHANNEL_ID 43 100.0 null
1 SIMPLE t1 null ALL null null null null 56367 10.0 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t2 null eq_ref PRIMARY,index_qd_channel PRIMARY 4 qd_dz_db.t1.CHANNEL_ID 1 100.0 null 
1
show index from qd_prochannel
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type CommentIndex_comment
qd_prochannel 0 PRIMARY 1 PR_CHANNEL_ID A 56367 null null BTREE
qd_prochannel 1 index_qd_prochannel 1 CHANNEL_ID A 1311 null null BTREE
qd_prochannel 1 index_qd_prochannel 2 MAIN_PROJECT_CODE A 3316 null null YES BTREE
qd_prochannel 1 index_qd_prochannel 3 CREATE_TIME A 14092 null null YES BTREE
qd_prochannel 1 index_qd_prochannel_MAIN_PROJECT_CODE 1 MAIN_PROJECT_CODE A 613 null null YES BTREE 
1
show index from qd_channel
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
qd_channel 0 PRIMARY 1 CHANNEL_ID A 212 null null BTREE
qd_channel 1 index_qd_channel 1 CHANNEL_ID A 212 null null BTREE
qd_channel 1 index_qd_channel 2 CHANNEL_CODE A 212 null null YES BTREE 
1