索引基础知识

索引基础知识

磁盘预读（预读一般为页（page）的整数倍）

页是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续大小相等的块。每个块称之为一页（多数操作系统中，一页的大小为4k），主存与磁盘以页为单位进行交换。

索引优点

1、大大减少了服务器需要扫描的数据量
2、帮助服务器避免排序和临时表
3、将随机io变成顺序io

索引用处

1、快速查找匹配WHERE子句的行
2、从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
3、如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
4、当有表连接的时候，从其他表检索行数据
5、查找特定索引列的min或max值
6、如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组
7、在某些情况下，可以优化查询以检索值而无需查询数据行

索引分类

主键索引
唯一索引
普通索引
全文索引
组合索引

面试技术名词

回表

针对于innoDB引擎来说，依据辅助索引搜索数据时，先查询辅助索引B+树，再根据叶节点数据（主键id），去查询主键索引B+树时，触发了我回表。
注：至于触发回表后，执行效率的快慢问题。数据量少的时候-慢；数据量大的时候-快；

覆盖索引（详见覆盖索引）

如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引。
简单说：InnoDB引擎中，只查一次辅助索引，所需要的数据都获取到了，就叫做覆盖索引

最左匹配

如：组合索引（name, age）
where name =? and age =? -- 会走索引
where name = ? -- 会走索引
where age = ? -- 不会走索引
where age = ? and name = ? -- 会走索引 内部有优化器

索引下推

select * from table where name = ? and age = ?
数据存储在磁盘
mysql有自己的服务
mysql服务要跟磁盘发生交互

• 没有索引下推：
  先从存储引擎拉取数据（根据name 筛选的数据）再mysql server 根据age进行筛选数据
• 有索引下推：
  会在拉取数据的时候直接根据name,age来获取数据，不需要server做任务的筛选

索引丢失

索引丢失指的是存在没有被使用的索引，这样维护起来的索引是无效的，造成了性能浪费，开发中需要尽量避免索引丢失的情况。而造成索引丢失的原因大概包括：

• 被索引字段发生了隐式类型转换
• 被索引字段使用了表达式计算
• 被索引字段使用了函数
• 被索引字段不是联合索引地最左字段
• like关键字前使用了左模糊匹配或者左右模糊匹配

索引采用的数据结构

哈希 - Memory 引擎
B+树 - Innodb 和 MyISAM

索引匹配方式

全值匹配

全值匹配指的是和索引中的所有列进行匹配
如：explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';

匹配最左前缀

只匹配前面的几列
如：explain select * from staffs where name = 'July' and age = '23';

匹配列前缀

可以匹配某一列的值的开头部分
如：explain select * from staffs where name like 'J%';

匹配范围值

可以查找某一个范围的数据
如：explain select * from staffs where name > 'Mary';

精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分
如：explain select * from staffs where name = 'July' and age > 25;

只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
如：explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';