第13周作业

1、描述视图,存储过程,函数,触发器分别是什么并实践

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。表现为对实体表的查询结果，查完之后存储在视图。方便下次查询
　　通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。
创建视图 
create view v_students as select stuid,name,age from students;

创建视图，将查询结果存储成视图。可方便查询

show table status like "v_students" \G
查看表状态。comment表示了是view表。表示为视图

存储过程（procedure）类似shell中的函数。在函数体内部可以放一系列命令。然后通过存储过程调用，


delimiter // #临时更改命令执行的符号

create procedure showtime()  #创建存储过程

begin select now(); #函数体定义

end// #结尾

call showtime; #调用存储过程执行


MariaDB [hellodb]> delimiter //

MariaDB [hellodb]> CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED) #创建存储过程。并且定义参数

    -> BEGIN #存储过程内容

    -> SELECT * FROM students WHERE stuid = uid; #查询表并且赋值给形参uid

    -> END// #结束

MariaDB [hellodb]> delimiter ; 修改执行符号


delimiter //

CREATE PROCEDURE dorepeat(n INT) #定义存储过程，和形式参数n，并定义类型

BEGIN 

SET @i = 0; #设置会话变量。变量不会随着存储过程结束而消息，会在会话中一直生效

SET @sum = 0; #同上

REPEAT SET @sum = @sum+@i; SET @i = @i + 1;#重复执行 

UNTIL @i > n END REPEAT;#限制。如果变量i大于传入的参数时则循环结束

END//

delimiter ;

CALL dorepeat(100);#调用存储过程，将形100赋值给形参n

SELECT @sum; #查询结果

函数
create function hello() returns varchar(20) return "hello world!";

创建一个无参数的函数。返回字符串。具体返回为 hello world 实现一个打印字符串功能



查看函数定义

创建有参数的函数

MariaDB [hellodb]> delimiter // #将命令执行；号变成 //方便区分。在定义函数内部要执行的命令时。如果出现；号将不会执行

MariaDB [hellodb]> create function del(uid int unsigned) returns varchar(20) #创建函数。添加一个个uid参数，并指定类型为数字。返回结果为字符串

    -> begin # 函数的开始

    -> delete from students where stuid = uid ; 删除一条记录。这条记录为你输入的数字

    -> return (select count(stuid) from students);返回一个查询结果

    -> end//

delimiter


select del（25）#调用函数并传递25位参数


[hellodb]> CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) #定义两个行参的函数。类型为数字。

    -> RETURNS SMALLINT #返回结果为数字

    -> BEGIN #开始

    -> DECLARE a, b SMALLINT UNSIGNED; 声明为局部变量。a和b。只在函数内部生效。函数执行完毕后失效

    -> SET a = x, b = y; #将本地变量赋值为形参

    -> RETURN a+b; #返回结果

    -> END// #结束

触发器

触发器执行的不是由程序调用，也不是手工启动，而是通过事件来触发，激活从而执行

CREATE TABLE student_info (

stu_id INT(11) NOT NULL AUTO_INCREMENT,

stu_name VARCHAR(255) DEFAULT NULL,

PRIMARY KEY (stu_id)

);

CREATE TABLE student_count (

student_count INT(11) DEFAULT 0

);

insert into student_count values(0);

创建两张表。实现在info中增加学生时。count数量加1，减少info记录时，则count则减少数量

