高性能mysql笔记—索引篇

最近在读高性能mysql这本书，这一系列文章只为读书笔记

一、索引基础

        索引是存储引擎用于快速查找记录的一种数据结构，所以索引本质就是数据结构，根据索引类型的不同，所对应的数据结构也不尽相同，索引类型对应的数据结构将在第二节介绍。索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为mysql只能高效的使用索引的最左前缀。创建一个包含两个列的索引和两个只包含一个列的索引是大不相同的。

二、索引类型

        索引是在存储引擎层实现的，所以并没有统一的索引标准：不同的存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一索引类型，其底层的实现方式也可能不一样。下面主要讨论的是InnoDB和MyISAM两种存储引擎支持的索引类型。

        1、B-Tree索引

        使用术语上叫“B-Tree”，实际上底层存储引擎可能使用不同的存储结构，比如，NDB集群存储引擎使用的是T-Tree(一种特殊的AVL树)，而InnoDB和MyISAM使用的都是B+Tree。InnoDB和MyISAM虽然使用相同的存储结构，但是它们索引存储的数据以及使用方式并不相同。下图是建立B-Tree结构（技术实现上是B+Tree）上的索引：

        B-Tree对索引列是顺序组织存储的，所以很适合范围查找数据。假设有如下数据表：

CREATE TABLE People(
    last_name varchar(50) not null,
    first_name    varchar(50) not null,
    birth   date        not null,
    gender  enum('m','f') not null,   
    key(last_name,first_name,birth)  
);

       对于表中的每一行数据，索引中包含了last_name,first_name和birth列的值。下图显示B-Tree索引是如何组织数据存储的：

        我们注意到，索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时的顺序。

        B-Tree适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀查找。上面的索引对如下类型的查询有效：

        1）全值匹配

            指的是和索引中的所有列进行匹配，例如上面的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。

        2）匹配最左前缀

            上面的索引可用于查找所有last_name为Allen的人，即只使用索引的第一列。

        3）匹配列前缀

           也可以只匹配某一列的值的来头部分。例如上面的索引可用于查找last_name以J开头的人，这里也只用到了索引的第一列。

        4）匹配范围值

            例如上面的索引可用于查找last_name在Allen和Barrymore之间的人。这里也只用到了索引的第一列。

        5）精确匹配某一列并范围匹配另外一列

         上面的索引查找所有last_name是Allen，并且first_name 是字母K开头的人。即第一列last_name全匹配，第二列first_name范围匹配。

        6）只访问索引的查询

            B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，无需访问数据行，后面会专门讨论这种“覆盖索引的优化”。

        因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY 操作。一般来说，如果B-Tree可以按照某种方式查找值，那么也可以按照这种方式用于排序。

        下面是关于B-Tree索引的限制：

        1）如果不是按照索引的最左列开始查找，则无法使用索引。例如，上面的索引无法查找first_name是Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。

        2）不能跳过索引中的列。也就是说，上面的索引无法用于last_name是Smith、并且在某个特定日期出生的人，这里只能用到索引的第一列。

       3）如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询。列入查询WHERE    last_name='Smith'  AND first_name LIKE 'J%' AND   birth='1976-12-23'，这个查询只能用到索引的前两列，因为这里的LIKE是一个范围条件。如果范围查询列值的数量有限，可以使用多个等于条件来代替。

        看到这里就会明白，索引的顺序很重要。在优化性能的时候，可能需要想到的列但是顺序不同的索引来满足不同的查询需求。

        2、哈希索引

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