数据库优化理解

最新推荐文章于 2022-11-10 15:30:30 发布

Singllord

最新推荐文章于 2022-11-10 15:30:30 发布

阅读量185

点赞数 1

分类专栏：数据库文章标签：数据库sql 数据库优化

本文链接：https://blog.csdn.net/qq_36160933/article/details/85099033

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

sql语句执行顺序

在同一语句下，从右至左，自下而上执行(where 3…,2…,1…)

order by子句–执行顺序为从左到右排序,很耗资源

1.FROM

2.ON

3.JOIN

4.WHERE

5.GROUP BY

6.WITH CUBE or WITH ROLLUP

7.HAVING

8.SELECT

9.DISTINCT

10.ORDER BY

11.TOP(limit)

表连接两种方式LEFT HASH JOIN和嵌套，LEFT HASH JOIN的效率远大于嵌套。

#对outer join(left or right)条件放在on里会筛选掉更多数据

#放在on
select * from table1 as a
inner join table2 as b on a.id=b.id and a.status=1

#放在where
select * from table1 as a
inner join table2 as b on a.id=b.id
where a.status=1

索引

原理：基于平衡二叉树来存储表的相关信息。

聚集索引：通过主键把整张表排列成平衡二叉树的形式，速度->log(n)。
非聚集单索引：通过把索引列和主键建立成二叉树的形式，通过索引列查找到主键，再通过主键查找真实列。
联合索引：从左自右的顺序存储数据，非叶子节点存储的是第一个关键字的索引，而叶子节点存储的则是三个关键字col1、col2、col3…关键字的数据。只能通过左侧的列再找相对右则的列…类似于HashMap的hash值（第一列），equal比较（第二列）。

建议

索引一般加在查询条件的关键字上，如果有多个查询条件关键字，还可以添加组合索引，写sql的时候需要注意，索引字段和sql字段（类型）需要保持一致，否则索引会无效。
建立索引的字段要区分度比较高，比如user表中有一个性别字段，性别字段无非男女两种值，区分度不好，建立索引效果不好，要选择区分度高的字段。
建立组合索引，可以持续提升sql运行效率，但是也不要盲目，同样的要注意区分度，如果区分度不够高，就不要加了，多个字段，尽可能把区分度高的字段放在前面，另外，还要注意索引长度，这个索引要同时兼顾索引长度和区分度的平衡
索引会大幅提升查询效率，但是也会损耗查询后修改效率，要注意兼顾平衡，使用在一次插入，多次查询的表上效果最好，同时要注意的是，组合索引会不可避免的增加索引长度，会增加索引存储空间，注意索引长度和区分度平衡
如果支持全文索引，正常使用全文索引都是使用lunce，以及在其之上的solr和现在正火的elastisearch

优化

不要在查询 =前面使用函数，否则会导致索引不生效，举个栗子，where str=substring(“hello world”,6,8),这样是可以走索引的，但是 where substring(str,6,8)=“hello world” 是不会命中索引的。
union替换or（针对索引列）
使用or会对全表进行查询（索引无效）
避免在索引列上使用is null 和 is not null

避免在可能为空值的列上建立索引。

（组合索引）总是使用第一列

原因：略

停用索引的语句：’!=’,’||’,’+’,相同的索引列不能相互比较

索引只能告诉用户什么存在于表中。

全文检索：

从非结构化数据中提取出的然后重新组织的信息->索引。

这种先建立索引，再对索引进行搜索的过程->全文检索(Full-text Search)。

检索的数据主要分为两种：1. 结构化数据。 2. 非结构化数据。结构化数据主要运用sql进行检索，非结构化的数据主要运用建立索引，再对索引进行查找。

检索方式：字典-倒排表：建立一组关键词（字典），指向包含该关键词的文件（倒排表）。通过对字典的查找，对应到相关文件。创建索引过程慢，但利用性高
步骤：
- 将文档分词(Tokenizer)(转为词次（Token）)：1. 将文档分成单个单个词。 2. 去除标点。 3. 去除停用词。
- 将单词传递给语言处理组件(Linguistic Processor)（转为词元（Term））：英语：1. 转为小写。 2. 转为词根形式（如cars->car，叫stemming）。 3. 转为词根形式（如drove->drive，叫lemmatization）
- 将Term传给索引组件（Indexer）:1. 利用Term创建一个字典（Term-DocumentID）。 2. 将字典按字母顺序进行排序。 3. 合并相同词元，组成文档倒排表。（包含定义：1. Document Frequency 即文档频次，表示总共有多少文件包含此词(Term)。 2. Frequency 即词频率，表示此文件中包含了几个此词(Term)。）

