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 实现一个打印字符串功能
查看函数定义
![](https://i-blog.csdnimg.cn/blog_migrate/9306b42928409d7bae96156cfddbd9f0.webp?x-image-process=image/format,png)
创建有参数的函数 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// #结束
![](https://i-blog.csdnimg.cn/blog_migrate/590e50a87a8b4ed191dcf57efd504170.webp?x-image-process=image/format,png)
触发器 触发器执行的不是由程序调用,也不是手工启动,而是通过事件来触发,激活从而执行 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;
![](https://i-blog.csdnimg.cn/blog_migrate/e86ac7bc21bca505254defcb165a883b.webp?x-image-process=image/format,png)
当我们往info表中插记录时。count表则增加数量
当我们删除记录时。在查看count表则发现会自动减少
查看触发器
![](https://i-blog.csdnimg.cn/blog_migrate/9e4a133c0cfef906ff084a78492494db.webp?x-image-process=image/format,png)
删除触发器 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 直接查看二进制文件内容
![](https://i-blog.csdnimg.cn/blog_migrate/d5c014bb6c2e7d94ebb70c25c22728d8.webp?x-image-process=image/format,png)
当我们往表里插入记录时。二进制文件会记录下来
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 我们可以利用二进制的特性来做备份和还原 为了演示方便,刷新一下日志
在表中插入记录
![](https://i-blog.csdnimg.cn/blog_migrate/c93505c819094392e5cdf9d114e0a614.webp?x-image-process=image/format,png)
模拟数据丢失操作 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 写入计划任务中
![](https://i-blog.csdnimg.cn/blog_migrate/cb63fd5a0235151223716f7fa5dff619.webp?x-image-process=image/format,png)
然后就能每小时执行定期备份了