mysql服务性能优化 之 my.cnf配置说明详解

postgresql据说性能报表,但感觉mysql优化下性能也不差!!!

MYSQL服务器my.cnf配置文档详解
硬件:内存16G

[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit = 10240

back_log = 600
#在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。

max_connections = 3000
#MySQL允许最大的进程连接数,如果经常出现Too Many Connections的错误提示,则需要增大此值。

max_connect_errors = 6000
#设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。

table_cache = 614
#指示表调整缓冲区大小。# table_cache 参数设置表高速缓存的数目。每个连接进来,都会至少打开一个表缓存。#因 此, table_cache 的大小应与 max_connections 的设置有关。例如,对于 200 个#并行运行的连接,应该让表的缓存至少 有 200 × N ,这里 N 是应用可以执行的查询#的一个联接中表的最大数量。此外,还需要为临时表和文件保留一些额外的文件描述符。
# 当 Mysql 访 问一个表时,如果该表在缓存中已经被打开,则可以直接访问缓存;如果#还没有被缓存,但是在 Mysql 表缓冲区中还有空间,那么这个表就被打开并放入 表缓#冲区;如果表缓存满了,则会按照一定的规则将当前未用的表释放,或者临时扩大表缓存来存放,使用表缓存的好处是可以更快速地访问表中的内容。执 行 flush tables 会#清空缓存的内容。一般来说,可以通过查看数据库运 行峰值时间的状态值 Open_tables #和 Opened_tables ,判断是否需要增加 table_cache 的值(其 中 open_tables 是当#前打开的表的数量, Opened_tables 则是已经打开的表的数量)。即如果open_tables接近 table_cache的时候,并且Opened_tables这个值在逐步增加,那就要考虑增加这个#值的大小了。还有就是 Table_locks_waited比较高的时候,也需要增加table_cache。

external-locking = FALSE
#使用–skip-external-locking MySQL选项以避免外部锁定。该选项默认开启

max_allowed_packet = 32M
#设置在网络传输中一次消息传输量的最大值。系统默认值 为1MB,最大值是1GB,必须设置1024的倍数。

sort_buffer_size = 2M
# Sort_Buffer_Size 是一个connection级参数,在每个connection(session)第一次需要使用这个buffer的时候,一次性分配设置的内存。
#Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。例如:500个连接将会消耗 500*sort_buffer_size(8M)=4G内存
#Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。
#技术导读 http://blog.webshuo.com/2011/02/16/mysql-sort_buffer_size/
#dev-doc: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html
#explain select*from table where order limit;出现filesort
#属重点优化参数

join_buffer_size = 2M
#用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

thread_cache_size = 300
# 服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请 求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比 较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。设置规则如下:1GB 内存配置为8,2GB配 置为16,3GB配置为32,4GB或更高内存,可配置更大。

thread_concurrency = 8
# 设置thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或 多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个 cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那么 thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8
#属重点优化参数

query_cache_size = 64M
## 对于使用MySQL的用户,对于这个变量大家一定不会陌生。前几年的MyISAM引擎优化中,这个参数也是一个重要的优化参数。但随着发展,这个参 数也爆露出来一些问题。机器的内存越来越大,人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一 下 query_cache_size的工作原理:一个SELECT查询在DB中工作后,DB会把该语句缓存下来,当同样的一个SQL再次来到DB里调用 时,DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点,就是DB在利用Query_cache工作时,要求该语句涉及的 表在这段时间内没有发生变更。那如果该表在发生变更时,Query_cache里的数据又怎么处理呢?首先要把Query_cache和该表相关的语句全 部置为失效,然后在写入更新。那么如果Query_cache非常大,该表的查询结构又比较多,查询语句失效也慢,一个更新或是Insert就会很慢,这 样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统,该参数不适合分配过大。而且在高并发,写入量大的 系统,建议把该功能禁掉。
#重点优化参数(主库 增删改-MyISAM)

query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小,缺省为1M

query_cache_min_res_unit = 2k
#默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费
#查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
#如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。
#查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。
#查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

default-storage-engine = MyISAM
#default_table_type = InnoDB

thread_stack = 192K
#设置MYSQL每个线程的堆栈大小,默认值足够大,可满足普通操作。可设置范围为128K至4GB,默认为192KB。

transaction_isolation = READ-COMMITTED
# 设定默认的事务隔离级别.可用的级别如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-读未提交2.READ COMMITTE-读已提交3.REPEATABLE READ -可重复读4.SERIALIZABLE -串行

tmp_table_size = 256M
# tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一 个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增 加 tmp_table_size 值。如果超过该值,则会将临时表写入磁盘。
max_heap_table_size = 256M
long_query_time = 2
log_long_format
log-slow-queries=/data/3306/slow-log.log
#log-bin = /data/3306/mysql-bin
log-bin
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M

expire_logs_days = 7
key_buffer_size = 2048M
#批定用于索引的缓冲区大小,增加它可以得到更好的索引处理性能,对于内存在4GB左右的服务器来说,该参数可设置为256MB或384MB。

read_buffer_size = 1M
# MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变 量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

read_rnd_buffer_size = 16M
# MySql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首 先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当 设置该值,以避免内存开销过大。

bulk_insert_buffer_size = 64M
#批量插入数据缓存大小,可以有效提高插入效率,默认为8M

myisam_sort_buffer_size = 128M
# MyISAM表发生变化时重新排序所需的缓冲

myisam_max_sort_file_size = 10G
# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
# 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)

myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
# 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.

myisam_recover
#自动检查和修复没有适当关闭的 MyISAM 表
skip-name-resolve
lower_case_table_names = 1

server-id = 1

innodb_additional_mem_pool_size = 16M
#这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小,类似于Oracle的library cache。这不是一个强制参数,可以被突破。

innodb_buffer_pool_size = 2048M
# 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置 下运行的可以,然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起 来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的 规则有 — 如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size 设置的太大了

innodb_data_file_path = ibdata1:1024M:autoextend
#表空间文件 重要数据

innodb_file_io_threads = 4
#文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。

innodb_thread_concurrency = 8
#服务器有几个CPU就设置为几,建议用默认设置,一般为8.

innodb_flush_log_at_trx_commit = 2
# 如果将此参数设置为1,将在每次提交事务后将日志写入磁盘。为提供性能,可以设置为0或2,但要承担在发生故障时丢失数据的风险。设置为0表示事务日 志写入日志文件,而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志,但日志文件每次刷新到磁盘一次。

innodb_log_buffer_size = 16M
#此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据.MySQL开发人员建议设置为1-8M之间

innodb_log_file_size = 128M
#此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3
#为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M

innodb_max_dirty_pages_pct = 90
#推荐阅读 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html
# Buffer_Pool中Dirty_Page所占的数量,直接影响InnoDB的关闭时间。参数 innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page在Buffer_Pool中所占的比率,而且幸运的是 innodb_max_dirty_pages_pct是可以动态改变的。所以,在关闭InnoDB之前先将 innodb_max_dirty_pages_pct调小,强制数据块Flush一段时间,则能够大大缩短 MySQL关闭的时间。

innodb_lock_wait_timeout = 120
# InnoDB 有其内置的死锁检测机制,能导致未完成的事务回滚。但是,如果结合InnoDB使用MyISAM的lock tables 语句或第三 方事务引擎,则InnoDB无法识别死锁。为消除这种可能性,可以将innodb_lock_wait_timeout设置为一个整数值,指 示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)

innodb_file_per_table = 0
#独享表空间(关闭)

[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
log-error=/data/3306/mysql_oldboy.err
pid-file=/data/3306/mysqld.pid

#补充
#wait_timeout = 10
#指定一个请求的最大连接时间,对于4GB左右的内存服务器来说,可以将其设置为5-10。
#skip_networking
#开启该选可以彻底关闭MYSQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MYSQL数据库服务器的,则不要开启该选项,否则将无法正常连接。

#log-queries-not-using-indexes

via:http://www.linuxyw.com/a/shujuku/20130506/216.html

mysql中间件研究(Atlas,cobar,TDDL)

via:http://www.guokr.com/blog/475765/

主要是针对Atlas进行了一些修补

mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡,读写分离,failover等,但其不支持大数据量的分库分表且性能较差。如主库当机,从库也不能查询等。

下面介绍几款能代替其的mysql开源中间件产品,Atlas,cobar,tddl,让我们看看它们各自有些什么优点和新特性吧。

Atlas

Atlas是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上,对其进行了优化,增加了一些新的功能特性。

360内部使用Atlas运行的mysql业务,每天承载的读写请求数达几十亿条。

Altas架构:
Atlas是一个位于应用程序与MySQL之间,它实现了MySQL的客户端与服务端协议,作为服务端与应用程序通讯,同时作为客户端与MySQL通讯。它对应用程序屏蔽了DB的细节,同时为了降低MySQL负担,它还维护了连接池。

以下是一个可以参考的整体架构,LVS前端做负载均衡,两个Altas做HA,防止单点故障。

Altas的一些新特性:
1.主库宕机不影响读
主库宕机,Atlas自动将宕机的主库摘除,写操作会失败,读操作不受影响。从库宕机,Atlas自动将宕机的从库摘除,对应用没有影响。在mysql官方的proxy中主库宕机,从库亦不可用。
2.通过管理接口,简化管理工作,DB的上下线对应用完全透明,同时可以手动上下线。
图1是手动添加一台从库的示例。
图1

3.自己实现读写分离
(1)为了解决读写分离存在写完马上就想读而这时可能存在主从同步延迟的情况,Altas中可以在SQL语句前增加 /*master*/ 就可以将读请求强制发往主库。
(2)如图2中,主库可设置多项,用逗号分隔,从库可设置多项和权重,达到负载均衡。
图2

4.自己实现分表(图3)
(1)需带有分表字段。
(2)支持SELECT、INSERT、UPDATE、DELETE、REPLACE语句。
(3)支持多个子表查询结果的合并和排序。
图3

这里不得不吐槽Atlas的分表功能,不能实现分布式分表,所有的子表必须在同一台DB的同一个database里且所有的子表必须事先建好,Atlas没有自动建表的功能。
5.之前官方主要功能逻辑由使用lua脚本编写,效率低,Atlas用C改写,QPS提高,latency降低。
6.安全方面的提升
(1)通过配置文件中的pwds参数进行连接Atlas的用户的权限控制。
(2)通过client-ips参数对有权限连接Atlas的ip进行过滤。
(3)日志中记录所有通过Altas处理的SQL语句,包括客户端IP、实际执行该语句的DB、执行成功与否、执行所耗费的时间 ,如下面例子(图4)。
图4

7.平滑重启
通过配置文件中设置lvs-ips参数实现平滑重启功能,否则重启Altas的瞬间那些SQL请求都会失败。该参数前面挂接的lvs的物理网卡的ip,注意不是虚ip。平滑重启的条件是至少有两台配置相同的Atlas且挂在lvs之后。
8.支持事务
9.目前还不支持prepare特性,即将支持

source:https://github.com/Qihoo360/Atlas

alibaba.cobar

Cobar是阿里巴巴(B2B)部门开发的一种关系型数据的分布式处理系统,它可以在分布式的环境下看上去像传统数据库一样为您提供海量数据服务。那么具体说说我们为什么要用它,或说cobar--能干什么?以下是我们业务运行中会存在的一些问题:
1.随着业务的进行数据库的数据量和访问量的剧增,需要对数据进行水平拆分来降低单库的压力,而且需要高效且相对透明的来屏蔽掉水平拆分的细节。
2.为提高访问的可用性,数据源需要备份。
3.数据源可用性的检测和failover。
4.前台的高并发造成后台数据库连接数过多,降低了性能,怎么解决。
针对以上问题就有了cobar施展自己的空间了,cobar中间件以proxy的形式位于前台应用和实际数据库之间,对前台的开放的接口是mysql通信协议。将前台SQL语句变更并按照数据分布规则转发到合适的后台数据分库,再合并返回结果,模拟单库下的数据库行为。

Cobar应用举例
应用架构:

应用介绍:
1.通过Cobar提供一个名为test的数据库,其中包含t1,t2两张表。后台有3个MySQL实例(ip:port)为其提供服务,分别为:A,B,C。
2.期望t1表的数据放置在实例A中,t2表的数据水平拆成四份并在实例B和C中各自放两份。t2表的数据要具备HA功能,即B或者C实例其中一个出现故障,不影响使用且可提供完整的数据服务。
cabar优点总结:
1.数据和访问从集中式改变为分布:
(1)Cobar支持将一张表水平拆分成多份分别放入不同的库来实现表的水平拆分
(2)Cobar也支持将不同的表放入不同的库
(3) 多数情况下,用户会将以上两种方式混合使用
注意!:Cobar不支持将一张表,例如test表拆分成test_1,test_2, test_3.....放在同一个库中,必须将拆分后的表分别放入不同的库来实现分布式。
2.解决连接数过大的问题。
3.对业务代码侵入性少。
4.提供数据节点的failover,HA:
(1)Cobar的主备切换有两种触发方式,一种是用户手动触发,一种是Cobar的心跳语句检测到异常后自动触发。那么,当心跳检测到主机异常,切换到备机,如果主机恢复了,需要用户手动切回主机工作,Cobar不会在主机恢复时自动切换回主机,除非备机的心跳也返回异常。
(2)Cobar只检查MySQL主备异常,不关心主备之间的数据同步,因此用户需要在使用Cobar之前在MySQL主备上配置双向同步。
cobar缺点:
开源版本中数据库只支持mysql,并且不支持读写分离。
source:http://code.alibabatech.com/wiki/display/cobar/Home

TDDL

淘宝根据自己的业务特点开发了TDDL(Taobao Distributed Data Layer 外号:头都大了 ©_Ob)框架,主要解决了分库分表对应用的透明化以及异构数据库之间的数据复制,它是一个基于集中式配置的 jdbc datasource实现,具有主备,读写分离,动态数据库配置等功能。
TDDL所处的位置(tddl通用数据访问层,部署在客户端的jar包,用于将用户的SQL路由到指定的数据库中):

淘宝很早就对数据进行过分库的处理, 上层系统连接多个数据库,中间有一个叫做DBRoute的路由来对数据进行统一访问。DBRoute对数据进行多库的操作、数据的整合,让上层系统像操作一个数据库一样操作多个库。但是随着数据量的增长,对于库表的分法有了更高的要求,例如,你的商品数据到了百亿级别的时候,任何一个库都无法存放了,于是分成2个、4个、8个、16个、32个……直到1024个、2048个。好,分成这么多,数据能够存放了,那怎么查询它?这时候,数据查询的中间件就要能够承担这个重任了,它对上层来说,必须像查询一个数据库一样来查询数据,还要像查询一个数据库一样快(每条查询在几毫秒内完成),TDDL就承担了这样一个工作。在外面有些系统也用DAL(数据访问层) 这个概念来命名这个中间件。
下图展示了一个简单的分库分表数据查询策略:

主要优点:
1.数据库主备和动态切换
2.带权重的读写分离
3.单线程读重试
4.集中式数据源信息管理和动态变更
5.剥离的稳定jboss数据源
6.支持mysql和oracle数据库
7.基于jdbc规范,很容易扩展支持实现jdbc规范的数据源
8.无server,client-jar形式存在,应用直连数据库
9.读写次数,并发度流程控制,动态变更
10.可分析的日志打印,日志流控,动态变更
TDDL必须要依赖diamond配置中心(diamond是淘宝内部使用的一个管理持久配置的系统,目前淘宝内部绝大多数系统的配置,由diamond来进行统一管理,同时diamond也已开源)。
TDDL动态数据源使用示例说明:http://rdc.taobao.com/team/jm/archives/1645
diamond简介和快速使用:http://jm.taobao.org/tag/diamond%E4%B8%93%E9%A2%98/
TDDL源码:https://github.com/alibaba/tb_tddl
TDDL复杂度相对较高。当前公布的文档较少,只开源动态数据源,分表分库部分还未开源,还需要依赖diamond,不推荐使用。
终其所有,我们研究中间件的目的是使数据库实现性能的提高。具体使用哪种还要经过深入的研究,严谨的测试才可决定。

用php来备份mysql

#将所有备份文件备份到指定的目录,如/backup/mysql_data_backup
mkdir /backup/mysql_data_backup -p
cd /backup/mysql_data_backup
wget http://www.4wei.cn/files/2013/08/backmysql.txt -O backmysql.php
chmod +x backmysql.php
crontab -e

添加一行任务
0 13 * * * /usr/bin/php /backup/mysql_data_backup/backmysql.php

表示在每天晚上0点13分用php执行备份命令

#!/usr/bin/php
< ?php

//产生保存目录
$path = dirname(__FILE__) . '/' .date("Ym");
$filename = sprintf("%s/%s.sql.gz", $path, date("YmdHis"));

if(!is_dir($path))
        mkdir($path);

//导出并压缩所有数据库
$cmd = sprintf("/usr/bin/mysqldump -uroot -ppassword --all-databases | /bin/gzip > %s", $filename);
echo "backuping...\n";
`$cmd`;
echo "backup done.\n";

MySQL优化分库分表,为什么要分表,分表以后如何进行排序查询,业务如何设计?

昨天面试新人的时候,遇到了这么一个问题,按照自己的想法大体聊了一些,但大多是感性的,并没有完整的了解why and how.

今天查了一些相关的资料,包括《MySQL性能调优与架构设计》、《高性能Mysql》,慢慢的整体理解,请大家指正。

之一,为什么要分表?

分表,按形式,有水平分表和主附分表。
水平分表常见于按ID取模或者按日期将相同表结构的内容散列到不同的表上,主附分表常见于有对应关系的多张表,通过主外键进行关联。

1,解决磁盘系统最大文件限制

如大家所知,各文件系统对单个文件大小的限制是不一样的。
虽然在现代网站架构设计中,这个问题基本上不用考虑,但在面试时,如果能回答上来这点,可以让体现出较不错的计算机基础功底。
FAT16(最大分区2GB,最大文件2GB ,最大容量?)
FAT32(最大分区32GB,最大容量2TB,最大文件32G)
NTFS(最大分区2TB,最大容量,最大文件2TB)
ext3(最大文件大小: 2TB,最大文件极限: 仅受文件系统大小限制,最大分区/文件系统大小: 4TB,最大文件名长度: 255 字符)

2,减少增量数据写入时的锁对查询的影响,减少长时间查询造成的表锁,影响写入操作等锁竞争的情况
数据越来越多,查询逻辑比较复杂的情况下,可能造成表锁和行锁,会影响到正常的读写需求,如果把数据进行分表,典型的情况有按ID和日期进行分表,读取旧数据和写入新数据的操作在不同的表里,这样就可以避免单张表间产生的锁竞争,节省排队的时间开支,增加呑吐量。
需要注意的是,这里说的减少锁和排队的时间开支,并没有确切的说减少了cpu的运算开支,因为分表以后的运算需求还可能在同一台设备上。如果使用了分库分表则能取得更好的运算速度提升。

3,同2,由于单表数量下降,常见的查询操作由于减少了需要扫描的记录,使得单表单次查询所需的检索行数变少,减少了磁盘IO,时延变短。

windows下定时备份mysql的计划任务

mkdir E:\mysql\backup_day\%date:~0,10%
cd E:\mysql\backup_day\%date:~0,10%
mysqldump -uroot -ppassword dbname|gzip > dbname.%time:~0,2%.sql

%date:~0,10%和%time:~0,2%分别是日期和时间和格式化,格式化成数字,以生成按日期目录生成的文件

如果是使用了xampp这样的集成环境,mysqldump的路径请手工指定。gzip 命令是我下载的gzip for windows,以便对数据进行压缩保存。

mysql 5.5的主从配置及容错方案

主服配置文件
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-do-db = master_slave

//重启主服
service mysqld restart

//查看主服状态,并记下bin log pos,主服的所有操作均记录在Binlog中
show master status;

从服配置文件
server_id = 2
log_bin = mysql-bin
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-do-db = master_slave

//重启从服
service mysqld restart

//设定主服并启动同步
mysql> change master to master_host='192.168.0.1', master_user='root', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;
mysql> start slave;
mysql> show slave status;

mysql误清数据恢复手记

一直没想到玛雅人跟中国砖家一样不靠谱,本想着趁着世界末日洗手不干了,把钱花光,把信用卡刷爆,把数据库清了。

呜呜。

某库有表40余,所有表执行了truncate以后,5分钟后发现问题,停止业务开始准备恢复数据。

1,确定误操作的时间点
2,确定上次完整备份的时间点

误清操作前五分钟应该没有什么业务量进来,加上数据不重要,将数据恢复到误操作的前几分钟就行,从上次备份的SQL文件顶部,找到了备份操作生成的时间戳,最后,确定了需要恢复数据的时间节点

Date: 2012-12-21 21:20:40
Date: 2012-12-24 20:20:00

进入mysql服务器,找到Binlog文件列表,确定在2012-12-21 21:20:40到2012-12-24 20:20:00时间段内,产生了3个Binlog,分别为2G,1G,10M:

mysql_binglog.00000191
mysql_binglog.00000192
mysql_binglog.00000193

恢复数据的方法有多种,我们使用了一个比较傻的版本。

有直接恢复数据的方法,如

mysqlbinlog --start-position=134 --stop-position=330 test mysql_binglog.00000191 | mysql -uroot -p

我们使用的方法是,先将Binlog拆成SQL,确认SQL没有问题,然将需要的SQL的恢复进Mysql

mysqlbinlog --start-date="2012-12-21 21:20:40" --stop-date="2012-12-24 20:20:00" mysql_binglog.000191 > back_up_1.sql
mysqlbinlog --start-date="2012-12-21 21:20:40" --stop-date="2012-12-24 20:20:00" mysql_binglog.000192 > back_up_2.sql
mysqlbinlog --start-date="2012-12-21 21:20:40" --stop-date="2012-12-24 20:20:00" mysql_binglog.000193 > back_up_3.sql

二转十以后,发现第三个SQL为空,前两个备份文件里,包含了服务器上所有数据库的增删改SQL,在确定两个SQL文件中没有出现truncate 和 delete *以后,使用导入命令恢复数据

首先将备份的数据导入数据库,再将binlog拆出来的SQL依次导入

mysql -u root -p123456 -h192.168.0.1 -f --database=test --default-character-set=utf8 < back_up_2.sql

这个命令里,`--default-character-set=utf8`是指定编码,`-f`是忽略错误,因为是全库的binlog,而我们只需要其中一个库的回滚,其它库不存在表,恢复失败也不在考虑范围内。

最后,将恢复完成的数据库整理并同步到线上。

以前的项目中,出现过单表清空的问题,当时采用的是拆分出Binlog以后,跑脚本将SQL中指定表的SQL拆分出来再导入数据库,不修改其它表的数据,写程序跑文件比较麻烦。

MySQL MyISAM和InnoDB引擎的写入速度优化比较,分页速度优化

以下的文章主要介绍的是MySQL MyISAM的引擎和InnoDB引擎的实际性能的比较,我们首先是通过MySQL数据库的表结构来出MySQL MyISAM的引擎和InnoDB引擎的实际性能的实际操作。

CREATE TABLE `myisam` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
CREATE TABLE `innodb` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

数据内容:

$name = “heiyeluren”;

$content = “MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎:· MyISAM管理非事务表。它提供高速存储和检索,以及全文搜索能力。MySQL MyISAM在所有MySQL配置里被支持,它是默认的存储引擎,除非你配置MySQL默认使用另外一个引擎。 ·

MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MySQL MyISAM一样,MEMORY和MERGE存储引擎处理非事务表,这两个引擎也都被默认包含在MySQL中。

释:MEMORY存储引擎正式地被确定为HEAP引擎。· InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所有MySQL 5.1二进制分发版里,你可以按照喜好通过配置MySQL来允许或禁止任一引擎。·EXAMPLE存储引擎是一个“存根”引擎,它不做什么。你可以用这个引擎创建表,但没有数据被存储于其中或从其中检索。这个引擎的目的是服务,在MySQL源代码中的一个例子,它演示说明如何开始编写新存储引擎。同样,它的主要兴趣是对开发者。”;

[插入数据-1] (innodb_flush_log_at_trx_commit=1)

MyISAM 1W:3/s
nnoDB 1W:219/s
MyISAM 10W:29/s
nnoDB 10W:2092/s
MySQL MyISAM 100W:287/s
InnoDB 100W:没敢测试

[插入数据-2] (innodb_flush_log_at_trx_commit=0)

MyISAM 1W:3/s
InnoDB 1W:3/s
MyISAM 10W:30/s
InnoDB 10W:29/s
MyISAM 100W:273/s
InnoDB 100W:423/s

[插入数据3] (innodb_buffer_pool_size=1024M)

InnoDB 1W:3/s
InnoDB 10W:33/s
InnoDB 100W:607/s

[插入数据4] (innodb_buffer_pool_size=256M, innodb_flush_log_at_trx_commit=1, set autocommit=0)

InnoDB 1W:3/s
InnoDB 10W:26/s
InnoDB 100W:379/s

[MySQL 配置文件] (缺省配置)

# MySQL Server Instance Configuration File
[client]
port=3306
[mysql]
default-character-set=gbk
[mysqld]
port=3306
basedir=”C:/mysql50/”
datadir=”C:/mysql50/Data/”
default-character-set=gbk
default-storage-engine=INNODB
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
max_connections=100 

query_cache_size=0
table_cache=256
tmp_table_size=50M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=100M
key_buffer_size=82M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=159M
innodb_log_file_size=80M
innodb_thread_concurr

以上的相关内容就是对MySQL MyISAM的介绍,望你能有所收获。

总结

可以看出在MySQL 5.0里面,MyISAM和InnoDB存储引擎性能差别并不是很大,针对InnoDB来说,影响性能的主要是 innodb_flush_log_at_trx_commit 这个选项,如果设置为1的话,那么每次插入数据的时候都会自动提交,导致性能急剧下降,应该是跟刷新日志有关系,设置为0效率能够看到明显提升,当然,同样你可以SQL中提交“SET AUTOCOMMIT = 0”来设置达到好的性能。另外,还听说通过设置innodb_buffer_pool_size能够提升InnoDB的性能,但是我测试发现没有特别明显的提升。

基本上我们可以考虑使用InnoDB来替代我们的MyISAM引擎了,因为InnoDB自身很多良好的特点,比如事务支持、存储过程、视图、行级锁定等等,在并发很多的情况下,相信InnoDB的表现肯定要比MyISAM强很多,当然,相应的在my.cnf中的配置也是比较关键的,良好的配置,能够有效的加速你的应用。

如果不是很复杂的Web应用,非关键应用,还是可以继续考虑MyISAM的,这个具体情况可以自己斟酌。

硬件配置
CPU : AMD2500+ (1.8G)
内存: 1G/现代
硬盘: 80G/IDE

软件配置
OS : Windows XP SP2
SE : PHP5.2.1
DB : MySQL5.0.37
Web: IIS6

mysql分页优化方法拾遗
在我的老双至强2.4G的ibm服务器上,37万小文本数据表执行select count(*)时间长达3.8秒(innodb存储引擎),已经超出了可以忍受的范围(页面总执行时间不得超出1秒),因此开始寻找相关优化方法。

count在myisam和innodb下面的差异性
1.myisam保存表的总行数,因此count(*)并且无where子句,很快会返回表的总行数
2.myisam保存表的总行数,利用count(column)并且无where子句,并且此column不为null,很快会返回表的总行数
3.myisam保存表的总行数,利用count(column)并且无where子句,并且此column可以为null,mysql会对表进行全表或全索引扫描来确定行数
4.innodb查询count(*),count(column(not null)),count(column(may be null))并且无where子句,mysql会对表进行全表或全索引扫描来确定行数
5.myisam和innodb查询count(*),count(column(not null)),count(column(may be null))并且存在where子句,mysql会对表进行索引扫描(如果列上有索引),速度也比较快

来源:http://blog.sina.com.cn/s/blog_5b5460eb0100o4s6.html

在使用mysql数据表的分页程序里面,select count是效率的瓶颈所在。部分人使用避免使用不显示数据总数来规避这个问题,比如点点,永远只有下一页的分页链接。这种方式在我这里行不通,使用的extjs框架的gridpanel控件缺乏记录总数无法分页。另一种方式是新建一个数据表统计表记录数,在表新增和删除时对该记录同时做更新。这种方式或者可行。

记录总数的问题解决了,翻页到超过1万页之后速度又变得极慢(每页20条)。mysql分页使用limit offset,length,在10万记录之前效率可观,sql执行时间基本在1秒内(主键索引),超过10万条后效率成数量级下降。

查阅资料得以下几种优化方式:
1、索引列大于法
SELECT id FROM table WHERE id>(SELECT id FROM table ORDER BY id LIMIT $start,1) ORDER BY id LIMIT $length;
30万数据时,加order by效率提高不多(1秒左右),不加order by 效率提高一半
10万数据时,效率提高明显。

2、超过半数逆序分页法。
意为判断分页开始id是否超过总数的一半,如果超过一半则反写sql,减小了LIMIT里面的offset值,从而提高效率。
30万数据,假如原SQL语句是ORDER BY id LIMIT 200000,20,可改写为ORDER BY id DESC LIMIT 99980,20,效率提高超过一个数量级。

3、使用临时表缓存索引列,分页时使用临时表,获取到id用IN子查询。
SELECT id FROM table WHERE id IN(SELECT id FROM table_tmp LIMIT $start,$length);

经过再三考虑,我最后把这部分的优化暂时搁置了,太他娘的浪费时间了。

参考文献:
http://blog.hexu.org/archives/630.shtml
http://0e2.net/post/1555.html

mysql批量kill locked进度方法

select concat('kill ',id,';') from information_schema.processlist where user='root';
+------------------------+
| concat('kill ',id,';')
+------------------------+
| kill 3101;
| kill 2946;
+------------------------+
2 rows in set (0.00 sec)

mysql> select concat('kill ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
query ok, 2 rows affected (0.00 sec)

mysql> source /tmp/a.txt;
query ok, 0 rows affected (0.00 sec)

方法二,直接进入mysql找到information_schema的processlist全选删除就可以了。
方法三,进入mysql,show processlist; kill id

MySQL高效分页解决方案集

很久以前的一次面试中,被面试官问到这个问题,由于平时用到的分页方法不多,只从索引、分表、使用子查询精准定位偏移以外,没有使用到其它方法。
后来在看其它博客看到了一些不同的方案,也一直没有整理。今天有时间,整理出来,分享给大家。

一,最常见MYSQL最基本的分页方式:

select * from content order by id desc limit 0, 10

在中小数据量的情况下,这样的SQL足够用了,唯一需要注意的问题就是确保使用了索引。随着数据量的增加,页数会越来越多,查看后几页的SQL就可能类似:

select * from content order by id desc limit 10000, 10

一言以蔽之,就是越往后分页,LIMIT语句的偏移量就会越大,速度也会明显变慢。
此时,我们可以通过2种方式:
一,子查询的分页方式来提高分页效率,飘易用的SQL语句如下:

SELECT * FROM `content` WHERE id (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。(via)通过explain SQL语句发现:子查询使用了索引!

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY content range PRIMARY PRIMARY 4 NULL 6264 Using where
2 SUBQUERY content index NULL PRIMARY 4 NULL 27085 Using index

经过飘易的实测,使用子查询的分页方式的效率比纯LIMIT提高了14-20倍!
二,JOIN分页方式

select * FROM `content` AS t1
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id

经过我的测试,join分页和子查询分页的效率基本在一个等级上,消耗的时间也基本一致。explain SQL语句:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

三,使用MYSQL的FOUND_ROWS()函数
Mysql FOUND_ROWS() 函数结合SQL_CALC_FOUND_ROWS在SELECT中可以得到两个结果:
1. 得到Limit的内容
2. 得到去除Limit以后所有行数

SELECT语句中经常可能用LIMIT限制返回行数。有时候可能想要知道如果没有LIMIT会返回多少行,但又不想再执行一次相同语句。那么,在SELECT查询中包含SQL_CALC_FOUND_ROWS选项,然后执行FOUND_ROWS()就可以了:

select SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

其中SQL_CALC_FOUND_ROWS 告诉Mysql将sql所处理的行数记录下来,FOUND_ROWS() 则取到了这个纪录。 虽然也是两个语句,但是只执行了一次主查询,所以效率比原来要高很多。

1. 如果在前一条语句中使用SQL_CALC_FOUND_ROWS选项,FOUND_ROWS()将返回第一条语句没有LIMIT时返回的行数。
2. 如果在前一条语句中没有使用SQL_CALC_FOUND_ROWS选项,FOUND_ROWS()将返回前一条语句实际返回的行数。
如果使用 SELECT SQL_CALC_FOUND_ROWS,MySQL必须计算所有结果集的行数。尽管这样,总比再执行一次不使用LIMIT的查询要快多了吧,因为那样结果集要返回客户端滴。(另外:应该不单是没有将结果集返回的原因,还有原因可能是比如LIKE之类比较费劲的SQL不需要再去劳累一次。)

-- 注意下面语句中的条件 LIKE
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE Name LIKE '%string%' id > 100 LIMIT 10;
SELECT FOUND_ROWS();

-- 上面语句等价于下面语句,但性能方面应该提升非常非常的明显:
SELECT COUNT(*) FROM tbl_name WHERE Name LIKE '%string%' ;
SELECT * FROM tbl_name WHERE Name LIKE '%string%' id > 100 LIMIT 10;

参考博客:
http://blog.hexu.org/archives/1328.shtml
http://hi.baidu.com/thinkinginlamp/blog/item/17476d22d66876a14623e81d.html
http://www.piaoyi.org/php/MySQL-SUBQUERY-index.html

Navicat 9.1、10.0 简体中文最新版,注册码(For Mysql)

Navicat属于偶的必备开发工具,最新版的自动提示,SQL格式化比较好用。

今天测试过Navicat 9.1.11,注册码可以使用。

下载地址:
中文版 Navicat mysql 9.x
http://download2.navicat.com/download/navicat091_mysql_cs.exe
http://download2.navicat.com/download/navicat091_mysql_cs.tar.gz
NAVL-KSG4-K8D8-8TV6

Navicat mysql 10.0.x
http://download2.navicat.com/download/navicat100_mysql_cs.exe
NAVK-BPRI-EY6D-AXLK

中文版 Navicat premium 10.0.x
http://download2.navicat.com/download/navicat100_premium_cs.exe
NAVE-Q53X-JVQV-GHZ2

英文版 Navicat premium 10.0.x
http://download2.navicat.com/download/navicat100_premium_en.exe
NAVI-TKBI-77LR-PJ2Z

“InnoDB”和“MyISAM”的应用详解

  InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能。 继续阅读“InnoDB”和“MyISAM”的应用详解

Mysql插入语句的5种写法。

  1. insert into tablename (id,name) value ('', 'xxx');
  2. insert into tablename (id,name) values ('', 'xxxxx');
  3. insert into tablename (id, name) value ('','xxx'),('','xxxxxxx');
  4. insert into tablename (id, name) values ('','xxx'),('','xxxxxxx');
  5. insert into tablename set id='id', name='xxxxx';

一般推荐第四种写法,这是SQL标准,在Oracle等其它数据库类型中通用性好,其它几种在Mysql App开发时,有特定的方便之处。

KB967723的补丁造成mysql频繁出现无法连接的解决办法

由于Windows2003打了KB967723升级包,造成mysql数据库频繁出现无法连接数据库问题。

网上的解决办法都说是卸载此补丁就能解决问题,哥按照指示也卸载了KB967723,但问题依然。

卸载补丁以后,打开cmd->systeminfo,还是能看到已安装KB967723的补丁,但在控制面板中却打不到这个补丁的信息。

于是,接着使用修改注册表的办法。目前网上的解决方法普遍有误,搞了好几次才

微软提供的最新解决方案:此部分方法或任务包含告诉您如何修改注册表的步骤。 但是,如果错误地修改注册表,就可能发生严重的问题。 因此,请确保认真执行这些步骤。 添加的保护注册表之前先备份您修改它。 然后,发生问题时可以还原注册表。 有关如何备份和还原在注册表的详细信息,单击下面的文章编号,以查看 Microsoft 知识库中相应:322756 (http://support.microsoft.com/kb/322756/ ) 如何备份和还原在 Windows 注册表
默认最大的临时 TCP端口的数是 5000 适用于一节中包含的产品中。 在这些产品中添加一个新参数。 要提高临时端口的上限,请按照下列步骤操作:

  1. 启动注册表编辑器。(开始—运行—REGEDIT)
  2. 在的注册表中找到以下子项,然后单击 参数 :HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
  3. 在右边单击 新建 然后添加下面的注册表项:数值名称: MaxUserPort
    值类型: DWORD
    值数据: 65534 (注意是十进制)
    有效范围: 5000-65534 (十进制)
    默认值: 0×1388 (5000 十进制)
    说明: 此参数将控制程序从系统请求任何可用的用户端口时使用的最大端口数。 通常,1024 的值和包含的 5000 之间分配临时的 (短) 端口。
  4. 退出注册表编辑器,并重新重启计算机

我给各位截了个小图,如下:

重启服务器后,运行5个小时的Mysql查询,未出现错误,问题解决。