• InterviewFAQ-database
  • 数据库
    • 索引
    • 乐观锁和悲观锁
    • 多版本并发控制（MVCC）
    • 存储引擎
    • 优化步骤
    • 主从复制原理
    • MySQL高可用架构MHA
    • 读写分离
    • 配置文件
    • 备份（热备/冷备）
    • Memcached
    • Redis

InterviewFAQ-database

数据库

索引

• 特点

  • a）避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚集索引，有时不需要访问数据页即可得到数据。

    b）聚集索引可以避免数据插入操作，集中于表的最后一个数据页面。

    c）在某些情况下，索引可以避免排序操作。

• MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引

  • MySQL就普遍使用B+Tree实现其索引结构。

• 为什么索引提高查询效率，一定提升吗？

  • 因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。
  • 一般两种情况下不建议建索引。表记录比较少 、索引的选择性较低 （指不重复的索引值（也叫基数Cardinality）与表记录数（#T）的比值 ）的情况下不建议建索引 。

• 建立索引的几条规则，索引的数据结构？

  • 在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。
  • 不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

• b类树的特点

  • 首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。
  • 由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。
  • B+Tree比B-Tree更适合实现外存储索引结构
  • 索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

• InnoDB存储引擎的索引和其他的索引有什么区别？

  • MyISAM索引文件和数据文件是分离的，索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行。

  • InnoDB存储引擎采用"聚集索引"的数据存储方式实现B-Tree索引，所谓"聚集"，就是指数据行和相邻的键值紧凑地存储在一起，注意InnoDB只能聚集一个叶子页（16K）的记录（即聚集索引满足一定的范围的记录），因此包含相邻键值的记录可能会相距甚远。

  • 主键：

    • 主键索引既存储索引值,又在叶子中存储行的数据
    • 如果没有主键,则会Unique key做主键
    • 如果没有unique,则系统生成一个内部的rowid做主键
    • 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为"聚簇索引"

  • 推荐: http://tech.meituan.com/mysql-index.html

    MySQL索引背后的数据结构及算法原理

乐观锁和悲观锁

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

乐观锁与悲观锁的具体区别: 看这里

多版本并发控制（MVCC）

• MySQL的innodb引擎是如何实现MVCC的？
  • innodb会为每一行添加两个字段，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增。在repeated read的隔离级别（事务的隔离级别请看这篇文章）下，具体各种数据库操作的实现：
    • select：满足以下两个条件innodb会返回该行数据：
    • 该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地。
    • 该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
  • insert：将新插入的行的创建版本号设置为当前系统的版本号。
  • delete：将要删除的行的删除版本号设置为当前系统的版本号。
  • update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。
  • 其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。
  • 由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。参考：MVCC浅析

存储引擎

• MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
• InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持"行锁" ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。
• mysql 数据库引擎
• MySQL存储引擎－－MyISAM与InnoDB区别
• InnoDB引擎
  • 关键特性
    • 插入缓冲：提高非聚集索引插入的性能
      • 对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入，若不在，则先放入到一个Insert Buffer对象中，然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的合并操作，这时通常能将多个插入操作合并到一个操作中。
    • 两次写
    • 自适应哈希索引
    • 异步IO
    • 刷新邻接页
• 该如何选用两个存储引擎?
  • 因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。
  • 如果系统读少，写多的时候，尤其是并发写入高的时候，InnoDB就是首选了。

优化步骤

• 通过数据库分片来解决数据库写扩展的问题，避免单点故障以及写操作成为瓶颈
• 利用主从复制解决读的问题，通过多个从服务器来应对读操作

主从复制原理

• 主服务器把数据更新记录到二进制日志中
• 从服务器把主服务器的二进制日志复制到自己的中继日志中，由从服务器的IO线程负责
• 从服务器执行中继日志，把其更新应用在自己的数据库上，由从服务器的SQL线程负责

MySQL高可用架构MHA

• 管理节点：定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。
• 数据节点：
• 选举过程
• 错误恢复：MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

读写分离

• MySQL Proxy
• Mycat

配置文件

备份（热备/冷备）

• mysql表备份(mysqldump)
• 表类型
  • 支持事务
    • innodb可以在mysqladmin命令中加入single-transaction选项，生成一个快照来保证数据备份期间的一致性。
    • 停止从库，然后进行备份
  • 不支持事务
    • MyISAM中为了保持数据的一致性，需要在备份之前加读锁操作，flush table with read lock
    • mysqlhotcopy mydatabase backupdir
• 备份形式
  • 全备份
    • mysqldump -u root -p -all -database > all.sql
    • mysqldump -u root -p mydatabase > mydatabase.sql
    • mysqldump -u -root -p mydatabase tablename1 tablename2 > tablename.sql
  • 增量备份
    • 通过备份二进制日志来实现
• 备份时机
  • 选择应用负担小的时候
• 恢复测试

Memcached

• 一套分布式的快取系统
• Memcached的API使用三十二位元的循环冗余校验（CRC-32）计算键值后，将资料分散在不同的机器上。
• 当表格满了以后，接下来新增的资料会以LRU机制替换掉
• 应用首先从Memcached中获得数据，获取不到再从数据库中获得并保存在Memcached中。
• 好的应用95%的数据从Memcache中获得，3%的数据来自MySQL的query cache中获得，剩下的2%采取查表。Cache is King

Redis

• Redis是什么？

  • 是一个完全开源免费的key-value内存数据库
  • 通常被认为是一个数据结构服务器，主要是因为其有着丰富的数据结构 strings、map、 list、sets、 sorted sets

• Redis数据库

  • 通常局限点来说，Redis也以消息队列的形式存在，作为内嵌的List存在，满足实时的高并发需求。在使用缓存的时候，redis比memcached具有更多的优势，并且支持更多的数据类型，把redis当作一个中间存储系统，用来处理高并发的数据库操作

    • 速度快：使用标准C写，所有数据都在内存中完成，读写速度分别达到10万/20万

    • 持久化：对数据的更新采用Copy-on-write技术，可以异步地保存到磁盘上，主要有两种策略，一是根据时间，更新次数的快照（save 300 10 ）二是基于语句追加方式(Append-only file，aof)

    • 自动操作：对不同数据类型的操作都是自动的，很安全

    • 快速的主--从复制，官方提供了一个数据，Slave在21秒即完成了对Amazon网站10G key set的复制。

    • Sharding技术： 很容易将数据分布到多个Redis实例中，数据库的扩展是个永恒的话题，在关系型数据库中，主要是以添加硬件、以分区为主要技术形式的纵向扩展解决了很多的应用场景，但随着web2.0、移动互联网、云计算等应用的兴起，这种扩展模式已经不太适合了，所以近年来，像采用主从配置、数据库复制形式的，Sharding这种技术把负载分布到多个特理节点上去的横向扩展方式用处越来越多。

• Redis缺点

  • 是数据库容量受到物理内存的限制,不能用作海量数据的高性能读写,因此Redis适合的场景主要局限在较小数据量的高性能操作和运算上。

  • Redis较难支持在线扩容，在集群容量达到上限时在线扩容会变得很复杂。为避免这一问题，运维人员在系统上线时必须确保有足够的空间，这对资源造成了很大的浪费。