truncate和delete有什么区别？

truncate table：删除内容、不删除定义、释放空间 delete table：删除内容、不删除定义、不释放空间 drop table：删除内容、删除定义、释放空间

具体来说，有以下区别：

• truncate table 只能删除表中全部数据，delete from 可以删除表中的全部数据也可以部分删除。
• delete from记录是一条条删除的，删除的每一行记录都会进日志，而truncate一次性删除整个页，日志只会记录页的释放。
• truncate删除后不能回滚，delete则可以。
• truncate的删除速度比delete快。
• delete删除后，删除的数据占用的存储空间还存在，可以恢复数据；truncate则空间不存在，同时不能恢复数据。

总的来说，其差别在于，truncate删除是不可恢复的，同时空间也不存在，不支持回滚，而delete删除正好相反。

select Count(*)、select Count(数字)和select Count(column)的区别？

count(*)和count(1)返回的结果是记录的总行数，包括对NULL的统计；而count(column)是不会记录NULL的统计。

EXISTS关键词的使用方法

EXISTS表示是否存在，使用EXISTS时，如果内存查询语句查询到符合条件的值，就返回一个true，否则，将返回false。

例如：

SELECT * FROM user 
    where EXISTS
    (SELECT name FROM employee WHERE id=100)

如果employee表中存在id为100的员工，内层查询就会返回一个true，外层查询接收到true后，开始查询user表中的数据，因为where没有设置其他查询条件，所以，将查询出user的全部数据。

内连接（inner join）和外连接的区别？

• 内连接：只会查询出两表连接符合条件的记录。

SELECT * FROM user1 u1 INNER JOIN user2 u2 ON u1.id = u2.id;

如上sql所示，只会查询到user1和user2关联符合条件的记录。

外连接分为左外连接、右外连接和全外连接。

• 左外连接

它的原理是：以左表为基准，去右表匹配数据，找不到匹配的用NULL补齐。其显示左表的全部记录和右表符合连接条件的记录。

• 右外连接

它的原理是：以右表为基准，去左表匹配数据，找不到匹配的用NULL补齐。其显示右表的全部记录和左表符合连接条件的记录。这正好与左外连接相反。

• 全外连接

除了显示符合连接条件的记录之外，两个表的其他数据也会显示出来。

inner join 和 left join性能谁优谁劣？

如上图，是mysql的执行顺序，我们可以看出，外查询是在内查询的基础上，进而进行查询操作的。因此，我们从理论上可以得出，内连接的执行效率是更好一些的。

但是，外连接也是会进行优化操作的，在编译优化阶段，如果左连接的结果和内连接一样，左连接查询会转换成内连接查询，但这也表明编译优化器也认为内连接的效率是更高的。

虽然从查询的结果来看一般不会有太大的区别，但是，如果左右表之间的数据差别很大，内连接的效率是明显更高的，因为左连接以左表为基准，并且会进行回表操作。

最后，给出一个结论：在外连接和内连接都可以实现需求时，建议使用内连接进行操作。

存储过程是什么，优势是什么，为什么不建议使用？

存储过程：为了完成特定功能的SQL语句集，存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

优势

• 存储过程是预编译的，因此执行速度较快；
• 存储过程在服务端执行，减少客户端的压力；
• 减少网络流量，客户端只需要传递存储过程名称和参数即可进行调用，减少了传输的数据量；
• 一次编写，任意次执行，复用的思想简单便捷；
• 安全性较高，因为在服务端执行，管理员可以对存储过程进行权限限制，能够避免非法的访问，保证数据的安全性。

缺点

• 调试麻烦，可移植性查。

这一个缺点也是存储过程在实际开发中用的不多的原因，现在开发的理念是简便。

数据库的三级范式

