第12章_数据库其它调优策略（1）https://developer.aliyun.com/article/1530705
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.i
以后，可以直接从temp_student表中查询学生名称、班级名称和班级班长，而不用每次都进行联合查
询。这样可以提高数据库的查询速度。
3.3 增加冗余字段
设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。
但是，合理地加入冗余字段可以提高查询速度。
表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。尤其在数据量大，而
且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。
这部分内容在《第11章_数据库的设计规范》章节中 反范式化小节 中具体展开讲解了。这里省略。
3.4 优化数据类型
改进表的设计时，可以考虑优化字段的数据类型。这个问题在大家刚从事开发时基本不算是问题。但是，随着你
的经验越来越丰富，参与的项目越来越大，数据量也越来越多的时候，你就不能只从系统稳定性的角度来思考问
题了，还要考虑到系统整体的稳定性和效率。此时，优先选择符合存储需要的最小的数据类型。
列的字段越大,建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少，在遍历时
所需要的IO次数也就越多，索引的性能也就越差。
具体来说:
情况1：对整数类型数据进行优化。
遇到整数类型的字段可以INT型 。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数
据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以
的。但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。
对非负的数据（如自增ID、整型IP）来说，要优先使用无符号整UNSIGNED来存储。因为无符号
相对于有符号，同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255，多
出一倍的存储空间。
情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。
跟文本类型数据相比，大整数往往占用更少的存储空间 ，因此，在存取和比对的时候，可以占用更少的
内存空间。所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。如：将IP地
址转换成整型数据。
情况3：避免使用TEXT、BLOB数据类型
MySQL内存临时表不支持TEXT、BLOE这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能
使用内存临时表，必须使用磁盘临时表进行。并且对于这种数据，MySQL还是要进行二次查询，会使SQL性能变
得很差,但是不是说一定不能使用这样的数据类型。
如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select*，而只需要取出必
要的列，不需要TEXT列的数据时不要对该列进行查询。
情况4：避免使用ENUM类型
修改ENUM值需要使用ALTER语句。
ENUM类型的ORDER BY操作效率低，需要额外操作。使用TINYINT来代替ENUM类型。
情况5：使用TIMESTAMP存储时间
TIMESTAMP存储的时间范围1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。TIMESTAMP使用4字节， DATETIME使用8个
字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。
情况6：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
1)非精准浮点: float,double
2)精准浮点: decimal
Decimal类型为精准浮点数，在计算时不会丢失精度,尤其是财务相关的金融类数据。占用空间由定义的宽度决
定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。
3.5 优化插入记录的速度
1.MyISAM引擎的表:
①禁用索引
对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引就会降
低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。禁用索引
的语句如下:
ALTER TABLE table_name DISABLE KEYS
重新开启索引的语句如下:
ALTER TABLE table_name ENABLE KEYS
若对于空表批量导入数据，则不需要进行此操作,因为MyISAM引擎的表是在导入数据之后才建立索引的。
②禁用唯一-性检查
插入数据时，MySQL会对插入的记录进行唯一性校验。 这种唯一性校验会降低插入记录的速度。 为了降低这种情
况对查询速度的影响，可以在插入记录之前禁用唯一性检查, 等到记录插入完毕后再开启。禁用唯一性检查的语
句如下:
SET UNIQUE_CHECKS=0 ;
开启唯一性检查的语句如下:
SET UNIQUE_CHECKS=1 ;
③使用批量插入
插入多条记录时，可以使用一条INSERT语句插入-条记录，也可以使用一条INSERT语句插入多条记录。插入一条
记录的INSERT语句情形如下:
insert into student values(1, 'zhangsan',18, 1); insert into student values(2, 'lisi',17,1); insert into student values(3, ' wangwu' ,17,1); insert into student values(4,' zhaoliu' ,19,1);
使用一条INSERT语句插入多条记录的情形如下: .
insert into student values (1, ' zhangsan',18, 1)， (2,'lisil, 17,1)， (3, ' wangwu' ,17,1)， (4, ' zhaoliu',19,1);
第二种速度更快
④使用LOAD DATA INFILE批量导入
当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为LOAD DATA INFILE语句导入数据的速
度比INSERT语句快。
2.InnoDB引擎的表:
①禁用唯一性检查
插入数据之前执行set unique_checks=0 来禁止对唯一索引的检查, 数据导入完成之后再运行
setunique_checks=1。这个和MyISAM引擎的使用方法一样。
②禁用外键检查
插入数据之前执行禁止对外键的检查,数据插入完成之后再恢复对外键的检查。禁用外键检查的语句如下:
SET foreign_key_checks=0;
恢复对外键的检查语句如下:
SET foreign_key_checks=1;
③禁止自动提交
插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交的语句如下:
set autocommit=0;
恢复自动提交的语句如下:
set autocommit=1 ;
3.6 使用非空约束
在设计字段的时候，如果业务允许，建议尽量使用非空约束
①进行比较和计算时,省去要对NULL值的字段判断是否为空的开销，提高存储效率。
②非空字段也容易创建索引。因为索引NULL列需要额外的空间来保存，所以要占用更多的空间。使用非空约
束,就可以节省存储空间(每个字段1个bit)。
3.7 分析表、检查表与优化表
MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布，检查表主要是检查表是否存在
错误，优化表 主要是消除删除或者更新造成的空间浪费。
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 TABL分析表的过程中，数据库系统会自动对表加一个 只读锁 。在分析期间，只能读取
表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能
作用于视图。
ANALYZE TABLE分析后的统计结果会反应cardinality的值，该值统计了表中某一键所在的列不重复
的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使
**用。**也就是索引列的cardinality的值与表中数据的总条数差距越大，即使查询的时候使用了该索引作为查
询条件，存储引擎实际查询的时候使用的概率就越小。下面通过例子来验证下。cardinality可以通过
SHOW INDEX FROM 表名查看。
所以可以起到一个刷新的作用
2.检查表
MySQL中可以使CHECK TABL语句来检查表。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%一致的，但
是花的时间较长。 - option只对MyISAM类型的表有效，对InnoDB类型的表无效。比如：
该语句对于检查的表可能会产生多行信息。最后一行有一个状态的 Msg_type 值，Msg_text 通常为 OK。
如果得到的不是 OK，通常要对其进行修复；是 OK 说明表已经是最新的了。表已经是最新的，意味着存
储引擎对这张表不必进行检查
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关键字的意义和分析表相同，都是指定不写入二进制日志。
执行完毕,Msg_text显示
numysql.SYS_APP_USER’, ‘optimize’, ‘note’, ‘Table does not support optimize, doing recreate + analyze instead
原因是我服务器上的MySQL是InnoDB存储引擎。
到底优化了没有呢？看官网！
https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html
在MyISAM中，是先分析这张表，然后会整理相关的MySQL datafile，之后回收未使用的空间；在InnoDB
中，回收空间是简单通过Alter table进行整理空间。在优化期间，MySQL会创建一个临时表，优化完成之
后会删除原始表，然后会将临时表rename成为原始表。
说明： 在多数的设置中，根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量的更
新，也不需要经常运行， 每周一次 或 每月一次 即可，并且只需要对 特定的表 运行。
方式2:使用mysqlcheck命令
mysqlcheck -o DatabaseName TableName -u root -p
mysqlcheck是Linux中的rompt, -o是代表optimize。
举例:优化所有的表
mysqlcheck -o DatabaseName -u root -p
#或
mysqlcheck -o --all-databases -u root -p
3.8 小结
上述这些方法都是有利有弊的。比如：
- 修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围；
- 增加冗余字段的时候，不要忘了确保数据一致性；
- 把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维的成本。
因此，你一定要结合实际的业务需求进行权衡。
4.大表优化
4.1 限定查询的范围
禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制
在一个月的范围内；
4.2 读/写分离
经典的数据库拆分方案，主库负责写，从库负责读。
- 一主一从模式
- 双主双从模式
4.3 垂直拆分
当数据量级达到 千万级 以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，
减少对单一数据库服务器的访问压力
垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区
可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起 JOIN 操作。此外，垂直拆分会让事务
变得更加复杂。
4.4 水平拆分
水平拆分能够支持非常大的数据量存储，应用端改造也少,但分片事务难以解决，跨节点Join性能较差，逻辑复
杂。《Java工程师修炼之道》 的作者推荐水平拆分能够支持非常大的数据量存储，应用端改造也少,但分片事务难以解决，跨节点Join性能较差，逻辑复
杂。《Java工程师修炼之道》 的作者推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复
杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片,尽量选择
客户端分片架构，这样可以减少- -次和中间件的网络1/0。一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片,尽量选择
客户端分片架构，这样可以减少一次和中间件的网络1/0。
下面补充一下数据库分片的两种常见方案：
客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的
Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在
谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。
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语句的超时时间
5.2 创建全局通用表空间
尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复
杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片,尽量选择
客户端分片架构，这样可以减少- -次和中间件的网络1/0。一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片,尽量选择
客户端分片架构，这样可以减少一次和中间件的网络1/0。
下面补充一下数据库分片的两种常见方案：
客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的
Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在
谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。
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语句的超时时间
