【MySQL】索引

前言：

        hi~欢迎大家来到我的mysql笔记系列。本篇记录数据库中索引的相关知识，明白索引是提高海里数据中查找效率的关键，具体从底层实现细节以及操作角度进行学习。

我的前一篇mysql笔记：【MySQL】复合查询_柒海啦的博客-CSDN博客

        革命尚未成功，同志仍需努力！ 

目录

一、mysql和磁盘

磁盘的简单理解

mysql和磁盘的交互单位

mysql存储结构

二、索引的理解

1.海量数据下的无索引查询

2.理解page

-单个page

-多个page

3.页目录

-单个page

-多个page

4.数据结构的对比

5.聚簇索引和非聚簇索引

-多个索引结构、回表查询

6.海量数据下的索引查询

三、索引的相关操作

1.创建主键索引

2.唯一索引的创建

3.普通索引的创建

4.全文索引的创建

-explain工具查看是否使用索引

5.索引的其他操作

-查询索引

-删除索引

-索引的创建原则

---

一、mysql和磁盘

磁盘的简单理解

        当我们对数据库中的数据进行增删查改的时候，数据的存储从何而来？自然从计算机中来。而计算机中的存储外设自然是磁盘。

        磁盘是一个机械设备，相较于其他存储介质，效率低下。mysql数据库操作的数据首先从内存中进行获取。那么如何处理硬盘和mysql之间的关系是一个值得深讨的问题。

         上图就是当前mysql的相关数据保存的对应文件。说明最终mysql相关数据是保存到计算机的磁盘上的。

        对于磁盘，我们首先可以分析一下它的内部结构：

        磁盘中存在很多盘片，一个盘片中划分了很多扇区。目前我们认为一个扇区的大小就为512byte。

        数据库的文件很大，所以自然需要占据很多个扇区进行实际的存储。

        操作系统从磁盘读取数据的时候，为了效率问题，并不是以一个扇区的大小进行读取的，而是是以4kb一块的大小进行读取的。

        mysql和计算机交互数据的时候，看似是mysql->磁盘，实际上是mysql->操作系统->磁盘。

        需要注意，访问磁盘的数据有两种访问方式：磁盘随机访问(Random Access)与连续访问(Sequential Access)。

        磁盘的随机访问就是每次IO的时候，扇区的地址并不连续，导致磁盘的磁头需要较大的移动动作才能进行读写数据。但是连续访问的时候，磁头并不需要移动很久，很快就能进行下一次的读写数据，所以效率相对高一点。

mysql和磁盘的交互单位

        mysql数据库存在大量的IO操作，所以我们设计其存储和磁盘的联系的时候需要更高的IO效率。

        在innodb存储引擎下，mysql进行IO交互的基本单位是16kb。

        这点我们可以在连接mysql的命令行中进行查询：show global status like 'innodb_page_size';(查询全局变量，使用模糊查询)

        查询到的单位为字节，换算下来差不多16kb的样子。在mysql的innodb引擎下，这一次的IO的大小也就是Page的大小（注意这里的page需要和操作系统中的page区分开来）。

        我们需要知道，mysqld本身就是一个进程，是一定在OS上运行的。在mysql中对数据库、表进行操作实际上就是操作文件的内容。那么文件必须被打开，对文件做操作文件是需要在内存上进行换入换出的，并且很多文件加载入内容，mysql需要对其进行管理。

        于是，mysql可以预先申请一部分的空间（buffer_pool预先申请一堆的空间，一般为128mb），而这一部分的空间的又是由很多个小的Page组成起来的。mysql此时存在大量的page，需要进行先描述在组织，即对其结构利用高效的数据结构组织起来。从而达到快速查找修改输出的基本操作。

mysql存储结构

        通过上面的描述，现在对于mysql的存储可以有如下的基本认知：

1.mysql的数据文件，以page为单位保存到磁盘上的。
2.mysql的CURD（DML）、DDL、DML，需要通过计算进行修改数据或者查看。
3.cpu需要计算。
4.特定时间内：磁盘中有，内存中也有，后续特定的策略进行刷新。刷新的单位是mysql page。
5.对page的组织是在mysql buffer pool内的（inoodb - 128mb），来和磁盘数据进行IO交互。
6.更高的效率——减少系统和磁盘IO的次数。（也就是有限于内存操作中-buffer pool）

        那么，对于mysql操作提高效率，就是对page结构组织的体现上。实际上这个组织就是索引，下面我们通过实际的例子一步一步理解索引如何提升效率，以及其所使用的数据结构。

二、索引的理解

1.海量数据下的无索引查询

        首先，我们先在海量数据内不存在索引的情况下查询一个数据，看看效果如何：

--构建一个8000000条记录的数据
--构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do
  set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
  set i = i + 1;
  end while;
 return return_str;
 end $$
delimiter;

