这里是参考B站上的大佬做的面试题笔记。大家也可以去看视频讲解！！！

文章目录

  • 1 、mysql索引结构，各自的优劣
  • 2 、索引的设计原则
  • 3 、mysql锁的类型有哪些
  • 4 、mysql执行计划怎么看
  • 5 、事务的基本特性和隔离级别

1 、mysql索引结构，各自的优劣

索引的数据的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有hash索引，B+树索引等，innoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

B+树：

  • B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速快速左右移动，效率非常高。因此B+树索引被广泛应用于数据库、文件系统等场景。

哈希索引：

  • 哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
  • 如果时等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键值所在位置，然后再根据链表往后扫描，直到找到相应的数据；
  • 若果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索。
  • 哈希索引也没办法利用索引完成排序，以及like‘xxx%’这样的部分模糊查询（这种部分模糊查询，其实质上也是范围查询）；
  • 哈希索引也不支持多列联合索引的最左匹配规则。
  • B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。

2 、索引的设计原则

查询更快、占用空间小

  • 1 、适合索引的列是出现在where子句中的列，或者连接子句中指定的列
  • 2 、基数较小的表，索引效果较差，没有必要在此建立索引
  • 3 、使用短索引，如果对长字符串进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检索其余行是否可能匹配。
  • 4 、不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
  • 5 、定义有外键的数据列一定要建立索引

不适合建立索引的

  • 1 、更新频繁字段不适合创建索引
  • 2 、若是不能有效区分数据的列不适合做索引（如性别、男女未知、最多也就三种，区分度实在太低）
  • 3 、尽量的扩展索引，不要新建索引。比如表中已经有a索引，现在要加（a，b）的索引，那么只需要修改原来的索引即可。
  • 4 、对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
  • 5 、对于定义为text、image和bit的数据类型的列不要建立索引。

3 、mysql锁的类型有哪些

基于锁的属性分类：共享锁、排他锁

基于锁的粒度分类：行级锁（INNODB)、表级锁（INNODB、MYISAM）、页级锁（BDB引擎）、记录所、间隙锁、临建锁。

基于锁的状态分类：意向共享锁，意向排他锁。

  • 共享锁（Share Lock）

共享锁又称读锁，简称S锁：当一个事务为数据加上读锁之后，其他事务只能对该锁加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

  • 排他锁（exclusive Lock）

排他锁又称写锁，简称X锁：当一个事务为数据加上写锁时，其他请求将不再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时，不允许其他人同时修改，也不允许其他人读取，避免了出现脏数据和脏读的问题。

  • 表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问：

特点：粒度大、加锁简单、容易冲突

  • 行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问

特点：粒度小、加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高。

  • 记录所（Record Lock）

记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。
精准条件名中，并且名中的条件字段是唯一索引
加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

  • 页锁
    -页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
    特点：开销和加锁时间界于表锁和行锁之间；会出现死锁：锁定粒度界于表锁和行锁之间，并发读一般。
  • 间隙锁（Gap Lock）

属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。
范围查询并且查询未名中记录，查询条件必须名中索引、间隙锁只会出现在REPEATABLE_READ（重复读）的事务隔离级别中。

触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下的问题，在同一个事务里，A事务的两次查询出的结果会不一样。
比如表里面的数据ID为 1，4，5，7，10，那么会形成以下几个间隙区间-n-1区间，1-4区间、7-10区间、10-n区间（-n：代表负无穷大，n代表正无穷大）

  • 临建锁（Next-Key Lock)

也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临建锁会把查询出来的记录锁住，同时也会把该范围查询内的所有空隙空间也会锁住，再之它会把相邻的下一个区间也会锁住

触发条件：范围查询并命中，查询命中了索引。
结合记录锁和间隙锁的特性，临建锁避免了在范围查询时出现脏读、重复度、幻读问题。加了临建锁之后，在范围区间内数据不允许被修改和插入

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是意向锁。

  • 意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

  • 意向排它锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

4 、mysql执行计划怎么看

执行计划就是sql的执行查询顺序，以及如何使用索引查询，返回的结果集的行数

1 、 id：是一个有顺序的编号，是查询的顺序号，有几个select就显示几行。id的顺序是按select出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。

2 、selectType 表示查询中每个select子句的类型

  • SIMPLE：表示查询中每个select子句的类型
  • PRIMARY：表示此查询是最外层的查询（包括子查询）
  • SUBQUERY：子查询中的第一个SELECT
  • UNION：表示此查询是UNION的第二或后面的查询语句，取决于外面的查询
  • UNION RESULT,UNION 的结果
  • EEPENDENT SUBQUERY：子查询中的第一个SELECT,取决于外面的查询，即子查询依赖于外层查询的结果。
  • DERIVED：衍生，表示导出表的SELECT(FROM子句的子查询）

3 、table：表示该语句查询的表

4 、type：优化sql的重要字段，也就是我们判断sql性能和优化程度的重要指标。他的取值类型范围：

  • const：通过索引一次名中，匹配一行数据
  • system：表中只有一行记录，相当于系统表
  • eq_ref：唯一性索引扫描，对于一个索引键，表中只有一条记录与之匹配
  • ref：非唯一性索引扫描，返回匹配某个值的所有
  • range：只检索给定范围的行，使用一个索引来选择行，一般用于between、<>;
  • index：只遍历索引树；
  • ALL：表示全表扫描，这个类型的查询是性能最差的查询之一。那么基本就是随着表的数量增多，执行效率越慢

执行效率 ALL < index < range < ref < eq_ref < const <system。最好是避免ALL和index

5 、possible_key： 它表示MySQL在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。

6 、 key：此字段是mysql在当前查询时所真正使用到的索引。它是possible_keys的子集

5 、事务的基本特性和隔离级别

事务的基本特性和隔离级别

原子性：指的是一个事务中的操作要么全部成功，要么全部失败。
一致性：指的是：数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设A只有90块，支付之前我们数据库里的数据都是符合约束的，但是如果事务执行成功了，我们的数据库数据就破坏约束了，因此事务不能成功，这里我们说事务提供了一致性的保证。

隔离性：指的是一个事务的修改在最终提交前，对其他事务是不可见的。
持久性指的是：一旦事务提交，所做的修改就会永久保存到数据库中。

隔离性有四个隔离级别，分别是：

  • read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫脏读。
    用户本来应该读取到id=1的用户age应该是0，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。
  • read commit 读已提交，两次读取结果不一致，叫做不可重复读。
    不可重复读解决了脏读的问题，他只会读取已经提交的事务。
    用户开启事务读取id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复度。
  • repeatable read 可重复度，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读。
  • serializable串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

脏读（Drity Read）：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读（Non-repeatable read）：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更细的原有的数据。

幻读（Phantom Read）:在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列（Row）数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。