【MySQL数据库】：MySQL索引特性

目录

索引的概念

磁盘

磁盘的基本特征

MySQL与磁盘交互的基本单位

索引的理解

建立测试表

理解单个Page

理解多个Page  

页目录

单页情况

多页情况

索引的数据结构

聚簇索引 VS 非聚簇索引

索引操作

创建主键索引

 创建唯一索引

创建普通索引

 创建全文索引

查询索引

 删除索引

索引创建原则

---

索引的概念

• 索引提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。
• 但是天下没有免费的午餐，查询速度的提高 是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。
• 所以它的价值，在于提高一个海量数据的检索速度。

常见的索引分为：

• 主键索引（primary key）
• 唯一索引（unique）
• 普通索引（index）
• 全文索引（fulltext）

我们可以说主键（primary key）和唯一键（unique）是索引的具体应用形式，它们在实现数据的特定约束和提高查询性能方面发挥着重要作用，而索引为它们以及其他查询操作提供了基础支撑。比如，在通过主键或唯一键查找数据时，就是利用了它们所对应的索引来快速定位。同时，也可以根据具体需求创建其他类型的索引来进一步优化查询性能。

案例实现

使用如下SQL创建一个海量数据表：

drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8;
use `index_demon`;

-- 构建一个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 ;

-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);

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

 上述SQL中创建了一个名为index_demon的数据库，在该数据库中创建了一个名为EMP的员工表，并向表当中插入了八百万条记录。

将上述SQL保存到文件 index.sql 中，然后在MySQL中使用source命令依次执行文件中的SQL即可。

 SQL执行完毕后查看数据库就能看到一个名为index_demon的数据库，并且数据库中可以看到一个名为EMP的员工表。

由于EMP表中有八百万条记录，因此在查看EMP表中的数据时可以带上limit子句。并且目前EMP员工表中没有建立任何索引。 

查询EMP表中指定工号的员工信息，可以看到每次查询过程都需要花费1.63秒左右。 

当我们给员工表中的工号建立索引后，数据库底层就会为员工表中的数据记录构建特定的数据结构，需要注意的是，由于当前员工表中的数据量较大，因此建立索引时也需要花费较长时间。

这时再查询EMP表中指定工号的员工信息，可以看到几乎检测不到查询时耗费的时间。 

根本原因就是，给员工工号创建索引后再根据员工工号来查询数据，这时就能够直接通过底层建立的数据结构来快速定位到目标数据，从而提高数据的检索速度，这就是索引的价值。

磁盘

磁盘的基本特征

磁盘是一种存储设备，主要用于计算机系统中数据的存储。

它通常由磁性材料制成的盘片组成，通过磁头在盘片上读写数据。磁盘可以分为硬盘和软盘等类型，具有较大的存储容量，可以长期保存数据。

• 一个磁盘有很多盘片！！
• 一个盘片有很多的同心磁道！！
• 一圈磁道有很多的扇形的扇区 ！！
• 扇区是磁盘的最小的存储单元 ------- 512字节（0.5kb）

如果我想向一个扇区进行写入，我应该如何去寻址！！！

• 柱面号：确定扇区所在的柱面位置。
• 磁头号（哪一盘片）：确定读写头所在的磁头位置。
• 扇区号：确定具体要写入的扇区编号。

通过以上三个步骤，最终确定信息在磁盘的读写位置。柱面号（Cylinder）、磁头号（Head）和扇区号（Sector），简称为CHS定位法。

操作系统与磁盘交互的基本单位

操作系统与磁盘进行IO交互的基本单位是4KB，而不是扇区的大小512字节，原因如下：

• 物理内存实际是被划分成一个个4KB大小的页框的，磁盘上的数据也会被划分成一个个4KB大小的页帧，因此操作系统与磁盘以4KB为单位进行IO交互，就能提高数据加载和保存的效率。
• 操作系统与磁盘进行IO交互时，如果直接以扇区的大小作为IO的基本单位，那么这时系统的IO代码和硬件就是强相关的，将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码。
• 此外，以扇区的大小作为IO的基本单位太小了，这就意味着读取同样的数据内容，需要进行更多次的磁盘访问，而磁盘的效率是比较低的，这样IO效率就降低了。

因此操作系统与磁盘以4KB作为IO交互的基本单位，一方面是为了提高IO效率，另一方面是为了实现硬件和系统的解耦。

磁盘的随机访问（Random Access）与连续访问（Sequential Access）

• 随机访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址不连续，磁头在两次IO操作之间需要做比较大的移动动作才能找到目标扇区进行IO。
• 连续访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的，磁头很快就能找到目标扇区进行IO。

需要注意的是，尽管两次IO是在同一时刻发出的，但如果它们请求的扇区地址相差很大，那也只能称为随机访问，因为连续访问中的连续指的是访问的扇区地址的连续，而不是访问时间的连续，由于连续访问不需要过多的定位，因此效率比较高。

MySQL与磁盘交互的基本单位

MySQL作为一款应用软件，可以想象成是一种特殊的文件系统，它有着更高频的IO场景，因此为了提高基本的IO效率，MySQL与磁盘交互的基本单位是16KB，这个基本数据单元在MySQL这里也叫做Page。

通过show命令查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是16KB。

 SHOW GLOBAL STATUS LIKE 'innodb_page_size';

建立共识

建立测试表

create table if not exists user (
id int primary key, --一定要添加主键哦，只有这样才会默认生成主键索引
age int not null,
name varchar(16) not null
);

向表中插入一些数据，并且插入数据时没有按照主键的大小顺序插入。

 最终当我们查看表中的数据时，却发现显示出来的数据是按照主键进行有序排列的。