1NF：原子性，字段不可再分； 2NF：在满足第一范式的基础上，一个表只能说明一个事物，非主键属性必须玩去哪依赖于主键属性； 3NF：在满足第二范式的基础上，每列都与主键有直接关系，不存在传递依赖，任何非主键属性不依赖于其他非主键属性。

sql语句应该考虑哪些安全性

• 防止sql注入，对sql语句尽量进行过滤同时使用预编译的sql语句绑定变量
• 查询错误信息不要返回给用户，将错误记录到日志
• 最小用户权限设置，最好不要使用root用户连接数据库
• 定期做数据备份，避免数据丢失

什么叫sql注入，如何防止？

SQL注入是一种将SQL代码添加到输入参数中，传递到服务器解析并执行的一种攻击手法。

SQL注入攻击是输入参数未经过滤，然后直接拼接到SQL语句当中解析，执行达到预想之外的一种行为，称之为SQL注入攻击。

常见的sql注入，有下列几种：

• 数字注入

例如，查询的sql语句为：SELECT * FROM user WHERE id = 1，正常是没有问题的，如果我们进行sql注入，写成SELECT * FROM user WHERE id = 1 or 1=1，那么，这个语句永远都是成立的，这就有了问题，也就是sql注入。

• 字符串注入

字符串注入是因为注释的原因，导致sql错误的被执行，例如字符#、--。

例如，SELECT * FROM user WHERE username = 'sihai'#'ADN password = '123456'，这个sql语句'#'后面都被注释掉了，相当于SELECT * FROM user WHERE username = 'sihai' 。

这种情况我们在mybatis中也是会存在的，所以在服务端写sql时，需要特别注意此类情况。

该如何防范此类问题呢？

• 严格检查输入变量的类型和格式，也就是对相关传入的参数进行验证，尽可能降低风险。
• 过滤和转义特殊字符。
• 利用mysql的预编译机制，在Java中mybatis也是有预编译的方法的，所以可以采用这种方式避免。

MySQL中InnoDB和MyISAM的区别？

• 事务处理方面：MyISAM强调的是性能，查询的速度比InnoDB更快，但是，不支持事务，InnoDB是支持事务的。
• 外键：InnoDB支持外键，MyISAM不支持。
• 锁：InnoDB支持行锁和表锁，默认会使用行锁，而MyISAM只是支持表锁。由于行锁是能更好的支持并发操作，因此，InnoDB更加适合插入和更新操作较多的情况，而MyISAM适用于频繁查询操作。
• 全文索引：MyISAM支持全文索引，InnoDB不支持。但是，在5.6版本开始InnoDB也开始支持全文索引。
• 表主键：MyISAM允许没有主键的表存在，而InnoDB如果不存在主键，会自动生成一个6字节的主键。
• 查询表的行数差异：InnoDB不保存表的函数信息，因此，select count(*)时会扫描整个表来进行计算；MyISAM内置了计数器，只需要简单的读取保存好的行数即可。

数据库事务的四个基本要素？

事务是指一组SQL语句组成的逻辑处理单元。

ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

• 原子性（Atomicity）

原子性指的是整个数据库的事务是一个不可分割的工作单位，每一个都应该是一个原子操作。

当我们执行一个事务的时候，如果一系列的操作中，有一个操作失败了，那么，需要将这一个事务中的所有操作恢复到执行事务之前的状态，这就是事务的原子性。

下面举个简单的例子。

i++;

上面这个最简单不过的代码经常也会被问到，这是一个原子操作吗？那肯定不是，如果我们把这个代码放到一个事务中来说，当i+1出现问题的时候，回滚的就是整个代码i++（i = i + 1）了，所以回滚之后，i的值也是不会改变的。

以上就是原子性的概念。

• 一致性（consistency）

一致性是指事务将数据库从一种状态转变为下一种一致性的状态，也就是说在事务执行前后，这两种状态应该是一样的，也就是数据库的完整性约束不会被破坏。

