group by 的简单说明:  group by 一般和聚合函数一起使用才有意义,比如 count sum avg等使用group by的两个要素:   (1) 出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的.   (2) 要筛选结果 可以先使用where 再用group by 或者先用group by 再用having下面看下 group by多个条件的分析:---------- 测试数据初始化    begin --------------------在SQL查询器输入以下语句create table test1(a varchar2(20),b varchar2(20),c varchar2(20));insert into test1 values(1,'a','甲');insert into test1 values(1,'a','甲');insert into test1 values(1,'a','甲');insert into test1 values(1,'a','甲');insert into test1 values(1,'a','乙');insert into test1 values(1,'b','乙');insert into test1 values(1,'b','乙');insert into test1 values(1,'b','乙');---------- 测试数据初始化    end--------------------第一次查询select * from test1; 结果如下图:结果中       按照b列来分:则是 5个a 3个b.    按照c列来分:则是 4个甲 4个乙.第二次查询 按照 b列来分组 代码如下select count(a),b from test1 group by b;第三次 按照 c列来分组 代码如下select count(a),c from test1 group by c;第四次 按照 b c两个条件来分组select count(a),b,c from test1 group by b,c;可以看出 group by 两个条件的工作过程:先对第一个条件b列的值 进行分组,分为 第一组:1-5, 第二组6-8,然后又对已经存在的两个分组用条件二 c列的值进行分组,发现第一组又可以分为两组 1-4,5第五次 按照 c b 顺序分组select count(a),b,c from test1 group by c,b;原文链接：https://www.cnblogs.com/happyWolf666/p/8196147.html

