mysql查询索引的过程

综述

首先需要理解以下概念：

1. B+Tree、聚簇索引、二级索引、稀疏索引
2. mysql page的结构

其次，总体而言可以将获取数据的类型分为：

1. 命中了索引，可以直接从聚簇索引上面获取数据，或者通过二级索引定位到聚簇索引，接着获取数据；
2. 完全没有命中索引，mysql需要扫描所有数据页（也就是聚簇索引B+树的叶子节点）；

具体而言，就是分析where条件的具体写法，也就是常说的索引失效的情况：

1. 查询过程不符合B+树索引结构：

   1. 条件中有or；
   2. 使用!=，<, > 等范围查询；
   3. 使用is null和is not null；
   4. 全文匹配的like，只有%写在最后面才可以走索引。

2. 在索引列上搞骚操作：

   1. 在索引列上进行计算；
   2. 在索引列上进行隐式转换；
   3. 在索引中使用函数；

不确定的情况下可以使用explain语句，分析索引使用情况，主要看type、keys和extra字段，keys不做赘述。（分析性能可以看rows，即扫描了多少行）

• type字段：

  1. 没有join操作，type可以是all、index、range、const
  2. 有join操作，type可以是eq_ref和ref

• extra字段：当type是index的时候，说明用到了索引，extra可以有3个值：

  1. using where：等同于type=all，where是在取完所有数据之后才过滤；
  2. using index：可以直接在索引树上完成检索，无需访问实际的行数据。

mysql的数据也并非所有情况下都在叶子节点，当数据类型是blob或text且超过page size的一半（通常是8k，默认的page size是16k，这个值可以配置为4k，8k，16k，32k，64k），blob字段的值会被放到其他页，索引页只留下blob的前768个字节。

所谓的回表

默认情况下，mysql会用id作为聚簇索引；在没有id或者id不唯一的情况下，才会使用unique key作为索引。
假设有二级索引且有id作为聚簇索引，mysql会创建两颗B+树，查询二级索引会先去二级索引的B+树的叶子节点找到id，再去聚簇索引查到具体的数据页，就是俗称的回表。

关于join的常规优化小表join大表

原理：join在mysql的实现可以理解为一个双循环，小表join大表减少外层循环的次数，从而减少总的笛卡尔积次数。
通常情况下left join会用左表作为驱动表，所以左表用小表；right join会用右表做驱动表；
例外情况：如果两表之中有一个能用到索引，会用能用到索引那张表做驱动表。