MySQL高级（索引、索引优化、批量插入数据 showfile、表锁行锁、主从复制）

最新推荐文章于 2023-05-27 16:57:19 发布
最新推荐文章于 2023-05-27 16:57:19 发布
阅读量433 收藏
点赞数 1
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_45234510/article/details/119352362
版权

文章目录

一、SQL相关

MyISAM和InnoDB

在这里插入图片描述

SQL性能下降的原因

在这里插入图片描述

SQL执行加载顺序

手写：
在这里插入图片描述

机读：

在这里插入图片描述

在这里插入图片描述

7种JOIN

在这里插入图片描述

在这里插入图片描述

二、索引

1、索引是什么

可以得到索引的本质：索引是数据结构。

你可以简单理解为 “排好序的快速查找数据结构” 。
在这里插入图片描述

在这里插入图片描述
结论：

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

2、索引优势

在这里插入图片描述

3、索引劣势

在这里插入图片描述

4、索引的分类及基本语法

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

5、检索原理

【初始化介绍】
一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3， P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。 真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。 非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
在这里插入图片描述

【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁 盘块1的P2指针，内存时间因为非常短(相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存 ，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29.结市香询。点计三次16~~， 真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个 数据项都要发生一次IO，那么总共需要百万次的lO，显然成本非常非常高
在这里插入图片描述

6、需要建立索引的情况

在这里插入图片描述

7、不需要建立索引的情况

在这里插入图片描述

8、Explain关键字介绍

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

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

9、Explain之id（表的读取顺序）

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

10、Explain之select_type（数据读取操作的操作类型）、table

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

11、Explain之type

在这里插入图片描述

在这里插入图片描述

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

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

12、Explain之possible_keys、key

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

13、Explain之key_len

在这里插入图片描述

在这里插入图片描述

14、Explain之 ref 介绍

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

15、Explain之rows介绍

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

16、Explain之Extra介绍

在这里插入图片描述

17、Explain之热身Case

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

三、索引优化

1、索引单表优化案例

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

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

在这里插入图片描述

2、索引两表优化案例

左连接加右索引，右连接加左索引

建表：
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

3、索引三表优化案例

建表：
在这里插入图片描述

左连接，在右边两个表加索引：

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

4、索引优化

在这里插入图片描述
口诀：

  • 1.带头大哥不能死
  • 2.中间兄弟不能断，永远符合最左前缀原则
  • 3.索引列上不计算
  • 4.范围之后全失效
  • 5.百分 like 加右边
  • 6.两边都百分号时 可建 覆盖索引 解决索引失效
  • 7.字符串引号不能缺

建表：

在这里插入图片描述

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

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

两边都百分号时 可建 覆盖索引 解决索引失效

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

小总结：

在这里插入图片描述

5、例题分析及建议

在这里插入图片描述

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

6、小结

口诀：
在这里插入图片描述

一般性建议：
在这里插入图片描述

四、其他优化

1、小表驱动大表

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

2、order by 关键字优化

在这里插入图片描述

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

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

3、group by 关键字优化

在这里插入图片描述

4、慢查询日志

在这里插入图片描述
查看与临时开启
在这里插入图片描述

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

永久开启：
在这里插入图片描述

阈值：
在这里插入图片描述
设置慢查询的值：
在这里插入图片描述
在这里插入图片描述
查找慢SQL语句：

在这里插入图片描述
开启慢查询日志后，执行sql后，即可在 /var/lib/mysql 找到日志：
在这里插入图片描述
查看即可发现慢的SQL：
在这里插入图片描述
查看当前系统有多少条慢查询记录：
在这里插入图片描述

若要配置永久生效，则如下：
在这里插入图片描述

使用【mysqldumpslow --help】查看帮助信息：

在这里插入图片描述
帮助信息：

在这里插入图片描述
常用查询：

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

五、批量插入及showfile

1、批量插入数据脚本

在这里插入图片描述

【1 建表】

在这里插入图片描述
在这里插入图片描述
【2 设置参数】

在这里插入图片描述
【3 创建函数，保证每条数据都不同】

随机产生字符串
在这里插入图片描述
在这里插入图片描述
【4 创建存储过程】

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

【5 调用存储过程】

调用部门表的存储过程，插入从 100 开始 的 10条数据：
在这里插入图片描述

在这里插入图片描述

执行员工表的存储过程，添加50万条数据：

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

2、Show Profile

在这里插入图片描述

【第一步 开启功能】
在这里插入图片描述
【第二步 执行SQL】
在这里插入图片描述
【第三步 show profile】
在这里插入图片描述

在这里插入图片描述
【第四步 查看分析】
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

六、表锁行锁

1、读锁案例分析

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

2、写锁案例分析

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

3、案例总结

在这里插入图片描述

在这里插入图片描述
表锁分析
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

4、行锁定案例分析

在这里插入图片描述

5、间隙锁

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

6、如何锁定一行

在这里插入图片描述

7、行锁总结

在这里插入图片描述

8、行锁分析

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

优化建议：
在这里插入图片描述

9、页锁

在这里插入图片描述

七、MySQL主从复制

1、复制的基本原理

在这里插入图片描述
三步骤+原理图：

在这里插入图片描述

2、复制的基本原则

在这里插入图片描述

3、主从复制配置

配置教程：windows为主，linux为从

在这里插入图片描述

x-dragon8899
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
show_file_mysel
11-20
韦东山第三期视频数码相框中的电子书项目的ShowOnePage函数解析 iLen = g_ptEncodingOprForFile->GetCodeFrmBuf(pucBufStart, g_pucTextFileMemEnd, &dwCode); 这里得到一个字 但是得到的编码并不是返回值而是存在*pdwCode里 返回的是本次一共处理了文件里的多少个字
ShowFile
百年千岁
10-27 982
/** * Use a FileReader to display a text file. * * To use this program, specify the name * of the file that you want to see. * For example, to see a file called Test.txt, * use the following co
Mysql的常用的优化（创建索引），批量数据操作（存储过程）
weixin_44657829的博客
08-10 265
关于热议常用的索引一.关于索引的一些记录1. 创建索引2.查看索引3.查看执行计划二.实例1.ddl2.通过简单的存储过程创建测试数据3.创建索引 一.关于索引的一些记录 因为业务不断的扩张，数据量日益飙升，处理的数据愈来庞大，不得更换数据库，提升配置，采取一些手段，添加索引，批量操作数据，通过存储过程大幅提升效率。 说明：本文仅记录自己工作中遇到的问题及解决方案,具体的情景待补充。 1. 创建索引 -- 添加索引 alter table tableName add INDEX index_name(fi
mysql的优化---show file分析SQL
weixin_60948956的博客
12-20 111
【代码】mysql的优化---show file分析SQL。
MySQL---使用索引优化、大批量插入数据优化
weixin_43961909的博客
05-27 1085
索引是数据库优化最常用也是最重要的手段之一。1.2 避免索引失效应用-最左前缀法则。通过索引通常可以帮助用户解决大多数的。该情况下，索引生效，执行效率高。
MYSQL开发性能研究之批量插入数据的优化方法
12-16
但是当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真...
MySQL死锁套路之唯一索引下批量插入顺序不一致
12-16
死锁的本质是资源竞争，批量插入如果顺序不一致很容易导致死锁，我们来分析一下这个情况。为了方便演示，把批量插入改写为了多条 insert。 先来做几个小实验，简化的表结构如下 CREATE TABLE `t1` ( `id` int(11...
Java高级编程——MySQL索引实现及优化原理解析
02-25
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的...
MySQL高级知识-查询与索引优化分析
02-20
下面是替代方法leftjoin+union(可去除重复数据)+rightjoin实现如下面代码7、A的独有+B的独有MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。你可以简单理解为“排好序的快速查找结构”...
showfile.c
CCodes的专栏
11-08 385
  #include void main(int argc, char *argv[])  {    FILE *fp;        // File pointer    char line[255];  // Line from file    if ((fp = fopen(argv[1], "r")) == NULL)      printf("Error opening %s
js实现展示file表单选的图片
小兀的博客
12-29 617
笔记function showFile(obj,id) { // var filePath=$(obj).val(); if (obj.files && obj.files[0]) { var imgId=id; var showImg=document.getElementById(imgId); var reader = new F
基于matlab实现的空间调制通信过程，包含信号调制、天线选择等发送过程，以及采用最大似然估计的检测过程 .rar
最新发布
05-02
基于matlab实现的空间调制通信过程，包含信号调制、天线选择等发送过程，以及采用最大似然估计的检测过程。.rar
基于matlab的关于生猪养殖场经营管理的研究.docx
05-02
本文档是课题研究的研究报告内含调研以及源码设计以及结果分析
网络作为特征提取器-python源码.zip
05-02
网络作为特征提取器-python源码.zip
JavaScript-javaweb项目
05-02
JavaScript-javaweb项目
node-v12.11.0-linux-arm64.tar.xz
05-02
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
仿Slideby触屏版html5响应式手机wap网站模板下载.zip
05-02
触屏版自适应手机wap软件网站模板 触屏版自适应手机wap软件网站模板
node-v10.18.1-linux-arm64.tar.xz
05-02
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
IEC 60695-11-3：2012.pdf
05-02
IEC 60695-11-3：2012.pdf
mysql 表锁 行锁
08-12
MySQL提供了两种类型的锁：表锁（Table Lock）和行锁（Row Lock）。 表锁是对整个表进行锁定，当一个事务获取了表锁后，其他事务无法对该表进行任何修改操作，直到该事务释放了表锁。表锁适用于对表进行大批量的读写操作，但是会造成并发性能的下降。 行锁是对表中的行进行锁定，当一个事务获取了某行的行锁后，其他事务只能读取该行的数据，而不能修改该行的数据，直到该事务释放了行锁。行锁适用于对表中少量行进行读写操作，可以提高并发性能。MySQL的默认隔离级别为可重复读（REPEATABLE READ），在这个隔离级别下，MySQL会自动对涉及到的行进行行锁。 在使用行锁时，需要注意以下几点： 1. 行锁会占用更多的系统资源，因此在需要大量并发操作的情况下，可能会导致性能问题。 2. 行锁可能引起死锁问题，即多个事务互相等待对方释放行锁而无法继续执行。为了避免死锁，可以合理设计事务的顺序或者使用事务超时机制。 3. 行锁只对修改操作起作用，在读取数据时并不会对行进行锁定。 总的来说，表锁适用于对整个表进行大批量操作，行锁适用于对少量行进行读写操作。在实际应用中，需要根据具体场景选择合适的锁机制。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值