MySQL相关面试

MySQL相关面试

1、varchar和char区别

1. char需要设置长度，长度是固定的。varchar长度是不固定的，所以varchar的空间利用率高（时间换空间）。
2. char的长度是固定的，所以时间利用率上比varchar高（空间换时间）。
3. char适用于固定的字符串，比如身份证、性别代号、手机号等等一系列固定的字符串。

2、数据库三大范式

1. 1NF（第一范式）：保证字段不可再分，保证原子性（一个操作或者一组操作不可被中断的执行完毕/不执行）。
2. 2NF：在满足1NF的条件下，表的每一列必须和主键有关系。
3. 3NF：满足2NF的条件下，表的每一列必须和主键有直接关系，不能是间接关系。（A->B，B->C，A->C）。

3、SQL执行顺序

from（从那张表里进行查询）->on（连接条件，表1.id=表2.id）->inner join、left join、right join（表之间的连接关系）

->where（查询条件）->group by（分组）->聚合函数（if、循环）->having（分组过滤条件）->select、update、delete

->distinct（去重）->order by（排序）->limit（分页，限制）

4、索引是什么？它的优点？类型？

索引是一种高效获取数据的数据结构，相当于目录，是一个文件，占用物理地址。

优点：

• 提高检索的速度
• 索引列对数据排序，降低排序成本
• mysql 8之后引入的“隐藏索引”，当一个索引被隐藏就不会被优化器使用。索引对数据库的作用而言可以进行调优。

缺点：

• 索引是一个文件，占用物理地址会占用空间。
• 降低更新速度。因为更新数据的时候，也要更新索引。

索引的类型：

• 普通索引：基本索引类型，可以定义索引的字段为空或者重复值。
• 唯一索引：索引的值必须是唯一，允许定义索引的字段为空。
• 主键索引： 索引的值必须唯一，不可以为空。
• 复合索引：多个字段加索引，遵守最左匹配原则。
• 全局索引：只有在MyISAM引擎上使用。

5、如何设计索引？

1. 选择唯一性索引：值是唯一的。
2. 经常作为查询条件的字段作为索引。
3. 为经常需要排序、分组和联合操作的字段建立索引：order by、group by、union（联合）、distinct（去重）等。
4. 限制索引个数，索引数量多需要的磁盘空间就多，对空间利用率来说不友好。
5. 表数据比较少（根据工程量来定义）的时候，不适用索引，查询速度>遍历索引速度。
6. 删除不常用或者不再用的索引
7. 用类型小的数据作为索引。INT和BIGINT就用int，类型小占据空间小，查询速度快。
8. 使用前缀索引，要是字符串越长，那么索引占的空间越大，比较的时间越长。

6、如何避免索引失效？（sql优化）

1. 某列使用范围查询（>、<、like、between and）时，右边的所有列索引会失效。
2. 不对索引字段进行运算。
3. 在where语句上，不使用or、!=、<>和对值null的判断。
4. 避免使用“%”开头的like模糊查询。
5. 字符串不加单引号，避免索引失效。

7、索引的数据类型？为什么用树结构（一般在这里就开始问B+树、InnoDB、Memory了）

Hash：查询时调用Hash函数获取调用地址，回到数据库表进行查询实际数据。（InnoDB和MyISAM不支持、Memory支持）

B+树：每次从根节点出发去查询，得到地址，回到数据库表进行实际查询。

用树结构是因为可以加快查询效率，而且可以保持有序。

8、二叉查找树、B（B-）树、B+树

• 二叉树：一个节点最多存在2个子节点左小右大，查询次数和比较次数都是比较小的，但是索引是存在磁盘里的，当数据量过大时，不能直接把整个索引文件加载到内存，需要分多次IO，最坏情况IO次数等于树的高度，为了减少IO需要把树从竖向变成横向。
• B（B-）树：是一种多路查询，每个节点包含K个子节点，节点都存储**索引值和data，**K是B树的阶（树的高度）。比较次数上升，但是是在内存层面比较，可以忽略。B树的高度相比二叉树可以进一步减少。
• B+树：只有叶子结点存储的是索引值。

8.1 B+树 VS B树

1. B树只适合随机检索，而B+树同时支持随机检索和顺序检索（叶子节点相当于链表，保存索引值都是有序的）
   • 顺序检索：按照序列顺序遍历，找到给定值。
   • 随机检索：不断从序列中随机抽取数据进行比较。
2. 减少磁盘IO，提高空间利用率。B+树非叶子节点不存放数据，只有索引值。非叶子节点 可以保存更多的索引值，B+树高度更低，减少IO次数
3. B+树适合范围查找：数据库大部分都是范围查询，B+树的叶子结点是有序链表，直接进行遍历，而B树的范围查找可能两个节点之间距离很远，只能通过中序遍历进行查询。

9、最左匹配原则

最左优先，以最左边为起点任何连续的所有都能匹配上。遇到范围查询时就会停止

//这样索引abc列都起效，因为符合最左匹配原则，where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序
select * from Z where a = 1 and b = 2 and c = 3

//因为a列是起点，没有a列匹配不上，所以索引失效
select * from table_name where b = 2 and c = 3

//因为连续不到b，所以只有a列索引生效
select * from table_name where a = 1 and c = 3

10、MySQL怎么查看是否使用索引？/怎么查看sql执行计划

使用explain

ex：explain select * from 表明 where 条件

结果：查询出key（索引），还有type字段，可以看到索引是全表扫描还是索引扫描等

11、一条SQL查询很慢，如何进行优化？