创建触发器(增加触发）

CREATE TRIGGER trigger_student_count_insert #创建触发器

AFTER INSERT  #表示在insert命令之后触发，触发器执行 如果是before则在触发器之前执行。表示代替后面的动作

ON student_info FOR EACH ROW #表示对这个表改了多少行就触发几次触发器

UPDATE student_count SET student_count=student_count+1; #更新count表数量+1



删除触发器

CREATE TRIGGER trigger_student_count_delete 

AFTER DELETE

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count-1;

当我们往info表中插记录时。count表则增加数量


当我们删除记录时。在查看count表则发现会自动减少


查看触发器

 

删除触发器 drop trigger name；如果删除表。则在表中的触发器则也会删除。

 

2、描述MySQL中有哪些存储引擎并描述各自的区别

存储引擎是MySQL组件，用于处理不同表类型的SQL操作。MySQL存储引擎既包括处理事务安全表的表，也包括处理非事务安全表的表。InnoDB是MySQL 5.5.5的默认存储引
擎（CREATE TABLEMySQL 5.5中的 语句InnoDB默认创建表

在mysql5.1中默认为myisam存储引擎。现在的新版本都已经默认为innodb存储引擎

show engines;查看mysql中的各种存储引擎



常见存储引擎对比图



MyISAM引擎特点

不支持事务

表级锁定不支持行级锁

读写相互阻塞，写入不能读，读时不能写

只缓存索引

不支持外键约束

不支持聚簇索引

读取数据较快，占用资源较少

不支持MVCC（多版本并发控制机制）高并发

崩溃恢复性较差

MySQL5.5.5前默认的数据库引擎



InnoDB引擎特点

行级锁

支持事务，适合处理大量短期事务

读写阻塞与事务隔离级别相关

可缓存数据和索引

支持聚簇索引

崩溃恢复性更好

支持MVCC高并发

从MySQL5.5后支持全文索引

从MySQL5.5.5开始为默认的数据库引擎

Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎

MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库

Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区

Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单
独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集环境

BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性

Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性

CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换

BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储

example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

3、描述索引的工作原理并创建各种索引

 

一、概述

在mysql中，索引（index）又叫键（key），它是存储引擎用于快速找到所需记录的一种数据结构。在越来越大的表中，索引是对查询性能优化最有效的手段，索引对性能影响非常
关键。另外，mysql的索引是在存储引擎层实现，而不是在服务器层。

二、索引的工作原理

我们知道，在看一本书某章的时候，首先我们会查找目录索引，找到对应的页码然后快速找到相应的内容。mysql索引也一样，存储引擎利用类似的方法使用索引，先在索引中找到
对应的值，然后根据匹配的索引记录找到对应的数据行，然后返回结果。

例如，我们想在一个10W条记录表 table 中查询name等于“张三”的数据行，select * from table where name ='张三'。那么在没有对name字段建立索引的情况下，我们
需要扫描全表也就是扫描10W条数据来找到这条数据；如果我们为name字段建立索引，我们只需要查找索引，然后根据索引找到对应的数据行，只需要查找一条记录，性能会得到很
大的提高。

三、索引分类

索引按照实现方式不同可以分为 B-Tree索引、hash索引、空间数据索引以及全文索引等。如果没有特别指明，多半用的是B-Tree索引，B-Tree 对索引列是顺序存储的，因此很
适合查找范围数据。它能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。

四、索引类型

索引主要分为：单列索引（普通索引、主键索引、唯一索引）和组合索引。

普通索引：

CREATE INDEX name_Index ON `table`(`name`);

ALTER TABLE table ADD INDEX name_Index(`name`)
唯一索引：

CREATE UNIQUE INDEX id_UNIQUE_Index ON `table`(`id`);
主键索引：主键索引和唯一索引类似，唯一索引允许有空值，而主键索引不允许。

组合索引：通俗的说，组合索引就是一个表中一个索引包括多个字段，一个表中多个单列索引并不是组合索引。

例如：

1
CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);
五、组合索引的查询规则（什么情况下有效，什么情况下无效）

B-Tree 索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀查找。我们建立表user（id，last_name,first_name, age ,birthday,sex）,
建立组合索引 key（last_name, first_name, birthday）,那么它实际上包括三个索引（last_name），（last_name，first_name），
（last_name，first_name，birthday）。

