MySQL索引特性(上)

目录

索引的重要

 案例

认识磁盘 

MySQL与存储

先来研究一下磁盘

 扇区

定位扇区

结论 

 磁盘随机访问与连续访问

MySQL与磁盘交互基本单位

建立共识

 索引的理解

建立测试表

 插入多条记录

局部性原理 

---

所有的MySQL的操作(增删查改)全部都是在MySQL当中的内存中进行的，MySQL在启动的时候会预先帮我们开辟一大块空间，开辟后在合适的时候把数据的操作体现在内存级，然后MySQL再把数据定期刷新到我们对应的外设也就是磁盘当中做持久化；索引也是如此 

索引的重要

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度(查是快了)；    

常见索引分为：

1. 主键索引(primary key)

2. 唯一索引(unique)

3. 普通索引(index)

4. 全文索引(fulltext)--解决中子文索引问题。

 案例

先整一个海量表，在查询的时候，看看没有索引时有什么问题？

 向MySQL中导入mysql> source /home/wwz/index_data.sql

 导入后会在数据库中创建big_index库；use bit_insex;(因为数据量有点大所以根据机器导入时间不同)

mysql> show tables;//因为数据量好多所以不要selece *全部字段列否则要等待好久

mysql> select * from EMP limit 5;//可以列出部分少的

 查询员工编号为998877的员工

mysql> select * from EMP where empno=998877;

查询一条信息就用到5秒多，甚至有的机器因为数据量太大(亿级)直接killed服务(占资源太多了，os直接kill你了)

可以看到耗时5.75秒，这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。

解决办法创建索引：mysql> alter table EMP add index(empno);//加索引也需要时间

换一个员工编号，测试看看查询时间

mysql> select * from EMP where empno=1234567;//发现现在非常快(零点几秒)
由原来的线性遍历变为B+树的搜索当然快了；

认识磁盘 

MySQL与存储

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。磁盘是计算机中的一个机 械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提 交效率，是 MySQL 的一个重要话题。

先来研究一下磁盘

 扇区

数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区。

从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大那么，所有扇区都是默认512字节吗？目前是的，我们也这样认为。因为保证一个扇区多大，是由比特位密度决定的。 不过最新的磁盘技术，已经慢慢的让扇区大小不同了，不过我们现在暂时不考虑。

我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。(当然，有一些内存文 件系统，如： proc ， sys 之类，我们不考虑)

数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件

 ll /var/lib/mysql    #我们目前MySQL中的文件

自己定义的数据库，里面有数据表

 所以最基本的，找到一个文件的全部，本质就是在磁盘找到所有保存文件的扇区。 而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

定位扇区

1. 柱面(磁道): 多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面

2. 每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的

3. 所以，我们只需要知道，磁头（Heads）、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做CHS 。不过实际系统软件使用的并不是CHS （但是硬件是），而是LBA ，一种线性地址，可以想象成虚拟地址与物理地址。系统将 LBA 地址最后会转化成为 CHS ，交给磁盘去进行数据读取。不过，我们现在不关心转化细节，知道这个东西，让我们逻辑自洽起来即可。

结论 

我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。那么在系统软件上，就直接按照扇区 (512字节，部分4096字节),进行IO交互吗？不是

1. 如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化

2. 从目前来看，单次IO 512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低。

3. 之前学习文件系统，就是在磁盘的基本结构下建立的，文件系统读取基本单位，就不是扇区，而是数据块。 故，系统读取磁盘，是以块为单位的，基本单位是 4KB 。

 磁盘随机访问与连续访问

随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。

连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。

因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。

磁盘是通过机械运动进行寻址的，随机访问不需要过多的定位，故效率比较高。

MySQL与磁盘交互基本单位

首先MySQL是一款应用软件，他在系统角度就是应用进程，在网络层是应用服务所以都是在应用层的，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL进行IO的基本单位是16KB (后面统一使用 InnoDB 存储引擎讲解)

 mysql> show global status like 'innodb_page_size';
