• 超详细MySQL总结[从百草园到三味书屋]

    MySQL总结

    文章目录

    1、数据库相关概念

    数据：

    数据库：存储数据的仓库，数据是有组织的进行存储

    数据库管理系统：操纵和管理数据库的大型软件

    SQL：结构化查询语言（structured query language），是一套标准

    常用数据库软件：Mysql，Oracle，SQL server

    2、mysql安装与启动

    安装

    1. 下载

      官网下载mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

    2. 解压

      mkdir mysql
tar -xvf mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar -C mysql

      • 1
      • 2

    3. 安装

      cd mysql
rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-client-8.0.29-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-server-8.0.29-1.el7.x86_64.rpm --nodeps --force

      • 1
      • 2
      • 3
      • 4
      • 5

    4. 对mysql初始化配置

      mysqld --initialize
chown mysql:mysql /var/lib/mysql -R

      • 1
      • 2

    5. 启动MySQL服务

      systemctl start mysqld.service
systemctl enable mysqld

      • 1
      • 2

    6. 改密码

      cat /var/log/mysqld.log | grep password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root123.';

      • 1
      • 2

    7. 改权限

      use mysql;
select Host ,User from user;
update user set Host='%' where User='root';

      • 1
      • 2
      • 3

    登录

    mysql [-h 127.0.0.1] [-P 3306] -u root -p

    • 1

    3、数据模型

    关系数据库（RDBMS）

    建立在关系数据模型下，由多张互相连接的二维表组成的数据库

    特点：

    1. 是用表存储数据，格式统一，便于维护。
    2. 使用SQL语言操作，标准统一，使用方便

    4、SQL

    • 可以单行也可以多行，以分号结尾
    • 可使用缩进增加可读性
    • 不区分大小写，但关键字建议大写
    • 注释
      • 单行注释： --注释内容
      • 多行注释：/*注释内容*/

    SQL分类

    • DDL：数据定义语言，定义数据库对象（数据库，表，字段）
    • DML：数据操作语言，增删改
    • DQL：数据查询语言，用于查询
    • DCL：数据控制语言，用来创建数据库用户，控制数据库的访问权限。

    4.1、DDL

    数据库

    • 查询
      • show databases; 查询所有数据库
      • select database();查询当前数据库
    • create database [if not exits] 数据库名 [default charset 字符集] [collate 排序规则];创建
    • drop database [if exits]数据库名; 删除
    • use 数据库名; 使用

    数据表

    • show tables;查询当前数据库所有表

    • desc 表名;查询表结构

    • show create tables 表名;查询指定表的建表语句

    • create table 表名(

      字段1 字段1类型 [comment 字段1注释],

      字段1 字段1类型 [comment 字段1注释],

      )[comment 表注释]; 创建表

    • 修改 alter table 表名 add/modify/change/drop/rename to …;

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

    • drop table [if exits] 表名; 删除表

    • 数值类型

      image-20220517163656932

    • 字符串类型

      image-20220517163732400

    • 日期与时间类型

      image-20220517163759329

    4.2、DML

    • 添加数据

      • insert into 表名(字段1,字段2,…)values(值1,值2,…);给指定字段添加数据
      • insert into 表名 values(值1,值2,…);给全部字段添加数据
      • 批量添加数据
        • insert into 表名(字段1,字段2,…)values(值1,值2,…),(值1,值2,…),(值1,值2,…);
        • insert into 表名 values(值1,值2,…),(值1,值2,…),(值1,值2,…);
      • 注意：顺序要对应，字符串和日期需要在引号中，数据大小需要在字段规定范围内。

    • update 表名 set 字段名1=值1,字段名2=值2,…[where 条件]; 修改语句,不写where就是更新表中的所有

    • delete from 表名 [where 条件];删除数据，不能删除某一个字段的值，只能删除某一条数据

    4.3、DQL

    语法

    select
	字段列表
from
	表名列表
where
	条件列表
