MySQL

常见问题

sql 的执行流程

  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存, 如果命中了缓存, 则立刻返回储存在缓存中的结果. 否认这进入下一阶段
    • 查询缓存
      • 缓存会保存查询返回的完整结构
      • 当缓存涉及的表发生了更新, 则这些表的缓存全部失效
      • 缓存命中必须要求所有字符一致, 空格, 注解等的修改都会导致缓存不命中
      • 当语句中有不确定数据时, 不会被缓存. 例如 NOW(), CURRENT_DATE() 等查询
      • 任何用户自定义函数, 储存函数, 用户变量, 临时表, 系统表, 列级别的权限表都不会被缓存
      • 当语句中包含不确定函数时, 也会查询缓存, 但是一定不会查询到结果, 因为包含不确定函数的查询结果不会加入到缓存中
  3. 服务器端进行 SQL 解析, 预处理, 再 由优化器生成对应的执行计划
    • 解析
      • 解析器将 SQL 语句进行解析, 并生成解析树.
      • MySQL 解析器将使用 MySQL 语法规则验证和解析查询
    • 预处理
      • 根据一些 MySQL 规则进行进一步检查解析树是否合法, 例如检查数据表和数据列是否存在, 还会解析名字和别名, 看看它们是否有歧义.
    • 查询优化器
      • 查询优化器会将解析树转化为执行计划
      • 一条查询可有多种执行方法, 最后都是返回相同结果. 优化器的作用就是找出这其中最好的执行计划
      • 生成执行计划的过程会消耗较多的时间, 特别是存在许多可选的执行计划时
      • 执行计划会被缓存, 当类似的 SQL 查询再次到来时, 将会使用已缓存的执行计划, 从而跳过生成执行计划的过程, 提高语句的执行速度
      • MySQL 使用基于成本的查询优化器 (Cost-Based Optimizer, CBO), 会尝试预测一个查询使用某种执行计划时的成本, 并选择最少成本中的一个.
  4. MySQL 根据优化器生成的执行计划, 再调用储存引擎的 API 来执行查询
    • 查询执行引擎
      • MySQL 查询执行引擎根据上一步产生的执行计划来完成整个查询
      • 执行计划是一个数据结构
  5. 将结果返回给客户端
    • 如果查询可以被缓存, 那么在这个阶段将会把结果存放到查询缓存中.
    • 结果集返回给客户端是一个增量, 逐步返回的过程. 在查询生成第一条结果时, MySQL 就可以开始向客户端逐步返回结果集了.

select、from、where、join等子句执行顺序

完整顺序

(8) select (9) distinct <select_list>
(1) from <left_table> (3) <join_type>join<right_table>
(2) on <join_condition>
(4) where <where_condition>
(5) group by <group_by_list>
(6) with {cube|rollup}
(7) having <having_condition>
<10> order by <order_by_list>
<11> limit <limit_number>

from 后面的表关联, 是自右向左的

where 后面的解析顺序是自下而上的

所以在写 SQL 时, 尽量把数据量大的表放在最右边进行关联.

group by 执行顺序从左往右分组

(3) MySQL 有哪些储存引擎, 有哪些区别

为什么选择 InnoDB

MySQL 索引在什么情况下会失效

(10) MySQL 索引模型, 底层实现? 如何实现加速查询?

索引的优缺点

索引分类

  1. 主键索引, 不允许重复, 不允许空值
    • 联合主键: 限制组合值不允许重复, 不推荐使用
    • 主键不应该带有业务含义
    • 主键不允许 null
  2. 唯一索引, 允许空值
  3. 普通索引, 没有任何限制
  4. 全文索引: 用大文本对象的列构建的索引
  5. 组合索引: 用多个列组合构建的所有, 这个列中的值不允许有空值
    • 遵循最左前缀原则, 把最常用作为检索或排序的列放在最左, 依次递减, 组合索引相当于建立了 [col1], [col1, col2], [col1, col2, col3] 这三个索引.
    • 使用组合索引的时候可能因为列名太长而导致索引的 key 太大, 导致效率降低, 所以可以只取列前几个字符来创建索引

索引实现原理

1. 哈希索引

2. 全文索引

3. BTree 索引

4. B+Tree 索引

5. 带顺序的 B+Tree

聚簇索引与非聚簇索引

MyISAM 采用的是非聚簇索引.

非聚簇索引的主索引和辅助索引几乎是一样的, 只是主索引不允许重复, 不允许控制, 它们的叶子节点的 key 都储存指向键值对应的数据的物理地址.

InnoDB 采用的是聚簇索引

索引的使用策略

MySQL 主从怎么同步? 分哪几个过程? 如果有一台新机器要加入到从机里, 怎么个过程?

概念

主从复制是指数据可以从一个 MySQL 数据库服务器节点复制到一个或更多的从节点.

MySQL 默认采用异步复制方式, 这样从节点不用一直访问主服务器来更新自己的数据. 数据的更新可以再远程连接上进行, 从节点可以复制主数据库中的所有数据库或者特定的数据库, 或者特定的表.

