項(xiàng)目6 監(jiān)控、測(cè)試并優(yōu)化 MySQL 性能

數(shù)據(jù)庫(kù)運(yùn)維與管理項(xiàng)目6監(jiān)控、測(cè)試并優(yōu)化MySQL性能教學(xué)目標(biāo)知識(shí)目標(biāo)熟悉MySQL性能監(jiān)控常用指標(biāo)掌握MySQL性能監(jiān)控常用工具的用法掌握MySQL性能測(cè)試工具的使用方法掌握MySQL性能調(diào)優(yōu)的概念和基本方法能力目標(biāo)能使用MONyog等工具監(jiān)控MySQL服務(wù)器能基于性能參數(shù)對(duì)MySQL數(shù)據(jù)庫(kù)的讀寫(xiě)性能進(jìn)行簡(jiǎn)單調(diào)優(yōu)能完成對(duì)MySQL數(shù)據(jù)庫(kù)的壓力測(cè)試能基于explain工具分析并優(yōu)化SQL查詢素養(yǎng)目標(biāo)培養(yǎng)精益求精的工匠意識(shí)培養(yǎng)軟件即服務(wù)意識(shí)加強(qiáng)規(guī)范操作意識(shí)思政目標(biāo)民族自豪感國(guó)產(chǎn)軟件振興的使命擔(dān)當(dāng)項(xiàng)目6監(jiān)控、測(cè)試并優(yōu)化MySQL性能項(xiàng)目場(chǎng)景：夏天來(lái)臨，天天電器商場(chǎng)的空調(diào)在線銷(xiāo)售量及售后服務(wù)訪問(wèn)量逐漸增加，隨著該商場(chǎng)的空調(diào)大促銷(xiāo)活動(dòng)的開(kāi)始，有客戶反饋購(gòu)買(mǎi)空調(diào)商品的頁(yè)面經(jīng)?？D，甚至在申請(qǐng)?zhí)峤豢照{(diào)服務(wù)時(shí)，偶爾會(huì)出現(xiàn)提交失敗的情況。業(yè)務(wù)系統(tǒng)管理員也反饋在系統(tǒng)中按照電話號(hào)碼尾號(hào)進(jìn)行模糊查詢時(shí)，會(huì)出現(xiàn)假死機(jī)狀態(tài)。信息部門(mén)接到任務(wù)，要求對(duì)系統(tǒng)運(yùn)行進(jìn)行實(shí)時(shí)監(jiān)控，排查原因，優(yōu)化訪問(wèn)性能，改善用戶的體驗(yàn)。項(xiàng)目知識(shí)導(dǎo)入一、性能監(jiān)控1．性能監(jiān)控常用指標(biāo)（1）QPS（QueriesPerSecond，每秒查詢數(shù)）是指MySQL服務(wù)器每秒執(zhí)行的查詢總量，通過(guò)查詢數(shù)目狀態(tài)值（Queries）每秒內(nèi)的變化量來(lái)近似表示，即QPS=(Queries2-Queries1)/(Uptime2-Uptime1)（2）TPS（TransactionsPerSecond，每秒事務(wù)數(shù)）是指MySQLServer每秒處理的事務(wù)數(shù)量。事務(wù)數(shù)TC=Com_insert+Com_delete+Com_update。其中，Com_insert為insert語(yǔ)句執(zhí)行的次數(shù)；Com_update為update語(yǔ)句執(zhí)行的次數(shù)；Com_delete為delete語(yǔ)句執(zhí)行的次數(shù)。TPS=(TC2-TC1)/(Uptime2-Uptime1)（3）線程狀態(tài)Threads_running:當(dāng)前正處于激活狀態(tài)的線程個(gè)數(shù)，可以理解為當(dāng)前并發(fā)數(shù)。threads_connected：當(dāng)前連接的線程的個(gè)數(shù)，即當(dāng)前打開(kāi)的連接數(shù)。項(xiàng)目知識(shí)導(dǎo)入（4）流量狀態(tài)Bytes_received：從所有客戶端接收到的字節(jié)數(shù)。Bytes_sent：發(fā)送給所有客戶端的字節(jié)數(shù)。（5）InnoDB引擎文件讀寫(xiě)次數(shù)Innodb_data_reads：InnoDB引擎從文件中讀取的次數(shù)。

Innodb_data_writes：InnoDB引擎從文件中寫(xiě)入的次數(shù)。

Innodb_data_fsyncs：InnoDB引擎進(jìn)行fsync()操作的次數(shù)。

（6）

InnoDB引擎讀寫(xiě)量Innodb_data_read：自服務(wù)器啟動(dòng)以來(lái)讀取的數(shù)據(jù)量。

Innodb_data_written：InnoDB引擎寫(xiě)入的數(shù)據(jù)量（以字節(jié)為單位）。項(xiàng)目知識(shí)導(dǎo)入（7）InnoDB引擎緩沖池狀態(tài)Innodb_buffer_pool_reads:表示InnoDB緩沖池?zé)o法滿足的請(qǐng)求數(shù)，即從物理磁盤(pán)讀取的次數(shù)。Innodb_buffer_pool_read_requests：從InnoDB緩沖池讀取的請(qǐng)求數(shù)（邏輯讀取的請(qǐng)求數(shù)）。

