mysql體系結構
一.MySQL分層架構
1.鏈接層
處理網絡的鏈接，鏈接的網絡認證。
- 查看鏈接權限
-
mysql> select user,host from mysql.user\G;
-
ERROR 2006 (HY000): MySQL server has gone away
-
No connection. Trying to reconnect...
-
Connection id: 3
-
Current database: *** NONE ***
-
-
*************************** 1. row ***************************
-
user: mysql.session
-
host: localhost
-
*************************** 2. row ***************************
-
user: mysql.sys
-
host: localhost
-
*************************** 3. row ***************************
-
user: root
-
host: localhost
-
3 rows in set (0.00 sec)
-
-
ERROR:
-
No query specified
-
-
mysql> grant all on *.* to root@'%' identified by 'root';
-
Query OK, 0 rows affected, 1 warning (0.00 sec)
-
-
mysql> flush privilege;
- 查看連接
-
mysql> show processlist;
-
+----+------+-----------+------+---------+------+----------+------------------+
-
| Id | User | Host | db | Command | Time | State | Info |
-
+----+------+-----------+------+---------+------+----------+------------------+
-
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
-
+----+------+-----------+------+---------+------+----------+------------------+
- kill 會話
kill 3;
kill query 4; 殺掉會話正在執行的SQL，而不關閉會話。
- 連接池
(1).如果在程序中，頻繁的創建和銷毀mysql和客戶端連接，開銷很嚴重。
(2).為了減少相關的連接創建的開銷，在應用層部署一個連接池。
(3).會保持一定的連接在連接池中，如果應用想請求連接的話，不再需要向mysql請求，而是直接向連接池請求鏈接，減少了應用和mysqld頻繁創建鏈接的開銷。
- 線程池
(1).每個連接過來，就分配一個線程對該鏈接提供服務，鏈接撤銷后就撤銷該線程，在連接的頻繁創建與銷毀過程中，會需要消耗一定的系統資源。
(2).為了避免這個情況的發生線程池內部的線程是可以共用的，如果連接銷毀后，線程是不會被銷毀的，可以繼續為下一個連接提供服務。
(3).社區版沒有線程池的特性
2. SQL層
SQL的查詢解析，分析，優化,緩存以及所有的內置函數，所有存儲引擎的功能都在這一層實現，比如存儲過程。
- 查詢緩存
query_cache_size 1048576
query_cache_type OFF
(1).如果mysql之前執行的sql，直接從查詢緩存返回結果
(2).局限性比較大，任何查詢結果有變更，都需要進行更新，還持有全局鎖，鎖力度很重，效率很低。
使用場景: 如果查詢的SQL語句和結果集比較固定，可以考慮開啟查詢緩存。
- 修改系統參數
show variables like 'log%';
(1).只讀參數:關閉數據庫后進行修改，重啟數據庫后生效，不能在線修改
mysql> set global query_cache_type=on;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
(2).在線參數:可以直接開庫修改，不用重啟數據庫即可生效
(3).全局參數:修改后，所有新會話，會生效
show global variable;
SET GLOBAL var_name = value;
(4).會話參數:只在當前會話生效
show session variable;
SET SESSION var_name = value;
- SQL執行過程
(1).SQL解析(在數據庫內部將sql文本轉換為sql解析樹)
(2).SQL優化改寫
(3).SQL的執行計劃確立
(4).SQL執行
(5).binlog
3.存儲引擎層
MySQL的存儲引擎在存儲引擎層，負責存儲數據。正是因為分層的存在，導致MySQL本身有一些局限性
mysql5.5版本之前默認的存儲引擎是MyISAM
查看存儲引擎
show plugins;
建表的時候要選擇存儲引擎
create table t1 (id int) ENGINE=myisam;
show create table t1\G;
- Innodb MyISAM區別
- innodb表結構設計
一對一
一對多
多對多
不推薦使用外鍵，因為大批量插入數據時，每次插入都要檢查外鍵約束，造成性能大量消耗。 雖然不推薦，但是依賴關系依然存在。
- innodb和myisam讀寫性能測試