group by 
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    查询分类

    • 基本查询

      • 查询多个字段

        select 字段1，字段2 … from 表名;

        select * from 表名;

      • 设置别名

        select 字段1[as 别名1] … from 表名;

      • 去除重复记录

        select distinct 字段列表 from 表名;

    • 条件查询（where）

      • < > >= <= <> != =
      • between … and … 在某个范围之间
      • in(…) 在括号值之中
      • like 占位符 模糊匹配，%多个字符，_单个字符
      • is null 是否为null
      • and ,or, not 逻辑运算符

    • 聚合函数（count，max，min，avg，sum）

      • count 统计数量

      • max 最大值

      • min 最小值

      • avg 平均值

      • sum 求和

      • select 聚合函数(字段列表) from 表名;

        null值不参与聚合运算

    • 分组查询（group by）

      • select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
      • where 与having的区别
        • where 分组前先执行过滤。然后having是分组后在进行过滤
        • where 不能对聚合函数进行判断，而having可以
      • 执行顺序：where >聚合函数> having
      • 分组后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义

    • 排序查询（order by）

      • select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;

      • ASC :升序（默认）

        DESC:降序

    • 分页查询（limit）不同数据库有不同方式，MySQL是limit

      • select 字段列表 from 表名 limit 起始索引,查询记录数;
      • 其实索引从0开始，起始索引=(查询页码-1)*每页记录数
      • 如果查询第一页，可以省略起始索引

    执行顺序
    from
    where
    group by,having
    select
    order by
    limit

    4.4、DCL

    用户管理：

    • 查询用户

      use mysql;

      select * from user;

    • 创建用户

      create user '用户名'@'主机名' identified by ’密码';

    • 修改用户密码

      alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

    • 删除用户

      drop user '用户名'@'主机名';

    权限控制：

    • 查询权限

      show grants for '用户名'@'主机名';

    • 授予权限

      grant 权限列表 on 数据库名.[表名] to '用户'@'主机名;'

    • 撤销权限

      revoke 权限列表 on 数据库名.[表名] from '用户名'@'主机名'

    • 可使用 * 的通配符，代表所有

    • 常用权限

      image-20220519115617772

    5、函数

    字符串函数：

    select 函数(参数)

    • contact(s1,s2,s3) 字符串拼接为一个字符串
    • lower(str) 全部转换成小写
    • upper(str) 全部转换成大写
    • LPAD(str,n,pad) 用字符串pad对str的左边进行填充，达到n个字符串长度
    • RPAD(str,b,pad)右填充
    • TRIM 去掉头尾的空格
    • SUBSTRING(str,start,len) 返回字符串str从start起len个长度的字符串

    数值函数：

    • ceil(x) 向上取整
    • floor(x) 向下取整
    • mod(x,y) 返回x/y的模
    • rand() 返回0-1的随机数
    • round(x,y) 求x的四舍五入的值，保留y位小数

    日期函数：

    • curdate() 返回当前日期

    • curtime() 返回当前时间

    • now() 返回当前日期和时间

    • year(date) 获取指定date的年份

    • month(date)

    • day(date)

    • DATE_ADD(date,INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值

      类似于 select date_add(now(),INTERVAL 70 DAY);

    • 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默认值

      例：查询emp表的员工姓名和工作地址(北京/上海—>一线城市，其他---->二线城市)

      select
	name,
	(case workaddress
    	when '北京' then '一线城市' 
    	when '上海' then '一线城市' 
    	else '二线城市' 
    end) as '工作地址'
	from emp;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    • CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END 如果expr的值等于val1，返回res1，…，否则返回default默认值

      展示及格情况

      select
	id,
 	name,
 	(case 
     	when math>=85 then ’优秀 
     	when math>=60 then '及格' 
     	else '不及格' 
     end) '数学',
 	(case when english>=85 then ’优秀 when english>=60 then '及格' else '不及格' end) '英语',
 	(case when chinese>=85 then ’优秀 when chinese>=60 then '及格' else '不及格' end) '语文'
 from score;
 

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12

    6、约束

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

    目的：保证数据库数据的正确，有效性和完整性

    地点：作用于表中字段上，可以在创建表/修改表时候添加约束

    分类：

    image-20220519142634811

    常用约束：

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

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名); 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    外键约束：

    • 添加外键

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

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名); 

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7

    • 删除外键

      alter table 表名 drop foreign key 外键名称

      • 1

    外键删除行为：

    image-20220519144023334

    方式:

    alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) oon (操作) [行为];

    • 1

    7、多表查询

    概述：指从多张表查询数据（消除无效笛卡尔积）

    关系：

    • 一对一：在多的一方设置外键，关联一的一方的主键
    • 一对多：建立中间表，中间表包含俩个外键，关联俩张表的主键
    • 多对多：用于表的结构拆分，在其中任何一方设置外键(unique)。关联另一方的主键

    分类：

    • 连接查询

      • 内连接：相当于查询A，B交集部分数据

        • 隐式内连接

          select 字段列表 表1，表2 where 条件…;

        • 显式内连接

          select 字段列表 from 表1 [inner] join 表2 on 连接条件…;

      • 外连接：

        • 左外连接：查询左表所有数据，以及俩张表的交集部分数据

          select 字段列表 from 表1 left [outer] join 表2 on 条件;

        • 右外连接：查询右表所有数据，以及俩张表的交集部分数据

          select 字段列表 from 表1 right [outer] join 表2 on 条件;

      • 自连接：当前表与自身的连接查询，自连接必须用表别名

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

      • 联合查询-nuion,union all(就是把多次查询结果合并起来，形成一个新的结果集)

        select 字段列表 from 表A …

        union(ALL)

        select 字段列表 from 表B…;

        union all 不去重，union去重

        必须保证列数和字段类型一致

    • 子查询

      sql语句中嵌套select语句，成为嵌套查询，又称子查询

      select * from t1 where colunm1=(select column1 from t2);

      子查询外部语句可以是 insert/update/select/delete中的任何一种

      根据子查询结果不同，分为：

      • 标量子查询(结果为单个值)

        • 常用操作符(= ,<>,>,>=,<,<=)

      • 列子查询(查询结果为一列)

        • 常用操作符(in,not in,any,some,all)

          some 与any等同，用some的地方均可用any，含义为子查询列表中，有任意一个满足即可

      • 行子查询(查询结果为一行)

        • 常用操作符(=,<>,in,not in)

        • eg：

          select 
	*
from 
	emp
where (salary,managed)=(
	select salary,manageid from emp where name='张无忌'	
);

          • 1
          • 2
          • 3
          • 4
          • 5
          • 6
          • 7

      • 表子查询(查询结果为多行多列)

        • 常用操作符(in)

          select 
	*
from 
	emp
