MySQL的高阶部分

MySQL的高阶部分

1. MySQL的事务
（1）存储引擎的介绍
   介绍：当客户端发送一条SQL语句给服务器时，服务器端通过缓存、语法检查、校验通过之后，然后会通过调用底层的一些软件组织，去从数据库中查询数据，然后将查询到的结果集返回给客户端，而这些底层的软件组织就是存储引擎。
   MySQL的存储引擎：
     - MySQL的核心就是存储引擎，MySQL可以设置多种不同的存储引擎，不同的存储引擎在索引、存储、以及锁的策略上是不同的。
     - Mysql5.5之前，使用的是myisam存储引擎，支持全文搜索，不支持事务。
     - Mysql5.5以后，使用的是innodb存储引擎，支持事务以及行级锁
    
（2）MySQL事务的介绍
   介绍：事务是一个操作序列，这些操作要么都做，要么都不做，是一个不能分割的工作单位。在两条或两条以上的SQL语句才能完成的业务时，才需要用事务，因为事务时同步原则，效率比较低。
   事务的ACID特性：
     - 原子性：放在同一事务的一组操作时不可分割的
     - 一致性：在事务的执行前后，整体的状态是不变的
     - 隔离性：事务之间是独立存在的，两个不同事务之间互不影响
     - 持久性：事务执行之后，将会永久的影响到数据库。
    
#例：一个事务操作

BEGIN;
update  t_account set money=money+100 where id =1;
update  t_account set money=money-100 where id =2;
COMMIT;

#一个回滚操作

BEGIN;
update  t_account set money=money+100 where id =1;
update  t_account set money=money-100 where id =2;
COMMIT;

注意：MySQL数据库，dml操作采用的是自动提交

#查看自动提交
show variables like 'autocommit';
#修改自动提交
set autocommit=0;

（3）MySQL事务并发时产生的问题
  脏读：在一个事务的执行范围内，读到了另一事务未提交的数据。
  解决：读已提交，一个数据库只能读到另一个事务提交后的数据。（Oracle默认的事务隔离级别）
  不可重复读：一个事务，在只读范围内，被另一事务修改并提交事务，导致多次读取的数据不一致的问题。
  解决：可重复读（MySQL默认的事务隔离级别）
  虚读：一个事务的只读范围内，被另一个事务删除或者添加数据，导致多次读取的数据不一致的问题。
  解决：串行化：解决所有问题，但是速度十分缓慢，不能使用并发事务。
  注意：查看事务的隔离级别：select @@tx_isolation;
2. MySQL的存储程序
（1）MySQL的存储程序的介绍
   描述：运行与服务器端的程序。
   优点：简化开发，执行效率比较高（在服务器端以通过校验，可直接使用）
   缺点：服务器端保存这些存储程序需要占用磁盘空间；数据迁移时，需要将这些存储程序进行迁移；调试和编写程序在服务器端都不方便
   存储程序的分类：存储过程、存储函数、触发器
   注意：存储程序不能使用事务
（2）存储过程
  介绍：存储过程是在服务器端的一段可执行的代码块。
例：

#修改结束符标志
delimiter  // 
#创建存储过程
create procedure pro_book()
begin 
#sql 
select * from book;
select * from book where bid=3;
end //
#运行
call pro_book()  

#参数的传入
delimiter //
create procedure pro_book02(num int)
begin 
select * from book where bid=num;
end ; //
--调用
call pro_book02(3)

#传出参数
delimiter //
create procedure pro_book03(num int,out v_name varchar(10))
begin 
select bname into v_name from book where bid=num;
end ; //
--调用,这里的@v_name是一个用户变量
call pro_book03(1,@v_name);
select @v_name;

#传入传出参数
delimiter //
create procedure pro_book04(num int)
begin 
select bid into num from book where bid=num;
end ; //
--调用 
set @v_id=3; --给用户变量赋值
call pro_book04(@v_id);
select @v_id;

控制流程语句

#if语句
delimiter //
create procedure if_test(score int)
begin 
-- 定义局部变量
declare myLevel varchar(20);
if score>80 then 
set myLevel='A';
elseif score >60 then 
set myLevel='B';
else 
set myLevel='C';
end if;
select myLevel;
end; //
-- 调用
call if_test(70);

#while循环
delimiter //
create procedure while_test()
begin 
declare i int ;
declare sum int ;
set i=1;
set sum =0;
while i<=100 do
set sum=sum+i;
set i=i+1;
end while ;
select sum;
end ;//
call while_test()

#loop循环
delimiter //
create procedure loop_test()
begin 
declare i int ;
declare sum int ;
set i=1;
set sum =0;
-- 起别名
lip:loop 
if i>100 then
-- 离开loop循环
leave lip ;
end if ;
set sum=sum+i;
set i=i+1;
end loop ;
select sum;
end ;//
call loop_test()

#repeat循环
delimiter //
create procedure repeat_test()
begin 
declare i int ;
declare sum int ;
set i=1;
set sum =0;
repeat 
set sum=sum+i;
set i=i+1;
-- 不要加分号
until i>100 
end repeat ;
select sum;
end ;//
call loop_test()

（3）.存储函数
  存储在服务器端，有返回值，函数可以作为SQL的一部分进行调用。
例：

delimiter //
create function func_01(num int)
-- 返回值类型
returns varchar(20)
deterministic
begin 
declare v_name varchar(20);
select bname into v_name from book where bid =num ;
return v_name;
end ; //
set @v_name=func_01(3);
select @v_name;
-- 作为SQL的一部分调用
select * from book where bname=func_01(3);

