MySQL学习笔记-索引

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,98),(NULL,'李四',24,95),
(NULL,'王五',25,96),(NULL,'赵六',26,94),(NULL,'周七',27,99);

创建索引

创建索引的语法

CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  -- 默认是B+TREE
ON 表名(列名...);

我们现在主要是用B+ Tree的形式，基本不适用Hash索引了。

（1）为student表中姓名列创建一个普通索引

CREATE INDEX idx_name ON student(NAME);

我们将给name创建一个普通索引，名字定为idx_name；由于是普通索引，所以 [ ] 中的内容什么都不加！

（2）为student表中年龄列创建一个唯一索引

CREATE UNIQUE INDEX idx_age ON student(age);

查看索引

查看索引的语法

SHOW INDEX FROM 表名;

（1）查看student表中的索引

SHOW INDEX FROM student;

共有3个索引，两个是我们后面添加的，一个是创建表时设定的主键索引。

alter语句添加索引

添加索引的语法

-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

（1）为student表中name列添加全文索引

ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);

给name这一列添加全文索引，取名为idx_fulltext_name。

删除索引

删除索引的语法

DROP INDEX 索引名称 ON 表名;

（1）删除student表中的idx_score索引

DROP INDEX idx_score ON student;

索引的实现原则

索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的实现的B+Tree索引。

B+Tree是一种树型数据结构，是BTree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。我们逐步的来了解一下。

磁盘存储

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

举一个例子，如上图所示，一个磁盘有很多个磁盘块，多个磁盘块相互连接形成页，如果要读的数据是5，那么需要将整个磁盘块2读取出来，然后再从中过滤数据。

BTree

BTree结构的数据可以让系统高效的找到数据所在的磁盘块。

为了描述BTree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。

对于不同的记录，key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的BTree：

每一个磁盘块中可能既有索引，又有数据。

根据图中结构显示，每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。

模拟查找15的过程

（1）根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字15在区间（<17），找到磁盘块1的指针C1。

（2）P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】
比较关键字15在区间（>12），找到磁盘块2的指针C3。

（3）P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】
在磁盘块7中找到关键字15。

所以需要3次磁盘I/O操作，和3次内存查找操作。

B+Tree

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于BTree区别

非叶子节点只存储键值信息。
所有叶子节点之间都有一个连接指针。
数据记录都存放在叶子节点中。

使用B+Tree，还是以查询15为例，同样要经过磁盘块1、2，但是由于磁盘块1、2中存放的不是实际数据，所以是不用进行IO的。

索引的设计原则

对查询频次较高，且数据量比较大的表建立索引。

建议使用唯一索引，区分度越高，使用索引的效率越高。

索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。