Mysql优化技巧、存储过程、视图、触发器讲解

Mysql优化技巧、存储过程、视图讲解

一、存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

1. 存储过程特点

有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能
函数的普遍特性：模块化，封装，代码复用
速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

2. 存储过程优缺点

优点

1. 在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。
2. 执行速度快。存储过程经过编译之后会比单独一条一条执行要快。
3. 减少网络传输。存储过程直接就在数据库服务器上跑，所有的数据访问都在服务器内部进行，不需要传输数据到其它终端。

缺点

1. SQL本身是一种结构化查询语言，加上了一些控制（赋值、循环和异常处理等），但不是OO的，本质上还是过程化的，面对复杂的业务逻辑，过程化的处理会很吃力。这一点算致命伤。
2. 不便于调试。
3. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。
4. 无法适应数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

文章末尾：具体讲解了存储过程用法。

二、慢查询

1. 概念

MySQL默认10秒内没有响应SQL结果,则为慢查询，可以去修改MySQL慢查询默认时间

2. 查看慢查询的时间

show variables like 'long_query_time';

  

 

  
1
 

3. 修改慢查询的时间

set long_query_time=1; （重启后失效）

  

 

  
1
 

4. 将慢查询写入日志

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。

log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

log_output：日志存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

5. 开启慢查询日志:

set global slow_query_log=1

  

 

  
1
 

6. 修改my.cnf文件方式:

slow_query_log =1

slow_query_log_file=/tmp/mysql_slow.log

  

 

  
1
  
2
  
3
 

log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。

log_slow_admin_statements:表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志

三、优化和索引

表的设计合理化(符合3NF)
添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
SQL语句优化
分表技术(水平分割、垂直分割)
读写[写: update/delete/add]分离
存储过程 [模块化编程，可以提高速度]
对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
mysql服务器硬件升级
定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

1. 数据库范式

1. 概念
   为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

2. 三大范式

   第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

   第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。

   第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。 没有冗余的数据库设计可以做到。

2. 索引优化

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

索引的分类

1. 主键索引:主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。需要查询索引时可以通过以下语句查询。
2. 全文索引示例：

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
 

错误用法:
select * from articles where body like ‘%mysql%’; 错误用法 索引不会生效

正确用法:

select * from articles where match(title,body) against ( ‘database’)
说明:

在mysql中fulltext 索引只针对 myisam生效
mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
使用方法是 match(字段名…) against(‘关键字’)
全文索引：停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如（a，b，mysql，the）
mysql> select match(title,body) against (‘database’) from articles;（输出的是每行和database的匹配度）

3. 唯一索引：这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)；

创建表的时候指定索引，例如CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) )；

示例:创建表结构

create table ddd(id int primary key auto_increment , name varchar(32) unique);

  

 

  
1
 

注意

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复，但是不能存有重复的空字符串

4. 普通索引:普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

示例:创建表结构

create table ccc( id int unsigned,  name varchar(32));

  

 

  
1
 

索引的代价

占用磁盘空间

对DML(update、delete、insert)语句的效率影响

增删改会对索引影响，因为索引要重新整理。

适合添加索引的列

查询作为查询条件字段应该创建索引
唯一性太差的字段不适合单独创建索引,即使频繁
频繁更新字段，也不要定义索引。
不会出现在where语句的字段不要创建索引
总结:满处一下条件的字段，才应该创建索引

1. 肯定在where条件经常使用
2. 该字段的内容不是唯一的几个值
3. 字段内容不是频繁变化

3. 注意事项

创建一个联合索引

alter table dept add index my_ind (dname,loc); // dname 左边的列,loc就是右边的列

注意:

1. 对于创建的多列索引，如果不是使用第一部分，则不会创建索引。
   explain select * from dept where loc=’aaa’\G
   就不会使用到索引

2. 模糊查询在like前面有百分号开头会失效。

3. 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字

4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.

5. 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

4. 查询所用使用率

show status like ‘handler_read%’;

注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

四、SQL优化技巧

1. 使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，
   在group by 后面增加 order by null 就可以防止排序.

explain select * from emp  group by deptno order by null;

  

 

  
1
 

2. 有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]

select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接，更ok!]

  

 

  
1
  
2
  
3
 

3. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引，应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

  

 

  
1
 

最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用 NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。

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

select id from t where num = 0
4. 减少>=或<= 采用>-1 <+1 方式

五、分表、分库

垂直拆分

垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性
垂直拆分用于分布式场景。

水平拆分

上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分
通俗理解：水平拆分行，行数据拆分到不同表中， 垂直拆分列，表数据拆分到不同表中

使用取摸方式分表

例如有四个表取模3，分别为getid（只有id列，自增），user1，user2，user3，当有新用户注册时，首先在getid表获取自增的id，根据id%3得到的值将信息分入不同的表中。

@Service
public class UserService {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public String regit(String name, String pwd) {
		// 1.先获取到 自定增长ID
		String idInsertSQL = "INSERT INTO uuid VALUES (NULL);";
		jdbcTemplate.update(idInsertSQL);
		Long insertId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		// 2.判断存储表名称
		String tableName = "user" + insertId % 3;
		// 3.注册数据
		String insertUserSql = "INSERT INTO " + tableName + " VALUES ('" + insertId + "','" + name + "','" + pwd
				+ "');";
		System.out.println("insertUserSql:" + insertUserSql);
		jdbcTemplate.update(insertUserSql);
		return "success";
	}