函数和存储过程的区别：
   - 存储过程有三种参数模式（in、out、inout）实现数据的输入输出，而函数是通过返回值进行数据传递。
   - 关键字不同
   - 存储过程可以作为独立个体执行，函数只能作为SQL的一部分执行。

（4）.触发器
   触发器，存储在服务器端，由事件调用，不能传参。
   事件类型：增、删、改
   语法：
create trigger 触发器名
触发时机（after|before） event(update|delete|insert)
on 需要设置触发器的表名 for each row (设置为行级触发器)
begin
一组sql
end;

例：

delimiter //;
-- 创建一个触发器
create trigger tri_test
after delete 
-- 设置为行级别的触发器
on book for each row
begin
insert into book values(old.id,'悲惨数据','zzy');
end;//

注意：在触发器中有两个对象：old、new，old表示删除数据时那条原数据记录，
new表示修改和插入数据时，那条新数据记录。

3. MySQL的表的设计
（1）数据库的三大范式：
   - 1NF：所有字段都是原子性的，不可分割的。
   - 2NF：非主键字段必须与主键相关（每一张表只描述一类事物），而不能与主键部分相关（在联合主键时有效）
   - 3NF：非主键字段必须与主键相关（每一张表只描述一类事物），而不能与主键部分相关（在联合主键时有效）
（2）表的关系：
一一对应

#以人和×××为例
#人表：
CREATE TABLE `t_people` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
#×××表：
create table t_idcard(
card_number varchar(18) primary key,
create_date date,
p_id int unique,
foreign key (p_id) REFERENCES t_people(id)
)

注意：设计方法：想办法让外键字段同时拥有唯一约束，外键字段在任意的表中都可以
一对多：

#以部门和员工表为例：
create table t_emp(
eid int PRIMARY KEY,
ename varchar(50) not null,
job varchar(50),
deptno int , 
foreign key (deptno) REFERENCES t_dept(deptno)
)
#部门表：
create table t_dept(
deptno int primary key,
deptname varchar(50)
)

注意：设计方法：只需要在多的那个表中增加一个外键约束

多对多：

设计方法：需要找一张中间表，转化成两个一对多的关系

4.数据库的优化：
1、尽量避免全表扫描，首先应考虑在where及order by设计的列上建立索引。
2、尽量避免在where字句中对字段进行null判断，否则将导致引擎放弃使用索引而进行全表扫描。
3、应尽量避免在where字句中使用！=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4、应尽量避免在where字句中使用or来链接条件。
5、使用in和not in也要慎用。
6、尽量不要使用select *来查询。
7、避免频繁的创建和删除临时表，以减少系统表的资源消耗

5.mysql有关权限的表
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。
这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

6.数据表损坏的修复方式
使用 myisamchk 来修复，具体步骤：
1）修复前将mysql服务停止。
2）打开命令行方式，然后进入到mysql的/bin目录。
3）执行myisamchk –recover 数据库所在路径/*.MYI
使用repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表
REPAIR TABLE 用于修复被破坏的表。 OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘
空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，
而非数据库）

7.数据库架构
数据库架构要考虑的问题：

1. 数据可靠和一致性；
2. 数据容错；
3. 当数据量和访问压力变大时，方便扩充；
4. 高度可用，出问题时能及时恢复，无单点故障；
5. 不应因为某一台机器出现问题，导致整网性能的急剧下降；
6. 方便维护

8.数据库的读写分离
1. 将读操作和写操作分离到不同的数据库上，避免主服务器出现性能瓶颈；
2. 主服务器进行写操作时，不影响查询应用服务器的查询性能，降低阻塞，提高并发；
3. 数据拥有多个容灾副本，提高数据安全性，同时当主服务器故障时，可立即切换到其他服务器，提高系统可用性；

9.mysql 索引的建立原则
1、索引分四类：
index ---- 普通索引,数据可以重复
fulltext ---- 全文索引,用来对大表的文本域(char，varchar，text)进行索引
unique ---- 唯一索引,要求所有记录都唯一
primary key ---- 主键索引,也就是在唯一索引的基础上相应的列必须为主键
2、使用索引需注意：

(1). 只对 where 和order by 需要查询的字段设置索引，避免无意义的硬盘开销；
(2). 组合索引支持前缀索引；
(3). 更新表的时候，如增删记录，MySQL会自动更新索引，保持树的平衡；因此更多的索引意味着更多的维护成本
3、索引建立原则
（1）. 尽量减少like，但不是绝对不可用，”xxxx%” 是可以用到索引的
（2）. 表的主键、外键必须有索引
（3）. 谁的区分度更高（同值的最少），谁建索引，区分度的公式是count(distinct（字段）)/count(*)
（4）. 单表数据太少，不适合建索引
（5）. where，order by ,group by 等过滤时，后面的字段最好加上索引
（6）. 如果既有单字段索引，又有这几个字段上的联合索引，一般可以删除联合索引；
（7）. 联合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
（8）. 联合索引: mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index(a,b,c). 可以支持 a|a,b|a,b,c 3种组合进行查找，但不支持 b,c 进行查找.当最左侧字段是常量引用时，索引就十分有效。
（9）. 前缀索引: 有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引 Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。
（10）. NULL会导致索引形同虚设
4、禁用索引

• like “ %xxx ”
• not in ， ！=
• 对列进行函数运算的情况（如 where md5(password) = “xxxx”）
• WHERE index=1 OR A=10
• 存了数值的字符串类型字段（如手机号），查询时记得不要丢掉值的引号，否则无法用到该字段相关索引，反之则没关系