MySql

基础部分

一、DDL (表操作)

1.创建表

creat table 表名(
	字段1 字段类型 [约束] [comment 字段1注释],
    ...
    字段n 字段类型 [约束] [comment 字段n注释]
)[comment 表注释];

约束：作用于表中字段上的规则,用于限制存储在表中的数据

1.非空约束 not null: 限制该字段的值不能为空

2.唯一约束 unique: 保证字段的所有数据都是唯一的,不重复的

3.主键约束 primary key （auto_increment 自增 ）: 主键是一行数据的唯一标识，要求非空且唯一

4.默认约束 default: 保存数据时，如果未指定该字段值，则采用默认值

5.外键约束 foreign key: 让两张表的数据建立连接，保证数据的一致性和完整性

6.检查约束：check(MySQL8之后才支持)

• 检查约束

  create table t_stu(
    no int,
    name varchar(255),
    age int,
    check(age > 18)
  );
  

• 唯一性约束

  • 列级约束
  • 表级约束

  语法格式：

  create table t_stu(
    no int,
    name varchar(255),
    email varchar(255) unique
  );
  

  email字段设置为唯一性，唯一性的字段值是可以为NULL的。但不能重复。以上在字段后面添加的约束，叫做列级约束。
  当然，添加约束还有另一种方式：表级约束：

  create table t_stu(
    no int,
    name varchar(255),
    email varchar(255),
    unique(email)
  );
  

  使用表级约束可以为多个字段添加联合唯一。

  create table t_stu(
    no int,
    name varchar(255),
    email varchar(255),
    unique(name,email)
  );
  

  创建约束时也可以给约束起名字，将来可以通过约束的名字来删除约束：

  create table t_stu(
    no int,
    name varchar(255),
    email varchar(255),
    constraint t_stu_name_email_unique unique(name,email)
  );
  

  所有的约束都存储在一个系统表当中：table_constraints。这个系统表在这个数据库当中：information_schema

• 外键约束：假设给a字段添加了外键约束，要求a字段中的数据必须来自b字段，b字段不一定是主键，但至少要有唯一性。

添加外键：

create table t_school( 
  sno int primary key, 
  sname varchar(255) 
); 
create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  school_no int, 
  # constrain 为该约束起名：t_student_school_no_fk
  # foreign key(为哪个字段设为外键)
  # references后面指定 外键应用的是哪张表的哪个字段
  constraint t_student_school_no_fk foreign key(school_no) references t_school(sno)
);

关于外键的级联：

• 级联删除（谨慎使用！）

​ 删除父表数据的时候，子表会级联删除。这个特性默认是关闭的，可以手动开启。在创建表或者修改表的时候：外键添加：on delete cascade

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete cascade 
);

• 级联更新 on update cascade

  create table t_student( 
    no int primary key, 
    name varchar(255), 
    age int, 
    sno int, 
    constraint t_school_sno_fk foreign key(sno) references t_school(sno) on update cascade 
  );
  

• 级联置空 on delete set null

  • 在删除父表中的数据时，子表中的外键字段会自动设置为 null (置空)

  create table t_student( 
    no int primary key, 
    name varchar(255), 
    age int, 
    sno int, 
    constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete set null 
  );
  

2.MySQL数据类型

整数类型

tinyint：1个字节（微小整数）
smallint：2个字节（小整数）
mediumint：3个字节（中等大小的整数）
int（integer）：4个字节（普通大小整数）
bigint：8个字节（大整数）

浮点数类型

float：4个字节，单精度（最多5位小数）
double：8个字节，双精度（最多16位小数）

定点数类型

decimal：定点数类型。底层实际上采用字符串的形式存储数字。
语法：decimal(m, d)
例如：decimal(3, 2) 表示3个有效数字，2个小数。（有效数字最多65个，小数位最多30个）

日期和时间类型

year：1个字节，只存储年，格式YYYY
time：3个字节，只存储时间，格式HH:MM:SS / HHMMSS
date：3个字节，只存储年月日，格式：YYYY-MM-DD
datetime：8个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1000年~公元9999年）
timestamp：4个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1980年~公元2040年）或者格式为 YYYYMMDDHHMMSS（采用这种格式不需要使用单引号，当然你使用单引号也可以）

3.字符串类型

char

**char(m)：**m长度是0~255个字符。
固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。m表示列的长度，范围是 0～255 个字符。
例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当插入的字符长度大于4，则报错（除非超过4个长度之后都是空格字符，则空格字符会自动被删除用来保证插入的成功）。

varchar

