SQL高级 --优化

文章详细介绍了SQL查询的解析,特别是mysqlexplain的使用,包括id、select_type、type等关键列的解释,以及如何识别和优化索引失效、文件排序和临时表的使用。此外,还讨论了批量导入数据时的注意事项,如创建存储过程和行锁升级问题。最后,提到了主从复制的基本原理和配置要点,强调了延迟问题和事务隔离级别的重要性。
摘要由CSDN通过智能技术生成

一、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 ; -如何停止从服务复制功能

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

打赏作者

cookie3_1

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

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

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

打赏作者

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

抵扣说明:

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

余额充值