Innodb_buffer_pool_write_requests：向InnoDB緩沖池執(zhí)行的寫(xiě)入次數(shù)。

Innodb_buffer_pool_pages_dirty:InnoDB緩沖池中臟頁(yè)（在內(nèi)存修改但未寫(xiě)入數(shù)據(jù)文件的數(shù)據(jù)頁(yè)）的數(shù)量。

Innodb_buffer_pool_pages_flushed：InnoDB緩沖池中刷新頁(yè)面的請(qǐng)求數(shù)。

Innodb_buffer_pool_pages_free：InnoDB緩沖池中的空閑頁(yè)面數(shù)量。Innodb_buffer_pool_pages_total：InnoDB緩沖池的總大小，以page為單位。

Innodb_buffer_read_hit_ratio：InnoDB緩沖池的讀命中率，計(jì)算公式如下。Innodb_buffer_read_hit_ratio=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)×100%Innodb_buffer_usage：InnoDB緩沖池的利用率，計(jì)算公式如下。Innodb_buffer_usage=(1-Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total)×100%項(xiàng)目知識(shí)導(dǎo)入（8）InnoDB日志Innodb_os_log_fsyncs：向日志文件完成fsync()寫(xiě)入的數(shù)Innodb_os_log_written：寫(xiě)入日志文件的字節(jié)數(shù)。Innodb_log_writes：向日志文件物理寫(xiě)入的次數(shù)。Innodb_log_write_requests：向日志文件寫(xiě)入的請(qǐng)求數(shù)。（9）InnoDB行Innodb_rows_deleted：從InnoDB表中刪除的行數(shù)。

Innodb_rows_inserted：向InnoDB表中插入的行數(shù)。

Innodb_rows_read：從InnoDB表中讀取的行數(shù)。

Innodb_rows_updated：InnoDB表中更新的行數(shù)。

Innodb_row_lock_waits:：等待行鎖的次數(shù)。

Innodb_row_lock_time：獲取行鎖花費(fèi)的總時(shí)間，以毫秒為單位。

Innodb_row_lock_time_avg：獲取行鎖花費(fèi)的平均時(shí)間，以毫秒為單位。項(xiàng)目知識(shí)導(dǎo)入（10）MyISAM索引緩存MyISAM存儲(chǔ)引擎的緩存僅僅緩存索引數(shù)據(jù)，并不會(huì)緩存實(shí)際的表數(shù)據(jù)信息到內(nèi)存中，索引數(shù)據(jù)以塊（Block）為最小存儲(chǔ)單位。key_buffer_size：MyISAM索引緩存大小。

Key_read_requests：從MyISAM索引緩存中讀取塊的請(qǐng)求數(shù)。

Key_write_requests：將塊寫(xiě)入MyISAM索引緩存中的請(qǐng)求數(shù)。

Key_reads：將塊從磁盤(pán)文件物理讀取到MyISAM索引緩存的次數(shù)。緩存未命中的概率可以為Key_reads/Key_read_requests。

Key_writes：從MyISAM索引緩存中物理寫(xiě)入磁盤(pán)文件的次數(shù)。

