MYSQL高级（中）

站长

2023年07月26日 19:41 · 阅读数 11

第三章 MySQL性能(掌握)

3.1 分析-数据库查询效率低下

我们进入公司进行项目开发往往关注的是业务需求和功能的实现，但是随着项目运行的时间增加，数据量也就增加了，这时会影响到我们数据库的查询性能。所以我们要提高操作数据库的性能，有如下两种方式：

1.硬优化：就是软优化之后性能还很低，只能采取硬优化，最后的步骤了，就是公司花钱购买服务器。在硬件上进行优化。我们在这里不关注。我们关注的软优化。

2.软优化: 在操作和设计数据库方面上进行优化，例如下面讲解的索引。这是我们本课程学习的重点。（重点）

3.2 分析-执行次数比较多的语句

1.执行次数比较多的语句分类

* 1）查询密集型
    我们使用查询频率较高，8：2 左右
        我们就可以使用索引来进行优化

* 2）修改密集型
    在实际开发中修改密集型一般在订单中使用较多，例如用户增加商品，那么订单中的商品数量增加，或者用户修改商品数    量，订单也会修改或者删除等
    现在市面比较火的的是ElasticSearch简称ES.

2.查询累计插入和返回数据条数，即查看当前数据库属于查询密集型还是修改密集型。

-- 查询累计插入和返回数据条数
show global status like 'Innodb_rows%';

说明：Innodb数据一种存储引擎。我们后续会讲解。

MYSQL高级（中）

2.3 查看-sql语句的执行效率

我们想学习mysql的性能问题，这里需要准备千万条数据，这样才可以看出效果。

1.插入千万条记录

直接将如下sql语句复制到可视化工具中执行即可。涉及到的存储过程我们后续讲解。

create database itcast01;

use itcast01;

-- 1. 准备表
CREATE TABLE user(
    id INT,
    username VARCHAR(32),
    password VARCHAR(32),
    sex VARCHAR(6),
    email VARCHAR(50)
);

-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
-- 可以将下面的存储过程理解为java中的一个方法，插入千万条数据之后，在调用存储过程
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION; -- 开启事务
    WHILE(i<=10000000)DO
        INSERT INTO user VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
        SET i=i+1;
    END WHILE;
    COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号

-- 3. 查看存储过程
SHOW CREATE PROCEDURE auto_insert;

-- 4. 调用存储过程
CALL auto_insert();

说明：由于每个人的电脑配置不一样，所以插入千万条数据的时间也是不一样的，有的人是2分钟，有的人十几分钟或者半个小时。

需求：查询id是22的用户。

MYSQL高级（中）大概需要8秒。时间太长了。那么我们需要使用接下来讲解的索引进行优化。

第四章索引(掌握)

4.1 什么是索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

将数据进行排序整理的过程就称为==索引==

我们根据索引去查，提高效率

MYSQL高级（中） MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一

个人力三轮车。

MYSQL高级（中）

4.2 MySQL索引分类

* 主键（约束）索引
        主键约束+提高查询效率
        
* 唯一（约束）索引
        唯一约束+提高查询效率

* 普通索引
        仅提高查询效率

* 组合（联合）索引
        多个字段组成索引

* 全文索引
        solr、es

* hash索引
        根据key-value 效率非常高

说明：

1.我们创建表时就会指定主键和唯一约束，那么就相当于给表的字段添加了主键和唯一索引。

4.3 MySQL索引语法

4.3.1 创建索引

① 在已有表的字段上直接创建【了解】

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引
create unique index 索引名 on 表名(字段);

-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);

-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

说明：

1.如果在同一张表中创建多个索引，要保证索引名是不能重复的

2.上述创建索引的方式比较麻烦，还需要指定索引名

3.采用上述方式不能添加主键索引

【准备的创建表的SQL语句】

create database day04;

