[TOC]

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

mysql索引 (opens new window)

mysql 存储引擎

1. (MyISAM存储引擎):表结构/表中的数据/索引

   • mysql5.5版本之下默认存储方式使用MyISAM存储引擎

     #MyISAM存储引擎
     - 只支持表级锁:  在多用户操作数据事锁定某一张表保护数据安全.
     - 适合做读 ,插入数据比较频繁的,对修改和删除比较少的
     - mysql5.5版本之下默认存储方式使用MyISAM存储引擎
     

2. (InnoDB存储引擎):表结构/表中的数据

   • mysql5.6版本以上默认存储方式使用InnoDB存储引擎

     #InnoDB存储引擎
     - 支持 事务:例如在金融转账时保障数据安全
     - 支持 行级锁(也支持表级锁): 在多用户操作数据事锁定某一行保护数据安全.
     - 支持 外键:表与表之间关联
     - 适合并发比较高的数据库,对事物一致性要求较高的,相对更适应频繁的修改删除操作
     - 索引和数据存储在一起
     - mysql5.6版本以上默认存储方式使用InnoDB存储引擎
     

3. (MEMORY存储引擎):表结构

   • 存储在内存中
   • 优势:增删改查速度快
   • 劣势:重启数据消失,容量有限

索引的分类

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

1. 数据结构分类索引

• nnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

• 在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

  • 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；

  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；

  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

  • 其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

a. B+tree 索引数据结构 !!!

mysql 为什么采用B+tree(精品好文) : https://mp.weixin.qq.com/s/w1ZFOug8-Sa7ThtMnlaUtQ

• 先创建一张商品表，id 为主键，如下：

  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;
  

• 商品表里，有这些行数据：

  

• B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表.

  

b. B+tree 通过主键查询过程

• 比如，我们执行了下面这条查询语句，这条语句使用了主键索引查询 id 号为 5 的商品。查询过程是这样的，B+Tree 会自顶向下逐层进行查找：

  • 将 5 与根节点的索引数据 (1，10，20) 比较，5 在 1 和 10 之间，所以根据 B+Tree的搜索逻辑，找到第二层的索引数据 (1，4，7)；

  • 在第二层的索引数据 (1，4，7)中进行查找，因为 5 在 4 和 7 之间，所以找到第三层的索引数据（4，5，6）；

  • 在叶子节点的索引数据（4，5，6）中进行查找，然后我们找到了索引值为 5 的行数据。

• 数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点，也就是进行了 3 次 I/O 操作。

• B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

c. B+tree 通过二级索引查询过程

• 主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

  • 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；

  • 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

• 将前面的商品表中的 product_no （商品编码）字段设置为二级索引，那么二级索引的 B+Tree 如下图，其中非叶子的 key 值是 product_no（图中橙色部分），叶子节点存储的数据是主键值（图中绿色部分）。

  二级索引 B+Tree

• 如果我用 product_no 二级索引查询商品，如下查询语句：

  select * from product where product_no = '0002';
  

• 会先检二级索引中的 B+Tree 的索引值（商品编码，product_no），找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。如下图

  

• 不过，当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再查主键索引查，比如下面这条查询语句：

  select id from product where product_no = '0002';
  

• 这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。

d. B+tree 的优点!!!!

1、B+Tree vs B Tree

• B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

• B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

2、B+Tree vs 二叉树

• 对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

• 在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。

• 而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

3、B+Tree vs Hash

• Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

• 但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

2. 按物理存储分类

• 从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

  • 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；

  • 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

• 所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

3. 按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

a. 主键索引!!!

• 主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

• 在创建表时，创建主键索引的方式如下：

  CREATE TABLE table_name  (
    ....
    PRIMARY KEY (index_column_1) USING BTREE
  );
  

b. 唯一索引!!!

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

• 在创建表时，创建唯一索引的方式如下：

  CREATE TABLE table_name  (
    ....
    UNIQUE KEY(index_column_1,index_column_2,...) 
  );
  

• 建表后，如果要创建唯一索引，可以使用这面这条命令：

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

c. 普通索引

• 普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

• 在创建表时，创建普通索引的方式如下：

  CREATE TABLE table_name  (
    ....
    INDEX(index_column_1,index_column_2,...) 
  );
  

• 建表后，如果要创建普通索引，可以使用这面这条命令：

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

d. 前缀索引

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

• 使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

• 在创建表时，创建前缀索引的方式如下：

  CREATE TABLE table_name(
      column_list,
      INDEX(column_name(length))
  ); 
  

