mysql底層通訊協(xié)議：
mysql通訊類型： 同步/異步。
同步調用： 基于請求和響應
異步調用： 服務器端單獨開啟一個線程處理比較耗時的代碼；
優(yōu)點： 防止客戶端阻塞
缺點：1 客戶端不能及時獲取響應的結果
2， 單獨開啟個線程處理，有可能會消耗cpu資源，小項目處理用線程，大項目用mq
連接方式：長連接與短鏈接
長連接： http協(xié)議底層基于tcp封裝，tcp 三次握手和四次握手
tcp三次握手確認server端在，保證客戶端傳輸消息給服務器 可靠傳輸
注意： 如果頻繁發(fā)送http請求的時候，每個請求每次都會做tcp三次握手和四次握手 發(fā)送效率極低；
長連接：每次發(fā)送請求后會把連接保存起來實現(xiàn)復用。不會頻繁創(chuàng)建連接，避免tcp三次握手和四次揮手。
優(yōu)點： 避免重復創(chuàng)建tcp三次握手和四次揮手 ；
缺點： 有可能浪費服務器端資源。
短連接：每次發(fā)送完請求后都會把連接關閉；
優(yōu)點： 避免浪費我們服務器的資源
缺點： 每次建立連接的時候需要經歷tcp三次握手，如果在頻繁發(fā)送請求的情況下效率會非常低。
mysql jdbc 長連接。
28800秒
mysql客戶端發(fā)出jdbc請求連接到mysql 服務器的情況下 ，空閑時間8個小時，如果在8個小時以內沒有發(fā)送任何參數(shù)的情況下，認為該連接超時，自動會斷開該連接。
#非交互式超時時間，如 JDBC 程序
show global variables like 'wait_timeout';
#交互式超時時間，如數(shù)據(jù)庫工具
show global variables like 'interactive_timeout';
MySQL服務器默認的“wait_timeout”是28800秒即8小時，意味著如果一個連接的空閑時間超過8個小時，MySQL將自動斷開該連接，這也是大家在剛學習mysql遇到的“MySql連接空閑8小時自動斷開引起的問題”
mysql連接空閑8小時自動斷開引起的問題 JDBC
1.定時發(fā)送jdbc語句 （不合理）
- 增加mysql服務器空閑超時時間（不合理） jdbc
修改mysql安裝目錄下的配置文件 my.ini文件（如果沒有此文件，復制“my-default.ini”文件，生成“復件 my-default.ini”文件。將“復件 my-default.ini”文件重命名成“my.ini” ），在文件中設置：
wait_timeout=31536000
interactive_timeout=31536000
這兩個參數(shù)的默認值是8小時(60608=28800)。 注意： 1.wait_timeout的最大值只允許2147483 （24天左右）
也可以使用mysql命令對這兩個屬性進行修改
3.使用數(shù)據(jù)庫連接池 自帶功能 定時清理空閑超時的jdbc連接
數(shù)據(jù)庫連接池：提前幫創(chuàng)建5個連接 最小連接數(shù)5 最大連接數(shù)10
緩存每個連接請求 定時器檢查jdbc連接在設定空閑超時時間如果還是沒有被使用的情況下，自動回銷毀。
設定空閑超時時間比如6個小時 一定保證有5個連接一直存在
mysql數(shù)據(jù)庫空閑超時時間8個小時
定期使用連接池內的連接，使得它們不會因為閑置超時而被 MySQL 斷開。并且每次使用連接前檢查連接是否可用，定期回收空閑的連接。
mysql狀態(tài)分析之show global status
show global status like 'Thread%';
Threads_cached--- 服務器端緩存連接；
Threads_connected ---當前打開的連接數(shù)
Threads_created ---創(chuàng)建的線程數(shù)
Threads_running---正在運行的線程
數(shù)據(jù)庫連接池和線程池原理基本上一樣：
show PROCESSLIST; 查詢當前mysql服務器接收所有的連接信息
狀態(tài)詳細描述：
sleep：線程正在等待客戶端發(fā)送新的請求；
query：線程正在執(zhí)行查詢或者正在將結果發(fā)送給客戶端；
locked：在mysql服務器層，該線程正在等待表鎖。在存儲引擎級別實現(xiàn)的鎖，例如InnoDB的行鎖，并不會體現(xiàn)在線程狀態(tài)中。對于MyISAM來說這是一個比較典型的狀態(tài)。
analyzing and statistics：線程正在收集存儲引擎的統(tǒng)計信息，并生成查詢的執(zhí)行計劃；
copying to tmp table：線程在執(zhí)行查詢，并且將其結果集復制到一個臨時表中，這種狀態(tài)一般要么是做group by操作，要么是文件排序操作，或者union操作。如果這個狀態(tài)后面還有on disk標記，那表示mysql正在將一個內存臨時表放到磁盤上。
sorting Result：線程正在對結果集進行排序。
sending data：線程可能在多個狀態(tài)間傳送數(shù)據(jù)，或者在生成結果集，或者在想客戶端返回數(shù)據(jù)。
項目中可能會遇到MySQL: ERROR 1040: Too many connections”的異常情況，造成這種情況的一種原因是訪問量過高，MySQL服務器抗不住，這個時候就要考慮增加從服務器分散讀壓力；另一種原因就是MySQL配置文件中max_connections值過小。
首先，首先我們來看下mysql的最大連接數(shù)：
Mysql允許最大的連接數(shù)：
show variables like '%max_connections%';
max_connections 100
1.創(chuàng)建一個容器緩存連接；
2.提前設定創(chuàng)建好一定的連接緩存到該容器中；
3.數(shù)據(jù)結構模型采用鏈表
鏈表：
獲取連接時候：
每次在獲取連接的時候，獲取當前的鏈表的頭結點，并且刪除該頭結點引用關系；
釋放連接的時候：
會將該連接放入到我們鏈表的后面。
鏈表前面：最近少使用連接；
鏈表后面：最近有被使用連接；
package com.taoto.jdbcs;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.*;
import java.util.LinkedList;
import java.util.logging.Logger;
/**
*@author tom
*Date 2020/10/13 0013 9:14
*
*/
public class MKDataSource implements DataSource {
private String driverClassName = "com.mysql.cj.jdbc.Driver";
private String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=true";
private String username = "root";
private String password = "root";
private LinkedList<Connection> connections = new LinkedList<Connection>();
public MKDataSource(int initialSize) throws ClassNotFoundException, SQLException {
Class.forName(driverClassName);//執(zhí)行驅動
for (int i = 0; i < initialSize; i++) {
Connection connection = DriverManager.getConnection(url, username, password);
connections.add(connection);
}
}
//獲取連接
@Override
public Connection getConnection() throws SQLException {
/* Connection first=connections.getFirst();
connections.remove(first);*/
Connection first=connections.removeFirst();
return first;
}
//關閉連接
public void closeConnection(Connection connection){
connections.add(connection);
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
MKDataSource mkDataSource=new MKDataSource(2);
for (int i = 0; i <10 ; i++) {
Connection connection= mkDataSource.getConnection();
PreparedStatement pstmt=connection.prepareStatement("SELECT * from order_info WHERE 1=1");
ResultSet resultSet=pstmt.executeQuery();
System.out.println("發(fā)送結果"+connection);
if(resultSet==null){
return;
}
if(resultSet.next()){
String name=resultSet.getNString("name");
System.out.println("查詢結果："+name);
}
if(resultSet !=null)
resultSet.close();
if(pstmt!=null){
pstmt.close();
}
mkDataSource.closeConnection(connection);
}
}
}
Mysql通訊協(xié)議： Unix Socket/TCIP
Unix 在linux 操作系統(tǒng)中 客戶端和服務器端都在同一臺電腦上 客戶端訪問mysql使用Unix 協(xié)議非網(wǎng)絡協(xié)議。
TCP/IP套接字 客戶端與服務器不在同一臺電腦上 采用網(wǎng)絡方式實現(xiàn)通訊
命名管道和內存共享
在window系統(tǒng)中客戶端和Mysql服務器在同一臺電腦上，可以使用命名管道和共享內存的方式，
命名管道開啟：–shared-memory=on/off；
共享內存開啟：–enable-named-pipe=on/off;
MYSql底層通訊協(xié)議：半雙工機制：
單工： 數(shù)據(jù)單向發(fā)送（遙控器）
半雙工： 數(shù)據(jù)雙向發(fā)送。但是不同時運輸 mysql 采用半雙工模式（對講機）
全雙工：數(shù)據(jù)雙向運輸，可以同時運輸。
·max_allowed_packet 是mysql中一個設定參數(shù)，用于設定所接受的包的大小，根據(jù)情形不同，其缺省值可能是1m或者4m，比如是4m 的情況下，這個值的大小即為：4 10241024= 4194304 max_allowed_packet 最大值是1G(1073741824)，如果設置超過1G，查看最終生效結果也只有1G。
show variables like '%max_allowed_packet%';
mysql緩存機制原理：
在mysql中也自帶對查詢語句實現(xiàn)內部緩存，緩存的key就是為sql語句 value 就是為結果，但是在mysql8.0直接去除了自帶緩存因為作用不是很大，因為它緩存的key sql語句不能夠發(fā)生什么改變，連空格也不允許，字母大小寫必須要統(tǒng)一，當對該表數(shù)據(jù)實現(xiàn)修改的時候，也會清理緩存，所以mysql8.0直接去除了緩存，后期可以使用Redis作為緩存。
query_cache_type=OFF 關閉 ON開啟 no沒有
show variables like '%query_cache%';
mysql自帶緩存機制：
讀取內存數(shù)據(jù)一定比讀取磁盤io數(shù)據(jù)效率要高。
用戶空間切換到內核狀態(tài)過程。
緩存key：查詢語句
緩存value內容： 查詢結果
注意： mysql8.0去除了自帶緩存機制
原因： sql語句多了空格或者大小寫變化 都會清理緩存重新緩存
查詢sql語句執(zhí)行的原理：
sql 查詢底層如何實現(xiàn)的？
1，先查詢mysql 服務器端自帶緩存
2，解析
語法/詞法
詞法：
先將sql語句拆分成n多個不同的單詞，根據(jù)不同的單詞規(guī)則匹配語法，驗證sql語句寫法是否正確 生成解析樹。
3，處理器:
解析表名稱和列名稱是否正確。
4，執(zhí)行 優(yōu)化器，
數(shù)據(jù)庫存儲引擎 當前表的數(shù)據(jù)結構采用什么規(guī)則進行存放。
存儲引擎是和表關聯(lián)。
存放在硬盤
5， 查詢該表對應的存儲引擎
6，查詢數(shù)據(jù)返回給客戶端。
innodb 與 myisam 引擎之間的區(qū)別。
1.CSV存儲引擎 [圖片上傳失敗...(image-8a321c-1602633157278)]
數(shù)據(jù)存儲以CSV文件 特點： 不能定義沒有索引、列定義必須為NOT NULL、不能設置自增列 -->不適用大表或者數(shù)據(jù)的在線處理 CSV數(shù)據(jù)的存儲用,隔開，可直接編輯CSV文件進行數(shù)據(jù)的編排 -->數(shù)據(jù)安全性低 注：用vi或文本編輯器編輯之后，要生效使用flush table XXX 命令 應用場景： 數(shù)據(jù)的快速導出導入 表格直接轉換成CSV
2.Archive存儲引擎 壓縮協(xié)議進行數(shù)據(jù)的存儲，數(shù)據(jù)存儲為ARZ文件格式 特點： 只支持insert和select兩種操作 只允許自增ID列建立索引 行級鎖 不支持事務 數(shù)據(jù)占用磁盤少（較其他存儲引擎小的多，基本1:8-1:9的比例） 應用場景： 日志系統(tǒng) 大量的設備數(shù)據(jù)采集
3.Memory（heap）存儲引擎 因為現(xiàn)在nosql已經非常成熟了，所以一般不會在生產上使用memory存儲引擎。但是他是臨時表默認的存儲引擎。 數(shù)據(jù)都是存儲在內存中，IO效率要比其他引擎高很多 服務重啟數(shù)據(jù)丟失，內存數(shù)據(jù)表默認只有16M 特點： 支持hash索引，B tree索引，默認hash（查找復雜度0(1)） 字段長度都是固定長度varchar(32)=char(32) 不支持大數(shù)據(jù)存儲類型字段如 blog，text（如果超過的話，會選擇myisam引擎） 表級鎖 應用場景： 等值查找熱度較高數(shù)據(jù) 查詢結果內存中的計算，大多數(shù)都是采用這種存儲引擎 作為臨時表存儲需計算的數(shù)據(jù)
4.myisam存儲引擎（MySQL8.0被廢棄掉了） Mysql5.5版本之前的默認存儲引擎 較多的系統(tǒng)表也還是使用這個存儲引擎 系統(tǒng)臨時表也會用到Myisam存儲引擎 特點： a，select count(*) from table 無需進行數(shù)據(jù)的掃描，他有一個專門計算數(shù)據(jù)的函數(shù)，InnoDB需要一行行的掃描，計算出來。 b，數(shù)據(jù)（MYD）和索引（MYI）分開存儲 c，表級鎖 d，不支持事務
myisam實現(xiàn)B+樹的體現(xiàn) [圖片上傳失敗...(image-78efc0-1602633157278)]
數(shù)據(jù)和索引分別存儲，不管用哪個存儲引擎，都會生成一個.frm文件（表定義文件），數(shù)據(jù)保存在myd文件，索引保存在myi文件里面。在myisam里面，葉子節(jié)點的數(shù)據(jù)區(qū)保存的是.myd的內存地址，在.myi通過索引找到這條數(shù)據(jù)的內存地址，再通過這個地址去.myd里面找到對應的數(shù)據(jù)。
5.InnoDB Mysql5.5及以后版本的默認存儲引擎特點： a.事務ACID b.行級鎖 c.聚集索引（主鍵索引）方式進行數(shù)據(jù)存儲 d.支持外鍵關系保證數(shù)據(jù)完整性（不常用）
InnoDB在MySQL中的體現(xiàn) [圖片上傳失敗...(image-96f5b0-1602633157268)]1.CSV存儲引擎 [圖片上傳失敗...(image-8c72ce-1602633163048)]
數(shù)據(jù)存儲以CSV文件 特點： 不能定義沒有索引、列定義必須為NOT NULL、不能設置自增列 -->不適用大表或者數(shù)據(jù)的在線處理 CSV數(shù)據(jù)的存儲用,隔開，可直接編輯CSV文件進行數(shù)據(jù)的編排 -->數(shù)據(jù)安全性低 注：用vi或文本編輯器編輯之后，要生效使用flush table XXX 命令 應用場景： 數(shù)據(jù)的快速導出導入 表格直接轉換成CSV
2.Archive存儲引擎 壓縮協(xié)議進行數(shù)據(jù)的存儲，數(shù)據(jù)存儲為ARZ文件格式 特點： 只支持insert和select兩種操作 只允許自增ID列建立索引 行級鎖 不支持事務 數(shù)據(jù)占用磁盤少（較其他存儲引擎小的多，基本1:8-1:9的比例） 應用場景： 日志系統(tǒng) 大量的設備數(shù)據(jù)采集
3.Memory（heap）存儲引擎 因為現(xiàn)在nosql已經非常成熟了，所以一般不會在生產上使用memory存儲引擎。但是他是臨時表默認的存儲引擎。 數(shù)據(jù)都是存儲在內存中，IO效率要比其他引擎高很多 服務重啟數(shù)據(jù)丟失，內存數(shù)據(jù)表默認只有16M 特點： 支持hash索引，B tree索引，默認hash（查找復雜度0(1)） 字段長度都是固定長度varchar(32)=char(32) 不支持大數(shù)據(jù)存儲類型字段如 blog，text（如果超過的話，會選擇myisam引擎） 表級鎖 應用場景： 等值查找熱度較高數(shù)據(jù) 查詢結果內存中的計算，大多數(shù)都是采用這種存儲引擎 作為臨時表存儲需計算的數(shù)據(jù)
4.myisam存儲引擎（MySQL8.0被廢棄掉了） Mysql5.5版本之前的默認存儲引擎 較多的系統(tǒng)表也還是使用這個存儲引擎 系統(tǒng)臨時表也會用到Myisam存儲引擎 特點： a，select count(*) from table 無需進行數(shù)據(jù)的掃描，他有一個專門計算數(shù)據(jù)的函數(shù)，InnoDB需要一行行的掃描，計算出來。 b，數(shù)據(jù)（MYD）和索引（MYI）分開存儲 c，表級鎖 d，不支持事務
myisam實現(xiàn)B+樹的體現(xiàn) [圖片上傳失敗...(image-86cd66-1602633163047)]
數(shù)據(jù)和索引分別存儲，不管用哪個存儲引擎，都會生成一個.frm文件（表定義文件），數(shù)據(jù)保存在myd文件，索引保存在myi文件里面。在myisam里面，葉子節(jié)點的數(shù)據(jù)區(qū)保存的是.myd的內存地址，在.myi通過索引找到這條數(shù)據(jù)的內存地址，再通過這個地址去.myd里面找到對應的數(shù)據(jù)。
5.InnoDB Mysql5.5及以后版本的默認存儲引擎特點： a.事務ACID b.行級鎖 c.聚集索引（主鍵索引）方式進行數(shù)據(jù)存儲 d.支持外鍵關系保證數(shù)據(jù)完整性（不常用）
InnoDB在MySQL中的體現(xiàn) [圖片上傳失敗...(image-5ce4af-1602633163046)] 只有.ibd和.frm兩個文件，他的index和數(shù)據(jù)放在了一起，在InnoDB中，以主鍵為索引來組織數(shù)據(jù)的存儲，如果沒有明確指定一個主鍵（ID）索引，他會默認的生成一個隱藏的6byte的Int型的索引來作為他的主鍵索引，只是這個隱式的索引看不到而已。 注意：MySQL每個版本都是略微差異，比如在MySQL5.7之前都有.frm文件，而在mysql8.0之后就將該文件移除掉了。
只有.ibd和.frm兩個文件，他的index和數(shù)據(jù)放在了一起，在InnoDB中，以主鍵為索引來組織數(shù)據(jù)的存儲，如果沒有明確指定一個主鍵（ID）索引，他會默認的生成一個隱藏的6byte的Int型的索引來作為他的主鍵索引，只是這個隱式的索引看不到而已。 注意：MySQL每個版本都是略微差異，比如在MySQL5.7之前都有.frm文件，而在mysql8.0之后就將該文件移除掉了。
讀取io 操作觸發(fā) 的問題？
用戶狀態(tài)到內核狀態(tài)實現(xiàn)切換。
InnnoDb 存儲引擎中 BufferPool緩沖池 主要緩存硬盤數(shù)據(jù)減少io的操作。
概念：讀取io的數(shù)據(jù)以頁(page)訪問
一頁中默認占16kb 多行數(shù)據(jù)
mysql 底層存儲的數(shù)據(jù)以頁為單位
以頁訪問數(shù)據(jù)的情況下好處： 如果訪問熱點數(shù)據(jù)，‘局部性原則’
訪問page頁以什么數(shù)據(jù)庫存放？ 以鏈表方式存放
鏈表結構存放的好處是：
鏈表增刪效率比較高 緩存淘汰算法。
緩存的缺點：
數(shù)據(jù)一致性的問題：
緩存池將數(shù)據(jù)寫入到硬盤中的時候，為了防止數(shù)據(jù)丟失，會先將日志寫入到redoLog中，在將數(shù)據(jù)寫入到硬盤中
下次當mysql啟動的時候，讀取redolog數(shù)據(jù)等待恢復。
mysqlInnodb 底層 寫入 redolog 采用順序io
mysqlInnodb 底層 將數(shù)據(jù)寫入到硬盤中采用隨機io。
順序IO與隨機IO的區(qū)別
對于從磁盤中讀取數(shù)據(jù)的操作，叫做IO操作，這里有兩種情況：
第一種隨機IO：假設我們所需要的數(shù)據(jù)是隨機分散在磁盤的不同頁的不同扇區(qū)中的，那么找到相應的數(shù)據(jù)需要等到磁臂（尋址作用）旋轉到指定的頁，然后盤片尋找到對應的扇區(qū)，才能找到我們所需要的一塊數(shù)據(jù)，一次進行此過程直到找完所有數(shù)據(jù)，這個就是隨機IO，讀取數(shù)據(jù)速度較慢。
第二種順序io：假設我們已經找到了第一塊數(shù)據(jù)，并且其他所需的數(shù)據(jù)就在這一塊數(shù)據(jù)后邊，那么就不需要重新尋址，可以依次拿到我們所需的數(shù)據(jù)，這個就叫順序IO。
順序ｉｏ效率比隨機ＩＯ效率要高，
為什么記錄 redo log的日志 采用順序io？而我們的寫入磁盤的數(shù)據(jù)采用隨機io
因為記錄日志不需要建立索引目錄結構，直接追加日志文件后面即可，而我們寫入磁盤物理存儲的數(shù)據(jù)，需要建立索引的文件
索引文件排列數(shù)據(jù)都不在同一個扇區(qū)中，所以寫入磁盤的數(shù)據(jù)采用隨機io。
為什么需要設計緩存池
在innodb中我們的數(shù)據(jù)是存放在硬盤中，為了能夠減少磁盤io操作，在innodb中引入緩存池技術，會將從磁盤中讀取的數(shù)據(jù)（page頁）放入到緩沖池中。如果客戶端查詢數(shù)據(jù)（page頁） 如果在緩存池的存在的情況下，可以不需要查詢磁盤io操作，從而提高效率。
undolog/redlog/Binlog之間的區(qū)別？
1 redlog 主要用于數(shù)據(jù)庫宕機數(shù)據(jù)恢復；
2.undolog 事務回滾的日志
3， Binlog 底層mysql 實現(xiàn) 對sql 語句實現(xiàn)增量日志 ，主從復制，集群
redlog undolog 是Innodb 實現(xiàn)的。
臟頁： 緩沖區(qū)的頁數(shù)據(jù)與硬盤中數(shù)據(jù)不一致 redlog 日志恢復：
1，讀取當前磁盤io的數(shù)據(jù)，放入到緩沖區(qū)中。
2， 將原來的數(shù)據(jù)記錄到undo_do日志中；
3，修改緩沖池中的數(shù)據(jù)
4， 將數(shù)據(jù)記錄到redo_log 日志中。（順序io）
5，記錄binlog日志文件
6， 提交事務后，后臺會開啟n多個io線程將緩沖區(qū)的數(shù)據(jù)，刷新到硬盤（隨機io）
