文章目錄

• • • 一、MYSQL 架構(gòu)與歷史
    • • 1.mysql架構(gòu)簡(jiǎn)圖
      • 2. mysql并發(fā)控制
      • • 2.1 鎖策略
        • 2.2 鎖粒度
      • 3. mysql 事務(wù)
      • • 3.1 mysql事務(wù)日志
    • 二、服務(wù)性能剖析
    • • 1. 服務(wù)性能指標(biāo)
    • 三、mysql優(yōu)化
    • • 1. schema和數(shù)據(jù)類型的優(yōu)化
      • 2. mysql設(shè)計(jì)的范式
      • 3. 緩存匯總相關(guān)
      • 4. alter table
    • mysql知識(shí)點(diǎn)(補(bǔ)充)
    • • • 1. 查看表的相關(guān)信息

一、MYSQL 架構(gòu)與歷史

1.mysql架構(gòu)簡(jiǎn)圖

[外鏈圖片轉(zhuǎn)存失敗,源站可能有防盜鏈機(jī)制,建議將圖片保存下來直接上傳(img-vWu46yfh-1645871438197)(…/mysql-image\MySql架構(gòu)簡(jiǎn)圖.png)]

? mysql存儲(chǔ)引擎將存儲(chǔ)引起中的數(shù)據(jù)通過行緩存格式拷貝數(shù)據(jù)、服務(wù)層將拷貝內(nèi)存解碼成各個(gè)列。(數(shù)據(jù)庫(kù)一個(gè)表不建議設(shè)計(jì)多個(gè)列)

2. mysql并發(fā)控制

2.1 鎖策略

同一個(gè)數(shù)據(jù)并發(fā)修改需要鎖的機(jī)制來保證數(shù)據(jù)的一致性，所以常見的并發(fā)控制是通過實(shí)現(xiàn)兩種機(jī)制的鎖來解決并發(fā)問題。分別是排它鎖（也叫寫鎖）和共享鎖(讀鎖)，場(chǎng)景如下：

• 讀讀鎖：這種場(chǎng)景下是不會(huì)出現(xiàn)阻塞的，因?yàn)樽x數(shù)據(jù)并不會(huì)影響數(shù)據(jù)，所以支持并發(fā)讀數(shù)據(jù)。
• 寫寫鎖：該場(chǎng)景需要進(jìn)行阻塞。
• 讀寫鎖（寫讀鎖） ：雖然有讀請(qǐng)求，但是寫請(qǐng)求會(huì)影響讀取結(jié)果，也需要進(jìn)行排他鎖處理。

所以說一個(gè)寫鎖會(huì)阻塞其他的寫鎖和讀鎖。

2.2 鎖粒度

在上面我們介紹了鎖的實(shí)現(xiàn)策略，而提交系統(tǒng)并發(fā)還有一種方式是細(xì)化鎖定的范圍即鎖粒度 。只有在保證數(shù)據(jù)安全的前提下使得鎖定范圍盡可能的小，從而使得并發(fā)程度更高。

MySQL有三種鎖的級(jí)別：頁(yè)級(jí)、表級(jí)、行級(jí)

• MyISAM和MEMORY存儲(chǔ)引擎采用的是表級(jí)鎖（table-level locking）；
• BDB存儲(chǔ)引擎采用的是頁(yè)面鎖（page-level locking），但也支持表級(jí)鎖；
• InnoDB存儲(chǔ)引擎既支持行級(jí)鎖（row-level locking），也支持表級(jí)鎖，但默認(rèn)情況下是采用行級(jí)鎖。

MySQL這種鎖的特性可大致歸納如下：

• 表級(jí)鎖：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。
• 行級(jí)鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。
• 頁(yè)面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

鎖的機(jī)制是由各個(gè)的存儲(chǔ)引擎實(shí)現(xiàn)的

3. mysql 事務(wù)

3.1 mysql事務(wù)日志

? 在修改數(shù)據(jù)的時(shí)候，只需修改內(nèi)存中的數(shù)據(jù)并將修改記錄以順序IO的形式寫入事務(wù)日志中，待以后慢慢回寫磁盤。

有了事務(wù)日志，則數(shù)據(jù)持久化到磁盤不需要進(jìn)行實(shí)時(shí)隨機(jī)IO的更新數(shù)據(jù)操作

