【MySQL索引特性】
前言
| 剑指offer：一年又10天 |
|---|
引入
索引，是用来提高查询效率的，下面我们通过一个书本目录和一个实际查询示例来初步认识一下它。
场景1：我想要看条款49的内容。
因为我并不知道条款49在那一页，如果一页一页翻的话需要翻240页（也就是遍历），肯定能找到但是显而易见效率不高（如果条款49在820页呢）
但是如果我们从目录里面找只需要2，3页就可以涵盖全书的内容，因此可以十分快速地确认条款49的位置（240页），这里的目录就是各个条款的索引。
场景2：在八百万个员工中查询编号为778899的员工信息。
-- 构造表结构
mysql> CREATE TABLE EMP(
-> empid int(6) unsigned zerofill DEFAULT NULL, -- 员工id
-> ename varchar(20) DEFAULT NULL, -- 员工姓名
-> job varchar(20) DEFAULT NULL, -- 工作
-> mgr int(6) unsigned zerofill DEFAULT NULL, -- 上司
-> hiredate datetime DEFAULT NULL, -- 入职日期
-> sal float(8,2) DEFAULT NULL, -- 工资
-> bonus float(8,2) DEFAULT NULL, -- 奖金
-> deptid int(11) DEFAULT NULL -- 部门id
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
--构建一个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 ;
-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);
-- 要好长好长时间...（5min）
-- 查看一下记录插入情况，数据量太大，我们只看前十条
mysql> select * from EMP limit 0, 10;
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| empid | ename | job | mgr | hiredate | sal | bonus | deptid |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| 100002 | NRUZlg | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 504 |
| 100003 | DSDpOb | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 407 |
| 100004 | TbynUK | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 414 |
| 100005 | XrQeXC | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 430 |
| 100006 | EtfxXt | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 508 |
| 100007 | TvvPQI | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 431 |
| 100008 | mIJhSP | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 232 |
| 100009 | QPAjvx | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 28 |
| 100010 | RdSbDN | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 48 |
| 100011 | gtAxKh | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 318 |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
10 rows in set (0.00 sec)
-- 查看一下 empid=778899的员工信息， 用时4.8秒
mysql> select * from EMP where empid = 778899;
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| empid | ename | job | mgr | hiredate | sal | bonus | deptid |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| 778899 | pzEvtJ | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 486 |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
1 row in set (4.80 sec)
-- 再查一次，4.74秒
mysql> select * from EMP where empid = 778899;
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| empid | ename | job | mgr | hiredate | sal | bonus | deptid |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| 778899 | pzEvtJ | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 486 |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
1 row in set (4.74 sec)
-- 给EMP表添加主键
mysql> alter table EMP add primary key(empid);
Query OK, 0 rows affected (1 min 1.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 再次查找empid = 778899的员工信息，耗时0.00秒（小于1微秒）
mysql> select * from EMP where empid = 778899;
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| empid | ename | job | mgr | hiredate | sal | bonus | deptid |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| 778899 | pzEvtJ | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 486 |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
1 row in set (0.00 sec)
-- 再找一次，耗时0.00秒
mysql> select * from EMP where empid = 778899;
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| empid | ename | job | mgr | hiredate | sal | bonus | deptid |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
| 778899 | pzEvtJ | SALESMAN | 000001 | 2023-12-05 00:00:00 | 2000.00 | 400.00 | 486 |
+--------+--------+----------+--------+---------------------+---------+--------+--------+
1 row in set (0.00 sec)
通过测试我们发现，添加了主键后，查找的效率提升了至少1000倍。（主键就是索引的一种）
认识磁盘
MySQL与存储
MySQL是用来存储的，存储的都是数据，而数据都保存在磁盘中，因此当我们需要访问数据时都需要先将数据都磁盘中拷贝到内存，也就是需要访问磁盘，而磁盘是计算机中的外设，属于机械设备，因此访问速度是很慢的，所以为了提高MySQL处理速度，减少磁盘访问次数是很有必要的且有效的。
在往下进行之前我们要先建立一些共识：
- 磁盘中最小存储单位为扇区，一个扇区存储大小为512B；
- 内存IO的基本单位为块（也可以叫page），一个块存储大小为4KB（从磁盘中读取数据到内存，一次最少读取8个扇区）；
- MySQL进行IO的基本单位为page，一个page存储大小为16KB（4块，与内存中的page区分）；
为什么内存最小存储单位不采用扇区，甚至是需要多少数据就读取多少数据？
答：为了减少对磁盘的访问次数，提高访问效率，由于计算机的空间局部性原理（如果一个内存位置被访问，那么它附近的内存位置也有可能在近期内被访问。这通常是由于程序的数据结构和算法导致某些数据项在内存中聚集在一起），因此一次将它周围的数据一起IO到内存，以达到减少磁盘IO次数，提高效率的目的。
而MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎讲解)。
-- 查看InnoDB存储引擎的page大小
mysql> show global status like'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 | -- 16384 / 1024 = 16
+------------------+-------+
1 row in set (0.05 sec)
共识：
- MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
- MySQL 的 CURD操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
- 而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
- 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
- 为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数。
索引的理解
-- 建立测试表
mysql> create table if not exists user (
-> id int primary key, -- 一定要设置id为主键，否则观察不出效果
-> age int not null,
-> name varchar(16) not null
-> );
Query OK, 0 rows affected (0.03 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 -- 默认存储引擎就是 InnoDB
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)
我们插入数据时id是乱序的，但是查看时却发现id按照升序排好了序，是谁排的序？为什么要排序？
理解单个Page
MySQL要管理很多数据表，这些数据表在Linux下的表现就是文件，一个page也需要被OS所管理，通过"先描述再组织"，在OS内维护对应的page结构，一个page16KB，一个文件有个十几二十MB却很正常，也就是说一个张表可以拥有多个page，不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表
因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。
还记得我们最开始举的书本目录的例子吗，为什么通过目录查到条款49的页码为240就可以快速找到条款49，为什么呢？因为我们知道页码小于240的都不是，240一定在它们后面；如果240前面不是239，而是350，240后面不是241而是300，也就是说如果页码是无序的，那么目录也就失去了它快速定位的意义，所以为了能够快速定位，我们需要保证数据的有序性。
上表由于有主键id，mysql服务器在我们插入数据时就根据id的大小进行排序（如何排序的后面再说），方便后序的查找。
有了有序页表（根据id排序），下面就需要一个目录来帮助我们快速定位。
理解多个Page
多个page通过前后指针链接为双向链表。
通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。
引入B+树结构
在InnoDB中，实际上是使用B+树来维护一个数据表的page结构。
B+树的特点：
1.平衡的多路搜索树；
2.只有叶子节点存储数据，分支节点只存储关键字索引信息；
3.叶子节点通过指针连接为单链表；
根据B+树的特性，我们给page带上索引。
每次访问新的page都需要进行磁盘IO，为了减少磁盘IO的次数，在page的目录中只存储page索引和地址的映射而不存储数据，这样一个page目录页就可以存储更多的page地址，减少查找次数也就减少磁盘IO次数，数据只存储在最下一层的page中。
可是，我们每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还要遍历啊？不用担心，可以再加目录页。
复盘一下：
- Page分为目录页和数据页。目录页只放各个下级Page的最小键值；
- 查找的时候，自顶向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数；
- B+树的叶子节点连接为单链表而InnoDB中使用双链表（方便快速找到临近节点的数据）；
- 上面的两个图的第二层不一样但是并没有画错，倒数第一个由于有Page目录因此可以通过目录找到需要的Page，倒数第二个则需要遍历Page来查找，因此需要互相连接（倒数第二个属于推导的中间过程图，实际不存在，不用关注）。
为什么采用B+树而不是数组、红黑树以及B树和hash表呢？
数组、链表等不用说；
红黑树到底还是二叉树，当数据量很大时这棵红黑树是又高又细的，那么在查找时就需要查找更多的page目录，因此效率并不高；
B树则是中间节点也存储数据，那么中间节点存储的目录索引就变少，查找过程中遍历的page目录就变多了，没有B+树效率高；
hash表也有其优势，MySQL中也提供了hash表结构只是InnoDB没有采用，感兴趣的朋友可以之后自行了解。
这里的BTREE是B+树
聚簇索引 VS 非聚簇索引
MyISAM存储引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址，下图为MyISAM表的主索引，col1为主键。
其中MyISAM最大的特点是，将索引Page和数据Page分离，也就是叶子结点没有数据，只有对应数据的地址。
相较于InnoDB索引，InnoDB是将索引和数据放在一起的。
-- 创建测试表 mtest
mysql> create table mtest(
-> id int primary key,
-> name varchar(11) not null
-> )engine=MyISAM; -- 使用MyISAM存储引擎
Query OK, 0 rows affected (0.01 sec)
.frm文件是所有存储引擎都有的，用来存储表结构；
.MYD文件和.MYI文件是MyISAM存储引擎独有的，D就是data，用来存储数据，I就是index，用来存储索引。其中，MyISAM这种用户数据与索引数据分离的索引方案，叫做非聚簇索引。
-- 创建测试表 itest
mysql> create table itest(
-> id int primary key,
-> name varchar(11) not null
-> )engine=InnoDB; -- 使用InnoDB存储引擎
Query OK, 0 rows affected (0.03 sec)
.ibd文件是InnoDB存储引擎独有的，存储表的索引和数据。其中，InnoDB这种用户数据与索引数据放在一起的索引方案，叫做聚簇索引。
当然，MySQL除了默认会建立主键索引（默认建立的我们无法直接使用）外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。 对于MyISAM，建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。
下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别：
同样， InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的 Col3 建立对应的辅助索引如下图：
可以看到， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。
所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。
为何 InnoDB 针对这种辅助（普通）索引的场景，不给叶子节点也附上数据呢？原因就是太浪费空间了。
什么是默认主键索引？为何MySQL要默认建立主键索引？
当MySQL在创建表时，如果没有明确声明主键，系统会自动创建一个名为"PRIMARY"的默认主键索引。这个索引不是给用户直接使用的，而是创建普通索引时作为普通索引回表查询时的主键；如果用户声明了 primary key，MySQL就不会创建默认主键索引。
总结：
- InnoDB 主键索引和普通索引
- MyISAM 主键索引和普通索引
- 其他数据结构为何不能作为索引结构，尤其是B+和B
- 聚簇索引 VS 非聚簇索引
索引操作
创建主键索引
方法一：
-- 在创建表的时候，直接在字段名后指定 primary key
create table p_tb1(id int primary key, name varchar(20));
方法二：
-- 在创建表的最后，指定某列或某几列为主键索引
create table p_tb2(id int, name varchar(20), primary key(id));
方法三：
create table p_tb3(id int, name varchar(20));
-- 创建表以后再添加主键
alter table p_tb3 add primary key(id);
主键索引的特点：
- 一个表中，最多有一个主键索引，当然可以使复合主键；
- 主键索引的效率高（主键不可重复）；
- 创建主键索引的列，它的值不能为null，且不能重复；
- 主键索引的列基本都是int。
唯一索引的创建
方法一：
-- 在创建表的时候，直接在字段名后指定 unique key
create table u_tb1(id int unique key, name varchar(20));
方法二：
-- 在创建表的最后，指定某列或某几列为唯一键索引
create table u_tb2(id int, name varchar(20), unique key(id));
方法三：
create table u_tb3(id int, name varchar(20));
-- 创建表以后再添加唯一键
alter table u_tb3 add unique key(id);
唯一索引的特点：
- 一个表中，可以有多个唯一索引；
- 查询效率高；
- 如果在某一列建立唯一索引，必须保证这列不能有重复数据；
- 如果一个唯一索引上指定not null，等价于主键索引。
普通索引的创建
方法一：
-- 在创建表的最后指定某列或某几列为索引
create table i_tb1(id int, name varchar(20), index(id));
方法二：
create table i_tb2(id int, name varchar(20));
-- 创建表以后再添加索引
alter table i_tb2 add index(id);
方法三：
create table i_tb3(id int, name varchar(20));
-- 创建一个索引名为 idx_id 的索引
create index idx_id on i_tb3(id);
普通索引的特点：
- 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多；
- 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。
查看索引
方法一：
SHOW KEYS FROM table_name;
mysql> show keys from p_tb1\G
*************************** 1. row ***************************
Table: p_tb1 -- 表名
Non_unique: 0
Key_name: PRIMARY -- 索引名
Seq_in_index: 1 -- 主键在索引中的位置（复合索引中，越小越先比较）
Column_name: id -- 列名，索引所在的列
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE -- 索引类型：B+树
Comment:
Index_comment:
1 row in set (0.03 sec)
方法二：
SHOW INDEX FROM table_name; -- 结果同方法一一样
方法三：
DESC table_name; -- 只能简单查看索引信息
mysql> desc p_tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | | -- 主键索引
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc i_tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | | -- 普通索引
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删除索引
方法一：
alter table p_tb1 drop primary key; -- 由于主键索引只有一个，因此不需要特意指明
方法二：
-- 删除普通索引
ALTER TABLE table_name DROP INDEX index_name;
mysql> show keys from u_tb1\G
*************************** 1. row ***************************
Table: u_tb1
Non_unique: 0
Key_name: id -- 查看索引名为 id
...
...
alter table u_tb1 drop index id; -- 唯一键索引也是普通索引
方法三：
DROP INDEX index_name on table_name;
mysql> show keys from i_tb1\G
*************************** 1. row ***************************
Table: i_tb1
Non_unique: 1
Key_name: id -- 索引名为 id
...
...
drop index id on i_tb1;
索引创建原则
- 比较频繁作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
- 更新非常频繁的字段不适合作创建索引
- 不会出现在where子句中的字段不该创建索引
总结
一个索引对应一个B+树，一张表可以有多个索引就可以有多个B+树；
如果一张表没有主键，mysqld也会使用默认自增长主键创建B+树，目的是方便之后普通索引的建立。
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！