本文針對(duì)MySQL一些常見比較重要的知識(shí)點(diǎn)進(jìn)行了詳細(xì)的總結(jié)，希望對(duì)你有幫助。

MySQL如何執(zhí)行一條SQL

參考筆者這篇文章進(jìn)行了詳細(xì)的總結(jié)：《深入剖析 MySQL 某條執(zhí)行過(guò)程》

MySQL支持的存儲(chǔ)引擎有哪些

通過(guò)下面show engines;這段命令即查看MySQL默認(rèn)的存儲(chǔ)引擎。對(duì)應(yīng)的查詢結(jié)果如下圖所示，可以看到MySQL默認(rèn)采用InnoDB作為存儲(chǔ)引擎。而且InnoDB是MySQL中唯一一個(gè)支持事務(wù)性存儲(chǔ)的存儲(chǔ)引擎。

同時(shí)MySQL早期用的存儲(chǔ)引擎就是MyISAM ，然后變成InnoDB，因?yàn)镸ySQL采用的是插件時(shí)存儲(chǔ)引擎，所以存儲(chǔ)引擎是可以任意切換的，

注意：存儲(chǔ)引擎配置所針對(duì)的維度是針對(duì)表的，而不是針對(duì)某張數(shù)據(jù)庫(kù)的，如下建表語(yǔ)句，我們就將存儲(chǔ)引擎設(shè)置為innodb :

-- 測(cè)試腳本
drop table if exists `test`;
create table `test` (
    `id` bigint not null comment 'id',
    `name` varchar(50) comment '名稱',
    `password` varchar(50) comment '密碼',
    primary key (`id`)
) engine=innodb default charset=utf8mb4 comment '測(cè)試';

MyISAM和InnoDB的區(qū)別

MyISAM的特點(diǎn):

• 它在性能方面表現(xiàn)出色，例如全文索引、壓縮、空間函數(shù)等都沒(méi)問(wèn)題。
• 只支持表級(jí)鎖。
• 不支持事務(wù)。
• 不支持故障后安全恢復(fù)。
• 因?yàn)椴恢С中屑?jí)鎖，所以就不支持MVCC。
• MyISAM存儲(chǔ)引擎數(shù)據(jù)和索引文件是分開。
• 不支持外鍵。

InnoDB的特點(diǎn)：

• 支持行級(jí)鎖。
• 因?yàn)樾屑?jí)鎖，所以支持MVCC，通過(guò)MVCC保證了repeat read(可重復(fù)讀)的效率，并通過(guò)間隙鎖防止幻行插入所導(dǎo)致的幻讀的問(wèn)題。
• 支持事務(wù)，所以并發(fā)讀寫的情況下性能優(yōu)異。
• 同時(shí)支持故障后安全恢復(fù)(依賴redolog)，
• 也支持外鍵，但是一般情況下我們不太建議開發(fā)數(shù)據(jù)表使用外鍵。

特定情況下的索引和數(shù)據(jù)都在同一個(gè)文件上，也就是我們常說(shuō)的聚簇索引，通過(guò)聚簇索引可以保證高效快速的主鍵查詢，因?yàn)槎?jí)索引包含主鍵列，所以但如果主鍵占用物理空間過(guò)大的話，二級(jí)索引占用的空間也會(huì)很大，所以如果存在多個(gè)索引的情況下，建議適當(dāng)調(diào)小主鍵索引的大小。

什么是多版本并發(fā)控制(MVCC)

可以參考筆者這篇關(guān)于mvcc的講解，解釋的比較全面：《詳解 undoLog 在 MySQL 多版本并發(fā)控制 MVCC 中的運(yùn)用》

如何選擇MyISAM和InnoDB

大部分情況下都建議使用InnoDB，很多人認(rèn)為MyISAM性能要好于InnoDB，事實(shí)并非如此，在《高性能MySQL》中提及過(guò):

