压测 mysql关闭连接_（全栈须知）8.MYSQL优化总结-CSDN博客

本文链接：https://blog.csdn.net/weixin_32050773/article/details/113515629

本文介绍了MySQL数据库的优化策略，包括表结构优化、SQL查询优化和系统配置优化，并提供了具体的实例，如创建测试数据、分析SQL性能、启用sql_cache等。此外，还详细阐述了压力测试的方法，如使用mysqlslap进行并发测试，分析测试结果，以评估和提升数据库性能。

摘要由CSDN通过智能技术生成

1、数据库表结构优化

a.选取最适用的字段属性、索引

根据实际情况：如限制varchar字段长度，设置定长的char；使用enum。

b.使用连接来代替子查询

根据实际情况：能够改写为join的尽量不要使用子查询。

//子查询/left join/left semi join

c.使用explain分析SQL问题

记录慢查询日志，逐条分析。

d.测试

创建测试表：

CREATE TABLE `goods` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,

`price` FLOAT(10,3) UNSIGNED NOT NULL DEFAULT '0.000' COMMENT '价格';

`info` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='商品表';

CREATE TABLE `goods_order_log` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`order_id` int(10) unsigned NOT NULL COMMENT '订单id',

`buy_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '购买时间',

`goods_id` int(10) unsigned NOT NULL COMMENT '订单id中：商品id',

`goods_num` int(10) unsigned NOT NULL COMMENT '订单id中：商品id的数量',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='商品购买流水表';

CREATE TABLE `goods_fav_log` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`user_id` int(10) unsigned NOT NULL COMMENT '搜藏的用户id',

`goods_id` int(10) unsigned NOT NULL COMMENT '商品id',

`buy_time` `fav_value` ENUM('1','0') NOT NULL DEFAULT '1' COMMENT '是否搜藏',

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`),

KEY `goods_id` (`goods_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT COMMENT='商品搜藏流水表'

填充测试数据的存储过程：

DELIMITER $$

CREATE PROCEDURE `insert_goods`(IN `c` INT UNSIGNED)

NO SQL

COMMENT '生成测试数据的存储过程'

BEGIN

declare i int default 0;

set autocommit = 0;

repeat

set i = i + 1;

insert into goods(name,price,info) values(concat(i,'_',rand_string(60)),floor(RAND()*100000)/100,rand_string(120));

until i = c end repeat;

set autocommit = 1;

END$$

DELIMITER $$

CREATE PROCEDURE `insert_goods_order_log`(IN `c` INT UNSIGNED)

NO SQL

COMMENT '生成订单流水的存储过程'

BEGIN

declare i int default 0;

set autocommit = 0;

set @basetime = '2016-01-01 00:00:00';

repeat

set i = i + 1;

set @orderid = concat(REPLACE(REPLACE(REPLACE(NOW(),'-',''),' ',''),':',''), '_',rand_string(6));

set @goodsnums = floor(RAND()*10) + 1;

set @buytime = from_unixtime(UNIX_TIMESTAMP(@basetime) + floor((UNIX_TIMESTAMP()-UNIX_TIMESTAMP(@basetime))*RAND()));

while @goodsnums>0 do

set @goodid = floor(RAND() * 10000) + 1;

insert into goods_order_log(order_id,buy_time,goods_id,goods_num) values(@orderid,@buytime,@goodid,RAND()*50);

set @goodsnums = @goodsnums - 1;

end while;

until i = c end repeat;

set autocommit = 1;

END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE `insert_goods_fav_log`(IN `c` INT UNSIGNED)

NO SQL

COMMENT '生成用户搜藏商品的存储过程'

BEGIN

declare i int default 0;

set autocommit = 0;

repeat

set i = i + 1;

set @userid = floor(RAND()*100000) + 1;

set @goodsid = floor(RAND()*100000) + 1;

select @userid,@goodsid,FLOOR(RAND()*2);

insert into goods_fav_log(user_id,goods_id,fav_value) values(@userid,@goodsid,concat(FLOOR(RAND()*2),''));

until i = c end repeat;

set autocommit = 1;

END$$

DELIMITER ;

call insert_goods(10000); //商品表,1w

call insert_goods_order_log(1000000); //订单详情表,100w

call insert_goods_fav_log(100000); //商品收藏表,10w

//(部分商品)区间销量排行

select goods_id,sum(goods_num) as goods_nums from goods_order_log where goods_id between 1 and 199 group by goods_id order by goods_nums desc limit 0,20;

##销售额排行

//子查询

select a.id,b.goods_nums,a.price,(b.goods_nums*a.price) as goods_sales from goods as a

inner join (select goods_id,sum(goods_num) as goods_nums from goods_order_log where goods_id between 1 and 1999 group by goods_id ) as b on a.id=b.goods_id

order by goods_sales desc limit 0,20;

//商品收藏排行

select goods_id,count(id) as fav_nums from goods_fav_log where fav_value='1' group by goods_id order by fav_nums desc limit 0,20;

2、规范使用

真正好的优化，是需要建立一套施行的标准，在起步时即达到最优。

a.规则参考

b.面试答点

基于硬件的系统优化：《面试之Mysql优化问题》

优化效果：硬件 << 系统配置 << 数据库表结构 << SQL及索引

课程树优化：《MySQL优化/面试，看这一篇就够了》

开启sql_cache配置；大表分区；配置并查看slow.log日志(线上可开启)，配置并查看profile信息(线上建议关闭)；压测工具mysqlslap。

3、压力测试