SQL优化示例

一、distinct 、 union all 、 union 区别

1.问题SQL及改进SQL

-- 表结构，user_id c_user 分别添加了索引，且字段可空，varchar(32)，编码UTF-8
-- 问题SQL
select count(c_user) from 
(select user_id as c_user from jr_td_project_invest union select c_user from p2p_td_project_invest ) as count_investor;
-- 改进后：
select count(distinct c_user) from 
(select distinct user_id as c_user from jr_td_project_invest union all select distinct c_user from p2p_td_project_invest ) as count_investor;

2.性能分析：
通过explain 查询结果显示：
[img]http://dl2.iteye.com/upload/attachment/0127/6819/ebf8df70-15a5-3f57-9441-2227e436509b.png[/img]

其他指标基本相同，rows :MYSQL认为必须检查的用来返回请求数据的行数
改进后要比前者少了50%的数据量

3.原因分析：
[list]
[*]distinct 取出重复数据
[*]union all 合并不去除重复数据，即会列出所有的值，不进行合并
[*]union 合并且去重，将两个集合合并后先排序distinct 去重，再 union all
[*]explain 结果中 key_len: 编码为UTF-8，varchar 数据类型所占字节数为 字符长度n*3+2字节+1字节（索引字段为空+1字节） = 99
[/list]
Union all 与 Union 区别
博文参考：
[url]http://blog.csdn.net/jesseyoung/article/details/40427849[/url]
[url]http://blog.csdn.net/liuxiaohuago/article/details/7075371[/url]

EXPLAIN select user_id as c_user from jr_td_project_invest ;

[img]http://dl2.iteye.com/upload/attachment/0127/6861/da107f36-6a87-36dc-9e09-035a2561cfbd.png[/img]

EXPLAIN select distinct user_id as c_user from jr_td_project_invest ;

[img]http://dl2.iteye.com/upload/attachment/0127/6863/436d93c8-8221-32bd-938a-5d3902f98471.png[/img]

distinct 使用后可以识别到索引；distinct 依然是查询索引中所有的数据
仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。
博文参考：
[url=http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html]理解MySQL——索引与优化[/url]

前者：
两个子查询通过索引
两个大的集合通过 Union 合并并去重

后者：
将两个不含重复的数据的集合进行合并不去重，
再在合并后的集合中去重

4.经验总结
使用union 前考虑是否需要去重；如果需要改为 在单个查询中使用 distinct
合并时使用 union all

二、时间类型查询

1.问题SQL及业务逻辑

-- 有两处业务场景使用如下SQL
-- 签到日历列表查询，展示本月已签到天数
-- 当天是否已签到
-- 实现原理：DATE_FORMAT 函数中格式化时间部分为动态传入的参数，若查询列表，则%y-%m ；若查询某一天 %y-%m-%d 

SELECT
	CREATE_TIME,
	TYPE,
	POINT,
	POINT_PERIOD
FROM
	jf_user_point_record
WHERE
	1 = 1
AND USER_ID = ?
AND DATE_FORMAT(CREATE_TIME, ?) = ?
AND TYPE = ?
ORDER BY
	CREATE_TIME ASC

2.SQL优化

-- 拆分为两个SQL，按照上述的业务场景
-- 当天是否已签到，将查询到的DATE 与当天日期进行比较
SELECT CREATE_TIME FROM `jf_user_point_record`
WHERE 
USER_ID = ""
AND TYPE = ? 
ORDER BY RECORD_ID DESC LIMIT 1 ; 
-- 排序按照主键排序，主键有索引，且数据插入顺序递增，完全不可以不适用create_timem 作为排序条件

-- 列表查询
SELECT
	CREATE_TIME,
	TYPE,
	POINT,
	POINT_PERIOD
FROM
	jf_user_point_record
WHERE
	1 = 1
AND USER_ID = ?
AND CREATE_TIME >= #{} -- 当月1日的日期
AND TYPE = ?
ORDER BY
	RECORD_ID ASC
-- SQL中尽量避免使用函数在字段上，如果字段上添加了索引，使用函数会导致索引失效
-- 另外，比较当天的时间可以使用 CREATE_TIME like "2017-11-03%" 
-- 时间参数格式化在Service 层处理

3.经验总结
除必要的函数调用外不要在SQL中进行函数的处理，会影响效率