SQL語句優(yōu)化

MySQL大表優(yōu)化

索引(Index)相關(guān)及索引原理(B樹，B+樹）

一、優(yōu)化細則

  1. 在 where 子句中使用 != 或 <> 操作符，會導致引擎放棄使用索引而進行全表掃描。SQL 中，不等于操作符會限制索引，造成全表掃描，即使比較的字段上有索引。MySQL 只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些方式的 LIKE ('a%')。

模糊查詢效率很低原因：like 本身效率就比較低，應(yīng)該盡量避免查詢條件使用 like。對于like ‘%...%’(全模糊)這樣的條件，是無法使用索引的，全表掃描自然效率很低；另外，由于匹配算法的關(guān)系，模糊查詢的字段長度越大，模糊查詢效率越低。
解決辦法：首先盡量避免模糊查詢，如果因為業(yè)務(wù)需要一定要使用模糊查詢，則至少保證不要使用全模糊查詢，對于【右模糊查詢like ‘…%’是會使用索引的；左模糊like ‘%...’無法直接使用索引】，但可以利用 reverse + function index 的形式，變化成 like ‘…%’。全模糊是無法優(yōu)化的，一定要的話考慮用搜索引擎。出于降低數(shù)據(jù)庫服務(wù)器負載的考慮，盡可能的減少數(shù)據(jù)庫模糊查詢。

  1. 注意范圍查詢語句
    對于聯(lián)合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成后面的索引字段失效。

  2. 對查詢進行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。如果排序字段沒有用到索引，就盡量少排序。根據(jù)實際情況進行調(diào)整，因為有時索引太多也會降低性能。

  • order by的列盡量被索引。order by的列如果被索引，性能也會更好
  • 不使用order by rand() limit ~MySQL的隨機抽取實現(xiàn)方法
select id from table order by rand() limit 1000;

上面的SQL語句，可優(yōu)化為：

select id from table  t1 
join  (select rand() * (select max(id) from table ) as nid) t2 on t1.id > t2.nid
limit 1000;
  1. 在 where 子句中對字段進行 null 值判斷，會導致引擎放棄使用索引而進行全表掃描，如：

理由：并不是說使用了 is null 或者 is not null 就會不走索引了，這個跟 MySQL 版本以及查詢成本都有關(guān)。如果 MySQL 優(yōu)化器發(fā)現(xiàn)，走索引比不走索引成本還要高，肯定會放棄索引，這些條件 !=，>，is null，is not null 經(jīng)常被認為讓索引失效，其實是因為一般情況下，查詢的成本高，優(yōu)化器自動放棄索引的。如果把 null 值，換成默認值，很多時候讓走索引成為可能，同時，表達意思會相對清晰一點?！疽姷?1點】

  1. 在 where 子句中使用參數(shù)，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：
  1. 在 where 子句中對字段進行函數(shù)、算術(shù)運算或其他表達式運算，會導致引擎放棄使用索引而進行全表掃描。如：
算術(shù)運算
函數(shù)操作
  1. 為列選擇合適的數(shù)據(jù)類型，而且要避免隱式類型轉(zhuǎn)換
  • where子句中出現(xiàn)column字段的類型和傳入的參數(shù)類型不一致的時候會發(fā)生類型轉(zhuǎn)換，建議先確定where中的參數(shù)類型。
  • 能用TINYINT就不用SMALLINT，能用SMALLINE就不用INT，磁盤和內(nèi)存消耗越小越好。

【如果字段類型是字符串，where 時一定用引號括起來，否則索引失效】
反例：

select * from user where userid = 123;

正例：

select * from user where userid = '123';

為什么第一條語句未加單引號就不走索引了？這是因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL 會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點數(shù)再做比較。

  1. 結(jié)果集允許重復(fù)的話，盡量用union all代替union
    union 和 union all 的差異，主要是前者不管檢索結(jié)果有沒有重復(fù)，都會嘗試進行合并，然后在輸出最終結(jié)果前進行排序、過濾操作，增加大量的CPU運算，加大資源消耗及延遲。而UNINON ALL不去重，效率高于UNION。當然，union all 的前提條件是兩個結(jié)果集沒有重復(fù)數(shù)據(jù)?；蛘呷绻Y(jié)果集允許重復(fù)的話，盡量使用union all 代替 union 。

  2. 如果限制條件中其他字段沒有索引，盡量少用 or。or兩邊的字段中，如果有一個不是索引字段，而其他條件也不是索引字段，會造成該查詢不走索引的情況。應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描。很多時候使用 union all 或者是 union（必要的時候）的方式來代替“or” 會得到更好的效果。新建一個 user 表，它有一個普通索引 userId，表結(jié)構(gòu)如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假設(shè)現(xiàn)在需要查詢 userId 為 1 或者年齡為 18 歲的用戶，很容易有以下 SQL。
反例：

select * from user where userid=1 or age =18

正例：

//使用union all 
select * from user where userid=1 
union all 
select * from user where age = 18

//或者分開兩條sql寫：
select * from user where userid=1
select * from user where age = 18
理由：使用 or 可能會使索引失效，從而全表掃描。

對于 or+沒有索引的 age 這種情況，假設(shè)它走了 userId 的索引，但是走到 age 查詢條件時，它還得全表掃描，也就是需要三步過程：全表掃描+索引掃描+合并，如果它一開始就走全表掃描，直接一遍掃描就完事。MySQL 是有優(yōu)化器的，處于效率與成本考慮，遇到 or 條件，索引可能失效，看起來也合情合理。

  1. 將大的 delete，update 或者 insert 查詢變成多個小查詢

    為了更優(yōu)的性能以及更好的數(shù)據(jù)控制，應(yīng)該將大的 sql 寫成多個小查詢。避免同時修改或刪除過多數(shù)據(jù)，因為會造成 CPU 利用率過高，從而影響別人對數(shù)據(jù)庫的訪問。

反例：

//一次刪除10萬或者100萬+？
delete from user where id <100000;
//或者采用單一循環(huán)操作，效率低，時間漫長
for（User user：list）{
   delete from user； 
}

正例：

//分批進行刪除，如每次500
delete user where id<500
delete product where id>=500 and id<1000;

理由：一次性刪除太多數(shù)據(jù)，可能會有 lock wait timeout exceed 的錯誤，所以建議分批操作。

【關(guān)于 delete 優(yōu)化】一個 delete 語句執(zhí)行了2個多小時了，還沒執(zhí)行完，能不能把這個 SQL 回滾？答案是不能，如果直接回滾，結(jié)果會更慘。一個 SQL 能不能回滾，或者說進行到了什么程度？不建議暴力重啟，先執(zhí)行一個“show engine innodb status\G”語句，這樣能看到執(zhí)行時間。如果沒有運行狀態(tài)，就可以 Kill 掉。

  1. 如果插入數(shù)據(jù)過多，考慮批量插入

反例：

for(User u :list){
 insert into user(name,age) values(#name#,#age#)   
}

正例：

//一次500批量插入，分批進行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

理由：批量插入性能好，更加省時間。
打個比喻：假如需要搬一萬塊磚到樓頂。有一個電梯，電梯一次可以放適量的磚(最多放 500)，可以選擇一次運送一塊磚，也可以一次運送 500 塊磚，哪個時間消耗大?

  1. select語句務(wù)必指明字段名稱，盡量避免使用"select * "。因為它會進行全表掃描，不能有效利用索引，增加很多不必要的消耗（CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬），增大了數(shù)據(jù)庫服務(wù)器的負擔，以及它與應(yīng)用程序客戶端之間的網(wǎng)絡(luò)IO開銷。當表結(jié)構(gòu)發(fā)生改變時，前端也需要更新。所以要求直接在select后面接上字段名。

  2. Inner join 、left join、right join，優(yōu)先使用 Inner join。如果是 left join，左邊表結(jié)果盡量小。Inner join 內(nèi)連接，在兩張表進行連接查詢時，只保留兩張表中完全匹配的結(jié)果集。left join 在兩張表進行連接查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄。right join 在兩張表進行連接查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄。都滿足 SQL 需求的前提下，推薦優(yōu)先使用 Inner join(內(nèi)連接)，如果要使用 left join，左邊表數(shù)據(jù)結(jié)果盡量小，如果有條件的盡量放到左邊處理。

  • MySQL中沒有full join，可以用以下方式來解決：
select * from A 
left join B on B.name = A.name where B.name is null
union all 
select * from B;
  • 盡量使用inner join，避免left join：
    參與聯(lián)合查詢的表至少為2張表，一般都存在大小之分。如果連接方式是inner join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅(qū)動表；但是left join在驅(qū)動表的選擇上遵循的是左邊驅(qū)動右邊的原則，即left join左邊的表名為驅(qū)動表。
  • 合理利用索引：
    被驅(qū)動表的索引字段作為on的限制字段。
  • 利用小表去驅(qū)動大表
    從原理圖能夠直觀的看出如果能夠減少驅(qū)動表的話，減少嵌套循環(huán)中的循環(huán)次數(shù)，以減少 IO總量及CPU運算的次數(shù)。
  • 巧用STRAIGHT_JOIN：
    inner join是由MySQL選擇驅(qū)動表，但是有些特殊情況需要選擇另個表作為驅(qū)動表，比如有g(shù)roup by、order by等「Using filesort」、「Using temporary」時。STRAIGHT_JOIN來強制連接順序，在STRAIGHT_JOIN左邊的表名就是驅(qū)動表，右邊則是被驅(qū)動表。在使用STRAIGHT_JOIN有個前提條件是該查詢是內(nèi)連接，也就是inner join。其他鏈接不推薦使用STRAIGHT_JOIN，否則可能造成查詢結(jié)果不準確。
    這個方式有時能減少3倍的時間。

反例：

select * from tab a left join tab1 b on a.size = b.size where a.id>2;

正例：

select * from (select * from tab where id >2) a left join tab1 b on a.size = b.size;

理由如下：
如果 inner join 是等值連接，或許返回的行數(shù)比較少，所以性能相對會好一點。
同理，使用了左連接，左邊表數(shù)據(jù)結(jié)果盡量小，條件盡量放到左邊處理，意味著返回的行數(shù)可能比較少。

【不要有超過 5 個以上的表連接】連表越多，編譯的時間和開銷也就越大。把連接表拆開成較小的幾個執(zhí)行，可讀性更高。如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著設(shè)計非常糟糕。

  1. SQL語句中 in 和 not in 也要慎用，否則會導致全表掃描。MySQL對于in做了相應(yīng)的優(yōu)化，即將in中的常量全部存儲在一個數(shù)組里面，而且這個數(shù)組是排好序的。但是如果數(shù)值較多，產(chǎn)生的消耗也是比較大的。對于連續(xù)的數(shù)值，能用between就不要用in，再或者使用連接來替換。
  1. 區(qū)分 in 和 exists、not in 和not exists

區(qū)分 in 和 exists 主要是造成了驅(qū)動順序的改變（這是性能變化的關(guān)鍵）。如果是exists，那么以外層表為驅(qū)動表，先被訪問；如果是IN，那么先執(zhí)行子查詢。所以IN適合于外表大而內(nèi)表小的情況；exists適合于外表小而內(nèi)表大的情況。

關(guān)于 not in 和 not exists，推薦使用not exists，不僅僅是效率問題，not in 可能存在邏輯問題。如何高效的寫出一個替代 not exists 的SQL語句？

原SQL語句：

select colname … from tableA 
where a.id not in (select b.id from tableB)

高效的SQL語句：

select colname … from tableA 
left join tableB on a.id = b.id 
where b.id is null

取出的結(jié)果集為A表不在B表中的數(shù)據(jù)。

【exist&in 的合理利用】

假設(shè)表 A 表示某企業(yè)的員工表，表B表示部門表，查詢所有部門的所有員工，很容易有以下 SQL：

select * from A where deptId in (select deptId from B);

這樣寫等價于：

先查詢部門表B
select deptId from B
再由部門deptId，查詢A的員工
select * from A where A.deptId = B.deptId

可以抽象成這樣的一個循環(huán)：

List<> resultSet ;
    for(int i=0;i<B.length;i++) {
          for(int j=0;j<A.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

顯然，除了使用 in，也可以用 exists 實現(xiàn)一樣的查詢功能，如下：

select * from A where exists (select 1 from B where A.deptId = B.deptId);

因為 exists 查詢的理解就是，先執(zhí)行主查詢，獲得數(shù)據(jù)后，再放到子查詢中做條件驗證，根據(jù)驗證結(jié)果(true 或者 false)，來決定主查詢的數(shù)據(jù)結(jié)果是否得意保留。那么，這樣寫就等價于：

select * from A 先從A表做循環(huán)
select * from B where A.deptId = B.deptId 再從B表做循環(huán)。

同理，可以抽象成這樣一個循環(huán)：

List<> resultSet ;
    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].deptId==B[j].deptId) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

數(shù)據(jù)庫最費勁的就是跟程序鏈接釋放。假設(shè)鏈接了兩次，每次做上百萬次的數(shù)據(jù)集查詢，查完就走，這樣就只做了兩。相反建立了上百萬次鏈接，申請鏈接釋放反復(fù)重復(fù)，這樣系統(tǒng)就受不了了。

即 MySQL 優(yōu)化原則，就是小表驅(qū)動大表，小的數(shù)據(jù)集驅(qū)動大的數(shù)據(jù)集，從而讓性能更優(yōu)。因此，我們要選擇最外層循環(huán)小的，也就是，如果 B 的數(shù)據(jù)量小于 A，適合使用 in，如果 B 的數(shù)據(jù)量大于 A，即適合選擇 exist。

  1. 【推薦】in操作能避免則避免，若實在避免不了，需要仔細評估in后邊的集合元素數(shù)量，控制在1000個之內(nèi)。

  2. 給整型字段設(shè)置長度并無意義，int(1)與int(11)通常情況下沒有任何區(qū)別。

  3. 必要時可以使用force index來強制查詢走某個索引
    有的時候MySQL優(yōu)化器采取它認為合適的索引來檢索SQL語句，但是可能它所采用的索引并不是我們想要的。這時就可以采用force index來強制優(yōu)化器使用我們制定的索引。

  4. 【強制】不要使用count(列名)或count(常量)來替代count(*)，count(*)是SQL92定義的標準統(tǒng)計行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟NULL和非NULL無關(guān)。

說明：count(*)會統(tǒng)計值為NULL的行，而count(列名)不會統(tǒng)計此列為NULL值的行。

  1. 不要寫一些沒有意義的查詢，如需要生成一個空表結(jié)構(gòu)：
    select col1,col2 into #t from t where 1=0
    這類代碼不會返回任何結(jié)果集，但是會消耗系統(tǒng)資源的，應(yīng)改成這樣：
    create table #t(...)

  2. 并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的，當索引列有大量數(shù)據(jù)重復(fù)時，SQL查詢可能不會去利用索引。如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。

  3. 索引并不是越多越好

  • 索引并不是越多越好，索引雖然提高了查詢的效率，但是也降低了插入和更新的效率。
  • insert 或 update 時有可能會重建索引，所以建索引需要慎重考慮，視具體情況來定。
  • 一個表的索引數(shù)最好不要超過 6 個，若太多需要考慮一些索引是否沒有存在的必要。

  1. 應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調(diào)整，會耗費相當大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

  2. 盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，否則會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。

反例：

king_id` varchar(20) NOT NULL COMMENT '守護者Id'

正例：

`king_id` int(11) NOT NULL COMMENT '守護者Id'`
  1. 盡可能的使用 varchar/nvarchar 代替 char/nchar 。因為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。

反例：

`deptName` char(100) DEFAULT NULL COMMENT '部門名稱'

正例：

`deptName` varchar(100) DEFAULT NULL COMMENT '部門名稱'

  1. 盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只有主鍵索引）。

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

  3. 臨時表并不是不可使用，適當?shù)厥褂盟鼈兛梢允鼓承├谈行В?，當需要重?fù)引用大型表或常用表中的某個數(shù)據(jù)集時。但是，對于一次性事件，最好使用導出表。

  4. 在新建臨時表時，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。

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

  6. 盡量避免使用游標，因為游標的效率較差，如果游標操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。

  7. 使用基于游標的方法或臨時表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。

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

  9. 在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ，在結(jié)束時設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息。

  10. 盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。

  11. 盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

  12. 使用EXPLAIN分析 SQL 計劃。EXPLAIN可以檢查索引使用情況以及掃描的行。日常開發(fā)寫 SQL 的時候，盡量養(yǎng)成用 explain 分析 SQL 的習慣，尤其是走不走索引這一塊。

  13. 慎用 distinct 關(guān)鍵字

