主頁(yè) > 知識(shí)庫(kù) > SQL優(yōu)化經(jīng)驗(yàn)總結(jié)

SQL優(yōu)化經(jīng)驗(yàn)總結(jié)

熱門標(biāo)簽:廣州人工電銷機(jī)器人費(fèi)用 寧波人工外呼系統(tǒng)有效果嗎 如何在地圖標(biāo)注自己店鋪 洛陽(yáng)外呼系統(tǒng)平臺(tái) 地圖標(biāo)注一個(gè)圓圈怎么用 電銷機(jī)器人被曝光 怎樣把地圖標(biāo)注導(dǎo)入公司地址 400外呼系統(tǒng)合法 真人語(yǔ)音電銷機(jī)器人

一. 優(yōu)化SQL步驟
1. 通過(guò) show status和應(yīng)用特點(diǎn)了解各種 SQL的執(zhí)行頻率
    通過(guò) SHOW STATUS 可以提供服務(wù)器狀態(tài)信息,也可以使用 mysqladmin extende d-status 命令獲得。 SHOW STATUS 可以根據(jù)需要顯示 session 級(jí)別的統(tǒng)計(jì)結(jié)果和 global級(jí)別的統(tǒng)計(jì)結(jié)果。

   如顯示當(dāng)前session: SHOW STATUS like "Com_%"; 全局級(jí)別:show global status;
   以下幾個(gè)參數(shù)對(duì) Myisam 和 Innodb 存儲(chǔ)引擎都計(jì)數(shù):

  1). Com_select 執(zhí)行 select 操作的次數(shù),一次查詢只累加 1 ;

  2). Com_insert 執(zhí)行 insert 操作的次數(shù),對(duì)于批量插入的 insert 操作,只累加一次 ;

  3). Com_update 執(zhí)行 update 操作的次數(shù);

  4). Com_delete 執(zhí)行 delete 操作的次數(shù);

    以下幾個(gè)參數(shù)是針對(duì) Innodb 存儲(chǔ)引擎計(jì)數(shù)的,累加的算法也略有不同:

  1). Innodb_rows_read select 查詢返回的行數(shù);

  2). Innodb_rows_inserted 執(zhí)行 Insert 操作插入的行數(shù);

  3). Innodb_rows_updated 執(zhí)行 update 操作更新的行數(shù);

  4). Innodb_rows_deleted 執(zhí)行 delete 操作刪除的行數(shù);

   通過(guò)以上幾個(gè)參數(shù),可以很容易的了解當(dāng)前數(shù)據(jù)庫(kù)的應(yīng)用是以插入更新為主還 是以查詢操作為主,以及各種類型的 SQL大致的執(zhí)行比例是多少。對(duì)于更新操作的計(jì) 數(shù),是對(duì)執(zhí)行次數(shù)的計(jì)數(shù),不論提交還是回滾都會(huì)累加。
   對(duì)于事務(wù)型的應(yīng)用,通過(guò) Com_commit 和 Com_rollback 可以了解事務(wù)提交和回 滾的情況,對(duì)于回滾操作非常頻繁的數(shù)據(jù)庫(kù),可能意味著應(yīng)用編寫存在問(wèn)題。此外,以下幾個(gè)參數(shù)便于我們了解數(shù)據(jù)庫(kù)的基本情況:

 1). Connections 試圖連接 Mysql 服務(wù)器的次數(shù)
 2). Uptime 服務(wù)器工作時(shí)間
 3). Slow_queries 慢查詢的次數(shù)

