MySQL高级部分知识点

MySQL高级部分

1. MySQL引擎

引擎就是数据库处理数据的一种机制。

不同的引擎执行的策略/机制不同。

不同的需求选用不同的引擎，到达最优选择。

1.1 概述

​ MySQL 中的数据使用各种不同的技术存储在文件中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平，并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

​ 这些不同技术一级配套的相关功能在 MySQL中被称作存储引擎（也称做表类型）。MySQL 默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以根据需要选择不同的存储引擎，以便在选择如何存储你的信息，如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

​ 数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。

1.2 查看支持的引擎

SHOW ENGINES;

查看表引擎

SHOW TABLE STATUS LIKE '表名'

修改引擎

​ 方式1：将 mysql.ini中 default-storage-engine=InnoDB，重启服务

​ 方式2：建表时指定

CREATE TABLE 表名(...)ENGINE=MYISAM;

​ 方式3：建表后修改

ALTER TABLE 表名 ENGINE = INNODB;

存储引擎主要有：1.MyISAM ，2.InnoDB，3.Memory，4.Blackhole，5.CSV，6.Performance_Schema，7.Archive，8.Federated，9.Mrg_MyISAM

我们主要分析适用 MyISAM 和 InnoDB。

1.3 InnoDB

InnoDB：默认的存储引擎

InnoDB是一个事务型的存储引擎，有行级锁定和外检约束。

行级锁定意思是锁的粒度比较小。

InnoDB引擎提供了对数据库ACID事务（原子性、一致性、隔离性、持久性）的支持，并且实现了SQL标准的四种隔离级别，该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统。MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持 FULLTEXT 类型的索引（全文检索），而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE 时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用InnoDB 引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时 MySQL不能确定要扫描的范围，InnoDB同样会锁住全表。

**适用场景：**适合处理多重并发的更新请求；支持事务（只有InnoDB是支持事务的）；外键约束（只有InnoDB是支持外键的）；支持自动增加列属性 auto_increment（主键自增记录自增后的数值）。

以前不支持全文检索，mysql 8 支持全文检索（FULLTEXT）。

1.4 MyISAM

​ MyISAM也是MySQL的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

适用场景：不支持事务的设计，不支持外键的表设计。

MyISAM极度强调快速读取操作。

MyISAM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE 时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyISAM也是很好的选择。

小结：

​ 不支持事务，外键，不支持行级锁

​ 查询快

2. 索引

​ **在MySQL中，索引由数据表中一列或多列组合而成，创建索引的目的是为了优化数据库的查询速度。**其中，用户创建的索引指向数据库中具体数据所在位置。当用户通过索引去查询数据库中的数据中，不需要遍历所有数据库中的所有数据。这样，大幅度提高了查询效率。

2.1 为什么需要索引？

​ 当数据量很大时，索引的作用就凸显出来了。对于少量的数据，查询伴随着全表的一个扫描，查询速度会很快，但是，随着数据量的增加，去表扫描这种方式的查询就会导致性能急剧下降。

​ 索引类似于书的目录，在一本书前面加上目录，查找内容时不必逐页翻阅就能够快速地找到所需的内容。借助索引，执行查询时不必扫描整个表就能够快速地找到所需要的数据。

2.2 索引优势

​ 提高数据检索的效率，降低数据库的I/O成本；

​ 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

2.3 索引劣势

​ 实际上，所以呢也是一张”表“，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用磁盘空间的。

​ 虽然索引大大提高了查询速度，同时却会降低更新表的速度，例如对表进行INSERT，UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的的键值变化后的索引信息。

2.4 索引分类

2.4.1 主键索引

​ 设置主键后数据库会自动建立索引

ALTER TABLE 表名 add PRIMARY KEY 表名(列名); 
-- 删除建主键索引：
ALTER TABLE 表名 drop PRIMARY KEY ;

2.4.2 单值索引

​ 即一个索引只包含单个列，一个表可以有多个单列索引

-- 创建单值索引 
CREATE INDEX 索引名 ON 表名(列名); 
-- 删除索引： 
DROP INDEX 索引名;

2.4.3 唯一索引

​ 索引列的值必须唯一，允许为 null

CREATE UNIQUE INDEX 索引名 ON 表名(列名); 
-- 删除索引 
DROP INDEX 索引名 ON 表名;

2.4.4 复合索引

​ 即一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建索引），当表的行数远大于索引列的数目时可以使用复合索引。

