Mysql：最详细的介绍，万字长文带你了解 Mysql的知识点虽然很多，但是我总结了一些经常出现的考点供大家学习记忆谈到Mysql，首先我们就需要知道SQL到底是什么，我们为什么要用SQL。SQL概念：Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。后面的介绍太长了，你只需要知道它是一门查询语言就可以了。通过SQL我们可以去定义和操作数据，维护数据的完整性和安全性。SQL有几种呢？1）DDL(Data Definition Language)数据定义语言，用来操作数据库、表、列等，常用语句：CREATE、 ALTER、DROP；2）DML(Data Manipulation Language)数据操作语言，用来操作数据库中表里的数据，常用语句：INSERT、 UPDATE、 DELETE，俗称增删改；3）DQL(Data Query Language)数据查询语言，用来查询数据，常用语句：SELECT，目前SQL里面用的最多的；4）DCL(Data Control Language)数据控制语言，用来操作访问权限和安全级别； 常用语句：GRANT、DENY。讲完了SQL，我们就要来谈谈数据库了，以及数据库设计的三大范式了数据库：长期储存在计算机内、有组织的、可共享的大量数据的集合。三范式：1）第一范式(1NF)：字段具有原子性，不可再分。所有关系型数据库系统都满足第一范式；2）第二范式(2NF)：确保表中的每列都和主键相关。如果表是单主键，那么主键以外的列必须完全依赖于主键，如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。满足第二范式(2NF)必须先满足第一范式(1NF)；3）第三范式(3NF)：确保非主键列都和主键直接相关，而不是间接相关，非主键列之间不能相关依赖。满足第三范式必须要先满足第二范式。数据库了解之后，我们要知道如何去操作数据库1）DDL操作：创建一个数据库-- create database 数据库名称;-- 创建一个叫db1的数据库create database db1;查看创建的数据库基本信息-- 查看创建的数据库信息-- show create database 数据库名称;show create database db1;查出所有的数据库show databases;删除数据库drop database db1;修改数据库的字符集为utf-8-- alter database 数据库名称 character set 字符集;alter database db1 character set utf8;切换数据库use db1;查看当前使用的数据库select database();2）DML操作创建表-- create table 表名(字段1 字段类型,字段2 字段类型,…字段n 字段类型);create table student(id int,name varchar(20),gender varchar(10),);查看当前数据库中的所有表show tables;查看表信息-- show create table 表名;show create table student;查看字段信息-- desc 表名;desc student;修改表名-- alter table 原来表名 rename to 新表名;alter table student rename to stu;修改字段名-- alter table 表名 change 原来字段名 新字段名 varchar(10);alter table stu change name newname varchar(10);修改字段数据类型-- alter table 表名 modify 字段名 字段类型;alter table stu modify newname int;增加字段-- alter table 表名 add 新加字段名 类型;alter table stu add address varchar(100);删除字段-- alter table 表名 drop 字段名;alter table stu drop address;删除表-- drop table 表名;drop table stu;插入数据-- insert into 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);insert into student (id,name,age,gender) values (1,a,20,male);同时插入多条数据-- insert into 表名 [(字段名1,字段名2,...)] VALUES (值 1,值 2,…),(值 1,值 2,…),...;insert into student (id,name,age,gender)values (2,b,17,female),(3,c,19,male),(4,d,18,male);更改数据-- update 表名 set 字段名1=值1[,字段名2 =值2,…] [where 条件表达式];update student set age=20,gender=female where name=d;更改所有数据-- update 表名 set 字段名=值;update student set age=18;删除数据-- delete from 表名 [where 条件表达式];delete from student where age=14;删除所有数据-- delete from 表名;delete from student;这里说一个删除有关的细节知识truncate和delete都能实现删除表中的所有数据的功能，但两者也是有区别的：truncate和delete的区别：(1）delete语句后可以跟where子句，可通过指定where子句中的条件表达式只删除满足条件的部分记录。truncate语句只能用于删除表中的所有记录；(2）使用truncate语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始。使用delete语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1；(3）delete语句是DML语句，truncate语句通常被认为是DDL语句。3）DQL操作查询所有字段-- select * from 表名;select * from student;查询指定字段-- select 字段1,字段2 from 表名;select id,name from student;查询时过滤重复数据-- select distinct 字段 from 表名;select distinct gender from student;-- 注意！！！在查询语句中distinct关键字只能用在第一个所查列名之前因为可能真正开发时还有一些其他的需求，所以在DQL中聚合函数也是很重要的常用的聚合函数：count()：统计表中的数据或者指定列的值不为Null的数据个数。查询该表中有多少人select count(*) from student;max()：计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算。查询该表中年龄的最大值select max(age) from student;min()：计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算。查询该表中年龄的最大值select min(age) from student;sum()：计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0；查询该学生表中年纪的总和select sum(age) from student;avg()：计算指定列的平均值，如果指定列类型不是数值类型则计算结果为0；查询该学生表中年纪的平均数select avg(age) from student;当然，聚合函数使用也是有规则的，只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。除了聚合函数，DQL中还有一些其他的查询，比如条件查询，这个时候我们就需要用到WHERE关键字去过滤了关系运算符，比如>=、=、<=等等查询年龄等于或大于18的学生的信息select * from student where age>=18;IN关键字查询IN关键字用于判断某个字段的值是否在指定集合中，如果字段的值在指定的集合中，则将字段所在的记录将査询出来。查询ID在(1，10)的字段信息select * from student where id in (1,10);-- 如果查询的不在这里面的话就是select * from student where id not in (1,10);BETWEEN AND关键字查询BETWEEN AND用于判断某个字段的值是否在指定的范围之内,如果字段的值在指定范围内，则将所在的记录将查询出来。查询年龄在12-18之间的范围select * from student where age between 12 and 18;-- 如果查询的不在这里面的话就是select * from student where age not between 12 and 18;空值查询使用 IS NULL关键字判断字段的值是否为空值，注意！！！空值NULL不同于0，也不同于空字符串。查询ID不为控制的信息select * from student where id is not null;AND关键字查询AND关键字可以连接两个或者多个查询条件，全部满足才可以查询出来。查询ID大于5并且年龄等于18的信息select * from student where id> 5 and age= 18;OR关键字查询OR关键字连接多个査询条件，只要满足其中任意一个条件就会被查询出来。查询ID大于5或者年龄等于18的信息select * from student where id> 5 or age= 18;LIKE关键字查询LIKE关键字可以判断两个字符串是否相匹配，如果匹配则会查询出匹配的结果。查询name中与zhangsan匹配的学生信息select * from student where name like zhangsan;匹配中也有一种情况，就是含有%通配的字符串，%用于匹配任意长度的字符串。查询name以li开始的信息select * from student where name like li%; -- lisi liwu liliu都可以查询出来还有一种则是还有_通配的字符串，下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。查询姓名为zhao开头且长度为7的select * from student where name like zhao___; -- zhaoliu 后面通配符是三个LIMIT限制查询结果的数量当我们执行查询数据时，有时想查一条可能会返回很多条，这个时候就需要对查询结果进行限制了查询分页条数-- 分页 公式：开始的索引 = （当前的页码-1 ）* 每页显示的条数select * from student limit 0,3; -- 前一个表示从0条开始 ，后面一个表示一页显示几个select * from student limit 3,3;select * from student limit 6,3;GROUP BY进行分组查询一般情况下，GROUP BY和聚合函数是一起使用的。查询各个班级的学生数量，按照班级分组select count(*), classNumber from student group by classNumber;-- 从不同的班级统计学生数量统计学生数量大于50的班级select sum(*),classNumber from student group by classNumber having sum(*)>50;ORDER BY对查询结果排序按年龄升序查询 -- select 字段名1,字段名2,… from 表名 order by 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];select * from student order by age asc;基本的操作讲完之后，我们就要了解一下Mysql事务的四大特性了(高频考点)事务的四大特性：ACID1）原子性(Atomicity)原子性意味着数据库中的事务执行是作为原子，即不可再分，整个语句要么同时执行，要么同时不执行；2.一致性(Consistency)。一致性即在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。比如说，我有50我给你转账10块钱，不管成功了还是失败了，我和你的金额总数保持不变(50)；3.隔离性(Isolation)事务的执行是互不干扰的，相互隔离的，即一个事务不影响其它事务运行效果。你走你的阳关道，我过我的独木桥，互不打扰；4.持久性(Durability)意味着在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库中，不会被回滚。即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中。Char和Varchar的区别(高频考点)区别：1）长度不同char类型的长度是固定的，varchar类型的长度是可变的；2）效率不同char类型每次修改的数据长度相同，效率更高，varchar类型每次修改的数据长度不同，效率更低；3）存储不同char类型存储的时候是初始预计字符串再加上一个记录字符串长度的字节，占用空间较大，char最多可以存放255个字符。varchar类型存储的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小，varchar的最大长度为65535个字节，可存放的字符数跟编码有关。数据表也是有约束的，为了防止错误的数据被插入到数据表中，MySQL中定义了一些维护数据库完整性的规则，这些规则常称为表的约束。主键约束主键约束(primary key)用于唯一的标识表中的每一行，被标识为主键的数据在表中是唯一的且其值不能为空，比如我们每个人的身份证就是唯一标识。-- 字段名 数据类型 primary key;create table student(id int primary key,name varchar(20));非空约束非空约束(NOT NULL)指的是字段的值不能为空。-- 字段名 数据类型 NOT NULL;create table student(id intname varchar(20) not null);唯一约束唯一约束(UNIQUE)用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。-- 字段名 数据类型 UNIQUE;create table student(id int,name varchar(20) unique);默认约束默认约束(DEFAULT)用于给数据表中的字段指定默认值，当在表中插入一条新记录时若未给该字段赋值，那么数据库系统会自动为这个字段插人默认值。-- 字段名 数据类型 DEFAULT 默认值；create table student(id int,name varchar(20),gender varchar(10) default male);外键约束外键约束(FOREIGN KEY)常用于多张表之间的约束。-- CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)-- ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);-- 学生表 主表create table student(id int primary key,name varchar(20));-- 班级表 从表create table class(classid int primary key,studentid int);constraint fk_class_studentid foreign key(studentid) references student(id);alter table class add constraint fk_class_studentid foreign key(studentid) references student(id);删除外键-- alter table 从表名 drop foreign key 外键名；alter table class drop foreign key fk_class_studentid;注意！！！1）从表里的外键通常为主表的主键；2）从表里外键的数据类型必须与主表中主键的数据类型一致。既然谈到了多张表，那么就要讲一下表之间的关联关系了在这里我们只说到了一对一的表关系，但是在实际开发中，表的关系大致有三种。一对一多对一：多对一(一对多)是数据表中最常见的一种关系，比如像学生和班级，一个班级可以有很多学生，但是一个学生只对应一个班级。在多对一的表关系中，应将外键建在多的一方，否则会造成数据的冗余。多对多：多对多是数据表中常见的一种关系，比如像学生和老师，一个老师可以教多个学生，一个学生也可以被多个老师教。在多对多的表关系中，需要定义一张中间表(连接表)，该表会存在两个外键分别参照老师表和学生表。涉及到多张表时，就需要我们进行关联查询了。关联查询查询所有JAVA的学生-- 创建班级表CREATE TABLE class(cid int(4) NOT NULL PRIMARY KEY,cname varchar(30));-- 创建学生表CREATE TABLE student(sid int(8) NOT NULL PRIMARY KEY,sname varchar(30),classid int(8) NOT NULL);-- 为学生表添加外键约束ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(cid);-- 向班级表插入数据INSERT INTO class(cid,cname)VALUES(1,Java);INSERT INTO class(cid,cname)VALUES(2,Python);-- 向学生表插入数据INSERT INTO student(sid,sname,classid)VALUES(1,zhangsan,1);INSERT INTO student(sid,sname,classid)VALUES(2,lisi,1);INSERT INTO student(sid,sname,classid)VALUES(3,wangwu,2);INSERT INTO student(sid,sname,classid)VALUES(4,zhaoliu,2);-- 关联查询select * from student where classid=(select cid from class where cname=Java);当然除了关联查询，多表还有连接查询连接查询连接查询中又包含三种查询：内连接查询、左外连接查询、右外连接查询通过代码会更清楚-- 创建班级表CREATE TABLE class(cid int (4) NOT NULL PRIMARY KEY,cname varchar(20));-- 创建学生表CREATE TABLE student (sid int (4) NOT NULL PRIMARY KEY,sname varchar (20),sage int (2),classid int (4) NOT NULL);-- 向班级表插入数据INSERT INTO class VALUES(1001,Java);INSERT INTO class VALUES(1002,C++);INSERT INTO class VALUES(1003,Python);INSERT INTO class VALUES(1004,PHP);-- 向学生表插入数据INSERT INTO student VALUES(1,zhangsan,10,1001);INSERT INTO student VALUES(2,lisi,11,1002);INSERT INTO student VALUES(3,wangwu,14,1002);INSERT INTO student VALUES(4,zhaoliu,13,1003);内连接查询内连接(Inner Join)称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录，也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。查询学生姓名及其所属班级名称-- SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段select student.sname,class.cname from class inner join student on class.cid= student.sid;外连接查询在内连接查询时，返回的结果只包含符合查询条件和连接条件的数据。但是，我们有时还需要在查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询，外连接分为左外连接和右外连接。-- SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件;注意！！！在使用左外连接和右外连接查询时，查询结果是不一样的。1）LEFT [OUTER] JOIN 左外连接，返回包括左表中的所有记录和右表中符合连接条件的记录，俗话说就是从左到右；2）RIGHT [OUTER] JOIN 右外连接，返回包括右表中的所有记录和左表中符合连接条件的记录，俗话说就是从右到左。左外连接查询左外连接的结果包括LEFT JOIN子句中指定的左表的所有记录，还有所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。查询每个班的班级ID、班级名称还有班级的所有学生的名字select class.cid,class.cname,student.snamefrom class left outer join student on class.cid=student.classid;右外连接查询右外连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。select class.cid,class.cname,student.snamefrom class right outer join student on class.cid=student.classid;除了连接查询之外，常用的还有子查询，子查询：一个查询语句嵌套在另一个查询语句内部的查询，该查询语句可以嵌套在一个SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符、IN、EXISTS、ANY、ALL等关键字。先建表演示-- 创建班级表CREATE TABLE class(cid int (4) NOT NULL PRIMARY KEY,cname varchar(20));-- 创建学生表CREATE TABLE student (sid int (4) NOT NULL PRIMARY KEY,sname varchar (20),sage int (2),classid int (4) NOT NULL);-- 向班级表插入数据INSERT INTO class VALUES(1001,Java);INSERT INTO class VALUES(1002,C++);INSERT INTO class VALUES(1003,Python);INSERT INTO class VALUES(1004,PHP);-- 向学生表插入数据INSERT INTO student VALUES(1,zhangsan,10,1001);INSERT INTO student VALUES(2,lisi,11,1002);INSERT INTO student VALUES(3,wangwu,14,1002);INSERT INTO student VALUES(4,zhaoliu,13,1003);比较运算符的子查询查询张三同学所在班级的信息select * from class where cid=(select classid from student where sname=zhangsan);EXISTS关键字的子查询EXISTS关键字后面的参数可以带任意一个子查询， 不会产生任何数据，它只返回TRUE或FALSE，当返回值为TRUE时外层查询才会执行。张三同学若在学生表中，则从班级表中查询所有班级信息select * from class where exists (select * from student where sname=zhangsan);ANY关键字的子查询ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。查询比任一学生所属班级号码还大的班级号码select * from class where cid > any (select classid from student);带ALL关键字的子查询ALL关键字的子査询返回的结果需同时满足所有内层査询条件。查询比所有学生所属班级号码还大的班级号码select * from class where cid > all (select classid from student);未完待续，日常更新！

