更多了解Mysql
时间：2009-05-20 来源：yangzhigang
//本文从《冰山上的播客》中看到，进行学习。
//本文来源于 冰山上的播客 http://xinsync.xju.edu.cn
前言：
实验的数据表如下定义：
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: 291250
times: 29
1 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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using temporary; Using filesort
1 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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using temporary
1 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: ref
possible_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: ALL
possible_keys: uid
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using where
1 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: 0 mysql> explain select * from tbl_name where times > 20\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: ALL
possible_keys: times
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using where
1 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: range
possible_keys: times
key: times
key_len: 3
ref: NULL
rows: 1599
Extra: Using where
1 row in set (0.00 sec)数据表中times字段绝大多数都比20大，所以第一个查询没有用索引，第二个才用到索引.
前言：
实验的数据表如下定义：
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: 291250
times: 29
1 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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using temporary; Using filesort
1 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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using temporary
1 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: ref
possible_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: ALL
possible_keys: uid
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using where
1 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: 0 mysql> explain select * from tbl_name where times > 20\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: ALL
possible_keys: times
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using where
1 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: range
possible_keys: times
key: times
key_len: 3
ref: NULL
rows: 1599
Extra: Using where
1 row in set (0.00 sec)数据表中times字段绝大多数都比20大，所以第一个查询没有用索引，第二个才用到索引.
相关阅读 更多 +