-- 创建复合索引
CREATE INDEX 索引名 ON 表名(列 1,列 2...); 
-- 删除索引： 
DROP INDEX 索引名 ON 表名;

查看索引：

SHOW INDEX FROM 表名;

2.5 索引创建原则

2.5.1 哪些情况需要创建索引

​ 主键自动建立唯一索引

​ 频繁作为查询条件的字段应该创建索引（where 后面的语句）

​ 查询中与其它表关联的字段，外键关系建立索引

​ 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

2.5.2 哪些情况不要创建索引

​ 表记录太少

​ 经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

​ where 条件里用不到的字段不创建索引

​ 数据重复且分布平均的表字段 如性别（男/女）

2.5.3 索引数据结构

索引的特点：

​ 索引查询快

为什么使用B+Tree作为索引结构？

​ 哈希结构快，但它是散列的算出来的位置是不连续的且它是无序的，而我们要求是有序的，如id>10，条件范围查询不适应

​ 树，红黑树，加入按递增的方式添加节点， 深度太大，查询慢

​ 而B+Tree的一个节点可以存储多个数据，非叶子结点只存储索引，不存储数据。所有的数据都在叶子节点存储，而且数据与数据之间有连接适合范围查询。而且树的高度固定，查询快。

索引数据结构 （MySQL采用的是 B+Tree 的方式实现）

​ B+Tree 是在B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree 实现其索引结构。

​ 非叶子节点不存储数据，只存储索引，可以放多条索引。

​ 所有叶子结点之间都有一个链指针。

​ 数据记录都存放在叶子节点中。

2.6 聚簇索引和非聚簇索引

2.6.1 聚簇索引

​ 找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引。

​ 不需要回表查询的索引也是聚簇索引

​ 如：select no from student where no = “10”，使用非主键索引查询，结果只是查询索引列，没有查询其他列，此时也是聚簇索引。

2.6.2 非聚簇索引

​ 索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值（主键）再次回表查询，非聚簇索引也叫做辅助索引。

​ 先通过非主键索引查询，查询到主键，在通过主键回表查询（相当于二次查询）到需要的数据。

2.6.3 判断标准：

​ 是否进行回表查询。

3.事务

3.1 概述

​ 事务时数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

​ 在 MySQL中只有使用了 Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL语句要么全部执行，要么全部不执行。

​ 事务用来管理 insert，update，delete语句。

理解：

​ 事务就是一次对数据库的一个完整的操作过程，这个过程中包含一次或多次操作，这些操作要么都成立，要么都失败。

​ 提交事务，一次对数据库的整个操作（可包含多条SQL及一些其它的代码）就完成了。保证了对数据库操作的原子性（不可拆分）。

作用：

​ 用来维护数据库的完整性。

3.2 事务特性（ACID）

​ 一般来说，事务必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

​ 原子性：要么都成功，要么都失败，保证操作的原子性。

​ **隔离性：**多事务并发执行时的隔离机制。

​ 持久性：事务提交后，数据在硬盘上就定格，不能回滚。

​ 一致性：就是多次对数据库操作后，最终的数据与我们的预期值一致。（上面的三种特性最终还是要保证数据操作的一致性）

3.2.1 原子性

​ 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就行这个事务从来没有执行过一样。

3.2.2 一致性

​ 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3.2.3 隔离性

​ 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（Read committed）、可重复读（repeatable read）和串行化（Serializable）。

3.2.4 持久性

​ 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设规则。比如不能出现转了100，对方收到了50的情况。前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。

3.3 事务设置

​ 默认情况下，MySQL启用自动提交模式（变量autocommit为 ON ）。这意味着，只要你执行 DML操作(数据操作语言DML)的语句，MySQL会立即隐式提交事务。

​ 由于变量autocommit分会话系统变量与全局系统变量，所以查询的时候，最好区别是会话系统变量还是全局系统变量。

补充：

​ SQL语言共分为四大类：数据查询语言DQL，数据操作语言DML，数据定义语言DDL，数据控制语言DCL。

MySQL 事务处理主要有两种方法：

注意：

SESSION / GLOBAL是有区别的：
	SESSION 会话级别，临时的，会话窗口一关就失效了
	GLOBAL 全局的，会话窗口关闭还是有效的

1.用BEGIN，ROLLBACK，COMMIT实现

BEGIN; / START TRANSATION; 开始一个事务
ROLLBACK  事务回滚，使用回滚必须要开启事务
COMMIT  事务确认 确认提交事务后就不能再回滚了

