SQL語句的優化

SQL ç¼ç¨è¯è¨ MySQL Java CPU Javaé«çº§æ¶æ Java高級架構 2017-11-22

SQL語句的優化

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

通過用戶反饋獲取存在性能問題的SQL
通過慢查日誌獲取存在性能問題的SQL
實時獲取存在性能問題的SQL

慢查詢日誌介紹

slow_quey_log=on 啟動記錄慢查詢日誌
slow_query_log_file 指定慢查詢日誌的存儲路徑及文件(默認情況下保存在MySQL的數據目錄中)
long_query_time 指定記錄慢查詢日誌sql執行的閾值（默認為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

慢查詢日誌分析工具

mysqldumpslow

彙總除查詢條件外其它完全相同的SQL並將分析結果按照參數中所指定的順序輸出
mysqldumpslow -s r -t 10 slow-mysql.log
-s order(c,t,l,r,at,al,ar)[指定按照哪種排序方式輸出結果]

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

c按照查詢的次數排序
t按照查詢的總時間排序
l按照查詢中鎖的時間來排序
r按照查詢中返回總的數據行來排序
at、al、ar平均數量來排序

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

實時獲取存在性能問題的SQL

select id,user,host,db,command,time,state,info
FROM information_schema.processlist
WHERE time>=60

查詢速度為什麼會這麼慢？

客戶端發送SQL請求給服務器
服務器檢查是否可以在查詢緩存中命中該SQL
服務器端進行SQL解析，預處理，再由優化器生成對應的執行計劃
根據執行計劃，調用存儲引擎API來查詢數據
將結果返回給客戶端

》對於一個讀寫頻繁的系統使用查詢緩存很可能會降低查詢處理的效率，建議大家不要使用查詢緩存

2.其中涉及的參數： query_cache_type 設置查詢緩存是否可用[ON,OFF,DEMAND] DEMAND表示只有在查詢語句中使用了SQL_CACHE和SQL_NO_CACHE來控制是否需要進行緩存 query_cache_size 設置查詢緩存的內存的大小 query_cache_limit 設置查詢緩存可用的存儲的最大值（加上SQL_NO_CACHE可以提高效率） query_cache_wlock_invalidate 設置數據表被鎖後是否返回緩存中的數據 query_cache_min_res_unit 設置查詢緩存分配的內存塊最小單位 3.MySQL依照這個執行計劃和存儲引擎進行交互 解析SQL，預處理。優化SQL的查詢計劃 語法解析階段是通過關鍵字對MySQL語句進行解析，並生成一顆對應的解析樹 MySQL解析器將使用MySQL語法規則驗證和解析查詢，包括檢查語法是否使用了正確的關鍵走；關鍵字的順序是否正確等等； 預處理階段是根據MySQL規則進一步檢查解析樹是否合法 檢查查詢中所涉及的表和數據列是否存在及名字或別名是否存在歧義等等 語法檢查通過了，查詢優化器就可以生成查詢計劃了 優化器SQL的查詢計劃階段對上一步所生成的執行計劃進行選擇基於成本模型的最優的執行計劃【下面是影響選擇最優的查詢計劃的7因素】 1.統計信息不準確 2.執行計劃中的成本估算不等於實際的執行計劃的成本 3.MySQL優化器認為的最優的可能與你認為最優的不一樣【基於成本模型選擇最優的執行計劃】 4.MySQL從不考慮其他的併發的查詢，這可能會影響當前查詢的速度 5.MySQL有時候也會基於一些固定的規則來生成執行計劃 6.MySQL不會考慮不受其控制的成本 查詢優化器在目前的版本中可以進行優化的SQL的類型： 1.重新定義表的關聯順序 2.將外連接轉化為內連接 3.使用等價變換規則 4.優化count(),min()和max()[select tables optimozed away] 5.將一個表達式轉化為一個常數表達式 6.子查詢優化 7.提前終止查詢 8.對in()條件進行優化

如何確定查詢處理各個階段所消耗的時間

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

set profiling = 1;[啟動profile,這是一個session級別的配置]
執行查詢
show profiles;[查看每一個查詢所消耗的總的時間的信息]
show profile for query N;[查詢的每個階段所消耗的時間]
show profile cpu for query N;[查看每個階段所消耗的時間信息和所消耗的cpu的信息]

使用performance_schema

啟動所需要的監控和歷史記錄表的信息
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查詢優化

大表的更新和刪除

 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;

如何修改大表的表結構
1.對錶中的列的字段類型進行修改改變字段的寬度時還是會進行鎖表
2.無法解決主從數據庫延遲的問題
修改的方法：
```
 pt-online-schema-change --alter="modify c varchar(150) not null default''" --user=root --password=PassWord D=testDataBaseName,t=tesTableName --charset=utf-8 --execute
```

如何優化not in和<>查詢

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

使用匯總表的方法進行優化

 #統計商品的評論數[優化前的SQL] select count(*) from product_comment where product_id=999; #彙總表就是提前以要統計的數據進行彙總並記錄到數據庫中以備後續的查詢使用 create table product_comment_cnt(product_id int,cnt int); #統計商品的評論數[優化後的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

1、具有1-5工作經驗的，面對目前流行的技術不知從何下手，

需要突破技術瓶頸的。2、在公司待久了，過得很安逸，

但跳槽時面試碰壁。需要在短時間內進修、跳槽拿高薪的。

3、如果沒有工作經驗，但基礎非常紮實，對java工作機制，

常用設計思想，常用java開發框架掌握熟練的。

4、覺得自己很牛B，一般需求都能搞定。

但是所學的知識點沒有系統化，很難在技術領域繼續突破的。

5. 群號：468897908高級架構群備註好信息！

6.阿里Java高級大牛直播講解知識點，分享知識，

多年工作經驗的梳理和總結，帶著大家全面、

科學地建立自己的技術體系和技術認知！

SQL語句的優化

相關推薦