Mysql

总结Mysql中遇到的各种知识、命令、问题等

【慢Sql、索引失效、不会优化？那你必须得看看这套2025版MySQL调优实战全套精讲视频，这绝对是mysql教程天花板！】 https://www.bilibili.com/video/BV1uaguzUEJa/?p=12&share_source=copy_web&vd_source=b1cd12cd9d40457a3baf3133ec496e39

1、索引

索引就是一种数据结构，可以加快数据的查找效率，现在常见的索引结构是二叉树、红黑树（二叉平衡树）、Hash表、B树，但是最常用的还是B+Tree。一款在线索引绘图

1.1、常见索引结构

二叉树：会导致一边过长，形成链表结构
红黑树：层级会过深，且平衡时会有性能开销
Hash表：一维的数组+二维的链表，会有哈希冲突的风险，查找效率极高，时间复杂度接近O(1)。但是工作中不常用，因为不支持范围查找，还得需要范围查找
B-Tree：一个节点可以存储更多的索引元素，但是由于每个索引元素需要携带data，那一行可以存放的索引元素也不是很多，不如B+Tree
B+Tree：只有叶子节点存储真实的数据，其它节点都作为冗余索引，只存储索引元素。

在非叶子节点中：在启动时会加载在内存中，Mysql中默认每个节点可以存储16KB的数据，如果索引类型是bigint类型，那占据空间为8B，中间空白的为下一层级的磁盘文件地址大约占据6B，那么每一个节点可以存差不多1170个索引元素

在叶子节点中：真正加载中磁盘中，由于有data元素，大约不超过1KB，那么一个索引节点可以存16个，那一个高度为3的B+Tree，可以存储接近两千万的数据

查找时类似折半查找，并且叶子节点是双向链表结构，支持范围查找

1.2、索引引擎

1.2.1、MyISAM

存储格式如下，注意8.0版本之后没有frm文件了，会存放到InnoDB 表空间（位于 mysql/data/mysql.ibd 里）

# mysql/data/数据库名/

demo_myisam.frm			# 表结构文件
demo_myisam.MYD			# 存表数据
demo_myisam.MYI			# 存索引

由于这种索引和数据是不在一个文件中的，这也称为非聚集索引，即会二次回表查询，叶子节点存储的是MYD的位置

1.2.2、InnoDB

存储格式如下

# mysql/data/数据库名/

demo_innodb.frm			# 表结构文件
demo_innodb.ibd			# 索引+表数据

只有叶子节点存储真实数据，非叶子节点不存储数据，这是一种聚集索引

但是上面这个图是主键索引，如果现在我创建一个非主键索引，那么非主键索引的B+Tree的叶子节点存放的是主键字段值，然后再去主键索引的B+Tree中去进一步查找数据，这也相当于一种回表查询

所以从上面我们可以知道，推荐一张表一定要创建一个主键，并且推荐成自增的，如果没有创建主键和唯一索引，Mysql会给一张表创建一个隐藏字段rowid，这个是数据的唯一标识，并且根据rowid去维护这个B+Tree。

1.3、联合索引

就是一个排好序的数据结构，默认按照第一个字段进行排序，如果第一个字段相同，就按照第二个…这样一直进行排序即可

在创建完联合索引之后需要思考什么时候会走联合索引，这个就是最左前缀原则，如果没有第一个字段参与那肯定会进行了全表扫描了

1
2
3

EXPLAIN SELECT * FROM employee WHERE name='Bill' and age = 31;			# √
EXPLAIN SELECT * FROM employee WHERE age = 31 and position = 'dev';		# ×
EXPLAIN SELECT * FROM employee WHERE position = 'manager';				# ×

2、内部核心组件

客户端 + server层 + 存储引擎层

客户端：发送sql语句，建立链接
server层
1. 连接器：管理连接与权限校验
2. 查询缓存：5.7以前有可以进行开启，8.x代码层面删除。因为在高并发的场景下会导致锁竞争严重，并且命中率也很低，功能鸡肋
3. 词法分析器：词法分析，语法分析。检查sql语句的词法，会过滤掉无意义的空白、注释、将大小写统一，并且会将sql语句放到一个词法树中进一步检查
4. 优化器：在语法树中，并且结合一些索引等可以算出相同结果的路径中，用统计信息和成本公式，挑选一条最省时的执行计划
5. 执行器：调用引擎接口，获取查询结果
引擎层：读写磁盘，数据结构化存储的实现