Key_blocks_used：MyISAM索引緩存中已使用的塊數(shù)。項(xiàng)目知識(shí)導(dǎo)入（10）MyISAM索引緩存Key_blocks_unused：MyISAM索引緩存中未使用的塊數(shù)。Key_usage_ratio=Key_blocks_used/(Key_blocks_used+Key_blocks_unused)×100%Key_read_hit_ratio：MyISAM的索引緩存的讀命中率，計(jì)算公式如下。Key_read_hit_ratio=(1-Key_reads/Key_read_requests)×100%Key_write_hit_ratio：MyISAM的索引緩存的寫(xiě)命中率，計(jì)算公式如下。Key_write_hit_ratio=(1-Key_writes/Key_write_requests)×100%

項(xiàng)目知識(shí)導(dǎo)入（11）臨時(shí)表Created_tmp_disk_tables：服務(wù)器執(zhí)行語(yǔ)句時(shí)在磁盤(pán)上自動(dòng)創(chuàng)建的臨時(shí)表數(shù)量。

Created_tmp_tables：服務(wù)器執(zhí)行語(yǔ)句時(shí)在內(nèi)存中自動(dòng)創(chuàng)建的臨時(shí)表數(shù)量。

Created_tmp_disk_tables/Created_tmp_tables的值最好不要超過(guò)10%，如果Created_tmp_tables的值比較大，可能是排序句子過(guò)多或者連接句子優(yōu)化不足。（12）其他Slow_queries：執(zhí)行時(shí)間超過(guò)long_query_time的查詢的個(gè)數(shù)（重要）。

Sort_rows：已經(jīng)排序的行數(shù)。

Open_files：打開(kāi)的文件的數(shù)量。

Open_tables：當(dāng)前打開(kāi)的表的數(shù)量。

Select_scan：對(duì)第一個(gè)表進(jìn)行完全掃描的連接數(shù)量。項(xiàng)目知識(shí)導(dǎo)入二、性能監(jiān)控常用工具1．mysqladmin mysqladmin是MySQL的客戶端命令行管理工具，用于執(zhí)行監(jiān)視流程管理操作、檢查服務(wù)器配置、重裝權(quán)限、檢查當(dāng)前狀態(tài)、設(shè)置root賬戶的密碼、更改root賬戶的密碼，以及創(chuàng)建、刪除數(shù)據(jù)庫(kù)等操作。例如，要檢查MySQL狀態(tài)及正常運(yùn)行時(shí)間，可以執(zhí)行mysqladmin-uroot-pversion命令。只有具有root權(quán)限才能從Shell執(zhí)行命令。命令及執(zhí)行結(jié)果如下。項(xiàng)目知識(shí)導(dǎo)入2．MONyog

MONyog可以實(shí)時(shí)監(jiān)控MySQL服務(wù)器，查看MySQL服務(wù)器的運(yùn)行狀態(tài)。 MONyog提供了一個(gè)日志分析模塊，可以識(shí)別在服務(wù)器上運(yùn)行緩慢的語(yǔ)句和應(yīng)用程序。 MONyog提供各種過(guò)濾和explain選項(xiàng)，用于實(shí)現(xiàn)高效的工作流程。 MONyog可以提取錯(cuò)誤日志的內(nèi)容，同時(shí)可以通過(guò)郵件報(bào)告MySQL服務(wù)器狀態(tài)。 MONyog提供了圖表，用戶通過(guò)圖表可以監(jiān)控單個(gè)或多組服務(wù)器的MySQL和操作系統(tǒng)特定指標(biāo)。從而可以輕松了解所有MySQL服務(wù)器的安全性、可用性和性能。

MONyog的查詢分析器功能可幫助識(shí)別有問(wèn)題的SQL語(yǔ)句。項(xiàng)目知識(shí)導(dǎo)入三、性能測(cè)試

行業(yè)中的性能測(cè)試工具主要有以下幾類:

1．Mysqlslap：mysqlslap是MySQL自帶的基準(zhǔn)測(cè)試工具，隨MySQL5.1.4推出，可以通過(guò)模擬多個(gè)并發(fā)客戶端訪問(wèn)MySQL來(lái)進(jìn)行壓力測(cè)試。

2．tpcc-mysql：tpcc-mysql是Percona公司按照TPC-C開(kāi)發(fā)的工具，主要用于MySQL的壓力測(cè)試。

