一、聚簇索引与非聚簇索引

 

1.1 概念

聚簇索引：叶子节点存的是索引值和行数据值。每张表只能有一个聚簇索引，“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。

非聚簇索引：又叫二级索引，叶子节点保存的不是行的物理位置，而是主键值。查找时，通过索引先找到主键值，在通过主键值找到数据行的数据页，再通过数据页找到数据。

1.2 在InnoDB和MyIsam中的应用

结论：

  • InnoDB一定有聚簇索引，也可以有非聚簇索引。
  • MyIsam只有非聚簇索引。因为MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

聚集索引和非聚集索引原理图：

MSQLSERVER 聚集索引非聚集索引 mysql非聚集索引查询原理_mysql

InnoDB查询过程描述：

  1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
  2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

 MyIsam查询过程描述：

MyISAM的是非聚簇索引，B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，
这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。聚簇索引，叶子节点上的data是主键(所以聚簇索引的key，不能过长)。

在InnoDB引擎中，只有主键索引才是聚簇索引，其他所有都是非聚簇索引。

那么问题来了，如果没有主键，InnoDB怎么形成聚簇索引？

MSQLSERVER 聚集索引非聚集索引 mysql非聚集索引查询原理_数据库_02

上图是官网的说明，mysql的innodb引擎本身存储的形式就必须是聚簇索引的形式 , 在磁盘上树状存储的 , 但是不一定是根据主键聚簇的 , 有三种情形:

  1. 有主键的情况下 , 主键就是聚簇索引
  2. 没有主键的情况下 , 第一个非空null的唯一索引就是聚簇索引
  3. 如果上面都没有 , 那么就是有一个隐藏的row-id作为聚簇索引

 1.2.3 优缺点

聚簇索引优点：

  1. 聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。
  2. 聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的

 聚簇索引缺点

  1. 聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
  2. 插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
  3. 聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。
  4. 聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。

 与聚簇索引相对的是非聚簇索引，所以聚簇索引的优点就是非聚簇索引的缺点，聚簇索引的缺点就是非聚簇索引的优点。

二、覆盖索引与回表

覆盖索引概念
通过索引项的信息可以直接返回所需的查询列，也就是平时所说的不需要回表操作。

判断标准
使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

注意

1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值
2、Hash 和full-text索引不存储值，因此MySQL只能使用B-TREE
3、并且不同的存储引擎实现覆盖索引都是不同的
4、并不是所有的存储引擎都支持它们
5、如果要使用覆盖索引，一定要注意SELECT 列表值取出需要的列，不可以是SELECT *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降，不能为了利用覆盖索引而这么做

InnoDB

1、覆盖索引查询时除了除了索引本身的包含的列，还可以使用其默认的聚集索引列
2、这跟INNOB的索引结构有关系，主索引是B+树索引存储，也即我们所说的数据行即索引，索引即数据
3、对于INNODB的辅助索引，它的叶子节点存储的是索引值和指向主键索引的位置，然后需要通过主键在查询表的字段值，所以辅助索引存储了主键的值
4、覆盖索引也可以用上INNODB 默认的聚集索引
5、innodb引擎的所有储存了主键ID，事务ID，回滚指针,非主键ID，他的查询就会是非主键ID也可覆盖来取得主键ID

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点
1、索引项通常比记录要小，所以MySQL访问更少的数据
2、索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O
3、大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引
4、覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了 

2.1 回表

什么叫回表：

        在innodb引擎中，我们会为创建普通索引，普通索引b+数的叶子节点存储的并不是整行数据，而是主键，再根据主键查询主键的b+数，再从主键b+数取出来整行记录，这就叫回表；

 2.2 作业

MSQLSERVER 聚集索引非聚集索引 mysql非聚集索引查询原理_主键_03

答案：第1、3、5是使用了覆盖索引。覆盖索引就是避免回表。

曾经被问到过这样的面试题：

通常我们建议查询的时候尽量不要用" select *" ，那么在什么情况下用"select *" 查询与"select 字段"性能差别不大？

——这个问题但是我没有答出来，现在回想起来，可不就是当查询的条件和需要查询的字段无法避免回表操作，即无法用到覆盖索引的时候。

三、联合索引与最左前缀原则

3.1最左匹配原则：

假设我们创建（col1，col2，col3）这样的一个组合索引，那么相当于对col1列进行排序，也就是我们创建组合索引，以最左边的为准，只要查询条件中带有最左边的列，那么查询就会使用到索引。

