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

一、索引设置

1、索引的设置原则

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

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

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

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

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

2、案例分析

比如有下面一张表：

查询需求如下：

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

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

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

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

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


查询一：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

二、SQL优化

1、SQL优化的五个层次

主键 –> 唯一索引 –> 非唯一索引 –> 全表扫描(应尽量避免)

2、SQL优化的15条铁律

铁律1：尽量避免在索引列上使用表达式

如：
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%';

铁律2：尽量避免在WHERE条件中使用NOT、<>和!=操作符

如：
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;

铁律3：避免索引列的隐式类型转换

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

铁律4：在OR的两个条件上都有索引的话，将OR转换为UNION或UNION ALL

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

铁律5：使用IN操作符替换OR

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

铁律6：使用BETWEEN操作符替换IN

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

铁律7：在合适的情况下，使用EXISTS操作符替换IN

如：
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；

铁律8：LIKE通配符也可能导致索引失效

如：
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 ('机械原理','计算机导论');

铁律9：索引中不包含NULL值，所以使用IS NULL、IS NOT NULL做判断的条件，都用不到索引

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

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

铁律10： INT型字段中，应该使用>=替换>

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

铁律11： 在多个结果集不交叉的情况下，使用UNION ALL替换UNION

如：
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;

铁律12： 优化GROUP BY子句

如：
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;

铁律13： 使用ORDER BY配合LIMIT分页查询

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

铁律14： 避免不合理的DISTINCT

由于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');

铁律15： 不要把SQL语句写的太冗长

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