MySQL基础

MySQL

安装与配置:

  1. 访问https://dev.mysql.com/downloads/mysql/,点击Looking for previous GA versions?,选择5.7.28,操作系统选择macOS,下载.dmg并安装。
  2. 在终端切换到根目录,编辑./.bash_profile文件
1
2
cd ~
vim ./.bash_profile
  1. 编辑./.bash_profile文件,保存退出:
1
2
export PATH=$PATH:/usr/local/mysql/bin
export PATH=$PATH:/usr/local/mysql/support-files
  1. 重新加载环境变量
1
source ~/.bash_profile
  1. 启动mysql.server,登录后修改密码:

    忘记密码或未设置密码,参考https://www.cnblogs.com/vector11248/p/6680509.html

1
2
3
4
5
sudo mysql.server start
mysql -u root -p

use mysql;
update user set authentication_string=password('新密码') where user='root' and Host='localhost';

5.7之前的版本:update mysql.user set password=password('新密码') where user='root' and host='localhost';


(〇)架构

MySQL架构

  1. Server层:
  • 连接器:身份认证、权限相关

  • 查询缓存(Query Cache):执行查询语句前,会先查询缓存(8.0后删除)

  • 分析器:词法分析、语法分析(判断SQL语句是否正确)

  • 优化器:按照MySQL认为的最优方案执行(比如多个查询条件合并、多个索引选择哪个)

    !=/NOT NULL…走二级索引还是全表扫描也是由优化器决定

  • 执行器:执行语句,从存储引擎返回数据

  • Binlog:日志

  1. 存储模块

SQL语句执行过程

  1. 查询语句:权限校验 -> (如果命中缓存) -> 查询缓存 -> 分析器 -> 优化器 -> 权限校验 -> 执行器 -> 存储引擎
  2. 更新语句:权限校验 -> 分析器 -> 优化器 -> 权限校验 -> 执行器 -> 存储引擎 -> redo log prepare -> binlog -> redo log commit

    InnoDB存在redo log,用于数据恢复、容灾


(一)账号 & 密码 安全

  1. 将数据库管理信息如application.yml从项目中剥离,不要上传Git
  2. 数据库增加IP白名单限制
  3. 每个账号只分配最小的权限,防止删库删表
  4. 定期修改数据库账号、密码

(二)引擎

查看存储引擎:show engines;

InnoDB:默认存储引擎

  1. 支持事务,具有回滚能力。

  2. 支持行锁。

  3. 聚簇索引。

    聚簇索引:数据项和索引项集中存储,索引文件本身就是数据文件
    (1)对于主键索引:主键索引B+树的叶节点存储的是实际的完整数据
    (2)对于二级索引:二级索引B+树的叶节点存储的主键,然后再根据主键索引进行查询。所以说二级索引隐含一个回表,这也是二级索引走索引还是全表扫描的唯一判断依据
    (3)所以对于InnoDB来说,必须要有主键,如果没有主键会自动生成一个自增主键,同时主键不宜过长(比如UUID就不适合),同时主键不建议是非单调的,这样会造成主键索引频繁分裂

  4. InnoDB不会记录表的具体行数,也就是说SELECT COUNT(*)会全表扫描。

  5. 对于AUTO INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引。

  6. 对于用户删除的数据/更新的二级索引,InnoDB不是立即物理删除,而是标记一下(逻辑删除),由后台的Purge线程进行批量的删除和更新

  7. 过期(指的是不需要用于构建之前的版本,也不需要用来回滚事务)的undolog也需要回收。

  8. InnoDB中存在MVCC,相当于乐观锁。

MyISAM

  1. 强调性能,执行速度快,但是不支持事务。

    最大缺陷:崩溃后无法恢复

  2. 只有表锁,不支持行锁。

    MyISAM只有表锁,总是一次性获取SQL语句需要的全部锁,所以MyISAM引擎不会死锁

  3. 非聚簇索引。

    非聚簇索引:数据项和索引项分开存储,获取数据需要2次查询,即索引B+树的叶节点存储的是数据在内存中的地址,而非实际数据

  4. MyISAM会记录表的具体行数,SELECT COUNT(*)不需要全表扫描。

  5. 对于AUTO INCREMENT类型的字段,MyISAM中比一定要有只含该字段的索引,可以是联合索引。


(三)索引

索引只根据WHERE后字段进行判断,SELECT后字段只决定哪些字段加载到内存,以及判断是否会使用覆盖索引