2. 定位執(zhí)行效率較低的SQL語(yǔ)句
     可以通過(guò)以下兩種方式定位執(zhí)行效率較低的 SQL 語(yǔ)句:
     1). 可以通過(guò)慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 sql 語(yǔ)句,用 --log-slow-queries[=file_name] 選項(xiàng)啟動(dòng)時(shí), mysqld 寫一個(gè)包含所有執(zhí)行時(shí)間超過(guò)long_query_time 秒的 SQL 語(yǔ)句的日志文件??梢枣溄拥焦芾砭S護(hù)中的相關(guān)章節(jié)。
     2). 使用show processlist查看當(dāng)前MYSQL的線程, 命令慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才紀(jì)錄,所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問(wèn)題的時(shí)候查 詢慢查詢?nèi)罩静⒉荒芏ㄎ粏?wèn)題,可以使用 show processlist 命令查看當(dāng)前 MySQL 在進(jìn)行的線程,包括線程的狀態(tài),是否鎖表等等,可以實(shí)時(shí)的查看 SQL 執(zhí)行情況, 同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化。
     3). 通過(guò)EXPLAIN 分析低效 SQL的執(zhí)行計(jì)劃:
        通過(guò)以上步驟查詢到效率低的 SQL 后,我們可以通過(guò) explain 或者 desc 獲取MySQL 如何執(zhí)行 SELECT 語(yǔ)句的信息,包括 select 語(yǔ)句執(zhí)行過(guò)程表如何連接和連接 的次序。

二. MySQL索引
1. mysql如何使用索引    
       索引用于快速找出在某個(gè)列中有一特定值的行。對(duì)相關(guān)列使用索引是提高SELECT 操作性能的最佳途徑。
       查詢要使用索引最主要的條件是查詢條件中需要使用索引關(guān)鍵字,如果是多列 索引,那么只有查詢條件使用了多列關(guān)鍵字最左邊的前綴時(shí)(前綴索引),才可以使用索引,否則 將不能使用索引。

       下列情況下, Mysql 不會(huì)使用已有的索引:
      1).如果 mysql 估計(jì)使用索引比全表掃描更慢,則不使用索引。例如:如果 key_part 1均勻分布在 1 和 100 之間,下列查詢中使用索引就不是很好:
        SELECT * FROM table_name where key_part1 > 1 and key_part1 90
      2).如果使用 heap 表并且 where 條件中不用=索引列,其他 > 、 、 >= 、 = 均不使 用索引(MyISAM和innodb表使用索引);

      3).使用or分割的條件,如果or前的條件中的列有索引,后面的列中沒有索引,那么涉及到的索引都不會(huì)使用。
      4).如果創(chuàng)建復(fù)合索引,如果條件中使用的列不是索引列的第一部分;(不是前綴索引)
      5).如果 like 是以%開始;
      6).對(duì) where 后邊條件為字符串的一定要加引號(hào),字符串如果為數(shù)字 mysql 會(huì)自動(dòng)轉(zhuǎn) 為字符串,但是不使用索引。

2. 查看索引使用情況
        如果索引正在工作, Handler_read_key 的值將很高,這個(gè)值代表了一個(gè)行被索引值讀的次數(shù),很低的值表明增加索引得到的性能改善不高,因?yàn)樗饕⒉唤?jīng)常使 用。
Handler_read_rnd_next 的值高則意味著查詢運(yùn)行低效,并且應(yīng)該建立索引補(bǔ)救。這個(gè)值的含義是在數(shù)據(jù)文件中讀下一行的請(qǐng)求數(shù)。如果你正進(jìn)行大量的表掃描,
該值較高。通常說(shuō)明表索引不正確或?qū)懭氲牟樵儧]有利用索引。
       語(yǔ)法:
        mysql> show status like 'Handler_read%';

