Mysql 进阶（索引优化锁）

存储引擎

• 连接池：主要是用什么语言去连接

mysql 体系是分层的

1. 连接层：接收客户端的连接，以及认证授权和安全方案，是否超过最大连接数。
2. 服务层：SQL 接口，解析器，查询优化器，缓存。
3. 引擎层：可插拔式的存储引擎，控制数据存储和提取方式。
4. （index是索引在引擎层）（innoDB是5.5版本之后默认的引擎）
5. 存储层：存储数据库的相关数据。包含一系列的日志，错误日志，慢查询日志。

存储引擎就是存储数据，建立索引，更新/查询数据等技术实现方式，是基于数据库表的而不是数据库，也称之为表类型。

show create table user

执行完上面的代码我们会发现，最后有个InnoDB默认引擎，和下一个自增是24，字符集utf-8信息和排序方式，所以我们创建表的时候不指定是有默认的引擎的。

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sect_name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

查询数据库支持的存储引擎

show engines 

• MyISAM 早期版本的默认引擎

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	支持事务、行级锁定和外键	YES	YES	YES
MRG_MYISAM	YES		NO	NO	NO
MEMORY	YES	基于哈希，存储在内存中，对临时表很有用	NO	NO	NO
BLACKHOLE	YES		NO	NO	NO
MyISAM	YES		NO	NO	NO
CSV	YES		NO	NO	NO
ARCHIVE	YES		NO	NO	NO
PERFORMANCE_SCHEMA	YES		NO	NO	NO
FEDERATED	NO		null	null	null

引擎的特点(InnoDB)

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎。

• 特点：DML操作遵循ACID，支持事务
• 行级锁，提供并发访问性能
• 支持外键约束，保证数据的完整性和正确性。

那存储的文件是什么样子呢

• xxx.ibd xxx是表名

每张表都对应这样的表空间文件，存储表空间的（from和sdi）数据和索引--参数是innodb_file_per_table

查询是否开启，默认打开，表示每张表对应一个表空间

show variables like 'innodb_file_per_table' 

在mysql的根目录下的data文件夹中存储的每个数据库，里面的idb文件

虽然不能直接打开，但是可以用cmd提取

ibd2sdi 提起的文件名

