mysql怎么設置優(yōu)化 mysql如何優(yōu)化sql

linux 下怎么優(yōu)化mysql占用內存？

修改mysql配置文件，優(yōu)化緩存大小和連接數連接方式，優(yōu)化sql語句 ,記得mysql好像是有工具可以查看最占用資源的sql語句，找到他，優(yōu)化他。

10年積累的成都做網站、網站制作經驗，可以快速應對客戶對網站的新想法和需求。提供各種問題對應的解決方案。讓選擇我們的客戶得到更好、更有力的網絡服務。我雖然不認識你，你也不認識我。但先網站制作后付款的網站建設流程，更有興化免費網站建設讓你可以放心的選擇與我們合作。

安裝好mysql后，配制文件應該在/usr/local/mysql/share/mysql目錄中，配制文件有幾個，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的網站和不同配制的服務器環(huán)境，當然需要有不同的配制文件了。

一般的情況下，my-medium.cnf這個配制文件就能滿足我們的大多需要；一般我們會把配置文件拷貝到/etc/my.cnf 只需要修改這個配置文件就可以了，使用mysqladmin variables extended-status _u root _p 可以看到目前的參數，有3個配置參數是最重要的，即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只對MyISAM表起作用，

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。一般我們設為16M,實際上稍微大一點的站點　這個數字是遠遠不夠的，通過檢查狀態(tài)值Key_read_requests和Key_reads,可以知道key_buffer_size設置是否合理。比例 key_reads / key_read_requests應該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。 或者如果你裝了phpmyadmin 可以通過服務器運行狀態(tài)看到,筆者推薦用phpmyadmin管理mysql，以下的狀態(tài)值都是本人通過phpmyadmin獲得的實例分析:

這個服務器已經運行了20天

key_buffer_size _ 128M

key_read_requests _ 650759289

key_reads - 79112

比例接近1:8000 健康狀況非常好

mysql 性能優(yōu)化參數配置

mysql -u root -p 回車輸入密碼進入mysql

show processlist;

查看連接數，可以發(fā)現有很多連接處于sleep狀態(tài)，這些其實是暫時沒有用的，所以可以kill掉

show variables like "max_connections";

查看最大連接數，應該是與上面查詢到的連接數相同，才會出現too many connections的情況

set GLOBAL max_connections=1000;

修改最大連接數，但是這不是一勞永逸的方法，應該要讓它自動殺死那些sleep的進程。

show global variables like 'wait_timeout';

這個數值指的是mysql在關閉一個非交互的連接之前要等待的秒數，默認是28800s

set global wait_timeout=300;

修改這個數值，這里可以隨意，最好控制在幾分鐘內

set global interactive_timeout=500;

修改這個數值，表示mysql在關閉一個連接之前要等待的秒數，至此可以讓mysql自動關閉那些沒用的連接，但要注意的是，正在使用的連接到了時間也會被關閉，因此這個時間值要合適

SHOW VARIABLES LIKE '%table_open_cache%';

查看

show global status like 'Open%tables';

MySQL數據庫優(yōu)化（七）

為了能最小化磁盤I/O MyISAM 存儲引擎采用了很多數據庫系統(tǒng)使用的一種策略 它采用一種機制將最經常訪問的表保存在內存區(qū)塊中

對索引區(qū)塊來說 它維護著一個叫索引緩存(索引緩沖)的結構體 這個結構體中放著許多那些最常使用的索引區(qū)塊的緩沖區(qū)塊 對數據區(qū)塊來說 MySQL沒有使用特定的緩存 它依靠操作系統(tǒng)的本地文件系統(tǒng)緩存

本章首先描述了 MyISAM 索引緩存的基本操作 然后討論在MySQL 中所做的改進 它提高了索引緩存性能 同時能更好地控制緩存操作

線程之間不再是串行地訪問索引緩存 多個線程可以并行地訪問索引緩存 可以設置多個索引緩存 同時也能指定數據表索引到特定的緩存中

索引緩存機制對 ISAM 表同樣適用 不過 這種有效性正在減弱 自從MySQL 開始 MyISAM 表類型引進之后 ISAM 就不再建議使用了 MySQL 更是延續(xù)了這個趨勢 ISAM 類型默認被禁用了

