MySQL面试100问

面试官：“了解MySQL吗？”

要是说不了解，接下来该聊些啥呀？

面试官：“了解的话，那说说XXX吧。”

巴拉巴拉…

MySQL有哪些常见的引擎，说说他们的区别？

show engines; #查看支持的引擎

• MyISAM：非事务处理存储引擎

  索引放在XX.MYI文件中，数据放在XX.MYD文件中。

• InnoDB：具备外键功能的存储引擎

  索引也是数据。数据和索引存在一个XX.IDB文件中。

• Mermory：基于内存

  Memory表至少比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O。Memory表的结构在重启以后还会保留，但数据会丢失。

• Archive：数据存档，数据插入后不能修改

  针对高写入压缩做了优化的简单引擎。

  特点	InnoDB	MyISAM	Memory	Archive
  b+树索引	yes	yes	yes	no
  MVCC	yes	no	no	no
  事务	yes	no	no	no
  存储限制	64TB	256TB	RAM	None
  索引缓存	yes	yes	本身就是缓存	No
  外键	yes	no	no	no
  锁粒度	row	table	table	row
  全文索引	yes	yes	no	no
  数据缓存	yes	no	本身就是缓存	no

有哪些索引和他们之间的区别？

• Hash索引

  优点：

  1. Hash索引的检索可以一次到位，所以Hash索引的查询效率更高。

  缺点：

  1. Hash索引只能满足"="，“IN”，"!="，不能使用范围查询。
  2. Hash值的大小关系不一定个原键值一样，不能做排序操作。
  3. 联合索引中不能利用部分索引键查询。
  4. 遇到大量Hash值相等的情况后，性能不一定比B树高。
  5. 存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询。

  InnoDB引擎有一个特殊功能叫“自适应哈希索引”。当InnoDB发现某些索引值被使用的非常频繁是，会在内存中基于B-Tree索引之上再建一个哈希索引，这样可以让B-Tree索引具有哈希索引的优点。这是一个完全自动的、内部的行为，用户无法控制或配置（如果有必要，可以关闭该功能）。

• B+树索引

  优点：

  1. 所有索引数据都在叶子结点上，并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针。

  2. 利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

  3. B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。

     假设叶子节点页可以存放100条记录，内部节点可以存放1000条目录，则

     h=1，最多能放100条记录。

     h=2，最多能放1000*100=100000条记录。

     h=3，最多能放1000*1000*100=100000000（一亿）条记录。

     h=4，最多能放1000*1000*1000*100=100000000000（一千亿）条记录，应该不会有这种数据量的表吧！

     那为什么是O(logdN)呢？因为在页内查找一条记录时，使用的是二分查找。

• 全文索引

  1. 5.6版本之后InnoDB存储引擎开始支持全文索引，5.7版本之后通过使用ngram插件开始支持中文。
  2. 底层实现为倒排索引。

---

• 聚集索引（聚簇索引）

  1. 表数据按照索引的顺序存储，也就是说索引项的顺序和表中记录的物理顺序一致。

  2. 聚簇索引叶子节点存储了真实的数据行，不在有单独的数据页。

  3. 一张表只能创建一个聚簇索引，因为物理存储顺序只能有一种。聚簇集指的是数据行和相关的键值都保存在一起。

  4. 如果主键不是自增ID，MySQL会不断的调整数据的物理地址、分页分裂、页合并。如果自增，只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率高。

  5. 聚簇索引不是人为创建的，默认就有。

     

• 非聚集索引（二级索引）

  1. 表数据存储顺序与索引顺序无关。叶子节点包含索引字段值及指向数据页数据行的逻辑指针（行的主键值）。

  2. 适当建立联合索引（索引列包含全部查找列）能达到不用回表即可获取数据的目的。

  3. 为了保证B+树的同一层内节点的目录项记录除页号字段外是唯一的，内节点需要增加主键字段，即二级索引内节点由三部分组成：

     索引列的值

     主键值

     页号（页指针）

     

  ---

• 主键索引

  1. 每张表都会有主键，也会用主键生成主键索引。
  2. 主键索引就是聚簇索引，因此建表时最好用自增类型主键。
  3. 如果没有设置主键会发生什么？建表时如果没有设置主键，MySQL会启用内部的隐藏主键row_id（6个字节），也就是说无论如何一张表都必须有主键。MySQL会依次查找主键->唯一键->隐藏主键建立索引。

  alter table table_name add primary key (column name);
  

• 普通索引，联合索引

  create index 索引名 on table_name(column1,column2);
  alter table table_name add index 索引名(column1,column2);
  

