mysql 锁、数据库优化【黑马python培训】

更新时间:2019年07月26日 10时48分17秒来源:黑马程序员论坛

一、锁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字段，这为分页查询带来很多便利

推荐了解热门学科

java培训	Python人工智能	Web前端培训	PHP培训
区块链培训	影视制作培训	C++培训	产品经理培训
UI设计培训	新媒体培训	产品经理培训	Linux运维
大数据培训	智能机器人软件开发

传智播客是一家致力于培养高素质软件开发人才的科技公司，“黑马程序员”是传智播客旗下高端IT教育品牌。自“黑马程序员”成立以来，教学研发团队一直致力于打造精品课程资源，不断在产、学、研3个层面创新自己的执教理念与教学方针，并集中“黑马程序员”的优势力量，针对性地出版了计算机系列教材50多册，制作教学视频数+套，发表各类技术文章数百篇。

传智播客从未停止思考

传智播客副总裁毕向东在2019IT培训行业变革大会提到，“传智播客意识到企业的用人需求已经从初级程序员升级到中高级程序员，具备多领域、多行业项目经验的人才成为企业用人的首选。”

中级程序员和初级程序员的差别在哪里?
项目经验。毕向东表示，“中级程序员和初级程序员最大的差别在于中级程序员比初级程序员多了三四年的工作经验，从而多出了更多的项目经验。“为此，传智播客研究院引进曾在知名IT企业如阿里、IBM就职的高级技术专家，集中研发面向中高级程序员的课程，用以满足企业用人需求，尽快补全IT行业所需的人才缺口。

何为中高级程序员课程?

传智播客进行了定义。中高级程序员课程，是在当前主流的初级程序员课程的基础上，增加多领域多行业的含金量项目，从技术的广度和深度上进行拓展。“我们希望用5年的时间，打造上百个高含金量的项目，覆盖主流的32个行业。”传智播客课程研发总监于洋表示。

黑马程序员热门视频教程【点击播放】

Python入门教程完整版（懂中文就能学会）	零起点打开Java世界的大门
C++\| 匠心之作从0到1入门学编程	PHP\|零基础入门开发者编程核心技术
Web前端入门教程_Web前端html+css+JavaScript	软件测试入门到精通

全国中心

mysql 锁、数据库优化【黑马python培训】

相关阅读