跳至主要內容

MySQL基础

Cutewr大约 18 分钟MySQLMySQL基础

MySQL基础

Mysql对比redis的优势

MySQL 是关系型数据库（RDBMS），而 Redis 是内存型键值存储（Key-Value Store）。MySQL 相对于 Redis 的主要优势：

  1. 首先第一点，数据持久化和一致性。MySQL提供 ACID（原子性、一致性、隔离性、持久性）事务支持，确保数据的完整性和一致性。并且数据持久化到磁盘，即使系统崩溃也不会丢失数据。Redis：虽然支持持久化（如 RDB 和 AOF），但默认情况下数据存储在内存中，可能存在数据丢失的风险。不支持复杂的事务（仅支持简单的事务操作，如 MULTI/EXEC）。
  2. 然后，是复杂查询与关系模型。MySQL：支持复杂的 SQL 查询，包括 JOIN、子查询、聚合函数等。支持关系模型，可以定义表之间的关系（如外键）。Redis：仅支持简单的键值操作，无法执行复杂的查询。不支持关系模型，需要开发者手动维护数据之间的关系。
  3. 数据容量。MySQL：数据存储在磁盘上，可以支持非常大的数据集（TB 级别）。Redis：数据存储在内存中，受限于内存大小，通常适合存储较小的数据集（GB 级别），虽然可以通过集群扩展内存容量，但成本较高
  4. 根据特点不同，Mysql和Redis的适用场景也不同，MySQL：适合需要持久化存储、复杂查询和事务支持的场景，如业务系统、财务系统、ERP 系统等。Redis：适合需要高性能、低延迟的场景，如缓存、会话存储、实时排行榜等。

简单介绍一下Mysql的高可用、高性能和高并发?

  1. 高可用
    1. 主从复制：通过二进制日志（binlog）实现主库与从库的异步/半同步数据同步。当主库故障时，需手动或通过工具（如MHA）切换从库为主库。
  2. 高性能
    1. 分库分表：通过水平拆分（如按用户ID分片）降低单表数据量，提升查询效率。
    2. 读写分离：主库处理写操作，从库负载读请求，分散压力。
    3. 索引优化：B+树索引减少磁盘I/O，覆盖索引避免回表。
    4. 缓冲池（Buffer Pool）：缓存热点数据页，减少直接磁盘访问。
  3. 高并发
    1. 连接池：如HikariCP，复用连接避免频繁创建销毁开销。
    2. 锁机制：InnoDB的行级锁减少锁冲突，MVCC（多版本并发控制）支持非阻塞读。
    3. 事务隔离级别：根据场景选择RC（读已提交）或RR（可重复读），平衡一致性与并发性能。

Mysql中ID重复可能出现了什么问题?

并发问题：多个事务生成了相同的id并插入数据

在MySQL中，Statement、PreparedStatement和CallableStatement的区别。

在MySQL中，Statement、PreparedStatement和CallableStatement三者的主要区别如下：

  1. Statement：
    • 用途：用于执行简单的SQL语句，适合执行不带参数的查询或更新操作。
    • 方法：提供executeQuery()、executeUpdate()和execute()方法，分别用于返回结果集、执行更新操作和执行任意SQL语句。
    • 特点：不支持参数化查询，存在SQL注入风险，且每次执行都会重新编译SQL语句，效率较低。
  2. PreparedStatement：
    • 用途：用于执行带参数的SQL语句，支持参数化查询，提高安全性和执行效率。
    • 方法：继承自Statement，提供setXXX()方法设置参数，然后执行预编译的SQL语句。
    • 特点：防止SQL注入，支持批处理，适合重复执行相同类型的SQL语句。
  3. CallableStatement：
    • 用途：专门用于调用存储过程，支持处理输入输出参数。
    • 方法：继承自PreparedStatement，提供registerOutParameter()方法注册输出参数，调用存储过程时使用execute()方法。
    • 特点：用于复杂的业务逻辑，支持处理存储过程的IN、OUT、INOUT参数。

总结：

  • Statement适用于简单的SQL执行，但安全性较低，效率不高。
  • PreparedStatement适合带参数的SQL执行，提高安全性和效率。
  • CallableStatement专门用于调用存储过程，处理复杂的业务逻辑。

在实际开发中，应根据需求选择合适的接口，以提高代码的安全性和系统的性能。

MySQL基础架构

分为server层和存储引擎。

  1. server层
    1. 连接器【权限验证，管理连接】
    2. 分析器【词法分析；语法分析。说白了就是看你的sql语句要干嘛，再检查sql语句是否正确】
    3. 优化器【选择mysql认为最优的执行计划】
    4. 执行器【执行sql语句，从存储引擎中返回数据】
  2. 存储引擎

