javaee企業(yè)級分布式高級架構(gòu)師-開課吧17mysql

MySQLMySQLMySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，在WEB應(yīng)用方 MySQL是最好RDBMS(RelationalDatabaseManagementSystem：關(guān)系數(shù)據(jù)庫管理系統(tǒng))應(yīng)用軟件可以通過SQL對數(shù)據(jù)庫中的數(shù)據(jù)進行增加，修改，刪除及查詢操作。數(shù)據(jù)庫中的記錄是有行有列的數(shù)據(jù)庫NoSQLSystemDBMSMYSQL：開源免費OracleMySQL5.5都是由Oracle發(fā)布的版本。 Oracle ：IBM公司的數(shù)據(jù)庫產(chǎn)品,的。常應(yīng)用在銀行系統(tǒng)中.的互聯(lián)網(wǎng)公司，要求去IOE（IBM小型機、Oracle數(shù)據(jù)庫、EMC設(shè)備） 公司的中型的數(shù)據(jù)庫。C#、.net等語言常使用SyBasePowerDesigner:嵌入式的小型數(shù)據(jù)庫，應(yīng)用在端。MySQLMySQL1979MontyWideniusTcX上運行。當時，這只是一個很底層的且僅面向報表的引擎，名叫Unireg。1990年，TcX公司的客戶中開始有人要求為他的API提供SQL支持。Monty直接借助于mSQL的代碼，將它集成到自己的引擎中。令人失望的是，效果并不太令人滿意，決心自己重寫一個SQL支持。1996MySQL1.0199610MySQL3.11.1(MySQL2.xSolaris1999～2000MySQLAB公司在瑞典成立。MontySleepycatBerkeleyDB,由于BDBMySQL開始支持事務(wù)處理2000，MySQL不僅公布自己的源代碼，并采用GPL(GNUGeneralPublicLicense)協(xié)議，正式進入開源世界。同年4月，MySQL對舊的引擎ISAM進行了整理，將其命名為MyISAM。2001年，集成HeikkiTuuri的引擎InnoDB，這個引擎不僅能持事務(wù)處理，并且支持行級鎖。后來該引擎被證明是最為成功的MySQL事務(wù)引擎。MySQL與InnoDB的正式結(jié)合版本是4.02003年12月，MySQL5.0版本發(fā)布，提供了視圖、過程等功能SunSunBug的NDB集群系統(tǒng)，同時修復(fù)了大量的Bug。2009年4月，Oracle公司以74億收購Sun公司，自此MySQL數(shù)據(jù)庫進入時代，而其第的引擎InnoDB早在2005年就被Oracle公司收購擎。MySQL5.5MySQL業(yè)級的特性。Oracle公司同時也承諾MySQL5.5和未來版本仍是采用GPL的開源產(chǎn)MySQLMySQLMysQL啟動和關(guān)閉（cmd1：cmd>mysqlu用戶名–p例如：mysql-uroot–proot2：cmd>mysql--user=用戶名--host=ip地址--SQLyog安裝與使用輸入用戶名、，點擊連接按鈕，進行MySQL數(shù)據(jù)庫進行操Q(mào)uerySQLSQLF8SQLSQLStructuredQueryLanguageSQLmysqlSQL92SQL99SQL擴展了自己的一些SQL語句，比如MySQL中的limit關(guān)鍵字SQL數(shù)據(jù)定義語言：簡稱DDL(DataDefinitionLanguage)，用來定義數(shù)據(jù)庫對象：數(shù)據(jù)庫，表，列等。關(guān)鍵字：create，alter，drop等DML(DataManipulationLanguage)，用來對數(shù)據(jù)庫中表的記錄進行更新。關(guān)鍵字：insert，delete，update等數(shù)據(jù)控制語言：簡稱DCL(DataControlLanguage)，用來定義數(shù)據(jù)庫的權(quán)限和安全級別，及創(chuàng)建用戶；關(guān)鍵字：grantselect，from，where等SQL數(shù)據(jù)庫操作createdatabase數(shù)據(jù)庫名;createdatabase數(shù)據(jù)庫名characterset字符集;查看數(shù)據(jù)庫服務(wù)器中的所有的數(shù)據(jù)庫:show查看數(shù)據(jù)庫服務(wù)器中的所有的數(shù)據(jù)庫:show查看某個數(shù)據(jù)庫的定義的信息:showcreatedatabase數(shù)據(jù)庫名;dropdropdatabase數(shù)據(jù)庫名稱;切換數(shù)據(jù)庫：use切換數(shù)據(jù)庫：use數(shù)據(jù)庫名;查看正在使用的數(shù)據(jù)庫:selectcreatedatabaseday06;use表操作浮點型boolean類型：不支持壓縮嚴格的定點 開發(fā)時 -YYYY-MM-DD1000-01-01~9999-12-YYYY-MM-DDHH:MM:SS1000-01-019999-12-31YYYY-MM-DD 1970~01~01UTC~2038-01-19M0~255M0~655350~2550~65535允許長度 字允許長度 字0~2550~65535允許長度 字允許長度 字0~M0~Mcreatecreatetable表名(字段名類型(長度)約束,字段名類型(長度)約束*主鍵約束：primary唯一約束唯一約束非空約束：notCREATETABLEsortsidINTPRIMARYKEY,#分類snameVARCHAR(100)查看數(shù)據(jù)庫中的所有表：show查看數(shù)據(jù)庫中的所有表：show查看表結(jié)構(gòu)：desc表名;dropdroptable表名;alteraltertable表名add列名類型(長度)約束 --修改表添加列altertable表名modify列名類型(長度)約束; altertable表名change舊列名新列名類型(長度)約束; altertable表名drop列名; renametable表名to新表名; altertable表名characterset字符集; 插入記錄insertinsertinto表(列名123..values(值123..)insertinto表values123..);insertinto表(列名123..valuesselect123from表insertinto表valuesselect*from表values插入值得時候過最大長度（一般是單引號INSERTINSERTINTOsort(sid,snameVALUES('s001'INSERTINTOsort(sid,snameVALUES('s002'INSERTINTOsortVALUES('s003','化妝品');更新記錄updateupdate表名setupdate表名set字段名=值,字段名=值where條件;修改值得時候過最大長度刪除記錄deletedeletefrom表名[where條件];deletefrom表名;還是用truncatetable表名;刪除方式：delete一條一條刪除，不清空auto_increment記錄數(shù)。truncate直接將表刪除，重新建表，auto_incrementCMDmy.inimysqlSQLintvarchardatedatetimeCREATETABLEproductspidINTPRIMARYKEYAUTO_INCREMENT,#自增加priceDOUBLE,#商品價格pdateDATE,#日期sidVARCHAR(20)INSERTINTOproductsVALUES(NULL,' 大榴蓮',98,NULL,'s001');INSERTINTOproductsVALUES(NULL,' 大棗',38,NULL,'s002');INSERTINTOproductsVALUES(NULL,' 切糕',68,NULL,'s001');INSERTINTOproductsVALUES(NULL,'十三香',10,NULL,'s002');INSERTINTOproductsVALUES(NULL,'老干媽',20,NULL,'s002');SELECTSELECT[DISTINCT]*[FROM表[WHERESQLselect*from查詢商品名和商品價格.selectpname,pricefrom別名查詢，使用的as，asselect*fromproductas列別名：selectpnameaspnfrom去掉重復(fù)值.selectdistinctpricefromselectselectpname,price+10fromSQLselect*fromproductwherepname查詢商品價格>60select*fromproductwhereprice>where>like使用占位符_和 _代表一個字符%代表任意個字符select*fromproductwherepnamelikeinselect*fromproductwherepidin =inLIKE模糊查詢，Like符，_代表一個字符，例first_namelike‘IS不成立，例：whereSQLORDERASC（升序）DESC（降序select*fromproductorderbyselect*fromproductwherepnamelikeorderbyprice聚合函數(shù)（組函數(shù)獲得所有商品的價格的總和：select獲得所有商品的價格的總和：selectsum(price)from獲得所有商品的平均價格：selectavg(price)from獲得所有商品的個數(shù)：selectcount(*)fromSQLGROUP根根 o字段分組，分組后統(tǒng)計商品的個數(shù)selectcid,count(*)fromproductgroupby根 o分組，分組統(tǒng)計每組商品的平均價格，并且平均價格>selectcid,avg(price)fromproductgroupbycidhavingselect（非聚合函數(shù)列g(shù)roupbygroupbyselectselecthavingwhereunion集合的并集（不包含重復(fù)記錄unionall（包含重復(fù)記錄SQL（重要MySQLLEFTGROUPORDER示例SQL：SELECT*FROMuserLEFTJOINorderONuser.id=order.uidWHEREorder.price>GROUPBYHAVINGcount(1)>5ORDERBYLIMITFROM（將最近的兩張表，進行積）---ON（VT1）---LEFTJOIN（保留左表的記錄）---WHERE（VT3）--GROUPBY（VT4）---對SELECT（VT6）--ORDERBY（VT7）WHERE（影響性能MYSQL：WHEREOracleWHEREWHEREcreatetablecidvarchar(32)primarycnamecreatetablepidvarchar(32)primarypnamepricecreatetableoidvarchar(32)primarytotalpricecreatetableoidoidvarchar(50),pidvarchar(50)多對多關(guān)系（需要中間表實現(xiàn)如何表示表與表之間的關(guān)系呢？就是使用外鍵來約束要想理解外鍵，我們先去理解表的角色：主表和從表（需要建立關(guān)系才有了主從表的角分目前從表的上來說，沒有關(guān)系，但是我們有個需求商品應(yīng)該有所屬的分類altertable表名add[constraint][約束名稱]foreignkey(主表外鍵字段)references從altertable表名dropforeignkey外鍵約束名保證數(shù)據(jù)完整性（數(shù)據(jù)保存在多張表中的時候在互聯(lián)網(wǎng)項目中，一般情況下，不建議建立外鍵關(guān)系一對一關(guān)系（了解idINTPRIMARYKEY,wnameVARCHAR(20),sexCHAR(1)idINTPRIMARYKEY,hnameVARCHAR(20),sexCHAR(1)1外鍵唯一添加外鍵列wid，指定該列的約束為唯一（不加唯一約束就是一對多關(guān)系）ALTERTABLEhusbandADDwidINT altertablehusbandaddforeignkey(wid)references2主鍵做外鍵（課后作業(yè)總結(jié)：有外鍵的就是多的一方cid本章節(jié)向大家介紹如何使用MySQL的JOIN在兩個或多個表中查詢數(shù)據(jù)你可以在SELECTUPDATE和DELETE語句中使用MySQL的JOIN來聯(lián)合多表查詢。JOIN按照功能CROSSJOIN（交叉連接INNERJOIN（內(nèi)連接或等值連接OUTERJOIN（外連接insertinsertintocategoryame)valuesc001insertintocategoryame)valuesc002insertintocategoryame)valuesc003insertintoproduct(pid,pname,price,cid)values('p001','聯(lián)想insertintoproduct(pid,pname,price,cidvalues('p002','海爾',3000,'c001');insertintoproduct(pid,pname,price,cid)values('p003','雷神insertintoproduct(pid,pname,price,cid)values('p004','阿迪insertintoproduct(pid,pname,price,cid)values('p005','耐克',1200,'c002');insertintoproduct(pid,pname,price,cid)values('p006','NB',800,'c002');insertintoproduct(pid,pname,price,cid)values('p007','彪馬',600,'c002');insertintoproduct(pid,pname,price,cid)values('p008','雪花秀insertinsertintoproduct(pid,pname,price,cid)values('p009','悅詩風吟交叉連接的關(guān)鍵字：CROSS交叉連接也叫積連product第一個對象是X的成員而第二個對象是Y的所有可能有序?qū)Φ钠渲幸粋€成員。交叉連接的表現(xiàn)：行數(shù)相乘、列數(shù)相 A, 案例：查詢商品表和分類表的積內(nèi)連接的關(guān)鍵字：INNER內(nèi)連接也叫等值連接 A.id= INNER BONA.id=外聯(lián)接可以是左向外聯(lián)接、右向外聯(lián)接或完整外部聯(lián)接。也就是說外連接又分為：連接、全外連接外連接需要有主表或者保留表在FROM左外連接：LEFTJOIN或者LEFTOUTER BONA.id=右外連接:：RIGHTJOIN或者RIGHTOUTER BONA.id=全外連接（MySQL不支持：FULL JOIN或FULLOUTER FULL BONA.id=外連接總結(jié)LEFTJOINRIGHTJOINMySQLLIMIT關(guān)鍵字不是SQL92標準關(guān)鍵字，它是MySQL獨有的語法LimitMySQLLIMITSELECTSELECT*FROMtableLIMIT[offset,]1.分頁查詢商品表，每頁3子查詢，又叫內(nèi)部查詢，相對于內(nèi)部查詢，包含內(nèi)部查詢的就稱為外部查詢select：distinct、groupby、orderby、limit、join和union等；select中、from后、wheregroupby和orderby中無實用意義。1.WHERE（這個中間表對用戶是不可見的然后根據(jù)SELECT的選擇列選擇相應(yīng)的列進行返回最終結(jié)果。然后根據(jù)WHERE條件過濾中間表的記錄，并根據(jù)SELECT指定的列返回查詢結(jié)果。條件過濾中間表的記錄，并根據(jù)SELECT指定的列返回查詢結(jié)果。SQLSQLMySQLSQLManagementServeices&ConnectionPool:連接池***負責對接上MySQLServer的客戶端請求都會被分配（或創(chuàng)建*而連接線程的主要工作就是負責MySQLServerServercache等。SQLInterfaceSQLSQLselectfromSQLParser:解析器SQLSQL驗證和解析SQL然后做出針對性的轉(zhuǎn)發(fā)到后續(xù)步驟，以后SQL語句的傳遞和處理就是基于這個結(jié)構(gòu)的。sqlOptimizer:查詢優(yōu)化器SQLSQL使用查詢優(yōu)化器對查詢進行優(yōu)化用一個例子就可以理解：selectuid,namefromuserwheregenderselect查詢先根據(jù)whereselectuidname濾CacheBuffer：查詢緩存他的主要功能是將客戶端提交給MySQL的select請求的返回結(jié)果集cachequery的一個hash值做一個對應(yīng)。該Query所取數(shù)據(jù)的基表發(fā)生任何數(shù)據(jù)的變化之后，MySQL會自動使該query的Cache失效。在讀寫比例非常高的應(yīng)用系統(tǒng)中，QueryCache對系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權(quán)限緩存等引擎接口模塊可以說是MySQL引擎接口模塊可以說是MySQL只有MySQL可以實現(xiàn)其底層數(shù)據(jù)正是因為它成功地將各種數(shù)據(jù)處理高度抽象化，才成就了今天MySQL可插拔從圖還可以看出，MySQL區(qū)別于其他數(shù)據(jù)庫的最重要的特點就是從圖還可以看出，MySQL區(qū)別于其他數(shù)據(jù)庫的最重要的特點就是引擎MySQL插件式的* 引* 引擎是mysql有別于其他數(shù)據(jù)庫的一大特性**MySQL5.5MyISAMInnoDB*查看引擎：show.frm表定義文件.ibd數(shù)據(jù)文件.frm表定義文件 .myi索引文件鎖專門的地B+B+MySQLMySQL是通過文件系統(tǒng)對數(shù)據(jù)進行和管理的MySQLMySQL錯誤日志、二進制日志、查詢?nèi)罩?、慢查詢?nèi)罩竞虸nnoDB引擎Redo日志、中繼日志等。錯誤日志（errlog：5.5.7記錄了運行過程中遇到的所有嚴重的錯誤信息,以及MySQL每次啟動和關(guān)閉的詳細信息log-errorlog-warningslog-err二進制日志（binlog：**log-bin=mysql-binmysql-binbinlogbasename，binlog：mysql-bin-*binlogddldmlselectbinlog**如果是DDL語句，則直接記錄到binlog日志，而DML*binlogmysql。通用查詢?nèi)罩荆╣eneralquerylog：慢查詢?nèi)罩荆╯lowquerylog：默認是關(guān)閉的。默認是關(guān)閉的。需要通過設(shè)置：slow_query_log=ONlong_query_timeSQL事務(wù)日志（InnoDB事務(wù)日志（InnoDB）redoib_logfile1。*undo日志，默認**是在主從*SQLMySQLSHOWVARIABLESLIKE.frm主要存放與表相關(guān)的數(shù)據(jù)信息,主要包括.ibd和.ibdata文件：用 引擎的表數(shù)據(jù)和索引信.myd文件：主要用 使用 引擎的表數(shù)據(jù)信息.myi文件：主要用 使用 引擎的表數(shù)據(jù)文件中任何索引的數(shù)據(jù)樹MySQL，能快數(shù)據(jù)庫的查詢速度 MyISAM和引擎：只支持BTREE，也就是說默認使用BTREE，夠更 HASHBTREE*普通索引：ALTERALTERTABLEtable_nameADDINDEXindex_nameONCREATEUNIQUEINDEXindex_nameONCREATEFULLTEXTINDEXindex_nameONCREATEINDEXindex_nameONALTERTABLEarticleADDINDEXindex_titme_timeDROPDROPINDEXindex_nameON索引的結(jié)2-4IO BTreeB+Tree數(shù)據(jù)的問題。BTreeB+Tree非索**葉子節(jié)點只會*索引**主鍵索引 索引）的葉子節(jié)點 數(shù)據(jù)行，也就是說數(shù)據(jù)和索引是在一起，這就 *輔助索引只會*創(chuàng)創(chuàng) 索引使用索引的注意事MySQLMySQL提供了一個EXPLAIN命令,它可以對SELECT語句進行分析,并輸出SELECT執(zhí)行的詳細信息,以供開發(fā)人員針對性優(yōu)化.使用explain這個命令來查看一個這些SQL語句的執(zhí)行計劃，查看該SQL語句有沒有使用上了索引，有沒有做全表掃描，這都可以通過explain命令來查看?？梢酝ㄟ^explain命令深入了解MySQL的基于開銷的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的策略的細節(jié)，以及當運行SQL語句時哪種策略預(yù)計會被優(yōu)化器采用。EXPLAIN命令用法十分簡單,在SELECT語句前加上explain就可以了,例如:idSELECT查詢的標識符.每個SELECTselect_typeSELECT查詢的類型.table:查詢的是哪個表partitions:匹配的分區(qū)typejoin類型possible_keyskeyref:哪個字段或常數(shù)與key一起被使用rowsfiltered:extra:額外的信息select_typeSIMPLE,表示此查詢不包含UNION查詢或子查詢PRIMARYUNION,表示此查詢是UNION的第二或隨后的查詢DEPENDENTUNIONUNIONUNIONRESULTUNION的結(jié)果SUBQUERY,子查詢中的第一個DEPENDENTSUBQUERY:子查詢中的第一個SELECT,取決于外面的查詢.即子查type通常來說,不同的type類型的性能關(guān)系如下:ALL<index<range~index_merge<ref<eq_ref<const<每次與之前的表合并行都只在該表一行，這是除了system，const之joinref這個比較復(fù)雜，目前的理解是合并單表的范圍索引掃描（rangeinvaluein(select...)把形如“selectunique_key_coluPS：in同上，但把形如”selectnon_unique_key_column當查詢是索引覆蓋的，即所有數(shù)據(jù)均可從索引樹獲取的時候（ExtraUsing以索引順序從索引中查找數(shù)據(jù)行的全表掃描（無UsingExtraUsingIndexUsingWhere參考 MySQLSQL得先找到需要優(yōu)化的SQL。bugMySQL數(shù)據(jù)庫有一個“慢查詢?nèi)罩尽惫δ?，用來記錄查詢時間bugMySQL默認是關(guān)閉的，需要手動開啟參數(shù)說** ：是否開啟慢查詢?nèi)罩荆琌N為開啟，OFF*log-slow- ：舊版（5.6）MySQLhost_name-*slow-query-log-file：新版（5.6）MySQLhost_name-*long_query_time在在MySQL執(zhí)行SQL語句設(shè)置，但是如果重啟MySQL的話將失效setglobalslow_query_log=ON;setgloballong_query_time=1;f配置文件，重啟MySQL,這種永久生效.slow_query_log=slow_query_log_file=/var/log/mysql/slow.loglong_query_time=1第一行,SQL第一行,SQLSQLQuery_timeSQL,在第五行及后面所有行（Time:之前）sqlsqlMySQL[root@localhost[root@localhostmysql]#mysqldumpslow/var/lib/mysql/localhost-常用參數(shù)說明-s：-ttopn-g：10mysqldumpslow-st-t10-g“l(fā)eftjoin”mysqlsla不過此工具已停止，項目介紹頁面推薦使用percona-toolkit，下面有介紹mysqlslamysqlsla-lt tartar-xfpercona-toolkit-perlmakeCan'tCan'tlocateExtUtils/MakeMaker.pmin@INC*yuminstall-yperl-ExtUtils-CBuilderperl-ExtUtils-Can'tlocateTime/HiRes.pmin**yuminstall-yperl-Time-使用MySQL性能分析語句showQueryProfilerMYSQLquerySQLexplainslowquerylogQueryProfiler耗費的時間等。不過該工具只有在MYSQL5.0.37以及以上版本中才有實現(xiàn)。showprofile和showprofilessession,profiling為0)中執(zhí)行語句的資源使用情況.showprofiles:以列表形式顯示最近發(fā)送到服務(wù)器上執(zhí)行的語句的資源使用情況.顯示的記錄數(shù)由變量:profiling_history_size控制,默認15條showprofile:展示最近一條語句執(zhí)行的詳細資源占用信息,默認顯示Status和showprofile還可根據(jù)showprofiles列表中的Query_ID,選擇顯示某條記錄的性能分ProfileProfile功能由MySQL會話變量:profilingOFFProfileselectselectshowvariableslikeprofile**setprofiling=1;--10selectselect打開profiling功能setsetsql執(zhí)行showprofiles查看分析列表showshowprofileforqueryshowshowprofilecpu,swapsforqueryMySQL在MySQL中只有使用了Innodb數(shù)據(jù)庫引擎的數(shù)據(jù)庫或表才支持事務(wù)事務(wù)處理可以用來數(shù)據(jù)庫的完整性，保證成批的SQL語句要么全部執(zhí)行，要么全部不執(zhí)DDL、DML、DCL操作，比如insert,update,delete語句4（ACIDIsolation（性ACID原子性：：性：：MySQL在MySQL命令行的默認設(shè)置下，事務(wù)都是自動提交的，即執(zhí)行SQL語句后就會馬上執(zhí)行COMMIT操作。因此要顯式地開啟一個事務(wù)務(wù)須使用命令BEGIN或START或者執(zhí)行命令 使用當前會話的自動提交在事務(wù)的并發(fā)操作臟讀：一個事務(wù)到另一個事務(wù)未提交的數(shù)據(jù)不可重復(fù)讀：一個事務(wù)因到另一個事務(wù)已提交的數(shù)據(jù)。導(dǎo)致對同一條記錄兩次以上的結(jié)果不一致。updae幻讀：一個事務(wù)因到另一個事務(wù)已提交的數(shù)據(jù)。導(dǎo)致對同一張表兩次以上的結(jié)果不致。inert、delete事務(wù)級四種級別現(xiàn)在來看看MySQL數(shù)據(jù)庫為我們提供的四種級別（由低到高① mitted②Readcommitted③Repeatableread④Serializable默認級大多數(shù)數(shù)據(jù)庫的默認級別是Readcommitted，比如Oracle、DB2等MySQL數(shù)據(jù)庫的默認級別是Repeatableread如何查看和設(shè)置級別在MySQL數(shù)據(jù)庫中查看當前事務(wù)的級別selectselect在MySQL數(shù)據(jù)庫中設(shè)置事務(wù)的級別 [glogal| transactionisolationsetMySQL***數(shù)據(jù)庫MySQL *總的來說，MySQL和行級鎖定相反，表級別的鎖定是MySQL各引擎中最大顆粒度的鎖定機制。該鎖定機制最大MySQL總的來說，MySQL3***LockMySQL實現(xiàn)的表級鎖定的爭用狀態(tài)變量：showstatuslike**table_locks_immediatelocktable表名稱read(write),2read(write)showopenunlockCREATECREATETABLE`mylock``id`int(11)NOTNULL`NAME`varchar(20)DEFAULTNULL,PRIMARYKEY(`id`));INSERTINSERTINTOmylock(id,NAME)VALUES(1,INSERTINTOmylock(id,NAME)VALUES(2,'b');INSERTINTOmylock(id,NAME)VALUES(3,'c');INSERTINTOmylock(id,NAME)VALUES(4,共享鎖（S）：允許一個事務(wù)去讀一行，其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖ISIXSELECT，InnoDB共享鎖（S）：SELECT*FROMtable_nameWHERELOCKINSHAREMODE排他鎖（X)：SELECT*FROMtable_nameWHEREFORUPDATEInnoDB的行級鎖定同樣分為兩種類型，共享鎖和排他鎖InnoDB的行級鎖定同樣分為兩種類型，共享鎖和排他鎖享鎖和意向排他鎖InnodbKEYlocking（因為Innodbshowstatuslike5mysql>mysql>createtabletest_innodb_lock(aint(11),bvarchar(16))engine=innodb;QueryOK,0rowsaffected(0.02sec)mysql>createindextest_innodb_a_idxontest_innodb_lock(a);QueryOK,0rowsaffected(0.05sec)Records:0Duplicates:0Warnings:mysql>createindextest_innodb_lock_b_idxontest_innodb_lock(b);QueryOK,11rowsaffected(0.01sec)Records:11Duplicates:0Warnings:SessionSession1mysql> QueryOK,0rowsaffected(0.00mysql> QueryOK,0rowsaffected(0.00mysql>updatetest_innodb_locksetb='b1'wherea=1;QueryOK,1rowaffected(0.00Rowsmatched:1Changed:1Warnings:2mysql>updatetest_innodb_locksetb='b1'where=3mysql>commitQueryOK,0rowsaffected(0.05sec)提交4mysql>updatetest_innodb_locksetb='b1'where=QueryOK,0rowsaffected(36.14sec)Rowsmatched:1Changed:0Warnings05mysql>updatetest_innodb_locksetb='2'whereb=2000;QueryOK,1rowaffected(0.02Rowsmatched:1Changed:1Warnings:mysql>updatetest_innodb_locksetb='3'where=67mysql>commit;QueryOK,0rowsaffected(0.10sec)8mysql>updatetest_innodb_locksetb='3'where=QueryOK,1rowaffected(1min3.41Rowsmatched:1Changed:1Warnings:9mysql>select*from|a|b|1|b2|3|3|4|4000|5|5000|6|6000|7|7000|8|8000|9|9000|1|b19rowsinset(0.00mysql>updatetest_innodb_locksetb=a*100wherea<4anda>1;QueryOK,1rowaffected(0.02Rowsmatched:1Changed:1Warnings:mysql>insertintotest_innodb_lockmysql>QueryOK,0rowsaffected(0.02mysql>insertintotest_innodb_lockvalues(2,'200');QueryOK,1rowaffected(38.68sec)mysql>updatetest_innodb_locksetb='bbbbb'wherea=1andb='b2';QueryOK,1rowaffected(0.00Rowsmatched:1Changed:1Warnings:mysql>updatetest_innodb_locksetb'bbbbb'wherea=1andb='b1';被阻塞mysql>QueryOK,0rowsaffected(0.02mysql>updatetest_innodb_locksetb='bbbbb'wherea=1andb='b1';QueryOK,1rowaffected(42.89sec)Rowsmatched:1Changed:1Warnings0session提交事務(wù)，阻塞去除，更新完成mysql>updatet1setid=110whereid=11;QueryOK,0rowsaffected(0.00sec)Rowsmatched:0Changed:0Warnings:mysql>updatet2setid=210whereid=21;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:mysql>updatet2setid=2100where等待sessionbmysql>updatet1setid=1100whereid=11;QueryOK,0rowsaffected(0.39sec)Rowsmatched:0Changed:0等待sessiona兩個session主從原binlogrelay查看binlog日 mysqlbinlog文件名主從實在[mysqld]IDIPmysqlserviceservicemysqld第三步：建立帳戶并mysql>GRANTmysql>GRANTFILEON*.*TO'backup'@'%'IDENTIFIEDBYmysql>GRANTREPLICATIONSLAVE,REPLICATIONCLIENTON*.*toidentifiedidentifiedby“%”代替，如mysql>mysql>FLUSHmysqlmysql>selectmysql>selectuser,hostfrommastermysql>mysql>showmaster | |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set |mysql-bin.000001 120| | 1rowinUUIDffFatalerror:TheslaveI/OthreadstopsbecausemasterandslavehaveequalMySQLserverUUIDs;theseUUIDsmustbedifferentforreplicationtowork.mysqlmysqluuid注意語句中間不要斷開，master_portmysql(無引號)，master_user120”120showmasterstatus中看到的position值，這里的mysql-bin.000001就是file對應(yīng)的值)。第四步：啟動從服務(wù)器功mysql>start第五步：檢查從服務(wù)器功能狀態(tài)mysql>showslave Slave_IO_Running:Yes//此狀態(tài)必須YESSlave_SQL_Running:YesYES 注：Slave_IO及Slave_SQL進程必須正常運行，即YESNOProxy創(chuàng) f文修改rw-splitting.luamysql-mysql- f注意事項：如果沒有配置profile文件的環(huán)境變量，則需要去擁有mysql-proxy通MyCat： 支持事務(wù)、ACID、可以替代Mysql的加強版數(shù)據(jù)庫一個可以視為“MysqlOracle一個融合內(nèi)存緩存技術(shù)、Nosql技術(shù)、HDFS大數(shù)據(jù)的新型SQLServer支持SQL92MysqlProxy支持JDBC連接ORACLE、DB2、SQLServer，將其模擬為 Server使galeraformysqlpercona-clustermariadbcluster，提供高可用性數(shù)據(jù)分片集支持讀寫分離，支持Mysql雙主多從，以及一主多從的模式E-R關(guān)系的分片策略，實現(xiàn)了高效的表關(guān)聯(lián)查詢MySQLinstanceMySQLMySQLinstanceMySQLinstanceMySQLinstance(MySQLMySQLSocketProtocolSQLSQLSQLHeartBeat（數(shù)據(jù)庫、Table（數(shù)據(jù)表、User(用戶)的邏輯模型，并將這套邏輯模型映射到后端的節(jié)點DataNode（MySQLInstance）Mysql編程語言都能將MyCAT當成是MysqlServer來使用，不必開發(fā)新的客戶端協(xié)議。MycatE-RMycat垂直分片：一個數(shù)據(jù)庫中多個表格A，B，C，A到節(jié)點1上，B到節(jié)點2上，C到節(jié)點3上。1、SchemaMySQLDatabase（數(shù)據(jù)庫）對應(yīng)，一個邏輯庫中定義了所包括的儲的邏輯數(shù)據(jù)節(jié)點DataNode。在此可以指定表的分片規(guī)則。DataSource4、DataSource：定義某個物理庫的地址，用于到DatanodeMycat的及安mycat地址MycatMyCatlinux第二步：解壓縮，得到mycatmycat/bin，啟動命令：./mycatstart停止命令：./mycatstop重啟命令：./mycatrestartmysqlmycatMycat把商品表分片到三個數(shù)據(jù)節(jié)點上mysql1操作系統(tǒng)版本:centos6.5數(shù)據(jù)庫版本:mysql-5.6mycat版本：1.4release數(shù)據(jù)庫名:db1、db3mysql2操作系統(tǒng)版本:centos6.5數(shù)據(jù)庫版本:mysql-5.6mycat版本：1.4release數(shù)據(jù)庫名:db2MyCat1（Schema.xmlSchema.xml作為MyCat中重要的配置文件之一，管理著MyCat的邏輯庫、表、分片規(guī)則、schema用于定義MyCat實例中的邏輯Table定義了MyCat中的邏輯dataNode定義了MyCat中的數(shù)據(jù)節(jié)點，也就是我們通常說所的數(shù)據(jù)分片dataHost在mycat邏輯庫中也是作為最底層的存在，直接定義了具體的數(shù)據(jù)庫實例、讀LINUXMYSQLMysqlMySQLmy.inimysqld]中增加一行l(wèi)ower_case_table_names=Schema.xml<?xml<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="false"<!--autoshardingbyid(long)--<tablename="TB_ITEM"dataNode="dn1,dn2,dn3"rule="auto-sharding-long"/> dataNode="dn1,dn2"/><dataNodename="dn1"dataHost="localhost1"database="db1"<dataNodename="dn2"dataHost="localhost2"database="db2"<dataNodename="dn3"dataHost="localhost1"database="db3"<dataHostname="localhost1"maxCon="1000"minCon="10"balance="0" <heartbeat>select<!--canhavemultiwritehosts--<writeHosthost="hostM1"url="34:3306"user="root"<!--canhavemultireadhosts--<dataHostname="localhost2"maxCon="1000"minCon="10"balance="0" <heartbeat>select<!--canhavemultiwritehosts--<writeHosthost="hostM1"url="66:3306"user="root"<!--canhavemultireadhosts--server.xml幾乎保存了所有mycat需要的系統(tǒng)配置信息最常用的是在此配置用戶名及權(quán)限<user<user<property<property<propertyrule.xmltableRulefunction這兩個。在具體使用過程中可以按照需求添加tableRulefunctionmycatmysqlmycat，----TablestructureforDROPTABLEIFEXISTS`tb_item`;CREATETABLE`tb_item`(`id`bigint(20)NOTNULLCOMMENTid，`title`varchar(100)NOTNULLCOMMENT`sell_point`varchar(500DEFAULTNULLCOMMENT`price`bigint(20)NOTNULLCOMMENT`num`int(10)NOTNULLCOMMENT`barcode`varchar(30DEFAULTN