排查：

1. 开启慢查询
2. 查看慢查询日志（定位低效sql，命令：show processlist）
3. 使用explain查看sql的执行计划（查看索引是否失效或者性能低）

优化：

• sql优化+索引+数据库结构优化+优化器优化

12、MyISAM、InnoDB、Memory区别

MyISAM：mysql5.5之前的存储引擎，是表锁（悲观锁）级别的。不支持事务和外键

InnoDB：mysql5.5之后的存储引擎，是行锁（乐观锁）级别的吗。支持事务和外键

Memory：内存数据库引擎，因为在内存操作，所以读写快，但是Mysql服务重启，会丢失数据，不支持事务和外键

13、什么是事务？特性是什么

事务是对数据库中一系列操作进行统一的回滚或者提交的操作，主要用来保证数据的完整性和一致性。

特性：

• 原子性：要么全部失败/成功
• 一致性：事务执行前和执行后，原本和数据库一直的数据仍然一致
• 隔离性：事务和事务之间互不干扰
• 持久性：事务一旦被提交，那么对数据库中数据的改变是永久的

14、脏读、不可重复读、幻读

脏读：“读未提交”，事务A读取了事务B提交的内容

不可重复读：在一个事务内，多次读取同一个数据，返回不同结果。因为在该事物间隔读取数据期间内，其他事务对这段数据进行了修改，并且提交，就会发生不可重复读事故

幻读：在同一个事务中，第一次读取的结果集和第二次读取的结果集不一样。

脏读和不可重复读是基于数据值的错误，幻读是基于条数增加或减少的错误

15、事务的隔离级别

• 读取未提交内容：在该级别内，所有事务可以看到其他事务未提交内容。（脏读）
• 读取提交内容：一个事务只能看到已经提交的事务内容
• 可重读：MySQL默认的事务隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据，理论上会导致幻读
• 可串行化：最高隔离级别，通过强制事务排序，使事务之间不能相互冲突，解决幻读问题。在每个读的数据上加共享锁，在这个级别，可能导致大量的超时现象和锁竞争

16、数据库锁

16.1 乐观锁，悲观锁

种类：乐观锁和悲观锁

悲观锁：对数据冲突采用悲观态度，无论是读取数据还是数据修改都要加锁，适用于读取数据比较多的场景。如果写入操作突然增多就会发生数据冲突，放到应用层去不断判断数据一致性，就会加大查询操作，降低系统吞吐量

乐观锁：对数据冲突采用乐观态度，在数据读取时不会加锁，只有修改写入时会加锁。适用于写入操作比较多的场景。如果读取操作操作突然增多，就会发生数据冲突。

数据库常见的乐观锁，悲观锁：

• 悲观锁：表锁，行锁，读锁，写锁。
• 乐观锁：不会刻意使用数据库本身锁机制，依据数据本身来保证数据正确性。
  1. 使用版本号。数据库表中增加“版本号”，读取数据时，将版本号一起读取，每次修改数据，版本号进行修改，进行判定。
  2. 使用时间戳。同理

16.2 死锁

两个或两个以上的进程执行过程中，因为抢夺资源而造成相互等待的一种现象，如果外力不参与，将一直等待持续下去，此时就是死锁状态。

解决：

1. 等待锁超时，当更新语句等待锁一段时间后会超时退出，不会无休止等待下去，但是这个超时时间默认是50s，高并发系统中是无法接受的。
2. 设置死锁检测：通过设置innodb_deadlock_detect = on，开启MySQL死锁检测，系统自动检测死锁的事务并回滚改动。（常用）

避免发生：

1. 合理规划表的执行顺序，避免多个事务以不同顺序更新一个表。
2. 避免并发地执行涉及修改数据的语句。
3. 每个事务的执行时间不可太长，在业务允许的情况下可以考虑将事务分割成几个小事务来执行。
4. 要求每个事务一次就将所有要使用的数据全部加锁，否则就不执行。

17、优化数据库

• 优化SQL语句
• 加Redis缓存
• 分表
• 读写隔离

17.1 order by和group by优化（Index，filesort）

group by实质是先排序后进行分组，遵照索引键的最佳左前缀法则，where高于having，能写where的条件下尽量不要写having。

order by：

1. 尽量使用Index方式排序，避免使用filesort方式排序；
2. 尽可能在索引列上完成排序操作，遵照索引的最左前缀法则。

MySQL支持两种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序，FileSort效率较低。

filesort的排序算法：双路排序，单路排序。：

• 双路排序：MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。

• 单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间。因为它把每一行都保存在内存中了。

18、SQL优化

• 不使用select *，使用具体字段
• 使用数值代替字符串，0=男，1=女
• 返回避免大量数据，采用分页
• 使用索引，提升查询速度。
• 批量插入比单条更快，因为事务只需要开启一次，数据量太小体现不了
• 避免子查询，优化为多表查询

19、几种关联查询

• 内连接（inner join）：查询两个表匹配数据
• 左连接（left join）：查询左表全部行以及右表匹配列
• 右连接（right join）：查询右表全部行以及左表匹配列

20、in和exists区别

in（）：适合子表（子查询）比主表数据小的情况

exists（）：子表（子查询）比主表数据大的情况

21、都是删除，drop、truncate、delete的区别

速度：drop > truncate > delete。

回滚：delete支持，truncate和drop不支持。

删除内容：delete表结构还在，删除部分或者全部数据，不释放空间。truncate表结构还在，删除全部数据，释放空间。drop表结构和数据不在，包括索引和权限，释放空间。