2021.2.27 Mysql数据库学习

MySQL的初步认识

一. 了解一些基本知识

JavaEE：企业级java开发 Web

前端（页面：展示，数据！）

后台（连接点：连接数据库JDBC，连接前端（控制、控制视图跳转，和给前端传递数据））

数据库（存数据，Txt，Excel，Word）

二.数据库

1.数据库的基本概念

  • 数据库（DB，DataBase）

  • 概念：数据仓库，软件，安装在操作系统上（window，linux,mac）

  • SQL，可以存储大量的数据，500w!!!

  • 作用：存储数据，管理数据

2.数据库的分类

  • 关系型数据库（SQL）

    • 类似于Excel，有行和列

    • MySQL，Oracle，Sql Server，DB2

    • 通过表和表之间，行和列之间的关系进行数据的存储（学员信息表，考勤表等）

  • 非关系型数据库（NoSQL-----Not only SQL）

    • {key，value}形式
    • Redis，MongDB
    • 非关系型数据库的对象存储，是通过对象的自身属性来决定的

3.数据库管理系统（DBMS）

  • 数据库的管理软件，科学有效地管理我们的数据，维护和获取数据
  • MySQL------数据库管理系统

三.MySQL以及SQLyog的下载

1.MySQL简介

MySql

2.安装mysql-5.7.19------>一个数据库管理系统

3.安装SQLyog旗舰版----->一个软件，能更好地使用MySQL数据库管理系统

  • 1)进行注册
  • 2)新建连接名称，填写好连接的数据，记住主机是localhost 或者127.0.0.1。密码是123456
  • 3)新建一个数据库，名为school，配置如下

注意：每一个sqlyog的执行操作，本质就是对应了一个sql，可以在软件的历史记录中去查看

创建一个新的数据库

  • 新建一个新的表，名为student，配置如下
字段：id name age

创建一个新的表

  • 4)打开表，选定表，右键打开表即可

四.MySQL的数据库操作（DDL）

建库、建表、设置约束等：create\drop\alter

1.一些常见的命令行连接

常见的命令行连接

2.MySQL数据库管理系统所用的语言

  • DML ：操纵
  • DDL ：定义
  • DQL ：查询
  • DCL ：控制
  • 数据库XX语言

3.操作数据库（定义）

底下[ ]内的内容可写可不写，写代码时，去掉[ ]

操作数据库

  • 创建数据库，并设置参数
--创建一个已设置参数的数据库shop
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci

固定的关键字一定要记住，可以借助SQLyog可视化历史记录里查看语句怎么写

4.数据库的表的列类型

数值

列类型的数值

列类型的字符串，时间日期

null

  • 没有值，未知
  • 一般不利用其来进行运行，因为结果也为NULL

5. （重点）数据库的表的字段属性

字段属性

​ 默认

  • 设置默认的值，设置完就不是null了

如果勾选了自增，在记录表格的时候，对于主值那一列，如果你任有数据记录，会自动帮你填充

id(主值) name

1 lyj 1 lyj

3 zyx 》》》》》》》》》》 3 zyx

​ zxh 4 zxh

6.（重点）创建数据库表

创建数据库表的例子

  • NOT NULL-------- 非空，不允许为空
  • DEFAULT NULL---------设置为空

格式如下：

创建数据库表的格式

7.查看创建数据库、数据表的语句

SHOW CREATE DATABASE `school`--查看创建school数据库的定义语句
SHOW CREATE TABLE `teacher2`--查看创建teacher2数据表的定义语句
DESC `teacher2`--查看teacher2数据表的结构

8.数据表的类型

  • 又称数据库的引擎
  • 所有的数据库文件都存在date目录下，一个文件夹对应一个数据库，本质上还是文件的储存

数据表的类型

9.操作数据表

操作数据表

五.MySQL的数据管理（DML）

对数据库中的数据进行一些简单操作，如insert,delete,update,select等.

1.数据库级别的外键（了解即可）

  • 是一个物理外键，主要用于，一个表要用到另外一个表的内容，使用外键调用，弊端在于只是创建了物理上的联系，数据库过多时，会比较困扰操作

    （在进行delete和update都必须考虑外键约束）

  • 数据库就是单纯的表，只用来存数据，要做到行是数据，列是字段

  • 如果想用多张表的数据，想使用外键------要用程序去实现

2.DML数据库的管理语言（全部记住）

  • insert,delete,update,select

3.添加（insert）

--语法：insert into `表名` (`字段名1`,`字段名2`,`字段名3`,.....) values (值1，值2，值3...)，(值1，值2，值3...)
--如果不写字段名
--语法：insert into `表名` values (值1，值2，值3...)，(值1，值2，值3...)
--每个值一一对应创建表顺序的值

