《从基础到进阶：MySQL 核心知识体系全解析（涵盖语言分类 / 数据类型 / 事务 / 索引等 10 大核心模块）》全文30000+字

SQL语言分类

分类	命令
DDL（数据库定义语言）	create：创建  drop：删除  alter：修改  rename：重命名  truncate：截断
DML（数据操作语言）	insert：插入  delete：删除  update：更新
DCL（数据库控制语言）	grant：授权  revoke：回收权力  commit：提交事务  rollback：回滚事务
DQL（数据查询语言）	select：查询  from：指定SELECT语句查询及与查询相关的表或视图  where：提取那些满足指定条件的记录
TCL（事务控制语言）	commit：提交  rollback：回滚  savapoint：创建保存点

DDL语言 

        DDL（Data Definition Language）是数据定义语言，它用于定义或改变数据库或表的结构等初始化工作上。通常，包括数据类型，表直接的关系以及数据库中的约束，索引，视图，存储过程，触发器等。

        DDL命令通常用于创建数据库模式，例如CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMIT等。

DDL常见命令及使用

        CREATE：在MySQL中CREATE可以进行创建数据库，数据表

        create创建数据库book

  create  datebase          book;
-- 创建   数据库（关键字）  数据库名

        create创建数据表：创建图书表，包含 id 编号，bName 图书名，price 价格，authorld 作者编号，publishDate 出版日期。

create table books(
id int ,
bName varchar(20),
price int,
authold int,
publishDate datetime
)

        我们通过观察可以发现，CREATE在进行数据库和数据表的定义操作时，无法对数据表中数据进行操作，符合DDL语言定义， 用于定义或改变数据库或表的结构。

        ALTER：ALTER命令用于修改数据库、表和索引等对象的结构。ALTER命令允许你添加、修改或删除数据库对象，并且可以用于更改表的列定义，添加约束、创建和删除索引等操作。

        用alter将表名books改为books1

alter table books rename books1;

        用alter将id改为ID

alter table books1 change id ID int;

        用alter添加一列adda

alter table books1 add adda double;

        用alter删除adda列

alter table books1 drop adda;

        我们可以发现，ALTER命令可以与许多关键字搭配从而对数据库或数据表进行操作，而不是特地的作用于数据表中的数据，符合DDL语言的定义，用于定义或改变数据库或表的结构。

         DROP与TRUNCATE

         在MySQL中进行删除操作的常用关键字有DROP,TRUNCATE,DELECT。其中DROP与TRUNCATE属于DDL语言，而DELECT属于DML语言。它们虽然都可以进行删除操作，但是原理不同，且始终符合其从属的语言类型。

        （为方便进行删除操作，先插入部分数据，各位可以按照本文代码顺序进行练习。）

INSERT INTO books1 VALUES 
(001,'子鼠',10,123,20000101),
(002,'丑牛',20,234,20010202),
(003,'寅虎',30,345,20020303),
(004,'卯兔',40,456,20030404);

        用DELETE进行删除操作，删除序号为001的数据

delete from books1 where id=001;

        让我们从原理层面对该语句进行解释，DELETE作为DML语言进行删除操作时，作用对象为数据表中的数据，符合DML语言定义，对数据表中的数据进行操作。而DELETE在进行删除数据表中数据时，并不是真正将数据完全删除，虽然删除后我们无法再在表中查询到该数据，但是该数据仍然存在于日志中，当我们进行回滚操作时被删除语句仍然可以被恢复。

        用TRUNCATE删除剩余所有数据

truncate table books1;

         我们再从原理层面进行解释该语句，当我们使用truncate进行删除操作时，我们再次查询表发现数据全部被删除，好像truncate虽然是DDL语言但是作用对象仍然是数据表中的数据，但其实，truncate进行删除操作的本质是新建一个一模一样的表结构，并把原表删除，所以truncate仍然是对表结构进行的操作，是DDL语言，并且truncate删除彻底，无法进行回滚操作。

        用DROP将表进行删除

drop table books1;

        当drop将表进行删除，结合之前alter配合drop对列进行删除不难发现，drop的作用对象是数据库或数据表，而不是数据表中的数据，是DDL语言。

        delete与truncate区别 

         1.delete属于DML语言，truncate属于DDL语言

         2.delete能删除表中或基于真实表创建的视图中的数据，truncate只能删除 表中数据，无法应用在视图上

         3.delete删除表中数据时，可以通过where关键字进行选择性删除，truncate不能使用where关键字进行选择性删除

         4.delete删除表中数据时，涉及到事务处理：回退（缓存）、提交、撤退

            truncate删除数据时，不涉及缓存事务处理

         5.delete：由于 delete 删除数据时要考虑事务管理（缓存、回退机制、日志记录），所以当删除大批量数据时，速度慢，效率低，甚至达不到删除的目的
            truncate：删除大批量数据时，速度快、效率高、但无法撤销

         6.delete在进行删除操作时，可能激活触发器的处理，truncate不存在激活触发器处理

         7.delete可以运用SQL语句，按照业务逻辑，按照一定先后顺序删除相关表中结构，

            truncate不能删除对于由foreigh key约束引用的表，不能删除该表中的数据

         8.在删除有自增长主键的数据时，delete删除后，新插入数据的自增长主键数据会按照原来数据+1 ， truncate则用0开始 （例：id为自增长主键，数据最大值id=15，删除后新插入数据，用delete删除则新数据从16开始，而truncate从0开始）

DML语言    

        DML（Data Manipulation Language）是数据操作语言，用于管理和检索数据库中的数据。适用于对数据库中的数据进行一些简单操作，比如增删改查表中的数据。

        DML命令用于处理表中的记录，例如INSERT(插入)、UPDATE（更新）、SELECT（查询）、DELETE（删除）等。这些命令不会影响数据库的结构，而是直接作用于数据本身。如果执行了错误的操作，可以通过回滚机制来取消这些操作。

        需要注意的是，DML命令不会自动提交，而且是可以回滚操作。

        常用的语句关键字有：INSERT、UPDATE、DELETE。

        DML常见命令及使用 

        INSERT：在表中插入新的数据

（先重新创建books表）

create table books(
id int comment '编号',
bName varchar(20) comment '图书名',
price int comment'价格',
authorId int comment'作者编号',
publishDate datetime comment'出版日期'
)comment'图书表';

