MySql详解_入门到精通02进阶（全网最详细、黑马课程笔记）

泰勒今天想展开

已于 2024-09-07 22:11:20 修改

阅读量435

点赞数 4

分类专栏： Java 文章标签： mysql 运维笔记

于 2024-08-28 23:40:07 首次发布

本文链接：https://blog.csdn.net/qq_46065185/article/details/141615078

版权

Java 专栏收录该内容

22 篇文章 1 订阅

订阅专栏

一、进阶篇

1.1、存储引擎

1、Mysql体系结构

在这里插入图片描述

1、解决频繁连接问题：数据库连接池出现

在这里插入图片描述
由于每次查询数据都要连接数据库，用完再断开，非常消耗资源，因此出现了数据库连接池。连接池使得数据库与客户端之间保持连接一段时间，可以反复利用。

主要的参数：最大连接数，单次最大报文长度。在这里插入图片描述

2、优化Sql语句：接收-解析与优化

在这里插入图片描述

3、如何执行优化后的Sql：通过存储引擎

作用：根据优化后的SQL语句对数据库中的数据进行操作，读取数据或者写入数据
在这里插入图片描述
可以根据客户端不同的要求，选择不同的存储引擎进行处理：

2、存储引擎简介/特点

1、简介

在这里插入图片描述
创建表的时候我们没有指定存储引擎会使用默认的存储引擎

1、默认存储引擎
2、Id是自增的下一条Id为3
3、默认字符集
4、默认排序方式

#显示建表信息
show create table engineTest;

2、设置存储引擎

在这里插入图片描述

3、InnoDB引擎介绍

在这里插入图片描述
默认表文件参数是打开的，表示每一张表对应一个表空间文件，否则所有表对应一个表空间文件
InnoDB逻辑存储结构：

4、MyIsAM引擎介绍

在这里插入图片描述

5、Memory引擎介绍

在这里插入图片描述

6、InnoDB与MyIsAm的三大区别：（面试题）

在这里插入图片描述

3、存储引擎选择

在这里插入图片描述
目前需要使用MyIsAm引擎的情况下都是使用NoSql中的：MongDB，需要使用Memory引擎的情况下都是直接使用Redis。

注意：以下章节均在中真实企业环境Linux系统

Linux环境下Mysql配置

Linux版本Mysql下载
在这里插入图片描述

在这里插入图片描述

创建目录并解压：

创建MySQL文件夹
mkdir mysql
解压压缩包到MySQL文件夹：
remove mysql-8.0.37-1.el7.x86_64.rpm-bundle.tar  to mysql

在这里插入图片描述

使用 rpm 安装:严格按照顺序：
先获取root最高权限：
在这里插入图片描述

(1)rpm -ivh mysql-community-common-8.0.37-1.el7.x86_64.rpm
(2)rpm -ivh mysql-community-client-plugins-8.0.37-1.el7.x86_64.rpm
(3)rpm -ivh mysql-community-libs-8.0.37-1.el7.x86_64.rpm
(4)rpm -ivh mysql-community-libs-compat-8.0.37-1.el7.x86_64.rpm

#此处报错openssl 缺失

[root@master mysql]# rpm -ivh mysql-community-devel-8.0.37-1.el7.x86_64.rpm 
错误：依赖检测失败：
        pkgconfig(openssl) 被 mysql-community-devel-8.0.37-1.el7.x86_64 需要

#安装openssl
yum install openssl-devel -y
#重装包
(4)rpm -ivh mysql-community-libs-compat-8.0.37-1.el7.x86_64.rpm 
(5)rpm -ivh mysql-community-client-8.0.37-1.el7.x86_64.rpm
(6)rpm -ivh mysql-community-server-8.0.37-1.el7.x86_64.rpm

此处报错：

错误：依赖检测失败：
        mysql-community-icu-data-files = 8.0.37-1.el7 被 mysql-community-server-8.0.37-1.el7.x86_64 需

在这里插入图片描述

rpm -ivh mysql-community-icu-data-files-8.0.37-1.el7.x86_64.rpm
#再重装最后一个包
rpm -ivh mysql-community-server-8.0.37-1.el7.x86_64.rpm

在这里插入图片描述
安装完成后查看Mysql版本是否是8.0.37

启动mysql：再centos中安装好Mysql后会自动注册一个系统名：mysqld

#启动
systemctl start mysqld
#重启
systemctl restart mysqld
#停止
systemctl stop mysqld

第一次运行 MySQL 服务时，会进行初始化加载，同时会生成一个 root 用户的初始密码，可以通过查看日志文件 /var/log/mysqld.log 获取到 root 用户的初始密码，后续可手动修改密码在这里插入图片描述

