MySQL 中有哪几种锁？

• 全局锁、行级锁、自增锁、记录锁、外键锁、间隙锁、表级锁、元数据锁、意向锁、临键锁

MySQL 中有哪些不同的表格？

• 基础表、临时表、系统表、信息表、性能模式表、分区表、外键表、触发器使用的表、存储过程和函数使用的表

简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别？

• 事务支持
  • InnoDB：支持事务处理，具有提交、回滚、崩溃恢复能力。
  • MyISAM：不支持事务处理。
• 锁机制
  • InnoDB: 支持行级锁、外键约束，适合高并发应用场景。
  • MyISAM：只支持表级锁，可能导致并发性能较差。
• 崩溃恢复
  • InnoDB：具有崩溃恢复能力。
  • MyISAM：没有崩溃恢复能力，数据库崩溃可能导致数据损坏。
• 存储结构
  • InnoDB：数据和索引存储在一起，使用B+树结构。
  • MyISAM：数据和索引分开存储，使用B树结构。
• 全文索引
  • InnoDB：不支持全文索引。
  • MyISAM：支持全文索引，适合需要全文搜索的应用。
• 外键约束
  • InnoDB：支持外键约束，有助于保持数据的完整性。
  • MyISAM：不支持外键约束。
• 内存使用
  • InnoDB：需要更多的内存和存储空间。
  • MyISAM：需要的内存和存储空间较少。
• 表级锁定
  • InnoDB：虽支持行级锁，但某些情况下(如全表扫描)仍会使用表级锁。
  • MyISAM：总使用表级锁。
• 数据恢复
  • InnoDB：提供数据恢复日志，可以更容易恢复数据。
  • MyISAM：数据恢复较为困难。
• 性能
  • InnoDB：处理大量数据更新、删除操作时，性能可能不如MyISAM。
  • MyISAM：读取大量数据时，性能通常优于InnoDB。
• 自动扩展
  • InnoDB：支持自动扩展空间。
  • MyISAM：不支持自动扩展。
• 默认存储引擎
  • InnoDB：MySQL 5.5.5版本开始，InnoDB成为默认存储引擎。
  • MyISAM：MySQL 5.5.5版本之前，MyISAM是默认存储引擎。

MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

• InnoDB存储引擎支持四种事务隔离级别，分别是：
  • 读未提交(脏读)
  • 此隔离级别下，事务可以读取到其他事务未提交的数据，这种读取被称为"脏读"。
  • 读已提交(不可重复读)
  • 此隔离级别确保了一个事务只能读取到其他事务已提交的数据。
  • 解决了脏读的问题，但可能会遇到不可重复读问题，即同一个事务多次读取同样条件的数据可能会得到不同的结果。
  • 可重复读(幻读)
  • InnoDB默认隔离级别。
  • 此隔离级别下，在一个事务中多次读取同样条件的数据是一致的，即使其他事务修改了这些记录，只要这些事务尚未提交，当前事务读取的结果就不会收到影响。
  • 此级别解决了不可重复读问题，但可能遇到幻读问题，即同一个事务中，由于其他事务插入与当前事务条件匹配的行，导致当前事务读物的结果集发生变化。
  • 可串行化
  • 此隔离级别提供最高级别的隔离，事务会隐式的对所有读取的行加上共享锁，对所有修改的行加上排他锁。
  • 意味着其他事务不能并发修改这些数据，直到当前事务提交或回滚。
  • 解决了幻读问题，但会严重影响并发性能。

CHAR 和 VARCHAR 的区别？

• 固定长度 VS 可变长度
  • CHAR是固定长度的数据类型，不足部分会用空格填充。
  • VARCHAR是可变长度的数据类型，根据存储的字符串实际长度加上额外的长度字节来动态分配空间。
• 存储空间效率
  • CHAR：即使只存储一个字符，也会占用剩余的空间，使用空格填充。
  • VARCHAR：只存储一个字符，实际只占用2个字节(1个字节存储长度信息，1个字节存储实际的字符)。
• 性能
  • CHAR：某些情况下性能更好，因为它是固定长度，处理起来更快，尤其是当所有数据接近定义长度时。由此考虑适用场景(身份证号、手机号)。
  • VARCHAR：存储长度变化较大的数据时更有效，因为它只使用必要的空间。
• 空值和默认值
  • CHAR：若定义时未指定NOT NULL，那么它可以存储空字符串(空格填充)。
  • VARCHAR：若定义时未指定NOT NULL，它可以存储NULL值。
