[MySQL]MySQL索引

1. 索引的概念

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

• MySQL的服务器本质是在内存中的，因此MySQL的CRUD操作也在内存当中，索引也是如此。
• 索引提高效率的方式：对组织数据的方式进行重构。

常见索引分为：

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)–解决中子文索引问题。

示例： 先整一个海量表，在查询的时候，看看没有索引时有什么问题？

使用如下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)begindeclare chars_str varchar(100) default'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255) default '';declare i int default 0;while i < n doset 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)begindeclare 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))begindeclare i int default 0;set autocommit = 0;repeatset i = i + 1;insert into EMP values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());until i = max_numend 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语句导入到一个本地的文本文件中，然后在Linux系统中使用rz命令将该文件导入：

然后在MySQL中使用source执行导入的SQL语句文件：

由于服务器配置的限制，数据量太大可能会导致MySQL卡住，需要耐心等待：

最后执行完成，用时大概7分钟11.79秒：

完成后，会新增一个index_demon数据库：

数据库中有一个EMP表：

查看表结构：

由于表中数据量很大，我们只查看一下前5行数据：

经过多次测试发现正常查询某条记录大概需要4.2秒左右：

然后对表添加索引，花费了大概20.88秒：

有了索引之后，查找的效率大大提高了：

2. 认识磁盘

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。

磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，要想更好的理解MySQL的效率问题，我们必须要了解磁盘的结构和工作原理。

磁盘的内部结构

• 磁盘 – 由很多盘片组成，用于存储数据。
• 主轴 – 能够将磁盘进行高速旋转。
• 磁头 – 磁盘的每个盘片都有一个对应的磁头，用于读取对应盘片中数据，不与盘面接触。

磁盘中的一个盘片结构

磁盘由多个盘片组成，盘片具有磁性，使用磁性特点来记录对应的二进制数据。

• 磁道： 磁盘表面被分为许多同心圆，每个同心圆称为一个磁道，每个磁道都有一个编号，最外面的是0磁道。
• 扇区： 每个磁道被划分成若干个扇区，每个扇区的存储容量为512字节，每个扇区都有一个编号。

我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的，所以，最基本的，找到一个文件的全部，本质，就是在磁盘找到所有保存文件的扇区，而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

MySQL中的数据库和表也是通过文件形式存储在硬盘中的：

因此知道如何在磁盘中如何定位扇区，找到对应的文件，才能知道MySQL中的数据如何找到。

定位扇区

定位扇区的步骤：

• 定位数据所在的是哪一个盘面 – 也就是选择哪一个对应的磁头。
• 定位数据在所在盘面的哪一个的磁道。
• 定位数据在所在磁道中的哪一个扇区。

磁头（Heads）、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS 。不过实际系统软件使用 的并不是 CHS （但是硬件是），而是 LBA ，一种线性地址，可以想象成虚拟地址与物理地址。系统 将 LBA 地址最后会转化成为 CHS ，交给磁盘去进行数据读取。

我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。但是在系统软件上，不是直接按照扇区(512字节),进行IO交互，原因如下：

• 如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化
• 从目前来看，单次IO 512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低。
• 之前学习文件系统，就是在磁盘的基本结构下建立的，文件系统读取基本单位，就不是扇区，而是数据块。

综上原因，系统读取磁盘，是以块为单位的，基本单位是 4KB 。

磁盘随机访问与连续访问

• 随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。
• 连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。
• 因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。
• 磁盘是通过机械运动进行寻址的，连续访问不需要过多的定位，故效率比较高。

3. MySQL与磁盘交互的基本单位

而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎讲解)。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）。

• MySQL作为一款应用层软件无法直接与磁盘内的数据进行访问，只能通过操作系统来访问。
• 由于操作系统是按4KB大小的数据块和磁盘进行访存，而MySQL访存的基本单位是16KB。
• 操作系统为MySQL设置一个缓冲区，一次性从磁盘访存4次数据给MySQL使用。
• MySQL从操作系统为其准备的缓冲区中取数据到自身的缓冲区进行操作。

查看MySQL访存基本单位：

SHOW GLOBAL STATUS LIKE 'innodb_page_size';

4. 建立共识

• MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
• MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数 据。
• 而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
• 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新 策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位 就是Page。
• 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进 行IO交互。
• 为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数

查看MySQL的配置文件，可以看到Buffer Pool的默认大小：

5. 索引的理解

创建测试表进行测试

创建了一个主键为序号, 包含年龄、姓名属性的用户表：

用户表的结构如下：

向表中插入一些无序的数据：

查看表中数据：

无序插入的数据，在表中按照主键的顺序出现。

为什么IO交互要使用page：

如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那 么就需要2次IO。如果要找id=5，那么就需要5次IO。 但，如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时 候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5 等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。 我们不能严格保证，但是有很大概率，因为有局部性原理。 往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数。

理解单个page

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，在组织 ,而一个个独立文件是由一个或者多个Page构成的。

• Page 在 MySQL 中，都是 16KB 。
• 单个page文件的结构由两个指针（ prev 和 next ）和数据构成。
• page文件中的数据由链表结构组织起来，因此在page页中增删数据的速度很快，但是查询的速度很慢。
• 因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序。