以下为您演示MySQL常用的日期分组统计方法：  按月统计（一） select date_format(create_time, '%Y-%m') mont, count(*) coun from t_content group by date_format(create_time, '%Y-%m'); 按天统计（二） select date_format(create_time, '%Y-%m-%d') dat, count(*) coun from t_content group by date_format(create_time, '%Y-%m-%d'); 按天统计（三） select from_unixtime(create_time / 1000, '%Y-%m-%d') dat, count(*) coun from t_content group by from_unixtime(create_time / 1000, '%Y-%m-%d') 其他 格式转换 select from_unixtime(create_time / 1000, '%Y-%m-%d %H:%i:%S') create_time from t_content ———————————————— 原文链接：https://blog.csdn.net/lpw_cn/article/details/89487801 

 图片较多且部分无法显示  查看原图请到原文  原文链接在本文章末尾一、聚合查询 聚合查询是针对行与行之间的计算，常见的聚合函数有:  函数    作用 COUNT(expr)    查询数据的数量 SUM(expr)    查询数据的总和 AVG(expr)    查询数据的平均值 MAX(expr)    查询数据的最大值 MIN(expr)    查询数据的最小值 create table stu(id int primary key,name varchar(50),math int,english int); insert into stu values (001,"张三",80,90), (002,"李四",75,80), (003,"王五",85,90), (004,"小王",90,80), (005,"小孙",null,null); count函数: 顾名思义，count函数就是用来统计我们表的行数的。  但注意的是，我们再给count函数传参数时，这一列不能有null值。  我们发现当传入math参数时，因为math有一行的数据是null，count函数在统计时，自动省略这一行。  当然我们还可以传入全列，count传入全列时，只要这一列有不为null的值就会被统计上，但时间会相对增大，一般建议传入主键或者not null的列。 SUM函数: 用来计算某一列数值的综合，null自动省略。  也可以进行表达式进行聚合计算。  AVG函数: avg函数对某一列求平均值，我们可以发现计算平均值是，null既不计入分子也不计入分母。  MAX函数: 求某一列的最大值  MIN函数: 求某一列的最小值 二、分组查询 有时候单纯使用聚合查询没啥意思，我们需要先分组在进行聚合计算。  create table stu(id int,name varchar(20),class varchar(20),math int,english int); insert into stu values(001,"张三","计算机1班",80,95),                       (002,"李四","计算机1班",90,76),                       (003,"王五","计算机2班",86,77),                       (004,"小王","计算机2班",92,86),                       (005,"张良","计算机2班",86,96);  我们来计算平均数学成绩  这样的平均成绩没啥意思，我们来求一下每个班的数学平均成绩  select class,avg(math) from stu group by class; 我们在来求一下，每班的数学最高分。  select name,class,max(math) from stu group by class; 分组查询，也可以指定条件 1.分组之前指定条件,先筛选在分组，WHERE 2.分组之后指定条件,先分组在筛选, HAVING 3.分组之前和分组之后都指定条件，WHERE HAVING都使用。  分组之前: 查询每个班的平均数学成绩，但是去掉小王的成绩  select class,avg(math) from stu where name != '小王' group by class; 分组之后: 查询每个班级的平均数学成绩，但去除平均成绩为85的班级。  select class,avg(math) from stu group by class having avg(math) != 85; 分组之前和分组之后都指定条件： 查询班级的平均成绩，去掉小王的成绩，并且去除计算机1班的平均数学成绩  select class,avg(math) from stu where name != '小王' group by class having class != '计算机1班'; 三、联合查询 当我们多张表建立联系时，我们就可以进行联合查询，多表查询就是对多张表取笛卡尔积。  笛卡尔的结果列数是两张表列数之和，行数是两张表的行数之积.  create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));  create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,         classes_id int);  create table course(id int primary key auto_increment, name varchar(20));  create table score(score decimal(3, 1), student_id int, course_id int); select * from student,classes 大家轻易可以发现，笛卡尔积里的结果很多都是无效的数据，因此我们需要将一部分无意义的数据给去掉。  我们通过这两个变量来建立关系，多表查询时，我们访问表中的变量时用表名点(.)变量表示。   select * from student,classes where classes.id = student.classes_id; 当我们加上条件(这个条件我们成为连接条件)之后，剩下的都是“正确"的数据. 我们也可以指定列查询。   select student.id,student.name,student.classes_id,classes.name from student,classes where classes.id = student.classes_id; 内连接 我们现在构造了四张表出来，student(学生表),classes(班级表),course(课程表),score(分数表). 我们查询一下白素贞的班级: 我们在进行联合查询的时候，不必急于求成，一步一步进行。  -- 1.先计算笛卡尔积 select * from student,classes; -- 2.引入连接条件 select * from student,classes where classes.id = student.classes_id; -- 3.引入名字为白素贞的条件 select * from student,classes where classes.id = student.classes_id and student.name = '白素贞'; -- 4.只保留必要的列  select student.name,classes.name from student,classes where classes.id = student.classes_id and student.name = ' 白素贞'; 联合查询也可以用join来完成:   select student.name,classes.name from student join classes on classes.id = student.classes_id and student.name = '白素贞'; 内连接还可以使用inner join完成。   select student.name,classes.name from student inner join classes on classes.id = student.classes_id and student.name = '白素贞'; 我们还可以进行多张表进行联合查询。 select * from student,score,course where student.id = score.student_id and course.id = score.course_id; 我们可以省略部分列，使用别名，join来查询  select student.name as 学生姓名,course.name as 课程名称,score.score as 分数 from student join score on student.id = score.student_id join course on score.course_id = course.id; 外连接 内连接和外连接在一些情况下，查询的结果没有差异(当两个表一一对应时),如果没有一一对应那么就有区别了。 我们可以用这两张表，建立一下内外连接看一下效果。  -- 内连接 select * from student join score on student.id = score.student_id; -- 外连接  select * from student left join score on student.id = score.student_id; 我们可以发现内外连接查询的结果是一样的。因为我们两个表的内容是一一对应的。  这时我们发现student表id为6的数据在score无对应  这时我们发现，内外查询的结果就有所差异了。 外连接: 当进行外连接时，如果是左连接，会把左表所有的数据查询到总结果中，如果右表没有对应数据，就是用NULL补充（右连接同理）。  自连接 SQL中无法对行和行之间使用条件比较，当我们要进行行行运算时，我们可以使用自连接进行调整。 我们想查询那个同学的java成绩比英文成绩高。  我们可以发现至今将表明写两遍，会报一个表名不唯一的错误。正确的做法是为表名起别名。  这里我们是自己和自己比，所以我们加上student_id相等的条件 然后对score1的科目进行限制为java，score2的科目限制为英文  select * from score as score1,score as score2 where score1.student_id = score2.student_id and score1.course_id = 1 and score2.course_id = 6; 我们发现只有两名学生即选择了java，又选择了英文。 我们再加上java比英文高的条件。  select * from score as score1,score as score2 where score1.student_id = score2.student_id and score1.course_id = 1 and score2.course_id = 6 and score1.score > score2.score; 我们发现没有java比英文高的数据  所以我们查出来的是空集合。  四、合并查询 在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。 -- union  select * from course where id < 4 union select * from course where name != 'java'; -- union all  select * from course where id < 4 union all select * from course where name != 'java'; 这里我们可以发现union可以去掉重复数据，而union all不去重。 大家需要注意or 与 union的区别，or的查询只能针对同一个表,而union可以来自于多张表，只要查询的结果能够对应列即可。  五、子查询 子查询最本质就是套娃，将多个SQL组合起来。 实际开发中，子查询的使用要小心(子查询会构造出来一些非常复杂并且不好理解的SQL，对于代码的可读性，执行效率都有可能造成很大的影响。 查询许仙的同班同学  正常思路，先去查询许仙的班级号，再去按照班级号去查那些同学和他一个班  select classes_id from student where name = '许仙'; select name from student where classes_id = 1 and name != '许仙'; 子查询:  select name from student where classes_id = (select classes_id from student where name = '许仙') and name != '许 仙';   子查询返回一条记录，才可以写等号  查询java或者英文课的成绩信息 先查询java或者英文课的课程号，再根据课程号去查询课程分数   select id from course where name = 'java' or name = '英文'; 1  select * from score where course_id = 1 or course_id = 6;  子查询:   select * from score where course_id in (select id from course where name = 'java' or name = '英文');  EXISTS关键字: 可读性比较差，效率也大大的比in低，适用于解决特殊情况  还是更推荐大家分步查询。 ———————————————— 版权声明：本文为CSDN博主「熬夜磕代码丶」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/buhuisuanfa/article/details/127907559 

 http://blog.sina.com.cn/s/blog_68431a3b0100y04v.html 方法1： truncate table 你的表名 //这样不但将数据全部删除，而且重新定位自增的字段 方法2： delete from 你的表名 dbcc checkident(你的表名,reseed,0) //重新定位自增的字段，让它从1开始 方法3： 如果你要保存你的数据，介绍你第三种方法，by QINYI 用phpmyadmin导出数据库，你在里面会有发现哦 编辑sql文件，将其中的自增下一个id号改好，再导入。 -------------------------  truncate命令是会把自增的字段还原为从1开始的,或者你试试把table_a清空，然后取消自增，保存，再加回自增，这也是自增段还原为1的方法。  -------------------------  MySql数据库唯一编号字段(自动编号字段)  在数据库应用，我们经常要用到唯一编号，以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性  来自动生成。MySQL支持多种数据表，每种数据表的自增属性都有差异，这里将介绍各种数据表里的数据  列自增属性。  ISAM表  如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开  始，并1为基数递增。  把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。  当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。  当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果  插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；情况  二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递  增。也就是说，可以跳过一些编号。  如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。  如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号  从该值开始递增。  如果用replace命令基于AUTO_INCREMENT数据列里的值来修改数据表里的现有记录，即AUTO_INCREMENT数  据列出现在了replace命令的where子句里，相应的AUTO_INCREMENT值将不会发生变化。但如果replace命  令是通过其它的PRIMARY KEY OR UNIQUE索引来修改现有记录的(即AUTO_INCREMENT数据列没有出现在  replace命令的where子句中)，相应的AUTO_INCREMENT值--如果设置其为NULL(如没有对它赋值)的话--就  会发生变化。  last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中  生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。  其它数据表的自动编号机制都以ISAM表中的机制为基础。  MyISAM数据表  删除最大编号的记录后，该编号不可重用。  可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。  可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。  可使用复合索引在同一个数据表里创建多个相互独立的自增序列，具体做法是这样的：为数据表创建一个由多个数据列组成的PRIMARY KEY OR UNIQUE索引，并把AUTO_INCREMENT数据列包括在这个索引里作为它的最后一个数据列。这样，这个复合索引里，前面的那些数据列每构成一种独一无二的组合，最末尾的AUTO_INCREMENT数据列就会生成一个与该组合相对应的序列编号。  HEAP数据表  HEAP数据表从MySQL4.1开始才允许使用自增列。  自增值可通过CREATE TABLE语句的 AUTO_INCREMENT=n选项来设置。  可通过ALTER TABLE语句的AUTO_INCREMENT=n选项来修改自增始初值。  编号不可重用。  HEAP数据表不支持在一个数据表中使用复合索引来生成多个互不干扰的序列编号。  BDB数据表  不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。  可重用编号。  支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。  InnDB数据表  不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。  不可重用编号。  不支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。  在使用AUTO_INCREMENT时，应注意以下几点：  AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。  设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍。  AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复。  AUTO_INCREMENT数据列必须具备NOT NULL属性。  AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。  当进行全表删除时，AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时：  delete from table_name;  or  truncate table table_name  这是因为进行全表操作时，MySQL实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。如果想删除所有的数据行又想保留序列编号信息，可这样用一个带where的delete命令以  抑制MySQL的优化：  delete from table_name where 1;  这将迫使MySQL为每个删除的数据行都做一次条件表达式的求值操作。  强制MySQL不复用已经使用过的序列值的方法是：另外创建一个专门用来生成AUTO_INCREMENT序列的数据表，并做到永远不去删除该表的记录。当需要在主数据表里插入一条记录时，先在那个专门生成序号的  表中插入一个NULL值以产生一个编号，然后，在往主数据表里插入数据时，利用LAST_INSERT_ID()函数取得这个编号，并把它赋值给主表的存放序列的数据列。如：  insert into id set id = NULL;  insert into main set main_id = LAST_INSERT_ID();  可用alter命令给一个数据表增加一个具有AUTO_INCREMENT属性的数据列。MySQL会自动生成所有的编号。  要重新排列现有的序列编号，最简单的方法是先删除该列，再重建该，MySQL会重新生连续的编号序列。  在不用AUTO_INCREMENT的情况下生成序列，可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数的LAST_INSERT_ID(expr)去插入或修改一个数据列，紧接着又调用不带参数的LAST_INSERT_ID()函数，  则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作：  先创建一个只有一个数据行的数据表：  create table seq_table (id int unsigned not null);  insert into seq_table values (0);  接着用以下操作检索出序列号：  update seq_table set seq = LAST_INSERT_ID( seq + 1 );  select LAST_INSERT_ID();  通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。  该方法可用于计数器，在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK，UNLOCK表就可生成唯一的序列编号。不  会影响其它客户程序的正常表操作。  alter table table_name auto_increment=n;  注意n只能大于已有的auto_increment的整数值,小于的值无效.  show table status like 'table_name' 可以看到auto_increment这一列是表现有的值.步进值没法改变.只能通过下面提到last_inset_id()函数变通使用  在使用AUTO_INCREMENT时，应注意以下几点：  AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序  列的编号个可增加一倍。  AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复。  AUTO_INCREMENT数据列必须具备NOT NULL属性。  AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。  在不用AUTO_INCREMENT的情况下生成序列，可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数  的LAST_INSERT_ID(expr)去插入或修改一个数据列，紧接着又调用不带参数的LAST_INSERT_ID()函数，则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作：  先创建一个只有一个数据行的数据表：  create table seq_table (id int unsigned not null);  insert into seq_table values (0);  接着用以下操作检索出序列号：  update seq_table set seq = LAST_INSERT_ID( seq + 1 ); select LAST_INSERT_ID(); 通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。 该方法可用于计数器，在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK，UNLOCK表就可生成唯一的序列编号。不 会影响其它客户程序的正常表操作。 有两点需要加强注意: 1、只有一列的时候是不行的！ 2、自动编号必须作为主键才有效！ ———————————————— 原文链接：https://blog.csdn.net/weixin_39644611/article/details/113229282 

 日志 MySQL 的日志默认保存位置为 /usr/local/mysql/data  1日志类型与作用： 1.redo 重做日志：达到事务一致性（每次重启会重做） 作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，达到事务一致性  2.undo 回滚日志  作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交 隔离级别就是通过mvcc+undo实现  3.errorlog 错误日志 作用：Mysql本身启动，停止，运行期间发生的错误信息  slow query log 慢查询日志 作用：记录执行时间过长的sql，时间阈值（10s）可以配置，只记录执行成功 另一个作用：在于提醒优化  bin log 二进制日志 　　作用：用于主从复制，实现主从同步 记录的内容是：数据库中执行的sql语句  6.relay log 中继日志  作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放  general log 普通日志 　　作用：记录数据库的操作明细，默认关闭，开启后会降低数据库性能 mysql -u root -P show variables like 'general%';       #查看通用查询日志是否开启 show variables like 'log_bin%';       #查看二进制日志是否开启 show variables like '%slow%';         #查看慢查询日功能是否开启 show variables like 'long_query_time';    #查看慢查询时间设置 set global slow_query_log=ON;         #在数据库中设置开启慢查询的方法  PS: variables 表示变量 like 表示模糊查询   ##配置文件 vim /etc/my.cnf [mysqld] ##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启 log-error=/usr/local/mysql/data/mysql_error.log     #指定日志的保存位置和文件名  ##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的 general_log=ON general_log_file=/usr/local/mysql/data/mysql_general.log  ##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启 log-bin=mysql-bin       或 log_bin=mysql-bin  ##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的 s1ow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log long_query_time=5        #设置超过5秒执行的语句被记录，缺省时为10秒  ##复制段 log-error=/usr/local/mysql/data/mysql_error.log general_log=ON general_log_file=/usr/local/mysql/data/mysql_general.log log-bin=mysql-bin slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log long_query_time=5  systemctl restart mysqld  #xxx（字段） xxx% 以xxx为开头的字段 %xxx 以xxx为结尾的字段 %xxx% 只要出现xxx字段的都会显示出来 xxx   精准查询  #二进制日志开启后，重启mysql 会在目录中查看到二进制日志 cd /usr/local/mysql/data ls mysql-bin.000001        #开启二进制日志时会产生一个索引文件及一个索引列表  索引文件：记录更新语句 索引文件刷新方式： 1、重启mysql的时候会更新索引文件，用于记录新的更新语句 2、刷新二进制日志  mysql-bin.index： 二进制日志文件的索引  3.2 mysqldump 备份与恢复  完全备份一个或多个完整的库 (包括其中所有的表) mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql    #导出的就是数据库脚本文件 例: mysqldump -u root -p --databases kgc > /opt/kgc.sql        #备份一个kgc库 mysqldump -u root -p --databases mysql kgc > /opt/mysql-kgc.sql     #备份mysql与 kgc两个库  (2)、完全备份 MySQL 服务器中所有的库 mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql 例: mysqldump -u root -p --all-databases > /opt/all.sql  (3)、完全备份指定库中的部分表 mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql 例: mysqldump -u root -p [-d] kgc info1 info2 > /opt/kgc_info1.sql #使用“-d”选项，说明只保存数据库的表结构 #不使用“-d"选项，说明表数据也进行备份 #做为一个表结构模板  (4)查看备份文件 grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$   mysql 完全恢复 模拟删库 drop database 库名；  mysql -u root -p123123 < /backup/bbs.sql #恢复数据库操作  #恢复数据表 当备份文件中只包含表的 备份时，而不包含创建库时的语句，执行操作时必须指定库名，且目标库必须存在。  mysqldump -u root -p123123 kgc info > /backup/kgc_info.sql #只备份表  drop database kgc；   create database kgc；  mysql -u root -p123123 kgc < /backup/kgc_info.sql #######恢复表时需要指定对应的库########## 3.3增量备份与恢复 MySQL数据库增量恢复 1.一般恢复  将所有备份的二进制日志内容全部恢复  2.基于位置恢复  数据库在某一时间点可能既有错误的操作也有正确的操作 可以基于精准的位置跳过错误的操作 发生错误节点之前的一个节点，上一次正确操作的位置点停止  3.基于时间点恢复  跳过某个发生错误的时间点实现数据恢复 在错误时间点停止，在下一个正确时间点开始   一、增备实验 1.开启二进制日志功能 vim /etc/my.cnf [mysqld] log-bin=mysql-bin binlog_format = MIXED      #可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入） server-id = 1              #可加可不加该命令  #二进制日志(binlog)有3种不同的记录格式:  #STATEMENT (基于SQL语句)、 #ROW(基于行)、 #MIXED(混合模式)， #默认格式是STATEMENT  [root@localhost backup]#ls /usr/local/mysql/data #二进制日志位置  2 可以每周对数据库或表进行完全备份 mysqldump -uroot -p123123 kgc info >/backup/kgc_info.sql mysqldump -uroot -p123123 --all-databases  >/backup/kgc_info.sql  3.可以每天进行增量备份操作，生成新的二进制文件（mysql-bin.000002） mysqladmin -uroot -p123123 -p flush-logs  4插入新数据 use info  insert into info values(2,'gk',22); insert into info values(3,'jk',23);  5再次生成新的二进制日志文件 mysqladmin -uroot -p123123  flush-logs #之前的操作会保存在上一个二进制文件中，之后的操作会保存在新的二进制文件中  6.查看二进制日志文件 cp /usr/loacal/mysql/data/mysql-bin.000002 /opt/ mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002  增量恢复： 1模拟丢失文件 delete from info where id=1; mysqlbinlog --no-defaults /opt/mysql-bin.000002 |mysql -u -root -p123123  3.3-1增量备份与恢复 实际操作  #首先修改配置文件启用二进制日志 [root@localhost ~]#vim /etc/my.cnf #修改二进制日志文件 [mysqld] log-bin=mysql-bin binlog_format = MIXED   [root@localhost ~]#systemctl restart mysqld.service #重启服务 root@localhost ~]#ls /usr/local/mysql/data/ #查看日志文件是否生成 mysql-bin.000001 auto.cnf  ib_buffer_pool  ib_logfile0  ibtmp1  mysql-bin.000001  performance_schema bbs       ibdata1         ib_logfile1  mysql   mysql-bin.index   sys  [root@localhost ~]#mysql -uroot -p123123 #进入数据库创建环境 mysql> create database ky15; #创建数据库 ky15 mysql> use ky15 #进入数据库 ky15 mysql> create table info (id int , name char(20),age int,address char(50),hobby char(50)); mysql> desc info; #查看表结构 +---------+----------+------+-----+---------+-------+ | Field   | Type     | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | id      | int(11)  | YES  |     | NULL    |       | | name    | char(20) | YES  |     | NULL    |       | | age     | int(11)  | YES  |     | NULL    |       | | address | char(50) | YES  |     | NULL    |       | | hobby   | char(50) | YES  |     | NULL    |       | +---------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> insert into info values(1,'jk',18,'南京','吃饭'); mysql> insert into info values(2,'ll',19,'北京','跳舞'); #插入数据 mysql> select * from info; +------+------+------+---------+--------+ | id   | name | age  | address | hobby  | +------+------+------+---------+--------+ |    1 | jk   |   18 | 南京    | 吃饭   | |    2 | ll   |   19 | 北京    | 跳舞   | +------+------+------+---------+--------+ 2 rows in set (0.00 sec)  mkdir /backup #创建存放备份文件的目录 mysqldump -uroot -p123123 ky15 info > /backup/ky15_info.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. #备份ky15 下的info表 mysqladmin -uroot -p123123 flush-logs #刷新日志 ls /usr/local/mysql/data/ #查看是否生成了新的 二进制日志 auto.cnf  ib_buffer_pool  ib_logfile0  ibtmp1  mysql             mysql-bin.000002  performance_schema bbs       ibdata1         ib_logfile1  ky15    mysql-bin.000001  mysql-bin.index   sys  #数据库中插入新的文件 mysql> use ky15 #进入数据库 ky15 mysql> insert into info values(3,'lili',23,'北京','唱歌'); Query OK, 1 row affected (0.00 sec) mysql> insert into info values(4,'lihua',23,'北京','骂人'); Query OK, 1 row affected (0.00 sec)  mysql> select * from info; +------+-------+------+---------+--------+ | id   | name  | age  | address | hobby  | +------+-------+------+---------+--------+ |    1 | jk    |   18 | 南京    | 吃饭   | |    2 | ll    |   19 | 北京    | 跳舞   | |    3 | lili  |   23 | 北京    | 唱歌   | |    4 | lihua |   23 | 北京    | 骂人   | +------+-------+------+---------+--------+ 4 rows in set (0.00 sec)  cat /usr/local/mysql/data/mysql-bin.000002  mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002 #查看日志文件  mysql> drop database ky15; Query OK, 1 row affected (0.00 sec)                         #模拟删库  mysql> create database ky15；  mysql -uroot -p123123 ky15 < ky15_info.sql  mysql: [Warning] Using a password on the command line interface can be insecure. #恢复表  mysql> show tables; mysql> select * from info; +------+------+------+---------+--------+ | id   | name | age  | address | hobby  | +------+------+------+---------+--------+ |    1 | jk   |   18 | 南京    | 吃饭   | |    2 | ll   |   19 | 北京    | 跳舞   | +------+------+------+---------+--------+ 2 rows in set (0.00 sec)  mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p123123 mysql: [Warning] Using a password on the command line interface can be insecure. #恢复 增量备份  mysql> select * from info; +------+-------+------+---------+--------+ | id   | name  | age  | address | hobby  | +------+-------+------+---------+--------+ |    1 | jk    |   18 | 南京    | 吃饭   | |    2 | ll    |   19 | 北京    | 跳舞   | |    3 | lili  |   23 | 北京    | 唱歌   | |    4 | lihua |   23 | 北京    | 骂人   | +------+-------+------+---------+--------+ 4 rows in set (0.00 sec)  3.4断点恢复 基于位置恢复  删除库中的数据 mysql> delete from info where id=3; Query OK, 1 row affected (0.01 sec)  mysql> delete from info where id=4; Query OK, 1 row affected (0.00 sec)  mysql> select * from info; +------+------+------+---------+--------+ | id   | name | age  | address | hobby  | +------+------+------+---------+--------+ |    1 | jk   |   18 | 南京    | 吃饭   | |    2 | ll   |   19 | 北京    | 跳舞   | +------+------+------+---------+--------+ 2 rows in set (0.00 sec)  只恢复其中之一 [root@localhost backup]#mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002 >/backup/mysqlbin.log #查看 相关的序号  [root@localhost backup]#mysqlbinlog --no-defaults --stop-position='601' /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p123123 #从头开始  到601结束 mysql> select * from info; +------+------+------+---------+--------+ | id   | name | age  | address | hobby  | +------+------+------+---------+--------+ |    1 | jk   |   18 | 南京    | 吃饭   | |    2 | ll   |   19 | 北京    | 跳舞   | |    3 | lili |   23 | 北京    | 唱歌   | +------+------+------+---------+--------+ 3 rows in set (0.00 sec)  [root@localhost backup]#mysqlbinlog --no-defaults --start-position='601' /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p123123 mysql: [Warning] Using a password on the command line interface can be insecure. #从601 开始  mysql> select * from info; +------+-------+------+---------+--------+ | id   | name  | age  | address | hobby  | +------+-------+------+---------+--------+ |    1 | jk    |   18 | 南京    | 吃饭   | |    2 | ll    |   19 | 北京    | 跳舞   | |    4 | lihua |   23 | 北京    | 骂人   | +------+-------+------+---------+--------+ 基于时间点  #格式只将position 改为datetime 时间日期 格式  年-月-日 时：分：秒 [root@localhost backup]#mysqlbinlog --no-defaults --start-datetime='2021-11-29 14:31:14' /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p123123 mysql: [Warning] Using a password on the command line interface can be insecure. SQL 高级语言 1导入数据库 mysql> source /backup/hellodb_innodb.sql; #将脚本导入  source  加文件路径 1 2 2. select 显示表格中的一个或者多个字段中所有的信息 语法： select 字段名  from 表名; 例子 select * from info; select name from info; select name,id,age from info; 3. distinct distinct 查询不重复记录 中文含义：/dɪˈstɪŋkt/ 不同的 明显的 语法: select distinct 字段 from 表名﹔  例子： select distinct age from students; #去除年龄字段中重复的 select distinct gender from students; #查找性别  4. where where 有条件的查询 语法：select '字段' from 表名  where 条件  select name,age from students where age < 20; #显示name和age 并且要找到age 小于20的 5.and;or and 且           or  或 语法： select 字段名  from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;  例子： select name,age from students where 30 > age and age > 20; select name,age,classid from students where 30 > age and age > 20 and classsid=3; select name,age from students where gender='m' or(30 > age and age > 20); #男的 或 30到20岁之间   6.in in： 显示已知值的数值 语法： select 字段名  from 表名 where 字段 in ('值1'，'值2'....); 例子： select * from students where StuID in (1,2,3,4); select * from students where ClassID in (1,4); 7.between between： 显示两个值范围内的资料 语法： select 字段名  from 表名 where 字段 between  '值1' and '值2'; 包括 and两边的值 例子： select * from students where name between 'ding dian' and 'ling chong'; # 一般不使用在字符串上 select * from students where stuid between 2 and 5; #id 2到5 的信息 包括2和5 select * from students where age between '22' and '30'; #不需要表中一定有该字段，只会将22 到30 已有的都显示出来 8. like 通配符模糊查询 通配符通常是和  like 一起使用 语法： select 字段名  from 表名 where 字段 like 模式  select * from students where name like 's%'; select * from students where name like '%on%'; 通配符    含义 %    表示零个，一个或者多个字符 _    下划线表示单个字符 A_Z    所有以A开头 Z 结尾的字符串 ‘ABZ’ ‘ACZ’ 'ACCCCZ’不在范围内 下划线只表示一个字符 AZ 包含a空格z ABC%    所有以ABC开头的字符串 ABCD ABCABC ËA    所有以CBA结尾的字符串 WCBA CBACBA %AN%    所有包含AN的字符串 los angeles _AN%    所有 第二个字母为 A 第三个字母 为N 的字符串 9. order by order by 按关键字排序 语法： select 字段名  from 表名 where 条件 order by 字段 [asc,desc]; 默认 asc 正向排序 desc 反向排序  select age,name from students order by age; #正向排序  select name,age from info order by age desc; #反向排序  可以加上where select name,age from students where classid=3 order by age; #显示 name和age字段的数据  并且只显示classid字段为3 的 并且以age字段排序 10.函数 10.1 数学函数 函数    含义 abs(x)    返回x 的 绝对值 |+ -1| = 1 rand()    返回0到1的随机数 mod(x,y)    返回x除以y以后的余数 x 除以 y 的值 取余 power(x,y)    返回x的y次方 x是底数 y是次方 round(x)    返回离x最近的整数 round（1.4） 取1 round（1.5）2 round(x,y）    保留x的y位小数四舍五入后的值round（3.1415926，5） 3.14159 sqrt(x)    返回x的平方根 4 2 truncate(x,y)    返回数字 x 截断为 y 位小数的值 truncate(3.1415926345,3); 3.141直接截断 ceil(x)    返回大于或等于 x 的最小整数 正整数就返回本身 小数就整数加一 floor(x)    返回小于或等于 x 的最大整数 就是整数本身 greatest(x1,x2…)    返回返回集合中最大的值 least(x1,x2…)    返回返回集合中最小的值 例子：  select abs(-1); select rand(); select mod(6,4); select power(2,2); select round(2.5); select round(3.1415926345,3); select truncate(3.1415926345,3); select ceil(2.6); select floor(2.4); select least(22,33,44); select greatest(55,66,88); 10.2 聚合函数 函数    含义 avg()    返回指定列的平均值 count()    返回指定列中非 NULL 值的个数 空值返回 min()    返回指定列的最小值 max()    返回指定列的最大值 sum(x)    返回指定列的所有值之和 例子：  语法： select 函数（字段） from  表名;  select avg(age) from students; #求表中年龄的平均值 select sum(age) from students; #求表中年龄的总和 select max(age) from students; #求表中年龄的最大值 select min(age) from students; #求表中年龄的最小值 select count(classid) from students; #求表中有多少非空记录 select count(distinct gender) from students;  select count(*) from students;  insert into test values (''); #加入null值  insert into test values(null); select count(name) from test;  如果某表只有一个字段使用*不会忽略 null 如果count后面加上明确字段会忽略 null  select sum(age) from students where classid is Null; #查询空值  #####思考空格字符 会被匹配么？########  insert into students values(26,' ',22,'m',3,1);  select count(name) from students; +-------------+ | count(name) | +-------------+ |          27 | +-------------+ 空格字符是会被匹配的。 10.3 字符串函数 函数    描述 trim()    返回去除指定格式的值 concat(x,y)    将提供的参数 x 和 y 拼接成一个字符串 substr(x,y)    获取从字符串 x 中的第 y 个位置开始的字符串，跟substring（）函数作用相同 substr(x,y,z)    获取从字符串 x 中的第 y 个位置开始长度为z 的字符串 length(x)    返回字符串 x 的长度 replace(x,y,z)    将字符串 z 替代字符串 x 中的字符串 y upper(x)    将字符串 x 的所有字母变成大写字母 lower(x)    将字符串 x 的所有字母变成小写字母 left(x,y)    返回字符串 x 的前 y 个字符 right(x,y)    返回字符串 x 的后 y 个字符 repeat(x,y)    将字符串 x 重复 y 次 space(x)    返回 x 个空格 strcmp(x,y)    比较 x 和 y，返回的值可以为-1,0,1 reverse(x)    将字符串 x 反转 例子  #trim： 语法： select trim (位置 要移除的字符串 from 原有的字符串) #区分大小写 其中位置的值可以是  leading（开始）  trailing(结尾) both(起头及结尾) 要移除的字符串:从字符串的起头、结尾或起头及结尾移除的字符串，缺省时为空格。#区分大小写 select trim(leading 'Sun' from 'Sun Dasheng'); select trim(both  from ' Sun Dasheng      '); #去除空格  #length: 语法：select length(字段) from 表名； #可以在函数前再加字段 select name,length(name) from students; #计算出字段中记录的字符长度  #replace（替换） 语法：select replace(字段，'原字符''替换字符') from 表名； select replace(name,'ng','gl') from students; #把ng换成gl  #concat： 语法：select concat(字段1，字段2）from 表名 #如有任何一个参数为NULL ，则返回值为 NULL           select concat(name,classid) from students; elect concat(name,classid) from students where classid=3; select name || classid from students where classid=3; select concat(name,'\t',classid) from students where classid=3; select concat(classid,name) from students order by classid;  #substr：                语法：select substr(字段，开始截取字符，截取的长度)  where 字段='截取的字符串'                select substr(name,6) from students where name='Sun Dasheng'; #从第六个开始保留 （包括第六个） select substr(name,6,2) from students where name='Sun Dasheng'; 11 group by group by: 对group by  后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的 group by    有一个原则，就是select 后面的所有列中，没有使用聚合函数的列必须出现在 group by 的后面。  语法： select 字段1，sum(字段2) from 表名 group by 字段1；  例子： select classid,sum(age) from students group by classid; #求各个班的年龄总和 select classid,avg(age) from students group by classid; #求平均年龄 select classid,count(age) from students group by classid; having having：用来过滤由group by语句返回的记录集，通常与group by语句联合使用 having 语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。 语法:SELECT 字段1，SUM("字段")FROM 表格名 GROUP BY 字段1 having（函数条件);  select classid,avg(age) from students group by classid where age > 30; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where age > 30' at line 1  select classid,avg(age) from students group by classid having age > 30; ERROR 1054 (42S22): Unknown column 'age' in 'having clause'  select classid,avg(age) from students group by classid having avg(age) > 30;  要根据新表中的字段 来制定条件  13 别名 在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者  多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性 语法 对于字段的别名： select 原字段 as 修改字段,原字段 as 修改字段 from 表名 ; #as 可以省略。 例子： select s.name as n, s.stuid as id from students s; #对于列的别名  #如果表的长度比较长，可以使用 AS 给表设置别名，在查询的过程中直接使用别名临时设置info的别名为i 对于表的别名： select 表格别名.原字段 as 修改字段[,表格别名.原字段 as 修改字段]from 原表名 as 表格别名 ; #as可以省略  select avg(age) '平均值' from students; #将聚合函数字段 设置成平均值  使用场景： 1、对复杂的表进行查询的时候，别名可以缩短查询语句的长度 2、多表相连查询的时候（通俗易懂、减短sql语句长度）  此外，AS 还可以作为连接语句的操作符。 创建t1表，将info表的查询记录全部插入t1表  create table test as select * from students; #可以使用as直接创建，不继承 特殊键  create table test2 (select * from students); #或者不继承 特殊键 create table test2 like students; 14 子查询 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dCxZCIzj-1640048934875)(日志和备份.assets/image-20211201003447788.png)]  子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。 子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。 #子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询 语法： select 字段 from 表1 where 字段2 [比较运算符] (select 字段1 from 表格2 where 条件) #比较运算符 可以是 =  >  <  >= <= 也可以是文字运算符 like in between  例子： select name,age from students where age in (select age from students where age>30); #同一表中 select * from teachers where tid in (select teacherid from students where teacherid<3); #显示s 表中 老师id小于3的  select avg(age) from students; #计算平均年龄 select * from teachers where age > (select avg(age) from students); #找到大于平均年龄的人 update students set teacherid=2 where stuid=6; #更新数据再试一次  update teachers set age=(select avg(age) from students) where tid=4;  select name,age from students where age> (select avg(age) from teachers); #显示 students 表中 name和age 字段中大于  teachers表中的平均值  select * from students inner join teachers on students.teacherid=teachers.tid; 15 exists 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空， 则返回 TRUE；反之，则返回 FALSE  select * from teachers where tid in (select teacherid from students where teacherid<3);  select * from teachers where  exists (select teacherid from students where teacherid<1);  1 2 3 4 5 6 16 连接查询 inner join on（内连接）只返回两个表中联结字段的相等的行 left join on(左连接): 返回包括左表中的所有记录和右表中联结字段相等的记录 right join on(右连接): 返回包括右表中的所有记录和左表中联结字段相等的记录  语法： select 字段 from 表1 inner join 表2 on 条件 select 字段 from 表1 left join 表2 on 条件 select 字段 from 表1 right join 表2 on 条件  例子： #内连接： select * from teachers inner join students on students.teacherid=teachers.tid; #显示 teacher表的所有字段，采用内连接  要求 teacgerid=tid select * from teachers t inner join students  s on s.teacherid=t.tid;  select * from students , teachers where students.teacherid=teachers.tid; select * from students s ,teachers  t where s.teacherid=t.tid;  #左连接 select * from students s left join teachers  t on s.teacherid=t.tid; #右连接 select * from teachers t right join students  s on s.teacherid=t.tid;  显示内容：  students表是 全部  teacherid 有1 2 3 4  teachers表是 teacherid=tid 只显示 tid=1 2 3 4     上面没有5  所以xiaolongnv 不显示  17视图 ---- CREATE VIEW ----视图，可以被当作是虚拟表或 存储查询结果的表。 #视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。  #临时表在用户退出出或同步数据库的连接断开后就自动消失了，而视图不会消失。  视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。 比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的， 用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。  语法： create view “视图表名” as select 语句;  例子： create view v_test as select t.name from teachers t inner join students s on s.teacherid=t.tid;  查看视图表 show tables；  删除视图表 drop view 视图名字  视图表 本身并不实际存储数据  只是保存一个select语句查询结果  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 18 联集 ---- UNION ---- 联集，将两个sQL语句的结果合并起来，两个sqL语句所产生的字段需要是同样的数据类型； UNION:生成结果的资料值将没有重复，且按照字段的顺序进行排序 语法：select 语句1 union select 语句2  select 语句1 union all select 语句2  例子： select * from teachers union select stuid,name,age,gender from students; #合并 select * from teachers union select name，stuid,age,gender from students;  注意 字段 数据类型 要一致  int和int   char和char  1 2 3 4 5 6 7 8 9 10 11 12 13 19 case 是sql 用来 作为 if-then-else 之类的关键字 语法： select  需要显示的字段名1 '可以自定义'， 需要显示的字段名2 '可以自定义'， case  when 条件1 then 结果1 when 条件2 then 结果2 else  end '显示结果的字段名' 条件可以是一个 数值 或是公式 else 子句 不是必须的 mysql> select                      #语法     -> name '名字',                #需要显示的字段     -> age '年龄',                 #需要显示的字段     -> case                       #语法     -> when age < 18 then '少年'   #条件     -> when age < 30 then '青年'   #条件     -> when age < 45 then '中年'  #条件     -> else '老年'                #条件     -> end '状态'                 #显示结果的字段名     -> from students;  例1： select name, case when age <18 then '未成年' when age >=18 then '成年' end '是否成年'  from students;  例2： select  *, case when gender='m' then '男'  when gender='f' then '女'   end '性别' from students;  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 20 日期时间函数 字符串函数    描述 curdate()    返回当前时间的年月日 curtime()    返回当前时间的时分秒 now()    返回当前时间的日期和时间 month(x)    返回日期 x 中的月份值 week(x)    返回日期 x 是年度第几个星期 hour(x)    返回 x 中的小时值 minute(x)    返回 x 中的分钟值 second(x)    返回 x 中的秒钟值 dayofweek(x)    返回 x 是星期几，1 星期日，2 星期一 dayofmonth(x)    计算日期 x 是本月的第几天 dayofyear(x)    计算日期 x 是本年的第几天 select curdate(); #当前日期 select curtime(); #当前时间的 时分秒 select now(); #当前时间 年月日时分秒 select month('2021-08-11'); #返回月份 select week('2021-12-02'); #返回一年中的第几天 select hour('2021-12-02 14:18'); #返回小时值 select minute('2021-12-05 14:30:9'); #返回分钟的值 select second() #返回秒值 select dayofweek('2021-12-05'); #返回星期几 select dayofmonth('2021-12-05'); #返回月中某天的值 select dayofyear('2021-12-05'); #返回一年中的 第几天 21 空值和无值 NULL 值和空值有什么区别呢？二者的区别如下： 空值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。 IS NULL 或者 IS  NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是空值的。 空值的判断使用=’’或者<>’’来处理。 在通过 count()计算有多少记录数时，如果遇到 NULL 值会自动忽略掉，遇到空值会加入到记录中进行计算。  mysql> select length(null),length(' '),length('abc'); +--------------+-------------+---------------+ | length(null) | length(' ') | length('abc') | +--------------+-------------+---------------+ |         NULL |           1 |             3 | +--------------+-------------+---------------+  mysql> select length(null),length(''),length('abc'); +--------------+------------+---------------+ | length(null) | length('') | length('abc') | +--------------+------------+---------------+ |         NULL |          0 |             3 | +--------------+------------+---------------+ 1 row in set (0.00 sec)  select * from students where name is not null; select * from students where teacherid is null; 22 regexp正则表达式 匹配模式    描述    实例 ^    匹配文本的开始字符    ‘^bd’ 匹配以 bd 开头的字符串 $    匹配文本的结束字符    ‘qn$’ 匹配以 qn 结尾的字符串 .    匹配任何单个字符    ‘s.t’ 匹配任何s 和t 之间有一个字符的字符串 *    匹配零个或多个在它前面的字符    ‘fo*t’ 匹配 t 前面有任意个 o +    匹配前面的字符 1 次或多次    ‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串 字符串    匹配包含指定的字符串    ‘clo’ 匹配含有 clo 的字符串 p1|p2    匹配 p1 或 p2    ‘bg|fg’ 匹配 bg 或者 fg […]    匹配字符集合中的任意一个字符    ‘[abc]’ 匹配 a 或者 b 或者 c [^…]    匹配不在括号中的任何字符    [^ab] 匹配不包含 a 或者 b 的字符串 {n}    匹配前面的字符串 n 次    ‘g{2}’ 匹配含有 2 个 g 的字符串 {n,m}    匹配前面的字符串至少 n 次，至多m 次    f{1,3}’ 匹配 f 最少 1 次，最多 3 次 select id,name from info where name regexp '^sh'; #查询以sh开头的学生信息 select id,name from info where name regexp 'n$'; #查询以n结尾的学生信息 select id,name from info where name regexp 'an'; #查询名字中包含an的学生信息 select id,name from info where name regexp 'tangy.n'; #查询名字是tangy开头，n结尾，中间不知道是一个什么字符的学生信息 select id,name from info where name regexp 'an|zh'; #查询名字中包含an或者zh的学生信息 select id,name from info where name regexp '^[s-x]'; #查询名字以s-x开头的学生信息 select id,name from info where name regexp '^[^a-z]'; # 23 运算符 1、算术运算符 以 SELECT 命令来实现最基础的加减乘除运算，MySQL 支持使用的算术运算符，  +        加法 -        减法 *        乘法 /        除法 %        取余  select 1+2,2-1,3*4,4/2,5%2;  24 存储过程 使用方法 是shell脚本  格式和函数很像  MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合 存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一个指定的名称存储起来， 这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。 存储过程在执行上比传统sql速度更快、执行效率更高。 1 2 3 4 存储过程优势：  封装性  通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码  可增强 SQL 语句的功能和灵活性  存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。  可减少网络流量  由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。  提高性能  当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能  提高数据库的安全性和数据的完整性  存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。  使数据独立  数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。  语法： CREATE PROCEDURE <存储过程名> ( [过程参数[,…] ] ) <过程体> [过程参数[,…] ] 格式 <过程名>：尽量避免与内置的函数或字段重名 <过程体>：语句 [ IN | OUT | INOUT ] <参数名><类型>  1) 过程名 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。 需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。  2) 过程参数 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。 MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。  3) 过程体 存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束 在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。 为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下： delimiter $$ 语法说明如下： $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符  成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个?? mysql > DELIMITER ?? 若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可 mysql > DELIMITER ; 注意：DELIMITER 和分号“;”之间一定要有一个空格  delimiter ?? CREATE PROCEDURE 存储过程名()                                               BEGIN                 执行的sql语句 1 ; 执行的sql语句 2 ; end ??  delimiter(一定要加空格，一定要加空格，一定要加空格);  call 存储过程名  示例（不带参数的创建） ##创建存储过程## DELIMITER $$                                                      #将语句的结束符号从分号;临时改为两个$$(可以自定义) CREATE PROCEDURE Proc()                                              #创建存储过程，过程名为Proc，不带参数 -> BEGIN                                                          #过程体以关键字 BEGIN 开始 -> create table mk (id int (10), name char(10),score int (10));   #过程体语句 -> insert into mk values (1, 'wang',13);                          #过程体语句 -> select * from mk;                                              #过程体语句 -> END $$                                                          #过程体以关键字 END 结束  DELIMITER ;                                                          #将语句的结束符号恢复为分号  mysql> delimiter // mysql> create procedure data()     -> begin     -> select now();     -> end // Query OK, 0 rows affected (0.00 sec)  mysql> delimiter ; mysql> call data; +---------------------+ | now()               | +---------------------+ | 2021-12-01 17:36:43 | +---------------------+ 1 row in set (0.00 sec)  Query OK, 0 rows affected (0.00 sec)   delimiter @@ create procedure proc (in inname varchar(40))     begin select * from info where name=inname; end @@  delimiter ;          call proc2('wangwu');        条件判断 if then else   ..... end if delimiter $$ create procedure  proc6(in var int) begin if var>10 then update students set age=age+1 where stuid=1; end if; end $$   delimiter ; call proc(11)   循环 while do....end while  create table testlog (id int auto_increment primary key,name char(10),age int default 20);  delimiter $$  create procedure  ky15()  begin   declare i int; set i = 1;  while i <= 100000  do  insert into testlog(name,age) values (concat('zhou',i),i);  set i = i +1;  end while;  end$$  delimiter ;  select concat(zhou,1); select * from testlog limit 10;    ##查看存储过程## 格式： SHOW CREATE PROCEDURE [数据库.]存储过程名;        #查看某个存储过程的具体信息 SHOW CREATE PROCEDURE  proc1  ##删除存储过程## 存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。  DROP PROCEDURE IF EXISTS Proc; 24生产环境 my.cnf 配置案例 32G  #打开独立表空间 innodb_file_per_table = 1 #MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值 max_connections = 8000 #所有线程所打开表的数量 open_files_limit = 10240 #back_log 是操作系统在监听队列中所能保持的连接数 back_log = 300 #每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL 服务器重启或通过flush hosts命令清空此主机的相关信息 max_connect_errors = 1000 #每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值 max_allowed_packet = 32M #指定一个请求的最大连接时间 wait_timeout = 10 # 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序 sort_buffer_size = 16M  #不带索引的全表扫描.使用的buffer的最小值 join_buffer_size = 16M  #查询缓冲大小 query_cache_size = 128M #指定单个查询能够使用的缓冲区大小，缺省为1M query_cache_limit = 4M     # 设定默认的事务隔离级别 transaction_isolation = REPEATABLE-READ # 线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，此容量的内存在每次 连接时被预留. thread_stack = 512K  # 二进制日志功能 log-bin=/data/mysqlbinlogs/ #二进制日志格式 binlog_format=row #InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80% innodb_buffer_pool_size = 24G  #用来同步IO操作的IO线程的数量 innodb_file_io_threads = 4 #在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍 innodb_thread_concurrency = 16 # 用来缓冲日志数据的缓冲区的大小 innodb_log_buffer_size = 16M 在日志组中每个日志文件的大小 innodb_log_file_size = 512M  # 在日志组中的文件总数 innodb_log_files_in_group = 3 # SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间 innodb_lock_wait_timeout = 120 #慢查询时长 long_query_time = 2 #将没有使用索引的查询也记录下来 log-queries-not-using-indexes 25基础规范 (1)必须使用InnoDB存储引擎 解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高 (2)使用UTF8MB4字符集 解读：万国码，无需转码，无乱码风险，节省空间，支持表情包及生僻字 (3)数据表、数据字段必须加入中文注释 解读：N年后谁知道这个r1,r2,r3字段是干嘛的 (4)禁止使用存储过程、视图、触发器、Event 解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量 大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现 “增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧！ (5)禁止存储大文件或者大照片 解读：为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统，数据库里存URI多好。 26命名规范 (6)只允许使用内网域名，而不是ip连接数据库 (7)线上环境、开发环境、测试环境数据库内网域名遵循命名规范 业务名称：xxx 线上环境：xxx.db 开发环境：xxx.rdb 测试环境：xxx.tdb 从库在名称后加-s标识，备库在名称后加-ss标识 innodb_log_file_size = 512M  # 在日志组中的文件总数 innodb_log_files_in_group = 3 # SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间 innodb_lock_wait_timeout = 120 #慢查询时长 long_query_time = 2  #将没有使用索引的查询也记录下来 log-queries-not-using-indexes 线上从库：xxx-s.db 线上备库：xxx-sss.db (8)库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用 (9)库名与应用名称尽量一致，表名:t_业务名称_表的作用，主键名:pk_xxx，非唯一索引名:idx_xxx，唯 一键索引名:uk_xxx 27表设计规范 (10)单实例表数目必须小于500 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。 说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表 (11)单表列数目必须小于30 (12)表必须有主键，例如自增主键 解读： a)主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用 b)主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的 减少索引的磁盘空间，提高索引的缓存效率 c) 无主键的表删除，在row模式的主从架构，会导致备库夯住 (13)禁止使用外键，如果有外键完整性约束，需要应用程序控制 解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能， 甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先 28字段设计规范 (14)必须把字段定义为NOT NULL并且提供默认值 解读： a)null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 b)null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性;同等条件下，表中有较 多空字段的时候，数据库的处理性能会降低很多 c)null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识 d)对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符 号。如：where name!='shenjian'，如果存在name为null值的记录，查询结果就不会包含name为null 值的记录 (15)禁止使用TEXT、BLOB类型 解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能 BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。  (16)禁止使用小数存储货币 解读：使用整数吧，小数容易导致钱对不上  (17)必须使用varchar(20)存储手机号     解读： a)涉及到区号或者国家代号，可能出现+-() b)手机号会去做数学运算么? c)varchar可以支持模糊查询，例如：like“138%”  (18)禁止使用ENUM，可使用TINYINT代替 解读： a)增加新的ENUM值要做DDL操作 b)ENUM的内部实际存储就是整数，你以为自己定义的是字符串?  29索引设计规范 (19)单表索引建议控制在5个以内 (20)单索引字段数不允许超过5个 解读：字段超过5个时，实际已经起不到有效过滤数据的作用了 (21)禁止在更新十分频繁、区分度不高的属性上建立索引 解读： a)更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能 b)“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似 (22)建立组合索引，必须把区分度高的字段放在前面 解读：能够更加有效的过滤数据 30 SQL使用规范 (23)禁止使用SELECT *，只获取必要的字段，需要显示说明列属性 解读： a)读取不需要的列会增加CPU、IO、NET消耗 b)不能有效的利用覆盖索引 c)使用SELECT * 容易在增加或者删除字段后出现程序 BUG (24)禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性 解读：容易在增加或者删除字段后出现程序BUG  (25)禁止使用属性隐式转换 解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone 索引，猜猜为什么?(这个线上问题不止出现过一次)  (26)禁止在WHERE条件的属性上使用函数或者表达式 解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描 正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00') (27)禁止负向查询，以及%开头的模糊查询 解读： a)负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描 b)%开头的模糊查询，会导致全表扫描 (28)禁止大表使用JOIN查询，禁止大表使用子查询 解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能 (29)禁止使用OR条件，必须改为IN查询 解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮 助实施查询优化呢? (30)应用程序必须捕获SQL异常，并有相应处理  常见错误代码 常见的服务器错误代码及说明如下表所示：  错误代码    说 明 1004    无法创建文件 1005    无法创建数据表、创建表失败 1006    无法创建数据库、创建数据库失败 1007    无法创建数据库，数据库己存在 1008    无法删除数据库，数据库不存在 1009    不能删除数据库文件导致删除数据库失败 1010    不能删除数据目录导致删除数据库失败 1011    删除数据库文件时出错 1012    无法读取系统表中的记录 1013    无法获取的状态 1014    无法获得工作目录 1015    无法锁定文件 1016    无法打开文件 1017    无法找到文件 1018    无法读取的目录 1019    无法为更改目录 1020    记录已被其它用户修改 1021    硬盘剩余空间不足，请加大硬盘可用空间 1022    关键词重读，更改记录失败 1023    关闭时发生错误 1025    更改名字时发生错误 1032    记录不存在 1036    数据表是只读的，不能对它进行修改 1037    系统内存不足，请重启数据库或重启服务器 1042    无效的主机名 1044    当前用户没有访问数据库的权限 1045    不能连接数据库，用户名或密码错误 常见的客户端错误代码及说明如下所示：  2000    未知 MySQL 错误 2001    不能创建 UNIX 套接字（%d） 2002    不能通过套接字“ %s”（%d）连接到本地 MySQL 服务器， self 服务未启动 2003    不能连接到 %s ”（%d ）上的 MySQL 服务器，未启动 mysql 服务 2004    不能创建 TCP/IP 接字（%d） 2005    未知的 MySQL 服务器主机“ %s”（%d） 2007    协议不匹配，服务器版本=%d，客户端版本=%d 2008    MySQL 客户端内存溢出 2009    错误的主机信息 2010    通过 UNIX 套接字连接的本地主机 2012    服务器握手过程中出错 2013    查询过程中丢失了与 SQL 服务器的连接 2014    命令不同步，现在不能运行该命令 2024    连接到从服务器时出错 2025    连接到主服务器时出错 2026    SSL 连接错误 死锁 是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。就是所谓的锁资源请求产生了回路现象，即死循环，此时称系统处于死锁状态或系统产生了死锁。常见的报错信息为“Deadlock found when trying to get lock…”。  在 A窗口中执行以下命令：  use hellodb create index id_index on students(age);  在 A窗口中执行以下命令： BEGIN; UPDATE students set age=50 where stuid=1; 紧接着在 B窗口中执行以下命令。 BEGIN; UPDATE students set age=60 where stuid=2; 由于 age 是索引字段，与 A窗口中更新的是不同行的数据，所以这时不会出现锁等待现象。  然后在 A窗口中，执行以下命令，这时就会出现锁等待现象了 UPDATE students set age=70 where stuid=2; 最后在 B窗口中，执行以下命令，这时会出现相互等待资源的现象，也就是死锁现象 UPDATE students set age=80 where stuid=1;  innoDB 的并发写操作会触发死锁，同时 InnoDB 也提供了死锁检测机制。通过设置 innodb_deadlock_detect 参数的值来控制是否打开死锁检测。 innodb_deadlock_detect = ON ：默认值，打开死锁检测。数据库发生死锁时，系统会自动回滚其中的某一个事务，让其它事务可以继续执行。 innodb_deadlock_detect = OFF：关闭死锁检测。发生死锁时，系统会用锁等待来处理。 show VARIABLES like 'innodb_deadlock_detect';  MySQL 通过 innodb_lock_wait_timeout 参数控制锁等待的时间，单位是秒 SHOW VARIABLES LIKE '%innodb_lock_wait%'; set global VARIABLES innodb_lock_wait_timeout = 500  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 在实际应用中，我们要尽量防止死锁等待现象的发生，下面介绍几种避免死锁的方法：  如果不同程序会并发存取多个表，或者涉及多行记录时，尽量约定以相同的顺序访问表，这样可以大大降低死锁的发生。 业务中要及时提交或者回滚事务，可减少死锁产生的概率。 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。 对于非常容易产生死锁的业务部分，可以尝试使用升级锁粒度，通过表锁定来减少死锁产生的概率（表级锁不会产生死锁）。 MySQL集群Cluster 1MySQL 主从复制 1.1主从复制架构和原理 1.1.1服务性能扩展方式  向上扩展，垂直扩展  向外扩展，横向扩展  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v78UWpOK-1640048934876)(mysql日志和备份高级语言.assets/image-20211202142236168.png)]  1.2MySQL的扩展 读写分离 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制 1.2.1什么是读写分离？ 读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE）， 而从数据库处理SELECT查询操作。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。  MySQL 读写分离原理 读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性操作，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性操作导致的变更同步到集群中的从数据库。 目前较为常见的 MySQL 读写分离分为以下两种： 1）基于程序代码内部实现 在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。 优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。 但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。  2）基于中间代理层实现 代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。 （1）MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断。 （2）Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。 （3）Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。  由于使用MySQL Proxy 需要写大量的Lua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MySQL Proxy 内置变量和MySQL Protocol 的人来说是非常困难的。 Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。 1.2.2为什么要读写分离呢？ 因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。 但是数据库的“读”（读10000条数据可能只要5秒钟）。 所以读写分离，解决的是，数据库的写入，影响了查询的效率。 1.2.3 什么时候要读写分离？ 数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。 利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。 1.2.4 主从复制与读写分离 在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。  mysq支持的复制类型 （1）STATEMENT：基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。 （2）ROW：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。 （3）MIXED：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。 1.3 复制的功用 数据分布 负载均衡读操作 备份 高可用和故障切换 MySQL升级测试 1.4 复制架构 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lRPz0sz3-1640048934877)(mysql日志和备份高级语言.assets/image-20211202144811304.png)]  ​  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BKdx9xJE-1640048934877)(mysql日志和备份高级语言.assets/image-20211202144750987.png)]  1.5 主从复制原理 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qzVuFaqD-1640048934878)(mysql日志和备份高级语言.assets/image-20211202144924645.png)]  主从复制的工作过程： 1Master节点的数据的改变记录成二进制日志（bin log）， 2当Master上的数据发生改变时，则将其改变写入二进制日志中。 3开启一个slave服务线程传递给从服务器 4Slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求 Master的二进制事件。 5同时Master节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至Slave节点本地的中继日志（Relay log）中，Slave节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，即解析成 sql 语句逐一执行，使得其数据和 Master节点的保持一致，最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒。  注： ●中继日志通常会位于 OS 缓存中，所以中继日志的开销很小。 ●复制过程有一个很重要的限制，即复制在 Slave上是串行化的，也就是说 Master上的并行更新操作不能在 Slave上并行操作 1 2 3 4 5 6 7 8 9 10 1.5.1主从复制相关线程 主节点：  dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events  从节点：  I/O Thread：向Master请求二进制日志事件，并保存于中继日志中  SQL Thread：从中继日志中读取日志事件，在本地完成重放  1.5.2 跟复制功能相关的文件： master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等 relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系 mariadb-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志 1.5.3 MySQL 主从复制延迟 master服务器高并发，形成大量事务 网络延迟 主从硬件设备导致 cpu主频、内存io、硬盘io 本来就不是同步复制、而是异步复制 从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。 从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o面性。 从库使用SSD磁盘 网络优化，避免跨机房实现同步 2.实际操作 1.环境配置 master服务器： 192.168.91.100   mysql5.7 slave1服务器： 192.168.91.101     mysql5.7 slave2服务器： 192.168.91.102    mysql5.7 Amoeba服务器： 192.168.91.103   jdk1.6、Amoeba 客户端 服务器： 192.168.91.104      mysql 2.初始环境准备 [root@localhost ~]#systemctl stop firewalld [root@localhost ~]#setenforce 0 systemctl stop firewalld setenforce 0 1 2 3 4 3.搭建mysql主从复制 3.1搭建时间同步： [root@localhost ~]#yum install ntp -y #安装时间同步服务器，主从都安装好后  ################配置主服务器##################  [root@localhost ~]#vim /etc/ntp.conf  #修改配置文件  server 127.127.91.0 #设置本地时钟源 fudge  127.127.91.0 stratum 8 #设置时间层级为8 限制在15 以内  server 127.127.91.0 fudge  127.127.91.0 stratum 8  [root@localhost ~]#service ntpd start #开启服务  ################配置从服务器################## [root@localhost ~]#yum install ntpdate -y #安装同步服务 [root@localhost ~]#service ntpd start #开启服务 Redirecting to /bin/systemctl start ntpd.service [root@localhost ~]#/usr/sbin/ntpdate 192.168.91.100 #执行同步  4 Dec 13:21:17 ntpdate[70994]: the NTP socket is in use, exiting   [root@localhost ~]#crontab -e */30 * * * *  /usr/sbin/ntpdate 192.168.91.100  3.2配置主从 ######开启二进制日志#### ###主服务器##### [root@localhost ~]#vim /etc/my.cnf [mysqld] server-id = 1 log-bin=master-bin #开启二进制日志 binlog_format=MIXED #二进制日志格式 log-slave-updates=true #开启从服务器同步  log-bin=master-bin binlog_format=MIXED log-slave-updates=true  [root@localhost ~]#systemctl restart mysqld.service  [root@localhost ~]#mysql -uroot -p123123  grant replication slave on *.* to 'myslave'@'192.168.91.%' identified by '123456'; flush privileges; show master status;  ####从服务器###### [root@localhost ~]#vim /etc/my.cnf [mysqld] server-id = 2 #修改，注意id与Master的不同，两个Slave的id也要不同 relay-log=relay-log-bin #添加，开启中继日志，从主服务器上同步日志文件记录到本地 relay-log-index=slave-relay-bin.index #添加，定义中继日志文件的位置和名称，一般和relay-log在同一目录  server-id = 2 relay-log=relay-log-bin relay-log-index=slave-relay-bin.index [root@localhost ~]# systemctl restart mysqld.service  [root@localhost ~]#mysql -uroot -p123123 help change master to change master to master_host='192.168.91.100',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;  start slave; show slave status\G #Slave_IO_Running: Yes #Slave_SQL_Running: Yes  验证主从同步 create database ky15; show databases;   3.3 搭建Amoeba 实现读写分离 ##安装 Java 环境## 因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。  [root@localhost local]#cd /opt/ [root@localhost local]#cp jdk-6u14-linux-x64.bin /usr/local/ [root@localhost local]#chmod +x /usr/local/jdk-6u14-linux-x64.bin [root@localhost local]#cd /usr/local/ [root@localhost local]#./jdk-6u14-linux-x64.bin #一路回车到底，最后输入yes 自动安装 [root@localhost local]#mv jdk1.6.0_14/ jdk1.6 #改个名字 [root@localhost local]#vim /etc/profile export JAVA_HOME=/usr/local/jdk1.6 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba export PATH=$PATH:$AMOEBA_HOME/bin [root@localhost local]#source /etc/profile #刷新下 [root@localhost opt]#mkdir /usr/local/amoeba [root@localhost opt]#cd /opt/ [root@localhost opt]#tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba [root@localhost opt]#chmod -R 755 /usr/local/amoeba/ [root@localhost bin]#ls amoeba      amoeba.classworlds  benchmark.bat amoeba.bat  benchmark           benchmark.classworlds [root@localhost bin]#/usr/local/amoeba/bin/amoeba #如显示amoeba start|stop说明安装成功  #配置 Amoeba读写分离，两个 Slave 读负载均衡## #先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问 grant all on *.* to test@'192.168.91.%' identified by '123123'; flush privileges;  #修改amoeba配置 [root@localhost amoeba]#cd /usr/local/amoeba/conf/ [root@localhost conf]#cp amoeba.xml amoeba.xml.bak #备份配置文件 [root@localhost conf]#vim amoeba.xml #全局配置 30 <property name="user">amoeba</property> #设置登录用户名 32<property name="password">123456</property> #设置密码  115<property name="defaultPool">master</property> #设置默认池为master 118<property name="writePool">master</property> #设置写池 119<property name="readPool">slaves</property> #设置读池  [root@localhost conf]#vim dbServers.xml  23 <!-- <property name="schema">test</property> --> #23行注释 26<property name="user">test</property> #设置登录用户 28 <!--  mysql password --> #删除 29<property name="password">123123</property> #解决28注释，添加密码  45<dbServer name="master"  parent="abstractServer"> #服务池名 48<property name="ipAddress">192.168.91.100</property> #添加地址  52<dbServer name="slave1"  parent="abstractServer"> 55<property name="ipAddress">192.168.91.101</property> 复制6行 添加另一从节点 59<dbServer name="slave2"  parent="abstractServer"> 62<property name="ipAddress">192.168.91.102</property>  66<dbServer name="slaves" virtual="true"> #定义池名 72<property name="poolNames">slave1,slave2</property> #写上从节点名  [root@localhost conf]#amoeba start & #开启服务  [root@localhost ~]#netstat -ntap |grep java tcp6       0      0 127.0.0.1:49171         :::*                    LISTEN      38266/java           tcp6       0      0 :::8066                 :::*                    LISTEN      38266/java           tcp6       0      0 192.168.91.103:45624    192.168.91.102:3306     ESTABLISHED 38266/java           tcp6       0      0 192.168.91.103:49984    192.168.91.100:3306     ESTABLISHED 38266/java           tcp6       0      0 192.168.91.103:60266    192.168.91.101:3306     ESTABLISHED 38266/java   [root@localhost ~]#yum install mariadb mariadb-server.x86_64 -y [root@localhost ~]#mysql -uamoeba -p123123 -h 192.168.91.103 -P8066  mysql> show variables like 'general%'; +------------------+-------------------------------------+ | Variable_name    | Value                               | +------------------+-------------------------------------+ | general_log      | OFF                                 | | general_log_file | /usr/local/mysql/data/localhost.log | +------------------+-------------------------------------+ 2 rows in set (0.00 sec)  mysql> set global general_log=1; [root@localhost data]#tail -f /usr/local/mysql/data/localhost.log 版权声明：本文为CSDN博主「dingshun129」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/dingshun129/article/details/122054735 

 一，delete与truncate区别 在Mysql中，id使用auto_increment参数之后，表示自增。在使用删除操作delete之后，主键id值不会重置，最大值任然是之前的。但是如果使用的是truncate操作，id值将会重置。 delete删除操作为逐行删除，效率较低；而truncate操作类似于drop table + create table，速度较快。 二，delete操作 新建一张表： mysql> show columns from t1; +-------+---------+------+-----+---------+----------------+ | Field | Type    | Null | Key | Default | Extra          | +-------+---------+------+-----+---------+----------------+ | id    | int(11) | NO   | PRI | NULL    | auto_increment | | c1    | int(11) | YES  | MUL | NULL    |                | +-------+---------+------+-----+---------+----------------+ 然后在表中进行一定的增删操作： mysql> insert into t1 values(1,111); Query OK, 1 row affected (0.01 sec)  mysql> select * from t1; +----+------+ | id | c1   | +----+------+ |  1 |  111 | +----+------+ 1 row in set (0.00 sec)  mysql> insert into t1(c1) values(222),(333),(444); Query OK, 3 rows affected (0.01 sec) Records: 3  Duplicates: 0  Warnings: 0  mysql> select * from t1; +----+------+ | id | c1   | +----+------+ |  1 |  111 | |  2 |  222 | |  3 |  333 | |  4 |  444 | +----+------+ 4 rows in set (0.00 sec)  mysql> delete from t1; Query OK, 4 rows affected (0.01 sec)  mysql> insert into t1 values(0,555); Query OK, 1 row affected (0.01 sec)  mysql> select * from t1; +----+------+ | id | c1   | +----+------+ |  5 |  555 | +----+------+ 1 row in set (0.00 sec) mysql> insert into t1 values(1,666); Query OK, 1 row affected (0.02 sec)  mysql> select * from t1; +----+------+ | id | c1   | +----+------+ |  5 |  555 | |  1 |  666 | +----+------+ 2 rows in set (0.00 sec) 到最后的结果可以发现前面被删除的id值虽然不存在，但是顺序的大小被保留了。 所以，我们在使用delete操作的时候，要注意该操作是不会重置自增id的。但是delete可以带where字句可以删除单行，而truncate只能删除整张表中的数据！ ———————————————— 原文链接：https://blog.csdn.net/a765717/article/details/116144040 