where (salary,managed)=(
	select salary,manageid from emp where name='张无忌' or name='李时珍'	
);

          • 1
          • 2
          • 3
          • 4
          • 5
          • 6
          • 7

      根据子查询位置，分为：

      • where 之后
      • from之后
      • select之后

      适当的拆分语句然后组合，可以减少工作量，增大准确率

    8、事务

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

    默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL就会立即隐式的提交事务。

    操作：

    • 方式1

      • 查看/设置事务提交方式

        select @@autocommit;--默认为自动，显示为1;
        set @@autocommit=0;--改为手动提交

      • 提交事务

        commit;

      • 回滚事务

        rollback;

    • 方式2

      • 开启事务

        start transaction 或begin;

      • 提交事务

        commit;

      • 回滚事务

        rollback;

    事务的四大特性：

    • **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
    • **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
    • **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
    • **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

    事务并发问题：

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

    事务的隔离级别：

    隔离级别脏读不可重复读幻读
    未提交读(Read Uncommitted)√√√
    提交读(Read Committed)×√√
    可重复读(Repeated Read)（默认）××√
    串行读(Serializable)×××

    查看事务隔离级别：

    select @@TRANSACTION_ISOLATION;

    俩个@@表示当前系统的变量信息

    设置事务隔离级别：

    SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED| READ COMMITED|REPEATABLE READ|SERIALIZABLE}

    session：会话级别，当前客户端窗口有效
    global：对于所有级别

    事务隔离级别越高，越安全，但性能越低

    9、存储引擎（mysql默认InnoDB）

    MySQL体系结构：

    • 连接层
    • 服务层
    • 引擎层（索引在这一层）
    • 存储层

    在这里插入图片描述

    简介：

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

    1. 建表指定存储引擎

      create table 表名(
	字段1 字段1类型 [comment 字段1注释],
	字段1 字段1类型 [comment 字段1注释],
)ENGINE=INNODB  [comment 表注释];

      • 1
      • 2
      • 3
      • 4

    2. 查看数据库支持的存储引擎

      show engines;

      • 1

    9.1、InnoDB

    介绍：

    高可靠性，高性能，MySQL5.5后作为默认引擎

    特点:

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

    文件：

    xxx.ibd：xxx是表名，innoDB引擎的每个表对应一个这样的文件，存储表的表结构（frm，sdi），数据和索引。

    参数： innodb_file_per_table

    逻辑存储结构：

    • 表空间：InnoDB所有数据都存放
    • 段：常见的段有数据段、索引段、回滚段等
    • 区：区是由连续的页组成的空间，每个区大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘中申请4~5个区。在默认情况下，InnoDB存储引擎页大小为16KB，即一个区中一共有64个连续的页。
    • 页：页是InnoDB磁盘管理的最小单位，默认每个页大小为16KB
    • 行：InnoDB存储引擎是按行进行存放的。一个页中存放的行数据越多，其性能越高，这也是为什么创建字段的时候应该按照最小可用原则。

    image-20220520104520377

    9.2、MyISAM

    **介绍：**MyISAM是mysql早期的默认存储引擎

    特点：

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

    文件：

    xxx.sdi：存储表结构信息

    xxx.MYD：存储数据

    xxx.MYI：存储索引

    9.3、Memory

    **介绍：**数据存储在内存种，由于受到硬件，断电等问题，智能将这些表作为临时表或者缓存。

    特点：

    • 内存存放
    • hash索引（默认）

    文件：

    xxx.sdi:存储表结构

    不同引擎的区别：

    image-20220520105330563

    选择方式：

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VBi90MzE-1659003924686)(C:\Users\高晓如\AppData\Roaming\Typora\typora-user-images\image-20220520105720592.png)]

    10、索引

    10.1、索引概述

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

    优点

    • 提高数据检索的效率，降低数据库的IO成本
    • 通过索引对数据列排序，降低数据排序的成本，降低CPU的消耗

    缺点

    • 索引列也是要占用空间的
    • 索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行INSERT，UPDATE，DELETE时，效率降低

    10.2、索引结构

    索引在引擎层，不同引擎索引结构不同。

    索引结构描述InnoDBMyISAMMemory
    B+Tree索引最常见的索引类型，大部分引擎都支持B+树索引支持支持支持
    Hash索引底层数据结构为哈希表，只有精确匹配索引列的查询才有效，不支持范围查询不支持不支持支持
    R-Tree（空间索引）空间索引时MyISAM引擎的一个特殊索引类型，主要用于地理控件数据类型，通常使用较少不支持支持不支持
    Full-text（全文索引）是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene,Solr,ES5.6版本后支持支持不支持

    索引没有特别指明，都是B+数结构组织的索引。

    BTree(多路平衡查找树)

    数据结构可视化网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

    中间元素向上分裂，然后构建树

    B+Tree

    与BTree的区别：

    1. 所有数据都会出现在叶子节点
    2. 叶子节点会形成一个单向链表

    MySQL中的B+Tree对经典B+Tree做了优化，增加了指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

    image-20220520161755287

    hash

    采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。hash冲突使用链表解决。

    特点：

    1. hash索引只能用于对等比较(=,in)，不支持范围查询（between，>，<,…）
    2. 无法利用索引完成排序操作
    3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引

    存储引擎支持：

    支持hash索引的为Memory引擎，而innodb中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

    为什么InnoDB使用B+Tree？

    • 相对于二叉树，层级更少，搜索效率高
    • 对于B-tree，无论叶子节点还是非叶子节点，都会保存数据，这样导致一页存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
    • 相对于hash索引，B+Tree支持范围搜素和排序操作

    10.3、索引分类

    分类含义特点关键字
    主键索引针对表中的主键创建的索引默认自动创建。只能有一个primary
    唯一索引避免同一个表中某列中的值重复可以有多个unique
    常规索引快速定位特定数据可以有多个
    全文索引全文索引查找的是文本中的关键字，为不是比较索引中的值可以有多个fulltext

    InnoDB存储引擎中，根据索引的存储形式，又可以分为下面俩种:

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

    聚集索引选取规则：

    • 有主键，主键索引即是聚集索引
    • 无主键，第一个唯一索引即是
    • 无主键，无唯一索引，则innodb自动生成一个rowid作为隐藏的聚集索引

    回表查询：

    先去二级索引找值，然后回到聚集索引找行所有数据。

    image-20220520164509226

    10.4、索引语法

    • 创建索引

      CREATE [UNIQUE|FULLTEXT] INDEX index name ON 表名(列名 排序情况 ,...);

      • 1

    • 查看索引

      SHOW INDEX FROM 表名;

      • 1

    • 删除索引

      DROP INDEX index_name ON 表名;

      • 1

    10.5、SQL性能分析

    SQL执行频率:

    MySQL可以通过 show[session|global]status查看服务器状态信息。下面命令可以查看数据库个操作的执行频次。

    SHOW GOLBAL STATUS LIKE 'Com_______'

    • 1

    慢查询日志：

    慢日志查询记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有sQL语句的日志。

    MySQL慢查询日志默认关闭，需要在配置文件（/etc/my.cnf）中配置如下信息：

    slow_query_log=1  //慢查询日志开关
long_query_time=2 //时间阈值

    • 1
    • 2

    配置好后重启，然后在慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

    profile详情：

    show profiles 能够展示时间耗费在哪里。

    • 查看是否支持profile详情

      select @@ having_profiling

      • 1

    • 查看是否打开

      select @@profiling

      • 1

    • 默认关闭

      set profiling=1;打开。

    • 执行一系列SQL操作，通过如下指令查看详情

      • 查看每一条SQL的耗时基本情况

        show profiles;

        • 1

      • 查看指定query_id的sql语句各个阶段耗时情况

        show profile for query query_id;

        • 1

      • 查看指定query_id的sql语句CPU的使用情况

        show profile cpu for query query_id;

        • 1

    explain执行计划：

    explain 或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。

    语法：

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

    • 1

    各字段含义：https://blog.csdn.net/pipizhen_/article/details/115335294

    10.6、索引使用规则

    比对效率：创索引前SQL执行时间，创索引后执行时间，做对比。

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

    范围查询：联合索引中，出现范围查询(>,<),范围查询右侧的索引失效。所以尽量使用大于等于或者小于等于。

    索引失效情况：

    • 字符串不加引号，字符串类型字段使用时，不加引号，索引将失效。
    • 索引列运算，不要再索引列上进行运算操作，索引列将失效。
    • 模糊查询，如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
    • or连接的条件，用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
    • 数据分布影响，如果MySQL聘雇使用索引比全表更慢，则不使用索引。

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

    • user index;建议用

      explain select * from 表名 use index(索引名) where 条件;

      • 1

    • ignore index;不用

      explain select * from 表名 ignore index(索引名) where 条件;

      • 1

    • force index;强制用

      explain select * from 表名 force index(索引名) where 条件;

      • 1

    覆盖索引：

    尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引列已经全部找到），减少select *;

    • using index condition:查找使用了索引，但是需要回表查询数据
    • using where；using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。

    **前缀索引：**对文本的前几个字符建立索引（具体是几个字符在建立索引时指定），这样建立起来的索引更小，所以查询更快。

    • 语法：

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

    • 前缀长度：

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

    单列索引与联合索引：

    • 单列索引：即一个索引只包含单个列。
    • 联合索引：即一个索引包含了多个列。

    在业务场景中，如果存在多个查询条件，考虑针对查询字段建立联合索引，而非单列索引。

    多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

    10.7、索引设计原则

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

    11、sqL优化

    插入数据优化

    • insert优化

      • 批量插入

        insert into 表名 values(值1),(值2),…;

      • 手动提交事务

        start transaction
插入语句1
插入语句2
...
commit;

        • 1
        • 2
        • 3
        • 4
        • 5

      • 主键顺序插入

        1，2，3，4，5....

        • 1

    • 大批量插入

      使用MySQL提供的load指令进行插入数据

      #客户端连接服务端加参数
