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

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

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

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

表列

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

行格式

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

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

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

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

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

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

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

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

我們要觀察的是有問題的，所以可以進行過濾：

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

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

總結(jié):

CPU報警：很可能是 SQL 里面有較多的計算導致的

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

3.MySQL的復制原理以及流程

MySQL主從復制工作原理

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

復制過程

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

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

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

方案一

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

方案二

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

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

方案三

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

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