MySQL入门到精通——进阶篇（基础篇——进阶篇——运维篇）本文以MySQL8.0版本以上为例_mysql数据库入门到精通——运维篇.

此文档来源于网络,如有侵权，请联系删除！

前言

MySQL 是一个 关系型数据库管理系统 。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

SQL 是结构化查询语言（Structured Query Language）的英文首字母，它是一种专门用来与数据库通信的语言。

---

MySQL——进阶篇

一、存储引擎

1.存储引擎-MySQL体系结构

• 连接层

最上层是一些客户端和连接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它具有的操作权限。

• 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。

• 引擎层

存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

• 存储层

主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

2.存储引擎-简介

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

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

  CREATE TABLE 表名(
  	字段1 字段1类型 [COMMENT 字段1注释],
  	......
  	字段n 字段n类型 [COMMENT 字段n注释]
  )ENGINE=InnoDB [COMMENT 表注释];
  1
  2
  3
  4
  5

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

  SHOW ENGINES;
  1

3.存储引擎-InnoDB介绍

• InnoDB

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

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

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

• 查看控制表空间（ON打开代表每张表对应一个表空间，OFF关闭代表多张表公共一个表空间）

  SHOW variables like 'innodb_file_per_table';
  1

  

4.存储引擎-MyISAM和Memory

• MyISAM

  介绍： MyISAM是MySQL早期的默认存储引擎。

  特点：
  1.不支持事务，不支持外键
  2.支持表锁，不支持行锁
  3.访问速度快

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

• Memory

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

  特点：
  1.内存存放
  2.hash索引（默认）

  文件：
  xxx.sdi：存储表结构信息

• 存储引擎特点

  特点	InnoDB	MyISAM	Memory
  存储限制	64TB	有	有
  事务安全	支持	-	-
  锁机制	行锁	表锁	表锁
  B+tree索引	支持	支持	支持
  Hash索引	-	-	支持
  全文索引	支持(5.6版本之后)	支持	-
  空间使用	高	低	N/A
  内存使用	高	低	中等
  批量插入速度	低	高	高
  支持外键	支持	-	-

5.存储引擎-选择

• 存储引擎-选择

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

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

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

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

二、索引

1.索引-概述

• 介绍

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

• 优缺点

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

2.索引-结构

2.1.索引-结构-介绍

• 索引-结构-介绍

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

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

  索引	InnoDB	MyISAM	Memory
  B+Tree索引	支持	支持	支持
  Hash索引	不支持	不支持	支持
  R-tree索引	不支持	支持	不支持
  Full-text	5.6版本之后支持	支持	不支持

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

2.2.索引-结构-Btree

• 二叉树

  二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

  红黑树：大数据量情况下，层级较深，检索速度慢。

• B-Tree（多路平衡查找树）

  以一颗最大度数（max-degree）为5（5阶）的b-tree为例（每个节点最多存储4个Key，5个指针）：

  插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 数据为例。

  

  点击前往B-Tree演示

  点击前往演示链接

2.3.索引-结构-B+tree

• B+Tree

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

  插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 数据为例。

  

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

• B+Tree

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

  点击前往B+Tree演示

2.4.索引-结构-hash

• 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支持范围匹配及排序操作；

3.索引-分类

• 索引-分类

  分类	含义	特点	关键字
  主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
  唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
  常规索引	快速定位特定数据	可以有多个
  全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

  在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

  分类	含义	特点
  聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
  二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

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

4.索引-语法

• 创建索引

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

  例子

  #在student表中为字段name创建一个名为index_student_name的索引（单字段索引）
  create index index_student_name on student(name);
  #在student表中创建一个联合索引（字段name+age+grade）
  create index index_student_name_age_grade on student(name,age,grade);
  1
  2
  3
  4

• 查看索引

  SHOW INDEX FROM table_name;
  1

  例子（这是一个联合索引）

• 删除索引

  DROP INDEX index_name ON table_name;
  1

5.索引-性能分析

5.1.索引-性能分析-查看执行频次

• SQL执行频率

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

  SHOW GLOBAL STATUS LIKE 'Com_______';
  1

  
  查看当前数据库增、删、改、查哪种频次比较多

5.2.索引-性能分析-慢查询日志

• 慢查询日志

  #查看慢查询日志是否开启
  show variables like 'slow_query_log';
  1
  2

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

  #开启MySQL慢日志查询开关
  slow_query_log=1
  #设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
  long_query_time=2
  1
  2
  3
  4

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

5.3.索引-性能分析-show profiles

• profile详情

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

  SELECT @@have_profiling;
  1

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

• 查看profiling是关闭还是开启的

  SELECT @@profiling;
  1

• 设置profiling是关闭还是开启

  SET profiling=1;	#0代表关闭	1代表开启
  1

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

• 查看每一条SQL的耗时基本情况

  show profiles;
  1

  示例
  

• 查看指定query_id的SQL语句各个阶段的耗时情况

  show profile for query query_id;
  1

• 查看指定query_id的SQL语句CPU的使用情况

  show profile cpu for query query_id;
  1

5.4.索引-性能分析-explain

• explain执行计划

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

  语法：

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

  

• 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。
  possible_keys	显示可能应用在这张表上的索引，一个或多个
  key	实际使用到的索引，如果为NULL，则没有使用索引。
  key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
  rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
  filtered	表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

6.索引-使用规则

6.1.索引-使用规则-最左前缀法则

• 最左前缀法则

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

  例子

  前提是创建了一个联合索引，联合索引字段1为name，字段二为age，字段三为grade（查看key字段是否使用联合索引，查看key_len字段的长度，使用了哪几个字段的索引）

  
  

  #使用了联合索引（索引字段为name+age+grade）
  explain select * from student where name = '张三' and age = 18 and grade = '初中';
  #使用了联合索引（索引字段为name+age,grade字段索引失效）
  explain select * from student where name = '张三' and age = 18;
  #使用了联合索引（索引字段为name，age和grade字段索引失效）
  explain select * from student where name = '张三';
  #未使用联合索引（最左列name字段不存在，导致索引失效）
  explain select * from student where age = 18 and grade = '初中';
  #未使用联合索引（最左列name，age字段不存在，grade索引失效）
  explain select * from student where grade = '初中';
  #使用了联合索引（索引字段为name,因为age在grade字段的左侧，未使用倒age字段，导致grade字段索引失效）
  explain select * from student where name = '张三' and grade = '初中';
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

• 范围查询

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

  例子

  #使用了联合索引（索引字段为name,age使用了范围查询，grade字段索引失效）
  explain select * from student where name = '张三' and age > 18 and grade = '初中';
  1
  2

  业务允许的情况下，尽量使用">="或者"<="来规避。

6.2.索引-使用规则-索引失效情况一

• 索引列运算

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

  例子
  

  #phone索引失效
  explain select * from student where substring(phone,10,2) = '35';
  1
  2

• 字符串不加引号

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

  例子

  #phone索引失效(字符串没加引号)
  explain select * from student where phone = 13166246235;
  1
  2

• 模糊查询

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

  例子

  #头部模糊匹配，索引失效
  explain select * from student where phone like '%35';
  1
  2

6.3.索引-使用规则-索引失效情况二

• or连接的条件

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

  例子

  #id(主键索引)，age(没有索引)，索引使用失效
  explain select * from student where id = 2 or age = 20;
  1
  2

• 数据分布影响

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

6.4.索引-使用规则-SQL提示

• SQL提示

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

  例子

  #name字段即是一个单独索引也是一个联合索引（实际使用了联合索引）
  explain select * from student where name = '张三';
  1
  2

  
  

  use index;（告诉数据库建议使用哪个索引——建议数据库使用某个索引）

  #指定使用index_student_name索引
  explain select * from student use index(index_student_name) where name = '张三';
  1
  2

  ignore index;（告诉数据库忽略使用哪个索引——忽略不使用某个索引）

  #指定不使用index_student_name索引
  explain select * from student ignore index(index_student_name) where name = '张三';
  1
  2

  force index;（告诉数据库必须使用这个索引——强制使用某个索引）

  #必须使用index_student_name索引
  explain select * from student force index(index_student_name) where name = '张三';
  1
  2

6.5.索引-使用规则-覆盖索引&回表查询

• 覆盖索引

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

  例子

  explain select id,name,age from student where name = '张三' and age = 16 and grade = '初中';
  1

  

  explain select id,name,age,grade,phone from student where name = '张三' and age = 16 and grade = '初中';
  1

  

  Using where（在查找使用索引的情况下，需要回表去查询所需的数据）

  Using where; Using index（查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据）

  Using index condition（查找使用了索引，但是需要回表查询数据）

  using index（使用覆盖索引的时候就会出现，测试发现主键索引也会生效）

