SQL語句的優(yōu)化

如何索取有性能問題SQL的渠道

通過用戶反饋獲取存在性能問題的SQL

通過慢查日志獲取存在性能問題的SQL

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

慢查詢?nèi)罩窘榻B

slow_quey_log=on 啟動(dòng)記錄慢查詢?nèi)罩?/p>

slow_query_log_file 指定慢查詢?nèi)罩镜拇鎯?chǔ)路徑及文件(默認(rèn)情況下保存在MySQL的數(shù)據(jù)目錄中)

long_query_time 指定記錄慢查詢?nèi)罩緎ql執(zhí)行的閾值（默認(rèn)為10秒，通常改為0.001秒比較合適）

log_queries_not_using_indexes 是否記錄未使用索引的SQL

set global sql_query_log=on;

sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=tests --mysql-user=sbtest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock run

慢查詢?nèi)罩痉治龉ぞ?/p>

mysqldumpslow

匯總除查詢條件外其它完全相同的SQL并將分析結(jié)果按照參數(shù)中所指定的順序輸出

mysqldumpslow -s r -t 10 slow-mysql.log

-s order(c,t,l,r,at,al,ar)[指定按照哪種排序方式輸出結(jié)果]

t top[指定取前幾條作為結(jié)束輸出]

c按照查詢的次數(shù)排序

t按照查詢的總時(shí)間排序

l按照查詢中鎖的時(shí)間來排序

r按照查詢中返回總的數(shù)據(jù)行來排序

at绞呈、al艺智、ar平均數(shù)量來排序

pt-query-digest

pt-query-digest \

--explain h=127.0.0.1,u=root,p=p@ssWord \

slow-mysql.log

pt-query-digest --explain h=127.0.0.1 slow-mysql.log > slow.rep

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

select id,user,host,db,command,time,state,info

FROM information_schema.processlist

WHERE time>=60

查詢速度為什么會(huì)這麼慢志鹃？

客戶端發(fā)送SQL請(qǐng)求給服務(wù)器

服務(wù)器檢查是否可以在查詢緩存中命中該SQL

服務(wù)器端進(jìn)行SQL解析，預(yù)處理曹铃，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃

根據(jù)執(zhí)行計(jì)劃缰趋，調(diào)用存儲(chǔ)引擎API來查詢數(shù)據(jù)

將結(jié)果返回給客戶端

》 對(duì)于一個(gè)讀寫頻繁的系統(tǒng)使用查詢緩存很可能會(huì)降低查詢處理的效率，建議大家不要使用查詢緩存

2.其中涉及的參數(shù)： query_cache_type 設(shè)置查詢緩存是否可用[ON,OFF,DEMAND] DEMAND表示只有在查詢語句中使用了SQL_CACHE和SQL_NO_CACHE來控制是否需要進(jìn)行緩存 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)存塊最小單位 3.MySQL依照這個(gè)執(zhí)行計(jì)劃和存儲(chǔ)引擎進(jìn)行交互 解析SQL陕见，預(yù)處理直撤。優(yōu)化SQL的查詢計(jì)劃 語法解析階段是通過關(guān)鍵字對(duì)MySQL語句進(jìn)行解析，并生成一顆對(duì)應(yīng)的解析樹 MySQL解析器將使用MySQL語法規(guī)則驗(yàn)證和解析查詢蜕着，包括檢查語法是否使用了正確的關(guān)鍵走谋竖；關(guān)鍵字的順序是否正確等等； 預(yù)處理階段是根據(jù)MySQL規(guī)則進(jìn)一步檢查解析樹是否合法 檢查查詢中所涉及的表和數(shù)據(jù)列是否存在及名字或別名是否存在歧義等等 語法檢查通過了承匣，查詢優(yōu)化器就可以生成查詢計(jì)劃了 優(yōu)化器SQL的查詢計(jì)劃階段對(duì)上一步所生成的執(zhí)行計(jì)劃進(jìn)行選擇基于成本模型的最優(yōu)的執(zhí)行計(jì)劃【下面是影響選擇最優(yōu)的查詢計(jì)劃的7因素】 1.統(tǒng)計(jì)信息不準(zhǔn)確 2.執(zhí)行計(jì)劃中的成本估算不等于實(shí)際的執(zhí)行計(jì)劃的成本 3.MySQL優(yōu)化器認(rèn)為的最優(yōu)的可能與你認(rèn)為最優(yōu)的不一樣【基于成本模型選擇最優(yōu)的執(zhí)行計(jì)劃】 4.MySQL從不考慮其他的并發(fā)的查詢蓖乘，這可能會(huì)影響當(dāng)前查詢的速度 5.MySQL有時(shí)候也會(huì)基于一些固定的規(guī)則來生成執(zhí)行計(jì)劃 6.MySQL不會(huì)考慮不受其控制的成本 查詢優(yōu)化器在目前的版本中可以進(jìn)行優(yōu)化的SQL的類型： 1.重新定義表的關(guān)聯(lián)順序 2.將外連接轉(zhuǎn)化為內(nèi)連接 3.使用等價(jià)變換規(guī)則 4.優(yōu)化count(),min()和max()[select tables optimozed away] 5.將一個(gè)表達(dá)式轉(zhuǎn)化為一個(gè)常數(shù)表達(dá)式 6.子查詢優(yōu)化 7.提前終止查詢 8.對(duì)in()條件進(jìn)行優(yōu)化

