mysql面试八股

Mysql相关

能说下myisam 和 innodb的区别吗？

• myisam是5.1版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用
• innodb是基于聚簇索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。

说下mysql的索引有哪些吧，聚簇和非聚簇索引又是什么？

按数据结构：b+树和hash

b+树和hash的区别

• b+树支持范围查找，核心是类AVL二分查找树
• hash快，等值查找，然后热点数据均匀分布

按物理存储：

聚簇和非聚簇索引：

聚簇索引：主键和数据的位置对应，找到了索引就找到了需要的数据

非聚簇索引：索引的存储和数据的存储是分离的，也就是说如果找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询

按字段特性：

• 主键索引
• 唯一索引
• 普通索引
• 前缀索引 以前缀字符或者比特简历的索引 char varchar等

按列数

• 单列索引
• 多列索引

1.存储引擎

1.1 MyISAM

特点

不支持事务，不支持外键，Mysql5.1以前的默认存储引擎。

相比InnoDB，数据可压缩，内存使用率低，速度快。

文件存储

• .frm 存储表的定义
• .MYD 存储数据
• .MYI 存储索引

这样带来的好处就是可以将三个文件放在不同目录下以平均性能，但是也增加了出错误的几率。

三种存储模式

• 静态表
  • 默认存储模式，所有字段都是定长的，速度快，容易缓存，容易定位故障，
  • 缺点是占用空间大，另外在存储字符串的时候，末尾的空格无法存储
• 动态表
  • 占用空间小，可以存储变长字段
  • 但是频繁删更新和删除会导致碎片，需要定期碎片整理
• 压缩表
  • 占用空间极小，但是可塑性不强

1.2 INNODB

特点

• 增加了表字段的约束

  • AutoIncreasment 自动增长，会自动创建索引，如果是联合索引，该列会是联合索引的第一项

  • 外键约束

• 支持事务，使用行锁，支持集群

• 空间使用高，内存高，批量插入更新速度慢

• 独立表空间，支持MVCC，预读

1.2.1 后台线程

InnoDB后台有多个不同的线程，用来负责不同的任务。主要有如下：

• Master Thread
  这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性.
• IO Thread
  在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求, IO Thread 的工作主要是负责这些 IO 请求的回调处理。
• Purge Thread
  事务被提交之后, undo log 可能不再需要,因此需要 Purge Thread 来回收已经使用并分配的 undo页. InnoDB 支持多个 Purge Thread, 这样做可以加快 undo 页的回收。
• Page Cleaner Thread
  Page Cleaner Thread 是在InnoDB 1.2.x版本新引入的,其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成,这样减轻了 Master Thread 的工作及对于用户查询线程的阻塞。

1.2.2 内存

InnoDB存储引擎是基于磁盘存储的也就是说数据都是存储在磁盘上的,由于CPU速度和磁盘速度之间的鸿沟InnoDB引擎使用缓冲池技术来提高数据库的整体性能。

• 在数据库中进行读取页的操作,首先将从磁盘读到的页存放在缓冲池中,下一次读取相同的页时,首先判断该页是不是在缓冲池中,若在,称该页在缓冲池中被命中,直接读取该页。否则,读取磁盘上的页。

• 对于数据库中页的修改操作,首先修改在缓冲池中页,然后再以一定的频率刷新到磁盘,并不是每次页发生改变就刷新回磁盘，redo log。

1.2.3三个链表

内存淘汰策略

• LRU和free list机制

free list

以链表为数据结构，每个Free链表节点都对应一个空闲的缓存页

• 每当需要从磁盘中加载一个页到Buffer Pool中时，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free链表节点从链表中移除，表示该缓存页已经被使用了

LRU优化

将链表一分为二，加入了midpoint，每次插入到链表的中间，中间的左边就是new列表，右边就是old列表，减少了全盘扫描的次数

肮页链表 FLUSH链表

这个链表只保存脏页在内存中的位置，当数据第一次被修改的时候加入flush链表，后续修改就不加入

1.2.4 插入缓冲 ——对于非聚簇索引的数据的插入和更新操作 ——提高写性能

如果索引是非聚集的且不唯一。在进行插入操作时，数据的存放对于非聚集索引叶子节点的插入不是顺序的，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。这是因为B+树的特性决定了非聚集索引插入的离散性。

Insert Buffer的设计，对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入非聚集索引页是否在缓冲池中，若存在，则直接插入，不存在，则先放入一个Insert Buffer对象中。

数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

1.2.5 两次写 ——提高可靠性

当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，在部分写失效的情况下，我们在应用重做日志之前，需要原始页的一个副本，两次写就是为了解决这个问题。

其原理是这样的：
1）当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓冲区。
2）接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写入1MB
3）待第2步完成后，再将两次写缓冲区写入数据文件

第2步是额外的性能开销，但由于磁盘共享表空间是连续的，因此开销不是很大。

1.2.6 文件

普通日志：

• 错误日志
• 慢查询日志
• 查询日志
• socket日志
• 二进制日志

重做日志文件

Undo日志文件

表结构定义文件 .frm

表空间文件

InnoDB将所有数据（表数据，索引，插入缓冲索引页，回滚信息，插入缓冲索引页，系统事务信息，二次写缓冲等等）逻辑地放在一个空间中，称为共享表空间。

1.3 MEMORY

使用内存保存数据，使用hash索引，速度非常快，但是一旦服务关闭数据丢失

