mysql優(yōu)化

1，sql的編譯順序

站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到深澤網(wǎng)站設(shè)計(jì)與深澤網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：成都網(wǎng)站制作、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、國(guó)際域名空間、雅安服務(wù)器托管、企業(yè)郵箱。業(yè)務(wù)覆蓋深澤地區(qū)。

sql 編譯順序 from… on… join… where… order by… group by… having… select…

2，查看sql語(yǔ)句性能:

explain 查詢sql語(yǔ)句

3，優(yōu)化

(1). 最佳作前綴，使用索引順序(按編譯順序)與定義索引時(shí)順序一致，若該字段有跳過(guò)、反序，該字段及后面字段索引失效

(2). where條件中一切不是=的操作大概率會(huì)使索引失效，包括in、!=、、is null、計(jì)算、函數(shù)等等

(3). 查詢字段與條件字段不一致時(shí)使用子查詢，避免臨時(shí)表出現(xiàn)

(4). 若用了復(fù)合索引，盡量使用全部索引字段

(5). 能不查詢多字段時(shí)，盡量使用索引覆蓋

(6). 使用like模糊查詢時(shí)，按關(guān)鍵字左匹配，即‘x%’，若使用’%x%’，索引失效

(7). or會(huì)使全部索引失效

(8). 盡量不要導(dǎo)致類型轉(zhuǎn)換，否則索引失效

(9). 使用order by時(shí)，根據(jù)表中數(shù)據(jù)量調(diào)整單路還是雙路查詢，也可以調(diào)整buffer區(qū)大?。喝鐂et_max_length_for_sort_data = 1024 (單位byte)

(10). 避免使用select *…

(11). 分頁(yè)偏移量大時(shí)，盡量使用子查詢 select * from tab where id=(select id from tab limit 100000,1) limit 100;

mysql優(yōu)化的常用方法有哪些

1.對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

2.應(yīng)盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

3.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num is null

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒(méi)有null值，然后這樣查詢：

select id from t where num=0

4.應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

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

5.下面的查詢也將導(dǎo)致全表掃描：

select id from t where name like '%abc%'

若要提高效率，可以考慮全文檢索。

6.in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

select id from t where num in(1,2,3)

對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7.如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問(wèn)計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問(wèn)計(jì)劃，變量的值還是未知的，因而無(wú)法作為索引選擇的輸入項(xiàng)。如下面語(yǔ)句將進(jìn)行全表掃描：

select id from t where num=@num

可以改為強(qiáng)制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where num/2=100

應(yīng)改為:

select id from t where num=100*2

9.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where substring(name,1,3)='abc'--name以abc開(kāi)頭的id

select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

應(yīng)改為:

select id from t where name like 'abc%'

select id from t where createdate='2005-11-30' and createdate'2005-12-1'

10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。

11.在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

12.不要寫一些沒(méi)有意義的查詢，如需要生成一個(gè)空表結(jié)構(gòu)：

select col1,col2 into #t from t where 1=0

這類代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的，應(yīng)改成這樣：

create table #t(...)

13.很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：

select num from a where num in(select num from b)

用下面的語(yǔ)句替換：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引對(duì)查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對(duì)查詢效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。

超詳細(xì)MySQL數(shù)據(jù)庫(kù)優(yōu)化

數(shù)據(jù)庫(kù)優(yōu)化一方面是找出系統(tǒng)的瓶頸,提高M(jìn)ySQL數(shù)據(jù)庫(kù)的整體性能,而另一方面需要合理的結(jié)構(gòu)設(shè)計(jì)和參數(shù)調(diào)整,以提高用戶的相應(yīng)速度,同時(shí)還要盡可能的節(jié)約系統(tǒng)資源,以便讓系統(tǒng)提供更大的負(fù)荷.

1. 優(yōu)化一覽圖

2. 優(yōu)化

筆者將優(yōu)化分為了兩大類,軟優(yōu)化和硬優(yōu)化,軟優(yōu)化一般是操作數(shù)據(jù)庫(kù)即可,而硬優(yōu)化則是操作服務(wù)器硬件及參數(shù)設(shè)置.

2.1 軟優(yōu)化

2.1.1 查詢語(yǔ)句優(yōu)化

