30秒优化到0.01秒

在一次慢查询检查中发现一条超过30秒的语句，当时详细记录了优化过程，在此分享我的优化过程

//原始查询语句

select s.time,m.uid,m.cid,m.content_id,c.table_name,c.pid,f.content_code,m.set_date from h_slave as s left join h_master as m on m.id = s.rm_id left join category as c on m.cid = c.id left join content_to_category as f on f.cid=m.cid and m.content_id = f.content_id where m.status = 1 order by s.time

id    select_type    table      type     possible_keys      key                key_len        ref            rows                  Extra
1      SIMPLE            m          ALL         PRIMARY          NULL              NULL        NULL        1752        Using where; Using temporary; Using filesort
1      SIMPLE            c          eq_ref      PRIMARY          PRIMARY        4                m.cid        1
1      SIMPLE            s           ref           rm_id                 rm_id               63             m.id           1                  Using where; Using index
1      SIMPLE            f            ALL         NULL                 NULL              NULL        NULL         33296

//第1步修改：发现f表没有索引，进行了全表扫描，给f表cid加索引。此时速度已经有质的飞越

正在显示第 0 - 24 行 (共 4014 行, 查询花费 0.8829 秒) [time: 000001 - 000032]

id    select_type    table      type     possible_keys      key                key_len        ref            rows                  Extra 
1      SIMPLE            m          ALL         PRIMARY          NULL              NULL        NULL        1752        Using where; Using temporary; Using filesort
1      SIMPLE            c          eq_ref      PRIMARY          PRIMARY        4                m.cid        1 
1      SIMPLE             s           ref           rm_id                 rm_id               63             m.id           1                  Using where; Using index
1      SIMPLE             f            ref            cid                       cid                   4                m.cid        166

//第2步，检查表结构，发现部分字段类型设置不合理。稍作修改，此处对速度影响不大，但作为良好的设计习惯，还是应该提倡

h_master表字段

hour                varchar(2)          -> char(2)
minute            varchar(2)         -> char(2)
second           varchar(2)         -> char(2)
interval_time  int(3)                 -> tinyint
times               int(3)                 ->tinyint
state                int(2)                  -> tinyint

h_slave表字段time (varchar(10))，用于计算时分秒，因为时分秒一共6位数字，改为char(6)

s表排序字段time为字符串，改为int。（此处修改需要连带修改业务逻辑，数据库字段批量转换）

第3步

分析sql语句，where条件全部用m表的字段，而排序用s表字段。

分析业务逻辑，where条件实际为无效条件，m表不存在state管理，那个字段设计表时有，但实际没用。所以直接删掉改字段。

正在显示第 0 - 24 行 (共 4439 行, 查询花费 0.0152 秒) [time: 000001 - 000032]

id    select_type    table      type     possible_keys      key                key_len        ref            rows                  Extra 
1      SIMPLE            m          ALL         PRIMARY          NULL              NULL        NULL        1701        Using where;
1      SIMPLE            c          eq_ref      PRIMARY          PRIMARY        4                m.cid        1 
1      SIMPLE             s           ref           rm_id                 rm_id               63             m.id           1                  Using where; Using index
1      SIMPLE             f            ref            cid                       cid                   4                m.cid        90

结论：通过sql语句、表结构和业务逻辑的优化，将30多秒的查询缩短到0.01秒