MySQL索引_什么是索引_索引的分类_什么时候需要/不需要创建索引_优化索引_索引失效

索引

1. 什么是索引

帮助存储引擎快速获取的数据的一种数据结构，是数据的目录，以空间换实际

2. 索引的分类

按数据结构分类

B+ Tree索引、HASH索引、Full-Text索引

• 有主键，默认使用主键作为聚簇索引的索引键
• 没有主键，选择第一个不包含NULL值的唯一列作为聚簇索引的索引键
• 上面两个都没有的情况下，自动生成一个隐式自增id列作为…
• 其他索引都属于辅助索引（二级索引、非聚簇索引）
• 创建的主键索引和二级索引默认属于B+ Tree索引

例：

CREATE TABLE `product`  (
  `id` int(11) NOT NULL,
  `product_no` varchar(20)  DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
（id为主键）

主键索引的B+ Tree：

叶子节点存放数据，非叶子节点只存放索引，每个节点里的数据按主键顺序存放。每一个叶子节点有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成双向链表

数据库的索引和数据都存在硬盘，读取一个节点当作一次磁盘I/O操作。B+ Tree存储千万级数据只需要3-4层高度，即最多需要3-4次磁盘I/O。查询效率很高。

二级索引的B+ Tree：

叶子节点存放的是主键值，而不是实际数据

（将product_no设置为二级索引）

用二级索引查询数据：

select * from product where product_no = '0002';

找到对应的叶子节点，获取主键值，再通过主键索引的B+Tree树查询到对应的叶子节点，获取整行数据。称为回表，需要两个B+ Tree才能查到数据

select id from product where product_no = '0002';

查询的数据在二级索引的B+ Tree的叶子节点里查询到，不用再查主键索引再查。称为覆盖索引，只需要查一个B+ Tree

为什么mysql innoDB 选择 B+ tree作为索引的数据结构？

1. B+ Tree vs B Tree

2. B树的非叶子节点也要存储数据，所以B+树的单个节点的数据量更小，相同的磁盘I/O次数下，就能查询到更多的节点

3. B+树的叶子节点采用双链表连接，B树做不到

4. B+ Tree vs 二叉树

5. B+Tree的搜索复杂度：O(logdN)，d表示节点允许的最大子节点的个数

6. 二叉树的搜索复杂度：O（log2N）

7. 二叉树检索到目标数据所经历的磁盘I/O次数更多

8. B+ Tree vs Hash

9. Hash在做等值查询时，搜索复杂度O(1)，不适合做范围查询

按物理存储分类

聚簇索引（主键索引），二级索引（辅助索引）

• 主键索引的叶子节点存放实际数据
• 二级索引的叶子节点存放主键值
  • 如果查询的数据能在二级索引里查询到，则为覆盖索引
  • 如果查询的数据在二级索引里查询不到，需要先检索二级索引，找到对应叶子节点获取到主键值后，再检索主键索引，查询到数据，则为回表

按字段特性分类

主键索引、唯一索引、普通索引、前缀索引

• 主键索引

  • 建立在主键字段上的索引，一张表最多只有一个主键索引，索引列不允许有空值

  • CREATE TABLE table_name(
        ...
        PRIMARY KEY(index) USING BTREE
    )
    

• 唯一索引

  • 建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，允许有空值

  • CREATE TABLE table_name(
        ...
        UNIQUE KEY(index1,index2...)
    )
    

  • CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...); 
    

• 普通索引

  • 建立在普通字段上的索引

  • CREATE TABLE table_name(
        ...
        INDEX(index1,index2...)
    )
    

  • CREATE INDEX index_name ON table_name(index_column_1,index_column_2,...); 
    

• 前缀索引

  • 对字符类型字段的前几个字符建立索引，而不是整个字段上建立的索引。（可以建立在字段类型为char varchar binary varbinary的列上）

  • 减少索引占用的存储空间，提升查询效率

  • CREATE TABLE table_name(
        ...
        INDEX(column_name(length))
    )
    

  • CREATE INDEX index_name ON table_name(column_name(length))
    

按字段个数分类

• 单列索引

  • 建立在单列上的索引称为单列索引，比如：主键索引