1.首先我們可以用EXPLAIN或DESCRIBE(簡(jiǎn)寫:DESC)命令分析一條查詢語(yǔ)句的執(zhí)行信息.

2.例:

顯示:

其中會(huì)顯示索引和查詢數(shù)據(jù)讀取數(shù)據(jù)條數(shù)等信息.

2.1.2 優(yōu)化子查詢

在MySQL中,盡量使用JOIN來(lái)代替子查詢.因?yàn)樽硬樵冃枰短撞樵?嵌套查詢時(shí)會(huì)建立一張臨時(shí)表,臨時(shí)表的建立和刪除都會(huì)有較大的系統(tǒng)開(kāi)銷,而連接查詢不會(huì)創(chuàng)建臨時(shí)表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高數(shù)據(jù)庫(kù)查詢速度最重要的方法之一,關(guān)于索引可以參高筆者M(jìn)ySQL數(shù)據(jù)庫(kù)索引一文,介紹比較詳細(xì),此處記錄使用索引的三大注意事項(xiàng):

2.1.4 分解表

對(duì)于字段較多的表,如果某些字段使用頻率較低,此時(shí)應(yīng)當(dāng),將其分離出來(lái)從而形成新的表,

2.1.5 中間表

對(duì)于將大量連接查詢的表可以創(chuàng)建中間表,從而減少在查詢時(shí)造成的連接耗時(shí).

2.1.6 增加冗余字段

類似于創(chuàng)建中間表,增加冗余也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優(yōu)化表

分析表主要是分析表中關(guān)鍵字的分布,檢查表主要是檢查表中是否存在錯(cuò)誤,優(yōu)化表主要是消除刪除或更新造成的表空間浪費(fèi).

1. 分析表: 使用 ANALYZE 關(guān)鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關(guān)鍵字,如CHECK TABLE user [option]

option 只對(duì)MyISAM有效,共五個(gè)參數(shù)值:

3. 優(yōu)化表:使用OPTIMIZE關(guān)鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日志.,優(yōu)化表只對(duì)VARCHAR,BLOB和TEXT有效,通過(guò)OPTIMIZE TABLE語(yǔ)句可以消除文件碎片,在執(zhí)行過(guò)程中會(huì)加上只讀鎖.

2.2 硬優(yōu)化

2.2.1 硬件三件套

1.配置多核心和頻率高的cpu,多核心可以執(zhí)行多個(gè)線程.

2.配置大內(nèi)存,提高內(nèi)存,即可提高緩存區(qū)容量,因此能減少磁盤I/O時(shí)間,從而提高響應(yīng)速度.

3.配置高速磁盤或合理分布磁盤:高速磁盤提高I/O,分布磁盤能提高并行操作的能力.

2.2.2 優(yōu)化數(shù)據(jù)庫(kù)參數(shù)

優(yōu)化數(shù)據(jù)庫(kù)參數(shù)可以提高資源利用率,從而提高M(jìn)ySQL服務(wù)器性能.MySQL服務(wù)的配置參數(shù)都在my.cnf或my.ini,下面列出性能影響較大的幾個(gè)參數(shù).

2.2.3 分庫(kù)分表

因?yàn)閿?shù)據(jù)庫(kù)壓力過(guò)大，首先一個(gè)問(wèn)題就是高峰期系統(tǒng)性能可能會(huì)降低，因?yàn)閿?shù)據(jù)庫(kù)負(fù)載過(guò)高對(duì)性能會(huì)有影響。另外一個(gè)，壓力過(guò)大把你的數(shù)據(jù)庫(kù)給搞掛了怎么辦？所以此時(shí)你必須得對(duì)系統(tǒng)做分庫(kù)分表 + 讀寫分離，也就是把一個(gè)庫(kù)拆分為多個(gè)庫(kù)，部署在多個(gè)數(shù)據(jù)庫(kù)服務(wù)上，這時(shí)作為主庫(kù)承載寫入請(qǐng)求。然后每個(gè)主庫(kù)都掛載至少一個(gè)從庫(kù)，由從庫(kù)來(lái)承載讀請(qǐng)求。

2.2.4 緩存集群

