首页 > 数据库 >Mysql优化的思路以及工具的使用

Mysql优化的思路以及工具的使用

时间：2023-06-15 16:57:39浏览次数：49  
标签：name 查询 role user Mysql 思路 NULL 优化 id

一，查询sql的执行效率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。
下面的命令显示了当前 session 中所有统计参数的值(模糊查询，全部查询大概有356行数据)
SHOW STATUS LIKE "Com_______";

从上面我们可以看出当前会话中，插入了0次，删除了0次，查询了15次；
当我们再一次进行查询时：

可以发现查询的次数+1了；这样可以让我们分析改数据库的哪种操作最为频繁，然后可以选择合适的引擎，或者对应的优化。
下面是常见的几个参数：

我们可以看下这个命令：
show status like 'Innodb_rows_%';

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

二、定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

1.慢查询日志

a.首先我们先输入一个命令来查看慢查询的配置，输入show variables like '%slow%'来进行查看

long_query_time ： 设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log ： 指定是否开启慢查询日志
log_slow_queries ： 指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)
slow_query_log_file ： 指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
b.查看之后找到slow_query_log选项，可以看到它的值显示为OFF，表示当前是关闭的状态，还未开启。

c.之后下一步我们使用命令将slow_query_log选项的值设置为ON开启，如下图中所示的代码。


d.经过上一个步骤之后就成功开启慢查询了，然后我们在指定的路径中就可以看到一个多出来的慢查询日志文件，如下图中所示。

2.show processlist

使用这条命令时，只有在sql执行较慢的时候才能测出，进行一下操作：

定位到了该条执行的信息，下面是上述信息参数的大体介绍：

1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4） db列，显示这个进程目前连接的是哪个数据库
5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6） time列，显示这个状态持续的时间，单位是秒
7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8） info列，显示这个sql语句，是判断问题语句的一个重要依据

三、explain分析执行计划

通过以上步骤查询到效率低的 SQL(一般都是分析select操作) 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划

其对应的字段的含义如下：