mysql --local-infile -uroot -p
#色湖之全局参数local_infile=1，开启文件导入数据开关
set global local_infile=1;
#执行load指令
load data local infile 文件路径 into table 表名 fields terminated by ',' lines terminated by '\n';

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    主键优化

    数据组织方式：在innodb引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表为索引组织表

    • 页分裂

      页可为空，也可填充一半，也可填充100%，每个页包含了2-n行数据（如果一行数据过大，会行溢出），根据主键排列。（主键乱序插入，就可能会产生页分裂）

    • 页合并

      当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。

      当页中删除的记录达到MERGE_THRESHOLD(默认为50%)，innodb会开始寻找最靠近的页（前或后）看看是否可以将俩个页合并并以优化空间使用。

      MERGE_THRESGOLD：页合并的阈值，可以自己设置，在创建表或者创建索引时候指定

    主键设计原则:

    • 满足业务需求情况，尽量减少主键长度

    • 插入数据时候尽量选择顺序插入，选择使用 AUTO——INCEREMENT自增主键

    • 尽量不要使用UUID作为主键或者其他自然主键，如身份证号

    • 业务操作时，避免对主键的修改。

    order by优化

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

    优化方式：

    • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
    • 尽量使用覆盖索引
    • 多字段排序，一个升序，一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
    • 如果不可避免filesort，大量数据排序时，可以适当增大排序缓存去大小 sort_buffer_size(默认256k)

    group by优化

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

    limit优化

    当 limit 2000000，10时，需要排序前2000010条记录，然后返回后十个记录，丢弃其他。代价非常大

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

    select 
	* 
from 表1 t,
	(select id from 表1 order by id limit 2000000,10)  a
where 
	t.id=a.id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    count优化

    • MyISAM引擎把一个表的总行数记录在磁盘上，因此count(*)比较快。
    • InnoDB 引擎比较麻烦，执行时候需要一行一行读出来，然后累计计数

    优化思路：自己计数

    count的几种用法：

    • count()是一个聚合函数，对于返回结果集，如果count的参数部位null，累计值加1，否则不加，最后返回累计值
    • 用法：
      • count(*): innodb并不会把所有字段取出来，而是作了专门优化，不取值，服务层直接按行累加
      • count(主键)：innodb遍历整张表，把每一行主键取出，返回给服务层，服务层拿到主键后，直接进行累加。
      • count(字段)：
        • 没有notnull约束：innodb遍历整张表，把每一行字段值取出，返回给服务层，服务层判断是否为null，计数累加。
        • 有notnull约束：innodb遍历整张表，把每一行字段值取出，返回给服务层，服务层直接按行计数累加。
      • count(1)：innodb遍历整张表，不取值出，服务层对于返回的每一行，放一个1进去，直接按行累加

    效率排序： c o u n t ( 字段 ) < c o u n t ( 主键 ) < c o u n t ( 1 ) ≈ c o u n t ( ∗ ) count(字段)count(字段)<count(主键)<count(1)≈count(∗),所以尽量使用count(*)

    update优化

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

    12、视图/存储过程/触发器

    12.1、视图

    介绍：视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。通俗的讲，视图只保存了查询SQL的逻辑，不保存查询结果，所以我们创建视图，主要工作就落在创建这条SQL语句上.

    语法：

    • 创建

      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 table 视图名称[(列名列表) ] as select语句 [with [cascaded|local] check option];

    • 删除视图

      drop view [if exists] 视图名称 [,视图名称] …;

    试图检查选项：

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

    • cascaded：如果当前视图有检查选项，则插入数据要满足包括当前视图条件以及满足当前视图所依赖的视图的条件。如果当前视图没有检查选项，则插入数据要满足当时视图所依赖视图有检查选项及其依赖的视图的条件。
    • local：检查选项是递归的查找当前视图所依赖的视图是否有检查选项，如果有，则检查；如果没有，就不做检查。

    视图更新：

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

    1. 聚合函数或窗口函数（sum(),min(),max(),count()等）
    2. distinct
    3. group by
    4. having
    5. union 或union on

    作用：

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

    12.2、存储过程

    定义：

    存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

    思想就是SQL语言层面上的代码封装与重用

    特点：

    1. 能完成较复杂的判断和运算
    2. 可接受参数，可编程行强，灵活
    3. SQL编程的代码可重复使用
    4. 执行的速度相对快一些
    5. 减少网络之间的数据传输，节省开销,效率提升

    语法：

    • 创建

      create procedure 名称([参数列表])
begin
	SQL语句;
end;

      • 1
      • 2
      • 3
      • 4

      注意：在命令行中创建时候需要delimiter 指定SSQL语句的结束符，防止碰见;语句提前结束报错

      例如

      delimiter $$;
create procedure 名称([参数列表])
begin
	SQL语句;
end$$

      • 1
      • 2
      • 3
      • 4
      • 5

    • 调用

      call 名称([参数])

      • 1

    • 查看

      select * from information_schema.routines where routine_schema='数据库名'; 
show create procedure 名称;

      • 1
      • 2

    • 删除

      drop proceduce [if exists] 名称

      • 1

    变量:

    • 系统变量： MySQL服务器提供的，不是用户定义的，属于服务器层面，分为全局变量（global），局部变量（session）

      • 查看系统变量

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

        • 1
        • 2
        • 3

      • 设置系统变量

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

        • 1
        • 2

        不指定session/gobal，默认为session

        MySQL重启后，所有设置的全局参数会失效，如果不想失效，可以在/etc/my.cnf中配置

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

      • 赋值

        set @var_name=expr,[,@var_nam=expr]...;
set @var_name:=expr,[,@var_nam:=expr]...;

        • 1
        • 2
        select @var_name:=expr,[,@var_nam:=expr]...;
select 字段名 into @var_name from 表名

        • 1
        • 2

      • 使用

        select @var_name;

        • 1

        用户定义变量无需对其进行声明或初始化，只不过获取到的值为null

    • 局部变量：是更具需要定义的在局部生效的变量，访问前，需要declare 声明。可用于存储过程内的局部变量和输入参数，局部变量的范围是在其声明的begin…end块。

      • 声明：

        declare 变量名 变量类型[default...];

        • 1

        变量类型：int，bigint，char，varchar，date，time等

      • 赋值

        set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名from 表名...;

        • 1
        • 2
        • 3

      • 例子

        create procedure test2()
begin
  -- 使用 declare语句声明一个变量
  declare username varchar(32) default '';
  -- 使用set语句给变量赋值
  set username='xiaoxiao';
  -- 将users表中id=1的名称赋值给username
  select name into username from users where id=1;
  -- 返回变量
  select username;
end;

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11

    if判断:

    • 语法：

      if 条件1 then...
elseif 条件2 then...
else ...
end if;

      • 1
      • 2
      • 3
      • 4

    • 例子

      create procedure test7(in userId int)