INSERT INTO books VALUES 
(001,'子鼠',10,123,20000101),
(002,'丑牛',20,234,20010202),
(003,'寅虎',30,345,20020303),
(004,'卯兔',40,456,20030404);

         update：更新表中数据

UPDATE books 
SET bName = '辰龙'
WHERE id = 001;                             --将books表中id为001的记录的bName设置为辰龙

        delete：删除表中数据 

delete from books
where id=001;                         -- 删除books表中id为001的数据

        我们通过观察可以发现，无论是insert、update、delete都是直接对数据表中的数据进行操作，符合DML语言定义。 

DCL语言

        DCL（Data Control Language）数据控制语言：主要用于控制用户对数据库的访问权限以及对数据的执行权限。常见的DCL语句包括GRANT、REVOKE等。

        DCL主要包括两种SQL命令，分别是GRANT和REVOKE。

DCL常用命令及使用

        GRANT：该命令用于授予用户对数据库对象（例如表格）的访问权限。

grant select，insert on books to user1;

        给user1用户授予对books表进行selete和insert的权限。

        REVOKE：该命令用于撤回已经授予用户的某些权限。

revoke select on books from user1;

        撤回user1对books表的selete权限。

 DQL语言

        DQL（Data Query Language）数据查询语言：主要用来查询数据。实际上，DQL在操作中主要体现为SQL的SELECT语句。

DML常用命令及使用

        select：从表中检索数据 

SELECT * FROM books;                    --选择books表中的所有数据
SELECT id, bName FROM books;          --选择books表中的id和bName数据

        通过selete、from和where对查询结果进行筛选

selete * from books where id<4;       -- 查询books表中id小于4的数据

 TCL语言

        TCL (Transaction Control Language) 是事务控制语言的简称，主要用来管理和控制数据库中的事务（Transaction），以保证数据库操作的完整性和一致性。

        TCL命令往往和DML（数据操作语言）命令一起使用，以确保一系列的数据库操作要么全部成功，要么全部不成功（可回滚至操作前的状态）。

TCL常用命令及使用

        COMMIT：用于提交事务，将所有数据库修改保存到数据库中。

INSERT INTO books VALUES(005,'巳蛇',50,567,20040505);
commit;   -- 提交事务，将插入的数据保存到数据库中

        ROLLBACK：用于回滚事务，撤销所有未经提交的数据库修改 

INSERT INTO books VALUES(006,'未羊',60,678,20050606);
ROLLBACK;     -- 回滚事务，撤销上一条插入命令

 MySQL数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
tinyint	1 Bytes	（-128，127）	（0，255）	小整数值
smallint	2 Bytes	（-32768，32767）	（0，65535）	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
int或intege	4 Bytes	（-2 147 483 648 ， 2 147 483 647）	（0，4 294 967 295）	大整数值
bigint	8 Bytes	（-9 223 372 036 854 775 808 ， 9 223 372 036 854 775 807）	(0，18 446 744 073 709 551 615)	极大整数值
float	4 Bytes	(-3.402 823 466 E+38， -1.175 494 351 E-38)， 0，(1.175 494 351 E-38， 3.402 823 466 351 E+38)	0，(1.175 494 351 E-38， 3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

类型	大小（bytes）	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	日期值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-MM hh:mm:ss	混合日期和时间值，时间戳

字符串类型 

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过255个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65535 bytes	二进制形式的长文本数据
TEXT	0-65535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
ONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

约束 

约束的作用及分类

        约束的作用：约束是作用于表中具体字段上，用来限制表中特定字段的取值范围

        约束的分类：在MySQL中约束有6种

主键约束：primary key 在给字段添加主键约束后，该字段不能重复也不能为空，即：被添加了主键约束的字段值唯一且非空。一张表只能有一个主键，但是一个主键可以有多个字段，即：复合主键。

外键约束：foreign key 外键约束，外键约束经常和主键约束一起使用，用来确保数据的一致性，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。

唯一约束：unique 唯一约束，用于约束该字段的值具有唯一性，可以有多个，可以没有，可以为空。

非空约束：not null 非空约束是一种数据完整性约束，用于确保列中的值不能为NULL。这意味着在插入或更新记录时，必须为该列提供一个值。非空约束可以在创建表时或通过修改表结构来添加。

默认值约束：default 默认值约束允许为表中的列指定一个默认值。这意味着，如果在插入记录时没有为该列提供值，MySQL将自动使用默认值填充该列。

检查约束：Check 检查约束用于限制列中可以放置的取值范围

列级约束：not null 、default 、primary key 、unique 、check

表级约束：primary key 、unique 、check 、foreign key

列级约束不能添加外键，表级约束不支持非空和默认值。

添加约束

1.建表时添加约束

        首先创建外键需要引用的表

CREATE table a1(id int PRIMARY KEY,majorName VARCHAR(20));

        正式建表创建stu表，id 主键，stuname 非空，gender check，seat 唯一

age default，majorid 外键。

CREATE TABLE stu(
-- 列级
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
genner CHAR(1) CHECK(genner='男'OR genner='女'),
seat INT UNIQUE,
age INT DEFAULT(18),
majorId INT,
-- 表级，列级约束不能添加外键，所以通过constraint再进行添加外键约束
CONSTRAINT fk_majorId FOREIGN KEY(majorId) REFERENCES major(id)
);

          删除创建的stu表，重新以表级约束的方式进行创建。

-- 删除表
DROP TABLE stu
-- 创建表
CREATE TABLE stu(
id INT,
stuname VARCHAR(20),
genner CHAR(1),
seat INT,
age INT,
majorId INT,
-- 以下为表级查询，因为表级查询不支持非空和默认值，故只有四个。
CONSTRAINT pk PRIMARY KEY(id),
CONSTRAINT ug UNIQUE(seat),
CONSTRAINT chk CHECK(genner='男'OR genner='女'),
CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id)
);

2.在修改表时添加约束
        添加列级约束：alter table 表名 modiy column 字段名 字段类型 新的约束；

        添加表级约束：alter table 表名 add【constraint 约束名】的约束类型（字段名）【外键的引用】

        先删除原表，再重新创建无约束stu表，再进行添加约束。