理解多个page

page通过一次加载16KB的数据的方式，减少IO，提高了了效率，但是数据库中不只是有一个page页：

• 多个page页是由prev 和 next 两个指针构成双向链表。
• 由于多个page页是用链表结构组织起来的，page页内的数据也是由链表结构组织起来的，因此查找数据时需要先遍历page页链表结构找到对应的page页，然后在对应的page页中遍历数据链表结构，因此效率很低。

页目录

由于page结构的限制，只能线性遍历搜素数据，效率很低，我们引入了页目录的概念。

页目录用于记录对应数据以及对应数据所在位置的结构。

这里的页目录就和我们平常看书的目录类似，我们想找到书的某一部分时，可以通过查看目录找到该部分所在章节，然后从该章节对应的页数在章节内寻找，大大提高了效率。于是我们在MySQL中的page页结构中也引入了页目录。

单表情况：

• page页中不仅存有prev、next指针和数据还有页目录。
• 查找数据时，通过页目录筛选数据可能在的位置，减少不必要的记录的遍历。
• page页的大小是固定的，引入页目录本质上是一种空间换时间的策略。

引入页目录后，比如我们要查找图中主键为4的数据，可以先遍历页目录发现主键大于目录2记录的主键数据3，目录1到目录2之间不可能存在，于是就减少了不必要记录的遍历，然后我们发现要找的记录会在目录2指向的位置往后，于是根据目录2指向的位置往后寻找。由于这种查找的方式是需要主键有序的，因此我们可以理解为什么在测试表中发现无序的插入数据，结果却是有序的。

多表情况：

在单个page页内引入了页目录提高了page页内链式结构的数据的查找效率，但是多个page页间仍然是链式结构的，导致了如果要查找特定page页，就要从page页链表的表头开始线性查找，但是一次只从磁盘中加载一个page页，寻找特定page页就要多次IO加载page页影响效率。因此将页目录的结构也使用在page页的查找上：

• 用一些page页专门记录页目录，page页内只存有页目录。
• 专门做页目录的page页中的页目录用于记录page页的编号，和指向对应page页的指针。
• 通过专门做页目录的page页查找对应page页，提高多个page页的查找效率。

同样的专门记录页目录的page页也是链式结构的，因此如果专门记录页目录的page页过多，page页的查找效率也会很低，因此我们需要一些page页记录这些page页的目录：

如此以来，我们建立了由上到下的结构用于提高查找效率，如果数据量增大，我们只需要不断增加向上增加page页就可以提高效率。在后续的查找过程中由上到下通过页目录筛选、除去不必要的page页不记录即可大大的提升查找效率。由于一个page页的大小为16KB，能够存储大量的页目录，因此不用担心，page页的层数过多。

• page页整体的结构是一个B+树的结构。
• 除了B+树也够，也有使用其他结构的存储引擎，比如哈希结构。
• 除了InnoDB,大部分的存储引擎都采用B+树的结构。
• B+树结构只有叶子结点保存数据，并用链式结构存储，非叶子结点保存目录，非叶子节点只存目录的情况下可以存放大量目录，因此该B+树的整体结构是层数比较少的“矮胖”结构，顺着目录寻找page，由于层数少，因此寻找叶子节点需要加载的page页少，IO次数也就少，提高效率。
• InnooDB存储引擎下就是这种B+树的结构，因此在MySQL中对数据的增删查改就是在这个结构内进行的，如果我们没有设置主键，MySQL会生成隐藏的属性列作为排序属性，存在目录里用于管理数据，当然我们不设置主键时，也无法使用这个隐藏的属性列，因此查找数据时只能在叶子节点中线性遍历，因此效率很低。

简单复盘一下：

由于存储数据的page是链式结构，page内的数据是链式结构因此，查找数据只能线性遍历，效率很低，为此引入了只存储页目录的page，通过页目录知道数据在那个范围内，排除不需要遍历的page，由于只存储页目录的page的也是链式结构，因此需要有只存储页目录的page用来查找这些page页，最后形成的整体结构是B+树结构，通过这个结构可以排除大量不需要遍历的page，page页的加载次数只取决于B+树结构的层数，减少了IO,提升了效率。索引的本质就是数据结构。

其他的数据结构的问题

• 链表？线性遍历，效率太低。
• 二叉搜索树？退化问题，可能退化成为线性结构 。
• AVL &&红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。
• Hash？官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash跟 进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就效率很低。

下面是几个常见的存储引擎，与其所支持的索引类型：

• 这里的BTREE指的是B+树。

B树和B+树

B+树是B树的一种变形结构，那为什么我们没有采用普通的B树作为索引结构呢？

• 首先，普通B树中的所有结点中都同时包括索引信息和数据信息，由于一个Page的大小是固定的，因此非叶子结点中如果包含了数据信息，那么这些结点中能够存储的索引信息一定会变少，这时这棵树形结构一定会变得更高更瘦，当查询数据时就可能需要与磁盘进行更多次的IO操作。
• 其次，普通B树中的各个叶子结点之间没有连接起来，这将不利于进行数据的范围查找，而B+树的各个叶子结点之间是连接起来的，当我们进行范围查找时，直接先找到第一个数据然后继续向后遍历找到之后的数据即可，因此将各个叶子结点连接起来更有利于进行数据的范围查找。