begin
   declare username varchar(32) default '';
   if(userId%2=0)
   then
      select name into username from users where id=userId;
      select username;
   else
      select userId;
      end if;
end;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11

    参数：

    • 类别

      类型
      in输入，当传入值默认
      out输出，当返回值
      inout即可输入，也可输出

    • 用法

      create procedure 存储过程名称([IN|OUT|INOUT] 参数名 参数类型 )
begin
	SQL语句
end

      • 1
      • 2
      • 3
      • 4

    • 例子:传入id,根据id返回name

      create procedure test4(in userId int,out username varchar(32))
begin
	select name into username from users where id=userId;
	select username;
end;

      • 1
      • 2
      • 3
      • 4
      • 5
      create procedure test4(inout score double)
begin
	set score:=score*0.5;
end;

      • 1
      • 2
      • 3
      • 4

    case

    • 语法

      • CASE case_val 
	WHEN [val1] THEN 语句1
	[WHEN [val2] THEN 语句2]
	[ELSE 语句列表]  
END CASE; 

        • 1
        • 2
        • 3
        • 4
        • 5
      • CASE 
	WHEN 条件1 THEN 语句列表1
	[WHEN 条件2 THEN 语句列表2]
	[ELSE 语句列表]  
END CASE; 

        • 1
        • 2
        • 3
        • 4
        • 5

    **while：**循环控制语句

    • 语法

      while(表达式) do 
   SQL逻辑
end while;

      • 1
      • 2
      • 3

    • 例子：使用循环语句，向表test1(id)中插入10条连续的记录

      create procedure test9()
begin
  declare i int default 0;
  while(i<10) do 
    begin
        select i;
        set i=i+1;
        insert into test1(id) values(i);
     end;
  end while;
end;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11

    repeat有条件的循环控制语句，当满足条件时候退出循环

    • 语法：类似于 do while

      repeat
	SQL逻辑
	until 条件
end repeat;

      • 1
      • 2
      • 3
      • 4

    • 例子：给test1表中的id字段插入数据，从1到10

      create procedure test10()
begin
    declare i int default 0;
    repeat 
    begin
        select i;
        set i=i+1;
        insert into test1(id) values(i);
    end;
    until i>=10 -- 如果i>=10,则跳出循环
    end repeat;
end;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12

    loop

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

      • LEAVE：配合循环使用，退出循环
      • ITERATE：必须使用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

    • 语法：

      [begin_label:]LOOP
	SQL逻辑
END LOOP [end_label]

      • 1
      • 2
      • 3
      LEAVE label;--退出指定标记的死循环
ITERATE label; --直接进入下一次死循环

      • 1
      • 2

    • 例子

      累计1-n的值

      create procedure test10()
begin
    declare total int default 0;
    sum:loop
    	if n<=0 then
    		leave sum;
    	end if;
    	set total:=total+n;
    	set n:=n-1;
    end loop sum;
    select total
end;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12

      累计1-n的偶数的值

      create procedure test10()
begin
    declare total int default 0;
    sum:loop
    	if n<=0 then
    		leave sum;
    	end if;
    	if n%2=1 then
    		iterate sum;
    	end if
    	set total:=total+n;
    	set n:=n-1;
    end loop sum;
    select total
end;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15

    游标

    • 定义：是用来存储查询结果集的数据类型，在储存过程和函数中可以使用游标对结果集进行循环处理。

    • 语法

      • 声明游标

        declare 游标名称 cursor for 查询语句;

        • 1

        注意：需要声明在所有变量的最后位置

      • 打开游标

        open 游标名称

        • 1

      • 获取游标记录

        fetch 游标名称 into 变量[,变量];

        • 1

      • 关闭游标

        close 游标名称

        • 1

    • 例子：使用游标，把users表中 id为偶数的记录逐一更新用户名

      create procedure test11()
    begin
        declare stopflag int default 0;
        declare username VARCHAR(32);
        -- 创建一个游标变量.
        declare username_cur cursor for select name from users where id%2=0;
        -- 当游标变量中保存的结果都查询一遍(遍历),到达结尾，将变量stopflag设置为1，用于循环中判断是否结束
        declare continue handler for not found set stopflag=1;

        open username_cur; -- 打开游标
        -- 游标向前走一步，取出一条记录放到变量username中
        fetch username_cur into username; 
        while(stopflag=0) do -- 如果游标还没有结尾，就继续
            begin
                -- 在用户名前拼接 '_cur' 字符串
                update users set name=CONCAT(username,'_cur') where name=username;
                fetch username_cur into username;
            end;
        end while; -- 结束循环
        close username_cur; -- 关闭游标
    end;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21

    条件处理程序

    多少有点看不懂

    • 概念：条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

    • 语法：

      DECLARE handler_action HANDLER FOR condition_value [,condition_value]...statement;

      • 1
      • handler_action
        • continue:继续执行
        • exit：终止当前程序
      • condition_value
        • SQLSTATE sqlatate_value:状态码，如02000
        • SQLWARNING：所有以01开头的SQLSTATE代码的简写
        • NOT FOUND:所有以02开头的SQLSTATE代码的简写
        • SQLEXECPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

    12.3、存储函数

    存储函数是有返回值的存储过程，存储函数的参数只能是in类型的。

    语法：

    create function 存储函数名称([参数列表])
returns type [characteristic ...]
begin 
	--SQl语句
	return ...;
end;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    characteristic说明：

    • deterministic:相同数额u参数总是产生相同结果
    • no sql ：不包含SQL语句
    • reads sql data：包含读取数据的语句，但不残生写入数据的语句

    例子：从1-n的累加

    create function fun1(n int)
returns int deterministic
begin 
	declare total int default 0;
	while n>0 do
		set total:=total+n;
		set n:=n-1;
	end while;
	return total;
end;


--调用
select fun1(10);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    12.4、触发器

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

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

    触发器类型NEW和OLD
    insert型触发器NEW表示将要或者已经新增的数据
    update型触发器OlD表示修改之前的数据，NEW表示将要或已经修改后的数据
    delete型触发器OLD表示将要或者已经删除的数据

    语法：

    • 创建

      create trigger tigger_name
before/after insert.update.delete
on tbl_name for each row --行级触发器
begin 
	trigger_stmt;
