MySQL复习过程中的查漏补缺

最新推荐文章于 2024-11-08 22:59:30 发布
最新推荐文章于 2024-11-08 22:59:30 发布
阅读量185 收藏
点赞数
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43822703/article/details/120239234
版权

这篇的内容是自己复习MySQL过程中觉得比较难，自己还没有完全掌握的部分，今天刚整理完，分享出来希望对一些人有帮助

MySQL

1 数据类型相关

1.1 varchar

  1. 在MySQL5.0后，varchar(20)指的是20个字符【根据编码不同占的字节数不同】，最大大小65532字节

    字符若为gbk，每个字符最多占2个字节，最大长度【即括号里的值】不超过32766

    字符若为utf8，每个字符最多占3个字节，最大长度不超过21845

1.2 limit语句

limit 5,10 //检索6-15
limit 95,-1  //检索96-last
limit 5 //检索前5个

2 索引

2.1 MySQL索引底层是怎样的？

  1. MySQL索引其实就是当我们插入数据时，对于插入的数据，根据我们建立的索引进行排序，并通过指针将数据连接起来；同时为了进一步优化查询效率，采用B+树的结构，对数据进行分页存储，每一页大小16KB；并且，顶层索引常驻内存，对于3层B+树结构，进行一次查询最多进行2次磁盘IO。

2.2 聚簇索引和非聚簇索引？

  1. 聚簇索引：聚簇索引就是叶子节点中将数据和索引放到一起。

  2. 非聚簇索引：在聚簇索引基础之上创建。叶子节点存储的是辅助索引和对应数据的聚簇索引键。【即需要二次查找】

    为什么不记录内存地址，记录聚簇索引键？

    1. 如果存地址，增删改时由于内存变化还需要维护非聚簇索引，成本高

    像复合索引、前缀索引、唯一索引都是属于辅助索引

2.3 innoDB和MyISAM

  1. innoDB中默认为主键建立聚簇索引，若没定义主键，innoDB会选择唯一且非空的索引代替，若不存在，则会隐式定义一个主键作为聚簇索引【如果设置了主键为聚簇索引又希望单独设置聚簇索引，需要先删除主键，后添加我们想要的聚簇索引，再恢复设置主键即可】
  2. MyISAM使用非聚簇索引，叶子节点存储的都是索引和一个指向真正表数据的指针。【因此通过辅助索引也不需要二次查找】

2.4 innoDB的优势在哪里？

  1. 由于聚簇索引采用分页存储，当我们访问同一页的不同数据时，页已经提前加载到缓冲区，可以减少磁盘IO操作，提高访问速度
  2. 维护成本低，innoDB中辅助索引叶子节点存储的是主键值，当发生数据的增删改时，我们只需要维护聚簇索引。同时，由于存储的是主键值，减少了辅助索引占用的空间大小

2.5 使用聚簇索引要注意什么？

  1. 不建议使用uuid，太过离散，当我们插入时可能要对页中数据进行移动，维护成本高；建议使用雪花算法【在分布式系统中产生全局唯一且层递增趋势的ID】

int 不利于分布式。

2.6 索引失效

  1. 使用模糊查询like且第一个字符串为%，索引会失效
  2. 使用or关键字时，如果**or前后有一个条件的列不是索引**，索引失效
  3. 不要对索引进行操作（计算、函数、自动或者手动的类型转换），否则索引失效
  4. 范围查询之后的索引全部失效

  • 针对复合索引：

    1. 不要跨列或者无序使用；

    2. 不要使用不等号（!=和<>）或者is null，否则自身及其右侧的索引全部失效

3 事务

3.1 如何理解innoDB中的事务？

  1. 事务可以使得一组操作要么都成功，要么都失败
  2. 而事务有四大特性

