9.MySQL索引-EW帮帮网

微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

没有索引, 可能会有什么问题

索引是什么? 想要提高数据库的性能, 索引是非常好的选择, 索引的价值, 在于提高一个海量数据的检索速度.
常见的索引分为:
主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)

我们可以将提高算法效率的因素分为两点:

组织数据的方式(就是以哪种数据结构来组织数据)

算法本身(同样是数组, 有线性遍历, 有二分查找)

索引就是更改以前的数据组织方式, 存数据变为变为了插入到某个特定的数据结构中

案例: 先创建一个海量表, 在查询的时候看看有没有什么问题?

1.在linux的某一目录下, 先创建一个文件index.sql:

-- 构建一个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 ;
 
-- 创建EMP雇员表
CREATE TABLE emp (
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /* 编号 */
    ename VARCHAR(20) NOT NULL DEFAULT "", /* 名字 */
    job VARCHAR(9) NOT NULL DEFAULT "", /* 工作 */
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /* 上级编号 */
    hiredate DATE NOT NULL, /* 入职时间 */
    sal DECIMAL(7, 2) NOT NULL, /* 薪水 */
    comm DECIMAL(7, 2) NOT NULL, /* 红利 */
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /* 部门编号 */
);
 
-- 执行存储过程，添加8000000条记录
CALL insert_emp(100001, 8000000);

2.进入MysQL中某一数据库里, 执行命令source /root/index.sql

等待十几分钟是正常现象

插入完成后, 我们进行查询

可以看到耗时7s多, 这还是mysql只由我一个人来操作, 在实际项目中, 如果放在公网中, 假如同时有1000个人并发查询, 那很可能就死机了
解决方法: 创建索引

可以看到这就是索引的好处, 接下来我们来学习索引之前, 来学习一下索引的预备知识.

认识磁盘–硬件理解

MySQL 给用户提供存储服务, 存储的数据是在磁盘中. 磁盘是计算机中的一个机械设备, 相比于计算机其他电子元件, 磁盘效率是比较低的, 所以如何MySQL的效率是一个重要的话题.

磁盘的物理结构:

如何定位一个扇区呢？
先定位在哪一个磁头---------就能定位在哪个面了
再定位磁道---------磁头来回摆动的时候就是在定位磁道
再定位对应的扇区-----------盘片旋转的时候就是让磁头定位扇区
这种硬件的定位方式叫做：CHS定位法

磁盘的逻辑结构:
将圆形结构抽象成直线----数组的形式

只要知道这个扇区的下标，就可以定位一个扇区了

在操作系统内，我们称这种地址为LBA(Logic Block Address 逻辑块地址)
这里就形成了逻辑扇区地址和物理地址的互相转换-----------LBA<----->CHS

我们现在已经能够在硬件层面定位任何一个基本数据块(扇区)了, 那么在系统软件上, 就直接按照扇区(512字节)进行IO交互吗? 不是

如果操作系统直接使用硬件提供的数据大小进行交互, 那么系统的IO代码就会和硬件强相关, 换言之, 如果硬件发生变化, 系统就必须跟着变化.

单次IO 512字节, IO单位小, 就意味着读取同样的数据内容, 需要进行多次磁盘访问, 会带来效率的降低.

所以之前学习的文件系统, 文件系统读取的基本单位是数据块, 而不是扇区

所以系统读取磁盘, 不会以扇区为单位, 而是以块为单位, 基本单位是4KB.

磁盘随机访问(Random Access)与连续访问(Sequential Access)

随机访问: 本次IO所给出的扇区地址和上次IO给出扇区地址不连续, 这样的话磁头在两次IO操作之间需要比较大的移动动作才能读/写到数据
连续访问: 如果本次IO给出的扇区地址与上次IO结束的扇区地址是连续的, 那磁头就能很快的开始这次IO操作, 这样的多个IO操作称为连续访问.

为什么上面连续多次访问访问, 按理说第一次访问是随机访问, 第二次访问就是连续访问, 访问时间应该很短, 为什么和第一次访问时间一样?

尽管相邻的多次IO操作在同一时刻发出, 但他们的请求扇区的地址相差仍然很大, 所以只能称为随机访问, 而非连续访问.

软件的理解

MySQL与磁盘交互的基本单位

MySQL作为一款应用软件, 为了和文件系统解耦, 可以想象成一种特殊的文件系统. 它有着更高的IO场景, 所以, 为了提高基本的IO效率, MySQL进行IO的基本单位不是4KB, 而是 16KB(后面统一使用InnoDB存储引擎讲解)