另外，需要注意的是一致性是不关注中间状态的，比如银行转账的过程，你转账给别人，至于中间的状态，你少了500 ，他多了500，这些中间状态不关注，如果分多次转账中间状态也是不可见的，只有最后的成功或者失败的状态是可见的。

如果到分布式的一致性问题，又可以分为强一致性、弱一致性和最终一致性，关于这些概念，可以自己查查，还是很有意思的。

• 隔离性（isolation）

事务我们是可以开启很多的，MySQL数据库中可以同时启动很多的事务，但是，事务和事务之间他们是相互分离的，也就是互不影响的，这就是事务的隔离性。

• 持久性（durability）

事务的持久性是指事务一旦提交，就是永久的了，就是发生问题，数据库也是可以恢复的。因此，持久性保证事务的高可靠性。

并发事务会带来什么问题

脏读： 在不同的事务下，当前事务可以读到另外事务未提交的数据。另外我们需要注意的是默认的MySQL隔离级别是REPEATABLE READ是不会发生脏读的，脏读发生的条件是需要事务的隔离级别为READ UNCOMMITTED，所以如果出现脏读，可能就是这种隔离级别导致的。

下面我们通过一个例子看一下。

从上面这个例子可以看出，当我们的事务的隔离级别为READ UNCOMMITTED的时候，在会话A还没有提交时，会话B就能够查询到会话A没有提交的数据。

不可重复读： 是指在一个事务内多次读取同一集合的数据，但是多次读到的数据是不一样的，这就违反了数据库事务的一致性的原则。但是，这跟脏读还是有区别的，脏读的数据是没有提交的，但是不可重复读的数据是已经提交的数据。

我们通过下面的例子来看一下这种问题的发生。

从上面的例子可以看出，在A的一次会话中，由于会话B插入了数据，导致两次查询的结果不一致，所以就出现了不可重复读的问题。

我们需要注意的是不可重复读读取的数据是已经提交的数据，事务的隔离级别为READ COMMITTED，这种问题我们是可以接受的。

如果我们需要避免不可重复读的问题的发生，那么我们可以使用Next-Key Lock算法（设置事务的隔离级别为READ REPEATABLE）来避免，在MySQL中，不可重复读问题就是Phantom Problem，也就是幻像问题。

幻读本质上也属于不可重复读的情况，会话 A 读取某个范围的数据，会话 B 在这个范围内插入新的数据，会话 A 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

丢失更新： 指的是一个事务的更新操作会被另外一个事务的更新操作所覆盖，从而导致数据的不一致。在当前数据库的任何隔离级别下都不会导致丢失更新问题，要出现这个问题，在多用户计算机系统环境下有可能出现这种问题。

如何避免丢失更新的问题呢，我们只需要让事务的操作变成串行化，不要并行执行就可以。

我们一般使用SELECT ... FOR UPDATE语句，给操作加上一个排他X锁。

数据库事务的隔离级别

数据库提供了四种隔离级别。

• 读未提交数据（READ UNCOMMITTED）

允许事务读取未被其他事务提交的变更，可能有脏读、不可重复读和幻读的问题。

例如，某个时刻会话a修改了一个数据，但是未提交，此时，会话b读取了该数据，此时，a回滚了事务，这就会出现a、b数据不一致，这就是脏读。

• 读已提交数据（READ COMMITTED）

允许事务读取已经被其他事务提交的变更，可能不可重复读和幻读的问题。

例如，某个时刻会话a修改了一个数据，提交了，此时的结果是10，此时b对该数据进行了修改为20，并提交了，此时会话a再次读取该数据，发现结果是20了，因此，在同一事务中，出现了两次读取的结果不一致的现象，这就是不可重复读。

• 可重复读（REPEATABLE READ，默认隔离级别）

可重复读，从字面上的意思也能明白，就是在同一事务中读取多次，确保每次读取到的数据都是一样的，可以避免脏读和不可重复读，但是可能会出现幻读。