3、三大日志+SQL全流程

undolog日志：用于事务回滚和MVCC，记录逻辑修改的逆操作。
redolog日志：InnoDB引擎独有，用于事务持久性和崩溃恢复，记录物理修改。
1. 存放的是物理页的修改
  1
  2
  # mysql5.x放在mysql/data/
  # mysql8.x放在mysql/data/#innodb_redo
2. os cache
  
  在redo日志这里还有个os cache，在内存中，为了解决硬盘和内存的数据传输速率
binlog日志：server层，用于主从复制和数据恢复，记录逻辑修改的SQL语句。
1. 存放：逻辑变更事件（SQL或行数据），可以支持主从、备份、闪回能够按照事务再执行一遍
  1
  2
  3
  # mysql/data
  binlog.000xxx
  binlog.000xxx
2. 当binlog日志完成记录之后会立即回调InnoDB，将对应事务的redo日志从prepare状态隐式地改成commit（实际上只是把内存里的 trx -> state改掉）

4、Like模糊匹配

1	select * from test where name like 'abc%'

'abc%' -> 右模糊，走索引
'%abc' -> 左模糊，不走索引，会全表扫描
'%abc%' -> 前后都模糊，不走索引，会全表扫描

只要 % 出现在模式最左侧，普通 B+Tree 索引就失效；放后面或配合反向索引 / 全文索引才能继续享受索引加速。

5、count(?)

在SQL中，COUNT()用于统计数量，有三种用法

COUNT(1)：统计表中行数量，包含所有行
COUNT(*)：统计表中行数量，包含所有行，其实和COUNT(1)没区别，一些营销号喜欢胡说
COUNT(col_name)：统计非NULL的行数量

6、索引失效的情况

看走不走索引还是提前先用explain执行计划进行测试

直接在where的字段上进行函数操作，比如：

1	select * from test where UPPER(username) = 'john';

最左前缀原则

# 现在创建了联合索引 (a, b, c)
EXPLAIN SELECT * FROM employee WHERE name='Bill' and age = 31;			# √
EXPLAIN SELECT * FROM employee WHERE age = 31 and position = 'dev';		# ×
EXPLAIN SELECT * FROM employee WHERE position = 'manager';				# ×

order by 使用不当
使用select *
where和order by不匹配
not in进行查询

7、Mysql深分页

在我的电影推荐系统的项目中，对于影视库的资源采用了用户可以实现无限滚动的状态，为了解决Mysql传统的limit-offest查询，在查询语句上进行了调整，传统的查询语句是这样的

select
	mid, title, rate, cover, release_newest_date
from movie
where
	types like '恐怖%'
	and country like '美国'
	and release_date = '2023'
order by rate DESC, mid DESC