mysql清空表数据自增_mysql 清空或删除表数据后，控制表自增列值的方法方法1：truncate table 你的表名//这样不但将数据全部删除，而且重新定位自增的字段方法2：delete from 你的表名dbcc checkident(你的表名,reseed,0)//重新定位自增的字段，让它从1开始方法3：如果你要保存你的数据，介绍你第三种方法，by QINYI用phpmyadmin导出数据库，你在里面会有发现哦编辑sql文件，将其中的自增下一个id号改好，再导入。-------------------------truncate命令是会把自增的字段还原为从1开始的,或者你试试把table_a清空，然后取消自增，保存，再加回自增，这也是自增段还原为1的方法。-------------------------MySql数据库唯一编号字段(自动编号字段)在数据库应用，我们经常要用到唯一编号，以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。MySQL支持多种数据表，每种数据表的自增属性都有差异，这里将介绍各种数据表里的数据列自增属性。ISAM表如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。如果用replace命令基于AUTO_INCREMENT数据列里的值来修改数据表里的现有记录，即AUTO_INCREMENT数据列出现在了replace命令的where子句里，相应的AUTO_INCREMENT值将不会发生变化。但如果replace命令是通过其它的PRIMARY KEY OR UNIQUE索引来修改现有记录的(即AUTO_INCREMENT数据列没有出现在replace命令的where子句中)，相应的AUTO_INCREMENT值--如果设置其为NULL(如没有对它赋值)的话--就会发生变化。last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。其它数据表的自动编号机制都以ISAM表中的机制为基础。MyISAM数据表删除最大编号的记录后，该编号不可重用。可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。可使用复合索引在同一个数据表里创建多个相互独立的自增序列，具体做法是这样的：为数据表创建一个由多个数据列组成的PRIMARY KEY OR UNIQUE索引，并把AUTO_INCREMENT数据列包括在这个索引里作为它的最后一个数据列。这样，这个复合索引里，前面的那些数据列每构成一种独一无二的组合，最末尾的AUTO_INCREMENT数据列就会生成一个与该组合相对应的序列编号。HEAP数据表HEAP数据表从MySQL4.1开始才允许使用自增列。自增值可通过CREATE TABLE语句的 AUTO_INCREMENT=n选项来设置。可通过ALTER TABLE语句的AUTO_INCREMENT=n选项来修改自增始初值。编号不可重用。HEAP数据表不支持在一个数据表中使用复合索引来生成多个互不干扰的序列编号。BDB数据表不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。可重用编号。支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。InnDB数据表不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。不可重用编号。不支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。在使用AUTO_INCREMENT时，应注意以下几点：AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍。AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复。AUTO_INCREMENT数据列必须具备NOT NULL属性。AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。当进行全表删除时，AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时：delete from table_name;ortruncate table table_name这是因为进行全表操作时，MySQL实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。如果想删除所有的数据行又想保留序列编号信息，可这样用一个带where的delete命令以抑制MySQL的优化：delete from table_name where 1;这将迫使MySQL为每个删除的数据行都做一次条件表达式的求值操作。强制MySQL不复用已经使用过的序列值的方法是：另外创建一个专门用来生成AUTO_INCREMENT序列的数据表，并做到永远不去删除该表的记录。当需要在主数据表里插入一条记录时，先在那个专门生成序号的表中插入一个NULL值以产生一个编号，然后，在往主数据表里插入数据时，利用LAST_INSERT_ID()函数取得这个编号，并把它赋值给主表的存放序列的数据列。如：insert into id set id = NULL;insert into main set main_id = LAST_INSERT_ID();可用alter命令给一个数据表增加一个具有AUTO_INCREMENT属性的数据列。MySQL会自动生成所有的编号。要重新排列现有的序列编号，最简单的方法是先删除该列，再重建该，MySQL会重新生连续的编号序列。在不用AUTO_INCREMENT的情况下生成序列，可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数的LAST_INSERT_ID(expr)去插入或修改一个数据列，紧接着又调用不带参数的LAST_INSERT_ID()函数，则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作：先创建一个只有一个数据行的数据表：create table seq_table (id int unsigned not null);insert into seq_table values (0);接着用以下操作检索出序列号：update seq_table set seq = LAST_INSERT_ID( seq + 1 );select LAST_INSERT_ID();通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。该方法可用于计数器，在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK，UNLOCK表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。alter table table_name auto_increment=n;注意n只能大于已有的auto_increment的整数值,小于的值无效.show table status like 'table_name' 可以看到auto_increment这一列是表现有的值.步进值没法改变.只能通过下面提到last_inset_id()函数变通使用在使用AUTO_INCREMENT时，应注意以下几点：AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍。AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复。AUTO_INCREMENT数据列必须具备NOT NULL属性。AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。在不用AUTO_INCREMENT的情况下生成序列，可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数的LAST_INSERT_ID(expr)去插入或修改一个数据列，紧接着又调用不带参数的LAST_INSERT_ID()函数，则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作：先创建一个只有一个数据行的数据表：create table seq_table (id int unsigned not null);insert into seq_table values (0);接着用以下操作检索出序列号：update seq_table set seq = LAST_INSERT_ID( seq + 1 );select LAST_INSERT_ID();通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。该方法可用于计数器，在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK，UNLOCK表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。有两点需要加强注意:1、只有一列的时候是不行的！2、自动编号必须作为主键才有效！————————————————原文链接：https://blog.csdn.net/weixin_39644611/article/details/113229282

