Mysql数据库问题

一、索引

  • 索引分类：主键索引，普通索引，复合索引，唯一索引
  • 技术名词：回表，最左匹配，索引覆盖，索引下推

二、explain

之前已有文章讲解：优化器-SQL语句分析与优化

这里我再写一下。

2.1 id

id:select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序；

2.2 select_type：

  • SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION；
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary；
  • DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里；
  • SUBQUERY：在SELECT或WHERE列表中包含了子查询；
  • DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查询,子查询基于外；
  • UNCACHEABLE SUBQUREY：无法被缓存的子查询；
  • UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED；
  • UNION RESULT：从UNION表获取结果的SELECT；

2.3 table：

table:显示这一行的数据是关于哪张表的；

2.4 type

  • system:表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计；
  • const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量；
  • eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；
  • ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体；
  • range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
  • index:Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）；
  • all:Full Table Scan，将遍历全表以找到匹配的行；
  • index_merge:在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中；
  • ref_or_null:对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询；
  • index_subquery:利用索引来关联子查询，不再全表扫描；
  • unique_subquery :该联接类型类似于index_subquery。 子查询中的唯一索引；

备注：
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range(尽量保证) > index > ALL

常用type：

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

2.5 possible_keys

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

2.6 key

key：实际使用的索引。如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

2.7 key_len

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引。

2.8 ref

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

2.9 rows

rows：rows列显示MySQL认为它执行查询时必须检查的行数。越少越好。

2.10 Extra

Extra：包含不适合在其他列中显示但十分重要的额外信息。

  • Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中
    无法利用索引完成的排序操作称为“文件排序”
  • Using temporary:使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和
    分组查询 group by。
  • USING index:表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
    如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来
    读取数据而非利用索引执行查找。
  • Using where:表明使用了where过滤；
  • using join buffer:使用了连接缓存；
  • impossible where:where子句的值总是false，不能用来获取任何元组；
  • select tables optimized away:在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

三、索引失效

  • 全值匹配我最爱；
  • 最佳左前缀法则；
  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描；
  • 存储引擎不能使用索引中范围条件右边的列；
  • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *；
  • mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；
  • is not null 也无法使用索引,但是is null是可以使用索引的；
  • like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作；
  • 隐式类型转换索引失效；
  • 少用or,用它来连接时会索引失效；

四、MySQL/InnoDB 事务隔离级别分享

4.1 一条语句的执行过程

在这里插入图片描述
redo log写入拆成了两个步骤： prepare和commit，这就是大名鼎鼎的两阶段提交。

  • 深色部分代表service层流程，浅色部分代表Innodb内部流程；
  • 两阶段提交就是为了保证binlog内容和redo log内容一致；；
  • redo log：保证了数据持久性；

备注：

很多业务只要做到【读已提交】就行了，不用做到【可重复读】。

4.2 事务+锁+日志：

略；见PDF

4.2.1 隔离性案例

RR级别下： 我们继续看一组案例：
开始时： status=1
在这里插入图片描述

结果：
在这里插入图片描述
1. session2里面查询的结果， status=3;
2. session1里面查询的结果， status=1;

结论与你了解的事务隔离是否矛盾？

原理:

  • session2为什么是3，因为RR隔离级别下，都会使更新操作，最新值生效。（更新都是在最新的版本上更新的）；
  • session1最终查到的status值是1，比较好理解，一直事务进来后，数据库是什么样，后面无论怎么查询，值都不变；

上述session2和session3，两条update语句，能同时更新吗？
——不能，update语句有锁的存在。

五、一些需要注意的事项

5.1 count(*),count(1),count(主键id),count(字段),到底用谁？

  • count(字段):select from 表+where 条件判断：正常没有落在缓存，是在数据页上，在innodb引擎层里，读一条拿回来判断…；
  • 对于count(*)和count(主键)是一样的，select这张表，符合记录的数就取回来，有一个从innodb取值的过程；（但是count(*)毕竟特殊）；

但是mysql对count(*)进行了特殊优化：count时候不取值，只加值；少了一次数据复制，这层复制就是innodb引擎向service层复制数据的过程，即使复制了一个小的字段，也是有io上的消耗的的。 所以count(*)性能最好。

