MySQL笔记

最新推荐文章于 2024-09-11 11:59:38 发布

音域探险家

最新推荐文章于 2024-09-11 11:59:38 发布

阅读量3.5k

点赞数 27

文章标签： sql redis mysql 数据库数据库开发大数据

本文链接：https://blog.csdn.net/2401_87228802/article/details/142029779

版权

MySql服务的启动和关闭

net stop mysql123关闭MySql服务

net start mysql123开启MySql服务

MySql服务端的登录和退出

方式一：通过mysql自带的客户端，但只限于root用户，直接输入密码

方式二：通过windows自带的客户端

服务端登录：mysql -h localhost -P 3305 -u root -p

登录本机数据库时，可以简写 mysql -u root -p

服务端退出：exit

MySql常用命令

1、查看当前数据库 show databases;

2、打开指定的库 use 库名

3、查看当前库的所有表 show tables

4、查看其它库的所有表 show tables from 库名

5、创建表 create table 表名（

列名列类型，

。。。）；

6、查看表结构 desc 表名

7、查看服务器版本

第一种方式：登录到mysql服务端输入命令 select version();

第二种方式：在windows命令行下输入命令 mysql --version 或者 mysql --V

MySql语法规范

1、不区分大小写，但建议关键字大写，表名、列名小写；

2、每条命令最好用分号结尾；

3、每条命令可以根据需要，可以进行缩进和换行

4、注释：单行注释：#注释文字或者单行注释： --注释文字

多行注释:/* 注释文字*/

在SQLyog界面中，想要执行哪条语句，选中语句去执行就行了

SELECT 基础查询

SELECT 查询列表 from 表名

特点：查询列表可以是：表中字段，常量值，表达式，函数，查询结果为一个虚拟表格

为显示的结果起别名（上面加一个列名）：select last_name as 别名；其中as可以省略，直接空格代替

当别名与关键字冲突时，要加上双引号 select last_name "姓" from employees;

去重：在查询列表前加关键字DISTINCT

mysql中的”+“符号：只做加法运算

select 120+90;如果都是数字，执行加法

select "123"+49;//如果有字符串，将字符串转换成数字，执行加法；

select "jonn"+29;;//如果有字符串，将字符串转换成数字失败，则字符串表示0，执行加法

select null+20;如果有null,结果必为null

拼接查询列表，输出一列用CONCAT关键字,如果拼接项中包括null，则拼接结果为null

SELECT CONCAT(last_name,"_",first_name) AS 姓名 FROM employees；

IFNULL关键字：如果某字段或者表达式为NULL,则用第二参数代替

IFNULL(commission_pct,0)

如果列表项为null，则用0代替null

SELECT CONCAT(last_name,"_",first_name) AS 姓名,
IFNULL(commission_pct,0) AS 奖金
FROM employees;

SELECT 条件查询

使用where字句

格式：select 查询列表

from 表名

where 查询条件

分类：1、按条件表达式筛选 < > = != <> >= <=

LIKE关键字:可以判断数值型和字符串型，通常与通配符一起使用，如”%“百分号通配符，表示可以替代任意多个字符，”_"下划线通配符可以替换一个字符，在使用LIKE时，如果要匹配 _ 需要使用转义符ECCAPE

select last_name from employees where last_name LIKE '_\_%'

或者

ESCAPE '$'表示美元符号后面的'_'为所要匹配的字符，而不是通配符，不是必须要使用美元符号，其他类似符号也行，如!、、#、&

select last_name from employees where last_name LIKE '_$_%' ESCAPE '$'

BETWEEN val1 AND val2 显示查询值位于val1与val2之间

IN （val1,val2,val3) 显示查询值为val1,val2，val3，作用为判断某字段的值是否属于IN列表中的某一项，提高语句简洁度，IN列表的类型必须统一，IN列表中不支持通配符

NOT IN （val1,val2,val3) 显示查询值不为val1,val2，val3的信息

IS NULL、IS NOT NULL的使用，选择显示字段项是NULL的员工，即无奖金的员工

IS NOT NULL 显示有奖金的员工

SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;

SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

安全等于 <=>,既可以判断NULL值，也可以判断普通类型的值，可读性比较差

SELECT last_name,salary FROM employees
WHERE salary <=> 12000;

1、数据库的好处：可以持久化数据到本地，结构化查询

2、数据库常见概念：

DB：数据库，

DBMS：数据库管理系统，

SQL：结构化查询语言，用于与数据库通信的语言

排序查询

order by 排序列表 asc/desc;

可以按字段、表达式、别名、函数排序，可以多字段排序

order by 执行顺序一般放在查询语句的最后面，limit字句除外

SELECT *FROM employees 
ORDER BY salary DESC;

SELECT * FROM employees
WHERE department_id >=90
ORDER BY hiredate;

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees
ORDER BY 年薪 DESC;

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

#先按工资排序，再按员工序号降序排序
SELECT * FROM employees
ORDER BY salary ASC,employee_id DESC;

常见函数

select 函数名(实参列表) 【from employees】

函数：1、单行函数，如ifnull,concat,length 2、分组函数：作统计使用

mysql中的索引从1开始

常见函数：LENGTH() 、 CONCAT() 、 UPPER() 、LOWER()、 SUBSTR() 、INSTR() 、TRIM() 、LPAD() 、 RPAD() 、REPLACE()

#常见函数
#select 函数名(实参列表) from employees

#字符函数
SELECT LENGTH('join');#获取字节个数
SELECT CONCAT(last_name,'_',first_name) FROM employees;
SELECT UPPER('join');
SELECT LOWER('JOin');
#实例
SELECT CONCAT(LOWER(last_name),'_',UPPER(first_name)) 姓名
FROM employees;

