《Mysql應(yīng)用MySQL學(xué)習(xí)筆記小結(jié)》要點：
本文介紹了Mysql應(yīng)用MySQL學(xué)習(xí)筆記小結(jié)，希望對您有用。如果有疑問，可以聯(lián)系我們。

慢速SQL：執(zhí)行時間超過給定時間范圍的查詢就稱為慢速查詢.
在MySQL中如何記錄慢速SQL?
答：可以在my.cnf中設(shè)置如下信息：MYSQL教程

[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes

這三個設(shè)置的意思是可以記錄執(zhí)行時間超過5 秒和沒有使用索引的查詢.
MySQL中日志分類：
1. error log mysql錯誤記錄日志
2. bin log 記錄修改數(shù)據(jù)時候產(chǎn)生的quer并用二進制的方式進行存儲
3. mysql-bin.index 記錄是記錄所有Binary Log 的絕對路徑,保證MySQL 各種線程能夠順利的根據(jù)它找到所有需要的Binary Log 文件.
4. slow query log 記錄慢速SQL,是一個簡單的文本格式,可以通過各種文本編輯器查看其中的內(nèi)容.其中記錄了語句執(zhí)行的時刻,執(zhí)行所消耗的時間,執(zhí)行用戶.
5. innodb redo log 記錄Innodb 所做的所有物理變更和事務(wù)信息,保證事務(wù)安全性.

SQL架構(gòu)可分為：SQL 層 與 Storage Engine層
SQL Layer 中包含了多個子模塊：
1、初始化模塊
顧名思議,初始化模塊就是在MySQL Server 啟動的時候,對整個系統(tǒng)做各種各樣的初始化操作,比如各種buffer,cache 結(jié)構(gòu)的初始化和內(nèi)存空間的申請,各種系統(tǒng)變量的初始化設(shè)定,各種存儲引擎的初始化設(shè)置,等等.
2、核心API
核心API 模塊主要是為了提供一些需要非常高效的底層操作功能的優(yōu)化實現(xiàn),包括各種底層數(shù)據(jù)結(jié)構(gòu)的實現(xiàn),特殊算法的實現(xiàn),字符串處理,數(shù)字處理等,小文件I/O,格式化輸出,以及最重要的內(nèi)存管理部分.核心API 模塊的所有源代碼都集中在mysys 和strings文件夾下面,有興趣的讀者可以研究研究.
3、網(wǎng)絡(luò)交互模塊
底層網(wǎng)絡(luò)交互模塊抽象出底層網(wǎng)絡(luò)交互所使用的接口api,實現(xiàn)底層網(wǎng)絡(luò)數(shù)據(jù)的接收與發(fā)送,以方便其他各個模塊調(diào)用,以及對這一部分的維護.所有源碼都在vio 文件夾下面.
4、Client & Server 交互協(xié)議模塊
任何C/S 結(jié)構(gòu)的軟件系統(tǒng),都肯定會有自己獨有的信息交互協(xié)議,MySQL 也不例外.MySQL的Client & Server 交互協(xié)議模塊部分,實現(xiàn)了客戶端與MySQL 交互過程中的所有協(xié)議.當(dāng)然這些協(xié)議都是建立在現(xiàn)有的OS 和網(wǎng)絡(luò)協(xié)議之上的,如TCP/IP 以及Unix Socket.
5、用戶模塊
用戶模塊所實現(xiàn)的功能,主要包括用戶的登錄連接權(quán)限控制和用戶的授權(quán)管理.他就像MySQL 的大門守衛(wèi)一樣,決定是否給來訪者“開門”.
6、訪問控制模塊
造訪客人進門了就可以想干嘛就干嘛么?為了安全考慮,肯定不能如此隨意.這時候就需要訪問控制模塊實時監(jiān)控客人的每一個動作,給不同的客人以不同的權(quán)限.訪問控制模塊實現(xiàn)的功能就是根據(jù)用戶模塊中各用戶的授權(quán)信息,以及數(shù)據(jù)庫自身特有的各種約束,來控制用戶對數(shù)據(jù)的訪問.用戶模塊和訪問控制模塊兩者結(jié)合起來,組成了MySQL 整個數(shù)據(jù)庫系統(tǒng)的權(quán)限安全管理的功能.
7、連接管理、連接線程和線程管理
連接管理模塊負(fù)責(zé)監(jiān)聽對MySQL Server 的各種請求,接收連接請求,轉(zhuǎn)發(fā)所有連接請求到線程管理模塊.每一個連接上MySQL Server 的客戶端請求都會被分配(或創(chuàng)建)一個連接線程為其單獨服務(wù).而連接線程的主要工作就是負(fù)責(zé)MySQL Server 與客戶端的通信,接受客戶端的命令請求,傳遞Server 端的結(jié)果信息等.線程管理模塊則負(fù)責(zé)管理維護這些連接線程.包括線程的創(chuàng)建,線程的cache 等.
8、Query 解析和轉(zhuǎn)發(fā)模塊
在MySQL 中我們習(xí)慣將所有Client 端發(fā)送給Server 端的命令都稱為query,在MySQLServer 里面,連接線程接收到客戶端的一個Query 后,會直接將該query 傳遞給專門負(fù)責(zé)將各種Query 進行分類然后轉(zhuǎn)發(fā)給各個對應(yīng)的處理模塊,這個模塊就是query 解析和轉(zhuǎn)發(fā)模塊.其主要工作就是將query 語句進行語義和語法的分析,然后按照不同的操作類型進行分類,然后做出針對性的轉(zhuǎn)發(fā).
9、Query Cache 模塊
Query Cache 模塊在MySQL 中是一個非常重要的模塊,他的主要功能是將客戶端提交給MySQL 的Select 類query 請求的返回結(jié)果集cache 到內(nèi)存中,與該query 的一個hash 值做一個對應(yīng).該Query 所取數(shù)據(jù)的基表發(fā)生任何數(shù)據(jù)的變化之后,MySQL 會自動使該query 的Cache 失效.在讀寫比例非常高的應(yīng)用系統(tǒng)中,Query Cache 對性能的提高是非常顯著的.當(dāng)然它對內(nèi)存的消耗也是非常大的.
10、Query 優(yōu)化器模塊
Query 優(yōu)化器,顧名思義,就是優(yōu)化客戶端請求的query,根據(jù)客戶端請求的query 語句,和數(shù)據(jù)庫中的一些統(tǒng)計信息,在一系列算法的基礎(chǔ)上進行分析,得出一個最優(yōu)的策略,告訴后面的程序如何取得這個query 語句的結(jié)果.
11、表變更管理模塊
表變更管理模塊主要是負(fù)責(zé)完成一些DML 和DDL 的query,如：update,delte,insert,create table,alter table 等語句的處理.
12、表維護模塊
表的狀態(tài)檢查,錯誤修復(fù),以及優(yōu)化和分析等工作都是表維護模塊需要做的事情.
13、系統(tǒng)狀態(tài)管理模塊
系統(tǒng)狀態(tài)管理模塊負(fù)責(zé)在客戶端請求系統(tǒng)狀態(tài)的時候,將各種狀態(tài)數(shù)據(jù)返回給用戶,像DBA 常用的各種show status 命令,show variables 命令等,所得到的結(jié)果都是由這個模塊返回的.
14、表管理器
這個模塊從名字上看來很容易和上面的表變更和表維護模塊相混淆,但是其功能與變更及維護模塊卻完全不同.大家知道,每一個MySQL 的表都有一個表的定義文件,也就是*.frm文件.表管理器的工作主要就是維護這些文件,以及一個cache,該cache 中的主要內(nèi)容是各個表的結(jié)構(gòu)信息.此外它還維護table 級別的鎖管理.
15、日志記錄模塊
日志記錄模塊主要負(fù)責(zé)整個系統(tǒng)級別的邏輯層的日志的記錄,包括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)在兩個線程上面.一個是負(fù)責(zé)從Master 請求和接受binary 日志,并寫入本地relay log 中的I/O 線程.另外一個是負(fù)責(zé)從relay log 中讀取相關(guān)日志事件,然后解析成可以在Slave 端正確執(zhí)行并得到和Master 端完全相同的結(jié)果的命令并再交給Slave 執(zhí)行的SQL 線程.
17、存儲引擎接口模塊
存儲引擎接口模塊可以說是MySQL 數(shù)據(jù)庫中最有特色的一點了.目前各種數(shù)據(jù)庫產(chǎn)品
中,基本上只有MySQL 可以實現(xiàn)其底層數(shù)據(jù)存儲引擎的插件式管理.這個模塊實際上只是一個抽象類,但正是因為它成功地將各種數(shù)據(jù)處理高度抽象化,才成就了今天MySQL 可插拔存儲引擎的特色.MYSQL教程