采取从节点向主节点 pull 的方式同步.

主要用途

主从方式

主从复制原理

主从复制涉及三个线程
复制过程
复制模式
复制方式

乐观锁与悲观锁的区别?

binlog 日志是 master 推的还是 slave 来拉的?

slave pull.

具体流程: slave 连接到 master 时, master 机器会为 slave 开启 binlog dump 线程, binlog 发生变化时, binlog dump 线程会通知 salve, 并将相应的内容发送给 slave

[5] MySQL 事务的四个隔离级别? (哪几个级别, 每个级别可能有的问题, 分别解决什么问题)

binlog 和 redolog 日志的作用和使用阶段

redolog 是 InnoDB 特有 日志, 只能被 InnoDB 引擎使用. binlog 是 MySQL server 层提供的日志, 是可以被所有的引擎使用.

(2) 访问数据库请求过慢可能是什么原因

常见瓶颈

  1. SQL 效率低
  2. 选项配置不当
  3. 访问飙升
  4. 硬件性能低
  5. 其它进程抢资源

如果确认是不是 MySQL 存在瓶颈

htop 查看

  1. mysqld 进程的 CPU 消耗占比
  2. mysqld 进程的 CPU 消耗是 user 高, 还是 sys 高
    • user 占比高, 说明用户进程消耗的 CPU 时间多. 如果长期超过 50%, 需要优化程序算法了
    • sys 高, 需要具体检查原因
  3. 确认物理内存是否不够了
    • 当有 swap 产生的时候, 说明物理内存不够了.
  4. 是否有 swap 产生
  5. 确认 CPU 上是否有大量中断 (中断不均)
  6. 查看 CPU 状态
    • IO 瓶颈
    • 等待分配内存
    • CPU 处理能力低
  7. 查看 MySQL 线程在干嘛
    • show processlist
      • Sending Data , 表示从引擎中读取数据返回给 Server 端的状态
        • 没有合适的索引, 查询效率低 (加上合适的索引, 优化 SQL)
        • 读取大量的数据, 读取缓慢 (使用 Limit)
        • 系统负载高, 读取缓慢 (升级设备)
      • Waiting for table metadata lock 长时间等待 MDL 锁
        • DDL 被阻塞, 进而阻塞后边的 SQL (把 DDL 放在半夜低谷时间执行)
        • DDL 之前的 SQL 长时间未结束 (提高 SQL 效率, 干掉长时间运行的 SQL / 采用 pt-osc 执行 DDL)
      • Sleep 线程睡眠中
        • 占用连接数
        • 消耗内存未释放
        • 可能有行锁(甚至是表锁)未释放
        • 适当调低 timeout
        • 主动 kill 不活跃连接
        • 定期检查锁, 锁等待
        • 可以利用 pt-kill 工具
      • Copy to tmp table
        • 执行 alter table 修改表结构, 需要生成临时表
        • 建议放在夜间低谷时运行, 或者使用 pt-osc
      • Copying to tmp table [on disk]
        • 常见于 group by 没有索引的情况下, 需要拷贝数据到临时表中 (内存/磁盘上), 执行计划中会出现 Using temporary 关键字, 建议创建合适的所有, 消除临时表.
        • order by 没有索引的情况下, 需要进行 filesort 排序, 执行计划中会出现 Using filesort 关键字, 建议创建排序索引.

(6) 数据库 ACID 指什么? 有什么含义? 索引触发有什么原则?

innodb 如何解决幻读问题?

幻读是指在事务中读取到了其它事务insert的数据, 导致查询不一致, 提高隔离级别到 可队列化可以解决这个问题. 底层实现是使用 Next-key-locks 实现的.

超键、候选键、主键、外键分别是什么？

画出 B 树, B+树, B+树比起B树有什么优点? 为什么不用哈希表做索引?

最左匹配原则是什么

分库分表具体实现

权限系统数据库表如何设计

复合索引如何查询才能使用

最左匹配原则

平衡二叉树、b树、b+树区别以及查询时间复杂度

优化与灾难备份

触发器和储存过程

索引查找在Linux的磁盘上是怎么操作的

聚簇索引和非聚簇索引的区别

区别在于叶子节点是否存放一整行数据

InnoDB 主键使用的是聚簇索引. MyISAM 不管是主键索引还是二级索引, 使用的都是非聚簇索引.

MySQL的IO过高怎么优化，分库分表及分区

添加索引，为什么可以减少io操作（磁盘页）

MySQL 储存数据在磁盘中, 读取速度瓶颈在于 磁盘 I/O 的读取, 索引一般使用 B+树结构, 这种数据结构在查找时类似于二分查找, 能够有效的降低查找次数, 再则 B+树 叶节点形成了链表, 在范围查询的这种情形下更有利. 关于磁盘来说, 机械硬盘分扇区, 每个扇区由页组成, 页一般大小为 16k, 处于读取速度的考虑, MySQL 在申请磁盘空间时, 都是按照整页来申请的, 索引储存也是储存在页中, 在读取磁盘数据时, 会读取整页的数据到内存中, 再进行查找, 索引以关键字作为检索条件, 能够将读取 I/O 次数降到很低.

