mysql事务及优化、触发器详解

mysql事务

1、事务本身就存在三种问题。脏读、重复读、幻读。
	脏读：A设置事务为手动提交，然后插入了一条数据，B这时候读取到了这条数据。
			然后A把这个数据回滚了，那么B读到的数据显然就是脏东西，被清理掉了，所以
			叫脏读。
	重复读：读到的数据之后被修改了。A从数据库中读了一条数据，B对同一条数据进行了修改，
			那么A的数据就失效了。10点查询时候还没改，11点改动了就失效了。
	幻读：和重复读差不多，都是查询后有修改。不过幻读是A查询整表查到了10条数据，B又加了
			5条数据，那么A查询的信息就失效了。

解决事务

原理：innodb引擎实现了4个隔离级别，就是来对这些修改、未提交进行一个控制，及时通知其他的事务。
1、读未提交 （read uncommitted)
	允许一个事务可以看到其他事务未提交的修改。
	就是啥问题都不解决，随便搞。
	【事务对当前被读取的数据不加锁】
2、读已提交（read committed）
		顾名思义就是只读提交过的信息。如果一个信息开启了事务手动提交，做了修改之后还没提交，那么这条数据是
		不可查的。也就不存在之后回滚造成脏读。
	【事务对当前被读取的数据加行级共享锁，一旦读完该行，立即释放该行级共享锁】
3、可重复读（reaptable read）
		最开始解决不了幻读，mysql已经对这个进行了优化，这个隔离级别可以解决上述三种问题。
	【】
4、串行化/序列化（serialiable）
		解决了所有问题，加表锁。
【需要注意的是，以上4种，安全性越高，执行效率就越低】

两种数据库引擎对事务的支持

1、InnoDB（MySQL默认存储引擎 从版本5.5.5开始）

支持事务，行级锁，以及外键，拥有高并发处理能力。但是在创建索引和加载数据时，比MyISAM慢。默认的隔离级别是Repeatable Read（可重复读）
2、MyISAM
不支持事务和行级锁。所以速度很快，性能优秀。可以对整张表加锁，支持并发插入，支持全文索引。

mysql的语句

数据库隔离级别查询：select @@tx_isolation
数据库隔离级别设置：set session transaction isolation level serializable
																							或者 read uncommitted 
																							或者 read committed
																							或者 repeatable read

SET autocommit = false; #手动提交，一直是手动提交，除非重启mysql或者 SET autocommit =true;
SET autocommit = true;
#回滚	ROLLBACK; 	
#提交	COMMIT;

mysql优化介绍：

1、查看mysql5.75可以使用的引擎	show engines;
2、explain 
		select * from student			查看该查询的效率
		【
			system	表里面至少有一行且只有一行数据并且查询的时候会出现，	
			const	就是主键索引。有两个条件限制【必须是=号，只能匹配到一条，有等号才能精准匹配。等号里面的值一定是常量，不能是变量，不仅如此，还不能搞各种函数。/必须匹配到索引查询】 搜索主键索引的时候int占4个字节，所以key_len是4。
			eq_ref		主键索引
			ref		非主键索引
			ref_or_null
			index_merge
			unique_subquery	唯一子查询
			index_subquery	索引子查询
			range		范围扫描。
			index		查询的结果全是索引。也是全表扫描，只不过扫描所有的索引而已。相当于买一本书只翻目录，翻完目录就扔那里不看了。b+tree的所有非叶子节点就是索引，你查的是所有非叶子节点。
			all		查询的结果就是数据，只不过一行行的去找全表扫描，扫描所有的内容
	】
	3、显示所有索引  show index from stu

mysql优化详细实例？

1、*变索引字段，索引覆盖
	select * from student    		----------->        select id from student  （因为id是主键索引，其他索引同理）
2、建立联合索引，最常用的字段放左边，尽量所有索引都用上，
	select * from stu where name='123' and phone='123' and age=13 （这里name、phone、age都用上了）
3、in关键字换为子查询
4、like查询百分号查询放最后。
5、不在索引列上做任何操作（计算、函数、自动/手动 类型转换），会导致索引失效而转向全表扫描。
	比如select count(*)from stu where name = '王五'
6、in的优化，一般都会采用连接查询方案，大多数情况都是把子查询，安排在结果集上面，然后去连接其他的表。从而避免使用in，这就是in的优化原则。结果集就是ifrom后面。为啥呢？因为in的非主键索引失效。
	采取下面这种方案
	explain
	select * from stu as t1,(select email from stu as s1 where email != 'hzj@11.com')
	as t2 where t1.email=t2.email;

mysql几种结构？

1、二叉树	每个节点最多两个叶子，不能超过两个。极端情况为递增递减情况，会往一边倾斜。一旦往一边倾斜，二叉树就没意义了。深度太深了，等于没有树。
2、红黑树（平衡二叉树，核心就是降低树的层次）	左旋转、右旋转，用旋转来降低自己的深度，深度降下来效率一定会提高，时间复杂度就会降低。一排超过三个就会连最中间一个，小的放中间的左边，大的放中间的右边。但是数量一旦提高，比如变成700万，变成100万，红黑树也不行。
3、BTree(红黑过渡到btree是一种集合的概念)	利用度（MAX-degree）设置来控制层次，我们也叫水平扩展。每个节点不再是一个数据，变成集合了，然后再加上度，度就是每个节点的数量。
4、B+Tree	每个节点只放索引主键数据，其他的数据全部放到叶子身上，把所有叶子利用双向链表进行头尾相接（双向的话升序和倒序都可以，目的是为了范围查询）。

icp和mrr优化

icp大概是把where查询部分操作放在了引擎层而不是数据库，比如range、ref等等。
mrr就是磁道的优化，查询时把那个随机磁盘找转换为确定某一片区域的磁盘读。
开启MRR
– 设置mrr启用
set @@optimizer_switch=‘mrr=on’;
set @@optimizer_switch=‘mrr_cost_based=off’;
– 设置read_rnd_buffer_size缓存尺寸（512k）
set @@read_rnd_buffer_size=524288;