• 可串行化（SERIALIZABLE）

可串行化是指所有的事务都是一个接一个执行，可以避免所有的问题，但是，效果太低。

最后，再用一张图来总结一下。

在理解 MVCC 的实现原理之前，需要先带大家了解一下 版本链。

我们都知道，在 InnoDB 每个事务都有一个唯一的事务 ID（transaction id），该 ID 是在启动一个事务时申请的并且严格顺序递增。

另外，数据表中的每行数据都是有多个版本的，每次事务更新都会生成新的版本，并且把本次事务的 transaction id 赋值给这个数据版本的事务 ID（row trx_id）。

除此之外，还有一个 roll_pointer指针，该指针 ROLL_PTR 把一个数据行的所有快照版本记录连接起来。

undo log 的回滚机制也是依靠这个版本链，每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

有了上面的知识储备，所谓的 MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用读已提交（READ COMMITTD）、可重复读（REPEATABLE READ） 这两种隔离级别的事务在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

这两个隔离级别的一个很大不同就是：生成 ReadView 的时机不同，READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成一个 ReadView，而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个ReadView，数据的可重复读其实就是 ReadView 的重复使用。

MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, ...}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。

在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

• TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
• TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
• TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
  • 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
  • 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

数据库中锁机制，说说数据库中锁的类型

对于MySQL来说，锁是一个很重要的特性，数据库的锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性，这样才能保证在高并发的情况下，访问数据库的时候，数据不会出现问题。

在数据库中，lock和latch都可以称为锁，但是意义却不同。

Latch一般称为闩锁（轻量级的锁），因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差，在InnoDB引擎中，Latch又可以分为mutex（互斥量）和rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

Lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

InnoDB存储引擎中存在着不同类型的锁，下面一一介绍一下。

S or X (共享锁、排他锁)

数据的操作其实只有两种，也就是读和写，而数据库在实现锁时，也会对这两种操作使用不同的锁；InnoDB 实现了标准的行级锁，也就是共享锁（Shared Lock）和互斥锁（Exclusive Lock）。

• 共享锁（读锁）（S Lock），允许事务读一行数据。
• 排他锁（写锁）（X Lock），允许事务删除或更新一行数据。

IS or IX (共享、排他)意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB存储引擎支持一种额外的锁方式，就称为意向锁，意向锁在 InnoDB 中是表级锁，意向锁分为：

• 意向共享锁：表达一个事务想要获取一张表中某几行的共享锁。
• 意向排他锁：表达一个事务想要获取一张表中某几行的排他锁。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

• 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
• 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

另外，这些锁之间的并不是一定可以共存的，有些锁之间是不兼容的，所谓兼容性就是指事务 A 获得一个某行某种锁之后，事务 B 同样的在这个行上尝试获取某种锁，如果能立即获取，则称锁兼容，反之叫冲突。

下面我们再看一下这两种锁的兼容性。

• S or X (共享锁、排他锁)的兼容性

• IS or IX (共享、排他)意向锁的兼容性

注意： 任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁。

MySQL中锁的粒度

在数据库中，锁的粒度的不同可以分为表锁、页锁、行锁，这些锁的粒度之间也是会发生升级的，锁升级的意思就是讲当前锁的粒度降低，数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁，下面分别介绍一下这三种锁的粒度（参考自博客：https://blog.csdn.net/baolingye/article/details/102506072）。

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

特点： 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。 在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源 颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。 使用页级锁定的主要是BerkeleyDB存储引擎。

特点： 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

特点： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

比较表锁我们可以发现，这两种锁的特点基本都是相反的，而从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

了解一致性非锁定读和一致性锁定读吗？

在一个事务中查询数据时，普通的SELECT语句不会对查询的数据进行加锁，其他事务仍可以对查询的数据执行更新和删除操作。因此，InnoDB提供了两种类型的锁定读来保证额外的安全性：

