Mysql8的优化(DBA)

1、Mysql的安装优化

1.1 修改配置参数（命令行、配件文件）

#   -----------------------修改配置参数-------------------------------------

# 配置 innodb 

show VARIABLES like  '%innodb_buffer%';

# 内存 修改
set global innodb_buffer_pool_size = 144217728;



show  DATABASES;

#  innodb_buffer_pool_instances =  2 ，innodb_buffer_pool_size必须大于1G；那样可以分为两个子池


# 连接池,max_connections默认151，mysqlx_max_connections最大默认100； 和内核参数有关

show  VARIABLES like '%max_connections%';  


show  VARIABLES like '%port%';  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1.1.1 命令行修改配置参数

# 命令行、或者配置文件；部分参数不能在内存中修改；

#   -----------------------修改配置文件--------------------------------------

# -----------------------动态修改参数（部分参数：set）：重启就会恢复原样--------------------------------------

set GLOBAL max_connections = 100;
show VARIABLES like '%connections%';


show VARIABLES like '%autocommit%';

set global autocomm=off;
 
# global 必须新的会话才可以生效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1.1.2 参数持久化

# -------------------------如何设置参数持久化：，mysql8的特性------------------


# 持久化到数据库的目录下：mysqld-auto.conf

set persist max_connections = 101;

show VARIABLES like '%connections%';
show   databases;

# 变量表
use `performance_schema`;


select  * from  performance_schema.variables_info where variable_name = 'max_connections';


set  persist max_connections= default;

show VARIABLES like '%connections%';
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

1.1.3 Mysql多实例启动，以及配置密码文件

# -----------------------配置客户端启动命令：可以在mysqld文件中

# 密码放到一个加密文件：    mysql_config_editor set --login-path =用户名 --user=root --PASSWORD


#客户端可以配置提示符


#  mysql 实例初始化  mysqld  --no-defaults --initialize-insecure --user=mysql  --data = /mysql/data1

mysqld  --no-defaults --initialize-insecure --user=mysql  --data = /mysql/data1
mysqld  --no-defaults --initialize-insecure --user=mysql  --data = /mysql/data2
mysqld  --no-defaults --initialize-insecure --user=mysql  --data = /mysql/data3


#  mysql 启动实例；连接远程tcp ，本地 udp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

1.2 查询表的相关参数，以及表空间管理

# 查询表的相关参数
show  variables like '%table%';
1
2

# 创建表空间
create TABLESPACE general add datafile 'general.ibd';

create  table  t_demo(b int) TABLespace = general;


insert into t_demo  values(1);
insert into t_demo  values(2);
insert into t_demo  values(3);

select  * from t_demo;
#  查询表对应的表空间 ：CREATE TABLE `t_demo` ( `b` int DEFAULT NULL) /*!50100 TABLESPACE `general` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
show  create table  t_demo;

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

# 外部文件通用表空间

show   VARIABLES like '%innodb_direct%';
1
2
3

#  删除表空间

drop  TABLESPACE  general;
1
2
3

2、Mysql高级优化（SQL）

2.1 SQL慢查询监控

#设置慢查询的开启状态，默认是关闭状态
show   VARIABLES like "%slow%";
# 开启慢查询功能
set  GLOBAL slow_query_log = "ON";
# 查询慢查询的输出：默认是file
show   VARIABLES like "%log_output%";
# 设置慢查询记录到表中
set  GLOBAL log_output = "TABLE"
# 查询慢查询记录
select  * from mysql.slow_log;
# #设置慢查询的监控时间，默认是10秒
show   VARIABLES like "%long%";
#设置成0.5秒，注意这个重新开启会话才可以生效
set  GLOBAL long_query_time=0.5;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.2 performance_schema(引擎)

