SQL进阶

一、索引设置

1、索引的设置原则

1经常出现在WHERE条件、关联条件中的字段作为索引字段；
2
3在满足查询需求的前提下，应尽可能少的创建索引；（对于一个组合索引，可以满足以组合索引左边的一部分字段的查询需求）；
4
5经常更新的字段，不适合创建索引；
6
7区分度太低的字段，不适合创建索引；
8
9不要为永远不会出现在WHERE条件、关联条件中的字段创建索引；

2、案例分析

比如有下面一张表：

查询需求如下：

1需求一：按单个客户编号查询某个客户的交易明细。
2
3需求二：按单个客户编号查询某个时间段的某只股票的交易明细。
4
5需求三：统计某个时间段每只股票不同交易类型的交易金额。
6
7需求四：统计每天所有股票的交易金额。
8
9需求五：统计每只股票所有的交易费用。
10
11
12查询一：SELECT * FROM stock_trans_detail WHERE customer_id = '?';
13
14查询二：SELECT * FROM stock_trans_detail WHERE customer_id = '?' AND trans_date BETWEEN '2020-01-01' AND '2020-12-31' AND stock_code = '?';
15
16查询三：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;
17
18查询四：SELECT trans_date,sum(price*volume) FROM stock_trans_detail GROUP BY trans_date;
19
20查询五：SELECT stock_code,sum(fee) FROM stock_trans_detail GROUP BY stock_code;

索引设置分析：

1需求一：按单个客户编号查询某个客户的交易明细。
2需求二：按单个客户编号查询某个时间段的某只股票的交易明细。
3需求三：统计某个时间段每只股票不同交易类型的交易金额。
4需求四：统计每天所有股票的交易金额。
5需求五：统计每只股票所有的交易费用。
6
7
8索引一：customer_id
9索引二：customer_id,trans_date,stock_code
10索引三：trans_date,stock_code
11索引四：无
12索引五：无
13
14最终：
15索引一：customer_id,trans_date,stock_code
16索引二：trans_date,stock_code

二、SQL优化

1、SQL优化的五个层次

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

2、SQL优化的15条铁律

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

1如：
2SELECT * FROM score WHERE score / 100 >= 0.6;
3转换为：
4SELECT * FROM score WHERE score >= 0.6 * 100;
5
6
7SELECT * FROM score WHERE LEFT(student_id,1) = 'S';
8转换为：
9SELECT * FROM score WHERE student_id LIKE 'S%';

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

1如：
2SELECT * FROM score WHERE score <> 50;
3转换为：
4SELECT * FROM score WHERE score > 50 OR score < 50;
5或
6SELECT * FROM score WHERE score > 50;
7UNION ALL
8SELECT * FROM score WHERE score < 50;

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

1如：
2SELECT * FROM stock_trans_detail WHERE stock_code = 600001;
3转换为：
4SELECT * FROM stock_trans_detail WHERE stock_code = '600001';

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

1如：
2SELECT * FROM score WHERE score = 100 OR gender = '男';
3转换为：
4SELECT * FROM score WHERE score = 100 
5UNION
6SELECT * FROM score WHERE gender = '男';

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

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

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

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

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

1如：
2SELECT * FROM stock 
3WHERE stock_code IN (
4SELECT stock_code FROM stock_trans_detail
5WHERE trans_date BETWEEN '2020-01-01' AND '2020-12-31'
6);
7转换为：
8SELECT * FROM stock a
9WHERE EXISTS (
10SELECT 1 FROM stock_trans_detail b
11WHERE a.stock_code = b.stock_code
12AND b.trans_date BETWEEN '2020-01-01' AND '2020-12-31'
13);
14
15
16子查询结果集较大时，适合用EXISTS；
17子查询结果集较小时，适合用IN；

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

1如：
2SELECT * FROM score WHERE subject_name LIKE '%机%';
3转换为：
4SELECT * FROM score WHERE subject_name LIKE '机%'
5UNION ALL
6SELECT * FROM score WHERE subject_name LIKE '计算机%';
7或
8SELECT * FROM score 
9WHERE subject_name IN ('机械原理','计算机导论');

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

1解决方法：应该将数据库中的所有字段都设置为不可为NULL，且针对不同的数据类型设置默认值。
2比如，对于INT类型的字段，如果为NULL，则设为默认值0。这样就可以将IS NULL的判断，转换为与0相等的判断。
3
4如：
5SELECT * FROM score WHERE score IS NULL;
6转换为：
7SELECT * FROM score WHERE score = 0;

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

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

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

1如：
2SELECT * FROM score WHERE score = 100 
3UNION
4SELECT * FROM score WHERE score = 99;
5转换为：
6SELECT * FROM score WHERE score = 100 
7UNION ALL
8SELECT * FROM score WHERE score = 99;

铁律12：优化GROUP BY子句

1如：
2SELECT trans_date,stock_code,sum(volume) 
3FROM stock_trans_detail
4GROUP BY trans_date,
5CASE WHEN trans_type = 'B' THEN '买入' WHEN trans_type = 'S' then '卖出' 
6ELSE '' END
7HAVING trans_date BETWEEN '2020-01-01' AND '2020-12-31';
8转换为：
9SELECT trans_date,
10CASE WHEN trans_type = 'B' THEN '买入' WHEN trans_type = 'S' then '卖出' 
11ELSE '' END, SUM(volume) 
12FROM stock_trans_detail
13WHERE trans_date BETWEEN '2020-01-01' AND '2020-12-31'
14GROUP BY trans_date,trans_type;

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

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

铁律14：避免不合理的DISTINCT

1由于DISTINCT去重功能的限制，实际开发过程中使用到DISTINCT的情况很少。如果发现结果集有重复而需要使用DISTINCT去重，
2则很可能是因为对业务逻辑理解不足导致的SQL语句的编写问题。
3
4如：
5SELECT DISTINCT a.stock_code,a.stock_name
6FROM stock a
7INNER JOIN stock_trans_detail b
8ON a.stock_code = b.stock_code
9AND b.trans_date BETWEEN '2020-01-01' AND '2020-12-31‘;
10转换为：
11SELECT a.stock_code,a.stock_name FROM stock a
12WHERE EXISTS (
13SELECT 1 FROM stock_trans_detail b
14WHERE a.stock_code = b.stock_code
15AND b.trans_date BETWEEN '2020-01-01' AND '2020-12-31');

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

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

评论区

相关推荐