搜档网
当前位置:搜档网 › 我的mysql+优化日记

我的mysql+优化日记

我的mysql+优化日记
我的mysql+优化日记

我的mysql 优化日记

时间:2009-03-04 17:00:33 来源:https://www.sodocs.net/doc/6d11693965.html,/?p=431 作者:

同时在线访问量继续增大对于1G内存的服务器明显感觉到吃力严重时甚至每天都会死机或者时不时的服务器卡一下这个问题曾经困扰了我半个多月MySQL使用是很具伸缩性的算法,因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。

安装好mysql后,配制文件应该在/usr/local/mysql/share/mysql目录中,配制文件有几个,有https://www.sodocs.net/doc/6d11693965.html,f https://www.sodocs.net/doc/6d11693965.html,f https://www.sodocs.net/doc/6d11693965.html,f https://www.sodocs.net/doc/6d11693965.html,f,不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文件了。

一般的情况下,my- https://www.sodocs.net/doc/6d11693965.html,f这个配制文件就能满足我们的大多需要;一般我们会把配置文件拷贝到/etc/https://www.sodocs.net/doc/6d11693965.html,f 只需要修改这个配置文件就可以了,使用mysqladmin variables extended-status –u root –p 可以看到目前的参数,有3个配置参数是最重要的,即

key_buffer_size,query_cache_size,table_cache。

key_buffer_size只对MyISAM表起作用,

key_buffer_size 指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点这个数字是远远不够的,通过检查状态值 Key_read_requests和Key_reads,可以知道

key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql,以下的状态值都是本人通过phpmyadmin获得的实例分析:

这个服务器已经运行了20天

key_buffer_size – 128M

key_read_requests – 650759289

key_reads - 79112

比例接近1:8000 健康状况非常好

另外一个估计key_buffer_size的办法把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。

从4.0.1开始,MySQL提供了查询缓冲机制。使用查询缓冲,MySQL将SELECT

语句和查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到

238%的效率。

通过调节以下几个参数可以知道query_cache_size设置得是否合理

Qcache inserts

Qcache hits

Qcache lowmem prunes

Qcache free blocks

Qcache total blocks

Qcache_lowmem_prunes 的值非常大,则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小 Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多

query_cache_type指定是否使用查询缓冲

我设置:

query_cache_size = 32M

query_cache_type= 1

得到如下状态值:

Qcache queries in cache 12737 表明目前缓存的条数

Qcache inserts 20649006

Qcache hits 79060095 看来重复查询率还挺高的

Qcache lowmem prunes 617913 有这么多次出现缓存过低的情况

Qcache not cached 189896

Qcache free memory 18573912 目前剩余缓存空间

Qcache free blocks 5328 这个数字似乎有点大碎片不少

Qcache total blocks 30953

如果内存允许32M应该要往上加点

table_cache 指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于 table_cache,并且

opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,

从而造成性能不稳定或者连接失败。

对于有1G内存的机器,推荐值是128-256。

笔者设置table_cache = 256

得到以下状态:

Open tables 256

Opened tables 9046

虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,已经运行了20天,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值那就要考虑增大

table_cache。

如果你不需要记录2进制log 就把这个功能关掉,注意关掉以后就不能恢复出问题前的数据了,需要您手动备份,二进制日志包含所有更新数据的语句,其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外,如果做同步复制( Replication )的话,也需要使用二进制日志传送修改情况。

log_bin指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引,每次启动服务时,都会重新生成一个新的二进制文件。此外,使用log-bin-index可以指定索引文件;使用

binlog-do-db可以指定记录的数据库;使用binlog-ignore-db 可以指定不记录的数据库。注意的是:binlog-do-db和binlog-ignore-db一次只指定一个数据库,指定多个数据库需要多个语句。而且,MySQL会将所有的数据库名称改成小写,在指定数据库时必须全部使用小写名字,否则不会起作用。

关掉这个功能只需要在他前面加上#号

#log-bin

开启慢查询日志( slow query log ) 慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询,如果需要,还可以记录不使用索引的记录。下面是一个慢查询日志的例子:

开启慢查询日志,需要设置参数log_slow_queries、long_query_times、

log-queries-not-using-indexes。

log_slow_queries 指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值,缺省是10秒。log-

queries-not-using-indexes是4.1.0以后引入的参数,它指示记录不使用索引的查询。笔者设置 long_query_time=10

笔者设置:

sort_buffer_size = 1M

max_connections=120

wait_timeout =120

back_log=100

read_buffer_size = 1M

thread_cache=32

interactive_timeout=120

thread_concurrency = 4

参数说明:

back_log

要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log

高于你的操作系统的限制将是无效的。

max_connections

并发连接数目最大,120 超过这个值就会自动恢复,出了问题能自动解决

thread_cache

没找到具体说明,不过设置为32后 20天才创建了400多个线程而以前一天就创建了上千个线程所以还是有用的

thread_concurrency

#设置为你的cpu数目x2,例如,只有一个cpu,那么thread_concurrency=2

#有2个cpu,那么thread_concurrency=4

skip-innodb

#去掉innodb支持

代码:

# Example MySQL config file for medium systems.

#

# This is for a system with little memory (32M - 64M) where MySQL plays

# an important part, or systems up to 128M where MySQL is used together with

# other programs (such as a web server)

#

# You can copy this file to

# /etc/https://www.sodocs.net/doc/6d11693965.html,f to set global options,

