MySql索引笔记

MySQL 索引是什么

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引，单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引，组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用，因此索引也会有它的缺点，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

MYSQL索引类型

NORMAL：普通索引

FULLTEXT：全文索引

SPATIAL：空间索引（目前还没用过）

UNIQUE：表示唯一的，不能重复的索引，可为NULL

PRIMARY KEY：唯一索引，不能为NULL

KEY：普通非唯一索引

MYSQL索引方法

目前主要有以下几种索引方法：B-Tree，Hash，R-Tree。

B-Tree：

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

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索引。

MYSLQ索引创建

1、单列索引

1.1、普通索引，最基本最常用的索引。

CREATE INDEX IndexName ON `TableName`(`字段名`(length))；
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))；

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

1.2、唯一索引，与普通索引类似，但是不同的是唯一索引要求所有列的值是唯一的，但是允许有空值。

CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
ALTER TABLE TableName ADD UNIQUE INDEX IndexName(`字段名`(length));

1.3、主键索引，与唯一索引类似，但是不能有空值。

主键索引建立的规则是 int优于varchar，一般在建表的时候创建，最好是与表的其他字段不相关的列或者是业务不相关的列，一般会设为 int 而且是 AUTO_INCREMENT自增类型的

2、组合索引

一个索引包含2个及以上的字段

CREATE INDEX IndexName ON `TableName`(`字段名1`(length),`字段名2`(length),`字段名3`(length))；
ALTER TABLE TableName ADD INDEX IndexName(`字段名1`(length),`字段名2`(length),`字段名3`(length))；

组合索引(IndexName) 那么它实际包含的是3个索引 (字段1) ，(字段1,字段2)，(字段1,字段2,字段3)，那么应该如何使用组合索引呢？

查询的时候遵循组合索引的【最左前缀】原则，什么是最左前缀？就是索引where时的条件要按照建立索引的时候字段的排序方式，举例说明一下：

a、不按索引最左列开始查询索引无效，例如index(‘c1’, ‘c2’, ‘c3’) ，查询条件是where ‘c2’ = ‘aaa’或者where `c2` = `aaa` and `c3`=`sss` ，这些情况索引都是无效的，where c1 = 'aaa' and c2 = 'bbb'或者where c1 = 'aaa' and c2 = 'bbb' and c3 = 'ccc'索引有效。

b、查询中某个列有范围查询，则其右边的所有列索引无效，例如index(‘c1’, ‘c2’, ‘c3’) ，查询条件是where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ ，该查询c1和c2索引有效，c3索引无效，因为like是范围查询。

c、跳过某个字段来进行查询其实和第2条一样的原理，索引无效，例如index('c1','c2','c3')，查询条件是where  c1 > 1 and c2 = 2 and c3 = 3，索引无效

3、全文索引

文本字段类型是text，如果建立的是普通索引，那么只有对文本的字段内容前面的字符进行索引，其字符大小根据索引建立索引时申明的大小来规定，如果文本中出现多个一样的字符，而且需要查找的话，那么其条件只能是 where column lick '%xxxx%' 这样做会让索引失效，这个时候全文索引就发挥作用了。

CREATE FULLTEXT INDEX IndexName ON `TableName`(`字段名`);
ALTER TABLE TableName ADD FULLTEXT INDEX IndexName(`字段名`);

未完待续。。。