use day04;
-- 创建学生表
CREATE TABLE student(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

【给上述表的字段设置索引】

1.给name字段设置普通索引

CREATE INDEX name_idx ON student(name);

2.给telephone字段设置唯一索引

CREATE UNIQUE INDEX telephone_uni_idx ON student(telephone);

设置好之后可以通过图形化工具查看设置的索引：

MYSQL高级（中）

② 在已有表的字段上修改表时指定【了解】

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名：primary

-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名

-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名

【准备的创建表的SQL语句】

-- 创建学生表
CREATE TABLE student2(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

【给上述表的字段设置索引】

1.指定id为主键索引

ALTER TABLE student2 ADD PRIMARY KEY(id);

2.指定name为普通索引

ALTER TABLE student2 ADD INDEX(name);

3.指定telephone为唯一索引

ALTER TABLE student2 ADD UNIQUE(telephone);

设置好之后可以通过图形化工具查看设置的索引：

MYSQL高级（中）

③ 创建表时指定【掌握】

-- 创建学生表
CREATE TABLE student3(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 name VARCHAR(32),
 telephone VARCHAR(11) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 INDEX(name) -- 普通索引
);

MYSQL高级（中）

4.3.2查看索引

show index from 表名;

【查看student3表的索引信息】

show index from student3;

【结果】

MYSQL高级（中）

4.3.3 删除索引

语法

【语法1】直接删除

-- 直接删除
drop index 索引名 on 表名;

【语法2】修改表时删除

-- 修改表时删除
alter table 表名 drop index 索引名;

练习

【1】删除student表的name普通索引

DROP INDEX name_idx ON student;

【2】删除student表的telephone唯一索引

ALTER TABLE student DROP INDEX telephone_uni_idx;

4.4 千万表记录索引效果演示

使用之前创建好的user数据表中的千万条数据进行测试。注意user表中是没有索引的。

MYSQL高级（中） 【1.先来测试没有索引情况下查询】

-- 1.指定id查询
select * from user where id = 8888888;
-- 2.指定username精准查询
select * from user  where username = 'jack1234567';

MYSQL高级（中） 【2.给这2个字段添加索引】

说明：给表的字段添加索引时，底层通过排序方式进行关联组合。所以需要消耗一些时间，并且索引也会占硬盘空间。所以大家使用索引时还需要慎重。

没有添加索引之前，数据占硬盘空间大小：

MYSQL高级（中）按照如下方式给以下字段添加索引

-- 指定id为主键索引
ALTER TABLE USER ADD PRIMARY KEY(id);
-- 指定username为普通索引
ALTER TABLE USER ADD INDEX(username);

MYSQL高级（中）添加索引之后，数据占硬盘空间大小：

MYSQL高级（中）如果在多添加索引，那么占硬盘空间大小还会增加。如果表很复杂，索引加多的话，有可能比原来多几个G也是有可能的。

【3.再测试有索引情况下查询】

-- 1.指定id查询
select * from user where id = 8888888;
-- 2.指定username精准查询
select * from user  where username = 'jack1234567';

MYSQL高级（中）说明：通过以上结果可以看出有了索引之后，查询速度比之前快了几十倍。快的飞起。

4.5 索引的优缺点

优势

1）类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。 2）索引底层就是排序，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间。
在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

4.6 索引创建原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

1. 字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%
	例如：一个表数据只有50行，那么性别和年龄哪个字段适合创建索引，明显是年龄，因为年龄的唯一值个数比较多，性别只有两个选项 。性别的识别度是50%。

2. 经常使用where条件搜索的字段，例如user表的id name等字段。

3. 经常使用表连接的字段（内连接、外连接），可以加快连接的速度。

4. 经常排序的字段 order by，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查 询速度。

* 注意: 那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。因为索引的建立和维护都是需要耗时的 创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的 效率。

4.7 索引的数据结构【了解】

4.7.1 概述

我们知道索引是帮助MySQL高效获取排好序的数据结构。

为什么使用索引后查询效率提高很多呢？

肯定和mysql底层的数据结构有关的，接下来我们就分析下mysql中的索引底层的数据结构。

MYSQL高级（中）每一行数据都对应一个磁盘地址，假设我要想查找数据6，那么如果没有索引，那么内存读取磁盘会进行6次的磁盘IO。

在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址，上述最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。每个存储数据的磁盘片有可能是分开的。

select  * from user where col1=6;

为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

select  * from user where col2=89;

4.7.2 索引的数据结构

数据结构学习网站(美国旧金山大学)

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大

MYSQL高级（中）说明：类似上述数据比较特殊的情况下，那么如果存储在二叉查找树中就会出现类似链表的情况，那么会大大降低查找效率。

红黑树：平衡二叉树（左旋、右旋、变色）

MYSQL高级（中）说明：如果数据结构是红黑树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发访问，那么一个用户有可能需要23次磁盘IO.那么100万用户，那么会造成效率极其低下。所以为了减少红黑树的高度，那么就得增加树的宽度，就是不再像红黑树一样每个节点只能保存一个数据，可以引入另外一种数据结构，一个节点可以保存多个数据，这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。

3.BTree：多路平衡搜索树

MYSQL高级（中）说明：

1.上述数据结构称为BTree,翻译过来是多路平衡搜索树，B 通常认为是Balance的简称。并不是二叉的。

2.一个节点可以有多个元素，按照上述BTree数据结构，第一个节点即根节点含有3个元素。

3.BTree增加了树的宽度，一个节点可以存储多个元素,每个元素由索引、指针域、数据域组成。

4.一个节点大概有16KB大小，不是节点越大越好。假设一个节点占1G，那么高并发时会占大量的服务器内存，反而会降低效率.

5.内存读取硬盘的内容一次读取单位:页(page 每页大小是4KB)

举例：假设读取一个字符a,那么会读取字符a的前后内容，大小是4KB,然后在读取的内容中找到字符a.读取一个字符是4KB,10个字符也会读取4KB.

6.一个元素=索引(bigint 8字节)+指针域(6字节)+数据域(data 假设是1KB).而一个节点大概有16KB大小，这样计算下来一个节点能存储15个元素。那么log以15为底的1000W的对数大概是6左右，这样就一下将原来红黑树的高度23降为6，宽度增加，高度降低就会大大提高查找效率。

7.BTree有效的控制了树的高度，但是还会有如下问题：

1)树的高度还是有点高

2)范围查询磁盘IO次数较多。例如上述数据查找比15索引大的数据，由于15索引对应的元素具有指针域指定，所以到比15大的数据域中进行查找，那么IO次数还是比较多