MYSQL教程

MySQL性能調(diào)優(yōu)之監(jiān)控方法：MYSQL教程

1. set profiling=1 開啟性能監(jiān)控,此命令在某些版本的mysql中無法使用
2. 然后執(zhí)行SQL
3. show profiless,查看系統(tǒng)執(zhí)行SQL的時間
4. show profile cpu, block io for query 數(shù)字ID (此ID為show profiles中的性能輸出日志序號)MYSQL教程

?MySQL 各存儲引擎使用了三種類型(級別)的鎖定機制：行級鎖定,頁級鎖定和表級鎖定.
在MySQL 數(shù)據(jù)庫中,使用表級鎖定的主要是MyISAM,Memory,CSV 等一些非事務(wù)性存儲引擎,而使用行級鎖定的主要是Innodb 存儲引擎和NDB Cluster 存儲引擎,頁級鎖定主要是BerkeleyDB 存儲引擎的鎖定方式.
?
MyISAM讀請求和寫等待隊列中的寫鎖請求的優(yōu)先級規(guī)則主要為以下規(guī)則決定：
1. 除了READ_HIGH_PRIORITY 的讀鎖定之外,Pending write-lock queue 中的WRITE 寫鎖定能夠阻塞所有其他的讀鎖定；
2. READ_HIGH_PRIORITY 讀鎖定的請求能夠阻塞所有Pending write-lock queue 中的寫鎖定；
3. 除了WRITE 寫鎖定之外,Pending write-lock queue 中的其他任何寫鎖定都比讀鎖定的優(yōu)先級低.MYSQL教程

