测试开发面试题---MySQL

索引

索引是数据库管理系统中用于提高数据检索速度的数据结构，它可以帮助快速定位数据不需要扫描全表。

优点

提高查询速度；唯一性约束，确保数据不会重复；辅助排序和分组；减少磁盘IO。

缺点

占用存储空间；有维护成本，数据发生变化时索引也需要发生变化；可能不会使用到。

索引的底层数据结构

• hash索引：使用哈希表来存储索引和对应的值，可以实现快速查找，但是不支持范围查找。
• B树：多路平衡树，支持一个节点有多个分节点；所有的叶子节点在同一层，查找速度相同。
• B+树：索引存储在非叶子节点上，所有的数据存储在叶子节点上，使得树的高度更低进而减少磁盘IO次数。叶子节点通过指针相互连接，有很好的范围查找。

索引类型

普通索引，唯一索引，主键索引，覆盖索引，联合索引。

索引失效的原因

• 表的数据比较小的时候，可能会采用全表扫描（因为索引还需要创建和维护）；
• 字符串数据类型不匹配（“a”--0，“123”--123）；
• 使用函数或者表达式，索引是基于原始列值的；
• 不满足索引顺序，如（a,b,c）中没有a；

索引设计的原则

• 使用常用列作为索引，不要使用频繁更新或者没有区分度的列作为索引
• 尽量使用联合索引，但是要注意最左原则匹配
• 只创建必要的索引，对于数据量小的表没必要设计索引

InnoDB

MySQL中选择InnoDB作为默认的存储引擎。

优点

• 支持事务（这是选择InnoDB作为默认的存储引擎的重要原因）
• 支持外键，通过外键实现数据间的连接
• 支持热备份，在数据库没有停止的情况下对数据进行备份
• 支持崩溃恢复，在发生意外时可以自动恢复
• 支持行锁，并发性能高

与MyISAM的区别

• InnoDB使用行锁，并发性能高；MyISAM使用表锁，减少死锁发生的概率
• InnoDB支持事务、外键；MyISAM不支持
• InnoDB基于非聚集索引，它的索引文件和数据文件在一起；MyISAM是聚集索引，它的数据文件在MYD中，索引文件在MYI中。

InnoDB的索引策略

• 覆盖索引：索引覆盖所需要查询的所有列，直接在表上进行查询，不需要回表
• 最坐匹配原则，如索引（a,b,c）代表索引（a）（a,b）和（a,b,c）
• 索引下推：在索引遍历过程中，对字段进行判断，过滤不满足条件的索引，减少回表次数。

事务

事务是一系列数据库操作的逻辑单元，它具有四大特征：原子性、一致性、隔离性和持久性。

• 原子性：事务作为一个整体被执行，要么全部成功要么全部失败，如果失败则需要回滚，MySQL设计了一个undo-log表，在事务执行之前将数据存入到表中，一旦事务失败则直接在表中进行反向操作。
• 一致性：事务操作之前和操作之后完整不会改变。这主要基于业务层面，数据库提供了类型保证等实现这个。
• 隔离性：事物之间的操作是相互隔离的，一个事务的操作对其他事务没有影响，通过四个隔离级别实现。
• 持久性：事务对数据的改变是持久的，事务提交后将数据刷新到磁盘中。这种非直接刷新的效率比较低，因此InnoDB设置了一个缓冲区，将数据先刷新到缓冲区中，然后一起刷新到磁盘中。

并发事务引发的问题

• 脏读：事务A读取到事务B没有还没有提交的数据。
• 幻读：事务A在多次读取一个范围数据时，每次读出的数据行数不同。事务A第一次读完，事务B在这个范围中添加了数据，事务A再次读取。
• 不可重复读：事务A在读取同一个数据时得到的结果不同。

事务的隔离级别

