SQL优化

定义：

对sql进行改进，提高在数据库中的执行效率。

优化包括重写SQL语句、调整查询逻辑、使用索引等技巧。

优化的目的：

是减少数据库的负载，提高查询速度，改善用户体验。

目标和原则：

提高查询速度、减少资源消耗、保证数据准确性。

保持SQL语句简洁明了、避免使用全表扫描、合理利用索引、减少不必要的JOIN操作、优化数据访问路径等。

在优化过程中，需要权衡查询速度、资源消耗和数据准确性等多个因素，避免过度优化导致其他问题。同时，优化后的SQL语句要进行测试确保符合预期。

为什么要SQL优化：

提高性能（减少等待时间） 节省资源（减少数据库的cpu、内存和磁盘的使用） 应对高并发 提升稳定性

执行计划：

执行计划获取：使用EXPLAIN等命令获取SQL语句的执行情况，了解查询的执行路径、索引使用情况等。

执行计划解读：（1）查看总耗时，一般而言，执行计划第一行所对应的cost值，反应了运行这段SQL的总体估计成本。（2）按照从左至右，从上至下的方法，了解执行计划的执行步骤、执行计划按照层次逐步缩进，从左至右看，缩进最多的那一步，最先执行，如果缩进量相同，则按照从上而下的方法判断执行顺序，可粗略的认为上面的步骤优先执行。（3）分析表的访问方式。表的方位方式主要是有两种：全表扫描（table access full）和索引扫描（index scan），如果表上存在选择性很好的索引，却走了全表扫描，而且是大表的全表扫描，就说明表的访问方式可能存在问题；若大表上没有合适的索引而走了全表扫描，就需要分析能否建立索引，或者是否能选择更合适的表连接方式和连接顺序已提高效率（4）这里看到的执行计划，只是SQL运行前可能的执行方式，实际运行时可能因为软硬件环境的不同，而有所改变，而且cost高的执行计划，不一定在实际运行起来，速度就一定差。对于表的连接顺序，多数情况下使用的是嵌套循环，尤其是在索引可用性好的情况下，使用嵌套循环式最好的，但当ORACLE发现需要访问的数据表较大，索引 的成本较高或者没有合适的索引可用时，会考虑使用哈希连接，以提高效率。排序合并连接的性能最差，但在存在排序需求，或者存在非等值连接无法使用哈希连接 的情况下，排序合并的效率，也可能比哈希连接或嵌套循环要好。

F5查看总耗时，执行计划第一行所对应的时间。

执行计划分析：分析执行计划中的关键信息，如查询类型、扫描行数、索引类型等，判断查询是否高效。

根据分析提出建议：调整查询条件顺序、增加或优化索引等。

三种表连接方式：嵌套循环和哈希连接和排序-合并连接，常见的是前两种

常见性能问题定位：

索引问题：检查是否存在缺失索引、冗余索引或索引失效等问题，导致查询性能下降。

查询逻辑：检查是否存在缺失索引、冗余索引或索引失效等问题，导致查询性能下降。

数据量：检查是否存在缺失索引、冗余索引或索引失效等问题，导致查询性能下降。

数据库配置：检查数据库配置是否合理，如内存分配、并发连接数等，确保数据库性能得到充分发挥。

数据库存储结构

oracle存储结构：

逻辑存储结构：

Oracle内部的组织和管理数据的方式。数据库是由一个或多个表空间等组成。

物理存储结构：

Oracle外部（操作系统）组织和管理数据的方式——真实存储数据的地方。包括：数据文件（data file）、联机日志文件（online redo log file）、控制文件（control file）等组成的。

表空间：

定义：

最高级的逻辑存储结构，一个Oracle数据库由多个表空间组成，但一个表空间只隶属于一个数据库，一个表空间可以有多数据文件，但一个数据文件只能属于一个表空间。一个表空间就是一片磁盘区域，由一个或多个磁盘文件组成。

表空间类型：

SYSTEM：系统表空间，保存系统关键数据信息（必须）

SYSAUX：系统辅助表空间，主要保存统计信息（提供给执行计划）

UNDO：还原表空间（DDM操作在未commit前数据保存在改表空间）

TEMP：临时表空间。存储sql执行过程中产生的临时数据。

