一、SQL查詢(xún)優(yōu)化(重要)

1.1 獲取有性能問(wèn)題SQL的三種方式

通過(guò)用戶(hù)反饋獲取存在性能問(wèn)題的SQL；

通過(guò)慢查日志獲取存在性能問(wèn)題的SQL；

實(shí)時(shí)獲取存在性能問(wèn)題的SQL；

1.1.2 慢查日志分析工具

相關(guān)配置參數(shù)：

slow_query_log?#?啟動(dòng)停止記錄慢查日志，慢查詢(xún)?nèi)罩灸J(rèn)是沒(méi)有開(kāi)啟的可以在配置文件中開(kāi)啟(on)
slow_query_log_file?#?指定慢查日志的存儲(chǔ)路徑及文件，日志存儲(chǔ)和數(shù)據(jù)從存儲(chǔ)應(yīng)該分開(kāi)存儲(chǔ)

long_query_time?#?指定記錄慢查詢(xún)?nèi)罩維QL執(zhí)行時(shí)間的閥值默認(rèn)值為10秒通常,對(duì)于一個(gè)繁忙的系統(tǒng)來(lái)說(shuō),改為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

1.1.3 實(shí)時(shí)獲取有性能問(wèn)題的SQL(推薦)

SELECT?id,user,host,DB,command,time,state,info
FROM?information_schema.processlist
WHERE?TIME>=60

查詢(xún)當(dāng)前服務(wù)器執(zhí)行超過(guò)60s的SQL，可以通過(guò)腳本周期性的來(lái)執(zhí)行這條SQL，就能查出有問(wèn)題的SQL。

1.2 SQL的解析預(yù)處理及生成執(zhí)行計(jì)劃(重要)

1.2.1 查詢(xún)過(guò)程描述(重點(diǎn)！！！)

通過(guò)上圖可以清晰的了解到MySql查詢(xún)執(zhí)行的大致過(guò)程：

發(fā)送SQL語(yǔ)句。

查詢(xún)緩存，如果命中緩存直接返回結(jié)果。

SQL解析，預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的查詢(xún)執(zhí)行計(jì)劃。

執(zhí)行查詢(xún)，調(diào)用存儲(chǔ)引擎API獲取數(shù)據(jù)。

返回結(jié)果。

1.2.2 查詢(xún)緩存對(duì)性能的影響(建議關(guān)閉緩存)

第一階段：相關(guān)配置參數(shù)：

query_cache_type?#?設(shè)置查詢(xún)緩存是否可用
query_cache_size?#?設(shè)置查詢(xún)緩存的內(nèi)存大小
query_cache_limit?#?設(shè)置查詢(xún)緩存可用的存儲(chǔ)最大值(加上sql_no_cache可以提高效率)
query_cache_wlock_invalidate?#?設(shè)置數(shù)據(jù)表被鎖后是否返回緩存中的數(shù)據(jù)
query_cache_min_res_unit?#?設(shè)置查詢(xún)緩存分配的內(nèi)存塊的最小單
緩存查找是利用對(duì)大小寫(xiě)敏感的哈希查找來(lái)實(shí)現(xiàn)的，Hash查找只能進(jìn)行全值查找(sql完全一致)，如果緩存命中，檢查用戶(hù)權(quán)限，如果權(quán)限允許，直接返回，查詢(xún)不被解析，也不會(huì)生成查詢(xún)計(jì)劃。

在一個(gè)讀寫(xiě)比較頻繁的系統(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è)子過(guò)程：

??

??

??

一條查詢(xún)可以有多種查詢(xún)方式，查詢(xún)優(yōu)化器會(huì)對(duì)每一種查詢(xún)方式的(存儲(chǔ)引擎)統(tǒng)計(jì)信息進(jìn)行比較，找到成本最低的查詢(xú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ā)查詢(xún)????5、會(huì)基于固定規(guī)則生成執(zhí)行計(jì)劃????6、MySQL不考慮不受其控制的成本，如存儲(chǔ)過(guò)程，用戶(hù)自定義函數(shù)

1.4 MySQL優(yōu)化器可優(yōu)化的SQL類(lèi)型

查詢(xún)優(yōu)化器：對(duì)查詢(xún)進(jìn)行優(yōu)化并查詢(xún)mysql認(rèn)為的成本最低的執(zhí)行計(jì)劃。為了生成最優(yōu)的執(zhí)行計(jì)劃，查詢(xún)優(yōu)化器會(huì)對(duì)一些查詢(xún)進(jìn)行改寫(xiě)

