漫谈索引

目录

数据库索引

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. —— [ 维基百科 ]

数据库索引是一种可以提高数据库查询速度的数据结构，同时数据库需要消耗更多的资源用以维护和存储该数据结构。索引可以快速的定位数据，而不需要每次查询数据库表中的所有数据。

---

聚簇索引

Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s).

聚簇索引的顺序与数据库中数据的存储顺序相同，因此一个表至多能创建一个聚簇索引。
正因索引顺序与物理存储顺序相同，因此需要检索的下条纪录与当前纪录的相邻，可以有效减少数据块的读取次数。

术语“聚簇“表示数据行行和相邻的键值紧凑的存储在一起。因为无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚簇索引。

非聚簇索引

The data is present in arbitrary order, but the logical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines).
In a non-clustered index,
The physical order of the rows is not the same as the index order.
The indexed columns are typically non-primary key columns used in JOIN, WHERE, and ORDER BY clauses.
There can be more than one non-clustered index on a database table.

数据库中数据的存储顺序与非聚簇索引的顺序无关。非聚簇索引中存储数据物理存储地址的指针。一个数据库表中可以创建多个非聚簇索引。创建非聚簇索引的非主键列可以用在JOIN，WHERE，ORDER BY等子查询语句中。

---

数据库索引类型

• 主键索引
  CREATE TABLE `order` (
`ID` int(30) NOT NULL AUTO_INCREMENT,
`CREATE_TIME` timestamp NOT NULL,
`UPDATE_TIME` timestamp NOT NULL,
`VERSION` int(11) NOT NULL,
`ORDER_NO` varchar(32) NOT NULL,
`MEMBER_ID` varchar(32) NOT NULL,
`STORE_ID` varchar(32) NOT NULL,
`STATUS` varchar(32) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `index_order_no` (`ORDER_NO`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
• 唯一键索引
  ALTER TABLE `order` ADD UNIQUE index_order_no` USING BTREE (ORDER_NO);
• 普通索引
  -单键索引
  ALTER TABLE `order` ADD INDEX `index_uid` (MEMBER_ID), ADD INDEX `index_sid` (STORE_ID), ADD INDEX `index_status` (`STATUS`);
  -组合键索引
  ALTER TABLE `order` ADD INDEX `index_uid_sid_status` (MEMBER_ID, STORE_ID, `STATUS`);

上述sql相当于创建了：
1 MEMBER_ID,STORE_ID,STATUS
2 MEMBER_ID,STORE_ID
3 MEMBER_ID
三条索引，故当查询语句中不存在值MEMBER_ID时，该所以不能被使用。组合索引创建顺序为：通过某列索引过滤之后剩余数据条数较少的且该列值不为空时，应将该列索引置前。
相较于单键索引中创建的三条索引，当列MEMBER_ID值不为空时，该组合键索引效率较高。

---

MySQL索引

• HASH

  Java中HashMap即是一种hash算法的实现，HashMap采用链地址发来解决hash冲突问题。
  HashMap采用数组＋链表结构实现，数组中存放链表的头节点。

  put操作：

  1. 计算key的hash值
  2. 根据该hash值与数组的长度，获取链表所在的index值
  3. 根据index获取数组中的链表的头节点，若头节点不为空则循环该链表查找当前key是否已存在，若存在则将oldValue替换为新的value并返回oldValue；若当前key不存在或该头节点为空，则将创建新的节点并将该节点插入到链表的头部并返回null。

  tips：使用HashMap时应注意HashMap的自动扩容问题（map中当前size>=capacity*loadFactor时，会创建新的hash表并将原hash表中的数据根据当前数组的长度rehash后复制到新的hash表中），创建map前可确定元素个数时应指定map的capacity。

  get操作：

  1. 计算key的hash值
  2. 根据该hash值与数组的长度，获取链表所在的index值
  3. 根据index值获取数组中的链表的头节点，若头节点不为空则循环该链表查找当前key对应的value值，若头节点为空则直接返回null。

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

1. Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
   由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

2. Hash 索引无法被用来避免数据的排序操作。
   由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

3. Hash 索引不能利用部分索引键查询。
   对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

4. Hash 索引在任何时候都不能避免表扫描。
   前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

5. Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
   对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

   • BTREE

   TODO

---

Oracle索引

TODO

参考：MySQL的btree索引和hash索引的区别