MySQL数据库笔记——进阶篇

存储引擎

MySQL体系结构

连接层：
最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层：
第二层结构主要是完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
引擎层
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
存储层：
主要是讲数据存储在文件系统之上，并完成与存储引擎的交互。

注意：索引是在存储引擎层实现的，也就意味着不同的存储引擎，索引的结构是不一样的
存储引擎控制的是数据库的数据该如何来存，如何来取，如何来组织，而具体的数据库数据最终是存储在磁盘当中的

存储引擎简介

what is 存储引擎？
do not know？
what is 引擎？
引擎就是发动机，发动机是一个机器的核心部分
而不同的引擎实际上是有不同的应用场景的（就像火箭的引擎不能放在汽车上）
引擎没有好坏，只要在合适的场景使用合适的引擎就可以了

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

没有指定的存储引擎，那就是默认的InnoDB

1、在创建表时，指定存储引擎

CREATE TABLE 表名(
	...
)ENGINE = INNODB [ COMMIT 表注释];

2、查看当前数据库支持的存储引擎

SHOW ENGINES;

memory：存储在内存当中的，通常用来做临时表及缓存

create table my_myisam(
	id int,
	name varchar(10)
)engine = MyISAM;

create table my_memory(
	id int,
	name varchar(10)
)engine = Memory;

InnoDB介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎

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

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

MyISAM

介绍;
MyISAM是MySQL早期的默认存储引擎
特点：
不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

文件
xxx.sdi；存储表结构信息
xxx,MYD：存储数据
xxx.MYI：存储索引

Memory

介绍：Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些作为临时表或缓存使用。

特点：
内存存放
能使用hash索引（默认）

文件：
xxx.sdi：存储表结构信息
为什么只有一个，因为其他的在内存里

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB：是MySQL的默认存储引擎，支持事务、外键。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB是比较适合的选择

MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事物的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。

MEMORY：将所有的数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

小结

1、体系结构
连接层、服务层、引擎层、存储层
2、存储引擎简介

SHOW ENGINES;
CREATE TABLE XXXX(.....) ENGINE = INNODB;

3、存储引擎特点
INNODB与MyISAM：事务、外键、行级锁
4、存储引擎应用
INNODB：存储业务系统中对于事务、数据完整性要求较高的核心数据
MyISAM：存储业务系统的非核心事务

索引

概述

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

普通的查找就是顺序查找，并且找到以后不会停止，会一直往下找，直到把表全找全了

索引的优缺点
优势：1、提高数据检索的效率，降低数据库的IO成本。2、通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
劣势：1、索引列也要占用空间的。2、索引大大提高了查询效率，同时却也降低更新表的速度，如对标进行INSERT、UPDATE、DELETE时，效率降低

索引结构

概述

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种

索引结构：描述
B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
Hash索引：底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES

”错综复杂的关系“

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

Btree

二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级越深，检索速度慢。
可以通过红黑树解决单向链表的问题。但是大数据量情况下，层级较深，检索速度慢

而B-tree（多路平衡查找树）
以一颗最大度数（max-degree）为5（5阶）的B-tree为例（每个节点最多存储4个key，五个指针

具体动态变化的过程可以参考网站：
https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree

以一颗最大度数（max-degree）为4（4阶）的B+tree为例：

相对于B-Tree区别
1、所有的数据都会出现在叶子节点
2、叶子节点形成一个单向链表

B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原先B+TRee的基础上，增加了一个指向相邻节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对印的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也叫做hash碰撞），可以通过链表来解决

Hash索引特点
1、Hash索引只能用于对等比较（=，in），不支持范围查询（between，>,<，…）
2、无法利用索引完成排序操作
3、查询效率搞，通常只需要一次检索就可以了，效率通常要高于B+tree索引

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

“严肃的思考题”

为什么InnoDB存储引擎选择使用B+tree索引结构？
分开思考
1、相对于二叉树，层级更少，搜索效率高。
2、相对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样会导致一页中存储的键值减少，指针跟着减少，腰痛要保存大量数据，只能增加树的高度，导致性能降低。
3、相对于Hash索引，B+tree支持范围匹配及排序操作；