负责数据的存储和读取

常见的有InnoDB、MyISAM、memory

  • InnoDB存储引擎，索引和数据放在一起，只有一个.idb文件
  • MyISAM存储引擎，索引和数据分开放，有.myi索引文件和.myd数据文件

MyISAM和InnoDB存储引擎有什么区别

  1. MyISAM只支持表锁，InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁
  2. MyIsam不提供事务支持，InnoDB提供事务支持，具有提交和回滚事务的能力。并且InnoDB可以通过MVCC和next-key lock解决幻读问题
  3. MyISAM不支持外键，InnoDB支持
  4. MyISAM不支持崩溃后的恢复，InnoDB可以使用redolog进行崩溃恢复
  5. 索引实现不一样，MyISAM中索引文件和数据文件是分离的；InnoDB，表文件本身就是按B+树组织的聚簇索引，叶子结点保存了完整的数据记录。
  6. InnoDB采用缓冲池(buffer pool)缓存数据页和索引页，MyISAM只用键缓存(key-value)缓存索引页。

三、SQL优化

慢SQL如何治理?

MySQL的调优是一个系统性的工程，需要从多个维度综合优化。以下是常见的调优方法分类及具体策略：

  1. SQL语句本身优化
    1. **避免低效查询：**减少SELECT *，明确指定所需字段，减少数据传输。避免复杂子查询，改用JOIN（但需注意JOIN的关联字段是否含索引）；
    2. 使用EXPLAIN分析执行计划：是否走了索引，如果是联合索引，有没有符合最左匹配原则；
    3. **优化WHERE条件：**避免在索引列上使用函数或运算（如WHERE YEAR(date_column) = 2023），会导致索引失效。
    4. **分页优化：**避免大偏移量分页（如LIMIT 100000, 10），改用WHERE id > 上一页最大ID的方式（游标分页）。
  2. 索引优化
    1. **合理创建索引：**为高频查询的WHERE、JOIN ON、ORDER BY、GROUP BY字段创建索引。联合索引遵循最左前缀原则，区分度高的字段放左侧。
    2. 避免冗余索引
    3. 定期检查重复索引（如联合索引(a,b)和单列索引(a)可能冗余）。
  3. **表结构优化：选择合适的数据类型：**使用更小的数据类型（如用INT而非BIGINT，CHAR定长字段替代VARCHAR）。
  4. 架构优化
    1. **读写分离：**主库处理写操作，读请求分发到多个从库
    2. **分库分表：**垂直拆分：按业务模块分库；水平拆分：按哈希、范围等规则分表（如ShardingSphere工具）。
    3. **缓存层：**高频读数据缓存到Redis/Memcached，减轻数据库压力。
  5. 硬件与存储优化：使用SSD替代机械硬盘，提升IOPS。

深度分页问题有哪些解决方案?

深度分页：limit offset size来实现跳过前面offset条数据，但事实上Mysql会读取(offset+size)条数据，再忽略前offset条数据，造成资源浪费。

解决方案：

  1. 游标分页：通过自增id，例如主键来记录位置，避免使用offset
  2. 使用特定的搜索引擎：ElasticSearch，天然支持海量数据的分页

MySQL死锁是如何产生的，如何解决?

不同事务循环等待对方的锁释放。

解决方案：

  1. 统一访问顺序：所有事务按固定顺序访问资源（如按主键排序更新）。
  2. 减少事务粒度：避免长事务，尽快提交。
  3. 合理使用索引：确保WHERE条件字段有索引，减少锁范围。
  4. 降低隔离级别：使用RC（读已提交）替代RR，减少间隙锁的使用。

死锁检测与自动处理

  1. InnoDB死锁检测：默认开启（innodb_deadlock_detect=ON），检测到死锁后自动回滚代价较小的事务。
  2. 设置锁超时：通过 innodb_lock_wait_timeout（默认50秒）强制释放锁。

explain命令用过吗，有哪些主要字段？

  1. id。标识整个查询中 SELELCT 语句的操作表顺序
  2. select_type

查询的类型，主要用于区分普通查询、子查询、联合查询等，常见的值有：

  • SIMPLE：简单查询，不包含 UNION 或者子查询。
  • PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
  • SUBQUERY：子查询中的第一个 SELECT。
  • UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
  • DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
  • UNION RESULT：UNION 查询的结果。
  1. table。查询用到的表名。
  2. type（重要）描述了找到所需数据用到的扫描方式。