2.直接用 SET 来改变MySQL 的自动提交模式：

SET SESSION / GLOBAL autocommit=0; 禁止自动提交
SET SESSION / GLOBAL autocommit=1;开启自动提交

查看 autocommit 模式

SHOW SESSION / GLOBAL VARIABLES LIKE 'autocommit';

3.4 并发事务处理带来的问题

​ 当两个或者多个事务选择同一行，然后基于最初选定的值进行更新操作时，由于每个事务都不知道其他事务的存在，则会发生丢失更新问题，即最后的更新并覆盖了前一个程序员所做的更改。

问题有三类：

​ 1.脏读

​ 2.不可重复读

​ 3.幻读

假设有两个事务A和B，同时并发。

(1) 脏读

1. 事务B更新年龄 18
2. 事务A读取数据库信息，年龄是 18
3. 事务 B 回滚
   
   这种情况就叫做脏读：读取到其他事务未提交的数据。

​ A 事务读到了B 事务未提交事务的数据，一旦 B 事务回滚，A 事务读到的数据就是脏数据。

(2) 不可重复读

​ 在事务A 中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。

这种情况就叫做不可重复读：在同一个事务中两次读取的数据不一致。

​ A 事务读取到了B 事务已提交的数据，A事务在B 事务提交前后两次读取到的数据不一致。

(3) 幻读

​ 在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

1. 事务A读取年龄大于15 的数据，发现有1条记录
2. 事务B插入一条记录，并提交
3. 事务A 再读取年龄大于 15 的数据，发现有2条记录
   
   这种情况就叫做幻读：在同一个事务中两次读取到的数据量不一致。一般幻读出现在范围查询。

​ A事务查询两次的数据结构不一致，表现在两次查询的数量不一致。

3.5 事务隔离级别

​ 只有InnoDB支持事务，所以这里说的事务隔离级别是指InnoDB下的事务隔离级别。

3.5.1 查看当前会话隔离级别

SHOW VARIABLES LIKE 'tx_isolation'; mysql5 
SELECT @@global.transaction_isolation,@@transaction_isolation; mysql8

3.5.2 设置当前会话隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

3.5.3 读未提交(read uncommitted)

一个事务可以读取到另一个事务未提交的修改。这会带来脏读，幻读，不可重复读问题。

3.5.4 读已提交(read committed)

一个事务只能读取另一个事务已经提交的修改。其避免了脏读，仍然存在不可以重复读和幻读问题。

3.5.5可重复读（repeatable read,MySQL默认隔离级别）

同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但是幻读依然存在。

3.5.6 串行化(serializable)

事务串行执行，避免了以上所有问题。在一个事务执行时，会把整个表锁住。

4. 锁机制

4.1 概述

​ 隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。

​ 锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

4.2 行锁与表锁

​ 按照锁粒度，锁可以分为表锁、行锁以及其他位于两者之间的锁。

​ 表锁在操作数据时会锁定整张表，并发性能较差。

​ 行锁只锁定需要操作的数据，并发性能好。

​ 由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyISAM只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

InnoDB为例。

4.2.1 行锁

​ 行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。

​ 行级锁 分为共享锁 和 排他锁。

​ 当隔离级别不能是串行化时吗，多个事务对同一行数据进行写操作时，会把这一行数据锁住。

特点：

​ 开销大，加锁慢；

​ 会出现死锁；

​ 锁定力度最小，发生锁冲突的概率最低，并发度也最高。

共享锁(S)

​ 又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A 加上 S锁，则事务T可以读A 但不能修改A，其他事务只能再对A 加S锁，而在T释放A上的S锁之前不能对A做任何修改。

排他锁(X)

​ 又称写锁，允许获取排他锁的事务更新数据，阻止其他事物取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

​ update，delete，insert 都会自动给设计到的数据加上排他锁，select语句默认不会加任何所类型。

​ 如果加排他锁可以使用 select … for update 语句，加共享锁可以使用 select … lock in share mode 语句。

4.2.2 表锁

​ 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。

​ 表级锁定分为表共享锁与表排他锁。

​ 隔离级别为串行化时，使用表级锁。

特点：

​ 开销小，加锁块；

​ 不会出现死锁；

​ 锁定粒度大，发出锁冲突的概率最高，并发度最低。

5. MVCC

