1.數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

其實(shí)一般業(yè)務(wù)開發(fā)中, 這個(gè)關(guān)注的不多，估計(jì)是一些偏極客的團(tuán)隊(duì)關(guān)注的比較多些

使表占用盡量少的磁盤空間。減少磁盤I/O次數(shù)及讀取數(shù)據(jù)量是提升性能的基礎(chǔ)原則。表越小，數(shù)據(jù)讀寫處理時(shí)則需要更少的內(nèi)存，同時(shí)，小表的索引占用也相對(duì)小，索引處理也更加快速。

MySQL支持不同類型的存儲(chǔ)引擎和行格式，針對(duì)不同類型，業(yè)務(wù)需求的表應(yīng)該設(shè)置合適的存儲(chǔ)引擎及索引方法。表設(shè)置建議如下：

表列

• 為列選擇最合適(通常最小)的數(shù)據(jù)類型。MySQL 具有許多不同列類型以最大化的減少磁盤和內(nèi)存占用。例如，使用足夠小的整型來表示小范圍的小整型數(shù)據(jù)。MEDIUMINT 通常是一個(gè)很好的選擇，它只占用INT 25%，甚至更小的空間。
• 如果可能，則將列聲明為NOT NULL。聲明為NOT NULL，使得優(yōu)化器能夠更好的使用索引，并避免了判斷NULL的處理，這使得SQL 操作執(zhí)行的更加快速。同時(shí)也為每列節(jié)省了1 bit的空間。如果確實(shí)需要使用NULL 值，那么也應(yīng)該避免所有列都允許NULL。
• InnoDB 表默認(rèn)使用動(dòng)態(tài)類型(DYNAMIC )的行格式。可以通過設(shè)置默認(rèn)行格式(innodb_default_row_format)，或者在表定義(CREATE TABLE 或 ALTER TABLE )中聲明使用的行格式。

行格式

壓縮類型的行格式，包括COMPACT, DYNAMIC, 和 COMPRESSED，對(duì)于特定操作，減少了存儲(chǔ)空間占用，但是增加了CPU計(jì)算能力使用。如果主要的負(fù)載在緩存命中率及磁盤讀寫速度，那么這種格式將能夠提升數(shù)據(jù)庫反應(yīng)速度。如果是極端情況負(fù)載受限于CPU性能，那么使用這種格式則會(huì)降低數(shù)據(jù)庫性能。

壓縮行格式也會(huì)對(duì)使用utf8mb3 或者 utf8mb4格式的變長CHAR 類型列存儲(chǔ)進(jìn)行優(yōu)化處理。對(duì)于使用ROW_FORMAT=REDUNDANT, CHAR(N) 定義的表，每個(gè)列值最多占用 N × 個(gè)字節(jié)長度。許多語言可以使用但字節(jié)的utf8格式表示，所以規(guī)定那個(gè)長度的定義通常會(huì)造成空間浪費(fèi)。壓縮行格式定義下，InnoDB 會(huì)每一個(gè)列值分配一個(gè)N 到 N× 個(gè)字節(jié)的空間。

2.MySQL數(shù)據(jù)庫cpu飆升到500%的話, 應(yīng)該怎么處理?

當(dāng) cpu 飆升到 500%時(shí)，先用操作系統(tǒng)命令 top 命令觀察是不是 mysqld 占用導(dǎo)致的，如果不是，找出占用高的進(jìn)程，并進(jìn)行相關(guān)處理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情況，是不是有消耗資源的 sql 在運(yùn)行。找出消耗高的 sql，看看執(zhí)行計(jì)劃是否準(zhǔn)確， index 是否缺失，或者實(shí)在是數(shù)據(jù)量太大造成。

一般來說，肯定要 kill 掉這些線程(同時(shí)觀察 cpu 使用率是否下降)，等進(jìn)行相應(yīng)的調(diào)整(比如說加索引、改 sql、改內(nèi)存參數(shù))之后，再重新跑這些 SQL。

也有可能是每個(gè) sql 消耗資源并不多，但是突然之間，有大量的 session 連進(jìn)來導(dǎo)致 cpu 飆升，這種情況就需要跟應(yīng)用一起來分析為何連接數(shù)會(huì)激增，再做出相應(yīng)的調(diào)整，比如說限制連接數(shù)等

show full processlist 可以看到所有鏈接的情況，但是大多鏈接的 state 其實(shí)是 Sleep 的，這種的其實(shí)是空閑狀態(tài)，沒有太多查看價(jià)值