下面我们来分析组合索引有效以及无效的情况，mysql在使用组合索引查询的时候需要遵循“最左前缀”规则，什么是“最左前缀”规则呢，就是在使用组合索引查询，where的条件要
按照从左到右的顺序，last_name first_name birthday，可以是只有last_name，或者包括last_name、first_name，或者last_name、first_name、birthday，这
个从左到右的顺序不能变，也不能跳过；如果是直接first_name='ruby' 组合索引不生效，或者跳过first_name，last_name=‘allon’ and birthday = ‘2012’，组合
索引只有last_name生效，后面的所有不生效。

例如：1、全键值匹配：select * from user where last_name=‘allon’ and first_name='java' and birthday=‘2017-12'是生效的，
如果 select * from user where first_name='java' and birthday=‘2017-12'组合索引是不生效的，因为没有key(first_name,birthday)的索引。

2、键前缀查找：select * from user where last_name=‘allon’；这个索引存在，也是有效的，但不能select * from user where first_name='java'，这样不生效。

3、like模糊查询：比如只匹配组合索引第一列的值的开头部分，查询last_name姓张的人，select * from user where last_name like ‘张%’；
但是不能select * from user where last_name like ‘%张’；组合索引也无法查找以张结尾的人。 
再如 select * from user where last_name='allon' and first_name like '三%' and birthday = '2012-11-06'，
因为first_name用了like这个范围查询条件，那么查询只用到了组合索引的前两列，范围查询右面的列birthday无法用索引优化查询。

如果查询中有某个列的范围查询，则该列右边的所有列都无法使用索引优化查找

4、匹配范围值：select * from user where last_name between ’allon‘ and 'clitton';这里只使用了组合索引的第一列，是生效的。

六、索引的优缺点

优点：1、建立索引后，在查询的时候合理利用索引能够提高数据库性能；

2、主键索引 唯一索引能保证表中每一条数据的唯一性

3、减少分组和排序的时间

4、在表连接的连接条件上使用索引，可以加速表与表之间的相连。

缺点：1、创建索引和维护索引需要时间消耗；

2、索引文件占用物理空间

3、当对表的数据进行insert update delete时候需要维护索引，会降低数据的维护数据。

4、总结binlog备份方法，用脚本实现每小时备份binlog

要启用binlog，首先需要开启二进制日志。有两个选项和二进制日志文件相关

sql_log_bin=ON|OFF：是否记录二进制日志，默认ON

log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可

binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT 建议改成行级别row

在配置文件 /etc/my.cnf 中加入 log_bin后面如果不加路径默认和数据库路径放在一起，名字会使用默认系统的名字

利用mysqlbinlog管理和备份还原




查看二进制日志文件


查看当前正在使用的二进制日志文件


mysqlbinglog工具使用

mysqlbinlog /var/lib/mysqlcentos7-bin.000001

直接查看二进制文件内容

当我们往表里插入记录时。二进制文件会记录下来


mysqlbinlog centos7-bin.000001 -v 利用-v选项查看详细记录，会发现我们做的操作都会记录在二进制日志中


mysqlbinlog /var/lib/mysql/centos7-bin.000001--start-position=507 --stop-position=564 可以利用位置查看二进制内容。 在日志内at 后面的数字表示为位置

也可以利用时间挑出一部分二进制日志的内容

mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" centos-bin.000003 -vvv


我们可以利用二进制的特性来做备份和还原

为了演示方便，刷新一下日志


在表中插入记录

模拟数据丢失操作
delete from teachers;


将二进制文件导出

mysqlbinlog /var/lib/mysql/centos7-bin.000003 -v > /root/mylog.sql

在导出的sql文件中，找到最后执行的delete语句，将delete语句删除之后，


mysql  < mylog.sql 导入日志。还原

建议二进制日志需要配合完全备份来使用，如果drop 表，二进制文件如果没记录表结构，则还原会失败



编写脚本执行每小时执行备份

#!/bin/bash

for i in `ls /var/lib/mysql/ | grep centos.*[0-9]$`;do

        mysqlbinlog /var/lib/mysql/$i -v > /root/${i}.sql`date +"%F-%T`

done



写入计划任务中

然后就能每小时执行定期备份了