精华内容
下载资源
问答
  • 数据库基本原理

    千次阅读 2019-05-09 19:58:14
    数据库基本原理我对DB的理解1、数据库的组成:存储+实例不必多说,数据当然需要存储;存储了还不够,显然需要提供程序对存储的操作进行封装,对外提供增删改查的API,即实例。一个存储,可以对应多个实例,这将提高...
    前言

    今天我将站在程序员的角度以MySQL为例探索数据库的奥秘!

    数据库基本原理

    我对DB的理解

    1、数据库的组成:存储+实例

    不必多说,数据当然需要存储;存储了还不够,显然需要提供程序对存储的操作进行封装,对外提供增删改查的API,即实例。

    一个存储,可以对应多个实例,这将提高这个存储的负载能力以及高可用;多个存储可以分布在不同的机房、地域,将实现容灾。

    2、按Block or Page读取数据

    用大腿想也知道,数据库不可能按行读取数据(Why?^_^)。实质上,数据库,如Oracle/MySQL,都是基于固定大小(比如16K)的物理块(BlockorPage,我这里就不区分统一称为Block)来实现调度和管理的。要知道Block是数据库的概念,如何对应到文件系统呢?显然需要指出“这个Block的地址在哪里”,当查找到地址后,读取固定大小的数据就相当于完成了Block的读取了。

    数据库很聪明的,它不会仅仅只读取需要读取的Block,它还会替我们把附近的Block块都读取加载至内存。实际上,这是为了减少IO次数,提高命中率。事实上,一个Block块的附近Block也是热点数据,这种处理方式很有必要!

    3、磁盘IO是数据库的性能瓶颈

    毫无疑问,数据在磁盘上,少不了磁盘IO。什么磁头旋转,定位磁道,寻址的过程,就不说了,我们是程序员,也管不了这些。但是这个过程确实是非常耗时的,和内存读取不是一个数量级,所以后来出现了很多方式来减少IO,提升数据库性能。

    比如,增加内存,让数据库把数据更多的加载至内存。内存虽好,但也不能滥用,为什么这么说呢?假设数据库中有100G数据,如果都加载至内存,也就说数据库要管理100G磁盘数据+100G内存数据,你说累不累?(数据库要处理磁盘和内存的映射关系,数据的同步,还要对内存数据进行清理,如果涉及数据库事务,又是一系列复杂操作......)不过这里需要指出的是,为了加快内存查找速度,数据库一般对内存进行HASH存放。

    比如,利用索引,索引相比内存,是一个性价比非常高的东西,后文详细介绍MySQL的索引原理。

    比如,利用性能更好的磁盘...(和咱们就没关系呢)

    4、提出一些问题思考下:

    为什么我们说利用delete删除一个表的数据较trancate一个表要慢?

    【一个按行查找删除,多费劲;一个基于Block的体系结构删除】

    为什么我们说要小表驱动大表?

    【小表驱动大表会快?什么鬼?M*N和N*M不是一样的么?有鬼的地方,就有索引!】

    探索MySQL索引背后的原理

    对于绝大数的应用系统,读写比例在10:1,甚至100:1,而且insert/update很难出现性能问题,遇到最多的,最棘手的就是select了,select优化是重中之重,显然少不了索引!

    说起MySQL的索引,我们会冒出很多这些东西:BTree索引/B+Tree索引/Hash索引/聚集索引/非聚集索引...这么多,晕头!

    索引到底是什么,想解决什么问题?

    老生常谈了,官网说MySQL索引是一种数据结构,索引的目的就是为了提高查询效率。

    说白了,不使用索引的话,磁盘IO次数比较多!要想减少磁盘IO次数,怎么办?

    我们想通过不断缩小想要获取的数据的范围来筛选出最终想要的结果,把每次查找数据的磁盘IO次数控制在一个很小的数量级,最好是常数数量级。

    为了应对上述问题,B+Tree索引出来了!

    Hello,B+Tree

    在MySQL中,不同存储引擎对索引的实现方式是不同的,这里将重点分析MyISAM和Innodb。

    MyISAM引擎的B+Tree索引结构

    我们知道对于MyISAM引擎而言,数据文件和索引文件是分离的。从图中也可以看出,通过索引查找到后,就得到了数据的物理地址,然后根据地址定位数据文件中的记录即可。这种方式也叫"非聚集索引"。

    而对于Innodb引擎而言,数据文件本身是索引文件!通俗点说,叶子节点上,MyISAM存储的是记录的物理地址,而Innodb上存储的是数据内容,这种方式即"聚集索引"。

    另外一点需要注意的是,对于Innodb而言,主键索引中叶子节点存储的是数据内容,而普通索引的叶子节点中存储的是主键值!也就是说,对于Innodb的普通索引字段查找,先通过普通索引的B+Tree查找到主键后,然后通过主键索引的B+Tree进行查找。从这里你可以看出,对于Innodb而言,主键的建立非常重要!

    而对于MyISAM而言,主键索引和普通索引仅仅的区别在于主键只需要查找到一条记录即可停止,而普通索引允许重复,找到一条记录后需要继续查找,在结构上没有区别,如上图所示。

    深入B+Tree

    提几个问题:

    ①.为什么B+Tree把真实的数据放到叶子节点,而不是内层节点?

    ②.为什么我们说索引字段要尽可能短,最好是单调递增的?

    ③.为什么复合索引存在最左匹配原则?

    ④.范围查询(>,<,between,like)对最左匹配有什么影响?

    关于B+Tree的一些数学理论,咱们就不玩了,至少一点可以肯定的是:数据表的数据量N=F(树的高度h,每个Block存储的索引的个数m)。在N一定的情况下,索引字段越小,那么m会越大,这意味着h将越小!树越低,当然查找的更快!

    如果内层节点存放真实的数据,显然m会变小,树将变高。

    在实际应用中,我们应该尽可能采用单调递增的字段作为主键,一方面不会使得索引的数据结构变大,减小了索引占用的空间;另一方面也不会频繁的分裂B+Tree,使得效率下降。

    比如复合索引(name,age,sex),B+Tree会优先比较name来确定下一步的搜索方向。如果突然来了个(age,sex),根本上就无从下手。这也是符合常理的,对于一本书,我们说“找到第几章第几节的XXX”,从没有听说过“找到第几节的XXX”!这是复合索引的重要特性,即最左匹配特性。

    假设存在复合索引(name,age,sex),我们在进行select的时候,并没有按照这个顺序进行,而是sex='man'andname='zfz'andage=27,是否会使用索引呢?数据库是很聪明的,在SQL优化的时候,会自动帮助我们调整!但是如果缺失了复合索引的第一列,数据库也将无能为力呢。

    对于最左匹配,MySQL会一直向右匹配直到遇到范围查询就停止匹配。什么意思?比如复合索引(name,age,sex),对于name='zhangfengzhe'andage>26andsex='man',实际上只利用到了复合索引的name列。

    想利用索引,就得“干净”

    什么叫“干净”?就是不要让索引参与计算!比如在索引上应用函数,很可能导致索引失效。为什么呢?

    其实不用想,B+Tree上存储的是数据,要比较的话,需要把所有的数据都应用上函数,显然成本太大。

    想建立索引,看看区分度

    索引虽然物美价廉,但是也别乱来。count(distinctcol)/count(*)可以算一下col的区分度,显然对于主键而言,就是1。区分度太低的话,可以考虑下,是否还有必要建立索引呢?

    Hash索引

    这里并不是要深入分析Hash索引,而是要说明一下Hash的思想真是无处不在!

    在MySQL的Memory存储引擎中,存在hash函数,给一个key,通过hash函数进行计算得到地址,所以通常情况下,hash索引查找,会非常快,O(1)的速度。但是也存在hash冲突,和HashMap一样,通过单链表的形式解决。

    思考下,hash索引是否支持范围查询呢?

    显然是不支持的,它只能给一个KEY去查找。就如同HashMap一样,查找key包含"zhangfengzhe"的,会很快么?

    SQL优化神器:explain

    SQL优化的场景很多,网上的技巧也很多,完全记不住!

    要想彻底解决这个问题,我想只有把索引背后的数据结构和原理做适当的理解,遇到书写SQL或者SQL慢查询的时候,我们有基础去分析,再利用好explain工具去验证,就应该问题不大呢。

    explain查询的结果,可以告诉你哪些索引正在被使用,表是如何被扫描的等等。这里我将演示个Demo。

    数据表student:

    注意复合索引(age,address)

    符合最左前缀匹配

    复合索引失效

    OK,到这里,准备结束了,查询容易,优化不易,且写且珍惜!

    扩展阅读

    一个不可思议的MySQL慢查分析与解决

    MySQL大数据量分页查询方法及其优化

    MySQL性能优化:索引和查询优化

    深入理解synchronized关键字

    深入了解Java之虚拟机内存

    Redis为何这么快--数据存储角度

    来源:https://www.jianshu.com/p/aa1f0f29b4f8

    展开全文
  • 数据库基本原理及应用_oracle 比较适合刚开始学习数据库的 有需要的可以下载哦 如果觉得好的话 给个好评 谢谢
  • 关系数据库基本原理

    2011-12-12 07:21:35
    第三讲\第2章 关系数据库基本原理系列文档中的第三档
  • 数据库基本原理课件 内容包括数据库基本概念、SQL等
  • 数据库原理,讲述数据库基本原理,简明扼要,很不错
  • 数据库基本原理ppt

    2008-10-23 19:33:47
    计算机等级考试一级,数据库初学者,内有详细的数据库基本原理、Access 数据库基础知识、库和表、 查询四四章PPT详解,计算机一级考虽不是难事,却也非基础不可。
  • 1.什么是数据库 数据库是数据的集合;是对现实中一个企业的建模; 2.数据库管理系统 用来存储和管理数据库的一种系统软件 3.为什么要用数据库而不是文件 文件:文件是操作系统提供的最简单的,最基本的存储数据的机制,...
  • 自用资料,其他人不建议下载
  • 为初学者介绍sqlserver基本原理的好书。是入门关系数据库技术的不二选择。深入浅出的介绍了安装和配置、数据库的设计和管理、SQL 语言基础、创建索引、设计数据完整性、存储过程、触发器、安全性、数据库的备份和...
  • 数据库关系模型规范化理论 第一范式到第六范式
  • HBase概述 > * HBase是一个构建在HDFS上的分布式列存储系统 * HBase是基于Google BigTable模型开发的,典型的key/value系统 * HBase是Apache Hadoop生态系统中的重要一员,主要用于海量结构化数据存储 ...

    HBase概述

    >
    * HBase是一个构建在HDFS上的分布式列存储系统
    * HBase是基于Google BigTable模型开发的,典型的key/value系统
    * HBase是Apache Hadoop生态系统中的重要一员,主要用于海量结构化数据存储
    * 从逻辑上讲,HBase将数据按照表、行和列进行存储
    * 与hadoop一样,Hbase目标主要依靠横向扩展,通过不断增加廉价的商用服务器,来增加计算和存储能力

    HBase表的特点

    >
    * 大:一个表可以有数十亿行,上百万列
    * 无模式:每行都有一个可排序的主键和任意多的列,列可以根据需要动态的增加,同一张表中不同的行可以有截然不同的列
    * 面向列:面向列(族)的存储和权限控制,列(族)独立检索
    * 稀疏:空(null)列并不占用存储空间,表可以设计的非常稀疏
    * 数据多版本:每个单元中的数据可以有多个版本,默认情况下版本号自动分配,是单元格插入时的时间戳
    * 数据类型单一:Hbase中的数据都是字符串,没有类型

    HBase数据逻辑视图

    物理存储

    >
    * Table中所有行都按照row key的字典序排列
    * Table在行的方向上分割为多个Region
    * Region按大小分割的,每个表开始只有一个region,随着数据增多,region不断增大,当增大到一个阀值的时候,region就会等分会两个新的region,之后会有越来越多的region
    * Region是Hbase中分布式存储和负载均衡的最小单元,不同Region分布到不同RegionServer上
    如图:

    HBase写入数据流程

    如下是官方给出的数据架构图:

    当client向HRegionServer发起Table.put(put)请求时,会将请求给对应的HRegion实例来处理。
    第一步是决定数据是否需要写入由HLog类实现的预写日志中(WAL);
    一旦数据被写入WAL中,数据接下来被放到memstore内存模块中。同时还会检查memstore是否已经满了,
    如果满了,就会被请求刷写内存数据到磁盘中,由后台另外一个HRegionServer的线程把数据写成HDFS
    中的一个新HFile,保存最后的写入序列号,系统就知道那些数据现在被持久化了。
    (shell中的flush命令手动持久化内存数据)

    HBase读取数据流程

    -ROOT- 和 .meta. 表

    >
    从存储结构和操作方法的角度来说,-ROOT-、.META.与其他表没有任何区别。
    它们与众不同的地方是HBase用它们来存贮一个重要的系统信息:
    >
    * -ROOT-:记录.META.表的Region信息
    * .META.:记录用户表的Region信息
    >
    其中-ROOT-表本身只会有一个region,这样保证了只需要三次跳转,就能定位到任意region

    .meta. 表结构

    >

    逻辑视图如下:

    .META.表中每一行记录了一个Region的信息
    1). RowKey
    RowKey就是Region Name,它的命名形式是TableName,StartKey,TimeStamp.Encoded.
    其中 Encoded 是TableName,StartKey,TimeStamp的md5值。
    例如:

    mytable,,1438832261249.ea2b47e1eba6dd9a7121315cdf0e4f67.  
    

    表名是mytable,StartKey为空,时间戳是1438832261249,前面三部分的md5是:

    echo -n "mytable,,1438832261249" | md5sum   # -n选项表示不输出换行符
    2ea2b47e1eba6dd9a7121315cdf0e4f67  -
    

    2) Column Family
    .META.表有两个Column Family:info 和 historian
    其中info包含了三个Column:

    • regioninfo:region的详细信息,包括StartKey、EndKey以及Table信息等等
    • server:管理该region的 RegionServer 的地址
    • serverstartcode:RegionServer 开始托管该region的时间

    这个Column Family是用来追踪一些region操作的,例如open、close、compact等。事实证明这非常的麻烦,所以在想出一个更好的解决方案之前我们禁用了此功能。这个列族会保持向后兼容。
    综上所述,.META.表中保存了所有用户表的region信息,在进行数据访问时,它是必不可少的一个环节。当Region被拆分、合并或者重新分配的时候,都需要来修改这张表的内容 来保证访问数据时能够正确地定位region。

    -ROOT- 表结构

    当用户表特别大时,用户表的region也会非常多。.META.表存储了这些region信息,也变得非常大,这时.META.自己也需要划分成多个Region,托管到多个RegionServer上。
    这时就出现了一个问题:当.META.被托管在多个RegionServer上,如何去定位.META.呢?HBase的做法是用另外一个表来记录.META.的Region信息,就和.META.记录用户表的Region信息一样,这个表就是-ROOT-表。
    在 HBase Shell 里对-ROOT-表进行 scan 和 describe :

    逻辑视图如下:

    可以看出,除了没有historian列族之外,-ROOT-表的结构与.META.表的结构是一样的。另外,-ROOT-表的 RowKey 没有采用时间戳,也没有Encoded值,而是直接指定一个数字。
    -ROOT-表永远只有一个Region,也就只会存放在一台RegionServer上。—— 在进行数据访问时,需要知道管理-ROOT-表的RegionServer的地址。这个地址被存在 ZooKeeper 中。

    展开全文
  • 数据库基本存储原理  基本存储单元——页 数据库文件存储是以页为存储单元的,一个页是8K(8192Byte),一个页就可以存放N行数据。我们表里的数据都是存放在页上的,这种叫数据页。还有一种页存放索引数据的,叫...

      数据库基本存储原理

     基本存储单元——

    数据库文件存储是以页为存储单元的,一个页是8K8192Byte),一个页就可以存放N行数据。我们表里的数据都是存放在页上的,这种叫数据页。还有一种页存放索引数据的,叫索引页。

    同时,页也是IO读取的最小单元(物理IO上不是按行读取),也是所有权的最小单位。如果一页中包含了表A的一行数据,这页就只能存储表A的行数据了。或是一页中包含了索引B的条目,那这页也仅仅只能存储索引B的条目了。每页中除去存储数据之外,还存储一些页头信息以及行偏移以便SQL Server知道具体每一行在页中的存储位置。

    数据库的基本物理存储单元是页,一个表由很多个页组成,那这些页又是如何组织的呢?我们一般都会对表创建索引,这些索引又是如何存储的呢?不要走开,请看下文。

     /索引的存储结构

    如下图,是一个B树(二叉搜索树)的示例,都是小的元素放左边,大的元素放右边,依次构造的,比如要查找元素9,从根节点开始,只要比较三次就找到他了,查询效率是非常高的。

    B+树和B-树都是B树的变种,或者说是更加高级的复杂版本(关于B树的资料,有兴趣可以自己去学习,这里只是抛砖引玉)。B+树和B-树是数据库中广泛应用的索引存储结构,它可以极大的提高数据查找的效率。前面说了数据库存储的基本单元是页,因此,索引树上的节点就是页了。

    因此不难看出,索引的主要优点和目的就是为了提高查询效率。

     

    为了保证数据的查询效率,当新增、修改、删除数据的时候,都需要维护这颗索引树,就可能会出现分裂、合并节点(页)的情况(这是树的结构所决定的,想要更好理解这一点,可以尝试自己代码实现一下B-B+树)。好!重点来了!

    索引的缺点:

    • 当新增、修改、删除数据的时候,需要维护索引树,有一定的性能影响;
    • 同上面,在频繁的树维护过程中,B树的页拆分、合并会造成大量的索引碎片,又会极大的印象查询效率 ,因此索引还需要维护;
    • 非聚集索引需要额外的存储空间,不过这个一般问题都不是很大,但是需要注意的一个问题;

     聚集索引

    聚集索引决定了表数据的物理存储顺序,也就是说表的物理存储是根据聚集索引结构进行顺序存储的,因此一个表只能有一个聚集索引。如下图,就是一个聚集索引的树结构:

    • 所有数据都在叶子节点的页上,在叶子节点(数据页)之间有一个链指针,这是B+树的特点;
    • 非叶子节点都是索引页,存储的就是聚集索引字段的值;
    • 表的物理存储就是依据聚集索引的结构的,一个表只能有一个聚集索引;

    聚集索引的所有的数据都存储在叶子节点上,数据查询的复杂度都是一样的(树的深度),按照聚集索引列查找数据效率是非常高的。上面说了,聚集索引决定了表的物理存储结构,那如果没有创建聚集索引,会如何呢?——表内的所有页都无序存放,是一个无序的堆结构。堆数据的查询就会造成表扫描,性能是非常低的。

    因此聚集索引的的重要性不言而喻,一般来说,大多会对主键建立聚集索引,大多数普通情况这么做也可以。但实际应用应该尊从一个原则就是频繁使用的、排序的字段上创建聚集索引

     非聚集索引

    除了聚集索引以外的其他索引,都称之为非聚集索引,非聚集索引一般都是为了优化特定的查询效率而创建的。非聚集索引也是B树(B+树和B-树)的结构,与非聚集索引的存储结构唯一不一样的,就是非聚集索引中不存储真正的数据行,因为在聚集索引中已经存放了所有数据,非聚集索引只包含一个指向数据行的指针即可。

    • 非聚集索引的创建会单独创建索引文件来存储索引结构,会占用一定存储空间,就是用空间换时间;
    • 非聚集索引的目的很单纯:提高特定条件的查询效率,一个表有可能根据多种查询需求创建多个非聚集索引;

    https://images.cnblogs.com/cnblogs_com/changbluesky/WindowsLiveWriter/SQLServer6IndexandTSQLTuning_DD36/image_4.png

    数据查询SQL简单来看,分为两个部分:SELECT****  Where ****,因此索引的创建也是根据这两部分来决定的。根据这两点,有两种主要的索引形式:复合索引和覆盖索引,在实际使用中,根据具体情况可能都会用到,只要能提高查询效率就是好索引。

    覆盖索引:就是在索引中包含的数据列(非索引列,SELECT需要的列),这样在使用该索引查询数据时就不会再进行键查找(也叫书签查找)了。

    复合索引:主要针对Where中有多个条件的情况,索引包含多个数据列。在使用复合索引时,应注意多个索引键的顺序问题,这个是会影响查询效率的,一般的原则是唯一性高的放前面,还有就是SQl语句中Where条件的顺序应该和索引顺序一致。

     

     索引碎片

    前面说过了,索引在使用一段时间后(主要是新增、修改、删除数据,如果该页已经存储满了,就要进行页的拆分,频繁的拆分,会产生较多的索引碎片)会产生索引碎片,这就造成了索引页在磁盘上存储的不连续。会造成磁盘的访问使用的是随机的i/o,而不是顺序的i/o读取,这样访问索引页会变得更慢。如果碎片过多,数据库是可会能不使用该索引的(她嫌弃你太慢了,数据库会选择一个更优的执行计划)。

    解决这个问题主要是两种方法:

    第一种是预防:设置页的填充因子

    意思就是在页上设置一段空白区域,在新增数据的时候,可以使用这段空白区域,可以一定的避免页的拆分,从而减少索引碎片的产生。

    填充因子就是用来描述这种页中填充数据的一个比例,一般默认是100%填充的。如果我们修改填充因子为80%,那么页在存储数据时,就会剩余20%的剩余空间,这样在下次插入的时候就不会拆分页了。 那么是不是我们可以把填充因子设置低一点,留更多的剩余空间,不是很好嘛?当然也不好,填充因子设置的低,会需要分配更多的存储空间,叶子节点的深度会增加,这样是会影响查询效率的,因此,这是要根据实际情况而定的。

    那么一般我们是怎么设置填充因子的呢,主要根据表的读写比例而定的。如果读的多,填充因子可以设置高一点,如100%,读写各一半,可以80~90%;修改多可以设置50~70%

    第二种是索引修复:定期对索引进行检查、维护,写一段SQL检查索引的碎片比例,如果碎片过多,进行碎片修复或重建,定期执行即可。具体可以参考本文末尾的相关参考资料。

    https://images0.cnblogs.com/blog/151257/201308/13102931-26c04edc675741899452a9ab514d1072.png

     索引使用总结

    • 创建索引的的字段尽量小,最好是数值,比如整形int等;
    • 对于频繁修改的字段,尽量不要创建索引,维护索引的成本很高,而且更容易产生索引碎片;
    • 定期的索引维护,如索引碎片的修复等;
    • 不要建立或维护不必要的重复索引,会增加修改数据(新增、修改、删除数据)的成本;
    • 使用唯一性高的字段创建索引,切不可在性别这样的低唯一性的字段上创建索引;
    • SQL语句中,尽量不要在Where条件中使用函数、运算符或表达式计算,会造成索引无法正常使用;
    • 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描;
    • 应尽量避免在 where 子句中使用!=<>操作符,否则将导致引擎放弃使用索引而进行全表扫描;

      事务与锁

    事务就是作为一个逻辑工作单元的SQL语句,如果任何一个语句操作失败那么整个操作就被失败,以后操作就会回滚到操作前状态,或者是上个节点。为了确保要么执行,要么不执行,就可以使用事务。而锁是实现事务的关键,锁可以保证事务的完整性和并发性。

    这部分的理论性太强了,不如看看下文章末尾的参考资料更好(博客园的高质量文章还是相当多的),简单总结一下就好了!

    和在.NET中的锁用途类似,数据库中的锁也是为了解决在并发访问时出现各种冲突的一种机制。

     

      题目答案解析:

    1. 索引的作用?和它的优点缺点是什么?

    索引就一种特殊的查询表,数据库的搜索引擎可以利用它加速对数据的检索。索引很类似与现实生活中书的目录,不需要查询整本书内容就可以找到想要的数据。缺点是它减慢了数据录入的速度,同时也增加了数据库的尺寸大小。

    2. 介绍存储过程基本概念和 她的优缺点

    存储过程是一个预编译的SQL语句,他的优点是允许模块化的设计,也就是说只需创建一次,在该程序中就可以调用多次。例如某次操作需要执行多次SQL,就可以把这个SQL做一个存储过程,因为存储过程是预编译的,所以使用存储过程比单纯SQL语句执行要快。缺点是可移植性差,交互性差。

    3. 使用索引有哪些需要注意的地方?

    • 创建索引的的字段尽量小,最好是数值,比如整形int等;
    • 对于频繁修改的字段,尽量不要创建索引,维护索引的成本很高,而且更容易产生索引碎片;
    • 定期的索引维护,如索引碎片的修复等;
    • 不要建立或维护不必要的重复索引,会增加修改数据(新增、修改、删除数据)的成本;
    • 使用唯一性高的字段创建索引,切不可在性别这样的低唯一性的字段上创建索引;
    • SQL语句中,尽量不要在Where条件中使用函数、运算符或表达式计算,会造成索引无法正常使用;
    • 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描;
    • 应尽量避免在 where 子句中使用!=<>操作符,否则将导致引擎放弃使用索引而进行全表扫描;

    4. 索引碎片是如何产生的?有什么危害?又该如何处理?

    索引在使用一段时间后(主要是新增、修改、删除数据,如果该页已经存储满了,就要进行页的拆分,频繁的拆分,会产生较多的索引碎片)会产生索引碎片。

    索引碎片会严重印象数据的查询效率,如果碎片太多,索引可能不会被使用。

    碎片的处理方式主要有两种:

    第一种是预防:设置页的填充因子

    意思就是在页上设置一段空白区域,在新增数据的时候,可以使用这段空白区域,可以一定的避免页的拆分,从而减少索引碎片的产生。

    填充因子就是用来描述这种页中填充数据的一个比例,一般默认是100%填充的。如果我们修改填充因子为80%,那么页在存储数据时,就会剩余20%的剩余空间,这样在下次插入的时候就不会拆分页了。 那么是不是我们可以把填充因子设置低一点,留更多的剩余空间,不是很好嘛?当然也不好,填充因子设置的低,会需要分配更多的存储空间,叶子节点的深度会增加,这样是会影响查询效率的,因此,这是要根据实际情况而定的。

    那么一般我们是怎么设置填充因子的呢,主要根据表的读写比例而定的。如果读的多,填充因子可以设置高一点,如100%,读写各一半,可以80~90%;修改多可以设置50~70%

    第二种是索引修复:定期对索引进行检查、维护,写一段SQL检查索引的碎片比例,如果碎片过多,进行碎片修复或重建,定期执行即可。具体可以参考本文末尾的相关参考资料。

    5. 锁的目的是什么?

    主要解决多个用户同时对数据库的并发操作时会带来以下数据不一致的问题:

    • 丢失更新,同时修改一条数据
    • 读脏,A修改了数据后,B读取后A又取消了修改,B读脏
    • 不可重复读,A用户读取数据,随后B用户读取该数据并修改,此时A用户再读取数据时发现前后两次的值不一致
    • 还有一种是幻读,这个情况好像不多。

    并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

    6. 锁的粒度有哪些?

    • 数据库锁:锁定整个数据库,这通常发生在整个数据库模式改变的时候。
    • 表锁:锁定整个表,这包含了与该表相关联的所有数据相关的对象,包括实际的数据行(每一行)以及与该表相关联的所有索引中的键。
    • 区段锁:锁定整个区段,因为一个区段由8页组成,所以区段锁定是指锁定控制了区段、控制了该区段内8个数据或索引页以及这8页中的所有数据行。
    • 页锁:锁定该页中的所有数据或索引键。
    • 行或行标识符:虽然从技术上将,锁是放在行标识符上的,但是本质上,它锁定了整个数据行。

    7. 什么是事务?什么是锁?

    事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组,如果任何一个语句操作失败那么整个操作就被失败,以后操作就会回滚到操作前状态,或者是上个节点。为了确保要么执行,要么不执行,就可以使用事务。要将所有组语句作为事务考虑,就需要通过ACID测试,即原子性,一致性,隔离性和持久性。 
    锁是实现事务的关键,锁可以保证事务的完整性和并发性。

    8. 视图的作用,视图可以更改么?

    视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作,隐藏具体的细节,保护数据;视图创建后,可以使用与表相同的方式利用它们。

    视图的目的在于简化检索,保护数据,并不用于更新。

    9. 什么是触发器(trigger)? 触发器有什么作用?

    触发器是数据库中由一定时间触发的特殊的存储过程,他不是由程序掉用也不是手工启动的。触发器的执行可以由对一个表的insert,delete, update等操作来触发,触发器经常用于加强数据的完整性约束和业务规则等等。

    10. SQL里面IN比较快还是EXISTS比较快?

    这个题不能一概而论,要根据具体情况来看。IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

    如果查询语句使用了not in,那么对内外表都进行全表扫描,没有用到索引;而not exists的子查询依然能用到表上的索引。所以无论哪个表大,用not exists都比not in 要快。参考资料:http://www.cnblogs.com/seasons1987/archive/2013/07/03/3169356.html

    11. 维护数据库的完整性和一致性,你喜欢用触发器还是自写业务逻辑?为什么?

    尽可能使用约束,如check、主键、外键、非空字段等来约束。这样做效率最高,也最方便。其次是使用触发器,这种方法可以保证,无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑,但这样做麻烦,编程复杂,效率低下。

    文章来源:http://www.cnblogs.com/anding

    自己总结:

    != <> 都是不等于 用法一致 不包含空值

    交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。 

    如果不加任何限制条件,将得到的是数据的笛卡儿积

     

    INNER JOIN 内连接=join   隐式  用比较运算符号  a.i=b.id

    Left join 左连接 

    Right join 右连接

    Full join全连接

    Cross join 交叉连接   隐式 a.id=1

     

    SQL LIKE子句使用通配符运算符比较相似的值。:

    百分号 (%):百分号代表零个,一个或多个字符

    下划线 (_):下划线表示单个数字或字符

    查询以 "g" 开始的: like ‘g%’

    查询以 "g" 结尾的: like ‘%g’

    第一个字符之后是 "eorge" 的人: LIKE '_eorge'

    "C" 开头,然后是一个任意字符,然后是 "r",然后是任意字符,然后是 "er":

    LIKE 'C_r_er'

    以 "A" 或 "L" 或 "N" 开头的人:: '[ALN]%'

    不以 "A" 或 "L" 或 "N" 开头的人: LIKE '[!ALN]%'

     

     Sqlserver不支持limit

     

    窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。

    语法结构:排名函数 ( ) OVER ( [ <partition_by子句> ] <order_by子句> ) 

    ROW_NUMBER():为每一组的行记录按顺序生成一个唯一的行号。

    查询各科成绩前2名的记录

    1. 先从科目倒序 再成绩倒序
    2. 子查询查找各科前两名的人

     

    什么是索引?

    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。

    举个例子,索引就像我们查字典时用的按拼音或笔画或偏旁部首

     

    索引的存储是以B+树(注意和二叉树的区别)结构来存储的,又称索引树

    • 索引节点;
    • 叶子节点

      索引节点按照层级关系,有时又可以分为根节点和中间节点,其本质是一样的,都只包含下一层节点的入口值和入口指针;

    叶子节点就不同了,它包含数据,这个数据可能是表中真实的数据行,也有可能是索引列值和行书签,前者对应于聚集索引,后者对应于非聚集索引。

     

     

     

     

    聚集索引

    根节点包含下一层中间节点的节点指针(页号),节点值(主键值)

    中间节点包含下一层页节点的节点指针(页号),节点值(主键值)

    总结:

    聚集索引的根节点和中间节点是索引页,都只包含下一层的入口指针和入口值(位于存储位置的第一个主键值);

    聚集索引的叶节点就是数据页。

     

     

     

    数据页里面的数据按照主键值顺序存放

     

    总结:

    聚集索引就是把数据按主键顺序存储;

    因为一张表中的数据只能有一个物理顺序,所以一张表只能有一个主键/聚集索引。

    非聚集索引

     

    根节点包含下一层中间节点的页号,非聚集索引的键值以及聚集索引的键值

    中间页节点同样包含了下一层(叶节点)的页号以及聚集、非聚集键值

    叶节点包含聚集、非聚集索引键值以及一个KeyHasValue

     

    总结:

    • 非聚集索引的根节点和中间节点是索引页,都只含下一层级的入口指针和入口值(位于存储位置的第一个键值);
    • 非聚集索引的叶节点也是索引页,也存储有聚集索引和非聚集索引的键值;
    • 非聚集索引中的每个索引行(不论是根节点、中间节点还是叶节点)都包含非聚集键值和行定位符(本例为聚集索引键值),此定位符指向聚集索引或堆(没有聚集索引的表)中包含该键值的数据行。

      非聚集索引行中的行定位器可以是指向行的指针,也可以是行的聚集索引键,具体根据如下情况而定:

    • 如果表是堆(意味着该表没有聚集索引),则行定位器是指向行的指针。该指针由文件标识符(ID)、页码和页上的行数生成。整个指针称为行ID (RID)
    • 如果表有聚集索引或索引视图上有聚集索引,则行定位器是行的聚集索引键(本例即为EmployeeId)。如果聚集索引不是唯一的索引,SQL Server将添加在内部生成的值(称为唯一值)以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时,才添加该值。SQL Server通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

     

    非聚集索引作用:

    非聚集索引一般都是为了优化特定的查询效率

    索引覆盖

    • 索引覆盖和非聚集索引的根节点和中间节点一样,都是索引页,都只包含下一层的入口指针和入口值。
    • 索引覆盖的叶节点却稍有不同,多了一列DepartmentCode,此列即为索引覆盖列,而且此列只在叶节点出现,如果查询时,只需返回键值列和索引覆盖列,则只需索引查找,肯本无需访问数据页,不仅提高了性能,而且节省占用空间。

     

     

    如果表有聚集索引(区段结构),那么书签就是从非聚集索引找到聚集索引后,利用聚集索引定位到数据。此处的书签就是聚集索引。如果表没有聚集索引(堆结构)。那么扫描非聚集索引后,通过RID定位到数据,那么此处书签就是RID

      所谓的书签查找,就是通过聚集索引,然后利用聚集索引或RID定位到数据。

    书签查找(RID查找、键查找)

     

    覆盖索引又可以称为索引覆盖。
      解释一: 就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。
      解释二: 索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。
      解释三: 是非聚集组合索引的一种形式,它包括在查询里的SelectJoinWhere子句用到的所有列(即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段,也即,索引包含了查询正在查找的所有数据)

     

     

     

    BEGIN TRAN:设置起始点。

    COMMIT TRAN:使事务成为数据库中永久的、不可逆转的一部分。

    ROLLBACK TRAN:本质上说想要忘记它曾经发生过。

    SAVE TRAN:创建一个特定标记符,只允许部分回滚。

    begin catch

    end catch 异常

     

    B的字段是从表A获取,表A的字段改变了,表B的相应字段也需要改变,这就叫一致性

     

    避免事务中的用户交互:尽量不要在事务中要求用户响应,因为事务持有的任何锁只有在事务提交或回滚后才会释放,等待用户响应的时间,容易导致阻塞或死锁。

    由一个基表定义的视图,只含有基表的主键或候补键,并且视图中没有用表达式或函数定义的属性,才允许更新。

    展开全文
  • 一、数据、数据库数据库管理系统、数据库系统 1、数据:描述事物的符号记录。数据的种类有很多,文本、图形、图像、音频、视频等 2、数据库:长期存储在计算机内、有组织的、可共享的大量数据的集合 3、数据库...

    一、数据、数据库、数据库管理系统、数据库系统

    1、数据:描述事物的符号记录。数据的种类有很多,文本、图形、图像、音频、视频等

    2、数据库:长期存储在计算机内、有组织的、可共享的大量数据的集合

    3、数据库管理系统:位于用户与操作系统之间的一层数据库管理软件,关注的是如何科学地组织和存储数据。包含的功能有:数据定义(DDL),数据组织、存储和管理,数据操纵(DML),数据库的事务管理和运行管理,数据库的简历与维护等

    4、数据库系统:在计算机内引入数据库的系统,一般由数据库、数据库管理系统、应用系统、数据库管理员构成。

    二、数据模型

    1、数据模型是对现实世界数据特征的抽象,用来描述数据、组织数据、和对数据进行操作的。

    2、数据模型分两类:一类是概念模型,一类是逻辑模型和物理模型。

    1)、概念模型:按用户的观点来对数据和信息建模,主要用于数据库设计

    2)、第二类中的逻辑模型:主要包括层次模型、网状模型、关系模型、面向对象模型等。它是按计算机的观点对数据建模,主要用于DBMS的实现

    3)、第二类中的物理模型:描述数据在系统内部的表示方式和存储方式,是面向计算机系统的。物理模型的具体实现是DBMS的任务

    4)、从概念模型到逻辑模型的转换有数据库设计人员完成,从逻辑模型到物理模型的转换由DBMS来实现

    三、数据模型的组成要素

    1、数据结构

    1)、描述数据库的组成对象以及对象之间的联系

    2)、数据结构是所描述的对象类型的集合,是对系统静态特性的描述

    2、数据操作

    1)、指对数据库中各种对象(型)的实例(值)允许执行的操作的集合,包括操作及有关操作规则

    2)、数据操作是对系统动态特性的描述

    3、数据的完整性约束条件

    1)、是一组完整性规则,保证数据的正确、有效、相容

    四、概念模型

    1、基本概念

    1)、实体:客观存在并相互区别的事务,例如一个学生

    2)、属性:实体所具有的某一特性,例如学生的学号

    3)、码:唯一标识实体的属性集

    4)、域:一组具有相同数据类型的值的集合,属性的值来自于某个域

    5)、实体型:学生(学号,姓名,性别,班级)就是一个实体型

    6)、实体集:同一类型实体的集合,全体学生就是一个实体集

    7)、联系:一般指的是不同实体间的联系

    2、两个实体之间的联系:一对一、一对多、多对多

    3、两个以上的实体型之间的联系

    4、单个实体内的联系

    5、概念模型的一种表示方法:实体-联系方法(E-R图)

    五、数据模型之一:关系模型(其他模型略)

    1、关系模型的数据结构

    1)、关系:对应一张表

    2)、元组:表中的一行即为一个元组

    3)、属性:表中的一列即为一个属性

    4)、码:可以唯一标识一个元组,对应主键

    5)、域:属性的取值范围

    6)、分量:元组中的一个属性值

    7)、关系模式:关系名(属性1,属性2,属性3,···)

    2、关系数据模型的操纵:包括曾删改查

    3、完整性约束:实体完整性、参照完整性、用户定义完整性

    六、数据库系统的结构

    1、从数据库管理系统角度看,数据库系统通常采用三级模式结构

    2、模式:、是数据库中全体数据的逻辑结构和特征的描述,他仅仅涉及到型的描述,不涉及到具体的值。模式的具体值成为模式的一个实例,同一个模式可以有很多实例

    3、数据库系统的三级模式结构

    1)、模式:也称逻辑模式,是数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共视图。一个数据库只有一个模式,一个模式对应的就是一个数据库。DBMS提供DDL定义模式

    2)、外模式:也称子模式或用户模式,它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图。外模式通常是模式的子集,一个数据库可以有多个外模式,同一个外模式可以为某一个用户的多个应用程序服务,到那一个应用程序只能使用一个外模式。DBMS提供DDL定义外模式

    3)、内模式:也称存储模式,一个数据库只有一个内模型,它是数据无力结构和存储方式的描述,是数据在数据库内部的表示方式。DBMS提供DDL定义内模式

    4、数据库的二级映像与数据独立性

    1)、外模式/模式影像

    2)、模式/内模式影像

    展开全文
  • Java开发之数据库基本原理

    千次阅读 2013-05-27 20:30:38
    在现代的程序开发中,大量的开发都用到了数据库。使用数据库可以方便地实现数据的存储及查询,以下对Java的数据库操作技术——JDBC进行学习。 一、JDBC概述 1)JDBC(Java Database Connectivity,Java数据库连接...
  • 例题 任务5.16将上一个任务中创建的默认对象绑定在学生表的性别列上 exec sp_bindefault 'def_sex, 'student.sSex' go 默认值的反绑定 使用sp_unbindefault 在当前数据库中为列解除默认值绑定 语法格式为 sp_...
  • [img=https://img-bbs.csdn.net/upload/201406/24/1403570359_519055.jpg][/img] 上面的技术要求中,数据库基本原理原则 是什么?
  • 数据库系统的基本原理数据库访问: 方法1:利用数据库管理系统提供的交互工具访问数据库 方法2:利用开发工具设计界面、处理数据、调用ODBC等接口访问数据库,如:asp,jsp,vc++,php等 数据库(DB): 1.与...
  • 包括数据库原理与技术及数据库习题,非常实用的课件,有利于学习数据库
  • 自考 04735 数据库系统原理 2018版 各章节思维导图 个人整理,供各位考友学习复习使用,其他自考资源会陆续上传,建议去下载那个常考操作,比较全,这些章节没那个细致
  • 数据库作为使用频率最广的中间件,作为一个IT工程师,就算不打算从事数据库开发或者做DBA,也应该掌握其基础的知识、原理基本的使用。为此,本篇开始,尝试对数据库基本知识与原理进行讲解。数据库的类型关系型...
  • 四:SQL与关系数据库基本操作 1.SQL概述 a.什么是SQL b.SQL的特点 c.SQL的组成 1.数据定义语言(Data Definition Language,DDL) 2.数据操纵语言(Data Manipulation Language, DML) 3.数据控制语言(Data ...
  • 数据库_基本原理

    2016-07-06 12:56:06
    数据库在日常的Web开发中基本是必需品,我们开发人员除了会基本的SQL操作语言外,还是需要对数据库基本原理有所了解的,我想结合这篇文章做个自己的总结: “如果有人问你数据库的原理,叫他看这篇文章” 主要记录...
  • 数据库原理

    2012-12-19 15:10:38
    数据库基本原理的课件,很实用。ppt课件。被多名老师作为讲课必须参照的课件
  • 2017年10月自学考试数据库系统原理04735真题,主要考核数据库基本原理和应用
  • 自编自考课件04735数据库
  • 数据库基本原理

    2015-11-04 23:16:00
    锁的基本原理如下: 1、当一个事务访问某种数据库资源时,如果执行select语句必须先获得共享锁,如果执行insert、update、或delete语句,必须先获得独占锁,这些锁用于锁定被操作的资源。 2、当第二个...
  • 数据库 基础原理 简单分析 有兴趣的同学可以看一下 感谢

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,233
精华内容 4,093
关键字:

数据库基本原理