這篇文章主要講解了“怎么掌握MySQL查詢優(yōu)化和分庫分表”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“怎么掌握mysql查詢優(yōu)化和分庫分表”吧！
網(wǎng)站設(shè)計制作過程拒絕使用模板建站；使用PHP+MYSQL原生開發(fā)可交付網(wǎng)站源代碼;符合網(wǎng)站優(yōu)化排名的后臺管理系統(tǒng)；成都網(wǎng)站建設(shè)、網(wǎng)站制作收費合理；免費進(jìn)行網(wǎng)站備案等企業(yè)網(wǎng)站建設(shè)一條龍服務(wù).我們是一家持續(xù)穩(wěn)定運(yùn)營了十余年的創(chuàng)新互聯(lián)網(wǎng)站建設(shè)公司。
重慶分公司
重慶分公司
重要）通過用戶反饋獲取存在性能問題的SQL；
通過慢查日志獲取存在性能問題的SQL；
實時獲取存在性能問題的SQL；
相關(guān)配置參數(shù)：
slow_query_log # 啟動停止記錄慢查日志，慢查詢?nèi)罩灸J(rèn)是沒有開啟的可以在配置文件中開啟(on) slow_query_log_file # 指定慢查日志的存儲路徑及文件，日志存儲和數(shù)據(jù)從存儲應(yīng)該分開存儲 long_query_time # 指定記錄慢查詢?nèi)罩維QL執(zhí)行時間的閥值默認(rèn)值為10秒通常,對于一個繁忙的系統(tǒng)來說,改為0.001秒(1毫秒)比較合適 log_queries_not_using_indexes #是否記錄未使用索引的SQL
常用工具：mysqldumpslow和pt-query-digest
pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord slow-mysql.log
SELECT id,user,host,DB,command,time,state,info FROM information_schema.processlist WHERE TIME>=60
查詢當(dāng)前服務(wù)器執(zhí)行超過60s的SQL，可以通過腳本周期性的來執(zhí)行這條SQL，就能查出有問題的SQL。
重要）重點?。。?/code>）
上圖原文連接
發(fā)送SQL語句。
查詢緩存，如果命中緩存直接返回結(jié)果。
SQL解析，預(yù)處理，再由優(yōu)化器生成對應(yīng)的查詢執(zhí)行計劃。
執(zhí)行查詢，調(diào)用存儲引擎API獲取數(shù)據(jù)。
返回結(jié)果。
第一階段：
相關(guān)配置參數(shù)：
query_cache_type # 設(shè)置查詢緩存是否可用 query_cache_size # 設(shè)置查詢緩存的內(nèi)存大小 query_cache_limit # 設(shè)置查詢緩存可用的存儲最大值（加上sql_no_cache可以提高效率） query_cache_wlock_invalidate # 設(shè)置數(shù)據(jù)表被鎖后是否返回緩存中的數(shù)據(jù) query_cache_min_res_unit # 設(shè)置查詢緩存分配的內(nèi)存塊的最小單
緩存查找是利用對大小寫敏感的哈希查找來實現(xiàn)的，Hash查找只能進(jìn)行全值查找（sql完全一致），如果緩存命中，檢查用戶權(quán)限，如果權(quán)限允許，直接返回，查詢不被解析，也不會生成查詢計劃。在一個讀寫比較頻繁的系統(tǒng)中，建議關(guān)閉緩存，因為緩存更新會加鎖。將query_cache_type設(shè)置為off,query_cache_size設(shè)置為0。這個階段包括了多個子過程：
一條查詢可以有多種查詢方式，查詢優(yōu)化器會對每一種查詢方式的（存儲引擎）統(tǒng)計信息進(jìn)行比較，找到成本最低的查詢方式，這也就是索引不能太多的原因。 1、統(tǒng)計信息不準(zhǔn)確
2、成本估算與實際的執(zhí)行計劃成本不同
3、給出的最優(yōu)執(zhí)行計劃與估計的不同
4、MySQL不考慮并發(fā)查詢
5、會基于固定規(guī)則生成執(zhí)行計劃
6、MySQL不考慮不受其控制的成本，如存儲過程，用戶自定義函數(shù)
查詢優(yōu)化器：對查詢進(jìn)行優(yōu)化并查詢mysql認(rèn)為的成本最低的執(zhí)行計劃。 為了生成最優(yōu)的執(zhí)行計劃，查詢優(yōu)化器會對一些查詢進(jìn)行改寫
可以優(yōu)化的sql類型
1、重新定義表的關(guān)聯(lián)順序；
2、將外連接轉(zhuǎn)換為內(nèi)連接；
3、使用等價變換規(guī)則；
4、優(yōu)化count(),min(),max()；
5、將一個表達(dá)式轉(zhuǎn)換為常數(shù)；
6、子查詢優(yōu)化；
7、提前終止查詢，如發(fā)現(xiàn)一個不成立條件(如where id = -1)，立即返回一個空結(jié)果；
8、對in()條件進(jìn)行優(yōu)化；
set profiling = 1; #啟動profile,這是一個session級的配制執(zhí)行查詢 show profiles; # 查詢每一個查詢所消耗的總時間的信息 show profiles for query N; # 查詢的每個階段所消耗的時間
啟動監(jiān)控和歷史記錄表：use performance_schema
update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%'; update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';
利用主從復(fù)制，先對從服務(wù)器進(jìn)入修改，然后主從切換
（推薦）
添加一個新表（修改后的結(jié)構(gòu)），老表數(shù)據(jù)導(dǎo)入新表，老表建立觸發(fā)器，修改數(shù)據(jù)同步到新表， 老表加一個排它鎖（重命名）， 新表重命名， 刪除老表。
修改語句這個樣子：
alter table sbtest4 modify c varchar(150) not null default ''
利用工具修改：
子查詢改寫為關(guān)聯(lián)查詢：
分擔(dān)讀負(fù)載 可通過 一主多從，升級硬件來解決。
拆分簡單,不允許跨庫。但并不能減少寫負(fù)載。
該方式只能在一定時間內(nèi)減少寫壓力。
以上兩種方式只能暫時解決讀寫性能問題。
對一個庫中的相關(guān)表進(jìn)行水平拆分到不同實例的數(shù)據(jù)庫中
分區(qū)鍵要能盡可能避免跨分區(qū)查詢的發(fā)生
分區(qū)鍵要盡可能使各個分區(qū)中的數(shù)據(jù)平均
感謝各位的閱讀，以上就是“怎么掌握mysql查詢優(yōu)化和分庫分表”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對怎么掌握mysql查詢優(yōu)化和分庫分表這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！