end;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    • 查看

      show triggers;

      • 1

    • 删除

      drop trigger [schema_name.] tigger_name;

      • 1

    13、锁

    锁： 锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制(以上内容来自必应)。通俗点来说锁机制就是用来管理对共享资源的并发操作，从而保证数据的正确性。

    分类：MySQL按照锁的粒度分为：

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

    13.1、全局锁

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

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

    语法：

    • 加锁：

      flush tables with read lock;

      • 1

    • 数据库备份

      mysqldump -uroot -p密码 数据库 >备份名.sql

      • 1

      不是mysql命令，在外部运行。

    • 解锁：

      unlock tables;

      • 1

    特点:

    数据库中加全局锁，是一个比较重的操作，存在以下问题

    1. 如果在主库备份，那么备份期间都不能执行更新，业务基本上就得停摆
    2. 如果在从库上备份，那么备份期间从库布恩那个执行主库同步过来的二进制日志，会导致主从延迟

    在innodb中，我们可以在备份时加上参数 --single-transaction参数完成不加锁的一致性数据备份

    mysqldump --single-transaction -uroot -p密码 数据库 >备份名.sql

    • 1

    13.2、表级锁

    每次锁住整张表。锁定粒度大，发生说冲突的概率最高，并发度低，应用在MyISAM，InnoDB，BDB等存储引擎中。

    主要分为以下三类：

    1. 表锁

      • 分类：

        • 表共享读锁(read lock)

          不同客户端都可可DQL，不可DML/DDL

        • 表独占写锁(write lock)

          加锁客户端可DQL/DML/DDL，其他客户端均不可。

      • 语法

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

    2. 元数据锁(meta data lock,MDL)

      • 概念

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

        在MySQL5.5中引入了MDL，当对一张表增删改查时候，加MDL读锁（共享）；当对表结构进行变更时候，加MDL写锁（排他）。

        image-20220522143950295

      • 查看元数据锁

        select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

        • 1

    3. 意向锁

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

      • 分类

        1. 意向共享锁（IS）：由语句 select …lock in share mode 添加，与表锁共享锁（read）兼容，与表锁排他锁（write）互斥。
        2. 意向排他锁（IX）：由insert，update，select 。。。for update 添加。与表锁共享锁（read）及排他锁（write）都互斥。意向锁之间不会互斥。

      • 查看意向锁及行锁情况：

        select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

        • 1

    13.3、行级锁

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

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

    1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务进行update和delete，在RC，PC隔离级别下都支持

      • 分类：

        1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

        2. 排他锁（X）：允许获取排他锁的事务更新，阻止其他事务获得相同数据集的共享锁和排他锁。

          image-20220522151759463

      • 不同类型操作加的锁

        image-20220522151952479

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

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

      • 查看行锁加锁情况

        select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

        • 1

    2. 间隙锁（Gap Lock）：锁定索引记录的间隙（不含该记录），确保索引记录间隙不变，放置其他食物在这个间隙进行insert，产生幻读。在PR隔离级别下都支持。

      • 默认情况下，InnoDB在REPEATABLE READ 事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。
        1. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
        2. 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
        3. 索引上的范围查询（唯一索引）——会访问到不满足条件的第一个值为止。
      • 注意：间隙锁唯一存在目的时防止其他事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

    3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在PR隔离级别下支持。

    14、InnoDB核心

    14.1、逻辑存储结构

    image-20220522153455230

    14.2、架构

    架构图：左为内存结构，右为磁盘结构

    image-20220522161401643

    内存架构

    • Buffer Pool：缓冲池时用来缓存磁盘上经常操作的真实数据,在增删改查时先操作缓冲池中的数据(缓冲中没有数据则上磁盘去找),然后以一定频率刷新到磁盘，减少磁盘IO

      • 以页为单位,底层采用链表数据结构管理Page,
        • free page(空闲页)
        • clean page(被使用,但数据没被修改)
        • drity page(被使用,且数据被修改)

    • Change Buffer：更改缓冲区是针对非唯一二级索引页,在执行DML语句时，这些数据的Page没有在Buffer Pool中，不会直接操作磁盘，二回将数据变更存在Change Buffer中，未来在数据被读取时，再将数据合并回复到buffer pool理，再将合并数据刷新到磁盘。

      • 因为修改的数据会相对随机的插入二级索引(难有顺序),如果每次都操作磁盘会有很大的磁盘IO,先存在这里,再合并到缓冲池中,减少IO。

    • Adaptive Hash Index：自适应hash索引，用于优化缓冲池中的数据查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提高速度,自动建立哈希索引。

      需人工干涉，系统自动完成；参数：adaptive_hash_index

    • Log Buffer :日志缓冲区,用来保存要写入到磁盘中的log日志数据（redo log，undo log）,默认大小16mb,定期刷新到磁盘中。

      • 参数

        innodb_log_buffer_size:缓冲区大小

        innodb_flush_log_at_trx_commit:日志刷新到磁盘时机

        • 1：每次提交事务写入并刷新
        • 0：每秒刷新到磁盘一次
        • 2：每次事务提交后写入，并每秒刷新一次

    磁盘结构:

    • system Tablespace:系统表空间是’更改缓冲区’的存储区域

    • File-Per_table Tableapaces:文件表空间是每个表的文件表空间包含单个InnoDB表的数据和索引,并存储在文件系统的单个数据文件上

    • General Tablespace：通用表空间是可以创建并指定使用该表空间,默认不用(需要先创建再指定)

      • 创建语法

        create tablespace xxx add
