MySQL入门到进阶，学习MySQL，此篇足矣

MySQL是非常热门的关系型数据库（RDBMS）

关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

SQL分类

SQL分为DDL,DML,DCL和DQL,下面会分别对其详细介绍。

DDL:数据库定义语言，主要操作数据库，数据库表，字段的定义

数据类型有数值类型，字符串类型，日期类型。

数值类型：

TINYINT

SMALLINT

MEDIUMINT

INT或INTEGER

BIGINT

FLOAT

DOUBLE

DECIMAL

字符串类型：

   CHAR（长度） //定长字符串：如果超出长度不会报错，只会存储它的最大长度；不论存储几个字符，都会占用10个字符的空间；性能相对较高

   VARCHAR（长度）//变长字符串：如果超出长度则报错；存储几个字符，就占用几个字符的空间；性能相对较低。

TINYBLOB

TINYTEXT

BLOG

TEXT

MEDIUMBLOG

MEDIUMTEXT

LONGBLOG

LONGTEXT

日期类型：

DATE//日期值

TIME//时间值或持续时间

YEAR//年份值

DATETIME//混合日期和时间

TIMESTAMP//混合日期和时间值，时间戳，但是它的取值范围到2038-01-19 03：14：07

数据库操作： 

SHOW DATABASES；

CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则]；

USE 数据库名;

SELECT DATABASE();//查看当前处于哪一个数据库

DROP if exists DATABASE 数据库;

表操作：

show tables;

create table 表名(字段 字段类型 ，字段 字段类型)comment'注释';

DESC 表名；//查看当前表有哪些字段

SHOW CREATE TABLE 表名；//查询表的建表语句

删除：DROP TABLE 表名;

TRUNCATE TABLE 表名字;//删除表并重新创建该表 ，相当于删除该表的所有数据

表结构修改：

ALTER TABLE 表名 ADD(添加字段)/MODIFY（修改字段类型）/CHANGE(修改字段名称及类型)/DROP（删除字段）/RENAME TO...;(修改表名)

DML：用来对表中的数据记录进行增删改操作

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

DELETE FROM 表名 [ WHERE 条件 ] ;

DQL:数据查询语言，用来查询数据库表中的记录

基本查询

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;

SELECT DISTINCT 字段列表 FROM 表名;//去除重复记录

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

聚合函数

COUNT:统计数量  MAX:求最大值 MIN:求最小值 AVG:求平均值 SUM:求和

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];

 where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;//起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

DQL的编写顺序和执行顺序：

编写顺序：SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT

执行顺序：FROM WHERE GROUP BY HAVING SELECT ORDER BY LIMIT

DCL：数据控制语言，用来管理数据库用户，控制数据库的访问权限。

DCL用户管理

权限控制：

查询权限，授予权限，撤销权限.

查询用户：select * from mysql.user;

创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户：DROP USER '用户名'@'主机名' ;

函数:是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

数值函数

日期函数

流程函数

约束：约束时作用于表中字段上的规则，用于限制存储在表中的数据。目的：保证数据库中数据的正确性，有效性和完整性。

添加外键

删除外键

删除/更新行为

多表查询

多表关系：一对多（多对一），多对多，一对一

多表查询分为连接查询和子查询。

连接查询分为内连接和外连接。

内连接 ：相当于A,B集合的交集部分数据

隐式内连接：就是使用where条件，消除不符合条件的无用数据（消除笛卡尔积的无用数据）。

显式内连接：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

外连接

左外连接：查询左表中的所有数据，以及两张表交集部分的数据。。

右外连接：查询右表中的所有数据，以及两张表交集部分的数据

自连接：当前表与自身的连接查询，自连接必须使用表别名。可以是内连接查询，也可以是外连接。

联合查询：就是把多次查询的结果合并起来，形成一个新的查询结果集。

对于联合查询的多张表的列数 必须保持一致，字段类型 也需要保持一致。

第一条SQL  UNION all  第二条SQL（去重则去掉all）

子查询：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

根据子查询结果不同，分为标量子查询，列子查询，行子查询，表子查询 。

根据子查询位置，分为：where之后，from之后，select之后。

多表查询练习。

1.select e.name,e.age,e.job,d.name  from emp e, dept d WHERE e.dept_id=d.dept_id;//隐式内连接

