SQL优化方法

在下方文章基础上持续更新。。。
sql优化的N种方法_持续更新

1、先查询再关联补充信息

当遇到条件筛选的表中只有id，需要关联其他表获取name时，可以在条件查询完成后在外面再包裹一层再进行join和order操作，查询出name。示例代码如下：

select
	temp.channel_id as x_data,
	c.channel_name as x_name,
	temp.api_id as y_data,
	concat( concat( concat( d.api_key, '[' ), d.api_name ), ']' ) as y_name,
	temp.count
from (
		select
			a.channel_id,
			b.api_id,
			count( 1 ) as count
		from
			a
			join b on a.serial = b.serial
		where
			a.type not in (3,4)
	) temp
	join c on temp.channel_id = c.channel_id
	join d on temp.api_id = d.api_id

2、null值判断在条件查询完成后再包一层再做判断

select
	temp.channel_id as x_data,
	temp.api_id as y_data,
	temp.count
from (
		select
			a.channel_id,
			b.api_id,
			count( 1 ) as count
		from
			a
			join b on a.serial = b.serial
		where
			a.type not in (3,4)
	) temp
where
	temp.channel_id is not null