方法：1、利用“show OPEN TABLES where In_use > 0;”命令查看表被锁状态；2、利用“SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS”命令查询被锁的表。本教程操作环境：windows10系统、mysql8.0.22版本、Dell G3电脑。mysql怎样查询被锁的表1.查看表是否被锁：（1）直接在mysql命令行执行:show engine innodb status\G。（2）查看造成死锁的sql语句，分析索引情况，然后优化sql。（3）然后show processlist,查看造成死锁占用时间长的sql语句。（4）show status like ‘%lock%。2.查看表被锁状态和结束死锁步骤：（1）查看表被锁状态：show OPEN TABLES where In_use > 0; 这个语句记录当前锁表状态 。（2）查询进程：show processlist查询表被锁进程；查询到相应进程killid。（3）分析锁表的SQL：分析相应SQL，给表加索引，常用字段加索引，表关联字段加索引。（4）查看正在锁的事物：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS。（5）查看等待锁的事物：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS。扩展资料MySQL锁定状态查看命令：Checking table：正在检查数据表（这是自动的）。Closing tables：正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。Connect Out：复制从服务器正在连接主服务器。Copying to tmp table on disk：由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。Creating tmp table：正在创建临时表以存放部分查询结果。deleting from main table：服务器正在执行多表删除中的第一部分，刚删除第一个表。deleting from reference tables：服务器正在执行多表删除中的第二部分，正在删除其他表的记录。Flushing tables：正在执行FLUSH TABLES，等待其他线程关闭数据表。Killed：发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。Locked：被其他查询锁住了。Sending data：正在处理SELECT查询的记录，同时正在把结果发送给客户端。Sorting for group：正在为GROUP BY做排序。Sorting for order：正在为ORDER BY做排序。Opening tables：这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。Removing duplicates：正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。Reopen table：获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。Repair by sorting：修复指令正在排序以创建索引。Repair with keycache：修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。Searching rows for update：正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。Sleeping：正在等待客户端发送新请求。System lock：正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。Upgrading lock：INSERT DELAYED正在尝试取得一个锁表以插入新记录。Updating：正在搜索匹配的记录，并且修改它们。User Lock：正在等待GET_LOCK()。Waiting for tables：该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。waiting for handler insert：INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。原文链接：https://m.php.cn/article/487451.html

