SQL优化之浅见
SQL優(yōu)化之淺見
- 一、查詢優(yōu)化
- 二、導(dǎo)入大量數(shù)據(jù)時的優(yōu)化
- 三、INSERT優(yōu)化
- 四、字段注釋
- 五、說明
用了段mysql/Oracle/hive等數(shù)據(jù)庫,對SQL語言以Mysql為例，總結(jié)一下對sql優(yōu)化方面的見解，歡迎大家補(bǔ)充。
一、查詢優(yōu)化
1.在關(guān)聯(lián)查詢中，關(guān)聯(lián)鍵的數(shù)據(jù)類型一定要相同，最常見的是字符串類型的數(shù)字被當(dāng)作INT類型與INT類型的鍵進(jìn)行關(guān)聯(lián)，隱性類型轉(zhuǎn)換會使性能受到很大影響。
2.模糊查詢使用LIKE時盡量不使用左側(cè)模糊，比如“%其實(shí)也厲害”，“%其實(shí)%”，不如使用右側(cè)模糊查詢，如：“軟輔其實(shí)也%”；
3.在關(guān)聯(lián)或者WHERE條件中，盡量不要對等號左側(cè)的字段進(jìn)行任何處理，以免索引失效，如將8位數(shù)字類型的字段轉(zhuǎn)換為日期：
4.如果可以，請限制大表的數(shù)據(jù)量，包括過濾無效、無意義數(shù)據(jù)，合理分區(qū)等；
5.落表也是一種很好的辦法。
6.添加索引，優(yōu)先考慮where/group by 使用的字段。
7.where條件中注意避免索引失效。
8.合理分區(qū)；
9.數(shù)據(jù)更新一般delete+insert into方式要比update等更新方式要快的多。
10.in 和 exists性能比較：
以哪個表為驅(qū)動表就會優(yōu)先使用哪個表的索引，所以如果外層表較大，內(nèi)層表較小，我們一般使用in；反之使用exists，這樣我們會用到內(nèi)層表（大表）的索引列；(其實(shí)后來的優(yōu)化器中,如果兩種方法都會使用外層表做為驅(qū)動,具體還要看執(zhí)行計劃.)
外表大，用in; 內(nèi)表大，用exists;現(xiàn)在，雖然優(yōu)化器的不斷完善，兩者在相同情況下執(zhí)行是一樣的，都會使用子查詢的索引。如下：
mysql> EXPLAIN SELECT * FROM user_buy_product_dtl_f WHERE CNAME IN (SELECT PNAME FROM later_fo_word_record_f ); +----+--------------------+------------------------+----------------+---------------+------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------------+----------------+---------------+------------+---------+------+---------+--------------------------+ | 1 | PRIMARY | user_buy_product_dtl_f | ALL | NULL | NULL | NULL | NULL | 1016485 | Using where | | 2 | DEPENDENT SUBQUERY | later_fo_word_record_f | index_subquery | name_index | name_index | 63 | func | 3 | Using index; Using where | +----+--------------------+------------------------+----------------+---------------+------------+---------+------+---------+--------------------------+ 2 rows in set (0.00 sec)mysql> EXPLAIN SELECT * FROM later_fo_word_record_f WHERE PNAME IN (SELECT CNAME FROM user_buy_product_dtl_f ); +----+--------------------+------------------------+----------------+---------------+-------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------------+----------------+---------------+-------------+---------+------+------+--------------------------+ | 1 | PRIMARY | later_fo_word_record_f | ALL | NULL | NULL | NULL | NULL | 36 | Using where | | 2 | DEPENDENT SUBQUERY | user_buy_product_dtl_f | index_subquery | name_indexc | name_indexc | 63 | func | 641 | Using index; Using where | +----+--------------------+------------------------+----------------+---------------+-------------+---------+------+------+--------------------------+ 2 rows in set (0.00 sec)mysql> EXPLAIN SELECT * FROM user_buy_product_dtl_f F WHERE EXISTS (SELECT 1 FROM later_fo_word_record_f T WHERE F.CNAME = T.PNAME ); +----+--------------------+-------+------+---------------+------------+---------+--------------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------------+---------+--------------+---------+--------------------------+ | 1 | PRIMARY | F | ALL | NULL | NULL | NULL | NULL | 1016485 | Using where | | 2 | DEPENDENT SUBQUERY | T | ref | name_index | name_index | 63 | test.F.CNAME | 3 | Using where; Using index | +----+--------------------+-------+------+---------------+------------+---------+--------------+---------+--------------------------+ 2 rows in set (0.00 sec)mysql> EXPLAIN SELECT * FROM later_fo_word_record_f F WHERE EXISTS (SELECT 1 FROM user_buy_product_dtl_f T WHERE F.PNAME = T.CNAME ); +----+--------------------+-------+------+---------------+-------------+---------+--------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+-------------+---------+--------------+------+--------------------------+ | 1 | PRIMARY | F | ALL | NULL | NULL | NULL | NULL | 36 | Using where | | 2 | DEPENDENT SUBQUERY | T | ref | name_indexc | name_indexc | 63 | test.F.PNAME | 641 | Using where; Using index | +----+--------------------+-------+------+---------------+-------------+---------+--------------+------+--------------------------+ 2 rows in set (0.00 sec)但是not in和not exists就不一樣了：
not in 會對內(nèi)外表進(jìn)行全表掃描，不會用到索引； not exists 依然會用到子查詢的索引；所以not in的效率往往不如not exists.
然而實(shí)際操作中,未必是這樣的,也可能兩種語法中都會使用子查詢的表索引，如下later_fo_word_record_f為小表，user_buy_product_dtl_f為大表，執(zhí)行計劃為：
11.union all
如果union all要優(yōu)化的話，落個臨時表吧，insert into效率還是不錯的。
12.union all 與 union
眾所周知，union 包含有去重操作，而union all沒有，但
在大數(shù)據(jù)量的情況下distinct + union all 性能大于 UNION 的性能。
當(dāng)然，如果確認(rèn)沒有重復(fù)記錄，直接用union all就OK了。
13.有子查詢的group by 字段數(shù)和group by數(shù)都要盡量少。
14.盡量避免使用select *，無效字段會影響查詢效率。
15.盡量避免空值判斷
空值判斷會使引擎放棄索引進(jìn)行全表掃描，因而降低查詢效率。
Mysql中字段要求盡量設(shè)置為not null，可以用0或’'代替設(shè)計的表模型中的Null;
一定要注意‘’與null的區(qū)別：
16.避免使用or,會使索引失效，可以使用union all代替。
17數(shù)據(jù)量較大時，避免使用where 1=1，雖然便于拼接，但也會導(dǎo)致索引失效。
二、導(dǎo)入大量數(shù)據(jù)時的優(yōu)化
1.MylSAM引擎的表可以使用DISABLE KEYS/ENABLE KEYS來打開表非唯一索引校驗：
alter table t1 DISABLE KEYS; loading data... alter table t1 ENABLE KEYS;2.InnoDB引擎插入數(shù)據(jù)前可以提前對自增長主鍵排序，也會提高數(shù)據(jù)導(dǎo)入效率。
3.InnoDB引擎開關(guān)唯一性校驗：
4.InnoDB引擎開關(guān)自動提交：
SET AUTOCOMMIT =0; SET AUTOCOMMIT =1;三、INSERT優(yōu)化
多值插入比每個insert插入一個值的連接次數(shù)要少很多，因此多值插入比多個insert插入要快。
四、字段注釋
如果節(jié)點(diǎn)性能超級差，在建表時盡量把陪伴我的注釋放在ddl語句內(nèi)，這樣只需要連接一次。而注釋在DDL之外會連接多次。
五、說明
這也只是我在平時工作中的一些技巧總結(jié)，實(shí)際的優(yōu)化還是要根據(jù)日志來對慢sql優(yōu)化，比如配置或物理存儲也會影響執(zhí)行的性能。
總結(jié)
- 上一篇： VC char和TCHAR之间转换
- 下一篇： Unicode字符集下CString/t