distinct 關(guān)鍵字一般用來過濾重復(fù)記錄，以返回不重復(fù)的記錄。在查詢一個字段或者很少字段的情況下使用時，給查詢帶來優(yōu)化效果。但是在字段很多的時候使用，卻會大大降低查詢效率。

反例：

select distinct * from user;

正例：

select distinct name from user;

理由：帶 distinct 的語句 CPU 時間和占用時間都高于不帶 distinct 的語句。因為當查詢很多字段時，如果使用 distinct，數(shù)據(jù)庫引擎就會對數(shù)據(jù)進行比較，過濾掉重復(fù)數(shù)據(jù)，然而這個比較、過濾的過程會占用系統(tǒng)資源，CPU 時間。

【強制】count(distinct col) 計算該列除NULL之外的不重復(fù)行數(shù)，注意 count(distinct col1, col2) 如果其中一列全為NULL，那么即使另一列有不同的值，也返回為0。

  1. 【強制】當某一列的值全是NULL時，count(col)的返回結(jié)果為0，但sum(col)的返回結(jié)果為NULL，因此使用sum()時需注意NPE問題。

正例：可以使用如下方式來避免sum的NPE問題：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;

  1. 【強制】使用ISNULL()來判斷是否為NULL值。

說明：NULL與任何值的直接比較都為NULL。

