数据库索引

索引原理：

当在一个没有索引的表中查询符合某个条件的记录时。DBMS会顺序地逐条读取每个记录与查询条件进行匹配，这种方式成为全表扫描。全表扫描需要遍历全表效率很低。

索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。

对比MySAM和InnoDB：

MySAM：无论是主键索引还是普通索引，查找数据的流程都是一样的，都会先去B+tree找到对应叶子节点，再去物理地址取数据。（先找地址，再通过地址找数据）

InnoDB：查找数据的流程分为两种类型

聚簇索引：通过主键id能够直接取出全部数据（叶子节点中存放真实数据）

辅助索引：通过索引找到对应id，再通过聚簇索引的B+tree找到真实数据，要找两次。

InnoDB再聚簇索引的查询过程中比MyISAM快一些；InnoDB辅助索引的查询过程在一定程度上比MyISAM慢一些

使用索引遵循的的规则：

1.小表不需要建立索引。

2.对于大表，如果经常查询的数据可以建立索引。

3.对于大部分不重复列值的可以建立索引

4.基数大的列列，适合B树索引，基数小的，适合建立位图索引。

5.对于控制多，但经常查询所有非空值的列，应该建立索引。

6.LONG和LONG RAW列不能创建索引。

7.经常连接查询的列可以创建索引。

8.在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面。

9.经常更改的表索引应该少一点，主要用于读的表索引可以多点。

10.在查询数据后创建索引。如果在转载数据之前创建了索引，那么当拆入每行时，Oracle都必须更改每个索引。

创建索引

CREATE [UNIQUE]|[BITMAP] INDEX index_name on table_name([columm1[ASC|DESC],column2[ASC|DESC],...]|[express])
[TABLESPACE tablespace_name] 
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING][NOLINE][NOSORT];


UNIQE:表示唯一索引，默认情况，不使用
BITMAP：表示位图索引  默认情况，不使用
PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，索引指定空间应该较大一点
NOLOGGING：表示在创建索引过程中不产生任何重做日志信息。默认，不适用
ONLINE：表示在创建或重建索引使，允许进行DML操作
NOSORT：默认情况下，不使用。Oracle在创建索引时对表进行排序。如果表中数据已经是按该索引顺序排列时则使用

 索引顺序不同也是合法的。

create index idx1 on sales(id,name);
create index idx2 on sales(name,id);

创建B树索引

create index idx_name on sales(name); 单列索引
CREATE INDEX idx_name ON sales(job,sal); 复合索引

创建位图索引

create bitmap index idx_name on sales(name);

创建函数索引

CREATE INDEX idx_name ON sales(UPPER(name));

索引重命名

alter index idx_name1 rename to idx_name2;

 删除索引

drop index idx_name;

重建索引

drop index idx_name;

合并索引

alter index idx_name coalesce;

 使用索引查找时，最左前缀原则，根据索引最左边的行才能实现索引查找。

InnoDB 采用B树    其中索引储存值的都是排好序的，例入索引值：111，但是你查询时最左边的值你不知道，就相当于*11的查询，你就没有办法利用索引。

通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。