目录
浅入深出学习MySQL
一、数据库基础
1、数据库概念
- 数据库(DB):存储数据库的仓库
- 数据库管理系统(DBMS):操纵数据库的大型软件
- SQL:操纵关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准
2、关系型和非关系型数据库
①关系型数据库
- 结构化数据：数据存储在表格中，每个表由行和列组成。
- 关系模型：数据之间的关系通过外键（Foreign Key）来表示。
- SQL语言：使用结构化查询语言（SQL）进行数据操作和查询。
- 事务支持：ACID（原子性、一致性、隔离性、持久性）属性保证数据的可靠性和一致性。
- 严格的模式：在插入数据之前，需要先定义表的结构（Schema），字段类型和约束。
- 常见数据库：MySQL、Oracle、SQLServer、PostgreSQL
②非关系型数据库
- 灵活的数据模型：支持多种数据模型，如键-值对、列存储、文档存储和图数据库等。
- 无固定模式：数据结构可以是动态的，可以存储结构化、半结构化或非结构化的数据。
- 可扩展性：通常具有高扩展性，适合处理大规模数据和高并发访问。
- 高性能：针对特定场景进行了优化，读写性能通常高于关系型数据库。
- CAP理论：通常在一致性（Consistency）、可用性（Availability）和分区容错性（Partition Tolerance）之间做出权衡。
- 常见数据库：MongoDB（文档型）、Redis（键值型）、Cassandra（列存储型）、Neo4j（图数据库）
③主要区别
-
数据模型：
-
关系型数据库：数据存储在表格中，表之间通过外键建立关系。
-
非关系型数据库：数据存储方式多样，支持键值对、文档、列族和图等多种数据模型。
-
-
查询语言：
-
关系型数据库：使用SQL进行查询和操作。
-
非关系型数据库：没有统一的查询语言，不同数据库有不同的查询方式，例如MongoDB使用MQL（MongoDB Query Language）。
-
-
事务处理：
-
关系型数据库：严格遵循ACID特性，事务处理可靠。
-
非关系型数据库：通常不完全遵循ACID特性，某些数据库提供最终一致性（Eventual Consistency）。
-
-
扩展性：
-
关系型数据库：主要通过垂直扩展（增加单台服务器的处理能力）来提高性能。
-
非关系型数据库：更适合水平扩展（增加更多的服务器）来处理大规模数据和高并发。
-
-
模式灵活性：
-
关系型数据库：具有严格的模式，数据插入前需要定义表结构。
-
非关系型数据库：模式灵活，可以动态变化，适合处理多样化和变化频繁的数据。
-
-
适用场景：
-
关系型数据库：适用于事务密集型应用，如金融系统、企业级应用等。
-
非关系型数据库：适用于数据量大且结构多变的应用，如社交媒体、物联网、大数据分析等。
-
3、启动MySQL
- 启动/停止服务
net start mysql8
net stop mysql8
- 连接到MySQL
mysql [-h 127.0.0.1] [-P 3306] -u root -p # []内指令可以省略
mysql -h 127.0.0.1 -P 3307 -u root -p
mysql -u root -p
- 退出
exit
二、SQL
1、分类
| 分类 | 说明 |
|---|---|
| DDL | 定义语言，定义数据库对象（数据库、表、字段） |
| DML | 操作语言，对数据库表中数据进行增删改查 |
| DQL | 查询语言，用于对数据库中表记录进行查询 |
| DCL | 控制语言，用于创建数据库用户、定义权限 |
2、数据类型
-
数值类型（Numeric Types）
-
INT: 常用的整数类型，4字节。
-
FLOAT: 单精度浮点数，4字节，约7位数值。
-
DOUBLE: 双精度浮点数，8字节，约15位数值。
-
DECIMAL: 精确的小数类型，常用于金融数据，定义为
DECIMAL(M, D)。
-
-
日期和时间类型（Date and Time Types）
-
DATE: 日期，格式为
YYYY-MM-DD。 -
TIME: 时间，格式为
HH:MM:SS。 -
DATETIME: 日期和时间，格式为
YYYY-MM-DD HH:MM:SS。 -
TIMESTAMP: 时间戳，格式为
YYYY-MM-DD HH:MM:SS，自动记录插入或更新的时间。
-
-
字符串类型（String Types）
-
CHAR: 固定长度字符串，最大255字符。
-
VARCHAR: 可变长度字符串，最大65535字符。
-
TEXT: 大文本字段，最大65535字符。
-
TINYTEXT: 小文本字段，最大255字符。
-
MEDIUMTEXT: 中等大小文本字段，最大16,777,215字符。
-
LONGTEXT: 超大文本字段，最大4,294,967,295字符。
-
-
枚举和集合类型（Enumeration and Set Types）
-
ENUM: 枚举类型，允许选择单个值，例如
ENUM('value1', 'value2', 'value3')。 -
SET: 集合类型，允许选择多个值，例如
SET('value1', 'value2', 'value3')。
-
3、DDL
定义语言，定义数据库对象（数据库、表、字段）
（1）数据库操作
- 查询数据库
show databases; # 查询所有数据库
select database(); # 查询当前数据库
- 创建数据库
create database 数据库名；
create database if not exists 数据库名 ； # 判断数据库是否存在不存在则创建
create database 数据库名 default charset utf8mb4; # 创建数据库并设置编码为utf8mb4
- 使用数据库
use 数据库名；
- 删除数据库
drop database [if not exists] 数据库名;
（2）表查询操作
-
查询当前数据库所有表
show tables; -
查询表结构
desc 表名； -
查询指定表的建表语句
show create table 表名；
（3）创建表
create table tb_test(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄'
) comment '测试表';
（4）修改表
- 添加新字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
- 修改数据类型
alter table 表名 modify 被修改字段名 新数据类型(长度);
-
修改字段名或字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]; -
修改表名
alter table 表名 rename to 新表名；
- 删除字段
alter table 表名 drop 字段名；
- 删除表
drop table [if exists] 表名;
- 删除表，并重新创建该表（即清空表数据）
truncate table 表名;
4、DML
数据操作语言，对数据库表中数据进行增删改查
（1）添加数据
- 指定字段添加数据
insert into 表名(字段1，字段2) values（值1，值2）；
- 全部字段添加数据
insert into 表名 values (值1，值2)；
- 批量添加数据
insert into 表名(字段1，字段2) values（值1，值2），（值3，值4）
（2）修改数据
update 表名 set 字段名1=值1， 字段名2=值2 [where 条件]；
（3）删除数据
delete from 表名 [where 条件];
5、DQL
查询语言，用于对数据库中表记录进行查询
（1）语法
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件查询
order by 排序字段列表
limit 分页参数
- 执行顺序
（2）基本查询
- 查询多个字段
select 字段1 [as 别名1]，字段2[as 别名2] from 表名
select * from 表名；
- 去重查询
select distinct 字段 [as 别名1]from 表名;
（3）条件查询
- 语法
select 字段列表 from 表名 where (条件列表);
-
条件
<>或！=：不等于between and：在某个范围之内（含最小、最大值）in(值1，值2)：多值匹配like 占位符:模糊查询（_匹配单个字符，%匹配任意个字符）AND 或 &&：并且OR 或 ||：或者not 或 ！:非
-
多条件查询
# 查询departmen字段为IT、HR其中一个的数据
select * from employees where (department='IT' || department='HR');
select * from employees where department='IT' or department='HR';
select * from employees where department in('IT', 'HR');
- 模糊查询
select * from employees where department like '__'; # 查询部门字段中，部门名为2个字的部门
select * from employees where name like '%e';# 查询姓名最后一个字母为e的人
- 区间查询
# 查询20年1月到12月内在 IT和HR部门的员工
select * from employees where hire_date between '2020-01-01' and '2021-12-31' and department in('IT', 'HR');
（4）聚合函数
- 语法
select 聚合函数（字段列表） from 表名；
- 函数
count:统计数量max:最大值min:最小值avg:平均值sum:求和
（5）分组查询
- 语法
SELECT 字段列表 FROM 表名 [where 条件] GROUP BY 分组字段 [having 分组后过滤条件];
-
where和having区别
- 执行顺序不同：where是分组前过滤，不满足条件不参与分组。
- 判断条件不同：having可以对聚合函数进行判断，但where不可以
- where > 聚合函数 > having
-
按部门分组，统计各个部门薪资大于60000的人数
select department, count(*) from employees where salary>60000 group by department;
（6）排序查询
- 语法
select 字段列表 from 表名 order by 字段1 排序方式, 字段2 排序方式 ;
-
排序方式
-
asc：默认，从大到小，降序排序 -
desc:从小到大，升序排序
-
-
按薪水从大到小排序，薪水相同按入职时间从先到后排序
select * from employees order by salary desc, hire_date asc ;
（7）分页查询
- 语法
select 字段列表 from 表名 limit 起始索引，每页查询个数；
起始索引 = (查询页面 - 1) * 每页查询个数
select * from employees limit 0,10; # 查询第1页数据，每页十条
select * from employees limit 20,10;# 查询第3页数据，每页十条
6、DCL
控制语言，用于创建数据库用户、定义权限
（1）管理用户
- 查询用户
use mysql;
select * from user;
- 创建用户
create user '用户名'@'主机名' identified by '密码';
主机名： 127.0.0.1通过 TCP/IP 连接进行登录，localhost只能通过本地计算机上的本地套接字连接，%表所有主机可以访问
- 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
- 删除用户
drop user '用户名'@'主机名'
(2)权限控制
- 查询权限
show grants for '用户名'@'主机名'
- 授予权限
可以使用*表示所有表
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'
- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'
- 权限列表
三、SQL进阶
1、函数
（1）字符串函数
指一段被定义好的代码段，可以直接调用执行
| 函数 | 功能 |
|---|---|
| concat(s1,s2) | 字符串拼接 |
| lower(str) | 将字符串str全转为小写 |
| upper(str) | 将字符串str全转为大写 |
| lpad(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| rpad(str,n,pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| trim(str) | 去掉字符串头部和尾部的空格 |
| substring（str,start,len） | 返回字符串str（也可以是字段名）从start位置起的len个长度字符串 |
- 对employees表，hire_dtae列中不满5位数的填充0，使其满足5位数
update employees set hire_date = lpad(hire_date, 5, '0')
（2）数值函数
- 生成一个6位数的随机验证码，并只保留整数
# 不足六位则补0，
select lpad(round(rand() * 10000000, 0), 6, '0')
| 函数 | 功能 |
|---|---|
| ceil（x） | 向上取整 |
| floor（x） | 向下取整 |
| mod（x,y） | 返回x/y的模 |
| rand（） | 返回0~1内的随机数 |
| round(x,y) | 求参数x的四舍五入的值，保留y位小数 |
（3）日期函数
- 查询所有员工入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as 'entrtdays' from tb_emp order by entrydays desc;
| 函数 | 功能 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| year(date) | 获取指定date的年份 |
| moonth(date) | 获取指定date的月份 |
| day(date) | 获取指定date的日期 |
| date_add（date，interval expr type） | 返回一个日期/时间值加上expr后的时间值 |
| datediff(date1， date2) | 返回起始时间date1和结束时间date2之前的天数 |
（4）流程函数
| 函数 | 功能 |
|---|---|
| if(value, t, f) | 如果为true，返回t，否则返回f |
| ifnull(value1, vlaue2) | 如果value不为空，返回value1，否则返回value2 |
| case when [val1] then [res1] else [default] end | 如果val1为true，返回res1，否则返回default默认值 |
| case [expr] when [val1] then [res1] else [defult] end | 如果expr的值等于val1，返回res1，否则返回defult默认值 |
- 大于等于85表示优秀，大于等于60表示及格，小于60表示不及格
select id, name
(case when math >= 85 then '优秀' when math >=60 '及格' else '不及格' end) '数学'，
(case when english >= 85 then '优秀' when english >=60 '及格' else '不及格' end) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 '及格' else '不及格' end) '语文'
from score;
2、约束
限制表中字段数据的规则，保证数据正确、有效、完整性
1、约束类型
| 关键字 | 约束 | 描述 |
|---|---|---|
| not null | 非空约束 | 字段数据不能为null |
| unique | 唯一约束 | 该字段数据都是唯一不可重复的 |
| primary key | 主键约束 | 主键是每行数据的唯一标识，不重复的。auto_increment 自动增长 |
| default | 默认约束 | 保存数据时，如果未指定该字段值，则使用默认值 |
| check | 检查约束 | 保证字段值满足某一个条件 |
| foreign key | 外键约束 | 用于让两张表的数据之间建立连接，保证数据一致性、完整性 |
2、定义约束
- 创建表定义约束
create table tb_user
(
id int primary key auto_increment comment '主键id', # 主键，且自动增长
name varchar(10) not null unique comment '姓名', # 不为空，并且唯一
age int check ( age > 0 && age <= 120 ) comment '年龄', # 大于0，并且小于等于120
status char(1) default '1' comment '状态', # 如果没有指定该值默认为1
gender char(1) comment '性别' # 无约束
) comment '用户表';
3、定义外键
- 添加外键
alter table 表名 add constraint 外键名称 foreign key（外键字段名） references 主表（主表主键列名）;
create table tb_user
(
id int primary key auto_increment comment '主键id',
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 && age <= 120 ) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别',
constraint fx_employese foreign key(id) references employees(employee_id)
) comment '用户表';
- 删除外键
alter table 表名 drop foreign key 外键名称;
4、外键行为约束
| 行为 | 说明 |
|---|---|
| no action | 默认行为，当在父表中删除更新对应记录时，如果有外键不允许删除 |
| restrict | 与上面效果一致 |
| cascade | 级联，同时删除/更新子表中记录 |
| set null | 父表删除记录时，子表外键值设置为null |
| set default | 父表变更时，子表将外键列设置为一个默认值 |
- 定义外键及约束
alter table 表名 add constraint 外键名称 foreign key（外键字段） 主表名(主表字段名) on update 更新数据时外键约束 on dekete 删除时约束
3、多表查询
（1）多表关系
- 一对一：表a与表b有一行数据是对应的， 如：你(1)和身体(1)是一对一对应的
- 一对多：在多的(子表)一方设置外键，如：每个班级(多)可以有多个学生（一）。
- 多对多:建立中间表，中间表外键包含两个外键，关联两张表的主键。 如：每个学生可以选修多门课程，每门课程可以有多个学生选修。
（2）连接查询
-
内连接：相当于查询a、b表交集的部分
-
外连接：
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
-
自连接：当当前表与当前表不同字段的连接查询，自连接必须使用表的别名
-
子查询：嵌套在另一条 SQL 查询中的查询
-
联合查询：把多次查询的结果合并起来，形成一个新的查询结果集，数据字段、行数必须一致
（3）内连接
查询两张表交集部分
-
语法
- 隐式内连接
select 字段列表 from 表1 [别名1]，表2 [别名2] where 条件；- 显式内连接
select 字段列表 from 表1 [别名1] [inner] join 表2 [别名1] on 连接条件；- 区别：语法、连接方式，不影响结果
-
查询学生姓名、年龄、以及外键表中的所处班级
# 隐式内连接
select students.student_id,name,age,class_name from students, classes where students.student_id = classes.class_id
select s.student_id,name,age,class_name from students s, classes c where s.student_id = c.class_id;
# 显式内连接
select students.student_id,name,age,class_name from students join classes on students.student_id = classes.class_id
（4）外连接
查询左/右表所有数据，以及两张表交集部分数据
-
语法
- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件； # 查询表1所有数据，包含表1和表2部分交集数据- 左外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件； # 查询表2所有数据，包含表1和表2部分交集数据
（5）自连接
当前表与当前表不同字段的连接查询，自连接必须使用表的别名
- 语法
select 字段列表 from 表1 别名1 join 表a 别名b 条件；
- 查询各个员工所属领导，并展示员工和领导姓名，managerid字段所属领导id
select a.name, b.name from tb_sutudent a, tb_sutudent b where a.managerid = b.id;
# 如果员工没有领导也需要查询出来
select a.name '员工姓名', b.name '领导姓名' from tb_sutudent a left join tb_sutudent b on a.managerid = b.id;
（6）联合查询
把多次查询的结果合并起来，形成一个新的查询结果集,数据字段、行数必须一致
- 语法
select 字段列表 from 表a
union [all]
select 字段列表 from 表b;
- 查询小于21，大于22岁的学生
select * from students where age<21
union all
select * from students where age>22;
（7）子查询
嵌套在另一条 SQL 查询中的查询
- 语法
select * from 表名 where
-
操作符
in:在指定的集合范围之内，多选一not in：不在指定集合范围内的其他所有数据any：子查询返回列表中，有任意一个满足即可- some：与any等同
all：子查询返回列表所有值都必须满足
-
标量子查询：子查询结果为单个值，如数字、字符串、日期这些最简单的形式
# 查询班级为class A的学生所有信息 select * from students where class_id = (select class_id from classes where class_name='Class A'); -
列子查询：查询结果是一列(可以多行)
# 查询班级为A、B的所有学生信息 select * from students where class_id in (select class_id from classes where class_name in('Class A', 'Class C')); # 查询班级为A、B之外的所有学生信息 select * from students where class_id not in (select class_id from classes where class_name in('Class A', 'Class C')); -
行子查询：子查询返回结果是一行数据（可以是多列）
# 查查询与学生alice，年龄、班级都相同的学生信息 select * from students where (age, class_id) = (select age, class_id from students where name='Alice'); -
表子查询：子查询返回的结果是多行多列
# 查询与Alice、Bob，字段age、class_id相同的学生信息 select * from students where (age, class_id) in (select age, class_id from students where name in ('Alice', 'Bob')); -
多表联查子查询：使用子查询出来的数据，充当另外的表
select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
4、事务
即一组操作，事务让这组操作要么同时成功要么同时失败。来保证数据一致性。
（1）事务操作
- 查看/设置事务提交方式
select @@autocommit;
set @@autocommit = 0; # 设置为手动提交
- 开始事务
start transaction; 或 begin;
- 提交事务
commit;
- 回滚事务
rollback;
（2）事务四大特性(ACID)
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性（Consistency）：事务完成时，必须使所有数据都保持一致状态
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据改变就是永久的
（3）并发事务问题
- 脏读：一个事务读到另一个事务还没有提交的数据
- 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
- 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在。
（4）事务隔离级别
解决并发事务问题的
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 |
|---|---|---|---|---|
| read uncommitted | 不可解决 | 不可解决 | 不可解决 | 最好 |
| read committed | 解决 | 不可解决 | 不可解决 | 好 |
| repatable read 默认 | 解决 | 解决 | 不可解决 | 差 |
| serializable | 解决 | 解决 | 解决 | 最差 |
- 查询事务隔离级别
select @@transaction_isolation
- 设置事务隔离级别
set [session global] transaction isolation level 事务隔离级别
四、数据库进阶
1、MySQL体系结构
- 连接层：主要完成连接处理、授权认证及相关的安全方案，服务器也会为安全接入的每个客户端验证其权限
- 服务层：核心服务功能，如sql、缓存的查询、sql分析优化、部分内置函数的执行，所有跨存储引擎功能也在这实现
- 引擎层：负责mysql中数据存储与提取，服务器通过api和存储引擎通信，不同引擎不同功能
- 存储层：将数据存储在文件系统上，即硬盘上。并完成与存储引擎的交互。
2、存储引擎
（1）简介
- 指定存储引擎
create table my_memory(
id int ,
name varchar(10)
) engne = Memory;
- 查询当前数据库版本支持的存储引擎
show engines;
（2）innoDB存储引擎
-
兼顾高可靠性和高性能的通用存储引擎，在mysql5.5之后为mysql的默认存储引擎
-
特点
- DML(增删改语句)操作遵循AICD（原子性、一致性、隔离性、持久性）模型，支持事务
- 行级锁，提高并发访问性能
- 支持外键foreign key 约束，保证数据的完整性和正确性
-
文件： xxx.ibd其中xxx代表表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。参数：innoodb_file_per_table
-
查看ibd文件，文件地址通常在mysql的Data目录\数据库名
ibd2sdi 文件名
- 逻辑存储结构
（3）MyISAM
- mysql早期默认存储引擎
- 特点
- 不支持事务、外键
- 支持表锁，不支持行锁
- 访问速度快
- 文件
- xxx.sdi：存储表结构信息
- xxx.MYD：存储数据
- xxx.MYI：存储索引
（4）Memory存储引擎特点
- 数据存储在内存中
- 特点
- 内存存放，访问速度极快
- hash索引（默认）
- 文件： xxx.sdi：存储表结构信息
（5）常见引擎区别
3、索引
(1)概述
- 索引是帮助MySQL高效获取数据的数据结构，通过创建一种特定的数据结构（通常是B树或哈希表）来快速定位数据
- 优势
- 提高数据检索效率，降低数据库IO成本
- 使用索引进行排序，降低数据排序成本，降低cpu的消耗
- 劣势
- 索引列也是要占用空间的
- 因为会多加个索引列，但会降低更新表的速度，如对表增、删、改操作效率降低
（2）索引结构
| 索引结构 | 描述 | InnoDB | MyISAM | Memory |
|---|---|---|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 | 支持 | 支持 | 支持 |
| Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 | 不支持 | 不支持 | 支持 |
| R-tree空间索引 | MyISAM引擎特殊的类型，主要用于地理空间数据类型，通常使用较少 | 不支持 | 支持 | 不支持 |
| Full-text全文索引 | 通过建立倒排序索引，快速匹配文档方式，类似于Lucene、solr、es | 5.6后支持 | 支持 | 不支持 |
- 为什么InnoDB存储引擎选择使用B+tree索引结构？
- 相对于二叉树、层级更少、搜索效率高
- 对于b-tree无论叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
- 相对Hash索引，B+tree支持范围匹配及排序操作
（3）索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | primary |
| 唯一索引 | 避免表中数据列数据重复 | 可以有多个 | unique |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键字，而不是比较索引中的值 | 可以有多个 | fulltext |
| 聚集索引 | B+tree将数据存储和索引放到一块，索引结构的叶子节点保存了行数据 | 必须有，而且唯一 | |
| 二级索引 | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存多个 |
-
聚集索引选取规则
- 如果存在主键，主键索引就是聚集索引
- 不存在主键，将使用第一个唯一（unique）索引作为聚集索引
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
-
回表查询
先在name字段找到‘Arm’，获取到其索引10，在使用10到聚集索引（主键列）找到该行所有字段数据。
- 以下哪个sql语句执行效率更高
答：第一条，因为第一条只需要执行聚集索引查询。而第二条需要先二级索引查询到主键id，再使用主键id去找到改行数据
select * from tb_user where id = 10;
select * from tb_user where name = 'Arm';
（4）索引语法
- 查看表索引
show index from tb_user;
show index from tb_user\G; # 格式化查看
- 创建索引
create [unique] index 索引名 on 表名(字段名)； # 可选唯一索引
- 创建联合索引
create index 索引名 on 表名(字段1,字段2，字段3)
create index 索引名 on 表名(字段1 asc,字段2 desc); # 设置字段1降序排序，字段2升序排序
- 删除索引
drop index 索引名 on 表名;
（5）SQL性能分析
- 查看mysql服务器状态信息
可以查询增删改查相对应执行次数
show global status like 'Com_______'; # 模糊匹配，七个下划线
-
慢查询日志
-
慢查询：执行时间超过指定参数（long_query_time,默认10秒）时间，即为慢查询
-
查询慢查询是否开启
show variables like 'show_query_log';- 默认不开启，可以在
/etc/my.cnf中配置如下开启
# 开启MySQL慢查询日志开关 show_query_log = 1 # 设置慢查询时间为2秒 long_query_time 2- Liux指令
vi /etc/my.cnf # 编辑mysql systemctl restart mysql # liux重启mysql服务器 -
-
profile性能详情信息
- 查看当前MySQL是否支持
select @@have_profiling;- 默认不开启，设置开启profile性能详情信息
set profiling = 1;- 查看mysql指令耗时
# 查看所有历史指令，及其id show profiles; # 查看指定sql,在开始执行、初始化、结束等等操作各耗费的时间 show profile [cpu] for query 16; -
explain执行计划信息
- 语法
explain 要查看的sql语句；- 字段含义
id：操作顺序select_type：查询类型- SIMPLE：简单表，无表连接和子查询
- PRIMARY：主查询，即外层查询）
- UNION：union中的第二个或者后面的查询语句
- SUBQUERY：select、where之后包含了子查询
type：表示连接类型，性能由好到差null、system、const、eq_ref、ref、range、index、allpossible_key：显示可以使用到的索引，一个或多个key：实际使用的索引，如果为Null，则没有使用索引key_len：表示索引中使用的字节数，表示可能使用最大字节数，但不一定是实际使用的，越短越好rows：MySQL认为必须要执行查询的行数，在Innodb引擎的表中，是个估计值，可能并不总是准确的filterd：表示返回结果的行数占需要读取行数的百分比，filtered的值越大越好
（6）索引使用原则
索引优化
验证索引效率
select * from 表名 where sn ='100000003145001' # 查看sql耗时
create index 索引名 on 表名(字段名); # 创建索引，后再次查看sql耗时
-
最左前缀法则- 即如果索引了多列，最左边的列数据不存在，则不满足最左前缀法则，会导致后面字段索引失效
- 复合索引的使用顺序：查询条件应尽量包含复合索引的最左列开始的连续列。
- 索引设计：在设计复合索引时，考虑查询中最常使用的列组合，将频繁使用的列放在最左边。
- 优化查询：重构查询以匹配现有的复合索引，充分利用最左前缀法则。
-
导致**索引失效**的操作-
索引列运算
explain select * from tb_user where substring(phone, 10, 2) = '15'; -
字符串不加引号
explain select * from tb_user where profession='软件工程' and age = 31 and status = 0; -
模糊查询
- 有头部模糊匹配索引失效，但仅仅是尾部模糊匹配，索引不会失效。
explain select * from tb_user where profession='软件%'; # 不会失效 explain select * from tb_user where profession='%工程'; # 失效 explain select * from tb_user where profession='%共%'; # 失效 -
or连接失效
用or分开的条件，如果前面列有索引，后面列没有索引，则不会使用索引
# 因为age没有索引，即使id有索引，也会失效，所以也需要针对age设置索引 explain select * from tb_user where id = 10 or age = 23; -
数据分布影响
如果MySQL评估使用索引比使用全表扫描更慢，则使用全表扫描
select * from tb_user where id >= 1; # 因为全表大部分数据都满足，MySQL评估使用全表扫描快，则使用全表查询
-
-
SQL提示主观设置MySQL使用、或者不允许使用的索引
-
use index:告诉数据库可以使用的索引explain select * from tb_user use index(索引名) where profession='软件工程'; -
ignore index：指定不允许使用的索引explain select * from tb_user ignore index(索引名) where profession='软件工程'; -
force index：指定必须使用的索引explain select * from tb_user force index(索引名) where profession='软件工程';
-
-
覆盖索引select * from tb_user where id = 2; # 直接在聚集索引查，返回所有列数据，一次索引扫描 select id, name from tb_user where name = 'Arm'; # 先二级索引name查，查询到id、name直接返回，一次索引扫描 # 先二级索引查id、name，但还需要返回gender，索引需要使用id到聚集索引(回表查询)查 select id, name, gender from tb_user where name = 'Arm';# 可以给name、gender建立联合索引，则可以避免回表查询，性能最优
-
前缀索引-
当字段类型为字符串（varchar、char）大文本字段时，如果存储的是文章内容等。直接建立索引会让索引很大，影响io操作。可以只将字符串的一部分前缀建立索引，从而节约空间、提高io效率。
-
语法
create index 索引名(column(前缀长度)); -
前缀长度 = 去重后数据行数 / 数据总行数
- 为1则该长度区分度高且性能最好
# 索引选择性越高则查询效率越高，唯一索引的选择性是1，是最好、最优的索引选择性 select count(distinct 字段名) / count(*) from tb_user;
-
-
单列索引和联合索引选择问题- 单个索引：索引只有一个列
- 联合索引：索引有多个列，又称组合索引。
- 如何选择：如果存在多个查询条件，可以针对查询的字段建立联合索引
# 对name、phone建立联合索引查询效率最高 explain select id, name, phone from tb_user where phone='17799990010' and name = '韩信';- 联合索引数据结构图
- 因为创建时候phone在前面，所以先按phone字段先排序
- 联合索引会让两个字段数据放在一起，所以可以一起查询到
（7）索引设计原则
- 数据量大(一万条数据以上)，且查询比较频繁的表建立索引。
- 常作为查询条件（where）、排序（order by）、分组（group by）操作的字段。
- 尽量选择区分度较高的列作为索引，尽量建立唯一索引，字段数据区分度越高，索引效率越高。
- 如果是大文本字符串类型，建立前缀索引。
- 尽量使用联合索引，减少单列索引，节省存储空间、避免回表查询、提高查询效率。
- 控制索引数量，索引并不是多多益善，索引多会占用空间且维护的代价越大。
- 如果索引列不能存储null值，创建表时一定设置not null约束，这样可以让优化器知道每列是否会包含null值，从而更好的选择索引。
4、SQL优化
（1）插入数据
-
批量插入- 因为每次insert都需要与数据库建立网络连接，所以批量插入可以降低连接次数
- 批量插入数据500~1000条数据为宜，数据多可以多次批量插入
-
手动提交事务- 因为MySQL中是默认提交事务，所以每次insert之后都会提交事务
- 开启手动提交事务
start transaction; # 开启事务 # 多条SQL语句 commit; # 提交事务 -
主键id顺序插入- 顺序插入性能优于乱序插入
乱序：8 1 9 6 7 2 顺序：1 2 3 4 5 6 -
大批量插入数据load指令-
如果需要大批量数据(几十万以上)，可以使用load指令,因为insert相对性能低了
-
语法
# 连接mysql服务器，加上--local-infile参数 mysql --local-infile -u root -p # 设置全局参数为1，表明开启从本地文件加载数据的开关 set global local_infile = 1 # 执行load指令，将数据加载到表结构中,表名每列使用逗号分割，每行使用回车分割 load data local infile '文件地址.log' into table '表名' fields terminated by '，' lines terminated by '\n'
-
（2）主键优化
-
数据组织方式：在innoDB存储引擎，表数据是根据主键顺序组织存放，称为索引组织表（IOT）
-
页分裂：页分裂发生在B树或B+树节点（即页）满时插入新记录的情况下。此操作将一个页分成两个页，以保持树的平衡和有效的检索性能。
-
页合并：页合并发生在删除记录导致某个页的使用率下降到某个阈值以下时。此操作将两个或多个页的内容合并到一个页中，以提高空间利用率和检索效率。
-
主键设计原则
- 满足业务需求下，尽量降低主键长度
- 插入数据时，尽量顺序插入，选择Auto_increment自增主键
- 尽量不使用uuid做主键或者其他自然主键，如：身份证号码
- 业务操作时，避免对主键的修改
（3）排序优化
Using filesort：通过索引或全表扫描，读取满足条件行，然后在缓冲区sortbuffe中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高- 根据排序字段建立合适的索引，多字段排序，也遵循最左前缀法则
- 尽量使用覆盖索引
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）
- 多字段排序，一个需要升序一个降序，此时需要注意创建时联合索引指定排序规则(asc降序、desc升序)
create index 索引名 on 表名(字段1 asc,字段2 desc);
（4）分组优化
- 分组也可以使用索引来优化
- 索引的使用也是需要满足最左前缀法则
# 分组查询
explain select profession,count(*) from tb_user group by profession;
# 创建索引
create index iidx_user_pro_age_sta on tb_user(profession, age, status);
# 分组查询
explain select profession,count(*) from tb_user group by profession, age;
（5）分页优化
-
起始索引大的时候会导致sql执行时间非常长
-
使用多表联查、创建覆盖索引、子查询解决
select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
（6）count统计优化
-
MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count（*）的时候会直接返回这个数，效率很高
-
InnoDB引擎就麻烦了，它执行count（*）的时候，需要把数据一行一行读取出来，再累计计数。
-
优化思路：自己计数
-
count（）：是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是null，累计值就加1，否则不加，最后返回累计值
-
count（主键id）:
- InnoDB引擎会遍历整张表，把每行主键id都取出来，返回给服务层。服务层拿到主键后，直接按行累加（主键不可能为null）
-
count（字段）：
- 没有not null字段约束，InnoDB引擎会遍历整表，每行字段值返回服务层，服务层判断不为null则累加
- 有not null字段约束，InnoDB引擎会遍历整张表把每行的字段值取出来，返回给服务层，直接按行累加
-
count（1）:指定行
- InooDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行累加
-
count（*）
- InnoDB专门做了优化，不会将全部字段取出，而是服务层直接按行累加
-
-
效率排序：
count（字段） < count（主键id） < count（1） < count（*）, 所以**尽量使用count(*)**
(7)update修改优化
-
尽量根据主键id、索引字段进行数据更新
-
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。
-
行锁: 该行数据在事务提交前不能进行二次操作。
START TRANSACTION; UPDATE student SET no = '2000100100' WHERE id = 1; -- 此时事务未提交，行锁被持有 -
表锁： 该表数据在事务提交前，表锁会阻止其他事务对该表进行任何读或写操作，直到锁被释放。
START TRANSACTION; UPDATE student SET no = '2000100105' WHERE name = '大胖子'; -- 此时事务未提交，表锁被持有
-
5、视图
（1）介绍
- 视图（view）是一种虚拟存在的表，视图中的数据并不在数据库实际存在、随数据库数据动态生成，
- 视图只保存查询的SQL逻辑，不保存查询结果数据。
- 修改视图中的数据，会直接修改创建视图的表数据
（2）语法
- 创建
create [or replace] view 视图名 as select查询语句 [with[cascaded | local] check option]；
create or replace view stu_v_1 as select id,name from tb_student where id <= 10;
- 查询视图
show create view 视图名称； # 查询创建视图语句
select * from 视图名称; # 查询视图数据，即正常查询表数据
- 修改视图
create [or replace] view 视图名 as select查询语句 [with[cascaded | local] check option]； # 方法一，覆盖修改
alter view 视图名称[(列名列表)] as select 查询语句； # 方法二，修改视图
- 删除
drop view [if exists] 视图名称[, 视图名称2]; # 可以一起删除多个
（3）检查选项
with check option创建视图时，MySQL会通过视图检查真正更改的每个行，列如插入、更新、删除以其符合视图创建规则
（4）作用
- 简单：可以简化用户操作，定义经常使用的数据为视图，避免多余、重复操作
- 安全：数据库可以授权，但不能授权到数据库特定行和特定列上，通过修改视图、定义检查选项查询和修改他们所能见到的数据
- 数据独立：视图可以帮助用户屏蔽真实表结构变化带来的影响
6、存储过程
（1）介绍
存储过程（Stored Procedure）是一组预先编译并存储在数据库中的SQL语句集，可以通过指定的名字和参数来调用执行。存储过程可以包含多个SQL语句和逻辑控制结构（如条件判断和循环），并且可以接受输入参数、输出参数以及返回值。
（2）特点
- 预编译：存储过程在创建时就被预编译，因此执行速度比普通的SQL查询更快。
- 重用性：可以定义一次并多次调用，减少代码重复。
- 封装性：将复杂的业务逻辑封装在存储过程中，隐藏具体实现细节，提供一致的接口。
- 安全性：通过存储过程可以控制对数据库表的访问权限，只允许用户通过存储过程来访问数据。
- 参数化：存储过程可以接受输入参数和输出参数，增强灵活性。
（3）语法
-
创建
create procedure 存储过程名称([in|out|inout]) begin -- SQL语句 end; -
调用
call 名称([参数]); -
查看
# 查询指定数据库的存储过程以及状态信息 select * from information_schema.routines where routine_schema = 'xxx'; # 查询某个存储过程定义 show create procedure 存储过程名称; -
删除
drop procedure [if exists] 存储过程名称； -
参数
-
in:默认参数，作为输入，即需要调用时传入的值 -
out:作为输出，该参数可以作为返回值 -
inout:可以作为输出参数也可以作为输入参数 -
案例
create procedure p4(in score int, out result varhcar(10)) begin declare score int default 58; declare result varchar(10); if score >= 85 then set result := '优秀'; elseif score >= 60 then set result := '及格'; else set result := '不及格'; end if; end; call p4(18, @result) select @result; create procedure p5(inout score double) begin set score := score * 0.5; end; set @score = 78; call p5(@score); select @score;
-
（4）参数变量
-
系统变量-
MySQL服务器提供的
全局变量（GLOBAL）：生效于全部mysql服务器会话变量（SESSION）：只生效于当前会话窗口（控制台）
-
查看系统变量
show [session | global] variables; # 查看所以系统变量 show [session | global] variables like '...%'; # 模糊匹配查看系统变量 show [session | global] 系统变量名； # 查询指定系统变量的值 -
设置系统变量
set [session | global] 系统变量名 = 值; set @@[session | global] 系统变量名 = 值; -
注意
- 如果没有指定global、session，则默认为session会话变量
- mysql服务器重启之后，所设置的全局参数会失效，想要不失效需要在/etc/my.cnf中配置。
-
-
用户自定义变量-
用户根据需要自定义的变量，用户变量不需要提前声明，“@变量名”直接使用即可，作用域为当前连接
-
赋值
set @变量名 = 值; # 字符串加'' set @变量名 := 值; set @变量1 := 值1,@变量2 ：= 值2; select @变量名 := 值; select 字段名 into @变量名 from 表名； -
使用
select @变量名；
-
-
局部变量-
局部生效的变量，访问之前需要使用declare声明。可用作存储过程内的局部变量和输入参数，范围是在其内声明的begin…end块。
-
声明
declare 变量名 变量数据类型类型 [default 指定的默认值]; -
赋值
set 变量名 = 值； set 变量名 := 值; select 字段名 into 变量名 from 表名; -
使用
create procedure p2() begin declare stu_count int default 0; # 创建局部变量 select count(*) into stu_count from student; # 给局部变量赋值 select stu_count; # 查询结果 end;
-
（5）流程控制
-
if判断- 语法
if 条件1 then 语句体 elseif 条件2 then 语句体 else 语句体 end if；- 案例
create procedure p3() begin declare score int default 58; declare result varchar(10); if score >= 85 then set result := '优秀'; elseif score >= 60 then set result := '及格'; else set result := '不及格'; end if; select result; end; -
case-
语法
case case_value # 判断case_value是when_value1还是2,从而执行对应的语句 where when_value1 then statment_list1 [where when_value2 then statment_list2] [else statement_list] end case; case where 条件表达式1 then 待执行语句1 [where 条件表达式2 then 待执行语句2] [else 待执行语句3] end case;
-
-
while循环-
语法
# 先判定条件，如果条件为true，则执行逻辑，否则不执行逻辑 while 条件 do SQL逻辑 end while；
-
-
repeat循环-
先执行一次，满足条件退出循环
repeat SQL逻辑 until条件 end repeat;
-
-
loop循环-
loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合一下两个语句使用
- leave：退出循环，配合循环使用
- iterate：跳过当前循环剩下的语句，直接进入下次循环，必须用在循环中
-
语法
循环名:loop SQL语句体 end loop 循环名;
(6)游标
-
游标是用来存储查询结果集的数据类型，再存储过程和函数中可以使用游标对结果集进行循环处理
-
声明游标
declare 游标名称 cursor for 查询SQL语句; -
打开游标
open 游标名称; -
获取游标记录
fetch 游标名称 into 变量[,变量2]; -
关闭游标
close 游标名称;
(7)条件处理程序
-
用于在流程控制结构执行过程中定义，遇到问题时对应执行的步骤
-
DECLARE … HANDLER：声明条件处理程序，用于指定在满足某些条件时执行的操作。
-
CONDITION：指定要处理的条件，例如某个SQLSTATE值或一个MySQL错误代码。
-
EXIT、CONTINUE、UNDO：指定条件处理程序的执行方式。
- EXIT：终止存储过程。
- CONTINUE：继续执行存储过程。
- UNDO：回滚存储过程（MySQL暂不支持）。
-
案例
DELIMITER // -- 设置新的语句结束符为 // ，以便可以定义包含分号的存储过程 -- 创建名为 AddStudent 的存储过程，接收两个输入参数 CREATE PROCEDURE AddStudent(IN student_id INT, IN student_name VARCHAR(50)) BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -- 声明一个条件处理程序，当发生SQL异常时执行以下代码 BEGIN -- 处理异常的代码，可以记录错误或执行其他操作 SELECT 'Duplicate key error occurred. Handling error.' AS ErrorMessage; -- 当发生异常时，返回一条错误消息 END; INSERT INTO students (id, name) VALUES (student_id, student_name); -- 尝试向 students 表中插入一条新记录 -- 如果插入成功，则返回成功消息 SELECT 'Student added successfully.' AS SuccessMessage; -- 如果插入成功，返回一条成功消息 END // DELIMITER ; -- 恢复默认的语句结束符 ;
-
7、触发器
（1）介绍
MySQL触发器（Trigger）是一种在对表进行INSERT、UPDATE或DELETE操作时自动执行的一种特殊类型的存储过程。触发器通常用于强制业务规则、自动审计、更改历史记录等。触发器在特定事件发生时被触发，可以在事件之前（BEFORE）或之后（AFTER）执行。
（2）特点
- 自动执行：在表上的特定操作发生时自动执行，无需手动调用。
- 事件驱动：基于INSERT、UPDATE或DELETE事件。
- 作用范围：可以在事件发生前（BEFORE）或后（AFTER）执行。
- 表级别：每个触发器与特定的表关联。
（3）语法
-
创建
create trigger 触发器名 before/after insert/update/delete on 表名 FOR EACH ROW 操作SQL语句; -
查看
show triggers -
删除
drop trigger [指定数据库名]触发器名;
8、锁
①概述
协调计算机进程、线程并发访问分配资源机制，MySQL中加锁后只能读
②全局锁
-
介绍：锁定数据库实例中的所有表，实例就只读
-
执行流程：加锁 -> SQL操作 -> 释放锁
-
语法
flush tables with read lock; # 上全局锁 mysqldump -uroot -p123456 itcast>itcast.sql # 备份数据 unlock tables; # 拆锁
③表级锁
（1）表锁
-
表共享读锁（read lock）
- 并发读取：多个事务可以同时持有共享读锁，从而允许并发读取。
- 阻止写入：在有共享读锁的情况下，任何事务都不能对数据进行修改（不能加独占写锁）。
-
表独占写锁（write lock）
- 独占访问：持有独占写锁的事务可以读取和修改数据，其他事务在锁释放之前不能对数据进行任何操作（读取或写入）。
- 确保一致性：独占写锁可以确保事务在修改数据时，不会被其他事务干扰，保证数据的一致性。
-
语法
# 加锁 lock tables 表名 read/write # 释放锁 unlock tables /客户端断开连接
（2）元数据锁
-
MDL操作加锁是系统自动控制，在访问一张表时会自动加上，MDL锁主要作用是维护表元数据一致性
-
表有活动事务时，不允许对元数据进行写入操作，为了避免DML和DDL冲突，保证读写正确性。
-
MySQL5.5的引入，对表增删改会加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、write兼容，与exclusive互斥 insert、update、delete、select…for update shared_write 与shared_read、write兼容，与exclusive互斥 alter table … exclusive 与其他MDL都互斥 -
查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks; -
意向锁
-
为了避免DML执行时，加的行锁和表锁有冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，从而减少表的检查。
-
意向共享锁（IS）
- 与表锁共享锁（read）兼容，与表锁排他锁（wirte）互斥
- 由语句select … lock in share mode添加;
-
意向排他锁（IX）
- 与表锁共享锁（read）及排它锁（wirte）都互斥，意向锁之间不会互斥
- 由insert、update、delete、select…for update添加；
-
查看意向锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-
（3）行级锁
锁住对应行数据，颗粒度小、冲突性低、并发度高，应用在innoDB引擎中。
-
行锁（record lock）-
锁定单个行记录的锁，防止其他事务对此进行update和delete，在rc、rr隔离级别下都支持。
-
共享锁（S）
- 允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
-
排它锁（x）
- 允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
-
查看行锁信息
selecy object_schema,object_name,index_name,lock_type,lock_mode,lock_data from oerformance_schema.data_locks;
SQL 行锁类型 说明 insert 排它锁 自动加锁 update 排它锁 自动加锁 delete 排它锁 自动加锁 select（正常） 不加任何锁 select…lock in share mode 共享锁 手动加锁 select…for update 排它锁 手动加锁 -
-
间隙锁（gap lock）- 锁定索引记录间隙中间数据（不包含该记录），确保索引间隙不变，防止其他事务在这个间隙进行insert，产生幻读，RR隔离级别下都支持
-
临键锁（next-key lock）
- 行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap，在RR隔离级别下都支持。
9、InnoDB引擎
（1）逻辑存储结构
扫一扫
1342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
