mysql触发器与事务控制、数据库锁

触发器

#创建触发器
create trigger c_cs2
after insert on cs3 for each row begin
insert into cs2(b,c)values('vgsh','8945612');
end;$$

#查看触发器
show triggers \G;

表锁

#表加锁
lock table cs2 read;

#表解锁  （表加锁的时候，另台客户端想修改写入需要等待解锁）
unlock tables;

事务

#新建事务（新建事务的时候只有commit了另一台客户端才能看见）
start transaction; 
#设置回滚点
savepoint a;  
#回滚到点a
rollback to savepoint a;  


#创建分布式事务a
xa start 'cs','a'; 
#第一阶段提交
xa end 'cs','a'; xa prepare 'cs','a'; 
#查看当前分支事务状态
xa recover \G;  
#提交分支事务
xa commit 'cs','a'; 

sql调优

sql语句执行过程

#查看方法：select @@have_profiling;
#设置开启方法： set profiling = 1;

#查询耗时
show profiles;

其他调优项

#查询数据库的操作类型（读取为主还是写入为主）
show status like 'com_%'; 

#查看mysql线程
show processlist; 

#查看表状态（type访问类型，百度）
explain select * from cs where a = 100; 

#查询各语句耗时（扩展：show profile cpu; //查询语句在cpu耗时）
show profile; 


#开启trace分析，设置为json
set optimizer_trace="enabled=on",end_markers_in_json=on; 

#设置trace可用最大内存
set optimizer_trace_max_mem_size=1000000; 

#检车trace
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G; 

#检查一个或者多个表是否有错误
check table users;

#优化表碎片
optimize table users;

#查看表的最大和最小value等信息，用来优化表
select * from cs1 PROCEDURE ANALYSE() \G;

利用二进制设计数据库

#省空间

#例如顾客购买过什么
#牛奶=1 , 面包=2 , 水=3

#假设: 1=买的 , 0=没买的
买牛奶 = 100
面包 = 010
面包和水 = 101

拆分表

#拆分表用以提高效率

#有些列常用，有些不常用。减少IO，需要管理冗余，需要联合查询
垂直拆分（竖着拆）

#表很大，表数据有独立性（3月前的为一组），需要把数据放到多个介质上
#水平拆分会增加复杂度
水平拆分（横着拆)

细节点优化

逆规范开发

#增加冗余列列
#将数据转移到中间表计算
#派生列  其他表计算后的结果增加的列
#重新组表  两表合一表
#分隔表
#维护数据完整性
#批处理维护
#触发器

越规范 越复杂，越浪费性能，所以可以适当冗余，提升性能

查询用户姓名和金额，分别在两个表，链接表很浪费性能，所以，把姓名表添加个冗余字段，少链接个表，提升性能

但是会出现大量冗余数据，所以要实际的斟酌

锁

MyISAM

MEMORY -- 表级锁 -- 

MyISAM 同时请求读锁和写锁，优先写锁，而且写锁请求可以插入到读锁前面（插队）

InnoDB

-- 行级锁/表级锁 
-- 默认行级 //支持事务

BDB

-- 页面锁/也支持表级锁 
-- 被InnoDB取代，成为了历史

不同锁区别

表级锁

-- 开销小，加锁快，不会出现死锁，粒度大，锁冲突概率高，并发度低
-- 适合查询。没有大量写入。例如web

-- 读的情况下 -> 不会阻塞其他读取请求，会阻塞其他写入请求
-- 写的情况下 -> 会阻塞其他读取请求，也会阻塞其他写入请求

#加锁，有锁的用户只能访问加锁表（write->写锁，read->读锁）
lock table m write;
#解锁
unlock tables;

页面锁

-- 开销中，加锁中，会死锁，冲突合粒度中
-- 大并发，大量索引查询修改。例如在线事务处理（OLTP）

行级锁

#行级锁
开销大，加锁慢，会死锁，粒度小，锁冲突低，并发高

#分析表锁争用情况,Table_locks_waited越高争用越大
show status like 'table%';

数据库内存分配原则

1. 把多点内存分配给缓存
	
2. MyISAM依赖IO缓存，如果有MyISAM表，就把内存分配给IO

3. 排序区和连接去等缓存是session用的，设置太大会浪费资源，高并发会把内存用光