# mysql-data-dir/https://www.sodocs.net/doc/6d11693965.html,f to set server-specific options (in this

# installation this directory is /var/lib/mysql) or

# ~/https://www.sodocs.net/doc/6d11693965.html,f to set user-specific options.

#

# In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option.

# The following options will be passed to all MySQL clients

[client]

#password = your_password

port = 3306

socket = /tmp/mysql.sock

#socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

#socket = /var/lib/mysql/mysql.sock

skip-locking

key_buffer = 128M

max_allowed_packet = 1M

table_cache = 256

sort_buffer_size = 1M

net_buffer_length = 16K

myisam_sort_buffer_size = 1M

max_connections=120

#addnew config

wait_timeout =120

back_log=100

read_buffer_size = 1M

thread_cache=32

skip-innodb

skip-bdb

skip-name-resolve

join_buffer_size=512k

query_cache_size = 32M

interactive_timeout=120

long_query_time=10

log_slow_queries= /usr/local/mysql4/logs/slow_query.log

query_cache_type= 1

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 4

#end new config

# Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless!

#

#skip-networking

# Replication Master Server (default)

# binary logging is required for replication

#log-bin

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1

# Replication Slave (comment out master section to use this)

#

# To configure this host as a replication slave, you can choose between # two methods :

#

# 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is:

#

# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,

# MASTER_USER=, MASTER_PASSWORD= ;

#

# where you replace , , by quoted strings and

# by the master's port number (3306 by default).

#

# Example:

#

# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

# MASTER_USER='joe', MASTER_PASSWORD='secret';

#

# OR

#

# 2) Set the variables below. However, in case you choose this method, then

# start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a https://www.sodocs.net/doc/6d11693965.html, file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the https://www.sodocs.net/doc/6d11693965.html, file, unless you shutdown # the slave server, delete https://www.sodocs.net/doc/6d11693965.html, and restart the slaver server. # For that reason, you may want to leave the lines below untouched

# (commented) and instead use CHANGE MASTER TO (see above)

#

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

#server-id = 2

#

# The replication master for this slave - required

#master-host =

#

# The username the slave will use for authentication when connecting # to the master - required

#master-user =

#

# The password the slave will authenticate with when connecting to

# the master - required

#master-password =

#

# The port the master is listening on.

# optional - defaults to 3306

#master-port =

#

# binary logging - not required for slaves, but recommended

#log-bin

# Point the following paths to different dedicated disks

#tmpdir = /tmp/

#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables

#bdb_cache_size = 4M

#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql/

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /var/lib/mysql/

#innodb_log_arch_dir = /var/lib/mysql/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 16M

#innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 5M

#innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL #safe-updates

[isamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

补充

优化table_cachetable_cache指定表高速缓存的大小。每当MySQL访问一个表

时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加 table_cache的值。如果你发现open_tables等于

table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache 的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。对于有1G内存的机器,推荐值是128-256。

案例1:该案例来自一个不是特别繁忙的服务器table_cache –512open_tables – 103opened_tables – 1273uptime – 4021421 (measured in seconds)该案例中table_cache似乎设置得太高了。在峰值时间,打开表的数目比table_cache 要少得多。

案例2:该案例来自一台开发服务器。table_cache – 64open_tables –

64opened-tables – 431uptime – 1662790 (measured in seconds)虽然

open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。案例3:该案例来自一个upderperforming的服务器table_cache –

64open_tables – 64opened_tables – 22423uptime – 19538该案例中

table_cache设置得太低了。虽然运行时间不到6小时,open_tables达到了最大值,opened_tables的值也非常高。这样就需要增加table_cache的值。优化key_buffer_sizekey_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和

Key_reads,可以知道key_buffer_size 设置是否合理。比例key_reads /

key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。key_buffer_size只对MyISAM 表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是 MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。对于

1G内存的机器,如果不使用 MyISAM表,推荐值是16M(8-64M)。

案例1:健康状况key_buffer_size –402649088 (384M)key_read_requests –597579931key_reads - 56188案例2:警报状态key_buffer_size – 16777216 (16M)key_read_requests – 597579931key_reads - 53832731案例1中比例低于1:10000,是健康的情况;案例2中比例达到1:11,警报已经拉响。

1)、back_log:要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。

back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列

大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。当你观察你的主机进程列表,发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时,就要加大 back_log 的值了。默认数值是50,我把它改为500。

(2)、 interactive_timeout:服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用

CLIENT_INTERACTIVE 选项的客户。默认数值是28800,我把它改为7200。

(3)、 key_buffer_size:索引块是缓冲的并且被所有的线程共享。

key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是8388600(8M),我的MySQL主机有2GB内存,所以我把它改为 402649088(400MB)。

(4)、max_connections:允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 Too many connections 错误。默认数值是100,我把它改为1024 。

(5)、record_buffer:每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072(128K),我把它改为16773120 (16M)

(6)、sort_buffer:每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M),我把它改为16777208 (16M)。

(7)、table_cache:为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64,我把它改为512。

