海量数据处理之数据库索引

最新推荐文章于 2021-01-27 04:48:15 发布

weixin_30685029

最新推荐文章于 2021-01-27 04:48:15 发布

阅读量132

点赞数 1

文章标签：数据库数据结构与算法

原文链接：http://www.cnblogs.com/javaspring/archive/2012/08/13/2656228.html

版权

前言：本文第一部分讨论数据库的索引及其优化，主要以sql server为例，第二部分我们从Mysql讨论它背后的数据结构和算法原理。

第一部分，数据库索引及其优化

一，什么是索引

　　数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
　　例如这样一个查询：select * from table1 where id=44。如果没有索引，必须遍历整个表，直到ID等于44的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，直接在索引里面找44（也就是在ID这一列找），就可以得知这一行的位置，也就是找到了这一行。可见，索引是用来定位的。
　　索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；显然在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新该索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新得列不宜建立聚簇索引，聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。建立一个聚簇索引如：

create cluster index id on Student(id);

二，概述

　　建立索引的目的是加快对表中记录的查找或排序。
　　为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

精简来说，索引是一种结构.在SQL Server中，索引和表（这里指的是加了聚集索引的表）的存储结构是一样的,都是B树，B树是一种用于查找的平衡多叉树.理解B树的概念如下图:

理解为什么使用B树作为索引和表（有聚集索引）的结构，首先需要理解SQL Server存储数据的原理.

在SQL SERVER中，存储的单位最小是页(PAGE),页是不可再分的。就像细胞是生物学中不可再分的，或是原子是化学中不可再分的最小单位一样.这意味着,SQL SERVER对于页的读取，要么整个读取，要么完全不读取，没有折中.

在数据库检索来说，对于磁盘IO扫描是最消耗时间的.因为磁盘扫描涉及很多物理特性，这些是相当消耗时间的。所以B树设计的初衷是为了减少对于磁盘的扫描次数。如果一个表或索引没有使用B树（对于没有聚集索引的表是使用堆heap存储),那么查找一个数据，需要在整个表包含的数据库页中全盘扫描。这无疑会大大加重IO负担.而在SQL SERVER中使用B树进行存储，则仅仅需要将B树的根节点存入内存，经过几次查找后就可以找到存放所需数据的被叶子节点包含的页！进而避免的全盘扫描从而提高了性能.

下面，通过一个例子来证明：

在SQL SERVER中，表上如果没有建立聚集索引，则是按照堆（HEAP）存放的，假设我有这样一张表: