chapter03_架构优化和索引_2_索引基础知识

• (1) 数据越多时，索引越重要

  (2) 如果索引了多列数据，那么列的顺序非常重要 --> 索引的最左前缀原则

  (3) 索引在__存储引擎层__实现, 而不是在服务器层

• MySQL支持的索引类型

  (1) BTree索引

  (2) Hash索引

  (3) RTree索引

• BTree索引

  (1) BTree索引只是一种称呼，实际实现有可能是B+树

  (2) 大概的结构

    指针1  key1  指针2  key2 ...指针n+1
  

  直到叶子页面才是包含数据的

  (3) 索引中保存数据的顺序是按照创建索引的顺序

    CREATE TABLE People {
  
        first_name VARCHAR(50) NOT NULL,
        last_name VARCHAR(50) NOT NULL,
        dob DATE NOT NULL,
        gender ENUM('M','F') NOT NULL,
        INDEX( last_name, first_name, dob)
    );
  

  此时，在一个key中的结构大概类似于

    ('Bo', 'Chen', '1994-10-18')
  

  对于每个叶子页面，保存数据记录的顺序也是按照创建索引的顺序排序

    ('Basinger', 'Viven', '1976-12-08'), ('Basinger', 'Viven', '1979-11-24')
  

  (4) 最左前缀原则：只支持以下几种查询方式

  1° 匹配全名

  查询中和索引中的所有列匹配，并且顺序也相同

  2° 匹配最左前缀

  查询中匹配索引中的前N列，并且顺序相同

  3° 匹配列前缀

  查询中匹配索引第一列的前缀，例如查询last_name中以’B’开头的人

  4° 匹配范围值

  查询中匹配索引第一列的范围，例如查询last_name中大于Allen小于Barry的人

  5° 精确匹配前面的部分并且匹配后面某个范围中的另一部分

  例如查询first_name为Allen，last_name为Smith，出生日期大于1970-10-01小于1970-12-31的人

  6° 只访问索引的查询

  ???

  (2) 局限性的几种情况

  1° 查找必须从索引的最左面一列开始，否则就用不上索引

  2° 不能跳过索引中的列查询，此时用上的还是前面连续的列

  例如

    SELECT * FROM PEOPLE WHERE last_name = 'MARK' AND dob = '1960-01-01';
  

  只用到了索引的第一列

  3° 不能访问出现了范围查询后面的列

  例如

    SELECT * FROM PEOPLE WHERE last_name = 'MARK' AND first_name LIKE 'J%' AND dob = '1960-01-01';
  

  只用到了前两列

• 哈希索引

  (1) 原理

  对一些列计算一下hash值，每当需要查询的时候，根据待查询的值计算hash值，在hash表中寻找这个值，hash表中存储着hash值和记录的位置指针，然后根据指针找到记录，判断一下是否是符合要求的记录

  hash表中是按照hash值排好序的，所以在hash表中找hash值的过程可以使用二分查找很快的搜索到

  (2) 局限性

  1° 如果建立hash索引时使用了A,B,C三列，那么查询时为了使用hash索引，必须要在WHERE中把这三列都用上（因为要根据散列函数计算hash值必须全用上）

  2° hash表中保存的是hash值和记录的位置。如果记录在内存中可以很快定位记录，如果记录在磁盘上定位也需要时间

  3° hash索引只支持=、IN这种确定范围查询，部分前缀LIKE、范围查询都是不支持的

  4° 如果hash函数选的不好，会导致一个hash值对应好几条记录指针，每个都要判断

  (3) 只有Memory引擎__显式__支持hash索引

  但是，InnoDB会建立__自适应hash索引__：当一些索引值被频繁访问时，InnoDB会自动在内存中建立hash表

• MYSQL对使用索引的顺序会优化，例如索引中列的顺序是 a = A and b = B and c = C，但是实际如果使用了 b = B and a = A and c = C 也会优化成正确顺序，所以只要使用了就行，顺序无关紧要