由于影视库这里和淘宝购物一样在默认或供给用户去选择合适的字段进行排序，比如年份、国家、星级等进行排序，那排序时可以添加一个判断标准，由前端传递每次查询到页数的最后一个数据的信息，比如这里的rate和mid，然后后端添加一个判断，就不需要从头开始进行全表扫描了，并且注意需要创建这种覆盖索引防止不能走索引，可以对这里的筛选字段添加全文索引，这类似与Elasticsearch中的倒排索引MATCH ... AGAINST

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yyh.mapper.FilmLibraryMapper">

    <select id="selectMoviesWithCursor" resultType="com.yyh.model.po.Movie">
        SELECT
        mid, title, types, rate, cover, release_newest_date
        FROM movie
        <where>
            <!-- 其他筛选条件 -->
            <if test="type != null and type != ''">
                AND types LIKE CONCAT(#{type}, '%')
            </if>
            <if test="region != null and region != ''">
                AND country LIKE CONCAT(#{region}, '%')
            </if>
            <if test="year != null and year != ''">
                AND release_date LIKE CONCAT(#{year}, '%')
            </if>

            <!-- 动态游标条件 -->
            <if test="lastId != null">
                <choose>
                    <when test="sort == 'rating' and lastRating != null">
                        AND (rate, mid) &lt; (#{lastRating}, #{lastId})
                    </when>
                    <when test="sort == 'hottest' and lastHotness != null">
                        AND (release_newest_date, mid) &lt; (#{lastHotness}, #{lastId})
                    </when>
                    <!-- 默认为 'latest' -->
                    <otherwise>
                        <if test="lastDate != null">
                            AND (release_newest_date, mid) &lt; (#{lastDate}, #{lastId})
                        </if>
                    </otherwise>
                </choose>
            </if>
        </where>

        <!-- 动态排序 -->
        ORDER BY
        <choose>
            <when test="sort == 'rating'">
                rate DESC, mid DESC
            </when>
            <when test="sort == 'hottest'">
                release_newest_date DESC, rate DESC, mid DESC
            </when>
            <!-- 默认为 'latest' -->
            <otherwise>
                release_newest_date DESC, mid DESC
            </otherwise>
        </choose>

        LIMIT #{pageSize}
    </select>

</mapper>

8、全文索引

在Mysql8.x之后内置了FULLTEXT的索引结构，其类似于ES的倒排索引

创建FULLTEXT索引

1 2	ALTER TABLE article ADD FULLTEXT INDEX ft_title_body (title, body) WITH PARSER ngram;

使用MATCH...AGAINST进行查询

1 2	SELECT * FROM article WHERE MATCH(title, body) AGAINST('小米' IN NATURAL LANGUAGE MODE);

使用EXPLAIN执行计划进行分析

未使用全文索引的情况，这里采用了模糊查询并且创建了对应的索引

1
2
3

EXPLAIN FORMAT=JSON
SELECT * FROM article
WHERE title LIKE '小米%';

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "57.86"
    },
    "table": {
      "table_name": "article",
      "access_type": "range",
      "possible_keys": [
        "idx_title",
        "ft_title_body",
        "ft_title"
      ],
      "key": "idx_title",
      "used_key_parts": [
        "title"
      ],
      "key_length": "803",
      "rows_examined_per_scan": 128,
      "rows_produced_per_join": 128,
      "filtered": "100.00",
      "index_condition": "(`test`.`article`.`title` like '小米%')",
      "cost_info": {
        "read_cost": "45.06",
        "eval_cost": "12.80",
        "prefix_cost": "57.86",
        "data_read_per_join": "103K"
      },
      "used_columns": [
        "id",
        "title",
        "body"
      ]
    }
  }
}

使用全文搜索

1
2
3

EXPLAIN FORMAT=JSON
SELECT * FROM article
WHERE MATCH(title) AGAINST('小米');

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "article",
      "access_type": "fulltext",
      "possible_keys": [
        "ft_title"
      ],
      "key": "ft_title",
      "used_key_parts": [
        "title"
      ],
      "key_length": "0",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "ft_hints": "sorted",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "824"
      },
      "used_columns": [
        "id",
        "title",
        "body"
      ],
      "attached_condition": "(match `test`.`article`.`title` against ('小米'))"
    }
  }
}

9、事务

9.1、ACID

原子性、一致性、隔离性、持久性

原子性：事务的操作要不全部成功，要不全部失败。原子性通过undolog日志完成
一致性：使用事务的最终目的，由业务代码逻辑保证，比如try catch
隔离性：在事务并发执行时，他们内部操作不能相互干扰，比如mysql中读未提交、读已提交、可重复读、串行化，其都是由Mysql中各种锁以及MVCC机制来实现的
持久性：一但提交了事务，它对数据库的改变是永久性的，持久性由redo log日志来保证

9.2、四大隔离级别

读未提交：可以读取到别的事务还没提交的数据，会引起脏读的问题

-- 事务一：读未提交
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';

begin;

update account set name = 'yyh' where id = 1;

-- 事务二：读未提交
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';

select * from account;

读已提交：只能读取到别的事务已经提交的数据，但是如果在事务一还没有提交之前事务二进行了一次数据的操作，然后事务一提交之后事务二又进行了一次数据的操作，那在事务二中这两次操作数据完全不一致，就会导致不可重复读的问题
1
2
3
4
5
6
7
8
-- 事务二：读已提交
SET SESSION transaction_isolation = 'READ-COMMITTED';