1） NULL<>NULL的返回結(jié)果是NULL，而不是false。

2） NULL=NULL的返回結(jié)果是NULL，而不是true。

3） NULL<>1的返回結(jié)果是NULL，而不是true。

  1. 【強制】不得使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決。

說明：以學生和成績的關(guān)系為例，學生表中的student_id是主鍵，那么成績表中的student_id則為外鍵。如果更新學生表中的student_id，同時觸發(fā)成績表中的student_id更新，即為級聯(lián)更新。外鍵與級聯(lián)更新適用于單機低并發(fā)，不適合分布式、高并發(fā)集群；級聯(lián)更新是強阻塞，存在數(shù)據(jù)庫更新風暴的風險；外鍵影響數(shù)據(jù)庫的插入速度。

  1. 【強制】禁止使用存儲過程，存儲過程難以調(diào)試和擴展，更沒有移植性。

  2. 【強制】數(shù)據(jù)訂正（特別是刪除、修改記錄操作）時，要先select，避免出現(xiàn)誤刪除，確認無誤才能執(zhí)行更新語句。

  3. 【參考】如果有國際化需要，所有的字符存儲與表示，均以utf-8編碼，注意字符統(tǒng)計函數(shù)的區(qū)別。

說明：
SELECT LENGTH("輕松工作")； 返回為12
SELECT CHARACTER_LENGTH("輕松工作")； 返回為4