会显示很多JSON内容

    "dd_object": {
        "name": "stu", // 
        "columns": [ // 有哪些字段
            {
                "name": "id", // 字段1，id
                "type": 4,
                "is_auto_increment": true, // id是否自增
            },
            {
                "name": "age", // 字段2，age字段 
            },
            {
                "name": "name",
            }

InnoDB 逻辑存储结构

1. TavleSpace 表空间中包含多个 Segment 段
2. Segment 段 中包含多个Exgent区（大小固定1M）
3. Exgent区中包含多个Page页（大小固定16K）
4. Page页中包含多个Row行（磁盘操作的最小单元）
5. 行里面包含
   • Trx ID 最后一次操作的ID
   • Roll pointer 指针
   • coll 字段

根据上面可以看出一个区可以包含64个页

MyISAM 引擎

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

文件

• xxx.sdi 存储表结构信息
• xxx.MYD：存储数据
• xxx.MYI：存储索引

它的文件可以直接打开的是JSON

Memory 引擎

数据存放在内存中，会受到影响，只能作为缓存和临时表使用

• 内存存放，hash索引（默认）
• xxx.sdi：存储结构信息

总结

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持（5.6版本之后）	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
支持外键	支持	-	-

存储引擎的选择

• InnoDB：适用于对事务的完整性有比较高的要求，并发条件下要求数据一致
• MyISAM：读操作和插入操作为主，少量的更新和删除，可以使用，例如：评论足迹（被NOSQL替代，MonngDB）
• MyMory：所有的数据都在内存中，对表的大小有限制，无法保证数据安全性（被redis取代）

centos7 卸载mysql

1. 检查是否安装了mysql

yum list installed | grep mysql

1. 将查出来的名称，复制出来删除掉，多个的空格隔开

yum remove 名称1 名称2

1. 查询有没有其他的

rpm -qa | grep -i mysql

1. 存在就删除

rpm -e --nodeps mysql-community-libs-5.7.22-1.el7.x86_64

1. 查找文件，并删除

find / -name mysql
rm -rf /usr/lib64/mysql

1. 删除mysql的数据库文件和配置文件

sudo rm -rf /var/lib/mysql/
sudo rm /etc/my.cnf

centos7 安装mysql，并设置权限

1. 添加MySQL官方存储库】

sudo rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm

1. 安装MySQL服务器

sudo yum install mysql-community-server

1. 启动mysql服务

sudo systemctl start mysqld

1. 配置MySQL服务自启动

sudo systemctl enable mysqld

1. 检查MySQL服务状态

sudo systemctl status mysqld

1. 获取初始密码

sudo grep 'temporary password' /var/log/mysqld.log

1. 设置默认密码,必须包含大小写特殊符号

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

1. 设置密码安全等级

set global validate_password.policy = 0;

1. 设置密码安全长度

set global validate_password.length = 4;

1. 重置密码看第七条
2. 创建一个用户让本机访问数据库

create user 'root'@'%' identified with mysql_native_password by '1234';

1. 给 刚刚的用户设置权限为所有

grant all on *.* to 'root'@'%';

查看centos7 ip地址

ip addr show

1. 开放3306端口，允许外主机访问

// 开放3306
sudo firewall-cmd --add-port=3306/tcp --permanent
// 使修改生效
sudo firewall-cmd --reload

索引

索引就是帮助mysql高效的获取数据的数据结构（有序）

• 默认是没有索引的，也称之为全表扫描，性能极低
• 有索引，通过二叉树等算法快速查找

优势	劣势
提高数据检索效率，降低数据库的IO成本	索引也是占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引大大提高了查询的效率，但是也降低了表的增删改的速度，因为需要维护结构

索引结构

mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hahs索引	底层数据结构是用哈希表实现的，只有精确匹配索引，不支持范围查询
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text 全文索引	是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene，Solr，ES

支持情况

索引	InnoDB	MyISAM	Memory
B+Tree 索引	支持	支持	支持
Hash 索引	不支持	支持	支持
R+Tree 索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

B-Tree（多路平衡查找树）

以一颗最大度数（max-degreee）为5（5阶）的b-tree为例

1. 每个节点最多存储4个key，5个指针
2. 一旦元素达到5个，中间元素向上分裂
3. 非叶子节点和叶子节点都会存储数据

B树演变规则

B+Tree

所有的元素都会出现在叶子节点上

绿色框起来的部分仅仅起到索引作用，不存储数据，红色框起来的部分进行存储数据

叶子节点存储数据，非叶子节点仅仅起到索引作用

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

Hash 结构

哈希索引就是采用一定的Hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在Hash表中。

1. 他会把每一行加个Hash地址
2. 然后把name字段单独提取出来，设定一个Hahs地址
3. 根据名称找到Hash地址里面就是这一行的ID
4. 出现Hash碰撞会跟HashMap一样，变成链表形式

特点：

1. Hash只能用于对等（= ， in），不支持范围查询（between， >，<）
2. 无法利用索引完成排序操作
3. 查询效率高，通常一次即可，效率通常高于B+Tree索引（因为Hash碰撞的原因）

存储引擎支持 支持hash索引的是Memory引擎，而InnoDB中具有自适应的Hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

为什么InnoDB 存储引擎选择使用B+Tree索引结构？

1. 相对二叉树，层级更少，搜索效率高。
2. 对于B+Tree，无论叶子节点和非叶子节点，都可以保存数据，这样导致一页中的存储键值减少，指针跟着减少，要同样保存大量数据，只能增加高度，导致性能降低。
3. 相对Hash索引，B+Tree支持范围匹配及排序操作

分类	含义	特点	关键词
主键索引	针对于表中主键创建的索引	默认自动创建，只有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引		全文索引查找的是文本中的关键词，而不是比较索中的值	可以有多个

索引结构

分类	含义	特点
聚集索引（Clustered Index）	将数据存储与索引放一块，索引结构的叶子节点保存了行数据	只有一个，必须有
二级索引（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以有多个

聚集索引选取规则：

• 如果存在主键，主键就是聚集索引
• 如果不存在主键，将使用第一个唯一（Unique）索引作为主键
• 如果表没有主键，也没有唯一索引，则InnoDB会自动创建一个rowid作为隐藏的聚集索引

聚集索引的B+Tree下面挂的是这一行的数据 二级索引下面挂的是这一行所对应的ID

1. mysql查询方式：先去二级索引查找对应的ID
2. 再进行聚集索引查询行数据（检查回表查询）

创建索引

查看当前表索引

show index from tb_user

创建常规索引

规范：使用 idex_表名_字段名 on 表名（要给那个字段加）

create index idx_user_name on tb_user (name)

注意：InnoDB 默认索引结构就是 B+Tree

创建唯一索引（unique）

create unique index idx_user_phone on tb_user(phone);

创建联合索引（括号要写多个就是联合索引）

就是一个索引key对应多个字段

create index idx_user_pro_age_status on tb_user(profession, age, status);

删除索引

• idx_user_email 为key键

drop index idx_user_email on tb_user

SQL 性能分析（慢查询日志）

第一点：SQL执行频率（主要是查询的比例） 查看当前数据库语句执行的数量（七个下划线哦~）

show global status like 'Com_______'

第二点：慢查询日志，定位sql语句

1. 慢查询日志会记录超过指定参数的sql语句
2. 默认没开启，默认会认为执行超过10秒的sql为慢查询

查看慢查询状态

show variables like 'slow_query_log'

开启慢查询日志：

1. windows 永久开启慢查询日志
2. slow_query_log = ON // 打开
3. slow_query_log_file = D:\mysql-8.0.24-winx64\slow.log // 日志路径
4. long_query_time = 5 // 超时时间

在mysql根目录新建my.ini文件

[mysqld]
port=3306
basedir=D:\mysql-8.0.24-winx64
datadir=D:\mysql-8.0.24-winx64\data

    slow_query_log = ON
slow_query_log_file = "D:\mysql-8.0.24-winx64\log\slow.log"
long_query_time = 5

在linux里这样配，/etc/my.cnf

slow_query_log = 1 // 打开开关
long_query_time = 5

show variables like '%slow_query_log%' 可以查看更多信息，log文件存放的路径

默认，/var/lib/mysql/主机名-slow.log

会记录：

1. 当前通过那个用户在那个连接上来的
2. 执行时间
3. 锁了多少行
4. 返回多少记录
5. 用的数据库
6. 操作时间
7. 执行的sql语句

SQL 性能分析（profile分析）

查询是否支持profile分析

select @@have_profiling

查看是否开启，默认是关闭的

select @@profiling

开启分析

set profiling = 1

查询执行过的sql语句执行时间

show profiles

有个queryID字段，可以执行查看语句执行哪里影响了性能

show profile for query 1; // 查询第一条

show profile for query 1; // 查询第一条

show profile cpu for query 1; // 查询第一条包含cpu信息

explain 执行计划分析

可以获得语句执行过程中，如何连接以及连接顺序

直接在 select 前面加 explain / desc 效果都一样

explain select * from tb_user;

1. table：操作的表
2. partitions：分区
3. type：连接的类型
4. possible_keys：可能用到的索引
5. key：实际用到的索引
6. key_len：索引的长度
7. rows：扫描记录数
8. Extra：额外信息

先讲讲 ID 的作用：sql语句当中表的执行顺序 如果ID相同则从上往下执行，如果不同值越大越先执行

说下id值不同的时候怎么执行，一般子查询的id值是大的，先执行子查询再执行本条语句

第二个是select_type表示查询类型（不重要）

1. SIMPLE：简单表，未室友表连接或者子查询
2. PRIMARY：主查询，外层查询
3. UNION：union后面的查询语句
4. SUBQUERY：表示包含子查询或者连接查询

第三个 type，表示连接类型

性能由好到差：NULL、system、const、eq_ref、ref、range、 index、all

• null是不可能的，因为我们必须访问表
• system：访问系统表
• const：用的是唯一索引或者主键
• ref：非唯一
• index：用了索引，但是会遍历整个索引

Key_len：表示索引使用的字节数，并非实际长度，不损失精度情况下，越短越好 rows：Mysql认为的查询行数，在innodb引擎中是一个估计值，不准确 filtered：返回结果的行数百分比，如果主键查询就是百分百，越大越好

验证索引

在一千万数据下，查询一个字段我用了8秒，sn非主键

select * from tb_sku where sn = '100000003145001'

为sn字段添加普通索引，耗时35秒，innodb 默认B+tree

create index idx_skuq_sn on tb_sku(sn);

再次执行，发现耗时 38毫秒

最左前缀法则（联合索引使用）

使用了联合索引，遵守最左前缀法则，查询从索引最左列开始，不能跳过索引列

记住，是先profession，后age，后state，那么使用索引的时候，就要遵循这些规则，不能跳过使用

create index idx_user_pro_age_sta on tb_user1(profession, age, status)

现在下面这个，使用到了三个字段，完全具备最左前缀法则

（调换位置也能,因为是判断存不存在，而不是位置）

explain select *
from tb_user1
where profession = '软件工程'
  and age = 31
  and status = '0';

查看执行计划发现长度 key_len 是54

把最后的state条件去掉，发现 key_len 49

把最后的age条件去掉，发现 key_len 47

如果中间跳过了某个字段，比如没有age字段，直接就state，像这种情况，就是跳过字段那么，后面的state不走索引，前面的走吗，当然如果缺少最前面的pro字段，那么将全部不走索引

说下范围查询

这个 >= 对于state就会使用索引，如果 > 就不会 < 也同理

explain select *
from tb_user1
where profession = '软件工程'
  and age >= 31
  and status = '0';

索引失效情况

1. 下面这种情况，phone字段是存在索引的，由于使用了函数运算，索引失效

explain select *
from tb_user1 where substring(phone,10,2) = '15'

1. 字符串类型不加单引号，phone是字符串，索引失效

explain select *
from tb_user1 where substring(phone,10,2) = 15

1. like 模糊匹配，前面百分号在前面或者前后都有，索引失效

explain select *
from tb_user1 where profession like '%软件';

1. or 连接失效，用or连接必须两侧字段都存在索引，否则全部失效，下面age无索引

explain select *
from tb_user1 where id = 10 or age = 23

1. Mysql评估索引不如全表扫描

• 下面这个扫描的数据是表的一半多，没有走索引

explain select *
from tb_user1 where phone >= '17799990011';

1. is not null 和 is null 是根据mysql评估的不一定不使用索引

sql提示（指定使用索引）

一个字段是可以设置多个索引，联合索引和单列索引（会走联合）

建议mysql使用指定索引（use）

explain select * from tb_user1 use index(idx_user_pro) where profession = '软件工程'

忽略指定索引（ignore）

explain select * from tb_user1 ignore index(idx_user_pro) where profession = '软件工程'

必须使用指定索引（force）

explain select * from tb_user1 force index(idx_user_pro) where profession = '软件工程'

回表查询

• 回表查询：就是查询到了字段，但是有些字段，不在覆盖索引中，就需要回表查询其他字段信息

上述第三个例子中，where name = 'arm'

走辅助索引去查询arm字段，查询到了之后，覆盖索引只有arm和id的信息，并不包含gender字段，所以就要去回表查询

除非建立一个所有字段的联合索引。

如何为以下sql语句最优：建立一个username和password的联合索引。返回这些字段不需要回表

select id,username,password from tb_user where username = 'itcast';

前缀索引（适合大篇文章--text，varchar）

这样查询文章重复率，一定要是1

select count(distinct email) / count(*)
from tb_user1

判断前缀，前十个字符

select count(distinct substring(email, 1, 10)) / count(*)
from tb_user1

创建索引，只要判断email的前五个字符

create index idx_user_email_5 on tb_user1 (email(5))

我们查看索引会发现，只要前五个

看下流程：

1. 先走辅助索引查找前五位的一样的节点，是个链表
2. 查找到，获取ID，然后根据情况是否进行回表操作
3. 拿到这行数据，刚刚只是比较了前缀，现在需要当前值进行全文判断，最后返回

单列索引与联合索引

• 单列索引：一个索引包含一列
• 联合索引：一个索引包含多列

有以下sql语句，phone和name都是单列索引，并不是联合的，只使用到了phone索引，没有name

explain select id,phone,name
from tb_user1 where phone = '17799990010' and name = '韩信'

联合索引的顺序，基本一致，最左前缀法则要根据情况排序，有一定影响

索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引
2. 针对于常作为查询条件（where，order by， group by）操作字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引效率越高，比如状态和性别列
4. 如果是字符串类型的字段，字段长度较长，可以针对字段特定建立前缀索引
5. 尽量使用联合索引，较少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6. 要控制索引数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，影响增删改效率。
7. 如果索引列不能存储null值，建表时候使用not null 约束它，mysql优化器就知道每列是不是包含null值。利于查询

思考题：InnoDb 主键索引的 B+Tree高度为多高啊

数据存放在叶子节点的，叶子节点形成双向链表，主键索引是聚集索引， 聚集索引叶子节点是行数据， 在B+树的索引结构中，每一个节点最终落在磁盘上，存放在一个页中， 一个页是16K，一个节点只能存储是有限的， 计算高度：一行数据是1K，一页就只能存放16行，而InnoDB指针占用6个字节是固定的，而Key占用的字节是根据类型计算的，如果是bigint占用字节数是8

我们估算一下，如果树的高度为2，可以存储多少容量呢 我们先来计算一下，非叶子节点他能存储多少个key多少个指针呢

因为非叶子节点，不存储数据，只存储key和指针，指针永远比key多一个，我们就能列出公式

n * 8 + ( n + 1 ) * 6 = 16 */ 1024 ,算出 n 约为 1170 8 是主键占用的字节数，主键是 bigint 所以 是8 n 就是当前节点存储的key数量 n+1 就是 指针，因为指针永远比 key 多一个 6 指针占用的空间是6个字节 16 * 1024 因为 一页的大小是16K，那么就应该 16 * 1024 个字节

1171 * 16 = 18736

如果高度为 3 那就是加一个 1171 就等于 1171 * 1171 * 16 = 21939856

SQL优化

插入数据优化，大批量数据

第一种方式

要批量插入，不用重复开启事务关闭事务（500-1000条是合适的）

第二种方式

手动提交事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

第三点：主键顺序插入，比乱序快（看主键优化）

大批量数据如何插入？

先看下文件结构，左边每个逗号分隔都是一个字段，对应右边的

• 并不一定是逗号，还可以是其他符号

window 报错路径找不到--解决了，必须用下面的路径 / 不能是 \

load data local infile '../tb_sku4.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

1. 首先连接数据库

mysql --local-infile -u root -p

1. 设置全局参数 local_infile = 1，开启从本地加载数据的开关

set global local_infile = 1

1. 加载数据--记得选择数据库和创建相应的表格

load data local infile '路径' into table `表名` fields terminated by '分割条件' lines terminated by '一般\n一行一条数据';

主键优化

看下，主键顺序插入

页可以为空，也可以填充一半，也可以填充百分百，每个页包含2-N的数据，会行溢出。

还有一种，主键乱序插入（页分裂）

1. 如果页满了，会创建新的页
2. 把数据放到新的页，然后数据进行重写分配
3. 将指针重新分配，一致之后完成

页合并

当删除一行记录时，实际上并没有被物理删除，只是被标记了删除，且空间允许被其他记录声明使用。

当页中删除记录达到当前页的百分之五十时，innoDB会开始找相邻的页，也就是前后页，是否可以将两个页进行合并以优化空间使用。

下面的图二页可以和三页进行合并

注意：页合并的阈值是可以在创建索引和建表的时候设置的，默认百分之五十

主键设计原则：

1. 满足业务逻辑的情况下，尽量降低主键的长度。

在二级索引有很多，在二级索引的叶子节点中挂着都是数据的主键，主键长度比较长，二级索引比较多，将会占用大量的磁盘空间，在搜索的时候耗费大量的磁盘IO。

2.尽量选择顺序插入，选择Auto自增主键

选择乱序插入导致页分裂现象

1. 尽量不要使用UUID做主键或者其他自然主键，如身份证。

长度比较长，检索耗费大量的磁盘IO

1. 业务操作，避免对主键修改

order by优化

1. Using filesort：通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完全排序操作，不是通过索引直接返回的排序结果都叫 FileSort。
2. Using index：通过有序索引顺序扫描直接返回有序的索引，这种情况using index，不需要额外排序效率高（推荐，性能高）

下面的语句执行后，发现 Extra 位置出现了 Using filesort（效率低）

explain select id, age, phone
from tb_user1 order by age,phone

我们加入索引之后，就是 Using index了

create index idx_user_age_phone on tb_user1(age,phone)

explain select id, age, phone
from tb_user1 order by age,phone

那么我们倒序排序呢，Extra 出现了这个，表示反向扫描，但是效率也是很高

Backward index scan; Using index

还有一种可能，排序先后和创建联合索引时顺序不一致，有影响Extra（Using index; Using filesort）

explain
select id, age, phone
from tb_user1
order by phone, age

age升序和phone倒序会发生什么呢

Using index; Using filesort 我们发现 age 是 index 而 phone 是 filesort

explain
select id, age, phone
from tb_user1
order by age, phone desc

原因其实很简单，因为我们创建索引时，并没有指定排序默认是 asc，解决方案就是创建索引时指定好

a 就是升序，d 倒序

create index idx_user_age_asc_phone_desc on tb_user1 (age asc, phone desc)

全部倒序，也是最优哦

以上使用前提条件：不需要回表查询

我们在使用过程中无法避免出现 Using filesort，在大数据量的情况下，可以适当增加缓冲区的大小（默认256K）

查看缓冲区大小

show variables like 'sort_buffer_size'

那如何修改呢

1. 打开MySQL配置文件my.cnf
2. 查找并编辑sort_buffer_size参数。 如果该参数不存在，则需要手动添加一行sort_buffer_size=size，其中“size”是您要设置的新缓冲区大小，通常建议设置为64M或128M（如果您的服务器的内存足够大）。

group by 分组优化

查询下面sql执行计划，发现是用到了临时表，性能比较低（Using temporary; Using filesort）

explain select profession, count(*)
from tb_user group by profession

下面这个不满足最左前缀法则----没有按照创建索引时的前后来

explain select age, count(*)
from tb_user group by age

像下面这样用到了索引列也是走索引

explain select age, count(*)
from tb_user where profession = '软件工程' group by age

limit 优化

有个场景的问题，limit 2000000, 10 ，此时排序前 2000010 记录仅仅返回 2000000 - 2000010 记录，代价非常大

• 所以我们先把 id 查出来，根据id查出来信息，但是并不支持此语法

select *
from tb_sku
where id in (select id
             from tb_sku
             limit 2000000,10);

只需要这样就可以，使用子查询，效率高

select s.*
from tb_sku s, (select id
              from tb_sku
              limit 2000000,10) a where a.id = s.id;

count 优化 & update 优化

• MyISAM 引擎把一个表的总行存在了磁盘上，因此执行 count(*) 时效率很高
• InnoDB 引擎需要把一行一行的从引擎里读出来，然后计数（麻烦）

用法：count(*) count(字段) count(1)

判断是不是null，不是null +１

优化思路：自己计数，比如redis

1. count(主键)：遍历整个表，把主键取出来，返回服务层
2. count(字段)：没有非空约束，就遍历表，把每一行字段取出来，判断是否不为空，不为空返回给服务层。
3. count(1)：遍历表，不取值，不做判断，直接返回给服务层
4. count(*)：专门做的优化，不取值，服务层直接累加

update 优化

当我们更新数据的时候，条件如果不是索引字段，将会进行表锁。 行锁是快的，表锁并发性能将会降低

视图 / 存储过程 / 存储函数 / 触发器

视图

是一种虚拟存在的表，使用视图时动态生成的，视图只存储SQL逻辑，不保存查询结果。

创建视图

• create or replace view stu_d1：创建或者替换一个视图后面跟sql逻辑（）

视图是可以当作表一样用的

create or replace view stu_d1 as
select id, name
from student
where id <= 10

修改视图

修改视图只需要加上or replace就可以了，不加就是创建

第二种方式

alter view stu_d1 as select id, name, no
                     from student
                     where id <= 20

删除视图

if exists 是可选项--不存在不删除

drop view if exists stu_d1

检查选项

当我们对视图插入数据时，由于视图不会保存数据，直接交给基类了插入到表格里

insert into stu_d1(id, name)
values (6, 'tom')

但是如果这个视图只查询小于等于20的数据，用视图插入一个id = 30的数据，会不会成功呢，答案是会的，所以需要用检查选项来检查。

当我们加上 with cascaded check option 表示开启了检查

• 通过视图检查更改的每个行，插入删除更新符是否符合视图的定义
• CASCADED 和 LOCAL 默认 CASCADED
• CASCADED：表示不仅仅检查当前视图，如果当前视图用到了其他视图，也会检查是否满足其他视图
• local: 它的递归检查视图判断又没有添加检查约束，如果基类有检查就做检查

下面的语句会执行失败

create or replace view stu_d1 as
select id, name
from student
where id <= 20 with cascaded check option

演示一下 CASCADED

现在有一个视图，没有任何检查约束，查询 id <= 20 的数据

再创建一个视图，查询大于等于10的，加上检查约束

create or replace view stu_d2 as
select id, name
from stu_d1
where id >= 10
with cascaded check option

那么现在我们使用 stu_d2 插入id = 8的数据时，会检查当前视图是否满足条件，肯定是插入失败啊。

再插入 id = 25 的，会检查视图里面使用到的 stu_d1 视图，发现也不满足规则，这就是 CASCADED

如果当前视图没用检查约束，可是基类有检查约束，那么会成功吗？

这是不能的，只要依赖（基类）视图有约束，就会进行判断。不管当前视图有没有

视图更新 / 作用

视图的行与基础表的行必须存在一对一关系。

视图包含以下任何一项，则不能更新

1. 聚合函数或窗口函数
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或 UNION ALL

下面代码创建一个视图用来查询总数，用到了聚合函数，所以在插入数据时会报错

也不是行一一对应了

create view v5 as select count(*) from student

insert into v5 value (10)

作用

简单：视图不仅可以简化用户对数据的理解，也可以简化他们操作, 不必每次都指定全部条件

安全：数据库授权，但是不能授权到特定行和特定的列上，通过视图用户只能查询和修改他们所能见到的数据

数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响

案例：查询每个学生所修的课程---封装到视图中

create view stu_course as
select student.no, student.name, course.name  course_name
from student
         left join student_course stu_course on student.id = stu_course.studentid
         left join course on stu_course.courseid = course.id

存储过程

存储过程就是将一些 sql 语句封装起来，存储到mysql中，需要使用的时候直接调用该集合即可

p1 里面封装了查询修改的一些操作。

特点：

• 封装，复用
• 可以接收参数，也可以返回数据
• 减少网络交互，效率提升

创建存储过程

create procedure 参数名称()
begin
    select count(*) from student;
end;

调用存储过程

call 参数名称()

查看存储过程

第一种方式----test为数据库名称

select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test'

第二种方式指定存储过程

show create procedure 参数名称

在结果里面有一个 Create Procedure

它的内容是创建存储过程的语法

• DEFINER=root@localhost：表示登录用户

CREATE DEFINER=`root`@`localhost` PROCEDURE `参数名称`()
begin
    select count(*) from student;
end

删除存储过程

drop procedure if exists 参数名称

有个小细节，在命令行中是无法正常使用的，他遇到分号就自动结束了。需要使用 delimiter 指定结束符号

/*修改结束符号*/
delimiter $$
#创建 sql 存储过程
create procedure 参数名称()
begin
    select count(*) from student;
end$$

系统变量

系统变量：是 mysql 服务器提供的，不是用户自定义的，属于服务器层面。

分为 全局变量（GLOBAL）和会话变量（SESSSION）

查询系统变量

查看全部系统变量---没有加级别默认是SESSION级别

show variables
# 查看全局的系统变量
show global variables

使用like模糊匹配

show global variables like 'auto%'

准确查看某个系统变量的值

select @@autocommit
# 全局的
select @@global.autocommit

设置系统变量

set session autocommit

set @@[SESSSION | GLOBAL] 系统变量名 = 值

细节：设置全局的系统变量，服务器重启后还是会重置默认参数

用户变量

用户根据自己需要定义的变量，不需要提前声明，直接用@变量名即可，作用域为当前连接

赋值操作

# = 赋值
set @myname = 'itcast'
# := 赋值
set @myage := 10
# 一次赋值多个变量
set @mygender := '男', @myhobby := 'java'

# select 赋值
select @mycolor

将sql结果赋值给变量

select count(*) into @myCount from student

推荐使用 := 这样可以区分 比较 = 和赋值 = 的区别

查看变量

select @myname, @myage, @mygender, @myhobby

如果该变量不存在，返回 null

局部变量

根据需要在局部生效的变量，访问之前需要 declare 声明，可用在存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 BEGIN ... END 块

create procedure p2()
begin 
    # 局部变量声明在 begin end 中， 用 declare 声明
    # default 是可选参数表示默认值，类型 int
    declare stu_count int default 0;
    # 赋值 也可以用 set
    select count(*) into stu_count from course;
    select stu_count;
end;

IF 条件判断 / 存储过程参数定义

参数定义类型	含义
in	作为输入参数，也就是调用时传入值（默认）
out	作为输出，可以作为返回值
inout	既可以作为输入，也可以作为输出

create procedure p3(in/out/inout 参数名 参数类型)
begin

end;

IF 条件1 THEN ..... ELSEIF 条件2 THEN -- 可选 ..... ELSE -- 可选 ..... END IF;

create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then # 执行下面的语句
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

# 需要传递变量，第一个为输入参数，第二个为用户自定义变量
call p4(68, @result)
select @result

例子 inout 使用：

# 将传入的200分制进行换算换算成百分制，然后返回分数
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

set @score = 78; # 必须先赋值
call p5(@score); # 进行调用，并返回
select @score

在存储过程中是可以使用 case when 的形式的，类同

存储过程 while 循环 / repeat 循环 / loop 循环

while 循环是先判断条件

create procedure p6(in n int)
begin
    declare total int default 0;
    # while 循环后跟条件
    while n > 0
        do # 开始
            set total := total + n;
            set n := n - 1;
        end while; # 结束
    select total;
end;


call p6(10)

repeat 先执行一次再进行判断，满足条件退出循环

create procedure p7(in n int)
begin
    declare total int default 0;

    repeat
        set total := total + n;
        set n := n - 1;
    until n <= 0 # 如果 n <= 0 结束循环
    end repeat;
    select total;
end;


call p7(10)

loop 循环

实现简单的循环，如果sql中没有增加退出循环条件，将是死循环

• leave：配合循环使用，退出循环
• iterate：跳过当前循环，进行下一次

语法：

标记：loop
    sql 逻辑
end loop 标记

leave 标记 # 退出循环
iterate 标记 # 直接进入下次循环

跳出指定循环---累加方式

create procedure p9(in n int)
begin
    declare total int default 0;

    sum: # 定义标记
    loop
        if n <= 0 then
            leave sum; # 跳出指定的循环
        end if;
        set total := total + n;
        set n := n - 1;
    end loop;

    select total;
end;

call p9(100)

跳过循环---只累加偶数

create procedure p10(in n int)
begin
    declare total int default 0;
    sum:
    loop
        if n <= 0 then
            leave sum; # 如果小于等于 0 就退出循环
        end if;
        if n % 2 = 1 then # 如果是奇数减1，并跳出当前循环
            set n := n - 1;
            iterate sum;
        end if;
        # 否则就累加
        set total := total + n;
        set n := n - 1;

    end loop;
    select total;
end;

call p10(10)

游标

在下面，把一行的数据直接赋值给 stu_count 是不可以的，因为定义的变量只能接收单列单行的数据

create procedure p11()
begin
    declare stu_count int default 0;
    select * into stu_count from student;
    select stu_count;
end;

所以就要使用游标来封装结果集

声明游标

# 游标一定在局部变量的下边，否则会报错
declare 游标名称 cursor for 查询语句;

开启游标

# 开启游标
open 游标名称;

获取游标的记录数据---这个顺序是根据查询语句的返回顺序

# 从游标的字段开始赋值给局部变量，一定要按照顺序
fetch 游标名称 into 局部变量, 局部变量;

关闭游标

# 关闭游标
close 游标名称;

目前下面的这条 sql 是会报错的，但是数据是会插入成功的。

报错原因是因为 结果集在遍历的时候，发现没有数据了，但是while循环还在执行。

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);

    # 游标一定在局部变量的下边，否则会报错
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;

    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro
    (
        id         int primary key auto_increment,
        name       varchar(100),
        profession varchar(100)
    );

    # 开启游标
    open u_cursor;

    while true
        do
            # 从游标的字段开始赋值给局部变量，一定要按照顺序
            fetch u_cursor into uname, upro;
            # 插入数据操作
            insert into tb_user_pro values (null, uname, upro);
        end while;
    # 关闭游标
    close u_cursor;
end;


call p11(30)

条件处理程序

可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤 （也叫拦截异常）

语法：

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;

handler_action 的取值：

• CONTINUE: 继续执行当前程序
• EXIT: 终止执行当前程序

condition_value 的取值：

• SQLSTATE sqlstate_value: 状态码，如 02000
• SQLWARNING: 所有以01开头的SQLSTATE代码的简写
• NOT FOUND: 所有以02开头的SQLSTATE代码的简写
• SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;

    # 当遇到 02000 错误时候，执行关闭游标,不建议用下面的方式
    # declare exit handler for sqlstate '02000' close u_cursor;
    declare exit handler for not found close u_cursor;
    drop table if exists tb_user_pro;

    create table if not exists tb_user_pro
    (
        id         int primary key auto_increment,
        name       varchar(100),
        profession varchar(100)
    );

    open u_cursor;
    while true
        do
            fetch u_cursor into uname,upro;
            insert into tb_user_pro(name, profession) values (uname, upro);
        end while;
    close u_cursor;
end;

call p11(30)

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是 in 类型的。

语法：

create function 存储函数名称(参数列表)
    returns 类型 characteristic
    begin
        -- SQL 语句
        return ...;
    end;

characteristic

• deterministic：相同的输入参数总是产生相同的结果
• no sql：不包含 SQL 语句
• reads sql data：包含读取数据的语句，但不包含写入数据的语句

例子：从1累加到 n

create function fun1(n int) -- 因为 只能是 in类型，可以省略
    returns int deterministic -- 设置返回值类型(deterministic 表示描述，8.0需要)
begin
    declare total int default 0;

    while n > 0
        do
            set total := total + n;
            set n := n - 1;
        end while;
    return total; -- 返回 total
end;

在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定 characteristic特性，否则就会报如下错误

触发器

触发器是与表有关的数据库对象，指在 insert / update / delete 之前或之后触发并执行触发器定义的 SQL 语句集合。触发器这种特性可以确保数据的完整性，日志记录，数据校验等操作。

使用别名 old 和 new 来引用触发器发送变化的记录内容，现在触发器只支持行级触发，不支持语句级触发。

• 行级触发器：执行一个语句，影响了n行，触发器执行5次
• 语句级触发器：不管影响几行，我只执行一次

触发器类型	NEW 和 OLD
insert 型触发器	new 表示将要或者已经新增的数据
update型触发器	old 表示修改之前的数据，new 表示将要或者已经修改后的数据
delete型触发器	old 表示将要或者已经删除的数据

语法

create trigger 触发器名称
    before / after
    insert / update / delete
    on 表名
    for each row -- 行级触发器
    begin 
        -- 触发器逻辑
    end;

查看触发器

show triggers 

删除触发器--如果没有指定数据库，默认当前数据库

drop trigger 触发器名称

新增操作触发器，在之前触发--例子

它这个after表示在提交到数据库之前触发，并不是新增执行之前

新增tb_user的时候，就会往log表新增数据

create trigger tb_user_insert_trigger
    after insert
    on tb_user -- 在 tb_user 执行了增删改之后触发
    for each row
begin
    insert into user_logs(operation, operate_time, operate_id, operate_params)
    -- new 表示新增好的数据，直接拿到新增的数据（也就是tb_user的新增）
    values ('insert', now(), NEW.id, concat('新增了name=', NEW.name));
end;

修改的触发器--用到了 old获取旧数据

create trigger tb_user_update_trigger
    after update
    on tb_user
    for each row
begin
    insert into user_logs(operation, operate_time, operate_id, operate_params)
        -- new 表示新增好的数据，直接拿到新增的数据（也就是tb_user新增的）
    values ('update', now(), NEW.id, concat('原数据name=', OLD.name, '最新数据：', NEW.name));
end;

删除不再演示了，删除是不能用NEW的因为已经删除了

总结：

1. 视图：虚拟表，简单，安全，数据独立
2. 存储过程：减少网络交互，提高性能，封装重用
3. 存储函数：存储函数可以被存储过程替代
4. 触发器：在增删改的之前和之后触发一些操作

锁 / 表级锁 / 行级锁

锁是多个进程或者线程并发访问某一资源的机制，用来保证数据的一致性，有效性。

• 全局锁：锁定数据库中的所有表
• 表级锁：每次操作锁住整个表
• 行级锁：每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例处于只读状态，后续 DML 写语句，DDL语句，都将会被阻塞。

其典型的使用场景就是全库逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据库的完整性。

就是防止在备份的时候对数据库表格进行操作，导致备份的不是最新的数据

在全局锁时只能读不能写

1. 加全局锁
2. 备份
3. 释放全局锁

演示全局锁的使用

加锁---加上之后其他客户端只能进行查询，其他操作将会阻塞

flush tables with read lock;

进行数据备份

mysqldump -h localhost -u root -p 数据库名 > D:/BaiduNetdiskDownload/123.sql

释放锁

unlock tables;

特点：

在数据库中加全局锁，是一个比较重的操作、

• 如果在主库上备份，那么在备份期间不能执行更新，业务基本停摆
• 如果在从库备份，那么备份期间不能执行主库同步过来的二进制日志，会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致 性数据备份。

mysqldump --single-transaction -u root -p sam > D:/BaiduNetdiskDownload/234.sql

那么是如何实现一致性的呢

在备份期间，所有的写事务都将被暂时阻塞，直到备份结束，才恢复执行

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

对于表锁

1. 表共享读锁（read lock）
2. 表独占写锁（write lock）

语法：

读锁：只能读，不会阻塞其他客户端的读，但是阻塞它们的写
写锁：只能写

加锁：read / 读锁，write / 写锁

读锁演示

lock tables 表名... read/write
lock tables course read; -- 给course加读锁
-- 加了读锁，无论那个客户端都不能写入数据，只能读入
-- 注意：其他客户端如果进行写入，会进入阻塞状态
-- 当前客户端是不能进行写操作的，会报错

释放锁：unlock tables / 客户端断开连接 。

-- 释放锁
unlock tables;

写锁演示

当前客户端，我既能够读，也能够写 其他客户端不能读，也不能写，会进入阻塞状态

-- 对 course 加写锁
lock tables course write;

表级锁---元数据锁

MDL加锁过程是系统自动控制的，无需手动加锁，访问一张表的时候，元数据锁会自动加上，主要作用是维护表数据的一致性，表中有活动事务存在的时候，不能对表结构进行修改。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变 更操作的时候，加MDL写锁(排他)。

对应SQL	锁类型	说明
lock tables xxx read /write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select ... lock in share mode	SHARED_READ	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert 、update、delete、select ... for update	SHARED_WRITE	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table ...	EXCLUSIVE	与其他的MDL都互斥

查询和修改都是共享锁，都是兼容的。两个客户端都可以

在另一个客户端事务未提交时，另一个客户端在进行修改事务，是会堵塞的。因为加上排他锁了。与其他锁互斥。

查看元素数据锁的信息

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

每当有锁进行的时候，都会显示在里面。

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行 数据是否加锁，使用意向锁来减少表锁的检查。

首先看下流程

1. 开启事物，对一张表执行DML操作时，使用主键字段，对涉及的该行加行锁
2. 另一个客户端在进行加表锁的时候，会依次检查每一行是否有表锁，没有则添加表锁（效率极低）
3. 所以 InnDB在添加行锁时会添加意向锁
4. 其他客户端在加表锁时通过意向锁判断是否可以添加表锁

• 意向共享锁(IS)：由语句select ... lock in share mode添加。与表锁共享锁 (read)兼容，与表锁排他锁(write)互斥。
• 意向排他锁(IX)：由insert、update、delete、select...for update添加。与表锁共 享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

查看数据锁定信息

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

演示 开启事物，查询一个语句，并加行锁的共享所，并为这个表加意向共享锁

begin;
select * from data6_5 where id = 10 lock in share mode;

查询加锁记录（8版本）

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

• record：表示行锁
• table：表示表锁
• object_schema: 那个数据库
• object_name：那个表
• lock_mode：锁类型
• IS：意向共享锁

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
iom	data6_5	NULL	TABLE	IS	NULL
iom	data6_5	PRIMARY	RECORD	S,REC_NOT_GAP	10

思考：这个时候为这个表加表读锁会成功吗

为什么成功：因为表读锁和IS意向共享锁是兼容的，和写锁是互斥的。

我们看下拍他锁和表锁的兼容情况

开启事物，执行下面语句的时候，会自动加上行锁和意向排他锁

update data6_5 set EMS = '小米' where id = 1013133;

再次查看加锁情况，发现有个IX，表示意向排他锁，再执行下面语句，加读锁或者写锁都会堵塞

lock tables data6_5 read;

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB存储引擎中。

• InnoDB的数据是基于索引组织的
• 行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持。

其中，repeatable-read 是 RR可重复读，read committed 是RC，读已提交

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

什么是间隙，就是6-12之间的间隙。不包含该值。

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。

行级锁---先说行锁

InnoDB实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。（与共享锁兼容，与排他锁互斥）

排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

假如一个事务获取到了某一行的排他锁，此时它可以更新数据，其他事务将不能获取共享锁和排他锁。

当前锁类型	S（共享锁）	X（排他锁）
S（共享锁）	兼容	冲突
X（排他锁）	冲突	冲突

常见 SQL 执行加锁情况

SQL	行锁类型	说明
INSERT ...	排他锁	自动加锁
UPDATE ...	排他锁	自动加锁
DELETE ...	排他锁	自动加锁
SELECT（正常）	不加任何锁
SELECT ... LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

演示一下

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

当我们开启事务，执行下面sql时，发现并没有加锁（下面的ID是有索引的）

select * from stu where id = 1;

加锁是这样的

select * from stu where id = 1 lock in share mode;

再查询加锁情况--只看lock 不等于table的，不看意向锁

lock_type = record：行锁 lock_mode = S,REC_NOT_GAP：共享锁

多个客户端，因为共享锁兼容共享锁，是可以进行查询同一条数据的

对加共享锁的行执行增删改是不可以的，进入阻塞状态

多个客户端进行修改同一个数据，由于加了排他锁，其他客户端将会堵塞。

如果条件没有索引，将会将会进表锁。表的任何行都不能进行修改。

间隙锁&临键锁

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

案例1：例子（唯一索引）

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。

修改一条不存在的数据，没有 id = 5

begin;
update stu set age = 10 where id = 5;

查看加锁情况会发现

lock_mode = X,GAP

表示排他锁，GAP 是间隙锁的意思

lock_data = 8

表示锁的是 id 在 8 之前的，然后看上一条数据，是 3 那么就是 3-8

注意：这个锁住了，我们去插入一条 id = 7的数据，那么会不会堵塞呢。答案是会的。

insert into stu values(7,15,'lube');

案例2：非唯一索引 / 普遍索引

索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

什么意思呢？就是在B+Tree中，叶子节点存储的双向链表，根据二级索引18查询，我们加了共享锁。锁住18，可能会在前后插入18，向右边遍历找到不满足条件的时候，将18~不满足条件的，锁住。以及18之前的添加间隙锁。

我们执行下面的语句

begin;
select * from stu where age = 3 lock in share mode;

看 lock_mode 和后面的lock_data

• 第一个IS是意向锁
• 第二个S表示共享锁 | 3,3 表示锁住的前部分，从3到3
• 第三个S，REC_NOT_GAP 表示锁住这一行，没有间隙
• 第四个S,GAP 表示间隙锁，8-8

大致意思就是，从3行到8行的间隙锁住

案例3：范围索引（唯一索引）

索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

执行下面的语句。

begin;
select * from stu where id >= 19 lock in share mode;

1. 首先会加一个意向锁
2. 然后把当前19行锁住
3. 第三个锁住了25 到 正无穷之间的间隙（25，+∞）
4. 最后一个是25，25（临键锁）及之前的间隙

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

意思就是：间隙锁只会阻止插入数据

InnoDB 引擎

逻辑存储结构 / 架构

InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

先看内存架构

• Buffer Pool：缓冲池
• Change Buffer：更改缓冲区
• Adaptive Hash Index：自适应哈希索引
• Log Buffer：日志缓冲区

磁盘架构

也就是每次创建表，都会为该表创建独立的表空间文件。

创建通用表空间语法

create tablespace 表空间名字 add datafile '磁盘文件名称.ibd' engine = innodb;

创建表的时候就可以指定表空间了

create table word(
id int primary key auto_increment,
name varchar(10)
) engine = innodb tablespace 表空间名称

撤销表空间，在磁盘中通常为两个分别是，undo_001和 undo_002

后台线程

分为四类：

1. Master Thread

核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收 。

1. IO Thread

在 InnoDB 存储引擎中大量使用了 AIO 来处理 IO 请求，这样可以极大地提高数据库的性能，而 IO Thread主要负责这些 IO 请求的回调。

线程类型	默认个数	职责
Read thread	4	负责读操作
Write thread	4	负责写操作
Log thread	1	负责将日志缓冲区刷新到磁盘
Insert buffer thread	1	负责将写缓冲区内容刷新到磁盘

查看Innodb状态信息，里面包含线程类型等

show engine innodb status \G

1. Purge Thread

主要用于回收事务已经提交了的 undo log，在事务提交之后，undo log 可能不用了，就用它来回收。

1. Page Cleaner Thread

协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

事务原理

• 其中的原子性、一致性、持久化，实际上是由 InnoDB 中的两份日志来保证的
• 一份是 redo log 日志，一份是 undo log 日志
• 而持久性是通过数据库的锁，加上MVCC来保证的。

事务原理--redo log（一致性，持久性）

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：

• 重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）
• 前者是在内存中，后者在磁盘中
• 当事务提交之后会把所有修改信息都存到该日志文件中
• 用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

那我们分析一下：如果没有 redolog 会存在什么问题呢

脏页：在更新数据时，数据不在缓冲区中，通过后台线程将数据从磁盘中读取，放到缓冲区中，在进行修改，之后称为脏页

• 在 innodb 引擎中，主要的内存区域就是缓冲池
• 在缓冲池中缓存了很多的数据页
• 当我们在一个事务中，执行多个增删改的操作时
• innodb会先操作缓冲池的数据
• 如果没有数据就会通过线程加载本地的数据，再次修改之后
• 脏页就会在一定的时机将数据刷新到磁盘中
• 假如刷新磁盘的时候出错了，提示给用户成功了，那么就没有保证事物的持久性

那么有了 redo log 是如何解决的呢

• 当对数据进行增删改之后，会先将操作的数据页的变化
• 记录在 redo buffer 中，当对事物提时，会将 redo buffer 缓冲区的数据写入磁盘文件中
• 如果刷新缓冲区的脏页发生错误了，可以借助 redo log 进行数据恢复
• 如果涉及到的数据已经落盘或者成功刷新，就可以删除了，所以 redo log 文件是循环写的。

那为什么每一次提交事务，要刷新 redo log 到磁盘中呢，而不是直接将buffer pool 中的脏页刷新到磁盘呢 ?

• 在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘
• 而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的
• 顺序写的效率，要远大于随机写
• 这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

事务原理---undo log（原子性）

回滚日志，用于记录数据被修改前的信息

作用包含两个：提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制)

