MySQL

终端操作

#[windows] 启动\停止
net start|stop mysql

#[Linux] 
systemctl restart mysqld

#连接
mysql -h IP -p PORT -u USER -p
mysql -u USER -p

#查看当前数据库
show databases;

#使用
use DATABASES;
show table;

#配置文件
mysql --verbose --help|grep -A 1 'Default options'

Root 外部连接

#MySQL默认只允许root帐户在本地登录

#1.服务器连接入数据库

#2.进入 mysql 数据库
use mysql;

#3.查看用户user表中的约束
SELECT `Host`,`User` FROM user;

#4.更改用户名的 Host 约束
UPDATE user SET `Host` = '%' WHERE `User` = 'root';

#5.强制更新权限
flush privileges;

SQL分类

DDL

数据库操作

创建数据库：用于创建新的数据库。

utf8mb4：Unicode字符集，支持包括Emoji等在内的各种字符。

utf8mb4_general_ci：Unicode字符集的不区分大小写排序规则。

CREATE DATABASE database_name;

/*
字符集:utf8mb4
排序规则:COLLATE
*/
CREATE DATABASE mydatabase
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

删除数据库：用于删除现有的数据库。

DROP DATABASE database_name;

备份数据库：使用mysqldump命令来创建数据库备份。将mydatabase数据库导出为一个SQL文件，以进行备份。

mysqldump -u username -p mydatabase > mydatabase_backup.sql

还原数据库：使用mysql命令来还原数据库。将之前备份的SQL文件中的数据还原到mydatabase数据库中。

mysql -u username -p mydatabase < mydatabase_backup.sql

查看数据库列表：查看当前MySQL服务器上的所有数据库。

SHOW DATABASES;

切换数据库：选择要在其上执行操作的数据库。

USE mydatabase;

查看数据库大小：查看数据库的大小信息。

SELECT table_schema "Database Name",
       sum(data_length + index_length) / 1024 / 1024 "Database Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;

表操作

CREATE TABLE mytable (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '唯一标识',
    name VARCHAR(255) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    email VARCHAR(255) UNIQUE COMMENT '电子邮件地址',
    registration_date DATE COMMENT '注册日期',
    is_active TINYINT(1) DEFAULT 1 COMMENT '是否活跃 (0=否, 1=是)'
    sex varcahr(1) ENUM('男','女') NOT NULL DEFAULT '男';
);

更改表结构

添加一列：

ALTER TABLE table_name
ADD new_column_name VARCHAR(50);

ALTER TABLE 表名
ADD sex ENUM('男', '女') NOT NULL DEFAULT '男';

删除一列

ALTER TABLE table_name
DROP COLUMN column_name;

修改 MODIFY

增加修改的属性就行，不更改的不用加上，mysql默认不会去除

修改列的数据类型

ALTER TABLE tb_user
MODIFY COLUMN age INT;

-- 修改为自增
ALTER TABLE table_name
MODIFY COLUMN column_name INT AUTO_INCREMENT;

添加默认值：

修改 tb_user 表格中的 createtime 列，将其数据类型修改为 TIMESTAMP，并将默认值设置为当前日期和时间。

ALTER TABLE tb_user
MODIFY COLUMN createtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

修改列名

ALTER TABLE tb_user
CHANGE COLUMN old_column_name new_column_name 数据类型;

添加主键

ALTER TABLE table_name
ADD CONSTRAINT 主键约束名称 PRIMARY KEY (column_name);

添加外键

ALTER TABLE table_name
ADD CONSTRAINT 外键约束名称 FOREIGN KEY (table_column_name)
REFERENCES orther_table_name(orther_table_name_column);

添加索引

CREATE INDEX index_name
ON table_name (column_name);

删除主键约束

从表格 table_name 中删除名为 pk_constraint_name 的主键约束

ALTER TABLE table_name
DROP CONSTRAINT pk_constraint_name;

DML

查询

基础查询

查询数据：

SELECT * FROM students WHERE age >= 21;

排序 ORDER BY

-- ASC升序
-- DESC降序
SELECT * FROM tb_user ORDER BY age ASC
SELECT * FROM tb_user ORDER BY age ASC , phone DESC  -- age相同则按phone降序排

分组 GROUP BY

分组后再进行过滤使用having

