mysql知识整理

第一部分：通用模块。
此部分对 MySQL 整体概念、执行流程、数据库引擎、查询缓存、表空间、回表查询、数据类型间的区别、内存表、临时表、删除表的 n 种方式、枚举、视图、数据恢复等相关知识点对应的面试题进行解答。

1，windows中安装mysql

在解压后的文件路径下的bin目录下执行mysqld -install mysql
如果要删除则执行命令sc delete mysql
启动：net start mysql
关闭：net stop mysql

2，linux中安装mysql
启动：service mysqld start
关闭：service mysqld stop

安装参考：
Centos 安装MySQL5.7步骤
CentOS7安装MySQL8.0图文教程

配置外部访问参考：
mysql error1103 Host * is not allowed to connect to this MySQL server解决方法

3，数据库基本操作

（1）环境变量配置： 在path中添加mysql安装目录下的bin目录
（2）进入mysql数据库：mysql -h localhost -u root -p -b
　　　　　　-h 服务器(本地、指定服务器IP的)[本地localhost可以省略]
　　　　　　-u 用户名(我们用的是root用户：超级管理员)
　　　　　　-p 用户密码(不需要再它后面写密码，密码是回车之后写的)
　　　　　　-b 蜂鸣器(当命令敲错了，报错时提示的声音)
（3）进入MySQL数据库以后的命令
　　　　注意：
　　　　1> 每一行命令结束了以后，请使用 ; 或 \g 来结束，否则命令可以一直写下去
　　　　2> 在MySQL的命令中，如果一行命令没有敲完，就回车了，它会继续执行下去，前提是必须是一条完整的命令
　　　　3> 退出敲错的命令、或不想继续执行下去的命令时，用\c 或者 Ctrl+c
　　　　4> 我们要想让数据竖起来显示，在命令后用\G，有时候一行盛不下了会乱，\G可以解决这个问题
　　　　5> \s 可以显示当前服务器的配置
　　　　6> help 查看所有可以使用的快捷命令
　　　　7> 如果在命令行中遇到了单引号，则MySQL认为你要输入一段字符串，所以，它是必须使用结束的单引号，可以跳出字符串输入状态；

（4）退出数据库服务器
　　　　\q 、 exit、 quit　　三者皆可
（5）创建数据库
　　　　create database 数据库名;
（6）删除数据库
　　　　drop database 数据库名;
（7）查看已创建的数据库
　　　show databases;
（8）使用一个数据库
　　　use 数据库名;
（9）查看当前我们所使用的数据库
　　　select database();
（10）查看建库语句
　　　show create database 数据库名;
　　　说明：通过该命令，我们可以看到所建数据库的建库语句、编码类型;
　　　注意：
　　　　1）MySQL数据库中命令不区分大小写。
　　　　2）每创建一个数据库，就会在data目录下创建一个以此数据库名称命名的文件夹。
　　　3）在Windows下，数据库名称也是不区分大小写的，但在Linux下，数据库名称严格区分大小写。

4，数据库中的基本概念

（1）数据库、表相关
数据库：数据库中存放的是表，一个数据库中可以存放多个表
表：表是用来存放数据的。
关系：两个表的公共字段
行：也称记录，也称实体
列：也称字段，也称属性

5，MYSQL的执行流程

mysql主要分为Server层和存储引擎层
Server层： 主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog日志模块。
存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog 模块。
InnoDB 5.5.5版本作为默认引擎。

连接器
主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存
连接建立后，执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中，Key是查询预计，Value是结果集。如果缓存key被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。
Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了，往往带来的效果并不好，对于不经常更新的数据来说，使用缓存还是可以的，Mysql 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器
mysql 没有命中缓存，那么就会进入分析器，分析器主要是用来分析SQL语句是来干嘛的，分析器也会分为几步：
第一步，词法分析，一条SQL语句有多个字符串组成，首先要提取关键字，比如select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
第二步，语法分析，主要就是判断你输入的sql是否正确，是否符合mysql的语法。
完成这2步之后，mysql就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器
优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器
当选择了执行方案后，mysql就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

查询语句
当选择了执行方案后，mysql就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

6，表空间

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间

查看innodb的表空间：
select * from information_schema.INNODB_TABLESPACES;

