首页 / MYSQL / 浅谈MySQL数据库性能优化

浅谈MySQL数据库性能优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了浅谈MySQL数据库性能优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9901字，纯文字阅读大概需要15分钟。

内容图文

MySQL数据库是 IO 密集型的程序，和其他数据库一样，主要功能就是数据的持久化以及数据的管理工作。本文侧重通过优化MySQL 数据库缓存参数如查询缓存，表缓存，日志缓存，索引缓存，innodb缓存，插入缓存，以及连接参数等方式来对MySQL数据库进行优化。缓存

MySQL数据库是 IO 密集型的程序，和其他数据库一样，主要功能就是数据的持久化以及数据的管理工作。本文侧重通过优化MySQL 数据库缓存参数如查询缓存，表缓存，日志缓存，索引缓存，innodb缓存，插入缓存，以及连接参数等方式来对MySQL数据库进行优化。

缓存参数

这里先引用一句话，从内存中读取一个数据的时间消耗是微秒级别，而从普通硬盘上读取是毫秒级别，二者相差3个数量级。可见，想对MySQL数据库进行优化，合理调配缓存参数显得更为直接

表缓存

相关参数： table_open_cache

指定表缓存的大小，早期版本为table_cache。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值，如果发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么就需要增加这个参数的值了。注意，不能盲目地把这个参数设置得很大，如果设置太大，会引起文件描述符不足，造成性能不稳定或者数据库连接失败。建议为512

对于大多数情况，比较适合的值：

Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95

查询缓存

相关参数： query_cache_size / query_cache_type

QC（注：查询缓存简称）主要用来缓存 MySQL 中的结果集，也就是一条SQL语句执行的结果集，所以仅仅只能针对select 语句。如果启用了QC 功能，MySQL在接到select 请求后，如果该语句满足QC的要求，MySQL 会直接根据HASH算法将接收到的select 语句以字符串方式进行hash，然后到QC中直接查找，如果已经在缓存中，该select 请求就会直接将数据返回，从而省略了后面所有的步骤（如 SQL语法解析，优化器优化以及存储引擎请求数据等），极大的提高性能。

当然，QC也有一个致命的缺陷，就是当表中数据有变化时，所有引用到该表的 QC 缓存全部失效。所以，当数据变化非常频繁的情况下，使用QC 反而得不偿失。

QC 的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置缓存记录集的内存大小，后者设置在何场景下使用QC 。

在以往的经验来看，中等规模的网站，query_cache_size 设置 256MB 足够了。当然，还可以通过计算QC的命中率来进行调整。

Qcache_hits / (Qcache_hits + Qcache_inserts)  * 100%

query_cache_type有三种选择：0（OFF，不使用QC），1（ON，默认使用QC ），2（DEMAND，默认不使用QC）。

为什么加上“默认”？MySQL还支持动态使用缓存的SQL语法，如下：

# 强制使用缓存 
SELECT SQL_CACHE id FROM table 

# 强制不使用缓存 
SELECT SQL_NO_CACHE id FROM table

日志缓存

相关参数：binlog_cache_size

用于在打开了二进制日志（binlog）记录功能的环境中，是 MySQL 用来提高 binlog 的记录效率而设计的一个在短时间内缓存binlog 数据的内存缓存。

如果数据库中没有大事务，写入不是特别频繁，2MB～4MB是一个合适的选择。但是如果数据库大事务较多，写入比较频繁，可适当加大。使用的时候，还可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件来缓存了。

索引缓存

相关参数：key_buffer_size

这个是对MyISAM表性能影响最大的一个参数，用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果有足够的内存，这个缓存区域大小可以设为所有的 MyISAM表的索引大小的总和，即 data 目录下所有*.MYI文件大小的总和。

注意，由于 MyISAM 引擎只会缓存索引块到内存中，而不会缓存表数据库块。所以，查询SQL语句一定要尽可能让过滤条件都在索引中，以便使用到索引缓存来提高查询效率。

计算索引缓存未命中的概率：

Key_reads / Key_read_requests * 100%

插入缓存

InnoDB缓存

相关参数：innodb_buffer_pool_size / innodb_additional_mem_pool_size

innodb_buffer_pool_size参数是影响InnoDB存储引擎性能的最为关键的一个参数，设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key_buffer_size 参数，当然，可能更像是 Oracle 的 db_cache_size。简单来说，当操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会涉及到这个内存区域。

innodb_buffer_pool_size 参数设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，所以如果有足够的内存，尽可能加大该参数的值，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中。

当然，可以通过计算缓存命中率，并根据命中率来调整这个参数的大小：

 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

