MySQL 学习笔记7 事务 备份与恢复 索引 存储过程
1. 关于事务的存储引擎
数据库对同样的数据，有着不同的存储方式和管理方式， 在mysql中，称为存储引擎。
2. 常见的事务引擎介绍 Myisam(张马虎)  InnoDB(李小心)
arrow 艾睿电子
区别比较：
特点              Myisam          InnoDB
批量插入的速度    高          低
事务安全                  支持
全文索引      　支持
锁机制        表锁        　行锁
3. 事务
c语言中运算符号的优先顺序
通俗的说事务：  就是指一组操作，
java随机函数random事务应符合下列特性：
a. 要么都成功执行， 要么都不执行。 --> 原子性 Atomicity
b. 在所有的操作没有执行完毕之前，其他会话不能够看到中间改变的过程。 --> 隔离性  Isolation
c. 事务发生前和发生后， 根据数据的规则，总额应该匹配 --> 一致性  -> consisstency
d. 如果事务出了错误，已执行了的事务不允许撤销，只能通过补偿性事务来修正。  --> 持久性  durability
综合为 ACID
4. 事务语法：
开启事务：  start transaction;
sql 事务操作
....
提交事务：  commit 提交/ rollback 回滚(如果事务操作在commit之前发现有错误，表示将rollback之前的事务语句效果都取消，返回到开启事务之前的状态。一旦commit结束了，就不能用rollback回滚到先前状态了);（注意： 当一个事务commit或者rollback之后，事务就结束了，如果需要再次操作，需要用start transaction; 再次开启事务） 在commit之前， 前面的sql事务结果并不显示。
mysql里有些隐私提交语句，对这些语句不需要commit就会直接提交： 比如 alter function, alter procedure, alter table, begin, create database, create function, create index ， start transaction（造成前一个事务的提交）等。
5. 用了事务之后， 将start transaction之后的事务语句每一句sql的效果放在事务日志文件中，最后当我们commit之后，会将事务日志文件中的sql 语句集中的作用到表数据文件上。 如果是rollback 这将事务日志表标示为 未提交，但并未删除事务日志表。而不用事务时，对表的操作是直接作用于表的，所以sql语句的效果会马上显现。
6. 备份与恢复
a. 增量备份与整体备份： 
cv2imread函数例： 每周日整体备份一次(整体备份)， 周一至周六 只备份当天的(增量备份)
如果周五出了问题， 可以用周日的整体备份 + 周一，周二，周三，周四的 增量备份来恢复。
b. 备份工具:
由第三方的收费备份工具。也有系统自带的简单的备份工具。
mysql/ 该
工具用于导出数据。 可以导出库， 也可以导出表。
I.导出备份表的语句：  mysqldump -u用户名 -p密码 数据库名 表1名 表2名 表N名 > 导出目录\\导出文件名
mysqldump -uroot -p mugua account > d:\\account.sql  导出完毕后可以到d盘目录下查看导出文件。导出命令的末尾不用加；号。然后windows下路径要用\\表示。 而linux则用/表示路径。
导出的即为需要备份的
不同数据库的区别： sql server导出是二进制文件， mysql导出的是 建表语句与insert语句。
II. 导出数据库中所有表的语句: mysqldump -u用户名 -p密码 数据库名 > 导出目录\\导出文件名  #即忽
略表名时，就会把该库下的所有表都导出。
III. 如何导出一个库：(区别在于II少了关于库的操作, 包含 use 数据库名 这样的命令。)： mysqldump -u用户名 -p密码 -B 数据库名1  数据库名2 数据库名N> 导出目录\\导出文件，名 加了关键字 -B 后就说明之后的所有名 均为数据库名,  而非  数据库名 表名
IV.  如何导出所有库: mysqldump -u用户名 -p密码 -A > 导出目录\\导出文件名  # -A 表示导出所有数据库
c. 恢复， 导入并批量执行备份文件
I. 对于库级的备份恢复: 在Mysql的console下 输入：    Mysql>source D:\\备份文件名;    #回车后会以该备份文件为源，立刻执行所有语句, 注, 不要在source后输入 < 符, 这个是不需要的，否则报错,  例如  source d:\\account.sql; 
II. 如果需要恢复表级的备份：比I多一步进入所要恢复表的库: use 库名mysql查看所有存储过程
Mysql>use 库名;
Mysql>source 备份文件地址;
III. 在不登录到mysql命令行时的恢复操作方法：
在dos命令行，而非mysql>状态下输入：
1). 导入数据库备份： mysql -uroot -ppsi <；库级备份文件路径:/备份文件名  # 不用加分号; 导入数据库, 注意 用了 <；就不用加 source关键字了
2). 导入表备份：  mysql -uroot -ppsi 库名<；表级备份文件路径://备份文件名
7. 索引: 好处是加快了查询速度， 坏处是降低了增删改的速度， 同时也增大了表的文件大小(索引文件甚至会比原表原库文件更大)
a. 一般介绍
•二叉树索引 log2N 和 哈希索引  理论上 1
•索引与优化： 不过度索引；
•索引条件列(where后面最频繁的条件比较适宜用索引)
•索引hash值，过于集中的值不要索引    例如： 给  男 女 加索引，就没有什么意义， 因为如果有300
万行值，对性别索引，其结果只有 男女 两种，最后索引的结果一样，如果300万行值，其有一列的键值几乎都不相同，那么哈希后得到的索引也可能非常分散，那样用哈希索引比较有利。
b. 一些相关文件类型：. frm 是表的
结构文件，对表进行说明，有哪几列，每列什么类型;
          .MYD 是表的数据文件，对表内数据进行说明;
          .MYI 是表文件的索引文件，比如.MYD才1kb, 而.MYI 2kb很正常。
