1，sql的編譯順序
站在用戶的角度思考問題，與客戶深入溝通，找到深澤網(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)站推廣、國際域名空間、雅安服務(wù)器托管、企業(yè)郵箱。業(yè)務(wù)覆蓋深澤地區(qū)。
sql 編譯順序 from… on… join… where… order by… group by… having… select…
2，查看sql語句性能:
explain 查詢sql語句
3，優(yōu)化
(1). 最佳作前綴，使用索引順序(按編譯順序)與定義索引時(shí)順序一致，若該字段有跳過、反序，該字段及后面字段索引失效
(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). 分頁偏移量大時(shí)，盡量使用子查詢 select * from tab where id=(select id from tab limit 100000,1) limit 100;
1.對查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
2.應(yīng)盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。
3.應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：
select id from t where num is null
可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：
select id from t where num=0
4.應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(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)
對于連續(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)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描：
select id from t where num=@num
可以改為強(qiáng)制查詢使用索引：
select id from t with(index(索引名)) where num=@num
8.應(yīng)盡量避免在 where 子句中對字段進(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子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：
select id from t where substring(name,1,3)='abc'--name以abc開頭的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)將可能無法正確使用索引。
11.在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。
12.不要寫一些沒有意義的查詢，如需要生成一個(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)
用下面的語句替換：
select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
數(shù)據(jù)庫優(yōu)化一方面是找出系統(tǒng)的瓶頸,提高M(jìn)ySQL數(shù)據(jù)庫的整體性能,而另一方面需要合理的結(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ù)庫即可,而硬優(yōu)化則是操作服務(wù)器硬件及參數(shù)設(shè)置.
2.1 軟優(yōu)化
2.1.1 查詢語句優(yōu)化
1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執(zhí)行信息.
2.例:
顯示:
其中會(huì)顯示索引和查詢數(shù)據(jù)讀取數(shù)據(jù)條數(shù)等信息.
2.1.2 優(yōu)化子查詢
在MySQL中,盡量使用JOIN來代替子查詢.因?yàn)樽硬樵冃枰短撞樵?嵌套查詢時(shí)會(huì)建立一張臨時(shí)表,臨時(shí)表的建立和刪除都會(huì)有較大的系統(tǒng)開銷,而連接查詢不會(huì)創(chuàng)建臨時(shí)表,因此效率比嵌套子查詢高.
2.1.3 使用索引
索引是提高數(shù)據(jù)庫查詢速度最重要的方法之一,關(guān)于索引可以參高筆者M(jìn)ySQL數(shù)據(jù)庫索引一文,介紹比較詳細(xì),此處記錄使用索引的三大注意事項(xiàng):
2.1.4 分解表
對于字段較多的表,如果某些字段使用頻率較低,此時(shí)應(yīng)當(dāng),將其分離出來從而形成新的表,
2.1.5 中間表
對于將大量連接查詢的表可以創(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 只對MyISAM有效,共五個(gè)參數(shù)值:
3. 優(yōu)化表:使用OPTIMIZE關(guān)鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;
LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日志.,優(yōu)化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執(zhí)行過程中會(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ù)庫參數(shù)
優(yōu)化數(shù)據(jù)庫參數(shù)可以提高資源利用率,從而提高M(jìn)ySQL服務(wù)器性能.MySQL服務(wù)的配置參數(shù)都在my.cnf或my.ini,下面列出性能影響較大的幾個(gè)參數(shù).
2.2.3 分庫分表
因?yàn)閿?shù)據(jù)庫壓力過大，首先一個(gè)問題就是高峰期系統(tǒng)性能可能會(huì)降低，因?yàn)閿?shù)據(jù)庫負(fù)載過高對性能會(huì)有影響。另外一個(gè)，壓力過大把你的數(shù)據(jù)庫給搞掛了怎么辦？所以此時(shí)你必須得對系統(tǒng)做分庫分表 + 讀寫分離，也就是把一個(gè)庫拆分為多個(gè)庫，部署在多個(gè)數(shù)據(jù)庫服務(wù)上，這時(shí)作為主庫承載寫入請求。然后每個(gè)主庫都掛載至少一個(gè)從庫，由從庫來承載讀請求。
2.2.4 緩存集群
如果用戶量越來越大，此時(shí)你可以不停的加機(jī)器，比如說系統(tǒng)層面不停加機(jī)器，就可以承載更高的并發(fā)請求。然后數(shù)據(jù)庫層面如果寫入并發(fā)越來越高，就擴(kuò)容加數(shù)據(jù)庫服務(wù)器，通過分庫分表是可以支持?jǐn)U容機(jī)器的，如果數(shù)據(jù)庫層面的讀并發(fā)越來越高，就擴(kuò)容加更多的從庫。但是這里有一個(gè)很大的問題：數(shù)據(jù)庫其實(shí)本身不是用來承載高并發(fā)請求的，所以通常來說，數(shù)據(jù)庫單機(jī)每秒承載的并發(fā)就在幾千的數(shù)量級(jí)，而且數(shù)據(jù)庫使用的機(jī)器都是比較高配置，比較昂貴的機(jī)器，成本很高。如果你就是簡單的不停的加機(jī)器，其實(shí)是不對的。所以在高并發(fā)架構(gòu)里通常都有緩存這個(gè)環(huán)節(jié)，緩存系統(tǒng)的設(shè)計(jì)就是為了承載高并發(fā)而生。所以單機(jī)承載的并發(fā)量都在每秒幾萬，甚至每秒數(shù)十萬，對高并發(fā)的承載能力比數(shù)據(jù)庫系統(tǒng)要高出一到兩個(gè)數(shù)量級(jí)。所以你完全可以根據(jù)系統(tǒng)的業(yè)務(wù)特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫數(shù)據(jù)庫的時(shí)候同時(shí)寫一份數(shù)據(jù)到緩存集群里，然后用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機(jī)器資源承載更高的并發(fā)。
一個(gè)完整而復(fù)雜的高并發(fā)系統(tǒng)架構(gòu)中，一定會(huì)包含：各種復(fù)雜的自研基礎(chǔ)架構(gòu)系統(tǒng)。各種精妙的架構(gòu)設(shè)計(jì).因此一篇小文頂多具有拋磚引玉的效果,但是數(shù)據(jù)庫優(yōu)化的思想差不多就這些了.
為了能最小化磁盤I/O MyISAM 存儲(chǔ)引擎采用了很多數(shù)據(jù)庫系統(tǒng)使用的一種策略 它采用一種機(jī)制將最經(jīng)常訪問的表保存在內(nèi)存區(qū)塊中
對索引區(qū)塊來說 它維護(hù)著一個(gè)叫索引緩存(索引緩沖)的結(jié)構(gòu)體 這個(gè)結(jié)構(gòu)體中放著許多那些最常使用的索引區(qū)塊的緩沖區(qū)塊 對數(shù)據(jù)區(qū)塊來說 MySQL沒有使用特定的緩存 它依靠操作系統(tǒng)的本地文件系統(tǒng)緩存
本章首先描述了 MyISAM 索引緩存的基本操作 然后討論在MySQL 中所做的改進(jìn) 它提高了索引緩存性能 同時(shí)能更好地控制緩存操作
線程之間不再是串行地訪問索引緩存 多個(gè)線程可以并行地訪問索引緩存 可以設(shè)置多個(gè)索引緩存 同時(shí)也能指定數(shù)據(jù)表索引到特定的緩存中
索引緩存機(jī)制對 ISAM 表同樣適用 不過 這種有效性正在減弱 自從MySQL 開始 MyISAM 表類型引進(jìn)之后 ISAM 就不再建議使用了 MySQL 更是延續(xù)了這個(gè)趨勢 ISAM 類型默認(rèn)被禁用了
可以通過系統(tǒng)變量 key_buffer_size 來控制索引緩存區(qū)塊的大小 如果這個(gè)值大小為 那么就不使用緩存 當(dāng)這個(gè)值小得于不足以分配區(qū)塊緩沖的最小數(shù)量( )時(shí) 也不會(huì)使用緩存
當(dāng)索引緩存無法操作時(shí) 索引文件就只通過操作系統(tǒng)提供的本地文件系統(tǒng)緩沖來訪問(換言之 表索引區(qū)塊采用的訪問策略和數(shù)據(jù)區(qū)塊的一致)
一個(gè)索引區(qū)塊在 MyISAM 索引文件中是一個(gè)連續(xù)訪問的單元 通常這個(gè)索引區(qū)塊的大小和B樹索引節(jié)點(diǎn)大小一樣(索引在磁盤中是以B樹結(jié)構(gòu)來表示的 這個(gè)樹的底部時(shí)葉子節(jié)點(diǎn) 葉子節(jié)點(diǎn)之上則是非葉子節(jié)點(diǎn))
在索引緩存結(jié)構(gòu)中所有的區(qū)塊大小都是一樣的 這個(gè)值可能等于 大于 或小于表的索引區(qū)塊大小 通常這兩個(gè)值是不一樣的
當(dāng)必須訪問來自任何表的索引區(qū)塊時(shí) 服務(wù)器首先檢查在索引緩存中是否有可用的緩沖區(qū)塊 如果有 服務(wù)器就訪問緩存中的數(shù)據(jù) 而非磁盤 就是說 它直接存取緩存 而不是存取磁盤 否則 服務(wù)器選擇一個(gè)(多個(gè))包含其它不同表索引區(qū)塊的緩存緩沖區(qū)塊 將它的內(nèi)容替換成請求表的索引區(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ū)塊被訪問了 就把它放到隊(duì)列的最后位置 當(dāng)區(qū)塊要被替換時(shí) 在隊(duì)列開始位置的區(qū)塊就是最近最少使用的 它就是第一候選刪除對象
共享訪問索引緩存
在MySQL 以前 訪問索引緩存是串行的 兩個(gè)線程不能并行地訪問索引緩存緩沖 服務(wù)器處理一個(gè)訪問索引區(qū)塊的請求只能等它之前的請求處理完 結(jié)果 新的請求所需的索引區(qū)塊就不在任何索引緩存環(huán)沖區(qū)塊中 因?yàn)槠渌€程把包含這個(gè)索引區(qū)塊的緩沖給更新了
從MySQL 開始 服務(wù)器支持共享方式訪問索引緩存
沒有正在被更新的緩沖可以被多個(gè)線程訪問
緩沖正被更新時(shí) 需要使用這個(gè)緩沖的線程只能等到更新完成之后
多個(gè)線程可以初始化需要替換緩存區(qū)塊的請求 只要它們不干擾別的線程(也就是 它們請求不同的索引區(qū)塊 因此不同的緩存區(qū)塊被替換)
共享方式訪問索引緩存令服務(wù)器明顯改善了吞吐量
多重索引緩存
共享訪問索引緩存改善了性能 卻不能完全消除線程間的沖突 它們?nèi)匀粻帗尶刂乒芾泶嫒∷饕彺婢彌_的結(jié)構(gòu) 為了更進(jìn)一步減少索引緩存存取沖突 MySQL 提供了多重索引緩存特性 這能將不同的表索引指定到不同的索引緩存
當(dāng)有多個(gè)索引緩存 服務(wù)器在處理指定的 MyISAM 表查詢時(shí)必須知道該使用哪個(gè) 默認(rèn)地 所有的 MyISAM 表索引都緩存在默認(rèn)的索引緩存中 想要指定到特定的緩存中 可以使用 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?| + + + + +
注意 如果服務(wù)器編譯支持存 ISAM 儲(chǔ)引擎了 那么 ISAM 表也使用索引緩存機(jī)制 不過 ISAM 表索引只能使用默認(rèn)的索引緩存而不能自定義
CACHE INDEX 語句中用到的索引緩存是根據(jù)用 SET GLOBAL 語句的參數(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)索引緩存中去 對一個(gè)繁忙的系統(tǒng)來說 我們建議以下三條策略來使用索引緩存 熱緩存占用 %的總緩存空間 用于繁重搜索但很少更新的表 冷緩存占用 %的總緩存空間 用于中等強(qiáng)度更新的表 如臨時(shí)表 冷緩存占用 %的總緩存空間 作為默認(rèn)的緩存 用于所有其他表 使用三個(gè)緩存的一個(gè)原因是好處在于 存取一個(gè)緩存結(jié)構(gòu)時(shí)不會(huì)阻止對其他緩存的訪問 訪問一個(gè)表索引的查詢不會(huì)跟指定到其他緩存的查詢競爭 性能提高還表現(xiàn)在以下幾點(diǎn)原因 熱緩存只用于檢索記錄 因此它的內(nèi)容總是不需要變化 所以 無論什么時(shí)候一個(gè)索引區(qū)塊需要從磁盤中引入 被選中要替換的緩存區(qū)塊的內(nèi)容總是要先被刷新 索引被指向熱緩存中后 如果沒有需要掃描全部索引的查詢 那么對應(yīng)到B樹中非葉子節(jié)點(diǎn)的索引區(qū)塊極可能還保留在緩存中 在臨時(shí)表里必須頻繁執(zhí)行一個(gè)更新操作是相當(dāng)快的 如果要被更新的節(jié)點(diǎn)已經(jīng)在緩存中了 它無需先從磁盤中讀取出來 當(dāng)臨時(shí)表的索引大小和冷緩存大小一樣時(shí) 那么在需要更新一個(gè)節(jié)點(diǎn)時(shí)它已經(jīng)在緩存中存在的幾率是相當(dāng)高的
中點(diǎn)插入策略
默認(rèn)地 MySQL 的索引緩存管理系統(tǒng)采用LRU策略來選擇要被清除的緩存區(qū)塊 不過它也支持更完善的方法 叫做 中點(diǎn)插入策略
使用中點(diǎn)插入策略時(shí) LRU鏈就被分割成兩半 一個(gè)熱子鏈 一個(gè)溫子鏈 兩半分割的點(diǎn)不是固定的 不過緩存管理系統(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)擊率(訪問這個(gè)區(qū)塊)后 它就提升到熱子鏈中去 目前 要提升一個(gè)區(qū)塊的點(diǎn)擊率( )對每個(gè)區(qū)塊來說都是一樣的 將來 我們會(huì)讓點(diǎn)擊率依靠B樹中對應(yīng)的索引區(qū)塊節(jié)點(diǎn)的級(jí)別 包含非葉子節(jié)點(diǎn)的索引區(qū)塊所要求的提升點(diǎn)擊率就低一點(diǎn) 包含葉子節(jié)點(diǎn)的B索引樹的區(qū)塊的值就高點(diǎn)
提升起來的區(qū)塊首先放在熱子鏈的末尾 這個(gè)區(qū)塊在熱子鏈內(nèi)一直循環(huán) 如果這個(gè)區(qū)塊在該子鏈開頭位置停留時(shí)間足夠長了 它就會(huì)被降級(jí)回溫子鏈 這個(gè)時(shí)間是由索引緩存結(jié)構(gòu)體變量的組件 key_cache_age_threshold 值來決定的
這個(gè)閥值是這么描述的 一個(gè)索引緩存包含了 N 個(gè)區(qū)塊 熱子鏈開頭的區(qū)塊在低于 N*key_cache_age_threshold/ 次訪問后就被移動(dòng)到溫子鏈的開頭位置 它又首先成為被刪除的候選對象 因?yàn)橐惶鎿Q的區(qū)塊還是從溫子鏈的開頭位置開始的
中點(diǎn)插入策略就能在緩存中總能保持更有價(jià)值的區(qū)塊 如果更喜歡采用LRU策略 只需讓 key_cache_division_limit 的值低于默認(rèn)值
中點(diǎn)插入策略能幫助改善在執(zhí)行需要有效掃描索引 它會(huì)將所有對應(yīng)到B樹中高級(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ū)塊用來保存全部索引 或者至少足夠保存全部非葉子節(jié)點(diǎn) 那么在使用前就載入索引緩存就很有意義了 將索引區(qū)塊以十分有效的方法預(yù)載入索引緩存緩沖 從磁盤中順序地讀取索引區(qū)塊
沒有預(yù)載入 查詢所需的索引區(qū)塊仍然需要被放到緩存中去 雖然索引區(qū)塊要保留在緩存中 因?yàn)橛凶銐虻木彌_ 它們可以從磁盤中隨機(jī)讀取到 而非順序地
想要預(yù)載入緩存 可以使用 LOAD INDEX INTO CACHE 語句 如下語句預(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?| + + + + +
增加修飾語 IGNORE LEAVES 就只預(yù)載入非葉子節(jié)點(diǎn)的索引區(qū)塊 因此 上述語句加載了 t 的全部索引區(qū)塊 但是只加載 t 的非葉子節(jié)點(diǎn)區(qū)塊
如果使用 CACHE INDEX 語句將索引指向一個(gè)索引緩存 將索引區(qū)塊預(yù)先放到那個(gè)緩存中去 否則 索引區(qū)塊只會(huì)加載到默認(rèn)的緩存中去
索引緩存大小
MySQL 引進(jìn)了對每個(gè)索引緩存的新變量 key_cache_block_size 這個(gè)變量可以指定每個(gè)索引緩存的區(qū)塊大小 用它就可以來調(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)來很多不必要的數(shù)據(jù) 這大大阻礙了讀其他葉子節(jié)點(diǎn)
目前 還不能控制數(shù)據(jù)表的索引區(qū)塊大小 這個(gè)大小在服務(wù)器創(chuàng)建索引文件 ` MYI 時(shí)已經(jīng)設(shè)定好了 它根據(jù)數(shù)據(jù)表的索引大小的定義而定 在很多時(shí)候 它設(shè)置成和I/O緩沖大小一樣 在將來 可以改變它的值 并且會(huì)全面采用變量 key_cache_block_size
重建索引緩存
索引緩存可以通過修改其參數(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ì)清空原來的緩存 在新的變量值基礎(chǔ)上重建緩存 如果緩存中有任何的 臟 索引塊 服務(wù)器會(huì)先把它們保存起來然后才重建緩存 重新設(shè)定其他的索引緩存變量并不會(huì)重建緩存
lishixinzhi/Article/program/Oracle/201311/16615
有八個(gè)方面可以對mysql進(jìn)行優(yōu)化：
1、選取最適用的字段屬性
MySQL可以很好的支持大數(shù)據(jù)量的存取，但是一般說來，數(shù)據(jù)庫中的表越小，在它上面執(zhí)行的查詢也就會(huì)越快。因此，在創(chuàng)建表的時(shí)候，為了獲得更好的性能，我們可以將表中字段的寬度設(shè)得盡可能小。
2. 使用連接（JOIN）來代替子查詢(Sub-Queries)
MySQL從4.1開始支持SQL的子查詢。這個(gè)技術(shù)可以使用SELECT語句來創(chuàng)建一個(gè)單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為過濾條件用在另一個(gè)查詢中。
3、使用聯(lián)合(UNION)來代替手動(dòng)創(chuàng)建的臨時(shí)表
MySQL從4.0的版本開始支持union查詢，它可以把需要使用臨時(shí)表的兩條或更多的select查詢合并的一個(gè)查詢中。在客戶端的查詢會(huì)話結(jié)束的時(shí)候，臨時(shí)表會(huì)被自動(dòng)刪除，從而保證數(shù)據(jù)庫整齊、高效。
4、事務(wù)
盡管我們可以使用子查詢（Sub-Queries）、連接（JOIN）和聯(lián)合（UNION）來創(chuàng)建各種各樣的查詢，但不是所有的數(shù)據(jù)庫操作都可以只用一條或少數(shù)幾條SQL語句就可以完成的。更多的時(shí)候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當(dāng)這個(gè)語句塊中的某一條語句運(yùn)行出錯(cuò)的時(shí)候，整個(gè)語句塊的操作就會(huì)變得不確定起來。設(shè)想一下，要把某個(gè)數(shù)據(jù)同時(shí)插入兩個(gè)相關(guān)聯(lián)的表中，可能會(huì)出現(xiàn)這樣的情況：第一個(gè)表中成功更新后，數(shù)據(jù)庫突然出現(xiàn)意外狀況，造成第二個(gè)表中的操作沒有完成，這樣，就會(huì)造成數(shù)據(jù)的不完整，甚至?xí)茐臄?shù)據(jù)庫中的數(shù)據(jù)。要避免這種情況，就應(yīng)該使用事務(wù)，它的作用是：要么語句塊中每條語句都操作成功，要么都失敗
5、鎖定表
盡管事務(wù)是維護(hù)數(shù)據(jù)庫完整性的一個(gè)非常好的方法，但卻因?yàn)樗莫?dú)占性，有時(shí)會(huì)影響數(shù)據(jù)庫的性能，尤其是在很大的應(yīng)用系統(tǒng)中。由于在事務(wù)執(zhí)行的過程中，數(shù)據(jù)庫將會(huì)被鎖定，因此其它的用戶請求只能暫時(shí)等待直到該事務(wù)結(jié)束。其實(shí)，有些情況下我們可以通過鎖定表的方法來獲得更好的性能。
6、使用外鍵
鎖定表的方法可以維護(hù)數(shù)據(jù)的完整性，但是它卻不能保證數(shù)據(jù)的關(guān)聯(lián)性。這個(gè)時(shí)候我們就可以使用外鍵。
7、使用索引
索引是提高數(shù)據(jù)庫性能的常用方法，它可以令數(shù)據(jù)庫服務(wù)器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當(dāng)中包含有MAX(),MIN()和ORDERBY這些命令的時(shí)候，性能提高更為明顯。
8、優(yōu)化的查詢語句
絕大多數(shù)情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當(dāng)?shù)脑?，索引將無法發(fā)揮它應(yīng)有的作用。
語句執(zhí)行后，會(huì)顯示三個(gè)字段： Query_ID（執(zhí)行ID） | Duration（持續(xù)時(shí)間）| Query（查詢語句） ；
拿到后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ù)期。
返回字段包含： select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 等。
select_type 常見類型：
(1) SIMPLE(簡單SELECT，不使用UNION或子查詢等)
(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY)
(3) UNION(UNION中的第二個(gè)或后面的SELECT語句)
(4) SUBQUERY(子查詢中的第一個(gè)SELECT，結(jié)果不依賴于外部查詢)
table 常見類型：
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的.
有時(shí)不是真實(shí)的表名字,看到的是derivedx(x是個(gè)數(shù)字,我的理解是第幾步執(zhí)行的結(jié)果)
type 常見類型：
對表訪問方式，表示MySQL在表中找到所需行的方式，又稱“訪問類型”。
常用的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL （從左到右，性能從差到好）
possible_keys
指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒有任何索引顯示 null）
該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢
key
key列顯示MySQL實(shí)際決定使用的鍵（索引），必然包含在possible_keys中
如果沒有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len
表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度，非實(shí)際長度，為最大可能長度。
注：不損失精確性的情況下，長度越短越好。
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:#）
沒有找到理想的索引，因此對于從前面表中來的每一個(gè)行組合，MYSQL檢查使用哪個(gè)索引，并用它來從表中返回行。這是使用索引的最慢的連接之一
(4).Using filesort
看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來發(fā)現(xiàn)如何對返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來排序全部行；
(5).Using temporary
看到這個(gè)的時(shí)候，查詢需要優(yōu)化了。這里，MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來存儲(chǔ)結(jié)果，這通常發(fā)生在對不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上；
(6).Using index
列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對表的全部的請求列都是同一個(gè)索引的部分的時(shí)候。
(7).Using where
使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會(huì)發(fā)生，或者是查詢有問題。
重慶分公司
重慶分公司
