MySQL技術進階篇-B站尚矽谷

B站尚矽谷MySQL技術進階篇

之前看了陽哥的Redis視訊,感覺陽哥講的挺好的

文章記錄在gitee上

https://gitee.com/Freegxx/note/blob/master/MySQL/MySQL%E5%AD%A6%E4%B9%A0.md

我發現使用Git加Typora是真的爽,就是Typora文章有改動時Git拉取資料,本地檔案會丢失還找不回來,丢失了好幾篇文章了,氣到炸,可能是我Git用的菜吧

學習位址

https://www.bilibili.com/video/BV1EC4y1s7a7

1.MySQL架構介紹

MYsql安裝

檢視mysql是否已經安裝

rpm -qa|grep mysql

ps -ef |grep mysql

mysqladmin -version

mysql安裝完成後會有密碼與使用者組記錄

cat /etc/passwd|grep mysql

cat /etc/group|grep mysql

Mysql啟動與暫停,開機啟動

service mysql start

systemctl start mysql

service mysql stop//-- /etc/init.d/mysql 啟停相關腳本

systemctl enable mysql

修改Mysql密碼

/usr/bin/mysqladmin -u root password 123456 --改為了123456

修改mysql編碼格式

檢視mysql編碼格式

show variables like ‘%char%’

Mysql配置檔案

二進制日志log-bin

錯誤日志log-error

查詢日志–預設關閉的–慢查詢日志等

資料檔案:

• frm檔案–存放表結構
• myd檔案–存放表資料
• myi檔案–存放表索引

Mysql邏輯架構介紹

總體概覽

1. 連接配接層:用戶端與服務連接配接,包含本地socket通信和大多數基于用戶端服務端工具實作于tcp/ip的通信,主要完成類似于連接配接處理,授權認證以及線管的安全方案,在改成引入了線程池的概念
2. 服務層:主要完成核心服務功能,如SQL接口,完成緩存的查詢,SQL分析優化以及部分内置函數
3. 引擎層:真正負責資料的存儲和提取,不同存儲引擎的功能不同
4. 存儲層:主要是将資料存儲在裸裝置的檔案系統之上,完成與存儲引擎的互動

查詢說明

MYSQL存儲引擎

檢視所有引擎指令

show engines

檢視目前引擎指令

show variables like ‘%engines%’

MyISAM與InnoDB

對比項	MyISAM	InnoDB
主外鍵	不支援	支援
事務	不支援	支援
行表鎖	支援标所	行鎖,操作時隻鎖某一行,适合高并發
緩存	僅緩存索引,不緩存真實資料	緩存索引與真實資料,對記憶體要求高
表空間	小	大
關注點	性能	事務
預設安裝	Y	Y

2.MySQL索引分析

性能下降SQL慢,執行時間長,等待時間長

• 查詢語句寫的爛
• 索引失效
• 關聯查詢太多join
• 伺服器調優以及各個參數設定(緩沖,線程數)

create index idx_user_name on user(name)

索引名:idx_user_name

表名:user

列名:name

常見的Join查詢

SQL執行順序

SQL JOINS

• 

• 

• 

• 

• 

• 

上面的幾張圖中,MySQL不支援Full 文法

union合并加去重,union all合并不去重(這裡總是記反)

索引簡介

索引的定義

Mysql官方對索引的定義是:索引是幫助MySQL高效擷取資料的資料結構

是以索引的本質是:資料結構

排好序的快速查找資料結構

為什麼要建立索引?

索引的目的在于提高查詢效率,類似于字典查詢

在資料之外,資料庫系統還維護着滿足特定查找算法的資料結構,這些資料結構一模中方式引用指向資料,這樣就可以在這些資料結構上實作進階查找算法,這種資料結構,就是索引–>B樹索引

平常說的索引,沒有特别指明,都是B樹(多路搜尋樹,并不一定是二叉樹)

