索引初始一、索引没有索引可能会发生什么问题呢索引提高数据库的性能索引是物美价廉的东西不用加内存不用改程序不用调sql只要执行正确的create index查询速度就可能提高成白上千但是天下没有免费的午餐查询速度的提高是以插入、更新、删除为代价的这些写操作增加了大量的IO所以它的价值在于提高一个海量数据的检索速度Mysql的服务器本质是在内存中所有数据库的CRUD操作全部是在内存中进行的索引也是如此。提高算法效率的因素组织数据的方式和算法本身常见索引分为主键索引唯一索引普通索引全文索引 ————解决中子文索引问题看看索引怎么跑的先整一个海量表在查询的时候看看没有索引有什么问题drop database if exists bit_index; create database if not exists bit_index default character set utf8; use bit_index; delimiter $$ create function rand_string(n INT) returns varchar(255) DETERMINISTIC begin declare chars_str varchar(100) default abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ; 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) DETERMINISTIC 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 ; CREATE TABLE EMP ( empono int(6) unsigned zerofill NOT NULL COMMENT 雇员编号, ename varchar(10) DEFAULT NULL COMMENT 雇员姓名, job varchar(9) DEFAULT NULL COMMENT 雇员职位, mgr int(4) unsigned zerofill DEFAULT NULL COMMENT 雇员领导编号, hiredate datetime DEFAULT NULL COMMENT 雇佣时间, sal decimal(7,2) DEFAULT NULL COMMENT 工资月薪, comm decimal(7,2) DEFAULT NULL COMMENT 奖金, deptno int(2) unsigned zerofill DEFAULT NULL COMMENT 部门编号 ); call insert_emp(100001, 800000);创建出海量数据的表后查询员工编号为100002的员工我们可以看到这里一共耗时 0.23 秒这还是在本机一个人来操作在实际项目中如果放在公网中假如同时有 100000 个人并发查询那很可能就死机。解决方法创建索引换一个员工编号测试看看查询时间二、认识磁盘1、MySQL与存储MySQL给用户提供存储服务而存储的都是数据数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备相比于计算机其他电子元件磁盘效率是比较低的再加上IO本身的特征可以知道如何提交效率是MySQL的一个重要话题2、磁盘3、磁盘中一个盘片4、扇区数据库文件本质其实就是保存在磁盘的盘片当中也就是上面的一个个小格子中就是我们经常所说的扇区。当然数据库文件很大也很多一定需要占据多个扇区从上图可以看出在半径方向上距离圆心越近扇区越小距离圆心越圆扇区越大那么所有的扇区都是默认512字节吗目前是的我们也这样认为。因为保证一个扇区多大是由比特位密度决定的不过在最新的磁盘技术中已经慢慢的让扇区大小不同了不过我们目前暂时不考虑这个问题。我们在使用Linux时所看到的大部分目录/文件其实就是保存在硬盘当中。当然有一些内存文件系统如procsys之类的我们不做考虑数据库文件的本质其实就是保存在磁盘当中就是一个个的文件所以最基本的找到一个文件的全部本质就是磁盘找到所有保存文件的扇区而我们能够定位任何一个扇区那么便能找到所有扇区因为查找的方式是一样的5、定位扇区柱面磁道多盘磁盘每盘都是双面大小完全相等。那么同半径的磁道整体上便构成了一个柱面。每个盘面都有一个磁头那么磁头和盘面的对应关系便是 1 对 1 的。所以我们只需要知道磁头Heads、柱面Cylinder等价于磁道、扇区Sector对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS 。不过实际系统软件使用的并不是 CHS但是硬件是而是 LBA 一种线性地址可以想象成虚拟地址与物理地址。系统将 LBA 地址最后会转化成为 CHS 交给磁盘去进行数据读取。不过现在不关心转化细节只需要知道这个东西让我们逻辑自洽起来即可。6、结论我们现在已经能够在硬件层面定位任何一个基本数据库扇区了那么在系统软件上面就直接按照扇区512字节部分4096字节进行IO交互吗不是如果操作系统直接使用硬件提供的数据大小进行交互那么系统的IO代码就和硬件强相关。换而言之如果硬件发生变化系统也会发生变化从目前来看单次IO 512字节还是太小了。IO 单位小意味着读取同样的数据内容需要多次磁盘访问会带来效率的降低之前学习文件系统就是在磁盘的基本结构下简历 的文件读取基本单位就是不是扇区而是数据块所以系统读取磁盘是以快为单位的其基本单位是4KB7、磁盘随机访问与连续访问随机访问本次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 区分四、建立共识MySQL 中的数据文件是以 page 为单位保存在磁盘当中的。MySQL 的 CURD 操作都需要通过计算找到对应的插入位置或者找到对应要修改或者查询的数据。只要涉及计算就需要 CPU 参与而为了便于 CPU 参与一定要能够先将数据移动到内存当中。所以在特定的时间内数据一定是磁盘中有内存中也有。后续操作完内存数据之后以特定的刷新策略刷新到磁盘。而这时就涉及到磁盘和内存的数据交互也就是 IO 了。而此时 IO 的基本单位就是 Page。为了更好的进行上面的操作 MySQL 服务器在内存中运行时在服务器内部就申请了被称为 Buffer Pool 的的大内存空间来进行各种缓存其实就是很大的内存空间来和磁盘数据进行 IO 交互。为了达到更高的效率一定要尽可能的减少系统和磁盘 IO 的次数。