#修改密码，但是不符合规则，需要更改规则让他可以使用简单密码
alter user 'root'@'localhost' identified by '123456';

在这里插入图片描述
进入Mysql官网：官网连接，搜索：validate_password

#设置长度位6
set global validate_password.length=6;
#设置校验等级是Low
set global validate_password.policy=0;

后面修改为123456
在这里插入图片描述

1.2、索引

在这里插入图片描述

1、索引概述

在这里插入图片描述

2、索引结构

在这里插入图片描述

1、二叉树与B树

在这里插入图片描述
二叉树第二个问题：由于每个节点最多只能有两个子节点，当数据量比较大时会造成树的层级比较深。
解决方案：引入B树

B树的原理是先在同一个节点中进行排序，加入设置的最大度数是5，则每个节点中只能处插入4个数，这四个元素进行一个排序，当插入第五个元素的时候，此节点不满足最多存4个元素，则这五个元素的中间元素第三个向上分裂

数据结构演示官网
在这里插入图片描述

在这里插入图片描述

2、B+树

在这里插入图片描述

3、Mysql中对B+数进行优化

在这里插入图片描述

4、Hash

在这里插入图片描述

3、索引分类

在这里插入图片描述

在这里插入图片描述

聚集索引叶子节点挂的是那一行的数据
二级索引挂的是id数据，如果也挂的是一整行的数据，会很冗余。

1、聚集索引与非聚集索引区别？（面试题）

在这里插入图片描述

4、索引语法

在这里插入图片描述

# name字段为姓名字段，该字段的值可能会重复
CREATE INDEX idx_user_name ON tb_user(name);

# phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

# 为profession、age、status创建联合索引
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

5、SQL性能分析

1、Sql的执行频率

首先需要确定一个数据库的增删改查的频率，确定该数据库趋向于增删改还是查询，SQL性能优化：主要优化的目标的查询语句。
查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
-- 'Com_______'：模糊匹配，七个_每个_占一个字符
SHOW [GLOBAL/session] STATUS LIKE 'Com_______';

GLOBAL：如果指定为GLOBAL，则显示的状态信息是针对整个MySQL服务器的，即所有连接共享的状态。
SESSION：如果指定为SESSION，则显示的状态信息是针对当前连接的，即只有当前连接的状态。
LIKE ‘Com_______’：这是一个过滤条件，用于筛选出状态名称以’Com’开头的状态信息。Com通常代表与通信相关的状态，例如客户端发送的命令、服务器响应的状态等。
在这里插入图片描述

2、慢查询日志来定位SQL语句

确定了数据库的主要操作时查询以后，针对查询语句进行优化，应该针对哪些SQL查询语句进行优化呢？需要先定位这些SQL查询语句。
在这里插入图片描述

注意：此时应该是root状态拥有最高权限，否则无法修改此文件

vi /etc/my.cnf 
#01然后数据 i进入编辑状态输入以下内容：

#开启慢日志
slow_query_log=1
#设置阈值为1秒
long_query_time=1


#02：按下ESC 进入命令状态
#输入：再输入：x，保存退出

在这里插入图片描述配置完毕之后，通过以下指令重新启动MySQL服务器（Linux语句）

systemctl restart mysqld

在这里插入图片描述

#进入Mysql文件夹
cd /etc/lib/mysql/
#查询是否开启，慢查询日志
show variables like 'slow_query_log';

在这里插入图片描述

Use UserTest01;
create table user
(
    id     int primary key auto_increment,
    name   varchar(10) not null unique,
    age    int,
    gender varchar(2)
) comment '用户表';

insert into user(name, age, gender)
values ('Jack',20,'男'),('Tom',24,'男'),('Mary',22,'女');

监测master-slow.log文件尾部内容变化

 tail -f master-slow.log

在这里插入图片描述
当执行的sql运行时间超过设定的值1s就会在慢查询日志文件中记录

3、profile详情

对于慢查询日志，我们设置好了时间1秒，凡是查询时间超过1秒就会在慢查询日志中记录，对于一些数据庞大的查询，时间超过预设值很正常，如果对于简单的语句，花费了1秒或者0.9秒则时不正常的，当超过1秒的时候有记录，当没超过1秒但是接近一秒的时候没有记录，但是此条查询语句是有问题的。
在这里插入图片描述

查看profiling是否开启 select @@profiling；在这里插入图片描述

#先执行两条Sql语句
select *
from user
where age=22;


select count(*)
from user;

查看耗时情况

# 查看每一套SQL的耗时基本情况
show profiles;