所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型从最优到最差有：

  • system：不进行磁盘IO，查询系统表，只返回一条数据。
  • const：查询主键索引，精确查找，最多返回一条数据。
  • eq_ref：查询唯一性索引，精确查找，最多返回一条数据。
  • ref：使用普通索引，精确查询，查询结果可能找到是多条
  • range：对索引列进行范围查询
  • index：没有使用索引进行过滤，但是要查询的字段都在索引中，通过遍历整棵索引树来查找，和all类似，但是索引一般在内存中，速度更快一些。
  • ALL：全表扫描。

  1. possible_keys 表示可能用到的索引。

  2. key 表示实际使用到的索引。

  3. key_len。表示 MySQL 实际使用的索引长度；比如使用联合索引时，有可能是多个列的长度和。

  4. rows。大致估算出找到所需的记录或所需读取的行数，数值越小越好。

  5. Extra（重要）

    https://blog.csdn.net/li1325169021/article/details/113925826

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

  • Using filesort：在排序时使用了外部的索引排序，无法利用索引完成排序操作
  • Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
  • Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
  • Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
  • Using where：表明查询使用了 WHERE 子句进行条件过滤。
  • Using join buffer：表明用了连接缓存，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表一起进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

如何定位慢SQL

查询slowlog，定位

SQL语句执行很慢，怎么优化？

SQL语句本身层面

  1. 索引层面：选取合适的列作为索引；避免索引失效，不对索引列进行运算、类型转换、函数，使用联合索引的时候注意遵循最左匹配原则；不使用select *，尽量用上联合索引，实现索引覆盖，避免回表操作带来更多的IO次数；
  2. 小表驱动大表

数据表层面

表数据量大，B+树就越高，获取数据需要的IO次数也就越多，相应查询速度也就慢了。这个时候可以考虑分表。或者由于单机数据库限制，顶不住高并发，可以考虑分库，把请求分配到多台服务器中。

Mysql中左连接、右连接、内连接

  • left join：返回左表的全部数据，以及右表对应字段和左表匹配的数据
  • right join：返回右表的全部数据，以及左表对应字段和右表匹配的数据
  • inner join：只返回两个表中连接字段相等的行
  • Mysql不支持全连接full join，但是可以通过union all左连接和右连接的结果

分库分表

为什么要分库分表?

  1. 数据量大，B+树高，IO查询次数多，查询慢【分表】
  2. 单机数据库所能承载的连接数、IO及网络的吞吐等都是有限的，当并发量上来了之后，单机数据库可能会顶不住了。【分库】

分库分表有哪几种?

可以分为 垂直分库、垂直分表，水平分库、水平分表

  1. 垂直分表，简单来说就是将原本的一张表的字段分到多张表中。
  2. 垂直分库。就是把一个数据库里面的多个表，按照功能分成多个数据库存放。

优点：容易进行业务逻辑的划分

缺点：跨数据库的连接操作【可以在代码层面查出不同数据库的信息，再进行操作】;数据存储不均衡

  1. 水平分表，表结构是一样的，数据按照固定的规则选择数据表存放。
  2. 水平分库。就是每个库的表结构是一样的，但是数据是不一样的。

优点：提高了单表的查询性能；可以有效拓展存储容量；提高容错性，当一个数据库故障了，别的数据库还能正常运行，只影响小部分数据查询。

缺点：拆分规则；数据一致性；分布式事务管理复杂

项目中为什么使用了分表?

因为当这个平台发展很快，用户量增加，所带来的订单量会增加很多。订单表数据越多，底层存储的B+树就越高，IO查询次数就越多，查询效率就变慢了。所以我使用订单ID作为分片键，运用取模算法，将订单分为了两张表。处于学习的目的，实践了一下Sharding JDBC。

如何使用Sharding JDBC进行分表的?

Sharding JDBC 开箱即用，在配置文件中填写好数据源信息、分表依赖的列、分片算法，就能自动实现业务透明的分库分表。 本项目中，我对数据增长较快的订单表进行分表，选择订单id作为分表键，采用取模分片算法，将用户订单表拆分为2个表（order_0 和order_1）；提高了单表查询性能和可扩展性。

ShardingJDBC的原理是什么?

原理大致就是 改写SQL，当我们在配置文件中配置好了分片算法，分表依赖的列；ShardingJDBC就会根据指定的分片键，按照分片算法来计算对应的数据表，改写SQL语句。

