一、索引算法
一般，我们看到术语“索引”和“键”交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。
单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。
多列索引：即一个索引包含多个列，但是此时列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列（一般用在WHERE字句带AND操作会有用）。
1）B+tree索引
B+树是一种经典的数据结构，由平衡树和二叉查找树结合产生，它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有的记录节点都是按键值大小顺序存放在同一层的叶节点中，叶节点间用指针相连，构成双向循环链表，非叶节点（根节点、枝节点）只存放键值，不存放实际数据。
普通索引
普通索引是MySQL最基本的B+树索引类型，它没有任何限制。
唯一索引
它与前面的”普通索引”类似，不同的就是：索引列的值必须唯一，但允许为NULL。如果是联合唯一索引，则列值的组合必须唯一。
这里需要明白主键索引（primary key）、唯一索引（unique index）、普通索引（inde）与主键约束（primary key）、唯一键约束（unique key）的区别，这里说的索引都是Btree索引。
唯一键约束（unique key）和主键约束（primary key）创建时都会默认隐式创建同名的唯一索引，以键名为索引名。唯一键约束索引列的值必须唯一，但允许为NULL。而主键约束索引不允许有空值，且在一张表中只允许有一个主键约束，但允许存在复合主键索引，也就是两个字符共同组成一个主键索引，两个字段组合必须唯一。一般是在建表的时候同时创建主键约束。
唯一键约束和主键约束与唯一索引的区别是，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除。但你可以先创建唯一索引，后创建主键约束或唯一键约束。这样显式创建的唯一索引不受主键约束或唯一键约束失效的影响。
2）Hash索引
所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO。既然Hash索引的效率要比B-Tree高很多，为什么大家不都用Hash索引而还要使用B-Tree索引呢？任何事物都是有两面性的，Hash索引也一样，虽然Hash索引效率高，但是Hash索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。
1）Hash索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
2）Hash索引无法被用来避免数据的排序操作。
3）Hash索引不能利用部分索引键查询。
4）Hash索引每次都要表扫描。
5）Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
3）全文索引
FULLTEXT类型索引，可以在CHAR，VARCHAR或者TEXT类型的列上创建，之前仅仅是MYISAM存储引擎支持，从MySQL5.6版本开始支持InnoDB引擎的全文索引，语法层面上大多数兼容之前MyISAM的全文索引模式。全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。
4）空间索引
MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从素有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性，MySQL 5.7对GIS改进了很多。开源关系数据库系统中对GIS的解决方案做得比较好的就是PostgreSQL的PostGIS。
下面主要介绍B+树索引的管理。
二、B+树索引管理
第一种：普通索引
直接创建索引
|
1 |
mysql> create index INDEX_NAME on TABLE_NAME(Column[length]) [using btree|hash]; |
修改表结构添加索引
|
1 |
mysql> alter TABLE_NAME add index [INDEX_NAME] on (Column[length]) [using btree|hash]; |
创建表时创建索引
|
1 |
mysql> create table TABLE_NAME( [...],INDEX [INDEX_NAME] (Column[length]) [using btree|hash]; |
[length]：可选，如果是CHAR或VARCHAR类型，length可以小于字段实际长度。如果是 BLOB 和 TEXT 类型，必须指定length。
[using btree|hash]：可选，btree或hash都是用来排序算法，默认是btree。
第二种：唯一索引
直接创建索引
|
1 |
mysql> create unique index INDEX_NAME on TABLE_NAME(column(length)); |
修改表结构添加索引
|
1 |
mysql> alter TABLE_NAME add unique index [INDEX_NAME] on (Column(length)); |
创建表时创建索引
|
1 |
mysql> create table TABLE_NAME ( [...], UNIQUE [INDEX_NAME] (Column(length)); |
创建唯一键约束
|
1 |
mysql> alter TABLE_NAME add unique key [INDEX_NAME] on (Column(length)); |
创建主键约束
|
1 |
mysql> create table TABLE_NAME(id INT NOT NULL AUTO_INCREMENT, Name VARCHAR(16) NOT NULL,PRIMARY KEY(id)); |
这里就体现出来了，我们使用唯一索引和唯一键约束都可以创建一条唯一索引，在创建唯一键约束时会自动创建一条唯一键索引。
另外有时候可能会碰到我们需要为以前的表创建一个索引，有可能以前的数据中存在重复的记录机会报Duplicate entry，那怎么办呢?
|
1 |
mysql> alter ignore table TABLE_NAME add unique index(user_id,user_name); |
它会删除重复的记录（别怕，会保留一条），然后建立唯一索引，高效而且人性化。
第三种：联合索引
直接创建索引（联合唯一索引）
|
1 |
mysql> create unique index INDEX_NAME on TABLE_NAME(FIELD1,FIELD2); |
修改表结构添加索引（联合唯一索引）
|
1 |
mysql> alter table TABLE_NAME add unique index INDEX_NAME(FIELD1,FIELD2); |
创建表时创建索引（普通联合索引）
|
1 |
mysql> 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)); |
索引删除
|
1 |
mysql> drop index INDEX_NAME on TABLE_NAME; |
查看索引
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> show index from TABLE_NAME; Table: user Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: account_id Collation: A Cardinality: 21941 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: |
通过SHOW INDEX可以查看到一个表上的所有索引。
Table：索引所在的表名。
Non_unique：非唯一的索引，0表示是唯一索引，1表示不是唯一索引。
Key_name：索引名称，用户可以通过这个名字来DROP INDEX。
Seq_in_index：索引中该列的位置，对于单列索引都是1，对于联合索引就比较直观了，会有序号1、2、3等。
Column_name：索引列的名称。
Collation：列是以什么方式存储在索引中的，可以是A或是NULL，B+树索引总是A，即排序的。如果使用了Hash索引，这里会显示NULL。因为Hash根据hash桶存放索引数据，而不是对数据进行排序。
Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。
Sub_part：是否是列的部分索引，如果值为NULL表示整个列被索引了。如果是数值，那么表示只取了这个列的部分数据做索引了。
Index_type：索引的类型，默认都是BTREE索引。
Comment：索引注释。
如何检测所创建的索引是否有效（或者说索引使用情况）
|
1 |
mysql> show status like 'Handler_read%'; |
Handler_read_key越高越好。
Handler_read_rnd_next越高越不好。
三、索引性能测试
1）分别在没有索引和有索引的情况下执行查询。
2）分别在没有索引和有索引的情况下新增数据。
创建没有索引的表
|
1 2 3 |
mysql> create database it; mysql> use it; mysql> create table indextest(id int,number int,password varchar(50)); |
插入数据
|
1 |
$ for ((i=1;i<=500000;i++));do `mysql -uroot -predhat -e "insert into it.indextest values($i,floor($i+rand()*$i),md5($i));"`;done |
创建有索引的表
|
1 2 3 |
mysql> create table indextest01(id int,number int,password varchar(50)); mysql> insert into indextest01 select * from indextest; mysql> create index iid on indextest01(id) using btree; |
查看表
|
1 2 3 4 5 |
mysql> show tables\G *************************** 1. row *************************** Tables_in_it: indextest *************************** 2. row *************************** Tables_in_it: indextest01 |
刷新查询缓存，如果没有效果重启MySQL是最有效的
|
1 |
mysql> reset query cache; |
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select number,password from indextest where id>200000; 300000 rows in set (6.31 sec) mysql> select number,password from indextest where id>200000; 300000 rows in set (0.23 sec) mysql> select number,password from indextest01 where id>200000; 300000 rows in set (0.35 sec) mysql> select number,password from indextest01 where id>200000; 300000 rows in set (0.18 sec) |
执行查询计划
|
1 |
mysql> explain select number,password from indextest01 where id>200000\G; |
查询计划可以用于输出查询的详细信息，可以看出此条SQL语句有没有用到索引。
另外一个没有索引的表在接收数据导入时比有索引的表要快很多。因为当一个表建立索引之后，导入数据时。每导入一行数据随之索引就需要变化，从而导致导入数据慢。