如何確定查詢處理各個(gè)階段所消耗的時(shí)間

使用profile[不建議使用，未來mysql中將被移除]

set profiling = 1;[啟動(dòng)profile,這是一個(gè)session級(jí)別的配置]

執(zhí)行查詢

show profiles;[查看每一個(gè)查詢所消耗的總的時(shí)間的信息]

show profile for query N;[查詢的每個(gè)階段所消耗的時(shí)間]

show profile cpu for query N;[查看每個(gè)階段所消耗的時(shí)間信息和所消耗的cpu的信息]

使用performance_schema

啟動(dòng)所需要的監(jiān)控和歷史記錄表的信息

update setup_instruments set enabled='yes',timed='yes' where name like 'stage%';

update setup_consumers set enabled='yes' where name like 'events%';

SELECT

a.thread_id,

sql_text,

c.event_name,

(c.timer_end - c.timer_start) / 1000000000 AS 'duration(ms)'

FROM

events_statements_history_long a

JOIN threads b on a.thread_id=b.thread_id

JOIN events_stages_history_long c ON c.thread_id=b.thread_id

AND c.event_id between a.event_id and a.end_event_id

WHERE b.processlist_id=CONNECTION_ID()

AND a.event_name='statement/sql/select'

ORDER BY a.thread_id,c.event_id

特定的SQL查詢優(yōu)化

大表的更新和刪除

delimiter $$ use 'imooc'$$ drop procedure if exists 'p_delete_rows'$$ create definer='root'@'127.0.0.1' procedure 'p_delete_rows'() begin declare v_rows int; set v_rows int, while v_rows=1, while v_rows>0 do delete from test where id>=9000 and id<=19000 limit 5000; select row_count() into v_rows; select sleep(5); end while; end $$ delimiter;

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

1.對(duì)表中的列的字段類型進(jìn)行修改改變字段的寬度時(shí)還是會(huì)進(jìn)行鎖表

2.無法解決主從數(shù)據(jù)庫延遲的問題

修改的方法：

pt-online-schema-change --alter="modify c varchar(150) not null default''" --user=root --password=PassWord D=testDataBaseName,t=tesTableName --charset=utf-8 --execute

如何優(yōu)化not in和<>查詢

#原始的SQL語句 SELECT customer_id, first_name, last_name, email FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM payment ) #優(yōu)化后的SQL語句 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

使用匯總表的方法進(jìn)行優(yōu)化

#統(tǒng)計(jì)商品的評(píng)論數(shù)[優(yōu)化前的SQL] select count(*) from product_comment where product_id=999; #匯總表就是提前以要統(tǒng)計(jì)的數(shù)據(jù)進(jìn)行匯總并記錄到數(shù)據(jù)庫中以備后續(xù)的查詢使用 create table product_comment_cnt(product_id int,cnt int); #統(tǒng)計(jì)商品的評(píng)論數(shù)[優(yōu)化后的SQL] select sum(cnt) from( select cnt from product_comment_cnt where product_id=999 union all select count(*) from product_comment where product_id=999 and timestr>DATE(NOW()) ) a