-- 删除原表
DROP TABLE stu;
-- 创建无约束表
CREATE TABLE stu(
id INT,
stuname VARCHAR(20),
genner CHAR(1),
seat INT,
age INT,
majorId INT)

-- 列级
ALTER TABLE stu MODIFY COLUMN id int PRIMARY KEY;
ALTER TABLE stu MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
ALTER TABLE stu MODIFY COLUMN genner CHAR(1) CHECK(genner='男'OR genner='女');
ALTER TABLE stu MODIFY COLUMN seat INT DEFAULT(18);
-- 表级
ALTER TABLE stu ADD FOREIGN KEY(majorid) REFERENCES major(id);

主键约束，外键约束，唯一约束 在添加时，会自动添加索引让我们来查看一下。

SHOW INDEX FROM stu

结果如下： 

 约束添加成功。

删除约束

        用alter modify尝试直接更改约束。（更改id的约束，由主键更改为无）

-- 更改id字段
ALTER TABLE stu MODIFY COLUMN id INT;
-- 查看索引
SHOW INDEX FROM stu

运行结果及再次查看索引

        发现无变化，故已有约束，通过修改表字段结构，不能删除。

        再次删除约束。

ALTER TABLE stu DROP PRIMARY KEY

运行结果：

 

外键 

外键约束有不同策略，默认策略为restrict。

 外键策略：针对父表中已被引用的数据的更新操作，会如何影响子表中数据
                    restrict/no action 父表（被参照表）数据删除/更新时，拒绝操作
                    cascade 父表数据删除/更新时，子表数据级联删除/更新
                    set null 父表数据删除/更新时，子表数据设置null
                    set default 父表数据删除/更新时，子表数据设置成默认值

分区表

分区表概念

        MySQL分区 是一种数据库优化的技术，它允许将一个大的表、索引或其子集分割成多个较小的、更易于管理的片段，这些片段称为“分区”。每个分区都可以独立于其他分区进行存储、备份、索引和其他操作。这种技术主要是为了改善大型数据库表的查询性能、维护的方便性以及数据管理效率。

为什么要分区

1.用户请求量太大

瓶颈：单服务器的TPS，内存，IO都是有限的。

解决方法：分散请求到多个服务器上； 其实用户请求和执行一个sql查询是本质是一样的，都是请求一个资源，只是用户请求还会经过网关，路由，http服务器等。

2.单表数据量太大

瓶颈：索引膨胀，查询耗时长，影响正常CRUD。

解决方法：切分成多个数据集更小的表。

3.单库数据量太大

瓶颈：单个数据库处理能力有限，单库所在服务器上磁盘空间不足，I/O有限；

解决方法：切分成更多更小的库

分区表分类

水平分区

        水平分区是指将单个数据表中的行分成一些较小的表。这种方法需要根据特定属性对数据表进行分割，例如用户ID、日期、城市等。在水平划分中，相似的片段被分配给相同的表。这种方法带来的优势是可以提高数据备份/恢复等操作的效率，同时提高了查询速度，因为只有一部分数据被读取。

垂直分区

        垂直分区是指按列将数据表划分成较小的表，每个表都具有不同的列并存储记录。这种方法可以帮助优化查询性能，减少读取磁盘的数据量。在垂直分区中，将经常访问的列分为一组，并将不常用的列分为另一组。最常见的实现方法是将经常使用的列（如ID、名称、日期等）分为一个表，而较少使用的列（如大文本、高清照片等）则分为另一个表。

MySQL分区类型

1.RANGE分区：基于列的值范围将数据分配到不同的分区。

2.LIST分区：类似于RANGE分区，但LIST分区是基于列的离散值集合来分配数据的。

3.HASH分区：基于用户定义的表达式的哈希值来分配数据到不同的分区。

4.KEY分区：类似于HASH分区，但KEY分区支持计算一列或多列的哈希值来分配数据。

注意：

        1.建立分区表时，必须至少指定一个分区。

        2.range、list、hash分区的分区字段必须是整型（小数或字符串类型不可以）。

        3.分区字段可以是表中原有字段，也可以是字段计算后的表达式。

        4.分区表插入数据前，数据所属分区必须存在。

        5.分区字段建议设置为not null。

        6.各中分区对null值的处理方式：

        range分区：会将null划分到最小的分区里

        list分区：不允许插入，必须显示定义

        hash分区：null值结果为0，会放到第0个分区

        key分区：null被视为0，会放到第0个分区

        7.多列分区：加 columns 可以多列分区，不限制数据类型，故可以创建单个字段的非整数类型的表分区。

        对比方式为从左到右，第一个字段值小于第一个字段分区值则放入第一个分区，等于或大于第一个字段分区值则对比第二个字段值与第一个字段分区值的大小，以此类推

RANGE分区

        范围分区，一个分区的数据，是某个字段的某一个范围的数据

        分区字段：连续分区的字段

create table user(
id int not null,
name varchar(10)
)
partition by range(id)(
partition p0 values less than (10),        -- p0字段存储id为0到9的字段
partition p1 values less than (20),        -- p1字段存储id为10到19的字段
partition p2 values less than (30),        -- p2字段存储id为20到29的字段
partition p3 values less than maxvalue     -- p3字段存储id>29的字段
)

        可以多个分区字段同时插入，在插入时自己进入相应的分区

insert into user 
values(0,'aa'),(10,'bb'),(20,'cc'),(30,'dd');

        查看user表全表数据及各分区表数据

select * from user;
select * from user partition (p0);
select * from user partition (p1);
select * from user partition (p2);
select * from user partition (p3);

注：Oracle可以非整型数据 

LIST分区

        列表分区，一个分区只能放分区字段固定某几个值的数据

        分区字段：离散字段

create table user1(
id int,
name varchar
)
partition by list(id)(
partition p0 values in (10),    -- p0分区存储id为10的数据
partition p1 values in (20),    -- p1分区存储id为20的数据
partition p2 values in (30)     -- p2分区存储id为30的数据
)

注：Oracle可以非整型数据，不需要in

        插入数据和查看表数据同上

HASH分区

        HASH分区基于分区键（如列或表达式）的值计算哈希值，再通过取模运算确定数据所在的分区编号。

注：hash分区和key分区都是让各个分区数据尽可能分布均匀

-- 系统自动取名
create table u2 (
id int,
name varchar(20)
)
partition by hash(id) partitions 3;     -- 对三取模分区