不改SQL性能提升5倍！小红书MySQL内核秒杀能力升级实践 “秒杀”是电商平台最典型的高并发促销场景，双十一等大促活动也常以秒杀能力作为数据库技术实力的标志。随着小红书电商业务快速增长，直播带货等爆品场景对极致下单速度的需求更加突出，希望将下单吞吐提升至 1W+/s。基于 MySQL 内核实现的合并秒杀优化，相对排队秒杀方案，将秒杀写入能力再提升 5 倍，相对MySQL 社区版本，更有百倍的性能提升。在设计上保持了和排队秒杀一致的能力，该能力对业务完全透明：仅需升级 MySQL 内核，无需改动 SQL，即可获得 5 倍以上性能提升。该方案不仅显著提升库存、优惠券、红包等高抢购场景的用户体验，也能在热门笔记点赞等高频写入场景实现数量级性能增强。一、背景24年小红书数据库团队首次通过对热点线程排队，将自研版本秒杀性能提升了10倍，但依然跟不上业务的快速发展。尤其是在直播带货、热门笔记点赞、爆品抢购等场景下，业务迫切需要更快的秒杀速度，本次数据库团队在自研内核上迭代实现了合并秒杀方案，将热点行更新速度提升5倍至1.5W/s+，极大提升了用户的使用体验。二、收益概览合并秒杀版本在方案设计时考虑了和排队版本的兼容性，内核会根据SQL自动选择最优的秒杀方案。对于热点SQL，内核会依据SQL自动选择合并秒杀、排队秒杀和普通更新的最优解，只需升级MySQL内核版本，无需业务侧修改SQL即可享受到5倍以上的性能提升。从下面性能分析图可以看到，在128线程秒杀时，TPS从4276提升至23543，提升约5.5倍。在1024线程极端场景下，仍然有4.7倍性能提升。随着线程数的升高，线程切换的开销越来越大，TPS也逐步下降，因此建议线程数保持在128-256之间以获得最佳性能。测试数据均为sysbench模拟标准库存扣减模型进行的压测数据。三、热点瓶颈问题分析首先对秒杀场景进行分析，抽象出了它的事务模型。下面所示为最经典的库存扣减模型：begin;insert into inventory_log value (...);-- 插入库存修改的流水表update inventory set quantity=quantity-1 where sku_id=? and quantity > 0;  -- 扣减库存表commit;随着并发数的增加，数据库的update写入性能急剧下降，最终基本处于不可用的状态（TPS约为100-200），出现非常严重的卡顿，下面将依次分析不同方案的性能瓶颈点和解决思路。1、排队秒杀提升点合并秒杀版本重点解决了性能下降问题，将秒杀性能维持在一个性能基本不变的状态。2、合并秒杀提升点再次分析上面的库存扣减模型，每个事务都是（begin；insert；update；commit）这种格式。那么insert是插入不同的行（主键不一样），所以可以并发，但是update是对同一行的改写，无法并发，红色的标识为临界区（行锁）。所以性能的瓶颈点在update同一行的修改上，秒杀V2将解决该瓶颈点。3、方案总结将上述方案的瓶颈点和解决思路整合如下对比所示，直观反映了各方案的优化思路。四、整体设计合并秒杀将多个事务 SQL 合并到一个事务进行提交，修改了MySQL的事务模型，必然涉及到MySQL事务系统、锁系统、Binlog系统等模块的修改。合并秒杀方案提供了如下优势：生态组件无感知：将改动内容收敛到MySQL内核，输出的 Binlog内容和格式没有变化，对于DTS/Canel等组件无感知，避免了联动组件升级内核升级无感知：不修改InnoDB格式，不影响版本兼容性和版本回退业务SQL无修改：和排队秒杀版本SQL语法兼容，可以动态开关。业务/DBA可以随时将合并秒杀退化到排队秒杀，也可以随时开启。可以同时将合并秒杀，排队秒杀，无秒杀在一个数据库同时跑起来，减少业务迁移成本。一句话总结本方案：合并秒杀通过Leader预读取库存数据写入缓存，在缓存中进行Follower库存数据合并扣减，最后Leader一次性将合并数据写入存储引擎，提升了写入性能1、缓存可见性为了合并秒杀，要解决如下两个问题：数据的可见性：目前MySQL的数据是线程可见的，这样最方便。但是合并秒杀是需要多个线程之间共享数据的。数据一致性问题：Leader-Follower的数据同步问题，要做好状态的流转。1）数据可见性为了解决以上问题，按照表维度添加了全局缓存，多个线程操作同一张表的结构体访问同一份缓存，缓存的生命周期和表结构是一样的，方便缓存管理。2）数据一致性解决了数据的可见性问题，剩下的核心点就是解决Leader-Follower的数据同步问题，保证数据的一致性。下面一张图系统的展示了一个Leader两个Follower线程是如何配合扣减三次的：首先三个客户端发送了相同的update语句。经过了Queue PK，由于开启了合并秒杀，跳过了排队秒杀过程三个线程开始抢独占锁，最先抢到的将自己标记为Leader，然后读取InnoDB数据和更新数据，将修改后的数据写入全局缓存。Leader做完了工作，释放独占锁，开始进入收集状态，等待若干毫秒另外两个Follower开始抢独占锁。抢到的标记为Follower，然后将全局缓存数据写入线程缓存，然后更新线程缓存完成扣减，最后将线程缓存数据再写入全局缓存。释放独占锁，进入等待唤醒状态。在全局缓存中完成Follower的库存扣减后面的线程依次进入Follower过程，按照读全局缓存->完成扣减->更新全局缓存的过程，依次执行了update语句扣减Leader线程完成了收集，重新申请独占锁，将全局缓存数据作为本组最终扣减的值。开始进入2PC过程完成最终数据提交。Leader完成后会唤醒Follower，所有SQL结束2、行锁极致优化上面的过程看起来配合的非常好，是否还有优化空间？再次回到下面的这张图，将update分为两个步骤，一个是收集更新缓存阶段（phase 1），一个是commit阶段（phase 2）。如果将多个组提交按照时间串起来，可以看到上一个组必须commit完释放了行锁，才能让下一个组重新申请行锁。如下图所示。所以整个流水线在组内是合并提交的，但是组和组之间是完全的串行。如按照两个阶段分为两个组，那么在第一个组进行commit的时候，第二个组完全可以开始收集，无需等待第一组commit完成。这样整体的执行时间会进一步压缩。以两个组为例，第一个组从1000扣减50为950，那么在第一个组提交950的时候，第二个组可以从950开始扣减。如下图所示，绿色部分就是节省的时间。3、Binlog并行提交整个组的Binlog是同一批由Leader统一提交。4、Crash Recovery优化Crash Recovery过程简单来说，先由Binlog生成一个事务集合，然后拿到Redolog进行对比，该提交就提交，该回滚就回滚。合并秒杀是Leader提交Binlog，将整个组的Binlog都写入Redolog记录的是合并前后的值（例如1000->900)，但是binlog记录的是每个事务的改动（1000->999, 999->998)。所以要回滚Redolog的内容或者提交Redolog的内容，必须要求整个Binlog组的完整性对于以上两种场景，Crash Recovery需要做好兼容处理。具体的思路如下：以数据组为单位进行扣减数据的提交和回滚，相对事务粒度更大一些。五、秒杀兼容性正如上面所述，在内核中使用的Hint关键字如下：作者介绍张凡凡，小红书关系型数据库研发工程师，主要负责小红书关系型数据库内核研发。韩明顺，小红书电商库存领域研发工程师，小红书热点抢购性能提升的发起者和推进者。程明星，小红书关系型数据库DBA，主要负责小红书关系型数据库运维工作。来源丨公众号：小红书技术REDtech（ID：gh_f510929429e3）dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn

Oracle 正对 MySQL 失去兴趣？开发者需作更多打算 导读：随着Oracle管理这款热门数据库的兴趣越来越冷淡，MySQL 的贡献者们正在考虑夺回控制权。近日，MySQL 社区开发者们正在合作共议，敦促 Oracle 提高其在处理MySQL --这款流行的开源数据库方面的透明度与承诺，同时也在考虑其他方案，包括代码分叉。本月初，在美国旧金山举行的一次数据库会议上，一群对 MySQL 社区感兴趣的开发者聚在一块，讨论了最近Oralce对该数据库系统处理方式的担忧，很多人正是依靠该系统创立了公司或开始的职业生涯。还有一个重要原因，Oracle 的MySQL 核心开发团队最近还出现了大规模裁员。MySQL的创始开发者Michael “Monty” Widenius 听到这个消息说“心碎了” ，从GitHub上肉眼可见该项目提交数量的急剧下降，让一些开发者认为它被打入了“冷宫”，正处于一个关键的十字路口。Vadim Tkachenko 曾就职于 MySQL AB，这家瑞典公司最初开发了该数据库，后被 Sun Microsystems 收购，Sun Microsystems 随后又与 Oracle 合并。现如今，Tkachenko 担任开源咨询公司 Percona 的首席技术官（CTO）。他认为MySQL 在 Oracle 的管理下正处于关键十字路口，开发者社区现在必须选择其未来：要么继续留在 Oracle 旗下，要么加入其他模式。Percona 联合创始人兼 MySQL 性能专家 Peter Zaitsev 表示道，Oracle 对 MySQL 的处理方式就像温水煮青蛙：MySQL 的服务临界点可能要到为时已晚，才会被人们广泛理解。“他们正将越来越多的功能迁移到云端和企业软件中，同时也在裁减MySQL团队，但他们并没有采取任何补救的措施。这对MySQL社区来说显然非好事，MySQL的潜力更远远没有得到充分发挥，”他如此说道。为此，一群全球开发者在世界各地飞到美国聚会，讨论MySQL未来发展的潜在方向。该小组包括来自Percona和PlanetScale的软件工程师，PlanetScale围绕Vitess（https://github.com/vitessio/vitess，一个基于MySQL的分布式数据库）构建了数据库服务。PlanetScale联合创始人兼首席执行官Sam Lambert表示道：“MySQL对互联网运行至关重要，并为数百万种产品提供支持。PlanetScale致力于MySQL的未来发展。我们维护着自己的MySQL分支……我们将始终支持MySQL这项技术，并利用我们的工程资源来确保其健康发展。我们希望能够在一个开放且蓬勃发展的社区中实现这一目标。”这次小组会议亦并非专门面向开发者用户，而是更多地面向贡献代码的开发者和围绕开源 MySQL 构建的企业。Zaitsev表示道，Oracle的一位代表也出席了会议。“我很感激他们还真的来了。因为很明显，他们不会在这种场合受到很多人的喜爱，所以他们能来真是太好了，”他说道。摆在桌面上的选项有两个：一是将MySQL的管理权仍交给Oracle，二是创建一个开源数据库的分支，供社区开发和管理。Zaitsev如此解释道，这样的分支可以是硬分支，也可以是跟踪分支。MariaDB 就是一个典型的硬分叉例子，它是创始人 Widenius 在 2009 年分叉出来的。该数据库仍旧是开源的，由一个基金会集中管理，并隶属于一家独立的公司 MariaDB PLC。自分叉以来，MariaDB 的发展与 MySQL 基本上是完全独立的。Zaitsev表示道，Percona的MySQL服务器则是一个跟踪分支的例子。该公司称，该服务器以开源形式提供企业级功能。“我们会应用一些补丁和其他一些更改，但我们始终会跟踪它（与MySQL相兼容）。跟踪分支通常比硬分支具有更高的兼容性，与MariaDB相比，它与MySQL的兼容性要好得多。”他如此说道。该MySQL开发者组织计划召开更多会议。最近将包含一场专门针对欧洲开发者的会议，该会议或与开源大会 Fosdem 26 联手举行。Fosdem 已经决定于一月末在布鲁塞尔举办一场数据库大会。在决定最佳前进方向之前，该开发者组织还可能需要收集更多开发者的反馈意见。历史上，开源社区一直以来都擅长通过从厂商关联的系统中分叉代码来夺取项目控制权。例如，Linux 基金会在 2024 年推出了 Valkey，其代码就分叉自流行的缓存/数据库 Redis。Valkey 得到了 AWS、Google Cloud、Oracle、Ericsson、Snap 以及 Percona 的支持。Zaitsev表示，MySQL开发者团队已经与美国和其他关键地区的主要云供应商以及规模较小的云计算机公司进行了洽谈。“他们很多客户都因为MySQL缺少某些功能而感到痛苦，例如向量搜索。但在正式发布之前，我们都不会做任何承诺。”无论结果如何，MySQL 的开发者社区需要强烈要求 Oracle 对近期的做法做个明确说法。https://www.theregister.com/2026/01/23/mysql_post_oracle/