如果需要存儲表情，那么選擇utf8mb4來進行存儲，注意它與utf-8編碼的區(qū)別。

  1. 【參考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少，但TRUNCATE無事務(wù)且不觸發(fā)trigger，有可能造成事故，故不建議在開發(fā)代碼中使用此語句。 說明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。

  2. 為獲得相同結(jié)果集的多次執(zhí)行，請保持SQL語句前后一致。這樣做的目的是為了充分利用查詢緩沖。比如根據(jù)地域和產(chǎn)品ID查詢產(chǎn)品價格，第一次使用了：
    select price from order where id='123' and region='BEIJING'
    那么第二次同樣的查詢，請保持以上語句的一致性，比如不要將where語句里面的id和region位置調(diào)換順序。

  3. 為了提高 group by 語句的效率，可以在執(zhí)行到該語句前，把不需要的記錄過濾掉。

反例：

select job,avg(salary) from employee
group by job 
having job ='president' or job = 'managent'

正例：

select job,avg(salary) from employee
where job ='president' or job = 'managent' 
group by job

  1. 索引不會包含有 NULL 值的列
    只要列中包含有 NULL 值都將不會被包含在索引中，復(fù)合索引中只要有一列含有 NULL 值，那么這一列對于此復(fù)合索引就是無效的。所以在數(shù)據(jù)庫設(shè)計時不能讓字段的默認值為 NULL。

  2. 索引列排序
    MySQL查詢只使用一個索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會使用索引的。因此數(shù)據(jù)庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引

  3. 字符字段只建前綴索引

  4. 字符字段最好不要做主鍵

  5. 盡量不用UNIQUE，由程序保證約束

  6. 明知只有一條查詢結(jié)果，那請使用 “LIMIT 1”