#substr:1、返回从索引位置开始的字符串，2、返回从索引位置开始且长度为一定值的字符串
SELECT SUBSTR('李莫愁爱上了陆湛远',7) out_put;
SELECT SUBSTR('李莫愁爱上了陆湛远',1,3) out_put;

#实例,将姓名中的首字母大写
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) 姓名
FROM employees;

#instr():返回第二个字符串在第一个字符串中第一次出现的起始索引，如果找不到返回0
SELECT INSTR('杨不悔爱上殷六侠','殷六侠') AS out_put;

#trim：删除字符串前后指定的字符，默认删除空格
SELECT TRIM('    join   ') AS output;

SELECT TRIM('a' FROM 'aaaaaaa张aaaaaa翠aa山aaaaaa') 
AS out_put;

#lpad():用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*') AS out_put;//左填充7个*号实现长度10

#rpad():用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',10,'*') AS out_put;

#replace():替换
SELECT REPLACE('杨不悔爱上殷六侠','殷六侠','王雷') AS out_put;

数学函数:ROUND()、CEIL()、FLOOR()、TRUNCATE()、MOD()

#数学函数
#round()四舍五入
SELECT ROUND(-1.5);
SELECT ROUND(1.567,2);#小数点后保留两位

#ceil()向上取整,返回大于等于该参数的最小整数
SELECT CEIL(1.25);
SELECT CEIL(-1.25);

#floor()向下取整，返回小于等于该参数的最大整数
SELECT FLOOR(1.5);
SELECT FLOOR(-2.5);

#truncate()截断,第二个参数表示小数点后保留几位
SELECT TRUNCATE(1.65,1);

#mod() 取余
SELECT MOD(10,3);
SELECT MOD(10,-3);

日期函数:NOW()、CURDATE() 、CURTIME()、YEAR()、MONTH()、MONTHNAME()、STR_TO_DATE()、DATE_FORMATE()

#日期函数
#now() 返回系统当前日期+时间
SELECT NOW();

#curdate() 只返回日期
SELECT CURDATE();

#curtime() 只返回当前时间
SELECT CURTIME();

#获取时间和日期的指定部分
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-8-17') 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;#monthname返回英文月份名

STR_TO_DATE(str,format)函数是将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值。
SELECT STR_TO_DATE('9-13-1998','%m-%d-%Y');

#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees
WHERE hiredate=STR_TO_DATE('4-3 1992','%m-%d %Y');


#date_format()：将日期转换成字符
SELECT DATE_FORMAT('2019/6/5','%Y年%m月%d日');
SELECT DATE_FORMAT('2019/6/5','%Y年%c月%d日');

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name 姓名,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

其他函数

#其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
SELECT PASSWORD('字符'):返回该字符的密码形式
SELECT MD5('字符'):返回该字符的密码形式

流程控制函数：IF 、 CASE

#流程控制函数
#1.if函数 ：if else的效果,第一个表达式为真则，输出第二个参数，否则输出第三个参数
SELECT IF (10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;

#case 函数：两个使用方式
#格式一：
/*case 要判断的字段或表达式
  when 常量1 then 要显示值或者语句
  whne 常量2 then 要显示值或者语句
  .....
  else 要显示值或者语句
  end
*/
/*员工工资，要求部门号为30，显示工资为1.1倍
部门号为40，显示工资为1.2倍
部门号为50，显示工资为1.3倍
部门号为其他，显示工资为1倍
*/
SELECT last_name,department_id, salary 原始工资,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 加倍工资
FROM employees;

/*使用二:类似于多重if，进行区间判断
case 
when 条件1 then 要显示值或者语句
when 条件2 then 要显示值或者语句
....
else 要显示值或者语句
end
*/
/*查询员工的工资情况
工资大于20000，显示A级别
工资大于15000，显示B级别
工资大于10000，显示C级别
否则显示D级别
*/
SELECT last_name,department_id,salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 级别
FROM employees;

SELECT NOW();

SELECT last_name,salary,job_id,salary*1.2 AS 新工资
FROM employees;

SELECT last_name,LENGTH(last_name) AS 长度
FROM  employees
ORDER BY SUBSTR(last_name,1,1);

SELECT CONCAT(last_name,' earn ',salary,' monthly but wants ',salary*3) AS Dream_Salary
FROM employees;

SELECT job_id AS Job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE 'D'
END AS gread
FROM employees;

分组函数

/*分组函数：用于统计作用，又称为聚合函数或统计函数或组函数
分类：
sum求和、avg平均值、max最大值、min最小值、count计算个数
1、sum,avg用于处理数值型，max,min,count可以处理任何类型

2、是否忽略NULL值
分组函数都忽略NULL值，不处理NULL项
3、都可以和DISTINCT搭配
4、和分组函数一起查询的字段有限制，要求是group by后的字段

*/
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,AVG(salary) 平均值,MIN(salary) 最小值,MAX(salary) 最大值,COUNT(salary) 个数
FROM employees;

SELECT SUM(salary) 和,ROUND(AVG(salary)) 平均值,MIN(salary) 最小值,MAX(salary) 最大值,COUNT(salary) 个数
FROM employees;

#可以和DISTINCT搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary) FROM employees;
#count函数的详细介绍
SELECT COUNT(*) FROM employees;#统计行数
SELECT COUNT(1) FROM employees;#统计行数
#效率：
/*
MYISAM存储引擎下，count(*)效率最高
INNODB存储引擎写，count(*)与count(1)差不多，count(字段)效率比较低
*/

#例题
SELECT DATEDIFF(NOW(),'1997-3-25');

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))  最大相差天数
FROM employees;

SELECT COUNT(department_id) AS 个数
FROM employees
WHERE department_id=90;

分组查询

