MySql面试问题

JXSundwich
109 阅读54分钟

B树&B+树

这两个的插入操作都是在叶子上进行的 但是B树可以在非叶子结点存值 B+树只能在叶子结点存值

B树

参考：zhuanlan.zhihu.com/p/35811482 B树（B-TREE）满足如下条件，即可称之为m阶B树：

  • 每个节点至多拥有m棵子树
  • 根节点至少拥有两棵子树
  • 除了根节点以外，其余每个分支结点至少拥有m/2课子树
  • 所有的叶子结点都在同一层上
  • 有k棵子树的分支节点则存在k-1个关键码，关键码按照递增次序进行排列
  • 关键字数量需要满足ceil(m/2)-1<=n<=m-1
B+树

B+树满足如下条件，即可称之为m阶B+树：

  • 根节点只有一个，分支数量范围[2,m];
  • 分支节点，每个节点包含分支数范围为[ceil(m/2),m];
  • 分支结点的关键字数量等于其子分支的数量减一，关键字的数量范围为[ceil(m/2)-1, m-1]，关键字顺序递增；
  • 所有叶子结点都在同一层 image.png B+树总是会保持平衡。但是为了保持平衡，对于新插入的键值可能需要做大量的拆分页（split）操作，而B+树主要用于磁盘，因此页的拆分意味着磁盘数据移动，应该在可能的情况下尽量减少页的拆分。因此，B+树提供了旋转（rotation）的功能。旋转发生在Leaf Page已经满了、但是其左右兄弟节点没有满的情况下。这时B+树并不会急于去做拆分页的操作，而是将记录移到所在页的兄弟节点上。通常情况下，左兄弟被首先检查用来做旋转操作，在第一张图情况下，插入键值70，其实B+树并不会急于去拆分叶节点，而是做旋转，50，55，55旋转。

image.png image.png

B树与B+树的区别 以m阶树为例：

  • 关键字不同

数据库引擎

存储引擎是数据库底层的组件，是数据库的核心。 使用存储引擎可以创建、查询、更新、删除数据库。存储引擎可以理解为数据库的操作系统，不同的存储引擎提供的存储方式、索引机制等也不相同，就像 Windows 系统和 Mac 系统一样。在数据库开发时，为了提高 MySQL 的灵活性和高效性，可以根据实际情况来选择存储引擎。

image.png

MySql索引

  • B+树索引
  • Hash索引
  • 全文索引

InnoDB索引与MySAM索引的区别

image.png

使用的都是B+树

  • InnoDB的辅助索引data域存储相应记录主键的值而不是地址
  • InnoDB的数据文件本身就是主索引文件
  • MyISAM的索引和数据是分开存储的

一个表如果没有创建索引，那么还会创建B+树吗

有 聚簇索引 每个InnoDB都拥有一个索引，称之为聚簇索引，此索引中存储着行记录，一般来说，聚簇索引是根据主键生成的。 聚簇索引按照如下规则创建：

  • 当定义了主键后，InnoDB会利用主键来生成其聚簇索引
  • 如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引
  • 如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引

补充： 辅助索引

除了聚簇索引之外的索引都可以称之为辅助索引

区别：1. 辅助索引的叶子节点中存放的是主键的键值，聚簇索引存放的是行数据 2. 一张表可以有多个辅助索引，但是只能有一个聚簇索引

通过辅助索引来查找对应的行记录的话，需要进行两步，第一步通过辅助索引来确定对应的主键,第二步通过相应的主键值在聚簇索引中查询到对应的行记录，也就是进行两次B+书搜索。相反通过辅助索引来查询主键的话，遍历一次辅助索引就可以确定主键了，也就是所谓的索引覆盖，不用回表（查询聚簇索引）。

创建辅助索引，可以创建单列的索引，也就是用一个字段来创建索引，也可以用多个字段来创建副主索引成为联合索引，创建联合索引后，B+书的节点存储的键值数量不是1个，而是多个，如下图： image.png

  • 联合索引的B+树和单键辅助索引的B+树是一样的，键值都是排序的，通过叶子节点可以逻辑顺序的读出所有的数据，比如上图所存储的数据时，按照(a,b)这种形式(1,1),(1,2),(2,1),(2,4),(3,1),(3,2)进行存放，这样有个好处存放的数据时排了序的，当进行order by对某个字段进行排序时，可以减少复杂度，加速进行查询；

  • 当用select * from table where a=? and b=?可以使用索引(a,b)来加速查询，但是在查询时有一个原则，sql的where条件的顺序必须和二级索引一致，而且还遵循索引最左原则，select * from table where b=?则无法利用(a,b)索引来加速查询。

  • 辅助索引还有一个概念便是索引覆盖，索引覆盖的一个好处便是辅助索引不高含行记录，因此其大小远远小于聚簇索引，利用辅助索引进行查询可以减少大量的IO操作。

说一下B+树索引实现原理

B+树每个页16k 能存储多少条数据取决于一条数据有多大

image.png

image.png

每个页面存储了上个页的地址和下个页的地址（双向链表）

聚簇索引和非聚簇索引B+树实现有什么区别

聚簇索引 特点：

  • 索引和数据保存在同一个B+树里面
  • 页内的记录是按照主键的大小顺序排成一个单向链表
  • 页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表
  • 非叶子节点存储的是记录的主键+页号
  • 叶子节点存储的是完整的用户记录

数据格式：

image.png

页格式：

image.png

优点：

image.png

为了充分利用聚簇索引的聚簇特性，InnoDB中表的主键应该选择有序的id，不建议选择使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长

非聚簇索引 （二级索引，辅助索引） 聚簇索引，只有在搜索条件是主键值的时候才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建非聚簇索引

image.png 下面黄色的是主键id

这个B+树与聚簇索引有几处不同

  • 页内的记录是按照从c2列的大小顺序排成一个单向链表
  • 页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表
  • 非叶子节点存储的是记录的c2列+页号
  • 叶子节点存储的并不是完整的用户记录而只是c2列+主键这两个列的值

B+树中的聚簇索引的查找（匹配）逻辑

非聚簇索引的查找逻辑

平衡二叉树、红黑树、B树和B+树的区别是什么，都有哪些应用场景