• undo log 和 redo log 记录物理日志不一样，它是逻辑日志
• 可以认为当delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然
• 当 update 一条记录时，它记录一条对应相反的 update 记录（也就是修改之前的信息）
• 当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚

MVCC 多版本并发控制

当前读： 保证我们读取的的记录都是最新的，读取时不能被其他事务修改当前记录。共享锁和排他锁都是一种当前读。

演示当前读

首先开启事务，查询数据

begin;
select * from student;

此时，在另一个客户端修改当前的记录

update student set name = '维纳' where id = 1;

那么再次查询数据会变成最新的吗？显然不是

那要实现当前读，就可以查询最新的数据了

select * from student lock in share mode;

快照读：

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

• Read Committed：每次select，都生成一个快照读。
• Repeatable Read：开启事务后第一个 select 语句才是快照读的地方。（默认）
• Serializable：快照读会退化为当前读。

MVCC：全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为 MySQL 实现 MVCC 提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log 日志、readView。

隐藏字段 / undo log 版本链

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合 undo log，指向上一个版本
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

undo log 版本链

• 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志
• 当insert 的时候，产生的 undo log 日志只在回滚时需要，在事务提交后，可被立即删除
• 而 update、delete 的时候，产生的 undo log 日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

有一张表原始数据为：