2.select e.name,e.age,e.job,d.name  from emp e INNER JOIN dept d ON e.dept_id=d.id WHERE e.age<30;//显式内连接

3.SELECT DISTINCT d.id,d.name FROM emp e,dept d where e.dept_id=d.id;//去重distinct

4.SELECT e.*,d.name from emp e LEFT join dept d on e.dept_id=d.id where age>40;//左外连接

5.SELECT e.*,s.grade from emp e,salary s WHERE e.salary between e.salary and s.hisal;//隐式内连接

6.SELECT a.*,s.grade from (SELECT e.*,d.id from emp e,dept d where d.name='研发部' and e.dept_id=d.id) a,salary s where a.salary between a.salary and s.hisal;

7.SELECT AVG(e.salary) FROM emp e,dept d where e.dept_id=d.id and d.name='研发部'; //研发部平均薪资

8.SELECT * from emp where salary>(SELECT salary from emp where name='灭绝');

9.select * from emp where salary>(AVG(emp.salary) FROM emp);

10.select * from emp e2 where e2.salary<(SELECT avg(e1.salary) from emp1 e1 where e1.dept_id=e2.dept_id);

11.select d.id,d.name,(SELECT COUNT(*) FROM emp e where e.dept_id=d.id)'人数' * from dept d;//查询所有部门信息，并统计部门的员工人数

12.SELECT s.name,s.number,c.name from student s,course c,student_course sc where s.id=sc.studentid and c.id=sc.courseid;//查询所有学生的选课情况，展示出学生的学号，姓名，课程名称

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

事务操作：

查看/设置事务提交方式：

SELECT @@autocommit;

SET @@autocommit=0;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

开启事务：

START TRANSACTION 或BEGIN;

事务的四大特性：

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

一致性：事务完成时，必须使所有数据都保持一致状态

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

持久性：事务一旦提交或回滚，它对数据库这种的数据的改变就是永久的。

并发事务问题：

脏读：一个事务读取到另一个事务还没有提交的数据。

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

幻读：一个 事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

事务隔离级别：

读未提交

读已提交

可重复读

串行化

查看事务隔离级别：

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别：

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |

READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

事务隔离级别越高，数据越安全，但是性能越低。

存储引擎

MySQL体系结构：连接层—服务层—引擎层—存储层

什么是存储引擎

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

InnoDB

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

DML操作遵循ACID模型 ，支持事务；

行级锁，提高并发访问性能；

支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

MyISAM

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

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

MEMORY

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

内存存放

Hash索引（默认）

使用场景：

索引：是帮助MySQL高效获取数据的数据结构（有序）。

Mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构。

主要结构:

B+tree索引

Hash索引

R-tree索引（空间索引）

Full-text（全文索引）

索引结构的比较：

二叉树

红黑树

红黑树是一颗自平衡二叉树，那这样即使是顺序插入数 据，最终形成的数据结构也是一颗平衡的二叉树,结构如下:

但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点： 大数据量情况下，层级较深，检索速度慢。

B-Tree的演变过程

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。 以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5 个指针：

B-Tree Visualization (usfca.edu)

B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一 下其结构示意图：

MySQL优化后的B+Tree

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在 hash表中。

Hash索引的特点：

A. Hash索引只能用于对等比较(=，in)，即支持精确查询，不支持范围查询（between，>，< ，...）

B. 无法利用索引完成排序操作

C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引。

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

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

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

相对于Hash索引，B+Tree支持范围匹配及排序操作。

索引分类：

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

聚集索引又称为聚簇索引。

二级索引又称为非聚簇索引，辅助索引。

聚集索引选取规则

如果存在主键，主键索引就是聚集索引。 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

InnoDB主键索引的B+Tree高度为多高？

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

高度为2： n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170 1171* 16 = 18736 也就是说，如果树的高度为2，则可以存储 18000 多条记录。

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

索引语法：

创建索引

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

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name ;

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信 息。

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据 库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以 查询为主，那么就要考虑对数据库的索引进行优化了。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志。

SQL性能分析

Profile详情

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

Explain执行计划

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

索引使用

最左前缀法则

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

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

范围查询

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

在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <。

索引列运算

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

字符串不加引号

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

模糊查询

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

or连接的条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。or连接的条件，左右两侧字段都有索引时，索引才会生效。

数据分布影响

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

SQL提示

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

覆盖索引：

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

回表查询

