sql语句查询性能优化

表对另一张表统计并返回统计（一对一COUNT）

主表 cms_anthology ，关联关系表 cms_anthology_in_blog

• 第一种，查询里面套查询

SELECT a.*, (SELECT count(*) FROM cms_anthology_in_blog ib WHERE ib.AnthologyId=a.Id) as Total FROM cms_anthology  a

• 第二种，使用左连接方式

SELECT a.*,count(ib.BlogId) as total FROM
  cms_anthology a LEFT JOIN cms_anthology_in_blog ib on a.Id =ib.AnthologyId  GROUP BY a.Id

从查询效率上来说，第二种查询下来大幅提升

mysql实现每个分类下取N条数据

核心思想使用分组将，查找分类下随机数据后将主键变成集合形式（GROUP_CONCAT）后使用 FIND_IN_SET 取需要的条数，这里的分类不多的情况下使用 where xx in ()

SELECT 
    a.*
FROM
    cms_blog AS a,
    (SELECT 
        GROUP_CONCAT(id order by rand()) AS ids
    FROM
        cms_blog
				WHERE  cms_blog.CategoryId in (SELECT DISTINCT cms_blog.CategoryId FROM cms_blog)
    GROUP BY cms_blog.CategoryId)
		AS b
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 2

性能提升版（提升好像不是很多）

SELECT 
    a.*
FROM
    cms_blog AS a,
    (SELECT 
       substring_index(GROUP_CONCAT(id order by rand()),',',2) AS ids
    FROM
        cms_blog
				WHERE  cms_blog.CategoryId in (SELECT DISTINCT cms_blog.CategoryId FROM cms_blog)
    GROUP BY cms_blog.CategoryId)
		AS b
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 2