数据库索引及索引方法

 

 

 

 

 

数据库索引及索引方法

 

 

 

 

 

 

信息管理部

 

 

目录

 

一、 索引定义 3

二、 索引作用 3

三、 索引种类 3

四、 索引方法 7

五、 索引的使用 8

六、 索引优点&缺点 8

七、 名词解释 9

八、 执行工具 9

 

一、 
索引定义

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

二、 索引作用

快速获取数据、保证数据唯一性、实现表与表之间的参照完整性、在使用分组排序中使用索引可以减少分组排序的时间。

三、 索引种类

普通索引（非唯一索引）：

最基本的的索引；

直接创建索引：create INDEX index_name on Tablename(列的列表)；

修改表结构添加索引：alter table TableName add INDEX index_name ON (列的列表)；

创建表时同步创建索引：create table TableName([...], PRIMARY KEY (‘列名’),index [IndexName] (列的列表)；

删除索引：DROP INDEX index_name ON table；

唯一索引：

索引列的值必须唯一，但允许有空值，如果是组合索引，则列值组合必须唯一

直接创建索引：CREATE UNIQUE INDEX indexName ON table(column(length))；

修改表结构添加索引：ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

创建表时同步创建索引：create table tableName([...], PRIMARY KEY (‘列名’), UNIQUE [IndexName] (列的列表)；

主键索引（MySQL）：

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：MySQL 、主键、自动增长；

组合索引（多行索引）：

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

创建索引：create index indexName on tableName(列1,列2);

添加索引：ALTER TABLE tableName ADD INDEX indexName (列1,列2);

Eg:

create index  A_index on A(id,custName);// 创建索引

正确：select * from A where id > 1 and custName = 'tom';  -- 这种情况会走索引

正确：select * from A where id > 1;  --  这种情况也会走索引，尽管只是使用了组合索引中一个字段

错误：select * from A where custName = 'tom';  --因为custName位于组合索引的第二个字段

全文索引（MySql）：

全文索引主要用来查找文本中的关键字，而非直接与值相比较。全文索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

创建表添加索引：CREATE TABLE tableName ([列字段]，PRIMARY KEY (主键), FULLTEXT (列名))；

修改表结构添加索引：ALTER TABLE tableName ADD FULLTEXT indenxName(列名)；

创建索引：CREATE FULLTEXT INDEX indexName ON tableName(列名)

删除索引：DROP INDEX indexName ON tableName ;

ALTER TABLE tableName DROP INDEX indexName;

使用全文索引：使用格式MATCH (columnName) AGAINST ('string')

Eg:

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')

全文索引相关变量：SHOW VARIABLES LIKE 'ft%';

ft_boolean_syntax 改变IN BOOLEAN MODE的查询字符

ft_min_word_len 最短的索引字符串

ft_max_word_len 最长的索引字符串

ft_query_expansion_limit 查询括展时取最相关的几个值用作二次查询

ft_stopword_file 全文索引的过滤词文件

重新建立索引命令：repair table tablename quick

注：

① 50%的门坎限制

可使用IN BOOLEAN MODE 避开50%的限制

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪' IN BOOLEAN MODE)

② ft_boolean_syntax 详解

+ : 用在词的前面，表示一定要包含该词，并且必须在开始位置

- : 不包含该词，所以不能只用「-yoursql」这样是查不到任何row的，必须搭配其他语法使用

eg: MATCH (列名) AGAINST ('-AAA +BBB')-- 匹配到： 所有不包含AAA，但包含BBB的记录

空(也就是默认情况)，表示可选的，包含该词的顺序较高

> :提高该字的相关性，查询的结果会排在比较靠前的位置

< :降低相关性，查询的结果会排在比较靠后的位置

1. 只要使用 ><的总比没用的 靠前；

                  2. 使用  >的一定比 <的排的靠前 (这就符合相关性提高和降低)；

                  3. 使用同一类的，使用的越早，排的越前。

( )：可以通过括号来使用字条件

eg: +aaa +(>bbb <ccc) // 找到有aaa和bbb和ccc，aaa和bbb，或者aaa和ccc(因为bbb，ccc前面没有+，所以表示可有可无)，然后 aaa&bbb > aaa&bbb&ccc > aaa&ccc

~ :将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面

eg:   +apple ~macintosh   先匹配apple，但如果同时包含macintosh，就排名会靠后

* :通配符，这个只能接在字符串后面

" " :整体匹配，用双引号将一段句子包起来表示要完全相符，不可拆字

函数索引（Oracle）：

为了避免由于在条件匹配时引入函数，导致执行计划不再使用索引，oracle提供了基于函数的索引，进而解决上述问题，提高访问效率。

创建索引：create INDEX index_name on tablename((函数索引所使用的函数))；

使用：SELECT * FROM tableName WHERE (函数索引所使用的函数) > 0

注：函数索引的综合消耗大于普通的B树索引（数索引要保证创造的函数列数据一致性和多次进行函数计算），函数索引适应范围小不灵活。综上所述不推荐使用函数索引。

域索引（全文索引|Oracle）：

域索引实际为用户自定义索引，域索引主要对存储在数据库中的媒体，图像数据进行索引，这些数据在oracle中基本上以BLOB类型存储，不同的应用存储格式也不同，oracle不可能提供某一种现成的算法对这些数据进行索引，为了能够对这些类型数据快速访问，oracle提供了现成的接口函数，用户可以针对自己的数据格式实现这些接口函数，以达到对这些数据的快速访问。

三种分析器：

basic_lexer（默认的分析器）:主要针对英语。有较高的处理效率，因为它只认空格和标点，所以对于汉语没有空格的情况不会分词

chinese_vgram_lexer: 专用汉语分析器，支持所有汉字字符集（ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8）因为采用了分词的方法，可以查到所有的分词，效率差

chinese_lexer: 新汉语分析器，只支持utf8, 支持识别大部分的分词，但是会过滤掉很多无法识别的分词，因为这个原因也导致效率很高，如果数据库是zhs16gbk字符集，则只能使用Chinese vgram lexer.

建立索引：

① 检查数据库中是否存在CTXSYS用户和CTXAPP角色

select * from dba_users WHERE username='CTXSYS'

select * from dba_roles WHERE ROLE = 'CTXAPP'

② 权限赋予：在ctxsys用户下把ctx_ddl的执行权限赋于要使用全文索引的用户

ALTER USER CTXSYS ACCOUNT UNLOCK; // 解锁

GRANT CTXAPP TO 用户 // 角色授予

③ 根据需求选择lexer制定文本解析器

创建解析器

BEGIN

ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');

END;

删除解析器

BEGIN

   ctx_ddl.drop_preference ('my_lexer');

END;

④ 创建全文索引

CREATE INDEX ind_lot ON lot(lot_id) indextype is ctxsys.context parameters('lexer my_lexer');

⑤ 删除全文索引

Drop index index_name force

⑥索引同步维护

BEGIN

ctx_ddl.sync_index('ind_lot');

ctx_ddl.optimize_index('ind_lot','full');

END;

索引使用：

select * from lot where contains(lot_id,'北京')>0

分区索引：

分区索引分为两类：Locally partitioned index（局部分区索引）、Globally partitioned index（全局分区索引）

Locally partitioned index（局部分区索引）

局部分区索引随表对索引完成相应的分区（即索引会使用与底层表相同的机制分区），每个表分区都有一个索引分区，并且只索引该表分区

局部分区索引又分为两类：

    Local prefixed index（局部前缀索引）

    Local nonprefixed index（局部非前缀索引）

Ⅰ：局部前缀索引：以分区键作为索引定义的第一列

Ⅱ：局部非前缀索引：分区键没有作为索引定义的第一列

Eg：

create table local_index_example(

 id number(2),

 name varchar2(50),

 sex varchar2(10) )

partition by range (id)(-- 分区 id 分区键

  partition part_1 values less than (5),

  partition part_2 values less than (10) )

--创建局部前缀索引；分区键（id）作为索引定义的第一列

create index local_prefixed_index on local_index_example (id, name) local;

--创建局部非前缀索引；分区键未作为索引定义的第一列

create index local_nonprefixed_index on local_index_example (name, id) local;

索引类型选择：

①： select … from local_index_example where id = :id and name = :name;

②： select … from local_index_example where name = :name;

对于以上两个查询来说，如果查询第一步是走索引的话，则：

局部前缀索引 local_prefixed_index 只对 ① 有用；

局部非前缀索引 local_nonprefixed_index 则对 ① 和 ② 均有用；

Globally partitioned index（全局分区索引）

全局分区索引，顾名思义，就是针对整个表空间（全局）来说的。在此，索引按范围（Range）或散列（Hash，Oracle 10g中引入）进行分区，一个分区索引（全局）可能指向任何（或全部的）表分区

与局部索引不同，全局索引只有一类，即全局前缀索引（prefixed global index），索引分区键必须作为索引定义的第一列，否则执行会报错。

非分区索引：

在未进行分区的表空间配置索引；

四、 索引方法

HASH 算法：

在某一列中，通过HASH算法计算，得到HASH值，并且排序在HASH数组。可以实现一次定位

B-Tree算法：

将索引结果按照B+树为存储结构实现，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动

二者区别：

HASH算法的优势：

（1）等值查询；

HASH算法不适用的场景：

（1）不支持范围查询

（2）不支持索引完成排序

（3）不支持联合索引的最左前缀匹配规则

（4）没有大量重复键值 

（5）组合索引不能利用部分索引字段进行查询

MySQL中，只有HEAP/MEMORY引擎才显示支持哈希索引。而常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

Oracle中，使用hash索引必须使用hash集群，相当于定义了一个hash集群键，通过这个集群键来告诉oracle来存储表。存储数据时，所有相关集群键的行都存储一个数据库块中，方便快速定位查找。hash索引是一个等值查询，通过hash函数确定行的物理位置。

创建Oracle Hash集群之前必须知道集群键上不同值的数目，而且在创建HASH集群的时候指定这个值。低估了集群键的不同值的数目可能会造成集群的冲突(两个集群的键值拥有相同的哈希值)。这种冲突是非常消耗资源的，会造成使用更多的内存来存储额外行，造成额外的I/O。如果不同哈希值的数目已经被低估，就必须在重建这个集群之后改变这个值。ALTER CLUSTER命令不能改变哈希键的数目；其次HASH集群还可能浪费空间，如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长预留好空间，HASH集群可能就不是最好的选择。

增删改操作频率不高的等值查询推荐使用HASH算法进行查询，否则推荐使用BTREE算法查询

五、 索引的使用

1.索引不会包含有null值的列

只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

2.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4.like语句操作

一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

5.不要在列上进行运算

这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6.不使用not in和<>操作

六、 索引优点&缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，降低了数据的维护速度。

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

索引只是提高效率的一个因素，如果数据库有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一， 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二， 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三， 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四， 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

七、 名词解释

最左前缀集合：以最左边的为起点任何连续的索引都能匹配上（即索引字段从左向右排列）

分表：是指把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。操作是按照事先设定好的规则获得子表表名，然后去操作他。

分区：分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。操作是操作大表的名字，数据库自动去组织分区的数据；

八、 执行工具

查看表索引情况：

MySQL：show index from sys_user;

Oracle：select * from USER_INDEXES where table_name = 'TableName'

select table_name,constraint_name,constraint_type from user_constraints where table_name='TableName

select table_name,constraint_name,constraint_type from dba_constraints where table_name=' TableName '