索引【MySQL】

聚簇索引 VS 非聚簇索引

MyISAM存储引擎 - 主键索引结构

• MyISAM存储引擎同样采用B+树作为索引的基本数据结构

• 与InnoDB存储引擎的B+树不同的是，MyISAM存储引擎的B+树的叶子结点存放的不是数据记录，而是数据记录对应的地址

下图为MyISAM存储引擎的主键索引结构，其中Col1为主键

MyISAM存储引擎 - 普通索引结构

• MyISAM存储引擎的普通索引采用的也是B+树结构，与主键索引唯一不同的地方就是普通索引的B+树中的键值可以重复。
• 因此一张表可能会同时存在多个B+树结构，但由于MyISAM存储引擎的B+树叶子结点中，存储的是对应的数据记录的地址，因此有效数据只会存储一份

下图为MyISAM存储引擎的普通索引结构，其中Col2为索引列

InnoDB存储引擎 - 普通索引结构

• InnoDB存储引擎的普通索引采用的也是B+树结构，但普通索引的B+树中的键值可以重复，并且B+树的叶子结点中存储的不是数据记录，而是对应数据记录的主键值。
• 当根据普通索引查询数据时，会先查找普通索引对应的B+树找到目标记录的主键值，然后再查找主键索引对应的B+树找到目标记录，这个过程就叫做回表查询

下图为InnoDB存储引擎的普通索引结构，其中Col3为索引列

InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录，是为了节省空间，因为同一张表可能会创建多个普通索引，每个普通索引的B+树中都保存一份数据会造成数据冗余，所以通过回表查询主键索引对应的B+来获取整个数据记录，该做法本质一种以时间换取空间的做法。
当根据普通索引查询数据时，其实也不一定需要进行回表查询，因为有可能我们要查询的就是这条记录对应的主键值，因此查询完普通索引对应B+树后即可完成查询。
采用InnoDB存储引擎建立的每张表都会有一个主键，就算用户没有设置，InnoDB也会自动帮你创建一个不可见的主键，因为完整数据记录只会存储在主键索引对应的B+树中的，因此采用InnoDB存储引擎建立的表必须有主键。

• 聚簇索引： 像InnoDB存储引擎这种，将数据记录与索引结构放在一起的索引方案，叫做聚簇索引。
• 非聚簇索引： 像MyISAM存储引擎这种，将数据记录与索引结构分离的索引方案，叫做非聚簇索引

采用InnoDB存储引擎创建表时，在数据库对应的目录下会新增两个文件

采用MyISAM存储引擎创建表时，在数据库对应的目录下会新增三个文件

采用InnoDB和MyISAM存储引擎创建表时都会生成xxx.frm文件，该文件中存储的是表结构相关的信息。
采用InnoDB存储引擎创建表时会生成一个xxx.ibd文件，该文件中存储的是索引和数据相关的信息，这就是聚簇索引，索引和数据是存储在同一个文件中的。
采用MyISAM存储引擎创建表时会生成一个xxx.MYD文件和一个xxx.MYI文件，其中xxx.MYD文件中存储的是数据相关的信息，而xxx.MYI文件中存储的是索引相关的信息，这就是非聚簇索引，索引和数据是分开存储的。

索引

MySQL与磁盘交互的基本单位

MySQL与磁盘交互的基本单位是16KB，这个基本数据单元在MySQL这里也叫做Page

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

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.02 sec)

16384 = 1024* 16

Buffer Pool

在MySQL中进行的各种CRUD时，需要先通过计算找到对应的操作位置，只要涉及计算就需要CPU参与，而冯诺依曼体系结构决定了CPU只能和内存打交道，为了便于CPU参与，就需要先将数据加载到内存当中
所以在特定的时间内，MySQL中的数据一定是同时存在于磁盘和内存中的，当操作完内存数据后，再以特定的刷新策略将内存中的数据刷新到磁盘当中，这时MySQL和磁盘进行数据交互的基本单位就是Page。
为了更好的支持上述操作，MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存，这块内存空间叫做Buffer Pool，后续磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘。
由于内核中是有内核文件缓冲区的，因此MySQL从磁盘读取数据时，需要先将数据从磁盘读取到内核文件缓冲区，再将数据从内核缓冲区读取到Buffer Pool，MySQL将数据刷新到磁盘时，同样需要先将数据从Buffer Pool刷新到内核文件缓冲区，再将数据从内核文件缓冲区刷新到磁盘

操作系统和磁盘交互的基本单位是4KB，是指内核文件缓冲区与磁盘之间是以4KB为单位进行交互的。而MySQL的Buffer Pool和磁盘实际并不是直接交互的

MySQL与磁盘交互的基本单位是16KB，指的是MySQL的Buffer Pool与内核文件缓冲区之间是以16KB为单位进行交互的。只不过更关注的是MySQL和磁盘之间的关系，所以直接说的是MySQL与磁盘交互的基本单位是16KB，忽略了中间的内核缓冲区

主键索引

创建索引