其中聚集索引,次要索引,覆寫索引,複合索引,字首索引,唯一索引預設都是B+樹索引,統稱索引,當然除了B+樹索引,還有哈希索引

索引的優勢

類似圖書館建立書目 索引,提高檢索效率,降低資料庫IO成本

通過索引對資料庫進行排序,降低排序成本,降低CPU的消耗

MySQL索引分類

• 單值索引
• 唯一索引
• 複合索引

MySQL索引結構

主要了解BTree

哪些情況需要建立索引

主鍵自動建立唯一索引

頻繁作為條件的字段應該作為索引

頻繁與其他表關聯的字段，外鍵建立索引

哪些情況不要建立索引

表記錄比較少，與經常增删該的表不要建立索引

頻繁更新的字段不适合建立索引

where後面用不到的字段不用建立索引

列重複資料比較多的字段不用建立索引

索引的選擇性是指索引列中不同值的數目與表中記錄數的比，一個索引的選擇項越接近于一，這個索引的效率就越高

性能分析

Mysql Query optimizer

Mysql中有專門負責優化Select語句的優化器子產品

Mysql常見瓶頸

Explain

Explain + SQL語句

explain select * from table

能幹嗎?

• 表的讀取順序 explain -->id
• 資料讀取操作的操作類型explain -->select_type
• 哪些索引可以使用
• 哪些索引實際被使用
• 表之間的引用
• 每張表有多少行被優化器查詢

執行計劃包含的資訊

• id(重要) : id相同,執行順序從上往下,id越大越先執行

• select_type :

  ​ Simple:簡單的select查詢,查詢中不包含子查詢或者UNION

  ​ Primary:查詢中若包含任何複雜的子查詢,最外層查詢則被标記為Primary

  ​ SubQuery:在select或where清單中包含的子查詢

  ​ derived:衍生,在from清單中包含的子查詢被标記為Derived衍生,MySQL會遞歸查詢這些子查詢,将結果放到臨時表

  ​ union: union查詢後面的結果

  ​ union result:所有的結果在一起

• table :這一行資料是關于哪一張表的
• type (重要):常用的8種: all,index,range,ref,eq_ref,const,system,null
  • 通路類型排列
  • 最好到最差 system>const>eq_ref>ref>range>index>all
  • system表隻有一行記錄,平常不會出現,這個可以忽略不計
  • const:通過索引一次(不需要回表)就找到了,其用于比較primary key或者unique索引,因為隻有一條,是以很快
  • eq_ref:唯一性索引掃描(需要回表),表中隻有一條記錄與之比對,常見于逐漸或者唯一索引
  • ref:非唯一性索引掃描,傳回比對某個單列值的所有行
  • range:隻檢索給定範圍的行,between,in ><等
  • index:查詢所有索引,值周遊索引樹,因為索引檔案比資料檔案小
  • all 全表掃描
• possible_keys(重要):顯示可能應用的索引,一個或多個,索引被列出,但是不一定被使用
• key(重要):實際使用的索引,如果為null,則沒有使用索引
  • 查詢中如果使用了覆寫索引,則索引出現在key中
• key_len:表示索引中使用的位元組數,不損失精度的情況下長度越短越好
• ref:顯示索引的那一列被使用了
• rows:每張表有多少行被優化器查詢,即每張表查詢出來了多少行資料
• extra(重要):

  • Using filesort:mysql會對資料使用一個外部的索引排序,而不是按照表内的索引順序進行讀取

    mysql中無法利用索引完成的排序操作稱為"檔案排序"

  • Using temporary:産生了建立的内部臨時表,常見于order by和分組group by
  • Using Index:相應的select中使用了覆寫索引,避免的通路表的資料行(回表),效率不錯
  • 覆寫索引:select s1 from t where s1 = “”,查詢的條件正好是索引s1對應的列(不需要回表)
  • 等等

索引分析

組合索引中,如果其中一個是條件範圍,則會導緻索引失效

