对于互联网行业而言，MySQL是非常重要的一环。

除了我们刚刚梳理过的MySQL之中索引的部分知识之外，还有其他的一些MySQL相关的知识点需要我们去掌握。在之前的一些文章之中有过梳理，在此总结一篇我认为很好的文章，作为一个综合性的整理。

学习文章地址：

http://huyan.couplecoders.tech/mysql/笔试面试/2019/07/10/MySQL面试一百问/

对于本文之中一些观点的不同理解

首先是对于”事务“的不同理解。我是不推荐在项目之中使用数据库的事务的。因为事务在数据库之中的处理会可能造成一些问题，这些问题在下文之中会涉及到。对于事务相关的需求，可以在业务之中使用代码实现，同时很多事务相关的需求如果中途被打断，其实在业务代码之中更容易去了解其被打断的原因，并且针对不同的业务做一些逻辑上的处理，例如及时放弃并开始重试，或者怎么样的一个需求。

索引相关

关于索引的内容在上一篇博文之中已经写过不少了，在这里只是简要的介绍一下。

什么是索引？

索引是一种数据结构，将某些数据按照特定顺序进行重新排列，以达到优化查找速度的目的。

索引是一个怎样的数据结构？

索引的数据结构和选用的具体存储引擎的实现有关。索引有Hash索引，B+树索引等等，而经常使用的InnoDB实现的索引的数据结构是B+树索引。

Hash索引和B+树索引有何区别？优劣比较？

Hash索引，其底层就是hash表格，每次操作都是调用hash函数获得其键值，然后回表查询。

而B+树其底层是多路平衡查找树，在查询过程之中，每次都是从根节点直接到最底层的叶子节点，查找叶子节点才可以得到最终所查键值。根据索引之中的键值，来确定其是否需要回表查询数据。（例如上一篇文章之中说的，联合索引在某些情况之下可以直接返回而不需要拿到主键键值再去大表之中查询）

那么可以看出来其不同：

从原理上来讲，hash索引是将数据取Hash值之后作为其主键，然后对其进行各种操作。这就决定了对于Hash索引而言，每一条记录都是单独处理的，彼此之间没有范围或者排序上面的联系。但是B+树在叶子这一层所有的节点都连在一起，且是按照顺序连接，因此其可以处理范围值。B+树的所有节点都遵循左节点小于父节点，右节点大于父节点的的规律。

Hash函数的优势：

Hash索引进行等值查询更快

B+树的优势：

B+树支持使用索引进行排序，而Hash索引不支持其使用索引进行排序。
B+树支持模糊查询和多列查询的最左前缀匹配。但是因为hash函数的不可预测性，这两个特性hash索引都无法支持。因为对于Hash而言，AAAA和AAAAB的hash值是完全没有关系的。
hash索引在任何时候都不能避免回表查询数据。Hash索引之中存储的是hash值和值所在的指针。那么在Hash索引之中就无法存储任何的有用信息。而B+树在符合某些条件的时候，比如上一篇文章之中所说的，其可以在聚簇索引之中直接取得部分值，在某些情况之下不需要再去大表查所有的值。
hash索引虽然在单个值的查询上面比较快，但是其性能不稳定，不可预测，因为其在某个键值存在冲突的时候，会发生hash碰撞，这个时候的效率比较差。但是B+树的查询比较稳定（其每次都到叶子节点才可以取得主键值，而且本身B+树的高度就比较低）。

综上，在大部分的情况下，选择B+树索引可以获得较好而且比较稳定的查询速度，而不需要使用hash索引。

提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据，那么什么是聚簇索引？

在B+树的索引之中，叶子节点可能存储了当前的key值，或者存储了key值和当前整行的数据，这就是非聚簇索引和聚簇索引。

在InnoDB之中，只有主键索引才是聚簇索引。如果没有主键，那么首选是一个唯一键来做索引，如果所有的列都不是唯一键，那么自己生成一个列作为索引列。

如果查询使用聚簇索引的时候，在对应的叶子节点就可以获取所有的信息，不再需要进行回表查询。

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

不一定。我们从定义上面也可以区分出来，非聚簇索引之中的叶子节点上面有可能包含其所指向列的部分信息，如果我们所要的信息在索引之中已经完全包括了，例如我们已经在员工表的age 上面建立了索引，那么当进行select age from employee where age<20 的查询，就可以在叶子节点上面直接拿到age信息，从而不需要进行再次的回表查询。

在建立索引的时候，有哪些需要考虑的因素呢？

