My Sql相关问题2

什么是OnlineDDL #

DDL,即Data Defination（定义、注释） Language，是用于定义数据库结构的操作。DDL操作用于创建、修改和删除数据库中的表、索引、视图、约束等数据库对象，而不涉及实际数据的操作。

以下是一些常见的DDL操作：

  • CREATE

  • ALTER

  • DROP

    用于永久删除数据库对象

  • TRUNCATE

    用于快速清空表中的所有数据，但保留表结构

与DDL相对的是DML，及Data Manipulation（操作） Language，用于操作数据。即包括我们常用的INSERT、DELETE和UPDATE等。

在MySQL 5.6之前，所有的ALTER操作其实是会阻塞DML操作的，如：添加/删除字段、添加/删除索引等，都是会锁表的。

但是在MySQL 5.6中引入了Online DDL，OnLineDDL是MySQL5.6提出的加速DDL方案，尽最大可能保证DDL期间不阻塞DML动作。但是需要注意，这里说的尽最大可能意味着不是所有DDL语句都会使用OnlineDDL加锁。

Online DDL的优点就是可以减少阻塞，是MySQL的一种内置优化手段，但是需要注意的是，DDL在刚开始和快结束的时候，都需要获取MDL锁，而在获取锁的时候如果有事务未提交，那么DDL就会因为加锁失败而进入阻塞状态，也会造成性能影响。

还有就是，如果Online DDL操作失败，其回滚操作可能成本较高。以及长时间运行的Online DDL操作可能导致主从同步滞后。

但是需要注意的是，即使有了Online DDL，也不意味着就可以随意在业务高峰期进行DDL变更了

在SQL后增加ALGORITHM=INPLACE, LOCK=NONE;或者 ALGORITHM=INSTANT、ALGORITHM=COPY

要根据不同的业务场景，选择不同的算法

  1. ALGORITHM算法选择
算法 原理 适用场景 缺点
COPY 创建临时表复制数据，原表被替换 所有ALTER操作（最通用） 锁表时间长，空间占用翻倍
INPLACE 直接在原表上修改（不复制数据） 添加索引、修改列类型（有限支持） 可能仍有短暂锁
INSTANT 仅修改元数据（最快） MySQL 8.0+ 的列添加/删除等操作 支持的操作有限
  1. LOCK锁定策略
锁定级别 行为 适用场景
NONE 允许并发读写 高并发业务时段
SHARED 允许读但阻塞写 需要数据一致性但可接受读
EXCLUSIVE 完全锁表（默认） 维护时段或低峰期

具体算法内容待补充

为什么不推荐使用外键 #

MySQL 外键最大的作用就是有助于维护数据的一致性和完整性。

但是，其实在很多大型互联网公司中，很少用外键的，甚至阿里巴巴Java开发手册中明确规定了：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明: 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群;级联更新是强阻塞，存在数据库更新风暴的风险;外键影响数据库的插入速度。

外键带来的问题：

  • 性能问题

    因为外键会增加数据库的维护负担，因为每次插入、更新或删除数据时，数据库都需要检查外键约束的完整性。

    首先，这两张表中共有两个索引，一个是Orders表的主键索引，一个是OrdersItems表的外键索引，这就使得每次插入、更新或删除订单或订单项时，数据库需都要维护这两个索引，这可能会导致性能开销。

    其次，在插入新的订单项之前，数据库需要执行数据一致性检查以确保引用的订单号在 Orders 表中存在。这额外的检查可能增加插入订单项的执行时间。

  • 无法适应分库分表

    在分库分表环境中，相关数据可能分布在不同的数据库中，外键通常难以跨越不同数据库来建立关系。更重要的是，分库分表环境中，数据的一致性可能更难维护。跨库事务搞不定。

为什么MySql会选错索引，如何解决？ #

有的时候，我们加了索引，也不一定最终查询语句就能用上索引，因为Innodb要不要使用索引，该使用哪个索引是优化器决定的。

成本因素：

  • 基数性： 索引的基数性就是我们常说的区分度，表示索引中不同值的数量。基数性越高，索引区分度越好，优化器更偏向于使用该索引。

    索引区分度 = 不重复的索引值数量 / 表中总记录数

    值接近1，代表几乎无重复

  • 选择性：

    指索引过滤数据的能力

  • 索引覆盖

    如果一个查询完全可以通过索引来解决，即所需的列都包含在索引中。

  • order by

    为了避免额外的排序操作，当SQL语句中有ORDER BY时，如果这个字段有索引，那么优化器为了减少file sort，会愿意选择使用这个索引，因为索引天然有序。

  • 索引类型

    不同类型的索引（如B-TREE、HASH、FULLTEXT等）适用于不同类型的查询。优化器会根据查询类型选择最合适的索引

  • join类型和顺序

    对于包含JOIN的查询，优化器会考虑使用哪些索引以及JOIN的顺序

  • 索引大小和深度

    较小、较浅的索引通常更快，因为它们占用更少的磁盘空间，可以更快地加载到内存中

  • 访问类型

    如范围查询、点查找、扫描等，也会影响索引的选择。例如，某些索引可能更适合范围查询。

  • 内存使用

    对于大型表，优化器还会考虑执行计划的内存使用情况，尽量避免造成过多的内存占用。

  • 系统资源限制

    优化器还会考虑系统的资源限制，如内存和磁盘I/O

  • 查询缓存：

    如果启用了查询缓存且相同的查询已被缓存，优化器会使用这个缓存的结果而不是选择新的索引。