平衡二叉树AVL： 每个节点只能存储一条数据，可能会出现树高过高的情况，自旋过多产生的开销很大

红黑树：

  • hashmap存储
  • 两次旋转达到平衡
  • 分为红黑节点

B树和B+树的区别：

  • B+树的非叶子节点的关键字也会同时存在子节点中并且是子节点中所有关键字的最大值（或最小）
  • B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引又保存数据记录
  • B+树中所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接

在MySql5之前InnoDB底层用的是B树

一个B+树大概能存放多少条索引

image.png

image.png

使用B+树存储的索引crud执行效率如何？

  1. 搜索操作的时间复杂度为O(log n)，其中n为B+树中存储的关键字总数。搜索操作的过程类似于二分查找，它可以在较短的时间内找到所需的关键字。
  2. 插入操作的时间复杂度也为O(log n)。在插入新的关键字时，B+树需要先进行搜索，然后在叶子节点上插入新的关键字。如果插入导致某个节点的关键字数超过了上限，则需要进行分裂操作，将该节点分成两个节点。分裂操作可能会导致树的高度增加，但是它不会影响搜索和删除操作的时间复杂度。
  3. 删除操作的时间复杂度也为O(log n)。在删除关键字时，B+树需要先进行搜索，然后将关键字从叶子节点中删除。如果删除导致某个节点的关键字数低于了下限，则需要进行合并操作，将该节点与相邻节点合并。合并操作也可能会导致树的高度减小，但是它同样不会影响搜索和插入操作的时间复杂度。

什么是2-3树 什么是2-3-4树

2-3树 image.png 根节点下面最多两个节点，再往下最多三个节点 可以少不能多 2-3-4 image.png 和上方类似

为什么官方推荐推荐自增主键作为索引（说一下自增主键和字符串类型主要主键的区别和影响）

  • 自增主键可以维持底层数据顺序读写
  • 读取可以由B+树的二分查找定位
  • 支持范围查找，范围数据自带顺序

使用int自增主键后，最大id是10，删除id10和9，在添加一条记录，最后添加的id是几？删除后重启mysql然后添加一条记录最后id是几？

删除之后

  • 如果重启，会从最大id开始递增
  • 如果没有重启，会延续删除之前的id开始递增

索引的优缺点是什么

优点： 聚簇（主键）索引：

  • 顺序读写
  • 范围快速查找
  • 范围查找自带顺序

非聚簇索引：

  • 条件查询避免全表扫描scan
  • 范围、排序、分组查询返回行id，排序后，再回表查询完整数据，有可能利用顺序读写
  • 覆盖索引不需要回表操作

索引的代价 索引是个好东西，可不能乱建，它在空间和时间上都会有消耗

  • 空间上的代价
  • 每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。
  • 时间上的代价 每次对表中的数据进行增删改操作时，都需要去修改各个B+索引树。而增删改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位、页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建立了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。 B树和B+都可以作为索引的数据结构，在MySql中采用的是B+树。但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然

使用索引一定能提升效率吗

  • 数据很少，没必要
  • 索引建太多会影响效率
  • 唯一索引，插入时先检查有没有这个值，然后再写，更麻烦，但是该用的时候还是要用

如果是大段的文本内容，如何创建（优化索引）

  • 第一种是分表存储，然后创建索引
  • 第二种是使用es为大文本创建索引

非聚簇索引为什么只存储主键不存储数据地址

因为聚簇索引有时会引发分页操作、重排操作数据可能会移动

什么是回表操作

id age name sex age->index 第一次 取回id，第二次（回表）根据id拿到对应的数据 select * from user where age>20;

什么是覆盖索引

age,name->index select age from user where age>20 and name like "张%"; 覆盖索引不会回表查询，查询效率也是比较高的

非聚簇索引一定会回表查询吗

不一定，只要创建索引的字段，包含（覆盖）了想要select的字段，那么就不会回表查询了。

为什么一定要回表查询，直接存储数据不可以吗

为了控制非聚簇索引的大小

如果把一个InnoDB表的主键删掉，是不是就没有主键，就没有办法进行回表查询了？

不是，InnoDB会生成rowid辅助回表查询

什么是联合索引，组合索引，复合索引？

c2,c3->index 全值匹配c2 c3 最左前缀c2

什么是唯一索引

  • 随表创建

image.png

  • 单独创建唯一索引

image.png

image.png

什么时候使用唯一索引

只要业务上需要有唯一特性的字段，甚至是唯一特性的组合字段，都需要建唯一索引。

什么时候适合创建索引，什么时候不适合创建索引

适合建立索引

  • 频繁作为where条件语句查询字段
  • 关联字段需要建立索引
    • 关联字段的索引可以提高多表关联查询的效率。当我们通过关联字段进行查询时，数据库会使用索引来定位到符合条件的记录，而不需要依次扫描整个表。这样可以大大减少查询的时间复杂度。
  • 排序字段可以建立索引
  • 分组字段可以建立索引（因为分组前提是排序）
  • 统计字段可以建立索引

不适合建立索引

  • 频繁更新的字段不适合建立索引
  • where，分组，排序中用不到的字段不必要建立索引
  • 可以确定表数据非常少不需要建立索引
  • 参与mysql函数计算的列不适合建索引

什么是索引下推

select name, page from user where name like "张%" and age =18

  • 如果没有索引下推需要两次回表 先筛选第一个条件，回表，再筛选第二个条件，再回表
  • 有索引下推就可以两个条件一起筛选，减少回表操作
  • MySql5.6之前是没有索引下推这个优化的 Using index condition:叫做 Index Condition Pushdown optimization（索引下推优化）

image.png

image.png

哪些情况会使索引失效

  • 计算，函数导致索引失效

image.png

image.png

  • like以%,_开头索引失效

image.png

  • 不等于(!=或者<>)索引失效

image.png

  • IS NOT NULL可能会失效 mysql内部优化器决定是否走索引 当null比较多的时候可能会走索引

image.png

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

id,name,age select * from user where name like '%明'; 这个一定不会走索引 type=all

select name from user where name like '%明'; type=index

其实不会完全失效，覆盖索引下会出现type=index，表示遍历了索引树，再回表查询，覆盖索引没有生效的时候会直接type=all