3.2 事务的四大特性？

  1. 原子性（Atomic）：事务的原子性就是指当前事务中的的全部操作要么都成功要么都失败；

    原子性通过undo log日志来保证，undo log记录着数据的版本变更记录；可以简单理解成比如我们要执行insert操作，undolog会记录一条对应的delete日志，我们要执行update操作，undolog会记录对应的旧值的update操作。

    如果事务执行过程中出现了异常，就利用undolog记录的数据来进行回滚

  2. 一致性（Consistency）：事务的一致性保证数据库的事务执行前后数据库由一种正确状态转移到另一种正确状态。

    比如事务的执行到一半出现了异常，事务就需要回滚，而不是强行提交导致数据不一致。

  3. 隔离性（Isolation）：事务的隔离性保证事务在并发执行时，内部操作互不干扰

    因为如果说，多个事务在同一时间操作同一份数据，就可能导致脏读，可重复读，幻读的问题

    因此，在数据库中定义了四种隔离级别给我们使用，分别是：

    1. read uncommited读未提交
    2. read commited读已提交
    3. repeatable read可重复读
    4. serializable串行

    在innoDB中默认的隔离级别是可重复读，事务的隔离性是通过MySQL的各种锁来实现的

  4. 持久性（Durability）：事务的持久性保证事务一旦提交，数据就会持久化到硬盘上或者说，事务提交后对于数据库的改变是永久性的。而事务的持久性是通过redo log日志来保证，当我们修改数据时，MySQL会把数据对应的页加载到内存中对其修改【脏页】，当修改完后会将改变写入到redo log，记录了我们的操作，这样即便MySQL在中途宕机，也可以根据redo log进行恢复；redo log是物理日志，文件体积小，恢复快。

    redo log一部分在内存一部分在磁盘，事务提交时刷新到磁盘

    1. 两阶段提交：为了保证redolog和binlog的数据一致；因此才能使用redolog将数据库恢复到crush前，用binlog实现数据备份、恢复以及主从复制；
    2. 两阶段提交细节：写完 redolog，标记为prepare状态，【并在redolog中记录一个XID】；之后写binlog【也有一个XID】，再将redolog的状态标记为commit；

4 锁

明确：之所以把锁和事务分开是因为锁太多内容

  1. 在innoDB引擎之下，按锁的粒度大小可以将锁分为行锁和表锁，行锁实际上是作用于索引之上【取决于是否命中索引】

    当我们SQL命中了索引，会把命中条件的索引节点锁住【行锁】，其他线程无法在通过这个索引找到data；如果没有命中索引，我们锁的就是整张表【表锁】

  2. 而行锁又可以分为读锁和写锁，读锁是共享锁，多个事务可以同时读取同一资源，但不允许其他事务修改。写锁是排他锁，会阻塞其他写锁和读锁。

  3. 在锁的维度而言其实就是在read uncommit隔离级别下，读不加锁，写加排他锁【读不加锁，排他锁就无法排它了】，但这种情况下，写的时候加锁，而读不加锁，导致读到了脏数据，也就是脏读。【脏读在生产环境下是无法接受的，那我们为了解决脏读就需要使用下一个隔离级别read commit。在read commit中，如果说我们采用读锁解决脏读，并发度会极度下降；因此引出了MVCC多版本并发控制的概念，做到读写不阻塞的同时，避免了脏读】

  • innoDB对于MVCC的实现：innoDB为每行数据添加三个隐藏字段和ReadView
    • DB_TRX_ID(6字节)：表示最后一次插入或更新该行的事务ID。此外，delete操作也视为更新，只是会在记录头Record header中的deleted_flag字段将其标记为已删除【组织语言：在记录头用一个删除标记标记其为已删除】
    • DB_ROLL_PTR(7字节)：回滚指针，指向该行的undo log
    • DB_ROW_ID(6字节)：没有设置主键且没有唯一且非空索引，innoDB用此id建立索引

  1. 在read commit隔离级别之下，每次select都会生成一个ReadView；而read repeatable隔离级别下，只在事务开始后第一个select语句生成readview【readview中包含当前活跃事务id列表，当前活跃最小id，即将分配的下一个事务id，生成该readview的id】，有了readview和隐藏字段中的trx_id和rollpointer之后，我们在每次访问数据时，通过比较readview中的字段和隐藏字段就可以解决脏读和不可重复读的问题；具体如下：

    1. readview中的创建事务id若和数据的事务id一致，则表示访问自己修改的数据，可以访问该版本数据
    2. readview中的最小活跃事务id如果比数据的事务id大，则表示修改数据的事务已经提交，可以访问该版本数据
    3. readview中的下一个事务id如果小于等于数据的事务，则表示修改数据的事务在当前事务生成readview后才开启，因此不可访问该版本数据
    4. 数据的事务id在readview的活跃列表里，不可访问；否则表示已提交，可以访问
    • 如果说，不可访问该版本数据，则 通过rollpointer指向的undolog取出快照记录，用快照记录中的trxid重新判断，直到找到满足的快照版本或返回空

  2. 因此，我们在read commit隔离级别解决了脏读，在read repeatable隔离级别解决了不可重复读；

  3. 而在innoDB下的read repeatable，我们通过事务版本控制，实际上已经解决了快照读的幻读问题；而如果是当前读，我们通过行锁和间隙锁合并的Next-key锁解决幻读问题【行锁防止别的事务删除读行，间隙锁防止别的事务增加行】！！！！！！！！！！！重要！！

  4. 最后一个隔离级别是serializable，不允许事务的并发，事务与事务之间的执行是串行的，效率最低，却也最安全

    • 快照读：就是select
      • select * from table ….;
    • 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
      • select * from table where ? lock in share mode;
      • select * from table where ? for update;
      • insert;
      • update ;
      • delete;

