再谈mysql优化之性能分析

timo-nbktp 1年前 ⋅ 893 阅读

在应用的的开发过程中，由于初期数据量小，开发人员写SQL语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化.
一般Mysql的优化分为两步，首先分析Mysql的性能，然后想出对应的方法优化。

一，性能分析

1.1 查看sQL执行频率

MySQL客户端连接成功后，通过show [session|global] status命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。

下面的命令显示了当前会话中所有统计参数的值，统计我们的各种操作的执行频率，分析我们的业务主要是什么操作。
1，查看当前会话的统计结果。

show session status like 'Com_______';

2，查看自数据库上次启动至今的统计结果

show global status like 'Com_______';

3，查看针对Innodb引擎的统计结果

show status like 'Innodb_rows_%';

ps：以下是返回结果中字段的含义：

1.2 定位低效率执行sql

可以通过以下两种方式定位执行效率较低的sql语句。
1，慢查询日志。

2，show provesslist:该命令查看当前MysQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

1.2.1 慢查询日志

-- 查看慢日志配置信息
show variables like '%slow_query_log%' ;
-- 开启慢日志查询
set global slow_query_log='ON';
-- 查看慢日志记录sQL的最低阈值时间
show variables like 'long_query_time%' ;
-- 修改慢日志记录sQL的最低阈值时间
set global long_query_time=4;
-- 注意这里修改long_query_time以后再查看该变量，发现结果还是10，
读者不要疑惑，其实这个值已经改了，再断开数据库连接重新连接就会发现改成4了已经。

然后查看数据目录下的DESKTOP-3HJOHIJ-slow.log日志文件即可

1.2.2 实时定位低效率执行sql

show processlist;

例如：除了在navicat连接数据库之外，再开一个命令行连接mysql，执行select sleep(50);，再在navicat连接上执行该命令查看当前实时执行的命令情况。

下面是 show processlist;返回结果的字段解释：

1.3 使用explain分析执行计划。

通过以上步骤查询到效率低的SQL语句后，可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

使用方法：
要分析哪一条sql的执行顺序，只需要在那一条sql前面加上explain关键字执行即可。

1.3.1 数据准备

准备测试数据

