1.MySQL Server 系統(tǒng)架構(gòu)
? ??邏輯模塊組成：MySQL 可以看成是二層架構(gòu)候学，第一層我們通常叫做SQL Layer，在MySQL 數(shù)據(jù)庫系統(tǒng)處理底層數(shù)據(jù)之前的所有工作都是在這一層完成的，包括權(quán)限判斷杂穷，sql 解析乡小，執(zhí)行計(jì)劃優(yōu)化，query cache 的處理等等脑漫；第二層就是存儲(chǔ)引擎層，我們通常叫做StorageEngine Layer咙崎，也就是底層數(shù)據(jù)存取操作實(shí)現(xiàn)部分优幸，由多種存儲(chǔ)引擎共同組成。
????SQL-Layer組成：
????1叙凡、初始化模塊：對整個(gè)系統(tǒng)做各種各樣的初始化操作劈伴，比如各種buffer，cache 結(jié)構(gòu)的初始化和內(nèi)存空間的申請握爷，各種系統(tǒng)變量的初始化設(shè)定跛璧，各種存儲(chǔ)引擎的初始化設(shè)置
? ??2、核心API：提供一些需要非常高效的底層操作功能的優(yōu)化實(shí)現(xiàn)新啼，包括各種底層數(shù)據(jù)結(jié)構(gòu)的實(shí)現(xiàn)追城，特殊算法的實(shí)現(xiàn)，字符串處理燥撞，數(shù)字處理等座柱，小文件I/O，格式化輸出物舒，以及最重要的內(nèi)存管理部分色洞。
? ? 3、網(wǎng)絡(luò)交互模塊：冠胯，實(shí)現(xiàn)底層網(wǎng)絡(luò)數(shù)據(jù)的接收與發(fā)送
? ??4火诸、Client & Server 交互協(xié)議模塊：實(shí)現(xiàn)了客戶端與MySQL 交互過程中的所有協(xié)議。當(dāng)然這些協(xié)議都是建立在現(xiàn)有的OS 和網(wǎng)絡(luò)協(xié)議之上的荠察，如TCP/IP 以及Unix Socket
? ??5置蜀、用戶模塊：主要包括用戶的登錄連接權(quán)限控制和用戶的授權(quán)管理
? ??6奈搜、訪問控制模塊：控制用戶對數(shù)據(jù)的訪問，將用戶模塊和訪問控制模塊兩者結(jié)合起來盯荤，組成了MySQL 整個(gè)數(shù)據(jù)庫系統(tǒng)的權(quán)限安全管理的功能馋吗。
? ??7、連接管理秋秤、連接線程和線程管理：連接線程的主要工作就是負(fù)責(zé)MySQL Server 與客戶端的通信宏粤，接受客戶端的命令請求，傳遞Server 端的結(jié)果信息等航缀。線程管理模塊則負(fù)責(zé)管理維護(hù)這些連接線程商架。包括線程的創(chuàng)建堰怨，線程的cache 等芥玉。
? ? 8：Query 解析和轉(zhuǎn)發(fā)模塊：主要工作就是將query 語句進(jìn)行語義和語法的分析，然后按照不同的操作類型進(jìn)行分類备图，然后做出針對性的轉(zhuǎn)發(fā)灿巧。
? ??9、Query Cache 模塊：主要功能是將客戶端提交給MySQL 的Select 類query 請求的返回結(jié)果集cache 到內(nèi)存中
? ??10揽涮、Query 優(yōu)化器模塊：優(yōu)化客戶端請求的query
? ??11抠藕、表變更管理模塊：表變更管理模塊主要是負(fù)責(zé)完成一些DML 和DDL 的query，如：update蒋困，delte盾似，insert，create table雪标，alter table 等語句的處理
? ??12零院、表維護(hù)模塊:表的狀態(tài)檢查，錯(cuò)誤修復(fù)村刨，以及優(yōu)化和分析等工作都是表維護(hù)模塊需要做的事情告抄。
? ??13、系統(tǒng)狀態(tài)管理模塊：系統(tǒng)狀態(tài)管理模塊負(fù)責(zé)在客戶端請求系統(tǒng)狀態(tài)的時(shí)候嵌牺，將各種狀態(tài)數(shù)據(jù)返回給用戶
? ??14打洼、表管理器：主要內(nèi)容是各個(gè)表的結(jié)構(gòu)信息。此外它還維護(hù)table 級(jí)別的鎖管
? ??15逆粹、日志記錄模塊日志記錄模塊主要負(fù)責(zé)整個(gè)系統(tǒng)級(jí)別的邏輯層的日志的記錄募疮，包括error log，binarylog僻弹，slow query log 等阿浓。
? ??16、復(fù)制模塊：復(fù)制模塊又可分為Master 模塊和Slave 模塊兩部分奢方， Master 模塊主要負(fù)責(zé)在Replication 環(huán)境中讀取Master 端的binary 日志搔扁，以及與Slave 端的I/O 線程交互等工作爸舒。Slave 模塊比Master 模塊所要做的事情稍多一些，在系統(tǒng)中主要體現(xiàn)在兩個(gè)線程上面稿蹲。一個(gè)是負(fù)責(zé)從Master 請求和接受binary 日志扭勉，并寫入本地relay log 中的I/O 線程。另外一個(gè)是負(fù)責(zé)從relay log 中讀取相關(guān)日志事件苛聘，然后解析成可以在Slave 端正確執(zhí)行并得到和Master 端完全相同的結(jié)果的命令并再交給Slave 執(zhí)行的SQL 線程涂炎。
? ??17、存儲(chǔ)引擎接口模塊：實(shí)現(xiàn)可插拔存儲(chǔ)引擎
2.日志文件
? ??1设哗、錯(cuò)誤日志：Error Log???????
? ??????錯(cuò)誤日志的默認(rèn)存放位置在數(shù)據(jù)目錄下唱捣，以hostname.err 命名
? ??????log-error[=file_name]，修改其存放目錄和文件名网梢。
?????2震缭、二進(jìn)制日志：Binary Log & Binary Log Index
? ??????log-bin[=on|off]：打開/關(guān)閉記錄功能
? ??????max_binlog_size：設(shè)置binlog 的最大存儲(chǔ)上限，當(dāng)日志達(dá)到該上限時(shí)战虏，MySQL 會(huì)重新創(chuàng)建一個(gè)日志開始繼續(xù)記錄?
????3拣宰、查詢?nèi)罩荆簈uery log
? ??????????general_log[=on|off]：打開/關(guān)閉查詢?nèi)罩?/p>
????????????general_log_file[=filename]： 查詢?nèi)罩疚募?/p>
? ? 4、慢查詢?nèi)罩荆簊low query log
? ??????????slow_query_log[=on|off]：打開/關(guān)閉慢查詢?nèi)罩?/p>
? ??????????long_query_time[=long]：超過多少秒的查詢就寫入日志
? ? 5烦感、Innodb 的在線redo 日志：innodb redo log
? ??????????Innodb 是一個(gè)事務(wù)安全的存儲(chǔ)引擎巡社，其事務(wù)安全性主要就是通過在線redo 日志和記錄在表空間中的undo 信息來保證的。redo 日志中記錄了Innodb 所做的所有物理變更和事務(wù)信息手趣，可以通過innodb_log_group_home_dir 來更改設(shè)置日志的存放位置晌该，通過innodb_log_files_in_group 設(shè)置日志的數(shù)量。
3.數(shù)據(jù)文件
? ??1绿渣、“.MYD”文件：MyISAM 存儲(chǔ)引擎專用朝群，存放MyISAM 表的數(shù)據(jù)。每一MyISAM 表都會(huì)有一個(gè)“.MYD”文件與之對應(yīng)怯晕。
? ? 2潜圃、“.MYI”文件：專屬于MyISAM 存儲(chǔ)引擎，主要存放MyISAM 表的索引相關(guān)信息
? ? 3舟茶、“.ibd”文件和ibdata 文件：獨(dú)享表空間存儲(chǔ)方式使用“.ibd”文件來存放數(shù)據(jù)谭期，且每個(gè)表一個(gè)“.ibd”文件，文件存放在和MyISAM 數(shù)據(jù)相同的位置吧凉。如果選用共享存儲(chǔ)表空間來存放數(shù)據(jù)隧出，則會(huì)使用ibdata 文件來存放，所有表共同使用一個(gè)（或者多個(gè)阀捅，可自行配置）ibdata 文件胀瞪。ibdata 文件可以通過innodb_data_home_dir 和innodb_data_file_path兩個(gè)參數(shù)共同配置組成
? ??????innodb_data_file_path默認(rèn)值是ibdata1:12M:autoextend镜廉，ibdata共享表文件暑塑，12M設(shè)置表空間大小，autoextend自動(dòng)擴(kuò)展。
4.Replication相關(guān)文件
? ??1伶授、master.info 文件：master.info 文件存在于Slave 端的數(shù)據(jù)目錄下免姿，里面存放了該Slave 的Master 端的相關(guān)信息岖是，包括Master 的主機(jī)地址行您，連接用戶，連接密碼汛蝙，連接端口烈涮，當(dāng)前日志位置，已經(jīng)讀取到的日志位置等信息窖剑。
? ??2坚洽、relay log 和relay log index：mysql-relay-bin.xxxxxn 文件用于存放Slave 端的I/O 線程從Master 端所讀取到的Binary Log 信息，然后由Slave 端的SQL 線程從該relay log 中讀取并解析相應(yīng)的日志信息西土，轉(zhuǎn)化成Master 所執(zhí)行的SQL 語句讶舰，然后在Slave 端應(yīng)用。
? ?3翠储、 relay-log.info 文件：類似于master.info绘雁，它存放通過Slave 的I/O 線程寫入到本地的relay log 的相關(guān)信息
5.其他文件
? ??1、system config file：MySQL 的系統(tǒng)配置文件一般都是“my.cnf”援所，Unix/Linux 下默認(rèn)存放在"/etc"目錄下，Windows 環(huán)境一般存放在“c:/windows”目錄下面欣除。
? ??2住拭、pid file：mysqld 應(yīng)用程序在Unix/Linux 環(huán)境下的一個(gè)進(jìn)程文件，和許多其他Unix/Linux 服務(wù)端程序一樣历帚，存放著自己的進(jìn)程id滔岳。
? ? 3、socket file：用戶在Unix/Linux 環(huán)境下客戶端連接可以不通過TCP/IP 網(wǎng)絡(luò)而直接使用Unix Socket 來連接MySQL挽牢。
6.mysql自帶工具
????mysqladmin：與MySQL 管理相關(guān)的各種功能
? ??????Usage: mysqladmin [OPTIONS] command command ...
? ??????mysqladmin -uroot -p123 -hlocalhost ping????//檢測MySQL Server 是否還能正常提供服務(wù)
? ? ? ? >mysqld is alive
? ??????mysqladmin -uroot -p123 -hlocalhost status? ?// 獲取當(dāng)前MySQL Server 的幾個(gè)基本的狀態(tài)值
? ? ? ? >Uptime: 20960 Threads: 1 Questions: 75 Slow queries: 0 Opens: 15 Flush
? ? ? ? >tables: 1 Open tables: 9 Queries per second avg: 0.3
? ??????mysqladmin -uroot -p123 -hlocalhost processlist? ? //獲取當(dāng)前數(shù)據(jù)庫的連接線程信息
? ??????此外谱煤，還可以通過mysqladmin 來start slave 和stop slave，kill 某個(gè)連接到MySQL Server 的線程等等禽拔。
? ??mysqldump? ?:將MySQL Server中的數(shù)據(jù)以SQL 語句的形式從數(shù)據(jù)庫中dump 成文本文件刘离。這
個(gè)功能實(shí)際上是調(diào)用了MySQL 中的“select * into OUTFILE from ...”語句而實(shí)現(xiàn)
? ?????Usage: mysqldump [OPTIONS] database [tables].
????????OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
????????OR mysqldump [OPTIONS] --all-databases [OPTIONS]
? ??????可以通過“-d,--no-data”僅僅生成結(jié)構(gòu)創(chuàng)建的語句,每次執(zhí)行mysqldump 程序的時(shí)候都通過盡量做到“--defaultcharacter-set=name”顯式指定字符集內(nèi)容
? ??mysqlimport ：mysqlimport 程序是一個(gè)將以特定格式存放的文本數(shù)據(jù)（如通過“select * into OUTFILE from ...”所生成的數(shù)據(jù)文件）導(dǎo)入到指定的MySQL Server 中的工具程序
? ??Usage: mysqlimport [OPTIONS] database textfile ...
? ??mysqlbinlog：通過mysqlbinlog，我們可以解析出binlog 中指定時(shí)間段或者指定日志起始和結(jié)束位置的內(nèi)容解析成SQL 語句睹栖，并導(dǎo)出到指定的文件中硫惕，在解析過程中，還可以通過指定數(shù)據(jù)庫名稱來過濾輸出內(nèi)容野来。
? ??????Usage: mysqlbinlog [OPTIONS] log-files
? ? ? ??
????mysqlcheck
? ??????Usage: mysqlcheck [OPTIONS] database [tables]
????????OR mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
????????OR mysqlcheck [OPTIONS] --all-databases
? ??????mysqlcheck 工具程序可以檢查（-c）恼除，修復(fù)（-r），分析（ -a）和優(yōu)化????（-o）MySQL Server 中的表
????myisamchk
? ??????Usage: myisamchk [OPTIONS] tables[.MYI]
? ??????功能有點(diǎn)類似“mysqlcheck -c/-r”曼氛，對檢查和修復(fù)MyISAM 存儲(chǔ)引擎的表豁辉，但只能對MyISAM 存儲(chǔ)引擎的索引文件有效
????myisampack
? ??Usage: myisampack [OPTIONS] filename ...
? ??對MyISAM 表進(jìn)行壓縮處理令野，以縮減占用存儲(chǔ)空間，一般主要用在歸檔備份的場景下徽级，而且壓縮后的MyISAM 表會(huì)變成只讀彩掐，不能進(jìn)行任何修改操作。
? ??mysqlhotcopy
? ??Usage: mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]
? ??僅能在Unix/Linux 環(huán)境下使用灰追。他的主要功能就是對MySQL 中的MyISAM 存儲(chǔ)引擎的表進(jìn)行在線備份操作堵幽，其備份操作實(shí)際上就是通過對數(shù)據(jù)庫中的表進(jìn)行加鎖，然后復(fù)制其結(jié)構(gòu)弹澎，數(shù)據(jù)和索引文件來完成備份操作
7.數(shù)據(jù)引擎
? ??MySQL 的插件式存儲(chǔ)引擎主要包括MyISAM朴下，Innodb，NDB Cluster苦蒿，Maria殴胧，F(xiàn)alcon，Memory佩迟，Archive团滥，Merge，F(xiàn)ederated 等
? ??MyISAM 存儲(chǔ)引擎
? ? 特點(diǎn)：1报强、表的行結(jié)構(gòu)緊湊灸姊，浪費(fèi)空間少，因此比較適合數(shù)據(jù)倉庫系統(tǒng)
????????????????2秉溉、因?yàn)闆]有redo力惯、沒有undo，因此進(jìn)行insert的時(shí)候召嘶，速度相對較快
? ??MyISAM 支持以下三種類型的索引：1父晶、B-Tree 索引 2、R-Tree 索引?3弄跌、Full-text 索引
? ??
????Innodb 存儲(chǔ)引擎
? ? 特點(diǎn)：1甲喝、支持事務(wù)安裝????2、數(shù)據(jù)多版本讀取 3铛只、使用行級(jí)鎖????4埠胖、實(shí)現(xiàn)外鍵
? ??Innodb 的物理結(jié)構(gòu)分為兩大部分：
? ??1、數(shù)據(jù)文件（表數(shù)據(jù)和索引數(shù)據(jù)）
? ??????在Innodb 中格仲，存在了表空間（tablespace）這樣一個(gè)概念押袍，Innodb 的表空間分為兩種形式。一種是共享表空間凯肋，也就是所有表和索引數(shù)據(jù)被存放在同一個(gè)表空間（一個(gè)或多個(gè)數(shù)據(jù)文件idata）中谊惭，通過innodb_data_file_path 來指定，增加數(shù)據(jù)文件需要停機(jī)重啟,Innodb 的undo 信息和其他一些元數(shù)據(jù)信息都是存放在共享表空間里面的。另外一種是獨(dú)享表空間圈盔，也就是每個(gè)表的數(shù)據(jù)和索引被存放在一個(gè)單獨(dú)的.ibd 文件中豹芯。
? ??????共享表空間增加數(shù)據(jù)文件的操作比較簡單， 只需要在innodb_data_file_path 參數(shù)后面按照標(biāo)準(zhǔn)格式設(shè)置好文件路徑和相關(guān)屬性即可驱敲，不過這里有一點(diǎn)需要注意的铁蹈，就是Innodb 在創(chuàng)建新數(shù)據(jù)文件的時(shí)候是不會(huì)創(chuàng)建目錄的，如果指定目錄不存在众眨，則會(huì)報(bào)錯(cuò)并無法啟動(dòng)
? ??2握牧、日志文件
? ??????由于Innodb 是事務(wù)安全的存儲(chǔ)引擎，所以系統(tǒng)Crash 對他來說并不能造成非常嚴(yán)重的損失娩梨，由于有redo 日志的存在沿腰，有checkpoint 機(jī)制的保護(hù)，Innodb 完全可以通過redo 日志將數(shù)據(jù)庫Crash 時(shí)刻已經(jīng)完成但還沒有來得及將數(shù)據(jù)寫入磁盤的事務(wù)恢復(fù)狈定，也能夠?qū)⑺胁糠滞瓿刹⒁呀?jīng)寫入磁盤的未完成事務(wù)回滾并將數(shù)據(jù)還原颂龙。
? ??????Innodb 的所有參數(shù)基本上都帶有前綴“innodb_”。
????NDB Cluster存儲(chǔ)引擎
? ??????NDB 存儲(chǔ)引擎也叫NDB Cluster 存儲(chǔ)引擎纽什，主要用于MySQL Cluster 分布式集群環(huán)境措嵌，Cluster 是MySQL 從5.0 版本才開始提供的新功能。
? ??????一般來說芦缰，一個(gè)Mysql Cluster 的環(huán)境主要由以下三部分組成：
? ??????a) 負(fù)責(zé)管理各個(gè)節(jié)點(diǎn)的Manage 節(jié)點(diǎn)主機(jī)：于管理節(jié)點(diǎn)上保存在整個(gè)Cluster 環(huán)境的配置企巢，同時(shí)擔(dān)任了集群中各節(jié)點(diǎn)的基本溝通工作，所以他必須是最先被啟動(dòng)的節(jié)點(diǎn)饺藤。
? ??????b) SQL 層的SQL 服務(wù)器節(jié)點(diǎn)（后面簡稱為SQL 節(jié)點(diǎn)）包斑，也就是我們常說的Mysql Server：主要負(fù)責(zé)實(shí)現(xiàn)一個(gè)數(shù)據(jù)庫在存儲(chǔ)層之上的所有事情，比如連接管理涕俗，query 優(yōu)化和響應(yīng)，cache 管理等等神帅，只有存儲(chǔ)層的工作交給了NDB 數(shù)據(jù)節(jié)點(diǎn)去處理了再姑。SQL 層各Mysql 服務(wù)器的啟動(dòng)與普通的Mysql 啟動(dòng)有一定的區(qū)別，必須要添加ndbcluster 項(xiàng)找御，可以添加在my.cnf 配置文件中元镀，也可以通過啟動(dòng)命令行來指定。
? ??????c) Storage 層的NDB 數(shù)據(jù)節(jié)點(diǎn)霎桅，也就是上面說的NDB Cluster：NDB 節(jié)點(diǎn)主要是實(shí)現(xiàn)底層數(shù)據(jù)存儲(chǔ)的功能栖疑，保存Cluster 的數(shù)據(jù)。每一個(gè)NDB 節(jié)點(diǎn)保存完整數(shù)據(jù)的一部分滔驶。
????其他存儲(chǔ)引擎
? ??????Merge存儲(chǔ)引擎：MERGE 存儲(chǔ)引擎可以簡單的理解為其功能就是實(shí)現(xiàn)了對結(jié)構(gòu)相同的MyISAM 表遇革，通過一些特殊的包裝對外提供一個(gè)單一的訪問入口，以達(dá)到減小應(yīng)用的復(fù)雜度的目的
? ??????Memory存儲(chǔ)引擎：將數(shù)據(jù)存儲(chǔ)在內(nèi)存中的存儲(chǔ)引擎。MySQL Crash 或者主機(jī)Crash 之后萝快，Memory 的表就只剩下一個(gè)結(jié)構(gòu)了锻霎。Memory 表支持索引，并且同時(shí)支持Hash 和B－Tree 兩種格式的索引揪漩。
? ? ? ? 另外還有BDB 存儲(chǔ)引擎旋恼、FEDERATED存儲(chǔ)引擎、ARCHIVE存儲(chǔ)引擎奄容、BLACKHOLE存儲(chǔ)引擎冰更、CSV存儲(chǔ)引擎，不一一列舉昂勒。
8.權(quán)限系統(tǒng)
? ??MySQL 的權(quán)限系統(tǒng)在實(shí)現(xiàn)上比較簡單蜀细，相關(guān)權(quán)限信息主要存儲(chǔ)在幾個(gè)被稱為granttables 的系統(tǒng)表中，即： mysql.User叁怪，mysql.db审葬，mysql.Host，mysql.table_priv 和mysql.column_priv奕谭。
? ?mysql 在啟動(dòng)的時(shí)候涣觉，就會(huì)將所有的權(quán)限信息都Load 到內(nèi)存中保存在幾個(gè)特定的結(jié)構(gòu)中,所以才有我們每次手工修改了權(quán)限相關(guān)的表之后，都需要執(zhí)行“FLUSH PRIVILEGES”命令重新加載MySQL的權(quán)限信息血柳。當(dāng)然官册，如果我們通過GRANT，REVOKE 或者DROP USER 命令來修改相關(guān)權(quán)限难捌，則不需要手工執(zhí)行FLUSH PRIVILEGES 命令膝宁，因?yàn)橥ㄟ^GRANT，REVOKE 或者DROP USER 命令所做的權(quán)限修改在修改系統(tǒng)表的同時(shí)也會(huì)更新內(nèi)存結(jié)構(gòu)中的權(quán)限信息根吁。
? ??權(quán)限授予與去除：要為某個(gè)用戶授權(quán)员淫，可以使用GRANT 命令，要去除某個(gè)用戶已有的權(quán)限則使用REVOKE命令击敌。當(dāng)給某個(gè)用戶授權(quán)的時(shí)候介返，不僅需要指定用戶名，同時(shí)還要指定來訪主機(jī)沃斤。
? ??查看某個(gè)用戶目前擁有的權(quán)限可以通過“SHOW GRANTS FOR 'username'@'hostname'”和查詢mysql.user里面的權(quán)限信息圣蝎。
? ???創(chuàng)建用戶：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
? ???授權(quán)：GRANT privileges ON databasename.tablename TO 'username'@'host'
????????????用戶的操作權(quán)限，如SELECT衡瓶，INSERT徘公，UPDATE等，如果要授予所的權(quán)限則使用ALL哮针。 如果想讓該用戶可以授權(quán)关面，用以下命令:
????????????????GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
? ??設(shè)置與更改用戶密碼:SET PASSWORD FOR 'username'@'host' =PASSWORD('newpassword');
? ??撤銷用戶權(quán)限：:REVOKE privilege ON databasename.tablename FROM 'username'@'host';
? ??刪除用戶：DROP USER 'username'@'host';
9.數(shù)據(jù)庫備份
? ??mysqldump邏輯備份
????”--single-transaction”選項(xiàng)坦袍，可以達(dá)到備份數(shù)據(jù)的一致性和完整性
? ??“--lock-tables”和“--lock-all-tables”，讓數(shù)據(jù)庫在備份過程中僅提供數(shù)據(jù)的查詢服務(wù)缭裆，鎖定寫入的服務(wù)键闺，來使數(shù)據(jù)暫時(shí)處于一個(gè)一致的不會(huì)被修改的狀態(tài)
? ??“--master-data[=value]”會(huì)將當(dāng)前MySQL 使用到binlog 日志的名稱和位置記錄到dump 文件中。這個(gè)選項(xiàng)在實(shí)施slave 的在線搭建的時(shí)候是非常有用的澈驼，即使不是進(jìn)行在線搭建slave辛燥，也可以在某些情況下做恢復(fù)的過程中通過備份的binlog 做進(jìn)一步恢復(fù)操作。
? ? "--no-data”僅僅dump 數(shù)據(jù)庫結(jié)構(gòu)創(chuàng)建腳本?
? ? mysqldump邏輯恢復(fù)?
? ??可以通過在mysql 中執(zhí)行“source /path/backup.sql”或者“\. /path/backup.sql”來進(jìn)行恢復(fù)缝其】嫠或在命令行使用mysql -uusername -p < backup.sql
? ??數(shù)據(jù)庫物理課備份
? ? ?數(shù)據(jù)庫的物理備份就是對數(shù)據(jù)庫的物理對象所做的備份。
? ? MyISAM物理備份需要備份的內(nèi)容有：.frm”文件内边，存儲(chǔ)表數(shù)據(jù)的“.MYD”文件榴都，以及存儲(chǔ)索引數(shù)據(jù)的“.MYI”文件
? ??Innodb 存儲(chǔ)引擎“innodb_data_home_dir”和“innodb_data_file_path”參數(shù)所設(shè)定的所有數(shù)據(jù)文件，“datadir”中相應(yīng)數(shù)據(jù)庫目錄下的所有Innodb 存儲(chǔ)引擎表的“.frm”文件“.idb”文件漠其，redo文件
? ??NDB Cluster 存儲(chǔ)引擎1嘴高、元數(shù)據(jù)（Metadata）2、表數(shù)據(jù)（Table Records）3和屎、事務(wù)日志數(shù)據(jù)（Transaction Log）：
? ??我們也可以通過登錄數(shù)據(jù)庫中手工加鎖拴驮，然后再通過操作系統(tǒng)的命令來復(fù)制相關(guān)文件執(zhí)行熱物理備份，且在完成文件copy 之前柴信，不能退出加鎖的session（因?yàn)橥顺鰰?huì)自動(dòng)解鎖）套啤，執(zhí)行FLUSH TABLES WITH READ LOCK;
10.MyISAM 鎖優(yōu)化
? ??MyISAM 表鎖優(yōu)化建議優(yōu)化MyISAM 存儲(chǔ)引擎鎖定問題的時(shí)候，最關(guān)鍵的就是如何讓其提高并發(fā)度随常。由于鎖定級(jí)別是不可能改變的了潜沦，所以我們首先需要盡可能讓鎖定的時(shí)間變短，然后就是讓可能并發(fā)進(jìn)行的操作盡可能的并發(fā)绪氛。
? ??1唆鸡、縮短鎖定時(shí)間
? ??????a) 盡兩減少大的復(fù)雜Query，將復(fù)雜Query 分拆成幾個(gè)小的Query 分布進(jìn)行枣察；
? ??????b) 盡可能的建立足夠高效的索引喇闸，讓數(shù)據(jù)檢索更迅速；
? ??????c) 盡量讓MyISAM 存儲(chǔ)引擎的表只存放必要的信息询件，控制字段類型；
? ??????d) 利用合適的機(jī)會(huì)優(yōu)化MyISAM 表數(shù)據(jù)文件唆樊；
????2宛琅、分離能并行的操作
? ? ? ? 配置是Concurrent Insert（并發(fā)插入），MyISAM 存儲(chǔ)引擎有一個(gè)控制是否打開Concurrent Insert 功能的參數(shù)選項(xiàng)：concurrent_insert逗旁，可以設(shè)置為0嘿辟，1 或者2舆瘪。三個(gè)值的具體說明如下：
? ??????a) set global concurrent_insert=2（always）2，無論MyISAM 存儲(chǔ)引擎的表數(shù)據(jù)文件的中間部分是否存在因?yàn)閯h除數(shù)據(jù)而留下的空閑空間红伦，都允許在數(shù)據(jù)文件尾部進(jìn)行Concurrent Insert;
? ??????b)?set global? concurrent_insert=1(auto)當(dāng)MyISAM 存儲(chǔ)引擎表數(shù)據(jù)文件中間不存在空閑空間的時(shí)候英古，可以從文件尾部進(jìn)行Concurrent Insert;
? ??????c) set global??concurrent_insert=0(never)，無論MyISAM 存儲(chǔ)引擎的表數(shù)據(jù)文件的中間部分是否存在因?yàn)閯h除數(shù)據(jù)而留下的空閑空間昙读，都不允許Concurrent Insert召调。
? ? 3、合理利用讀寫優(yōu)先級(jí)
? ??????如果我們的系統(tǒng)是一個(gè)以讀為主蛮浑，而且要優(yōu)先保證查詢性能的話唠叛，我們可以通過設(shè)置系統(tǒng)參數(shù)選項(xiàng)low_priority_updates=1，如果我們的系統(tǒng)需要有限保證數(shù)據(jù)寫入的性能的話沮稚，則可以不用設(shè)置low_priority_updates參數(shù)
11.innodb鎖優(yōu)化
? ??Innodb 存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定艺沼，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來的性能損耗可能比表級(jí)鎖定會(huì)要更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM 的表級(jí)鎖定的
? ??a) 盡可能讓所有的數(shù)據(jù)檢索都通過索引來完成蕴掏，從而避免Innodb 因?yàn)闊o法通過索引鍵加鎖而升級(jí)為表級(jí)鎖定障般；
? ??b) 合理設(shè)計(jì)索引，讓Innodb 在索引鍵上面加鎖的時(shí)候盡可能準(zhǔn)確盛杰，盡可能的縮小鎖定范圍挽荡，避免造成不必要的鎖定而影響其他Query 的執(zhí)行；
? ??c) 盡可能減少基于范圍的數(shù)據(jù)檢索過濾條件饶唤，避免因?yàn)殚g隙鎖帶來的負(fù)面影響而鎖定了不該鎖定的記錄徐伐；
? ??d) 盡量控制事務(wù)的大小，減少鎖定的資源量和鎖定時(shí)間長度募狂；
? ??e) 在業(yè)務(wù)環(huán)境允許的情況下办素，盡量使用較低級(jí)別的事務(wù)隔離，以減少M(fèi)ySQL 因?yàn)閷?shí)現(xiàn)事務(wù)隔離級(jí)別所帶來的附加成本祸穷；
? ??減少死鎖產(chǎn)生概率建議：
? ??a) 類似業(yè)務(wù)模塊中性穿，盡可能按照相同的訪問順序來訪問，防止產(chǎn)生死鎖雷滚；
? ??b) 在同一個(gè)事務(wù)中需曾，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率祈远；
? ??c) 對于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分呆万，可以嘗試使用升級(jí)鎖定顆粒度，通過表級(jí)鎖定來減少死鎖產(chǎn)生的概率车份；
? ??系統(tǒng)鎖定爭用情況查詢：show status like 'table%';
? ??Table_locks_immediate：產(chǎn)生表級(jí)鎖定的次數(shù)谋减；
? ??Table_locks_waited：出現(xiàn)表級(jí)鎖定爭用而發(fā)生等待的次數(shù)，如果Table_locks_waited 狀態(tài)值比較高扫沼，那么說明系統(tǒng)中表級(jí)鎖定爭用現(xiàn)象比較嚴(yán)重
? ? innodb鎖定爭用情況查詢：show status like 'innodb_row_lock%';
? ??Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量出爹；
????Innodb_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長度庄吼；
????Innodb_row_lock_time_avg：每次等待所花平均時(shí)間；
????Innodb_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最常的一次所花的時(shí)間严就；
????Innodb_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)总寻；
12.Query 的優(yōu)化?
? ??Query 語句優(yōu)化基本思路和原則
? ??1. 優(yōu)化更需要優(yōu)化的Query；
? ??????一般來說梢为，高并發(fā)低消耗（相對）的Query 對整個(gè)系統(tǒng)的影響遠(yuǎn)比低并發(fā)高消耗的Query 大(io次數(shù)高cpu消耗大)渐行。
????2. 定位優(yōu)化對象的性能瓶頸；
? ??????PROFILING 功能很清楚的找出一個(gè)Query 的瓶頸所在抖誉。
? ??????1殊轴、 開啟 profiling 參數(shù)set profiling=1;????????? 2、 執(zhí)行 Query
? ? ? ? 3袒炉、show profiles;獲取系統(tǒng)中保存的所有 Query 的 profile 概要信息
? ? ? ? 4旁理、 針對單個(gè) Query 獲取詳細(xì)的 profile 信息show PROFILE cpu ,block io for query 116
????3. 從Explain 入手；
? ? ? ?Explain 功能中給我們展示的各種信息的解釋：
? ??????ID：Query Optimizer 所選定的執(zhí)行計(jì)劃中查詢的序列號(hào)我磁；
? ??????Select_type：所使用的查詢類型孽文，主要有以下這幾種查詢類型
????????????◇ DEPENDENT SUBQUERY：子查詢中內(nèi)層的第一個(gè)SELECT，依賴于外部查詢的結(jié)果集夺艰；◇ DEPENDENT UNION：子查詢中的UNION芋哭，且為UNION 中從第二個(gè)SELECT 開始的后面所有SELECT，同樣依賴于外部查詢的結(jié)果集郁副；◇ PRIMARY：子查詢中的最外層查詢减牺，注意并不是主鍵查詢；◇ SIMPLE：除子查詢或者UNION 之外的其他查詢存谎；◇ SUBQUERY：子查詢內(nèi)層查詢的第一個(gè)SELECT拔疚，結(jié)果不依賴于外部查詢結(jié)果集；◇ UNCACHEABLE SUBQUERY：結(jié)果集無法緩存的子查詢既荚；◇ UNION：UNION 語句中第二個(gè)SELECT 開始的后面所有SELECT稚失，第一個(gè)SELECT 為PRIMARY◇ UNION RESULT：UNION 中的合并結(jié)果；
? ??????Table：顯示這一步所訪問的數(shù)據(jù)庫中的表的名稱恰聘；
? ??????Type：告訴我們對all：全表掃描表所使用的訪問方式
????????????◇ const：讀常量句各，且最多只會(huì)有一條記錄匹配，由于是常量晴叨，所以實(shí)際上只需要讀一次凿宾；◇ eq_ref：最多只會(huì)有一條匹配結(jié)果，一般是通過主鍵或者唯一鍵索引來訪問兼蕊；◇ fulltext：◇ index：全索引掃描菌湃；◇ index_merge：查詢中同時(shí)使用兩個(gè)（或更多）索引，然后對索引結(jié)果進(jìn)行merge 之后再讀取表數(shù)據(jù)遍略；◇ index_subquery：子查詢中的返回結(jié)果字段組合是一個(gè)索引（或索引組合）惧所，但不是一個(gè)主鍵或者唯一索引；◇ rang：索引范圍掃描绪杏；◇ ref：Join 語句中被驅(qū)動(dòng)表索引引用查詢下愈；◇ ref_or_null：與ref 的唯一區(qū)別就是在使用索引引用查詢之外再增加一個(gè)空值的查詢；◇ system：系統(tǒng)表蕾久，表中只有一行數(shù)據(jù)势似；◇ unique_subquery：子查詢中的返回結(jié)果字段組合是主鍵或者唯一約束
????? ??Possible_keys：該查詢可以利用的索引. 如果沒有任何索引可以使用，就會(huì)顯示成null
? ??????Key：MySQL Query Optimizer 從possible_keys 中所選擇使用的索引僧著；
? ??????Key_len：被選中使用索引的索引鍵長度履因；
? ??????Ref：列出是通過常量（const），還是某個(gè)表的某個(gè)字段（如果是join）來過濾（通過key）的盹愚；
? ? ? ? Rows：MySQL Query Optimizer 通過系統(tǒng)收集到的統(tǒng)計(jì)信息估算出來的結(jié)果集記錄條數(shù)栅迄；
? ??????Extra：查詢中每一步實(shí)現(xiàn)的額外細(xì)節(jié)信息? ? ? ? ? ?
?????????????◇ Distinct：查找distinct 值，所以當(dāng)mysql 找到了第一條匹配的結(jié)果后皆怕，將停止該值的查詢而轉(zhuǎn)為后面其他值的查詢毅舆；◇ Full scan on NULL key：子查詢中的一種優(yōu)化方式，主要在遇到無法通過索引訪問null值的使用使用愈腾；◇ Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通過收集到的統(tǒng)計(jì)信息判斷出不可能存在結(jié)果憋活；◇ No tables：Query 語句中使用FROM DUAL 或者不包含任何FROM 子句；◇ Not exists：在某些左連接中MySQL Query Optimizer 所通過改變原有Query 的組成而使用的優(yōu)化方法虱黄，可以部分減少數(shù)據(jù)訪問次數(shù)悦即；◇ Range checked for each record (index map: N)：通過MySQL 官方手冊的描述，當(dāng)MySQL Query Optimizer 沒有發(fā)現(xiàn)好的可以使用的索引的時(shí)候橱乱，如果發(fā)現(xiàn)如果來自前面的表的列值已知辜梳，可能部分索引可以使用。對前面的表的每個(gè)行組合仅醇，MySQL 檢查是否可以使用range 或index_merge 訪問方法來索取行冗美。◇ Select tables optimized away：當(dāng)我們使用某些聚合函數(shù)來訪問存在索引的某個(gè)字段的時(shí)候析二，MySQL Query Optimizer 會(huì)通過索引而直接一次定位到所需的數(shù)據(jù)行完成整個(gè)查詢粉洼。當(dāng)然，前提是在Query 中不能有GROUP BY 操作叶摄。如使用MIN()或者M(jìn)AX（）的時(shí)候属韧；◇ Using filesort：當(dāng)我們的Query 中包含ORDER BY 操作，而且無法利用索引完成排序操作的時(shí)候蛤吓，MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來實(shí)現(xiàn)宵喂。◇ Using index：所需要的數(shù)據(jù)只需要在Index 即可全部獲得而不需要再到表中取數(shù)據(jù)会傲；◇ Using index for group-by：數(shù)據(jù)訪問和Using index 一樣锅棕，所需數(shù)據(jù)只需要讀取索引即可拙泽，而當(dāng)Query 中使用了GROUP BY 或者DISTINCT 子句的時(shí)候，如果分組字段也在索引中裸燎，Extra 中的信息就會(huì)是Using index for group-by顾瞻；◇ Using temporary：當(dāng)MySQL 在某些操作中必須使用臨時(shí)表的時(shí)候，在Extra 信息中就會(huì)出現(xiàn)Using temporary 德绿。主要常見于GROUP BY 和ORDER BY 等操作中荷荤。◇ Using where：如果我們不是讀取表的所有數(shù)據(jù)移稳，或者不是僅僅通過索引就可以獲取所有需要的數(shù)據(jù)蕴纳，則會(huì)出現(xiàn)Using where 信息；◇ Using where with pushed condition：這是一個(gè)僅僅在NDBCluster 存儲(chǔ)引擎中才會(huì)出現(xiàn)的信息个粱，而且還需要通過打開Condition Pushdown 優(yōu)化功能才可能會(huì)被使用古毛。控制參數(shù)為engine_condition_pushdown 几蜻。
? ? ? ?a) 永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大的結(jié)果集喇潘。因?yàn)樵贛ySQL 中的Join，只有Nested Loop 一種Join 方式梭稚，也就是MySQL 的Join 都是通過嵌套循環(huán)來實(shí)現(xiàn)的颖低。
? ? ? b）只取出自己需要的Columns。對于任何Query弧烤，返回的數(shù)據(jù)都是需要通過網(wǎng)絡(luò)數(shù)據(jù)包傳回給客戶端忱屑，如果取出的Column 越多，需要傳輸?shù)臄?shù)據(jù)量自然會(huì)越大暇昂。如果是需要排序的Query 來說莺戒，影響就更大了。在MySQL 中存在兩種排序算法急波，一種是在MySQL4.1 之前的老算法从铲，實(shí)現(xiàn)方式是先將需要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出，然后在我們所設(shè)定的排序區(qū)（通過參數(shù)sort_buffer_size 設(shè)定）中進(jìn)行排序澄暮，完成排序之后再次通過行指針信息取出所需要的Columns名段，也就是說這種算法需要訪問兩次數(shù)據(jù)。第二種排序算法是從MySQL4.1 版本開始使用的改進(jìn)算法泣懊，一次性將所需要的Columns 全部取出伸辟，在排序區(qū)中進(jìn)行排序后直接將數(shù)據(jù)返回給請求客戶端。改行算法只需要訪問一次數(shù)據(jù)馍刮，減少了大量的隨機(jī)IO信夫，極大的提高了帶有排序的Query 語句的效率。但是，這種改進(jìn)后的排序算法需要一次性取出并緩存的數(shù)據(jù)比第一種算法要多很多静稻，如果我們將并不需要的Columns 也取出來警没，就會(huì)極大的浪費(fèi)排序過程所需要的內(nèi)存。在MySQL4.1 之后的版本中姊扔，我們可以通過設(shè)置max_length_for_sort_data 參數(shù)大小來控制MySQL 選擇第一種排序算法還是第二種排序算法惠奸。當(dāng)所取出的Columns 的單條記錄總大小max_length_for_sort_data 設(shè)置的大小的時(shí)候，MySQL 就會(huì)選擇使用第一種排序算法，反之，則會(huì)選擇第二種優(yōu)化后的算法绷雏。為了盡可能提高排序性能魏烫，我們自然是更希望使用第二種排序算法，所以在Query 中僅僅取出我們所需要的Columns 是非常有必要的及穗。
? ? c)僅僅使用最有效的過濾條件摧茴。因?yàn)椴煌乃饕I長度，如果多個(gè)索引鍵都能與表一一對應(yīng)埂陆，那么應(yīng)該只使用鍵長度較短的索引作為過濾條件苛白。
? ? d)盡可能避免復(fù)雜的Join 和子查詢。Query 語句所涉及到的越復(fù)雜的Join 語句焚虱，所需要鎖定的資源也就越多购裙，所表越多，所需要鎖定的資源就越多鹃栽。也就是說躏率，阻塞的其他線程也就越多。如果我們將比較復(fù)雜的Query 語句分拆成多個(gè)較為簡單的Query 語句分步執(zhí)行民鼓，每次鎖定的資源也就會(huì)少很多薇芝，所阻塞的其他線程也要少一些。（犧牲響應(yīng)時(shí)間提高整體處理能力）
? ? 4.合理利用索引
? ??????如何判定是否需要?jiǎng)?chuàng)建索引?
? ??????????◆ 較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引丰嘉；
? ??????????◆ 唯一性太差的字段不適合單獨(dú)創(chuàng)建索引夯到，即使頻繁作為查詢條件；
? ??????????◆ 更新非常頻繁的字段不適合創(chuàng)建索引饮亏；
? ??????????◆ 不會(huì)出現(xiàn)在WHERE 子句中的字段不該創(chuàng)建索引耍贾；
? ? ????使用組合索引
? ? ? ? ? ?只要不是其中如果某個(gè)過濾字段在大多數(shù)場景下都能過濾出90%以上的數(shù)據(jù)，而且其他的過濾字段會(huì)存在頻繁的更新克滴，都建議創(chuàng)建組合索引逼争。因?yàn)楫?dāng)我們的并發(fā)量較高的時(shí)候，即使我們?yōu)槊總€(gè)Query 節(jié)省很少的IO 消耗劝赔，但因?yàn)閳?zhí)行量非常大誓焦，所節(jié)省的資源總量仍然是非常可觀的。
? ?????強(qiáng)制使用索引：
? ? ? ? 在有些情況下杂伟，可能是由于我們的系統(tǒng)統(tǒng)計(jì)信息的不夠準(zhǔn)確完整移层，也可能是MySQL Query Optimizer 自身功能的缺陷，會(huì)造成他并沒有選擇一個(gè)真正最優(yōu)的索引而選擇了其他查詢效率較低的索引赫粥。這時(shí)可以使用 FORCE INDEX(index_name)強(qiáng)制使用索引观话。
? ?5. MySQL 中索引的限制
? ??????1. MyISAM 存儲(chǔ)引擎索引鍵長度總和不能超過1000 字節(jié)；
????????2. BLOB 和TEXT 類型的列只能創(chuàng)建前綴索引越平；
????????3. MySQL 目前不支持函數(shù)索引频蛔；
????????4. 使用不等于（!= 或者<>）的時(shí)候MySQL 無法使用索引；
????????5. 過濾字段使用了函數(shù)運(yùn)算后（如abs(column)）秦叛，MySQL 無法使用索引晦溪；
????????6. Join 語句中Join 條件字段類型不一致的時(shí)候MySQL 無法使用索引；
????????7. 使用LIKE 操作的時(shí)候如果條件以通配符開始（ '%abc...'）MySQL 無法使用索引挣跋；
????????8. 使用非等值查詢的時(shí)候MySQL 無法使用Hash 索引三圆；
13.Join 的實(shí)現(xiàn)原理及優(yōu)化思路
? ??在MySQL 中只有Nested Loop Join算法，實(shí)際上就是通過驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)避咆，然后一條一條的通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個(gè)表中查詢數(shù)據(jù)舟肉，然后合并結(jié)果。
? ? 當(dāng)join類型為all,index,rang,index_merge時(shí)查库，會(huì)使用Join Buffer(緩存)結(jié)果集路媚，可以通過join_buffer_size 參數(shù)設(shè)置Join buffer的大小
? ??Join 語句的優(yōu)化
? ??????1. 盡可能減少Join 語句中的Nested Loop 的循環(huán)總次數(shù)。永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大的結(jié)果集
? ??????2. 優(yōu)先優(yōu)化Nested Loop 的內(nèi)層循環(huán)膨报。因?yàn)閮?nèi)層循環(huán)是循環(huán)中執(zhí)行次數(shù)最多的
? ? ? ? 3.當(dāng)無法保證被驅(qū)動(dòng)表的Join 條件字段被索引且內(nèi)存資源充足的前提下磷籍，不要太吝惜JoinBuffer 的設(shè)置
? ? ? ? 4.保證Join 語句中被驅(qū)動(dòng)表上Join 條件字段已經(jīng)被索引
14.ORDER BY的實(shí)現(xiàn)與優(yōu)化
? ??ORDER BY 的實(shí)現(xiàn) 。在MySQL 中现柠，ORDER BY 的實(shí)現(xiàn)有如下兩種類型：
? ? ? ? a)一種是通過有序索引而直接取得有序的數(shù)據(jù)
? ??????????利用索引實(shí)現(xiàn)數(shù)據(jù)排序的方法是MySQL 中實(shí)現(xiàn)結(jié)果集排序的最佳做法院领，可以完全避免因?yàn)榕判蛴?jì)算所帶來的資源消耗。
? ? ? ? b)通過MySQL 的排序算法將存儲(chǔ)引擎中返回的數(shù)據(jù)進(jìn)行排序
? ? ? ? ? ?排序有兩種實(shí)現(xiàn)：1够吩、取出滿足過濾條件的用于排序條件的字段以及可以直接定位到行數(shù)據(jù)的行指針信息比然，在SortBuffer 中進(jìn)行實(shí)際的排序操作，然后利用排好序之后的數(shù)據(jù)根據(jù)行指針信息返回表中取得客戶端請求的其他字段的數(shù)據(jù)????2周循、根據(jù)過濾條件一次取出排序字段以及客戶端請求的所有其他字段的數(shù)據(jù)强法，并將不需要排序的字段存放在一塊內(nèi)存區(qū)域中，然后在Sort Buffer 中將排序字段和行指針信息進(jìn)行排序湾笛，最后再利用排序后的行指針與存放在內(nèi)存區(qū)域中和其他字段一起的行指針信息進(jìn)行匹配合并結(jié)果集
? ??ORDER BY 的優(yōu)化：
? ? ? ? a)盡可能根據(jù)索引排序
? ? ? ? b)當(dāng)我們無法避免排序操作的時(shí)候饮怯，可以配置一下參數(shù)進(jìn)行優(yōu)化：
? ? ? ? ? ? 1.加大max_length_for_sort_data 參數(shù)的設(shè)置
? ??????????????????在MySQL 中，決定使用第一種老式的排序算法還是新的改進(jìn)算法的依據(jù)是通過參數(shù)max_length_for_sort_data 來決定的嚎研。當(dāng)我們所有返回字段的最大長度小于這個(gè)參數(shù)值的時(shí)候蓖墅，MySQL 就會(huì)選擇改進(jìn)后的排序算法，反之，則選擇老式的算法（需多訪問一次數(shù)據(jù)庫）
? ? ? ? ? ? 2..去掉不必要的返回字段
? ??????????3. 增大sort_buffer_size 參數(shù)設(shè)置
? ??????????????????讓MySQL可以盡量減少在排序過程中對需要排序的數(shù)據(jù)進(jìn)行分段论矾，因?yàn)檫@樣會(huì)造成MySQL 不得不使用臨時(shí)表來進(jìn)行交換排序教翩。
15.GROUP BY 的實(shí)現(xiàn)與優(yōu)化
? ? GROUP BY 實(shí)際上也同樣需要進(jìn)行排序操作，而且與ORDER BY 相比贪壳，GROUP BY 主要只是多了排序之后的分組操作饱亿。
? ??GROUP BY 的實(shí)現(xiàn)有三種：
? ? ? ? ? ? a)使用松散（Loose）（效率高）索引掃描實(shí)現(xiàn)GROUP BY。實(shí)際上就是當(dāng)MySQL 完全利用索引掃描來實(shí)現(xiàn)GROUP BY 的時(shí)候闰靴，并不需要掃描所有滿足條件的索引鍵即可完成操作得出結(jié)果彪笼。在執(zhí)行計(jì)劃的Extra 信息中有信息顯示“Using index for group-by”，實(shí)際上這就是告訴我們蚂且，MySQL Query Optimizer 通過使用松散索引掃描來實(shí)現(xiàn)了我們所需要的GROUP BY 操作杰扫。 要利用到松散索引掃描實(shí)現(xiàn)GROUP BY，需要至少滿足以下幾個(gè)條件：1膘掰、GROUP BY 條件字段必須在同一個(gè)索引中最前面的連續(xù)位置；2佳遣、在使用GROUP BY 的同時(shí)识埋，只能使用MAX 和MIN 這兩個(gè)聚合函數(shù)；3零渐、如果引用到了該索引中GROUP BY 條件之外的字段條件的時(shí)候窒舟，必須以常量形式存在。
? ? ? ????? b)使用緊湊（Tight）索引掃描實(shí)現(xiàn)GROUP BY诵盼。緊湊索引掃描實(shí)現(xiàn)GROUP BY 和松散索引掃描的區(qū)別主要在于他需要在掃描索引的時(shí)候惠豺，讀取所有滿足條件的索引鍵，然后再根據(jù)讀取的數(shù)據(jù)來完成GROUP BY 操作得到相應(yīng)結(jié)果风宁。執(zhí)行計(jì)劃的Extra 信息中顯示“Using where; Using index”
? ? ? ? ? ?c)使用臨時(shí)表實(shí)現(xiàn)GROUP BY洁墙。當(dāng)MySQL QueryOptimizer 無法找到合適的索引可以利用的時(shí)候，就不得不先讀取需要的數(shù)據(jù)戒财，然后通過臨時(shí)表來完成GROUP BY 操作热监。執(zhí)行計(jì)劃的Extra 信息中顯示“Using where; Using index; Using temporary; Using filesort”
? ??針對GROUP BY有兩種優(yōu)化思路：
? ??????1. 盡可能讓MySQL 可以利用索引來完成GROUP BY 操作
? ??????2. 當(dāng)無法使用索引完成GROUP BY 的時(shí)候，由于要使用到臨時(shí)表且需要filesort饮寞，所以我們必須要有足夠的sort_buffer_size孝扛。盡量不要進(jìn)行大結(jié)果集的GROUP BY 操作，因?yàn)槿绻鱿到y(tǒng)設(shè)置的臨時(shí)表大小的時(shí)候會(huì)出現(xiàn)將臨時(shí)表數(shù)據(jù)copy 到磁盤上面再進(jìn)行操作幽崩，這時(shí)候的排序分組操作性能將是成數(shù)量級(jí)的下降苦始；
16.DISTINCT 的實(shí)現(xiàn)與優(yōu)化
? ? ? ?DISTINCT 實(shí)際上和GROUP BY 的操作非常相似，只不過是在GROUP BY 之后的每組中只取出一條記錄而已慌申。但是陌选，和GROUP BY 有一點(diǎn)差別的是，DISTINCT 并不需要進(jìn)行排序。也就是說柠贤，在僅僅只是DISTINCT 操作的Query 如果無法僅僅利用索引完成操作的時(shí)候香浩，MySQL 會(huì)利用臨時(shí)表來做一次數(shù)據(jù)的“緩存”，但是不會(huì)對臨時(shí)表中的數(shù)據(jù)進(jìn)行filesort 操作臼勉。
16.高效的模型設(shè)計(jì)
? ? 1邻吭、適度冗余- 讓Query 盡量減少Join
? ? 2、大字段垂直分拆
? ? 3宴霸、大表水平分拆
? ? 4囱晴、統(tǒng)計(jì)表- 準(zhǔn)實(shí)時(shí)優(yōu)化
17.合適的數(shù)據(jù)類型
? ??1. 通過選用更“小”的數(shù)據(jù)類型減少存儲(chǔ)空間，使查詢相同數(shù)據(jù)需要的IO 資源降低瓢谢；
? ??2. 通過合適的數(shù)據(jù)類型加速數(shù)據(jù)的比較畸写；
????????對于數(shù)字類型，這里分別列出了整數(shù)類型和小數(shù)類型氓扛，也就是浮點(diǎn)數(shù)類型枯芬。實(shí)際上，還有一類通過二進(jìn)制格式以字符串來存放的數(shù)字類型如DECIMAL(DEC)[(M[,D])]采郎，NUMERIC[(M[,D])]千所，由于其存放長度主要通過其定義時(shí)候的的M 所決定，M 定義為多大蒜埋，則實(shí)際存放就有多長淫痰。M 代表整個(gè)位數(shù)長度，而D 則表示小數(shù)點(diǎn)后的位數(shù)整份，默認(rèn)M 為10待错，D 為0。一般來說烈评，主要用在固定精度的場合火俄，由于其存放長度較大，而且考慮到這種數(shù)據(jù)完全可以變化形式以整數(shù)存放础倍，所以并不是特別推薦烛占。
????????時(shí)間存儲(chǔ)格式總類并不是太多，我們常用的主要就是DATETIME沟启，DATE 和TIMESTAMP忆家，從存儲(chǔ)空間來看TIMESTAMP 最少，四個(gè)字節(jié)德迹，而其他兩種數(shù)據(jù)類型都是八個(gè)字節(jié)芽卿，多了一倍。而TIMESTAMP 的缺點(diǎn)在于他只能存儲(chǔ)從1970 年之后的時(shí)間胳搞，而另外兩種時(shí)間類型可以存放最早從1001 年開始的時(shí)間卸例。但是只要我們不需要使用1970 年之前的時(shí)間称杨，最好盡量使用TIMESTAMP 來減少存儲(chǔ)空間的占用。
????????CHAR[(M)]類型屬于靜態(tài)長度類型筷转，存放長度完全以字符數(shù)來計(jì)算姑原，所以最終的存儲(chǔ)長度是基于字符集的。VARCHAR[(M)]屬于動(dòng)態(tài)存儲(chǔ)長度類型呜舒，僅存占用實(shí)際存儲(chǔ)數(shù)據(jù)的長度锭汛。TINYTEXT，TEXT袭蝗，MEDIUMTEXT 和LONGTEXT 這四種類型同屬于一種存儲(chǔ)方式唤殴，都是動(dòng)態(tài)存儲(chǔ)長度類型，不同的僅僅是最大長度的限制到腥。
18.MySQL Server 性能優(yōu)化
? ??源碼包的編譯參數(shù)推薦
19.MySQL 日志設(shè)置優(yōu)化
? ??在默認(rèn)情況下朵逝，系統(tǒng)僅僅打開錯(cuò)誤日志，關(guān)閉了其他所有日志乡范，但是在一般稍微重要一點(diǎn)的實(shí)際應(yīng)用場景中配名，都至少需要打開二進(jìn)制日志，因?yàn)檫@是MySQL很多存儲(chǔ)引擎進(jìn)行增量備份的基礎(chǔ)晋辆，也是MySQL 實(shí)現(xiàn)復(fù)制的基本條件段誊。一般情況下，在生產(chǎn)系統(tǒng)中最好關(guān)閉查詢?nèi)罩菊煌希瑴p少io負(fù)擔(dān)。
? ??Binlog 相關(guān)參數(shù)及優(yōu)化策略：
? ? ? ? 查看相關(guān)參數(shù)命令 show variables like '%binlog%';
? ??????binlog_cache_size：在事務(wù)過程中容納二進(jìn)制日志SQL 語句的緩存大小没陡，注意涩哟，是每個(gè)Client 都可以分配設(shè)置大小的binlog cache 空間∨涡可以通過MySQL 的以下兩個(gè)狀態(tài)變量來判斷當(dāng)前的binlog_cache_size 的狀況：Binlog_cache_use 和Binlog_cache_disk_use贴彼。
? ? ? ? max_binlog_cache_size：和"binlog_cache_size"相對應(yīng)，但是所代表的是binlog 能夠使用的最大cache 內(nèi)存大小埃儿。
? ??????max_binlog_size：Binlog 日志最大值器仗，一般來說設(shè)置為512M 或者1G，但不能超過1G童番。
? ??????sync_binlog：這個(gè)參數(shù)是對于MySQL 系統(tǒng)來說是至關(guān)重要的精钮，他不僅影響到Binlog 對MySQL 所帶來的性能損耗，而且還影響到MySQL 中數(shù)據(jù)的完整性剃斧。對于“sync_binlog”參數(shù)的各種設(shè)置的說明如下： 1)sync_binlog=0轨香，當(dāng)事務(wù)提交之后，MySQL 不做fsync 之類的磁盤同步指令刷新binlog_cache 中的信息到磁盤幼东，而讓Filesystem 自行決定什么時(shí)候來做同步臂容，或者cache 滿了之后才同步到磁盤科雳。2）sync_binlog=n，當(dāng)每進(jìn)行n 次事務(wù)提交之后脓杉，MySQL 將進(jìn)行一次fsync 之類的磁盤同步指令來將binlog_cache 中的數(shù)據(jù)強(qiáng)制寫入磁盤糟秘。
????Slow Query Log 相關(guān)參數(shù)及使用建議：
? ? ? ? 查看慢查詢相關(guān)設(shè)置：show variables like 'slow_query%';
????????????????????????????????????????????show variables like 'long_query%';
20.連接池相關(guān)優(yōu)化
? ??網(wǎng)絡(luò)連接的性能配置項(xiàng)：
? ??????max_conecctions：整個(gè)MySQL 允許的最大連接數(shù)。 這個(gè)參數(shù)主要影響的是整個(gè)MySQL 應(yīng)用的并發(fā)處理能力球散，當(dāng)系統(tǒng)中實(shí)際需要的連接量大于max_conecctions 的情況下尿赚，由于MySQL 的設(shè)置限制，那么應(yīng)用中必然會(huì)產(chǎn)生連接請求的等待沛婴，從而限制了相應(yīng)的并發(fā)量吼畏。所以一般來說，只要MySQL 主機(jī)性能允許嘁灯，都是將該參數(shù)設(shè)置的盡可能大一點(diǎn)泻蚊。一般來說500 到800 左右是一個(gè)比較合適的參考值
? ??????max_user_connections：每個(gè)用戶允許的最大連接數(shù)，針對于單個(gè)用戶的連接限制丑婿。在一般情況下我們可能都較少使用這個(gè)限制
? ??????net_buffer_length：網(wǎng)絡(luò)包傳輸中性雄，傳輸消息之前的net buffer 初始化大小羹奉；這個(gè)參數(shù)主要可能影響的是網(wǎng)絡(luò)傳輸?shù)男拭胄捎谠搮?shù)所設(shè)置的只是消息緩沖區(qū)的初始化大小，所以造成的影響主要是當(dāng)我們的每次消息都很大的時(shí)候MySQL 總是需要多次申請擴(kuò)展該緩沖區(qū)大小诀拭。系統(tǒng)默認(rèn)大小為16KB迁筛，一般來說可以滿足大多數(shù)場景，當(dāng)然如果我們的查詢都是非常小耕挨，每次網(wǎng)絡(luò)傳輸量都很少细卧，而且系統(tǒng)內(nèi)存又比較緊缺的情況下，也可以適當(dāng)將該值降低到8KB筒占。
? ??????max_allowed_packet：在網(wǎng)絡(luò)傳輸中贪庙，一次傳消息輸量的最大值。這個(gè)參數(shù)net_buffer_length 相對應(yīng)翰苫，只不過是net buffer 的最大值止邮。當(dāng)我們的消息傳輸量大于net_buffer_length 的設(shè)置時(shí)，MySQL 會(huì)自動(dòng)增大net buffer 的大小奏窑，直到緩沖區(qū)大小達(dá)到max_allowed_packet 所設(shè)置的值导披。系統(tǒng)默認(rèn)值為1MB，最大值是1GB埃唯，必須設(shè)定為1024 的倍數(shù)盛卡，單位為字節(jié)。
? ??????back_log：在MySQL 的連接請求等待隊(duì)列中允許存放的最大連接請求數(shù)筑凫。
?????相關(guān)的系統(tǒng)參數(shù)及狀態(tài)變量說明如下：
? ??????thread_cache_size：Thread Cache 池中應(yīng)該存放的連接線程數(shù)滑沧。在短連接的應(yīng)用系統(tǒng)中并村，thread_cache_size 的值應(yīng)該設(shè)置的相對大一些
? ??????thread_stack：每個(gè)連接線程被創(chuàng)建的時(shí)候，MySQL 給他分配的內(nèi)存大小滓技。當(dāng)MySQL 創(chuàng)建一個(gè)新的連接線程的時(shí)候哩牍，是需要給他分配一定大小的內(nèi)存堆棧空間令漂，以便存放客戶端的請求Query 以及自身的各種狀態(tài)和處理信息膝昆。
? ??????show status like 'connections'：系統(tǒng)被連接的次數(shù)?
? ??????show status like '%thread%'：當(dāng)前系統(tǒng)中連接線程的狀態(tài)
21.Sort Buffer，Join Buffer 和Read Buffer
? ? 查看相關(guān)配置信息：show variables like '%buffer%';可以查看join_buffer_size和sort_buffer_size叠必。
? ??join_buffer_size ：當(dāng)我們的Join 是ALL 荚孵， index ，rang 或者index_merge 的時(shí)候使用的Buffer纬朝；實(shí)際上這種Join 被稱為Full Join收叶。實(shí)際上參與Join 的每一個(gè)表都需要一個(gè)Join Buffer，所以在Join 出現(xiàn)的時(shí)候共苛，至少是兩個(gè)判没。Join Buffer 的設(shè)置在MySQL 5.1.23 版本之前最大為4GB，但是從5.1.23 版本開始隅茎，在除了Windows 之外的64 位的平臺(tái)上可以超出4BG 的限制澄峰。系統(tǒng)默認(rèn)128KB。
sort_buffer_size：系統(tǒng)中對數(shù)據(jù)進(jìn)行排序的時(shí)候使用的Buffer辟犀；Sort Buffer 同樣是針對單個(gè)Thread的俏竞，所以當(dāng)多個(gè)Thread 同時(shí)進(jìn)行排序的時(shí)候，系統(tǒng)中就會(huì)出現(xiàn)多個(gè)Sort Buffer堂竟。一般我們可以通過增大Sort Buffer 的大小來提高ORDER BY 或者是GROUP BY的處理性能胞此。系統(tǒng)默認(rèn)大小為2MB，最大限制和Join Buffer 一樣跃捣，在MySQL 5.1.23 版本之前最大為4GB，從5.1.23 版本開始夺蛇，在除了Windows 之外的64 位的平臺(tái)上可以超出4GB 的限制疚漆。
? ??如果應(yīng)用系統(tǒng)中很少有Join 語句出現(xiàn)，則可以不用太在乎join_buffer_size 參數(shù)的大小設(shè)置刁赦，但是如果Join 語句不是很少的話娶聘，個(gè)人建議可以適當(dāng)增大join_buffer_size 的設(shè)置到1MB 左右，如果內(nèi)存充足甚至可以設(shè)置為2MB甚脉。對于sort_buffer_size 參數(shù)來說丸升，一般設(shè)置為2MB 到4MB 之間可以滿足大多數(shù)應(yīng)用的需求。
22.MyI SAM存儲(chǔ)引擎優(yōu)化
? ??MyISAM 存儲(chǔ)引擎的索引和數(shù)據(jù)是分開存放于“.MYI”文件中牺氨，每個(gè)“.MYI”文件由文件頭和實(shí)際的索引數(shù)據(jù)狡耻《掌剩“.MYI”的文件頭中主要存放四部分信息，分別稱為：state（主要是整個(gè)索引文件的基本信息）夷狰，base（各個(gè)索引的相關(guān)信息岭皂，主要是索引的限制信息）， keydef（每個(gè)索引的定義信息）和recinfo（每個(gè)索引記錄的相關(guān)信息）沼头。在文件頭后面緊接著的就是實(shí)際的索引數(shù)據(jù)信息了爷绘。索引數(shù)據(jù)以Block（Page）為最小單位，每個(gè)block 中只會(huì)存在同一個(gè)索引的數(shù)據(jù)进倍，這主要是基于提高索引的連續(xù)讀性能的目的土至。在MySQL 中，索引文件中索引數(shù)據(jù)的block 被稱為Index Block猾昆，每個(gè)Index Block 的大小并不一定相等陶因。
? ??在“.MYI”中，Index Block 的組織形式實(shí)際上只是一種邏輯上的毡庆，并不是物理意義上的坑赡。在物理上，實(shí)際上是以File Block 的形式來存放在磁盤上面的么抗。在Key Cache 中緩存的索引信息是以“Cache Block”的形式組織存放的毅否，“Cache Block”是相同大小的，和“.MYI”文件物理存儲(chǔ)的Block（ File Block ） 一樣蝇刀。在一條Query 通過索引檢索表數(shù)據(jù)的時(shí)候螟加， 首先會(huì)檢查索引緩存（key_buffer_cache）中是否已經(jīng)有需要的索引信息，如果沒有吞琐，則會(huì)讀取“.MYI”文件捆探，將相應(yīng)的索引數(shù)據(jù)讀入Key Cache 中的內(nèi)存空間中，同樣也是以Block 形式存放站粟，被稱為Cache Block黍图。不過，數(shù)據(jù)的讀入并不是以Index Block 的形式來讀入奴烙，而是以File Block 的形式來讀入的助被。以File Block 形式讀入到Key Cache 之后的Cache Block 實(shí)際上是于File Block 完全一樣的。如下圖所示：
? ? 索引緩存優(yōu)化
????????MyISAM 索引緩存相關(guān)的幾個(gè)系統(tǒng)參數(shù)和狀態(tài)參數(shù)：
? ??????◆ key_buffer_size切诀，索引緩存大锌贰；
? ? ? ? ? ? 這個(gè)參數(shù)用來設(shè)置整個(gè)MySQL 中的常規(guī)Key Cache 大小幅虑。一般來說丰滑，如果我們的MySQL 是運(yùn)行在32 位平臺(tái)紙上，此值建議不要超過2GB 大小倒庵。如果是運(yùn)行在64 位平臺(tái)紙上則不用考慮此限制褒墨，但也最好不要超過4GB炫刷。
? ??????◆ key_buffer_block_size，索引緩存中的Cache Block Size貌亭；
? ? ? ? ? ? 在Key Cache 中的所有數(shù)據(jù)都是以Cache Block 的形式存在柬唯，而key_buffer_block_size 就是設(shè)置每個(gè)Cache Block 的大小，實(shí)際上也同時(shí)限定了我們將“.MYI”文件中的Index Block 被讀入時(shí)候的File Block 的大小圃庭。
? ??????◆ key_cache_division_limit锄奢，LRU 鏈表中的Hot Area 和Warm Area 分界值；
? ??????????實(shí)際上剧腻，在MySQL 的Key Cache 中所使用的LRU 算法并不像傳統(tǒng)的算法一樣僅僅只是通過訪問頻率以及最后訪問時(shí)間來通過一個(gè)唯一的鏈表實(shí)現(xiàn)拘央，而是將其分成了兩部分。一部分用來存放使用比較頻繁的Hot Cacke Lock（Hot Chain）书在，被成為Hot Area灰伟，另外一部分則用來存放使用不是太頻繁的Warm Cache Block（Warm Chain），被成為Warm Area儒旬。這樣做的目的主要是為了保護(hù)使用比較頻繁的Cache Block 更不容易被換出栏账。而key_cache_division_limit 參數(shù)則是告訴MySQL該如何劃分整個(gè)Cache Chain劃分為Hot Chain和Warm Chain 兩部分，參數(shù)值為WarmChain 占整個(gè)Chain 的百分比值栈源。設(shè)置范圍1～100挡爵，系統(tǒng)默認(rèn)為100，也就是只有Warm Chain甚垦。
? ??????◆ key_cache_age_threshold茶鹃，控制Cache Block 從Hot Area 降到Warm Area 的限制；????????
? ??????????key_cache_age_threshold參數(shù)控制Hot Area 中的Cache Block 何時(shí)該被降級(jí)到Warm Area 中艰亮。系統(tǒng)默認(rèn)值為300闭翩，最小可以設(shè)置為100。值越小迄埃，被降級(jí)的可能性越大疗韵。
? ??key_buffer_size計(jì)算指標(biāo)：Key_Size = key_number * (key_length+4)/0.67
? ??Key Cache 的命中率：
? ??????Cache 相關(guān)的性能狀態(tài)參數(shù)變量。
????????◆ Key_blocks_not_flushed侄非，已經(jīng)更改但還未刷新到磁盤的Dirty Cache Block蕉汪；
????????◆ Key_blocks_unused，目前未被使用的Cache Block 數(shù)目彩库；
????????◆ Key_blocks_used，已經(jīng)使用了的Cache Block 數(shù)目先蒋；
????????◆ Key_read_requests骇钦，Cache Block 被請求讀取的總次數(shù)；
????????◆ Key_reads竞漾，在Cache Block 中找不到需要讀取的Key 信息后到“.MYI”文件中讀取的次數(shù)眯搭；
????????◆ Key_write_requests窥翩，Cache Block 被請求修改的總次數(shù)；
????????◆ Key_writes鳞仙，在Cache Block 中找不到需要修改的Key 信息后到“.MYI”文件中讀入再修改的次
數(shù)寇蚊；????
? ??????Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *
100%? ? ? ? ? ? //緩存使用率，如果該值過低說明key_bufffer_size設(shè)置過大棍好。
? ? ? ??Key_Buffer_Read_HitRatio = (1 - Key_reads/Key_read_requests) * 100%? ? //緩存命中率仗岸，如果該值較低可能是key_buffer_size設(shè)置較小借笙；或key_cache_age_thresholdkey_cache_division_limit的設(shè)置不當(dāng)扒怖，造成Key Cache cache失效太快。
????多Key Cache 的使用?
? ??????MySQL 官方建議在比較繁忙的系統(tǒng)上一般可以設(shè)置三個(gè)Key Cache：
????????????一個(gè)Hot Cache 使用20%的大小用來存放使用非常頻繁且更新很少的表的索引业稼；
????????????一個(gè)Cold Cache 使用20%的大小用來存放更新很頻繁的表的索引盗痒；
????????????一個(gè)Warm Cache 使用剩下的60%空間，作為整個(gè)系統(tǒng)默認(rèn)的Key Cache低散；
? ??Key Cache 預(yù)加載：LOAD INDEX INTO CACHE tb_name_list ...俯邓； 對于這種啟動(dòng)后立即加載的操作，可以利用MySQL 的init_file 參數(shù)來設(shè)置相關(guān)的命令熔号，如下：
? ??表讀取緩存優(yōu)化
????????在MySQL 中有兩種讀取數(shù)據(jù)文件的緩沖區(qū)稽鞭，一種是Sequential Scan 方式（如全表掃描）掃描表數(shù)據(jù)的時(shí)候使用，另一種則是在Random Scan（如通過索引掃描）的時(shí)候使用跨嘉。雖然這兩種文件讀取緩沖區(qū)并不是MyISAM 存儲(chǔ)引擎所特有的川慌，但是由于MyISAM 存儲(chǔ)引擎并不會(huì)Cache 數(shù)據(jù)（.MYD）文件，每次對數(shù)據(jù)文件的訪問都需要通過調(diào)用文件系統(tǒng)的相關(guān)指令從磁盤上面讀取物理文件祠乃。所以梦重，每次讀取數(shù)據(jù)文件需要使用的內(nèi)存緩沖區(qū)的設(shè)置就對數(shù)據(jù)文件訪問的性能非常重要了。
? ??◆ read_buffer_size亮瓷，以Sequential Scan 方式掃描表數(shù)據(jù)時(shí)候使用的Buffer琴拧；
? ? ? ??每個(gè)Thread 進(jìn)行Sequential Scan 的時(shí)候都會(huì)產(chǎn)生該Buffer，所以在設(shè)置的時(shí)候盡量不要太高嘱支，避免因?yàn)椴l(fā)太大造成內(nèi)存不夠蚓胸。一般來說，可以嘗試適當(dāng)調(diào)大此參數(shù)看是否能夠改善全表掃描的性能除师。
? ??◆ read_rnd_buffer_size沛膳，進(jìn)行Random Scan 的時(shí)候使用的Buffer；
? ??????一般來說汛聚，read_rnd_buffer_size 值的適當(dāng)調(diào)大锹安，對提高ORDER BY 操作的性能有一定的效果。
并發(fā)優(yōu)化
? ??由于MyISAM 存儲(chǔ)引擎的表級(jí)鎖定機(jī)制，以及讀寫互斥的問題叹哭，其并發(fā)寫的性能較差忍宋。如果覺得光靠Key Cache 來緩存索引還是不夠快的話，我們還可以通過Query Cache 功能來直接緩存Query 的結(jié)果集风罩。
? ??1. 打開concurrent_insert 的功能糠排，提高INSERT 操作和SELECT 之間的并發(fā)處理，大部分情況下concurrent_insert 的值都被設(shè)置為1超升，當(dāng)表中沒有刪除記錄留下的空余空間的時(shí)候都可以在尾部并行插入入宦。如果我們的系統(tǒng)主要以寫為主，尤其是有大量的INSERT 的時(shí)候廓俭。為了盡可能提高INSERT 的效率云石，我們可以將concurrent_insert 設(shè)置為2，也就是告訴MyISAM研乒，不管在表中是否有刪除行留下的空余空間汹忠，都在尾部進(jìn)行并發(fā)插入，使INSERT 和SELECT 能夠互不干擾雹熬。
? ??2. 控制寫入操作的大小宽菜，盡量讓每次寫入操作都能夠很快的完成
? ??3. 通過犧牲讀取效率來提高寫入效率。為了盡可能讓寫入更快竿报，可以適當(dāng)調(diào)整讀和寫的優(yōu)先級(jí)別铅乡，讓寫入操作的優(yōu)先級(jí)高于讀操作的優(yōu)先級(jí)。
????除了上面我們分析的這幾個(gè)方面之外烈菌，MyISAM還存在其他一些可以優(yōu)化的地方和一些常用的優(yōu)化技巧阵幸。1. 通過OPTIMIZE 命令來整理MyISAM 表的文件? ?2. 設(shè)置myisam_max_[extra]_sort_file_size 足夠大，對REPAIR TABLE 的效率可能會(huì)有較大改善芽世。3. 在執(zhí)行CREATE INDEX 或者REPAIR TABLE 等需要大的排序操作的之前可以通過調(diào)整session 級(jí)別的myisam_sort_buffer_size 參數(shù)值來提高排序操作的效率挚赊。4. 通過打開delay_key_write 功能，減少IO 同步的操作济瓢，提高寫入性能荠割。5. 通過調(diào)整bulk_insert_buffer_size 來提高INSERT...SELECT...這樣的bulk insert 操作的整體性能，LOAD DATA INFILE...的性能也可以得到改善旺矾。
23.I nnodb 存儲(chǔ)引擎優(yōu)化
? ??Innodb 存儲(chǔ)引擎和MyISAM 存儲(chǔ)引擎最大區(qū)別主要有四點(diǎn)蔑鹦，第一點(diǎn)是緩存機(jī)制（索引+數(shù)據(jù)），第二點(diǎn)是事務(wù)支持箕宙，第三點(diǎn)是鎖定實(shí)現(xiàn)（行級(jí)鎖定）嚎朽，最后一點(diǎn)就是數(shù)據(jù)存儲(chǔ)方式的差異（共享表空間）。
? ??無論是對于哪一種數(shù)據(jù)庫來說柬帕，緩存技術(shù)都是提高數(shù)據(jù)庫性能的關(guān)鍵技術(shù)哟忍，物理磁盤的訪問速度永遠(yuǎn)都會(huì)與內(nèi)存的訪問速度永遠(yuǎn)都不是一個(gè)數(shù)量級(jí)的室囊。通過緩存技術(shù)無論是在讀還是寫方面都可以大大提高數(shù)據(jù)庫整體性能。
Innodb_buffer_pool_size 的合理設(shè)置
? ? 可以同過show status like 'Innodb_buffer_pool_%' 指令查看Buffer pool使用情況
? ? 上面的值可以看出總共有8192pages,還有6765是free狀態(tài)，只有1420個(gè)pages有數(shù)據(jù)，read請求47848次瘫想，其中1066次請求buffer pool中沒有蹋偏，也就是說有1066次是通過讀取物理磁盤獲取數(shù)據(jù)的，很容易的出read命中率大概為：(47848-1066)/47848*100%=97.7%
? ??當(dāng)然饶火，通過上面的數(shù)據(jù)鹏控，我們還可以分析出write 命中率，可以得到發(fā)生了多少次read_ahead_rnd肤寝，多少次read_ahead_seq当辐，發(fā)生過多少次latch，多少次因?yàn)锽uffer 空間大小不足而產(chǎn)生wait_free 等等鲤看。
? ??單從這里的數(shù)據(jù)來看缘揪，我們設(shè)置的Buffer Pool 過大，僅僅使用1420/ 8192* 100% = 17.33%义桂。
innodb_log_buffer_size 參數(shù)的使用
? ??顧名思義找筝，這個(gè)參數(shù)就是用來設(shè)置Innodb 的Log Buffer 大小的，系統(tǒng)默認(rèn)值為1MB慷吊。Log Buffer的主要作用就是緩沖Log 數(shù)據(jù)袖裕，提高寫Log 的IO 性能。一般來說溉瓶，如果你的系統(tǒng)不是寫負(fù)載非常高且以大事務(wù)居多的話急鳄，8MB 以內(nèi)的大小就完全足夠了。
? ? 查看log buffer使用情況：show status like 'innodb_log%';
? ??如果完全從Log Buffer 本身來說堰酿，自然是大一些會(huì)減少更多的磁盤IO疾宏。但是由于Log 本身是為了保護(hù)數(shù)據(jù)安全而產(chǎn)生的，而Log 從Buffer 到磁盤的刷新頻率和控制數(shù)據(jù)安全一致的事務(wù)直接相關(guān)胞锰，并且也有相關(guān)參數(shù)來控制（innodb_flush_log_at_trx_commit）所以得進(jìn)行權(quán)衡灾锯。
24.事務(wù)優(yōu)化
? ??Innodb 的事務(wù)隔離級(jí)別：
? ? ? ? 1.READ UNCOMMITTED：常被成為Dirty Reads（臟讀）,最低隔離級(jí)別，在普通的非鎖定模式下SELECT 的執(zhí)行使我們看到的數(shù)據(jù)可能并不是查詢發(fā)起時(shí)間點(diǎn)的數(shù)據(jù)嗅榕，因而在這個(gè)隔離度下是非Consistent Reads（一致性讀）顺饮；
? ??????2. READ COMMITTED：這一隔離級(jí)別下，不會(huì)出現(xiàn)Dirty Read凌那，但是可能出現(xiàn)Non-Repeatable Reads(不可重復(fù)讀)和Phantom Reads（幻讀）兼雄。屬于語句級(jí)別的隔離，如通過SELECT ... FOR UPDATE 和SELECT ... LOCK IN SHARE MODE 來執(zhí)行的請求僅僅鎖定索引記錄帽蝶，而不鎖定之前的間隙赦肋，因而允許在鎖定的記錄后自由地插入新記錄。
? ??????3. REPEATABLE READ：InnoDB 默認(rèn)的事務(wù)隔離級(jí)別。在這一級(jí)中佃乘，同一事務(wù)中所有的Consistent Reads 均讀取第一次讀取時(shí)已確定的快照囱井。在REPEATABLE READ 隔離級(jí)別下，不會(huì)出現(xiàn)Dirty Reads趣避，也不會(huì)出現(xiàn)Non-Repeatable Reads庞呕，但是仍然存在Phantom Reads 的可能性。SELECT ... FOR UPDATE, SELECT... LOCK IN SHARE MODE, UPDATE, 和DELETE 程帕，這些以唯一條件搜索唯一索引的住练，只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙愁拭。
? ? ? ? 4.SERIALIZABLE：讲逛。設(shè)置為SERIALIZABLE 隔離級(jí)別之后，在事務(wù)中的任何時(shí)候所看到的數(shù)據(jù)都是事務(wù)啟動(dòng)時(shí)刻的狀態(tài)岭埠。不論在這期間有沒有其他事務(wù)已經(jīng)修改了某些數(shù)據(jù)并提交盏混。所以，SERIALIZABLE 事務(wù)隔離級(jí)別下惜论，Phantom Reads 也不會(huì)出現(xiàn)括饶。（幻讀：并不是說兩次讀取獲取的結(jié)果集不同，幻讀側(cè)重的方面是某一次的 select 操作得到的結(jié)果所表征的數(shù)據(jù)狀態(tài)無法支撐后續(xù)的業(yè)務(wù)操作来涨。更為具體一些：select 某記錄是否存在图焰，不存在，準(zhǔn)備插入此記錄蹦掐，但執(zhí)行 insert 時(shí)發(fā)現(xiàn)此記錄已存在技羔，無法插入，此時(shí)就發(fā)生了幻讀）
解決不可重復(fù)讀的方法是?鎖行卧抗，解決幻讀的方式是?鎖表
25.悲觀鎖和樂觀鎖
數(shù)據(jù)庫中的樂觀鎖和悲觀鎖以及實(shí)現(xiàn)方式
樂觀鎖：獲取數(shù)據(jù)時(shí)不會(huì)考慮并發(fā)情況造成的數(shù)據(jù)沖突藤滥，然后再數(shù)據(jù)更新提交時(shí)正式對數(shù)據(jù)的沖突與否進(jìn)行檢測，如果發(fā)現(xiàn)沖突了社裆，則返回錯(cuò)誤信息拙绊，讓用戶重新操作。
悲觀鎖：總是做最壞的打算泳秀，每次去讀取數(shù)據(jù)都會(huì)認(rèn)為會(huì)被其它線程修改标沪，所以會(huì)加鎖,當(dāng)其它線程想要訪問數(shù)據(jù)時(shí)，都需要阻塞掛起嗜傅。
樂觀鎖實(shí)現(xiàn)方式
version方式：
一般在數(shù)據(jù)表中加一個(gè)version版本字段,表示數(shù)據(jù)被修改的版本次數(shù)金句，當(dāng)數(shù)據(jù)被修改時(shí)，version會(huì)被加一吕嘀。當(dāng)線程A讀取數(shù)據(jù)時(shí)也要同時(shí)讀取version值违寞，在提交更新的時(shí)候贞瞒，如果剛才讀取的version值和當(dāng)前數(shù)據(jù)庫里的version值一致，那么才能更新趁曼，否則重新更新操作军浆，直到更新成成功。樂觀鎖在獲得鎖的同時(shí)已經(jīng)完成了更新操作
SQL代碼實(shí)現(xiàn)：
悲觀鎖實(shí)現(xiàn)方式
for update方式:
一般使用select … for update 對所選擇的數(shù)據(jù)加鎖挡闰，例如 select * from t_goods where id =1 for update, 這條sql語句就鎖定了t_goods表中符合id=1的這條記錄瘾敢，本次事務(wù)提交之前，外界無法修改這些記錄尿这。悲觀鎖遵循一鎖、二判庆杜、三更新射众、四釋放的原則