-- 分组
select 显示名 ，数量 from table group by 要分组的字段名	-- 根据性别分组，统计男性和女性
SELECT gender, COUNT(*) FROM tb_user GROUP BY gender

-- 3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress,count(*)from emp where age < 45 group by workaddress having count(*)>=3;

分页 LIMIT

-- 分页
SELECT * FROM tb_user LIMIT 起始,数量
SELECT * FROM tb_user LIMIT 5,5

子查询

标量子查询

子查询的结果就一个值，可以进行对数据进行操作

列子查询

SELECT * FROM student s
WHERE s.id IN (
    SELECT studentid
    FROM student_course sc
    WHERE sc.courseid IN (
        SELECT id
        FROM course c
        WHERE c.name = 'MySQL'
    )
);

行子查询

select * from emp where salary =12500 and managerid = 1

select * from emp where (salary,managerid) = (select salary,managerid from emp where name ='辰')

表查询

-- 返回 和 鹿杖客，宋远桥 相同job和salary的信息，满足一即可
select * from emp where (job,salary) in (select job,salary from emp where name='鹿杖客'or name='宋远桥')；

select e.*,d.*from (select from emp where entrydate '2006-01-01')e left join dept d on e.dept_id = d.id

内部子查询

select 
(
select distinct Salary
from Employee
order by Salary desc
limit 1,1
)
as SecondHighestSalary

内连接

隐示内连接

select a.name,b.name from A a,B b where a.dept_id  = b.id;

显式内连接（INNER JOIN * ON）：获取两个表格之间匹配的行。

JOIN 是 SQL 中用于连接多个表格的关键字，允许您检索来自不同表格的相关数据。

-- INNER JOIN 表 ON 条件
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

-- INNER可省略
SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

USING

USING关键字用于指定连接列为customer_id，MySQL将自动连接这两个表中具有相同列名的行。

SELECT *
FROM orders
JOIN customers
USING (customer_id);

外连接

左外连接（LEFT JOIN）【*】：获取左边表格中的所有行和右边表格中匹配的行。

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

右外连接（RIGHT JOIN）：获取右边表格中的所有行和左边表格中匹配的行。

SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

全外连接（FULL OUTER JOIN）：获取两个表格中的所有行，如果没有匹配的行，则填充 NULL 值。

SELECT customers.customer_name, orders.order_id
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

自连接

select a.name,b.name from emp a,emp b where a.madagerid = b.id

select a.name '员工'，b.name '领导' from emp a left join emp b on a.managerid=b.id;

联合查询

-- 查询
-- all 是合并，会存在重复元素
select from emp where salary > 5000
union all
select from emp where age < 50;

-- 去除all为 去重
select from emp where salary > 5000
union
select from emp where age < 50;

更新

插入数据

INSERT INTO students (student_id, first_name, last_name, age)
VALUES (1, 'John', 'Doe', 20);

INSERT INTO students (student_id, first_name, last_name, age)
VALUES (2, 'Jane', 'Smith', 22);

更新数据：

UPDATE students
SET age = 23
WHERE student_id = 1;

删除数据：

DELETE FROM students
WHERE student_id = 2;

删除

删除表：

DROP TABLE students;

别名

as 和直接使用 “” 互斥，不能同时使用

别名不需要双引号

SELECT column_name AS alias_name FROM table_name;

“ “

不需要as

SELECT column_name "alias_name" FROM table_name;

DCL

锁

FOR UPDATE

写锁：排它锁
查询时锁定禁止刷新
FOR UPDATE 是 SQL 查询中的一个子句，用于在事务中锁定查询结果的行，以确保其他会话无法同时修改这些行的数据。它通常与 SELECT 语句一起使用，以实现事务的隔离性和并发控制。

SELECT * FROM your_table WHERE your_condition FOR UPDATE;

lock in share mode

读锁：共享锁
FOR SHARE 选项用于共享锁定，它允许多个事务同时读取被锁定的行，但不允许写入。这对于并发读取数据的场景非常有用，因为多个事务可以同时读取相同的数据，而不会产生冲突。

-- 使用 FOR SHARE 进行共享锁定
SELECT * FROM your_table WHERE your_condition lock in share mode

FOR KEY SHARE

FOR KEY SHARE 选项类似于 FOR SHARE，但它更加特定于索引键的共享锁定。它允许多个事务同时读取被锁定的行，只要它们使用相同的索引键进行访问。这也对于并发读取数据的场景非常有用，因为不同的事务可以使用不同的索引键进行读取，而不会产生冲突。