每个事务执行时，都会记录 undo log 日志，记录数据变更之前的样子，然后更新记录，并且记录本次操作的事务 ID，回滚指针，回滚指针用来指定如果发送回滚，回滚到哪一个版本。

最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog 生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readview（读视图）

是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id

ReadView中包含了四个核心字段：

字段	含义
m_ids	当前活跃的事务ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id	ReadView创建者的事务ID

不同的隔离级别，生成ReadView的时机不同：

• READ COMMITTED：在事务中每一次执行快照读时生成ReadView
• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

读视图---RC 级别 / RR 级别

RC

• 首先根据记录去依次判断条件是否满足
• 用 DB_TRX_ID（最近修改的事务id） 去比较创建事务的ID，是否满足第一个条件
• 然后再用最近修改事务的ID去比较活跃最小的id
• 然后再用最近修改的事务ID去比较最大活跃的id
• 再用最小活跃的id去比较最近的修改事务id，并且 最近修改事务id不在最近集合中

RR

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。而RR 是可 重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。

实现方式：

隔离性：MVCC + 锁

• 原子性：undo log
• 持久性：redo log
• 一致性：undo log + redo log
• 隔离性：MVCC + 锁
• MVCC：记录隐藏字段 + undo log版本链 + readView

MYSQL 管理

