SQL高级 --优化

最新推荐文章于 2024-06-18 20:21:19 发布
最新推荐文章于 2024-06-18 20:21:19 发布
阅读量423 收藏
点赞数
分类专栏： mysql 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48618320/article/details/128984087
版权

一、SQL查询的解析

  • 关联查询过多
  • 索引失效（单值、符合）
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

二、mysql explain使用简介

在这里插入图片描述在这里插入图片描述

1、关于id的说明：

在这里插入图片描述

2 、select_type

常见和常用的值有如下几种：

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

1、 SIMPLE 简单的select查询，查询中不包含子查询或者UNION

2、 PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

3、 SUBQUERY 在SELECT或WHERE列表中包含了子查询

4、DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

5、UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
6、UNION RESULT 从UNION表获取结果的SELECT

3、 关于 type 说明

在这里插入图片描述
在这里插入图片描述

4 、 table

指的就是当前执行的表

5 、possible_keys 和 key

possible_keys： 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key ： 实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

6、 explain 关于 EXtra介绍

包含不适合在其他列中显式但十分重要的额外信息
在这里插入图片描述

6.1 Using filesort（九死一生）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

6.2 Using temporary（十死无生）

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

6.3 Using index（发财了）

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

6.4 Using where

表明使用了where过滤

6.5 Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

6.6 impossible where

where子句的值总是false，不能用来获取任何元组

SELECT * FROM t_user WHERE id = '1' and id = '2'

### 6.7 select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

### 6.8 distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

覆盖索引(Covering Index), 一般说为索引覆盖。

  • 理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
  • 理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
- 小练习

在这里插入图片描述

答案：在这里插入图片描述

Join语句的优化

在这里插入图片描述

常见失效：

在这里插入图片描述

第2点解释、最佳左侧法则：

如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
自己理解：（在复合索引中）按照复合索引中的第一个字段进行引用。

第4点解释、存储引擎不能使用索引中范围条件右边的列。存储引擎不能使用索引中范围条件右边的列的比较

第5点解释、


在这里插入图片描述

第6点解释、

在这里插入图片描述

第7点解释、

在这里插入图片描述

第8点解释、

在这里插入图片描述
解决方法

  • like 匹配的值可以在左边写 % ，右边不能写。
  • 可以使用覆盖索引解决

第9点解释、字符串不加单引号索引失效

在这里插入图片描述

in与exists

在这里插入图片描述
在这里插入图片描述

ORDER BY优化

在这里插入图片描述

  • MySQL支持二种方式的排序，FileSort和Index，Index效率高.它指MySQL扫描索引本身完成排序。FileSort方式效率较低。在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

MYSQLDUNMPSLOW

在这里插入图片描述

在这里插入图片描述

三、批量导入数据时（1000w）时注意

在这里插入图片描述

  • 示例
  • 在这里插入图片描述
CREATE TABLE demo_dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13)NOT NULL DEFAULT ""
)ENGINE=INNODB ;

在这里插入图片描述

#创建表 demo_emp
CREATE TABLE demo_emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,##/*编号*
ename VARCHAR(20)NOT NULL DEFAULT"",##*名字*
job VARCHAR(9)NOT NULL DEFAULT"",##*工作*
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, ##/*上级编号*
hiredate DATE NOT NULL,##*入职时间*
sal DECIMAL(7,2) NOT NULL,##*薪水*
comm DECIMAL(7,2) NOT NULL,##*红利*
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0##*部门编号*
)ENGINE=INNODB ;
  • 2.1随机产生字符串
    在这里插入图片描述
##随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghjklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUWWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i<n DO
	SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i=i+1;
	END WHILE;
	RETURN return_str;
END $$
  • 2.2随机产生部门编号
    在这里插入图片描述
#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i= FLOOR(100+RAND()*10);
RETURN i;
END $$
  • 2.3创建往emp表中插入数据的存储过程（进行一次提交）
    在这里插入图片描述

