html 网站 模板,江苏网站建设价格低,银河互联网电视有限公司,杭州网站建设页面文章目录 索引是什么#xff1f;#xff1f;硬件理解MySQL与存储 MySQL 与磁盘交互基本单位索引的理解B vs B聚簇索引 VS 非聚簇索引索引操作创建主键索引唯一索引的创建普通索引的创建全文索引的创建查询索引删除索引 在现代数据库应用中#xff0c;查询性能是决定系统响应… 文章目录 索引是什么硬件理解MySQL与存储 MySQL 与磁盘交互基本单位索引的理解B vs B聚簇索引 VS 非聚簇索引索引操作创建主键索引唯一索引的创建普通索引的创建全文索引的创建查询索引删除索引 在现代数据库应用中查询性能是决定系统响应速度和用户体验的关键因素之一。MySQL作为最流行的关系型数据库之一提供了强大的索引功能来优化查询性能。本文将深入探讨MySQL索引的工作原理、类型以及如何通过索引优化查询让你的数据库查询飞起来。
索引是什么
对应文章标题让查询飞起来 显然索引就能让加快查询速度
首先我们知道MySQL的服务器本质就是在内存中的对于数据库的CRUD操作全部是在内存中进行的对于索引也是这样的
我们在下面做一个测试
我们先创建一个数据库里面填充一个名为EMP的表插入大量数据我们发现耗时很久才插入完成 由于数据量太大我们仅仅查看前十条
我们随便查询一条发现时间好像有点久
这还是在本机一个人来操作在实际项目中如果放在公网中假如同时有1000个人并发查询那很可能就死机
如何解决 那就是本文所提到的索引
解决方法创建索引
当我们用下列SQL语句对原表的empo列添加索引时数据库底层就会为员工表中的数据记录构建特定的数据结构
alter table EMP add index(empno);由于当前员工表中的数据量较大因此建立索引时也需要花费较长时间 我们用同样的方法查询可以看见时间非常短
原因
常见的索引分为
主键索引primary key唯一索引unique普通索引index全文索引fulltext
硬件理解
MySQL与存储 MySQL 给用户提供存储服务而存储的都是数据数据在磁盘这个外设当中。磁盘是计算机中的一个机 械设备相比于计算机其他电子元件磁盘效率是比较低的。 我们看一个磁片可以看到扇区的大小为512字节 那么在系统软件上 IO一定是512字节吗 并不是
如果操作系统直接使用硬件提供的数据大小进行交互那么如果硬件发生变化系统必须跟着变而且单次访问512字节还是有点小这样的话读取同样的内容单次访问小也就意味着访问的次数需要提高即效率降低 磁盘随机访问(Random Access)与连续访问(Sequential Access) 随机访问本次IO所给出的扇区地址和上次IO给出扇区地址不连续这样的话磁头在两次IO操作之间需 要作比较大的移动动作才能重新开始读/写数据连续访问如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的那磁头就能很快的开始这次 IO操作这样的多个IO操作称为连续访问
MySQL 与磁盘交互基本单位 而 MySQL 作为一款应用软件可以想象成一种特殊的文件系统。它有着更高的IO场景所以为了提高 基本的IO效率 MySQL 进行IO的基本单位是 16KB MySQL 中的数据文件是以page为单位保存在磁盘当中的 MySQL 的 CURD 操作都需要通过计算找到对应的插入位置或者找到对应要修改或者查询的数据 为了更高的效率要尽可能的减少系统和磁盘IO的次数
索引的理解
为什么MySQL与磁盘交互的基本单位是Page 当我们要查询第一条内容时会进行一次IO再次查询第三条内容时又会进行一次IO此时已经IO两次 但如果将其放在同一个Page下的话查询第一条内容时整个Page就会被放在缓冲池Buffer Pool中后面再查询该Page中的内容时就不用IO直接在Page中找这样相当于只用了第一次查询的IO 我们无法保证下次要查询的内容在该Page中但是由于局部性原理很大概率下次的访问会在该Page中
局部性原理大多数业务场景中相邻数据如同一表的连续记录可能被同时访问当需要某一行数据时整个Page会被加载到内存后续对同一Page内的数据访问无需再次触发磁盘I/O
理解单个Page MySQL 中要管理很多数据表文件而要管理好这些文件就需要 先描述在组织 ,我们目前可以简单理解 成一个个独立文件是有一个或者多个Page构成的 主键优化使数据的按序存储也是对查询的一种优化正是因为有序在查找的时候从头到后都是有效查找没有任何一个查找是浪费的
对于单个Page如果数据较多线性遍历查找对于最后一个数据来说并不友好 所以我们在单个Page下创建一个目录 也就是说对于1 2条数据我们放在目录1下对于3 4条数据放在目录2下也就是下图 这也就对应了上述所说主键的有序也是一种优化如果不是有序的话目录下的内容是混乱的此时效率非常低随着数据量不断增大单个Page中无法存下所有数据这时就需要用多个Page来存储数据这时在查询数据时就需要先遍历Page双链表确定目标数据在哪一个Page然后再在该Page内部找到目标数据
多个Page 随着数据量不断增大单个Page中无法存下所有数据这时就需要用多个Page来存储数据 在查询某条数据的时候直接将一整页的数据加载到内存中以减少硬盘IO次数从而提高性能 虽然在单个Page内部能够通过页内目录来快速定位数据但在遍历Page双链表寻找目标Page时本质进行的还是线性遍历这时可以给各个Page结构体也建立页目录页目录中的每个目录项都指向一个Page而这个目录项存放的就是其指向的Page中存放的最小数据的键值在给各个Page结构体建立页目录后在查询数据时就可以先通过遍历页目录找到目标数据所在的Page然后再在该Page内部找到目标数据
所以我们给Page也带上目录
给Page也带上目录
目录页的本质也是页普通页中存的数据是用户数据而目录页中存的数据是普通页的地址 顶层的目录页少了但是还要遍历啊 随着数据量不断增大页目录的数量也会越来越多这时在遍历页目录寻找目标Page时本质进行的还是线性遍历
结论套娃——给目录页加一个目录 豁~~这不就是B树吗 这棵B树就是InnoDB的索引结构
B vs B
B树 B树
-B树B树数据存储位置所有节点均可存储数据仅叶子节点存储数据内部节点为索引叶子节点链接无叶子节点通过双向链表串联树的高度相对较高相同数据量相对较低查询稳定性可能在任何层级命中数据必须查找到叶子节点范围查询效率低需回溯父节点高链表直接遍历相邻叶子冗余数据无键值在内部节点重复存储适用场景随机读写密集、数据离散访问范围查询频繁、顺序扫描需求高 B树 每个节点包括内部节点和叶子节点均可存储数据查询可能在任意层级终止若命中内部节点) B树 仅叶子节点存储数据内部节点仅存储键值作为索叶子节点通过双向链表连接支持高效顺序访问所有查询必须走到叶子节点才能获取数据 查询性能 单次查询B树更快可能在内部节点提前命中 范围查询B树更快通过叶子节点的链表直接遍历无需回溯父节点效率高 聚簇索引 VS 非聚簇索引
MyISAM 存储引擎-主键索引 MyISAM 引擎同样使用B树作为索引结果叶节点的data域存放的是数据记录的地址。下图为 MyISAM 表的主索引 Col1 为主键。 引Page和数据Page分离也就是叶子节点没有数据只有对应数据的地址MyISAM 这种用户数据与索引数据分离的索引方案叫做非聚簇索引所以 MyISAM 这种用户数据与索引数据分离的索引方案叫做非聚簇索引
索引操作
创建主键索引
第一种方式
-- 在创建表的时候直接在字段名后指定 primary 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);唯一索引的创建
第一种方式
-- 在表定义时在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);第二种方式
-- 创建表时在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));第三种方式
create table user6(id int primary key, name varchar(30)
alter table user6 add unique(name);普通索引的创建
第一种方式
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); --创建完表以后指定某列为普通索引第三种方式
create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);全文索引的创建 当对文章字段或有大量文字的字段进行检索时会使用到全文索引 CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body)
)engineMyISAM;查询索引
第一种方法 show keys from 表名;第二种方法
show index from 表名;第三种方法
desc 表名;删除索引
第一种方法删除主键索引
alter table 表名 drop primary key;第二种方法其他索引的删除
alter table 表名 drop index 索引名;第三种方法
drop index 索引名 on 表名;索引创建原则
比较频繁作为查询条件的字段应该创建索引唯一性太差的字段不适合单独创建索引即使频繁作为查询条件更新非常频繁的字段不适合作创建索引不会出现在where子句中的字段不该创建索引 文章转载自： http://www.morning.rfbt.cn.gov.cn.rfbt.cn http://www.morning.xldpm.cn.gov.cn.xldpm.cn http://www.morning.dnpft.cn.gov.cn.dnpft.cn http://www.morning.mnkz.cn.gov.cn.mnkz.cn http://www.morning.pxsn.cn.gov.cn.pxsn.cn http://www.morning.tnqk.cn.gov.cn.tnqk.cn http://www.morning.kttbx.cn.gov.cn.kttbx.cn http://www.morning.qxnlc.cn.gov.cn.qxnlc.cn http://www.morning.nckjk.cn.gov.cn.nckjk.cn http://www.morning.yyngs.cn.gov.cn.yyngs.cn http://www.morning.yppln.cn.gov.cn.yppln.cn http://www.morning.kyjyt.cn.gov.cn.kyjyt.cn http://www.morning.wbyqy.cn.gov.cn.wbyqy.cn http://www.morning.ryyjw.cn.gov.cn.ryyjw.cn http://www.morning.qlpq.cn.gov.cn.qlpq.cn http://www.morning.fkyqt.cn.gov.cn.fkyqt.cn http://www.morning.nkpml.cn.gov.cn.nkpml.cn http://www.morning.mszwg.cn.gov.cn.mszwg.cn http://www.morning.cmzgt.cn.gov.cn.cmzgt.cn http://www.morning.xhkgl.cn.gov.cn.xhkgl.cn http://www.morning.xwbld.cn.gov.cn.xwbld.cn http://www.morning.wsjnr.cn.gov.cn.wsjnr.cn http://www.morning.kmqjx.cn.gov.cn.kmqjx.cn http://www.morning.gjwkl.cn.gov.cn.gjwkl.cn http://www.morning.pbtrx.cn.gov.cn.pbtrx.cn http://www.morning.qmtzq.cn.gov.cn.qmtzq.cn http://www.morning.hbywj.cn.gov.cn.hbywj.cn http://www.morning.czqqy.cn.gov.cn.czqqy.cn http://www.morning.ljyqn.cn.gov.cn.ljyqn.cn http://www.morning.wqfzx.cn.gov.cn.wqfzx.cn http://www.morning.gwqkk.cn.gov.cn.gwqkk.cn http://www.morning.ckdgj.cn.gov.cn.ckdgj.cn http://www.morning.ztjhz.cn.gov.cn.ztjhz.cn http://www.morning.lgqdl.cn.gov.cn.lgqdl.cn http://www.morning.yrlfy.cn.gov.cn.yrlfy.cn http://www.morning.rswfj.cn.gov.cn.rswfj.cn http://www.morning.tgpgx.cn.gov.cn.tgpgx.cn http://www.morning.qcztm.cn.gov.cn.qcztm.cn http://www.morning.dodoking.cn.gov.cn.dodoking.cn http://www.morning.pqnkg.cn.gov.cn.pqnkg.cn http://www.morning.lhrxq.cn.gov.cn.lhrxq.cn http://www.morning.c7498.cn.gov.cn.c7498.cn http://www.morning.krxzl.cn.gov.cn.krxzl.cn http://www.morning.rzcbk.cn.gov.cn.rzcbk.cn http://www.morning.jpwkn.cn.gov.cn.jpwkn.cn http://www.morning.kjtdy.cn.gov.cn.kjtdy.cn http://www.morning.itvsee.com.gov.cn.itvsee.com http://www.morning.jgrjj.cn.gov.cn.jgrjj.cn http://www.morning.bfmrq.cn.gov.cn.bfmrq.cn http://www.morning.rdlong.com.gov.cn.rdlong.com http://www.morning.ssxlt.cn.gov.cn.ssxlt.cn http://www.morning.kmqwp.cn.gov.cn.kmqwp.cn http://www.morning.tsynj.cn.gov.cn.tsynj.cn http://www.morning.sgwr.cn.gov.cn.sgwr.cn http://www.morning.zwppm.cn.gov.cn.zwppm.cn http://www.morning.qlkjh.cn.gov.cn.qlkjh.cn http://www.morning.tqrjj.cn.gov.cn.tqrjj.cn http://www.morning.mqgqf.cn.gov.cn.mqgqf.cn http://www.morning.epeij.cn.gov.cn.epeij.cn http://www.morning.lstmg.cn.gov.cn.lstmg.cn http://www.morning.qnlbb.cn.gov.cn.qnlbb.cn http://www.morning.yskhj.cn.gov.cn.yskhj.cn http://www.morning.cwkcq.cn.gov.cn.cwkcq.cn http://www.morning.zmpsl.cn.gov.cn.zmpsl.cn http://www.morning.kdpal.cn.gov.cn.kdpal.cn http://www.morning.ltzkk.cn.gov.cn.ltzkk.cn http://www.morning.yktr.cn.gov.cn.yktr.cn http://www.morning.zdsdn.cn.gov.cn.zdsdn.cn http://www.morning.pdwzr.cn.gov.cn.pdwzr.cn http://www.morning.rxhs.cn.gov.cn.rxhs.cn http://www.morning.hnrpk.cn.gov.cn.hnrpk.cn http://www.morning.ggcjf.cn.gov.cn.ggcjf.cn http://www.morning.plznfnh.cn.gov.cn.plznfnh.cn http://www.morning.lkmks.cn.gov.cn.lkmks.cn http://www.morning.hjrjr.cn.gov.cn.hjrjr.cn http://www.morning.bkjhx.cn.gov.cn.bkjhx.cn http://www.morning.qzsmz.cn.gov.cn.qzsmz.cn http://www.morning.nrqnj.cn.gov.cn.nrqnj.cn http://www.morning.rnhh.cn.gov.cn.rnhh.cn http://www.morning.wdrxh.cn.gov.cn.wdrxh.cn