如果用戶量越來(lái)越大，此時(shí)你可以不停的加機(jī)器，比如說(shuō)系統(tǒng)層面不停加機(jī)器，就可以承載更高的并發(fā)請(qǐng)求。然后數(shù)據(jù)庫(kù)層面如果寫入并發(fā)越來(lái)越高，就擴(kuò)容加數(shù)據(jù)庫(kù)服務(wù)器，通過(guò)分庫(kù)分表是可以支持?jǐn)U容機(jī)器的，如果數(shù)據(jù)庫(kù)層面的讀并發(fā)越來(lái)越高，就擴(kuò)容加更多的從庫(kù)。但是這里有一個(gè)很大的問(wèn)題：數(shù)據(jù)庫(kù)其實(shí)本身不是用來(lái)承載高并發(fā)請(qǐng)求的，所以通常來(lái)說(shuō)，數(shù)據(jù)庫(kù)單機(jī)每秒承載的并發(fā)就在幾千的數(shù)量級(jí)，而且數(shù)據(jù)庫(kù)使用的機(jī)器都是比較高配置，比較昂貴的機(jī)器，成本很高。如果你就是簡(jiǎn)單的不停的加機(jī)器，其實(shí)是不對(duì)的。所以在高并發(fā)架構(gòu)里通常都有緩存這個(gè)環(huán)節(jié)，緩存系統(tǒng)的設(shè)計(jì)就是為了承載高并發(fā)而生。所以單機(jī)承載的并發(fā)量都在每秒幾萬(wàn)，甚至每秒數(shù)十萬(wàn)，對(duì)高并發(fā)的承載能力比數(shù)據(jù)庫(kù)系統(tǒng)要高出一到兩個(gè)數(shù)量級(jí)。所以你完全可以根據(jù)系統(tǒng)的業(yè)務(wù)特性，對(duì)那種寫少讀多的請(qǐng)求，引入緩存集群。具體來(lái)說(shuō)，就是在寫數(shù)據(jù)庫(kù)的時(shí)候同時(shí)寫一份數(shù)據(jù)到緩存集群里，然后用緩存集群來(lái)承載大部分的讀請(qǐng)求。這樣的話，通過(guò)緩存集群，就可以用更少的機(jī)器資源承載更高的并發(fā)。

一個(gè)完整而復(fù)雜的高并發(fā)系統(tǒng)架構(gòu)中，一定會(huì)包含：各種復(fù)雜的自研基礎(chǔ)架構(gòu)系統(tǒng)。各種精妙的架構(gòu)設(shè)計(jì).因此一篇小文頂多具有拋磚引玉的效果,但是數(shù)據(jù)庫(kù)優(yōu)化的思想差不多就這些了.

MySQL數(shù)據(jù)庫(kù)優(yōu)化（七）

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

對(duì)索引區(qū)塊來(lái)說(shuō) 它維護(hù)著一個(gè)叫索引緩存(索引緩沖)的結(jié)構(gòu)體 這個(gè)結(jié)構(gòu)體中放著許多那些最常使用的索引區(qū)塊的緩沖區(qū)塊 對(duì)數(shù)據(jù)區(qū)塊來(lái)說(shuō) MySQL沒(méi)有使用特定的緩存 它依靠操作系統(tǒng)的本地文件系統(tǒng)緩存

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

線程之間不再是串行地訪問(wèn)索引緩存 多個(gè)線程可以并行地訪問(wèn)索引緩存 可以設(shè)置多個(gè)索引緩存 同時(shí)也能指定數(shù)據(jù)表索引到特定的緩存中

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

可以通過(guò)系統(tǒng)變量 key_buffer_size 來(lái)控制索引緩存區(qū)塊的大小 如果這個(gè)值大小為 那么就不使用緩存 當(dāng)這個(gè)值小得于不足以分配區(qū)塊緩沖的最小數(shù)量( )時(shí) 也不會(huì)使用緩存

當(dāng)索引緩存無(wú)法操作時(shí) 索引文件就只通過(guò)操作系統(tǒng)提供的本地文件系統(tǒng)緩沖來(lái)訪問(wèn)(換言之 表索引區(qū)塊采用的訪問(wèn)策略和數(shù)據(jù)區(qū)塊的一致)

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

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

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

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

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

共享訪問(wèn)索引緩存