16384 = 16 * 1024, 磁盘的硬件设备的基本单位是512字节, 而 MySQL InnoDB引擎使用 16KB进行IO交互, MySQL和磁盘进行数据交互的基本单位是 16KB. 这个基本数据单元, 在MySQL 这里叫做page(注意和系统的page区分)

建立共识(结论)

MySQL中的数据文件, 是以page为单位保存在磁盘当中的, 其中page单位为16KB.

任何对数据进行操作, 都是需要加载在内存中让CPU进行操作, 所以数据操作完后, 数据仍然在内存中, 因为MySQL具有持久化的特点, 会将内存中数据的数据放到磁盘中, 他会以特定的刷新策略刷新到磁盘. 而这时, 就涉及到磁盘和内存的数据交互, 也就是IO了, 此时的IO的基本单位就是page.

为了更高的效率, 一定要尽可能的减少系统和磁盘IO的次数

为什么MySQL和磁盘进行IO交互的时候, 要采用16KB, 而不是1KB, 32KB呢?
如果为1KB, 这必定会导致page中的数据减少, 如果你要找id = 1, 下次要找id = 5, 此时page中就可能只存有你这次要找的id = 1, 而你要再次找 id = 5 就需要又一次加载另一个page. 就增加了IO的次数
如果为16KB, 这必定会导致page中的数据增加, 如果你要找id = 1, 下次要找id = 5, 因为局部性原理, 此时page中就可能既存有你这次要找的id = 1, 又存有你要找的 id = 5, 就不需要又一次加载另一个page. 就减少了IO的次数
如果为32KB, 当然也可以改变为32KB, 但是每次查找1byte的数据的时候, 也需要加载上32KB, 就浪费太多资源了.
所以之所以是16KB得益于其与硬件限制、内存管理、文件系统以及性能表现等多个方面的良好匹配

索引的理解

建立测试表

插入多条记录

我们插入的时候, 并没有按照主键的大小顺序插入, 但是为什么我们查询出来是有序的?

想要搞明白这个问题, 就需要先再次更深入的理解page是什么?
之前讲过, MySQL是以page"数据块"为单位, 与磁盘进行交互. 在MySQL内部, page是如何被定义的呢?
struct page
{
struct page* next; //指向前面的page
string page* prev; //指向后面的page
char buffer[NUM]; //存储数据
…
};

再回答上面的问题, MySQL为了管理好这些数据(如更好的引入页目录), 以这些数据的主键为key, 将上面的数据进行了有序的管理

引入一个概念: 页目录
我现在给了你一个本你从未看过的书, 让你去看某一个章节, 你就需要找到这个章节, 找到该章节由两种做法:

从头逐页往后翻, 知道找到目标内容

查找书的目录, 能快速的定位

本质上, 书中的目录是多花了纸张, 却提高了你查找的效率. 所以目录是一种 “空间换时间” 的做法

在单页情况下是否能够引入页目录呢? 当然可以

现在我们知道了单个page内部是如何的, 我们接下来就需要了解MySQL是如何将多个page进行管理起来的.

这种数据结构就是B+树, 只有叶子节点才存储数据, 非叶子节点只存储目录项, 每次查找的时候, 先在最上层开始查找, 查找的层数就是IO的次数, 一般页目录里存1023个数据, 所以层数很难超过4层, IO次数也就4次左右. 所以这样提高了整体的搜索效率.

表没有主键怎么办, 也会这样构建吗? 也是这样构建的, 会有默认的主键, 会自动生成一个隐藏列充当主键.
那为什么上面查找的时候还是这么慢呢? 是因为你的员工id不是主键, 所以在查找的时候只能在叶子节点中去线性遍历查找, 给员工id加上索引后, 以员工id为主键, 重新又构建了一棵B+树, 搜的时候搜的是这棵新的B+树
叶子节点全部用链表链了起来, 为什么? a.首先这是B+树的特点 b.我们希望能够范围查找, 如你要查找10, 11, 12的数据, 你不可能每一次查找都从B+树顶端开始查找, 而是找到10后, 直接开始遍历, 就可以直接得到11, 12

为什么不选择其他的数据结构?
链表: 线性遍历
二叉搜索树: 退化问题, 可能退化成线性结构
AVL树或者红黑树: 虽然是平衡或者近似平衡, 但是毕竟是二叉结构, 相比较B+树, 层数还是过高了, 大家都是自顶向下找, 层数越低, 意味着系统与磁盘 IO 交互的次数越少
Hash: 官方的索引实现方式中, MySQL是支持Hash的, 不过InnoDB和MyISAM并不支持Hash, 虽然有时候很快O(1), 但并不是一次, 而是常数次, 而且在面对范围查找就明显不行了.

