MySQL复习

前言

数据库概述

三大范式

第一范式 = 表字段要有原子性,不可再分。
第二范式 = 要有主键+其他字段依赖主键 , 表中每个记录能唯一区分。
第三范式 = 其他非主键外所有字段互不依赖。

反范式 - 冗余设计

空间换时间,简单来说就是冗余存储避免联表查数据了。不过有时候就要注意级联更新了。

事务

事务 = 一组数据库操作序列，要么全部执行要么全不执行。

事务的四个属性: 原子性 - 隔离性 - 持久性 - 一致性.
- 原子性 = 事务要么全部执行要么全部不执行。
- 隔离性 = 多个事务并发，一个事务的执行不影响其他事务。
- 持久性 = 事务一旦提交，修改则保存在磁盘中。
- 一致性 = 数据库中的数据应该满足完整性约束。

事务隔离级别

服务端的场景面对的

并行 = 同一时刻有多个客户端做读写操作者可能会操纵多个表和多个数据。
并发 = 同一时刻服务端可能开启多个线程可能会操纵多个表和多个数据。
假设没有事务这个机制 , 那么会导致数据不一致的问题简单总结是下面三中情况。比如你修改了一个值那么假设同一时刻就是有个客户端请求给你改了回来。比如并发的爬虫就是不断的买商品,而且肯定是多线程来操作，每次先查询然后再尝试减数据 / 或者直接对购买请求一直发请求。
解决方案就是就是隔离级别最简单的就是串行化直接不并发了一个个来,其他方法都是为了减少锁力度增加性能的方案。

三种事务并发的问题
- 脏读 = 读到了别的事务的脏数据 = 事务a读取到了事务b修改但是未提交的值，最后这个修改撤回。脏数据 = 旧值 / 无效值。
- 不可重复读 = 事务a , 对于同一行记录读取 , 显示的结果不一样 , 因为中间别的事务修改了。
- 幻读 = 事务a读取一定范围的数据 , 事务b对其范围进行新增或者删除导致范围数据行数不一致。
四种事务隔离级别

mysql默认隔离级别 = RR。Oracle默认隔离级别 = RC。
1. 串行化
2. 可重复读 RR
3. 读已提交 RC
4. 读未提交 RU
事务实现的原理

Innodb中通过 MVCC解决脏读和不可重复读。通过MVCC+间隙锁解决幻读。

三大日志

undo log

在事务中每条sql通过执行器后 , 第一时间申请 undo log页并写入磁盘中 , 保证操纵数据前每次undo log是完整的 ⇒ 保证事务发生异常可以回滚数据 ⇒ 实现mysql中acid的原子性。

redo log

redo log 用于恢复数据让mysql有了崩溃恢复的能力 = 容器挂了通过 redo log 来恢复数据的 ⇒ 数据的持久性和完整性。

刷盘时机 = 持久化redo log的时机
- 事务提交：当事务提交时，log buffer 里的 redo log 会被刷新到磁盘（可以通过innodb_flush_log_at_trx_commit参数控制，后文会提到）。
- log buffer 空间不足时：log buffer 中缓存的 redo log 已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
- 事务日志缓冲区满：InnoDB 使用一个事务日志缓冲区（transaction log buffer）来暂时存储事务的重做日志条目。当缓冲区满时，会触发日志的刷新，将日志写入磁盘。
- Checkpoint（检查点）：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。
- 后台刷新线程：InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地将脏页（已修改但尚未写入磁盘的数据页）刷新到磁盘，并将相关的重做日志一同刷新。
- 正常关闭服务器：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

bin log

bin log 是mysql记录数据库中所有 DDL语句和DML语句的二进制日志、用来支持 { 数据备份 - 灾难恢复 - 数据复制 } , 保证了数据一致性。

bin log 写入时机

事务开始写入 bin log cache , 事务提交写入page cache 最后再持久化到磁盘中。

所以对于bin log来说如果容器突然下线可能会丢失 page cache 没有持久化到磁盘中的事务数据。

二阶段提交

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。因为提交的时机不一致可能会导致 redo log和binlog 不一致 , mysql通过两阶段提交来解决。两阶段 = prepare + commit.

索引

索引 = 快速查询和检索数据的数据结构 = 排序好的数据结构。索引的底层数据结构很多{ b树 b+树和hash 红黑树 }

