3. 优化数据库结构
3.1 拆分表：冷热数据分离
举例1： 会员members表存储会员登录认证信息，该表中有很多字段，如id、姓名、密码、地址、电话、个人描述字段。其中地址、电话、个人描述等字段并不常用，可以将这些不常用的字段分解出另一个表。将这个表取名叫members_detail，表中有member_id、address、telephone、description等字段。这样就把会员表分成了两个表，分别为members表和members_detail表。
创建这两个表的SQL语句如下：
CREATE TABLE members (
id INT ( 11 ) NOT NULL AUTO_INCREMENT,
username VARCHAR ( 50 ) DEFAULT NULL,
PASSWORD VARCHAR ( 50 ) DEFAULT NULL,
last_login_time datetime DEFAULT NULL,
last_login_ip VARCHAR ( 100 ) DEFAULT NULL,
PRIMARY KEY ( Id )
);
CREATE TABLE members_detail (
Member_id INT ( 11 ) NOT NULL DEFAULT 0,
address VARCHAR ( 255 ) DEFAULT NULL,
telephone VARCHAR ( 255 ) DEFAULT NULL,
description text
);
如果需要查询会员的基本信息或详细信息，那么可以用会员的id来查询。如果需要将会员的基本信息和详细信息同时显示，那么可以将members表和members_detail表进行联合查询，查询语句如下：
SELECT * FROM members LEFT JOIN members_detail on members.id =members_detail.member_id;
通过这种分解可以提高表的查询效率。对于字段很多且有些字段使用不频繁的表，可以通过这种分解的方式来优化数据库的性能。
3.2 增加中间表
举例1： 学生信息表和班级表的SQL语句如下：
CREATE TABLE `class` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`className` VARCHAR ( 30 ) DEFAULT NULL,
`address` VARCHAR ( 40 ) DEFAULT NULL,
`monitor` INT NULL,
PRIMARY KEY ( `id` )
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
CREATE TABLE `student` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL,
`name` VARCHAR ( 20 ) DEFAULT NULL,
`age` INT ( 3 ) DEFAULT NULL,
`classId` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` )
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
现在有一个模块需要经常查询带有学生名称（name）、学生所在班级名称（className）、学生班级班长（monitor）的学生信息。根据这种情况可以创建一个temp_student 表。temp_student表中存储学生名称（stu_name）、学生所在班级名称（className）和学生班级班长（monitor）信息。创建表的语句
如下：
CREATE TABLE `temp_student` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`stu_name` INT NOT NULL,
`className` VARCHAR ( 20 ) DEFAULT NULL,
`monitor` INT ( 3 ) DEFAULT NULL,
PRIMARY KEY ( `id` )
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
接下来，从学生信息表和班级表中查询相关信息存储到临时表中：
INSERT INTO temp_student ( stu_name, className, monitor ) SELECT
s.NAME,
c.className,
c.monitor
FROM
student AS s,
class AS c
WHERE
s.classId = c.id
3.3 增加冗余字段
设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。
表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。尤其在数据量大，而且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。
3.4 优化数据类型
情况1：对整数类型数据进行优化。
遇到整数类型的字段可以用 INT 型。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以的。但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型UNSIGNED 来存储。因为无符号相对于有符号，同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255，多出一倍的存储空间。
情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型
跟文本类型数据相比，大整数往往占用更少的存储空间，因此，在存取和比对的时候，可以占用更少的内存空间。所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。如：将IP地址转换成整型数据。
情况3：避免使用TEXT、BLOB数据类型
情况4：避免使用ENUM类型
情况5：使用TIMESTAMP存储时间
情况6：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
3.5 优化插入记录的速度
MyISAM引擎的表：
① 禁用索引
② 禁用唯一性检查
③ 使用批量插入
④ 使用LOAD DATA INFILE 批量导入
InnoDB引擎的表：
① 禁用唯一性检查
② 禁用外键检查
③ 禁止自动提交
3.6 使用非空约束
在设计字段的时候，如果业务允许，建议尽量使用非空约束
3.7 分析表、检查表与优化表
1. 分析表
MySQL中提供了ANALYZE TABLE语句分析表，ANALYZE TABLE语句的基本语法如下：
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…
默认的，MySQL服务会将 ANALYZE TABLE语句写到binlog中，以便在主从架构中，从服务能够同步数据。可以添加参数LOCAL 或者 NO_WRITE_TO_BINLOG取消将语句写到binlog中。
使用ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能作用于视图。
2. 检查表
MySQL中可以使用CHECK TABLE 语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。CHECK TABLE语句在执行过程中也会给表加上只读锁。
对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据。而且，CHECK TABLE也可以检查视
图是否有错误，比如在视图定义中被引用的表已不存在。该语句的基本语法如下：
CHECK TABLE tbl_name [, tbl_name] ... [option] ...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
其中，tbl_name是表名；option参数有5个取值，分别是QUICK、FAST、MEDIUM、EXTENDED和CHANGED。各个选项的意义分别是：
-
QUICK ：不扫描行，不检查错误的连接。
-
FAST ：只检查没有被正确关闭的表
-
CHANGED ：只检查上次检查后被更改的表和没有被正确关闭的表
-
MEDIUM ：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点
-
EXTENDED ：对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的，但是花的时间较长
3. 优化表
方式1：OPTIMIZE TABLE
MySQL中使用OPTIMIZE TABLE 语句来优化表。但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR 、BLOB 或TEXT 类型的字段。一个表使用了这些字段的数据类型，若已经删除了表的一大部分数据，或者已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
OPTIMIZE TABLE 语句对InnoDB和MyISAM类型的表都有效。该语句在执行过程中也会给表加上只读锁。
OPTILMIZE TABLE语句的基本语法如下：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
LOCAL | NO_WRITE_TO_BINLOG关键字的意义和分析表相同，都是指定不写入二进制日志。
在MyISAM中，是先分析这张表，然后会整理相关的MySQL datafile，之后回收未使用的空间；在InnoDB中，回收空间是简单通过Alter table进行整理空间。在优化期间，MySQL会创建一个临时表，优化完成之后会删除原始表，然后会将临时表rename成为原始表。
说明： 在多数的设置中，根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量的更
新，也不需要经常运行， 每周一次或每月一次即可，并且只需要对特定的表运行。
3.8 小结
上述这些方法都是有利有弊的。比如：
- 修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围；
- 增加冗余字段的时候，不要忘了确保数据一致性；
- 把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维的成本。
因此，你一定要结合实际的业务需求进行权衡。
4. 大表优化
4.1 限定查询的范围
禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
4.2 读/写分离
4.3 垂直拆分
当数据量级达到千万级以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力
垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起 JOIN 操作。此外，垂直拆分会让事务变得更加复杂。
4.4 水平拆分
5. 其它调优策略
5.1 服务器语句超时处理
在MySQL 8.0中可以设置服务器语句超时的限制，单位可以达到毫秒级别。当中断的执行语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。
设置服务器语句超时的限制，可以通过设置系统变量MAX_EXECUTION_TIME 来实现。默认情况下，MAX_EXECUTION_TIME的值为0，代表没有时间限制。 例如：
SET GLOBAL MAX_EXECUTION_TIME=2000;
SET SESSION MAX_EXECUTION_TIME=2000; #指定该会话中SELECT语句的超时时间
扫一扫
691
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