(、 thread_cache_size:可以复用的保存在中的线程的数量。如果有,新的线

程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。通过比较 Connections 和Threads_created 状态的变量,可以看到这个变量的作用。我把它设置为 80。

(10)、 wait_timeout:服务器在关闭它之前在一个连接上等待行动的秒数。默认数值是28800,我把它改为7200。注:参数的调整可以通过修改 /etc/https://www.sodocs.net/doc/6d11693965.html,f 文件并重启 MySQL 实现。这是一个比较谨慎的工作,上面的结果也仅仅是我的一些看法,你可以根据你自己主机的硬件情况(特别是内存大小)进一步修改。

我从网上找到的,我刚看了一下,还算不错,发在这里,大家看看,最好有牛人补充完善然后,再整理整理!

======================================== 在Apache, PHP, MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分。对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数。下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化;二是MySQL自身(https://www.sodocs.net/doc/6d11693965.html,f)的优化。

(1) 服务器硬件对MySQL性能的影响

a) 磁盘寻道能力(磁盘I/O),以目前高转速SCSI硬盘(7200转/秒)为例,这种硬盘理论上每秒寻道7200次,这是物理特性决定的,没有办法改变。 MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以,通常认为磁盘I/O是制约MySQL性能的最大因素之一,对于日均访问量在100万PV

以上的Discuz!论坛,由于磁盘I/O的制约,MySQL的性能会非常低下!解决这一制约因素可以考虑以下几种解决方案:使用RAID-0+1磁盘阵列,注意不要尝试使用RAID-5,MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快;抛弃传统的硬盘,使用速度更快的闪存式存储设备。经过Discuz!公司技术工程的测试,使用闪存式存储设备可比传统硬盘速度高出6-10倍左右。

b) CPU 对于MySQL应用,推荐使用S.M.P.架构的多路对称CPU,例如:可以使用两颗Intel Xeon 3.6GHz的CPU。

c) 物理内存对于一台使用MySQL的Database Server来说,服务器内存建议不

要小于2GB,推荐使用4GB以上的物理内存。

(2) MySQL自身因素当解决了上述服务器硬件制约因素后,让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件 https://www.sodocs.net/doc/6d11693965.html,f中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。由于https://www.sodocs.net/doc/6d11693965.html,f文件的优化设置是与服务器硬件配置息息相关的,因而我们指定一个假想的服务器硬件环境:

CPU: 2颗Intel Xeon 2.4GHz 内存: 4GB DDR 硬盘: SCSI 73GB

下面,我们根据以上硬件配置结合一份已经优化好的https://www.sodocs.net/doc/6d11693965.html,f进行说明:

# vi /etc/https://www.sodocs.net/doc/6d11693965.html,f以下只列出https://www.sodocs.net/doc/6d11693965.html,f文件中[mysqld]段落中的内容,其他段落内容对MySQL运行性能影响甚微,因而姑且忽略。

[mysqld]

port = 3306

serverid = 1

socket = /tmp/mysql.sock

skip-locking

# 避免MySQL的外部锁定,减少出错几率增强稳定性。

skip-name-resolve禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!

back_log = 384指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求,该参数生效,主线程花费很短的时间检查连接并且启动一个新线程。

back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统

在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。

key_buffer_size = 256M

# key_buffer_size指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意:该参数值设置的过大反而会是服务器整体效率降低!

max_allowed_packet = 4M

thread_stack = 256K

table_cache = 128K

sort_buffer_size = 6M查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占!如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。

read_buffer_size = 4M读查询操作所能使用的缓冲区大小。和

sort_buffer_size一样,该参数对应的分配内存也是每连接独享!

join_buffer_size = 8M联合查询操作所能使用的缓冲区大小,和

sort_buffer_size一样,该参数对应的分配内存也是每连接独享!

myisam_sort_buffer_size = 64M

table_cache = 512

thread_cache_size = 64

query_cache_size = 64M指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察:

# > SHOW VARIABLES LIKE ‘%query_cache%’;

# > SHOW STATUS LIKE ‘Qcache%’;

# 如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。

tmp_table_size = 256M

max_connections = 768指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提示,则需要增大该参数值。

max_connect_errors = 10000000

wait_timeout = 10指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。

thread_concurrency = 8该参数取值为服务器逻辑CPU数量×2,在本例中,服务器有2颗物理CPU,而每颗物理CPU又支持H.T超线程,所以实际取值为4 × 2 = 8

skip-networking开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB 服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将

无法正常连接!

————————————————————————————————–my.ini配置建议:

table_cache=1024

物理内存越大,设置就越大.默认为2402,调到512-1024最佳

innodb_additional_mem_pool_size=4M

默认为2M

innodb_flush_log_at_trx_commit=1

(设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1)

innodb_log_buffer_size=2M

默认为1M

innodb_thread_concurrency=8

你的服务器CPU有几个就设置为几,建议用默认一般为8

key_buffer_size=256M

默认为218 调到128最佳

tmp_table_size=64M

默认为16M 调到64-256最挂

read_buffer_size=4M

默认为64K

read_rnd_buffer_size=16M

默认为256K

sort_buffer_size=32M

默认为256K

max_connections=1024

默认为1210

thread_cache_size=120

默认为60

query_cache_size=32M ————————————————————————————————–

以下是另一个的my.ini配置建议:

port=3306

default-character-set=latin1

default-storage-engine=INNODB

sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITU TION”

max_connections=120

query_cache_size=32M

#缓存数据表数量,设置这个参数可以参见系统状态中的 open_tables(表示当前打开的数据表总数) 和 opened_tables(表示所有打开的数据表总数)

table_cache=256

#临时表的大小

tmp_table_size=12M

#缓存可重用的线程数

thread_cache_size = 64

myisam_max_sort_file_size=100G

myisam_max_extra_sort_file_size=100G

myisam_sort_buffer_size=64M

