myisam索引实现方式

MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。

MyISAM的索引与行记录是分开存储的,叫做 非聚集索引(UnClustered Index)。

其主键索引与普通索引没有本质差异:

有连续聚集的区域单独存储行记录,主键索引的叶子节点,存储主键,与对应行记录的指针,普通索引的叶子结点,存储索引列,与对应行记录的指针;

举个例子:有用户表如下:id为主键,name字段为普通索引

id name desc text
1 sj m A
5 ls m A
9 ww f B

主键索引与普通索引是两棵独立的索引B+树,通过索引列查找时,先定位到B+树的叶子节点,再通过指针定位到行记录。

行记录单独存储,id为PK,有一棵id的索引树,叶子指向行记录,name为KEY,有一棵name的索引树,叶子也指向行记录。

image-20210202000750022

innodb索引实现方式

InnoDB的主键索引与行记录是存储在一起的,故叫做聚集索引(Clustered Index):

没有单独区域存储行记录,主键索引的叶子节点,存储主键,与对应行记录(而不是指针)

因为这个特性,InnoDB的表必须要有聚集索引:
(1) 如果表定义了PK,则PK就是聚集索引;
(2) 如果表没有定义PK,则第一个非空unique列是聚集索引;
(3) 否则,InnoDB会创建一个隐藏的row-id作为聚集索引;
聚集索引,也只能够有一个,因为数据行在物理磁盘上只能有一份聚集存储。

聚集索引,也只能够有一个,因为数据行在物理磁盘上只能有一份聚集存储。

InnoDB的普通索引可以有多个,它与聚集索引是不同的**:普通索引的叶子节点,存储主键(也不是指针)**

对于InnoDB表,这里的启示是:

(1)不建议使用较长的列做主键,例如char(64),因为所有的普通索引都会存储主键,会导致普通索引过于庞大;
(2)建议使用趋势递增的key做主键,由于数据行与索引一体,这样不至于插入记录时,有大量索引分裂,行记录移动;

先通过name辅助索引定位到B+树的叶子节点得到 id=xxx,再通过聚集索引定位到行记录。

image-20210202000944831

InnoDB 回表问题

如粉红色路径,需要扫码两遍索引树:

(1)先通过普通索引定位到主键值id=5;

(2)在通过聚集索引定位到行记录;

这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。

什么是覆盖索引?

mysql官网表达意思:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

概念:索引覆盖:通过普通索引查询的时候,不需要回表查询,直接可以获取到对应的数据

image-20210202001404268

该怎么实现覆盖索引,提高效率?

  • 创建联合索引(name,sex) 可以避免回表查询提高效率

  • 索引的使用跟查询的and的前后顺序没有关系:

  • 创建(name,sex,flag)索引,以下这两个查询都走索引

select name,id,sex from t where name = “lisi” and sex = “m”;

select name,id,sex from t where sex = “m” and name = “lisi” ;
  • 以下查询不走索引:
select name,id,sex from t where sex = “m”; 

建了一个索引idx(A, B, C), 说的是要使用A, AB, ABC这样的顺序查询,

而使用B, BC, 这样是使用不到索引的

聚集索引和非聚集索引

聚集索引(聚集索引也叫聚簇索引)

1,如果一个主键被定义了,那么这个主键就是作为聚集索引
2,如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引
3,如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增

非聚集索引

因为mysiam的存储两个文件是分开的,索引文件存储的是文件的地址,并不需要通过 (pk主键)找到数据,所以我们说mysql是非聚集索引