mysql学习总结-优快云博客

SELECT 
    category,
    SUM(amount) AS total_sales,
    COUNT(order_id) AS order_count
FROM orders
WHERE 
    -- 时间范围筛选
    order_date BETWEEN '2023-01-01' AND '2023-12-31'
    -- 地区多值匹配
    AND region IN ('华东', '华南', '华北')
    -- 状态逻辑组合（OR）
    AND (status = '已完成' OR status = '已发货')
    -- 金额过滤
    AND amount > 1000
    -- 类别模糊匹配（排除"电子配件"）
    AND category LIKE '电子%'
    AND category NOT LIKE '%配件%'
GROUP BY category
HAVING 
    -- 分组后过滤
    SUM(amount) > 50000
ORDER BY 
    total_sales DESC
LIMIT 10;  -- 限制返回行数

order by desc 降序

union和join的区别

union更多是处理水平分表列数不变行数变两张表最好是相同的两张表不相同的话查询的字段个数要相同查到的数据按个数排序

Union 前后都是要select语句 select * from test UNION ALL select * from test2

union和union all的区别 all是没去重单独的union去重

连表查询

join更多是处理两张有关系的表一般都是有外键的表两张表不同的行不变列变

inner join/join 内连接左右两个表需要符合条件的才输出没有条件则笛卡尔积

外连接

left join/ left outer join on 左表全部输出右表符合条件的才输出

right join/right outer join on 右表全部输出左表符合条件才输出

和on连用

where having on这三者都是条件过滤使用时期不同

on是和join一起用优先级高于where

having 是和group by一起使用 having 聚合字段

where 就是条件过滤不能使用聚合函数

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 5000  -- 先过滤工资
GROUP BY department
HAVING COUNT(*) > 10;  -- 再过滤部门人数

范式

三大范式

字段是不可分割的单元

存在主键依赖主键

非主键之间不互相依赖

mysql执行流程

查询缓存这个在mysql8.0后被废弃了

词法分析找出关键字和非关键字

语法分析判断是否符合mysql的语法

预处理器判断对应的字段和表是否存在

优化器处理下where 的字段的顺序

架构和引擎

主要是innodb 有时用myisam（以读为主，会记录数据的数量）

MyISAM 与 InnoDB两种引擎中索引的区别

MyISAM的索引方式都是“非聚簇”的，主键索引和二级索引的叶子节点都是关键字加数据的地址没有回表的说法

InnoDB包含1个聚簇索引主键索引是聚集索引二级索引的叶子节点是关键字加主键值然后回表查询找到对应的数据

在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录

InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。

MyISAM的查找数据是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

Inndb支持外键事务行锁 myisam不支持

存储过程和函数

函数有返回值 funtion为标识符不可单独使用例如 max() min()

存储过程有In out inout参数 procedure为标识符是用户定义的一系列sql语句的集合可单独使用 call()

索引和优化

索引分为：

主键索引（不为null）

一般索引

唯一索引

组合索引（最左匹配原则）

全文索引（myisam）

索引说白了就是空间换时间多占用存储空间换取查询的速度

好处就是加快了查询坏处就是多用存储空间并且修改数据时索引也需要修改修改数据时时间增加

聚簇索引和非聚簇索引

主键索引叶子结点中有关键字和值

其他索引一般是非聚簇索引叶子结点只有关键字和主键找到主键后回表查询主键索引

索引覆盖

叶子结点的关键字和值刚好是要查询的数据不用回表

索引下推

非主键索引查询到关键字和值后根据查询条件先筛选一波后再回表而不是先回表再筛选

索引结构

索引的底层一般用b+树也有hash

b树和b+树的区别

b树是每个结点都有关键字和值

b+树的非叶子结点只有关键字

叶子结点中有全部的关键字和值并且用链表链接方便范围查询

非叶子结点只有关键字所以可以存多点关键字一个结点就需要一次io

区别

MySQL为什么使用B+树？B+树和B树的区别_为什么mysql用b+树不用b树-优快云博客

分析explain

ID