count(字段)：第一：业务语义不一样；第二：涉及到判断空还是非空的问题，效率最慢。

5.2 普通索引和主键索引到底有没有区别？

innodb引擎，唯一索引和普通索引性能上谁更好？

  • 普通索引：比方说查一个要索引值为2的数据，普通索引查到了，还要往下找；但是其实性能差距不大，基本等于和唯一索引性能没有区别，因为mysql是b+树，且有序的，在一页中普通索引往下查还挺快的，除非2在页的最后一个节点还有（也就是还要查下一页的数据）；
  • 唯一索引：查一个要索引值为2的数据，查到了，就不会往下找了；

查询：唯一索引性能领先，但领先幅度不大；
插入或更新：唯一索引性能性能不如普通索引， 因为唯一索引有一个判断是否唯一的过程；

结论：唯一索引和普通索引性能只有在更新上性能有区别，普通索引性能更好；查询上性能区别不大；

5.3 很多时候第二次或者后面的多次查询同一条语句，速度变快了，是什么原因？

  有时候是因为mysql有查询缓存；
  也有可能是这样的：mysql的索引是一种数据结构，一开始是在磁盘里的，访问一次，会加载到内存当中的，这样就少了一次查询b+数的过程；

一般来说，生产当中，mysql服务器的内存都很大，为什么？
——就是为了存各种各样的buffer，有查询的缓存，有更新的缓存，有binlog刷日志的缓存，等等；

一页能存多少数据结构?
——略；
MySQL的一个数据页大小 mysql一页可以存储多少数据

5.4 其他

  • redo log 主要节省的是随机写磁盘的IO(顺序写)；
  • change buffer主要节省随机读磁盘的IO消耗；

就像5.3提到的buffer，更新时：

  • 普通索引：先更新的缓存，就是change buffer。 更新时：先写change buffer，后写日志；所以这条数据就被持久化了，没丢；
  • 唯一索引：没有经过change buffer，直接查库有没有值，所以上面说的更新性能就慢在这了。

5.5 我把我这个项目对应的mysql的进程全部kill掉，会怎么样？

  • 有@Trancation注释的方法会回滚吗？——会；
  • 应用服务器还会重新连接吗？应用要重启吗？——连接池是能重新建立连接的，不用重启；

六、处理问题一些技巧

  1. 慢sql定位：开启慢日志;
  2. 大事务处理：SELECT * FROM information_schema.INNODB_TRX;
  3. 降低死锁概率：控制并发度；

七、死锁

7.1 场景

场景：

  1. 用户A余额支付金额给商家B：update t set money = money-100 where user =‘A’;
  2. 商家B余额增加:update t set money = money+100 where user =‘B’;
  3. A生成订单日志:insert …

如何设计三条语句的顺序?

——应该是3>1>2;

分析：

  1. 不容易被锁，因为买东西的用户A，同时买东西的情况很少；
  2. 容易被锁，商家user=B，同时卖出东西的情况，肯定是多的；

应该把最容易锁的语句放到最后执行，尽可能让锁的时间变短；

这样是降低了锁的概率，但是还有一种情况避免不了的：死锁；

7.3 死锁不可避免

事务1：
在这里插入图片描述
事务2：
在这里插入图片描述
按顺序执行1，2，3，4步骤，在执行4后，会发生什么现象？
——死锁了。

死锁报错，执行4步骤后：
在这里插入图片描述

问题：deadlock之后，事务会自动关闭吗？如果不会，需要手动操作吗，怎么操作？

  • mysql死锁不危险，不会导致mysql挂掉，但会有性能上的影响；mysql死锁避免不了；
  • jvm死锁很危险，会导致jvm挂掉；

再回到上述场景，用户A在买了商户B的东西过程中，又买了其他商户的东西，这过程也有概率发生死锁的。

死锁检测：当mysq更新一条语句的时候，只有发现这条记录已经被别人拿到锁了，才会进行死锁检测。

上述场景减少死锁的方案：

  1. 系统设计过程中，给商家开多个账号，在一张表中有多条账户记录，每次都随机商家账号，增加金额；
  2. 让sql执行有序，用mq，或者分布式锁，都一定程度上能降低死锁概率；

7.4 MDL锁