ElasticSearch全文搜索

Cluster集群：一个node服务节点的集合。
Node服务节点：搜索基础服务。
Index索引：文档关键信息标识。
Type类型：同类文档。
Document文档：基础文档。
Shards & Replicas碎片和副本：
- shard分片是把一个索引的数据进行hash分片（一般是文件ID）存储、查找。
- replica是shard的副本，用于容错。shard和其副本（replica）不能存在于同一个node中，否则就不能起到容错作用。
- shard和其replica都执行成功进返回成功。（请求执行时shard执行成功后，同步发送消息到其replica）。

ES与数据库对应关系对照：

关系型数据库 => 数据库 => 表 => 行 => 列
ElasticSearch => 索引 => 类型 => 文档 => 字段

查询优化

1. 嵌套表查询

多个表嵌套查询，最内层的表最先查询（最左），所以选择基础表很重要（可减少查询基数）

数据最少的表作为基表
交叉表作为基表

2. Where子句连接顺序

where子句的执行顺序是自下而上的，应该把过滤大量数据的条件写在where末尾

3. select子句中避免使用 ‘*’

‘*’ 在数据库解析中会通过数据字典自动转换成各列名，会消耗更多的时间

4. 减少访问数据库的次数

数据库系统在读取访问时会：解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等操作

5. 访问量设置

在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200

6. 使用Decode函数减少处理时间

使用Decode函数可以避免重复扫描相同记录或重复连接相同的表

7. 整合简单、无关联的数据库访问（同4）

如果有几个摊铺机的数据库查询语句，可以把他们合并到同一个查询中去（即使它们毫无关系）

8. 用Truncate清空你表中全部数据

Truncate（类似于不带where子句的delete子句，清空表，并归零处境字段）不使用rollback机制，效率更高。

9. 尽可能多地使用commit

commit会释放数据库所占用的许多资源：

回滚段数据
被程序语句所获得的锁
redo log buffer 中的空间
上述三个资源的管理花费

10. 使用表的别名

在多表查询的时候，尽量使用表的别名，并把它加在第个列上，这样可以减少解析列的时间并减少列歧义所引发的错误

11. 避免使用In或Not In

在需要对另一个表进行联接时，使用join或exists代替in或not in。

NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)

12. 定期重构索引

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

13. Exists替换Distinct

EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果

14. SQL语句用大写

数据库在解析SQL语句时，总是把小写语句转换成大写再执行。

15. java中尽量少用连接符"+"连接字符串。

原因：略

16. 用">=“替换”>"

“>=” 会直接跳到第一个x等于4的记录，而">" 会定位到x等于3的记录并向前扫描第一个大于3的记录。

17. in代替or

#低效:
SELECT * FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
#高效：
SELECT * FROM LOCATION WHERE LOC_IN IN (10,20,30);

18. union-all与union

union-all不对组合进行排序与去重-简单拼接
union对组合进行排序，去重。

19. 避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。通常，带有 UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写。如果你的数据库的SORT_AREA_SIZE调配得好。使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

20. 优化group by

先排序，后分组

将不需要的记录在group by前过渡掉。

21. 优化order by

双路排序：Mysql4.1之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段。文件的磁盘IO非常耗时的，所以在Mysql4.1之后，出现了第二种算法，就是单路排序。

单路排序：从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。

选择需要的列，避免使用SELECT * ,首先，字典映射需要时间，其次超出缓冲区大小会自动把单路排序转为双路排序。
根据业务需求，提高sort_buffer_size的大小（它是针对每个进程的）。

Singllord

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库优化理解

sql语句执行顺序在同一语句下，从右至左，自下而上执行(where 3…,2…,1…)order by子句–执行顺序为从左到右排序,很耗资源1.FROM2.ON3.JOIN4.WHERE5.GROUP BY6.WITH CUBE or WITH ROLLUP7.HAVING8.SELECT9.DISTINCT10.ORDER BY11.TOP(limit)表连接两...
复制链接

扫一扫