目录:
并发控制:锁
事务
存储引擎
性能优化
并发控制:锁
MySQL并发控制的主要手段就是锁,锁主要分为两种:
1、共享锁(也叫读锁)。
2、排它锁(也叫写锁),写锁会阻塞其它的读锁与写锁。
———————————————————————————————————————————————————————
说道锁,那么就一定要了解锁的粒度,MySQL中锁的粒度大可分为三种(不同的存储引擎支持的锁粒度不一样,目前高版本的MySQL默认使用InnoDB引擎,它默认为行级锁):
开销
加锁速度
是否会出现死锁
并发读与性能
表级锁
小
快
不会
并发度低,性能最差
行级锁
大
慢
会
并发度高,性能最高
页级锁
介于表级锁与行级锁之间
介于表级锁与行级锁之间
会
介于表级锁与行级锁之间
———————————————————————————————————————————————————————
事务
1、事务的四大特性:
原子性:一个事物中的SQL要么全都执行,要么全不执行。
一致性:事务前后的数据需要保持一致,所有约束依然需要满足。
隔离性:一个事物的执行,不受另一个事务的影响。
持久性:事务执行完后,对数据的修改必须持久化到数据库中。
———————————————————————————————————————————————————————
2、事务的隔离级别:
READ UNCOMMIT(未提交读):即使事务没有commit,其它事务也能狗看到修改后的数据。
READ COMMIT(已提交读/不可重复读):事务从开始到结束,所做的任何修改都对其它事务不可见。
REPEATABLE READ(可重复读,MySQL默认):同一个事务中,读到的数据是一致的。
SERIALIZABLE(序列化):事务串行执行。
脏读:事务可以读取到其它未提交的数据。
不可重复读:因其它事务的修改对本身可见,所以会导致多次读取的值可能会不一样,故不能读取多次,被称为不可重复度。
幻读:事务A读1到5的数据,此时1-5会被事务A上锁,然后事务B插入一条数据6,并执行了commit。此时事务A如果再查询的话就会查到数据6,这就像幻觉一样,但MySQL通过MVCC使得数据6并不会被查询出来(虽然不会被查询出来,但却不能插入数据6,因为库里已经存在了数据6)。总得来说幻读就是当事务不独立执行时发生的一种现象,当第一个事务涉及到全表修改,第二个事务插入一行新数据,此时便会发生操作第一个事务的用户发现表中还有未修改的数据,就像发生幻觉一样。
———————————————————————————————————————————————————————
3、死锁:
死锁是指,多个两个及以上的事务对同一资源的占用,并请求锁定对方占用的资源。
如以下情况:
--事务一
start transaction;
update t_customers set name= '小明1' where id = 1;
update t_customers set name= '小华1' where id = 2;
commit;--事务二
start transaction;
update t_customers set name= '小华2' where id = 2;
update t_customers set name= '小明2' where id = 1;
commit;
若两个事务都执行了第一条update语句,锁定了各自的资源后,那么执行第二条语句时就会出现死锁的情况。
而InnoDB目前处理死锁的方法是,将持有最少行级排它锁的事务进行回滚(这是相对比较简单的死锁回滚算法),如果数量相同,就会回滚最后尝试获取锁的事务。
———————————————————————————————————————————————————————
4、MVCC:多版本并发控制,可以认为MVCC就是行级锁的一个变种,但是他在很多情况下避免了加锁操作。
MVCC是通过保存数据在某个时间点的快照来实现的,因MVCC没有一个同意的标准,所以各自的实现都不一样。
在InnoDB引擎是通过在每列数据后添加两个隐藏的列来实现,一列保存行数据创建时间(版本号),一列是行数据删除时间(版本号)。
比如在REPEATABLE READ隔离级别下:
Insert:为新插入的每一行记录保存当前系统版本号作为行版本号。
Delete:为删除的每一行保存当前系统版本号作为删除版本号。
Update:插入一行新的记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号为原来的行作为删除版本号。
Select:
1、只查找版本号早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号);这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或修改过的。
2、行的删除版本号要么没有,要么大于当前事务版本号。这样可以确保事务读取到的行,在事务开始之前未被删除。
MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。因为READ UNCOMMITTED总是读取最新的数据行,而不是符合当前事务版本的数据行。
而SERIALIZABLE则会对所有读取的行都加锁。
存储引擎
InnoDB引擎:InnoDB表基于聚簇索引建立,其索引结构和MySQL的其他存储引擎有很大差别,从磁盘读取数据时采用的可预测性预读。
Myisam引擎:在MySQL5.1版本之前,MyISAM是默认的存储引擎。MyISAM提供了大量的特性,包括全文检索、压缩、空间函数等,但MyISAM不支持事务和行级锁,而有一个缺陷就是崩溃后无法安全恢复虽然MyISAM有这样的缺陷,但并非一无是处。对于只读的数据,或者表比较小,可以忍受修复操作,则依然可以继续使用MyISAM引擎。
Memory引擎:在Memory表中,所有的数据都是保存在内存中,不需要进行磁盘I/O。重启时候会保留表结构,但数据会丢失。
性能优化
大致可从4个方面优化:
1、硬件
2、系统配置优化
3、数据表结构优化
4、SQL及索引优化