优化数据库结构

MySQL学习大纲

---

一个好的数据库设计方案对于数据库的性能尝尝会起到事倍功半的效果，合理的数据库结构不仅使数据库占用更小的磁盘空间，而且使查询速度更快。数据库结构的设计需要考虑数据冗余、查询和更新速度、字段的数据类型是否合理等多方面的内容！

---

1、拆分表：冷热数据分离

• 1.拆分表的思路是，把1个包含很多字段的表拆分成2个或者多个相对较小的表,这样做的原因是:这些表中某些字段的操作频率很高（热数据），经常要进行查询或者更新操作，而另外一些字段的使用频率却很低（冷数据），冷热数据分离，可以减小表的宽度。如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源
• 2.MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。表越宽，把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的IO。冷热数据分离的目的是：①减少磁盘IO，保证热数据的内存缓存命中率。②更有效的利用缓存，避免读入无用的冷数据

---

2、增加中间表：非频繁更新类型的表适用

• 1.对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率
• 2.首先，分析经常联合查询表中的字段；然后，使用这些字段建立一个中间表，并将原来联合查询的表的数据插入中间表中；最后使用中间表来进行查询

---

3、增加冗余字段

• 1.设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度
• 2.表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。尤其在数据量大，而且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。

---

4、优化数据类型：

4.1.概述：

• 1.改进表的设计时，可以考虑优化字段的数据类型。这个问题在大家刚从事开发时基本不算是问题。但是，随着你的经验越来越丰富，参与的项目越来越大，数据量也越来越多的时候，你就不能只从系统稳定性的角度来思考问题了，还要考虑到系统整体的稳定性和效率。此时，优先选择符合存储需要的最小的数据类型。
• 2.列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差

---

4.2.案例分析：

a.对整数类型进行优化

• 1.遇到整数类型的字段可以用INT型。这样做的理由是，INT型数据有足够大的取值范围，不用担心数据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以的。但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率
• 2.对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型UNSIGNED来存储。因为无符号相对于有符号，同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255，多出一倍的存储空间。

b.既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型

• 1.跟文本类型数据相比，大整数往往占用更少的存储空间，因此，在存取和比对的时候，可以占用更少的内存空间。所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。如：将IP地址转换成整型数据

c.避免使用TEXT、BLOB数据类型：

• 1.MySQL内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。并且对于这种数据，MySQL还是要进行二次查询，会使SQL性能变得很差，但不是说一定不能使用这样的数据类型
• 2.如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select*，而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询

d.避免使用ENUM(枚举)类型:

• 1.修改ENUM值需要使用ALTER语句
• 2.ENUM类型的ORDER BY操作效率低，需要额外操作。使用TINYINT来代替ENUM类型

e.使用TIMESTAMP存储时间

• TIMESTAMP 存储的时间范围1970-01-01 00:00:01~2038-01-19-03:14:07
• TIMESTAMP使用4字节，DATETIME使用8个字节，同时 TIMESTAMP具有自动赋值以及自动更新的特性

f.用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

• 非精准浮点：float,double
• 精准浮点：decimal（不会丢失精度）

---

5、优化插入记录的速度

5.1.概述：

• 插入记录时，影响插入的速度主要是索引，唯一性校验，一次性插入记录条数等。根据这些情况可以分别进行优化，我们以不同的存储引擎来进行分析：

---

5.2.MyIDAM引擎

a.禁用索引

b.禁用唯一性检查

c.使用批量插入

d.使用LOAD DATA INFILE批量导入

---

5.2 InnoDB引擎

a.禁用唯一性检查

b.禁用外键检查

c.禁止自动提交

---

6、使用非空约束

• 1.在设计字段的时候，如果业务允许，建议尽量使用非空约束。这样做的好处是:
  • 进行比较和计算时，省去要对NULL值的字段判断是否为空的开销，提高存储效率
  • 非空字段也容易创建索引。因为索引NULL列需要额外的空间来保存，所以要占用更多的空间。使用非空约束，就可以节省存储空间（每个字段1个bit）

---

7、分析表、检查表与优化表

7.1.概述：

• 1.MySQL提供了分析表、检查表和优化表的语句
• 2.分析表主要是分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新造成的空间浪费

---

7.2.分析表：

a.说明：

b.案例：

• 1.创建数据库：
  
• 2.为了插入1000条数据，创建存储函数和存储过程：
  
  
• 3.调用存储函数，实现数据插入：
  
• 4.验证数据：
  

c.分析：

• 1.如下可以看到主id的
  

---

7.3.

---

8、大表优化

8.1 限定查询的范围

• 禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

---

资料