• SELECT ... LOCK IN SHARE MODE
• SELECT ... FOR UPDATE

SELECT ... LOCK IN SHARE MODE: 对读取的行添加S锁，其他事物可以对这些行添加S锁，若添加X锁，则会被阻塞。

SELECT ... FOR UPDATE: 会对查询的行及相关联的索引记录加X锁，其他事务请求的S锁或X锁都会被阻塞。 当事务提交或回滚后，通过这两个语句添加的锁都会被释放。 注意：只有在自动提交被禁用时，SELECT FOR UPDATE才可以锁定行，若开启自动提交，则匹配的行不会被锁定。

一致性非锁定读(consistent nonlocking read) 是指InnoDB存储引擎通过多版本控制(MVVC)读取当前数据库中行数据的方式。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个快照。所以，非锁定读机制大大提高了数据库的并发性。

一致性非锁定读是InnoDB默认的读取方式，即读取不会占用和等待行上的锁。在事务隔离级别READ COMMITTED和REPEATABLE READ下，InnoDB使用一致性非锁定读。

然而，对于快照数据的定义却不同。在READ COMMITTED事务隔离级别下，一致性非锁定读总是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别下，则读取事务开始时的行数据版本。

下面我们通过一个简单的例子来说明一下这两种方式的区别。

首先创建一张表;

插入一条数据；

insert into lock_test values(1);

查看隔离级别；

select @@tx_isolation;

下面分为两种事务进行操作。

在REPEATABLE READ事务隔离级别下；

在REPEATABLE READ事务隔离级别下，读取事务开始时的行数据，所以当会话B修改了数据之后，通过以前的查询，还是可以查询到数据的。

在READ COMMITTED事务隔离级别下;

在READ COMMITTED事务隔离级别下，读取该行版本最新的一个快照数据，所以，由于B会话修改了数据，并且提交了事务，所以，A读取不到数据了。

InnoDB存储引擎行锁的算法了解吗？

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁。
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

Record Lock：总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock：结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。举个例子10，20，30，那么该索引可能被Next-Key Locking的区间为：

除了Next-Key Locking，还有Previous-Key Locking技术，这种技术跟Next-Key Lock正好相反，锁定的区间是区间范围和前一个值。同样上述的值，使用Previous-Key Locking技术，那么可锁定的区间为：

不是所有索引都会加上Next-key Lock的，这里有一种特殊的情况，在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock会降级为Record Lock。

接下来，我们来通过一个例子解释一下。

CREATE TABLE test (
    x INT,
    y INT,
    PRIMARY KEY(x),    // x是主键索引
    KEY(y)    // y是普通索引
);
INSERT INTO test select 3, 2;
INSERT INTO test select 5, 3;
INSERT INTO test select 7, 6;
INSERT INTO test select 10, 8;

我们现在会话A中执行如下语句；

SELECT * FROM test WHERE y = 3 FOR UPDATE

我们分析一下这时候的加锁情况。

• 对于主键x

• 辅助索引y

用户可以通过以下两种方式来显示的关闭Gap Lock：

• 将事务的隔离级别设为 READ COMMITED。
• 将参数innodb_locks_unsafe_for_binlog设置为1。

Gap Lock的作用：是为了阻止多个事务将记录插入到同一个范围内，设计它的目的是用来解决Phontom Problem（幻读问题）。在MySQL默认的隔离级别（Repeatable Read）下，InnoDB就是使用它来解决幻读问题。

幻读：是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL可能会返回之前不存在的行，也就是第一次执行和第二次执行期间有其他事务往里插入了新的行。

说说悲观锁和乐观锁

悲观锁是指在数据处理过程中，从一开始就使数据处于锁定状态，知道更改完成才释放。

MySQL中悲观锁使用以下方式：select...for update

例如：

select name from item where id = 200 for update;
insert into orders(id,item_id) values(null,100)
update item set count = count - 1 where id = 100;