在建立索引的过程之中，首先要考虑的是字段的使用频率，如果涉及到联合索引，按照我前一篇博文之中提到过的，还需要考虑索引之中的顺序问题。此外就是不要矫枉过正，对使用频率不高的列也进行额外的索引，防止对表造成过高的压力。

联合索引是什么？为何要注意联合索引之中的顺序？

MySQL 可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引之中，如果想要命中索引，必须按照建立索引的顺序使用索引，不然就没有办法命中索引。

这个在我们之前的文章之中也有提到过，就是联合索引之中，只有前一列的值相同的情况下，后一列的值才有序，所以没有办法单独使用后一列作为索引。

创建的索引有没有被使用到？怎么才能知道为何某条语句运行速度很慢？

MySQL 之中提供了explain命令来查看语句的执行计划。MySQL在执行某条语句之前，会将该语句直接过一遍查询优化器，之后会拿到对该语句的分析，也就是执行计划，其中包含的信息可以用来做进一步的分析。这些信息可以用来分析是否命中了索引，例如possible_key，key,key_len等字段，分别说明了这个语句可能使用的索引，实际使用的索引和索引长度。

在哪些情况下会发生针对该列创建了索引但是查询时候没使用的情况呢？

使用不等于查询：个人理解为比如联合索引针对该列有索引，但是是比较靠后的顺序，那么可能第一列就已经筛选完数据返回了，那么就是使用到了索引但没有在该索引之中查询。
列参与了数学运算或者函数
在字符串 like 的时候，左边是通配符。比如 %aaa
在mysql分析全表扫描比使用索引还快的时候不使用索引
在使用联合索引时，前面一个列是范围查询，那么后面的即使符合最左前缀法则，也没法使用索引。（上一篇博文之中有讲）

事务相关

什么是事务

事务的最经典例子就是转账，在转账的过程之中要么所有流程全部成功，要么失败，不可以出现部分执行的情况。

最常见的一种理解就是事务之中的操作，要不全部成功，要不全部失败。

ACID是什么？解释一下？

A=Atomicity

原子性，也就是上面说的，要不全部成功，要不全部失败，不可以只有部分执行。

C=Consistency

系统总是从一个一致性的状态到另一个一致性的状态，不存在中间态。

I=Isolation

隔离性，通常而言，一个事务在没有完全提交之前，对于其他事务是不可见的。当然，存在例外情况。

D=Durability

持久性，一旦事务提交，那么就永远生效，哪怕系统崩溃其结果也是有效的。

同时有多个事务在进行会怎样呢？

多事务的并发进行一般会造成以下三个问题：

脏读：A事务读取到了B事务未提交的内容，但是B事务在之后进行了回滚
不可重复读：针对同一行数据，当设置A事务只能读取到B事务已经提交的部分时，会造成在A事务查询两次的过程之中，居然发现结果不一样，原因是B进行了提交操作
幻读：在A事务读取一个范围的内容的过程之中，其发现两次读取的内容条数不同，原因是B事务在期间插入了一条数据，造成“幻觉”

怎么解决这些问题呢？MySQL的事务隔离级别有哪些？

这里有个例子说的很好：https://www.cnblogs.com/zhoujinyi/p/3437475.HTML

先上结论：

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

MySQL的有四种隔离级别，如下：

未提交读(READ UNCOMMITED):

这就是上面说的例外情况。这个隔离级别下面，其他事务可以看到本事务的未提交的部分修改，个人理解为，这个层次就根本没有任何的隔离措施，因此会造成脏读的问题。也就是，读取到了其他事务未提交的部分，然后对这个事务进行了回滚。

这个级别又菜又没用，所以没人用。

session 1:
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
row in set (0.00 sec)
   
mysql> select @@session.tx_isolation;
+-----------------------+
| @@session.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
row in set (0.00 sec)
   
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
   
mysql> insert into ttd values(1);
Query OK, 1 row affected (0.05 sec)
   
mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
+------+
row in set (0.00 sec)
   
session 2:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
row in set (0.00 sec)
   
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ   |        --------该隔离级别下(除了 read uncommitted)
+-----------------------+
row in set (0.00 sec)
   
mysql> select * from ttd;
Empty set (0.00 sec)              --------不会出现脏读
   
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
   
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |   --------该隔离级别下
+------------------------+
row in set (0.00 sec)
   
mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |                                       --------REPEATABLE-READ级别出现脏读
   
+------+
row in set (0.00 sec)

已提交读(READ COMMITED):

