记一次 SQL优化

水深无声 2022-09-10 06:18 263阅读 0赞

### 一、经常遇到的问题 ###

1、没有索引或没有用到索引  
2、I/O吞吐量小，存在瓶颈效应  
3、内侧不足  
4、网络速度慢  
5、查询出的结果数据量过大  
6、出现锁或者死锁  
7、返回不必要的行和列数据，比如 select \*  
8、查询预计存在问题，没有优化

### 二、基础策略 ###

1、尽量避免在 where 子句中使用不等于（!= 或 <>） 操作符，否则将导致引擎放弃使用索引而进行全表扫描。MySql 只有对以下操作符才使用索引：<、<=、=、>、>=、between and 以及某些特殊方式的 like（例如 ‘a%’）;  
2、尽量避免在where子句中对字段进行null值的判断，否则将导致引擎放弃使用索引而进行全表扫描，在创建表时，null 时默认值，通常应该使用 not null，或者用一个默认值，例如把 0 作为默认值；  
3、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。使用or的语句可以分解成多个查询，并且通过 union 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 union all 执行，效率会更高；  
4、应尽量避免在 where 子句中使用 in 和 not in ，否则将导致全表扫描，对于连续的数值，能用 between and 尽量避免使用 in . 一般用 exists 代替 in . 若需要使用 in ，则在 in 后面值的列表中，按照值的分布量降序排了，减少判断的次数；  
5、where 子句中使用like时，’%T%’ 和 ‘%T’ 也将导致全表扫描，‘T%’ 才会用到索引；  
6、应尽量避免在 where 子句中对 “=” 左边的字段进行函数、算术运算及其他表达时运算，可以将表达式运算放在 “=” 的右边，否则将导致引擎放弃使用索引而进行全表扫描。如果在where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时。它必须在编译时进行选择。然而，如果在编译时简历访问计划，变量的值还是未知的，因而无法作为索引选择的输入项；  
7、在使用索引字段作为条件是，如果该索引是复合索引，那么必须使用到该索引重点 第一个字段作为条件时，才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致；  
8、当索引列有大量数据重复时，sql 查询可能不回去利用索引，并不是所有索引对查询都有效，sql 是根据表中数据来进行查询优化的。如表中有“性别”字段，技师在“性别”字段简历索引，对查询效率也起不了作用，尽量不要对数据库中某个含有大量重复的值的字段简历索引；  
9、建立索引可以提高select的效率，但是索引并不是越多越好。索引同时降低了 insert 和 update 的效率，因为 insert 或 update 可能会有重建索引的现象，具体也看业务应用情况而定。同时，一个表的索引最好不要超过 6 个，如果太多则应考虑一些不常使用的字段是否有简历索引的必要；  
10、对于数值字段，尽量使用数字型字段，如果只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并且会增加存储的开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对数字型而言，只需比较一次就够了；对于字符型字段，尽量使用 varchar/nvarchar 代替 char/ncchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个想对较小的字段内搜索效率显然要搞一些；  
11、避免使用 select \* from table ，用具体的字段代替 “\*” ，避免返回用不到的字段；  
12、尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index\_name 来强制指定索引，避免对大表查询时进行 table scan ，必要时考虑新建索引。要注意索引的维护，周期性重建索引，重新编译存储过程；  
13、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 来代替 create table ，避免造成产生大量日志，以提高速度。如果数据量不大，为了缓和系统表的资源，应先 create table ，然后 insert ；  
14、如果使用到了临时表，在存储过程的最后务必将所有的临时表显示删除，先 truncate table ，然后 drop table，这样可以避免系统表的较长时间锁定；  
15、当服务器内存足够多时，配置线程数量=最大连接数+5，使其发挥最大的效率。否则使用配置线程数量<最大连接数，启用sql server的线程池来解决，如果还是 线程数量=最大连接数+5，可能会严重损坏服务器的性能。  
16、尽量避免向客户端返回大量结果数据，如果数据量过大，应该考虑相应的需求是否合理。尽量避免大失误操作，提高系统并发能力；

### 三、创建索引的一般规则 ###

1、表的主键、外键需要简历索引  
2、频繁与其他表进行连接的表，在连接字段上应该建立索引；  
3、频繁在where子句及order by中的字段，特别是大表的字段，应该建立索引；  
4、索引应该建在短字段上，对于大的文本字段甚至超长字段，避免建索引；  
5、复合索引的建立，需要介个实际应用进行分析，尽量考虑用单字段索引代替；  
6、正确选择复合索引中的主列字段，一般是选择性较好的字段；  
7、复合索引的几个字段是否经常同时以and方式出现在where子句中，单字段查询是否极少甚至没有，如果是，则可以建立复合索引，否则考虑单字段索引；  
8、如果丰富和索引中包含的字段经常单独出现在where子句中，则分解未多个单字段索引；  
9、如果复合索引包含的字段超过3个，需要结合实际应用考虑其必要性，考虑减少复合的字段；  
10、如果既有单字段索引，又有这几个字段上的复合索引，通常可以删除复合索引；  
11、频繁进行数据操作的表，不要建立太多的索引，删除无用的索引；  
12、建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的，相反，还会降低数据增加、删除时的性能，特别是对频繁更新的表来说，负面影响非常大；  
13、尽量不要对数据库中某个含有大量重复的值的字段简历索引。