MySQL——黑马

• MySQL配置文件(my.cnf 或 my.ini) 一般位于 /etc/mysql/ 或 /etc/ 目录下。
• MySQL 数据目录 默认情况下是在 /var/lib/mysql/，这里存放了数据库的数据文件。
• 我安装的mysql
  • ip: 192.168.31.20
  • port: 3306
  • 用户名: root, mysql8_gtc
  • 密码: ROOT, gtc123

⭕️第一章: 基础篇⭕️

1. 数据类型

1.1 数值类型

1.2 字符串类型

1.3 日期时间类型

2. SQL

2.1 SQL分类

2.2 DDL

DDL用来定义，删除和修改数据库，表和字段。

2.2.1 DDL操作数据库

• 查询所有数据库

  SHOW DATABASES;

• 查询当前所处数据库

  SELECT DATABASES();

• 创建数据库

  CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

• 删除数据库

  DROP DATABASE [IF EXISTS] 数据库名;

• 使用数据库

  USE 数据库名;

2.2.2 DDL操作表

• 查询当前数据库所有表

  SHOW TABLES;

• 查询表结构

  DESC 表名;

• 查询指定表的建表语句

  SHOW CREATE TABLE 表名;

• 创建表

  CREATE TABLE 表名(
      字段1 字段1类型 [COMMENT 字段1注释],
      字段2 字段1类型 [COMMENT 字段2注释],
      字段3 字段1类型 [COMMENT 字段3注释],
      ......
      字段n 字段1类型 [COMMENT 字段n注释]
  ) [COMMENT 表注释];

• 修改表

  -- 添加字段
  ALTER TABLE 表名 ADD 字段名 字段类型(长度) [COMMENT 注释] [约束];
  
  -- 修改数据类型
  ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
  
  -- 修改字段名和字段类型
  ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
  
  -- 修改表名
  ALTER TABLE 表名 RENAME TO 新表名;

•  删除表

  -- 删除表
  DROP TABLE [IF EXISTS] 表名；
  
  -- 删除指定表，并重新创建该表
  TRUNCATE TABLE 表名;
  
  -- DROP删除表的数据+表的结构，而TRUNCATE只删除表的数据，不删除表的结构

选择使用 DROP TABLE 还是 TRUNCATE TABLE 应基于你是否希望保留表结构以及你需要执行的具体操作类型。如果你只是想清空表内的数据并保留表结构，TRUNCATE TABLE 是更合适的选择。如果你不再需要这个表及其数据，则应使用 DROP TABLE。

2.3 DML

DML用来对数据库中表的记录进行增删改操作。

2.3.1 增加-INSERT

• 给指定字段添加数据

  INSERT INTO 表名(字段1， 字段2, ...) VALUES(值1, 值2, ...);

• 给全部字段添加数据

  INSERT INTO 表名 VALUES (值1, 值2, ...);

• 批量添加数据

  -- 批量添加指定字段
  INSERT INTO 表名(字段1, 字段2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
  
  -- 批量添加全部字段
  INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

• 注意：
  • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
  • 字符串和日期数据应该包括在引号中。
  • 插入的数据大小，应该在字段的规定范围内。

2.3.2 修改-UPDATE

• 修改数据

  UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ... [WHERE 条件];

  注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

2.3.3 删除-DELETE

• 删除数据

  DELETE FROM 表名 [WHERE 条件];

  注意：DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。DELETE语句是按照行删除的，不能删除某一个字段的值（可以使用UPDATE）

2.4 DQL

2.4.1 单表查询

（1）基础查询

• 查询多个字段

-- 查询指定字段
SELECT 字段1, 字段2, 字段3, ... FROM 表名；

-- 查询所有字段
SELECT * FROM 表名；

• 设置别名

SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], ... FROM 表名；

• 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

（2）条件查询

• 语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

• 条件

（3）聚合函数

• 介绍：将一列数据作为一个整体，进行纵向计算。注意：NULL值不参与运算
• 常见聚合函数
  • count：统计数量
  • max：统计最大值
  • min：统计最小值
  • avg：平均值
  • sum：求和
• 语法

SELECT 聚合函数(字段列表) FROM 表名;

（4）分组查询⭐️⭐️⭐️

分组查询 === 一组一行数据 === 聚合函数也是得到一行数据

分组查询使用的关键字是GROUP BY，通常要和聚合函数与HAVING一起。SELECT 列表中的所有字段，要么是 GROUP BY 中出现的字段，要么必须是聚合函数（如 MAX、MIN、COUNT、SUM 等）的参数。

GROUP BY 子句在 SQL 查询中并不强制要求与聚合函数一起使用，但通常情况下它们是搭配使用的。这是因为 GROUP BY 的主要作用是将数据按照一个或多个列的值进行分组，而聚合函数（如 COUNT(), SUM(), AVG(), MAX(), MIN() 等）则用于对每个分组计算汇总值。

• 语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

• where和having区别
  • 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；而having是分组之后对结果进行过滤。
  • 判断条件不同：where不能对聚合函数进行判断，而having可以。

分组查询案例：

有如下一个部门表：

分析执行：

select deptno,sum(sal)  AS total  from employee where sal>1000 group by  deptno having sum(sal) >9000 
order by sum(sal) asc;

分析过程：

该SQL的书写顺序：

select, from, where, group by, having, order by

实际的执行顺序:

 from, where, group by, having, select, order by

一: 执行from employee

select * from employee;

二: 执行where sal > 1000

select * from employee where sal > 1000;

三: 执行group by deptno

select deptno from employee where sal > 1000 group by deptno;

