小貝_mysql表最佳化

標籤：可變   寫鎖   pac   enable   插入   default   效率   部分   upgrade   

mysql表最佳化 簡要:
       一、分析表
       二、檢查表
       三、最佳化表
       四、表資料匯入
       五、鎖表操作 一、分析表

1.1、命令:

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

1.2、作用:

本語句用於分析和儲存表的keyword分布。分析的結果將可以使得系統得到準確的統計資訊，使得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、原始表讀寫鎖定會影響視圖

The quieter you become，the more you are able to hear！

 

小貝_mysql表最佳化