（1）系统表空间（System Tablespace）
innodb系统表空间包含innodb数据字典（innodb相关对象的元数据），同时，双写缓冲（doublewrite buffer）、改变缓冲（change buffer）和undo日志（undo logs）等也存储于系统表空间中。此外，系统表空间
也包含用户在改表空间创建的表和索引等数据。由于系统表空间可以存储多张表，因此，其为一个共享表空间。系统表空间由一个或多个数据文件组成，默认情况下，其包含一个叫ibdata1的系统数据文件，位于mysql
数据目录下。系统表空间数据文件的大小和数目由innodb_data_file_path启动选项控制。

（2）表文件表空间（File-Per-Table Tablespaces）

表文件表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表文件表空间中。否则，innodb将被创建于系统表空间中。
每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表文件表空间支持动态（DYNAMIC)和压缩（commpressed)行格式。

（3）通用表空间（General Tablespaces）
通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。
通过create table tab_name … tablespace [=] tablespace_name或alter table tab_name tablespace [=] tablespace_name语法将其添加与通用表空间内。

（4）undo表空间（undo tablespace）
undo表空间由一个或多个包含undo日志的文件组成。innodb_undo_tablespace配置选项控制undo表空间的数目。undo表空间创建于innodb_undo_directory配置选项确定的位置，该选项典型被用于将undo日志放于不同的
存储设备上。如果该选项没有确定任何路径，undo表空间则备创建于mysql通过datadir确定的数据目录下。

（5）临时表空间（Temporary Tablespace）
用户创建的临时表和磁盘内部临时表创建于共享临时表空间中。innodb_temp_data_file选项确定临时表空间数据文件的相对路径、名字、大小和属性等。如果该选项未确定任何值，默认情况下，系统将在
innodb_data_home_dir确定的目录下创建一个叫ibtmp1的自动扩展的数据文件，该文件将稍大于12m。
mysql服务器正常关闭或异常终止初始化时，临时表空间将被移除，并且，mysql服务器每次启动时会被重新创建。当临时表空间被创建时，其被赋予一个动态产生的空间ID（space ID）。如果不能创建临时表空间，
mysql服务器启动将被拒绝。mysql服务器异常终止的情况下，临时表空间将不被移除。这种情况下，DBA能手工移除临时表空间或重启mysql服务器，重启服务器过程中，将自动移除和重新创建临时表空间。
临时表空间并不能存储于裸设备。
这里既然说到了innodb_data_home_dir，那么，就说说这个选项，该选项确定innodb系统表空间数据文件目录路径的共同部分。innodb_file_per_table开启时，该选项设置并不影响表文件表空间的位置。该选项默认值
为mysql数据目录。如果你将该选项设置为空串，那么，你可以为innodb_data_file_path设置一个绝对路径值。此外，当为innodb_data_home_dir指定一个值时，需要在尾部添加一个斜杠。

7，内存表与临时表

内存表，就是放在内存中的表，所使用内存的大小可通过My.cnf中的max_heap_table_size指定，如max_heap_table_size=1024M，内存表与临时表并不相同，临时表也是存放在内存中，临时表最大所需内存需要通过tmp_table_size = 128M设定。当数据超过临时表的最大值设定时，自动转为磁盘表，此时因需要进行IO操作，性能会大大下降，而内存表不会，内存表满后，会提示数据满错误。

临时表和内存表都可以人工创建，但临时表更多的作用是系统自己创建后，组织数据以提升性能，如子查询，临时表在多个连接之间不能共享。这里只讨论内存表

创建表是，用engine=heap可创建（mysql5.5中已经不支持type，以后都用engine，形成习惯）。

create table test
(
   id int unsigned not null auto_increment primary key,
    state char(10),
    type char(20),
    date char(30)
)ENGINE=MEMORY DEFAULT CHARSET=utf8;

内存表的特性
（1）内存表的表定义是存放在磁盘上的，扩展名为.frm， 所以重启不会丢失。
（2）内存表的数据是存放在内存中的，所以重启会丢失数据。
（3）内存表使用一个固定的记录长度格式。
（4）内存表不支持BLOB或TEXT列，比如varchar与text字段就不会被支持。
（5）内存表支持AUTO_INCREMENT列和对可包含NULL值的列的索引（网上大多说不支持，这是错误的）。内存表支持大于(>) 小于( <)操作，网上也说不支持。
（6）mysql重启后，主键、自增、索引仍然存在，只是数据丢失。这也是对网上的一些错误文字纠正。
（7）内存表表在所有客户端之间共享（就像其它任何非TEMPORARY表）。
（8）MEMORY存储引擎执行HASH和BTREE索引。你可以通过添加一个如下所示的USING子句为给定的索引指定一个或另一个：

CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;

（9）内存表初始化，可以使用–init-file来初始化，避免重启mysql后数据被清空。比如–init-file="/data/mysql/init.sql", init.sql格式为：

use db_test;
select *** into m_table;

（10）在数据库复制时，如果主机当掉，则会在binLog中自动加入delete from [内存表]，将slave的数据也删除掉，以保证两边的数据一致性。
（11）内存表不支持事务。
（12）内存表是表锁，当修改频繁时，性能可能会下降。

内存表的使用
内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库，但是使用上受到一些限制。
（1）heap对所有用户的连接是可见的，这使得它非常适合做缓存。
（2）仅适合使用的场合。heap不允许使用xxxTEXT和xxxBLOB数据类型。注：操作符 “<=>” 说明：NULL-safe equal.这个操作符和“=”操作符执行相同的比较操作，不过在两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。
（3）一旦服务器重启，所有heap表数据丢失，但是heap表结构仍然存在，因为heap表结构是存放在实际数据库路径下的，不会自动删除。重启之后，heap将被清空，这时候对heap的查询结果都是空的。
（4）如果heap是复制的某数据表，则复制之后所有主键、索引、自增等格式将不复存在，需要重新添加主键和索引，如果需要的话。
（5）对于重启造成的数据丢失，有以下的解决办法：
在任何查询之前，执行一次简单的查询，判断heap表是否存在数据，如果不存在，则把数据重新写入，或者DROP表重新复制某张表。这需要多做一次查询。不过可以写成include文件，在需要用该heap表的页面随时调用，比较方便。
对于需要该heap表的页面，在该页面第一次且仅在第一次查询该表时，对数据集结果进行判断，如果结果为空，则需要重新写入数据。这样可以节省一次查询。
更好的办法是在mysql每次重新启动时自动写入数据到heap，但是需要配置服务器，过程比较复杂，通用性受到限制。

8，数据库引擎
mysql常见引擎

MyISMA是MySQL的默认存储引擎。MyISMA不支持事务，不支持外键，优势是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用MyISMA引擎。比较适合Web、数据仓储等场景。

InnoDB存储引擎提供具有提交、回滚和崩溃恢复的事务安全，支持外键。对数据一致性要求比较高或更新比较频繁的的应用可以选择InnoDB。比较适合类似计费和财务系统等准确度要求比较高的系统。

MEMORY存储引擎-内存数据库，服务重启数据会丢失。适用于那些内容变化不频繁的代码表（常量表），或者作为统计结果的中间结果表。修改的数据不会写入磁盘。

MERGE存储引擎是一组MyISMA表的组合，这些MyISMA表的结构必须完全相同，MERGE表本身没有数据，对MERGE表的操作实际上是对内部的MyISMA表进行的。较适合数据仓储。

-- 查看数据库支持的存储引擎
show engines;

第二部分：索引模块。

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 Hash索引和B_TREE索引。

索引可以提高查询的速度，但是创建和维护索引需要耗费时间，同时也会影响插入的速度，如果需要插入大量的数据时，最好是先删除索引，插入数据后再建立索引。

B_TREE 索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，即：MyISAM索引文件和数据文件是分离的，MyISAM的索引文件仅仅保存数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的。

InnoDB引擎也使用B+Tree作为索引结构，但是InnoDB的数据文件本身就是索引文件，叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这种索引叫做“聚焦索引”。InnoDB的辅助索引的data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。InnoDB的索引实现后，不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。在Innodb中也不建议使用非单调的字段作为主键，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，建议使用自增字段作为主键。

索引相关知识查看此博客

第三部分：事务模块。
事务决定了程序的稳定性，在 MySQL 中的地位也是首屈一指。
1，事务的四大特性：（ACID）
原子性（Atomicity）：操作这些指令时，要么全部执行成功，要么全部不执行。只要其中一个指令执行失败，所有的指令都执行失败，数据进行回滚，回到执行指令前的数据状态。
一致性（Consistency）：事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定。
隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
持久性（Durability）：当事务正确完成后，它对于数据的改变是永久性的。

