添加索引
show index from table_name
,查看table_name这个表有几个索引。
添加索引操作alter table tableName add index(列名)
当我们创建数据库指定主键和唯一键时,MySQL会自动构建B+树,创建索引,如果要创建普通索引,可以使用index(列名)
方式来指定。
科普知识
科普知识
数据库服务端本质上在操作系统中也是一个进程,数据库中的数据也是会存放在磁盘上的。之前学文件系统时,我们知道磁盘和内存是块和块的IO交互,基本单位是4kb(如果不懂为什么是基本交互是4kb可以查看我之前的文件系统)。而MySQL交互的基本单位是16kb,但是MySQL作为一个进程不能越过操作系统直接和硬件进行交互,16kb也是从MySQL视角来看的,本质上还是MySQL让操作系统读取了4*4kb。
数据库为什么要用16kb作为和磁盘的基本单位?为了更高的效率,要尽可能的减少系统和磁盘的IO次数,所以利用局部性原理去优化。
MySQL的curd操作都需要进行计算位置,涉及到计算都需要CPU,所以需要把部分数据加载到内存中,MySQL会向操作系统申请大块内存空间(buffer Pool),用来缓存数据。
索引是什么?为什么要有索引?
我们日常使用读书、查阅资料时,有时候会带着目的去查找,也就是我们提前知道我们想要查看的东西是什么,这个时候就需要精确的定位到固定的章节甚至页数,而这时我们需要目录来帮我们完成这些操作。目录就是这个书的索引。MySQL数据库同样有索引,同样索引的目的是为了帮我们更快的查找数据。这就是索引的作用。
而数据库增加索引不是没有代价的,索引增加了增删改的成本、增加了内存的开销、增加了维护数据的成本。但是会让查询速度变得很快,所以使用的时候需要使用者权衡利弊。
索引的原理
我们初步了解了索引,但是数据库的索引怎么实现的?
我给大家介绍的是innodedb存储引擎的默认索引,所以其他存储引擎或者数据库可能会有不同。
上面介绍了MySQL开辟的buffer Pool中,数据的交互是以page(16kb)为单位进行IO交互和管理的。一个page中的数据需要管理就用到了目录去进行查找。一个page页中会占用一些空间对数据构建目录。
这样就能根据目录快速的定位到page页中的数据,但是如果有很多的数据,就需要很多的page页进行存储数据,页和页之间会用prev和next指针连接起来。
如果页多了我们查找还是很费劲,查找数据就需要先定位到某一页,所以就对这些存储数据的page页再进行管理,用一个page来存放这些数据page第一个数据的地址作为索引。
同理,如果存放目录的page页再增多,我们再增加page页进行管理,但是这种情况比较少见,因为一个一个page页可以存放16kb的数据,一个page页就能管理上千个page页的起始位置。这样就形成了一个树状结构,这样的树是一个“矮胖形状的树”,这样的树状结构称为B+树。
B+树特点:数据都存放在叶子节点,叶子节点之间都是相连的,主要目的是为了顺序访问。
聚簇索引和非聚簇索引
上面介绍了索引的原理,其实上面是以innodedb存储引擎为样例介绍的索引,这种把数据都存放在叶子节点的索引叫做聚簇索引。
那么非聚簇索引是什么?非聚簇索引其实叶子节点存储的不是数据,而是数据的地址,例如myisam存储引擎默认就是用的非聚簇索引。将索引page和数据page分离,这就是非聚簇索引。
一个表中通常只能有一个聚簇索引,这个索引列通常是主键列,可以有多个非聚簇索引,每一个索引就是一颗B+树,给某一列添加索引就是构建一颗B+树。
这就是非聚簇索引
回表查询
这个就是普通索引,普通索引就是叶子节点存放的被设置为普通索引列的地址,通过这个地址然后找到普通索引列的值,再通过普通索引和主键索引列值的映射关系,找到对应的数据。主键添加了主键索引,然后再给其他列添加了普通(非聚簇)索引,那么通过普通索引查找到被设定为普通索引那一列的值,找到主键,再通过主键索引查找数据,索引两遍,叫做回表查询。
这就是MySQL的索引,希望对您有所帮助。