count(1) count(*) count(id)
mysiam:
將表的總行數(shù)保存到了磁盤上、所以對于無條件的count(*) 是很快返回的
innodb實現(xiàn)：
count(*) 是把數(shù)據(jù)記錄一行行的拿出來判斷、
(innodb是索引組織表、主鍵索引的葉子節(jié)點是數(shù)據(jù)記錄、普通索引的葉子節(jié)點是主鍵值、會小很多、mysql會選擇最小的那顆索引樹來遍歷怒坯、在保證邏輯正確的情況下、盡量減少掃描的數(shù)據(jù)量)
count(1) 遍歷表藻懒、但不取值剔猿、掃描記錄時、返回1給server
count(id) 會把id返回給server
count(field)
若定義為非null嬉荆、會先判斷該字段記錄是否為null归敬、非null才累加
若定義為null、從記錄讀出字段、累加
為什么mysql不直接記錄count數(shù)弄慰？
innodb要保證事務(wù)執(zhí)行第美、不同會話、commit前后 總行數(shù)是會發(fā)生改變的
show table status 替代陆爽？
得到的結(jié)果是通過采樣估算得到的什往、不精準(zhǔn)、最高偏差有40%-50%
采樣緩存系統(tǒng)保存計數(shù)?
1.redis重啟慌闭、數(shù)據(jù)丟失
2.redis和db本身記錄增加先后的問題會導(dǎo)致短時間的不精準(zhǔn)
使用mysql表保存總記錄别威？
可以、使用mysql的事務(wù)來保證驴剔、但是會影響性能
mysql兩階段提交的過程：
image.png
在不同階段crash對于系統(tǒng)的影響
1. 圖中A時刻(redo log寫完省古、binlog還沒寫的時候)crash、binlog還沒寫丧失、redo log也沒提交豺妓、重啟事務(wù)會回滾~
2.若是在B、binlog寫完布讹、redo log還未commit時琳拭、crash會發(fā)生什么？
a. 若redo log也是完整的-有了commit標(biāo)識描验、直接提交
b. 若redo log只有完整的prepare白嘁、則判斷對應(yīng)的binlog是否完整、
完整-提交事務(wù); 否則: 回滾事務(wù)
mysql如何知道binlog是完整的膘流？
一個事務(wù)的binlog有完整的格式:
statement格式的: 最后會有commit
row格式的: 最后會有Xid event
MySQL5.6.2 之后絮缅、還引入了checksum檢查日志中間出錯的情況
redo log和binlog是如何關(guān)聯(lián)的
有一個共同的字段XID、crash恢復(fù)的時候會按順序掃描redo log:
1. 遇到既有prepare 又有commit的redo log直接提交
2. 遇到只有prepare的呼股、就拿XID去對應(yīng)的binlog查找事務(wù)
處于prepare階段的redo log + 完整的binlog重啟就能恢復(fù)耕魄、為什么這么設(shè)計?
在時刻B、binlog就已經(jīng)被寫入了卖怜、若是應(yīng)用于從庫屎开、從庫就有了這條記錄、為了保證主從數(shù)據(jù)的一致性马靠、就必須保證主庫也有這條記錄、所以把redo log提交
為什么不是先寫完redo log再寫binlog 蔼两？
比較典型的分布式問題
若redo log提交了甩鳄、又不能回滾(回滾可能會覆蓋掉其它的事務(wù))、所以redo log直接提交额划、binlog寫入失敗的時候妙啃、由于不能回滾、就會比從庫多一個事務(wù)
為什么不直接使用binlog、不用redo log 揖赴？
若是歷史原因: innodb本身不是mysql的原生引擎馆匿、原生引擎是mysiam、不支持崩潰恢復(fù)
innodb在加入mysql之前燥滑、就可以支持崩潰恢復(fù)和事務(wù)
innodb發(fā)現(xiàn)binlog沒有崩潰恢復(fù)的能力渐北、那就直接使用redo log吧、
如果用binlog支持崩潰恢復(fù)呢 铭拧？流程如下圖
在這樣的流程下赃蛛、binlog還是不能支持崩潰恢復(fù)、不能支持恢復(fù)數(shù)據(jù)頁
若binlog2寫完搀菩、未commit的時候crash呕臂、引擎內(nèi)部事務(wù)2會回滾、應(yīng)用binlog2可以補回來肪跋、但對于binlog1事務(wù)已經(jīng)提交歧蒋、不會再應(yīng)用binlog1
innodb使用的是WAL、在寫完內(nèi)存和日志的時候州既、事務(wù)就算完成了谜洽、若以后崩潰、依賴日志恢復(fù)數(shù)據(jù)頁易桃、圖中1位置crash 事務(wù)1可能會丟失褥琐、且是數(shù)據(jù)頁級的丟失、binlog未記錄數(shù)據(jù)頁的更新細節(jié)晤郑、不支持?jǐn)?shù)據(jù)頁恢復(fù)
image.png
能只用redo log不用binlog嗎敌呈？
1. 只從崩潰恢復(fù)的角度來講、可以關(guān)掉binlog造寝、系統(tǒng)依然是crash-safe的磕洪、但binlog有redo log不可替代的功能
a. 歸檔. redo log是循環(huán)寫、日志無法保留
b. mysql系統(tǒng)依賴于binlog诫龙、binlog作為mysql本身就有的功能析显、
c. 一些異構(gòu)系統(tǒng)、需要消費binlog來更新數(shù)據(jù)
redo log一般設(shè)置多大签赃？
redo log過小谷异、會導(dǎo)致很快寫滿、不得不強行刷盤锦聊、這樣WAL的能力就發(fā)揮不出來了歹嘹、若磁盤在T級別、就直接設(shè)置為G級別吧~
正常運行的實例孔庭、數(shù)據(jù)寫入后的最終落盤是從redo log更新的還是從buffer poll更新的尺上？
redo log 并沒有記錄數(shù)據(jù)頁的完整數(shù)據(jù)材蛛、所以本身沒有能力更新磁盤數(shù)據(jù)頁、
1. 數(shù)據(jù)頁被修改后與磁盤數(shù)據(jù)頁不一致怎抛、最終落盤就是把內(nèi)存中的數(shù)據(jù)寫入磁盤卑吭、與redo log無關(guān)
2. 崩潰恢復(fù)的場景中、innodb如果判斷到一個數(shù)據(jù)頁可能在崩潰恢復(fù)的時候丟失了更新就會將它讀入內(nèi)存马绝、然后讓redo log更新內(nèi)存內(nèi)容豆赏、更新完、內(nèi)存變成臟頁迹淌、回到1的情況
redo log buffer是什么河绽？是先修改內(nèi)存、還是寫寫redo log 唉窃？
在事務(wù)更新過程中 redo log 是要多次寫的耙饰、
eg. begin;
insert into t1...;
insert into t2...;
commit;
這個事務(wù)要在兩個表中插入記錄、在插入的過程中纹份、生成的日志都得先保存起來苟跪、但又不能在還沒commit的時候?qū)憆edo log
所以 redo log buffer就是一塊內(nèi)存、用來保存 redo log日志的. 即 在執(zhí)行第一個insert的時候蔓涧、數(shù)據(jù)的內(nèi)存被修改了件已、redo log buffer也寫入了日志
但真正寫入redo log(ib_logfile+日志)是在執(zhí)行commit語句的時候做的、
update記錄為原值的時候元暴、mysql如何操作篷扩？
1. update是先讀后寫、發(fā)現(xiàn)值本來就是原值茉盏、不操作鉴未、直接返回
2. mysql調(diào)用innodb引擎接口修改、引擎發(fā)現(xiàn)與原值相同鸠姨、不更新铜秆、直接返回
3. innodb認(rèn)真的執(zhí)行了更新、改加鎖的加鎖?
答案是3讶迁、可以通過事務(wù)來驗證
過程請參考: https://time.geekbang.org/column/article/73479
varchar(255)是邊界连茧、>255需要兩個字節(jié)存儲、小于需要1個字節(jié)
- mysql 源碼編譯啟動報錯
mysql啟動報錯：Starting MySQL... ERROR! The server quit without updating PID file
看errlog 發(fā)現(xiàn):
2018-01-24 07:57:03 67547 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
or: 2018-01-24 07:57:03 [ERROR] Can't locate the language directory.
重新初始化db:
mysql_install_db --user=mysql --basedir=/home/devil/mysql57/ --datadir=/home/devil/mysql57/data/
出現(xiàn):
2019-04-18 21:23:16 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
so.
mysqld --initialize --user=mysql --basedir=/home/devil/mysql57/ --datadir=/home/devil/mysql57/data/
可以看到初始化成功. 并生成了臨時密碼
- mysql binlog 查看
a. 登錄mysql查看
1) 只查看第一個binlog文件的內(nèi)容
show binlog events;
2) 查看指定binlog文件的內(nèi)容
show binlog events in 'mysql-binlog.000001';
3) 查看當(dāng)前正在寫入的binlog
show master status;
4) 獲取binlog文件列表
show binary logs;
b. 使用mysqlbinlog工具查看
1) 本地查看
基于開始/結(jié)束時間:
mysqlbinlog --start-datatime='2019-04-10 00:00:00' --stop-datatime='2019-04-10 01:00:00'
基于pos值
mysqlbinlog --start-postion=107 --stop-position=1000 -d 庫名 二進制文件
2) 遠程查看
mysqlbinlog -u{uname} -p{pass} -htest.com -P3306 \
--read-from-remote-server --start-datetime='2013-09-10 23:00:00' --stop-datetime='2013-09-10 23:30:00' mysql-bin.000001 > t.binlog
set optimizer_trace='enabled=on' 打開trace記錄
select trace from `information_schema`.`optimizer_trace`; 查看trace記錄
tmp_table_size 內(nèi)存臨時表大小
sort_buffer_size 用于排序的內(nèi)存大小巍糯、超過會使用文件排序
max_length_for_sort_data 單行數(shù)據(jù)量超過這個值會使用rowid排序
查看sql被哪個語句阻塞
select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'
select blocking_pid from sys.schema_table_lock_waits 可以找到阻塞的pid