6.6.索引-使用规则-前缀索引

• 前缀索引

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

  语法

  create index 索引名 on 表名(字段名(n));
  1

  例子

  #为phone字段创建一个名为student_phone_1_5的索引，使用phone字段的前五个字符来作为索引条件
  create index student_phone_1_5 on student(phone(5));
  1
  2

  

  前缀长度

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

  #计算某个字段作为索引的效率
  select count(distinct 字段名) / count(*) from 表名;
  1
  2

  例子

  #计算name字段作为索引效率
  select count(distinct name) / count(*) from student;
  1
  2

  

  #计算某个字段截取某几个字符作为索引的效率
  select count(distinct substring(字段名,开始截取字符的位置,截取结束的字符位置)) / count(*) from 表名;
  1
  2

  例子

  #计算phone字段前5个字符作为索引的效率
  select count(distinct substring(phone,1,5)) / count(*) from student;
  1
  2

6.7.索引-使用规则-单列&联合索引

• 单列索引与联合索引

  单列索引： 即一个索引只包含单个列。
  联合索引： 即一个索引包含了多个列。

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

  单列索引情况

  

  #可能会使用到的索引有三个，实际只使用到了phone字段创建的索引index_student_phone
  explain select id,phone,name from student where phone = '13266246234' and name = '张三';
  1
  2

  

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

7.索引-设计原则

• 索引设计原则

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

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

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

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

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

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

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

8.索引-小结

• 1.索引概述

  索引是高效获取数据的数据结构；

• 2.索引概述

  B+Tree
  Hash

• 3.索引概述

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

• 4.索引概述

  #创建索引
  create [unique] index 索引名 on 表名(字段名);
  #查看索引
  show index from 表名;
  #删除索引
  drop index 索引名 on 表名;
  1
  2
  3
  4
  5
  6

• 5.SQL性能分析

  执行频次、慢查询日志、profile、explain

• 6.索引使用

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

• 7.索引设计原则

  表
  字段
  索引

三、SQL优化

1.SQL优化-插入数据

• insert优化

  1.批量插入

  #插入500~1000条数据时使用批量插入
  insert into 表名 values (值1，值2...值n),(值1，值2...值n),(值1，值2...值n);
  1
  2

  2.手动提交事务

  start transaction;
  insert into 表名 values (值1，值2...值n),(值1，值2...值n),(值1，值2...值n);
  insert into 表名 values (值1，值2...值n),(值1，值2...值n),(值1，值2...值n);
  commit;
  1
  2
  3
  4

  3.主键顺序插入

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

  4.主键顺序插入

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

  第一步

  #客户端连接服务器端时，加上参数 --local-infile
  mysql --local-infile -u root -p
  1
  2

  第二步

  #查看全局参数local_infile开关是否开启（1为开启，0为关闭）
  select @@local_infile;
  1
  2

  
  全局参数local_infile开关已打开就不需要执行弟三步了
  第三步

  #设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
  set global local_infile = 1;
  1
  2

  #执行load指令将准备好的数据，加载到表结构中
  load data local infile '数据文件路径' into table '表名' fields terminated by '每个字段之间分隔符' lines terminated by '每一行数据之间的分隔符';
  1
  2

  例子

  #将sql.log文件里面的数据导入到数据库中（文件中字段与字段之间用','分割，行与行之间用'\n'换行分割）
  load data local infile '/root/load_student.sql' into table 'student' fields terminated by ',' lines terminated by '\n';
  1
  2

  文件内容展示

  

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

2.SQL优化-主键优化

• 数据组织方式

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

• 页分裂

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

  
  

• 页合并

  当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许其他记录声明使用。

  当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

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

• 主键设计原则

  满足业务需求的情况下，尽量降低主键的长度。

  插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

  尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

  业务操作时，避免对主键的修改。

3.SQL优化-order by优化

• order by优化

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

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

  例子

  #没有创建索引时,根据age,phone进行升序排序(可以发现Extra字段值为Using filesort)
  explain select id,age,phone from student order by age,phone;
  1
  2

  

  #创建索引
  create index index_student_age_phone on student(age,phone);
  1
  2

  

  #创建索引后,根据age,phone进行升序排序(可以发现Extra字段值为Using index)
  explain select id,age,phone from student order by age,phone;
  1
  2

  

  #创建索引后,根据age进行升序排序,phone进行降序排序(可以发现Extra字段值为Using index; Using filesort)
  explain select id,age,phone from student order by age asc ,phone desc;
  1
  2

  

  #创建索引(age为升序,phone为降序)如果执行语句只有索引降序不生效跟数据库版本有关系(需要数据库版本8.0以后)
  create index index_student_age_phone_ad on student(age asc ,phone desc);
  1
  2

  

  #创建索引后,根据age进行升序排序,phone进行降序排序(可以发现Extra字段值为Using index)
  explain select id,age,phone from student order by age asc ,phone desc;
  1
  2

  

  1.根据排序字段建立合适的索引,多字段排序时,也遵顼最左前缀法则。

  2.尽量使用覆盖索引。

  3.多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。

  4.如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

  #查看缓冲区大小
  show variables like 'sort_buffer_size';
  1
  2

  

4.SQL优化-group by优化

• group by
  例子

  #未创建索引，根据profession字段进行分组操作。
  explain select profession,count(*) from student group by profession;
  1
  2

  

  #创建索引
  create index index_student_profession_age on student(profession,age);
  1
  2

  

  #创建索引后，根据profession字段进行分组操作。
  1,SIMPLE,student,index,,index_student_profession_age,28,,4,Using index
  1
  2

  

  在分组操作时，可以通过索引来提高效率。

  在分组操作时，索引的使用也是满足最左前缀法则的。

5.SQL优化-limit优化

• limit优化

  一个常见又非常头疼的问题就是limit 1000000,10，此时需要MySQL排序前1000010条记录，仅仅返回1000000-1000010的记录，其他记录丢弃，查询排序的代价非常大。

  优化思路：一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

  例子

  #优化前
  explain select * from student limit 1000000,10;
  #优化后
  explain select s1.* from student s1,(select id from student order by id limit 1000000,10) s2 where s1.id = s2.id;
  1
  2
  3
  4

6.SQL优化-count优化

• count优化

  explain select count(*) from student;
  1

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

  InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

  优化思路：自己计数。

• count的几种用法

  count()是一个聚合函数,对于返回的结果集，一行行地判断,如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。

  用法：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(主键)<count(1)=count(*)，所以尽量使用count(*)。

7.SQL优化-update优化(避免行锁升级为表锁)

• update优化

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

  例子1

  #打开两个会话窗口，两个会话都开启事务。
  begin;
  1
  2

  

  #第一个会话执行更新语句（执行成功）
  update student set age = 14 where id = 1;
  1
  2

  

  #第二个会话执行更新语句（执行成功）
  update student set age = 21 where id = 4;
  1
  2

  

  #两个会话窗口一词提交事务。
  commit;
  1
  2

  

  会话一执行更新语句，是行锁，锁的是id为1的这一行数据，会话2更新的是id为4的数据所以不影响。

  例子2

  #打开两个会话窗口，两个会话都开启事务。
  begin;
  1
  2

  

  #第一个会话执行更新语句（执行成功）
  update student set age = 15 where name = '张三';
  1
  2

  

  #第二个会话执行更新语句（执行失败）
  update student set age = 20 where name = '赵六';
  1
  2

  

  失败原因： 因为 InnoDB的行锁是针对索引加的锁，这里根据name字段作为条件去执行更新操作，name字段没有建立索引所以行锁升级为了表锁，导致会话2的更新语句执行失败。只有当会话一事务提交以后，会话2才能执行成功。行锁升级为表锁，并发性能就会降低。

  

8.SQL优化-小结

• 1.插入数据

  insert：批量插入、手动控制事务、主键顺序插入
  大批量插入：load data local infile

• 2.主键优化

  主键长度尽量短、顺序插入 auto_increment

• 3.order by优化

  using index：直接通过索引返回数据，性能高
  useing filesort：需要将返回的结果在排序缓冲区排序

• 4.groud by优化

  索引，多字段分组满足最左前缀法则

• 5.limit 优化

  覆盖所有 + 子查询

• 6.count 优化

  性能：count(字段) < count(主键) < count(1) < count(*)

• 7.update 优化

  尽量恩据主键/索引字段进行数据更新

四、视图

1.视图-介绍及基本语法

• 介绍

  视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

  通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

• 创建

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

  例子

  #创建一个名为student_view的视图
  create or replace view student_view as select id,name from student;
  1
  2

• 查询
  查看创建视图语句

  SHOW CREATE VIEW 视图名称;
  1

  查看视图数据

  SELECT * FROM 视图名称......;
  1

