mysql技術內幕筆記

標籤：使用   ar   for   strong   sp   資料   art   on   ad   

1.innodb對於獨立子查詢，具體執行時會轉換成關聯子查詢然後執行，這個效能是很差的，比如

select * from upload_files where file_time in (select max(file_time) file_time from upload_files group by from_unixtime(file_time,‘%Y%m‘)); 針對2000多條資料要執行14秒才能完成，顯然太慢；

直接寫成內關聯查詢 select *,from_unixtime(a.file_time,‘%Y%m‘) tt from upload_files a inner join   (select max(file_time) file_time from upload_files group by from_unixtime(file_time,‘%Y%m‘)) b on a.file_time =b.file_time 就好多了，執行時間只有0.34秒；

直接改成關聯查詢也可以（多一層）select *,from_unixtime(file_time,‘%Y%m‘) tt from upload_files a  where exists(select * from  (select max(file_time) file_time from upload_files group by from_unixtime(file_time,‘%Y%m‘)) b where a.file_time = b.file_time);這個效能和內關聯是一樣的。
2.查詢每個員工最大訂單日期的訂單（員工號和訂單日期主查詢和子查詢都必須匹配）：

雖然可以得到正確結果，但效能太差；使用派生表可以避免子查詢和外查詢過多的比較操作，派生表的方法為：

此方法效能提升很多。如果每個人只想顯示一條資料的話則sql語句（需要找出最大的訂單號）變為

派生表比子查詢的效能要好

但這個不會對執行有任何的影響

3.

因此對於條件列含有null值時，in必須對null值進行過濾才能得出和exists相同的結果，比如select * from site_article_channel where article_id not in (10,12,null)

article_id 不為10,12的資料存在，但not in (10,12,null)的值為false或unknown，故此得不到任何結果

4.

5.natural join可以理解為將兩個表相同名稱的列進行關聯，不需要指明；例如

6.straight join功能上等同於join，它為使用者提供了一種方法可以用來控制最佳化器，

這個情況只適用於多表關聯查詢時，最佳化器無法提供正確的最佳化方案的情況，使用者可以強制最佳化器按使用者指定的順序去關聯表

7.self join就是表自己串連自己，self並不會出現

8.unoin（集合操作，垂直操作），mysql支援unoin all和unoin distinct兩種集合操作方式，進行集合操作的兩張表列數必須相同，如果資料類型不同，資料庫會進行隱式轉換，同時結果列的名稱由第一個表決定；unoin distinct一般寫為unoin ，distinct會省略掉；所以對於unoin all中的all則必須帶著，用以區分。

 

9.pivoting：技術是指一種可以把行轉換為列的技術，Unpivoting可以看作是pivoting的反向操作，即將列旋轉成為行

10.rollup是根據維度在資料結果集中進行彙總操作，其優點是一次可以取得N次group by的結果；對單列執行rollup的情況
此時結果為

比較不添加rollup的情況，最後多了一個合計列，這在需要摘要資料的時候還是有必要的。

多列執行rollup的情況，
得到結果如下


 

其結果等同於

但效能上要好很多，只需要一次表掃描即可，而後者需要做四次表的掃描；不過需要注意的是rollup不能與order by一起使用，那麼與limit一起使用文法上可以但一般沒有意義，另外就是對null的處理，如果彙總的列含有null值則可能出錯，這時最好進行ifnull（列明，替換值）處理。

11.事務（扁平事務，帶儲存點的扁平事務，鏈事務，嵌套事務，其中扁平事務最常用）：

事務 

採用事務對於插入或更新，刪除等操作，操作完畢一次提交，比不適用事務的情況效能要好，因為採用事務唯寫了一次日誌，而不不使用的情況則會有多次寫日誌操作

innodb儲存引擎會對操作自動認可，有時這不是個好事情，對於事務開啟後這個自動功能是會禁用的，等事務結束系統會自動開啟；對於“事務要短小，不能有長時間啟動並執行大事務”這條在mysql 的innodb上不適用。

12.輔助索引一般體積比叢集索引小很多（它不包含所有的列），如果查詢條件可以命中輔助索引效能一般會更好，此時不需要使用叢集索引，稱為覆蓋索引。

在某些特殊的情況下可以通過查詢語句後面跟force  index（索引名）強制查詢採用某個索引，例如
；
例如
force則不同，他是強制的

13.分區：innodb，myisam以及ndb三種引擎都支援分區，csv等就不支援分區

局部分區：資料和相關索引存放在一個分區內

全域分區：資料存放在各個分區內，索引單獨存放

水平資料分割：按行進行分區，同一個行肯定在一個分區內

垂直資料分割：按列分區，同一個列肯定在一個分區內

目前mysql僅支援局部分區，並且僅支援水平資料分割不支援垂直資料分割，mysql的分區分為四種類型，

• range（屬於某個條件連續區間的資料放入一個分區），
• list（面向離散的值），
• hash（根據使用者自訂的運算式的值來進行分區，傳回值不能為負數），
• key（根據資料庫的散列函數來進行分區）

不論那種類型的分區，如果表存在主鍵或唯一索引，那麼分區列必須是他們的一部分；如果沒有主鍵或唯一索引，那麼任何列都可以作為分區列。

注意：對於range類型分區，如果按年分區的話，查詢條件是日期，那麼依然會掃描所有分區，得不到應有的效果，如果採用
這種分區才是合適的，因此分區要考慮實際的使用情境。最好查詢條件和分區標準一致。

list類型分區與讓個類似，只是採用了values in（1,2,3等類似的散列值）；

對於插入不符合分區的值時，range和list分區類型都一樣，會報錯，無法插入；如果一次插入多條資料，對於不同的引擎又有區別：

innodb會將多個插入是為一個事務，遇到錯誤，整體復原；MyIsam則不同，錯誤前的插入，錯誤後的無法插入，其實還是本質上對事務是否支援的區別。

mysql5.5以前的版本，分區的條件必選是整型值（如果不是必選通過函數轉換成整型值）；5.5以後則可以用整型值，時間以及字串等欄位作為條件。

子分區：如果要建立子分區，則所有分區上的子分區個數必須相同並且名稱不能重複。

Null值：mysql將null值視為比任何非null值都小，分區時也一樣；

B+樹可以很好的完成大資料表的操作，不需要分區的協助，相反分區如果不合理，則還會降低效能；相對而言，分區的技術更適合於olap的業務，而非oltp的業務。

一般來講，對於符合分區條件的查詢才會很快，對於不合格則查詢成本反而會成倍增長。

 

 

 

 

 

 

 

 

 

 

mysql技術內幕筆記