# 这对MyISAM表来说非常重要。如果只是使用MyISAM表,可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载—#记住,MyISAM 表会使用操作系统的缓存来缓存数据,因此需要留出部分内存给它们,很多情况下数据比索引大多了。

key_buffer_size=128M

read_buffer_size=1M

read_rnd_buffer_size=512K

sort_buffer_size=1M

#这对innodb表来说非常重要

innodb_buffer_pool_size = 256M

#这取决于你需要的回复速度.128M这个数值是适当的恢复时间和良好性能之间的一个好的平衡.

innodb_log_file_size = 128M

#大多数情况4M足够,除非正将很大的blob数据导入到Innodb中可以增加一点.

innodb_log_buffer_size=4M

#这个值取决于你的程序,可能高或者低.8是代表起始值.

innodb_thread_concurrency=8

innodb_additional_mem_pool_size=100M

#如果你不是很关心ACID,可以容许在系统完全crash的情况下丢失最后一两秒的事务,那么可以设置这个值.它可以极大的提高”短”的写事务的效率.

innodb_flush_log_at_trx_commit=2

注意:

很多情况需要具体情况具体分析

1>如果Key_reads太大,则应该把https://www.sodocs.net/doc/6d11693965.html,f中Key_buffer_size变大,保持Key_reads/Key_read_requests至少1/100以上,越小越好。

2>如果Qcache_lowmem_prunes很大,就要增加Query_cache_size的值。

sql语句(mysql优化)绝对经典

sql语句(mysql优化)绝对经典 误区1:count(1)和count(primary_key) 优于count(*) 很多人为了统计记录条数,就使用count(1) 和count(primary_key) 而不是count(*) ,他们认为这样性能更好,其实这是一个误区。对于有些场景,这样做可能性能会更差,应为数据库对count(*) 计数操作做了一些特别的优化。 误区2:count(column) 和count(*) 是一样的 这个误区甚至在很多的资深工程师或者是DBA 中都普遍存在,很多人都会认为这是理所当然的。实际上,count(column) 和count(*) 是一个完全不一样的操作,所代表的意义也完全不一样。count(column) 是表示结果集中有多少个column字段不为空的记录,count(*) 是表示整个结果集有多少条记录 误区3:select a,b from … 比select a,b,c from …可以让数据库访问更少的数据量 这个误区主要存在于大量的开发人员中,主要原因是对数据库的存储原理不是太了解。实际上,大多数关系型数据库都是按照行(row)的方式存储,而数据存取操作都是以一个固定大小的IO单元(被称作block 或者page)为单位,一般为4KB,8KB… 大多数时候,每个IO单元中存储了多行,每行都是存储了该行的所有字段(lob等特殊类型字段除外)。 所以,我们是取一个字段还是多个字段,实际上数据库在表中需要访问的数据量其实是一样的。当然,也有例外情况,那就是我们的这个查询在索引中就可以完成,也就是说当只取a,b两个字段的时候,不需要回表,而c这个字段不在使用的索引中,需要回表取得其数据。在这样的情况下,二者的IO量会有较大差异。(覆盖索引) 误区4:order by 一定需要排序操作 我们知道索引数据实际上是有序的,如果我们的需要的数据和某个索引的顺序一致,而且我们的查询又通过这个索引来执行,那么数据库一般会省略排序操作,而直接将数据返回,因为数据库知道数据已经满足我们的排序需求了。实际上,利用索引来优化有排序需求的SQL,是一个非常重要的优化手段。延伸阅读:MySQL ORDER BY 的实现分析,MySQL 中GROUP BY 基本实现原理以及MySQL DISTINCT 的基本实现原理。(order by null)

MYSQL四种备份方法总结

MYSQL四种备份方法总结 Mysql数据库备份主要有4种方法: 1、mysqldump 2、直接拷贝(cp、tar,gzip,cpio) 3、mysqlhotcopy 4、同步复制 mysqldump生成能够移植到其它机器的文本文件,缺省地,文件内容包含创建正在倾倒的表的CREATE语句和包含表中行数据的INSERT语句。也就是说,mysqldump产生的输出可在以后用作mysql的输入来重建数据库。mysqldump比直接拷贝要慢些。 使用直接拷贝,如果正在备份的表正被读写就容易导致表损坏,而且不建议对isam表使用直接拷贝的方法来备份,因为ISAM表只能在相似的硬件结构的机器上拷贝。 1、mysqldump备份: 使用方法:mysqldump [OPTIONS] database [tables] 输出文件的开头看起来象这样: # MySQL Dump 6.0 # # Host: localhost Database: samp_db #--------------------------------------- # Server version 3.23.2-alpha-log # # Table structure for table 'absence' # CREATE TABLE absence( student_id int(10) unsigned DEFAULT '0' NOT NULL, date date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (student_id,date) ); # # Dumping data for table 'absence' # INSERT INTO absence VALUES (3,'1999-09-03'); INSERT INTO absence VALUES (5,'1999-09-03'); INSERT INTO absence VALUES (10,'1999-09-08'); ...... 文件剩下的部分有更多的INSERT和CREATE TABLE语句组成。例: %mysqldump samp_db >/opt/mysqldatabak/samp_db.2006-5-15 %mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz #产生压缩备份 %mysqldump samp_db student score event absence >grapbook.sql #备份数据库的某些表 %mysqladmin -h https://www.sodocs.net/doc/6d11693965.html, create samp_db %mysqldump samp_db | mysql -h https://www.sodocs.net/doc/6d11693965.html, samp_db #直接恢复到另一个服务器上使用--add-drop-table选项告诉服务器将DROP TABLE IF EXISTS语句写入备份文件,这样当我们以后用来恢复数据库时,如果表已经存在,你