不要輕易相信“MyISAM 比 InnoDB 快”之類的經(jīng)驗(yàn)之談，這個(gè)結(jié)論往往不是絕對(duì)的。在很多我們已知場(chǎng)景中，InnoDB 的速度都可以讓 MyISAM 望塵莫及，尤其是用到了聚簇索引，或者需要訪問(wèn)的數(shù)據(jù)都可以放入內(nèi)存的應(yīng)用。

現(xiàn)代應(yīng)用軟件系統(tǒng)大部分都是用于處理一些短期的事務(wù)，且大部分情況下是不需要回滾的，所以InnoDB是個(gè)不錯(cuò)的選擇，況且InnoDB是可以通過(guò)redo.log完成數(shù)據(jù)崩潰后恢復(fù)，這一點(diǎn)是MyISAM所不具備的，這也就是為了MySQL8.0之后將InnoDB作為默認(rèn)的存儲(chǔ)引擎。

MySQL字段char和varchar 的區(qū)別

我們先來(lái)說(shuō)說(shuō)varchar，varchar 常用于存儲(chǔ)一些不定長(zhǎng)的字段數(shù)據(jù)，它會(huì)通過(guò)1-2字節(jié)來(lái)記錄字段長(zhǎng)度，后續(xù)字節(jié)用于記錄可變長(zhǎng)字符串：

因?yàn)槭强勺冮L(zhǎng)的緣故，所以在于字符串區(qū)間變化較大的場(chǎng)景下，相對(duì)于char它會(huì)更加節(jié)省存儲(chǔ)空間，同樣的缺點(diǎn)也很明顯，如果涉及大量修改varchar字段導(dǎo)致原有空間無(wú)法容納varchar時(shí)，就可能導(dǎo)致頁(yè)分裂來(lái)容納行。

所以varchar可能更適合字段長(zhǎng)度不一定大量趨近于平均長(zhǎng)度，且更新較少長(zhǎng)度變化不大（不容易產(chǎn)生碎片）的場(chǎng)景。

而char則時(shí)定長(zhǎng)的空間，如果字符長(zhǎng)度不足則用結(jié)束符標(biāo)記字符串結(jié)束，對(duì)于字符串較短或者長(zhǎng)度幾乎相同、修改較少的場(chǎng)景，使用char性能表現(xiàn)會(huì)比前者更出色一些，因?yàn)閏har長(zhǎng)度固定，碎片較少，可以很少的利用局部性原理IO大量數(shù)據(jù)。

需要了解的是varchar(30) 代表存30個(gè)字符，其中中文占3字節(jié)，所以30個(gè)字符要占用90字節(jié)。英文是1字節(jié)。

我們可以鍵入下面sql，這里補(bǔ)充一下char_length獲取的字符長(zhǎng)度，有幾個(gè)字符長(zhǎng)度就是多少,length算的是字節(jié)數(shù)，查看可以看到length('哈哈')為6，length('hh')為2。

select char_length('哈哈'),length('哈哈');

中文字符串長(zhǎng)度的輸出結(jié)果:

char_length('哈哈')|length('哈哈')|
-----------------+------------+
                2|           6|

英文字符長(zhǎng)度查詢SQL：

select char_length('hh'),length('hh');

英文字符長(zhǎng)度輸出結(jié)果：

char_length('hh')|length('hh')|
-----------------+------------+
                2|           2|

如何開啟MySQL看查詢緩存

通過(guò)修改my.cnf中加入下面這段配置即可：

query_cache_type=1
query_cache_size=600000

查詢緩存不命中的幾個(gè)特殊場(chǎng)景是什么

• 查詢SQL一樣，但是字符串大小寫不一樣。
• 查詢的SQL涉及自定義函數(shù)、用戶變量、臨時(shí)表、MySQL庫(kù)中的表等情況，MySQL服務(wù)器不會(huì)緩存數(shù)據(jù)。
• 一旦我們進(jìn)行數(shù)據(jù)更新或者表結(jié)構(gòu)調(diào)整的情況，那么緩存也會(huì)被清理掉。
• 緩存空間滿了，會(huì)根據(jù)緩存回收算法去清空SQL緩存。

