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(不以通配符%或_开头的情形)。
不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
唯一索引与主键索引的比较：
- 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引)
- 对于多列索引，不是使用的第一部分，则不会使用索引
- like查询是以%开头
- 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
Hash索引和btree索引的区别：