datafile 'file_name'
engine= engine_name

        • 1
        • 2
        • 3

      • 使用语法：

        create table xxx tablespace ts_name;

        • 1

    • Undo Tablespace:撤销表空间,MySQL实例在初始化时会自动创建两个默认的撤销表空间(初始大小16m),用于存储撤销日志(undo log)

    • Temporary Tablespace：临时表空间,innodb使用会话临时表和全局临时表空间，用于存储用户创建的临时表

    • Doublewrite Buffer Files：双写缓冲区，缓存池的数据刷新到磁盘前会先进入这里,便于系统异常时恢复数据

    • 重做日志(redo log): 用来实现事务的持久性,当事务提交后会把所有修改信息存到该日志,用于数据恢复,每隔一段时间清理旧的日志

    后台线程:

    将InnoDB缓冲池中的数据在合适的时间刷新到磁盘当中

    • Master Thread: 核心后台线程,负责调度其他线程,还负责将缓冲池中的数据异步刷新到磁盘中，保证数据一致性，还包括脏页的刷新、合并插入缓存、undo页的存放

    • IO Thread: 在innodb存储引擎中大量使用了AIO来处理IO请求，这样可以大大提高数据库的性能，而IO thread主要负责这些IO请求的回调

      • Read Thread: 负责读操作,默认4
      • Write Thread: 负责写操作,默认4
      • Log Thread: 负责将日志缓冲区刷新到磁盘,默认1
      • Insert buffer Thread: 负责将写缓冲区刷新到磁盘,默认1

    • Purge Thread: 回收事务已经提交的undo log。

    • Page Cleaner Thread: 协助Master Thread刷新脏页到磁盘的线程，减轻master Thread 的工作压力，减少阻塞。

    14.3、事务原理

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

    特性:

    • 原子性A-undo log

    • 一致性C- redo log

    • 隔离性I - undo log+re dolog

    • 持久性D - 锁+MVCC

    隔离级别(从低到高):读未提交内容RU,读取提交内容RC,可重复读RR,串行化S

    其中ACD是由redo log和undo log实现的,I是由锁机制和MVCC实现的

    • redo log(保证持久性):

      重做日志,记录的是事务提交数据页的物理修改,在脏页刷新失败时能够进行数据恢复,物理日志

    • undo log(保证原子性):

      回滚日志,用于记录数据被修改前的信息,提供回滚和MVCC,逻辑日志,记录反向的事务操作

    • 一致性由redo log和undo log共同保证

    14.4、MVCC（多版本并发控制）

    基本概念

    • 当前读

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

    • 快照读

      简单d额select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

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

    • MVCC

      多版本并发控制。指维护一个数据的多个版本，是得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞功能。MVCC的具体实现，还需要依赖数据库记录中的三个隐式字段，undo日志，readView。

    MVCC实现原理

    • 记录当中的隐藏字段

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

      • 查看方法

        打开数据库的根文件夹找到ibd文件

        使用命令 ibd2sdi xxx.ibd

    • undo lod

      • 当insert时,产生的undo log只在回滚时需要,事务提交后可立即删除

        当update,delete时,产生的undo log不仅在回滚时需要,在快照读时也需要,不会立刻删除

      • undo log版本链(用于连续回滚,由DB_ROLL_PTR保存指针,形成了个链表)

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

    • readView

      ReadView（读视图）是 快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id

      • 包含四个核心字段:

        字段含义
        m_ids当前活跃的事务id的集合
        min_trx_id最小活跃事务id
        max_trx_id预分配事务id,当前最大事务id+1(事务id是自增的）
        creator_trx_idReadView创建者的事务id

      • 版本链数据访问规则
        image-20220523150628840

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

        • Read Committed：在事务中每一次执行快照读时生成ReadView
        • Repeatable Read：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

      当前读: 我们读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录加锁.共享锁,排他锁都是一种当前读

      • InnoDB默认的RR是不满足当前读的(在用普通select不加锁的情况下),需要人为的加锁

      快照读: 就是简单的不加锁的select,读取的实时记录数据的可见版本,可能是历史数据,不加锁,是非阻塞读

      • RC: 每次select都会快照读

      • RR: 开启事务后第一个select是快照读,后面相同的select会直接那之前的结果

      • S: 快照读退化成当前读

    15、MySQL管理

    15.1、系统数据库

    8.0以后的版本

    • mysql： 存储MySQL服务器正常运行所需要的各种信息（时区，主从，用户，权限等）
    • information_schema：提供了访问数据库元数据的各种表和视图，包含数据库，表，字段类型和访问权限等。
    • performance_schema：为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器的性能参数。
    • sys：包含了一些列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

    15.2、常用工具

    • mysql：指MySQL的客户端工具

      • 语法：mysql [options] [database]

      • 选项：

        -u #指定用户名
-p #指定密码
-h #指定服务器ip或域名
-P #指定连接端口
-e #执行SQL语句并退出

        • 1
        • 2
        • 3
        • 4
        • 5

    • mysqladmin ：执行管理操作的客户端程序。可以用它检查服务器的配置和当前状态，创建并删除数据库等

      • 示例：

        mysqladmin --help #擦好看帮助
mysqladmin -uroot -p密码 drop '数据库' #删除数据库
mysqladmin -uroot -p密码 version #查看数据库版本

        • 1
        • 2
        • 3

    • mysqlbinlog：查看你服务器生成的二进制日志

      • 语法：mysqlbinlog [option] log-files1 log-files2

      • 选项

        -d #指定数据库
-o #忽略数据库日志的前n行命令
-r #将输出的文本格式日志到指定文件
-s #显示简单格式，省略到一些信息

        • 1
        • 2
        • 3
        • 4

    • mysqlshow 查找工具，用来很快的查找存在那些数据库，数据库当中的表，表中的列或索引

      • 语法：mysqlshow [option] [db_name[table_name[col_name]]]

      • 选项：

        --count #显示数据库及表的统计信息
-i      #显示指定数据库或指定数据表的状态信息

        • 1
        • 2

    • mysqldump 数据库备份迁移工具。备份内容包含创建表，及插入表的sql语句

      • 语法

        mysqldump [options] db_name[tables]
mysqldump [options] --database/-B db1[db2,db3...]
mysqldump [options] --all-database/-A

        • 1
        • 2
        • 3

      • 选项[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HYgSiRED-1659003924698)(C:\Users\高晓如\AppData\Roaming\Typora\typora-user-images\image-20220523154000830.png)]

    • mysqlimport 数据导入工具，用来导入mysqldump 加-T参数导出的文本文件

      • 语法：mysqlimport [options] db_name textfile1 [textfile2...]

    • source 用来导入sql文件

      • 语法：source xxx.sql

    16、日志

    MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件，从日志当中可以查询到MySQL数据库的运行情况、用户的操作、错误的信息等。

    MySQL 日志分类：

    • 错误日志（error log）
    • 查询日志
      • 通用查询日志（general log）
      • 慢查询日志（slow query log）
    • 二进制日志（bin log）
    • 中继日志（relay log）
    • 事务日志
      • 重做日志（redo log）
      • 回滚日志（undo log）

    错误日志（error log）：

    记录MySQL运行过程中的Error、Warning、Note等信息，系统出错或者某条记录出问题可以查看Error日志。

     show variables like 'log_error';

    • 1

    image-20220728173105548

    通用查询日志(general log)：

    通用查询日志记录了用户的所有操作，包括 sql 语句的查询更新，类似于 debug 级别的日志。

    操作：
    查看通用查询日志是否开启：

    show variables like '%general%';

    • 1

    image-20220728173340357

    开启：

    set global general_log=on;

    • 1

    设置日志保存的位置：

    set global general_log_file='D:\\log\\general.lg'

    • 1

    慢查询日志(slow query log)：

    慢查询日志记录了执行时间超过指定阈值的 SQL 语句，主要用于性能瓶颈分析。我们在sql优化时候常用。

    如果启用了慢查询日志，执行时间超过 long_query_time 并且执行次数达到min_examined_row_limit 次的查询语句都会被记录到慢查询日志中。

    操作
    查看慢查询日志是否开启：

    show variables like '%slow_query_log%';

    • 1

    开启：

    set global slow_query_log=1;

    • 1

    设置慢查询日志保存文件：

    set global slow_query_log_file='D:\\log\\slow.log'

    • 1

    时间阈值的查询和设置：

    show variables like 'long_query_time%';

set global long_query_time=8;

    • 1
    • 2
    • 3

    二进制日志(bin log)：

    二进制日志属于逻辑语句的记录，记录了引起或可能引起数据库改变的事件信息，比如update/delete/insert/truncate/create，但并不包括 select 和 show 这样的查询语句。

    作用：复制和恢复数据

    • 在MySQL的一主多从结构中，用于主从数据库的同步
    • 数据库的数据被破坏了，可以用binlog进行复制(主从复制)和恢复数据

    操作
    查看MySQL的二进制日志是否开启：

    show variables like '%log_bin%';

    • 1

    启动二进制日志:
    MySQL 默认是关闭的，可以在配置文件 my.ini 中的 [mysqld] 语句下设置 log-bin。

    [mysqld]