mysql优化笔记

◆Mysql数据库的优化技术<大型网站优化技术> 对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/delete/add]分离 e: 存储过程[模块化编程,可以提高速度] 数据库的三层结构: f: 对mysql配置优化[配置最大并发数my.ini, 调整缓存大小] g: mysql服务器硬件升级 h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM) CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) ◆什么样的表才是符合3NF (范式) 表的范式,是首先符合1NF, 才能满足2NF , 进一步满足3NF 1NF: 即表的列的具有原子性,不可再分解,即列的信息,不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server),就自动的满足1NF ?数据库的分类 关系型数据库: mysql/oracle/db2/informix/sysbase/sql server 非关系型数据库: (特点: 面向对象或者集合) NoSql数据库: MongoDB(特点是面向文档) 2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现id primary key ; 3NF: 即表中不要有冗余数据, 就是说,表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF:显示推导处理

MySQL二进制日志清除

1:二进制日志 二进制日志记录了所有的DDL(数据定义语言)语句和DML(数据操作语言)语句,但是不记录包括数据查询的语句。语句以“事件”的形式保存,它描述了数据的更改过程,此日志对于灾难时的数据恢复起着极其重要的作用 2:日志的位置和格式 当用--log-bin[=file_name]选项启动时,mysqld将包含所有更新数据的SQL命令写入日志文件。如果没有给出file_name值,默认名为主机名后面跟_bin,如果给出了文件名,但没有包含路劲,则文件默认被写入参数DATADIR(数据目录)指定的目录 3:日志的读取 由于日志以二进制的方式存储,不能直接读取,需要用mysqlbinlog工具来查看,语法如下:#mysqlbinlog log_file 4:日志的删除 对于比较繁忙的OLTP系统,由于每天生产日志量大,这些日志如果长时间不清理,将会对磁盘空间带来很大的浪费,因此,定期删除日志是DBA维护Mysql数据库的一个重要工作内容,下面将介绍几种删除日志的常见方法 (1):执行“reset master;”命令,该命令将删除所有二进制日志,新日志的编号从“000001”开始,命令如下 Mysql>reset master; (2):执行“Purge master logs to …mysql-bin.*****?”命令,该命令将删除“*****”编号之前的所有日志,下列中删除了“mysql-bin.000001”之前编号的所有日志 Mysql>purge master logs to …mysql-bin.000015; 从结果中发现,编号000015之前的所有日志都已经删除 (3):执行“purge master logs before …yyyy-mm-dd hh24:min:ss?”命令,该命令将删除日期为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志,下列中删除了日期在“2010-05-22 01:00:00”之前的所有日志 Mysql>purge master logs before …2010-05-22 01:00:00??; (4): 设置参数—expire_logs_days=#(days),此参数的含义是设置日志的过期天数,过来指定的天数后日志将会被自动删除,这样将有利于减少DBA管理日志的工作量。 #vi /etc/https://www.sodocs.net/doc/6d11693965.html,f [mysqld] --expire_logs_days=3 这样,3天前的日志都会被删除,系统自动删除

优化mysql数据库性能

为了提高性能建议作如下优化修改: 优化mysql数据库性能的参数: (1)、max_connections: 允许的同时客户的数量。增加该值增加mysqld 要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到too many connections错误。默认数值是16384,请根据实际情况设置此参数。 (2)、key_buffer_size: 索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是10M,请根据实际情况设置此参数。 (3)、sort_buffer: 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是256K,请根据实际情况设置此参数。 4)、table_cache: 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是256,,请根据实际情况设置此参数。 (5)、thread_cache_size: 可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能修改这个变量值。通过比较connections 和threads_created 状态的变量,可以看到这个变量的作用。默认数值是8,请根据实际情况设置此参数。 注:以上参数的调整可以通过修改C:\AppServ\MySQL\my.ini 文件并重启mysql 实现。这是一个比较谨慎的工作,上面的结果只供参考,请根据具体主机的硬件情况(特别是内存大小)进一步修改。 优化配置文件: C:\zxin10\Was\tomcat\conf\ server.xml (6)、在server.xml中修改标红相关参数。 (7)、

mysql服务性能优化my_cnf配置说明详解16G内存

mysql服务性能优化—https://www.sodocs.net/doc/6d11693965.html,f配置说明详解 (16G内存) MYSQL服务器https://www.sodocs.net/doc/6d11693965.html,f配置文档详解 硬件:内存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帮助文档

MYSQL帮助文档 1.使用Nvicat对MySql进行数据迁移操作方法一:数据传输 说明:通过数据传输机制可以实时的将某个指定服务器中的数据库信息同步到指定的目标数据中。还原内容为原数据库当前表信息、记录信息及其他内容,也可以根据用户需要指定传输内容。 操作步骤: 1)打开目标数据库连接,选择需要目标数据库,单击鼠标右键弹出可操作选项,如图 1.1所示。 图1.1 2)鼠标左键单击“数据传输”,弹出数据传输对话框如图1.2,根据需要 a)选择原数据库连接及数据库名称,选择迁移数据库对象。 b)选择目标数据库连接及数据库名称。