索引分类

主键索引：PRIMARY：针对于表中主键创建的索引。
特点：默认自动创建，只能有一个

唯一索引：UNIQUE：避免同一个表中某数据列中的值重复
特点：可以有多个

常规索引：（没有关键字）：快速定位特定数据
特点：可以有多个

全文索引：FULLTEXT：全文索引查找的是文本中的关键词，而不是比较索引中的值
特点：可以有多个

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
聚集索引（Clustered index）：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据：必须有，而且只有一个
二级索引（Secondary Index）：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键：可以存在多个。

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

“严肃的思考题”

1、以下SQL语句，那个执行效率高？为什么？

select * from user where id = 10;
select * from user where name = 'Arm';
-- 备注：id为主键，name字段创建的有索引

第一条高，第二条还要执行一次回表查询

2、InnoDB主键索引的B+tree高度为多高呢？
假设：
一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。
高度为2时：n8+（n+1）6 = 161024，算出n约为1170，117116 = 18736
高度为3时：1171117116 = 21939856

索引语法

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,...);

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

小练习：
已知两表如下所示：

需求：
1、name字段为姓名字段，该字段的值可能会重复，为该字段创建索引

create index idx_user_name on tb_user(name);

2、phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

create unique index idx_user_phone on tb_user(phone);

3、为profession、age、status创建联合索引

create index idx_user_pre_age_sta on tb_user(profession,age,status);

4、为email建立合适的索引来提升查询效率

create index idx_user_email on tb_user(email);

5、删除索引

drop index idx_user_email on tb_user;

SQL性能分析

查看执行频次

SQL执行频率
MySQL客户端连接成功后，通过show[session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

SHOW GLOBAL STATUS LIKE 'Com____';

实操：

show global status like 'Com_______'; -- 七个下划线代表七个字符

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认十秒)的所有SQL语句的日志。
MySQL的慢性日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

-- 查看慢查询日志是否开启
show variables like 'slow_query_log';
-- 开启MySQL慢日志查询开关
slow_query_log = 1;
-- 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2;

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/va/lib/mysql/localhost-slow.log。

show profiles

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了（有些运行时间为1.9几秒的慢查询日志查不到）。
通过have_profiling参数，能够看到当前MySQL是否支持profile操作

SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

SET profiling = 1;

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

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

explian

explian或者desc命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法

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

explain执行计划
explain执行计划个字段含义：

id：
select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。