--产生随机数字
delimiter $$
create function rand_num()
returns int(5)
begin
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
end $$
delimiter;

--创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 set autocommit = 0;
 repeat
  set i = i + 1;
  insert into EMP values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 commit;
end $$
delimiter;

-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

  

        执行完后，此时发现如果使用select * from EMP的话会加载很久并且刷屏哦~

        此时，我们从EMP表中寻找empno为123456：

        会发现非常的慢，如果还是在并发环境下的话那么效率就十分低下了。所以数据库就需要在内存交互中做出效率优化的方案出来，那么就要从mysql的基本IO单位page说起。

2.理解page

        我们先从现象入手，建立一个测试表，观察一下所谓优化效率的现象：

create table if not exists test_users(
    id int primary key,
    name varchar(10) not null,
    age int not null
)engine InnoDB default charset=utf8;

        插入一堆数据后进行查询：（注意插入的数据id为乱序-特别注意id为primary key-主键约束）

insert into test_users values(3, '李四', 21);
insert into test_users values(1, '王五', 19);
insert into test_users values(4, '张三', 20);
insert into test_users values(2, '赵六', 18);

        但是此时我们对test_users进行一个全体查询会发现一个有意思的现象：

        为什么我插入的时候是按照乱序排序的，但是查询出来确实排好序的呢？这就和primary key主键索引有关，即关于mysql基本存储单位Page的构成相关。 

-单个page

        我们通过主键-建立索引，并且局部性原理理解page的作用。

        局部性原理是什么？这就和一个page大小有关。有没有想过，mysql为何采用page(16kb)作为io的基本单位呢？大一点或者小一点不好吗？

        首先，自然大一点比较好，因为如果上面insert数据都装在一个page下，那么效率不就提升了吗，因为此时此page就会被装载入内存-buffer pool中，所以就可以大大减少了IO的次数（注意效率影响最大的就是IO的频率），另外，因为有局部性原理的存在，我们有很大概率下次找的数据就在此page中。

        在前面我们已经知道了，管理mysql中的page，需要先描述在组织。而上面我们插入的数据如果在单个page下可以有着如下的组织：

        而这样的page可以当作一个结点，将各个结点连接起来可以组成一个双向链表，将page组合起来。

        但是双链表这样也没有解决效率、以及为什么排序的问题啊？别急，听我继续向下说道。

        在我们创建表的时候，我们是用id作为primary key的。所以MySQL内部就自动为其做了排序，我们可以用一组对比实验证明一下：

create table if not exists test_users_2(
    id int not null,
    name varchar(10) not null,
    age int not null
);

        可以注意到，此时我们已经将primary key主键约束去掉了，我们重复之前的插入操作，select *查看一下结果看看是否是primary key的原因。

        可以看到，果然没有排序，显示的结果就是按照我们插入的顺序来的。那么问题来了，primary key主键约束为什么要对其属性排序？

        注意，我们是要优化效率的，在单个page中的数据结构如之前的page图所示，是链接到一个链表上的。链表的特点是什么？不就是容易插入、删除，但是不容易查找修改嘛。如果我们针对某个值对其排序（实际就是对其做索引），就能优化查找效率，并且也为后续工作做准备（定位某个区间）。

        单个page是这样，但是比较16kb的内存数据存不下很多进入数据库的数据，在千万条数据下，page是如何组织并且优化效率的呢？

-多个page

        因为一个page结构就是一个结点，前后连接着结点。如果需要存储很多数据，那么自然需要很多结点，这些结点组合起来就可以形成如下的双链表：

        但是这样的话不仍然是一个链表结构吗？线性的查找效率十分低下。

        如果此时我们针对特定的属性（就是排序的属性-比如现在举例的主键），利用一些手段，来提升效率，从而便就能够提高数据库整体的查找效率了。

3.页目录

        讲解整个提升效率的方法之前，可以引入一个生活中非常常见的例子。

        你可以回想一下（或者当前实验一下），你在找一本书上的某种内容的时候，你是如何去找的？难道是一页一页挨着去翻的吗？自然不是，你是通过前面几页的目录去找的。

        这就是一个典型的空间换取时间的做法。我们利用几页纸的空间，能够为我们的查找效率提升很多的空间（快速确定内容在某个范围内）。在MySQL中的page结构中，是否也能引入这样的结构呢？

-单个page

        单个page下，我们可以引入目录来确定某些值的空间：

        此时，我们如果查找4的话，我们需要遍历4次。但是现在的话，之间根据目录2找到3的位置遍历2次即可。（假设目录的查找效率为O1）

        同时，这也更加证明了为什么排序可以提升效率的说明，目的就是为了更好的构建页目录（页码是不可乱序的），从而实现快速定位。