可以通過系統(tǒng)變量 key_buffer_size 來控制索引緩存區(qū)塊的大小 如果這個值大小為 那么就不使用緩存 當這個值小得于不足以分配區(qū)塊緩沖的最小數量( )時 也不會使用緩存

當索引緩存無法操作時 索引文件就只通過操作系統(tǒng)提供的本地文件系統(tǒng)緩沖來訪問(換言之 表索引區(qū)塊采用的訪問策略和數據區(qū)塊的一致)

一個索引區(qū)塊在 MyISAM 索引文件中是一個連續(xù)訪問的單元 通常這個索引區(qū)塊的大小和B樹索引節(jié)點大小一樣(索引在磁盤中是以B樹結構來表示的 這個樹的底部時葉子節(jié)點 葉子節(jié)點之上則是非葉子節(jié)點)

在索引緩存結構中所有的區(qū)塊大小都是一樣的 這個值可能等于 大于 或小于表的索引區(qū)塊大小 通常這兩個值是不一樣的

當必須訪問來自任何表的索引區(qū)塊時 服務器首先檢查在索引緩存中是否有可用的緩沖區(qū)塊 如果有 服務器就訪問緩存中的數據 而非磁盤 就是說 它直接存取緩存 而不是存取磁盤 否則 服務器選擇一個(多個)包含其它不同表索引區(qū)塊的緩存緩沖區(qū)塊 將它的內容替換成請求表的索引區(qū)塊的拷貝 一旦新的索引區(qū)塊在緩存中了 索引數據就可以存取了

當發(fā)生被選中要替換的區(qū)塊內容修改了的情況時 這個區(qū)塊就被認為 臟 了 那么 在替換之前 它的內容就必須先刷新到它指向的標索引

通常服務器遵循LRU(最近最少使用)策略 當要選擇替換的區(qū)塊時 它選擇最近最少使用的索引區(qū)塊 為了想要讓選擇變得更容易 索引緩存模塊會維護一個包含所有使用區(qū)塊特別的隊列(LRU鏈) 當一個區(qū)塊被訪問了 就把它放到隊列的最后位置 當區(qū)塊要被替換時 在隊列開始位置的區(qū)塊就是最近最少使用的 它就是第一候選刪除對象

共享訪問索引緩存

在MySQL 以前 訪問索引緩存是串行的 兩個線程不能并行地訪問索引緩存緩沖 服務器處理一個訪問索引區(qū)塊的請求只能等它之前的請求處理完 結果 新的請求所需的索引區(qū)塊就不在任何索引緩存環(huán)沖區(qū)塊中 因為其他線程把包含這個索引區(qū)塊的緩沖給更新了

從MySQL 開始 服務器支持共享方式訪問索引緩存

沒有正在被更新的緩沖可以被多個線程訪問

緩沖正被更新時 需要使用這個緩沖的線程只能等到更新完成之后

多個線程可以初始化需要替換緩存區(qū)塊的請求 只要它們不干擾別的線程(也就是 它們請求不同的索引區(qū)塊 因此不同的緩存區(qū)塊被替換)

共享方式訪問索引緩存令服務器明顯改善了吞吐量

多重索引緩存

共享訪問索引緩存改善了性能 卻不能完全消除線程間的沖突 它們仍然爭搶控制管理存取索引緩存緩沖的結構 為了更進一步減少索引緩存存取沖突 MySQL 提供了多重索引緩存特性 這能將不同的表索引指定到不同的索引緩存

當有多個索引緩存 服務器在處理指定的 MyISAM 表查詢時必須知道該使用哪個 默認地 所有的 MyISAM 表索引都緩存在默認的索引緩存中 想要指定到特定的緩存中 可以使用 CACHE INDEX 語句

如下語句所示 指定表的索 t t 和 t 引緩存到名為 hot_cache 的緩存中

