Mushroom Notes
kinoko
2023-12-18
目录

Mysql 进阶篇

# Mysql主从复制

MySQL主从复制是一个异步的复制过程，底层是基于Mysql数据库自带的 二进制日志 功能。就是一台或多台MySQL数据库（slave，即从库）从另一台MySQL数据库（master，即主库）进行日志的复制，然后再解析日志并应用到自身，最终实现 从库 的数据和 主库 的数据保持一致。MySQL主从复制是MySQL数据库自带功能，无需借助第三方工具。

二进制日志：

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。默认MySQL是未开启该日志的。

MySQL的主从复制原理如下：
image.png
MySQL复制过程分成三步：

  1. MySQL master 将数据变更写入二进制日志( binary log)
  2. slave将master的binary log拷贝到它的中继日志（relay log）
  3. slave重做中继日志中的事件，将数据变更反映它自己的数据

我们需要做的事情:

  1. 在主库中开启binlog,设置server_id,且创建一个拥有副本复制权限的用户
  2. 查看主库状态,就是看日志文件和日志的偏移量(开始位置)
  3. 在从库上设置server_id,使用主库提供的用户监听主库日志,开启监听即可

# 具体操作步骤

# 准备工作

首先先把防火墙都关闭或者放行3306端口，注意若是使用虚拟机克隆来模拟主从数据库的话需要修改克隆虚拟机的ip地址以及mysql的server_uuid，去网上随机生成一个uuid替换即可

server_uuid目录：/var/lib/mysql/auto.cnf systemctl restart mysqld 重启mysql systemctl restart network 重启网络

关闭防火墙 方式一： firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --zone=public --list-ports

方案二： systemctl stop firewalld 关闭防火墙 systemctl disable firewalld 关闭开机自启

# 主库配置

1). 修改Mysql数据库的配置文件/etc/my.cnf

注意：在mysqld下面复制在最下面增加配置:

log-bin=mysql-bin   ##[必须]启用二进制日志
server-id=200       ##[必须]服务器唯一ID(唯一即可)
1
2

**2). 重启Mysql服务 ** systemctl restart mysqld

3). 创建数据同步的用户并授权

登录mysql，并执行如下指令，创建用户并授权：

GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by 'Root@123';

-- 刷新授权状态
flush privileges;
1
2
3
4

注：上面SQL的作用是创建一个用户 xiaoming ，密码为 Root@123456 ，并且给xiaoming用户授予REPLICATION SLAVE权限。常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制。

4). 登录Mysql数据库，查看master同步状态

执行SQLshow master status;，记录下结果中File和Position的值
image.png

# 从库配置

1). 修改Mysql数据库的配置文件/etc/my.cnf

server-id=201 	##[必须]服务器唯一ID
1

image.png

**2). 重启Mysql服务 **systemctl restart mysqld

3). 登录Mysql数据库，设置主库地址及同步位置

change master to master_host='192.168.78.128',master_user='xiaoming',master_password='Root@123',master_log_file='mysql-bin.000001',master_log_pos=436;

-- 启用从属关系
start slave;
1
2
3
4

若想重置从属关系可以输入stop slave;停止从属关系，然后重置主从状态reset master;

参数说明：
A. master_host : 主库的IP地址
B. master_user : 访问主库进行主从复制的用户名(上面在主库创建的)
C. master_password : 访问主库进行主从复制的用户名对应的密码
D. master_log_file : 从哪个日志文件开始同步(上述查询master状态中展示的有)
E. master_log_pos : 从指定日志文件的哪个位置开始同步(上述查询master状态中展示的有)

**4). 查看从数据库的状态 **show slave status;
然后通过状态信息中的 Slave_IO_running 和 Slave_SQL_running 可以看出主从同步是否就绪，如果这两个参数全为Yes，表示主从同步已经配置完成。
image.png

# MySQL的体系结构

image.png
MySQL的体系结构分为四层：连接层、服务层、引擎层、存储层

  • **连接层：**一些客户端和链接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • **服务层：**第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储器的功能也在这一层，如过程、函数等。
  • **引擎层：**存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，索引的结构也不一样。
  • **存储层：**主要是将数据存储到文件系统上，并完成与存储引擎的交互。

# 存储引擎

# InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是MySQL的默认存储引擎。

