幾個SQL問題

1、索引是什麼？

索引：

索引是一種特殊的查詢表，可以被資料庫搜尋引擎用來加速資料的檢索。簡單說來，索引就是指向表中資料的指針。資料庫的索引同書籍後面的索引非常相像。

例如，如果想要查閱一本書中與某個特定主題相關的所有頁面，你會先去查詢索引（索引按照字母表順序列出了所有主題），然後從索引中找到一頁或者多頁與該主題相關的頁面。

索引能夠提高 SELECT 查詢和 WHERE 子句的速度，但是卻降低了包含 UPDATE 語句或 INSERT 語句的資料輸入過程的速度。索引的建立與删除不會對表中的資料産生影響。

建立索引需要使用 CREATE INDEX 語句，該語句允許對索引命名，指定要建立索引的表以及對哪些列進行索引，還可以指定索引按照升序或者降序排列。

同 UNIQUE 限制一樣，索引可以是唯一的。這種情況下，索引會阻止列中（或者列的組合，其中某些列有索引）出現重複的條目。

CREATE INDEX 指令：

CREATE INDEX index_name ON table_name;      

單例索引

單列索引基于單一的字段建立，其基本文法：

CREATE INDEX index_name
ON table_name (column_name);      

唯一索引：

唯一索引不止用于提升查詢性能，還用于保證資料完整性。唯一索引不允許向表中插入任何重複值。其基本文法如下所示：

CREATE UNIQUE INDEX index_name
on table_name (column_name);      

聚簇索引：

聚簇索引在表中兩個或更多的列的基礎上建立。

CREATE INDEX index_name
on table_name (column1, column2);      

建立單列索引還是聚簇索引，要看每次查詢中，哪些列在作為過濾條件的 WHERE 子句中最常出現。

如果隻需要一列，那麼就應當建立單列索引。如果作為過濾條件的 WHERE 子句用到了兩個或者更多的列，那麼聚簇索引就是最好的選擇。

2、sql WHERE字句中“AND”連接配接的多個條件的次序？

如果是多表查詢的，将資料較少的表作為基礎表。

單表查詢，将出現機率大的放在前面。

因為WHERE字句AND後的執行是在AND前的查詢結果的基礎上再次查詢。

3、sql查詢怎麼優化？

1、避免在where子句中使用 is null 或 is not null 對字段進行判斷。

如：

select id from table where name is null      

在這個查詢中，就算我們為 name 字段設定了索引，查詢分析器也不會使用，是以查詢效率底下。為了避免這樣的查詢，在資料庫設計的時候，盡量将可能會出現 null 值的字段設定預設值，這裡如果我們将 name 字段的預設值設定為0，那麼我們就可以這樣查詢：

select id from table where name = 0      

2、避免在 where 子句中使用 != 或 <> 操作符。

select name from table where id <> 0      

資料庫在查詢時，對 != 或 <> 操作符不會使用索引，而對于 < 、 <= 、 = 、 > 、 >= 、 BETWEEN AND，資料庫才會使用索引。是以對于上面的查詢，正确寫法應該是：

select name from table where id < 0

union all

select name from table where id > 0

這裡我們為什麼沒有使用 or 來連結 where 後的兩個條件呢？這就是我們下面要說的第3個優化技巧。

3、避免在 where 子句中使用 or來連結條件。

select id from tabel where name = 'UncleToo' or name = 'PHP'      

這種情況，我們可以這樣寫：

select id from tabel where name = 'UncleToo'

union all

select id from tabel where name = 'PHP'      

4、少用 in 或 not in。

雖然對于 in 的條件會使用索引，不會全表掃描，但是在某些特定的情況，使用其他方法也許效果更好。如：

select name from tabel where id in(1,2,3,4,5)      

像這種連續的數值，我們可以使用 BETWEEN AND，如：

select name from tabel where id between 1 and 5      

5、注意 like 中通配符的使用。

下面的語句會導緻全表掃描，盡量少用。如：

select id from tabel where name like'%UncleToo%'      

或者

select id from tabel where name like'%UncleToo'      

而下面的語句執行效率要快的多，因為它使用了索引：

select id from tabel where name like'UncleToo%'      

6、避免在 where 子句中對字段進行表達式操作。

select name from table where id/2 = 100      

正确的寫法應該是：

select name from table where id = 100*2
      

7、避免在 where 子句中對字段進行函數操作。

select id from table where substring(name,1,8) = 'UncleToo'      

或

select id from table where datediff(day,datefield,'2014-07-17') >= 0      

這兩條語句中都對字段進行了函數處理，這樣就是的查詢分析器放棄了索引的使用。正确的寫法是這樣的：

select id from table where name like'UncleToo%'      

select id from table where datefield <= '2014-07-17'      

也就是說，不要在 where 子句中的 = 左邊進行函數、算術運算或其他表達式運算。

8、在子查詢中，用 exists 代替 in 是一個好的選擇。