• 读未提交：最低的级别，但是并发性能最好，会导致脏读、幻读和不可重复读。
• 读已提交：会导致幻读和不可重复读。
• 可重复读：MySQL默认的隔离级别，会导致幻读。
• 可串行化：最高的隔离级别，会解决所有问题。

多版本并发控制（MVCC）

• MySQL通过MVCC维护数据的半个版本，减少事务的读写冲突。
• 隐藏列：事务的id和指向undo_log的指针。
• 基于undo_log的版本链：每个版本通过指针指向上一个版本，形成版本链。
• ReadView：通过版本链可以会回滚到任意的版本，但是具体要回滚到哪个版本需要ReadView决定。

锁

• 表级锁：锁的粒度大，死锁概率小，并发性能低。
• 行级锁：锁的粒度小，死锁概率大，并发性能高。
• 共享锁：读锁，允许多个事务同时操作，但是不允许修改。
• 排他锁：写锁，事务执行期间不会被其他事务访问。
• 乐观锁：假设并发冲突少，事务在提交前会检查数据是否被修改过，如果是则回滚。
• 悲观锁：假设并发冲突多，事务在开始前就锁定需要的数据。

事务的使用场景

• 资金转账：资金转账时需要将一个账户里的钱转入的另一个账户中，这一系列的操作必须同时成功或者失败。

• 订单处理：在订单处理过程中需要考虑库存、创建订单、跟踪发货信息，这一系列的操作必须同时成功或者失败。

• 批量数据更新：保证更新过程中的完整性，避免一部分成功一部分没成功。

• 并发控制：在高并发控制下，事务提供了锁定机制，确保多个用户操作同一个数据时数据的完整性和一致性。

分库分表

• 水平拆分：按照某种规则进行划分。优点是每个库或者表中数据量一致，有助于提高性能、表的结构一致，改动小；缺点是扩容难度大、规则难以抽取。
• 垂直拆分：按照业务进行划分。优点是业务逻辑清晰；缺点是会导致单个库或者表的数据量过大，效果不明显。

分表

• 单表数据量大
• 查询优化，如果查询经常基于某一列进行，可以按照这一列进行划分，将数据分散到各个区来提高性能。

分库

• 并发压力大，单个数据库的并发连接实例达到上限，无法处理更多请求。
• 当单个数据库存储或者计算能力到达极限时进行资源扩展。

分库分表：当数据量和并发访问都很高的时候，分库解决高并发问题，分表解决数据量大问题。

分库分表注意点

• 确保跨库跨表的数据一致性
• 设计合理的路由算法，确保查询和写入能够进入正确的库表
• 通过水平扩展（增加服务器）和垂直扩展（增加资源）来平衡系统性能
• 分库分表的维护比单一库表的难度更大，需要考虑数据的迁移、备份和恢复
• 分表后非分片键如何查询
• 联合查询；在每个分表上独立进行查询然后将结果返回；使用全局索引；扫描全表。

SQL优化

SQL响应慢的原因

• 没有使用索引或者索引失效。采用慢查询日志找到最耗时的语句使用explain查看是否命中索引，如果命中了就对语句进行优化，如果未命中就添加新的索引。
• 网络原因或者机器负载高。使用主从复制加读写分离。
• 数据量大，并发压力大。使用分库分表。
• 热点数据导致的单点负载不均衡。使用缓存，将热点数据存入缓冲之中。

SQL优化方法

• 尽量避免使用select(*)。select(*)需要解析所有的字段且存在大量回表操作，效率低。建议查询尽可能具体的字段。
• 使用小表驱动大表。在SQL中A表和B表进行join时，将A表的m个数据放在连接池中，用B表去匹配。当A表小于B表的时候可以减少对比次数。
• 使用limit来限制返回行数提高性能。
• 使用连接查询代替子查询。子查询需要两次查询（外部，嵌套）且存在回表操作，连接查询只需要查询表上表上索引，性能更高。
• join表不能太多。join表太多时，对比次数增加，系统的负载增加且表的维护难度更大，因为一个表的改变会影响其他的表。