mysql问题

MySql基本架构

• 分为server层和储存引擎两部分
• server层：连接器，查询缓存，分析器，优化器，执行器等
• 存储引擎：数据的存储和提取

数据库三范式

• 第一范式：原子性，每一行数据都是不可分割的原子数据项（字段不可拆分）
• 第二范式：要求实体的属性完全依赖于关键字
• 第三范式：任何非主属性不依赖于其他非主属性

数据库索引

• 索引是为了提高查询的效率，相当于目录
• 创建和维护索引需要时间，需要占用物理空间，修改表要动态维护索引
• 建立索引
• 最频繁使用，用于缩小查询范围或需要排序的字段上创建索引
• 不适合创建索引：重复值比较多，很少用，文本字段等

索引底层机制

• 和具体存储引擎有关
• InnoDB使用的是B+树，还有hash索引等

B+树

• 有k个子树的中间节点包含有k个元素（B是k-1)，不保存数据，只用来索引
• 叶子节点包含有全部信息，以及指向这些元素记录的指针，叶子节点本身根据关键字大小自小而大排序
• 所用中间节点元素都同时存在于子节点，是子节点元素中最大或最小的元素
• 根节点的最大元素就是整个树的最大元素
• 每一个根节点都有指向下一个根节点的指针，形成有序列表
• 只有跟节点带有卫星数据，其余节点只是索引
  • 聚集索引，叶子节点直接包含卫星数据（主键索引）
  • 非聚集索引，叶子节点带有指向卫星数据的指针（辅助索引）
• 每次查询数据都要查询到叶子节点，效率更稳定
• 查询只需要在链表上做遍历
• 优势
  • IO次数更少
  • 查询性能稳定
  • 范围查询更简便（先用树知道第一个，再用链表查询后面的）

哈希索引

• 哈希索引查找很快，但失去了有序性，无法用于排序与分组，只能用于静去查找
• 自适应哈希索引：在B+树的链表上创建哈希索引

覆盖索引

• 如果一个索引中已经包含所查询的字段了，则不需要回表查询，就是覆盖索引

索引的分类

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

左前缀索引

• 按索引中字段的顺序从左向右比较

如何知道索引有没有被使用

• explain关键字。如果type是all，说明遍历了所有行，没有使用索引

不使用索引的情况

• 索引列参与表达式运算
• 函数运算
• 模糊查询
• 字符串与数字比较
• or中有一个不带索引
• MySQL内部优化，优化器使用全表扫描比使用索引快则不用索引-

查询优化

• 只返回必要的列，必要的行
• 缓存重复查询的数据
• 索引

MyISAM和InnoDB的区别

• 默认使用的是A
• A不支持事务，B支持
• B支持行级锁，A只支持表级锁，A表上的读和写锁是互斥的，默认写锁的优先级更高
• B支持外键，A不支持
• B主键范围更大，是A的两倍
• B不支持全文索引，A支持
• A内置了计数器，count(*)直接从计数器中读取，速度较快

水平切分和垂直切分

• 水平切分将同一个表数据源拆分到多个结构相同的表中，分不到集群的不同节点上
• 垂直切分是将一个表按列切分成多个表

主从复制

• bin日志：记录下所有修改了数据库的SQL的语句
• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-08bvugpm-1648556665565)(.mysql_images/244ba2c6.png)]
• 复制过程
  • 主节点启用二进制日志，记录任何修改了数据库数据的事件
  • 从节点开启线程I/O thread请求主节点中日志中的事件
  • 主节点启用一个线程（dump Thread)，检查自己日志中的事件，与对方的请求位置对比，并把事件一个一个发给对方
  • 从节点将主节点发过来的事件存入中继日志（Relay log),并记录位置
  • 从节点启动sql Thread,读出中继日志的事件，在本地执行
• 复制特点
  • 异步复制：主节点自己的写操作完成就发给用户，并不需要等对方反馈
  • 主从数据不一致

数据库读写分离

• 用代理方式实现，由代理分发
• 主服务器处理写操作和实时性要求比较高的读操作，服务器处理读操作
• 优势
  • 缓解了锁的争用
  • 从服务器可以使用MyISAM
  • 增加冗余，提高可用性

事务的特性

• 原子性：事务是最小的执行单位，不可分割，一个事务要不全部写完，要不一个也不写
• 一致性：执行事务前后，数据可从一个一致性状态转变为另一个一致性状态
• 隔离性：并发访问数据库时，一个用户的事务不被其他事务干扰
• 持久性: 一个事务被提交后，它对数据库中数据的改变是持久的

事务的隔离级别

• read_uncommitted(未提交读)：允许读取尚未提交的数据变更。导致脏读，幻读和不可重复读
• read_committed：允许读取并发事务已经提交的数据，会发生幻读，不可重复度
• repeated_read(可重复读)：多次读取数据一致，除非自身修改数据，可能有幻读
• serializable(串行化)：最高隔离级别，完全服从ACID

脏读，幻读，不可重复读

• 脏读：一个事务能读取另一个事务中没有提交的数据
• 不可重复读：一个事务中多次读同一数据，结果不一致，因为被另一个事务改过了，重点在于update和delete
• 幻读：多次查询返回的结果集不一样，重点在于删除和新增
• MySql默认repeated_read

MVCC

• 多版本并发控制
• 用于解决读写冲突的无锁并发控制，为事务分配单向增长的时间戳，读操作只读事务开始前的数据库拍照
• 可以做到读和写操作互不阻塞
• 解决脏读，幻读，不可重复读

行锁和表锁

• 行锁：开销大，加锁慢，会出现死锁，锁力度小，发生冲突概率小，并发度最高
• 行锁不给select加锁，而表锁加

InnoDB存储引擎锁种类

• Record lock：单个行记录上的锁
• Gap lock:间隙锁，锁定一个范围，不包括记录本身
• Next-key lock:锁定一个范围，包含记录本身

问题排查方法

• 用show processlist命令
• 用explain命令
  记录上的锁
• Gap lock:间隙锁，锁定一个范围，不包括记录本身
• Next-key lock:锁定一个范围，包含记录本身

问题排查方法

• 用show processlist命令
• 用explain命令
• 慢查询日志