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;