06 MySQL索引

文章目录

一、索引的价值

索引提高了数据库的性能，特别是提高了海量数据的检索速度。但是查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。

因此索引带来的价值，是提高查找的效率，如果有大量的插入、更新、删除则不建议使用索引。

常见索引分为：

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

1.1. mysql与磁盘交互的基本单位

1. 操作系统读取磁盘，是以块为单位的，基本单位是 4KB 。MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB (InnoDB 存储引擎)。
2. 这个基本单位在MySQL 之中叫做Page，即只要发生了数据的交互，哪怕只有1bit,也是需要进行16KB的数据进行交互。
3. MySQL 在服务器启动的时候，会预先加载一大块空间自己进行内存管理,这块空间被称为Buffer Pool(MySQL 5.7之中为128KB)。
4. Page的大小是固定的，数据量有限，如果不存储数据，就能够存储更多的索引信息，目录Page能够管理更多的Page，否则目录Page管理的页数太少，整颗树的层数就越多，更深，也就意味着从根节点到叶子节点的Page更多，即需要更多的IO。

1.2. 为什么交互的基本单位为Page（页）

1. 由冯诺依曼体系可知，IO的时间远远大于CPU运算的时间，如果每次要那个数据就加载那个数据，这样IO的次数就提高了，程序所需要的时间就会提高，效率也就降低了。
2. 因此，mysql提高效率的本质是减少IO。
3. 根据局部性原理，我们查找的数据有很大的概率会在同一个区间之内，即同一个page之内，因此我们进行IO的时候就加载一个page到内存之中，如果下次数据在这个区间之间，就不需要进行IO，提高了程序的效率。

1.3. 索引是如何增加查找速度的

以主键（id）索引为例：

由于MySQL和磁盘交互的基本单位是Page，这些Page叫做数据页。我们只需要将数据保存在每个数据页中即可，加载时直接加载一整个数据页，而数据页与数据页之间通过指针连成双向链表，这样就能够获取前一个或后一个数据页。而每条数据之间通过主键进行排序。在每个数据页中也有一个目录，这样在单个数据页中的查询速度就会加快。

在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的数据页来保存新的数据，然后通过指针的方式，将所有的数据页组织起来。但是当数据页多起来的时候，如果加载每个数据页去遍历检测的话，时间会非常慢，这时可以给每个数据页建立起对应的目录，这就是索引。而索引也是通过Page保存的，我们称之为目录页，目录页只放各个下级Page的最小键值：

目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。
当顶层的目录页过多时，可以再加一层目录页。

这种组织方式就是B+树。

总结：

1. Page分为目录页和数据页。数据页在B+树中都是叶子节点。目录页只放各个下级Page的最小键值，由于不放具体的数据，所以目录页中能够存储的索引就会很多，能够减少空间浪费。
2. 查找的时候，自顶向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数。
3. 每一层IO一次，再对加载的页进行二分查找，寻找下一层的页的地址，或者对应的数据的地址。

1.4. 没有主键会怎么创建索引

上面的组织形式是以主键为索引的组织形式，也就是先按照主键进行排序放到数据页中，再用目录页将数据页组织成B+树。如果没有设置主键该以谁为索引呢？

当我们没有设置主键的时候，InnoDB会优先选取一个唯一键作为索引，如果表中连唯一键也没有的话，就会自动为每一条记录添加一个叫做DB_ROW_ID的列作为默认主键，该列是一个6字节的自增数值，随着插入而自增，但是这个主键我们看不到。

1.5. 为什么推荐使用自增ID作为主键

换句话说，为什么主键必须是自增的，用非自增的（比如学号，身份证号）会怎么样？

由于数据页中的记录是按照主键从小到大进行串联的，自增ID决定了后来插入的记录一定会排列在上一条记录的后面，只需要简单添加next_record指针就可以了；如果当前数据页写满，那就放心地直接插入新的数据页中就可以了。

而非自增的主键则不同，它的大小顺序是不确定的，后来插入的记录有可能（而且概率相当大）插入到上一条记录之前（甚至是当前数据页之前），这就意味着需要遍历当前数据页的记录（或者先找到相关的数据页），然后找到自己的位置进行插入；如果当前数据页写满了，只能先找到适合自己位置的数据页，然后在数据页中遍历记录找到自己的合适位置进行插入。

因此使用非自增的主键插入记录花费的时间更长。

1.6. 普通索引（第二索引）

