Mysql索引

2018-08-19 本文已影响168人游牧族人

一、索引的分类

1. 普通索引
最基本的索引类型。

(1.1)
创建：
CREATE INDEX index_name ON table_name(column1, column2, ...)
ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...)
修改(mysql 5.7+)：
ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name
删除：
DROP INDEX index_name ON table_name
ALTER TABLE table_name DROP INDEX index_name

2. 唯一索引
唯一的，不可重复的索引。

(1.2)
创建：
CREATE UNIQUE INDEX index_name ON table_name(column1, column2, ...)
ALTER TABLE table_name ADD UNIQUE index_name(column1, column2, ...)
修改(mysql 5.7+)：
ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name
删除：
DROP INDEX index_name ON table_name
ALTER TABLE table_name DROP INDEX index_name

3. 主键索引
是一种唯一性索引，但是他的字段必须指定为 PRIMARY KEY。

(1.3)
创建：
ALTER TABLE table_name ADD PRIMARY KEY(column1, column2, ...)
主键索引无法用 CREATE 语句创建。
删除：
ALTER TABLE table_name DROP PRIMARY KEY
若是主键是自增的，那么要先取消主键的自增。
一个数据表只能有一个主键。

4.全文索引
支持值的全文查找，适用于内容较多的字段上。

(1.4)
创建：
CREATE FULLTEXT INDEX index_name ON table_name(column1, column2, ...)
ALTER TABLE table_name ADD FULLTEXT(column1, column2, ...)
删除：
DROP INDEX index_name ON table_name
ALTER TABLE table_name DROP INDEX index_name

查看索引

查看索引
SHOW INDEX FROM table_name
SHOW KEYS FROM table_name

数据信息：
Table              表名
Non_unique         是否为唯一索引
Key_name           索引名称
Seq_in_index       索引列序列号
Column_name        列名称
Collation          列在索引中存放的方式
                   'A' 升序
                   'NULL' 无分类
Cardinality        索引中唯一值数目的估计值 表连接查询时数值越大效率越高
Sub_part           此列是否部分编入索引 
                   若是部分编入则显示编入的索引字符数
                   若是全部编入则显示 NULL
Packed             指示关键字是如何进行压缩的  若没有被压缩则显示 NULL
NULL               
Index_type         索引类型（BTREE | FULLTEXT | HASH | RTREE）
Comment            
Index_Comment

二、索引再分类

1. 聚簇索引
聚簇索引在一个数据表中只能有一个，一般是根据主键建立聚簇索引。聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引要比非聚簇索引查询效率高很多。
2. 非聚簇索引
非聚簇索引在一个数据表中可以有任意个，非聚簇索引的数据的物理顺序和索引顺序是不一致的。

区别与联系

聚簇索引在一个数据表中只能含有一个，非聚簇索引在一个数据表中可以含有多个。
聚簇索引数据物理顺序和逻辑顺序是一致的，非聚簇索引的物理顺序和逻辑顺序不是一致的。
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过存在着指向对应数据块的指针。
当我们查询聚簇索引时，可以直接定位到数据节点并获取到数据信息。当我们查询非聚簇索引时不会直接定位到数据节点，而是定位到聚簇索引上然后根据聚簇索引再次定位到数据节点。

三、索引实现方式

B树
B树的结构特点 [m阶]：

根节点至少有两个孩子。
每个中间节点都包含着 k-1 个元素和 k 个孩子 (m/2 <= k <= m)。
每个叶子节点都包含着 k-1 个元素 (m/2 <= k <= m)。
所有的叶子节点都位于同一层。
每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

一颗B树

B树的性能优势：
降低了树的高度进而减少了磁盘的IO操作。

B+树 [Mysql一般以B+树作为索引数据结构]
B+树结构特点：

若B+树的树度为m，则节点指针上限为2m+1。
非叶子节点不存储数据，只存储节点指针。所有数据都存储在叶子节点之中
叶子节点之间通过顺序指针进行连接。

一颗B+树

B+树的性能优势：

由于B+树中间节点不存储数据，只存储节点指针，因此一个磁盘页（中间节点）可以容纳更多的节点指针，降低树的高度，进而减少数据IO次数。
因为B+树每次查询数据必须进入到叶子节点，因此查找性能比较稳定。
由于B+树的叶子节点之间存在指针连接，因此对于数据的范围查找不需要进行中序遍历，直接叶子节点指针遍历就好，查询效率更高。

四、索引性能分析

由于索引数据量大，不能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。从磁盘上读取索引文件就要涉及到磁盘的IO操作，所以当IO次数越少我们的索引效率就会越高。索引的实现就是要尽可能的减少磁盘IO操作。
根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：
每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小。
综上所述，用B-Tree作为索引结构效率是非常高的。
而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

五、索引使用和优化

1. 最左前缀原理与相关优化
现在有一张数据表 artical

id	title	date	content
...	...	...	...
...	...	...	...

建立联合索引：

sql >> CREATE INDEX pri_index ON artical (id,title,date);

全列匹配查询：

sql >> SELECT * FROM artical 
       WHERE id = 1024 AND title = "Index Story" AND date = "2018-08-19" ;

理论上索引是对顺序敏感的，但是Mysql查询优化器会自动调整where子句的条件以使用适合的索引。因此下面的sql语句也是效率很高的全列匹配查询：

sql >> SELECT * FROM artical 
       WHERE date = "2018-08-19" AND id = 1024 AND  title = "Index Story" ;

最左前缀匹配查询：

sql >> SELECT * FROM artical WHERE id = 1024;

这种查询仅仅用到了索引的第一列，剩余两列没有用到。

sql >> SELECT * FROM artical WHERE id = 1024 AND date = "2018-08-19" ;

这种情况虽然出现了联合索引中的两个索引，但是由于中间的title索引未使用因此无法与左前缀连接，因此查询也是仅仅使用到了id索引，而date数据的条件筛选未用索引，使用的是普通where子句查询。

sql >> SELECT * FROM artical WHERE date = "2018-08-19" ;

查询条件没有指定索引的第一列，显然这样的查询方式没有用到索引。

范围查询索引使用

sql >> SELECT * FROM artical WHERE id < 1024 AND title = "Index Story" ;

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引只能用于一个范围列，如果查询过程中使用到了两个范围列则无法同时使用索引。

函数或表达式索引

sql >> SELECT * FROM artical where id -1 = 1024 ;

在查询语句中索引列含有函数或表达式时索引无效。
2. 索引选择
索引的建立可以加快数据信息的查询速度，但是索引也会消耗内存空间，因此索引的建立需要进行考虑，并不是哪个字段需要查询就为哪个字段建立索引，索引并不是越多越好。

当表的数据量较少时不需要建立索引，直接全表查询的效率也不慢。
当表的字段不经常查询但是却经常增加或删除时不需要建立索引。因为数据库对于索引的维护成本就会很高。
当一个字段有很多个重复数据时不需要建立索引。例如性别列。
当我们为字段建立索引时，若字段的长度很长，我们可以考虑建立前缀索引。

[参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html]