• 最大长度
  • CHAR：最大长度是255个字符。
  • VARCHAR：最大长度是65535个字符。
• 使用场景
  • 当知道所有的数据都接近固定长度时，使用CHAR可以提高性能。
  • 当数据长度变化较大时，使用VARCHAR可以节省空间。
• 存储开销
  • VARCHAR：需要额外的一个字节来存储字符串的长度(对于长度小于等于255的字符串)。
  • CHAR：不需要。

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

• 定义
  • 候选键：是表中的一个或一组属性，其值能够唯一标识表中的每一行记录。
  • 主键：是从候选键中选择的一个特定的候选键，用于在表中表示每条记录。
• 唯一性：
  • 候选键：值必须在表中是唯一的，不能有重复。
  • 主键：值必须在表中是唯一的，并且表中只能有一个主键。
• 约束
  • 候选键：是一种逻辑上的约束，不直接影响数据库的存储、查询性能，但确保了数据的完整性。
  • 主键：是一种物理上的约束，不仅确保数据的完整性，还通常被数据库系统用来作为索引，提高查询效率。
• 用途
  • 候选键：主要用于理论分析和数据库规范化设计，帮助确定数据模型中关键数据。
  • 主键：在数据库的实际应用中更为重要，不仅用于数据完整性，还用于连接不同表(外键关系)、索引创建、查询优化。
• 自动创建
  • 若创建表时没有指定主键，数据库系统通常会自动为表创建一个隐藏的候选键，这个候选键通常是表中每行的系统生成的唯一标识符。
• 修改和删除
  • 候选键：可以被修改和删除，只要保证表中还有其他候选键能够唯一标识记录。
  • 主键：一旦被设置，修改或删除会比较复杂，因为涉及到索引和外键关系的更新。

myisamchk 是用来做什么的？

• myisamche是MySQL数据库管理系统中的一个工具，主要用于维护和修改MyISAM存储引擎的表。
• 主要用途和功能
  • 修复损坏的MyISAM表
  • 优化表
  • 检查表的完整性
  • 压缩表
  • 获取表信息
  • 支持多种操作选项

如果一个表有一列定义为 TIMESTAMP，将发生什么？

• 当插入一行数据但未设置该列的值，则自动设置当前时间(默认是UTC时间)作为该列的值。
• 当更新一行数据但未设置该列的值，则自动更新该列的值为当前时间。
• 当插入一行数据但未设置该列的值，且没有设置默认值，则会将 1970-01-01 00:00:01 作为默认值。
• TIMESTAMP列的时间范围是 1970-01-01 00:00:00 到 2038-01-19 03:14:07。
• TIMESTAMP列存储的时间是不带时区信息的，通常以服务器的时区设置为准。
• TIMESTAMP的精度为1秒，不支持毫秒。
• TIMESTAMP列占用4个字节的存储空间。
• TIMESTAMP列可以被索引，有助于提高基于时间的查询性能。

你怎么看到为表格定义的所有索引？

• 使用 show index from table_name;
• 使用 show create table table_name;
• 查询 information_schema.STATISTICS 表。
• 使用图形化工具界面查看。

LIKE 声明中的％和_是什么意思？

• %
  • 代表任意数量的字符，可以匹配任意长度的字符串。
• _
  • 代表任意单个字符，匹配一个字符。

BLOB 和 TEXT 有什么区别？

• 存储内容
  • BLOB：用于存储二进制大对象，可以包含可变的二进制数据，如图片、音频、视频等。
  • TEXT：用于存储字符串数据，如大量的文本。
• 存储长度
  • BLOB：存储的数据长度从TINYBLOB的255字节到LONGBLOB的4GB。
  • TEXT：存储的数据长度从TINYTEXT的255字节到LONGBLOB的4GB。
• 字符集和校对
  • BLOB：由于不涉及字符集转换，处理二进制数据更高效。
  • TEXT：由于涉及字符集转换和校对的可能，处理文本数据稍慢，但提供字符级别的操作。
• 函数和操作
  • BLOB：可以使用二进制函数和操作，比如BIN()、HEX()、BIT_LENGTH()等。
  • TEXT：可以使用字符串函数和操作，比如CONCAT()、SUBSTRING()、REPLACE()等。
• 排序和比较
  • BLOB：使用二进制排序，比较的是字节值。
  • TEXT：使用基于字符集的排序，比较的是字符值。
• 默认值
  • BLOB：可以设置默认值，但默认值必须是二进制字符串。
  • TEXT：可以设置默认值，但默认值必须是文本字符串。