​ MVCC(多版本并发控制 Multi-Version Concurrent Control)，是MySQL提高性能的一种方式，配合 Undo log （记录版本的地方）和版本链，替代锁，让不同事务的读-写、写-读操作可以并发执行（前提：非串行化的情况下），从而提升系统性能。一般在使用 读已提交（READ COMMITTED）和 可重复读（REPEATABLE READ）隔离级别的事务中实现。

​ 可以解决可重复读问题。

理解:

​ 使用 Undo log 记录版本，形成版本链，有两个隐藏列记录

​ 修改 删除前都要进行版本记录，回滚时需要，并发读需要。

​ 比如我们给数据库发送一个删除操作时，它会把 原来的数据“拍个照”（快照），记下来，然后在执行删除操作，万一有问题，可以回滚到原来的版本上去。（快照是数据存储的某一时刻的状态记录；备份则是数据存储的某一时刻的副本。）

基本原理：

• ​ 事务
• ​ 版本链

​ 对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列。

​ **trx_id：**每次对某条聚簇索引记录进行改动时，都会把对应的事务id赋值给 trx_id 隐藏列。

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

基本特征：

​ 每行数据都存在一个版本，每次数据更新时都更新该版本。

​ 修改时Copy 出当前版本随意修改，各个事务之间无干扰。

​ 保存时比较旧版本，如果成功（commit），则覆盖原纪录；失败则放弃copy(rollback)。

ReadView

​ 使用 读未提交 隔离级别的事务来说，直接读取记录的最新版本，从版本链读取，读取是最新的。

​ 使用 串行化 隔离级别的事务来说，使用加锁的方式来访问记录，加锁了，直接读表中的数据。

​ 使用 读已提交 隔离级别的事务，需要用到版本链。每次读取数据是，都会生成readview(将所有事务操作的记录在版本链中拍了个照)，造成了不可重复读。

​ 使用 可重复读 隔离级别的事务，需要用到版本链。第一次读取时生成readview，解决了不可重复读。

核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。

​ 所以在InnoDB中设计了一个 ReadView 的概念，这个 ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为m_ids。所以我们在开启一次会话进行SQL读写，开始事务并生成readview时，会把当前系统中正在执行的写事务写入到m_ids列表中，另外还会存储两个值：

min_trx_id：该值代表生成ReadView时 m_ids中的最小值

max_trx_id：该值代表生成ReadView时系统中应该分配下一个事务的id值。

判断可见性的步骤如下：

​ 如果记录的trx_id列小于min_trx_id，说明肯定可见。

​ 如果记录的trx_id列大于max_trx_id，说明肯定不可见。

​ 如果记录的trx_id列在min_trx_id和max_trx_id之间，就要看一下该trx_id在不在m_ids列表中，如果在，说明不可见，否则可见。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，以此类推，直到版本链汇总的最后一个版本，如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

​ 在MySQL中，READ COMMITTED 和 REPEATABLE READ 隔离级别的一个非常大的区别就是他们生成ReadView的时机不同。

​ READ COMMITTED：每次读取数据前都生成一个ReadView

​ REPEATABLE READ：在第一次读取数据时生成一个ReadView

6. 视图

6.1 什么是视图？

​ 视图是基于查询的虚拟表，可以理解为：视图就是一条SELECT语句执行后返回的结果集。

​ SELECT 语句所查询的表称为视图的基表，而查询的结果集称为虚拟表，视图本身并不存储具体的数据，视图的数据存在于视图的基表中，基本表数据发生了改变，视图的数据也会跟着改变。

6.2 为什么使用视图？

​ 使用视图是为了方便复杂的查询语句。基本思路是将复杂的查询语句定义在视图内部，然后对视图进行查询，从而简化复杂的查询语句。

定义视图

CREATE VIEW 视图名 AS SELECT 列 1，列 2... FROM 表(查询语句);

使用视图

SELECT * FROM 视图名

删除视图

drop view 视图名

7. 存储过程

7.1 概述

​ 如果实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现，那么可以将这组复杂的SQL语句集编写在数据库中，由JDBC调用来执行这组SQL语句，把编写在数据库中的SQL语句集称为存储过程。

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

​ 存储过程类似于JAVA 语言中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为 IN、OUT、INOUT 三种类型。IN 类型的参数表示接受调用者传入的数据，OUT类型的参数表示向调用者返回数据，INOUT类型的参数可以接受调用者传入的参数，也可以向调用者返回数据。

7.2 MySQL存储过程的定义

创建存储过程的语法格式

