MySQL

MySQL面试题：聚簇索引：数据和索引存储到一起，找到索引就获取到了数据。聚簇索引是唯一的，InnoDB一定会有一个聚簇索引来保存数据。非聚簇索引一定存储有聚簇索引的列值。

MySQL

• 体系结构
  • 体系结构
    • 连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件
  • 查询执行流程
    • 简单来说分为五步：① 客户端发送一条查询给服务器。② 服务器先检查查询缓存，如果命中了缓存则立刻返回存储在缓存中的结果，否则进入下一阶段。③ 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。④ MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。⑤ 将结果返回给客户端。
  • 存储引擎
    • myisam与innodb的区别
      • 1>.InnoDB支持事物，而MyISAM不支持事物 2>.InnoDB支持行级锁，而MyISAM支持表级锁 3>.InnoDB支持MVCC, 而MyISAM不支持 4>.InnoDB支持外键，而MyISAM不支持 5>.InnoDB不支持全文索引，而MyISAM支持。
• InnoDB存储引擎
  • 特性
    • 插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
• 索引与算法
  • 索引
    • 索引分类
      • 数据结构：
        • B+TREE（Innodb和MyISAM默认） HASH（Mermory默认） r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。相对于b-tree，r-tree的优势在于范围查找.
      • 物理分类：
        • 聚簇索引：数据和索引存储到一起，找到索引就获取到了数据。聚簇索引是唯一的，InnoDB一定会有一个聚簇索引来保存数据。非聚簇索引一定存储有聚簇索引的列值； InnoDB聚簇索引选择顺序： 默认选择主键； 没有主键，选择唯一的非空索引；都没有，则隐式定义一个主键；
        • 非聚簇索引（也称为辅助索引或二级索引） 数据存储和索引分开，叶子节点存储对应的行，需要二次查找，通常称为[二级索引]或[辅助索引]；
      • 逻辑分类：
        • 按功能划分：主键索引，唯一索引，普通索引，全文索引 按列数划分：单列索引，组合索引
    • Hash索引和BTree索引:
      • Hash 索引仅仅能满足"=","IN"和" "查询，不能使用范围查询。
        • 由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
      • Hash 索引无法用来数据的排序操作。
        • 由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
      • Hash 索引不能利用部分索引键查询。
        • 子主题
        • 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
      • Hash 索引在任何时候都不能避免表扫描。
        • 前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
      • Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
        • 对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。
    • 索引失效
      • * 索引规范不合理,sql解析器不命中索引. * 表中索引是以表中数据量字段最多的建立的索引,sql解析器不命中索引.(实际就是索引没用,最后全局查找了) * bool的字段做索引,sql选择器不命中索引. * 模糊查询 %like * 索引列参与计算,使用了函数 * 非最左前缀顺序 * where对null判断 * where不等于 != , <> * or操作有至少一个字段没有索引 * 需要回表的查询结果集过大（超过配置的范围） * 字符串不加单引号索引失效
    • B+树
      • 不用B-树？
        • * 所有的叶子结点使用链表相连，，并且所有叶子节点之间都有一个链指针。 这样遍历叶子节点就能获得全部数据，便于区间查找和遍历。 * B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。 * B+树只有叶节点存放数据，其余节点用来索引，能容纳更多节点元素。而B-树是每个索引节点都会有Data域
      • 不用红黑树？
        • 大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。
      • 树的查询时间跟树的高度有关，B+树是一棵多路搜索树可以降低树的高度，提高查找效率
• 锁
  • 锁
    • 锁类型
      • 共享锁:S Lock
        • 允许事务读一行数据
      • 排他锁:X Lock
        • 允许事务删除或更新一行数据
    • 锁算法
      • Record Lock:单行记录上的锁
      • Gap Lock:间隙锁，锁定一个范文，但不包含记录本身
      • Next-Key Lock：Record Lock+Gap Lock,锁定一个范文，并且锁定记录本身
    • 锁问题
      • 脏读
        • 脏数据：事务对缓冲池中的杭记录修改，但未提交
        • 读到的是未提交的数据
      • 不可重复读：读到的是已经提交的数据
      • 死锁
        • 多个事务争夺锁资源而造成的一种互相等待的现象