create database mydb13__optimize ;
use mydb13_optimize;
/*
 Navicat Premium Data Transfer
 
 Source Server         : conn-localhost
 Source Server Type    : MySQL
 Source Server Version : 80025
 Source Host           : localhost:3306
 Source Schema         : mydb13_optimize
 
 Target Server Type    : MySQL
 Target Server Version : 80025
 File Encoding         : 65001
 
 Date: 22/02/2022 18:25:14
*/
 
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for privilege
-- ----------------------------
DROP TABLE IF EXISTS `privilege`;
CREATE TABLE `privilege`  (
  `pid` int NOT NULL,
  `pname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of privilege
-- ----------------------------
INSERT INTO `privilege` VALUES (1, '玩跑车');
INSERT INTO `privilege` VALUES (2, '挖煤');
INSERT INTO `privilege` VALUES (3, '敲代码');
 
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `rid` int NOT NULL,
  `rname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`rid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, '女神');
INSERT INTO `role` VALUES (2, '屌丝');
INSERT INTO `role` VALUES (3, '老板');
 
-- ----------------------------
-- Table structure for role_privilege
-- ----------------------------
DROP TABLE IF EXISTS `role_privilege`;
CREATE TABLE `role_privilege`  (
  `rid` int NOT NULL,
  `pid` int NOT NULL,
  PRIMARY KEY (`rid`, `pid`) USING BTREE,
  INDEX `privilege_pk`(`pid`) USING BTREE,
  CONSTRAINT `privilege_pk` FOREIGN KEY (`pid`) REFERENCES `privilege` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `role_p_pk` FOREIGN KEY (`rid`) REFERENCES `role` (`rid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of role_privilege
-- ----------------------------
INSERT INTO `role_privilege` VALUES (1, 1);
INSERT INTO `role_privilege` VALUES (3, 1);
INSERT INTO `role_privilege` VALUES (2, 2);
INSERT INTO `role_privilege` VALUES (2, 3);
 
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `uid` int NOT NULL,
  `uname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '小乔');
INSERT INTO `user` VALUES (2, '张飞');
INSERT INTO `user` VALUES (3, '貂蝉');
 
-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `uid` int NOT NULL,
  `rid` int NOT NULL,
  PRIMARY KEY (`uid`, `rid`) USING BTREE,
  INDEX `ru_pk`(`rid`) USING BTREE,
  CONSTRAINT `ru_pk` FOREIGN KEY (`rid`) REFERENCES `role` (`rid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `ur_pk` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES (1, 1);
INSERT INTO `user_role` VALUES (2, 2);
INSERT INTO `user_role` VALUES (3, 3);
 
SET FOREIGN_KEY_CHECKS = 1;

1.3.2 实践中认识explain返回结果

先举一个简单的例子:

explain select * from user where uid=1;

返回结果如下：

可以看到，返回结果为一张表，这张表有一条记录，表中的每个字段都代表了上面这条sql运行时候的一些信息。下表表示了每个字段的含义，下面分别举不同的例子来深刻理解这些字段的意义。

1.3.2.1 认识explain返回结果中的id字段

id 字段是select查询的序列号，是一组数字。因为一句sql中操作的可能不止一张表，而explain返回结果中id表示的是查询中执行select子句或者是操作各个表的顺序。id情况有两种:

1）id相同表示加载表的顺序是从上到下。

EXPLAIN SELECT* FROM
	user u,
	user_role ur,
	role r 
WHERE
	u.uid = ur.uid 
	AND ur.rid = r.rid;

可以看到，这是一个内连接的sql，内连接了user，role，user_role三张表，id都=1代表这三张表在mysql看来优先级是一样的，但是先加载了role，然后是user_role，最后是user。
2）id不同id值越大，优先级越高，越先被执行。

explain select * from 
role r,(select *from user_role ur where ur.uid=(select uid from user where uname='张飞')) t
where r.rid = t.rid;

如图：表示先执行user表，然后是ur表和r表同优先级情况下，先执行ur，后执行r。

1.3.2.2 认识explain返回结果中的select_type字段

select_type的取值含义如下：

1.3.2.3 认识explain返回结果中的type字段

type显示的是访问类型，是较为重要的一个指标，可取值为:

下面演示一下eq_ref返回结果和index返回结果：
为了方便试验，先创建两张表：

create table user2(id int,
name varchar(20));
insert into user2 values(1,'张三'),(2,'李四'),(3,'王五');
create table user2_ex(
id int,
age int);
insert into user2_ex values(1,20),(2,21),( 3,22);

注意，这时候还没有插入主键索引，执行

explain select * from user2 a, user2_ex b where a.id = b.id;

这时候两张表都是用all访问。
而加了主键以后

alter table user2 add primary key(id);
explain select * from user2 a,user2_ex b where a.id = b.id;

表示b表中有唯一的记录和a对应。

1.3.2.4 认识explain返回结果中的其他字段

1，table
显示这—步所访问数据库中表名称有时不是真实的表名字，可能是简称，

2，rows
扫描行的数量。
3，key
possible_keys:显示可能应用在这张表的索引，即这张表所拥有的索引

key :实际使用的这张表的索引，如果为NULL，则没有使用索引。
key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
4，extra
其他的额外的执行计划信息，在该列展示。

1.4 show profile分析SQL

Mysq|从5.0.37版本开始增加了对show profiles和show profile语句的支持。show profiles能够在做sQL优化时帮助我们了解时间都耗费到哪里去了。。

通过have_profiling参数,能够看到当前MySQL是否支持profile:

select @@have_profiling;
set profiling=1; --开启profiling开关

1.4.1 show profiles

怎么使用profiling？我们可以先执行一系列的语句，然后使用show profiles;语句查看刚才执行的一系列语句花费的时间。

1.4.1 show profile for query query_id

通过show profile for query query_id语句可以查看到该sQL执行过程中每个线程的状态和消耗的时间:

例如，上面show profiles的返回结果中，我们曾经执行过的730号的sql：explain select * from user2 a,user2_ex b where…，可以用以下语句查看细节：

补充：

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、 context switch、page faults等

明细类型类查看MySQL在使用什么资源上耗费了过高的时间。

例如，选择查看CPu的耗费时间: show profile cpu for query 730;

 

--end--

 

 

#mysql #mysql优化
版权 本着开源共享、共同学习的精神，本文转载自 https://blog.csdn.net/weixin_42445051/article/details/123051658 ， 如果侵权之处，请联系博主进行删除，谢谢~
阅读全部