當面試官讓你聊聊MySQL數(shù)據(jù)庫性能優(yōu)化，你還是只能回答優(yōu)化sql，建索引嗎？
讓我們看看還可以從哪些方面聊聊吧。其實你還可以從網(wǎng)速、數(shù)據(jù)量、數(shù)據(jù)庫日志、內存等問題、硬件配置，當前占用資源、硬盤碎片或索引碎片等等諸多方面分析聊聊。

一、show table status from db_name【 如何判斷是否有碎片？】

1、碎片查詢分析

show status可以查看MySQL服務器運行狀態(tài)值。而show table status from db_name可以查詢db_name 數(shù)據(jù)庫里所有表的信息，是否有索引碎片等。MySQL 的表空間設置和優(yōu)化策略有什么？

這個命令中 Data_free 字段，如果該字段不為 0，則產生了數(shù)據(jù)碎片。

show table status 查詢結果中各列字段含義：

• Name 表名稱
• Engine 表的存儲引擎，對于分區(qū)表，Engine 顯示所有分區(qū)使用的存儲引擎的名稱。
• Version 版本；此列未使用。在 MySQL 8.0 中刪除 .frm 文件后，此列現(xiàn)在報告硬編碼值 10，這是 MySQL 5.7 中使用的最后一個 .frm 文件版本。
• Row_format 行格式。對于MyISAM引擎，這可能是Dynamic，F(xiàn)ixed或Compressed。動態(tài)行的行長度可變，例如Varchar或Blob類型字段。固定行是指行長度不變，例如Char和Integer類型字段。
• Rows 行數(shù)。一些存儲引擎，例如 MyISAM，存儲確切的計數(shù)。對于其他存儲引擎，例如 InnoDB，這個值是一個近似值，可能與實際值相差 40% 到 50%。在這種情況下，請使用 SELECT COUNT(*) 獲得準確的計數(shù)。
  對于 INFORMATION_SCHEMA 表，Rows 值為 NULL。
  對于 InnoDB 表，行數(shù)只是 SQL 優(yōu)化中使用的粗略估計。 （如果 InnoDB 表是分區(qū)的，這也是如此。）
• Avg_row_length 平均每行包括的字節(jié)數(shù)
• Data_length 對于 MyISAM，Data_length 是數(shù)據(jù)文件的長度，以字節(jié)為單位。
  對于 InnoDB，Data_length 是為聚集索引分配的近似空間量，以字節(jié)為單位。具體來說，它是聚集索引大?。ㄒ皂撁鏋閱挝唬┏艘?InnoDB 頁面大小。
  對于 MyISAM，Max_data_length 是數(shù)據(jù)文件的最大長度。給定使用的數(shù)據(jù)指針大小，這是可以存儲在表中的數(shù)據(jù)總字節(jié)數(shù)。
• Max_data_length 對于 MyISAM，Max_data_length 是數(shù)據(jù)文件的最大長度。給定使用的數(shù)據(jù)指針大小，這是可以存儲在表中的數(shù)據(jù)總字節(jié)數(shù)。
  未用于 InnoDB。
• Index_length 對于 MyISAM，Index_length 是索引文件的長度，以字節(jié)為單位。
  對于 InnoDB，Index_length 是為非聚集索引分配的近似空間量，以字節(jié)為單位。具體來說，它是非聚集索引大小的總和，以頁面為單位，乘以 InnoDB 頁面大小。
• Data_free 已分配但未使用的字節(jié)數(shù)。
  InnoDB 表報告該表所屬表空間的可用空間。對于位于共享表空間中的表，這是共享表空間的空閑空間。如果您使用多個表空間并且該表有自己的表空間，則可用空間僅用于該表?？臻e空間是指完全空閑范圍中的字節(jié)數(shù)減去安全裕度。即使可用空間顯示為 0，只要不需要分配新的擴展區(qū)，就可以插入行。

2、產生碎片的原因

• 經(jīng)常進行 delete 操作
  經(jīng)常進行 delete 操作，產生空白空間，久而久之就產生了碎片；

• update 更新
  update 更新可變長度的字段(例如 varchar 類型)，將長的字符串更新成短的。之前存儲的內容長，后來存儲是短的，即使后來插入新數(shù)據(jù)，那么有一些空白區(qū)域還是沒能有效利用的。

由于碎片空間是不連續(xù)的，導致這些空間不能充分被利用；由于碎片的存在，導致數(shù)據(jù)庫的磁盤 I/O 操作變成離散隨機讀寫，加重了磁盤 I/O 的負擔。

二、MySQL 的表空間設置和優(yōu)化策略【 如何清理碎片？】

如何清理碎片，加快索引掃描，給表空間瘦身呢，常用的方法可以從以下幾個方面進行處理：

1、innodb_file_per_table 參數(shù)設置為 ON（基本上是默認打開的）

• 打開該參數(shù)，創(chuàng)建表則會分2個文件，.frm存放元數(shù)據(jù)，.ibd存放表數(shù)據(jù)(表初始大小是98304KB)；
• 關掉該參數(shù)off之后，創(chuàng)建表，只會生成.frm元數(shù)據(jù)文件，數(shù)據(jù)會存放到系統(tǒng)表空間，這樣會不利于后期管理，系統(tǒng)表空間會逐漸膨脹，導致影響性能。

2、定期執(zhí)行 OPTIMIZE TABLE tableName;

MyISAM：

optimize table 表名；（OPTIMIZE 可以整理數(shù)據(jù)文件,并重排索引）

對于MYISAM表，OPTIMIZE TABLE 的工作原理：
如果表已刪除或分隔行，就修復該表。
如果索引頁沒有排序，就排序它們。
如果表的統(tǒng)計信息不是最新的(而且修復不能通過對索引進行排序)，就更新它們。

Innodb：

optimize table 表名；
對于InnoDB的表，OPTIMIZE TABLE 的工作原理如下
對于InnoDB表， OPTIMIZE TABLE映射到ALTER TABLE … FORCE（或者這樣翻譯：在InnoDB表中等價 ALTER TABLE … FORCE），它重建表以更新索引統(tǒng)計信息并釋放聚簇索引中未使用的空間。

3、定期執(zhí)行 ALTER TABLE tableName ENGINE = InnoDB;

Innodb：
方法一：ALTER TABLE tablename ENGINE=InnoDB；(重建表存儲引擎，重新組織數(shù)據(jù))
或：ALTER TABLE tbl_name FORCE;

方法二：進行一次數(shù)據(jù)的導入導出
使用 mysqldump 將表轉儲到文本文件，刪除表，然后從轉儲文件重新加載

注意：第optimize、ALTER TABLE是高危操作，會鎖表，影響業(yè)務，建議在低峰期操作。