• 唯一索引

  1. 唯一索引可以有多个null（不提倡有null值）,但不能有重复的内容。
  2. 相比主键索引，主键字段不能为null，也不能重复。

  create unique index index_name on table_name (column_list);
  

为什么要使用索引？

1. 减少存储引擎需要扫描的数据量，加快查询速度。
2. 索引可以把随机I/O变为顺序I/O。
3. 对索引结果进行排序以避免使用磁盘临时表。

建索引、使用索引要注意什么？

1. 数据类型越小越好。数据类型越小，在磁盘、内存和CPU缓存中需要的空间更少，处理速度更快。
2. 数据类型越简单越好。整形优于字符串，内置日期和时间优于字符串。
3. 尽量避免NULL。
4. 复合索引将最常用作限制条件的列放在最左边，依次递减。
5. 复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
6. 短索引。对串列进行索引，如果可能应该指定一个前缀长度，不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
7. mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
8. 建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量redo，从而提高执行的速度。
9. 建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。
10. 使用NOT IN和!=操作，mysql将无法使用索引。
11. 不要在列上进行运算，where字句的查询条件里使用了函数将不会使用索引。

B+树有什么特点？为什么B+树更适合做索引？

回答这个问题，心里一定要有点B树🐶。

1. 二叉查找树：

   • 左子树不为空，则左子树节点值均小于根节点。

   • 右子树不为空，则右子树节点值均大于根节点。

   • 任意节点的左右子树也是二叉查找树。

   • 没有键值相同的节点。

   • 有可能会退化成一条线性链。

2. AVL树：

   • AVL树是带有平衡条件的二叉查找树，一般用平衡因子差值判断是否平衡并通过旋转实现平衡。

   • 左右子树高度差不超过1。

   • 与红黑树相比，它是严格的平衡二叉树。

   • AVL树适合于插入删除比较少，但查找多的情况。

3. 红黑树：

   • 一种二叉查找树，每个节点增加一个存储位标识颜色。
   • 通过对任何一条从根节点到叶子节点的路径上各个节点着色方式的限制，确保没有一条路径会比其他路径长出两倍。
   • 是一种弱平衡二叉树，相对AVL树，它的旋转次数少，适用于搜索，插入，删除操作多的情况。

4. B树（b-树）

   • 为了磁盘或其它存储设备而设计的一种平衡多路查找树。
   • 与二叉树相比，每个节点有多个分支。在相同节点的情况下，高度远远低于红黑树。
   • B/B+树操作时间由磁盘存取和CPU两部分构成，CPU速度非常快，多以B树的效率取决于磁盘访问次数。关键字总数相同的情况下B树的高度越小，磁盘I/O所花时间越少。
   • 所有叶子节点位于同一层。

5. B+树

   • B树的变形树，非叶子节点只保存索引，不保存实际的数据，数据都保存在叶子节点中，且有序。
   • B树不允许关键字重复,B+树允许重复。
   • 所有叶子节点都有指向下一个叶子节点的指针。

6. 那么为什么是B+树？

   • 磁盘读写代价更低：内部节点只有指向下层节点的页指针（6个字节），因此内部节点相对B树更小，一页空间能容纳的关键字数量更多，I/O次数也就更少。

   • 查询效率更稳定：任何关键字的查找必须走一条从根结点到叶子结点的路，所以每一个数据的查询效率相当。

   • 利于数据库的扫描：B+树只需要遍历叶子节点就可以实现对全部关键字的扫描，更有利于范围查询。B树如果需要扫描，需要进行一次中序遍历，效率太低。

7. InnoDB的B+树索引

   • 每当某个表创建B+树索引的时候，都会为这个索引创建一个根节点页面。开始没有数据的时候，根节点中既没有用户记录，也没有目录项记录。
   • 根节点中可用空间用完后再继续插入记录，会将根节点中的记录复制到一个新分配的页总，然后对新页进行页分裂。新插入的记录根据索引值大小分配到分裂的页中，根节点升级为存储目录项记录的页。
   • B+树索引的根节点从创建开始，便不会再移动。InnoDB用到这个索引的时候，会从固定的地方取出根节点的页号，从而访问这个索引。

说说什么是最左匹配，对组合索引的理解

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。

• 建立联合索引时，从左到右依次判断，如果左边列相同，则根据第二列的大小排列，多列以此类推。

• 想要找”二班的小Q“，因为不知道年级，所以需要遍历所有数据。