代表执行 select 子句或操作表的顺序，如果包含子查询，则会出现多个 ID。值越大，优先级越高，越先被执行。值相同的按照由上至下的顺序执行。

select_type（查询类型）

查询类型主要用于区别普通查询、联合查询以及子查询等复杂查询。

table

查询的表

type

查询扫描情况，最好到最差依次是：system>const>eq_ref>ref>range>index>All，一般情况下至少保证达到 range 级别，最好能达到 ref。

system: 表中只有一条数据，这个类型是特殊的 const 类型。
const: 针对主键或唯一索引的等值查询扫描，表示通过索引一次就找到了，最多只返回一行数据。 const 查询速度非常快，因为它仅仅读取一次即可。例如下面的这个查询，它使用了主键索引，因此 type 就是 const 类型的：explain select * from user_info where id = 2；
eq_ref: 唯一性索引扫描此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。例如：explain select * from user_info, order_info where user_info.id = order_info.user_id;
ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询。例如下面这个例子中，就使用到了 ref 类型的查询：explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5
range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。例如下面的例子就是一个范围查询：explain select * from user_info where id between 2 and 8；
index: 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段会显示 Using index。
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说，我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。如一个查询是 ALL 类型查询，那么一般来说可以对相应的字段添加索引来避免。
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询到的索引不一定是真正被使用。

key

实际使用的索引，如果为 null 则表示没有使用索引。因此会出现 possible_keys 列有可能被用到的索引，但是 key 列为 null。

key_len

表示索引中使用的字节数，在不损失精确性的情况下长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。即 key_len 是根据表定义计算而来。

ref

显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况，估算出找到所需的记录所需要读取的行数。

Extra

索引失效

1ike 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效；

2or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有左右查询字段均为索引时，才会生效；

3联合索引不使用第一列，索引失效；或者不按照从左到右使用列则失效并且在第一个范围查询后的字段失效当前范围查询还是用到索引

4数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描；

5在索引列上使用 IS NULL 或 IS NOT NULL操作。最好给列设置默认值。

6在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。

7对索引字段进行计算操作、字段上使用函数。

8当 MySQL 觉得全表扫描更快时（数据少）;
【MySQL 进阶之路】索引失效的11种情况_mysql索引失效的几种情况-优快云博客

表数据存储结构

表空间由段（segment）、区（extent）、页（page）、行（row）组成

1、行（row）数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

2、页（page）记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的

3、区（extent）我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。 B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机 I/O，随机 I/O 是非常慢的。解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。那具体怎么解决呢？在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

4、段（segment）表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。索引段：存放 B + 树的非叶子节点的区的集合；数据段：存放 B + 树的叶子节点的区的集合；回滚段：存放的是回滚数据的区的集合

锁

类型区分

读锁写锁

读锁与读锁不互斥读锁和写锁互斥

写锁和读锁写锁互斥

在读未提交读已提交可重复读级别下查询语句不加读锁修改语句加写锁

在串行化级别下查询语句加读锁修改语句加写锁

查询语句也可手动加锁

select ... lock in share mode; 加读锁

select ... for update;加写锁

事务a加了读锁那么事务a只可以读不可以修改数据事务b也可以加读锁不可以修改数据

事务a加了写锁那么事务a可读可修改不可以加读锁事务b不可以加读锁和修改数据

mysql中修改数据都是加写锁的

粒度区分

表锁：给整个表加锁行锁：给某行或者多行数据加锁

表锁

单纯的表锁

元数据锁（mdl）

mysql自动加对表里的数据做curd时加mdl读锁修改表结构时加mdl写锁

意向锁

给表里的行数据加行读锁时同时也会加意向读锁

给表里的行数据加行写锁同时也会加意向写锁

意向锁之间和行锁不会有冲突只会跟单纯的表锁有冲突主要是为了加表锁时加快效率（某一时刻给表加写锁时需要遍历所有的行数据是否有加读锁或者写锁现在有了意向锁直接看有没有意向锁就可以了）

auto-inc锁

主键递增锁插入数据时加锁执行完语句就解锁

行锁

记录锁（行锁）：record lock，即锁住一条记录