聚簇索引和非聚簇索引

• 聚簇索引 – 数据与索引数据在一起索引方案。
• 非聚簇索引 – 数据与索引数据不在一起索引方案。
• InnoDB存储引擎采用的是聚簇索引。
• MyISAM采用的是非聚簇索引。

这个聚簇索引的结构，就是前文提到的只有叶子节点存储数据的B+树结构。

MyISAM索引结构：

MyISAM存储引擎同样采用的是B+树索引结构，不同的是叶子结点不存储数据，只存储数据的指针。

用存储文件来验证聚簇索引和非聚簇索引的结构：

InnoDB存储引擎采用聚簇索引：

MyISAM存储引擎采用非聚簇索引：

MyISAM主键索引结构：

• 和InnoDB的结构一样都是B+树结构，只是叶子结点不存储数据而是数据指针。
• 通过主键进行索引从上倒下查找到对应叶子结点，通过叶子节点存储的指针找到对应数据。

MyISAM普通索引结构：

• MyISAM普通索引和MyISAM主键索引结构一样。
• MyISAM普通索引和MyISAM主键索引的区别是值不能重复。

InnoDB普通索引结构：

• InnoDB普通索引和InnoDB主键索引的结构一样。
• InnoDB普通索引最后会索引到InnoDB主键索引对应的叶子结点，也就是从普通数据索引到对应主键数据，由于主键索引中存储了整条记录，因此找到对应主键数据，就找到了整条记录。
• 通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。

一条结论：

由于具有主键索引和普通索引，因此一张表关联的B+树不止一个。

6. 索引操作

查看索引结构

• 方式一

show keys from table_name;

查看索引结构示例：

• Table – 表名
• Non_unique – 0表示唯一索引
• Key_name – 索引名
• Column_name – 索引属性
• Index_type – 索引结构

其中BTREE就是B+树。

表中不存在索引：

• 方式二

show index from table_name;

查看索引结构示例：

• 方式三

desc table_name;

查看索引结构示例：

• 这种查看方式显示信息太简略，不推荐。
• MySQL默认为主键添加索引。

创建主键索引

• 方式一

在创建表的时候，直接在字段名后指定 primary key。

• 方式二

在创建表的最后，指定某列或某几列为主键索引

• 方式三

创建表以后再添加主键

主键索引的特点：

• 由于存储引擎管理和存储数据方式，添加主键就相当于添加了索引。
• 在已创建的表中添加索引，就会在存储结构中添加一颗新的B+树结构存储索引。
• 一个表中，最多有一个主键索引，当然可以使复合主键。
• 主键索引的效率高（主键不可重复）。
• 创建主键索引的列，它的值不能为null，且不能重复 。
• 主键索引的列基本上是int。

创建唯一键索引

• 方式一

在定义时，在某列后直接指定unique唯一属性。

• 方式二

创建表时，在表的后面指定某列或某几列为unique。

• 方式三

创建表以后再添加唯一键。

唯一索引的特点：

• 一个表中，可以有多个唯一索引。
• 查询效率高。
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据。
• 如果一个唯一索引上指定not null，等价于主键索引。

创建普通索引

• 方式一

在表的定义最后，指定某列为索引。

• 方式二

创建完表以后指定某列为普通索引。

• 方式三

创建一个自定义索引名为索引在表的属性上。

注意： 这里的索引名为自定义的my_index。

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多。
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。

创建复合索引

复合索引就是将多个属性作为索引的普通索引。

• 方式一

在表的定义最后，指定某列为索引。

注意： 由于是多个属性作为一个索引，因此多个属性所属索引名相同。

• 方式二

创建完表以后指定某列为普通索引。

• 方式三

创建一个自定义索引名为索引在表的属性上。

创建全文索引

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

创建测试表：

创建测试表的代码如下：

CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body))engine=MyISAM;-- 插入数据INSERT INTO articles (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 ...');

测试表的结构和数据如下：

查看索引结构：

尝试查询某条记录：

用explain工具看一下，是否使用到索引：

• type : ALL --采用的是线性遍历的查找方式。
• key : NULL – 没有使用索引来查找。

尝试使用全文索引查询某条记录：

用explain工具看一下，是否使用到索引：

• type : fulltext – 使用了全文索引。
• key : title – 使用title索引查询的数据。

删除索引

• 方式一 – 删除主键索引

alter table table_name drop primary key;

• 方式二 – 删除其他索引

alter table table_name drop index index_name; 

• 索引名(index_name)就是查看表结构时中的 Key_name 字段。

注意： 索引名(index_name)不是属性名，删除其他索引时使用的是索引名。

注意： 使用的是索引名，Key_name对应的字段，不是属性名(Column_name对应的字段)：

• 方式三 – 删除自定义名索引

drop index index_name on table_name;

创建索引的原则

索引创建原则：

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

来源地址：https://blog.csdn.net/csdn_myhome/article/details/131753821