• 建表后，如果要创建前缀索引，可以使用这面这条命令：

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

4. 按字段个数分类

• 从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

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

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

a. 联合索引!!!

• 通过将多个字段组合成一个索引，该索引就被称为联合索引。比如将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)，创建联合索引的方式如下：

  CREATE INDEX index_product_no_name ON product(product_no, name);
  

• 联合索引(product_no, name) 的 B+Tree 示意图如下：

  

• 可以看到，联合索引的非叶子节点保持了两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下按 name 字段比较。

• 也就是说，联合索引查询的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

• 比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

  • where a=1；

  • where a=1 and b=2 and c=3；

  • where a=1 and b=2；

• 需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

  • where b=2；

  • where c=3；

  • where b=2 and c=3；

• 另外，建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

索引优缺点

• 索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

  • 需要占用物理空间，数量越大，占用空间越大；

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；

  • 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护。

1. 什么时候适用索引？

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段；
• 经常用于 GROUP BY 和 ORDER BY 的字段；

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

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如电商项目的用户余额，因为索引字段频繁修改，那就意味着需要频繁的重建索引；

索引优化

• 这里说一下几种常见优化索引的方法：

  • 前缀索引优化；

  • 覆盖索引优化；

  • 主键索引最好是自增的；

  • 防止索引失效；

1. 前缀索引优化

• 前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？

• 使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

• 不过，前缀索引有一定的局限性，例如：

  • order by 就无法使用前缀索引；

  • 无法把前缀索引用作覆盖索引；

2. 覆盖索引优化

• 覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

• 假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

• 我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

• 所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

3. 主键索引最好是自增的

• 我们在建表的时候，都会默认将主键索引设置为自增的，具体为什么要这样做呢？又什么好处？

  • InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

  • 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为不需要重新移动数据，因此这种插入数据的方法效率非常高。

  • 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

• 举个例子，假设某个数据页中的数据是1、3、5、9，且数据页满了，现在准备插入一个数据7，则需要把数据页分割为两个数据页：

  

• 出现页分裂时，需要将一个页的记录移动到另外一个页，性能会受到影响，同时页空间的利用率下降，造成存储空间的浪费。

• 而如果记录是顺序插入的，例如插入数据11，则只需开辟新的数据页，也就不会发生页分裂：

  

4. 防止索引失效

• 用上了索引并不意味着查询的时候会使用到索引，所以我们心里要清楚有哪些情况会导致索引失效，从而避免写出索引失效的查询语句，否则这样的查询效率是很低的。

• 索引失效原因

  1. 索引列不能参与计算,参与计算索引不生效

  2. 查询的数据范围大,索引不生效

     • 比较运算 </>/>=/<=----

     • 范围查询 between and

       #分页 面试题
       select * from 表 order by age limit 1000000,5;
       select * from 表 where id between 1000000 and 1000005; #速度更快,分页推荐格式
       

     • 模糊查询 like

       • 结果的范围大,索引不生效
       • 结果 abc% 索引生效, %abc索引不生效

  3. 如果一列的内容区分度不高,索引不生效

  4. 对两列内容进行条件查询时

     • and

       • and条件两端的内容,优先选择一个有索引的,并且树形结构更好的进行查询;
       • 两个条件先完成范围较小的,缩小压力

       #两个条件都要成立
       select * from 表 where id = 1000000 and email = '123@163.com';
       

     • or or条件的不会进行优化,索引不生效

       • 条件中有or的想要命中索引的,or两边的条件都要有索引

       • select * from 表 where id = 1000000 or email = '123@163.com';
         

执行计划

• 实际过程中，可能会出现其他的索引失效场景，这时我们就需要查看执行计划，通过执行计划显示的数据判断查询语句是否使用了索引。

• 如下图，就是一个没有使用索引，并且是一个全表扫描的查询语句。

  

• 对于执行计划，参数有：

• • partitions 表分区

  • possible_keys 字段表示可能用到的索引；

  • key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；

  • key_len 表示索引的长度；

  • rows 表示扫描的数据行数。

  • type 表示数据扫描类型，我们需要重点看这个。

    • type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

      • ALL（全表扫描）；

      • index（全索引扫描）；

      • range（索引范围扫描）；

      • ref（非唯一索引扫描）；

      • eq_ref（唯一索引扫描）；

      • const（结果只有一条的主键或唯一索引扫描）。

• 考虑到查询效率问题，全表扫描和全索引扫描要尽量避免。

总结