第11步---MySQL的优化

第11步---MySQL的优化

 1.概念

原先写功能。后来对平静进行优化

  • 设计

  • 查询语句

  • 索引

  • 存储

2.查看执行效率

-- 查看当前会话sql得执行类型得统计信息SHOW session STATUS like 'Com%'

上面展示得信息就是统计了当前会话得执行得操作得次数。

-- 查看全局得
SHOW GLOBAL STATUS like 'Com%'

-- 查看基于innoDB引擎得操作
SHOW STATUS LIKE 'Innodb_rows_%'

 3.sql优化操作

查看慢sql

-- 查看慢日志得配置得信息
SHOW VARIABLES LIKE '%slow_query_log%';

 

系统得慢日志是默认是关闭的状态的需要自己单独进行开启。

开启慢日志查询

-- 开启慢日志查询
set GLOBAL slow_query_log=1;

重新查看慢日志的信息

 

-- 查看慢日志的执行的阈值的信息
SHOW VARIABLES LIKE '%long_query_time%';

 只有执行的执行的时间超过了10s的就会被记录下来。

-- 修改慢日志的执行的阈值的信息
SET SESSION long_query_time=5;-- 查看慢日志的执行的阈值的信息
SHOW VARIABLES LIKE '%long_query_time%';

要是进行全局修改的修改的话只能修改本地的配置文件。

4.定位执行效率低下的sql

-- 查看当前客户端连接服务器的线程执行的状态信息
SHOW PROCESSLIST;

5.Explain执行计划

-- 查看当前sql的执行计划
EXPLAIN SELECT * FROM account WHERE name ='zhangsan';

 

  • id是表的查询序号

  • type是简单表还是子查询等

  • 输出结果的表

  • type：连接的类型

  • 用到的索引

  • 实际的索引

  • 索引字段的长度

  • 扫描字段的长度

  • 扫描行的长度

  • 执行情况的说明和描述

-- 查看表的连接情况
EXPLAIN SELECT * FROM dept d join emp e ON d.deptno=e.deptno;

 

上面的表都是平行的关系，没有主副之分的。

要是子查询的话有顺序的关系。id的值越大执行的顺序就越高。里面的子查询最先被加载的。

主查询是子查询最外层的查询。

 

select_type指定的是表中的数据来自于什么地方。可以来自基本表，可以来自衍生表等等。

6.type类型

all：表示全表扫描。

 

5.7以上的版本不再显示system。

普通的索引是ref。只有唯一索引才会出现const。