CREATE DEFINER=`root`@`%` PROCEDURE `insert_emp`(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
##set autocommit =）把autocommit设置成0
SET autocommit = 0;
REPEAT
SET i = i+ 1;
INSERT INTO demo_emp (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num END REPEAT;
COMMIT;
END

3、验证
在这里插入图片描述

  • 练习
    在这里插入图片描述

四 Profile进行sql分析

4.1 是否支持
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

查询时不要出现的：

在这里插入图片描述

4.2全局日志

在这里插入图片描述

  • 1 、配置启用在这里插入图片描述
  • 1 、编码置启用
    在这里插入图片描述

五、数据库锁机制

在这里插入图片描述
在这里插入图片描述
查看锁的命令 : show open tables;
在这里插入图片描述
释放锁

unlock 表名

在这里插入图片描述
在这里插入图片描述

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

六、ACID原理

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

事务隔离级别

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
手动提交事务
在这里插入图片描述

七、大坑

7.1 无索引行锁升级为表锁

我们知道锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁 , 从上面的测试中也可以验证这个观点。

结论
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

所以建表的时候 ，结合你的业务，如果有更新的操作，切记要对操作的字段建立索引，不然并发下这个问题就非常明显

7.2 间隙锁危害

对条件范围进行加锁
在这里插入图片描述

7.3 面试常见问题： 如何锁一行

在这里插入图片描述
杭锁总结：
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述


# 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
show STATUS LIKE 'innodb_row_lock%'

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

八、 主从复制

8.1原理

在这里插入图片描述

8.2 规则在这里插入图片描述

复制的最大问题：有延时 ！
在这里插入图片描述

一主一从配置

  1. MySQL版本必须一致。
  2. 主从都配置在[mysqld]结点下，都是小写
  3. 主机 my.ini配置
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
  4. 从机配置
  • [必须]从服务器唯一ID
  • [可选]启用二进制日志

  1. 关闭防火List item

  2. 在Windows主机上建立帐户并授权slave
    在这里插入图片描述
    在这里插入图片描述

  3. 从机配置在这里插入图片描述
    在这里插入图片描述

  4. stop slave ; -如何停止从服务复制功能

cookie3_1
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
mysql优化-sql高级
06-13
mysql优化从以下几个方面介绍 mysql的架构 索引优化分析 查询截取分析 mysql锁机制 主从复制
高级SQL优化(二)
02-26
Oracle10g起，在基于CBO的优化器模式下Oralce会进行自动优化，但在基于RBO（基于规则）的优化器模式下，依然保持此规则。1.索引对不等号和NOT的限制RBO模式下，执行计划如下：1.索引对不等号和NOT的限制RBO模式下，...
MySQL高级 - SQL优化 - or优化
Leon_Jinhai_Sun的博客
12-06 3791
优化OR条件 对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。 获取 emp 表中的所有的索引 ： 示例 ： explain select * from emp where id = 1 or age = 30; 建议使用 union 替换 or ： 我们来比较下重要指标，发现主要差别是 type 和 ref 这两项 type 显示的是访问类型，是较为重要的一个指标，结果值从好...
MySQL高级 - SQL优化 - group by 优化
Leon_Jinhai_Sun的博客
12-05 1262
优化group by 语句 由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。 如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ： drop index idx_emp_age_salar
SQL--数据库引擎优化顾问功能
Mimo_YY的博客
04-20 2652
目的：优化性能，通过优化顾问提出的优化建议来达到目的。 整体分两步： 1.跟踪出相关功能的语句（使用SQL Server Profiler跟踪工具） 2. 1.新建跟踪 2.【列筛选器】TextData类似于‘select%’ select%是个筛选监测的TextData。那个%是个通配符，他的意思就是筛选select开头的语句。当然自己可以随便定义，如update%，delete%…。 注释：如果要跟踪某个功能的脚本语句，那么不用在这里限制（即：不用动列筛选器里的内容） 如果想跟踪某一个数据库实体的脚
SQL-＞基础-＞进阶
小小IT人的专栏
10-19 3398
SQL 基础： 增删查改、多表查询更新、递归查询、索引应用、事务 进阶
【高级开发必掌握SQL】SQL优化篇
小虚竹的专栏
08-17 1万+
SQL优化
MySQL高级篇（SQL优化、索引优化、锁机制、主从复制）
热门推荐
努力让自己发光，对的人才能迎着光而来
01-24 12万+
目录0 存储引擎介绍1 SQL性能分析2 常见通用的JOIN查询SQL执行加载顺序七种JOIN写法3 索引介绍3.1 索引是什么3.2 索引优劣势3.3 索引分类和建索引命令语句3.4 索引结构与检索原理3.5 哪些情况适合建索引3.6 哪些情况不适合建索引4 性能分析4.1 性能分析前提知识4.2 Explain使用简介4.3 执行计划包含的信息字段解释（重中之重）id（表的读取顺序）select_type（ 数据读取操作的操作类型）table（显示执行的表名）type（访问类型排列）possible_k
MySQL 高级--优化 —— explain
猎人在吃肉
11-14 4824
文章目录1、id -- SQL片段的执行顺序1.1、有三种情况：1.2、示例：1.2.1、id不相同:1.2.2、id相同和不同，同时存在2、select_type -- 查询类型（6个）2.1 、simple 简单查询2.2 、primary 复杂查询，最后被执行的SQL片段2.3、subquery （子查询）2.4 、derived [dɪ'raɪvd] 衍生; 起源2.5、union2.6...
flink-sql查询配置与性能优化参数详解-1.13
第一片心意的博客
08-02 3887
本博客是对 flink 1.13 版本的 flink sql 中所有配置的翻译整理。
黑马程序员--mysql高级 --sql优化相关笔记
abaidaye的博客
01-08 2581
1.5.sql的优化 1.5.1.查看sql的执行频率 MySQL 客户端连接成功后，通过 show [session|global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。 --下面的命令显示了当前 session 中所有统计参数的值 show session status like 'Com_______'; -- 查看当前会话统计结果 show global status like 'Com_______'; -- 查看自数据库上次启动
使用 with as 优化SQL-深度剖析
认知 行动 坚持
10-31 1379
背景： 当我们书写一些结构相对复杂或者行数过多的SQL时，可能某个子查询在多个层级多个地方存在重复使用的情况。在 SQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。 WITH …… ...
高级SQL优化(一)
02-26
（题外话：优秀的Oracle数据库优化人才，是任何公司都稀缺的）SQL优化又称SQL调节，其步骤一般包括：SQL调节包括三大目标：降低负载、均衡负载和并行化负载。l降低负载：即寻找更高效的途径来完成相同的功能如某个非...
高级SQL优化(三)
02-26
SQL*PLUS下使用AUTOTRACE1.AUTOTRACE简介AUTOTRACE是SQL*Plus的一项功能，其作用是自动跟踪SQL语句，为SQL语句生成一个执行计划并且提供与该语句的处理有关的统计信息。SQL*PlusAUTOTRACE可以用来替代SQLTrace使用，...
PostgreSQL源码分析——绑定变量
让我思考一下
06-18 80
这里分析一下函数中应用绑定变量的问题，但实际应用场景中，不推荐这么使用。
PostgreSQL源码分析——CREATE SCHEMA
让我思考一下
06-18 84
这里我们对创建模式的源码进行分析。创建一个schema，主流程如下，需要注意pg_namespace系统表，所有的schema都可在这个系统表中查到，创建一个新的schema，需要向pg_namespace系统表中插入一个数据。插入模式名以及对应的namespace oid的值。所有的模式名都在pg_namespace系统表中。
PostgreSQL源码分析——CREATE DATABASE
让我思考一下
06-18 361
这里我们分析一下在PostgreSQL中创建数据库的源码，在分析源码之前，最好先阅读《PostgreSQL指南内幕探索》的第一章，数据库集簇、数据库和数据表，弄清其空间布局，理解PG中，数据库、表、元组是怎么布局的。通俗一点说，创建一个数据库相当于创建一个数据目录，在这个目录下，存放该数据库中定义的表、索引等对象。
PostgreSQL源码分析——COPY
最新发布
让我思考一下
06-18 299
这里postgres中的代码注释写的很明白，另外一点就是在语法解析，语义分析，执行计划生成这块，批量插入相比单条插入也省了很多时间，不用每条插入都执行解析、执行计划生成，只需一次即可，而copy则连计划生成这步都省了，直接批量构造tuple插入表中。函数，我们进行分析，核心流程如下，可以看到，copy from，就是跳过解析，优化，直接进入执行器，提取文件中的数据，构造tuple，每1000 tuple为一批tuple，然后执行table_multi_insert批量插入。我们来分析一下COPY的实现。
PostgreSQL源码分析——常量表达式化简
让我思考一下
06-18 146
常量表达式可以进行化简，可降低执行器计算表达式的代价。在逻辑优化阶段，会判断是否可以进行常量表达式化简，如果可以，则在执行器执行之前就预先对常量表达式树进行计算，计算出常量后，以新计算出的常量表达式代替原有的表达式。当进入执行器时，此时表达式已被替换为常量，避免了在执行器中频繁的计算表达式。可以看到，在逻辑优化阶段，常量表达式已被化简为常量，进入执行器时，表达式树已被替换为常量。除了常量表达式，常量函数也可以在逻辑优化阶段提前执行，计算得到常量，用常量替换原有的函数表达式。，是常量表达式，可以进行化简。
sqlserver-oledb-provider
07-13
SQL Server OLE DB Provider还提供了一些性能优化的功能，例如连接池和命令缓存。连接池允许开发人员重复使用数据库连接，以减少连接的开销。命令缓存可以缓存已执行的命令，以加快后续执行同样命令的速度。 总之，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

cookie3_1

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值