Java面试宝典2020系列 MySQL篇（二）

可以按照锁的粒度把数据库锁分为表级锁和行级锁。

• 表级锁

Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

• 行级锁

Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种。

• Record Lock : 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
• Gap Lock : 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
• Next-key Lock ： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

虽然使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：

1. 事务更新大表中的大部分数据直接使用表级锁效率更高；
2. 事务比较复杂，使用行级索很可能引起死锁导致回滚。

1.2. 按照锁的粒度分类

表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（X）。

• 共享锁（s）

共享锁（Share Locks，简记为S）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。

共享锁(S锁)又称为读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

• 排他锁（X）

排它锁（(Exclusive lock,简记为X锁)）又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。

两者之间的区别：

1. 共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不 能加排他锁。获取共享锁的事务只能读数据，不能修改数据。

2. 排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获取排他锁的事务既能读数据，又能修改数据。

1.3. 两个表级锁：IS和IX

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。 而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁 。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

InnoDB另外的两个表级锁：

• 意向共享锁（IS）： 表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）： 表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

注意：

1. 这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。
2. IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。

InnoDB的锁机制兼容情况如下：

当一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之如果请求不兼容，则该事物就等待锁释放。

1.4. MVCC和事务的隔离级别

MVCC(Multi-Version Concurrency Control)多版本并发控制，可以简单地认为：MVCC就是行级锁的一个变种(升级版)。

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的， 锁的应用最终导致不同事务的隔离级别 。 事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节。 在表锁中我们读写是阻塞的，基于提升并发性能的考虑， MVCC一般读写是不阻塞的 (所以说MVCC很多情况下避免了加锁的操作)。

MVCC实现的读写不阻塞正如其名：

多版本并发控制--->通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。

快照有两个级别：

• 语句级 ：针对于Read committed隔离级别
• 事务级别 ：针对于Repeatable read隔离级别

1. Read uncommitted

Read uncommitted会出现的现象--->脏读：一个事务读取到另外一个事务未提交的数据

例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。

出现脏读的本质就是因为操作(修改)完该数据就立马释放掉锁，导致读的数据就变成了无用的或者是错误的数据。

2. Read committed

Read committed避免脏读的做法其实很简单：

就是把释放锁的位置调整到事务提交之后，此时在事务提交前，其他进程是无法对该行数据进行读取的，包括任何操作

但Read committed出现的现象--->不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改

注：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】

3. Repeatable read

Read committed是语句级别的快照！每次读取的都是当前最新的版本！

Repeatable read避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据。

1.5. 乐观锁和悲观锁

1. 乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。
2. 悲观锁是数据库层面加锁，都会阻塞去等待锁。

• 悲观锁

我们使用悲观锁的话其实很简单(手动加行锁就行了)：

select * from xxxx for update
复制代码

在select 语句后边加了 for update相当于加了排它锁(写锁)，加了写锁以后，其他的事务就不能对它修改了！需要等待当前事务修改完之后才可以修改。

也就是说，如果张三使用 select ... for update ，李四就无法对该条记录修改了。

• 乐观锁

乐观锁不是数据库层面上的锁，是需要自己手动去加的锁。一般我们添加一个版本字段来实现， 具体过程是这样的：

1. 张三 select * from table --->会查询出记录出来，同时会有一个version字段

2. 李四 select * from table --->会查询出记录出来，同时会有一个version字段

3. 李四对这条记录做修改： update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version} ，判断之前查询到的version与现在的数据的version进行比较，同时会更新version字段 此时数据库记录如下：

4. 张三也对这条记录修改： update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version} ，但失败了！因为当前数据库中的版本跟查询出来的版本不一致！

1.6. 死锁和避免死锁

并发的问题就少不了死锁，在MySQL中同样会存在死锁的问题。

但一般来说MySQL通过回滚帮我们解决了不少死锁的问题了，但 死锁是无法完全避免的 ，可以通过以下的经验参考，来尽可能少遇到死锁：

1. 以固定的顺序访问表和行 。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。
2. 大事务拆小 。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3. 在同一个事务中，尽可能 做到一次锁定 所需要的所有资源，减少死锁概率。
4. 降低隔离级别 。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5. 为表添加合理的索引 。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

2. MySQL大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。

一些常见的优化措施如下：

1. 单表优化

• 字段

1. 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
2. VARCHAR的长度只分配真正需要的空间
3. 使用枚举或整数代替字符串类型
4. 尽量使用TIMESTAMP而非DATETIME，
5. 单表不要有太多字段，建议在20以内
6. 避免使用NULL字段，很难查询优化且占用额外索引空间
7. 用整型来存IP

• 索引

1. 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
2. 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
3. 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
4. 字符字段只建前缀索引
5. 字符字段最好不要做主键
6. 不用外键，由程序保证约束
7. 尽量不用UNIQUE，由程序保证约束
8. 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

• 查询SQL

1. 可通过开启慢查询日志来找出较慢的SQL
2. 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
3. sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
4. 不用SELECT *
5. OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
6. 不用函数和触发器，在应用程序实现
7. 避免%xxx式查询
8. 少用JOIN
9. 使用同类型进行比较，比如用'123'和'123'比，123和123比
10. 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
11. 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
12. 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

2. 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

3. 垂直分区

根据数据库里面数据表的相关性进行拆分。

例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。 简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

• 垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
• 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

4. 水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

5. 系统参数调优

1. back_log ：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500
2. wait_timeout ：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时
3. max_user_connection : 最大连接数，默认为0无上限，最好设一个合理上限
4. thread_concurrency ：并发线程数，设为CPU核数的两倍
5. skip_name_resolve ：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
6. key_buffer_size ：索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like 'key_read%'，保证key_reads / key_read_requests在0.1%以下最好
7. innodb_buffer_pool_size ：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好
8. innodb_additional_mem_pool_size ：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小
9. innodb_log_buffer_size ：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
10. query_cache_size ：缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大. 可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小
11. read_buffer_size ：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
12. sort_buffer_size ：MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
13. read_rnd_buffer_size ：MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
14. record_buffer ：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值
15. thread_cache_size ：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
16. table_cache ：类似于thread_cache_size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM

3. 分库分表代理架构

• 客户端代理 ： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
• 中间件代理 ： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

如此多的方案，如何进行选择？可以按以下思路来考虑：

1. 确定是使用代理架构还是客户端架构。中小型规模或是比较简单的场景倾向于选择客户端架构，复杂场景或大规模系统倾向选择代理架构
2. 具体功能是否满足，比如需要跨节点ORDER BY，那么支持该功能的优先考虑
3. 不考虑一年内没有更新的产品，说明开发停滞，甚至无人维护和技术支持
4. 最好按大公司->社区->小公司->个人这样的出品方顺序来选择
5. 选择口碑较好的，比如github星数、使用者数量质量和使用者反馈
6. 开源的优先，往往项目有特殊需求可能需要改动源代码

按照上述思路，推荐以下选择：

• 客户端架构：ShardingJDBC
• 代理架构：MyCat或者Atlas