小貝_mysql表優化

mysql表優化

簡要:
一、分析表
二、檢查表
三、優化表
四、表數據導入
五、鎖表操作

一、分析表

1.1、命令:

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]TABLE tbl_name [, tbl_name] ...

1.2、作用:

本語句用於分析和存儲表的關鍵字分布，分析的結果將可以使得系統得到准確的統計信息，使得sql能夠生成正確的行計劃

1.3、不足:

在分析期間，會對表進行讀鎖操作

1.4、例子:

 

二、檢查表

2.1、命令

CHECK TABLE tbl_name [, tbl_name] ...[option] ...

option = {FOR UPGRADE | QUICK | FAST |MEDIUM | EXTENDED | CHANGED}

2.2、作用:

檢查一個或多個表是否有錯誤，便於排錯

2.3、不足:

在進行檢查表期間，需要給執行當前操作者更多的權限或優先級

2.4、例子:

 

 

三、優化表空間

3.1、命令

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]TABLE

tbl_name [, tbl_name] ...

3.2、作用

如果已經刪除了表的一大部分，或者已經對含有可變長度的表進行了很多的改動，則需要做定期的優化。這個命令可以將表中的空間碎片進行合並，進行回收。

3.3、不足:

依賴表的存儲引擎。只能針對innodb和myisam存儲引擎才可以使用

3.4、例子:

3.4.1、表結構以myisam為例

Create Table: CREATE TABLE `t` (

`id` int(10) unsigned DEFAULT NULL,

`name` char(3) DEFAULT NULL,

`age` char(5) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

3.4.2、查看表t大小

 

3.4.3、刪除表數據t

 

3.4.4、優化表

 

 

四、表數據導入

4.1、針對myisam非唯一索引

格式: alter table 表名 disable keys;

loading data

alter table 表名 enable keys

(備注: disable keys和enable keys用來打開或關閉myisam表中非唯一索引的更新，提高導入速度。)

4.1.1、例子:

創建表t

4.1.2、插入測試數據

4.1.3、導出數據並清空數據

4.1.4、未關閉索引

4.1.5、關閉索引

 

4.2、針對myisam唯一索引

格式: set unique_checks=0

loading data

setunique_checks=1

(備注: unique_checks是myisam表進行唯一索引的校驗，當其為0時，則是關閉，為1時，則為開啟。因此，可以在導入數據時，讓其關閉。導入成功後再進行改期，提高導入速度。)

4.2.1、例子:

4.2.2、插入測試數據

4.2.3、導出數據並清空數據

4.2.4、未關閉唯一索引校驗

4.2.5、關閉唯一索引校驗

 

4.3、針對innodb表

格式: set autocommit=0;

loadingdata

setautocommit=1;

導入數據前先執行setautocommit=0,關閉自動提交事務，在導入結束後執行set autocommit=1,恢復自動提交，從而提高導入效率。

五、鎖操作

5.1、myisam表讀、寫鎖

5.1.1、讀鎖定

5.1.1A、作用: 一旦進行讀鎖定，則當前表就不允許刪、插入、修改等操作

5.1.1B、例子:

a、測試表:

b、讀鎖定

c、當前終端執行刪除、修改、插入操作

d、另外的終端執行刪除操作

(出現等待狀態)

e、解鎖操作

（當前終端解鎖）

 

（新的終端才完成刪除操作）

5.1.2、myisam寫鎖定

5.1.2A、作用: 一旦進行寫鎖定，則只允許當前終端執行增刪改查操作，新的終端則不允許，必須等待第一個終端執行完，才可以

5.1.2B、例子:

第一個終端

第二個終端

解鎖：

第一個終端:

第二個終端:

 

5.1.3、表鎖定是否會影響視圖

5.1.3A、例子

對表t進行讀鎖

(執行讀鎖定的終端截圖)

（新終端）

(舊終端解鎖)

（新終端）

總結: a、myisam表的寫鎖定比讀鎖定更嚴格

b、一般情況下很少去顯式地去對表進行讀和寫鎖定。myisam會自動進行鎖定

c、原始表讀寫鎖定會影響視圖