2，并发事务导致的问题
（1）第一类丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖。
（2）脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
（3）幻读也叫虚读：一个事务执行两次查询，第二次结果集包含第一次中没有或某些行已经被删除的数据，造成两次结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。幻读是事务非独立执行时发生的一种现象。
（4）不可重复读：一个事务两次读取同一行的数据，结果得到不同状态的结果，中间正好另一个事务更新了该数据，两次结果相异，不可被信任。
（5）第二类丢失更新：是不可重复读的特殊情况。如果两个事物都读取同一行，然后两个都进行写操作，并提交，第一个事物所做的改变就会丢失。

3，事务隔离级别
（1）未提交读(Read Uncommitted)：允许脏读，其他事务只要修改了数据，即使未提交，本事务也能看到修改后的数据值。也就是可能读取到其他会话中未提交事务修改的数据。并发事务导致的问题一个也避免不了
（2）提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。可避免脏读的发生。
（3）可重复读(Repeated Read)：可重复读。无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响。可避免脏读、不可重复读的发生。MySQL数据库(InnoDB引擎)默认使用可重复读（ Repeatable read)
（4）串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。可避免脏读、不可重复读、幻读的发生。

第四部分：锁。
锁包括：全局锁、表锁、行锁、死锁、乐观锁、悲观锁等，不同的数据库引擎支持的锁支持粒度也是不同的，此部分的面试题，让你彻底搞定锁相关的面试题。

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

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MyISAM表锁
MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；

MyISAM的锁调度
前面讲过，MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后 到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原 因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

上面已经讨论了写优先调度机制带来的问题和解决办法。这 里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语 句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每 一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

InnoDB锁
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。
表锁
略

行锁

共享锁（s）：又称读锁。
允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
排他锁（Ｘ）：又称写锁。
允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据。
排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from emp where empid > 100 for update;
1
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需 要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

第五部分：日志。
日志看似不起眼，却是 MySQL 主备同步和容灾恢复以及问题排除的关键，当然也是面试中必问的问题，这部分会对不同的数据库引擎中的重点日志，进行详细的介绍。

1：重做日志（redo log）
作用：
　　确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

内容：
　　物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

什么时候产生：
　　事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

什么时候释放：
　　当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

对应的物理文件：
　　默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2
　　innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
　　innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2
关于文件的大小和数量，由以下两个参数配置：
　　innodb_log_file_size 重做日志文件的大小。
　　innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

其他：

很重要一点，redo log是什么时候写盘的？前面说了是在事物开始之后逐步写盘的。

之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，原因就是，重做日志有一个缓存区Innodb_log_buffer，Innodb_log_buffer的默认大小为8M(这里设置的16M),Innodb存储引擎先将重做日志写入innodb_log_buffer中。

然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘
　　（1）Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件。
　　（2）每个事务提交时会将重做日志刷新到重做日志文件。
　　（3）当重做日志缓存可用空间 少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer到重做日志文件是Master Thread线程的定时任务。

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。

另外引用《MySQL技术内幕 Innodb 存储引擎》（page37）上的原话：
　　即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。
　　这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

2：回滚日志（undo log）

作用：
　　保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容：
　　逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

什么时候产生：
　　事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

什么时候释放：
　　当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

对应的物理文件：
　　MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。

MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数

如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

关于MySQL5.7之后的独立undo 表空间配置参数如下：

innodb_undo_directory = /data/undospace/ –undo独立表空间的存放目录 innodb_undo_logs = 128 –回滚段为128KB innodb_undo_tablespaces = 4 –指定有4个undo log文件

如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。

其他：
　　undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redolog的产生。

默认情况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。

因此共享表空间可能会变的很大，默认情况下，也就是undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。

因此，mysql5.7之后的“独立undo 表空间”的配置就显得很有必要了。

3：二进制日志（binlog）
作用：
　　用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
　　用于数据库的基于时间点的还原。

内容：
　　逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。
　　但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

在使用mysqlbinlog解析binlog之后一些都会真相大白。

因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

什么时候产生：

事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。

这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

什么时候释放：

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

对应的物理文件：

配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

对于每个binlog日志文件，通过一个统一的index文件来组织。

其他：
　　二进制日志的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同

作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。

内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句

另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

4：错误日志（errorlog）
　错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。
　　指定日志路径两种方法:
　　　　编辑my.cnf 写入 log-error=[path]
　　　　通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &

显示错误日志的命令（如下图所示）

5：一般查询日志（general log）