-- 数值对3取模结果分别为0，1，2故三个分区


-- 自定义分区
create table u3(
id int,
name varchar(20)
)
partition by hash(id) (
partition p11,
partition p12,
partition p13,
partition p14
)
-- 对4取模

KEY分区

        利用MySQL内置的hash加密函数得到值后再取模

create table u4(
id int,
name varchar(20)
)
partition by key(name) partitions 4    -- 对4取模

复合分区

        MySQL主分区（range，list）+子分区（hash，key）

        range，list只能作为主分区，hash，key只能作为子分区。

注：Oracle中只有range，list，hash分区，没有key分区，其中range和list既可以是主分区也可以是子分区，hash只能是子分区

create table u5 (
id int，
name varchar(20)
)
 partition by range(id)        -- 主分区为range分区
 subpartition by hash(id)      -- 子分区为hash分区
 ( partition p1 values less than (10)    -- p1主分区存储id<10的字段
 ( subpartition p11 ,subpartition p12 , subpartition p13 ),  
    -- 子分区对3取模分别存储到三个分区
	partition p2 values less than (20)
 ( subpartition p21 ,subpartition p22 , subpartition p23 )
 )

注：复合分区中hash分区子分区数量必须相同，range，list分区没有限制

分区常见操作

1.将非分区表修改为分区表

-- 先创建非分区表
create table a(
id int
)
-- 将非分区表修改为分区表
alter table a 
partition by range(id)
(
partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than (30),
partition p3 values less than maxvalue
)

注：不是所有关系型数据库均支持将非分区表修改为分区表，MySQL与Oracle支持。

2.删除分区

        删除某个分区的数据和结构。

alter table a drop partition p0;

3.清空分区

        只删除某个分区的数据，不删除分区结构

alter table a truncate partition p1;

4.添加分区

        range分区只能往后添加，由于a表已经创建maxvalue故不能添加分区

-- 创建前置数据
create table a1(
id int
)
partition by range(id)
(
partition p0 values less than (10)
)
-- 添加分区
-- MySQL语法，多个分区在括号里面用逗号分隔
alter table a1 add partition
(
partition p1 values less than (20),
partition p2 values less than (30)
)
-- Oracle语法
-- Oracle不能添加多个分区，一次只能添加一个主分区
alter table a1 add partition p1 values less than (10);

        list分区

alter table a1 add partition 
( partition p1 values in (20) )

5.分解分区

分解分区后数据也会被拆分到对应分区

alter table a 
reorganize partition p0 into
(
partition p01 values less than (5),
partition p02 values less than (10)
)

注：MySQL可以一次拆分分区拆成多个，Oracle只能一次拆成两个

6.合并分区

合并分区并不会丢失数据

alter table a
reorganize partition p01,p02 into
(
partition p0 values less than (10)
)

7.删除分区表所有分区，即 将分区表变为非分区表

不会丢失数据

alter table a remove partitioning;

8.重建分区

重建分区可以优化数据分布，修复分区损坏，调整分区策略，释放存储空间。

alter table a rebuild partition p0;    -- 重建分区p0

9.优化分区

优化空间分布

alter table a optimize partition p1;    -- 优化分区p1

10.修补分区

当分区结构损坏，打不开分区时需要修改分区

alter table a repair partition p2;    -- 修补分区p2

事务

        事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
        注：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

        Oracle需要显示提交事务，plsql软件可通过设置自动提交

存储引擎

        存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可称为表类型。

innoDB引擎

        innoDB是一种兼顾高可靠性和高性能的通用存储引擎

特点
        DML操作遵循ACID模型，支持事务行级锁，提高并发访问功能，支持外键 FOREIGN KEY约束，保证数据的完整性和正确性

文件

        xxx.bid（二进制）：xxx代表表名，innoDB 引擎每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引

        参数：innodb_file_per_table  8.0版本默认开启，每张表对应一个表空间文件
        以json格式显示表的结构
        ibd2sdi his_stu_gpa.ibd

事务四大特性

        原子性：事务包含的所有数据库操作要么全部成功，要么全部失败回滚。

        一致性：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一个一致性状态。一致性规定事务提交前后只存在两种状态，提交事务前的状态和提交事务后的状态，绝不会出现中间的状态。

        隔离性：一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。

        持久性：持久性是指一个事物一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库遇到故障的情况下也不会丢失提交事务的操作。

事务并发时存在的问题

        脏读（Dirty Read）

        脏数据所指的就是未提交的数据，而脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

        例：一个事务正对一条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态（可能提交也可能回滚），此时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。

        不可重复读（Non-repeatable Read）

        一个事务先后读取同一条记录，而事务在两次读取之间该数据被其他事务所修改，则两次读取的数据不同，我们称为不可重复读。

        例：事务1在读取某一数据，而事务2立马修改了这个数据并提交事务给数据库，事务1再次读取该数据就得到了不同的结果，发生了不可重复读。

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

        幻读（Phantom Read）

        一个事物按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足查询跳到的新数据，这种现象称为幻读，幻读是指当事务不是独立执行时发生的一种现象。

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

四个隔离级别

        读未提交（Read uncommitted）

        所有事务都可以看到其他未提交事务的执行结果。本隔离级别是最低的隔离级别，虽然拥有超高的并发处理能力和很低的系统开销，但很少用于实际应用。因为采用这种隔离级别只能防止更新丢失问题（这个问题现代关系型数据库已经不会发生），不能解决脏读，不可重复读及幻读问题。

        读已提交（Read committed）

        这是大多数数据库的默认隔离级别（Oracle默认隔离级别，但不是MySQL默认的）。它满足简单的隔离定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别可以防止脏读问题，但会出现不可重复读和幻读问题。

        可重复读（Repeatable read）

        它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。可以防止除幻读外其他问题（MySQL innodb引擎默认  8版本幻读问题已解决）。

        串行化（Serializable）

        这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读、第二类更新丢失问题。在这个级别，可以解决上面提到的所有并发问题，但可能导致大量的超时现象和锁竞争，通常数据库不会用这个隔离级别，我们需要其他机制来解决这些问题：乐观锁和悲观锁。

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
可串行化	×	×	×

事务操作

查看MySQL支持哪些引擎

show engines;    -- 当前MySQL版本支持哪些引擎