特点：

  • DML操作遵循ACID模型，支持事务；
  • 行级锁，提高并发访问性能；
  • 支持外键 FORIEGN KEY约束，保证数据的完整性和正确性；

文件
xxx.ibd：xxx代表的是表明，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

逻辑存储结构
image.png
可以理解成：图书馆（表空间）->书架（段）->书本（区）->书页（页）->一行字（行）->一个字

使用场景：
对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

# MyISAM（MongoDB）

MyISAM 是MySQL早期的默认存储引擎

特点：
不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

文件：
xxx.sdi：存储表结构信息
xxx.MYD：存储数据
xxx.MYI：存储索引

使用场景：
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，如日志、足迹之类的数据，那么可以选择MyISAM。

# Memory（Redis）

Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：
内存存放
hash索引（默认）

文件：
xxx.sdi：存储表结构信息

使用场景：
通常用于临时表及缓存，MEMORY的缺陷就是对表的大小又限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

# 三个常见引擎的区别

image.png

# 索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引优点：
提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

索引缺点：
索引也是要占用空间的，所以大大提高了查询效率，同时也降低更新表的熟度，如对表进行INSERT、UPDATE、DELETE时，要去维护索引的结构，向结构中增删元素，效率降低。

# 索引结构

image.png
image.png
一页能存约16K的记录，以bigint类型为例，2层结构的B+树可以存放18736条记录，3层结构可以存约21939856

# 索引分类

image.png
根据索引存放形式又分为两种
image.png
聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一索引作为聚集索引
  • 如果表没有主键，也没有唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

当查找的是聚集索引字段，由于聚集索引会将数据与索引存储到一起，所以可以快速定位到那行数据，如果查找的是二级索引，则会进行回表查询，通过二级索引字段查询到这行数据的聚集索引如id，再通过聚集索引查找。

# 索引语法

创建索引：CREATE INDEX idx_user_name ON tb_user(name);
创建索引且指定类型：CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
创建聚合索引：CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
查看索引：SHOW INDEX FROM tb_name;
删除索引：DORP INDEX index_name ON tb_name;

# SQL性能分析

SQL的执行频率：SHOW GLOBAL STATUS LIKE 'Com_______';

# 慢查询日志

慢查询日志记录了所有执行时间烧过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认是没有开启的，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
image.png
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息：
/var/lib/mysql/localhost-slow.log

查询慢sql日志log路径：show variables like '%slow_query_log%'

# profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：
SET profiling = 1;
image.png

# explain执行计划

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括SELECT语句执行过程中表如何连接和连接的顺序。
image.png
image.png
EXPLAIN执行计划各字段含义：

  • Id：

select查询的序列号，表示查询中执行select子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。

  • select_type：

表示SELECT的类型，常见的取值有SIMPLE（简单表 ，即不使用表连接或者子查询）、PRIMARY（主查询 ，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等

  • type：

表示连接类型，性能由好到差大致分为：NULL（一般为不查询表时）、system（一般为访问系统表时，或者表中只有一行数据时）、const（一般访问主键或唯一索引）、eq_ref、ref（访问非唯一性的索引）、range、index（用了索引，但需要对索引进行扫描，效率也较低）、all（全表扫描）。
image.png

  • possible_key：

显示可能应用在这张表上的索引，一个或多个。

  • Key：

实际使用的索引，如果为NULL，则没有使用索引。

  • Key_len：

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

  • ref：

列出是通过常量（const），还是某个表的某个字段来过滤的，就是筛选的指标类型

  • rows：

MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的。

  • filtered：

表示返回结果的行数占总读取行数的百分比，filtered的值越大越好。

# 索引使用

# 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

范围查询会使索引失效，如> < ，但是>= <=不会

# 索引失效情况

  • 索引列运算（如字符串切割等操作）
  • 字符串不加引号
  • 模糊查询百分号放前面
  • or连接条件中涉及没有建立索引的字段（不能为联合索引）
  • 为遵循最左前缀
  • 数据分布影响（MySQL评估）

# SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index(index_name)建议MySQL使用指定索引
ignore index(index_name)让MySQL忽略指定索引
force index(index_name)强制让MySQL使用指定索引

加在from后面

# 覆盖索引

当查询条件为非聚集索引字段时，且查询字段存在条件字段外的字段，会导致需要回表查询。反之会覆盖索引，也就是说，查询的条件与查询的字段相同，则可以直接从叶子节点中获取数据，不需要回表查询。