MySQL的performance_schema是一个系统数据库，专门设计用于收集和展示服务器性能相关的数据。它的主要作用是：

 1. **资源监控：** performance_schema可以实时监控MySQL服务器内部的各种操作，包括但不限于内存、CPU、线程、文件系统I/O、表锁、阶段（stages）和事件（events）等资源的使用情况。
 2.  **性能分析：** 通过该库提供的视图，用户可以获取到详细的执行计划、查询时间、等待事件统计等信息，帮助开发人员和DBA分析应用程序或SQL语句的性能瓶颈。
 3.  **深度跟踪：** 它能够提供低级别的SQL执行细节，例如记录每个查询在MySQL内部各个组件中的执行时间和消耗资源情况，有助于进行更深入的诊断和优化工作。
 4.  **安全性监控：** 可以用来监视和控制诸如连接数、线程状态、账户活动等与安全性和稳定性相关的信息。
 5. **自定义监控：** 用户可以根据需要启用或禁用特定的监控点（instrument），并配置采样频率和其他参数，以便对特定的关注点进行定制化的性能监控。
总之，performance_schema为MySQL提供了丰富的内部运行时信息，是进行数据库性能调优的重要工具之一。通过它，DBA和开发者可以更好地理解数据库的工作方式，发现潜在问题，并采取相应措施提高系统的整体性能和效率。

1
2
3
4
5
6
7
8
9

2.2.1基本监控

#  ----------------------------------------------------`performance_schema`-------------------

show VARIABLES like  "performance_schema";

# `performance_schema`就是一个引擎

show  ENGINE `performance_schema` status;

  
use `performance_schema`;

# 查下看配置表

show TABLES like '%setup%';

# 查看自定义配置

select * from setup_consumers;

# 查看链接状态
select * from setup_actors;


# 查看每行的指标
select * from setup_instruments;


select * from setup_instruments  where  name like  "wait/io/table%";


# 查看I O的表
select  * from table_io_waits_summary_by_table;

#  记录每一张表的IO
select  * from table_io_waits_summary_by_table where object_schema="test_db" and  object_name ="t_demo";

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

2.3mysql 用户管理和授权

2.3.1 认证

1. username@localhost  等价于  ‘username’@‘localhost’  可以有单引号也可以没有
2. 主机名hostmane
1
2

3. 创建账户 5.7 和8之后不一样

不要创建没有密码的账户，也不要创建用户和授权

4. 角色权限
   

用户的权限可以通过角色授权

• 示例： 当前用户@客户端的名字 root@localhost 5. 维护修改账户、角色- 修改账户

5. 配置账户密码
   

6. mysql8新特性： 一个账户两套密码：
   

手动添加第二套密码，之前的密码作为老密码；执行 discard 就会抛弃老密码。

7. 密码过期：手动过期
   

注意：过期密码可以连接，但是不能操作

• **默认过期：**default_password_lifetime 可以作为默认过期的，另外 never 是永不过期；expire interval 30 day：30天后过期；
  

• 密码过期修改密码
  

• 示例：
  

登入的用户username@（mysql所在的客户端机器）选 C
dbserver2： 这个是db服务器 用户名@主机

9. mysql 认证插件
   

**注意：**mysql 5.7 使用的 前两种； mysql 8 之后添加了第三种，将密码放到内存中，舍弃了第一种。

10. 几种连接认证方式：客户端连接数据库
    
    

• PAM认证
  
  
  
  

不用登录、或者文本插件、或者企业级PAM

2.3.2 授权

1. 为什么授权：

2. 授权类型

3. 授权管理权限

4. 动态权限

5. 制定或者自定义权限：

usage：默认就是连接权限，创建好就有

6. 语句类的权限

7. 授权角色：

with admin option: 授权管理权限
8. 查询权限

9. 权限收回：revoke

10. mysql8 新特性：收回部分权限，但是必须打开部分收回的权限，partial_revokes=on

• 使用权限的限制
  

. 所有库下面的所有表

11. 示例：
    

a :锁定 不能登录 ；b：过期可以登录； c：删除不可以登录；usage：可以登录

2.3.3 角色激活：

1. 几种类型:服务器、用户、会话层面

2. 设置激活角色：

3. 设置默认角色：

4. 会话级别：

5. 强制持久化

2.3.3 授权表：

1. 权限类型：

2. 用户表授权


3. 修改权限