最频繁使用的列放在左边；查看列的选择性（即该列的索引值数量与记录数量的比值），比值越高，效果越好；

3.2 索引项比较规则：

如果索引项为varchar或者其他类型，索引排序的时候，其内部其实是把该字段的每个字符转成ASCII码，组装在一起，再进行排序。

例如：

ASCII码：a-97,b-98,c-99，g-103；
abc就是：979899；
abg就是：9798103；

那么排序起来自然是9798103>979899，即abg>abc

索引排序示意图:

MSQLSERVER 聚集索引非聚集索引 mysql非聚集索引查询原理_聚簇索引_04

 like语句也遵循最左匹配原则？

mysql索引是B+树，B+树是排序的，like '%xx'，如果 like语句%号在前面，是没办法确定数据的范围的，所以不走索引。
反之，如果是 like 'xx%'语句，like语句%号在后面，前面值是确定的，可以在B+树中进行范围查找，索引是走索引的。

3.3 延伸到联合索引

3.2延伸到联合索引，其实道理是一样的。单列索引你可以把他看成特殊的联合索引，把它认为是几个字段按照顺序组合成一个字符串。

用这种思想，下面我们讲联合索引与like做类比：

还是上面的创建（col1，col2，col3）为联合索引：

  1. where col1=x1 and col2 =x2,这种情况我们是不是可以类比成 like 'xx%' 呢？
  2. where col2=x2 and col3 =x3,这种情况我们是不是可以类比成 like '%xx' 呢？

在like语句中，1是走索引的， 2是不走索引的。

用like类比联合索引，是不是更好理解和记住最左前缀原则了。

四、隐式转换

4.1.mysql中 int 与 varchar  类型的隐式转换：

  1. 如果表定义的是varchar字段，传入的是int型数字，则会发生隐式转换
  2. 表定义的是int字段，传入的是varchar数字字符串，不会发生隐式转换，如果在与数字字符串比较大小并且数字字符串还超过int定义的长度（会以字符串类型比较'$'）会隐式转换
  3. 隐式转换会扫描全表，造成字段的索引的阻塞。

注意：

数字类型的0001等价于1；
字符串的0001和1不等价；

4.2.在oracle中

create table tn (id number, name varchar2(1)); 
create index idx_tn on tn (id); 
create index idx_tn2 on tn (name);

select * from tn where id='123';     VARCHAR2->NUMBER的隐式转换，不会对索引产生影响。

select * from tn where name=123;    这里Oracle会将数值类型的123转换为VARCHAR2字符类型，和name进行比较，会用用全表扫描，导致name的列索引失效。

所以NAME和VARCHAR2之间可以进行隐式转换，其中VARCHAR2->NUMBER不会导致索引失效，NUMBER->VARCHAR2会让索引失效，因此这种隐式转换，是需要注意避免。

五、SQL优化索引的生产级使用

5.1 索引列的离散性

我们先来看一个重要的属性列的离散度，公式如下：

count(distinct(column_name)) : count(*) -- 列的全部不同值个数：所有数据行行数

如果在B+Tree里面的重复值太多，MySQL的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。

MSQLSERVER 聚集索引非聚集索引 mysql非聚集索引查询原理_主键_05

如上图，这个给我们的启发是什么？——建立索引，要使用离散度（选择度）更高的字段。

5.2 几点建议与优化原则

建议：

  1. 在用于where判断order排序和join的（on）字段上创建索引
  2. 索引的个数不要过多（浪费空间，更新变慢）
  3. 区分度低的字段，例如性别，不要建索引（离散度太低，导致扫描行数过多）
  4. 更新频繁的值，不要作为主键或者索引（页分裂）
  5. 组合索引把散列性高（区分度高）的值放在前面
  6. 创建复合索引，而不是单列索引
  7. 不建议用无序的值作为索引，例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）
  8. 若在多个字段都要创建索引的情况下，联合索引优于单值索引

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销（CostBaseOptimizer），它不是基于规则（Rule-BasedOptimizer），也不是基于语义。怎么样开销小就怎么来。 另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。 

MSQLSERVER 聚集索引非聚集索引 mysql非聚集索引查询原理_聚簇索引_06

参考博文：mysql高效索引之覆盖索引MySQL索引-B+树（看完你就明白了）