6、其他优化策略

最新推荐文章于 2023-12-08 11:43:31 发布
最新推荐文章于 2023-12-08 11:43:31 发布
阅读量2.5k 收藏
点赞数 1
分类专栏： 后台编程技术 # MySQL 文章标签： mysql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HadesLin/article/details/118653880
版权
本文详细介绍了MySQL的相关知识，包括应用优化如使用连接池、避免重复索引和主从复制，SQL缓存优化，内存优化策略，以及并发控制和锁机制。重点讲解了主从复制的原理和配置，以及SQL查询缓存的启用和失效情况。同时，讨论了InnoDB和MyISAM的内存优化，并分析了行锁和间隙锁的概念及其影响。通过对相关参数的调整，可以显著提升数据库性能和并发处理能力。
摘要由CSDN通过智能技术生成

该栏目讲叙 MySQL 相关的知识体系，包括数据库简介、SQL 简介、数据定义、数据操作、数据查询及数据优化等模块

文章目录

应用优化

1、使用连接池

  • ：建立连接的代价是比较耗费资源的，所以有必要建立数据库连接池（c3p0、druid），以提高访问的性能

2、避免重复索引

  • ：减少对数据库无用的重复请求，能一次连接获取全部结果的，就不用分次获取

3、应用层增加缓存

  • ：在应用中增加缓存层来达到减轻数据库负担。可以使用redis，也可以使用框架（MyBatis）提供的缓存机制

4、分布式数据库架构

  • ：分布式数据库架构适合大数据量、负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率

5、主从复制

  • 概述：通过 MySQL 的主从复制，实现读写分离，使得增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力

  • 原理：复制是指将主数据库的二进制日志传到从库服务器中，然后在从库上对这些日志重新执行，从而使得从库和主库的数据保持同步。MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制
    主从复制原理

  • 好处

    • 当主库出现问题，可以快速切换到从库提供服务
    • 在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力
    • 在从库中执行备份，以避免备份期间影响主库的服务

  • 搭建

 主库配置 

# 在master 的配置文件（/usr/my.cnf）中，配置如下内容
# mysql 服务ID,保证整个集群环境中唯一
server-id=1
# mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin
# 错误日志,默认已经开启
# log-err
# mysql的安装目录
# basedir
# mysql的临时目录
# tmpdir
# mysql的数据存放目录
# datadir
# 是否只读,1 代表只读, 0 代表读写
read-only=0
# 忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql
# 指定同步的数据库
# binlog-do-db=db01

# 重启
service mysql restart;

# 创建同步数据的账户，并且进行授权操作
grant replication slave on *.* to 'itcast'@'192.168.192.131' identified by 'itcast';
flush privileges;

# 查看master状态
show master status;
# 字段含义
File : 从哪个日志文件开始推送日志文件
Position ： 从哪个位置开始推送日志
Binlog_Ignore_DB : 指定不需要同步的数据库

 从库配置 

# 在 slave 端配置文件中，配置如下内容
# mysql 服务端ID,唯一
server-id=2
# 指定binlog日志
log-bin=/var/lib/mysql/mysqlb

# 重启
service mysql restart;

# 执行如下指令：
change master to master_host= '192.168.192.130', master_user='itcast',
master_password='itcast', master_log_file='mysqlbin.000001', master_log_pos=413;

# 开启同步操作
start slave;
show slave status;

#  停止同步操作
stop slave;

SQL 缓存优化

1、简介

  • 概述：当执行完全相同的SQL语句的时候，服务器会直接从缓存中读取上一次查询的结果，当数据被修改了，之前缓存的数据会失效，对于修改比较频繁的表不适合开启查询缓存
  • 执行流程
    执行流程

2、操作

# 查看当前的MySQL数据库是否支持查询缓存
SHOW VARIABLES LIKE 'have_query_cache';

# 查看当前MySQL是否开启了查询缓存：0关闭、1打开、2只缓存查询 
SHOW VARIABLES LIKE 'query_cache_type';

# 查看查询缓存的占用大小
SHOW VARIABLES LIKE 'query_cache_size';

# 查看查询缓存的状态变量
SHOW STATUS LIKE 'Qcache%';

# 启动缓存，在/usr/my.cnf配置中，增加以下配置，配置完毕之后，重启服务既可生效
query_cache_type = 1

# 定制select语句
SELECT SQL_CACHE id,name FROM customer; # 缓存查询结果的id,name
SELECT SQL_NO_CACHE id,name FROM customer;  # 不缓存查询结果的id,name

3、缓存失效

  • SQL 语句不一致时
  • 查询语句中 有不确定的值时，如 now()、current_date()
  • 查询语句中 不是查询表中的数据，如 select ‘A’
  • 查询 系统数据库的表
  • 在 存储的函数、触发器或事件的主体内执行的查询
  • 当表被修改时，则使用该表的所有高速缓存数据将被缓存中删除