1.4 Merge

多个MyISAM表的组合

1.5存储引擎的选择

• MYISAM：如果是以读操作和插入为主，更新和删除操作较少，对事务的完整性、并发现要求性并不高的情况下使用
• INNODB：事务处理应用程序，对于可靠性有较高要求，高并发和事务的场景，更新删除操作较为频繁的场景

2. 索引

2.1索引类型

主键索引、普通索引、唯一索引、全文索引、聚合索引（多列索引

2.2 高效使用索引

• 首先应考虑在where及order by，group by涉及的列上建立索引。
• 对于普通索引，尽量考虑列中值的分布越大的
• 对字符索引使用前缀索引
• 利用最左前缀
• 不要过度使用索引，会降低写性能

3.存储过程

常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。

特点：

• 函数式编程，灵活
• 可减少网络流量
• 高性能
• 高数据库的安全性和数据的完整性

4. 分区

4.1分区类型

• RANGE 适用于删除过期数据，经常运行有关分区键的查询
• LIST 枚举
• HASH 平均性能，但不适用于增改分区的情况
• KEY

5. SQL调优

5.1 分析原因

• show status 查看服务器状态信息，包括增删改查次数、行数等
• 定位慢sql语句——慢查询日志
• explain关键字分析sql
  • 分析查询方式 simple(简单查询，不用表连接)、主查询、UNION、子查询等
  • 找到所需行的方式，包括all、index、range、ref、NULL等
  • 找到所需要的扫描行数
  • 使用的索引
• show profile
  • 可以查询的每个步骤，从选择数据库、选择表、到具体的sql执行的时间
  • 可以查看线程每个状态消耗的时间
  • 可以查看在cpu、块io、页面切换等消耗的时间

5.2 索引

使用索引的场景：

• 等值匹配
• 范围匹配
• 最左前缀匹配
  • 例如col1+col2+col3的联合索引，能够被col1,col1+col2,col1+col2+col3利用，但是不能被col2+col3利用

不能使用索引的场景：

• 列与列对比
• NULL值匹配
• NOT、IN、LIKE等
• 不等于 <>
• 条件上包括函数

select * from test where upper(name)='SUNYANG';

• 数据类型的转换

select * from sunyang where id='123';

索引优化：

• 根据sql新增或删除索引
• 优化索引顺序、优化sql

5.3 数据插入

批量数据插入：

• 顺序排序好
• 关闭唯一性校验
• 关闭自动提交

多个insert合并

insert into test values(1,2),(2,3),(3,4)...

5.4 sql语句优化

Order by

排序方式：

• 通过索引默认顺序
• Filesort

尽量减少额外的排序，通过索引直接返回排序结果

优化嵌套

子查询更耗时，使用join代替

OR

使用or条件时，所有条件都需要索引

LIMIT优化

尽可能少使用全表扫描

比如在查询

select * from test order by create_at limit 100,20

可以先找到第100条的数据

select * from test where create_at>'1970-01-01' limit 20

5.5 优化表的数据类型

• 枚举类型，比如性别、职业等
• varchar和char
• date、time、timestamp

5.6 分表

横向分表、纵向分表

5.7 中间表

6.优化SQL Server

6.1 MyISAM优化

myisam对索引进行了缓存，对数据并没有进行缓存。

• 设置key_buffer_size的大小 一般不低于内存的1/4
• 调整中点插入策略
• read_buffer_size

6.2 INNODB优化

与MYISAM不同，innodb对数据也做了缓存，也就是表空间

• 调整innodb_buffer_pool_size大小
• 调整中点插入策略
• 调整old_blocks_time 旧数据到新数据列表的快慢
• 控制buffer刷新率
• 设置log策略

6.3 高并发场景

• max_connection 连接最大数量
• back_log TCP请求栈空间大小
• 行锁超时时间调大

Mysql实现事务

• 事务的原子性是通过 undo log 来实现的
• 事务的持久性性是通过 redo log 来实现的
• 事务的隔离性是通过 (读写锁+MVCC)来实现的
• 而事务的终极大 boss 一致性是通过原子性，持久性，隔离性来实现的！！！

事务ACID和隔离级别

事务基本特性ACID分别是：

• 原子性指的是一个事务中的操作要么全部成功，要么全部失败。

• 一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。

• 隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。

• 持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

出现的问题：

• 脏读：指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚
• 不可重复读：不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据**更新（UPDATE）**操作。
• 幻读：假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，就好像修改无效一样

4个隔离级别

• 读未提交（READ UNCOMMITTED）
• 读提交 （READ COMMITTED）
• 可重复读 （REPEATABLE READ）
• 串行化 （SERIALIZABLE）

redo log 重做日志

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。

作用：redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。

执行过程如下所示。

undo log 回滚日志

每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log。

mysql锁技术以及MVCC基础

读写锁

解决上述问题很简单，只需用两种锁的组合来对读写请求进行控制即可，这两种锁被称为：

共享锁(shared lock),又叫做"读锁"

读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞。

排他锁(exclusive lock),又叫做"写锁"

写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁。

MVCC基础

MVCC (MultiVersion Concurrency Control) 叫做多版本并发控制。

MVCC在mysql中的实现依赖的是undo log与read view

• undo log :undo log 中记录某行数据的多个版本的数据。
• read view :用来判断当前版本数据的可见性