如何查看一个表的索引

show index from t_emp;//显示表上的索引 explain select * from t_emp where id=1;

能否看到索引选择的逻辑？是否使用过optimizer_tracer？

image.png information_schema.OPTIMIZER_TRACER;

image.png 注意这个cost cost越少执行效率越高 调优sql的时候可以打开看看 线上开发环境千万别打开

多个索引优先级是如何匹配的

  1. 主键（唯一索引）匹配
  2. 全值匹配（单值匹配）
  3. 最左前缀匹配
  4. 范围匹配
  5. 索引扫描
  6. 全表扫描

image.png

使用Order By时能否通过索引排序

没有过滤条件不走索引

image.png

通过索引排序内部流程是什么

关键配置

  • sort_buffer 可供排序的内存缓冲区大小
  • max_length_for_sort_data 单行所有字段总和限制，超过这个大小启动双路排序
  1. 通过索引检索过滤筛选条件到需要排序的字段+其他字段（如果是符合索引）
  2. 判断索引内容是否覆盖select的字段
    1. 如果覆盖索引，select的字段和排序都在索引上，那么在内存中进行排序，排序后输出结果
    2. 如果索引没有覆盖查询字段，接下来计算select的字段是否超过max_length_for_sort_data限制，如果超过，启动双路排序，否则使用单路。

什么是单路排序和双路排序

  • 单路排序： 一次取出 所有字段进行排序，内存不够用的时候会使用磁盘
  • 双路排序：取出排序字段进行排序，排序完成后再次回表查询所需要的其他字段

group by和order by在索引使用上有什么区别

group by使用索引的原则几乎跟order by一致，唯一区别：

  • group by先排序在分组，遵照索引键的最左前缀法则
  • group by没有过滤条件，也可以用上索引。order by必须有过滤条件才能使用上索引

如果有字段为null，又经常被查询该不该给这个字段创建索引

应该创建索引，使用的时候尽量使用IS NULL判断

  • IS NOT NULL失效

有字段为NULL索引是否会失效

不一定会失效，每一条sql具体有没有使用索引 可以通过trace追踪一下 最好还是给上默认值 数字类型的给0，字符串给“”

MySql内部支持缓存吗

mysql5.7支持缓存，8.0之后就废弃掉了

mysql8为何废弃掉查询缓存

image.png

替代方案是什么

应用层组织缓存，最简单的是使用redis，ehcache等

MySQl内部有哪些核心模块组成，作用是什么

image.png 连接，连接池复用

MySql的事务

Mysql的事务主要用于处理操作量大，复杂程度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

事务的操作

  • 开启事务：start transaction
  • 回滚：rollback
  • 提交：commit
  1. 事务提交的两种方式：
  • 自动提交：MySQL数据库默认是自动提交的，一条DML（增删改）会自动提交一次事务
  • 手动提交：需要先开启食物（START TRANSACTION），再提交（COMMIT）
  1. 修改事务的默认提交方式
  • 查看默认提交方式：SELECT @@autocommit；--1代表自动提交，0代表手动提交
  • 修改默认提交方式：SET @@autocommit=0; --1代表自动提交，0代表手动提交

事务的四大特征（ACID）

  1. 原子性（Atomicity）：是不可分割的最小操作单位，要么同时成功，要么同时失败
  2. 一致性（Consistency）：表示事务操作前后，数据总量不变，从一个一致状态变为另一个一致状态
  3. 隔离性（Isolation）：多个事务之间相互独立
  4. 持久性（Durability）：事务一旦提交或回滚，数据表的数据将被持久化的保存

事务的隔离级别

  • 存在的问题
    • 脏读：一个事务读取到另一个事务中没有提交的数据，然后另一个事务回滚了，就读到了脏数据
    • 修改丢失：是指一个事务读取一个数据时，另外一个事务也访问了这个数据，那么在第一个事务修改了这个数据之后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，这种情况就被称为修改丢失
    • 不可重复读：在同一个事务中两次读取到的数据不一样
    • 幻读：指同一个事务哪多次查询返回的结果集总数不一样（比如增加或者减少了行记录）
  • 隔离级别：
    • read uncommitted：读未提交
    • read committed：读已提交（Oracle默认）
    • repeatable read：可重复读（MySQL默认）
    • serializable：串行化

image.png 注意：隔离级别从小到大，安全性越来越高，但是效率越来越低。但是一般情况下不会修改数据库默认的隔离级别，只有在极其特殊的情况下才会做出修改来解决的一些特殊问题

不可重复度和幻读的区别

  • 不可重复读 针对的是对一份数据的修改
  • 幻读 针对的是行数修改

默认的级别是什么

MySQL InnoDB存储引擎默认的事务隔离级别是可重复度（REPEATABLE-READ）

靠缓存可以提升事务隔离级别的性能吗

不仅要保证数据库的数据一致性，还要保证缓存的数据一致性，会进一步恶化写入的性能，mysql的事务本来也是针对写入的 如果只是读，那mysql本身也没有问题

什么是事务隔离级别

  • 串行化 对整张表加锁，读锁可以重复获取，写锁不可以

什么是一致性非锁定读和锁定读

锁定读 使用到了读写锁 读写锁是最简单直接的事务隔离实现方式

  • 每次毒操作需要获取一个共享（读）锁，每次写操作需要获取一个写锁。
  • 共享锁之间不会产生互斥，共享锁和写锁之间、以及写锁和写锁之间会产生互斥。
  • 当产生锁竞争时，需要等待其中一个操作释放锁以后，另一个操作才能获取到锁。

锁机制，解决的就是多个事务同时更新数据，此时必须要有一个加锁的机制

  • 行锁（记录锁）：解决的就是多个事务同时更新一行数据
  • 间隙锁：解决的就是多个事务同时更新多行数据

下列操作属于锁定读

select ... lock in share mode
select ... for update
insert,update,delete

非锁定读 使用MVCC多版本控制实现（Muti-Version Concurrency Control 多版本并发控制，一般在数据库管理系统中，实现对数据库的并发访问）

数据库查询隔离级别：select @@tx_isolation;

数据库的锁：

image.png

  1. 行锁 行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引，一锁锁一条或多条记录

