数据库索引技术

1、毛军军 2017.9数据库索引技术CONTENT 目录01B-Tree的介绍02索引的介绍03索引的分类04索引的优点05索引的缺点06索引的使用B-Tree的介绍Part1Part One B-Tree的介绍 B-Tree 我们常见的数据库系统，其索引使用的数据结构多是B-Tree或者B+Tree。例如：MySql、DB2使用的是B+Tree，Oracle、SQLServer及Sysbase使用的是B-Tree。所以在最开始，简单地介绍一下B-Tree。B-Tree不同于Binary Tree（二叉树，最多有两个子树），一棵M阶的B-Tree满足以下条件：1）每个结点至多有M个孩子；2）除根

2、结点和叶结点外，其它每个结点至少有M/2个孩子；3）根结点至少有两个孩子（除非该树仅包含一个结点）；4）所有叶结点在同一层，叶结点不包含任何关键字信息；5）有K个关键字的非叶结点恰好包含K+1个孩子；6）对于一个结点，其内部的关键字是从小到大排序的Part One B-Tree的介绍一个简单的B-Tree（M=4）的样例41016679123111215172236对于每个结点，主要包含一个关键字数组Key，一个指针数组（指向儿子）Son。在B-Tree内，查找的流程是：使用顺序查找（数组长度较短时）或折半查找方法查找Key数组，若找到关键字K，则返回该结点的地址及K在Key中的位置；否则，可

3、确定K在某个Keyi和Keyi+1之间，则从Soni所指的子结点继续查找，直到在某结点中查找成功；或直至找到叶结点且叶结点中的查找仍不成功时，查找过程失败。Part One B-Tree的介绍上图演示如何生成B-Tree（M=4，依次插入16）：从图可见，当我们插入关键字4时，由于原结点已经满了，故进行分裂，基本按一半的原则进行分裂，然后取出中间的关键字2，升级（这里是成为根结点）。其它的依类推，就是这样一个大概的过程。索引的介绍Part2Part Two 索引的介绍 索引的概念 索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到

4、所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。数据库索引区别于驻留内存的数据结构的地方在于，数据库索引包含的数据量比一次能调入内存的数据量大。因此，数据库索引的数据是存放在磁盘上的，只有被访问的时候才会被部分地调入到内存中。Part Two 索引的介绍 索引的存储一条索引记录中包含的基本信息包括：键值（即你定义索引时指定的所有字段的值）+逻辑指针（指向数据页或者另一索引页）。索引的分类Part3Part Three 索引的分类 1.直接创建索引和间接创建索引 直接创建索引：CREATE INDEX mycolum_index ON table ( m

5、ycolum) 间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引 2.普通索引和唯一性索引 普通索引： CREATE INDEX mycolum_index ON table ( mycolum) 唯一索引：保证在索引列中的全部数据是唯一的CREATE UNIQUE INDEX mycolum_index ON table ( mycolum) 3.单个索引和复合索引CREATE INDEX mycolum_index ON table ( col1，col2) 4.聚集索引和非聚集索引聚集索引，表数据按照索引的顺序来存储的。非聚集索引，表数据存储顺序与索引顺序无关。Part Th

6、ree 索引的分类聚集索引的结构Part Three 索引的分类非聚集索引的结构索引的优点Part4Part Four 索引的优点 创建唯一索引，保证数据库表中每一行数据的唯一性 大大加快数据的检索速度，这也是创建索引的最主要的原因 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义索引的缺点Part5Part Five 索引的缺点 索引需要占用数据表以外的物理存储空间。 创建索引和维护索引要花费一定的时间。 当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。索引的使用Part6Part Six 索引的使用1.负向条件查询不能使用索引select * from or

7、der where status!=0 and stauts!=1 not in/not exists都不是好习惯2.前导模糊查询不能使用索引select * from order where desc like %XX而非前导模糊查询则可以：select * from order where desc like XX%3.在属性上进行计算不能命中索引select * from order where YEAR(date) = 2017即使date上建立了索引，也会全表扫描，可优化为值计算：select * from order where date = CURDATE()或者：select

8、* from order where date = 2017-01-01Part Six 索引的使用4.允许为null的列，查询有潜在大坑单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集select * from user where name != shenjian如果name允许为null，索引不存储null值，结果集中不会包含这些记录。所以，请使用not null约束以及默认值。5.复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致用户中心建立了(login_name, passwd)的复合索引select * from user where login_name=? and passwd=?select * from user where passwd=? and login_name=?都能够命中索引select * from user where login_name=