mysql 优化

 

 1、存储过程造数据

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_test_data`(`n` int)
begin
    declare i int;
        DECLARE max_id bigint;
    set i=1;
        select max(dmId) into @max_id from wt_wallet  ;
        set autocommit = 0;
    while i<=n do
                #生成 供应商 应收 对账 提现 钱包
                INSERT INTO `ppcs_test_v1`.`wt_wallet` (`dmId`, `owner_type`, `owner_id`, `owner_name`, `item_id`, `item_name`, `item_amount`, `payer_type`, `payer_id`, `create_time`, `create_by`, `update_time`, `update_by`) 
                    VALUES (@max_id+(i-1)*3+1 , '1', 1718683099023360 + i, concat('供应商', i), '1', '应收', RAND()*10000, '2', 1707710161422336, now()*1000, NULL, NULL, NULL);
                INSERT INTO `ppcs_test_v1`.`wt_wallet` (`dmId`, `owner_type`, `owner_id`, `owner_name`, `item_id`, `item_name`, `item_amount`, `payer_type`, `payer_id`, `create_time`, `create_by`, `update_time`, `update_by`) 
                    VALUES (@max_id+(i-1)*3+2, '1', 1718683099023360 + i, concat('供应商', i), '0', '对账中', RAND()*10000, '2', 1707710161422336, now()*1000, NULL, NULL, NULL);
                INSERT INTO `ppcs_test_v1`.`wt_wallet` (`dmId`, `owner_type`, `owner_id`, `owner_name`, `item_id`, `item_name`, `item_amount`, `payer_type`, `payer_id`, `create_time`, `create_by`, `update_time`, `update_by`) 
                    VALUES (@max_id+i*3, '1', 1718683099023360 + i, concat('供应商', i), '4', '提现', RAND()*10000, '2', 1707710161422336, now()*1000, NULL, NULL, NULL);
                #RAND()*10000生成10000以内的随机数
                if i%1000 = 0 then
                    COMMIT;
                end if;
        set i=i+1;
    end while;
        set autocommit = 1;
end

 

2、碎片整理：optimize table table_name;

 

3、分析表 analyze table table_name;

 

4、explain select sql 语句

 

5、procedure analyse() 优化表结构

 

6、 delete update 后面可以加上 limit 限制，防止 批量删除出错 或 误删除。

而且数据量 大的时候可能 导致 未走索引， 网友说是因为“MYSQL会自动判断最优的执行计划，可能你不加limit的时候数据量增到导致MYSQL判断你索引不是最优计划，从而不走，FORCE INDEX 可以走你的想法”

 

EXPLAIN update wt_bldgl_income t force index(idx_wt_bldgl_income_rel_time) set t.stat = 3 ,t.update_time = 1467365030000 where t.release_time < 1467540146000 and t.stat = 1 ;

EXPLAIN update wt_bldgl_income t set t.stat = 3 ,t.update_time = 1467365030000 where t.release_time < 1467540146000 and t.stat = 1;

EXPLAIN update wt_bldgl_income t set t.stat = 3 ,t.update_time = 1467365030000 where t.release_time < 1467540146000 and t.stat = 1 limit 1000  ;

 

 

 

 

 

7、查看表索引信息

show INDEX FROM table_name;

 

 8、 清空 二进制log

　　reset MASTER;

 

9、字段类型不匹配导致 不走索引

表结构如下

CREATE TABLE `tb_apps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_id` varchar(128) DEFAULT '',
  `app_name` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_app_update_time` (`updated_at`),
  KEY `idx_app_aid_country_ctime` (`app_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

 

 查询语句：

select app_id, app_name from tb_apps t where t.app_id=895670960;

解释执行后发现未走索引

 

sql改成

EXPLAIN  select app_id, app_name from tb_apps t where t.app_id='895670960';

 

这次走索引了

 

 

 

 

 

 

 

 

参考资料：

1、MySQL数据表碎片整理 http://www.365mini.com/page/mysql-optimize-table.htm

2、MySQL定期分析检查与优化表 http://www.cnblogs.com/littlehb/archive/2013/05/08/3067175.html

3、 mysql优化Analyze Table http://blog.csdn.net/alongken2005/article/details/6394016

4、MYSQL explain详解 http://blog.csdn.net/zhuxineli/article/details/14455029

5、mysql删除大表更快的drop table办法

6、mysql 性能优化方向 http://www.cnblogs.com/AloneSword/p/3207697.html

7、MySQL缓存的查询和清除命令使用详解 http://www.jb51.net/article/75955.htm

8、MySQL的优化点总结---通过计算多种状态的百分比看MySQL的性能情况 http://www.tuicool.com/articles/zAnaIvM

9、 mysql的缓存机制 http://blog.itpub.net/15480802/viewspace-755582/