InnoDB的行锁是通过给索引列加锁来实现的。
只有通过索引检索数据,InnoDB才会用行级锁。
如果不走索引,InnoDB将默认表级锁。

优点:加速查询;减少I/O
缺点:维护索引代价大;会减慢增删改速度;占用空间多

单张表索引数量不要超过5个。


查看索引:EXPLAIN

EXPLAIN + SQL语句:查看某条SQL语句走的哪条索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
  • idSELECT语句的唯一标识符;
  • table:涉及的表或衍生表(比如UNION表)
  • possible_keys:此次查询可以使用的索引(但不一定使用
  • key:此次查询真正的索引
  • key_len:此次查询使用了索引的字节数(可以用于评估联合索引是全部使用,还是只是用了最左匹配部分
  • rows:估算需要扫描读取的数据行数(原则上rows越小说明索引设计越合理
  • partitions: 匹配的分区
  • ref: 哪个字段或常数与key一起被使用
  • filtered: 表示此查询条件所过滤的数据的百分比
  • Extra: 额外的信息,比如:是否使用覆盖索引 —— Extra:Using index

1.select_typeSELECT查询的类型

  • SIMPLE:不包含连接查询和子查询
  • PRIMARY:最外层的查询
  • UNIONUNION的第二或者随后的查询
  • DEPENDENT UNIONUNION的第二或者随后的查询语句,依赖于外层查询的结果
  • UNION RESULTUNION的结果
  • SUBQUERY:子查询中的第一个SELECT语句
  • DEPENDENT SUBQUERY:子查询中的第一个SELECT语句,依赖于外层查询的结果
1
2
3
4
5
6
7
8
9
10
11
mysql> EXPLAIN (SELECT * FROM user_info  WHERE id IN (1, 2, 3))
-> UNION
-> (SELECT * FROM user_info WHERE id IN (3, 4, 5));
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where |
| 2 | UNION | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

2.type:索引类型,可以判断是否走索引

速度:ALL < 【index】 < range ~ index_merge < 【ref】 < eq_ref < const < system

  1. system:表中只有一条数据
  2. const:针对主键索引/唯一索引的等值查询,最多返回一行数据
  3. ref常出现在多表的JOIN,针对非唯一索引或者非主键索引,或者是使用了“最左匹配原则”索引的查询
  4. eq_ref:常出现在多表的JOIN,表示对于前表的每一个结果,都只能匹配到后表的一行结果,并且查询的比较通常是=,效率高
  5. index表示全索引扫描(full index scan),扫描所有的索引,而不扫描数据
  6. range:范围查询
  7. ALL全表扫描

3.覆盖索引:Extra:Using index

  • 对于非主键索引,它的非叶子节点存储的是索引项,叶子节点存储的是主键。如果select条件后面有非索引项、非主键的字段,那么查询时就需要通过非主键索引查找到id,然后通过主键索引根据id查找非主键/非索引项,这就叫做“回表”
  • 所谓覆盖索引,就是说:select条件后没有非索引项(也就是说所有项都是主键/索引项),这样使用索引时就不需要进行回表。

    比如一个字段为id name sex的表,主键为idname字段上有索引。对于以下两条SQL

1.select * from table where name = 'xxx'
2.select id from table where name = 'xxx'
语句1中select *查找了非索引项sex,所以需要进行回表,而语句2select id不需要回表,性能更高。

一直到2020-2-27字节跳动·二面,我才真正搞清楚什么是覆盖索引……
它并不是一种索引类型,而是说查找字段全部被索引项覆盖,不需要回表


原理:B+

B+树:叶子节点中的数据是排好序的,可以串成一条链表。单个节点存储的数据应该为一页或者说页的倍数,可以减少MySQL页面替换的消耗

优点:范围查询十分迅速
缺点:空间换时间。

InnoDB/MyISAM两种引擎中B+树的实现:

  1. InnoDB聚簇索引:对于主键索引,B+树叶子节点data域存储的就是实际数据;对于非主键索引(二级索引),B+树叶子节点data域存储的是主键,然后根据主键再查主键索引,找到实际数据
  2. MyISAM非聚簇索引:B+树叶子节点data域存储的是数据在内存中存储的地址,每次索引检索,如果对应的key存在,则拿出存储的内存地址,然后去对应的内存地址获取数据。

B+树(MongoDB) vs B树(MySQL)?

首先我们需要知道B+树和B树的两个不同点:

  1. B+叶子节点有指向相邻叶子节点的指针,所有叶子节点是一条排序单链表
  2. B所有节点都存储数据B+只有叶子节点存储数,这意味着:虽然B+树在单次查找时的平均速度不如B树,但是B+树的查找时间更加稳定和平均,每次查询的耗时都是一样的

从这两个不同点可以推测出MongoDBMySQL的设计思想:

  1. MySQL是关系型数据库,存在大量范围查找,而B+树显然更适合范围查找
  2. MySQL中,每在树上向下查一层就是一次I/O操作,所以使用B+树会让I/O更加稳定;
  3. MongoDB范围查找比较少,一般都是单次查找某个key,所以更偏爱单次查找更快B树。

索引分类

主键索引:PRIMARY KEY

ALTER TABLE table ADD PRIMARY KEY ('column');

如果一条SQL操作了主键索引,InnoDB会锁定这条主键索引
如果一条SQL操作了二级索引,InnoDB会先锁定该二级索引,然后再锁定相关的主键索引

唯一索引:UNIQUE

索引列必须是唯一的,可以是null。
ALTER TABLE table ADD UNIQUE ('column');

普通索引:INDEX

ALTER TABLE table ADD INDEX index_name ('column');

联合索引:INDEX

ALTER TABLE table ADD INDEX index_name ('column1','column2',...);


最左匹配原则:type = ref
  1. 对于联合索引,查询条件只要含有左边第一列,就可以直接匹配对应的联合索引(type = ref),而不需要扫描全部索引

    对于目前主流的CBO优化数据库引擎,会自动选择最优的执行路径,并不会笨比到需要where条件的顺序和索引完全一致,只需要用到了联合索引的最左边第一列,就能够以type = ref的形式直接匹配到联合索引,不需要扫描全部索引

  2. 如果联合索引的查询条件不含左边第一列,就会扫描全部索引(type = index),尝试找到最佳的索引。不是100%不走索引!!!

    注意对于CBO数据库优化来说:①查询顺序不需要与索引顺序相同;②查询条件不需要包含全部索引列
    也就是说:对于索引idx_test(a,b,c,d),查询d AND c也可以匹配索引,只不过需要扫描全部索引(type = index)才能找到idx_test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 建表
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` varchar(11),
`b` varchar(11),
`c` varchar(11),
`d` varchar(11),
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_test`(`a`, `b`, `c`, `d`) USING BTREE
) ENGINE = InnoDB;

-- 插入数据
INSERT INTO `test` VALUES (1, 'a', 'b', 'c', 'd');

-- 【type=const possible_keys=PRIMARY;idx_test key=PRIMARY】 存在主键则会使用主键索引,其优先级最高,不会回表
EXPLAIN SELECT * FROM test WHERE a = 'a' AND d = 'd' AND id = 1;

-- 【type=ref possible_keys=idx_test key=idx_test】 存在联合索引最左字段,可以通过最左匹配直接使用联合索引
EXPLAIN SELECT * FROM test WHERE a = 'a' AND d = 'd';
-- 【type=ref possible_keys=idx_test key=idx_test】 联合索引的最左匹配和查询顺序无关
EXPLAIN SELECT * FROM test WHERE c = 'c' AND a = 'a';

-- 【type=index key=idx_test】 不存在联合索引最左字段,只能搜索所有索引,找到对应的联合索引
EXPLAIN SELECT * FROM test WHERE c = 'c' AND d = 'd';
-- 【type=index key=idx_test】 查询顺序不需要和索引顺序对应
EXPLAIN SELECT * FROM test WHERE d = 'd' AND c = 'c';

全文索引:FULLTEXT

最开始全文索引仅适用于MyISAM引擎,但从MySQL 5.6开始,InnoDB引擎也支持全文索引。

其原理就是反向索引(Inverted Index)

ALTER TABLE table ADD FULLTEXT ('column');


索引优化

  1. 表的数据少于百万级,不需要索引
  2. 经常增删改的表不需要索引
  3. 频繁更新的字段不适合索引
  4. LIKE %将不会走索引,而是全表扫描,避免这样的写法
  5. 如果列是varchar,传入的搜索条件是数字,不会走索引,避免这样的写法
  6. WHERE条件中有数学表达式或函数,不走索引,避免这样的写法
  7. OR两边的列都有索引才会走索引

值得注意的是!MySQL中使用!=/IS NULL/IS NOT NULL/<>同样会使用索引,并非像网上说的那样,不使用索引。
使用二级索引时,NULL被视为最小的数,被存放在B+树最左端。
使用!=/IS NULL/IS NOT NULL走不走索引的唯一判断依据是“成本”—— 二级索引需要逆向查聚簇索引,有一次回表操作,如果数据量过大,导致回表操作过多,索引的代价大于全表扫描,MySQL就会放弃索引,直接全表扫描
所以,以后谁再说!=/IS NULL/IS NOT NULL/<>不走索引啊,应该优化啊吧啦吧啦的,就给TM两拳。

  1. 能走主键索引的不要走二级索引,因为二级索引需要回表(即查主键索引),在数据量很大时同样可能造成慢查询

(四)事务

(1)特性:ACID

1.A(tomic)原子性

一个事务要么全部完成,要么全部不完成,不存在只完成一半的情况。

2.C(onsistency)一致性

事务执行前后,数据要保持一致。比如转账,不能我扣了8000,她却没有收到8000。

3.I(solation)隔离性

一个事务的执行不受其他事务影响。

4.D(urability)持久性

一个事务提交了,对数据库的改变就应该持久化。

(2)事务的隔离级别 & 事务的并发问题

查询隔离级别:SELECT @@tx_isolation;

1.RU(Read Uncommitted)(读未提交)

可以读取未提交的数据,什么并发问题都不能解决,一般不使用。

2.RC(Read Committed)(读已提交):Record

binlog日志格式必须设置为ROW,防止主从数据不一致
只能读取已经提交的数据,可以防止脏读,一般搭配版本号使用。

脏读:
读取了其他事务未提交的脏数据。

原理:给读取数据加Record锁,只锁住行,不会锁住Gap。

主键id = 5 、id = 8
SELECT … WHERE id = 5 FOR UPDATE; 锁住id=5,Record锁
SELECT … WHERE id >= 5 FOR UPDATE; 锁住id=5和id=8,Record锁

3.RR(Read Repeatable)(可重复读):NextKey、可以防止幻读

保证两次重复读取的数据是一样的,可以防止脏读不可重复度,但幻读仍有可能发生(这是说的普通SQL标准,不是MySQL的标准)。
但是要注意,MySQL的默认隔离级别RR使用的是NextKey锁,已经可以防止幻读

不可重复读:
两次重复读取数据不一致,原因是读取期间其他事务修改了数据。

幻读:
一个事务读取了几行数据,接着另一个事务插入/删除了数据,在随后的查询中,第一个事务会发现一些原本不存在的数据,就好像发生了幻觉一样。

不可重复读重点在于修改;幻读重点在于新增/删除。

原理:给读取数据加Record锁,同时给数据加范围的Gap锁

主键id = 5 、id = 8
SELECT … WHERE id = 5 FOR UPDATE; 锁住id=5,Record锁
SELECT … WHERE id = 6 FOR UPDATE; Gap锁,锁住(5,6)和(6,8)
SELECT … WHERE id >= 5 FOR UPDATE; Gap锁,锁住(-∞,5)、(5,8)和(8,+∞)

4.Serializable(序列化)

所有事务完全按照顺序执行,读写冲突,并发度急剧下降,可以防止脏读不可重复度幻读


(五)锁

MVCC(多版本并发控制)

参考《Mysql中MVCC的使用及原理详解》
【阿里-政务钉钉-一面】的时候被问到。

  • MVCCMulti Version Concurrency Control,多版本并发控制):通过对一个对象维护多个版本,提供一种友好的并发控制技术,使得READWRITE操作不发生冲突,读操作、读写操作不需要加锁,只有多个事务WRITE才会加锁

  • MVCC只工作在InnoDB引擎中,由于RC(读未提交)可以读取未提交事务的数据、序列化完全串行化执行,所以MVCC只支持RR(可重复读)/RC(读已提交)两个隔离级别

    从本质上来说MVCC就是一种基于CAS的乐观锁

  • InnoDBMVCC是通过undolog实现的,通过undolog可以找回数据的历史版本,找回的历史数据可以提供给用户读,也可以在回滚的时候覆盖数据页上的数据。


原理:create version & delete version

InnoDBMVCC,是通过在每行记录后面保存两个隐藏的列(create version & delete version)来实现的

  1. create version:创建该行的系统版本号(即:事务ID)
  2. delete version:删除该行的系统版本号(即:事务ID)

(1)INSERT:保存create version

INSERT会为新插入的每一行保存当前系统版本号作为create version

  1. 假设第一个事务ID为1,执行如下SQL:
1
2
3
4
5
start transaction;
insert into test values(1,'a') ;
insert into test values(2,'b');
insert into test values(3,'c');
commit;
  1. 第三个事务ID为3,执行如下SQL
1
2
3
start transaction;
insert into test values(4,'d') ;
commit;
  1. SELECT * FROM test;
idnamecreate versiondelete version
1a1undefined
2b1undefined
3c1undefined
4d3undefined

(2)DELETE:保存delete version

DELETE会为删除的每一行保存当前系统版本号作为delete version

  1. 假设第二个事务ID为2,执行如下SQL:
1
2
3
start transaction;  
delete from test where id=1;
commit;
  1. SELECT * FROM test;
idnamecreate versiondelete version
1a12
2b1undefined
3c1undefined

(3)UPDATE:新建行保存create version,被删除行保存delete version

UPDATE会新建一行,将当前系统版本号作为此新建行的create version
被修改的行相当于被删除,UPDATE将当前版本号作为被删除行的delete version

  1. 假设第四个事务ID为5,执行如下SQL:
1
2
3
start transaction;  
update test set name='test' where id=2;
commit;
  1. SELECT * FROM test;
idnamecreate versiondelete version
1a12
2b15
3c1undefined
4d3undefined
2test5undefined

(4)SELECT:当前事务ID>= create version<= delete version

InnoDB只会返回同时满足下面两个条件的行:

  1. create version <= 当前系统版本号(当前事务ID),即行在当前事务开始前就存在,或者是当前事务插入或修改过的
  2. delete version >= 当前系统版本号(当前事务ID)或者是undefined,即行在当前事务开始之前没有被删除

所以我如果在事务ID为3的事务中执行SELECT * FROM test,会返回:

idnamecreate versiondelete version
2b15
3c1undefined
4d3undefined

锁的类型:行锁/页锁/表锁

锁的类型可以理解为锁的粒度,即锁住了多大范围的数据,反映在lock_pagelock_rec字段中。

  1. 行锁:锁定一行,会出现死锁。开销大、加锁慢;锁定力度小、锁冲突概率低、并发度高。
  2. 页锁:介于两者之间,会出现死锁
  3. 表锁:直接锁定整个表,不会死锁。开销小、加锁快;锁定力度大、锁冲突概率高、并发度低。

子类型:RK/GK/NK/IK

1.Record锁(RK)

锁直接加在索引记录上,锁住的是key

在一个已经存在的key上加锁,加的是Record锁。

2.Gap锁(GK):只存在于RR

锁住key所在的一段范围,但不包括key本身。

Gap锁只存在于隔离级别RR,目的是防止同一事务两次读当前行时出现幻读。

等值范围的Gap锁是兼容的,不需要等待释放锁

隔离级别RR下,在一个不存在的key上加锁,加的是Gap锁。
比如我现在有(4,6,8,12)这几个id,数据库隔离级别是RR,在id = 9上加锁时,会加一个(8,12]的Gap锁,等于锁住了(10,11,12),另一个事务再给id = 10加锁就会报错;如果给id = 14加锁,锁住的是(12, + ∞);如果给2加锁,锁住的就是(- ∞, 4)。

显式关闭Gap锁的两种方式:

  1. 隔离级别改为RC/RU
  2. innodb_locks_unsafe_for_binlog设置为1/true/ON
3.NextKey锁(NK):Record锁 + Gap锁

一种特殊的会加NextKey锁的模式:

  1. 隔离级别RRInnoDB引擎
  2. innodb_locks_unsafe_for_binlog = false
  3. DELETE操作针对的是唯一索引上的等值查询,即索引为uniq(a,b,c)时,操作DELETE ... WHERE a = 'a' and b = 'b' and c = 'c';
  4. 如果数据是有效的,加的是X锁;如果数据已经被标记为删除,加的是NextKey锁

    InnoDB中删除一行不是直接物理删除,而是将数据标记为删除状态,后续会统一回收。

InnoDB对行的查询默认加的NextKey锁,当查询的索引上有唯一属性时,NextKey锁降级为Record锁

4.InsertIntention锁(IK):插入意向锁

如果插入前已经有Gap锁,那么INSERT操作会申请插入意向锁,并且阻塞。

锁的模式(X/S) & 与语句的关系

锁的模式可以理解为锁的力度

InnoDB的行锁是通过给索引列加锁来实现的。
只有通过索引检索数据,InnoDB才会用行级锁。
如果不走索引InnoDB将默认表级锁。

(1)行锁:必须有索引

如果一条SQL操作了主键索引,InnoDB会锁定这条主键索引
如果一条SQL操作了二级索引,InnoDB会先锁定该二级索引,然后再锁定相关的主键索引

1.共享锁(S锁):LOCK IN SHARE MODE

加了S锁的行,允许其他事务加S锁,不允许加X锁
即我读取的行,你可以读,但你不能改。

  • 普通的SELECT语句不会加锁,但是可以通过SELECT ... LOCK IN SHARE MODES锁
2.排他锁(X锁):UPDATE/DELETE/INSERT/FOR UPDATE

在任何事务隔离级别下,加了X锁的行,都不允许其他事务加任何锁
即我正在改的行,你不能读,更不能写。

  • 对于UPDATE/DELETE/INSERT这3个对行进行修改的操作,InnoDB会自动给涉及的行加X锁
  • 普通的SELECT语句不会加锁,但是可以通过SELECT ... FOR UPDATEX锁

    select * from table where id='xxx' for update;
    注意:id字段一定要是主键/唯一索引,否则表锁就会取代行锁

(2)表锁

1.意向共享锁(IS锁)

事务在加S行锁时,必须先获得表的IS锁。IS锁是InnoDB自动加的,不需要用户干预。

2.意向排他锁(IX锁)

事务在加X行锁时,必须先获得表的IX锁。IX锁是InnoDB自动加的,不需要用户干预。


(六)死锁

1.死锁产生场景

死锁的原因:两个或两个以上的进程因争夺资源造成的相互等待,没有外力作用它们都将无法推进。

表锁不会出现死锁
MyISAM引擎不会死锁,因为MyISAM总是一次性获取SQL语句需要的全部锁。
InnoDB的表锁也不会死锁。

死锁一般出现在InnoDB的行锁和页锁中。

以下是几种常见的死锁场景:

  1. 多个事务以不固定的顺序访问数据:比如事务A锁住id = 1,事务B锁住id = 2,此时事务A申请id = 2的锁,事务B申请id = 1的锁,就会产生死锁。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    # 解决方法:SELECT ... IN (id1, id2, ...) FOR UPDATE

    # Trx A:锁住8,9
    SELECT * FROM table WHERE id IN (8, 9) FOR UPDATE;

    # Trx B:尝试锁住10,8,2失败
    SELECT * FROM table WHERE id IN (10, 8, 2) FOR UPDATE;
    # 尝试失败,等待锁

    # 注意:IN的所顺序是根据id大小决定的!
    # 所以id = 2被锁住了,id = 10则没有锁住

    # Trx C
    SELECT * FROM table WHERE id = 2 FOR UPDATE; # 失败
    SELECT * FROM table WHERE id = 10 FOR UPDATE; # 成功
  2. 事务隔离级别默认RR时,会给不存在的主键加Gap锁,防止幻读。两个事务分别获取了等范围的Gap锁,然后申请对方持有的锁,就会死锁。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 隔离级别RR,id中10和12都不存在,会加gap锁
    # Trx A:Gap锁,锁住(- ∞, + ∞)
    SELECT * FROM table WHERE id = 10 FOR UPDATE;
    # Trx B:Gap锁,锁住(- ∞, + ∞),因为等范围所以不互斥
    SELECT * FROM table WHERE id = 12 FOR UPDATE;

    # Trx A:申请Trx B持有的Gap锁
    INSERT INTO table VALUES(10, 'A');
    # 等待锁,不会成功

    # Trx B:申请Trx A持有的Gap锁,死锁
    INSERT INTO table VALUES(12, 'B');
    # ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

解决方法:
使用INSERT INTO table VALUES(12, 'b') ON DULPLICATE KEY UPDATE id = 12;替代,因为INSERT加的是X锁,不存在Gap锁。

  1. 事务隔离级别默认RR时,innodb_locks_unsafe_for_binlog = falseDELETE操作针对的是唯一索引上的等值查询,即索引为uniq(a,b,c)时,作DELETE ... WHERE a = 'a' and b = 'b' and c = 'c';,如果数据存在,那么加的是X锁,如果数据已经标记为删除,那么加的是NextKey锁。多个事务操作就可能会发生死锁,具体如下:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # Trx A:数据存在,加X锁,删除
    DELETE ... WHERE a = 'a' and b = 'b' and c = 'c';

    # Trx B:在Trx A没有释放锁时操作,申请加X锁,等待Trx A释放X锁
    DELETE ... WHERE a = 'a' and b = 'b' and c = 'c';
    # Trx A释放锁后,数据标记为删除,Trx B不能再申请X锁了,所以它转而申请NextKey锁

    # Trx C:Trx B的NextKey锁没有申请下来,X锁没有释放掉时,Trx C为了删除数据,申请加NextKey锁
    DELETE ... WHERE a = 'a' and b = 'b' and c = 'c';
    # Trx B和Trx C循环等待对方的NextKey锁,死锁发生

2.排查死锁

查询数据库进程

SHOW FULL PROCESSLIST;
如果进程的State字段出现大量的Waiting for ... lock,即可判定死锁。

查看当前事务:INNODB_TRX

SELECT * FROM INFOMATION_SCHEMA.INNODB_TRX;

TRX:transaction 事务

查询结果中的trx_mysql_thread_id列就是事务的线程ID,直接kill掉死锁的事务就解除了死锁。

相关列信息:

  1. trx_id:InnoDB引擎内部唯一的事务ID。
  2. trx_state:事务状态,如果等待锁则为LOCK WAIT,可以通过INFORMATION_SCHEMA.INNODB_LOCK_WAITS
  3. trx_started:事务开始的时间
  4. trx_requested_lock_id:等待事务的锁ID,只有trx_stateLOCK WAIT才有值,否则为null
  5. trx_waited_started:事务等待开始的时间
  6. trx_weight:事务权重,反映了一个事务修改和锁住的行数。InnoDB中,如果死锁需要回滚,会选择事务权重最小的事务回滚。
  7. trx_mysql_thread_id:正在运行的MySQLid,即SHOW FULL PROCESSLISTthread_id
  8. trx_query:事务运行的SQL语句

查看已经锁定的事务:INNODB_LOCKS

SELECT * FROM INFOMATION_SCHEMA.INNODB_LOCKS;

相关列信息:

  1. lock_idlock_trx_id:lock_space:lock_page:lock_rec
  2. lock_trx_id:锁住的事务ID
  3. lock_mode:锁模式,排他锁(X)/共享锁(S)
  4. lock_type:锁类型,表锁/行锁
  5. lock_table:要加锁的表
  6. lock_index:锁索引
  7. lock_space:被锁住的InnoDB表空间的ID
  8. lock_page:锁住的页数,如果是行锁,则为null
  9. lock_rec:锁住的行数,如果是表锁,则为null
  10. lock_data:锁住的行的主键值,如果是表锁,则为null

查看等待锁的事务:INNODB_LOCK_WIATS

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

相关列信息:

  1. requestint_trx_id:申请锁资源的事务ID
  2. requesting_lock_id:申请的锁的ID
  3. blocking_trx_id:阻塞的事务ID
  4. blocking_lock_id:阻塞的锁的ID

确定最后一个死锁产生的原因

show engine innodb status\G

3.解除死锁

  1. kill进程
  2. 事务回滚:将死锁的事务回滚即可。如果死锁,InnoDB会自动回滚,选择持有最少行级排他锁(即trx_weight最小的事务)的事务回滚。

4.预防死锁

  1. 按同一顺序访问对象:比如都按照自增ID顺序加锁
  2. 避免事务中的用户交互,发生死锁了手动回滚事务
  3. 保持事务简短,并在一个批处理中:大事务尽量缩小,在同一个事务中,尽可能将所有需要的行一次性锁住,SELECT ... IN (id1, id2, ...) FOR UPDATE,不要锁住一行再去申请另一行
  4. 使用低隔离级别:默认的RR存在Gap锁,所以一般是RC版本号但是binlog日志格式必须设置为ROW,防止主从数据不一致
  5. 为表添加合理的索引:通过索引检索,将加行锁,如果不走索引,会加表锁,死锁概率大大提升。
  6. RR隔离级别下,UPDATE + INSERT的操作用INSERT INTO table ON DUPLICATE KEY UPDATE key = 'value'代替INSERT语句不管key是否存在加的都是X锁,不是Gap锁。

(七)SQL优化

INSERT优化:合并SQL & 顺序插入

1
2
INSERT INTO table VALUES(1);
INSERT INTO table VALUES(2);

优化成

1
2
3
4
5
INSERT INTO table VALUES (1), (2);

# 原因:
# 1.合并后的SQL降低了日志量,减少了日志刷盘的频率;同时减少了SQL解析次数,降低了网络传输的IO。
# 2.VALUES值尽可能按照主键顺序插入,可以减少维护索引的成本。

LIMIT语句优化:offset改为范围 & 索引

LIMIT语句格式如下:

1
2
3
# SELECT ... FROM table LIMIT size;
# SELECT ... FROM table LIMIT offset,size;
select * from table limit 100000, 99;

存在的问题

  1. LIMIT offset,size中,offset越大,SQL查询速度越慢;
  2. SELECT *不走索引,效率低下

优化方案

  1. LIMIT offset,size优化为范围查找,比如WHERE id > offset LIMIT size的形式
  2. SQL语句优化为走索引,如果SELECT条件中无法加入索引键,则在WHERE条件中加入
1
2
3
4
# LIMIT offset,size优化为范围查找
# 主键索引
select * from table where id >= (select id from table limit 100000,1) limit 99;
# 或者你可以使用IN/JOIN这样的连接查询,但是不推荐

使用JOIN替代子查询

子查询会建立临时表,效率不高,使用JOIN代替

在明显不会有重复值时使用 UNION ALL 而不是 UNION

UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
UNION ALL 不会再对结果集进行去重操作


如果不是必须要有NULL,否则所有SQL字段都建议使用NOT NULL

  1. 比起NOT NULL字段,NULL字段需要额外 1 bit 的存储空间,用于存储NULL标志位,不管该字段实际值是否为NULL

  2. NULL字段的索引也需要额外 1 bit 的存储空间

    但是NULL改为NOT NULL并不会明显优化索引性能,因为NULL列在索引树中是存在最左边的

  3. NULL字段在NOT IN!=count()中表现异常

1
2
3
4
5
6
7
8
9
10
11
12
# id NOT NULL, name NULL
# 1 张三
# 2 NULL

SELECT name FROM table WHERE name NOT IN( SELECT name FROM table WHERE id != 1);
# 返回NULL ? 不,无返回值

SELECT * FROM table WHERE name != '张三';
# 返回2 NULL ? 不,无返回值

SELECT count(name) FROM table;
# 返回2 ? 不,返回1,不计算NULL

有一点值得说明的是,以上问题只出现在编码为utf8的情况。
MySQL 5.6以后,默认编码变成了utf8mb4,可以识别更多编码,包括NULL,也就不会出现这样的问题了


(八)大表优化

读写分离

主库负责写,多个从库负责读。
读写分离的实现原理是主从复制,具体原理可以参考《MySQL主从复制》

主从同步存在延迟,如果数据要求强一致性,可以通过强制路由走写库
解决方案:在SQL中加入注释 —— /*master*/select ... from ... where ...;


常见方案

1.客户端代理(JDBC

  • 原理:分片逻辑封装在应用端的一个JAR包中,加入应用WAR包中,通过JDBC将读(DQL)和写(DML)分发到对应的数据库上。
  • 代表:sharding-jdbc/TDDL
  • 缺点:不能动态添加数据源(需要重启JVM);只支持Java语言
  • 优点:源程序不需要任何改动就可以实现读写分离;可以动态添加数据源,不需要重启程序

2.中间件代理(Proxy

  • 原理:应用服务器请求Proxy代理,Proxy代理再请求DB,核心是拦截与分发。
  • 代表:MyCat/mysql-proxy
  • 缺点:调用链变长,性能微损
  • 优点:源程序不需要任何改动就可以实现读写分离;可以动态添加数据源,不需要重启程序

水平拆分

将一个表按照Hash路由的方式拆分为多个分表,分布到不同的库上。

如果水平拆分后,表仍在同一个库上,其实并不能提高业务并发量


垂直拆分

将一个表按照业务拆分为多个子表。
数据要做冗余。


分库分表方案

1.hash取模

  • 优点:数据均匀分布,不会有热点问题
  • 缺点:将来如果扩容需要数据迁移时会很难

2.range范围

以范围进行拆分数据:事先将表的范围设计好,将一定范围的数据放到同一张表中。

  • 优点:有利于扩容 —— 如果需要扩容,只需要在现有范围后加一个range范围即可,不会对原有数据产生影响
  • 缺点:多表压力分布不均匀,单表压力过大 —— 当id在某一个range范围时,所有请求都只会落到单独一张表上,其他表不分摊压力

3.一致性hash

《一致性哈希》

-------------本文结束感谢您的阅读-------------

本文标题:MySQL基础

文章作者:DragonBaby308

发布时间:2019年07月23日 - 00:01

最后更新:2020年02月27日 - 22:28

原始链接:http://www.dragonbaby308.com/mysql/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

急事可以使用右下角的DaoVoice,我绑定了微信会立即回复,否则还是推荐Valine留言喔( ఠൠఠ )ノ
0%