索引

概念

    索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可以快速访问表中特定信息。

 

创建索引

#创建普通索引
#ALTER TABLE gg ADD INDEX common_index (id);
#创建唯一索引（允许多个null）
ALTER TABLE gg ADD UNIQUE common_index2 (login_name);
#创建主键包含唯一索引
ALTER TABLE gg ADD PRIMARY KEY pri_index (id);

查询索引

show index from gg

删除索引

DROP INDEX common_index2 on gg;

分类

• 唯一索引。不允许有重复值
• 普通索引。
• fulltext索引。用来进行全文检索，只适用于myisam存储引擎
• apatial索引。只适用于myisam和空间（apatial）数据类型
• hash索引。memory默认索引。如果打算对memory数据表进行范围查询，则最好是建立一个前缀索引。

 

    或者

• 主键索引
• 唯一索引
• 普通索引
• 联合索引
• 覆盖索引

 

索引结构

•     B+树结构

        非叶子节点至存储key，同一层的叶子节点存储数据（减少内存）。叶子之间增加了链表，使得范围查询速度加快（比B书最大的优势，b书是所有叶子、非叶子都存储数据）。这种结构使得树结构高度很低

•     Hash索引

        一般情况下hash比b+速度快，但是有范围查询、分组、排序就速度慢了。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

缺点：

• 哈希索引也没办法利用索引完成排序；
• 不支持最左匹配原则；
• 在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题；
• 不支持范围查询

• 目前大多采用b+树，对于哈希索引，InnoDB是自适应哈希索引的（hash索引的创建由InnoDB存储引擎引擎自动优化创建，我们干预不了，不能人为生成hash索引）！

 

聚集索引

    以主键创建的索引。如果没有，则第一个非空的列为聚集索引，否则会建立一个隐藏的row_id作为聚集索引。(指的innodb)

     InnoDB的主键索引与行记录是存储在一起的，故叫做聚集索引.主键索引的叶子节点，存储主键，与对应行记录（而不是指针），innodb的普通索引也是存储的主键。所以优化innodb主键不建议过长，因为所有索引都会存储主键，导致索引很占内存；主键推荐使用递增数据，这样不至于插入一条记录，索引重新调整。

 

非聚集索引（二级索引）

    以非主键创建的索引。

    Myisam的索引与行记录是分开存储的，所以是非聚集索引。它的主键索引与普通索引没有太大区别

区别：聚集索引在叶子节点存储的是表中的数据，非聚集索引存储的是主键+索引列。使用非聚集索引查询后，拿到主键，再根据主键去表中查询数据。

 

覆盖索引

    覆盖索引就是把要查询出的列和索引是对应的，不做回表操作。速度较快，尽量使用。

例如：现在我创建了索引(username,age)，在查询数据的时候：select username , age from user where username = 'Java3y' and age = 20

 

最左匹配原则

    如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、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等的顺序，mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。

如有索引(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c.

一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。