三. 具體優(yōu)化查詢語(yǔ)句
 1. 查詢進(jìn)行優(yōu)化,應(yīng)盡量避免全表掃描
    對(duì)查詢進(jìn)行優(yōu)化,應(yīng)盡量避免全表掃描,首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引

       .    嘗試下面的技巧以避免優(yōu)化器錯(cuò)選了表掃描:

       ·   使用ANALYZE TABLEtbl_name為掃描的表更新關(guān)鍵字分布。

       ·   對(duì)掃描的表使用FORCEINDEX告知MySQL,相對(duì)于使用給定的索引表掃描將非常耗時(shí)。

            SELECT * FROM t1, t2 FORCE INDEX (index_for_column)   WHERE t1.col_name=t2.col_name;

       ·   用--max-seeks-for-key=1000選項(xiàng)啟動(dòng)mysqld或使用SET max_seeks_for_key=1000告知優(yōu)化器假設(shè)關(guān)鍵字掃描不會(huì)超過(guò)1,000次關(guān)鍵字搜索。

 1). 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷

        否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:

        select id from t where num is null

        NULL對(duì)于大多數(shù)數(shù)據(jù)庫(kù)都需要特殊處理,MySQL也不例外,它需要更多的代碼,更多的檢查和特殊的索引邏輯,有些開發(fā)人員完全沒有意識(shí)到,創(chuàng)建表時(shí)NULL是默認(rèn)值,但大多數(shù)時(shí)候應(yīng)該使用NOT NULL,或者使用一個(gè)特殊的值,如0,-1作為默  認(rèn)值。

        不能用null作索引,任何包含null值的列都將不會(huì)被包含在索引中。即使索引有多列這樣的情況下,只要這些列中有一列含有null,該列    就會(huì)從索引中排除。也就是說(shuō)如果某列存在空值,即使對(duì)該列建索引也不會(huì)提高性能。 任何在where子句中使用is null或is not null的語(yǔ)句優(yōu)化器是不允許使用索引的。

        此例可以在num上設(shè)置默認(rèn)值0,確保表中num列沒有null值,然后這樣查詢:

         select id    from t where num=0

 2). 應(yīng)盡量避免在 where 子句中使用!=或>操作符

         否則將引擎放棄使用索引而進(jìn)行全表掃描。
         MySQL只有對(duì)以下操作符才使用索引:,=,=,>,>=,BETWEEN,IN,以及某些時(shí)候的LIKE。

         可以在LIKE操作中使用索引的情形是指另一個(gè)操作數(shù)不是以通配符(%或者_(dá))開頭的情形。例如:
         SELECT id FROM  t WHERE col LIKE 'Mich%'; #  這個(gè)查詢將使用索引,
         SELECT id FROM  t WHERE col  LIKE '%ike';   #這個(gè)查詢不會(huì)使用索引。

 3). 應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件

        否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:

        select id from t where num=10 or num=20

        可以 使用UNION合并查詢: select id from t where num=10 union all select id from t where num=20

         

       在某些情況下,or條件可以避免全表掃描的。

        1 .where 語(yǔ)句里面如果帶有or條件, myisam表能用到索引, innodb不行。

        2 .必須所有的or條件都必須是獨(dú)立索引

       mysql or條件可以使用索引而避免全表

 4) .in 和 not in 也要慎用,否則會(huì)導(dǎo)致全表掃描,

        如:

        select id from t where num in(1,2,3)

        對(duì)于連續(xù)的數(shù)值,能用 between 就不要用 in 了:

        Select id from t where num between 1 and 3

  5).下面的查詢也將導(dǎo)致全表掃描:

        select id from t where name like '%abc%' 或者

        select id from t where name like '%abc' 或者

        若要提高效率,可以考慮全文檢索。

        而select id from t where name like 'abc%' 才用到索引

 6). 如果在 where 子句中使用參數(shù),也會(huì)導(dǎo)致全表掃描。

       因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量,但優(yōu)化程序不能將訪問(wèn)計(jì)劃的選擇推 遲到運(yùn)行時(shí);它必須在編譯時(shí)進(jìn)行選擇。然而,如果在編譯時(shí)建立訪問(wèn)計(jì)劃,變量的值還是未知的,因而無(wú)法作為索引選擇的輸入項(xiàng)。如下面語(yǔ)句將進(jìn)行全表掃描:

       select id from t where num=@num

       可以改為強(qiáng)制查詢使用索引: select id from t with(index(索引名)) where num=@num

 7). 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作,

       這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:

       select id from t where num/2=100

       應(yīng)改為:  select id from t where num=100*2

 8). 應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作,

       這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:

      select id from t where substring(name,1,3)='abc'   --name

      select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30'

      生成的id 應(yīng)改為:

      select id from t where name like 'abc%'

      select id from t where createdate>='2005-11-30' and createdate'2005-12-1'

9).不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算,

      否則系統(tǒng)將可能無(wú)法正確使用索引。

10). 索引字段不是復(fù)合索引的前綴索引

       例如 在使用索引字段作為條件時(shí),如果該索引是復(fù)合索引,那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引,否則該索引將不會(huì)被使用,并且應(yīng)盡可能的讓字段順序與索引順序相一致。

