表分析与优化

(注意会造成锁表 生产环境慎用)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//查看索引信息  注意字段cardinality(“散列程度”),太小则可以认为索引基本失效
show index from tbl;
//分析 local 取消binlog写入
analyze [local] Table tbl;
//check table 检查表及索引是否同步更新或异常
check table tbl;

//Optimize Table 碎片整理,如果表经常更新或物理删除记录 可释放空间
optimize [local] table tbl

//修复表
repair table tbl

//同 mysqlcheck 命令

拆分提高表的访问效率

1
2
3
4
5
6
7
8
9
10
11
12

1. 垂直分表 (根据数据冷热常用与否)

缺点:冗余管理维护 和 多表join
优点:数据行小 ,一定的数据块能放更多数据,减少查询IO
2. 水平拆分(依据一列或多列) 综合考虑数据量的增长速度 hash桶散列思想分散数据均匀分布

a. 表很大 分割降低查询读的数据和索引页,索引层 提高查询速度
b. 表的数据具有天然独立性 比如地区、年份 、常用数据
c. 存放到不同介质
缺点:
a. 给应用增加复杂度,业务逻辑复制 ,涉及多表 union

逆规范化

1
2
3
4
5
6
7
8
9

1. 增加冗余列 避免查询是join操作
2. 增加派生列 经其他表数据计算而成 减少连接或避免使用集函数
3. 重新组表 (把大量用户经多个表查询热数据组合成一张新表,减少连接提高性能)
4. 分割表 (水平 垂直)
5. 完整性维护
a. 批处理 (实时要求不高情况)
b. 逻辑业务实现 (风险大且不易业务变更维护)
c. 触发器 (实时的 ,相应的逻辑在同一地方出现,易于维护)

使用中间表提高统计查询

数据量较大的生产环境

1
2
1.操作临时表和源表独立,不影响生产环境
2.可以更灵活的增加修改字段或索引达到提高统计效用