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.规则参考
《MySQL 数据库铁律 2019.09》
《详解Mysql 30条军规》
b.面试答点
基于硬件的系统优化:《面试之Mysql优化问题》
优化效果:硬件 << 系统配置 << 数据库表结构 << SQL及索引
课程树优化:《MySQL优化/面试,看这一篇就够了》
开启sql_cache配置;大表分区;配置并查看slow.log日志(线上可开启),配置并查看profile信息(线上建议关闭);压测工具mysqlslap。
3、压力测试
a.自动生成sql测试
/usr/local/mysql/bin/mysqlslap --auto-generate-sql -uroot -p
结果中各项含义:
Average number of …
运行所有语句的平均秒数
Minimum number of …
运行所有语句的最小秒数
Maximum number of …
运行所有语句的最大秒数
Number of clients …
客户端数量
Average number of queries per client
每个客户端运行查询的
平均数
b.添加并发测试
/usr/local/mysql/bin/mysqlslap --auto-generate-sql --concurrency=100 –number-of-queries=1000 -uroot -p
–concurrency=100 指定同时有100个客户端连接
–number-of-queries=1000 指定总的测试查询次数(并发客户端数 \* 每个客户端的查询次数)
c.常用参数
**常用参数 \[options\] 详细说明:**
--auto-generate-sql, -a 自动生成测试表和数据,表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括:read,key,write,update和mixed(默认)。
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列,从5.1.18版本开始支持。
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列,默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列,默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试,例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--create-schema 代表自定义的测试库名称,测试的schema,MySQL中schema也就是[database](http://www.ha97.com/category/database)。
--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩,则压缩信息传递。
--concurrency=N, -c N 表示并发量,也就是模拟多少个客户端同时执行select。可指定多个值,以逗号或者--delimiter参数指定的值做为分隔符。例如:--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要测试的引擎,可以有多个,用分隔符隔开。例如:--engines=myisam,innodb。
--iterations=N, -i N 测试执行的迭代次数,代表要在不同并发环境下,各自运行测试多少次。
--only-print 只打印测试语句而不实际执行。
--detach=N 执行N条语句后断开重连。
--debug-info, -T 打印内存和CPU的相关信息。
说明:
测试的过程需要生成测试表,插入测试数据,这个mysqlslap可以自动生成,默认生成一个mysqlslap的schema,如果已经存在则先删除。可以用`--only-print`来打印实际的测试过程,整个测试完成后不会在数据库中留下痕迹。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。