	public String get(Long id) {
		String tableName = "user" + id % 3;
		String sql = "select name from " + tableName + "  where id="+id;
		System.out.println("SQL:" + sql);
		String name = jdbcTemplate.queryForObject(sql, String.class);
		return name;
	}

}

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
 

六、读写分离

通过配置主从关系，一般主数据库负责写，从数据库负责读，从而实现读写分离，避免IO阻塞。
主从配置参考下面我的博客：

https://blog.csdn.net/qq_43692950/article/details/107731431

七、视图详细用法

视图是一个虚拟表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据，但视图并不是数据库真实存储的数据表。
视图是从一个、多个表或者视图中导出的表，包含一系列带有名称的数据列和若干条数据行。
视图并不同于数据表，它们的区别在于以下几点：

1. 视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
2. 存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
3. 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
4. 视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
5. 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
6. 视图的建立和删除只影响视图本身，不影响对应的基本表。

使用视图的时候，还应该注意以下几点：

1. 创建视图需要足够的访问权限。
2. 创建视图的数目没有限制。
3. 视图可以嵌套，即从其他视图中检索数据的查询来创建视图。
4. 视图不能索引，也不能有关联的触发器、默认值或规则。
5. 视图可以和表一起使用。
6. 视图不包含数据，所以每次使用视图时，都必须执行查询中所需的任何一个检索操作。如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图，可能会发现系统运行性能下降得十分严重。因此，在部署大量视图应用时，应该进行系统测试。

创建视图

create VIEW test_view as 
select * from user;

  

 

  
1
  
2
 

使用视图

select * from test_view ;

  

 

  
1
 

删除视图

drop view if exists test_view ;

  

 

  
1
 

八、存储过程详细用法

1. 基本使用

创建无参存储过程

create procedure userPorced ()
begin
select name from users;
end;

  

 

  
1
  
2
  
3
  
4
 

调用存储过程

call  userPorced ();

  

 

  
1
 

创建有参存储过程

create procedure userPorced ( in a int(10) )
begin
select name from users where age>a;
end;

  

 

  
1
  
2
  
3
  
4
 

调用存储过程

call  userPorced (10);

  

 

  
1
 

2. 参数

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

IN输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT输出参数:该值可在存储过程内部被改变，并可返回

INOUT输入输出参数:调用时指定，并且可被改变和返回

示例：

DROP procedure if EXISTS userPorced;

create procedure userPorced (INOUT p_id int)
begin
select * from user where id = p_id;
end;

set @p_test = 5;

CALL userPorced(@p_test);

SELECT @p_test;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
 

3. 创建变量

create procedure userPorced (INOUT p_id int)
begin
-- 定义变量
DECLARE a int DEFAULT 0;
declare x1 varchar(5) default 'aa';  
-- 赋值
SET a = 2;
SET p_id = a;
select * from user where id = p_id;
end;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

4. 判断

create procedure userPorced (in p_id int)
begin

IF p_id = 0 THEN 
select 'a';
ELSEIF p_id = 1 THEN 
select 'b';
 ELSE 
select 'c';
END IF;

end;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
 

5. case

create procedure userPorced (in p_id int)
begin

case p_id 
when 0 THEN 
select 'a';
when 1 THEN 
select 'b';
 ELSE 
select 'c';
end case;

end;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
 

6. while

create procedure userPorced (in p_id int)
begin

declare var int;
set var = 0;
while var < p_id do 
	set var = var+1;
end while;

select var;

end;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
 

7. 动态SQL

create procedure userPorced (in f_id varchar(50),in f_name varchar(50))
begin

SELECT * FROM user WHERE 1=1 
AND (f_id IS NULL OR `id` = f_id) 
AND (f_name IS NULL OR `name` = f_name);

end;

CALL userPorced(4,'abc');

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

九、触发器

触发器（TRIGGER）是MySQL的数据库对象之一，从5.0.2版本开始支持。该对象与编程语言中的函数非常类似，都需要声明、执行等。但是触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行。
创建触发器：

CREATE TRIGGER trname -- 触发器的名称
BEFORE -- 事件的触发点（ { BEFORE | AFTER } 表示在事件之前或之后触发）  
INSERT --- { INSERT |UPDATE | DELETE }，触发该触发器的具体事件
ON test --- 作用在那张表上
FOR EACH ROW  
BEGIN 
	--- 触发后的操作
	UPDATE test1 SET test1 =test1 +1;
END

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
 

CREATE TRIGGER t1 
BEFORE INSERT ON test FOR EACH ROW 
BEGIN 
	UPDATE test1 SET test1 =test1 +1;
END

  

 

  
1
  
2
  
3
  
4
  
5
 

查看所有触发器：

SHOW TRIGGERS

  

 

  
1
 

删除触发器：

DROP TRIGGER t1;

  

 

  
1
 

文章来源: blog.csdn.net，作者：小毕超，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/qq_43692950/article/details/107444276