MySQL进阶—从零到入土

1. 存储引擎

1). 建表时指定存储引擎

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

show engines;

show create table account;

-- 创建表 my_myisam , 并指定MyISAM存储引擎
create table my_myisam(
id int,
name varchar(10)
) engine = MyISAM ;
 
-- 创建表 my_memory , 指定Memory存储引擎
create table my_memory(
id int,
name varchar(10)
) engine = Memory ;

InnoDB 引擎与 MyISAM 引擎的区别 ?

① . InnoDB 引擎 , 支持事务 , 而 MyISAM 不支持。

② . InnoDB 引擎 , 支持行锁和表锁 , 而 MyISAM 仅支持表锁 , 不支持行锁。

③ . InnoDB 引擎 , 支持外键 , 而 MyISAM 是不支持的。

 存储引擎选择

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。  

2. 索引

2.1 索引概述

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引的优缺点

2.2 索引结构

2.2.1 概述

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构

  注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。 

2.2.2 B-Tree 

以一颗最大度数（max-degree）为 5(5 阶 ) 的 b-tree 为例，那这个 B 树每个节点最多存储 4 个 key，5个指针：

2.2.3 B+Tree

绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。

红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree 相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

2.2.4 Hash

• Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
• 无法利用索引完成排序操作
•  查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

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

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

B. 对于 B-tree ，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储

的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

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

2.3 索引分类

2.3.1 索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

2.3.2 聚集索引&二级索引

聚集索引选取规则 :

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

 聚集索引和二级索引的具体结构如下：

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引的叶子节点下挂的是该字段值对应的主键值。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

以下两条 SQL 语句，那个执行效率高 ? 为什么 ?

A. select * from user where id = 10 ;

B. select * from user where name = 'Arm' ;

备注 : id 为主键， name 字段创建的有索引；

解答： A 语句的执行性能要高于 B 语句。

因为 A 语句直接走聚集索引，直接返回数据。 而 B 语句需要先查询 name 字段的二级索引，然

后再查询聚集索引，也就是需要进行回表查询。

InnoDB 主键索引的 B+tree 高度为多高呢 ?

假设 : 一行数据大小为1k ，一页中可以存储 16 行这样的数据。 InnoDB 的指针占用 6 个字节的空间，主键即使为bigint ，占用字节数为 8 。

高度为2 ：

        n * 8 + (n + 1) * 6 = 16*1024 , 算出 n 约为 1170

        1171* 16 = 18736

也就是说，如果树的高度为 2 ，则可以存储 18000 多条记录。

高度为3 ：

        1171 * 1171 * 16 = 21939856

也就是说，如果树的高度为 3 ，则可以存储 2200w 左右的记录。

2.4 索引语法

 1). 创建索引

create [ uniqe | felltext ] index index_name on table_name (index_col_name,... ) ;

2). 查看索引

show index from table_name ;

drop index index_name ON table_name ;

--查看MySQL服务器配置参数
show variables;

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
show global status like 'Com_______';

2.5.2 慢查询日志

--慢查询日志的开关查询
show variables like 'slow_query_log';

vi /etc/my.cnf
-- 按i到最后添加下面语句
 
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
 
-- 重启数据库服务器
systemctl restart mysql
 

2.5.3 profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling 参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling ;

显示 YES，代表当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句 session/global 级别开启profiling：

-- 查看profile操作是否开着
select @@profiling;
 
-- 打开
set profiling = 1;

 执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况
show profiles;
 
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
 
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

2.5.4 explain

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

desc select * from tb_user WHERE id =1;

2.6 索引使用

2.6.1 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';

当执行 SQL 语句 : explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程 ' ； 时，是否满足最左前缀法则，走不走上述的联合索引，索引长度？

可以看到，是完全满足最左前缀法则的，索引长度 54 ，联合索引是生效的。

注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段 ( 即是第一个字段) 必须存在，与我们编写 SQL 时，条件编写的先后顺序无关。

2.6.2 范围查询

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

• 当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字段是没有走索引的。

• 当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引的。

2.6.3 索引失效情况