• 存储开销
  • BLOB、TEXT：在存储时都会有一定的开销，都需要额外的一个字节来存储长度信息。
• 存储位置
  • BLOB、TEXT：对于较大的值，可能会存储在表的外部，表中只存储一个指针。
• 使用场景
  • BLOB：适合存储图片、音频文件、视频文件等二进制文件。
  • TEXT：适合存储文章、评论、描述等大量文本。

NOW（）和 CURRENT_DATE（）有什么区别？

• NOW（）
  • 返回当前的日期和时间。
  • 返回的时DATETIME类型的值，格式通常是 YYYY-MM-DD HH:MM:SS。
• CURRENT_DATE（）
  • 返回当前的日期
  • 返回的是DATE类型的值，格式通常是YYYY-MM-DD。

MySQL有哪些常见存储引擎？

• InnoDB、MyISAM、MEMORY、MERGE、ARCHIVE、FEDERATED、CSV、BLACKHOLE等

MySQL默认是哪个存储引擎？

• InnoDB

MySQL的架构设计？

• 宏观架构：
  • 网络连接层：提供与MySQL服务器建立连接的支持。支持几乎所有主流的编程语言，如Java、C、Python等，通过各自的API与MySQL建立连接。负责处理客户端的连接请求、身份验证、安全性检查等。
  • 服务层：
  • 连接池：负责存储和管理客户端与数据库的连接。
  • 系统管理和控制工具：如备份恢复、安全管理、集群管理等，维护数据库的正常运行。
  • SQL接口：接收客户端发送的各种SQL命令，并返回查询结果。支持DML、DDL、以及存储过程、视图、触发器等高级功能。
  • 解析器：负责将请求的SQL语句解析生成一个"解析树"，根据MySQL的规则进一步检查解析树是否合法。
  • 查询优化器：将解析树转化为执行计划，并与存储引擎交互。采用基于开销的优化策略，选择最优的执行计划来执行SQL语句。
  • 缓存：有一系列小缓存组成，如表缓存、记录缓存、权限缓存等。用于存储常用的查询结果和数据，提高查询效率。
  • 存储引擎层：负责数据的存储与提取，与底层文件系统交互。采用插件式的存储引擎涉及，支持多种存储引擎。
  • 系统文件层：文件的物理存储层，主要包含日志文件、数据文件、配置文件等。日志文件记录数据库的运行状态和错误信息。数据文件存储数据库的数据和索引信息。配置文件存放配置信息，如字符集、校验规则等。
• 逻辑架构：
  • Server层：负责建立连接、分析和执行SQL。包括连接池、执行器、优化器、解析器、预处理器、查询缓存等。所有的内置函数和跨存储引擎的功能都在Server层实现。
  • 存储引擎层：负责数据的存储和提取。

详细说一下一条 MySQL 语句执行的步骤？

• 客户端发送请求：客户端将SQL语句发送到MySQL服务器。
• 服务器接收请求：
  • 连接管理：服务器接收请求后，检查客户端的连接信息(用户名、密码、主机)，确保连接的合法性。
  • 建立连接：服务器为该客户端建立一个连接，并分配一个线程来处理该连接的请求。
• 解析SQL语句：
  • 语法解析：服务器收到SQL语句后，首先进行语法解析。解析器检查SQL语句是否符合MySQL的语法规则。
  • 预处理：对SQL语句进行预处理，包括解析表名、字段名等，将它们转换为内部可识别的格式。
• 优化SQL语句：
  • 查询优化：查询优化器会分析SQL语句，生成一个或多个执行计划。执行计划描述了如何访问数据表、如何连接表、如何使用索引等。
  • 选择最佳执行计划：优化器根据统计信息(如表的行数、索引的选择性等)评估不同执行计划的成本，选择成本最低的执行计划。
• 执行SQL语句：
  • 执行计划：根据优化器生成的执行计划，服务器开始执行SQL语句。包括访问数据库表、读取数据行、应用条件过滤等。
  • 存储引擎操作：服务器会调用存储引擎的接口来执行具体的数据操作。
• 返回结果：
  • 查询结果：如果是查询语句，服务器将查询结果返回给客户端。结果集包括满足条件的数据行和列。
  • 操作结果：如果是修改语句，服务器会返回操作的结果信息，例如影响的行数、自增ID等。
• 连接关闭：
  • 关闭连接：客户端处理完结果后，可以选择关闭连接。服务器会释放与该连接相关的资源，如内存、线程等。
  • 保持连接：如果客户端选择保持连接，服务器会继续监听来自该客户的后续请求。
