MySQL面试(看这一篇就够了)

这里写目录标题

• • 字符集及校对规则
    • 索引
    • 1、传统非块式存储引擎（MyISAM）
    • 2、块式存储引擎（InnoDB）
    • 数据缓存机制
    • 事务管理策略
    • 数据锁机制
    • 大表优化方案
    • MySQL数据库作为发布系统的存储层，在日增量达到5万条以上的情况下,预计维护期限为三年,如何制定有效的优化方案？
    • 如何进行SQL性能调优
    • 数据锁优化策略解析
    • 索引底层实现原理及最佳实践
    • 事务管理的分布式实现策略

字符集及校对规则

字符集是一种包含符号与编码的集合；其中包含了多种字符集类型；而这些类型又根据不同的编码规则进行了分类。

UTF-8 General Case Collation按照普通字母顺序排列，并且不区分大小写（例如：a B c D）；而UTF-8 Binary Collation则按照二进制顺序排列，并且大小写字母的位置不同（例如：A排在a前面，B排在D前面）。

通常以ci、cs或bin后缀命名，并分别表示大小写不敏感、大小写敏感以及二进制形式。

索引

MySQL中所采用的索引数据结构主要分为哈希表和B+树两种类型。其中基于哈希表实现的哈希索引在本质上是一种高效的键值存储机制，在处理单一记录查询需求时可获得最佳查询性能。然而，在其他较为常见的场景下，则建议优先选择使用B+树构建的B树型（即所谓的B Tree）作为主键空间类型的外层排序算法以提高整体性能。值得注意的是 MySQL中采用的是基于 B+ 树优化版本其具体实现细节则会因不同的存储引擎而有所差异

1、MyISAM

在BTree结构中，叶子节点的数据域存储了数据记录的位置信息。在执行索引查找操作时，遵循BTree树状结构进行索引查找。当目标键值存在于树中时，则可访问对应的叶子节点的数据域，并根据该区域存储的位置信息读取相应的数据记录内容。这种组织方式被称为"非聚簇式索引"

2、InnoDB

其数据文件本身就是一种特殊的组织形式即为一个独立的数据仓库系统其中包含有多个独立的数据仓库每个仓库都是一个完整的数据库系统这样的设计使得我们可以在各个层次上灵活地进行业务操作并保证系统的灵活性与扩展性

缓存

my.cnf加入以下配置，重启Mysql开启查询缓存

    query_cache_type=1
    query_cache_size=600000

Mysql执行以下命令也可以开启查询缓存

    set global  query_cache_type=1;
    set global  query_cache_size=600000;

在此基础上，在开启查询缓存后的情况下进行相同的 查询条件和数据设置时会直接从 缓存中获取结果。此轮的 查询涉及的因素包括但不限于：原始 查询语句内容、目标 数据库名称以及客户端使用的 协议版本号等关键 信息。因此只要存在任何 微细差别就会导致 缓存失效。此外 在这些特殊情况下：使用自定义 函数调用存储函数管理 用户变量引用 临时表或者涉及 Mysql 库内系统 表时其执行结果均无法被 应用层进行 缓存处理。

建立之后，在MySQL中执行的查询缓存系统会跟踪每张涉及的表。当这些表（数据或结构）发生变动时，在这种情况下与这张表相关的所有缓存数据都将失效。因此，在考虑启用该功能之前，请谨慎评估其适用场景。特别地，在面对那些更新频率较高的应用时应格外谨慎。通常而言其最大容量设置为几十MB就足够了。此外还可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要使用缓存。

    select sql_no_cache count(*) from usr;

事务

点击查看各项事务管理相关内容

锁

该页面深入探讨了MySQL中的锁机制。它涵盖了不同类型的锁及其功能。通过事务管理机制实现了对锁的有效控制。在优化资源利用率的同时提升了系统的性能表现。提供了一种可靠的方式来执行并发操作的能力。这种设计对数据库性能有一定的提升作用。作为理解MySQL内核的重要参考资料之一，该内容为数据库开发人员提供了深入的技术指导。

大表优化

一旦MySQL单表记录数量过高时，数据库的CRUD性能将显著降低，并有一些常用的优化方法可用。例如，在处理订单历史查询时，请确保仅检索一个月内的记录。