“LIMIT 1”可以避免全表掃描，找到對應(yīng)結(jié)果就不會再繼續(xù)掃描了。

  1. varchar應(yīng)給設(shè)置一個合適的長度而不是給一個很大的長度，因為MySQL建立索引時一般會默認使用的該字段的長度，占用存儲空間且降低性能，另外，所有列總長度被限制為65,535字節(jié)。

  2. text、blob字段應(yīng)該從頻繁查詢的表中分離出去，因為text、blob之類的長列會完全被MySQL保存在一個單獨的數(shù)據(jù)頁里面里，查詢效率低。

  3. 不要輕易使用查詢緩存，特別是寫密集型應(yīng)用，緩存失效會占用大量的系統(tǒng)資源。

二、Oracle中的in參數(shù)的個數(shù)限制：

Oracle 中 in 后括號中的參數(shù)個數(shù)有限制，Oracle 9i 中個數(shù)不能超過256，Oracle 10g個數(shù)不能超過1000。
當in的個數(shù)大于1000時，有以下兩個解決辦法：

  1. 對參數(shù)進行處理，分成多個in，其中每個in列表中參數(shù)都小于1000。如 params in(1,2,3.........1000) or params in(1001,1002...2000)。
    不過這種方法性能和維護性方面不好
  2. 將in后面的字符串改成子查詢，將in里面的數(shù)據(jù)保存到臨時表中，params in（select ....from dual)