begin;

update account set name = 'yyh' where id = 1;

commit;
1
2
3
4
5
6
7
8
9
10
-- 事务二：读已提交
SET SESSION transaction_isolation = 'READ-COMMITTED';

begin;

select * from account;

select * from account;

commit;

可重复读：第一次查到的数据不管别的事务是否操作，第二次查到的都是相同的数据。但是如果在第二次事务进行了update这种操作时候。但是会出现脏写的问题。比如第二个事务读取到第一个事务没有提交之前的cash比如300，但是第一个事务提交后就变成了400，但是第二个事务将cash变成了 300 + 200，而不是400 + 200，从而导致了cash的脏写问题，注意这些都是Java代码层面而不是数据库层面

还有当两次事务操作中如果插入了某一条数据，而另外一个事务是对整个表进行的统计，那这里会产生幻读问题，，前后两次范围查询得到的结果集“行数”不一致

底层使用了MVCC，实现了读和写不阻塞的

-- 事务三：可重复读
SET SESSION transaction_isolation = 'REPEATABLE-READ';

begin;

update account set cash = cash + 100 where id = 1;

commit;

-- 事务三：可重复读
SET SESSION transaction_isolation = 'REPEATABLE-READ';

begin;

select * from account;

select * from account where id = 1;

update account set cash = cash + 200 where id = 1;

select * from account;

commit;

串行化：使用的读锁和写锁，性能极差，但是并发安全，同长不会使用串行化，为了解决可重复读中脏写问题会使用乐观锁CAS + 数据库版本versionId进行 或者直接采用

9.3、MVCC

多版本并发控制，我的总结就是：类似Git的版本链，可以一步步找到之前的旧版本数据

MVCC是解决事务隔离级别中的读已提交和可重复读

读未提交：每次查询会创建一个新的Read View
可重复度：同样的查询只会第一次创建Read View读取数据

InnoDB 里每开启一个 SELECT（注意不是 DML）语句，如果当前隔离级别 ≥ RC，就会分配一个 一致性视图（consistent read view），源码里叫 read_view_t。
它其实只存 4 个全局事务号：

read_view::creator_trx_id   // 本事务的 trx_id（如果是只读 SELECT，则为 0）
read_view::low_limit_id     // 生成视图时，下一个将分配的事务号 = max_trx_id + 1
read_view::up_limit_id      // 生成视图时，活跃事务列表中的最小 trx_id
read_view::m_ids[]          // 生成视图时，所有“还未提交”的 trx_id 数组

生成过程拿的是 全局 trx_sys->mutex，所以是“瞬间快照”，后面别的事务再怎么开启、提交，都不会影响这张视图。

对行记录里的两个隐藏列：

1 2	DB_TRX_ID （最后修改它的事务 id） DB_ROLL_PTR（指向 undo 链的指针）

算法如下：

if  trx_id == read_view->creator_trx_id        → 可见（自己改的）
else if trx_id < read_view->up_limit_id        → 可见（事务已提交）
else if trx_id >= read_view->low_limit_id     → 不可见（快照后才开始）
else if trx_id 存在于 m_ids[]                 → 不可见（生成快照时还活着）
else                                           → 可见（生成快照时已提交）

一旦不可见，就顺着 undo log 链 向前找更早的版本，再对新版本重复上述判断，直到找到第一个“可见”副本——这就是 多版本并发控制（MVCC） 的核心。

RC 级别每条 SELECT 都会重新生成一个新的 ReadView（快照点不同）。
步骤：

事务 A 启动，第一次 SELECT 生成 ReadView_V1；

事务 B 修改某行但 未提交；

该行最新版本 trx_id = B，尚在 m_ids[] 里 → 对 A 不可见；

A 顺着 undo 找到 B 之前的已提交版本 → 读到“老值”，不会脏读；

事务 B 提交；

事务 A 在同一条事务里再发一次 SELECT，会重新建 ReadView_V2；

此时 B 已不在 m_ids[] → 最新版本可见；