记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来 ，记录的格式为 {Time ，Id ，Command，Argument }。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。 因此，Mysql默认是把General log关闭的。

查看日志的存放方式：show variables like ‘log_output’;

如果设置mysql> set global log_output=’table’ 的话，则日志结果会记录到名为gengera_log的表中，这表的默认引擎都是CSV
　　如果设置表数据到文件set global log_output=file;
　　设置general log的日志文件路径：
　　　　set global general_log_file=’/tmp/general.log’;
　　　　开启general log： set global general_log=on;
　　　　关闭general log： set global general_log=off;

然后在用：show global variables like ‘general_log’

6：慢查询日志（slow query log）
慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。
　　1. 查看慢查询时间：
　　show variables like “long_query_time”;默认10s

　2. 查看慢查询配置情况：
　　show status like “%slow_queries%”;

3. 查看慢查询日志路径：
　　show variables like “%slow%”;

4. 开启慢日志
   

查看已经开启：

第六部分：MySQL 操作命令和内置函数。
MySQL 的操作命令，对于程序员或者 DBA 来说也是必须具备的一项技能，比如，用户和权限的创建、数据库相关信息的查询等，都离不开对 MySQL 命令行的掌握。对内置函数的掌握程度，代表了你对 MySQL 的掌握程度，善用 MySQL 提供的内置函数，会让你有事半功倍的效果，内置函数也是笔试中必考的面试题。

1：数学函数
数学函数主要用于处理数字，包括整型、浮点数等。

函数	作用
ABS(x)	返回x的绝对值SELECT ABS(-1) – 返回1
ROUND(x,y)	保留x小数点后y位的值，但截断时要进行四舍五入SELECT ROUND(1.23456,3) – 1.235
CEIL(x),CEILING(x)	返回大于或等于x的最小整数SELECT CEIL(1.5) – 返回2
FLOOR(x)	返回小于或等于x的最大整数SELECT FLOOR(1.5) – 返回1

2：字符串函数
字符串函数是MySQL中最常用的一类函数，字符串函数主要用于处理表中的字符串。

函数	作用
CONCAT(s1,s2,…)	将字符串s1,s2等多个字符串合并为一个字符串SELECT CONCAT(‘12’,‘34’) – 1234
UPPER(s),UCAASE(S)	将字符串s的所有字母变成大写字母SELECT UPPER(‘abc’) – ABC
LOWER(s),LCASE(s)	将字符串s的所有字母变成小写字母SELECT LOWER(‘ABC’) – abc
LEFT(s,n)	返回字符串s的前n个字符SELECT LEFT(‘abcde’,2) – ab
RIGHT(s,n)	返回字符串s的后n个字符SELECT RIGHT(‘abcde’,2) – de
TRIM(s)	去掉字符串s开始和结尾处的空格
REVERSE(s)	将字符串s的顺序反过来SELECT REVERSE(‘abc’) – cba

3：日期时间函数
MySQL的日期和时间函数主要用于处理日期时间。

函数	作用
CURDATE(),CURRENT_DATE()	返回当前日期SELECT CURDATE()->2014-12-17
CURTIME(),CURRENT_TIME	返回当前时间SELECT CURTIME()->15:59:02
NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP()	返回当前日期和时间SELECT OW()->2014-12-17 15:59:02
DATE_FORMAT(d,f)	按表达式f的要求显示日期dSELECT DATE_FORMAT(‘2011-11-11 11:11:11’,’%Y-%m-%d %r’)->2011-11-11 11:11:11 AM

4：条件判断函数

1、IF(expr,v1,v2)函数

如果表达式expr成立，返回结果v1；否则，返回结果v2。

SELECT IF(1 > 0,'正确','错误')    
->正确

2、IFNULL(v1,v2)函数

如果v1的值不为NULL，则返回v1，否则返回v2。

SELECT IFNULL(null,'Hello Word')
->Hello Word

3、CASE

语法1：

CASE 
　　WHEN e1
　　THEN v1
　　WHEN e2
　　THEN e2
　　...
　　ELSE vn
END

CASE表示函数开始，END表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。

SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0

语法2：

CASE expr 
　　WHEN e1 THEN v1
　　WHEN e1 THEN v1
　　...
　　ELSE vn
END

如果表达式expr的值等于e1，返回v1；如果等于e2,则返回e2。否则返回vn。

SELECT CASE 1 
　　WHEN 1 THEN '我是1'
　　WHEN 2 THEN '我是2'
ELSE '你是谁'

