小貝_mysql表最佳化

標籤：資料庫   mysql   最佳化   sql   

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、原始表讀寫鎖定會影響視圖

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

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

小貝_mysql表最佳化