2 .其他一些注意優(yōu)化:
11). 不要寫一些沒有意義的查詢,

        如需要生成一個(gè)空表結(jié)構(gòu):

        select col1,col2 into #t from t where 1=0

        這類代碼不會(huì)返回任何結(jié)果集,但是會(huì)消耗系統(tǒng)資源的,應(yīng)改成這樣: create table #t(...)

12). 很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇:

       select num from a where num in(select num from b)

       用下面的語(yǔ)句替換:

       select num from a where exists(select 1 from b where num=a.num)

13). 并不是所有索引對(duì)查詢都有效,

       SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢優(yōu)化的,當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí),SQL查詢可能不會(huì)去利用索引,如一表中有字段sex,male、female幾乎各一半,那么即使在sex上建了索引也對(duì)查詢效率起不了作用。

14). 索引并不是越多越好,

       索引固然可以提高相應(yīng)的 select 的效率,但同時(shí)也降低了 insert 及 update 的效率,因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引,所以怎樣建索引需要慎重考慮,視具體情況而定。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè),若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。

15).應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列,

       因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序,一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整,會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列,那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

16).盡量使用數(shù)字型字段,

      若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型,這會(huì)降低查詢和連接的性能,并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符,而對(duì)于數(shù)字型而言只需要比較一次就夠了。

17).盡可能的使用 varchar/nvarchar 代替 char/nchar ,

      因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小,可以節(jié)省存儲(chǔ)空間,其次對(duì)于查詢來(lái)說(shuō),在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

18).最好不要使用"*"返回所有: select * from t ,

     用具體的字段列表代替“*”,不要返回用不到的任何字段。

3. 臨時(shí)表的問(wèn)題:
19). 盡量使用表變量來(lái)代替臨時(shí)表。

    如果表變量包含大量數(shù)據(jù),請(qǐng)注意索引非常有限(只有主鍵索引)。

20).避免頻繁創(chuàng)建和刪除臨時(shí)表,以減少系統(tǒng)表資源的消耗。

21).臨時(shí)表并不是不可使用,

     適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行?,例如,?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是,對(duì)于一次性事件,最好使用導(dǎo)出表。

22).在新建臨時(shí)表時(shí),如果一次性插入數(shù)據(jù)量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;

     如果數(shù)據(jù)量不大,為了緩和系統(tǒng)表的資源,應(yīng)先create table,然后insert。

23). 如果使用到了臨時(shí)表,在存儲(chǔ)過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除,先 truncate table ,然后 drop table ,這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。

4. 游標(biāo)的問(wèn)題:
24).盡量避免使用游標(biāo),

      因?yàn)橛螛?biāo)的效率較差,如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行,那么就應(yīng)該考慮改寫。

25).使用基于游標(biāo)的方法或臨時(shí)表方法之前,

      應(yīng)先尋找基于集的解決方案來(lái)解決問(wèn)題,基于集的方法通常更有效。

26).與臨時(shí)表一樣,游標(biāo)并不是不可使用。

     對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法,尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時(shí)間允許,基于游標(biāo)的方法和基于集的方法都可以嘗試一下,看哪一種方法的效果更好。

27).在所有的存儲(chǔ)過(guò)程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ,在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF 。

      無(wú)需在執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器的每個(gè)語(yǔ)句后向客戶端發(fā)送 DONE_IN_PROC 消息。

5. 事務(wù)的問(wèn)題:
28).盡量避免大事務(wù)操作,提高系統(tǒng)并發(fā)能力。

6. 數(shù)據(jù)量的問(wèn)題
29).盡量避免向客戶端返回大數(shù)據(jù)量,若數(shù)據(jù)量過(guò)大,應(yīng)該考慮相應(yīng)需求是否合理。
7. COUNT優(yōu)化:
30) count(*) 優(yōu)于count(1)和count(primary_key)

  很多人為了統(tǒng)計(jì)記錄條數(shù),就使用 count(1) 和 count(primary_key) 而不是 count(*) ,他們認(rèn)為這樣性能更好,其實(shí)這是一個(gè)誤區(qū)。對(duì)于有些場(chǎng)景,這樣做可能性能會(huì)更差,應(yīng)為數(shù)據(jù)庫(kù)對(duì) count(*) 計(jì)數(shù)操作做了一些特別的優(yōu)化。