/*语法：select 分组函数，列（要求出现在group by的后面）from 表【where 筛选条件】group by 分组的列表【order by 字句】

注意：查询列表比较特殊，要求是分组函数和group by 后出现的字段

特点：1、分组查询的筛选条件分为两类分组前筛选，数据源为原始表，位置为group by字句前面，关键字为where 分组后筛选，数据源为分组后的结果集，位置为group by字句后面，关键字为having

分组函数做条件放在having子句中

groub by字句支持单个字段、多个字段、表达式、函数分组也可以添加排序（排序放在整个分组查询最后

#查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

#查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#添加筛选条件

#案例：查询邮箱中包含‘a'字符的，每个部门的平均工资
SELECT  email,AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#添加复杂的筛选条件

#案例1：查询哪个部门的员工个数大于2

#第一步，求出每个部门的员工个数
#第二步，根据第一步，利用HAVING在后面添加查询语句
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#案例二
#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary) 最高工资,job_id 工种编号
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

#案例三 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

#按表达式或函数分组
#案例一 按员工姓名长度分组，查询每一组员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*) 个数,LENGTH(last_name) 长度
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

#按多个字段分组
#案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

#添加排序
#案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
HAVING AVG(salary)>15000
ORDER BY AVG(salary) DESC;

连接查询/*含义：多表查询，当查询的字段来自多个表时，就会用到连接查询笛卡尔乘积现象：表1：n行，表2：m行,结果m*n行发生原因：没有有效的连接条件如何避免：添加上有效的连接条件分类：按年代分类：sql92标准（仅支持内连接），sql99标准按功能分：内连接：等值连接，非等值连接，自连接外连接：左外连接、右外连接，全外连接

交叉连接

#sql92标准
#1、等值连接
/*
1、多表等值连接的结果为多表的交集部分
2、n表连接，至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般需要为表起别名
5、可以搭配前面介绍的所有字句使用
*/

#查询女神名和对应的男朋友
SELECT NAME ,boyname FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;

#查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#2、为表其别名,起了别名就必须使用别名，提高语句简洁度，区分多个重名的字段
#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees  AS e,jobs AS j
WHERE e.job_id=j.job_id;

#3、加筛选
#查询有奖金的员工名和部门名
SELECT e.last_name,d.department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#查询出城市名中第二字符为'o'的对应的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND l.city LIKE '_o%';

#4、加分组
#查询一下每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

#查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY department_name;

#5、加排序
#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT COUNT(*),job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
 
#6、实现三表连接
#查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.`department_id` 
AND d.`location_id`=l.`location_id`;
#2、非等值连接
#创建工资级别表
CREATE TABLE job_grades
(grade_level VARCHAR(3),lowest_sal INT,highest_sal INT);

INSERT INTO job_grades
VALUES ('A',1000,2999);

INSERT INTO job_grades
VALUES ('B',3000,5999);

INSERT INTO job_grades
VALUES ('C',6000,9999);

INSERT INTO job_grades
VALUES ('D',10000,14999);

INSERT INTO job_grades
VALUES ('E',15000,24999);

INSERT INTO job_grades
VALUES ('F',25000,40000);

SELECT * FROM job_grades;
#查询员工工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.grade_level='A';

#3、自然连接：重复使用一个表
#查询员工名以及上级名称
SELECT e.employee_id 员工编号,e.last_name 员工名,m.employee_id 领导编号,m.last_name 领导名
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

#例题
SELECT MAX(salary),AVG(salary)
FROM employees;

SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_id DESC,salary ASC;

SELECT job_id
FROM employees
WHERE job_id LIKE '%a%e%';


SELECT NOW();
SELECT TRIM('     nihao     ');
SELECT SUBSTR('nihao',1,2);

1、如何看索引是否生效？索引有哪些类型？

在查询语句之前加上explain，explain的功能是：显示了MYSQL如何使用索引来处理select语句以及连接表。

MySQL目前主要有的索引类型为：普通索引、唯一索引、主键索引、组合索引、全文索引。

普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询
唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。简单来说：唯一索引是加速查询 + 列值唯一（可以有null）
主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。一般是在建表的时候同时创建主键索引
组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀法则。
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

2、说说InnoDB和MyISAM的区别？

事务

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务。
外键

myisam不支持外键；Innodb支持外键。
索引

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和主键索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

小结：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
锁

InnoDB支持表、行(默认)级锁MyISAM支持表级锁
存储文件

myisam在磁盘上存储上有三个文件.frm(存储表定义) .myd（存储表数据） .myi（存储表索引）；

innodb磁盘上存储的是表结构文件、表空间数据文件和日志文件，innodb表大小只受限于操作系统大小。
myisam保存表具体行数；innodb不保存。

3、索引该怎么建？@

cteate index  索引名   on  表名（建立索引所需的字段）

表的主键、外键必须有索引；
经常要把哪几个字段拿来进行查询或者排序这个时候可以考虑建立索引
经常与其他表进行连接的表，在连接字段上应该建立索引。
索引应该建在选择性高的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
频繁进行数据操作的表，不要建立太多的索引

4、如何优化慢查询？或者避免慢查询？

慢查询非常多的情况是由于SQL没有走适当的索引导致的。但是，即使加上索引，如果使用不正确，SQL语句在执行时依然不会走索引。

5、B+树和B树的区别？为什么用B+树？

B+树相比B树的优势：　　

1.单一节点存储更多的元素，在相同的数据量下，B+树更加的"矮胖“，使得查询的IO次数更少。

2.所有查询都要查找到叶子节点，查询性能稳定。　　

3.所有叶子节点形成有序链表，便于范围查询。

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

6、什么是联合索引？最左匹配？

