「前言」文章内容大致是Mysql索引的学习。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 目录 一、索引概念二、从硬件角度理解2.1 磁盘2.2 结论
「归属专栏」MySQL
「主页链接」个人主页
「笔者」枫叶先生(fy)
O(N)mysql是一款网络服务器，Mysql服务端是一直在内存中，MySQL客户端的CURD操作都会交给MySQL的服务端完成，即MySQL的所有CURD操作都是在内存中进行的，即索引也是在内存中的进行的。
比如，假设数据库组织数据的方式是线性的，查询的时间复杂度将会是O(N)；如果数据库组织数据的方式是以二叉树的，那么查询的时间复杂度将会大大降低。
所以，索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护，维护结构是需要成本的。
即查询速度的提高是以插入、更新、删除的速度为代价的
常见的索引分为：
先见一见索引
使用如下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中创建了一个名为index_demon的数据库，在该数据库中创建了一个名为EMP的员工表，并向表当中插入了八百万条记录。
将上述的SQL语句保存在一个文件中，然后MySQL中使用source命令执行文件中的SQL
等待SQL语句执行完成（时间有点久）
查看数据库就能看到一个名为index_demon的数据库
在查看EMP表中的数据时可以带上limit子句（数据量太大）
查询EMP表中指定工号的员工信息（该表没有索引），每次查询都要花费几秒进行查询（在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机）
下面给该表创建索引
再查询EMP表中指定工号的员工信息，发现查询的效率大大提高了
根据实验结果，发现索引能大大提高查询数据的效率，这就是索引的价值所在。
磁盘在该文章有详细介绍，文章链接：点击传送，这里就简单说一下概念
磁盘的整体结构如下：（物理结构）
磁盘的存储结构
Tracker）：磁盘表面被分为许多同心圆，每个同心圆称为一个磁道，每个磁道都有自己的编号Cylinder）：多个磁盘的同一个磁道重叠起来叫做磁柱Head）：一个盘面，每个盘面都有自己的编号Sector）：磁道的一个扇形区，每个扇区都有自己的编号磁盘的基本读写单位是扇区，扇区大小一般是 512字节（512byte） ，每个盘片被分为若干个同心圆，每一个同心圆就是一个磁道，而每个磁道被划分为若干个扇区，每个扇区的大小都是 512字节
注意：近三十年来，扇区大小一直是512字节，但最近几年正在迁移到更大、更高效的4096字节扇区，通常称为4K扇区
我们在使用linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。(当然，有一些内存文件系统，如： proc ， sys 之类，我们不考虑)
比如：数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件
在该目录下存储着mysql的文件
ls /var/lib/mysql
新建一个数据库，本质上就是在该目录下创建一个目录（图中颜色没有高亮，普通用户查看）
所以，最基本的，找到一个文件的全部，本质，就是在磁盘找到所有保存文件的扇区
而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的
扇区的定位方式
定位扇区时采用CHS寻址方式
注：通过 柱面Cylinder —— 磁头Head —— 扇区Sector进行寻址，这种寻址方法为 CHS寻址
说明一下：
LogicalBlock Address，逻辑区块地址）注：以上在磁盘篇章有详细介绍，这里就不再谈论。
操作系统与磁盘交互的基本单位
操作系统与磁盘进行IO交互的基本单位是4KB，而不是扇区的大小512字节，原因如下：
磁盘随机访问(
Random Access)与连续访问(Sequential Access)
因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问
连续访问中的连续指的是访问的扇区地址的连续，而不是访问时间的连续，由于连续访问不需要过多的定位，因此效率比较高
MySQL与磁盘交互的基本单位
MySQL作为一款应用软件，可以想象成是一种特殊的文件系统，它有着更高频的IO场景，因此为了提高基本的IO效率，MySQL与磁盘交互的基本单位是16KB
通过show命令查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是16KB
mysql> show global status like 'innodb_page_size';
注：后面统一使用InnoDB存储引擎进行讲解
也就是说，磁盘这个硬件设备的基本单位是512字节，而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即， MySQL 和磁盘进行数据交互的基本单位是16KB。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）
Buffer Pool
Buffer Pool，后续磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘Buffer Pool，MySQL将数据刷新到磁盘时，同样需要先将数据从Buffer Pool刷新到内核缓冲区，再将数据从内核缓冲区刷新到磁盘。
为了方面下面理解，我们需要有以下共识：
建立一个测试表，表当中包含用户的id、年龄和姓名，并将用户的id设置成主键
create table if not exists user (id int primary key, -- 一定要添加主键，只有这样才会默认生成主键索引age int not null,name varchar(16) not null);
然后插入多条记录，并且插入数据时没有按照主键的大小顺序插入，即乱序插入
insert into user (id, age, name) values(3, 18, '张三');insert into user (id, age, name) values(4, 16, '李四');insert into user (id, age, name) values(2, 26, '王五');insert into user (id, age, name) values(5, 36, '赵六');insert into user (id, age, name) values(1, 56, '田七');插入完成后，查看表中的数据时，却发现显示出来的数据是按照主键进行有序排列的
引入主键之后，插入的数据自动按照主键排序这个排序工作是谁做的，为什么要这么做？
重谈MySQL的page，MySQL与磁盘进行交互时为什么不是按需交互，而是以Page为基本单位进行交互（16KB）？
Buffer Pool中，这里完成了一次IO。怎么保证，用户一定下次找的数据，就在这个Page里面？
综上，MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率
对Page进行建模如下：
struct page{struct page *next;struct page *prev;char buffer[NUM];}// 一个Page大小16KB不同的Page ，在 MySQL 中，都是16KB ，都是使用prev和next指针构成双向链表，从而用链表的形式对Page进行了管理
假设测试表中的记录都在同一个Page当中，那么该Page的结构大致如下：
单个Page内创建页内目录（引入页目录）
什么是页目录
比如我们在看《谭浩强C程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法：
上面是理解单个Page，下面理解多个Page
多个Page
Page之上创建页目录
注意：目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址
页目录之上再创建页目录
B+树中的Page结点无需全部加载到Buffer Pool中：
常见的存储引擎，与其所支持的索引类型：
注：这里的BTREE指的是B+树
B树 VS B+树
B+树是B树的一种变形结构
B树结构如下：
B+树结构如下：
MyISAM存储引擎 - 主键索引结构
注：图中Col1列为主键
InnoDB存储引擎 - 主键索引结构
聚簇索引 VS 非聚簇索引（实验）
创建一个是InnoDB为索引的表
mysql> create table if not exists test1( -> id int unsigned auto_increment primary key, -> name varchar(20) -> )engine=InnoDB;进入该路径，找到自己所在的数据库
ls /var/lib/mysql -l
进入自己所在的数据库
当采用InnoDB存储引擎创建表时，在数据库对应的目录下会新增两个文件
xxx.frm文件，该文件中存储的是表结构相关的信息xxx.ibd文件，该文件中存储的是索引和数据相关的信息，这就是所谓的聚簇索引，索引和数据是存储在同一个文件中的
再创建一个test2的表，使用MyISAM存储引擎创建表
mysql> create table if not exists test1( -> id int unsigned auto_increment primary key, -> name varchar(20) -> )engine= MyISAM;
当采用MyISAM存储引擎创建表时，在数据库对应的目录下会新增三个文件
xxx.frm文件，该文件中存储的是表结构相关的信息xxx.MYD文件和一个xxx.MYI文件xxx.MYD文件中存储的是数据相关的信息xxx.MYI文件中存储的是索引相关的信息
普通索引
MyISAM存储引擎 - 普通索引结构
对于 MyISAM，建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复，如下图：
InnoDB存储引擎 - 普通索引结构
同样， InnoDB除了主键索引，用户也会建立辅助（普通）索引
以上表中的 Col3 建立对应的辅助索引如下图：
注意：采用InnoDB存储引擎建立的每张表都会有一个主键，就算用户没有设置，InnoDB也会自动帮你创建一个不可见的主键
创建主键索引方式一
在创建表的时候，直接在字段名后指定primary key
mysql> create table if not exists user1( -> id int primary key, -> name varchar(20) -> );
创建主键索引方式二
在创建表的最后，指定某列或某几列为主键索引
mysql> create table if not exists user2( -> id int, -> name varchar(20), -> primary key(id) -> );
创建主键索引方式三
创建表以后再添加主键
alter table user3 add primary key(id);
主键索引的特点：
唯一索引的创建方式一
在表定义时，在某列后直接指定unique唯一属性
mysql> create table user4( -> id int primary key, -> name varchar(20) unique -> );
唯一索引的创建方式二
创建表时，在表的后面指定某列或某几列为unique
mysql> create table user5( -> id int primary key, -> name varchar(20), -> unique(name) -> );
唯一索引的创建方式三
创建表以后再添加唯一键
alter table user6 add unique(name);
唯一索引的特点
not null，等价于主键索引普通索引的创建方式一
在表的定义最后，指定某列为索引
create table user7(id int primary key,name varchar(20),email varchar(30),index(name));
普通索引的创建方式二
创建完表以后指定某列为普通索引
alter table user8 add index(name);
普通索引的创建方式三
创建表后，使用create命令给指定字段创建普通索引，并指定索引名
-- 创建一个索引名为 idx_name 的索引create index idx_name on user9(name);
普通索引的特点
查询索引方式一
语法：
show keys from 表名;例如，查询user1表
部分说明：
Table： 表示创建索引的表的名称Non_unique： 表示该索引是否是唯一索引，如果是则为0，如果不是则为1Key_name： 表示索引的名称（索引也有名字）Column_name： 表示定义索引的列字段Index_type： 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）第二种方法
语法：
show index from 表名;比如查询user4的索引，有两行说明有两个索引
第三种方法
语法：（查出的信息比较简略）
desc 表名;比如查询user7表的索引
删除主键索引
语法：
alter table 表名 drop primary key;例如，删除user1的主键索引
删除非主键索引
语法：
alter table 表名 drop index 索引名;注：索引名就是show keys from 表名 中的Key_name字段
例如，删除user4表的唯一键索引
第三种方法
该语法可以删除指定的非主键索引：
drop index 索引名 on 表名;MyISAM，而且默认的全文索引支持英文，不支持中文sphinx的中文版(coreseek)全文索引案例
创建一个文章表，表当中包含文章的id、文章名称、文章内容，并在创建表的最后通过fulltext给title和body列创建全文索引
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 ...');
如果要查询哪些文章中包含database关键字，我们可以通过模糊匹配进行查找（没有使用到全文索引）
select * from articles where body like '%database%';
可以用explain工具看一下，是否使用到索引
在SQL语句前面加上explain，可以看到key对应的值为NULL，表示这条SQL在执行过程中没有用到任何索引
如果要通过全文索引来查询，需要使用match against进行搜
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');
在这条SQL语句前面加上explain，可以看到key对应的值为title，表示这条SQL在执行过程中用到了索引名为title的索引
注：MyISAM存储引擎是支持全文索引的，而InnoDB存储引擎是在5.6以后才开始支持全文索引的
索引创建的原则如下：
--------------------- END ----------------------
「 作者 」 枫叶先生「 更新 」 2023.9.1「 声明 」 余之才疏学浅，故所撰文疏漏难免， 或有谬误或不准确之处，敬请读者批评指正。来源地址：https://blog.csdn.net/m0_64280701/article/details/132516434
--结束END--
本文标题: 【MySQL系列】索引的学习及理解
本文链接: https://lsjlt.com/news/392027.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0