MySQL 必知必会读书笔记 (3)

存储过程

大型系统必须得要存储过程和触发器吗，from 知乎
一般情况下，Web 应用的瓶颈常在 DB 上，所以会尽可能的减少 DB 做的事情，把耗时的服务做成 Scale Out，这种情况下，肯定不会使用存储过程；

而如果只是一般的应用（比如，SAP、peopleSoft、ERP 等企业级别应用），DB 没有性能上的问题，在适当的场景下，也可以使用存储过程。减少开发成本，毕竟其业务逻辑修改频繁，而且为通用，很多时候会把一些业务逻辑编写成存储过程，像 Oracle 会写成包，比存储过程更强大。另外一个原因是服务器的负载是可控，也即系统的访问人数首先是可控的，没有那么大，而且这些数据又非常关键，为此往往使用的设备也比较好，多用存储柜子支撑数据库

至于触发器，我是知道有这东西但从来没用过。我希望风险可控，遇到问题能够快速的找到原因，尽可能不会去使用触发器。

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不限于批处理。

使用存储过程的理由(简单，安全，高性能)：

• 通过把处理封装在容易使用的单元中，简化复杂的操作 - 简单。

• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。 如果所有开发人员和应用程序都使用同一（test）存储过程，则所使用的代码都是相同的 -- 即防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据一致性。

• 简化对变动的管理。如果表名，列名或业务逻辑有变化，只需要更改存储过程的代码。 -- 即保证安全性。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全.

• 存储过程能减少网络流量。

  针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的 Transaction-SQL 语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

• 提高性能。使用存储过程比使用单独sql语句快.
  如果某一操作包含大量的 Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析 优化，并且给出最终被存储在系统表中的执行计划。而批处理的 Transaction-SQL 语句在每次运行时都要进行编译和优化，速度相对要慢一些。

存储过程的缺陷：

• 存储过程的编写比基本SQL复杂。

• 用户可能没有创建存储过程的安全访问权限。

语句格式

mysql-- 无参数 或者 有参数
Delimiter //

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2),
)  
BEGIN 
    SELECT MIN(prod_price)   
    INTO pl
    FROM products;
    SELECT MAX(prod_price)
    INTO ph
    FROM products;
    SELECT Avg(prod_price)
    INTO pa
    FROM products;
END //

Delimiter ;

调用此修改过的存储过程，指定出三个变量名

mysqlcall productpricing(@pricelow, 
                    @pricehigh,
                    @priceaverage);             

mysqlselect @priceaverage;

例子2： 检索出产品平均价格

mysqlCREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2),
)  
BEGIN 
    SELECT Sum(item_price * quantity)
    from orderitems
    where order_num = onumber
    into ototal
END //

Delimiter ;

调用存储过程

mysqlcall productpricing(20005, @total);             

显示查询

mysqlselect @total;

---

游标

游标是一个存储在mysql服务器上的数据库查询，他不是一条select语句，而是被该语句检索出来的结果集。

如定义了ordernumbers的游标，用来检索所有订单的select语句。

使用游标的数据：

mysqlcreate procedure processorder()
begin
    -- declare local variables
    declare o int;
    -- declare the cursor
    declare ordernumbers cursor
    for 
    select order_num from orders;
    -- open the cursor
    open ordernumbers;
    -- get order number
    fetch ordernumbers into o;
    -- close the cursor
    close ordernumbers;
end //

---

数据库恢复技术

事务处理：是用户定义的一个数据库操作序列，这些操作保证成批的MySQL操作要么完全执行，要么完全不执行。 transaction processing 可以用来维护数据库的完整性：如果没有错误发生，整组语句提交给数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

一个事务可以是一条SQL语句，也可以一组SQL语句。事务和程序是两个概念，Generally, 一个程序包含多个事务。

MySQL 的事务支持不是绑定在 MySQL 服务器本身，而是与存储引擎相关:
1.MyISAM：不支持事务，用于只读程序提高性能
2.InnoDB：支持 ACID 事务、行级锁、并发
3.Berkeley DB：支持事务

事务的特性： ACID

A 原子性： 事务是数据库的逻辑工作单位，事务中包括的所有操作要么都做，要么都不做，否则事物将被终止在故障点，和以前的操作将回滚到以前的状态。
C 一致性： 事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态。
I 隔离性： 一个事物的执行不能被其他事物干扰。即一个事物内部的操作及使用的数据对其他事物是隔离的，并发执行的各个事物之间互相不干扰。
D 永久性： 一个事物一旦成功提交，对数据库中数据的修改就是永久性。接下来其他的其他操作或故障不应该对其执行结果有任何影响。

控制事务处理

管理事务处理的关键在于将SQL语句分解为逻辑块，并明确规定数据应该何时回退，何时不回退。保证事务的ACID是事务管理的重要任务。