联合索引指的是对一张表上的多个列进行索引。也就是说，表上多个列加起来组成一个索引，供快速查询使用。 从本质上说，联合索引还是一个B+树，不过联合索引的键值数量不是1，而是大于等于2.

联合索引的一个优势：当两个列的组合是唯一值时，联合索引是个不错的选择。

联合索引的另一个好处就是可以对第二个键值进行排序，例如，在很多时候我们需要查询某个用户的购物情况，并按照时间排序。这时使用联合索引（userid,buy_date）可以减少一次排序操作，因为这个索引本身在叶子节点上已经排序了。

最佳左前缀法则：当索引了多列时，要采用最佳左前缀法则，即查询从索引中的最左边索引列开始，中间不要跳过索引中的列，否则后面的索引列用不到

7、数据库表设计三大范式原则？事务的四大特性？

数据库三大范式：

1.第一范式：每一列都是不可分割的原子性数据项，不能是集合、数组非原子数据项；如果一个列中存在多个值，需要将改了拆分成不同的列；第一范式就是每一列保证不可拆分，保证原子性；

2.第二范式：在满足数据库第一范式的前提下，所有数据必须要完全依赖于主键；如果表中存在复合主键的情况下不符合第二范式；一张表只能描述一件事；表中每一列都必须依赖于主键；

3.第三范式：满足第二范式的前提下，任何非主键列不得传递依赖于主键(称为解耦不知是否合适？)。传递依赖：A→B→C，C字段传递依赖于A字段，不满足第三范式要求：主键字段A→非主键字段B→非主键字

事务的ACID属性：

原子性(Atomicity)。原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性(Consistency)。事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。比如：如果从A账户转账到B账户，不可能因为A账户扣了钱，而B账户没有加钱。

隔离性(Isolation)。多个事务之间相互独立；事物的隔离性是指一个事务的执行不能被其他事物干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。

持久性(Durability)。持久性是指一个事务一旦被提交，他对数据库中的数据改变是永久性的，接下来的其他操作和数据库故障不应该对其由产生任何影响。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

8、聚簇索引和非聚簇索引

聚簇索引，数据文件是和主键索引绑在一起的

非聚簇索引，索引和数据文件是分离的，索引保存的是数据文件的指针

9、MySQL事务隔离级别？每种隔离级别解决了什么问题？

1、对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读:一个事务读到另一个事务未提交的更新数据。 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.之后, 若 T2 回滚, T1读取的内容就是临时且无效的.

不可重复读: 一个事务两次读同一行数据，可是这两次读到的数据不一样。对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.之后, T1再次读取同一个字段, 值就不同了.

幻读: 一个事务执行两次查询，但第二次查询比第一次查询多出了一些数据行。对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响，避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高, 数据一致性就越好，但并发性越弱。

事务的隔离级别

读未提交（read uncommitted）:一个事务还没有提交时，它做的变更就能被别的事务看到。有脏读、不可重复读、幻读

读提交（read committed）:一个事物提交之后，它做的变更才会被其他事务看到。避免脏读，有不可重复读和幻读

可重复读（repeatable read）:避免脏读和不可重复读，有幻读,可用于数据校对的场景，固定一个时间点的数据不变进行校对，即使校对过程中出现了数据变更，也不会影响开始校对前创建的视图

串行化（serializable）:避免脏读、幻读和不可重复读

mysql中默认的为第三个隔离级别 repeatable read

oracle默认第二个隔离级别 read committed

查看隔离级别语句：select @@tx_isolation;

设置隔离级别：set session transaction isolation level 隔离级别;

10、SQL优化方法

sql调优的步骤：

1、慢查询的开启并捕获

2、explain+慢SQL分析

3、show profile 查询SQL在MySQL服务器里面的执行细节和生命周期情况。

4、SQL数据库服务器的参数调优。

调优方法：

1、永远小表驱动大表

即小的数据集驱动大的数据集，优化的目标是减小循环次数，使用in和exists时执行的顺序不一样，使用in先执行的是子查询语句，循环次数为子查询中表的记录数据，所以应该使子查询中表的记录数小，使用exists时主查询语句先执行，循环次数为外面的表的记录数，所以应该使外表的记录数小，

避免索引失效的方法：

1、最好全值匹配：筛选条件完全包括索引列。

2、最佳左前缀法则：当索引了多列时，要采用最佳左前缀法则，即查询从索引中的最左边索引列开始，中间不要跳过索引中的列，否则后面的索引列用不到

3、不要在索引列上做任何操作，如计算，函数，类型转换等，会导致索引失效而造成全表扫描。例如，查询varchar类型的name时，查询条件写成name=1000,会发生隐式类型转换，造成全表扫描。

4、存储引擎不能使用索引中范围条件右边的列，即age>10，age之后的列使用不到。

5、尽量使用覆盖索引，即查询的字段最好包括索引列，减少回表查询数据

6、mysql在使用不等于的时候（!=或者<>）会导致索引失效而造成全表扫描

7、is null和is not null也无法使用索引

8、like以通配符%开头，mysql会索引失效造成全表扫描，百分号应该放在字符串右边，此时部分使用到当前索引，字符串开头为字符可以匹配一定结果，类似于范围，由此也能使用到后面的索引列，如果百分号放在开头，导致无法匹配一定结果，导致全表扫描

11、什么是回表？

主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

回表是发生在二级索引中的；在使用二级索引查询数据时，如果要查询的字段中拥有索引字段和主键之外的字段时，此时就需要回表；使用在二级索引树中查询到的主键值去主键索引树中查询具体的行记录，然后在具体行记录中取得最终的数据。

12、分库和分表 @

-------丹源医学：50分钟

为什么分库

如果业务量剧增，数据库可能会出现性能瓶颈，这时候我们就需要考虑拆分数据库。从这几方面来看：

磁盘存储