-多个page

        多个page下，因为存在页目录，所以在连接page的双链表中每个结点增加目录即可。如下图：

        但是，如果只存在单个page下的目录话，一旦page多起来，查找每个page的目录的时候同样是线性查找，所以还是会不断的进行IO(IO一次page)，显得有点杯水车薪了。为了提升效率，我们可以单独开一张page专门用于存储页码，从而快速定位这些存在数据的page。

        我们可以以page中的首个页码作为此页的页码，上面的page就可以进行快速定位。

        这种page被称为目录页，本身不存储数据，只是负责维护page的目录结构。

        并且，如果此目录页也多起来，利用一样的机制，继续向上搭建。这样越往上走目录页越少，范围也越大。-这样就是一个树形结构了。这也，在每次针对此属性（当作页码的属性）查找时，会有效的减少单个pageIO次数，从而达到提升效率的效果。

        明确目的，因为MySQL每次和数据交互（硬盘）以page(16kb)为单位进行交互的，那么交互的次数越少，效率越高。而上面这个结构正是实现了这样的功能。实际上，上面组成的树形结构，就是一颗B+树。此树的叶子节点全是由双链表连接起来的。在MySQL中最多也搭建三层（如图）。

总结一下。

        在由page构建的B+树中，分为数据页和目录页。目录页中存放着单个page中的最小键值。

        查找的过程中自顶向下，只需要加载部分的目录页和数据页完成整个算法的查找过程，大大减少了IO的次数，从而效率提升。

4.数据结构的对比

        在InnoDB的存储模型中，MySQL采用的是B+树建立索引（根据指定的属性，上述例子是主键）。那么为什么采用B+树呢？

        1.首先对比链表这个数据结构，因为是线性遍历，效率自然没有任何说法，淘汰。

        2.二叉搜索树呢？首先因为其无法保持平衡状态，可能退化为线性遍历，IO次数变多，不合适。

        3.AVL？红黑树？虽然可以保持平衡状态，但是他们是二叉树，对于B+的多叉树结构，层数越高，IO次数自然越多，因此淘汰。

        4.Hash？hash在MySQL中支持（存储模型：MEMORY/HEAP、NDB），但是在InnoDB和MyISAM存储模型中并不支持，因为虽然可以接近O1的查找到对应page，但是在面对连续查找的时候比较麻烦。

        最后，B+树在和B树进行比较一下。

可以在网上查找相关图或者学习相关数据结构，这里只是区别不同之处并且找到差异：

1.B树每个结点都存在数据。B+只是叶子结点存在数据。这样的话B+的页目录就可以存储更多的目录key，从而减少树的高度减少IO的次数。

2.B+的叶子结点使用链表相连，B树没有，B+适合范围查找。

5.聚簇索引和非聚簇索引

        同样以B+树为实现索引的数据结构的另外一个存储引擎MyISAM，却和InnoDB存在一定的区别，我们可以从如下的MyISAM的B+树中看出区别：

        可以看到，此B+树在最后一层的数据页中并没有存放实际的数据，而是存放数据的地址，是将数据和其的索引进行了一个分离。实际上，这样的索引我们称之为非聚簇索引，而之前的索引为聚簇索引。

聚簇索引：
    表中所有带有键（主键）的数据，都是以B+的形式呈现的，其中B+树和数据是耦合在一起的。-InnoDB存储引擎
非聚簇索引：
    但是B+索引结构和数据分开，并没有放在一起的。 -MyISAM。（目录页和数据页进行分开。目录页组成B+）

        建立的这两个索引，我们也可以从物理结构上看出区别：首先创建一个类似的表test,只不过采用的存储引擎为MyISAM。

create table if not exists test_users_mi(
    id int primary key,
    name varchar(10) not null,
    age int not null
)engine=MyISAM;

        在/var/lib/mysql/数据库名字文件下，就可以看到两份表的存储文件：

        对于InnoDB存储引擎（聚簇索引），.frm后缀表示表结构,而.ibd则是数据和索引放在一起的。对于MyISAM存储引擎（非聚簇索引），.frm表示表结构，.MYD是用来存放数据的，而.MYI是用来存放索引的。 

-多个索引结构、回表查询

        首先，我们需要知道一张表不仅仅只有一个索引结构。

        当我们建立表结构的时候，如果有主键，mysqld会为我们构建主键索引。（聚簇、非聚簇）需要注意，任意一列都可以建立索引。（实际上如果没有主键，mysql会自动生成隐藏主键，因为用户看不见，自然无法使用其作为索引来进行使用）

        但是，构建索引要求尽量不要重复值。并且需要频繁使用，如果不使用或者使用频率少那么就是浪费空间（白浪费空间建立索引）。

        在构建多个索引-B+树的时候，MyISAM 重新创建索引结构即可。叶子保存数据记录的地址即可。

        但是innoDB数据是在叶子节点的，不可重复的复制数据-数据冗余，浪费空间。所以innoDB会根据普通索引建立一个非聚簇索引，只不过叶子节点保存的主键ID。-然后通过主键回表主键索引在进行一次查找！回表行为。
        那么是不是每次都要回表呢？ 不一定，找的数据如果就是主键ID，那么会优化为从主键索引下进行寻找。

