黑马程序员技术交流社区

标题: 【广州python】mysql 锁、数据库优化 [打印本页]

---

作者: 唐伯虎（0）    时间: 2019-7-25 15:01
标题: 【广州python】mysql 锁、数据库优化
一、锁1.1锁分类

• 按操作分
  • 读锁（共享锁）
  • 写锁（排他锁）
    
    
• 按粒度分
  • 表锁
  • 行锁
  • 页锁
    
    
  
  

2.2表锁

• 偏向MyISAM存储引擎,开销小, 加锁快, 无死锁,锁定粒度大, 发生锁冲突的概率最高, 并发最底,整张表就只能一个人使用
• 查看表锁show open tables;
• 对表加锁lock table user read, user write;
• 对表解锁unlock tables
• 查询表锁查询、等待次数show status like 'table%';
  • Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数
  • Table_locks_waited:出现表级锁定争用而发生等待的次数
    
    
  
  

1.3行锁

• 偏向InnoDB存储引擎,开销大, 加锁慢, 会出现死锁;锁定粒度最小, 发生锁冲突的概率最底,并发度也最高。
  
  

1.4事务

• 事务是一批操作，要么同时成功，要么同时失败!
  • start transaction开启事务
  • commit提交事务
  • rollback回滚事务
  • savepoint 名字设置回滚点
  • rollback to 名字回到回滚点
    
    
• 取消自动提交
  • SHOW VARIABLES LIKE '%commit%';
  • SELECT @@autocommit;
  • SET autocommit = 0;
    
    
• 事务四大特性(ACID)
  • 原子性
  • 一致性
  • 隔离性
  • 持久性
    
    
• 四种隔离级别和可能出现的问题
  • 隔离级别
    • 读未提交(read uncommitted)：所有的事务都可以读到其他事务未提交的执行结果。容易出现脏读。
    • 读已提交(read committed)：一个事务只能读到已经提交事务的执行结果。容易出现不可重复读(虚读)。
    • 可重复读(repeatable read)：MySQL默认。一个事务前后几次，会得到同样的结果。容易出现幻读。
    • 可串行化(serializable)：最高隔离级别。每个读操作上共享锁，写操作上排他锁，容易出现超时，阻塞现象。
      
      
  • 脏读、不可重复读(虚读)、幻读
    • 脏读：一个事务读到其他事务未提交的执行结果。
    • 不可重复读（虚读）：同一个事务前后相同的查询语句所读取的结果不同
    • 幻读：幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
      
      
    
    
• 手动加行级锁
  • 在查询之后添加for update，其它操作会被阻塞,直到锁定的行提交commit;
  • show status like 'innodb_row_lock%';查看行锁的使用信息
    
    
  
  

1.5悲观锁、乐观锁

• 悲观锁
  • 定义：就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改。整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制。事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁。
    
    
• 乐观锁
  • 定义：顾名思义，就是很乐观，每次自己操作数据的时候认为没有人回来修改它，所以不去加锁。但是在更新的时候会去判断在此期间数据有没有被修改。需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。
  • 实现方式
    • 版本号
      • 就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段每次更新把这个字段加1
      • 读取数据的时候把version读出来，更新的时候比较version
      • 如果还是开始读取的version就可以更新了
      • 如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到
        一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。
        
        
    • 时间戳
      • 原理和版本号相同，只是标识的字段不一样。
        
        
      
      
    
    
• 悲观锁,乐观锁使用前提
  • 对于读操作远多于写操作的时候，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁
  • 如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。
    
    
  
  

二、数据库优化2.1MySQL优化综合性技术

• 表的设计合理化（符合3NF，有时也要进行反三范式操作）
• 添加适当索引
• 分表技术（水平分割、垂直分割）
• 主从复制，读写分离
• 存储过程（模块化编程，可以提高速度）
• 对MySQL配置优化（配置最大并发数my.ini，调整缓存大小）
• 系统应用优化等
• 服务器的硬件优化
  
  

2.2索引分类

• 单值索引
  • 一个索引只包含单个列,一个表可以有多个单值索引,一般来说, 一个表建立索引不要超过5个
    
    
• 唯一索引
  • 索引列的值必须唯一,但允许有空值
    
    
• 复合索引
  • 一个索引包含多个列
    
    
• 全文索引
  • MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。
    
    
  
  

2.3MySQL语句正确使用索引

• 全值匹配（最好）
  • 如：建立了三个索引，查询时最好都使用上三个索引
    
    
• 最佳左前缀法则
  • 如果索引有多列,要遵守最左前缀法则,指的就是从索引的最左列开始 并且不跳过索引中的列
    
    
• 计算、函数、类型转换会导致索引失效
• 范围条件查询，右边索引失效
• 使用不等于(!=或者<>)时无法使用索引
• or引起索引失效
• like引起索引失效
  
  

2.4大批量数据分页操作优化

• 没有优化的查询
  • SELECT * FROM logs1 LIMIT 500000,10;
    
    
• 优化
  • 使用子查询优化方式1
    select * from logs1 e inner join (SELECT id from logs1 limit 500000 ,10 ) et on e.id = et.id
  • 使用子查询优化方式2
    select * from logs1 where id >=(SELECT id from logs1 limit 500000 , 1) limit 10
  • 使用id限定优化
    • 记录上一页最大的id号 使用范围查询,限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利
      
      
    
    
  
  

---

作者: 灵儿啊    时间: 2019-7-26 09:05
好好像要啊

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2