本质：因为我们创建表时设置了主键，即便向表中插入数据时是乱序插入的，MySQL底层也会自动按照主键对插入的数据进行排序。 

为什么MySQL与磁盘交互的基本单位是Page

• 当我们查询表中的某一条记录时，如果MySQL只从磁盘中将这一条记录加载到内存当中，那么当我们继续查询表中的其他记录时，MySQL就一定需要再次与磁盘进行IO交互。
• 而如果当我们查询表中的某一条记录时，MySQL直接将这条记录所在的整个Page都加载到内存当中，那么当我们继续查询表中的其他记录时，MySQL很可能就不再需要与磁盘进行IO交互了，因为这条记录很可能也在被加载进来的Page当中，这时直接在内存中进行查询即可，大大减少了IO的次数。
• 当然，我们不能保证用户下一次要访问的数据一定就在本次加载进来的Page当中，但是根据统计学原理，当一个数据正在被访问时，那么下一次有很大可能会访问其周围的数据（局部性原理），因此我们有较大概率保证用户下一次要访问的数据和本次访问的数据在同一个Page当中，如果局部性原理没有起作用，那就再把对应的Page加载到内存当中即可。

本质：MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率。

理解单个Page

• MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，在组织 ,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的

 

注意： 

• 每个Page结构体内部的数据会按照主键进行排序，目的是为了优化数据查询的效率，因为单链表在查找的时候是顺序查找的，有序就意味着在查找的过程中有机会提前结束查询过程。
• 只要设置了主键，即便向表中插入的数据是乱序的，MySQL底层也会自动按照主键对插入的数据进行排序，因此查询得到的数据是按照主键进行有序排序的。 

为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？

理解多个Page  

• 通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
• 如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这 效率也太低了。

主键索引的特点

• 一个表中，最多有一个主键索引，当然可以使符合主键。
• 主键索引的效率高（主键不可重复）。
• 创建主键索引的列，它的值不能为null，且不能重复。
• 主键索引的列基本上是int。

 创建唯一索引

 方式一：在创建表的时候，直接在字段名后指定 unique

方式二：在创建表的最后，指定某列或某几列为唯一索引

方式三：创建表以后再添加唯一键

唯一索引的特点

• 一个表中，可以有多个唯一索引，一个唯一键可以由多个列同时承担。
• 唯一索引的查询效率高。
• 创建唯一索引的列，其列值可以为NULL，但是不能重复。
• 如果给唯一索引设置NOT NULL属性，则等价于主键索引。

创建普通索引

方式一：在创建表的最后，指定某列或某几列为普通索引。

方式二：创建表后，使用 alter 命令给指定字段添加普通索引。

方式三：创建表后，使用create命令给指定字段创建普通索引，并指定索引名。

普通索引的特点

• 一个表中，可以有多个普通索引，一个普通索引可以由多个列同时承担。
• 创建普通索引的列，其列值可以为NULL，也可以重复。

 创建全文索引

• 当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。
• MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。
• 如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)

全文索引的案例 

全文索引比较常见的案例就是对文章中的词进行搜索，比如下面创建一个文章表，表当中包含文章的 id、文章名称、文章内容等字段，并在创建表的最后通过 fulltext 给 title 和 body 字段创建全文索引。

我们向articles表中插入一些数据！！！

如果要查询哪些文章中包含database关键字，我们可以通过模糊匹配进行查找。

虽然查询出数据，但是没有使用到全文索引

select * from articles where body like '%database%';

 可以用 explain 工具看一下，是否使用到索引 ，key 为 null 表示没有用到索引 ！！

 explain select * from articles where body like '%database%'\G

如何使用全文索引呢？ 

 SELECT * FROM articles where match (title,body) against ('database');

通过explain来分析这个sql语句，key用到了title！！！

注意： 

• MyISAM存储引擎是支持全文索引的，而InnoDB存储引擎是在5.6以后才开始支持全文索引的。
• 同时使用title和body建立全文索引时，相当于建立了一个复合索引，默认会选择fulltext中的第一个列名作为这个复合索引的索引名，所以这里explain中key对应的索引名为title。
• 由于是title和body共同建立的全文索引，所以如果文章当中没有出现关键字，但文章名称中出现了关键字则也会被筛选出来（当前示例没有体现出来）。

查询索引

方式一：使用show keys from 表名SQL查询，比如查询articles表中的索引信息。

Table	创建索引的表的名称
Non_unique	该索引是否是唯一索引，如果是则为0，如果不是则为1
Key_name	索引的名称
Seq_in_indexSeq_in_index	该列在索引中的位置，如果索引是单列的，则该列的值为1，如果索引是复合索引，则该列的值为每列在索引定义中的顺序。
Column_name	定义索引的列字段
Collation	列以何种顺序存储在索引中，“A”表示升序，NULL表示无分类。
Cardinality	索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part	列中被编入索引的字符的数量，若列只是部分被编入索引，则该列的值为被编入索引的字符的数目，若整列被编入索引，则该列的值为NULL。
Packed	指示关键字如何被压缩。若没有被压缩，则值为NULL
Null	用于显示索引列中是否包含NULL，若包含则为YES，若不包含则为NO
Index_type	显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）
Comment	显示评注

方式二：使用show index from 表名SQL查询，比如查询articles表中的索引信息。

方式三：使用desc 表名SQL查询（信息比较简略）。

 删除索引

删除主键索引

alter table 表名 drop primary key

 

删除非主键索引 

alter table 表名 drop index 索引名
drop index 索引名 on 表名

索引创建原则

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

注意：时刻要记住，创建索引的目的就是为了提高查询的效率。