mysql?CACHE?INDEX?t ?t ?t ?IN?hot_cache; + + + + + |?Table?|?Op?|?Msg_type?|?Msg_text?| + + + + + |?test t ?|?assign_to_keycache?|?status?|?OK?| |?test t ?|?assign_to_keycache?|?status?|?OK?| |?test t ?|?assign_to_keycache?|?status?|?OK?| + + + + +

注意 如果服務器編譯支持存 ISAM 儲引擎了 那么 ISAM 表也使用索引緩存機制 不過 ISAM 表索引只能使用默認的索引緩存而不能自定義

CACHE INDEX 語句中用到的索引緩存是根據用 SET GLOBAL 語句的參數設定的值或者服務器啟動參數指定的值創(chuàng)建的 如下 mysql SET GLOBAL keycache key_buffer_size= * ;想要刪除索引緩存 只需設置它的大小為 mysql SET GLOBAL keycache key_buffer_size= ;索引緩存變量是一個結構體變量 由名字和組件構成 例如 keycache key_buffer_size keycache 就是緩存名 key_buffer_size 是緩存組件 默認地 表索引在服務器啟動時指定到主(默認的)索引緩存中 當一個索引緩存被刪掉后 指定到這個緩存的所有索引都被重新指向到了默認索引緩存中去 對一個繁忙的系統(tǒng)來說 我們建議以下三條策略來使用索引緩存 熱緩存占用 %的總緩存空間 用于繁重搜索但很少更新的表 冷緩存占用 %的總緩存空間 用于中等強度更新的表 如臨時表 冷緩存占用 %的總緩存空間 作為默認的緩存 用于所有其他表 使用三個緩存的一個原因是好處在于 存取一個緩存結構時不會阻止對其他緩存的訪問 訪問一個表索引的查詢不會跟指定到其他緩存的查詢競爭 性能提高還表現在以下幾點原因 熱緩存只用于檢索記錄 因此它的內容總是不需要變化 所以 無論什么時候一個索引區(qū)塊需要從磁盤中引入 被選中要替換的緩存區(qū)塊的內容總是要先被刷新 索引被指向熱緩存中后 如果沒有需要掃描全部索引的查詢 那么對應到B樹中非葉子節(jié)點的索引區(qū)塊極可能還保留在緩存中 在臨時表里必須頻繁執(zhí)行一個更新操作是相當快的 如果要被更新的節(jié)點已經在緩存中了 它無需先從磁盤中讀取出來 當臨時表的索引大小和冷緩存大小一樣時 那么在需要更新一個節(jié)點時它已經在緩存中存在的幾率是相當高的

中點插入策略

默認地 MySQL 的索引緩存管理系統(tǒng)采用LRU策略來選擇要被清除的緩存區(qū)塊 不過它也支持更完善的方法 叫做 中點插入策略

使用中點插入策略時 LRU鏈就被分割成兩半 一個熱子鏈 一個溫子鏈 兩半分割的點不是固定的 不過緩存管理系統(tǒng)會注意不讓溫子鏈部分 太短 總是至少包括全部緩存區(qū)塊的 key_cache_division_limit 比率 key_cache_division_limit 是緩存結構體變量的組件部分 因此它是每個緩存都可以設置這個參數值

當一個索引區(qū)塊從表中讀入緩存時 它首先放在溫子鏈的末尾 當達到一定的點擊率(訪問這個區(qū)塊)后 它就提升到熱子鏈中去 目前 要提升一個區(qū)塊的點擊率( )對每個區(qū)塊來說都是一樣的 將來 我們會讓點擊率依靠B樹中對應的索引區(qū)塊節(jié)點的級別 包含非葉子節(jié)點的索引區(qū)塊所要求的提升點擊率就低一點 包含葉子節(jié)點的B索引樹的區(qū)塊的值就高點

提升起來的區(qū)塊首先放在熱子鏈的末尾 這個區(qū)塊在熱子鏈內一直循環(huán) 如果這個區(qū)塊在該子鏈開頭位置停留時間足夠長了 它就會被降級回溫子鏈 這個時間是由索引緩存結構體變量的組件 key_cache_age_threshold 值來決定的

