Mysql专栏：@Mysql
本篇博客简介：介绍mysql的索引
在课件上对于索引的定义是这样子的
索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。
总结下上面这段话
索引是mysql的一个特性 使用它可以增加mysql的查找效率 但是可能会牺牲一部分IO效率
常见的索引分类如下
关于这个分类 大家现在不理解也没关系 等看完整篇博客之后就能对于它们有一个清晰的认知
我们这里创建一张海量数据的数据库表（八万条数据） 演示在有和没有索引的情况下 效率的差别
我们可以使用下面的sql来创建一个海量数据表 （直接复制粘贴到mysql终端即可）
drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8;
use `index_demon`;
-- 构建一个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(1+rand()*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(10+rand()*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 ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;
-- 雇员表
CREATE TABLE `EMP` (
`empno` 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 '部门编号'
);
-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);
上述SQL中创建了一个名为index_demon的数据库 在该数据库中创建了一个名为EMP的员工表 并向表当中插入了八百万条记录
建立该数据库需要大概七分钟左右的时间 大家耐心等待
创建完毕之后我们即可使用index_demo数据库和里面的EMP表 下面开始我们的对比工作
查询员工编号为998877编号的员工
我们可以发现 花费了4.35秒才找到了该条记录
创建索引
对这八百万条记录创建索引我们用了20.33秒
重新查询该员工的数据
重新查询之后进步十分明显 直接从4.35秒降低到了0秒
这里就说明一个道理 索引对于海量数据查询效率的提升是十分显著的
宏观过程
由于冯诺伊曼体系我们可以知道 mysql不能对于磁盘中的数据库进行直接访问 它的访问一定是要经过操作系统的内核缓冲区的
大概的运行过程如上图
磁盘的整体结果如下
我们再来看看磁盘中的一个盘片
盘片中又被分为磁道和扇区 如上图所示 我们这里对于磁盘的整体结构和扇区概念有一个认知即可
其中扇区的大小大部分都是512字节
而我们前面说过 数据库的文件都保存在磁盘当中 所以说我们查找数据的本质就是在定位扇区
我们在硬件层面定位扇区的方式叫做CHS
但是在软件层面上我们使用的定位方式叫做LBA（一种线性地址） 我们可以把它们之间的关系想象成虚拟地址和物理地址之前的关系来方便理解
在软件层面上通过LBA定位到以后最终还是要使用CHS来定位具体的物理空间
操作系统和磁盘交互的基本单位
我们现在已经能够在硬件层面定位扇区了 那么在系统软件上我们就直接按照扇区的基本单位（大部分512字节）进行交互嘛？
答案是否定的 原因有以下几点
综上所述 系统读取磁盘是以块为单位的 大小为4kb
因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。
磁盘是通过机械运动进行寻址的，随机访问不需要过多的定位，故效率比较高。
mysql和磁盘交互这句话其实并不准确 因为mysql没有资格直接和磁盘进行交互 它们之间一定是要经过操作系统的 但是我们学习这部分的时候为了方便理解可以暂时忽略操作系统
MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎讲解)
我们可以使用下面的sql语句来查看mysql交互的IO大小 我们可以发现是16384字节 实际也就是16kb
根据上面讲解的知识总结一些共识
如何理解IO请求
首先我们可以明确一点 系统中肯定会存在大量的IO请求 而操作系统作为软硬件资源的管理者肯定要对这些资源进行管理 那么问题又来了 应该如何管理呢？ - - 先描述 再组织
我们都知道Linux操作系统是由C语言写的 而C语言中描述一个对象所使用的方式是结构体
所以说IO请求在Linux操作系统中的形式其实就是结构体 这些结构体再被通过双链表的形式组织起来 这就是操作系统对于IO请求的管理
我们建立一个测试表user 设置id为主键
create table user(
-> id int primary key,
-> age int not null,
-> name varchar(16) not null
-> );
之后我们往表中插入几组数据
在上图中可以发现我们是乱序插入的 可是当我们查询插入结果的时候却变成有序的了
那么看到这里我们就会有两个问题产生
在回答上面两个问题之间我们再理解下page
为什么IO交互的单位要是page呢 为什么不可以我们需要哪个数据就将哪个数据拿到内存中来
我们在前面说过 实际上IO的过程是十分缓慢的 如果说我们要拿十个数据 按照上面的方法就要交互十次
但是如果按照单位page进行交互 根据计算机的局部性原理 我们很可能IO的次数要小于十次 需要注意的是 局部性原理并不能确保一定减少IO的次数 这只是一个大概率事件
mysql在启动时会向系统申请128mb的内存空间（这也就是为什么有时候mysql会启动失败 因为系统没有这么多空间了）
一个page的大小是16kb 也就是说如果这些内存空间全部用来储存page的话就大概会有8000多个page页 既然有这么多的page页mysql肯定就要对它们进行管理 而管理的方法无法就是 先描述 再组织
所以说单个的page其实就是一个结构体
大概的格式如下图
每个page中有一个前驱和后继指针指向前后的page以方便管理 此外数据记录也存放在page中
那么到现在为止 我们就能够回答上面提出的两个问题了
是谁对于这些数据进行排序
mysql
为什么要进行排序
为了提高搜索效率
通过上面的分析我们可以知道 mysql中存在大量的page 并且它们之间是用双链表的形式连接起来的 如下图
但是仔细观察下我们就可以发现下面两点
在之前数据结构–链表章节的学习中 我们知道链表只能够线性的查找数据 也就是说虽然我们将这些数据和页表排序了 但是事实上搜索的效率并不会快多少
这个时候我们就引入了一个叫做页目录的方式来提高效率
假如我们现在看《深入理解计算机系统》这本书的时候 我们想要找到优化程序性能这一章节的内容有两种选择
显然方法二的查询比方法一要快速许多 那么在page内和page之间 我们当然也可以引入目录的概念
我们要查找id=4记录，之前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。现在我们可以再次正式回答上面的问题了，为何通过键值 MySQL 会自动排序
可以方便的建立目录
建立目录的行为本质是一种以空间换时间的做法 虽然目录占用了一定的空间 但是却可以大大提高我们的搜索效率
前面我们说过 在mysql内部有着大量的page页由双链表连接 如下图
虽然说我们通过单页目录提高了页内的搜索效率 但是在实际搜索的过程中 我们还是会遇到下面的两个问题
上面的两个问题如果不解决 我们页内目录做出的效率提升就毫无意义
那么应该如何解决呢？ 答案也是建目录
我们可以将单个的page页看作是一篇文章 它的代号就是page页中键值的最小值
之后我们再重新创建新的page页用来做目录 指向这些 “文章”
但是这样子的模型还是没有解决我们的问题
这个问题的解决方案还是加目录
我们可以在刚刚建立完毕的目录page上层再加上一个page页作为它们的目录指向这些page页
这样子当我们需要找一个page页的时候只需要从最上层的目录开始往下依次查找就可以了
如下图
我们再看上面的结构 其实就是一颗B+树
也就是说我们选择B+数主要有两个方面的考虑
这里直接给出解释
其中innodb用的就是非聚簇索引 而myisam用的是聚簇索引
mysql除了会建立默认的主键索引之外 用户也能按照其他列信息建立索引 而这些索引被叫做辅助索引
对于myisam来说辅助索引和默认的主键索引没有区别
而对于innodb来说 InnoDB的非主键索引中叶子节点并没有数据 而只有对应记录的key值
所以通过辅助（普通）索引 找到目标记录 需要两遍索引 首先检索辅助索引获得主键 然后用主键到主索引中检索获得记录 这种过程 就叫做回表查询
方法一： 在创建表的时候直接添加主键
create table user1(id int primary key, name varchar(30));
方法二： 在创建表的时候在最后指定主键
create table user2(id int, name varchar(30), primary key(id));
方式三： 在创建表后再添加主键
create table user3(id int, name varchar(30));
alter table user3 add primary key(id）;
创建唯一键索引的语法和创建主键索引相同 这里就不过多赘述
方式一：在创建表的最后指定索引
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);
方式二：创建完表之后添加索引
create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引
创建全文索引
FULLTEXT (title,body)--创建全文索引
使用全文索引
WHERE MATCH (title,body) AGAINST ('database');
show index from 表名;
删除主键索引
--方式一：删除主键索引
alter table 表名 drop primary key;
删除其他索引
alter table 表名 drop index 索引名;