• 缓存查询结果(可选)：对于某些查询语句，MySQL可以将查询结果缓存气力啊。当相同查询再次执行时，可直接从缓存中获取结果。MySQL8.0版本开始，默认关闭了查询缓存功能。

非关系型数据库（NOSQL）的优点？

• 适应大规模数据：
  • 易扩展：NoSQL数据库种类繁多，共同特点都是去掉关系数据库的关系型特性，数据之间无关系，这样就非常容易扩展。
  • 支持大规模数据存储和处理：NoSQL数据库能够更好地应对大规模数据的存储和处理需求，适用于大数据和分布式计算环境。
• 灵活的数据模型：
  • 非结构化数据存储：不要求事先定义数据的结果，能够存储非结构化、半结构化、结构化的数据。
  • 多模型支持：支持各种灵活的数据类型，如文档型、键值对、列族型、图形数据库等。
• 高性能：
  • 读写能力优越：与关系型数据库相比，NoSQL具有更高的读写性能，尤其是在处理大量写入操作时更为高效，适合处理大规模数据和高并发场景。
  • 内存优先：一些NoSQL数据库(如Couchbase)采用内存优先的涉及，数据首先存储在内存中。
• 高容错性和可用性：
  • 分布式架构：许多NoSQL数据库支持分布式架构，可以扩展到多个节点，实现高可用和容错能力。
  • 自动分区和复制：具备自动分区和复制功能，在节点故障时自动恢复数据。
• 简化应用开发流程：
  • 无需复杂的数据建模和查询语句：NoSQL数据库无需进行复杂的数据建模和编写SQL查询语句。
  • 易于集成云服务：许多NoSQL数据库支持云环境，易于与云服务集成。
• 低成本：NoSQL数据库采用横向扩展的方式，通过在廉价硬件上运行更多的节点来降低成本。
• 实时处理：适用于实时数据处理的应用场景，如实时分析、推荐系统等。

谈谈数据库设计三大范式？

• 第一范式：要求数据库表中的每一列都是不可分割的基本数据项，即每个字段都是原子性的，不可再分解。例如将地址字段拆分为街道、城市、省份等。
• 第二范式：要求表中的每个实例必须依赖于主键，即非主键字段必须完全依赖于主键，不能只依赖于主键的一部分。例如一个订单表中包含订单号、客户名、产品名，订单号是主键，那么客户名和产品名必须依赖于订单号，不能只依赖于订单号的一部分。
• 第三范式：要求非主键字段之间不能相互依赖，每个非主键字段只能依赖于主键。例如一个学生表中包含学生ID、姓名、专业名，学生ID是主键，专业名不能依赖于性能，只能依赖于学生ID。
• 范式的作用：减少数据冗余、提高数据一致性、简化数据维护。
• 范式的局限性：查询性能影响、复杂性增加、实际应用中的权衡。

谈谈MySQL索引？

• 索引类型：
  • 功能逻辑分类：普通索引、唯一索引、主键索引、全文索引、空间索引。
  • 物理实现分类：聚簇索引、非聚簇索引。
  • 作用字段分类：单列索引、组合索引。
• 索引的优缺点：
  • 优点：提高查询速度、保证数据唯一性、加速表之间的连接。
  • 缺点：占用磁盘空间、降低写操作性能、维护成本较高。

MySQL表可以创建多少列索引？

• InnoDB存储引擎：
  • 最大索引数量：1024个。
  • 最大列数限制：一个表最多可以有64个索引列。
• MyISAM存储引擎：
  • 最大索引数量：64个。
  • 最大列数限制：支持最多16个索引列。
• 影响索引数量的其他限制：
  • 行大小限制：索引的列数据会增加行的存储大小。如果索引导致行超过最大行大小(InnoDB为16kb)，可能会受限。
  • 组合索引的列数：InnoDB允许单个组合索引列数最大为16列。
  • 存储引擎和版本：某些限制可能因MySQL版本或特定存储引擎实现而有所不同。

MySQL索引包含哪些？优缺点？

• 普通索引：
  • 优点：提高select查询性能，特别是where、order by、group by语句。可用于多列组合索引。
  • 缺点：维护开销较高，会在插入、更新、删除操作时增加时间消耗。
• 唯一索引：
  • 优点：确保数据完整性，避免重复数据。查询性能与普通索引相似。
  • 缺点：不适用于需要重复值的场景。更新索引值时，可能因唯一性约束增加复杂性。
• 主键索引：
  • 优点：用于唯一标识表中的每一行数据。InnoDB存储引擎中，主键是聚簇索引的基础。
  • 缺点：涉及不当可能导致表结果难以修改。
• 全文索引：
  • 优点：对于大文本字段的搜索性能较好。支持自然语言模式和布尔值模式搜索。
  • 缺点：不适合小数据集或短文本字段。只支持MyISAM和InnoDB存储引擎。不支持实时更新的场景。
• 空间索引：
  • 优点：对空间数据的搜索性能较好。
  • 缺点：只支持MyISAM。MySQL8.0起支持InnoDB。不支持变长数据类型(如TEXT、BLOB);
• 组合索引：
  • 优点：适合多列组合查询。可被部分匹配查询利用。
  • 缺点：索引大小较大，占用更多存储空间。如果查询未遵循最左前缀原则，索引会失效。
• 哈希索引：
  • 优点：精确匹配查询速度极快。
  • 缺点：不支持范围查询。哈希冲突时性能下降。

谈谈MySQL主键索引？

• 特点：唯一性、不可为空、自动创建、查询效率高。
• 作用：加速查询、作为外键的参照、性能优化。
• 注意事项：
  • 一个表只能有一个主键索引，但一个主键索引可以有多个列组成，即复合主键。
  • 主键索引通常用于整型列，因为整型列的比较和查找速度通常比字符串列快。
  • 避免频繁更新的列上创建主键索引，因为每次更新都会触发索引的更新，可能会降低性能。

谈谈MySQL唯一索引？

• 特点：唯一性约束、允许NULL值。
• 作用：数据完整性、提高查询性能、优化数据检索。
• 注意事项：
  • 索引类的数据类型：建议为具有唯一性的列创建唯一索引，例如身份证号、邮箱地址等。
  • 索引列的选择性：对于唯一索引，选择性非常高，因为每个值都是唯一的。
  • 索引维护开销：会增加插入、更新、删除操作的开销，因为数据库需要维护索引结构。
  • 索引列的顺序：对于多列组合的唯一索引，索引列的顺序会影响查询优化的效果。

谈谈MySQL全文索引？

• 特点：针对文本内容设计、多模式支持、自动计算相关性、分词机制、高效的查询性能。
• 作用：提高查询效率、实现全文搜索功能、支持多字段组合查询、自动排序和过滤。
• 注意事项：
  • 适合长文本字段的查询。对于短文本或精确匹配需求，普通索引或LIKE更为合适。
  • MyISAM较早支持全文索引。MySQL5.6开始支持全文索引，且更推荐使用InnoDB。
  • 只能应用于CHAR、VARCHAR、TEXT类型字段。不支持BLOB或其他非文本字段。
  • 默认分词机制对中文等语言支持较差，需要使用第三方分词器(如ICU或Sphinx)。默认忽略长度小于3个字符的单词，可以通过配置更改。
  • 对于频繁更新字段，全文索引会增加维护成本，因为索引需要重建或调整。
  • 索引更新可能存在延迟，尤其在高并发写入场景下。
  • 默认算法较简单，可能无法满足高级搜索需求(如权重设置、语义分析)。
  • 默认会忽略在50%以上记录中出现的词语(常见于停止此，如the、and)。可通过调整ft_min_word_len(最小词长)或ft_stopword_file(停止词文件)进行修改。

索引，主键，唯一索引，联合索引的区别？

• 唯一性：主键和唯一索引都保证了数据的唯一性，但主键有额外的约束(如不允许NULL值，每个表只能有一个主键)。
• 数量：一个表只能有一个主键索引，但可以有多个唯一索引和联合索引。
• 列值：主键索引的列值不允许为空，而唯一索引允许有空值。
• 应用场景：主键通常用于唯一标识表中的记录，唯一索引用于防止数据重复并提高查询效率，联合索引用于提高多列查询的效率。

什么情况下设置了索引但无法使用？

• 数据类型不匹配

• 使用函数或表达式

  sql
SELECT * FROM example WHERE YEAR(created_at) = 2023;

• 使用LIKE通配符

  sql
SELECT * FROM example WHERE name LIKE '%Alice';

• 索引覆盖不足

  sql
SELECT col1, col2, col3 FROM example WHERE col1 = 10;

• 使用OR条件

  sql
SELECT * FROM example WHERE col1 = 10 OR col2 = 20;

• 最左前缀法则不遵守：对于组合索引，查询条件必须从索引的第一列开始，后续列才能被有效利用。

• 索引选择性地：即索引列的值重复很多，MySQL可能会选择全表扫描而不是使用索引。
• 隐式类型转换
• 空值比较
• 不等式操作符：在where子句中使用!、<>操作符，尤其是与范围查询结合使用时。

MySQL索引的底层原理，是如何实现的？

• 底层原理：基于不同的数据结构，主要包括B+树、哈希表、全文索引(倒排索引)等。不同类型的索引使用不同的数据结构来提高查询效率。
• B+树索引：
  • 概念：一种自平衡的多路搜索树，保证查询、插入、删除等操作时的时间复杂度为O(log N)。每个节点可以有多个子节点，因此B+树的高度相对较低，可以高效进行范围查询和精确查找。
  • 特点：叶子节点存储数据、排序存储、内存和磁盘空间优化。
  • 操作原理：
  • 查找：从根节点开始，逐层向下查找。每个节点存储多个索引值，通过比较查找条件与节点值大小，确定下一个访问的子节点。在叶子节点中找到具体的索引位置。
  • 插入：从根节点开始查找，找到合适的位置插入新的索引值。如果插入导致节点溢出(超出节点的最大容量)，则分裂节点，保持树的平衡。
  • 删除：与插入相似，删除索引后可能导致节点不满，需要进行合并操作，保持树的平衡。
• 哈希索引：
  • 概念：通过一个哈希函数将键值映射到一个固定大小的哈希表中，哈希表的每个桶存储一个索引项。基于键值进行精确匹配查询，对于相等条件查询非常高效。
  • 特点：查询速度快、不支持范围查询。
  • 操作原理：
  • 查找：对查询的键值应用哈希函数，计算哈希值，定位到哈希表中的桶，然后查找该桶中的元素。
  • 插入：将索引值通过哈希函数映射到哈希表的某个桶中，如果桶已存在元素，通过链表解决哈希冲突。
  • 删除：通过哈希值找到对应的桶并删除相应的索引项。

MySQL事务的隔离级别有哪些？区别？

• 读未提交：事务可读取其他事务尚未提交的数据，可能导致脏读、不可重复读、幻读问题。
• 读已提交：事务只能读取已提交的数据，可能导致不可重复读、幻读问题。
• 可重复读：确保事务可以多次从一个字段中读取相同的值，事务持续期间，禁止其他事务对这个字段进行更新，可能导致幻读。
• 串行化：事务按顺序执行，每个事务完全独立。
• 区别：
  • 并发性能：读未提交 -> 读已提交 > 可重复读 > 串行化。
  • 一致性问题：串行化避免一切一致性问题。读未提交可能产生脏读、不可重复读、幻读。读已提交可能产生脏读、幻读。可重复读可能产生不可重复读。
  • 实际应用：InnoDB存储引擎默认隔离级别为可重复读。

MySQL事务的四大特征？

• 原子性：事务中的所有操作要么全部执行成功，要么全部执行失败，事务是一个不可分割的最小工作单元。
• 一致性：事务执行的结果必须从一个一致的状态转换到另一个一致的状态，即事务执行前后数据的完整性约束没有被破坏。
• 隔离性：多个事务并发执行时，每个事务的执行结果不会收到其他事务的影响，事务之间是相互隔离的。
• 持久性：事务一旦提交，对数据库的更改是永久性的，即使系统发生故障也不会丢失。

MySQL事务原理？

• 实现原理：redo log(重做日志)、undo log(回滚日志)、MVCC(多版本并发控制)、锁机制。
• 操作流程：事务开始、执行SQL操作、事务提交或回滚。

谈谈bin log？

• 定义：记录所有对数据库的操作，包括DML和DDL操作。
• 应用场景：数据恢复、主从复制、审计功能。
• 工作原理：SQL执行、事务提交、日志轮换。
• 日志格式：STATEMENT(基于SQL语句的复制)、ROW(基于行的复制)、MIXED(混合模式复制)。
• 管理与归档：日志轮换与清理、日志归档。

谈谈redo log？

• 作用：保证事务的持久性、提高事务提交速度。
• 工作原理：记录物理修改、写入流程、持久化策略。
• 重要性：崩溃恢复、性能优化。

谈谈undo log？

• 定义：存储事务发生前的数据副本。
• 应用场景：事务回滚、并发控制。
• 工作原理：记录旧版本数据、回滚操作、MVCC支持。
• 存储与管理：Undo Log存在撤销日志段中，包含在回滚段中。对正规表和临时表执行插入、更新、删除操作的事务需要完整分配撤销日志。