我們要觀察的是有問題的，所以可以進(jìn)行過濾：

-- 查詢非 Sleep 狀態(tài)的鏈接，按消耗時(shí)間倒序展示，自己加條件過濾

select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc 

總結(jié):

CPU報(bào)警：很可能是 SQL 里面有較多的計(jì)算導(dǎo)致的

連接數(shù)超高：很可能是有慢查詢，然后導(dǎo)致很多的查詢?cè)谂抨?duì)，排查問題的時(shí)候可以看到”事發(fā)現(xiàn)場“類似的 SQL 語句一大片，那么有可能是沒有索引或者索引不好使，可以用：explain 分析一下 SQL 語句

3.MySQL的復(fù)制原理以及流程

MySQL主從復(fù)制工作原理

• 在主庫上把數(shù)據(jù)更高記錄到二進(jìn)制日志
• 從庫將主庫的日志復(fù)制到自己的中繼日志
• 從庫讀取中繼日志的事件，將其重放到從庫數(shù)據(jù)中
• 基本原理流程，3個(gè)線程以及之間的關(guān)聯(lián)
• 主：binlog線程——記錄下所有改變了數(shù)據(jù)庫數(shù)據(jù)的語句，放進(jìn)master上的binlog中;
• 從：io線程——在使用start slave 之后，負(fù)責(zé)從master上拉取 binlog 內(nèi)容，放進(jìn)自己的relay log中;
• 從：sql執(zhí)行線程——執(zhí)行relay log中的語句;

復(fù)制過程

• Binary log：主數(shù)據(jù)庫的二進(jìn)制日志
• Relay log：從服務(wù)器的中繼日志
• 第一步：master在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，將該操作記錄串行地寫入到binlog文件中。
• 第二步：salve開啟一個(gè)I/O Thread，該線程在master打開一個(gè)普通連接，主要工作是binlog dump process。如果讀取的進(jìn)度已經(jīng)跟上了master，就進(jìn)入睡眠狀態(tài)并等待master產(chǎn)生新的事件。I/O線程最終的目的是將這些事件寫入到中繼日志中。
• 第三步：SQL Thread會(huì)讀取中繼日志，并順序執(zhí)行該日志中的SQL事件，從而與主數(shù)據(jù)庫中的數(shù)據(jù)保持一致。

4、讀寫分離有哪些解決方案?

讀寫分離是依賴于主從復(fù)制，而主從復(fù)制又是為讀寫分離服務(wù)的。因?yàn)橹鲝膹?fù)制要求slave不能寫只能讀(如果對(duì)slave執(zhí)行寫操作，那么show slave status將會(huì)呈現(xiàn)Slave_SQL_Running=NO，此時(shí)你需要按照前面提到的手動(dòng)同步一下slave)。

方案一

• 使用mysql-proxy代理
• 優(yōu)點(diǎn)：直接實(shí)現(xiàn)讀寫分離和負(fù)載均衡，不用修改代碼，master和slave用一樣的帳號(hào)，mysql官方不建議實(shí)際生產(chǎn)中使用
• 缺點(diǎn)：降低性能， 不支持事務(wù)

方案二

• 使用AbstractRoutingDataSource+aop+annotation在dao層決定數(shù)據(jù)源。
• 如果采用了mybatis， 可以將讀寫分離放在ORM層，比如mybatis可以通過mybatis plugin攔截sql語句，所有的insert/update/delete都訪問master庫，所有的select

都訪問salve庫，這樣對(duì)于dao層都是透明。 plugin實(shí)現(xiàn)時(shí)可以通過注解或者分析語句是讀寫方法來選定主從庫。不過這樣依然有一個(gè)問題，也就是不支持事務(wù)， 所以我們還需要重寫一下DataSourceTransactionManager， 將read-only的事務(wù)扔進(jìn)讀庫，其余的有讀有寫的扔進(jìn)寫庫。

方案三

• 使用AbstractRoutingDataSource+aop+annotation在service層決定數(shù)據(jù)源，可以支持事務(wù).
• 缺點(diǎn)：類內(nèi)部方法通過this.xx()方式相互調(diào)用時(shí)，aop不會(huì)進(jìn)行攔截，需進(jìn)行特殊處理。

以上就是“大廠整理出來的mysql優(yōu)化面試題目”，你能回答上來嗎?如果想要了解更多的Java面試題相關(guān)內(nèi)容，可以關(guān)注動(dòng)力節(jié)點(diǎn)Java官網(wǎng)。