alert table（MDL锁-写锁），尽量让dba执行，alert极容易产生锁，而且锁的时候不知道，而别的业务线程全部中断了；
写锁是等读锁释放的时候才会写，在写的时候，后面的线程又被锁住了，极其容易造成死锁。

八、select语句执行流程：

  1. 如果数据库刚启动，缓存（不是查询缓存，查询缓存互联网公司一般关了，因为只要数据更新，就得维护它，麻烦）空空如也；——这里缓存，指的是MySQL的pull buffer（可以存索引数据），缓存没有，就找索引，找到索引了，就去硬盘上找数据记录，同时该索引所在的页，都别拿到内存中了（下次再查这条语句就很快了，因为拿到内存中了）。读到之后，数据一般不在缓存当中；
  2. 拿到数据之后，如遇到order by，就涉及到排序，优先放到内存中排序，内存放不下，就放到硬盘中排序；如果order by 是索引，天生有序，无序额外操作；
  3. 如果有limit，找到一条，判断合适不合适，合适-放到内存里，继续下一条…
  4. 如果是join操作，上面3的步骤要重复笛卡尔积次；

九、一般大厂数据库规约

略；参考文章：待写；

9.1 建立组合索引，必须把区分度高的字段放在前面

解读：能够更加有效的过滤数据。

A字段和B字段查询时都会用到，建立组合索引，和分别个A和B都建立索引，在执行的时候，有啥不一样？
答：组合索引查询效率更高，一次性就检索出来了。而后者，mysql执行前会进行分析，最终可能只走了A索引，也可能只B索引，也可能两个索引都走了，最后取交接。
反正性能就是没有组合索引高；

9.2 order by

在这里插入图片描述

  • 如果c是索引列：天生有序；
  • 如果c不是索引列

9.2.1 Using filesort分析

在这里插入图片描述
用到了文件排序，用文件排序不一定慢，什么时候会慢？
——查询*的时候，会从数据列中拿出来，可能放到文件，也可能是在内存（【查询缓存】）。当数据少的时候，内存处理可以排序，那性能就还好；
但就是怕这条sql取的数据过大，【查询缓存】放不下，就会落到文件上（硬盘），这时候排序就慢了。

措施：

  • order by 尽量用索引列。
  • 索引缓存、排序缓存放大；——生产上，从库缓存可以配大一点；

9.3 不用join，用啥

——join最多的情况用在分页上。
字典表、配置表连连影响不大；
但是，如果是两张很大的表连表，影响就很大了，比如两张100w，产生的笛卡尔积：100w*100w。

解决方法1：先条件筛选，再进行连表:

select at.* (select * from a where a.xx=xx and ...) at
 join ( select b.* from b where b.xx=xx and ...) bt
 on at.xxx=b.xxx

解决方法2：
——数据冗余；

9.4 jdk stream的并发流——parallel()，慎用

jdk stream的并发流——parallel()，慎用！会有一些cpu的性能上的问题；

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

爬虫案例1

爬虫案例1

通过get请求直接获取电影信息 目标页面: https://spa6.scrape.center/在network中可以看到是通过Ajax发送的请求&#xff0c;这个请求在postman中也可以直接请求成功&#xff0c;这只是一个用来练习爬虫的&#xff0c;没有达到js逆向的过程&#xff0c;需要通过分析js 代码来获…
阅读更多...
C++开发基础——IO操作与文件流

C++开发基础——IO操作与文件流

一&#xff0c;基础概念 C的IO操作是基于字节流&#xff0c;并且IO操作与设备无关&#xff0c;同一种IO操作可以在不同类型的设备上使用。 C的流是指流入/流出程序的字节序列&#xff0c;在输入操作中数据从外部设备(键盘&#xff0c;文件&#xff0c;网络等)流入程序&#x…
阅读更多...
lnmp环境部署-im

lnmp环境部署-im

安装nginx 配置nginx源 vim /etc/yum.repos.d/nginx.repo [nginx-stable] namenginx stable repo baseurlhttp://nginx.org/packages/centos/$releasever/$basearch/ gpgcheck1 enabled1 gpgkeyhttps://nginx.org/keys/nginx_signing.key module_hotfixestrue安装nginx yum …
阅读更多...
【开源】SpringBoot框架开发假日旅社管理系统