31)count(column) 和 count(*) 是不一樣的

  這個(gè)誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在,很多人都會(huì)認(rèn)為這是理所當(dāng)然的。實(shí)際上,count(column) 和 count(*) 是一個(gè)完全不一樣的操作,所代表的意義也完全不一樣。
  count(column) 是表示結(jié)果集中有多少個(gè)column字段不為空的記錄
  count(*) 是表示整個(gè)結(jié)果集有多少條記錄

8. 優(yōu)化order by語(yǔ)句
     基于索引的排序
     MySQL的弱點(diǎn)之一是它的排序。雖然MySQL可以在1秒中查詢大約15,000條記錄,但由于MySQL在查詢時(shí)最多只能使用一個(gè)索引。因此,如果WHERE條件已經(jīng)占用了索引,那么在排序中就不使用索引了,這將大大降低查詢的速度。我們可以看看如下的SQL語(yǔ)句:
     SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
     在以上的SQL的WHERE子句中已經(jīng)使用了NAME字段上的索引,因此,在對(duì)SALE_DATE進(jìn)行排序時(shí)將不再使用索引。為了解決這個(gè)問(wèn)題,我們可以對(duì)SALES表建立復(fù)合索引:
     ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME,SALE_DATE)
     這樣再使用上述的SELECT語(yǔ)句進(jìn)行查詢時(shí)速度就會(huì)大副提升。但要注意,在使用這個(gè)方法時(shí),要確保WHERE子句中沒有排序字段,在上例中就是不能用SALE_DATE進(jìn)行查詢,否則雖然排序快了,但是SALE_DATE字段上沒有單獨(dú)的索引,因此查詢又會(huì)慢下來(lái)。

     在某些情況中, MySQL可以使用一個(gè)索引來(lái)滿足 ORDER BY子句,而不需要額外的排序。 where條件和order by使用相同的索引,并且order by 的順序和索引順序相 同,并且order by的字段都是升序或者都是降序。例如:下列sql可以使用索引。
     SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
     SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
     SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
    但是以下情況不使用索引:
     SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ; --order by 的字段混合 ASC 和 DESC
     SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ;-- 用于查詢行的關(guān)鍵字與 ORDER BY 中所使用的不相同
     SELECT * FROM t1 ORDER BY key1, key2 ;-- 對(duì)不同的關(guān)鍵字使用 ORDER BY :

9. 優(yōu)化GROUP BY
      默認(rèn)情況下, MySQL 排序所有 GROUP BY col1 , col2 , .... 。查詢的方法如同在查詢中指定 ORDER BY col1 , col2 , ... 。如果顯式包括一個(gè)包含相同的列的 ORDER BY
子句, MySQL 可以毫不減速地對(duì)它進(jìn)行優(yōu)化,盡管仍然進(jìn)行排序。如果查詢包括 GROUP BY 但你想要避免排序結(jié)果的消耗,你可以指定 ORDER BY NULL禁止排序。
例如 :
INSERT INTO foo  SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

10. 優(yōu)化 OR
具體詳解看:mysql or條件可以使用索引而避免全表

四. Explain解釋說(shuō)明
explain顯示了mysql如何使用索引來(lái)處理select語(yǔ)句以及連接表??梢詭椭x擇更好的索引和寫出更優(yōu)化的查詢語(yǔ)句。
使用方法,在select語(yǔ)句前加上explain就可以了:
如:

explain select surname,first_name form a,b where a.id=b.id  
分析結(jié)果形式如下:
table |  type | possible_keys | key | key_len  | ref | rows | Extra
EXPLAIN列的解釋:

1 table:
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
2 type:
這是重要的列,顯示連接使用了何種類型。從最好到最差的連接類型為:system、const、eg_reg、ref、ref_or_null、 range、indexhe、 ALL。
        system:表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個(gè)特例
        const:(PRIMARY KEY或UNIQUE)
            表最多有一個(gè)匹配行,它將在查詢開始時(shí)被讀取。因?yàn)閮H有一行,在這行的列值可被優(yōu)化器剩余部分認(rèn)為是常數(shù)。
            const表很快,因?yàn)樗鼈冎蛔x取一次!
            const用于用常數(shù)值比較PRIMARY KEY或UNIQUE索引的所有部分時(shí)。
            在下面的查詢中,tbl_name可以用于const表:
