mysql 5.7 innodb 预热_mysql innodb 数据预热

高负荷mysql数据库突然崩溃之后，在一段时间内，特别是高峰期，整个应用感觉非常卡！

这个时候就需要我们对mysql进行预热了！

因为mysql热点数据都在innodb buffer pool中，如果mysql突然关闭，buffer pool被清空！所有数据都需要从硬盘加载至内存中的buffer pool，当需要某个数据时，才从硬盘拿取,导致速度异常慢！这个时候，我们可以将一些数据手动加载至buffer pool，称为人为预热！

当然，还有一种方式，在mysql正常关闭的时候，将内存中的buffer pool备份值硬盘！在下次开启时，将该备份导入至buffer pool！这是5.6的快速预热特性！前提是正常关闭，如果是突然关闭或者kill -9关闭，就需要手动预热了！

简单讲一下几个版本的预热方式！

5.0

最简单的，只需要在information_schema.tables依次做一次count统计即可！

SELECT table_name FROM INFORMATION_SCHEMA.TABLES

select count(1) from table_name;

然后SHOW STATSU 查看buffer pool中free页，即Innodb_buffer_pool_pages_free参数是否减少！

5.1

对于5.1来说，5.0的方法已经不适用了！

当时我们还是可以结合information_schema.tables！用动态sql以及游标组装出select * from db.tables limit 1，写一个预热存储过程！(也可以通过shell/python脚本来写！)

提供一个存储预热存储过程！

DELIMITER $$

USE `mysql`$$

DROPPROCEDURE IF EXISTS `Proc_MySQL_Warmup`$$

CREATE DEFINER=`root`@`%` PROCEDURE `Proc_MySQL_Warmup`(

)

COMMENT '预热mysql存储过程'

BEGIN

#############################################################

#1.先从information_schema.tables中找出所有表

#2.利用游标进行获取

#3.动态语句组装sql

#4.依次执行select * from db.tables limit 1;

#############################################################

# 宣告游标变量

DECLARE p_c INTDEFAULT 0;

# 宣告db.table变量

DECLARE p_table VARCHAR(1024);

# 宣告游标

DECLARE cur1 CURSORFOR

SELECT CONCAT(TABLE_SCHEMA,".",table_name) FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE';

# 如果获得'02000'状态，则表示游标已经获取到最后一个,将p_c变量置为NULL

DECLARECONTINUE HANDLER FOR SQLSTATE '02000'SET p_c = NULL;

# 开启游标

OPEN cur1;

# 在开启循环之前，获取第一个值

FETCH cur1 INTO p_Table;

WHILE (p_c ISNOTNULL) DO

# 打印上一个游标值

# select p_Table;

SET @p_Table= p_Table;

# 进行动态语句执行

SET @SQL=CONCAT("select *  from   ",p_Table, " limit 1 ");

PREPARE smtm FROM @SQL;

EXECUTE smtm ;

DEALLOCATEPREPARE smtm;

FETCH cur1 INTO p_Table;

END WHILE;

END$$

DELIMITER ;

有的时候上面这种该方法不管用，还是得用select count(1) from table_name;

5.5

和5.1类似的操作

5.6

如果是正常关闭,可以使用5.6的快速预热备份！

你只需在my.cnf里，加入如下：

innodb_buffer_pool_dump_at_shutdown = 1

解释：在关闭时把热数据dump到本地磁盘。

innodb_buffer_pool_dump_now = 1

解释：采用手工方式把热数据dump到本地磁盘。

innodb_buffer_pool_load_at_startup = 1

解释：在启动时把热数据加载到内存。

innodb_buffer_pool_load_now = 1

解释：采用手工方式把热数据加载到内存。

在关闭MySQL时，会把内存中的热数据保存在磁盘里ib_buffer_pool文件中，位于数据目录下。

如果是异常关闭，就只能用5.1的手动预热方法了！

预热后，查看innodb_buffer_pool是不是变化较大

SHOW GLOBAL STATUS LIKE '%innodb_buffer%';

Innodb_buffer_pool_pages_data和Innodb_buffer_pool_pages_free是否变化较大！

MyISAM预热

有专用命令进行预热

LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

将t1的全部索引加入cache，t2出子叶以外的索引加入缓冲

还可以对分区进行缓冲

LOAD INDEX INTO CACHE pt PARTITION (p1, p3);

预热之后可以查看key_buffer_size中缓冲区使用情况

查看Key_blocks_unused和Key_blocks_used变化情况