• 修改
  方法一：

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

  方法二：

  ALTER VIEW 视图名称[列名列表] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION];
  1

  例子

  alter view student_view as select id,name,age from student;
  1

• 删除

  DROP VIEW [IF EXISTS] 视图名称;
  1

  例子

  drop view if exists student_view;
  1

2.视图-检查选项(cascaded)

• 视图的检查选项

  当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除、以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会被检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL，默认值为CASCADED。

• CASCADED：

  例子1 （未添加WITH CASCADED CHECK OPTION检查选项）

  #创建一个视图student_view_1（没有加检查选项）
  create or replace view student_view_1 as select id,name from student where id <= 20;
  1
  2

  #向视图中新增满足视图条件的数据（新增成功）
  insert into student_view_1 values (10,'小红');
  1
  2

  视图是不存储数据的，向视图中插入数据等于是向基表中插入数据

  #查询视图（新增数据可以查看到）
  select * from student_view_1;
  1
  2

  

  #向视图中新增不满足视图条件的数据（新增成功）
  insert into student_view_1 values (30,'小黄');
  1
  2

  #查询视图（新增数据查看不到，因为创建视图的条件为id小于20，新增的数据id大于二十）
  select * from student_view_1;
  1
  2

  

  #查看基表（发现基表中有新增的数据）
  select * from student;
  1
  2

  

  此时发现我们向视图中新增不满足视图条件的数据也会执行成功，为了避免这种情况，创建视图的时候就需要添加检查选项

  例子2（添加了WITH CASCADED CHECK OPTION检查选项）

  #创建一个视图student_view_1（添加了检查选项）
  create or replace view student_view_1 as select id,name from student where id <= 20 with cascaded check option;
  1
  2

  #向视图中新增不满足视图条件的数据（新增报错）
  insert into student_view_1 values (30,'小黄');
  1
  2

  

  当使用WITH CASCADED CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除、以使其符合视图的定义

  例子3（基于另一个视图建立视图添加WITH CASCADED CHECK OPTION检查选项）

  #创建一个视图student_view_1（没有加检查选项）
  create or replace view student_view_1 as select id,name from student where id <= 20;
  1
  2

  #基于视图student_view_1 创建一个视图student_view_2 (加了检查选项)
  create or replace view student_view_2 as select id,name from student_view_1 where id >= 10 with cascaded check option;
  1
  2

  #向视图中新增满足视图student_view_2条件的，不满足视图student_view_1条件数据（新增失败）
  insert into student_view_2 values (30,'小黄');
  1
  2

  

  失败原因： cascaded 会检查依赖视图中的规则以保持一致性。（虽然满足了视图student_view_2 的条件，但是没有满足视图student_view_1的条件，试图student_view_1虽然没有添加检查选项不需要检查，但是自从视图student_view_2 添加了WITH CASCADED CHECK OPTION检查选项后，相当于视图student_view_1也添加了WITH CASCADED CHECK OPTION检查选项，所以执行失败。只有当同时满足两个视图的条件才能新增成功。 ）

  添加WITH CASCADED CHECK OPTION检查选项后，等于会对所依赖的视图也自动加上了WITH CASCADED CHECK OPTION检查选项

  例子4

  #创建一个视图student_view_1（没有加检查选项）
  create or replace view student_view_1 as select id,name from student where id <= 20;
  1
  2

  #基于视图student_view_1 创建一个视图student_view_2 (加了检查选项)
  create or replace view student_view_2 as select id,name from student_view_1 where id >= 10 with cascaded check option;
  1
  2

  #基于视图student_view_2 创建一个视图student_view_3 (没有加检查选项)
  create or replace view student_view_3 as select id,name from student_view_2 where id <= 15;
  1
  2

  #新增成功（原因：满足三个视图的所有条件）
  insert into student_view_3 values (14,'小黄');
  1
  2

  #新增成功（原因：虽然视图student_view_3没有加检查选项，不做判断。满足视图student_view_2和视图student_view_1的条件所以执行成功）
  insert into student_view_3 values (18,'小黄');
  1
  2

  #新增失败（原因：满足了视图student_view_3的条件，但是没有满足视图student_view_2的条件，所以失败，因为视图student_view_2加了检查选项）
  insert into student_view_3 values (8,'小黄');
  1
  2

  #新增失败（原因：虽然视图student_view_3没有加检查选项，不做判断。同时也满足了视图student_view_2的条件，但是没有满足视图student_view_1的条件所以失败。因为视图student_view_2的检查选项也会作用到视图student_view_1上，所以失败）
  insert into student_view_3 values (28,'小黄');
  1
  2

  由于MySQL数据库版本问题，5.7.6版本之前案例4中添加的WITH CASCADED CHECK OPTION检查选项会失效

  由于MySQL数据库版本问题，5.7.6版本之前和5.7.6之后使用效果不相同

3.视图-检查选项(local)

• 视图的检查选项

  当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除、以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会被检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL，默认值为CASCADED。

• LOCAL：

  例子1 （未添加WITH LOCAL CHECK OPTION检查选项）

  #创建一个视图student_view_1（没有加检查选项）
  create or replace view student_view_1 as select id,name from student where id <= 20;
  1
  2

  #向视图中新增满足视图条件的数据（新增成功）
  insert into student_view_1 values (14,'小黄');
  1
  2

  #向视图中新增不满足视图条件的数据（新增成功）
  insert into student_view_1 values (28,'小黄');
  1
  2

  未添加WITH LOCAL CHECK OPTION检查选项，不会对视图条件进行判断

  例子2（添加了WITH LOCAL CHECK OPTION检查选项）

  #创建一个视图student_view_1（添加了检查选项）
  create or replace view student_view_1 as select id,name from student where id <= 20 with local check option;
  1
  2

  #向视图中新增满足视图条件的数据（新增成功）
  insert into student_view_1 values (14,'小黄');
  1
  2

  #向视图中新增不满足视图条件的数据（新增失败）
  insert into student_view_1 values (28,'小黄');
  1
  2

  添加WITH LOCAL CHECK OPTION检查选项，增、删、改会对视图条件进行判断，满足视图条件才能执行成功

  例子3（基于另一个视图建立视图添加WITH LOCAL CHECK OPTION检查选项）

  #创建一个视图student_view_1（没有加检查选项）
  create or replace view student_view_1 as select id,name from student where id <= 20;
  1
  2

  #基于视图student_view_1 创建一个视图student_view_2 (加了检查选项)
  create or replace view student_view_2 as select id,name from student_view_1 where id >= 10 with local check option;
  1
  2

  #向视图中新增不满足视图student_view_2条件的数据（新增失败）
  insert into student_view_2 values (8,'小黄');
  1
  2

  #向视图中新增满足视图student_view_2，也满足视图student_view_1条件的数据（新增成功）
  insert into student_view_2 values (15,'小黄');
  1
  2

  #向视图中新增满足视图student_view_2，不满足视图student_view_1条件的数据（新增成功）
  insert into student_view_2 values (30,'小黄');
  1
  2

  添加WITH LOCAL CHECK OPTION检查选项，增、删、改会对视图条件进行判断，也会对依赖的视图进行条件判断，如果依赖的视图加了检查选项则会进行条件判断，没有加检查选项则不会进行判断

  例子4

  #创建一个视图student_view_1（没有加检查选项）
  create or replace view student_view_1 as select id,name from student where id <= 20;
  1
  2

  #基于视图student_view_1 创建一个视图student_view_2 (加了检查选项)
  create or replace view student_view_2 as select id,name from student_view_1 where id >= 10 with local check option;
  1
  2

  #基于视图student_view_2 创建一个视图student_view_3 (没有加检查选项)
  create or replace view student_view_3 as select id,name from student_view_2 where id <= 15;
  1
  2

  #（新增失败）向视图中新增满足视图student_view_3、student_view_1，不满足视图student_view_2条件的数据
  insert into student_view_2 values (8,'小黄');
  1
  2

  #（新增成功）向视图中新增满足视图student_view_2、student_view_3不满足视图student_view_1条件的数据
  insert into student_view_3 values (14,'小黄');
  1
  2

  #（新增成功）向视图中新增满足视图student_view_2、不满足视图student_view_1、student_view_3条件的数据
  insert into student_view_3 values (30,'小黄');
  1
  2

  发现只要满足了视图student_view_2的条件就能新增成功

  添加WITH LOCAL CHECK OPTION检查选项，增、删、改会对视图条件进行判断，也会对依赖的视图进行条件判断，如果依赖的视图加了检查选项则会进行条件判断，没有加检查选项则不会进行判断

  由于MySQL数据库版本问题，5.7.6版本之前和5.7.6之后使用效果不相同