**varchar(m)：**m长度是0~16383个字符
长度可变的字符串。varchar 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。
例如，varchar(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。varchar在值保存和检索时尾部的空格仍保留。

text

text类型：

• tinytext 表示长度为 255字符的 TEXT 列。
• text 表示长度为 65535字符的 TEXT 列。
• mediumtext 表示长度为 16777215字符的 TEXT 列。
• longtext 表示长度为 4294967295 或 4GB 字符的 TEXT 列。

enum

enum类型：

• 语法：<字段名> enum(‘值1’,‘值2’,…)
• 该字段插入值时，只能是指定的枚举值。

set

set类型：

• 语法：<字段名> set(‘值1’,‘值2’,‘值3’,…) 注意：值不可重复。
• 该字段插入值时，只能是指定的值。

4.二进制类型

BLOB类型：二进制大对象，可以存储图片、声音、视频等文件。

• blob：小的，最大长度65535个字节
• mediumblob：中等的，最大长度16777215个字节
• longblob：大的，最大长度4GB的字节

二、DML(数据操作)

1.添加数据 insert

指定字段添加数据：

insert into 表名(字段名1,字段名2) values(值1,值2);

全部字段添加数据

insert into 表名 values(值1,值2,...);

批量添加数据(指定字段)：

insert into 表名(字段名1,字段名2) values(值1,值2),(值1,值2);

批量添加数据(所有字段)

insert into 表名 values(值1,值2,...),(值1,值2,...)

注意事项：字符串和日期型数据应该包含在引号中,添加的字段与值一一对应

2.更改数据 update

修改数据:

update 表名 set 字段名 = 值1 , 字段名2 = 值2 ,... [where 条件];

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

3.删除数据 delete

删除数据:

delete from 表名 [where 条件];

注意事项：1.delete 语句的条件可以有也可以没有，如果没有条件，则会删除整张表的所有数据

​ 2.delete语句不能删除某一个字段的值（如果要操作，可以使用updata,将该字段的值置为null）

三、DQL(查询操作)

1.基本查询

查询多个字段：

select 字段1,字段2,... from 表名;

查询所有字段(通配符)

select * from 表名;

设置别名：

select 字段1 [as] 别名1 , 字段2 [as] 别名2 from 表名;

• 别名中有空格，需要用双引号或单引号将其括起来

  select ename, sal * 12 "year sal" from emp;
  

去除重复记录：

select distinct 字段列表 from 表名;

注意事项：通配符 在实际开发中尽量少用，不直观且影响效率*

​ 起别名时，别名如果带有空格，别名用引号引起来 eg: ‘张 三’

2.条件查询

条件查询语法：

select 字段列表 from 表名 where 条件列表;

以上语句的执行顺序是：

1. 执行from
2. 执行where条件过滤
3. 执行select，查询结果并输出到控制台

特殊运算符：

​ in(…) :在in之后的列表中的值，多选一

select * from 表名 where job = 2 or job = 3 or job = 4;
select * from 表名 where job in (2,3,4);

​ like 占位符：模糊匹配( _匹配单个占位符，%匹配任意个字符 )

• like查询 带有下划线_的数据时，如

  • select * from student where name like '%\_%'
    

    必须使用\对下划线进行转义，否则下划线会被识别为 匹配单个占位符

​ is null:是null

​ <>: 不等于

• is null ,null , in 三者

  • in是自动忽略 null的,因为 in 的执行原理是 采用 = 和 or 执行的

    • select * from emp where comm  in(NULL, 300);
      

      上述语句查询等同于：

      select * from emp where comm  = null or comm = 300;
      

      所以能查出来 comm = 300 的数据

  • not in 是不会自动忽略 null的，因为 not in 的执行原理是 <> 和 and 执行的， 所以在使用 not in时要提前过滤Null

    • select * from emp where comm not in(NULL, 300);
      

      上述语句等同于：

      select * from emp wherw comm <> null and comm <> 300;
      

      由于语句是 and 连接， 左边 comm <> null 的结果是 false, 整体语句的结果就是false,后面的 comm <> 300不会执行。

**多个条件连接用：and or **

• and和or同时出现，and优先级较高，会先执行，如果希望or执行，可以添加小括号。另外，遇到不确定的优先级时，可以通过添加小括号的方式来解决。

​ 错误写法：

select
  ename,sal,deptno
from
  emp
where
  sal < 1500 and deptno = 20 or deptno = 30;

此时会查出 sal >1500 的数据，因为 and的优先级较高，查询时会把 sal < 1500 and deptno = 20 结合在一起，deptno = 30 成了一个单独的查询条件。导致 deptno = 30 的数据全部都被查询出来。正确写法如下：

select
  ename,sal,deptno
from
  emp
where
  sal < 1500 and (deptno = 20 or deptno = 30);

• 查询 日期数据需要添加单引号

*.聚合函数

作用：将一列数据作为一个整体，进行纵向运算

语法：

select 聚合函数(字段列表) from 表名

五个函数：count max min avg sum

统计数量可以使用： count(*)	count(字段)	count(常量) ，推荐使用 count(*)

注意：null值不参与所有聚合函数的运算

count(*)和count(1)的效果一样，统计该组中总记录行数。
count(ename)统计的是这个ename字段中不为NULL个数总和。
例如：count(comm) 结果是 4，而不是14

3.分组查询

分组查询语法：

select 字段列表 [, 聚合函数] from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

注意事项：

​ 1.分组之后，查询的字段一般为要分组的字段和聚合函数，查询其他字段无任何意义

​ 2.执行顺序：where > 聚合函数 > having

where 和 having 区别：

​ 1.执行时机不同，where是分组之前进行过滤，不满足where条件，不参与分组；having 是对分组之后的结果进行过滤

​ 2.where不能对聚合函数进行判断，而having可以

eg: 在tb_emp表中查询入职时间(entrydate) 在 ‘2015-01-01’(包含) 以前的员工，并对结果根据职位(job)进行分组，获取员工数量大于等于2的职位(job)

select job , count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;

4.排序查询

排序查询语法：

select 字段列表 from 表名 [where 条件列表] [group up 分组字段名] order by 字段1 排序方式1 , 字段2 排序方式2

排序方式：asc: 升序(默认值) desc:降序

注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序,字段排序之间用逗号隔开

eg:根据入职时间entrydate,对公司的员工进行 升序排序，入职时间相同，再按照更新时间update_time进行降序排序

select * from tb_emp order by entrydate , update_time desc;

* distinct去重

在字段前添加 distinct，可以对查询出来的结果进行去重

注意： distinct只能出现在所有字段的最前面。当查询多个字段且使用distinct进行去重的时候，会把后面字段进行联合去重

no1	no2
1	2
1	3
3	2

联合去重后：两个字段查询的结果放一起考虑是不是一条数据。

*大写小写

• 转大写 upper ucase
• 转小写 lower lcase

# 查询所有员工名字，以大写形式展现
select upper(ename) as ename from emp;
select ucase(ename) as ename from emp;

# 查询员工smith的岗位、薪资（假如你不知道数据库表中的人名是大写、小写还是大小写混合）
select ename, job, sal from emp where upper(ename) = 'SMITH';

# 查询员工姓名，以小写形式展现
select lower(ename) as ename from emp;
select lcase(ename) as ename from emp;

* 字符串处理

• 截取字符串

   substr(被截取的字符串，起始下标，截取长度)；
  

  起始下标是从1 开始，不是从0开始。（-1表示右侧开始的第一个位置）

  如：找出员工名字中第二个字母是A的

   select ename from emp where substr(ename,2,1) = 'A';
   # 模糊查询同样可以实现
   select ename from emp where ename like '_A%';
  

• 获取字符串长度 length (一个汉字两个长度)

  select length('hello')
  

• 获取字符的个数 char_length

  select char_length('hello')
  

• 字符串拼接 concat(‘字符串1’，‘字符串2’，‘字符串3’)

  select concat('wo','ai','zhongguo');
  

• 去除字符串前后空白 trim,默认是去除前后空白，也可以去除指定的前缀后缀

  # 去除前后空白
  select concat(trim('  abc  '), 'def'); //abcdef
  # 去除前置0
  select trim(leading '0' from '000111000'); //111000
  # 去除后置0
  select trim(trailing '0' from '000111000'); 111000
  # 前置0后置0都去掉
  select trim(both '0' from '000111000'); // 111
  

* 数学相关

• rand() 生产0~1的随机浮点数

  rand(x) 生产0~1的随机浮点数，通过指定整数x来确定每次获取到相同位数的浮点值

• round(x) 四舍五入，保留整数

  round(x.y) 四舍五入，保留y为小数

• truncate(x,y) 保留y位小数，剩下的全部舍去

• ceil(x)函数：返回大于等于数值x的最小整数：向上取整

• floor(x)函数：返回小于等于数值x的最大整数，向下取整

* 空处理

• ifnull(x,y),空处理函数，当x为null时，将x当作y处理

• ifnull(comm,0) 表示如果comm是Null时，当作0处理

  • 在SQL语句中，凡是有NULL参与的数学运算，最终计算出来的结果都是null，如：

    #需求：查询每个员工的年薪。（年薪 = (月薪 + 津贴) * 12个月。注意：有的员工津贴comm是NULL。）
    select ename,(sal + comm) * 12 as yealsal from emp;
    

    ename	yearsal
    SMITH	null

    上述sql语句查出来的结果中，如果有员工comm为Null值，那么查出来的年薪也会为Null值

    此时就需要用到空处理，ifnull函数：

    select ename,(sal + ifnull(comm,0) * 12 as yealsal from emp;
    

* 日期和时间相关函数

• 获取当前日期和时间 now() , sysdate()
  • now():获取执行sql语句的时刻
  • sysdate():获取执行 sysdate()函数的时刻
• 获取当前日期 三种写法
  • curdate();
  • current_date();
  • current_date()
• 获取当前时间 三种写法
  • curtime();
  • current_time();
  • current_time()
• 获取单独的年月日时分秒
  • year(now())、month(now())、day(now())、hour(now())、minute(now())、second(now())
  • 获取“年月日”： data(now())
  • 获取“时分秒”：time(now())

5.分页查询

分页查询语法：

select 字段列表 from 表名 limit  起始索引,查询记录数

注意事项：

​ 1. 起始索引从0开始， 起始索引 = (查询页码 - 1) * 每页显示记录数

​ 2.分页查询是数据库的方言，不同的数据库有不同的实现，mysql是limit

​ 3.如果查询的是第一页数据，起始索引可以省略

*函数

if(表达式，tvalue,fvalue) : 当表达式为true时，取值tvalue,当表达式为false时，取值fvalue

case expr when value1 then result1 [when value2 then result2 …] [else result] end

四、多表设计

一对多：在多的一方添加外键，关联另一方的主键

一对一：在任意一方添加外键，关联另一方的主键

多对多：通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键

五、多表查询

1.内连接

内连接：查询两张表交集的部分

隐式内连接语法：

select 字段列表 from 表1 , 表2 where 条件

显示内连接语法：

select 字段列表 from 表1 [inner] join 表2 on 连接条件...

eg:查询员工的姓名(name)，以及所属的部门名称(name) (员工表：tb_emp 部门表：tb_dept)

select tb_emp.name , tb_dept.name from tb_emp , tb_dept where tb.emp.dept_id = tb_dept.id;

表名过长 ，可以给表起别名：

select e.name,d.name from tb_emp e , tb_dept d where e.dept_id = d.id;

2.外连接

左外连接：查询结果包含左边表1的所有字段

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...

右外连接：查询结果包含右边表2的所有字段

select 字段列表 from 表1 right [outer] join 表2 on 连接条件...

eg左外连接:查询所有员工的姓名 以及其所属部门的名称：

select e.name,d.name from tb_emp e left outer join tb_dept d on e.dept_id = d.id;

3.子查询

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

形式：

select * from t1 where column1 = (select column1 from t2 ...);

注意：子查询外部的语句可以是insert/ update/ delete / select 中的任何一个，最常见的是select

3.1 标量子查询

标量子查询：子查询返回的结果单个值，常用的操作符 = , <>, >,<…

eg:查询教研部的所有员工信息：

select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部' );

3.2 列子查询

**列子查询：子查询返回的结果是一列，常用操作符 in ，not in **

eg:查询“教研部”和“咨询部”的所有员工信息：

select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

3.3 行子查询

行子查询：子查询返回的结果是一行，常用操作符： = ，<> , in , not in

eg:查询与“韦一笑”的入职日期及职位都相同的员工信息

select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑')；

3.4 表子查询

表子查询：子查询返回的结果是多行多列,常作为临时表，常用操作符：in

eg:查询入职日期是 ‘2006-01-01’ 之后的员工信息，及其部门名称

// 获取入职日期是'2006-01-01'之后的员工信息
select * from tb_emp where entrydate > '2006-01-01'
//将获得的员工信息作为一张临时表 
select from (select * from tb_emp where entrydate > '2006-01-01') e , tb_dept d where e.dept_id = d.id;

exists、not exists

在 MySQL 数据库中，EXISTS（存在）用于检查子查询的查询结果行数是否大于0。如果子查询的查询结果行数大于0，则 EXISTS 条件为真。（即存在查询结果则是true。）

主要应用场景：

• EXISTS 可以与 SELECT、UPDATE、DELETE 一起使用，用于检查另一个查询是否返回任何行；
• EXISTS 可以用于验证条件子句中的表达式是否存在；
• EXISTS 常用于子查询条件过滤，例如查询有订单的用户等。

如下列场景：

| customer_id | customer_name |
±------------±--------------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
±------------±--------------+

±---------±------------±------------+
| order_id | order_price | customer_id |
±---------±------------±------------+
| 10 | 1000.0 | 1 |
| 20 | 2000.0 | 1 |
| 30 | 3000.0 | 2 |
| 40 | 4000.0 | 2 |
±---------±------------±------------+

查询先前有过订单的顾客，而订单信息保存在 t_order 表中，顾客信息保存在 t_customer 表中

以往我们可以使用子查询：

select * from t_customer c 
where t_customer 
in(select customer_id from t_order o where c.customer_id=o.customer_id) 

使用exists:

select * from t_customer c where exists(select * from t_order o where o.customer_id=c.customer_id);

查询没有下过单的用户：

select * from t_customer c where not exists(select * from t_order o where o.customer_id=c.customer_id);

in和exists的区别

IN和EXISTS都是用于关系型数据库查询的操作符，不同之处在于：

1. IN操作符是根据 指定列表中的值来判断是否满足条件，EXISTS操作符则是根据子查询查询的结果是否有返回记录集来判断
2. EXISTS操作符通常比IN操作符更快，尤其是在子查询返回记录数很大的情况下。因为EXISTS只需要判断是否存在符合条件的记录，而IN操作符需要对比整个列表，因此执行效率相对较低
3. IN操作符可以同时匹配多个值，而EXISTS操作符只能匹配一组条件。

union & union all

union和union all 都可以对 查询的结果集进行去重

union会对合并后的结果集进行去重，而union all不会进行去重操作

所以两者都可满足要求的话，优先选择union all，因为不需要去重所以效率高一些

六、事务

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

语句：

开启事务： start transaction /  begin;
提交事务： commit;
回滚事务： rollback;

**事物的四大特性ACID：原子性 一致性 隔离性 持久性 **

**A原子性：**事务是最小的工作单元，不可再分

**C一致性：**事务要求要么同时成功，要么同时失败，事务前后的总量不变

I隔离性：事务与事务之间具有隔离性，保证互不干扰

D持久性：持久性是事务结束的标志

七、索引

索引是帮助数据库高效获取数据数据结构

优点：提高数据查询的效率，降低数据的IO成本 、 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：索引会占用存储空间、索引大大的提高了查询效率，但同时降低了insert 、delete、update的效率。

*语法：

​ 创建索引：

create [unique] index 索引名 on 表名 (字段名 , ...);

​ 索引命名规范：idx_表名 _字段名

​ 查看索引：

show index from 表名;

​ 删除索引：

delete index 索引名 on 表名;

注意事项：主键字段，在建表时，会自动创建主键索引

​ 添加唯一约束时，数据库实际上会添加唯一索引

索引的结构：B+tree

重要部分

• 使用 source sql脚本绝对路径 可以导入无限大小的sql语句

数据库的三大范式

数据库三大范式，其实是理论上的一个东西。在实际开发的过程当中，也存在可能拿冗余换速度，拿空间换速度，最终的设计目标还是为了满足用户的需求。用户觉得慢，我们就需要优化，优化的过程中，难免拿空间换速度的现象。

第一范式：所有表都应该有主键，且表的字段都具有原子性不可再分

第二范式：在满足第一范式的基础上，满足 所有非主键字段都必须完全依赖于主键，不能有部分依赖的情况。

第三范式：建立在第二范式的基础上，满足 所有非主键字段不可以传递依赖于主键字段。

• 第一范式反例：

• 第二范式反例：

• 第三范式反例：
  

实际上我们可以看出，第一范式是必须的，因为主键必须存在，而其他范式的约束始终都是为了节省空间，减少数据的冗余。

数据表多种关系的设计

一对多

多的一方设外键

多对多

建立第三张表，表中设两个外键

一对一

两种方案：

• 主键共享：

• 外键唯一：

视图

1. 只能将select语句创建为视图。

2. 创建视图

   create or replace view v_emp as select语句 
   

   通过select查询出来的结果集会被视图 v_emp拿到

3. 视图作用

   1. 如果开发中有一条非常复杂的SQL,且这条SQL在多处被使用，会给开发和维护带来成本。使用视图可以降低开发和维护的成本。
   2. 视图可以隐藏表的原来字段名。

4. 修改视图

   alter view v_emp as select语句
   

5. 删除视图

   drop view if exists v_emp;
   

6. 对视图增删改（insert delete update ）可以影响到原表数据

​

事务

1.四大特性：ACID

1. 原子性：事务包含的所有操作要么同时发生，要么同时失败，事务是最小的执行单位
2. 一致性：指事务完成前后数据的总量是不变的（如转账，不管多少次转账操作，双方的金额总量是不变的）
3. 隔离性：事务与事务间具有隔离性。在用户并发访问一张数据库表的时候，数据库为每一个用户开启事务，不能被其他事务干扰，多个并发的事务之间要相互隔离。
4. 持久性：指事物一旦被提交了，对数据库中数据的改变就是永久性的。

• 在dos命令窗口开启事务：start transaction 或 begin
• 回滚事务： rollback
• 提交事务： commit

执行 rollback或者commit，一次事务都会结束

MySQL默认情况下采用的事务机制是自动提交，即只要执行一条SQL语句就提交一次。只要事务提交了，就具有了持久性。

2.事务隔离级别

读未提交<读提交<可重复读<串行化

MySQL默认隔离级别三档起步：可重复读

Oracle数据库隔离级别默认二档起步：读提交

三种现象：脏读、不可重复读、幻读

脏读:读到了 未提交 的数据

不可重复读： 倾向于更改操作，在读同一个数据的，重复读发现数据变化了

幻读：更倾向于 增删操作 读取到的结果集发生了变化

隔离级别	脏读	不可重复读	幻读
读未提交(read uncommitted)	存在	存在	存在
读提交(read committed)	不存在	存在	存在
可重复读(repeatable read)	不存在	不存在	存在
串行化(serializable)	不存在	不存在	不存在

查看与修改事务的隔离级别：

查看事务隔离级别：

• 查看当前会话的隔离级别： select @@session.transaction_isolation;
• 查看全局的隔离级别： select @@gobal.transaction_isolation;

设置事务隔离级别：

• 会话级： set session transaction isolation level read committed;
• 全局级： set global transaction isolation level read committed;

3.关于可重复读的幻读问题：

在这个隔离级别，是Mysql默认的事务隔离级别，Mysql会尽可能的避免幻读现象，但是仍然存在幻读现象。

了解可重复读隔离级别的幻读问题，我们首先要知道快照读和当前读，因为可重复读就是依赖这两种读实现解决幻读现象的问题的。

​

• 快照读 普通查询语句

  select * from emp;
  

  普通的select语句采用的都是快照读，底层的实现原理是：

  • 底层由MVCC（多版本并发控制）实现。在开启事务后，执行第一个查询语句时，底层会创建一个 Read View 对象，后续查询相同的数据时，会通过这个 Read View 在 undo log版本链中找到事务开始时的数据。所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新记录，我们也是查询不出来这条记录的，这就解决了幻读的问题。

• 当前读 在查询语句后 加 for update

  select * from emp for update;
  

  在执行 insert,update,delete语句之前，都会默认在底层执行一次 ‘当前读‘ 来获取当前最新的数据。当前读，就是读取到最新的数据。底层的实现原理是：

  • 对查询范围内的数据进行加锁：next-key lock,即 间隙锁+记录锁。通过加锁的方式来避免其他事务对这个范围内的数据进行修改，也就是说这个select语句查询到的数据是不允许并发的，只能排队执行，从而解决幻读问题**。间隙锁是用来保证范围内不允许进行 insert 插入操作**， 记录锁是用来保证范围内不允许进行 update 和 delete 操作。

在可重复读隔离级别下，我们在同一个事务操作中，保持前后都使用快照读或者都使用当前读，可以有效避免幻读现象。但是要注意任然不能绝对避免幻读现象，以下两种情况任然会出现幻读现象：

• 第一种：A事务和B事务，A事务第一次使用快照读查询数据，第二次使用当前读查询数据，而在A事务两次查询之间如果B事务对数据进行了增删改操作，会出现幻读现象
• 第二种：A事务和B事务，A事务使用快照读查询数据，然后B事务插入了一条数据或删除了一条数据，A事务中对数据进行了一次更新操作再查询数据，会出现幻读现象。因为 在 A事务进行更新操作是底层会默认进行一次 当前读 查询最新的数据。

存储过程

什么是存储过程？

存储过程就是数据库内置的编程语言，这种编程语言有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式串联起来，执行存储过程就是将这些SQL语句按照逻辑去执行，所以一个存储过程也可以看作是一组为了完成特定功能的SQL语句集。

每一个存储过程都是一个数据库对象，就像table和view一样，存储在数据库中，一次编译永久有效。

存储过程的优点和缺点

• 优点：速度快。降低了 应用服务器（Tomcat） 和 数据库服务器 之间网络通讯的开销。尤其是在数据量庞大的情况下效果显著

• 缺点：移植性差。编写难度大，难以维护。

  • 移植性差：存储过程是具有数据库特色的。也就是说每一款数据库都有属于自己的存储过程的语法规则。
  • 编写难度大，难以维护：对于存储过程这种语法（编程语言）来说，没有像心爱的idea这样的集成开发环境，所以编码速度低，难以维护。

MySQL的变量

mysql中的变量包括： 系统变量、用户变量、局部变量

游标cursor

游标（cursor） 可以理解为一个指向结果集中某条记录的指针，允许逐一的访问结果集中每条记录，并对结果集进行逐行操作。

使用过程：

1. 使用游标时需要在存储过程或函数中定义一个游标变量，并通过 declare 语句进行声明和初始化。注意游标的声明必须放在局部变量声明的后面。
2. 使用 open 语句打开游标
3. 使用 fetch 语句逐行获取游标指向的记录，并进行处理。注意：每次fetch读取的都是结果集中的某一行数据，想要读取结果集中所有行数据可以用循环进行包裹。
4. 使用 close 语句**关闭游标，**释放资源。

使用游标的优点：

• 提高数据库查询的灵活性和效率

四个步骤相关的语法：

• declare

  declare 游标名称 cursor for 结果集（查询语句）;
  

• open

  open 游标名称;
  

• fetch

  fetch 游标名称 into 变量[变量1,变量2,..];
  

• close

  close 游标名称;
  

案例：从dept表查询部门编号和部门名，创建一张新表dept2，将查询结果插入到新表中

drop procedure if exists p3;
create procedure p3()
BEGIN
   /* 声明变量 */
   declare dept_no int ;
   declare dept_name varchar(255);


--   声明游标 声明游标的语句必须放在声明普通变量的后面
    declare dept_cursor cursor for select deptno,dname from dept;
    
    -- 删除表
    drop table if exists dept2;
    
    -- 创建表
    create table dept2(
      deptno int,
      dname varchar(255)
    );
    
--   打开游标
    open dept_cursor;
--   通过游标取数据
    -- fetch dept_cursor into dept_no , dept_name;
    
   -- 查看变量的值
   -- select dept_no,dept_name; 
   
   -- 使用循环从游标中取数据，依次放入dept2表的对应字段中
   while true do 
    fetch dept_cursor into dept_no , dept_name;
    insert into dept2(deptno,dname) values(dept_no,dept_name);
   end while; 
--   关闭游标
    close dept_cursor;

END;

call p3;

异常捕捉和处理

语法格式：

declare handler_name handler for condition_value action_statemenst

1. handler_name: 表示异常处理程序的名称，重要取值包括：
   • continue: 表示异常发生后，程序不会停止，会正常执行后面的语句 （捕获）
   • exit： 发生异常后终止程序。 （上抛）
2. condition_value: 指捕获的异常，通常取值包括：
   • SQLSTATE sqlstate_value，例如：SQLSTATE ‘02000’
   • SQLWARNING，代表所有01开头的SQLSTATE
   • NOT FOUND，代表所有02开头的SQLSTATE
   • SQLEXCEPTION，代表除了01和02开头的所有SQLSTATE
3. action_statemenst 表示异常发生时要执行的语句，例如关闭游标： close cursor_name;

给上面的案例加上异常处理：当发生异常时，如果是 NOT FOUND异常，则退出，并执行关闭游标

drop procedure if exists p3;
create procedure p3()
BEGIN
   /* 声明变量 */
   declare dept_no int ;
   declare dept_name varchar(255);


--   声明游标 声明游标的语句必须放在声明普通变量的后面
    declare dept_cursor cursor for select deptno,dname from dept;
    
    -- 通常在这个位置进行异常的处理
    declare exit handler for not found close dept_cursor;
    
    -- 删除表
    drop table if exists dept2;
    
    -- 创建表
    create table dept2(
      deptno int,
      dname varchar(255)
    );
    
--   打开游标
    open dept_cursor;
--   通过游标取数据
    -- fetch dept_cursor into dept_no , dept_name;
    
   -- 查看变量的值
   -- select dept_no,dept_name; 
   
   -- 使用循环从游标中取数据，依次放入dept2表的对应字段中
   while true do 
    fetch dept_cursor into dept_no , dept_name;
    insert into dept2(deptno,dname) values(dept_no,dept_name);
   end while; 
--   关闭游标
    close dept_cursor;

END;

call p3;

存储函数

存储函数：带返回值的存储过程。参数只允许是 in（类似于java的形参，不用写in）

语法格式：

-- 创建函数
creat function 存储函数名称([参数列表] 参数名 参数类型) returns 参数类型 [特征]
begin
	
	-- 函数体
	return ...;
	
end;

-- 删除函数
drop function if exists 函数名

其中 ‘特征’ 的可能取值如下：

• deterministic：用该特征标记该函数为确定性函数（什么是确定性函数？每次调用函数时传同一个参数的时候，返回值都是固定的）。这是一种优化策略，这种情况下整个函数体的执行就会省略了，直接返回之前缓存的结果，来提高函数的执行效率。
• no sql：用该特征标记该函数执行过程中不会查询数据库，如果确实没有查询语句建议使用。告诉 MySQL 优化器不需要考虑使用查询缓存和优化器缓存来优化这个函数，这样就可以避免不必要的查询消耗产生，从而提高性能。
• reads sql data：用该特征标记该函数会进行查询操作，告诉 MySQL 优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果，从而提高查询性能；同时 MySQL 还会针对该函数的查询进行优化器缓存处理。

案例：计算 1~100 的偶数和

drop function if exists sum_fun;

create function sum_fun(n int) returns int DETERMINISTIC
begin 
  declare result int default 0;
  while n > 0 DO
    if n % 2 = 0  then
      set result := result + n;
    end if;
    set n := n -1;  
  end while;
  
  return result;


end;

/* 调用存储函数 */
set @result =  sum_fun(100);
select @result;

触发器

触发器是一种数据库对象。触发器可以在特定的数据操作（增删改） 触发时自动执行。

触发器的主要作用：

1. 强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。
2. 数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。
3. 执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总和等。

MySQL触发器分为两种类型，before和after。 before触发器在 insert,update,delete之前执行，after在之后执行。

创建触发器的语法：

create trigger trigger_name
before/after insert/update/delete/ on table_name for each row
begin
	-- 触发器要执行的SQL语句
end;

其中：

• trigger_name：触发器的名称
• BEFORE/AFTER：触发器的类型，可以是 BEFORE 或者 AFTER
• INSERT/UPDATE/DELETE：触发器所监控的 DML 调用类型
• table_name：触发器所绑定的表名
• FOR EACH ROW：表示触发器在每行受到 DML 的影响之后都会执行
• 触发器执行的 SQL 语句：该语句会在触发器被触发时执行

需要注意的是，触发器是一种高级的数据库功能，只有在必要的情况下才应该使用，例如在需要实施强制性业务规则时。过多的触发器和复杂的触发器逻辑可能会影响查询性能和扩展性。

**关于触发器中的两个关键字： new old **

• new 指新数据

• old 原本数据

  例如：OLD.column_name 从旧行中引用列值，也可以使用 NEW.column_name 从新行中引用列值。

案例：新建日志表 dept_log, 对dept表进行增删改时都需要在这张日志表中进行记录。

-- 新建日志表
drop table if exists oper_log;

create table oper_log(
  id bigint primary key auto_increment,
  table_name varchar(100) not null comment '操作的哪张表',
  oper_type varchar(100) not null comment '操作类型包括insert delete update',
  oper_time datetime not null comment '操作时间',
  oper_id bigint not null comment '操作的那行记录的id',
  oper_desc text comment '操作描述'
);

触发器1：向 dept 表中插入数据后，记录日志

drop trigger if exists trigger_dept_insert;

create trigger trigger_dept_insert
/* 触发规则 */
after insert on dept for each row 
begin
  /* 一旦触发之后要执行的SQL语句 */
  insert into oper_log (id,table_name,oper_type,oper_time,oper_id,oper_desc) values
  (null,'dept','insert',now(),new.deptno,concat('插入数据：deptno=',new.deptno,',dname=',new.dname,',loc=',new.loc));
end;

触发器2：向dept表中更新数据之后，记录日志

drop trigger if exists trigger_dept_update;

create trigger trigger_dept_update after update on dept for each ROW
begin
  insert into oper_log (id,table_name,oper_type,oper_time,oper_id,oper_desc) values
  (null,'dept','update',now(),new.deptno,concat('更新前：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc, 
                                              ',更新后：deptno=', new.deptno, ',dname=', new.dname,',loc=', new.loc));
end;

存储引擎

MySQL存储引擎决定了数据在磁盘上的存储方式和访问方式。不同的存储引擎实现了不同的存储和检索算法。

MySQL常见的存储引擎包括： InnoDB, MyISAM, Memory, Archive等

• InnoDB

  1. MySQL默认的事务型存储引擎，支持ACID事务
  2. 支持行级锁定。
  3. 具有较好的并发性能和数据完整性
  4. 适用于大多数应用场景，尤其是需要事务支持的应用

• MyISAM

  1. 是MySql早期版本的存储引擎
  2. 支持全文索引和表级锁定
  3. 不支持事务
  4. 由于其简单性和高性能，适用于某些特定的场景：如 读密集 的应用

• MEMORY

  1. 称为heap(堆)，是将表存储在内存中的存储引擎
  2. 具有非常高的读写性能，但数据会在服务器重启时丢失
  3. 使用于需要快速读写的临时数据集、缓存、临时表等

• CSV

  1. 将数据以纯文本格式存储的存储引擎

  2. 适用于需要处理和导入/导出CSV格式的数据

• Archive

  1. 能将数据高效的压缩和存储
  2. 适用于 长期存储的历史数据且不常访问的场景

索引

对索引的理解：

索引 index 也是MySQL中的一个对象，类似于 table表,view视图,proceduce存储过程等。

索引一般是加在某一个字段上的， 例如 emp 表中 有 ename 字段，这个字段中存储了大量的数据，我们就可以为这个ename字段加上索引来 提高ename字段中的数据的查找效率。

索引 index 本身类似于字典目录， 我们为字段加上索引后，就等于给该字段中的所有数据加上了目录，底层也就是为这些数据加上了某一种数据结构的存储方式。对应MySQL数据库来说，索引对应的数据结构是 B+ 树。

在没有添加索引的字段中，对于这些字段中的数据的查询都是 全表扫描

索引的创建与删除

unique约束的字段自动添加索引（所以主键也是默认自动添加索引的 ： 主键：主键索引， unique:唯一索引

给指定的字段添加索引

• 建表时添加：

  creat table table_name(
  	...
      id int,
      name varchar(255),
  	index index_name (name)
  );
  

• 建表完成后添加：

  alter table table_name add index index_name (name);
  

  create index index_name on emp(name);
  

删除指定字段上的索引

alter table emp drop index index_name ;

查看某张表上添加了哪些索引

show index from 表名

索引的分类

不同的存储引擎有不同的索引类型和实现：

• 按照数据结构进行分类：
  • B+树 索引：（InnoDB存储引擎采用的索引方式）采用B+树的数据结构
  • Hash 索引：（仅 memory 存储引擎支持）采用哈希表的数据结构 （哈希表：数组 + 单向链表）
• 按物理存储 分类
  • 聚集索引： 索引和表中数据一起，数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引
  • 非聚集索引：索引是单独独立于表空间的，一张表可以有多个非聚集索引。
• 按照 字段特性 分类：
  • 主键索引 （primary key）
  • 唯一索引 (unique)
  • 普通索引 (index)
  • 全文索引 (fulttext: 仅 InnoDB和 MyISAM存储引擎支持)
• 按照 字段个数 分类：
  • 单列索引 ：
  • 联合索引（组合索引、复合索引）

MySQL索引采用了B+树的数据结构

首先了解常见的树相关的数据结构：

• 二叉树
• 红黑树（自平衡二叉树）
• B树
• B+树

区别：树的高度不同。数的高度越低，性能越高。

• 二叉树：左小有大排序的树结构

  • 中序、前序、后续遍历：左根右、根左右、左右根
  • 极端情况下，二叉树会变成类似单向链表的结构，如：0123456789，在这10个数据中，查找的效率为 O(n)

• 红黑树（自平衡二叉树）

  • 通过自旋规则调整树的高度
  • 红黑树任然是一个节点两个分叉的原则，本质上还是二叉树，所以当数据量非常庞大时，树的高度依然会非常高

• B树 B Trees （多路平衡查找树）

  • B树是自平衡的
  • 每个节点下的子节点数大于2
  • B树中有”阶’的概念，
    • 3阶的B树，一个节点下最多有3个子节点，每个节点最多有两个数据，
    • 4阶的B树，一个节点下最多有4个子节点，每个节点最多有三个数据，
  • B树中，当节点存储数据到达临界值，再假如新的数据，是中间节点向上分裂的方式
  • 每个接节点存储了索引值，还存储了该索引指对应的数据行，并且每个节点存储了指向下一个节点的指针

缺点：是用B树存储数据时，不适合区间查找，对于区间查找的效率很低。假设要查找【3，7】之间的数据，即3，4，5，6，7，每次都需要从头节点开始查找

B+树

B+树相对于B树，改进的方面是：

• B+树将数据都存储在叶子节点当中，并且叶子节点之间使用链表进行连接，这样很适合范围查找
• B+树的非叶子节点上只有索引值，没有数据，所以非叶子节点可以存储更多的索引值，让B+树更矮更胖，提高检索效率

MySQL索引数据结构：

MySQL索引数据结构对传统的B+树进行了优化。在原B+树的基础上，又增加了指向相邻叶子节点的链表指针，并且树的最左边叶子节点也有一个和树的最右边的链表指针。形成了有序带指针的B+树，提高了区间访问的性能。MySQL采用的是16阶的B+树

面试题两道：

1. 如果一张表没有主键索引，那还会创建 B+树吗？

​ 会！因为MySql中每一张表除了我们自己建的字段外，还有一个隐藏的字段叫 row_id,row_id存储的是一条条数据在磁盘上存储的物理地址。当一张表没有主键时，默认会为这个 row_id字段添加上聚集索引。这个聚集索引通常也是B+树实现的。

2. MySQL为什么使用B+树，而不是用B树？

   因为B+树在B树上做了一些优化。

   1.首先B+树所有数据都是存储在叶子节点上的，让范围查找，分组查找效率更高

   2.所有的非叶子节点就可以存更多的键值，阶数可以更大，使树更矮更胖，可以减少IO次数，提高数据查询效率

   3.所有叶子节点的 数据页之间、数据记录之间采用链表连接，让升序降序更方便操作。

Hash索引

支持哈希索引的引擎有：

• InnoDB (不支持手动创建hash索引，系统会自动维护一个自适应的 hash索引)
  • 对于InnoDB来说，即使手动的指定某字段采用 Hash索引，最终该字段的索引也不会变，还是B+tree
• Memory （完全支持Hash索引）

Hash索引底层的数据结构就是hash表。key存储索引值，value存储行指针

检索原理：假设 name=‘孙行者’。通过哈希算法将’孙行者’转换为数组下标，通过下标找链表，在链表上遍历找到孙行者的行指针。

Hash索引的优缺点：

• 优点：只能用在等值比较这中，效率很高。例如 ：name= ‘孙悟空’；
• 缺点：不支持排序，不支持范围查找

聚集索引和非聚集索引

按照物理存储的方式不同，可以分为聚集索引（聚簇索引）和 非聚集索引

存储引擎是 InnoDB的，主键上的索引数据 聚集索引。

存储引擎是 MyISAM的，任意字段上的索引都是非聚集索引。

InnoDB的物理存储方式：当创建一张表t_user，并使用InnoDB存储引擎时，会在硬盘上生成这样一个文件：

• t_user.ibd （InnoDB data表索引 + 数据）
• t_user.frm （存储表结构信息）

MyISAM的物理存储方式：当创建一张表t_user，并使用MyISAM存储引擎时，会在硬盘上生成这样一个文件：

• t_user.MYD （表数据）
• t_user.MYI （表索引）
• t_user.frm （表结构）

聚集索引的原理图：B+树，叶子节点上存储了 索引值 + 所在行数据

所在行数据：该索引值所在行的所有数据

非聚集索引的原理图：B+树，叶子节点上存储了 索引值+行指针

行指针：指向磁盘中该索引值所在行的物理位置的指针

聚集索引的优点和缺点：

1. 优点：能够减少一次查询。因为聚集索引将数据存储在索引树的叶子节点上，获取索引值的同时可以获取数据
2. 缺点：由于聚集索引将数据和索引值放一起，导致当数据发生变化时，需要更新索引树，对系统的开销大

二级索引

二级索引（辅助索引）也属于非聚集索引 属于非聚集索引，但不是非聚集索引！底层叶子节点存储的内容是不一样的

所有非主键索引都是二级索引，之所以称为辅助索引，因为二级索引的叶子节点存储的数据是 “主键”，可以通过二级索引找到主键。

所以二级索引的 B+树结构中，叶子节点存储的是 索引值 + 所在行主键

二级索引的查询原理：

-- 给 age 字段添加索引后进行如下查询
select * from t_user where age = 30;

为什么会回表？因为使用了 select *

避免回表也是提高SQL数据查询效率的手段。例如：select id from t_user where age = 30; 这样的SQL语句是不需要回表的。

覆盖索引

覆盖索引：创建索引时包含的字段完全覆盖住 查询语句中涉及的字段

覆盖索引（Covering Index），是指某个查询语句可以通过索引的覆盖来完成，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中提取到，而不需要再去查询实际数据行获取查询所需数据。
当使用覆盖索引时，MySQL可以直接通过索引，也就是索引上的数据来获取所需的结果，而不必再去查找表中的数据。这样可以显著提高查询性能。

创建覆盖索引的SQL语句可以如下：

CREATE INDEX idx_user_username_email ON user (username, email);

当执行以下查询时：

SELECT email FROM user WHERE username = 'lucy';

MySQL可以直接使用覆盖索引（idx_user_username_email）来获取查询结果，而不必再去查找用户表中的数据。这样可以减少磁盘I/O并提高查询效率。而如果没有覆盖索引，MySQL会先使用索引（username）来找到匹配的行，然后再回表查询获取邮箱，这个过程会增加更多的磁盘I/O和查询时间。

值得注意的是，覆盖索引的创建需要考虑查询的字段选择。如果查询需要的字段较多，可能需要创建包含更多列的覆盖索引，以满足完全覆盖查询的需要。

覆盖索引具有以下优点：

1. 提高查询性能：覆盖索引能够满足查询的所有需求，同时不需要访问表中的实际数据行，从而可以提高查询性能。这是因为DBMS可以直接使用索引来执行查询，而不需要从磁盘读取实际的数据行。
2. 减少磁盘和内存访问次数：当使用覆盖索引时，DBMS不需要访问实际的数据行。这样可以减少磁盘和内存访问次数，从而提高查询性能。
3. 减少网络传输：由于在覆盖索引中可以存储所有查询所需的列，因此可以减少数据的网络传输次数，从而提高查询的性能。
4. 可以降低系统开销：在高压力的数据库系统中，使用覆盖索引可以减少系统开销，从而提高系统的可靠性和可维护性。

覆盖索引的缺点包括：

1. 需要更多的内存：覆盖索引需要存储查询所需的所有列，因此需要更多的内存来存储索引。在大型数据库系统中，这可能会成为一项挑战。
2. 会使索引变得庞大：当索引中包含了许多列时，它们可能会使索引变得非常庞大，从而影响查询性能，并且可能会占用大量的磁盘空间。
3. 只有在查询中包含了索引列时才能使用：只有==当查询中刚好包含了所有的索引列时才能使用覆盖索引。如果查询中包含了其他列，DBMS仍然需要访问实际的数据行，并且无法使用覆盖索引提高查询性能。

索引下推

覆盖索引能完全覆盖查询涉及的字段，当我们要查询的内容是 select * 时，要么创建索引覆盖所有字段，这显然不现实。所以为了尽可能的减少回表的次数，mysql提供了 索引下推 这个优化方法

索引下推是MySQL中的优化方法。==这不是一个索引对象，只是一个InnoDB自带的优化方法！==它会将 查询过程中的过滤条件下推到 索引层级 进行一次处理，从而减少回表的次数，优化查询性能。（下面的示例能充分解释这段话）

查询过程中的过滤条件下推到 索引层级 ：在索引的叶节点层级执行一次查询条件的过滤。意思就是如果为某些字段创建了索引，但是这些字段并没有完全覆盖查询字段，但是也不能白白创建索引！所以先根据这些索引再根据查询条件先进行一次过滤。

索引下推通常是基于 多列索引 的

如：users表：name,city,age

-- 为city,age字段创建索引
ALTER TABLE users ADD INDEX idx_name_city_age (city, age);
-- 查询语句
SELECT * FROM users WHERE age > 30 AND city = 'London';

上述语句为 city,age字段添加了索引，但我们要查询的结果是整个 user数据 。所以 最终一定是要进行回表查询的。但是在回表前会在索引层级将 city,age字段进行过滤，筛选出 age>30 and city = ‘london’ 的 ‘索引值结果集’，其他的都过滤掉。 再通过这些索引值进行回表查询。

索引的优缺点：

优点：

1. 提高查询性能：减少了数据库查询的数据量
2. 加速排序。
3. 减少磁盘IO

缺点：

1. 占据额外的存储空间
2. 增删改性能会损耗：每次进行增删改都需要更新索引，导致操作的性能下降
3. 消耗资源大：索引需要占用内存和cpu资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响

何时使用索引

建议使用索引的情况：

1. 频繁执行查询操作的字段
2. 大表
3. 需要进行排序或分组的字段
4. 外键关联的字段：可以加快联表查询的速度

不建议使用索引的字段：

1. 频繁执行增删改操作的字段
2. 小表
3. 对于唯一性很差的字段

具体情况具体分析=_=

MySql优化

MySQL数据库的优化手段通常包括但不限于：

• SQL查询优化：这是最低成本的优化手段，通过优化查询语句、适当添加索引等方式进行。并且效果显著。
• 库表结构优化：通过规范化设计、优化索引和数据类型等方式进行库表结构优化，需要对数据库结构进行调整和改进
• 系统配置优化：根据硬件和操作系统的特点，调整最大连接数、内存管理、IO调度等参数
• 硬件优化：升级硬盘、增加内存容量、升级处理器等硬件方面的投入，需要购买和替换硬件设备，成本较高

我们主要掌握：SQL查询优化

性能分析工具

1.通过以下命令可以查看当前数据库在SQL语句执行方面的整体情况：

show global status like 'Com_select';
show global status like 'Com_insert';
show global status like 'Com_delete';
show global status like 'Com_update';

show global status like 'Com_______';

2.慢查询工具

通过以下命令查看慢查询日志功能是否开启：

show variables like 'slow_query_log';

慢查询日志文件默认存储在：C:\dev\mysql-8.0.36-winx64\data 目录下，默认的名字是：计算机名-slow.log

3.show profiles

查看当前数据库是否支持 profile操作：

select @@have_profiling;

查看 profiling 开关是否打开：

navicat for mysql 默认是开启的，

dos命令窗口默认是关闭的

select @@profiling;

将 profiling 开关打开：

set profiling = 1;

可以执行多条DQL语句，然后使用 show profiles; 来查看当前数据库中执行过的每个SELECT语句的耗时情况。

select empno,ename from emp;
select empno,ename from emp where empno=7369;
select count(*) from emp;
show profiles;

查看某个SQL语句语句在执行过程中，每个阶段的耗时情况：

show profile for query 4;

想查看执行过程中cpu的情况，可以执行以下命令：

show profile cpu for query 4;

4.explain

explain命令可以查看一个DQL语句的执行计划，根据执行计划可以做出相应的优化措施。提高执行效率。

explain select * from emp where empno=7369;

索引优化

最左前缀原则

最左前缀原则：当查询语句where条件包含了添加复合索引时最左边的字段时，索引才会生效

细节部分：

添加了这样的复合索引：

create index idx_name_age_gender on t_customer(name,age,gender);

1. 查询时，where 后的过滤条件的字段最好按照 添加索引时的字段顺序进行

   explain select * from t_customer where name='zhangsan' and gender='M';
   

   结果：where条件后包含了 name 和 gender字段，此时中间隔了 age字段，会导致查询过程中 gender字段的索引失效，只引用了name 字段的索引。

2. 范围查询时，范围右侧的索引会失效

   explain select * from t_customer where name='zhangsan' and age>20 and gender='M';
   

   name和age字段索引生效，gender字段索引无效

   上述问题可以解决，在 age>20 改成 age>=20即可

   explain select * from t_customer where name='zhangsan' and age>=20 and gender='M';
   

索引失效情况

create index idx_t_emp_name on t_emp(name);
create index idx_t_emp_sal on t_emp(sal);
create index idx_t_emp_age on t_emp(age);

1.索引列参加了运算，索引失效

-- 索引生效
explain select * from t_emp where sal > 5000;
-- 索引失效
explain select * from t_emp where sal*10 > 50000;

2.索引列进行了模糊查询以 % 开头的，索引失效

-- 索引有效
explain select * from t_emp where name like '张%';
-- 索引失效
explain select * from t_emp where name like '%飞';

3.索引列是字符串char类型，但查询时省略了单引号，索引失效

-- 索引有效
explain select * from t_emp where age='20';
-- 索引失效
explain select * from t_emp where age=20;

4.查询条件中有or,只要有未添加索引的字段，索引失效

-- 索引生效
explain select * from t_emp where name='张三' or sal=5000;
-- 索引失效
-- 将sal 上的索引删除，此时查询语句完全没使用索引
alter table t_emp drop index idx_t_emp_sal;
explain select * from t_emp where name='张三' or sal=5000;

5.当查询的符合条件的记录在表中的占比比较大，索引失效

因为此时用索引和不用索引的效率差别已经不大了。

6.关于 is null 和 is not null 的索引失效的问题

本质上还是和第五条相同，走索引还是不走索引，取决于数据分布。如果符合条件的记录的占比比较大，那就走全表扫描，会放弃使用索引。

指定索引

当一个字段上既有 单列索引，又有复合索引时，我们可以通过SQL语句来指定使用哪个索引：

• use index(index_name) ：建议使用该索引，只是建议，底层会根据实际情况自行判断

• ignore index(index_name)：忽略使用该索引

• force index(index_name) : 强行使用改索引

如：

 select * from t_customer use index(idx_name) where name='zhangsan';
 select * from t_customer ignore index(idx_name_age_gender) where name='zhangsan
 select * from t_customer force index(idx_name) where name='zhangsan';

覆盖索引

覆盖索引强调的是：在 select 后面写字段的时候，这些字段尽可能的是 索引覆盖了的字段，这样可以避免回表查询。所以尽可能避免使用 select *

面试题：t_user表字段如下：id,name,password,realname,birth,email。表中数据量500万条，请针对以下SQL语句给出优化方案：

select id,name,realname from t_user where name='鲁智深';

优化方案： 给 name,realname 条件上组合索引，这样可以减少回表的次数，提高查询效率

前缀索引

如果一个字段类型是varchar或text, 字段中存储的是大文本，直接对该字段添加索引会让所索引的体积很大，怎么优化呢？

可以使用前缀索引：将字段中的每个数据进行截取，截取前几个字符当作索引值来创建索引

如：将 ename字段中每个数据的前两个字符截取下来 进行创建索引

create index idx_emp4_ename_2 on emp4(ename(2));

此时会又可能出现这种情况：截取的字符在 这个字段中有很高的重复率，这样我们的索引值就没有唯一性，所以截取字符的长度怎么决定？

可以使用如下公式：

select count(distinct substring(ename,1,前几个字符))/count(*) from emp;

通过以上查询，得出的结果越接近1，表示截取的字段重复性越低（做索引值的话，索引值的唯一性越高）

使用单列索引还是复合索引

• 当查询的语句中有多个字段，建议使用复合索引，减少回表次数
• 创建复合索引时需要注意最左前缀原则，主字段并且具有很强的唯一性的字段应排在第一位。
  • 原因：当使用复合索引时，创建索引表时会先按照靠左边的字段进行升序排，最左边的字段值相同时才会由靠由的字段决定

索引创建原则总结

1. 表数据量庞大，通常超过百万条数据
2. 经常出现在 where ，order by, group by 后面的字段建议添加索引
3. 创建索引引用的字段具有唯一性
4. 如果字段存储文本，内容较大，要使用前缀索引
5. 尽量使用复合索引，减少回表查询的次数
6. 如果一个字段中的数据不会为null，建议建表时添加 not null约束。这样优化器会知道哪个索引更有效。
7. 不要创建太多索引，这样进行增删改的时候，索引需要重写排序
8. 少查询，多删改的字段不建议创建索引

SQL优化

order by 优化

1. order by排序也遵循 最左前缀法则

2. 针对不同的排序规则，创建不同的索引。

   • -- 查询以下语句
     select id,age,sal from workers order by age asc, sal desc;
     -- 建议创建的索引
     create index idx_workers_ageasc_saldesc on workers(age asc, sal desc);
     

3. 创建复合索引

4. 如果无法避免 filesort, ==要注意排序缓存的大小，默认缓存256kb,==可以修改系统变量。 sort_buffer_size;

   show variables like 'sort_buffer_size';
   

group by优化

1. 遵循最左前缀法则

limit 优化

数据量非常庞大时，取数据时，越往后效率越低

解决方案：使用 覆盖索引 + 子查询

案例：在t_user表中，根据id排序，从 9000000 条数据开始，查2条数据

select * from t_user order by id limit 9000000,2;
-- 耗时 4.57 sec

使用覆盖索引

select id from t_user order by id limit 9000000,2;
-- 耗时 3.08 sec

此时可以看到使用覆盖索引速度提升了，但是我们要查询的是全部数据，怎么解决？

使用 覆盖索引 + 子查询(或其他列的数据)

select u.* from t_user u join (select id from t_user order by id limit 9000000,2)t on u.id=t.id; 
-- 耗时 3.09 sec

主键优化

1. 主键不要设置太长，因为 二级索引 的叶子节点存储的是主键值，主键值过长容易导致索引空间较大

2. 插入数据时，主键值最好是顺序插入。因为乱序插入可能会导致B+树叶子节点频繁的进行 页分裂和页合并操作。效率降低。所以尽量使用 auto_increment 生成主键，尽量不要使用uuid做主键，因为 uuid 不是顺序插入。

   但是还是要根据实际业务考量，比如当使用自增主键时，订单id就很容易暴露业务信息。

3. 最好不要使用业务字段做主键。 因为业务字段需要修改，导致主键值频繁修改，聚集索引进行频繁重排。

insert 优化

1. 批量插入：数据量较大时，不要一条一条的插入，要批量的进行插入，建议一次性不要超过1000条数据

• insert into t_user(id,name,age)  (1,'jack',20),(2,'lucy',30),(3,'timi',22);
  

2. 不建议使用自动提交事务机制。自动提交时，执行一条DML语句就自动提交一次，100万条数据提交100万次？ 6666
3. 主键建议采取顺序插入。
4. 超大数据量可以考虑使用 MySQL提供的 load指令。load指令可以将csv文件中的数据批量导入到数据库表当中，并且效率很高，过程如下：

• 第一步

mysql --local-infile -uroot -p1234

• 第二步：开启local_infile功能

set global local_infile = 1;

• 第三步：执行load指令

use powernode;

create table t_temp(
  id int primary key,
  name varchar(255),
  password varchar(255),
  birth char(10),
  email varchar(255)
);

load data local infile 'E:\\powernode\\05-MySQL高级\\resources\\t_temp-100W.csv' into table t_temp fields terminated by ',' lines terminated by '\n';

count（*） 优化

分组函数count（）的使用方式：

• count(主键)
  • 原理： 将每个主键值取出，累加次数
• count(常量)
  • 原理：将每个常量取出，累加次数
• count(字段)
  • 原理：将该字段的每个值取出，并 判断是否为 null ,不为 NULL则累加次数
• count(*)
  • 原理：不用取值，底层Mysql做了优化，直接取出总行数。效率最高

结论：如果想统计一张表的总行数，使用 count(*)

注意：

• 对于MySIAM存储引擎来说，当一个select 语句中没有where时，获取总行数的效率是极高的。因为MySIAM存储引擎对总行数进行了单独的维护。
• 对于InnoDB存储引擎来说，count计数的实现原理就是将表中每一条记录取出，然后累加。如果你想真正提高效率，可以自己使用额外的其他程序来实现，例如每向表中插入一条记录时，在redis数据库中维护一个总行数，这样获取总行数的时候，直接从redis中获取即可，这样效率是最高的。

update优化

当存储引擎是InnoDB时，表的行级锁是针对索引添加的锁，如果索引失效了，或者不是索引时，会提升为表级锁。

如：

create table t_fruit(
  id int primary key auto_increment,
  name varchar(255)
);

insert into t_fruit values(null, '苹果');
insert into t_fruit values(null, '香蕉');
insert into t_fruit values(null, '橘子');

这张表 id 是主键，并自带索引（聚集索引），现在有A事务和B事务

1. 演示行级锁，A事务和B事务对统一数据进行修改

A事务	B事务
start transaction;	start transaction;
update t_fruit set name=‘火龙果’ where id=1;
（事务正常进行中）	update t_fruit set name=‘榴莲’ where id=1;
	事务卡住（因为此时A事务先操作这一行数据，且id是有索引的，这一行数据会有行级锁进行锁定，A为提交事务之前，其他事务无法操作此行数据）

当然，如果两个事务更新的不是同一条数据，是可以并发进行的

A事务	B事务
start transaction;	start transaction;
update t_fruit set name=‘火龙果’ where id=1;
（事务正常进行中）	update t_fruit set name=‘火龙果’ where id=2;
commit;	(事务正常进行中)
	commit;

2. 行级锁是对索引进行加锁，以上的更新语句where 条件是 id, id是主键，有索引，所以使用了行级锁。如果索引失效，或该字段上没有索引，则会升级为表级锁

   演示表级锁：字段上无索引

   A事务	B事务
   start transaction;	start transaction;
   update t_fruit set name=‘火龙果’ where name=‘西瓜’;
   （事务正常进行中）	update t_fruit set name=‘桃子’ where name=“橘子”;
   	事务卡住。（因为A事务先对数据进行修改，而修改的字段无索引，此时会使用表级锁，将整张表进行上锁。A事务未提交之前，其他事务无法对这张表的任何数据进行修改）
   	commit;

所以，建议 update语句中 where 条件中的字段是添加索引的。

or 和 in效率

在 所在列有索引或者是主键的情况下，or和in的查询效率基本没差别

但在 所在列没有索引的情况下，随着查询数据的增加，in 的查询效率远在 or之上，in 会随着查询数据量的增加，时间成指数型增长，而in 查询的效率基本没区别。

锁

mysql中的锁分为：

• 全局锁：锁住数据库中的所有表
  • 全库备份需要加上全局锁，保证数据一致性，但是加锁必然导致期间业务停止
  • 优化：在 InnoDB引擎中，可以备份时加上参数==- -single-transaction参数完成不加锁的一致性数据备份==，底层通过快照读实现
• 表级锁：每次操作锁住整张表
  • 表锁
    • 表共享读锁
      • 加锁期间所有客户端仍然可读，但不可写
    • 表独占写锁
      • 加锁期间，当前客户端可读可写，其他客户端不可读不可写
  • 元数据锁 meta data lock,MDL
    • Mysql5.5中引入MDL。系统自动控制
    • 作用：维护元数据的数据一致性，避免DML与DDL冲突。元数据：表结构
    • 对一张表进行crud时，自动加 MDL读锁(共享); 对表结构进行变更操作时，自动加 MDL写锁（排他）
  • 意向锁
    • 作用：为了解决 InnoDB引擎中行锁和表锁的冲突问题，避免加表锁时需要去逐行检查当前表的行锁情况
    • 意向共享锁（IS）: 由语句 select … lock in share mode 添加
      • 与表锁共享锁兼容，与表锁排他锁互斥
    • 意向排他锁 （IX）: 由 insert、update、delete、select …for updata 添加
      • 与表锁共享锁及排他锁都互斥。意向锁之间不会互斥，
• 行级锁：每次操作锁住对应的行数据
  • InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现，而不是对记录加的锁。分为以下三类：
    • 记录锁 Record Lock: 锁定单个行记录的锁，防止其他事务对此进行 update、delete。
    • 间隙锁 Gap Lock": 锁定索引记录间隙（不含该记录），确保索引间隙不变，防止其他事务进行 insert,产生幻读
    • 临键锁，next-key Lock: next-key Lock = 记录锁 + 间隙锁。
  • 默认情况下，InnoDB在可重复读事务隔离级别下运行，InnoDB使用 next-key Lock 进行搜索和索引扫描，尽量防止幻读
    • 对于唯一索引行，自动优化为 记录锁（行锁）
    • 对于无索引行，自动升级为表锁

InnoDB引擎

1. 逻辑存储结构

• 表空间( idb文件): 一个mysql实例可以对应多个表空间,用于存储记录 , 索引等数据
• 段, 分为数据段 ,索引段, 回滚段, InnoDB是索引组织表
  • 数据段: B+树的叶子节点
  • 索引段: B+树的非叶子节点
• 区, 表空间的单元结构,每个区的大小为 1M, 默认情况下, InnoDB存储引擎大小为 16K, 即一个区中一共有64个连续的页
• 页, 是 InnoDB存储引擎磁盘管理的最小单元, 每个页的大小默认为 16KB, 为了保证页的连续性, InnoDB存储引擎每次从磁盘申请4-5个区,一个区有 64个连续页
• 行, InnoDB存储引擎数据是按行进行存放的,有两个隐藏列
  • Trx_id: 每次对某条记录进行改动时,都会把对应的事务id赋值给 trx_id隐藏列
  • Roil_pointer: 每次对某条记录进行改动时,都会把 旧的版本写入到 undo日志中, 然后这个隐藏列就相当于一个指针,可以通过它来找到该记录修改前的信息.

2.架构

mysql5.5版本后,默认使用 InnoDB存储引擎, InnoDB架构图如下: 分为两部分,左侧为内存结构,右侧为磁盘结构

InnoDB的内存结构:

• Buffer Pool : 缓冲池, 属于主内存的一块区域, 缓存的是磁盘上经常操作的数据 , 在执行 增删改查操作时, 会先操作缓冲池中的数据(若第一次没有数据,会从磁盘中加载然后更新到缓冲池中), 然后再以一定的频率刷新到磁盘, 从而减少 磁盘IO

  • 缓冲池以 页 为单位, 每个页大小16KB,底层采用链表数据结构管理 page, 根据状态, page分为三种类型:

    • free page: 空闲page, 未被使用
    • clean page: 已经被使用,但数据还未修改过
    • dirty page: 被使用且数据被修改过, 和磁盘中的数据不一致

    Buffer Pool的作用：减少磁盘IO次数，提高性能，并保护磁盘 （类似于缓存的存在(如redis)是用于保护数据库）

• Change Pool: 更改缓冲区, 8.0之后引入,主要是针对 非唯一的二级索引页而建立的, 在执行DML语句时,如果这些数据page没有在 Buffer Pool中, 那么也不会直接操作磁盘, 而是将数据变更存在更改缓冲区Change Buffer中, 在==未来数据被读取时, 再将数据合并并恢复到 Buffer Pool 中==, 合并后的数据再刷新到磁盘中

  • 因为 二级索引 通常是非唯一的, 且 以相对随机的顺序建立的B+树结构, 同样, 更新删除等可能会影响索引树中不相邻的二级索引页 , 使用 Change Buffer 可以在缓冲页中进行合并处理,减少随机磁盘IO

• 自适应哈希索引: 用于==优化 Buffer Pool 的查询==, InnoDB存储引擎会监控对表上各索引页的查询, 如果观察到hash索引可以提升速度,则建立 hash索引, 称之为自适应hash索引.

• Log Buffer: 日志缓冲区, 用来保存要写入磁盘中的log日志数据(redo log, undo log) , 默认大小为 16M, 日志缓冲区的日志会定期刷新到磁盘中. 如果需要更新, 插入或删除许多行的事务, 增加日志缓冲区的大小可以减少磁盘IO

  • 两个参数:
  • Innodb_log_buffer_size: 缓冲区大小
  • innodb_flush_log_at_trx_commit: 日志刷新到磁盘时机
    • 1: 默认值, 日志在每次事务提交时都会刷新到磁盘
    • 0: 每秒将日志写入并刷新到磁盘一次
    • 2: 日志在每次事务提交后写入, 并每秒刷新到磁盘一次

磁盘结构

• Doublewrite Buffer Files: 双写缓冲区,InnoDB引擎将数据页从 Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区，便于系统异常时恢复数据

  • 为什么需要双写缓冲区？
    1. 防止数据丢失，通过双写缓冲区记录了数据和对应的回滚sql
    2. 保护磁盘，避免高并发下大量数据一次性被刷新到磁盘

• Red Log,重做日志：事务的持久性就依赖于 redo log日志。该日志两部分组成：重做日志缓冲 ( redo log buffer ) 以及 重做日志 (redo log)， 前者是在内存中，后者在磁盘中，当事务提交之后会把所有信息修改到日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用 。执行时会循环写入两个文件

事务原理

持久性 –redo log

• redo log, 重做日志文件，分为两部分

  • 重做日志缓冲 redo log buffer：存在内存中，当进行增删改操作时，InnoDB会及时把 Buffter Pool 中的数据页变化存入该区中,当每一次事务提交时，redo log buffer会将记录的 数据页变化 刷新到 redo log file 磁盘文件中进行持久保存。
  • 重做日志文件：redo log file: 存在磁盘中

  当事务提交之后，会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时可以根据该文件进行数据恢复

  问题：redo log buffer 中记录的 数据页变化 在每次事务提交后都会刷新到磁盘中，同样是从缓存到磁盘IO的过程，为什么 Buffer Pool就需要 等多次事务提交后在一起刷新到磁盘？

  • 因为每一次的事务通常都包含了多次的增删改操作，这些记录都是随机的去操作数据页的，会造成大量的磁盘IO
  • 而对于 redo log来说，本身是日志文件，日志文件采用的是 追加记录的方式，写入磁盘的过程也是属于 顺序IO, 顺序IO的性能是远高于随机IO的 （这就是 WAL, write-ahead Logging）
  • 总结就是 buffer pool 刷新到磁盘是 随机 IO, redo log刷新磁盘是 顺序IO

原子性 – undo log

undo log, 记录的是数据修改的相反操作，属于逻辑日志，两个作用：提供回滚和MVCC（多版本并发控制）

数据修改的相反操作：当前delete一条记录，记录的就是 insert 这条记录，指向的是 update一条记录，记录的就是 对应相反的 update记录，属于是==反向备份==

undo log销毁：在事务执行时产生，事务提交后，并不会立即删除 undo log, 因为这些日志可能还用于多版本并发控制

一致性： undo log + redo log

隔离性：锁 + MVCC

MVCC底层原理

• 当前读
• 快照读：隔离级别是串行化的时候，快照都会退化为当前读
• MVCC：多版本并发控制，只在 InnoDB当中会维护一个数据的多个版本，使得读写操作无冲突，快照读为 Mysql实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log版本链、readView

记录中的三个隐式字段：

• db_trx_id : 记录最近一次修改(insert, update)了事务的ID
• db_roll_ptr: 回滚指针，配合 undo log, 指向上一个版本
• db_roll_id: 隐藏主键，如果表未指定主键且无唯一键的时候，该隐藏字段生效，作为主键

undo log版本链：

• 不同事物或相同事物对同一条数据进行修改，undo log会生成一条记录版本链条，链表的头部是最新的旧记录，尾部是最早记录的旧记录

readView: 读视图，就是记录了读的规则，只要我的 readView没有更新，我读的数据就能保证不变，所以 readView 就是实现可重复读的关键！

• min_id : 最小事务id， 记录的是 actice_id_list 当前活跃事务列表中的事务最小id （单独从 当前事务活跃列表中抽取出来，为了提高读的效率，避免每次都要从列表中查找最小事务id）
• max_id: 最大事务 id，指全局的下一个事物 id
• cur_trx_id : 当前事务id
• active_id_list: 当前活跃事务列表，只记录当前正在执行但还未提交的所有事物id

依据这四个字段，就可以固定读的范围，读的时候读与不读就是根据事务id进行判断

1. 比 最小事务id 还要小的一定读，因为该事务已经提交了

2. 比 最大事务id 还要大的一定不读，因为 这个事务是在我记录版本 readView之后的

3. 等于当前事务 id的一定读

4. 看在不在 活跃事务列表，如果在，说明这个事务还未提交，一定不读

   如果不在，说明该事务已经提交了，一定读

所以 对于 读已提交的事务隔离级别，就是 每一次通过每一次读 都生成最新的 readView 实现的，这样可以保证提交了一定能读到

而对于 可重复读，就是只记录当事务开启后第一读时的 readView， 之后readView固定，读的数据就一定不会变。

日志

• 错误日志 mysqld.log

• 二进制日志 binlog, 记录了所有 DDL和DML语句，数据逻辑日志，但不包括查询，作用如下

  • 灾难数据的恢复
  • Mysql的主从复制，在 mysql8版本中，默认二进制日志是开启，二进制日志包含：binlog 、binlog.index
  • 二进制日志包含三种格式：
    • statement: 记录每次操作的sql语句
    • row: 基于 行的记录，记录的是每一次操作涉及到的行的数据的变更，包括变更前和变更后的，也是mysql默认的记录格式
    • mixed: 混合上面两种，默认采用 statement, 在某些特殊情况下会自动切换为 row进行记录

• 查询日志 ： 记录了所有操作语句（保护增删改），默认情况下未开启

• 慢查询日志

主从复制

主从复制：将主数据库中的 DML操作和DDL操作通过 二进制日志 传到从库服务器中，从库对日志再进行重做，实现主从库数据同步

作用：

• 高可用性,主库出现故障，可以快速切换到从库提高服务
• 降低数据库压力，读写分离
• 可以在从库中进行数据备份，以免影响到主库服务

复制过程，三步：

1. 主库在事务提交时，会把数据变更记录在 binlog中
2. 从库调用 IOthread线程读取 binlog日志，写入到自己的中继日志 relay log中
3. 从库调用 SQLthread线程执行 relay log中的日志，进行数据同步

分库分表

单数据库在高并发环境下存在的问题：

• IO瓶颈：
  • 磁盘IO： 热点数据较多，导致 缓存（内存）不足，产生大量的磁盘IO
  • 网络IO：请求太多，带宽不够，网络IO瓶颈
• CPU瓶颈：对于排序，分组，连接查询，聚合统计等SQL会耗费大量的CPU资源，请求数天多，CPU出现瓶颈

拆分策略：

垂直拆分：

• 垂直分库：以表问依据，根据业务将不同的表拆分到不同的库中。微服务就是天然的垂直分库
• 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同的表中。拆分后一般通过主外键关联

水平拆分：

• 水平分库：以字段为依据，按照一定策略将一个库中的数据拆分到多个库当中
• 水平分表：以字段为依据，按照一定策略将一个库中的数据拆分到多个表当中

实现技术：

• shardingJDBC: 基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析，改写，路由处理。需要自行编码配置实现，只支持java语言，性能较高
• MyCat: 数据库分库分表中间件，支持多种语言，性能不如 shardingJDBC

MyCat

分库分表数据库中间件，使用时对于开发人员来说是无感知的

MyCat中不存储数据，只是进行逻辑处理，包含：逻辑库，逻辑表，逻辑表根据分片规则进行分片，分片节点，而真实的数据还是存放在数据库中

分片规则：

• 范围 range: 范围策略划分表，

  • 比如我们可以将表的主键order_id，按照从0~300万的划分为一个表，300万~600万划分到另外一个表。
  • 有时也会按照时间范围划分，不同年月的订单划分到不同的表

  优点： 有利于扩容

  缺点：容易出现热点问题。比如订单id一直增大，最近某段时间内的数据都会落在一张表里面

• 哈希取模

  • 优点：不会出现热点问题
  • 缺点：扩容成本高，每次扩容都需要重新hash运算

• 一致性哈希：在移除或者添加一个服务器时，尽可能小的改变已存在的请求与其已经映射好的服务器的映射关系。一致性哈希解决了简单哈希算法在分布式哈希表存在时的动态伸缩问题

关于分库分表面试题

如何避免热点问题数据倾斜？

比如按时间片划分，在双11期间大量数据落在了11月份的分片中，其他分片很闲，这就是数据倾斜。

解决方案：range范围 + hash取模 结合的分表策略。简单做法是：

先使用 range 进行分库，比如将订单 0~500万划分到订单库1，500 ~1000万划分到订单库2，依次类推，将来在订单数量激增时也可以进行很好的扩容

再使用 hash取模 的方式进行分表，将不同的订单划分到不同的表中

跨节点 join问题，进行分库分表后，如何再进行 join联表查询呢？

• 字段冗余：空间换时间，在表中添加字段避免联表join即可

• 全局表：类似于微服务的公共模块，即将 一些所有分片都要用到的 数据抽取出来，在每个数据库中保存一份

• 数据抽象同步：定时将指定的表做同步，将数据汇集，生成新的表。一般借助于 工具实现（如：ETL）

• 应用层代码组装：在应用层调用不同模块分开查询，将数据进行组装，代码层进行数据拼接的思想。像 order by,group by 等聚合函数问题，都可以采用这种思想解决。

分库分表后的分页查询问题

1. 全局视野：在应用层查到所有数据，代码进行分页封装。优点是业务无损，缺点是查询数据量大
2. 业务折衷：禁止跳页查询。禁止跳页查询，类似于解决 es中 serch after思想，不跳页查询，只需要第一次查询出全部数据，后续可以根据查询数据的最后一条和第一条锁定目标获取数据。

分布式id

雪花算法：高1位记录符号，41位记录时间戳，毫秒单位，10位记录计算机id，12位记录机器生成的序列号

非分表键如何查询，比如用户登录时根据手机号查询用户信息？

• 遍历：全表全库扫描
• 将用户信息同步到ES中，利用es进行查询

什么时候考虑分库分表？如何评估分库数量？

阿里巴巴的《Java开发手册》提出：

单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。

但在实际开发中，我们应该提前规划分库分表，如果估算3年后，你的表都不会到达这个五百万，则不需要分库分表。

一般什么类型业务表需要才分库分表？

需要评估业务的量级，通用是一些流水表、用户表等才考虑分库分表，如果是一些配置类的表，则完全不用考虑，因为不太可能到达这个量级。

对于MySQL来说的话，一般单库超过5千万记录，DB的压力就非常大了。所以分库数量多少，需要看数据量和单库处理记录能力有关。
如果分库数量太少，达不到分散存储和减轻DB性能压力的目的；如果分库的数量太多，需要更频繁的跨库访问
一般是建议分4~10个库

---