内存优化

1、优化原则

  • 在操作系统和其他程序有足够的内存的情况下，则尽量将更多的内存分配给 MySQL 做缓存
  • 排序区、连接区等缓存是分配给每个数据库会话专用的，其默认值的设置要根据最大连接数合理分配
  • MyISAM 的数据文件读取依赖于操作系统自身的 IO 缓存，因此，如果有 MyISAM 表，就要预留更多的内存给操作系统做 IO 缓存

2、InnoDB 优化

  • 概述：InnoDB 使用一块内存区做 IO 缓存池，该缓存池用来缓存索引块、数据块
  • 相关参数
# 设置数据块和索引块的缓存区大小
innodb_buffer_pool_size：[size]

# 设置 InnoDB 日志缓存的大小
innodb_log_buffer_size：[size]

3、MyISAM 优化

  • 概述：MyISAM使用key_buffer缓存索引块，加速索引的读写速度。对于表的数据块，MyISAM没有特别的缓存机制，完全依赖于操作系统的IO缓存
  • 相关参数
# 设置索引块缓存区大小
key_buffer_size：[size]

# 设置读取缓存区的大小
read_buffer_size：[size]

# 设置排序缓存区的大小
read_rnd_buffer_size：[size]

并发优化

  • 概述：MySQL 数据库是多线程程序，可以通过设置并发相关的参数，有效利用资源，提高数据库的并发性能
  • 相关参数
# 设置最大连接数，默认值是151
max_connections：[size]

# 查询连接数
show variables like 'max_connections'

# 设置 MySQL 监听TCP端口时请求栈大小，默认值为50，当连接到达上限，则新来请求将会被缓存在堆栈中
back_log：[size]

# 设置 SQL 语句执行线程可打开表缓存的大小
table_open_cache：[size]

# 设置线程缓存大小，用于加快连接数据库的速度
thread_cache_size：[size]

# 设置InnoDB事务等待行锁时间，默认50ms，对于需要快速反馈的业务系统，可以将行锁的等待时间调小，
# 以避免事务长时间挂起；对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作
innodb_lock_wait_timeout：[time]

锁机制

1、概述

  • ：InnoDB 对于对于 update、insert 和 delete 语句，会自动给涉及数据集加排他锁；但对于 select 语句，InnoDB 不会加任何锁

2、行锁模型

  • 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改
  • 排它锁（X）：又称为写锁，简称 X 锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排它锁，但是获取排他锁的事务是可以对数据就行读取和修改
# 为select添加共享锁
select * from table_name where ... lock in share mode

# 为select添加排它锁
select * from table_name where ... for update

3、行锁升级为表锁

  • ：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样

4、间隙锁的危害

  • ：使用条件的范围去查询数据，当该查询有共享或排它锁时，InnoDB 会给符合条件的已有数据进行加锁，对于键值在条件范围内但并不存在的记录（间隙），InnoDB 也会对这个"间隙"加锁

5、查看行锁争用情况

show  status like 'innodb_row_lock%'