3．Sysbench：sysbench是一個(gè)主流的、開(kāi)源的、模塊化的、跨平臺(tái)的多線程性能d測(cè)試工具，可以用來(lái)對(duì)CPU、內(nèi)存、磁盤(pán)I/O、線程、數(shù)據(jù)庫(kù)進(jìn)行性能測(cè)試。sysbench目前支持的數(shù)據(jù)庫(kù)系統(tǒng)有MySQL、Oracle和PostgreSQL。項(xiàng)目知識(shí)導(dǎo)入Sysbench的使用方法:sysbench的安裝請(qǐng)參考任務(wù)6-3，或者直接在LinuxCentOS7中使用命令yuminstall-ysysbench進(jìn)行快捷安裝。安裝完成后，可以通過(guò)命令sysbench--help來(lái)查看sysbench支持哪些功能參數(shù)，命令及執(zhí)行結(jié)果如下。項(xiàng)目知識(shí)導(dǎo)入sysbench內(nèi)置如下測(cè)試。fileio：文件I/O測(cè)試。cpu：CPU性能測(cè)試。memory：內(nèi)存功能速度測(cè)試。threads：線程子系統(tǒng)性能測(cè)試。mutex：互斥鎖性能測(cè)試。項(xiàng)目知識(shí)導(dǎo)入Fileiosysbench提供了fileio測(cè)試功能可以執(zhí)行

sysbench--test=fileiohelp命令查看其使用方法項(xiàng)目知識(shí)導(dǎo)入cpu