-- 使用 FOR KEY SHARE 进行索引键的共享锁定
SELECT * FROM your_table WHERE your_condition FOR KEY SHARE;

事务

四大特性

默认自动提交

启动事务：使用数据库支持的事务控制语句来启动一个新的事务。在大多数数据库中，使用 BEGIN 或 START TRANSACTION 语句启动事务。

BEGIN; -- 或者 START TRANSACTION;

执行数据库操作：在事务内执行一系列数据库操作，例如插入、更新或删除数据。这些操作被视为一个原子操作单元，只有在事务成功提交后才会对数据库产生影响。

-- 数据库操作
INSERT INTO employees (employee_name, salary) VALUES ('Alice', 50000);
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

提交事务：如果所有的操作都成功，可以使用 COMMIT 语句来提交事务，将所有的更改保存到数据库中。

提交事务会将所有的操作永久应用到数据库，事务结束。

COMMIT;

回滚事务：如果在事务执行过程中发生错误或事务不符合要求，可以使用 ROLLBACK 语句回滚事务，将数据库恢复到事务开始前的状态。

回滚操作会撤销事务中的所有更改，将数据库恢复到事务开始的状态。

ROLLBACK;

并发事务问题

脏读：A事务对数据进行了修改，但还没有提交之前B事务也对该数据进行了读取，虽然获取到已修改的值，但B事务还没有提交，这就造成了脏读现象。【事务未提交缺可获得变更值】
不可重复读：A事务中，第一次成功读取了数据，读取完后B事务进入，把这个数据进行了修改操作并提交事务，导致A事务中进行再次读取时，发现和第一次读取的数据不一致，造成了不可重复读现象 –【同一事务查询同条件出现不同数据】
幻读：A事务查询后无数据，准备插入，但准备插入期间被B事务插入了数据并提交事务，导致A事务插入失败，但曾经读取该数据反馈不存在，造成了幻读现象

事务隔离级别

Mysql默认事务隔离级别：Repeatable Read
Oracel默认：Read committed

【1】Read Uncommitted(读未提交)：三大问题均会出现 (性能Max，安全Min)

【2】Read committed(读已提交)：解决脏读

【3】Repeatable Read(可重复读)：会出现幻读问题

【4】Serializable（串行化）：均解决 隔离级别Max，性能Min

session：会话级别，代表针对当前客户端窗口有效
global：针对所有客户端的窗口有效

-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION

-- 设置事务隔离级别  -- 读方
set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

事务原理

redo log

MVVC[*]

多版本并发控制

当前读：在事务中，设置为类似脏读，可读取到其他事务过程中提交的数据==最新数据

隐藏字段

redo log 重做日志

当刷新缓冲池脏页数据到磁盘时，发生错误时，使用redolog进行数据恢复磁盘的数据

undo log

readview

分析

RC读已提交事务隔离下

RR可重复读

总结：

视图

视图是一种虚拟的表格，它基于一个或多个表格的查询结果，允许用户以类似于查询实际表格的方式访问数据

创建视图：

-- 创建视图
CREATE VIEW student_courses AS
SELECT students.first_name, students.last_name, courses.course_name
FROM students
INNER JOIN student_course_relationship ON students.student_id = student_course_relationship.student_id
INNER JOIN courses ON student_course_relationship.course_id = courses.course_id;

查询视图

-- 查询视图
SELECT * FROM student_courses;

表约束

唯一约束（UNIQUE Constraint）：确保列中的所有值都是唯一的。

CREATE TABLE mytable (
    email VARCHAR(255) UNIQUE,
);

默认值约束（DEFAULT Constraint）：指定在插入新记录时，如果没有提供值，则将使用默认值。

CREATE TABLE mytable (
    age INT DEFAULT 25,
);

NOT NULL约束：确保列中的值不为空。

CREATE TABLE mytable (
    username VARCHAR(50) NOT NULL,
);

主键约束（PRIMARY KEY Constraint）：唯一标识表中的每一行记录，同时确保列中的值都是唯一的。

CREATE TABLE mytable (
    id INT PRIMARY KEY,
);

外键约束（FOREIGN KEY Constraint）：用于建立表与其他表之间的关系，确保参照完整性。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

