1.數據庫結構優化

其實一般業務開發中, 這個關注的不多，估計是一些偏極客的團隊關注的比較多些

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

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

表列

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

行格式

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

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

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

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

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

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

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

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

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

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

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

總結:

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

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

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

MySQL主從復制工作原理

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

復制過程

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

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

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

方案一

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

方案二

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

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

方案三

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

以上就是“大廠整理出來的mysql優化面試題目”，你能回答上來嗎?如果想要了解更多的Java面試題相關內容，可以關注動力節點Java官網。