一、SQL查詢優(yōu)化（重要）
1.1 獲取有性能問題SQL的三種方式
通過用戶反饋獲取存在性能問題的SQL；
通過慢查日志獲取存在性能問題的SQL；
實(shí)時(shí)獲取存在性能問題的SQL；
1.1.2 慢查日志分析工具
相關(guān)配置參數(shù)：
<pre>slow_query_log # 啟動(dòng)停止記錄慢查日志，慢查詢?nèi)罩灸J(rèn)是沒有開啟的可以在配置文件中開啟(on) slow_query_log_file # 指定慢查日志的存儲(chǔ)路徑及文件，日志存儲(chǔ)和數(shù)據(jù)從存儲(chǔ)應(yīng)該分開存儲(chǔ) long_query_time # 指定記錄慢查詢?nèi)罩維QL執(zhí)行時(shí)間的閥值默認(rèn)值為10秒通常,對(duì)于一個(gè)繁忙的系統(tǒng)來說,改為0.001秒(1毫秒)比較合適 log_queries_not_using_indexes #是否記錄未使用索引的SQL</pre>
常用工具：mysqldumpslow和pt-query-digest
<pre>pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord slow-mysql.log</pre>
10年架構(gòu)師領(lǐng)你架構(gòu)-成長之路-（附面試題（含答案））
（騰訊T3-T4）打造互聯(lián)網(wǎng)PHP架構(gòu)師教程目錄大全，只要你看完，薪資立馬提升2倍（持續(xù)更新）
1.1.3 實(shí)時(shí)獲取有性能問題的Sad-imageshu.io/upload_images/20190641-791ae20aa9662a09.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
?
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。
1.2 SQL的解析預(yù)處理及生成執(zhí)行計(jì)劃（重要）
1.2.1 查詢過程描述（重點(diǎn)?。。。?/h3>
![]()
image
![]()
image.gif
?
通過上圖可以清晰的了解到MySql查詢執(zhí)行的大致過程：
發(fā)送SQL語句。
查詢緩存，如果命中緩存直接返回結(jié)果。
SQL解析，預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的查詢執(zhí)行計(jì)劃。
執(zhí)行查詢，調(diào)用存儲(chǔ)引擎API獲取數(shù)據(jù)。
返回結(jié)果。
1.2.2 查詢緩存對(duì)性能的影響（建議關(guān)閉緩存）
第一階段： 相關(guān)配置參數(shù)：
<pre>query_cache_type # 設(shè)置查詢緩存是否可用 query_cache_size # 設(shè)置查詢緩存的內(nèi)存大小 query_cache_limit # 設(shè)置查詢緩存可用的存儲(chǔ)最大值（加上sql_no_cache可以提高效率） query_cache_wlock_invalidate # 設(shè)置數(shù)據(jù)表被鎖后是否返回緩存中的數(shù)據(jù) query_cache_min_res_unit # 設(shè)置查詢緩存分配的內(nèi)存塊的最小單</pre>
緩存查找是利用對(duì)大小寫敏感的哈希查找來實(shí)現(xiàn)的，Hash查找只能進(jìn)行全值查找（sql完全一致），如果緩存命中，檢查用戶權(quán)限，如果權(quán)限允許，直接返回，查詢不被解析，也不會(huì)生成查詢計(jì)劃。
在一個(gè)讀寫比較頻繁的系統(tǒng)中，建議關(guān)閉緩存，因?yàn)榫彺娓聲?huì)加鎖。將query_cache_type設(shè)置為off,query_cache_size設(shè)置為0。
1.2.3 第二階段：MySQL依照?qǐng)?zhí)行計(jì)劃和存儲(chǔ)引擎進(jìn)行交互
這個(gè)階段包括了多個(gè)子過程：
?
?
?
一條查詢可以有多種查詢方式，查詢優(yōu)化器會(huì)對(duì)每一種查詢方式的（存儲(chǔ)引擎）統(tǒng)計(jì)信息進(jìn)行比較，找到成本最低的查詢方式，這也就是索引不能太多的原因。
1.3 會(huì)造成MySQL生成錯(cuò)誤的執(zhí)行計(jì)劃的原因
1、統(tǒng)計(jì)信息不準(zhǔn)確 2、成本估算與實(shí)際的執(zhí)行計(jì)劃成本不同
?
3、給出的最優(yōu)執(zhí)行計(jì)劃與估計(jì)的不同
?
4、MySQL不考慮并發(fā)查詢 5、會(huì)基于固定規(guī)則生成執(zhí)行計(jì)劃 6、MySQL不考慮不受其控制的成本，如存儲(chǔ)過程，用戶自定義函數(shù)
感謝大家一直來支持，這是我準(zhǔn)備的1000粉絲福利
【1000粉絲福利】10年架構(gòu)師分享PHP進(jìn)階架構(gòu)資料，助力大家都能30K
1.4 MySQL優(yōu)化器可優(yōu)化的SQL類型
查詢優(yōu)化器：對(duì)查詢進(jìn)行優(yōu)化并查詢mysql認(rèn)為的成本最低的執(zhí)行計(jì)劃。 為了生成最優(yōu)的執(zhí)行計(jì)劃，查詢優(yōu)化器會(huì)對(duì)一些查詢進(jìn)行改寫
可以優(yōu)化的sql類型
1、重新定義表的關(guān)聯(lián)順序；
?
2、將外連接轉(zhuǎn)換為內(nèi)連接；
3、使用等價(jià)變換規(guī)則；
?
4、優(yōu)化count(),min(),max()；
?
5、將一個(gè)表達(dá)式轉(zhuǎn)換為常數(shù)； 6、子查詢優(yōu)化；
?
7、提前終止查詢，如發(fā)現(xiàn)一個(gè)不成立條件(如where id = -1)，立即返回一個(gè)空結(jié)果；
8、對(duì)in()條件進(jìn)行優(yōu)化；
1.5 查詢處理各個(gè)階段所需要的時(shí)間
1.5.1 使用profile(目前已經(jīng)不推薦使用了)
<pre>set profiling = 1; #啟動(dòng)profile,這是一個(gè)session級(jí)的配制執(zhí)行查詢 show profiles; # 查詢每一個(gè)查詢所消耗的總時(shí)間的信息 show profiles for query N; # 查詢的每個(gè)階段所消耗的時(shí)間</pre>
1.5.2 performance_schema是5.5引入的一個(gè)性能分析引擎（5.5版本時(shí)期開銷比較大）
啟動(dòng)監(jiān)控和歷史記錄表：use performance_schema
<pre>update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%'; update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';</pre>
?
?
1.6 特定SQL的查詢優(yōu)化
1.6.1 大表的數(shù)據(jù)修改
?
?
1.6.2 大表的結(jié)構(gòu)修改
?
利用主從復(fù)制，先對(duì)從服務(wù)器進(jìn)入修改，然后主從切換
（推薦）
大廠2000道面試題（含答案）
PHP面試題匯總，看完這些面試題助力你面試成功，工資必有20-25K
添加一個(gè)新表（修改后的結(jié)構(gòu)），老表數(shù)據(jù)導(dǎo)入新表，老表建立觸發(fā)器，修改數(shù)據(jù)同步到新表， 老表加一個(gè)排它鎖（重命名）， 新表重命名， 刪除老表。
?
修改語句這個(gè)樣子：
<pre>alter table sbtest4 modify c varchar(150) not null default ''</pre>
利用工具修改：
?
1.6.3 優(yōu)化not in 和 <> 查詢
子查詢改寫為關(guān)聯(lián)查詢：
?
二、分庫分表
2.1 分庫分表的幾種方式
分擔(dān)讀負(fù)載 可通過 一主多從，升級(jí)硬件來解決。
2.1.1 把一個(gè)實(shí)例中的多個(gè)數(shù)據(jù)庫拆分到不同實(shí)例（集群）
?
拆分簡單,不允許跨庫。但并不能減少寫負(fù)載。
2.1.2 把一個(gè)庫中的表分離到不同的數(shù)據(jù)庫中
?
該方式只能在一定時(shí)間內(nèi)減少寫壓力。
以上兩種方式只能暫時(shí)解決讀寫性能問題。
2.1.3 數(shù)據(jù)庫分片
對(duì)一個(gè)庫中的相關(guān)表進(jìn)行水平拆分到不同實(shí)例的數(shù)據(jù)庫中
?
2.1.3.1 如何選擇分區(qū)鍵
分區(qū)鍵要能盡可能避免跨分區(qū)查詢的發(fā)生
分區(qū)鍵要盡可能使各個(gè)分區(qū)中的數(shù)據(jù)平均
2.1.3.2 分片中如何生成全局唯一ID
?
喜歡我的文章就關(guān)注我吧，持續(xù)更新中.....
以上內(nèi)容希望幫助到大家，很多PHPer在進(jìn)階的時(shí)候總會(huì)遇到一些問題和瓶頸，業(yè)務(wù)代碼寫多了沒有方向感，不知道該從那里入手去提升，對(duì)此我整理了一些資料，包括但不限于：分布式架構(gòu)、高可擴(kuò)展、高性能、高并發(fā)、服務(wù)器性能調(diào)優(yōu)、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql優(yōu)化、shell腳本、Docker、微服務(wù)、Nginx等多個(gè)知識(shí)點(diǎn)高級(jí)進(jìn)階干貨需要的可以免費(fèi)分享給大家，需要的可以點(diǎn)擊進(jìn)入暗號(hào)：知乎。