5：系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

函数	作用
VERSION()	返回数据库的版本号SELECT VERSION()->5.0.67-community-nt
CONNECTION_ID()	返回服务器的连接数
DATABASE()、SCHEMA	返回当前数据库名
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER	返回当前用户
CHARSET(str)	返回字符串str的字符集
COLLATION(str)	返回字符串str的字符排列方式
LAST_INSERT_ID()	返回最近生成的AUTO_INCREMENT值

6：加密函数
加密函数是MySQL用来对数据进行加密的函数。
1、PASSWORD(str)
　　该函数可以对字符串str进行加密，一般情况下，PASSWORD(str)用于给用户的密码加密。

SELECT PASSWORD('123')
    ->*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

2、MD5
　　MD5(str)函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密。

SELECT md5('123')
    ->202cb962ac59075b964b07152d234b70

3、ENCODE(str,pswd_str)与DECODE(crypt_str,pswd_str)

ENCODE函数可以使用加密密码pswd_str来加密字符串str，加密结果是二进制数，需要使用BLOB类型的字段保存。该函数与DECODE是一对，需要同样的密码才能够解密。

SELECT ENCODE('123','xxoo')
    ->;vx
SELECT DECODE(';vx','xxoo')
    ->123

7：聚合函数

Count()
Sum()
Max()
Min()
Avg()
Group_concat()

8：其他函数

1、格式化函数FORMAT(x,n)

FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。

SELECT FORMAT(3.1415926,3)
    ->3.142

2、不同进制的数字进行转换

ASCII(s) 返回字符串s的第一个字符的ASCII码；
BIN（x） 返回x的二进制编码；
HEX(x) 返回x的十六进制编码；
OCT(x) 返回x的八进制编码；
CONV(x,f1,f2) 返回f1进制数变成f2进制数；

3、IP地址与数字相互转换的函数

INET_ATON(IP)函数可以将IP地址转换为数字表示；IP值需要加上引号；
INET_NTOA(n)函数可以将数字n转换成IP形式。

SELECT INET_ATON('192.168.0.1')
    ->3232235521
SELECT INET_NTOA(3232235521)
    ->192.168.0.1

4、加锁函数和解锁函数

GET_LOCK(name,time)函数定义一个名称为nam、持续时间长度为time秒的锁。如果锁定成功，则返回1；如果尝试超时，则返回0；如果遇到错误，返回NULL。
RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，则返回1；如果尝试超时，返回0了如果解锁失败，返回NULL；
IS_FREE_LOCK(name)函数判断是否已使用名为name的锁定。如果使用，返回0，否则，返回1；

SELECT GET_LOCK('MySQL',10)
    ->1    (持续10秒)
SELECT IS_FREE_LOCK('MySQL')
    ->1    
SELECT RELEASE_LOCK('MySQL')
    ->1

5、重复执行指定操作的函数

BENCHMARK(count.expr)函数将表达式expr重复执行count此，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。

SELECT BENCHMARK(10000,NOW())
    ->0    返回系统时间1万

6、改变字符集的函数

CONVERT(s USING cs)函数将字符串s的字符集变成cs。

SELECT CHARSET('ABC')
    ->utf-8    
 
SELECT CHARSET(CONVERT('ABC' USING gbk))
    ->gbk

7、转换数据类型

CAST(x AS type)
CONVERT(x,type)
　　这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER。

SELECT CAST('123' AS UNSIGNED INTEGER) + 1
    ->124
 
SELECT '123' + 1
    ->124 其实MySQL能默认转换
 
SELECT CAST(NOW() AS DATE)
　　->2014-12-18

第七部分：性能优化和分布式。
性能优化和分布式是面试中决定你高度的关键指标，其中性能优化包括了慢查询的分析和处理，对分布式的掌握体现了你的技术深度。
1：性能优化

参考博客：
MySQL 面试，必须掌握的 8 个知识点
事务相关
MySQL常见面试题
Mysql中的锁机制
MyISAM与InnoDB 的区别（9个不同点）
MySQL中的几种日志了解
MySQL 常用内置函数与所有内置函数
MySQL基本概念以及简单操作
MySQL之执行流程
浅谈mysql中各种表空间（tablespaces）的概念
MySQL内存表的特性与使用介绍
Mysql的性能优化
MySql性能优化
Mysql性能优化一
Mysql性能优化二