MySQL 常用SQL優化

MySQL 常用SQL優化:
一.大批量插入數據:
1.對于load MyISAM存儲引擎的表,可以通過關閉打開MyISAM表非唯一索引的更新來提升導入速度:
? 例:mysql > alter table tbl_name disable keys;
???? mysql > load data infile ‘/home/mysql/tbl_name.txt’ into table tbl_name;
???? mysql > alter table tbl_name enable keys;
2.對于InnoDB類型的表有以下方式提高導入數據庫的效率:
? (1)在導入錢執行set unique_checks=0關閉唯一性校驗,在導入結束后執行set set unique_checks=1 恢復唯一性校驗,可以提高導入速度
?? 例:mysql > set set unique_checks=0;
????? mysql > load data infile ‘/home/mysql/tbl_name.txt’ into table tbl_name;
????? mysql > set set unique_checks=1;
? (2)如果使用自動提交方式,建議在導入前執行set autocommit=0關閉自動提交,導入后再執行set autocommit=1打開自動提交
?? 例:mysql > set autocommit=0;
????? mysql > load data infile ‘/home/mysql/tbl_name.txt’ into table tbl_name;
????? mysql > set autocommit=1;二.優化Insert語句:
1.如果同時從同一客戶插入很多行,盡量使用多個值表示的insert語句,這種方式將大大縮減客戶端與數據庫之間的連接,關閉等消耗,是的效率比分開執行的單個insert語句快(在一些情況中幾倍).
? 例:insert into tset values (1,2),(1,3),(1,4)……
2.如果從不同客戶插入很多行,能通過使用insert delayed語句得到更高的速度.DELAYED的含義是讓insert語句馬上執行,其實數據都被放在內存隊列中,并沒有真正寫入磁盤,這比每條語句分別插入要快得多;LOW_PRIORITY剛好相反,在所有其他用戶對表的讀寫完后才能插入.
3.將索引文件和數據庫文件分在不同的磁盤上存放(利用建表中的選項);
4.如果進行批量插入,可以增加bukl_insert_buffer_size 變量值的方法來提高速度,但是,這只能對MyISAM表使用;
5.當從一個文本文件裝載一個表時,使用LOAD DATA INFILE .這通常比使用很多INSERT語句快20倍.三.優化GROUP BY語句:
1.如果查詢包括GROUP BY但用戶想要避免排序結果的消耗,則可以指定ORDER BY NULL禁止排序:
? 例:mysql > explain select id ,sum(moneys) from sales2 group id \G;
???? *********************************** 1.row **********************************
????????????????????? ?? id: 1
??????????? ?? select_type: SIMPLE
????????????????????????? ?? table: sales2
????????????????????????????? type: ALL
????????? possible_keys: NULL
????????????????????? ???????? key: NULL
????????????????? ??? key_len: NULL
???????????????????????? ?????? ref: NULL
????????????????????? ????? rows: 1000
????????????????????? ???? Extra: Using temporary ; Using Filesort
??? 1 row in set (0.00 sec)
??? mysql > explain select id ,sum(moneys) from sales2 group id order by null \G;
???? *********************************** 1.row **********************************
?????????????????????????? ??????? id: 1
?????????????? select_type: SIMPLE
???????????????????????? ?? table: sales2
????????????????????????? ?? type: ALL
????????? possible_keys: NULL
??????????????????? ?????????? key: NULL
????????????????????? key_len: NULL
????????????????????????? ????? ref: NULL
??????????????????????????? rows: 1000
?????????????????????? ?? Extra: Using temporary
??? 1 row in set (0.00 sec)從上面的例子可以看出第一個SQL語句需要進行”filesort”,而第二個SQL由于ORDER BY NULL 不需要進行”filesort”,而filesort往往非常耗費時間.四.優化ORDER BU語句:
?? 在某些情況中,MySQL可以使用一個索引來滿足ORDER BY子句,而不需要額外的排序.WHERE條件和ORDER BY使用相同的索引,并且ORDER BY的順序和索引的順序相同,并且ORDER BY的字段都是升序或者都是降序.
例如,下列SQL可以使用索引:
? mysql > select * from t1 order by key_part1 , key_part2,… ;
? mysql > select * from t1 where key_part1=1 order by key_part1 DESC , key_part2 DESC;
? mysql > select * from t1 ORDER BY key_part1 DESC , key_part2 DESC;
? 但是在以下幾種情況下則不使用索引:
? mysql > select * from t1 order by key_part1 DESC , key_part2 ASC ;
? –order by 的字段混合ASC和DESC
? mysql > select * from t1 where key2=constant order by key1;
? –用于查詢行的關鍵字與ORDER BY中所使用的不相同
? mysql > select * from t1 order by key1 ,key2;
? –對不同的關鍵字使用ORDER BY五.優化嵌套查詢:
1.使用子查詢可以一次性地完成很多邏輯上需要多個步驟才能完成的SQL操作,同時也可以避免事務或者表鎖死,并且寫起來也很容易.但是有些情況下,子查詢可以被更有效率的連接(JOIN)替代.六.優化OR條件:
1.對于含有OR的查詢子句,如果要利用索引,則OR之間的每個條件列都必須用到索引;如果沒有索引,則應該考慮增加索引.七.使用SQL提示:
1.USE INDEX
? 在查詢語句中表名的后面,添加USE INDEX來提供希望MySQL去參考的索引列表,就可以讓MySQL不在考慮其他可用的索引
? 例: mysql > select * from sales2 use index (ind_sales2_id) where id = 3 ;
????? –查詢sales2表時采用ind_sales2_id索引
2.IGNORE INDEX
? 如果用戶只是單純地向讓MySQL忽略一個或者多個索引,則可以使用IGNORE INDEX作為HINT.
? 例: mysql > select * from sales2 ignore index (ind_sales2_id) where id = 3 ;
????? –查詢sales2表時忽略ind_sales2_id索引
3.FORCE INDEX
? 為強制MySQL使用一個特定的索引,可在查詢中使用FORCE INDEX 作為HINT.
? 例: mysql > select * from sales2 where id > 0 ;
????? –當使用FORCE INDEX進行提示時,即便使用索引的效率不是最高,MySQL還是選擇使用索引,這是MySQL留給用戶的一個自行選擇執行計劃的權利

轉載于:https://blog.51cto.com/samyu/154234

總結

以上是生活随笔為你收集整理的MySQL 常用SQL优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。