业务量剧增，MySQL单机磁盘容量会撑爆，拆成多个数据库，磁盘使用率大大降低。

并发连接支撑

我们知道数据库连接是有限的。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！当前非常火的微服务架构出现，就是为了应对高并发。它把订单、用户、商品等不同模块，拆分成多个应用，并且把单个数据库也拆分成多个不同功能模块的数据库（订单库、用户库、商品库），以分担读写压力。

分库的优点：

①减少大量数据写入时的锁对查询的影响

②由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短，但是它无法解决单表数据量太大的问题。

什么是分库

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上，通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

分表：就是一个数据库表分成多个表。例如原始表可以将1、2、3月的订单的数据分别放到不同数据表中。

为什么要分表？

数据量太大的话，SQL的查询就会变慢。如果一个查询SQL没命中索引，千万数据量级别的表可能会拖垮整个数据库。

即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢啦。

如何分库分表

1、垂直拆分

垂直拆分可以进行垂直分库、垂直分表。

垂直分库，将原来一个单数据库的压力分担到不同的数据库，可以很好应对高并发场景

垂直分表，如果一个单表包含了几十列甚至上百列，管理起来很混乱，每次都select *的话，还占用IO资源。这时候，我们可以将一些不常用的、数据较大或者长度较长的列拆分到另外一张表。

2、水平拆分

水平分库是指，将表的数据量切分到不同的数据库服务器上，每个服务器具有相同的库和表，只是表中的数据集合不一样。它可以有效的缓解单机单库的性能瓶颈和压力。

水平分表，如果一个表的数据量太大，可以按照某种规则（如hash取模、range），把数据切分到多张表去。表的结构一样，只是数据集合不同

水平分库分表策略

分库分表策略一般有几种，使用与不同的场景：

range范围

即范围策略划分表。可以按主键进行划分，也可以按时间进行划分。

优点是有利于扩容，不需要数据迁移，即使后面又增加了很多数据，只需要增加相应分库/分表的个数就行了。

缺点是容易产生热点问题，对于分库/表中拥有热点的，可能会导致其中一个分库/分表中的数据被频繁访问
hash取模

指定的路由key（一般是user_id、订单id作为key）对分表总数进行取模，把数据分散到各个表中。

优点是hash取模的方式，不会存在明显的热点问题。

缺点是如果一开始按照hash取模分成4个表了，未来某个时候，表数据量又到瓶颈了，需要扩容，这就比较棘手了。比如你从4张表，又扩容成8张表，那之前id=5的数据是在（5%4=1，即t_order_1），现在应该放到（5%8=5，即t_order_5），也就是说历史数据要做迁移了。
range+hash取模混合

比较简单的做法就是，在拆分库的时候，我们可以先用range范围方案，比如订单id在0~4000万的区间，划分为订单库1;id在4000万~8000万的数据，划分到订单库2,将来要扩容时，id在8000万~1.2亿的数据，划分到订单库3。然后订单库内，再用hash取模的策略，把不同订单划分到不同的表。

什么时候分表？

如果你的系统处于快速发展时期，如果每天的订单流水都新增几十万，并且，订单表的查询效率明变慢时，就需要规划分库分表了。一般B+树索引高度是2~3层最佳，如果数据量千万级别，可能高度就变4层了，数据量就会明显变慢了。不过业界流传，一般500万数据就要考虑分表了。

什么时候分库

业务发展很快，还是多个服务共享一个单体数据库，数据库成为了性能瓶颈，就需要考虑分库了。比如订单、用户等，都可以抽取出来，新搞个应用（其实就是微服务思想），并且拆分数据库（订单库、用户库）。

如何查看 MySQL 服务被多少个客户端连接了？

show processlist进行查看

空闲连接会一直占用着吗？

当然不是了，MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

当然，我们自己也可以手动断开空闲的连接，使用的是 kill connection + id 的命令。

一个处于空闲状态的连接被服务端主动断开后，这个客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

MySQL 的连接数有限制吗？

MySQL 服务支持的最大连接数由 max_connections 参数控制，比如我的 MySQL 服务默认是 100 个,超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念，它们的区别如下：

// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

可以看到，使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接。

但是，使用长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

怎么解决长连接占用内存的问题？

有两种解决方式。

第一种，定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接。

第二种，客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

至此，连接器的工作做完了，简单总结一下：

与客户端进行 TCP 三次握手建立连接；
校验客户端的用户名和密码，如果用户名或密码不对，则会报错；
如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；

什么是索引？

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

所谓的存储引擎，说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL 存储引擎有 MyISAM 、InnoDB、Memory，其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。

我们可以按照四个角度来分类索引。

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

MySQL实现记录存在时就更新，不存在时就插入？

replace into  table_name(...) values(...)

MySQL实现记录存在时就忽略，不存在时就插入？

insert ignore into table_name(...) values(...)

什么是MVCC

对于读提交和可重复读隔离级别下的事务而言，在执行事务前会生成read view，该read view类似于快照，共有四个字段：

创建read view的事务id
创建read view时，未提交但活跃的事务id列表
活跃的事务id中最小的id，min_trx_id
活跃的事务id最大的id+1，max_trx_id

而对于主键索引记录，每个记录都包含两个隐藏的字段，分别为：

最近修改这条记录的事务id
roll_pointer指针，每次对记录进行改动时都会将旧版本的记录写到undo日志中，指针指向旧版本记录，于是可以通过它找到修改前的记录，MySQL 会记录相应的 undo log，并以链表的方式串联起来，形成版本链

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
- 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
- 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）

可重复读是如何工作的？

可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

读提交是如何工作的？

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。

也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。

幻读，next-key lock锁？

