MySQL相关基础

MySQL
MySQL
发布日期:   2022-03-14
文章字数:   4.3k
阅读时长:   15 分
阅读次数:  

MySQL相关

三大范式

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

MySQL有关权限的表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。(5.6.7移除)

事务的四大特性(ACID)

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性：一个事务被提交之后。它对数据库中数据的改变是持久的， 即使数据库发生故障也不应该对其有任何影响。

SQL语句主要分为哪几类

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER,对逻辑结构等有操作的，其中包括表结构，视图和索引
数据查询语言DQL（Data Query Language）SELECT,查询操作，以select关键字。
数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE,对数据进行操作的，
数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK,对数据库安全性完整性等有操作的,就是权限等

MySQL分库分表的目的是

分库分表就是为了 解决由于数据量过大而导致数据库性能降低的问题， 将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成， 使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。
分库分表常用的中间件:…

死锁？怎么解决

两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
常见的解决死锁的方法

如果不同程序会并发存取多个表， 尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
如果业务处理不好可以用分布式事务锁或者使用乐观锁

脏读？幻读？不可重复读？

脏读：指一个事务中访问到了另外一个事务未提交的数据
不可重复读：在一个事务内根据同一个条件对行记录进行多次查询，但是搜出来的结果却不一致。发生不可重复读的原因是在多次搜索期间查询条件覆盖的数据被其他事务修改了，
幻读：指同一个事务内多次查询返回的结果集不一样（比如增加了或者减少了行记录）。比如同一个事务A内第一次查询时候有n条记录，但是第二次同等条件下查询却又n+1条记录，这就好像产生了幻觉，为啥两次结果不一样那。其实和不可重复读一样，
区别：幻读是记录数改变了，不可重复读是同一记录的内容不一样。

视图特点

视图是由基本表(实表)产生的表(虚表)。视图的建立和删除不影响基本表。
对视图内容的更新(添加，删除和修改)直接影响基本表。
当视图来自多个基本表时，不允许添加和删除数据。

SQL生命周期

  1. 应用服务器与数据库服务器建立一个连接
  2. 数据库进程拿到请求sql
  3. 解析并生成执行计划，执行
  4. 读取数据到内存并进行逻辑处理
  5. 通过步骤一的连接，发送结果到客户端
  6. 关掉连接，释放资源

主键使用自增ID还是UUID

推荐使用自增ID，不要使用UUID。
因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说， 主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)， 如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID， 由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。
总之，在数据量大一些的情况下，用自增主键性能会好一些。
关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

MySQL数据库cpu飙升到100%的话怎么处理

show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确，index 是否缺失，或者实在是数据量太大造成。
一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)， 等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。
也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升， 这种情况就需要跟应用一起来分析为何连接数会激增， 再做出相应的调整，比如说限制连接数等。

MySQL主从复制解决了哪些问题

主数据库出现问题，可以切换到从数据库。可以进行数据库层面的读写分离。可以在从数据库上进行日常备份。
数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以用更高版本的MySQL作为从库

什么是MySQL的GTID

全局事务标识符， 是一个已提交事务的编号，并且是一个全局唯一的编号。
GTID是从MySQL 5.6版本开始在主从复制方面推出的重量级特性。
GTID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
GTID有如下几点作用：

  • 根据GTID可以知道事务最初是在哪个实例上提交的。
  • GTID的存在方便了Replication的Failover。因为不用像传统模式复制那样去找master_log_file和master_log_pos。
    基于GTID搭建主从复制更加简单， 确保每个事务只会被执行一次。

事务

begin;
try:
 操作1;
 操作2;
 操作3;
 xxxxx
 ....
 commit;
except Exception:
   rollback;

数据的回滚是依靠undo日志，它记录了某一行数据，在执行事务前是怎么样的。
日志里加入事务的id（trx_id）字段，用于标明这是哪个事务下产生的undo日志
多个日志用链表的形式组织，形成版本链

当前读，快照读

undo日志版本链中最新的数据在表头，其它的都是旧数据，它们都叫快照
当前读就是表头的数据，快照读就是所有版本链中的快照的数据

// 快照读
select * from user where ...
// 下面两个都是当前读，只需在select后加...
select lock in share mode * from user ...
select for update * from user ...
// 插入，更新，删除 属于写操作，都是用的当前读

事务只能读到自己产生的undo日志数据（事务提不提交都行），或者是其他事务已经提交完成的数据

B+树和跳表

B+树:

  1. 也是一种二叉查找树，所以具有二叉查找树的特点，也就是比左边的都大，比右边的都小
  2. 根节点是黑色
  3. 默认将空节点作为叶子结点，且是黑色
  4. 红色节点的左右孩子必须是黑色
  5. 每个节点到达其所有可达叶子节点路径上的黑色节点数量必须一致
  6. 每个节点要么红色要么黑色