# server_id=1234
log-bin=[on|filename]

    • 1
    • 2
    • 3

    查看二进制日志:
    Show binary logs查看当前的二进制日志文件个数及其文件名。

    mysql> show binary logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| binary_log.000001 |       154 |
+-------------------+-----------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    bin log默认情况下是二进制格式，可以使用mysqlbinlog（mysql官方提供的binlog查看工具）来查看。

    中继日志(relay log)：

    从服务器IO线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL 线程会读取 relay log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

    relay log 除了包含 binlog 的内容，还会记录当前恢复到哪个位置。如果从服务器断开重连，则可以从上次记录的位置开始恢复。

    操作
    查看中继日志：

    show variables like '%relay%';

    • 1

    重做日志（redo log）：

    redo log 记录了对数据文件的物理更改，偏向于结果的记录，并保证总是日志先行（WAL，Write-Ahead Logging），即在持久化数据文件前，保证之前的 redo 日志已经写到磁盘。由于 redo log 是顺序整块写入，所以性能要更好。

    重做日志两部分组成：

    • 重做日志缓冲(redo log buffer)，是易失的
    • 重做日志文件(redo log file)，是持久的

    回滚日志（undo log）：

    ndo log 和bin log 一样也是逻辑日志，记录的是一种相反操作的记录，比如在回滚时，如果是 insert 操作时，则会逆向为 delete，delete 操作时，逆向为 insert 操作，更新则恢复到当时的版本数据。

    回滚日志主要有两个作用：

    • 回滚
    • 多版本控制(MVCC)，保证事务的原子性

    17、主从复制

    概念

    主从复制是将主数据库的DDL和DMl操作通过二进制日志传到从数据库中，然后在从库上对这些日志重新执行，从而是得从库与主库数据保持同步。

    支持方式：

    • 一主一从
    • 主主复制
    • 一主多从
    • 多主一从
    • 级联复制

    原理：

    1. master将修改数据的操作记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）。
    2. slave的io线程将master的binary log events拷贝到它的中继日志(relay log)。
    3. slave的sql线程解析中继日志中的事件并在从库执行，保持与主库的数据一致。

    配置主从复制：

    主库：192.168.0.2

    从库：192.168.0.3

    • 配置

      • 主库设置

        1. 修改配置文件

          vim /etc/my.cnf
#添加
[mysqld] 
log-bin=master-bin 
server-id=1

          • 1
          • 2
          • 3
          • 4
          • 5

        2. 创建主从同步用户

          mysql -u root -p
#创建用户test，设置密码为testpasswd，赋予远程登录权限
create user test identified by 'test'; 
grant all privileges on *.* to 'test'@'%' identified by 'testpasswd' with grant option;
#赋予主从同步权限 
grant replication slave on *.* to 'test'@'%'; 
flush privileges;

          • 1
          • 2
          • 3
          • 4
          • 5
          • 6
          • 7

        3. 重启mysql

          systemctl restart mysqld

          • 1

        • 从库设置

          1. 修改配置文件

            vim /etc/my.cnf
#在[mysqld]下添加如下两行配置
[mysqld]
server-id=2
read-only=on
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

            • 1
            • 2
            • 3
            • 4
            • 5
            • 6
            • 7

    • 执行mysql主从命令

      • 主库执行

        show master status;

        • 1

      • 从库执行

        stop slave;
change master to master_host='192.168.0.24', master_port=3306, master_user='test', master_password='test', master_log_file='master-bin.000002',master_log_pos=154;
start slave;
show slave status\G;

        • 1
        • 2
        • 3
        • 4

    • mysql主从配置验证

      • 在主mysql创建数据库

        CREATE DATABASE test_db;

        • 1

      • 从数据库执行也能看到test_db 数据库即成功

    18、分库分表

    背景：

    采用单数据库存储存在以下的性能瓶颈：

    1. IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。
    2. CPU瓶颈：排序，分组，连接查询，聚合统计等SQL会消耗大量的CPU资源，请求数太多，CPU出现瓶颈。

    分库分表将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题。

    拆分策略：

    1. 水平拆分：水平分表，水平分库；

    2. 垂直拆分：垂直分表，垂直分库。

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

      1. 特点：
        1. 每个库的表结构都不一样；
        2. 每个库的数据也不一样；
        3. 所有库的并集是全量数据。

    分库分表的实现技术：

    shardingJDBC:基于AOP原理，在应用程序对本地执行的SQL进行拦截，解析，改写，路由处理。需要自行编码配置实现，支持java语言，性能较高。

    MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及shardingJDBC。

    MyCat：

    MyCat是一个数据库中间件，使用MyCat也很简单，把我们之前连接数据库换成连接MyCat即可。

    具体用法详见

    MyCat 1.6.7（一）MySQL高可用及分库分表

    MyCat 1.6.7（二）高可用及权限

    19、读写分离

    背景：

    数据库写入效率要低于读取效率，一般系统中数据读取频率高于写入频率，单个数据库实例在写入的时候会影响读取性能，这是做读写分离的原因。

    基础：

    实现方式主要基于mysql的主从复制，通过路由的方式使应用对数据库的写请求只在master上进行，读请求在slave上进行。

    方案：

    1. 基于MySQL proxy代理的方式，即使用中间件

    2. 基于应用内路由的方式

      基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行sql。

      • 原理：

      • 方案：基于spring的aop实现: 用aop来拦截spring项目的dao层方法，根据方法名称就可以判断要执行的sql类型(即是read还是write类型)，进而动态切换主从数据源

    3. 基于mysql-connector-java的jdbc驱动方式

      • 原理：

        使用mysql驱动Connector-J的可以实现读写分离。即在jdbc的url中配置为如下的形示：

        jdbc:mysql:replication://master,slave1,slave2,slave3/test

    4. 基于sharding-jdbc的方式

      • 原理：

  • 相关阅读:
    debian安装portainer
    java毕业设计“传情旧物”网站（附源码、数据库）
    CI/CD工具中的CI和CD的含义
    【资源分享】2022年第五届土木，建筑与环境工程国际会议（ICCAEE 2022）
    红蓝对抗--sliver 搭建
    【JAVA-Day43】Java常用类Calendar解析
    CalBioreagents 绵羊抗α-2-HS糖蛋白 亲和纯化说明
    【校招VIP】前端布局模块之Flex弹性布局
    上周热点回顾（4.1-4.7）
    Anchor-free目标检测综述 -- Dense Prediction篇
  • 原文地址：https://blog.csdn.net/m0_46272485/article/details/126041224