MySql 知识点之事务、索引、锁原理与用法解析

数据库  /  管理员 发布于 6年前   193

本文实例讲述了MySql 知识点之事务、索引、锁原理与用法。分享给大家供大家参考，具体如下：

事务

• 事务概念

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎执行一组操作语句，那么久执行所有的操作，如果其中有任何一条崩溃或其他原因无法执行，所有语句将不会执行。也就是说事务内的语句，要么全部执行成功，要么全部执行失败。

• 事务特性ACID
  • 原子性(atomicity)

  一个事务被视为最小工作单元，不可拆分，整个事务所有的操作要么全部提交成功，要么全部失败回滚，不可只执行部分。

  • 一致性(consistency)

  数据库从一个一致性的状态转换到另外一个一致性的状态。数据库某个状态下符合所有的完整性约束的状态。

  • 隔离性(isolation)

  通常来说，一个事务所做的修改在最终提交前，对其他事务是不可见的。此时应该保证各个事务要进行隔离，事务之间不可相互干扰。

  • 持久性(durability)

  一旦事务提交，所有的修改会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

• 事务的隔离级别
  • READ UNCOMMITTED(未提交读)

  事务中的修改，即使没有提交，对其他事务也是可见的，事务可以读取未提交的数据，造成脏读，也会造成不可重复。

  • READ COMMITTED(提交读)

  大多数数据库的默认级别是READ COMMITTED（MySQL默认REPEATABLE READ），该级别事务解决了脏读，但是会出现不可重复读，因为两次执行同样的查询，查询结果不一样。

  • REPEATABLE READ(可重复读)

  该级别解决了脏读，保证可重复读，但是理论上，可重复读隔离级别还是无法解决幻读，所谓幻读，指的是党某个事物在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。InnoDB和XtraDB存储引擎通过多版本并发控制MVVC解决了幻读的问题。

  • SERIALIZABLE(可串行化)

  可串行化是隔离最高级，它强制了事务串行执行，完全避免了幻读，简单来说SERIALIZABLE会在读取的每一行加锁，所以会导致大量的等待超时和锁争用的问题，实际开发中很少使用。

索引

• 索引概念

索引是存储引擎用户快速找到记录的一种数据结构，举例

SELECT userName FROM user WHERE userId = 1;

如果在userId列上加上索引，则MySQL将使用该索引找到userId的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

• 索引方式
  • B-Tree索引

  使用B-Tree数据结构来存储数据，大多MySQL引擎都支持该索引。B-Tree索引可以加快访问数据的速度，因为B-Tree对索引列顺序组织存储，范围查找快。

  • hash索引

  哈希索引基本哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码值较小。哈希索引将所有的哈希码存储在索引中，同时在哈斯表中保存指向每个数据行的指针。MySQL中只有Memory引擎显示支持哈希索引。

• 索引类型
  • 普通索引

  主要任务加快对数据的访问

  • 唯一索引

  普通索引是允许数据重复的，如果确定了某列数据不会重复，则可创建唯一索引，唯一索引有两个好处，索引更有效：插入新数据，如果重复，MySQL拒绝插入。

  • 主键索引

  主键本身默认创建索引

  • 全文索引

  文本字段上的普通索引只能加快对出现在字段内最前面的字符串进行的检索操作，如果字段里存放的是由几个或者多个单词构成的大段文字，普通索引就不行了，这种场合用全文索引比较合适

  查询效率：唯一索引>自增主键>主键

  插入：主键>自增主键>唯一索引

锁

在这里我们主要讨论下行级锁

• 表级

引擎MyISAM，可以理解为锁整张表，可以同时读，不可以同时写。在锁定期间，其它进程无法对该表进行写操作，如果是写锁，则其它进程则不允许读。

• 行级

引擎INNODB，单独一行记录加锁，可以同时读，不可同时写。行级锁开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• InnoDB锁行

由于InnoDB预设是Row-Level Lock，所以只有[明确]的指定主键，MySQL才会执行Row lock，否则MySQL将会执行Table Lock

例1：（明确指定主键，并且有此记录，Row Lock）

SELECT * FROM products WHERE id='3' FOR UPDATE;SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主键，若查无此记录，无lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主键不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。