已提交读意味着其他事务只可以读取到本事务已经提交的部分，不能读取到其还没有提交的部分。这个级别有不可重复读的问题，说的再直白一点，其也就是避免了之前一个级别之中的“脏读”，事务如果还没提交自己的修改，就不能被读取到，这样就避免了事务失败了中途回滚的脏读问题。但是除了这个之外，还是什么都没有解决。这个级别有不可重复读的问题，比如在某个事务执行之中其数据被其他事务修改了，就会造成在一个线程之中的两次读取数据结果不同。

  session 1:
  mysql> select @@session.tx_isolation;
  +------------------------+
  | @@session.tx_isolation |
  +------------------------+
  | READ-COMMITTED         |
+------------------------+
  row in set (0.00 sec)
  

mysql> start transaction; Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd; +——+ | id | +——+ | 1 | +——+ row in set (0.00 sec)

session 2 :

mysql> select @@session.tx_isolation; +————————+ | @@session.tx_isolation | +————————+ | REPEATABLE-READ | +————————+ row in set (0.00 sec)

mysql> start transaction; Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd; +——+ | id | +——+ | 1 | +——+ row in set (0.00 sec)

mysql> insert into ttd values(2); /也可以更新数据 Query OK, 1 row affected (0.00 sec)

mysql> select * from ttd; +——+ | id | +——+ | 1 | | 2 | +——+ rows in set (0.00 sec)

mysql> commit; Query OK, 0 rows affected (0.02 sec)

session 2 提交后,查看session 1 的结果;

session 1:

mysql> select * from ttd; +——+ | id | +——+ | 1 | ——–和第一次的结果不一样,READ-COMMITTED 级别出现了不重复读 | 2 | +——+ rows in set (0.00 sec)

