SQL进阶-索引设置&sql优化

经常出现在WHERE条件、关联条件中的字段作为索引字段；

在满足查询需求的前提下，应尽可能少的创建索引；（对于一个组合索引，可以满足以组合索引左边的一部分字段的查询需求）；

经常更新的字段，不适合创建索引；

区分度太低的字段，不适合创建索引；

不要为永远不会出现在WHERE条件、关联条件中的字段创建索引；

需求一：按单个客户编号查询某个客户的交易明细。

需求二：按单个客户编号查询某个时间段的某只股票的交易明细。

需求三：统计某个时间段每只股票不同交易类型的交易金额。

需求四：统计每天所有股票的交易金额。

需求五：统计每只股票所有的交易费用。


查询一：SELECT * FROM stock_trans_detail WHERE customer_id = ‘?‘;

查询二：SELECT * FROM stock_trans_detail WHERE customer_id = ‘?‘ AND trans_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘ AND stock_code = ‘?‘;

查询三：SELECT stock_code,trans_type,sum(price*volume) FROM stock_trans_detail WHERE trans_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘ GROUP BY stock_code,trans_type;

查询四：SELECT trans_date,sum(price*volume) FROM stock_trans_detail GROUP BY trans_date;

查询五：SELECT stock_code,sum(fee) FROM stock_trans_detail GROUP BY stock_code;

需求一：按单个客户编号查询某个客户的交易明细。
需求二：按单个客户编号查询某个时间段的某只股票的交易明细。
需求三：统计某个时间段每只股票不同交易类型的交易金额。
需求四：统计每天所有股票的交易金额。
需求五：统计每只股票所有的交易费用。


索引一：customer_id
索引二：customer_id,trans_date,stock_code
索引三：trans_date,stock_code
索引四：无
索引五：无

最终：
索引一：customer_id,trans_date,stock_code
索引二：trans_date,stock_code

如：
SELECT * FROM score WHERE score / 100 >= 0.6;
转换为：
SELECT * FROM score WHERE score >= 0.6 * 100;


SELECT * FROM score WHERE LEFT(student_id,1) = ‘S‘;
转换为：
SELECT * FROM score WHERE student_id LIKE ‘S%‘;

如：
SELECT * FROM score WHERE score <> 50;
转换为：
SELECT * FROM score WHERE score > 50 OR score < 50;
或
SELECT * FROM score WHERE score > 50;
UNION ALL
SELECT * FROM score WHERE score < 50;

如：
SELECT * FROM stock_trans_detail WHERE stock_code = 600001;
转换为：
SELECT * FROM stock_trans_detail WHERE stock_code = ‘600001‘;

如：
SELECT * FROM score WHERE score = 100 OR gender = ‘男‘;
转换为：
SELECT * FROM score WHERE score = 100 
UNION
SELECT * FROM score WHERE gender = ‘男‘;

如：
SELECT * FROM score WHERE score = 100 OR score = 99;
转换为：
SELECT * FROM score WHERE score IN (100,99);

如：
SELECT * FROM score WHERE score IN (100,99,98,97,96,95);
转换为：
SELECT * FROM score WHERE score BETWEEN 95 AND 100;

如：
SELECT * FROM stock 
WHERE stock_code IN (
SELECT stock_code FROM stock_trans_detail
WHERE trans_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘
);
转换为：
SELECT * FROM stock a
WHERE EXISTS (
SELECT 1 FROM stock_trans_detail b
WHERE a.stock_code = b.stock_code
AND b.trans_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘
);


子查询结果集较大时，适合用EXISTS；
子查询结果集较小时，适合用IN；

如：
SELECT * FROM score WHERE subject_name LIKE ‘%机%‘;
转换为：
SELECT * FROM score WHERE subject_name LIKE ‘机%‘
UNION ALL
SELECT * FROM score WHERE subject_name LIKE ‘计算机%‘;
或
SELECT * FROM score 
WHERE subject_name IN (‘机械原理‘,‘计算机导论‘);

解决方法：应该将数据库中的所有字段都设置为不可为NULL，且针对不同的数据类型设置默认值。
比如，对于INT类型的字段，如果为NULL，则设为默认值0。这样就可以将IS NULL的判断，转换为与0相等的判断。

如：
SELECT * FROM score WHERE score IS NULL;
转换为：
SELECT * FROM score WHERE score = 0;

如：
SELECT * FROM student WHERE age > 15;
转换为：
SELECT * FROM student WHERE age >= 16;

如：
SELECT * FROM score WHERE score = 100 
UNION
SELECT * FROM score WHERE score = 99;
转换为：
SELECT * FROM score WHERE score = 100 
UNION ALL
SELECT * FROM score WHERE score = 99;

如：
SELECT trans_date,stock_code,sum(volume) 
FROM stock_trans_detail
GROUP BY trans_date,
CASE WHEN trans_type = ‘B‘ THEN ‘买入‘ WHEN trans_type = ‘S‘ then ‘卖出‘ 
ELSE ‘‘ END
HAVING trans_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘;
转换为：
SELECT trans_date,
CASE WHEN trans_type = ‘B‘ THEN ‘买入‘ WHEN trans_type = ‘S‘ then ‘卖出‘ 
ELSE ‘‘ END, SUM(volume) 
FROM stock_trans_detail
WHERE trans_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘
GROUP BY trans_date,trans_type;

如：
当LIMIT的偏移量特别大时，效率会非常低
SELECT * FROM score LIMIT 1000,10 效率高
SELECT * FROM score LIMIT 100000,10 效率低
转换为：
SELECT * FROM score ORDER BY student_id LIMIT 100000,10;

由于DISTINCT去重功能的限制，实际开发过程中使用到DISTINCT的情况很少。如果发现结果集有重复而需要使用DISTINCT去重，
则很可能是因为对业务逻辑理解不足导致的SQL语句的编写问题。

如：
SELECT DISTINCT a.stock_code,a.stock_name
FROM stock a
INNER JOIN stock_trans_detail b
ON a.stock_code = b.stock_code
AND b.trans_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘;
转换为：
SELECT a.stock_code,a.stock_name FROM stock a
WHERE EXISTS (
SELECT 1 FROM stock_trans_detail b
WHERE a.stock_code = b.stock_code
AND b.trans_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘);

合理使用临时表，而不是想着一个SQL解决所有问题。如果一个SQL关联的表超过5张，就应该考虑拆分。