sysbench提供了cpu測(cè)試功能，可以執(zhí)行sysbench--test=cpuhelp命令查看其使用方法項(xiàng)目知識(shí)導(dǎo)入Memorysysbench提供了memory測(cè)試功能可以執(zhí)行sysbench--test=memoryhelp命令查看其使用方法項(xiàng)目知識(shí)導(dǎo)入threadssysbench提供了threads測(cè)試功能可以執(zhí)行sysbench--test=threadshelp命令查看其使用方法。項(xiàng)目知識(shí)導(dǎo)入Mutexsysbench提供了mutex測(cè)試功能可以執(zhí)行sysbench--test=mutexhelp命令查看其使用方法項(xiàng)目知識(shí)導(dǎo)入二、mysqlslap壓力測(cè)試mysqlslap是一個(gè)MySQL官方提供的壓力測(cè)試工具。它通過(guò)模擬多個(gè)并發(fā)客戶端訪問(wèn)MySQL來(lái)進(jìn)行壓力測(cè)試，并且能很好地對(duì)比多個(gè)存儲(chǔ)引擎在相同環(huán)境下的并發(fā)壓力性能差別。選項(xiàng)名稱選項(xiàng)描述--auto-generate-sql自動(dòng)生成測(cè)試表和數(shù)據(jù)，表示用mysqlslap自己生成的SQL腳本來(lái)測(cè)試并發(fā)壓力。--auto-generate-sql-add-autoincrement對(duì)生成的表自動(dòng)添加auto_increment列。--auto-generate-sql-load-type測(cè)試語(yǔ)句的類型，取值包括：read，key，write，update和mixed(默認(rèn))。--auto-generate-sql-write-number每個(gè)線程執(zhí)行多少行的插入。--commit設(shè)置N條DML后提交一次。--compress如果服務(wù)器和客戶端支持都?jí)嚎s，則壓縮信息傳遞。--concurrency表示并發(fā)量，也就是模擬多少個(gè)客戶端同時(shí)執(zhí)行select?？芍付ǘ鄠€(gè)值，例如，--concurrency=100,200,500。--create-schema自定義的測(cè)試庫(kù)名稱。項(xiàng)目知識(shí)導(dǎo)入選項(xiàng)名稱選項(xiàng)描述--debug-info打印內(nèi)存和CPU的相關(guān)信息。--delimiter在SQL語(yǔ)句中使用的定界符。--detach執(zhí)行N條語(yǔ)句后斷開(kāi)重連。--engine要測(cè)試的引擎，可以有多個(gè)，用分隔符隔開(kāi)。例如，--engines=myisam,innodb--help顯示幫助信息。--hostMySQL服務(wù)器所在的主機(jī)。--iterations測(cè)試執(zhí)行的迭代次數(shù)，表示要在不同并發(fā)環(huán)境下，各自運(yùn)行測(cè)試多少次。--number-char-cols自動(dòng)生成的測(cè)試表中包含N個(gè)字符類型的列，默認(rèn)為1--number-int-cols自動(dòng)生成的測(cè)試表中包含N個(gè)數(shù)字類型的列，默認(rèn)為1--number-of-queries總的測(cè)試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))--only-print只打印測(cè)試語(yǔ)句而不實(shí)際執(zhí)行。--password連接服務(wù)器時(shí)使用的密碼。--port連接的TCP/IP端口號(hào)。--post-query包含要在測(cè)試完成后執(zhí)行的語(yǔ)句的文件或字符串。--query使用自定義腳本執(zhí)行測(cè)試，例如可以自定義一個(gè)存儲(chǔ)過(guò)程或者sql語(yǔ)句來(lái)執(zhí)行測(cè)試。--user連接服務(wù)器時(shí)要使用的MySQL賬戶名。項(xiàng)目知識(shí)導(dǎo)入二、mysqlslap壓力測(cè)試mysqlslap的運(yùn)行分為三個(gè)階段。創(chuàng)建架構(gòu)，表及可選的任何存儲(chǔ)程序或數(shù)據(jù)以供測(cè)試使用。此階段使用單個(gè)客戶端連接。運(yùn)行負(fù)載測(cè)試。此階段可以使用很多客戶端連接。清理（斷開(kāi)連接，刪除指定表）。此階段使用單個(gè)客戶端連接。項(xiàng)目知識(shí)導(dǎo)入三、性能調(diào)優(yōu)（一）查詢優(yōu)化據(jù)統(tǒng)計(jì)，80%的響應(yīng)時(shí)間問(wèn)題都是應(yīng)用了性能差的SQL語(yǔ)句造成的。那么如何定位并優(yōu)化慢查詢語(yǔ)句？具體場(chǎng)景需要具體分析，一般思路如下。（1）根據(jù)慢查詢?nèi)罩径ㄎ宦樵冋Z(yǔ)句。（2）使用explain等工具分析SQL語(yǔ)句。（3）修改SQL語(yǔ)句或者盡量利用索引。查詢優(yōu)化的原則是：最大化利用索引；盡可能地避免全表掃描；減少對(duì)無(wú)效數(shù)據(jù)的查詢項(xiàng)目知識(shí)導(dǎo)入explain工具是MySQL中常用的查詢分析工具，可以幫助分析select語(yǔ)句，以便管理員了解查詢效率低下的原因。需要注意的是，執(zhí)行explain并不會(huì)真正的執(zhí)行SQL，而是對(duì)SQL做了一些分析。1、explain工具項(xiàng)目知識(shí)導(dǎo)入explain列名稱explain列說(shuō)明id查詢的唯一標(biāo)識(shí)。select_type查詢類型。table查詢的那個(gè)表。partitions匹配的分區(qū)。type訪問(wèn)類型。possible_keys可能使用的索引。key最終使用的索引。key_len最終使用的索引的長(zhǎng)度。ref與索引一起被使用的字段或常數(shù)。rows查詢掃描的行數(shù)，是個(gè)估算值。filtered查詢條件所過(guò)濾的數(shù)據(jù)的百分比。Extra額外的信息。項(xiàng)目知識(shí)導(dǎo)入下面對(duì)其中的一些重點(diǎn)字段做詳細(xì)的介紹。(1)type:顯示的是訪問(wèn)類型，是較為重要的一個(gè)指標(biāo)，結(jié)果值從好到壞依次是：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL，一般來(lái)說(shuō)，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref，如右所示。type重要項(xiàng)項(xiàng)說(shuō)明system表僅有一行(=系統(tǒng)表)，這是const連接類型的一個(gè)特例。const單條記錄，系統(tǒng)會(huì)把匹配行中的其他列作為常數(shù)處理，如主鍵或唯一索引查詢。eq_ref類似ref,