SELECT * from tbl_name WHERE primary_key=1;  
        eq_reg:key
  對(duì)于每個(gè)來(lái)自于前面的表的行組合,從該表中讀取一行。這可能是最好的聯(lián)接類型,除了const類型。
           它用在一個(gè)索引的所有部分被聯(lián)接使用并且索引是UNIQUE或PRIMARY KEY。
           eq_ref可以用于使用= 操作符比較的帶索引的列。比較值可以為常量或一個(gè)使用在該表前面所讀取的表的列的表達(dá)式。
  在下面的例子中,MySQL可以使用eq_ref聯(lián)接來(lái)處理ref_tables:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; 
   SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column 
                                                 AND ref_table.key_column_part2=1; 

        ref:key
 對(duì)于每個(gè)來(lái)自于前面的表的行組合,所有有匹配索引值的行將從這張表中讀取。如果聯(lián)接只使用鍵的最左邊的前綴,或如果鍵不是UNIQUE或PRIMARY KEY(換句話說(shuō),如果聯(lián)接不能基于關(guān)鍵字選擇單個(gè)行的話),則使用ref。

 如果使用的鍵僅僅匹配少量行,該聯(lián)接類型是不錯(cuò)的。
 ref可以用于使用=或=>操作符的帶索引的列。
 在下面的例子中,MySQL可以使用ref聯(lián)接來(lái)處理ref_tables:

SELECT * FROM ref_table WHERE key_column=expr; 
 SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; 
 SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column 
                          AND ref_table.key_column_part2=1;  
      ref_or_null:Or Is null
該聯(lián)接類型如同ref,但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經(jīng)常使用該聯(lián)接類型的優(yōu)化。
        在下面的例子中,MySQL可以使用ref_or_null聯(lián)接來(lái)處理ref_tables:
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;      
      range:=、>、>、>=、、=、IS NULL、=>、BETWEEN或者IN
 只檢索給定范圍的行,使用一個(gè)索引來(lái)選擇行。key列顯示使用了哪個(gè)索引。
          key_len包含所使用索引的最長(zhǎng)關(guān)鍵元素。在該類型中ref列為NULL。
當(dāng)使用=、>、>、>=、、=、IS NULL、=>、BETWEEN或者IN操作符,用常量比較關(guān)鍵字列時(shí),可以使用range:
SELECT * FROM tbl_name WHERE key_column = 10; 
SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; 
SELECT * FROM tbl_name WHERE key_column IN (10,20,30); 
SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);  
      indexhe:
該聯(lián)接類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因?yàn)樗饕募ǔ1葦?shù)據(jù)文件小。
當(dāng)查詢只使用作為單索引一部分的列時(shí),MySQL可以使用該聯(lián)接類型。
      ALL:
對(duì)于每個(gè)來(lái)自于先前的表的行組合,進(jìn)行完整的表掃描。如果表是第一個(gè)沒標(biāo)記const的表,
        這通常不好,并且通常在它情況下很差。通??梢栽黾痈嗟乃饕灰褂肁LL,
        使得行能基于前面的表中的常數(shù)值或列值被檢索出。
3 possible_keys :
   顯示可能應(yīng)用在這張表中的索引。如果為空,沒有可能的索引??梢詾橄嚓P(guān)的域從WHERE語(yǔ)句中 
   選擇一個(gè)合適的語(yǔ)句
4 key :
 實(shí)際使用的索引。如果為NULL,則沒有使用索引。很少的情況下,MYSQL會(huì)選擇優(yōu)化不足的索引  。
 這種情況下,可以在SELECT語(yǔ)句中使用USEINDEX(indexname)來(lái)強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX(indexname)來(lái)強(qiáng)制MYSQL忽略索引
5key_len:
使用的索引的長(zhǎng)度。在不損失精確性的情況下,長(zhǎng)度越短越好

6 ref
顯示索引的哪一列被使用了,如果可能的話,是一個(gè)常數(shù)

7 rows
MYSQL認(rèn)為必須檢查的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù) (掃描行的數(shù)量)