例如組合索引是三個條件(c1,c2,c3),c2如果是範圍判斷則c3的索引會失效

兩張表左右連接配接時添加索引:

左連接配接索引加到右表中,右連接配接索引加到左表中,相反加

JOIN語句的優化

盡可能減少Join語句中的NestedLoop的循環總次數;“永遠用小結果集驅動大結果集”,小表驅動大表

優先優化NestedLoop(嵌套循環)的内層循環

保證Join語句中被驅動表上Join條件字段已經被索引

當無法保證被驅動表的Join條件字段被索引,且内層充足時,無需吝啬JoinBuffer

索引失效(怎樣避免)

MySQL基本指令

使用哪個資料庫

use database1

建立索引

單索引create index idx_table_id on table(id)

組合索引create index idx_table_id on table(id,name)

alter table “table” add index idx_table_id(id,name)

檢視索引

show index from table

删除索引

drop index idx_table_id on table

案例索引失效

最好全值比對

組合索引最好幾個條件都寫上,最左側不可少,中間不可斷

最佳左字首法則

組合索引最左側列不可缺少

不在索引列上做任何操作(計算,函數,類型轉換(自動手動))

将運算放在邏輯運算符右邊

例:where age > 3+1

範圍後面全失效

where後面多個條件,中間一個是範圍(例:>),則後面的查詢都不會走索引

盡量使用覆寫索引

即指不需要回表,隻從MYI檔案中就能得到值

mysql在使用不等于時會全表掃描

is null ,is not null也無法使用索引

like以通配符開頭(’%abc’)也會導緻索引失效

解決like索引失效的問題，使用覆寫索引，

如果查詢的項是*或者查詢的項比（覆寫的與id）的要多，依然會失效

字元串不加單引号索引失效

自動或手動類型轉換

少用or,用它來連接配接會導緻索引失效

MySql優化器

視訊P44

當查詢時,MySQL會自動調整查詢條件的順序

MySQL定值，範圍還是排序，一般order by是給個範圍

排序沒有用到索引，都會有using filesort

group by 基本上都需要進行排序，會有臨時表産生

範圍後面全失效，如果是like ‘aa%’,組合索引依然會用到,是以該并不是範圍後面全失效

P45結束

SQL優化:

1.explain

1. 觀察,至少跑1天,看看生産的慢SQL情況
2. 開啟慢查詢日志,設定門檻值,比如超過5秒鐘的就是慢SQL,将其抓取出來
3. explain + 慢SQL 分析
4. show profile
5. 最後是DBA進行SQL資料庫伺服器的參數調優

==總結

1. 慢查詢的開啟并捕獲
2. explain+慢SQL分析
3. show profile查詢SQL在mysql伺服器的執行細節和聲明周期情況
4. SQL資料庫伺服器參數的調優

3.MySQL查詢截取分析

小表驅動大表 例子IN與Exist

下面兩種循環,上面隻需要與資料建立5次連接配接,下面則需要建立1000連接配接

具體SQL例子:

Order BY 關鍵字優化

order by 子句,盡量使用index方式排序,避免使用filesort方式排序

盡可能在索引列上完成排序操作,遵循索引建的最佳左字首原則

如果不在索引列上,filesort有兩種算法:mysql就要啟動雙路排序和單路排序

雙路排序:MySQL4.1之前使用(已經過時),意思是兩次掃描磁盤,先進行一次排列,在将拍好序的資料傳回

單路排序:一次掃描磁盤,在buffer中進行排序(在記憶體中處理了)

單路總體好過雙路,但是其有問題,如果一次性不能抓取完所有資料,則可能會比雙路慢

MySQL配置檔案中可以配置sort_buffer的大小,如果單路取出的需要的資料比sort_buffer的容量大,則會導緻多次I/O,會更慢

優化政策

增大sort_buffer_size參數的設定

增大max_length_for_sort_data

提高order by的速度