当多个事务并发执行的时候，会引发脏读、不可重复读、幻读这些问题，那为了避免这些问题，SQL 提出了四种隔离级别，分别是读未提交、读已提交、可重复读、串行化，从左往右隔离级别顺序递增，隔离级别越高，意味着性能越差，InnoDB 引擎的默认隔离级别是可重复读。

要解决脏读现象，就要将隔离级别升级到读已提交以上的隔离级别，要解决不可重复读现象，就要将隔离级别升级到可重复读以上的隔离级别。

而对于幻读现象，不建议将隔离级别升级为串行化，因为这会导致数据库并发时性能很差。InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过 next-key lock 锁（行锁+间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志）来保证的；
隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

MySQL 有哪些锁？

在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

全局锁是怎么用的？

要使用全局锁，则要执行这条命：

flush tables with read lock

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

对数据的增删改操作，比如 insert、delete、update等语句；
对表结构的更改操作，比如 alter table、drop table 等语句。

如果要释放全局锁，则要执行这条命令：

unlock tables

当然，当会话断开了，全局锁会被自动释放。

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

delete与truncate的区别

1、delete可以加where条件，truncate不能加

2、truncate删除，效率比较高，直接删除所有数据

3、如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。

4、truncate删除没有返回值，delete有返回值

5、truncate删除不能回滚，delete删除可以回滚

加全局锁又会带来什么缺点呢？

加上全局锁，意味着整个数据库都是只读状态。

那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

有的，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法

MySQL 表级锁有哪些？具体怎么用的。

MySQL 里面表级别的锁有这几种：

表锁；
元数据锁（MDL）;
意向锁；
AUTO-INC 锁

表锁

如果我们想对学生表（t_student）加表锁，可以使用下面的命令：

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

unlock tables

另外，当会话退出后，也会释放所有表锁。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

元数据锁

再来说说元数据锁（MDL）。

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

MDL 不需要显示调用，那它是在什么时候释放的?

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更

意向锁

接着，说说意向锁。

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁

最后，说说 AUTO-INC 锁。

在为某个字段声明 AUTO_INCREMENT 属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是， AUTO-INC 锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此，在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁；
当 innodb_autoinc_lock_mode = 2，就采用轻量级锁；
当 innodb_autoinc_lock_mode = 1，这个是默认值，两种锁混着用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就采用 AUTO-INC 锁。

不过，当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是会带来一定的问题。因为并发插入的存在，在每次插入时，自增长的值可能不是连续的，这在有主从复制的场景中是不安全的。

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

行级锁的类型主要有三类：

Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

普通的 select 语句是不会对记录加锁的，如果要在查询时对记录加行锁，可以使用下面这两个方式：

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

对记录加锁时，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

MySQL 版本的行级锁的加锁规则

唯一索引等值查询：

当查询的记录是存在的，next-key lock 会退化成「记录锁」。
当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。

非唯一索引等值查询：

当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

唯一索引在满足一些条件的时候，next-key lock 退化为间隙锁和记录锁。
非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

update 没加索引会锁全表？

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

在 update 语句的 where 条件使用了唯一索引，那么 next-key 锁会退化成记录锁，也就是只会给一行记录加锁。

update 语句的 where 带上索引就能避免全表记录加锁了吗？

并不是。

关键还得看这条语句在执行过程中，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

又该如何避免这种事故的发生？

我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

当 sql_safe_updates 设置为 1 时。

update 语句必须满足如下条件之一才能执行成功：

使用 where，并且 where 条件中必须有索引列；
使用 limit；
同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足以下条件能执行成功：

同时使用 where 和 limit，此时 where 条件中可以没有索引列；

如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

设置事务等待锁的超时时间。

当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值是 50 秒。

当发生超时后，就出现下面这个提示：

开启主动死锁检测。

主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。

事务 A 和事务 B 的间隙锁范围都是一样的，为什么不会冲突？

两个事务的间隙锁之间是相互兼容的，不会产生冲突。

间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享（S型）和排他（X型）的间隙锁是没有区别的，他们相互不冲突，且功能相同。

当事务B生成间隙锁，而之后事务A想要在这个间隙插入数据的话，会被阻塞并生成插入意向锁。

插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。

插入意向锁的生成时机：

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，那 Insert 语句会被阻塞，并生成一个插入意向锁。

MySQL日志？

更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志）、binlog （归档日志）这三种日志：

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

为什么需要 undo log？

考虑一个问题。一个事务在执行过程中，在还没有提交事务之前，如果MySQL 发生了崩溃，要怎么回滚到事务之前的数据呢？

如果我们每次在事务执行过程中，都记录下回滚时需要的信息到一个日志里，那么在事务执行中途发生了 MySQL 崩溃后，就不用担心无法回滚到事务之前的数据，我们可以通过这个日志回滚到事务之前的数据。

实现这一机制就是 undo log（回滚日志），它保证了事务的 ACID 特性 (opens new window)中的原子性（Atomicity）。

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。比如当 delete 一条记录时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据，然后进行 insert 操作。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

通过 trx_id 可以知道该记录是被哪个事务修改的；
通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

另外，undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）。

对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见行，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

因此，undo log 两大作用：

实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

为什么需要 Buffer Pool（缓存池）？

MySQL 的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？

当然是缓存起来好，这样下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了。

为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

有了 Buffer Pool 后：

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

Buffer Pool 缓存什么？

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

如何管理 Buffer Pool？

如何管理空闲页？

Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。

那当我们从磁盘读取数据的时候，总不能通过遍历这一片连续的内存空间来找到空闲的缓存页吧，这样效率太低了。

所以，为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）。

Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

Free 链表节点是一个一个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于 Free 链表节点都对应一个空闲的缓存页。

有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除。

如何管理脏页？

设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。

那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页

有了 Flush 链表后，后台线程就可以遍历 Flush 链表，将脏页写入到磁盘。