4.视图-更新及作用

• 视图的更新

  要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

  1.聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）

  2.DISTINCT

  3.GROUP BY

  4.HAVING

  5.UNION 或者 UNION ALL

• 视图的作用

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

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

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

五、存储过程

1.存储过程-介绍

• 介绍

  存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

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

• 特点

  1.封装，复用

  2.可以接收参数，也可以返回数据

  3.减少网络交互，效率提升

2.存储过程-基本语法

• 创建

  CREATE PROCEDURE 存储过程名称([参数列表])
  BEGIN
      --SQL语句
  END;
  1
  2
  3
  4

  例子

  #创建一个名为p1的存储过程来查询student表中的记录数
  create procedure p1()
  begin
      select count(*) from student;
  end;
  1
  2
  3
  4
  5

• 调用

  CALL 存储过程名称([参数]);
  1

  例子

  #调用名为p1的存储过程
  call p1();
  1
  2

• 查看
  查询指定数据库的存储过程及状态信息

  SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名';
  1

  例子

  #查询名为test数据库的存储过程及状态信息
  select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test';
  1
  2

  查询某个存储过程的定义

  SHOW CREATE PROCEDURE 存储过程名称;
  1

  例子

  #查询名为p1的存储过程
  show create procedure p1;
  1
  2

• 删除

  DROP PROCEDURE [IF EXISTS] 存储过程名称;
  1

  例子

  #删除名为p1的存储过程
  drop procedure if exists p1;
  1
  2

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

  报错

  

  解决方法

  #设置结束符为#号
  delimiter #
  1
  2

  
  

3.存储过程-变量

3.1.存储过程-变量-系统变量

• 系统变量

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

• 查看系统变量
  查看所有系统变量

  SHOW VARIABLES;		#（没有指定，默认是SESSION）
  1

  SHOW [SESSION|GLOBAL] VARIABLES;
  1

  例子

  #查看系统会话变量
  show session variables;
  1
  2

  

  #查看系统全局变量
  show global variables;
  1
  2

  

  可以通过LIKE模糊匹配方式查找变量

  SHOW GLOBAL VARIABLES LIKE '......';
  1

  例子

  #查看以auto开头的系统会话变量
  show session variables like 'auto%';
  1
  2

  #查看以auto开头的系统全局变量
  show global variables like 'auto%';
  1
  2

  查看指定变量的值

  SELECT @@[SESSION|GLOBAL] 系统变量名;
  1

  例子

  select @@autocommit;	（默认为session）
  1

  select @@session.autocommit;
  1

  select @@global.autocommit;
  1

• 设置系统变量
  方法一

  SET [SESSION|GLOBAL] 系统变量名 = 值;
  1

  例子

  #设置系统会话变量
  set session autocommit = 0;
  1
  2

  #设置系统全局变量
  set global autocommit = 0;
  1
  2

  方法二

  SET @@[SESSION|GLOBAL] 系统变量名 = 值;
  1

  例子

  #设置系统会话变量
  set @@session.autocommit = 0;
  1
  2

  #设置系统全局变量
  set @@global.autocommit = 0;
  1
  2

  注意

  如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。

  MySQL服务重启之后，所设置的全局参数会失效，要想不失效，可以在 my.cnf 配置文件中配置

3.2.存储过程-变量-用户定义变量

• 用户定义变量

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

  赋值
  第一种方式

  SET @变量名 = 值 [,@变量名 = 值]...;
  1

  例子

  #定义一个变量并赋值。
  set @name = '张三';
  1
  2

  #定义多个个变量并赋值。
  set @name = '张三',@age := 18;
  1
  2

  第二种方式

  SET @变量名 := 值 [,@变量名 := 值]...;
  1

  例子

  #定义一个变量并赋值。
  set @name := '张三';
  1
  2

  #定义多个个变量并赋值。
  set @name := '张三',@age := 20;
  1
  2

  第三种方式

  SELECT @变量名 := 值 [,@变量名 := 值]...;
  1

  例子

  #定义一个变量赋值并查询。
  select @name := '张三';
  1
  2

  #定义多个变量赋值并查询。
  select @name := '张三',@age := 20;
  1
  2

  第四种方式

  SELECT 字段名 INTO @变量名 FROM 表名;
  1

  例子

  #将查询到的记录数给count变量
  select count(*) into @mycount from student;
  1
  2

  使用

  SELECT @变量名 [,@变量名]...;
  1

  例子

  #使用单个变量
  select @name;
  1
  2

  #使用多个变量
  select @name,@age;
  1
  2

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

3.2.存储过程-变量-局部变量

• 局部变量

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

  声明

  DECLARE 变量名 变量类型 [DEFAULT ...];
  1

  变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

  例子

  #创建一个名为p1的存储过程，声明一个局部变量，赋值并使用
  create procedure p1()
  begin
      declare student_count int default 0;
      select count(*) into student_count from student;
      select student_count;
  end;
  #调用存储过程查看结果
  call p1();
  1
  2
  3
  4
  5
  6
  7
  8
  9

  赋值

  SET 变量名 = 值;
  1

  SET 变量名 := 值;
  1

  SELECT 字段名 INTO 变量名 FROM 表名 ...;
  1

4.存储过程-if判断

• if判断
  语法

  IF 条件1 THEN
  	......
  ELEIF 条件2 THEN      --可选
      ......
  ELSE                 --可选
      ......
  END IF
  1
  2
  3
  4
  5
  6
  7

  例子

  根据定义的分数score变量，判断当前分数对应的分数等级。
  1.score >= 85分，等级为优秀。
  2.score >= 60分且score < 85分，等级为及格。
  3.score < 60分，等级为不及格。

  #创建存储过程
  create procedure p1()
  begin
      declare score int default 80;
      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 p1();
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16

5.存储过程-参数(IN、OUT、INOUT)

