综述
首先需要理解以下概念:
- B+Tree、聚簇索引、二级索引、稀疏索引
- mysql page的结构
其次,总体而言可以将获取数据的类型分为:
- 命中了索引,可以直接从聚簇索引上面获取数据,或者通过二级索引定位到聚簇索引,接着获取数据;
- 完全没有命中索引,mysql需要扫描所有数据页(也就是聚簇索引B+树的叶子节点);
具体而言,就是分析where条件的具体写法,也就是常说的索引失效的情况:
查询过程不符合B+树索引结构:
- 条件中有or;
- 使用!=,<, > 等范围查询;
- 使用is null和is not null;
- 全文匹配的like,只有%写在最后面才可以走索引。
在索引列上搞骚操作:
- 在索引列上进行计算;
- 在索引列上进行隐式转换;
- 在索引中使用函数;
不确定的情况下可以使用explain语句,分析索引使用情况,主要看type、keys和extra字段,keys不做赘述。(分析性能可以看rows,即扫描了多少行)
type字段:
- 没有join操作,type可以是all、index、range、const
- 有join操作,type可以是eq_ref和ref
extra字段:当type是index的时候,说明用到了索引,extra可以有3个值:
- using where:等同于type=all,where是在取完所有数据之后才过滤;
- 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会用右表做驱动表;
例外情况:如果两表之中有一个能用到索引,会用能用到索引那张表做驱动表。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。