這個閥值是這么描述的 一個索引緩存包含了 N 個區(qū)塊 熱子鏈開頭的區(qū)塊在低于 N*key_cache_age_threshold/ 次訪問后就被移動到溫子鏈的開頭位置 它又首先成為被刪除的候選對象 因為要被替換的區(qū)塊還是從溫子鏈的開頭位置開始的

中點插入策略就能在緩存中總能保持更有價值的區(qū)塊 如果更喜歡采用LRU策略 只需讓 key_cache_division_limit 的值低于默認值

中點插入策略能幫助改善在執(zhí)行需要有效掃描索引 它會將所有對應到B樹中高級別的有價值的節(jié)點推出的查詢時的性能 為了避免這樣 就必須設定 key_cache_division_limit 遠遠低于 以采用中點插入策略 則在掃描索引操作時那些有價值的頻繁點擊的節(jié)點就會保留在熱子鏈中了

索引預載入

如果索引緩存中有足夠的區(qū)塊用來保存全部索引 或者至少足夠保存全部非葉子節(jié)點 那么在使用前就載入索引緩存就很有意義了 將索引區(qū)塊以十分有效的方法預載入索引緩存緩沖 從磁盤中順序地讀取索引區(qū)塊

沒有預載入 查詢所需的索引區(qū)塊仍然需要被放到緩存中去 雖然索引區(qū)塊要保留在緩存中 因為有足夠的緩沖 它們可以從磁盤中隨機讀取到 而非順序地

想要預載入緩存 可以使用 LOAD INDEX INTO CACHE 語句 如下語句預載入了表 t 和 t 的索引節(jié)點(區(qū)塊)

mysql?LOAD?INDEX?INTO?CACHE?t ?t ?IGNORE?LEAVES; + + + + + |?Table?|?Op?|?Msg_type?|?Msg_text?| + + + + + |?test t ?|?preload_keys?|?status?|?OK?| |?test t ?|?preload_keys?|?status?|?OK?| + + + + +

增加修飾語 IGNORE LEAVES 就只預載入非葉子節(jié)點的索引區(qū)塊 因此 上述語句加載了 t 的全部索引區(qū)塊 但是只加載 t 的非葉子節(jié)點區(qū)塊

如果使用 CACHE INDEX 語句將索引指向一個索引緩存 將索引區(qū)塊預先放到那個緩存中去 否則 索引區(qū)塊只會加載到默認的緩存中去

索引緩存大小

MySQL 引進了對每個索引緩存的新變量 key_cache_block_size 這個變量可以指定每個索引緩存的區(qū)塊大小 用它就可以來調整索引文件I/O操作的性能

當讀緩沖的大小和本地操作系統(tǒng)的I/O緩沖大小一樣時 就達到了I/O操作的最高性能了 但是設置索引節(jié)點的大小和I/O緩沖大小一樣未必能達到最好的總體性能 讀比較大的葉子節(jié)點時 服務器會讀進來很多不必要的數據 這大大阻礙了讀其他葉子節(jié)點

目前 還不能控制數據表的索引區(qū)塊大小 這個大小在服務器創(chuàng)建索引文件 ` MYI 時已經設定好了 它根據數據表的索引大小的定義而定 在很多時候 它設置成和I/O緩沖大小一樣 在將來 可以改變它的值 并且會全面采用變量 key_cache_block_size

重建索引緩存

索引緩存可以通過修改其參數值在任何時候重建它 例如

mysql?SET?GLOBAL?cold_cache key_buffer_size= * * ;

如果設定索引緩存的結構體變量組件變量 key_buffer_size 或 key_cache_block_size 任何一個的值和它當前的值不一樣 服務器就會清空原來的緩存 在新的變量值基礎上重建緩存 如果緩存中有任何的 臟 索引塊 服務器會先把它們保存起來然后才重建緩存 重新設定其他的索引緩存變量并不會重建緩存

lishixinzhi/Article/program/Oracle/201311/16615

本文題目：mysql怎么設置優(yōu)化 mysql如何優(yōu)化sql
文章鏈接：http://muchs.cn/article2/doedeic.html

成都網站建設公司_創(chuàng)新互聯，為您提供網站導航、服務器托管、營銷型網站建設、網站制作、移動網站建設、品牌網站設計

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