注：INNODB 支持事务，myisam、memory等不支持事务

查看MySQL当前默认的存储引擎

show variables like '%storage_engine%';

  注：MySQL 5.5版本前，默认存储引擎是MyISAM
         MySQL 5.5版本后，默认存储引擎是innoDB

显示自动提交事务的状态（insert、update、delete）

show variables like 'autoconmit';

开启事务

set @@autocommit=0 ;    -- 将自动提交事务关闭，只对当前对话有效
set @@autocommit=1 ;    -- 设置自动提交事务

begin;    -- 开启事务
start transaction;    -- 开启事务

提交事务

select @@autoconmit;    -- 查询结果为1自动提交
set @@autocommit=0 ;    -- 为0手动提交

commit;    -- 提交事务（成功）
rollback;  -- 回滚事务（失败）

视图

视图概念

        视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图的数据，这些数据存放在原来的表中。视图本身并不存储数据，而是存储一个查询语句，当查询视图时，数据库系统会动态生成视图的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。同样，对视图的更新也会影响到原来表的数据。

视图的作用

        1.使操作简单化

        视图需要达到的目的就是所见即所需。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部条件。（视图存放的是查询语句，通过查询视图可以直接查看到该查询语句查询出的结果，不必再次输入查询语句，即操作简单化。）

        2.增加数据的安全性

        通过视图，用户只能查询和修改指定的数据。指定数据之外的信息，用户接触不到。这样可以防止敏感信息被未授权的用户查看，增强机密信息的安全性。

        3.提高表的逻辑独立性

        视图可以屏蔽原有表结构变化带来的影响。例如：原有表增加列或删除未被引用的列，对视图不会造成影响。同样，如果修改表中的某些列，可以使用修改视图来解决这些列带来的影响。

视图基本操作

（以下操作前置代码）

-- 创建学生表，表名改为 stu
CREATE TABLE stu (
    sid INT AUTO_INCREMENT PRIMARY KEY,  -- 学生ID
    sname VARCHAR(100) NOT NULL,         -- 学生姓名
    gender ENUM('Male', 'Female') NOT NULL, -- 性别
    dob DATE                             -- 出生日期
);

-- 插入示例数据到学生表
INSERT INTO stu (sname, gender, dob) VALUES
('Alice', 'Female', '2001-05-12'),
('Bob', 'Male', '2000-08-23'),
('Charlie', 'Male', '2002-01-30');

-- 创建课程表，表名改为 co
CREATE TABLE co (
    cid INT AUTO_INCREMENT PRIMARY KEY,  -- 课程ID
    cname VARCHAR(100) NOT NULL,         -- 课程名称
    credits INT NOT NULL                 -- 学分
);

-- 插入示例数据到课程表
INSERT INTO co (cname, credits) VALUES
('Mathematics', 4),
('Physics', 3),
('Chemistry', 3);

-- 创建成绩表，表名改为 sc
CREATE TABLE sc (
    scid INT AUTO_INCREMENT PRIMARY KEY, -- 成绩ID
    sid INT NOT NULL,                    -- 学生ID
    cid INT NOT NULL,                    -- 课程ID
    成绩 DECIMAL(3,1),                   -- 成绩（数字类型）
    FOREIGN KEY (sid) REFERENCES stu(sid),
    FOREIGN KEY (cid) REFERENCES co(cid)
);

-- 插入示例数据到成绩表
INSERT INTO sc (sid, cid, score) VALUES
(1, 1, 90.5),
(1, 2, 85.0),
(2, 1, 82.0),
(2, 3, 88.5),
(3, 2, 78.5),
(3, 3, 85.0);

创建视图

1.查看创建视图的权限

        创建视图需要具有CREATE VIEW权限。同时具有涉及的列的SELECT权限。可以使用SELECT语句来查询这些权限信息。

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名';

参数说明

（1）Select_priv：属性表示用户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。

（2）Create_view_priv：属性表示用户是否具有CREATE VIEW权限；

（3）mysql.user：表示MySQL数据库下面的user表。

（4）用户名：参数表示要查询是否拥有权限的用户，该参数需要用单引号引起来。
2.创建视图语句

create [or replace] [algorithm={undefied|merge|temptable}]
view 视图名 [(属性清单)]
as select 语句
[with [cascaded|local] check option];

参数说明：

（1）algorithm:可选项，表示视图选择的算法。

（2）视图名：表示要创建的视图名称。

（3）属性清单：可选项，指定视图中各个属性的名词，默认与select 语句中的查询的属性相同。

（4）select语句:表示一个完整的查询语句，将查询记录导入视图中。

（5）with check option：可选项，表示更新视图时要保证在该视图的权限范围内。

例：创建视图

create view v1 as SELECT 
    s.sname, 
    c.cname, 
    sc.成绩
FROM 
    sc
JOIN 
    stu s ON sc.sid = s.sid
JOIN 
    co c ON sc.cid = c.cid;

        创建视图的时候，指定属性清单。

create [or replace] view v1 (sname,cname,score)as 
-- []内内容为可选项，代表如果视图已存在进行重载
SELECT 
    s.sname, 
    c.cname, 
    sc.score
FROM 
    sc
JOIN 
    stu s ON sc.sid = s.sid
JOIN 
    co c ON sc.cid = c.cid;

 注：

（1）运行创建视图的语句需要用户具有创建视图（create view）的权限，若加了[or replace]时，还需要用户具有删除视图[drop view]的权限。

（2）select语句不能包含from字句中的子查询。

（3）select语句不能引用系统或用户变量。

（4）select语句不能引用预处理语句参数。

（5）在存储子程序内，定义不能引用子程序参数或局部变量。

（6）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在此类问题，可使用check table语句。

（7）在定义中不能引用temporary表，不能创建temporary视图。

（8）在视图定义中命名的表必须已存在。

（9）不能将触发程序与视图关联在一起。

（10）在视图定义中允许使用order by，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。

修改视图

        修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过create or replace view语句和alter view语句来修改视图。

create or replace view修改视图

create or replace 
view v1 (sname , cname , score) as
select 
	s.sname,
	c.cname,
	sc.score
from 
	sc
join 
	stu s on sc.sid=s.sid
join
	co  c on sc.cid=c.cid;