图1.2 3)点击【开始】,弹出用户确认对话框如图1.3,若确认有问题点击【取消】,则取消 本次操作,用户可以重新编辑数据传输信息,若确认无误点击【确定】 图1.3 4)确认数据传输,对话框自动切换到信息日志部分如图1.4,当日志下端提示“Finished - Successfully”,即表示数据传输已完成且操作成功。

图1.4 5)若只想迁移数据表结构则在开始数据传输前,在数据传输窗口中选择高级-记录选 项如图1.5,将插入记录的复选框清空即可,根据窗体展示的内容用户可根据实际 需要进行勾选。

图1.5 6)打开目标数据库,刷新数据表,打开相关数据表验证数据表结构及数据迁移结果。 方法二:数据备份、还原 说明:通过数据备份还原机制可以还原某一时间备份文件中的的数据信息。还原内容为原数据库备份时的表信息、记录信息及其他内容。 操作步骤: 步骤一:备份数据 1)打开Navicat Premium,打开原数据库连接,选择需要迁移的数据表结构,单击鼠 标右键弹出可操作选项,如图2.1所示。

谈谈项目中常用的MySQL优化方法

谈谈项目中常用的MySQL优化方法 本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下: 一、EXPLAIN 做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据: type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式 key_len列,索引长度。 rows列,扫描行数。该值是个预估值。 extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。 二、SQL 语句中IN 包含的值不应过多 MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from t where num in(1,2,3) 对于连续的数值,能用between就不要用in了;再或者使用连接来替换。 三、SELECT语句务必指明字段名称 SELECT*增加很多不必要的消耗(CPU、IO、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。 四、当只需要一条数据的时候,使用limit 1 这是为了使EXPLAIN中type列达到const类型 五、如果排序字段没有用到索引,就尽量少排序 六、如果限制条件中其他字段没有索引,尽量少用or or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用union all或者是union(必要的时候)的方式来代替“or”

传智 韩忠康 mysql 课程笔记5(吐血整理)

昨天作业 2013年4月20日星期六 09:56 class as h on m.host_id =h.id left join

select 2013年4月20日星期六10:06

生成的文件格式: 默认的,采用行来区分记录,而采用制表符,来 区分字段。 为了满足某种特别的需求,会采用不同的分割方式。 支持,在导出数据时,设置记录,与字段的分割符。 通过如下的选项: fields:设置字段选项 Lines: 设置行选项(记录选项) 先看默认值: 字段:fields terminated by '\t' enclosed by '' escaped by '\\‘ 记录:lines terminated by '\n' starting by '' 可以自己设定: select * into outfile 'e:/amp/three' fields terminated by ',' lines terminated by '\n' starting by 'start:' from teacher_class where t_name = '韩信'; 字段包裹 select * into outfile 'e:/amp/four' fields terminated by '\t' enclosed by 'x' lines terminated by '\n' starting by 'start:' from teacher_class where t_name = '韩信'; 注意: 常规的,所有的记录,应该通过行来显示例外是保存二进制数据:

MySQL5.1性能优化方案

MySQL5.1性能优化方案 1.平台数据库 1.1.操作系统 Red Hat Enterprise Linux Server release 5.4 (Tikanga) ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped 32位Linux服务器,单独作为MySQL服务器使用。 1.2.M ySQL 系统使用的是MySQL5.1,最新的MySQL5.5较之老版本有了大幅改进。主要体现在以下几个方面: 1)默认存储引擎更改为InnoDB InnoDB作为成熟、高效的事务引擎,目前已经广泛使用,但MySQL5.1之前的版本默认引擎均为MyISAM,此次MySQL5.5终于将默认数据库存储引擎改为InnoDB,并且引进了Innodb plugin 1.0.7。此次更新对数据库的好处是显而易见的:InnoDB的数据恢复时间从过去的一个甚至几个小时,缩短到几分钟(InnoDB plugin 1.0.7,InnoDB plugin 1.1,恢复时采用红-黑树)。InnoDB Plugin 支持数据压缩存储,节约存储,提高内存命中率,并且支持adaptive flush checkpoint, 可以在某些场合避免数据库出现突发性能瓶颈。 Multi Rollback Segments:原来InnoDB只有一个Segment,同时只支持1023的并发。现已扩充到128个Segments,从而解决了高并发的限制。 2)多核性能提升

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

mysql性能优化-慢查询分析、优化索引和配置目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) max_connections 2) back_log 3) interactive_timeout 4) key_buffer_size 5) query_cache_size 6) record_buffer_size 7) read_rnd_buffer_size 8) sort_buffer_size 9) join_buffer_size 10) table_cache 11) max_heap_table_size 12) tmp_table_size

13) thread_cache_size 14) thread_concurrency 15) wait_timeout 一、优化概述 MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上,我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。 除了服务器硬件的性能瓶颈,对于MySQL系统本身,我们可以使用工具来优化数据库的性能,通常有三种:使用索引,使用EXPLAIN分析查询以及调整MySQL的内部配置。 二、查询与索引优化分析 在优化MySQL时,通常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 1 性能瓶颈定位 Show命令 我们可以通过show命令查看MySQL状态及变量,找到系统的瓶颈: Mysql> show status ——显示状态信息(扩展show status like ‘XXX’) Mysql> show variables ——显示系统变量(扩展show variables like ‘XXX’) Mysql> show innodb status ——显示InnoDB存储引擎的状态 Mysql> show processlist ——查看当前SQL执行,包括执行状态、是否锁表等

