MySQL，Oracle索引介绍和优化

Oracle索引分类

B树(b-tree)索引：最常用的索引，其树结构与二叉树比较类似，根据ROWID快速定位所访问的行。一般创建索引，默认就是在创建b-tree索引。

位图(bitmap)索引：使用位图来管理与数据行的对应关系，适用于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。

函数索引：针对频繁的对列使用函数的索引，只有当查询语句包含该函数或者表达式时，基于函数的索引才会被调用。

反转索引：反转了b-tree索引码中的字节，使索引条目分配更均匀，多用于并行服务器环境下，用于减少索引的竞争。

分区索引和全局索引：分区表的索引，包括本地分区索引（本地前缀分区索引和本地非前缀分区索引）和全局索引，一般建议使用本地分区索引，因其与基表具有良好的数据均衡性和可维护性。

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

Oracle数据扫描

1.全表扫描：全表扫描(FULL TABLE SCANS) 时所有行、所有数据快均会被读到，是效率最低的一种，一般会在表缺少索引、读取大量数据、访问小表或高并发时发生.
2. ROWID扫描:ROWID扫描是通过rowid中数据文件和块位置访问数据行。一般作为访问索引后的第二步，如果访问的列全部包含在索引中，则不会执行rowid扫描
3. 索引扫描包括全索引扫描(full index scan FIS)、快速全索引扫描(fast full index scan FFIS)、索引范围扫描(index range scan)、索引唯一扫描(index unique scan)、索引跳跃式扫描(index skip scan)、位图索引扫描(bitmap index scan)

如何创建索引

创建表:

create table person (id int, sex char(1), name char(10));

创建索引:

create index person_index on person (id);

查看创建的表与索引:

select object_name, object_type from user_objects;

除了可以根据单个字段创建索引，也可以根据多列创建索引:

create index person_index2 on person (sex, name);

删除索引：

drop index person_index;

创建索引的注意事项

1、建立索引的目的是为了提高查询效率，但建立的索引过多会影响插入和删除数据的速度，这是因为我们修改表数据时，索引也要跟着修改，所以创建索引的时候我们需要权衡是查询多还是修改多。

2、应该创建索引的字段：

① 经常作为查询条件的字段

② 经常用在多表连接的列，例如外键

③ 经常需要排序的字段

3、 应该少建或者不建索引的情况：

① 表中数据太少，增加索引基本不会带来查询速度的提升，反而浪费了存储空间。

② 经常需要插入、修改、删除操作的表

③ 表中数据重复且分布平均的字段(如“性别”)

④ 查询中很少用到不应该创建索引

⑤ 定义为text、image、bit数据类型的列不应该加索引

4、 一些sql的写法会限制索引的使用：

where子句中如果使用in、or、like、!= 、<>均会导致索引不能正常使用，将<>换成>and <,将is not null换成>=chr(0)

where语句中索引的使用

1. 索引列上不要使用is null 或 is not null

错误：select column from table where column is not null

正确：select column from table where column >= chr(0)

1. 索引列上不要使用函数：

错误：select column from table where substr(name, 1, 3) = ‘ABC’

正确：select column from table where name like ‘ABC%’ // 避免通配符在词首出现

1. 索引列上不要使用NOT(!=、<>)

错误：select column from table where column != 10

正确：select column from table where column > 10 or column < 10

1. 索引列上不要进行计算

错误：select column from table where column / 10 > 10

正确：select column from table where column > 10 * 10

---

MySQL索引分类：

主键索引（primary key）
唯一索引（unique）
常规索引（index/key）
全文索引（fulltext 只有MyISAM类型表可以用）

MySQL的索引类型有普通索引(normal)，唯一索引(unique)和全文索引(full text)，合理使用索引可大大提升数据库的查询效率，那么这三种类型的索引都有哪些区别呢？

normal：这是最基本的索引，它没有任何限制，MyIASM中默认的BTREE类型的索引，是我们大多数情况下用到的索引。

unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复。例如身份证号用作索引时，可设置为unique。

full text : 表示全文搜索的索引，仅可用于 MyISAM 表。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

总结，索引的类别由建立索引的字段内容特性来决定，通常normal最常见。

在实际操作过程中，应该选取表中哪些字段作为索引？

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

创建索引7大原则

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

6．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

举个例子来说，比如你在为某商场做一个会员卡的系统。这个系统有一个会员表（大致字段如下）：

会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个会员编号，作为主键，使用 PRIMARY

会员姓名如果要建索引的话，那么就是普通的 INDEX

会员身份证号码如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

会员备注信息如果需要建索引的话，可以选择 FULLTEXT，全文索引。