mysql定义事务

begin transaction // 开始事务
commit // 结束事务(提交事务的所有操作，将事务中所有对DB的更新写回到磁盘上的物理DB中，事务正常结束)
rollback  // 结束事务（回滚。事务在运行过程中发生了某种故障，事务不能正常执行，系统将事务中对DB的所有已经完成的操作全部撤销，回滚到事务开始的状态。）

for eaxample: mysql语句使用rollback来回退/撤销 mysql语句。

select * from orderitems;
start transaction;
delete from orderitems;
rollback;
select * from orderitems;

恢复策略

当系统运行过程中发生故障，利用数据库后备副本和日志文件就可以将数据库恢复到故障之前的某个一致性状态。
利用日志技术进行DB恢复时候，恢复子系统必须搜索日志，确定哪些事务需要REDO,哪些需要UNDO。（正向扫描日志文件，找出故障发生前已经提交的事务（these transactions both have "begin transaction" and "commit"）, 将其事务标识记入重做REDO队列；同时找出故障发生时尚未完成的事务（these transactions just have "begin transaction" not "commit"）将其事务标识记入撤销UNDO队列）。

搜索整个日志需要大量时间 --- 具有检查点的恢复技术。

---

并发控制

多用户数据库系统。当多个用户并发地存取数据库时就会产生多个事务同时存取同一个数据的情况。弱对并发操作不加控制就可能会存取和存储不正确的数据，破坏事务的一致性和数据库的一致性。 -- 所以引入并发控制。

事务ACID特性可能遭到破坏的原因之一是多个事务对数据库并发操作造成的。为了保证事务的隔离性和一致性，DBMS需要对并发操作进行正确调度。

并发操作带来的不一致性主要包括丢失修改，不可重复读和读脏数据。

并发控制的主要技术：封锁，时间戳，乐观控制法。

---

访问控制

确保数据的安全和完整 -- 访问控制, 即创建和管理用户账号。

MYSQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

• 创建用户账号

mysqlcreate user USERNAME identified by 'password';

• 删除用户账号

mysqldrop user USERNAME;

• 设置访问权限

创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访问权限，它们只能登录mysql，但是看不到数据，不能执行任何数据库操作

查看用户的权限：

show grants for USERNAME;

GRANT 给出信息
- 要授予的权限
- 被授予访问权限的数据库或表
- 用户名

grant允许用户在数据库creatdb.*的所有表上使用select

REVOKE，撤销特定的权限。

---

数据库维护

数据库管理员而不是开发者使用这些语句。

mysqlANALYZE TABLE
CHECK TABLE
CHECKSUM TABLE
OPTIMIZE TABLE
REPAIR TABLE

备份数据

首先保证所有数据都被写到了磁盘，需要备份前刷新未写数据

mysqlflush table

备份命令
- mysqldump, 备份数据库到外部文件
- mysqlhotcopy 从一个数据库复制所有数据, 比mysldump快但是不被InnoDB支持。
- nysql的backup table 或者 select into outfile转储所有数据到某个外部文件。

维护数据库

1. 对表进行优化 (优化表主要作用是消除删除或者更新造成的空间浪费)
2. 对表进行分析（分析关键字的分布, 分析并存储 MyISAM 和 BDB 表中键的分布）
3. 对表进行检查（检查表的错误，并且为 MyISAM 更新键的统计内容）
4. 对表进行修复（修复被破坏的 MyISAM 表）

分析表

对表的定期分析可以改善性能，且应该成为常规维护工作的一部分。因为通过更新表的索引信息对表进行分析，可改善数据库性能。

分析关键字的分布, 分析并存储 MyISAM 和 BDB 表中键的分布

mysql 使用analyze table TABLENAME来分析

Table：表示表的名称；
Op：表示执行的操作。analyze 表示进行分析操作。check 表示进行检查查找。optimize 表示进行优化操作；
Msg_type：表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；
Msg_text：显示信息。
检查表和优化表之后也会出现这 4 列信息。

检查表

mysql 使用check table TABLENAME来检查，该语句能检查InnoDB和MyISAM类型的表是否存在错误。而且可以检查视图是否存在错误。

优化表

为什么优化？随着 MySQL 的使用，包括 BLOB 和 VARCHAR 字节的表将变得比较繁冗，因为这些字段长度不同，对记录进行插入、更新或删除时，会占有不同大小的空间，记录就会变成碎片，且留下空闲的空间。像具有碎片的磁盘，会降低性能，需要整理，因此要优化。

三种优化的方式
1. mysql 使用optimize table TABLENAME来检查
2. 利用mysqlcheck对表进行优化
3. 利用myisamchk对表进行优化

想更一进步的支持我，请扫描下方的二维码，你懂的~