select_type
表示select的类型，常见的取值有simple（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY(SELECT/WHERE之后包含了子查询）等

type
表示连接类型，性能由好到差的来凝结类型为NULL、system、const、eq_ref、ref、range、index、all。（一般情况下是不可能优化到null的，这玩意只有不查询表的时候才会变成null；system相当于范围系统表；根据主键还有唯一索引进行访问，一般会出现const；使用非唯一性的索引进行查询时，会出现ref；all是全表扫描性能最低，尽量将type往前优化）

possible_key
显示可能应用在这张表上的索引，一个或多个。

key
实际使用的索引，如果为null，则没有使用索引

key_len
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows
MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个预估值，可能并不总是准确的

filtered
表示返回结果的函数占需读取行数的百分比，filtered的值越大越好。

索引使用规则

验证索引效率

在未建立索引之前，执行如下SQL语句，查看SQL的耗时

SELECT * FROM tb_sku WHERE sn = '100000003145001';

针对字段创建索引

create index idx_sku_sn on tb_sku(sn);

然后再次执行相同的SQL语句，再次查看SQL的耗时

SELECT * FROM tb_sku WHERE sn = '100000003145001';

最左前缀法则

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

范围查询

联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效

索引失效情况一

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

例如：

explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引号
字符串类型字段使用时，不加引号，索引将失效

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

索引失效情况二

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

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

数据分布影响：
如果MySQL评估使用索引比全表更慢，则不会使用索引，而是用全表扫描

SQL提示

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

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

ignore index

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

force index

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

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部都能够找回），减少select *。

using index condition：查找使用了索引，但是需要回表查询数据
using where；using index：查找使用了索引，但是需要的数据都在索引列中都能找到，所以不需要回表查询数据

小思考
一张表，有四个字段（id，username，password，status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案

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

前缀索引

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

语法

create index idx_xxx on table_name(column(n));

前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

-- 求取索引选择性
select count(distinct email) / count(*) from tb_user;
select count(distinct sunstring(email,1,5) / count(*) from tb_user ;

单列索引与联合索引的选择

单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引而非单列索引。

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

索引的设计原则

1、针对于数据量大的，且查询比较频繁的表建立索引。
2、针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4、容易过时字符串类型的字段，字段的长度较长，可针对于字段的特点，建立前缀索引。
5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改查的效率。
7、如果索引列不能存储null值，请在创建表时使用not null约束它。当优化器直到每列是否包含null值时，他可以更好地确定哪个索引最有效的用于查询。

总结

1、索引概述
索引是高效获取数据的数据结构；

2、索引结构
B+Tree
Hash

3、索引分类
主键索引、唯一索引、常规索引、全文索引、聚集索引、二级索引

4、索引语法

create [unique] index xxx on xxx(xxx);
show index from xxx ;
drop index xxx on xxx;

5、SQL性能分析
执行频次、慢查询日志、profile、explain

6、索引使用
联合索引
索引失效
SQL提示
覆盖索引
前缀索引
单列/联合索引

7、索引设计原则
表
字段
索引

SQL优化

其中包含：
插入数据
主键优化
order by优化
group by优化
limit优化
count优化
update优化

插入数据

insert优化：
批量优化

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

手动提交事务

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;

主键顺序插入

主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入：1 2 3 4 5 6 7 8 9 15 21 88 89

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

# 客户端连接服务端时，加上参数 --local-infile
mysql--local-infile -u root -p
# 设置全局参数local_infile = 1；
set global local_infile = 1;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主键顺序插入性能高于乱序插入

主键优化

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

页分裂（现象）
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据多大，会行溢出），根据主键排列

页合并（现象）
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标示为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则
满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。

order by优化

1、Using filesort：通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序 缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FIleSort排序
2、Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率极高。

# 没有创建索引时，根据age，phone进行排序
explain select id,age,phone from tb_user order by age,phone;
# 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);
# 创建索引后，根据age，phone进行升序排序
explain select id,age,phone from tb_user order by age,phone;
# 创建索引后，根据age，phone进行降序排序
explain select id,age,phone from tn_user order by age desc , phone desc;

一个升序一个降序

# 根据age，phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc,phone desc;
# 创建索引
create index idx_user_age_phone_ad on tb_user(age asc, phone desc);
# 根据age，phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc , phone desc;

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）。

group by优化

# 删除掉目前的联合索引idx_user_pro_age_sta
drop index idx_user_pro_age_sta on tb_user;
# 执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession;
# 创建索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);
# 执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession;
# 执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession , age;

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;

count优化

explain select count(*) from tb_user;

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count（* ）的时候会直接返回这个数，效率很高；

innodb引擎就麻烦了，它执行count（* ）的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数
优化思路：自己计数。

count的几种用法
count（）是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数地参数不是null，累计值就加一，否则就不加，最后返回累计值
用法：count（*）、count（主键）、count（字段）、count（1）

count（主键）
InnoDB引擎会遍历整张表，把每一行地主键id都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。

count（字段）
没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
有not null约束：InnoDB引擎会便利整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count（1）
InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

count（*）
InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count（字段)<count（主键 id）<count（1）≈count（*）

所以尽量使用count（*）。

update优化

update student set no = '2000100100' where id = 1;

update student set no = '2000100105' where name = '韦一笑';

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

小总结

1、插入数据
insert：批量插入、手动控制事务、主键顺序插入
大批量插入：load data local infile
2、主键优化
主键长度尽量短、顺序插入 AUTO_INCREMENT
3、order by优化
using index：直接通过所以返回数据，性能高
using filesort：需要将返回的结果在排序缓冲区排序
4、group by优化
索引，多字段分组满足最左前缀法则
5、limit优化
覆盖索引+子查询
6、count优化
性能：count（字段）<count（主键 id）<count（1）≈count（*）
7、update优化
尽量根据主键/索引字段进行数据更新

视图

介绍及基本语法

什么是视图？
试图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的
通俗的讲，视图只保存了查询SQL逻辑，不保存串查询结果。所以我们在创建视图的时候，主要的工作就落在了创建这条SQL查询语句上。

创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL ] CHECK OPTION ]

