MySql 40W+数据，加上order by 查询速度很慢

Question

本帖最后由 duanbinhua03 于 2014-02-24 17:43:09 编辑

Answer 1

explain select ..
show index from ..直接以文本格式贴出来，不要贴图。

Answer 2

貌似没什么好的方法 只能换快的cpu了

Answer 3

mysql> explain Select * from vkq_kqdata Where EmpSysID<>''  Order by EmpNo ASC,K
QDate ASC  limit 0,30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: AK_KQ_KqData
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 76013
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: ecarddb.b.EmpSysID
         rows: 1
        Extra: Using index condition; Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.DepartSysID
         rows: 1
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: NULL
         rows: 4
        Extra: Using where; Using index; Using join buffer (Block Nested Loop)
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: NULL
         rows: 2
        Extra: Using where; Using index; Using join buffer (Block Nested Loop)
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.PolitySysID
         rows: 1
        Extra: Using index
*************************** 8. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: NULL
         rows: 1
        Extra: Using where; Using index; Using join buffer (Block Nested Loop)
*************************** 9. row ***************************
           id: 1
  select_type: SIMPLE
        table: g
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.NationSysID
         rows: 1
        Extra: Using index
*************************** 10. row ***************************
           id: 1
  select_type: SIMPLE
        table: h
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.JobSysID
         rows: 1
        Extra: NULL
*************************** 11. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.EduSysID
         rows: 1
        Extra: Using index
*************************** 12. row ***************************
           id: 1
  select_type: SIMPLE
        table: j
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.CountrySysID
         rows: 1
        Extra: Using index
*************************** 13. row ***************************
           id: 1
  select_type: SIMPLE
        table: k
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.CertTypeSysID
         rows: 1
        Extra: Using index
*************************** 14. row ***************************
           id: 1
  select_type: SIMPLE
        table: sy_id2name
         type: eq_ref
possible_keys: PRIMARY,AK_SY_ID2Name
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.EmpSexSysID
         rows: 1
        Extra: Using where
*************************** 15. row ***************************
           id: 1
  select_type: SIMPLE
        table: sy_id2name
         type: ref
possible_keys: AK_SY_ID2Name
          key: AK_SY_ID2Name
      key_len: 48
          ref: const,ecarddb.a.EmpStatusID
         rows: 13
        Extra: Using where; Using index
*************************** 16. row ***************************
           id: 1
  select_type: SIMPLE
        table: sy_id2name
         type: eq_ref
possible_keys: PRIMARY,AK_SY_ID2Name
          key: PRIMARY
      key_len: 74
          ref: ecarddb.a.EmpMarrySysID
         rows: 1
        Extra: Using where
*************************** 17. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: eq_ref
possible_keys: AK_RS_EmpCard
          key: AK_RS_EmpCard
      key_len: 74
          ref: ecarddb.b.EmpSysID
         rows: 1
        Extra: NULL
*************************** 18. row ***************************
           id: 1
  select_type: SIMPLE
        table: sy_id2name
         type: ref
possible_keys: AK_SY_ID2Name
          key: AK_SY_ID2Name
      key_len: 48
          ref: const,ecarddb.p.CardStatusID
         rows: 13
        Extra: Using where; Using index
*************************** 19. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
         type: eq_ref
possible_keys: AX_RS_EmpPhoto
          key: AX_RS_EmpPhoto
      key_len: 74
          ref: ecarddb.b.EmpSysID
         rows: 1
        Extra: Using index
19 rows in set (0.00 sec)mysql>

Answer 4

差不多有15张表，人事表涉及到职位等基本信息表，kq_kqdata这是人员的打卡时间表