MySQL 存储过程简单了解&数据库优化简单方式

原文地址： https://blog.csdn.net/H_Rhui/article/details/96920260

最近看到触发器之后，就想了解一下存储过程。触发器是一种和表关联的特殊的存储过程。

一：存储过程定义：

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

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

二：存储过程使用：

存储过程就是具有名字的一段代码，用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中。

1. 语法格式：

声明语句结束符，可以自定义：
DELIMITER $$ 或 DELIMITER // 
 
声明存储过程：
CREATE PROCEDURE demo_in_parameter(IN p_in int)    
存储过程的开始和结束符号：
BEGIN …… END
变量赋值：
SET @p_in=1
变量定义：
DECLARE l_int int unsigned default 5000000;
 
创建过程体：
create function 存储函数名(参数)

存储过程体

存储过程体包含在过程调用时必须执行的语句，例如： dml、ddl 语句， if-then-else 和 while-do 语句、声明变量的 declare 语句等
过程体格式：已 begin 开始，end 结束（可嵌套）

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

注意：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

为语句块贴标签:
1、增强代码的可读性
2、在某些语句(例如:leave和iterate语句)，需要用到标签
```
[begin_label:] BEGIN
　　[statement_list]
END [end_label]
```

例如：

label1: BEGIN
　　label2: BEGIN
　　　　label3: BEGIN
　　　　　　statements; 
　　　　END label3 ;
　　END label2;
END label1

2. 存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类型...])

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

1）in 输入参数

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;
 
mysql> set @p_in=1;
 
mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+
 
+------+
| P_in |
+------+
|    2 |
+------+
 
mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

以上可以看出，p_in 在存储过程中被修改，但并不影响 @p_id 的值，因为前者为局部变量、后者为全局变量。

使用 Navicat 创建存储过程：

新建函数，选择过程
选择模式，定义参数

编辑过程体；如：

 
BEGIN
 
SELECT p_in;
 
SET p_in = 2;
 
SELECT p_in;
 
END

保存存储过程，点击运行按钮运行输入参数。或者通过 sql 命令执行存储过程。 call in_param(1);

2) out输出参数

mysql> delimiter //
 
mysql> create procedure out_param(out p_out int)
 
-> begin
 
-> select p_out;
 
-> set p_out=2;
 
-> select p_out;
 
-> end
 
-> //
 
mysql> delimiter ;
 
 
 
mysql> set @p_out=1;
 
 
 
mysql> call out_param(@p_out);
 
+-------+
 
| p_out |
 
+-------+
 
| NULL |
 
+-------+
 
　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
 
+-------+
 
| p_out |
 
+-------+
 
| 2 |
 
+-------+
 
 
 
mysql> select @p_out;
 
+--------+
 
| @p_out |
 
+--------+
 
| 2 |
 
+--------+
 
　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

3) inout 输入参数

mysql> delimiter $$
 
mysql> create procedure inout_param(inout p_inout int)
 
-> begin
 
-> select p_inout;
 
-> set p_inout=2;
 
-> select p_inout;
 
-> end
 
-> $$
 
mysql> delimiter ;
 
 
 
mysql> set @p_inout=1;
 
 
 
mysql> call inout_param(@p_inout);
 
+---------+
 
| p_inout |
 
+---------+
 
| 1 |
 
+---------+
 
 
 
+---------+
 
| p_inout |
 
+---------+
 
| 2 |
 
+---------+
 
 
 
mysql> select @p_inout;
 
+----------+
 
| @p_inout |
 
+----------+
 
| 2 |
 
+----------+
 
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

注意：

1、如果过程没有参数，也必须在过程名后面写上小括号例：

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

2、确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

建议：

输入值使用in参数。
返回值使用out参数。
inout参数就尽量的少用。

3. 变量

1）变量定义

局部变量声明一定要放在存储过程体的开始：

datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

例如：

DECLARE l_int int unsigned default 4000000;
 
DECLARE l_numeric number(8,2) DEFAULT 9.95;
 
DECLARE l_date date DEFAULT '1999-12-31';
 
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
 
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

2）变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

3）用户变量

在MySQL客户端使用用户变量:

mysql > SELECT 'Hello World' into @x;
 
mysql > SELECT @x;
 
+-------------+
 
| @x |
 
+-------------+
 
| Hello World |
 
+-------------+
 
mysql > SET @y='Goodbye Cruel World';
 
mysql > SELECT @y;
 
+---------------------+
 
| @y |
 
+---------------------+
 
| Goodbye Cruel World |
 
+---------------------+
 
 
 
mysql > SET @z=1+2+3;
 
mysql > SELECT @z;
 
+------+
 
| @z |
 
+------+
 
| 6 |
 
+------+

在存储过程中使用用户变量

CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
 
SET @greeting='Hello';
 
CALL GreetWorld( );
 
-- 输出： hello World

在存储过程间传递全局范围的用户变量

CREATE PROCEDURE p1() SET @last_procedure='p1';
 
CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);
 
CALL p1();
 