解决方式：

  • 定期运行ANALYZE TABLE命令来更新表的统计信息

    1. 更新统计信息：表的基数（cardinality）、索引分布情况、列值的分布直方图（MySQL 8.0+）

    2. 优化查询性能：

      -- 执行前（可能使用低效索引）
EXPLAIN SELECT * FROM orders WHERE user_id BETWEEN 100 AND 200;

-- 执行分析后优化器能选择更好索引
ANALYZE TABLE orders;

  • 使用强制索引

    如果我们确定某个索引比优化器选择的更有效，可以在查询中使用FORCE INDEX来强制使用特定索引。（谨慎使用）

  • 优化查询逻辑

    尽量避免复杂的连接和子查询，这有助于优化器做出更好的决策

  • 调整索引

    我们可以为where条件中的过滤条件创建更合适的索引，并尽可能考虑创建复合索引来提高查询效率，尤其是对于多列的过滤和排序。

  • 调整MySQL配置

    根据系统的资源和需求调整MySQL的配置参数，比如缓冲池大小（innodb_buffer_pool_size）。

主键索引和唯一索引的区别？ #

  • 唯一性

    都是唯一的

  • 是否为空？

    主键索引不能为空，唯一索引可以为空

  • 是否可以有多个？

    一张表中，主键索引只能是一个，唯一索引可以有多个

  • 索引结构

    在innode中，主键索引就是聚簇索引，而唯一索引通常是非聚簇索引。

    这是因为有的时候，我们可能没有创建主键索引，那么，MySQL会默认选择一个唯一的非空索引作为聚簇索引。所以，唯一索引也可能被选为聚簇索引。

  • 是否回表

    主键索引查询不需要回表，基于唯一索引查询，通常是需要回表。

  • 外键

    主键可以被其他表引用为外键，而唯一索引是不可以的。

联合索引是越多越好吗？ #

不是，因为索引的创建和维护也是有很多成本和开销的。

首先，每个索引都需要占用存储空间。创建太多的联合索引可能会增加数据库的存储需求。

其次，每次对表进行插入、更新或删除操作时，相关的索引都需要进行维护。如果有太多的联合索引，这可能会增加写入操作的成本。

索引的维护会带来额外的一些页分裂、页合并等操作，会让整个过程效率更加低。

为什么不建议使用存储过程？ #

什么是存储过程？

存储过程是数据库系统中一组预编译的SQL语句集合，存储在数据库服务器中，可以被应用程序或其他存储过程调用。

主要特点

  1. 预编译执行：存储过程在创建时进行编译，后续调用时直接执行，提高性能
  2. 减少网络流量：客户端只需发送调用命令，而非多条SQL语句
  3. 模块化编程：将复杂业务逻辑封装在数据库层
  4. 安全性：可通过权限控制限制对底层数据的直接访问
  5. 复用性：可被多个应用程序共享调用

存储过程存在以下几个问题或者局限性：

  • 可维护性：存储过程的逻辑可能是非常复杂的，随着内容的不断修改，会变的难以理解和维护。
  • 调试和测试困难
  • 跨数据库兼容性：存储过程通常不是跨数据库平台兼容的。
  • 容易出错：用到存储过程的场景，都是非常复杂的业务场景，里面会有很多很多业务逻辑，这些业务逻辑在存储过程中通过各种IF-ELSE分支来实现的话，非常容易出错。
  • 安全性问题：存储过程可能成为安全风险的源头，特别是如果它们不正确地处理输入数据，可能导致SQL注入等安全漏洞。此外，过度依赖存储过程可能会导致数据库权限和访问控制变得复杂。
  • 版本控制和源代码管理：存储过程的代码通常存储在数据库中，这可能使得将它们纳入常规的源代码管理和版本控制流程变得更加困难。
  • 代码审查：如果是业务逻辑写在代码中，很多时候CodeReview的时候都会非常重点的关注，但是对于SQL语句，有的时候就很容易被忽略，那么很多问题就不容易被暴露出来。

阿里数据库能抗秒杀的原理 #

在阿里电商的秒杀等（据我了解，淘宝、天猫、猫超、大麦等都是这么干的）场景中，主要还是基于MySQL数据库在做扣减的，主要是因为这样做最可靠了（避免了redis扣减方案中的数据不一致、少卖等问题）。