create procedure 存储过程名（[in 变量名 类型，out 参数 2，…]） begin
	[declare 变量名 类型 [DEFAULT 值];] 
	存储过程语句块; 
end;

语法解析：

1.存储过程的参数分为in，out，inout三种类型。

2.in代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。

3.out代表输出参数，表示该参数的值经存储过程计算后，将out 参数的计算结果返回给调用程序。

4.inout代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将 inout参数的计算结果返回给调用程序。

5.存储过程中的语句必须包含在begin 和end 之间。

6.declare 用来声明变量，变量默认复制使用 default，语句块中改变变量值，要使用 set 变量 = 值;

7.3 存储过程的使用

定义第一个存储过程

-- 开始位置 
DELIMITER$$ 
CREATE PROCEDURE test() 
BEGIN 
	-- 声明变量
    DECLARE v_name VARCHAR(20) DEFAULT 'jim'; 
    SET v_name = 'tom'; -- 变量赋值 
    SELECT v_name; -- 测试输出语句 
END$$ -- 结束位置 
-- 调用存储过程 
CALL test1()

定义一个有参数的存储过程

DELIMITER$$ 
CREATE PROCEDURE findUserCount(IN p_type INT,OUT p_count INT) 
BEGIN 
	-- 把 sql 中查询的结果赋给 变量 
	SELECT COUNT(*) INTO p_count FROM USER WHERE TYPE = p_type; 
	SELECT p_count;
END$$ 
-- 测试
CALL findUserCount(1,@p_count); -- @p_count 测试输出参数

流程控制语句 if else

DELIMITER$$
CREATE PROCEDURE test(IN p_day INT)
BEGIN 
	IF p_day=0 THEN 
		SELECT "星期天"; 
	ELSEIF p_day=1 THEN 
		SELECT "星期一"; 
	ELSEIF p_day=2 THEN 
		SELECT "星期二"; 
	ELSE 
		SELECT "无效日期"; 
	END IF; 
END$$ 
-- 测试
CALL test(2)

case when

DELIMITER$$ 
CREATE PROCEDURE test(IN p_day INT) 
BEGIN 
	CASE WHEN p_day = 0 THEN 
		SELECT "星期天"; 
	ELSE
		SELECT "星期一"; 
	END CASE;
END$$ 
CALL test(2)

循环

DELIMITER$$ 
CREATE PROCEDURE test7() 
BEGIN
	DECLARE v_num INT DEFAULT 0; 
	-- 循环开始 
	addnum:LOOP 
		SET v_num = v_num+1; -- 循环语句 
	-- 循环结束条件
	IF v_num = 10 THEN
		LEAVE addnum;
	END IF;
    END LOOP; -- 循环结束 
    SELECT v_num; 
END$$ 
CALL test7()

使用存储过程插入信息

DELIMITER$$ 
CREATE PROCEDURE saveUser(IN p_account VARCHAR(20),IN p_sex CHAR(1),OUT res_mark INT) 
BEGIN 
	DECLARE v_count INT DEFAULT 0;
    	SELECT COUNT(*) INTO v_count FROM t_user WHERE account = p_account;
        IF v_count = 0 THEN 
        	INSERT INTO t_user(account,sex)VALUES(p_account,p_sex); 
        	SET res_mark = 0;
        ELSE
        	SET res_mark = 1;
        END IF;
END$$

8. 函数

8.1 函数语法

create function 函数名([参数列表]) returns 数据类型 
begin 
	DECLARE 变量； 
		sql 语句; 
	return 值; 
end;

注意：

​ 1.参数列表包含两部分：参数名 参数类型

​ 2.函数体：肯定会有 return 语句，如果没有会报错

​ 3.函数体中仅有一句话，则可以省略 begin end

​ 4.使用 delimter 语句设置结束标记

​ 设置函数可以没有参数

SET GLOBAL log_bin_trust_function_creators=TRUE;

​ 删除函数

DROP FUNCTION 函数名;

不带参数

DELIMITER$$ 
CREATE FUNCTION test() RETURNS INT 
BEGIN
	DECLARE v_num INT;
    SELECT COUNT(*) INTO v_num FROM t_user;
   RETURN v_num;
END$$ 
-- 测试
select test() from dual;

带参数

DELIMITER$$
CREATE FUNCTION findDeptNameById(p_id INT) RETURNS VARCHAR(10) 
BEGIN
	DECLARE v_name VARCHAR(10); 
	SELECT NAME INTO v_name FROM dept WHERE id = p_id; 		RETURN v_name;
