[亲测]数据库优化

Myth丶恋晨 2022-03-27 03:48 346阅读 0赞

# 一. 准备数据 #

*  ###### 生成一千万条测试数据(约耗时3-10分钟) ######

create table t_user as 
    	select rownum as id,
            to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as birthday,
            trunc(dbms_random.value(0, 100)) as age,
            dbms_random.string('x', 20) username
    	from dual
            connect by level <= 10000000;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70]

# 二. 数据库优化 #

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

### 2.1 使用索引 ###

*  未使用索引查询，耗时1.125秒  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 1]
 *  设置主键，数据库会自动为主键所在列创建唯一索引

alter table t_user add constraint pk_user_id primary key(id)

*  使用主键，再次查询，耗时0.047秒

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 2]

### 2.2 避免在 where 子句中对字段进行 null 值判断 ###

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

*  对age字段建立普通索引

create index index_age on t_user(age)

*  查询id=0的记录，此时会使用索引，耗时0.032秒  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 3]
 *  设置id=1的记录age值为null

update t_user set age = null where id=1

查询结果：耗时1.062秒

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 4]

*  设置id=1的记录age值为101,查询耗时0.031秒

update t_user set age = 101 where id = 1

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 5]

*  结论

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.  
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值。

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

*  普通查询，会使用索引  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 6]
 *  !=或<>不会sh’y使用全表扫描

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 7]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 8]

### 2.4 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描 ###

*  使用or进行查询，耗时2.016

select * from t_user where id = 1 or username = 'E2NU2Q7OFSZPDBECN2A4'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 9]

![在这里插入图片描述][2019011121314940.png]

*  使用union all代替or进行查询

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 10]  
![在这里插入图片描述][20190111213216297.png]

### 2.5 避免使用 select ###

任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段

*  测试说明：2千万条数据，username表未添加索引
 *  使用select \* 查询，耗时4.672秒

select * from t_user where username = 'PQVQX8L4VF18AJD0ZOWH'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 11]

*  使用字段列表代替\*进行查询，耗时2.469

select id,birthday,age,username from t_user where username = 'PQVQX8L4VF18AJD0ZOWH'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 12]

### 2.6 使用模糊查询like可能导致索引失效 ###

说明：模糊查询like，只能是 like ‘aa%’ 这种前端匹配的才能使用索引，而‘%aa’或’%aa%'将不会使用索引

*  准备工作，为username创建普通索引

create index index_username on t_user(username)

*  使用’ %username%’，耗时3.015秒

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 13]

采用的是全表扫描

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 14]

*  使用’username%’，耗时0.016秒  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 15]  
    使用了索引  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 16]

### 2.7 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。 ###

然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num = @num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num = @num

.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100

应改为:

select id from t where num = 100*2

### 2.8 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描 ###

select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
    select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

应改为:

select id from t where name like 'abc%'
    select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

### 2.9 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 ###

### 2.10 复合索引索引命中问题 ###

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

### 2.11 不要写一些没有意义的查询，如需要生成一个空表结构 ###

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：  
create table \#t(…)

### 2.12 索引并不是越多越好 ###

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

### 2.13 任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段 ###

### 2.14 尽可能的使用 varchar 代替 char ， ###

因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些

### 2.15 尽量使用数字型字段 ###

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。  
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

### 2.15 避免频繁创建和删除临时表，以减少系统表资源的消耗 ###

### 2.16 避免频繁创建和删除临时表，以减少系统表资源的消耗。 ###

临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。

### 2.17 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度 ###

在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

### 2.18 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 ###

### 2.19 尽量避免大事务操作，提高系统并发能力。 ###

### 2.20 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。 ###

**以下推荐个人文章**  
[\[亲测\]Oracle数据库安装与配置  
][Oracle_]

[\[亲测\]Oracle数据库操作  
][Oracle_ 1]

[\[亲测\]Oracle表的创建、修改与删除  
][Oracle_ 2]

[\[亲测\]Oracle数据库约束][Oracle]

[\[亲测\]Oracle数据增删改][Oracle 1]

[\[亲测\]Oracle查询–单表查询,连接查询(一)  
][Oracle_ 3]

[\[亲测\]Oracle查询–子查询,分页查询(二)  
][Oracle_ 4]

[\[亲测\]Oracle查询–单行函数–PL/SQL,分析函数,集合运算(三)  
][Oracle_PL_SQL_]

[Oracle执行计划Explain Plan 如何使用][Oracle_Explain Plan]

[\[亲测\]数据库优化][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70]: /images/20220327/0869ec51c2084c6eaa09f412f1f158c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 1]: /images/20220327/a49c66f01b9b4b9980b4dd666678a8ec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 2]: /images/20220327/e4a60cfc86de4aa6a6b7b7b057cdd9ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 3]: /images/20220327/0ca9e9dfecc541b3b80d040590ac2105.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 4]: /images/20220327/3711bf506407402fbfa8c99216b90729.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 5]: /images/20220327/9511eb8299e346d49a2a819bb289743c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 6]: /images/20220327/e15bd246bd8b496c989ea03a03f88e89.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 7]: /images/20220327/aaea1b9ff9634011879bab23f3cda095.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 8]: /images/20220327/23d3fed461d34fdeb5dc858120f9bf69.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 9]: /images/20220327/705c67b8d14f4666a8cb57c9ce993dcc.png
[2019011121314940.png]: /images/20220327/d5b07e57b50c4f71b2ea4e5ddb177f6c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 10]: /images/20220327/0e50c722a3ff4d7d82a8ff4b652b79c7.png
[20190111213216297.png]: /images/20220327/0891fc8f0288421baca6bd23ffa620f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 11]: /images/20220327/3b02b58c519542dbaabee76ad6801e16.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 12]: /images/20220327/2bf1c95eafa043b3ad5abf81cee5f97a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 13]: /images/20220327/2c68774952bd4b36b2b7a653f91cdf49.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 14]: /images/20220327/3cd007f4ecf341fca1142628d843347a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 15]: /images/20220327/d2585746a7444f3fa1548f66fa2a69ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 16]: /images/20220327/f4ef2f325330457aa1ecf432611620e2.png
[Oracle_]: https://blog.csdn.net/qq_43652509/article/details/86096360
[Oracle_ 1]: https://blog.csdn.net/qq_43652509/article/details/86096706
[Oracle_ 2]: https://blog.csdn.net/qq_43652509/article/details/86096661
[Oracle]: https://blog.csdn.net/qq_43652509/article/details/86096587
[Oracle 1]: https://blog.csdn.net/qq_43652509/article/details/86096497
[Oracle_ 3]: https://blog.csdn.net/qq_43652509/article/details/86292696
[Oracle_ 4]: https://blog.csdn.net/qq_43652509/article/details/86301674
[Oracle_PL_SQL_]: https://blog.csdn.net/qq_43652509/article/details/86303911
[Oracle_Explain Plan]: https://blog.csdn.net/qq_43652509/article/details/86319169
[Link 1]: https://blog.csdn.net/qq_43652509/article/details/86319322