索引的数据结构
- 二叉查找树 BST = 小中大
  - 左子树所有节点值 < 根节点
  - 右字数所有节点值 > 根节点
  - 左右子树分别都是二叉查找树
- AVL 树 = 平衡二叉查找树
- 红黑树 = 自平衡的二叉查找树
- B树 = B-树 = 多路平衡查找树
- B+树 = 只有叶子节点存放data其他节点存放key
索引类型总结
- 主键索引
  
  主键列使用的就是主键索引。
- 二级索引
  
  二级索引 = 辅助索引 , 二级索引的叶子节点存储的数据是主键 , 回表查询数据。
  - 唯一索引
  - 普通索引
  - 前缀索引
  - 全文索引
索引覆盖 + 联合索引 + 最左前缀匹配原则
- 覆盖索引 = 查询的字段正好是索引的字段 — 避免了回表查询数据
- 联合索引 = 使用多个字段创建索引。
- 最左前缀匹配原则 = 最左前缀 = 最左优先,以最左边为七点任何连续的索引都能走上。同时遇到范围查询(>、<、between、like)就会停止匹配。在联合索引中 , 根据联合索引的字段顺序进行匹配 , 顺序=左到右一次。
索引下推

索引下推 = 在辅助索引的遍历过程中 , 先对于索引中包含的字段进行条件判断 , 过滤数据减少回表次数。
索引失效

美团暑期实习一面：MySQl 索引失效的场景有哪些？

（二）MySQL索引篇-2：详解MySQL索引失效、索引实战及慢查询分析 - 掘金
- 创建了组合索引，但查询条件未准守最左匹配原则;
- 在索引列上进行计算、函数、类型转换等操作;
- 以 % 开头的 LIKE 查询比如 LIKE '%abc';;
- 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
- IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
- 发生隐式转换open in new window;
索引正确的选择
- 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
- 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
- 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
- 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
- 执行计划分析

慢SQL优化

SQL执行计划分析

MySQL Explain详解+实战 - 掘金

简单来说就是通过使用 explain 语句来分析sql的执行计划。explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。

id = id越大优先级越高越先执行
select_type = 查询的类型
table = 表名
partition = 当前操作涉及到的分区。
type = 表示何种类型。
possible_keys = 此次查询可能选用的索引。
key = 此次查询确切使用到的索引
key_len = 索引的长度
ref = 表示那些列或常量被用来与key列命中的索引进行比较。
rows = 找到的记录数量。
filtered = 存储引擎返回的数据在经过过滤后的记录数量的比例。
extra = 额外的信息说明

type属性的各种情况
- system = 系统表少量数据往往不需要磁盘IO
- const = 常数索引
  - 使用唯一性索引做唯一查询
- eq_ref = 唯一索引扫描 , 智慧扫描索引树中的一个匹配行。
  - 当链接操作使用了唯一索引或者主键索引 , 并且链接条件是基于这些索引的等值条件时。
- ref = 非唯一索引扫描 , 只会扫描索引树中一部分来查询匹配的行
  - 使用非唯一索引进行查询
- range = 范围查询 , 只会扫描索引树中的一个范围类匹配行记录。
  - 使用索引进行范围查询
- index = 全索引扫描会遍历索引树来查找匹配行记录
  - 不符合最左前缀匹配的查询
- all = 全表扫描
Extra
- Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
- Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
- Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
- Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
- Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
- Using join buffer (Block Nested Loop) ：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

SQL优化沙箱模拟

数据量800W - 之前项目社会人员表信息差不多这么多。服务端的中间件和数据库是在政务云的要做什么都要申请(不够是架构师管听架构师说的) , 当时界面报超时就先优化sql然后扩大超时时间的timeout。

查询 - 地区信息(4个外键) + 姓名(模糊查询)

msql沙箱测试字符串作为索引查询条件

地区信息外键作为区分数据建立索引，满足最左前缀原则

# 18.4
explain
SELECT * FROM `0example_single_800w` where province_id = 587 
# 1.72
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and city_id = 466 
# 0.5 
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and city_id = 466 and region_id = 167 
# 0.8
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and city_id = 466 and region_id = 167 and district_id = 474

字符串作为索引部分 - 靠单个索引查询 - 普通索引

精确查询 - 模糊查询

精确查询

模糊查询

错误的模糊查询

## %开头的like是不走索引的
select * from 0example2000w where status_name like '%laios%';

全文索引

原理就是倒排索引同理es。