??可以?xún)?yōu)化的sql類(lèi)型

? 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、子查詢(xún)優(yōu)化；

??

? 7、提前終止查詢(xún)，如發(fā)現(xiàn)一個(gè)不成立條件(如where id = -1)，立即返回一個(gè)空結(jié)果；

? 8、對(duì)in()條件進(jìn)行優(yōu)化；

1.5 查詢(xún)處理各個(gè)階段所需要的時(shí)間

1.5.1 使用profile(目前已經(jīng)不推薦使用了)

set?profiling?=?1;?#啟動(dòng)profile,這是一個(gè)session級(jí)的配制執(zhí)行查詢(xún)

show?profiles;?#?查詢(xún)每一個(gè)查詢(xún)所消耗的總時(shí)間的信息

show?profiles?for?query?N;?#?查詢(xún)的每個(gè)階段所消耗的時(shí)間

1.5.2 performance_schema是5.5引入的一個(gè)性能分析引擎(5.5版本時(shí)期開(kāi)銷(xiāo)比較大)

啟動(dòng)監(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%';

????

????

1.6 特定SQL的查詢(xún)優(yōu)化

1.6.1 大表的數(shù)據(jù)修改

????

????

1.6.2 大表的結(jié)構(gòu)修改

對(duì)表中的列的字段類(lèi)型進(jìn)行修改

改變字段的寬度時(shí)還是會(huì)鎖表

無(wú)法解決主從數(shù)據(jù)庫(kù)延遲的問(wèn)題

利用主從復(fù)制，先對(duì)從服務(wù)器進(jìn)入修改，然后主從切換

(推薦)

添加一個(gè)新表(修改后的結(jié)構(gòu))，老表數(shù)據(jù)導(dǎo)入新表，老表建立觸發(fā)器，修改數(shù)據(jù)同步到新表， 老表加一個(gè)排它鎖(重命名)， 新表重命名， 刪除老表。

????

修改語(yǔ)句這個(gè)樣子：

alter?table?sbtest4?modify?c?varchar(150)?not?null?default?''

利用工具修改：

????

1.6.3 優(yōu)化not in 和 <> 查詢(xún)

??子查詢(xún)改寫(xiě)為關(guān)聯(lián)查詢(xún)：

SELECT?customer_id,first_name,last_name,email?
FROM?customer
WHERE?customer_id
NOT?IN?(SELECT?customer_id?FROM?payment)?

改寫(xiě)后：

SELECT?a.customer_id,a.first_name,a.last_name,a.email?
FROM?customer?a
LEFT?JOIN?payment?b?ON?a.customer_id?=?b.customer_id
WHERE?b.customer_id?IS?NULL

二、分庫(kù)分表

2.1 分庫(kù)分表的幾種方式

分擔(dān)讀負(fù)載 可通過(guò) 一主多從，升級(jí)硬件來(lái)解決。

2.1.1 把一個(gè)實(shí)例中的多個(gè)數(shù)據(jù)庫(kù)拆分到不同實(shí)例(集群)

拆分簡(jiǎn)單,不允許跨庫(kù)。但并不能減少寫(xiě)負(fù)載。

2.1.2 把一個(gè)庫(kù)中的表分離到不同的數(shù)據(jù)庫(kù)中

該方式只能在一定時(shí)間內(nèi)減少寫(xiě)壓力。

以上兩種方式只能暫時(shí)解決讀寫(xiě)性能問(wèn)題。

2.1.3 ?數(shù)據(jù)庫(kù)分片

對(duì)一個(gè)庫(kù)中的相關(guān)表進(jìn)行水平拆分到不同實(shí)例的數(shù)據(jù)庫(kù)中

2.1.3.1 如何選擇分區(qū)鍵

分區(qū)鍵要能盡可能避免跨分區(qū)查詢(xún)的發(fā)生

分區(qū)鍵要盡可能使各個(gè)分區(qū)中的數(shù)據(jù)平均

2.1.3.2 分片中如何生成全局唯一ID

使用auto_increment_increment和auto_increment_offset參數(shù)使用全局節(jié)點(diǎn)來(lái)生成ID在Redis等緩存服務(wù)器中創(chuàng)建全局ID(推薦)

完！

唐成勇

https://segmentfault.com/a/1190000013781544

總結(jié)

以上是生活随笔為你收集整理的linux数据泵导入command not found_MySQL：数据库结构优化、高可用架构设计、数据库索引优化...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。