跳表:

  1. 跳表分为多层，先查上层的链表，就很容易知道数据落在哪个范围，
  2. 然后跳到下一个层级里进行查询。这样就把搜索范围一下子缩小了一大半
  3. 跳表也是通过牺牲空间换取时间的方式提升查询性能。时间复杂度都是lg(n)。
  4. 跳表新增数据纯靠随机函数，有一个新的数据插入了，它有50%的概率需要在第二层加入索引，有25%的概率需要在第三层加个索引，以此类推，直到最顶层

MySQL为什么用B+树?

B+树是多叉树结构，每个结点都是一个16k的数据页，能存放较多索引信息。
三层左右就可以存储2kw左右的数据。也就是说查询一次数据，如果这些数据页都在磁盘里，那么最多需要查询三次磁盘IO。
跳表是链表结构，一条数据一个结点，如果最底层要存放2kw数据，且每次查询都要能达到二分查找的效果，2kw大概在2的24次方左右，所以，跳表大概高度在24层左右。
放在mysql数据库上来说，就是磁盘IO次数更少，因此B+树查询更快。
当然，对于修改来说，B+树需要旋转和维持平衡的开销，跳表不需要，性能要好一些。

Redis为什么用跳表？

redis 是纯纯的内存数据库，不存在磁盘IO了，所以层高就不再是跳表的劣势了。
而修改上，只需要随机一下，就知道自己要不要往上加索引，不需要旋转平衡，所以跳表性能高一些

唯一索引

如果要保证电话号码唯一怎么做？

保证电话号码唯一
begin;
select user where phone_no =2;  // 查询sql
if (user 存在) {
        return 
} else {
  insert user;   // 插入sql
}
commit;

上面是不行的，并不能阻止多个线程并发进入
直接用唯一索引即可，在Java程序写锁也行

为什么唯一索引能保证数据唯一？数据库并不是直接从磁盘读数据,它在磁盘前面加了一层内存，叫buffer pool。它里面有很多细节，但最主要的就是个双向链表，里面放的是一个个数据页，每个数据页的大小默认是 16kb，数据页里面放的就是磁盘的数据。有了这层buffer pool内存，mysql的读和写操作都可以先操作这部分内存，如果想要读写的数据页不在buffer pool里，再跑到磁盘里去捞
很多时候写操作，数据库光知道把什么字段更新为什么数据就行了，根本不需要知道数据页原来长什么样子,所以，只要把想要写的内容写到change buffer，然后后续异步慢慢更新到磁盘数据上,但具有唯一索引的字段不行，他需要判断数据唯一。
唯一索引会绕过change buffer，确保把磁盘数据读到内存后再判断数据是否存在，不存在才能插入数据，否则报错，以此来保证数据是唯一的。
(以上例子来自微信公众号小白debug)

字符集utf8mb4

  • ASCII -> Unicode
    ASCII编码用1个字节来表示，而Unicode用则两个字节来表示
    同样都是字母d，unicode比ascii多使用了一个字节
    ascll: 01100100
    unicode: 00000000 01100100
  • Unicode -> utf-8
    多占了个字节，可以省略，于是出现了utf-8，utf-8则在unicode的基础上做了优化，压缩了空间
  • utf8mb4
    utf8mb4:most bytes 4,最多支持4个字节表示字符,utf-8最多支持用3个字节表示字符，utf-8mb4支持更多字符，例如emoji表情
    推荐utf8mb4,对于char utf8mb4可能占用空间大，但是使用varchar，utf8mb4和utf-8占用空间一样。

MySQL架构

  • 连接层：连接处理，授权认证
  • 服务层：核心服务，查询解析，分析，优化，缓存，内置函数，触发器，存储过程，视图等
  • 引擎层：存储引擎，负责数据存储和提取
  • 存储层：数据存储，和存储引擎交互，将数据存到文件系统

sql的执行流程

  • 客户端 连接
  • 连接器 管理连接，权限验证
  • 查询缓存 是否命中缓存
  • 分析器 语法分析
  • 优化器 优化，执行计划生成，索引选择
  • 执行器 调用存储引擎，返回结果
  • 存储引擎 提供接口，读写数据，返回给执行器

MyISAM和InnoDB

  • InnoDB支持事务，外键
  • InnoDB是聚族索引，主键索引查询性能高，非主键索引要查询两次，MyISAM是非聚族索引，主键索引和辅助索引是独立的
  • InnoDB不保存表具体行数，MyISAM用变量保存了表行数
  • InnoDB最小锁粒度是行锁，MyISAM是表锁，并发性能低

索引

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引,只有一个）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

主键索引,二级索引

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。
当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再查主键索引查。种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据
下面是主键索引

索引失效

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下索引失效是因为查询过程需要扫描整个索引并回表，代价高于直接全表扫描，所以优化最终选择走全表扫描。
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
为了更好的利用索引，索引列要设置为 NOT NULL 约束。

explain

对于执行计划，参数有：
possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型，我们需要重点看这个。
其中
ALL（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）

select for update

在查询的时候加锁，悲观锁
查询条件用了索引/主键，会进行行锁
查询条件是普通字段，会进行表锁

MySQL
  目录