挑战三天刷完-MySQL数据库优化（第二天）

存储过程与函数

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

 

 存储过程和函数的区别就是有没有返回值，函数有，存储过程没有

创建、调用、查看、删除存储过程

创建

 

 

 

 必须得更换分隔符，用delimiter 更换。存储过程创建时没有参数也得加（），像函数一样。

调用：

 

 查看：

 

 删除

 语法：

变量：

declare声明变量

 

 可以用set为变量直接赋值，也可以select ... into赋值

 

 

 

 if条件判断：

语法结构

 

示例：

 

 

 传递参数：

类似函数的参数

 

 IN输入：

OUT输出：

 

 @代表的是调用用户变量

case结构：

 

 方式一是根据值，方式二是根据条件。

while循环

 

 

 也是在procedure里

repeat结构

 

 

 相当于do..while；until后面没有分号。

loop语句、leave语句

 

 

 

 

如何不加退出循环的语句，会成死循环。可以用leave退出循环。

例子：

 

 

 游标/光标：

 

 

 

 也是在存储过程中使用

fetch光标是一行一行地抓取，使用一次抓取一行，平常是只能读取全部或条件读取，fetch光标可以帮助读取第几行的信息，用几次就相当于第几行

 

 

 用循环获取游标时，可以设置如下，通过has_data的值来跳出循环

DECLARE has_data int default 1;

DECLARE emp_result CURSOR FOR select * from emp;（游标声明）

DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;（必须在游标声明下边，抓取不到数据时执行赋值）

存储函数（有返回值）

语法结构

 

 存储过程要看返回的话要不采用select语句写在过程里面，要不用一个out存储结果；存储函数是直接返回不需要用out变量。

都需要则行call “存储名”,之后若有out变量或者函数返回值，用select查看。

触发器

就是指在 每次插入、更新、删除操作，操作完成之前或者之后执行触发器，只要有这些操作就会执行；

下面的OLD、NEW则是在触发器语句中使用，它可以拿到操作时想要的数据。比如在insert操作时，insert触发器中要想使用insert新增的数据,可以用now.字段去表示。

 

 创建触发器

可以用自定义日志表来记录触发器操作，日志表的数据插入操作在触发器操作（begin 和end）中。

触发器用INSERT型触发器、UPDATE触发器、DELETE触发器

 

 

 查看及删除触发器

查看：

 

 

删除：

 MySQL的体系结构

 

 

 分为红圈4层：

 

第一层：连接层，接受客户端请求，开启线程进行相关认证和授权操作

第二层：服务层，主要起到备份、恢复、SQL语句的封装、解析、优化以及缓存

第三层：引擎层，存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。

第四层：存储层，数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

Connection Pool : 连接池组件

Management Services & Utilities : 管理服务和工具组件

SQL Interface : SQL接口组件

Parser : 查询分析器组件

Optimizer : 优化器组件

Caches & Buffers : 缓冲池组件

Pluggable Storage Engines : 存储引擎

File System : 文件系统

存储引擎：

默认是InnoDB,高版本是MyISAM.

用show engines可以查看支持的存储引擎。

常见的存储引擎的特性：

 

 InnoDB存储引擎：

 事务控制：

 

 commit未执行，insert未成功。

外键约束（InnoDB是存储引擎中唯一一个支持外键约束的）：

 

设置外键约束后，主表更新，外键关联的表数据也会更新。主表数据要是被关联了，则不能删除主表数据。

 存储方式：

 

 

表结构在.frm文件中，数据在.ibd文件中。

MyISAM存储引擎：

不支持事务，不支持外键。适用于对事务的完整性不做要求，对少量数据丢失可以容忍。优点是访问速度快。

Memory存储引擎：

将表数据放到内存中，不放到.ibd文件中，访问速率高，但CPU内存资源宝贵，不适合用改存储大数量数据。断电会导致数据丢失。

MERGE存储引擎：

 

 

