数据库索引（Oracle和Mysql）学习总结

旭日Follow_24 的CSDN 博客 ，全文地址请点击：

https://mp.csdn.net/postedit/80910082

索引概念：

    索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。

一、Oracle索引

1、索引类型

列出常见常用的几种索引类型，如下：

  非唯一索引（最常用） NonUnique
　　唯一索引  Unique
　　位图索引  Bitmap
　　分区索引  Partitioned
　　非分区索引  NonPartitioned  
　　正常型B树 Normal
　　基于函数的索引  Function-based

2、索引结构

B-tree：
适合与大量的增、删、改（OLTP）； 
不能用包含OR操作符的查询； 
适合高基数的列（唯一值多） 
典型的树状结构； 
每个结点都是数据块； 
大多都是物理上一层、两层或三层不定，逻辑上三层； 
叶子块数据是排序的，从左向右递增； 
在分支块和根块中放的是索引的范围；

Bitmap:
适合与决策支持系统； 
做UPDATE代价非常高； 
非常适合OR操作符的查询； 

基数比较少的时候才能建位图索引；

树型结构：
索引头 
开始ROWID，结束ROWID（先列出索引的最大范围）

BITMAP
每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值

1. b-tree索引
Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE INDEX语句时，默认就是在创建b-tree索引。没有特别规定可用于任何情况。

2. 位图索引(bitmap index)
位图索引特定于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。

3. 基于函数的索引
比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。

4. 分区索引和全局索引
这2个是用于分区表的时候。前者是分区内索引，后者是全表索引

5. 反向索引（REVERSE）
这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值（10001,10002,10033,10005,10016..） 
这种情况默认索引分布过于密集，不能利用好服务器的并行 
但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布，能高效的利用好并行运算。

6.HASH索引
HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。使用HASH索引必须要使用HASH集群。

3、索引原理

若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍
若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

4、建立索引的原则

1.如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
　　2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
　　3. 小表不要简历索引
　　4. 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引
　　5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引
　　6. 经常进行连接查询的列应该创建索引
　　7. 使用create index时要将最常查询的列放在最前面
　　8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
　　9.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

5、索引使用原则

经常检索排序大表中40%或非排序表7%的行，建议建索引；
为了改善多表关联，索引列用于联结；
列中的值相对比较唯一；
取值范围（大：B*树索引，小：位图索引）；
Date型列一般适合基于函数的索引；
列中有许多空值，不适合建立索引

二、MySQL索引

1、索引类型

单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引：索引列中的值必须是唯一的，但是允许为空值。

　  主键索引：是一种特殊的唯一索引，不允许有空值。

　　组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说　

　　全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思，如果感兴趣进一步深入使用它，那么看下面测试该索引时，会给出一个博文，供大家参考。

　　空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

2、索引原理

在MySQL中，存储引擎用类似的方法使用索引，其先在索引中查找对应的值，然后根据匹配的索引记录找到对应的数据行，最后将数据结果集返回给客户端。

3、索引使用原则

最左前缀匹配原则
mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a = 1 and b = 2 and c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序
比如a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

尽量选择区分度高的列作为索引
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

索引列不能参与计算，保持列“干净”
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

尽量的扩展索引，不要新建索引
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

4、高性能索引列

独立的列

前缀索引和索引的选择性

多列索引

选择合适的索引列顺序

聚簇索引

覆盖索引

使用索引扫描来做排序

冗余和重复索引

未使用的索引