3. 可重复读（REPEATABLE READ）：

   这个解决了上面不可重复读的问题，但是仍然有一个“幻读”的新问题。不可重复读，指的是对于某一行的数据，但是行锁不可以避免表锁的问题，比如当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

   ```sql
   session 1:
   mysql> select @@session.tx_isolation;
   +------------------------+
   | @@session.tx_isolation |
   +------------------------+
   | REPEATABLE-READ        |
   +------------------------+
   row in set (0.00 sec)
   
   mysql> start transaction;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> select * from ttd;
   +------+
   | id   |
   +------+
   |    1 |
   |    2 |
   +------+
   rows in set (0.00 sec)
   
   session 2 :
   
   mysql> select @@session.tx_isolation;
   +------------------------+
   | @@session.tx_isolation |
   +------------------------+
   | REPEATABLE-READ        |
   +------------------------+
   row in set (0.00 sec)
   
   mysql> start transaction;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> insert into ttd values(3);
   Query OK, 1 row affected (0.00 sec)
   
   mysql> commit;
   Query OK, 0 rows affected (0.03 sec)
   
   session 2 提交后,查看session 1 的结果;
   
   session 1:
   
   mysql> select * from ttd;
   +------+
   | id   |
   +------+
   |    1 |                                      --------和第一次的结果一样,REPEATABLE-READ级别出现了重复读
   |    2 |
   +------+
   rows in set (0.00 sec)
   
   (commit session 1 之后 再select * from ttd 可以看到session 2 插入的数据3)

可串行化(SERIALIZABLE):

这是最高的隔离级别，可以解决上面的所有问题，因为其强制所有的操作串行执行，这会导致并发性能极速下降。

InnoDB使用的是哪种隔离级别呢？

InnoDB之中默认是可重复读的隔离级别。

MySQL都有哪些锁呢？

从类别上看，有共享锁和排他锁

共享锁：又叫做读锁，当用户要对数据进行读取的时候，就加上共享锁。共享锁同时可以加上多个。
排他锁：又叫做写锁，当用户要写入数据时候，加上排他锁。排他锁只可以加一个，其和其他的共享锁，排他锁都相斥

从粒度上讲，锁的粒度取决于具体的引擎。InnoDB实现了行级锁，页级锁，表级锁。

表结构设计

在我们的上一篇博文之中已经有了基本的梳理，在这里我只是将其中一些还没有涉及到的点进行进一步的拓展。

为什么要尽量设定一个主键？

一张表，不可能没有主键。设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

主键使用自增ID还是UUID?

主键应该使用自增ID，原因是在底层是B+树，而B+树天然就具备排序的性质。在MySQL的InnoDB存储引擎之中，主键索引是作为聚簇索引存在的。也就是说其叶子节点上面按序存储了主键索引和全部的数据。

这样的情况之下，如果主键是UUID，那么每次插入记录的开销都要大很多，因为UUID不能保证是自增的，所以会造成很多的数据插入，数据移动等开销，并且产生很多的内存碎片，造成和原来相比插入性能的下降。

图片来源于《高性能MySQL》: 其中默认后缀为使用自增ID,_uuid为使用UUID为主键的测试,测试了插入100w行和300w行的性能.

2019-07-18-15-00-18

如果没有设置自增ID作为主键会怎么样？

如果没有设置自增ID作为主键，那么首先会选择一列其中值唯一的列作为主键来生成聚簇索引(cluster Index)。如果没有这样的一列，那么InnoDB会自己生成一个隐式的主键。

If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

字段定义为什么最好是not null?

官网介绍是因为null会额外占用字节来表示此列的状态：

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

null值会占用更多的字节,且会在程序中造成很多与预期不符的情况.

如果要存储用户的密码散列,应该使用什么字段进行存储?

密码散列,盐,用户身份证号等固定长度的字符串应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率.

MySQL存储引擎区别？

MySQL支持多种存储引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多数的情况下,直接选择使用InnoDB引擎都是最合适的,InnoDB也是MySQL的默认存储引擎.

InnoDB和MyISAM有什么区别?

InnoDB支持事物，而MyISAM不支持事物
InnoDB支持行级锁，而MyISAM支持表级锁
InnoDB支持MVCC, 而MyISAM不支持
InnoDB支持外键，而MyISAM不支持

其他问题

varchar(10)和int(10)代表什么含义?

varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.

MySQL的 binlog有几种形式？

先上结论，一共三种，statement, row 和 mixed

statement 模式下面的记录单元是语句，也就是每个sql造成的影响都会记录。因为sql的执行是有上下文的，因此在保存的时候就需要保存相关信息。同时，还使用了一些函数的语句无法复制。
row级别下面的记录单元是行，基本可以全都记下来，缺点是有些操作导致大量的改动，从而保存的信息太多，量太大。
mixed。折中方案，普通操作用statement,无法使用statement的时候使用row

此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录.

超大分页一般怎么解决？

超大分页，说白了就是想方设法按到索引上面去。比如select * from table where age>20 limit 1000000,0，这条语句先取1000000条记录然后全都丢弃，只剩下10条。

有一种思路，是修改成select * from table where id in (select id from table where age > 20 limit 1000000,10)，

而且实际上，对于超大分页，比如查询第10000页的内容，一般不需要精确查找，那么可以直接使用ID来进行模糊的匹配，虽然删除等操作可能对ID的连续性有影响，但是在精确度要求不高的情况下是无所谓的。

实际可用的情况是靠缓存，将有可能的结果存至redis等之中，然后直接返回即可。

关心过业务之中的sql耗时吗？统计过慢查询吗？如何优化？

慢查询主要是下面的几种因素：

查询的数据量过大
没有对对应的列加入索引
load了不需要的数据列

对症下药：

首先对语句进行自我分析:是不是查询了不需要的数据列？比如这种情况：

2019-07-18-11-46-19

这种情况下，如果直接筛选会有很大的问题，所以尽量避免这种查询很多数据然后丢弃大部分的情况。可以对语句进行分析并且重写

分析语句的执行计划。使用explain来查询语句的具体执行，然后尽量让语句可以命中索引。根据具体的情况修改语句，或者修改索引
如果真的就是要大量数据，在对语句进行自我优化和explain之后还是数据量很大，那么可以考虑横向或者纵向的分表。

什么是横向分表？什么是纵向分表？可以举个例子吗？

横向分表：个人理解为将表格横向“拽开”，也就是“按行分表”。将一个有一千万数据的表格按照自增ID分成10个表，每个表之中有一百万条数据，那么单表的效率此刻是满足我们的需求的。

纵向分表：将某些不常用，且数据量存储较大（例如text类型）的表格纵向分开。比如有些列之中存了很长的备注，这个备注不是经常使用的，那么就可以单独分出来一个id-备注的表格，在需要显示备注的时候，在从这个表格之中查询备注信息并且将其显示出来。这样可以做到不拖累主表的效率。

什么是存储过程？有哪些优缺点？

存储过程就是一些预编译的SQL语句，个人理解为一个SQL的“程序块”，就像我们编程的时候，某个函数接收参数并且执行特定任务一样，存储过程也可以接收参数并且在SQL之中执行特定的任务。

优点：因为是在数据库之中预编译的代码块，所以执行效率高，而且可以降低网络通信亮（全在数据库内部搞），一定程度确保数据安全。

但是！！！！！

这个东西根本不好用啊！

很难维护啊！！！！

所以强烈不推荐存储过程，不可以维护的代码就是shit，我再重复一遍。

MyBatis之中的# 和 $ 有什么区别？

# 会将传入的内容当做字符串，但是 $ 会将传入的值直接拼接在sql之中，所以 # 可以一定程度上防止注入攻击。

MySQL面试相关问题