Mysql底层分析

索引

1.索引的分类

从数据结构上分类，B+树索引，hash索引，倒排索引，R树索引

从InoDB的B+树的索引分类，分为聚簇索引和非聚簇索引

从索引的性质进行分类，普通索引，主键索引，唯一索引，联合索引，全文索引，空间索引

锁&隔离级别

1.表级锁与行级锁的区别？

MySQL 常见的两种锁是表级锁和行级锁。

表级锁锁定整个表，所有对该表的读写操作都会被阻塞，适用于低并发场景；而行级锁锁定特定行，允许其他行的操作并发进行，适用于高并发场景。

表级锁粒度大，加锁快、开销小，但并发性能差，典型代表是 MyISAM。
行级锁粒度小，允许多个事务并发操作不同的行，并发性高，但锁的开销大，容易出现死锁，典型代表是 InnoDB。
因此实际使用时，MyISAM 适合读多写少的分析场景，而 InnoDB 的行级锁更适合高并发的 OLTP 系统。

2.MySQL 默认隔离级别？为何选择它？

MySQL 的默认隔离级别是可重复读（REPEATABLE READ）。选择这一隔离级别是因为它能够提供较高的数据一致性，防止不可重复读，并通过 MVCC（多版本并发控制）技术支持高效的并发性能。同时，通过间隙锁（Gap Lock）解决了幻读问题，使得在一个事务中多次读取结果一致，尽管它并不能完全消除幻读的可能性。

如果选择 Serializable，虽然隔离性最强，但会强制事务串行化执行，性能开销过大。

如果选择 Read Committed，虽然性能更高，但会出现不可重复读，影响数据一致性。但是某些大厂的事务隔离级别就是RC，数据的一致性可以由MQ等组件来完成，为了提高并发量

日志

1.MySQL 的 buffer pool 与缓存命中率如何监控？

MySQL 的 buffer pool 是 InnoDB 存储引擎中用于缓存数据和索引的内存区域。监控 buffer pool 和缓存命中率可以通过 SHOW ENGINE INNODB STATUS 查看状态变量，关键指标包括 buffer pool 的总大小、当前缓存的数据页数量等。或者是直接SHOW GLOBAL STATUS，SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_read%’;

视图更加友好

1
2
3
4
5
6
-- 查看 Buffer Pool 状态的摘要信息
SELECT * FROM sys.innodb_buffer_stats_by_schema;

-- 按表查看 Buffer Pool 的使用情况，用于定位热点数据
SELECT * FROM sys.innodb_buffer_stats_by_table ORDER BY pages DESC LIMIT 10;

缓存命中率的计算方法是：命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) × 100%。使用性能监控工具如 MySQL Enterprise Monitor 或 Percona Monitoring and Management （PMM）可以实时监控这些指标，从而优化数据库性能。

SQL语句

1.Order by是怎么实现的

在 MySQL 中，ORDER BY 的实现主要有三种方式：

  • 索引有序扫描：如果 ORDER BY 字段能和索引顺序匹配，直接按索引返回，最优。

  • Filesort 文件排序：无法利用索引时，MySQL 会将结果放入 sort buffer 排序，小数据在内存，大数据写磁盘；它有单路和双路两种实现方式。

    • 双路：扫描符合条件的行 → 将排序字段和主键 ID 放到 sort buffer → 排序 → 再根据主键 ID 回表取数据。需要两次数据访问，性能差。
    • 单路：直接将排序字段和要返回的所有列一起放入 sort buffer → 排序 → 直接返回。回表减少了，需要更大内存，sort buffer 容量不够时会写磁盘临时文件。
  • 优先队列排序：当 ORDER BY 搭配 LIMIT N 时，可能只维护一个 N 大小的堆，提高效率。结果集很大，但只取前 N 条。

调优：

尽量让 ORDER BY 和索引顺序匹配，避免 filesort。

如果必须 filesort，调大 sort_buffer_size、tmp_table_size，减少磁盘落盘。

避免 SELECT *，减少 sort buffer 内存占用。

大数据排序时考虑加索引或改写 SQL。

2.MySQL 如何存储 IP 地址？

因为我们的IP地址是有两种的IPV4 IPV6

  • VARCHAR：直接存 '192.168.0.1'，简单直观，但存储和查询效率差。

  • UNSIGNED INT：用 INET_ATON/INET_NTOA 把 IPv4 转整数（4B），空间小、查询快，但只支持 IPv4。使用Mysql提供的两个函数，

  • INET_ATON('192.168.0.1')` → `3232235521
INET_NTOA(3232235521)` → `'192.168.0.1'

  • VARBINARY(16)：用 INET6_ATON/INET6_NTOA 存二进制，既支持 IPv4 又支持 IPv6（推荐做法）。使用函数

  • INET6_ATON('2001:db8::1') → 二进制（16B）

    INET6_NTOA(binary_value) → 字符串 IP

如果只考虑 IPv4，INT 最优；如果要兼容 IPv6，推荐 VARBINARY(16)。

3.MySQL 一行记录是怎么存储的？

InnoDB 是最常用的存储引擎，数据以 页（Page，16KB） 为最小单位。

一个页中包含多个行记录。页内部有 页头、页目录、行数据 等。

一行记录的结构主要包括：行头信息（删除标记、指针）、事务信息（事务 ID、回滚指针）、NULL 标记位、变长字段长度列表以及实际字段值。
如果某行太大（例如包含 TEXT/BLOB），就会发生行溢出，行内只存前 768 字节和指针，其余内容存放在溢出页。
InnoDB 还支持 Compact、Dynamic、Compressed 等多种行格式，Compact 是默认的。

事务

1.Mysql长事务可能带来哪些问题？

首先，它会长时间持有锁，阻塞其他事务，影响并发性能；
其次，InnoDB 的 MVCC 机制需要依赖 undo log 保存历史版本，长事务不提交会导致 undo log 膨胀，版本链变长，阻止 purge 线程清理，造成表膨胀和性能下降；
同时也会增加死锁概率和主从延迟。
因此在开发中我们要控制事务范围，及时提交，并通过监控和超时机制避免长事务。

比如：

  • 及时提交事务：避免在事务中执行不必要的逻辑，比如用户交互、网络请求。
  • 控制事务大小：大批量操作要拆分成多个小事务。
  • SELECT * FROM information_schema.INNODB_TRX \G;监控长事务
  • 设置超时：
    • innodb_lock_wait_timeout：控制锁等待时间。
    • wait_timeout / interactive_timeout：控制会话空闲超时。