4、mysql

1、什么是Buffer Pool

缓冲池，用来缓存表数据与索引数据，减少磁盘IO，提升效率

• Buffer Pool由缓存数据页（Page）和 对缓存数据页进行描述的控制块组成，控制块中存储着对应缓存页的所属表空间、数据页的编号、以及对应缓存页在Buffer Pool中的地址等信息。
• Buffer Pool默认大小128M，以Page页为单位，Page页默认大小16K，控制块大小约为数据页的5%，大概800字节

2、InnoDB如何管理Page页

• Page页分类
  • free page：空闲page，未被使用
    • free list：表示空闲缓冲区，管理free list
      • free链表是把所有空闲的缓冲页对应的控制块作为一个个的节点放到一个链表中，这个链接便称之为free链表
      • 基节点：free链表中只有一个基节点是不记录缓存页信息（单独申请空间），它里面就存放了free链表的头节点的地址，尾节点的地址，还有free链表里面当前有多少个节点。
  • clean page：被使用page，数据没有被修改过
    • lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少的数据，占
  • dirty page：脏页，被使用page，数据被修改过，Page页中数据和磁盘的数据产生了不一致
    • flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序
      • InnoDB引擎为了提高处理效率，在每次修改缓冲页后，并不是立刻把修改刷到新磁盘上，而是在未来的某个时间点进行刷新操作，所以需要使用到flush链表存储脏页，凡是被修改过的缓冲页对应的控制块都会作为节点加入到flush链表
      • flush链表的结构与free链表的结构相似

3、为什么写缓冲区，仅适合用于非唯一普通索引页？

• change Buffer：写缓冲区，是针对二级索引（辅助索引）页的更新优化措施
  • 作用：在进行DML操作时，如果请求的辅助索引（二级索引）没有在缓冲池中，并不会立刻将磁盘叶加载到缓冲池，而是在change buffer记录缓冲变更，等未来数据被读取时，再将数据合并恢复到buffer pool中
  • changebuffer用于存储SQL变更操作，比如insert、update、delete等sql语句
  • changebuffer中的每个变更操作都有其对应的数据页，并且该数据页未加载到缓冲中
• 原因：如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一个IO操作。会直接将记录查询到BufferPool中，然后在缓冲池中修改，不会在ChangeBuffer中操作

4、Mysql为什么改进LRU算法？

• 普通LRU：最近最少使用，就是末位淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰
  • 优点：所有最近使用的数据都在链表头部，最近未使用的数据都在链表表尾，保证热数据能最快被获取到
  • 缺点：如果发生全表扫描，很有可能将真正的热数据淘汰掉
    • 由于Mysql中存在预读机制，很多预读的页都会被放到LRU链表的表头。如果这些预读的页都没有用到的话，会导致很多尾部的缓冲页很快就会被淘汰
• 改进型LRU：将链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入（也就是放在冷数据区的头部），如果数据很快被访问，那么page就会像new列表的头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰
• 冷数据区的数据页什么时候会被转到热数据区呢？
  • 如果该数据页在LRU链表中存在时间超过1s，就将其移动到链表头部
  • 如果该数据页在lru链表中存在的时间短于1s，其位置不变（由于全表扫描有一个特点，就是它对某个页的频繁访问总耗时会很短）
  • 1s这个时间是由参数innodb old blocks time控制的

5、使用索引一定可以提升效率吗？

• 索引就是排好序的，帮助我们进行快速查找的数据结构
• 简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能
• 优点
  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
• 缺点
  • 创建索引和维护索引要耗费时间，这种事件随着数据量的增加而增加
  • 索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
  • 当对表中的数据进行增加、删除和修改时，索引也要动态的维护，降低了数据的维护速度
• 创建索引的原则
  • 在经常需要搜索的列上创建索引、可以加快搜索的速度；
  • 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
  • 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
  • 在经常使用的WHERE子句中的列上面创建索引，加快条件的判断速度

6、说一下聚簇索引和非聚簇索引

• 聚簇索引和非聚簇索引区别：叶子节点是否存放一整行的记录

  • 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
  • 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

• InnoDB主键使用的是聚簇索引，MyISAM主键和二级索引都使用的非聚簇索引

