数据库基本原理-索引

2020/4/201数据库基本原理•在物理层面上，数据库有数据文件组成，而这些数据文件可以组成文件组，然后存储在磁盘上。每个文件包含许多区，每个区的大小为64K由八个物理上连续的页组成（一个页8K），我们知道页是SQLServer数据库中的数据存储的基本单位。2020/4/202•一个页可以存放N行数据。我们表里的数据都是存放在页上的，这种叫数据页。还有一种页存放索引数据的，叫索引页。2020/4/203•为数据库中的数据文件（.mdf或.ndf）分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。•页中存储的类型有：数据，索引和溢出2020/4/204文件和文件组•在SQLServer中，通过文件组这个逻辑对象对存放数据的文件进行管理2020/4/205•在顶层是我们的数据库，由于数据库是由一个或多个文件组组成，而文件组是由一个或多个文件组成的​​逻辑组，所以我们可以把文件组分散到不同的磁盘中，使用户数据尽可能跨越多个设备，多个I/O运转，避免I/O竞争，从而均衡I/O负载，克服访问瓶颈。2020/4/206区和页•如图2所示，文件是由区组成的，而区由八个物理上连续的页组成，由于区的大小为64K，所以每当增加一个区文件就增加64K.2020/4/207•页中保存的数据类型有：表数据、索引数据、溢出数据、分配映射、页空闲空间、索引分配等，具体如下图所示：2020/4/2082020/4/2092020/4/2010基本存储单元—页•数据库文件存储是以页为存储单元的，一个页是8K（8192Byte），一个页就可以存放N行数据。表里的数据都是存放在页上的，这种叫数据页。还有一种页存放索引数据的，叫索引页。2020/4/2011•页也是IO读取的最小单元（物理IO上不是按行读取），也是所有权的最小单位。如果一页中包含了表A的一行数据，这页就只能存储表A的行数据了。或是一页中包含了索引B的条目，那这页也仅仅只能存储索引B的条目了。每页中除去存储数据之外，还存储一些页头信息以及行偏移以便SQLServer知道具体每一行在页中的存储位置。2020/4/2012•在数据页上，数据行紧接着页头（标头）按顺序放置；页头包含标识值，如页码或对象数据的对象ID；数据行持有实际的数据；最后，页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目，每个条目记录对应行的第一个字节与页头的距离，行偏移表中的条目的顺序与页中行的顺序相反。2020/4/20132020/4/2014索引•数据库的基本物理存储单元是页，一个表由很多个页组成，那这些页又是如何组织的呢？我们一般都会对表创建索引，这些索引又是如何存储的呢？•索引（Index）提供查询的速度”这是对索引的最基本的解释，接下来我们将通过介绍索引的组成，让大家对索引有更深入的理解。2020/4/2015•索引是数据库中的一个独特的结构，由于它保存数据库信息，那么我们就需要给它分配磁盘空间和维护索引表。创建索引并不会改变表中的数据，它只是创建了一个新的数据结构指向数据表；打个比方，平时我们使用字典查字时，首先我们要知道查询单词起始字母，然后翻到目录页，接着查找单词具体在哪一页，这时我们目录就是索引表，而目录项就是索引了。2020/4/2016•当然，索引比字典目录更为复杂，因为数据库必须处理插入，删除和更新等操作，这些操作将导致索引发生变化。2020/4/20172020/4/2018表/索引的存储结构2020/4/2019•是一个B树（二叉搜索树）的示例，都是小的元素放左边，大的元素放右边，依次构造的，比如要查找元素9，从根节点开始，只要比较三次就找到，查询效率是非常高的。•B+树和B-树是数据库中广泛应用的索引存储结构，它可以极大的提高数据查找的效率。数据库存储的基本单元是页，因此，索引树上的节点就是页了2020/4/2020•索引的主要优点和目的就是为了提高查询效率.•为了保证数据的查询效率，当新增、修改、删除数据的时候，都需要维护这颗索引树，就可能会出现分裂、合并节点（页）的情况2020/4/2021索引的缺点•当新增、修改、删除数据的时候，需要维护索引树，有一定的性能影响；•同上面，在频繁的树维护过程中，B树的页拆分、合并会造成大量的索引碎片，又会极大的印象查询效率，因此索引还需要维护；•非聚集索引需要额外的存储空间，不过这个一般问题都不是很大，但是需要注意的一个问题；页节点•假设我们磁盘上的数据是物理有序的，那么数据库在进行插入，删除和更新操作时，必然会导致数据发生变化，如果我们要保存数据的连续和有序，那么我们就需要移动数据的物理位置，这将增大磁盘的I/O，使得整个数据库运行非常缓慢；使用索引的主要目的是使数据逻辑有序，使数据独立于物理有序存储。2020/4/2022•为了实现数据逻辑有序，索引使用双向链表的数据结构来保持数据逻辑顺序，如果要在两个节点中插入一个新的节点只需修改节点的前驱和后继，而且无需修改新节点的物理位置。2020/4/2023•双向链表（Doublylinkedlist）也叫双链表，是链表的一种，它的每个数据结点中都有两个指针，分别指向直接后继和直接前驱。所以，从双向链表中的任意一个结点开始，都可以很方便地访问它的前驱结点和后继结点2020/4/20242020/4/2025聚集索引•聚集索引决定了表数据的物理存储顺序，也就是说表的物理存储是根据聚集索引结构进行顺序存储的，因此一个表只能有一个聚集索引。如下图，就是一个聚集索引的树结构2020/4/20262020/4/2027•所有数据都在叶子节点的页上，在叶子节点（数据页）之间有一个链指针，这是B+树的特点；•非叶子节点都是索引页，存储的就是聚集索引字段的值；•表的物理存储就是依据聚集索引的结构的，一个表只能有一个聚集索引；2020/4/2028•聚集索引的所有的数据都存储在叶子节点上，数据查询的复杂度都是一样的（树的深度），按照聚集索引列查找数据效率是非常高的。2020/4/2029•聚集索引决定了表的物理存储结构，那如果没有创建聚集索引，会如何呢？2020/4/2030•表内的所有页都无序存放，是一个无序的堆结构。堆数据的查询就会造成表扫描，性能是非常低的。2020/4/2031•因此聚集索引的的重要性不言而喻，一般来说，大多会对主键建立聚集索引，大多数普通情况这么做也可以。但实际应用应该遵从一个原则就是“频繁使用的、排序的字段上创建聚集索引”2020/4/2032非聚集索引•除了聚集索引以外的其他索引，都称之为非聚集索引，非聚集索引一般都是为了优化特定的查询效率而创建的。非聚集索引也是B树（B+树和B-树）的结构，与非聚集索引的存储结构唯一不一样的，就是非聚集索引中不存储真正的数据行，因为在聚集索引中已经存放了所有数据，非聚集索引只包含一个指向数据行的指针即可。2020/4/20332020/4/2034•非聚集索引的创建会单独创建索引文件来存储索引结构，会占用一定存储空间，就是用空间换时间；•非聚集索引的目的很单纯：提高特定条件的查询效率，一个表有可能根据多种查询需求创建多个非聚集索引；2020/4/20352020/4/2036•数据查询SQL简单来看，分为两个部分：SELECT****和Where****，因此索引的创建也是根据这两部分来决定的。根据这两点，有两种主要的索引形式：复合索引和覆盖索引，在实际使用中，根据具体情况可能都会用到，只要能提高查询效率就是好索引。2020/4/2037•覆盖索引：就是在索引中包含的数据列（非索引列，SELECT需要的列），这样在使用该索引查询数据时就不会再进行键查找（也叫书签查找）了。•复合索引：主要针对Where中有多个条件的情况，索引包含多个数据列。在使用复合索引时，应注意多个索引键的顺序问题，这个是会影响查询效率的，一般的原则是唯一性高的放前面，还有就是SQl语句中Where条件的顺序应该和索引顺序一致。2020/4/20382020/4/2039索引碎片•索引在使用一段时间后（主要是新增、修改、删除数据，如果该页已经存储满了，就要进行页的拆分，频繁的拆分，会产生较多的索引碎片）会产生索引碎片，这就造成了索引页在磁盘上存储的不连续。会造成磁盘的访问使用的是随机的I/O，而不是顺序的I/O读取，这样访问索引页会变得更慢。如果碎片过多，数据库是可会能不使用该索引的（太慢了，数据库会选择一个更优的执行计划）。2020/4/2040预防•设置页的填充因子：•在页上设置一段空白区域，在新增数据的时候，可以使用这段空白区域，可以一定的避免页的拆分，从而减少索引碎片的产生2020/4/2041•填充因子就是用来描述这种页中填充数据的一个比例，一般默认是100%填充的。如果我们修改填充因子为80%，那么页在存储数据时，就会剩余20%的剩余空间，这样在下次插入的时候就不会拆分页了。那么是不是我们可以把填充因子设置低一点，留更多的剩余空间，不是很好嘛？当然也不好，填充因子设置的低，会需要分配更多的存储空间，叶子节点的深度会增加，这样是会影响查询效率的，因此，这是要根据实际情况而定的。•那么一般我们是怎么设置填充因子的呢，主要根据表的读写比例而定的。如果读的多，填充因子可以设置高一点，如100%，读写各一半，可以80~90%；修改多可以设置50~70%。2020/4/2042索引修复•定期对索引进行检查、维护，写一段SQL检查索引的碎片比例，如果碎片过多，进行碎片修复或重建，定期执行即可2020/4/2043索引使用总结•创建索引的的字段尽量小，最好是数值，比如整形int等；•对于频繁修改的字段，尽量不要创建索引，维护索引的成本很高，而且更容易产生索引碎片；•定期的索引维护，如索引碎片的修复等；•不要建立或维护不必要的重复索引，会增加修改数据（新增、修改、删除数据）的成本；2020/4/2044•使用唯一性高的字段创建索引，切不可在性别这样的低唯一性的字段上创建索引；•在SQL语句中，尽量不要在Where条件中使用函数、运算符或表达式计算，会造成索引无法正常使用；•应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；•应尽量避免在where子句中使用!=或操作符，否则将导致引擎放弃使用索引而进行全表扫描；2020/4/2045•索引的作用？和它的优点缺点是什么？•索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。索引很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。事务与锁•事务就是作为一个逻辑工作单元的SQL语句，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上个节点。为了确保要么执行，要么不执行，就可以使用事务。而锁是实现事务的关键，锁可以保证事务的完整性和并发性。2020/4/20462020/4/2047•事务：保持逻辑数据一致性与可恢复性，必不可少的利器。•锁：多用户访问同一数据库资源时，对访问的先后次序权限管理的一种机制，没有他事务或许将会一塌糊涂，不能保证数据的安全正确读写。•死锁：是数据库性能的重量级杀手之一，而死锁却是不同事务之间抢占数据资源造成的。2020/4/2048•去不了终点，回到原点。---无间道2020/4/2049•在一个事务中，你写了2条sql语句，一条是修改订单表状态,一条是修改库存表库存-1。如果在修改订单表状态的时候出错，事务能够回滚，数据将恢复到没修改之前的数据状态，下面的修改库存也就不执行，这样确保你关系逻辑的一致，安全。。•事务就是这样，要么全部执行，要么全部不执行，回到原数据状态。2020/4/2050事务具有原子性，一致性，隔离性，持久性。•原子性：事务必须是一个自动工作的单元，要么全部执行，要么全部不执行。•一致性：事务结束的时候，所有的内部数据都是正确的。•隔离性：并发多个事务时，各个事务不干涉内部数据，处理的都是另外一个事务