MYSQL表優化辦法小結 講的挺周全。本站提示廣大學習愛好者：（MYSQL表優化辦法小結 講的挺周全）文章只能為提供參考，不一定能成為您想要的結果。以下是MYSQL表優化辦法小結 講的挺周全正文
同時在線拜訪量持續增年夜 關於1G內存的辦事器顯著感到到費勁嚴重時乃至天天都邑逝世機 或許時不時的辦事器卡一下 這個成績已經困擾了我半個多月MySQL應用是很具伸縮性的算法，是以你平日能用很少的內存運轉或給MySQL更多的被存以獲得更好的機能。
裝置好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 extended-status –u root –p 可以看到今朝的參數，有３個設置裝備擺設參數是最主要的，即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/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf 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 master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info 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，警報曾經拉響。