# 前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这样会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。
image.png

# 索引设计原则

image.png

# SQL优化

# 插入数据优化

如果一次性需要插入大量数据，使用insert语句插入性能较低，此时可以使用MySQL提供的load指令进行插入。
image.png

## 客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -u root -p
## 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
## 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table `table_user` fields terminated by ',' lines terminated by '\n';
1
2
3
4
5
6

# 主键优化

数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）
image.png
页分裂现象
页可以为空，也可以填充一般，也可以填充100%。每个页包含了2-N行数据（如果一行数据太大，会行溢出），根据主键排序。

正序插入
image.png
正序插入时，当页面占用已满，或者放不下下一行数据，则会申请新的页插入数据。

乱序插入
image.png
乱序插入时，根据主键排序，当发现插入位置所在的页已经无法容纳当前行时，会申请新的页，并且将原先页中的行进行对半分至新页中，再重新计算插入位置，并且重新维护双向指针。
image.png
可以看到当乱序插入时，需要涉及到重新分配，消耗CPU的额外性能，自然效率更低。

页合并
当删除一条记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到某个阈值（MERGE_THRESHOLD 默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

MERGE_THRESHOLD 合并页的阈值可以配置，在创建表或者创建索引时指定

所以说，在乱序插入的时候，由于页分裂时的重分配导致的页数据存储不完全，还会触发InnoDB的页合并机制，就无法避免的会增加CPU的开销，而顺序插入的话就可以最大限度的避免这些问题。

# 主键设计原则
  1. 满足业务需求的情况下，尽量降低主键的长度。因为在二级索引的叶子节点中挂的就是数据的主键，如果主键的长度比较长，二级索引的数量比较多，则会占用大量的磁盘空间，在搜索的时候也会消耗大量的磁盘IO。
  2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  3. 业务场景允许的情况下，尽量不要使用UUID做主键或是其他自然主键，如身份证号，雪花ID
  4. 业务操作时，尽量不要修改主键

# order by 优化

查看explain时，在Extra列中可以看到两种参数：

  • Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  • Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

在order by时尽量保证使用了索引，当有多个字段参与排序时要建立联合索引，并且保证建立时的索引排序与实际搜索时的排序一致，否则创建多一个逆向排序的索引来保证，多种排序情况都可以Using index。
image.png
image.png

# count 优化

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
  • InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累计数。

count的几种用法

count（主键）

  • InnoDB引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加，由于主键不可能为null，所以不需要判空。

count（字段）

  • 没有 not null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，技术累加。
  • 有 not null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count（1）

  • InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字"1"进去，直接按行进行累加。

count（*）

  • InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以尽量使用count()。

# update 优化

InnoDB引擎在执行update语句时，如果更新的字段有索引，根据默认的事务隔离级别，会加上行锁，但如果是没有索引的字段，则加的是表锁，会锁住整个表，影响效率，所以在使用update语句时，尽量的给需要频繁更新的字段加上索引以提高效率。

InnoDB的行锁是针对索引加的锁，不是针对记录加锁，并且该索引不能失效，否则会升级为表锁。

# 高性能数据库表设计原则及规范

# 建表设计原则

  1. 一定程度上可以根据业务采用反范式设计 :::info 范式化模型：数据没有冗余，更容易更新，但表的数量较多，查询数据需要多表关联时性能低下
    反范式化模型：冗余将带来很好的读取性能，对磁盘空间的消耗是可以接受的，但需要维护冗余数据，当冗余数据在多表中都存在时，维护的成本会越来越大，所以冗余数据尽量选择不经常改变的又经常会被查询的 :::

  2. 尽量设计成单表查询就能满足业务，避免多表关联，跨库查询

  3. 回归存储的基本职能，不做复杂运算，杜绝大事务、大SQL、大批量修改

  4. 使用innodb存储引擎 :::info mysql8.0已经将所有数据字典表转成了innodb，所有几乎不会再使用innodb以外的引擎了 :::

  5. 默认字符集uft8mb4

