手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表2009年04月7日的文章

你可能不知道的MySQL

冰山上的博客,更新速度之快,令我乍叹不已,我以为我算是勤劳的,想不到,他更是属于勤劳中的勤劳分子。。

以下内容又是COPY于他的网站,看来,他关注的部分内容还是比我高档,我仍然沉迷于一些小技巧,而忽略了一些大的东西。

心有多大,舞台有多大,其实这对技术人员来说非常重要,如果你只注重于一小块技术,那么在大的方面,你永远不会有进步。
扯远了。。不过以下一些小技巧我真的大部分都没有用过,所以我才会贴出来,让大家一起学习一下,如果知道的就直接跳过,当我没说。

内容如下:http://xinsync.xju.edu.cn/index.php/archives/4719

前言:

实验的数据表如下定义:

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大,所以第一个查询没有用索引,第二个才用到索引.

Tags: mysql, 技巧

atime、mtime与ctime

以下内容来自于两个博客的内容合并。
首先是来自于冰山上的博客的关于atime、mtime与ctime的介绍(我估计作者也是COPY来的,因为最后两段明显单词断行了,没有修改):

原文:http://xinsync.xju.edu.cn/index.php/archives/4683

atime 访问时间(access time):访问时间是文件最后一次被读取的时间。因此阅读一个文件会更新它的访问时间,而它的改变时间并没有变化(有关文件状态的信息没有被改变),它的修改时间也同样没有变化(文件内容本身没有被改变);

mtime 修改时间(modification time):文件内容最后被修改的时间。如 echo “Hello” >myfile ,则myfile的mtime被改变,同时ctime和atime也被改变;

ctime 改变时间(change time):文件状态(status)最后被改变的时间。如 chmod a+w myfile ,则myfile的ctime被改变,atime和mtime都不变;

参考:

st_atime
Time when file data was last accessed. Changed by  the following   functions:   creat(),   mknod(),   pipe(), utime(2), and read(2).

st_mtime
Time when data was last modified. Changed by the  fol-lowing  functions:  creat(), mknod(), pipe(), utime(),and write(2).

st_ctime
Time when file status was last changed. Changed by the following   functions:   chmod(),   chown(),  creat(),link(2),  mknod(),  pipe(),  unlink(2),  utime(),  and write().

然后下面的内容又来自于知道分子的博客:http://hutuworm.blogspot.com/2009/03/blog-post_31.html

Linus 大神最近将一项名为 relatime 的特性设定为内核默认行为。(参阅:Matthew Garrett,Reducing disk use

UNIX 文件系统中的 ctime、mtime、atime 属性分别记录了文件的创建时戳、修改时戳和访问时戳。每当有进程创建、修改、访问某个文件时,文件系统就会保存相应的时戳。对于一般用户而言,文件系统自 动记录这些时戳所花费的时间根本不值一提,完全可以忽略不计。但对于某些特定用途的生产服务器,比如存储大量小图片文件的服务器(写少读多),若每次读取 一个文件都要修改该文件的 atime——想像中的单纯读取操作,实际上却是一读一写——如果这些服务器访问量还挺大,那么这个过程中的消耗则无疑值得斤斤计较、细细考究了。

以 往我们为了节省不必要的 atime 改写,常常采用的方法,是通过 mount 选项 noatime 挂载文件系统,完全摈弃该属性。但随之而来的问题是,某些依赖于 atime 属性的系统程序无法正常工作。而 relatime 补丁的改进之处在于,仅当 atime 比 ctime 或 mtime 更早,或者早于当前时间 24 小时以上,系统才会去修改 atime,由此就大幅度减少了 atime 改写操作,提升了文件系统性能。该项功能已在 Fedora 和 Ubuntu 去年的新版本中发布。

其 实,可以节省的地方还有很多。上周小算了一笔账,某个页面引用的 js 和 css 文件 Expire 时间为 900 秒,一部分图片的 Expire 时间为 12 小时,另一部分图片的 Expire 时间为 4 天——假设该页面内容不变、访问量不变,那么一年的相关静态文件总下载量为数万 GB;如果把这些静态文件的 Expire 时间统一调整为一个月,那么总下载量可以下降一个数量级,降到几千 GB;如果胆子大一点、步子快一点,统一调整为一年,那么总下载量还可以再降一个数量级,降到几百 GB。阿米豆腐,这样的估算结果真是令人咋舌。

这两天又看了三篇跟性能优化相关的报道。一篇报道是 Linux Magazine 采访 Theodore Ts'o,其中提到 Google 的工程师向 Ext4 文件系统开发者提交了一个取消日志补丁,以获得更高的性能,可以看出,这个功能对于 Google 非常重要。一篇报道是 Red Hat 官方新闻,说 RHEL 5.3 开始支持 Intel 的 QuickPath 内存技术,运行内存密集型的应用可以获得两倍性能提升——显然这得归功于 Nehalem CPU 的崭新架构和超强性能。另一篇报道是 Linux Kernel 2.6.24 到 2.6.29 横向测试,OpenSSL 在 2.6.29 内核上测得性能比前几个版本高出一倍:


无论如何,关注这些信息,稍微算一算,我们就知道随便从其中找一项出来优化,可以提升多少性能、节省多少资源。虽说咱不差钱,但能省干嘛不省呢?

Tags: atime, mtime, ctime, linux