第12章数据库其它调优策略【2.索引及调优篇】【MySQL高级】3-阿里云开发者社区

3.3 增加冗余字段

设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。

表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。尤其在数据量大，而且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。

这部分内容在《第11章_数据库的设计规范》章节中反范式化小节中具体展开讲解了。这里省略。

3.4优化数据类型

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

具体来说:
情况1：对整数类型数据进行优化。
遇到整数类型的字段可以用 INT 型。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以的。但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。

对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型 UNSIGNED 来存储。因为无符号相对于有符号，同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255，多出一倍的存储空间。

情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。

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

MySQL内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。并且对于这种数据，Mysql还是要进行二次查询，会使SQL性能变得很差，但是不是说一定不能使用这样的数据类型。

如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select *，而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。

情况4∶避免使用ENUM类型

修改ENUM值需要使用ALTER语句。

ENUN类型的ORDER BY操作效率低，需要额外操作。使用TINYINT来代替ENUM类型。
情况5:使用TIMESTAMP存储时间

TIMESTAMP存储的时间范围1970-01-0100:00:01~ 2038-01-19-03:14:07。TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAN1P具有自动赋值以及自动更新的特性
情况6:用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

1)非精准浮点: float,double

2)精准浮点: decimal
Decimal类型为精准浮点数，在计算时不会丢失精度，尤其是财务相关的金融类数据。占用空间由定义的宽度决定，每4个字节可以存储3位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据

总之，遇到数据量大的项目时，一定要在充分了解业务需求的前提下，合理优化数据类型，这样才能充分发挥资源的效率。使系统达到最优

3.5 优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况可以分别进行优化。这里我们分为MyISAM存储引擎和InnoDB存储引擎来讲。

1. MyISAM引擎的表

① 禁用索引

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引就会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。禁用索引的语句如下:

ALTER TABLE table_name DISABLE KEYS;

重新开启索引的语句如下:

ALTER TABLE table_name ENABLE  KEYS;

若对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的

②禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检合，等到记录插入完毕后再开启。禁用唯一性检查的语句如下:

SET UNIQUE_GHECKS=0;

开启唯一性检查的语句如下:

SET UNIQUE_GHECKS=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,'lisi',17,1),
(3,'wangwu',17,1),
(4,'zhaoliu',19,1);

第2种情形的插入速度要比第1种情形快。

④ 使用LOAD DATA INFILE 批量导入

当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快。
2. InnoDB引擎的表：

① 禁用唯一性检查
插入数据之前执行 set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_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 TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能作用于视图。

ANALYZE TABLE分析后的统计结果会反应到 cardinality 的值，该值统计了表中某一键所在的列不重复的值的个数。**该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。**也就是索引列的cardinality的值与表中数据的总条数差距越大，即使查询的时候使用了该索引作为查询条件，存储引擎实际查询的时候使用的概率就越小。下面通过例子来验证下。cardinality可以通过SHOW INDEX FROM 表名查看。

下面我们举例说明，使用下面的语句创建一张user表。

演示

#08-数据库的其他优化策略
CREATE TABLE `user1` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `age` INT DEFAULT NULL,
  `sex` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;
#######
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END //
DELIMITER ;
###
DELIMITER //
CREATE PROCEDURE  insert_user( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO `user1` ( NAME,age,sex ) 
 VALUES ("atguigu",rand_num(1,20),"male");  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER;
##
CALL insert_user(1000);
SHOW INDEX FROM user1;
SELECT * FROM user1;
UPDATE user1 SET NAME = 'atguigu03' WHERE id = 3;
#分析表
ANALYZE TABLE user1;
#检查表
CHECK TABLE user1;
#优化表
CREATE TABLE t1(id INT,NAME VARCHAR(15)) ENGINE = MYISAM;
OPTIMIZE TABLE t1;
CREATE TABLE t2(id INT,NAME VARCHAR(15)) ENGINE = INNODB;
OPTIMIZE TABLE t2;
####
CREATE TABLESPACE atguigu1 ADD DATAFILE 'atguigu1.ibd' file_block_size=16k;
CREATE TABLE test(id INT,NAME VARCHAR(10)) ENGINE=INNODB DEFAULT CHARSET utf8mb4 TABLESPACE atguigu1;
ALTER TABLE test TABLESPACE atguigu1;
DROP TABLESPACE atguigu1;
DROP TABLE test;

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%一致的，但是花的时间较长。

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。即使对可变长度的行进行了大量的更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定的表运行。

举例：

1.新建一张表，使用存储过程往里面放入100W数据，或者更多一些，争取能够以兆的单位显示
2.查看服务器上数据文件的大小，文件目录是/var/1ib/mysq1/所在的数据库
3.删除二分之一的数据，然后再查看当前数据文件的大小，会发现此时大小是不变的
4.使用OPTIMIZE table表名；命令优化表
5.再查看当前数据文件的大小，会发现此时大小已经变化了，做了空间的回收

优化前：

优化后：

方式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 小结

上述这些方法都是有利有弊的。比如：

修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围；
增加冗余字段的时候，不要忘了确保数据一致性；
把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维的成本。

因此，你一定要结合实际的业务需求进行权衡。

第12章数据库其它调优策略【2.索引及调优篇】【MySQL高级】3

3.3 增加冗余字段

3.4优化数据类型

3.5 优化插入记录的速度

3.6 使用非空约束

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

1.分析表

2. 检查表

3. 优化表

3.8 小结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

第12章 数据库其它调优策略【2.索引及调优篇】【MySQL高级】3

3.3 增加冗余字段

3.4优化数据类型

3.5 优化插入记录的速度

3.6 使用非空约束

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

1.分析表

2. 检查表

3. 优化表

3.8 小结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

第12章数据库其它调优策略【2.索引及调优篇】【MySQL高级】3