Advance Java 第八周作业

题目01-MySQL的锁机制

1、按照锁的粒度，锁的功能来分析

（1）锁粒度

[1]全局锁：锁整个Database，由MySQL的SQL Layer层实现；

[2]表级锁：锁某个Table，由MySQL的SQL Layer层实现;

[3]行级锁：锁某Row的索引，或者，锁行索引之间的间隙，由存储引擎实现InnoDB

（2）锁功能

[1]共享锁Shared Locks（S锁，读锁）：

加了读锁的记录，可以允许其他事务继续加读锁，但是不允许其他事务加写锁。

读锁不排斥读锁，排斥写锁

加锁方式：select ... lock in share mode

[2]排他锁Exclusive Locks（X锁，写锁）：

写锁排斥写锁和读锁

加锁方式：增删改SQL语句，select ... for update

2、什么是死锁，为什么会发生，如何排查？

（1）死锁：两个事务互相持有对方想要获取的锁，互相等待对方释放锁。

（2）原因：两个事务的加锁顺序不一致。

（3）排查手段：

SQL语句查看最近一次死锁日志： SHOW ENGINE INNODB STATUS;

LATEST DETECTED DEADLOCK部分可以看到最近一次死锁的详细情况

3、行锁是通过加在什么上完成的锁定？

InnoDB的行锁是通过给索引上面的索引项加锁来实现的。

InnoDB的行级锁：

（1）记录锁（Record Locks)：锁定索引中的一条记录

（2）间隙锁（Gap Locks)：锁定索引记录的间隙（没找到目标值，所以锁住目标值的范围）

（3）临键锁（Next-Key Locks）：索引记录的记录锁 + 间隙锁

（4）插入意向锁（Insert Intention Locks）：做insert操作时添加的对记录id的锁。

同一个间隙，可以允许多个不同的插入意向锁，但是不能允许多个间隙锁。

插入意向锁时对间隙锁的优化。

4、详细说说这条 SQL 的锁定情况：`delete from tt where uid = 666`;

（1）uid是主键

[1] uid = 666 如果命中，锁住主键索引uid=666这条记录；主键的记录锁；

[2] uid = 666 如果没有命中，锁住uid=666所在的主键索引的间隙；主键的间隙锁；

（2）uid是唯一性辅助索引

[1] uid = 666 如果命中，锁住uid=666对应的主键索引记录，主键的记录锁

锁住辅助索引uid=666的记录，  辅助索引的记录锁

[2] uid=666 如果没有命中，锁住辅助索引uid=666的对应的辅助索引的间隙，间隙锁

（3）uid是非唯一性辅助索引

[1] uid = 666 如果命中，锁住uid=666对应的主键索引记录（可能多个），主键的记录锁

锁住辅助索引uid=666的记录，  辅助索引的记录锁

锁住辅助索引uid=666两边的间隙。辅助索引的间隙锁

[2] uid=666 如果没有命中，锁住辅助索引uid=666对应的辅助索引的间隙。辅助索引的间隙锁

（4）uid不是索引

锁的是整张表

 

题目 02-  MySQL 的 SQL 优化

MySQL的数据库调优：

1、调SQL语句：

工具：

（1）慢查询日志：

慢查询日志：set global slow_query_log=on;

通过慢查询日志找到执行超时的SQL语句

（2）Explain执行计划

通过explain工具，查看超时SQL的执行计划，分析SQL超时的原因，进行优化。

优化方式：合理利用索引：

[1] 对where语句，order语句，group by语句的常用字段使用索引

[2] 对于select语句中的字段，结合where语句的字段，使用组合索引

[3] 对于join... on ...两边的字段使用索引

然后再通过explain工具进行验证

（3）show profile工具

如果通过explain工具无法分析出原因，再用show profile从CPU，IO等方面分析超时原因。

2、调整数据库表结构

（1）分表：将字段很多的表分解为多个表

（2）添加中间表

（3）添加冗余字段

3、调MySQL的配置：

（1）缓冲区innodb_buffer_pool_size：总物理内存的50%~80%。

（2）日志文件nnodb_log_file_size=48

（3） 将Redo日志刷新到磁盘：innodb_flush_log_at_trx_commit=1

（4）事务同步到磁盘的次数sync_binlog=1

（5）脏页占innodb_max_dirty_pages_pct = 30。

（6）后台进程最大IO性能指标。innodb_io_capacity=200 

（7）慢查询日志的阈值设置，单位秒。 long_qurey_time=3 

（8）row binlog_format=row 

（9）同时连接客户端的最大数量 max_connections=200 

（10）全量日志建议关闭 general_log=0

4、MySQL客户端：MySQL客户端连接池的参数：

（1）客户端连接池最大活跃连接数：maxActive = 20

（2）客户端连接池最大获取连接等待时间：maxWait = 10000

（3）JDBC连接池配置参数：

jdbc:mysql://172.26.233.200:3306/hero_all? serverTimezone=UTC&characterEncoding=utf8&connectionTimeout=3000&socketTimeout=1200