Mysql性能优化(三) 索引的基础理解

概念

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的类型(关于数据结构方面的知识请自行寻找):

• B-Tree索引
• Hash索引
• 聚簇索引
• 非聚集索引(辅助索引、二级索引)

• 覆盖索引

B-Tree索引：

B-Tree索引使用B-Tree来存储数据，当然不同存储引擎的实现方式不同。B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，B-Tree索引的底层数据结构一般是B+树。

MyISAM索引实现

• 在MyISAM中索引的数据存储,叶节点的data域存放的是数据记录的地址.

 

这里设表一共有三列，假设我们以Col1为主键，上图是主键索引示意图。可以看出MyISAM的索引仅仅保存数据记录的地址。

• 如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

Col2索引同样也是一颗B+Tree，data域保存数据记录的地址。在MyISAM主索引和辅助索引(除了主键索引之外的在别的列上创建的索引)在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

1、第一个重大区别是:

InnoDB的数据本身就是索引中的value。从上文知道MyISAM索引和数据记录是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表的数据本身就是主索引。

 

上图是InnoDB主索引（同时也是数据表数据）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2、第二个与MyISAM索引的不同是

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

• 下图为定义在Col3上的一个辅助索引：

 

• 辅助索引的缺点：

辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

B-Tree索引数据结构对索引的使用分析：

• 全值匹配

全值匹配指的是和索引中的所有列进行匹配(需要按照顺序)

• 匹配最左前缀

即只使用索引的第一列

• 匹配列前缀

sql中的   xxx like'xxx%' 这里也是只用到了索引的第一列

• 匹配范围值

sql中的>、<  不可以使用!=、<> 、is null 、is not null 否则索引将失效

• 精确匹配某一行并范围匹配另外一行

即索引的第一列精确匹配，第二列范围匹配

• 只访问索引的查询,即查询只需要访问索引，而无须访问数据行。(覆盖索引后面解析)

• 因为索引中的节点是有顺序的，所以除了按值查找以外，索引还可以用于查询中的Order By操作(按照顺序查找)

B-Tree索引的一些限制：

• 如果不是从索引的最左列开始查找的，则无法使用索引
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询,外围之后的查询索引将失效

Hash索引

Hash索引是基于hash表实现的，对于每一行数据存储引擎都会对这行数据上的所有索引列计算成一个哈希码，hash码是一个比较小的值，并且不同键值计算出的hash码也不一样，Hash索引将所有的hash码存放到索引中，同时hash表中保存指向每个数据行的指针。所以只有在使用hash索引的时候只有精确匹配所有索引列的查询才有效。

只有Memory引擎显式支持hash索引,这也是Memory引擎的默认索引类型，Memory引擎同时也支持B-Tree索引，同时Memory引擎是支持非唯一hash索引的，如果多个列的哈希值相同，索引将会以链表的方式存放多个指向数据的指针。

Hash索引的优点

因为hash索引只需存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快

Hash索引的的限制

• Hash索引只包含hash值和行指针，而不是存储字段值，所以不能用索引中的值来避免读取行。
• Hash索引并不是按照索引的顺序排序的，所以也就无法用于排序
• hash索引也不支持部分索引列查找匹配
• hash索引只支持等值比较查询
• 当存在多个hash冲突的时候，即链表特别深的时候，存储引擎必须遍历链表中的所有的行指针。逐行进行比较，直到找到所有符合条件的行。而且删除的时候，也需要遍历链表中的所有的行，找到并且删除对应行的引用。

聚簇索引：

聚簇索引并不是一种单独的索引，而是一种数据的存储方式，InnoDB聚簇索引实际在同一个结构中保存了索引和数据行。当表中有聚簇索引的时候，数据整行存储到页子节点中，一个表只能存在一个聚簇索引，比如索引列是主键，则在索引中key为主键值value为当前行的值，所以聚簇索引中有表中全部的数据。

 

非聚集索引(辅助索引、二级索引)

非聚簇索引是相对于聚簇索引来说的，我们又称为辅助索引或二级索引。 InnoDB 的二级索引key为当前主键行的值value存储的是相应记录主键的值而不是物理位置的指针。

• MyISAM和InnoDB数据存储上的区别

 

覆盖索引：

如果一个索引包含所有需要扫描的字段的值  称之为覆盖索引。就是指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。也可以称之为实现了索引覆盖。

一、覆盖索引的优点

• 覆盖索引能够极大的提高性能。使用覆盖索引查询只需要扫描索引而无需回表
• 索引条目通常远小于数据行大小，所以如果只需读取索引，那mysql就会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也很有帮助，因为索引比数据更小，更容易全部放入内存中。
• 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。对于某些存储引擎，例如MyISAM，甚至可以通过optimize命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。