六年开发经验，整理Mysql数据库技巧笔记，全网最详细的笔记集合！

数据库

表

DML

DQL

多表查询

内连接查询

外连接查询

子查询

将一条查询语句作为一张虚拟表

Mysql约束

主键约束

特点：主键约束默认包含非空和唯一两个功能。

一张表只能有一个主键。

主键一般用于表中数据的唯一标识。

主键自增约束

唯一约束

外键约束

外键的级联更新和级联删除

视图

视图：是一种虚拟存在的数据表，这个虚拟表并不在数据库中实际存在。

作用：将一些较为复杂的查询语句的结果，封装到一个虚拟表中，后期再有相同需求时，直接查询该虚拟表即可。(实则存储的是一条sql语句)

存储过程

创建存储过程

调用存储过程

查看数据库中所有的存储过程

删除存储过程

存储过程语法 - 变量

存储过程语法 – if 语句

存储过程语法 - 参数传递

存储过程语法 – while 循环

存储函数

创建存储函数

调用存储函数

删除存储函数

触发器

触发器是与表有关的数据库对象，可以在 insert、update、delete 之前或之后触发并执行触发器中定义的 SQL 语句。这种特性可以协助应用系统在数据库端确保数据的完整性、日志记录、数据校验等操作。使用别名 NEW 和 OLD 来引用触发器中发生变化的内容记录。

触发器分类

创建触发器

查看触发器

删除触发器

事务

事务的四大特征(ACID)

原子性(Atomicity)

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

因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性(Consistency)

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态。

也就是说一个事务执行之前和执行之后都必须处于一致性状态。

隔离性(isolcation)

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务。

不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

持久性(durability)

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。

即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

事务的隔离级别

引发的问题

存储引擎

MySQL 支持的存储引擎有很多，常用的有三种：InnoDB、MyISAM、MEMORY。

MyISAM 存储引擎：访问快，不支持事务和外键操作。使用场景：以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高！

InnoDB 存储引擎：支持事务和外键操作，支持并发控制，占用磁盘空间大。(MySQL 5.5版本后默认) 使用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作！

MEMORY 存储引擎：内存存储，速度快，不安全。适合小量快速访问的数据。使用场景：通常用于更新不太频繁的小表，用来快速得到访问的结果！

索引

按照功能分类

普通索引：最基本的索引，没有任何限制。

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。

主键索引：一种特殊的唯一索引，不允许有空值。在建表时有主键列同时创建主键索引。

联合索引：顾名思义，就是将单列索引进行组合。

外键索引：只有 InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。

全文索引：快速匹配全部文档的方式。InnoDB 引擎 5.6 版本后才支持全文索引。MEMORY 引擎不支持。

按照结构分类

BTree 索引：MySQL 使用最频繁的一个索引数据结构，是 InnoDB 和 MyISAM 存储引擎默认的索引类型，底层基于 B+Tree 数据结构。

Hash 索引：MySQL 中 Memory 存储引擎默认支持的索引类型。

创建索引

查看索引

添加索引

删除索引

索引的原理 – 磁盘存储

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的。

位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16KB。

InnoDB 引擎将若干个地址连接磁盘块，以此来达到页的大小 16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

索引的原理 – B+Tree

BTree 数据结构

每个节点中不仅包含 key 值，还有数据。会增加查询数据时磁盘的 IO 次数。

B+Tree 数据结构

非叶子节点只存储 key 值。

所有数据存储在叶子节点。

所有叶子节点之间都有连接指针。

B+Tree 好处

提高查询速度。

减少磁盘的 IO 次数。

树型结构较小。

索引的设计原则

创建索引遵循的原则

对查询频次较高，且数据量比较大的表建立索引。

使用唯一索引，区分度越高，使用索引的效率越高。

索引字段的选择，最佳候选列应当从 where 子句的条件中提取。

索引虽然可以有效的提升查询数据的效率，但并不是多多益善。

最左匹配原则(适用组合索引)

InnoDB和MyISAM存储数据和索引上的区别

innodb是将数据和索引存储在.ibd文件中的

myisam是将索引存储在.myi文件中，将数据存储在.myd文件中，先去myi文件中找到数据的磁盘地址，再去myd文件中根据地址直接获取数据。

MySQL锁机制

锁机制 : 数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则。

锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性。

按操作分类

共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据。

排他锁：也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入。

按粒度分类

表级锁：会锁定整个表。开销小，加锁快。锁定力度大，发生锁冲突概率高，并发度低。不会出现死锁情况。

行级锁：会锁定当前行。开销大，加锁慢。锁定粒度小，发生锁冲突概率低，并发度高。会出现死锁情况。

按使用方式分类

悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。

乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据。

不同存储引擎支持的锁

InnoDB 共享锁

共享锁特点：数据可以被多个事务查询，但是不能修改。

创建共享锁格式：SELECT语句 LOCK IN SHARE MODE;

InnoDB 排他锁

排他锁特点：加锁的数据，不能被其他事务加锁查询或修改。

创建排他锁格式：SELECT语句 FOR UPDATE;

MyISAM 读锁

读锁特点,所有连接只能查询数据，不能修改。

读锁语法格式 加锁：LOCK TABLE 表名 READ; 解锁：UNLOCK TABLES;

MyISAM 写锁

写锁特点,其他连接不能查询和修改数据。

写锁语法格式 加锁：LOCK TABLE 表名 WRITE; 解锁：UNLOCK TABLES;

悲观锁和乐观锁

悲观锁它对于数据被外界修改的操作持保守态度，认为数据随时会修改。

整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。

乐观锁每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。

但是在更新的时候会去判断在此期间数据有没有被修改。

需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

方式一：给数据表中添加一个 version 列，每次更新后都将这个列的值加 1。

读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。

如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。

方式二：和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是 timestamp。

每次更新后都将最新时间插入到此列。

读取数据时，将时间读取出来，在执行更新的时候，比较时间。

如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

行锁和表锁

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。行锁的劣势：开销大；加锁慢；会出现死锁行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句中使用到索引列，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然也可以显示的加锁；