1、读/写分离： 采用经典的数据库分离架构，在主库里执行 writes，在从库里执行 reads；
2、缓存： 在缓存层上主要采用MySQL的技术实现；
3、垂直分区： 垂直分区依据数据表内部属性的相关性来划分；
4、举例说明： 如将用户表按照功能划分，则可以将其分解为登录记录和基本信息两个独立的部分；
5、进一步解释： 垂直分割即针对单张表格中的多列属性进行切分；
6、总结说明： 这种方法能够有效地提升查询效率并降低资源消耗

垂直拆分的优点： 通过将数据按列组织存储的方式实施拆分优化后可使行数据规模降低，在查询时可有效降低因查询而读取的数据块数量并减少I/O操作所需的次数。这种设计有助于简化表的结构设计并提高数据库管理效率与维护便捷性；

垂直拆分的缺点： 采用此方法会导致主键字段会产生冗余副本从而需对多余的列进行管理维护这将直接增加系统运行时因Join操作而导致的操作需求提升；此外该方法可能导致事务处理变得更为复杂化因为每个事务可能涉及更多的Join逻辑运算从而增加了系统的复杂度；

5、水平分区：
采用特定策略保留原有数据库结构。
通过特定策略实现分片存储。
这样各部分的数据被分散至独立的数据库或存储单元中。
从而实现了分布式存储的目的。
这种技术能够有效支持海量数据量。
水平拆分指的是将单个数据库表按照行进行分割。
当单个表格的数据量超过200万条记录时，
举个例子，在实际应用中可以通过将一个用户的详细信息表格分解成多个独立的信息片段来优化资源分配。

水平拆分能够处理海量数据。需要注意的一点是：分表仅是解决了单一份表的数据规模过大问题；然而由于这些数据仍然保留在同一台服务器上，在实际应用中并无法显著提升MySQL的并发处理能力；因此建议最好是按照数据库分区来进行拆分优化

MySQL数据库作为发布系统的存储部分,日均增量达5万条以上；预计运维三年,则如何进行优化？

a. 精心构建的数据库架构允许一定程度的数据冗余并尽量减少联机查询操作以提升运行效率
b. 合理配置数据库字段的数据类型与存储引擎以优化性能并适当增加索引以提升查询速度
c. MySQL数据库采用了主从读写分离的技术
d. 基于业务规律对相关数据进行分表处理从而减少单表规模加快查询速度
e. 引入缓存机制例如Memcached、APC等技术来缓解热点数据访问压力
f. 优先处理不频繁修改的页面内容并生成静态版本
g. 编写高效运行的SQL语句例如将SELECT语句优化为SELECT field_1 field_2 field_3 FROM TABLE

如何进行SQL优化

1、选择正确的存储引擎
InnoDB适合于大量的写操作且支持事务操作
MyISAM适合于一些需要大量查询的应用，不支持事务
2、优化字段的数据类型
记住一个原则，越小的列会越快。如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间
3、为搜索字段添加索引
索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引
4、避免使用Select *
从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。即使你要查询数据表的所有字段，也尽量不要用*通配符，善用内置提供的字段排除定义也许能给带来更多的便利
5、使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR
6、尽可能的使用 NOT NULL
除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值
7、固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理
固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键
并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间

锁的优化策略

实现读写分离；引入分段加锁机制；尽量缩短资源持有时间；确保多线程按统一顺序进行资源获取；这些并非绝对原则，在实际应用中需根据具体情况调整策略；例如，在某些情况下过度细化锁的粒度可能导致不必要的开销过大（即频繁地进行加锁和解锁操作），从而反而降低了整体效率（比方说一次加载大量数据比多次加载小块数据更为高效）。

索引的底层实现原理和优化

优化版B+树
由于在所有叶子节点之间增加了指向下一个节点的指针这一改进措施，在InnoDB中推荐大多数表应使用默认自增主键作为其主索引

MySQL：
采用数据库中间件MyCat以达成读写分离的目的。
进行分库分表操作。
支持约1千至2千并发处理。

数据库设计三大范式指的是什么？
①字段不可分割
②基于主键的设计原则下,所有非主键字段必须依赖于主键
③在非关系型数据库中,为了保证数据完整性,禁止任意两个非主键字段之间相互依赖

MYSQL数据库设计规范遵循哪些具体规定？
名称构造原则要求所有数据库名称必须由字母字符、数字以及下划线组成，并且当涉及多个部分时采用下划线分隔开来。
建立索引是标准操作，在每个表格中都需要为其定义一个主键索引。
对于非运算功能的需求，在设计过程中建议限制字段数量不超过20个以确保系统的高效运行。
推荐避免定义null类型的字段，并优先选择varchar数据类型替代text类型以减少存储空间需求和提高数据存储效率。