image.png 如上图所示，数据库表中有一个主键索引和一个普通索引，Sql语句基于索引查询，命中两条记录。此时行锁一锁就锁定两条记录，当其他事务访问数据库同一张表时，被锁定的记录不能被访问，其他的记录都可以访问到。

行锁的特征：锁冲突概率低，并发性高，但是会有死锁的情况出现。

  1. 表锁 表锁响应的是非索引字段，即全表扫描，在表被锁定期间，其他事务不能对该表进行操作

由于表锁每次都是锁一整张表，所以表锁的锁冲突几率特别高，表锁不会出现死锁的情况

  1. 记录锁

记录锁锁的是表中的某一条记录，记录锁的出现条件是必须精准命中索引并且索引是唯一索引，如主键id image.png

  1. 间隙锁

间隙锁锁住一个间隙以防止插入。假设索引列有2, 4, 8 三个值，如果对 4 加锁，那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入索引值在这两个间隙之间的记录。但是，间隙锁有个例外:

  1. 如果索引列是索引，那么只会锁住这条记录(只加行锁)，而不会锁住间隙。
  2. 对于联合索引且是索引，如果 where 条件只包括联合索引的一部分，那么依然会加间隙锁。
  1. 临键锁 next-key lock

mysql的行锁默认就是使用的临键锁，临键锁是由记录锁和间隙锁共同实现的。 临键锁的触发条件是查询条件命中索引并且有匹配到数据库记录

间隙锁锁定的区间是一个左开右闭的集合，而临键锁锁定的是当前记录的区间和下一个记录的区间。

image.png

从上图我们可以看到，数据库中只有三条数据1、5、7，当修改范围为1~8时，则锁定的区间为(1,+∞)，锁定额不单是查询范围，并且还锁定了当前范围的下一个范围区间，此时，查询的区间8，在数据库中是一个不存在的记录值，并且，如果此时的查询条件是小于或等于8，也是一样的锁定8到后面的区间。

如果查询的结尾是一个存在的值，此时又会怎样呢？现在数据库有三条数据id分别是1、5、7，我们查询条件改为大于1小于7再看看。

image.png

此时，我们可以看到，由于7在数据库中是已知的记录，所以此时的锁定后，只锁定了(1,7]，7之后的数据都没有被锁定。我们还是可以正常插入id为8的数据及其后面的数据。

所以，临键锁锁定区间和查询范围后匹配值很重要，如果

  1. 后匹配值存在，则只锁定查询区间
  2. 后匹配值不存在则锁定查询区间和后匹配值与它的下一个值的区间。

一条sql发送给mysql后，内部是如何执行的？（说一下MySQL执行一条查询语句的内部执行过程）

image.png

  1. Mysql客户端通过协议与MySQL服务器建连接，通过SQL接口发送SQL语句，先检查查询缓存（SQL转为hash看是否命中），如果命中，直接返回结果，否则进行语句解析。
  2. 接下来，MySQL解析器通过关键字将SQL语句进行解析，并生成一棵对应的解析树，解析器使用MySQl语法规则验证和解析SQL语句。例如，它将验证是否使用了错误的关键字，或者使用关键字的顺序是否正确，引号能否前后匹配等；
  3. 接下来，查询优化器将解析树转化成执行计划。MYSQL优化程序会对我们的语句做一些优化，如子查询转为连接、表达式简化等。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引执行查询，以及表之间的连接顺序是啥样，等等。我们可以使用explain语句来查看某个语句的执行计划。
  4. 最后，进入执行器阶段。完成查询优化后，查询执行引擎会按照生成的执行计划调用存储引擎提供的接口执行SQL查询并将结果返回给客户端。在MySQL8以下的版本，如果设置了查询缓存，这时会讲查询结果进行缓存，再返回给客户端。

image.png

词法分析器是最难写的部分

MySQl提示“不存在此列”是执行到哪个节点报出的

是在Parser：解析器 分析sql语法的时候检查的列

如果一个表创建了多个索引，在哪个阶段或模块进行的索引选择

在优化器阶段Optimizer：查询优化器

支持哪些存储引擎，默认使用哪个

支持InnoDB MyISAM...默认使用InnoDB

MySQL8.0带来哪些存储引擎 分别有什么作用

  • FEDERATED 跨库关联查询的时候用 也很少用

  • MEMORY Hash based，stored nin memory，useful for temporary tables 基本用不到 一般用redis

  • InnoDB 支持事务 支持回滚

  • MyISAM 不支持事务 不支持回滚

  • BLACKHOLE anything you write to it disappears存进去的数据有去无回 image.png 主备机集群的时候，主机可以只记录行为在binlog里面，具体的操作让slaves去做

  • CSV引擎

    • CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引
    • CSV引擎可以作为一种数据交换的机制，非常有用
    • CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取

MYSQL存储引擎的架构了解吗

image.png

能否为一张表设置存储引擎

可以的

  • 方法1 设置默认存储引擎

SET DEFAULT_STORAGE_ENGINE=MyISAM;

  • 方法2 或者修改my.cnf文件 ：vim/etc/my.cnf 新增一行：default-storage-engine=MyISAM 重启MySQL：systemctl restart mysqld

  • 方法3 我们可以为不同的表设置不同的存储引擎

CREATE TABLE 表名（建表语句；） ENGINE=存储引擎名称； ALTER TABLE 表名 EBGINE=存储引擎名称；

阿里、京东等大厂都有自研的存储引擎，如何让开发一套自己的？

开发一套存储引擎并不难，难的是开发出来高效的有意义的存储引擎。

MyISAM和InnoDB的区别是什么

外键 事务 锁 image.png 前三项是最重要的！

（跟上面）具体说一下技术选型：

除非几乎没有写操作，全部都是高频的读操作可以选择MyISAM作为表的存储引擎，其他业务一律使用InnoDB

是否使用过select for update

select本身是一个查询语句，查询语句是不会产生冲突的一个行为，一般情况下是没有锁的，用select for update会让select for update会让select语句产生一个排他锁，这个锁和update的效果一样，会使两个事务无法同时更新一条记录

  • for update只能适用于InnoDB，且必须在事务块（BEGIN/COMMIT）中才能生效
  • 在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包括行锁，表锁。
  • InnoDB默认是行级别的锁，在筛选条件中当有明确指定主键或唯一索引的时候，是行级锁。否则是表级锁。