有时候我们搜索数据并不是通过主键（或者唯一键）来搜索的，也有可能是通过非主键列来搜索，而主键索引又是通过主键来完成的，所以主键索引就失效了。这时候可以通过建立普通（辅助）索引来解决这一问题。

比如我们想寻找name字段的一条信息，就可以给name字段创建普通索引。普通索引也是一棵B+树，但是和上面主键的B+树有一些区别：

1. 该B+树的叶子节点存放的不再是完整的用户记录，而是只记录name列和主键值；
2. 目录页记录除了存储索引列（name）和页号之外，同时还存储了主键值。之所以存主键值，主要是因为数据页和目录页不再按照主键进行排序，而是按照name排序。按照name排序时，会出现name相同的情况，此时就可以根据主键进行排序。

此时通过普通索引，就能够找到name对应的主键了，然后我们就可以再从主键索引中找到name对应的完整信息。这个过程称为回表。

1.7. 复合索引

我们的主键也有可能是复合主键。
包含多个列的主键始终会自动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，而不是在主键定义中指定的顺序。

假设我们以id列和name列作为主键，那么在系统创建主键索引时，自然也是创建一棵B+树，但就不是以id为索引了，而是以id和name为索引，当id字段相同时，则会按照name排序。并且在搜索时，也是先匹配id字段，然后再name字段。

同理，我们也可以对多个非主键的字段建立普通的复合索引，其方式和上面类似，比如假设我们为name列和phone列建立联合索引，创建一棵B+树：

1. 叶子节点存放的是name列、phone列和主键值；
2. 目录页记录除了存储索引列（name和phone）和页号之外，同时还存储了主键值。按照name排序时，会出现name相同则按照phone排序，phone再相同则按照主键进行排序。

二、innoDB在建立索引结构来管理数据的时候，其他数据结构为何不行

• 链表 链表是线性结构的，查找的时候需要线性遍历。
• 二叉搜索树 在某些场景下，二叉搜索树会退化成链表。
• AVL树和红黑树 和B+树相比，B+树的层数更低，每层进行一次IO，树越矮，IO的次数越少，AVL和红黑树相对来说，比B+树更改。
• Hash 官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持。Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行。
• B树 B树节点，既有数据，又有Page指针，而B+，只有叶子节点有数据，其他目录页，只有键值和Page指针。 B+树非叶子节点不存储数据，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。B+叶子节点，全部相连，而B没有。叶子节点相连，更便于进行范围查找。

三、InnoDB 和 MyISAM两种存储引擎索引的区别

3.1. 聚簇索引和非聚簇索引

MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。这种用户数据与索引数据分离的索引方案，叫做非聚簇索引。

InnoDB 的数据和索引是放在一起的，这种用户数据与索引数据在一起索引方案，叫做聚簇索引。

它们之间有如下差别：

1. MyISAM的主键索引（非聚簇索引）的叶子节点存储对应数据的地址；InnoDB 的主键索引（聚簇索引）的叶子节点则是存储用户的整条数据。
2. MyISAM的普通索引其叶子节点也只是存储对应数据的地址，和主键索引没区别；InnoDB 的普通索引其叶子节点存储索引值和对应的主键，想找到对应的数据还需要进行回表操作。
3. 聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。相比之下，聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。

四、索引操作

4.1. 创建索引

创建主键索引

创建主键以后，MySQL会自动创建主键索引。

• 第一种方式 在创建表的时候，直接在字段名后指定 primary key

createtable user1(id intprimarykey, name varchar(30));

• 第二种方式： 在创建表的最后，指定某列或某几列为主键索引

createtable user2(id int, name varchar(30),primarykey(id));

• 第三种方式： 创建表以后再添加主键

createtable user3(id int, name varchar(30));altertable user3 addprimarykey(id);

主键索引的特点：

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

唯一索引的创建

创建唯一键以后，MySQL会自动创建唯一索引。

• 第一种方式 =在表定义时，在某列后直接指定unique唯一属性。

createtable user4(id intprimarykey, name varchar(30)unique);

• 第二种方式 创建表时，在表的后面指定某列或某几列为unique

createtable user5(id intprimarykey, name varchar(30),unique(name));

• 第三种方式 创建表以后再添加唯一键