MySQL优化原则

MySQL优化原则 转载2014年05月20日10:27:13 1113 数据库已成为互联网应用必不可少的底层依赖,其中MySQL作为开源数据库得到了更加广泛的应用。最近一直专注于项目工程的开发,对开发过程中使用到的一些关于数据库的优化原则进行了总结,希望能够帮助更多的应用开发人员更好的使用MySQL数据库。 MySQL的优化主要包括三个方面,首先是SQL语句的优化,其次是表结构的优化,这里主要指索引的优化,最后是服务器配置的优化。第四点代码结构的优化!!! 1.SQL语句的优化 1)查询语句应该尽量避免全表扫描,首先应该考虑在Where子句以及OrderBy子句上建立索引,但是每一条SQL语句最多只会走一条索引,而建立过多的索引会带 来插入和更新时的开销,同时对于区分度不大的字段,应该尽量避免建立索引,可 以在查询语句前使用explain关键字,查看SQL语句的执行计划,判断该查询语 句是否使用了索引; 2)应尽量使用EXIST和NOT EXIST代替 IN和NOT IN,因为后者很有可能导致全表扫描放弃使用索引; 3)应尽量避免在Where子句中对字段进行NULL判断,因为NULL判断会导致全表扫描; 4)应尽量避免在Where子句中使用or作为连接条件,因为同样会导致全表扫描; 5)应尽量避免在Where子句中使用!=或者<>操作符,同样会导致全表扫描; 6)使用like “%abc%”或者like “%abc”同样也会导致全表扫描,而like “abc%”会使用索引。 7)在使用Union操作符时,应该考虑是否可以使用Union ALL来代替,因为Union 操作符在进行结果合并时,会对产生的结果进行排序运算,删除重复记录,对于没

MySQL练习题及答案

答案见参考下列黄色标记 一、下面所有题目中包括单选或多选 1.若MySQL Server运行在Linux系统上,那访问MySQL服务器的客 户端程序也必须运行在Linux系统吗? A.是 B. 否 2.MySQL与其他关系型数据库(SQL Server/Oracle)架构上最大的区别 是? A.连接层 B. SQL层 C.存储引擎层 3.MySQL使用磁盘空间来存储下面哪些信息? A.server和client程序、其他lib库文件 B.日志文件和状态文件 C.数据库 D.表格式(.frm)文件、数据文件、索引文件 E.当内部临时表超过控制设置时,由内存表形式转化为磁盘形式存储 F.上面所有 4.下面哪四种是mysql客户端程序的功能? A.创建、删除数据库 B.创建、删除、修改表和索引

C.使用shutdown命令关闭服务器 D.创建、管理用户 E.显示replication状态信息 F.使用start backup命令来进行数据库二进制备份 5.在MySQL内部有4种常见日志,哪种日志是不能直接cat或more 文本查阅日志内容? A.错误日志(error-log) B.二进制日志(bin-log) C.查询日志(query-log) D.慢查询日志(slow-log) 6.下面哪三种方式可以查看Country表的存储引擎? A.SHOW CREATE TABLE Country; B.SHOW ENGINE Country STATUS;; C.SHOW TABLE STATUS LIKE ‘Country’; D.SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=’Country’; E.SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES WHERE TABLE_NAME =’County’; 7.在高并发、事务等场景下,MySQL5.6数据库默认使用哪种存储引

MySQL大数据量的查询提高性能优化

最近一段时间参与的项目要操作百万级数据量的数据,普通SQL查询效率呈直线下降,而且如果where中的查询条件较多时,其查询速度简直无法容忍。之前数据量小的时候,查询语句的好坏不会对执行时间有什么明显的影响,所以忽略了许多细节性的问题。 经测试对一个包含400多万条记录的表执行一条件查询,其查询时间竟然高达40几秒,相信这么高的查询延时,任何用户都会抓狂。因此如何提高sql语句查询效率,显得十分重要。以下是结合网上流传比较广泛的几个查询语句优化方法: 基本原则:数据量大的时候,应尽量避免全表扫描,应考虑在where 及order by 涉及的列上建立索引,建索引可以大大加快数据的检索速度。但是,有些情况索引是不会起效的,因此,需要下面的做法进行优化: 1、应尽量避免在where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 2、应尽量避免在where 子句中对字段进行null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3、尽量避免在where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union all select id from t where num=20 4、下面的查询也将导致全表扫描:

千万级的mysql数据库与优化方法

千万级的mysql数据库与优化方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在where 及order by 涉及的列上建立索引。 2.应尽量避免在where 子句中对字段进行null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: Sql代码 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: Sql代码 3.应尽量避免在where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 4.应尽量避免在where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:Sql代码 可以这样查询: Sql代码 5.in 和not in 也要慎用,否则会导致全表扫描,如: 对于连续的数值,能用between 就不要用in 了: 6.下面的查询也将导致全表扫描: Sql代码

若要提高效率,可以考虑全文检索。 7.如果在where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: Sql代码 可以改为强制查询使用索引: 8.应尽量避免在where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如: 应改为: 9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:Sql代码 应改为: 10.不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。 11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。 12.不要写一些没有意义的查询,如需要生成一个空表结构:

Mysql日志管理笔记