6.海量数据下的索引查询

        我们知道了索引的构建是提高mysql查找数据的效率的。那么我们使用可以利用构建索引的操作（不是主键索引，后续会讲到），提升我们找empno=123456的效率呢？我们不妨对empno这个属性建立索引。

alter table EMP add index(empno);

        对empno构建索引，我们再次对其进行查找：

        可以发现，建立了索引后，和上一次的4s查询效率提升的太快了，这也正式我们研究索引的意义所在。

三、索引的相关操作

        前面我们具体理解了索引的建立以及其底层原理。可以发现。先前针对于主键，创建出来的称之为主键索引。是不是根据此依赖属性，能够创建不同的索引呢？

1.创建主键索引

        创建主键索引实际上就是在创建表的过程中增加primary key约束即可，如果创教表的时候没有主键约束，可以后续使用alter进行修改添加。

create table test(
    id int -- 1.primary key,
    -- 2 primary key(id)
);

-- 3 alter table test add primary key(id);  -- 添加主键索引 

        需要注意主键索引的特点：

1.一个表中，最多只有一个主键索引；

2.主键索引的效率高（不可重复、不可为null）；

3.主键索引的列基本都是int；

2.唯一索引的创建

        唯一索引的创建，实际上就是给属性加上唯一键约束。注意唯一键约束保证其属性中每一个元素的唯一，但是其可以为null(和主键的区别)。要么创建表增加唯一键约束即可，否则就是alter添加唯一键约束即可创建唯一索引。

create table test(
    id int -- 1.unique
    -- 2.unqiue(id)
);

-- 3.alter table test add unique(id);

        唯一索引的特点：

1.一个表中，可以有多个唯一索引；

2.查询效率高；

3.如果在某一列建立唯一索引，保证此列数据不可重复；

4.如果在唯一索引上指定not null，等价于主键索引；

3.普通索引的创建

        普通索引的创建正如其名字那样，就是普通的没有任何约束，对其属性创建索引（即使其存在重复）。可以在创建表的时候利用index()进行定义，也可以alter进行添加index，甚至可以为表创建普通索引（指定表和属性）。

create table test(
    id int,
    -- 1.index(id)
);

-- 2.alter table test add index(id);

-- 3.create index index_name on test(id);  --创建索引名为index_name的普通索引

        注意普通索引的特点：

1.一个表中可以存在多个普通索引，普通索引在实际开发中使用的比较多；

2.如果此属性需要创建索引，但是该列存在重复的值，我们可以使用普通索引进行创建；

4.全文索引的创建

        MySQL提供全文索引，但是其存引擎是MyISAM。并且只对英文有效（如果对中文，使用sphinx的中文版(coreseek)）。全文索引对文章大量字段进行检索的时候会使用到。

        以下面这个例子理解全文索引的创建和使用：

create table text_users(
    id int auto_increment primary key,
    time timestamp,
    title varchar(20),
    body text,
    fulltext(title, body)  -- 创建全文索引，关联title
)engine=MyISAM;

        创建了表text_users后，我们给他添加几行信息：

insert into text_users(title, body) values
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

        我们查询body中是否存在database数据，显示出来：

         观察其是否使用了索引：

-explain工具查看是否使用索引

        只需要在select 前面加上一个explain显示即可。注意如果不按表格式打印后面加\G不带;哦~

        注意其key，因为其为null说明没有利用全文索引进行查找（也可以看type）。下面我们利用全文索引去查找。

select * from text_users match(title, body) against('database');

        同样的，我们使用explain工具检查一下：

        此时就使用了全文索引进行查找。

5.索引的其他操作

-查询索引

        对于我们的一张表，我们能否查找其存在哪些索引呢？可以的，并且存在多种方法：

show keys from table_name\G

show index from table_name;

desc table;  -- 信息比较简陋

-删除索引

        删除索引比如主键、唯一键实际上就是取消约束即删除了索引。主键有自己的删除方法，唯一键和普通索引可以使用同一种方法进行删除。

1.删除主键索引

drop table table_name primary key;

2.删除唯一索引或者普通索引

drop table table_name index name;
--  drop index on tablename;

        需要注意，索引名是使用show keys from table_name中的key_name字段：

-索引的创建原则

比较频繁作为查询条件的字段应该创建索引；
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
更新非常频繁的字段不适合作创建索引；
不会出现在where子句中的字段不该创建索引；

        需要注意，索引本身就是牺牲空间换取时间的做法，所以牺牲空间的价值需要远远小于我们的实际需求的价值。