區(qū)別在于使用的是唯一索引，使用主鍵的關(guān)聯(lián)查詢。ref使用非唯一索引掃描或唯一索引前綴掃描，返回單條記錄，常出現(xiàn)在關(guān)聯(lián)查詢中。ref_or_null如同ref,但是MySQL必須在初次查找的結(jié)果里找出null條目,然后進(jìn)行二次查找。index_merge說(shuō)明索引合并優(yōu)化被使用了。unique_subquery在某些IN查詢中使用此種類型,而不是常規(guī)的ref:valueIN(SELECTprimary_keyFROMsingle_tableWHEREsome_expr)。index_subquery在某些IN查詢中使用此種類型,與unique_subquery類似,但是查詢的是非唯一性索引:valueIN(SELECTkey_columnFROMsingle_tableWHEREsome_expr)range索引范圍掃描。只檢索給定范圍的行,使用一個(gè)索引來(lái)選擇行。key列顯示使用了哪個(gè)索引。當(dāng)使用=、<>、>、>=、<、<=、ISNULL、<=>、BETWEEN或者IN操作符,用常量比較關(guān)鍵字列時(shí),可以使用range。index索引全掃描。掃描表的時(shí)候按照索引次序進(jìn)行而不是行。主要優(yōu)點(diǎn)是避免了排序,但是開(kāi)銷(xiāo)仍然非常大。all最壞的情況,從頭到尾全表掃描。項(xiàng)目知識(shí)導(dǎo)入(2)Extra是另外一個(gè)很重要的字段，該字段顯示MySQL在查詢過(guò)程中的一些詳細(xì)信息Extra重要項(xiàng)項(xiàng)說(shuō)明Usingindex說(shuō)明查詢使用了覆蓋索引(CoveringIndex)，不需要讀取數(shù)據(jù)文件，從索引樹(shù)（索引文件）中即可獲得信息。Usingindexcondition索引條件推送，用于二級(jí)索引上，可以在索引上執(zhí)行如like這樣的操作，減少了不必要的IO操作。Usingwhere使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。Usingjoinbuffer使用了連接緩存：BlockNestedLoop,連接算法是塊嵌套循環(huán)連接；BatchedKeyAccess,連接算法是批量索引連接。impossiblewherewhere子句的值總是false,不能用來(lái)獲取任何元組。selecttablesoptimizedaway在沒(méi)有GROUPBY子句的情況下，基于索引優(yōu)化MIN/MAX操作，或者對(duì)于MyISAM存儲(chǔ)引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化。distinct優(yōu)化distinct操作，在找到第一匹配的元組后即停止找同樣值的動(dòng)作。Usingfilesort說(shuō)明MySQL會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。Usingtemporary使了用臨時(shí)表保存中間結(jié)果,MySQL在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于排序orderby和分組查詢groupby項(xiàng)目知識(shí)導(dǎo)入（二）表設(shè)計(jì)優(yōu)化1．表設(shè)計(jì)規(guī)范化

