Mysql的不常見功能

實驗的資料表如下定義：mysql> desc tbl_name;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| uid   | int(11)      | NO   |     | NULL    |       || sid   | mediumint(9) | NO   |     | NULL    |       || times | mediumint(9) | NO   |     | NULL    |       |+-------+--------------+------+-----+---------+-------+3 rows in set (0.00 sec)

儲存引擎是MyISAM，裡面有10,000條資料。
一、”\G”的作用

mysql> select * from tbl_name limit 1;+--------+--------+-------+| uid    | sid    | times |+--------+--------+-------+| 104460 | 291250 |    29 |+--------+--------+-------+1 row in set (0.00 sec)mysql> select * from tbl_name limit 1\G;*************************** 1. row ***************************  uid: 104460  sid: 291250times: 291 row in set (0.00 sec)

有時候，操作返回的列數非常多，螢幕不能一行顯示完，顯示折行，試試”\G”，把列資料逐行顯示（”\G”挽救了我，以前看explain語句橫向顯示不全折行看起來巨費勁，還要把資料和列對應起來）。

二、”Group by”的”隱形殺手”

mysql> explain select uid,sum(times) from tbl_name group by uid\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_name         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 10000        Extra: Using temporary; Using filesort1 row in set (0.00 sec)mysql> explain select uid,sum(times) from tbl_name group by uid order by null\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_name         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 10000        Extra: Using temporary1 row in set (0.00 sec)

預設情況下，Group by col會對col欄位進行排序，這就是為什麼第一語句裡面有Using filesort的原因，如果你不需要對col欄位進行排序，加上order by null吧，要快很多，因為filesort很慢的。

三、大批量資料插入

最高效的大批量插入資料的方法：

load data infile '/path/to/file' into table tbl_name;

如果沒有辦法先產生文字檔或者不想產生文字檔，可以一次插入多行：

insert into tbl_name values (1,2,3),(4,5,6),(7,8,9)...

注意一條sql語句的最大長度是有限制的。如果還不想這樣，可以試試MySQL的prepare，應該都會比硬生生的逐條插入要快許多。

如果資料表有索引，建議先暫時禁用索引：

alter table tbl_name disable keys;

插入完畢之後再啟用索引：

alter table tbl_name enable keys;

對MyISAM表尤其有用。避免每插入一條記錄系統更新一下索引。

四、最快複製表結構方法

mysql> create table clone_tbl select * from tbl_name limit 0;Query OK, 0 rows affected (0.08 sec)

只會複製表結構，索引不會複製，如果還要複製資料，把limit 0去掉即可。

五、加引號和不加引號區別

給資料表tbl_name添加索引：

mysql> create index uid on tbl_name(uid);

測試如下查詢：

mysql> explain select * from tbl_name where uid = '1081283900'\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_name         type: refpossible_keys: uid          key: uid      key_len: 4          ref: const         rows: 143        Extra:1 row in set (0.00 sec)

我們在整型欄位的值上加索引，是可以用到索引的，網上不少人誤傳在整型欄位上加引號無法使用索引。修改uid欄位類型為varchar(12):

mysql> alter table tbl_name change uid uid varchar(12) not null;

測試如下查詢：

mysql> explain select * from tbl_name where uid = 1081283900\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_name         type: ALLpossible_keys: uid          key: NULL      key_len: NULL          ref: NULL         rows: 10000        Extra: Using where1 row in set (0.00 sec)

我們在查詢值上不加索引，結果索引無法使用，注意安全。

六、首碼索引

有時候我們的表中有varchar(255)這樣的欄位，而且我們還要對該欄位建索引，一般沒有必要對整個欄位建索引，建立前8~12個字元的索引應該就夠了，很少有連續8~12個字元都相等的欄位。

為什嗎？更短的索引意味索引更小、佔用CPU時間更少、佔用記憶體更少、佔用IO更少和很更好的效能。

七、MySQL索引使用方式

MySQL在一個查詢中只能用到一個索引（5.0以後版本引入了index_merge合并索引，對某些特定的查詢可以用到多個索引，具體查考[中文]
[英文]），所以要根據查詢條件建立聯合索引，聯合索引只有第一位的欄位在查詢條件中能才能使用到。

如果MySQL認為不用索引比用索引更快的話，那麼就不會用索引。

mysql> create index times on tbl_name(times);Query OK, 10000 rows affected (0.10 sec)Records: 10000  Duplicates: 0  Warnings: 0mysql> explain select * from tbl_name where times > 20\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_name         type: ALLpossible_keys: times          key: NULL      key_len: NULL          ref: NULL         rows: 10000        Extra: Using where1 row in set (0.00 sec)mysql> explain select * from tbl_name where times > 200\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_name         type: rangepossible_keys: times          key: times      key_len: 3          ref: NULL         rows: 1599        Extra: Using where1 row in set (0.00 sec)

資料表中times欄位絕大多數都比20大，所以第一個查詢沒有用索引，第二個才用到索引。