MySQL死锁的原因和解决方案

死锁的要素：

  1. 两个或两个以上事务
  2. 每个事务都已经持有锁并且申请新的锁
  3. 锁资源同时只能被同一个事务持有或者不兼容
  4. 事务之间因为持有锁和申请锁导致彼此循环等待

show engine innodb status可以看到最近一次死锁的日志

解决：

  • 死锁无法避免，上线前要进行严格的压力测试
  • 快速失败
    • innodb_lock_wait_timeout 行锁超时时间
  • 拆分sql，严禁大事务
  • 充分利用索引，优化索引，尽量把有风险的事务sql使用上覆盖索引，优化where条件前缀匹配，以减少表锁
  • 无法避免时：
    • 操作多张表时，尽量以相同的顺序来访问避免形成等待环路
    • 单张表时先排序再操作
    • 使用排他锁 比如 for update（可能会影响性能）

说一下MVCC的内部实现细节(Multiversion Concurrency Control)

通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其他事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一个时刻看到的数据可能是不一样的。 解决的问题：

  1. 读写之间阻塞的问题 通过MVCC可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。

提高并发的演进思路 > 普通锁，只能串行执行 > 读写锁，可以实现读读并发 > 数据多版本并发控制，可以实现读写并发

  1. 降低了死锁的概率
  2. 因为InnoDB的MVCC采用了乐观锁的方式，读取数据时不需要加锁，对于写操作，也只锁定必要的行
  3. 解决一致性读的问题，一致性读也称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

快照读是一种一致性不加锁的读，时InnoDB并发如此之高的核心原因之一

这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据，要么是事务自身插入或者修改过的数据

  • 不加锁的简单SELECT都属于快照读，例如： SELECT * FROM t WHERE id=1;

  • 与快照读相对应的则是当前读，当前读就是读取最新数据，而不是历史版本的数据。加锁的SELECT就属于当前读，例如 SELECT * FROM T WHERE id=1 LOCK IN SHARE MODE; SEELCT * FROM T WHERE id=1 FOR UPDATE;

InnoDB的MVCC是如何工作的

  1. InnoDB是如何存储记录的多个版本的 事务版本号 每开启一个事务，我们都会从数据库中获得一个事务ID（也就是事务版本号），这个事务ID是自增长的，通过ID大小，我们就可以判断事务的时间顺序。

行记录的隐藏列 InnoDB的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段：

  • DB_ROW_ID:6-byte,隐藏的行ID，用来生成默认聚簇错音。如果我们创建数据表的时候没有指定聚簇索引，这时InnoDB就会用这个隐藏ID来创建聚簇索引。采用聚簇索引的方式可以提升数据的查找效率。
  • DB_TRX_ID:6-byte,操作这个数据的事务ID，也就是最后一个对该数据进行插入或者更新的事务ID。
  • DB_ROLL_PTR:7-byte，回滚指针，也就是指向这个记录的Udo Log信息 Undo Log InnoDB将行记录快照保存在了Undo Log里，我们可以在混滚段中找到它们，如下图所示： image.png 从图中能看到回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 ID。这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找。

在可重复度的隔离级别下，InnoDB的MVCC是如何工作的 ReadView 查询（SELECT） InnoDB会根据以下两个条件检查每行记录：

  1. 只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小雨或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前就已经存在的，要么是事务本身插入或者修改过的。
  2. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的记录，才能返回作为查询的结果

插入（INSERT） InnoDB为新插入的每一行保存当前系统版本号作为行版本号

删除（DELETE） InnoDB为删除的每一行保存当前系统版本号作为行删除标识。 删除在内部被视为更新，行中的一个特殊位会被设置为已删除

InnoDB使用回收过程来清理已删除的行，并将它们从数据页中移除。这通常是由InnoDB后台线程执行的操作，称为purge线程。当没有活动的事务需要查看已删除行的版本时，purge线程会清理并回收这些行。

更新（UPDATE） InnoDB为插入一条新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

所以InnoDB的更新实际上是插入一条新的数据，删除旧的数据。

MVCC在一定程度上实现了读写并发，它只在可重复读和提交读两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行。而可串行化则会对所有读取的行都加锁。

MySQL事务的一致性，原子性是如何实现的

首先是通过锁和mvcc实现了执行过程中的一致性和原子性 其次是在灾备方面通过Redo log实现，Redo log会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃以后可以把事务所做的任何修改都恢复出来

MySql事务的持久性是如何实现的

使用Redo log保证了事务的持久性。当事务提交时，必须先将事务的所有日志写入日志文件进行持久化，就是我们常说的WAL（write ahead log）机制，如果出现断电重启便可以从redo log中恢复，如果redo log写入失败，那么也就意味着修改失败，整个事务也就直接回滚了。

表级锁和行级锁有什么区别

  • 表级锁：串行化（serializable）时，整表加锁，事务访问表数据时需要申请锁，虽然可以分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做ddl处理时使用
  • 行级锁：除了串行化（serializable）时InnoDB使用的都是行级锁，只锁一行数据，其他数据不影响，并发能力强。

什么是行级锁？MySql如何完成的

行级锁实现比较复杂，不是单纯的锁住一行数据，是由mvcc完成的

什么是意向锁

它分为意向共享锁（IS）和意向排他锁（IX） 一个事务对一张表的某行添加共享锁之前，必须获得对该表一个IS锁或者优先级更高的锁。 一个事务对一张表的某行添加排他锁之前，它必须对该表获取一个IX锁 意向锁属于表锁，它不与innodb中的行锁冲突，任意两个意向锁之间也不会产生冲突，但是会与表锁（S锁和X锁）产生冲突

当前读和快照读是什么

当前读：在锁定读（使用锁隔离事务）的时候读到的是最新版本的数据 快照读：可重复读（repeatable-read）下mvcc生效读取的是数据的快照（在redo log里面），并不是罪行的数据（未提交事务的数据）

是否使用过select for update，会产生哪些操作

