Mysql使用总结

      毕业设计的程序主体编好了，运行少量数据没有任何问题，一旦数据量增加到22w，运行时间就长得令人发指。导师建议，程序中尽可能地减少与数据库交互的次数，尽可能地把工作丢给数据库完成。在这一思想的指导下，我三天大幅修改程序，部分代码几乎是翻新了一遍。总的来说，运行时间确实得到了很大的改善，把这三天的经验总结一下。

 

     1、update操作

          使用preparestatement以batch方式提交。某个操作的运行时间从12min缩减到82s。

    

      2、建立索引

           如果使用select from where的操作，一定要为where后的属性建立索引。否则在大表上进行此操作，时间非常长。如果用  

            show    processlist查看，发现query的状态长时间停留在sending data。所以建索引很重要。

 

      3、distinct

           最好不要用distinct操作，非常耗时。我最初的存储过程及代码里，都多次用到select count(distinct attribute)，使得query执

           行的时间非常长。网上有建议，如果语句为 select count(distinct attribute) from table_name where,可以优化为

           select count(distinct attribute) from (select attribute from table_name where ) as t， t是table_name的别名。

       

      4、存储过程

           游标申明后，没有打开时，不能使用select into操作，有解释说select into其实是一个隐式游标。所有我 只好把select into写进  

           了游标的循环里。等下要再试一试，在open cursor和fetch之间，写入select into，看是否能够成功。

          

           存储过程里，不支持数组，真的很不方便。我有200个变量要声明，只能一个一个地写，一个一个地赋值。从数据库设计的原理来

           说，一张表的属性不能太多，我某一张表创纪录地达到了122个属性。如果分割成若干个小表，从原理上也是可行的，但是要增加  

           很多select操作并且要跨越多表，既麻烦，而且耗时也会更长。因为这张表是算法执行中生成的临时表，所以属性多、不符合范式

           要求，也就没关系了。但至少我的经验证明，mysql一张表的属性个数<=122都是没有任何问题的。

    

      5、相同属性的统计

           算法中有这样的一个要求，统计2条tuple相同的属性个数，2条tuple位于同一个表，都有100个属性。我最初使用 select count

           (distinct att1)+ count(distinct att2)+...+count(distinct att100) from table_name where id=t_id1 or id=t_id2, 最初

           对这个语句还是很满意的，至少比较的功能都由sql完成。但一运行，就发现速度超慢无比，时间瓶颈在于distinct操作。本来想找一

           个distinct实现的原理，但mysql文档和网上似乎都没有。

           只好另外想办法解决，用了很愚蠢的方法。先select att1, att2,..., att100 into a_att1,a_att2,... a_att100 from    

           table_name  where id=t_id1，select att1, att2,..., att100 into b_att1,b_att2,... b_att100 from table_name 

           where id=t_id2。 然后使用了100个if(a_att1=b_att1)语句进行判断，并用一个int 变量记录相似的结果数。

           功能顺利实现了，时间也减少了很多很多。最初存储过程执行一次，需要5-7s，修改后只需要0.01-0.02s。 虽然方法愚蠢，而且

           需要声明很多临时变量，但总归机械的劳动带来了一点回报。

 

    暂且总结到这里，明天去找导师。