我们使用select name from item where id = 200 for update;对id为200的数据进行了锁定，其他要对该条数据进行修改，必须等到该事务提交之后，否则无法修改。这样就保证了并发的安全性。

需要注意的是：select...for update语句必须在事务中使用。

悲观锁虽然能够解决并发安全的问题，但是，这种锁定会导致性能降低，加锁时间过长，并发性不好，影响系统的整体性能。因此，这种方式在实际的开发中用的很少。

乐观锁是相对悲观锁而言的，认为数据一般情况下不会出现冲突，所以在数据进行更新的时候，才会将数据锁定。

乐观锁的实现方式

• 使用数据版本（version）机制实现

该方式是在每次更新数据的时候，都要对更新的数据进行version版本+1操作。

其具体原理是：读取数据时，将此版本一同读出，之后，更新数据时，对此版本+1，每次提交数据时，如果提交的数据版本大于或等于数据库表中的版本，则可以更新，说明是最新数据，否则，不予更新，说明数据已经过期。

• 使用时间戳实现

该机制和version是类似的，也是需要再表中增加一个字段，类型使用时间类型即可。

原理：在更新数据时，检查数据库中当前的时间戳和更新前取到的时间戳，如果对比一致，就予以更新，否则不更新。

悲观锁可以在并发量不大的情况下使用，并发量大的情况下，使用乐观锁，大多数情况下都建议使用乐观锁。

索引有哪些类型？

索引有很多中类型：普通索引、唯一索引、主键索引、组合索引、全文索引，下面我们看看如何创建和删除下面这些类型的索引。

• 唯一索引：是在表上一个或者多个字段组合建立的索引，这些字段组合的值在表中不可重复。
• 非唯一索引：是在表上一个或者多个字段组合建立的索引，这些字段组合的值在表中可重复。
• 主键索引：是唯一索引的特定类型。表中创建主键时，会自动创建主键索引且只有一个。
• 组合索引：基于多个字段而创建的索引。

下面再看看索引的创建和删除的方法。

索引的创建是可以在很多种情况下进行的。

• 直接创建索引

CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length))

[UNIQUE|FULLLTEXT]：表示可选择的索引类型，唯一索引还是全文索引，不加话就是普通索引。 table_name：表的名称，表示为哪个表添加索引。 column_name(length)：column_name是表的列名，length表示为这一列的前length行记录添加索引。

• 修改表结构的方式添加索引

ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))

• 创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    [UNIQUE|FULLLTEXT] INDEX index_name (title(length))
)

前面讲的都是普通索引、唯一索引和全文索引创建的方式，但是，主键索引和组合索引创建的方式却是有点不一样的，所以单独拿出来讲一下。

组合索引创建方式

• 创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name(id,title)
)

• 修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX name_city_age (name,city,age); 

