1.使用procedure analyse()语句查看表字段是否需要优化,此语句会显示出当前表每个字段保存数据的最大值、最小值、保存数据的最大长度和最小长度等,可以根据这些信息连更改字段的长度更合适为什么值,以及适合枚举的值有哪些:
select * from duck_cust procedure analyse()\G;
从上面的结果可以看到因为nalyse()语句查看表字段是否需要优化,此语句会显示出当前表每个字段保存数据的最大值、最小值、保存数据的最大长度和最小长度等,可以根据这些信息连更改字段的长度更合适为什么值,以及适合枚举的值有哪些:
select * from duck_cust procedure analyse()\G;
从上面的结果可以看到因为最大长度Max_length 和最小长度Min_length都是1,所以应该把cust_num字段的长度改为2:alter table duck_cust modify cust_num mediumint(2);
2.拆分表提高访问效率:
拆分表分垂直拆分和水平拆分。
垂直拆分的优缺点:分离查用字段和不常用字段,这样数据行少了可以减少I/o次数。缺点是要查询完整表需要join链表查询。
水平拆分的优缺点:再表很大的情况下,垂直拆分可以减少查表时的索引查询因此可以提高访问速度,一些数据常用,一些不常用的时候可以水平分表提高查询性能,比如历史数据账单,不需要经常查。缺点就是增加了应用端的复杂度,因为应用端需要连接很多个表才能查找。
拆分表要根据情况做拆分,比如表拆分后会不会比不拆分效果更高。
3.反规范设计:
规范的表设计程度并不是越高越好,因为规范越高,联表查询操作越高,而联表查询会降低数据库性能。有时在表中增加一个冗余字段避免联表操作会提高很大性能。
反规范设计有以下几种设计方式:
增加冗余列:在多个表中有相同的列,可以避免联表查询带来的性能降低。
增加派生列:增加的这个列是由其他表的数据通过计算得到的值,这样有助于避免联表查询和避免在查询中使用集合函数。
重组表:如果大多数的查询都涉及到两个表的数据,那将这两个表合并了,会是性能更好。
分隔表:分离不常用的列减少I/O的输出,和join,水平分隔可以减少索引的查询,提高访问速度,剔除不常访问的数据。
反规范设计需要维护有三种,一种是批量处理比如批量修改统计派生列的数据。第二种是应用逻辑,就是用事物保持数据的完整性。第三就是用触发器,就是数据已修改触发器就执行进行对应修改。
4.使用中间表:
当数据量特别大的时候,如果只需要表中一部分数据做统计的话,可以用一个中间表保存部分数据做数据分析,这样可以省去大表查询带来的性能降低。