Mysql日志管理笔记(5.7版本) wangzz 四种日志文件: 1,二进制日志:以二进制形式记录数据库的各种操作,但不记录查询语句. 2,错误日志: 该日志文件记录mysql服务器启动,关闭和运行时的出错等信息。 3,通用查询日志:记录mysql启动,关闭,及客户端的连接信息,更新数据记录sql语句和查询数据记录sql语句. 4,慢查询日志:记录执行时间超过指定时间的各种操作,通过工具分析慢查询日志可以定位性能瓶颈。 1,二进制日志 1)启动二进制日志 /etc/https://www.sodocs.net/doc/6d11693965.html,f 文件。 [mysqld] log-bin[=dir/[filename]] server_id=100 log-bin=/export/app/log/binlog/binlog.log 重启服务就可以启动二进制日志文件,如果启动不了。看log-error日志,你会时到重启不了的原因 mysql> show variables like '%bin%'; +-----------------------------------------+-------------------------------------+ | Variable_name | Value | +-----------------------------------------+-------------------------------------+ | bind_address | * | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | ON | | log_bin_basename | /export/app/log/binlog/binlog | | log_bin_index | /export/app/log/binlog/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_statements_unsafe_for_binlog | ON | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sql_log_bin | ON | | sync_binlog | 1 | +-----------------------------------------+-------------------------------------+ 27 rows in set (0.00 sec) 查看二进制日志文件 [root@risoserverbinlog]# mysqlbinlog binlog.000001

Mysql千万级别数据优化方案总结

Mysql千万级别数据优化方案 目录 目录 (1) 一、目的与意义 (2) 1)说明 (2) 二、解决思路与根据(本测试表中数据在千万级别) (2) 1)建立索引 (2) 2)数据体现(主键非索引,实际测试结果其中fid建立索引) (2) 3)MySQL分页原理 (2) 4)经过实际测试当对表所有列查询时 (2) 三、总结 (3) 1)获得分页数据 (3) 2)获得总页数:创建表记录大数据表中总数通过触发器来维护 (3)

一、目的与意义 1)说明 在MySql单表中数据达到千万级别时数据的分页查询结果时间过长,对此进行优达 到最优效果,也就是时间最短;(此统计利用的jdbc连接,其中fid为该表的主键;) 二、解决思路与根据(本测试表中数据在千万级别) 1)建立索引 优点:当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜 索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记 录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是 在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索 引中的ROWID(相当于页码)快速找到表中对应的记录。 缺点:当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降 低了数据的维护速度。 2)数据体现(主键非索引,实际测试结果其中fid建立索引) 未创建索引:SELECT fid from t_history_data LIMIT 8000000,10结果:13.396s 创建索引:SELECT fid from t_history_data LIMIT 8000000,10结果:2.896s select*fromt_history_datawherefidin (任意十条数据的id )结果:0.141s 首先通过分页得到分页的数据的ID,将ID拼接成字符串利用SQL语句 select * from table where ID in (ID字符串)此语句受数据量大小的影响比较小 (如上测试); 3)MySQL分页原理 MySQL的limit工作原理就是先读取n条记录,然后抛弃前n条,读m条想要 的,所以n越大,性能会越差。 优化前SQL: SELECT * FROM v_history_data LIMIT 5000000, 1010.961s 优化后SQL: SELECT * FROM v_history_data INNER JOIN (SELECT fid FROM t_history_data LIMIT 5000000, 10) a USING (fid)1.943s 分别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后 抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了, 然后通过member_id读取需要的列 4)经过实际测试当对表所有列查询时 select * from table 会比select (所有列名)from table 快些(以查询8000000

mysqlbinlog通过日志文件恢复数据

事由:mysql 数据库文件被意外删除或覆盖,数据需要恢复 前提:在利用日志恢复数据以前,我们需要知道我们的MYSQL服务器已经开启日志文件,通常情况下,我们可以使用命令show variables like 'log_%';来查看是否存在可使用的日志文件 mysql> show variables like 'log_%'; +---------------------------------+------------------------------------------------------------------------+ | Variable_name | Value +---------------------------------+------------------------------------------------------------------------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_error | D:\Program Files\MySQL\MySQL Server 5.5\Datas\Data\PC2011071113yod.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | +---------------------------------+------------------------------------------------------------------------+ 注意蓝色部分,这里我们要看到的就是这一部分。 这时我们需要到我们的数据存储目录去查看是来有相应的日志文件,假设我们的数据配置文件(my.ini)中存在节点 [mysql] Log-bin = mysql_bin 如果不存在,请自行配置好(如果你认为你有必要需要日志文件的话)。 这时在data目录中应该包含有mysql_bin.000001 文件,如果中途有刷新日志文件,则还会生成mysql_bin.000002等更多文件。 到了这里我们可以确定我们的日志文件是存在的,那么我们就要根据数据进行分析恢复,假如我们只有一个日志文件,即mysql_bin.000001,那么这个日志文件记录了我们从配置日志记录到现在的所有MYSQL数据操作,包括其增、删、改操作,我们现在要做的就是,将日志以.txt文档形式输出,以查看我们所需要的文件部分 实例: 一、打开命令行 二、输入mysqlbinlog (如果环境变量中包括了这个目录,则会直接提示相关信息,否则提示无此命令,如果没有这个命令,我们可以直接到MYSQL 的BIN目录再运行此命令)。 三、详细命令mysqlbinlog ../data/mysql_bin.000001 H:/001.txt; 四、这时我们就可以在H盘中找到001.txt分析我们所需要的内容了,这里我截取一部分数

相关主题