全文索引模糊查询其他条件+字符串条件 = 联合索引+普通索引

# ref - 0.9
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and status_name="Stephanie Fox"

# range - 18
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and status_name like "Stephanie%"

# ref = 0.5 
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and city_id = 466 and status_name like "Step%anie%"

# ref = 0.5
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and city_id = 466 and region_id = 167 and district_id = 474 and status_name like "Step%anie%"

联合索引+全文索引

都挺快的反正

# ref
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and city_id = 466 and region_id = 167 and district_id = 474 and  match(biz_info) against('Jd4z*' in Boolean MODE) 

# ref
explain
SELECT * FROM `0example_single_800w` where match(biz_info) against('Jd4z*' in Boolean MODE) and province_id = 587 and city_id = 466 and region_id = 167 and district_id = 474 

# fulltext
explain
SELECT * FROM `0example_single_800w` where province_id = 587 and  match(biz_info) against('Jd4z*' in Boolean MODE) 

# fulltext
explain
SELECT * FROM `0example_single_800w` where province_id = 587  and city_id = 466 and  match(biz_info) against('Jd4z*' in Boolean MODE) 

# ref
explain
SELECT * FROM `0example_single_800w` where province_id = 587  and city_id = 466 and region_id = 167 and  match(biz_info) against('Jd4z*' in Boolean MODE)

建立普通索引

explain
SELECT * FROM `0example_single` where status_name = "name" # ref

explain
SELECT * FROM `0example_single` where status_name like "name" # range

explain
SELECT * FROM `0example_single` where status_name like "%name" # all

explain
SELECT * FROM `0example_single` where status_name like "name%" # range

explain
SELECT * FROM `0example_single` where status_name like "na%me" # range

建立全文索引

# type=fulltext ref=const
explain
SELECT * FROM `0example_single` where match(biz_info) against('name')

好文全部的sql命令以及查询的各个场景讲了字符串模糊查询的各个方式 MySQL 全文索引 MySQL 用全文索引解决模糊查询

ES优化查询

ES生产最终实现的效果就是复杂的字符串查找记录=多个字符串的字段查找相关的记录或者skuId列表。实现效果.

电商场景实例代码查询条件的构建 = 多String字段的条件拼接 127、全文检索-ElasticSearch-整合-测试复杂检索_"avgaggregationbuilder balanceaggbuilder = aggrega-CSDN博客

前序步骤 = es的数据同步(crud) = gulimall里是有个sku列表中上架/下架按钮同步单挑数据到es中。 15-GuliMall ElasticSearch复杂检索_gulimall es测试数据-CSDN博客

电商场景-开源mall-seach模块-多个字符串字段组合查询相关的spu列表

macro/mall 这个服务当前暴露的接口就是 crud+批量导入+主要看下搜索的接口

es仓储层和jpa差不多+ElasticsearchRestTemplate

public interface EsProductRepository extends ElasticsearchRepository<EsProduct, Long> {
    /**
     * 搜索查询
     *
     * @param name              商品名称
     * @param subTitle          商品标题
     * @param keywords          商品关键字
     * @param page              分页信息
     */
    Page<EsProduct> findByNameOrSubTitleOrKeywords(String name, String subTitle, String keywords,Pageable page);

}

Service层实现的功能 =

商品名称 ==> 分页查询es的EsProduct（pms索引）
[ 商品名称 . 分类ID ，品牌ID ] ==> 查询ES的EsProduct
根据 mysql中spu中的信息来推荐（获取相关字段设置到es的模型然后查询列表）

具体实现和mp差不多就是构建查询的条件 mall-search/src/main/java/com/macro/mall/search/service/impl/EsProductServiceImpl.java · macro/mall - 码云 - 开源中国 (gitee.com)

电商场景-gulimall-搜索模块

这里使用的是es的某个客户端的依赖

<dependency>
    <groupId>org.elasticsearch.client</groupId>
    <artifactId>elasticsearch-rest-high-level-client</artifactId>
    <version>7.6.2</version>
</dependency>

gulimall中es放的是 sku的信息包含了基本信息+价格+规格参数列表

@Data
public class SkuEsModel {

    private Long skuId;

    private Long spuId;

    private String skuTitle;

    private BigDecimal skuPrice;

    private String skuImg;

    private Long saleCount;

    private Boolean hasStock;

    private Long hotScore;

    private Long brandId;

    private Long catalogId;

    private String brandName;

    private String brandImg;