• 参数

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

  用法

  CREATE PROCEDURE 存储过程名称 ([IN/OUT/INOUT 参数名 参数类型])
  BEGIN
      --SQL语句
  END;
  1
  2
  3
  4

  例子1

  1.根据传入参数score，判定当前分数对应的分数等级，并返回。
  ①.score >= 85分，等级为优秀。
  ②.score >= 60分且score < 85分，等级为及格。
  ③.score < 60分，等级为不及格。

  #创建存储过程
  create procedure p1(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 p1(90,@result);
  #使用变量
  select @result;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

  例子2

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

  #创建存储过程
  create procedure p1(inout score double)
  begin
      set score := score * 0.5;
  end;
  #变量赋值
  set @score := 90;
  #调用存储过程
  call p1(@score);
  #使用变量
  select @score;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

6.存储过程-case

• case
  语法一

  CASE case_value
      WHEN when_value1 THEN statement_list1
      [WHEN when_value2 THEN statement_list2]...
      [ELSE statement_list]
  END CASE;
  1
  2
  3
  4
  5

  语法二

  CASE
      WHEN search_condition1 THEN statement_list1
      [WHEN search_condition1 THEN statement_list2]...
      [ELSE statement_list]
  END CASE;
  1
  2
  3
  4
  5

  例子

  根据传入的月份，判定月份所属的季节
  ①.1-3月份，为第一季度
  ②.4-6月份，为第二季度
  ③.7-9月份，为第三季度
  ④.10-12月份，为第四季度

  create procedure p1(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 p1(11);
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19

7.存储过程-循环

7.1.存储过程-循环-while

• 循环-while

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

  #先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
  WHILE 条件 DO
      SQL逻辑...
  END WHILE;
  1
  2
  3
  4

  例子

  计算从1累加到number的值，number为传入的参数值。

  create procedure p1(in number int)
  begin
      declare sum int default 0;
      while number > 0 do
          set sum := sum + number;
          set number := number - 1;
      end while;
      select sum;
  end;
  #调用存储过程
  call p1(100);
  
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

7.2.存储过程-循环-repeat

• 循环-repeat

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

  语法

  #先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
  REPEAT
      SQL逻辑...
      UNTIL 条件
  END REPEAT;
  1
  2
  3
  4
  5

  例子

  计算从1累加到number的值，number为传入的参数值。

  create procedure p1(in number int)
  begin
      declare sum int default 0;
      while number > 0 do
          set sum := sum + number;
          set number := number - 1;
      end while;
      select sum;
  end;
  #调用存储过程
  call p1(100);
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

7.3.存储过程-循环-loop

• 循环-loop

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

  语法

  [begin_label:] LOOP
      SQL逻辑...
  END LOOP [end_label];
  1
  2
  3

  LEAVE label;    #退出指定标记的循环体
  ITERATE label;  #直接进入下一次循环
  1
  2

  例子

  计算从1累加到number的值，number为传入的参数值。

  create procedure p1(in number int)
  begin
      declare result int default 0;
      sum:loop
          if number <= 0 then
              leave sum;
          end if;
          set result := result + number;
          set number := number - 1;
      end loop sum;
      select result;
  end;
  #调用存储过程
  call p1(100);
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

  例子

  计算从1到number之间的偶数累加的值，number为传入的参数值。

  create procedure p1(in number int)
  begin
      declare result int default 0;
      sum:loop
          if number <= 0 then
              leave sum;
          end if;
          if number % 2 = 1 then
              set number := number - 1;
              iterate sum;
          end if;
          set result := result + number;
          set number := number - 1;
      end loop sum;
      select result;
  end;
  #调用存储过程
  call p1(100);
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18

8.存储过程-游标-cursor

• 游标

  游标(CURSOR) 是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标
  的声明、OPEN、FETCH 和 CLOSE。

  语法
  声明游标

  DECLARE 游标名称 CURSOR FOR 查询语句;
  1

  打开游标

  OPEN 游标名称;
  1

  获取游标记录

  FETCH 游标名称 INTO 变量[,变量];
  1

  关闭游标

  CLOSE 游标名称;
  1

  例子

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

  create procedure p1(in uage int)
  begin
      declare user_name varchar(10);
      declare user_profession varchar(10);
      declare user_cursor cursor for select name,profession from tb_user where age <= uage;
      drop table if exists tb_user_profession;
      create table if not exists tb_user_profession(
          id int primary key auto_increment comment 'id',
          name varchar(10) comment '姓名',
          profession varchar(10) comment '专业'
      ) comment '用户专业信息表';
      #打开游标
      open user_cursor;
      while true do
      	#获取游标记录
          fetch user_cursor into user_name,user_profession;
          insert into tb_user_profession values (null,user_name,user_profession);
      end while;
      #关闭游标
      close user_cursor;
  end;
  #调用存储过程（使用DataGrip软件调用存储过程以后需要刷新下表或者数据库，不然新的表不显示）
  call p1(40);
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23

  调用存储过程会报错，但是没有影响效果 下一节通过添加条件处理程序handler解决此问题

9.存储过程-条件处理程序-handler

• handler

  条件处理程序(Handler) 可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

  语法

  DECLARE handler_action HANDLER FOR condition_value [,condition_value]... statement;
  
  handler_action
      CONTINUE:继续执行当前程序
      EXIT:终止执行房前程序
  
  condition_value
      SQLSTATE sqltate_value:状态码，如 02000
      SQLWARNING:所有以01开头的SQLSTATE代码的简写
      NOT FOUND:所有以02开头的SQLSTATE代码的简写
      SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

  例子

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

  create procedure p1(in uage int)
  begin
      declare user_name varchar(10);
      declare user_profession varchar(10);
      declare user_cursor cursor for select name,profession from tb_user where age <= uage;
      #声明一个条件处理程序
      declare exit handler for sqlstate '02000' close user_cursor;
      drop table if exists tb_user_profession;
      create table if not exists tb_user_profession(
          id int primary key auto_increment comment 'id',
          name varchar(10) comment '姓名',
          profession varchar(10) comment '专业'
      ) comment '用户专业信息表';
      open user_cursor;
      while true do
          fetch user_cursor into user_name,user_profession;
          insert into tb_user_profession values (null,user_name,user_profession);
      end while;
      close user_cursor;
  end;
  #调用存储过程
  call p1(40);
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22

  想要了解更多可以参考MySQL的官方文档：https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.htm 点击前往

10.存储函数

• 存储函数

  存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

  语法

  CREATE FUNCTION 存储函数名称([参数列表])
  RETURNS type [characteristic...]
  BEGIN
      SQL语句
      RETURN...;
  END;
  
  characteristic说明：
  	DETERMINISTIC：相同的输入参数总是产生相同得结果。
  	NO SQL：不包含SQL语句。
  	READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

  例子

  计算从1到number之间的偶数累加的值，number为传入的参数值。

  create function function_sum(number int)
  returns int deterministic
  begin
      declare sum int default 0;
      while number > 0 do
          set sum := number + sum;
          set number := number - 1;
      end while;
      return sum;
  end;
  #调用函数
  select function_sum(100);
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

六、触发器

1.触发器-介绍

• 介绍

  触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

  使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

  触发器类型	NEW 和 OLD
  INSERT型触发器	NEW 表示将要或者已经新增的数据
  UPDATE型触发器	OLD表示修改之前的数据，NEW 表示将要或已经修改后的数据
  DELETE型触发器	OLD 表示将要或者已经删除的数据

  语法
  创建

  CREATE TRIGGER trigger_name
  	BEFORE/AFTER INSERT/UPDATE/DELETE
  ON table_name FOR EACH ROW --行级触发器
  BEGIN
      trigger_statement;
  END;
  1
  2
  3
  4
  5
  6

  BEFORE：在……之前 AFTER ：在……之后

  查看

  SHOW TRIGGERS;
  1

  删除

  #如果没有指定database_name，默认是当前数据库。
  DROP TRIGGER [database_name.]trigger_name;
  1
  2

2.触发器-案例一(insert类型)

• 案例一-insert

  通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中,包含增加，修改，删除。

  #创建用户日志表
  create table user_logs(
      id               int(11)        not null auto_increment primary key comment 'ID',
      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 '操作参数'
  )engine = innodb default charset = utf8;
  1
  2
  3
  4
  5
  6
  7
  8

  #创建用户基本信息表
  create table tb_user(
      id         int auto_increment primary key comment '主键ID',
      name       varchar(10) null comment '姓名',
      age        int         null comment '年龄',
      gender     char        null comment '性别 1:男，2：女',
      phone      char(11)    null comment '手机号',
      profession varchar(10) null comment '专业'
  )comment '用户基本信息表';
  1
  2
  3
  4
  5
  6
  7
  8
  9

  #创建插入数据触发器
  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,',age=',NEW.age,',gender=',NEW.gender,',phone=',NEW.phone,',profession=',NEW.profession));
  end;
  
  1
  2
  3
  4
  5
  6
  7
  8

  #查看触发器
  show triggers;
  1
  2

  

  #向tb_user表新增数据
  insert into tb_user (id, name, age, gender, phone, profession) values (5,'麻子','20','男','13166245122','厨师');
  1
  2

  #查询用户日志表（触发器触发成功,数据成功新增到了用户日志表中）
  select * from user_logs;
  1
  2

  

3.触发器-案例二(update类型)

• 案例二-update

  通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中,包含增加，修改，删除。

  tb_user 用户基本信息表，user_logs用户日志表基于案例一即可

  #创建修改数据触发器
  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,',age=',OLD.age,',gender=',OLD.gender,',phone=',OLD.phone,',profession=',OLD.profession,
           ' | 更新之后的数据：id=',NEW.id,',name=',NEW.name,',age=',NEW.age,',gender=',NEW.gender,',phone=',NEW.phone,',profession=',NEW.profession));
  end;
  1
  2
  3
  4
  5
  6
  7
  8
  9

  #查看触发器
  show triggers;
  1
  2

  

  #修改tb_user表中的数据
  update tb_user set name = '小花花',profession = '会计' where id = 5;
  1
  2

  #查询用户日志表（触发器触发成功,数据成功新增到了用户日志表中）
  select * from user_logs;
  1
  2

  

4.触发器-案例三(delete类型)

• 案例三-delete

  通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中,包含增加，修改，删除。

  tb_user 用户基本信息表，user_logs用户日志表基于案例一即可

  #创建删除数据触发器
  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,',age=',OLD.age,',gender=',OLD.gender,',phone=',OLD.phone,',profession=',OLD.profession));
  end;
  1
  2
  3
  4
  5
  6
  7
  8

  #查看触发器
  show triggers;
  1
  2

  

  #删除tb_user表中额数据
  delete from tb_user where id = 5;
  1
  2

  #查询用户日志表（触发器触发成功,数据成功新增到了用户日志表中）
  select * from user_logs;
  1
  2

  

5.视图&存储过程&触发器-小结

• 1.视图(VIEW)

  1.虚拟存在的表，不保存查询结果，只保存查询的SQL逻辑
  2.简单、安全、数据独立

• 2.存储过程(PROCEDURE)

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

• 3.存储函数(FUNCTION)

  1.存储函数是有返回值的存储过程，参数类型只能为IN类型。
  2.存储函数可以被存储过程替代。

• 4.触发器(TRIGGER)

  1.可以在表数据进行INSERT、UPDATE、DELETE之前或之后触发。
  2.保证数据完整性、日志记录、参数校验

七、锁

1.锁-介绍

