由innodb锁引起的数据库相关

innodb 锁的问题

1、事务

  • 原子性：要么成功，要么失败
  • 一致性：前后数据保持一致状态
  • 隔离性：多个事务并行，相互不影响
  • 持久性：事务提交之后，对数据的影响是永久性的，即使故障也可以保持。

2、并发带来的问题：隔离级别

  • 脏读：读未提交（read uncommitted）。事务读取了其他事务还没有提交的事务。

  • 不可重复读：non-repeatable read。两次读取的数据不一致，有些数据被修改或删除了。

  • 幻读：Phantom read。两次读取数据不一致，有多的数据。像产生幻觉一样。

  • 更新丢失：两个事务同时对一条数据进行修改。导致先前提交的修改不可见。需要加锁，使不可见。

    事务隔离方式分类：
    - 读取数据之前加锁，防止其他事务进行修改
    - 不用加锁，使用特定的方式生成数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。从使用者的角度看，数据库好像有多个版本一样。这种技术叫做：数据的多版本并发控制Multi-version concurrency control。

    数据的隔离级别越严格，并发副作用越小，同时付出的代价更大。因为隔离在一定程度上是对数据进行串行化，这显然是与并发矛盾的。
    同时，不同的应用对隔离程度要求不同，有些应用对幻读和不可重复读要求并不敏感，更关心并发访问能力。

3、行锁类型

  • 共享锁 S：允许事务进行读取，阻止其他事务获取该数据集的排他锁。
    语法：Lock in share mode

  • 排他锁 X：允许获取排他锁的事务更新数据，阻止其他事务获取该数据集的共享读锁和排他写锁。
    语法：for update

    为了表锁和行锁并存的情况，实现多粒度锁机制，还有两种innodb 内部使用的锁 Intention Locks，这两种锁都是表锁

    • 意向共享锁 IS：事务打算给记录加共享锁，在加共享锁之前，必须先取得该表的意向共享锁。
    • 意向排他锁 IX：事务打算给记录加排他锁，在加排他锁之前，必须先取得该表的意向排他锁。

    兼容模式：只有 S 和 S 兼容；意向锁之间全部兼容。S 和 IS 兼容。
    如果一个事务的锁请求模式 和 当前锁兼容，Innodb 就将请求的锁授予该事物，否则 等待锁释放。
    <----
    请求锁模式
    当前锁模式 X IX S IS
    X 冲突 冲突 冲突 冲突
    IX 冲突 兼容 冲突 兼容
    S 冲突 冲突 兼容 兼容
    IS 冲突 兼容 兼容 兼容
    ---->

4、innodb 锁实现方式

​ innodb 行锁是通过给索引上的索引项加锁实现，这一点和oracle 不同，后者是通过对数据块中的数据行进行加锁，来实现。
​ innodb 这种加锁模式实现特点意味着：只有通过索引条件检索数据，innodb 才会使用行锁，否则将使用表锁。

在实际的使用中，需要特别注意锁使用，不然的话，会导致大量的所冲突，从而影响并发性能。

a、不加索引使用表锁；
b、由于是在索引项上加锁的方式，如果使用同样的索引键，虽然查询行不同，也会使用表锁；
c、当表有多个索引时，不同的事务使用不同的索引可以锁定不同的行。不区分 主键索引，普通索引和唯一索引；
d、是否使用索引时mysql的执行计划确定，如果没有使用索引，记得查看执行计划，是否使用到了索引；

  • 间隙锁（Next-key）
    当我们使用的是范围查询时，会对不存在的记录id加锁，这种成为间隙锁。
    目的：

    • 避免幻读。
    • 为了满足 恢复 和 复制 的需要。

    恢复和复制的需要，对InnoDB锁机制的影响：MySQL通过BINLOG录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。MySQL的恢复机制（复制其实就是在Slave Mysql不断做基于BINLOG的恢复）有以下特点。
    - 一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。这与Oracle数据库不同，Oracle是基于数据库文件块的。
    - 二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。这点也与Oralce不同，Oracle是按照系统更新号（System Change Number，SCN）来恢复数据的，每个事务开始时，Oracle都会分配一个全局唯一的SCN，SCN的顺序与事务开始的时间顺序是一致的。

      从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的，有关InnoDB在不同隔离级别下加锁的差异在下一小节还会介绍。

5、不确定性sql

使用 insert ... select ... 和 create table ... select ... ，可能会阻止对源表的并发更新操作，造成对源表锁 等待。如果查询比较复杂，会出现性能问题，不推荐使用

原因：mysql 的 binlog 恢复机制是按照事务提交的先后顺序记录。如果并发插入会导致数据的不可确定性， 需要保证操作的原子性。

