数据库常见问题（2） —— MySQL

使用B树和B+树的比较

InnoDB的索引使用的是B+树实现，B+树对比B树的好处：

• IO次数少：B+树的中间结点只存放索引，数据都存在叶结点中，因此中间结点可以存更多的数据，让索引树更加矮胖；
• 范围查询效率更高：B树需要中序遍历整个树，只B+树需要遍历叶结点中的链表；
• 查询效率更加稳定：每次查询都需要从根结点到叶结点，路径长度相同，所以每次查询的效率都差不多

使用B树索引和哈希索引的比较

哈希索引能以 O(1) 时间进行查找，但是只支持精确查找，无法用于部分查找和范围查找，无法用于排序与分组；B树索引支持大于小于等于查找，范围查找。哈希索引遇到大量哈希值相等的情况后查找效率会降低。哈希索引不支持数据的排序。

使用索引的优点

• 大大加快了数据的检索速度；
• 可以显著减少查询中分组和排序的时间；
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）

缺点：建立和维护索引耗费时间空间，更新索引很慢。

哪些情况下索引会失效？

• 以“%(表示任意0个或多个字符)”开头的LIKE语句；
• OR语句前后没有同时使用索引；
• 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
• 对于多列索引，必须满足 最左匹配原则/最左前缀原则 (最左优先，eg：多列索引col1、col2和col3，则 索引生效的情形包括 col1或col1，col2或col1，col2，col3)；
• 如果MySQL估计全表扫描比索引快，则不使用索引（比如非常小的表）

在哪些地方适合创建索引？

• 某列经常作为最大最小值；
• 经常被查询的字段；
• 经常用作表连接的字段；
• 经常出现在ORDER BY/GROUP BY/DISDINCT后面的字段

创建索引时需要注意什么？

• 只应建立在小字段上，而不要对大文本或图片建立索引（一页存储的数据越多一次IO操作获取的数据越大效率越高）；
• 建立索引的字段应该非空，在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替NULL；
• 选择数据密度大（唯一值占总数的百分比很大）的字段作索引

索引的分类？

• 普通索引
• 唯一索引 UNIQUE：索引列的值必须唯一，但允许有空值；
• 主键索引 PRIMARY KEY：必须唯一，不允许空值（是一种特殊的唯一索引；MySQL创建主键时默认为聚集索引，但主键也可以是非聚集索引）；
• 单列索引和多列索引/复合索引（Composite）：索引的列数；
• 覆盖（Covering）索引：索引包含了所有满足查询所需要的数据，查询的时候只需要读取索引而不需要回表读取数据；
• 聚集（Clustered）索引/非聚集索引：对磁盘上存放数据的物理地址重新组织以使这些数据按照指定规则排序的一种索引（数据的物理排列顺序和索引排列顺序一致）。因此每张表只能创建一个聚集索引（因为要改变物理存储顺序）。优点是查询速度快，因为可以直接按照顺序得到需要数据的物理地址。缺点是进行修改的速度较慢。对于需要经常搜索范围的值很有效。非聚集索引只记录逻辑顺序，并不改变物理顺序；
• 分区索引（？）
• 虚拟索引（Virtual）：模拟索引的存在而不用真正创建一个索引，用于快速测试创建索引对执行计划的影响。没有相关的索引段，不增加存储空间的使用

MySQL的两种存储引擎 InnoDB 和 MyISAM 的区别？

• InnoDB支持事务，可以进行Commit和Rollback；
• MyISAM 只支持表级锁，而 InnoDB 还支持行级锁，提高了并发操作的性能；
• InnoDB 支持外键；
• MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢；
• MyISAM 支持压缩表和空间数据索引，InnoDB需要更多的内存和存储；
• InnoDB 支持在线热备份

应用场景

• MyISAM 管理非事务表。它提供高速存储和检索（MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB更快），以及全文搜索能力。如果表比较小，或者是只读数据（有大量的SELECT），还是可以使用MyISAM；
• InnoDB 支持事务，并发情况下有很好的性能，基本可以替代MyISAM

热备份和冷备份

• 热备份：在数据库运行的情况下备份的方法。优点：可按表或用户备份，备份时数据库仍可使用，可恢复至任一时间点。但是不能出错
• 冷备份：数据库正常关闭后，将关键性文件复制到另一位置的备份方式。优点：操作简单快速，恢复简单

如何优化数据库？

SQL 语句的优化

分析慢查询日志：记录了在MySQL中响应时间超过阀值long_query_time的SQL语句，通过日志去找出IO大的SQL以及发现未命中索引的SQL

使用 Explain 进行分析：通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及被扫描的行数等问题；

• 应尽量避免在 where 子句中使用!=、<、>操作符或对字段进行null值判断，否则将引擎放弃使用索引而进行全表扫描；
• 只返回必要的列：最好不要使用 SELECT * 语句；
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据；
• 将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：
  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用；
  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余的查询；
  • 减少锁竞争

索引的优化

注意会引起索引失效的情况，以及在适合的地方建立索引

数据库表结构的优化

• 设计表时遵循三大范式；
• 选择合适的数据类型：尽可能不要存储NULL字段；使用简单的数据类型（int, varchar/ text）；
• 表的水平切分（Sharding）：将同一个表中的记录拆分到多个结构相同的表中（策略：哈希取模；根据ID范围来分）。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓解单个数据库的压力；
• 表的垂直切分：将一张表按列切分成多个表。可以将不常用的字段单独放在同一个表中；把大字段独立放入一个表中；或者把经常使用的字段（关系密切的）放在一张表中。垂直切分之后业务更加清晰，系统之间整合或扩展容易，数据维护简单

系统配置的优化

• 操作系统：增加TCP支持的队列数；
• MySQL配置文件优化：缓存池大小和个数设置

硬件的优化

• 磁盘性能：固态硬盘；
• CPU：多核且高频；
• 内存：增大内存

什么是主从复制？实现原理是什么？

主从复制（Replication）是指数据可以从一个MySQL数据库主服务器复制到一个或多个从服务器，从服务器可以复制主服务器中的所有数据库或者特定的数据库，或者特定的表。默认采用异步模式。

实现原理：

• 主服务器 binary log dump 线程：将主服务器中的数据更改（增删改）日志写入 Binary log 中；
• 从服务器 I/O 线程：负责从主服务器读取binary log，并写入本地的 Relay log；
• 从服务器 SQL 线程：负责读取 Relay log，解析出主服务器已经执行的数据更改，并在从服务器中重新执行（Replay），保证主从数据的一致性

为什么要主从复制？

• 读写分离：主服务器负责写，从服务器负责读
  • 缓解了锁的争用，即使主服务器中加了锁，依然可以进行读操作；
  • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性
• 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
• 降低单个服务器磁盘I/O访问的频率，提高单个机器的I/O性能