四: 执行having sum(sal) > 9000

对每个分组，分别求sum(sal)，只有sum(sal) > 9000 的分组才会显示。

五: 执行select deptno, sum(sal) as total

计算每个分组的sum(sal)

六: 执行order by

（5）排序查询

• 语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

• 排序方式
  • ASC：升序（默认）
  • DESC：降序
• 注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

（6）分页查询

• 语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

• 注意
  • 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数
  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

（7）执行顺序⭐️⭐️⭐️ 

• 书写顺序

1. select
2. from
3. where
4. group by
5. having
6. order by
7. limit

• 执行顺序

1. from -- 行过滤
2. where
3. group by
4. having
5. select -- 列过滤
6. order by
7. limit

一句话：select在分组查询group by ... having ...之后。

where是分组之前过滤，having是分组之后过滤。

2.4.2 多表查询

多表查询见第5小节: 多表查询。

2.5 DCL

DCL是数据控制语言，用来管理数据库用户和控制数据库的访问权限。

2.5.1 DCL用户管理

（1）查询用户

USE mysql;
SELECT * FROM user;

mysql中，用户信息保存在mysql数据库的user表里。

（2）创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

（3）修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

（4）删除用户

DROP USER '用户名'@'主机名';

案例：

-- 创建用户itcast, 只能够在当前主机localhost访问, 密码123456
create user 'itcast'@'localhost' identified by '123456';

-- 创建用户heima, 可以在任意主机访问该数据库, 密码123456
create user 'heima'@'%' identified by '123456';

-- 修改用户heima的访问密码 1234;
alter user 'heima'@'%' identified with mysql_native_password by '1234';

-- 删除itcast@localhost用户
drop user 'itcast'@'localhost';

注意：

• 主机名可以使用%通配
• 这类SQL开发人员操作的比较少，主要是DBA（Database Administrator数据库管理员）使用。

2.5.2 DCL权限控制

mysql数据库可以被不同用户登陆，但是不同用户对于数据库的操作权限可能不一样。

权限: mysql登陆用户操作某数据库的权限。

（1）查询权限

SHOW GRANTS FOR '用户名'@'主机名';

（2）授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

（3）撤销权限

REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';

3. 函数

函数是指一段可以直接被另一段程序调用的程序或代码。函数是mysql内置的。

3.1 字符串函数

案例：

SELECT CONCAT('Hello', ' MySQL'); -- 得到'Hello MySQL'

SELECT LOWER('Hello'); -- 得到'hello'

SELECT UPPER('Hello'); -- 得到'HELLO'

SELECT LPAD('01',5,'-'); -- 得到'---01'

SELECT RPAD('01',5,'-'); -- 得到'01---'

SELECT TRIM(' Hello MySQL ') -- 得到'Hello MySQL'

SELECT SUBSTRING('Hello MySQL',1,5); -- 得到'Hello'

3.2 数值函数

3.3 日期函数

3.4 流程函数

4. 约束

4.1 约束概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据；

目的：保证数据库中数据的正确，有效和完整性。

4.2 约束的分类

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

4.3 约束演示

根据需求，完成表结构的创建。

CREATE TABLE user(
	id int PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
	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 '性别'
) COMMENT '用户表';

4.4 外键约束

4.4.1 概念

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

注意：目前上述的两张表，在数据库层面，并没有建立外键关联，所以是无法保证数据的一致性和完整性。

4.4.2 外键的建立

• 添加外键