8 Extra 
 該列包含MySQL解決查詢的詳細(xì)信息
 關(guān)于MYSQL如何解析查詢的額外信息。將在表4.3中討論,但這里可以看到的壞的例子是Using temporary和Using filesort,
 意思MYSQL根本不能使用索引,結(jié)果是檢索會(huì)很慢

extra列返回的描述的意義

Distinct:
一旦MYSQL找到了與行相聯(lián)合匹配的行,就不再搜索了
Not exists :
MYSQL優(yōu)化了LEFT JOIN,一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行, 就不再搜索了
        面是一個(gè)可以這樣優(yōu)化的查詢類型的例子:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
假定t2.id定義為NOT NULL。在這種情況下,MySQL使用t1.id的值掃描t1并查找t2中的行。
        如果MySQL在t2中發(fā)現(xiàn)一個(gè)匹配的行,它知道t2.id絕不會(huì)為NULL,并且不再掃描t2內(nèi)有相同的id值的行。
        換句話說(shuō),對(duì)于t1的每個(gè)行,MySQL只需要在t2中查找一次,無(wú)論t2內(nèi)實(shí)際有多少匹配的行。
Range checked for each Record(index map:#)
沒有找到理想的索引,因此對(duì)于從前面表中來(lái)的每一個(gè)行組合,MYSQL檢查使用哪個(gè)索引,并用它來(lái)從表中返回行。
        這是使用索引的最慢的連接之一
        MySQL沒有發(fā)現(xiàn)好的可以使用的索引,但發(fā)現(xiàn)如果來(lái)自前面的表的列值已知,可能部分索引可以使用。
        對(duì)前面的表的每個(gè)行組合,MySQL檢查是否可以使用range或index_merge訪問(wèn)方法來(lái)索取行。
        關(guān)于適用性標(biāo)準(zhǔn)的描述參見7.2.5節(jié),“范圍優(yōu)化”和7.2.6節(jié),“索引合并優(yōu)化”,
        不同的是前面表的所有列值已知并且認(rèn)為是常量。這并不很快,但比執(zhí)行沒有索引的聯(lián)接要快得多。
Using filesort
看到這個(gè)的時(shí)候,查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。
        它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行
Using index
列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實(shí)際的行動(dòng)的表返回的,
        這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候
Using temporary
看到這個(gè)的時(shí)候,查詢需要優(yōu)化了。這里,MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果,這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上,而不是GROUP BY上
Using where
使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行,
        并且連接類型ALL或index,這就會(huì)發(fā)生,或者是查詢有問(wèn)題
Impossible WHERE noticed after reading const table...

五 SQL核心語(yǔ)句(非常實(shí)用的幾個(gè)技巧)

1) 插入數(shù)據(jù)

批量插入:

INSERT mytable (first_column,second_column,third_column) 
VALUES ('some data','some more data','yet more data') , 
VALUES ('some data','some more data','yet more data') , 
VALUES ('some data','some more data','yet more data')  

2).清空數(shù)據(jù)表

TRUNCATE TABLE `mytable` 

注意:刪除表中的所有記錄,應(yīng)使用TRUNCATE TABLE語(yǔ)句。注意這里為什么要用TRUNCATE TABLE語(yǔ)句代替DELETE語(yǔ)句:當(dāng)你使用TRUNCATE TABLE語(yǔ)句時(shí),記錄的刪除是不作記錄的。也就是說(shuō),這意味著TRUNCATE TABLE要比DELETE快得多。

3)用SELECT創(chuàng)建記錄和表

  INSERT語(yǔ)句與DELETE語(yǔ)句和UPDATE語(yǔ)句有一點(diǎn)不同,它一次只操作一個(gè)記錄。然而,有一個(gè)方法可以使INSERT 語(yǔ)句一次添加多個(gè)記錄。要作到這一點(diǎn),你需要把INSERT語(yǔ)句與SELECT語(yǔ)句結(jié)合起來(lái),象這樣:

