`
wangdei
  • 浏览: 372917 次
社区版块
存档分类
最新评论

大议mysql变量及其优化

阅读更多
mysql里的变量分系统变量和用户变量.           

 

系统变量

其中系统变量,又分全局(global)和会话(session)两种.
全局系统变量,主要影响整个mysql实例的全局设置.
大部分变量都是作为mysql的服务器调节参数存在.新增或修改这类变量, 会影响mysql的运行方式.
比如: auto_increment_increment 代表序列的自增值, 默认为1

另外, mysql中,只有一部分变量是支持运行时动态修改的.
变量修改的作用范围,是那些重新创建连接到mysql服务器的客户端连接.

如果你的连接是由应用通过连接池来维护的,并且,是长连接的,那么,此时在服务器运行过程中,动态地修改全局变量对你是没有什么影响的.

通过全局变量, 我们可以想到一些应用场景:
1,  在存储过程, 函数, sql里,使用一些业务相关的全局变量.
经常编写业务相关的存储过程,函数的朋友, 可能会需要针对不同业务,设置能共享访问的全局变量.

比如, 公司的总人数,很多存储过程都需要调用这个值,但是因为这个值,不是经常会改变的,不需要每次都count.
所以大多数人会考虑把它cache一把,那么mysql提供的全局变量,就是一个好的存储场所.

2,  数据库配置中心
 

 

首先,这不一定是一种好的解决方式, 特别是大型地分布式系统.但是想到了,就跟大家分享一下.
主要就是利用了三层,四层架构的系统, 必不可少的数据库层来实现的.

大家知道, 应用的配置, 大多数是用配置文件或配置服务器来实现, 前者需要跟着项目打包, 部署.
后者, 只要在应用初始化或运行时,去配置中心取配置就行了.(淘宝这边两者都在用)

而数据库的配置中心, 其实类似配置服务器, 只不过灵活运用了mysql的变量机制.
它能继承配置服务器的多数优点, 但最大的特色就是在 sql, function , procedure 里都可以很方便地引用到.(相比用表的方式, 要方便一些)
并且本身就具有缓存, 移植得话, mysql得导出也是很快.
所以小系统, 想快速得搭建一个配置中心,利用mysql感觉还是不错的.

 

 

调用语法:
select * from user_info where id = @@global.admin_id — 例子,通过全局里配置的管理员id来取用户信息

以下是全局变量的一些常用命令,比较简单:

set global auto_increment_increment=1; — 设置序列的增长值
show global variables; —  显示所有的global变量
show global variables like ‘%test%’ — 查询包含test字符串的global变量

会话系统变量主要用于在当前客户端连接的生命周期内.它的变量值是全局变量的一份拷贝.
如果连接断开, 对当前会话变量所做修改都会被重置.

比如, 服务器会话变量 autocommit 默认为 true , 而你在非常连的客户端连接里设置了 false , 那么在执行完sql后, 连接就会断开. 此后,如果创建新的连接来执行sql, autocommit又会默认 true.

会话变量的使用场景与全局类似,只是生命周期不同, 因此可以用来
统计同一连接内请求sql次数, sql类型等信息.

session变量的一些常用操作:

set session auto_increment_increment=1; — 设置序列的增长值
show variables; or show session variables; —  如果不指明, 默认使用session变量
show variables like ‘%test%’ or show session variables lile ‘%test%’ — 查询包含test字符串的session变量

用户变量

基于会话变量实现的, 可以暂存值, 并传递给同一连接里的下一条sql使用的变量.
当客户端连接退出时,变量会被释放.

应用场景:
同一连接,未关闭情况下,帮你暂存一些计算结果.
 

 

比如
select @admin_id:=max(id) from user_info;
select * from user_info where id = @admin_id
以上两条sql在同一connection中完成.
另外,注意,用户变量前只有1个@, 2个@是用于存取系统变量的

业务相关的变量定义太多,无法区分系统全局变量?

 

 

这类问题,可以通过mysql提供的结构化系统变量来解决.

他们有独特的表示形式:
instance_name.test_var

比一般的变量多了一个instance_name.这样,你就可以很方便地对他们进行分类,比如  app.test_var,就可以区别于系统的var了.

PS: 其实,普通的系统变量,它也属于结构化变量,只不过他们默认隶属于 default 这个 instance,  不需要显示出来罢了.

影响mysql性能的系统变量

以下内容参考 http://www.mysqlperformanceblog.com/2006/06/08/mysql-server-variables-sql-layer-or-storage-engine-specific/

http://www.bt285.cn/content.php?id=1196863

bulk_insert_buffer_size

批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时, 提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.

concurrent_insert
并发插入, 当表没有空洞(删除过记录), 在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.
值可以设0不允许并发插入,1当表没有空洞时,执行并发插入,2.不管是否有空洞都执行并发插入.
默认是1.针对表的删除频率来设置.

delay_key_write

针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘. 值为 0不开启, 1开启. 默认开启.

delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延迟插入, 将数据先交给内存队列, 然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持, 目前来看, 常用的InnoDB不支持, MyISAM支持. 根据实际情况调大, 一般默认够用了.

expire_logs_days

自动删除超过指定天数的日志. 建议为0,表示“不自动删除”.

flush, flush_time

是否启用, 同步表数据到磁盘.以及自动同步的间隔时间.
针对flush_time, 官方建议只在Windows 9xMe,或有最小资源的系统中使用该选项.所以,建议关闭.

ft_boolean_syntax, ft_max_word_len, ft_min_word_len,ft_query_expansion_limit, ft_stopword_file

针对MyISAM设置的参数, 全文搜索特性. 如果你不使用 FULLTEXT 索引,就不需要优化了. 详见mysql参考手册.

join_buffer_size

用于表间关联(join)的缓存大小.建议设为 131072.(128K)

key_buffer_size

索引块缓存区大小, 针对MyISAM存储引擎,该值越大,性能越好.但是超过操作系统能承受的最大值,反而会使mysql变得不稳定.
如果不是MyISAM存储引擎,一般设置为 4-32M大小.

key_cache_age_threshold, key_cache_block_size, key_cache_division_limit

键值缓存的相关设置.需要针对实际情况调优.只是针对MyISAM储存引擎.

large_pages

是否启动大页面支持.意思是,可以一些缓存分配更大的空间.这个特性已经被InnoDB, MyISAM等常用存储引擎支持.

low_priority_updates

低优先级更新.意思是, 所有的写操作(表写锁), 包括update,delete,insert等都需要等待读操作完成后才执行 (表读锁解开).
因为是针对表的锁,所以,这里仅支持MyISAM.

max_write_lock_count

最大写锁数量.这个变量的含义是, 当写锁达到一定数量后, 就不限制读锁, 允许一部分读锁进入.(可以读数据了,否则需要等待写锁释放后,才能读)

因为是针对表的锁,所以,这里仅支持MyISAM.

preload_buffer_size

重载索引时分配的缓冲区大小, 该变量仅支持MyISAM.

read_buffer_size, read_rnd_buffer_size

每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描,可能需要增加该值, 默认值为131072

sort_buffer_size

每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BYGROUP BY操作.默认数值是2097144(2M),建议加大到 16777208 (16M)。

timed_mutexes

显示mutexes的统计信息, 默认关闭OFF

tmp_table_size

临时表的大小,在未超过大小之前进行的操作是在内存中的, 当超过后,mysql会自动转换到硬盘上.

分享到:
评论

相关推荐

    MySQL 5.5 服务器变量详解

    ### MySQL 5.5 服务器变量详解 #### autocommit={0|1} ...以上介绍了MySQL 5.5中的一些关键服务器变量及其配置方式。了解这些变量的含义和作用可以帮助数据库管理员更好地管理和优化MySQL服务器性能。

    mysql 系统变量中文详细解释

    对于数据库管理员来说,理解这些系统变量的意义及其作用至关重要,这有助于优化数据库性能、解决性能瓶颈问题,并确保系统的稳定运行。下面将对MySQL 5.5中的部分关键系统变量进行详细的中文解释。 ### 1. `innodb_...

    mysql性能优化-慢查询分析、优化索引和配置.doc

    ### MySQL性能优化详解 #### 一、优化概述 MySQL作为一种广泛使用的开源关系型数据库管理系统,在很多应用场景中都可能遇到性能瓶颈的问题。常见的性能瓶颈主要分为两大类:**CPU瓶颈**和**I/O瓶颈**。 - **CPU...

    MySQL中服务器状态变量全解.pdf

    本文将详细介绍MySQL中几种重要的服务器状态变量及其作用,帮助读者更深入地理解这些变量,并学会如何利用它们来提升MySQL服务器的稳定性和性能。 #### 状态变量概述 MySQL服务器维护了一系列状态变量,用以记录和...

    MySQL优化中的内存计算公式

    本文将详细介绍MySQL内存计算公式及其背后的原理。 #### 二、MySQL内存计算公式 MySQL的内存消耗主要包括两大部分:静态内存消耗与动态内存消耗。其中,静态内存消耗是指由MySQL服务端自身所占用的固定内存部分;...

    MYSQL环境变量设置方法

    本文将详细介绍如何为MySQL设置环境变量,以及如何通过命令行使用MySQL及其配置变量。 首先,MySQL数据库的环境变量设置主要指的是将MySQL的安装目录下的bin文件夹路径添加到系统的Path环境变量中。这样一来,用户...

    (免编译版)linux系统mysql安装配置优化手册V2.1.docx

    使用`chown`和`chgrp`命令将`/usr/local/mysql`及其子目录的所有权更改为`root`用户和`mysql`组,然后将`/usr/local/mysql/data`目录的所有权转移给`mysql`用户,确保数据安全性。 6. **配置MySQL**: 复制配置...

    mysql5.1动态修改的参数变量

    下面将详细介绍 MySQL 5.1 中可动态修改的参数及其含义、作用范围等信息。 #### 可动态修改的参数列表及说明 1. **`autocommit`** - **值类型**:`boolean` - **类型**:`SESSION` - **说明**:控制事务是否...

    MySQL查询优化--调整内部变量的详解

    以下将详细讲解几个关键的MySQL内部变量及其优化方法。 1. **改变索引缓冲区长度(key_buffer_size)**: 索引缓冲区是MySQL用来存储索引页的内存区域。增大`key_buffer_size`可以提升索引的读取速度,特别是对于...

    mysql5.7 mac版本64位 免费下载安装

    本篇将详细讲解MySQL 5.7在Mac上的免费安装过程及其核心特性。 首先,让我们了解一下MySQL 5.7相较于早期版本的一些显著改进: 1. **三倍更快的性能**:MySQL 5.7通过一系列性能优化,包括更好的查询执行计划、更...

    MySQL优化笔记

    本篇笔记将围绕MySQL 5.5.35版本展开,探讨其优化的三大方向及其具体实施方法。 #### 一、优化MySQL所在服务器内核 这一环节通常由运维人员来完成,旨在通过调整操作系统内核参数来改善MySQL的运行环境。主要包括...

    linux 高负载下mysql数据库优化

    在这种情况下,对MySQL进行优化至关重要。MySQL的灵活性使其能够在各种资源限制下运行,通过适当调整配置参数,我们可以显著提高其性能。 首先,我们需要关注配置文件。在安装MySQL后,配置文件通常位于`/usr/local...

    MySQL性能优化的最佳20多条经验分享

    - MySQL的查询缓存可以显著提升性能,存储执行过的SQL查询及其结果。但需要注意的是,含有诸如`CURDATE()`、`NOW()`等动态函数的查询无法被缓存,因为它们每次执行的结果可能不同。因此,应避免在SQL查询中使用这类...

    MySQL学习-程序选项修改器和使用选项设置程序变量.pdf

    综上所述,理解和掌握这些程序选项修改器及变量设置方法对于高效地使用MySQL及其相关工具至关重要。这些技巧不仅可以帮助优化性能,还能提高程序的灵活性和适应性,使得MySQL更加适合不同的应用场景。

    2MySQL的调控按钮 —— 启动选项和系统变量(1).md

    ”命令的输出,用户可以看到所有系统变量及其值。 举例说明: 1. 使用“skip-networking”启动选项阻止客户端通过TCP/IP连接到MySQL服务器。 2. 通过“--default-storage-engine=MyISAM”更改表的默认存储引擎为...

    MySQL Internals Manual.pdf

    提供了如何使用CMake来控制MySQL插件构建的变量和宏的信息,这些指南适用于MySQL 5.5及之后的版本。 从这些内容中,我们可以提炼出以下知识点: - MySQL源码目录结构和功能 - MySQL编码规范和风格指南 - MySQL...

    MySQL 5.7参考手册

    该手册为MySQL 5.7版本的官方文档,包含了MySQL 5.7及其相关的NDB Cluster版本(7.5和7.6)的所有特性、配置和使用指南。 #### 描述:MySQL 5.7参考手册PDF版本,欢迎下载 这是一个可供下载的PDF格式的手册,供...

Global site tag (gtag.js) - Google Analytics