mysql 索引碎片自动清理函数

一、mysql 索引
在使用一段时间之后，对于数据库的修改删除，以及索引等会产生碎片，导致查询数据效率变慢，
一般可以通过语句，检查表的状态，

SHOW TABLE STATUS LIKE '表名称' 

这里我们关注Data_free这个字段：这里的信息包括了已被删除行的空间，因此占用了一部分空间

二、何时进行清理
一般是在2个月之后，运行一次最合适。官方建议时间不要太短。

三、索引碎片自动清理
1、原理：
1）先通过information_schema.tables查询到tabel的名称列表
2）根据information_schema.tables的table个数，进行循环，每次取出第n个table
3）有些用户的mysql 对索引优化语句有限制，需要先将数据库的表存储引擎改为myisam
4）使用 OPTIMIZE TABLE 表名称，进行索引优化
5）最后将数据库的表存储引擎改回去原来的innodb
6）call PROC_OPTIMIZE_TABLE () ，执行存储过程
2、完整的代码如下：

--创建一个存储过程
create PROCEDURE PROC_OPTIMIZE_TABLE ()
BEGIN
        -- 定义变量 -- 
        declare i int default 0; 
		declare rown int default 0;
		declare l_table_name varchar(300);
		declare SQL_FOR_SELECT varchar(1000);
		-- 查询有多少个表格 数据库名称中输入自己的数据库名称-- 
		select count(*) as rown into rown from  information_schema.tables where table_schema='数据库名称' and table_type='base table';

    while rown > 0 do
		  set l_table_name = '';
			set i = i + 1;
			set @n = 0;
			-- 根据变量i，查询第i个table-- 
			select table_name into l_table_name
			from (
			select (@n := @n + 1) as id, table_name from information_schema.tables where table_schema='数据库名称' and table_type='base table' 
			) as x where id = i;
			-- 有些用户的mysql 对索引优化语句有限制，需要先将数据库的表存储引擎改为myisam-- 
			set SQL_FOR_SELECT = CONCAT("ALTER TABLE ",l_table_name," ENGINE='myisam' ;");   -- 拼接sql语句
      set @sql = SQL_FOR_SELECT;
			PREPARE stmt FROM @sql;         -- 预处理动态sql语句
      EXECUTE stmt ; 
			deallocate prepare stmt;      -- 释放prepare
			-- 索引优化-- 
			set SQL_FOR_SELECT = CONCAT("OPTIMIZE TABLE ",l_table_name,";");   -- 拼接sql语句
      set @sql = SQL_FOR_SELECT;
			PREPARE stmt FROM @sql;         -- 预处理动态sql语句
      EXECUTE stmt ;                        -- 执行sql语句
      deallocate prepare stmt;      -- 释放prepare
			-- 数据库的表存储引擎改回去原来的innodb-- 
			set SQL_FOR_SELECT = CONCAT("ALTER TABLE ",l_table_name," ENGINE='innodb' ;");   -- 拼接sql语句
      set @sql = SQL_FOR_SELECT;
			PREPARE stmt FROM @sql;         -- 预处理动态sql语句
      EXECUTE stmt ; 
			deallocate prepare stmt;      -- 释放prepare
			
			set rown = rown - 1;
		end while;

END

四、进阶
可以通过作业，设置定时运行，有需要的可以@本人信息讨论。