USERS：用户表空间，创建用户指定的表空间。

表索引

定义：

是帮助数据库高效获取数据的数据结构。好比一本书的目录，能加快数据库的查询速度。

特点：

从检索方面：可以提高数据检索的效率，降低数据库的IO成本。

从排序方面：通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

索引会占据磁盘空间；虽然会提高查询效率但会降低更新表的效率；比如每次对表进行增删改查。

索引分类及创建语句：

普通索引：

create index 索引名称 on 表面（列名）；

针对单个列进行索引，适用于查询条件种经常使用的列

唯一索引：

create unique index 索引名称 on 表面（列名）；

唯一性字段，例如：表主键字段

组合索引：

create index 索引名称 on 表名（列名1，列名2）；

针对多个列进行索引，适用于多列联合查询的情况。

全文索引、空间索引、位图索引——不常用

创建索引原则：

1.主键自动建立唯一索引；

2.频繁作为查询条件的字段应该创建索引；

3.查询中排序的字段应该创建索引；

4.多表关联查询中，关联字段应该创建索引

5.避免在索引列上进行计算或函数操作

6.尽量减少索引的数量和大小，过度索引会增加数据库的存储空间和维护成本，同时也会影响数据插入，更新和删除的性能。

7.避免对那些可能会产生很高的更新动作的列进行索引；

8.数据量太小没必要建索引；

9.避免对那些经常会被删除的表中的多个列进行索引。若有可能，只对那些在这样的表上会进行删除的主关键字与/或列进行索引。

10.定期分析和优化索引，根据数据库的使用情况，定期分析索引的使用效率吧，对不需要的索引进行删除或重建

索引失效场景

1.没有where子句

2.使用is null 和is not null

3.对索引字段进行了运算或使用了函数

select * from t where id +1 = 100 ; //这种情况索引失效

select * from t where id = 100 - 1; //这种情况索引生效

select * from t where abs(id)= 100; //索引失效

4.查询中的数据类型和字段类型不一致（默认函数转换）

select * from t where col = 100; //col 是varchar2 类型，这种情况能查出来，但索引不生效。因为隐性使用了 to_number(col) = 100；

5.like模糊查询，”%“在前面

select * from t where name like ‘%张三’；//索引失效

6.WHERE 子句中使用不等于操作

不等于操作包括：<>, !=, NOT colum >= ?, NOT colum <= ?

对于这个限制条件可以通过 OR 替代，例如： colum <> 0 ===> colum>0 OR colum<0

7.违背最左匹配原则

eg：

A、B、D会失效， A为空 B的500应该加单引号 D无组合索引

优化技巧：

1.限制结果集，避免使用select，只检索需要的字段数据

2.利用索引，为常用查询列和join列创建索引，提供查询效率

3.分页查询，对大量数据的查询采用分页，减少单词查询的数据量

4.优化join操作：

减少join数量，尽量避免多表JOIN，可通过数据冗余或中间表来减少JOIN。

使用合适的join类型，根据数据关联情况选择合适的JOIN类型，如INNER JOIN、LEFT JOIN等。

调整join顺序，将小表放在前面，大表放在后面，减少扫描的数据量

5.谨慎使用子查询，尤其在select语句中使用

6.对于复杂查询可以考虑用临时表存储中间结构，提高查询效率。临时表适用于数据量较大且需要多次使用的场景，不适用于小数据量或一次性使用的场景

SQL设计层面优化

1.设计中间表，一般针对于统计分析功能，或者实时性不高的需求

2.为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）

3.对于字段太多的大表，考虑拆表（比如一个表有100多个字段）

4.对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联）

5.每张表建议都要有一个主键（主键索引）

6.考虑分表区，数量量特别大的表

SQL语句优化

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。

2.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：

select * from t where num is null

可以在num上设置默认值是0，确保表中num列没有null值，然后这样查询：select * from t where num=0

3.应尽量避免在where子句中使用！=或<>操作符，否则将引起放弃使用索引而进行全表扫描。

4.应尽量避免在where子句中使用or来连接条件，否则将导致引起放弃使用索引而进行全表扫描。如：select * from t where num=10 or num=20

select * from t where num=10

union all

select * from t where num=20

5.in 和 not in也要慎用，否则会导致全表扫描