select name from a where id in(select id from b)       

如果我們将這條語句換成下面的寫法：

select name from a where exists(select 1 from b where id = a.id)      

4、DROP、TRUNCATE、DELETE的差別？

1、DELETE語句執行删除的過程是每次從表中删除一行，并且同時将該行的删除操作作為事務記錄在日志中儲存以便進行進行復原操作。

TRUNCATE TABLE 則一次性地從表中删除所有的資料并不把單獨的删除操作記錄記入日志儲存，删除行是不能恢複的。并且在删除的過程中不會激活與表有關的删除觸發器。執行速度快。

2、表和索引所占空間。

當表被TRUNCATE 後，這個表和索引所占用的空間會恢複到初始大小，

DELETE操作不會減少表或索引所占用的空間。

drop語句将表所占用的空間全釋放掉。

3、一般而言，drop > truncate > delete

4、應用範圍。

TRUNCATE 隻能對TABLE；        
 DELETE可以是table和view      

5、TRUNCATE 和DELETE隻删除資料， DROP則删除整個表（結構和資料）。

6、truncate與不帶where的delete ：隻删除資料，而不删除表的結構（定義）drop語句将删除表的結構被依賴的限制（constrain),觸發器（trigger)索引（index);依賴于該表的存儲過程/函數将被保留，但其狀态會變為：invalid。

7、delete語句為DML（data maintain Language),這個操作會被放到 rollback segment中,事務送出後才生效。如果有相應的 tigger,執行的時候将被觸發。

8、truncate、drop是DLL（data define language),操作立即生效，原資料不放到 rollback segment中，不能復原

9、在沒有備份情況下，謹慎使用 drop 與 truncate。要删除部分資料行采用delete且注意結合where來限制影響範圍。復原段要足夠大。要删除表用drop;若想保留表而将表中資料删除，如果于事務無關，用truncate即可實作。如果和事務有關，或老師想觸發trigger,還是用delete。

10、 Truncate table 表名 速度快,而且效率高,因為:

truncate table 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少。DELETE 語句每次删除一行，并在事務日志中為所删除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表資料所用的資料頁來删除資料，并且隻在事務日志中記錄頁的釋放。

11、 TRUNCATE TABLE 删除表中的所有行，但表結構及其列、限制、索引等保持不變。新行辨別所用的計數值重置為該列的種子。如果想保留辨別計數值，請改用 DELETE。如果要删除表定義及其資料，請使用 DROP TABLE 語句。

12、 對于由 FOREIGN KEY 限制引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，是以它不能激活觸發器。

一、delete

1、delete是DML，執行delete操作時，每次從表中删除一行，并且同時将該行的的删除操作記錄在redo和undo表空間中以便進行復原（rollback）和重做操作，但要注意表空間要足夠大，需要手動送出（commit）操作才能生效，可以通過rollback撤消操作。

2、delete可根據條件删除表中滿足條件的資料，如果不指定where子句，那麼删除表中所有記錄。

3、delete語句不影響表所占用的extent，高水線(high watermark)保持原位置不變。

二、truncate

1、truncate是DDL，會隐式送出，是以，不能復原，不會觸發觸發器。

2、truncate會删除表中所有記錄，并且将重新設定高水線和所有的索引，預設情況下将空間釋放到minextents個extent，除非使用reuse storage，。不會記錄日志，是以執行速度很快，但不能通過rollback撤消操作（如果一不小心把一個表truncate掉，也是可以恢複的，隻是不能通過rollback來恢複）。

3、對于外鍵（foreignkey ）限制引用的表，不能使用 truncate table，而應使用不帶 where 子句的 delete 語句。

4、truncatetable不能用于參與了索引視圖的表。

三、drop

1、drop是DDL，會隐式送出，是以，不能復原，不會觸發觸發器。

2、drop語句删除表結構及所有資料，并将表所占用的空間全部釋放。

3、drop語句将删除表的結構所依賴的限制，觸發器，索引，依賴于該表的存儲過程/函數将保留,但是變為invalid狀态。

總結：

1、在速度上，一般來說，drop> truncate > delete。

2、在使用drop和truncate時一定要注意，雖然可以恢複，但為了減少麻煩，還是要慎重。

3、如果想删除部分資料用delete，注意帶上where子句，復原段要足夠大；

如果想删除表，當然用drop；

如果想保留表而将所有資料删除，如果和事務無關，用truncate即可；

如果和事務有關，或者想觸發trigger，還是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新導入/插入資料。

參考：

【1】、

https://www.w3cschool.cn/sql/cuj91oz2.html

【2】

https://www.cnblogs.com/angleSJW/archive/2011/01/22/1942046.html

【3】、

https://blog.csdn.net/csdnstudent/article/details/40398245

【4】、

https://blog.csdn.net/u012942818/article/details/53969972

【5】、

https://www.cnblogs.com/zhizhao/p/7825469.html

​​