• 年级>=三 and 年级<=四 and 名字=小Q，因为不知道是几班的，所以要把三、四年级都遍历一遍，但是也用到了年级这个索引列，不用找其他年级。

• 如果期望order by的子句使用索引，后面的顺序也必须按照索引列的顺序给出。如：按照年级、班级、名字顺次取10位同学。

  

有没有遇到过慢查询，如何优化？

要是说没遇到过，面试官也不会以为你SQL学的好，他一定认为”渣渣，连慢查询都没写过🐶🐶🐶“。

怎么更容易的写出慢查询？

• 建表随便一点，能达到事半功倍的效果。
• 建表不建索引，或者建了索引但是没有用上。
• 隐式类型转换。
• select * where xxx=xxx 不管是啥，先查出来再说。
• select xxx limit 100000 符合条件的数据有多少，全部拿出来，用不用再说。
• join 各种表，连接一万遍。
• 为了展现强大的SQL水平，一条SQL要写一页，谁都别想看懂。
• 花样 like。
• 可能还有更高深的方法。。。

优化

要想优化，得先定位问题，explain或desc查看SQL是怎么执行的。👀👀👀explain用法和结果的含义

• 索引

  1. 合理利用覆盖索引。
  2. 索引中的字段数建议不超过5个。
  3. 单张表的索引数量控制在5个以内。
  4. 对长度过长的VARCHAR字段建立索引时，添加crc32或者MD5 Hash字段，对Hash字段建立索引。
  5. 短索引。

• SQL

  1. SQL语句中IN包含的值不应过多，否则即使有索引还是可能扫全表。
  2. 如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引（不建议）。
  3. WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化。
  4. SELECT语句只获取需要的字段。
  5. 避免在SQL语句进行数学运算或者函数运算，否则不走索引。
  6. 避免使用JOIN。
  7. 统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。但是COUNT(*)也可以用Redis等其他方式代替。
  8. WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。
  9. 复杂SQL拆分。
  10. 使用合理的分页方式以提高分页的效率。

• 建表

  1. 每一列都是not null：如果某一列可能存储null值，那么mysql需要额外维护一个null值列表，存储上，查询上都增加了成本。

  2. 能用固定长度数据类型解决的，不用变长类型。

• 表维护

  定期将历史数据存档，表内数据维持在一定数量范围。

  使用hash等方式分表。

• 碎片整理

  碎片整理遇到的那些事

Mysql有哪几种事物隔离级别及区别？

• 事务基本要素

  1. 原子性Atomicity
  2. 一致性Consistency
  3. 隔离性Isolation
  4. 持久性Durability

• 事务隔离级别

  默认隔离级别：repeatable-read 可重复读。

  事务隔离级别为串行化时，读写数据都会锁住整张表。

  事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。

  事务隔离级别为读提交时，写数据只会锁住相应的行。

  隔离级别	脏读	不可重复读	幻读
  读未提交read-uncommitted	是	是	是
  读已提交read-committed	否	是	是
  可重复读repeatable-read	否	否	是
  串行化serializable	否	否	否

• 不可重复读和幻读

  不可重复读重点在于update

  幻读的重点在于insert

Mysql中的锁

• 锁结构

• MySQL中的行锁和表锁

  1. 行锁是InnoDB引擎才有的。MyISAM、Memory、Merge等引擎都只支持表锁，并且不支持事务。
  2. 表锁实现简单，占用资源少，但是粒度大，性能较差。
  3. 对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，反之也会发生阻塞。这个过程通过在server层使用元数据锁（MDL）实现。

• 一致性读

  事务利用MVCC进行读取操作，称为一致性读、快照读或一致性无锁读。一致性读不会对表中记录进行加锁，其他事务可以自由对表中记录进行改动。

• 行级锁

  1. 共享锁

     Share Locks，简称S锁。事务在读取一条记录时，需要先获取S锁。

     select ... lock in share mode;
     

  2. 独占锁

     也叫排它锁，Exclusive Locks，简称X锁。事务要改动一条记录时，需要先获取该记录的X锁。

     select ... for update;
     

  3. 行级锁兼容性

     兼容性	X	S
     X	No	No
     S	No	Yes

• 表级锁

  1. 表级共享锁

  2. 表级独占锁

  3. 意向共享锁（IS锁）

     给表上锁前需要知道表中有没有行锁，遍历又不是MySQL的风格，于是提出了意向锁。也就是在给某条记录加S锁时，需要先加一个表级别的IX锁。

  4. 意向独占锁（IX锁)

     同理

  5. 表级锁兼容性

     意向锁只是为了快速判断，意向锁之间都是兼容的。

     兼容性	X	S	IX	IS
     X	No	No	No	No
     S	No	Yes	No	Yes
     IX	No	No	Yes	Yes
     IS	No	Yes	Yes	Yes

