对MySQL数据库索引的基本理解-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_36896711/article/details/131979848

在谈索引之前，首先要了解基本的数据库结构。

一、数据库结构

1.数据库的一条记录是由记录的额外信息和记录的真实数据组成的

2.组（数据库里的多条记录（4~8条）形成一个组）

3.页（数据库里的多个组形成一页，1page=16K，每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表）

4.在页里面基本的查找方式：比如查找6，left=槽0，right=槽4，mid=(0+4)/2=槽2，槽2的最大值为8>6，接着以槽2为right，mid=(0+2)/2=槽1，槽1的最大值为4<6，从而锁定位置为槽(1+1)，也就是槽2，在槽2里依次遍历，找到6。简单来说，就是通过二分查找，找到对应的槽号，然后在该槽里面进行线性查找。

5.但是，数据库可不止一个页，页与页之间的连接其实就是一个双链表。

6.那么，一个完整的数据库查找应该是这样的：先逐页遍历，确定页，然后在页内通过二分查找加线性遍历的方式找到最终的数据。但这种逐页遍历的方式是否太慢了？这个时候就需要索引了。

二、索引

1.一个简单的索引方案：以主键为key建立页目录，每个目录项的page_no表明指向的页，这样，如果以主键为条件来查询的话，就可以用二分查找的方式查询目录来定位某一页，而不用逐页遍历了。

2.而MySQL数据库的索引和刚才的简易索引的原理其实差不多，并且进行了改进，他们发现目录项和用户记录差不多，只不过目录项中的两个列是主键和页号而已，所以他们复用了之前存储用户记录的数据页来存储目录项，利用记录头信息里的record_type属性来区分是目录项记录（1）还是普通的用户记录（0），另外还有最小记录（2），最大记录（3），将前面使用的目录项放到数据页就像下面的图一样

3.如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢？那就是为这些存储目录项记录的页再生成一个更高级的目录。

4.这就是指定主键后MySQL自动帮我们创建的索引（本质是B+树），名字叫聚簇索引，叶子节点包含完整的用户记录。当然，我们也可以依据其他列名创建二级索引（多个列名则为联合索引，本质也是二级索引），二级索引的叶子节点仅包含数据的主键，所以查完整用户记录时，查完二级索引获得主键，再进行回表操作，查聚簇索引得到结果。

5.二级索引的缺陷：

（1）因为是以非主键列名建立的索引，所以该列名是可重复的，所以有时候需要跨页查多个，不能够有效的利用缓存的局部性原理，也就是说会有更多的磁盘IO操作。

（2）因为叶子节点存储的是主键，所以要进行回表操作，查询的次数比直接用聚簇索引要多。

三、对sql查询语句是否用到索引的一些测试

1.首先我通过sql语句建表person_info，指定主键id，并建立联合索引

CREATE TABLE person_info(
        id INT NOT NULL auto_increment,
        name VARCHAR(100) NOT NULL,
        birthday DATE NOT NULL,
        phone_number CHAR(11) NOT NULL,
        country varchar(100) NOT NULL,
        PRIMARY KEY (id),
        KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

2.通过java代码向表中插入随机数据