❝数据库的乐观锁和悲观锁? MySQL 中有哪几种锁,列举一下? MySQL中InnoDB引擎的行锁是怎么实现的? MySQL 间隙锁有没有了解,死锁有没有了解,写一段会造成死锁的 sql 语句,死锁发生了如何解决,MySQL 有没有提供什么机制去解决死锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。
打个比方,我们到淘宝上买一件商品,商品只有一件库存,这个时候如果还有另一个人买,那么如何解决是你买到还是另一个人买到的问题?这里肯定要用到事物,我们先从库存表中取出物品数量,然后插入订单,付款后插入付款表信息,然后更新商品数量。在这个过程中,使用锁可以对有限的资源进行保护,解决隔离和并发的矛盾。
从对数据操作的类型分类:
从对数据操作的粒度分类:
为了尽可能提高数据库的并发度,每次锁定的数据范围越小越好,理论上每次只锁定当前操作的数据的方案会得到最大的并发度,但是管理锁是很耗资源的事情(涉及获取,检查,释放锁等动作),因此数据库系统需要在高并发响应和系统性能两方面进行平衡,这样就产生了“锁粒度(Lock granularity)”的概念。
适用:从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
行锁 | 表锁 | 页锁 | |
---|---|---|---|
MyISAM | √ | ||
BDB | √ | √ | |
InnoDB | √ | √ | |
Memory | √ |
MyISAM 的表锁有两种模式:
MyISAM 表的读操作与写操作之间,以及写操作之间是串行的。当一个线程获得对一个表的写锁后, 只有持有锁的线程可以对表进行更新操作。其他线程的读、 写操作都会等待,直到锁被释放为止。
默认情况下,写锁比读锁具有更高的优先级:当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,然后再给读锁队列中等候的获取锁请求。
InnoDB 实现了以下两种类型的行锁:
为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁:
索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。
乐观锁与悲观锁是两种并发控制的思想,可用于解决丢失更新问题
乐观锁会“乐观地”假定大概率不会发生并发更新冲突,访问、处理数据过程中不加锁,只在更新数据时再根据版本号或时间戳判断是否有冲突,有则处理,无则提交事务。用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式
悲观锁会“悲观地”假定大概率会发生并发更新冲突,访问、处理数据前就加排他锁,在整个数据处理过程中锁定数据,事务提交或回滚后才释放锁。另外与乐观锁相对应的,悲观锁是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了。
记录锁(Record Locks):单个行记录上的锁。对索引项加锁,锁定符合条件的行。其他事务不能修改和删除加锁项;
SELECT * FROM table WHERE id = 1 FOR UPDATE;
它会在 id=1 的记录上加上记录锁,以阻止其他事务插入,更新,删除 id=1 这一行
在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时,也会对该行数据加记录锁:
-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;
间隙锁(Gap Locks):当我们使用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录,叫做“间隙”。
InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。
对索引项之间的“间隙”加锁,锁定记录的范围(对第一条记录前的间隙或最后一条将记录后的间隙加锁),不包含索引项本身。其他事务不能在锁范围内插入数据,这样就防止了别的事务新增幻影行。
间隙锁基于非唯一索引,它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking
算法,请务必牢记:使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据。
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
即所有在(1,10)
区间内的记录行都会被锁住,所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞,但是 1 和 10 两条记录行并不会被锁住。
GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况
临键锁(Next-key Locks):临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。(临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。)
Next-Key 可以理解为一种特殊的间隙锁,也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁,当某个事务持有该数据行的临键锁时,会锁住一段左开右闭区间的数据。需要强调的一点是,InnoDB
中行级锁是基于索引实现的,临键锁只与非唯一索引列有关,在唯一索引列(包括主键列)上不存在临键锁。
对于行的查询,都是采用该方法,主要目的是解决幻读的问题。
❝select for update有什么含义,会锁表还是锁行还是其他
for update 仅适用于InnoDB,且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时,通过“for update”语句,MySQL会对查询结果集中每行数据都添加排他锁,其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。
InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!假设有个表单 products ,里面有id跟name二个栏位,id是主键。
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
SELECT * FROM products WHERE id='-1' FOR UPDATE;
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
注1: FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。注2: 要测试锁定的状况,可以利用MySQL的Command Mode ,开二个视窗来做测试。
❝MySQL 遇到过死锁问题吗,你是如何解决的?
死锁产生:
检测死锁:数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
死锁恢复:死锁发生以后,只有部分或完全回滚其中一个事务,才能打破死锁,InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁,多数情况下只需要重新执行因死锁回滚的事务即可。
外部锁的死锁检测:发生死锁后,InnoDB 一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。但在涉及外部锁,或涉及表锁的情况下,InnoDB 并不能完全自动检测到死锁, 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
死锁影响性能:死锁会影响性能而不是会产生严重错误,因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上,当许多线程等待同一个锁时,死锁检测可能导致速度变慢。有时当发生死锁时,禁用死锁检测(使用innodb_deadlock_detect配置选项)可能会更有效,这时可以依赖innodb_lock_wait_timeout
设置进行事务回滚。
MyISAM避免死锁:
InnoDB避免死锁:
SELECT ... FOR UPDATE
语句来获取必要的锁,即使这些行的更改语句是在之后才执行的。SELECT ... LOCK IN SHARE MODE
获取行的读锁后,如果当前事务再需要对该记录进行更新操作,则很有可能造成死锁。如果出现死锁,可以用 show engine innodb status;
命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的 SQL 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
❝日常工作中你是怎么优化SQL的? SQL优化的一般步骤是什么,怎么看执行计划(explain),如何理解其中各个字段的含义? 如何写sql能够有效的使用到复合索引? 一条sql执行过长的时间,你如何优化,从哪些方面入手? 什么是最左前缀原则?什么是最左匹配原则?
在优化MySQL时,通常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。
我们可以通过 show 命令查看 MySQL 状态及变量,找到系统的瓶颈:
Mysql> show status ——显示状态信息(扩展show status like ‘XXX’)
Mysql> show variables ——显示系统变量(扩展show variables like ‘XXX’)
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行,包括执行状态、是否锁表等
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息
是什么:使用 Explain 关键字可以模拟优化器执行SQL查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈
能干吗:
怎么玩:
expalin
各字段解释
explain-key
case:
explain-demo
MySQL 的慢查询日志是 MySQL 提供的一种日志记录,它用来记录在 MySQL 中响应时间超过阈值的语句,具体指运行时间超过 long_query_time
值的 SQL,则会被记录到慢查询日志中。
long_query_time
的默认值为10,意思是运行10秒以上的语句查看开启状态
SHOW VARIABLES LIKE '%slow_query_log%'
开启慢查询日志
mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;
也可set文件位置,系统会默认给一个缺省文件host_name-slow.log
使用set操作开启慢查询日志只对当前数据库生效,如果MySQL重启则会失效。
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3
注:log-slow-queries 参数为慢查询日志存放的位置,一般这个目录要有 MySQL 的运行帐号的可写权限,一般都将这个目录设置为 MySQL 的数据存放目录;long_query_time=2 中的 2 表示查询超过两秒才记录;在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数,表示记录下没有使用索引的查询。
可以用 select sleep(4)
验证是否成功开启。
在生产环境中,如果手工分析日志,查找、分析SQL,还是比较费劲的,所以MySQL提供了日志分析工具mysqldumpslow。
通过 mysqldumpslow --help 查看操作帮助信息
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more
也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志
通过慢日志查询可以知道哪些 SQL 语句执行效率低下,通过 explain 我们可以得知 SQL 语句的具体执行情况,索引使用等,还可以结合Show Profile
命令查看执行状态。
Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下,参数处于关闭状态,并保存最近15次的运行结果
分析步骤
mysql> show profiles; +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.00385450 | show variables like "profiling" | | 2 | 0.00170050 | show variables like "profiling" | | 3 | 0.00038025 | select * from t_base_user | +----------+------------+---------------------------------+
mysql>Show variables like 'profiling'; --默认是关闭,使用前需要开启
❝查询中哪些情况不会使用索引?
一般性建议
永远小标驱动大表(小的数据集驱动大的数据集)
slect * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id
当 B 表的数据集必须小于 A 表的数据集时,用 in 优于 exists
select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`
当 A 表的数据集小于B表的数据集时,用 exists优于用 in
注意:A表与B表的ID字段应建立索引。
order by关键字优化
GROUP BY关键字优化
max_length_for_sort_data
参数的设置,增大sort_buffer_size
参数的设置MySQL 支持的数据类型非常多,选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据,下面几个简单的原则都有助于做出更好的选择。