mysql深入學習02

1.各存儲引擎特點

1.1存儲引擎介紹

插拔式的插件方式

存儲引擎是指定在表上面的

不管使用什么存儲引擎莫杈，都會在數據區(qū)產生一個frm文件（表結構定義描述文件）

1.2存儲引擎特點總結

1.2.1MyISAM與InnoDB的索引差異

csv存儲引擎

特點：
不能定義沒有索引梧疲、列定義必須為NOT NULL、不能設置自增列
不適用大表或者數據的在線處理
CSV數據的存儲用,隔開碴卧，可直接編輯CSV文件進行數據的編排
數據安全性低
注：編輯之后，要生效使用flush table XXX 命令
應用場景：
數據的快速導出導入
表格直接轉換成CSV
archive存儲引擎

壓縮協議進行數據的存儲數據存儲為ARZ文件格式
特點：
只支持insert和select兩種操作
只允許自增ID列建立索引
行級鎖
不支持事務
數據占用磁盤少
應用場景：
日志系統
大量的設備數據采集
myisam:

Mysql5.5版本之前的默認存儲引擎漾唉，較多的系統表也還是使用這個存儲引擎炬称，系統臨時表也會用到Myisam存儲引擎
特點：

a伞广，select count(*) from table 無需進行數據的掃描
b，數據（MYD）和索引（MYI）分開存儲
c疼电，表級鎖
d嚼锄，不支持事務

Mysql5.5及以后版本的默認存儲引擎
優(yōu)點：

Its DML operations follow the ACID model [事務ACID]
Row-level locking[行級鎖]
InnoDB tables arrange your data on disk to optimize queries based on primary keys[聚集索引（主鍵索引）方式進行數據存儲]
To maintain data integrity, InnoDB supports FOREIGN KEY constraints[支持外鍵關系保證數據完整性]

2.mysql 體系結構

image.png

Client Connectors
接入方支持協議很多
Management Serveices & Utilities
系統管理和控制工具，mysqldump蔽豺、 mysql復制集群区丑、分區(qū)管理等
Connection Pool
連接池：管理緩沖用戶連接、用戶名修陡、密碼沧侥、權限校驗、線程處理等需要緩存的需求
SQL Interface
SQL接口：接受用戶的SQL命令魄鸦，并且返回用戶需要查詢的結果
Parser
解析器宴杀，SQL命令傳遞到解析器的時候會被解析器驗證和解析。解析器是由Lex和YACC實現的
Optimizer
查詢優(yōu)化器拾因，SQL語句在查詢之前會使用查詢優(yōu)化器對查詢進行優(yōu)化
Cache和Buffer（高速緩存區(qū)）
查詢緩存旺罢，如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據
pluggable storage Engines
插件式存儲引擎绢记。存儲引擎是MySql中具體的與文件打交道的子系統
file system
文件系統扁达，數據、日志（redo蠢熄，undo）跪解、索引、錯誤日志护赊、查詢記錄惠遏、慢查詢等

3.查詢路徑

image.png

mysql 客戶端/服務端通信

Mysql客戶端與服務端的通信方式是“半雙工”；
客戶端一旦開始發(fā)送消息骏啰，另一端要接收完整個消息才能響應节吮。

客戶端一旦開始接收數據沒法停下來發(fā)送指令。

查詢狀態(tài)：對于一個mysql連接判耕，或者說一個線程透绩，時刻都有一個狀態(tài)來標識這個連接正在做什么
查看命令 show full processlist / show processlist
https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html (狀態(tài)全集)

狀態(tài)代碼	狀態(tài)含義
Sleep	線程正在等待客戶端發(fā)送數據
Query	連接線程正在執(zhí)行查詢
Locked	線程正在等待表鎖的釋放
Sorting result	線程正在對結果進行排序
Sending data	向請求端返回數據

?可通過kill {id}的方式進行連接的殺掉

查詢緩存

工作原理
緩存SELECT操作的結果集和SQL語句；
新的SELECT語句壁熄，先去查詢緩存帚豪，判斷是否存在可用的記錄集；
判斷標準
與緩存的SQL語句草丧，是否完全一樣狸臣，區(qū)分大小寫 (簡單認為存儲了一個key-value結構，key為sql昌执，value為sql查詢結果集)