# 库表规范（参考阿里）

  1. 【强制】库、表、字段的名称必须控制在32个字符以内，表名只能使⽤字母、数字和下划线，⼀律⼩写，禁止出现数字开头，禁止两个下划线中间只出现数字。表名不使用复数名词。禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
  2. 【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8mb4。创建数据库SQL举例：create database db1 default character set utf8mb4;。
  3. 【强制】相关模块的表名与表名之间尽量提现join的关系，如user表和user_login表。
  4. 【强制】库的名称格式：业务系统名称_⼦系统名，同⼀模块使⽤的表名尽量使⽤统⼀前缀。
  5. 【强制】表名要求模块名强相关，如师资系统采⽤”sz”作为前缀，渠道系统采⽤”qd”作为前缀等。
  6. 【强制】⼀般分库名称命名格式是库通配名_编号，编号从0开始递增，⽐如wenda_001以时间进⾏分库的名称格式是“库通配名_时间”
  7. 【强制】小数类型为decimal，禁止使用float和double。
  8. 【强制】任何字段如果为非负数，必须是unsigned。
  9. 【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型。
  10. 【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
  11. 【强制】表必备三字段：id,create_time,update_time。
  12. 【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（0否 1是）。
  13. 【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。 :::info 说明：冗余字段应遵循
  • 不是频繁修改的字段。
  • 不是唯一索引的字段。
  • 不是varchar超长字段，更不能是text字段。

如：各业务线经常冗余存储商品名称，避免查询时需要调用IC服务获取。 :::

  1. 【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
  2. 【推荐】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

image.png

# 索引规范（参考阿里）

  1. 【强制】主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。

  2. 【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。 :::info 说明：不要以为唯一索引影响insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。 :::

  3. 【强制】超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

  4. 【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。 :::info 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名,索引长度))/count(*)的区分度来确定。 :::

  5. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 :::info 说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。 :::

  6. 【推荐】如果有order by的场景，请注意利用索引的有序性。order by最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。 :::info 正例：where a=? and b=? order by c; 索引：a_b_c
    反例：索引如果存在范围查询，那么索引有序性无法利用 如：WHERE a>10 ORDER BY b;索引a_b无法排序。 :::

  7. 【推荐】利用覆盖索引来进行查询操作，避免回表。 :::info 说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。就是查询字段与索引字段一致，能够直接从索引获取需要的字段信息。
    正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效
    果，用explain的结果，extra列会出现：using index。 :::

  8. 【推荐】利用延迟关联或者子查询优化超多分页场景。 :::info 说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
    正例：先快速定位需要获取的id段，然后再关联：
    SELECT t1.* FROM 表1 as t1,
    (select id from 表1 where 条件 LIMIT 100000,20 ) as t2 where t1.id = t2.id :::

  9. 【推荐】SQL性能优化的目标：至少要达到range级别，要求是ref级别，如果可以是consts最好。 :::info 说明：
    1）consts单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
    2）ref指的是使用普通的索引（normal index）。
    3）range对索引进行范围检索。
    反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range
    还低，与全表扫描是小巫见大巫。 :::

  10. 【推荐】建组合索引的时候，区分度最高的在最左边。 :::info 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=?
    那么即使c的区分度更高，也必须把d放在索引的最前列，即建立组合索引idx_d_c。
    正例：如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。 :::

  11. 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

  12. 【推荐】创建索引时避免有如下极端误解 :::info 1）索引宁滥勿缺。认为一个查询就需要建一个索引。
    2）吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
    3）抵制唯一索引。认为唯一索引一律需要在应用层通过“先查后插”方式解决。 :::