在MySQL 以前 訪問(wèn)索引緩存是串行的 兩個(gè)線程不能并行地訪問(wèn)索引緩存緩沖 服務(wù)器處理一個(gè)訪問(wèn)索引區(qū)塊的請(qǐng)求只能等它之前的請(qǐng)求處理完 結(jié)果 新的請(qǐng)求所需的索引區(qū)塊就不在任何索引緩存環(huán)沖區(qū)塊中 因?yàn)槠渌€程把包含這個(gè)索引區(qū)塊的緩沖給更新了

從MySQL 開(kāi)始 服務(wù)器支持共享方式訪問(wèn)索引緩存

沒(méi)有正在被更新的緩沖可以被多個(gè)線程訪問(wèn)

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

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

共享方式訪問(wèn)索引緩存令服務(wù)器明顯改善了吞吐量

多重索引緩存

共享訪問(wèn)索引緩存改善了性能 卻不能完全消除線程間的沖突 它們?nèi)匀粻?zhēng)搶控制管理存取索引緩存緩沖的結(jié)構(gòu) 為了更進(jìn)一步減少索引緩存存取沖突 MySQL 提供了多重索引緩存特性 這能將不同的表索引指定到不同的索引緩存

當(dāng)有多個(gè)索引緩存 服務(wù)器在處理指定的 MyISAM 表查詢時(shí)必須知道該使用哪個(gè) 默認(rèn)地 所有的 MyISAM 表索引都緩存在默認(rèn)的索引緩存中 想要指定到特定的緩存中 可以使用 CACHE INDEX 語(yǔ)句

如下語(yǔ)句所示 指定表的索 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?| + + + + +

注意 如果服務(wù)器編譯支持存 ISAM 儲(chǔ)引擎了 那么 ISAM 表也使用索引緩存機(jī)制 不過(guò) ISAM 表索引只能使用默認(rèn)的索引緩存而不能自定義

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

中點(diǎn)插入策略

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

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

當(dāng)一個(gè)索引區(qū)塊從表中讀入緩存時(shí) 它首先放在溫子鏈的末尾 當(dāng)達(dá)到一定的點(diǎn)擊率(訪問(wèn)這個(gè)區(qū)塊)后 它就提升到熱子鏈中去 目前 要提升一個(gè)區(qū)塊的點(diǎn)擊率( )對(duì)每個(gè)區(qū)塊來(lái)說(shuō)都是一樣的 將來(lái) 我們會(huì)讓點(diǎn)擊率依靠B樹(shù)中對(duì)應(yīng)的索引區(qū)塊節(jié)點(diǎn)的級(jí)別 包含非葉子節(jié)點(diǎn)的索引區(qū)塊所要求的提升點(diǎn)擊率就低一點(diǎn) 包含葉子節(jié)點(diǎn)的B索引樹(shù)的區(qū)塊的值就高點(diǎn)

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

這個(gè)閥值是這么描述的 一個(gè)索引緩存包含了 N 個(gè)區(qū)塊 熱子鏈開(kāi)頭的區(qū)塊在低于 N*key_cache_age_threshold/ 次訪問(wèn)后就被移動(dòng)到溫子鏈的開(kāi)頭位置 它又首先成為被刪除的候選對(duì)象 因?yàn)橐惶鎿Q的區(qū)塊還是從溫子鏈的開(kāi)頭位置開(kāi)始的

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

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

索引預(yù)載入

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

沒(méi)有預(yù)載入 查詢所需的索引區(qū)塊仍然需要被放到緩存中去 雖然索引區(qū)塊要保留在緩存中 因?yàn)橛凶銐虻木彌_ 它們可以從磁盤中隨機(jī)讀取到 而非順序地