show status like 'Qcache%' ;  //命令可查看緩存情況

query_cache_type
值：0 -– 不啟用查詢緩存烛亦，默認值诈泼；
值：1 -– 啟用查詢緩存，只要符合查詢緩存的要求，客戶端的查詢語句和記錄集
都可以緩存起來，供其他客戶端使用凤类，加上 SQL_NO_CACHE將不緩存
值：2 -– 啟用查詢緩存，只要查詢語句中添加了參數：SQL_CACHE瓮孙，且符合查詢
緩存的要求，客戶端的查詢語句和記錄集选脊，則可以緩存起來杭抠，供其他客戶端使用
query_cache_size
允許設置query_cache_size的值最小為40K，默認1M知牌，推薦設置為：64M/128M祈争；
query_cache_limit
限制查詢緩存區(qū)最大能緩存的查詢記錄集斤程，默認設置為1M

不會緩存的情況
?1.當查詢語句中有一些不確定的數據時角寸，則不會被緩存。如包含函數NOW()忿墅，
CURRENT_DATE()等類似的函數扁藕，或者用戶自定義的函數，存儲函數疚脐，用戶變
量等都不會被緩存
?2.當查詢的結果大于query_cache_limit設置的值時亿柑，結果不會被緩存
?3.對于InnoDB引擎來說，當一個語句在事務中修改了某個表棍弄，那么在這個事務提交之前望薄，所有與這個表相關的查詢都無法被緩存。因此長時間執(zhí)行事務呼畸，會大大降低緩存命中率
?4.查詢的表是系統表
?5.查詢語句不涉及到表

mysql默認關閉查詢緩存

緩存的弊端：
1.在查詢之前必須先檢查是否命中緩存,浪費計算資源
2.如果這個查詢可以被緩存痕支，那么執(zhí)行完成后，MySQL發(fā)現查詢緩存中沒有這
個查詢蛮原，則會將結果存入查詢緩存卧须，這會帶來額外的系統消耗
3.針對表進行寫入或更新數據時，將對應表的所有緩存都設置失效儒陨。
4.如果查詢緩存很大或者碎片很多時花嘶，這個操作可能帶來很大的系統消耗

使用場景:
以讀為主的業(yè)務，數據生成之后就不常改變的業(yè)務
比如門戶類蹦漠、新聞類椭员、報表類、論壇類等

查詢優(yōu)化處理

優(yōu)化的3個階段

解析sql
?通過lex詞法分析,yacc語法分析將sql語句解析成解析樹

預處理階段
?根據mysql的語法的規(guī)則進一步檢查解析樹的合法性笛园，如：檢查數據的表
和列是否存在隘击，解析名字和別名的設置容劳。還會進行權限的驗證

查詢優(yōu)化器
?優(yōu)化器的主要作用就是找到最優(yōu)的執(zhí)行計劃

查詢優(yōu)化器的優(yōu)化方法

使用等價變化規(guī)則
5 = 5 and a > 5 改寫成 a > 5
a < b and a = 5 改寫成 b > 5 and a = 5
基于聯合索引，調整條件位置等

優(yōu)化count 闸度、min竭贩、max等函數
min函數只需找索引最左邊
max函數只需找索引最右邊
myisam引擎count(*)

覆蓋索引掃描

子查詢優(yōu)化

提前終止查詢
用了limit關鍵字或者使用不存在的條件

IN的優(yōu)化
先進性排序，再采用二分查找的方式

Mysql的查詢優(yōu)化器是基于成本計算的原則莺禁。他會嘗試各種執(zhí)行計劃留量。數據抽樣的方式進行試驗（隨機的讀取一個4K的數據塊進行分析）

執(zhí)行優(yōu)化
explain select * from user

image.png

id
select查詢的序列號，標識執(zhí)行的順序
1哟冬、id相同楼熄，執(zhí)行順序由上至下
2、id不同浩峡，如果是子查詢可岂，id的序號會遞增，id值越大優(yōu)先級越高翰灾，越先被執(zhí)行
3缕粹、id相同又不同即兩種情況同時存在，id如果相同纸淮，可以認為是一組平斩，從上往下順序
執(zhí)行；在所有組中咽块，id值越大绘面，優(yōu)先級越高，越先執(zhí)行