系统数据库

Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：

数据库	含义
mysql	存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
information_schema	提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
performance_schema	为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys	包含了一系列方便 DBA 和开发人员利用
performance_schema	性能数据库进行性能调优和诊断的视图

mysql

• db 表：存储性能指标的一些系统
• slave_master_info
• slave_relay_log_info
• slave_worker_info：这三个都是主从复制的一些信息
• slow_log：慢日志
• time_zone
• time_zone_leap_second
• time_zone_name
• time_zone_transition
• time_zone_transition_type：时区操作的相关信息
• user：用户表

information_schema

• ENGINES：引擎表
• INNODB_TABLES：innodb引擎的表有哪些
• INNODB_TABLESPACES：innodb 表空间
• TABLESPACES：表空间
• SCHEMA_PRIVILEGES：数据库的权限
• TABLE_PRIVILEGES：表权限

再看一下视图

• ROUTINES：存储过程和存储函数
• SCHEMATA：数据库的说明信息等
• TABLES：数据库表的信息
• TRIGGERS：触发器
• VIEWS：视图

performance_schema

• data_locks：加锁情况
• metadata_locks：元数据锁情况
• binary_log_transaction_compression_stats：二进制日志信息
• error_log：错误日志信息
• 还有事务相关的信息，事件信息