• 介绍

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

• 分类

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

2.锁-全局锁

2.1.锁-全局锁-介绍

• 介绍

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

  语法

  #加全局锁
  flush tables with read lock;
  1
  2

  #本地数据库
  mysqldump -u数据库用户 -p数据库密码 数据库名 > 磁盘路径/备份文件名.sql
  #非本地数据库（远程数据库）
  mysqldump -h 数据库地址(IP地址) -u数据库用户 -p数据库密码 数据库名 > 磁盘路径/备份文件名.sql
  1
  2
  3
  4

  #解锁
  unlock tables;
  1
  2

• 特点

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

  在innoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份

  #不加锁的一致性数据备份
  mysqldump --single-transaction -u数据库用户 -p数据库密码 数据库名 > 磁盘路径/备份文件名.sql
  1
  2

2.2.锁-全局锁-一致性数据备份

• 一致性数据备份

  例子1(一致性数据备份——加锁)

  第一步(登录数据库)

  

  第二步(加全局锁)

  #加全局锁
  flush tables with read lock;
  1
  2

  
  第三步(退出数据库)

  #退出数据库
  exit;
  1
  2

  
  第四步(使用MySQL提供的工具mysqldump进行备份)

  mysqldump介绍：mysqldump是mysql用于转存储数据库的实用程序。是MySQL提供的一个工具，不是SQL语句，所以不要在MySQL的命令行中执行，直接在Windows命令行中执行(在MySQL的命令行中执行无效)

  #使用MySQL提供的工具mysqldump进行备份
  mysqldump -uroot -proot db_test > D:\backups\db_test.sql
  1
  2

  

  第五步(查看备份文件)

  
  第六步(登录数据库并解锁)

  #解锁
  unlock tables;
  1
  2

  

  例子1(一致性数据备份——不加锁)

  在innoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份

  #使用MySQL提供的工具mysqldump进行备份
  mysqldump --single-transaction -uroot -proot db_test > D:\backups\db_test.sql
  1
  2

  

3.锁-表级锁

• 表级锁-介绍

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

  对于表级锁，主要分为以下三类：
  1.表锁
  2.元数据锁（meta data lock，MDL）
  3.意向锁

3.1.锁-表级锁-表锁

• 表锁

  对于表锁，分为两类：
  1.表共享读锁（read lock）
  2.表独占写锁（write lock）

  语法
  加锁

  lock tables 表名... read/write;
  1

  释放锁

  unlock tables;	#客户端断开连接。
  1

  例子1(读锁)

  #打开两个客户端，对客户端1的student表进行加读锁
  lock tables student read;
  1
  2

  

  客户端1加读锁后客户端1和客户端2都可以查询。

  #查询语句
  select * from student;
  1
  2

  

  客户端1加读锁后，在客户端1中执行修改语句报错，修改数据失败。

  #修改语句
  update student set name = '赵六' where id = 3;
  1
  2

  

  客户端1加读锁后，在客户端2中执行修改语句没有报错，处于阻塞状态。

  #修改语句
  update student set name = '赵六' where id = 3;
  1
  2

  

  当客户端1释放读锁后，客户端2中执行修改语句就解除阻塞状态执行成功。

  #释放锁
  unlock tables;
  1
  2

  

  读锁特点： 读锁不会阻塞其他客户端的读，但是会阻塞写。

  例子2(写锁)

  #打开两个客户端，对客户端1的student表进行加些锁
  lock tables student write;
  1
  2

  

  客户端1加写锁后，在客户端1中执行查询语句，查询成功。

  #查询语句
  select * from student;
  1
  2

  

  客户端1加写锁后，在客户端1中执行修改语句，修改成功。

  #修改语句
  update student set name = '王五' where id = 3;
  1
  2

  

  客户端1加写锁后，在客户端2中执行查询语句，处于阻塞状态。

  #查询语句
  select * from student;
  1
  2

  

  客户端1加写锁后，在客户端2中执行修改语句，处于阻塞状态。

  #修改语句
  update student set name = '赵六' where id = 3;
  1
  2

  

  当客户端1释放读锁后，客户端2中执行修改语句就解除阻塞状态执行成功。

  #释放锁
  unlock tables;
  1
  2

  

  写锁特点： 写锁即会阻塞其他客户端的读，也会阻塞其他客户端的写。

  总结： 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁即会阻塞其他客户端的读，也会阻塞其他客户端的写。

3.2.锁-表级锁-元数据锁

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

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

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

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

  查看元数据锁

  #数据库版本的不同有的会查询不了
  select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION from performance_schema.metadata_locks;
  1
  2

  例子1

  #在客户端1开启一个事务
  begin;
  #客户端1执行查询语句（查询成功）
  select * from student;
  1
  2
  3
  4

  

  #在客户端2开启一个事务
  begin;
  #客户端2执行查询语句（查询成功）
  select * from student;
  #客户端2执行修改语句（执行成功）
  update student set name = '赵六' where id = 3;
  1
  2
  3
  4
  5
  6

  

  #客户端1和客户端2都提交事务
  commit;
  1
  2

  

  总结 SHARED_READ和SHARED_WRITE是兼容的，两个客户端怎么执行都可以。

  例子2

  #在客户端1开启一个事务
  begin;
  #客户端1执行查询语句（查询成功）
  select * from student;
  1
  2
  3
  4

  

  #客户端2执行添加字段语句（处于阻塞状态）
  alter table student add column age int comment '年龄';
  1
  2

  原因： 因为客户端1执行了一个查询语句，自动加上了SHARED_READ共享读锁，客户端2执行alter语句时候自动加上一个EXCLUSIVE排他锁，EXCLUSIVE与其他的MDL都互斥所以导致客户端2执行alter语句的时候处于阻塞状态。

  

  #客户端1提交事务
  commit;
  1
  2

  当客户端1提交事务以后，客户端2的alter语句执行成功。

  
  

  例子3

  #查看元素据锁
  select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION from performance_schema.metadata_locks;
  1
  2

  

  #客户端1开启事务
  begin;
  #客户端1执行查询语句
  select * from student;
  #客户端2查看元素据锁
  select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION from performance_schema.metadata_locks;
  1
  2
  3
  4
  5
  6

  当客户端1执行查询语句后，客户端2就会自动加上了SHARED_READ锁

  

  #客户端2开启事务
  begin;
  #客户端2执行更新语句
  update student set name = '王五' where id = 3;
  #客户端2查看元素据锁
  select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION from performance_schema.metadata_locks;
  1
  2
  3
  4
  5
  6

  客户端2执行更新语句后，客户端2又自动加上了SHARED_WRITE锁

  

  #客户端2执行alter语句（处于阻塞状态）
  alter table student drop column age;
  1
  2

  客户端2执行alter语句,会自动加EXCLUSIVE锁，EXCLUSIVE与其他的MDL都互斥所以导致客户端2执行alter语句的时候处于阻塞状态。

  

  #客户端1提交事务
  commit;
  1
  2

  当客户端1提交事务后，客户端2的alter语句就执行成功。

  

  #客户端2提交事务
  commit;
  #客户端2执行查询语句（age字段已经删除）
  select * from student;
  1
  2
  3
  4

  

3.3.锁-表级锁-意向锁

• 意向锁

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

  1.意向共享锁(IS):由语句 select … lock in share mode添加。
  2.意向排他锁 (IX): 由insert、update、delete、select … for update添加。

  1.意向共享锁 (IS):与表锁共享锁 (read)兼容，与表锁排它锁 (write)互斥
  2.意向排他锁 (IX):与表锁共享锁 (read) 及排它锁 (write)都互斥。意向锁之间不会互斥

  查看意向锁及行锁的加锁情况

  #查看意向锁及行锁的加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2

3.4.锁-表级锁-意向锁-测试

• 意向锁-测试

  例子1

  #客户端1开启事务
  begin;
  #客户端1执行查询语句，给查询的结果集加上共享锁
  select * from student where id = 1 lock in share mode;
  #客户端2查看意向锁及行锁的加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4
  5
  6

  lock in share mode 给查询的结果集加上共享锁。
  TABLE LOCK_TYPE字段里面的值为TABLE，表示表锁。
  RECORD LOCK_TYPE字段里面的值为RECORD，表示行锁。

  

  #在客户端2中添加表共享读锁（添加成功）
  lock tables student read;
  1
  2

  意向共享锁 (IS):与表锁共享锁 (read)兼容

  

  #在客户端2中添加表独占写锁
  lock tables student write;
  1
  2

  意向共享锁 (IS):与表锁排它锁 (write)互斥

  

  #客户端1提交事务
  commit;
  1
  2

  当客户端1提交事务，客户端2涉及的行锁和意向锁都会得到释放。此时客户端2表锁的写锁也就添加成功了。

  

  #客户端2释放锁
  unlock tables;
  1
  2

  

  例子2

  #客户端1开启事务
  begin;
  #客户端1执行更新语句
  update student set name = '赵六' where id = 3;
  #客户端2查看意向锁及行锁的加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4
  5
  6

  

  #在客户端2中添加表共享读锁（阻塞状态）
  lock tables student read;
  1
  2

  意向排他锁 (IX):与表锁共享锁 (read) 及排它锁 (write)都互斥。意向锁之间不会互斥

  

  #客户端1提交事务
  commit;
  1
  2

  当客户端1提交事务，客户端2涉及的行锁和意向锁都会得到释放。此时客户端2表共享读锁就添加成功了。

  

  #客户端2释放锁
  unlock tables;
  1
  2

  