innodb_additional_mem_pool_size 参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。随着数据库对象越来越多，需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。这个参数的大小是相对稳定的，没有必要预留非常大的值。如果InnoDB引擎用光了这个池内的内存，InnoDB引擎就开始从操作系统申请内存，并往MySQL错误日志写警告信息。默认值是1MB，当发现错误日志中已经有相关的警告信息时，就应该适当的增加该参数的大小。

innodb_log_buffer_size 参数是 InnoDB 存储引擎的事务日志所使用的缓冲区。类似于 Binlog Buffer，InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innofb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

innodb_flush_log_trx_commit 参数对 InnoDB引擎日志的写入性能有非常关键的影响。该参数可以设置为0，1，2，如下：

0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；

1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；

2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。

此外，MySQL文档中还提到，这几种设置中的每秒同步一次的机制，可能并不会完全确保非常准确的每秒就一定会发生同步，还取决于进程调度的问题。实际上，InnoDB 能否真正满足此参数所设置值代表的意义正常 Recovery 还是受到了不同 OS 下文件系统以及磁盘本身的限制，可能有些时候在并没有真正完成磁盘同步的情况下也会告诉 mysqld 已经完成了磁盘同步。

innodb_max_dirty_pages_pct 参数用来控制在 InnoDB 缓冲池（Buffer Pool）中可以不用写入数据文件中的脏页（Dirty Page）的比例（已经被修但还没有从内存中写入到数据文件的脏数据）。这个比例值越大，从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘IO。但是，如果这个比例值过大，当数据库崩溃（Crash）之后重启的时间可能就会很长，因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。同时，过大的比例值同时可能也会造成在达到比例设定上限后的 flush 操作“过猛”而导致性能波动很大。如果这个参数设置过大，将会导致MySQL启动时间过长，关闭时间也过长。

连接参数

MySQL数据库操作是建立在MySQL数据库连接的基础上，所以提高MySQLl处理连接的能力，也是提高MySQL的性能的一个重要途经。

连接数量

相关参数：max_connections / back_log

max_connections参数设置MySQL的最大连接数，也就是允许同时连接的客户数量。如果服务器的并发连接请求比较大，建议调高此值，以增加并行连接数量。但连接数越大，MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，服务器消耗的内存越多，可能会影响服务器性能，所以要根据服务器的配置适当调整该值，不能盲目提高设值。默认数值是100。

计算MySQL繁忙时处理连接的情况，建议值50% ~ 80%

max_used_connections / max_connections * 100%

back_log参数设置MySQL能暂存的连接数量。当MySQL在一个很短时间内收到非常多的连接请求时起作用。如果MySQL的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。设定back_log高于你的操作系统的限制是无效的。默认数值是50。

连接超时

相关参数：wait_timeout / interactive_timeout

服务器关闭连接之前等待活动的秒数。MySQL所支持的最大连接数是有限的，因为每个连接的建立都会消耗内存，因此我们希望MySQL 处理完相应的操作后，应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。建议120 ~ 300

连接检查

相关参数：skip-name-resolve

skip-name-resolve参数用于禁止DNS的反向解析。MySQL默认开启了DNS的反向解析，当有新的连接到来时，MySQL会解析连接主机的DNS，这就影响了连接速度。使用该参数也有一个代价，就是每次连接都要使用ip地址，就不能再使用localhost，改成127.0.0.1

配置建议值

根据以往经验取值，仅供参考，不一定适用于所有场景，建议在生产环境中进一步分析调整

MySQL配置	建议值说明
table_open_cache	如果设置太大，会造成系统不稳定或者数据库连接失败，建议最大512
query_cache_type	没有使用MyISAM引擎，建议0；否则建议1，如果写入过于频繁，建议2
query_cache_size	根据实际命中率进行调整，不需要太大，建议256MB
binlog_cache_size	建议2MB ~ 4MB，事务较大且写入频繁可以适当调大，但不要超过32MB
key_buffer_size	如果使用MyISAM，在内存允许的情况下，尽可能加大，参考值 512MB
bulk_insert_buffer_size	如果经常性的需要使用批量插入数据，可以适当调大至32MB
innodb_buffer_pool_size	如果使用InnoDB，在内存允许的情况下，可以设置50% ~ 80%内存
innodb_log_buffer_size	默认是1MB，数据库操作频繁的系统可适当增大至4MB ~ 16MB
innodb_max_dirty_pages_pct	这个值越大，数据库启动时间和关闭时间越长，可以适当调大至90
max_connections	根据实际情况取值，过大反而影响性能。默认值100，建议128 ~ 512
back_log	默认数值是50，建议 128 ~ 512
wait_timeout	同时修改interactive_timeout，默认28800（8小时），建议120 ~ 300

可能用到的MySQL命令：

# 查看当前MySQL运行状态值
mysql> show global status like 'Thread_%';

# 查看当前MySQL配置信息
mysql> show global variables like '%binlog%';

