一、索引设置

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 ALLSELECT * 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 UNIONSELECT * 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_detailWHERE trans_date BETWEEN '2020-01-01' AND '2020-12-31');转换为：SELECT * FROM stock aWHERE EXISTS (SELECT 1 FROM stock_trans_detail bWHERE a.stock_code = b.stock_codeAND 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 ALLSELECT * 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 UNIONSELECT * FROM score WHERE score = 99;转换为：SELECT * FROM score WHERE score = 100 UNION ALLSELECT * FROM score WHERE score = 99;

铁律12： 优化GROUP BY子句

如：SELECT trans_date,stock_code,sum(volume) FROM stock_trans_detailGROUP BY trans_date,CASE WHEN trans_type = 'B' THEN '买入' WHEN trans_type = 'S' then '卖出' ELSE '' ENDHAVING 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_detailWHERE 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_nameFROM stock aINNER JOIN stock_trans_detail bON a.stock_code = b.stock_codeAND b.trans_date BETWEEN '2020-01-01' AND '2020-12-31‘;转换为：SELECT a.stock_code,a.stock_name FROM stock aWHERE EXISTS (SELECT 1 FROM stock_trans_detail bWHERE a.stock_code = b.stock_codeAND b.trans_date BETWEEN '2020-01-01' AND '2020-12-31');

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

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