CREATE TABLE `user2` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;CREATE TABLE `user2_sex` (`id` int(11) NOT NULL AUTO_INCREMENT,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;INSERT INTO `xx`.`user2` (`id`, `name`) VALUES (1, '张三');
INSERT INTO `xx`.`user2` (`id`, `name`) VALUES (2, '李四');
INSERT INTO `xx`.`user2` (`id`, `name`) VALUES (3, '王五');INSERT INTO `xx`.`user2_sex` (`id`, `age`) VALUES (1, 20);
INSERT INTO `xx`.`user2_sex` (`id`, `age`) VALUES (2, 22);
INSERT INTO `xx`.`user2_sex` (`id`, `age`) VALUES (3, 23);EXPLAIN SELECT * FROM user2 a,user2_sex b WHERE a.id=b.id;

 

下面给第二个添加一个主键索引。

eq_ref比ref效率更高。

range：

EXPLAIN SELECT * FROM user2 WHERE id>2;

index:扫描的是索引列

all：把整个表都进行扫描一遍

执行的效率：

system<const<eq_ref<ref<range<index<all

7.其他执行字段

table

  • 显示的不一定是真实的名字可以是数据表的简称

rows：

  • 扫描的行的数量

key：

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

  • key：实际使用的索引。如果为null，就是没有使用索引。

EXPLAIN SELECT * FROM user2 WHERE id>2;

 要是直接查询全部的话就是不会显示

EXPLAIN SELECT * FROM user2 ;

 

extra

  • 显示额外的执行计划

EXPLAIN SELECT * FROM user2 order by name  ;

 


EXPLAIN SELECT count(*) FROM user2 GROUP BY name   ;

 

 8.show profile查看执行效率

-- 采用show profile
-- 8.x不支持的
show @@have_profiling;-- 不支持的话
SET profiling=1;

执行一些简单的操作

SHOW DATABASES;
USE xx;
SELECT * FROM user2 WHERE id>2;SHOW PROFILES;

 查看单个sql的执行的时间的信息。

SHOW PROFILE FOR QUERY 90;

 查看cpu的执行的时间的信息

SHOW PROFILE cpu FOR QUERY 109;

 

  • 1：整个阶段的时间

  • 2：用户消耗

  • 3：系统的cpu的消耗

9.查看trace优化器

sql中提供了跟踪的trace，通过trace文件可以进一步为什么优化器执行的过程。

-- 设置trace
set optimizer_trace ='enabled=on',end_markers_in_json=on;
-- 设置最大的显示内存
SET optimizer_trace_max_mem_size=1000000;SELECT * FROM user2;-- 执行sql
SELECT * FROM information_schema.OPTIMIZER_TRACE \G;

下面是在dos终端显示的内容。navicat中是显示不出来的。

*************************** 1. row ***************************QUERY: SELECT * FROM user2TRACE: {"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `user2`.`id` AS `id`,`user2`.`name` AS `name` from `user2`"}] /* steps */} /* join_preparation */},{"join_optimization": {"select#": 1,"steps": [{"table_dependencies": [{"table": "`user2`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": [] /* depends_on_map_bits */}] /* table_dependencies */},{"rows_estimation": [{"table": "`user2`","table_scan": {"rows": 3,"cost": 0.25} /* table_scan */}] /* rows_estimation */},{"considered_execution_plans": [{"plan_prefix": [] /* plan_prefix */,"table": "`user2`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 3,"access_type": "scan","resulting_rows": 3,"cost": 0.55,"chosen": true}] /* considered_access_paths */} /* best_access_path */,"condition_filtering_pct": 100,"rows_for_plan": 3,"cost_for_plan": 0.55,"chosen": true}] /* considered_execution_plans */},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions_computation": [] /* attached_conditions_computation */,"attached_conditions_summary": [{"table": "`user2`","attached": null}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{"refine_plan": [{"table": "`user2`"}] /* refine_plan */}] /* steps */} /* join_optimization */},{"join_execution": {"select#": 1,"steps": [] /* steps */} /* join_execution */}] /* steps */
}

10.索引优化

最左原则

避免索引失效。

  • 全值匹配是会出现失效的。

  • 组合索引的时候从左到右进行匹配的。只要包含就行与顺序是无关的。

  • 不能违背最左原则

11.其他常见失效的情况

索引失效的其他的常见的做法

  • 范围上不能进行运算

  • 字符串不加单引号索引会失效。不影响查询，会自动地进行类型地转换

  • 尽量避免覆盖索引。避免select *。

  •  没有必要的时候尽量不要写select *的操作。

  •  避免l以%
  • 开头的like模糊查询。索引失效。要是%放在最后面的话就可以利用索引

12.不使用索引的情况

不使用索引的情况

  • 当全表查询的速度比索引快的时候不用索引

  • is null 和 is not null 有时候失效。有时候是有效的。根据实际的数据是有关的。

  • in走索引。not in索引失效。但是主键索引都是会用的。

  • 单列索引和复合索引，尽量比卖你使用复合索引。

  • 多个索引的话，即使where中有多个索引列，则只有一个是最优的索引是有效的。

 

13.SQL优化-大批量插入数据

大批量插入数据

  • 要保证主键列是有序的插入的速度是比较快的

CREATE TABLE tb_user
(id int(11) NOT NULL AUTO_INCREMENT,username varchar(45) NOT NULL,password varchar(96) NOT NULL,name varchar(45) NOT NULL,birthday datetime DEFAULT NULL,sex char(1) DEFAULT NULL,email varchar(45) DEFAULT NULL,phone varchar(45) DEFAULT NULL,qq varchar(32) DEFAULT NULL,status varchar(32) NOT NULL COMMENT '用户状态',create_time datetime NOT NULL,update_time datetime DEFAULT NULL,PRIMARY KEY (id),UNIQUE KEY unique_user_username (username)
) ;

我们将文件中的数据保存到表中。插入的数据最好是要插入的数据是有顺序的。

  • 指定字段之间的分隔符 逗号

  • 指定行之间的分割符是 换行符

  • load是加载本地的文件插入到数据库中的

load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

需要开启系统的相关的操作


-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;

此时执行load操作。

主键乱序的比主键不乱序的执行的效率是低的。

关闭唯一性校验

-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;执行插入操作
-- 插入完成之后再开启校验的操作
SET UNIQUE_CHECKS=1;

14.insert优化

  • 将多个insert转换成一个插入的操作

  • 采用手动事务提交的方式。

  • 插入的数据尽量保持有序

15.order by优化

常见的两种排序的方式

  • 直接对返回的数据进行的排序就是filesort，所有不是通过索引直接返回排序结果的排序叫做filesort

  • 有序索引顺序扫描直接返回有序数据。这种情况极为using index。不需要额外排序，操作效率高。

DROP TABLE IF EXISTS emp;
-- 优化order by语句
CREATE TABLE `emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(100) NOT NULL,`age` int(3) NOT NULL,`salary` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');-- 创建组合索引
CREATE INDEX idx_emp_age_salary on emp (age,salary);-- Using filesort
EXPLAIN SELECT * FROM emp ORDER BY age;-- Using filesort
EXPLAIN SELECT * FROM emp ORDER BY age,salary;

上面出现的不能使用索引的原因是select *

-- Using index
EXPLAIN SELECT id FROM emp ORDER BY age;-- Using index
EXPLAIN SELECT id,age FROM emp ORDER BY age;-- Using index
EXPLAIN SELECT id,age,salary FROM emp ORDER BY age;-- Using filesort
EXPLAIN SELECT id,age,salary,name  FROM emp ORDER BY age;-- Using index; Using filesort
EXPLAIN SELECT id,age  FROM emp ORDER BY age ASC ,salary DESC;-- Backward index scan; Using index
EXPLAIN SELECT id,age  FROM emp ORDER BY age DESC ,salary DESC;-- Using index; Using filesort
EXPLAIN SELECT id,age  FROM emp ORDER BY salary,age;

order by后面的排序的字段尽量和索引的顺序是一致的。

可以适当的提高排序的缓存的空间大小。

show variables like 'max_length_for_sort_data'; -- 4096
show variables like 'sort_buffer_size'; -- 262144

上面的值越大越优先选择一次扫描。

16.优化子查询

连接查询比子查询效率更高一点。尽量不采用临时表的形式进行查询。

17.优化limit

数据量越大需要的数据越少，消耗的性能越大，查询900010的10条数据，前面900000排序号的数据被舍弃了，导致了性能的损耗。

优化的方式

  • 可以对索引列进行排序，在这个基础上进行limit。可以跟原先的表进行关联查询。

explain select * from tb_user limit 900000,10; -- 0.684  速度是比较慢的explain select * from tb_user a, (select id from tb_user order by id limit 900000,10) b where a.id = b.id; -- 0.486 速度是比较快的。用到了id索引列然后进行了关联查询
  • 转换成某个id的查询

采用下面的方式也是可以的
explain select * from tb_user where id > 900000 limit 10;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/100165.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

使用 PyTorch 进行高效图像分割：第 3 部分

使用 PyTorch 进行高效图像分割：第 3 部分

一、说明 在这个由 4 部分组成的系列中&#xff0c;我们将使用 PyTorch 中的深度学习技术从头开始逐步实现图像分割。本部分将重点介绍如何使用深度可分离卷积来优化我们的 CNN 基线模型&#xff0c;以减少可训练参数的数量&#xff0c;使模型可部署在移动设备和其他边缘设备上…
阅读更多...
opencv如何调用YOLOv5（无pytorch）

opencv如何调用YOLOv5（无pytorch）

目录 一、前言 二.正文 2.1定义颜色 2.2目标检测主代码详解 2.3读取视频or图片进行检测 注意&#xff1a;opencv-python 本文使用的版本为4.5.2.52 一、前言 YOLO系列是one-stage且是基于深度学习的回归方法&#xff0c;而R-CNN、Fast-RCNN、Faster-RCNN等是two-stage且…
阅读更多...
计算机网络第2章（物理层）

计算机网络第2章（物理层）

计算机网络第2章&#xff08;物理层&#xff09; 2.1 物理层的基本概念2.2 物理层下面的传输媒体2.2.1 导引型传输媒体2.2.2 非导引型传输媒体 2.3 传输方式2.3.1 串行传输和并行传输2.3.2 同步传输和异步传输2.3.3 单向通信&#xff08;单工&#xff09;、双向交替通信&#x…
阅读更多...
很好的启用window10专业版系统自带的远程桌面

很好的启用window10专业版系统自带的远程桌面

启用window10专业版系统自带的远程桌面 文章目录 启用window10专业版系统自带的远程桌面前言1.找到远程桌面的开关2. 找到“应用”项目3. 打开需要远程操作的电脑远程桌面功能 总结 前言 Windows操作系统作为应用最广泛的个人电脑操作系统&#xff0c;在我们身边几乎随处可见。…
阅读更多...
分布式事务（4）：两阶段提交协议与三阶段提交区别

分布式事务（4）：两阶段提交协议与三阶段提交区别

1 两阶段提交协议 两阶段提交方案应用非常广泛&#xff0c;几乎所有商业OLTP数据库都支持XA协议。但是两阶段提交方案锁定资源时间长&#xff0c;对性能影响很大&#xff0c;基本不适合解决微服务事务问题。 缺点&#xff1a; 如果协调者宕机&#xff0c;参与者没有协调者指…
阅读更多...
如何在window下cmd窗口执行linux指令？

如何在window下cmd窗口执行linux指令？

1.Git&#xff1a;https://git-scm.com/downloads(官网地址) 2.根据自己的实际路径,添加两个环境变量 3.重启电脑
阅读更多...
Kubernetes教程—查看 Pod 和节点

Kubernetes教程—查看 Pod 和节点

目标 了解 Kubernetes Pod。了解 Kubernetes 节点。对已部署的应用故障排除。 Kubernetes Pod 在模块 2 中创建 Deployment 时, Kubernetes 创建了一个 Pod 来托管你的应用实例。Pod 是 Kubernetes 抽象出来的&#xff0c; 表示一组一个或多个应用容器&#xff08;如 Docker…
阅读更多...
爬虫借助代理会让网速快点吗？

爬虫借助代理会让网速快点吗？

亲爱的程序员朋友们&#xff0c;你曾经遇到过爬虫网速慢的情况吗&#xff1f;别着急&#xff01;今天我将和你一起探讨一下使用代理是否可以加速爬虫&#xff0c;让我们一起进入这个轻松又专业的知识分享。 一、原因和机制的解析 1.IP限制 某些网站为了保护资源和防止爬虫行…
阅读更多...
线段树详解——影子宽度

线段树详解——影子宽度

OK&#xff0c;今天来讲一讲线段树~~ 线段树是什么线段树的实现线段树的时间复杂度线段树的应用线段树的节点结构其他操作和优化例题——影子宽度输入输出格式输入格式输出格式 输入输出样例输入样例输出样例 例题讲解 线段树是什么 线段树&#xff08; S e g m e n t Segmen…
阅读更多...
Goland 注释时自动在注释符号后添加空格

Goland 注释时自动在注释符号后添加空格

不得不说 JetBrains 旗下的 IDE 都好用&#xff0c;而且对于注释这块&#xff0c;使用 Ctrl / 进行注释的时候&#xff0c;大多会在每个注释符号后统一添加一个空格&#xff0c;比如 PyCharm 和 RubeMine 等。 # PyCharm # print("hello world") # RubyMine # req…
阅读更多...
Confluent kafka 异常退出rd_tmpabuf_alloc0: rd kafka topic info_new_with_rack

Confluent kafka 异常退出rd_tmpabuf_alloc0: rd kafka topic info_new_with_rack

rd_tmpabuf_alloc0: rd kafka topic info_new_with_rack 根据网上的例子&#xff0c;做了一个测试程序。 C# 操作Kafka_c# kafka_Riven Chen的博客-CSDN博客 但是执行下面一行时&#xff0c;弹出上面的异常&#xff0c;闪退。 consumer.Subscribe(queueName) 解决方案&…
阅读更多...
攻防世界-supersqli

攻防世界-supersqli

原题 解题思路 直接查找看不到明显的回显变化 先找回显变化数量 -1 order by 2 #如果是3列就报错&#xff0c;说明只有两列。接下来找数据库名称&#xff1a; -1 union select 1,databases # 结果是后端做了一些简单的过滤&#xff0c;需要更换查找语句。 -1; show …
阅读更多...
sNMFcross-entropyK

sNMFcross-entropyK

0.简单介绍 稀疏非负矩阵&#xff08;sNMF&#xff09;和最小二乘优化来产生祖先比例估计数的祖先推断算法&#xff0c;这个算法呢与admixture来说差别不是很大&#xff0c;但是优点就是快&#xff0c;运算速度可以快到10-30倍左右。 1.安装 这一步不必多说&#xff0c;下载…
阅读更多...
PHP加密与安全的最佳实践

PHP加密与安全的最佳实践

PHP加密与安全的最佳实践 概述 在当今信息时代&#xff0c;数据安全是非常重要的。对于开发人员而言&#xff0c;掌握加密和安全的最佳实践是必不可少的。PHP作为一种常用的后端开发语言&#xff0c;提供了许多功能强大且易于使用的加密和安全性相关函数和类。本文将介绍一些P…
阅读更多...
C# WPF ListBox 动态显示图片

C# WPF ListBox 动态显示图片

前言 最近在和其他软件联合做一个本地图片选择传输功能&#xff0c;为此希望图片能够有序的呈现在客户端&#xff0c;简单的实现了一下功能&#xff0c;通过Mvvm模式进行呈现&#xff0c;过程简单通俗&#xff0c;话不多说直接上图。 处理过程 前台代码 你只需要粘贴到你的前台…
阅读更多...
c#设计模式-结构型模式 之 桥接模式

c#设计模式-结构型模式 之 桥接模式

前言 桥接模式是一种设计模式&#xff0c;它将抽象与实现分离&#xff0c;使它们可以独立变化。这种模式涉及到一个接口作为桥梁&#xff0c;使实体类的功能独立于接口实现类。这两种类型的类可以结构化改变而互不影响。 桥接模式的主要目的是通过将实现和抽象分离&#xff0c;…
阅读更多...
css学习2（利用id与class修改元素）

css学习2（利用id与class修改元素）

1、id选择器可以为标有特定id的html元素指定特定的样式。 2、选择器以#开头&#xff0c;后跟某id的属性值。 3、class选择器用于描述一组元素的样式&#xff0c;class可以在多个元素使用。 4、类选择器用.选择。 5、指定特定的元素使用class。 6、元素的多个类用空格分开&…
阅读更多...
SpringCloud学习笔记(一)_快速入门

SpringCloud学习笔记(一)_快速入门

SpringCloud简介 Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具&#xff08;例如配置管理&#xff0c;服务发现&#xff0c;断路器&#xff0c;智能路由&#xff0c;微代理&#xff0c;控制总线&#xff09;。分布式系统的协调导致了样板模式, 使用Spr…
阅读更多...
【React】生命周期和钩子函数

【React】生命周期和钩子函数

概念 组件从被创建到挂载到页面中运行&#xff0c;再到组件不用时卸载的过程。 只有类组件才有生命周期。 分为三个阶段&#xff1a; 挂载阶段更新阶段销毁阶段 三个阶段 挂载阶段 钩子函数 - constructor 创建阶段触发 作用&#xff1a;创建数据 之前定义状态是简写&…
阅读更多...
Linux面试笔试题（5）

Linux面试笔试题（5）

79、下列工具中可以直接连接mysql的工具有【c 】。 A.xhsell B.plsql C.navicat D.以上都不是 Navicat 是一套可创建多个连接的数据库开发工具&#xff0c; 让你从单一应用程序中同时连接 MySQL、Redis、MariaDB、MongoDB、 SQL Server、Oracle、PostgreSQL和 SQLite 。它与…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023