• 不要在索引列上进行运算操作， 索引将失效。

-- 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';
-- 当根据phone字段进行函数运算操作之后，索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
 
explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;

•  如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from tb_user where profession like '软件%';
 
explain select * from tb_user where profession like '%工程';
 
explain select * from tb_user where profession like '%工%';

在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。

• 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

explain select * from tb_user where id = 10 or age = 23;
 
explain select * from tb_user where phone = '17799990017' or age = 23;

• 如果MySQL评估使用索引比全表更慢，则不使用索引。  

select * from tb_user where phone >= '17799990005';
 
select * from tb_user where phone >= '17799990015';

2.6.4 SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:

select id,username,password from tb_user where username = 'itcast';

答案 : 针对于 username, password 建立联合索引 , sql 为 : create index idx_user_name_pass on tb_user(username,password);

这样可以避免上述的 SQL 语句，在查询的过程中，出现回表查询。

2.6.6 前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

create index idx_xxxx on 表名(列名(n)); 

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

2.7 索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（ where ）、排序（ order by ）、分组（ goup b ）操作的字段建立索引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，影响增删改的效率。

7). 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

3. SQL优化

3.1 插入数据

3.1.1 insert

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

-- 批量插入数据
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

2). 优化方案二

-- 手动控制事务
start transaction;
 
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
 
commit;

3). 优化方案三

-- 主键顺序插入，性能要高于乱序插入。
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

3.1.2 大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql --local-infile -u root -p
 
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
 
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/home/mysql/桌面/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
 
select count(*) from tb_user;

3.2 主键优化

1). 数据组织方式

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table IOT)。

2). 页分裂

页可以为空，也可以填充一半，也可以填充 100% 。每个页包含了 2-N 行数据 ( 如果一行数据过大，会行溢出) ，根据主键排列。

A. 主键顺序插入效果

① . 从磁盘中申请页， 主键顺序插入

B. 主键乱序插入效果 

3). 页合并

3.3 order by优化 

using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index ，不需要 额外排序，操作效率高。

3.4 group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能：

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

3.5 limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
• InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

3.6.2 count用法

用法：count（*）、count（主键）、count（字段）、count（数字）

3.7 update优化

4.1.2 语法

create [or replace] view 视图名称[(列名列表)] as select语句 [ with [ cascaded | local ] check option ]

2). 查询

-- 查看创建视图语句
show create view 视图名称;
-- 查看视图数据
select * from 视图名称 ...... ;

3). 修改

-- 方式一：
create or replace view 视图名称[(列名列表)] as select语句 [ with [ cascaded | local ] check option ]
-- 方式二：
alter view 视图名称[(列名列表)] as select语句 [ with [ cascaded | local ] check option ];

4). 删除

drop view [if exists] 视图名称 [,视图名称] ...

4.1.3 检查选项

create or replace view 视图名称[(列名列表)] as select语句 with cascaded check option;

2).local(本地)

v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。

create or replace view 视图名称[(列名列表)] as select语句 with local check option;

4.1.4 视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一项，则该视图不可更新：

create view stu_v_count as select count(*) from student;
insert into stu_v_count values(10);
-- 插入失败，不是一一对应

4.1.5 视图作用

1). 简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2). 安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

3). 数据独立

视图可帮助用户屏蔽真实表结构变化带来的影响。

4.1.6 案例

create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;
select * from tb_user_view;

create view tb_stu_course_view as select s.name student_name , s.no student_no,c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;
select * from tb_stu_course_view;

4.2.2 基本语法

create procedure 过程名称 ([ 参数列表 ])
begin
-- SQL语句
end;

call 名称 ([ 参数 ]);

3). 查看

-- 查询指定数据库的存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'xxx'; 
-- 查询某个存储过程的定义
show create procedure 存储过程名称; 

drop procedure [if exists] 存储过程名称 ；

-- 注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符
delimiter $$

4.2.3 变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

4.2.3.1 系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

 -- 查看所有系统变量