三、SQL語句中過濾條件where和having的區(qū)別

  1. where 是一個約束聲明，使用 where 約束來自數(shù)據(jù)庫的數(shù)據(jù)，where 是在結(jié)果返回之前起作用，where 中不能使用聚合函數(shù)。
  2. Having 是一個過濾聲明，是在查詢返回結(jié)果集以后對查詢結(jié)果進行的過濾操作，在 Having 中可以使用聚合函數(shù)。
  3. 在查詢過程中，where 子句執(zhí)行優(yōu)先級高于聚合語句。聚合語句(sum，min，max，avg，count)優(yōu)先級高于 having 子句。

四、一個表建有多個索引，Oracle如何選擇

一個表最多可有255 個字段和32個索引。

  1. 表的主鍵、外鍵必須有索引；
  2. 數(shù)據(jù)量超過300的表應(yīng)該有索引；
  3. 經(jīng)常與其他表進行連接的表，在連接字段上應(yīng)該建立索引；
  4. 經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引；
  5. 索引應(yīng)該建在選擇性高的字段上；
  6. 索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；
  7. 復(fù)合索引的建立需要進行仔細分析；盡量考慮用單字段索引代替：
    ①正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段。
    ②復(fù)合索引的幾個字段是否經(jīng)常同時以 AND 方式出現(xiàn)在 Where 子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引。
    ③復(fù)合索引中包含的字段經(jīng)常單獨出現(xiàn)在 where 子句中，則分解為多個單字段索引。
    ④復(fù)合索引所包含的字段超過3個，要仔細考慮必要性，考慮減少復(fù)合的字段。
    ⑤既有單字段索引，又有這幾個字段上的復(fù)合索引，可以刪除復(fù)合索引。
  8. 頻繁進行數(shù)據(jù)操作的表，不要建立太多的索引；
  9. 刪除無用的索引，避免對執(zhí)行計劃造成負面影響；
    以上是一些普遍的建立索引時的判斷依據(jù)。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應(yīng)該經(jīng)過仔細分析，要有建立的依據(jù)。因為太多的索引與不充分、不正確的索引對性能都毫無益處：在表上建立的每個索引都會增加存儲開銷，索引對于插入、刪除、更新操作也會增加處理上的開銷。另外，過多的復(fù)合索引，在有單字段索引的情況下，一般都是沒有存在價值的；相反，還會降低數(shù)據(jù)增加刪除時的性能，特別是對頻繁更新的表來說，負面影響更大。

