MySQL的索引

概述：

　　索引（Index）是帮助MySQL高效获取数据的数据结构。

　　索引是以表列为基础的数据库对象，它保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序，

　　其主要目的是提高数据库系统的性能，加快数据的查询速度和减少系统的响应时间。

　　在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以每种存储引擎的索引都不一定完全相同。

MYSQL目前提供了一下4种索引：

　　B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+树。
　　HASH 索引：只有Memory引擎支持，使用场景简单。
　　R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
　　Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文检索，InnoDB从MYSQL5.6版本提供对全文索引的支持。

B-Tree索引：

　　也就是通常所指的索引。

　　InnoDB使用B+Tree这种数据结构。

　　B+Tree：每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

　　适合查找范围数据。比如:找出所有以I到K开头的名字。

B-Tree索引具体又可分为：

普通索引：

　　最基本的索引类型。

–直接创建索引
CREATE INDEX index_name ON table(column(length))

–修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))   

–创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)

–删除索引
DROP INDEX index_name ON table

唯一索引：

　　索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。

–创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))

–修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

–创建表的时候直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (title(length))
);

主键索引：

　　它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

　　也可以用 ALTER 命令，但不能用CREATE INDEX语句创建主键索引。

　　每个表只能有一个主键。（主键相当于聚合索引，是查找最快的索引）

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
PRIMARY KEY(ID)  
);

多列索引（组合索引）：

　　ALTER TABLE table_name ADD INDEX indexName( name, address ) ;

　　对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

根据数据的存储方式的不同，B-Tree索引又可分为聚集索引和非聚集索引：

聚集索引：

　　CREATE CLUSTERED INDEX indexName ON mytable(mycolumn)

　　InnoDB的聚集索引实际上是在同一个结构中保存了B-Tree索引和数据行。

　　因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。

　　表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。

　　对于聚集索引， 叶子结点即存储了真实的数据行，不再有另外单独的数据页 。

非聚集索引：

　　CREATE UNCLUSTERED INDEX indexName ON mytable(mycolumn)

　　表数据存储顺序与索引顺序无关。

　　对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。

可以使用B-Tree索引的查询类型，以多列索引key(last_name, first_name, dob)为例：

全值匹配：指定查询的人的fitst_name, last_name和dob;
匹配最左前缀：查找指定了last_name的记录；
匹配列前缀：匹配某一列的值的开头部分，比如last_name以J开头；
精确匹配某一列并范围匹配另一列：查找last_name为Allen，并且first_name以k开头的；
只访问索引的查询：B-Tree通常可以支持只访问索引的查询，即查询只需要访问索引，而无需访问数据行。

B-Tree的一些限制：

如果不是按照从最左列开始查找，则无法使用索引。例如无法查找只指定了first_name或者dob的记录；
不能跳过索引中的列：不能在查找的时候只指定了last_name和dob，那么dob不会使用索引。
如果查询的时候有某个列的查询范围，则其右边的所有列都无法使用索引优化查找。比如对last_name使用了like，那么first_name和dob将不会使用索引。

哈希索引：

　　基于哈希表实现，只有精确匹配索引所有列的查询才有效。

　　因为它对每行中的所有索引列计算出一个哈希码，作为哈希表的键（原理是基于拉链法的解决碰撞的策略）。

　　在MySQL中只有Memory引擎显式地支持哈希索引，Memory引擎同时也支持B-Tree索引。

　　InnoDB中的自适应哈希索引：某些索引值使用非常频繁时，会在内存中基于B-Tree索引只上再创建一个hash索引。

哈希索引的一些限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能用索引中的值来避免读取行；
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；
哈希索引不支持部分列匹配查找，因为它用所有索引列来计算得到哈希值。
索引列只支持等值查询，理由同上；
哈希索引数据查找非常快，除非有很多哈希冲突；
如果哈希冲突比较高，一些索引维护操作的代价也会很高。比如性别字段，冲突会很高。

空间数据索引(R-Tree)：

　　ALTER TABLE table_name ADD SPATIAL INDEX indexName( line ) ;

　　MyISAM表支持空间索引，可以用作地理数据存储。

　　空间索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

　　必须使用 GIS 相关的函数来维护数据。

全文索引：

　　它查找的是文本中的关键词，而不是直接比较索引中的值。

　　使用 MATCH AGAINST，而不是普通的 WHERE。

–直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

 –修改表结构添加全文索引
 ALTER TABLE article ADD FULLTEXT index_content(content)

索引的优点：

　　创建唯一性索引，保证数据库表中每一行数据的唯一性
　　大大加快数据的检索速度，这也是创建索引的最主要的原因
　　加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
　　在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
　　通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

索引的缺点：
　　创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
　　索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
　　当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

应该在这些列上创建索引：

　　在经常需要搜索的列上，可以加快搜索的速度；
　　在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
　　在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
　　在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
　　在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
　　在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不应该创建索引的的这些列具有下列特点：

　　第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
　　第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
　　第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
　　第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引选择原则：

　　较频繁的作为查询条件的字段应该创建索引

　　定义有主键的数据列一定要建立索引。因为主键可以加速定位到表中的某一行。

　　定义有外键的数据列一定要建立索引。外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接。

　　唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，比如性别只有男，女

　　更新非常频繁的字段不适合创建索引

　　不会出现在 WHERE 子句中的字段不该创建索引

　　表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了;

　　对于定义为text、image和bit数据类型的列不要建立索引。

　　可以考虑使用索引的主要有两种类型的列：在where子句中出现的列，在join子句中出现的列，而不是在SELECT关键字后选择列表的列；

　　使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度；比如很长的字符串，对前10个或者20个字符进行索引能够节省大量索引空间，也可能会使查询更快。

　　利用最左前缀：Mysql会一直向右查找直到遇到范围操作（>，<，like、between）就停止匹配。比如a=1 and b=2 and c>3 and d=6；此时如果建立了（a,b,c,d）索引，那么后面的d索引是完全没有用到，当换成了（a,b,d,c）就可以用到。

　　MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。

　　不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

唯一索引与主键索引的比较：

唯一索引：

　　唯一索引不允许两行具有相同的索引值。

主键索引：

　　主键索引是唯一索引的特殊类型。

　　数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。该列称为表的主键。

　　在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

它们的一些比较：

　　(1)对于主健/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；

　　(2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；

　　(3)主健可作外健，唯一索引不可；

　　(4)主健不可为空，唯一索引可；（唯一索引还可以有多个NULL值）

　　(5)主健也可是多个字段的组合；

　　(6)主键与唯一索引不同的是：

　　　　a.有not null属性；

　　　　b.每个表只能有一个。

索引失效：

如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引)
对于多列索引，不是使用的第一部分，则不会使用索引
like查询是以%开头
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

Hash索引和btree索引的区别：

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

　　由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

　　由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

　　对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

　　前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

　　对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

　　如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引