MySQL基础知识：存储引擎、锁

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;