1. 存储引擎
MySQL支持多种存储引擎,不同的存储引擎有着各自的特点和适用场景,选择合适的存储引擎可以提升数据库性能和可靠性。
常见的三种存储引擎是InnoDB、MyISAM和Memory。
1.1 InnoDB引擎
InnoDB引擎是MySQL默认的存储引擎,具有以下特点:
- 支持事务:InnoDB支持ACID事务特性,保证数据的一致性和完整性。
- 安全性最强:InnoDB支持行级锁和外键约束,数据访问更加安全可靠。
- 使用聚簇索引:InnoDB使用聚簇索引,可以大幅度提高查询效率。
- 适合高并发:InnoDB使用行级锁,对于高并发的应用非常友好。
1.2 MyISAM引擎
MyISAM引擎是一个轻量级的存储引擎,适合以读和插入操作为主,并对事务和并发要求不高的应用。具有以下特点:
- 访问速度快:MyISAM使用表锁,访问速度较快。
- 节省空间:MyISAM可以转换为压缩、只读表节省空间。
- 适合读写比例低的应用:MyISAM适合以读和插入操作为主,并对事务和并发要求不高的应用。
1.3 Memory引擎
Memory引擎是一种将表数据存储在内存中的存储引擎,具有以下特点:
- 读取速度快:Memory引擎可以大幅度提高查询效率。
- 查询效率最高:由于表数据存储在内存中,查询效率非常高。
- 数据易丢失:由于表数据存储在内存中,数据库重启或系统宕机会导致表数据丢失。
2. MySQL锁
MySQL锁是控制并发访问的重要手段之一。锁可以避免多个线程同时修改同一数据,保证数据的一致性和完整性。MySQL支持两种基本的锁类型:共享锁(读锁)和排它锁(写锁)。根据锁作用范围的不同,MySQL锁又可分为表级锁和行级锁。
2.1 表锁
表锁是一种粗粒度的锁,对整张表加锁,可以保证事务的串行化执行。MySQL会自动给涉及到的表加锁,对于select语句会自动加读锁,对于update、delete、insert语句会自动加写锁。如果对某张表加了读锁,不会阻塞其它线程的读操作,但是会阻塞其它线程的写操作。如果对某张表加了写锁,则既会阻塞其它线程的读操作,也会阻塞其它线程的写操作。为了避免死锁的发生,应尽量缩小锁的范围。
举个例子,假设有两个线程同时对一个表进行操作,第一个线程执行update语句,此时会给表加上写锁,第二个线程执行select语句,由于需要读取数据,所以会给表加上读锁,此时第二个线程会被阻塞,直到第一个线程执行完毕释放锁。
在使用表级锁时要注意以下几点:
- 尽量缩小锁定范围,减少锁冲突。
- 避免长时间占用锁,及时释放锁资源。
- 在事务中,只有当所有表都加锁成功后才会真正执行事务处理。
2.2 行锁
行锁是一种细粒度的锁,只针对某些特定的行记录加锁,可以提高并发度。MySQL采用多版本并发控制(MVCC)实现行锁,每行记录都有一个版本号,事务可以读取该版本的数据,同时不能读取未提交的数据或已经被其他事务锁定的数据。
对于update、delete、insert语句,InnoDB会自动给涉及到的数据集加排它锁(写锁),防止其他事务修改该记录。对于普通的select语句,InnoDB不会加任何锁,如果需要获取该行数据的共享锁,需要使用"SELECT … FOR SHARE"进行查询。在模拟行锁前,需要关闭自动提交功能,并通过commit提交事务释放锁。
举个例子,假设有两个线程同时对一个表的不同记录进行操作,第一个线程修改一条记录时,只会对该记录加锁,不会影响其它行的访问,第二个线程可以继续访问其它行记录,直到需要修改的行被锁定。
在使用行级锁时要注意以下几点:
- 可以使用索引尽量缩小锁范围。
- 动态调整innodb_lock_wait_timeout参数来控制等待锁的时间。
- 要避免死锁发生,可以使用lock in share mode和for update语句加共享锁和排它锁。
代码示例:
– 模拟行级锁
set autocommit = 0;
begin;
– 对id=1的行加排它锁
select * from student where id=1 for update;
– 此时对id=2的行进行操作会被阻塞
update student set name=‘张三’ where id=2;
commit;
– 使用lock in share mode加共享锁
select * from student where id=1 lock in share mode;