Linux的mysql深入学习笔记
1、描述视图,存储过程,函数,触发器分别是什么并实践
一、视图
1.视图的定义
视图是一种虚拟存在的表，它本身在数据库中并不存在，而是在我们出于某种目的想要去使用视图的时候，使用我们数据库中已经存在的表的行和列数据来自定义的一种动态生成的，便于检索的安全的数据独立的一种虚拟表。
2.优点
①简单。视图是一种已经经过过滤筛选之后的符合条件的结果集，因此，使用视图的用户不需要关心对应表的表结构、关联条件和筛选条件。
②安全。使用视图的用户只能对视图相关的表的列进行查询，并不能去具体的操作表和列的权限。
③数据独立。一旦视图确定之后，它的数据就是独立的，也就是说，源表的增加一个字段对视图没有任何影响，源表更改和删除视图中存在的字段的时候，可以通过修改视图来解决，不会对访问者造成影响。
3.缺点
①性能差
数据库r必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。因此，多表查询使用较多的不建议使用视图。
②修改限制
当用户视图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的试图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。因此，视图的使用一般只用来检索数据，很少用它来修改或删除数据。
二、存储过程和函数
1.定义
存储过程和函数都是事先经过编译并且存储在数据库中的一段SQL语句集合，存储过程和函数的 执行不是由系统执行也不是手动执行的，而是由事件触发、激活从而事先执行的。
2,.存储过程和函数的区别
①函数必须有返回值，且返回的只能是值或者表对象，而存储过程可以有返回值，也可以没有，返回值可以返回一个结果集，也就是多个值。
②存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能是IN类型的，且存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。
③一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。因为存储函数可 以包含一组操作，而函数只能包含一个操作。
④存储过程中可以使用非确定函数，而在用户自定义函数的主体中是不允许使用非确定函数的。
⑤ SQL语句中不可用存储过程，而可以使用函数。存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。
3.存储过程和函数的优缺点
优点：
①存储过程和函数的优势是可以把对数据的处理放在数据库服务器上进行，从而避免大量的结果集传输到客户端上，从而减少数据的传输，从而减少网络流量，执行速度较快。
②存储过程和函数可以被作为一种安全机制来利用。
③存储过程和函数允许标准组件式编程，提高了SQL语句的重用性、共享性和可移植性。
缺点：
①在数据库服务器上进行进行运算也会占用数据库服务器的CPU，造成数据库服务器压力，因此，不要在数据库服务器上进行大量的存储过程和函数的运算，要分摊到应用服务器上运行。
②存储过程和函数的编写比单句SQL语句复杂。
③在编写存储过程和函数时，需要创建这些数据库对象的权限。
三、触发器
1.定义
触发器是与表有关的数据库对象在满足定义条件时触发，并执行触发器中定义的语句的集合。它的触发是事件来触发的也不是系统自动执行或手动调用执行，这一点个存储过程一样，因此我们可以把触发器看成一种特殊的不存在任何输入输出的存储过程。他可以协助在数据库客户端保证数据的完整性。
2.触发器的优缺点
触发器的优点: 以事件方式来处理. 当数据发生变化的时候, 自动作处理.
缺点: 触发器是行触发的，因此每次对数据的增删更新操作都会触发进行处理，编写过于复杂的触发器会对记录的增删更新性能产生很大的影响，因此不要讲过多的应用逻辑交给触发器来处理。
3.触发器使用时的注意事项
①不能使用讲数据返回到客户端的存储过程和函数，但是可以使用返回值类型为OUT或INOUT的存储过程和函数。
②不能在触发器中使用以显式或者隐式的方式开始或者结束事务的语句，如START TRANSACTION、COMMIT、ROLLBACK。
③MySQL的触发器的执行顺序是按照BEFORE触发器、行操作、AFTER触发器的顺序来执行的，任何一部出错都不会执行下面的操作，如果是对事务表进行的操作，失败将会回滚，如果是对非事务的操作，已经更新的数据将无法进行回滚。
2、描述MySQL中有哪些存储引擎并描述各自的区别
1.InnoDB 引擎(MySQL5.5以后默认使用)
MySQL 5.5 及以后版本中的默认存储引擎，他的优点：
灾难恢复性好
支持事务
使用行级锁
支持外键关联
支持热备份
对于InnoDB引擎中的表，其数据的物理组织形式是簇表（Cluster Table），主键索引和数据是在一起的，数据按主键的顺序物理分布
实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
支持热备份
2.MyISAM引擎
特性：
不支持事务
使用表级锁，并发性差
主机宕机后，MyISAM表易损坏，灾难恢复性不佳
可以配合锁，实现操作系统下的复制备份、迁移
只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
3.MEMORY 存储引擎
特性：
提供内存表，也不支持事务和外键。显著提高访问数据的速度，可用于缓存会频繁访问的、可以重构的数据、计算结果、统计值、中间结果。
缺点：
使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈
只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪费空间
不支持TEXT、BLOB字段。当有些查询需要使用到临时表（使用的也是MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁盘的MyISAM表，严重降低性能
由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错
服务器重启后数据会丢失，复制维护时需要小心
MySQL存储引擎MyISAM与InnoDB两种存储引擎的大致区别表现在：
1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
3）InnoDB支持外键，MyISAM不支持
4）从MySQL5.5.5以后，InnoDB是默认引擎
5）InnoDB不支持FULLTEXT类型的索引
6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表。
7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。
8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'
3、描述索引的工作原理并创建各种索引
什么是索引
在关系数据库中，索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
创建索引可以大大提高系统的性能。
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。
唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。
在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
聚集索引在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。
如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。
4、总结binlog备份方法，用脚本实现每小时备份binlog
一、binlog日志介绍
1）什么是binlog
binlog日志用于记录所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。
2）binlog作用
因为有了数据更新的binlog，所以可以用于实时备份，与master/slave主从复制结合。
3）和binlog有关参数
log_bin 设置此参数表示启用binlog功能，并指定路径名称
log_bin_index 设置此参数是指定二进制索引文件的路径与名称
binlog_do_db 此参数表示只记录指定数据库的二进制日志
binlog_ignore_db 此参数表示不记录指定的数据库的二进制日志
max_binlog_cache_size 此参数表示binlog使用的内存最大的尺寸
binlog_cache_size 此参数表示binlog使用的内存大小，可以通过状态变量
binlog_cache_use和binlog_cache_disk_use来帮助测试。
binlog_cache_use：使用二进制日志缓存的事务数量
binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
max_binlog_size Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束
sync_binlog 这个参数直接影响mysql的性能和完整性
4）binlog的删除
binlog的删除可以手工删除或自动删除：
a）自动删除binlog
通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog
mysql> show binary logs;
mysql> show variables like 'expire_logs_days'; //该参数表示binlog日志自动删除/过期的天数，默认值为0，表示不自动删除
mysql> set global expire_logs_days=3; //表示日志保留3天，3天后就自动过期。
b）手工删除binlog
mysql> reset master; //删除master的binlog，即手动删除所有的binlog日志
mysql> reset slave; //删除slave的中继日志
mysql> purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002'; //删除指定日志文件的日志索引中binlog日志文件
mysql> set sql_log_bin=1/0; //如果用户有super权限，可以启用或禁用当前会话的binlog记录
mysql> show master logs; //查看master的binlog日志列表
mysql> show binary logs; //查看master的binlog日志文件大小
mysql> show master status; //用于提供master二进制日志文件的状态信息
mysql> show slave hosts; //显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中
mysql> flush logs; //产生一个新的binlog日志文件
二、脚本实现每小时备份binlog
#!/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
crontab -e
* */1 * * * root /binlogbak.sh
