Listening to the Words

学习MySQL之索引( Index)

MySQL的索引其实就是排好序列的数据结构

聚集索引

中文有许多翻译:聚集索引 聚簇索引等等,Cluster含义就是一些东西堆在一起的含义

简单解释聚集索引(Clustered Index):在mysql中指的是索引和数据聚集在叶子节点,这样做的好处是通过所以就可以直接获取数据,简单高效.

mysql 索引(B+ tree)

Myisam引擎索引存储方式

Myisam索引存储并不是聚集索引方式,而是放在了不同的文件里,打个比喻:你使用部首偏旁查索引方式查字典,查到的结果应该是个页码,真实数据放在这个页面里.

《学习MySQL之索引( Index)》

InnoDB引擎索引存储方式

InnoDB引擎主键索引直接和数据放在了一起,它明显更加高效

《学习MySQL之索引( Index)》

为何主键索引非常快?

在InnoDB引擎来说,它会维护一个主键索引,而这个主键索引使用的是聚集索引方式,如果这张表不存在主键的话,它的底层仍然会自动创建一个主键来维持主键查找的高效性

为何主键推荐使用整形自增?

  • 数字索引比大小远远快于字符串之间比大小
  • 省空间(bigint最多占据8Bytes)
  • 自增占用内存空间连续,方便查找(Hash索引最大的缺点)

最左前缀原则的原理?

MySQL的索引其实就是排好序列的数据结构

复合索引的实际存储结构如下图所示,按照字段的优先顺序排序,然后比对

《学习MySQL之索引( Index)》

举例:

  1. name age
  2. age position
  3. position

这三个条件查询元素哪些会触发索引?答案是 1!

因为 条件1符合实际存储顺序,即便position字段没有使用到也没关系

什么是索引回表?

索引回表就是指先读取索引键值,然后,再根据索引信息回到索引基表读取数据行的过程

什么是索引覆盖(Covering index)?

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

如何实现索引覆盖?

常见的方法是:将被查询的字段,建立到联合索引里去

create table user (
    id int primary key,
    name varchar(100),
    sex tinyint(1),
    index(name)
)engine=innodb;

第一个SQL语句:

《学习MySQL之索引( Index)》

select id,name from user where name='masami';

能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。

第二个SQL语句:

《学习MySQL之索引( Index)》

select id,name,sex from user where name='masami';

能够命中name索引,索引叶子节点存储了主键id,但sex字段必须回表查询才能获取到,不符合索引覆盖,需要再次通过id值扫码聚集索引获取sex字段,效率会降低。

ps: Extra:Using index condition。

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create table user (
    id int primary key,
    name varchar(100),
    sex TINYINT(1),
    index(name,sex)
)engine=innodb;

《学习MySQL之索引( Index)》

都能够命中索引覆盖,无需回表。

点赞