运行结果：

        该语句与创建视图语句几乎相同，但是增加了 or replace 代表创建v1视图，若视图已存在，则重载，所以尽管已经有v1视图，我们仍然可以使用该语句进行创建或修改v1视图。

alter view修改视图

alter view v1 as 
select sid , sname from stu;

运行结果：

        alter 为ddl语言关键字，作用为修改表结构，故 alter view可以改变视图的表结构即视图的定义。

查看视图

        查看视图是指查看数据库中已存在的视图的定义。使用describe关键字。

describe v1;

运行结果：

删除视图

        删除视图是指删除数据库中已存在的视图，删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用drop view语句删除视图，同时，用户必须拥有drop权限。

drop view if exists v1;

        drop也是ddl语言可以进行删除表，所以可以删除视图（视图为虚拟表），if exists为如果存在，防止报错，存在则删除，不存在则不进行操作。

索引

索引概念

        索引是对数据库表中一列或多列的值进行排序的一种结构，使得查询时减少检索行数，使用索引可快速访问数据库表中特定的信息。索引是提高效率的一个因素，如果你的数据库存在大数据量的表，则需建立优秀索引或优化查询语句来提高查询效率。

索引优缺点

优点：

1. 索引大大减小了服务器需要扫描的数据量，从而加快数据的检索速度，这也是创建索引的最主要原因。
2. 索引可以帮助服务器避免排序和创建临时表（group by 会创建临时表）。
3. 索引可以将随机IO变成顺序IO。
4. 索引对应InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表的并发性。
5. 关于InnoDB（）、索引和锁：InnoDB可以在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）。
6. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
7. 可以加速锁和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
8. 在使用分组和排序字句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
9. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 索引需要占用物理空间，除了数据表占用数据空间之外，每个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么占用的空间会更大。
3. 对表中的数据进学校增、删、改时，索引也要动态的维护，这就降低了整体的维护速度。
4. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
5. 对于非常小的表，大部分情况下简单的全表扫描更有效。

索引类型

        索引的分类分为逻辑分类与物理分类。

逻辑分类

• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许null值插入。

（1）直接创建索引

create index index_name on table_name(col_name);

以视图中创建的数据为例，为sc表sid列创建普通索引。

create index in1 on sc(sid);

（2）以修改表结构方式添加索引

alter table table_name add index index_name(col_name);

 为sc表scid添加索引。

alter table sc add index in2(scid);

（3）创建表时同时添加索引

create table news(
id int(10),
primary key (id),
index index_name(id)
)

• 复合索引：复合索引是在多个字段上创建的索引。复合索引遵循'最左前缀'原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使。因此，在复合索引中索引列的顺序至关重要。

(1)创建一个复合索引

create index index_name on
table_name(col_name1,col_name2,...);

例： 为sc表scid和sid添加复合索引

create index in3 on sc (scid,sid);

（2） 修改表结构的方式添加索引

alter table table_name add index index_name (col_name1,col_name2,...);

例： 为sc表scid和sid添加复合索引 

ALTER TABLE 表名 DROP INDEX 索引名; -- 删除索引
alter table sc drop index in3;     -- 删除刚创建的索引in3
alter table sc add index in3(scid,sid); -- 以修改表结构方式创建复合索引in3

• 唯一索引：唯一索引和普通索引类似，主要区别在于，唯一索引限制列的值必须唯一，但允许为null值（只允许存在一条空值），如果是组合索引，则列值的组合必须唯一。一张表可以有多个唯一索引

（1）创建唯一索引

        创建单列唯一索引

create unique index index_name on table_name (col_name);

 例：为sc表scid建立单个唯一索引

create unique index in4 on sc(scid);

         创建多列唯一索引

create unique index index_name on table_name( col_name1 , col_name2... );

  例：为sc表scid和sic建立多个列的唯一索引

create unique index in5 on sc(scid ,sid);

（2）修改表结构方式创建唯一索引

alter table table_name add unique index index_name (col_name); -- 单列
alter table table_name add unique index index_name (col_name1 , col_name2 ,...);
    -- 多列

 例：为sc表scid建立单个唯一索引，为sc表scid和sic建立多个列的唯一索引

alter table sc drop index in4;    -- 删除刚创建的索引in4
alter table sc drop index in5;    -- 删除刚创建的索引in5

alter table sc add unique index in4(scid);        -- 修改表结构方式创建索引in4
alter table sc add unique index in5(scid , sid);  -- 修改表结构方式创建索引in5

 （3）创建表时直接指定索引

create table 'news'(
id int(10),
primary key(id),
unique index_name_unique(title)
)

-- 例：
create table news (
	id int(10),
	primary key(id),
	unique in6(id)
)

• 主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键索引，不允许重复，不允许有空值。一般在建表时同时创建主键索引。

（1）创建表时添加主键索引

create table news(
	id int(10),
	primary key (id)
)

 （2）修改表结构时添加主键索引

alter table table_name add primary key(col_name);

• 全文索引：通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引，MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引，只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

create fulltext index 名字 on tbl_name (col_name);

物理分类 

索引物理分类可以分为聚簇索引和非聚簇索引。

聚簇是为了提高某个属性或属性组的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块。

聚簇索引：不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树，且B+树叶子节点存放都是表的行记录时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储和索引放到了一块，找到索引也就找到了数据。

一般是主键索引、唯一索引 一张表只能有一个聚簇索引，不是必须有。

非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

聚簇索引优缺点：

优点：

1. 访问数据更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
2. 聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
2. 更新主键代价很高，因为将会导致被更新的行移动。
3. 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

B+树索引

B+树结构图 

1.B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。

之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。

如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。

另外，B+ 树的阶数是等于键值的数量的，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。

一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。

2.因为 B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。

那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而 B 树因为数据分散在各个节点，要实现这一点是很不容易的。

有心的读者可能还发现上图 B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

其实上面的 B 树我们也可以对各个节点加上链表。这些不是它们之前的区别，是因为在 MySQL 的 InnoDB 存储引擎中，索引就是这样存储的。

也就是说上图中的 B+ 树索引就是 InnoDB 中 B+ 树索引真正的实现方式，准确的说应该是聚集索引。

通过上图可以看到，在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

最左前缀原则

MySQL建立联合索引时遵守最左前缀原则，即最左优先（查询条件精确匹配索引的左边连续），在MySQL中，查询条件会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配，即复合索引范围查询之后的字段不会用到索引。

