mysql的join连接查询优化经历

    工作中接开发主管反馈，有个资讯接口调用时有出现响应较慢，需要优化。

    接口返回有时较慢？？第一反应是接口的redis缓存过期时有大量请求穿过redis缓存，对mysql访问压力较大造成的。

    先看PHP代码，逻辑不复杂，首先根据传入的资讯类型id，从redis获取资讯缓存，没有就读取mysql同时更新redis缓存，缓存有效期3-5分钟。

    大概定位在mysql查询这块，然后上阿里云后台发现mysql慢查询记录。

    表结构：

资讯分类表
CREATE TABLE `article_cats` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(50) NOT NULL COMMENT '分类名',
     `pid` INT(11) NOT NULL DEFAULT '0',
     `type` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '类型(0文章1视频)',
     `img_url` VARCHAR(300) NOT NULL COMMENT '类别ICON',
     `sort` INT(11) NOT NULL DEFAULT '0' COMMENT '排序(数值越大越靠前)',
     PRIMARY KEY (`id`),
     UNIQUE INDEX `name` (`name`),
     INDEX `sort` (`sort`)
 )
 COMMENT='文章分类表'
 COLLATE='utf8_general_ci'
 ENGINE=InnoDB
 AUTO_INCREMENT=106;

资讯表

CREATE TABLE `article` (

  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `type` TINYINT(1) UNSIGNED ZEROFILL NULL DEFAULT NULL COMMENT '类型',
  `title` VARCHAR(500) NOT NULL COMMENT '标题',
  `abstract` VARCHAR(255) NULL DEFAULT '' COMMENT '摘要',
  `own` VARCHAR(50) NULL DEFAULT NULL,
  `url_md5` VARCHAR(200) NULL DEFAULT NULL COMMENT '文章URL',
  `live_status` TINYINT(1) NULL DEFAULT '1' COMMENT '显示类型 1 web显示，2 app显示，3 全显示，4 全不显示',
  `status` TINYINT(1) NULL DEFAULT '1' COMMENT '状态，1正常，-1删除',
 `grab_time` DATETIME NULL DEFAULT NULL COMMENT '采集第3方时间',
 `img_url` VARCHAR(200) NULL DEFAULT '',
 `sort` INT(11) NULL DEFAULT '0' COMMENT '文章排序',
 `last_own` VARCHAR(20) NULL DEFAULT NULL COMMENT '最后编辑者帐号',
 `url` VARCHAR(200) NULL DEFAULT NULL,
 `level` TINYINT(4) NULL DEFAULT '1' COMMENT '快讯的重要程度：1不重要,2一般,3重要',
 `share_num` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '分享数',
 `status_level` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '置顶状态',PRIMARY KEY (`id`),
 INDEX `title` (`title`(255)),
 INDEX `status` (`status`),
 INDEX `grab_time` (`grab_time`),
INDEX `t_l_s` (`type`, `live_status`, `status`),
INDEX `type` (`type`, `status`, `live_status`),
INDEX `live_status` (`live_status`, `status_level`),
INDEX `Index 11` (`sort`)
)

 COMMENT='文章表'
 COLLATE='utf8_general_ci'
 ENGINE=InnoDB
 AUTO_INCREMENT=537710;

查询sql：
SELECT 

a.id,a.title,a.img_url,ac.type actype,a.template,a.sort,a.grab_time,a.type as type_id,a.abstract,ac.name as type,ac.img_url as cat_img_url

FROM article a

 left join article_cats ac 

on a.type = ac.id 

WHERE ( a.status = 1 ) AND ( ac.pid = 25 )

 ORDER BY a.id DESC LIMIT 0,20

/* Affected rows: 0 已找到记录: 20 警告: 0 持续时间 1 query: 0.328 sec. (+ 0.016 sec. network) */

查询结果显示耗时0.3秒，这样的时间间隔在大量请求穿透redis缓存直接访问mysql时肯定会对接口有影响。

用explain执行sql得到如下分析结果：

其中possible_keys列指出MySQL能使用哪个索引在该表中找到行，key列显示MySQL实际决定使用的键（索引）。

可以看到mysql服务端自做判断使用status索引，但status只分两种情况，1和-1，区分度不大，索引用起来效果不佳(当时也不知道谁对status字段建了索引。。。)。

 我们可以强制mysql使用区分度大的索引和字段。

这里我们还有type索引：

`type` (`type`, `status`, `live_status`)
加上强制使用的索引sql变成如下

SELECT 

a.id,a.title,a.img_url,ac.type actype,a.template,a.sort,a.grab_time,a.type as type_id,a.abstract,ac.name as type,ac.img_url as cat_img_url

FROM article a force index(type)

 left join article_cats ac 

on a.type = ac.id 

WHERE ( a.status = 1 ) AND ( ac.pid = 25 )

  /* Affected rows: 0 已找到记录: 20 警告: 0 持续时间 1 query: 0.015 sec. (+ 0.016 sec. network) */

  查询结果显示耗时0.015秒，查询效率有了很大提升。

再用explain分析

可以发现ac表走全表扫描(资讯类型表一百来号数据)，a表使用type索引。

改完后测试环境测试完成么问题，下周来上线。

explain关键字是项目开发过程的一件sql效率提优利器，写sql，特别是联合查询，字表查询之类的时候还是大有裨益。