select本身是一个查询语句，查询语句是不会产生冲突的一种行为，一般情况下是没有锁的，用select for update会让语句产生一个排他锁(X)，这个锁和update的效果一样，会使两个事务无法同时更新一条记录

  • for update仅适用于InnoDB，且必须在事务块（BEGIN/COMMIT）中才能生效。

  • 在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁，表锁。

  • InnoDB默认是行级别的锁，在筛选条件中当有明确指定主键或唯一索引列的时候，是行级锁。否则是表级锁。

Mysql会产生几种日志

  • 错误日志（error log） error log主要记录MySQL在启动，关闭或者运行过程中的错误信息，在MySQL的配置文件my.cnf中，可以通过log-error=/var/log/mysqld.log执行mysql错误日志的位置
  • 一般性查询日志（general log） general log记录了客户端连接信息以及执行的SQL语句信息，通过MySQL的命令

bin log的作用是什么

存的是执行过的sql

  • 灾备，恢复
  • 主从同步

bin log里面的语句如果涉及到系统变量的话可能会有些不一样，比如当前时间

image.png

MySQl日志是否实时写入磁盘

磁盘写入固然是比较慢的 参数：sync_binlog binlog写入策略：

  1. sync_binlog=0的时候，表示每次提交事务binlog不会马上写入到磁盘，而是先写到page cache，相对于磁盘写入来说写page cache要快得多，不过在Mysql崩溃的时候有丢失日志的风险
  2. sync_binlog=1的时候，表示每次提交事务都会执行fsync写入到磁盘；
  3. sync_binlog的值大于1的时候，表示每次提交事务都先写到page cache，只有等到积累到了N个事务之后才fsync写入到磁盘，同样在此设置下Mysql崩溃的时候会有丢失N个事务日志的风险

很显然三种模式下，sync_binlog=1是强一致性的选择，选择0或N的情况下在极端情况下就会有丢失日志的风险，具体选择什么模式还是得看系统对于一致性要的要求

redo log是如何刷盘的

image.png 脏页：只change buffer中与磁盘中数据不一致的页 Innodb_flush_log_at_trx_commit

image.png

时机顺序：

image.png

MYSQL的binlog有几种录入格式？分别有什么区别

logbin格式：

  • binlog_format=STATEMENT(默认)：数据操作的时间，同步不一致 每一条会修改数据的sql语句会记录到binlog中。有点事并不需要记录每一条sql语句和每一行的数据变化，减少了binlog的日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致（如sleep()函数，last_insert_id(),以及user-defined functions(udf)等会出现问题）
  • binlog_format=ROW：批量数据操作时，效率低，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样子。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和处罚无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨
  • binlog_format=MIXED:时以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量

Mysql集群的时候为什么使用binlog？优缺点是什么？

  • binlog是mysql提供的日志，所有存储引擎都可以用
  • 支持增量同步

增量同步（Incremental Synchronization）是一种数据同步的方法，用于将数据从一个源系统同步到目标系统，但只同步源系统中自上次同步以来发生的更改数据，而不是将整个数据集复制到目标系统。这可以有效减少数据传输和处理的开销，提高同步效率，特别是在大型数据集的情况下

  • binlog还可以供其他中间件读取，比如同步到hdfs中
  • 如果复制表数据
    • 不支持某个阶段回放
    • 直接复制数据过程中一旦中断复制（比如断网），很难确定复制的offset

可以直接使用MySQL存储文件吗

可以使用BLOB（binary large object），用来存储二进制大对象的字段类型

  • TinyBlob 255字节的长度加上用于记录长度的1个字节（8位）
  • Blob 65k值的长度加上用于记录长度的2个字节（16位）
  • MediumBlob 16M值的长度加上用于记录长度的3个字节（24位）
  • LongBlob 4G值的长度加上用于记录长度的4个字节（32位）

什么时候存 什么时候不存

存：需要高效查询并且文件很小的时候 不存：文件比较大，数据量多或变更频繁的时候

存储的时候有遇到过什么问题吗

1.上传数据过大sql执行失败 调整max_allowed_packet 2. 主从同步数据时比较慢 3. 应用线程阻塞 4. 占用网络带宽

Emoji乱码怎么办

使用utf8mb4

MySQL在5.5.3之后增加了utf8mb4的编码，mb4就是most bytes 5的意思，专门用来兼容四字节的unicode，好在utf8mb4是utf8的超集，除了将编码改为utfmb4之外不需要做其他转换。当然，一般情况下使用utf8也就够了

如何存储ip地址

  1. 使用字符串
  2. 使用无符号整型

因为一个 int 型的数据占 4 个字节，每个字节 8 位，其范围就是 0~(2^8-1)，而 ipv4地址 可以分成4段，每段的范围是 0~255 刚刚好能存下，所以将其稍稍转换，就巧妙的将 IP地址 用最小的空间存在了数据库中（接下来的描述若无特殊说明，则都是指的 ipv4地址）。

  • 四个字节即可解决
  • 可以支持范围查询
  • INET_ATON()和INET_NTOA()ipv6使用INET6_ATON()和INET6_NTOA()

长文本类型如何存储

可以使用TEXT存储

TINYTEXT（255长度） TEXT（65535） MEDIDUMTEXT（int最大值16M） LONGTEXT（long最大值4G）

大段文本如何设计索引

  1. 或将大段文本同时存储到搜索引擎
  2. 分表存储
  3. 分表后多段存储

大段文本查找时如何建立索引

  1. 全文索引，模糊匹配最好存储到搜索引擎中
  2. 指定索引长度（只适合开头精确匹配，后面模糊匹配）
  3. 分段存储后创建索引

日期，时间如何存取

使用TIMESTAMP，DATETIME

TIMESTAMP和DATETIME的区别是什么

跨时区的业务使用TIMESTAMP，TIMESTAMP会有时区转换

  1. 两者的存储方式不一样
  • 对于TIMESTAMP，它把客户端的插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前失去进行返回
  • 而对于DATETIME，不做任何改变，基本上是原样输入和输出
  1. 存储字节大小不同
数据类型MYSQL 5.6.4之前需要存储MYSQL 5.6.4之后需要存储
DATETIME8 bytes5 bytes + 小数秒存储
TIMESTAMP4 bytes4 bytes + 小数秒存储

