首页 > 数据库 >Mysql超详细基础干货——几分钟带你认识mysql

Mysql超详细基础干货——几分钟带你认识mysql

时间:2024-08-29 09:25:24浏览次数:13  
标签:事务 log mysql 查询 索引 干货 Mysql 数据 主键

Mysql数据库

事务的特性

binlog、redo log和undo log

MySQL事务实现原理

left join、right join和inner join区别?

说一下 mysql 的行锁和表锁

索引有哪些

数据结构

Innodb和Myisam存储引擎区别

为什么索引底层实现选择B+

uuid为什么不适合做主键?

1万数据未支付,已支付,支付失败状态3种,适合建索引么?

sql优化

Sql优化不走索引的情况?

索引覆盖

回表

mysql深度分页问题

Mysql数据库

事务的特性

原子性(支持回滚,底层根据undo log,事务如果中途出现错误,会进行回滚,回滚到开始前的状态)

一致性 (事务开始前和结束后,数据库的完整性约束没有破坏)

隔离性(只允许一个事务请求同一数据,不同事务之间彼此**互不干扰**,底层MVCC多版本并发控制间隙锁--读写锁)

持久性 (不支持回滚,底层根据 redo log)

事务的并发问题(脏读、幻读、不可重复读)

脏读:事务A已经更新了一份数据,在这个过程中,事务B去执行了同一份数据,但是由于某些原因,被修改的数据rollback了,然后一个事务所读取的数据就不一样了(没有提交,进行了回滚)

不可重复读(一个事务中不允许多次读取数据):事务a多次读取同一个数据,事务B在事务A多次读取的过程中对数据做了更改,导致最终事务A读的数据不一致(提交成功了)

幻读:管理员A已经把学生的信息全部统计完毕了,在统计过程中,管理员B添加了一条数据,但是管理员A不知道,等管理员A执行完之后,发现有一条数据没有被统计进来,这个时候就发生了幻读(提交成功了)

不可重复读和幻读的区别:不可重复读是侧重于修改,幻读侧重于新增或删除,解决不可重复读就锁住满足条件的,解决幻读是需要锁全表

MySQL事务隔离级别   

读未提交(脏读、不可重复读、幻读)事务A读取到事务B未提交的数据

读已提交(不可重复读、幻读)事务A去修改数据但是不提交,事务B查询数据查询的还是原来的数据,事务A提交事务,事务B再次读取数据,读到的数据和第一次读取 的数据是不一致的

可重复读(幻读  默认的)事务A在执行的过程中不会读取到其他提交的事务,只有当前事务结束之后才可以读取到

串行化  排队依次去执行

binlog、redo log和undo log

bin log:读写分离,每次操作都会记录到binlog中去,可以直接在binlog中去查询丢失的数据

redo log:保证持久性

undo log:保证原子性

MySQL事务实现原理

主要是用undo log和redo log来实现的。

undo log用来恢复数据,保证了原子性

redo log用来回滚数据,保证了持久化

事务的隔离性是通过(读写锁(间歇锁)+MVCC)来实现的

MVCC的原理:将历史信息在快照内存中,其他事务发生删除修改操作,都是对他不可见的

间隙锁的原理:读取数据的该行与上一行和下一行有一个间隙的锁定,保证在此范围内读取到的数据是一致的

left join、right join和inner join区别