insert添加数据

4.修改（update）

--语法：update `表名` set [`字段名1`=value1, `字段名2`=value2...] where [条件]
--条件可以是等号也可以是不等式，如果没有设定，就是全部修改

update修改数据

条件操作符

  • 非操作符 NOT 或！

5.删除

--语法： delete from `表名` where [条件]

delete删除数据

  • delete 和 truncate 的区别
delete from `表名`

truncate [table] `表名`

相同点：都能删除表内的所有数据，但是都不会删除表结构、索引、约束等

不同点：

    • truncate 之后，会重新设置 自增列 计数器会归零。而delete不会
    • truncate 之后，不会影响事务。而delete会

​

6.MD5数据加密

  • 数值经过MD5( ) 函数加密后，是不可逆的
  • 相同数字的MD5值是一样的
  • 使用MD5处理数据是为了增强算法的复杂度和不可逆性

MD5加密处理

MD5加密处理的密码值

六.MySQL的数据库的数据查询（DQL）

  • 数据查询语言（Date Query Language）
  • 所有数据的查询操作都用select
  • 这是数据库使用中最核心的语言，最重要的语言，使用频率最高的语言
  • select语法结构注意：不能将语句的顺序前后颠倒

1.查询字段信息和设置别名

--查询全部字段(表中所有字段的信息)
SELECT * FROM `student`
--查询指定字段
SELECT `studentno`,`studentname` FROM`student`
--有时候，列的名字不太懂，可以起别名，表名也可以
SELECT `studentno` AS `学号`,`studentname` AS `姓名` FROM`student` AS `学生表`
--可以利用函数concat(a,b)来修改表中的某个字段的数据格式
SELECT CONCAT('姓名：',`studentname`) AS `新名字` FROM`student`

concat函数

2.查询中的去重操作distinct

SELECT * FROM `result`
SELECT `studentno` FROM `result`
--查找哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result`
--发现重复数据，进行去重

3.查询数据库的表达式

image-20210223162656476

  • 数据库的表达式：文本值、列、null、函数、计算表达式、系统变量
  • 还可以对某个字段的值进行操作后 查询
  • 如对成绩进行加分后，查询

select查询加分后的成绩

4.where子句和操作符

  • where后面接条件
  • 4.1逻辑运算符（见上）（除了between..and...）
SELECT `studentno` ,`studentresult` FROM `result` WHERE `subjectno`= 1
SELECT `studentno` ,`studentresult` FROM `result` WHERE  NOT `subjectno`= 1
SELECT `studentno` ,`studentresult` FROM `result` WHERE `studentresult`>=70 AND `studentresult`<=80
SELECT `studentno` ,`studentresult` FROM `result` WHERE `subjectno` =2 OR `subjectno`=4
  • 4.2模糊查询：比较运算符

模糊查询：比较运算符

  • is null 和 is not null运算符
SELECT `studentno`,`studentname` FROM `student`  WHERE `email` IS NULL
--查询邮箱没填的学生学号和姓名
SELECT `studentno`,`studentname` FROM `student` WHERE `address` IS  NOT NULL
--查询 地址填了的 学生学号和姓名
  • between运算符
SELECT `studentno` ,`studentresult` FROM `result` WHERE `subjectno` BETWEEN 1 AND 2
--查询考试科目从1到2的学生学号和学生成绩

  • like运算符：

  • %代表0到任意个字符

  • _代表一个字符

like运算符

  • in运算符
  • in(只能是具体的一个或多个值，不能用到%或者_来代表某些值)
  • 不能用where address in('广东%','北京%')来表示，来自广东/北京来的人，必须写明具体是哪里

in 运算符

5.联表查询（JoinOn）

  • 5.1联表查询常用连接的方法
  • 将两张表中需要的字段以及其信息连接起来，然后查询

inner join-------如果交叉字段的数据在判断条件中成立，就返回该行

left join-------会返回在判断条件中成立的值，以及左表中在交叉字段中剩余的值，即使右表中没有匹配

right join-----会返回在判断条件中成立的值，以及右表中在交叉字段中剩余的值，即使左表中没有匹配

联表查询步骤：

/*
1.分析需要查询的字段来自哪个表（连接查询）
2.选择用哪种连接方法（上面有三种，一共7种）
3.确定交叉点（这两个表中哪个数据是相同的）
利用ON判断条件，将两者连起来

公式：查询的字段 from 左表 join方法  右表（连接的表） on 判断的条件
*/

SELECT r.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM `result` AS r
INNER JOIN `student` AS s
ON r.`studentno`=s.`studentno`

SELECT r.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM `result` AS r
LEFT JOIN `student` AS s
ON r.`studentno`=s.`studentno`

SELECT r.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM `result` AS r
RIGHT JOIN `student` AS s
ON r.`studentno`=s.`studentno`

进阶思考：三表连接

  • 解题思路，先连接两表，再连接一表

联表查询进阶：三表连接

  • 其中学号和学生姓名来自表 student 科目名来自表subject 分数来自表result

  • 表student和表result有共同字段studentNo

  • 表result和表subject有共同字段subjectNo

  • 5.2自表查询（自连接）

  • 自己的表和自己的表进行的连接，核心在于：将一张表拆分为两张一样的表

--创建有父子关系的表
CREATE TABLE `school`.`category`( 
 `categoryid` INT(3) NOT NULL COMMENT 'id',
 `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
 `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
 PRIMARY KEY (`categoryid`) ) 
 ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
 
 INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
INSERT INTO `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
INSERT INTO `school`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
INSERT INTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES ('4', '3', '数据库'); 
INSERT INTO `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
INSERT INTO `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES ('6', '3', 'web开发'); 
INSERT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('7', '5', 'ps技术');

