你可能不知道的MySQL

时间:2016-04-04

  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语句横向显示不全折行看起来巨费劲,还要把数据和列对应起来)。

#p#副标题#e#

 

 

  二、“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表尤其有用。避免每插入一条记录系统更新一下索引。

#p#副标题#e#

 

 

  四、最快复制表结构方法

  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更少和很更好的性能。

#p#副标题#e#

 

 

  七、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被Oracle并购后的409个日日夜夜 下一条:手把手教你学会SQL Server镜像操作

相关文章

最新文章