二、服務(wù)性能剖析

1. 服務(wù)性能指標(biāo)

• 響應(yīng)時(shí)間 : 服務(wù)器處理一次請(qǐng)求響應(yīng)的耗時(shí)。
• QPS：Query Per Second，每秒請(qǐng)求數(shù)。響應(yīng)時(shí)間越短，QPS則越高。在單線程的情況下，是呈線性關(guān)系，多線程時(shí)，總QPS = （1000ms/ 響應(yīng)時(shí)間）* 線程數(shù)。
• 吞吐量:單位時(shí)間內(nèi)可處理的事務(wù)的數(shù)量,屬于性能定義的倒數(shù)。

三、mysql優(yōu)化

1. schema和數(shù)據(jù)類型的優(yōu)化

schema在數(shù)據(jù)庫(kù)中表示的是數(shù)據(jù)庫(kù)對(duì)象集合，它包含了各種對(duì)像，比如：表，視圖，存儲(chǔ)過程，索引。mysql的schema等價(jià)于數(shù)據(jù)庫(kù)對(duì)象（包含表、視圖、存儲(chǔ)過程、索引等等的集合）

• 最小數(shù)據(jù)長(zhǎng)度： 能正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型,datetime和timesamp存儲(chǔ)日期(精確到秒)，但是后者只需要占據(jù)前者一半的存儲(chǔ)空間。

• 簡(jiǎn)單的數(shù)據(jù)類型：整型要比字符串(字符串設(shè)計(jì)字符編碼和排序)操作代價(jià)更低，varchar 比char類型操作代價(jià)更低。

• 避免NULL列：查詢包含NULL的列會(huì)使得 索引、索引統(tǒng)計(jì)、值比較變的復(fù)雜，且可為null的類創(chuàng)建索引的時(shí)候會(huì)額外占據(jù)空間

• 整型類型：tinyint(8)、smallint(16)、mediumint(24)、int(32)、bigint(64)。unsinged 無符號(hào)會(huì)使得正數(shù)的上限提高一倍。sql中的整型計(jì)算一般都會(huì)使用64字節(jié)的bigint整數(shù)

• 實(shí)數(shù)類型：float/double 使用浮點(diǎn)類型進(jìn)行計(jì)算(會(huì)有誤差)。decimal 為精確類型計(jì)算,其有一定的空間和計(jì)算開銷，所以一些財(cái)務(wù)數(shù)據(jù)可以使用bigint(乘以相應(yīng)的倍數(shù)存儲(chǔ))。

• 字符類型：不同引擎存儲(chǔ)格式不同。

  varchar 可變字符長(zhǎng)度（需要一個(gè)額外字段記錄長(zhǎng)度）

  char定長(zhǎng)長(zhǎng)度

  varchar相對(duì)于char節(jié)省了存儲(chǔ)空間，但更新操作來說char不容易產(chǎn)生碎片。(由于行長(zhǎng)度可變對(duì)于更新操作，頁(yè)內(nèi)沒有更多的空間可能需要通過拆分?jǐn)?shù)據(jù)進(jìn)行存儲(chǔ)（MyIsam會(huì)將行拆分成不同的片段存儲(chǔ)，InnoDB則需要分裂頁(yè)來存儲(chǔ)數(shù)據(jù)）)。

  blob：二進(jìn)制形式存儲(chǔ)大的字符類型數(shù)據(jù) tinyblob、smallblob、blob、mediumblob、bingblob

  text：字符串形式存儲(chǔ)大的字符類型數(shù)據(jù) tinytext、smatext、text、mediumtext、bigtext

  mysql將blob和text當(dāng)做獨(dú)立對(duì)象，如果其只太大時(shí) 頁(yè)內(nèi)只存儲(chǔ)指針，指向外部存儲(chǔ)的實(shí)際區(qū)域 盡量避免這兩種數(shù)據(jù)類型

• enum類型：枚舉可以將字符串存儲(chǔ)到預(yù)定義集合中，占用很少的空間。

• 日期時(shí)間類型：DateTime類型和TIMESTAMP類型，推薦使用TIMESTAMP因?yàn)槠淇臻g效率高。