如何提高缓存命中率？

Buffer Pool 的大小是有限的，对于一些频繁访问的数据我们希望可以一直留在 Buffer Pool 中，而一些很少访问的数据希望可以在某些时机可以淘汰掉，从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还能保证常用数据留在 Buffer Pool 中。

要实现这个，最容易想到的就是 LRU（Least recently used）算法。

该算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。

简单的 LRU 算法的实现思路是这样的：

当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部。
当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点。

简单的 LRU 算法并没有被 MySQL 使用，因为简单的 LRU 算法无法避免下面这两个问题：

预读失效；
Buffer Pool 污染；

什么是预读失效？

先来说说 MySQL 的预读机制。程序是有空间局部性的，靠近当前被访问数据的数据，在未来很大概率会被访问到。

所以，MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。

但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，这个就是预读失效。

如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉。

如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率。

怎么解决预读失效而导致缓存命中率降低的问题？

我们不能因为害怕预读失效，而将预读机制去掉，大部分情况下，局部性原理还是成立的。

要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。

那到底怎么才能避免呢？

MySQL 是这样做的，它改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域和 young 区域。

什么是 Buffer Pool 污染？

当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。

注意， Buffer Pool 污染并不只是查询语句查询出了大量的数据才出现的问题，即使查询出来的结果集很小，也会造成 Buffer Pool 污染。

怎么解决出现 Buffer Pool 污染而导致缓存命中率下降的问题？

像前面这种全表扫描的查询，很多缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入到 young 区域，从而导致热点数据被替换了。

LRU 链表中 young 区域就是热点数据，只要我们提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。

MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。

具体是这样做的，在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：

如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；
如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

这个间隔时间是由 innodb_old_blocks_time 控制的，默认是 1000 ms。

也就说，只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题。

另外，MySQL 针对 young 区域其实做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会。

脏页什么时候会被刷入磁盘？

引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。

因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。

可能大家担心，如果在脏页还没有来得及刷入到磁盘时，MySQL 宕机了，不就丢失数据了吗？

这个不用担心，InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

下面几种情况会触发脏页的刷新：

当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
MySQL 认为空闲时，后台线程回定期将适量的脏页刷入到磁盘；
MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

在我们开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

Undo 页是记录什么？

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

查询一条记录，就只需要缓冲一条记录吗？

不是的。

当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

为什么需要 redo log ？

Buffer Pool 是提高了读写效率没错，但是问题来了，Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术，指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。

什么是 redo log？

redo log 是物理日志，记录了某个数据页做了什么修改，对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

被修改 Undo 页面，需要记录对应 redo log 吗？

需要的。

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

不过，在修改该 Undo 页面前需要先记录对应的 redo log，所以先记录修改 Undo 页面的 redo log ，然后再真正的修改 Undo 页面。

redo log 和 undo log 区别在哪？

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务

所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来， redo log 保证了事务四大特性中的持久性。

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。

磁盘的「顺序写」比「随机写」高效的多，因此 redo log 写入磁盘的开销更小。

针对「顺序写」为什么比「随机写」更快这个问题，可以比喻为你有一个本子，按照顺序一页一页写肯定比写一个字都要找到对应页写快得多。

可以说这是 WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。

至此，针对为什么需要 redo log 这个问题我们有两个答案：

实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能

产生的 redo log 是直接写入磁盘的吗？

不是的。

实际上，执行一个事务，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。

所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘.

redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

redo log 什么时候刷盘？

缓存在 redo log buffe 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有下面几个时机：

MySQL 正常关闭时；
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制）。

InnoDB 还提供了另外两种策略，由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

当设置该参数为 0 时，表示每次事务提交时，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。
当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。
当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache（如果你想了解 Page Cache，可以看这篇 (opens new window)），Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。

innodb_flush_log_at_trx_commit 为 0 和 2 的时候，什么时候才将 redo log 写入磁盘？

InnoDB 的后台线程每隔 1 秒：

针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
针对参数 2 ：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

这三个参数的应用场景是什么？

这三个参数的数据安全性和写入性能的比较如下：

数据安全性：参数 1 > 参数 2 > 参数 0
写入性能：参数 0 > 参数 2> 参数 1

所以，数据安全性和写入性能是熊掌不可得兼的，要不追求数据安全性，牺牲性能；要不追求性能，牺牲数据安全性。

在一些对数据安全性要求比较高的场景中，显然 innodb_flush_log_at_trx_commit 参数需要设置为 1。
在一些可以容忍数据库崩溃时丢失 1s 数据的场景中，我们可以将该值设置为 0，这样可以明显地减少日志同步到磁盘的 I/O 操作。
安全性和性能折中的方案就是参数 2，虽然参数 2 没有参数 0 的性能高，但是数据安全性方面比参数 0 强，因为参数 2 只要操作系统不宕机，即使数据库崩溃了，也不会丢失数据，同时性能方便比参数 1 高

redo log 文件写满了怎么办？

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫：ib_logfile0 和 ib_logfile1 。

在重做日志组中，每个 redo log File 的大小是固定且一致的，假设每个 redo log File 设置的上限是 1 GB，那么总共就可以记录 2GB 的操作。

重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。

所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

我们知道 redo log 是为了防止Buffer Pool 中的脏页丢失而设计的，那么如果随着系统运行，Buffer Pool 的脏页刷新到了磁盘中，那么 redo log 对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出空间记录新的更新操作。

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置

write pos 和 checkpoint 的移动都是顺时针方向；
write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；

如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程

为什么需要 binlog ？

前面介绍的 undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事务执行过程中产生的所有 binlog 统一写入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

为什么有了 binlog，还要有 redo log？

这个问题跟 MySQL 的时间线有关系。

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力

redo log 和 binlog 有什么区别？