-- 第一种方式
CREATE TABLE 表名(
    字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

-- 第二种方式
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

当dept_id被设置为外键之后，再去删除dept表的'1研发部'就无法成功删除（因为emp表里还存在dept_id=1的员工）

• 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键;

4.4.3 外键删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;

执行这条语句后，若删除dept表id=1的研发部，则emp表中dept_id=1的数据也会删除。

5. 多表查询

前面讲了单表查询的DQL，这一节讲解多表查询的DQL。

5.1 多表关系

5.1.1 一对多

• 案例：部门与员工的关系
• 关系：一个部门可以有多个员工，一个员工对应一个部门
• 实现：在多的一方建立外键，指向一的一方的主键

5.1.2 多对多

• 案例：学生与课程的关系
• 关系：一个学生可以选择多个课程，一个课程也可以被多个学生选择
• 实现：建立第三章中间表，中间表至少包含两个外键，分别关联两方主键

5.1.3 一对一

• 案例：用户与用户详情的关系
• 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。
• 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

5.2 多表查询概述

• 概述：指从多张表中查询数据
• 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）。

笛卡尔积演示：

SELECT * FROM emp, dept;

消除上述笛卡尔积（隐式内连接）：

SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;

5.3 多表查询分类

• 连接查询
  • 内连接：查询A, B交集部分数据
  • 外连接
    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名
• 联合查询
• 子查询
  • 标量子查询：返回一个值
  • 列子查询：返回一列
  • 行子查询：返回一行
  • 表子查询：返回多行多列

5.4 连接查询

5.4.1 内连接查询

 内连接：查询A, B交集部分数据

• 隐式内连接

SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

• 显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

5.4.2 外连接查询

•  左外连接：表1所有数据+表2部分数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 条件...;

• 右外连接：表2所有数据+表1部分数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 条件...;

5.4.3 自连接查询

SELECT 字段列表 FROM 表A 别名A  JOIN 表A 别名B ON 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。

5.5 联合查询

 对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

5.6 子查询

概念：SQL语句中嵌套select语句为嵌套查询，又称子查询

select * from 表1 where 字段=(select 字段 from 表2);

子查询外的语句可以是insert、update、delete、select中的一个

5.7.1 标量子查询

# 根据销售部门的id查询员工信息
# 先分开查询
# 查询销售部门的id
select id from dept where name='销售部'; #id为4
# 查询销售部门中员工的信息
select * from emp where dept_id=4;
# 合并为一个查询
select * from emp where dept_id=(select dept.id from dept where dept.name='销售部' );

5.7.2 列子查询

# 列子查询
# 查询销售部和市场部的所有员工信息
# 查询销售部和市场部的id
select id from dept where name='销售部' or name='市场部'; #id为2 4
# 查询两个部门的所有员工
select * from emp where dept_id in (2,4);
# 合并
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');

5.7.3 行子查询

# 查询与张无忌的薪资及直属领导相同的员工信息
# 查询张无忌的薪资和直属领导
select salary, managerid from emp where name='张无忌';
# 查询与张无忌的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='张无忌');

5.7.4 表子查询

# 查询与鹿杖客和宋远桥的职位和薪资相同的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋远桥'));

5.7 多表查询演示

多表查询演示：

准备工作：部门表dept和员工表emp

create table dept (
    id int auto_increment primary key comment 'id',
    name varchar(50) not null comment '部门名称'
) comment '部门表';

insert into dept (id, name)
values (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办'),
       (6, '人事部');

create table emp(
    id int auto_increment primary key ,
    name varchar(50) not null ,
    age int,
    job varchar(20) comment '职位',
    salary int ,
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '所在部门id'
) comment '员工表';

insert into emp
values ( 1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5 ),
       ( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
       ( 3, '杨晓', 33, '开发', 8400, '2000-11-03', 2, 1 ),
       ( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
       ( 5, '陈玉存', 43, '开发', 10500, '2004-09-07', 3, 1 ),
       ( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
       ( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
       ( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),
       ( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
       ( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
       ( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
       ( 12, '何碧文', 19, '职员', 3750, '2007-05-09', 10, 2 ),
       ( 13, '东方白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
       ( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
       ( 15, '鱼梁洲', 38, '销售', 4600, '2004-10-12', 14, 4 ),
       ( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
       ( 17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null );

（1）内连接演示

内连接查询的是两表的交集。

（2）外连接演示

左外连接相当于查询表1的所有数据包含表1和表2交集的部分数据
右外连接相当于查询表2的所有数据包含表1和表2交集部分的数据

# 查询emp表的所有数据，和应于的部门信息(左)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
# 查询dept表的所有数据，和对于的员工信息(右)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;

左外连接结果：

左外连接哪怕左表的emp.dept_id=NULL，也要放进结果集。

注意：外连接NULL值也要返回

左外连接返回emp全部结果，右外连接返回dept全部结果

右外连接结果：

右外连接哪怕右表的dept.dept_id=NULL，也要放进结果集。

（3）自连接演示

自连接就是把同一张表，当作两张不同的表，然后进行内/外连接查询

# 查询员工及其所属领导的名字
# 自连接可以看成两张一样的表进行连接查询
select a.name, b.name from emp a join emp b on a.managerid=b.id;

（4）联合查询演示

union、union all
对于联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集
语法：

# 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union all
select * from emp where age>50;

# 没有all重复满足条件的只出现一次
# 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union
select * from emp where age>50;

对于联合查询的多张表的列数必须保持一致，字段类型也要保持一致
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重

6. 事务

6.1 事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些请求要么同时成功，要么同时失败。

Mysql数据库的事务是默认自动提交的。也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

在 MySQL 中，默认的 autocommit 模式是开启的（autocommit=1）。在这种模式下：

1. 自动开启事务：当你执行一条 DML 语句（如 INSERT, UPDATE, DELETE）时，MySQL 会隐式地为你开启一个新的事务（不需要你手动执行 START TRANSACTION 或 BEGIN）。
2. 自动提交事务：在该 DML 语句成功执行后，MySQL 会立即隐式地提交这个事务（相当于自动执行了 COMMIT），将更改永久保存到数据库中。

注意：InnoDB引擎支持事务，当autocommit=1时会自动提交，但是MyISAM不支持事务。

6.2 事务操作

6.2.1 方式一

• 查看/设置事务提交方式: select/set @@autocommit;
• 提交事务：commit
• 回滚事务：rollback

6.2.2 方式二

• 开启事务：START TRANSACTION / BEGIN
• 提交事务：commit
• 回滚事务：rollback

6.3 事务四大特性

• 原子性：(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性：(Consistency)：事务完成时，必须使所有的数据都保持一致状态
• 隔离性：（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性：(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

6.4 并发事务问题

• 脏读：一个事务读到另外一个事务还没有提交的数据
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

6.5 事务隔离级别

• 读未提交
• 读已提交
• 可重复读（mysql默认）
• 串行化

⭕️第二章: 进阶篇⭕️

1. 存储引擎

1.1 MySQL体系结构

MySQL 的体系结构可以分为四个主要层次：连接层、服务层、引擎层和存储层。每一层都有其特定的功能，共同协作完成数据库的管理和数据处理任务。

1. 连接层：
   • 连接层负责处理客户端与服务器之间的通信。当客户端尝试连接到 MySQL 服务器时，首先会通过网络接口（TCP/IP 或 Unix 域套接字）到达连接层。
   • 这一层包括了用户身份验证、权限检查以及连接管理等功能。它确保只有经过授权的用户才能访问数据库，并根据用户的权限限制其操作范围。
2. 服务层（SQL Layer）：
   • 服务层是 MySQL 的核心部分，包含了查询解析、分析、优化以及内置函数（例如日期、时间等）、存储过程、触发器、视图等功能。
   • 当收到 SQL 查询请求后，服务层会先对查询进行解析，生成解析树；然后进行语义和语法分析；接着是查询优化，决定最优执行计划；最后执行查询并返回结果。
   • 此外，服务层还负责缓存管理（如查询缓存），虽然在较新的 MySQL 版本中已经逐渐弃用了查询缓存功能，转而推荐使用外部缓存解决方案。
3. 引擎层（Storage Engine Layer）：
   • 引擎层是 MySQL 中非常重要的一个层次，因为它实现了数据的存储、检索和更新等功能。MySQL 支持多种存储引擎，比如 InnoDB、MyISAM、Memory 等，每种引擎都有其特点和适用场景。
   • 存储引擎之间是插件式的，可以根据需要灵活选择或切换。例如，InnoDB 提供事务支持、行级锁定和外键约束，适用于高并发环境；而 MyISAM 虽不支持事务，但在某些简单查询场景下性能可能更优。
4. 存储层：
   • 存储层负责实际的数据存储和文件管理。不同的存储引擎在这个层面上会有不同的实现方式。例如，InnoDB 存储数据时使用表空间的概念，而 MyISAM 则是将数据存储为三个文件：.frm（格式定义）、.MYD（数据）和 .MYI（索引）。
   • 数据库中的数据最终以文件形式保存在操作系统上，这些文件可以位于本地硬盘或者分布式存储系统中。

1.2 存储引擎简介

• 在创建表时，指定存储引擎

CREATE TABLE 表名(
    字段1    字段1类型 [COMMENT 字段1注释],
    ...
    字段n    字段n类型 [COMMENT 字段n注释]
) ENGINE = INNODB [COMMENT 表注释];

• 查看当前数据库支持的存储引擎

SHOW ENGINES;

1.3 InnoDB介绍

InnoDB存储引擎创建一张表之后，磁盘会生成xxx.ibd文件。该文件包括了表的结构，数据和索引。

InnoDB的三大特点：事务，外键和行级锁。

InnoDB的逻辑存储结构：

其中，磁盘是以页为单位（16KB）进行IO的，xxx.ibd文件=表空间文件。

1.4 MyISAM和Memory

1.4.1 MyISAM介绍

• 介绍：MyISAM是MySQL早期的默认存储引擎

• 特点

  • 不支持事务

  • 不支持外键

  • 不支持行级锁，支持表锁

  • 访问速度快

• 文件

  • xxx.sdi: 存储表结构信息

  • xxx.MYD: 存储数据

  • xxx.MYI: 存储索引

1.4.2 Memory介绍

• 介绍：Memory引擎的表数据是存储在内存中的，由于受到硬件问题，断电问题，只能将这些表作为临时表或缓存使用。
• 特点
  • 内存存放
  • hash索引（默认）
• 文件
  • xxx.sdi: 存储表结构信息（数据存在内存里）

2. 索引

2.1 概述

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

• 优点
  • 提高数据库检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
• 缺点
  • 索引列需要占用空间
  • 索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

2.2 索引结构介绍

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

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

2.2.1 B树

B树可视化网站：B-Tree Visualization

以一颗最大度数为5的B树为例子（每个节点最多存储4个key，5个指针）：

• B树的数据保存在各个节点

2.2.2 B+树

B+树可视化网站：B+ Tree Visualization

以一颗最大度数为5的B+树为例子（每个节点最多存储4个key，5个指针）：

• B+树的所有数据保存在叶子节点
• B+树的叶子节点形成一个单向链表

MySQL索引数据结构对经典的B+树进行了优化。在原B+树的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+树，提高了区间访问的性能。InnoDB引擎的B+树的一个节点大小为16KB（一页）。 

2.2.3 Hash

Hash索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果多个键值，映射到一个相同的槽位上，他们就产生了哈希冲突，可以通过链表来解决。

• Hash索引特点
  • 只能用于对等比较(=, in)，不支持范围查询（between, >, <, ...）
  • 无法利用索引进行排序操作
  • 查询效率高，通过只需要一次检索就可以了，效率通常高于B+tree索引

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

2.3 索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

• 聚集索引：将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据（必须有，而且只能有一个）
• 二级索引：将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键（可以存在多个）

聚集索引选择规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

回表查询：

以select * from user where name = 'Arm';为例子，先走二级索引得到主键id，再走聚集索引，就是回表查询。

2.4 索引语法

2.4.1 创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_col_name,...);

2.4.2 查看索引

SHOW INDEX FROM table_name;

 2.4.3 删除索引

DROP INDEX index_name ON table_name;

综合案例：

-- 创建tb_user表并插入数据
CREATE TABLE tb_user (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    phone VARCHAR(20),
    email VARCHAR(255),
    profession VARCHAR(255),
    age INT,
    gender INT,
    status INT,
    createtime DATETIME
);

INSERT INTO tb_user (id, name, phone, email, profession, age, gender, status, createtime) VALUES
(1, '吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, 1, 6, '2001-02-02 00:00:00'),
(2, '曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, 1, 0, '2001-03-05 00:00:00'),
(3, '赵云', '17799990002', '1779990@139.com', '英语', 34, 1, 2, '2002-03-02 00:00:00'),
(4, '孙悟空', '17799990003', '1779990@sina.com', '工程造价', 54, 1, 0, '2001-07-02 00:00:00'),
(5, '花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, 2, 1, '2001-04-22 00:00:00'),
(6, '大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, 2, 0, '2001-02-07 00:00:00'),
(7, '露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, 2, 0, '2001-02-08 00:00:00'),
(8, '程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, 1, 5, '2001-05-23 00:00:00'),
(9, '项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, 1, 0, '2001-09-18 00:00:00'),
(10, '白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, 1, 2, '2001-08-16 00:00:00'),
(11, '韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, 1, 0, '2001-06-12 00:00:00'),
(12, '荆轲', '17799990011', 'jingke123@163.com', '会计', 29, 1, 0, '2001-05-11 00:00:00'),
(13, '兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, 1, 1, '2001-04-09 00:00:00'),
(14, '狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, 1, 2, '2001-04-10 00:00:00'),
(15, '貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, 2, 3, '2001-02-12 00:00:00'),
(16, '妲己', '17799990015', '2873238293@qq.com', '软件工程', 31, 2, 0, '2001-01-30 00:00:00'),
(17, '芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, 2, 0, '2000-05-03 00:00:00'),
(18, '嬴政', '17799990017', '8839434342@qq.com', '化工', 38, 1, 1, '2001-08-08 00:00:00'),
(19, '狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, 1, 0, '2007-03-12 00:00:00'),
(20, '安琪拉', '17799990019', 'jdodmlh@126.com', '城市规划', 51, 2, 0, '2001-08-15 00:00:00'),
(21, '典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, 1, 2, '2000-04-12 00:00:00'),
(22, '廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, 1, 3, '2002-07-18 00:00:00'),
(23, '后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, 1, 0, '2002-03-10 00:00:00'),
(24, '姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, 1, 4, '2003-05-26 00:00:00');

需求：

（1）查询tb_user表有哪些索引

SHOW INDEX FROM tb_user;

结果显示只有一个主键索引

（2）为name创建idx_user_name的索引

CREATE INDEX idx_user_name ON tb_user(name);

（3）为phone创建一个唯一索引

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

（4）创建联合索引

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

注意：创建联合索引的顺序，是有讲究的，更换顺序会造成不同的影响。

（5）为email创建索引

CREATE UNIQUE INDEX idx_user_email ON tb_user(email);

2.5 索引性能分析

我们在进行SQL优化之前，首先要定位哪些SQL语句执行的慢，需要优化。我们主要优化查询语句。

2.5.1 查询执行频次

MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次。

-- Com后有七个下划线
SHOW GLOBAL STATUS LIKE 'Com_______';

2.5.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位: 秒，默认10秒）的所有SQL语句的日志。

（1）检查慢查询日志的开关是否打开

SHOW VARIABLES LIKE 'slow_query_log';

（2）开启慢查询日志开关

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

（3）重启服务器，并查看慢查询日志

配置完毕之后，重启服务器，查看慢查询日志文件信息/var/lib/mysql/localhost-slow.log

2.5.3 show profiles

（1）show profiles

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling

SET profiling=1;

（2）profile详情

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时

# 查看每一条SQL的耗时情况
show profiles;

# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query_id;

# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

2.5.4 explain执行计划

explain或者desc命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

# 直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

EXPLAIN执行计划各字段含义：

• id: select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
• select_type: 表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
• type🌟: 表示连接类型，性能由好到差的连接类型为NULL, system, const, eq_ref, ref, range, index, all
• possible_keys🌟: 显示可能应用在这张表上的索引，一个或多个
• key🌟: 实际使用的索引，如果为NULL，则没有使用索引
• key_len🌟: 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
• ref: 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
• rows: MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，不准确
• filtered: 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
• Extra🌟: 额外信息。using index condition表示使用了索引，但是需要回表查询数据。using where; using index表示使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询。

2.6 索引使用规则

2.6.1 验证索引效率

tb_sku表有1000万条数据。其中第一条数据为

（1）执行select * from tb_sku where sn = '100000003145001'，发现耗时20秒，原因是该表只对主键id建立了主键索引，没有对字段sn建立索引。

（2）针对sn字段建立索引：create index idx_sku_sn on tb_sku(sn);

（3）再次执行select * from tb_sku where sn = '100000003145001'，耗时0.01秒。

2.6.2 最左前缀法则

最左前缀法则主要针对于联合索引。如果索引了多列，要遵守最左前缀法则，即查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃了某一列，索引将部分失效。

现有tb_user表，针对字段profession, age和status建立联合索引idx_user_pro_age_sta。根据最左前缀法则，条件必须有最左边的profession字段。

• select * from tb_user where profession = '软件工程' ✅（走联合索引）
• select * from tb_user where age = 18 ❌（走全表扫描）
• select * from tb_user where profession = '软件工程' and status = '0'✅（走联合索引，部分失效，即status索引失效）
• select * from tb_user where profession = '软件工程'  and age = 31 and status = '0' ✅（走联合索引）
• select * from tb_user where age = 31 and status = '0' and profession = '软件工程' ✅（走联合索引，效果同上，最左前缀法则不是指sql语句要最左，最左索引存在即可）
• select * from tb_user where profession = '软件工程' and age > 30 and status = '0' ✅ （走部分联合索引profession和age，由于使用了age范围查询，范围查询右边的列会失效）
• select * from tb_user where profession = '软件工程' and age >= 30 and status = '0' ✅（走联合索引，虽然使用了age范围查询，但是>=不会使右边的列失效）

联合索引中，出现范围查询（>, <), 范围查询右侧的列索引失效。但是使用>=不会失效。

2.6.3 索引失效情况

（1）索引列运算🔥

不要在索引列上进行运算操作，不然索引会失效。

执行select * from tb_user where phone = '17782756471';会走索引，因为我们对tb_user的phone建立了索引。

再执行select * from tb_user where substring(phone, 10, 2)=15。该语句会导致phone的索引失效（因为该sql语句对索引列phone进行了函数运算），导致结果走全表扫描。

（2）字符串不加引号🔥

字符串类型字段使用时，不加引号，虽然也可以查出来（全表扫描查），但是索引将失效。

• select * from tb_user where phone = '17782756471' ✅ （走联合索引）
• select * from tb_user where phone = 17782756471 ❌（走全表扫描）

（3）模糊查询🔥

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

‘软件%'是尾部模糊匹配，'%工程'是头部模糊匹配。

• 尾部模糊匹配：select * from tb_user where profession like '软件%'; ✅（走索引）

• 头部模糊匹配：select * from tb_user where profession like '%工程'; ❌（走全表扫描）

（4）or连接的条件🔥

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

• 执行select * from tb_user where id = 10 or age = 23; ❌（id有索引，但是age没有索引，走全表扫描）

由于age没有索引，即使id有索引，索引也会失效。所以需要针对于age也要建立索引。

（5）数据分布影响🔥

如果MySQL评估使用索引比全表扫描更慢，则会走全表扫描。

2.6.4 SQL提示

 SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

对于profession字段，我们对其建立了两个索引：idx_user_pro_age_sta联合索引和idx_user_pro单列索引。

use index（建议mysql使用该索引，但是mysql也可能不接受建议）

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

ignore index（忽略指定的索引）

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

force index（强迫使用该索引）

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

2.6.5 覆盖索引&回表查询

覆盖索引：尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

现有索引如下：

• id: 主键索引
• profession, age, status: 联合索引

执行以下sql语句：

• select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0'; （使用联合索引，不需要回表查询）
• select id, profession,age,stauts from tb_user where profession = '软件工程' and age = 31 and status = '0';（使用联合索引，不需要回表查询）
• select id, profession,age,stauts, name from tb_user where profession = '软件工程' and age = 31 and status = '0';（使用联合索引+主键索引，需要回表查询。因为name字段没有索引）
• select * from tb_user where profession = '软件工程' and age = 31 and status = '0';（使用联合索引+主键索引，需要回表查询。因为*里面包括很多没有建立索引的字段）

2.6.6 前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

• 语法

create index idx_xxx on table_name(column(n));

• 前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性时1，这是最好的索引选择性，性能也是最好的。

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;

2.6.7 单列&联合索引

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

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

假设有俩个单列索引：phone和name

执行：select id, phone, name from tb_user where phone='17799990010' and name = '韩信‘；

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

2.7 索引设计原则

1. 针对于数据量较大（一张表数据量超过100多万条），且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where），排序（order by），分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是越多多好，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

3. SQL优化

3.1 insert优化

• 批量插入

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

• 手动提交事务

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;

• 主键顺序插入
• 大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile=1;

# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主键顺序插入性能高于乱序插入

3.2 主键优化

• 主键设计原则
  • 满足业务需求的情况下，尽量降低主键的长度
  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
  • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
  • 业务操作时，避免对主键的修改

主键顺序插入涉及到的页分裂和页合并比较少，而乱序插入会导致很多的页分裂和页合并，影响性能。

3.3 order by优化

• Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

• Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

-- 没有创建索引时，根据age，phone进行排序
select id,age,phone,from tb_user order by age, phone;

-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

-- 创建索引后，根据age,phone进行升序排序
select id,age,phone from tb_user order by age,phone;

-- 创建索引后，根据age,phone进行降序排序
select id,age,phone from tb_user order by age desc, phone desc;

-- 创建索引
create index idx_user_age_phone_ad on tb_user(age, asc, phone desc);

-- 根据age,phone进行降序一个升序，一个降序
select id,age,phone from tb_user order by age asc, phone desc;

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

• 尽量使用覆盖索引。

• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256K）。

3.4 group by优化

• 分组操作时，可以通过索引来提高效率

• 分组操作时，索引的使用也是满足最左前缀法则的

3.5 limit优化

一个常见又非常头疼的问题是limit 200000,10，此时需要MySQL排序前2000010条记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引+子查询形式进行优化。

explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

3.6 count优化

select count(*) from tb_user;

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
• InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数。

count的几种用法：

• count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）
• count(字段)
  • 没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
  • 有not null约束：InnoDB引擎会遍历整张表把每一行的字段都取出来，返回给服务层，直接按行进行累加
• count(1)：InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字1进去，直接按行进行累加。
• count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键id) < count(1) 约等于 count(*)，所以尽量使用count(*)

3.7 update优化

该sql由于使用了id主键索引，因此InnoDB引擎加了行锁。

update student set no = '1234' where id = 1;

该sql使用了name，但是studnet表没有name索引，此时InnoDB引擎加的是表锁。

update student set no = '1234' where name = '小米';

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

4. 视图

4.1 介绍及基本用法

介绍：视图（View）是一种虚拟存在的表。视图中的数据并不在数据库汇中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

基本用法：

• 创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL] CHECK OPTION]

• 查询

-- 查看创建视图语句
SHOW CREATE VIEW 视图名称;
-- 查看视图数据
SELECT * FROM 视图名称;

• 修改

-- 方式一
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL] CHECK OPTION]
-- 方式二
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL] CHECK OPTION]

• 删除

DROP VIEW [IF EXISTS] 视图名称 [,视图名称]...

案例演示：

-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;

-- 查询视图
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id < 3;

-- 修改视图(两种方式)
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
alter view stu_v_1 as select id,name from student where id <= 10;

-- 删除视图
drop view if exists stu_v_1;

4.2 检查选项cascaded和local

向视图插入数据，实际上就是向视图对应的表中插入数据。

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除。以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL，默认值为CASCADED。

4.3 更新及作用

• 视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

1. 聚合函数或窗口函数（SUM(), MIN(), MAX(), COUNT()等）
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者 UNION ALL

• 视图的作用
  • 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
  • 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
  • 数据独立：视图可以帮助用户屏蔽真实表结构变化带来的影响。

5. 存储过程

5.1 介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

思想：数据库SQL语言层面的代码封装与重用。

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

5.2 基本语法

• 创建

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    --SQL语句
END;

• 调用

CALL 名称([参数]);

• 查看

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名';
-- 查询某个存储过程定义
SHOW CREATE PROCEDURE 存储过程名称;

• 删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

 注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。

5.3 变量

5.3.1 系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）

• 查看系统变量

-- 查看所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES;
-- 可以通过LIKE模糊匹配方式查找变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '...';
-- 查看指定变量的值
SELECT @@[SESSION|GLOBAL] 系统变量名;

• 设置系统变量

SET [SESSION|GLOBAL] 系统变量=值;
SET @@[SESSION|GLOBAL] 系统变量=值;

注意：

• 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
• mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置

5.3.2 用户自定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接"@变量名"使用就可以。其作用域为当前连接。

• 赋值

SET @var_name = expr [,@var_name=expr]...;
SET @var_name := expr [,@var_name:=expr]...;

SELECT @var_name := expr [,@var_name := expr] ...;
SELECT 字段名 INTO @var_name FROM 表名;

• 使用

SELECT @var_name;

注意：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

5.3.3 局部变量

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

• 声明

DECLARE 变量名 变量类型[DEFAUlT ...];

变量类型就是数据库字段类型：INT, BIGINT, CHAR, VARCHAR, DATE, TIME等。

• 赋值

SET 变量名=值;
SET 变量名:=值;
SELECT 字段名 INTO 变量名 FROM 表名...;

5.3 if判断

IF 条件1 THEN
    ...
ELSEIF 条件2 THEN
    ...
ELSE
    ...
END IF;

 5.4 参数IN, OUT, INOUT

• IN: 传入参数（默认）
• OUT: 传出参数
• INOUT: 传入传出参数

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])

BEGIN
    -- SQL语句
END;

5.5 case

• 语法一

CASE case_value
    WHEN when_value1 THEN statement_list1
    [WHEN when_value2 THEN statement_list2] ...
    [ELSE statement_list]
END CASE;

• 语法二

CASE
    WHEN search_condition1 THEN statement_list1
    [WHEN search_condition2 THEN statement_list2]...
    [ELSE statement_list]
END CASE;

5.6 循环

5.6.1 while

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

# 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
    SQL逻辑
END WHILE;

5.6.2 repeat

repeat是有条件的循环控制语句，当满足条件的时候推出循环。具体语法为：

# 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环。
REPEAT
    SQL逻辑
    UNTIL 条件
END REPEAT;

5.6.3 loop

LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其实现简单的死循环。LOOP可以配合一下两个语句使用：

• LEAVE: 配合循环使用，退出循环
• ITERATE: 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
    SQL逻辑
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE lable; -- 直接进入下一次循环

5.7 游标cursor

游标（cursor）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

• 声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

• 打开游标

OPEN 游标名称;

• 获取游标记录

FETCH 游标名称 INTO 变量[,变量];

• 关闭游标

CLOSE 游标名称;

5.8 条件处理程序handler

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR conditon_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代码的简写

6. 存储函数

存储函数是返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

CREATE FUNCTION 存储函数名称([参数列表])
RETURN type [characteristic ...]
BEGIN
    ---SQL语句
    RETURN ...;
END;

• characteristic说明
  • DETERMINISTIC: 相同的输入参数总是产生相同的结果
  • NO SQL: 不包含SQL语句
  • READS SQL DATA: 包含读取数据的语句，但不包含吸写入数据的语句

7. 触发器

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

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

• 创建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
    trigger_stmt;
END;

• 查看

SHOW TRIGGERS;

• 删除

DROP TRIGGER [schema_name.]trigger_name; -- 如果没有指定schema_name，默认为当前数据库

8. 锁

8.1 介绍

MySQL中的锁，按照锁的粒度，分为以下三类：

• 全局锁：锁定数据库中的所有表
• 表级锁：每次操作锁住整张表
  • 表锁
  • 元数据锁
  • 意向锁
• 行级锁：每次操作锁住对应的行数据
  • 行锁
  • 间隙锁
  • 临键锁

8.2 全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

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

案例演示：通过全局锁实现数据库db01的备份

（1）给MySQL服务器的数据库实例枷锁

FLUSH TABLES WITH READ LOCK; 是针对 MySQL 服务器实例上的所有数据库加锁，而不是仅仅针对某一个数据库。这个命令会关闭所有打开的表，并使用全局读锁定给所有的数据库和表上锁。这意味着在执行该命令之后，任何对数据库的写操作（包括更新、删除、插入等）都会被阻塞，直到对应的 UNLOCK TABLES; 命令被执行释放锁为止。此命令通常用于需要确保数据库的一致性状态的场景，比如进行备份时防止数据的不一致更改。

flush tables with read lock;

加完锁之后，其他客户端只能读，不能写（进入阻塞状态）。

（2）数据库db01备份成db01.sql

mysqldump -uroot -p1234 db01 > D:/db01.sql

db01.sql里保存的内容就是创建db01数据库的所有表和所有数据

（3）释放锁

unlock tables;

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

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

mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql

8.3 表级锁

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

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

• 表锁
  • 表共享读锁
  • 表独占写锁
• 元数据锁
• 意向锁
  • 意向共享锁
  • 意向排他锁

8.3.1 表锁

共享读锁（Shared Read Lock）

• 加锁者：能读，写报错
• 其他客户端：能读，写阻塞

• 特性：共享读锁允许多个会话同时对同一个表进行读取操作。也就是说，一旦一个表被加上了共享读锁，其他会话仍然可以获取该表的共享读锁来进行读操作，但不能进行写操作。
• 使用场景：当你需要确保在读取数据期间，没有其他会话能够修改这些数据时，就可以使用共享读锁。这通常用于确保查询结果的一致性。
• 如何加锁：可以通过 LOCK TABLES table_name READ; 对指定表加共享读锁。

独占写锁（Exclusive Write Lock）

• 加锁者：能读，能写
• 其他客户端：读阻塞，写阻塞

• 特性：独占写锁是一种排他锁，意味着一旦一个表被加上了独占写锁，其他的会话既不能获取读锁也不能获取写锁。只有持有独占写锁的会话可以对该表执行写操作（如更新、删除等），并且还可以执行读操作。
• 使用场景：当你需要确保在进行写操作时，没有其他会话能读取或写入相同的数据，以避免数据不一致的问题时，就可以使用独占写锁。
• 如何加锁：可以通过 LOCK TABLES table_name WRITE; 对指定表加独占写锁。

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

8.3.2 元数据锁

元数据锁，Meta Data Lock，MDL。MDL加锁过程是系统自动控制，无需显式使用。在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML和DDL冲突，保证读写的正确性。

在MySQL5.5引入MDL，当对一张表进行CRUD时，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）

查看元数据锁：

8.3.3 意向锁

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

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

查看意向锁和行锁：

8.4 行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC、PR隔离级别下都支持。
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在PR隔离级别下都支持。
• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在PR隔离级别下支持。

8.4.1 行锁

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

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

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

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
• InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

8.4.2 间隙锁&临键锁

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

• 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
• 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。
• 索引上的范围查询（唯一索引）会访问到不满足条件的第一个值为止。

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

9. InnoDB引擎

10. MySQL管理

⭕️第三章: 运维篇⭕️

1. 日志

2. 主从复制

3. 分库分表

4. 读写分离

⭕️第四章：MySQL八股⭕️

1. 基础篇

1.1 SQL语句的书写顺序和执行顺序

• 书写顺序
  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY
  7. LIMIT
• 执行顺序
  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

1.2 约束的分类

• 唯一约束UNIQUE
• 非空约束NOT NULL
• 主键约束 PRIMARY KEY
• 外键约束 FOREIGN KEY
• 默认约束DEFAULT
• 检查约束CHECK

1.3 多表查询有哪几种分类？

• 连接查询
  • 左外连接
  • 右外连接
  • 自连接
• 联合查询
• 子查询
  • 标量子查询
  • 行子查询
  • 列子查询
  • 表子查询

1.4 事务的四大特性是什么？

• 原子性：(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性：(Consistency)：事务完成时，必须使所有的数据都保持一致状态
• 隔离性：（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性：(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

1.5 并发事务有哪些问题？

• 脏读：一个事务读到另外一个事务还没有提交的数据
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

1.6 事务的隔离级别有哪些？

1.7 说一说三大范式

• 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
• 第二范式（2NF）：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
• 第三范式（3NF）：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

 2. 进阶篇

2.1 说说MySQL的存储体系

• 连接层：负责处理客户端与服务器之间的通信，包括用户身份验证，权限检查以及连接管理。
• 服务层：当收到 SQL 查询请求后，服务层会先对查询进行解析，生成解析树；然后进行语义和语法分析；接着是查询优化，决定最优执行计划；最后执行查询并返回结果。
• 引擎层：实现了数据的存储、检索和更新等功能。MySQL 支持多种存储引擎，比如 InnoDB、MyISAM、Memory 等，每种引擎都有其特点和适用场景。例如，InnoDB 提供事务支持、行级锁定和外键约束，适用于高并发环境；而 MyISAM 虽不支持事务，但在某些简单查询场景下性能可能更优。
• 存储层：存储层负责实际的数据存储和文件管理。不同的存储引擎在这个层面上会有不同的实现方式。例如，InnoDB 存储数据时使用表空间的概念，而 MyISAM 则是将数据存储为三个文件：.frm（格式定义）、.MYD（数据）和 .MYI（索引）。

2.2 不同存储引擎的特点

2.3 如何选择存储引擎

• InnoDB：是MySQL的默认引擎，支持事务，外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新，删除操作。
• MyISAM：读操作和插入操作为主，只有很少的更新和删除，并且对事务的完整性、并发行要求不是很高。
• Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。但是对表的大小有限制。

现在，使用MyISAM一般用MongoDB代替，Memory被Redis代替。

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

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

2.5 索引的分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

• 聚集索引：将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据（必须有，而且只能有一个）
• 二级索引：将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键（可以存在多个）

聚集索引选择规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

2.6 什么是回表查询？

先走二级索引找到对应的主键值，再根据主键值去聚集索引找到对应行数据。

2.7 InnoDB主键索引的B+tree存储多少数据？

前提假设：

• 一行数据=1KB
• 指针大小=6B
• 主键类型BIGINT=8B
• 假设一页保存的主键个数为n

若B+树的高度为2: 保存1.8w行数据

若B+树的高度为3: 保存2000w行数据