END$$
SELECT account,findDeptNameById(dept_id) FROM user

有参数，有判断

DELIMITER$$ 
CREATE FUNCTION checkUserType(p_type INT) RETURNS VARCHAR(4) 
BEGIN
	IF p_type = 0 THEN 
		RETURN '管理员';
    ELSE 
    	RETURN '业务用户';
    END IF; 
END$$ 
SELECT tu.account,checkUserType(tu.type)utype FROM user tu

9. JDBC调用存储过程

mybatis 调用存储过程

<parameterMap type="map" id=“usermap">
	<parameter property="addend1" jdbcType="VARCHAR" mode="IN"/> 
	<parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>

jdbcType 必须制定

<insert id="saveUserDemo" parameterMap="usermap" statementType="CALLABLE"> 
{call saveuser(?, ?)} 
</ insert > 
    
Map<String, Object> parms = new HashMap<String, Object>(); 		parms.put("addend1", 3);
	userDao.saveUserDemo(parms); 
	parms.get(“result”);//获得输出参数

10. 触发器

​ 触发器（trigger）是一种特殊的存储过程，其特殊性在于它并不需要用户直接调用，而是在对表添加、修改、删除之前或者之后自动执行的存储过程。

触发器具有以下特点：

​ 1.与表相关联

​ 触发器定义在特定的表上，这个表称为触发器表。

​ 2.自动激活触发器

​ 当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。

​ 3.不能直接调用

​ 与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。

​ 4.作为事务的一部分

​ 触发器与激活触发器的语句一起作为对一个单一的事务来对待，可以从触发器中的任何位置回滚。

定义触发器的语法规则：

CREATE TRIGGER 触发器名称 触发时机 触发事件 
ON 表名称 
FOR EACH ROW -- 行级触发
BEGIN
	语句
END;

语法解析：

​ 1.触发器名称：是用来标识触发器的，由用户自定义。

​ 2.触发时机：其值是before 或after。

​ 3.触发事件：其值是 insert，update和delete

​ 4.表名称：标识建立触发器的表名，即在哪张表上建立触发器

​ 5.语句：是触发器程序体，触发器程序可以使用begin 和 end 作为开始和结束，中间包含多条语句。

例子：

删除用户时，自动触发删除用户菜单关系

DELIMITER $$
CREATE TRIGGER delete_user_menu BEFORE DELETE ON t_user 
FOR EACH ROW 
BEGIN
	DELETE FROM t_user_menu WHERE user_id = old.id; 
END$$;

新增用户时，自动向其他表插入数据

DELIMITER $$ 
CREATE TRIGGER save_user_log AFTER INSERT ON user 
FOR EACH ROW 
BEGIN 
	INSERT INTO test(id,NAME)VALUES(new.id,new.account); END$$;
    
INSERT INTO user(account)VALUES('jim')

在行级触发器代码中，可以使用 old 和 new 访问到该行的旧数据和新数据，old 和 new 是对应表的行记录类型变量。

11. Sql优化

为什么要对 SQL 进行优化

​ 项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断 SQL对程序的运行效率有多大，故很少针对 SQL进行专门的优化，随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

SQL优化的一些方法

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 ORDER by 涉及的列上建立索引。

2.应尽量避免索引失效

​ 2.1 在where子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

	select id from t where num is null

可以在 num上设置默认值为0，确保表中num 列没有 null 值，然后这样查询：

select id from t where num=0

​ 2.2 应尽量避免在 where 子句中使用 != 或<> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。

​ 2.3 应尽量避免在 where 子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

​ 2.4 in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

​ 对于连续的数据，能用 between 就不要用 in 了:

select id from t where num between 1 and 3

​ 2.5 下面的查询会导致全表扫描：

select id from t where name like '%abc%'

​ 2.6 应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

​ 应改为:

select id from t where num=100*2

​ 2.7 应尽量避免在where 子句中对字段进行函数在操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'
-- name 以 abc 开头的 id

​ 应改为：

select id from t where name like 'abc%'

3. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率。因为 insert 或update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数量最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

4. 尽量使用数据型字段，若致函数值信息的字段尽量不要涉及为字符型，这回降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
5. 尽可能的使用 varchar 代替 char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
6. 任何地方都不要使用 select * from t ，用具体的字段列代替 “*”，不要反悔用不到的任何字段。
7. 尽量避免向客户端返回大数据两，若数据量过大，应该考虑相应需求是否合理。