• 位數(shù)據(jù)類型，mysql5.0之前BIT和tinyInt是一樣的。mysql5.0之后是使用一個(gè)或多個(gè)bit(位)存儲(chǔ)0/1,MySql將bit當(dāng)做字符串類型進(jìn)行處理。

2. mysql設(shè)計(jì)的范式

第一范式1NF： 數(shù)據(jù)庫(kù)中的每個(gè)字段不可再分。

第二范式2NF: 有主鍵，非主鍵字段依賴主鍵。

第三范式3NF: 數(shù)據(jù)庫(kù)表中不包含已在其他表中已包含的非主鍵字段。

反范式：通過一定的數(shù)據(jù)字段冗余避免表關(guān)聯(lián)。

3. 緩存匯總相關(guān)

? 對(duì)于緩存匯總相關(guān)統(tǒng)計(jì)數(shù)據(jù)來說 有如下設(shè)計(jì)方式

1. 使用視圖 增加讀性能2. 技術(shù)器相關(guān) 可以使用多條記錄存儲(chǔ)計(jì)數(shù)，最終使用sum匯總（解決多個(gè)地方同時(shí)修改一行記錄 造成的全局互斥鎖）

4. alter table

? mysql的alter table 操作對(duì)大表的性能損耗很大，與其需要表結(jié)構(gòu)的原理有關(guān)：mysql在執(zhí)行代大部分修改表結(jié)果的方式是創(chuàng)建一個(gè)新表，從舊表查詢數(shù)據(jù)并插入新表（重構(gòu)緩存）再刪除舊表，同時(shí)alter 操作會(huì)終端mysql服務(wù)。

? alter table使用技巧：

1. 在不提供服務(wù)的mysql節(jié)點(diǎn)進(jìn)行alter table操作 然后進(jìn)行主從切換。2. 使用**影子拷貝**。3. alter table 表名 alter column xxx 操作改變表結(jié)構(gòu) 會(huì)直接修改.frm文件而不涉及表數(shù)據(jù)。（<font color="red">是僅僅限于修改列的默認(rèn)值 還是所有操作</font>）

mysql知識(shí)點(diǎn)(補(bǔ)充)

1. 查看表的相關(guān)信息

SHOW TABLE STATUS LIKE 'table_name'

(Name, Engine, Version, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment)

表屬性屬性說明

Name	表名
Engine	表使用的存儲(chǔ)引起
Version
Row_format	行的格式。對(duì)于MyISAM表，可選的值為Dynamic 可變字段、Fixed或者Compressed
Rows	表中的行數(shù)。對(duì)于 MyISAM 和其他一些存儲(chǔ)引擎，該值是精確的，但對(duì)于 InnoDB，該值是估計(jì)值
Avg_row_length	平均每行包含的字節(jié)數(shù)
Data_length	表數(shù)據(jù)大小(以字節(jié)為單位)
Max_data_length	表數(shù)據(jù)的最大容量，該值和存儲(chǔ)引擎有關(guān)。
Index_length	索引的大小(以字節(jié)為單位)
Data_free	對(duì)于 MyISAM 表，表示已分配但目前沒有使用的空間。這部分空間包括了之前刪除的行，以及后續(xù)可以被 INSERT 利用到的空間。
Auto_increment	下一個(gè) AUTO_INCREMENT 的值
Create_time	表的創(chuàng)建時(shí)間
Update_time	表數(shù)據(jù)的最后修改時(shí)間
Check_time	使用 CHECK TABLE 命令或者 myisamchk 工具最后一次檢查表的時(shí)間。
Collation	表的默認(rèn)字符集和字符列排列規(guī)則
Checksum	如果啟用，保存的是整個(gè)表的實(shí)時(shí)校驗(yàn)和。
Create_options	創(chuàng)建表時(shí)的其他選項(xiàng)
Comment	該列包含了一些其他的額外信息。對(duì)于 MyISAM 表，保存的是表在創(chuàng)建時(shí)帶的注釋。對(duì)于 InnoDB 表，則保存的是 InnoDB 表空間的剩余空間信息。如果是一個(gè)視圖，則該列包含 “VIEW” 的文本字樣。

總結(jié)

以上是生活随笔為你收集整理的高性能mysql学习笔记一的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。