• 联合索引（复合索引）

  • 建立在多列上的索引称为联合索引

  • CREATE INDEX index_product_no_name ON product(product_no, name);
    

  • 联合索引的B+树

  • 非叶节点用两个字段的值作为key值，在联合索引查询数据时，先按prodcut_no字段比较，相同的情况下再按name字段排序

  • 叶子节点保存主键值

  • 使用联合索引时，存在最左匹配原则

    • 例：创建了(a,b,c)联合索引

    • 1. 可以使用联合索引（有查询优化器，a字段的顺序不重要）
          where a=1；
          where a=1 and b=2 and c=3；
          where a=1 and b=2；
      2. 联合索引失效
          where b=2；
          where c=3；
          where b=2 and c=3；
          因为b和c是全局无序，局部有序
      

    • 例：a,b联合索引的B+ Tree

    • 注意到a是全局有序的，b是全局无序的，所以直接执行where b = 2这种查询条件没法使用联合索引，利用索引的前提是索引里的key是有序的

  • 联合索引范围查询

    • 并不是查询过程使用了联合索引就代表所有字段使用了联合索引进行索引查询 => 发生在范围查询（范围查询的字段可以使用到联合索引，但是范围查询字段后面的字段无法使用到联合索引）

    • 例：

      1. Q1: select * from t_table where a > 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
              a字段
              联合索引先按a字段值排序，所以a>1的记录一定是相邻的。但符合a>1条件的二级索引记录的范围里，b字段的值无序。
      2. Q2: select * from t_table where a >= 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
              a和b
              与1不同的是，a的查询条件是大于等于
              虽然在符合a>=1的二级索引记录的范围里，b字段是无序的，但是a=1时，b字段是有序的
              从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描。
      3. Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
              a和b
              mysql中between包含了边界值，类似于>= <=，所以类似于Q2查询语句。
      4. Q4: SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引（name, age）哪一个字段用到了联合索引的 B+Tree？
      

      

      4. 
              a和b
              j相等时，age字段有序，即从符合 name = 'j' and age = 22 条件的第一条记录时开始扫描，而不需要从第一个 name 为 j 的记录开始扫描 
      

    • 综上所述，联合索引的最左匹配原则，在遇到范围查询为> < 时，会停止匹配。对于>= <= BETWEEN like前缀匹配的范围查询，不会停止匹配

  • 索引下推

    • select * from table where a > 1 and b = 2
      

    • 找到第一个满足条件的主键值后，判断b是否=2，在联合索引里判断，还是回主键索引判断？

    • mysq5.6之前，只能回表，回到主键索引找到数据行，对比b字段值

    • 5.6之后引入索引下推优化，在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

  • 索引区分度

    • 在建立联合索引时，要把区分度大的字段排在前面，越有可能被更多的sql使用
    • 区分度计算公式：某个字段column不同值的个数/表的总行数
    • 如果索引区分度很小，不如不要索引

  • 联合索引进行排序

    • select * from order where status = 1 order by create_time asc
      

    • 可以使用status和create_time建立联合索引，可以避免mysql数据库发生文件排序（如果只用到 status 的索引，但是这条语句还要对 create_time 排序，这时就要用文件排序 filesort），提高查询效率

3. 什么时候需要 / 不需要创建索引？

索引的好处：提高查询速度

缺点：占用物理空间、创建和维护要耗费时间、降低表的增删改的频率（增删改后要进行维护）

什么时候适用索引？

• 字段有唯一性限制，比如商品编码
• 经常用于WHERE查询的字段
• 经常用于GROUP BY 和 ORDER BY的字段，查询时不需要再做排序

什么时候不需要创建索引？

• where条件，group by，order by用不到的字段
• 字段中存在大量重复数据（见区分度）
• 表数据太少
• 经常更新的字段

4. 优化索引的方法

前缀索引优化

使用某个字段中字符串的前几个字符建立索引，减少索引字段大小

增加一个索引页中存储的索引值，有效提高索引的查询速度

缺点：

• order by无法使用前缀索引
• 无法把前缀索引用作覆盖索引

覆盖索引优化

覆盖索引：查询的所有字段，在索引B+树的叶子节点上都能找得到的索引。从二级索引中查询得到记录，不需要回表

例：只需要查询商品的名称、价格，什么方式避免回表？

? 建立（商品ID、名称、价格）联合索引

主键索引最好是自增的

• 使用自增主键
  • 每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有数据，页面写满，就会自动开辟一个新页面
  • 插入一条新纪录，都是追加操作，不需要重新移动数据
• 使用非自增主键
  • 每次插入新数据时，有可能插入到现有数据页中间的某个位置，不得不移动其他数据来满足新数据的插入，甚至需要从一个页面复制数据到另一个页面=>页分裂，还有可能造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率
• 主键的长度不要太大，因为主键的长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也越小

索引最好设置为NOT NULL

• 为NULL会导致优化器在做索引选择时更加复杂，难以优化
• NULL值无意义，但是会占用物理空间（NULL值列表）

防止索引失效

避免写出索引失效的查询语句，否则查询效率很低

发生索引失效的情况：

• 使用左/左右模糊匹配时，like %xx like %xx%

• 在查询条件中对索引列做了计算、函数、类型转换操作

• 联合索引要能正确使用需要遵循最左匹配原则

• where子句中，如果or前的列是索引列，or后的不是索引列，那么索引会失效

• 其他索引失效的场景可以查看执行计划 explain ...

  • type字段：表示数据扫描类型

    • ALL：全表扫描

      • 最坏情况

    • index：全索引扫描

      • 和全表扫描差不多，对索引表进行全扫描

    • range：索引范围扫描

      • 一般在使用< > in between时

    • ref：非唯一索引扫描

      • 索引是有序的，即便有重复值，也是在很小的范围内扫描

    • eq_ref：唯一索引扫描

      • 使用主键/唯一索引时产生，通常使用在多表联查中
      • 例如，两张表进行联查，关联条件是user_id相等，且user_id是唯一索引

    • const：结果只有一条的主键/唯一索引扫描

      • 例如，select name from product where id = 1
      • 与eq_ref不同的是：与常量进行比较，查询效率更快。eq_ref通常用于多表联查