[Mysql]mysql索引 文章目录 [MySQL]MySQL索引1. 索引的概念2. 认识磁盘磁盘的内部结构磁盘中的一个盘片结构定位扇区磁盘随机访问与连续访问 3. MySQL与磁盘交互的基本单位4. 建立共识5. 索引的
索引:提高数据库的性能,索引是物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行 正确的 create index ,查询速度就可能提高成百上千倍。但是天下没有免费的午餐,查询速度的提高 是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的io。所以它的价值,在于提高一个 海量数据的检索速度。
常见索引分为:
示例: 先整一个海量表,在查询的时候,看看没有索引时有什么问题?
使用如下SQL语句创建一个拥有海量数据的表:
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)begindeclare chars_str varchar(100) default'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255) default '';declare i int default 0;while i < n doset 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)begindeclare 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))begindeclare i int default 0;set autocommit = 0;repeatset i = i + 1;insert into EMP values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());until i = max_numend 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语句导入到一个本地的文本文件中,然后在linux系统中使用rz命令将该文件导入:
然后在MySQL中使用source执行导入的SQL语句文件:
由于服务器配置的限制,数据量太大可能会导致MySQL卡住,需要耐心等待:
最后执行完成,用时大概7分钟11.79秒:
完成后,会新增一个index_demon数据库:
数据库中有一个EMP表:
查看表结构:
由于表中数据量很大,我们只查看一下前5行数据:
经过多次测试发现正常查询某条记录大概需要4.2秒左右:
然后对表添加索引,花费了大概20.88秒:
有了索引之后,查找的效率大大提高了:
MySQL 给用户提供存储服务,而存储的都是数据,数据在磁盘这个外设当中。
磁盘是计算机中的一个机械设备,相比于计算机其他电子元件,磁盘效率是比较低的,在加上IO本身的特征,要想更好的理解MySQL的效率问题,我们必须要了解磁盘的结构和工作原理。
磁盘由多个盘片组成,盘片具有磁性,使用磁性特点来记录对应的二进制数据。
我们在使用Linux,所看到的大部分目录或者文件,其实就是保存在硬盘当中的,所以,最基本的,找到一个文件的全部,本质,就是在磁盘找到所有保存文件的扇区,而我们能够定位任何一个扇区,那么便能找到所有扇区,因为查找方式是一样的。
MySQL中的数据库和表也是通过文件形式存储在硬盘中的:
因此知道如何在磁盘中如何定位扇区,找到对应的文件,才能知道MySQL中的数据如何找到。
定位扇区的步骤:
磁头(Heads)、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS 。不过实际系统软件使用 的并不是 CHS (但是硬件是),而是 LBA ,一种线性地址,可以想象成虚拟地址与物理地址。系统 将 LBA 地址最后会转化成为 CHS ,交给磁盘去进行数据读取。
我们现在已经能够在硬件层面定位,任何一个基本数据块了(扇区)。但是在系统软件上,不是直接按照扇区(512字节),进行IO交互,原因如下:
综上原因,系统读取磁盘,是以块为单位的,基本单位是 4KB 。
而 MySQL 作为一款应用软件,可以想象成一种特殊的文件系统。它有着更高的IO场景,所以,为了提高基本的IO效率, MySQL进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎讲解)。这个基本数据单元,在 MySQL 这里叫做page(注意和系统的page区分)。
查看MySQL访存基本单位:
SHOW GLOBAL STATUS LIKE 'innodb_page_size';
查看MySQL的配置文件,可以看到Buffer Pool的默认大小:
创建了一个主键为序号, 包含年龄、姓名属性的用户表:
用户表的结构如下:
向表中插入一些无序的数据:
查看表中数据:
无序插入的数据,在表中按照主键的顺序出现。
为什么IO交互要使用page:
如上面的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的次数。 我们不能严格保证,但是有很大概率,因为有局部性原理。 往往IO效率低下的最主要矛盾不是IO单次数据量的大小,而是IO的次数。
MySQL 中要管理很多数据表文件,而要管理好这些文件,就需要 先描述,在组织 ,而一个个独立文件是由一个或者多个Page构成的。
page通过一次加载16KB的数据的方式,减少IO,提高了了效率,但是数据库中不只是有一个page页:
由于page结构的限制,只能线性遍历搜素数据,效率很低,我们引入了页目录的概念。
页目录用于记录对应数据以及对应数据所在位置的结构。
这里的页目录就和我们平常看书的目录类似,我们想找到书的某一部分时,可以通过查看目录找到该部分所在章节,然后从该章节对应的页数在章节内寻找,大大提高了效率。于是我们在MySQL中的page页结构中也引入了页目录。
单表情况:
引入页目录后,比如我们要查找图中主键为4的数据,可以先遍历页目录发现主键大于目录2记录的主键数据3,目录1到目录2之间不可能存在,于是就减少了不必要记录的遍历,然后我们发现要找的记录会在目录2指向的位置往后,于是根据目录2指向的位置往后寻找。由于这种查找的方式是需要主键有序的,因此我们可以理解为什么在测试表中发现无序的插入数据,结果却是有序的。
多表情况:
在单个page页内引入了页目录提高了page页内链式结构的数据的查找效率,但是多个page页间仍然是链式结构的,导致了如果要查找特定page页,就要从page页链表的表头开始线性查找,但是一次只从磁盘中加载一个page页,寻找特定page页就要多次IO加载page页影响效率。因此将页目录的结构也使用在page页的查找上:
同样的专门记录页目录的page页也是链式结构的,因此如果专门记录页目录的page页过多,page页的查找效率也会很低,因此我们需要一些page页记录这些page页的目录:
如此以来,我们建立了由上到下的结构用于提高查找效率,如果数据量增大,我们只需要不断增加向上增加page页就可以提高效率。在后续的查找过程中由上到下通过页目录筛选、除去不必要的page页不记录即可大大的提升查找效率。由于一个page页的大小为16KB,能够存储大量的页目录,因此不用担心,page页的层数过多。
简单复盘一下:
由于存储数据的page是链式结构,page内的数据是链式结构因此,查找数据只能线性遍历,效率很低,为此引入了只存储页目录的page,通过页目录知道数据在那个范围内,排除不需要遍历的page,由于只存储页目录的page的也是链式结构,因此需要有只存储页目录的page用来查找这些page页,最后形成的整体结构是B+树结构,通过这个结构可以排除大量不需要遍历的page,page页的加载次数只取决于B+树结构的层数,减少了IO,提升了效率。索引的本质就是数据结构。
下面是几个常见的存储引擎,与其所支持的索引类型:
存储引擎 | 支持的索引类型 |
---|---|
InnoDB | BTREE |
MyISAM | BTREE |
MEMORY/HEAP | HASH、BTREE |
NDB | HASH、BTREE |
B树和B+树
B+树是B树的一种变形结构,那为什么我们没有采用普通的B树作为索引结构呢?
这个聚簇索引的结构,就是前文提到的只有叶子节点存储数据的B+树结构。
MyISAM索引结构:
MyISAM存储引擎同样采用的是B+树索引结构,不同的是叶子结点不存储数据,只存储数据的指针。
用存储文件来验证聚簇索引和非聚簇索引的结构:
InnoDB存储引擎采用聚簇索引:
MyISAM存储引擎采用非聚簇索引:
MyISAM主键索引结构:
MyISAM普通索引结构:
InnoDB普通索引结构:
一条结论:
由于具有主键索引和普通索引,因此一张表关联的B+树不止一个。
show keys from table_name;
查看索引结构示例:
其中BTREE就是B+树。
表中不存在索引:
show index from table_name;
查看索引结构示例:
desc table_name;
查看索引结构示例:
在创建表的时候,直接在字段名后指定 primary key。
在创建表的最后,指定某列或某几列为主键索引
创建表以后再添加主键
主键索引的特点:
在定义时,在某列后直接指定unique唯一属性。
创建表时,在表的后面指定某列或某几列为unique。
创建表以后再添加唯一键。
唯一索引的特点:
在表的定义最后,指定某列为索引。
创建完表以后指定某列为普通索引。
创建一个自定义索引名为索引在表的属性上。
注意: 这里的索引名为自定义的my_index。
普通索引的特点:
复合索引就是将多个属性作为索引的普通索引。
在表的定义最后,指定某列为索引。
注意: 由于是多个属性作为一个索引,因此多个属性所属索引名相同。
创建完表以后指定某列为普通索引。
创建一个自定义索引名为索引在表的属性上。
当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有 要求,要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文。如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。
创建测试表:
创建测试表的代码如下:
CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body))engine=MyISAM;-- 插入数据INSERT INTO articles (title,body) VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly, MySQL ...');
测试表的结构和数据如下:
查看索引结构:
尝试查询某条记录:
尝试使用全文索引查询某条记录:
用explain工具看一下,是否使用到索引:
alter table table_name drop primary key;
alter table table_name drop index index_name;
注意: 索引名(index_name)不是属性名,删除其他索引时使用的是索引名。
注意: 使用的是索引名,Key_name对应的字段,不是属性名(Column_name对应的字段):
drop index index_name on table_name;
索引创建原则:
来源地址:https://blog.csdn.net/csdn_myhome/article/details/131753821
--结束END--
本文标题: [MySQL]MySQL索引
本文链接: https://www.lsjlt.com/news/372373.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
2024-04-30
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0