MySQL数据库优化技术之配置技巧总结

本文实例讲述了MySQL数据库优化技术的配置方法。分享给大家供大家参考,具体如下:

(一)减少数据库访问

对于可以静态化的页面,尽可能静态化

对一个动态页面中可以静态的局部,采用静态化

部分数据可以生成XML,或者文本文件形式保存

使用数据缓存技术,例如: MemCached

(二)优化的检测方法

1.用户体验检测

2.Mysql状态检测

在Mysql命令行里面使用show status命令,得到当前mysql状态。

主要关注下列属性:

key_read_requests (索引读的请求数)(key_buffer_size设置影响)

key_reads(索引读响应数)

Key_blocks_used

Qcache_*

Open_tables(通过table_cache的设置影响)

Opened_tables

table_locks

3. 第三方工具检测

mysqlreport http://hackmysql.com/mysqlreport

mytop http://jeremy.zawodny.com/mysql/mytop/

系统及Mysql的Log

系统命令: top, sar

Mysql的Log: slow_query.log

(三)硬件方面的优化

硬件方面,最容易成为Mysql瓶颈的部分是磁盘,其次是CPU和内存

磁盘方面

使用更快的磁盘,会对Mysql有很好的帮助

使用更多的硬盘,通过Raid,可以提高单块磁盘速度的问题

对于Raid方式,建议采用Raid 0+1 或者 Raid 1+0

CPU

毫无疑问,更高主频的CPU和更多的CPU数量可以给Mysql更

高的性能

内存

更高的内存,往往可以让Mysql中的更多的数据缓存在内存中,

但是,一个重要的因素是,需要有正确的Mysql的配置

网卡

使用千兆网卡及千兆网络

(四)操作系统方面的优化

1.不使用交换区。如果内存不足,增加更多的内存或配置你的系统使用较少内存

2. 不要使用NFS磁盘

3.增加系统和MySQL服务器的打开文件数量

使用ulimit –n 65535

4.增加系统的进程和线程数量。

5.关闭不必要的应用,优化硬盘参数,使用hdparm测试

(五)应用级的优化

1.使用多服务器负载均衡(多台读和写,用复制技术进行数据同步)

2.表的分区 (自定义分区,mysql5.1开始支持自带分区功能)

3.使用数据缓存技术memcached

(六)Mysql配置的优化

1.key_buffer(=512):索引缓冲使用的内存数量

这对MyISAM表来说非常重要,设定在可用内存的25%-30%较好,通过检查状态值 Key_read_requests和 Key_reads,

可以知道key_buffer设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好 ,否则说明 key_buffer 设置有点偏小

2.innodb_buffer_pool_size(= 512):索引缓冲使用的内存数量

3.table_cache (=1024):数据表缓存区的尺寸

每当 MySQL 访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。

通过检查运行峰值时间的 Open_tables 和 Opened_tables 状态值,可以决定是否需要调整 table_cache 的值。

如果你发现 open_tables 的值等于 table_cache,并且发现 opened_tables 状态值在不断增长,那么你就需要增加 table_cache 参数值了,

也不能盲目地把 table_cache 参数设置成很大的值,如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

4.sort_buffer_size (=256):指定排序用缓冲区的长度

该参数对应的分配内存是每连接独占!如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。

所以,对于内存在4GB左右的服务器推荐设置为6-8M

5.join_buffer_size :关联查询用缓冲区的长度

4G内存以上,建议大于32M,该参数对应的分配内存也是每连接独享!

6.max_connections (=1024):可以复用的线程数量

允许同时连接MySQL服务器的客户数量 ,可以观察和估计系统在峰值最大的并发连接数来设置

7.thread_cache(=*):可以复用的线程数量

一般设置为CPU数×2

8.innodb_buffer_pool_size(= 512):innodb表缓存池大小

这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,

然而Innodb在默认的innodb_buffer_pool_size 设置下却跟蜗牛似的。

由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。

一些应用于 key_buffer 的规则有 -- 如果你的数据量不大,并且不会暴增,那么无需把innodb_buffer_pool_size 设置的太大了.

9.innodb_flush_logs_at_trx_commit(=1) :事务提交后的日志刷新模式

是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是 1,这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中,

而这相当耗费资源,尤其是没有电池备用缓存时。很多应用程序,尤其是从 MyISAM转变过来的那些,把它的值设置为 2 就可以了,也就是不把日志刷新到磁盘上,

而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。如果设置为0就快很多了,不过也相对不安全了,

MySQL服务器崩溃时就会丢失一些事务。设置为2指挥丢失刷新到操作系统缓存的那部分事务.

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL索引操作技巧汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

希望本文所述对大家MySQL数据库计有所帮助。

(0)

