5. Mysql索引详解

题记

0.引言

我们创建一张表user，这张表里面有4 个字段，id，name，gender，phone。当这张表有500万条数据，在没有索的name字段上执行一条where查询:

select * from user where name ='卡卡西'

• 在没有索引时查询比较慢

• 在有索引时查询非常快

导致这样的现象是为什么呢，下面让我们一步一步的探索。

1.索引是什么

数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询更新数据库表中数据。到底是个什么玩意？

我们姑且理解为是一种数据结构，这个数据结构的形式是B+Tree。

2.如何创建索引

2.1 查看表中索引

show index from {table_name};

2.2 添加索引

CREATE TABLE {table_name}(  
    ...
    INDEX  {index_name}({column_name})  
);

2.3 修改索引

# 修改表

alter table {table_name} add unique index {index_name}({column_name}); # 唯一索引
alter table {table_name} add index {index_name}({column_name});        # 普通索引
create index {index_name} on {table_name}({column_name});              # 普通索引

2.4 删除索引

drop index {index_name} on {table_name};
alter table {table_name} drop index {index_name}

3.B+Tree的落地形式

3.1 Mysql数据存储文件

MySQL的数据都是文件的形式存放在磁盘中的，我们可以找到这个数据目录的地址。在MySQL中有这么一个参数，我们来看一下:

show VARIABLES LIKE 'datadir';

我们新建一个库，会发现在此目录下会有一个库名对应的文件夹

CREATE DATABASE IF NOT EXISTS `my_test`;