为什么不用字符串存储日期

字符串无法完成数据库内部的范围筛选 在大数据量优化索引时，查询必须加上时间范围

如果需要使用时间戳timestamp和int该如何选择

int 存储空间小，查询效率高，不受时区影响，精度低，需要注意时区转换，timestamp更易读，一般选择timestamp，两者性能差异不明显，本质上存储都是使用的int

char与varchar的区别？如何选择？

char是固定的长度 varchar是可变的长度

  1. char的优点是长度固定（最大255），没有碎片，尤其更新比较频繁的时候，方便数据文件指针的操作，所以存储读取速度快，缺点是空间冗余，对于数据量大的表，非固定字段长度属性使用char字段，空间浪费。
  2. varchar字段，存储空间根据存储的内容变化，空间长度为L+size，存储内容长度加描述存储内容长度信息，优点是空间节约，缺点是读取和存储的时候，需要读取信息计算下标，才能获得完整信息

财务计算有没有出现错乱

第一类： 锁包含多线程，数据库，UI展示超时提交等 第二类：应用与数据库浮点运算精度丢失 1. 应用开发问题：多线程共享数据读写 2. 之前有丢失精度问题，使用decimal解决 3. 使用乘法替换除法 4. 使用事务保证acid特性 5. 更新时使用悲观锁SELECT ... FOR UPDATE 6. 数据只有标记删除 7. 记录详细日志方便溯源

decimal与float，double的区别是什么

float：浮点型，4字节，32bit double：双精度实型，8字节，64位 decimal：数字型，128bit，不存在精度损失 对于声明语法DECIMAL（M，D），自变量的值范围如下：

  • M是最大的数（精度），范围是1到65，可不指定，默认是10
  • D是小数点右边的位数（小数位），范围是0到30，并且不能大于M，可不指定，默认值是0

例如字段salary DECIMAL(5,2),能够存储具有5位数字和两位小数的任何值，因此可以存储在salary中的范围是-999.99到999.99

浮点数如何选型？为什么

  • 需要不丢失精度的计算使用DECIMAL
  • 仅用于展示没有计算的小数存储可以使用字符串存书
  • 低价值数据允许计算后丢失精度可以使用float double
  • 整型记录不会出现小数的不要使用浮点类型

预编译sql是什么

通过mysql服务器，可以通过占位符的方式先把sql进行一次编译 sql语句复用 每次预编译的sql会被mysql缓存下来

  • 预编译会被mysql缓存下来

  • 作用域是每个session，对其他session无效，重新连接也会失效

  • 提高安全性防止sql注入

    • select * from user where id=?;
    • select * from user where id=1;delete from user where id =1"; sql注入
    • 如果是占位符 编译了"select * from user where id=" 只会认为“1;delete from user where id=1”;是一个字符串

  • 编译语句可能被重复调用，也就是说sql相同，参数不同在同一session中重复查询执行效率明显比较高

  • mysql 5，8 支持服务器端的预编译

子查询和join哪个效率高

子查询虽然很灵活，但是执行效率不高

为什么自查询效率低

在进行子查询的时候，MYSQL创建了临时表，查询完毕之后再删除这些临时表 子查询的速度慢的原因是多了一个创建和销毁临时表的过程 而join则不需要创建临时表所以会比子查询快一点

join可以无限叠加吗

建议join不超过三张表关联，mysql对内存敏感，关联过多会占用更多内存空间，使性能下降

产生太多临时数据

Too many tables;MySql can only use 61 tables in a join; 系统限制最多关联61个表

join查询算法了解过吗

image.png

SNLJ：拿一张表符合条件的数据去一个一个匹配另一张表 INLJ：join的字段加了索引，会通过索引树来做匹配 拿到主键再去找具体数据

如何优化过多join查询关联

  • 适当增加冗余字段减少多表关联查询
  • 驱动表和被驱动表（小表join大表）
  • 业务允许的话 尽量使用inner join让系统帮忙自动选择驱动表
  • 关联字段一定创建索引
  • 调整JOIN BUFFER大小

是否有过mysql调优经验

调优：

  1. sql调优
  2. 表（结构）设计调优
  3. 索引调优
  4. 慢查询调优
  5. 操作系统调优
  6. 数据库参数调优

开发中使用过哪些调优工具

官方自带

  • EXPLAIN image.png
  • mysqldumpslow 分析系统中慢查询的sql
  • show profiles
  • optimizer_trace 追踪优化器的选择 (cost)

第三方：性能诊断工具，参数扫描提供建议，参数辅助优化

如何监控线上环境执行比较慢的sql

开启慢查询 查看慢查询日志

如何查看当前sql使用了哪个索引

type: 查找方式 key：使用的索引 filtered：有效率的百分比 image.png EXPLAIN关键字，选择索引的过程可以使用optimizer_trace

select * from information_schema.OPTIMIZER_TRACE

详解EXPLAIN关键字

explain的作用：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

属性详解：

image.png 1. id

id常见的三种情况

  1. id相同，执行顺序从上到下

image.png

  1. id不同，如子查询，id的序号会递增，id的值越大优先级越高，越先被执行

image.png

  1. id相同不同都有，id值越大越先被执行，相同从上到下顺序执行 image.png

image.png

2. select type

含义：表示该条SQL查询的类型，如子查询、联合查询等，常见值如下：

  • simple
    • 简单的select查询，查询中不包含子查询或union
  • primary
    • 查询中若包含任何的子查询，最外层的查询被标记为primary
  • subsequence
    • 在select或where中包含子查询
  • derived
    • 在from后包含的子查询被标注为derived（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
  • union
    • 若第二个select出现在union之后，则被标记为union，若union包含在from后面的子查询中，外层的select将被标记为：DERIVED
  • union result
    • 从union表获取结果的select
  • update 更新
  • insert 插入
  • delete 删除

3. table

含义：表示用到了几张表，若出现了derived的情况，则表示产生了中间表，常见产生中间表的情况有子查询和联合查询等

