筆記之mysql優(yōu)化篇

辛星筆記之MySQL優(yōu)化篇2014年12******************說明1.百度搜索“辛 筆辛星筆記之MySQL優(yōu)化篇2014年12******************說明1.百度搜索“辛 筆記”可以找到更多更全更優(yōu)秀的資料奧2.辛星筆記，進擊的捷徑，致勝的法********************致敬原著1.本書原名為《深入淺出MySQL：數(shù)據(jù)庫開發(fā)、優(yōu)化與管理維護》，出版時間為2010年，是網(wǎng)易技術(shù)部的唐漢明、翟振興、蘭麗華、關(guān)2.祝愿各位作者工作順利，事業(yè)順心，萬事如意*********************辛星筆記去除廢話，提煉精華辛星筆記，恪守此道傳播知識，傳遞溫情我心永恒，始終如一*********************目錄第一節(jié)：SQL技 第二節(jié)：SQL優(yōu) 第三節(jié)：數(shù)據(jù)庫對 第四節(jié)：鎖問 第五節(jié)：優(yōu)化 第六節(jié)：磁盤 第七節(jié)：應(yīng)用優(yōu) *********************與君共勉因寂寞而優(yōu)因努力而1/第一節(jié)：SQL***************正則表達式的使用正則表第一節(jié)：SQL***************正則表達式的使用正則表達式(RegularExpression)，是指一個用來描述或者匹系列復(fù)合某個句法規(guī)則的字符串的單個字符串縮寫為regex或者regexp。MySQL利用regexp命令提供給用戶擴展的正則表達式功能，regexp現(xiàn)的功能，類似Unixgrepsed功能，并且regexp*********************正則表達式中的模式1.^表示在字符串的開始處進行匹配2.$表示在字符串的末尾處進行匹配3..表示匹配任意單個字符，包括換行符4.[...]表示匹配出括號內(nèi)的任意字5.[^...]表示匹配不出括號內(nèi)的任意字6.a*表示匹配零個或者多個a(包括空7.a+表示匹配一個或者多個a(不包括空格8.a?表示1或者零個9.a1|a2a112.a(m,n)表示匹配m到na13.a(,n)表示匹配0到na14.(...)將模式元素組成單2/*****************操作范例*****************操作范例不匹配，下面檢測abcdefg是否以a開頭，范例：select‘a(chǎn)bcdefg’regexp ‘efg’regexp‘[ef]’;提取以’@163.com’結(jié)尾的范例selectname,emailfromtwhereemailregexp‘@163[.,]com$’;4.當(dāng)然上例中使用email like‘@163%.com’oremaillike‘@163%.com’也可以***************巧用rand()提取隨機行2.在MySQL中，產(chǎn)生隨機數(shù)的方法是rand()函數(shù)3.我們可以利用這個函orderby句一起完成隨機抽取某些行4.它的原理其實就是orderbyrand()能夠把數(shù)據(jù)隨機排序5.比如隨機抽取一部分樣本select*fromt1orderbylimit**********利用groupbywithrollup句做統(tǒng)計1.在SQL句中，使用groupby的withrollup句可以檢索出更它不僅僅能像一般的groupby語句那樣檢索出各組的聚合信息還能檢索出本組類的整體聚合信息其實withrollup反應(yīng)的是一種olap思想，也就是說這一個groupby句執(zhí)行完后可以得到任何一個分組的聚合信息值。3/4.當(dāng)使用rollup不能使用orderby行4.當(dāng)使用rollup不能使用orderby行結(jié)果排序，也就是說，rollup和orderby是互斥的。5.另外，limit用來rollup后面6.范例操selectyear，country，product，sum(profit)byyear,country,productwithrollup。fromsales**************用bitgroupfunctions統(tǒng)計1.我們可以使用groupbybit_and、bit_or數(shù)完成統(tǒng)計工2.這兩個函數(shù)的一般用途就是做數(shù)值之間的邏輯位運算3.當(dāng)把它們與groupby子句聯(lián)合使用的時候可以做一些其他的任務(wù)4.操作范selectcustomer_id,bit_or(kind)fromorder_rabgroupby還是10進制。***************數(shù)據(jù)庫名、表名大小寫問題1.在mysql中，數(shù)據(jù)庫對應(yīng)操作系統(tǒng)下的數(shù)據(jù)目在大多數(shù)Unix操作系統(tǒng)，由于操作系統(tǒng)對大小寫的敏感性導(dǎo)致數(shù)據(jù)庫命名和表名對大小寫的敏感win由于操作系統(tǒng)本身對大小寫不敏感，以你winmysql對數(shù)據(jù)庫名和表名對大小寫也不敏感。4/6.列、索引、存儲子程序和6.列、索引、存儲子程序和觸發(fā)器在任何平臺上對大小寫不敏感7.默認(rèn)情況下，表別名Unix大小寫敏感，但是在windows或者macosx中對大小寫不敏感。1mysql何在硬盤上保存、使用表名和數(shù)據(jù)庫名由lower_case_tables_names系統(tǒng)變量決定。它可以取值為012取值為0示使用createtable者createdatabase語句指定的Unix系統(tǒng)中的默認(rèn)設(shè)置就是這個值。4.取值為1表示表名在硬盤上以小寫保存，名稱對大小寫敏感，mysql所有表名轉(zhuǎn)換為小寫以便存儲和查找，該值為win和mac5.取值為2示表名和數(shù)據(jù)庫名在硬盤上使用createtablecreatedatabase語句指定的大小寫進行保存，但mysql將它們轉(zhuǎn)換為小寫6.要查看它，可以使用showvariableslike************使用外鍵需要注意的問題在mysql中，InnoDB存儲引擎支持對外部鍵字約束條件的查對于其他類型存儲引擎的表，當(dāng)使用referencest1(c1)子句定義列3.下面是InnoDB引擎的范例>createtablet1(idint,namevarchar(10),primarykey(id))>createtable->id5/- ->userid->primary->constraintfk_userid_id- ->userid->primary->constraintfk_userid_idforeignkey(userid)references->engine=4.而且當(dāng)我們用showcreatetable令查看建表語句的時候，發(fā)現(xiàn)MyISAM存儲引擎的并不顯示外鍵的語句，而InnoDB存儲引擎的****************小結(jié)2.rand()函數(shù)與by子句的配合能夠?qū)崿F(xiàn)隨機抽取樣本這個期繳在進行數(shù)據(jù)統(tǒng)計的時候很方便3.groupby的withrollup句能夠?qū)崿F(xiàn)類似于olap4.比特函數(shù)與groupby的聯(lián)合使用在某些應(yīng)用場合可以大大降6/第二節(jié)：SQL***************前言在應(yīng)用的第二節(jié)：SQL***************前言在應(yīng)用的開發(fā)過程中，由于初期數(shù)據(jù)量小，開發(fā)人員寫SQL更重視功能上的實現(xiàn)當(dāng)應(yīng)用系統(tǒng)上線之后，隨著生產(chǎn)數(shù)據(jù)量的急劇增長，很多SQL**************優(yōu)化SQL語句的一般步驟1.通過showstatus令了解各種SQL行頻2.定位執(zhí)行執(zhí)行效率較低的SQL3.通過explain分析低效SQL的執(zhí)行4.確定問題并采取相應(yīng)的優(yōu)化*************了解SQL執(zhí)行頻率1.我們可以通過show[session|global]status令可以提供服務(wù)器也可以在操作系統(tǒng)上mysqladminextended-statusshowstatus果使用session飾表示當(dāng)前連接，使用global操作范showstatuslike其中Com_xxx每個xxx句執(zhí)行的次數(shù)，我們比較關(guān)心的是(1)Com_select:表示執(zhí)行select操作的次數(shù)，一次查詢累加(2)Com_insert：執(zhí)行insert操作的次數(shù)，對于批量插入累加7/(3)Com_update:執(zhí)行update操作的次(4)Com_delete:執(zhí)行(3)Com_update:執(zhí)行update操作的次(4)Com_delete:執(zhí)行delete操作的次數(shù)6.對于InnoDB存儲引擎來說，我們關(guān)注下面幾個數(shù)據(jù)(1)Innodb_rows_read:select查詢返回的行(2)Innodb_rows_inserted:執(zhí)行insert操作插入的(3)Innodb_rows_updated:執(zhí)行update操作更新的行(4)Innodb_rows_deleted:執(zhí)行delete操作刪除的行插入更新為主還是以查詢操作為主，以及各種類型的SQL致的執(zhí)對于事務(wù)型的應(yīng)用，通過Com_commitCom_rollback以了10.我們可以查詢?nèi)缦聨讉€參數(shù)來了解數(shù)據(jù)庫的基本情況(1)Connections:試圖連接MySQL服務(wù)器的(2)Uptime:服務(wù)器工作時(3)Slow_queries:慢查詢的***********定位執(zhí)行效率較低的SQL語句可以通過慢查詢?nèi)罩镜姆绞絹矶ㄎ荒男﹫?zhí)行效率較低的SQL語句，用--log-slow-queries[=file_name]啟動選項時，mysqld寫一個包含所有執(zhí)行時間超過long_query_time秒的SQL語句的日志文3.可以使用showprocesslist查看mysql在進行的線程，包括線程的狀態(tài)、是否鎖表等等，可以實時地查看SQL的執(zhí)行情況，8/***********分析低效SQL的執(zhí)行計劃1.我們可以通explaindesc***********分析低效SQL的執(zhí)行計劃1.我們可以通explaindesc令獲MySQL何執(zhí)select語句的信息，包括在select語句執(zhí)行過程中表如何連接和連接的順2.使用格式如下explainselect3.其中在結(jié)果中select_type表示select的類型，常見的取值有simpe簡單表，即不使用表連接或者子查primary主查詢，即外層的查 union中的第二個或者后面的查詢語subquery--子查詢中的第一個4.在結(jié)果中的type表示表的連接類型，性能由好到差為 表中僅有一行，即常量const單表中最多只有一個匹配行，比如unique 在表中只查詢一條記錄，多表連接使用唯一索 使用普通ref_or_null--條件中包含對null的查index_merge--索引合并優(yōu)unique_subquery-in后面是一個查詢主鍵字段的子查index_subquery--in的后面是查詢非唯一索引字段的子查 單表查詢中的范圍 通過查詢索引來得到數(shù) 通過全表掃描來得到數(shù)5.在結(jié)果中possible_keys表示查詢時，可能使用的索6.在結(jié)果中key表示實際使用的索引9/7.在結(jié)果中的key_len表示索引字段的長7.在結(jié)果中的key_len表示索引字段的長度8.在結(jié)果中的rows表示掃描行的數(shù)9.在結(jié)果中的extra表示執(zhí)行情況的說明和描*******************索引的存儲分類1.MyISAM存儲引擎的表的數(shù)據(jù)和索引是自動分開存儲的，各自是獨立的一個文件，InnoDB儲引擎的表的數(shù)據(jù)和索引是存儲在同一2.MySQL索引的存儲類型目前有BTREE和HASH，具體MyISAMInnoDB儲引擎都只支btree，memory/heap存儲引擎都可以支持hash和btree索引。比如name字段，可以只取name的前4個字符進行索引，這個特5.創(chuàng)建前綴索引的范例createindexind_t1on**************索引說明引是提高select操作性能的最佳途徑。2.查詢要使用索引最主要的條件是查詢條件中需要使用索引關(guān)鍵字******************使用索引的情況10/3.如果like后面跟的是一個3.如果like后面跟的是一個列的名字，那么索引也不會被使如果對大量的文本進行索引，使用全文索引而不是使用如果列名是索引，使用c1 null將使用索引**************存在索引但不使用索引的情況如果mysql估計使用索引比全表掃描更慢，則不使用索引如果使用memory/heap表并且where不使用”=”進行索引heap表只有在”=”的條件下才會使用索引or隔開的條件，如果or件中的列有索引，而后面的5.如果不是索引列的第一部分，這里指的是復(fù)合索如果like始如果列類型是字符串，那么一定記得在where件中把字符串常量值用引號引起來，否則的話即使這個列上有索引，mysql也不會用。因為mysql默認(rèn)把輸入的常量值進行轉(zhuǎn)換后才進行檢索。比explain *fromt1 ***********查看索引使用情況1.如果索引正在工作，那么Handler_read_key的值將會很2.這個值代表了一個行被索引值讀的次數(shù)這個值的含義是在數(shù)據(jù)文件中讀下一行的請求數(shù)11/7.查詢它們的使用為 statuslike7.查詢它們的使用為 statuslike*******兩個簡單有效的優(yōu)化方法方法。對于更多更復(fù)雜的優(yōu)化，更傾向于交給專業(yè)的DBA去做2.第一個優(yōu)化方法就是定期分析表和檢查表3.第二個優(yōu)化方法就是定期優(yōu)化表**************分析表1.分析表的語法如下analyzelocal|no_write_to_binlog]table表名1[,表名系統(tǒng)得到準(zhǔn)確的統(tǒng)計信息，使得SQL能夠生成正確的執(zhí)行計劃。 BDB和InnoDB表有作用。5.對于MyISAM表，本語句與使用6.對表進行分析的范例 table*****************檢查表1.檢查表的語法如下checktable表名1[,表名2] option={QUICKFAST|MEDIUM|EXTENDED2.檢查表的作用是檢查一個或多個表是否有錯誤3.checktable對MyISAM和InnoDB4.對MyISAM表，關(guān)鍵字?jǐn)?shù)據(jù)數(shù)據(jù)被更新，就可以checktable12/(1)首先我們創(chuàng)建一個視圖 select*from(2)然后check一下該視圖，發(fā)現(xiàn)沒有問題check (3)現(xiàn)在刪除掉該視圖依賴的表drop (4)再來check一下剛才的視圖，發(fā)現(xiàn)報錯了check *************優(yōu)化表1.優(yōu)化表的語法如下optimize[local|table表名1varchar，blobtext使用optimizetable命令來進行表優(yōu)化。4.但是optimizetable命令只對MyISAM、BDBInnoDB起作5.操作范optimize**************常用SQL的優(yōu)化1.大批量插入13/2.優(yōu)化insert3.優(yōu)化groupby4.優(yōu)化order2.優(yōu)化insert3.優(yōu)化groupby4.優(yōu)化orderby5.優(yōu)化嵌套查6.優(yōu)化or7.使用SQL***************提高MyISAM表的導(dǎo)入效率alter disablethe tablet1enable3.其中disablekeysenablekeys打開或者關(guān)閉MyISAM6.上面MyISAM數(shù)據(jù)導(dǎo)入時的優(yōu)化措施，對于InnoDB************提高InnoDB表的導(dǎo)入效率因為InnoDB型的表是按照主鍵的順序保存的，所以將導(dǎo)入的數(shù)在導(dǎo)入數(shù)據(jù)前執(zhí)行setunique_checks=0關(guān)閉唯一性校驗，在導(dǎo)入結(jié)束后執(zhí)行setunique_checks=1恢復(fù)唯一性校驗，提高效率。14/3.如果應(yīng)用使用自動提交的方3.如果應(yīng)用使用自動提交的方式，建議在導(dǎo)入前執(zhí)行setautocommit=0來關(guān)閉自動提交，導(dǎo)入結(jié)束后再執(zhí)行set************優(yōu)化insert語句1.當(dāng)進行數(shù)據(jù)insert的時候，可以考慮采用以下幾種優(yōu)化方式如果同時從同一客戶插入很多行，盡量使用多個值表的insert句，這種方式將大大縮減客戶端與數(shù)據(jù)庫之間的連接、關(guān)閉等消耗，使得效率比分開執(zhí)行的單個insert如果從不同客戶插入很多行，能通過使用insertdelayed語句得到更高的速度，delayed的含義是讓insert語句馬上執(zhí)行，其實數(shù)據(jù)都如果進行批量插入，可以增加bulk_insert_buffer_size變量值的方式來提高速度，但是，這只能對MyISAM表使用。7.當(dāng)從一個文本文件裝載一個表時loaddatainfile常比使用很多insert語句快20倍。****************優(yōu)化groupby句1.默認(rèn)情況下，MySQLbyc1，c2字段進行排序這與在查詢中指orderbyc1，c2似因此，如果顯式包含相同的列的orderby句，則對MySQL如果查詢包括groupby，但是用戶想要避免排序結(jié)果的消耗，可以指定orderbynull來禁止排序。4.比如explain id，sum(money)fromt1groupby15/5.在4被執(zhí)行的時候，發(fā)現(xiàn)有一個filesort6.禁止排序范5.在4被執(zhí)行的時候，發(fā)現(xiàn)有一個filesort6.禁止排序范例explain id，sum(tt)fromt1groupbyidorderby**************優(yōu)化orderby句1.在某些情況中，MySQL以使用一個索引來滿足orderby，2.whereorderby使用相同的索引，并且orderby順序和索引順序相同，并orderby的字段都是升序或者都是降序。3.比如下列SQL就可以使用索引select*t1orderbykey_part1 key_part= select*t1 key_part2select*t1orderbykey_part1desc,key_part24.但是在下面幾種情況下則不使用索(1)orderby字段混ascselect*from orderbykey_part1desc，key_part2(2)用于查詢行的關(guān)鍵字與orderby中所使用的不同select*fromt1wherekey2= orderby(3)對不同的關(guān)鍵字orderselect*fromt1orderby**************優(yōu)化嵌套子查詢1.MySQL4.1始支持SQL查詢，這個技術(shù)可以使用select句2.使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完的SQL操作，同時也可以避免失誤或者表鎖死，而且寫起來很容易16/3.在很多情況下，子查詢可以被更有3.在很多情況下，子查詢可以被更有效率的join替代4.比如在t1表中找到那些在t2表中不存在的所有公司信息select*fromt1wherecomany_idnotin(selectidfrom5.我們可以使用連接來優(yōu)化它，尤其t2表中對id有索引的話，select*fromt1leftjoint2pary_id=t2.idwherepany_idisnull;*************優(yōu)化or條件1.對于含有or查詢子句，如果要利用索引，則or間的每個條件2.MySQLor的時候，實際上是or各個字段分別查詢后的結(jié)果進行了union。*****************使用SQL提示1.SQL示(SQLHINT)是優(yōu)化數(shù)據(jù)庫的一個重要手段，簡單來說就是在SQL語句中加入一些人為的提示來達到優(yōu)化操作的目的。2.下面是一些常用的SQL提示useindexignoreindex、forceindex3.比如select sql_buffer_results*from...這個語句強制MySQL生成一個臨時結(jié)果集，只要臨時結(jié)果集生成后，所有表上的鎖定均釋放1.在查詢語句中表名的后面useindex提供mysql考的索引列表，就可以讓mysql不再考慮其他可用的索引。2.范例17/ select*fromt1useindex(ind_t1)id= select*fromt1useindex(ind_t1)id=3****************ignore使用ignoreindex作為hint。2.范例explainselect*fromt1ignoreindex(ind_t1)whereid=3*****************forcehint比如，當(dāng)不強制使用索引的時候，因為id值都是大于0此MySQL會默認(rèn)進行全表掃描，而不使用索引。即：explainselect*fromt1whereid>03.當(dāng)使用forceindex行提示時，即便使用索引的效率不是最高，MySQL是選擇了使用索引，這是MySQL給用戶的一個自行選explainselect*fromforceindex(id_xx)whereid**************小結(jié)1.SQL優(yōu)化問題是數(shù)據(jù)庫性能優(yōu)化最基礎(chǔ)也是最重要的一個問2.很多數(shù)據(jù)庫性能問題都是由不合適的SQL語句造成的18/第三節(jié)：數(shù)據(jù)庫對******************一些問題1.是否應(yīng)該第三節(jié)：數(shù)據(jù)庫對******************一些問題1.是否應(yīng)該把所有表都按照第三范式來設(shè)計2.表里面的字段到底設(shè)置為多大長度合適********************解決方案1.優(yōu)化表的數(shù)據(jù)類2.通過拆分提高表的訪問效3.逆規(guī)范4.使用中間表來提高統(tǒng)計查詢*****************優(yōu)化表的數(shù)據(jù)類型1.表需要使用何種數(shù)據(jù)類型，是需要根據(jù)應(yīng)用來判斷的analyse()對當(dāng)前應(yīng)用的進行分析，該函數(shù)可以對數(shù)據(jù)表中列的數(shù)據(jù)類型提出優(yōu)化建議，戶可以根據(jù)應(yīng)用的實際情況酌情考慮是否實施優(yōu)化4.范例select*fromt1procedureselect*fromt1procedure5.以上的第二個語句告訴procedureanalyse()不要為那些包含的值多于16256節(jié)的enum型提出建議。如果沒有這樣的限6.輸出的每一列信息都會對數(shù)據(jù)表中的列的數(shù)據(jù)類型提出優(yōu)化建議19/************通過拆分提高表的訪問效率************通過拆分提高表的訪問效率1.這里所說的“拆分”，是值對數(shù)據(jù)表進行拆分2.如果是針對MyISAM類型的表進行拆分，有兩種方法3.水平拆分在以下幾種情況下使用(3)需要把數(shù)據(jù)存放到多個介質(zhì)上水平拆分會給應(yīng)用增加復(fù)雜度，它通常在查詢時需要多個表名查詢所有數(shù)據(jù)需要union操作要索引關(guān)鍵字不大，則在索引用于查詢時，表中增加23的數(shù)****************逆規(guī)范化20/反規(guī)范的好處是降低連接操反規(guī)范的好處是降低連接操作的需求、降低外碼和索引的數(shù)目還可能減少表的數(shù)目****************反規(guī)范技術(shù)1.常用的反規(guī)范技術(shù)有增加冗余列、增加派生列、重新組表和分割所謂增加派生列，就是增加的列來自其他表中的數(shù)據(jù)，由其他表中的數(shù)據(jù)經(jīng)過計算生成。增加的派生列其作用就是在查詢時減少連5.分割表：也就是對表進行垂直和水平拆分*****************注意點1.逆規(guī)范化技術(shù)需要維護數(shù)據(jù)的完整的完整性風(fēng)險較大，因為同一邏輯必須在所有的應(yīng)用中使用和維護，21/***************使用中間表提高統(tǒng)計查詢速度2.在這種情況下，使用中間表可以提高統(tǒng)計查詢的效率3.比如session記錄了客戶每天的消費記錄，那么當(dāng)我們想分析最近一周客戶的消費情況的時候，可以創(chuàng)建一個tmp_session表，它只擁有session表的部分?jǐn)?shù)據(jù)，在該中間表上的操作會快很多。中間表在統(tǒng)計查詢中經(jīng)常會用到，其優(yōu)點如下*************小結(jié)1.數(shù)據(jù)庫對象設(shè)計的好壞是一個數(shù)據(jù)庫設(shè)計的基礎(chǔ)22/第四節(jié)：鎖問*****************說明1.鎖是計算第四節(jié)：鎖問*****************說明1.鎖是計算機協(xié)調(diào)多個進程或線程并發(fā)訪問某一資源的機4.鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素5.從這個角度來說，鎖對數(shù)據(jù)而言尤其重要，也更加復(fù)雜***************mysql鎖概述1.相比其他數(shù)據(jù)庫而言，mysql的鎖機制比較簡單它最顯著的特點就是不同的存儲引擎支持不同的鎖機制MyISAM和memory存儲引擎采用的是表級鎖(table-levelBDB存儲引擎采用的是頁面鎖(levellocking),但是也支持表locking),也支持表級鎖因此，很難說哪種鎖更好，只能根據(jù)具體應(yīng)用來判斷哪種鎖更適條件更新數(shù)據(jù)的應(yīng)用，比如web應(yīng)用。23/12.由于BDB已經(jīng)被InnoDB取代，成為歷史，因此就不介紹了*******************MyISAM表鎖1.MyISAM儲引擎只支持表鎖，這也是mysql始幾個版本中唯2.隨著應(yīng)用對事務(wù)完整性和并發(fā)性要求的不斷提高，mysql始開發(fā)基于事務(wù)的存儲引擎。后來慢慢出現(xiàn)了支持頁所的BDB引擎和支持行級鎖的InnoDB引擎(實際InnoDB是單獨的一個公司，后被oracle收購).但是MyISAM的表鎖依然是使用最為廣泛的鎖類可以通過檢查table_locks_waitedtable_locks_immediateshowstatuslike如果Table_locks_waited比較高，說明存在著比較嚴(yán)重的表*********************表級鎖的鎖模式1.mysql的表級鎖模式有兩種(1)表共享讀鎖(TableRead(2)表獨占寫鎖(TableWrite3.對MyISAM表的寫操作，會阻塞其他用戶對同一表的讀和4.MyISAM表的讀操作和寫操作之間，以及寫操作之間，是串行的24/*************MyISAM寫阻塞范例1.這里開啟了兩個session，我*************MyISAM寫阻塞范例1.這里開啟了兩個session，我們不妨簡單記為a和b2.at1write定locktable 在b中對鎖定表的查詢會被阻塞，需要等待鎖被釋放。4.在a釋放鎖unlock5.在b中會獲得資源，查詢返**************如何加表鎖1.MyISAM在執(zhí)行查詢語句(select)前，會自動給涉及的所有表加讀鎖，在執(zhí)行更新操作(update、delete、insert會自動給涉及table命令給MyISAM表顯式加鎖。3.上述例子中的顯式加鎖是為了方便查看給MyISAM表顯式加鎖，一般是為了在一定程度上模擬事務(wù)操作，在locktable的時候加了“l(fā)ocal”選項，其作用就是在滿足MyISAM并發(fā)插入條件的情況下，允許其他用戶在表尾并發(fā)插入lock t1readlocal，t2read7tables給表顯式加鎖時，必須同時取得所有涉及表的鎖能訪問顯式加鎖的這些表，不能訪問為加鎖的表25/MyISAM不會出現(xiàn)死鎖(DeadlockFree)的原因。*********MyISAM讀阻塞范例1.這里取得兩session，分別記a和b2a獲t1的read定locktablet1可以在a中查詢該表記錄，也可以在其他session中查詢該表的記但是在a中不能查詢沒有鎖定的表，在b中可以查詢或者更新未在a中插入或者更新鎖定的表會提示錯誤，在b中更新鎖定表會6.在a中使用unlocktables；來釋放鎖，在b中的更新操作完****************操作說明1.當(dāng)使用locktables時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL句中出現(xiàn)所少次，就要通過SQL句中相同的2.對actor表獲得讀>lock actor3.但是通過別名訪問會提示錯 fromactora4.需要對別名分別鎖定>lock actorasaread,actorasb5.之后的操作就正常了*****************并發(fā)插入1.由于MyISAM表的讀和寫是串行的，但是它是就總體而26/2.在一定條件下，MyISAM表也支2.在一定條件下，MyISAM表也支持查詢和插入操作的并發(fā)進行控制其并發(fā)插入的行為，它的取值可以為0、1、2。當(dāng)concurrent_insert為0時，不允許并發(fā)插入concurrent_insert1如果MyISAM中沒有空洞(即表的中間沒有被刪除的行),MyISAM允許在一個進程中讀表的同時，另一個進程從表尾插入記錄。這也是mysql的默認(rèn)設(shè)置。6concurrent_insert2無論MyISAM中有沒有空洞，都也就是說當(dāng)某個session表執(zhí)locktablet1read；的時候另一個表是可以執(zhí)行insert的，但是update會被阻塞可以利用MyISAM儲引擎的并發(fā)插入特性，來解決應(yīng)用中對同9.比如我們可以將concurrent_insert統(tǒng)變量設(shè)置為2，總是允許并發(fā)插入。同時，通過定期在系統(tǒng)空閑時optimizetable**************MyISAM的鎖調(diào)度1.MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串一表的寫鎖，MySQL會如何處理呢?答案是寫進程先獲得鎖。因為MySQL會認(rèn)為寫請求一般比讀請求更重要這也正是MyISAM不太適合有大量更新操作和查詢操作應(yīng)用的不過我們可以通過一些設(shè)置細(xì)節(jié)來調(diào)節(jié)MyISAM調(diào)度行為，通27/通過指定啟動參數(shù)：low-priority-updates，使得MyISAM通過指定啟動參數(shù)：low-priority-updates，使得MyISAM默認(rèn)給予讀請求以有限的權(quán)通過執(zhí)行命令setlow_priority_updates=1，使該連接發(fā)出的另外，mysql也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個合適的值，當(dāng)一個表的讀鎖達到這個值后，mysql就暫時將寫請求的優(yōu)先級降低，給讀進程一定語句解決問題，分解往往能夠減少鎖沖突***************InnoDB鎖問題1.InnoDB與MyISAM的最大不同在于兩點(1)支持事務(wù)(2)采用行級鎖*****************事務(wù)也就是acid屬性。原子性(atomicity):事務(wù)是一個原子操作單元，其對數(shù)據(jù)的修要么全都執(zhí)行，要么全都不執(zhí)行28/持?jǐn)?shù)據(jù)的完整性，事務(wù)結(jié)束時持?jǐn)?shù)據(jù)的完整性，事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)(比如B向鏈表)也都必須時正確的****************并發(fā)事務(wù)帶來的問題2.并發(fā)事務(wù)可能會出現(xiàn)的問題如下更新丟失、臟讀、不可重復(fù)讀、幻3.所謂更新丟失(lostupdate)，也就是當(dāng)兩個或多個事務(wù)選擇同一行，4.臟讀(dirtyreads):一個事務(wù)正在對一條記錄做修改，在這個事務(wù)5.不可重復(fù)讀(non-repeatablereads):一個事務(wù)在讀取某些數(shù)據(jù)后的讀”6.幻讀reads):一個事務(wù)按相同的查詢條件重新讀取以檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)這種現(xiàn)象稱為“幻讀7.這四種問題兩種因為更新、一種因為刪除、一種因為插入29/**************事務(wù)隔離級別1.上面提**************事務(wù)隔離級別1.上面提到的并發(fā)事務(wù)處理帶來的問題中，更新丟失通常是應(yīng)該全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫事務(wù)控制器來解決，丟失應(yīng)該是應(yīng)用的責(zé)任3.數(shù)據(jù)庫實現(xiàn)事務(wù)隔離的方式，基本可以分為兩種(1)在讀數(shù)據(jù)之前，對其加鎖，防止其他事務(wù)對數(shù)據(jù)修改ConcurrencyControl,簡稱MVCC或MCC),也經(jīng)常稱為多版本數(shù)據(jù)4.數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用就越小，但付出的代價就越大，因為事務(wù)隔離機制實質(zhì)上是使事務(wù)在一定程度上“串行化”5.因此，不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的。數(shù)據(jù)并發(fā)訪問的能力6.為了解決“隔離”與“并發(fā)”的矛盾，ISO/ANSISQL92定義了47.這四個隔離級(1)未提交讀(read最低級別，只能保證不讀取物理上損壞的數(shù)據(jù)可能出現(xiàn)：臟讀、不可重復(fù)讀、幻30/(2)以提交讀(read語句不會出現(xiàn)(2)以提交讀(read語句不會出現(xiàn)臟讀、但是可能出現(xiàn)：不可重復(fù)讀、幻(3)可重復(fù)讀(repeated事務(wù)不會出現(xiàn)臟讀、不可重復(fù)讀，可能(4)可序列化最高級別，事不可能出現(xiàn)臟讀、不可重復(fù)讀、幻8.各具體數(shù)據(jù)庫并不一定完全實現(xiàn)了上述4個隔離級別，比如oracle提供readcommitted和serializable個標(biāo)準(zhǔn)隔離級別，還提供自定義的readonly級別。9.SQLServer除了支持上述定義的4個級別之外，還支持一個“快照”的隔離級別，但嚴(yán)格來說它是一個用MVCCSerializable10.MySQL支持全部的四個隔離級別，但是在具體實現(xiàn)時，有一些特點，比如在一些隔離級別下面采用MVCC致性讀，有些情況下***************InnoDB行鎖爭用情況檢查Innodb_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情 statuslike如果發(fā)現(xiàn)鎖爭用比較嚴(yán)重，比如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高，還可以通過設(shè)置InnoDB3.具體操>createtableinnodb_monitor(aint)engine=31/>show #當(dāng)停止查看時，可以>droptable>show #當(dāng)停止查看時，可以>droptable在設(shè)置監(jiān)視器后，在showinnodbstatus細(xì)的當(dāng)前鎖等待的信息，包括表名、鎖類型、鎖定記錄的情況等等，在打開監(jiān)視器后，默認(rèn)情況下每15秒會向日志中記錄監(jiān)控的內(nèi)容，如果長時間打開會導(dǎo)致.err文件變得跟大，所以用戶在確認(rèn)問題原”***************InnoDB鎖分析1.InnoDB實現(xiàn)了以下兩種類型的鎖2.為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機制，InnoDB有兩種內(nèi)部使用的意向鎖(IntentionLocks)，這兩種鎖都是表鎖。加共享鎖前必須先取得該表的IS鎖。行加排他鎖之前必須先取得該表的IX鎖。5.各個鎖的兼容沖突情況如下X和所有鎖都沖突IXIXS兼容S和ISIXS32/7.意向鎖是InnoDB自動加的，不用用戶干9.對于select語句，InnoDB不會加任何所10.事務(wù)可以根據(jù)通過如下語句顯示給記錄集加共享鎖或排他鎖(1)共享鎖select*fromwhere inshare(2)排他鎖select*from11.select..inwhereformode獲得共享鎖，主要用在數(shù)據(jù)依存關(guān)系來確認(rèn)某行記錄是否存在，并確認(rèn)沒有人對這個記錄update者delete操作。如果當(dāng)前事務(wù)也需要對該記錄進行更新操作，很可用selectforupdate方式獲得排他鎖。***********實例操作InnoDB存儲引擎共享鎖1.這里開啟了兩個session，分別記為a和b2a和b分autocommit=0來關(guān)閉自動提交，此是可以在兩個事務(wù)中對同一行記錄進行select3.在a中對某條記錄加共享鎖select lockinshare4.此時在其他session以查詢記錄，也可以對該記錄加sharemode的共享鎖。5.在a中對鎖定的記錄進行更新操作，會等待鎖6.在b中對該記錄進行更新操作，則會死鎖退7.在a中獲得鎖后，更新成功33/*************InnoDB排他鎖示例1.這里開啟了兩個session，分別記為*************InnoDB排他鎖示例1.這里開啟了兩個session，分別記為a和b2a和b中都設(shè)置setautocommit=0關(guān)閉自動提交，此時兩3.在a中對某條記錄添加共享select for4.在b以查詢該記錄，但是不能對該記錄加共享鎖，加鎖的時commit操作)。6.此時b中獲得鎖，也就是在第4步中的加排他鎖會*************InnoDB行鎖實現(xiàn)方式1.InnoDB鎖是通過給索引項加鎖來實現(xiàn)的，而oracle是通過在2.InnoDB這種行鎖意味(1)只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖(2)否則，InnoDB使用表鎖********InnoDB在不使用索引時使用表鎖范例2.并且在a和b中設(shè)置setautocommit=0閉自動提交，注意這里的t1沒有索引。3.當(dāng)我們在a中加共享select*fromt1whereid=1for4.此時在b中加共享鎖會等待select*from whereid=2for5.雖然在上例中我們對t1只加了一行排他鎖，但是卻鎖定了34/6.其原因就是因為沒有索引6.其原因就是因為沒有索引的情況下，InnoDB只能使用表****************使用相同的索引鍵會出現(xiàn)沖突1.由于MySQL行鎖是針對索引加的鎖，不是針對記錄加的鎖，所2.比如在開啟了事務(wù)的兩個session中，在session1select*fromt1whereid1andname=’1’forupdate;3.在session2中訪問不同的記錄：select*fromt1whereid=1andname=’4’for4.雖然session2訪問的是不同的記錄，但是因為使用了相同的索************多個索引的阻塞情況3.比如在兩個session中都開啟事務(wù)，并且在session1select*fromt1whereid=1此時在session2中使用如下select*fromt1wherename=’4’for由于此記錄已session1定，所session2被阻塞，來等待*************說明即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是MySQL通過判斷不同執(zhí)行計劃的代價來決定的如果MySQL全表掃描效率更高，比如對一些很小的表，它就35/3.在不使用索引的情況下，InnoDB就會使3.在不使用索引的情況下，InnoDB就會使用表鎖，而不是行鎖***********************間隙鎖排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖InnoDB也會對這個間隙加鎖，這種鎖機制就是所謂的間隙鎖(Next-Key鎖)。3.InnoDB使用間隙鎖的目的(1)為了防止幻讀，以滿足相關(guān)隔離級別的要求(2)為了滿足其恢復(fù)和復(fù)制的需在實際開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用，我們要盡量優(yōu)業(yè)務(wù)邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件錄加forupdate鎖：select*fromt1whereempid=102for8.在另session們插入empid202記錄，也會出現(xiàn)9.只有sessionrollback后，釋放了Next-Key他session才可以獲得鎖并且成功插入記錄。******恢復(fù)和復(fù)制對InnoDB鎖機制的影響1.MySQL通過binlog記錄執(zhí)行成功的insert、update、delete新數(shù)據(jù)的SQL句，并由此實現(xiàn)MySQL數(shù)據(jù)庫的恢復(fù)和主從復(fù)制36/2.復(fù)制其實就是slavemysql斷2.復(fù)制其實就是slavemysql斷做基binlog恢3.MySQL的恢復(fù)機制有如下特點MySQL的恢復(fù)是SQL語句級的，也就是重新執(zhí)行binlogSQL語句，而oracle是基于數(shù)據(jù)庫文件塊的MySQL的binlog按照事務(wù)提交的先后順序記錄的，恢復(fù)也是按這個順序進行的。這點與oracle不同，oracle是按照系統(tǒng)更新號number，SCN)來恢復(fù)數(shù)據(jù)的，每個事務(wù)開始時5.這也是許多情況下，InnoDB用到間隙鎖的原因，比如在用范圍條件更新記錄時，無論時在readcommited還是repeatableread1.對于“insertintot1select*fromt2where和“createtablet1fromt2...”這種SQL句，用戶并沒有對t2任何操作，但是MySQL對這種SQL語句做了特殊處理。2.當(dāng)我們在兩個session啟事務(wù)，在一個session進行CTAS句的時候，在另一個session是無法對t2表加排他鎖，也就是說無雖然說對t2表只是一個簡單的select作，用一致性讀就可以了，oracle也是這么做的，它通過MVCC技術(shù)實現(xiàn)的多版本數(shù)據(jù)來實現(xiàn)一致性讀，不需要給t2任何所，但是InnoDB給t2了共享鎖，MySQL所以這么做，就是為了保證恢復(fù)和復(fù)制的正確性，因為不加鎖的話，如果其他事務(wù)對t2了更新操作，可能導(dǎo)致數(shù)據(jù)恢復(fù)5.我們用下面的操作范例來展示可能出現(xiàn)的問題37/********CTAS操作不給原表加鎖********CTAS操作不給原表加鎖帶來的安全問題這里開啟兩個session，分別記為a和b，我們在a和b都設(shè)置setautocommit=0，但是在a中進行setinsertintot1select*fromt2wherename‘1’;之后可以在b中進行進行操作：updatet2setname=’8’wherename=首先bcommit，然a中commit4.此時在兩個表中查看數(shù)據(jù)，結(jié)果也是符合邏輯的5.但是，在binlog，更新操作的位置在insert.select前，如果使用整個binlog進行數(shù)據(jù)恢復(fù)，恢復(fù)的結(jié)果就會與實際的應(yīng)用邏輯不如果CTASselect范圍條件，InnoDB會給原表添加因此，insertselectcreatetableselect...語句，坑你會阻將innodb_locks_unsafe_for_binlog的值設(shè)置為on，強制MySQL用多版本數(shù)據(jù)一致性讀，它的代價就是無法binlog(2)使用select*fromintooutfile和loadinfile語句組來間接實現(xiàn)，采用這種方式MySQL不會source_tab加鎖38/*********不同隔離級別下的一*********不同隔離級別下的一致性讀及鎖的差異鎖和多版本數(shù)據(jù)是InnoDB現(xiàn)一致性ISO/ANSISQL92在不同的隔離級別下，InnoDB處理SQL時采用的一致性讀策略數(shù)據(jù)恢復(fù)和復(fù)制機制的特點，也對一些SQL的一致性讀策略和鎖因此我們在應(yīng)用中，應(yīng)該盡量使用較低級別的隔離級別，以減鎖爭用的幾率通過優(yōu)化事務(wù)邏輯，大部分應(yīng)用使用readcommited隔離級就夠行setsessiontransactionisolationlevelrepeatableread或者setsessiontransactionisolationlevelserializable動態(tài)改變事務(wù)隔離級****************什么時候使用表鎖1.對于InnoDB在絕大部分情況下都應(yīng)該使用行級鎖，因為事務(wù)和行鎖往往是我們選擇InnoDB表的理由。2.個別事務(wù)中可以考慮使用表級鎖)事務(wù)需要更新大部分或者全部數(shù)據(jù)，表又比較大，如果使用默認(rèn)的行鎖，會使得事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長時間行速度。3.這種事務(wù)不能太多，否則，就應(yīng)該考慮使用MyISAM表了39/**************InnoDB的表鎖1.雖然使用lock**************InnoDB的表鎖1.雖然使用locktables雖然可以給InnoDB加表級鎖，但是表鎖并不是由InnoDB儲引擎管理的，而是由MySQLserver責(zé)的，僅僅當(dāng)autocommit=0,、innodb_table_locks=1(默認(rèn)設(shè)置)時，InnoDB層才能知道MySQL加的表鎖，MySQLServer也才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖，否則InnoDB將無法自動檢測并處理這種死鎖2.使用locktables對InnoDB表鎖時要注意，要將autocommit設(shè)置為0，否則MySQL不會給表加鎖，事務(wù)結(jié)束前，不要用unlocktables放表鎖，因為它會隱含的提交事務(wù)，commit者rollback并不能釋放用locktables加的表級鎖，必須用unlocktables釋放表鎖。3.正確操作范例setautocommitlocktablest1write，t2 commitunlock*************死鎖1.MyISAM是deadlockfree這是因為MyISAM是一次獲得所這就決定了在InnoDB中發(fā)生死鎖的可能。3.比如在兩個session中都設(shè)置autocommit為0，在第二個中 fromt1whereid=1forupdate，select*fromt1whereid=1forupdate；然后做一些其他處理。4.此時第一個session中進行select*fromt2whereid=1forupdate；此時會進入等待狀態(tài)，而第二個再進行select*fromt1 id=1forupdate；的時候，就進入了死鎖。40/當(dāng)兩個事務(wù)都需要獲當(dāng)兩個事務(wù)都需要獲得對方持有的排他鎖才能繼續(xù)完成事務(wù)，種循環(huán)鎖等待就是典型的死在設(shè)計到外部鎖、表鎖的情況下，InnoDB并不能完全自動檢測到鎖，這需要設(shè)置鎖等待超時innodb_lock_wait_timeout來解決。****************死鎖的解決庫對象設(shè)計、事務(wù)大小、訪問數(shù)據(jù)庫的SQL句，絕大部分死鎖都訪問表，這樣可以大大降低產(chǎn)生死鎖的機會。當(dāng)兩個session問表的順序不同的時候，發(fā)生死鎖的幾率會增加，以相同的順序來訪問，在程序批量方式處理數(shù)據(jù)的時候，如果事先對數(shù)據(jù)排序，保證個線程按固定的順序來處理記錄，也可以大大降低出現(xiàn)死鎖的可能申請排他鎖的時候，其他事務(wù)可能又已經(jīng)獲取了相同記錄的共享鎖，5.在repeatable-read隔離級別下，如果兩個線程同時對相同條 update加排他鎖，在沒有符合該條件記錄情況下隔離級別改為readcommited就可以避免問題。6.當(dāng)隔離級為readcommitted時，如果兩個線程都先執(zhí)行select...forupdate，判斷是否存在符合條件的記錄，如果沒有，就等待，當(dāng)?shù)谝粋€線程提交后，第二個線程會因主鍵重排出錯，雖41/行rollback釋放獲得的排他鎖。***********************說明1.如果出現(xiàn)死鎖，可showinnodbstatus令來確定最后一個2.返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細(xì)信息，如引發(fā)死鎖的SQL語句，事務(wù)已經(jīng)獲得的鎖，正在等待什么鎖，以及被回滾的事務(wù)等等。*****************小結(jié)1.在MyISAM，共享讀鎖(S)之間是兼容的，但共享讀鎖(S)與排他在一定條件下，MyISAM允許查詢和插入并發(fā)執(zhí)行，我們可以用這一點來解決應(yīng)用中對同一表查詢和插入的鎖爭用問題MyISAM偶人的鎖調(diào)度機制是寫優(yōu)先，這并不一定適合所有應(yīng)用，用戶可以通過設(shè)置low-priority_updates參數(shù)，或者在insert、update、delete語句中指定low_priority選項來調(diào)節(jié)讀寫鎖的爭用。操作較多，MyISAM表可能會出現(xiàn)嚴(yán)重的鎖等待，可以考慮用InnoDB表來減少鎖沖突。5.對于InnoDB表，主要有幾項內(nèi)容據(jù)，InnoDB會使用表鎖。(2)InnoDB的間隙鎖及其原因(3)在不同的隔離級別下，InnoDB的鎖機制和一致性讀策略不同(4)MySQLInnoDB機制和一致性讀策略也有較(5)鎖沖突甚至死鎖很難完全避42/*************減少鎖沖突和死*************減少鎖沖突和死鎖的方案1.盡量使用較低的隔離級別3.選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率更小43/第五節(jié)：優(yōu)化**************說明1.當(dāng)服務(wù)第一次啟動第五節(jié)：優(yōu)化**************說明1.當(dāng)服務(wù)第一次啟動的時候，所有的啟動參數(shù)都是系統(tǒng)默認(rèn)的*************查看MySQLServer數(shù)MySQL務(wù)啟動后，我們可以showvariables和showstatusshowvariables是在數(shù)據(jù)庫啟動后不會動態(tài)更改的值，比如緩沖showstatus是數(shù)據(jù)庫運行期間會動態(tài)變化的信息，比如鎖等待、 - -mysqld--verbose--6.比如查看當(dāng)前服務(wù)器字符集的設(shè)置，可以使用mysqld--verbose--help|grepcharacter-set-*******************重要參數(shù)44/官方對key_buffer_size定義是”官方對key_buffer_size定義是”thesizeofthebufferusedforindexblocksforMyISAM”。該參數(shù)是用來設(shè)置索引塊(IndexBlocks)索引的大小，它被所有線程共享，此參數(shù)只適用于MyISAM存儲引擎。MySQL5.1之前只允許使用一個系統(tǒng)默認(rèn)的key_buffer，而MySQL5.1后提供了多個key_buffer，可以將制定的表索引緩存入制定的key_buffer，這樣可以更小地降低線程之間的競爭。4.建立一個索引緩存的范例setglobalhot_cahe2.key_buffer_size=其中g(shù)lobal表示對每一個新的連接，此參數(shù)都生效，而hot_cache2新的key_buffer稱，可以重新用set句來對它進5.然后可以把相關(guān)表的索引放到指定的索引緩存中，比如cacheindext1，t2in6.要想索引預(yù)裝到默認(rèn)key_buffer，可以使用loadindexintocache語句，范例：loadindexintocache7.如果要刪除索引緩存，可以使用如下命令setglobalhot_cache2.key_buffer_size=8.查看默認(rèn)的key_buffer的大小，使用45/ variableslike9.如果是刪除默認(rèn)的key_buffer， variableslike9.如果是刪除默認(rèn)的key_buffer，雖然可以使用下面語句刪setglobalkey_buffer_size=但是我們使用showwarnings發(fā)現(xiàn)并沒有刪除cacheindex令在一個key_buffer建立一種聯(lián)系，但每次服務(wù)器重啟時key_buffer中的數(shù)據(jù)將清空。如果想要每次服務(wù)器重啟時相應(yīng)表的索引能自動放到key_buffer中，可以在配置文件中設(shè)置init-file項來指定包含cacheindex句的文件路徑，然后在對應(yīng)的文件中寫入cacheindex語句。12.比如在f中設(shè)置如下key_buffer_size=hot_cache.key_buffer_size=cold_cache.key_buffer_size=init_file=/path/to/data-13.然后在mysqld_init.sqlcache a.t1,a.t2,b.t3incachea.t4,b.t4,b.t5inMySQL方對table_cache定義如下”Thenumberofopentablesforallthreads”.這個參數(shù)表示數(shù)據(jù)庫用戶打開表的緩存數(shù)max_connections有關(guān)。4.比如，對于200個并行運行的連接，應(yīng)該讓表的緩存至少200xN個，這里N是可以執(zhí)行的查詢的一個連接中表的最大數(shù)量5.此外，還需要為臨時表和文件保留一些額外的文件描述符46/確定這個參數(shù)是否過flushtables作，則此系統(tǒng)會關(guān)閉一些當(dāng)前沒有使用的表緩存而使flushtables操作，值不會減小。9.查看操作如下 globalstatuslike‘open_tables’; globalstatuslike‘opened_tables’;1官方對它的定義如下”thesizethememorybufferinnodbtocachedataandindexesofitstables”2.這個參數(shù)定義了InnoDB存儲引擎的表數(shù)據(jù)和索引數(shù)據(jù)的最大存緩沖區(qū)大小3MyISAM不同的是，MyISAM的key_buffer_size引鍵，而innodb_buffer_pool_size同時為數(shù)據(jù)塊和索引塊做緩存需要的磁盤IO就越小。存大小的80%。對這個參數(shù)的設(shè)置可以對數(shù)據(jù)庫在性能與數(shù)據(jù)安全之間進行折中當(dāng)這個參數(shù)是0時候，日志緩沖每秒一次地被寫到日志文件，47/當(dāng)這個參數(shù)是1的時候當(dāng)這個參數(shù)是1的時候，在每個事務(wù)提交時，日志緩沖被寫到志文件，并且對日志文件做向磁盤刷新的操當(dāng)這個操作是2時候，在每個事務(wù)提交時，日志緩沖被寫到日候都會從logbuffer寫到日志文件，并且會實際刷新磁盤，但是這7.如果可以容忍在數(shù)據(jù)庫崩潰的時候損失一部分?jǐn)?shù)據(jù)，那么設(shè)置0者2會有所改善設(shè)置為0，則在數(shù)據(jù)庫崩潰的時候會丟失那些沒有被寫入日志文件的事務(wù)，最多丟失1秒鐘的事務(wù)，這種方式是最不安全的，也是設(shè)置為2，因為只是沒有刷新到磁盤，但是已經(jīng)寫入日志文件，所以只要操作系統(tǒng)沒有崩潰，那么并沒有丟失數(shù)據(jù)，比設(shè)置為010.建議設(shè)置為1.這個參數(shù)是InnoDB儲引擎用來存儲數(shù)據(jù)庫結(jié)構(gòu)和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池的大小，其默認(rèn)值是1MB。2.應(yīng)用程序里的表越多，則需要在這里分配越多的內(nèi)存3.如果InnoDB光了這個池內(nèi)的內(nèi)存，則InnoDB始從操作系統(tǒng)分配內(nèi)存，并且往MySQL錯誤日志寫警告信息。況下，這個緩沖池的大小也相對穩(wěn)定，系統(tǒng)默認(rèn)值為1MB。官方的定義為”TimeoutinsecondsanInnoDBtransactionmaywaitforalockbeforebeingrolledback”。系統(tǒng)默認(rèn)值為50秒48/MySQL可以自動地監(jiān)測行鎖導(dǎo)致的死MySQL可以自動地監(jiān)測行鎖導(dǎo)致的死鎖并進行相應(yīng)的處理對于表鎖的死鎖不能自動的檢測官方給出的解釋是“EnableInnoDBsupportforthexatwo-phasecommit”。通過該參數(shù)設(shè)置是否支持分布式事務(wù)，默認(rèn)值為on者1，表示減少磁盤刷新的次數(shù)并獲得更好的InnoDB性能。官方給出的定義如下”ThesizeofthebufferwhichInnoDBusestowritelogtothelogfiles ondisk”。含義就是日志緩存的大小4.默認(rèn)值為1MB5.如果存在更新操作峰值或者負(fù)載較大，應(yīng)該考慮加大它的值了次，因此無需設(shè)置超過1秒所需的內(nèi)存空間。7.通常它通常設(shè)置為8-16MB就夠了，越小的系統(tǒng)它的值越小1.官方的定義是“sizeofeachlogfileinaloggroup”2.該參數(shù)是一個日志組中每個日志文件的大3.此參數(shù)在高寫入負(fù)載尤其是大數(shù)據(jù)集的情況下很重要4.系統(tǒng)默認(rèn)值為5MB49/******************小結(jié)1.本小節(jié)的題目是優(yōu)化MySQLServer，實際上更多地介紹了一些服2.對服務(wù)器的優(yōu)化，實際也就是對這些參數(shù)的不斷調(diào)整50/第六節(jié)：磁盤***************說明第六節(jié)：磁盤***************說明但數(shù)據(jù)庫最終要將數(shù)據(jù)儲存到可以長久保存的IO設(shè)備--磁盤上。2.當(dāng)然磁盤的存取速度比CPU、RAM的速度慢很多盤IO的壓力及其對性能的影響。5.解決磁盤IO問題，減少或延緩磁盤操作肯定是一個重要方面6.增強磁盤IO本身的性能和吞吐量也是一個重要方7.下面我們從磁盤陣列、符號連接、裸設(shè)備等底層的方面來介紹*****************磁盤陣列1.RAIDRedundantArrayofInexpensiveDisks縮寫，翻譯成2.RAID就是按照一定策略將數(shù)據(jù)分布到若干物理磁盤上4.因為通過分布實現(xiàn)了數(shù)據(jù)的“并行”讀寫5.raid早是用來取代大型計算機上高檔存儲設(shè)備的，相對于那些6.但是很長的一段時間內(nèi)，相對于pc而言，其價格絕對談不上廉價7.不過最近幾年，隨著存儲技術(shù)的發(fā)展，raid開始真正廉價了***********raid級別1.根據(jù)數(shù)據(jù)分布和冗余方式，raid分為許多級別2.不同的存儲廠商提供raid或設(shè)備支raid也不盡相同51/3.raid0化，即stripe，按照一定3.raid0化，即stripe，按照一定的條帶大小(chunksize)將4.raid1也叫磁盤鏡像，即mirror，兩個磁盤一組，所有數(shù)據(jù)都同提高并發(fā)讀性能。缺點是容量一定的話，需2倍的磁盤，投資比5.raid10raid1raid0結(jié)合，也叫raid1+0，先對磁盤做鏡像，再條帶化，其實兼具raid1的可靠性和raid0的優(yōu)良并發(fā)讀寫性能。要2倍的磁盤，投資比較大6.raid4像raid0樣對磁盤組條帶化，不同的是：需要額外增加一個磁盤，用來寫各stripe的校驗糾錯數(shù)據(jù)。優(yōu)點就是raid中的一個保護能力，讀數(shù)據(jù)速度快。缺點就是每個stripe上數(shù)據(jù)的修改都要險大，也會形成一個性能ing經(jīng)，在出現(xiàn)壞盤時，讀性能會下降7.raid5是對raid4的改進，將每一個條帶的校驗糾錯數(shù)據(jù)塊也分布寫到各個磁盤，而不是寫到一個特定的磁盤。優(yōu)點基本同raid4，只是其寫性能和數(shù)據(jù)保護能力要強一點。缺點就是寫性能不及raid0、*************如何選擇raid級別合適的raid級別。數(shù)據(jù)讀寫都很頻繁，可靠性要求也較高的，最好選擇raid4.數(shù)據(jù)讀寫都很頻繁，但可靠性要求不高，可以選擇52/******虛擬文件卷或軟******虛擬文件卷或軟現(xiàn)在的操作系統(tǒng)中提供一些軟件包，也模擬實現(xiàn)了一些raid的特雖然它們性能上不如硬raid，但是相比單個磁盤，性能和可靠性4.比如Linux下的邏輯Volume)系統(tǒng)lvm2，支持條帶化Device)驅(qū)動，支持raid0、raid1、raid45.Linux下的MD(Multipleraid5、raid6等。**************symbolicMySQL的數(shù)據(jù)庫名和表名是與文件系統(tǒng)的目錄名和文件名對應(yīng)默認(rèn)情況下，創(chuàng)建的數(shù)據(jù)庫和表都存放在參數(shù)datadir定義的目錄如果不使用raid或邏輯卷，所有的表都存放在一個磁盤設(shè)備上，布磁盤IO的目的。5.常用方式有(1)將一個數(shù)據(jù)庫指向其他物理磁盤(2)將MyISAM表的數(shù)據(jù)文件或索引文件指向其他物理磁盤(3)windows下使用符號連6.所謂將一個數(shù)據(jù)庫指向其他物理磁盤，方法就是先在目標(biāo)磁盤創(chuàng)建目錄，然后在創(chuàng)建從MySQL數(shù)據(jù)目錄到目標(biāo)目錄的符號連接。53/shell>mkdirshell>ln-s將MyISAM表的數(shù)shell>mkdirshell>ln-s將MyISAM表的數(shù)據(jù)文件或索引文件指向其他物理磁盤，不過得注意的是其他存儲引擎不支持。操作如下對于新建的表，可以通過createtable句中增datadirectory和indexdirectory選項來完成，比如：>createtabletest(idintprimary ->type=->datadirectory=->indexdirectory=‘/disk3/index’;(2