1、基本的框架
客戶端- > 連接器 - > 分析器 -> 優化器 - >執行器 - > 存儲引擎
- > 查詢緩存 - >
這裏還有壹個緩存的位置,是在連接器處,如果緩存中存在要查詢的結果則直接走緩存返回
但在現實中開啟緩存的幾率比較低
原因1、對於壹個表的更新操作,這個表上的所有查詢緩存都會被清空
因此除了很少更新的配置表外可以使用查詢緩存來提高查詢速度,壹般不建議開啟查詢緩存
壹般也不建議開啟
分析器:分析語法及詞法,保證sql的正確性
優化器:壹條sql可以通過不同的方式獲取數據,優化器需要找到最優的查詢方式
查找的依賴:統計信息和代碼模型
例: select * from A where a = 3 and b = 4 ;
如果表中a都是3, b 只有壹條為4, 優化器會選擇b的索引進行查詢,因為a的區分度不高,且還需要
進行回表操作,導致代價更高
例: select * from S where ( a between 1 and 1000) and (b between 5000 and 10000) order by b limit 1 ;
mysql 會選擇哪個索引?
使用a索引需要最多掃描1000行數據,然後在進行排序
使用b索引需要最多掃描50000行數據,不需要進行排序
mysql5.7之前優化器最終會選擇b索引,因為受order by的影響
在5.7之後會選擇a索引
優化器確實會存在壹些bug,導致選擇的最終索引錯誤,這些內容需要進行具體sql具體分析
原則:盡量使用索引的排序,因為非索引的排序都屬於filesort, 壹提到文件排序其實就會比較耗時
執行器:
拿到優化器的信息,去調用搜索引擎的api接口,先取b=4的數據,判斷a是否=3,如果不等於跳過,否則放入結果集
調用接口再取b=4的下壹條數據並返回執行器,重復,直到循環遍歷結束
執行器講結果集返回給客戶端
註: mysql將結果返回客戶端是壹個增量、逐步返回的過程,不壹定等所有結果查到才返回
好處:
1、服務器無需查詢太多的結果,也不會因為返回太多的結果而消耗太多的內存
2、客戶端也可以第壹時間返回結果
慢sql的具體原因
磁盤io : 磁盤的訪問成本大概是內存的十萬倍左右,為了降低磁盤io
在每次io時,不光把磁盤地址的數據,而且把周邊的數據也讀到內存緩存區
每壹次讀取就是壹page, 具體大小在8k或者16k ,所以在讀取壹頁數據的時候才會發生io
在查找數據的時候,B+樹每壹層進行壹次IO,所以B+樹的高度決定了io的次數
索引有最左匹配的特性
哪些情況要建索引
1、主鍵自動建主鍵索引
2、頻繁作為查詢條件的字段應該創建索引
3、查詢中與其他表關聯的字段,外鍵關系建立索引
4、在高並發下傾向建立組合索引
5、查詢中的排序字段,排序字段若通過索引去訪問將大大提高排序速度
6、查詢中統計或者分組的數據
哪些情況不適合建索引
1、頻繁更新的字段
2、where條件用不到的字段不創建索引
3、表記錄太少
4、經常增刪改的表
5、數據重復太多的字段,為它建索引意義不大
進行explain
type:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
3.1 索引失效_復合索引(避免)
1、應該盡量全值匹配
2、復合最佳左前綴法則(第壹個索引不能掉,中間不能斷開)
3、不在索引列上做任何操作(計算、函數、類型轉換)會導致索引失效而轉向全表掃描
4、儲存引擎不能使用索引中範圍條件右邊的列
5、盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列壹致)),減少select*
6、mysql在使用不等於(!=或者<>)的時候無法使用索引會導致全表掃描
7、is null,is not null也會無法使用索引
8、like以統配符開頭
9、字符串不加單引號
10、少用or
有些sql 中會包含force index的寫法,強制去走某個索引,但條件中缺不存在這個字段,會導致全表掃描
SELECT * FROM `coupon` FORCE INDEX (`orderid`) WHERE `userid` = 1 AND `status` IN (0,1) ORDER BY `id` ASC ;
數據庫的主從同步
mysql主從復制需要三個線程:master(binlog dump thread)、slave(I/O thread 、SQL thread)
binlog dump線程:主庫中有數據更新時,根據設置的binlog格式,將更新的事件類型寫入到主庫的binlog文件中,並創建log dump線程通知slave有數據更新。當I/O線程請求日誌內容時,將此時的binlog名稱和當前更新的位置同時傳給slave的I/O線程。
I/O線程:該線程會連接到master,向log dump線程請求壹份指定binlog文件位置的副本,並將請求回來的binlog存到本地的relay log中。
SQL線程:該線程檢測到relay log有更新後,會讀取並在本地做redo操作,將發生在主庫的事件在本地重新執行壹遍,來保證主從數據同步
puma , databus :
主從延遲問題
主庫 A 執行完成壹個事務,寫入 binlog,該時刻記為T1.
傳給從庫B,從庫接受完這個binlog的時刻記為T2.
從庫B執行完這個事務,該時刻記為T3.
是T2-T1 嗎? 不是,如果網絡不延遲,T2-T1 是壹個很短的時間
是T3-T2嗎? 是的,主要是從庫執行的情況(relaylog)
具體原因:
1、從庫的機器性能比主庫差
2、從庫的壓力大
3、大事務的執行,如果是大事務,主庫必須等事務完成之後才寫入binlog,數據傳輸人到從庫,執行容易產生延遲
盡量避免壹次性的delete大量數據,盡量批次處理
4、主庫的ddl, alter, drop, repair, create
1、只讀節點與主庫的DDL同步是串行進行,如果DDL操作在主庫執行時間很長,那麽從庫也會消耗同樣的時間,比如在主庫對壹張500W的表添加壹個字段耗費了10分鐘,那麽只讀節點上也會耗費10分鐘。
2、只讀節點上有壹個執行時間非常長的的查詢正在執行,那麽這個查詢會堵塞來自主庫的DDL,讀節點表被鎖,直到查詢結束為止,進而導致了只讀節點的數據延遲。
5、鎖沖突
如何避免主從延遲
降低多線程大事務並發的概率,優化業務邏輯
優化SQL,避免慢SQL,減少批量操作,建議寫腳本以update-sleep這樣的形式完成。
提高從庫機器的配置,減少主庫寫binlog和從庫讀binlog的效率差。
盡量采用短的鏈路,也就是主庫和從庫服務器的距離盡量要短,提升端口帶寬,減少binlog傳輸的網絡延時。
實時性要求的業務讀強制走主庫,從庫只做災備,備份。
mysql索引原理、主從延遲問題及如何避免
標簽:框架線程tab使用位置應該事件redupd