SQL語句最佳化 (二) （53）

標籤：

接上一部分

(4)如果不是索引列的第一部分，如下例子:可見雖然在money上面建有複合索引，但是由於money不是索引的第一列，那麼在查詢中這個索引也不會被MySQL採用。

mysql> explain select * from sales2 where moneys=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

(5)如果like是以%開始，可見雖然在name上面建有索引，但是由於where條件中like的值的“%”在第一位了，那麼MySQL也會採用這個索引。

mysql> explain select * from company2 where name like‘%3’\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: company2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

(6)如果列類型是字串，但在查詢時把一個數值型常量賦值給了一個字元型的列名name，那麼雖然在name列上有索引，但是也沒有用到。

mysql> explain select * from company2 where name=294\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: company2
         type: ALL
possible_keys: ind_company2_name
          key:NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

 

  而下面的sql語句就可以正確使用索引

mysql> explain select * from company2 where name=‘294’\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: company2
         type: ref
possible_keys: ind_company2_name
          key:ind_company2_name
      key_len: 23
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

3 查看索引使用方式

  如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數。

  Handler_read_rnd_next的值高則意味著查詢運行低效，並且應該建立索引補救。

   mysql> show status like ‘Handler_read%‘;
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | Handler_read_first    | 0    |
  | Handler_read_key      | 5    |
  | Handler_read_next     | 0    |
  | Handler_read_prev     | 0    |
  | Handler_read_rnd      | 0    |
  | Handler_read_rnd_next| 2055  |
  +-----------------------+-------+
   6 rows in set (0.00 sec)

 

兩個簡單實用的最佳化方法

分析表的文法如下:(檢查一個或多個表是否有錯誤 )

mysql> CHECK TABLE tbl_name[,tbl_name] … [option] … option =
  { QUICK | FAST | MEDIUM | EXTENDED |CHANGED}

mysql> check tablesales;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text|
+--------------+-------+----------+----------+
| sakila.sales | check | status   | OK      |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)

最佳化表的文法格式：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]

如果已經刪除了表的一大部分，或者如果已經對含有可變長度行的表進行了很多的改動，則需要做定期最佳化。這個命令可以將表中的空間片段進行合并，但是此命令只對MyISAM、BDB和InnoDB表起作用。

mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text|
+--------------+----------+----------+----------+
| sakila.sales | optimize | status   | OK      |
+--------------+----------+----------+----------+
1 row in set (0.05 sec)

4 常用SQL的最佳化

1 大批量插入資料

    當用load命令匯入資料的時候，適當設定可以提高匯入的速度。

  對於MyISAM儲存引擎的表，可以通過以下方式快速的匯入大量的資料。

ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS

DISABLE KEYS 和ENABLE KEYS 用來開啟或關閉MyISAM表非唯一索引的更新，可以提高速度，注意：對InnoDB表無效。

 

沒有使用開啟或關閉MyISAM表非唯一索引：
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2 fieldsterminated by “,”;
Query OK,529056 rows affected (1 min 55.12 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0

使用開啟或關閉MyISAM表非唯一索引：
mysql> alter table film_test2 disablekeys;
Query OK,0 rows affected (0.0sec)
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2;
Query OK,529056 rows affected(6.34 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
mysql> alter table film_test2 enablekeys;
Query OK,0 rows affected (12.25sec)
以上對MyISAM表的資料匯入，但對於InnoDB表並不能提高匯入資料的效率

 

（1）針對於InnoDB類型表資料匯入的最佳化

因為InnoDB表的按照主鍵順序儲存的，所以將匯入的資料主鍵的順序排列，可以有效地提高匯入資料的效率。

使用test3.txt文本是按表film_test4主鍵儲存順序儲存的
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
使用test3.txt沒有任何順序的文本（效率慢了1.12倍）
mysql> load data infile ‘/home/mysql/film_test4.txt’into table film_test4;
Query OK, 1587168 rows affected (31.16 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

（2）關閉唯一性效驗可以提高匯入效率

在匯入資料前先執行set unique_checks=0，關閉唯一性效驗，在匯入結束後執行set unique_checks=1，恢複唯一性效驗，可以提高匯入效率。

當unique_checks=1時
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
當unique_checks=0時
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (19.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

（3）關閉自動認可可以提高匯入效率

  在匯入資料前先執行set autocommit=0，關閉自動認可事務，在匯入結束後執行set autocommit=1，恢複自動認可，可以提高匯入效率。

當autocommit=1時
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
當autocommit=0時
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (20.87 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

2 最佳化insert語句

盡量使用多個值表的insert語句，這樣可以大大縮短客戶與資料庫的串連、關閉等損耗。

可以使用insert delayed（馬上執行）語句得到更高的效率。

將索引檔案和資料檔案分別存放不同的磁碟上。

可以增加bulk_insert_buffer_size 變數值的方法來提高速度，但是只對MyISAM表使用

當從一個檔案中裝載一個表時，使用LOAD DATA INFILE。這個通常比使用很多insert語句要快20倍。

3 最佳化group by語句

如果查詢包含group by但使用者想要避免排序結果的損耗，則可以使用使用order by null來禁止排序：

  如下沒有使用order by null來禁止排序

mysql> explain select id,sum(moneys) from sales2 group by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using temporary;Using filesort
1 row in set (0.00 sec)

如下使用order by null的效果：

mysql> explain select id,sum(moneys) from sales2 group by id order by null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using temporary
1 row in set (0.00 sec)

 

SQL語句最佳化 (二) （53）