Mysql性能分析过程

2021-02-27 本文已影响0人 RalapHao

1. 数据准备（单表演示）

创建表

CREATE TABLE `biz_article` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文章标题',
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`cover_image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci  DEFAULT NULL COMMENT '文章封面图片',
`qrcode_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文章专属二维码地址',
`is_markdown` tinyint unsigned DEFAULT '1',
`content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '文章内容',
`content_md` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'markdown版的文章内容',
`top` tinyint(1) DEFAULT '0' COMMENT '是否置顶',
`type_id` bigint unsigned NOT NULL COMMENT '类型',
`status` tinyint unsigned DEFAULT NULL COMMENT '状态',
`recommended` tinyint unsigned DEFAULT '0' COMMENT '是否推荐',
`original` tinyint unsigned DEFAULT '1' COMMENT '是否原创',
`description` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文章简介，最多200字',
`keywords` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文章关键字，优化搜索',
`comment` tinyint unsigned DEFAULT '1' COMMENT '是否开启评论',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=6000001 DEFAULT CHARSET=utf8mb4   COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT

初始化数据（100w条数据）

create
    definer = root@localhost procedure proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
WHILE i<=1000000 DO
       INSERT INTO ralap_test.biz_article ( title, user_id, cover_image, qrcode_path, is_markdown,     content, content_md, top, type_id, status, recommended, original, description, keywords, comment, create_time, update_time) VALUES (concat(i,'OneBlog简介（未开启评论'), 1, 'zhyd/cover/20180613092017699.jpg', null, 1, '<h1 id="dblog-">OneBlog简介</h1>
<p>OneBlog是一款简洁美观、自适应的Java博客系统。使用springboot开发，前端使用Bootstrap。支持移动端自适应，配有完备的前台和后台管理功能。</p>
', '# OneBlog简介
DBlog是一款简洁美观、自适应的Java博客系统。使用springboot开发，前端使用Bootstrap。支持    移动端自适应，配有完备的前台和后台管理功能。
', 0, 3, 1, 0, 1, 'OneBlog是一款简洁美观、自适应的Java博客系统。使用springboot开发，前端使用Bootstrap。支持移动端自适应，配有完备的前台和后台管理功能。', 'OneBlog,开源博客', 0, '2021-02-20 11:54:21', '2021-02-20 11:54:21');
        SET i = i+1;
    END WHILE;
END;

2. 剖析

查看、配置剖析配置
select @@profiling;

image.png

0代表未开启

开启剖析配置：set profiling = 1

针对select * from biz_article where title = '164OneBlog简介（未开启评论' 这个SQL语句我们进行分析

分析
1. explain select * from biz_article where title = '164OneBlog简介（未开启评论'
  
  image.png
  
  这里通过分析查询处理的数据进行分析, 这里是各个列的说明
  
  image.png
  
  我们一般关心selec_type、type、key、ref、rows，这几个字段，这里就详细说明，具体可以看一些文章，都有详细介绍，这里我们分析我们的SQL，看到我们type是ALL，说明SQL进行了全表扫描，这样数据量打的情况是会产生性能问题，
我们再看下具体的执行耗时
show profiles;

image.png

我们关心的是Query_ID 为26的这条记录，这里可以看出耗时1.1s，
然后再看下具体各个阶段耗时情况
show profile for query 26

image.png
这里就是各个细节的耗时，主要时间还是在executing上，也就是耗时主要在检索数据本省
通过上述的分析，我们可以分析出主要的耗时问题，
这里我们可以通过分析，因为title字段是惟一的，所以我们可以创建唯一索引，优化查询，
添加唯一建
alter table biz_article
add constraint biz_title
unique (title)
继续分析，看是否生效
重复上述步骤就好，可以

image.png
image.png

得到显著提升

索引失效情况

select * from biz_article where title like '%999999OneBlog简介（未开启评论'；
针对以上SQL分析

image.png

image.png

这里可能会有问题，我添加了索引，为什么没有用到，这类需要注意的就是特殊情况索引失效问题，最左前缀原则
这里流出常出现的情况

image.png

3. 慢查询

在工作中，我们不可能所以数据人工分析、记录耗时问题，这里Mysq提供了慢查询日志，作用就是记录执行耗时超出预期的SQL语句，

查看、开启慢查询日志
查看： show variables like 'slow_query_log%'

image.png

slow_query_log：OFF未开启，ON开启
slow_query_log_file: 慢查询日志文件位置
查看耗时阈值：show variables like 'long_query_time'，默认是10s，意思是执行时间超过10s的查询，才会记录到日志，

image.png

开启慢查询、调整阈值
>set slow_query_log = ON;
>set global long_query_time = 1;
设置后退出连接重连Mysql生效
然后执行：select * from biz_article where title like '%999999OneBlog简介（未开启评论'；
查看日志文件：view /usr/local/var/mysql/MacBook-Pro-slow.log

image.png

看到慢查询语句已经在记录中

Mysql性能分析过程

1. 数据准备（单表演示）

2. 剖析

索引失效情况

3. 慢查询

猜你喜欢

热点阅读