createtable user6(id intprimarykey, name varchar(30)）； 
altertable user6 addunique(name);

唯一索引的特点：

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

普通索引的创建

• 第一种方式 在表的定义最后，指定某列为索引

createtable user8(id intprimarykey, name varchar(20), 
email varchar(30),index(name));

• 第二种方式 创建完表以后指定某列为普通索引

createtable user9(id intprimarykey, name varchar(20), 
email varchar(30));altertable user9 addindex(name);

• 第三种方式 创建一个索引名为 idx_name 的索引

createtable user10(id intprimarykey, name varchar(20), 
email varchar(30));createindex idx_name on user10(name);

普通索引的特点：

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

4.2. 查询索引

• 第一种方法：show keys from 表名 \G;
• 第二种方法：show index from 表名\G;
• 第三种方法：desc 表明;

mysql>showkeysfrom t4\G;***************************1.row***************************(第一个主键)Table: t4             <- 表名
Non_unique: 0<- 如果索引不能包括重复值则为0，如果可以则为1。也就是平时所说的唯一索引
Key_name: PRIMARY<- 索引的名字
Seq_in_index: 1<- 索引中的列序列号，从1开始
Column_name: id          <- 索引是那个(索引的列名)
Collation: A          <- 列以什么方式存储在索引中，大概意思就是字符序
Cardinality: 0<- 基数的意思，表示索引中唯一值的数目的估计值
Sub_part: NULL<-前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL
Packed: NULL<-指示关键字如何被压缩。如果没有被压缩，则为NULLNull:                   <-如果列含有NULL，则含有YES
Index_type: BTREE<- 以二叉树的形式构建索引
Comment: 
Index_comment:          <- 注释的意思

mysql>desc t6;+-------+----------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+----------+------+-----+---------+-------+| id    |int(11)| YES  | MUL |NULL||(key MUL)表示索引
| name  |char(10)| YES  | MUL |NULL||| grade |int(11)| YES  | MUL |NULL||+-------+----------+------+-----+---------+-------+

4.3. 删除索引

• 第一种方法 删除主键索引：alter table 表名 drop primary key;
• 第二种方法 其他索引的删除：alter table 表名 drop index 索引名;。索引名就是show keys from 表名中的Key_name 字段。

比如：

altertable user10 dropindex idx_name;

• 第三种方法方法：drop index 索引名 on 表名;

比如：

dropindex name on user8;

4.4. 全文索引的创建

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

比如下面的表：

createtable articles ( 
id intunsignedauto_incrementnotnullprimarykey,
title varchar(200), 
body text, fulltext(title,body))engine=myisam;insertinto 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 ...');

4.5. explain

mysql explain详解
explain 命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

六、验证索引以及其效率

使用下面的脚本创建EMP表并插入八百万行数据

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

没有索引时，搜索数据：

将empno字段设置为主键，然后再次搜索数据：

如果不使用主键搜索，使用ename：
由于ename字段并不是索引，所以查找的会很慢

将ename设置为普通索引，此时按照ename的查找速度会大大加快：

将主键删除，然后再用ename进行查找：

可以看到即使主键已经删除了，普通索引还是在的，并且主键删除的时间很长。
这主要是因为主键删除以后，DB_ROW_ID就会成为主键，那Page就会重新排列，并且普通索引叶子节点的主键值也会变为DB_ROW_ID。

七、索引覆盖、索引最左匹配原则、索引下推

• 索引覆盖 如果一个表中两个字段，比如id和name，两者为复合索引。 如果今天我们指向查找一个id对应的name，那么它在索引的过程中就能找到id和name，不需要到叶子节点，相当于覆盖了后面的获取主键然后回表操作。
• 索引最左匹配原则 还是id和name为复合索引。 它们在索引过程中是以id排序，id相同才以name排序。此时如果以name查找，就不能用这个复合索引，因为并不是以name排序的。这时候应该以name创建一个普通索引。
• 索引下推 还是id和name为复合索引。 我们要查询所有id为10，name为’%川’的人的信息，把%加在name字段前面的时候，是无法利用索引的顺序性来进行快速比较的，也就是说这条查询语句中只有id字段可以使用索引进行快速比较和过滤。所以会筛选出所有id为10的主键，然后进行回表操作，如果id为10的信息过多，就会产生多次回表操作。 索引下推就是过滤的动作由下层的存储引擎层通过使用索引来完成，而减少不必要的回表操作。

推荐阅读：
图解｜12张图解释MySQL主键查询为什么这么快
图解｜这次，彻底理解MySQL的索引