在这里插入图片描述

# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看之情query_id的SQL语句CPU的使用情况
show profile cpu for query query_id

在这里插入图片描述
仅仅凭借每条Sql语句运行时间长短并不能判别一个Sql语句的好坏，因此接下来还需要通过Explain执行计划来判断。

4、explain

在这里插入图片描述
通过添加explain来查询对于SQL的语句的执行计划：
explain执行计划各字段含义：

1、 ID字段

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

/*explain执行计划表:id值相同*/
explain
select s.*, c.name
from student s,
     courses c,
     student_courses sc
where s.id = sc.student_id
  and c.id = sc.course_id;

在这里插入图片描述

/*explain执行计划表:id值不同*/
# 查询选修了Java的学生信息,(通过子查询的形式)
/*分步进行查询：*/
select student_id
from student_courses sc
where sc.course_id =1;

select id
from courses
where name = 'Java';/*java对应的id是1*/
/*综合子查询形式*/
select *
from student s
where s.id in (select student_id
               from student_courses sc
               where sc.course_id = (select id
                              from courses
                              where name = 'Java'));

在这里插入图片描述

id值最大的先执行，剩下的如果id值相同则从上至下执行

2、select_type字段

select_type:表示select的类型，常见的取值simple（简单表，即不使用表连接或者子查询）、primar（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquer（select/where 之后包含了子查询）等（了解，意义不大）

3、type字段

type：表示连接类型，性能由好到差的连接类型为null、system、const、eq_ref、ref、range、index、all
在这里插入图片描述
（1）当使用主键或者唯一性（被unique关键字修饰的字段，不会出现重复情况）索引时能达到const级别
（2）很难达到null级别，除非不查询表，select ‘A’;
（3）使用的不是唯一索引，如name的值索引，一般是ref
（4）all表示全表扫描，性能低

4、 possible_key

possible_key:显示可能应用在这张表上的索引，一个或多个，实际上不一定用，只是可能用到的索引在这里插入图片描述

5、其他字段

在这里插入图片描述

6、索引使用

1、语法

CREATE INDEX index_name ON table_name (column1, column2, ...);

index_name 是索引的名称。
table_name 是要在其上创建索引的表的名称。
column1, column2, … 是要包含在索引中的列名。可以指定一个或多个列（符合索引）。

2、最左前缀法（联合索引遵循的法则）

最左前缀法则是MySQL数据库在使用复合索引进行查询时必须遵循的一种原则，它要求查询条件中必须包含复合索引的最左列，这样MySQL才能使用该复合索引进行查询。

在MySQL数据库中，为了提升查询效率，常常会对多个列创建复合索引。然而，使用这些复合索引时，必须遵守最左前缀法则，否则索引的效果将无法发挥。具体来说，当创建一个包含多个列的复合索引时，如(column1, column2, column3)，查询条件中必须包含索引的最左列，也就是column1，这样才能利用索引进行查询。如果查询条件中只包含column2或column3，那么MySQL无法使用该复合索引，查询性能会受到影响。

为什么需要最左前缀法则？这是因为MySQL的B-Tree索引结构按照从左到右的顺序存储数据。当创建了(column1, column2, column3)这样的联合索引后，MySQL会先根据column1排序，然后在相同的column1值下根据column2排序，以此类推。这种结构决定了必须从最左边开始使用索引，才能有效利用其排序特性。

在实际应用中，理解并正确应用最左前缀法则对优化数据库查询至关重要。例如，假设有一个用户表，包含用户的名、姓和年龄，并对这些字段建立了联合索引(first_name, last_name, age)。有效的查询会是那些包含first_name的查询，无论是否同时包含其他列；如SELECT * FROM users WHERE first_name = ‘John’ AND last_name = 'Doe’或者仅使用first_name进行查询都会利用到索引。但如果查询仅仅包含last_name或age，则无法使用该联合索引。

补充：
在这里插入图片描述

3、索引失效

1.添加运行
在这里插入图片描述
2. 字符串不加单引号

3. 头部模糊查询

4.or连接条件

4、Sql提示

对于专业列profession，给他创建了单列索引indx_profexxion,创建联合索引时也用到了这个列，indx_profession_name_age,那么在执行查询peofession的Sql语句，自动选择了联合索引，如果我们像要指定使用哪个索引就可以在SQL中添加提示。
在这里插入图片描述

5、覆盖所有

在这里插入图片描述

覆盖索引：覆盖索引是一种数据库优化技术，其核心在于通过仅访问索引而无需访问实际的数据记录，就能处理查询请求。

回表查询：性能低，使用select *的时候很容易出现回表查询