• 表级别AUTO-INC锁

  主键通常添加AUTO_INCREMENT属性，系统实现自增的原理有两个：

  1. 执行插入操作时，在表级别加一个AUTO-INC锁，然后为每条记录分配递增值，该语句结束后再释放AUTO-INC锁，这样可以保证递增值是连续的。

     AUTO_INC锁的作用范围只是insert语句，insert语句执行完就被释放了，不用等到事务结束。

  2. 采用轻量级的锁，在为insert语句生成递增值的时候获取一下轻量级锁，获取到后即可释放，不用等insert语句执行完毕再释放。

  3. InnoDB提供一个innodb_autoinc_lock_mode系统变量，值为0时，一律采用AUTO-INC锁；值为2时，一律采用轻量级锁（不安全）；值为1时，在插入记录数量确定时采用轻量级锁，不确定时使用AUTO-INC锁。

• 行锁的具体类型

  • 普通行锁

  • 间隙锁

    官方名为LOCK_GAP，简称gap锁，仅仅是为了防止插入幻影记录而提出的。获取间隙锁后，其他事务不能在当前记录和相邻前一条记录的间隙中插入新记录。

    MySQL在可重复读隔离级别下是可以解决幻读问题的：

    1. MVCC
    2. 加锁，加锁方案解决时，由于第一次执行读取操作时，幻影记录尚不存在，所以无法给其加锁。因此提出间隙锁。

  • Next-Key Lock

    官方名为LOCK_ORDINARY。既想锁住某条记录，又想阻止其他事务在该记录前面间隙插入新纪录。

  • 插入意向锁

    在插入数据时发现存在间隙锁，需要进行等待。在等待的时候需要在内存中生成插入意向锁。插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁（要它有啥用）。

Mysql主从是怎么同步的？

MySQL服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行（两个线程）该日志文件保持与主服务器的数据一致。

• 主从同步的好处

  1. 读写分离。
  2. 数据备份。
  3. 故障切换。
  4. 主服务器生成实时数据，从服务器分析，提高主服务器性能。

• 同步方法

  1. Statement Based Replication 基于SQL语句的复制，主服务器将SQL写到日志文件中

     优点：日志文件小；记录了所有语句，方便审计。

     缺点：sql中的函数语句不能正确复制；告警信息不能正确复制；

  2. Row Based Replication 基于行的复制，行变化作为事件写入二进制日志。

     优点：最安全的复制方式；更少的行级锁

     缺点：日志文件大；不能进行日志审计

  3. Mixed Based Replication 混合，既使用SBR又使用RBR，默认SBR。

MySQL主从同步延迟问题

1. 高延迟——高并发场景下从库的数据会比主库慢，从而导致延迟。出现写入主库成功，但是数据需要等待几十毫秒或者更长时间才能读到。
2. 丢数据——master节点突然挂掉，但是新写的数据还没有同步到slave节点，导致数据丢失。

主库和从库执行相同的SQL，主库可以快速执行，为什么从库不行？

master可以并发，但是slave的SQL线程只能顺序执行。当TPS较高时，产生的SQL超过slave一个SQL线程能承受的范围，延时就产生了。

show status # Seconds_Behind_Master查看从库落后多长时间

• MySQL主从同步解决方案

  1. 半同步复制——解决数据丢失问题，但会增加延迟。

     默认是异步复制的方式，master并不关心slave数据有没有写入成功。

     从库写入relay_log成功才返回ack消息，然后才返回客户端。

     

  2. 多库并行复制——解决从库复制延迟问题

     多启了几个线程

• 常见主从延迟原因及解决方案

  1. SQL执行速度慢：优化索引
  2. 批量DML：通过缓存等方式降低DB并发压力
  3. 大事务，耗时长：事务拆解，移除不必要事务

MySQL中varchar与char的区别以及char(10)中的10代表的涵义？

• 从字面上看varchar是变长，char是定长，实际是这样吗？如果使用utf8进行编码有什么不同？
• 在utf8下char(10)代表最小可以存储10字节的字符，最大可以存储30字节的字符。也就意味着变长长度列表中会记录char数据类型的长度。
• 在多字节字符集（如：utf8)的情况下，char和varchar的实际存储都是变长字符类型。
• char(10)最小存储10个字节，如果不足用0x20（空格）补足，因此在InnoDB utf8编码集下更推荐使用varchar。

持续更新…