sys：主要是跟性能相关的视图

常用工具

该mysql不是指mysql服务，而是指mysql的客户端工具 语法： mysql [options] [database] 选项：-u，--user=name #指定用户名            -p，--password[=name] #指定密码            -h，--host=name #指定服务器IP或域名            -P，--port=port #指定连接端口            -e, --execute=name #执行SQL语句并退出

-e 选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。

mysql -uroot -p1234 test -e "select * from course"

mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

• mysqladmin --help：查看帮助文档

其中 Usage: mysqladmin [OPTIONS] command command....表示语法

command 表示多个命令

往下滑，会看到很多命令语法

演示一下

# 查看版本号
mysqladmin -uroot -p1234 version
# 创建数据库
mysqladmin -uroot -p create isa

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。 语法：mysqlbinlog [options] log-files1 log-files2 ... 选项：-d，--database=name 指定数据库名称，只列出指定的数据库相关操作。            -o，--offset=# 忽略掉日志中的前n行命令。            -r,--result-file=name 将输出的文本格式日志输出到指定文件。            -s, --short-form 显示简单格式，省略掉一些信息。            --start-datatime=date1            --stop-datetime=date2 指定日期间隔内的所有日志。            --start-position=pos1            --stop-position=pos2 指定位置间隔内的所有日志。