关系见下图：

自连接表的关系

自连接的结果

6.分页和排序

  • Order by 排序
  • 语法：order by 根据哪个字段排序 怎么样排序
  • 查询到的数据可以有两种排序方法： 升序ASC（小--大） 降序DESC（大--小）
--查询 高等数学-2 课程成绩成绩排名前3的同学，且分数大于85分同学的（学号，姓名，课程名称，分数）
SELECT `studentname`,s.`studentno`,`subjectname`,`studentresult` FROM
`student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE sub.`subjectname` ='高等数学-1'
ORDER BY studentresult ASC
LIMIT 0,3

  • 其中学号和学生姓名来自表 student 科目名来自表subject 分数来自表result

  • 表student和表result有共同字段studentNo

  • 表result和表subject有共同字段subjectNo

  • limit 分页

  • 语法：limit 起始值，页面大小（起始值从0开始算）

  • 如 limit 0,5表示从第一个开始的前5个数据

limit分页

7.where的子查询和嵌套查询

  • 子查询比联表查询更快，因为不用联表操作

  • 子查询的规则（由里到外，先查询里面条件的数据，再查询外面条件的）

--使用子查询和嵌套查询
--查询 高等数学-2 课程成绩分数数大于85分同学的（学号，姓名）
SELECT `studentno`,`studentname` FROM `student` WHERE
`studentno` IN(
SELECT `studentno` FROM `result` WHERE `studentresult`>=80 AND 
`subjectno` IN(
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-2'
)
)

  • 其中学号和学生姓名来自表 student 科目名来自表subject 分数来自表result

  • 表student和表result有共同字段studentNo

  • 表result和表subject有共同字段subjectNo

  • where子查询和联表查询的复用

--查询 高等数学-2 前2名同学的成绩信息（学号，姓名，成绩）
--利用子查询和联表查询一起
SELECT s.`studentno`,`studentname`,`studentresult` FROM
`student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE `subjectno` IN(
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-2'
)
ORDER BY `studentresult` DESC
LIMIT 0,2

8.分组（group by）和二次（分组）过滤（having）

  • group by 指定结果按照哪个字段进行分组

  • having 过滤分组的记录必须满足的次要条件

  • where是判断每一条记录，having二次过滤用来判断组

  • 要注意where条件不能用聚合函数

分组和过滤

七.MySQL的函数

1.MySQL的常用函数（简单了解）

MySQL常用函数

2.（重点常用）MySQL的聚合函数

MySQL常用的聚合函数

  • 2.1count()函数的用法

  • count(*) 和 count(1)和count(列名)区别

    执行效果上：
    count(*) 包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
    count(1) 忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

    count(1)，其实就是计算一共有多少符合条件的行。
    1并不是表示第一个字段，而是表示一个固定值。
    其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1

    count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

    执行效率上：
    列名为主键，count(列名)会比count(1)快
    列名不为主键，count(1)会比count(列名)快
    如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（）
    如果有主键，则 select count（主键）的执行效率是最优的
    如果表只有一个字段，则 select count (    )最优。

  • 聚合函数简单用法

SELECT COUNT(studentname) FROM `student`
SELECT SUM(`studentresult`) FROM `result`
SELECT AVG(`studentresult`) FROM `result`
SELECT MAX(`studentresult`) FROM `result`

八.MySQL的事务（Transaction）

将一组sql放到一个批次去执行，要么都成功要么都失败

1.事务的特性/原则（ACID）

  • Atomic（原子性）：事务中包含的操作被看做一个逻辑单元，这个逻辑单元中的操作要么全部成功，要么全部失败。

  • Consistency（一致性）：事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏

    如：

    一致性

    事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没 有执行过一样

  • Durability（持久性）：事务结束后，事务处理的结果必须能够得到固化。

    事务一旦提交就不可逆，会持久化到数据库，事务没有提交，则会恢复原状

  • Isolation（隔离性）： 事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。需注意：多个并发事务之间要隔离，不能被其他事务的操作数所干扰

    同时，并行事务的修改必须与其他并行事务的修改相互独立。