16384=1024*16；可以看到16KB大小(1KB=1024B)

也就是说，磁盘这个硬件设备的基本单位是512字节，而 MySQL InnoDB引擎使用16KB进行IO交互。 即MySQL 和磁盘进行数据交互的基本单位是16KB 。这个基本数据单元，在 MySQL这里叫做page（注意和系统的page区分）

建立共识

MySQL 中的数据文件，是以page(16KB)为单位保存在磁盘当中的。(在物理上MySQL存数据时以page为单位塞到文件里)

 MySQL 的 CURD(增删查改) 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。

 而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。(计算机结构所决定的)

 所以在特定时间内，数据一定是磁盘中有，内存中也有(相当于我把数据加载到内存里，当我改完了再刷新，也是以page为单位刷新覆盖历史的page数据)。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了(其实也就是把MySQL的数据拷贝给os，单位是16KB，接下来让os强制的把16KB的数据或者整个缓冲区的数据持久化刷到磁盘里)。而此时IO的基本单位就是Page。

 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。(MySQL是用c/c++写的，他开辟空间就是new或者malloc)

vim /etc/my.cnf          //可以看到默认128M大小

 为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数；(就类比于我写100MB的数据，我一次把100MB的数据写到磁盘上和分100次每次1MB写到磁盘上来看，一次性的效率更高，因为只需要一次寻址找到磁盘当中的位置即可，如果100次的话可能进行100次寻址意味着磁盘盘片要转，磁头要摆)

 索引的理解

建立测试表

mysql> create table if not exists user(
    -> id int primary key,
    -> age int not null,
    -> name varchar(16) not null
    -> );

一定要添加主键，只有这样才会默认生成主键索引


默认就是InnoDB存储引擎

 插入多条记录

插入五条记录(随机插入的顺序)

查找发现我明明是无顺序插入的，最后是顺序插入

我们向一个具有主键的表中乱序插入数据，发现数据会自动排序；

 在MySQL内部(自己启动变成一个服务进程)一定需要并且会存在大量的page，也就决定了母三千里必须要将多个同时存在的page管理起来！需要先描述再组织；所以不要简单认为page是一个内存块(16KB大小),page内部也必须写入对用的管理信息！
struct page(
 struct page*prev,
 struct page * next,
 char buffer[num];
);----16KB                     
所以在MySQL里申请一个page就是new page；然后将所有的page用'链表'的形式管理起来(此链表非彼链表)；-----在buffer pool内部，对MySQL中的page进行了一个建模！

为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗? 其实多加载的就是预加载，可以有效减少IO次数；

如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那 么就需要2次IO。如果要找id=5，那么就需要5次IO。
但如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5 等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。

你怎么保证，用户一定下次找的数据，就在这个Page里面？我们不能严格保证，但是有很大概率，因为有局部性原理。 往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数。

局部性原理 

计算机的局部性原理是指在程序执行过程中，访问的数据和指令往往集中在某些局部区域，而不是分散在整个存储空间中。这个原理有两个主要方面：
1. 时间局部性（Temporal Locality）：如果程序中的某个数据项一旦被访问，那么在不久的将来它很可能被再次访问。这意味着最近被使用过的内存位置很可能会被再次使用，因此计算机会将这些位置的数据保留在高速缓存中，以便更快地访问。

2. 空间局部性（Spatial Locality）：一旦某个数据被访问，与该数据附近的数据很可能也会被访问。例如，如果程序访问了一个数组元素，那么其相邻的元素也很可能在不久之后被访问。为了利用空间局部性，计算机系统通常会将邻近的数据项也加载到缓存中，以便减少访问延迟。
局部性原理是计算机系统中优化性能的重要依据，因为它允许系统在内存和处理器之间的数据传输更有效率，减少了存储器访问的时间和能量消耗。通过合理利用局部性，计算机可以更快速地执行程序，提高整体性能和响应速度。