五、商品表，商品有類目屬性，找出某個類目下最新創(chuàng)建的100個商品，表索引怎么設(shè)計?

  1. 類目加索引，需要遍歷類目下所有商品，當類目商品數(shù)量多時，執(zhí)行速度慢，不同類目的執(zhí)行效率不一樣。
  2. 創(chuàng)建時間加索引，執(zhí)行過程會命中創(chuàng)建時間索引，按照時間倒排篩選類目，如果當前類目很長時間沒有新品創(chuàng)建，有可能遍歷整個表。
  3. 類目+創(chuàng)建時間聯(lián)合索引，先命中類目索引，然后按照創(chuàng)建時間后續(xù)遍歷就可以拿到最新的100條。

六、一個6億的表A，一個3億的表B，通過外鍵tid關(guān)聯(lián)，如何最快查詢出有關(guān)聯(lián)關(guān)系的第50000到第50200中的這200條數(shù)據(jù)記錄

1、如果 A 表 TID 是自增長，并且是連續(xù)的，B 表的 ID 為索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
2、如果 A 表的 TID 不是連續(xù)的，那么就需要使用覆蓋索引。TID 要么是主鍵，要么是覆蓋索引，B 表 ID 也需要有索引。
select * from b, (select tid from a limit 50000,200) a where b.id = a.tid;

總結(jié)：SQL調(diào)優(yōu)方法很多，同樣的查詢結(jié)果可以有很多種不同的查詢方式。其實最好的方法就是在開發(fā)環(huán)境中用最貼近真實的數(shù)據(jù)集和硬件環(huán)境進行測試，然后再發(fā)布到生產(chǎn)環(huán)境中。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 昨晚就打算寫的，但是要帶孩子睡覺，一直等到今天送孩子上學回來，此刻的心情，就像住的房子里，有這么一扇窗子，從不曾打...
    愛生活的檸檬閱讀 885評論 26贊 30
  • 5句圖圖他
    凡眼觀世界閱讀 195評論 0贊 0
  • 清明本是雨紛紛，今年是個例外，天氣特別好，特別藍。在家做了做飯，帶孩子做了推拿，周邊逛了逛，想做做頭發(fā)（但想到有孩...
    alicce閱讀 329評論 0贊 1
  • 陽春三月 陽光燦爛三月天，柳樹成蔭綠絲鮮。 路邊樹枝嫩芽顯，青青小草長得歡...
    曉英寫事閱讀 1,146評論 21贊 29
  • 今天是2018年12月28日，星期五。 今天中午，藍老師叫我們在教室門口排隊，藍老師叫我喊口令。有幾位同...
    黃穎嫣閱讀 211評論 0贊 0

友情鏈接更多精彩內(nèi)容