5 调优

5.1 关于索引的规范和使用

  1. 是否能使用覆盖索引，减少回表消耗时间，同时也意味着在select时要指明对应的列，而不是select *
  2. 考虑建立联合索引，要把区分度高的放在尽可能靠左，因为最左匹配原则
  3. 不要对索引进行计算或者类型转换
  4. 开启事务之后尽可能只进行数据库操作，并有意识减少锁占用时间
  5. explain

5.2 事务隔离级别相关

  1. MySQL默认使用RR，一般互联网公司采用RC，并发度更高，避免间隙锁导致死锁问题，而MySQL之所以使用RR，是因为在MySQL5.1以前，binlog没有row模式，在RC隔离级别下，使用statemnt模式会出现主从不一致的问题，为了兼容以前只有statement故使用RR为默认隔离级别。

5.3 即使走了索引还很慢

  1. 可能数据量太大了，如果可以删就删一些旧数据，不过一般不会删

5.4 读写性能瓶颈

  1. 考虑架构，如果是单库，考虑升级主从，实现读写分离；主库接收写请求，从库接收读请求，从库的数据由主库发送的binlog进而更新，实现主从的最终一致性！！

补充面试题

1 什么是回表？

  1. 当我们使用非聚簇索引查询数据并想要查询除了索引列的其他列时，由于非聚簇索引叶子结点存储的是当前列和主键值，所以还需要根据主键值去聚簇索引树在进行一遍查找，得到想要的列

  2. 想要避免回表，可以使用覆盖索引

2 什么是覆盖索引？

  1. 我们为想要查询的字段建立联合索引，这样在非聚簇索引时，我们叶子结点就已经存储了所有我们想要的列，这样就不需要进行回表操作了。
  2. 而联合索引存在最左匹配原则

3 最左匹配原则？

  1. 比如我们为(a,b,c)建立了联合索引，查询条件是where a=1 and b=2 and c = 3，那么我们在查询时，其底层会优先匹配最左边的索引。
  2. 而由于索引的最左匹配原则，在某些情况下我们的索引就会失效

4 redolog与binlog