• 如果有一个 2 列的索引 (col1, col2)，则已经对 (col1)、(col1, col2) 上建立了索引；
• 如果有一个 3 列索引 (col1, col2, col3)，则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引；

所以当我们查询col3或col2、col3时是用不到索引的，如果查询col1或col1、col2时才会用到索引，其中col1、col2的顺序可以打乱，MySQL的查询优化器会自动优化成索引可以识别的形式。

索引失效情况

        数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。即使建立索引，索引也不会生效。

        1.范围查询

        mysql 会一直向右匹配直到遇到索引搜索键使用 > , < , between , like 就停止匹配。一旦权重最高的索引搜索键使用范围查询，那么其它搜索键都无法用作索引。即索引最多使用一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

        2.对索引列进行运算

        如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。即，对索引列函数计算后过滤，索引会失效。

        3.or条件

        or条件除了左右两边同时是主键的时候索引才会生效，其他情况索引均会失效。

        4.数据类型不一样（隐式条件转换）

        如果列是字符串类型，传入条件必须是用引号引起来，不然会导致索引失效。

        例：将其他数据类型隐式转换成字符串导致索引失效。

        5..like模糊匹配
        如搜索键值以通配符%开头（如：like '%abc'），则索引失效，直接全表扫描；若只是以%结尾，则不影响索引构建。

锁

概述：

        锁是计算机协调多个进程或线程并发访问某一个资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）的征用外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度说，锁对数据库而言显得尤其重要，也更加复杂。

分类

1.全局锁：锁定数据库中的所有表。加上全局锁后，整个数据库只能允许读，不允许做任何写操作，即，对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

应用场景：

        假设在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表。

        在进行数据备份时，先备份了tb_stock库存表。
        然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入tb_order表）。
        然后再执行备份 tb_order表的逻辑。
        业务中执行插入订单日志操作。
        最后，又备份了tb_orderlog表。
        此时备份出来的数据，是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。
        那如何来规避这种问题呢? 此时就可以借助于MySQL的全局锁来解决。

        对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。

-- 加全局锁
flush tables with read lock;
-- 数据备份
mysqldump -uroot –p1234 itcast > itcast.sql
-- 释放锁
unlock tables;

特点：

        （1）全局锁存在弊端：全局锁的力度很大，一旦加了全局锁那么其他客户端在执行写入操作时都会阻塞。

        （2）如果业务当中的数据库是主从结构而不是单机版，而且还做了读写分离，那这时做写入操作是不会阻塞的，因为写入主库是可以从库当中进行备份，由此也还衍生出在从库上备份的问题。

        （3）若加上-single-transaction参数来执行备份操作，那么在innoDB引擎中它实际上是通过快照读来实现的。

2.表级锁：每次操作锁住整张表。锁的粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

主要分三类：表锁、元数据锁、意向锁。

表锁

        表共享读锁（read lock）：又称共享锁，顾名思义，共享读锁就是多个事务对于同一数据可以共享一把锁，就能访问到数据，但是只能读不能修改。

        例：客户端1首先给表加上一个读锁，且可以去读取该表的数据，但是不能进行写入操作，而客户端2同样可以读取该表的数据但是不能进行读写，在客户端1执行完逻辑以后释放表锁，所以加上读锁后，表只能读取而不能写入，而且读锁不会阻塞其他客户端执行读取操作。

        表独占写锁 （write lock）：又称排他锁，顾名思义，排他锁就是不能与其他锁并存。

        例：客户端1为表加上写锁后就锁定了该表，此时使用客户端2会发现既不能对表执行查询操作也不能执行写入操作，就是说读写都被禁止。但是对于客户端1来说，是可以对表执行读写操作的，最后将表写锁释放后，客户端2便可以对表进行操作。

-- 加锁
lock tables 表名... read/wirte
-- 释放锁
unlock tables / 客户端断开连接

元数据锁（meta data lock、MDL）

        基于表的元数据加锁，加锁后整张表不允许其他事务操作。这里的元数据可以简单理解为一张表的表结构。MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

对应SQL	锁类型	说明
lock tables xxx read/write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select … lock in share mode	SHARED_READ	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert 、update、delete、select … for update	SHARED_WRITE	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table …	EXCLUSIVE	与其他的MDL都互斥

例：

当执行select、insert、update、delete时，添加的是元数据共享锁（SHARED_READ / SHARED_WRITE） ，之间是兼容的。

当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。

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

意向锁

        意向锁是为了提高粗粒度锁的性能而设置的一种预判机制（意向锁是为了协调行锁和表锁的关系，用于优化InnoDB加锁的策略），意向锁的主要作用是避免为了判断表是否存在行锁而去全表扫描（即在一个操作发起实际资源的锁申请行为之前，先对更粗力度的资源发起一个加锁意向声明），意向锁是由InnoDB在操作数据之前自动加的，不需要用户干预。

分类：

意向共享锁(IS): 由语句select … lock in share mode添加 。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。

意向排他锁(IX): **由insert、update、delete、select…for update添加 **。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

应用场景：

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下：

首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

有了意向锁之后 :
客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

-- 查看意向锁及行锁加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

3.行级锁：在InnoDB存储引擎中行级锁每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

分类：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他 锁。
两种行锁的兼容情况如下:

当前锁类型/请求锁类型	S（共享锁）	X（排他锁）
S（共享锁）	兼容	冲突
X（排他锁）	冲突	冲突

常见的SQL语句，在执行时，所加的行锁如下：

SQL	行锁类型	说明
INSERT …	排他锁	自动加锁
UPDATE …	排他锁	自动加锁
DELETE …	排他锁	自动加锁
SELECT（正常）	不加任何锁
SELECT … LOCK IN SHARE MODE	共享锁	需要手动在
SELECT之后加LOCK IN SHARE MODESELECT … FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

执行计划

一、Explain 执行计划是什么？

EXPLAIN 是 MySQL 提供的用于分析 SQL 查询执行计划的工具，通过该命令可以获取查询优化器选择的执行路径。explain可以查看sql执行时是否有使用到索引，关联查询时可以查看sql 的执行顺序。

执行计划的作用：

1.验证索引使用情况

2.分析多表连接顺序

3.识别全表扫描与大表过滤

4.检测子查询与临时表