c. 索引的具体类型
• 普通索引 index： 仅仅是加快查询速度
• 唯一索引 primary key: 不仅加快查询速度，行上的值不能重复。 主键索引必定是唯一索引，唯一索引未必是主键索引。
• 主键索引 unique index：不能重复，与唯一索引的区别：主键索引必须唯一，不可能重复；但唯一索引不一定是主键，一张表上只能有一个主键，但是可以有一个或多个唯一索引。
免费教育视频下载• 全文索引：
d. 查看表上的所有索引:  show index from 表名;  #BTREE 二叉树索引 
          　show index from 表名 \G  #表示将索引按层次化显示出来， 更清楚的结构 按 1.row  2.w来显示所有索引类型。(不需要加; 在查询末尾)  例如对于 goods表， 查看\data\shop目录下的 goods.MYI 就是其索引文件。
e. 建立索引命令：  alter table 表名 add index/unique/fulltext [索引名] (列名);  # 索引名是可选的，如果不指定，则索引名默认以列名作为其索引名。
举例：
alter table member add index tel(tel) \G
alter table member add unique (email) \G  #省略了索引名
alter table member add fulltext (intro) \G
主键索引:  alter table 表名 add primary key(列名);    #声明主键索引，不允许指定 主键索引名。
例如:  alter table member add primary key(id) \G
f. 删除非主键索引：alter table 表名 drop index 索引名;
注意: 对主键索引的删除： alter table 表明 drop primary key;  # 因为主键索引没有 索引名， 所以直接 drop primary key来删除。
g. 案例： 设有新闻表15列， 10列上有索引， 共500万行数据，如何快速导入？
1. 把空表的索引全部删除
2. 集中导入数据
3. 数据导入完成后，集中建索引
#如果带着索引插入数据的话，因为每一次插入都要重新更新索引文件，所以插入的速度和效率会非常低，一般都是对数据输入完成后，再统一加上索引。
# 索引的创建原则，a. 不要过度索引  b.在where条件最频繁的列上加索引  c.尽量索引散列值/不会重复或者离散值加索引， 而对于过于集中的值加索引无意义
h. 关于全文索引: 在mysql的默认情况下, 对于中文的意义不大。
全文索引建立后的查询语句：  select * from 表名 where match （全文索引名） against ('要搜索的字段');
关于全文索引的停止词： 对于日常常见的单词，是不会建立索引的，会被系统忽略而不加索引
值,  例如 this, is, you, my等。
全文索引对中文支持不好的原因在于： 因为英文索引靠空格 和标点符号 来分隔单词，进而对单词进行索引。 而对于中文， 词与词之间没有空格来隔开单词，
所以mysql无法识别每个中文词。
i. 查看全文索引中某个词的匹配度的命令: select id, email, match (intro) against ('health') from member;
结果会显示在一个表中，右侧显示了index关键字 health 在member表中各个行上的匹配度。
match (全文索引名) against ('keyword');
8. Mysql的存储过程:  概念类似于函数，就是把一段代码封装起来，当要执行该段代码的时候，可以通过调用该存储过程来实现。 在封装的语句体里面，可以用
if/else, case, while等控制结构。 可以进行sql编程。
a. 查看现有的存储过程语句：
show procedure status;
b. 删除现有的存储过程语句：
drop procedure 存储过程的名称;
c. 编写一个存储过程，体会”封装sql“的用法：
create procedure p1(n int, j char(1))  #后面是参数表
begin
#封装sql存储过程的语句, 类似于函数体， 体会封装， 参数传递和控制结构 (条件判断， 循环语句)
if j = 'h' then
  select * from g where num>n;
else
  select * from g where num<n;
end inf;
end$
举例2：计算1->n的和  循环procedure的定义。
create procedure p4 (n smallint)
begin
  declare i int; 
  declare s int;  #声明变量
  set i = 1;  　#给声明的变量赋值 必须用set 关键字，而不能缺省
  set s = 0;
  while i<=n do
  set s = s+i;
  set i = i+1;
  end while;
select s;  #将最后求和的结果显示出
end$
调用语句  call p4(100);  #结果显示 5050
定义完成后可以 用  show procedure status$ 查看
d. 调用存储过程的语句:  call 存储过程的名字([参数表])$
例如:  call p1()$

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。