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

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