5.评估排序操作效率

6.验证查询优化器选择

二、查看执行计划

explain + sql
-- 例：
sxplain  select * from a where id=1

三、执行计划各个字段含义 

id

表示执行顺序，id的数字越大越先执行，如果数字一样，那么从上往下依次执行，如果为null表示这是一个结果集，不需要用它来进行查询。

select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂查询。

取值	含义
simple	简单的select查询，查询中不包含子查询或者union
primary	查询中包含任何复杂的子部分，最外层查询则被标记为primary
union	若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
dependent union	与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
union result	包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
subquery	在select 或 where列表中包含了子查询
dependent subquery	与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
derived	在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在临时表里

table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

type

MySQL的官网解释为：连接类型(the join type)。它描述了找到所需数据使用的扫描方式。
访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

扫描方式汇总

取值	含义
system	系统表，少量数据，往往不需要进行磁盘IO
const	常量连接
eq_ref	主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
ref	非主键非唯一索引等值扫描
ref_or_null	与ref方法类似，只是增加了null值的比较。实际用的不多
range	范围扫描
index	索引树扫描
index_merge	表示查询使用了两个以上的索引，最后取交集或者并集
fulltext	全文索引检索
unique_subquery	用于where中的in形式子查询，子查询返回不重复值唯一值
index_subquery	用于in形式子查询,子查询可能返回重复值，可以使用索引将子查询去重
ALL	全表扫描(full table scan)

扫描方式详解

        system

扫码类型为system，说明数据已经加载到内存里，不需要进行磁盘IO。扫描速度最快

        const

sxplain select id from billing_item_dis where id = 1;

 const扫描条件为：

        1.命中主键（primary key）或者唯一（unique）索引；

        2.被连接的部分是一个常量（const）值；

        eq_ref

eq_ref表示使用唯一性索引进行连接操作，即使用索引查找来匹配连接条件，这种方式适用于连接条件中的列是主键或唯一索引的情况。

扫描条件为：对于前表的每一行，后表只有一行被扫描。

出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。

        ref

对于前表的每一行(row)，后表可能有多于一行的数据被扫描。
不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。

        range

索引范围扫描，常见于使用>,<,is,null,between,in,like等运算符的查询中。

        index

索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组查询。

        all

全表扫描数据文件，然后在server层进行过滤返回符合要求的记录。

possible_keys

查询涉及到的字段上存在索引，则该索引将被列出，但不一定被实际使用。

key

实际使用的索引，如果为NULL，则没有使用索引。

查询中如果使用了覆盖索引，则该索引仅出现在key列表中

key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转化，这里可能显示为func。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录锁需要读取的行数。

Extra

取值	含义	举例
Using where	Extra为Using where说明， SQL使用了where条件过滤数据。	explain select * from billing_item_dis where id > 4;
Using index	Extra为Using index说明， SQL所需要返回的所有列数据均在一棵索引树上， 而无需访问实际的行记录。	explain select id from billing_item_dis;
Using index condition	Extra为Using index condition说明， 确实命中了索引，但不是所有的 列数据都在索引树上，还需要访问实际的行记录。	explain select * from billing_item_dis t1, billing_item_result t2 where t1.user_id = t2.id;
Using filesort	Extra为Using filesort说明，得到所需结果集， 需要对所有记录进行文件排序。典型的，在一个没有建立索引的列上进行了order by，就会触发，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。	explain select id from billing_item_dis order by item_name;
Using temporary	Extra为Using temporary说明， 需要建立临时表(temporary table)来暂存中间结果。 这类SQL语句性能较低，往往也需要进行优化。 典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。	explain select item_name, COUNT(*) from billing_item_dis GROUP BY item_name order by item_name;

四、索引失效情况

七字诀：模型数空运最快

• 模：模糊查询LIKE以%开头
• 型：数据类型错误
• 数：对索引字段使用内部函数
• 空：索引列是NULL
• 运：索引列进行四则运算
• 最：复合索引不按索引列最左开始查找
• 快：全表查找预计比索引更快

存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点：

• 存储过程可封装，并隐藏复杂的商业逻辑。
• 存储过程可以回传值，并可接受参数。
• 存储过程无法使用select指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
• 存储过程可以用在数据检验，强制实行商业逻辑。

缺点：

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
• 存储过程的性能调优与撰写，受限于各种数据库系统。

一、存储过程的创建和调用

创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

关键语法解析：

声明语句结束符（可以自定义）：

DELIMITER $$

或

DELIMITER / /

声明存储过程：

 CREATE PROCEDURE demo_in_parameter(IN p_in int)       

存储过程开始和结束符号:

BEGIN .... END    

变量赋值：

 SET @p_in=1  

变量定义：

DECLARE l_int int unsigned default 4000000; 

创建mysql存储过程、存储函数：

 create procedure 存储过程名(参数)

存储过程体：

 create function 存储函数名(参数)

例（删除给定球员参加的所有比赛）：

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter;　　#将语句的结束符号恢复为分号

 调用存储过程

call sp_name[(传参)];

例：

call delete_matches(57);

代码解析：

在存储过程中设置了需要传参的变量p_playemo，调用存储过程的时候，通过传参将57赋值给p_playemo，然后进行存储过程里的sql操作。

存储过程体

 存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl、if-then-else和while-do语句、声明变量的declare语句等

过程体格式：以begin开始，以end结束（可嵌套）

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

 注：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

二、存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量

三、变量

变量定义

局部变量声明一定要放在存储过程体的开始：

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)

变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

四、MySQL存储过程的控制语句

 变量控制域

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值

条件语句

1.if-then-else语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

2.case语句： 

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else   
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 
case
    when var=0 then
        insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case

循环语句

 1.while...end while

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

 while 条件 do
    --循环体
endwhile

2.repeat...end repeat

它在执行操作后检查结果，而while则是执行前进行检查。

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc5 ()  
     -> begin   
     -> declare v int;  
     -> set v=0;  
     -> repeat  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> until v>=5  
     -> end repeat;  
     -> end;  
     -> //  
mysql > DELIMITER ;

 repeat
    --循环体
until 循环条件  
end repeat;

3.loop...endloop

 loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

原文链接：MySQL 存储过程 | 菜鸟教程

制作不易，求关注、点赞、收藏、转发，这对我真的很重要！！！