发现采集已达十多万条数据,然后发现在里面找到某条件的数据时,运行慢了不少,有的一个查询竟然达到3秒了。

        于是网上搜索‘MySQL优化’,简单总结有如下:

一、选择适当的存储引擎

        MySQL的存储引擎有两种,MyISAM和InnoDB。MyISAM适合大量查询(select)的场合,但是对于大量的定操作不太好,容易导致表被锁。另外MyISAM对于SELECT COUNT(*)这类的计算很快。InnoDB则对于写操作比较多时适用比较好,和MyISAM各有利弊吧。

二、优化表字段的数据类型

        一个原则,在满足的情况下,数据类型的存储占用空间越小越好,即尽量选择简单、适合的类型。比如存在时间就不要用varchar,而可用int(10),int(10)比datetime还好。而在明知列数不多的情况下,主键id就不要用int了,而要用MEDIUMINT或SMALLINT,或者是更小的TINYINT,如果满足需求的话。

三、将要搜索的字段添加索引
        这点非常重要!在数据量不多的时候不太明显,但数据一多,像我这次遇到的,十多万行里找某字段符合某条件时竟然用了3秒。核查发现此字段没有索引,而由于它既不是主键也不唯一,所以就添加为index索引,结果缩短至<0.00秒,快300倍以上。。。

        一般MySQL的索引有三种, Primary key是主键,not null且唯一,一个表只有一个primary key。Unique key相对于主键是外键,特点是唯一。第三种就是普通的key了,也就是index,可以说Unique是一种特殊的index。这三种类型的字段用来搜索的where最合适。

四、尽量不使用select *

        select *是将符合条件的表中所有数据都查出,如果我们只需要某些字段,当然要比select某些字段的搜索要耗时了。

五、短字符串尽量使用ENUM而不是VARCHAR

        ENUM是紧凑的类型,实际保存的是TINYINT,但其外表上显示为字符串。这样一来,用这个字段来保存一些短字符串列表将会相当的完美。例如,性别、民族、部门和状态之类的这些字段的取值是有限而且固定的,这时,你应该使用 ENUM 而不是 VARCHAR。
六、尽可能的使用 NOT NULL:

        除非一定要用NULL,否则尽量设置为NOT NULL,NULL需要额外空间,作比较等时会使程序更复杂。当然如果某情况要用就会吧。

七、类型为固定长度,表会更快

        类同于第二点。

        如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB等。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。

        固定长度的表会提高性能,是因为MySQL搜寻得会更快一些。这些固定的长度是很容易计算下一个数据的偏移量的,所以读取自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序先找到主键。


        并且,固定长度的表也更容易被缓存和重建。不过,副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用或不用,它都是要分配占用那么多空间的。

        使用“垂直分割”技术,你可以分割你的表成为两个一个是定长的,一个则是不定长的。

八、垂直分割数据表

        没用过,不过可以想象,网上找到的。

        “垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。

        例如:在User表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。

        另外,需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。

9、用EXPLAIN来SELECT 查询

        我还没用过,网上找到的。据说使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN的查询结果还会告诉你的索引主键被如何利用的,你的数据表是如何被搜索和排序的等等。