# SQL编写规范（参考阿里）

  1. 【强制】不要使用count(列名)或count(常量)来替代count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。 :::info 说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。 :::

  2. 【强制】count(distinct col)计算该列除NULL之外的不重复行数，注意count(distinct col1,col2)如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。

  3. 【强制】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题。 :::info 正例：可以使用如下方式来避免sum的NPE问题：SELECT IFNULL(SUM(column), 0) FROM table; :::

  4. 【强制】使用ISNULL()来判断是否为NULL值。 :::info 说明：NULL与任何值的直接比较都为NULL。
    1）NULL<>NULL的返回结果是NULL，而不是false。
    2）NULL=NULL的返回结果是NULL，而不是true。
    3）NULL<>1的返回结果是NULL，而不是true。
    反例：在SQL语句中，如果在null前换行，影响可读性。select * from table where column1 is null and
    column3 is not null;而ISNULL(column)是一个整体，简洁易懂。从性能数据上分析，ISNULL(column)
    执行效率更快一些。 :::

  5. 【强制】代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。

  6. 【强制】不得使用外键与级联，一切外键概念必须在应用层解决。 :::info 说明：（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。 :::

  7. 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

  8. 【强制】数据订正（特别是删除或修改记录操作）时，要先select，避免出现误删除，确认无误才能执行更新语句。

  9. 【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。 :::info 说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且
    操作列在多个表中存在时，就会抛异常。
    正例：select t1.name from table_firstas t1, table_second as t2 where t1.id = t2.id;
    反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出1052异常：Column 'name' in field list is ambiguous。 :::

  10. 【推荐】SQL语句中表的别名前加as，并且以t1、t2、t3、...的顺序依次命名。 :::info 说明：1）别名可以是表的简称，或者是依照表在SQL语句中出现的顺序，以t1、t2、t3的方式命名。2）
    别名前加as使别名更容易识别。
    正例：select t1.name from table_firstas t1, table_second as t2 where t1.id=t2.id; :::

  11. 【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。

  12. 【推荐】TRUNCATETABLE比DELETE速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发trigger，有可能造成事故，故不建议在开发代码中使用此语句。 :::info 说明：TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同。 :::

# InnoDB存储引擎

# 锁

全局锁
加锁：flush tables with read lock;
释放锁：unlock tables;

使用场景：全库的数据备份

表级锁
表共享读锁（read lock）
表独占写锁（write lock）

加锁：lock tables 表名... read/write
释放锁：unlock tables

元数据锁（meta data lock, MDL）
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写操作。为了避免DML 与DDL冲突，保证读写的正确性。
MySQL5.5 中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）。
image.png

意向锁
为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表的检查。

意向共享锁（IS）：由语句select ... lock in share mode添加。
意向排他锁（IX）：由insert、update、delete、select ... for update添加。

# 事务原理

事务的原理涉及几个表，redo_log，undo_log
image.png

redo_log，重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是内存中，后者是在磁盘中。当事务提交之后会把所有修改信息都存到该日志中，用于刷新脏页到磁盘，发生错误时，进行数据恢复使用。
image.png
简单说就是redo_log以日志的形式记录数据页的修改，然后在提交事务的时候一次性将脏页刷新到磁盘，如果出现错误也可以通过这个日志回滚。因为日志文件都是追加的形式写入数据，所以一定程度上可以提高磁盘IO。redologfile日志写入成功一段时间后会将脏页数据写入磁盘，并且清除日志记录。

undo_log，回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制），用于实现事务的原子性。
undo_log和redo_log记录物理日志不一样，他是逻辑日志。可以认为当delete一条记录时，undo_log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo_log中的逻辑记录读取到相应的内容并进行回滚。
Undo_log销毁：undo_log在事务执行时产生，事务提交时，并不会立即删除undo_log，因为这些日志可能还用于MVCC。
Undo_log存储：undo_log采用段的方式进行管理和记录，存放在前面介绍的rollback segment 回滚段中，内部包含1024个undo_log segment。

# MVCC

mvcc概念：

**当前读：**读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode（共享锁），select ... for update、update、insert、delete（排他锁）都是一种当前读。

**快照读：**简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

  • Read Committed：每次select，都生成一个快照读。
  • Repeatable Read：开启事务后第一个select 语句才是快照读的地方。
  • Serializable：快照读会退化为当前读。

**mvcc：**全程 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo_log 日志、readView。

# MVCC原理

记录中影藏的字段
image.png

隐藏字段 含义
DB_TRX_ID 最近修改事务ID，记录插入这个记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用于配合undo_log，指向上一个版本。
DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

undo_log日志
回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo_log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo_log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

undo_log版本链
不同事务或相同事务对同一条记录进修改，会导致该记录的undo_log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readView
ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交）id。ReadView中包含了四个核心字段：

字段 含义
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id ReadView创建者的事务ID

image.png

不同的隔离级别，生成ReadView的时机不同：

  • READ COMMITTED：在事务中每一次执行快照读时生成ReadView。
  • REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
#mysql#db#主从#集群
上次更新: 2023/12/29 11:32:56
Mysql 基础篇
Mysql 库表规范

← Mysql 库表规范→

最近更新
01
JVM 底层
09-13
02
JVM 理论
09-13
03
JVM 应用
09-13
更多文章>
Theme by Vdoing | Copyright © 2022-2024 kinoko | MIT License | 粤ICP备2024165634号