# Innodb_row_lock_current_waits: 当前正在等待锁定的数量
# Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
# Innodb_row_lock_time_avg:每次等待所花平均时长
# Innodb_row_lock_time_max:从系统启动到现在等待长的一次所花的时间
# Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
# 当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，
# 然后根据分析结果着手制定优化计划
HadesLin
关注
专栏目录
近端策略优化(PPO)
AI天才研究院
05-05 1079
1. 背景介绍 强化学习（Reinforcement Learning，RL）是机器学习的一个重要分支，它研究的是智能体如何在与环境的交互中学习并做出最佳决策。近年来，深度强化学习（Deep Reinforcement Learning，DRL）的兴起，将深度学习与强化学习相结合，取得了令人瞩目的成就，例如AlphaGo战胜围棋世界冠军。
前端框架的性能优化策略
最新发布
官方推荐
06-26 2485
前端框架的性能优化策略
Unity优化策略（常用优化手段）
qq_51613678的博客
12-08 3492
浅谈Uniy中的常用优化手段
数据库查询优化策略
plqwf19880902的博客
05-09 1225
数据库查询优化策略 1.合理使用索引 数据库服务器对数据进行访问一般采用下面的两种方式：①索引扫面，通过索引访问数据；②表扫描，读表中的所有页。 当对一个表进行查询时，如果返回的行数占全表总行数的10%到15%时，使用索引可以极大的优化查询性能。但是如果查询涉及到全表40%以上的行时，表扫描的效率比使用索引扫描的效率高。 使用索引的通用规则： 1.在经常用作过滤器或者查询频率较高字段上建立索引； 2.在 SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引； 3.在不同值较少的字段 上不必
MySQL的性能优化参数和优化策略
Sakura_QKS的博客
12-05 1095
MySQL性能优化参数和优化策略
【MySql】mysql 常用查询优化策略详解
congge_study的博客
11-06 8703
mysql 常用查询优化策略
多策略融合算术优化算法
心升明月的博客
12-08 2477
针对算术优化算法(AOA)在搜索过程中容易陷入局部极值点、收敛速度慢以及求解精度低等缺陷，提出一种多策略集成的算术优化算法(MFAOA)。首先，采用Sobol序列初始化AOA种群，增加初始个体的多样性，为算法全局寻优奠定基础；然后，重构数学优化器加速函数(MOA)，权衡全局搜索与局部开发过程的比重；最后，利用混沌精英突变策略，改善算法过于依赖当前最优解的问题，增强算法跳出局部极值的能力。选用12个基准函数和部分CEC2014测试函数进行实验仿真，结果表明MFAOA在求解精度和收敛速度上均有明显的提升。
Sql语句优化策略
一天一小步，梦想一大步
10-30 2320
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：  select id from t where num is null  可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：  select id f...
机器学习（深度学习）优化策略
weixin_39542613的博客
08-16 1146
机器学习（深度学习）优化策略
MySql更新优化策略
12-15
MySQL更新优化策略主要关注如何有效地提高数据库中数据更新的速度和效率。在这个场景中，开发者遇到了一个性能问题，即在更新大量数据时速度极其缓慢。以下是一些关键的优化策略： 1. **索引利用**： - 联合索引的...
Mysql优化策略（推荐）
12-16
6. **其他优化措施**：启用慢查询日志可以帮助识别性能瓶颈。避免使用触发器，尽可能在应用程序层面处理业务逻辑。对于大量数据的插入或删除，可以拆分成小批次操作，以降低对数据库的影响。 通过遵循上述优化策略...
MySQL 建表的优化策略 小结
12-15
6. **特殊优化策略**： - **表格分割**：通过分区或分表，将大表拆分成更小的部分，以分散I/O负载，提高查询效率。 - **使用非事务表类型**：如MyISAM，相比于InnoDB，它们没有事务支持，但在读取密集型应用中可能...
1、Spring框架基础知识
HadesLin的博客
06-29 5863
文章目录Spring框架简介入门案例 Spring框架简介 概述：不仅是一个轻量级、开源的JavaEE框架，还是一个一站式的框架 核心功能 IOC：控制反转 AOP：面向切面编程 特点 简化开发、方便解耦 AOP支持 声明式事务支持 方便程序的设计 方便集成各种优秀框架 体系结构 入门案例 描述：讲解了以配置文件（bean.xml）方式使用Spring框架的IOC技术去创建一个类的对象，也从侧面说明Spring框架便于解耦 过程 Created with Raphaël 2.2.
2、IOC容器
HadesLin的博客
06-30 5558
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录一、pandas是什么？二、使用步骤1.引入库2.读入数据 一、pandas是什么？ 示例：pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。 二、使用步骤 1.引入库 代码如下（示例）： import numpy as np import pandas as pd import matplotlib.pyplot as plt import seaborn as sns import warning
3、SQL 注入器
HadesLin的博客
07-07 4860
该栏目会系统的介绍 MyBatis Plus 的知识体系，共分为简介、常用API、SQL注入器、代码生成器等模块 文章目录SQL 注入原理自定义 SQL 注入 SQL 注入原理 AbstractSqlInjector 中，主要由 inspectInject()方法 进行注入，该方法获取所有 AbstractMethod （相关子类用于 sql 注入，如SelectById 子类） 遍历 AbstractMethod，并调用其 inject()方法，最后调用其抽象 injectMappedStateme
3、AOP技术
HadesLin的博客
06-30 4761
文章目录前言一、pandas是什么？二、使用步骤1.引入库2.读入数据总结 前言 提示：这里可以添加本文要记录的大概内容： 例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。 提示：以下是本篇文章正文内容，下面案例可供参考 一、pandas是什么？ 示例：pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。 二、使用步骤 1.引入库 代码如下（示例）： import numpy as np import
4、数据库操作
HadesLin的博客
06-30 4680
文章目录JdbcTemplate框架1、概述2、实现CRUD事务 JdbcTemplate框架 1、概述 ：小型的轻量级持久化层框架 2、实现CRUD 流程 Created with Raphaël 2.2.0开始导入相关Jar包配置文件配置信息数据源对象，加载驱动设置数据库模版调用jdbcTemplate对象提供的相关方法编写测试类结束 用户操作类 public class UserDao { private JdbcTemplate jdbcTemplate; public
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值