MySQL磁盤爆滿對(duì)應(yīng)的解決方案

我們需要根據(jù)不同的原因進(jìn)行相應(yīng)的處理：

• 數(shù)據(jù)量暴增：這就得多方面考慮了，為什么會(huì)暴增，暴增是否因?yàn)闃I(yè)務(wù)涉及不合理，我們是否可以從功能上進(jìn)行優(yōu)化，例如某些日志分表存儲(chǔ)著一些過(guò)期的稽核數(shù)據(jù)，我們是否可以適當(dāng)?shù)膶⑦@些表空間數(shù)據(jù)釋放，若實(shí)在無(wú)法進(jìn)行空間釋放，可以考慮服務(wù)進(jìn)行磁盤擴(kuò)容了。
• 日志：日志導(dǎo)致容量暴增基本就bin log或者error日志沒(méi)有及時(shí)清理了，這種情況我們只能刪除一些binlog即可了。
• 臨時(shí)文件：數(shù)據(jù)庫(kù)某些查詢結(jié)果都會(huì)放在內(nèi)存的，當(dāng)內(nèi)存空間不足時(shí)就會(huì)為查詢結(jié)果生成一個(gè)臨時(shí)文件(例如對(duì)并發(fā)場(chǎng)景下各種大表進(jìn)行select * from table)，就很可能產(chǎn)生大量臨時(shí)文件，進(jìn)而出現(xiàn)CPU爆滿和IO次數(shù)激增。

針對(duì)臨時(shí)文件爆滿問(wèn)題，對(duì)應(yīng)的解決方式也很簡(jiǎn)單，首先找到臨時(shí)文件的位置：

show variables like 'tmpdir'

然后到達(dá)對(duì)應(yīng)的位置將臨時(shí)文件內(nèi)容置空：

echo '' >> host-xxxxx.log

注意：我們此時(shí)可能還需清除慢查詢SQL，查看是否有time數(shù)據(jù)很大的慢查詢

SELECT id, `state`, user,host,time,`INFO` FROM information_schema.processlist where state IS NOT NULL  and state <> "" ORDER BY time desc;

如果有則殺掉：

SELECT concat('kill ', id, ';') FROM information_schema.processlist where user = 'HispaceCMS' and  `COMMAND` = 'Query' and  state IS NOT NULL and state <> '' and DB is not null and time > 1000 ORDER BY time desc

MySQL中的count(*)、count(1)、count(列名)的區(qū)別

回答這個(gè)問(wèn)題我們不妨做個(gè)實(shí)驗(yàn)，首先建立數(shù)據(jù)表：

create table count_test(
 id int 
)


insert into count_test values(1);
insert into count_test values(2);
insert into count_test values(null);

然后鍵入以下SQL進(jìn)行查詢，可以看到前面兩條不會(huì)忽略null值，最后count(列名)會(huì)忽略null值。

select count(*),count(1),count(id) from count_test;

而性能在性能方面，很多人認(rèn)為count(1)>count(*)>count(id)實(shí)際上前兩者性能表現(xiàn)基本是一樣的，按照《高性能MySQL》的說(shuō)法：

通配符*并不會(huì)像我們所想的那樣擴(kuò)展成所有的列，實(shí)際上，它會(huì)忽略所有的列而返回統(tǒng)計(jì)的行數(shù)。

而count(1)傳入的是常量，所以只做掃描行數(shù)，所以實(shí)際上性能表現(xiàn)為：count(1)≈count(*)>count(id)

如何定位慢查詢SQL

針對(duì)慢SQL問(wèn)題，如果業(yè)務(wù)上可以感知我們直接通過(guò)接口定位就好了，但是針對(duì)界面不可見的后端調(diào)度任務(wù)，就必須進(jìn)行實(shí)時(shí)監(jiān)控了。 要想定位慢查詢SQL首先自然是要開啟慢查詢?nèi)罩荆瑢?duì)應(yīng)的我們可以在my.cnf/my.ini中增加如下配置

