MYSQL索引

索引简介

    索引是存储引擎用于加快查询的一种数据结构，一种优良的索引策略能够对查询性能有效的优化。索引有以下三个优点：

1. 索引减少了需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机IO变成顺序IO

    但是，索引并不是越多越好，过多的索引易导致磁盘使用率上升，给系统带来负担。总体来说，一般只有索引帮助存储引擎快速查找到记录带来的好处大于其带来的开销时候，索引才是有效的。一般用于中到大型表的多值属性；当表太小，直接全表扫描最有效；当表太大，索引的代价会随之增加，这时用分区技术会更好。

索引类型

    索引的类型，一般分成三类：B+树索引，哈希索引，全文索引。

哈希索引

    哈希索引是基于哈希表而实现的，只有精确匹配索引的所有列的查询才能有效，范围查找是不能使用哈希索引的。查找时间复杂度为O(1)，速度很快。对于InnoDB存储引擎而言，其拥有一种自适应哈希索引（InnoDB会根据表的使用情况自动的为表生成哈希索引，用户无法控制），冲突机制采用链表法，哈希函数采用除法散列的方式。

    B+树索引可以利用前缀索引，哈希索引不行。

全文索引

    全文索引是一种特殊的索引类型，用于查找文本中的关键词，而不是比较索引中的值，所以匹配方式与其他的索引完全不一样，有点类似于搜索引擎中的查找。

B+树索引

    B+树索引是大多是存储引擎中使用的。分为聚集索引和辅助索引（二级索引）。聚集索引指的是B+树中的叶子节点，不单单包含索引的键值，还包含了实际的数据行（但是非叶子节点只存储索引键值），“聚集”的意思就是数据行和索引键值存储在一起。辅助索引中叶子节点保存了索引键值和数据行主键值（注意不是数据行的指针，所以辅助索引需要两次索引查找）

    因为实际数据只能按照一颗B+树进行排序，所以每张表中只能有一个聚集索引。InnoDB默认为通过主键进行聚集数据，如果没有定义主键，InnoDB会选择一个非空索引替代（MyISAM不支持聚集索引，无论是主键还是二级索引都是非聚集索引）

    聚集索引的优点：

1. 可以把相关数据保存在一起，减少磁盘IO。
2. 数据访问更加快，因为数据和索引保存在一起，非聚集索引需要两次索引查找，聚集索引只需要一次
3. 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

    聚集索引缺点：

1. 插入、更新聚集索引的代价很高
2. 在插入或者主键被更新的时候会产生“页分类”问题，页分类会导致表占用很多磁盘空间，可以采取一些策略进行优化。
3. 聚集索引会导致全表扫描变慢

     联合索引指的是对表中的多列进行索引。如下图所示，对表中的列a和列b建立联合索引，可以用于select * from t where a=x and b=x; 也可以用于列a单独索引，select * from t where a=x；但是不能用于列b单独索引，因为其不是有序的。因此使用联合索引的时候主要要把选择性最高的列放在索引的最前面。

    覆盖索引，即一个索引包含所需要查询的字段的值。通常而言，在辅助索引中，可以直接查询到记录，而不需要查询聚集索引中的记录，就是覆盖索引

MYSQL索引基本操作

索引创建

ALTER TABLE 表名 ADD 索引类型 [索引名] (字段名)

    索引类型可以为：index（普通索引），unique（唯一索引），primary key（主键索引），fulltext index（全文索引）。同时也可以用create index在创建表的时候创建索引

CREATE INDEX 索引名 ON 表名(列名)

查看表中的索引

show index from 表名;

删除索引

drop index 索引名 on 表名 ;

alter table 表名 drop index 索引名 ;

 

参考 《高性能MYSQL》