MyISAM寫鎖定出現(xiàn)在Current write-lock queue 之后,會阻塞除了以下情況下的所有其他鎖定的請求：
1. 在某些存儲引擎的允許下,可以允許一個WRITE_CONCURRENT_INSERT 寫鎖定請求
2. 寫鎖定為WRITE_ALLOW_WRITE 的時候,允許除了WRITE_ONLY 之外的所有讀和寫鎖定請求
3. 寫鎖定為WRITE_ALLOW_READ 的時候,允許除了READ_NO_INSERT 之外的所有讀鎖定請求
4. 寫鎖定為WRITE_DELAYED 的時候,允許除了READ_NO_INSERT 之外的所有讀鎖定請求
5. 寫鎖定為WRITE_CONCURRENT_INSERT 的時候,允許除了READ_NO_INSERT 之外的所有讀鎖定請求
?
Innodb 的行級鎖定注意事項：
a) 盡可能讓所有的數(shù)據(jù)檢索都通過索引來完成,從而避免Innodb 因為無法通過索引鍵加鎖而升級為表級鎖定；
b) 合理設(shè)計索引,讓Innodb 在索引鍵上面加鎖的時候盡可能準(zhǔn)確,盡可能的縮小鎖定范圍,避免造成不必要的鎖定而影響其他Query 的執(zhí)行；
c) 盡可能減少基于范圍的數(shù)據(jù)檢索過濾條件,避免因為間隙鎖帶來的負(fù)面影響而鎖定了不該鎖定的記錄；
d) 盡量控制事務(wù)的大小,減少鎖定的資源量和鎖定時間長度；
e) 在業(yè)務(wù)環(huán)境允許的情況下,盡量使用較低級別的事務(wù)隔離,以減少MySQL 因為實現(xiàn)事務(wù)隔離級別所帶來的附加成本；
如何查看MyISAM中表級鎖定信息：
答：show status like '%table_locks%'
???? table_locks_immediate:顯示的數(shù)字就是鎖定的次數(shù).
???? table_locks_waited:顯示的數(shù)字是出現(xiàn)表級鎖定爭用而發(fā)生等待的次數(shù)
?
如何查看Innodb中行級鎖定信息：
答： show status like '%Innodb_rows%'
Innodb 的行級鎖定狀態(tài)變量不僅記錄了鎖定等待次數(shù),還記錄了鎖定總時長,每次平均時長,以及最大時長,此外還有一個非累積狀態(tài)量顯示了當(dāng)前正在等待鎖定的等待數(shù)量.對各個狀態(tài)量的說明如下：
● Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量；
● Innodb_row_lock_time：從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度；
● Innodb_row_lock_time_avg：每次等待所花平均時間；
● Innodb_row_lock_time_max：從系統(tǒng)啟動到現(xiàn)在等待最常的一次所花的時間；
● Innodb_row_lock_waits：系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)；
?
mysqlslap是一個mysql官方提供的壓力測試工具.以下是比較重要的參數(shù)：
Cdefaults-file,配置文件存放位置
Cconcurrency,并發(fā)數(shù)
Cengines,引擎
Citerations,迭代的實驗次數(shù)
Csocket,socket文件位置
自動測試：
Cauto-generate-sql,自動產(chǎn)生測試SQL
Cauto-generate-sql-load-type,測試SQL的類型.類型有mixed,update,write,key,read.
Cnumber-of-queries,執(zhí)行的SQL總數(shù)量
Cnumber-int-cols,表內(nèi)int列的數(shù)量
Cnumber-char-cols,表內(nèi)char列的數(shù)量
例如：
shell>mysqlslap Cdefaults-file=/u01/mysql1/mysql/my.cnf Cconcurrency=50,100 Citerations=1 Cnumber-int-cols=4 Cauto-generate-sql Cauto-generate-sql-load-type=write Cengine=myisam Cnumber-of-queries=200 -S/tmp/mysql1.sock
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.016 seconds
Minimum number of seconds to run all queries: 0.016 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.265 seconds
Minimum number of seconds to run all queries: 0.265 seconds
Maximum number of seconds to run all queries: 0.265 seconds
Number of clients running queries: 100
Average number of queries per client: 2
指定數(shù)據(jù)庫的測試：
Ccreate-schema,指定數(shù)據(jù)庫名稱
Cquery,指定SQL語句,可以定位到某個包含SQL的文件
例如：
shell>mysqlslap Cdefaults-file=/u01/mysql1/mysql/my.cnf Cconcurrency=25,50 Citerations=1 Ccreate-schema=test Cquery=/u01/test.sql -S/tmp/mysql1.sock
Benchmark
Average number of seconds to run all queries: 0.018 seconds
Minimum number of seconds to run all queries: 0.018 seconds
Maximum number of seconds to run all queries: 0.018 seconds
Number of clients running queries: 25
Average number of queries per client: 1
Benchmark
Average number of seconds to run all queries: 0.011 seconds
Minimum number of seconds to run all queries: 0.011 seconds
Maximum number of seconds to run all queries: 0.011 seconds
Number of clients running queries: 50
Average number of queries per client: 1
?
MySQL 中索引使用相關(guān)的限制:
1. MyISAM 存儲引擎索引鍵長度總和不能超過1000 字節(jié)；
2. BLOB 和TEXT 類型的列只能創(chuàng)建前綴索引；
3. MySQL 目前不支持函數(shù)索引；
4. 使用不等于(!= 或者<>)的時候MySQL 無法使用索引；
5. 過濾字段使用了函數(shù)運算后(如abs(column)),MySQL 無法使用索引；
6. Join 語句中Join 條件字段類型不一致的時候MySQL 無法使用索引；
7. 使用LIKE 操作的時候如果條件以通配符開始( '%abc...')MySQL 無法使用索引；
8. 使用非等值查詢的時候MySQL 無法使用Hash 索引；
MySQL 目前可以通過兩種算法來實現(xiàn)數(shù)據(jù)的排序操作:
1. 取出滿足過濾條件的用于排序條件的字段以及可以直接定位到行數(shù)據(jù)的行指針信息,在SortBuffer 中進行實際的排序操作,然后利用排好序之后的數(shù)據(jù)根據(jù)行指針信息返回表中取得客戶端請求的其他字段的數(shù)據(jù),再返回給客戶端；
2. 根據(jù)過濾條件一次取出排序字段以及客戶端請求的所有其他字段的數(shù)據(jù),并將不需要排序的字段存放在一塊內(nèi)存區(qū)域中,然后在Sort Buffer 中將排序字段和行指針信息進行排序,最后再利用排序后的行指針與存放在內(nèi)存區(qū)域中和其他字段一起的行指針信息進行匹配合并結(jié)果集,再按照順序返回給客戶端.
?
MySQL Explain 功能中給我們展示的各種信息的解釋：
◆ ID：Query Optimizer 所選定的執(zhí)行計劃中查詢的序列號；
◆ Select_type：所使用的查詢類型,主要有以下這幾種查詢類型
◇ DEPENDENT SUBQUERY：子查詢中內(nèi)層的第一個SELECT,依賴于外部查詢的結(jié)果集；
◇ DEPENDENT UNION：子查詢中的UNION,且為UNION 中從第二個SELECT 開始的后面所有SELECT,同樣依賴于外部查詢的結(jié)果集；
◇ PRIMARY：子查詢中的最外層查詢,注意并不是主鍵查詢；
◇ SIMPLE：除子查詢或者UNION 之外的其他查詢；
◇ SUBQUERY：子查詢內(nèi)層查詢的第一個SELECT,結(jié)果不依賴于外部查詢結(jié)果集；
◇ UNCACHEABLE SUBQUERY：結(jié)果集無法緩存的子查詢；
◇ UNION：UNION 語句中第二個SELECT 開始的后面所有SELECT,第一個SELECT 為PRIMARY
◇ UNION RESULT：UNION 中的合并結(jié)果；
◆ Table：顯示這一步所訪問的數(shù)據(jù)庫中的表的名稱；
◆ Type：告訴我們對表所使用的訪問方式,主要包含如下集中類型；
◇ all：全表掃描
◇ const：讀常量,且最多只會有一條記錄匹配,由于是常量,所以實際上只需要讀一次；
◇ eq_ref：最多只會有一條匹配結(jié)果,一般是通過主鍵或者唯一鍵索引來訪問；
◇ fulltext：
◇ index：全索引掃描；
◇ index_merge：查詢中同時使用兩個(或更多)索引,然后對索引結(jié)果進行merge 之后再讀取表數(shù)據(jù)；
◇ index_subquery：子查詢中的返回結(jié)果字段組合是一個索引(或索引組合),但不是一個主鍵或者唯一索引；
◇ rang：索引范圍掃描；
◇ ref：Join 語句中被驅(qū)動表索引引用查詢；
◇ ref_or_null：與ref 的唯一區(qū)別就是在使用索引引用查詢之外再增加一個空值的查詢；
◇ system：系統(tǒng)表,表中只有一行數(shù)據(jù)；
◇ unique_subquery：子查詢中的返回結(jié)果字段組合是主鍵或者唯一約束；
◆ Possible_keys：該查詢可以利用的索引. 如果沒有任何索引可以使用,就會顯示成null,這一項內(nèi)容對于優(yōu)化時候索引的調(diào)整非常重要；
◆ Key：MySQL Query Optimizer 從possible_keys 中所選擇使用的索引；
◆ Key_len：被選中使用索引的索引鍵長度；
◆ Ref：列出是通過常量(const),還是某個表的某個字段(如果是join)來過濾(通過key)的；
◆ Rows：MySQL Query Optimizer 通過系統(tǒng)收集到的統(tǒng)計信息估算出來的結(jié)果集記錄條數(shù)；
◆ Extra：查詢中每一步實現(xiàn)的額外細(xì)節(jié)信息,主要可能會是以下內(nèi)容：
◇ 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)計信息判斷出不可能存在結(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)好的可以使用的索引的時候,如果發(fā)現(xiàn)如果來自前面的表的列值已知,可能部分索引可以使用.對前面的表的每個行組合,MySQL 檢查是否可以使用range 或index_merge 訪問方法來索取行.
◇ Select tables optimized away：當(dāng)我們使用某些聚合函數(shù)來訪問存在索引的某個字段的時候,MySQL Query Optimizer 會通過索引而直接一次定位到所需的數(shù)據(jù)行完成整個查詢.當(dāng)然,前提是在Query 中不能有GROUP BY 操作.如使用MIN()或者MAX()的時
候；
◇ Using filesort：當(dāng)我們的Query 中包含ORDER BY 操作,而且無法利用索引完成排序操作的時候,MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來實現(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 子句的時候,如果分組字段也在索引中,Extra 中的信息就會是Using index for group-by；
◇ Using temporary：當(dāng)MySQL 在某些操作中必須使用臨時表的時候,在Extra 信息中就會出現(xiàn)Using temporary .主要常見于GROUP BY 和ORDER BY 等操作中.
◇ Using where：如果我們不是讀取表的所有數(shù)據(jù),或者不是僅僅通過索引就可以獲取所有需要的數(shù)據(jù),則會出現(xiàn)Using where 信息；
◇ Using where with pushed condition：這是一個僅僅在NDBCluster 存儲引擎中才會出現(xiàn)的信息,而且還需要通過打開Condition Pushdown 優(yōu)化功能才可能會被使用.控制參數(shù)為engine_condition_pushdown .
?
什么是松散索引?
答：實際上就是當(dāng)MySQL 完全利用索引掃描來實現(xiàn)GROUP BY 的時候,并不需要掃描所有滿足條件的索引鍵即可完成操作得出結(jié)果.
要利用到松散索引掃描實現(xiàn)GROUP BY,需要至少滿足以下幾個條件：
◆ GROUP BY 條件字段必須在同一個索引中最前面的連續(xù)位置；
◆ 在使用GROUP BY 的同時,只能使用MAX 和MIN 這兩個聚合函數(shù)；
◆ 如果引用到了該索引中GROUP BY 條件之外的字段條件的時候,必須以常量形式存在；MYSQL教程

為什么松散索引掃描的效率會很高?
答：因為在沒有WHERE 子句,也就是必須經(jīng)過全索引掃描的時候, 松散索引掃描需要讀取的鍵值數(shù)量與分組的組數(shù)量一樣多,也就是說比實際存在的鍵值數(shù)目要少很多.而在WHERE 子句包含范圍判斷式或者等值表達(dá)式的時候, 松散索引掃描查找滿足范圍條件的每個組的第1 個關(guān)鍵字,并且再次讀取盡可能最少數(shù)量的關(guān)鍵字.
?
什么是緊湊索引?
答：緊湊索引掃描實現(xiàn)GROUP BY 和松散索引掃描的區(qū)別主要在于他需要在掃描索引的時候,讀取所有滿足條件的索引鍵,然后再根據(jù)讀取的數(shù)據(jù)來完成GROUP BY 操作得到相應(yīng)結(jié)果.MYSQL教程

MySQL 處理GROUP BY 的方式,有兩種如下優(yōu)化思路：
1. 盡可能讓MySQL 可以利用索引來完成GROUP BY 操作,當(dāng)然最好是松散索引掃描的方式最佳.在系統(tǒng)允許的情況下,我們可以通過調(diào)整索引或者調(diào)整Query 這兩種方式來達(dá)到目的；MYSQL教程

2. 當(dāng)無法使用索引完成GROUP BY 的時候,由于要使用到臨時表且需要filesort,所以我們必須要有足夠的sort_buffer_size 來供MySQL 排序的時候使用,而且盡量不要進行大結(jié)果集的GROUPBY 操作,因為如果超出系統(tǒng)設(shè)置的臨時表大小的時候會出現(xiàn)將臨時表數(shù)據(jù)copy 到磁盤上面再進行操作,這時候的排序分組操作性能將是成數(shù)量級的下降；
?
DINSTINCT 其實和 GROUP BY 原理類似,同樣可以使用松散索引.
?
MySQL Schema 設(shè)計優(yōu)化小記：
1. 適度冗余
2. 大字段垂直分拆
3. 大表水平分拆
?
時間字段類型：timestamp 占用4個字節(jié),datetime,date占用8個字節(jié),但是timestamp只能用在1970年以后的記錄,datetime,date可用在1001年開始.
?
MySQL binlog日志優(yōu)化方案：MYSQL教程

Binlog 相關(guān)參數(shù)及優(yōu)化策略
我們首先看看Binlog 的相關(guān)參數(shù),通過執(zhí)行如下命令可以獲得關(guān)于Binlog 的相關(guān)參數(shù).當(dāng)然,其中也顯示出了“ innodb_locks_unsafe_for_binlog”這個Innodb 存儲引擎特有的與Binlog 相關(guān)的參數(shù)：
mysql> show variables like '%binlog%';
+--------------------------------+------------+
| Variable_name | Value |
+--------------------------------+------------+
| binlog_cache_size | 1048576 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| sync_binlog | 0 |
+--------------------------------+------------+
“binlog_cache_size"：在事務(wù)過程中容納二進制日志SQL 語句的緩存大小.二進制日志緩存是服務(wù)器支持事務(wù)存儲引擎并且服務(wù)器啟用了二進制日志(―log-bin 選項)的前提下為每個客戶端分配的內(nèi)存,注意,是每個Client 都可以分配設(shè)置大小的binlog cache 空間.如果讀者朋友的系統(tǒng)中經(jīng)常會出現(xiàn)多語句事務(wù)的華,可以嘗試增加該值的大小,以獲得更好的性能.當(dāng)然,我們可以通過MySQL 的以下兩個狀態(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)存大小.當(dāng)我們執(zhí)行多語句事務(wù)的時候,max_binlog_cache_size 如果不夠大的話,系統(tǒng)可能會報出“ Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的錯誤.
“max_binlog_size”：Binlog 日志最大值,一般來說設(shè)置為512M 或者1G,但不能超過1G.該大小并不能非常嚴(yán)格控制Binlog 大小,尤其是當(dāng)?shù)竭_(dá)Binlog 比較靠近尾部而又遇到一個較大事務(wù)的時候,系統(tǒng)為了保證事務(wù)的完整性,不可能做切換日志的動作,只能將該事務(wù)的所有SQL 都記錄進入當(dāng)前日志,直到該事務(wù)結(jié)束.這一點和Oracle 的Redo 日志有點不一樣,因為Oracle 的Redo 日志所記錄的是數(shù)據(jù)文件的物理位置的變化,而且里面同時記錄了Redo 和Undo 相關(guān)的信息,所以同一個事務(wù)是否在一個日志中對Oracle 來說并不關(guān)鍵.而MySQL 在Binlog 中所記錄的是數(shù)據(jù)庫邏輯變化信息,MySQL 稱之為Event,實際上就是帶來數(shù)據(jù)庫變化的DML 之類的Query 語句.“sync_binlog”：這個參數(shù)是對于MySQL 系統(tǒng)來說是至關(guān)重要的,他不僅影響到Binlog 對MySQL 所帶來的性能損耗,而且還影響到MySQL 中數(shù)據(jù)的完整性.對于“sync_binlog”參數(shù)的各種設(shè)置的說明如下：
● sync_binlog=0,當(dāng)事務(wù)提交之后,MySQL 不做fsync 之類的磁盤同步指令刷新binlog_cache 中的信息到磁盤,而讓Filesystem 自行決定什么時候來做同步,或者cache 滿了之后才同步到磁盤.
● sync_binlog=n,當(dāng)每進行n 次事務(wù)提交之后,MySQL 將進行一次fsync 之類的磁盤同步指令來將binlog_cache 中的數(shù)據(jù)強制寫入磁盤.在MySQL 中系統(tǒng)默認(rèn)的設(shè)置是sync_binlog=0,也就是不做任何強制性的磁盤刷新指令,這時候的性能是最好的,但是風(fēng)險也是最大的.因為一旦系統(tǒng)Crash,在binlog_cache 中的所有binlog 信息都會被丟失.而當(dāng)設(shè)置為“1”的時候,是最安全但是性能損耗最大的設(shè)置.因為當(dāng)設(shè)置為1 的時候,即使系統(tǒng)Crash,也最多丟失binlog_cache 中未完成的一個事務(wù),對實際數(shù)據(jù)沒有任何實質(zhì)性影響.從以往經(jīng)驗和相關(guān)測試來看,對于高并發(fā)事務(wù)的系統(tǒng)來說,“sync_binlog”設(shè)置為0 和設(shè)置為1 的系統(tǒng)寫入性能差距可能高達(dá)5 倍甚至更多.
?
MySQL QueryCache 負(fù)面影響：
a) Query 語句的hash 運算以及hash 查找資源消耗.當(dāng)我們使用Query Cache 之后,每條SELECT類型的Query 在到達(dá)MySQL 之后,都需要進行一個hash 運算然后查找是否存在該Query 的Cache,雖然這個hash 運算的算法可能已經(jīng)非常高效了,hash 查找的過程也已經(jīng)足夠的優(yōu)化了,對于一條Query 來說消耗的資源確實是非常非常的少,但是當(dāng)我們每秒都有上千甚至幾千條Query 的時候,我們就不能對產(chǎn)生的CPU 的消耗完全忽視了.
b) Query Cache 的失效問題.如果我們的表變更比較頻繁,則會造成Query Cache 的失效率非常高.這里的表變更不僅僅指表中數(shù)據(jù)的變更,還包括結(jié)構(gòu)或者索引等的任何變更.也就是說我們每次緩存到Query Cache 中的Cache 數(shù)據(jù)可能在剛存入后很快就會因為表中的數(shù)據(jù)被改變而被清除,然后新的相同Query 進來之后無法使用到之前的Cache.
c) Query Cache 中緩存的是Result Set ,而不是數(shù)據(jù)頁,也就是說,存在同一條記錄被Cache 多次的可能性存在.從而造成內(nèi)存資源的過渡消耗.當(dāng)然,可能有人會說我們可以限定QueryCache 的大小啊.是的,我們確實可以限定Query Cache 的大小,但是這樣,Query Cache 就很容易造成因為內(nèi)存不足而被換出,造成命中率的下降.
?
在短連接的應(yīng)用系統(tǒng)中,thread_cache_size 的值應(yīng)該設(shè)置的相對大一些,不應(yīng)該小于應(yīng)用系統(tǒng)對數(shù)據(jù)庫的實際并發(fā)請求數(shù).
?
通過系統(tǒng)設(shè)置和當(dāng)前狀態(tài)的分析,我們可以發(fā)現(xiàn),thread_cache_size 的設(shè)置已經(jīng)足夠了,甚至還遠(yuǎn)大于系統(tǒng)的需要.所以我們可以適當(dāng)減少thread_cache_size 的設(shè)置,比如設(shè)置為8 或者16.根據(jù)Connections 和Threads_created 這兩個系統(tǒng)狀態(tài)值,我們還可以計算出系統(tǒng)新建連接連接的ThreadCache 命中率,也就是通過Thread Cache 池中取得連接線程的次數(shù)與系統(tǒng)接收的總連接次數(shù)的比率,如下：
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%
一般來說,當(dāng)系統(tǒng)穩(wěn)定運行一段時間之后,我們的Thread Cache 命中率應(yīng)該保持在90%左右甚至更高的比率才算正常.可以看出上面環(huán)境中的Thread Cache 命中比率基本還算是正常的.
?
如何查看MySQL打開Table的數(shù)量：
mysql> show status like 'open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 6 |
+---------------+-------+
?
MySQL buffer注意事項
join_buffer_size 和 sort_buffer_size 是針對的每個線程的buffer大小而言的,而不是整個系統(tǒng)共享的Buffer.
?
假設(shè)是一臺單獨給MySQL 使用的主機,物理內(nèi)存總大小為8G,MySQL 最大連接數(shù)為500,同時還使用了MyISAM 存儲引擎,這時候我們的整體內(nèi)存該如何分配呢?
內(nèi)存分配為如下幾大部分：
a) 系統(tǒng)使用,假設(shè)預(yù)留800M；
b) 線程獨享,約2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB),組成大概如下:
sort_buffer_size：1MB
join_buffer_size：1MB
read_buffer_size：1MB
read_rnd_buffer_size：512KB
thread_statck：512KB
c) MyISAM Key Cache,假設(shè)大概為1.5GB；
d) Innodb Buffer Pool 最大可用量：8GB - 800MB - 2GB - 1.5GB = 3.7GB；MYSQL教程

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5999.html