Explain执行计划
Explain Type:
const > eq_reg > ref > range > index > ALL
const:当确定最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一次,因此非常快。
当主键放入where子句时,mysql把这个查询转为一个常量(高效)
eq_ref:最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 (高效)
ref:一种索引访问,它返回所有匹配某个单个值的行,此类索引访问只有当使用非唯一性索引。
这个类型跟eq_ref不同的是,它用在关联操作只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
range:范围扫描,一个有限制的索引扫描。key 列显示使用了哪个索引。
当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用range
index:和全表扫描一样,只是扫描表的时候按照索引次序进行而不是行,主要优点就是避免了排序。
ALL:全表扫描
索引数据结构
1.hash索引
定位数据只需要一次查找,O(1),InnoDB会监控对表上各个索引页的查询,如果观察到建立hash索引可以带来速度提升,则建立hash索引
即InnoDB会自动的根据访问频率和模式来自动的为某些热点页建立hash索引
前提是确定的查询条件(where a=xxx),范围查询不适用(where a > xxx)。
2.B+树索引
B+ 树索引是一种自平衡的树结构,其节点分为内部节点和叶子节点:
内部节点(Internal Nodes):用于索引导航,存储键值和指向子节点的指针。
叶子节点(Leaf Nodes):存储实际的数据或指向数据记录的指针。
在 B+ 树中,所有的数据记录都存储在叶子节点中,而内部节点仅用于存储键值和导航信息。
树的高度一般为2-4层,需要2-4次查询(100w和1000w行数据,如果B+tree都是3层,那么查询效率是一样的)
B+树索引能查到的是数据行所在的页。
B+树
首先B+树是B树的一种扩展,在B+树里面,非叶子节点不再存储数据,仅仅存在索引,而叶子这点存储具体的数据,并且最底层的数据直接之间从左到右是按照从小到大的顺序分布,并且是一个双链表的结构。
索引分类
聚簇索引:即主键索引
叶子节点存放的是行记录数据所在的页,而页中的每一行都是完整的行,针对范围查询也比较快。辅助索引(非聚簇索引)
叶子节点存放的也是行记录数据所在的页,但还是页中存放的不是完整的行,而是仅仅是一对key-value和一个指针,该指针指向相应行数据的聚集索引的主键。
假设辅助索引树高3层,聚集索引树为3层,那么根据辅助索引查找数据,需要先经过3次IO找到主键,再经过3次IO找到行做在的数据页。
针对辅助索引的插入和更新操作:辅助索引页如果在缓冲池中,则插入;若不在,则放到InsertBuffer对象中,之后在以一定的频率进行InsertBuffer和辅助索引页子节点的合并
3.联合索引(多列索引)
左边匹配原则(如果索引为(a,b),则where a=x可以用到索引,但是b=x用不到,如果是覆盖索引有可能会用到)
建索引的几大原则
- 最左前缀匹配原则,非常重要的原则。
- =和in可以乱序
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。 - 尽量选择区分度高的列作为索引。
区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0。 - 索引列不能参与计算,保持列“干净”。
- 尽量的扩展索引,不要新建索引。
比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
不能命中索引的操作:
1.“列类型”与“where值类型”不符,不能命中索引,会导致全表扫描(full table scan)。
2.相join的两个表的字符编码不同,不能命中索引,会导致笛卡尔积的循环计算(nested loop)。