MySQL索引

索引类型

• normal：表示普通索引
• unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
• full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

总结，索引的类别由建立索引的字段内容特性来决定，通常normal最常见。

索引方法

• B-Tree

B-Tree是最常见的索引类型，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）
B-Tree在MyISAM里的形式和Innodb稍有不同：
MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址
InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录。

BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，例如：
select * from user where name like ‘jack%’;
select * from user where name like ‘jac%k%’;
如果一通配符开头，或者没有使用常量，则不会使用索引，例如：
select * from user where name like ‘%jack’;
select * from user where name like simply_name;

• Hash索引

1.仅支持"=",“IN"和”<=>"精确查询，不能使用范围查询：
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash
2.不支持排序：
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
3.在任何时候都不能避免表扫描：
由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果
4.检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引
5.只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引。

Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。
但为什么我们使用BTree比使用Hash多呢？主要Hash本身由于其特殊性，也带来了很多限制和弊端：

1. Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询。
2. 联合索引中，Hash索引不能利用部分索引键查询。
   对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。
3. Hash索引无法避免数据的排序操作
   由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。
4. Hash索引任何时候都不能避免表扫描
   Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。
5. Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高
   对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

• R-Tree索引

R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

创建索引

• 普通索引

创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)：

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定：

CREATE TABLE mytable(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	INDEX [indexName] (username(length))  
);

删除索引的语法：

DROP INDEX [indexName] ON mytable;

• 唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构：

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定：

CREATE TABLE mytable(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	UNIQUE [indexName] (username(length))  
);

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

1. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
2. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
3. ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
4. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引：

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name; \G

索引使用要点

ALTER TABLE t_user ADD INDEX name_city_phone(USERNAME,CITY,PHONE)  //普通复合索引

ALTER TABLE t_user ADD UNIQUE name_city_phone(USERNAME,CITY,PHONE) //唯一复合索引

这样的组合索引，其实相当于分别建立了三个索引。
为什么没有（CITY,PHONE）索引这是因为MYSQL组合查询只从最左边开始组合：始终包含username 【最左前缀匹配原则】
　　1.USERANME,CITY,PHONE
　　2.USERNAME,CITY
　　3.USERNAME,PHONE

并不是查询语句包含这三列就会用到该组合索引：

//这样的查询语句才会用到创建的组合索引
SELECT * FROM t_user where USERNAME="parry" and CITY="广州" and PHONE="180"
SELECT * FROM t_user where USERNAME="parry" and CITY="广州"
SELECT * FROM t_user where USERNAME="parry" and PHONE="180" 

//这样的查询语句是不会用到创建的组合索引，但是会走整个索引，只是效率低下
SELECT * FROM t_user where CITY="广州" and PHONE="180"
SELECT * FROM t_user where CITY="广州"

explain结果中的type字段。查询中分别是：

• type: index //没有按照复合索引顺序
• type: ref //按照顺序

index：这种类型表示mysql会对整个该索引进行扫描。要想用到这种类型的索引，只要是索引，或者某个联合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高。
ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，不会对索引中每一个数据都进行一一的扫描判断，会更快的取出数据。索引字段的数据必须是有序的，才能实现这种类型的查找。

注意：
虽然索引提高了查询的速度，但是降低了INSERT、UPDATE、DELETE（DML）的速度，因为在插入、修改、删除数据时，还要同时操作一下索引文件；

最左前缀匹配原则使用注意事项：

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。【范围查询的字段，在建立复合索引一定要置后】
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

索引使用注意事项：

• 索引会忽略null值，所以我们在设计数据库的时候设置为为NOT NULL;
• 短索引（给索引设置长度）不仅能够提高查询速度，而且能节省I/O操作。
• Mysql在查询的时候只会使用一个索引，但不是一个字段。
• 不鼓励使用like对索引操作：like"%aaa%"不会使用索引；但like“aaa%”会使用索引。即可以对固定起始值模糊查询。
• 不适用于否地操作（not in , <>, !=，or） //用到or地方，尽量用union，或者程序两次查找。
• 如果创建了索引，但是查询语句并没有使用，则会使原来的效率更差。
• 使用多个字段，查询更高效，所以尽量使用复合索引。

mysql的两种索引方法：

• BTREE（用于对等比较，如"=“和” <=>"） //<=> 安全的比对 ，用与对null值比较，语义类似is null（）。
• HASH（用于非对等比较，比如范围查询）>，>=，<，<=、BETWEEN、Like。