MySQL的事务处理
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
- 1、事务的原子性：一组事务，要么成功；要么撤回。
- 2、稳定性 ：有非法数据（外键约束之类），事务撤回。
- 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
- 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。
MYSQL 事务处理方法：
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
-- 开启事务
BEGIN;
-- 执行sql...
-- 成功执行 事务提交
commit
-- 失败执行 事务回滚
rollback
-- 修改密码
begin
update users set pssword='123123' where id=1;
commit
MySQL的表复制
复制表结构
mysql> create table 目标表名 like 原表名;
复制表数据
mysql> insert into 目标表名 select * from 原表名;
-- 复制表结构
mysql> create table 目标表名 like 原表名;
-- 复制表数据
mysql>insert into 目标表名 select * from 原表名;
mysql>insert into user(username,pssword,sex,age) select username,pssword,sex,age from users;
-- 暴力添加数据
mysql> into user(username,pssword,sex,age) select username,pssword,sex,age from user;
数据表的索引操作
PRIMARY, INDEX, UNIQUE 这3种是一类
PRIMARY 主键。 就是 唯一 且 不能为空。 INDEX 索引，普通的 UNIQUE 唯一索引。 不允许有重复。FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车
但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
-- 使用alter创建
alter table user add index index_name(字段值)
alter table user add unique unique_name(字段值)
alter table user add primary key(字段值)
-- 使用 alter删除索引
alter table user drop index index_name -- 删除普通索引和唯一索引
alter table user drop primary key -- 删除主键(必须先删除自动递增)
-- 修改表结构：
alter table user add aaa int after bbbb;
alter table user modify username varchar(20);
-- 显示索引信息
-- 你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
mysql> SHOW INDEX FROM table_name; \G
MySQL视图
-- 创建视图:
mysql> create view v_t1 as select * from t1 where id>4 and id<11;
Query OK, 0 rows affected (0.00 sec)
-- view视图的帮助信息:
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW
-- 查看视图:
mysql> show tables;
-- 删除视图v_t1:
mysql> drop view v_t1;
MySQL常用内置函数
-- 字符串处理函数
-- *concat(s1,s2,…Sn) 连接s1,s2..Sn为一个字符串
-- *length(str) 返回值为字符串str 的长度
-- 数值函数
-- *abs(x) 返回x的绝对值
-- *round(x,y)返回参数x的四舍五入的有y位小数的值
-- 日期和时间函数
-- *now() 返回当前日期和时间,
-- *unix_timestamp(date) 返回date时间的unix时间戳
-- *date_fomat(date,fmt) 返回按字符串fmt格式化日期date值
-- *datediff(expr,expr2) 返回起始时间和结束时间的间隔天数
-- 统计时间戳647583423距离当前时间相差天数（生日天数（不考虑年份））
mysql> select datediff(date_format(from_unixtime(647583423),"2017-%m-%d %h:%i:%s"),now());
-- 其他常用函数
-- *database() 返回当前数据库名
-- version() 返回当前服务器版本
-- user() 返回当前登陆用户名
-- inet_aton 返回当前IP地址的数字表示 inet_aton("192.168.80.250");
-- inet_ntoa(num) 返回当前数字表示的ip inet_ntoa(3232256250);
-- *password(str) 返回当前str的加密版本
-- *md5(str) 返回字符串str的md5值
MySQL的触发器
-- 格式：1、触发器的定义：
create trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt
说明：
# trigger_name：触发器名称
# trigger_time:触发时间，可取值：BEFORE或AFTER
# trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
# tb1_name：指定在哪个表上
# trigger_stmt：触发处理SQL语句。
-- 查看所有的 触发器
show triggers\G;
-- 删除触发器
drop trigger trigger_name;
-- 创建一个deluser的触发器,,当 对user表进行 删除 之前 往del_user中添加一个数据
\d //
create trigger deluser before delete on user for each row
begin
insert into del_user values(old.id,old.username,old.age,old.email,old.sex,old.account);
end;
//
-- 创建一个触发器,当对user表中数据进行添加时,修改统计的数据
\d //
create trigger countuser after insert on user for each row
begin
update user_count set num = num+1;
end;
//
注意:
1,注意在触发器中的 执行语句,要保证SQL能够正确执行
2,在创建一个 insert 类型触发器时,在触发器中无法使用 old 来获取原来的数据,
MySQL存储过程
Mysql储存过程是一组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，
当需要使用该组SQL语句时用户只需要通过指定储存过程的名字并给定参数就可以调用执行它了，
简而言之就是一组已经写好的命令，需要使用的时候拿出来用就可以了
\d //(修改语句结束符号)
create procedure p1()
begin
set @i=0;
while @i<10 do
insert into user values(null,concat('user',@i),@i,concat('user',@i,'@qq.com'),1,99);
set @i=@i+1;
end while;
end;
//
-- 执行储存：
call p1；
-- 查看存储具体信息：
show create procedure p1\G；
-- 删除触发器:
drop procedure p1;
MySQL日志
开启bin-log日志
-- 1,使用vim或其它编辑器 打开 mysql 配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
-- 2,找到 以下两个配置项
server-id = 123456
log_bin = /var/log/mysql/mysql-bin
-- 3,把注释打开 改完后 保存推出
-- 4,重启mysql
sudo service mysql restart
-- 5,查看bin-log日志:
mysql>show binary logs;
-- 6,ls 查看是否存在mysql日志文件
cd /var/lib/mysql
-- 7,清空所有的bin-log日志
mysql>reset master;
-- 8,查看binlog日志文件
mysqlbinlog mysql-bin.000001
-- 备份数据时 刷新日志:
mysqldump -uroot -pwei test -l -F '/tmp/test.sql'
-- 其中：-F即flush logs，可以重新生成新的日志文件，当然包括log-bin日志
数据库的恢复
前提是开启了bin-log日志,并定期备份
思路:
1,通过定期备份的文件恢复大量数据
2,通过日志恢复备份后的数据操作
-- 刷新 日志
mysql> reset master;
Query OK, 0 rows affected (0.39 sec)
-- 查询最新日志
show binary logs;
-- 创建数据库
mysql> create database ops;
Query OK, 1 row affected (0.28 sec)
-- 选择并打开库
mysql> use ops;
Database changed
-- 创建表
create table user(
id int not null auto_increment,
name char(20) not null,
age int not null,
primary key(id)
)engine=InnoDB;
-- 添加数据
insert into user values(1,"wangbo","24"),(2,"guohui","22"),(3,"zhangheng","27");
-- 查询
mysql> select * from user;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
+----+-----------+-----+
3 rows in set (0.00 sec)
-- 现在进行数据备份
mysqldump -uroot -p -B -F -R -x --master-data=2 ops >/home/yc/py08/04-mysql/ops.sql
-----------------
参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
-- 再添加新的数据
insert into user values(4,"liupeng","21"),(5,"xiaoda","31"),(6,"fuaiai","26");
-- 查询数据
mysql> select * from user;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
| 4 | liupeng | 21 |
| 5 | xiaoda | 31 |
| 6 | fuaiai | 26 |
+----+-----------+-----+
6 rows in set (0.00 sec)
-- 此时误操作，删除了test数据库
drop database ops;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| python4 |
| sys |
| wx |
+--------------------+
6 rows in set (0.00 sec)
-- 1,先讲binlog文件导出
-- 将binlog文件导出sql文件，并vim编辑它删除其中的drop语句
-- [root@vm-002 backup]# mysqlbinlog -d ops /var/lib/mysql/mysql-bin.000002> /home/yc/002bin.sql
sudo mysqlbinlog -d ops /var/log/mysql/mysql-bin.000002> /home/yc/py08/04-mysql/002bin.sql
-- 2,-- 删除里面的drop语句
vim 002bin.sql
-- 3,-- 导入备份的数据文件,
mysql -uroot -p < /home/yc/py08/04-mysql/ops.sql
-- 4,-- 再导入删除 drop语句后的 binlog日志文件
mysql -uroot -p ops < /home/yc/py08/04-mysql/002bin.sql
SQL优化
优化sql步骤：
1,慢查询日志
2,找出执行慢的sql语句
3,进行具体语句分析,优化或建立索引
有关慢查询操作：
-- 查看有关mysql数据库服务器的一些信息
show [session|global] status;
session:当前连接
global：数据库服务器启动之后
show global status;
show status like 'Com_%' 一般查看以com开头的
-- 根据执行的sql语句（次数）
Com_select:查询
Com_update:修改次数
Com_insert:插入次数
Com_delete:删除的次数
-- 此处是影响的行数（比如查询一次显示20行，那么行数增加20行）
InnoDB_rows_read:执行select操作的次数
InnoDB_rows_updated:执行update的次数
InnoDB_rows_inserted:执行insert操作的次数
InnoDB_rows_deleted:执行delete操作的次数
connections：连接mysql的数量
Uptime:服务器已经工作的秒数
Slow_queries:慢查询的次数
-- // 查看“慢查询”的配置信息
show variables like "%slow%";
-- // 查看“慢查询”的时间定义
show variables like "long%";
-- //设置“慢查询”的时间定义
set long_query_time=0.2;
-- //开启慢日志
set global slow_query_log='ON';
mysql> show variables like '%slow%';
+---------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/yc-virtual-machine-slow.log |
+---------------------------+--------------------------------------------+
mysql> show variables like "long%";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
定位执行效率较低的SQL语句
1. explain select * from table where id=1000;
2. desc select * from table where id=1000;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE(简单查询，不是多表查询和复杂查询) 可能的值：simple,primary,union,dependent union,union result
table: php114（表名）
type: ALL（） 可能的值：system,const(最优化，使用了主键或者唯一健),eq_ref,ref.ref_or_null,index_merge
possible_keys: NULL 提示使用哪个索引会在该表中找到行
key: NULL（） mysql使用的索引，简单且重要
key_len: NULL（） mysql使用的索引长度
ref: NULL 显示使用哪个列或者常数与key一起从表中选择行
rows: 6 mysql执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引
Extra: 包含mysql解决查询的详细信息
1 row in set (0.00 sec)
sql语句具体优化
-- 1,给经常作为查询条件的字段添加索引
-- 2,添加索引的字段,尽量保持一定的唯一性,
-- 3,在使用索引时,注意类型,防止出现有索引,用不上
-- 4,注意在使用like模糊查询时,如果%或_出现在前面,则用不上索引
注意:索引不是加的越多越好,同时符合1,2,条件则可以添加索引索引过多,会增加表的维护成本,增 删 改 都会变慢,