• 聚簇索引（聚集索引）：索引和数据存储在同一个文件

  • 聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑的存储在一起。这也意味着InnoDB的主键索引就是数据本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

  • InnoDB的表要求必须要有聚簇索引

    • 如果表定义了主键，则主键索引就是聚簇索引
    • 如果表没有定义主键，则第一个非空unique列作为聚簇索引
    • 否则InnoDB会创建一个隐藏的row-id作为聚簇索引

  • 辅助索引（非聚簇）

    InnoDB的富足索引，也叫做二级索引，是根据索引列构建B+Tree结构。但在B+Tree的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，通常创建辅助索引就是为了提升查询效率。一个InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引

• 非聚簇索引：索引和数据分开文件存储

  • 与InnoDB不同，MyISAM使用的是非聚簇索引，非聚簇索引的两个B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助索引B+树存储了辅助建。
  • 表数据存储在独立的位置，这两颗B+树的叶子节点都使用一个地址指向真正的表的数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树

• 聚簇索引的优点

  • 当你需要取出一定范围内的数据时，用聚簇索引比非聚簇索引好
  • 当通过聚簇索引查找目标数据时，理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址，即多一次IO操作
  • 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

• 聚簇索引的缺点

  • 插入速度验证依赖于插入顺序
  • 更新主键的代价很高，因为将会导致被更新的行移动
  • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

7、索引有哪几种类型

• 普通索引：基于普通字段创建，没有任何限制
• 唯一索引：索引字段必须唯一，允许有空值，因为null是未知，未知和未知比较也是未知
• 主键索引：不允许有空值，每个表只有一个主键
• 复合索引：多个字段一起创建索引
• 全文索引：fulltext关键字，需要配合WHERE MATCH(字段名) AGAINST("搜索值")使用

8、最左前缀法则

创建的联合索引，使用索引时，where后面的条件需要从索引的最左前列开始使用，并且不能跳过索引中的列使用

• 索引：A+B+C
• 查询
  • 不能跳过A，查BC
  • 查AC，A生效，C不会生效

9、什么是索引下推

• 条件：A和B建立联合索引
• 场景：select * from table where A like 'a%' and b = 1;
• 不使用索引下推
  • 在辅助索引中查询到所有like条件的值，然后全部都回表查询，这样可能会多出来很多的回表操作
• 使用索引下推
  • 在辅助索引中查询到like条件的值之后，继续判断后面索引的条件，筛选出符合条件的值之后，再进行回表操作，这样可能会少很多次的IO回表，从而提升整体的性能

10、什么是自适应hash索引

• 自适应hash索引是InnoDB对查询的一个优化操作，三大特性之一（Buffer Pool、Boublewrite Buffer（双写缓冲区））
• 存在于内存中，默认开启
• 创建：如果某个查询满足hash索引的数据结构特点（散列表），就建立一个索引
• 下次查询的时候可以根据hash索引直接找到叶子节点的位置，不用通过主键索引进行检索
• 只适用于等值查询

11、为什么LIKE以%开头索引会失效

• 因为索引按照字符串进行了排序，如果左边字符就是模糊的话，无法进行首字母匹配
• 怎么解决：索引覆盖

12、数据库主键类型的选择，自增和UUID

• 自增的优点
  • 字段长度小，便于检索
  • 新增的数据永远在后面，对于性能有很大的提升
  • 数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利
  • 数字型，占用空间小，易排序，在程序中传递方便
• 自增的缺点
  • 由于是自增，很容易被爬虫知道当前系统的业务量
  • 高并发情况下，竞争自增锁会降低数据库的吞吐能力
  • 数据迁移或分库分表场景下，自增方式不再适用
• UUID优点
  • 不会冲突。进行数据拆分、合并存储的时候，能够保证主键全局的唯一性
  • 可以在应用层生成，提高数据库吞吐能力
• UUID缺点
  • 影响插入速度，并且造成硬盘使用率低。与自增相比，最大的缺陷就是随机io
  • 字符串类型相比整数类型肯定更消耗空间，而且会比证书类型操作慢
• 选择
  • 使用InnoDB尽可能按照主键自增的顺序插入
  • 如果是分库分表，分布式主键ID的生成方案优先选择雪花算法生成全局唯一主键，而且雪花算法生成的主键在一定程度上是有序的