show [ session| global] variables;
-- 可以通过LIKE模糊匹配方式查找变量
show [ session| global] variables like '......'; 
-- 查看指定变量的值
select @@[session| global] 系统变量名; 

set [ session | global] 系统变量名 = 值 ;
set @@[ session| global]系统变量名 = 值 ;

-- 查看系统变量
show session variables ;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;
select @@session.autocommit;
-- 设置系统变量
set session autocommit = 1;
insert into course(id, name) VALUES (6, 'ES');
set global autocommit = 0;
select @@global.autocommit;

4.2.3.2 用户定义变量

set @var_name = expr [, @var_name = expr] ... ;
set @var_name := expr [, @var_name := expr] ... ;
 
-- 赋值时，可以使用 = ，也可以使用 :=
 
select @var_name := expr [, @var_name := expr] ... ;
select 字段名 into @var_name FROM 表名;

select @var_name;

-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;
select @abc;

4.2.3.3 局部变量

declare变量名 变量类型 [default... ] ;

set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ...;

if 条件1 then
.....
elseif 条件2 then -- 可选
.....
else-- 可选
.....
end if;

create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
        select result;
end;
 
call p3();

create procedure 存储过程名称 ([ in/out/inout 参数名 参数类型 ])
begin
    -- SQL语句
end;

create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;
 
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
 
select @result;
 
-- 将传入的200分制的分数，进行换算，换算成百分制，然后返回。
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;
set @score = 198;
call p5(@score);
select @score;

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
执行statement_list2， 否则就执行 statement_list
case case_value
    when when_value1 then statement_list1
    when when_value2 then statement_list2 ...
    else statement_list
end case;

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
case
    when search_condition1 then statement_list1
    when search_condition2 then statement_list2 ...
    else statement_list
end case;

create procedure p6(in month int)
begin
    declare result varchar(10);
    case
        when month >= 1 and month <= 3 then
            set result := '第一季度';
        when month >= 4 and month <= 6 then
            set result := '第二季度';
        when month >= 7 and month <= 9 then
            set result := '第三季度';
        when month >= 10 and month <= 12 then
            set result := '第四季度';
        else
            set result := '非法参数';
    end case;    
    select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
end;

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
    SQL逻辑...
end while; 

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
create procedure p7(in n int)
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;
    select total;
end;
call p7(100);

-- 先执行一次逻辑，然后判定until 条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
    SQL逻辑...
    until 条件
end repeat;

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)
begin
    declare total int default 0;
    repeat
        set total := total + n;
        set n := n - 1;
    until n <= 0
    end repeat;
select total;
end;
 
call p8(100);

• LEAVE ：配合循环使用，退出循环。
• ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] loop
    SQL逻辑...
end loop [end_label];

leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环

create procedure p9(in n int)
begin
    declare total int default 0;
    sum:loop
        if n<=0 then
            leave sum;
        end if;
 
        set total := total + n;
        set n := n - 1;
    end loop sum;
    select total;
end;
call p9(100);

create procedure p10(in n int)
begin
    declare total int default 0;
    sum:loop
        if n<=0 then
            -- 退出sum循环
            leave sum;
        end if;
        if n%2 = 1 then
            set n := n - 1;
            -- 直接进入下一次循环
            iterate sum;
        end if;
            set total := total + n;
            set n := n - 1;
    end loop sum;
    select total;
end;
call p10(100);

4.2.10 游标

declare 游标名称 cursor for 查询语句;

open 游标名称;

fetch游标名称 into 变量 [, 变量 ];

close 游标名称;

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null, uname, upro);
    end while;
    close u_cursor;
end;
call p11(30);

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;

-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
declare exit handler for SQLSTATE '02000' close u_cursor;

4.3 存储函数

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
    RETURNS type [characteristic ...]
BEGIN
    -- SQL语句
    RETURN ...;
END ;

characteristic 说明：

• DETERMINISTIC：相同的输入参数总是产生相同的结果
• NO SQL ：不包含 SQL 语句。
• READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;
    return total;
end;
select fun1(50);

4.4 触发器

4.4.1 介绍

4.4.2 语法