# 实时修改MySQL配置信息
mysql> set global max_connections=512;

参考

http://blog.csdn.net/mycwq/article/details/16370525
http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter
http://database.51cto.com/art/201010/229939.htm
http://jackyrong.iteye.com/blog/781859
http://www.bootf.com/523.html

内容总结

以上是互联网集市为您收集整理的浅谈MySQL数据库性能优化全部内容，希望文章能够帮你解决浅谈MySQL数据库性能优化所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/535314.html

来源：【匿名】

【上一篇】mysql索引、存储方式对性能的影响【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【浅谈MySQL数据库性能优化】教程文章相关的互联网学习教程文章

从入门到自闭之Python--MySQL数据库的单表操作【代码】

单表查询：select * from 表 where 条件 group by 分组 having 过滤 order by 排序 limit n;语法：select distinct 字段1，字段2... from 表名 where 条件 group by 组名 having 筛选 order by 排序 limit 限制条数找到表:from拿着where指定的约束条件，去文件/表中取出一条条记录将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组执行select（去重）：select * from 表名;将分组的结果进行having过滤将结果按...

备份mysql数据库【图】

使用navicate 使用命令备份mysqldump -hlocalhost -uroot -prootdatabases > C:\Users\asus008\Desktop\mp4\databases.sql还原mysql -hlocalhost -uroot -proot databases < C:\Users\asus008\Desktop\mp4\databases.sql备份多个数据库mysqldump -hlocalhost -uroot -proot --databases oa tele > C:\Users\asus008\Desktop\mp4\databasesf.sql备份所有数据库mysqldump --all -databases -hlocalhost -uroot -proot > C:\Users\as...

Ubuntu下取消MySQL数据库本机绑定限制方法

出现问题：在Ubuntu系统中，添加了MySQL账户，赋予了数据库完全操作权限，并且允许数据库从外部链接但是，还是无法远程访问MySQL数据库。原因：Ubuntu系统中，默认安装的MySQL限制了只有本机才能访问数据库解决办法：取消MySQL数据库的本机绑定限制nano /etc/mysql/my.cnf #编辑配置文件找到bind-address = 127.0.0.1改为#bind-address = 127.0.0.1ctrl+o #保存配置ctrl+x #退出/etc/init.d/mysql restart #重启数据库现在已经可以...

MySQL数据库web维护管理软件【图】

TreeSoft数据库管理系统使用JAVA开发，采用稳定通用的springMVC +JDBC架构，实现基于WEB方式对 MySQL，Oracle，PostgreSQL 等数据库进行维护管理操作。功能包括：数据库的展示,库表的展示，表字段结构的展示， SQL语句的在线编辑批量执行，表结构的在线设计维护，数据的在线编辑维护，查询语句保存，JSON数据格式化，SQL语法帮助，在线数据源选择配置等。系统内置14套UI皮肤，45套代码编辑区UI方案，使用中可以依个人喜好选择配...

mysql数据库相关基础知识01【代码】

数据库的简介1.什么是数据库：数据仓库。访问必须只能用SQL语句来访问。数据库也是一个文件的系统。 2.数据库的作用：存储数据的作用。开发任何的应用，都有数据库。 3.关系型的数据库：数据库中保存的都是实体与实体之间的关系。 4.常见的数据库* Java开发，必用的两个数据库Oracle和MySQL* Oracle数据库（甲骨文）大型的数据库，收费的。* MySQL数据库小型的数据库，免费开源的。被Oracle收购了（在6.x版本下开始收费了）*...

python连接mysql数据库

1.系统必须安装MySQL-python软件，否则python没有连接的模块(在Linux系统)yum install MySQL-python2.安装mysql数据库yum install mysql-server mysql[root@AY140528120357495c4bZ ~]# /etc/init.d/mysqld restartStopping mysqld: [ OK ]Starting mysqld: [ OK ][root@AY140528120357495c4bZ ~]#3.在mysql中创建数据库和表[root@AY1405...

配置MySQL数据库【图】

一、配置MySQL数据库1、解压绿色版mysql，如下图二、安装服务1、运行cmd（管理员版本，否则没有权限），如下图 2、运行命令mysqld –install安装服务，如下图：如果不需要mysql里，只需要运行mysqld –remove即可移除，如下图 3、运行net start mysql 启动服务，如下图如需要配置是否开机启动mysql可以在windows服务里面配置。三、客户端测试1、mysql –uroot –p 如下图四、配置编码为UTF-81、在mysql5.6的根目录下，复制my-d...

MYSQLshu数据库学习----查询