注意：ddl 不需要刷新的： dml；在内存中需要刷新flush；不是所有的都需要刷新。
DDL和DML的区别：
DDL (Data Definition Language) 和 DML (Data Manipulation Language) 是 SQL 中两种不同类型的语句，它们在数据库操作中有明确的区别：
DDL (数据定义语言)：
作用：用于创建、修改和删除数据库中的结构或模式对象。
功能：包括创建表（CREATE TABLE）、修改表结构（ALTER TABLE）、删除表（DROP TABLE）、创建索引（CREATE INDEX）、删除索引（DROP INDEX）以及创建视图（CREATE VIEW）、删除视图（DROP VIEW）等与数据库架构相关的操作。
特点：DDL 语句通常是自动提交的，并且一旦执行，对数据库结构的改变立即生效，无法通过事务回滚来撤销。
DML (数据操作语言)：
作用：用于插入、更新、删除数据库表中的实际数据行，以及查询这些数据。
功能：主要包括插入数据（INSERT INTO）、更新数据（UPDATE）、删除数据（DELETE FROM）以及从表中选择数据（SELECT）的操作。
特点：DML 语句通常是在事务内执行的，可以控制事务的开始、提交和回滚。这意味着执行一系列 DML 操作后可以选择是否将更改永久保存到数据库中。
总结来说，DDL 主要关注数据库的物理结构设计，而 DML 则是针对已存在结构中的数据进行具体的操作和管理。

2.3.4 创建用户授权：

#  创建用户
create  user  jan@localhost IDENTIFIED by 'jan123';

#创建两个权限
create role r_mgr,r_emp;

# 把表的查询权限授权给角色
grant select  on test_db.t_demo to  r_emp;

# 把表的ddm权限付给角色
grant  insert,update,delete on test_db.* to r_emp;

# 把表的ddm权限付给角色
grant  insert,update,delete on test_db.* to r_mgr;


# 授权角色给用户
grant r_emp,r_mgr to jan@localhost;

# 查看用户
select  * from mysql.`user`;

# 查看角色
select  * from  mysql.role_edges;

# 授权：把表的查询权限授给用户
grant select  on test_db.t_demo to jan@localhost;
#  查看当前用户角色
select  current_role();
#  查看授权
show grants;

# 把角色授权给所有用户 
set role all;

# 查看当前用户
select  current_user();

select  current_role();

show grants;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

2.4 mysql 安全

2.4.1 启用SSL连接

# SSL 默认是开启的，可以关闭 skip_ssl ;本地tcp和ip

show  VARIABLES like  "%have_ssl%";

# --ssl-modle = disable 加这个参数可以不使用ssl


show  global VARIABLES like  "%tls%";


show  session VARIABLES like  "%ssl%";
#  证书连接  --ssl-cert =?  --ssl-key=? ssl_type = X509
show  create table test_db.t_demo;
1
2
3
4
5
6
7
8
9
10
11
12
13

2.4.2 操作数据库防火墙

show  create  table  mall.cms_help;



SHOW   global status like "%firewall%";
1
2
3
4
5

2.5 mysql 优化和监控

2.5.1 数据库锁

#  排他锁；

prompt 1;

# 设置锁
SET  innodb_lock_wait_timeout = 3600;

begin;

# 启动事务
start transaction;
# 自动提交
show  variables like "%autocommit%";


select * from mall.cms_help for update;


show processlist;

use  sys;

select  * from sys.innodb_lock_waits;


show  create table sys.innodb_lock_waits;

# 阻塞，KILL 到pid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

2.5.2 数据库索引优化

select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100;

explain select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100;

# 创建索引

create index idx_price on pms_product_full_reduction(full_price);

explain select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100;



select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100 and reduce_price >100;

explain select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100 and reduce_price >100;

# 创建复合索引  ,单个字段不要在多个索引中出现，容易出现死锁问题

create index idx_price_2 on pms_product_full_reduction(full_price,reduce_price);

explain select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100 and reduce_price >100;

# 查询索引
show  index FROM mall.pms_product_full_reduction

# 删除索引
drop index idx_price_3 on mall.pms_product_full_reduction;



show  tables;

# 设计索引一定要考虑组合索引；需要尝试和调试；调试完成后删除多余的索引
create index idx_price_3 on pms_product_full_reduction(full_price,reduce_price);


#  mysql  查询分析器

END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

2.5.3 数据库监控（待补充）

2.5 mysql 备份和恢复

2.5.1 mysqldump 和 mysqlpump

#   secure_file_priv
show  variables  like "%secure%";

# mysqldump
# mysqlpump
# binlog  日志恢复数据库

1
2
3
4
5
6
7

binlog 恢复（待补充）