4.锁-行级锁

4.1.锁-行级锁-介绍

• 介绍

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

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

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

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

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

4.2.锁-行级锁-行锁

• 行锁

  lnnoDB实现了以下两种类型的行锁:

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

  

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

• 行锁-演示

  默认情况下，lnnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

  2.lnnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么lnnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

  查看意向锁和行锁的加锁情况

  #查看意向锁和行锁的加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2

  例子1

  #客户端1开启事务
  begin;
  #客户端1执行查询语句，给查询的结果集加上共享锁
  select * from student where id = 1 lock in share mode;
  #客户端2开启事务
  begin;
  #客户端2查看意向锁和行锁的加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4
  5
  6
  7
  8

  

  #客户端2执行查询语句，给查询的结果集加上共享锁（执行成功）
  select * from student where id = 1 lock in share mode;
  #客户端2查看意向锁和行锁的加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4

  共享锁(S)与共享锁(S)是兼容的

  

  #客户端2提交事务
  commit;
  #客户端2查看意向锁和行锁的加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4

  客户端2提交事务以后客户端之前加的共享锁就会得到释放

  

  #客户端2开启事务
  begin;
  #客户端2执行更新语句（执行成功）
  update student set name = '小李子' where id = 2;
  #客户端2执行更新语句（执行成功）
  update student set name = '王五' where id = 3;
  #客户端2执行更新语句（阻塞状态）
  update student set name = '小张' where id = 1;
  1
  2
  3
  4
  5
  6
  7
  8

  客户端1对id=1的这行数据添加了共享锁，当客户端2执行更新语句的时候会对没有加锁的行能执行成功，对加了锁的行就会处于阻塞状态（共享锁与排他锁互斥）

  

  #结束客户端2对id=1的更新语句
  #客户端2提交事务
  commit;
  #客户端1提交事务
  commit;
  #客户端2查看意向锁和行锁的加锁情况（此时没有任何锁）
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4
  5
  6
  7

  

  #客户端1开启事务
  begin;
  #客户端1执行更新语句（执行成功）
  update student set name = '赵六' where id = 3;
  #客户端2开启事务
  begin;
  #客户端2执行更新语句（阻塞状态）
  update student set name = '赵六' where id = 3;
  1
  2
  3
  4
  5
  6
  7
  8

  排他锁(X)与排他锁(X)是互斥的

  

  #客户端1提交事务
  commit;
  #客户端2执行更新语句（执行成功）
  update student set name = '赵六' where id = 3;
  #客户端2提交事务
  commit;
  1
  2
  3
  4
  5
  6

  只有当客户端1提交事务以后，客户端2才能执行成功，解除阻塞状态

  
  例子2

  #客户端1开启事务
  begin;
  #客户端1执行更新语句，根据name字段为条件（执行成功）
  update student set name = '李四' where name = '小李子';
  #客户端2开启事务
  begin;
  #客户端2执行更新语句（阻塞状态）
  update student set name = '王五' where id = 3;
  1
  2
  3
  4
  5
  6
  7
  8

  lnnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么lnnoDB将对表中的所有记录加锁，此时就会升级为表锁 所以导致客户端2的更新语句处于阻塞状态。

  

  #客户端1提交事务（当客户端1提交事务，客户端2的更新语句就会执行成功）
  commit
  #客户端2提交事务
  commit
  1
  2
  3
  4

  

  #客户端1创建索引
  create index index_student_name on student(name);
  1
  2

  

  #客户端1开启事务
  begin;
  #客户端1执行更新语句，根据name字段为条件（执行成功）
  update student set name = '小李子' where name = '李四';
  #客户端2开启事务
  begin;
  #客户端2执行更新语句（执行成功）
  update student set name = '赵六' where id = 3;
  1
  2
  3
  4
  5
  6
  7
  8

  lnnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么lnnoDB将对表中的所有记录加锁，此时就会升级为表锁 这里给name字段创建了索引，所以客户端2的更新语句会执行成功没有处于阻塞状态。

  

  #客户端1提交事务
  commit;
  #客户端2提交事务
  commit;
  1
  2
  3
  4

  

4.3.锁-行级锁-间隙锁&临键锁1

• -间隙锁&临键锁

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

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

  例子

  #客户端1查看表数据
  select * from student;
  1
  2

  

  #客户端1开启事务
  begin;
  #客户端1执行更新语句，实际表中没有id = 5的数据（执行成功）
  update student set name = '小可爱' where id = 5;
  #客户端2查看加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4
  5
  6

  LOCK_MODE字段中的值GAP，代表的就是间隙锁 客户端1修改了一个id = 5的数据，实际表里没有id = 5的数据，此时就会加一个间隙锁，锁住的就是id = 3到id = 8的数据，不包含id = 3、id = 8的数据。

  索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁。

  

  #客户端2开启事务
  begin;
  #客户端2执行新增语句（阻塞状态）
  insert into student values (7,'张无忌','007');
  1
  2
  3
  4

  原因：间隙锁的存在

  

  #客户端1提交事务（客户端2的新增语句就会执行成功）
  commit;
  #客户端2提交事务
  commit;
  #客户端1查看表数据（新增数据存在）
  select * from student;
  1
  2
  3
  4
  5
  6

4.4.锁-行级锁-间隙锁&临键锁2

• 间隙锁&临键锁

  例子1

  #客户端1查看表数据
  select * from student;
  #客户端1创建索引
  create index index_student_no on student(no);
  #客户端1查看索引
  show index from student;
  1
  2
  3
  4
  5
  6

  

  #客户端1开启事务
  begin;
  #客户端1执行查询语句，给查询的结果集加上共享锁
  select * from student where no = '003' lock in share mode;
  #客户端2查看加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4
  5
  6

  索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

  

  #客户端1提交事务
  commit;
  1
  2

  

  例子2

  #客户端1查看表数据
  select * from student;
  #客户端1执行查询语句，给查询的结果集加上共享锁
  select * from student where id >= 19 lock in share mode;
  #客户端2查看加锁情况
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4
  5
  6

  

  #客户端1提交事务
  commit;
  #客户端2查看加锁情况（没有任何锁）
  select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
  1
  2
  3
  4

  

  临键锁就是行锁和间隙锁的组合

5.锁-小结

• 1.概述

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

• 2.全局锁

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

• 3.表级锁

  ①操作锁住整张表，锁定粒度大，发生锁冲突的概率高
  ②表锁、元素据锁、意向锁

• 4.行级锁

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

八、InnoDB引擎

1.InnoDB引擎-逻辑存储结构

• 逻辑存储结构

  

2.InnoDB引擎-架构

• 架构

  

2.1.InnoDB引擎-架构-内存结构1

• 内存结构

  

  

  

  #可以查看自适应哈希索引的开关是否开启
  show variables like '%hash_index%';
  1
  2

  

  #查看缓冲区大小
  show variables like '%log_buffer_size%';
  #查看日志刷新时机
  show variables like '%flush_log';
  1
  2
  3
  4

2.2.InnoDB引擎-架构-磁盘结构

• 磁盘结构

  
  

  #创建表空间
  create tablespace ts_test add datafile 'user.idb' engine = innodb;
  #指定表空间
  create table user(id int primary key auto_increment,name varchar(10)) engine = innodb tablespace ts_test;
  1
  2
  3
  4

  

2.3.InnoDB引擎-架构-后台线程

• 后台线程

  

  1.Maste Thread

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

  2.IO Thread

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

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

  #查看innodb引擎上的状态信息
  show engine innodb status;
  1
  2

  3.Purge Thread

  主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

  4.Page Cleaner Thread

  协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

3.InnoDB引擎-事务原理

3.1.InnoDB引擎-事务原理-概述

• 事务

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

