网站开发技术实验教程,秦皇岛市建设路小学网站,项目建设管理费,慧聪网官方网站文章目录 聚簇索引 VS 非聚簇索引索引MySQL与磁盘交互的基本单位主键索引索引操作唯一索引的创建普通索引的创建复合索引 索引创建原则 聚簇索引 VS 非聚簇索引
MyISAM存储引擎 - 主键索引结构 MyISAM存储引擎同样采用B树作为索引的基本数据结构 与InnoDB存储引擎的B树不同的… 文章目录 聚簇索引 VS 非聚簇索引索引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 PoolMySQL将数据刷新到磁盘时同样需要先将数据从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)
) ENGINEInnoDB DEFAULT CHARSETutf8
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 )engineinnodb ;
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)
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)mysql create table test2 ( id int primary key , name varchar(20) not null )enginemyisam ;
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)
) ENGINEMyISAM DEFAULT CHARSETlatin1
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子句中的字段不该创建索引
