数据库查询优化问题
来源:互联网 发布:a站软件 编辑:程序博客网 时间:2024/06/04 16:11
原先数据库没有好好学习,感觉一直是似懂非懂的状态,很多原理也不懂,今天抽时间查阅一些资料自己也总结了一些。
本文数据库索引原理部分主要参考博文:http://www.cnblogs.com/aspwebchh/p/6652855.html ,是一片很优秀的文章,值得一看。
不说废话直接开撸:
关系数据库管理系统查询处理可分为四个阶段:查询分析、查询检查、查询优化和查询执行。
查询分析:对查询语句进行扫描、词法分析和语法分析,判断查询语句是否符合SQL语法规则。如果没有错误则进行下一步处理,否则报告语句中出现的错误。
查询检查:对合法的查询语句进行语义检查,注意是检查语句中涉及到的数据库、表对象、关系名、属性等是否存在以及对完整性的检查。如果检查通过则转换成等价的关系代数表达式。
查询优化:查询优化是指选择一个高效执行的查询策略,一般分为代数优化和物理优化。代数优化是指关系表达式的优化,按照一定的规则通过关系代数表达式进行等价变换,改变代数表达式中操作的次序和组合,使得查询更高效。物理优化则是指存取路径和底层操作算法的选择。
查询执行:前面的步骤都是为了这一步。根据优化器得到的执行策略生成查询执行计划,由代码生成器生成执行这个查询计划的语句,然后执行 再 返回结果。
查询树的启发式查询优化:选择运算尽可能先做;把投影运算和选择运算同时进行;把投影同其前后的双目运算结合起来 ;把某些选择同在他前面要执行的笛卡尔积结合起来称为一个运算;找出公共子表达式。
要进行查询优化,首先得知道查询的原理!
数据库索引原理(很重要):主流的数据库系统都是使用平衡树(不是平衡二叉树)作为索引的数据结构,或者称为B Tree 或 B+ Tree。也有一些是使用哈希桶来最为索引的数据结构。
我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,数据库会拒绝建表的语句执行。 事实上, 一个加了主键的表,并不能被称之为「表」。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的「表」很接近。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是上面说的「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错, 再说一遍, 整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键, 一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。
索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。
非聚集索引: 也就是我们平时经常提起和使用的常规索引。
非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段。再数据改变时,DBMS需要一直维护索引结构的正确性。每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引,不同索引之间是相互独立的。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。
非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,归根到底还是要使用聚集索引(主键)来找到真正的数据。
然而, 有一种例外可以不使用聚集索引就能查询出所需要的数据, 这种非主流的方法 称之为「覆盖索引」查询, 也就是平时所说的复合索引或者多字段索引查询。
因为建立索引时,索引指定的字段内容会被同步到索引中,当我们所查询到的属性被指定为索引的字段时,可以通过覆盖索引直接查找到,而不必通过聚集索引查询。
当然,这样引入存在的问题就是增加数据表的数据。
以下是例子:
先看下面这个SQL语句
//建立索引
create index index_birthday on user_info(birthday);
//查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1'
这句SQL语句的执行过程如下
首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值
然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对应的真实数据(数据行)存储的位置
最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果
我们把birthday字段上的索引改成双字段的覆盖索引
create index index_birthday_and_user_name on user_info(birthday, user_name);
这句SQL语句的执行过程就会变为
通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,然而,叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能,如下图
![](file:///F:/%E4%BA%91%E7%AC%94%E8%AE%B0/weixinobU7VjhTIpTdiSbH8tWnKWZ2o08k/882ba044ce8841408fe43ca030dae7b2/-1554738501.jpeg)
参考自博文:http://www.cnblogs.com/aspwebchh/p/6652855.html
阅读全文
0 0
- 数据库查询优化问题
- 数据库查询优化问题笔记
- 数据库优化:优化查询
- 数据库优化--优化查询语句
- Oracle查询性能优化(面试题:数据库查询优化也是常问的问题)
- 数据库查询优化技术
- MYSQL数据库查询优化
- 数据库查询优化
- 数据库查询优化
- 数据库 查询优化
- 优化MySQL数据库查询
- 数据库查询性能优化
- 优化MySQL数据库查询
- 优化MySQL数据库查询
- 数据库查询优化
- 数据库查询优化(上)
- 数据库查询优化(下)
- 数据库查询优化
- IBM与麻省理工学院联合建立AI实验室 承诺投资2.4亿美元
- 论QQ浏览器下如何转载CSDN博客
- 由两个栈组成的队列
- 填写企业小程序信息-公司宣传型小程序源代码下载2-视频教程25
- 浅谈UML中的聚合与组合(两者之间的区别)
- 数据库查询优化问题
- vim最实用的配置脚本文件
- 36Kr皇包车-第1个投资退出案例
- VMware安装Ubuntu 15
- ACboy(队列和栈的基础操作)
- VideoCenter App's README
- jquery 标签切换效果,单击选择效果
- 克隆
- JAVA中多线程实现