关于您对数据库密码加密方式的了解程度如何？采用Bcrypt算法，其中密码长度必须在8到56位字符之间。随后会生成一个448位长的密钥。通过调用genSalt方法来设定加密强度，并使用hash函数对密码进行最终的加密处理。

建立数据库：
指定字符集类型为UTF-8；
排序规则采用：UTF-8 General Collation；
默认存储引擎选择InnoDB；
MySQL在字符编码方面具有广泛的支持，默认采用拉丁1编码方案；
其中大多数场景下推荐使用UTF-8字符集以实现全码支持；
该编码方案的特点是占用存储空间较大；
若仅在中国境内应用，则可选用GBK编码方案；
而UTF-8 General Collation在校对效率上有显著提升；
InnoDB常被视为事务处理的高效选择，
特别适合需要频繁进行事务操作的应用场景，
其支持完整的ACID特性，
包括行锁机制和外键约束功能

DBMS中的并发控制任务：保证在多个事务同时存取数据库中同一个数据时不破坏其隔离性和一致性；主要手段包括使用乐观锁、悲观锁以及memcache/hibernate/tair等技术；悲观lock的工作原理是每次在获取数据时都需要先进行锁定；这种机制会导致可能出现长时间的阻塞现象；而乐观lock则通过版本控制的方式实现并发管理；具体而言，在没有发生冲突的情况下不会进行锁定操作；但在检测到潜在冲突时会自动切换到锁定状态以防止数据不一致的问题；这种方法特别适用于那些对读操作要求较高的应用场景，在提升系统的吞吐量方面表现更为出色

分库分表即为实现一个数据库或表的物理分离；采用读写分离策略,即使使读操作执行于一个数据库中,而写的操作则应用于另一个数据库；首先复制两个MySQL文件夹,并对其中的日志配置进行相应调整以确保一致性

分布式事务

1、什么是本地事务？
本地事务即通过关系型数据库实现对事务的控制管理。其中关系型数据库具有ACID特性显著特征。
传统的单体应用一般会将所有数据存储在一个数据库中，并借助该数据库完成对事务的控制管理。
2、什么是分布式系统？
部署于不同节点上的系统通过网络交互协作完成协同工作的系统。
举例：
充值与积分关联业务：充值系统增加金额的同时同步更新积分记录
订单创建库存减少业务：订单系统更新订单信息后同步修改库存记录
下单后自动选课：订单系统更新订单信息后触发选课系统新增选课记录

3、问题描述
为了使两个分布式服务协同完成一个任务即订单支付成功自动添加学生选课的需求, 我们需要采取相应的技术措施来实现这一目标。
此问题的关键在于确保两个分布式服务事务的一致性, 这是满足上述需求的基础。
为了满足上述需求, 在订单服务中实施远程调用选课接口的操作, 并通过伪代码的方式记录具体操作流程。

    订单支付结果通知方法｛ 
    	更新支付表中支付状态为“成功”。 
    	远程调用选课接口添加选课记录。 
    ｝

存在如下问题：

• 如果支付表未能完成更新，则触发错误信息并停止执行后续操作。
• 如果支付表的更新操作顺利完成，则可能由于远程调用超时而导致本地事务处理延迟。
• 如果支付表的更新操作顺利完成，则首先完成选课操作并提交至选课数据库；随后尝试提交至支付表时却未成功。

CAP理论：分布式事务设计中只能满足一致性、可用性和分区容忍性中的两个特性，在三者之间必须做出取舍。一致性：微服务ABC三个服务实例需要保持数据的一致性；可用性：ABC三个服务实例中任意一个发生故障都不会影响整个集群对外提供的服务；分区容忍性：主要针对的是网络中断导致的数据分散问题；网络分区：通常由节点间网络中断或延迟引起的数据分布现象；为什么分布式系统无法同时具备C（一致性）、A（available）、P（partition tolerance）特性？为了维持系统的分区容忍性和提高系统的可用度，则要求每个服务实例都能保证本地数据的一致性和快速响应能力。然而，在保证高强一致性的前提下（即写操作后立刻读取到最新数据），系统必须牺牲强一致性这一特性才能提升整体性能和可靠性。例如，在订单退款等场景中常采用这种权衡策略：今日退款成功后等待用户确认并完成到账流程即可满足退款请求的成功条件

相关内容可访问以下资源链接：MySQL数据库面试题（2020最新版）