第二章 创建高性能索引

索引：索引是存储引擎用于快速查找出一条数据的一种数据结构。

一、.索引的类型：B-Tree 索引、哈希索引
    
    1.B-Tree 索引：最常用的索引，B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中放有指向子节点的指针，存储引擎根据这些指针向下层查找。
        通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限 。最终存储引擎要么找到对应的值，要么值不存在。
    
    可使用B-Tree索引的查询类型： 假设一个user表，索引为姓、名、生日（lastName,firstName,birthday），此索引对如下查询有效
        全值匹配 ：既查询条件为索引的三列
        匹配最左前缀 ：可以查找所有姓‘Allen’的人，即只使用第一列。
        匹配列前缀 ：可以用索引查询以‘J’开头的姓的人
        匹配范围值 ：例如索引可以查找姓在‘Allen’和‘Barry’之间的人。此例也只使用了索引第一列。
        精确匹配某一列并范围匹配另一列 ：索引可以查找姓‘Allen’和名‘J’开头的人，既第一列全匹配，第二列范围匹配
        只访问索引的查询 ：
    
    B-tree树索引的限制：
        1.如果不是按照索引最左列开始查找，则无法使用索引，比如无法使用例子中索引查找名为‘Green’的人。
        2.不能跳过索引的列，索引无法用于查找姓‘Allen’和生日为‘1982-09-15’的人，这是MYSQL只能使用索引的第一列
        3.如果查询的某列是范围查询，则其右边所有列无法使用索引优化查找，既索引无法用于where lastName = 'Allen' and firstName like 'J' and birthday = '1980-09-15',这时使用的是精确匹配某一列并范围匹配另一列查找
    总结：索引列的顺序对查询很重要，在优化性能时，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。 
    
    2.哈希索引 hash index :基于哈希表实现，只有精确匹配所有的列才有效，对于每一行数据，存储引擎都会根据索引列生成每条数据的hash code,哈希索引存储每条数据的hash code，同时保存指向每条数据的指针。

    哈希索引的限制：
        1.哈希索引只包含hash code和指针，而不存储字段值，所有不能使用索引中的值来避免读取行。
        2.哈希索引数据不是按照索引值顺序存储的，所有也无法用于排序。
        3.哈希索引不支持部分列匹配查找，需要全部匹配。
        4.哈希索引只支持等值比较查询，包括= 、in()、<=>
        5.访问哈希索引的数据非常快，除非有hash冲突(不同的索引值却有相同的hash code)。
        6.如何哈希索引冲突很多的话，一些索引维护操作代价会很高。例如在某个选择性很低的列建立哈希索引
    
    总结：因为这些限制，哈希索引只适用某些特定场合，而一旦适合哈希索引，则它带来的性能提升非常显著。例如，在数据仓库中有种经典的“星型”schema,需要关联很多查找表，哈希索引就非常适合。除了Memory外，NDB集群引擎也支持唯一哈希索引。

    3.空间数据索引 R-Tree : MyISAM表支持索引，可以用作地理数据存储。

    4.全文索引：是一种特殊的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文索引类似搜索引擎做的事情，而不是简单的where条件匹配

二、索引的优点

    1.索引大大减少了服务器需要扫描的数据量
    2.索引可以帮助服务器避免排序和临时表
    3.索引可以将随机I/O变为顺序I/O

    评价一个索引是否适合某个查询的‘三星系统’：索引将相关的记录放在一起则获得一星；如果索引中列顺序和查询中where顺序一致则获得二星；如果索引的列包含了查询中需要的全部列则获得三星。
    索引适合中大型表，数据量很小的表全表扫描效率更高，而特大型表，则需要考虑分区技术。

三、高性能索引策略

    1.独立的列：是指索引列不能是表达式的一部分，也不能是函数的参数。（始终将索引列单独放在等式一侧）
    
    2.前缀索引和索引选择性：如果需要索引一个很长的字符列(text,或者很长的varchar)，会让索引变的大且慢，可以考虑创建前缀索引。mysql前缀索引不支持做order by 和group by，也无法使用前缀索引做覆盖扫描。
        前缀索引多少字符，需要考虑选择性是否够高，同时又不能太长（节约空间），决定前缀的合适长度，select count(distinct left(列名,num))/count(*) from table_name;数值越高num越短越好。
        创建前缀索引语法：ALTER TABLE table_name ADD key(列名(num)) 

    3.多列索引：很多人对索引理解不够，常见的错误是为每个列建索引 和 错误的顺序创建索引。

    4.选择合适的列顺序：创建一个合适列顺序的索引经验法则是：将选择性最高的列放在索引最前列（不考虑排序和分组时）。在一个多列的B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是是第二列。
        索引，索引可以按照升序或者降序进行扫描，以满足符合顺序的查询需求。
    
    5.聚簇索引：是一种数据存储方式，具体细节依赖其实现方式，但InnoDB的聚簇索引实际上是在同一结构中保存了B-Tree索引和数据行。mySql的主键是聚簇索引。

    6.覆盖索引：索引包含了所需要查询的字段的值，我们称为‘覆盖索引’，mysql可以使用索引直接获取列的值，而不需要再读取数据行。

    7.冗余和重复的索引：重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，这会使数据库单独维护重复的索引，影响性能。
        冗余索引和重复索引有所不同，比如你建立了索引（A,B），再创建索引（A）就冗余了。（如果创建索引（B,A）或者（B）则不是冗余）
    
    8.未使用的索引：除了冗余和重复索引，还有一些服务器永远用不到的索引，这些索引完全是累赘，需要删除。需要工具来查找未使用的索引。

    9.索引和锁：索引可以让查询锁定更少的行，如果你的查询从不访问这些不需要的行，将会锁定的更少。