MySQL学习笔记（二）

• 为什么SELECT COUNT(*) FROM table 在 InnoDB比MyISAM慢？

  对于SELECT COUNT(*) FROM table语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM内部维持了一个计数器，预存了结果，所以直接返回即可。

• 说说InnoDB与MyISAM有什么区别？

  • 在MySQL 5.1及之前的版本中，MyISAM是默认的存储引擎，而在MySQL 5.5版本以后，默认使用InnoDB存储引擎。
  • MyISAM不支持行级锁，换句话说，MyISAM会对整张表加锁，而不是针对行。同时，MyISAM不支持事务和外键。MyISAM可被压缩，存储空间较小，而且 MyISAM在筛选大量数据时非常快。
  • InnoDB是事务型引擎，当事务异常提交时，会被回滚。同时，InnoDB支持行锁。此外，InnoDB需要更多存储空间，会在内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB支持自动奔溃恢复特性。

• MySQL索引类型有哪些？

  • 主键索引：索引列中的值必须是唯一的，不允许有空值。
  • 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
  • 全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。
  • 空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这 方面遵循OpenGIS几何数据模型规则。
  • 前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

  其他（按照索引列数量分类）

  1. 单列索引
  2. 组合索引 组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

• 什么时候不要使用索引？

  1. 经常增删改的列不要建立索引；
  2. 有大量重复的列不建立索引；
  3. 表记录太少不要建立索引。

• 说说什么是 MVCC？

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

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

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

• MySQL 事务隔离级别？

  • READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的。会导致脏读。
  • READ COMMITTED（提交读）：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。会导致不可重复读。这个隔离级别，也可以叫做“不可重复读”。
  • REPEATABLE READ（可重复读）：一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据。产生幻行，会导致幻读。（MySQL 默认隔离级别）
  • SERIALIZABLE（可串行化）：强制事务串行执行。

• 请说说 MySQL 数据库的锁？

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

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

• 说说什么是锁升级？

  MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondaryindex 查找的时候，也是通过找到对应的 primary，再找对应的数据行。

  当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁。

• 说说悲观锁和乐观锁

  • 悲观锁

    说的是数据库被外界（包括本系统当前的其他事物以及来自外部系统的事务处理）修改保持着保守态度，因此在整个数据修改过程中，将数据处于锁状态。悲观的实现往往是依靠数据库提供的锁机制，也只有数据库层面提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统汇总实现了加锁机制，也是没有办法保证系统不会修改数据。在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

  • 乐观锁

    相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

    而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

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

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

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

  

• CHAR 和 VARCHAR 的区别？

  CHAR 和VARCHAR 类型在存储和检索方面有所不同

  CHAR 列长度固定为创建表时声明的长度，长度值范围是1 到255当 CHAR 值被存储时，它们被用空格填充到特定长度，检索CHAR 值时需删除尾随空格。

• 主键和候选键有什么区别？

  表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

• 主键与索引有什么区别？

  • 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；
  • 主键不允许为空值，唯一索引列允许空值；
  • 一个表只能有一个主键，但是可以有多个唯一索引；
  • 主键可以被其他表引用为外键，唯一索引列不可以；
  • 主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本；

• MySQL 如何做到高可用方案？

  MySQL 高可用，意味着不能一台 MySQL 出了问题，就不能访问了。

  1. MySQL 高可用：分库分表，通过 MyCat 连接多个 MySQL
  2. MyCat 也得高可用：Haproxy，连接多个 MyCat
  3. Haproxy 也得高可用：通过 keepalived 辅助 Haproxy