将表结构和字段一样的MyISAM表合在一张Meger表中，查询时只需查找Meger表，Meger表就会在MyISAM基表中查找数据。

通过INSERT_METHOD子句插入，用first插入第一张表，last插入最后一张表，NO不插入表。

在建表时定义INSERT_METHOD

 

存储引擎的选择：

 

 InnoDB:默认存储引擎，对安全性要求高、数据一致性要求高、对完整性要求高。(最常用）

MyISAM:对安全性要求不高、数据一致性要求不高，对查询、插入速度快，更新和删除少。

优化SQL优化步骤（测试和定位）：

查看SQL执行频率

 

 

 

 

 

 定位低效率执行SQL

 

 

 

 用show processlist查看当前是否某些线程还在运行，这些线程的SQL语句即为低效率SQL

explain分析执行计划

 

 

 

explain之id

有以下3种情况，子查询先查询，先查询的表优先级高，id越大。

1） id 相同表示加载表的顺序是从上到下。

2） id 不同id值越大，优先级越高，越先被执行。

3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越 大，优先级越高，越先执行。

explain之select_type

 

查询速度从上往下越来越低。

explain 之 table

 展示这一行的数据是关于哪一张表的

explain 之 type

 

eq_ref和ref是有where条件的，对应索引值必须设置。range则是设置区间查询，all则是select * ，index一个是select (索引字段)，没有where条件。

explain 之 key

 

 

explain 之 rows

 扫描行的数量。

explain 之 extra

 

前面两个效率不行，最后一个效率好。

show profile分析SQL

select @@have_profiling;查看当前系统是否支持profile;

select @@profiling 查看当前系统是否开启profile;

set profiling=1; //开启profiling 开关；

执行语句之后，通过show profiles来查看每条语句的耗时；

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

trace分析优化器执行计划

set optimizer_trace="enabled=on",end_markers_in_json=on;开启优化器，设置格式为json

set optimizer_trace_max_mem_size=1000000;设置trace最大内存

 

 

索引的使用

避免索引失效

1). 全值匹配 ，对索引中所有列都指定具体值。（主要是复合索引）

该情况下，索引生效，执行效率高。

2). 最左前缀法则 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。（复合索引）

3). 范围查询右边的列，不能使用索引 。（就是where使用了>，<，in的字段，后面的字段都不能使用索引了,前面的可以）

4). 不要在索引列上进行运算操作， 索引将失效。

5). 字符串不加单引号，造成索引失效。

6). 尽量使用覆盖索引，避免select *（覆盖索引指查询时访问不是全部字段，而是索引中的字段）一般都是{using index;using where;和using index condition)

 

 

 

 

 

 

 

7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。and则会索引。

8). 以%开头的Like模糊查询，索引失效。（用覆盖索引可以解决索引失效）

9). 如果MySQL评估使用索引比全表更慢，则不使用索引。（跟数据有关系）

10). is NULL ， is NOT NULL 有时索引失效。（用索引比全表慢，所以失效了）

11). in 走索引， not in 索引失效。

12). 单列索引和复合索引。（尽量使用复合索引，少使用单列索引；一是多字段单列索引只会使用一个索引；二是复合索引覆盖范围广）

查看索引使用情况

show status like 'Handler_read%';会话的索引

show global status like 'Handler_read%';全局的索引

 

------------------------------------------------------------------------------

内容有：存储过程与函数、触发器、MySQL体系结构、优化SQL的步骤（感觉更像测试和定位）、索引的使用

----------------------------------------------------------------------------------

图片是课件，打字大部分是自己的思考，若有错误请指出。

今天学的进度也是挺慢的，早上没起来，下午有课，导致和昨天学习的时间差不多，明天事情也更多，反正肯定说好3天就3天，第一次尝试就鸽了那会打击积极性的。想把博客做成笔记一类，还可以吐槽的那种，但这格式看着找起来有点难受，等有空找个样式改下。