索引用于快速查找出在某个列中有一特定值的行，不适用索引，MySQL必须从第一条记录开始读完整个表，直到找到相关行。表越大，查询数据花费的时间越多。如果表中查询的列有一个索引，MySQL能快速达到某个位置去搜索文件，而不必查看所有数据。因此使用索引是数据库优化最直接有效的方式，所以了解索引对于开发人员尤为重要。
索引简介
索引是一个单独的、存储在磁盘上的数据库结构，他们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或者多个列中有一特定值的行，所有MySQL列类型都可以被索引，对于相关列使用索引是提高查询操作速度的最佳途径。
索引的优点：
1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的查询速度，这也是创建索引的最主要原因。
3.在实现数据的参考完整性方面，可以加速表与表之间的连接。
4.在使用分组和排序字句进行数据查询时，也可以显著减少查询中分组和排序的时间。
索引的缺点：
1.创建和维护索引要耗费时间，数据量越大耗费时间也会越长。
2.索引需要占磁盘空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
索引的原理
一 索引原理
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
二 磁盘IO与预读
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
三、索引的数据结构任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。
如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
###b+树的查找过程如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
###b+树性质1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
索引的分类
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有的存储引擎支持每个表至少16个索引，总长度至少为256字节，大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种：Btree和hash，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持Btree索引，MEMORY/HEAP存储引擎可以支持hash和Btree索引。
MySQL索引的分类：
1.普通索引：MySQL的基本索引类型，允许索引字段的列插入重复值和空值。
2.唯一索引：索引列字段必须唯一，但允许有空值，如果是组合索引，则组合必须唯一，主键索引是一种特殊的唯一索引，不允许有空值。
3.单列索引和组合索引：单列索引只包含单个列，一个表可以有多个单列索引。组合索引指在表的多个字段组合上创建索引，使用组合索引时遵循最左前缀集合。
4.全文索引：全文索引类型为FullText，在定义索引的列上支持值得全文查找，允许在这些索引列中插入重复值和空置，全文索引可以在char、varchar和text类型的列上创建，MySQL中只有MyISAM存储引擎支持全文索引。
5.空间索引：空间索引是对空间数据类型的字段建立的索引，只能在MyISAM存储引擎的表中创建。
索引创建
创建表的时候创建索引
语法结构：
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATITAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
1.创建普通索引
最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据库的访问速度。
CREATE TABLE book
(
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
info VARCHAR(255) NOT NULL,
INDEX(info)
);没有指定索引名称时会默认使用索引字段作为索引名称，此处为给字段info创建名称为info索引。
2.创建唯一索引
创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表，它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
); 此处为给id字段创建一个名为UniqIdx的唯一索引。
3.创建单列索引
单列索引是在数据表中某一个字段上创建的索引，一个表中可以创建多个单列索引，前面两个例子创建的都为单列索引
CREATE TABLE t2
(
id INT NOT NULL,
name CHAR(30) NULL,
INDEX SingleIdx(name(20))
); 4.创建组合索引
组合索引是在多个字段上创建一个索引
CREATE TABLE t3
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, age(100))
);此处为给id,name和age字段创建一个名为MultiIdx的组合索引。
组合索引可起几个索引的作用，但必须遵从“最左前缀”：利用索引中最左边的列集来匹配行。例如这里又id，name和age 3个字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合：（id，name，age）、(id，name) 或者 id。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如（age）或者（name，age）组合则不能使用索引查询。
5.创建全文索引
FullText（全文索引）可以用于全文搜索。只有MyISAM存储引擎支持FullText索引，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。
CREATE TABLE t4
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;此处为给info字段创建一个名为FullTxtIdx的组合索引，索引长度为20，全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。
6.创建空间索引
空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。
CREATE TABLE t5
(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
) ENGINE=MyISAM;此处为g字段上创建了名为spatIdx的空间索引，注意创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM。
在已经存在的表上创建索引
1.使用 ALTER TABLE 语句创建索引
语法结构：
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
例如：使用ALTER TABLE在bookname字段上添加名为BkNameIdx索引，SQL语句如下：
ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));其他类型的索引只需将INDEX换成其他类型即可如：UNIQUE INDEX，FULLTEXT INDEX，SPATIAL INDEX。
2.使用 CREATE INDEX 创建索引
语法结构：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
例如：使用CREATE INDEX在bookname字段上添加名为BkNameIdx索引，SQL语句如下：
CREATE INDEX BkNameIdx ON book(bookname)
删除索引
1.使用ALTER TABLE删除索引
语法结构：ALTER TABLE table_name DROP INDEX index_name;
2.使用DROP INDEX语句删除索引
语法结构：DROP INDEX index_name ON table_name;
总结
为数据库选择正确的索引是一项复杂的任务，如果索引列较少，则需要的磁盘空间和维护开销都较少。如果在一个大表上创建了多种组合索引，索引文件也会膨胀很快。另一方面，索引较多可覆盖更多的查询，可能需要实验若干不同的设计，才能找到最有效的索引。对字符串类型的字段进行索引，如果可能应该指定一个前缀长度。例如，如果有个CHAR(255)的列，在前10个或者30个字符内，多数值是惟一的，则不需要对整个列进行索引，短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作，因此如何更好的使用索引，还需要我们在实际中多尝试多测验。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