​

隔离性

2.脏读、不可重复读和幻读（虚读）

隔离所产生的问题

  • 脏读：指一个事务读取了另外一个事务未提交的数据

  • 不可重复读：在一个事务内读取表中的某行数据，多次读取结果不一样。

    （这个不一定是错误的，只是场所不一样）

    不可重复读

  • 幻读（虚读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致，一般是读取多了一行

    幻读（虚读）

不可重复读重点在于update和delete，而幻读的重点在于insert

不可重复读和脏读的区别是：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

3.执行事务

事务执行的步骤

--创建一个已设置参数的数据库shop
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
--在数据库shop下创建一个accout表
CREATE TABLE `accout` (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
--在accout表中添加数据
INSERT INTO `accout` (`name`,`money`)VALUES ('A',1000.00),('B',2000.00),('C',300.00)
--==============================模拟转账========================
SET autocommit=0;--关闭提交
START TRANSACTION ;--开启一个事务
UPDATE  accout SET money=money-500 WHERE NAME='A';#A减500
UPDATE  accout SET money=money+500 WHERE NAME='B';#B加500
COMMIT;--提交事务,就被持久化了
ROLLBACK;--若提交失败，数据回滚
SET autocommit=1;--恢复默认值

4.在java中，是这样体现事务的

java中的事务

九.索引

1.索引的定义

索引( Index)是帮助MySQL高效获取数据的数据结构，索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

2.索引的优缺点分析

  • 优点：
    • 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的。
    • 另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 缺点：
    • 创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
    • 占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

3.索引的分类

3.1 常见的索引

在一个表中，主键索引只能有一个，唯一索引可以有很多个

  • 主键索引（PRIMARY KEY）

    • 唯一的标识，主键不可以重复，只能有一个列作为主键

    • `ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

  • 唯一索引（UNIQUE KEY）

    • 避免重复的列出现，唯一可以重复，多个列都可以标识为 唯一索引

    • 可以保证数据库表中每一行数据的唯一性。

    • ALTER TABLE `table_name` ADD UNIQUE ( `column` )

  • 普通索引（INDEX/KEY）

    • 默认的，利用index或key关键字来设置，唯一的任务是加快对数据的访问速度。

    • 普通索引允许被索引的数据列包含重复的值

    • ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

  • 全文索引（FULLTEXT）

    • 快速定位数据

    • ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

  • 联合索引(多列索引)

    • 联合索引即由多列属性组成索引。

    • 遵循最左前缀原则

      • 假设创建的联合索引由三个字段组成:

        ALTER TABLE `table_name` ADD INDEX index_name (num,name,age)

        那么当查询的条件有为:num / (num AND name) / (num AND name AND age)时，索引才生效。所以在创建联合索引时，尽量把查询最频繁的那个字段作为最左(第一个)字段。查询的时候也尽量以这个字段为第一条件。

        • 由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面（更离散的，区分度更高的）。ORDER BY子句也遵循此规则。
        • 遇到等值（=）查询，和非等值（>,<,between,like）查询等，将等值查询的列放前面
          • 如果非等值字段放在前面，那么需要进行索引跳跃扫描，或者范围扫描，这是就扫描了很多无效的索引。

3.2 主键索引和唯一索引的区别

(1) 对于主键/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；
(2) 主键不一定只包含一个字段，所以在主键的其中一个字段建唯一索引还是有必要的；
(3) 主键可作外键，唯一索引不可；
(4) 主键不可为空，唯一索引可；
(5) 主键可是多个字段的组合；
(6) 主键索引一定是唯一索引，而且只有一个， 唯一索引不是主键索引，可以有多个

4.聚集索引和非聚集索引

4.1 聚集索引的定义

  • 聚集(clustered)索引，也叫聚簇索引。定义：数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同，一个表中只能拥有一个聚集索引。

4.2 聚集索引的创建

  • 如果有主键，按照主键顺序构造B+树。叶子节点保存行记录
  • 如果没有主键，将第一个不为NULL的唯一索引作为聚集索引
  • 都没有，就使用隐式字段创建聚集索引，该字段在插入新行时自动递增（缺点：自动递增避免排序，但是引起大范围的数据的物理移动，损耗磁盘IO性能）

4.3 聚集索引的缺点

  • 依赖于有序的数据 ：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  • 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

4.4 辅助索引的定义

  • （辅助索引）非聚集(unclustered)索引的定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
    • 除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。

4.5 辅助索引的创建

4.6 辅助索引的缺点和解决

  • 缺点：聚簇索引的叶子节点上存储着表中的所有数据；而非聚簇索引的叶子节点上只存储了索引列和主键，如果需要查询的列不在索引列中，则需要依据主键再重新回到聚簇索引中进行查找，即回表查询，需要查询两次索引树。

  • 覆盖索引：

    • 覆盖索引就是把要使得需要查询的列和索引列是对应的，只查找一次索引树就能得到想要的所有列，从而避免了回表操作。
    • 常见的方法是：将被查询的字段，建立到联合索引里去。

5.各个索引创建的语法（了解）

--创建表格时

索引的语法

索引的基本语法2

创建索引

  • 索引在小数据量时，用处不大，但是在大数据时，区别较大，可以看出它的查找性能提高好几个数量级

6.索引的原则和优化

  • 索引不是越多越好
  • 不要对经常变动的数据加索引，修改会比较麻烦
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上，尽量选择区分度高的列作为索引
  • 尽可能的考虑建立联合索引，而不是单列索引；同时设计覆盖索引
  • 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

索引的数据类型

Btree：InnoDB的默认数据类型

面试时可以装杯：去阅读索引的文章

https://blog.codinglabs.org/articles/theory-of-mysql-index.html

十.面试考的内容

1.引擎

1.1 引擎的基本概念

  • MySQL中常用的四种存储引擎分别是： MyISAM存储引擎、innoDB存储引擎、MEMORY存储引擎、ARCHIVE存储引擎。
  • 存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。
  • MySQL中的数据用各种不同的技术存储在文件（或内存）中，这些技术中的每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

1.2 InnoDB 和 MyISAM之间的区别

1）InnoDB支持事物，而MyISAM不支持事物

  • InnoDB 提供事务支持，具有提交(commit)和回滚(rollback)事务的能力。

2）InnoDB支持行级锁，而MyISAM支持表级锁

  • 表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
  • 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
  • InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB使用表锁也就是说，InnoDB的行锁是基于索引的！

3）InnoDB支持MVCC, 而MyISAM不支持

  • MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提供性能。

4）InnoDB支持外键，而MyISAM不支持

5）InnoDB支持数据库异常崩溃后的安全恢复，而MyISAM不支持

  • 使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

    🌈 拓展一下：

    • MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
    • MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。
    • 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

6）InnoDB不支持全文索引，而MyISAM支持。

1.3 四种存储引擎比较

  • InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

优缺点：InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

  • MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

优缺点：MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

  • MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
  • Archive：如果只有INSERT和SELECT操作，可以选择它。该存储引擎非常适合存储大量独立的、作为历史记录的数据。区别于InnoDB和MyISAM这两种引擎，ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些。本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
  • 注意：同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

2. 事务（四大特性 隔离级别）

2.1 四大特性acid（见上）

  1. 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
  3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性（Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

数据事务的实现原理呢？

我们这里以 MySQL 的 InnoDB 引擎为例来简单说一下。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。

保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

2.2 脏读、不可重复读、幻读（见上）

2.3 隔离级别

隔离的级别

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

  • 像 Serializable 这样的级别，就是以 锁表 的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)

  • 而在 Oracle数据库 中，只支持Serializable (串行化) 级别和 Read committed (读已提交) 这两种级别，其中默认的为 Read committed（读已提交） 级别。
    原文链接：https://blog.csdn.net/u010960184/article/details/82557978

    • InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。

3.索引的结构

3.1 Hash索引

Hash索引定位快：

  • Hash索引指的就是Hash表，最大的优点就是能够在很短的时间内，根据Hash函数定位到数据所在的位置（key为存储的索引列，value为存放行数据的磁盘位置），这是B+树所不能比的。

  • Hash索引不支持顺序和范围查询（Hash索引不支持顺序和范围查询是它最大的缺点）。B+树在叶子节点上能够通过双向链表顺序遍历，进行范围查询。

3.2 树的基本概念

  • 树中一个结点的子结点个数称为该结点的度。
  • 树中结点的最大度数称为树的度。（例如：结点 B 的度为 2 ； 结点 D 的度为 3 ， 树的度为 3 。）
  • 度大于 0 的结点称为 分支结点（又称为非终端结点）
  • 度为 0 的（没有子女结点）的结点称为叶子结点（又称为终端结点）
  • 在分支结点中，每个结点的分支树就是该结点的度。
  • 结点的深度是从根结点开始自顶向下逐层累加的。
  • 结点的高度是从叶结点开始自底向上逐层累加的。
  • 结点的层次从树根开始定义，根结点为第 1 层（有些教材中将根结点定义为第 0 层），它的子结点为第2 层，依次类推
  • 树的高度（又称为深度）是树中结点的最大层数。

3.3 二叉树

每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点；( 但B树在经过多次插入与删除后，有可能导致不同的结构 )

img

右边也是一个二叉树，但它的搜索性能已经是线性的了；同样的关键字集合有可能导致不同的树结构索引；所以，使用B树还要考虑尽可能让B树保持左图的结构，和避免右图的结构，也就是所谓的“平衡”问题；

​ 实际使用的B树都是在原B树的基础上加上平衡算法，即“平衡二叉树”；如何保持B树结点分布均匀的平衡算法是平衡二叉树的关键；平衡算法是一种在B树中插入和删除结点的策略

3.4 B-tree

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：
\1. 每个节点最多有m个孩子。
\2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
\3. 若根节点不是叶子节点，则至少有2个孩子
\4. 所有叶子节点都在同一层，且不包含其它关键字信息
\5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
\6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
\7. ki(i=1,…n)为关键字，且关键字升序排序。
\8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：
img

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

3.5 B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息。
  2. 所有叶子节点之间都有一个链指针。
  3. 数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
img

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

3.6 为什么不用二叉树？为什么不用红黑树？为什么不用B树

  • MySQL根据磁盘I/O次数，也就是搜索的次数，来衡量查询效率。每个节点的读取都是一次IO操作，节点存取数据越多，磁盘的IO次数越少。
  • 经过多次插入与删除后，二叉树会退化成链表
  • 红黑树和二叉树一样，都只有两个分支，而B树和B+树有多个分支，因而在相同数据量的情况下，深度比B树、B+树更深，搜索次数也就越多
  • 红黑树一个节点只能存出一个值，B树一个节点可以存储两个值，红黑树的深度会更大，IO效率低
  • 除B+树以外，二叉树、红黑树、B树范围查询都要从根节点多次遍历查询
  • B树与B+树索引的区别：
    • B树的所有节点既存放键(key)也存放数据(data)；除了叶子节点外，还存放了子节点的地址信息
    • 而B+树只有叶子节点存放 key 和 data，其他非叶子节点只存放key和存储子节点的地址信息。从而减小B+数的深度，提高IO效率
    • B树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点（双向链表），前继指针和后继指针指向相邻的磁盘块，即能够提高范围查找的效率。
    • B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，而且都是根据主键顺序构建的B+树，叶子节点的顺序检索很明显。
      • B+树拥有良好的顺序查询效率，相比较其他的索引结构，这极大地提高了效率，按顺序访问范围数据是很快的，这有两个原因：
        • 顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）
        • 如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作
    • ————————————————
      版权声明：本文为CSDN博主「竹秋。」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
      原文链接：https://blog.csdn.net/qq_41615726/article/details/107639975

4. Mysql的锁

  • MySQL锁可以按使用方式分为：乐观锁与悲观锁。
  • 按粒度分可以分为表级锁，行级锁，页级锁。

4.1 表锁

  • 表锁下又分为两种模式： 表读锁（Table Read Lock）&& 表写锁（Table Write Lock）
    读读不阻塞，读写阻塞，写写阻塞！

    读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁

    读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁！

    写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁！

4.2 行锁

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

    • 共享锁（S锁、读锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。即多个客户可以同时读取同一个资源，但不允许其他客户修改。

    • SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

    • 排他锁（X锁、写锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的读锁和写锁。写锁是排他的，写锁会阻塞其他的写锁和读锁。

    • SELECT * FROM table_name WHERE ... FOR UPDATE

  • 为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

    • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
    • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

4.3 MVCC行级锁（多版本并发控制）

  • 锁的应用最终导致不同事务的隔离级别，事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节。

  • InnoDB基于行锁还实现了MVCC多版本并发控制，MVCC在隔离级别下的Read committed和Repeatable read下工作。MVCC实现了读写不阻塞。

  • 在表锁中我们读写是阻塞的，基于提升并发性能的考虑，MVCC一般读写是不阻塞的（所以说MVCC很多情况下避免了加锁的操作）。MVCC实现的读写不阻塞正如其名（多版本并发控制）：可以简单的理解为：对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，使得读取时可以完全不加锁。

  • InnoDB中的MVCC的具体实现：

    • InnoDB中每一行记录后都有两个隐藏的列，一个保存了行的创建时间，另一个保存了行的过期时间（或删除时间），以系统版本号而非时间值的形式存储
    • 每开始一个新的事务时，系统版本号都会递增；而此时的系统版本号也会作为事务的版本号，用于和查询到的记录中的版本号进行对比
    • InnoDB只查找（SELECT）小于当前事务版本号的数据（行内的系统版本号小于等于事务的版本号），从而事务查询到的记录要么是此前就已经创建的，要么是在当前事务中创建的
    • 行的删除版本号要么未定义，要么大于当前事务版本号，以确保事务读取到的行在事务开始之前没有被删除
      ————————————————
      版权声明：本文为CSDN博主「竹秋。」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
      原文链接：https://blog.csdn.net/qq_41615726/article/details/107639975

4.5 悲观锁和乐观锁

  • 需要解决的问题：有一张数据库表USER，只有id、name字段，现在有2个请求同时操作表A，过程如下：（模拟更新丢失，虽然不是很恰当）

    1. 操作1查询出name="zhangsan"
    2. 操作2也查询出name="zhangsan"
    3. 操作1把name字段数据修改成lisi并提交
    4. 操作2把name字段数据修改为wangwu并提交

    那么操作1的更新丢失啦，即一个事务的更新覆盖了其它事务的更新结果，解决上述更新丢失的方式有如下3种：

    1. 使用Serializable隔离级别，事务是串行执行的！

    2. 乐观锁

      • 乐观锁不是数据库层面上的锁，需要用户手动去加的锁。一般我们在数据库表中添加一个版本字段version来实现，例如操作1和操作2在更新User表的时，执行语句如下：

        update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}，

        此时即可避免更新丢失。

    3. 悲观锁

      • 我们使用悲观锁的话其实很简单(手动加一个行锁就行了)

        select * from xxxx for update

      • 在select 语句后边加了for update相当于加了排它锁(写锁)，加了写锁以后，其他事务就不能对它修改了！需要等待当前事务修改完之后才可以修 改.也就是说，如果操作1使用select ... for update，操作2就无法对该条记录修改了，即可避免更新丢失。

4.6 死锁

  • 产生原因：
    • 所谓死锁：是指两个或两个以上的进程在执行过程中,因争夺资源，它们中的一个或多个等待某个资源被释放，而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁。
    • 表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。
  • 解决方法：
    • 以固定的顺序访问表和行。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。
    • 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
    • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4.7 GAP间隙锁

GAP间隙锁的理解：

  • 当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在 的记录，叫做“间隙（GAP)”。

    InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

    例子：假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101

    Select * from emp where empid > 100 for update;

    上面是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁

InnoDB使用间隙锁的目的有两个：

  • 为了防止幻读( Repeatableread隔离级别下再通过GAP锁即可避免了幻读)
  • 满足恢复和复制的需要
    • MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

5.索引优化 失效

5.1 使用单个索引失效的情况

1、查询语句中使用!= 或者 <> 导致索引失效

  • SELECT * FROM user WHERE name != ‘冰峰’;
    我们给name字段建立了索引，但是如果!= 或者 <> 这种都会导致索引失效，进行全表扫描，所以如果数据量大的话，谨慎使用

2、查询语句中字段类型不一致导致的索引失效。（隐式转换导致不走索引）

  • 索引不适用于隐式转换的情况，比如你的SELECT * FROM T WHERE Y = 5 在Y上面有一个索引，但是Y列是VARCHAR2的，那么会将上面的5进行一个隐式的转换，SELECT * FROM T WHERE TO_NUMBER(Y) = 5,这个时候也是有可能用不到索引的。

3、查询语句中使用函数导致的索引失效

  • SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;
    如果你的索引字段使用了索引，对不起，他是真的不走索引的。

4、查询语句中使用运算符导致的索引失效

  • SELECT * FROM user WHERE age - 1 = 20;
    如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

5、查询语句中使用OR引起的索引失效

  • SELECT * FROM user WHERE name = ‘张三’ OR height = ‘175’;
    • 注意：OR 导致索引是在特定情况下的，并不是所有的 OR 都是使索引失效，如果 OR 连接的是同一个字段，那么索引不会失效，反之索引失效。

6、查询语句中使用模糊搜索导致的索引失效

  • like以通配符开头('%abc...')或者('%abc%...')，mysql索引失效会变成全表扫描的操作，使用('abc%')索引不失效。

  • SELECT * FROM user WHERE name LIKE ‘%冰’;

7、查询语句中使用NOT IN、NOT EXISTS导致索引失效

  • SELECT s.* FROM user s WHERE NOT EXISTS (SELECT * FROM user u WHERE u.name = s.name AND u.name = ‘XXX’)
    SELECT * FROM user WHERE name NOT IN (‘XXX’);
    这两种用法，也将使索引失效。但是 IN 还是走索引的，千万不要误解为 IN 全部是不走索引的。

8、查询语句中使用is not null/is null可能会导致索引失效

  • SELECT * FROM user WHERE address IS NULL;
    SELECT * FROM user WHERE address IS NOT NULL;
    注意：

    • is not null不管什么情况下都不会走索引

    • is null在字段允许为空时会使用索引，但一般索引字段不设置为空（null）

5.2使用联合索引失效的情况

  • 最佳左前缀法则，意思就是当你如果有建立过多个字段索引的组合索引的时候，最要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不能跳过索引中的列。

例子：

2b8234dcd7dd5f7d5677dd698c98ea3c.png

  • 解决联合索引失效的方法：.尽量使用覆盖索引(只访问索引的查询(索引列和查询一致))

原文链接：https://blog.csdn.net/heguixian/article/details/108615075

6.主键怎么选

7.手写sql查询

（见上文 六.MySQL的数据库的数据查询（DQL））

7.1补充数据库的连接方式：（六.MySQL的数据库的数据查询（DQL）.5联表查询（join on））

  • 1、内连接(inner join)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。

    2、外连接分为左外连接(left out join或left join)、右外连接(right out join或right join)和全外连接(full out join或full join)三种。

    3、交叉连接：返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

8.读写分离

8.1 为什么进行读写分离

  • 高并发场景下MySQL的一种优化方案，依靠主从复制使得MySQL实现了数据复制为多份，把读操作和写操作分离出来，增强了抵抗 高并发读请求的能力，提升了MySQL查询性能同时，也提升了数据的安全性。当某一个MySQL节点，无论是主库还是从库故障时，还有其他的节点中存储着全量数据，保证数据不会丢失。

    • 读写分离原理：
      执行SQL语句的时候，判断到底是读操作还是写操作，把读的操作转向到读服务器上（从服务器，一般是多台），写的操作转到写的服务器上（主服务器，一般是一台，视数据量来看）。

    ​ 当然为了保证多台数据库数据的一致性，需要主从复制。

    读写分理，主从复制

8.2 主从复制

MySQL通过三个线程来完成主从库间的数据复制，其中Binlog Dump线程跑在主库上，I/O线程和SQL线程跑着从库上

  • 主从复制原理：
    1. 将主数据库的DDL（数据库定义语言）和DML（数据库操作语言）的变更操作写入到二进制日志（bin log）中
    2. 在从库上启动复制时，先创建一个I/O线程连接主库
    3. 主库创建Binlog Dump线程读取Binlog中的数据库事件并发送给I/O线程，I/O线程将其更新到从库的Relay Log（中继日志）中去
    4. 然后在从库创建SQL线程读取中继日志Relay Log中的数据库事件，对这些日志进行重新执行，从而使从数据库和主数据库的数据保持一致。

8.3 了解一些常见的日志

bin log
  • binlog记录了数据库表结构和表数据变更的sql语句，比如update/delete/insert/truncate/create；它不会记录select（因为这没有对表没有进行变更）。
  • 作用：
    • 通过binlog来实现的（主从复制）。
    • 数据库的数据被干掉了，我们可以通过binlog来对数据进行恢复。
redo log
  • 对数据库中的一条数据进行修改时，需要先将这条记录读取至内存，在内存中进行修改，之后再写回去。如果在内存中修改完之后，还没有写入磁盘之前，数据库就发生了故障，此时这条修改就会丢失。它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
  • 作用：
    • redo log的作用是为持久化而生的。写完内存，如果数据库挂了，那我们可以通过redo log来恢复内存还没来得及刷到磁盘的数据，将redo log加载到内存里边，那内存就能恢复到挂掉之前的数据了。binlog的作用是复制和恢复而生的。
undo log
  • undo log是回滚日志，提供回滚操作，来保证事务的原子性。

8.4 主从复制几种复制形式

  • 异步复制

    • MySQL主从异步复制是最常见的复制场景。数据的完整性依赖于主库BINLOG的不丢失，只要主库的BINLOG不丢失，那么就算主库宕机了，我们还可以通过BINLOG把丢失的部分数据通过手工同步到从库上去。
    • 缺点：主库和从库的数据之间难免会存在一定的延迟，这样存在一个隐患：当在主库上写入一个事务并提交成功，而从库尚未得到主库的BINLOG日志时，主库由于磁盘损坏、内存故障、断电等原因意外宕机，导致主库上该事务BINLOG丢失，此时从库就会损失这个事务，从而造成主从不一致。

  • 多线程复制

  • 半同步复制

    • 而半同步复制时，为了保证主库上的每一个BINLOG事务都能够被可靠地复制到从库上，主库在每次事务成功提交时，并不及时反馈给前端应用用户，而是等待至少一个从库(详见参数rpl_semi_sync_master_wait_for_slave_count)也接收到BINLOG事务并成功写入中继日志后，主库才返回Commit操作成功给客户端

      半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的BINLOG日志上，另一份在至少一个从库的中继日志Relay Log上，从而更进一步保证了数据的完整性。

posted @ 2021-02-27 18:00  维他命D片  阅读(185)  评论(0)  编辑  收藏  举报