mysql 慢查询处理

mysql 执行

EXPLAIN SELECT `post`.*,`category_post`.`category_id` FROM `mj_portal_post` `post` INNER JOIN `mj_portal_category_post` `category_post` ON `post`.`id`=`category_post`.`post_id` 

WHERE `post`.`post_status` = 1 AND `post`.`post_type` = 1 AND `post`.`delete_time` = 0 AND `category_post`.`category_id` IN ('1',2,3,4) AND `post`.`create_time` >= 0 

ORDER BY post.published_time DESC LIMIT 9180,20

结果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	category_post	ALL	term_taxonomy_id	NULL	NULL	NULL	23041	Using where; Using temporary; Using filesort
1	SIMPLE	post	eq_ref	PRIMARY,type_status_date,post_date	PRIMARY	8	数据库.category_post.post_id	1	Using where

type ALL 遍历了全表，而且数据量很大，这应该是造成慢查询的主要原因

type eq_ref  唯一性索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引。关联字段确实是主键 post.id 

post 没法优化了，看看portal_category_post 怎么优化

Extra列

      Using temporary：使用了临时表保存中间结果。常见于排序order by和分组查询group by（最好优化）

      Using filesort：使用外部的索引排序，而不是按照表内的索引顺序进行读取。（一般需要优化）

key 列： NULL 没有使用索引

ref 列：Null  哪些列或常量被用于查找索引列上的值，因为没有使用索引，所以是null

需要优化的部分有 Using temporary，Using filesort，想办法建立索引

1、先看能不能建立索引 ，portal_category_post 是文章和分类中间表。给post_id添加索引，加快查询文章分类的速度。添加索引后结果

      

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	post	ref	PRIMARY,type_status_date,post_date	type_status_date	2	const,const	12946	Using where; Using filesort
1	SIMPLE	category_post	ref	term_taxonomy_id,post_id	post_id	8	cndcaheb_org_cn.post.id	1	Using where

     少了Using temporary ，为什么添加索引以后避免了Using temporary

     post 表查询次数增多，造成执行效率更低（大概5秒左右，建索引前1s多），这是为什么？

     post 的type 由eq_ref 变成了ref  

    结论：分类表的post_id不能建立索引，或者我建里的索引不对，为什么？？

2、category_id 是有索引的 为什么会是ALL ，没有走索引。IN列表不大的情况下应该走索引才对，这里为什么没走索引，百度一圈后偶然发现  IN ('1',2,3,4)   是不是1是字符串的原因。改成 in(1,2,3,4) 后

      

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	category_post	range	term_taxonomy_id	term_taxonomy_id	8	NULL	11092	Using where; Using temporary; Using filesort
1	SIMPLE	post	eq_ref	PRIMARY,type_status_date,post_date	PRIMARY	8	cndcaheb_org_cn.category_post.post_id	1	Using where

     走索引了，rows少了一半。但是多次执行sql发现比不使用索引慢一点。ALL的是1-2s左右  使用索引后3s左右

难道无法通过索引优化了？？

参考 ：《mysql中explain详解》    链接： https://www.jianshu.com/p/be1c86303c80

            《mysql 索引优化》    https://www.cnblogs.com/istitches/p/17153172.html