mysql内部元件架構，索引管理，視圖view

mysql内部元件架構，索引管理，視圖view

                                                                                                  ——以下内容摘自馬哥教育課堂

===

單程序多線程模型

每個使用者連接配接都使用一個線程

mysql使用線程池來管理各個線程

mysql内部元件架構

connection

--management service & unities(管理服務單元，如備份恢複，叢集，合并，遷移工具，複制工具)；

    connection pool(認證，線程重用，連接配接限制，記憶體檢查，緩存)；

--SQL接口（DML,DDL,存儲過程，視圖，觸發器）；

    分析器parser（查詢翻譯成二進制指令，通路權限）；

    優化器optimizer(檔案系統的通路路徑，統計資料)；

    緩存cache和緩沖buffer;

--存儲引擎myisam,innodb,...

--檔案系統，檔案和日志（資料，索引，二進制日志，重做日志，撤銷日志，錯誤日志，中繼日志 ）

mysql的某些存儲引擎，可以把熱點資料直接裝入記憶體中，加速資料的通路，周期性同步到磁盤上（innodb可以實作）

mysql内部的工作流程（簡化）

使用者--[連接配接管理器--線程管理器]--使用者子產品--指令派發器--查詢緩存(sql-value)，記錄日志；分析器--[優化器(sql語句)，表修改子產品，表維護子產品，複制子產品，狀态報告子產品]--通路控制子產品--表管理器--存儲引擎接口--myisam,innodb

mysql邏輯架構（簡化版）

用戶端--連接配接/線程處理--查詢緩存，分析器--優化器--存儲引擎--檔案系統

索引管理

索引：按照特定資料結構存儲的索引，特别适合某些算法快速執行。索引建構于記憶體中并排好序，可利用搜尋算法快速定位某項或若幹項，定位後，通過存儲引擎到磁盤上加載索引項指向資料行到記憶體中即可。

沒有索引就隻能表掃描，把整張表資料從磁盤加載到記憶體中，再一行行比對操作，這顯然費時費力。

索引建構原理

抽取表中某個字段做排序後單獨存放為整字段索引中，然後，在此基礎之上建構一個稀疏索引，比如分為四段索引A-H,I-M,O-T,U-Z分别映射到整字段索引中，如果要查找蘋果，則會把O-T的字段加載到記憶體中查找即可。

B+樹索引--多級稀疏格式索引來建構

索引類型：

聚集索引，非聚集索引：資料是否與索引存儲在一起

主鍵索引，輔助索引：主鍵索引可實作一對一查詢，輔助索引通常用于輔助其他查詢操作

稠密索引，稀疏索引：索引是否索引了每個資料項

B+樹索引，HASH索引（一對一查找），R樹索引，全文索引：不同的索引資料結構

單鍵索引，組合索引：索引由一個字段還是多個字段來建構

mysql使用左字首索引：LIKE 'ABC%'

弊端：LIKE '%abc00'就不能使用索引了

覆寫索引：例如在組合索引的場景中，在索引的基礎上就找到需要查詢的結果了，而無需再加載表資料的方式稱作覆寫索引。

如何管理和使用索引

建立索引：建立表時指定，也可以單獨建立

HELP CREATE TABLE

HELP CREATE INDEX;HELP DROP INDEX

索引沒必要修改，隻有建立/删除

在一張大表中不要随意建立/删除索引，非常耗費資源

操作示範

# mysql < hellodb.sql

> USE hellodb

> SHOW TABLES;

> SELECT  * FROM classes;

> HELP SHOW INDEX

> SHOW INDEXES FROM students;

結果顯示在StuID上建立了索引

> SELECT * FROM students WHERE StuID=3;

> EXPLAIN SELECT * FROM students WHERE StuID=3\G

結果顯示，使用了索引，隻查詢了1行即命中結果

> EXPLAIN SELECT * FROM students WHERE Age=9\G

結果顯示，沒有使用索引，需要查詢25行

> HELP ALTER TABLE

> ALTER TABLE students ADD INDEX(Age);

此時修改表，添加索引建立在Age字段上

結果顯示在StuID，Age上建立了索引

結果顯示，使用索引，需要查詢1行

> CREATE INDEX name ON students (Name);

此時，該表有3個索引，都是BTREE索引

> EXPLAIN SELECT * FROM students WHERE Name LIKE 'X%'\G

結果顯示，過濾了6行，使用了索引

> EXPLAIN SELECT * FROM students WHERE Name LIKE '%X'\G

結果顯示，沒有使用索引，全表掃描

視圖view

視圖是虛表，是存儲下來的SELECT語句

建立視圖 HELP CREATE VIEW;

mysql的視圖不完善？

霧化視圖：把select語句的執行結果永久儲存下來

> CREATE VIEW test AS SELECT StuID,Name,Age, FROM students;

可以看到test表

> SHOW TABLES STATUS LIKE 'test'\G;

大部分提示NULL，comment提示VIEW

> SELECT * FROM test;

> SELECT * FROM test WHERE Age=22;

> EXPLAIN SELECT * FROM test WHERE Age=22;

同樣可以使用索引Age

删除視圖 HELP DROP VIEW;

視圖能插入資料嗎？

可以，往視圖中插入資料，其實是插入到基表中，是以能否插入成功取決于基表對字段的限制。

本文轉自 zhuhc1988 51CTO部落格，原文連結：http://blog.51cto.com/changeflyhigh/1886365，如需轉載請自行聯系原作者