1.MySQL Server 系統(tǒng)架構(gòu)
? ??邏輯模塊組成：MySQL 可以看成是二層架構(gòu)，第一層我們通常叫做SQL Layer，在MySQL 數(shù)據(jù)庫(kù)系統(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、初始化模塊：對(duì)整個(gè)系統(tǒng)做各種各樣的初始化操作，比如各種buffer，cache 結(jié)構(gòu)的初始化和內(nèi)存空間的申請(qǐng)，各種系統(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)了客戶(hù)端與MySQL 交互過(guò)程中的所有協(xié)議。當(dāng)然這些協(xié)議都是建立在現(xiàn)有的OS 和網(wǎng)絡(luò)協(xié)議之上的，如TCP/IP 以及Unix Socket
? ??5、用戶(hù)模塊：主要包括用戶(hù)的登錄連接權(quán)限控制和用戶(hù)的授權(quán)管理
? ??6、訪(fǎng)問(wèn)控制模塊：控制用戶(hù)對(duì)數(shù)據(jù)的訪(fǎng)問(wèn)，將用戶(hù)模塊和訪(fǎng)問(wèn)控制模塊兩者結(jié)合起來(lái)，組成了MySQL 整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的權(quán)限安全管理的功能。
? ??7、連接管理、連接線(xiàn)程和線(xiàn)程管理：連接線(xiàn)程的主要工作就是負(fù)責(zé)MySQL Server 與客戶(hù)端的通信，接受客戶(hù)端的命令請(qǐng)求，傳遞Server 端的結(jié)果信息等。線(xiàn)程管理模塊則負(fù)責(zé)管理維護(hù)這些連接線(xiàn)程。包括線(xiàn)程的創(chuàng)建，線(xiàn)程的cache 等。
? ? 8：Query 解析和轉(zhuǎn)發(fā)模塊：主要工作就是將query 語(yǔ)句進(jìn)行語(yǔ)義和語(yǔ)法的分析，然后按照不同的操作類(lèi)型進(jìn)行分類(lèi)，然后做出針對(duì)性的轉(zhuǎn)發(fā)。
? ??9、Query Cache 模塊：主要功能是將客戶(hù)端提交給MySQL 的Select 類(lèi)query 請(qǐng)求的返回結(jié)果集cache 到內(nèi)存中
? ??10、Query 優(yōu)化器模塊：優(yōu)化客戶(hù)端請(qǐng)求的query
? ??11、表變更管理模塊：表變更管理模塊主要是負(fù)責(zé)完成一些DML 和DDL 的query，如：update，delte，insert，create table，alter table 等語(yǔ)句的處理
? ??12、表維護(hù)模塊:表的狀態(tài)檢查，錯(cuò)誤修復(fù)，以及優(yōu)化和分析等工作都是表維護(hù)模塊需要做的事情。
? ??13、系統(tǒng)狀態(tài)管理模塊：系統(tǒng)狀態(tài)管理模塊負(fù)責(zé)在客戶(hù)端請(qǐng)求系統(tǒng)狀態(tài)的時(shí)候，將各種狀態(tài)數(shù)據(jù)返回給用戶(hù)
? ??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 線(xiàn)程交互等工作。Slave 模塊比Master 模塊所要做的事情稍多一些，在系統(tǒng)中主要體現(xiàn)在兩個(gè)線(xiàn)程上面。一個(gè)是負(fù)責(zé)從Master 請(qǐng)求和接受binary 日志，并寫(xiě)入本地relay log 中的I/O 線(xiàn)程。另外一個(gè)是負(fù)責(zé)從relay log 中讀取相關(guān)日志事件，然后解析成可以在Slave 端正確執(zhí)行并得到和Master 端完全相同的結(jié)果的命令并再交給Slave 執(zhí)行的SQL 線(xiàn)程。
? ??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]：打開(kāi)/關(guān)閉記錄功能
? ??????max_binlog_size：設(shè)置binlog 的最大存儲(chǔ)上限，當(dāng)日志達(dá)到該上限時(shí)，MySQL 會(huì)重新創(chuàng)建一個(gè)日志開(kāi)始繼續(xù)記錄?
????3、查詢(xún)?nèi)罩荆簈uery log
? ??????????general_log[=on|off]：打開(kāi)/關(guān)閉查詢(xún)?nèi)罩?/p>
????????????general_log_file[=filename]： 查詢(xún)?nèi)罩疚募?/p>
? ? 4、慢查詢(xún)?nèi)罩荆簊low query log
? ??????????slow_query_log[=on|off]：打開(kāi)/關(guān)閉慢查詢(xún)?nèi)罩?/p>
? ??????????long_query_time[=long]：超過(guò)多少秒的查詢(xún)就寫(xiě)入日志
? ? 5、Innodb 的在線(xiàn)redo 日志：innodb redo log
? ??????????Innodb 是一個(gè)事務(wù)安全的存儲(chǔ)引擎，其事務(wù)安全性主要就是通過(guò)在線(xiàn)redo 日志和記錄在表空間中的undo 信息來(lái)保證的。redo 日志中記錄了Innodb 所做的所有物理變更和事務(wù)信息，可以通過(guò)innodb_log_group_home_dir 來(lái)更改設(shè)置日志的存放位置，通過(guò)innodb_log_files_in_group 設(shè)置日志的數(shù)量。
3.數(shù)據(jù)文件
? ??1、“.MYD”文件：MyISAM 存儲(chǔ)引擎專(zhuān)用，存放MyISAM 表的數(shù)據(jù)。每一MyISAM 表都會(huì)有一個(gè)“.MYD”文件與之對(duì)應(yīng)。
? ? 2、“.MYI”文件：專(zhuān)屬于MyISAM 存儲(chǔ)引擎，主要存放MyISAM 表的索引相關(guān)信息
? ? 3、“.ibd”文件和ibdata 文件：獨(dú)享表空間存儲(chǔ)方式使用“.ibd”文件來(lái)存放數(shù)據(jù)，且每個(gè)表一個(gè)“.ibd”文件，文件存放在和MyISAM 數(shù)據(jù)相同的位置。如果選用共享存儲(chǔ)表空間來(lái)存放數(shù)據(jù)，則會(huì)使用ibdata 文件來(lái)存放，所有表共同使用一個(gè)（或者多個(gè)，可自行配置）ibdata 文件。ibdata 文件可以通過(guò)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ī)地址，連接用戶(hù)，連接密碼，連接端口，當(dāng)前日志位置，已經(jīng)讀取到的日志位置等信息。
? ??2、relay log 和relay log index：mysql-relay-bin.xxxxxn 文件用于存放Slave 端的I/O 線(xiàn)程從Master 端所讀取到的Binary Log 信息，然后由Slave 端的SQL 線(xiàn)程從該relay log 中讀取并解析相應(yīng)的日志信息，轉(zhuǎn)化成Master 所執(zhí)行的SQL 語(yǔ)句，然后在Slave 端應(yīng)用。
? ?3、 relay-log.info 文件：類(lèi)似于master.info，它存放通過(guò)Slave 的I/O 線(xiàn)程寫(xiě)入到本地的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：用戶(hù)在Unix/Linux 環(huán)境下客戶(hù)端連接可以不通過(guò)TCP/IP 網(wǎng)絡(luò)而直接使用Unix Socket 來(lái)連接MySQL。
6.mysql自帶工具
????mysqladmin：與MySQL 管理相關(guān)的各種功能
? ??????Usage: mysqladmin [OPTIONS] command command ...
? ??????mysqladmin -uroot -p123 -hlocalhost ping????//檢測(cè)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ù)庫(kù)的連接線(xiàn)程信息
? ??????此外，還可以通過(guò)mysqladmin 來(lái)start slave 和stop slave，kill 某個(gè)連接到MySQL Server 的線(xiàn)程等等。
? ??mysqldump? ?:將MySQL Server中的數(shù)據(jù)以SQL 語(yǔ)句的形式從數(shù)據(jù)庫(kù)中dump 成文本文件。這
個(gè)功能實(shí)際上是調(diào)用了MySQL 中的“select * into OUTFILE from ...”語(yǔ)句而實(shí)現(xiàn)
? ?????Usage: mysqldump [OPTIONS] database [tables].
????????OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
????????OR mysqldump [OPTIONS] --all-databases [OPTIONS]
? ??????可以通過(guò)“-d,--no-data”僅僅生成結(jié)構(gòu)創(chuàng)建的語(yǔ)句,每次執(zhí)行mysqldump 程序的時(shí)候都通過(guò)盡量做到“--defaultcharacter-set=name”顯式指定字符集內(nèi)容
? ??mysqlimport ：mysqlimport 程序是一個(gè)將以特定格式存放的文本數(shù)據(jù)（如通過(guò)“select * into OUTFILE from ...”所生成的數(shù)據(jù)文件）導(dǎo)入到指定的MySQL Server 中的工具程序
? ??Usage: mysqlimport [OPTIONS] database textfile ...
? ??mysqlbinlog：通過(guò)mysqlbinlog，我們可以解析出binlog 中指定時(shí)間段或者指定日志起始和結(jié)束位置的內(nèi)容解析成SQL 語(yǔ)句，并導(dǎo)出到指定的文件中，在解析過(guò)程中，還可以通過(guò)指定數(shù)據(jù)庫(kù)名稱(chēng)來(lái)過(guò)濾輸出內(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)類(lèi)似“mysqlcheck -c/-r”，對(duì)檢查和修復(fù)MyISAM 存儲(chǔ)引擎的表，但只能對(duì)MyISAM 存儲(chǔ)引擎的索引文件有效
????myisampack
? ??Usage: myisampack [OPTIONS] filename ...
? ??對(duì)MyISAM 表進(jìn)行壓縮處理，以縮減占用存儲(chǔ)空間，一般主要用在歸檔備份的場(chǎng)景下，而且壓縮后的MyISAM 表會(huì)變成只讀，不能進(jìn)行任何修改操作。
? ??mysqlhotcopy
? ??Usage: mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]
? ??僅能在Unix/Linux 環(huán)境下使用。他的主要功能就是對(duì)MySQL 中的MyISAM 存儲(chǔ)引擎的表進(jìn)行在線(xiàn)備份操作，其備份操作實(shí)際上就是通過(guò)對(duì)數(shù)據(jù)庫(kù)中的表進(jìn)行加鎖，然后復(fù)制其結(jié)構(gòu)，數(shù)據(jù)和索引文件來(lái)完成備份操作
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ù)倉(cāng)庫(kù)系統(tǒng)
????????????????2、因?yàn)闆](méi)有redo、沒(méi)有undo，因此進(jìn)行insert的時(shí)候，速度相對(duì)較快
? ??MyISAM 支持以下三種類(lèi)型的索引：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）中，通過(guò)innodb_data_file_path 來(lái)指定，增加數(shù)據(jù)文件需要停機(jī)重啟,Innodb 的undo 信息和其他一些元數(shù)據(jù)信息都是存放在共享表空間里面的。另外一種是獨(dú)享表空間，也就是每個(gè)表的數(shù)據(jù)和索引被存放在一個(gè)單獨(dú)的.ibd 文件中。
? ??????共享表空間增加數(shù)據(jù)文件的操作比較簡(jiǎn)單， 只需要在innodb_data_file_path 參數(shù)后面按照標(biāo)準(zhǔn)格式設(shè)置好文件路徑和相關(guān)屬性即可，不過(guò)這里有一點(diǎn)需要注意的，就是Innodb 在創(chuàng)建新數(shù)據(jù)文件的時(shí)候是不會(huì)創(chuàng)建目錄的，如果指定目錄不存在，則會(huì)報(bào)錯(cuò)并無(wú)法啟動(dòng)
? ??2、日志文件
? ??????由于Innodb 是事務(wù)安全的存儲(chǔ)引擎，所以系統(tǒng)Crash 對(duì)他來(lái)說(shuō)并不能造成非常嚴(yán)重的損失，由于有redo 日志的存在，有checkpoint 機(jī)制的保護(hù)，Innodb 完全可以通過(guò)redo 日志將數(shù)據(jù)庫(kù)Crash 時(shí)刻已經(jīng)完成但還沒(méi)有來(lái)得及將數(shù)據(jù)寫(xiě)入磁盤(pán)的事務(wù)恢復(fù)，也能夠?qū)⑺胁糠滞瓿刹⒁呀?jīng)寫(xiě)入磁盤(pán)的未完成事務(wù)回滾并將數(shù)據(jù)還原。
? ??????Innodb 的所有參數(shù)基本上都帶有前綴“innodb_”。
????NDB Cluster存儲(chǔ)引擎
? ??????NDB 存儲(chǔ)引擎也叫NDB Cluster 存儲(chǔ)引擎，主要用于MySQL Cluster 分布式集群環(huán)境，Cluster 是MySQL 從5.0 版本才開(kāi)始提供的新功能。
? ??????一般來(lái)說(shuō)，一個(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)（后面簡(jiǎn)稱(chēng)為SQL 節(jié)點(diǎn)），也就是我們常說(shuō)的Mysql Server：主要負(fù)責(zé)實(shí)現(xiàn)一個(gè)數(shù)據(jù)庫(kù)在存儲(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 配置文件中，也可以通過(guò)啟動(dòng)命令行來(lái)指定。
? ??????c) Storage 層的NDB 數(shù)據(jù)節(jié)點(diǎn)，也就是上面說(shuō)的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ǔ)引擎可以簡(jiǎn)單的理解為其功能就是實(shí)現(xiàn)了對(duì)結(jié)構(gòu)相同的MyISAM 表，通過(guò)一些特殊的包裝對(duì)外提供一個(gè)單一的訪(fǎng)問(wèn)入口，以達(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)上比較簡(jiǎn)單，相關(guān)權(quán)限信息主要存儲(chǔ)在幾個(gè)被稱(chēng)為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)然，如果我們通過(guò)GRANT，REVOKE 或者DROP USER 命令來(lái)修改相關(guān)權(quán)限，則不需要手工執(zhí)行FLUSH PRIVILEGES 命令，因?yàn)橥ㄟ^(guò)GRANT，REVOKE 或者DROP USER 命令所做的權(quán)限修改在修改系統(tǒng)表的同時(shí)也會(huì)更新內(nèi)存結(jié)構(gòu)中的權(quán)限信息。
? ??權(quán)限授予與去除：要為某個(gè)用戶(hù)授權(quán)，可以使用GRANT 命令，要去除某個(gè)用戶(hù)已有的權(quán)限則使用REVOKE命令。當(dāng)給某個(gè)用戶(hù)授權(quán)的時(shí)候，不僅需要指定用戶(hù)名，同時(shí)還要指定來(lái)訪(fǎng)主機(jī)。
? ??查看某個(gè)用戶(hù)目前擁有的權(quán)限可以通過(guò)“SHOW GRANTS FOR 'username'@'hostname'”和查詢(xún)mysql.user里面的權(quán)限信息。
? ???創(chuàng)建用戶(hù)：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
? ???授權(quán)：GRANT privileges ON databasename.tablename TO 'username'@'host'
????????????用戶(hù)的操作權(quán)限，如SELECT，INSERT，UPDATE等，如果要授予所的權(quán)限則使用ALL。 如果想讓該用戶(hù)可以授權(quán)，用以下命令:
????????????????GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
? ??設(shè)置與更改用戶(hù)密碼:SET PASSWORD FOR 'username'@'host' =PASSWORD('newpassword');
? ??撤銷(xiāo)用戶(hù)權(quán)限：:REVOKE privilege ON databasename.tablename FROM 'username'@'host';
? ??刪除用戶(hù)：DROP USER 'username'@'host';
9.數(shù)據(jù)庫(kù)備份
? ??mysqldump邏輯備份
????”--single-transaction”選項(xiàng)，可以達(dá)到備份數(shù)據(jù)的一致性和完整性
? ??“--lock-tables”和“--lock-all-tables”，讓數(shù)據(jù)庫(kù)在備份過(guò)程中僅提供數(shù)據(jù)的查詢(xún)服務(wù)，鎖定寫(xiě)入的服務(wù)，來(lái)使數(shù)據(jù)暫時(shí)處于一個(gè)一致的不會(huì)被修改的狀態(tài)
? ??“--master-data[=value]”會(huì)將當(dāng)前MySQL 使用到binlog 日志的名稱(chēng)和位置記錄到dump 文件中。這個(gè)選項(xiàng)在實(shí)施slave 的在線(xiàn)搭建的時(shí)候是非常有用的，即使不是進(jìn)行在線(xiàn)搭建slave，也可以在某些情況下做恢復(fù)的過(guò)程中通過(guò)備份的binlog 做進(jìn)一步恢復(fù)操作。
? ? "--no-data”僅僅dump 數(shù)據(jù)庫(kù)結(jié)構(gòu)創(chuàng)建腳本?
? ? mysqldump邏輯恢復(fù)?
? ??可以通過(guò)在mysql 中執(zhí)行“source /path/backup.sql”或者“\. /path/backup.sql”來(lái)進(jìn)行恢復(fù)?；蛟诿钚惺褂胢ysql -uusername -p < backup.sql
? ??數(shù)據(jù)庫(kù)物理課備份
? ? ?數(shù)據(jù)庫(kù)的物理備份就是對(duì)數(shù)據(jù)庫(kù)的物理對(duì)象所做的備份。
? ? 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ù)庫(kù)目錄下的所有Innodb 存儲(chǔ)引擎表的“.frm”文件“.idb”文件，redo文件
? ??NDB Cluster 存儲(chǔ)引擎1、元數(shù)據(jù)（Metadata）2、表數(shù)據(jù)（Table Records）3、事務(wù)日志數(shù)據(jù)（Transaction Log）：
? ??我們也可以通過(guò)登錄數(shù)據(jù)庫(kù)中手工加鎖，然后再通過(guò)操作系統(tǒng)的命令來(lái)復(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ǔ)引擎鎖定問(wèn)題的時(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ǔ)引擎的表只存放必要的信息，控制字段類(lèi)型；
? ??????d) 利用合適的機(jī)會(huì)優(yōu)化MyISAM 表數(shù)據(jù)文件；
????2、分離能并行的操作
? ? ? ? 配置是Concurrent Insert（并發(fā)插入），MyISAM 存儲(chǔ)引擎有一個(gè)控制是否打開(kāi)Concurrent Insert 功能的參數(shù)選項(xiàng)：concurrent_insert，可以設(shè)置為0，1 或者2。三個(gè)值的具體說(shuō)明如下：
? ??????a) set global concurrent_insert=2（always）2，無(wú)論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)，無(wú)論MyISAM 存儲(chǔ)引擎的表數(shù)據(jù)文件的中間部分是否存在因?yàn)閯h除數(shù)據(jù)而留下的空閑空間，都不允許Concurrent Insert。
? ? 3、合理利用讀寫(xiě)優(yōu)先級(jí)
? ??????如果我們的系統(tǒng)是一個(gè)以讀為主，而且要優(yōu)先保證查詢(xún)性能的話(huà)，我們可以通過(guò)設(shè)置系統(tǒng)參數(shù)選項(xiàng)low_priority_updates=1，如果我們的系統(tǒng)需要有限保證數(shù)據(jù)寫(xiě)入的性能的話(huà)，則可以不用設(shè)置low_priority_updates參數(shù)
11.innodb鎖優(yōu)化
? ??Innodb 存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來(lái)的性能損耗可能比表級(jí)鎖定會(huì)要更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM 的表級(jí)鎖定的
? ??a) 盡可能讓所有的數(shù)據(jù)檢索都通過(guò)索引來(lái)完成，從而避免Innodb 因?yàn)闊o(wú)法通過(guò)索引鍵加鎖而升級(jí)為表級(jí)鎖定；
? ??b) 合理設(shè)計(jì)索引，讓Innodb 在索引鍵上面加鎖的時(shí)候盡可能準(zhǔn)確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他Query 的執(zhí)行；
? ??c) 盡可能減少基于范圍的數(shù)據(jù)檢索過(guò)濾條件，避免因?yàn)殚g隙鎖帶來(lái)的負(fù)面影響而鎖定了不該鎖定的記錄；
? ??d) 盡量控制事務(wù)的大小，減少鎖定的資源量和鎖定時(shí)間長(zhǎng)度；
? ??e) 在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級(jí)別的事務(wù)隔離，以減少M(fèi)ySQL 因?yàn)閷?shí)現(xiàn)事務(wù)隔離級(jí)別所帶來(lái)的附加成本；
? ??減少死鎖產(chǎn)生概率建議：
? ??a) 類(lèi)似業(yè)務(wù)模塊中，盡可能按照相同的訪(fǎng)問(wèn)順序來(lái)訪(fǎng)問(wèn)，防止產(chǎn)生死鎖；
? ??b) 在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；
? ??c) 對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級(jí)鎖定顆粒度，通過(guò)表級(jí)鎖定來(lái)減少死鎖產(chǎn)生的概率；
? ??系統(tǒng)鎖定爭(zhēng)用情況查詢(xún)：show status like 'table%';
? ??Table_locks_immediate：產(chǎn)生表級(jí)鎖定的次數(shù)；
? ??Table_locks_waited：出現(xiàn)表級(jí)鎖定爭(zhēng)用而發(fā)生等待的次數(shù)，如果Table_locks_waited 狀態(tài)值比較高，那么說(shuō)明系統(tǒng)中表級(jí)鎖定爭(zhēng)用現(xiàn)象比較嚴(yán)重
? ? innodb鎖定爭(zhēng)用情況查詢(xún)：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í)間長(zhǎng)度；
????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ǔ)句優(yōu)化基本思路和原則
? ??1. 優(yōu)化更需要優(yōu)化的Query；
? ??????一般來(lái)說(shuō)，高并發(fā)低消耗（相對(duì)）的Query 對(duì)整個(gè)系統(tǒng)的影響遠(yuǎn)比低并發(fā)高消耗的Query 大(io次數(shù)高cpu消耗大)。
????2. 定位優(yōu)化對(duì)象的性能瓶頸；
? ??????PROFILING 功能很清楚的找出一個(gè)Query 的瓶頸所在。
? ??????1、 開(kāi)啟 profiling 參數(shù)set profiling=1;????????? 2、 執(zhí)行 Query
? ? ? ? 3、show profiles;獲取系統(tǒng)中保存的所有 Query 的 profile 概要信息
? ? ? ? 4、 針對(duì)單個(gè) Query 獲取詳細(xì)的 profile 信息show PROFILE cpu ,block io for query 116
????3. 從Explain 入手；
? ? ? ?Explain 功能中給我們展示的各種信息的解釋?zhuān)?/p>
? ??????ID：Query Optimizer 所選定的執(zhí)行計(jì)劃中查詢(xún)的序列號(hào)；
? ??????Select_type：所使用的查詢(xún)類(lèi)型，主要有以下這幾種查詢(xún)類(lèi)型
????????????◇ DEPENDENT SUBQUERY：子查詢(xún)中內(nèi)層的第一個(gè)SELECT，依賴(lài)于外部查詢(xún)的結(jié)果集；◇ DEPENDENT UNION：子查詢(xún)中的UNION，且為UNION 中從第二個(gè)SELECT 開(kāi)始的后面所有SELECT，同樣依賴(lài)于外部查詢(xún)的結(jié)果集；◇ PRIMARY：子查詢(xún)中的最外層查詢(xún)，注意并不是主鍵查詢(xún)；◇ SIMPLE：除子查詢(xún)或者UNION 之外的其他查詢(xún)；◇ SUBQUERY：子查詢(xún)內(nèi)層查詢(xún)的第一個(gè)SELECT，結(jié)果不依賴(lài)于外部查詢(xún)結(jié)果集；◇ UNCACHEABLE SUBQUERY：結(jié)果集無(wú)法緩存的子查詢(xún)；◇ UNION：UNION 語(yǔ)句中第二個(gè)SELECT 開(kāi)始的后面所有SELECT，第一個(gè)SELECT 為PRIMARY◇ UNION RESULT：UNION 中的合并結(jié)果；
? ??????Table：顯示這一步所訪(fǎng)問(wèn)的數(shù)據(jù)庫(kù)中的表的名稱(chēng)；
? ??????Type：告訴我們對(duì)all：全表掃描表所使用的訪(fǎng)問(wèn)方式
????????????◇ const：讀常量，且最多只會(huì)有一條記錄匹配，由于是常量，所以實(shí)際上只需要讀一次；◇ eq_ref：最多只會(huì)有一條匹配結(jié)果，一般是通過(guò)主鍵或者唯一鍵索引來(lái)訪(fǎng)問(wèn)；◇ fulltext：◇ index：全索引掃描；◇ index_merge：查詢(xún)中同時(shí)使用兩個(gè)（或更多）索引，然后對(duì)索引結(jié)果進(jìn)行merge 之后再讀取表數(shù)據(jù)；◇ index_subquery：子查詢(xún)中的返回結(jié)果字段組合是一個(gè)索引（或索引組合），但不是一個(gè)主鍵或者唯一索引；◇ rang：索引范圍掃描；◇ ref：Join 語(yǔ)句中被驅(qū)動(dòng)表索引引用查詢(xún)；◇ ref_or_null：與ref 的唯一區(qū)別就是在使用索引引用查詢(xún)之外再增加一個(gè)空值的查詢(xún)；◇ system：系統(tǒng)表，表中只有一行數(shù)據(jù)；◇ unique_subquery：子查詢(xún)中的返回結(jié)果字段組合是主鍵或者唯一約束
????? ??Possible_keys：該查詢(xún)可以利用的索引. 如果沒(méi)有任何索引可以使用，就會(huì)顯示成null
? ??????Key：MySQL Query Optimizer 從possible_keys 中所選擇使用的索引；
? ??????Key_len：被選中使用索引的索引鍵長(zhǎng)度；
? ??????Ref：列出是通過(guò)常量（const），還是某個(gè)表的某個(gè)字段（如果是join）來(lái)過(guò)濾（通過(guò)key）的；
? ? ? ? Rows：MySQL Query Optimizer 通過(guò)系統(tǒng)收集到的統(tǒng)計(jì)信息估算出來(lái)的結(jié)果集記錄條數(shù)；
? ??????Extra：查詢(xún)中每一步實(shí)現(xiàn)的額外細(xì)節(jié)信息? ? ? ? ? ?
?????????????◇ Distinct：查找distinct 值，所以當(dāng)mysql 找到了第一條匹配的結(jié)果后，將停止該值的查詢(xún)而轉(zhuǎn)為后面其他值的查詢(xún)；◇ Full scan on NULL key：子查詢(xún)中的一種優(yōu)化方式，主要在遇到無(wú)法通過(guò)索引訪(fǎng)問(wèn)null值的使用使用；◇ Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通過(guò)收集到的統(tǒng)計(jì)信息判斷出不可能存在結(jié)果；◇ No tables：Query 語(yǔ)句中使用FROM DUAL 或者不包含任何FROM 子句；◇ Not exists：在某些左連接中MySQL Query Optimizer 所通過(guò)改變?cè)蠶uery 的組成而使用的優(yōu)化方法，可以部分減少數(shù)據(jù)訪(fǎng)問(wèn)次數(shù)；◇ Range checked for each record (index map: N)：通過(guò)MySQL 官方手冊(cè)的描述，當(dāng)MySQL Query Optimizer 沒(méi)有發(fā)現(xiàn)好的可以使用的索引的時(shí)候，如果發(fā)現(xiàn)如果來(lái)自前面的表的列值已知，可能部分索引可以使用。對(duì)前面的表的每個(gè)行組合，MySQL 檢查是否可以使用range 或index_merge 訪(fǎng)問(wèn)方法來(lái)索取行?！?Select tables optimized away：當(dāng)我們使用某些聚合函數(shù)來(lái)訪(fǎng)問(wèn)存在索引的某個(gè)字段的時(shí)候，MySQL Query Optimizer 會(huì)通過(guò)索引而直接一次定位到所需的數(shù)據(jù)行完成整個(gè)查詢(xún)。當(dāng)然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者M(jìn)AX（）的時(shí)候；◇ Using filesort：當(dāng)我們的Query 中包含ORDER BY 操作，而且無(wú)法利用索引完成排序操作的時(shí)候，MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來(lái)實(shí)現(xiàn)?！?Using index：所需要的數(shù)據(jù)只需要在Index 即可全部獲得而不需要再到表中取數(shù)據(jù)；◇ Using index for group-by：數(shù)據(jù)訪(fǎng)問(wèn)和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 。主要常見(jiàn)于GROUP BY 和ORDER BY 等操作中。◇ Using where：如果我們不是讀取表的所有數(shù)據(jù)，或者不是僅僅通過(guò)索引就可以獲取所有需要的數(shù)據(jù)，則會(huì)出現(xiàn)Using where 信息；◇ Using where with pushed condition：這是一個(gè)僅僅在NDBCluster 存儲(chǔ)引擎中才會(huì)出現(xiàn)的信息，而且還需要通過(guò)打開(kāi)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 都是通過(guò)嵌套循環(huán)來(lái)實(shí)現(xiàn)的。
? ? ? b）只取出自己需要的Columns。對(duì)于任何Query，返回的數(shù)據(jù)都是需要通過(guò)網(wǎng)絡(luò)數(shù)據(jù)包傳回給客戶(hù)端，如果取出的Column 越多，需要傳輸?shù)臄?shù)據(jù)量自然會(huì)越大。如果是需要排序的Query 來(lái)說(shuō)，影響就更大了。在MySQL 中存在兩種排序算法，一種是在MySQL4.1 之前的老算法，實(shí)現(xiàn)方式是先將需要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出，然后在我們所設(shè)定的排序區(qū)（通過(guò)參數(shù)sort_buffer_size 設(shè)定）中進(jìn)行排序，完成排序之后再次通過(guò)行指針信息取出所需要的Columns，也就是說(shuō)這種算法需要訪(fǎng)問(wèn)兩次數(shù)據(jù)。第二種排序算法是從MySQL4.1 版本開(kāi)始使用的改進(jìn)算法，一次性將所需要的Columns 全部取出，在排序區(qū)中進(jìn)行排序后直接將數(shù)據(jù)返回給請(qǐng)求客戶(hù)端。改行算法只需要訪(fǎng)問(wèn)一次數(shù)據(jù)，減少了大量的隨機(jī)IO，極大的提高了帶有排序的Query 語(yǔ)句的效率。但是，這種改進(jìn)后的排序算法需要一次性取出并緩存的數(shù)據(jù)比第一種算法要多很多，如果我們將并不需要的Columns 也取出來(lái)，就會(huì)極大的浪費(fèi)排序過(guò)程所需要的內(nèi)存。在MySQL4.1 之后的版本中，我們可以通過(guò)設(shè)置max_length_for_sort_data 參數(shù)大小來(lái)控制MySQL 選擇第一種排序算法還是第二種排序算法。當(dāng)所取出的Columns 的單條記錄總大小max_length_for_sort_data 設(shè)置的大小的時(shí)候，MySQL 就會(huì)選擇使用第一種排序算法，反之，則會(huì)選擇第二種優(yōu)化后的算法。為了盡可能提高排序性能，我們自然是更希望使用第二種排序算法，所以在Query 中僅僅取出我們所需要的Columns 是非常有必要的。
? ? c)僅僅使用最有效的過(guò)濾條件。因?yàn)椴煌乃饕I長(zhǎng)度，如果多個(gè)索引鍵都能與表一一對(duì)應(yīng)，那么應(yīng)該只使用鍵長(zhǎng)度較短的索引作為過(guò)濾條件。
? ? d)盡可能避免復(fù)雜的Join 和子查詢(xún)。Query 語(yǔ)句所涉及到的越復(fù)雜的Join 語(yǔ)句，所需要鎖定的資源也就越多，所表越多，所需要鎖定的資源就越多。也就是說(shuō)，阻塞的其他線(xiàn)程也就越多。如果我們將比較復(fù)雜的Query 語(yǔ)句分拆成多個(gè)較為簡(jiǎn)單的Query 語(yǔ)句分步執(zhí)行，每次鎖定的資源也就會(huì)少很多，所阻塞的其他線(xiàn)程也要少一些。（犧牲響應(yīng)時(shí)間提高整體處理能力）
? ? 4.合理利用索引
? ??????如何判定是否需要?jiǎng)?chuàng)建索引?
? ??????????◆ 較頻繁的作為查詢(xún)條件的字段應(yīng)該創(chuàng)建索引；
? ??????????◆ 唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢(xún)條件；
? ??????????◆ 更新非常頻繁的字段不適合創(chuàng)建索引；
? ??????????◆ 不會(huì)出現(xiàn)在WHERE 子句中的字段不該創(chuàng)建索引；
? ? ????使用組合索引
? ? ? ? ? ?只要不是其中如果某個(gè)過(guò)濾字段在大多數(shù)場(chǎng)景下都能過(guò)濾出90%以上的數(shù)據(jù)，而且其他的過(guò)濾字段會(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ì)造成他并沒(méi)有選擇一個(gè)真正最優(yōu)的索引而選擇了其他查詢(xún)效率較低的索引。這時(shí)可以使用 FORCE INDEX(index_name)強(qiáng)制使用索引。
? ?5. MySQL 中索引的限制
? ??????1. MyISAM 存儲(chǔ)引擎索引鍵長(zhǎng)度總和不能超過(guò)1000 字節(jié)；
????????2. BLOB 和TEXT 類(lèi)型的列只能創(chuàng)建前綴索引；
????????3. MySQL 目前不支持函數(shù)索引；
????????4. 使用不等于（!= 或者<>）的時(shí)候MySQL 無(wú)法使用索引；
????????5. 過(guò)濾字段使用了函數(shù)運(yùn)算后（如abs(column)），MySQL 無(wú)法使用索引；
????????6. Join 語(yǔ)句中Join 條件字段類(lèi)型不一致的時(shí)候MySQL 無(wú)法使用索引；
????????7. 使用LIKE 操作的時(shí)候如果條件以通配符開(kāi)始（ '%abc...'）MySQL 無(wú)法使用索引；
????????8. 使用非等值查詢(xún)的時(shí)候MySQL 無(wú)法使用Hash 索引；
13.Join 的實(shí)現(xiàn)原理及優(yōu)化思路
? ??在MySQL 中只有Nested Loop Join算法，實(shí)際上就是通過(guò)驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條的通過(guò)該結(jié)果集中的數(shù)據(jù)作為過(guò)濾條件到下一個(gè)表中查詢(xún)數(shù)據(jù)，然后合并結(jié)果。
? ? 當(dāng)join類(lèi)型為all,index,rang,index_merge時(shí)，會(huì)使用Join Buffer(緩存)結(jié)果集，可以通過(guò)join_buffer_size 參數(shù)設(shè)置Join buffer的大小
? ??Join 語(yǔ)句的優(yōu)化
? ??????1. 盡可能減少Join 語(yǔ)句中的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)無(wú)法保證被驅(qū)動(dòng)表的Join 條件字段被索引且內(nèi)存資源充足的前提下，不要太吝惜JoinBuffer 的設(shè)置
? ? ? ? 4.保證Join 語(yǔ)句中被驅(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)有如下兩種類(lèi)型：
? ? ? ? a)一種是通過(guò)有序索引而直接取得有序的數(shù)據(jù)
? ??????????利用索引實(shí)現(xiàn)數(shù)據(jù)排序的方法是MySQL 中實(shí)現(xiàn)結(jié)果集排序的最佳做法，可以完全避免因?yàn)榕判蛴?jì)算所帶來(lái)的資源消耗。
? ? ? ? b)通過(guò)MySQL 的排序算法將存儲(chǔ)引擎中返回的數(shù)據(jù)進(jìn)行排序
? ? ? ? ? ?排序有兩種實(shí)現(xiàn)：1、取出滿(mǎn)足過(guò)濾條件的用于排序條件的字段以及可以直接定位到行數(shù)據(jù)的行指針信息，在SortBuffer 中進(jìn)行實(shí)際的排序操作，然后利用排好序之后的數(shù)據(jù)根據(jù)行指針信息返回表中取得客戶(hù)端請(qǐng)求的其他字段的數(shù)據(jù)????2、根據(jù)過(guò)濾條件一次取出排序字段以及客戶(hù)端請(qǐng)求的所有其他字段的數(shù)據(jù)，并將不需要排序的字段存放在一塊內(nèi)存區(qū)域中，然后在Sort Buffer 中將排序字段和行指針信息進(jìn)行排序，最后再利用排序后的行指針與存放在內(nèi)存區(qū)域中和其他字段一起的行指針信息進(jìn)行匹配合并結(jié)果集
? ??ORDER BY 的優(yōu)化：
? ? ? ? a)盡可能根據(jù)索引排序
? ? ? ? b)當(dāng)我們無(wú)法避免排序操作的時(shí)候，可以配置一下參數(shù)進(jìn)行優(yōu)化：
? ? ? ? ? ? 1.加大max_length_for_sort_data 參數(shù)的設(shè)置
? ??????????????????在MySQL 中，決定使用第一種老式的排序算法還是新的改進(jìn)算法的依據(jù)是通過(guò)參數(shù)max_length_for_sort_data 來(lái)決定的。當(dāng)我們所有返回字段的最大長(zhǎng)度小于這個(gè)參數(shù)值的時(shí)候，MySQL 就會(huì)選擇改進(jìn)后的排序算法，反之，則選擇老式的算法（需多訪(fǎng)問(wèn)一次數(shù)據(jù)庫(kù)）
? ? ? ? ? ? 2..去掉不必要的返回字段
? ??????????3. 增大sort_buffer_size 參數(shù)設(shè)置
? ??????????????????讓MySQL可以盡量減少在排序過(guò)程中對(duì)需要排序的數(shù)據(jù)進(jìn)行分段，因?yàn)檫@樣會(huì)造成MySQL 不得不使用臨時(shí)表來(lái)進(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 完全利用索引掃描來(lái)實(shí)現(xiàn)GROUP BY 的時(shí)候，并不需要掃描所有滿(mǎn)足條件的索引鍵即可完成操作得出結(jié)果。在執(zhí)行計(jì)劃的Extra 信息中有信息顯示“Using index for group-by”，實(shí)際上這就是告訴我們，MySQL Query Optimizer 通過(guò)使用松散索引掃描來(lái)實(shí)現(xiàn)了我們所需要的GROUP BY 操作。 要利用到松散索引掃描實(shí)現(xiàn)GROUP BY，需要至少滿(mǎn)足以下幾個(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í)候，讀取所有滿(mǎn)足條件的索引鍵，然后再根據(jù)讀取的數(shù)據(jù)來(lái)完成GROUP BY 操作得到相應(yīng)結(jié)果。執(zhí)行計(jì)劃的Extra 信息中顯示“Using where; Using index”
? ? ? ? ? ?c)使用臨時(shí)表實(shí)現(xiàn)GROUP BY。當(dāng)MySQL QueryOptimizer 無(wú)法找到合適的索引可以利用的時(shí)候，就不得不先讀取需要的數(shù)據(jù)，然后通過(guò)臨時(shí)表來(lái)完成GROUP BY 操作。執(zhí)行計(jì)劃的Extra 信息中顯示“Using where; Using index; Using temporary; Using filesort”
? ??針對(duì)GROUP BY有兩種優(yōu)化思路：
? ??????1. 盡可能讓MySQL 可以利用索引來(lái)完成GROUP BY 操作
? ??????2. 當(dāng)無(wú)法使用索引完成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 到磁盤(pán)上面再進(jìn)行操作，這時(shí)候的排序分組操作性能將是成數(shù)量級(jí)的下降；
16.DISTINCT 的實(shí)現(xiàn)與優(yōu)化
? ? ? ?DISTINCT 實(shí)際上和GROUP BY 的操作非常相似，只不過(guò)是在GROUP BY 之后的每組中只取出一條記錄而已。但是，和GROUP BY 有一點(diǎn)差別的是，DISTINCT 并不需要進(jìn)行排序。也就是說(shuō)，在僅僅只是DISTINCT 操作的Query 如果無(wú)法僅僅利用索引完成操作的時(shí)候，MySQL 會(huì)利用臨時(shí)表來(lái)做一次數(shù)據(jù)的“緩存”，但是不會(huì)對(duì)臨時(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ù)類(lèi)型
? ??1. 通過(guò)選用更“小”的數(shù)據(jù)類(lèi)型減少存儲(chǔ)空間，使查詢(xún)相同數(shù)據(jù)需要的IO 資源降低；
? ??2. 通過(guò)合適的數(shù)據(jù)類(lèi)型加速數(shù)據(jù)的比較；
????????對(duì)于數(shù)字類(lèi)型，這里分別列出了整數(shù)類(lèi)型和小數(shù)類(lèi)型，也就是浮點(diǎn)數(shù)類(lèi)型。實(shí)際上，還有一類(lèi)通過(guò)二進(jìn)制格式以字符串來(lái)存放的數(shù)字類(lèi)型如DECIMAL(DEC)[(M[,D])]，NUMERIC[(M[,D])]，由于其存放長(zhǎng)度主要通過(guò)其定義時(shí)候的的M 所決定，M 定義為多大，則實(shí)際存放就有多長(zhǎng)。M 代表整個(gè)位數(shù)長(zhǎng)度，而D 則表示小數(shù)點(diǎn)后的位數(shù)，默認(rèn)M 為10，D 為0。一般來(lái)說(shuō)，主要用在固定精度的場(chǎng)合，由于其存放長(zhǎng)度較大，而且考慮到這種數(shù)據(jù)完全可以變化形式以整數(shù)存放，所以并不是特別推薦。
????????時(shí)間存儲(chǔ)格式總類(lèi)并不是太多，我們常用的主要就是DATETIME，DATE 和TIMESTAMP，從存儲(chǔ)空間來(lái)看TIMESTAMP 最少，四個(gè)字節(jié)，而其他兩種數(shù)據(jù)類(lèi)型都是八個(gè)字節(jié)，多了一倍。而TIMESTAMP 的缺點(diǎn)在于他只能存儲(chǔ)從1970 年之后的時(shí)間，而另外兩種時(shí)間類(lèi)型可以存放最早從1001 年開(kāi)始的時(shí)間。但是只要我們不需要使用1970 年之前的時(shí)間，最好盡量使用TIMESTAMP 來(lái)減少存儲(chǔ)空間的占用。
????????CHAR[(M)]類(lèi)型屬于靜態(tài)長(zhǎng)度類(lèi)型，存放長(zhǎng)度完全以字符數(shù)來(lái)計(jì)算，所以最終的存儲(chǔ)長(zhǎng)度是基于字符集的。VARCHAR[(M)]屬于動(dòng)態(tài)存儲(chǔ)長(zhǎng)度類(lèi)型，僅存占用實(shí)際存儲(chǔ)數(shù)據(jù)的長(zhǎng)度。TINYTEXT，TEXT，MEDIUMTEXT 和LONGTEXT 這四種類(lèi)型同屬于一種存儲(chǔ)方式，都是動(dòng)態(tài)存儲(chǔ)長(zhǎng)度類(lèi)型，不同的僅僅是最大長(zhǎng)度的限制。
18.MySQL Server 性能優(yōu)化
? ??源碼包的編譯參數(shù)推薦
19.MySQL 日志設(shè)置優(yōu)化
? ??在默認(rèn)情況下，系統(tǒng)僅僅打開(kāi)錯(cuò)誤日志，關(guān)閉了其他所有日志，但是在一般稍微重要一點(diǎn)的實(shí)際應(yīng)用場(chǎng)景中，都至少需要打開(kāi)二進(jìn)制日志，因?yàn)檫@是MySQL很多存儲(chǔ)引擎進(jìn)行增量備份的基礎(chǔ)，也是MySQL 實(shí)現(xiàn)復(fù)制的基本條件。一般情況下，在生產(chǎn)系統(tǒng)中最好關(guān)閉查詢(xú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ù)過(guò)程中容納二進(jìn)制日志SQL 語(yǔ)句的緩存大小，注意，是每個(gè)Client 都可以分配設(shè)置大小的binlog cache 空間?？梢酝ㄟ^(guò)MySQL 的以下兩個(gè)狀態(tài)變量來(lái)判斷當(dāng)前的binlog_cache_size 的狀況：Binlog_cache_use 和Binlog_cache_disk_use。
? ? ? ? max_binlog_cache_size：和"binlog_cache_size"相對(duì)應(yīng)，但是所代表的是binlog 能夠使用的最大cache 內(nèi)存大小。
? ??????max_binlog_size：Binlog 日志最大值，一般來(lái)說(shuō)設(shè)置為512M 或者1G，但不能超過(guò)1G。
? ??????sync_binlog：這個(gè)參數(shù)是對(duì)于MySQL 系統(tǒng)來(lái)說(shuō)是至關(guān)重要的，他不僅影響到Binlog 對(duì)MySQL 所帶來(lái)的性能損耗，而且還影響到MySQL 中數(shù)據(jù)的完整性。對(duì)于“sync_binlog”參數(shù)的各種設(shè)置的說(shuō)明如下： 1)sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL 不做fsync 之類(lèi)的磁盤(pán)同步指令刷新binlog_cache 中的信息到磁盤(pán)，而讓Filesystem 自行決定什么時(shí)候來(lái)做同步，或者cache 滿(mǎn)了之后才同步到磁盤(pán)。2）sync_binlog=n，當(dāng)每進(jìn)行n 次事務(wù)提交之后，MySQL 將進(jìn)行一次fsync 之類(lèi)的磁盤(pán)同步指令來(lái)將binlog_cache 中的數(shù)據(jù)強(qiáng)制寫(xiě)入磁盤(pán)。
????Slow Query Log 相關(guān)參數(shù)及使用建議：
? ? ? ? 查看慢查詢(xún)相關(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)生連接請(qǐng)求的等待，從而限制了相應(yīng)的并發(fā)量。所以一般來(lái)說(shuō)，只要MySQL 主機(jī)性能允許，都是將該參數(shù)設(shè)置的盡可能大一點(diǎn)。一般來(lái)說(shuō)500 到800 左右是一個(gè)比較合適的參考值
? ??????max_user_connections：每個(gè)用戶(hù)允許的最大連接數(shù)，針對(duì)于單個(gè)用戶(hù)的連接限制。在一般情況下我們可能都較少使用這個(gè)限制
? ??????net_buffer_length：網(wǎng)絡(luò)包傳輸中，傳輸消息之前的net buffer 初始化大??；這個(gè)參數(shù)主要可能影響的是網(wǎng)絡(luò)傳輸?shù)男?，由于該參?shù)所設(shè)置的只是消息緩沖區(qū)的初始化大小，所以造成的影響主要是當(dāng)我們的每次消息都很大的時(shí)候MySQL 總是需要多次申請(qǐng)擴(kuò)展該緩沖區(qū)大小。系統(tǒng)默認(rèn)大小為16KB，一般來(lái)說(shuō)可以滿(mǎn)足大多數(shù)場(chǎng)景，當(dāng)然如果我們的查詢(xún)都是非常小，每次網(wǎng)絡(luò)傳輸量都很少，而且系統(tǒng)內(nèi)存又比較緊缺的情況下，也可以適當(dāng)將該值降低到8KB。
? ??????max_allowed_packet：在網(wǎng)絡(luò)傳輸中，一次傳消息輸量的最大值。這個(gè)參數(shù)net_buffer_length 相對(duì)應(yīng)，只不過(guò)是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 的連接請(qǐng)求等待隊(duì)列中允許存放的最大連接請(qǐng)求數(shù)。
?????相關(guān)的系統(tǒng)參數(shù)及狀態(tài)變量說(shuō)明如下：
? ??????thread_cache_size：Thread Cache 池中應(yīng)該存放的連接線(xiàn)程數(shù)。在短連接的應(yīng)用系統(tǒng)中，thread_cache_size 的值應(yīng)該設(shè)置的相對(duì)大一些
? ??????thread_stack：每個(gè)連接線(xiàn)程被創(chuàng)建的時(shí)候，MySQL 給他分配的內(nèi)存大小。當(dāng)MySQL 創(chuàng)建一個(gè)新的連接線(xiàn)程的時(shí)候，是需要給他分配一定大小的內(nèi)存堆?？臻g，以便存放客戶(hù)端的請(qǐng)求Query 以及自身的各種狀態(tài)和處理信息。
? ??????show status like 'connections'：系統(tǒng)被連接的次數(shù)?
? ??????show status like '%thread%'：當(dāng)前系統(tǒng)中連接線(xiàn)程的狀態(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 被稱(chēng)為Full Join。實(shí)際上參與Join 的每一個(gè)表都需要一個(gè)Join Buffer，所以在Join 出現(xiàn)的時(shí)候，至少是兩個(gè)。Join Buffer 的設(shè)置在MySQL 5.1.23 版本之前最大為4GB，但是從5.1.23 版本開(kāi)始，在除了Windows 之外的64 位的平臺(tái)上可以超出4BG 的限制。系統(tǒng)默認(rèn)128KB。
sort_buffer_size：系統(tǒng)中對(duì)數(shù)據(jù)進(jìn)行排序的時(shí)候使用的Buffer；Sort Buffer 同樣是針對(duì)單個(gè)Thread的，所以當(dāng)多個(gè)Thread 同時(shí)進(jìn)行排序的時(shí)候，系統(tǒng)中就會(huì)出現(xiàn)多個(gè)Sort Buffer。一般我們可以通過(guò)增大Sort Buffer 的大小來(lái)提高ORDER BY 或者是GROUP BY的處理性能。系統(tǒng)默認(rèn)大小為2MB，最大限制和Join Buffer 一樣，在MySQL 5.1.23 版本之前最大為4GB，從5.1.23 版本開(kāi)始，在除了Windows 之外的64 位的平臺(tái)上可以超出4GB 的限制。
? ??如果應(yīng)用系統(tǒng)中很少有Join 語(yǔ)句出現(xiàn)，則可以不用太在乎join_buffer_size 參數(shù)的大小設(shè)置，但是如果Join 語(yǔ)句不是很少的話(huà)，個(gè)人建議可以適當(dāng)增大join_buffer_size 的設(shè)置到1MB 左右，如果內(nèi)存充足甚至可以設(shè)置為2MB。對(duì)于sort_buffer_size 參數(shù)來(lái)說(shuō)，一般設(shè)置為2MB 到4MB 之間可以滿(mǎn)足大多數(shù)應(yīng)用的需求。
22.MyI SAM存儲(chǔ)引擎優(yōu)化
? ??MyISAM 存儲(chǔ)引擎的索引和數(shù)據(jù)是分開(kāi)存放于“.MYI”文件中，每個(gè)“.MYI”文件由文件頭和實(shí)際的索引數(shù)據(jù)?！?MYI”的文件頭中主要存放四部分信息，分別稱(chēng)為：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 被稱(chēng)為Index Block，每個(gè)Index Block 的大小并不一定相等。
? ??在“.MYI”中，Index Block 的組織形式實(shí)際上只是一種邏輯上的，并不是物理意義上的。在物理上，實(shí)際上是以File Block 的形式來(lái)存放在磁盤(pán)上面的。在Key Cache 中緩存的索引信息是以“Cache Block”的形式組織存放的，“Cache Block”是相同大小的，和“.MYI”文件物理存儲(chǔ)的Block（ File Block ） 一樣。在一條Query 通過(guò)索引檢索表數(shù)據(jù)的時(shí)候， 首先會(huì)檢查索引緩存（key_buffer_cache）中是否已經(jīng)有需要的索引信息，如果沒(méi)有，則會(huì)讀取“.MYI”文件，將相應(yīng)的索引數(shù)據(jù)讀入Key Cache 中的內(nèi)存空間中，同樣也是以Block 形式存放，被稱(chēng)為Cache Block。不過(guò)，數(shù)據(jù)的讀入并不是以Index Block 的形式來(lái)讀入，而是以File Block 的形式來(lái)讀入的。以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ù)用來(lái)設(shè)置整個(gè)MySQL 中的常規(guī)Key Cache 大小。一般來(lái)說(shuō)，如果我們的MySQL 是運(yùn)行在32 位平臺(tái)紙上，此值建議不要超過(guò)2GB 大小。如果是運(yùn)行在64 位平臺(tái)紙上則不用考慮此限制，但也最好不要超過(guò)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)的算法一樣僅僅只是通過(guò)訪(fǎng)問(wèn)頻率以及最后訪(fǎng)問(wèn)時(shí)間來(lái)通過(guò)一個(gè)唯一的鏈表實(shí)現(xiàn)，而是將其分成了兩部分。一部分用來(lái)存放使用比較頻繁的Hot Cacke Lock（Hot Chain），被成為Hot Area，另外一部分則用來(lái)存放使用不是太頻繁的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)更改但還未刷新到磁盤(pán)的Dirty Cache Block；
????????◆ Key_blocks_unused，目前未被使用的Cache Block 數(shù)目；
????????◆ Key_blocks_used，已經(jīng)使用了的Cache Block 數(shù)目；
????????◆ Key_read_requests，Cache Block 被請(qǐng)求讀取的總次數(shù)；
????????◆ Key_reads，在Cache Block 中找不到需要讀取的Key 信息后到“.MYI”文件中讀取的次數(shù)；
????????◆ Key_write_requests，Cache Block 被請(qǐng)求修改的總次數(shù)；
????????◆ Key_writes，在Cache Block 中找不到需要修改的Key 信息后到“.MYI”文件中讀入再修改的次
數(shù)；????
? ??????Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *
100%? ? ? ? ? ? //緩存使用率，如果該值過(guò)低說(shuō)明key_bufffer_size設(shè)置過(guò)大。
? ? ? ??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%的大小用來(lái)存放使用非常頻繁且更新很少的表的索引；
????????????一個(gè)Cold Cache 使用20%的大小用來(lái)存放更新很頻繁的表的索引；
????????????一個(gè)Warm Cache 使用剩下的60%空間，作為整個(gè)系統(tǒng)默認(rèn)的Key Cache；
? ??Key Cache 預(yù)加載：LOAD INDEX INTO CACHE tb_name_list ...； 對(duì)于這種啟動(dòng)后立即加載的操作，可以利用MySQL 的init_file 參數(shù)來(lái)設(shè)置相關(guān)的命令，如下：
? ??表讀取緩存優(yōu)化
????????在MySQL 中有兩種讀取數(shù)據(jù)文件的緩沖區(qū)，一種是Sequential Scan 方式（如全表掃描）掃描表數(shù)據(jù)的時(shí)候使用，另一種則是在Random Scan（如通過(guò)索引掃描）的時(shí)候使用。雖然這兩種文件讀取緩沖區(qū)并不是MyISAM 存儲(chǔ)引擎所特有的，但是由于MyISAM 存儲(chǔ)引擎并不會(huì)Cache 數(shù)據(jù)（.MYD）文件，每次對(duì)數(shù)據(jù)文件的訪(fǎng)問(wèn)都需要通過(guò)調(diào)用文件系統(tǒng)的相關(guān)指令從磁盤(pán)上面讀取物理文件。所以，每次讀取數(shù)據(jù)文件需要使用的內(nèi)存緩沖區(qū)的設(shè)置就對(duì)數(shù)據(jù)文件訪(fǎng)問(wèn)的性能非常重要了。
? ??◆ 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)存不夠。一般來(lái)說(shuō)，可以嘗試適當(dāng)調(diào)大此參數(shù)看是否能夠改善全表掃描的性能。
? ??◆ read_rnd_buffer_size，進(jìn)行Random Scan 的時(shí)候使用的Buffer；
? ??????一般來(lái)說(shuō)，read_rnd_buffer_size 值的適當(dāng)調(diào)大，對(duì)提高ORDER BY 操作的性能有一定的效果。
并發(fā)優(yōu)化
? ??由于MyISAM 存儲(chǔ)引擎的表級(jí)鎖定機(jī)制，以及讀寫(xiě)互斥的問(wèn)題，其并發(fā)寫(xiě)的性能較差。如果覺(jué)得光靠Key Cache 來(lái)緩存索引還是不夠快的話(huà)，我們還可以通過(guò)Query Cache 功能來(lái)直接緩存Query 的結(jié)果集。
? ??1. 打開(kāi)concurrent_insert 的功能，提高INSERT 操作和SELECT 之間的并發(fā)處理，大部分情況下concurrent_insert 的值都被設(shè)置為1，當(dāng)表中沒(méi)有刪除記錄留下的空余空間的時(shí)候都可以在尾部并行插入。如果我們的系統(tǒng)主要以寫(xiě)為主，尤其是有大量的INSERT 的時(shí)候。為了盡可能提高INSERT 的效率，我們可以將concurrent_insert 設(shè)置為2，也就是告訴MyISAM，不管在表中是否有刪除行留下的空余空間，都在尾部進(jìn)行并發(fā)插入，使INSERT 和SELECT 能夠互不干擾。
? ??2. 控制寫(xiě)入操作的大小，盡量讓每次寫(xiě)入操作都能夠很快的完成
? ??3. 通過(guò)犧牲讀取效率來(lái)提高寫(xiě)入效率。為了盡可能讓寫(xiě)入更快，可以適當(dāng)調(diào)整讀和寫(xiě)的優(yōu)先級(jí)別，讓寫(xiě)入操作的優(yōu)先級(jí)高于讀操作的優(yōu)先級(jí)。
????除了上面我們分析的這幾個(gè)方面之外，MyISAM還存在其他一些可以?xún)?yōu)化的地方和一些常用的優(yōu)化技巧。1. 通過(guò)OPTIMIZE 命令來(lái)整理MyISAM 表的文件? ?2. 設(shè)置myisam_max_[extra]_sort_file_size 足夠大，對(duì)REPAIR TABLE 的效率可能會(huì)有較大改善。3. 在執(zhí)行CREATE INDEX 或者REPAIR TABLE 等需要大的排序操作的之前可以通過(guò)調(diào)整session 級(jí)別的myisam_sort_buffer_size 參數(shù)值來(lái)提高排序操作的效率。4. 通過(guò)打開(kāi)delay_key_write 功能，減少I(mǎi)O 同步的操作，提高寫(xiě)入性能。5. 通過(guò)調(diào)整bulk_insert_buffer_size 來(lái)提高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ǔ)方式的差異（共享表空間）。
? ??無(wú)論是對(duì)于哪一種數(shù)據(jù)庫(kù)來(lái)說(shuō)，緩存技術(shù)都是提高數(shù)據(jù)庫(kù)性能的關(guān)鍵技術(shù)，物理磁盤(pán)的訪(fǎng)問(wèn)速度永遠(yuǎn)都會(huì)與內(nèi)存的訪(fǎng)問(wèn)速度永遠(yuǎn)都不是一個(gè)數(shù)量級(jí)的。通過(guò)緩存技術(shù)無(wú)論是在讀還是寫(xiě)方面都可以大大提高數(shù)據(jù)庫(kù)整體性能。
Innodb_buffer_pool_size 的合理設(shè)置
? ? 可以同過(guò)show status like 'Innodb_buffer_pool_%' 指令查看Buffer pool使用情況
? ? 上面的值可以看出總共有8192pages,還有6765是free狀態(tài)，只有1420個(gè)pages有數(shù)據(jù)，read請(qǐng)求47848次，其中1066次請(qǐng)求buffer pool中沒(méi)有，也就是說(shuō)有1066次是通過(guò)讀取物理磁盤(pán)獲取數(shù)據(jù)的，很容易的出read命中率大概為：(47848-1066)/47848*100%=97.7%
? ??當(dāng)然，通過(guò)上面的數(shù)據(jù)，我們還可以分析出write 命中率，可以得到發(fā)生了多少次read_ahead_rnd，多少次read_ahead_seq，發(fā)生過(guò)多少次latch，多少次因?yàn)锽uffer 空間大小不足而產(chǎn)生wait_free 等等。
? ??單從這里的數(shù)據(jù)來(lái)看，我們?cè)O(shè)置的Buffer Pool 過(guò)大，僅僅使用1420/ 8192* 100% = 17.33%。
innodb_log_buffer_size 參數(shù)的使用
? ??顧名思義，這個(gè)參數(shù)就是用來(lái)設(shè)置Innodb 的Log Buffer 大小的，系統(tǒng)默認(rèn)值為1MB。Log Buffer的主要作用就是緩沖Log 數(shù)據(jù)，提高寫(xiě)Log 的IO 性能。一般來(lái)說(shuō)，如果你的系統(tǒng)不是寫(xiě)負(fù)載非常高且以大事務(wù)居多的話(huà)，8MB 以?xún)?nèi)的大小就完全足夠了。
? ? 查看log buffer使用情況：show status like 'innodb_log%';
? ??如果完全從Log Buffer 本身來(lái)說(shuō)，自然是大一些會(huì)減少更多的磁盤(pán)IO。但是由于Log 本身是為了保護(hù)數(shù)據(jù)安全而產(chǎn)生的，而Log 從Buffer 到磁盤(pán)的刷新頻率和控制數(shù)據(jù)安全一致的事務(wù)直接相關(guān)，并且也有相關(guān)參數(shù)來(lái)控制（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ù)可能并不是查詢(xún)發(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（幻讀）。屬于語(yǔ)句級(jí)別的隔離，如通過(guò)SELECT ... FOR UPDATE 和SELECT ... LOCK IN SHARE MODE 來(lái)執(zhí)行的請(qǐng)求僅僅鎖定索引記錄，而不鎖定之前的間隙，因而允許在鎖定的記錄后自由地插入新記錄。
? ??????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)。不論在這期間有沒(méi)有其他事務(wù)已經(jīng)修改了某些數(shù)據(jù)并提交。所以，SERIALIZABLE 事務(wù)隔離級(jí)別下，Phantom Reads 也不會(huì)出現(xiàn)。（幻讀：并不是說(shuō)兩次讀取獲取的結(jié)果集不同，幻讀側(cè)重的方面是某一次的 select 操作得到的結(jié)果所表征的數(shù)據(jù)狀態(tài)無(wú)法支撐后續(xù)的業(yè)務(wù)操作。更為具體一些：select 某記錄是否存在，不存在，準(zhǔn)備插入此記錄，但執(zhí)行 insert 時(shí)發(fā)現(xiàn)此記錄已存在，無(wú)法插入，此時(shí)就發(fā)生了幻讀）
解決不可重復(fù)讀的方法是?鎖行，解決幻讀的方式是?鎖表
25.悲觀鎖和樂(lè)觀鎖
數(shù)據(jù)庫(kù)中的樂(lè)觀鎖和悲觀鎖以及實(shí)現(xiàn)方式
樂(lè)觀鎖：獲取數(shù)據(jù)時(shí)不會(huì)考慮并發(fā)情況造成的數(shù)據(jù)沖突，然后再數(shù)據(jù)更新提交時(shí)正式對(duì)數(shù)據(jù)的沖突與否進(jìn)行檢測(cè)，如果發(fā)現(xiàn)沖突了，則返回錯(cuò)誤信息，讓用戶(hù)重新操作。
悲觀鎖：總是做最壞的打算，每次去讀取數(shù)據(jù)都會(huì)認(rèn)為會(huì)被其它線(xiàn)程修改，所以會(huì)加鎖,當(dāng)其它線(xiàn)程想要訪(fǎng)問(wèn)數(shù)據(jù)時(shí)，都需要阻塞掛起。
樂(lè)觀鎖實(shí)現(xiàn)方式
version方式：
一般在數(shù)據(jù)表中加一個(gè)version版本字段,表示數(shù)據(jù)被修改的版本次數(shù)，當(dāng)數(shù)據(jù)被修改時(shí)，version會(huì)被加一。當(dāng)線(xiàn)程A讀取數(shù)據(jù)時(shí)也要同時(shí)讀取version值，在提交更新的時(shí)候，如果剛才讀取的version值和當(dāng)前數(shù)據(jù)庫(kù)里的version值一致，那么才能更新，否則重新更新操作，直到更新成成功。樂(lè)觀鎖在獲得鎖的同時(shí)已經(jīng)完成了更新操作
SQL代碼實(shí)現(xiàn)：
悲觀鎖實(shí)現(xiàn)方式
for update方式:
一般使用select … for update 對(duì)所選擇的數(shù)據(jù)加鎖，例如 select * from t_goods where id =1 for update, 這條sql語(yǔ)句就鎖定了t_goods表中符合id=1的這條記錄，本次事務(wù)提交之前，外界無(wú)法修改這些記錄。悲觀鎖遵循一鎖、二判、三更新、四釋放的原則