（1）环境准备（创建表）：
CREATE TABLE t_role( idvarchar(32) NOT NULL, role_namevarchar(255) DEFAULT NULL, role_codevarchar(255) DEFAULT NULL, description varchar(255) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY unique_role_name (role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE t_user (
id varchar(32) NOT NULL,
username varchar(45) NOT NULL,
password varchar(96) NOT NULL,
name varchar(45) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_user_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE user_role (
id int(11) NOT NULL auto_increment ,
user_id varchar(32) DEFAULT NULL,
role_id varchar(32) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_ur_user_id (user_id),
KEY fk_ur_role_id (role_id),
CONSTRAINT fk_ur_role_id FOREIGN KEY (role_id) REFERENCES t_role (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_ur_user_id FOREIGN KEY (user_id) REFERENCES t_user (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t_user (id, username, password, name) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into t_user (id, username, password, name) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into t_user (id, username, password, name) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into t_user (id, username, password, name) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into t_user (id, username, password, name) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into t_user (id, username, password, name) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');

INSERT INTO t_role (id, role_name, role_code, description) VALUES('5','学生','student','学生');
INSERT INTO t_role (id, role_name, role_code, description) VALUES('7','老师','teacher','老师');
INSERT INTO t_role (id, role_name, role_code, description) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO t_role (id, role_name, role_code, description) VALUES('9','管理员','admin','管理员');
INSERT INTO t_role (id, role_name, role_code, description) VALUES('10','超级管理员','super','超级管理员');

INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
`

（2）explain的id：

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种 ：
explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

通过分析多表连接查询可以得出三者id是同样大小，所以是由上往下执行的。
id 不同id值越大，优先级越高，越先被执行。
EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));

这是通过子查询分析得到的结果，可以发现最先执行的就是id为3的，对应的也是最内层的查询。
id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

(2)explain的select_type：

表示 SELECT 的类型，常见的取值，如下表所示：

（3）explain的table：

展示这一行的数据是关于哪一张表的

（4）explain的type：

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

（5）explain的key：

possible_keys : 显示可能应用在这张表的索引， 一个或多个。

key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

（6）explain的 rows：

扫描行的数量。

（7）explain的 extra：


对应的查询
explain select * from t_role group by role_code;

explain select role_name from t_role order by role_name;

四、show profile分析SQL

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

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

select @@have_profiling;

可以看出是支持的；然后查看是否开启：

select @@profiling;

set profiling=1; //开启profiling 开关；


刚开启之后去查询时，是没有sql执行时间的记录的，我们可以先进行一些操作（切换到本地数据库，需要大数据支持更好观察）：

然后我们可以通过下面操作来查看每条sql的详细耗时：

TIP ：
Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：
show profile cpu for query 55;

五、trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。
打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

点击查看代码 
# 打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

# 执行SQL语句 ：
select * from tb_item where id < 4;

# 最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：
select * from information_schema.optimizer_trace;
-----------------------------------
**完整的使用 optimizer trace 功能的步骤总结如下：**
    1. 打开optimizer trace功能 (默认情况下它是关闭的): SET optimizer_trace="enabled=on";
    1. 这里输入你自己的查询语句 SELECT ...;
    1. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程 SELECT * FROM information_schema.OPTIMIZER_TRACE;
    1. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步 ...
    1. 当你停止查看语句的优化过程时，把optimizer trace功能关闭 SET optimizer_trace="enabled=off";

+-------------------------------------------------+------------------------------------------------
| QUERY | TRACE | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |
+-------------------------------------------------+------------------------------------------------
| select role_name from t_role order by role_name | {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select t_role.role_name AS role_name from t_role order by t_role.role_name"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "t_role",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"rows_estimation": [
{
"table": "t_role",
"table_scan": {
"rows": 5,
"cost": 0.25
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "t_role",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 5,
"access_type": "scan",
"resulting_rows": 5,
"cost": 0.75,
"chosen": true,
"use_tmp_table": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 5,
"cost_for_plan": 0.75,
"sort_cost": 5,
"new_cost_for_plan": 5.75,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "t_role",
"attached": null
}
]
}
},
{
"optimizing_distinct_group_by_order_by": {
"simplifying_order_by": {
"original_clause": "t_role.role_name",
"items": [
{
"item": "t_role.role_name"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "t_role.role_name"
}
}
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
],
"index_order_summary": {
"table": "t_role",
"index_provides_order": true,
"order_direction": "asc",
"index": "unique_role_name",
"plan_changed": false
}
}
},
{
"finalizing_table_conditions": [
]
},
{
"refine_plan": [
{
"table": "t_role"
}
]
},
{
"considering_tmp_tables": [
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
} | 0 | 0 |

1 row in set (0.00 sec)

标签：name,查询,role,user,Mysql,思路,NULL,优化,id
From： https://www.cnblogs.com/waacode/p/17482853.html

相关文章

  • 「Python实用秘技14」快速优化Python导包顺序
    本文完整示例代码及文件已上传至我的Github仓库https://github.com/CNFeffery/PythonPracticalSkills这是我的系列文章「Python实用秘技」的第14期，本系列立足于笔者日常工作中使用Python积累的心得体会，每一期为大家带来一个几分钟内就可学会的简单小技巧。作为系列第1......
  • MySQL 数据库表字段字符集编码不一致导致隐式转换索引失效案例分析
       有网友咨询，丢过来一段sql语句，说是执行一次耗时10多分钟，让帮忙看看。表数据量在5万左右，数据量不大，首先确认表统计信息都是准确的，查询sql以及执行计划如下：  可以看到嵌套循环join部分全表扫描，缺失索引，应该在join条件列hoist_code、device_code建立索引，但是网友反馈......
  • CentOs7安装部署Sonar环境(JDK1.8+MySql5.7+sonarqube7.8)
    sonarqube安装前环境准备JDK1.8、MySql5.7。一、JDK安装1、下载jdk #打开下面的网址，选择jdk-8u371-linux-x64.tar.gz进行下载(8u371版本可能会有区别，但是没有影响) http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html2、......
  • mysql 报错 ：The table xxx is full
    ############################################现象：用户执行的sql语句报错：Cause:java.sql.SQLException:Thetable'/home/work/mysql_3306//tmp/#sql117f0c_db7113_a4'isfull 原因：参数internal_tmp_mem_storage_engine是默认值TempTable，当临时表大小超过temptable_m......
  • Longest Path (牛客多校) (换根DP+斜率优化)
    换根dp:第一次dfs处理儿子点的权值第二次dfs处理父亲点,和兄弟节点的权值处理兄弟节点的时候,利用父亲节点统一处理,利用stl存储斜率优化:为什么会用到斜率优化:在遇到转移式子是fixfj的时候,不是分开的,(分开的,直接用单调队列处理)(通常会遇到平方式子)把......
  • 如何优化深度克隆 deepclone
    普通克隆的方式 1.大部分会使用序列化和反序列化的方式,缺点：这种方式不会对对象中的函数或者Map对象进行深度克隆。constresult=JSON.parse(JSON.stringify(value)) 2.手写一个deepclone函数 //创建可回收map集合来处理环形引用的情况例如:obj.c=objconstc......
  • mysql面试常见问题
    mysql面试常见问题以下是MySQL面试中常见的问题：什么是索引？MySQL中有哪些类型的索引？InnoDB和MyISAM存储引擎的区别是什么？MySQL的锁机制有哪些？如何避免死锁？什么是事务？MySQL中如何回滚一个事务？如何进行SQL性能优化？你了解的常用优化技巧有哪些？请简述MyS......
  • docker 安装 mysql5
    version:'3'services:mysql57:#容器名(以后的控制都通过这个)container_name:mysql57#重启策略restart:alwaysimage:mysql:5.7ports:-"3307:3306"volumes:#挂载配置文件#-./mysql/db/:/docker-en......
  • Windows Server 2022 安装配置 MYSQL 及远程连接
    官方下载地址：https://dev.mysql.com/downloads/mysql/解压后新建my.ini放在解压目录下。配置环境变量：此电脑->属性->高级系统设置->环境变量->系统变量。找到path，新建D:\ProgramFiles\mysql-8.0.33-winx64\bin[mysqld]#设置3306端口port=3306#设置mysql的安装目录based......
  • 个人对DDD（领域驱动设计）理解，以及对实际开发的优化
    目录DDD简单介绍个人理解DDD落地到具体开发的优化总结DDD简单介绍什么是DDD？w我们看看Wiki上怎么说领域驱动设计（英语：Domain-drivendesign，缩写DDD）是一种通过将实现连接到持续进化的模型[1]来满足复杂需求的软件开发方法。领域驱动设计的前提是：把项目的主要重点放在核心领域（c......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99