个人网站建设详细教程,建站工具大全,给别人做网站的公司,免费生成网站网址文章目录 MySQL1. 索引的引入2. 认识磁盘2.1 磁盘的组成2.2 扇区2.3 磁盘访问 3. 磁盘和MySQL交互4. 索引的概念4.1 索引测试4.2 Page4.3 单页和多页情况 MySQL 1. 索引的引入 海量表在进行普通查询的时候#xff0c;效率会非常的慢#xff0c;但是索引可以解决这个问题。
-… 文章目录 MySQL1. 索引的引入2. 认识磁盘2.1 磁盘的组成2.2 扇区2.3 磁盘访问 3. 磁盘和MySQL交互4. 索引的概念4.1 索引测试4.2 Page4.3 单页和多页情况 MySQL 1. 索引的引入 海量表在进行普通查询的时候效率会非常的慢但是索引可以解决这个问题。
--构建一个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(1rand()*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(10rand()*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 ((starti)
,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);查询员工编号为998877的员工
select * from EMP where empno998877;本机一个人来操作就要耗时接近5秒所有如果放在公网中假如同时有1000个人并发查询那很可能就死机。 解决方法创建索引
alter table EMP add index(empno);换一个员工编号测试看看查询时间
select * from EMP where empno123456;2. 认识磁盘 磁盘disk是指利用磁记录技术存储数据的存储器。 磁盘是计算机主要的存储介质可以存储大量的二进制数据并且断电后也能保持数据不丢失。早期计算机使用的磁盘是软磁盘Floppy Disk简称软盘如今常用的磁盘是硬磁盘Hard disk简称硬盘。 2.1 磁盘的组成
盘片 盘片是存储数据的主要介质通常由铝、玻璃或陶瓷等材料制成表面涂有磁性物质数据就记录在这些磁性涂层上。 例如一个磁盘可能有多个盘片像多层蛋糕一样叠放。 磁道 磁道是磁盘表面上的一组同心圆。数据在磁盘上的存储就是分布在这些磁道上的。可以把磁道想象成一个环形的跑道数据就如同运动员在跑道上的位置。每个磁道被划分成多个扇区扇区是数据读写的基本单位。磁道的密度会影响磁盘的存储容量和数据传输速度。 例如磁盘外圈的磁道周长较长能存储更多数据而内圈磁道周长较短存储的数据相对较少。在磁盘工作时磁头会沿着磁道移动来读取或写入数据。 2.2 扇区
扇区 扇区是磁盘存储的基本单位它的大小一般是固定的常见为 512 字节。比如要存数据到磁盘就会按扇区来存放。扇区有编号从 0 开始。 相邻扇区组成磁道多个磁道构成盘面。就算数据不满一个扇区也会占一整个扇区的空间。扇区能让磁盘存储更高效、管理数据更方便。 数据库文件本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中就是我们经常所说的扇区。当然数据库文件很大也很多一定需要占据多个扇区。 定位扇区 通常存储着磁盘的重要信息比如分区表这能告诉系统磁盘如何划分区域来存储不同的数据。还可能存有引导记录帮助计算机启动时找到操作系统的位置并加载。定位扇区就像是磁盘的“地图指南”和“启动钥匙”。 例如当计算机开机时会首先读取定位扇区的信息来知道如何找到并启动系统 定位扇区通常是指在磁盘操作中为了特定目的而专门标识或指定的扇区。它可能具有特殊的用途或被系统用于特定的功能例如存储磁盘的关键信息如分区表、引导记录等。 普通扇区则是磁盘上用于一般数据存储和读写的扇区。 我们现在已经能够在硬件层面定位任何一个基本数据块了(扇区)。那么在系统软件上就直接按照扇区(512字节部分4096字节)进行IO交互吗不是 如果操作系统直接使用硬件提供的数据大小进行交互那么系统的IO代码就和硬件强相关换言之如果硬件发生变化系统必须跟着变化 从目前来看单次IO512字节还是太小了。IO单位小意味着读取同样的数据内容需要进行多次磁盘访问会带来效率的降低。 之前文件系统就是在磁盘的基本结构下建立的文件系统读取基本单位就不是扇区而是数据块。所以系统读取磁盘是以块为单位的基本单位是 4KB 。 2.3 磁盘访问 磁盘随机访问(Random Access)与连续访问(Sequential Access) 随机访问本次IO所给出的扇区地址和上次IO给出扇区地址不连续这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。 连续访问如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的那磁头就能很快的开始这次IO操作这样的多个IO操作称为连续访问。 因此尽管相邻的两次IO操作在同一时刻发出但如果它们的请求的扇区地址相差很大的话也只能称为随机访问而非连续访问。 磁盘是通过机械运动进行寻址的随机访问不需要过多的定位故效率比较高。 3. 磁盘和MySQL交互 而 MySQL 作为一款应用软件可以想象成一种特殊的文件系统。它有着更高的IO场景所以为了提高基本的IO效率 MySQL 进行IO的基本单位是 16KB。 也就是说磁盘这个硬件设备的基本单位是 512 字节而 MySQL InnoDB引擎 使用 16KB 进行IO交互。MySQL 和磁盘进行数据交互的基本单位是 16KB 。 这个基本数据单元在 MySQL 这里叫做page。
mysql SHOW GLOBAL STATUS LIKE innodb_page_size;
-------------------------
| Variable_name | Value |
-------------------------
| Innodb_page_size | 16384 | -- 16*102416384
-------------------------
1 row in set (0.01 sec)总结 MySQL 中的数据文件是以page为单位保存在磁盘当中的。 MySQL 的 CURD 操作都需要通过计算找到对应的插入位置或者找到对应要修改或者查询的数据。 而只要涉及计算就需要CPU参与而为了便于CPU参与一定要能够先将数据移动到内存当中。 所以在特定时间内数据一定是磁盘中有内存中也有。后续操作完内存数据之后以特定的刷新策略刷新到磁盘。而这时就涉及到磁盘和内存的数据交互也就是IO了。而此时IO的基本单位就是Page。 为了更好的进行上面的操作 MySQL 服务器在内存中运行的时候在服务器内部就申请了被称为 Buffer Pool 的的大内存空间来进行各种缓存。其实就是很大的内存空间来和磁盘数据进行IO交互。 为何更高的效率一定要尽可能的减少系统和磁盘IO的次数。 4. 索引的概念 MySQL 索引是一种用于提高数据库查询和操作性能的数据结构。 它就像是一本书的目录通过索引MySQL 能够更快地定位和获取所需的数据而不必遍历整个数据表。 所以索引能够显著提高数据库的查询速度可能让速度提升数百甚至数千倍只需执行正确的 create index 操作无需对内存、程序或 SQL 语句进行大的改动。 同时也强调了索引并非毫无代价。虽然能加快查询但会降低插入、更新和删除操作的速度因为这些写操作会产生大量的 I/O 开销。这意味着在使用索引时需要权衡查询性能和写操作性能之间的平衡。 关于常见索引的分类 主键索引primary key用于唯一标识表中的每一行记录确保其值的唯一性和非空性。例如在学生表中学号可以作为主键索引。 唯一索引unique确保某一列的值不重复但允许为 NULL。比如在用户表中身份证号可以设置为唯一索引。 普通索引index用于加快数据的查询速度但不保证列值的唯一性。比如在商品表中商品名称可以创建普通索引。 全文索引fulltext主要用于解决中文文本的索引问题能够高效地在大量文本数据中进行搜索。例如在文章表中文章内容可以创建全文索引来快速搜索特定的关键词或短语。 4.1 索引测试
建立测试表
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 insert into user (id, age, name) values(3, 18, 杨过);
Query OK, 1 row affected (0.01 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.01 sec)mysql insert into user (id, age, name) values(5, 36, 郭靖);
Query OK, 1 row affected (0.01 sec)mysql insert into user (id, age, name) values(1, 56, 欧阳锋);
Query OK, 1 row affected (0.00 sec)查看插入结果
mysql select * from user;我们会发现排序竟然默认是有序的。 4.2 Page
单个Page MySQL 中要管理很多数据表文件而要管理好这些文件就需要 先描述在组织 我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。 不同的 Page 在 MySQL 中都是 16KB 使用 prev 和 next 构成双向链表。 因为有主键的问题 MySQL 会默认按照主键给我们的数据进行排序从上面的Page内数据记录可以看出数据是有序且彼此关联的。 多个Page 在上面页模式中只有一个功能就是在查询某条数据的时候直接将一整页的数据加载到内存中以减少硬盘IO次数从而提高性能。但是我们也可以看到现在的页模式内部实际上是采用了链表的结构前一条数据指向后一条数据本质上还是通过数据的逐条比较来取出特定的数据。 如果有1千万条数据一定需要多个Page来保存1千万条数据多个Page彼此使用双链表链接起来而且每个Page内部的数据也是基于链表的。那么查找特定一条记录也一定是线性查找。这效率也太低了。 4.3 单页和多页情况
单页情况 针对上面的单页Page我们能否也引入目录呢当然可以。 那么当前在一个Page内部我们引入了目录。比如我们要查找id4记录之前必须线性遍历4次才能拿到结果。现在直接通过目录2[3]直接进行定位新的起始位置提高了效率。现在我们可以再次正式回答上面的问题了为何通过键值 MySQL 会自动排序可以很方便引入目录。 多页情况 MySQL 中每一页的大小只有 16KB 单个Page大小固定所以随着数据量不断增大 16KB 不可能存下所有的数据那么必定会有多个页来存储数据。 在单表数据不断被插入的情况下 MySQL 会在容量不足的时候自动开辟新的Page来保存新的数据然后通过指针的方式将所有的Page组织起来。 其实目录页的本质也是页普通页中存的数据是用户数据而目录页中存的数据是普通页的地址。 … …