單表的設(shè)計(jì)首先要規(guī)范化，滿足第三范式，消除數(shù)據(jù)冗余。第一范式（1NF）：無(wú)重復(fù)的列。所謂1NF是指在關(guān)系模型中，對(duì)域添加的一個(gè)規(guī)范要求，所有的域都應(yīng)該是原子性的，即數(shù)據(jù)表的每一列都是不可分割的原子數(shù)據(jù)項(xiàng)，而不能是集合、數(shù)組、記錄等非原子數(shù)據(jù)項(xiàng)。第二范式（2NF）：屬性。在1NF的基礎(chǔ)上，非主屬性必須完全依賴于主碼（在1NF的基礎(chǔ)上消除非主屬性對(duì)主碼的部分函數(shù)依賴）。第三范式（3NF）：屬性。在1NF的基礎(chǔ)上，任何非主屬性不依賴于其他非主屬性，在2NF的基礎(chǔ)上消除傳遞依賴。項(xiàng)目知識(shí)導(dǎo)入2．?dāng)?shù)據(jù)類型優(yōu)化表設(shè)計(jì)過(guò)程中，首先要注意的就是MySQL的數(shù)據(jù)類型優(yōu)化。MySQL支持的數(shù)據(jù)類型非常多，選擇正確的數(shù)據(jù)類型對(duì)于實(shí)現(xiàn)高性能至關(guān)重要。不管存儲(chǔ)哪種類型的數(shù)據(jù)，下面幾個(gè)簡(jiǎn)單的原則都有助于提高數(shù)據(jù)庫(kù)的訪問(wèn)性能。（1）較小的數(shù)據(jù)類型通常更好。（2）簡(jiǎn)單就好。簡(jiǎn)單數(shù)據(jù)類型的操作通常需要更少的CPU周期。（3）盡量避免NULL。很多表都包含可為NULL的列，即使應(yīng)用程序并不需要保存NULL，這是因?yàn)榭蔀镹ULL是列的默認(rèn)屬性。通常情況下最好指定列為NOTNULL，除非真的需要存儲(chǔ)NULL。項(xiàng)目知識(shí)導(dǎo)入3．索引優(yōu)化索引用于快速查找具有特定列值的行。如果沒(méi)有索引，MySQL就必須從第一行開(kāi)始讀取整個(gè)表以找到相關(guān)的行。索引優(yōu)化一般遵循以下一些原則。（1）索引并不是越多越好，要根據(jù)查詢有針對(duì)性地創(chuàng)建索引，考慮給WHERE和ORDERBY子句上涉及的列建立索引，可根據(jù)explain來(lái)查看是用了索引還是全表掃描。（2）應(yīng)盡量避免在WHERE子句中對(duì)字段進(jìn)行NULL判斷，否則將導(dǎo)致存儲(chǔ)引擎放棄使用索引而進(jìn)行全表掃描。（3）值很少的字段不適合創(chuàng)建索引，例如“性別”這種只有兩個(gè)值的字段。（4）字符字段只創(chuàng)建前綴索引。（5）字符字段最好不要作為主鍵。（6）不用外鍵，由程序保證約束。（7）盡量不用UNIQUE，由程序保證約束。（8）使用多列索引時(shí)注意順序和查詢條件保持一致，同時(shí)刪除不必要的單列索引。項(xiàng)目知識(shí)導(dǎo)入（三）配置優(yōu)化配置優(yōu)化即對(duì)MySQL的配置文件中的參數(shù)進(jìn)行優(yōu)化?？梢酝ㄟ^(guò)啟動(dòng)時(shí)指定參數(shù)和使用配置文件兩種方法配置MySQL。下面對(duì)MySQL配置文件的常用參數(shù)做簡(jiǎn)單介紹。1．max_connectionsmax_connections表示MySQL的最大連接數(shù)2．back_logback_log表示MySQL能暫存的連接數(shù)量。項(xiàng)目知識(shí)導(dǎo)入3．interactive_timeoutinteractive_timeout表示交互連接在被服務(wù)器關(guān)閉前等待行動(dòng)的秒數(shù)。4．key_buffer_sizekey_buffer_size用于指定索引緩沖區(qū)的大小，它能夠決定索引處理的速度，尤其是索引讀的速度。通過(guò)檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。Key_reads/Key_read_requests的值應(yīng)該盡可能小，至少小于1:100，1:1000更好（上述狀態(tài)值可以使用showglobalstatuslike'key_read%'命令獲得）5．read_rnd_buffer_size

read_rnd_buffer_size表示隨機(jī)讀緩沖區(qū)大小。當(dāng)按任意順序（如按照排序順序）讀取行時(shí)，系統(tǒng)將分配一個(gè)隨機(jī)讀緩沖區(qū)。進(jìn)行排序查詢時(shí)，MySQL會(huì)首先掃描一遍該緩沖區(qū)，以避免搜索磁盤(pán)，提高查詢速度。如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但MySQL會(huì)為每個(gè)客戶連接分配緩沖區(qū)，所以應(yīng)適當(dāng)設(shè)置該值，以避免內(nèi)存開(kāi)銷(xiāo)過(guò)大，一般可將其設(shè)置為16MB。項(xiàng)目知識(shí)導(dǎo)入6.sort_buffer_size系統(tǒng)會(huì)為每個(gè)需要進(jìn)行排序的線程分配大小為sort_buffer_size的一個(gè)緩沖區(qū)。增加這個(gè)值可加速ORDERBY或GROUPBY操作。7．join_buffer_sizejoin_buffer_size表示聯(lián)合查詢操作使用的緩沖區(qū)大小。它與read_rnd_buffer_size、sort_buffer_size一樣，系統(tǒng)會(huì)為每個(gè)連接分配指定大小的聯(lián)合查詢操作緩沖區(qū)，也就是說(shuō)join_buffer_size的值為16MB，現(xiàn)在有100個(gè)線程連接，則服務(wù)器分配內(nèi)存大小為100×16MB。