想要預(yù)載入緩存 可以使用 LOAD INDEX INTO CACHE 語(yǔ)句 如下語(yǔ)句預(yù)載入了表 t 和 t 的索引節(jié)點(diǎn)(區(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?| + + + + +

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

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

索引緩存大小

MySQL 引進(jìn)了對(duì)每個(gè)索引緩存的新變量 key_cache_block_size 這個(gè)變量可以指定每個(gè)索引緩存的區(qū)塊大小 用它就可以來(lái)調(diào)整索引文件I/O操作的性能

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

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

重建索引緩存

索引緩存可以通過(guò)修改其參數(shù)值在任何時(shí)候重建它 例如

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

如果設(shè)定索引緩存的結(jié)構(gòu)體變量組件變量 key_buffer_size 或 key_cache_block_size 任何一個(gè)的值和它當(dāng)前的值不一樣 服務(wù)器就會(huì)清空原來(lái)的緩存 在新的變量值基礎(chǔ)上重建緩存 如果緩存中有任何的 臟 索引塊 服務(wù)器會(huì)先把它們保存起來(lái)然后才重建緩存 重新設(shè)定其他的索引緩存變量并不會(huì)重建緩存

lishixinzhi/Article/program/Oracle/201311/16615

怎么進(jìn)行mysql數(shù)據(jù)庫(kù)優(yōu)化？

有八個(gè)方面可以對(duì)mysql進(jìn)行優(yōu)化：

1、選取最適用的字段屬性

MySQL可以很好的支持大數(shù)據(jù)量的存取，但是一般說(shuō)來(lái)，數(shù)據(jù)庫(kù)中的表越小，在它上面執(zhí)行的查詢也就會(huì)越快。因此，在創(chuàng)建表的時(shí)候，為了獲得更好的性能，我們可以將表中字段的寬度設(shè)得盡可能小。

2. 使用連接（JOIN）來(lái)代替子查詢(Sub-Queries)

MySQL從4.1開(kāi)始支持SQL的子查詢。這個(gè)技術(shù)可以使用SELECT語(yǔ)句來(lái)創(chuàng)建一個(gè)單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為過(guò)濾條件用在另一個(gè)查詢中。

3、使用聯(lián)合(UNION)來(lái)代替手動(dòng)創(chuàng)建的臨時(shí)表

MySQL從4.0的版本開(kāi)始支持union查詢，它可以把需要使用臨時(shí)表的兩條或更多的select查詢合并的一個(gè)查詢中。在客戶端的查詢會(huì)話結(jié)束的時(shí)候，臨時(shí)表會(huì)被自動(dòng)刪除，從而保證數(shù)據(jù)庫(kù)整齊、高效。

4、事務(wù)

盡管我們可以使用子查詢（Sub-Queries）、連接（JOIN）和聯(lián)合（UNION）來(lái)創(chuàng)建各種各樣的查詢，但不是所有的數(shù)據(jù)庫(kù)操作都可以只用一條或少數(shù)幾條SQL語(yǔ)句就可以完成的。更多的時(shí)候是需要用到一系列的語(yǔ)句來(lái)完成某種工作。但是在這種情況下，當(dāng)這個(gè)語(yǔ)句塊中的某一條語(yǔ)句運(yùn)行出錯(cuò)的時(shí)候，整個(gè)語(yǔ)句塊的操作就會(huì)變得不確定起來(lái)。設(shè)想一下，要把某個(gè)數(shù)據(jù)同時(shí)插入兩個(gè)相關(guān)聯(lián)的表中，可能會(huì)出現(xiàn)這樣的情況：第一個(gè)表中成功更新后，數(shù)據(jù)庫(kù)突然出現(xiàn)意外狀況，造成第二個(gè)表中的操作沒(méi)有完成，這樣，就會(huì)造成數(shù)據(jù)的不完整，甚至?xí)茐臄?shù)據(jù)庫(kù)中的數(shù)據(jù)。要避免這種情況，就應(yīng)該使用事務(wù)，它的作用是：要么語(yǔ)句塊中每條語(yǔ)句都操作成功，要么都失敗

5、鎖定表

盡管事務(wù)是維護(hù)數(shù)據(jù)庫(kù)完整性的一個(gè)非常好的方法，但卻因?yàn)樗莫?dú)占性，有時(shí)會(huì)影響數(shù)據(jù)庫(kù)的性能，尤其是在很大的應(yīng)用系統(tǒng)中。由于在事務(wù)執(zhí)行的過(guò)程中，數(shù)據(jù)庫(kù)將會(huì)被鎖定，因此其它的用戶請(qǐng)求只能暫時(shí)等待直到該事務(wù)結(jié)束。其實(shí)，有些情況下我們可以通過(guò)鎖定表的方法來(lái)獲得更好的性能。

6、使用外鍵

鎖定表的方法可以維護(hù)數(shù)據(jù)的完整性，但是它卻不能保證數(shù)據(jù)的關(guān)聯(lián)性。這個(gè)時(shí)候我們就可以使用外鍵。

