視圖 1. 為什麼要使用視圖？什麼是視圖？ 為了提高複雜 SQL 語句的復用性和表操作的安全性，MySQL 資料庫管理系統提供了視圖特性。所謂視圖，本質上是一種虛擬表，在物理上是不存在的，其內容與真實的表相似，包含一系列帶有名稱的列和行數據。但是，視圖並不在資料庫中以儲存的數據值形式存在。行和列數據 ...
視圖
1. 為什麼要使用視圖？什麼是視圖？
- 為了提高複雜 SQL 語句的復用性和表操作的安全性，MySQL 資料庫管理系統提供了視圖特性。所謂視圖，本質上是一種虛擬表，在物理上是不存在的，其內容與真實的表相似，包含一系列帶有名稱的列和行數據。但是，視圖並不在資料庫中以儲存的數據值形式存在。行和列數據來自定義視圖的查詢所引用基本表，並且在具體引用視圖時動態生成。
- 視圖使開發者只關心感興趣的某些特定數據和所負責的特定任務，只能看到視圖中所定義的數據，而不是視圖所引用表中的數據，從而提高了資料庫中數據的安全性。
2. 視圖有哪些特點？
視圖的特點如下:
- 視圖的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關係。
- 視圖是由基本表（實表）產生的表（虛表）。
- 視圖的建立和刪除不影響基本表。
- 對視圖內容的更新（添加，刪除和修改）直接影響基本表。
- 當視圖來自多個基本表時，不允許添加和刪除數據。
- 視圖的操作包括創建視圖，查看視圖，刪除視圖和修改視圖。
3. 視圖的使用場景有哪些？
視圖根本用途：簡化 SQL 查詢，提高開發效率。如果說還有另外一個用途，那就是相容老的表結構。
- 重用 SQL 語句；
- 簡化複雜的 SQL 操作。在編寫查詢後，可以方便地重用它而不必知道它的基本查詢細節；
- 使用表的組成部分而不是整個表；
- 保護數據。可以給用戶授予表的特定部分的訪問許可權而不是整個表的訪問許可權；
- 更改數據格式和表示。視圖可返回與底層表的表示和格式不同的數據。
4. 視圖的優點？
- 查詢簡單化。視圖能簡化用戶的操作；
- 數據安全性。視圖使用戶能以多種角度看待同一數據，能夠對機密數據提供安全保護；
- 邏輯數據獨立性。視圖對重構資料庫提供了一定程度的邏輯獨立性。
5. 視圖的缺點？
- 性能。資料庫必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個複雜的多表查詢所定義，那麼，即使是視圖的一個簡單查詢，資料庫也把它變成一個複雜的結合體，需要花費一定的時間。
- 修改限制。當用戶試圖修改視圖的某些行時，資料庫必須把它轉化為對基本表的某些行的修改。事實上，當從視圖中插入或者刪除時，情況也是這樣。對於簡單視圖來說，這是很方便的，但是，對於比較複雜的視圖，可能是不可修改的。
這些視圖有如下特征：
- 有 UNIQUE 等集合操作符的視圖；
- 有 GROUP BY 子句的視圖；
- 有諸如 AVG、SUM、MAX 等聚合函數的視圖；
- 使用 DISTINCT 關鍵字的視圖；
- 連接表的視圖（其中有些例外）。
6. 什麼是游標？
游標是系統為用戶開設的一個數據緩衝區，存放 SQL 語句的執行結果，每個游標區都有一個名字。用戶可以通過游標逐一獲取記錄並賦給主變數，交由主語言進一步處理。
7. 如何定位及優化 SQL 語句的性能問題？創建的索引有沒有被使用到？或者說怎麼才可以知道這條語句運行很慢的原因？
8. 大表數據查詢，怎麼優化？
- 優化 schema、SQL 語句 + 索引；
- 加緩存，如 memcached, redis；
- 主從複製，讀寫分離；
- 垂直拆分，根據你模塊的耦合度，將一個大的系統分為多個小的系統，也就是分散式系統；
- 水平切分，針對數據量大的表，這一步最麻煩，最能考驗技術水平，要選擇一個合理的 sharding key，為了有好的查詢效率，表結構也要改動，做一定的冗餘，應用也要改，SQL 中儘量帶 sharding key，將數據定位到限定的表上去查，而不是掃描全部的表。
9. MySQL 分頁？
LIMIT 子句可以被用於強制 SELECT 語句返回指定的記錄數。LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個返回記錄行的偏移量，第二個參數指定返回記錄行的最大數目。初始記錄行的偏移量是 0（而不是 1）。
mysql> SELECT * FROM table LIMIT 5,10; -- 檢索記錄行 6-15
為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個參數為 -1：
mysql> SELECT * FROM table LIMIT 95,-1; -- 檢索記錄行 96-last.
如果只給定一個參數，它表示返回最大的記錄行數目：
mysql> SELECT * FROM table LIMIT 5; -- 檢索前 5 個記錄行
換句話說，LIMIT n 等價於 LIMIT 0,n。
10. 慢查詢日誌？
用於記錄執行時間超過某個臨界值的 SQL 日誌，用於快速定位慢查詢，為我們的優化做參考。
開啟慢查詢日誌
配置項：slow_query_log
可以使用 SHOW VARIABLES LIKE 'slow_query_log' 查看是否開啟，如果狀態值為 OFF，可以使用 SET GLOBAL slow_query_log = ON 來開啟，它會在 datadir 下產生一個 xx-slow.log 的文件。
設置臨界時間
配置項：long_query_time
查看：SHOW VARIABLES LIKE 'long_query_time'，單位秒
設置：SET long_query_time=0.5
實操時應該從長時間設置到短的時間，即將最慢的 SQL 優化掉。
查看日誌，一旦 SQL 超過了我們設置的臨界時間就會被記錄到 xxx-slow.log 中。
11. 關心過業務系統裡面的 SQL 耗時嗎？統計過慢查詢嗎？對慢查詢都怎麼優化過？
在業務系統中，除了使用主鍵進行的查詢，其他的我都會在測試庫上測試其耗時，慢查詢的統計主要由運維在做，會定期將業務中的慢查詢反饋給我們。
慢查詢的優化首先要搞明白慢的原因是什麼？是查詢條件沒有命中索引？是載入了不需要的數據列？還是數據量太大？
優化也是針對這三個方向：
- 首先分析語句，看看是否載入了額外的數據，可能是查詢了多餘的行並且拋棄掉了，可能是載入了許多結果中並不需要的列，對語句進行分析以及重寫；
- 分析語句的執行計劃，然後獲得其使用索引的情況，之後修改語句或者修改索引，使得語句可以儘可能地命中索引；
- 如果對語句的優化已經無法進行，可以考慮表中的數據量是否太大，如果是的話可以進行橫向或者縱向的分表。
12. 優化查詢過程中的數據訪問？
- 訪問數據太多導致查詢性能下降；
- 確定應用程式是否在檢索大量超過需要的數據，可能是太多行或列；
- 確認 MySQL 伺服器是否在分析大量不必要的數據行；
- 避免犯如下 SQL 語句錯誤：
- 查詢不需要的數據。解決辦法：使用
LIMIT解決； - 多表關聯返回全部列。解決辦法：指定列名；
- 總是返回全部列。解決辦法：避免使用
SELECT *； - 重覆查詢相同的數據。解決辦法：可以緩存數據，下次直接讀取緩存；
- 是否在掃描額外的記錄。解決辦法：
- 使用
EXPLAIN進行分析，如果發現查詢需要掃描大量的數據，但只返回少數的行，可以通過如下技巧去優化：- 使用索引覆蓋掃描，把所有的列都放到索引中，這樣存儲引擎不需要回表獲取對應行就可以返回結果；
- 改變資料庫和表的結構，修改數據表範式；
- 重寫 SQL 語句，讓優化器可以以更優的方式執行查詢。
- 使用
- 查詢不需要的數據。解決辦法：使用
13. 優化長難的查詢語句？
- 一個複雜查詢還是多個簡單查詢；
- MySQL 內部每秒能掃描記憶體中上百萬行數據，相比之下，響應數據給客戶端就要慢得多；
- 使用儘可能小的查詢是好的，但是有時將一個大的查詢分解為多個小的查詢是很有必要的；
- 切分查詢，將一個大的查詢分為多個小的相同的查詢；
- 一次性刪除 1000 萬的數據要比一次刪除 1 萬，暫停一會的方案更加損耗伺服器開銷；
- 分解關聯查詢，讓緩存的效率更高。執行單個查詢可以減少鎖的競爭；
- 在應用層做關聯更容易對資料庫進行拆分。查詢效率會有大幅提升；
- 較少冗餘記錄的查詢。
14. 優化特定類型的查詢語句？
COUNT(*)會忽略所有的列，直接統計所有列數，不要使用COUNT(列名)；- 在 MyISAM 中，沒有任何
WHERE條件的COUNT(*)非常快。當有WHERE條件時，MyISAM 的COUNT統計不一定比其他引擎快； - 可以使用
EXPLAIN查詢近似值，用近似值替代COUNT(*)； - 增加彙總表；
- 使用緩存。
15. 優化關聯查詢？
- 確定
ON或USING子句中是否有索引； - 確保
GROUP BY和ORDER BY只有一個表中的列，這樣 MySQL 才有可能使用索引。
16. 優化子查詢？
- 用關聯查詢替代；
- 優化
GROUP BY和DISTINCT：- 這兩種查詢可以使用索引來優化，是最有效的優化方法；
- 關聯查詢中，使用標識列分組的效率更高；
- 如果不需要
ORDER BY，進行GROUP BY時加ORDER BY NULL，MySQL 不會再進行文件排序； WITH ROLLUP超級聚合，可以挪到應用程式處理。
17. 優化 LIMIT 分頁？
LIMIT偏移量大的時候，查詢效率較低；- 可以記錄上次查詢的最大 ID，下次查詢時直接根據該 ID 來查詢。
18. 優化 UNION 查詢？
UNION ALL的效率高於UNION。
19. 優化 WHERE 子句？
- 確保
WHERE子句中的條件能有效利用索引； - 避免在
WHERE子句中進行計算或轉換操作，儘量將計算移到查詢外部處理。
20. 資料庫為什麼要優化？
- 系統的吞吐量瓶頸往往出現在資料庫的訪問速度上
- 隨著應用程式的運行，資料庫的中的數據會越來越多，處理時間會相應變慢
- 數據是存放在磁碟上的，讀寫速度無法和記憶體相比
- 優化原則：減少系統瓶頸，減少資源占用，增加系統的反應速度。
