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

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

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

MySQL支持的存儲引擎有哪些

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

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

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

-- 測試腳本
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 '測試';

MyISAM和InnoDB的區(qū)別

MyISAM的特點(diǎn):

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

InnoDB的特點(diǎn)：

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

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

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

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

如何選擇MyISAM和InnoDB

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

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

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

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

我們先來說說varchar，varchar 常用于存儲一些不定長的字段數(shù)據(jù)，它會通過1-2字節(jié)來記錄字段長度，后續(xù)字節(jié)用于記錄可變長字符串：

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

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

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

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

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

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

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

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

英文字符長度查詢SQL：

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

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

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

如何開啟MySQL看查詢緩存

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

query_cache_type=1
query_cache_size=600000

查詢緩存不命中的幾個特殊場景是什么

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

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

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

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

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

show variables like 'tmpdir'

然后到達(dá)對應(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ū)別

回答這個問題我們不妨做個實(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)行查詢，可以看到前面兩條不會忽略null值，最后count(列名)會忽略null值。

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

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

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

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

如何定位慢查詢SQL

針對慢SQL問題，如果業(yè)務(wù)上可以感知我們直接通過接口定位就好了，但是針對界面不可見的后端調(diào)度任務(wù)，就必須進(jìn)行實(shí)時(shí)監(jiān)控了。 要想定位慢查詢SQL首先自然是要開啟慢查詢?nèi)罩?，對?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ù)想要獲取慢查詢的日志信息，我們可以通過如下指令導(dǎo)出，亦或者通過通過監(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)容，大體如下所示，對應(yīng)字段含義分別是：

• Query_time：查詢耗時(shí)
• Lock_time：等待表鎖的時(shí)間
• Rows_sent：返回給客戶端的行數(shù)
• Rows_examined：掃描了 50萬行 數(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你會考慮用哪個數(shù)據(jù)庫

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

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

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

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

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