create table trigger_name
before/after insert/update/delete
on tbl_name for each row -- 行级触发器
begin
    trigger_stmt ;
end;

show TRIGGERS ;

drop taigger [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数
据库 。

-- 准备工作 : 日志表 user_logs
create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;

create trigger tb_user_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
    (null, 'insert', now(), new.id, concat('插入的数据内容为:id=',new.id,',name=',new.name,', phone=', NEW.phone, ', email=', NEW.email,',profession=', NEW.profession));
end;

-- 查看
show triggers ;
-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

create trigger tb_user_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
    (null, 'update', now(), new.id,
        concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=',old.phone, ',email=', old.email, ', profession=', old.profession,
             '| 更新之后的数据: id=',new.id,',name=',new.name, ', phone=',NEW.phone, ', email=',NEW.email, ', profession=', NEW.profession));
end;

-- 查看
show triggers ;
-- 更新
update tb_user set profession = '会计' where id = 23;
update tb_user set profession = '会计' where id <= 5;

create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
    (null, 'delete', now(), old.id,
        concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=',old.phone, ', email=', old.email, ', profession=', old.profession));
end;

-- 查看
show triggers ;
-- 删除数据
delete from tb_user where id = 26;

全局锁：锁定数据库中的所有表。

表级锁：每次操作锁住整张表。

行级锁：每次操作锁住对应的行数据。

5.2 全局锁

5.2.1 介绍

5.2.2 语法

flush tables with read lock;

mysqldump -uroot –p1234 itcast > itcast.sql

unlock tables;

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

5.3 表级锁

5.3.1 介绍

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

5.3.2 表锁

• 表共享读锁（read lock）
• 表独占写锁（write lock）

lock tables 表名 read/write

unlock tables;

5.3.3 元数据锁

select ... lock in share mode

insert、update、delete、select...for update

可以通过以下SQL，查看意向锁及行锁的加锁情况：

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

​​​​​​5.4 行级锁

5.4.1 介绍

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

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

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。 ​​​​​​​

5.4.2 行锁

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

 两种行锁的兼容情况如下:

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

5.4.3 间隙锁&临键锁

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
• 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
• 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。 ​​​​​​​ 

6. InnoDB引擎

6.1 逻辑存储结构

1). 表空间

表空间是InnoDB存储引擎逻辑结构的最高层。每张表都会有一个表空间（xxx.ibd）

一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

2). 段

 段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的 非叶子节点。段用来管理多个Extent（区）。

3). 区

区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一 个区中一共有64个连续的页。 ​​​​​​​

6.3 事务原理

6.3.1 事务基础

1). 事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

 2). 特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而持久性是通过数据库的锁，加上MVCC来保证的。

重做日志(redo log)，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

回滚日志(undo log)，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制) 。  

7. MySQL管理

7.1 系统数据库

 7.2 常用工具

7.2.1 mysql

该mysql不是指mysql服务，而是指mysql的客户端工具。

语法：
mysql [options] [database]
选项：
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
-e, --execute=name #执行SQL语句并退出

7.2.2 mysqladmin

通过帮助文档查看选项：
mysqladmin --help

语法:
mysqladmin [options] command ...
选项:
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口

语法 ：
mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
-d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# 忽略掉日志中的前n行命令。
-r,--result-file=name 将输出的文本格式日志输出到指定文件。
-s, --short-form 显示简单格式， 省略掉一些信息。
--start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
--start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。

语法 ：
mysqlshow [options] [db_name [table_name [col_name]]]
选项 ：
--count 显示数据库及表的统计信息（数据库，表 均可以不指定）
-i 显示指定数据库或者指定表的状态信息
示例：
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count

7.2.5 mysqldump

语法 ：
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
连接选项 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器ip或域名
-P, --port=# 指定连接端口
输出选项：
--add-drop-database 在每个数据库创建语句前加上 drop database 语句
--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

语法 ：
mysqlimport [options] db_name textfile1 [textfile2...]
示例 ：
mysqlimport -uroot -p2143 test /tmp/city.txt

 2). source ​​​​​​​

语法 ：
source /root/xxxxx.sql