mysql表被锁了的解决办法：1、通过暴力解决方式，即重启MYSQ；2、通过“show processlist;”命令查看表情况；3、通过“KILL10866;”命令kill掉锁表的进程ID。mysql表被锁了的解决办法如下:1、暴力解决方式重启MYSQL(重启解决问题利器，手动滑稽)2、查看表情况:1show processlist;1State状态为Locked即被其他查询锁住3、kill掉锁表的进程ID1KILL   10866;//后面的数字即时进程的ID原文链接：https://m.php.cn/article/418327.html

Ubuntu卸载mysql删除mysql的配置文件sudo rm /var/lib/mysql/ -Rsudo rm /etc/mysql/ -R自动卸载mysql（包括server和client）sudo apt-get autoremove mysql* --purge输入y选择yes,按回车键sudo apt-get remove apparmor输入y，按回车键然后在终端中查看MySQL的依赖项dpkg --list|grep mysql依次输入下面的命令1sudo apt-get remove dbconfig-mysqlsudo apt-get remove mysql-clientsudo apt-get remove mysql-client-5.7sudo apt-get remove mysql-client-core-5.7再次执行自动卸载sudo apt-get autoremove mysql* --purge查看MySQL的剩余依赖项dpkg --list|grep mysql如果显示为空，则证明mysql完全删除