4.B+Tree：优化BTree（非叶子节点：索引+指针、叶子节点：索引+数据【地址】）

说明：

1.非叶子节点：不是树结构最下面的节点.存储的是：索引+指针域。

2.叶子节点：就是最后一层子节点 , 数据存储在叶子节点。存储的是：所有索引+数据或者数据的地址值

注：

1）不同的存储引擎叶子节点存储的内容不一样，有可能是：索引+数据。也有可能是索引+数据的地址值。

2）B+Tree是可以允许有冗余索引出现的，每个节点都有索引，例如上述索引15，明显是冗余的。

3.非叶子节点都是由索引+指针域组成的，一个索引占8字节，一个指针域占6字节，一个元素大概需要14字节。而一个节点总共容量是16KB.那么可以计算出一个节点可以存储的元素个数：

16*1024字节 / (8+6)=1170个元素

4.对于根节点中可以存储1170个元素，那么根据每个元素的地址值又会找到下面的子节点，每个子节点也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：1170*1170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。

5.对于第二层每个元素有指针域，那么会找到第三层，第三层由索引+数据域组成，假设索引+数据域总大小是1KB.而每个节点一共能存储16KB.所以一个第三层一个节点大概可以存储16个元素即16条记录。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W*16结果就是2000W+的元素个数。

6.结合上述讲解B+Tree我们发现有如下优点：

1）千万条数据，B+Tree可以控制在小于等于3的高度。

2）所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找。

补充：由于叶子节点按照索引已经排好序，每次查找范围的数据不用再像BTree还得回到根节点查找，直接在叶子节点中查找即可.

小结：

B+Tree好处：

1）降低树的高度

叶子节点按照索引排好序，支持范围查找，速度会很快。

3）还有一点是mysql将根节点都加载到内存中，每张表有一个根节点，大小是16KB.那么这样的好处，按照上述如果是千万条数据，那么只有2次磁盘IO.这就是为什么我们加完索引之后瞬间查到数据的原因了。

4.7.3 MySQL中的B+Tree

-- 查看mysql索引节点大小
show global status like 'innodb_page_size';

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的 B+Tree 索引结构示意图：

MYSQL高级（中）

MYSQL高级（中）

第三章 MySQL性能(掌握)

3.1 分析-数据库查询效率低下

3.2 分析-执行次数比较多的语句

2.3 查看-sql语句的执行效率

第四章 索引(掌握)

4.1 什么是索引

4.2 MySQL索引分类

4.3 MySQL索引语法

4.3.1 创建索引

① 在已有表的字段上直接创建【了解】

② 在已有表的字段上修改表时指定【了解】

③ 创建表时指定【掌握】

4.3.2查看索引

4.3.3 删除索引

4.4 千万表记录索引效果演示

4.5 索引的优缺点

优势

劣势

4.6 索引创建原则

4.7 索引的数据结构【了解】

4.7.1 概述

4.7.2 索引的数据结构

4.7.3 MySQL中的B+Tree

第四章索引(掌握)