7、使用索引

索引是提高數(shù)據(jù)庫(kù)性能的常用方法，它可以令數(shù)據(jù)庫(kù)服務(wù)器以比沒(méi)有索引快得多的速度檢索特定的行，尤其是在查詢語(yǔ)句當(dāng)中包含有MAX(),MIN()和ORDERBY這些命令的時(shí)候，性能提高更為明顯。

8、優(yōu)化的查詢語(yǔ)句

絕大多數(shù)情況下，使用索引可以提高查詢的速度，但如果SQL語(yǔ)句使用不恰當(dāng)?shù)脑挘饕龑o(wú)法發(fā)揮它應(yīng)有的作用。

MySQL常用優(yōu)化方案

語(yǔ)句執(zhí)行后，會(huì)顯示三個(gè)字段： Query_ID（執(zhí)行ID） | Duration（持續(xù)時(shí)間）| Query（查詢語(yǔ)句） ；

拿到后Query_ID后，可執(zhí)行 show profile for query Query_ID ，查看詳細(xì)的準(zhǔn)備時(shí)間，執(zhí)行時(shí)間、執(zhí)行結(jié)束（ preparing、executing、end ）等。

顯示用戶正在運(yùn)行的線程，需要注意的是，除了 root 用戶能看到所有正在運(yùn)行的線程外，其他用戶都只能看到自己正在運(yùn)行的線程，看不到其它用戶正在運(yùn)行的線程。除非單獨(dú)個(gè)這個(gè)用戶賦予了PROCESS 權(quán)限。

顯示字段包含： User| Host| db | Command | Time| State| Info 等。

解析語(yǔ)句，查詢是否命中索引，及，命中何種索引，用以判斷是否符合我們的預(yù)期。

返回字段包含： select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 等。

select_type 常見(jiàn)類型：

(1) SIMPLE(簡(jiǎn)單SELECT，不使用UNION或子查詢等)

(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY)

(3) UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句)

(4) SUBQUERY(子查詢中的第一個(gè)SELECT，結(jié)果不依賴于外部查詢)

table 常見(jiàn)類型：

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的.

有時(shí)不是真實(shí)的表名字,看到的是derivedx(x是個(gè)數(shù)字,我的理解是第幾步執(zhí)行的結(jié)果)

type 常見(jiàn)類型：

對(duì)表訪問(wèn)方式，表示MySQL在表中找到所需行的方式，又稱“訪問(wèn)類型”。

常用的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL （從左到右，性能從差到好）

possible_keys

指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒(méi)有任何索引顯示 null）

該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。

如果該列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下，可以通過(guò)檢查WHERE子句看是否它引用某些列或適合索引的列來(lái)提高你的查詢性能。如果是這樣，創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢

key

key列顯示MySQL實(shí)際決定使用的鍵（索引），必然包含在possible_keys中

如果沒(méi)有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度，非實(shí)際長(zhǎng)度，為最大可能長(zhǎng)度。

注：不損失精確性的情況下，長(zhǎng)度越短越好。

ref

列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。

rows

估算出結(jié)果集行數(shù)，表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)；

extra

該列包含MySQL解決查詢的詳細(xì)信息,有以下幾種情況：

(1).Distinct

一旦MYSQL找到了與行相聯(lián)合匹配的行，就不再搜索了

(2).Not exists

MYSQL優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行，就不再搜索了

(3).Range checked for each

Record（index map:#）

沒(méi)有找到理想的索引，因此對(duì)于從前面表中來(lái)的每一個(gè)行組合，MYSQL檢查使用哪個(gè)索引，并用它來(lái)從表中返回行。這是使用索引的最慢的連接之一

(4).Using filesort

看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行；

(5).Using temporary

看到這個(gè)的時(shí)候，查詢需要優(yōu)化了。這里，MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果，這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上；

(6).Using index

列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候。

(7).Using where

使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會(huì)發(fā)生，或者是查詢有問(wèn)題。

新聞標(biāo)題：mysql優(yōu)化怎么優(yōu)化 MySQL的優(yōu)化
文章來(lái)源：http://jinyejixie.com/article48/hpdhep.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、微信小程序、靜態(tài)網(wǎng)站、虛擬主機(jī)、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站排名

廣告

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