MYSQL數(shù)據(jù)庫技術(shù)分享

1、MYSQL數(shù)據(jù)庫技術(shù)分享數(shù)據(jù)庫技術(shù)分享數(shù)據(jù)庫技術(shù)分享 2內(nèi)容提要數(shù)據(jù)庫參數(shù)介紹慢SQL定位與分析SQL優(yōu)化FAQ數(shù)據(jù)庫參數(shù)優(yōu)化MYSQL參數(shù)分成幾塊：Client-會(huì)被MySQL客戶端應(yīng)用讀取-端口設(shè)置等mysqld -MySQL 服務(wù)端設(shè)置-內(nèi)存連接等mysqldump-運(yùn)行mysqldump的時(shí)候會(huì)讀取.數(shù)據(jù)庫參數(shù)優(yōu)化內(nèi)存部分：innodb_buffer_pool_size 緩沖池字節(jié)大小，InnoDB緩存表和索引數(shù)據(jù)的內(nèi)存區(qū)域，我們做的99%的操作都要跟這個(gè)打交道，理論上緩沖越大，效率越高，你設(shè)置越大,你在存取表里面數(shù)據(jù)時(shí)所需要的磁盤I/O越少. # 在一個(gè)獨(dú)立使用的數(shù)據(jù)庫服務(wù)器上,你可

2、以設(shè)置這個(gè)變量到服務(wù)器物理內(nèi)存大小的80%(Innodb_buffer_pool_pages_data/ Innodb_buffer_pool_pages_total)innodb_additional_mem_pool_size這個(gè)參數(shù)用來設(shè)置 InnoDB 存儲(chǔ)的數(shù)據(jù)目錄信息和其它內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。應(yīng)用程序里的表越多，你需要在這里分配越多的內(nèi)存。對(duì)于一個(gè)相對(duì)穩(wěn)定的應(yīng)用，這個(gè)參數(shù)的大小也是相對(duì)穩(wěn)定的，也沒有必要預(yù)留非常大的值。如果 InnoDB 用光了這個(gè)池內(nèi)的內(nèi)存， InnoDB 開始從操作系統(tǒng)分配內(nèi)存，并且往 MySQL 錯(cuò)誤日志寫警告信息。默認(rèn)值是 1MB ，當(dāng)發(fā)現(xiàn)錯(cuò)誤日志中已

3、經(jīng)有相關(guān)的警告信息時(shí)，就應(yīng)該適當(dāng)?shù)脑黾釉搮?shù)的大小innodb_log_buffer_size 磁盤速度是很慢的，直接將log寫道磁盤會(huì)影響InnoDB的性能，該參數(shù)設(shè)定了log buffer的大小，默認(rèn)4M或8M，一般設(shè)置1秒的寫入量即可 key_buffer_size myisam引擎存放索引數(shù)據(jù)文件的，因?yàn)楝F(xiàn)在我們默認(rèn)都使用INNODB所以很少用數(shù)據(jù)庫參數(shù)優(yōu)化文件部分：datadir=/data/mysqldata 設(shè)置我們的數(shù)據(jù)庫文件存放位置character-set-server = utf8 設(shè)置字符集innodb_file_per_table 一個(gè)表一個(gè)文件獨(dú)立，不設(shè)置，所有數(shù)據(jù)

4、文件一個(gè)，在表數(shù)量很多的環(huán)境下I/O負(fù)載會(huì)很高數(shù)據(jù)庫參數(shù)優(yōu)化連接部分：skip-name-resolve 都會(huì)向DNS做反向地址查詢，查不到只有等超時(shí)失敗后才能連接，這個(gè)選項(xiàng)就是禁止DNS反響查詢back_log 參數(shù)的值指出在MySQL暫時(shí)停止響應(yīng)新請(qǐng)求之前的短時(shí)間內(nèi)多少個(gè)請(qǐng)求可以被存在堆棧中。 如果系統(tǒng)在一個(gè)短時(shí)間內(nèi)有很多連接，則需要增大該參數(shù)的值max_connections 數(shù)據(jù)庫能承受最大連接數(shù)sort_buffer_size 連接級(jí)別的，每個(gè)連接都需要分配這么多，不要太大 1Mmax_allowed_packet設(shè)置Server接受的數(shù)據(jù)包的大小，粗略的估算一下一個(gè)查詢返回的最大大

5、小thread_cache_size 這個(gè)值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時(shí)如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請(qǐng)求，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求，那么這個(gè)線程將被重新創(chuàng)建,如果有很多新的線程，增加這個(gè)值可以改善系統(tǒng)性能.256-512數(shù)據(jù)庫參數(shù)優(yōu)化慢日志：slow_query_log =ON 開啟慢日志long_query_time 設(shè)置運(yùn)行時(shí)間超過幾秒的SQL會(huì)被記錄下來slow_query_log_file 慢日志位置名稱log_queries_not_using_indexes 是否記錄沒有使用索引的sql數(shù)據(jù)