【开源】SpringBoot框架开发假日旅社管理系统

目录 一、摘要1.1 项目介绍1.2 项目录屏 二、功能模块2.1 系统介绍2.2 QA 问答 三、系统展示四、核心代码4.1 查询民宿4.2 新增民宿评论4.3 查询民宿新闻4.4 新建民宿预订单4.5 查询我的民宿预订单 五、免责说明 一、摘要 1.1 项目介绍 基于JAVAVueSpringBootMySQL的假日旅社…
阅读更多...
产品推荐 - ALINX XILINX FPGA开发板 Artix-7 XC7A100T-2FGG484I

产品推荐 - ALINX XILINX FPGA开发板 Artix-7 XC7A100T-2FGG484I

01开发板介绍 此款开发板采用核心板扩展板的模式&#xff0c;方便用户对核心板的二次开发利用。FPGA使用的是Xilinx公司的ARTIX-7系列的芯片&#xff0c;型号为XC7A100T-2FGG484I。在核心板使用了2片MICRON公司的MT41J256M16HA-125 DDR3芯片&#xff0c;组合成32bit的数据总线…
阅读更多...
Java日志框架Log4j 2详解

Java日志框架Log4j 2详解

目录 一、什么是日志&#xff1f; 二、日志的主要用途 三、常用日志框架 1、Apache Log4j 2、Commons Logging 3、SLF4J 4、Logback 5、JUL(Java Util Logging) 6、Log4j 2 四、log4j 2 的优点 五、Log4j 2下载和配置 1、访问Log4j – 下载 Apache Log4j™ 2官网&a…
阅读更多...
Linux内核之kstrdup代码实例(二十六)

Linux内核之kstrdup代码实例(二十六)

简介&#xff1a; CSDN博客专家&#xff0c;专注Android/Linux系统&#xff0c;分享多mic语音方案、音视频、编解码等技术&#xff0c;与大家一起成长&#xff01; 优质专栏&#xff1a;Audio工程师进阶系列【原创干货持续更新中……】&#x1f680; 优质专栏&#xff1a;多媒…
阅读更多...
【数据结构】线性表的定义及基本操作

【数据结构】线性表的定义及基本操作

文章目录 前言线性表的定义线性表的基本操作基本操作其他常用操作 总结 前言 数据结构的三要素是逻辑结构、数据的运算、存储结构&#xff08;物理结构&#xff09;&#xff0c;存储结构不同&#xff0c;运算的实现方式也不同。 本次文章包括线性表的定义和基本操作&#xff0…
阅读更多...
rancher里的ingress如何配置gzip压缩

rancher里的ingress如何配置gzip压缩

方案一&#xff0c;未试验成功&#xff0c;但配置过程值得记录一下 通过配置configmap&#xff0c;然后在ingress的deployment里引用configmap实现。 参考文章 创建configmap apiVersion: v1 kind: ConfigMap metadata:name: nginx-ingress-controllerannotations:{} # k…
阅读更多...
Mybatis的XML配置文件

Mybatis的XML配置文件

Xml文件中写SQL 为什么要学? 学习了Mybatis中XML配置文件的开发方式了&#xff0c;大家可能会存在一个疑问&#xff1a;到底是使用注解方式开发还是使用XML方式开发&#xff1f; 官方说明&#xff1a;https://mybatis.net.cn/getting-started.html 结论&#xff1a;使用Myba…
阅读更多...
信号处理--基于gumbel-softmax方法实现运动想象分类的通道选择

信号处理--基于gumbel-softmax方法实现运动想象分类的通道选择

目录 背景 亮点 环境配置 数据 方法 结果 代码获取 参考文献 背景 基于Gumbel-softmax方法EEG通道选择层的PyTorch实现。该层可以放置在任何深度神经网络架构的前面&#xff0c;以共同学习给定任务和网络权重的脑电图通道的最佳子集。这一层由选择神经元组成&#xff0c;每个神…
阅读更多...
【Node.js相关问题】npm install报错后重装node版本及npm环境变量配置及npm run dev启动报错原因分析解决办法

【Node.js相关问题】npm install报错后重装node版本及npm环境变量配置及npm run dev启动报错原因分析解决办法