小实操

-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;

查询

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

小实操

show create view stu_v_1;

select * from stu_v_1;

select * from stu_v_1 where id < 3;

# 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <=10;

alter view stu_v_1 as select id,name from student where id <= 10;

删除

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

小实操

drop view if exists stu_v_1;

检查选项

select * from stu_v_1;
insert into stu_v_1 values(6,'Tom');

create or replace view stu_v_1 as select id,name from student where id <= 20 with cascaded check option;

这条语句可以组织id>20的数据插入,若是操作了这样的数据,会报错

视图的检查选项
当使用with check option子句创建视图时,MySQL会通过试图检查正在更改的每个行,例如:插入,更新,删除,以使其符合试图的定义.MySQL允许基于另一个视图创建试图,他还会检查以来试图中的规则以保持一致性.为了确定检查的范围,mysql提供了两个选项,CASCADED和LOCAL,默认值为CASCADED.

在编写的时候一定要在后面加上with cascade/local check option不然只会执行不会判断

更新和作用

视图的更新
要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系.如果视图包含以下任何一项,则该视图不可更新:
1、聚合函数或窗口函数(SUM()、MIN()、MAX()、COUNT()等)
2、DISTINCT
3、GROUP BY
4、HAVING
5、UNION或 UNION ALL

作用
简单:视图不仅可以简化用户对数据的理解,也可以简化他们的操作.那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件
安全:数据库可以授权,但不能授权到数据库特定行和特定的列上.通过视图用户只能查询和修改他们所能见到的数据
数据独立:数据可帮助用户屏蔽真实表结构变化带来的影响.

案例

需求:
1、为了保证数据库的安全性,开发人员在操作tb_user表时,只能看到用户的基本字段,屏蔽手机号和邮箱两个字段

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