4. type type显示的是访问类型，较为重要，结果值从好到坏依次为：simple>const>eq_ref>ref>range>index>all，一般来说最好保证达到range级别，最好达到ref

  1. system：这是最高级别的访问类型，通常用于只有一行的表。它表示MySQL将从系统表中获取一行数据。
  2. const：这是最有效的访问类型，通常用于常量条件下的等值查询。它表示MySQL将只从表中读取一次数据，因为查询条件是一个常量，对于给定的查询，只有一行数据匹配。
  3. eq_ref：这是连接查询中最有效的访问类型，通常用于唯一索引或主键等值查询。它表示MySQL将使用一个索引来查找表中的一行数据。
  4. ref：这是用于非唯一索引等值查询的访问类型。它表示MySQL将使用一个索引来查找多行数据，但不是所有行都匹配查询条件。
  5. range：这是用于范围查询的访问类型。它表示MySQL将使用索引来查找满足条件的多行数据范围。
  6. index：这是使用索引扫描全表的访问类型，通常用于覆盖索引查询。它表示MySQL将扫描整个索引，而不是整个表，以查找匹配的数据。
  7. all：这是最低效的访问类型，表示MySQL将扫描整个表来查找匹配的数据，而不使用索引。

5. possible_keys

显示可能应用在这张表中的索引，一个或多个，但不一定实际用到，实际用到的在key上显示

6. key

现实实际用到的索引，每张表的一次查询只会用到一个索引

7. key_len

显示索引字段中使用的最大可能的字节数，并非实际的长度，可通过该参数计算查询中使用的索引长度，在不损失精度的情况下，长度越短越好

8. ref

显示哪些索引列或者常量被引用了，优先const

9. rows

根据表统计信息及索引选用情况，大致估算出所优化的行数，越少越好

10. extra 包含不适合在其他列显示但又十分重要的额外信息

MySQL数据库cpu飙升的话如何做

重点是定位问题

1 使用top观察mysqld的cpu利用率

  1. 切换到常用的数据库
  2. 使用show full processlist；查看会话
  3. 观察是哪些sql消耗掉了资源，其中重点观察state指标
  4. 定位到具体sql

2 pidstat

  1. 定位到线程
  2. 在PERPORMANCE_SCHEMA.THREADS中记录了thread_os_id找到线程执行的sql
  3. 根据操作系统id可以到processlist表找到对应的会话
  4. 在会话中即可定位到问题sql

3 使用show profile观察各个阶段耗时 4 服务器上是否运行了其他程序 5 检查一下是否有慢查询

有没有进行过分库分表

什么是分库分表

垂直分库

一个数据库由很多表组成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：

image.png

水平分表

把一张表里的内容按照不同的规则，写到不同的库里 相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库中，每个表中包含一部分数据，简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行分到一个数据库，而另外的某些行又切分到其他的数据库中，如图：

image.png

什么时候进行分库分表？有没有配合ES使用经验

  1. 能不分就不分
  2. 单机性能下降明显的时候
  3. 增加缓存（通常查询量比较大），细分业务
  4. 首先尝试主备集群，读写分离
  5. 尝试分库
  6. 尝试分表

大数据量下可以配合es完成高效查询

分库分表实现思路

  1. 伪装成mysql服务器，代理用户转发到真实的服务器
  2. 基于本地aop实现，拦截sql，改写，路由和结果归集处理

分库分表可能会有哪些问题

经典的问题：

  1. 执行效率明显降低
  2. 表结构很难再次调整
  3. 引发分布式id问题
  4. 产生跨库join
  5. 代理类中间件网络io成为瓶颈

读写分离常见方案

image.png

image.png

为什么要使用视图，什么是视图

视图定义：

  1. 视图是一个虚表，是从一个或多个基本表（或视图）导出的表
  2. 只存放视图的定义，不存放视图对应的数据
  3. 基表中的数据发生变化，从视图中查询出来的数据也随之发生变化

视图的作用：

  1. 简化用户的操作
  2. 视图使用户能够以多种角度看待同一数据
  3. 视图对重构数据库提供了一定程度的逻辑独立性
  4. 视图能够对机密数据提供安全保护
  5. 适当的利用视图可以更清晰的表达查询

有没有使用过外键？有什么需要注意的地方

不得使用外键，一切外键概念必须在应用层解决

某个表有千万数据，查询比较慢，如何优化？说一下思路

  1. 前端优化
    1. 合并请求：多个请求需要的数据尽量一条sql拿出来
    2. 会话保存：和用户绘画相关的数据尽量一次取出重复使用
    3. 避免无效刷新
  2. 多级缓存 不要触及到数据库
    1. 应用层热点数据高速查询缓存（低一致性缓存）
    2. 高频查询大数据量镜像缓存（双写高一致性缓存）（双写：在写入mysql的时候同时更新redis）
    3. 入口层缓存（几乎不变的系统变量）
  3. 使用合适的字段类型，比如varchar换成char
  4. 一定要高效使用索引
    1. 使用explain深入观察索引使用情况
    2. 使用的select字段最好满足索引覆盖
    3. 复合索引注意观察key_len索引使用情况
    4. 有分组，排序，注意file sort，合理配置响应的buffer
  5. 检查查询是否可以分段查询，避免一次拿出过多无效数据
  6. 多表关联查询是否可以设置冗余字段，是否可以简化多表查询或分批查询
  7. 分而治之：把服务拆分成更小粒度的微服务
  8. 冷热数据分库存储
  9. 读写分离，主备集群 然后考虑分库分表

count(列名)和count(*)有什么区别

count(*)会统计表中所有的字段数，包括值为NULL的行，而count(列名)统计改字段在表中出现的次数，忽略值为NULL的行

count(1)和count(*)差不多

如果有超大分页该怎么处理？

延迟关联

说明： MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就特别低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：

先快速定位需要获取的id段，然后再关联：SELECT a.* FROM 表1 a,(select id from 表1 where 条件 LIMIT 100000,20) b where a.id=b.id;

快速定位到id在哪儿，可以利用聚簇索引

需要order by时： - 一定要注意增加筛选条件，避免全表排序 - where->order by->limit - 减少select字段（没有用的 用不到的） - 优化相关参数避免filesort（在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。）

什么是mysql多实例部署？

  • 指的是在一台主机上部署多个实例

  • 主要目的是压榨服务器性能

  • 缺点是相互影响

avatar
文章
阅读
粉丝