    private String catalogName;

    private List<Attrs> attrs;

    @Data
    public static class Attrs {

        private Long attrId;

        private String attrName;

        private String attrValue;

    }


}

也是构建DSL语句

buildSearchRequest()
逻辑就是封装入参传来的条件 
字符串条件是 skuTitle 分类id 品牌id 
然后有个价格区间

索引失效排查

索引失效有几大失效场景

简单总结就是对于条件部分的索引列传入的值你别动就完事了 , 你就直接传值啥事没有。现在mysql还会自动帮你优化顺序。

常见失效场景

or 条件查询 and走索引基本没有什么问题但是主要场景的条件是or的组合逻辑


## union all 拼接 - 去重后面再处理
## 条件就是两个辅助索引列 这里就直接用provinveID了反正
# range
explain 
select * from 0example_single_800w where province_id=1000 or province_id=965 

# ref = 80m = 11685 = 4011 + 7674
explain
select * from 0example_single_800w where province_id=1000 
UNION all
select * from 0example_single_800w where province_id=965 

# 外面去重 里面是ref
explain
select distinct * from (
	select * from 0example_single_800w where province_id=1000 
	UNION all
	select * from 0example_single_800w where province_id=965 
) as t

深度分页优化

<https://juejin.cn/post/7230979300828151865?searchId=2024031316002721562618000EC7E662AD>

is not null 不走索引

## ref
explain 
select * from 0example_single_800w where province_id is null

## all
explain 
select * from 0example_single_800w where province_id is not null

范围查询 - not in ，not exists不走索引

# 走索引的普通场景
########################################## in 就是range就不写了 

########################################## between and 
# range
explain 
select * from 0example_single_800w where id BETWEEN 100 and 200

#all = 基数太少 = 全表
explain 
select * from 0example_single_800w where province_id BETWEEN 100 and 200

# 

# range
explain 
select * from 0example_single_800w FORCE INDEX (area_info)  where province_id BETWEEN 100 and 200

# not in 不走索引场景
## range = 主键的话是range
explain 
select * from 0example_single_800w where province_id in (1,2,3)

## all = 普通索引的话 就失效了
explain 
select * from 0example_single_800w where province_id not in (1,2,3)

## 解决方案 = range
explain 
select * from 0example_single_800w where id not in (1,2,3)

错误使用like = 别 %开头就行
不等于比较

当查询条件为字符串时，使用”<>“或”!=“作为条件查询，有可能不走索引，但也不全是。

分库分表

索引的底层数据结构是B+树，数据量增长则影响树的高度。比如走了索引的查询树高度=3那么就是3次IO查询,高度是10的话就是10次IO查询。数据量影响的是后面走的树的高度，但是还是走了所以的。

为什么大家说mysql数据库单表最大两千万？依据是啥？ - 掘金

Mysql内部实现原理

查询sql执行流程

建立链接 - 查询缓存 - 分析器 - 优化器 - 执行器 - 引擎

写操作sql的执行流程讲一下

分析器 - 执行器 - 引擎 — redo log(prepare) - binlog - redo log(commit)

如何优化一个大规模的数据库系统

硬件优化
数据库设计
查询优化
索引优化
缓存
负载均衡
分区分片
数据备份和恢复
性能监控和调优

深度分页优化问题

mysql的limit m n 的工作原理是读取前面 m+n条记录然后舍弃m条返回n条作为结果集。m越大偏移量越大则性能越差。

深度分页优化手段

优化方案的具体解释 = 简单来说就是子查询走辅助索引直接找到id列表，然后外边直接是走主键索引来查询。当然where条件可以省略直接是优化分页。 mp.weixin.qq.com/s/mLfxK_934…

*   使用子查询和JOIN优化

<!---->

    select c1,c2...
    from table
    inner join (
    	select id from table 
    	where name = 'laios'
    	order by id
    	limit 10000000,10
    ) as subquery on table.id = subquery.id

使用子查询来获取限定条件的部分主键id然后用id获取行记录。

*   使用子查询+ID过滤优化

<!---->

    select * from table
    where name = 'laios'
    	and id > ( select id from table where name = 'laios' order by id limit 1000000,1 )
    order by id 
    limit 10

*   记录上一个ID
*   使用搜索引擎 ES。ES的全文搜索引擎能优化深度分页,不过ES自己也有深度分页的问题。

mysql-基础知识+索引相关+慢sql排查