6、行锁和表锁的选择

- 更新大部分数据时，如果使用行锁，会造成执行效率低，而且会让其他事务造成锁等待，和锁冲突；此时可以考虑使用表锁。
- 事务涉及多个表，比较负责，可能产生死锁，如果回滚会造成多个表的回滚。此时可以考虑一次性锁定所有表，避免死锁以及回滚带来的开销。

在InnoDB下，使用表锁要注意以下两点。
（1）使用LOCK TABLES 虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的， 而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时， InnoDB层才能知道MySQL加的表锁， MySQL Server也才能感知InnoDB加的行锁， 这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。有关死锁，下一小节还会继续讨论。
（2）在用 LOCK TABLES对InnoDB表加锁时要注意， 要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前， 不要用UNLOCK  TABLES释放表锁， 因为 ** UNLOCK TABLES ** 会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

7、死锁

​ 产生：两个事务需要获取另一个事务的排他锁，出现互相等待的情况。
​ 产生死锁后，Innodb 一般能自己检测到，让一个事务释放锁并回退，另一个事务获得锁继续执行事务。但在涉及外部锁 或 表锁的情况下，并不能自己检测到。需要通过设置锁等待超时时间来自动结束事务，并回滚。同时，这个参数在高并发，锁等待严重的情况下，造成多事务挂起，耗尽计算机资源，拖垮数据库时也会产生作用，避免此种情况发生。

避免方法：
- 多个事务对多表进行操作的时候，约定保持访问顺序的一致性。
- 批量处理时，先排序，按照顺序去处理数据。
- 更新记录时，应申请足够级别的锁，不能需要的时候再申请。
- 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题.
- 当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。

PS：SQl 优化 和设计 会大大减少死锁的发生，但是仍然不可避免，因此在程序中习惯性的捕获死锁并处理是一个好习惯。

8、sql 执行计划

  • id，由数字组成，表示执行过程的先后顺序：

    • id越大，越优先执行；
    • id相等，由上至下执行。
    • id为null时表示一个结果集，不需要使用它查询。常出现在union语句中。

  • select_type ，每个子查询的类型

    • id=1 simple，不包含任何子查询或union等查询
    • id=2 primary，包含子查询，最外层的查询就是primary
    • id=3 subquery，在select或where中的查询
    • id=4 derived，from 子句中的查询
    • id=5 union ，union 后出现的 查询
    • id=6 union result， 从 union 中获取结果集
      table 查询的数据表，从衍生表中查询数据时会显示x 查询子句的id

  • partitions 表分区，表建立的时候可以指定通过那个列进行分区。

  • type 非常重要，可以看出是否走索引查询

    • all 全表扫描
    • index 遍历索引
    • range 索引范围查找
    • index_subquery 在子查询中使用ref
    • unique_subquery 子查询中使用eq_ref
    • ref_or_null 对null进行优化的ref
    • fulltext 全文索引
    • ref 使用非唯一索引进行查找
    • eq_ref 在join 中使用primary key 或 unique not null 进行索引关联

  • possible_keys 可能使用的索引，不一定一定使用。为null 考虑是否需要优化。

  • key 显示实际执行过程中使用的索引，若没有null

TIPS:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

  • key_length 索引长度

  • ref 表示上述表使用的连接条件，即哪些列会被用于查找索引上的值

  • rows 返回估算的结果集数目，不是一个准确的数值

  • extra 信息丰富，一般有如下几种
    Using index 使用覆盖索引
    Using where 使用了用where子句来过滤结果集
    Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
    Using temporary 使用了临时表
    sql优化的目标可以参考阿里开发手册

    ❗️❗️❗️❗️ 索引type 至少是range级别，要求是ref。

9、数据库拆分

  • 垂直拆分：拆分单表字段，最好分为两台服务器，避免单库的处理能力成为瓶颈。
    数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

  • 水平拆分
    针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。

      水平分库分表
  将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
  水平分库分表切分规则
  - RANGE
  	从0到10000一个表，10001到20000一个表；
  - HASH取模
  	一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
  - 地理区域
  	比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
  - 时间
  	按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

10、分库分表后的问题

  • 事务支持
    分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

  • 跨库join
    只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

  • 跨节点的count,order by,group by以及聚合函数问题
    这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

  • 数据迁移，容量规划，扩容等问题
    来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。

  • 主键生成策略
    UUID
    使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。
    Twitter的分布式自增ID算法Snowflake
    在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

补充：mysql中in 和exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

posted @ 2019-02-25 14:51  爬行  阅读(198)  评论(0)  编辑  收藏  举报