前缀索引：当遇到字符串较长，或者大文本字段。

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

单列索引：即一个索引只包含单个列.

联合索引：即一个索引包含了多个列.

如果涉及多个查询条件，考虑针对于查询字段建立索引时，建立联合索引，而非单列索引。

索引下推（ICP）

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

只能用于range、 ref、 eq_ref、ref_or_null访问方法；只能用于InnoDB和 MyISAM存储引擎及其分区表；对存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;引用了子查询的条件不能下推；引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

索引设计原则：

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

2. 针对于常作为查询条件（where）、排 

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

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

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增 删改的效率。 1 create unique index idx_user_phone_name on tb_user(phone,name);

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

SQL优化

插入数据

批量插入

手动事务提交

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

大批量插入数据(比如几百万的记录)：使用insert语句插入性能较低，使用MySQL提供的load命令进行插入。Load指令需要相应命令开启。

主键优化

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

页分裂

如果一个数据应该被插入第10页，但是10页满了，11页也满了，那怎么办？

直接把第10页分裂成两个，页分裂会发生在插入或更新，并且造成页的错位（dislocation，落入不同的区）

InnoDB用INFORMATION_SCHEMA.INNODB_METRICS表来跟踪页的分裂数。可以查看其中的index_page_splits和index_page_reorg_attempts/successful统计。

一旦创建分裂的页，唯一将原先顺序恢复的办法就是新分裂出来的页因为低于合并阈值（merge threshold）被删掉。这时候InnoDB用页合并将数据合并回来。

另一种方式就是用OPTIMIZE重新整理表。这可能是个很重量级和耗时的过程，但可能是唯一将大量分布在不同区的页理顺的方法。

要注意的是在合并和分裂的过程中会加锁

页合并

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

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

页合并发生在删除或更新操作中，关联到当前页的相邻页。如果页合并成功，在INFOMATION_SCHEMA.INNODB_METRICS中的index_page_merge_successful将会增加。

主键设计原则：

1. 尽量降低主键的长度
2. 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT自增主键
3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
4. 业务操作时，避免对主键的修改。

Order by优化

MySQL的排序，有两种方式，即下面两种。

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

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

Usingindex性能较高，建议使用。

Orderby优化原则：

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

B. 尽量使用覆盖索引。

C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。 D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

Group by 优化

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

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

Limit 优化

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

Count 优化

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

按照效率排序的话，

count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽 量使用 count(*)。

Update 优化

使用innodb存储引擎，update 的时候存在where 条件的情况下，条件字段是索引的情况可以提升更新的效率，避免锁表的情况发生。如果条件字段不是索引，在多个事务同时进行的时候会使行锁升级为表锁，阻塞某些业务，直到业务提交才能继续进行。

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

视图/存储过程/触发器

视图

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

视图的检查选项：

CASCADED级联

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

LOCAL本地

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

视图的更新

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

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

B. DISTINCT

C. GROUP BY

D. HAVING

E. UNION 或者 UNION ALL

视图的作用：

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

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

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

存储过程

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

特点：封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到 的时候直接调用即可。

可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回 值。

减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传 输。

而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

存储过程的创建：

调用：

查看：

删除：

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

变量

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

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

查看系统变量

设置系统变量

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

赋值：

使用：

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

声明：

赋值：

作用域在begin – end之间。

If判断

if 用于做条件判断，具体的语法结构为：

参数：

case：当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时， 执行statement_list2， 否则就执行 statement_list。

注意：如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接。

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

Repeat：repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环 。

loop

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

LOOP可以配合以下两个语句使用：

LEAVE ：配合循环使用，退出循环。

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

游标

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

声明游标

打开游标

获取游标记录

关闭游标

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

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 CREATE TABLE

IF

       NOT EXISTS tb_user_pro ( id INT PRIMARY KEY auto_increament, 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, u_pro );

END WHILE;

CLOSE u_cursor;

END;

CALL p11 ( 40 );

存储函数

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

存储函数弊端：必须有返回值。

触发器

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

触发器和Spring中的AOP有些相似。

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

语法

创建：

查看：

删除：

锁

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

MySQL中的锁，按照锁的粒度分（锁粒度从大到小），分为以下三类：

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

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

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

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

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

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

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

表级锁

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

对于表级锁，主要分为：

表锁又可以分为 两类：