同一条事务里两次读结果不同 → 不可重复读 允许，但 脏读已杜绝。

结论：
ReadView 的“每次重新快照”机制，把“未提交”版本挡在门外，从而解决了 RU 会遇到的脏读问题。

10、分库分表

垂直分库：把“原来放在一起的表”按业务搬到不同数据库，解决 连接/CPU/内存 瓶颈；
水平分表：把“同一张表的行”按分片键拆成多张结构相同的小表，解决 数据量/索引/磁盘 瓶颈。

11、慢SQL优化

首先在业务中峰期，临时开启慢查询日志

-- 1. 打开慢日志功能
SET GLOBAL slow_query_log = ON;

-- 2. 设置慢阈值，单位秒，支持小数
SET GLOBAL long_query_time = 1;      -- 1 秒，可按需改 0.1

-- 3. 指定日志文件路径（需保证 mysql 用户可写）
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 4. 如果想把“没走索引”的语句也记进来
SET GLOBAL log_queries_not_using_indexes = ON;

在Mysql8之前使用mysqldumpslow分析慢查询日志，Mysql8之后采用pt-query-digest.pl进行分析

# 8.x 之前
perl mysqldumpslow -s t -t 10 E:\develop\mysql-8.0.31-winx64\data\DESKTOP-S7CC1J2-slow.log

# 8.x 之后
perl pt-query-digest.pl --limit 10 F:\slow.log > slow_report.txt

使用explain进行分析，是否走索引，分值多少
要考虑是不是最左前缀或%是否放到了前面
查看是否数据量大于两千万，如果大于两千五是不是考虑分表

12、分布式主键ID

【Java后端开发面试必问：布式ID雪花算法实战，花90分钟看完，java面试直接加分！】 https://www.bilibili.com/video/BV1FPW2z6EUB/?p=4&share_source=copy_web&vd_source=b1cd12cd9d40457a3baf3133ec496e39

自增ID：不推荐当主键，在分布式情况下会导致查表冲突，当查询一个ID为3的数据的时候会在不同的表中查出相同的ID，会导致冲突。
UUID：可以当主键但是会导致性能下降，在InnoDB存储引擎中索引存储方式是B+Tree，由于是有序的，但UUID是无序的所以每次插入新数据会导致整棵树进行重排序，导致性能下降。但mysql8.0新增了一个Bin——UUID会使用低位高位这种保证UUID的有序性。
雪花算法：全局唯一、趋势递增、信息安全，但是不保证单调递增。雪花算法强依赖于时间戳，存在时钟回拨问题（Linux时间比真实时间快几秒）需要通过网络时间校准和人工设置（这里需要改变机器码或序列化中的组成，添加一个时钟序列）

13、各种锁

全局锁、表级锁、行级锁、共享锁、排他锁、意向锁、记录锁、间隙锁、临键锁、悲观锁、乐观锁、MVCC、幻读、死锁

锁的粒度从大到小
- 全局锁（锁住整个库，可用于全库备份）
- 表锁（锁住整张表，MyISAM引擎）
- 行锁（锁住单行数据，InnoDB，容易死锁）
锁的模式
- 悲观锁（写多读少）：使用select ... for update，适用于秒杀场景
- 乐观锁（读多写少）：使用版本号或时间戳，适用于文章改动
锁的属性
- 共享锁：读读共享，读写互斥，使用lock in share mode
- 排他锁：写写互斥，读写互斥，普通快照读是可以，使用for update
InnoDB特有
- 意向锁：就是提高加锁的效率，比如事务A加了一个行锁，事务B需要加表锁时候就可以不用全表扫描是否存在行锁，避免冲突，意向锁直接在门口加了一个通知，提高了加锁的效率
- 记录锁：通过唯一索引，进行等值查询时候会触发，只锁定命中的这一行
  1
  select * from t where id = 10 for update
- 间隙锁：锁定一个开区间，不锁记录本身。防止间隙中插入新数据
- 临键锁：记录锁+临键锁的组合，记录一个左开右闭区间
  
  “MVCC + 临键锁 + 间隙锁”合起来把快照读和当前读两条路都封死，在 REPEATABLE READ 隔离级别下，防止幻读