mysql基础（十三）mysql并发参数与锁

文章目录1 应用优化2 mysql并发参数调整2.1 max_connections2.2 back_log2.3 table_open_cache2.4 thread_cache_size2.5 Innodb_lock_wait_timeout3 MySQL锁3.1 引擎与锁分类3.2 MyISAM表锁3.3 InnoDB行锁3.4 InnoDB表锁3.5 间隙锁Gap Lock与Next-Key锁3.6 查看锁情况1 应用优化1.使用连接池不多说。2.减少对数据库访问三种方式①.少次——能够一次完成的就不要分两次。②.缓存——使用缓存减少对数据库访问。③.负载均衡——可通过主从复制主机增删改从机查询实现读写分离降低单台压力或者使用分布式架构。2 mysql并发参数调整2.1 max_connections此参数控制允许连接到MySql的最大数量默认是151。当状态变量connection_errors_max_connections不为0且一直增长则说明不断有请求因连接数达到最大值而连接失败此时可以考虑增大max_connections。2.2 back_log此参数控制MySQL监听TCP端口时设置的积压请求栈大小也就是连接数量已经达到最大的时候排队的请求数量。如果排队的请求也大到最大排队量back_log会将拒绝排队请求直接报错。如果要在短时间内处理大量连接请求可以考虑增大可排队值back_log。2.3 table_open_cache此参数用来控制所有SQL语句执行线程可打开表缓存的数量在执行语句时每一个线程最少要打开一个表缓存。该参数的值应该根据设置的最大连接数Max_connections以及每个连接执行关联查询中涉及表的最大数量而定。2.4 thread_cache_size此参数控制MySQL缓存客户服务线程的数量也就是已断开客户端对应服务线程的复用缓存用来加快连接速度。线程缓存有空闲线程则直接复用没有则创建新线程。2.5 Innodb_lock_wait_timeout此参数调用InnoDB事务的行锁等待时间。如果要加快响应速度可以调小避免事务长时间挂起。而对于后台运行的批量处理程序可以将行锁的等待时间调大避免发生大的回滚。3 MySQL锁3.1 引擎与锁分类从数据操作粒度来分有行锁和表锁——行锁锁定当前行表锁锁定整个表。从数据操作类型来分有读锁和写锁——读锁针对同一份数据同时进行多个操作互不影响也叫共享锁。写锁在当前操作没完成之间会阻塞其它任何操作也称排它锁。3.2 MyISAM表锁此引擎会自动加读写锁也可以手动加。准备两张myisam存储引擎表总之读锁只阻塞写写锁阻塞读与写。所以MyISAM只适合用来读操作。3.3 InnoDB行锁行锁开销大加锁慢会出现死锁但是锁定粒度小锁冲突概率低并发度高。InnoDB正是因为有了行锁才能支持事务。我们先关闭全局自动提交重启后失效要永久有效须在配置文件中更改-- 关闭当话自动提交,set后缺省值为session。只对本会话有效setautocommit0;-- 关闭全局自提交,对当前对话无效setGLOBALautocommit0;-- 查看会话自动提交设置缺省值为sessionshowVARIABLESLIKEautocommit;-- 查看全局自动提交设置showglobalVARIABLESLIKEautocommit;3.4 InnoDB表锁如果不通过条件检索数据那么InnoDB会进行全表扫描并对扫描到的每一行都加上行锁实际效果等同表锁。3.5 间隙锁Gap Lock与Next-Key锁REPEATABLE READ隔离级别下InnoDB为了解决幻读问题引入了间隙锁。间隙锁是对索引记录之间“间隙”GAP的锁也包括第一条索引记录前或最后一条记录后的间隙。它的唯一目的是阻止其他事务向该间隙插入新记录。需要特别注意的是间隙锁并不仅限于范围查询。在对非唯一索引进行等值查询时同样可能产生间隙锁。Next-Key锁是InnoDB的默认行锁算法它是 “记录锁 该记录前面的间隙锁” 的组合。在执行锁定读如SELECT … FOR UPDATE或UPDATE、DELETE语句时InnoDB默认会在扫描到的索引记录上使用Next-Key锁。简单来说间隙锁是锁住一个“范围”而 Next-Key锁 是“记录 它前面的间隙”是组合锁。3.6 查看锁情况行锁争用情况总结1.尽量走索引InnoDB 不会因为行锁过多自动“升级”为表锁但无合适索引导致全表扫描时可能锁住大量甚至全部记录实际表现类似整表被阻塞。2.合理设计索引缩小锁的范围。3.尽量减少索引条件与匹配范围避免间隙锁。4.控制事务大小减少锁定资源量和时长。5.在不和业务需求冲突的情况下尽量使用低级别隔离。