日志文件在：cd /var/lib/mysql 下

扩展名是：000008这种形式的都是

在linux中，直接查看会乱码，所以需要用该工具

命令：mysqlbinlog 文件名

mysqlbinlog -s 文件名 -- 简化格式

mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。 语法：mysqlshow [options] [db_name [table_name [col_name]]] 选项：--count 显示数据库及表的统计信息（数据库，表 均可以不指定）            -i 显示指定数据库或者指定表的状态信息

例子

#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count 

可以查询这个数据库的所有表，以及每个表对应的总记录数

#查询test库中book表的详细情况,可以一直在后面跟字段,查看详细的信息
mysqlshow -uroot -p2143 test book --count
# 比如看 test 数据库的 tb_user 表的id字段
mysqlshow -u root -p1234 test tb_user id  --count
# 查看 test tb_user 的状态，表的信息信息
mysqlshow -u root -p1234 test tb_user -i

mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。 语法：mysqldump [options] db_name [tables]            mysqldump [options] --database/-B db1 [db2 db3...]            mysqldump [options] --all-databases/-A 连接选项：-u， --user=name 指定用户名                    -p，--password[=name] 指定密码                    -h, --host=name 指定服务器ip或域名                    -P, --port=# 指定连接端口 输出选项：--add-drop-database 在每个数据库创建语句前加上 drop database 语句                    --add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)                    -n, --no-create-db 不包含数据库的创建语句                    -t, --no-create-info 不包含数据表的创建语句                    -d --no-data 不包含数据                    -T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

# 备份 sam 数据库，输出到 db01.sql
mysqldump -uroot -p sam > D:/working/vscode/db01.sql
# 仅仅备份数据
mysqldump -uroot -p sam -t > D:/working/vscode/db02.sql
# 将db01数据库的表的表结构与数据分开备份(-T)
mysqldump -uroot -p1234 -T /root db01 score

这里我们往 root 路径备份---是会报错的

原因是因为我们所指定的数据存放目录/root，MySQL认为是不安全的，需要存储在MySQL信任的目录下。

# 可以查看MYSQL 信任目录
show variables likes '%secure_file_priv%'
这个txt 是按照指定格式创建的

mysqlimport/source

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。

语法：mysqlimport [options] db_name textfile1 [textfile2...] 示例 ： mysqlimport -uroot -p2143 test /tmp/city.txt

如果需要导入sql文件,可以使用mysql中的source 指令 语法 ： source /root/xxxxx.sql