MySql 数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER数据查询语言DQL（Data Query Language）SELECT数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK1.1 三大范式第一范式：字段具有原子性，不可再分（字段单一职责）第二范式：满足第一范式，每行应该被唯一区分，加一列存放每行的唯一标识符，称为主键（非主属性都要依赖主键）第三范式：满足一二范式，且一个表不能包含其他表已存在的非主键信息（不间接依赖即不存在其他表的非主键信息）范式优点与缺点：优点：范式化，重复冗余数据少，更新快，修改少，查询时更少的 distinct缺点：因为一个表不存在冗余重复数据，查询可能造成很多关联，效率变低，可能使一些索引策略无效，范式化将列存在不同表中，这些列若在同一个表中可以是一个索引。1.2InnoDB 与 MyISAM 区别MySQL 5.5 之前，MyISAM 引擎是默认存储引擎，5.5 版本之后，InnoDB 是默认存储引擎。（1）InnoDB 支持事务（实现了 SQL 标准定义的四个隔离级别，具有提交和回滚事务的能力。默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock））、外键、行锁和表锁（默认为行级锁），被用来处理大量短期事务，MyISAM 不支持事务、外键、只支持表锁。（2）索引实现MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。InnoDB 引擎中，其数据文件本身就是索引文件。其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶子节点 data 域保存了完整的数据记录。MyISAM 索引文件和数据文件是分离的，索引保存的是数据文件的指针。InnoDB 数据与索引一起保存.ibd，MyISAM 表结构.frm 索引.myi 数据.myd。InnoDB 聚簇索引，MyISAM 非聚簇索引（数据和索引分开存储）。（3）MyISAM 支持全文索引，InnoDB5.6 后支持。（4）MyISAM 查询更优，InnoDB 更新更优。使用场景：MyISAM 适合读多，更新少的场景，MyISAM 使用非聚簇索引，数据和索引分开存的，因此有读取更快的说法。而 InnoDB 数据和索引存一起的，数据量大时，一个内存页大小固定，读进内存的数据就多一点(数据量小看不出差距，数据量大时差距就明显）。因为 MyISAM 只把索引指针读进内存，可以存更多，查询速度也就更快，而且 InnoDB 还需要维护其他数据，比如其他隐藏字段 row_id、tx_id 等。InnoDB 适合插入更新频繁的：索引与数据一起放，建立索引更复杂，使用行锁，更新频繁效率更高。需要事务，高并发场景用 Innodb：Innodb 支持事务，采用行锁。MVCC锁的粒度过大会导致性能的下降， MySQL 的 InnoDB 引擎下存在一种性能更优越的 MVCC 方法。MVCC 是 Multi-Version Concurremt Control 的简称，意思是基于多版本的并发控制协议，通过版本号避免同一数据在不同事务间的竞争。它主要是为了提高数据库的并发读写性能，不用加锁就能让多个事务并发读写。MVCC 的实现依赖于隐藏列、Undo log、 Read View 。标准的 SQL 隔离级别定义里，REPEATABLE-READ(可重复读)是不可以防止幻读的。但是 InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：快照读 ：由 MVCC 机制来保证不出现幻读。当前读 ：使用 Next-Key Lock（临键锁） 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE 隔离级别。1.3 自增主键MySQL 5.7 及之前的 InnoDB 引擎的自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。MySQL 8.0 版本后，自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值，这才有了“自增值持久化”的能力。也就是实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”。推荐自增id作为主键问题普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会「导致普通索引的存储空间较大」使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，直接「按照顺序插入」，不用刻意维护页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动，「导致页分裂维护成本较高」MySQL 8.0 版本：自增值修改机制：如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。自增值新增机制：如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；否则，自增值不变。为什么自增主键不连续1.在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。2.事务回滚（自增值不能回退，因为并发插入数据时，回退自增 ID 可能造成主键冲突）。3.唯一键冲突（由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键=现在变了的子增值+1，所以不连续）eg：假设，表 t 里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：insert into t values(null, 1, 1); (自增 id,唯一键 c,普通字段 d)这个语句的执行流程就是：1.执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是(0,1,1);2.InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；3.将传入的行的值改成(2,1,1)；4.将表的自增值改成 3；5.继续执行插入数据操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。这个表的自增值改成 3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键 c 冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。所以，在这之后，再插入新的数据行时，拿到的自增 id 就是 3。也就是说，出现了自增主键不连续的情况。1.4 索引索引可以提高查询速度，查询使用优化隐藏器提高性能，但是也会占据物理空间，降低增删改的速度，因为还要操作索引文件。索引实现本质就是一个查找算法。二叉树：当数据量庞大时，二叉树的深度会变得非常大，索引树会变成参天大树，每次查询会导致很多磁盘 IO。多叉树：多叉树解决了了树的深度大的问题。B+树的叶子节点存放了所有的索引值。创建索引方法：ALTER TABLE table_name ADD INDEX index_name (column_list)；CREATE INDEX index_name ON table_name (column_list)；在 CREATE TABLE 时创建1.4.1 常见索引类型数据结构角度（实现层面）B-Tree 索引（采用 B+树的数据结构）；哈希索引（基于哈希表实现）；R-Tree 索引；全文索引B-tree 索引能够加快访问数据的速度，不需要进行全表扫描，而是从索引树的根节点层层往下搜索，在根节点存放了索引值和指向下一个节点的指针。其特点：1.在叶子节点存放所有的索引值，非叶子节点值是为了更快定位包含目标值的叶子节点2.叶子节点的值是有序的3.叶子节点之间以链表形式关联对于 InnoDb 存储引擎的 B-tree 索引，会按以下步骤通过索引找到行数据如果使用了聚簇索引(主键)，则叶子节点上就包含行数据，可直接返回如果使用了非聚簇索引(普通索引)，则在叶子节点存了主键，再根据主键查询一次上面 的聚簇索引，最后返回数据对于 MyISAM 存储引擎的 B-tree 索引，会按以下步骤通过索引找到行数据在 MyISAM 的索引树的叶子节点上除了索引值之外即没存储主键，也没存储行数据，而是存了指向行数据的指针，根据这个指针在从表文件查询数据。哈希索引是基于哈希表（键值对的集合）来实现的，只有精确匹配所有列才能生效。既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？ 主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了，并且每次 IO 只能取一个。物理存储角度（应用层面）普通索引：可以重复。普通索引的唯一作用就是为了快速查询数据。自增主键：一般会建立与业务无关的自增主键，保证空间利用率，不会触发叶子节点分裂。主键自增是无法保证完全自增的，遇到唯一键冲突、事务回滚等都可能导致不连续。主键索引：唯一不为空，叶子结点存了行记录数据，InnoDB中也称聚簇索引（clustered index）。非主键索引：非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index），用二级索引查需要回表操作（根据二级索引查到主键，再根据主键去主键索引查）。二级索引又称为辅助索引。唯一索引：唯一，可为空，表中只有一个主键索引，可多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。单列索引：以某一个字段为索引。联合索引：两个及以上字段联合组成的索引。使用时需要注意满足最左匹配原则！一次性最多联合16个。覆盖索引：指一个索引包含或覆盖了所有需要查询字段的值，无需回表查询，即索引本身存了对应的值。覆盖索引好处：1.避免了对主键索引(聚簇)的二次查询 2.由于不需要回表查询(从表数据文件)所以大大提升了 Mysql 缓存的负载。回表：通过索引找到主键，再根据主键 id 去主键索引查。（覆盖索引就不走回表）[当所要查找的字段不在非主键索引树上时，需要通过叶子节点的主键值去主键索引上获取对应的行数据，这个过程称为回表操作。]索引下推：5.6引入的，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少最后的回表操作。[索引下推主要是减少了不必要的回表操作。对于查找出来的数据，先过滤掉不符合条件的，其余的再去主键索引树上查找。]延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。InnoDB存储: *.frm文件是一份定义文件，也就是定义数据库表是一张怎么样的表。*.ibd文件则是该表的索引，数据存储文件，既该表的所有索引树，所有行记录数据都存储在该文件中。MyISAM存储：*.frm同上。*.MYD文件是MyISAM存储引擎表的所有行数据的文件。*.MYI文件存放的是MyISAM存储引擎表的索引相关数据的文件。MyISAM引擎下，表数据和表索引数据是分开存储的。MyISAM查询：MyISAM下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。普通索引与唯一索引查询比较查询会以页为单位将数据页加载进内存，不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果，普通索引查到第一条记录往后遍历直到不满足条件，由于都在内存中，不需要磁盘读取那么大开销，带来的额外查询开销忽略不计，所以查询性能几乎一致。更新比较唯一索引由于更新时要检查唯一性，所以需要将数据页先加载进内存才能判断，此时直接操作内存，不需要操作 change buffer。补充：普通索引若数据在内存中则直接更新，否则会将更新操作先记录到 channge buffer 中，等下一次查询将数据读到内存中再进行 change buffer 里相关更新操作后将数据返回，这样在写多读少场景就减少了磁盘 IO，若写完马上查询，就大可不必用 change buffer，不但没提高多少效率还造成维护 change buffer 额外消耗。将 change buffer 的操作对应到原始数据页的操作称为 merge（在查询来时读到内存再修改数据，后台线程也会 merge，数据库正常关闭也会 merge）。适合场景写多读少，选用普通索引更好，可以利用 change buffer 进行性能优化减少磁盘 IO，将更新操作记录到 change bufer，等查询来了将数据读到内存再进行修改。唯一索引更加适合查询的场景。1.4.2 聚簇索引与非聚簇索引聚簇索引指的是索引和行数据在一起存储。也就是在一颗 B+树的叶子结点上存储的不仅是他的索引值，还有对应的某一行的数据。聚簇索引不是一种索引，而是一种数据存储组织方式 ！！！crreate table test( col1 int not null, col2 int not null, PRIMARY KEY(col1), KEY(col2) )如上所示，表 test 由两个索引，分别是主键 col1 和普通索引 col2。这俩索引跟聚簇非聚簇有啥关系呢？会生成一个聚簇索引和一个非聚簇索引(二级索引)，也就是说会组织两个索引树。主键索引会生成聚簇索引的树 以及以 col2 为索引的非聚簇索引的树。InnoDb 将通过主键来实现聚簇索引 ，如果没有主键则会选选一个唯一非空索引来实现。如果没有唯一非空索引则会隐式生成一个主键。下图是聚簇索引的数据组织方式。col1 为主键索引的聚簇索引树，索引列是主键 col1可以看出叶子结点除了存储索引值 列 col1 (3994700)值 之外还存储了其他列的值，如列 col2 (92813),如果还有别的列的话也会存储，或者换句话说聚簇索引树在叶子节点上存储某个索引值对应的一行数据。下图是非聚簇索引(二级索引)的数据组织方式。索引列是 col2与聚簇索引不同的是非聚簇索引在索引树叶子节点上除了索引值之外只存了主键值。而聚簇索引则存了一行数据。假如有一条 sql 语句 :select * from test where col2=93;上面这条语句会经历两次从索引树查找过程:1.第一步从非聚簇索引的索引树上找到包含 col2=93 的叶子节点，并定位到行的主键 32.第二步 根据主键 3 在从聚簇索引定位包含 主键=3 的叶子节点并返回全部行数据。以上说的都是基于 InnoDb 存储引擎的,MyISAM 是不支持聚簇索引的，因为他的数据文件和索引文件是相互独立存储的 MyISAM 存储引擎的索引树的叶子节点不会寸主键值，而存一个指向对应行的地址或者说是指针，然后再从表数据文件里去找，如下面图所示。结论：聚簇索引:通常由主键或者非空唯一索引实现的，叶子节点存储了一整行数据非聚簇索引：又称二级索引，就是我们常用的普通索引，叶子节点存了索引值和主键值，在根据主键从聚簇索引查InnoDB支持聚簇索引，MyISAM不支持聚簇索引。1.4.3 索引使用策略索引设置原则：出现在 where 子句或则连接子句中的列基数小的表没必要使用短索引，如果索引长字符串列，应该指定前缀长度（index(filed(10))）定义有外键的数据列一定索引不要过度索引更新频繁的不适合区分度不高的不适合，如性别尽量扩展索引，别新建索引，如(a)->(a,b)字符串字段建立索引方法1、直接创建完整索引，这样可能比较占用空间；2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；3、倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；4、额外用一个字段进行索引，额外计算开销。总结：索引设计原则要求查询快，占用空间少；一般建在 where 条件，匹配度高的；要求基数大，区分度高，不要过大索引，尽量扩展，用联合索引，更新频繁不适合、使用短索引。索引失效场景：以“%”开头的 like 语句，索引无效，后缀“%”不影响or 语句前后没有同时使用索引列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换）如果 mysql 估计使用全表扫描比索引快，则不用索引（键值少，重复数据多）组合索引要遵守最左前缀原则——不使用第一列索引 失效在索引字段上使用 not，<>，！= （对它处理是全表扫描）对索引字段进行计算操作，字段使用函数也会失效is null1.4.4 Explaintype：表示 MySQL 在表中找到所需行的方式，或者叫访问类型type=ALL，全表扫描，MySQL 遍历全表来找到匹配行type=index，索引全扫描type=range，索引范围扫描type=eq_ref，唯一索引type=NULL，MySQL 不用访问表或者索引，直接就能够得到结果（性能最好）possible_keys: 表示查询可能使用的索引key: 实际使用的索引key_len: 使用索引字段的长度rows: 扫描行的数量Extra：using index：覆盖索引，不回表using where：回表查询using filesort：需要额外的排序，不能通过索引得到排序结果1.4.5 其它B+树、hash、红黑树：hash：底层是哈希表实现，等值查询，可以快速定位，一般情况效率很高，但不稳定，当出现大量键重复哈希冲突，效率下降，不支持范围查询，无法用于排序分组，无法模糊查询，不支持多列索引的最左前缀匹配，任何时候都避免不了回表操作等。B+树：非叶子结点不存 data，只存 key，查询更稳定，增大了广度(B+树出度更大，树高矮，节点小，磁盘 IO 次数少)；叶子结点下一级指针（范围查询）；索引冗余。B+树的页节点都是由双向列表连接的，而页里面的记录则是用单向链表连接的，所以获取区间数据也会更高效。与红黑树相比：更少查询次数：B+树出度更大，树高更低，查询次数更少磁盘预读原理：为了减少 IO 操作，往往不严格按需读取，而是预读。B+树叶子结点存储相临，读取会快一些。存储更多索引结点：B+树只在叶子结点储存数据，非叶子结点存索引，而一个结点就是磁盘一个内存页，内存页大小固定，那么相比 B 树这些可以·存更多的索引结点，出度更大，树高矮，查询次数少，磁盘 IO 少。B+树比B树优势在于：B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比B树更加矮胖，IO次数更少。B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。B+树中更有利于对数据扫描，可以避免B树的回溯扫描。B树和B+ 树B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。1.5 事务事务：一系列操作组成，要么全部成功，要么全部失败。事务 ACID 特性：原子性（Atomicity）：一些列操作要么全部成功，要么全部失败。undolog（记录事务开始前的老版本数据，可以保证原子操作，回滚，实现 MVCC 版本链）。一致性（Consistency）：数据库总时从一个一致状态变到另一个一致状态（事务修改前后的数据总体保证一致转账）隔离性（Isolation）：事务的结果只有提交了其他事务才可见。MVCC--多版本并发控制持久性（Durability）：事务提交后，对数据修改永久的。redo log（记录事务开启后对数据的修改，可用于 crash-safe）事务的并发问题：脏读：读到未提交的数据不可重复读：一个事务下，两次读取数据不一致（侧重内容数据的修改）幻读：事务 A 按照一定条件进行数据读取， 期间事务 B 插入了相同搜索条件的新数据，事务 A 再次按照原先条件进行读取时，发现了事务 B 新插入的数据 称为幻读（侧重新增或删除，插入数据读到多了一行）隔离级别原理及解决问题分析：隔离级别脏读不可重复读幻读READ UNCOMMITTED（读未提交）可能可能可能READ COMMITTED（读已提交，oracle默认）不可能可能可能REPEATABLE READ(可重复读，mysql默认)不可能不可能*可能SERIALIZABLE(可串行化)不可能不可能不可能MySQL 中 RR 级别的隔离是已经实现解决了脏读，不可重复读和幻读的。读未提交：原理：直接读取数据，不能解决任何并发问题读已提交：读操作不加锁，写操作加排他锁，解决了脏读。原理：利用 MVCC 实现，每一句语句执行前都会生成 Read View（一致性视图）可重复读：MVCC 实现，只有事务开始时会创建 Read View，之后事务里的其他查询都用这个 Read View。解决了脏读、不可重复读，快照读（普通查询，读取历史数据）使用 MVCC 解决了幻读，当前读（读取最新提交数据）通过间隙锁解决幻读（lock in share mode、for update、update、detete、insert），间隙锁在可重复读下才生效。（默认隔离级别）可串行化：原理：使用锁，读加共享锁，写加排他锁，串行执行总结：读已提交和可重复读实现原理就是 MVCC Read View 不同的生成时机。可重复读只在事务开始时生成一个 Read View，之后都用的这个；读已提交每次执行前都会生成 Read View。脏页（内存数据页和磁盘数据页不一致）刷脏页情景：redo log 写满了，停止所有更新操作，将 checkpoint 向前推进，推进那部分日志的脏页更新到磁盘。系统内存不够，需要将一部分数据页淘汰，如果是干净页，直接淘汰就行了，脏页的话，需要全部同步到磁盘。mysql 自认为空闲时mysql 正常关闭之前1.6日志WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。事务在提交写入磁盘前，会先写到 redo log 里面去。如果直接写入磁盘涉及磁盘的随机 I/O 访问，涉及磁盘随机 I/O 访问是非常消耗时间的一个过程，相比之下先写入 redo log，后面再找合适的时机批量刷盘能提升性能。为了保证事务 ACID 中的一致性与原子性，mysql 采用 WAL，预写日志，先写日志，合适时再写磁盘。innodb 引擎有 undo log（回滚日志） 与 redo log（重做日志），mysql server 级别有 bin log（归档日志）。binlog 是MySQL server层的日志，而redo log 和undo log都是引擎层（InnoDB）的日志，要换其他数据引擎那么就未必有redo log和undo log了。1.6.1UndoLog作用：undolog 记录事务开始前老版本数据，用于实现回滚，保证原子性，实现 MVCC，会将数据修改前的旧版本保存在 undolog，然后行记录有个隐藏字段回滚指针指向老版本。一般是逻辑日志。redo log 是属于引擎层(innodb)的日志，redo log 和undo log的核心是为了保证innodb事务机制中的持久性和原子性，事务提交成功由redo log保证数据持久性，而事务可以进行回滚从而保证事务操作原子性则是通过undo log 来保证的。undo log 的主要应用场景分别：事务回滚 ：后台线程会不定时的去刷新buffer pool中的数据到磁盘，但是如果该事务执行期间出现各种错误(宕机)或者执行rollback语句，那么前面刷进去的操作都是需要回滚的，保证原子性，undo log就是提供事务回滚的。MVCC：当读取的某一行被其他事务锁定时，可以从undo log中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据——快照读。MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。undo log 数据主要分两类：insert undo loginsert 操作的记录，只对事务本身可见，对其他事务不可见(这是事务隔离性的要求)，故该undo log可以在事务提交后直接删除，不需要进行purge操作。update undo logupdate undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。在InnoDB存储引擎中，undo log使用rollback segment回滚段进行存储，每隔回滚段包含了1024个undo log segment。MySQL5.5之后，一共有128个回滚段。即总共可以记录128 * 1024个undo操作。每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务。1.6.2RedoLogredo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎，称为重做日志。每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成。作用：会记录事务开启后对数据做的修改，crash-safe特性：空间一定，写完后会循环写，有两个指针 write pos 指向当前记录位置，checkpoint 指向将擦除的位置，redolog 相当于是个取货小车，货物太多时来不及一件一件入库太慢了这样。就先将货物放入小车，等到货物不多或则小车满了或则店里空闲时再将小车货物送到库房。用于 crash-safe，数据库异常断电等情况可用 redo log 恢复。刷盘时机：InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：0 （延迟写）：设置为 0 的时候，表示每次事务提交时都只把 redo log 留在redo log buffer中，开启一个后台线程，每1s刷新一次到磁盘中。为0时，如果MySQL挂了或宕机可能会有1秒数据的丢失。1（实时写，实时刷） ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）为1时， 只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。2（实时写，延迟刷）：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache，具体刷盘时机不确定。为2时， 只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘。除上面几种机制外，还有其它两种情况会把redo log buffer中的日志刷到磁盘：1、InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。2、当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘。原因：因为在事务执行过程 redo log 记录是会写入redo log buffer 中，这些 redo log 记录会被后台线程刷盘。硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。 它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生「内存抖动」现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷盘了。日志文件组中还有两个重要的属性，分别是 write pos、checkpointwrite pos 是当前记录的位置，一边写一边后移checkpoint 是当前要擦除的位置，也是往后推移每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。两阶段提交：redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。binlog（归档日志）保证了MySQL集群架构的数据一致性。虽然它们都属于持久化的保证，但是则重点不同。 在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。何为两阶段提交？（2PC）mysql 中在 server 层级别有个 binlog 日志，归档日志，用于备份，主从同步复制，如果采用一主多从架构，主备切换，那就必须用到 binlog 进行主从同步。此时事务提交就必须保证 redolog 与 binlog 的一致性，一般情况没有开启 binlog 日志，事务提交不会两阶段提交，若需要主从同步就必须开启 binlog 使用两阶段提交保证数据一致性。为什么要两阶段提交？为了保证 binlog 和 redo log 两份日志的逻辑一致，最终保证恢复到主备数据库的数据是一致的，采用两阶段提交的机制。保证事务在多个引擎的原子性。两阶段提交过程？Prepare 阶段：InnoDB 将回滚段 undolog 设置为 prepare 状态；将 redolog 写文件并刷盘；（1、先写 redolog，事务进入 prepare 状态）Commit 阶段：Binlog 写入文件；binlog 刷盘；InnoDB commit；（2、prepare 成功，binlog 写盘，然后事务进入 commit 状态，同时会在 redolog 记录 commite 标识，代表事务提交成功）redolog 与 binlog 怎样联系起来的？（XID）崩溃恢复的时候，会按顺序扫描 redo log，若 redolog 既有 prepare 又有 commit，直接提交如果碰到只有 prepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。2pc 不同时刻的崩溃恢复？1、redolog 有 commite 标识，事务完整，直接提交事务2、若 redolog 里面的事务只有完整的 prepare，则判断对应事务的 binlog 是否存在并完整 （是-提交事务 | 否-回滚事务）为什么不直接修改磁盘中的数据？因为直接修改磁盘数据的话，它是随机IO，修改的数据分布在磁盘中不同的位置，需要来回的查找，所以命中率低，消耗大，而且一个小小的修改就不得不将整个页刷新到磁盘，利用率低；与之相对的是顺序IO，磁盘的数据分布在磁盘的一块，所以省去了查找的过程，节省寻道时间。使用后台线程以一定的频率去刷新磁盘可以降低随机IO的频率，增加吞吐量，这是使用buffer pool的根本原因。如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值，再加上是顺序写，所以刷盘速度很快。所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。同为操作数据变更的日志，有了binlog为什么还要redo log？两者记录的数据变更粒度是不一样的。以修改数据为例，binlog 是以表为记录主体，在ROW模式下，binlog保存的表的每行变更记录。MySQL 是以页为单位进行刷盘的，每一页的数据单位为16K，所以在刷盘的过程中需要把数据刷新到磁盘的多个扇区中去。而把16K数据刷到磁盘的每个扇区里这个过程是无法保证原子性的，如果数据库宕机，那么就可能会造成一部分数据成功，而一部分数据失败的情况。而通过 binlog 这种级别的日志是无法恢复的，因为一个update可能更改了多个磁盘区域的数据，所以这个时候得需要通过redo log这种记录到磁盘数据级别的日志进行数据恢复。redo logbin log文件大小大小是固定的可通过max_binlog_size设置每个binlog文件大小实现方式InnoDB引擎层实现的，并不是所有引擎都有server层实现的，所有引擎都可使用binlog记录方式采用循环写的方式记录，当写到结尾时，会回到开头循环写日志通过追加方式记录，当文件大小大于给定值后，后续日志会记录到新文件中适用场景适用于崩溃恢复（crash-safe）适用于主从复制由以上两者的对比可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。同样只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。1.6.3BinLogbinlog 是逻辑日志，用于主备同步，记录内容是语句的原始逻辑，属于MySQL Server 层。会记录所有涉及更新数据的逻辑操作（记录所有数据库表结构变更（例如create、alter table）以及表数据修改(insert、update、delete)的二进制日志），这其中不包括select、show，因为对数据没有修改，并且是顺序写。有 3 种格式，5.7.7之前默认statement，之后默认row，可以通过binlog_format参数指定：row：基于行的复制，记录对数据库做出修改的语句所影响到的数据行以及对这些行的修改。优点是能够完全的还原或者复制日志被记录时的操作。缺点：记录每行数据，占空间，IO 压力大，性能消耗大statement：基于SQL语句的复制，记录对数据库进行修改的语句本身，有可能会记录一些额外的相关信息。优点是 binlog 日志量少，IO 压力小，性能较高。缺点是由于记录的信息相对较少，在不同库执行时由于上下文的环境不同可能导致主备不一致。mysql> delete from t where a>=4 and b<=5 limit 1;主库是索引 a,那么删除 a=4；备库是索引 b,那么删除 b=5mixed：混合使用上述两种模式，一般的语句使用 statment 方式进行保存，如果遇到一些特殊的函数，则使用 row 模式进行记录。MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。但是在生产环境中，一般会使用 row 模式。写入机制：1、事务执行过程中将日志记录到 binlog cache（系统为 binlog 分配了一块内存，每个线程一份）2、事务提交时，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。write：把日志写到文件系统的 page cache，没有写磁盘，速度快；fsync：将数据持久化到磁盘的操作，这时才占磁盘 IOPS。write和fsync的时机是根据 sync_binlog 参数控制：0——>只write，不fsync，默认值。机器宕机，page cache里面的 binglog 会丢失。为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。1——>每次fsync。>1——>每次事务都write，等累积到N后才fsync。机器宕机，会丢失最近N个事务的binlog日志。在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。（可以提高 IO 性能，但是若发生异常，日志会丢失）怎样判断 binlog 是否完整？statement 格式的 binlog，最后会有 COMMITrow 格式末尾有 XID eventredo log 与 binlog 的区别redo log 是 InnoDB 引擎特有的，只记录该引擎中表的修改记录。binlog 是 MySQL 的 Server 层实现的，会记录所有引擎对数据库的修改。redo log 是物理日志，记录的是在具体某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑。redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的，binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。binlog 日志没有 crash-safe 的能力，只能用于归档，而 redo log 有 crash-safe 能力；redo log 在事务执行过程中可以不断写入（刷盘设置为1，后台线程1s执行一次或者 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候），而 binlog 只有在提交事务时才写入文件缓存系统；MySQL 的架构应用层：负责和客户端，响应客户端请求，建立连接，返回数据。逻辑层：包括 SQK 接口，解析器，优化器，Cache 与 buffer。数据库引擎层：有常见的 MyISAM,InnoDB 等等。物理层：负责文件存储，日志等等接下来以一条 sql 查询语句执行过程介绍各个部分功能。客户端执行一条 sql：1、首先由连接器进行身份验证，权限管理2、若开启了缓存，会检查缓存是否有该 sql 对应结果（缓存存储形式 key-vlaue，key 是执行的 sql，value 是对应的值）若开启缓存又有该 sql 的映射，将结果直接返回；（查询缓存：查询后的结果存储位置，MySQL8.0版本以后已经取消，因为查询缓存失效太频繁，得不偿失。）3、分析器进行词法语法分析4、优化器会生成执行计划、选择索引等操作，选取最优执行方案5、然后来到执行器，打开表调用存储引擎接口，逐行判断是否满足查询条件，满足放到结果集，最终返回给客户端；若用到索引，筛选行也会根据索引筛选。1.6.4RelayLogrelaylog 是中继日志，在主从同步的时候使用到，它是一个中介临时的日志文件，用于存储从 master 节点同步过来的 binlog 日志内容。master 主节点的 binlog 传到 slave 从节点后，被写入 relay log 里，从节点的 slave sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。1.7优化1、先设置慢查询（my.ini 或数据库命令）方式一：修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间（超过 2 秒就是慢查询），以及慢查询 log 日志记录（ slow_query_log）[mysqlld]//定义查过多少秒的查询算是慢查询，我这里定义的是 2 秒long_query_time=2#5.8、5.1 等版本配置如下选项log-slow-queries="mysql_slow_query.log"#5.5 及以上版本配置如下选项slow-query-log=Onslow_query_log_file="mysql_slow_query. log"1/记录下没有使用索引的 querylog-query-not-using-indexestpspb16glos dndnorte/t方式二：通过 MySQL 数据库开启慢查询:mysql>set global slow_query_log=ONmysql>set global long_query_time = 3600;mysql>set global log_querise_not_using_indexes=ON;2、分析慢查询日志3、定位低效率 sql（show processlist）4、explain 分析执行计划（是否索引失效，用到索引没，用了哪些）5、优化（索引+sql 语句+数据库结构优化+优化器优化+架构优化）索引1、尽量覆盖索引，5.6 支持索引下推2、组合索引符合最左匹配原则3、避免索引失效4、再写多读少的场景下，可以选择普通索引而不要唯一索引更新时，普通索引可以使用 change buffer 进行优化，减少磁盘 IO,将更新操作记录到 change bufer，等查询来了将数据读到内存再进行修改.5、索引建立原则（一般建在 where 和 order by，基数要大，区分度要高，不要过度索引，外键建索引）sql 语句1、分页查询优化该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询。select * from tb_sku where id>20000 limit 10;2、优化 insert 语句多条插入语句写成一条在事务中插数据数据有序插入（主键索引）数据库结构优化1、将字段多的表分解成多个表有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。2、对于经常联合查询的表，可以考虑建立中间表优化器优化1、优化器使用 MRR原理：MRR【Multi-Range Read】将 ID 或键值读到 buffer 排序，通过把「随机磁盘读」，转化为「顺序磁盘读」，减少磁盘 IO，从而提高了索引查询的性能。mysql >set optimizer_switch=mrr=on;explain 查看 Extra 多了一个 MRRexplainselect*from stu where age between 10 and 20;对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。磁盘预读：请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取（局部性原理）索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大https://zhuanlan.zhihu.com/p/148680235架构优化读/写分离（主库写，从库读）慢查询参数：long_query_time 默认值为 10s。long_query_time 属于 dynamic 类型的参数。set global long_query_time=xx；show variables like %slow%;基数：指 MySql 表中某一列的不同值的数量。如果这一列是唯一索引，则基数==行数；如果这一列是性别，枚举值只有男女，那它的基数就是 2。Cardinality 越高，列就越有成为索引的价值。MySQL 执行计划也会基于 Cardinality 选择索引。InnoDB 更新基数的时机：通过 innodb_stats_auto_recalc 参数控制 MySQL 是否主动重新计算这些持久性的信息。默认为 1 表示 true、0 表示 false。默认情况下当表中的行变化超过 10%时，重新计算基数信息。基数的估算：基数并不会实时更新！它是通过随机采样数据页的方式统计出来的一个估算值。而且随机采样的页数可以通过参数：innodb_stats_persistent_sample_pages 设置，默认值是 20。基数的持久化机制：通过参数 innodb_stats_persistent 控制是否持久化基数，默认为 off。当然你可以为一个单独的表设置 STATS_PERSISTENT=1 那么它的 innodb_stats_persistent 将自动被启用。开启它的好处是：重启 MySQL 不会再重复计算这个值，加快重启速度。主动更新基数的方式：执行 analyze table tableName;会触发 InnoDB 更新基数1.8 常见问题1.8.1char 和 varchar(MySql)1.区别：char 存储不可变长度的字符串，当字符集编码不同时，能存储的字节数是会变化的；而 varchar 可以存储可变长度的字符串。例如：插入时：char(5)表示可以存储 5 个字符而不是 5 个字节，它占用的存储空间一直是 5 个字符大小。可以往 a char(5)的列中插入"abc"3 个字符，在 MySql 底层会将"abc"后面追加两个空格字符"abc "。检索时：MySql 会自动做一次 trim()，返回"abc"。char(5)表示2.create table t(a char(5)) charset=utf8 engine=innodb;insert into t select "12345";insert into t select "青天白日梦";是否成功？utf8 中，每个数字占用一个 byte，每个中文占用 3 个 byte。因此，当 charset 为 utf8 时，char(5)这一列可以存储的字节范围是[5*1,5*3]。上述插入语句执行成功。3.create table t1(a varchar(2)) charset=utf8 engine=innodb;insert into t1 select "abcd";是否成功？varchar 的期望长度是 2，但是插入的字符串长度为 4，这时插入语句能否执行成功取决于 sql mode。通过 select @@sql_mode\G 查询，当值中包含 STRICT_TRANS_TABLES 时表示开启了严格模式。以上插入语句执行会报错。当值中包含 ONLY_FULL_GROUP_BY 时表示关闭了严格模式。以上插入语句执行会成功，而且 MySql 会将超出的字符砍掉，数据库中保存了"ab"。1.8.2MySql 各种锁MDL(Metadata Lock 元数据锁)：通常不需要显示的使用，当我们对表进行 CRUD 操作时 MySql 会自动给表加元数据锁，并且这把锁会和所有企图改变表结构的 SQL 互斥。元数据锁的作用：当有用户对表执行 DML 相关操作时，其它线程不能改变表结构（想改也是可以的，的等排在它前面的 DML 全部执行完）。反之，当有线程在更改表结构时，其它线程需要执行的 DML 也会被阻塞。表级别的锁：a.表级共享锁 lock tables t read;b.表级独占锁 lock tables t write;c.释放表锁 unlock table;d.其它锁全表的方式begin;（#首先手动开启事务）select * from t lock in share mode;（#检索时加上共享读锁）这时其它线程如果执行 insert 会被阻塞。Record Lock（行锁也叫记录锁）：每次都会去锁住具体的索引记录。如果表没有索引，连主键索引也不存在，MySql 会为表生成一个隐式的主键索引。行锁添加操作：begin;update t set name=xxx where id=1;commit;此时会对 id=1 的行加锁。Gap lock（间隙锁）：也是行锁的一种。它会锁定的是一个间隙范围，而不会锁住某条记录。其目的是为了防止同一事物的两次当前读出现幻读的情况。Next-key-lock（）：行锁+gap 锁。参考： https://www.cnblogs.com/ZhuChangwu/p/15079210.htmlselect...for update;加锁情况1.如果查询条件用了索引/主键，则会加行锁；2.如果是普通自动无索引/主键，则会加表锁。1.8.3B+树存储数据InnoDB的页结构在InnoDB中，索引默认使用的数据结构为B+树，而B+树里的每个节点都是一个页，默认的页大小为16KB。非叶子节点存的是索引值以及页的偏移量，而叶子节点上存放的则是完整的每行记录非叶子节点能存多少数据页默认16KBFile Header、Page Header等一共占102个字节Infimum + Supremum分别占13个字节记录头占5个字节id占为int，占4个字节页目录的偏移量占4个字节非叶子节点能存放的索引记录= (页大小 - File Header - Page Header - ...) / ( 主键 + 页偏移量 + 下一条记录的偏移量)= （16KB - 128B) / (5B + 4B + 4B)= 16256 / 13= 1250 条叶子节点能存多少数据变长列表占1个字节null标志位忽略记录头占5个字节id占为int，占4个字节name为VARCHAR，编码为UTF8，为了好算，所有行记录我都只用两个中文，那就是 2 * 3B = 6个字节事务ID列占6个字节回滚指针列占7个字节叶子节点能存放的数据记录= (页大小 - File Header - Page Header - ...) / ( 主键 + 字段 + 下一条记录的偏移量)= （16KB - 128B) / (1B + 5B + 4B + 6B + 6B + 7B)= 16256 / 29= 560 条高为3的B+树能存多少行数据记录根节点能放1250条索引记录第二层能放1250 * 1250 = 1,562,500条索引记录叶子节点 1250 * 1250 * 560 = 875,000,000条数据记录，八亿多条数据B树和B+ 树B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。1.8.4drop、delete、truncate区别（1）用法不同drop(丢弃数据): drop table 表名 ，直接将表结构都删除掉，在删除表的时候使用。truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。（2）属于不同的数据库语言truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segment 中，事务提交之后才生效。（3）执行速度不同delete命令执行的时候会产生数据库的binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。truncate命令执行的时候不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。drop命令会把表占用的空间全部释放掉。一般来说：drop > truncate > delete1.9与oracle的区别1、本质Oracle数据库是一个对象关系数据库管理系统（收费）重量级数据库。mysql是一个开源的关系数据库管理系统（免费）轻量级数据库。瑞典公司开发后被sun公司收购，sun又被oracle公司收购。2、事务mysql在innodb存储引擎的行级锁的情况下才可支持事务，而Oracle则完全支持事务；mysql默认是自动提交，而Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮；mysql是read commited的隔离级别，而Oracle是repeatable read的隔离级别，同时二者都支持serializable串行化事务隔离级别，可以实现最高级别的读一致性。每个session提交后其他session才能看到提交的更改。Oracle通过在undo表空间中构造多版本数据块来实现读一致性，每个session查询时，如果对应的数据块发生变化，Oracle会在undo表空间中为这个session构造它查询时的旧的数据块。MySQL没有类似Oracle的构造多版本数据块的机制，只支持read commited的隔离级别。一个session读取数据时，其他session不能更改数据，但可以在表最后插入数据。session更新数据时，要加上排它锁，其他session无法访问数据。3、数据持久性mysql默认提交sql语句，但如果更新过程中出现db或主机重启的问题，也许会丢失数据；oracle把提交的sql操作先写入了在线联机日志文件中，保持到了硬盘上，可以随时恢复。4、逻辑备份oracle逻辑备份时不锁定数据，且备份的数据是一致的。mysql逻辑备份时要锁定数据，才能保证备份的数据是一致的，影响业务正常的dml使用。5、性能诊断及管理工具mysql的诊断调优方法较少，主要有慢查询日志。管理工具较少。Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等。管理工具较多。6、锁级别mysql以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁。oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以oracle对并发性的支持要好很多。7、对象名称Oracle对所有对象名称都不区分大小写；mysql某些对象名称（如数据库和表）区分大小写（取决于底层操作系统）。8、临时表mysql中，临时表是仅对当前用户会话可见的数据库对象，并且一旦会话结束，这些表将自动删除。Oracle中临时表一旦创建就会存在，直到它们被显式删除，并且对具有适当权限的所有会话都可见。但是，临时表中的数据仅对将数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。9、语法区别①、表字段类型mysql的字符类型，如CHAR和VARCHAR的长度小于65535字节。Oracle支持4种字体类型：CHAR、NCHAR、NVARCHAR2和VARCHAR2。CHAR和NCHAR的最大长度为2000字节，NVARCHAR2和VARCHAR2最大长度为4000字节。②、列默认值mysql的非空字段也有空的内容，Oracle里定义了非空字段就不容许有空的内容。按mysql的NOT NULL来定义Oracle表结构，导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。③、引号处理mysql里可以用双引号包起字符串，Oracle里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。④、主键MySQL一般使用自动增长类型，在创建表的时候只要指定表的主键为auto increment。Oracle没有自动增长，主键一般使用序列，序列号的名称.NEXTVAL。⑤、分页处理mysql是直接在SQL语句中使用limit就可以实现分页。oracle则是需要用到伪劣ROWNUM和嵌套查询。⑥、分组函数Mysql中group by可以使用别名，Oracle 中不可以。Oracle中出现在select列表中的字段或者出现在order by后面的字段，如果不是包含在分组函数中，那么该字段必须同时在group by子句中出现。Mysql没有此限制。⑦、模糊查询MYSQL里用字段名 like %字符串%,ORACLE里也可以用字段名 like %字符串% 但这种方法不能使用索引，速度不快，用字符串比较函数 instr(字段名,字符串)>0 会得到更精确的查找结果。⑧、其它函数1、时间格式化及时间计算函数mysqloracle时间转换为字符串型时间date_format(NOW(),’%Y-%m-%d’)to_char(sysdate, ‘YYYY-MM-DD’)字符串型时间转换为时间类型date_format(NOW(),’%Y-%m-%d’)to_date(‘2020-12-01’, ‘YYYY-MM-DD’)2、空值返0mysqloracleIFNULL、COALESCEnvl、nvl2说明：nvl：基本语法为nvl(E1,E2)，意思是E1为null就返回E2，不为null就返回E1。nvl2：nvl2函数的是nvl函数的拓展，基本语法为nvl2(E1,E2,E3)，意思是E1为null，就返回E3，不为null就返回E2。IFNULL：基本语法为IFNULL(E1,E2)，假如E1不为NULL，则返回值为E1，否则为E2。COALESCE：COALESCE(value,…)是一个可变参函数，可以使用多个参数。作用：接受多个参数，返回第一个不为NULL的参数，如果所有参数都为NULL，此函数返回NULL；当它使用2个参数时，和IFNULL函数作用相同。3、长度函数mysqloraclechar_length(str)length(str)4、条件函数mysqloracleIFNULLnvlIF(expr1,expr2,expr3)nvl2IF(value=val1, val2, val3)、case whenDECODE说明：IF(expr1,expr2,expr3)：如果expr1不为null，则返回expr2，否则返回expr3。IF(value=val1, val2, val3)：如果value等于val1，则返回val2，否则返回val3。case when then else end：如果value等于if1，则返回val1，如果value等于if2，则返回value2…如果value等于ifn，则返回valn，否则返回val。5、trunc()函数MySQLOracletruncate(123.123,2)TRUNC(123.123,2)6、字符串整型转换函数MySQLOracle整型转字符串CAST(123 AS CHAR(3))to_char(123)字符串转整型cast(‘123’ as SIGNED)to_number(‘123’)7、字符串连接符MySQLOracleconcat(studentname, ‘=’, studentno)studentname||’=’||studentno8、空数据排序MySQLselect * from USER A order by IF(ISNULL(A.REMARK),1,0),A.REMARK descselect * from USER A order by IF(ISNULL(A.REMARK),0,1),A.REMARK descOracleSELECT * FROM USER A ORDER BY A.REMARK DESC NULLS FIRSTSELECT * FROM USER A ORDER BY A.REMARK DESC NULLS LAST9、表(左/右)关联Oracle左连接、右连接可以使用(+)来实现，MySQL只能使用left join ,right join等关键字。10、merge intoMySQL不支持（merge into），但提供的replace into 和on duplicate key update可实现相似的功能。

MySQL性能优化实战：从慢查询到极致性能 前言上周我们优化了一条执行时间从2.5秒降到50ms的复杂查询，用户体验立竿见影。MySQL性能优化看似神秘，其实有章可循。这篇文章将分享我在电商系统中总结的实战经验。一、性能诊断三板斧1.1 开启慢查询日志sqlSET GLOBAL slow_query_log = ON;SET GLOBAL long_query_time = 0.5;SET GLOBAL log_queries_not_using_indexes = ON;1.2 分析慢查询bash# 安装mysqldumpslowmysqldumpslow -s c -t 10 /var/log/mysql/slow.log# 输出：# Count: 150 Lock_time: 0.15s Rows_sent: 5 Rows_examined: 50000# SELECT * FROM orders WHERE status=pending1.3 EXPLAIN执行计划分析sqlEXPLAIN SELECT * FROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.created_date > 2024-01-01AND o.status = completed;-- 关键字段解读：-- type: index(索引扫描) > range(范围扫描) > ref(关联扫描) > ALL(全表扫描)-- key: 实际使用的索引-- rows: 扫描的行数-- filtered: 筛选比例二、索引优化策略2.1 创建高效索引sql-- ❌ 低效：字段顺序不当CREATE INDEX idx_order ON orders(status, customer_id, created_date);-- ✅ 优化：按查询频率排序CREATE INDEX idx_order ON orders(customer_id, created_date, status);-- ✅ 更优：覆盖索引（避免回表）CREATE INDEX idx_order_cover ON orders(customer_id, created_date, status, amount, pay_method);-- 联合索引最左匹配原则SELECT * FROM orders WHERE customer_id = 1 AND created_date > 2024-01-01;-- ✓ 使用索引SELECT * FROM orders WHERE created_date > 2024-01-01 AND customer_id = 1;-- ✗ 不走索引（没有customer_id作为前缀）2.2 索引维护和监控sql-- 查看索引使用情况SELECT object_name, count_read, count_writeFROM performance_schema.table_io_waits_summary_by_index_usageWHERE object_schema != mysqlORDER BY count_read DESC;-- 找出冗余索引SELECT a.object_name, a.index_name AS 冗余索引, b.index_name AS 关键索引FROM performance_schema.table_io_waits_summary_by_index_usage aJOIN performance_schema.table_io_waits_summary_by_index_usage bON a.object_name = b.object_nameAND a.count_read = 0AND b.count_read > 0;-- 删除冗余索引DROP INDEX idx_old ON orders;-- 定期重建索引OPTIMIZE TABLE orders;三、查询优化技巧3.1 避免常见陷阱sql-- ❌ 错误1：不走索引的LIKESELECT * FROM users WHERE name LIKE %张%;-- ✅ 正确：前缀匹配SELECT * FROM users WHERE name LIKE 张%;-- ❌ 错误2：隐式类型转换SELECT * FROM orders WHERE order_id = 12345;-- 字符串转数字，不走索引-- ✅ 正确：类型匹配SELECT * FROM orders WHERE order_id = 12345;-- ❌ 错误3：函数包裹字段SELECT * FROM orders WHERE DATE(created_date) = 2024-01-15;-- ✅ 正确：范围查询SELECT * FROM ordersWHERE created_date >= 2024-01-15AND created_date < 2024-01-16;-- ❌ 错误4：OR导致索引失效SELECT * FROM ordersWHERE customer_id = 1 OR status = pending;-- ✅ 正确：使用UNIONSELECT * FROM orders WHERE customer_id = 1UNIONSELECT * FROM orders WHERE status = pending;3.2 JOIN优化sql-- ❌ 笛卡尔积（最常见的性能杀手）SELECT * FROM orders o, customers c;-- 如果orders 100万行，customers 10万行-- 结果：1000亿行！-- ✅ 使用显式JOIN和ON条件SELECT o.order_id, o.amount, c.customer_nameFROM orders oINNER JOIN customers c ON o.customer_id = c.idWHERE o.created_date > 2024-01-01;-- JOIN顺序优化（小表驱动大表）-- 方案1：使用STRAIGHT_JOIN指定顺序SELECT * FROM orders oSTRAIGHT_JOIN order_items oi ON o.id = oi.order_idWHERE o.status = completed;-- 方案2：让优化器自动选择-- MySQL 8.0+会自动优化，通常无需手动指定3.3 子查询vs JOINsql-- ❌ 子查询（可能产生临时表）SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = China);-- ✅ 使用JOIN（更高效）SELECT DISTINCT o.* FROM orders oINNER JOIN customers c ON o.customer_id = c.idWHERE c.country = China;-- EXISTS vs IN（大数据量时EXISTS更优）-- ❌ IN（需要扫描整个子查询结果）SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE vip_level > 3);-- ✅ EXISTS（可提前终止扫描）SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers cWHERE c.id = o.customer_id AND c.vip_level > 3);四、分区和分片策略4.1 表分区（单机优化）sql-- 创建分区表CREATE TABLE orders_partition (id BIGINT PRIMARY KEY AUTO_INCREMENT,customer_id INT NOT NULL,amount DECIMAL(10,2),created_date DATE NOT NULL,status VARCHAR(20)) PARTITION BY RANGE (YEAR(created_date)) (PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025),PARTITION p_future VALUES LESS THAN MAXVALUE);-- 查询时自动选择分区SELECT * FROM orders_partitionWHERE created_date > 2024-01-01;-- 只扫描p2024和p_future分区-- 分区维护-- 删除旧分区ALTER TABLE orders_partition DROP PARTITION p2022;-- 添加新分区ALTER TABLE orders_partitionADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));4.2 水平分片（分布式扩展）python# 分片键选择# ✅ 好的分片键：# - customer_id（数据均衡分布）# - user_id（业务含义清晰）# ❌ 不好的分片键：# - status（数据分布不均）# - region（容易出现热点）class ShardingManager:def __init__(self, db_hosts):self.db_hosts = db_hosts # 5个数据库节点self.shard_count = len(db_hosts)def get_shard_index(self, customer_id):"""一致性哈希或简单取模"""return customer_id % self.shard_countdef execute_query(self, customer_id, sql):"""根据customer_id路由到对应分片"""shard_index = self.get_shard_index(customer_id)db_host = self.db_hosts[shard_index]return db_host.execute(sql)# 使用sharding = ShardingManager([db1.example.com,db2.example.com,db3.example.com,db4.example.com,db5.example.com])result = sharding.execute_query(customer_id=12345,sql=SELECT * FROM orders WHERE customer_id = 12345)``*五、缓存策略5.1 查询缓存sql-- MySQL查询缓存（MySQL 5.7已弃用，8.0移除）-- 改用应用层缓存：Redis-- 缓存预热查询SELECT customer_id, COUNT(*) as order_countFROM ordersGROUP BY customer_id;5.2 Redis缓存实现pythonimport redisimport jsonimport timeclass OrderCache:def __init__(self):self.redis_client = redis.Redis(host=localhost, port=6379)self.ttl = 3600 # 1小时def get_customer_orders(self, customer_id):"""先查缓存，再查数据库"""# 1. 查询Redis缓存cache_key = fcustomer:{customer_id}:orderscached = self.redis_client.get(cache_key)if cached:return json.loads(cached)# 2. 缓存未命中，查询数据库orders = self.db.query(SELECT * FROM orders WHERE customer_id = %s,[customer_id])# 3. 写入缓存self.redis_client.setex(cache_key,self.ttl,json.dumps(orders))return ordersdef invalidate_cache(self, customer_id):"""订单更新时删除缓存"""cache_key = fcustomer:{customer_id}:ordersself.redis_client.delete(cache_key)# 使用cache = OrderCache()orders = cache.get_customer_orders(12345)# 下单后更新cache.invalidate_cache(12345)六、批量操作优化6.1 批量插入sql-- ❌ 逐条插入（1000次网络往返，极其低效）INSERT INTO orders (customer_id, amount) VALUES (1, 100);INSERT INTO orders (customer_id, amount) VALUES (2, 200);-- ...重复1000次-- ✅ 批量插入（1次网络往返）INSERT INTO orders (customer_id, amount) VALUES(1, 100),(2, 200),(3, 300),(4, 400),(5, 500);-- 更优：load data（最快，20倍性能提升）LOAD DATA INFILE /tmp/orders.csvINTO TABLE ordersFIELDS TERMINATED BY ,LINES TERMINATED BY \n(customer_id, amount);6.2 批量更新sql-- ❌ 逐行更新UPDATE orders SET status = shipped WHERE id = 1;UPDATE orders SET status = shipped WHERE id = 2;-- ✅ 批量更新UPDATE orders SET status = shippedWHERE id IN (1, 2, 3, 4, 5);-- 使用CASE WHEN更新不同值UPDATE orders SETstatus = CASEWHEN amount > 1000 THEN vipWHEN amount > 100 THEN normalELSE regularENDWHERE created_date > 2024-01-01;七、监控和告警7.1 关键性能指标sql-- QPS（每秒查询数）SHOW STATUS LIKE Questions;-- TPS（每秒事务数）SHOW STATUS LIKE Innodb_trx_*;-- 连接数SHOW STATUS LIKE Threads_connected;SHOW STATUS LIKE Threads_running;-- 行锁等待SHOW ENGINE INNODB STATUS;7.2 Prometheus监控配置yaml# prometheus.ymlscrape_configs:- job_name: mysqlstatic_configs:- targets: [localhost:9104]# MySQL告警规则groups:- name: mysqlrules:- alert: SlowQueryexpr: rate(mysql_global_status_slow_queries[5m]) > 10for: 5m- alert: HighConnectionsexpr: mysql_global_status_threads_connected > 80for: 5m- alert: LongRunningTransactionexpr: mysql_global_status_innodb_trx_rseg_history_len > 1000000八、团队协作与技术交流我们的数据库优化小组由5位DBA分布在不同城市，每周进行MySQL性能调优的技术分享。由于成员背景多元，讨论中常出现专业术语理解不一致的情况。我们现在使用同言翻译（Transync AI）进行实时翻译和会议记录，确保每位工程师都能准确理解查询优化方案和架构设计意见。九、性能优化检查清单□ 开启慢查询日志分析□ 为频繁查询的字段建立索引□ 检查索引是否真正被使用□ 优化JOIN和子查询□ 避免大表全表扫描□ 使用合适的数据类型□ 批量操作而非逐行操作□ 合理使用缓存□ 定期维护表和索引□ 配置完善的监控告警十、常见误区❌ 加索引能解决一切问题✅ 索引是工具，需要配合正确的查询❌ 索引越多越好✅ 冗余索引反而拖累写入性能❌ 所有查询都需要优化✅ 先优化慢查询和高频查询❌ 关系型数据库天生低效✅ 合理设计能支撑日均10亿级别查询❌ 立即迁移到NoSQL✅ 先把关系数据库用到极致总结MySQL性能优化的核心是：1. 诊断 - 用慢查询日志找到问题 2. 分析 - 用EXPLAIN理解执行计划 3. 优化 - 索引、查询、缓存三管齐下 4. 监控 - 持续跟踪关键指标从简单的索引优化到复杂的分片方案，都遵循这个原则。掌握这些技巧，你可以让数据库性能提升10倍！参考资源：MySQL官方文档：https://dev.mysql.com/doc/高性能MySQL：《High Performance MySQL》在线EXPLAIN分析：https://www.explainextended.com点赞、收藏、关注，欢迎在评论区分享你的优化案例！