1. order by的select *是一個大忌,隻查詢需要的字段

   1.1 當query的字段大小小于max_length_for_sort_data而且排序字段不是text\blob時候,用單路,否則用多路排序

   1.2 兩種散發的資料都有可能超出sort_buffer的容量,會建立臨時檔案進行合并,會有多次IO,但是用單路排序風險會大一些

2. 嘗試提高sort_buffer_size
3. 嘗試提高max_length_for_sort_data

小總結

mysql兩種排序方式:檔案排序或掃描有序索引排序

mysql能為排序與查詢使用相同的索引

order by能使用索引最左字首

如果where使用索引的最左字首定義為常量,則order by能使用索引

不能使用索引排序

Group BY基本類似order by

隻有一點不一樣:where 高于having,能使用where的就不要使用having來限定

慢查詢日志

慢查詢

用來記錄SQL響應時間超過門檻值的語句,具體指時間超過long_query_time的SQL

預設情況下,mysql并沒有開啟這個功能

如何檢視和開啟

開啟隻對目前資料庫生效

如果要永久生效,需要改變配置檔案my.cnf

日志分析工具

mysqldumpslow --help

批量資料腳本

定義傳回随機字元串的方法

delimeter–用于定義結束符号,原先的是分号(😉

建立存儲過程

執行存儲過程,call 調用

show profile

是什麼

是Mysql提供可以用來分析目前會話中語句執行的資源消耗情況,可以用于SQL的調優的測量

預設情況下:參數處于關閉狀态,并儲存最近15次的運作結果

指令:

show variables like ‘profiling’

檢視結果:show profiles

show profiles cpu,block io for query 3(這個三是上面截圖的query_id)

全局查詢日志

僅可以在測試環境測試

配置啟用

在MySQL的my.cnf中，設定如下：

#開啟

general_log=1

#記錄日志檔案的路徑

general_log_file = /path/logfile

#輸出格式

log_output=FILE

指令:

set global general_log =1

set global log_output=‘table’

4.MySQL鎖機制

鎖是計算機協調多個程序或線程并發通路某一資源的機制

鎖分類

粒度來分:表鎖,行鎖,頁鎖

操作類型來分:讀寫鎖

讀鎖(共享鎖):針對同一份資料,多個讀操作可以同時進行而不會互相影響

寫鎖(排它鎖):目前寫操作沒有完成前,它會阻斷其他寫鎖和讀鎖

三鎖

• 表鎖(偏讀)
• 行鎖(偏寫)
• 頁鎖

表鎖:

偏向MYISAM存儲引擎,開銷小,加鎖快;無死鎖;鎖定粒度大.鎖沖突高,并發度小

手動增加表鎖

lock table 表名字 read(write),表名字2 read(write),其他;

檢視加鎖的表

show open tables

釋放表鎖

unlock tables;

讀鎖:

Thread1對表table1加了讀鎖,多個線程都可讀,但是都不可寫,被加鎖的表table1不可寫,未被加鎖的表(其他的表)也不可寫,其他線程不可以對table1寫操作(操作要等待,Thread1解鎖之後才能執行)

寫鎖:

目前線程可讀可寫表,不能讀其他表,其他線程不能讀寫被加鎖的表(會阻塞)

簡而言之:就是讀鎖阻塞寫,但是不會阻塞讀,而寫鎖則會把讀和寫都阻塞

行鎖

加鎖

CAP

強一緻性C

高可用A

分區容錯性P

間隙鎖

行鎖總結

分析行鎖

5.主從複制

就是一些配置

最好MySQL要版本一緻

主從都配置在 mysqlid 節點下

主機修改my.ini檔案

從機需要修改的東西

到這裡基本所有視訊都看完了

大部分都隻是看了,并沒有去實踐,是以之後要多實踐

另外這裡的主從配置隻是簡單截圖,之後要多搜一下其他部落格再看一下具體操作步驟以及相關的問題解決

ok,就到這裡了