6、前缀索引（截取大文本字段长度）

distinct 字段名：求该字段中不重复的数据个数
在这里插入图片描述

7、单列索引、联合索引

在这里插入图片描述
单列索引很容易造成回表查询导致性能变低。

7、索引设计原则

主键索引：当数据表中设置了主键，会自动建立主键索引
唯一索引：一般针对不会存在重复值的字段建立索引：手机号
InnoDB存储引擎中根据存储结构又分为：聚集索引（必须存在，且每个叶子节点存储的是对应一行数据）、二级索引（叶子节点存的是主键，以方便回调查询）
在这里插入图片描述

1.3、SQL优化

1、插入语句优化

在这里插入图片描述

2、主键优化

每个黄色区域是一个页
在这里插入图片描述

在这里插入图片描述
主键顺序插入：
1、主键乱序插入可能导致页分裂

第一页后面的数据应该是23，在第三页中，所以还需要重新调整链表：

将第三页与第一页连接：

2、删除操作可能导致页合并

3、Order By优化

在这里插入图片描述

4、Group By优化

在这里插入图片描述

5、Limit分页优化

在这里插入图片描述

6、Count

在这里插入图片描述
count（主键）/(*)求总数
Count（字段）：统计的是不为空的字段数

7、Updata

在这里插入图片描述

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

1.5、锁

在这里插入图片描述

1、全局锁

在这里插入图片描述

1、加全局锁/解锁

在这里插入图片描述

2、数据库备份

使用mysqldump进行全量备份

备份单个数据库：mysqldump -u[用户名] -p[密码] [数据库名] > [备份文件路径]
备份多个数据库：mysqldump -u[用户名] -p[密码] --databases [数据库1] [数据库2] > [备份文件路径]
备份所有数据库：mysqldump -u[用户名] -p[密码] --all-databases > [备份文件路径]

使用mysqldump进行增量备份

备份自上次备份以来有更改的表：mysqldump -u[用户名] -p[密码] [数据库名] --ignore-table=数据库.表 --since-last-backup > [备份文件路径]
备份指定时间范围内的数据更改：mysqldump -u[用户名] -p[密码] [数据库名] --ignore-table=数据库.表 --start-datetime="YYYY-MM-DD HH:MM:SS" --stop-datetime="YYYY-MM-DD HH:MM:SS" > [备份文件路径]

使用mysqldump备份特定表的数据

备份单个表：mysqldump -u[用户名] -p[密码] [数据库名] [表名] > [备份文件路径]
备份多个表：mysqldump -u[用户名] -p[密码] [数据库名] [表1] [表2] > [备份文件路径]

使用mysql命令进行备份

备份表结构：mysql -u[用户名] -p[密码] [数据库名] < [备份文件路径]
恢复表结构：mysql -u[用户名] -p[密码] [数据库名] < [备份文件路径]

使用mysqlhotcopy工具进行热备份

对数据库进行热备份：mysqlhotcopy [数据库名] [备份目录]
自动热备份：mysqlhotcopy --auto_increment_dir=[备份目录] [数据库名]

注意：备份操作的语句直接在命令行即可，不是在Mysql的命令行输入

2、表级锁

在这里插入图片描述

2.1表锁

2.1.1、读锁：lock table xxx read;

在这里插入图片描述

阻塞接触的条件是加锁的一方释放表锁：unlock tables；

2.1.2、写锁：lock table xxx write;

在这里插入图片描述

2.2元数据锁

在这里插入图片描述

2.3、意向锁

在这里插入图片描述
加表锁时只需要关注加的意向锁是什么类型是否与表锁兼容即可，不用一行行判断是否加锁

插入更新删除等语句会自动加意向排他锁，查询语句需要在最后加上：lock in share mode

3、行级锁

3.1、介绍

在这里插入图片描述

3.2、行锁

在这里插入图片描述

3.3、间隙锁

在这里插入图片描述

4、总结

在这里插入图片描述

1.6、InnoDB引擎

1.7、Mysql管理

泰勒今天想展开

关注

4
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySql详解_入门到精通02进阶（全网最详细、黑马课程笔记）

创建表的时候我们没有指定存储引擎会使用默认的存储引擎1、默认存储引擎2、Id是自增的下一条Id为33、默认字符集4、默认排序方式#显示建表信息默认表文件参数是打开的，表示每一张表对应一个表空间文件，否则所有表对应一个表空间文件目前需要使用MyIsAm引擎的情况下都是使用NoSql中的：MongDB，需要使用Memory引擎的情况下都是直接使用Redis。
复制链接

扫一扫