目录
1. 前置知识
1.1 数据库文件与磁盘介绍
MySQL 给用户提供存储服务，存储的都是数据，而这些数据都需要存储在磁盘中。磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘的 IO 效率是比较低的，那么如果提高效率就是一个重要的话题。
磁盘的介绍可以参考：https://blog.youkuaiyun.com/CSQCSQCC/article/details/145616617。
数据库文件本质就是保存在磁盘盘片上一个个扇区当中的，数据库文件很大就需要占据更多的扇区，所以找到一个文件的全部就是在磁盘上找到所有保存该文件数据的扇区。
在 Linux 系统下，数据库文件一般保存在 /var/lib/mysql 路径下。
1.2 磁盘随机访问和连续访问
随机访问（Random Access）：本次 IO 所给出的扇区地址和上次 IO 结束的扇区地址不连续，磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。
连续访问（Sequential Access）：如果当次 IO 给出的扇区地址与上次 IO 结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。
1.3 MySQL 与磁盘交互基本单位
MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。有着更高的 IO 场景，所以，为了提高基本的IO效率， MySQL 进行 IO 的基本单位是 16KB ( InnoDB 存储引擎)。这个基本数据单位，在 MySQL 中叫做 page。
MySQL 的 CURD 操作都需要计算，也就是需要 CPU 参与，所以一定要先将数据移动到内存中。那么在特定时间内，数据在磁盘中有，内存中也有。完成 CURD 操作后以特定的刷新策略刷新到磁盘上，这时就涉及到磁盘和内存的交互，也就是 IO，此时 IO 的基本单位就是 page。
MySQL 服务器在内存中运行时就申请了一大块内存空间被称为 Buffer Pool，用于将磁盘数据缓存到内存中便于进行 CURD 操作。
为了提高 IO 的效率，就一定要尽可能的减少系统和磁盘 IO 的次数。
2. 索引的理解
2.1 一个现象
先创建一个测试表：
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, '欧阳锋');查询的时候发现默认是有序的：
2.2 理解单个 page
如上图所示，插入的数据量较少时，数据存储在一个 page 当中，不同 page 在 MySQL 中都是 16KB，使用 prev 和 next 构成双链表。因为有主键，MySQL会默认按照主键给我们的数据进行排序。
插入数据时排序的目的就是为了优化查询的效率。要查找 4 号数据时，还是需要进行 4 次遍历，这时候，在一个 page 内部就可以引入目录：
引入目录后再进行 4 号数据的查找时，就先查找目录，找到目录 2，然后在子序列中遍历找到 4 号数据，减少遍历次数，提高查询效率。 所以 MySQL 通过键值进行排序就是方便引入索引，提高查询效率。当数据量较大的时候，就可以有效的提高查询速率。
2.3 理解多个 page
MySQL 中每一页的大小只有 16KB，单个 page 大小固定，随着数据量不断增大，16KB 不可能存下所有的数据，就需要多个页来存储。
在单表数据不断被插入的情况下， MySQL 会在 page 容量不足的时候，自动开辟新的 page来保存新的数据，然后通过指针的方式，将所有的 page 组织起来。
这样在查询的时候，先通过链式遍历到指定的 page，然后再在 page 中通过目录来快速定位。但是，在 page 之间，MySQL 也是需要遍历的，意味着依旧需要进行大量 IO 操作来依次将下一个 page 加载到内存中进行线性检测。
那么解决方法就像之前 page 内部引入目录一样，给 page 页也带上目录。
引入一个目录页来管理页目录，目录页中的每一项（目录项）存放的就是指向每一个 page 中最小的数据的指针。查询的时候就可以通过比较数据找到要访问的 page，然后在 page 内部也是通过目录的方式快速定位到要查询的数据。
目录页本质上也是页，普通页中存的数据时用户数据，而目录页中存的数据是普通页的地址。
加上了目录页之后，虽然可以减少大量 IO，但是如果数据量是特别的大，这时候还需要遍历目录页中的目录项来找到对应的 page。那么就还可以在目录页上再加一层目录页，来管理下面的目录页。
目录页：页中数据为 page 目录的页。
页目录：为一个 page 创建的目录，页目录就是目录页中的一项数据。
如上图就是表的索引结构，也就是B+数，非叶子节点不存储数据，叶子节点存储数据并且，每一个叶子节点也通过链表连接起来。
在 MySQL 中，索引是一种特殊的数据结构（如B+树或哈希表），用于快速定位和访问表中的数据。合理设计索引可以显著提升查询效率，但错误的索引设计可能导致性能下降。索引本质也是一种通过空间来换时间的方式提高查询效率的方式。
知识点1：
为什么非叶子节点不存储数据？因为非叶子节点不存储数据，就可以存储更多的目录项，可以使得树更矮，减少 IO 操作次数。知识点2：
为什么叶子节点要相连？叶子节点相连便于进行范围查找，因为如果要查询一个返回内的数据，则只需要查找到首数据和尾数据，然后通过链表提取范围中的所有数据即可。如果没有链表相连，则范围中的每一个数据都需要通过树形结构进行查询。
数据结构演示链接：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
2.4 聚簇索引和非聚簇索引
在 MySQL 中，聚簇索引（Clustered Index）和非聚簇索引（Non-Clustered Index，也叫做二级索引或辅助索引）是两种重要的索引方式。
聚簇索引：聚簇索引不是一种单独的索引类型，而是一种数据存储方式。数据行直接存储在索引的叶子节点中，即索引和数据是一体的，一个表只能有一个聚簇索引（通常是主键），因为数据只能按一种顺序存储。优点就是查询效率高，通过索引直接能获取到一行数据，无需通过主键值回表查询。缺点就是插入速度依赖于插入顺序，随机插入数据可能会导致页分裂，性能下降。
非聚簇索引：索引和数据分离存储，索引叶子节点存储的是主键值，而非完整数据。查询时需要二次查找，即先通过索引找到主键值，再通过主键值到聚簇索引中获取数据（回表）。优点是灵活，可以根据不同查询需求创建多个索引，插入和更新操作高效，不影响数据存储顺序。缺点就是查询时若字段不在索引中，需要二次查询聚簇索引，并且每个非聚簇索引都需要独立的存储空间。
2.5 索引的优化原则
（1）选择合适的字段：高频用于查询的字段，具有唯一性的字段，更新不频繁的字段。
（2）复合索引的最左前缀原则：如复合索引 (name, age)，查询的时候查询条件可以为如下 where name='SMITH' 或者 where name='SMITH' and age=20。如果只使用 age 字段进行查询如 where age=20，则没有使用到索引。
（3）避免冗余索引：若已存在索引 (a, b)，则无需再创建索引 a。
（4）覆盖索引：当索引包含所需查询出的字段，可以通过索引返回结果，无需查到索引之后再回表查询字段。
3. 索引操作
3.1 主键索引
一个表中，最多只能有一个主键索引，当然该主键索引也可以是复合索引，主键索引的效率高（主键不重复且值不能为 null，并且主键索引的列基本上是 int 类型）。
（1）创建主键索引。
方式1：
-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
方式2：
-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));
方式3：
create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);
（2）查询索引。
方法1：show keys from 表名；
方法2：show index from 表名;
如上图，查询到表 user3 的索引信息，Key_name 字段表示索引的名称，Column_name 字段表示是以哪一个字段建立的索引，Index_type 表示的是索引的数据结构，其中 BTREE 就是B+树。
show index from user3 和 show key from user3 查询的结果一样。
（3）删除主键索引。
alter table 表名 drop primary key;
3.2 唯一索引
一个表中，可以有多个唯一索引，如果在某一列建立索引，必须保证这列不能有重复数据，如果一个唯一索引上指定 not null，等价于主键索引。
（1）创建唯一索引。
方法1：
-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);
方法2：
-- 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));
方法3：
-- 创建表后，添加unique唯一属性。
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);
通过索引查询，可以知道建立了一个唯一索引，默认索引名字为列名称。
（3）删除唯一索引和其他索引。
方法1：
alter table 表名 drop index 索引名;
方法2：
drop index 索引名 on 表名;
3.3 普通索引
一个表中可以有多个普通索引，如果某列需要创建索引，但是该列有重复的值，就可以创建普通索引。
（1）创建普通索引。
方法1：
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);
方法2：
create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引
方法3：
create table user10(id int primary key, name varchar(20), email
varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);
3.4 全文索引
当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。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;上述创建全文索引，指定 title 列和 body 列。 这种指定多个列的索引也叫做复合索引。
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 ...');插入对应数据，如下图：
例：查询 body 中有 database 的数据行。
上述只是简单的模糊查询，没有使用到全文索引。可以使用 explain 工具查看是否使用到索引。
上述 key 字段为 NULL 表示没有使用到索引。
上图中的语句是使用全文索引进行查找的语句。
3.4.1 全文索引和 like 的对比
2708
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
