基础篇

SQL

SQL

  • SQL分类

    分类 说明
    DDL 数据定义语言，用来定义数据库对象（数据库，表，字段）
    DML 数据操作语言，用来对数据库表中的数据进行增删改
    DQL 数据查询语言，用来查询数据库中表的记录
    DCL 数据控制语言，用来创建数据库用户、控制数据库访问权限

DDL

  • DDL-数据库操作

    • 查询

      查询所有数据库

      show databases;

      查询当前数据库

      select database();

    • 创建

      create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

    • 删除

      drop database [if exists] 数据库名;

    • 使用

      use 数据库名;

  • DDL-表操作-查询，创建

    • 查询当前数据库所有表

      show tables;

    • 查询表结构

      desc 表明;

    • 查询指定表的建表语句

      show create table 表名;

    • 创建表

      create table 表名(
      	字段1 字段1类型[comment 字段1注释],
      	字段2 字段2类型[comment 字段1注释],
      	字段3 字段3类型[comment 字段1注释],
      	......
      	字段n 字段n类型[comment 字段1注释]
      )[comment 表注释]；

  • DDL-表操作-修改,删除

    • 修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度)[omment 注释][约束];
删除字段
alter table 表名 drop 字段名;
修改表名
alter table 表名 rename to 新表名;


      - ```sql
        删除表
        drop table [if exists] 表名;
        删除指定表，并创建该表
        truncate table 表名;

DML

  • DML-添加数据

    1.给指定字段添加数据
    insert into 表名(字段名1，字段名2,...) values(值1,值2);
    2.给全部字段添加数据
    insert into 表名 values(值1,值2,...);
    3.批量添加数据
    insert into 表名(字段名1,字段名2...) values((值1,值2...),(值1,值2...),(值1,值2...));
    insert into 表名 values((值1,值2...),(值1,值2...),(值1,值2...));

  • DML-更新数据,删除数据

    1.更新数据
    update 表名 set 字段名1=值1,字段值2=值2,...[where 条件];
    2.删除数据
    delete from 表名 [where 条件];

DQL

  • DQL-基本查询

    1.查询多个字段
    select 字段1,字段2,字段3... from 表名;
    select * from 表名;
    2.设置别名
    select 字段1[AS 别名1],字段2[AS 别名2],字段3[AS 别名3]... from 表名;
    3.去除重复记录
    select distinct 字段列表 from 表名;

  • DQL-条件查询

    • 语法

      select 字段列表 from 表名 where 条件列表;

    • 条件

      比较运算符 功能
      > 大于
      >= 大于等于
      < 小于
      <= 小于等于
      = 等于
      <> 或 != 不等于
      between…and… 在某个范围之内
      in(…) 在in之后的列表中的值，多选一
      like 占位符 模糊匹配（_匹配单个字符,%匹配多个字符）
      is null 是null
逻辑运算符 功能
&& 或 AND 并且（多个条件同时成立）
|| 或 OR 或者（多个条件任意一个成立）
! 或 NOT 非，不是

  • DQL-聚合函数

    函数 功能
    count 统计数量
    max 最大值
    min 最小值
    avg 平均值
    sum 求和 
    语法：select 聚合函数(字段数列) from 表名;

  • DQL-分组查询

    1.语法
    select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
    2.where 与 having 区别
    执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
    判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

  • DQL-排序查询

    1. 语法
    select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2 ;
    2. 排序方式
    ASC：升序（默认值）
    DESC：降序
    注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

  • DQL-分页查询

    1.语法
    SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
    注意
    · 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数。
    · 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
    · 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

  • DQL-执行顺序

    image-20251021212510152

函数

  • 字符串函数

    函数 功能
    CONCAT(S1,S2,…Sn) 字符串拼接，将 S1，S2，… Sn 拼接成一个字符串
    LOWER(str) 将字符串 str 全部转为小写
    UPPER(str) 将字符串 str 全部转为大写
    LPAD(str,n,pad) 左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符串长度
    RPAD(str,n,pad) 右填充，用字符串 pad 对 str 的右边进行填充，达到 n 个字符串长度
    TRIM(str) 去掉字符串头部和尾部的空格
    SUBSTING(str,start,len) 返回从字符串 str 从 start 位置起的 len 个长度的字符串

  • 数值函数

    函数 功能
    CELL(x) 向上取整
    FLOOR(x) 向下取整
    MOD(x,y) 返回x/y的模
    RAND() 返回0~1内的随机数
    ROUND(x,y) 求参数x的四舍五入的值，保留y为小数

  • 日期函数

    函数 功能
    curdate() 返回当前日期
    curtime() 返回当前时间
    now() 返回当前日期和时间
    year(date) 获得指定date的年份
    month(date) 获得指定date的月份
    day(date) 获得指定date的日期
    date_add(date,interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
    datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数

  • 流程函数

    函数 功能
    if(value , t, f) 如果 value 为 true，则返回 t，否则返回 f
    IFNULL(value1 , value2) 如果 value1 不为空，返回 value1，否则返回 value2
    CASE WHEN [val1] THEN [res1] … ELSE [default] END 如果 val1 为 true，返回 res1，… 否则返回 default 默认值
    CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END 如果 expr 的值等于 val1，返回 res1，… 否则返回 default 默认值

约束

  • 约束概要

    • 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

    • 目的：保证数据库中数的正确、有效性和完整性。

    • 分类

      约束 描述 关键词
      非空约束 限制该字段的数据不能为null not null
      唯一约束 保证该字段的所有数据都是唯一、不重复 unique
      主键约束 主键是一行数据的唯一标识，要求非空且唯一 primary key
      默认约束 保存数据时，如果未指定该字段的值，则采用默认值 default
      检查约束 保证字段值满足某一个条件 check
      外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 foreign key

  • 外键

    • 添加外键

      create table 表名(
      	字段名 数据类型,
          ...
          [constraint] [外键名称] foreign key(外键字段名) references 主表(主列表名);
      );
      alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主列表名)；

    • 删除外键

      alter table 表名 drop foreign key 外键名称;

  • 删除更新行为

    行为 说明
    NO ACTION 当在父表中删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除 / 更新。(与 RESTRICT 一致)
    RESTRICT 当在父表中删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除 / 更新。(与 NO ACTION 一致)
    CASCADE 当在父表中删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除 / 更新外键在子表中的记录。
    SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为 null（这就要求该外键允许取 null）。 
    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

  • 多表查询-多表关系

    • 一对多

      案例：部门与员工的关系
      关系：一个部门对应多个员工，一个员工对应一个部门
      实现：在多的一方建立外键，指向一的一方的主键

    • 多对多

      案例：学生与课程的关系
      关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
      实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

    • 一对一

      案例：用户与用户详情的关系
      关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
      实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的 (UNIQUE)

查询

  • 多表查询-内连接

    内连接查询的是两张表交集的部分
    1.隐式内连接
    select 字段列表 from 表1,表2 where 条件...;
    2.显式内连接
    select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

  • 多表查询-外连接

    1.左外连接(查询表1与表2交集部分)
    select 字段列表 from 表1 left [outer] join 表2 on 条件...;
    2.右外连接
    select 字段列表 from 表1 right [outer] join 表2 on 条件...;

  • 多表查询-自连接

    select 字段列表 from 表A 别名A join 表B 别名B on 条件...;

  • 多表查询-联合查询

    select 字段列表 from 表A...
    union[all]
    select 字段列表 from 表B...;

  • 子查询

    • 标量子查询

      子查询返回的结果是单个值
      常用操作符 = <> > >= < <=

    • 列子查询

      子查询返回的结果是一列
      常用操作符 IN "NOT NULL" ANY SOME ALL

    • 行子查询

      子查询返回的是一行(可以是多列)
      常用操作符 = <> IN "NOT IN"

    • 表子查询

      常用操作符为 IN

DCL

  • DCL-用户管理

    DCL-管理用户
    1. 查询用户
    USE mysql;
    SELECT * FROM user;
    2. 创建用户
    CREATE USER '用户名'@'主机名'
    IDENTIFIED BY '密码';
    3. 修改用户密码
    ALTER USER '用户名'@'主机名'
    IDENTIFIED WITH mysql_native_password BY '新密码';
    4. 删除用户
    DROP USER '用户名'@'主机名';

  • DCL-权限控制

权限 说明
SELECT 查询权限
INSERT 插入权限
UPDATE 修改权限
DELETE 删除权限
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表
ALL, ALL PRIVILEGES 所有权限 
查询权限SHOW GRANTS FOR '用户名'@'主机名';
授予权限GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

  • 方法一
查看/设置事务的提交方式
select @@autocommit;
set @@autocommit=0;
提交事务
commit;
回滚事务
rollback;
  • 方法2
开启事务
start transaction 或 begin
  • 事务的四大特性
原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

  • 并发事务的问题

    问题 描述
    脏读 一个事务读到另外一个事务还没有提交的数据。
    不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
    幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

  • 事务的隔离级别

    隔离级别 脏读 不可重复读 幻读
    Read uncommitted √ √ √
    Read committed × √ √
    Repeatable Read (默认) × × √
    Serializable × × × 
    -- 查看事务隔离级别SELECT @@TRANSACTION_ISOLATION;
    -- 设置事务隔离级别SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

进阶篇

存储引擎

  • 存储引擎-MySQL体系结构

    image-20251025134717679

  • 存储引擎-简介

    • 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型。

    • 建表语句

      create table 表名(
      	字段1 字段1类型[comment 字段1注释],
      	字段2 字段2类型[comment 字段2注释],
      	字段3 字段3类型[comment 字段3注释],
      	......
      	字段n 字段n类型[comment 字段n注释]
      )engine = innodb [comment 表注释]；

    • 查数据库支持哪些存储引擎

      show engines;

  • 存储引擎-innoDB介绍。

    • 介绍

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

    • 特点

      1.DML 操作遵循 ACID 模型，支持事务；

      2.行级锁，提高并发访问性能；

      3.支持外键FOREIGN KEY 约束，保证数据的完整性和正确性；

    • 文件

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

    • 逻辑存储结构

      image-20251025142440026

  • 存储引擎-MyISAM和Memory

    • MyISAM

      介绍
      MyISAM 是 MySQL 早期的默认存储引擎。
      特点
      不支持事务，不支持外键
      支持表锁，不支持行锁
      访问速度快
      文件
      xxx.sdi：存储表结构信息
      xxx.MYD：存储数据
      xxx.MYI：存储索引

    • Memory

      介绍
      Memory 引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
      特点
      内存存放
      hash 索引（默认）
      文件
      xxx.sdi：存储表结构信息

  • 存储引擎-选择

    InnoDB：是 Mysql 的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。
    MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
    MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY 的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

  • 索引-概述

    • 索引是帮助MySQL高效获取数据的数据结构(有序)。

    • 优势 劣势
      提高数据检索的效率，降低数据库的 IO 成本 索引列也是要占用空间的。
      通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗。 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行 INSERT、UPDATE、DELETE 时，效率降低。

索引结构

  • 索引结构

    索引结构 描述
    B+Tree 索引 最常见的索引类型，大部分引擎都支持 B+ 树索引
    Hash 索引 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询

  • 索引-结构

    • Btree

      image-20251025222431224

      具体动态变化的过程可以参考网站: https://www.cs.usfca.edu/~galles/visualization/BTree.html

    • B+tree

      image-20251025222903543

      区别：所有节点都会出现在叶子节点，叶子节点形成一个单向链表。

    • hash

      image-20251026101311701

      特点：
      1. Hash索引只能用于对等比较(=,in),不支持范围查询(bewteen,>,<,...)
      2.无法利用索引完成排序操作
      3.查询效率高，通常只需要一次检索就可以，效率通常要高于B+tree索引。

索引分类&&语法

  • 索引-分类

    • 分类 含义 特点
      聚集索引 (Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个
      二级索引 (Secondary Index) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

    • 聚集索引选取规则

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

  • 索引-语法

    • 创建索引

      create [unique|fulltext] index index_name on table_name (index_col_name...);

    • 查看索引

      show index from table_name;

    • 删除索引

      drop index index_name on table_name;

  • 索引-性能分析

    • 查看执行频次

      查看数据库语句访问频次
      show global status like 'Com_______';

    • 慢查询日志

      慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认 10 秒）的所有 SQL 语句的日志。
      MySQL 的慢查询日志默认没有开启，需要在 MySQL 的配置文件（/etc/my.cnf）中配置如下信息：
      #开启MySQL慢日志查询开关
      slow_query_log=1
      #设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
      long_query_time=2
      配置完后，通过以下指令重启MySQL服务器进行测试，查看满日志文件中记录的信息/var/lib/mysql/localhost-slow.log

  • show profiles

    select @@have_profiling;
    开启profiling:set profiling=1;
    #查看每一条SQL的耗时基本情况
    show profiles;
    #查看指定query_id的SQL语句的各个阶段的耗时情况。
    show profile for query query_id;
    #查询指定query_id的SQL语句的CPU使用情况。
    show profile cpu for query query_id;

    • 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 。
      #possible_key
      显示可能应用在这张表上的索引，一个或多个。
      #Key
      实际使用的索引，如果为 NULL，则没有使用索引。
      #Key_len
      表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
      #rows
      MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的。
      #filtered
      表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。

  • 索引-使用规则

    • 最左前缀法则

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

索引失效

  • 索引失效

    • 不要在索引列表上进行运算操作，索引将失效。

    • 字符串类型字段使用时，不加引号，索引将失效。

    • 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

    • 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引用不到。

    • 如果MySQL评估使用索引比全表更慢，索引失效。

    • SQL提示

      • SQL提示是优化数据库的重要手段，就是在SQL语句中加入一些人为的提示来达到目的

      • index

        explain select * from tb_user use index(idx_user_pro) where profession='软件工程'。
        ## use index表示使用该索引，ignore表示忽略，force表示强制。

    • 覆盖索引

      • 尽量使用覆盖索引（也就是说查询的字段，在使用的索引中都包含，也就是减少回表查询），减少select*。

    • 前缀索引

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

      • 语法

        create index idx_xxxx on table_name(column(n));

      • 前缀长度

        可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

        select count(distinct substring(字段值,1，5))/count(*) from tb_user;

  • 思考

    1.为什么InnoDB存储引擎选择使用B+tree索引结构？
    	相对于二叉树，层级更少，搜索效率高。
    	对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。

  • 使用原则

    针对于数据量较大，且查询比较频繁的表建立索引。
    针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
    尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
    如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
    尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
    要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
    如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

insert优化

  • 批量插入

    Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

  • 手动提交事务

    start transaction;
    insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
    insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
    insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
    commit;

  • 主键顺序插入

    主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
    主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89

  • 大批量插入数据(load)

    #客户端连接服务端时，加上参数 --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 tb_user fields terminated by ',' lines terminated by '\n';

主键优化

  • 页分裂
    image-20251027194910387
  • 页合并
    image-20251027195035830
  • 主键设计原则
    • 满足业务需求的情况下，尽量降低主键的长度。
    • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
    • 尽量不要使用UUID做主键或者时其他自然主键，如身份证号。
    • 业务操作时，避免对主键的修改。

order by优化

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

  • order by优化
    • 根据排序字段建立合适的索引，多字段排序时，页遵循最左前缀法则。
    • 尽量使用覆盖索引。
    • 多字段排序，一个升序一个降序，此时要注意联合索引创建的规则(ASD/DESC)。
    • 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size (默认 256k)

group by优化

  • group by优化
    • 在分组操作时，可以通过索引来提高效率。
    • 在分组操作时，索引的使用也满足最左前缀法则。

limit优化

  • 优化思路

    一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

    explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

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的行级锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行级锁升级为表锁。

视图

介绍及基本语法

  • 介绍

    视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

  • 语法

    • 创建

      create [or replace] view 视图名称[(列表名称)] AS select语句 [with[cascaded|local] check option];

    • 查询

      查看创建视图语句：show create view 视图名称;
      查看视图数据：   select * from 视图名称......;

    • 修改

      方式一：create [or replace] view 视图名称[(列表名称)] AS select语句 [with[cascaded|local] check option];
      方式二：alter view 视图名称[(列表名称)] AS select语句 [with[cascaded|local] check option];

    • 删除

      drop view [if exists] 视图名称

检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL 会通过视图检查正在更改的每个行，例如插入、更新、删除，以使其符合视图的定义。MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql 提供了两个选项：CASCADED和LOCAL，默认值为CASCADED。

cascaded

create view v1 as select id,name from student where id <= 20
create view v2 as select id , name from v1 where id >= 10 with cascaded check option ;

如果加了cascaded要检测当前视图条件还要检测关联的视图条件。关联权限有检查权限。

local

create view v1 as select id,name from student where id <= 15
create view v2 as select id,name from v1 where id >= 10 with local check option ;
create view v3 as select id,name from v2 where id < 20

关联视图没有检查权限

更新及作用

  • 注意

    要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

    1. 聚合函数或窗口函数（SUM ()、MIN ()、MAX ()、COUNT () 等）
    2. DISTINCT
    3. GROUP BY
    4. HAVING
    5. UNION 或者 UNION ALL

  • 作用

    • 简单
      视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
    • 安全
      数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
    • 数据独立
      视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

介绍

  • 封装，复用
  • 可以接受参数，也可以返回参数
  • 减少网络交互，效率提升。

基本语法

  • 创建

    create procedure 存储过程名称([参数列表])
    begin
    		--SQL语句
    end;

  • 调用

    call 名称 ([参数]);

  • 查看

    select * from information_schema.routines where routines_schema = 'xxx';--查询指定数据库存储过程及状态信息。
    show create procedure 存储过程名称： --查询某个存储过程的定义

  • 删除

    drop procedure [if exists] 存储过程名称;

    注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。

变量

系统变量

系统变量时MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(global)和会话变量(session).

  • 查看系统变量

    show [session|global] variables;				--查看所有系统变量
    show [session|global] variables like '......';  --模糊匹配查找
    select @@[session|global] 系统变量名;			 --查看指定变量的值

  • 设置系统变量

    set [session|global] 系统变量名 = 值
    set @@[session|global] 系统变量名 = 值

注意:如果没有指定 SESSION/GLOBAL，默认是 SESSION，会话变量。mysql 服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

用户自定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接。

  • 赋值

    SET @var_name = expr [, @var_name = expr] ... ;
    SET @var_name := expr [, @var_name := expr] ... ;
    SELECT @var_name := expr [, @var_name := expr] ... ;
    SELECT 字段名 INTO @var_name FROM 表名;

  • 使用

    SELECT @var_name;

注意：用户自定义的变量无需对其进行声明或初始化，只不过获取的职位NULL；

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要 DECLARE 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 BEGIN … END 块。

  • 声明

    DECLARE 变量名 变量类型 [DEFAULT ...];

    变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME 等。

  • 赋值

    SET 变量名 = 值;
    SET 变量名 := 值;
    SELECT 字段名 INTO 变量名 FROM 表名 ... ;

if语句

  • 语句

    IF 条件 1 THEN
    		......
    ELSEIF 条件 2 THEN    -- 可选
    		......
    ELSE 				 -- 可选
    		......
    END IF;

参数(IN 、OUT、INOUT)

  • 参数

    参数 含义 备注
    IN 该类参数作为输入，也就是需要调用时传入值 默认
    OUT 该类参数作为输出，也就是该参数可以作为返回值
    INOUT 既可以作为输入参数，也可以作为输出

  • 用法

    CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
    BEGIN
        -- SQL语句
    END;

case

  • 语法一

    CASE case_value
        WHEN when_value1 THEN statement_list1
        [ WHEN when_value2 THEN statement_list2 ] ...
        [ ELSE statement_list ]
    END CASE;

  • 语法二

    CASE
        WHEN search_condition1 THEN statement_list1
        [WHEN search_condition2 THEN statement_list2] ...
        [ELSE statement_list]
    END CASE;

while

  • 语法

    WHILE 条件 DO
    	SQL 逻辑...
    END WHILE;

repeat

  • 语法

    #先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
    REPEAT
    	SQL 逻辑...
    	UNTIL 条件
    END REPEAT;

loop

LOOP 实现简单的循环，如果不在 SQL 逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP 可以配合以下两个语句使用：

  • LEAVE：配合循环使用，退出循环。
  • ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
[begin_label:] loop
    SQL逻辑...
end loop [end_label];
leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环

游标cursor

游标（CURSOR） 是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
打开游标
OPEN 游标名称;
获取游标记录
FETCH 游标名称 INTO 变量[, 变量 ];
关闭游标
CLOSE 游标名称;

条件处理程序

条件处理程序（Handler） 可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：
DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement;

handler_action
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序

condition_value
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以 01 开头的 SQLSTATE 代码的简写
NOT FOUND: 所有以 02 开头的 SQLSTATE 代码的简写
SQLEXCEPTION: 所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的简写

存储函数

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic ...]
BEGIN
    -- SQL语句
    RETURN ...;
END;
  • characteristic 说明
    • DETERMINISTIC：相同的输入参数总是产生相同的结果
    • NO SQL：不包含 SQL 语句。
    • READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的 SQL 语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发

  • 语法

    • 创建

      CREATE TRIGGER trigger_name
      BEFORE/AFTER INSERT/UPDATE/DELETE
      ON tbl_name  FOR EACH ROW -- 行级触发器
      BEGIN
          trigger_stmt ;
      END;

    • 查看

      SHOW TRIGGERS ;

    • 删除

      DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库。

锁

锁是计算机协调多个进程或者线程并发访问某一资源的机制。

MySQL 中的锁，按照锁的粒度分，分为以下三类：

  1. 全局锁：锁定数据库中的所有表。
  2. 表级锁：每次操作锁住整张表。
  3. 行级锁：每次操作锁住对应的行数据。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句，DDL 语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

  • 特点

    • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

    • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

    • 在 InnoDB 引擎中，我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份。

      mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql

  • 语法

    flush tables with read lock; --加入全局锁
    unlock tables;               --解锁
    mysqldump -uroot -p db01 >D:/db01.sql --数据库备份

表锁

表级锁，每次操作锁住整张表。锁定力度打，发生锁冲突的概率最高，并发度最低。

  • 表锁

    • 分类

      • 表共享读锁(read lock)
      • 表独占写锁(write lock)

    • 语法

      • 加锁：lock tables 表名… read/write。
      • 释放锁：unlock tables /客户端断开连接。

      读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

  • 元数据锁

    MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突，保证读写的正确性。

  • 意向锁

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

    • 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
    • 意向排它锁（IX）：与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥。

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中。

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

  1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete。在 RC、RR 隔离级别下都支持。
  2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行 insert，产生幻读。在 RR 隔离级别下都支持。
  3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap。在 RR 隔离级别下支持。
  • 行锁
    • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
    • 排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁。

image-20251028195554714

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  2. InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁。

  • 间隙锁和临界锁

    默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读。

    1. 索引上的等值查询 (唯一索引)，给不存在的记录加锁时，优化为间隙锁 。
    2. 索引上的等值查询 (普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
    3. 索引上的范围查询 (唯一索引)– 会访问到不满足条件的第一个值为止。

InnoDB

逻辑存储结构

image-20251029195315935

架构

image-20251029195542689

内存架构

image-20251029195838694

image-20251029200053318

image-20251029200231809

image-20251029200345555

内存架构

image-20251029200833561

image-20251029201154685

image-20251029201357774

后台线程

image-20251029202535535

事务原理

image-20251029202814450

image-事务原理

image-事务原理

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。

  • 隐藏字段

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

    mysql的文件存放在var/lib/mysql文件夹中，mysql提供了查看idb文件的指令 idb2 stu.db

MVCC实现原理

  • undo log

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

  • undo log 版本链

    image-MVCC

  • 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-readview原理

演示

  • RC

    image-原理演示

  • RR

    image-原理演示

运维篇

日志

错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

​ 该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置：

show variables like '%log_error%';

二进制日志

介绍

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

作用：①. 灾难时的数据恢复；②. MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：

show variables like '%log_bin%';


参数说明：

  • log_bin_basename：当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文 件名需要再该basename的基础上加上编号(编号从000001开始)。

  • log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些。

格式

MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式 含义
STATEMENT 基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
ROW 基于行的日志记录，记录的是每一行的数据变更。（默认）
MIXED 混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会 
show variables like '%binlog_format%';

如果我们需要配置二进制日志的格式，只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。

查看

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，具体语法：

删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

指令 含义
reset master 删除全部 binlog 日志，删除之后，日志编号，将binlog.000001重新开始
purge master logs to 删除 * 编号之前的所有日志
purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ 删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

show variables like '%binlog_expire_logs_seconds%';
show variables like '%general%';

如果需要开启查询日志，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

开启了查询日志之后，在MySQL的数据存放目录，也就是 /var/lib/mysql/ 目录下就会出现 mysql_query.log 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。

慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为10 秒，最小为 0 精度可以到微秒。

如果需要开启慢查询日志，需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数：

#慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用 log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes，如下所述。

#记录执行较慢的管理语句
log_slow_admin_statements =1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1

上述所有的参数配置完成之后，都需要重新启动MySQL服务器才可以生效。

主从复制

概述

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 复制的优点主要包含以下三个方面：

  • 主库出现问题，可以快速切换到从库提供服务。
  • 实现读写分离，降低主库的访问压力。
  • 可以在从库中执行备份，以避免备份期间影响主库服务。

原理

MySQL主从复制的核心就是 二进制日志，具体的过程如下：

从上图来看，复制分成三步：

  1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
  2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
  3. slave重做中继日志中的事件，将改变反映它自己的数据。

搭建

准备

准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备(安装、密码配置等操作)工作。 其中：

  • 192.168.200.200 作为主服务器master

  • 192.168.200.201 作为从服务器slave

主从配置

  1. 修改配置文件 /etc/my.cnf

    #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1 
    server-id=1
    #是否只读,1 代表只读, 0 代表读写
    read-only=0
    #忽略的数据, 指不需要同步的数据库
    #binlog-ignore-db=mysql
    #指定同步的数据库
    #binlog-do-db=db01
    1. 重启MySQL服务器
    systemctl restart mysqld;

  2. 登录mysql，创建远程连接的账号，并授予主从复制权限

    #创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
    CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
    #为 'itcast'@'%' 用户分配主从复制权限
    GRANT REPLICATION SLAVE	ON *.* TO 'itcast'@'%';

  3. 通过指令，查看二进制日志坐标

    show  master  status;

    字段含义说明：

    • file : 从哪个日志文件开始推送日志文件

    • position ： 从哪个位置开始推送日志

    • binlog_ignore_db : 指定不需要同步的数据库

从库配置

  1. 修改配置文件 /etc/my.cnf

    #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
    server-id=2
    #是否只读,1 代表只读, 0 代表读写
    read-only=1
    #设置超级管理员只读权限
    super-read-only=1

  2. 重新启动MySQL服务

    systemctl restart mysqld;

  3. 登录mysql，设置主库配置

    CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.200', 
    SOURCE_USER='itcast', 
    SOURCE_PASSWORD='Root@123456', 
    SOURCE_LOG_FILE='binlog.000004', 
    SOURCE_LOG_POS=663;

    上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本，执行如下SQL：

    CHANGE MASTER TO MASTER_HOST='192.168.200.200', 
    MASTER_USER='itcast', 
    MASTER_PASSWORD='Root@123456',
    MASTER_LOG_FILE='binlog.000004',
    MASTER_LOG_POS=663;
    参数名 含义 8.0.23之前
    SOURCE_HOST 主库IP地址 MASTER_HOST
    SOURCE_USER 连接主库的用户名 MASTER_USER
    SOURCE_PASSWORD 连接主库的密码 MASTER_PASSWORD
    SOURCE_LOG_FILE binlog日志文件名 MASTER_LOG_FILE
    SOURCE_LOG_POS binlog日志文件位置 MASTER_LOG_POS

  4. 开启同步操作

start replica ; #8.0.22之后
start  slave ;  #8.0.22之前

  1. 查看主从同步状态

    show replica  status ;  #8.0.22之后
    show  slave  status ;   #8.0.22之前

分库分表

介绍

问题分析

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

  1. IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽不够，网络IO瓶颈。

  2. CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

为了解决上述问题，我们需要对数据库进行分库分表处理。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分策略

分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下：

垂直拆分

  1. 垂直分库

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

特点：

  • 每个库的表结构都不一样。

  • 每个库的数据也不一样。

  • 所有库的并集是全量数据。

  1. 垂直分表

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：

  • 每个表的结构都不一样。
  • 每个表的数据也不一样，一般通过一列（主键/外键）关联。
  • 所有表的并集是全量数据。

水平拆分

  1. 水平分库

水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。

特点：

  • 每个库的表结构都一样。
  • 每个库的数据都不一样。
  • 所有库的并集是全量数据。

  1. 水平分表

水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

特点：

  • 每个库的表结构都一样。
  • 每个库的数据都不一样。
  • 所有库的并集是全量数据。

在业务系统中，为了缓解磁盘IO及CPU的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分库，还是分表，都需要根据具体的业务需求具体分析。

实现技术

  • shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
  • MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

MyCat 概述

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在。

开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。 具体的分库分表的策略，只需要在MyCat中配置即可。

优势：

  • 性能可靠稳定

  • 强大的技术团队

  • 体系完善

  • 社区活跃

在MyCat的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构。

在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的数据存储还是在物理结构，也就是数据库服务器中存储的。

MyCat入门

需求

由于 tb_order 表中数据量很大，磁盘IO及容量都到达了瓶颈，现在需要对 tb_order 表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上, 具体的结构，参考下图：

环境准备

准备3台服务器：

  • 192.168.200.210：MyCat中间件服务器，同时也是第一个分片服务器。
  • 192.168.200.213：第二个分片服务器。
  • 192.168.200.214：第三个分片服务器。

并且在上述3台数据库中创建数据库 db01

配置

  1. schema.xml在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下：

  2. 需要在server.xml中配置用户名、密码，以及用户的访问权限信息，具体的配置如下：

上述的配置表示，定义了两个用户 root 和 user ，这两个用户都可以访问 DB01 这个逻辑库，访问密码都是123456，但是root用户访问DB01逻辑库，既可以读，又可以写，但是 user用户访问DB01逻辑库是只读的。

测试

  1. 连接MyCat通过如下指令，就可以连接并登陆MyCat。
mysql -h 192.168.200.210 -P 8066 -uroot -p123456

我们看到我们是通过MySQL的指令来连接的MyCat，因为MyCat在底层实际上是模拟了MySQL的协议。

  1. 数据测试

    然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况。

    CREATE TABLE TB_ORDER (
    id BIGINT(20) NOT NULL,
    title VARCHAR(100) NOT NULL ,
    PRIMARY KEY (id)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;


    INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1'); 
    INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2'); 
    INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
    INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1');
    INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2');
    INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
    INSERT INTO TB_ORDER(id,title) VALUES(5000000,'goods5000000');
    INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
    INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');
    INSERT INTO TB_ORDER(id,title) VALUES(15000000,'goods15000000');
    INSERT INTO TB_ORDER(id,title) VALUES(15000001,'goods15000001');

    经过测试，我们发现，在往 TB_ORDER 表中插入数据时：

    • 如果id的值在1-500w之间，数据将会存储在第一个分片数据库中。
    • 如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中。
    • 如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中。
    • 如果id的值超出1500w，在插入数据时，将会报错。

    这是由于分片规则引发的

MyCat配置

schema.xml

schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库 、 逻辑表 、 分片规则、分片节点及数据源的配置。

主要包含以下三组标签：

  • schema标签
  • datanode标签
  • datahost标签

schema标签

  1. schema 定义逻辑库

schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database概念, 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。

![](https://picbed.algk.com.cn/MySQL/image-20251104145625683.png)

核心属性：

  • name：指定自定义的逻辑库库名
  • checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
  • sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录

  1. schema 中的table定义逻辑表

    table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义 。

    核心属性：

    • name：定义逻辑表表名，在该逻辑库下唯一
    • dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
    • rule：分片规则的名字，分片规则名字是在rule.xml中定义的
    • primaryKey：逻辑表对应真实表的主键
    • type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为 global

datanode标签

  • 核心属性：

    • name：定义数据节点名称
    • dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
    • database：定义分片所属数据库

datahost标签

  • 该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。

    核心属性：

    • name：唯一标识，供上层标签使用
    • maxCon/minCon：最大连接数/最小连接数
    • balance：负载均衡策略，取值 0,1,2,3
    • writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
    • dbDriver：数据库驱动，支持 native、jdbc

rule.xml

rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签：tableRule、Function。

sever.xml

server.xml配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。

在测试权限操作时，我们只需要将 privileges 标签的注释放开。 在 privileges 下的schema标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性中配置逻辑表的权限。

MyCat分片

垂直拆分

场景

在业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。

现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库服务器，用户及省市区表拆分到一个服务器。最终结构如下：

配置

测试

  1. 上传测试SQL脚本到服务器的/root目录

  2. 执行指令导入测试数据

    重新启动MyCat后，在mycat的命令行中，通过source指令导入表结构，以及对应的数据，查看数据分布情况。

    source /root/shopping-table.sql
    source /root/shopping-insert.sql

    将表结构及对应的测试数据导入之后，可以检查一下各个数据库服务器中的表结构分布情况。 检查是否和我们准备工作中规划的服务器一致。

  3. 查询用户的收件人及收件人地址信息(包含省、市、区)。

    在MyCat的命令行中，当我们执行以下多表联查的SQL语句时，可以正常查询出数据。

    select ua.user_id, ua.contact, p.province, c.city, r.area , ua.address from tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id = r.areaid ;

  4. 查询每一笔订单及订单的收件地址信息(包含省、市、区)。实现该需求对应的SQL语句如下：

    SELECT order_id , payment ,receiver, province , city , area FROM tb_order_master o , tb_areas_provinces p , tb_areas_city c , tb_areas_region r WHERE o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid;

    但是现在存在一个问题，订单相关的表结构是在 192.168.200.213 数据库服务器中，而省市区的数据库表是在 192.168.200.214 数据库服务器中。那么在MyCat中执行是否可以成功呢？

    经过测试，我们看到，SQL语句执行报错。原因就是因为MyCat在执行该SQL语句时，需要往具体的数据库服务器中路由，而当前没有一个数据库服务器完全包含了订单以及省市区的表结构，造成SQL语句失败，报错。

    对于上述的这种现象，我们如何来解决呢？ 下面我们介绍的全局表，就可以轻松解决这个问题。

  5. 全局表

    对于省、市、区/县表tb_areas_provinces , tb_areas_city , tb_areas_region，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。修改schema.xml中的逻辑表的配置，修改 tb_areas_provinces、tb_areas_city、tb_areas_region 三个逻辑表，增加 type 属性，配置为global，就代表该表是全局表，就会在所涉及到的dataNode中创建给表。对于当前配置来说，也就意味着所有的节点中都有该表了。

    <table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
    <table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
    <table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>

    配置完毕后，重新启动MyCat。

    1. 删除原来每一个数据库服务器中的所有表结构

    2. 通过source指令，导入表及数据

    source /root/shopping-table.sql
    source /root/shopping-insert.sql

    然后执行就可以执行刚才的sql语句了。

水平拆分

  1. 配置

分片规则

  • 分片规则-范围

  • 分片规则-取模

  • 分片规则-一致性hash

  • 分片规则-枚举分片

  • 分片规则-应用指定

  • 分片规则-固定分片哈希算法