CALL p2();
 
 
 
-- 输出 Last procedure was p1

注意:

1、用户变量名一般以@开头
2、滥用用户变量会导致程序难以理解及管理

4. 存储过程的查询、删除

-- 查询存储过程
 
show procedure status where db='test';
 
-- 删除存储过程
 
DROP PROCEDURE p1;
 
DROP PROCEDURE p2;

5.MySQL存储过程的控制语句

1) 变量作用域

DELIMITER //
 
CREATE PROCEDURE proc3()
 
BEGIN
 
declare x1 varchar(5) default 'outer';
 
BEGIN
 
DECLARE x1 VARCHAR (5) DEFAULT 'inner' ;
 
SELECT x1 ;
 
END;
 
SELECT x1 ;
 
END;//
 
DELIMITER ;
 
 
 
CALL proc3();
 
-- 输出 inner
 
-- 输出 outer

2). 条件语句

-- 创建表用来测试
 
CREATE TABLE `t` (
 
`s` int(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
 
-- 初始化一条数据
 
INSERT INTO `test`.`t` (`s`) VALUES (0);
 
 
 
 
 
DELIMITER //
 
CREATE PROCEDURE proc2(IN parameter int)
 
BEGIN
 
declare var int;
 
set var=parameter+1;
 
if var=0 THEN
 
INSERT INTO t VALUES(1);
 
end if;
 
if parameter=0 then
 
update t set s=s+1;
 
else
 
update t set s=s+2;
 
end if;
 
END;//
 
DELIMITER ;
 
 
 
// 调用存储过程
 
CALL proc2(1);
 
 
 
// 删除存储过程
 
drop PROCEDURE proc2;

三：初步学习总结：

存储过程优点：

存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程可以用在数据检验，强制实行商业逻辑等。
存储过程只在创建时编译，以后每次执行存储过程都不需要再重新编译，而普通的sql语句每执行一次就编译一次，因此使用存储过程可以大大提高数据库的执行速度。
存储过程创建一次可以多次调用，可以减少我们开发人员的工作量
安全性高，存储过程可以屏蔽对底层数据库对象的直接访问。
提高我们的效率，降低我们的网络负载（通常情况下，复杂的业务逻辑需要执行多条sql语句，这些语句分别都需要客户端去连接服务器端，这个时候就会产生大量的网络传输，这个时候，结合业务，放在存储过程中只需要一次连接）
安全性高，存储过程可以屏蔽对底层数据库对象的直接访问。

存储过程缺点：

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程的性能调校与撰写，受限于各种数据库系统。

四：数据库优化简谈

1. 查询和定位慢查询

查询是否开启了慢查询：

show variables like '%slow%'; -- Mysql 默认是关闭的

-- 开启慢查询
 
set global slow_query_log=1;
 
 
 
-- 查看统计的时长
 
show variables like 'long_query_time';
 
 
 
--修改统计的时长为一秒。 请注意，修改之后，当前会话无效，重新连接之后可以看到修改效果
 
set global long_query_time=1;

2. 选择合适的数据库引擎

-- 查看当前安装的数据库 支持哪些 引擎
 
show engines;
 
 
 
-- 查看当前数据库使用的哪种引擎
 
SHOW VARIABLES LIKE 'storage_engine';

常用的存储引擎：Myisam innodb（目前应该是默认使用 innodb） memory

Myisam 存储引擎：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。
Innodb 存储引擎：InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键
Memory ：MEMORY存储引擎将表中的数据存储到内存中，当我们的数据频繁变化的时候，而且不需要入库，这个时候用memory存储引擎。

3. 使用索引

对于创建多列索引的时候（复合索引），记住，如果不是使用的第一部分就不会使用索引
对于使用like语句的时候没如果查询是‘%aaa’ 不会使用索引，’aaa%’ 会使用索引
当你的sql语句有or的时候，只要是有条件没有使用索引，其他条件即使带索引也不会使用。
如果列类型是字符串，那一定要在条件中讲数据使用引号引起来，否则就不能使用索引
Mysql如果感觉我遍历整个表都比使用索引快，那么它自动就不使用索引了

优化小技巧

1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
3.  应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
4.  应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如
select id from t where num=10 or Name = 'admin'
可以这样查询：
select id from t where num = 10 union all select id from t where Name = 'admin'
5.    select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
调优参数：
6.    back_log：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
7.    wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。
8.    max_user_connection: 最大连接数，默认为0无上限，最好设一个合理上限
9.    thread_concurrency：并发线程数，设为CPU核数的两倍
10.    skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
11.    key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like 'key_read%'，保证key_reads / key_read_requests在0.1%以下最好
12.    innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好
13.    innodb_additional_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小
14.    innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
15.    query_cache_size：缓存MySQL中的ResultSet，也就是一条SQL语执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大.
可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小
16.    read_buffer_size：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
17.    sort_buffer_size：MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
18.    read_rnd_buffer_size：MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
19.    record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值
20.    thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
21.    table_cache：类似于thread_cache_size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM