08mysql高級(jí)課堂筆記數(shù)據(jù)庫(kù)優(yōu)化

1 識(shí)導(dǎo) 3 識(shí) mysql入mysqlmysqlmysqlmysqlmysqlmysql重排auto_increment4 識(shí)入 入mysql>createtablet2likemysql>insertintot2select*from5 1.ALTERTABLE用來(lái)創(chuàng)建普通索引、UNIQUE索引或PRIMARYKEY索ALTERTABLEtable_nameADDINDEXindex_name(column_list)ALTERTABLEtable_nameADDUNIQUE(column_list)ALTERTABLEtable_nameADDPRIMARYKEYcreateCREATEINDEXindex_nameONtable_nameCREATEUNIQUEINDEXindex_nameONtable_namedropDROPINDEXindex_nameONaltertabletableALTERTABLEtable_nameDROPINDEXindex_nameALTERTABLEtable_nameDROPPRIMARYKEY6 mysql>createviewv_t1asselect*fromt1whereid>4andid<11;QueryOK,0rowsaffected(0.00sec)mysql>?viewALTERVIEWCREATEVIEWDROPVIEWmysql>showtables;mysql>dropview7 識(shí) 字符串函數(shù) CONCAT(string2[,…LCASE(string2)UCASE(string2)LENGTH(stringLTRIM(string2)RTRIM(string2REPEAT(string2,count

//重復(fù)countRECE(str,search_str,rece_str)//在str中用rece_str替換search_strSUBSTRING(str,position[,length])//從str的position開始,取length個(gè)字符 8 識(shí)入 入BIN(decimal_number)CEILING(number2)FLOOR(number2)MAX(num1,num2)

9 識(shí) 入

識(shí) 入mysql>preparestmt1from'select*fromt1wheremysqlset@i=1;mysqlexecutestmt1using@i;mysqlset@i=5;mysql>executestmt1using如何刪除預(yù)處理mysql>dropprepare 識(shí) mysql>setmysql>savepointp1;mysql>savepointp2;mysql>rollbacktop1;退回到最原始的還原點(diǎn):mysql>rollback 識(shí) 創(chuàng)建一 mysql>\dmysql>createprocedure->->set->while@i<10->select->set->end->mysql>\d;mysql>callp1();查看procedurep1()的status信息mysql>showprocedurestatus\G查看procedurep1()的具體信息:mysql>showcreateprocedure 識(shí) 修改delimiter為 mysql>\dmysql>createtriggertg1beforeinsertont1foreach>insertintot2(id)mysql>select*fromt1;mysql>select*fromt2;mysql>insertintot1 識(shí) 如何制作刪除表t1后t2表中的記錄也會(huì)跟著刪除呢 mysql>\dmysql>createtriggertg2beforedeleteont1foreach>begindeletefromt2wheremysql>\d;mysql>\d//mysql>createtriggertg3beforeupdateont1foreach>beginupdatet2setid=new.idwheremysql>\d;mysql>show 識(shí) MYSQL數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)的ID如何恢 deletefromtablename;truncatetable這樣auto_increment就恢復(fù)成1altertabletablenameauto_increment=1; 識(shí)入 入利用GROUPBY的WITHROLLUP用BITGROUPFUNCTIONS 識(shí)入 入^匹配1個(gè)或零個(gè)$.匹配0到n個(gè) 識(shí)入 入mysql>selectname, like“”

fromt

like“mysql>selectmysql>selectfromt 巧用RAND()提取隨機(jī)識(shí)入 入個(gè)函數(shù)一起和orderby能夠把數(shù)據(jù)隨機(jī)排序。mysql>select*fromstuorderbyrand()；mysql>select*fromstuorderbyrandlimit3; 利用GROUPBY的WITH識(shí) 使用groupby的withrollup子句可以檢索 的分組聚合信息 mysql>select*from |cname|pname | | | | | | | | | | | | | | | | | | 9rowsinset(0.00 利用GROUPBY的WITH識(shí) mysql>selectcname,pname,count(pname)fromdemogroupby |cname|pname|count(pname) ||||

| 3| 2| 3| 1 4rowsinset(0.00 利用GROUPBY的WITH識(shí) 同樣使用withrollup關(guān)鍵字后，統(tǒng)計(jì) 的信息，如下。注意： rollup不可以和orderyby同時(shí)使mysql>selectcname,pname,count(pname)fromdemogroupbycname,pnamewithrollup; |cname|pname|count(pname) | | 3|| | 2|||||

|NULL 5|| 3|| 1||NULL 4||NULL|NULL 9| 7rowsinset(0.00知2.4BITGROUPFUNCTIONS知 ?在使用groupby語(yǔ)句時(shí)可以同時(shí)用使用bit_and、mysql>selectmysql>selectid，bit_or(kind)fromorder_rabgroupbymysql>selectmysql>selectid，bit_and(kind)fromorder_rabgroupby知2.5知 mysql>createmysql>createtabletemp(idint,namechar(20),foreignkey(id)referencesoutTable(id)ondeletecascadeonupdate 2.6mysqlhelp使識(shí)導(dǎo)入在mysql中那么多令如何才能記得住是個(gè)問(wèn)題，這里?%可以獲得所有的mysql>里 ?opti因?yàn)橛洸蛔ptimize的全稱，這個(gè)時(shí)候可以用%reg%獲得了記不住了的regexp用法查看所有用?contents可以得到所有的幫助大綱,通過(guò)這個(gè) 識(shí)導(dǎo) 知知 3.1.1通過(guò)showstatus命令了解各種SQL的執(zhí)行頻格式：mysqlshowmysql>show mysql>showglobal mysql>showstatuslike mysql>showglobalstatuslike 參數(shù)說(shuō)明Com_XXX表示每個(gè)XXX執(zhí)行執(zhí)行只針對(duì)于 引擎的 inselect*fromtablewhere2)descselect*fromtablewheremysql> inselectcount(*)fromstumysql> inselectcount(*)fromstuwherenamelikeid:1table:stutype:key:namekey_len:50ref:NULLrows:8Extra:Usingwhere;Usingindex1rowinset(0.00sec) 每一列的簡(jiǎn)單解 id: select_type:SIMPLE表示select的類型，常見(jiàn)的取值有SIMPLE（）簡(jiǎn)單表，即不使用表連接或 table type:range表示表的連接類型，性能有好到差：system（表僅一行）、const（只一行匹配）、ref_or_null（同前面對(duì)null查詢）、index_merge（索引合并優(yōu)化）、unique_subquery（:key key_len:50索引字段的長(zhǎng)度ref:rows Extra:Usingwhere;Usingindex知知 3.2.1索引

indexpany2_namepany2_name3.2.2MySQL 于快速找出在某個(gè)列中有一特定值的行。對(duì)相關(guān)列使用索引是提高導(dǎo) 操作性能的最佳途徑導(dǎo)入1mysql>createmysql>createindex_mon然后 _id進(jìn)行查詢，發(fā)現(xiàn)使用到了復(fù)合索 inselect*fromsales2 inselect*fromsales2where識(shí) 識(shí)2where2wherenamelikeinselect*mysql>******************************************************1.rowid:1 type:ALLkey:NULLkey_len:NULLref:NULLrows:1000Extra:Using1rowinset(0.00 mysql> inselect* 2wherenamelike ***************************1.rowid:select_type: 2type:rangepossible_keys:pany2_namekey:pany2_namekey_len:11ref:rows:Extra:Usingwhere1rowinset(0.00sec)mysql>mysql>inselect*2wherenameis***************************1.rowid:select_type:SIMPLE 2type:refpossible_keys:pany2_namekey:pany2_namekey_len:11ref:(4)如果列名是索引，使用column_nameisnull將使用索引。如下知rows:Extra:Usingwhere1rowinset(0.00sec)知導(dǎo) 導(dǎo) mysql>selectmysql>select*fromtable_namewherekey_part1>1andmysql>showindexmysql>showindexfrom***************************1.……key_name:ind_sales_yearColumn_name:year……知 mysql> inselect*fromsaleswhereyear=2001or***************************1.rowid:1table:key:NULL1row1rowin2sing.00::t11rowUsing(0.00: 識(shí)inselectinselect*fromsales2wheremoneys=1mysql>******************************************************1.rowid:1type:ALLkey:NULLkey_len: 識(shí)導(dǎo) mysql> inselect* 2wherename***************************1.rowid:1 type:ALLkey:key_len:1rowinUsingt(0.00emysql>mysql>inselect*2where***************************1.rowid:select_type: 2type:ALLpossible_keys:pany2_namekey:NULLkey_len:NULLref:NULLrows:Extra:Using 識(shí)1rowinset(0.00mysql>mysql>inselect*2where***************************1.row***************************id:select_type:SIMPLEpossible_keys:pany2_namekey:pany2_namekey_len:ref:constrows:1Extra:Usingwhere2type:1rowinset(0.003.2.3 入 入mysql>mysql>showstatuslike | | | ||||Handler_read_rnd_next|2055 6rowsinset(0.00|||||||||知知導(dǎo) 導(dǎo)mysql>mysql>CHECKTABLEtbl_name[,tbl_name]…[option]…option{QUICK|FAST|MEDIUM|EXTENDED|mysql>checktable | | |Msg_type|Msg_text |sakila.sales|check| | 1rowinset(0.01 導(dǎo) 導(dǎo)mysql>mysql>optimizetable | ||Msg_type|Msg_text|sakila.sales|optimize| | 1rowinset(0.05|知知 3.4.1 loadingtheALTERTABLEtbl_nameENABLEDISABLEKEYS和ENABLEKEYS用來(lái)打開或關(guān)閉MyISAM表非 導(dǎo) mysql>loaddatainfile‘/home/mysql/film_test.txt’intotablefilm_test2導(dǎo) terminatedbyQueryOK,529056rowsaffected(1min55.12sec)mysql>altertablefilm_test2disablekeys;QueryOK,0rowsaffected(0.0sec)mysql>loaddatainfile‘/home/mysql/film_test.txt’intotableQueryOK,529056rowsaffected(6.34sec)mysql>altertablefilm_test2enablekeys;QueryOK,0rowsaffected(12.25 mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQuery rowsaffected(22.92 Deleted:0Skipped:0mysql>loaddatainfile‘/home/mysql/film_test4.txt’intotableQueryOK, rowsaffected(31.16sec) Deleted:0Skipped:0Warnings:0入 在導(dǎo)入數(shù)據(jù)前先執(zhí)行setunique_checks=0，關(guān)閉唯 一性效驗(yàn)，在導(dǎo)入結(jié)束后執(zhí)行setunique_checks=1， 入mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQueryOK, rowsaffected(22.92sec) Deleted:0Skipped:0Warnings:0mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQueryOK, rowsaffected(19.92sec) 入知?在導(dǎo)入數(shù)據(jù)前先執(zhí)行setmit=0，關(guān)閉自動(dòng)提交事 務(wù)，在導(dǎo)入結(jié)束后執(zhí)行setmit=1，恢復(fù)自動(dòng)提 入當(dāng)mit=1當(dāng)mit=1mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotablefilm_test4;Queryrowsaffected(22.92sec)Deleted:0Skipped:0Warnings:0當(dāng)mit=0mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotablefilm_test4;Queryrowsaffected(20.87Deleted:0Skipped:0 可以使用insertdelayed（馬上執(zhí)行）語(yǔ)句得到更高可以增加bulk_insert_buffer_size變量值的方法來(lái)當(dāng)從一個(gè)文件中裝載一個(gè)表時(shí)，使用LOADDATAmysql>mysql>inselectid,sum(moneys)fromsales2groupbyid:1table:sales2type:ALLkey:NULLref:NULLrows:1000Extra:Usingtemporary;Using1rowinset(0.003.4.3groupby導(dǎo) ?如果查詢包含groupby但用戶想要避免排序結(jié)果 的損耗，則可以使用使用orderbynull來(lái)排 導(dǎo)如下沒(méi)有使用orderbynull來(lái)排序inselectinselectid,sum(moneys)fromsales2groupbyidordermysql>******************************************************1.rowid:1type:ALLkey:NULLref:NULLrows:1000Extra:Usingtemporary1rowinset(0.00sec)3.4.5 導(dǎo)入mysql> inselect*fromsales2 _idnotin(select ***************************1.row***************************id:1table:sales2type:ALLkey:NULLref:NULLrows:Extra:Using1rowinset(0.00 id:2 type:index_subquerypossible_keys:pany2_idkey:pany2_idkey_len:5ref:funcrows:2Extra:Using1rowinset(0.00sec第2/2下面是使用更有效 查詢 id:2select_type:入 入type:index_subquerypossible_keys:pany2_idkey:pany2_idkey_len:5ref:funcrows:1Extra:1rowinset(0.00sec第2/2知知 知4.1知 ?在MySQL中，可以使用函數(shù)PROCEDUREYSE（）對(duì)當(dāng)前應(yīng)用的表進(jìn)行 mysql>select*fromduck_cust Field_n