2、查询每个学生所选修的课程(三张表联查，这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图

select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;

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;

存储过程

介绍

在实际应用场景中可能需要多次操作数据库，一个逻辑中可能需要操作多次数据库，那么就意味着会涉及到多次网络请求。

存储过程是实现经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

存储过程的特点
封装和复用
可以接受参数，也可以返回数据
减少网络交互，效率提升

基本语法

创建

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

调用

call 名称([参数]);

小实操

create procedure p1()
begin
	select count(*) from student;
end

call p1();

查看

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

删除

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

小实操

select * from information_schema.routines where routine_schema = 'itcast';
drop procedure if exists p1;

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

变量

系统变量

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

查看系统变量

show [ session | global ] variables; -- 查看所有系统变量
show [ session | global ] variables like '...'; -- 可以通过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;

注意：
如果没有指定session/global，默认是session，会话变量。
mysql服务器重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置。

用户变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用‘@变量名’使用就可以。其作用域为当前连接（当前会话）。

赋值

set @var_name = expr[,@var_name = expr] ...;
set @var_name := expr [,@var_name := expr] ...;

select @var_name := expr [,@var_name := expr] ...;
select 字段名 into @vsr_name from 表名;

使用

select @var_name;

小实操

set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @myname,@myage,@mygender,@myhobby;

select @mycolor := 'red';
select count(*) into @Mycount from tb_user;
select @mycolor,@mycount;

注意：
用户定义的变量无需对其进行声明或者初始化，只不过获取到的值为null。

局部变量

局部变量是更具需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN…END块。

声明

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

变量类型就是数据库字段类型：int、begint、char、varhcar、date、time等。

赋值

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

小实操

create procedure p2();
begin
	declare stu_count int default 0;
	select count(*) into stu_count from student;
	select stu_count;
end;

if判断

语法

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

小实操
需求：
根据定义的分数score变量，判断当前分数对应的分数等级。
1、score >= 85分，等级为优秀
2、score >= 60分 且 score < 85 , 等级为及格。
3、score < 60分，等级为不及格。

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;

参数

类型：含义：备注
in：该类参数作为输入，也就是需要调用时传入值：默认
out：该类参数作为参数，也就是该参数可以作为返回值
inout：既可以作为输入参数，也可以作为输出参数

用法：

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

小实操
需求：
1、根据传入的分数score变量，判断当前分数对应的分数等级，并返回。
1、score >= 85分，等级为优秀
2、score >= 60分 且 score < 85 , 等级为及格。
3、score < 60分，等级为不及格。

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;
call p4(68,@result);

2、将传入的200分值的分数，进行换算，换算成百分制，然后返回

create procedure p5(inout score double)
begin
	set score := score*0.5;
end;
set @score = 78;
call p5(@score);
select @score;

case

语法

case case_value
	when when_value1 then statement_list1
	[ when when_value2 then statement_list2]...
	[ else statement_list ]
end case;

case case_value
	when search_value1 then statement_list1
	[ when search_value2 then statement_list2]...
	[ else statement_list ]
end case;

小实操
需求:
根据传入的月份,判定月份所属的季节(要求采用case结构)。
1~3月份,为第一季度
4~6月份,为第二季度。
7~9月份,为第三季度。
10~12月份,为第四季度

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;

call p6(4);

炫酷的循环

while

while循环是有条件的循环控制语句。满足条件后,再执行循环体中的SQL语句。具体语法为:

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

小实操:
需求:计算从1累加到n的值,n为传入的参数值

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(10);

repeat

repeat是有条件的循环控制语句,当满足条件的时候退出循环。具体语法为:

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

小实操
需求:计算从1累加到n的值,n为传入的参数值

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;

callp8(10);

loop

loop实现简单的循环,如果不在SQL逻辑中增加退出循环的条件,可以用其来实现简单的死循环。loop可以配合一下两个语句使用:
leave:配合循环使用,退出循环
iterate:必须用在循环中,作用是跳过当前循环剩下的语句,直接进入下一次循环。

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

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

小实操
1、计算从1累加到n的值,n为传入的参数值

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(10);

2、计算1从n之间的偶数累加的值,n为传入的参数值

create procedure p10(in n int)
begin
	declare total int default 0;
	sum:loop
		if n<=0 then
			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(10);

游标

游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch和close,其语法分别如下。

# 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
# 打开游标
OPEN 游标名称;
# 获取游标记录
FETCH 游标名称 INTO 变量[,变量 ];

小实操
需求:根据传入的参数uage,来查询用户表tb_user中,所有的用户年龄小于等于uage的用户姓名(name)和专业(profession),并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)

1、声明游标,存储查询结果集
2、准备:创建表结构
3、开启游标
4、获取游标中的记录
5、插入数据到信标中
6、关闭游标

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(
		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(40)

条件处理程序

条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体的语法为:

declare handler_action handler for cindition_value [,condition_value] ... statement;

handler_action
	continue:继续执行当前程序
	exit:终止执行当前程序
condition_value
	sqlstate sqlstate_value:状态码,如02000
	sqlwarning:所有以01开头的sqlstate代码的简写
	not found:所有以02开头的sqlstate代码的简写
	sqlexception:所有没有被sqlwarning或not found捕获的sqlstate代码的简写

改写

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;
	declare exit handler for sqlstate '02000' close u_cursor;
	drop table if 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);

mysql官方文档,可以查询状态码
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

存储函数

what is 存储函数
存储函数是由返回值的存储过程,存储函数的参数只能是in类型的。具体语法如下

create function 存储函数名称([参数列表])
returns type [characteristic ...]
begin
	-- SQL语句
	return ...;
end;

characteristic说明
determinisitc:相同的输入参数总是产生相同的结果
no sql:不包含sql语句。
reads sql data:包含读取数据的语句,但不包含写入数据的语句。

小实操
需求:从1到n的累加

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(100);

触发器

介绍

触发器是与表有关的数据库对象,指在inset/update/delete之前或之后,触发并执行触发器中定义的sql语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。