redo logbin log
日志类型物理日志（记录某个数据也上做了什么修改，恢复快）逻辑日志（即）
存储格式页statement：SQL语句；row：行；mixed：混合
用途重做数据页数据复制，
所处层级innoDB存储引擎存储引擎上层，因此与引擎类型无关
Jiakaic
关注
MYSQL 查漏补缺+复习
小伟的博客
07-23 201
MYSQL查漏补缺MYSQL知识扫盲建表正则事务MySQL 字符串函数MySQL 数字函数MySQL 日期函数生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 MYSQL知识扫盲 建表 drop TABLE IF EXISTS test_chw; CREATE TABLE IF NOT EXISTS test_chw ( `i
MySQL查漏补缺加复习
qq_42727102的博客
02-16 243
MySQL（DOS下） 登录：mysql -uroot -p 查询所有数据库的名称：show databases; 查询某个数据库的字符集(查看某个数据库的创建)：show create database 数据库名称; DDL：数据定义语言，用来定义数据库对象：数据库，表，列等。关键字：create、drop、alter 数据库： 1.创建数据库：create databases 数据库名称; ​...
Mysql复习查漏补缺-----select语句篇
cxykang的博客
07-07 113
1.列的别名 （1）重命名一个列 （2）别名是跟在列名的后面，之间加上AS，也可以省略AS，别名可加双引号也可以不加（如果加上以便在别名中包含空格或特殊字符并区分大小写）. 示例：select last_name as name,commission_pct com from employees; select last_name "Name",salary*12 "Annual Salary" from employees; （3）别名用双引号字符串用单引号. 2.去除重复行 在SELECT
MySQL 学习之查漏补缺
weixin_34224941的博客
06-19 212
1、InnoDB 相关知识点 　　InnoDB 引擎是将数据划分为若干数据页，页大小一般16 KB，16384个字节。 　　插入数据是以记录为单位，这些记录在磁盘的存放方式称之为 行格式/记录格式，有 compact、Redundant、Dynamic和Compressed 四种格式。 　　1）、Compact 行格式 　　一条完整的记录：记录的额外信息（变长字段长度列表、NULL 值列表...
Mysql复习查漏补缺--逻辑运算符和位运算符的使用
cxykang的博客
07-07 283
1.逻辑运算符 逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。 注：在使用逻辑运算符时要考虑到运算符的优先级AND的优先级要高于OR 2.位运算符 位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算， 最后将计算结果从二进制变回十进制数。 ......
Mysql复习查漏补缺--算术运算符的使用
cxykang的博客
07-07 119
算数运算符：+，-，*，/ div,% mod; 1.加法与减法运算符 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数； 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数； 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的； 在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数 值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL 中字符串拼接要使用字符串函数CONCAT()实现）
Spring MVC复习（个人查漏补缺）
gxzhaha的博客
06-08 187
三层架构 表现层：web层，用来和客户端进行数据交互 业务层：处理公司具体的业务逻辑 持久层：用来操作数据库的 MVC模型 模型视图控制器 SpringMVC是基于java实现的MVC设计模型的请求驱动类型的轻量级WEB框架 入门程序 pom文件 <!-- 版本锁定 --> <properties> <spring.version>5.0.2.RELEASE</spring.version> </properties...
Mybatis复习笔记（个人查漏补缺）
gxzhaha的博客
06-08 396
不建议收藏，不建议点赞，建议评论错误的地方，小白自己学过一遍SSM，又来复习一下，比较多知识点第一次学不是那么理解，可能做的总结也会有些错误，望指点。 第一章 框架概述 框架:整个或部分系统的可重用设计 常见框架： 1.解决数据持久化问题的框架 2.解决web层问题的mvc框架 3.解决技术整合问题的框架Spring 传统JDBC 1.数据库链接创建、释放频繁造成系统资源浪费从而影响系统性能，如果使用数据库链接池可解决此问题。 ...
面试复习(查漏补缺)
weixin_42061487的博客
08-20 338
数据库系统原理 索引 索引是帮助MySQL高效获取数据的排序好的数据结构 建立的索引是存储在本地磁盘中的 索引结构 二叉树 红黑树 Hash表 B—Tree 二叉树 某些场景下二叉树有弊端，所以Mysql使用的是B+ 树来做索引 当索引为以下的情况时建立索引并不会加速查询速度 HashMap在JDK1.8之后将底层的链表优化成红黑树 红黑树 红黑树(平衡二叉树)演示地址：https://www.cs.usfca.edu/~galles/visualization/RedBlack.ht
2020秋招知识点查漏补缺（预热期），大量高频大厂面试题集合（有解析）
Android725的博客
07-10 1015
前言 之前因为一直在忙于秋招，也没有进行进一步的学习，一直都在复习已有的知识，寻找自己的不足，所以没有更新过博客，现在拿到了百度，猿辅导以及Keep的offer（百度已签），打算结束秋招了，然后就写一篇自己的秋招的经历，对自己秋招时光的一个总结，并且给以后的学弟学妹一点建议吧。 自我介绍 先介绍一下我吧，我是一个双非学校的应届生，但是我并不是从大学一开始的时候就是学习Android的，我大一到大三上学期都在搞ACM竞赛，一直到去年打完青岛现场赛才退役（苦逼的没有拿到牌，如果拿到了可能秋招之路就会好很多吧）。
西电数据库期末复习资源
03-26
通过复习西电数据库课程的历年试卷，学生可以了解出题趋势，发现自己的知识盲点，并通过答案进行自我评估和查漏补缺。而自制的答案更是宝贵的资源，它们可能包含了解题思路和解题技巧，帮助学生提高解决问题的能力。...
2022 年最新【Java 经典面试 800 题】面试必备，查漏补缺；多线程 +spring+JVM 调优 + 分布式 +redis+ 算法
m0_60721514的博客
10-14 2260
春招，秋招，社招，我们 Java 程序员的面试之路，是挺难的，过了 HR，还得被技术面，小刀在去各个厂面试的时候，经常是通宵睡不着觉，头发都脱了一大把，还好最终侥幸能够入职一个独角兽公司，安稳从事喜欢的工作至今…近期也算是抽取出大部分休息的时间，为大家准备了一份通往大厂面试的小捷径，准备了一整套 Java 复习面试的刷题以及答案，我知道很多同学不知道怎么复习，不知道学习过程中哪些才是重点，其实，你们经历过的事情，小马也经历过，正应为经历过，所以不愿意看到还有万千学子为此伤神，也许这篇文章帮不到万千学子，
2022年最新【Java八股文背诵版面试题】面试必备，查漏补缺；多线程+spring+JVM调优+分布式+redis+算法
m0_69305074的博客
08-25 988
春招，秋招，社招，我们Java程序员的面试之路，是挺难的，过了HR，还得被技术面，小刀在去各个厂面试的时候
零基础入门 全角度解读企业主流数据库MySQL8.0 --- 复习笔记
争当做一个有趣的人
03-09 1404
〖课程介绍〗: 本课程遵循“用户体验至上”的原则，不敢说设计精良，尽善尽美，却也竭尽全力去锻造良好的学习体验。包含三方面，（1）设计之初：遵循“有教无类，教学相长”的原则；（2）学习路线：采用循序渐进的教学模式，带你从了解到熟悉，到掌握，到精通，最后到深谙；（3）学习内容：课程遵循前沿性，以当前最新企业主流的MySQL8.0版本为内容。以“实用”为课程最高宗旨，带你搞定工作中SQL优化 痛点问题，最终的目的是：助力个人能力的快速提升，最终服务工作所需，助你占据不可替代的一席之地 第1章 【蓦然回首】开篇..
使用Docker-compose安装mysql5.7
州仔
11-08 259
1.首先选择一个目录用来存放docker-compse文件以及mysql的数据（例如logs、conf）docker发现没有mysql5.7这个镜像会去自动下载，执行完以后MySQL启动成功。2.填写docker-compse.yml内容。宿主机需要开放3306端口。
MySQL 索引
最新发布
果酱 の 博客
11-08 902
MySQL 索引是提高数据库性能的重要手段。通过选择合适的索引类型、创建必要的索引、定期维护索引和监控索引使用情况，可以大大提高数据库的查询性能。深入理解 MySQL 索引的原理和使用方法，对于构建高效的数据库应用至关重要。
[面试]Mysql的锁机制
ouyfg的博客
11-08 623
MySQL中的锁主要用于控制多个事务对数据库资源的并发访问，以确保数据的一致性和完整性。锁机制是数据库并发控制的核心，通过锁定数据库中的资源来防止并发操作引发的数据冲突。行锁允许多个事务在同一个表中并发执行不同行上的操作，而不会互相阻塞，因此具有较高的并发性。但相比于表锁，行锁需要更多的资源来维护锁的状态，开销更大。表锁是一种较粗粒度的锁机制，会锁定整个表，而不是某个具体的行或记录。综上，MySQL的锁机制是数据库并发控制的核心。除了表级锁和行级锁，MySQL还提供了其他类型的锁来支持不同的并发控制需求。
Linux（CentOS）安装 MySQL
BillKu的博客
11-06 533
MySQL版本：MySQL Community Server 8.4.3 LTS。CentOS版本：CentOS 7。
PySimpleGUI库和pymysql库
Lonelinessser的博客
11-06 217
PySimpleGUI 是一个用于简化 GUI 编程的 Python 包，它封装了多种底层 GUI 框架（如 tkinter、Qt、WxPython 等），提供了简单易用的 API。PySimpleGUI 包含了大量的控件（也称为小部件或组件），这些控件可以帮助你快速构建用户界面说明：5.0以上的版本不免费。
MySQL入门到熟练配置全攻略教程
资源摘要信息:"本资源提供了关于MySQL数据库的安装和配置的详细教程，适合初学者或者需要复习基础操作的专业人士。教程中不仅包含了安装MySQL服务器的...对于有经验的开发者，这些内容则可以作为复习和查漏补缺的参考。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值