主键索引创建方式 主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`)
)

删除索引可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

(1)DROP INDEX index_name ON talbe_name (2)ALTER TABLE table_name DROP INDEX index_name (3)ALTER TABLE table_name DROP PRIMARY KEY

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。

数据库索引的实现原理

在讲解本题之前，建议大家先了解一下B+树的原理，这对后面的讲解的理解有很大的帮助，大家可以阅读一些这篇文章：面试官问你B树和B+树，就把这篇文章丢给他

基于MySQL数据库的引擎不同，索引的实现原理也是不相同的，这里主要以最主流的InnoDB和MyISAM搜索引擎举例，来说明索引的实现原理。

首先，我们需要明白一点，MyISAM 引擎的整理结构是采用主键索引和辅助索引构成的。MyISAM 引擎使用 B+ 树作为索引结构,叶节点的 data 域存放的是数据记录的地址。如下图所示。

由上图可知，MyISAM 引擎的叶子节点存放的是数据记录的地址。

接下来，再来看一下辅助索引。

在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。

辅助索引也是一颗B+树，data域保存数据记录的地址。

基于MyISAM引擎的索引检索算法： 按照B+树算法搜索索引，假若指定搜索的key存在，则可以直接取出值，然后以data域的值为地址，使用地址获取对应的数据记录。

基于MyISAM引擎实现的索引原理与基于InnoDB实现的索引原理总是分不开的，可以说是一对欢喜冤家，而两者之间最大的区别就在于，InnoDB的数据文件本身就是索引文件，怎么理解这句话呢？其实是相对于MyISAM引擎实现的索引而言的。从上分析我们可以知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录，而保存这个索引的key就是数据表的主键，因此InnoDB引擎表数据文件本身就是主索引。

总结一下，意思就是说InnoDB实现的索引叶子节点保存的是数据记录，而MyISAM引擎实现的索引的叶子节点保存的是数据记录的地址，还需要通过地址去索引对应的数据。

另外，由于基于InnoDB实现的索引的数据文件本身要按主键聚集，因此，基于InnoDB实现的索引是必须有主键存在的。

其主键策略：如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键；如果没有找到上面符合条件的列，则会生成6个字节的bigint unsigned值作为其主键。

考点：尽量在InnoDB引擎上采用自增字段做表的主键

InnoDB引擎数据文件本身是一棵B+树，非自增的主键会造成在插入新记录时数据文件为了维持B+树的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。如果不了解以上B+树的原理，建议阅读上面的B+树的文章。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

因此，采用在我们平时的开发当中，我们通常会采用自增主键，因为，MySQL的常用的版本中，默认的搜索引擎就是InnoDB，所以，采用自增主键，其实是可以保证比较好的效率的。

辅助索引

在上述MyISAM引擎索引的讲解中提到了辅助索引，MyISAM引擎中的主索引和辅助索引都是指向了同一数据记录的，而在InnoDB引擎中的表现却不一样。

InnoDB的辅助索引data域存储相应记录主键索引的值而不是地址。搜索辅助索引需要先根据辅助索引获取到主键值，再根据主键到主索引中获取到对应的数据记录。

谈谈聚簇索引和非聚簇索引

其实，在上面对索引实现原理的分析当中，已经对这两个概念有了很好的讲解了，只是没有明显的指出而已。

InnoDB引擎采用的是聚簇索引，而MyISAM引擎采用的是非聚簇索引。这两个概念的区别就在于叶节点是否存放一整行记录，我们都知道，InnoDB引擎叶子节点存放的是数据记录，而MyISAM引擎的叶子节点存放的是数据记录的地址，所以说，只要理解了基于InnoDB引擎和基于MyISAM引擎实现的索引原理，就理解了以上这两个概念。这么说是不是就很容易理解的，不就是对应了两种不同的引擎吗，是不是很简单。

如果还不是很理解，再放一张图。

左边的是聚集索引（聚簇索引），右边的是非聚集索引（非聚簇索引），这两个是不是就是基于InnoDB引擎和基于MyISAM引擎实现的索引原理。

聚簇索引的优势

• 当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。
• 当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，原因在于非聚簇索引叶子节点存放的是数据记录的地址，索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。
• 采用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的不足

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。
• 采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

最后，说明一下，如果你很好的理解了索引的原理，这上面的就会很好理解，如果理解不到位，就会发现这都是什么东西？因此，看这个的时候，先看一下上面那一题的解答。

覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

举个例子

select * from user where name = "sihai";

以上语句查询是会从数据表中进行查询的，因为，没有对user表中的name字段增加索引的操作。

alter table user add index name_index(name);

我们对user表中的name字段添加了索引。我们再使用sql语句select * from user where name = "sihai";进行查询是，就会使用覆盖索引。

因此，我们就可以非常清楚的明白，覆盖索引就是如果发现可以走索引的方式得到数据，就不采用回表查询的操作了，从而提高了查询的效率。

另外，从上面的索引原理的介绍也可以得到另外一个结论：使用覆盖索引InnoDB引擎比MyISAM引擎效果更佳，原因在于InnoDB采用聚集索引，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

最后一点，想要使用覆盖索引，就必须要使得查询能够用到索引，因此，也需要注意索引失效的场景。

建立索引的原则

• 最左前缀匹配原则，MySQL会遇到范围查询停止匹配，所以会导致组合索引失效。
• 索引列不进行函数运算。
• 注意一个表建立索引的数量，不是索引建的越多越好，维护索引也会有很大的开销。
• 尽量选择区分度高的字段作为索引。
• where语句中，经常使用的字段应该考虑建立索引。
• 分组和排序语句中，经常使用的字段应该考虑建立索引。
• 两个表关联字段考虑建立索引。
• like模糊查询中，只有右模糊查询才会使用索引。
• 在varchar 字段上建立索引时，必须指定索引长度。
• 禁止建立超过3个字段的联合索引。
• 尽量采用覆盖索引，避免回表查询。
• 索引优化的目标:至少要达到range级别，要求是ref级别，如果可以是consts最好。

索引失效的情况

• 使用组合索引，没有满足最左匹配原则，导致失效。
• or语句所有字段必须都有索引，否则失效。
• like以%开头，索引失效。
• 需要类型转换。
• where中索引列有运算。
• where中索引列使用了函数。
• 如果mysql觉得全表扫描更快时（数据少）。

一张有自增id的表，当数据记录到了20之后，删除了第18，19，20条记录，再把MySQL重启，再插入一条记录，这条记录的id是21还是18呢？

当表的引擎采用MyISAM时，是21，当表的引擎采用InnoDB时是18。

MyISAM引擎会把表自增主键的最大id记录到数据文件中，做了持久化，重启后也不会消失，而InnoDB是将自增主键的最大id记录在内存中，重启后，会丢失。

关系型数据库和非关系型数据库的区别

非关系型数据库的优势在于性能和可扩展性，非关系型数据库一般都是基于键值对的，当然也支持文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，同时，也是基于内存进行相关的操作，同时，底层也有较好的数据结构的支持，保证了其操作的效率，性能较高；另外，同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。成本低：非关系型数据库部署简单，基本都是开源软件。

关系型数据库的优势在于支持更加复杂的sql操作、事务支持和使用表结构更加易于维护。

binlog、redo log和undo log

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

redo log 记录的是数据的物理变化。

binlog 记录了数据库表结构和表数据变更，比如update/delete/insert/truncate/create。它不会记录select（因为这没有对表没有进行变更），存储着每条变更的SQL语句（当然从下面的图看来看，不止SQL，还有XID「事务Id」等等）。

主要有两个作用：复制和恢复数据

• MySQL在公司使用的时候往往都是一主多从结构的，从服务器需要与主服务器的数据保持一致，这就是通过binlog来实现的

• 数据库的数据被干掉了，我们可以通过binlog来对数据进行恢复。

undo log主要有两个作用：回滚和多版本控制(MVCC)

在数据修改的时候，不仅记录了redo log，还记录undo log，如果因为某些原因导致事务失败或回滚了，可以用undo log进行回滚

undo log 主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

这也应该容易理解，毕竟回滚嘛，跟需要修改的操作相反就好，这样就能达到回滚的目的。因为支持回滚操作，所以我们就能保证原子性。

mysql 优化思路

https://mp.weixin.qq.com/s/jtuLb8uAIHJNvNpwcIZfpA

https://www.cnblogs.com/jay-huaxiao/p/12995510.html

mysql 语法和复杂语句练习题

• 常用语法

https://www.jb51.net/article/156898.htm www.cyc2018.xyz/算法/基础/算法 - 排序.html

• 练习题

https://www.jianshu.com/p/476b52ee4f1b www.cyc2018.xyz/算法/基础/算法 - 排序.html