Mysql中有哪几种锁？
索引
聚集索引和非聚集索引
索引的创建规则
索引的最左匹配原则
简述在MySQL数据库中MyISAM和InnoDB的区别
事务管理（ACID）
Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？
MySQL中事务的隔离级别有哪几个？
乐观锁和悲观锁
多版本控制MVCC
- 快照读与当前读
Undo_Log
Redo_Log
BinLog
Redo_Log的两阶段提交
主从复制
慢查询优化

Mysql中有哪几种锁？

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

索引

Primary Key（聚集索引）：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
单列索引：单列索引即一个索引只包含单个列
组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合
Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
FULLTEXT（全文索引）：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
SPATIAL（空间索引）：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL

聚集索引和非聚集索引

聚集索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

非聚集索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

索引的创建规则

索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改的同时，索引也会进行调整和更新
避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个CHAR(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果

索引的最左匹配原则

比如索引abc_index:(a,b,c)是a,b,c三个字段的联合索引，下列sql执行时都无法命中索引abc_index；

select * from table where c = '1';
select * from table where b ='1' and c ='2';

以下三种情况却会走索引:

select * from table where a = '1';
select * from table where a = '1' and b = '2';
select * from table where a = '1' and b = '2'  and c='3';

在数据库中创建联合索引时，底层生成的B+树是会对索引从左到右进行排序的，这样方便查找。

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

简述在MySQL数据库中MyISAM和InnoDB的区别

MySQL数据库自5.5版本开始InnoDB是MySQL数据库的默认引擎(之前是MyISAM)

InnoDb：
支持ACID的事务，支持事务的四种隔离级别；支持行级锁及外键约束：因此可以支持写并发；不存储总行数；一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里）也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。
主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

MyISAM：
不支持事务，但是每次查询都是原子的；支持表级锁，即每次操作是对整个表加锁；存储表的总行数；一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。
提供了大量的特性，包括压缩表、空间数据索引等。
不支持事务。
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。
可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

MyISAM是非集聚引擎，支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.
InnoDB是集聚引擎，5.6以后才有全文索引;支持事务；它是行级锁;不会保存表的具体行数.

事务管理（ACID）

谈到事务一般都是以下四点

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）
事务前后数据的完整性必须保持一致。

隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？

Mysql的默认隔离级别就是Repeatable read。
能够解决脏读，不可重复读

脏读：
指一个事务读取了另外一个事务未提交的数据。

不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

不可重复读是读取了其他事务更改的数据，针对update delete操作
解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

幻读是读取了其他事务新增的数据，针对insert和delete操作
解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

MySQL中事务的隔离级别有哪几个？

读未提交，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
串行化，对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

数据库
set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别

设置描述
Serializable 可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read 可避免脏读、不可重复读情况的发生。（可重复读）
Read committed 可避免脏读情况发生（读已提交）。
Read uncommitted 最低级别，以上情况均无法保证。(读未提交)

乐观锁和悲观锁

悲观锁
之所以叫做悲观锁，是因为这是一种对数据的修改抱有悲观态度的并发控制方式。我们一般认为数据被并发修改的概率比较大，所以需要在修改之前先加锁。
悲观锁主要分为共享锁和排他锁：

共享锁【shared locks】又称为读锁，简称 S 锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁【exclusive locks】又称为写锁，简称 X 锁。顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。获取排他锁的事务可以对数据行读取和修改。

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。

乐观锁通过CAS机制，通过增加版本号来控制并发
如果版本号和预期的不对则不执行

多版本控制MVCC

多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

简单来说，多版本并发控制 的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。

可以认为 多版本并发控制（MVCC） 是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。不仅是MySQL，包括Oracle、PostgreSQL等其他数据库系统也都实现了MVCC，但各自的实现机制不尽相同，因为MVCC没有一个统一的实现标准，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

快照读与当前读

快照读（SnapShot Read） 是一种一致性不加锁的读，是InnoDB并发如此之高的核心原因之一。
这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据，要么是事务自身插入或者修改过的数据。

当前读：像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读：像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

当使用最高的隔离策略Serializable时，快照读会退化为当前读

在可重复读的隔离级别下，会在事务启动时，创建快照，整个事务执行期间都在使用这个视图。因此，在可重复读隔离级别下，一个事务执行过程中看到的数据，总是和这个事务在启动时看到的数据是一致的

在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

Undo_Log

undo_log是逻辑日志，用于记录数据更新之前的值。undo_log可以用于事务回滚，即事务的原子性，和多版本并发控制（MVCC）。

在事务启动时，根据事务数组判断当前数据版本是否可见。如果不可见，根据undo_log，寻找前一数据版本，直到可见的数据版本，并根据该版本来创建快照。

Redo_Log

redo_log（重做日志）是InnoDB引擎特有的，有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe(崩溃安全恢复)。

BinLog

binlog（归档日志）是MySQL的Server层实现的，所有引擎都可以使用，用于归档。

Redo_Log的两阶段提交

Redo_Log在提交的时候有Prepare和Commit两个状态

通过Redo_Log可以对异常的事务进行恢复

假设现在有个事务是 update a = b where id = 1
当更新完成后，首先会将新值更新到内存，然后写入Redo_Log，再写入BinLog，如果在时刻A发生了异常回滚，由于redoLog提交了，但是binLog未提交，就会导致binLog少一条更新，redoLog就会将该值赋b，但是在binLog中该值还是原值

通过引入两阶段提交，只有在binLog完成后才提交，当出现异常时，在时刻A大家都未提交，直接回滚到原值
在时刻B异常，大家都写入成功，可以直接事务恢复

主从复制

Binlog 日志主要作用是数据恢复和主从复制。本身就是二进制格式的日志文件，网络传输无需进行协议转换。MySQL 集群的高可用，负载均衡，读写分离等功能都是基于Binlog 来实现的。

MySQL 主从复制涉及到三个线程：
一个在主节点的线程：log dump thread
从库会生成两个线程：一个 I/O 线程，一个 SQL 线程
如下图所示:

主库会生成一个 log dump 线程,用来给从库 I/O 线程传 Binlog 数据。
从库的 I/O 线程会去请求主库的 Binlog，并将得到的 Binlog 写到本地的 relay log (中继日志)文件中。
SQL 线程,会读取 relay log 文件中的日志，并解析成 SQL 语句逐一执行。

relay log 是怎么产生的呢？
从服务器 I/O 线程将主服务器的 Binlog 日志读取过来，解析到各类 Events 之后记录到从服务器本地文件，这个文件就被称为 relay log。然后 SQL 线程会读取 relay log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。中继日志充当缓冲区，这样 master 就不必等待 slave 执行完成才发送下一个事件。

慢查询优化

1、对于一下大的连接，可以进行拆分，分解关联查询
2、经常查询的字段，建立索引
3、对于联合索引，查看是否满足最左前缀原则
4、减少查询字段，只对需要的字段进行查询

Java 面试汇总

Mysql常见问题