触发器类型:NEW和OLD
insert型触发器:new表示将要或者已经新增的数据
update型触发器:OLD表示修改之前的数据,NEW表示将要或已经修改后的数据
delete型触发器:OLD表示将要或者已经删除的数据

语法

创建

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

查看

show triggers;

删除

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

小实操
需求:通过触发器记录tb_user表的数据变更日志,将变更日志插入到日志表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_parans) 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 ;

-- 删除
drop trigger tb_user_insert_trigger;

-- 插入数据到tb_user
insert into tb_user(id,name,phone,profession,age,gender,status,createtime) values (25,'二皇子','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_parans) 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 age = 20 where id = 23;

-- 删除数据触发器
create trigger tb_user_delete_trigger
	after delete on tb_user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_parans) 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 = 25;

about 视图、存储过程、触发器的小结

1、视图(VIEW)
虚拟存在的表,不保存查询结果,只保存查询的SQL逻辑
简单、安全、数据独立

2、存储过程(PROCEDURE)
事先定义并存储在数据库中的一段SQL语句的集合
减少网络交互,提高性能、封装重用
变量、if、case、参数(in/out/inout)、while、repeat、loop、cursor、handler

3、存储函数(FUNCTION)
存储函数是有返回值的存储过程,参数类型只能为IN类型
存储函数可以被存储过程替代

4、触发器(TRIGGER)
可以在表数据进行insert、update、delete之前或之后触发
保证数据完整性、日志记录、数据校验

锁

介绍

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

在mysql中的锁,按照锁的粒度分,分为以下三类:
1、全局锁:锁定数据库中的所有表。
2、表级锁:每次操作锁住整张表。
3、行级锁:每次操作锁住对应的行数据。

全局锁

介绍

全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML的写语句,DDL语句,已经更新操作的事物提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。

加了全局锁以后,在备份数据时,创建和修改操作处于阻塞状态,在完成备份之前只能进行读取操作

特点

数据库中加全局锁,是一个比较重的操作,存在以下问题:
1、如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
2、如果从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志,会导致主从延迟。

在InnoDB引擎中,我们可以在备份时机上参数

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

来完成不加锁的一致性数据备份

表级锁

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

对于表级锁,主要分为以下三类
1、表锁
2、元数据锁(meta data lock , MDL)
3、意向锁

表锁

对于表锁,分为两类
1、表共享读锁(read lock)
2、表独占写锁(write lock)

语法:
1、加索:lock tables 表名… read/write
2、释放锁:unlock tables / 客户端断开连接

加了读锁
都能读到,但是不能写

加了写锁,自己加的只有自己能读写,在没有解锁之前别人读写不了

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

元数据锁

MDL加锁过程是系统自动控制,无需显示使用,在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作。为了避免DML和DDL冲突,保证读写的正确性

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加DML读锁（共享）；当对表结构进行变更操作的时候，加DML写锁（排他）。

对应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自动上锁，右边的alter排他锁就只能阻塞。当左侧commit提交以后，右边才能执行

查看元数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

意向锁的分类
1、意向共享锁（IS）：由语句select…lock in share mode 添加
2、意向排他锁（IX）：由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;

行级锁

介绍

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

InnoDB的数据是基于索引组织的，行锁是通过对索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：
1、行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持。
2、间隙锁（Gap Lock）：锁定索引记录间隙（不包含该记录），确保索引记录间隙不，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
3、临建锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

行锁

InnoDB实现了以下两种类型的行锁
1、共享锁（S）：允许一个事务去读一行，组织其他事务获得相同数据集的排他锁。
2、排他锁（X）：允许获取排他锁的事务更新数据，组织其他事务活得相同数据集的共享锁和排他锁。

insert：排他锁：自动加锁
update：排他锁：自动加锁
delete：排他锁：自动加锁
select：不加任何锁
select … lock in share mode ：共享锁：需要手动在select之后加lock in share mode
select … for update ：排他锁：需要手动在select之后加for update

默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用next-key 锁进行搜索和索引扫描，以防止幻读。
1、针对唯一索引进行索引时，对已存在的记录进行等值匹配时，将会自动优化为行锁
2、InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

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

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

间隙锁/临建锁

