微信公众号后天网站开发,自己开网站工作室,百度指数关键词搜索趋势,炫酷网站界面设计Mysql8的优化 1、Mysql的安装优化1.1 修改配置参数#xff08;命令行、配件文件#xff09;1.1.1 命令行修改配置参数1.1.2 参数持久化1.1.3 Mysql多实例启动#xff0c;以及配置密码文件 1.2 查询表的相关参数#xff0c;以及表空间管理 2、Mysql高级优化#xff08;SQL命令行、配件文件1.1.1 命令行修改配置参数1.1.2 参数持久化1.1.3 Mysql多实例启动以及配置密码文件 1.2 查询表的相关参数以及表空间管理 2、Mysql高级优化SQL2.1 SQL慢查询监控2.2 performance_schema(引擎)2.2.1基本监控 2.3mysql 用户管理和授权2.3.1 认证2.3.2 授权2.3.3 角色激活2.3.3 授权表2.3.4 创建用户授权 2.4 mysql 安全2.4.1 启用SSL连接2.4.2 操作数据库防火墙 2.5 mysql 优化和监控2.5.1 数据库锁2.5.2 数据库索引优化2.5.3 数据库监控待补充 2.5 mysql 备份和恢复2.5.1 mysqldump 和 mysqlpumpbinlog 恢复待补充 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默认151mysqlx_max_connections最大默认100 和内核参数有关show VARIABLES like %max_connections%; show VARIABLES like %port%; 1.1.1 命令行修改配置参数
# 命令行、或者配置文件部分参数不能在内存中修改# -----------------------修改配置文件--------------------------------------# -----------------------动态修改参数部分参数set重启就会恢复原样--------------------------------------set GLOBAL max_connections 100;
show VARIABLES like %connections%;show VARIABLES like %autocommit%;set global autocommoff;# global 必须新的会话才可以生效1.1.2 参数持久化
# -------------------------如何设置参数持久化mysql8的特性------------------# 持久化到数据库的目录下mysqld-auto.confset 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.1.3 Mysql多实例启动以及配置密码文件
# -----------------------配置客户端启动命令可以在mysqld文件中# 密码放到一个加密文件 mysql_config_editor set --login-path 用户名 --userroot --PASSWORD#客户端可以配置提示符# mysql 实例初始化 mysqld --no-defaults --initialize-insecure --usermysql --data /mysql/data1mysqld --no-defaults --initialize-insecure --usermysql --data /mysql/data1
mysqld --no-defaults --initialize-insecure --usermysql --data /mysql/data2
mysqld --no-defaults --initialize-insecure --usermysql --data /mysql/data3# mysql 启动实例连接远程tcp 本地 udp1.2 查询表的相关参数以及表空间管理
# 查询表的相关参数
show variables like %table%;# 创建表空间
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 */ ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
show create table t_demo;
# 外部文件通用表空间show VARIABLES like %innodb_direct%;# 删除表空间drop TABLESPACE general;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_time0.5;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和开发者可以更好地理解数据库的工作方式发现潜在问题并采取相应措施提高系统的整体性能和效率。
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_schematest_db and object_name t_demo;
2.3mysql 用户管理和授权
2.3.1 认证 1. usernamelocalhost 等价于 ‘username’‘localhost’ 可以有单引号也可以没有
2. 主机名hostmane3. 创建账户 5.7 和8之后不一样 不要创建没有密码的账户也不要创建用户和授权 角色权限 用户的权限可以通过角色授权 示例 当前用户客户端的名字 rootlocalhost 5. 维护修改账户、角色- 修改账户 配置账户密码 mysql8新特性 一个账户两套密码 手动添加第二套密码之前的密码作为老密码执行 discard 就会抛弃老密码。 密码过期手动过期 注意过期密码可以连接但是不能操作 **默认过期**default_password_lifetime 可以作为默认过期的另外 never 是永不过期expire interval 30 day30天后过期 密码过期修改密码 示例 登入的用户usernamemysql所在的客户端机器选 C dbserver2 这个是db服务器 用户名主机 mysql 认证插件 **注意**mysql 5.7 使用的 前两种 mysql 8 之后添加了第三种将密码放到内存中舍弃了第一种。 几种连接认证方式客户端连接数据库
PAM认证 不用登录、或者文本插件、或者企业级PAM 2.3.2 授权
1. 为什么授权
2. 授权类型
3. 授权管理权限
4. 动态权限
5. 制定或者自定义权限 usage默认就是连接权限创建好就有 6. 语句类的权限
7. 授权角色 with admin option: 授权管理权限 8. 查询权限 9. 权限收回revoke 10. mysql8 新特性收回部分权限但是必须打开部分收回的权限partial_revokeson
使用权限的限制 . 所有库下面的所有表 示例 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 janlocalhost 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 janlocalhost;# 查看用户
select * from mysql.user;# 查看角色
select * from mysql.role_edges;# 授权把表的查询权限授给用户
grant select on test_db.t_demo to janlocalhost;
# 查看当前用户角色
select current_role();
# 查看授权
show grants;# 把角色授权给所有用户
set role all;# 查看当前用户
select current_user();select current_role();show grants;2.4 mysql 安全
2.4.1 启用SSL连接
# SSL 默认是开启的可以关闭 skip_ssl ;本地tcp和ipshow VARIABLES like %have_ssl%;# --ssl-modle disable 加这个参数可以不使用sslshow global VARIABLES like %tls%;show session VARIABLES like %ssl%;
# 证书连接 --ssl-cert ? --ssl-key? ssl_type X509
show create table test_db.t_demo;2.4.2 操作数据库防火墙
show create table mall.cms_help;SHOW global status like %firewall%;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 到pid2.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;2.5.3 数据库监控待补充
2.5 mysql 备份和恢复
2.5.1 mysqldump 和 mysqlpump
# secure_file_priv
show variables like %secure%;# mysqldump
# mysqlpump
# binlog 日志恢复数据库
binlog 恢复待补充