对数据库分库分表可能会引发哪些问题?

  1. //事务问题，我们使用关系型数据库，有很大一点在于它保证事务的完整性。 分库之后必须使用分布式事务来解决，而分布式事务相对而言就比较重了，大部分的分布式事务只能保证最终一致性，所以业务上会存在数据不一致的场景。
  2. 连表 JOIN 问题 在一个库中的时候我们还可以利用JOIN 来连表查询，而跨库了之后就无法使用JOIN 了。 解决方案：
    1. 在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张表，然后利用代码来关联得到最终的结果。
    2. 或者适当的冗余一些字段，来去除需要关联的操作。
    3. 或者通过宽表的形式查询，比如将数据全量存储至ES 中，利用ES 来查询数据。
  3. 全局ID 唯一性问题 单库单表直接使用数据库的自增ID即可。但是分库分表之后，此时需要分布式id生成算法。
  4. SQL语句层面的：排序问题/count问题 单表直接通过 order by 进行排序/直接count即可。分库分表后，要么利用分库分表中间件的能力进行汇总，要么自己在业务代码中进行，或者利用ES 存储全量数据 来排序/计数。

千万级别的数据怎么处理？

存储层面

关系型数据库 基础

一、数据量增加之后，单表查询效率低；单机数据库承载的连接数、IO、吞吐量有限。可以考虑分库分表

  1. 分表

分表分水平分表和垂直分表。 水平分表即拆分成数据结构相同的各个小表，提升单表数据查询性能；可以灵活拓展 垂直分表即将一些字段分出去形成一个新表，各个表数据结构不相同，可以优化高并发下锁表的情况。 可想而知，分表的话，程序的逻辑是需要做修改的，所以，一般是在项目初期时，预见到大数据量的情况，才会考虑分表。后期阶段不建议分表，成本很大。

  1. 分库

分库也可以分为垂直分库和水平分库。

二、当数据量达到TB级别时，即使做了分库分表，单表数据量也是非常大的。可以根据具体业务，考虑使用非关系型数据库，提供了更高的性能以及横向扩展能力。

比如我了解的MongoDB，它是一种面向集合、与模式无关的文档型数据库。我们可以把一个完整的对象信息构建成文档存储到数据库中。MongoDB没有“数据一致性检查”、“事务”等概念，不适合存储对数据事务要求较高的场景，常见场景如下：日志记录、快递信息记录。

读写设计层面

如果是读多写少的场景，可以考虑读写分离，把数据库主节点复制到一个或多个数据库从节点，主库负责写操作，从库负责读操作，从而达到读写分离，高可用，数据备份等优化目的。 当然，主从模式也会有一些缺陷，比如主从同步延迟。

数据表设计层面

  1. 设计合适的索引【选择合适的列、联合索引】
  2. 字段设计

分布式id

分布式自增长ID生成算法 雪花算法、百度分布式ID算法、美团分布式ID算法 为什么要使用这些算法呢，这个与MySQL数据存储结构有关

从业务上来说 在设计数据库时不需要费尽心思去考虑设置哪个字段为主键。然后是这些字段只是理论上是唯一的，例如使用图书编号为主键，这个图书编号只是理论上来说是唯一的，但实践中可能会出现重复的情况。所以还是设置一个与业务无关的自增ID作为主键，然后增加一个图书编号的唯一性约束。

从技术上来说 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。 总的来说就是可以提高查询和插入的性能。

对InnoDB来说主键索引既存储索引值，又在叶子节点中存储行的数据，也就是说数据文件本身就是按照b+树方式存放数据的。 如果没有定义主键，则会使用非空的UNIQUE键做主键 ; 如果没有非空的UNIQUE键，则系统生成一个6字节的rowid做主键;聚簇索引中，N行形成一个页（一页通常大小为16K)。如果碰到不规则数据插入时，为了保持B+树的平衡，会造成频繁的页分裂和页旋转，插入速度比较慢。所以聚簇索引的主键值应尽量是连续增长的值，而不是随机值(不要用随机字符串或UUID)。 故对于InnoDB的主键，尽量用整型，而且是递增的整型。这样在存储/查询上都是非常高效的。

深度分页问题怎么解决

查询偏移量过大的场景我们称为 深度分页。

深度分页效率低的原因是：MySQL并不是跳过offset行，而是在存储引擎层取(offset+limit)行数据，【如果这里没有索引覆盖的话，就涉及offset+limit次回表操作】全部交给server层，然后server层过滤掉前offset行。那么当偏移量大的时候效率就很低。

优化方式：

  1. 子查询。在子查询中根据limit查出对应的主键值返回给server层，大大减少了给server层的数据量，从而提高效率；server层对主键值进行过滤，再通过需要的主键值去回表查询需要的数据，也大大减少了回表次数。
  2. 通过标签记录，再通过范围查询来跳过前面这些数据。
To be a better Javaer.
Copyright © 2025 Cutewr