[mysqld]
slow_query_log = 1
# 慢查詢?nèi)罩镜奈恢?slow_query_log_file = /var/log/mysql/slow.log
# 最大時(shí)間閾值設(shè)置為5s
long_query_time = 5

后續(xù)想要獲取慢查詢的日志信息，我們可以通過(guò)如下指令導(dǎo)出，亦或者通過(guò)通過(guò)監(jiān)控工具導(dǎo)出告警：

mysqldumpslow -s t /var/log/mysql/slow.log   # 按耗時(shí)排序
mysqldumpslow -s c /var/log/mysql/slow.log   # 按出現(xiàn)次數(shù)排序

而slow.log日志的內(nèi)容，大體如下所示，對(duì)應(yīng)字段含義分別是：

• Query_time：查詢耗時(shí)
• Lock_time：等待表鎖的時(shí)間
• Rows_sent：返回給客戶端的行數(shù)
• Rows_examined：掃描了 50萬(wàn)行 數(shù)據(jù)

最后就是執(zhí)行的SQL和時(shí)間：

# Time: 2023-10-05T12:34:56.789012Z
# User@Host: app_user[app_db] @  [10.0.0.2]
# Query_time: 5.123456  Lock_time: 0.002000 Rows_sent: 0  Rows_examined: 500000
SET timestamp=1696516496;
UPDATE products SET stock = stock - 1 WHERE product_id IN (SELECT product_id FROM orders WHERE order_date < '2023-01-01');

如果不用MySQL你會(huì)考慮用哪個(gè)數(shù)據(jù)庫(kù)

優(yōu)先考慮TIDB，這是一個(gè)具備關(guān)系型數(shù)據(jù)庫(kù)和NOSQL數(shù)據(jù)庫(kù)的優(yōu)點(diǎn)，旨在提供高可用、強(qiáng)一致性的的分布式數(shù)據(jù)庫(kù)，總的來(lái)說(shuō)，它具備以下幾個(gè)優(yōu)點(diǎn)：

• 支持水平拓展，Tidb可以通過(guò)增加節(jié)點(diǎn)實(shí)現(xiàn)擴(kuò)展，支持大規(guī)模數(shù)據(jù)存儲(chǔ)和高并發(fā)訪問(wèn)。
• 數(shù)據(jù)庫(kù)會(huì)自行完成分片并存儲(chǔ)在不同節(jié)點(diǎn)上，避免我們業(yè)務(wù)邏輯上的分表的實(shí)現(xiàn)的復(fù)雜度。
• TiDB支持ACID事務(wù)，確保數(shù)據(jù)一致性和完整性。
• 它通過(guò)raft保證高可用和一致性。
• 支持大多數(shù)MySQL的SQL語(yǔ)法。

使用MySQL主從架構(gòu)時(shí)，需要注意那些問(wèn)題

在進(jìn)行分庫(kù)分表時(shí)，我們必須結(jié)合硬件條件對(duì)應(yīng)的MySQL壓測(cè)結(jié)果針對(duì)業(yè)務(wù)需求評(píng)估資源，例如我們的業(yè)務(wù)需求要求QPS是10w，對(duì)應(yīng)的數(shù)據(jù)庫(kù)給定的服務(wù)器配置是4C8G，按照下圖給出的壓測(cè)報(bào)告，我們至少是需要30臺(tái)(15臺(tái)master和15臺(tái)slave)數(shù)據(jù)庫(kù)服務(wù)器保證高并發(fā)和高可用：

主從同步期間，要保證寫操作都是在主庫(kù)上，一旦寫入操作不小心寫入到從庫(kù)，就會(huì)因?yàn)橹鲝臄?shù)據(jù)不一致導(dǎo)致bin.log同步復(fù)制數(shù)據(jù)中斷。