相关推荐

  • MySQL数据库优化经验详谈(服务器普通配置)第1/3页

    安装好mysql后,配制文件应该在/usr/local/mysql/share/mysql目录中,配制文件有几个,有my- huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文件了. 一般的情况下,my-medium.cnf这个配制文件就能满足我们的大多需要:一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了,使用mysqladmin variables ex

  • mysql数据库优化必会的几个参数中文解释

    第一步: 1:磁盘寻道能力,以高速硬盘(7200转/秒),理论上每秒寻道7200次.这是没有办法改变的,优化的方法是----用多个硬盘,或者把数据分散存储. 2:硬盘的读写速度,这个速度非常的快,这个更容易解决--可以从多个硬盘上并行读写. 3:cpu.cpu处理内存中的数据,当有相对内存较小的表时,这是最常见的限制因素. 4:内存的限制.当cpu需要超出适合cpu缓存的数据时,缓存的带宽就成了内存的一个瓶颈---不过现在内存大的惊人,一般不会出现这个问题. 第二步: (本人使用的是学校网站的l

  • mysql数据库优化总结(心得)

     1. 优化你的MySQL查询缓存在MySQL服务器上进行查询,可以启用高速查询缓存.让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一.当同一个查询被执行多次时,如果结果是从缓存中提取,那是相当快的.但主要的问题是,它是那么容易被隐藏起来以至于我们大多数程序员会忽略它.在有些处理任务中,我们实际上是可以阻止查询缓存工作的. 复制代码 代码如下: // query cache does NOT work$r = mysql_query("SELECT username FROM user W

  • MySQL数据库优化详解

    mysql表复制 复制表结构+复制表数据 mysql> create table t3 like t1; mysql> insert into t3 select * from t1; mysql索引 ALTER TABLE用来创建普通索引.UNIQUE索引或PRIMARY KEY索引 ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list)

  • mysql 单机数据库优化的一些实践

    数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表.另外,一般大企业面试往往会从单机数据库问起,一步一步问到分库分表,中间会穿插很多数据库优化的问题.本文试图描述单机数据库优化的一些实践,数据库基于mysql,如有不合理的地方,欢迎指正. 1.表结构优化 在开始做一个应用的时候,数据库的表结构设计往往会影响应用后期的性能,特别是用户量上来了以后的性能.因此,表结构优化是一个很重要的步骤.

  • MySQL 数据库优化的具体方法说明

    以下的文章主要描述的是MySQL数据库简单实用优化的具体方法的实现,中包括如何定期的表进行分析与检查, 以及如何正确对表进行定期的优化,以下就是具体方案的描述,希望在你今后的学习中会有所帮助. 1.定期分析表和检查表 分析表的语法如下: 复制代码 代码如下: ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]... 以上语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信息,使得SQL能够生成正确的

  • 运维角度浅谈MySQL数据库优化(李振良)

    一个成熟的数据库架构并不是一开始设计就具备高可用.高伸缩等特性的,它是随着用户量的增加,基础架构才逐渐完善.这篇博文主要谈MySQL数据库发展周期中所面临的问题及优化方案,暂且抛开前端应用不说,大致分为以下五个阶段: 1.数据库表设计 项目立项后,开发部根据产品部需求开发项目,开发工程师工作其中一部分就是对表结构设计.对于数据库来说,这点很重要,如果设计不当,会直接影响访问速度和用户体验.影响的因素很多,比如慢查询.低效的查询语句.没有适当建立索引.数据库堵塞(死锁)等.当然,有测试工程师的团队

  • mysql数据库优化需要遵守的原则

    这是我在网上看到的一篇不错的mysql数据库优化文章,内容说的很全! 1.使用索引来更快地遍历表 缺省情况下建立的索引是非群集索引,但有时它并不是最佳的.在非群集索引下,数据在物理上随机存放在数据页上.合理的索引设计要建立在对各种查询的分析和预测上.一般来说: a.有大量重复值.且经常有范围查询( > ,< ,> =,< =)和order by.group by发生的列,可考虑建立群集索引; b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引; c.组合索引要尽量使关键查询

  • MySQL数据库优化推荐的编译安装参数小结

    MySQL编译参数多而复杂,让新手感到很头大,如果是正式生成环境安装MySQL,没有充足的时间去研究每一个参数代表的意义,个人建议使用余洪春前辈整理的编译参数,便捷高效! MySQL的线上安装建议采取编译安装的方法,这样性能上有较大提升,,源码包的编译参数会默认以Debgu模式生成二进制代码,而Debug模式给MySQL带来的性能损失是比较大的,所以当我们编译准备安装的产品代码时,一定不要忘记使用"-without-debug"参数禁用Debug模式. 而如果把-with-mysqld

  • mysql数据库常见的优化操作总结(经验分享)

    前言 对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要.所以mysql数据库的优化操作大家都要有所了解,本文就主要总结了mysql数据库中常见的优化操作,下面话不多说了,来看看详细的介绍吧. 一.Index索引 将Index放第一位,不用说,这种优化方式我们一直都在悄悄使用,那便是主键索引.有时候我们可能并不在意,如果定义适合的索引,数据库查询性能(速度)将提高几倍甚至几十倍. 普通索引 作用是提高查询速度. 建表,创建索引 CREATE TABLE tbl_

随机推荐