ღゝ◡╹)ノ❤️

集中一点,登峰造极!

  menu
137 文章
0 浏览
1 当前访客
ღゝ◡╹)ノ❤️

mysql

sql执行顺序:

作者:牛客386943101号
链接:https://www.nowcoder.com/discuss/1039580?type=2&channel=-1&source_id=discuss_terminal_discuss_hot_nctrack
来源:牛客网
先有表(from、on、join),才能过滤(where),再才能分组(group、having),再才能选择去重(select distinct),再才能排序(order by),最后才能分页(limit)

事务隔离级别

  • READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读
  • READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生
  • REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。(对于mysql来说,可重复读的隔离级别下利用MVCC也可以解决幻读。
  • SERIALIZABLE(可串行化): 最高的隔离级别,完全服从 ACID 的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读

事务隔离利用mvcc使不同事务在读写的时候可以同时执行。


sql执行流程

  1. 客户端与服务器建立TCP链接
  2. 分析器进行语法分析和语义检查
  3. 优化器进行逻辑优化(关系代数的等价变换,例如列裁剪)和物理优化(小表驱动大表)
  4. 执行引擎进行查询计划

MYISAM和InnoDB

区别MYISAMInnoDB
事务安全不支持支持
锁机制表锁行锁
索引缓存只缓存索引,不缓存真实数据既缓存索引也缓存真实数据
支持外键不支持支持
全文索引支持不支持
统计具体行锁统计不统计

为什么数据库选择B+树做为索引的数据结构

常见的加速查找速度的数据结构

哈希

没有顺序,不能满足大于、等于、小于的查询。

如果创建复合索引,不能对其中的某一个查询。

如果发生hash冲突,效率会降低。

树形结构

在数据量大的情况下,B+树的层数是最小的,B+树的磁盘读写代价更低,因为B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B树更小,如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对IO读写次数就降低了。所以B+树可以比其他任何一个树结构的层数更少。数据都在叶子节点,遍历更方便。

另外InnoDB提供自适应的Hash的索引,如果某个数据经常被访问,满足一定条件的时候,会把这个数据页存到一个hash表中,这样下次查询的时候就能直接查询到页面所在的位置。

索引分类

功能逻辑划分

  • 普通索引
  • 唯一索引
  • 主键索引
  • 全文索引

使用上划分

  • 前缀索引
  • 覆盖索引

物理实现上划分

  • 聚簇索引
  • 非聚簇索引

字段个数

  • 单列索引
  • 联合索引

ACID

  • 原子性:要么全部成功,要么全部失败。undo日志保证。
  • 一致性:数据从一个合法状态转变成另一个合法状态。undo日志保证。
  • 持久性:对数据的操作是永久性的,接下来其他操作或者故障,都不会对其造成影响。redo日志保证。
  • 隔离性:一个事务的执行不被其他事务干扰,防止数据被覆盖。锁和mvcc(多版本并发控制)来保证。

mvcc理解

mvcc是多版本并发控制,
实现了不同事务之间读写并发执行。
原理是,每个事务进行读操作都会生成一个ReadView,ReadView包含创建这个事务的ID,活跃的事务id,最小的活跃事务id,系统分配给下一个事务的id值,在可重复读的隔离级别下,每次进行读操作,都会查看第一次生成的ReadView,然后用ReadView中记录的值与版本链中的trx_id(修改改版本的事务id)做比较,如果trx_id大于ReadView中记录的事务id,则继续寻找老版本,如果trx_id位于ReadView记录的最大值和最小值之间,则判断该线程id是否是活跃的,如果是活跃的则还没提交,不能读取,否则可以读取。如果trx_id小于ReadView中记录的最小事务id,则可以读取

间隙锁的作用

防止出现幻读的情况。假如给id100加了间隙锁,他会锁住100到100的前一条之间数据,如这个区间不能插入数据。

哪些情况适合建索引

  1. 字段数值有唯一性限制。
  2. 频繁作为where查询的字段。
  3. 经常GROUP BY和ORDER BY的列。
  4. UPDATE、DELETE的WHERE的条件列
  5. DISTINCT字段需要创建索引
  6. 区分度高的

创建索引注意事项

  1. 多表JOIN连接操作时,创建索引注意事项
    连接的数量尽量不要超过三张,因为每增加一个表数量级增加很快
    对WHERE条件创建索引
    对用于连接的字段创建索引
  2. 使用类型小的创建索引
  3. 使用字符串前缀创建索引
  4. 频繁查询的列放到符合索引的左侧

索引失效

  1. 计算、函数、类型转换
  2. 不等于、大于、小于、is null,is not null覆盖索引的情况生效,不是覆盖索引只有is null生效。
  3. %开头进行匹配
  4. 使用or,因为要全表扫描所以没必要走索引。如果条件中有or,只要其中一个条件没有索引,其他字段有索引也不会使用。因为如果其中一个没有索引,那么就会因为这个索引进行全表扫描,反正都要全表扫描,那么还不如只扫描一次呢。

关联查询优化

小表驱动大表。(小表指的是行数*行的大小)(加载到内存A+B*A ? B + A*B)

为什么小表驱动大表?减少外层循环的次数,内存中的buff是固定的,表越小,循环的次数就越小。

块嵌套循环连接。批量匹配

mysql 8.0 Hash Join

like使索引失效,怎么办?

使用全文索引,他是目前搜索引擎使用的一种关键技术,它能够利用分词技术,将存储的大量数据,分析成关键词,关键词所在文档的ID或者关键词,关键词所在文档ID和关键词所在文档的位置。在数据量大的情况下,能大大的增加搜索的效率。

MySQL中in和exists区别

MySQL中的in语句是把外表和内表作hash连接,而exists语句是对外表作loop循环, 每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高, 这种说法其实是不准确的。这个是要区分环境的。如果查询的两个表大小相当,那么用in和exists差别不大。如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。not in和not exists:如果查询语句使用了not in,那么内外表都进行全表扫描,没有用到索引;而not extsts的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not
in要快。

drop、delete与truncate的区别

image.png

数据库的乐观锁和悲观锁是什么?怎么实现的?

数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。
悲观锁:假定会发生并发冲突,屏蔽一-切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来,直到提交事务。实现方式:使用数据库中的锁机制
乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来,通过version的方式来进行锁定。实现方式:乐一般会使用版本号机制或CAS算法实现。

InnoDB引擎的行锁是怎么实现的?

InnoDB是基于索弓|来完成行锁
例: select * from tab_ with_ index where id = 1 for update;
for update可以根据条件来完成行锁锁定,并且id是有索引键的列,如果id不是索引|键那么InnoDB将完成表锁,并发将无从谈起。

隔离级别与锁的关系

在Read Uncommitted级别下,读取数据不需要加共享锁,这样就不会跟被修改的数据上的排他锁冲突
在Read Committed级别下,读操作需要加共享锁,但是在语句执行完以后释放共享锁;
在Repeatable Read级别下,读操作需要加共享锁,但是在事务提交之前并不释放共享锁,也就是必须等待事务执行完毕以后才释放共享锁。
SERIALIZABLE是限制性最强的隔离级别,因为该级别锁定整个范围的键,并一直持有锁,直到事务完成。

大表数据查询,怎么优化

  • 优化shema、sq|语句+索引;
  • 第二加缓存,memcached, redis;
  • 主从复制,读写分离;
  • 垂直拆分,根据你模块的耦合度,将一个大的系统分为多个小的系统,也就是分布式系统;
  • 水平切分,针对数据量大的表,这一步最麻烦,最能考验技术水平,要选择一个合理的sharding key, 为了有好的查询效率,表结构也要改动,做一定的冗余,应用也要改,sql中尽量带sharding key,将数据定位到限定的表上去查,而不是扫描全部的表;

如何优化查询过程中的数据访问

  • 访问数据太多导致查询性能下降
  • 确定应用程序是否在检索大量超过需要的数据,可能是太多行或列
  • 确认MySQL服务器是否在分析大量不必要的数据行
  • 查询到不需要的数据。解决办法:使用limit解决
  • 多表关联返回全部列。解决办法:指定列名
  • 总是返回全部列。解决办法:避免使用SELECT *
  • 重复查询相同的数据。解决办法:可以缓存数据,下次直接读取缓存
  • 是否在扫描额外的记录。解决办法:使用explain进行分析,如果发现查询需要扫描大量的数据,但只返回少数的行,可以通过如下技巧去优化:
  • 使用索弓|覆盖描,把所有的列都放到索引中,这样存储弓|擎不需要回表获取对应行就可以返回结果。
  • 改变数据库和表的结构,修改数据表范式n重写SQL语句,让优化器可以以更优的方式执行查询。

MySQL数据库qpu飙升到50o%的话他怎么处理?

当cpu飙升到500%时,先用操作系统命令top命令观察是不是MySQLd占用导致的,如果
不是,找出占用高的进程,并进行相关处理。

如果是MySQLd造成的,show processlist,看看里面跑的session情况,是不是有消耗资
源的sql在运行。找出消耗高的sql,看看执行计划是否准确,index 是否缺失,或者实在是
数据量太大造成。

一般来说,肯定要kill掉这些线程(同时观察cpu使用率是否下降),等进行相应的调整(比如
说加索引、改sql、改内存参数)之后,再重新跑这些SQL。

也有可能是每个sql消耗资源并不多,但是突然之间,有大量的session连进来导致cpu飙
升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制
连接数等。

索引下推?

索引下推是一种减少回表的一种策略,在复合索引的情况中,如果有的字段不能使用索引,那么可以使用索引下推进行一个过滤。

聚簇索引

特点:

  1. 使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
    页内 的记录是按照主键的大小顺序排成一个 单向链表 。
    各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
    存放 目录项记录的页 分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键
    大小顺序排成一个 双向链表 。
  2. B+树的 叶子节点 存储的是完整的用户记录。
    所谓完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。

优点:

  • 数据访问更快 ,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非
    聚簇索引更快
    聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
    按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多
    个数据块中提取数据,所以 节省了大量的io操作 。

缺点:

  • 插入速度严重依赖于插入顺序 ,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影
    响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
    更新主键的代价很高 ,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为
    不可更新
    二级索引访问需要两次索引查找 ,第一次找到主键值,第二次根据主键值找到行数据

Innodb数据结构

一、表空间(table space)


表空间(Tablespace)是一个逻辑容器,表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间。数据库由一个或多个表空间组成,表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

二、段(segment)


段(Segment)由一个或多个区组成,区在文件系统是一个连续分配的空间(在 InnoDB 中是连续的 64 个页),不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位,不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候,就会相应创建对应的段,比如创建一张表时会创建一个表段,创建一个索引时会创建一个索引段。

三、区(extent)


在 InnoDB 存储引擎中,一个区会分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB,所以一个区的大小是 64*16KB=1MB。在任何情况下每个区大小都为1MB,为了保证页的连续性,InnoDB存储引擎每次从磁盘一次申请4-5个区。默认情况下,InnoDB存储引擎的页大小为16KB,即一个区中有64个连续的页。

四、页(Page)


页是InnoDB存储引擎磁盘管理的最小单位,每个页默认16KB;InnoDB存储引擎从1.2.x版本碍事,可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成,则所有表中页的大小都为innodb_page_size,不可以再次对其进行修改,除非通过mysqldump导入和导出操作来产生新的库。

五、行(row)


InnoDB存储引擎是按行进行存放的,每个页存放的行记录也是有硬性定义的,最多允许存放16KB/2-200,即7992行记录。

范式

第一范式:每个列不可继续分割

第二范式:属性完全依赖主键

第三范式:属性不依赖于非主属性,减少冗余存储

为什么使用B+不使用B

  1. B+树非叶子节点不存在数据只存索引,B树非叶子节点存储数据
  2. B+树查询效率更高。B+树使用双向链表串连所有叶子节点,区间查询效率更高(因为所有数据都在B+树的叶子节点,扫描数据库 只需扫一遍叶子结点就行了),但是B树则需要通过中序遍历才能完成查询范围的查找。
  3. B+树查询效率更稳定。B+树每次都必须查询到叶子节点才能找到数据,而B树查询的数据可能不在叶子节点,也可能在,这样就会造成查询的效率的不稳定
  4. B+树的磁盘读写代价更小。B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B树更小,通常B+树矮更胖,高度小查询产生的I/O更少。

主键不自增的危害?

页分裂:因为B+树叶子节点是连续的,如果在中间插入,会导致数据的移动,可能也会导致非叶子节点的移动会严重影响性能。

雪花算法

snowflake是Twitter开源的分布式ID生成算法,结果是一个long型的ID。其核心思想是:使用41bit作为毫秒数,10bit作为机器的ID(5个bit是数据中心,5个bit的机器ID),12bit作为毫秒内的流水号(意味着每个节点在每毫秒可以产生 4096 个 ID),最后还有一个符号位,永远是0。

如何优化数据查询?

缓存不是万能的,如果是查询订单的业务场景,每个订单的id不一样,会导致缓存穿透。

怎么解决?读写分离。

主从复制模型?

image.png

何时分库何时分表

image.png


标题:mysql
作者:哇哇哇哇
地址:https://wuxiangshi.vip/articles/2022/02/18/1645169791948.html