1、适用对象不同：

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
redo log 是 Innodb 存储引擎实现的日志；

2、写入方式不同：

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

3、用途不同：

binlog 用于备份恢复、主从复制；
redo log 用于掉电等故障恢复。

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

主从复制是怎么实现？

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成

MySQL 集群的主从复制过程梳理成 3 个阶段：

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库是不是越多越好？

不是的。

因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。

所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

MySQL 主从复制还有哪些模型？

主要有三种：

同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果，并不保证一定能将binlog日志同步到从库。这种模式一旦主库宕机，数据就会发生丢失。
半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

binlog 什么时候刷盘？

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

MySQL 给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

什么时候 binlog cache 会写到 binlog 文件？

在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。

虽然每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件：

图中的 write，指的就是指把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里，write 的写入速度还是比较快的，因为不涉及磁盘 I/O。
图中的 fsync，才是将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高。

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失。

而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

如果能容少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

update 语句的执行过程

当优化器分析出成本最小的执行计划后，执行器就按照执行计划开始进行更新操作。

具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

1、执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录

如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。

2、执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样

如果一样的话就不进行后续更新流程；
如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；

3、开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在修改该 Undo 页面前需要先记录对应的 redo log，所以先记录修改 Undo 页面的 redo log ，然后再真正的修改 Undo 页面。

4、InnoDB 层开始更新记录，根据 WAL 技术，先记录修改数据页面的 redo log ，然后再真正的修改数据页面。修改数据页面的过程是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

5、至此，一条记录更新完了。

6、在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。

7、事务提交，剩下的就是「两阶段提交」的事情了，接下来就讲这个

为什么需要两阶段提交？

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。

举个例子，假设 id = 1 这行数据的字段 name 的值原本是 'jay'，然后执行 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 如果在持久化 redo log 和 binlog 两个日志的过程中，出现了半成功状态，那么就有两种情况：

如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。MySQL 重启后，通过 redo log 能将 Buffer Pool 中 id = 1 这行数据的 name 字段恢复到新值 xiaolin，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行 name 字段是旧值 jay，与主库的值不一致性；
如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。由于 redo log 还没写，崩溃恢复以后这个事务无效，所以 id = 1 这行数据的 name 字段还是旧值 jay，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行 name 字段是新值 xiaolin，与主库的值不一致性；

可以看到，在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功的状态，就会造成主从环境的数据不一致性。这是因为 redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。

MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是分别是「准备（Prepare）阶段」和「提交（Commit）阶段」，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。注意，不要把提交（Commit）阶段和 commit 语句混淆了，commit 语句执行的时候，会包含提交（Commit）阶段。

举个拳击比赛的例子，两位拳击手（参与者）开始比赛之前，裁判（协调者）会在中间确认两位拳击手的状态，类似于问你准备好了吗？

准备阶段：裁判（协调者）会依次询问两位拳击手（参与者）是否准备好了，然后拳击手听到后做出应答，如果觉得自己准备好了，就会跟裁判说准备好了；如果没有自己还没有准备好（比如拳套还没有带好），就会跟裁判说还没准备好。
提交阶段：如果两位拳击手（参与者）都回答准备好了，裁判（协调者）宣布比赛正式开始，两位拳击手就可以直接开打；如果任何一位拳击手（参与者）回答没有准备好，裁判（协调者）会宣布比赛暂停，对应事务中的回滚操作。

两阶段提交的过程是怎样的？

在 MySQL 的 InnoDB 存储引擎中，开启 binlog 的情况下，MySQL 会同时维护 binlog 日志与 InnoDB 的 redo log，为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务（是的，也有外部 XA 事务，跟本文不太相关，我就不介绍了），内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。

当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交

事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

prepare 阶段：将 XID（内部 XA 事务的 ID）写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
commit 阶段：把 XID 写入到 binlog，然后将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件，所以 commit 状态也是会刷盘的）

处于 prepare 阶段的 redo log 加上完整 binlog，重启就提交事务，MySQL 为什么要这么设计?

binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。

所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

事务没提交的时候，redo log 会被持久化到磁盘吗？

会的。

事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘。

也就是说，事务没提交的时候，redo log 也是可能被持久化到磁盘的。

有的同学可能会问，如果 mysql 崩溃了，还没提交事务的 redo log 已经被持久化磁盘了，mysql 重启后，数据不就不一致了？

放心，这种情况 mysql 重启会进行回滚操作，因为事务没提交的时候，binlog 是还没持久化到磁盘的。

所以， redo log 可以在事务没提交之前持久化到磁盘，但是 binlog 必须在事务提交之后，才可以持久化到磁盘

两阶段提交有什么问题？

两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有两个方面的影响：

磁盘 I/O 次数高：对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。
锁竞争激烈：两阶段提交虽然能够保证「单事务」两个日志的内容一致，但在「多事务」的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。

组提交

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这 10 个事务一次性一起刷盘的时间成本则近似于 1。

引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程：

flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；
sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）；
commit 阶段：各个事务按顺序做 InnoDB commit 操作；

上面的每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。

音域探险家

关注

27
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
MySQL笔记

联合索引指的是对一张表上的多个列进行索引。也就是说，表上多个列加起来组成一个索引，供快速查询使用。从本质上说，联合索引还是一个B+树，不过联合索引的键值数量不是1，而是大于等于2.联合索引的一个优势：当两个列的组合是唯一值时，联合索引是个不错的选择。联合索引的另一个好处就是可以对第二个键值进行排序，例如，在很多时候我们需要查询某个用户的购物情况，并按照时间排序。这时使用联合索引（userid,buy_date）可以减少一次排序操作，因为这个索引本身在叶子节点上已经排序了。最佳左前缀法则。
复制链接

扫一扫