Skip to main content

MySQL

数据库的事务​

事务： 多条sql语句，要么全部成功，要么全部失败。

事务的特性： 数据库事务特性：原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性 (Durabiliy)。简称ACID。

  • 原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功， 整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。
  • 一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。 如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。
  • 隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰
  • 持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

索引是什么​

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
  • 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

使用 MySQL 的索引应该注意些什么？​

下述操作会导致引擎放弃使用索引而进行全表扫描:

  • 在WHERE子句中使用!=或<>操作符，优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行。
  • 在WHERE子句中使用OR来连接条件，如：SELECT id FROM t WHERE num = 10 OR num = 20。
  • 在WHERE子句中对字段进行表达式操作或者函数操作
  • MySQL 评估使用索引比全表扫描更慢

避免出现索引失效：

  • 不要在WHERE子句中的=左边进行函数、算术运算或其他表达式运算，如：SELECT id FROM t WHERE num/2 = 5
  • 复合索引遵循最左前缀原则
  • 列类型是字符串类型，在查询时要给值加引号
  • LIKE查询，%不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引
  • 表字段为NULL也是不可以使用索引的
  • 字段是字符串类型的使用的时候，必须加引号

SQL优化手段有哪些​

  1. 查询语句中不要使用 select * , 避免全表扫描，应具体指定字段
  2. 尽量减少子查询，使用关联查询（left join,right join,inner join）替代
  3. 减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
  4. or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时， union all会更好)
  5. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎会放弃使用索引而进行全表扫描。
  6. 应尽量避免在 where 子句中对字段进行 null 值判断，否则引擎会放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有 null 值，然后这样查询： select id from t where num=0

事务​

事务隔离级别有哪些?MySQL的默认隔离级别是?​

SQL 标准定义了四个隔离级别：

  1. READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  2. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身 事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读 以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通 过SELECT @@tx_isolation; 命令来查看

需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLEREAD（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEAaTABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

MySQL事务的ACID是怎么保证的，或者说是怎么实现的​

MySQL事务的ACID，其中（C）一致性是最终目的。 保证一致性的措施有：

  • A原子性：靠undo log来保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功是sql。
  • D持久性：靠redo log来保证，MySQL修改数据的时候会在redo log中记录一份日志数据，就算数据没有保存成功，只要日志保存成功了，数据仍然不会丢失。
  • I隔离性：靠数据库的锁，加上MVCC实现的。
  • C一致性：一致性是由其他三大特征保证，同时程序代码要保证业务上的一致性。既需要数据库层面保证，又需要应用层面进行保证，并且MySQL底层通过两阶段提交事务保证了事务持久化时的一致性。

说说什么是 MVCC？​

多版本并发控制（MVCC=Multi-Version Concurrency Control），是一种用来解决读 - 写冲突的无锁并发控制。也就是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

MVCC 可以为数据库解决什么问题？​

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

说说 MVCC 的实现原理​

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3 个隐式字段、undo 日志、Read View 来实现的。

锁​

请说说 MySQL 数据库的锁？​

MySQL 中有共享锁和排它锁，也就是读锁和写锁。

  1. 共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。
  2. 排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。
  3. 表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。
  4. 行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现，否则会自动锁全表，那么就不是行锁了）。

说说什么是锁升级？​

  • MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index 和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。
  • 当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的 内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁。 where查询符合条件的数据多，每次索引查询之后又需要通过索引查询数据。

说说悲观锁和乐观锁​

  • 悲观锁 说的是数据库被外界（包括本系统当前的其他事物以及来自外部系统的事务处理）修改保持着保守态度，因此在整个数据修改过程中，将数据处于锁状态。悲观的实现往往是依靠数据库提供的锁机制，也只有数据库层面提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统汇总实现了加锁机制，也是没有办法保证系统不会修改数据。 在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务 无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。
  • 乐观锁 相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。 而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

怎样尽量避免死锁的出现？​

  1. 设置获取锁的超时时间，至少能保证最差情况下，可以退出程序，不至于一直等待导致死锁；
  2. 设置按照同一顺序访问资源，类似于串行执行；
  3. 避免事务中的用户交叉；
  4. 保持事务简短并在一个批处理中；
  5. 使用低隔离级别；
  6. 使用绑定链接。