6、庫參數(shù)優(yōu)化磁盤IO：以下兩個(gè)參數(shù)是控制MySQL 磁盤寫入策略以及數(shù)據(jù)安全性的關(guān)鍵參數(shù)sync_binlog 當(dāng)事務(wù)提交之后，MySQL以什么頻率進(jìn)行磁盤同步指令刷新binlog_cache中的信息到磁盤。=0當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時(shí)候來做同步，或者cache滿了之后才同步到磁盤=n當(dāng)每進(jìn)行n次事務(wù)提交之后，MySQL將進(jìn)行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤。0性能最好，1數(shù)據(jù)最安全，在繁忙系統(tǒng)兩者寫性能會(huì)相差3-5倍innodb_f

7、lush_log_at_trx_commit 以什么方式刷新日志到磁盤=0，log buffer將每秒一次地寫入log file中，并且log file的flush(刷到磁盤)操作同時(shí)進(jìn)行.該模式下，在事務(wù)提交的時(shí)候，不會(huì)主動(dòng)觸發(fā)寫入磁盤的操作。=1，每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去.=2，每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫入log file.但是flush(刷到磁盤)操作并不會(huì)同時(shí)進(jìn)行。該模式下,MySQL會(huì)每秒執(zhí)行一次 flush(刷到磁盤)操作。0性能最好，1數(shù)據(jù)最安全數(shù)據(jù)庫參數(shù)優(yōu)化數(shù)據(jù)庫

8、參數(shù)優(yōu)化如何查看參數(shù)設(shè)置：數(shù)據(jù)庫慢SQL如何知道有慢SQL了：1. 設(shè)置慢SQL參數(shù)，查看慢日志數(shù)據(jù)庫慢SQL如何知道有慢SQL了：1.查看慢日志：Time運(yùn)行時(shí)間點(diǎn)，Userhost運(yùn)行賬戶和服務(wù)器IP，Query_time這條sql運(yùn)行了多長時(shí)間數(shù)據(jù)庫慢SQL如何知道有慢SQL了：Mysqldumpslow工具：參數(shù) -s, 是表示按照何種方式排序，c、t、l、r分別是按照語句query次數(shù)、query time、lock time、rows sent的總數(shù)來排序數(shù)據(jù)庫慢SQL如何知道有慢SQL了：2. show processlist/show full processlist;id #

9、ID標(biāo)識(shí)，要kill一個(gè)語句的時(shí)候很有用user #當(dāng)前連接用戶host #顯示這個(gè)連接從哪個(gè)ip的哪個(gè)端口上發(fā)出db #數(shù)據(jù)庫名command #連接狀態(tài)，一般是休眠（sleep），查詢（query），連接（connect）time #連接持續(xù)時(shí)間，單位是秒state #顯示當(dāng)前sql語句的狀態(tài)info #顯示這個(gè)sql語句State包括（ Checking table ， Closing tables ， Copying to tmp table on disk ， Creating tmp table ，Killed 。）數(shù)據(jù)庫慢SQL如何知道有慢SQL了：數(shù)據(jù)庫慢SQL如何分析慢SQL

10、：1.explain 工具查看執(zhí)行計(jì)劃 數(shù)據(jù)庫慢SQL如何分析慢SQL：1. explain 工具查看執(zhí)行計(jì)劃 數(shù)據(jù)庫慢SQL如何分析慢SQL：1.explain 工具查看執(zhí)行計(jì)劃 數(shù)據(jù)庫慢SQL如何分析慢SQL：1.explain 工具查看執(zhí)行計(jì)劃 type 重要的項(xiàng),顯示連接使用的類型說明system表僅有一行。這是 const 連接類型的一個(gè)特例。constconst 用于用常數(shù)值比較 PRIMARY KEY 時(shí)。當(dāng)查詢的表僅有一行時(shí),使用 System。ref非唯一索引掃描，返回匹配某個(gè)單獨(dú)值得所有行，常見于非唯一索引掃描eq_ref唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄匹配

11、，常見于主鍵或唯一索引掃描allMysql會(huì)遍歷全表index遍歷整個(gè)索引樹進(jìn)行查找range索引范圍掃描，常見于between 數(shù)據(jù)庫慢SQL如何分析慢SQL：1.explain 工具查看執(zhí)行計(jì)劃 type執(zhí)行效率，從左至右，最差到最好 數(shù)據(jù)庫慢SQL如何分析慢SQL：1.explain 工具查看執(zhí)行計(jì)劃 數(shù)據(jù)庫慢SQL如何分析慢SQL：1. explain 工具查看執(zhí)行計(jì)劃 樣例：項(xiàng)說明extra列出不適合在其他列中顯示的重要信息數(shù)據(jù)庫慢SQL如何分析慢SQL：1.explain 工具查看執(zhí)行計(jì)數(shù)據(jù)庫慢SQL如何分析慢SQL：2.Show profile explain是看執(zhí)行計(jì)劃，了解SQL是如何被拆解運(yùn)行的，那么profile則是查看一條SQL到底是