• 性能与调优
  • 1、数据类型优化
    • * 一般情况下，尽量使用可以正确存储数据的最小数据类型。，因为占用的磁盘内存和cpu缓存也少。 * 尽量避免null值 * timestamp占4字节，但会根据时区变化，且有自动更新能力，datetime占8字节。
  • 2、高性能索引优化
  • 3、SQL优化
    • 分解关联查询，让缓存效率更高，执行单个查询减少锁的竞争。
    • 优化 COUNT 查询
    • 优化关联查询
    • 优化 GROUP BY
    • 优化 LIMIT 分页
    • 优化 UNION 查询
    • 优化 INSERT
  • 4、应用层优化
  • 5、服务器配置优化
  • 6、操作系统和硬件优化
• 备份与恢复
  • 主从复制
    • 1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中； 2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中； 3. 从：sql执行线程——执行relay log中的语句；
• 事务
  • ACID特性
    • 1.原子性 atomicity 一个事务在逻辑上是必须不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说不可能只执行其中的一部分。 2.一致性 consistency 数据库总是从一个一致性的状态转换到另一个一致性的状态。 3.隔离性 isolation 针对并发事务而言，隔离性就是要隔离并发运行的多个事务之间的相互影响，一般来说一个事务所做的修改在最终提交以前，对其他事务是不可见的。 4.持久性 durability 一旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。
  • 隔离级别
    • 1.未提交读 READ UNCOMMITTED 在该级别事务中的修改即使没有被提交，对其他事务也是可见的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还会导致不可重复读和幻读，性能没有比其他级别好很多，很少使用。 2.提交读 READ COMMITTED 多数数据库系统默认的隔离级别。提交读满足了隔离性的简单定义：一个事务开始时只能"看见"已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前的任何修改对其他事务都是不可见的。也叫不可重复读，因为两次执行同样的查询可能会得到不同结果。 3.可重复读 REPEATABLE READ（MySQL默认的隔离级别） 可重复读解决了不可重复读的问题，保证了在同一个事务中多次读取同样的记录结果一致。但还是无法解决幻读，所谓幻读指的是当某个事务在读取某个范围内的记录时，会产生幻行。InnoDB 存储引擎通过多版本并发控制MVCC 解决幻读的问题。 4.可串行化 SERIALIZABLE 最高的隔离级别，通过强制事务串行执行，避免幻读。可串行化会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，只有非常需要确保数据一致性且可以接受没有并发的情况下才考虑该级别。
  • 事务的实现
    • redo log
      • 保持事务的持久性
    • undo log
      • 帮助事务回滚及MVCC的功能
• 文件
  • 日志
    • 错误日志error log
      • 记录出错信息，也记录一些警告信息或者正确的信息。
    • 慢查询日志slow query log
      • 设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
    • 二进制日志bin log
      • 记录对数据库执行更改的所有操作。
    • 中继日志：
    • 事务日志：
    • 查询日志：log
      • 记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
  • 子主题
• 数据类型
  • 时间
    • 1、DATETIME 能保存大范围的值，从 1001~9999 年，精度为秒。把日期和时间封装到了一个整数中，与时区无关，使用 8 字节存储空间。
    • 2、TIMESTAMP 和 UNIX 时间戳相同，只使用 4 字节的存储空间，范围比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依赖于时区。
  • 字符串
    • VARCHAR 用于存储可变字符串，是最常见的字符串数据类型。它比 CHAR 更节省空间，因为它仅使用必要的空间。VARCHAR 需要 1 或 2 个额外字节记录字符串长度，如果列的最大长度不大于 255 字节则只需要 1 字节。VARCHAR 不会删除末尾空格。 VARCHAR 适用场景：字符串列的最大长度比平均长度大很多、列的更新很少、使用了 UTF8 这种复杂字符集，每个字符都使用不同的字节数存储。
    • CHAR 是定长的，根据定义的字符串长度分配足够的空间。CHAR 会删除末尾空格。 CHAR 适合存储很短的字符串，或所有值都接近同一个长度，例如存储密码的 MD5 值。对于经常变更的数据，CHAR 也比 VARCHAR更好，因为定长的 CHAR 不容易产生碎片。对于非常短的列，CHAR 在存储空间上也更有效率，例如用 CHAR 来存储只有 Y 和 N 的值只需要一个字节，但是 VARCHAR 需要两个字节，因为还有一个记录长度的额外字节
• 其他
  • MySQL窗口函数
    • ROW_NUMBER()
    • RANK()
    • DENSE_RANK()