查询语句是MYSQL数据库中用到的最多的语句。查询语句分为几种单表查询集合函数查询连接查询子查询合并查询正则表达式查询一：单表查询SELECT 属性 FROM 表名 [WHERE 查询条件][GROUP BY 属性名1 [HAVING 分组条件]][ORDER BY 属性2 [ASC/DESC]][LIMIT];WHERE 查询条件查询条件符号或关键字例子比较=、<、<=、>、>=、!=、<>、!>、!<WHERE ID = 1指定范围BETWEEN AND、NOT BETWEEN ANDWHERE ID BETWEEN 2 AND 3指定集合IN、NOT INWH...

MYSQL数据库导入数据时出现乱码的解决办法

首先在新建数据库时一定要注意生成原数据库相同的编码形式，如果已经生成可以用phpmyadmin等工具再整理一次，防止数据库编码和表的编码不统一造成乱码。方法一: 通过增加参数 –default-character-set = utf8 解决乱码问题mysql -u root -p password < path_to_import_file –default-character-set = utf8 方法二: 在命令行导入乱码解决1. use database_name; 2. set names utf8; (或其他需要的编码) 3. source example.sql (sql...

mysql数据库表格导出为excel表格【代码】

在本地数据库中操作如下：由于excel表格的编码是GBK，所以导出时要加一个设置字符编码；select*from 某个表 into outfile ‘d:/文件名.xls‘CHARACTERSET gbk; 原文：http://www.cnblogs.com/luwenjie110/p/4336988.html

如何设置才能远程登录Mysql数据库【代码】【图】

可以在一台机器上访问另一台机器的MySQL，但是需要一些设置。进入MySQL后，输入以下命令： GRANT ALL PRIVILEGES ON *.* TO ‘tigase‘@‘%‘ IDENTIFIED BY ‘123456‘ WITH GRANT OPTION;允许任何主机以tigase为用户名（root用户不能远程登录，只能本地登录），123456为密码访问MySQL。flush privileges;让MySQL重新加载权限，使赋予的权限能够马上生效。原文：http://www.jb51.net/article/54407.htm

linux下如何实现mysql数据库每天自动备份定时备份【代码】【图】

概述??备份是容灾的基础，是指为防止系统出现操作失误或系统故障导致数据丢失，而将全部或部分数据集合从应用主机的硬盘或阵列复制到其它的存储介质的过程。而对于一些网站、系统来说，数据库就是一切，所以做好数据库的备份是至关重要的！备份是什么？为什么要备份容灾方案建设存储介质光盘磁带硬盘磁盘阵列 DAS：直接附加存储 NAS：网络附加存储 SAN：存储区域网络云存储这里主要以本地磁盘为存储介质讲一下计划任务的...

ThinkPHP学习（三）配置PHP5支持MySQL，连接MySQL数据库【图】

配置PHP5支持MySQL打开PHP配置文件“F:\PHP\php5328Win32\php.ini”，查找“extension=php_mysql.dll”，把前面的分号去掉，保存文件，启动服务；却提示“PHP startup: Unable to load dynamic library :F:\PHP\php_mysql.dll”错误，解决办法，打开PHP配置文件“F:\PHP\php5328Win32\php.ini”，查找“extension_dir = "ext"”，把前面的分号去掉，保存文件，启动服务，问题解决；准备测试数据如果你还没有安装MySQL，请先安装MyS...

全网最全一篇MySQL数据库MVCC详解，不全你打我【图】

什么是MVCC全称Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。以下文章都是围绕InnoDB引擎来讲，因为myIsam不支持事务。同一行数据平时发生读写请求时，会上锁阻塞住。但mvcc用更好的方式去处理读—写请求，做到在发生读—写请求冲突时不用加锁。这个读是指的快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。那它到底是怎么做到读—写不用加锁的，快照读和当前读又是什么鬼，跟着...

Spring Batch示例: 读取CSV文件并写入MySQL数据库【代码】

Spring Batch示例: 读取CSV文件并写入MySQL数据库GitHub版本: https://github.com/kimmking/SpringBatchReferenceCN/blob/master/01_introduction/Spring_Batch_MySQL.md原文链接: Reading and writing CVS files with Spring Batch and MySQL原文作者: Steven Haines - 技术架构师下载本教程的源代码: SpringBatch-CSV演示代码用批处理程序来操作动辄上GB的数据很可能会拖死整个系统,但现在我们可以通过Spring Batch将其拆解为多...

首页 / MYSQL / 浅谈MySQL数据库性能优化

浅谈MySQL数据库性能优化

内容导读

内容图文

缓存参数

表缓存

查询缓存

日志缓存

索引缓存

插入缓存

InnoDB缓存

连接参数

连接数量

连接超时

连接检查

配置建议值

内容总结

内容备注

内容手机端

【浅谈MySQL数据库性能优化】教程文章相关的互联网学习教程文章

MYSQL - 相关标签

数据库 - 相关标签

性能 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程