mysql 优化
1、存储过程造数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
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/