回表概念

非聚簇索引的叶子节点上只储存行数据的指针, 在查询到指针后得回到数据表去获取想要的数据, 所以叫回表

mvcc 原理? 多版本数据放在那里

mysql 脏页？

  1. MySQL 脏页

    当内存数据页和磁盘数据页上的内容不一致时, 就称这个内存页为脏页. 内存数据写入磁盘后, 内存页上的数据就和磁盘页上一致了, 我们称这个内存页为干净页.

  2. 脏页刷新时机

    1. redo log 写满了, 没有空间了, 此时需要将 checkpoint 向前推进, 推进的这部分日志对应的脏页刷入到磁盘, 此时所有的更新全部阻塞, 此时写的性能变为0, 必须待刷一部分脏页后才能更新.
    2. 系统内存不足时, 需要将一部分数据页淘汰掉, 如果淘汰的是脏页, 需要将脏页同步到磁盘
    3. MySQL 认为空闲的时间, 这种没有性能问题
    4. MySQL 正常关闭之前,会把所有脏页刷入磁盘, 不存在性能问题.

redo log, undo log

为什么非主键索引结构叶子节点储存的是主键值?

  1. 主键索引和二级索引分别维护各自的 B+树 结构, 当插入数据的时候, 由于数据只有一份, 通过非主键索引获取到主键值, 然后去主键索引的 B+ 树数据结构中找到对应的行数据, 节省了内存空间.
  2. 如果非主键索引的叶子节点也储存一份数据, 那么通过非主键索引插入数据, 需要向主键索引进行数据同步, 会带来数据一致性的问题.

多列索引结构

每个节点都包含联合索引每一列的关键字, 并且按照层级进行的顺序全排列. 搜索数据时, 会按照一级级的进行过滤查询, 这就是最左前缀原则

字符串类型和数字类型索引的效率?

数据类型隐式转换.

当操作数与不同类型的操作数一起使用时, 会发生类型转换以使操作数兼容. 则会发生隐式转换.

join 和直接 from 有什么区别

性能上有很大差别

索引用得不太正常怎么处理？同时有（a，b）和（a，c）的索引，查询 a 的时候，会选哪个索引？

一次简单查询只会用到一次 索引. 查询优化器会判断 b 和 c 的区分度谁高, 区分度高的查询到的叶子节点数更少.

对 uuid 的理解？知道哪些 GUID、Random 算法？

GUID: 统一标识符算法

如果由大量的增删操作，那么应该选择哪个存储引擎，为什么？

从锁上来看, 选择 InnoDB, 因为 InnoDB 使用的是行级锁, 一般不会锁表, 并发性更好. 而 MyISAM 是的是表锁, 在增删高并发下很容易因为表锁导致阻塞.

常见数据库中间件有哪些

[2] 分布式事务

innodb是如何支持的事务

通过 undo log 来实现的事务

为什么myisam不采用和innodb相同的方案来解决事务问题

MyISAM 是为了性能而生的引擎, 没有 InnoDB 那样写多个 log 文件, 因为速度更快, 但是不支持事务, 崩溃恢复的能力.

为什么数据量大的时候会出现慢sql？

慢 SQL 如何解决

T（a,b,c,d） index（a,c,d） ， x = a and x = d / x = c and x = a / x = c and x = d

index(a, c, d) 会产生 (a), (a, c), (a, c, d) 三个索引都可以使用, 所以:

innodb存储引擎中是如何为磁盘io优化的。

使用 B+树 + 聚簇索引来优化的.

缓冲区的数据结构是怎样的？

你们生成id的速率超过了mysql的性能极限了吗？没超过为什么你们不采用MySQL生成全局唯一性id。

死锁什么时候会出现？应用层应该怎么做避免死锁？mysql 是怎么处理死锁的呢？

int 占多少字节？bigint 呢？int (3) 和 int (11) 有区别吗？可以往 int (3) 里存 1 亿吗？varchar 最长多少？

TinyInt 1字节 SmallInt 2字节 MediumInt 3字节 Int 4 字节: 4 * 8 = 32 位, [-2^31, 2^31 - 1] [0, 2^32 - 1] BitInt 8字节: 8 * 8 = 64 位, [-2^63, 2^63 - 1], [0, 2^64 - 1]

Int(3) 括号里的数只是显示宽度不同, 实际储存长度是一样的. Varchar 最大 65535 字节, 3字节的固定使用(起始位和结束位), 所以可以放 65532 字节的内容. Varchar(n) 表示能存放 n 个字符, 不论汉字和英文 Char 最大 255 字节, 固定长度, 不满补空格

explain

分布式 ID

[2] 索引慢分析

主键的特性以及原因

SQL 防注入

行锁 什么情况变 表锁

IO 过高优化