SQL語句的優化

Panda_XiaoXi發表於2017-11-14
SQL優化

SQL語句的優化

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

  1. 通過使用者反饋獲取存在效能問題的SQL

  2. 通過慢查日誌獲取存在效能問題的SQL

  3. 實時獲取存在效能問題的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)[指定按照哪種排序方式輸出結果]

    1. c按照查詢的次數排序
    2. t按照查詢的總時間排序
    3. l按照查詢中鎖的時間來排序
    4. r按照查詢中返回總的資料行來排序
    5. at、al、ar平均數量來排序

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

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

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

  1. 客戶端傳送SQL請求給伺服器
  2. 伺服器檢查是否可以在查詢快取中命中該SQL
  3. 伺服器端進行SQL解析，預處理，再由優化器生成對應的執行計劃
  4. 根據執行計劃，呼叫儲存引擎API來查詢資料
  5. 將結果返回給客戶端

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

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中將被移除]

    1. set profiling = 1;[啟動profile,這是一個session級別的配置]
    2. 執行查詢
    3. show profiles;[檢視每一個查詢所消耗的總的時間的資訊]
    4. show profile for query N;[查詢的每個階段所消耗的時間]
    5. show profile cpu for query N;[檢視每個階段所消耗的時間資訊和所消耗的cpu的資訊]

  • 使用performance_schema

    1. 啟動所需要的監控和歷史記錄表的資訊

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

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

    2. 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複製程式碼

相關文章