再在此库中创建user表

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_uid` (`uid`) USING BTREE,
  KEY `index_name` (`name`) USING BTREE,
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

会发现my_test目录下有users.frm和users.ibd

下边我们再探索这两个文件是个什么东西。要说明的是.frm文件存储的是表的元数据信息，即表的创建语句信息。

3.2 MyISAM引擎

首先我们先创建一个student表，存储引擎我们选择MyIsam

CREATE TABLE `student` (
    `id` INT(11) NOT NULL,
    `name` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
    `gender` INT(1) NOT NULL,
    `phone` VARCHAR(11) NOT NULL COLLATE 'utf8_general_ci',
    `email` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
    `class` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `name` (`name`) USING BTREE,
    INDEX `gender` (`gender`) USING BTREE,
    INDEX `phone_email` (`phone`, `email`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

再观察下my_test文件目录下会发现多了student.MYD,student.MYI和student.frm三个文件。

.MYD文件：D代表的Data，是MyISAM的数据存储文件，存放该表的所有数据记录。

.MYI文件：I代表Index，是MyISAM的索引文件，存放索引。如主键索引，唯一索引等。

.frm文件: 表的元数据信息，里面存储的是表的创建信息。

3.2.1 怎么根据索引找到数据呢？

介绍完结构后，我们来探索下它的数据查找形式。

MyISAM的B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录。如下图：

所有的索引文件都存储在.MYI文件中，在从.MYD文件中找到索引对应的数据值。

3.3 InnoDB引擎

在3.1中我们介绍users表时，使用的是InnoDB存储引擎，这时应该猜到了，InnoDB的索引肯定是在.ibd文件中，因为.frm存的是创建表的语法，那数据存哪了那，这里要说明下在InnoDB中，.idb文件即存储索引也存储数据，下边我们来探索下。

3.3.0 索引存储数据形式

在叶子节点存储索引和数据。

3.3.1 聚集索引

1. InnoDB和MyISAM之间，只有InnoDB支持聚簇索引

2. 若一张表存在主键，则以该主键列生聚簇索引树

3. 若一张表没有主键，则MySQL会找到该表的第一个唯一非空列的索引作为聚簇索引

4. 如以上条件皆不满足，InnoDB会在内部生成一个名为GEN_CLUST_INDEX隐式聚簇索引。该索引是基于一个名为DB_ROW_ID的隐藏字段，通常称之隐式主键。

聚集索引特点：

• 聚簇索引其中一个大特征就是将索引和数据存储在同一个文件中，既叶子结点不仅保存键的信息，还保存了位于同一行其他列的信息，简而言之，聚簇索引的叶子结点保存的是一个完整行记录数据

3.3.2 非聚集索引

1. 除主键索引外，其他的索引均为非聚集索引。

非聚集索引特点：

• 叶子节点存储该索引键信息以及对应的聚集索引信息(主键信息)，

• 在用非聚集索引查询时流程

1. 根据索引值找到索引位置并得到主键值

2. 根据上一步得到的主键索引值，再进行查询，最后得到全部记录

总结：两次查找，一次是查找次级索引自身，然后能再查找主键

4.索引的使用

4.1 选择合适列

1. 在设计表时，查询频次较高并且该列的重复值较少时创建索引

为什么强调重复值呢？

因为当该列的重复值较高时，则索引值相同，必须要再一次匹配查询到的数据，当数据量大时，mysql优化器可能不再使用索引进行查询。

4.2 联合索引最左匹配原则

1. 在MySQL建立组合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从组合索引的最左边开始匹配。

基于上述逻辑：在建联合索引时，最常用的查询列，优先放在最左边。

4.3 覆盖索引

上述表述，当索引为非聚集索引时，需要两次查询才能找到具体数据，但当这下边这种情况时，一次查询即可得到数据，即为覆盖索引。

对于组合索引(col1,col2,col3)，如果有如下的select col1,col2,col3 from table where col1=1 and col2=2。那么MySQL可以通过直接一遍历次该组合索引，便可以取到col1,col2,col3三列的数据，而无需回表，这就减少了很多的IO操作。

4.4 索引条件下推（ICP）

索引条件下推((Index Condition Pushdown)，是5.6版本以后完善功能，只适用于非聚集索引。目标是减少io操作，下边我们来研究下。

下推，其实是意思是把过滤的动作在存储引擎做完，而不需要到 Server 层过滤。

在last_name和first_name 上面创建联合索引。

drop table employees;
CREATE TABLE employees(
emp_no'int(11) NOT NULL
birth_date' date NULL,
first_name’ varchar(14) NOT NULL
last_name' varchar(16) NOT NULLgender enum('M','F') NOT NULL,
hire_date' date NULL,
PRIMARY KEY (emp no )
)ENGINE=InnoDB DEFAULT CHARSET=latinl;
alter table employees add index idx lastname firstname(last_name,first_name);

查询所有姓王，且最后一个字为zi的员工，比如王胖子，王瘦子。

select * from employees where last_name='wang' and first_name LIKE "%zi";

正常情况来说，因为字符是从左往右排序的，当你把%加在前面的时候，是不能基于索引去比较的，所以只有last name(姓)这个字段能够用于索引比较和过滤

所以查询过程是这样的:

1)根据联合索引查出所有姓 wang 的二级索引数据(3 个主键值:6、7、8)

2)回表，到主键索引上查询全部符合条件的数据(3条数据)

3)把这3条数据返回给 Server 层，在Server 层过滤出名字以zi结尾的员工。如下图。

注意，索引的比较是在存储引擎进行的，数据记录的比较，是在 Server 层进行的。而当first name的条件不能用于索引过滤时，Server 层不会把first name 的条件传递给存储引擎，所以读取了两条没有必要的记录

这时候，如果满足last name='wang'的记录有10万条，就会有99999条没有必要读取的记录。

所以，根据first name字段过滤的动作，能不能在存储引擎层完成呢?

这是第二种查询方法:

1)根据联合引查出所有姓 wang 的二级索引数据(3 个主键值:6、7、8)

2)然后从二级索引中筛选出first name以zi结尾的索(1个索引)

3)然后再回表，到主键索引上查询全部符合条件的数据(1条数据)，返回给Server层。

总结：即先利用二级索引查询到值，再用二级索引进行匹配规则，最后返回到server。如下图

很明显，第二种方式到主键索引上查询的数据更少。

ICP是默认开启的，也就是说针对于二级索引，只要能够把条件下推给存储引擎，它就会下推，不需要我们千预:

set optimizer_switch='index_condition_pushdown=on';