• 特性

  1.原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

  2.一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。

  3.隔离性(lsolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

  4.持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

3.2.InnoDB引擎-事务原理-redo log

• redo log

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

  redo log主要解决事务的持久性

3.3.InnoDB引擎-事务原理-undo log

• undo log

  回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚 和 MVCC(多版本并发控制)。
  undo log和redo log记录物理日志不一样，它是逻辑日志。记录物理日志不一样，它是逻辑日志。undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

  Undo log销毁： undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

  Undo log存储： undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo log segment。

  undo log主要解决事务的原子性

4.InnoDB引擎-MVCC

4.1.nnoDB引擎-MVCC-基本概念

• 基本概念

  Multi-Version Concurrency Control 多版本并发控制，MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存。

• 当前读

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

• 快照读

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

• MVCC

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

4.2.nnoDB引擎-MVCC-隐藏字段

• 隐藏字段
  记录中的隐藏字段

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

  例子(查看隐藏字段)

  #进入到mysql安装文件夹
  cd /var/lib/mysql
  #查看文件信息
  ls
  1
  2
  3
  4

  

  #进入数据库文件夹
  cd db_test/
  #查看文件信息
  ls
  1
  2
  3
  4

  

  #查看表空间文件
  ibd2sdi student.ibd
  1
  2

  
  
  
  

4.3.nnoDB引擎-MVCC-undo log版本链

• undo log

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

• undo log版本链

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

  

4.4.nnoDB引擎-MVCC-readview介绍

• 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。

4.5.nnoDB引擎-MVCC-原理分析(RC级别)

• 原理分析(RC级别)

  READ COMMITTED： 在事务中每一次执行快照读时生成ReadView。

  

4.6.nnoDB引擎-MVCC-原理分析(RR级别)

• 原理分析(RR级别)

  REPEATABLE READ： 仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

  

4.7.nnoDB引擎-MVCC-实现原理

• 实现原理

  

5.InnoDB引擎-小结

• 1.逻辑存储结构

  表空间、段、区、页、行

• 2.架构

  内存结构
  磁盘结果

• 3.事务原理

  原子性 - undo log
  持久性 - redo log
  一致性 - undo log + redo log
  隔离性 - 锁 + MVCC

• 5.MVCC

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

九、MySQL管理

1.MySQL管理-系统数据库介绍

• 系统数据库介绍

  Mysql数据库安装完成后，自带了以下四个数据库，具体作用如下：

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

• mysql数据据

  表名	存储内容
  user	存储数据库用户信息

• information_schema数据据

  表名	存储内容
  ENGINES	记录存储引擎信息
  INNODB_TABLES	INNODB引擎对应的表结构信息

  视图名	存储内容
  ROUTINES	存储过程及存储函数信息
  SCHEMATA	数据库说明信息
  TABLES	表信息
  TRIGGERS	触发器信息
  VIEWS	视图信息

2.MySQL管理-常用工具1

• 常用工具
  1、mysql

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

  语法：

  mysql [options] [database]
  1

  

  选项：

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

  例子

  #本地登录数据库
  mysql -uroot -proot
  1
  2

  
  例子

  #非本地登录
  mysql -h192.168.3.248 -P3306 -uroot -p1234
  1
  2

  

  例子

  mysql -h192.168.3.248 -P3306 -uroot -p1234  db_test -e "select * from student"
  1

  

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

  例子

  mysql -uroot -proot db_test -e "select * from student"		#db_test是数据库名
  1

  
  例子

  #查看数据库版本
  mysql --version
  mysql -V
  1
  2
  3

  

  #通过帮助文档查看选项
  mysql --help
  1
  2

  2、mysqladmin

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

  语法：

  mysqladmin [OPTIONS] command command....
  1

  

  #通过帮助文档查看选项
  mysqladmin --help
  1
  2

  

  例子

  #查看数据库版本
  mysqladmin -h192.168.3.248 -uroot -p1234 version
  1
  2

  

  例子

  #创建数据库
  mysqladmin -h192.168.3.248 -uroot -p1234 create "db_test01"
  #查看数据库
  mysql -h192.168.3.248 -uroot -p1234 -e "show databases"
  1
  2
  3
  4

  

  #删除数据库
  mysqladmin -h192.168.3.248 -uroot -p1234 drop "db_test01"
  1
  2

  

  3、mysqladmin

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

  语法：

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

  

  选项：

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

  例子

  以Linux服务器为例，二进制日志在 /var/lib/mysql文件夹中

  

  #查看二进制文件
  mysqlbinlog binlog.000004
  1
  2

  

  #显示简单格式，省略掉一些信息。
  mysqlbinlog -s binlog.000004
  1
  2

  4、mysqlshow

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

  语法：

  mysqlshow [OPTIONS] [database [table [column]]]
  1

  

  选项：

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

  例子

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

  

  #查询db_test库中每个表中的字段数，及行数
  mysqlshow -uroot -proot db_test --count
  1
  2

  

  #查询db_test库中student表的详细情况
  mysqlshow -uroot -proot db_test student --count
  1
  2

  

  #查询db_test库中student表中id字段的详细情况
  mysqlshow -uroot -proot db_test student id --count
  1
  2

  

  #查询db_test库中student表中id字段的状态信息
  mysqlshow -uroot -proot db_test student id -i
  1
  2

  

  5、mysqldump

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

  语法：

  mysqldump [OPTIONS] database [tables]
  mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
  mysqldump [OPTIONS] --all-databases [OPTIONS]
  1
  2
  3

  

  连接选项：

  -u, --user=name			#指定用户名
  -p, --password[=name]	#指定密码
  -h, --host=name			#指定服务器ip或域名
  -P, --port=#			#指定连接端口
  1
  2
  3
  4

  连接选项：

  --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文件，数据文件
  1
  2
  3
  4
  5
  6

  例子

  #将数据库db_test备份到D:\backups目录下的db_test.sql文件中
  mysqldump -uroot -proot db_test > D:\backups\db_test.sql
  1
  2

  
  

  #将数据库db_test中student表备份到D:\backups目录下的student.sql文件中
  mysqldump -uroot -proot db_test student > D:\backups\student.sql
  1
  2

  
  

  #将数据库db_test备份到D:\backups目录下的db_test01.sql文件中（不包含数据表的创建语句）
  mysqldump -uroot -proot -t db_test > D:\backups\db_test01.sql
  1
  2

  
  

  #将数据库db_test备份到D:\backups目录下的db_test01.sql文件中（不包含数据）
  mysqldump -uroot -proot -d db_test > D:\backups\db_test01.sql
  1
  2

  #将数据库db_test备份到D:\backups目录下的db_test02.sql文件中（不包含数据）
  mysqldump -uroot -proot -d db_test > D:\backups\db_test02.sql
  1
  2

  
  

  #查看数据库指定备份文件路径
  show variables like '%secure_file_priv%';
  1
  2

  

  #将数据库db_test中student表备份到/var/lib/mysql-files/指定目录下(自动生成两个文件:一个.sql文件，创建表结构的语句;一个.txt文件，数据文件)
  mysqldump -uroot -p1234 -T/var/lib/mysql-files/ db_test student
  #切换到/var/lib/mysql-files/目录
  cd /var/lib/mysql-files/
  #查看文件
  ll
  #查看student.txt内容
  cat student.txt
  #查看student.sql内容
  cat student.sql
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  
  
  

  -T使用需要放到数据库指定备份文件路径下，否者会报错

  6、mysqlimport/source

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

  语法：

  mysqlimport [OPTIONS] database textfile...
  1

  
  例子

  删除student表数据

  

  #查询表数据（没有表数据）
  mysql -uroot -p1234  db_test -e "select * from student";
  #导入student.txt数据
  mysqlimport -uroot -p1234 db_test /var/lib/mysql-files/student.txt
  #查询表数据（数据以导入成功）
  mysql -uroot -p1234  db_test -e "select * from student";
  1
  2
  3
  4
  5
  6

  

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

  语法：

  souurce /路径/xxx.sql
  1

  例子

  #将数据库db_test备份到D:\backups目录下的db_test.sql文件中
  mysqldump -uroot -proot db_test > D:\backups\db_test.sql
  #登录数据库
  mysql -uroot -proot
  #切换到db_test数据库
  use db_test;
  #删除表(我这数据库只有一张表，备份了多少张表尽量全删了)
  drop table student;
  #导入sql文件(sql文件导入完成)
  source D:\backups\db_test.sql
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  

3.MySQL管理-小结

• 1.mysql

  MySQL客户端工具，-e 执行SQL并退出

• 2.mysqladmin

  MySQL管理工具

• 3.mysqlbinlog

  二进制日志查看工具

• 4.mysqlshow

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

• 5.mysqldump

  数据备份工具

• 6.mysqlimport/source

  数据导入工具

此文档来源于网络,如有侵权，请联系删除！