INSERT mytable(first_column,second_column) 
SELECT another_first,another_second FROM anothertable WHERE another_first='Copy Me!';   

        這個(gè)語(yǔ)句從anothertable拷貝記錄到mytable.只有表anothertable中字段another_first的值為'Copy Me!'的記錄才被拷貝。

  當(dāng)為一個(gè)表中的記錄建立備份時(shí),這種形式的INSERT語(yǔ)句是非常有用的。在刪除一個(gè)表中的記錄之前,你可以先用這種方法把它們拷貝到另一個(gè)表中。

  如果你需要拷貝整個(gè)表,你可以使用SELECT INTO語(yǔ)句。例如,下面的語(yǔ)句創(chuàng)建了一個(gè)名為newtable的新表,該表包含表mytable的所有數(shù)據(jù):

SELECT * INTO newtable FROM mytable; 

       你也可以指定只有特定的字段被用來(lái)創(chuàng)建這個(gè)新表。要做到這一點(diǎn),只需在字段列表中指定你想要拷貝的字段。另外,你可以使用WHERE子句來(lái)限制拷貝到新表中的記錄。下面的例子只拷貝字段second_columnd的值等于'Copy Me!'的記錄的first_column字段。

SELECT first_column INTO newtable 
FROM mytable 
WHERE second_column='Copy Me!';

        使用SQL修改已經(jīng)建立的表是很困難的。例如,如果你向一個(gè)表中添加了一個(gè)字段,沒有容易的辦法來(lái)去除它。另外,如果你不小心把一個(gè)字段的數(shù)據(jù)類型給錯(cuò)了,你將沒有辦法改變它。但是,使用本節(jié)中講述的SQL語(yǔ)句,你可以繞過(guò)這兩個(gè)問(wèn)題。

  例如,假設(shè)你想從一個(gè)表中刪除一個(gè)字段。使用SELECT INTO語(yǔ)句,你可以創(chuàng)建該表的一個(gè)拷貝,但不包含要?jiǎng)h除的字段。這使你既刪除了該字段,又保留了不想刪除的數(shù)據(jù)。

  如果你想改變一個(gè)字段的數(shù)據(jù)類型,你可以創(chuàng)建一個(gè)包含正確數(shù)據(jù)類型字段的新表。創(chuàng)建好該表后,你就可以結(jié)合使用UPDATE語(yǔ)句和SELECT語(yǔ)句,把原來(lái)表中的所有數(shù)據(jù)拷貝到新表中。通過(guò)這種方法,你既可以修改表的結(jié)構(gòu),又能保存原有的數(shù)據(jù)。

以上就是整理的SQL優(yōu)化經(jīng)驗(yàn),希望對(duì)大家的學(xué)習(xí)有所幫助。

您可能感興趣的文章:
  • mysql數(shù)據(jù)庫(kù)查詢優(yōu)化 mysql效率
  • MSSQL Server 查詢優(yōu)化方法 整理
  • SqlServer 執(zhí)行計(jì)劃及Sql查詢優(yōu)化初探
  • MySQL查詢優(yōu)化:連接查詢排序淺談
  • mysql嵌套查詢和聯(lián)表查詢優(yōu)化方法
  • MySQL查詢優(yōu)化:用子查詢代替非主鍵連接查詢實(shí)例介紹
  • MySQL查詢優(yōu)化:LIMIT 1避免全表掃描提高查詢效率
  • MySQL查詢優(yōu)化:連接查詢排序limit(join、order by、limit語(yǔ)句)介紹
  • MySQL查詢優(yōu)化--調(diào)整內(nèi)部變量的詳解
  • MySQL查詢優(yōu)化之explain的深入解析
  • MySQL查詢優(yōu)化之索引的應(yīng)用詳解
  • 淺談MySQL中的子查詢優(yōu)化技巧
  • SQL大量數(shù)據(jù)查詢的優(yōu)化及非用like不可時(shí)的處理方案
  • SQL Server多表查詢優(yōu)化方案集錦

標(biāo)簽:晉中 南昌 石家莊 煙臺(tái) 北海 咸寧 東營(yíng) 珠海

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《SQL優(yōu)化經(jīng)驗(yàn)總結(jié)》,本文關(guān)鍵詞  SQL,優(yōu)化,經(jīng)驗(yàn)總結(jié),SQL,優(yōu)化,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《SQL優(yōu)化經(jīng)驗(yàn)總結(jié)》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于SQL優(yōu)化經(jīng)驗(yàn)總結(jié)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章