13、InnoDB和MyISAM的区别

• 事务和外键
  • InnoDB支持事务和外键，具有安全性和完整性，适合大量的insert或update操作
  • MyISAM不支持事务和外键，他提供高速存储和检索，适合大量的select操作
• 锁机制
  • InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现
  • MyISAM支持表级锁，锁住整张表
• 索引结构
  • InnoDB使用聚簇索引，索引和记录在一起存储，技能缓存索引，也能缓存记录
  • MyISAM使用非聚簇索引，索引和记录分开
• 并发处理能力
  • MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞
  • InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发
• 存储文件
  • InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB
  • MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制是256TB
• 场景：
  • 查询多，对数据一致性要求不高：MyISAM
  • 事务、并发、写频繁、数据一致性高用InnoDB

14、B树和B+树的区别是什么？

• B树所有节点存数据，B+树叶子节点存储数据
• B树叶子节点没有双向链表，B+树叶子节点有双向链表
• 子节点没有父节点的冗余，B+树叶子节点冗余了所有的叶节点数据

15、一个B+树中能存放多少条索引记录

• 每一个节点相当于一页
• 页大小：16KB
• 如果主键是int类型：int4个字节+指针6个字节，16KB/10B = 1638个索引
• 假设一条数据1K，三层大概4000W数据

16、explain用过吗？主要有哪些字段？

• id：select子句执行的优先级，越大越高
• select_type：查询类型
• table：正在访问的表
• type：使用索引的类型，一般要优化到ref
• possible_keys：可能用到的索引
• key：实际用到的索引
• key_len：索引长度
• rows：查询可能用到的总行数
• Extra：

17、type字段中常见的值

• system：不进行磁盘IO，查询系统表，仅仅返回一条数据
• const：查找主键索引，最多返回1条或0条数据，数据精确查找
• eq_ref：查找唯一性索引，返回数据最多一条，属于精确查找
• ref：查找非唯一索引，返回匹配某一条件的多条数据，属于精确查找，数据返回可能是多条
• range：查找某个索引的部分索引，只检索给定范围的行，数据范围查找，比如>、<、in、between
• index：查找所有索引树，比ALL快一些，因为索引文件要比数据文件小
• ALL：不使用任何索引，直接全表扫描

18、Extra有哪些只要指标，各自含义是什么？

• Using filesort：无法利用索引完成排序，称为”文件排序“
• Using index：索引覆盖，无需回表
• Using index condition：一部分条件能用索引，先根据索引查，再找不能通过索引查的数据
• Using join buffer：使用了连接缓存，会显示join连接查询时，mysql选择的查询算法
• Using temporary：临时表。常见于排序和分组
• Using where：全表扫描或者在查找使用索引的情况下，还有查询条件不在索引字段中，还是要全表扫描

19、如何进行分页查询优化？

• 如果偏移量一定，返回记录越多，花费时间越长
• 返回记录一定，偏移量越大，花费时间越长
• 优化1：通过索引进行分页
• 优化2：利用子查询优化
  • 先查询符合条件的id，然后通过id查询所有数据

20、如何做慢查询优化？

• MySQL慢查询的相关参数解释：
  • slow_query_log：默认关闭，是否开启慢查询日志，ON（1）表示开启，OFF(0)表示关闭
  • slow-query-log-file：慢查询日志路径
  • long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志
• 日志内容
  • Time：执行时间
  • User：用户信息，id信息
  • Query_time：查询时长
  • Lock_time：等待锁的时长
  • Rows_sent：查询结果的行数
  • Rows_examined：查询扫面的行数
  • SET timestamp：时间戳
  • SQL的具体信息
• SQL性能下降的原因
  • 等待时间长
    • 锁表导致
  • 执行时间长
    • 查询语句写的烂
    • 索引失效
    • 关联查询太多join
    • 服务器调优及各个参数的设置
• 慢查询优化思路
  • 优先选择高并发执行的SQL
  • 定位优化对象的性能瓶颈
  • 明确优化目标
  • 从explain执行计划入手
  • 永远用小的结果集驱动大的结果集
  • 尽可能在索引中完成排序
  • 只获取自己需要的列
  • 只是用最有效的过滤条件
  • 尽可能避免复杂的join和子查询
  • 合理设计并利用索引