select_type
查詢的類型侈沪，主要是用于區(qū)分普通查詢揭璃、聯合查詢、子查詢等
SIMPLE：簡單的select查詢亭罪，查詢中不包含子查詢或者union
PRIMARY：查詢中包含子部分瘦馍，最外層查詢則被標記為primary
SUBQUERY/MATERIALIZED：SUBQUERY表示在select 或 where列表中包含了子查詢
MATERIALIZED表示where 后面in條件的子查詢
UNION：若第二個select出現在union之后，則被標記為union皆撩；
UNION RESULT：從union表獲取結果的select

table
查詢涉及到的表
直接顯示表名或者表的別名
<unionM,N> 由ID為M,N 查詢union產生的結果
<subqueryN> 由ID為N查詢生產的結

type
訪問類型扣墩，sql查詢優(yōu)化中一個很重要的指標，結果值從好到壞依次是：
system > const > eq_ref > ref > range > index > ALL
system：表只有一行記錄（等于系統表）扛吞，const類型的特例呻惕，基本不會出現，可以忽略不計
const：表示通過索引一次就找到了滥比，const用于比較primary key 或者 unique索引
eq_ref：唯一索引掃描亚脆，對于每個索引鍵，表中只有一條記錄與之匹配盲泛。常見于主鍵或唯一索引掃描
ref：非唯一性索引掃描濒持，返回匹配某個單獨值的所有行键耕，本質是也是一種索引訪問
range：只檢索給定范圍的行，使用一個索引來選擇行
index：Full Index Scan柑营，索引全表掃描屈雄，把索引從頭到尾掃一遍
ALL：Full Table Scan，遍歷全表以找到匹配的行

possible_keys
查詢過程中有可能用到的索引
key
實際使用的索引官套，如果為NULL酒奶，則沒有使用索引 rows
根據表統計信息或者索引選用情況，大致估算出找到所需的記錄所需要讀取的行
數
filtered
它指返回結果的行占需要讀到的行(rows列的值)的百分比
表示返回結果的行數占需讀取行數的百分比奶赔，filtered的值越大越好

extra---十分重要的額外信息
1惋嚎、Using filesort ：
mysql對數據使用一個外部的文件內容進行了排序，而不是按照表內的索引進行排序讀取
2站刑、Using temporary：
使用臨時表保存中間結果另伍，也就是說mysql在對查詢結果排序時使用了臨時表，常見于order by 或 group by
3绞旅、Using index：
表示相應的select操作中使用了覆蓋索引（Covering Index）摆尝，避免了訪問表的數據行，效率高
4玻靡、Using where ：使用了where過濾條件
5结榄、select tables optimized away：
基于索引優(yōu)化MIN/MAX操作或者MyISAM存儲引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段在進行計算囤捻，查詢執(zhí)行計劃生成的階段即可完成優(yōu)化

查詢執(zhí)行引擎
調用插件式的存儲引擎的原子API的功能進行執(zhí)行計劃的執(zhí)行
返回客戶端

1、有需要做緩存的邻寿，執(zhí)行緩存操作
2蝎土、增量的返回結果：
開始生成第一條結果時,mysql就開始往請求方逐步返回數據
好處： mysql服務器無須保存過多的數據，浪費內存
???用戶體驗好绣否，馬上就拿到了數據

定位慢查詢sql

業(yè)務驅動--業(yè)務人員
測試驅動--測試人員誊涯，比如壓力測試
慢查詢日志--開啟數據庫慢查詢日志

慢查詢日志配置

show variables like 'slow_query_log'
set global slow_query_log = on
set global slow_query_log_file = '/var/lib/mysql/gupaoedu-slow.log'
set global log_queries_not_using_indexes = on set global long_query_time = 0.1 (秒)

慢查詢分析工具
mysqldumpslow -t 10 -s at /var/lib/mysql/慢查詢日志.log
其他工具：mysqlsla、pt-query-digest