默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。
1、索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
2、索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
3、索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个事务上采用间隙间隙锁。

小结

1、概述
在并发访问时，解决数据访问的一致性、有效性问题
全局锁、表级锁、行级锁

2、全局锁
对整个数据库实例加锁，加锁后整个实例就处于只读状态
性能较差，数据逻辑备份时使用

3、表级锁
操作锁住整张表，锁定粒度大，发生锁重复的概率高
表锁、元数据锁、意向锁

4、行级锁
操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低
行锁、间隙锁、临建锁

InnoDB引擎

逻辑存储结构

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

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

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

页，是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4~5个区。

行，InnoDB存储引擎数据是按行进行存放的。

Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋为trx_id隐藏列。

Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到记录修改前的信息。

架构

MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

内存架构

Buffer Pool：缓冲区是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲区中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲区以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
free page：空闲page，未被使用。
clean page：被使用page，数据没有被修改过。
dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

Change Buffer：更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而将数据变更存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer的意义是什么？
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树种不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO

Adaptive Hash Index：自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
自适应哈希索引，无需人工干预，是系统根据情况自动完成。
参数：adaptive_hash_index

Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。
参数：
innodb_log_buffer_size:缓冲区大小
innodb_flush_log_at_trx_commit:日志刷新到磁盘时机

about 磁盘时机
1：日志在每次事务提交时写入并刷新到磁盘
0：每秒将日志写入并刷新到磁盘一次
2：日志在每次事务提交后写入，并每秒刷新到磁盘一次。

磁盘架构

System Tablespace:系统表空间是更改缓冲区的存储区域。如果表实在系统表空间而不是每个表文件或通用表空间中创建的,它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)
参数:innodb_data_file_path

File-Per-Table Tablespaces:每个表的文件表空间包含单个InnoDB表的数据和索引,并存储在文件系统上的单个数据文件中。
参数:innodb_file_per_table

General Tablespaces:通用表空间,需要通过create tablespace语法创建通用表空间,在创建表时,可以指定该表空间

# 创建General Tablespaces语法
create tablespace xxx add
datafile 'file_name'
engine = engine_name;

Undo Tablespaces:撤销表空间,MySQL实例在初始化时会创建两个默认的undo空间(初始大小16M),用于存储undo log日志。

Temporary Tablespaces:InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

Doublewrite Buffer Files:双写缓冲区,innodb引擎将数据页从Buffer Pool刷新到磁盘前,先将数据页写入双写缓冲区文件中,便于系统异常时恢复数据。

Redo Log:重做日志,是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log),前者是在内存中,后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于刷新脏页到磁盘中,发生错误时,进行数据恢复使用。

后台线程

1、Master Thread
核心后台线程,负责调度其他线程,还负责将缓冲区中的数据异步刷新到磁盘中,保持数据的一致性,还包括脏页的刷新、合并插入缓存、undo页的回收。

2、IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求,这样可以极大地提高数据库地性能,而IO Thread主要负责这些IO请求的回调。

线程类型:默认个数:职责
Read thread:4:负责读操作
Write Thread:4:负责写操作
Log thread:1:负责将日志缓冲区刷新到磁盘
Insert buffer thread:1:负责将写的缓冲区内容刷新到磁盘

3、Purge Thread
主要用于回收事物已经提交了的undo log,在提交事物之后,undo log可能不用了,就用它来回收。

4、Page Cleaner Thread
协助Master Thread 刷新脏页到磁盘地线程,它可与i减轻Master Thread的工作压力,减少阻塞

事务原理

概述

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

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

redo log

重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性。
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中,后者在磁盘中。当事务提交之后会把所有修改信息都存到哦该日志文件中,用于刷新脏页到磁盘,发生错误时,进行数据恢复使用。

redolog buffer会将修改的数据备份记录到磁盘结构,如果提交时出现错误,就会自动修复。
直接将缓冲区中的数据修改到磁盘结构会有很大的性能问题,会造成大量的随机磁盘IO

undo log

