MySQL【索引上】

在我们学习的过程中我们了解索引绝对是 MySQL 优化中最核心、最常用、也最容易被误解的知识点。很多人只知道“加索引能变快”但不知道为什么快、什么时候该加、加了又有什么代价。这篇文章就从磁盘原理、数据组织、索引结构、聚簇 / 非聚簇索引、索引操作五个维度介绍MYSQL的索引。一、没有索引数据库会有多慢案例先整一个海量表在查询的时候看看没有索引时有什么问题--构建一个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的员工可以看到耗时4秒多这还是在本机一个人来操作在实际项目中如果放在公网中假如同时有1000个人并发查询那很可能就死机。解决方法创建索引测试看看查询时间无索引的本质问题必须全表扫描逐行匹配大量磁盘 IO速度极慢数据量越大慢得越离谱索引的作用不用扫全表快速定位数据大幅减少 IO 次数。二、认识磁盘2.1 MySQL与存储MySQL给用户提供存储服务而存储的都是数据数据在磁盘这个外设当中。磁盘是计算机中的一个机 械设备相比于计算机其他电子元件磁盘效率是比较低的在加上IO本身的特征可以知道如何提交效率是 MySQL的一个重要话题。先来研究一下磁盘在看看磁盘中一个盘片扇区 :1 . 数据库文件本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中就是我们经常所说的扇区。当然数据库文件很大也很多一定需要占据多个扇区。2. 题外话 从上图可以看出来在半径方向上距离圆心越近扇区越小距离圆心越远扇区越大那么所有扇区都是默认512字节吗目前是的我们也这样认为。因为保证一个扇区多大是由比特位密度决定的。不过最新的磁盘技术已经慢慢的让扇区大小不同了不过我们现在暂时不考虑。我们在使用Linux所看到的大部分目录或者文件其实就是保存在硬盘当中的。(当然有一些内存文件系统如 procsys之类我们不考虑)----数据库文件本质其实就是保存在磁盘的盘片当中就是一个一个的文件所以最基本的找到一个文件的全部本质就是在磁盘找到所有保存文件的扇区。而我们能够定位任何一个扇区那么便能找到所有扇区因为查找方式是一样的 。柱面(磁道):多盘磁盘每盘都是双面大小完全相等。那么同半径的磁道整体上便构成了一个柱面每个盘面都有一个磁头那么磁头和盘面的对应关系便是1对1的所以我们只需要知道磁头Heads、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS。不过实际系统软件使用的并不是 CHS但是硬件是而是LBA一种线性地址可以想象成虚拟地址与物理地址。系统将 LBA地址最后会转化成为CHS交给磁盘去进行数据读取。不过我们现在不关心转化细节知道这个东西让我们逻辑自洽起来即可。结论我们现在已经能够在硬件层面定位任何一个基本数据块了(扇区)。那么在系统软件上就直接按照扇区 (512字节部分4096字节),进行IO交互吗不是如果操作系统直接使用硬件提供的数据大小进行交互那么系统的IO代码就和硬件强相关换言之如果硬件发生变化系统必须跟着变化从目前来看单次IO 512字节还是太小了。IO单位小意味着读取同样的数据内容需要进行多次磁盘访问会带来效率的降低。之前学习文件系统就是在磁盘的基本结构下建立的文件系统读取基本单位就不是扇区而是数据块。故系统读取磁盘是以块为单位的基本单位是4KB。2.2 磁盘随机访问(Random Access)与连续访问(Sequential Access)随机访问本次IO所给出的扇区地址和上次IO给出扇区地址不连续这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。连续访问如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的那磁头就能很快的开始这次IO操作这样的多个IO操作称为连续访问。因此尽管相邻的两次IO操作在同一时刻发出但如果它们的请求的扇区地址相差很大的话也只能称为随机访问而非连续访问。磁盘是通过机械运动进行寻址的连续访问不需要过多的定位故效率比较高。随机访问地址不连续 → 磁头频繁移动 → 慢连续访问地址连续 → 磁头几乎不动 → 快三、MySQL与磁盘的交互单位而MySQL作为一款应用软件可以想象成一种特殊的文件系统。它有着更高的IO场景所以为了提高基本的IO效率MySQL 进行IO的基本单位是 16KB(后面统一使用InnoDB存储引擎讲解)也就是说磁盘这个硬件设备的基本单位是512字节而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即 MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元在MySQL这里叫做page注意和系统的page区分不同层级的最小 IO 单位磁盘硬件512 字节操作系统4KBInnoDB16KBPage 页为什么 MySQL 要用 Page16KB核心原因减少 IO 次数。局部性原理一次访问的数据附近数据大概率也会被访问一次加载 16KB 到内存 Buffer Pool后续查询直接在内存完成不用再读磁盘IO 效率的核心矛盾不是每次读多少而是读多少次。四、建立共识五、索引的理解5.1 建立测试表插入多条记录查看插入结果向一个具有主键的表中乱序插入数据发现数据会自动排序。5.2 为何IO交互要是Page为何MySQL和磁盘进行IO交互的时候要采用Page的方案进行交互呢?用多少加载多少不香吗?如上面的5条记录如果MySQL要查找id2的记录第一次加载id1第二次加载id2一次一条记录那么就需要2次IO。如果要找id5那么就需要5次IO。但如果这5条(或者更多)都被保存在一个Page中(16KB能保存很多记录),那么第一次IO查找id2的时候整个Page会被加载到MySQL的Buffer Pool中这里完成了一次IO。但是往后如果在查找id1,3,4,5等完全不需要进行IO了而是直接在内存中进行了。所以就在单Page里面大大减少了IO的次数。你怎么保证用户一定下次找的数据就在这个Page里面我们不能严格保证但是有很大概率因为有局部性原理。往往IO效率低下的最主要矛盾不是IO单次数据量的大小而是IO的次数