间隙锁：gap lock，即锁定一个区间，左开右开

临键锁：next-key lock 记录锁+间隙锁锁定的区间，左开右闭

varchar和char的区别

char是定长的

varchar是不定长的

事务

事务遵循acid原则

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

个人理解事务就是一堆的sql语句有点像存储过程但事务不同在于要遵循acid原则

每个事务类似于线程事务结束线程就消亡但是事务会操作数据库数据相当于操作全局变量

golang语言中可以运用channel或者mutex之类处理 mysql运用的就是锁处理修改数据的操作不过mysql事务是结束事务时自动释放写锁在读的时候根据隔离级别使用mvcc或者读锁

隔离级别

事务隔离级别分成四个

读未提交read-uncommitted

读已提交read-commtted

可重复读repeatable-read

串行化serializable

隔离级别不同会有不同的影响

存在的问题

脏读不可重复读幻读

根据测试

读未提交时两个事务在读的时候都没做处理写时加了写锁（行锁）提交后释放

有脏读不可重复读幻读的问题

读已提交时两个事务在读的时候都没做处理（mvcc）写时加了写锁（行锁）提交后释放

有不可重复读幻读的问题

可重复读两个事务在读的时候都没加锁用了（mvcc）写时加了写锁（行锁）提交后释放

很多程度上避免了幻读但还是有幻读的问题

主要矛盾在于select是快照读修改语句和select ... for update是当前读

当前读是可以读到其他事务已经提交了的最新数据

串行化两个事务在读的时候都加了读锁（行锁）提交后释放写时加了写锁（行锁）提交后释放

在读未提交时和可重复读使用mvcc版本控制也可以叫乐观锁

undolog中给每个记录加上了隐藏列上一次修改的事务id（有可能小的事务id在最新的位置大的事务id在之前历史的位置）, 删除这条数据的事务id 上一个版本的指针

每个事务开始时都会获得一个唯一的事务id 由系统分配分配后递增

使用mvcc时当前事务第一次select时会生成一个readview 会记录当前活跃的事务id 系统将要分配的事务id 当前最小的事务id

然后去查找记录中undolog中的判断上一次修改的事务id不属于当前活跃的事务id和小于系统将要分配的事务id 则可见否则不可见就要根据上一个版本的指针去寻找

读未提交时和可重复读的不同在于

读未提交时每次select时都会生成新的readview

可重复读只在第一次select时生成readview

undolog中在事务未提交时的修改也会有记录因为当前只可能有一个事务可以对记录进行修改所以对回滚不会有影响

读取数据分为快照读和当前读

增删改都是当前读特殊的查也是当前读

读已提交和可重复读隔离级别都是快照读快照读就是读历史数据并不是当前的数据

select 不加锁快照读

select .... lock in share mode 加读行锁

select ..... for update 当前读加写间隙锁

修改语句加写行锁

慢日志查询

set global slow_query_log='ON';

select * from mysql.slow_log;

slow_query_log 查看耗时长的语句

show profile 分析这个语句的性能

集群

设置一个主mysql 对应一个或者多个从mysql

主从复制

主从复制能实现读写分离容灾备份

在my.ini设置服务器id

然后主mysql开启binlog日志记录了所有sql语句允许从mysql连接从mysql去连接主mysql

主mysql的有个线程更新操作（Insert delete update）会写入到binlog中每个从mysql都在主mysql有一个线程发送 binlog内容

从mysql有个Io线程读取主Mysql的binlog 并把内容写入到relaylog中

再启一个线程将relaylog中的应用到本身的数据库中

分为

异步复制半同步复制同步复制

默认的主从复制方式就是异步复制

异步复制：主库提交事务后无需等待从库确认，可能导致数据滞后。
半同步复制：主库等待至少一个从库确认，以减少数据丢失风险。
半同步复制需要主mysql收到至少一个从mysql返回确认 mysql写入到relaylog就会返回
同步复制：所有从库必须确认后才提交事务，延迟较大

问题

主数据丢失用半同步解决

主从同步会有延迟从数据库只有一个sql thread 复制可能延迟

