mysql索引需要了解的几个注意

板子之前做过2年web开发培训(入门?),获得挺多学生好评,这是蛮有成就感的一件事,准备花点时间根据当时的一些备课内容整理出一系列文章出来,希望能给更多人带来帮助,这是系列文章的第一篇

注:科普文章一篇,大牛绕道

索引是做什么的?

索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。
表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。 继续阅读

特定场景下,别忘了optimize table

前几日,从日志发现个请求相应异常慢,看了下,是个用mysql实现的一个任务队列,登录上去

show processlist

发现某语句执行异常慢,挺奇怪,一个常年只有几百行的表,怎么会这么慢呢

show table status;
t_task        | MyISAM |      10 | Dynamic    |      84 |            346 |    19547784 |    281474976710655 |       705536 |  19518652 |           NULL | 2011-11-20 09:38:20 | 2012-03-15 16:30:03 | NULL       | utf8_general_ci |     NULL |
恍然大悟
346行数据,却有19M了
optimize table t_task
ok,问题解决
原因分析:可变长度表,大量的插入删除,数据文件碎片太多,导致异常
结论:可变长度,存在大量插入删除修改操作的表,别忘了时常optimize一下,也可酌情考虑改成定长的(ps:对于utf8来说,为了保证定长,char(1)即使是插入1个数字,也是3字节哦)
看下手册怎么说的optimize table吧,中文版:

13.5.2.5. OPTIMIZE TABLE语法

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE <em>tbl_name</em> [, <em>tbl_name</em>] ...

如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。

在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次即可,只对特定的表运行。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

对于MyISAM表,OPTIMIZE TABLE按如下方式操作:

1. 如果表已经删除或分解了行,则修复表。

2. 如果未对索引页进行分类,则进行分类。

3. 如果表的统计数据没有更新(并且通过对索引进行分类不能实现修复),则进行更新。

对于BDB表,OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。请参见13.5.2.1节,“ANALYZE TABLE语法”

使用—skip-new或—safe-mode选项可以启动mysqld。通过启动mysqld,您可以使OPTIMIZE TABLE对其它表类型起作用。

注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。

OPTIMIZE TABLE语句被写入到二进制日志中,除非使用了自选的NO_WRITE_TO_BINLOG关键词(或其别名LOCAL)。已经这么做了,因此,用于MySQL服务器的OPTIMIZE TABLE命令的作用相当于一个复制主服务器,在默认情况下,这些命令将被复制到复制从属服务器中。