一、进阶篇
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配置
创建目录并解压:
创建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、间隙锁