数据库优化完全指南

数据库优化主要有以下四个维度.
一般来讲,从上到下,成本由高到低,效果由低到高.

• 硬件
• 系统配置
• 数据库表结构
• SQL及索引

之所以讲效果是由低到高的原因是因为,在我看来很多时候,查询慢都是由于mysql内部的锁机制,从而导致了一定的阻塞,而阻塞又导致了高并发,从而导致数据库出现问题.

以下分别从四个层面讲下大概优化的思路.

硬件

1. 搞个Oracle啊
2. 搞成主从数据库,实现读写分离啊.

系统配置

1. TCP/IP最大连接数
2. Linux打开文件数
3. 关闭系统防火墙,它对于性能损耗也蛮大的.

数据库表结构

表的拆分

1. 水平拆分

表的水平拆分是为了解决单表过大的问题,水平拆分的每一个表的结构是一致的

常见的拆分方法:

1. 对ID进行取余运算.
2. 根据余数的不同,放到不同的表中

这种做法的问题:

• 跨分区表进行数据查询
• 统计以及后台报表
• 新插入数据的ID问题.

解决思路:

• 前后台表分开,前台注重效率,后台专注统计
• 维护一个专门的ID表.

2. 垂直拆分

所谓的垂直拆分,就是把原来一个具有很多字段的表拆分成多个表,这解决了表的宽度过宽问题.通常按照以下原则进行拆分:
1. 把不常用的字段放到一个表中
2. 把大字段独立放到一个表中
3. 把经常用的字段放到一个表中

选择合适的数据类型

有以下通用原则

1. 使用可以存下你数据的最小的数据类型
2. 使用简单的数据类型,如int要比varchar类型在mysql处理上简单
3. 尽可能的使用not null定义字段,这会让字段需要的储存空间更小,增加了一次IO可以读取的数量
4. 尽量少使用text类型,非用不可的时候最好考虑分表

其他的特殊字段的类型选择
1. 用unsigned int(10)来存储IP地址
2. 用unsigned int(10)来存储timestamp

SQL及索引

如何发现有问题的SQL

开启慢日志

如何分析SQL查询

使用explain 执行计划查看索引的使用情况

如何选择合适的列建立索引

1. 在where从句,group by从句,order by从句,on从句中出现的列
2. 索引字段越小越好
3. 离散度大的列放到复合索引的前面
4. 去除重复的索引,或者因业务逻辑变化不在用到的索引

如何查看已经建立的索引

show create table tb_name;