8．table_cachetable_cache表示表高速緩沖區(qū)的大小。當(dāng)MySQL訪問(wèn)一個(gè)表時(shí)，如果表緩沖區(qū)中還有空間，該表就被打開(kāi)并放入其中，這樣可以更快地訪問(wèn)表的內(nèi)容。通過(guò)檢查峰值時(shí)間的狀態(tài)值Open_tables（當(dāng)前在緩沖區(qū)中打開(kāi)表的數(shù)量）和Opened_tables（自MySQL啟動(dòng)起打開(kāi)表的數(shù)量），可以決定是否需要增加table_cache的值。項(xiàng)目知識(shí)導(dǎo)入9．max_heap_table_sizemax_heap_table_size表示用戶可以創(chuàng)建的內(nèi)存表（MemoryTable）的大小。這個(gè)參數(shù)用來(lái)計(jì)算內(nèi)存表的最大行數(shù)。

10．tmp_table_size通過(guò)設(shè)置tmp_table_size參數(shù)可以增加一張臨時(shí)表（如通過(guò)高級(jí)GROUPBY操作生成的臨時(shí)表）的大小。如果調(diào)高該值，MySQL將同時(shí)增加heap表的大小，從而達(dá)到提高連接查詢速度的效果，建議盡量?jī)?yōu)化查詢，要確保查詢過(guò)程中生成的臨時(shí)表在內(nèi)存中，避免臨時(shí)表過(guò)大導(dǎo)致生成基于磁盤(pán)的MyISAM表。項(xiàng)目知識(shí)導(dǎo)入11．thread_cache_sizethread_cache_size表示保存在緩沖區(qū)中的線程的數(shù)量。12．thread_concurrency推薦將該值設(shè)置為服務(wù)器CPU核數(shù)的2倍

13．wait_timeoutwait_timeout用于指定一個(gè)請(qǐng)求的最大連接時(shí)間。wait_timeout的默認(rèn)值是28800秒（8小時(shí)），MySQL會(huì)自動(dòng)關(guān)閉空閑時(shí)間超過(guò)wait_timeout值的連接，防止連接數(shù)過(guò)多。對(duì)于4GB左右內(nèi)存的服務(wù)器可以將該值設(shè)置為5～10小時(shí)。項(xiàng)目任務(wù)分解任務(wù)6-1安裝MONyog工具監(jiān)控MySQL服務(wù)器任務(wù)說(shuō)明

MONyog支持查詢分析功能，能夠輕松找出MySQL的問(wèn)題所在。此外，它還可以幫助用戶掌握服務(wù)器的運(yùn)行狀態(tài)，并查看在任一時(shí)間點(diǎn)繪制的具有詳細(xì)查詢信息的圖表。本任務(wù)要求下載并安裝MONyog工具以監(jiān)控MySQL服務(wù)器。任務(wù)實(shí)施過(guò)程詳見(jiàn)任務(wù)手冊(cè)項(xiàng)目任務(wù)分解任務(wù)6-2對(duì)MySQL數(shù)據(jù)庫(kù)讀寫(xiě)性能調(diào)優(yōu)任務(wù)說(shuō)明

首先我們要知道，大多數(shù)性能優(yōu)化不是一項(xiàng)單純的工作，需要考慮方方面面，例如CPU、內(nèi)存、I/O、數(shù)據(jù)庫(kù)參數(shù)等，不是簡(jiǎn)單地修改參數(shù)就能讓數(shù)據(jù)庫(kù)“快”起來(lái)，雖然有時(shí)候修改參數(shù)確實(shí)能優(yōu)化數(shù)據(jù)庫(kù)性能。本任務(wù)要求通過(guò)調(diào)整來(lái)熟悉影響性能的主要參數(shù)，以便對(duì)性能優(yōu)化形成一定認(rèn)知。任務(wù)實(shí)施過(guò)程

詳見(jiàn)任務(wù)手冊(cè)項(xiàng)目任務(wù)分解任務(wù)6-3創(chuàng)建MySQL壓力測(cè)試報(bào)告任務(wù)說(shuō)明

sysbench是一個(gè)跨平臺(tái)且支持多線程的模塊化基準(zhǔn)測(cè)試工具，用于評(píng)估系統(tǒng)在運(yùn)行高負(fù)載的數(shù)據(jù)庫(kù)時(shí)相關(guān)核心參數(shù)的性能表現(xiàn)。本任務(wù)要求在LinuxCentOS7中安裝并使用sysbench完成對(duì)