检查约束（CHECK Constraint）：允许您指定一个条件，确保插入或更新的数据满足条件。

CREATE TABLE mytable (
    gender VARCHAR(10) CHECK (gender IN ('男', '女')),
);

唯一组合约束（UNIQUE Constraint on Multiple Columns）：确保多列组合的值是唯一的。

CREATE TABLE mytable (
    username VARCHAR(50),
    email VARCHAR(255),
    UNIQUE (username, email)
);

常见函数

字符串函数：

CONCAT()：连接两个或多个字符串。
SUBSTRING()：返回子字符串。
UPPER()：将字符串转换为大写。
LOWER()：将字符串转换为小写。
LENGTH()：返回字符串的长度。
TRIM()：删除字符串两侧的空格。
REPLACE()：替换字符串中的子字符串。
CHAR_LENGTH()：返回字符数，而不是字节数。
DATE_FORMAT：将日期时间数据转换为字符串类型 `DATE_FORMAT(bid.bid_time, “%Y-%m-%d %H:%i:%s”) as bidTime

-- CONCAT() 函数
SELECT CONCAT('Hello, ', 'World') AS concatenated;

-- UPPER() 函数
SELECT UPPER('This is uppercase.') AS uppercase_text;

-- LENGTH() 函数
SELECT LENGTH('Hello') AS length_of_string;

-- REPLACE() 函数
SELECT REPLACE('Hello, world', 'world', 'there') AS replaced_string;

数学函数：

ROUND()：四舍五入到指定的小数位数。
CEIL()：向上取整。
FLOOR()：向下取整。
ABS()：返回绝对值。
POWER()：计算幂。
SQRT()：计算平方根。
mod(x,y):x/y

-- ROUND() 函数
SELECT ROUND(5.678, 2) AS rounded_number;

-- ABS() 函数
SELECT ABS(-10) AS absolute_value;

-- POWER() 函数
SELECT POWER(2, 3) AS result;

-- SQRT() 函数
SELECT SQRT(16) AS square_root;

日期和时间函数：

NOW()：返回当前日期和时间。
DATE()：提取日期部分。
TIME()：提取时间部分。
YEAR()：提取年份。
MONTH()：提取月份。
DAY()：提取日。
HOUR()：提取小时。
MINUTE()：提取分钟。
SECOND()：提取秒。
DATEDIFF()：计算日期之间的天数差。

-- NOW() 函数
SELECT NOW() AS current_datetime;

-- DATE() 函数
SELECT DATE('2023-10-08 14:30:00') AS date_part;

-- YEAR() 函数
SELECT YEAR('2023-10-08') AS year;

-- HOUR() 函数
SELECT HOUR('14:30:00') AS hour;

聚合函数：

COUNT()：计算行数。
SUM()：计算总和。
AVG()：计算平均值。
MIN()：查找最小值。
MAX()：查找最大值。

-- COUNT() 函数
SELECT COUNT(*) AS total_sales FROM sales;

-- SUM() 函数
SELECT SUM(sales_amount) AS total_amount FROM sales;

-- AVG() 函数
SELECT AVG(sales_amount) AS avg_amount FROM sales;

-- MIN() 函数
SELECT MIN(sales_amount) AS min_amount FROM sales;

-- MAX() 函数
SELECT MAX(sales_amount) AS max_amount FROM sales;

存储引擎

查看当前表的存储引擎

默认InnoDB，当前自增值AUTO_INCREMENT

show create table b_bid_info

-- 输出
ENGINE=InnoDB AUTO_INCREMENT=3300 DEFAULT CHARSET=utf8 COMMENT='投资记录表'

创建表，指定存储引擎

InnoDB

索引

概述

索引结构

数据结构可视化
默认B+tree

B-Tree

中间元素向上分裂，如图为4key，最多情况下，再插入一个数据，中间元素会向上裂变，五个中的中间元素向上裂变，左边的为左边的树，右边的为右边的树

5阶对应一个节点最多4个元素，各元素区间可指向一个key==5个key

B+Tree

数据只存在叶子节点

B+Tree【MySQL】

为什么InnoDB存储引擎选择使用B+tree索引结构？

相对于二叉树，层级更少，搜索效率高

相对于二叉树，层级更少，搜索效率高；对于B-tree,无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

聚集索引：叶子节点挂载的是行数据

二级索引：叶子节点挂载的是数据的id

回表查询

例如也为name创建了索引

先走二级索引查找数据，获取id，再走聚集索引查询行数据

思考

索引语法

当建表的时候，为一个列或一组列设置唯一约束（UNIQUE constraint）时，MySQL会自动创建一个唯一索引来确保这些列中的值是唯一的。

添加索引： CREATE INDEX 语句:

-- 常规索引
CREATE INDEX index_name ON table_Name;

-- 联合索引  （注意字段顺序）
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status)

-- 唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

-- 全文索引
CREATE FULLTEXT INDEX index_name ON table_name (column1, column2, ...);

查看索引 SHOW INDEX 语句:

SHOW INDEX FROM table_name

删除索引： DROP INDEX 语句

DROP INDEX index_name ON table_name;

SQL性能分析

查看当前数据库的操作次数

-- 7个_占位符
SHOW GLOBAL STATUS LIKE 'Com_______'

1.慢查询日志

-- 慢查询开启状态查看
SHOW VARIABLES LIKE 'slow_query_log'

2.profile详情

SELECT @@have_profiling
SET profiling =1

3.explain执行计划【*】

explain + Sql

最左前缀法制

包含索引最左侧的列（第一列）

联合索引

例如下列联合索引，字段已经有次序，保证左侧字段要存在(1)(1,2)(1,2,3)，不能跳（1，3）(2,3)，sql中的顺序可以打乱

使用范围查询(> \ <)会使索引部分失效 == 》使用 >= \ <=

索引失效

使用范围查询(> \ <)会使索引部分失效 == 》使用 >= \ <=

SQL提示

建议mysql使用我的提示：当一列存在多个索引(单列索引、联合使用。。。)，查询的时候会评估随机选用一个索引
多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

覆盖索引

尽量一次性在一个索引中找到全部需要的数据，否则需要回表查询数据，性能降低
select * 会触发回表查询，除非全部字段增加联合索引

前缀索引

当列为 长字符串、大文本 的时候要建立索引，使用前缀索引

求选择性：查找的列的多少位（极限最小位）的选择性最高，
等于求不重复，当基数和总记录数相等=1的时候，即可获取这个前缀当索引，查找索引可以精准定位到该行数据
distinct 去重

-- 选择裁取前几位的选择性最高	1,4->1.00  1,3->0.9     --> 前4位已经不存在重复的了
SELECT COUNT(DISTINCT SUBSTRING(email,1,4))/COUNT(*) FROM tb_user

-- 创建前缀索引
CREATE INDEX 索引名称 ON 表(列(前缀长度))
CREATE INDEX idx_emial_5 ON tb_user(email(4))

单例、联合索引

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

索引设计

SQL优化

插入数据

主键优化

ORDER BY优化

索引默认是升序

GROUP BY优化

-- 分组
SELECT profession, COUNT(*) FROM tb_user GROUP BY profession  -- 创建联合索引包含 profession

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的。

LIMIT优化

LIMIT
避免回表查询，在分页查询的时候尽量走索引

SELECT id FROM  tb_user ORDER BY id LIMIT 5,5   -- id 为聚集索引

然后使用联表查询，把上面查询出来的id识别为一个表

SELECT a.* FROM tb_user a ,(SELECT id FROM  tb_user ORDER BY id LIMIT 5,5) b WHERE a.id = b.id

Count 优化

使用Redis对表进行计数

count(1) ==》查询返回的所有记录都放1进入，再在服务层统计

Update 优化

当判断条件的列没有索引的话，会实现表锁
判断条件有索引的话 == 》行锁

锁

全局锁

加锁

FLUSH TABLE WITH READ LOCK

备份 – cmd

mysqldump -hlocalhost -uroot -p123456 数据库>备份位置
mysqldump -hlocalhost -uroot -p123456 mysqlc>D:/mysqlc.sql

解锁

unlock tables

不上锁进行备份(快照)

表级锁

表锁需要主动操作去加，而元数据锁和意向锁是自动添加加

表锁

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

读锁 – 各端都可进行读操作
写锁（独占锁，排他锁）：读取客户端可读和写，其他客户端既不可读，也不可写

元数据锁

表结构：避免read和write其间，第三端进行alter对表结构进行更改
在一端中，开启事务，事务中进行了查询操作，自动生成SHARED_READ锁，没提交，当其他端进行对这个表进行结构修改时，产生EXCLUSIVE锁，和其他MDL锁互斥，会被阻塞直到那端的事务提交

-- 查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks

意向锁

问题：在存在行锁的情况下，客户端2是不可以增加表锁的，所以增加表锁的时候，逐行遍历判断有没有行锁，判断是什么类型行锁，效率比较低

事务中，对表进行DDL语句操作的时候，给表加上意向锁，所以DML语句进入的时候直接判断意向锁类型即可，不用判断行锁，如果意向锁和表锁兼容 ==》加表锁，不兼容 == 》阻塞

查看意向锁

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

行锁

多个事务中，兼容的锁可以同时加上，不兼容的阻塞

间隙锁/临键锁

记录锁：record lock,即锁住一条记录
间隙锁（GRAP）:锁的是两个数据的间隙，即锁定一个区间，不包含数据记录
临键锁（S）：记录锁+间隙锁锁定的区间，左开右闭，包含数据记录

1、即A事务对数据进行操作，而数据不存在，例如8，在已有的5和9之间，则它会锁住（5，9 ]，B事务插入7，会被阻塞，防止幻读（预防A查询发现不存在采取插入期间被B事务先插入导致插入失败）
2、例如查询18且加上共享锁，此时锁上的不只有18，会把 18-29的间隙和16-18的间隙加锁，

日志

二进制日志

二进制日志( BINLOG )记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言:增删改)语句(不包括数据查询(SELECT、SHOW)语句)

灾难时的数据恢复；
MySQL的主从复制
默认二进制日志开启

#开启binlog   #1.在配置文件中添加以下内容

# 开启 Binlog
log_bin = mysql-bin
#删除超出这个变量保留期之前的全部日志被删除
expire_logs_days = 7
# 指定一个集群内的 MySQL 服务器 ID，如果做数据库集群那么必须全局唯一，一般来说不推荐 指定 server_id 等于 1。
server_id = 1
# Binlog 的日志模式
binlog_format = ROW
# 控制对哪些数据库进行收集，如果不配置，默认全部数据库都会进行日志收集
binlog_do_db = xxxDbName

#查看状态
show variables like '%log_bin%'
#输出
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin '(二进制日志开启状态)'    | ON                               |
| log_bin_basename '(存放位置)'    | /www/server/mysql/mysql-bin       |
| log_bin_index  '(日志索引文件)'  | /www/server/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+

#进入对应的文件夹
cd /www/server/data

#查看binlog日志索引文件，里面记录了关联了全部二进制文件
cat mysql-bin.index

格式

1)statement:语句级，binlog会记录每次一执行写操作的语句。相对row模式节省空间，但是可能产生不一致性，比如“update tt set create_date=now0”,如果用binlog日志进行恢复，由于执行时间不同可能产生的数据就不同。
优点：节省空间。缺点：有可能造成数据不一致。

2)row:行级，binlog会记录每次操作后每行记录的变化。~优点：保持数据的绝对一致性。因为不管是什么，引用了什么函数，他只记录执行后的效果。缺点：占用较大空间。

3)mixed:statement的升级版，一定程度上解决了，因为一些情况而造成的statement模式不一致问题，默认还是statement,,在某些情况下譬如：当函数中包含UUID0时；包含AUTO_INCREMENT字段的表被更新时：执行INSERT DELAYED语句时；用UDF时；会按照ROW的方式进行处理

#查看当前日志格式
show variables like '%binlog_format%';

#输出
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

#更改日志格式

查看

mysqlbinlog -d logfilename

解决无法找到 mysqlbinlog 问题（终端默认会去 /usr/local/bin 寻找mysqlbinlog 日志解析工具）：
#1.找到安装mysql目录下的bin里面的 mysqlbinlong，复制路径

#2.进入到要映射命令的文件夹
cd /usr/local/bin

#3. 把mysqlbinlog安装目录/www/server/mysql/bin/mysqlbinlog 映射到/usr/local/bin目录下
ln -s /www/server/mysql/bin/mysqlbinlog mysqlbinlog

删除

reset master

purge master logs to 'binlog.***'

purge master logs before 'yyy-mm-dd hh24:mi:ss'

主从复制

(1) Master的更新事件(update、insert、delete)会按照顺序写入bin-log中。当Slave连接到Master的后,Master机器会为Slave开启binlog dump线程,该线程会去读取bin-log日志

(2) Slave连接到Master后,Slave库有一个I/O线程通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。

(3) Slave还有一个 SQL线程,实时监控 relay-log日志内容是否有更新,解析文件中的SQL语句,在Slave数据库中去执行。

一、过程概述

(1) 既然是要把事件记录到bin-log日志，那么对于Master就必须开启bin-log功能。

(2) 整个Mysql主从复制一共开启了3个线程。Master开启 IO线程，Slave开启 IO线程和 SQL线程。

(3) 这点也很重要那就是Master和Slave交互的时候，记住这里是Slave去请求Master,而不是Master主动推给Slave。Slave通过IO线程

连接Master后发起请求,Master服务器收到Slave IO线程发来的日志请求信息，io线程去将bin-log内容返回给slave IO线程。

二、同步方式

(1)异步复制

MySQL主从同步 默认是异步复制的。就是上面三步中,只有第一步是同步的(也就是Mater写入bin log日志),就是主库写入binlog日志后即可成功返回客户端，无须等待binlog日志传递给从库的过程。Master 不关心 Slave 的数据有没有写入成功。因此如果Master和Slave之间有网络延迟，就会造成暂时的数据不一致的现象；如果Master出故障，而数据还没有复制过去，则会造成数据丢失；但也有好处，效率较其他两种复制方式最高。

（2)同步复制

对于同步复制而言，Master主机将事件发送给Slave主机后会触发一个等待，直到所有Slave节点（如果有多个Slave）返回数据复制成功的信息给Master。这种复制方式最安全，但是同时，效率也是最差的。

（3)半同步复制

对于半同步复制而言，Master主机将事件发送给Slave主机后会触发一个等待，直到其中一个Slave节点（如果有多个Slave）返回数据复制成功的信息给Master。由此增强了数据的一致性，但是因为Master主机的确认开销，会损耗一部分的性能；另外，半同步复制除了不需要等待所有Slave主机确认事件的接收外，半同步数据复制并不要求那些事件完全地执行，因此，仍有可能看到在Slave主机上数据复制延迟的发生，如果因为网络延迟等原因造成Slave迟迟没有返回复制成功的信息，超过了Master设置的超时时长，半同步复制就降级为异步复制方式，而后继续数据复制。

三、同步延时

Mysql默认采用的异步操作，因为它的效率明显是最高的。因为只要写入bin log后事物就结束返回成功了。但由于从库从主库异步拷贝日志以及串行执行 SQL 的特点，所以从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。这就是主从同步延时问题。

1、查看主从延迟时间

通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：

mysql> show slave status\G;
    // 状态一
    Seconds_Behind_Master: NULL
    // 状态二
    Seconds_Behind_Master: 0
    // 状态三
    Seconds_Behind_Master: 79

2、影响延迟因素

1）主节点如果执行一个很大的事务，那么就会对主从延迟产生较大的影响

2）网络延迟，日志较大，slave数量过多

3）主上多线程写入，从节点只有单线程同步

4）机器性能问题，从节点是否使用了“烂机器”

5）锁冲突问题也可能导致从机的SQL线程执行慢

3、优化主从复制延迟

这个没有说去完全解决，要想解决那么就只能采用同步复制策略。不过，一般不建议使用这种同步模式。显而易见，如果写操作必须等待更新同步完成，肯定会极大地影响性能，除非不在乎性能。

1）大事务：将大事务分为小事务，分批更新数据

2）减少Slave的数量，不要超过5个，减少单次事务的大小

3）MySQL 5.7之后，可以使用多线程复制，使用MGR复制架构

4）在磁盘、raid卡、调度策略有问题的情况下可能会出现单个IO延迟很高的情况，可用iostat命令查看DB数据盘的IO情况，再进一步判断

5）针对锁问题可以通过抓去processlist以及查看information_schema下面和锁以及事务相关的表来查看

常用

IFNULL

IFNULL 函数用于处理列中可能为 NULL 的值。如果为 NULL，它将被替换为默认值。

SELECT P1.product_id, IFNULL(P1.new_price, 10) AS price, P1.change_date

case

select
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
from seat,(select count(*) as counts from seat) as seat_count
order by id asc


update Salary 
set 
 sex = case sex
    when 'm' then 'f'
    ese 'm'
    end;