MySQL索引

1.1.定义

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

1.2.数据结构

目前大部分数据库系统及文件系统都采用 B-Tree（B 树）或其变种 B+Tree（B+树）作为索引结构。B+Tree 是数据库系统实现索引的首选数据结构。在 MySQL 中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

1.2.1.MyISAM 引擎索引

索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，使用 B+Tree 作为索引结构，叶节点的data 域存放的是数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。同样也是一颗 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其data 域的值，然后以 data 域的值为地址，读取相应数据记录。MyISAM 的索引方式也叫做“非聚集索引”。

1.2.2.InnoDB引擎索引

虽然 InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同：
(1)InnoDB 的数据文件本身就是索引文件。表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，类型为长整形。同时，请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
(2)与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。不建议使用过长的字段作为主键，所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

1.2.3.聚簇索引与非聚簇索引

InnoDB 使用的是聚簇索引， 将主键组织到一棵 B+树中， 而行数据就储存在叶子节点上， 若使用"where id = 14"这样的条件查找主键， 则按照 B+树的检索算法即可查找到对应的叶节点， 之后获得行数据。 若对 Name 列进行条件搜索， 则需要两个步骤：
(1)第一步在辅助索引 B+树中检索 Name， 到达其叶子节点获取对应的主键。
(2)第二步使用主键在主索引 B+树种再执行一次 B+树检索操作， 最终到达叶子节点即可获取整行数据。
MyISM 使用的是非聚簇索引，非聚簇索引的两棵 B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引 B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方， 这两颗 B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

1.3.分类

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。
(1)FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
(2)HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
(3)BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
(4)RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

1.4.种类及其优点

(1)普通索引：加速查询
(2)唯一索引：加速查询 + 列值唯一（可以有null）
(3)主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
(4)组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
(5)全文索引：对文本的内容进行分词，进行搜索

1.5.索引操作

(1)创建索引
①　创建普通索引CREATE INDEX index_name ON table_name(col_name);
②　创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);
③　创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
④　创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name (col_name_1,col_name_2);
(2)通过修改表结构创建索引
①　单列索引：CREATE INDEX index_userName ON t_user(userName);
②　唯一索引：CREATE UNIQUE INDEX index_userName ON t_user(userName);
③　联合索引：CREATE INDEX index_userName_password ON t_user(userName,PASSWORD);
④　单列索引：ALTER TABLE t_user ADD INDEX index_userName(userName);
⑤　唯一索引：ALTER TABLE t_user ADD UNIQUE INDEX index_userName(userName);
⑥　联合索引：
1)CREATE INDEX必须提供索引名，对于ALTER TABLE，将会自动创建，如果你不提供；
2)CREATE INDEX一个语句一次只能建立一个索引，ALTER TABLE可以在一个语句建立多个，如：ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
3)只有ALTER TABLE 才能创建主键，ADD INDEX 不能；ALTER TABLE t_user ADD INDEX index_userName_password(userName,PASSWORD);
(3)创建表时直接指定索引
CREATE TABLE table_name (
ID INT NOT NULL,
col_name VARCHAR (16) NOT NULL,
INDEX index_name (col_name)
);
(4)删除索引
–直接删除索引DROP INDEX index_name ON table_name;
–修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;
(5)使用索引
①　其他索引使用条件
1)建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立。
2)如果表是经常需要更新的也不适合做索引。频繁更新会导致索引也会频繁更新，降低写的效率。
3)唯一性差的字段不适合创建索引。
4)当给一个字段创建了索引的话，而这个字段要进行like模糊查询的话，那么这个值左边不可以有%，因为索引查询是要从左到右的，你如果给它加上%后，左边的值不是确定的话，它会找不到这个索引。所以在使用like模糊查询的时候，值得左边不可以有%。
5)order by不会使用索引
6)or当前后2个字段都有索引时才可以索引出来否则不可以。
7)如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引。查询大量数据时,索引有效,但是慢
8)不使用索引的原因：因为索引时会先过一遍索引在过一遍数据
②　全文索引使用条件
1)当数据多且字段值有相同的值得时候用普通索引。
2)当字段多且字段值没有重复的时候用唯一索引。
3)当有多个字段名都经常被查询的话用复合索引。
4)普通索引不支持空值，唯一索引支持空值。
5)但是，若是这张表增删改多而查询较少的话，就不要创建索引了，因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，
6)若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集，
7)若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集，
8)若是删，则会把索引中以这个字段为名的索引的子集删掉。
9)所以，会对增删改的执行减缓速度，
10)所以，若是这张表增删改多而查询较少的话，就不要创建索引了。
11)更新太频繁地字段不适合创建索引。
12)不会出现在where条件中的字段不该建立索引。
③　复合索引使用
1)最左前缀匹配原则：在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。示例：对列col1、列col2和列col3建一个联合索引：KEY test_col1_col2_col3 on test(col1,col2,col3);联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
2)触发条件：
a.创建表：
CREATE TABLE E (e1 INT, e2 VARCHAR(9), e3 INT, PRIMARY KEY(e1, e3));这样就建立了一个联合索引：e1,e3
b.触发联合索引是有条件的：
1、使用联合索引的全部索引键，可触发索引的使用。例如：SELECT E.* FROM E WHERE E.e1=1 AND E.e3=2
2、使用联合索引的前缀部分索引键，如“key_part_1 常量”，可触发索引的使用。例如：SELECT E.* FROM E WHERE E.e1=1
3、使用部分索引键，但不是联合索引的前缀部分，如“key_part_2 常量”，不可触发索引的使用。例如：SELECT E.* FROM E WHERE E.e3=1
4、使用联合索引的全部索引键，但索引键不是AND操作，不可触发索引的使用。例如：SELECT E.* FROM E WHERE E.e3=2 OR E.e1=1

1.6.索引区别

(1)主键索引与唯一索引的区别
①　 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
②　 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
③　 唯一性索引列允许空值，而主键列不允许为空值。
④　 主键列在创建时，已经默认为空值 ++ 唯一索引了。
⑤　 一个表最多只能创建一个主键，但可以创建多个唯一索引。
⑥　 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
⑦　 主键可以被其他表引用为外键，而唯一索引不能。
(2)复合索引
①　 用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。
②　 就是几个字段联合在一起组成一个索引.复合索引的创建方法与创建单一索引的方法完全一样。
③　 但复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。
④　 当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度。
(3)全文检索：
①　 全文检索就是FULLTEXT，用于 MyISAM 表，在列类型为 CHAR、VARCHAR 或 TEXT 列上创建。
②　 将数据装载到一个没有 FULLTEXT 索引的表中，然后再使用 ALTER TABLE (或 CREATE INDEX) 创建索引，这将是非常快的。
③　 将数据装载到一个已经有 FULLTEXT 索引的表中，将是非常慢的。
④　MySQL自带的全文索引只能对英文进行全文检索。要检索中文用sphinx。
⑤　 创建全文检索：alter table 表名 add fulltext index 索引名(列名,[列名]，[列名]…（可以有多个，可以有一个）
⑥　 在创建表的时候建全文检索：
CREATE TABLE article (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
FULLTEXT (title, content) --在title和content列上创建全文索引
);