表共享读锁(read lock)

表独写占锁(write lock)

语法：

加锁：lock tables 表名... read/write。

释放锁：unlock tables / 客户端断开连接 。

元数据锁（meta data lock,MDL）

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与 DDL冲突，保证读写的正确性。 这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

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

意向锁

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

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

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

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

行级锁

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

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

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

InnoDB提供了两种类型的行锁：

共享锁（读锁）

排他锁（写锁）

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

1. 临键锁：行锁和间隙锁的组合；同时锁住数据，并锁住数据前面的Gap。在RR隔离级别下支持。

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

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

除此之外，再介绍一下乐观锁和悲观锁。

悲观锁与乐观锁是人们定义出来的概念，你可以理解为一种思想，是处理并发资源的常用手段。

不要把他们与mysql中提供的锁机制(表锁，行锁，排他锁，共享锁)混为一谈。

悲观锁：顾名思义，就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。

悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select … for update来实现悲观锁。

乐观锁：顾名思义，就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。

如果发现冲突了，则返回错误信息给用户，让用户自已决定如何操作。

乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。

给表加一个版本号或时间戳的字段，读取数据时，将版本号一同读出，数据更新时，将版本号加1。

当我们提交数据更新时，判断当前的版本号与第一次读取出来的版本号是否相等。如果相等，则予以更新，否则认为数据过期，拒绝更新，让用户重新操作。

在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景就可以了。

乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
   悲观锁依赖数据库锁，效率低。更新失败的概率比较低。
   随着互联网三高架构（高并发、高性能、高可用）的提出，悲观锁已经越来越少的被应用到生产环境中了，尤其是并发量比较大的业务场景。

InnoDB引擎

逻辑存储结构

1). 表空间 表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在 8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空 间，用于存储记录、索引等数据。

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

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

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

5). 行 行，InnoDB 存储引擎数据是按行进行存放的。 在行中，默认有两个隐藏字段： Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。 Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个 隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

架构

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发 中使用非常广泛。

分为内存机构和磁盘结构

内存结构有：

Buffer Pool

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

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

• free page：空闲page，未被使用。

• clean page：被使用page，数据没有被修改过。

• dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

Change Buffer

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

Change Buffer的意义是什么呢?

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

Adaptive Hash Index

自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持 hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在 进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需 要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。 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：日志刷新到磁盘时机，取值主要包含以下三个：

 1: 日志在每次事务提交时写入并刷新到磁盘，默认值。

 0: 每秒将日志写入并刷新到磁盘一次。

2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

磁盘结构

1.System Tablespace:

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

参数：innodb_data_file_path

1. File-Per-Table Tablespaces

如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索 引 ，并存储在文件系统上的单个数据文件中。

3. General Tablespaces

通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空 间。

4.Undo Tablespaces

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储 undo log日志。

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

6. Doublewrite Buffer Files 1 CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name; 1 CREATE TABLE xxx ... TABLESPACE ts_name; 双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件 中，便于系统异常时恢复数据。

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

后台线程

1. Master Thread
2. IO Thread
3. Purge Thread
4. Page Cleaner Thread

事务原理

我们研究事务的原理，就是研究MySQL的InnoDB引擎是如何保证事务的这四大特性（ACID）的。

而对于这四大特性，实际上分为两个部分。 其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而持久性是通过数据库的锁， 加上MVCC来保证的。

Redo log 解决事务的持久性

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

Undo log 解决事务的原子性

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

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。

1.三个隐藏字段

可以通过命令：ibd2sdi stu.ibd

来查看，如果查询的表有主键，则没有隐藏主键字段。

2.Undo log版本链

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

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

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

ReadView

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务 （未提交的）id。

包含四个核心字段：

而在readview中就规定了版本链数据的访问规则： trx_id 代表当前undolog版本链对应事务ID。

不同的隔离级别，生成ReadView的时机不同：

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

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

综上所述，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。 而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。

MYSQL管理

系统数据库自带了Mysql，Information_schema，performance_schema，sys

常用工具

mysql

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

mysqladmin

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

mysqlbinlog

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

Mysqlshow

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

Mysqldump

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

Mysqlimport/source

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

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

以上内容作了解即可。

本文介绍的MySQL相关的东西先到这里。此外还有日志，主从复制，读写分离，分库分表等没有写出来，原因是本人还没学习，以后再学会补上。