深入淺出mysql數(shù)據(jù)庫開發(fā),優(yōu)化與管理維護

第一 開發(fā) 第1章幫助的使 第2章表類型（引擎）的選 Mysql引擎概 各種引擎的特 第3章選擇合適的數(shù)據(jù)類 char與 text和 第4章字符 字符集概 Mysql支持的字符集簡 Unicode簡 Mysql字符集的設 第5章索引的設計和使 Mysql索引概 Mysql如何使用索 第6章鎖機制和事務控 如何加 死 事務控 第7章SQL中的安全問 SQL注入簡 第8章SQLMode及相關(guān)問 MysqlSQLMode簡 SQLMode與可移植 SQLMode與數(shù)據(jù)效 第9章常用SQL技 利用groupby的withrollup子句做統(tǒng) 用bitgroupfunctions做統(tǒng) 第10章其他需注意的問 第二篇優(yōu)化 第11章SQL優(yōu) 優(yōu)化SQL的一般步 定位執(zhí)行效率較低的SQL語句 索引問 索引的分 常用SQL的優(yōu) 優(yōu)化insert語句 優(yōu)化groupby語句 優(yōu)化orderby語句 mysql如何優(yōu)化or條件 使用SQL提示 第12章優(yōu)化數(shù)據(jù)庫對 逆規(guī)范 第13章鎖問 insert…select…帶來的問 第14章優(yōu)化Mysql 查看Mysqlserver當前參 mit的設置 第15章I/O問 使用SymbolicLinks分布 第16章應用優(yōu) 使用連接 減少對Mysql的 使用mysqlquery 加cache層 負載均 利用mysql分流查詢操作 第三篇管理 第17章mysql安裝升 安 rpm安裝步 mysql升 mysql降 第18章Mysql日志管 錯誤日志 查詢?nèi)? 第19章數(shù)據(jù)備份與恢復 冷備份 邏輯備份 位置恢復 MyISAM表修復 第20章Mysql安全 正確設 權(quán)限 盡量避免以root權(quán)限運行 刪除帳號 給mysqlroot帳號設置口令 設置安全并定期修改 FILE、PROCESS或SUPER權(quán)限授予管理員以外的帳號： loaddatalocal帶來的安全問題 使用merge引擎潛藏的安全 防止DNS droptable命令并不收回以前的相關(guān) 使用 增加 old- secure- skip- 第21章Mysql Mysql概述 安裝配置 日常管理 經(jīng)常查看slave狀 Slave上出現(xiàn)logevententryexceededmax_allowed_packet錯誤怎么 多主時，自動增長變量的問 第22章Mysql MysqlCluster概述 MysqlCluster架構(gòu) 安裝配置 管理 Cluster的啟 Cluster的關(guān) 第23章Oracle向Mysql數(shù)據(jù)遷移 導出為insertsql文 第24章應急處理 忘記root MyISAM表超過4G無法 數(shù) 如何DNS反向解析 第25章Mysql管理中一些常用令和技巧 修改用戶 第一篇1章的語法是什么樣呢？最好的辦法是學會使用mysql的幫助。方法很簡單：按照層次看幫mysql>?Youaskedforhelpabouthelpcategory:Formoreinformation,type'help<item>',where<item>isoneofthefollowingDataDefinitionDataTypesFunctionsandModifiersforUsewithGROUPBYGeographicFeaturesStorageEnginesStoredRoutinesmysql>?dataYouaskedforhelpabouthelpcategory:"DataFormoreinformation,type'help<item>',where<item>isoneofthefollowingName:'INT'Anormal-sizeinteger.Thesignedrangeis- Theunsignedrangeis0to 知道show命令都能看些什么東西，可以用如下命令：mysql>?Name:SHOWhasmanyformsthatprovideinformationaboutdatabases,tables,columns,orstatusinformationabouttheserver.Thissectiondescribesthosefollowing:SHOWCHARACTERSET[LIKE'pattern']SHOWCOLLATION[LIKE'pattern']SHOW[FULL]COLUMNSFROMtbl_name[FROMdb_name][LIKE'pattern']SHOWCONTRIBUTORSSHOWCREATEEVENTevent_nameSHOWCREATEFUNCTION mysql>?create[table_option...][table_option...]第2章表類型（引擎）的選Mysql引擎概mysqlMyISAM、InnoDBBDB、MEMORYMERGEEXAMPLENDBClusterARCHIVE、CV、BLACHOLE、FDERATE等，其InnDB和BDB提事務安表，其引各種引擎的特有低低高低低高低高高高低Myisam是Mysql的默認引擎，當create創(chuàng)建新表時，未指定新表的引擎時，默（表定義、.MYD(MYData，數(shù)據(jù))、.MYI(MYIndex，索引)。數(shù)據(jù)文件和索 如何選擇合適的引并作為1個對象它們。對于諸如數(shù)據(jù)倉儲等VLDB環(huán)境十分適合。3章選擇數(shù)據(jù)類型的基本原 InnoDB引擎和數(shù)據(jù)列建議使用VARCHAR類型對于InnoDB數(shù)據(jù)表，內(nèi)部的行格式?jīng)]有區(qū)分固定長度和可變長度列（所有數(shù)據(jù)行用可變長度VARCHAR列簡單。因而，主要的性能因素是數(shù)據(jù)行使用的總量。由于CHAR平均占用的空間多于VARCHAR，因此使用VARCHAR來最小化需要處理的數(shù)據(jù)行的總固定長度數(shù)據(jù)列與可變長度的數(shù)char 值需需 'ab'abmysql>CREATETABLEvc(vVARCHAR(4),cQueryOK,0rowsaffected(0.02mysql>INSERTINTOvcVALUES('ab','ab');QueryOK,1rowaffected(0.00sec)mysql>SELECTCONCAT(v,'+'),CONCAT(c,'+')FROM |CONCAT(v,'+')|CONCAT(c,'+') |ab | 1rowinset(0.00textBLOB和TEXT值也會引起自己的一些問題，特別是執(zhí)行了大量的刪除或更新操作的時候。刪除這種值會在數(shù)據(jù)表中留下很大的"空洞"，以后填入這些"空洞"的記錄可能長度不OPTIMETABE使用合成的（snteic）索引。的建個，這在據(jù)。來以（散列值對于類似<或>=等范圍搜索操作符是沒有用處的。我們可以使用MD5()函數(shù)生成散列值，也可以使用H1CC2值高同果算字有格，就在不必要的時候避免檢索大型的BLOB或TEXT值。例如，SELECT*查詢就不是很好的想能毫無目的地在網(wǎng)絡上傳輸大量的值。這也是BLOB或TEXT標識符信息在合成的索格的數(shù)據(jù)行中檢索BLOB或TEXT值。主數(shù)據(jù)表上運行SELECT*查詢的時候不會通過網(wǎng)絡傳輸大量的BLOB或TEXT值。浮點數(shù)與定點mysql>CREATETABLEtest(c1float(10,2),c2decimal(10,2));QueryOK,0rowsaffected(0.29sec)mysql>insertintotestvalues(131072.32,131072.32);QueryOK,1rowaffected(0.07sec)mysql>select*from | | |131072.31|131072.32 1rowinset(0.00在mysql中float、double（或real）是浮點數(shù)，decimal（numberic）是定點數(shù)。浮4章字符集是一套符號和編碼的規(guī)則，不論是在oracle數(shù)據(jù)庫還是在mysql數(shù)據(jù)庫，都Mysqlmysql服務器可以支持多種字符集（可以用showcharacterset命令查看所有mysql支持對規(guī)則是一對多的關(guān)系,MySQL支持30多種字符集的70多種校對規(guī)則。UnicodeASCII，ASCIIASCII256夠用的。于是各個國家或組織都相繼制定了符合自己語言文字的標準，比如gb2312、big5文字的編碼方案。Unicode"UniversalMultiple-OctetCodedCharacterSet"，簡稱為UCS。UCS可以看作是"UnicodeCharacterSet"的縮寫。Unicode有兩套標準UCS-2和UCS-4，前者用2個字節(jié)表示一個字符，后者用4個字節(jié)表示一個字符。以目前常用的UCS-2為例，它可以表示的字符數(shù)為2^16=65535，基本上可以utf8、gb2312、gbk、latin1gb2312和gbk。因為gb2312字庫比gbk字庫小，有些偏僻字（例如：洺）不能保存，因此定答復的話最好選用gbk。Mysql f中設置：./configure--with-如果沒有特別的指定服務器字符集，默認使用latin1作為服務器字符集。上面三種設置可以用showvariableslike'character_set_server';命令查詢當前服務器的字符5Mysql 在MySQL5.1中，對于MyISAM和InnoDB表，前綴可以達到1000字節(jié)長。請注意前綴的限制應以字節(jié)為單位進量，而CREATETABLE語句中的前綴長度解釋為字符數(shù)。當引使用R-樹。默認情況MEMORY(HEAP)引擎使用hash索引，但也支持B-樹索引。設計索引的原句中的列，或連接子句中指定的列，而不是出現(xiàn)在SELECT關(guān)鍵字后的選擇列表中的列。重復值的列，其索引效果。例如，存放的列具有不同值，很容易區(qū)分各行。而用來記錄的列，只含有“M”和“F”，則對此列進行索引沒有多大用處（不管搜使用短索引。如果對串列進行索引，應該指定一個前綴長度，只要有可能就應該這樣做。例如，如果有一個CHAR(200)列，如果10個或20個字符內(nèi)，多數(shù)值是惟一的，那1020I/O更為以在內(nèi)存中容納的值。這增加了找到行而不用索引中較多塊的可能性。為這個索引中不會有許多不同的值。）利用最左前綴。在創(chuàng)建一個n列的索引時，實際是創(chuàng)建了MySQL可利用的n個索引。越長。如果有一個索引很少利用或從不使用，那么會不必要地減緩表的修改速度。此外，和BETWEEN運算。在模式具有一個直接量前綴時，索引也用于LIKE運算。如果只將btree索引與hash關(guān)鍵元素與常量值的比較關(guān)系對應一個范圍條件。Hash索引還有一些其它特征：它們只用(該類索引不能用來按順序搜索下一個條目）。MySQL不能確定在兩個值之間大約有多少行(這被范圍優(yōu)化器用來確定使用哪個索引)。如果你將一個MyISAM表改為hash-索引的MEMORY表，對于BTREE索引，當使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE'pattern'(其中'pattern'不以通配符開始)操作符時，關(guān)鍵元素與常量值的比較關(guān)系對應一個范圍條件?！俺Ａ恐怠毕抵福翰樵冏址械某Ａ?、同一聯(lián)接中的const或system表中的列、無關(guān)聯(lián)下面是一些WHERE子句中有范圍條件的查詢的例子：下列范圍查詢適用于btree索引和hash索引SELECT*FROM WHEREkey_col= ORkey_colINbtreeSELECT*FROM WHEREkey_col> ANDkey_col<SELECT*FROMt1 WHEREkey_colLIKE'ab%' ORkey_colBETWEEN'bar'ANDMysql索于快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第1條記果一個表有1000行，這比順序至少快100倍。注意如果你需要大部分行，順序讀空間列類型的索引使用R-樹，并且MEMORY表還支持hash6如何加LOCKtbl_name[ASalias]{READ[LOCAL]|[LOW_PRIORITY][,tbl_name[ASalias]{READ[LOCAL]|[LOW_PRIORITY]WRITE}]UNLOCK死LOCKTABLES設定表鎖定的地方或者涉及InnoDB之外的引擎設置鎖定的地方檢測死鎖。事務控MySQL通過 MIT,STARTTRANSACTION,COMMIT和ROLLBACK等語句支持本STARTTRANSACTION|BEGINCOMMIT[WORK][AND[NO]CHAIN][[NO]RELEASE]ROLLBACK[WORK][AND[NO]CHAIN][[NO]RELEASE] MIT={0|1}默認情況下，是mit的，如果需要通過明確的commit和rollback來提交和STARTTRANSACTION或BEGIN語句可以開始一項新的事務。SETMIT可以修改當前連接的提交方式，如果設置了SETMIT=0，則設置之后的所有事務都需要通過明確令進行提交或者回滾。mysql>mysql>select*frommysql>select*fromEmptyset(0.00Emptyset(0.00mysql>startQueryOK,0rows QueryOK,1rowmysqlselect*fromEmptyset(0.00mysql>QueryOK,0rows-mysqlselect*from | |name | |100 1rowinset(0.00 QueryOK,1rowmysql>select*from | |name | |1.00| |2.00 2rowsinset(0.00mysql>startQueryOK,0rows QueryOK,1rowaffectedmysql>commitandQueryOK,0rows QueryOK,1row(0mysql>select*from | |name | |1.00| |2.00| |300 3rowsinset(0.00mysql>QueryOK,0rowsmysql>select*from | |name | |1.00| |2.00| |3.00| |400 4rowsinset(0.00mysql>select*fromEmptyset(1665mysql>select*fromEmptyset(1665mysqllocktablett3write;QueryOK,0rowsaffected(0.00mysql>select*from QueryOK,1rowaffectedmysql>QueryOK,0rowsaffected(000-mysql>startQueryOK,0rowsmysqlselect*from | |name | |100 1rowinset(37.71不同的savepoint。需要注意的是，如果定義了相同名字savepoint則后面定義的savepoint會覆蓋之前的定義。對于不再需要使用的savepoint，可以通過releasesavepoint命令刪除savepoint，刪除后的savepoint，不能再執(zhí)行rollbacktosavepoint命令。mysql>select*from+ | |name+ | |2.00| |3.00| |400+ 3rowsinset(0.00mysql>select*from+ | |name+ | |2.00| |3.00| |400+ 3rowsinset(0.00mysql>startQueryOK,0rowsaffected(0.00mysql>deletefromtt3whereid=QueryOK,1rowaffected(0mysql>select*from+ | |name | |3.00| |4.00+ 3rowsinset(000mysql>select*from+ | |name | |2.00| |3.00| |4.00 3rowsinset(0.00mysql>savepointQueryOK,0rowsaffectedmysql>deletefromtt3whereidQueryOK,1rowaffectedmysql>select*frommysql>select*from+ | |name+ | |name | |4.00| |2.00+ 3rowsinset(0.00| |3.00| |4.00 3rowsinset(0.00mysql>rollbacktosavepointQueryOK,0rowsaffectedmysql>select*frommysql>select*from | |name+ | |3.00| |name+ | |2.00| |400| |300+ 2rowsinset(0.00| |4.00+ 3rowsinset(0.00OK,0mysqlselect*frommysqlselect*from+ + | |name| |name+ + | |300| |300| |4.00| |4.00+ + 2rowsinset(0.002rowsinset(0.007章SQLSQL丁或者進行簡單的安全配置進行自我保護，一般的也無法SQLInjectionSQLInjection結(jié)構(gòu)化查詢語言(SQL)是一種用來和數(shù)據(jù)庫交互的文本語言。SQLInjection就是利$sql="SELECT*FROMuserWHEREusername='$username'AND$result=mysql_db_query($dbname,'or們提交的時候是把后面的代碼注釋掉，值得注意的是由于編碼問題，在IEprepareStatement+Bind-Java,Jsp開發(fā)的應用,可以使用prepareStatement+Bind-variable來防止sql注入,另外從PHP5開始，也在擴展的mysqli中支持preparedstatements，所以在使用這類語言作數(shù)據(jù)庫開發(fā)時,強烈建議使用prepareStatement+Bind-variable來實現(xiàn),而盡量不要使用拼接的sql。MySQLCAPImysql_real_escape_stringAPIMySQL++escapequotePHPmysql_real_escape_string()函數(shù)（PHP4.3.0，之前的版支持preparedstatements。PerlDBI ：使用placeholders或者quote()方法。RubyDBI ：使用placeholders或者quote()方法。JavaJDBC ：使用PreparedStatement和placeholders。8章SQLModeMysqlSQLMode不同的環(huán)境下、與其他數(shù)據(jù)庫服務器一起更容易地使用MySQL。可以使用“--sql-SET[SESSION|GLOBAL]sql_mode='modes'語句，通過設置sql_modelinux下安裝完mysqlsql-mode值是空，在這種情形下mysql執(zhí)行的是一種不嚴格的檢查，例如日期字段可以插入’0000-00-0000:00:00’這樣的值，還有如果要插入mysql>createtablet5(c1char(3));mysql>insertintot5values('abcd');mysql>select*fromt5;+| | 1rowinset(0.00我們可以設置sql_mode為STRICT_TRANS_TABLES，如下：mysql>setsessionmysql>insertintot5ERROR1406(22001):Datatoolongforcolumn'c1'atrowSql_mode如果不能將給定的值插入到事務表中，則放棄該語句。對于非事務表，1MakeMySQL的行為象“傳統(tǒng)”SQL數(shù)據(jù)庫系統(tǒng)。該模式的簡單描述是方式不是你想要的，因為出現(xiàn)錯誤前進行的數(shù)據(jù)更改不會“滾動”，結(jié)果是更新“只進行了部分”。中插入或更新不正確的值時，mysqlinsert/update操作。在我們的是，如果數(shù)據(jù)庫運行在嚴格模式下，并且你的引擎不支持事務，那么有數(shù)據(jù)不一致的風sql中有兩個dml語句，如果后面的一個出現(xiàn)了問題，但是前面的已經(jīng)操mysqlsql_mode需要應用人員權(quán)衡各種Sql_modePIPES_AS_CONCATANSI_QUOTESSql_modePIPES_AS_CONCATANSI_QUOTESIGNORE_SPACENO_KEY_OPTIONSPIPES_AS_CONCATANSI_QUOTESIGNORE_SPACENO_KEY_OPTIONSNOTABLEOPTIONSNOFIELD NOAUTOCREATEPIPES_AS_CONCATANSI_QUOTESIGNORE_SPACENO_KEY_OPTIONSNO_TABLE_OPTIONSPIPES_AS_CONCATANSI_QUOTESIGNORE_SPACENO_KEY_OPTIONSPIPES_AS_CONCATANSI_QUOTESIGNORE_SPACENO_KEY_OPTIONSNOTABLEOPTIONSNOFIELDSQLMode·將||視為字符串連接操作符（＋）CONCAT())，而不視為OR9SQL檢索包含最大/最小值MIN([DISTINCT]expr),MAX([DISTINCT]exprMIN()MAX()些情況下，它們返回最小或最大字符串值。DISTINCT可以被用來查找expr的不同值的最小或最大值，然而，這產(chǎn)生的結(jié)果與省略DISTINCT的結(jié)果相同。若找不到匹配的行，MIN()MAXNULLmysql>SELECTstudent_name,MIN(test_score),- FROM- GROUPBY巧用rand()/rand(nmysql>SELECT*FROMtbl_nameORDERBYmysql>SELECT*FROMtable1,table2WHEREa=bANDc<dORDERBYRAND()LIMIT1000;利用groupbywithrollup intnotnull,countryvarchar(20)notnull,productvarchar(32)notprofitselectyear,sum(profit)fromsalesgroupbyselectyear,sum(profit)fromsalesgroupbyyearwithrollup;selectyear,country,product,sum(profit)fromgroupbyyear,country,selectyear,country,product,sum(profit)fromsalesgroupbyyear,country,productwithinsertintosalesvalues(2004,'china',' insertintosalesvalues(2004,'china',' insertintosalesvalues(2004,'china',' insertintosalesvalues(2005,'china',' insertintosalesvalues(2005,'china',' insertintosalesvalues(2005,'china',' insertintosalesvalues(2005,'china',' insertintosalesvalues(2005,'china',' insertintosalesvalues(2005,'china',' insertintosalesvalues(2006,'china',' insertintosalesvalues(2006,'china',' insertintosalesvalues(2006,'china',' selectyear,country,product,sum(profit)fromsalesgroupbyyear,country,selectyear,country,product,sum(profit)fromsalesgroupbyyear,country,productwithFROMsalesGROUPBYyear,country,productWITHROLLUPLIMIT5;和ORDERBYLIMITROLLUP用bitgroupfunctions統(tǒng)Groupby- FROM- GROUPBY在本小節(jié),groupbybit_and,bit_or函數(shù)共同使用完成統(tǒng)計工作,先來了解一下bit_and和bit_or函數(shù),舉例如下:->`id`smallint(5)unsignedNOTNULLdefault->KEY`id`->)QueryOK,0rowsaffected(0.01QueryOK,8rowsaffected(0.00sec)Records:4Duplicates:0Warnings:0mysql>SELECTBIT_OR(id)from |BIT_OR(id) 7 1rowinset(0.00#####OR##mysql>SELECTBIT_AND(id)from |BIT_AND(id) 0 1rowinset(0.00 #AND..1111 熟悉了bit_and和bit_or后我們一起來學下bit_and,bit_or和groupby函針對上面的表tacust_typealtertableta addcolumn cust_typevarchar(100);updatetasetcust_type='2'whereid>3;updatetasetcust_type wherecust_typeismysql>SELECTcust_type,BIT_OR(id)from groupby |cust_type|BIT_OR(id) | 3| 4 2rowsinset(0.00mysql>SELECTcust_type,BIT_and(id)from groupby |cust_type|BIT_and(id) | 0| 4 2rowsinset(0.00 所有比特被設置為1）。若找不到匹配的行，則函數(shù)返回0。若找不到匹配的行，則函數(shù)返回0。10MySQL中，數(shù)據(jù)庫對應數(shù)據(jù)中的。數(shù)據(jù)庫中的每個表至少對應數(shù)據(jù)庫中認文件系統(tǒng)類型(HFS+)，對大小寫不敏感。然而，MacOSX也支持UFS敏感，就像Unix一樣。小寫來給定的數(shù)據(jù)庫或表。下面的查詢不會工作，因為它同時了表my_tables和asmysql>SELECT*FROMmy_tableWHERE列、索引、子程序和觸發(fā)器名在任何平臺上對大小寫不敏感，列的別名也不敏感。默認情況，表別名在Unix中對大小寫敏感，但在Windows或MacOSX中對大小寫不敏感。下查在Unix名a和A：mysql>SELECTcol_nameFROMtbl_nameAS->WHEREa.col_name=1ORA.col_name=如總是用小寫創(chuàng)建并數(shù)據(jù)庫名和表名。在大多數(shù)移植和使用中建議使用該轉(zhuǎn)換。在MySQLlower_case_tables_name統(tǒng)變量確定，可以在啟動mysqld時設置。lower_case_tables_name可以采用下面的任一值：值含0使用CREATETABLECREATEDATABASE庫名。名稱比較對大小寫敏感。在Unix不敏感的文件系統(tǒng)上用lowercasetablenames00，并且使用不同的大小12表名和數(shù)據(jù)庫名在硬盤上使用CREATETABLECREATEDATABASEMySQL注釋：只在對大小寫不敏感的文件系統(tǒng)上適用!InnoDB表名以小寫保存，例如lowercasetablesname1MySQL，通常不需要更改lower_case_tables_name變量。lower_case_tables_name=1。使用該選項的不利之處是當使用SHOWTABLES或SHOWDATABASES時，看不出名字原來是用大寫還是小寫。Unix中使用lower_case_tables_name=0，在Windows中使用lower_case_tables_name=2。例外：如果你正使用InnoDB表，在任何平臺上均應將lower_case_tables_name設置為1，請注意在Unix中將lower_case_tables_name設置為1之前，重啟mysqld之前，必InnoDBREFERENCEStbl_name(col_name)子句定義列時可MySQL不執(zhí)行表tbl_name中的動作，例如作為你正定義的表中的行的動作的響應而刪除行；換句話說，該句法不會致使ONDELETEONUPDATE行為（如果你在REFERENCES子句中寫入ONDELETEONUPDATE子句，將被忽略）。如果用該句法定義InnoDB表，將會導致錯誤。 idSMALLINTUNSIGNEDNOTNULLAUTO_INCREMENT,nameCHAR(60)NOTNULL,styleENUM('t-shirt','polo','dress')NOTcolorENUM('red','blue','orange','white','black')NOTownerSMALLINTUNSIGNEDNOTNULLREFERENCES PRIMARYKEY(id) SELECT@last:=(NULL,'polo','blue',(NULL,'dress','white',(NULL,'t-shirt','blue', SELECT@last:=(NULL,'dress','orange',(NULL,'polo','red',(NULL,'dress','blue',(NULL,'t-shirt','white',SELECT* |id| |1|Antonio |2|LillianaAngelovska SELECT*FROM |id| |color|owner |1| | 1|2| |white 1|3|t-shirt| 1|4| |orange 2|5| | 2|6| | 2|7|t-shirt|white 2 SELECTs.*FROM p,shirtsWHERELIKE'Lilliana%'ANDs.owner=p.idANDs.color<> |id|style|color|owner |4|dress|orange 2|5|polo| 2|6|dress| 2 SHOWCREATETABLE***************************1.row***************************Table:shirtCreateTable:CREATETABLE`shirt``id`smallint(5)unsignedNOTNULL`style`enum('t-shirt','polo','dress')NOT`color`enum('red','blue','orange','white','black')NOT`owner`smallint(5)unsignedNOTNULL,PRIMARYKEY(`id`))ENGINE=MyISAMDEFAULT第二篇優(yōu)化11章SQL優(yōu)化SQL的一般步showstatus和應用特點了解各種SQL的執(zhí)行statusSHOWSTATUSsessionglobal以下幾個參數(shù)對Myisam和Innodb引擎都計數(shù)執(zhí)行執(zhí)行Innodb_rows_readselect執(zhí)行查詢操作為主，以及各種類型的SQL大致的執(zhí)行比例是多少。對于更新操作的計數(shù)，是mitCom_rollback以了解事務提交和回滾 Slow_queriesSQL可以通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的sql語句，用--log-slow-queries[=file_name]選項啟動時，mysqld寫一個包含所有執(zhí)行時間超過慢查詢?nèi)罩静⒉荒芏ㄎ粏栴}，可以使用showprocesslistMySQLEXPLAINSQL通過以上步驟查詢到效率低的SQL后，我們可以通過explain或者desc獲取更快的SELECT。mysql>explainselectsum(moneys)fromsales panysbwhere pany_id=b.idanda.year=2006; - | |table|type|possible_keys| | | - | | |index| | | 1 || | | | | |NULL 12 | - 2rowsinset(0.02 select possible_keys：表示查詢時,可以使用的索引列. 表的year字段創(chuàng)建了索引，查詢需要掃描的行數(shù)明顯較少。mysql>explainselectsum(moneys)from panysb pany_idb.idanda.year= - | |table|type|possible_keys| | | - | | |index| | | 1 Using||SIMPLE |a |ref |year |year |4 3| - 2rowsinset(0.02索引問索引的分CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX[USINGONtbl_name col_name[(length)][ASC| createindexind_testonMySQL均勻分布在1和100之間，下列查詢中使用索引就不是很好：SELECT*FROMtable_namewherekey_part1>1andkey_part1<值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高，因為索引并不經(jīng)常使用。mysql>showstatuslike兩個簡單實用的優(yōu)化方Y(jié)ZEYZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]定。這對于MyISAM,BDB和InnoDB表有作用。對于MyISAM表，本語句myisamchkCHECKCHECKTABLEtbl_name[,tbl_name]...[option]...option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}CHECKSUMCHECKSUMTABLEtbl_name[,tbl_name]...[QUICK|EXTENDEDoptimizeOPTIMIZEOPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]以使用OPTIMIZETABLE來重新利用未使用的空間，并整理數(shù)據(jù)文件的碎片。OPTIMIZETABLEMyISAM,BDBInnoDB表起作用。常用SQL的優(yōu)ALTERTABLEtblnameDISABLEloadingtheALTERTABLEtblnameENABLE這兩個命令用來打開或者關(guān)閉Myisam個非空的Myisam數(shù)據(jù)到一個空的Myisam而對于Innodb類型的表，這種方式并不能提高導入數(shù)據(jù)的效率。對于Innodb類型在導入數(shù)據(jù)前執(zhí)行SETUNIQUE_CHECKS=0，關(guān)閉唯一性校驗，在導入結(jié)束后執(zhí)行SET如果應用使用自動提交的方式，建議在導入前執(zhí)行SETMIT=0，關(guān)閉自動提交，導入結(jié)束后再執(zhí)行SETMIT=1，打開自動提交，也可以提高導入的效率。insertInsertintotest如果你從不同客戶插入很多行，能通過使用INSERTDELAYED語句得到更高的速度。Delayedinsert沒有真正寫入磁盤；這比每條語句分別插入要快的多；LOW_PRIORITY度，但是，這只能對myisam表使用；INSERT語句快20倍；groupby默認情況下，MySQLGROUPBYcol1，col2，中指定ORDERBY INSERTINTOSELECTa,COUNT(*)FROMbarGROUPBYaORDERBYorderby序。where條件和orderby使用相同orderby的順序和索引順序相同，并且orderby的字段都是升序或者都是降序。SELECT*FROMt1ORDERBYkey_part1,key_part2,...SELECT*FROMt1WHEREkey_part1=1ORDERBYkey_part1DESC,key_part2SELECT*FROMt1ORDERBYkey_part1DESC,key_part2SELECT*FROMt1ORDERBYkey_part1DESC,key_part2--orderby的字段混合ASCSELECT*FROMt1WHEREkey2=constantORDERBY--用于查詢行的關(guān)鍵字與ORDERBY中所使用的不相SELECT*FROMt1ORDERBYkey1,--對不同的關(guān)鍵字使用ORDERjoinMysql4.1開始支持SQL的子查詢。這個技術(shù)可以使用SELECT語句來創(chuàng)建一個單列的SELECT*FROMcustomerinfoWHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)SELECT*FROMLEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerIDWHEREsalesinfo.CustomerIDISNULLmysqlor對于or子句，如果要利用索引，則or之間的每個條件列都必須用到索引；如果查詢優(yōu)先還是更新（insert、update、delete）優(yōu)先LOW_PRIORITY（低優(yōu)先級）請求，那么系統(tǒng)就不會認為它的優(yōu)就允許第二個者插到寫入者之前。只有在沒有其它的者的時候，才允許寫入者開始SELECT查詢的HIGH_PRIORITY（高優(yōu)先級）關(guān)鍵字也類似。它允許SELECT插入正先級的SELECT在正常的SELECT語句之前執(zhí)行，因為這些語句會被寫入操作阻塞。如果你希望所有支持LOW_PRIORITY選項的語句都默認地按照低優(yōu)先級來處理，那么請使用--low-priority-updatesINSERTHIGH_PRIORITY來把INSERT語句提高到正常的寫入優(yōu)先級，可以消除該選項對單個INSERT語句的影響。SQL當處理一個會讓客戶端耗費點時間才能處理的大結(jié)果集時，可以考慮使用USEUSEINDEXMySQ去參考的索引列表，就可以讓MySQL不再考慮其他可用的索引。Eg:SELECT*FROMmytableUSEINDEX(mod_time,name)IGNORE為Hint。Eg:SELECT*FROMmytaleIGNOREINDEX(priority)FORCEMySQL使用一個特定的索引，可在查詢中使用FORCEINDEX作為HintEg:SELECT*FROMmytableFORCEINDEX(mod_time)SELECT查詢，以避免與鎖定表有關(guān)的由于讀、寫MyISAM表，插入操作和查詢操作可以并行進行，因為沒有少MySQL需要做的語法分析從而提高插入速度。通過可以提高某些操作的性能?？梢栽诜掌髦蟹植伎蛻舻臋z索以均分負載。12章我們可以使用PROCEDUREYSE()對當前已有應用的表類型的判斷，該函數(shù)可SELECT*FROMtbl_namePROCEDUREYSE();SELECT*FROMtbl_namePROCEDUREYSE(16,256);通過拆分，提高表的效中，這樣可以有效的避免Myisam表的和更新導致的鎖問題。逆規(guī)范使用冗余統(tǒng)計使用createtemporarytablesession內(nèi)存里面，當session斷掉后，表自然消除。對于大表的統(tǒng)計分析，如果統(tǒng)計的數(shù)據(jù)量不大，利用insert。。。select將數(shù)據(jù)13章獲取鎖等待情可以通過檢查table_locks_waitedtable_locks_immediatemysql>showstatuslike | |Value | | |Table_locks_waited| 2rowsinset(0.00mysql>showstatuslike | |Value ||0|||||||| || 3|+++5rowsinset(0.00Monitors，然后通過Showinnodbstatus察看，設置的方式是：CREATETABLEinnodb_monitor(aINT)DROPTABLE設置監(jiān)視器后，在showinnodbstatusUPDATEtbl_nameSETcolumn=valueWHEREDELETEFROMtbl_nameWHERESELECTINSERTUPDATEDELETEGROUPBYinsertselect型的，不論insert的表是什么類型的表，都會對select的表的進行鎖定。orcleraleoaeneteemysqlselect究其主要原因，是因為mysql在實現(xiàn)的機制時和oracle是不同的，如果不進行select某些文檔中提到可以通過設置innodb_locks_unsafe_for_binlog來避免這個現(xiàn)象，可能帶來非常嚴重的隱患。如果使用這個binlog進行從數(shù)據(jù)庫的恢復，或者進行主因此，我們并不推薦通過設置這個參數(shù)來避免insert...select.導致的鎖，如果需要進行可能會掃描大量數(shù)據(jù)的inert...eectoe和loadatainile行鎖定的。next-key在行級鎖定中，InnoDBnext-keylockingInnoDBInnoDB對索引記錄設置的鎖定也映像索引記錄之前的“間隙”。如果一個用戶對一個索引上的記錄RR一索錄個的被來止的題”。行插入一個重復。因此，thenext-keySELECTFORUPDATE,SELECTLOCKINSHAREMODE,UPDATE和DELETEnext-key鎖定，用next-key鎖定或者間隙鎖定鎖住搜索的索引范圍，并且其它用在持續(xù)讀中，有一個與READCOMMITTED級別重要的差別：在這個級別，在同一事務內(nèi)發(fā)出數(shù)個無格式SELECTSELECTREADCOMMITTED級別是一個有些象Oracle的級別。所有SELECT...FORUPDATESELECTLOCKINSHAREMOD在范圍類型UPDATE和DELETE語句，InnoDB必須對范圍覆蓋的間隙設置next-復起作用，“行”必須被掉如果應用是從基于ORACLE的應用遷移到MYSQL數(shù)據(jù)庫的，那么建議使用該級別提供數(shù)據(jù)庫服務，因為該級別是最接近ORACLE的默認級別的，遷移可能如何減少鎖根據(jù)應用的情況，嘗試橫向拆分成多個表或者改成Myisam分區(qū)對減少鎖也會有索引，那么即便你只是要更新其中的一行，也是全表鎖定的。要確保sql是使用索引來的，必要的時候，請使用explainsql執(zhí)行計劃，判里和Oracle有比較大的不同。SHOWINNODBSTATUS事務的詳細信息，包括執(zhí)行的SQL語句的內(nèi)容，每個線程已經(jīng)獲得了什么鎖，在等如果你正使用鎖定讀，（SELECTFORUPDATELOCKINSHARE試著用更低的級別，比如READCOMMITTED14Mysql查看Mysqlserver當前參mysqld--verbose--s>mysqladminvariablesmysql>SHOWs>mysqladminextended-mysql>SHOW影響Mysql性能的重要參key_buffer_size據(jù)需要分配。此參數(shù)只適用于myisam引擎。和mysql>CACHEINDEXt1,t2,t3IN的鍵高速緩沖的大小來創(chuàng)建鍵高速緩沖。例如：SETGLOBALmysql>SETGLOBAL括配置鍵高速緩沖的變量設定值，和一個init-file選項用來命名包含待執(zhí)行的CACHEINDEX語句的一個文件。例如：key_buffer_size=4Ghot_cache.key_buffer_size=2Gcold_cache.key_buffer_size=每次服務器啟動時執(zhí)行mysqld_init.sql中的語句。該文件每行應包含一個SQL下面的例子分配幾個表，分別對應hot_cache和cold_cache：CACHEINDEXa.t1,a.t2,b.t3INhot_cacheCACHEINDEXa.t4,b.t5,b.t6INcold_cache以預裝表t1和t2索引的非葉節(jié)點(索引塊)：mysql>LOADINDEXINTOCACHEt1,t2IGNOREmysql>SETGLOBALcold_cache。table_cache說明：數(shù)據(jù)庫中打開表的緩存數(shù)量。table_cache與max_connections有關(guān)。例如，對mysql>SHOWSTATUSLIKE |Variable_name|Value |Opened_tables|2741 innodb_buffer_pool_size緩存InnoDB數(shù)據(jù)和索引的內(nèi)存緩沖區(qū)的大小。你把這個值設得越高，表中 mit1logbuffer寫據(jù)庫的時候損失一部分數(shù)據(jù)，那么設置成0或者2都會有所改善。設置成0，則在庫的會那有入文的，丟失12到磁盤，但是已經(jīng)寫入日志文件以只要操作系統(tǒng)沒有，那么并沒有丟失數(shù)據(jù)，0建議將這個參數(shù)設置為1的。InnoDB據(jù) 1B用里越你在分多內(nèi)如IoB池內(nèi)的內(nèi)存，InnoDB開始從操作系統(tǒng)分配內(nèi)存，并且往MySQL錯誤日志寫警告信息。大相定。InnoDBLOCKTABLES，直到所有其它線程已經(jīng)釋放他們所有對表的鎖定，MySQLLOCKTABLEWRITE1，這意為LOCKTABLESInnoDB內(nèi)部鎖定一個表。在使用MIT=1應用里，InnoDB部表鎖定會可以通過設置innodb_table_locks=0來消除這個問題。理。默認值是50秒，根據(jù)應用的需要進行調(diào)整。通過該參數(shù)設置是否支持分布式事務，默認值是ON1，表示支持分布式事次數(shù)并獲得更好的InnoDB性能。默認地，InnoDB所有數(shù)據(jù)兩次，第一次到doublewrite緩沖，然后存skip-innodb-doublewrite關(guān)閉這個設置。間。通常8-16MB就足夠了。越小的系統(tǒng)它的值越小。15章I/O使用磁盤陣列或虛擬文件卷分布分條意味著你有許多磁盤，將第1個塊放到第1個硬盤，第2個塊放到第2個磁盤，并且第N（Nmodnumber_of_disks）磁盤上等等。這意味著如果正常數(shù)據(jù)大小小于為了高可靠性你可能想使用RAID0+1（條紋加鏡像），但在這種情況下，需要2*N塊磁盤來保持N個磁盤的數(shù)據(jù)。如果你肯為它花錢，這可能是最好的選項。然而，你可能還必RAID級別。例如，保存可以在RAID0硬盤上重新生成的不太重要的數(shù)據(jù)，但保存真正重要數(shù)據(jù)（例如主機信息和日志）到使用SymbolicLinks可以將表和數(shù)據(jù)庫從數(shù)據(jù)庫移動到其它的位置并且用指向新位置的符號進符號一個數(shù)據(jù)庫的方法是，首先在一些有空閑空間的硬盤上創(chuàng)建一個，然后從MySQL數(shù)據(jù)中創(chuàng)建它的一個符號。s>mkdirs>ln-s/dr1/databases/test 如果mysqld沒有運行，符號可以從服務器命令行使用ln-s手動完成。同樣，通過使DATADIRECTORYINDEXDIRECTORY選項創(chuàng)建表，你可以指示運行的MySQL任何臨時文件創(chuàng)建在數(shù)據(jù)文件或索引文件所處的中。數(shù)據(jù)庫有寫權(quán)限的原因。庫中的符號被重新命名為一個新名字并且數(shù)據(jù)文件和索引文件也相應地重新 能夠使用mysqld來刪除或重新命名數(shù)據(jù) ALTERTABLEDATADIRECTORYINDEXDIRECTORYBACKUPTABLE和RESTORETABLE不考慮符號16章對于數(shù)據(jù)庫來說，建立連接的代價比較昂貴，因此，我們有必要建立"連接池"以 后 減少對Mysqlmysqlqueryshowvariableslike‘%query_cache%’;query_cache_size表明緩存區(qū)大小，單位為Mquery_cache_type的變量值從0到2，含義分別為或者off（緩存關(guān)閉變含變含沒有被緩存的查詢數(shù)目(不能被緩存的，或由于cacheCache（高速緩存）、Memory（內(nèi)存）、Harddisk（硬盤）都是數(shù)據(jù)存取單元，但存取速PUCache數(shù)，不從存硬盤低個量速度存數(shù)。而Cache的是CPU（或程序）證Cache中率（HitRte）。因此，CPU內(nèi)容肯定是只讀的。需要你做的工作很少，程序中的SQL語句和直接DBMS時沒有分別，返回的結(jié)果也看不出有什么差別。而數(shù)據(jù)庫廠商往往會在DBServer的配置文件中提供與Cache相關(guān)的參數(shù)，通過修改它們，可針對我們的應用優(yōu)化Cache的管理。負載均利用mysql分流查詢操作mysql從5.0.3開始支持分布式事務，當前分布式事務只對 引擎支持現(xiàn)的時候，可以使用mysql的Cluster功能（NDB引擎）或者自己編寫程序來實現(xiàn)全局事務第三篇管理17章mysql安裝多個mysql裝多個mysql需要單獨客戶es庫庫庫sql-share/mysql基準程序和crashme試sql-benchrpms>rpm-ivhMySQL-server-VERSION.i386.rpms>rpm-ivhMySQL-client-二進制安裝步s>groupadds>useradd-gmysqlmysqls>cd/home/mysqls>tar-xzvf/home/mysql/mysql-VERSION-OS.tar.gzs>ln-smysql-VERSION-OS.tar.gzmysqls>cds>scripts/mysql_install_db--user=mysqls>chown-Rroot:mysql.s>chown-Rmysql:mysqldatas>bin/mysqld_safe--user=mysql&s>groupadds>useradd-gmysqls>gunzip<mysql-VERSION.tar.gz|tar-xvf-s>cdmysql-VERSIONs>./configure--prefix=/usr/local/mysqls>makes>makes>cpsupport-f s>cd/usr/local/mysqls>bin/mysql_install_db--user=mysqls>chown-Rroot.s>chown-Rmysqlvars>chgrp-Rmysql.bin/mysql_install_db--s>./configure--s>./configure--without- 似于下列configure命令的一個： (通常是“/usr/local/var”)并且把它改為/usr/local/mysql/data。編譯完MySQL后，可以通過選項文件更改這些選項修改sockets>./configure\--with-unix-socket-只選擇要使用的字符集s>./configure--with-./configure--with-s>./configure--with-extra-charsets=LISTlist可以是下面任何一項：all–，以將所有字符集包括進二進制使用pgcc編譯pgcc2.90.29CFLAGS="-O3-mpentiumpro-mstack-align-double"CXX=gccCXXFLAGS="-O3-mpentiumpro-mstack-align-double-felide-constructors-fno-exceptions-fno-s --with-client-ldflags=-allwith-mysqld-ldflags=-all-mysqls>mysqladmin-hhostname-Pport-uuser-ppasswdcreatedb_names>mysqldump--optdb_name|mysql-h hostname-Pport-uuser-ppasswd mysql數(shù)據(jù)庫全部cpmysql在s里面執(zhí)行mysql_fix_privilege_tables命令升級權(quán)限表 s>mkdirs>mysqldump--tab=DUMPDIR將DUMPDIR s>mysqladmincreatedb_name #createdatabases>catDUMPDIR/*.sql|mysqldb_name #createtablesindatabases>mysqlimportdb_nameDUMPDIR/*.txt #loaddataintotablesmysql數(shù)據(jù)庫全部cpmysql在s里面執(zhí)行mysql_fix_privilege_tables命令升級權(quán)限表 下的所有文件（.frm，.MYD，MYI）cp到新庫下的相應 mysql數(shù)據(jù)庫全部cpmysql在s里面執(zhí)行mysql_fix_privilege_tables命令升級權(quán)限表mysql沒有給定file_name值，mysqld使用錯誤日志名 并在數(shù) 中寫s>mysqlbinloglog-PURGEMASTERLOGSTOmysql-bin.010';//刪除mysql-bin.010之前所有日志PURGEMASTERLOGSBEFORE'2003-04-0222:46:26'2003-04-0222:46:26之前產(chǎn)生的所有日志告訴主服務器，如果當前的數(shù)據(jù)庫(即USE選定的數(shù)據(jù)庫)是db_name，應將更新記錄到二進制日志中。其它所有沒有明顯指定的數(shù)據(jù)庫被忽略 具有SUPER權(quán)限的客戶端可以通過SETSQL_LOG_BIN=0語句將自己的語句查詢?nèi)沼?-log[=file_name]或-l[file_name]選項啟動它。如果沒有給定file_name的值，用--log-slow-ueries[file_nae]選項啟動它。如果沒有給出file_name。使用mysqldumpslow命令獲得日志中顯示的查詢來處理慢查詢?nèi)罩?，例如[zzx@bj37data]$mysqldumpslowbj37-理語句，例如OPTIMIZETABLE、YZETABLE和ALTERTABLE寫入慢查詢?nèi)?9冷備份 mysqldump–uroot–p***pointcard–F>mysql–uroot–p***pointcard<使用mysqlbinlogmysqldumpbinlog。mysqlbinlog$HOME/data/mysql-bin.123456|mysqlurootp***單個表的備份mysql>select*intooutfile‘/tmp/order_tab’fields-terminated-frommysqldump–uroot–p***–T/tmppointcardorder_tab--fields-mysql>loaddata[local]infile order_tabfields-terminated-by=’,’;mysqlimport–uroot–p*** pointcatdorder_tab.txt--terminated-by，windowsline-terminated-by=’\r\n’，linux上設置為使用備份工具ibbackup是innodb公司（ )的一個熱備份工具，專門對innodb引擎進行物理熱備份，此工具是的，不能免費使用。現(xiàn)在innodb公司已經(jīng)被oracle datadir=innodb_data_home_dir=/home/heikki/datainnodb_data_file_path=ibdata1:10M:autoextendinnodb_log_group_home_dir=/home/heikki/dataset-variable=innodb_log_files_in_group=2set-variable=innodb_log_file_size=20M datadir=/home/heikki/backupinnodb_data_home_dir=/home/heikki/backupinnodb_data_file_path=ibdata1:10M:autoextendinnodb_log_group_home_dir=/home/heikki/backupset-variable=innodb_log_files_in_group=2set-variable= ibbackup--apply- ./bin/mysqld_saft-- f mysqlbinlog--stop-date="2005-04-20/var/log/mysql/bin.123456|mysql-urootmysqlbinlog--start-date="2005-04-20/var/log/mysql/bin.123456|mysql-uroot-pmypwdmysqlbinlog--start-date="2005-04-209:55:00"--stop-date="2005-04-2010:05:00"/var/log/mysql/bin.123456>/tmp/mysql_restore.sql 如前后位置號分別是368312和368315?；謴土艘郧暗膫浞菸募螅銘獜拿钚衜ysqlbinlog--stop-position="368312"/var/log/mysql/bin.123456|mysql-uroot-mysqlbinlog--start-position="368315"/var/log/mysql/bin.123456|mysql-uroot-pmypwdmysqlbinlog的輸出包括每個SQL之前的SETTIMESTAMPMySQLMyISAM修復“tbl_name.frm”被鎖定不能更改不能找到文件“tbl_name.MYI”（Errcode：nnn）myisamchk-rmyisamchk-oCHECKTABLEtbl_name[,tbl_name]...[option]option={QUICK|FAST|MEDIUM|EXTENDED|REPAIR[LOCAL|NO_WRITE_TO_BINLOG]tbl_name[,tbl_name]...[QUICK][EXT