DML 等术语概念

DML（Data Manipulation Language，数据操纵语言）： DML主要用于插入、更新、删除和查询数据库中的数据。常见的DML语句包括：

• INSERT：用于向表中插入新的数据行。
• UPDATE：用于修改表中的数据。
• DELETE：用于删除表中的数据行。
• SELECT：用于从表中查询数据。

DDL（Data Definition Language，数据定义语言）： DDL主要用于创建、修改和删除数据库中的表结构。常见的DDL语句包括：

• CREATE：用于创建数据库、表、视图等。
• ALTER：用于修改数据库、表、视图等结构。
• DROP：用于删除数据库、表、视图等。

DCL（Data Control Language，数据控制语言）： DCL主要用于控制不同数据的访问权限。常见的DCL语句包括：

• GRANT：用于授予用户操作权限。
• REVOKE：用于撤销用户操作权限。

TCL（Transaction Control Language，事务控制语言）： TCL主要用于管理数据库中的事务。常见的事务控制语句包括：

• COMMIT：用于提交事务，即将事务中的所有操作永久保存到数据库中。
• ROLLBACK：用于回滚事务，即撤销事务中的所有操作。
• SAVEPOINT：用于在事务中设置一个保存点，可以回滚到该保存点。

事务

事务四大特性：
原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性(Consistency)：开始之前和事务结束之后，数据库的完整性约束（如主键约束、外键约束、唯一约束等）都必须得到满足。
隔离性(lsolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

分别对应中文：读未提交、读已提交、可重复读、可串行化

脏读、幻读和不可重复读是数据库事务处理中可能出现的三种并发控制问题，它们都与事务的隔离级别和并发执行有关。

**脏读（Dirty Read）**发生在当一个事务读取了另一个尚未提交的事务的修改数据。具体来说，当事务A正在修改数据但还没有提交时，事务B读取了事务A尚未提交的修改数据。如果事务A最终回滚了它的修改，那么事务B所读取的数据就是无效的或“脏”的。脏读可能导致数据的不一致性和错误的结果。

**不可重复读（Non-repeatable Read）**是指在一个事务内，多次读取同一数据，但由于其他事务的修改导致每次读取的数据可能不同。例如，事务A首先读取了某个数据项的值，然后事务B修改了该数据项并提交，当事务A再次读取该数据项时，发现其值已经改变。不可重复读破坏了事务的一致性，因为同一个事务中的多次读取可能得到不同的结果。

**幻读（Phantom Read）**是指在一个事务内，同样的查询条件下，多次执行查询得到的结果集不一致。这通常发生在其他事务向表中插入或删除了符合查询条件的记录。例如，事务A执行了一个查询，返回了某些记录，然后事务B插入了一条新的记录，这条新记录符合事务A的查询条件。当事务A再次执行相同的查询时，会发现多了一条新的记录，就好像出现了“幻影”一样。

存储引擎

默认innodb存储引擎

索引

索引结构

B+Tree

(左小右大) 中间元素向上分且在叶子节点依旧保留并形成单向链表

引索分类

答：第一条sql效率高，因为第一条sql直接走聚集索引，第二条sql走二级索引之后再回表查询

引索语法

#查看当前表的索引
show index from student;#创建常规引索（索引命名规范：idx_{{引索字段名}}）
create index idx_student_name on student(name);#创建唯一引索(该列的值不能重复)
create unique index idx_student_phone on student(phone);#创建联合索引
create index idx_id_name_phone on student(id, name, phone);#删除索引
drop index idx_student_phone on student;

性能分析

一般在开发环境下开启慢查询，线上环境关闭慢查询，因为开启慢查询会损耗一部分musql的性能
Windows慢查询日志：LAPTOP-DHDL2KHM-slow.log

profile很少用

#查询是否开启profiling（检查慢查询的工具，0表示未开启，1表示已开启）
select @@profiling;#开启profiling
set global profiling=1;#查询执行的sql语句时间
show profiles;#查询id为584的执行具体时间（for爆红是idea问题）
show profile  for query 584;

 

索引使用

如果最左边的列不存在，引索失效，与sql语句的where条件顺序无关

可以在业务允许时使>=符号

答：建立username和password的联合索引

  索引设计原则

SQL优化

数据插入优化

主键优化

order by 优化

group by 优化

limit 优化

覆盖索引：指一个索引不仅能够满足查询条件，还能够覆盖查询结果中所有需要的数据，从而避免对表进行访问的一种索引。（查询的所有字段都在创建的索引字段中）

count 优化

updata 优化

注意：更新操作要根据索引更新，不然行锁变表锁，期间其他的用户就不能更新，削弱并发能力

SQL优化总结

视图

虚拟存在的表，不保存查询结果，只保存查询的SOL逻辑、简单、安全、数据独立

/**视图的基本简单使用创建、修改、删除、查询、数据的增删该*/-- 一会使用my_student作为基表
select * from my_student;-- 创建修改换视图，将my_student表中性别为男的逻辑体提取出来，封装成视图
-- or replace 可选关键字，当视图存在时，先删除，再创建
create or replace view my_student_view as select * from my_student where gender = '男';-- 查询视图
select * from my_student_view;-- 修改视图
-- 方式一：使用or replace关键字
create or replace view my_student_view as select * from my_student where gender = '女';
-- 方式二：
alter view my_student_view as select * from my_student where gender = '女';-- 删除视图，if exists：如果视图不存在，不报错
drop view if exists my_student_view;-- 向视图插入数据：该数据其实是写到基表my_student表中
insert into my_student_view values(7, '张无忌', 20, '男', '1');-- 删除数据
delete from my_student_view where id = 7;-- ---------------------------------------------------
/**检查选项 cascade: 级联检查，如果视图1没有加检查选项，视图2基于视图1创建的视图并加了检查选项，那么操作视图1也会检查（相当于视图1也加了检查）检查选项 local: 局部检查，如果视图1没有加检查选项，视图2基于视图1创建的视图并加了检查选项，那么操作视图1时先检查视图1，再检查视图2（如果视图1没有检查选项，那么不检查，有则检查）如果视图2是cascaded，那么又会继续检查视图2的基表...*/-- 创建一个基表为视图的视图并加上检查选项cascaded，数据操作时检查
create or replace view my_student_view2 as select * from my_student_view where id < 30 with cascaded check option;-- 向视图插入数据：如果检查发现不符合视图创建的条件，就会报错，不让插入
-- my_student_view2的id < 30 检查条件、my_student_view 的gender = '女' 检查条件 同时检查满足才能插入
insert into my_student_view2 values(29, '张无忌', 20, '女', '1');-- 创建一个基表为视图的视图并加上检查选项local，数据操作时检查
create or replace view my_student_view3 as select * from my_student_view2 where id > 20 with local check option;-- 只检查 my_student_view3 条件
insert into my_student_view3 values(21, '张三丰', 20, '女', '1');

存储过程

介绍

事先定义并存储在数据库中的一段SQL语句的集合，减少网络交互，提高性能、封装重用

基本语法

如果在命令行中去执行创建语句要注意 ";" 号，命令行中会把 ";" 识别为结束符 

-- 创建一个统计行数的存储过程
create procedure p1()
beginselect count(*) from my_student;
end;-- 调用
call p1();-- 查看存储过程
-- 方式一：根据存储过程名查看
select * from information_schema.routines where routine_name = 'p1';
-- 方式二：根据数据库名查看
select * from information_schema.routines where routine_schema ='study';
-- 方式三：
show create procedure p1;-- 删除存储过程
drop procedure if exists p1

变量

系统变量：

 用户变量：

局部变量：

关键字if cursor while...

-- if语句，入参是score，int类型；出参是result，varchar类型
create procedure p3(in score int, out result varchar(10))
beginif score >= 85 thenset result := '优秀';elseif score >= 60 thenset result := '及格';elseset result := '不及格';end if;
end;

-- case语句
create procedure p5(in score int)
begindeclare result varchar(10);casewhen score >= 85 and score <=100 thenset result := '优秀';when score >= 60 thenset result := '及格';when score < 60 and score >=0 thenset result := '不及格';elseset result := '非法分数';end case;select concat('您的分数为：', score, '，等级为：', result);
end;

-- while循环语句，计算从1累加到n的和，n为入参
create procedure p6(in n int)
begindeclare total int default 0;while n > 0 doset total := total + n;set n := n - 1;end while;select concat('累加和为：', total);
end;

-- repeat循环语句，计算从1累加到n的和，n为入参
create procedure p7(in n int)
begindeclare total int default 0;repeatset total := total + n;set n := n - 1;until n <= 0  -- 退出条件end repeat;select concat('累加和为：', total);
end;

-- loop循环语句搭配leave和iterate，计算从1累加到n的奇数和，n为入参
create procedure p8(in n int)
begindeclare total int default 0;sum: loop  -- 给循环起一个名字sumif n <= 0 thenleave sum;  -- 退出循环end if;if n%2 = 1 thenset n := n - 1;iterate sum;  -- 跳过本次循环，进入下一次循环end if;set total := total + n;set n := n - 1;end loop sum;select concat('累加和为：', total);
end;

-- cursor游标 and handler
create procedure p9()
begindeclare c_name varchar(50);declare c_age int;-- 将查询结果集赋值给游标c1declare c1 cursor for select name, age from my_student where id > 5;  -- 游标的定义需要放在其他变量的后面declare exit handler for sqlstate '02000' close c1;  -- 定义handler：当遇到02000错误时，关闭游标c1drop table if exists my_student_cursor;create table if not exists my_student_cursor(id int auto_increment primary key,name varchar(50),age int);open c1;  -- 打开游标while true dofetch c1 into c_name, c_age;  -- 将游标指向的行的数据赋值给变量name和ageinsert into my_student_cursor values (null, c_name, c_age);end while;select * from my_student_cursor;
end;

存储函数

存储函数是有返回值的存储过程，参数类型只能为IN类型，存储函数可以被存储过程替代

-- 定义存储函数，必须是in，不能是out，默认是in，不写也可以
create function f1(n int)
returns int deterministic  -- 返回值类型，deterministic表示确定的，每次调用都会返回相同的结果
begindeclare total int default 0;while n > 0 doset total := total + n;set n := n - 1;end while;return total;
end;-- 查询调用
select f1(100);

存储函数用得少一点，因为存储函数必须要求又入参，而且存储函数能做到，存储过程都可以做。

触发器

介绍：

可以在表数据进行INSERT、UPDATE、DELETE之前或之后触发、保证数据完整性、日志记录、数据校验

语法： 

-- 触发器-insert，
create trigger insert_student after insert on my_student for each row
begin-- 当my_student表插入数据时，执行该sql语句insert into trigger_logs(id, operation, operate_time, operate_id, operate_params)values (null, 'insert', now(), new.id,concat('插入的数据内容为：id=',new.id , ',name:', new.name, ',age:', new.age, ',gender:', new.gender, ',class_id:', new.class_id));
end;-- 查看
show triggers;-- 触发器-update
create trigger update_student after update on my_student for each row
begininsert into trigger_logs(id, operation, operate_time, operate_id, operate_params)values (null, 'update', now(), new.id,concat('更新之前的数据：id=',old.id , ',name:', old.name, ',age:', old.age, ',gender:', old.gender, ',class_id:', old.class_id,'更新之后的数据：id=',new.id , ',name:', new.name, ',age:', new.age, ',gender:', new.gender, ',class_id:', new.class_id));
end;-- 触发器-delete
create trigger delete_student after delete on my_student for each row
begininsert into trigger_logs(id, operation, operate_time, operate_id, operate_params)values (null, 'delete', now(), old.id,concat('删除之前的数据：id=',old.id , ',name:', old.name, ',age:', old.age, ',gender:', old.gender, ',class_id:', old.class_id));
end;

锁

介绍：

全局锁

flush tables with read lock; -- 加全局锁
-- 导出数据，这个命令不是SQL语句，是MySQL提供的工具，需要在命令行执行
# mysqldump -h localhost -uroot -pmysql123456 study > E:\java\study\mysql\src\main\resources\sql\study.sql;
unlock tables; -- 释放全局锁

表级锁

表锁

读锁是自己和别人都不可以写，但是都可以读

写锁是自己既可以读也可以写，别人既不可以读也不可以写

元数据锁

元数据锁就相当于表结构锁，读写时的锁都是兼容的（共享），可以同方进行（因为没有修改表结构）；当修改表结构时加的锁是和读写时加的锁是互斥的，此时无法进行读写，当有读写操作的事务没有提交时，进行表结构的修改也是会被阻塞的

-- 查看元数据锁信息
select Object_type,object_schema,object_name,lock_type, lock_duration from performance_schema.metadata_locks;

意向锁

当需要加表锁的时候，需要逐行检查是否有行锁，这样性能极低，于是引入了意向锁

行级锁

介绍：

行锁

如果更新条件没有使用索引，那么就会进行全表扫描，mysql 会将行锁升级为表锁（其实就是被扫描到的行都加行锁），update 语句的where条件使用了唯一索引，就只会对这一行加行锁（排他锁）

间隙锁

如果表中按唯一索引 id 顺序存在id为5，8的数据，那么执行 update tb set name = 'test' where id = 6; 这条数据不存在，那么就会加间隙锁，开区间锁住索引为5和8之间的数据，那么执行 insert into tb(id, name) values (7, 'test'); 将会被阻塞。

间隙锁作用：防止幻读，事务T1读取了一组数据，事务T2在这组数据中插入了新的数据，如果T1再次读取这组数据，就会看到T2插入的新数据，这就是幻读。通过间隙锁，可以锁定这个范围，防止其他事务在这个范围内插入新的数据，从而避免幻读。

 临键锁

 临键锁就是间隙锁 + 行锁，上述间隙锁的第三点会加临键锁

行锁优化：

1 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。
2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。
3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。
4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

总结

mysql的锁可以分为全局锁和表锁和行锁。
全局锁：全局锁是锁住整个库，所有的写操作语句都无法执行，在做数据库备份的时候会自动触发全局锁，手动获取全局锁：flush tables with read lock;  手动释放：unlock tables；

表锁：表锁可以分为读锁和写锁

1. 读锁（共享锁，Shared Lock）

   • 也称为共享锁。
   • 允许多个事务同时获得同一个表上的读锁。
   • 但是，如果一个事务持有读锁，则不允许其他事务获取写锁（排他锁）。
   • 在读锁下，可以进行查询操作，但不能进行修改操作

2. 写锁（排他锁，Exclusive Lock）

   • 排他锁阻止其他事务获取任何类型的锁（包括读锁和写锁）。
   • 当一个事务持有写锁时，只有这个事务可以对表进行读取或写入操作。
   • 这种锁通常用于确保数据的一致性，尤其是在执行更新、删除或插入操作时。

在执行某些表结构的DDL语句时，MySQL 可能会自动为涉及的表添加写锁，例如 alter table；drop table; truncate table; create index;  读锁通常不会自动触发；

行锁：行锁分为共享锁和排他锁，原理和表锁差不多

• 共享锁（Shared Locks, S Locks）：允许多个事务同时读取同一行数据，但阻止任何事务获取该行的排他锁。
• 排他锁（Exclusive Locks, X Locks）：阻止其他所有事务获取该行的任何类型的锁（包括共享锁和排他锁），确保只有当前事务可以修改或删除该行。

锁升级：

InnoDB 不支持自动锁升级，只是出现了类似锁升级的现象

updete索引未命中导致全表扫描：如果查询条件列上没有建立索引，MySQL 将扫描整个表以找到匹配的记录。在这种情况下，逐行遍历到的都会加行锁（排他锁），加的多了就类似表锁了。

InnoDB 引擎

逻辑存储结构

在InnoDB引擎逻辑结构中，数据和索引都是存放在表空间中，一个表空间有存在多个段，一个段中存在多个区，一个区中存在多个页，一个页中存在多个行，行中存放行数据的字段、回滚指针等

内存架构

内存架构中有：BufferPool、ChangeBuffer、AdaptiveHashIndex、LogBuffer

如果一台服务器专门部署MySQL，那么通常 80% 的内存都分配给MySQL

磁盘架构

磁盘架构中有：系统表空间、独立表空间、通用表空间、临时表空间、双写缓冲区、重做日志

 后台线程

事务原理

概述

redo log

undo log

MVCC

基本概念

实现原理

当 undo log 有多个版本的时候，查询应该返回那个版本的数据呢？这个是有 readview 控制的

MySQL管理

介绍

常用工具

日志

主从复制

前置知识

 

 当主库执行DDL（结构相关的语句）和增删改时，会往 dataChange 写下 binlog 日志，从库的 IOthread 线程会发起网络请求读取 主库的 binlog 并写入中继日志 Relaylog，SQLthread 线程就会读取 Relaylog 执行记录的 SQL 语句

主从搭建-普通方式

主从搭建-Docker

1、创建主从复制的目录结构，创建配置文件，文件夹授权

mkdir -p /dockerAppData/study/mysql/one_master-slave/{master,slave}/{data,conf}
touch /dockerAppData/study/mysql/one_master-slave/master/conf/master.cnf
touch /dockerAppData/study/mysql/one_master-slave/slave/conf/slave.cnf
chmod -R 775 /dockerAppData/study/mysql/one_master-slave

2、写入配置

[mysqld]
# 主从复制-主机配置
# 主服务器唯一ID
server-id = 1
# 指定MySQL数据目录的位置
datadir = /dockerAppData/study/mysql/one_master-slave/master/data
# 设置服务器默认字符集
character-set-server = utf8
# 1：只读,0：读写，默认值0，表示默认读写都可
# read-only = 0# 启用二进制日志
log-bin=mysql-bin
# 设置logbin格式
binlog_format = STATEMENT
# 1：表名不区分大小写，统一小写存入磁盘，这样可以兼容Windows和Linux
lower-case-table-names = 1

[mysqld]
# 主从复制-从机配置
# 主服务器唯一ID
server-id = 2
# 指定MySQL数据目录的位置
datadir = /dockerAppData/study/mysql/one_master-slave/slave/data
# 设置服务器默认字符集
character-set-server = utf8
# 1：只读,0：读写，默认值0，表示默认读写都可
# read-only = 1# 启用中继日志
relay-log = mysql-relay
# 1：表名不区分大小写，统一小写存入磁盘，这样可以兼容Windows和Linux
lower-case-table-names= 1

3、创建 master 容器

docker run --name mysql-master \
-p 3310:3306 \
-v /dockerAppData/study/mysql/one_master-slave/master/data:/var/lib/mysql \
-v /dockerAppData/study/mysql/one_master-slave/master/conf/my.cnf:/etc/mysql/my.cnf \
-e MYSQL_ROOT_PASSWORD=mysql123456 \
-d mysql

4、创建 slave容器

docker run --name mysql-slave \
-p 3311:3306 \
-v /dockerAppData/study/mysql/one_master-slave/slave/data:/var/lib/mysql \
-v /dockerAppData/study/mysql/one_master-slave/slave/conf/my.cnf:/etc/mysql/my.cnf \
-e MYSQL_ROOT_PASSWORD=mysql123456 \
-d mysql

5、在master上操作

# 进入容器内部
docker exec -it mysql-master /bin/bash# 登录
mysql -uroot -pmysql123456# 创建用户,用于从库连接主库，'@'%表示允许从任意主机连接
create user 'slave'@'%' identified with mysql_native_password by 'mysql123456';# 授权，slave可以从master上复制所有表操作
grant replication slave on *.* to 'slave'@'%';# 刷新权限
flush privileges;# 查询server_id值
show variables like 'server_id';# 查询Master状态，并记录File和Position的值，这里的值是后面slave需要的
show master status;# 在还没有主从同步的时候就先不要执行sql语句了

6、在slave上操作

# 进入从数据库容器
docker exec -it mysql-slave /bin/bash
# 登录
mysql -uroot -pmysql123456# 查询server_id值,这里的值不能和主数据库的server_id相同
show variables like 'server_id';# 若之前设置过同步，请先重置
stop slave;
reset slave;# 设置主数据库
change master to master_host='192.168.222.129',master_port=3310,master_user='slave',master_password='mysql123456',master_log_file='mysql-bin.000003',master_log_pos=829;# 开始同步
start slave;# 查询Slave状态
show slave status\G

分库分表

前置知识

MyCat      

安装与概述

MyCat下载链接

MyCat官方网站

由于 MyCat 目前没有支持 MySQL8.0 之后的版本，所有需要修改一些东西：
在 Mycat 安装目录下的 lib 目录有一个 mysql-connector-java-5.1.35.jar ，将其替换为 mysql-connector-j-8.0.33.jar 就可以连接 MySQL8.0 版本了

 mysql-connector-j-8.0.33.jar 下载可以到 Maven 仓库中搜索

由于 MyCat 比较老，且多年未更新，到这就不往下学习 MyCat 了.........TMD