我们主要谈的是InnoDB引擎, 和MyISAM引擎, 这两个引擎是使用的最广泛的.

为什么选择B+树而不选择B树呢?

这两棵树的区别:
B树的节点既有数据又有page指针, 而B+树的节点只有叶子有数据, 其他非叶子节点有键值和page指针
B树叶子节点之间不相连, B+树的叶子节点之间互相连接.

B树

B+树

1. 如果把节点中本来存储目录的数据变为存储数据, 这样一个页目录的目录数据就变少了, 这样就会比B+树更高瘦一些, 所以B+树和B树的区别之一就是B+树IO的IO次数比B树更少
2. B树的叶子节点没有进行相连, 想进行范围查找的时候就必须重新遍历这棵B树. B+树和B树的区别之一就是范围查找的时候, B树比B+树的效率要低

聚簇索引和非聚簇索引

MyISAM存储引擎-主键索引
MyISAM引擎同样使用B+树作为索引结果, 叶节点的data域存放的是数据记录的地址. 下图为MyISAM表的主索引, Col1为主键.

聚簇索引和非聚簇索引是什么?
MyISAM就是非聚簇索引, 将索引Page和数据Page分离, 也就是叶子节点没有数据, 只有对应数据的地址.
InnoDB是聚簇索引, InnoDB是将索引和数据放在一起的.

MySQL除了默认会建立主键的索引外, 我们用户也有可能会以其他列信息建立索引, 一般这种这种索引我们叫做辅助索引.
下图就是基于MyISAM的Col2建立的索引, 和主键索引没有差别, 与主键索引不同的是如果该列有相同的数据, 也可以作为普通索引

下面是以Col3建立的辅助索引如下图:

所以通过辅助(普通)索引, 找到目标记录, 需要两遍索引: 首先检索辅助索引获得主键, 然后用主键到主索引中检索获得记录. 这种过程就叫做回表查询

索引操作

创建主键索引

第一种方式: 在创建表的时候, 直接在字段名后指定 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);

查询索引

第一种方法: show keys from 表名;
第二种方法: show index from 表名;
第三种方法(信息比较简略): desc 表名;

删除索引

第一种方法-删除主键索引: alter table 表名 drop primary key;
第二种方法-其他索引的删除: alter table 表名 drop index 索引名;索引名就是show keys from 表名汇总的Key_name字段
第三种方法: drop index 索引名 on 表名

主键索引的特点:

一个表中, 最多有一个主键索引
主键索引的效率高(因为主键不可重复)
主键索引的列基本上是int

唯一索引的创建

给表添加唯一键约束的时候也会默认给我们构建B+树, 也叫做唯一索引. 所以一个表一般有很多B+树围绕着。

第一种方式: 在表定义时, 在某列后直接指定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);

可以看到这两个表, 已经有两个B+树索引了

删除唯一键索引

唯一索引的特点:

一个表中, 可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引, 必须保证这列不能有重复数据
如果unique上指定not null, 等价于主键索引

唯一键索引也是普通索引, 和普通索引没有任何差别, 只不过是照顾这个表有个unique约束.

普通索引的创建

第一种方式: 在表的定义最后, 指定某列为索引
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);
第三种方式: 创建一个索引名为 idx_name 的索引(同时也创建了这个索引)
create index idx_name on user10(name);

普通索引的特点:

一个表中可以有多个普通索引, 普通索引在实际开发中用的比较多
如果某列需要创建索引, 但是该列有重复的值, 那么我们就应该使用普通索引

复合索引的创建

我们发现后两个的索引名字都是一样的.

用多列构建成的索引叫做符合索引, 说白了就是普通索引, 以前是用一列充当键值, 而复合索引是以两个值放到一起作为键值.
什么时候用这个复合索引呢? 未来我们想高频的通过name找age的时候, 只需要将name和age构建符合索引, 从上往下查找到name的时候, 刚好age就在他旁边, 就不用在回表查询了

全文索引的创建

当对文章字段或有大量文字的字段进行检索时, 会使用到全文索引. MySQL提供全文索引机制, 但是要求存储引擎必须是MyISAM, 而且默认的全文索引支持英文, 不支持中文. 如果对中文进行全文检索, 可以使用sphinx的中文版(coreseek)

查询有没有database数据

可以用explain工具看一下这句话, 是否使用到索引

如何使用全文索引呢?

通过explain来分析这个sql语句是否用到了全文索引