背景环境鲲鹏云上应用高可用部署实验 系统：openEuler 20.03 64bit with ARM 版本：mysql-boost-8.0.24 流程：利用cmake编译mysql最终安装报错1.段错误segment fault2.启动时警告，磁盘上的单元文件、源配置文件或 mysql.service 的插入项已更改，但是输入systemctl daemon-reload没有任何作用3.查看messages文件时显示return code=[139], execute failed by [root(uid=0)] from [pts/0 (59.172.176.230)]报错图像

索引及其作用 索引（Index）是帮助 MySQL 高效获取数据的数据结构。索引的本质是数据结构。索引作用是帮助 MySQL 高效获取数据。通俗的说，索引就像一本书的目录，通过目录去找想看的章节就很快，索引也是一样的。如果没有索引，MySQL在查询数据的时候就需要从第一行数据开始一行一行数据对比，只能扫描完整个表找到要查询的数据，表的数据越多需要花费的时间越多。如果表中有相关列的索引，MySQL可以快速确定在数据文件中间查找的位置，而无需查看所有数据，这比按顺序读取每一行要快得多。索引常用的数据结构有BTREE、HASH、RTREE等等，其中数BTREE最为常见。索引的分类 索引分为聚集索引和二级索引。聚集索引 InnoDB引擎中使用了聚集索引(Clustered index)，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该 B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。一般来说，在MySQL中聚集索引和主键是一个意思。聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。如果没有设置主键的话，MySQL默认会创建一个隐含列row_id作为主键。二级索引 二级索引(Secondary Index，也称辅助索引、非聚集索引)是InnoDB引擎中的一类索引，聚集索引以外的索引统称为二级索引，包括唯一索引、联合索引、全文索引等等。二级索引并不包含行记录的全部数据，二级索引上除了当前列以外还包含一个主键，通过这个主键来查询聚集索引上对应的数据。当查询除索引以外的其他数据时，由于数据不在索引上就需要通过主键来找到完整的行记录，这就是回表。针对二级索引MySQL提供了一个优化技术，索引覆盖(covering index)。即从辅助索引中就可以得到查询的记录，就不需要回表再根据聚集索引查询一次完整记录。使用索引覆盖的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作，但是前提是要查询的所有列必须都加了索引。唯一索引 唯一索引(Unique Index)要求列的数据必须是唯一的，唯一索引具有唯一性约束，在插入数据时，如果有列中有相同的数据就会报错。唯一索引可以允许多个列的值为NULL，如果列是字符串类型的话，空字符串值只能有一个。全文索引 全文索引(Full-Text Index)只有在MyISAM和InnoDB存储引擎中支持，全文索引只能创建在基于文本的列上，例如CHAR、VARCHAR、TEXT类型。全文索引不支持索引前缀，即使设置了索引前缀也不会起作用。全文索引采用的是倒排索引(inverted index)设计，倒排索引就是将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字时，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。为了支持邻近搜索，还存储每个单词的位置信息，作为字节偏移量。MySQL 从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符(delimiter)的语言，如中文、日语、韩语等。全文索引辅助表创建一个db_test数据库，并创建一个users表，users表结构如下在name字段上创建全文索引：ALTER TABLE `users` ADD FULLTEXT INDEX `idx_name` (`name`);然后查看INNODB_SYS_TABLES中db_test数据库信息SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'db_test/%';当全文索引创建时就会创建一组辅助索引表，前六个表就是辅助索引表。辅助索引表以 FTS_ 开头，以index_# 结尾，每个辅助索引表的表名都和全文索引所在表的table_id的十六进制值关联。比如db_test/users的table_id是170，170对应的十六进制是0xaa，辅助索引表的表名就用aa作为其表名的一部分，以便和db_test/users表关联。全文索引的index_id也可以通过辅助索引表的表名获取，拿第一个辅助索引表db_test/FTS_00000000000000aa_00000000000000fb_INDEX_1举例，fb就是index_id的十六进制表示，换算成十进制是251，所以index_id=251.可通过以下SQL语句验证index_id：SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE index_id = 251;db_test/users表的table_id=170，通过index_id = 251查询到的table_id也是170，并且索引名称也是我们创建的idx_name，所以这个index_id就是我们创建的全文索引的id。多列索引 多列索引(Multiple-Column Index，又称联合索引、复合索引)顾名思义就是几个列共同组成一个索引。多列索引最多由16个列组成。多列索引遵守最左前缀原则。最左前缀原则就是在查询数据时，以最左边的列为基准进行索引匹配。例如，有个索引mul_index(col1, col2, col3)，在进行索引查询的时候，只有(col1)、(col1, col2)和(col1, col2, col3)这三种组合才能使多列索引mul_index(col1, col2, col3)生效。就是说col1只能在查询时被用到，这个索引就能被用到，索引在创建多列索引时一定要将查询最频繁的列放到最左边。空间索引 MyISAM、InnoDB、NDB和ARCHIVE存储引擎都支持空间索引(Spatial Indexe)，但是要求列必须是POINT和GEOMETRY相关类型。但是，对空间列索引的支持因引擎而异，可根据以下规则使用空间列上的空间和非空间索引。1.空间索引在空间列上有以下特性：只有MyISAM和InnoDB可以使用，如果在创建时指定其他存储引擎会报错索引列必须是NOT NULL不允许使用索引前缀2.非空间索引在空间列上有以下特性：允许用于除ARCHIVE存储引擎之外的任何支持空间列的存储引擎。列值可以为NULL，除非是唯一索引。非空间索引在空间列时，除了列的类型是POINT之外，在创建索引的时候都要指定索引前缀并且索引前缀长度是以字节为单位的。非空间索引在空间列的数据结构取决于存储引擎，目前使用的是BTREE。在InnoDB、MyISAM和 MEMORY存储引擎中允许空间列的值为NULL。空间索引主要用于列类型是地理位置或者坐标之类的列上的，空间索引主要使用的是R-Tree。自适应哈希索引 自适应哈希索引(Adaptive Hash Index)是InnoDB表的优化，可以通过在内存中构造哈希索引来加速使用 = 和 IN 运算符的查找。InnoDB 存储引擎内部自己去监控索引表，如果监控到某个索引频繁使用，那么就认为是热数据，然后内部就会自动创建一个 hash 索引。从某种意义上说，自适应哈希索引在运行时对MySQL进行配置，以利用充足的主存，这样更接近主存数据库的架构。这个特性是由innodb_adaptive_hash_index参数控制的，默认是开启的。可以通过以下命令查看自适应hash索引的使用状况show engine innodb statusstatus字段内容很长，有兴趣的可以自己试试看下，里面有这么一段：-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 195, seg size 197, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 2267, node heap has 0 buffer(s)Hash table size 2267, node heap has 2 buffer(s)Hash table size 2267, node heap has 0 buffer(s)Hash table size 2267, node heap has 1 buffer(s)Hash table size 2267, node heap has 0 buffer(s)Hash table size 2267, node heap has 1 buffer(s)Hash table size 2267, node heap has 0 buffer(s)Hash table size 2267, node heap has 1 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s通过 hash searches: nonhash searches 可以大概了解使用哈希索引后的效率。索引的增删改查 新增索引 新增索引有三种方式：使用create index 语句使用alter 语句在CREATE TABLE的时候创建索引前两种方式都是在创建好表以后再给表新增索引的，第三种是在创建表的同时创建索引CREATE TABLE 创建索引 例如：CREATE TABLE `test` (`id` int NOT NULL AUTO_INCREMENT ,`name` varchar(255) NULL ,PRIMARY KEY (`id`),INDEX `idx` (`name`) )CREATE INDEX 创建索引语法：CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type] ON tbl_name (key_part,...) [index_option] [algorithm_option | lock_option] ...key_part: col_name [(length)] [ASC | DESC]index_option: { KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'}index_type: USING {BTREE | HASH}algorithm_option: ALGORITHM [=] {DEFAULT | INPLACE | COPY}lock_option: LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}带中括号[]的都是可选项，可写可不写，[=]表示等于号可要可不要key_part:col_name [(length)] [ASC | DESC] ：表示列名、索引前缀长度、升序或降序index_type：表示使用BTREE或HASH作为索引的数据结构index_option:索引的可选项，包括索引类型、备注、PARSER、KEY_BLOCK_SIZE 等algorithm_option：算法的选择，可选值为DEFAULT、INPLACE、COPYlock_option：锁的选择，可选值为DEFAULT、NONE、SHARED(共享锁)、EXCLUSIVE(排它锁)示例：CREATE INDEX index_name USING BTREE ON account(amount DESC) COMMENT 'string' ALGORITHM INPLACE LOCK SHARED;ALTER TABLE 创建索引 语法：ALTER TABLE tbl_name ADD {UNIQUE | FULLTEXT | SPATIAL} INDEX index_name(key_part,...) [index_option]index_option: { KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'示例：-- 添加唯一索引ALTER TABLE `account` ADD UNIQUE INDEX `uk` (`amount`) USING BTREE;ALTER TABLE 和 CREATE INDEX 创建索引的区别：ALTER 本身有修改的意思，所以可以对索引进行增删改，而CREATE只能创建索引CREATE不能创建主键，ALTER可以CREATE INDEX 可以指定索引算法ALGORITHM和LOCK，ALTER在添加索引的时候不能指定。修改索引 修改索引是先删除之前的索引，然后重新添加ALTER TABLE tableName DROP INDEX oldIndexName,ADD INDEX indexName(columns ...) USING BTREE;删除索引 删除索引有两种方式：ALTER TABLE tableName DROP INDEX indexName;或DROP INDEX indexName ON tableName;索引查询 SHOW INDEX FROM tableName;索引前缀 对于字符串类型的索引，在创建索引的时候可以指定以索引的前多少个字符作为索引，可以通过使用col_name(length)语法来指定索引前缀长度，这样可以节省空间和查询效率。索引前缀使用范围及注意事项：可以给类型为CHAR、VARCHAR、BINARY和VARBINARY的列指定前缀。如果给类型是BLOB或者TEXT的列创建索引，必须为其指定前缀。此外，BLOB和TEXT类型的列只能在存储引擎是InnoDB、MyISAM和BLACKHOLE的表上建立索引。索引前缀的长度是以字节为单位的。对于非二进制的(CHAR,VARCHAR,TEXT)的字符类型来说，长度指的是字符的长度。对于二进制的(BINARY, VARBINARY, BLOB)字符类型来说，长度指的是字节的长度。使用多字节的字符编码时，在给二进制的字符类型的列设置长度时要考虑这些。索引前缀长度是否支持或者如何支持取决于存储引擎。对于InnoDB引擎来说，索引前缀长度可以最多可以支持767 bits，如果innodb_large_prefix参数开启，最多能支持3072 bits。对于MyISAM引擎来说，索引前缀的长度被限制在1000 bits以内。对于NDB引擎来说，压根就不支持索引前缀。从 MySQL 5.7.17 开始，如果指定的索引前缀超过最大列数据类型大小，CREATE INDEX会按如下方式处理索引：对于非唯一索引，要么发生错误（如果启用了SQL严格模式），要么索引长度减少到列数据类型大小内并产生警告（如果未启用严格SQL模式）。对于唯一索引，无论SQL模式如何都会发生错误，因为减少索引长度可能会导致插入不满足指定唯一性要求的非唯一条目。如果列的前n(n < 列的数据类型长度)个字符不同，使用索引前缀可能不会比使用整个列做索引慢，而且使用索引前缀索引文件更小，可以省更多磁盘空间并且可能会提高插入时的效率。索引的选择性 索引的选择性是指不重复的索引值(也称为基数，cardinality)和数据表的记录总数(N)的比值，取值范围是1/N到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL 在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。创建索引时要选择索引选择性高的值创建索引。比如有一百条数据，重复的行数有10条，那么索引的选择性就是10/100，也就是0.1。索引的选择性可以通过下列计算方式带入计算：SELECT COUNT(DISTINCT col...) / COUNT(*) FROM table_name;索引的代价 索引是把双刃剑，有利有弊。利：方面就是能提高查询效率。弊端主要是两个方面：空间代价：每创建一个就会产生一个索引数据文件占用磁盘空间，索引越多占用的磁盘空间也就越大。时间代价：虽然索引是提高了查询效率，但同时也降低了插入、删除和更新的效率。每次对数据进行增删改操作的同时也是在对索引的更改，索引越多更改时需要花费的时间越多。总结本文只是对索引进行一个简单的介绍。索引是把双刃剑，用的好可以提升系统查询效率，用的不好效率不升反降得不偿失。来源：51cto

 1 引言 大家好，今天与大家一起分享一下 mysql DDL执行方式。  一般来说MySQL分为DDL（定义）和DML（操作）。 DDL：Data Definition Language，即数据定义语言，那相关的定义操作就是DDL，包括：新建、修改、删除等；相关的命令有：CREATE，ALTER，DROP，TRUNCATE截断表内容（开发期，还是挺常用的），COMMENT 为数据字典添加备注。 DML：Data Manipulation Language，即数据操作语言，即处理数据库中数据的操作就是DML，包括：选取，插入，更新，删除等；相关的命令有：SELECT,INSERT,UPDATE,DELETE，还有 LOCK TABLE，以及不常用的CALL – 调用一个PL/SQL或Java子程序，EXPLAIN PLAN – 解析分析数据访问路径。 我们可以认为： CREATE，ALTER ，DROP，TRUNCATE，定义相关的命令就是DDL; SELECT，INSERT，UPDATE，DELETE，操作处理数据的命令就是DML； DDL、DML区别： DML操作是可以手动控制事务的开启、提交和回滚的。 DDL操作是隐性提交的，不能rollback，一定要谨慎哦！ 日常开发我们对一条DML语句较为熟悉，很多开发人员都了解sql的执行过程，比较熟悉，但是DDL是如何执行的呢，大部分开发人员可能不太关心，也认为没必要了解，都交给DBA吧。 其实不然，了解一些能尽量避开一些ddl的坑，那么下面带大家一起了解一下DDL执行的方式，也算抛砖引玉吧。如有错误，还请各位大佬们指正。  2 概述 在MySQL使用过程中，根据业务的需求对表结构进行变更是个普遍的运维操作，这些称为DDL操作。常见的DDL操作有在表上增加新列或给某个列添加索引。  我们常用的易维平台提供了两种方式可执行DDL，包括MySQL原生在线DDL（online DDL）以及一种第三方工具pt-osc。 下图是执行方式的性能对比及说明： 本文将对DDL的执行工具之Online DDL进行简要介绍及分析，pt-osc会专门再进行介绍。3 介绍 MySQL Online DDL 功能从 5.6 版本开始正式引入，发展到现在的 8.0 版本，经历了多次的调整和完善。其实早在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式，但是因为实现的问题，依然会阻塞 INSERT、UPDATE、DELETE 操作，这也是 MySQL 早期版本长期被吐槽的原因之一。  在MySQL 5.6版本以前，最昂贵的数据库操作之一就是执行DDL语句，特别是ALTER语句，因为在修改表时，MySQL会阻塞整个表的读写操作。例如，对表 A 进行 DDL 的具体过程如下： 按照表 A 的定义新建一个表 B 对表 A 加写锁 在表 B 上执行 DDL 指定的操作 将 A 中的数据拷贝到 B 释放 A 的写锁 删除表 A 将表 B 重命名为 A 在以上 2-4 的过程中，如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。此外，由于 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。  如果遇到巨大的表，可能需要几个小时才能执行完成，势必会影响应用程序，因此需要对这些操作进行良好的规划，以避免在高峰时段执行这些更改。对于那些要提供全天候服务（24*7）或维护时间有限的人来说，在大表上执行DDL无疑是一场真正的噩梦。  因此，MySQL官方不断对DDL语句进行增强，自MySQL 5.6 起，开始支持更多的 ALTER TABLE 类型操作来避免数据拷贝，同时支持了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL，即在执行 DDL 期间允许在不中断数据库服务的情况下执行DML(insert、update、delete)。然而并不是所有的DDL操作都支持在线操作。到了 MySQL 5.7，在 5.6 的基础上又增加了一些新的特性，比如：增加了重命名索引支持，支持了数值类型长度的增大和减小，支持了 VARCHAR 类型的在线增大等。但是基本的实现逻辑和限制条件相比 5.6 并没有大的变化。 4 用法 ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的算法模式和 DDL 期间 DML 的锁控制模式。 ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。 如果设置 ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML。另外也可以设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。 LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）。 5 两种算法 第一种 Copy： 按照原表定义创建一个新的临时表； 对原表加写锁（禁止DML，允许select）； 在步骤1 建立的临时表执行 DDL； 将原表中的数据 copy 到临时表； 释放原表的写锁； 将原表删除，并将临时表重命名为原表。 从上可见，采用 copy 方式期间需要锁表，禁止DML，因此是非Online的。比如：删除主键、修改列类型、修改字符集，这些操作会导致行记录格式发生变化（无法通过全量 + 增量实现 Online）。 第二种 Inplace： 在原表上进行更改，不需要生成临时表，不需要进行数据copy的过程。根据是否行记录格式，又可分为两类： rebuild：需要重建表（重新组织聚簇索引）。比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等； no-rebuild：不需要重建表，只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。 对于 rebuild 方式实现 Online 是通过缓存 DDL 期间的 DML，待 DDL 完成之后，将 DML 应用到表上来实现的。例如，执行一个 alter table A engine=InnoDB; 重建表的 DDL 其大致流程如下： 建立一个临时文件，扫描表 A 主键的所有数据页； 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中； 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中； 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件； 用临时文件替换表 A 的数据文件。 说明：  在 copy 数据到新表期间，在原表上是加的 MDL 读锁（允许 DML，禁止 DDL）； 在应用增量期间对原表加 MDL 写锁（禁止 DML 和 DDL）； 根据表 A 重建出来的数据是放在 tmp_file 里的，这个临时文件是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个原地操作，这就是”inplace”名称的来源。 使用Inplace方式执行的DDL，发生错误或被kill时，需要一定时间的回滚期，执行时间越长，回滚时间越长。 使用Copy方式执行的DDL，需要记录过程中的undo和redo日志，同时会消耗buffer pool的资源，效率较低，优点是可以快速停止。 不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行，具体的支持情况可以在（在线 DDL 操作) 中查看。  官网支持列表：6 执行过程 Online DDL主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段。下面将主要介绍ddl执行过程中三个阶段的流程。  1）Prepare阶段：初始化阶段会根据存储引擎、用户指定的操作、用户指定的 ALGORITHM 和 LOCK 计算 DDL 过程中允许的并发量，这个过程中会获取一个 shared metadata lock，用来保护表的结构定义。 创建新的临时frm文件(与InnoDB无关)。 持有EXCLUSIVE-MDL锁，禁止读写。 根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)。假如是Add Index，则选择online-norebuild即INPLACE方式。 更新数据字典的内存对象。 分配row_log对象来记录增量(仅rebuild类型需要)。 生成新的临时ibd文件(仅rebuild类型需要) 。 数据字典上提交事务、释放锁。 注：Row log是一种独占结构，它不是redo log。它以Block的方式管理DML记录的存放，一个Block的大小为由参数innodb_sort_buffer_size控制，默认大小为1M，初始化阶段会申请两个Block。 2）DDL执行阶段：执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL，但 DML 能可以正常执行。 降级EXCLUSIVE-MDL锁，允许读写（copy不可写）。 扫描old_table的聚集索引每一条记录rec。 遍历新表的聚集索引和二级索引，逐一处理。 根据rec构造对应的索引项 将构造索引项插入sort_buffer块排序。 将sort_buffer块更新到新的索引上。 记录ddl执行过程中产生的增量(仅rebuild类型需要) 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)。 重放row_log间产生dml操作append到row_log最后一个Block。 3）Commit阶段：将 shared metadata lock 升级为 exclusive metadata lock，禁止DML，然后删除旧的表定义，提交新的表定义。 当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁。 重做row_log中最后一部分增量。 更新innodb的数据字典表。 提交事务(刷事务的redo日志)。 修改统计信息。 rename临时idb文件，frm文件。 变更完成。 Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以几乎不会阻塞 DML 语句。 不过，在 DDL 执行前或执行时，其他事务可以获取 MDL。由于需要用到 exclusive MDL，所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。  7 踩坑 前面提到 Online DDL 执行过程中需要获取 MDL，MDL (metadata lock) 是 MySQL 5.5 引入的表级锁，在访问一个表的时候会被自动加上，以保证读写的正确性。当对一个表做 DML 操作的时候，加 MDL 读锁；当做 DDL 操作时候，加 MDL 写锁。  为了在大表执行 DDL 的过程中同时保证 DML 能并发执行，前面使用了 ALGORITHM=INPLACE 的 Online DDL，但这里仍然存在死锁的风险，问题就出在 Online DDL 过程中需要 exclusive MDL 的地方。  例如，Session 1 在事务中执行 SELECT 操作，此时会获取 shared MDL。由于是在事务中执行，所以这个 shared MDL 只有在事务结束后才会被释放。 # Session 1> START TRANSACTION;> SELECT * FROM tbl_name;# 正常执行这时 Session 2 想要执行 DML 操作也只需要获取 shared MDL，仍然可以正常执行。# Session 2> SELECT * FROM tbl_name;# 正常执行但如果 Session 3 想执行 DDL 操作就会阻塞，因为此时 Session 1 已经占用了 shared MDL，而 DDL 的执行需要先获取 exclusive MDL，因此无法正常执行。# Session 3> ALTER TABLE tbl_name ADD COLUMN n INT;# 阻塞通过 show processlist 可以看到 ALTER 操作正在等待 MDL。+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+| Id | User | Host | db | Command | Time | State | Info |│----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+| 11 | root | 172.17.0.1:53048 | demo | Query | 3 | Waiting for table metadata lock | alter table ... |+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+由于 exclusive MDL 的获取优先于 shared MDL，后续尝试获取 shared MDL 的操作也将会全部阻塞。# Session 4> SELECT * FROM tbl_name;# 阻塞到这一步，后续无论是 DML 和 DDL 都将阻塞，直到 Session 1 提交或者回滚，Session 1 占用的 shared MDL 被释放，后面的操作才能继续执行。  上面这个问题主要有两个原因： Session 1 中的事务没有及时提交，因此阻塞了 Session 3 的 DDL Session 3 Online DDL 阻塞了后续的 DML 和 DDL 对于问题 1，有些ORM框架默认将用户语句封装成事务执行，如果客户端程序中断退出，还没来得及提交或者回滚事务，就会出现 Session 1 中的情况。那么此时可以在 infomation_schema.innodb_trx 中找出未完成的事务对应的线程，并强制退出。 > SELECT * FROM information_schema.innodb_trx\G*************************** 1. row ***************************trx_id: 421564480355704trx_state: RUNNINGtrx_started: 2022-05-01 014:49:41trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 9trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1136trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1 row in set (0.0025 sec)可以看到 Session 1 正在执行的事务对应的 trx_mysql_thread_id 为 9，然后执行 KILL 9 即可中断 Session 1 中的事务。 对于问题 2，在查询很多的情况下，会导致阻塞的 session 迅速增多，对于这种情况，可以先中断 DDL 操作，防止对服务造成过大的影响。也可以尝试在从库上修改表结构后进行主从切换或者使用 pt-osc 等第三方工具。  8 限制 仅适用于InnoDB（语法上它可以与其他存储引擎一起使用，如MyISAM，但MyISAM只允许algorithm = copy，与传统方法相同）； 无论使用何种锁（NONE，共享或排它），在开始和结束时都需要一个短暂的时间来锁表（排它锁）； 在添加/删除外键时，应该禁用 foreign_key_checks 以避免表复制； 仍然有一些 alter 操作需要 copy 或 lock 表（老方法），有关哪些表更改需要表复制或表锁定，请查看官网； 如果在表上有 ON … CASCADE 或 ON … SET NULL 约束，则在 alter table 语句中不允许LOCK = NONE； Online DDL会被复制到从库（同主库一样，如果 LOCK = NONE，从库也不会加锁），但复制本身将被阻止，因为 alter 在从库以单线程执行，这将导致主从延迟问题。 官方参考资料：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html  9 总结 本次和大家一起了解SQL的DDL、DML及区别，也介绍了Online DDL的执行方式。  目前可用的DDL操作工具包括pt-osc，github的gh-ost，以及MySQL提供的在线修改表结构命令Online DDL。pt-osc和gh-ost均采用拷表方式实现，即创建个空的新表，通过select+insert将旧表中的记录逐次读取并插入到新表中，不同之处在于处理DDL期间业务对表的DML操作。  到了MySQL 8.0 官方也对 DDL 的实现重新进行了设计，其中一个最大的改进是 DDL 操作支持了原子特性。另外，Online DDL 的 ALGORITHM 参数增加了一个新的选项：INSTANT，只需修改数据字典中的元数据，无需拷贝数据也无需重建表，同样也无需加排他 MDL 锁，原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的，也不会阻塞 DML，不过目前8.0的INSTANT使用范围较小，后续再对8.0的INSTANT做详细介绍吧。 来源：51CTO

 GTID作用 主从环境中主库的dump线程可以直接通过GTID定位到需要发送的binary log的位置，而不需要指定binary log的文件名和位置，因而切换极为方便。  GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf)，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一。   对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。  通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。  直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可以直接完成failover的工作。  GTID变量和表 gtid_executed 表 是GTID持久化的一个介质，实例重启后所有的内存信息都会丢失，GTID模块初始化需要读取GTID持久化介质。  gtid_executed变量 表示数据库中执行了哪些GTID，它是一个处于内存中的GTID SET。  gtid_purged变量 表示由于删除binary log，已经丢失的GTID event，它是一个处于内存中的GTID set。搭建从库时，通常需要使用set global gtid_purged命令设置本变量，用于表示这个备份已经执行了哪些gtid操作，手动删除binary log 不会更新这个变量。  gtid_executed变量和gtid_purged变量这两个变量分别表示，数据库执行了哪些GTID操作，又有哪些GTID操作由于删除binary log 已经丢失了。 变量更新时机 主库更新时机（1）gtid_executed变量一定是实时更新的。在order commit的flush阶段生成GTID，在commit阶段才计入。（2）mysql.executed表在binary log切换时更新（3）gtid_purged变量在清理binary log时修改，比如purge binary logs 或者超过expire_logs_days的设置后从库更新时机log_slave_updates关闭（1）从库mysql.executed 未开启log_slave_updates情况下，只能通过实时更新mysql.executed表来保存（2）gtid_executed变量实时更新（3）gtid_purged变量实时更新log_slave_updates打开从库mysql.executed 开启log_slave_updates情况下，更新和主库一模一样。通用修改时机gtid_executed 表gtid_executed 表 在执行reset master ,set global gtid_purged命令时设置本表gtid_executed变量gtid_executed变量 reset aster清空本变量，set global gtid_executed命令时设置本变量mysql启动时初始化设置gtid_purged 变量reset master清空本变量set global gtid_purged 设置本变量mysql启动初始化设置GTID模块初始化流程1、获取到server_uuid2、读取mysql.gtid_executed表，但是该表不包含当前binlog的GTID3、读取binlog，先反向扫描，获取最后一个binlog中包含的最新GITD，然后正向扫描，获取第一个binary log中的lost GTID。4、将只在binlog的GTID加入mysql.gtid_executed表和gtid_executed变量。此时mysql.gtid_executed表和gtid_executed变量也正确了5、初始化gtid_purged，扫描到的lost GTID。开启GTID MySQL 5.6 版本，在my.cnf文件中添加:gtid_mode=on (必选) #开启gtid功能log_bin=log-bin=mysql-bin (必选) #开启binlog二进制日志功能log-slave-updates=1 (必选) #也可以将1写为onenforce-gtid-consistency=1 (必选) #也可以将1写为onMySQL 5.7或更高版本，在my.cnf文件中添加:gtid_mode=on (必选)enforce-gtid-consistency=1 （必选）log_bin=mysql-bin （可选） #高可用切换，最好开启该功能log-slave-updates=1 （可选） #高可用切换，最好打开该功能GTID的缺点- 不支持非事务引擎;- 不支持create table ... select 语句复制(主库直接报错);(原理: 会生成两个sql, 一个是DDL创建表SQL, 一个是insert into 插入数据的sql; 由于DDL会导致自动提交, 所以这个sql至少需要两个GTID, 但是GTID模式下, 只能给这个sql生成一个GTID)- 不允许一个SQL同时更新一个事务引擎表和非事务引擎表;- 在一个复制组中，必须要求统一开启GTID或者是关闭GTID;- 开启GTID需要重启 (mysql5.7除外);- 开启GTID后，就不再使用原来的传统复制方式;- 对于create temporary table 和 drop temporary table语句不支持;- 不支持sql_slave_skip_counter;GTID跳过事务的方法 开启GTID以后，无法使用sql_slave_skip_counter跳过事务，因为主库会把从库缺失的GTID，发送给从库，所以skip是没有用的。 为了提前发现问题，在gtid模式下，直接禁止使用set global sql_slave_skip_counter =x。 正确的做法: 通过set gtid_next= 'aaaa'（'aaaa'为待跳过的事务），然后执行BIGIN; 接着COMMIT产生一个空事务，占据这个GTID，再START SLAVE，会发现下一条事务的GTID已经执行过，就会跳过这个事务了。如果一个GTID已经执行过，再遇到重复的GTID，从库会直接跳过，可看作GTID执行的幂等性。 因为是通过GTID来进行复制的，也需要跳过这个事务从而继续复制，这个事务可以到主上的binlog里面查看：因为不知道找哪个GTID上出错，所以也不知道如何跳过哪个GTID 1、show slave status里的信息里可以找到在执行Master里的POS:1512、通过mysqlbinlog找到了GTID：3、stop slave;4、set session gtid_next='4e659069-3cd8-11e5-9a49-001c4270714e:1'5、begin; commit；6、SET SESSION GTID_NEXT = AUTOMATIC; #把gtid_next设置回来7、start slave; #开启复制1）对于跳过一个错误，找到无法执行事务的编号，比如是2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-10mysql> stop slave;mysql> set gtid_next='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-10';mysql> begin;mysql> commit;mysql> set gtid_next='AUTOMATIC';mysql> start slave; 2）上面方法只能跳过一个事务，那么对于一批如何跳过？在主库执行"show master status"，看主库执行到了哪里，比如：2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33，那么操作如下：mysql> stop slave;mysql> reset master;mysql> set global gtid_purged='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33';mysql> start slave;如何升级成 GTID replication先介绍几个重要GTID_MODE的参数:GTID_MODE = OFF不产生Normal_GTID,只接受来自master的ANONYMOUS_GTID GTID_MODE = OFF_PERMISSIVE不产生Normal_GTID,可以接受来自master的ANONYMOUS_GTID & Normal_GTID GTID_MODE = ON_PERMISSIVE产生Normal_GTID,可以接受来自master的ANONYMOUS_GTID & Normal_GTID GTID_MODE = ON产生Normal_GTID,只接受来自master的Normal_GTID 归纳总结:1)当master产生Normal_GTID的时候,如果slave的gtid_mode(OFF)不能接受Normal_GTID,那么就会报错2)当master产生ANONYMOUS_GTID的时候,如果slave的gtid_mode(ON)不能接受ANONYMOUS_GTID,那么就会报错3)设置auto_position的条件: 当master的gtid_mode=ON时,slave可以为OFF_PERMISSIVE,ON_PERMISSIVE,ON。 除此之外,都不能设置auto_position = on ============================================下面开始说下如何online 升级为GTID模式？ step 1: 每台server执行检查错误日志，直到没有错误出现，才能进行下一步mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN; step 2: 每台server执行mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON; step 3: 每台server执行不用关心一组复制集群的server的执行顺序，只需要保证每个Server都执行了，才能进行下一步mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE; step 4: 每台server执行不用关心一组复制集群的server的执行顺序，只需要保证每个Server都执行了，才能进行下一步mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE; step 5: 在每台server上执行，如果ONGOING_ANONYMOUS_TRANSACTION_COUNT=0就可以不需要一直为0，只要出现过0一次，就okmysql> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; step 6： 确保所有anonymous事务传递到slave上了#master上执行mysql> SHOW MASTER STATUS; #每个slave上执行mysql> SELECT MASTER_POS_WAIT(file, position); 或者，等一段时间，只要不是大的延迟，一般都没问题 step 7: 每台Server上执行mysql> SET @@GLOBAL.GTID_MODE = ON; step 8: 在每台server上将my.cnf中添加好gtid配置gtid_mode=onenforce-gtid-consistency=1log_bin=mysql-binlog-slave-updates=1 step 9: 在从机上通过change master语句进行复制mysql> STOP SLAVE;mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 1;mysql> START SLAVE;来源：51CTO

 慢查询日志是用于记录SQL执行时间超过某个临界值的SQL日志文件，可用于快速定位慢查询，为我们的SQL优化做参考。  具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的SQL语句。  查看是否开启 show variables like '%slow_query_log%'# 本文这里结果如下slow_query_log ONslow_query_log_file DESKTOP-KIHKQLG-slow.log​​slow_query_log_file​​​指的是慢查询日志文件。如果slow_query_log 状态值为OFF，可以使用​​set GLOBAL slow_query_log = on​​来开启，如果想永久生效，那么在MySQL的配置文件中进行配置。[mysqld]slow_query_log=1slow_query_log_file=/var/data/mysql-slow.log#如果不指定日志文件，那么系统会默认一个hostnam-slow.log 查看时间阈值  默认值是10秒，可以根据需求自行调整。 show variables like 'long_query_time';# 临时设置为1 秒，重启失效set GLOBAL long_query_time= 1查询当前慢查询SQL条数show global status like '%Slow_queries%' 慢查询日志格式  需要注意的是，慢查询日志文件里面不止有Query哦，只要执行时间大于我们设置的阈值都会进入。 如下所示是一个慢查询实例，其load了21W条数据。 # Time: 2022-09-14T05:43:57.174825Z# User@Host: root[root] @ localhost [127.0.0.1] Id: 2497# Query_time: 1.697595 Lock_time: 0.000226 Rows_sent: 210001 Rows_examined: 210001SET timestamp=1663134237;/* ApplicationName=DBeaver 7.3.0 - SQLEditor */ select * from tb_sys_user tsu limit 210001; 日志分析工具mysqldumpslow  mysql提供了日志分析工具mysqldumpslow来帮助我们快速定位问题。 [root@VM-24-14-centos ~]# mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time得到返回记录集最多的10个SQLmysqldumpslow -s r -t 10 /var/data/mysql-slow.log得到访问次数最多的10个SQLmysqldumpslow -s c -t 10 /var/data/mysql-slow.log得到按照时间排序的前10条SQL中包含左连接的语句mysqldumpslow -s t -t 10 -g "left join" /var/data/mysql-slow.log 慢查询日志场景应用  慢查询的优化首先要搞明白慢的原因是什么， 是查询条件没有命中索引？是 load了不需要的数据列？还是数据量太大？所以优化也是针对这三个方向来的。  首先分析语句，看看是否load了额外的数据​​，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。  ​​分析语句的执行计划，然后获得其使用索引的情况​​，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。  ​​如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大​​，如果是的话可以进行横向或者纵向的分表。 全局查询日志 其同样可以帮助我们定位SQL问题，通常不建议在生产环境开启。可以在配置文件my.cnf下进行启用：# 开启general_log=1#记录日志文件的路径general_log_file=/var/data/mysql_general_log#输出格式log_output=FILE或者临时开启：set global general_log=1;set global log_output='TABLE'此时SQL语句将会记录到MySQL库的​​mysql.general_log​​表中。来源：51CTO