Mysql索引底层原理分析

简介

Mysql中Innodb引擎的索引的数据类型为Hash或者B+树，默认为B+树。

叶子节点由双向指针连接

页

页是一种逻辑单位，一页可存储16kb数据。

使用页——局部性原理

局部性通常有两种形式：

时间局部性(temporal locality)

时间局部性指的是：被引用过一次的存储器位置在未来会被多次引用（通常在循环中）。

空间局部性(spatial locality)

如果一个存储器的位置被引用，那么将来他附近的位置也会被引用。

如图：

 索引的结构

聚簇索引

• * 如果表设置了主键，则主键就是聚簇索引
• * 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
• * 以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据），InnoDB必须要有至少一个聚簇索引。

由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录。

 

 

普通索引

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。

InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。

 使用普通索引时，看select后需要查询的字段，再判断是否需要根据普通索引拿到主键id，再进行回表查找。

回表查询

先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

由此引出sql优化：

索引覆盖

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

如何实现覆盖索引

常见的方法是：将被查询的字段，建立到联合索引里去。（联合索引遵从最左前缀原则）

 

补充

 explain执行计划、查询优化器选择方式

mysql中非数字的字符串转化成数字都是0，对字符串进行操作的(类型转换、截取字符串。。。)都不能走索引