这三种Log在MySQL应用在哪里？

• Binlog：主从复制、数据备份与恢复、数据审计与监控。
• Undo Log：事务处理、并发控制。
• Rego LOg：事务提交、崩溃恢复。

SQL常见的查询语句有哪些？

• 数据检索(SELECT)、数据插入(INSERT)、数据更新(UPDATE)、数据删除(DELETE)、表结构操作(CREATE TABLE、DROP TABLE、ALTER TABLE)等方面。

有哪些对SQL语句优化的方法？

• 索引类型选择
• 避免过度索引
• 优化查询语句
• 选择合适的JOIN类型
• 使用ON条件
• 避免在聚合函数中使用DISTINCT
• 使用GROUP BY优化
• 使用索引排序
• 减少排序的数据量
• 合理使用临时表
• 避免不必要的子查询
• 使用批处理和事务
• 查询缓存
• 应用层缓存
• 使用EXPLAIN分析查询计划
• 监控慢查询日志
• 合理设计数据类型
• 避免冗余数据

MySQL主从复制模式？

• 定义：将一台MySQL主服务器(主节点)的数据自动同步到一台或多台MySQL从服务器(从节点)。
• 应用场景：数据备份、高可用架构、读写分离、负载均衡等场景。
• 模式：异步复制、半同步复制、全同步复制、复制拓扑结构。

什么半同步复制？底层实现？

• 定义：在主库提交事务时，不是立即返回给客户端，而是等待至少一个从库接收到并确认写入了日志后才返回。
• 底层实现：从库通过I/O线程从主库读取二进制日志(BinLog)，并将其写入到本地的中继日志(RelayLog)中，SQL线程读取中继日志中的事件，并在从库上执行这些事件，当从库成功写入并准备好应用这些事件时，向主库发送ack消息。

什么是异步复制？底层实现？

• 定义：主库在执行完客户端提交的事务后，不等待从库接收并处理，而是立即将结果返回给客户端。
• 底层实现：从库通过I/O线程从主库读取二进制日志(BinLog)，并将其写入到本地的中继日志(RelayLog)中，然后SQL线程读取中继日志中的时间，并将这些事件逐条应用到从库数据库上。

什么是全同步复制？底层实现？

• 定义：主库只有在所有从库都完成同步后才会提交事务。
• 底层实现：从库通过I/O线程从主库读取二进制日志(BinLog)，并将其写入到本地的中继日志(RelayLog)中，SQL线程读取中继日志中的事件，并在从库上执行这些事件，当从库成功写入并准备好应用这些事件时，向主库发送ack消息。

什么是慢查询，如何避免？

• 指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。
• 开启方式：set GLOBAL slow_query_log=1;
• 设置记录阈值：set global long_query_time=0;

MySQL如何避免死锁？

• 查看死锁详情：show engine innodb status;

如何优化大量数据插入的性能？

• 对于大量数据插入的场景，可以采取以下优化措施：
  • 使用批量插入：+ 多个次批次（每批不要超过1000条）
  • 合并多条insert 为一条，即： insert into t values(a,b,c), (d,e,f) ,,
  • 将多个插入操作合并为一个大的插入操作，减少连接开销和通信次数。
  • 修改参数bulk_insert_buffer_size， 调大批量插入的缓存；
  • 使用LOAD DATA语句：MySQL提供了LOAD DATA语句来快速导入大量数据，在某些情况下比INSERT语句更高效。
  • 设置innodb_flush_log_at_trx_commit = 0 ，相对于 innodb_flush_log_at_trx_commit = 1 可以十分明显的提升导入速度；
  • innodb_flush_log_at_trx_commit = 0时，log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file 的刷新或者文件系统到磁盘的刷新操作;
  • 而如果这个值是其他的情况：
  • 在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步;
  • 事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。）

MySQL中字段类型DATETIME 和 TIMESTA的区别？

• timestamp类型的截至时间至2038年
• datetime类型的存储与时区无关

并发事务带来哪些问题?

• 脏读（Dirty read）：某个事务对数据进行修改时，另外一个事务读取了这个数据。因为这个数据是还没有提交的数据（可能会发生回滚），那么另外一个事务读到的这个数据是“脏数据”。
• 不可重复读（Unrepeatable read）：某个事务内多次读同一数据，数据不一致。可能在该事务多次读取数据期间，某一个事务修改了数据。（修改操作）
• 幻读（Phantom read）： 某个事务内多次读同一种数据，数据行数不一致。可能在该事务多次读取数据期间，某一个事务插入了数据，导致出现了本不该出现的数据。（插入删除操作）
• 丢失修改（Lost to modify）: 某个事务读取一个数据，并对数据进行修改，期间另外一个事务也访问了该数据，并对数据进行修改。导致第一个事务进行修改的的操作没有成功，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