回滚日志,用于记录数据被修改前的信息,作用包含两个:提供回滚和MVCC(多版本并发控制)。
undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁:undo log在事务提交执行时,事务提交时,并不会立即删除undo log,因为这些日志可能还用于MVCC。
Undo log存储:undo log采用段的方式进行管理和记录,存放在前面介绍的rollback segment回滚段中,内部包含1024个undo logsegment。

MVCC

基本概念

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... in share mode (共享锁)，select ... for update 、update、insert、delete（排他锁）都是一种当前读。

快照读

简单的select（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed :每次select，都生成一个快照读。
Reapeatable Read：开启事务后第一个select语句次啊是快照读的地方。
Serializable：快照读会退化为当前读。

MVCC

全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

实现原理

记录中的隐藏字段

DB_TRX_ID：最近修改事务的ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID（不是都会有的，没有主键的时候才会有）：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

undolog版本链

undo log：

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只有回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读也需要，不会立即被删除。

undo log版本链

不同事物或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readview

ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
ReadView中包含了四个核心字段：
m_ids：当前活跃的事务ID集合
min_trx_id：最小活跃事务ID
max_trx_id：预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id：ReadView创建者的事务ID

不同的隔离级别，生成ReadView的时机不同：
READ COMMITTED：在事务中每一次执行快照读时生成ReadView
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

RC隔离级别下，在事务中每一次执行快照读时生成ReadView
RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

小总结

1、逻辑存储结构
表空间、段、区、页、行

2、架构
内存结构
磁盘结构

3、事务原理
原子性 - undo log
持久性 - redo log
一致性 - Indo log + redo log
隔离锁 - 锁 +MVCC

4、MVCC
记录隐藏字段、undo log版本链、readView

MySQL管理

系统数据库

MySQL数据库安装完成之后，自带了四个数据库
mysql：存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
information_schema：提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
performance_schema：为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys：包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

常用工具

mysql

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

语法

mysql [options] [database]

选项

-u,--user=name                 #指定用户名
-p,--password[=name]           #指定密码
-h.--host=name                 #指定服务器ip或域名
-p,--port=port                 #指定连接端口
-e,--execute=name              #执行SQL语句并退出

-e选项可以在MySQL客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便

#such as
mysql -uroot -p123456 db01 -e "select * from stu";

mysqladmin

mysqladmin是一个执行管理操作的客户端程序。可以用它来检测服务器的配置和当前状态、创建并删除数据库等。

通过帮助文档查看选项

mysqladmin --help

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具。

语法

mysqlbinlog [options] log-files1 log-files2 ...

选项

-d.--database=name                                   #指定数据库名称，只列出指定的数据库相关操作。
-o,--offset=#                                        #忽略掉日志中的前n行命令
-r,--result-file=name                                #将输出的文本格式日志输出到指定文件
-s,--short-from                                      #显示简单格式，忽略掉一些信息
--start-datatime=date1 --stop-datetime=date2         #指定日期间隔内的所有日志
--start-position=post1 --stop-position=post2         #指定位置间隔内的所有日志

mysqlshow

mysqlshow客户端对象查找工具，用来很快的查找存在哪些数据库、数据库中的表、表中的列或者索引

语法

mysqlshow [options] [db_name [table_name[col_name]]]

选项

--count        #显示数据库及表的统计信息（数据库，表均可以不指定）
-i             #显式指定数据库或者指定表的状态信息

示例

#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p2143 --count

#查询set库中的每个表的字段书，及行数
mysqlshow -uroot -p2143 test --count

#查询est库中book表的详细情况
mysqlshow -uroot -p2143 test book --count

mysqldump

mysqldump客户端工具用来本分数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

语法

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/source

mysqlimport是客户端数据导入工具，用来导入MySQLdump加-T参数后导出的文本文件。

语法

mysqlimport [options] db_name textfile1 [textfile2...]

如果需要导入sql文件，可以使用mysql中的source指令：
语法：

source/root.xxxxx.sql

小总结

1、mysql
Mysql客户端工具，-e执行SQL并推出

2、mysqladmin
Mysql管理工具

3、mysqlbinlog
二进制日志查看工具

4、mysqlshow
查看数据库、表、字段的统计信息

5、mysqldump
数据备份工具

6、mysqlimpotr/source
数据导入工具