Sql优化索引优化脚本优化Sql性能优化

对于数据库的sql脚本优化的一些笔记

以下使用的sql脚本为Sql Server的语法。

1. 尽量避免隐式转换
   避免使用不一致的类型去匹配查询字段，而应该使用与数据库字段一致的类型去匹配查询。
   如：字段是varchar类型，而使用的是数字类型的值去匹配，否则可能导致不会走索引。

select * from userTest where idNo = '58714582698575';--字段类型一致
select * from userTest where idNo = 58714582698575; --字段类型不一致

在程序编码过程中，要在参数传入之前就转换好数据类型，而不是到了运行时依赖于数据库的隐式转换。

2. 尽量避免对字段使用函数或表达式
   在sql操作时，应尽量避免对字段使用函数等表达式来限制匹配。
   如：

---匹配证件号码idNo的前四位为”3625”的数据
select * from userTest where left(idNo,4) = '3624'; --避免使用这种方式，不会走索引
select * from userTest where idNo like '3624%'; --可以改用这种方式，走索引

---找到分数的一半等于20分的所有信息
select * from userTest where score/2 = 20;  --避免使用这种方式，不会走索引
select * from userTest where score = 20*2;  --可以改用这种方式，走索引

3. 使用非运算不走索引
   尽量避免使用 != 、<>、is null 、is not null、in、not in操作符。
   如：

---找到证件号码不是'58714582698575'的信息
select * from userTest where idNo <> '58714582698575';
--找到有证件号码的数据
select * from userTest where idNo is not null;
此中方式不是不能用，只是用了不会走该字段的索引。

4. 避免字段类型与需求字段类型不一致
   在工作当中，有的开发人员喜欢将数字类型、金额类型设计成字符varchar类型，以便于它在代码实现过程中，不用做任何字段类型的转换。
   如：本该存放金额的字段decimal类型，确设计成了varchar类型。
   对于数字字段，设计为字符型，会降低查询和连接的性能，增加存储开销。这是因为数据库在查询和连接时会逐个比较字符串中每一个字符，导致性能降低，而对于数字型而言只需要比较一次就可以了，当然这只是其中影响之一，对于类型不一致的情况，还会带来其他很多不便，不如sum金额的时候，排序金额的时候。
5. 尽量避免使用子查询

select * from userTest1 a where (select count(*) from userTest2 b where a.userId=b.userId)>0;
select * from userTest1 a where EXISTS (select top 1 1 from userTest2 b where a.userId=b.userId);

很明显第一句的性能比第二句的性能低，因为第一句会产生大量锁定的表扫描或是索引扫描。

6. 尽量避免使用count(*)
   在开发过程中，会存在很多的boolean的判断。

如：判断”张三”是否存在，然后很多的开发人员会这么做
select count(*) from userTest1 where name = '张三';

根据这条sql查询出的值，判断是否大于0来判断是否存在”张三”。
然后我们完全可以如下做，

select top 1 1 from userTest1 where name = '张三';--sql server的语法

只查询出一条记录，拿到结果集，只需要判断结果是否为’1’即可。
假如：该表有很多的’张三’,然后在存储中，存放在相距很远的内存块中，使用count()的话是要找到所有的内容汇总后才 返回结果，而top 1 1 的话只需要找到一条就返回结果。后者性能当然比前者高。*

7. 尽量避免索引字段用前缀通配符

select * from userTest where username like '_小二'; --查询所有单姓，名字叫小二的数据
select * from userTest where username like '%小二'; --查询所有单姓，名字叫小二的数据

以上两种均不会走索引，应该如下使用。

select * from userTest where username like '王%'; --查询王姓数据，走索引

8. 联合索引最左匹配原则
   给userTest建立一个联合索引index1：(userId,username, birthDate).

select * from userTest where username = '王小二'; ---不走index1索引
select * from userTest where birthDate = '2020-01-01'; ---不走index1索引
select * from userTest where userId = '0000001'; ---走index1索引

使用全是最优做法：

select * from userTest where userId='000001' and username = '王小二' and birthDate = '2020-01-01'; ---走index1索引

9. 尽量避免使用or、in、not in

   userId与birthDate均创建了索引

select  roleId,birthDate from userTest where birthDate = '2021-11-21' or (roleId = '000001' and birthDate > '2020-01-01');

可以优化成

select  roleId,birthDate from userTest where birthDate = '2021-11-21'
union all
select  roleId,birthDate from userTest where roleId = '000001' and birthDate > '2020-01-01';

但并不是所有的索引都适合这样优化，可通过查询计划找出原因进行优化。

10. 在使用union all 能满足时就不要使用union

    Union all 是将多个结果集拼成一个结果集
    Union 是将多个结果集去重后拼成一个结果集

11. 其他
    索引的建立，加快了数据的查询速度，但是同时也降低了update、insert、delete的效率，每次的update等操作，不仅需更新数，同时也会去更新索引，索引不是越多越好。很多的开发人员喜欢无脑的创建索引，我见过最多的是，一个表36个字段，确建立了16个索引，这是极其不可取的。
    科学索引一般3个为宜，万般无奈的情况下不要超过7个。