left join(左连接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右连接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(内连接) 只返回两个表中联结字段相等的行

说一下 mysql 的行锁和表锁

mysiam支持表锁,innodb支持行锁

表级锁:开销小,加锁快,不会出现死锁。锁定力度大,并发量最低,发生锁冲突的概率最高

行级锁:开销大,加锁慢,会出现死锁。锁定力度小,并发度最高,发生锁冲突的概率小

索引有哪些

主键索引,唯一索引,普通索引,组合索引

数据结构

 数组:查询效率比较高,添加,删除效率比较低

 链表:删除添加操作效率比较高,查询效率比较低

hash:做的是数据的比较,不能那个做区间查询  等值查

二叉树:

二叉树是每个节点最多有两个子节点的树。

二叉树的叶子节点有0个字节点,二叉树的根节点或者内部节点有一个或者两个字节点。

二叉树在极端的情况下会出现单链表的情况,所以说查询的速度还是慢

红黑树:根节点永远是黑色的

红黑树与AVL树的比较:

AVL是严格的平衡树,因此在增加或者删除节点的时候,根据不同情况,旋转的次数比红黑树要多;

红黑树是用非严格的平衡来换取增删节点时候旋转次数的降低开销;

所以简单说,如果你的应用中,搜索的次数远远大于插入和删除,那么选择AVL树,

如果搜索,插入删除次数几乎差不多,应选择红黑树

 B树(b-)一个节点可以存多个数据块,它的高度降低了很多,顶多达到3层高度,高度变小,IO次数变少,性能有提升

B+树(在B树上做了改造)


Innodb和Myisam存储引擎区别

1.索引区别,Innodb聚集索引(数据文件和索引文件放在一起的)

Innodb二级索引(非主键的索引)(建立普通索引和主键的关系,先通过普通索引找主键,然后回表找数据)


Myisam非聚集索引(数据文件和索引文件分开的)

Myisam二级索引(跟主键没有关系,都是维护独立索引树,然后叶子节点存地址,然后通过地址找数据)

2.Myisam不支持事务的,Innodb支持事务

3.Myisam表级锁,Innodb是行级锁(必须使用到索引列才能去使用)

4.Myisam崩溃后无法安全恢复 ,Innodb具有自动崩溃恢复功能 

为什么索引底层实现选择B+

1.新增了叶子节点,叶子节点存的是类似链表有序的数据,所以可以排序或区间查询

2.充分利用操作系统空间局部性原理(磁盘在读取数据的时候,不是按需读取的,是按页读取的,它会把需要的数据的,周边也读取到,比如说,1.2.3.4.5,我们读取4,会把它周边的1.2.3.4.5都读出来,减少了IO的次数),mysqB+树非叶子节点的数据都是按页存储,默认页存储是16KB

3.B+树在在其他树上改造的,数的高度控制了了,整个IO查找次数减少了,性能有提升

uuid为什么不适合做主键

32位,空间占用率大,无序,Innodb索引文件会很大

因为我们数据库通常Innodb用存储引擎,它的索引结构是根据主键去组织的,那么占用空间会很大,并且我们建了二级索引, 二级索引它会和主键索引建立关系,先去二级索引查询主键,然后通过主键再去查询数据块(回表 )

因为无序,没法充分利用B+树特性,非叶子节点没法充分使用操作系统系统空间局部性原理,导致性能低

1万数据未支付,已支付,支付失败状态3种,适合建索引么?

不适合,建索引规则必须要保证离散型最强的一列,不然重复比较多

sql优化

-- 全表扫描 EXPLAIN select * from tb_test;

EXPLAIN select * FROM tb_test where mobile='15921484451';

EXPLAIN select * FROM tb_test where mobile=15921484451;

数据库有隐式类型转换,索引类型不一样,不走索引

EXPLAIN select * FROM tb_test where mobile like '%21%';

EXPLAIN select * FROM tb_test where mobile like '134%';

like查询前模糊匹配不走索引,like后模糊匹配可能会走索引

组合索引user_name与mobile  

EXPLAIN select * FROM tb_test where user_name='大宝' and mobile='15921484451';

EXPLAIN select * FROM tb_test where user_name='大宝' ;

EXPLAIN select * FROM tb_test where mobile='15921484451';

Sql优化不走索引的情况?

1.数据库有隐式类型转换,索引类型不一样,不走索引

2.like查询前模糊匹配不走索引,后模糊匹配可能会走索引

3.NULL列不走索引

4.not,not in ,!=,or不走索引

5.在建的索列上有函数操作,都不走索引,比如EXPLAIN select * FROM tb_test where substr(mobile,1,3)='159'

6.组合索引必须满足最左匹配原则,比如:abc,a,ab,abc,ac

索引覆盖

建了user_name和mobile对应的组合索引

select * from tb_user_test where user_name='大宝' and mobile='15921484451';

select user_name,mobile from tb_user_test where user_name='大宝' and mobile='15921484451';

如果查询的列只包含我们建的索引列,此时不需要回表操作,叫索引覆盖

回表

Innodb是聚集索引,它的索引结构是根据主键去组织的,二级索引它会和主键索引建立关系,先去二级索引查询主键,然后通过主键再去查询数据块的这个过程

mysql深度分页问题

在查询的时候,如果查询的数据量较大的情况下,用limit查询的时候,查询的效率会慢很多,如:

select * from tb_user limit 3000000,10;

优化方案:

1.如果表有自增id,最好加上一个条件,id>,取上一页的id加上id>上一页id,可以提高效率

2.如果没有自增,可以用延迟关联处理

因为会有一个回表的操作,所以说可以先查询出他们的id,查询出来之后然后在去查询数据,通过inner join去连接

标签:事务,log,mysql,查询,索引,干货,Mysql,数据,主键
From: https://blog.csdn.net/2403_86981468/article/details/141665487

相关文章

  • MySQL联表查询
    按功能分内连接等值连接非等值连接自连接外连接左外连接右外连接全外连接交叉连接1.内连接(INNERJOIN)获取所有的员工名和其对应的部门名SELECTe.last_name,d.department_nameFROMemployeeseINNERJOINdepartmentsd ONe.department_id=d.departm......
  • MySQL修改密码
    第一种方式:最简单的方法就是借助第三方工具NavicatforMySQL来修改,方法如下:1、登录mysql到指定库,如:登录到test库。2、然后点击上方“用户”按钮。3、选择要更改的用户名,然后点击上方的“编辑用户”按钮。4、出现如图界面,输入新密码,并确认新密码,点击“保存”按钮即可。......
  • MySQL 源码|62 - 词法解析(V2):调用词法解析器的逻辑
    目录文档:MySQL源码|源码剖析文档目录源码位置(版本=MySQL8.0.37):sql/sql_class.cc;sql/sql_yacc.yy前置文档:MySQL源码|33-语法解析:bison基础语法规则MySQL源码|61-词法解析(V2):MySQL语法解析指定的返回值类型的联合体在sql/sql_yacc.yy中定义了%defineapi.pr......
  • mysqldump的使用详解
    一、mysqldump简介mysqldump 是 MySQL 自带的逻辑备份工具。它的备份原理是通过协议连接到 MySQL 数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的insert 语句,当我们需要还原这些数据时,只要执行这些 insert 语句,即可将对应的数据还原。二、备份命令2.1命......
  • mysql格式化日期
    mysql查询记录如果有时间戳字段时,查看结果不方便,不能即时看到时间戳代表的含义,现提供mysql格式换时间函数,可以方便的看到格式化后的时间。1.DATE_FORMAT()函数用于以不同的格式显示日期/时间数据。DATE_FORMAT(date,format)format参数的格式有 %a缩写星期名%b......
  • Java异常详解(全文干货)
    介绍ThrowableThrowable是Java语言中所有错误与异常的超类。Throwable包含两个子类:Error(错误)和Exception(异常),它们通常用于指示发生了异常情况。Throwable包含了其线程创建时线程执行堆栈的快照,它提供了printStackTrace()等接口用于获取堆栈跟踪数据等信息。Error(错......
  • MYSQL索引的选型比较
    MYSQL索引前言Mysql作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是Mysql数据的存储形式以及索引的设计,决定了Mysql整体的数据检索性能。我们知道,索引的作用是做数据的快速检索,而快速检索的实现的本质是数据结构。通过不同数据结构的选......
  • 第42天:WEB攻防-PHP应用&MYSQL架构&SQL注入&跨库查询&文件读写&权限操作 - 快捷方式
    接受的参数值未进行过滤直接带入SQL查询 MYSQL注入:(目的获取当前web权限)1、判断常见四个信息(系统,用户,数据库名,版本)2、根据四个信息去选择方案root用户:先测试读写,后测试获取数据非root用户:直接测试获取数据  #PHP-MYSQL-SQL常规查询获取相关数据:1、数据库版本-看是否......
  • mysql-day01
    SQL-DDL--注释的方式:两个减号或者一个#号--这对于库的DDL--创建库createdatabasedb_youcai;createdatabasedb_youcai02charactersetutf8;--使用库,切换库usedb_youcai;--查询正在使用的库selectdatabase();--查询都有哪些库showdatabases;--......
  • MySQL表分区与分表:概念、规则及应用案例
    MySQL表分区与分表:概念、规则及应用案例在大型业务系统中,随着数据量的急剧增加,优化数据库性能成为关键任务。MySQL提供了两种有效的数据管理技术来应对这些挑战:表分区(Partitioning)和分表(Sharding)。本文将深入探讨表分区和分表的概念、优缺点、具体规则以及适用场景,并通过电......