四种事务隔离级别

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
• MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@transaction_isolation;查看

什么是最左前缀匹配原则？

• 最左前缀匹配原则：最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

慢查询如何优化？

• 分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改素引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表

如果一个表有一列定义为TIMESTAMP，将发生什么？

• 每当行被更改时， 时间戳字段将获取当前时间戳。

列设置为 AUTO INCREMENT 时， 如果在表中达到最大值， 会发生什么情况？

• 会停止递增， 任何进一步的插入都将产生错误， 因为密钥已被使用。

怎样才能找出最后一次插入时分配了哪个自动增量？

• LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值。

你怎么看到为表格定义的所有索引？

• SHOW INDEX FROM TABLE;

列对比运算符是什么？

• 在 SELECT 语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND， OR 或 LIKE 运算符。

BLOB 和TEXT 有什么区别？

• 区别在于对 BLOB 值进行排序和比较时区分大小写， 对 TEXT 值不区分大小写。

表可以使用多少列创建索引？

• 任何标准表最多可以创建 16 个索引列。

什么是通用 SQL 函数？

• CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个
  字段。
• FORMAT(X, D)- 格式化数字 X 到 D 有效数字。
• CURRDATE(), CURRTIME()- 返回当前日期或时间。
• NOW（） – 将当前日期和时间作为一个值返回。
• MONTH（）， DAY（ ）， YEAR（）， WEEK（）， WEEKDAY（） – 从日期值中提取给定数据。
• HOUR（）， MINUTE（）， SECOND（） – 从时间值中提取给定数据。
• DATEDIFF（ A， B） – 确定两个日期之间的差异， 通常用于计算年龄
• SUBTIMES（ A， B） – 确定两次之间的差异。
• FROMDAYS（ INT） – 将整数天数转换为日期值。

锁的优化策略

• 读写分离
• 分段加锁
• 减少锁持有的时间
• 多个线程尽量以相同的顺序去获取资源
• 不能将锁的粒度过于细化， 不然可能会出现线程的加锁和释放次数过多， 反而效率不如一次加一把大锁。

SQL 语言包括哪几部分？每部分都有哪些操作关键字？

• SQL 语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询（ DQL） 四个部分。
• 数据定义： Create Table,Alter Table,Drop Table, Craete/Drop Index 等;
• 数据操纵： Select,insert,update,delete;
• 数据控制： grant,revoke;
• 数据查询： select

百万级别或以上的数据如何删除？

• 先删除索引
• 然后删除其中无用数据
• 删除完成后重新创建索引(此时数据较少了)创建索引也非常快
• 直接删除

数据库的乐观锁和悲观锁是什么？怎么实现的？

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
• 实现方式：使用数据库中的锁机制
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
• 实现方式：乐观锁一般会使用版本号机制或CAS算法实现。
• 乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
• 悲观锁适用多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

什么是游标？

• 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。
• 用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

什么是存储过程？有哪些优缺点？

• 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
• 优点:
  • 存储过程是预编译过的，执行效率高。
  • 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
  • 安全性高，执行存储过程需要有一定权限的用户。
  • 存储过程可以重复使用，减少数据库开发人员的工作量。
• 缺点:
  • 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
  • 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
  • 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
  • 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

什么是触发器？触发器的使用场景有哪些？

• 触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。
• 使用场景：
  • 可以通过数据库中的相关表实现级联更改。
  • 实时监控某张表中的某个字段的更改而需要做出相应的处理。
  • 例如可以生成某些业务的编号。

MySQL中都有哪些触发器？

• 在MySQL数据库中有如下六种触发器：
  • Before Insert
  • After Insert
  • Before Update
  • After Update
  • Before Delete
  • After Delete

drop、delete与truncate的区别？

• drop：不可回滚；删除表、所有数据行、索引和权限也会被删除；删除速度最快；
• truncate：不可回滚；表结构还在，删除表中的所有数据；删除速度快；
• delete：可回滚；表结构还在，删除表的全部或部分数据行；删除速度慢，需逐行删除；

UNION与UNION ALL的区别？

• 如果使用UNION ALL，不会合并重复的记录行
• 效率 UNION 高于 UNION ALL