并行复制：从库多线程apply binlog

mysql和redis一致性

1延迟双删

2canal

3kafka

4只修改redis然后redis更新到Mysql

5先更新mysql再删redis

Mysql连接和权限

mysql -u root -p连接在用户中设置权限

日志

mysql一共有这些日志

重做日志（redo log）：物理日志
作用是确保事务的持久性。 redo 日志记录事务执行后的状态，用来恢复未写入 data file 的已提交事务数据。

回滚日志（undo log）：逻辑日志
作用是保证数据的原子性。保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

二进制日志（binlog）：逻辑日志
常用于主从同步或数据同步中，也可用于数据库基于时间点的还原。

错误日志（errorlog）
记录着 MySQL 启动和停止，以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。

普通查询日志（general query log）
记录了服务器接收到的每一个命令，无论命令语句是否正确，因此会带来不小开销，所以也是默认关闭的。

慢查询日志（slow query log）
记录执行时间过长和没有使用索引的查询语句（默认 10s），同时只会记录执行成功的语句。

中继日志（relay log）
在从节点中存储接收到的 binlog 日志内容，用于主从同步

undolog：

保证原子性记录的是数据的值引擎生成的日志 undolog的修改会修改buffer pool中的undo 页

buffer pool中的数据变成

redolog：

保证持久性记录的是数据的值引擎生成的日志

redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

binlog：

二进制文件主从复制根据配置可记录数据的值或者sql语句

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会会覆盖以前的日志，保存的是全量的日志

undolog记录的东西事务id就是实现mvcc的关键

buffer pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log。redolog 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的。

事务执行时log的操作顺序

事务开始

undolog写入

redolog写入undolog的修改刷盘

执行操作语句

mysql缓存区存在脏页

写入redolog

redolog 设置为prepare落盘

binlog数据落盘

提交事务

redolog设置为commit落盘

buffer pool

Innodb 存储引擎设计了一个缓冲池 (Buffer Pool), 来提高数据库的读写性能

Buffer Pool 以页为单位缓冲数据，可以通过参数调整缓冲池的大小，默认是 innodb_buffer_pool_size 128 М.

Innodb 通过三种链表来管理缓页:

Free List (空闲页链表), 管理空闲页；

Flush List (脏页链表), 管理脏页；

LRU List, 管理脏页 + 干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出

InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化:

将 LRU 链表分为 young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，目的是为了解决预读失效的问题。

当 **「页被访问」且「old 区域停留时间超过 innodb_old_blocks_time 阈值 (默认为 1 秒)」时，才会将页插入到 young 区域，否则还是插入到 old 区域，目的是为了解决此量数据访问，大量热数据淘汰的问题。

可以通过调整参数，设置 young 区域和 old 区域比例。

innodb_old_blocks_pct 在开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL, 这可因为脏页在刷新到磁盘时导致数据库性能抖动。如果在很短的时间出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

问题

假如脏页没写入mysql挂了怎么办

InnoDB 的更新操作采用的是 WriteAhead Log 策略，即先写日志，再写入磁盘，通过 redolog 日志让 MySQL 拥有了崩溃恢复能力。

下面几种情况会触发脏页的刷新:

当 redolog 日志满了的情况下，会主动触发脏页刷新到磁盘；

Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；

MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；

MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

mysql线上扩容

线上分表

在不停机的情况下进行数据迁移，需要采用增量迁移和双写同步策略，确保数据的一致性

先全量迁移

在全量数据迁移过程中，业务系统仍在不停地写入数据，因此需要对新增的数据进行增量迁移。这可以通过以下方式实现：

时间戳记录：在全量迁移开始时，记录下当前的最大时间戳。完成全量迁移后，将该时间戳之后的数据迁移到分表中。
双写策略：在全量迁移后，修改业务逻辑，使得新数据同时写入旧表和分表。这样可以保证即使在迁移过程中有新数据写入，也不会丢失。

全量迁移后开启增量迁移和双写

这样能保证再全量迁移期间的数据通过增量迁移过来

同时在增量迁移期间的数据通过双写过来