mysql> select * from  EMP limit 5 ;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 100002 | pfDDeM | SALESMAN | 0001 | 2024-11-07 00:00:00 | 2000.00 | 400.00 |    251 |
| 100003 | jvDEkm | SALESMAN | 0001 | 2024-11-07 00:00:00 | 2000.00 | 400.00 |    325 |
| 100004 | vlRzWk | SALESMAN | 0001 | 2024-11-07 00:00:00 | 2000.00 | 400.00 |    130 |
| 100005 | EjioUI | SALESMAN | 0001 | 2024-11-07 00:00:00 | 2000.00 | 400.00 |    332 |
| 100006 | lhdXzF | SALESMAN | 0001 | 2024-11-07 00:00:00 | 2000.00 | 400.00 |    293 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
5 rows in set (0.00 sec)mysql> alter table EMP add index(empno) ;
Query OK, 0 rows affected (22.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table if not exists user (  id int primary key, age int not null,  name varchar(16) not null );
Query OK, 0 rows affected (0.01 sec)mysql> desc user ;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
|  id    | int(11)     | NO   | PRI | NULL    |       |
| age    | int(11)     | NO   |     | NULL    |       |
|  name  | varchar(16) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show create table user \G
*************************** 1. row ***************************Table: user
Create Table: CREATE TABLE `user` (` id` int(11) NOT NULL,`age` int(11) NOT NULL,` name` varchar(16) NOT NULL,PRIMARY KEY (` id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)mysql> insert into user( id, age,  name) values(3, 18, '杨过');
Query OK, 1 row affected (0.00 sec)mysql>  insert into user (id, age, name) values(4, 16, '小龙女');
Query OK, 1 row affected (0.00 sec)mysql>  insert into user (id, age, name) values(2, 26, '黄蓉');
Query OK, 1 row affected (0.00 sec)mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)mysql> insert into user (id, age, name) values(1, 56, '欧阳锋');
Query OK, 1 row affected (0.00 sec)mysql> select * from user;
+----+-----+-----------+
| id | age | name      |
+----+-----+-----------+
|  1 |  56 | 欧阳锋    |
|  2 |  26 | 黄蓉      |
|  3 |  18 | 杨过      |
|  4 |  16 | 小龙女    |
|  5 |  36 | 郭靖      |
+----+-----+-----------+
5 rows in set (0.00 sec)

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

之所以出现按照主键进行有序排列的现象，因为我们创建表时设置了主键，即便向表中插入数据时是乱序插入的，MySQL底层也会自动按照主键对插入的数据进行排序

如何理解MySQL与磁盘交互的基本单位是Page

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

索引操作

创建两个表test1,test2

mysql> create table  test1  ( id int primary key , name varchar(20) not null )engine=innodb ;
Query OK, 0 rows affected (0.01 sec)mysql> desc test1 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> show  create   table test1 \G
*************************** 1. row ***************************Table: test1
Create Table: CREATE TABLE `test1` (`id` int(11) NOT NULL,`name` varchar(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)mysql> create table  test2  ( id int primary key , name varchar(20) not null )engine=myisam ;
Query OK, 0 rows affected (0.00 sec)mysql> show  create   table test2 \G
*************************** 1. row ***************************Table: test2
Create Table: CREATE TABLE `test2` (`id` int(11) NOT NULL,`name` varchar(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_index_db |
+--------------------+
| test1              |
| test2              |
+--------------------+
2 rows in set (0.00 sec)mysql> show index from test1\G
*************************** 1. row ***************************Table: test1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 

删除索引

mysql> show tables;
+--------------------+
| Tables_in_index_db |
+--------------------+
| test1              |
| test2              |
+--------------------+
2 rows in set (0.00 sec)mysql> alter table test1 drop primary key ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from test1\G
Empty set (0.00 sec)mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加索引

mysql> alter table test1 add primary key(id) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from test1\G
*************************** 1. row ***************************Table: test1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 
1 row in set (0.00 sec)

主键索引的特点：

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

唯一索引的创建

mysql> alter table test1 add unique (name) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc test1 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

唯一索引的删除

mysql> alter table test1  drop   index name ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除之后 ，只剩下一个主键索引了

普通索引的创建

普通索引的特点如下：

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

第一种方式：

mysql> alter table test1 add index(name) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

普通索引的删除

mysql> alter table test1 drop index name ; 
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

第二种方式：

mysql> create  index myindex on test1(name) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show  index from test1\G
*************************** 1. row ***************************Table: test1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 
*************************** 2. row ***************************Table: test1Non_unique: 1Key_name: myindexSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 
2 rows in set (0.00 sec)

普通索引的删除

mysql> alter table  test1 drop index myindex ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from test1\G
*************************** 1. row ***************************Table: test1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 
1 row in set (0.00 sec)

复合索引

新增一列email, 再添加name和email为索引

mysql> alter table test1  add email varchar(30) not null after name  ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc test1 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| email | varchar(30) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> alter table test1 add index(name,email) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

name和emali这两列充当索引列 ，用这两列构建B+树， name和email使用的是同一颗B+树

删除name索引

mysql> alter  table   test1  drop   index  name ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

第二种普通索引的创建

mysql> show index from test1\G
*************************** 1. row ***************************Table: test1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 
1 row in set (0.00 sec)mysql> create index myindex on test1(name, email);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引的删除

mysql> alter table test1 drop index myindex;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引创建原则

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