一、问题描述 昨天在准备打开b站up主三更草堂的博客项目08-02.基础版本前端联调_哔哩哔哩_bilibili中的前端工程时&#xff0c;使用以下两个命令分别都出现了报错。 命令1&#xff1a; # install dependenciesnpm install 命令2&#xff1a; # serve with hot reload at loca…
阅读更多...
[vscode]将命令行参数传递给调试目标

[vscode]将命令行参数传递给调试目标

一、简介 本文介绍了在vscode中使用cmake工具时&#xff0c;如何传递参数给编译目标的方法。 前提&#xff1a;使用vscodecmake编译C/C程序。 二、方法 在.vscode/目录下新建settings.json文件&#xff0c;并将待传底的参数写在 cmake.debugConfig里。 下面介绍了一个示例&a…
阅读更多...
pymysql连不上mysql的原因

pymysql连不上mysql的原因

我试了两种解决办法。可以参考一下 第一种&#xff1a;查看有没有打开mysql服务 第二种&#xff1a;刷新 MySQL 用户权限 password改成自己的密码 GRANT ALL PRIVILEGES ON *.* TO root% IDENTIFIED BY password WITH GRANT OPTION;FLUSH PRIVILEGES; 第三种&#xff1a;检…
阅读更多...
CCCorelib 点云曲面特征（CloudCompare内置算法库）

CCCorelib 点云曲面特征（CloudCompare内置算法库）

文章目录 一、简介二、实现代码三、实现效果参考资料一、简介 这里基于每个点的邻域协方差来获取点云中具有的曲面几何特征的点，计算方式如下图所示： 二、实现代码 // CloudCompare #include <CCCoreLib/PointCloudTpl.h> #include <CCCoreLib/
阅读更多...
多维时序 | Matlab实现VMD-CNN-BiLSTM变分模态分解结合卷积神经网络结合双向长短期记忆神经网络多变量时间序列预测

多维时序 | Matlab实现VMD-CNN-BiLSTM变分模态分解结合卷积神经网络结合双向长短期记忆神经网络多变量时间序列预测

多维时序 | Matlab实现VMD-CNN-BiLSTM变分模态分解结合卷积神经网络结合双向长短期记忆神经网络多变量时间序列预测 目录 多维时序 | Matlab实现VMD-CNN-BiLSTM变分模态分解结合卷积神经网络结合双向长短期记忆神经网络多变量时间序列预测预测效果基本介绍程序设计参考资料 预测…
阅读更多...
微信小程序小白易入门基础教程1

微信小程序小白易入门基础教程1

微信小程序 基本结构 页面配置 页面配置 app.json 中的部分配置&#xff0c;也支持对单个页面进行配置&#xff0c;可以在页面对应的 .json 文件来对本页面的表现进行配置。 页面中配置项在当前页面会覆盖 app.json 中相同的配置项&#xff08;样式相关的配置项属于 app.js…
阅读更多...
＜2024最新＞ChatGPT逆向教程

＜2024最新＞ChatGPT逆向教程

前言 在使用本篇文章用到的项目以及工具时，需要对其有一定的了解，无法访问以及无法使用的问题作者不承担任何责任，可以自行想办法解决遇到的问题​。 文章若有不合适，有问题的地方，请私聊指出，谢谢~ 准备工具 一台至少 2 核 2G 内存的服务器，推荐是位于香港、新加坡或…
阅读更多...
微服务day01 -- SpringCloud01 -- (Eureka , Ribbon , Nacos)

微服务day01 -- SpringCloud01 -- (Eureka , Ribbon , Nacos)

介绍微服务 1.认识微服务(p1-p5) 随着互联网行业的发展&#xff0c;对服务的要求也越来越高&#xff0c;服务架构也从单体架构逐渐演变为现在流行的微服务架构。这些架构之间有怎样的差别呢&#xff1f; 1.0.学习目标 了解微服务架构的优缺点 1.1.单体架构 单体架构&#…
阅读更多...
vue.js项目评估流程图特效

vue.js项目评估流程图特效

vue.js项目评估流程图特效是一款带节点流程支持增加删除编辑的结构图代码。 下载地址 vue.js项目评估流程图特效
阅读更多...
最新文章

Copyright @ 2022~2023