数据库基础

1.主键、外键、超键、候选键超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。候选键：是最小超键，即没有冗余元素的超键。主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。外键：在一个表中存在的另一个表的主键称此表的外键。

2.触发器

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

mysql> DELIMITER ||
mysql> CREATE TRIGGER demo BEFORE DELETE
    -> ON users FOR EACH ROW
    -> BEGIN
    -> INSERT INTO logs VALUES(NOW());
    -> INSERT INTO logs VALUES(NOW());
    -> END
    -> ||
Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER ;

限制： (1)触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。 (2)不能在触发器中使用以显示或隐式方式开始或结束事务的语句，如START TRANS-ACTION,COMMIT或ROLLBACK。

注意事项： MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作，如果对事务表进行的操作，如果出现错误，那么将会被回滚，如果是对非事务表进行操作，那么就无法回滚了，数据可能会出错。触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过多的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。

和存储过程与触发器的区别：触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发(激活)执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

3.存储过程存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

调用： (1)可以用一个命令对象来调用存储过程。 (2)可以供外部程序调用，比如：java程序。

create procedure insertInnoDb()
begin
set @i = 1;
while @i <= 1000000
do
insert into testinnodb(name) values(concat("wy", @i));
set @i = @i + 1;
end while;
end

调用存储过程。对于存储引擎为InnoDB的表。默认开启了autocommit = 1。调用存储过程时，需要先关闭，调用存储过程，再开启。

set autocommit = 0;
call insertInnoDb;
set autocommit = 1;

优点： (1)存储过程是预编译过的，执行效率高。 (2)存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。 (3)安全性高，执行存储过程需要有一定权限的用户。 (4)存储过程可以重复使用，可减少数据库开发人员的工作量。

缺点：移植性差

和函数的区别： (1)存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据。 (2)存储过程声明用procedure，函数用function。 (3)存储过程不需要返回类型，函数必须要返回类型。 (4)存储过程可作为独立的pl-sql执行，函数不能作为独立的pl-sql执行，必须作为表达式的一部分。 (5)存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值。 (6)sql语句（DML或SELECT)中不可调用存储过程，而函数可以。

4.视图

是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。

优点： (1)对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。 (2)用户通过简单的查询可以从复杂查询中得到结果。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据。 (3)维护数据的独立性，试图可从多个表检索数据。 (4)对于相同的数据可产生不同的视图。

缺点： (1)性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么就无法更改数据。 (2)视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by则对视图再次order by将被覆盖。

5.游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

6.drop、truncate、delete区别

(1)执行速度：drop > truncate > delete。truncate比delete速度快，且使用的系统和事务日志资源少。 (2)数据影响：delete语句为DML（data maintain Language)，DELETE语句执行删除的过程是每次从表中删除一行，这个操作会被放到rollback segment中，事务提交后才生效。如果有相应的trigger，执行的时候将被触发。truncate、drop是DDL（data define language)，操作立即生效，原数据不放到rollback segment中，不能回滚，删除行是不能恢复的。通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。在没有备份情况下，谨慎使用drop与truncate要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。 (3)所占空间：当表被TRUNCATE后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。 (4)应用范围：truncate只能对table；delete可以是table和view。 (5)影响范围：truncate与不带where的delete只删除数据，而不删除表的结构（定义）。drop语句将删除表的结构，被依赖的约束（constrain)触发器（trigger)索引（index)。依赖于该表的存储过程/函数将被保留，但其状态会变为invalid。TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子（恢复1）。如果想保留标识计数值，请改用DELETE。 (6)对于由外键约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHERE子句的DELETE语句。由于TRUNCATE TABLE不记录在日志中，所以它不能激活触发器。

7.临时表

临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。可以手工删除。

CREATE TEMPORARY TABLE tmp_table (
NAME VARCHAR (10) NOT NULL,
time date NOT NULL
);

DROP TEMPORARY TABLE IF EXISTS temp_tb;

8.非关系型数据库和关系型数据库区别，优势比较？

非关系型数据库的优势：NOSQL是基于键值对的，而且不需要经过SQL层的解析，所以性能非常高。同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。关系型数据库的优势：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。事务支持使得对于安全性能很高的数据访问要求得以实现。

9.数据库范式

一般一个数据库设计符合3NF或BCNF就可以了。

(1)1NF：每一列属性都是不可再分的属性值，确保每一列的原子性。 (2)2NF：满足2NF的前提是必须满足1NF。此外，关系模式需要包含两部分内容，一是必须有一个（及以上）主键；二是没有包含在主键中的列必须全部依赖于全部主键，而不能只依赖于主键的一部分而不依赖全部主键。非主键列全部依赖于部分主键，非主键列部分依赖于全部主键，非主键列部分依赖于部分主键都是不符合2NF的。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。 (3)3NF：满足3NF的前提是必须满足2NF。另外关系模式的非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列m既依赖于全部主键，又依赖于非主键列n的情况。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。 (4)BCNF:符合3NF，并且主属性不依赖于主属性。BC范式既检查非主属性，又检查主属性。当只检查非主属性时，就成了第三范式。满足BC范式的关系都必然满足第三范式。还可以这么说：若一个关系达到了第三范式，并且它只有一个候选码，或者它的每个候选码都是单属性，则该关系自然达到BC范式。

10.什么是内连接、外连接、交叉连接、笛卡尔积

内连接：只连接匹配的行。inner join。左外连接：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行。left join。右外连接：包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。right join。全外连接：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。 full outer join。交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配。cross join。

11.varchar和char

(1)char的长度是不可变的，而varchar的长度是可变的。定义一个char[10]和varchar[10]。如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。 (2)char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找。char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。varchar是以空间效率为首位。 (3)char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。 (4)两者的存储数据都非unicode的字符数据。 (5)对于char来说，最多能存放的字符个数为255，和编码无关；对于varchar来说，最多能存放的字符个数为65532。

12.SQL语言分类

(1)数据查询语言DQL：基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块。 (2)数据操纵语言DML：主要有三种形式：INSERT、UPDATE、DELETE。 (3)数据定义语言DDL：Create Table,Alter Table,Drop Table, Craete/Drop Index等。用来创建数据库中的各种对象—–表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER。DDL操作是隐性提交的！不能rollback。 (4)数据控制语言DCL：用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：GRANT、ROLLBACK WORK TO SAVEPOINT（回退到某一点）、ROLLBACK、COMMIT WORK

13.通配符

%百分号通配符:表示任何字符出现任意次数(可以是0次)。 _下划线通配符:表示只能匹配单个字符，不能多也不能少，就是一个字符。

注意事项：注意大小写：在使用模糊匹配时,也就是匹配文本时,mysql是可能区分大小的,也可能是不区分大小写的,这个结果是取决于用户对MySQL的配置方式。注意尾部空格,”%yves”是不能匹配”heyves “这样的记录的。注意NULL,%通配符可以匹配任意字符,但是不能匹配NULL。

14.count(*)、count(1)、count(column)的区别

count()对行的数目进行计算，包含NULL。 count(column)对特定的列的值具有的行数进行计算，不包含NULL值。 count(1)这个用法和count()的结果是一样的。

性能问题： (1)任何情况下SELECT COUNT() FROM tablename是最优选择。尽量减少SELECT COUNT() FROM tablename WHERE COL = ‘value’ 这种查询。杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’的出现。 (2)如果表没有主键,那么count(1)比count()快。如果有主键,那么count(主键,联合主键)比count()快。如果表只有一个字段,count()最快。 (3)count(1)跟count(主键)一样,只扫描主键。count()跟count(非主键)一样,扫描整个表。明显前者更快一些。

15.分页

(1)limit用法：

SELECT * FROM table LIMIT [offset,] rows

rows OFFSET offset。解释：筛选出结果的第offset行后的rows行。如果offset不填也是可以的，默认为0。

select * from tbl_user limit 1,5; // 跳过1行，从第2行开始的5行
select * from tbl_user limit 6; // 从第1行开始的6行
select * from tbl_user limit 5 offset 1; // 跳过1行，从第2行开始的5行

(2)物理分页与逻辑分页

物理分页多次访问数据库，负担大。逻辑分页访问一次。物理分页只读取一部分数据，占用内存小。逻辑分页占用较大应用内存。物理分页每次都访问数据库，实时性强。逻辑分页无法读取最新数据状态。物理分页适合数据量大、更新频繁的场合。逻辑分页适合数据量小、数据稳定的场景。

(3)limit的效率问题

从vote_record_memory表中查出3600000到3800000的数据，此时在id上加个索引，索引的类型是Normal，索引的方法是BTREE，分别用两种方法查询。发现，方法2的执行效率远比方法1的执行效率高，几乎是方法1的九分之一的时间。

-- 方法1
SELECT * FROM vote_record_memory vrm  LIMIT 3600000,20000 ;
-- 方法2
SELECT * FROM vote_record_memory vrm WHERE vrm.id >= 3600000 LIMIT 20000 

分析一：因为在方法1中，我们使用的单纯的limit。limit随着行偏移量的增大，当大到一定程度后，会出现效率下降。而方法2用上索引加where和limit，性能基本稳定，受偏移量和行数的影响不大。

分析二：用explain来分析两个语句。 limit语句的执行效率未必很高，因为会进行全表扫描，这就是为什么方法1扫描的的行数是400万行的原因。方法2的扫描行数是47945行，这也是为什么方法2执行效率高的原因。我们尽量避免全表扫描查询，尤其是数据非常庞大。能用索引的尽量使用索引，type至少达到range级别！！如果不用索引，查询到的结果和返回的时间和方法1的时间差不多。这也就是我们为什么尽量使用索引的原因。mysql索引方法一般有BTREE索引和HASH索引，hash索引的效率比BTREE索引的效率高，但我们经常使用BTREE索引，而不是hash索引。因为最重要的一点就是：Hash索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。

16.数据库版本

select version();

17.一张自增表里面总共有7条数据，删除了最后2条数据，重启MySQL数据库，又插入了一条数据，此时id是几？

如果表的类型是InnoDB，不重启mysql的情况下这条记录的id是8。但是如果重启这条记录的ID是6。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。如果表的类型是MyISAM，那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

18.MySQL查询字段区是否不区分大小写？如何区分

MySQL默认的字符检索策略：utf8_general_ci，表示不区分大小写。

(1)可以使用utf8_general_cs，表示区分大小写，也可以使用utf8_bin，表示二进制比较，同样也区分大小写。创建表时，直接设置表的collate属性为utf8_general_cs或者utf8_bin；如果已经创建表，则直接修改字段的Collation属性为utf8_general_cs或者utf8_bin。

-- 创建表：
CREATE TABLE test(
id INT PRIMARY KEY,
name VARCHAR(32) NOT NULL
) ENGINE = INNODB COLLATE =utf8_bin;

-- 修改表结构的Collation属性
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

(2)直接修改sql语句，在要查询的字段前面加上binary关键字

-- 在每一个条件前加上binary关键字
select * from user where binary username = 'admin' and binary password = 'admin';

-- 将参数以binary('')包围
select * from user where username like binary('admin') and password like binary('admin');

19.MySQL INT和CHAR隐式类型转换

(1)当查询字段（数据库表字段）是INT类型，如果查询条件（SQL语句）为CHAR，将查询条件转换为INT，如果是字符串前导都是数字，将截取前导数字用来比较，如果没有前导数字，则转换为0。 (2)当查询字段是CHAR/VARCHAR类型，如果查询条件为INT，将查询字段转换为INT再进行比较，可能会造成全表扫描。

20.MySQL如何高效率随机获取N条数据？

ID连续的情况下：

SELECT *
FROM `mm_account` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `mm_account`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 4;

ID不连续的情况下：

SELECT * FROM `mm_account` 
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `mm_account`)))  and city="city_91" and showSex=1
ORDER BY id LIMIT 4;

如果有一个字段叫id，最快的方法如下（随机获取5条）：

SELECT * FROM mm_account 
WHERE id >= ((SELECT MAX(id) FROM mm_account)-(SELECT MIN(id) FROM mm_account)) * RAND() + (SELECT MIN(id) FROM mm_account)
limit 5;

如果带where语句，上面就不适合了，带where语句请看下面：

SELECT *
FROM `mm_account` AS t1 JOIN (SELECT ROUND(RAND() * (
(SELECT MAX(id) FROM `mm_account` where id<1000 )-(SELECT MIN(id) FROM `mm_account` where id<1000 ))+(SELECT MIN(id) FROM `mm_account` where id<1000 )) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 5;

20.MySQL性能

对于其中运行速度，根据官方介绍，MySQL 8.0 比之前广泛使用的版本MySQL 5.7有了两倍的提升。在其官方的Benchmarks中，只读的性能超过了每秒一百万次。读写的性能接近每秒二十五万次：

21.查询语句关键词执行先后顺序

SQL语句顺序依次为 select–from–where–group by–having–order by。这六个关键词的执行顺序与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行： from:需要从哪个数据表检索数据。 where:过滤表中数据的条件。 group by:如何将上面过滤出的数据分组。 having:对上面已经分组的数据进行过滤的条件。 select:查看结果集中的哪个列，或列的计算结果。 order by:按照什么样的顺序来查看返回的数据。

22.SQL执行过程

第一层：客户端通过连接服务，将要执行的sql指令传输过来。第二层：服务器解析并优化sql，生成最终的执行计划并执行。第三层：存储引擎，负责数据的储存和提取。

23.SQL数据类型

整数，TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存储8,16,24,32,64位存储空间。使用Unsigned表示不允许负数，可以使正数的上线提高一倍。实数，Float,Double , 支持近似的浮点运算。Decimal，用于存储精确的小数。字符串，VarChar，存储变长的字符串。需要1或2个额外的字节记录字符串的长度。Char，定长，适合存储固定长度的字符串，如MD5值。Blob，Text 为了存储很大的数据而设计的。分别采用二进制和字符的方式。时间类型，DateTime，保存大范围的值，占8个字节。TimeStamp，推荐，与UNIX时间戳相同，占4个字节。

24.in和exists、not in和not exists、in和=

(1)in和exists

小表驱动大表的原则。 in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

# 表A(小表)，表B(大表)
select * from A where cc in(select cc from B)  -->效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)  -->效率高，用到了B表上cc列的索引。

select * from B where cc in(select cc from A)  -->效率高，用到了B表上cc列的索引
select * from B where exists(select cc from A where cc=B.cc)  -->效率低，用到了A表上cc列的索引

(2)not in和not exists

not in逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG，请看下面的例子：

create table #t1(c1 int,c2 int);
create table #t2(c1 int,c2 int);
insert into #t1 values(1,2);
insert into #t1 values(1,3);
insert into #t2 values(1,2);
insert into #t2 values(1,null);
select * from #t1 where c2 not in(select c2 from #t2);  -->执行结果：无
select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)  -->执行结果：1  3

正如所看到的，not in出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同，后者使用了hash_aj，所以，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists（它会调用关联子查询）。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它用hash_aj或merge_aj连接。如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in要快。

(3)in与=

# 结果是相同的。
select name from student where name in('zhang','wang','zhao');
select name from student where name='zhang' or name='wang' or name='zhao';

25.条件判断

(1)CASE WHEN

# 用法1：
CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END

SELECT name,(CASE sex WHEN 0 THEN '女' ELSE '男' END) sex FROM score

# 用法2：语句中的condition是条件判断，如果该判断结果为true，那么CASE语句将返回result，否则返回result2，如果没有ELSE，则返回null。CASE与END之间可以有多个WHEN…THEN…ELSE语句。END表示CASE语句结束。
CASE
WHEN condition THEN result1 ELSE result2
END

SELECT name,score,(CASE
WHEN score>=90 THEN '优秀'
WHEN score>=80 THEN '良好'
WHEN score>=60 THEN '及格'
ELSE '不及格' END) level
FROM score；

# 用法3：CASE WHEN和聚合函数综合使用，能实现更加复杂的统计功能。
SELECT
SUM(CASE WHEN sex=0 THEN 1 ELSE 0 END) AS 女生人数,
SUM(CASE WHEN sex=1 THEN 1 ELSE 0 END) AS 男生人数,
SUM(CASE WHEN score>=60 AND sex=0 THEN 1 ELSE 0 END) 男生及格人数,
SUM(CASE WHEN score>=60 AND sex=1 THEN 1 ELSE 0 END) 女生及格人数
FROM score;

(2)IF

# 用法1：
IF(expr,result_true,result_false)，expr是一个条件表达式，如果结果为true，则返回result_true，否则返回result_false。
SELECT name,IF(sex=1,'男','女')sex FROM students;

# 用法2：IF函数还可以和聚合函数结合
SELECT COUNT(IF(sex=1,1,NULL)) 男生人数,COUNT(IF(sex=0,1,NULL))女生人数 FROM students

(3)IFNULL

SELECT IFNULL(price,0) price FROM goods WHERE name='aaa';

SELECT IFNULL(SUM(price),0) FROM goods WHERE status=3;

26.MySQL分区

(1)InnoDB逻辑存储结构：首先要先介绍一下InnoDB逻辑存储结构和区的概念，它的所有数据都被逻辑地存放在表空间，表空间又由段，区，页组成。段就是上图的segment区域，常见的段有数据段、索引段、回滚段等，在InnoDB存储引擎中，对段的管理都是由引擎自身所完成的。区就是上图的extent区域，区是由连续的页组成的空间，无论页的大小怎么变，区的大小默认总是为1MB。为了保证区中的页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区，InnoDB页的大小默认为16kb，即一个区一共有64（1MB/16kb=64）个连续的页。每个段开始，先用32页（page）大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小表或者是undo类的段，可以开始申请较小的空间，节约磁盘开销。页就是上图的page区域，也可以叫块。页是InnoDB磁盘管理的最小单位。默认大小为16KB，可以通过参数innodb_page_size来设置。常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页，未压缩的二进制大对象页，压缩的二进制大对象页等。

(2)分区概述：这里讲的分区，此“区”非彼“区”，这里讲的分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件，不是我们刚刚说的区。 MySQL在5.1时添加了对水平分区的支持。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。要知道MySQL是面向OLTP的数据，它不像TIDB等其他DB。那么对于分区的使用应该非常小心，如果不清楚如何使用分区可能会对性能产生负面的影响。 MySQL数据库的分区是局部分区索引，一个分区中既存了数据，又放了索引。也就是说，每个区的聚集索引和非聚集索引都放在各自区的（不同的物理文件）。目前MySQL数据库还不支持全局分区。无论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

(3)分区类型：目前MySQL支持一下几种类型的分区，RANGE分区，LIST分区，HASH分区，KEY分区。如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。 RANGE分区：是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型，那么只用存yyyyMM就好了。也不用关心函数了。

CREATE TABLE `m_test_db`.`Order` (
`id` INT NOT NULL AUTO_INCREMENT,
`partition_key` INT NOT NULL,
`amt` DECIMAL(5) NULL,
PRIMARY KEY (`id`, `partition_key`)) PARTITION BY RANGE(partition_key) PARTITIONS 5( PARTITION part0 VALUES LESS THAN (201901),  PARTITION part1 VALUES LESS THAN (201902),  PARTITION part2 VALUES LESS THAN (201903),  PARTITION part3 VALUES LESS THAN (201904),  PARTITION part4 VALUES LESS THAN (201905)) ;

# 先插入一些数据
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

# 现在查询一下，通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区
explain partision select * from m_test_db`.`Order` where partition_key = '201902';
# 如果sql语句有问题，那么会走所有区。会很危险。所以分区表后，select语句必须走分区键。
explain partision select * from m_test_db`.`Order` where amt > 500;

LIST分区：和RANGE分区很相似，只是分区列的值是离散的，不是连续的。LIST分区使用VALUES IN，因为每个分区的值是离散的，因此只能定义值。 HASH分区：说到哈希，那么目的很明显了，将数据均匀的分布到预先定义的各个分区中，保证每个分区的数量大致相同。 KEY分区：KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用数据库提供的函数进行分区。

(4)分区和性能一项技术，不是用了就一定带来益处。不是启动了分区数据库就会运行的更快，分区可能会给某些sql语句性能提高，但是分区主要用于数据库高可用性的管理。数据库应用分为2类，一类是OLTP（在线事务处理），一类是OLAP（在线分析处理）。对于OLAP应用分区的确可以很好的提高查询性能，因为一般分析都需要返回大量的数据，如果按时间分区，比如一个月用户行为等数据，则只需扫描响应的分区即可。在OLTP应用中，分区更加要小心，通常不会获取一张大表的10%的数据，大部分是通过索引返回几条数据即可。比如一张表1000w数据量，如果一句select语句走辅助索引，但是没有走分区键。那么结果会很尴尬。如果1000w的B+树的高度是3，现在有10个分区。那么不是要(3+3)*10次的逻辑IO？（3次聚集索引，3次辅助索引，10个分区）。所以在OLTP应用中请小心使用分区表。在日常开发中，如果想查看sql语句的分区查询结果可以使用explain partitions + select sql来获取，partitions标识走了哪几个分区。

27.union与union all的区别

union在进行表连接后会筛选掉重复的记录，所以在表连接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。 union all则会显示重复结果,只是简单的两个结果合并并返回.所以效率比union高，在保证没有重复数据的情况下用union all。效率UNION高于UNION ALL。

28.为什么要使用数据库

数据永久保存
使用SQL语句，查询方便效率高。
管理数据方便

29.MySQL有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

30.MySQL数据类型

(1)整数类型

包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

(2)实数类型包括FLOAT、DOUBLE、DECIMAL。 DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

(3)字符串类型

包括VARCHAR、CHAR、TEXT、BLOB VARCHAR用于存储可变长字符串，它比定长类型更节省空间。 VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。 VARCHAR存储的内容超出设置的长度时，内容会被截断。 CHAR是定长的，根据定义的字符串长度分配足够的空间。 CHAR会根据需要使用空格进行填充方便比较。 CHAR适合存储很短的字符串，或者所有值都接近同一个长度。 CHAR存储的内容超出设置的长度时，内容同样会被截断。

使用策略：对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

(4)枚举类型（ENUM）

把不重复的数据存储为一个预定义的集合。有时可以使用ENUM代替常用的字符串类型。 ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。 ENUM在内部存储时，其实存的是整数。尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。排序是按照内部存储的整数。

(5)日期和时间类型

尽量使用timestamp，空间效率高于datetime，用整数保存时间戳通常不方便处理。如果需要存储微秒，可以使用bigint存储。

31.什么是子查询

条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

子查询的三种情况

(1)子查询是单行单列的情况：结果集是一个值，父查询使用：=、 <、 > 等运算符。

select  * from employee where salary=(select max(salary) from employee);

(2)子查询是多行单列的情况：结果集类似于一个数组，父查询使用：in 运算符。

(3)子查询是多行多列的情况：结果集类似于一张虚拟表，不能用于where条件，用于select子句中做为子表。

select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;

32.int(20)中20的涵义

是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；不影响内部存储，只是影响带zerofill定义的int时，前面补多少个0，易于报表展示。

33.SQL的生命周期？

应用服务器与数据库服务器建立一个连接。
数据库进程拿到请求sql。
解析并生成执行计划，执行。
读取数据到内存并进行逻辑处理。
通过步骤一的连接，发送结果到客户端。
关掉连接，释放资源

34.数据库备份

# MySQL
# 备份单表结构和数据
mysqldump -h 127.0.0.1 -P 9000 -u username database_name table_name -p > ./table_name_new.sql
# 备份单表结构
mysqldump -h 127.0.0.1 -P 9000 -u username -d database_name table_name -p > ./table_name_new.sql
# 导入到新库中
create database database_name_new;
use database_name_new;
source ./table_name_new.sql
# 备份指定库结构和数据
mysqldump -h 127.0.0.1 -P 9000 -u username --databases database_name -p > ./table_name_new.sql

N.参考

(1)Java面试题之数据库三范式是什么？

(2)三张图搞透第一范式(1NF)、第二范式(2NF)和第三范式(3NF)的区别

数据库索引

1.索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。是数据库管理系统中一个排序的数据结构，索引的实现通常使用B树及其变种B+树。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

优点：

(1)通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 (2)可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 (3)可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 (4)在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 (5)通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

(1)时间方面：创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 (2)空间方面：索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

2.哪些列适合建立索引、哪些不适合建索引？

适合创建索引：where, order by, join, group by

(1)在经常需要搜索的列上，可以加快搜索的速度。特别是唯一、不为空、经常被查询的字段。 (2)在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。主键自动建立唯一索引。 (3)在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。 (4)在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。 (5)在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； (6)在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不应该创建索引：

(1)对于那些在查询中很少使用或者参考的列不应该创建索引（where条件里用不到的字段）。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 (2)对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 (3)对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 (4)当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 (5)表记录太少，不需要创建索引。

3.Hash索引

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位。如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据。在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

局限性：

Hash索引仅仅能满足”=”,”IN”和”“查询，不能使用范围查询,因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索。 Hash索引无法被用来数据的排序操作，因为Hash值的大小关系并不一定和Hash运算前的键值完全一样。哈希索引没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）。 Hash索引不能利用部分索引键查询，对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。不支持多列联合索引的最左匹配规则。 Hash索引在任何时候都不能避免表扫描，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要回表查询数据。 Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+树索引高。

引擎支持：

(1)MySQL中，MEMORY、InnoDB引擎支持Hash索引。 (2)常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。自适应哈希索引可以在InnoDB不牺牲事务功能或可靠性的情况下创建，但是他的使用范围就是Buffer Pool，那么最终这个hash索引仍然只是一个内存索引。而我们B+Tree索引是存储在磁盘的，一般只有根节点常驻内存。

4.B树

两个事实：

当处理大规模数据的时候（指无法将数据一次性存入内存），算法的实际运行时间，往往取决于数据在不同存储级别之间的IO次数。因此，要想提升速度，关键在于减少IO。想要提升速度，应该利用外存批量访问的特点，在一些文章中，也称其为磁盘预读。系统之所以这么设计，是基于一个著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用，程序运行期间所需要的数据通常比较集中。

分析：假设有10亿条记录，如果使用平衡二叉搜索树（Balanced Binary Search Tree, BBST），最坏的情况下，查找需要log(2, 10^9) = 30次 I/O 操作，且每次只能读出一个关键字。如果换成B树，是一种多叉平衡搜索树。多级存储系统中使用B树，可针对外部查找，大大减少I/O次数。通过B树，可充分利用外存对批量访问的高效支持，将此特点转化为优点。每下降一层，都以超级结点为单位（超级结点就是指一个结点内包含多个关键字），从磁盘中读入一组关键字。一个节点存放多少数据视磁盘的数据块大小而定，比如磁盘中1 block的大小有1024Byte，假设每个关键字的大小为4Byte，则可设定每一组的大小m = 1024 Byte / 4 Byte = 256。目前，多数数据库系统采用m = 200~300。假设取m = 256，则B树存储1亿条数据的树的高度大概是log(256, 10^9) = 4，也就是单次查询所需要进行的I/O次数不超过4次，由此大大减少了I/O次数。一般来说，B树的根节点常驻于内存中，B树的查找过程是这样的：首先，由于一个节点内包含多个（比如，是256个）关键码，所以需要先顺序/二分来查找，如果找到则查找成功；如果失败，则根据相应的引用从磁盘中读入下一层的节点数据（这里就涉及到一次磁盘I/O），同样的在节点内顺序查找，如此往复进行。事实上，B树查找所消耗的时间很大一部分花在了I/O上，所以减少I/O次数是非常重要的。由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少利用率。所以B-树的性能总是等价于二分查找（与M值无关），也就没有B树平衡的问题；由于M/2的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占M/2的结点；删除结点时，需将两个不足M/2的兄弟结点合并。

定义：

B树就是平衡的多路搜索树，所谓的m阶B树，即m路平衡搜索树。需满足以下要求：

每个结点至多含有m个分支节点（m>=2）。
除根结点之外的每个非叶结点，至少含有┌m/2┐（向上取整）个分支。
若根结点不是叶子结点，则至少有2个孩子。
一个含有k个孩子的非叶结点包含k-1个关键字。（每个结点内的关键字按升序排列）
所有的叶子结点都出现在同一层。实际上这些结点并不存在，可以看作是外部结点。
根据节点的分支的上下限，也可以称其为(┌m/2┐, m)树。比如，阶数m=4时，这样的B树也可以称为(2,4)树。（事实上，(2,4)树是一棵比较特殊的B树，它和红黑树有着特别的渊源！）
并且，每个内部结点的关键字都作为其子树的分隔值。比如，某结点含有2个关键字（假设为a1和a2），也就是说该结点含有3个子树。那么，最左子树的关键字均小于a1；中间子树的关键字介于a1~a2；最右子树的关键字均大于a2。

B树的搜索：

从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B树的特性：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制。

5.B树的高度

假定一棵B树非空，具有n个关键字、高度为h（令根结点为第1层）、阶数为m，那么该B树的最大高度和最小高度分别是多少？

最大高度：

当树的高度最大时，则每个结点含有的关键字数应该尽量少。根据定义，根结点至少有2个孩子（即1个关键字），除根结点之外的非叶结点至少有┌m/2┐个孩子（即┌m/2┐-1个关键字），为了描述方便，这里令p = ┌m/2┐。
第1层 1个结点 （含1个关键字）
第2层 2个结点 （含2*(p-1)个关键字）
第3层 2p个结点 （含2p*(p-1)^2个关键字）
…
第h层 2p^(h-2)个结点
故总的结点个数n≥ 1+(p-1)*[2+2p+2p^2+...+2p^(h-2)]≥ 2p^(h-1)-1
从而推导出 h ≤ log_p[(n+1)/2] + 1 （其中p为底数，p=┌m/2┐）

最小高度：

当树的高度最低时，则每个结点的关键字都至多含有m个孩子（即m-1个关键字），则有n ≤ (m-1)*(1 + m + m^2 +...+ m^(h-1)) = m^h - 1
从而推导出 h ≥ log_m(n+1) （其中m为底数）

6.B+树索引

分析：

在MySQL的InnoDb引擎中，页的大小是16KB，是操作系统的4倍，而int类型的数据是4个字节，其它类型的数据的字节数通常也在4000字节以内，所以一页是可以存放很多很多条数据的，而MySQL的数据正是以页为基本单位组合而成的。页的结构包含我们的多条数据，另外，MySQL的数据以页组成，那么它有指向下一页的指针和指向上一页的指针。页内部存放数据的模块，实质上就是一个链表的结构，链表的特点也就是增删快，查询慢，所以优化查询的效率是必须的。页目录的引入，实际上页目录就像是我们在看书的时候书本的目录一样，目录项1就相当于第一节，目录项2就相当于第二节，而每一条数据就相当于书本的每一页，这张图就可以解释成，第一节从第一页开始，第二节从第三页开始，而实际上，每个目录项会存放自己这个目录项当中最小的id，也就是说，目录项1中会存放1，而目录项2会存放3。那么对比一下数据库在没有页目录时候的查找流程，假设要查找id=3的数据，在没有页目录的情况下，需要查找id=1、id=2、id=3，三次才能找到该数据，而如果有页目录之后，只需要先查看一下id=3存在于哪个目录项下，然后直接通过目录项进行数据的查找即可，如果在该目录项下没有找到这条数据，那么就可以直接确定这条数据不存在，这样就大大提升了数据库的查找效率，但是这种页目录的实现，首先就需要基于数据是在已经进行过排序的的场景下，才可以发挥其作用，所以看到这里，大家应该明白第二个问题了，为什么数据库在插入时会进行排序，这才是真正发挥排序的作用的地方。页内数据区和多页模式本质上都是链表，那么的确可以采用相同的方式来对其进行优化，它就是目录页。所以我们对比页内数据区，来分析如何优化多页结构。在单页时，我们采用了页目录的目录项来指向一行数据，这条数据就是存在于这个目录项中的最小数据，那么就可以通过页目录来查找所需数据。所以对于多页结构也可以采用这种方式，使用一个目录项来指向某一页，而这个目录项存放的就是这一页中存放的最小数据的索引值。和页目录不同的地方在于，这种目录管理的级别是页，而页目录管理的级别是行。存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。其实目录页的本质也是页，普通页中存的数据是项目数据，而目录页中存的数据是普通页的地址。这是一棵MySQL意义上的B+树，MySQL的一种索引结构，其中的每个节点就可以理解为是一个页，而叶子节点也就是数据页，除了叶子节点以外的节点就是目录页。这一点在图中也可以看出来，非叶子节点只存放了索引，而只有叶子节点中存放了真实的数据，这也是符合B+树的特点的。

B树和B+树的区别：

B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null，叶子结点不包含任何关键字信息。 B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。(而B树的非终节点也包含需要查找的有效信息)。B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问。 B+树，非叶子结点的子树指针与关键字个数相同；非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；为所有叶子结点增加一个链指针；所有关键字都在叶子结点出现。

特性：

所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
更适合文件索引系统；

B+树的优势：

(1)由于叶子节点上存放了所有的数据，并且有指针相连，每个叶子节点在逻辑上是相连的，所以对于范围查找比较友好。 (2)B+树的所有数据都在叶子节点上，所以B+树的查询效率稳定，一般都是查询3次。B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。 (3)B+树有利于磁盘的IO，因为它的层高基本不会因为数据扩大而增高（三层树结构大概可以存放两千万数据量。B+树的磁盘读写代价更低：B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

Page数据结构：

File Header字段用于记录Page的头信息，其中比较重要的是FIL_PAGE_PREV和FIL_PAGE_NEXT字段，通过这两个字段，我们可以找到该页的上一页和下一页，实际上所有页通过两个字段可以形成一条双向链表。 Page Header字段用于记录Page的状态信息。 Infimum 和 Supremum 是两个伪行记录，Infimum（下确界）记录比该页中任何主键值都要小的值，Supremum （上确界）记录比该页中任何主键值都要大的值，这个伪记录分别构成了页中记录的边界。 User Records 中存放的是实际的数据行记录，具体的行记录结构。 Free Space中存放的是空闲空间，被删除的行记录会被记录成空闲空间。 Page Directory记录着与二叉查找相关的信息。 File Trailer存储用于检测数据完整性的校验和等数据。

7.聚集索引(clustered index)

聚簇索引：就是将索引和数据放到一起，找到索引也就找到了数据，我们刚才看到的B+树索引就是一种聚簇索引，而非聚簇索引就是将数据和索引分开，查找时需要先查找到索引，然后通过索引回表找到相应的数据。并不是所有的存储引擎都支持聚簇索引，目前InnoDB支持。InnoDB表有且只有一个聚簇索引，而MyISAM中都是非聚簇索引。聚集索引中键值的逻辑顺序和表中相应行的物理顺序相同。在innodb的设计中聚集索引包含整行的数据，所以innodb中索引就是数据本身，这就是大家常说的索引即数据。每个InnoDB表都有一个特殊的索引，称为聚簇索引，用于存储行数据。通常，聚簇索引与主键同义。 InnoDB中只要有主键被定义，那么主键列被作为一个聚簇索引，而其它索引都将被作为非聚簇索引，所以自然而然的，这个索引就会是一个非聚簇索引。聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。如果使用聚簇索引，最好使用AUTO_INCREMENT列作为主键，应该尽量避免使用随机的聚簇主键。字符串类型不建议使用聚簇索引，特别是随机字符串，因为它们会使系统进行大量的移动操作。聚集索引类似于新华字典中用拼音去查找汉字，拼音检索表于书记顺序都是按照a~z排列的，就像相同的逻辑顺序与物理顺序一样，当你需要查找a,ai两个读音的字，或是想一次寻找多个傻(sha)的同音字时，也许向后翻几页，或紧接着下一行就得到结果了。

非聚集索引：非聚集索引的话其实就是一个普通索引，但是非聚集索引不存储全部数据，只存储聚集索引的值（一般为主键id）。指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。非聚集索引类似在新华字典上通过偏旁部首来查询汉字，检索表也许是按照横、竖、撇来排列的，但是由于正文中是a~z的拼音顺序，所以就类似于逻辑地址于物理地址的不对应。同时适用的情况就在于分组，大数目的不同值，频繁更新的列中，这些情况即不适合聚集索引。每次查非聚集索引都会再次通过主键再次去聚集索引里面查询。这里我们再引申出一个概念那就是回表。回表的原因是我们需要获取的是整行或者是包含非索引字段的数据，因非聚集索引没有该字段所以需要回表查询。因此建议尽量少用SELECT * FROM TABLE,例如我们查询SELECT * FROM USER WHERE name LIKE ‘张%’，但是我们其实想要的只是名字的集合而已，那么我们就可以改造成SELECT name FROM USER WHERE name LIKE ‘张%’,前者会回表查询而后者不会，这应就减少了数据查询的时间同时也减少了数据库的压力。

根本区别：聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

8.为什么用自增列作为主键

如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新记录都要被插到现有索引页的中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

9.MySQL全表扫描

全表扫描是数据库搜寻表的每一条记录的过程，直到所有符合给定条件的记录返回为止。通常在数据库中，对无索引的表进行查询一般称为全表扫描；然而有时候我们即便添加了索引，但当我们的SQL语句写的不合理的时候也会造成全表扫描。当不规范的写法造成全表扫描时，会造成CPU和内存的额外消耗，甚至会导致服务器崩溃。

10.联合索引

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age); 为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。最左前缀原则：顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。

联合索引的排序有这么一个原则，从左往右依次比较大小，就拿刚才建立的索引举例子，他会先去比较age的大小，如果age的大小相同，那么比较height的大小，如果height也无法比较大小，那么就比较weight的大小，最终对这个索引进行排序。注意：此时由于是非聚簇索引，所以叶子节点不再有数据，而是存了一个主键索引，最终会通过主键索引来回表查询数据。 B+树的结构有了，就可以通过这个来理解最左前缀匹配原则了。

联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。对于复合索引，Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部分，但只能是最左侧部分。例如索引是key index（a,b,c）。可以支持a | a,b| a,b,c 3种组合进行查找，但不支持b,c进行查找。当最左侧字段是常量引用时，索引就十分有效。利用索引中的附加列，可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。如：建立姓名、年龄、性别的复合索引。

create table myTest(
     a int,
     b int,
     c int,
     KEY a(a,b,c)
);

以下示例：

select * from myTest  where a=3 and b=5 and c=4; --abc三个索引都在where条件里面用到了，而且都发挥了作用
select * from myTest  where  c=4 and b=6 and a=3; --where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样
select * from myTest  where a=3 and c=7;  --a用到索引，b没有用，所以c是没有用到索引效果的
select * from myTest  where a=3 and b>7 and c=3;  --a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
select * from myTest  where b=3 and c=4;   ---因为a索引没有使用，所以这里bc都没有用上索引效果
select * from myTest  where a>4 and b=7 and c=9;  --a用到了 b没有使用，c没有使用
select * from myTest  where a=3 order by b;  --a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的
select * from myTest  where a=3 order by c;  --a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
select * from mytable where b=3 order by a;  --b没有用到索引，排序中a也没有发挥索引效果

索引失效的条件：不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。存储引擎不能使用索引范围条件的右边的列。尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。即如果select的列都在索引列中，就算是覆盖索引，like ‘%abc’也能使用索引。 mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。 is null,is not null也无法使用索引。 like以通配符开头like ‘%abc’，mysql索引失效会变成全表扫描的操作。字符串不加单引号索引失效，SELECT * from staffs where name=2000; – 未使用索引，因为mysql会在底层对其进行隐式的类型转换。

要点：最佳左前缀法则。在等值查询时，更改索引列顺序，并不会影响explain的执行结果，因为mysql底层会进行优化。在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况。group by容易产生Using temporary。全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效；LIKE百分写最右，覆盖索引不写星；不等空值还有or，索引失效要少用。

联合索引的优势：

(1)减少开销：建一个联合索引 (a,b,c) ，实际相当于建了 (a)、(a,b)、(a,b,c) 三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销。 (2)覆盖索引：对联合索引 (a,b,c)，如果有如下sql的，SELECT a,b,c from table where a=’xx’ and b = ‘xx’;那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别是随机io其实是DBA主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。 (3)效率高：索引列多，通过联合索引筛选出的数据越少。比如有1000W条数据的表，有如下SQL:select col1,col2,col3 from table where col1=1 and col2=2 and col3=3;假设：假设每个条件可以筛选出 10% 的数据。 (a)如果只有单列索引，那么通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2 and col3= 3 的数据，然后再排序，再分页，以此类推（递归）； (b)如果是（col1,col2,col3）联合索引，通过三列索引筛选出 1000w10% 10% *10%=1w，效率提升可想而知！

11.前缀索引

前缀索引也叫局部索引，比如给身份证的前10位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在order by或者group by中触发前缀索引，也不能把它们用于覆盖索引。当要索引的列字符很多时，索引则会很大且变慢，可以只索引列开始的部分字符串，节约索引空间，从而提高索引效率。当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。为前4位字符创建索引：

alter table tbl_test add index(name(4));

实操的难度：在于前缀截取的长度。我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）。

12.覆盖索引（covering index）

指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。如表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

13.索引使用原则

(1)选择唯一性索引，唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。 (2)为经常需要排序、分组和联合操作的字段建立索引，经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。 (3)为常作为查询条件的字段建立索引，如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。而不是为查询输出结果的列加索引。 (4)限制索引的数目，索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 (5)对短小的值加索引，意味着索引所占的空间更小，可以减少I/O活动，同时比较索引的速度也更快。尽量使用数据量少的索引，如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。尤其是主键，要尽可能短小。另外，InnoDB使用的是聚集索引（clustered index），也就是把主键和数据行保存在一起。主键之外的其他索引都是二级索引，这些二级索引也保留着一份主键，这样在查询到索引以后，就可以根据主键找到对应的数据行。如果主键太长的话，会造成二级索引占用的空间变大。 (6)尽量使用前缀来索引，如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。有时候一个字符串的前几个字符就能唯一标识这条记录，这个时候设置索引的长度就是非常划算的做法。 (7)删除不再使用或者很少使用的索引，表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。 (8)最左前缀匹配原则，非常重要的原则。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a=1 and b=2 and c> 3 and d=4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 (9)=和in可以乱序。比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。 (10)尽量选择区分度高的列作为索引。区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。一般当查询优化处理器发现查询结果超过全表的30%的时候，就会跳过索引，直接进行全表扫描。 (11)索引列不能参与计算，保持列“干净”。比如from_unixtime(create_time) = ‘2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(‘2014-05-29’); (12)尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。 (13)写操作比较频繁的列慎重加索引，索引在提高查询速度的同时，也由于需要更新索引而带来了降低插入、删除和更新带索引列的速度的问题。一张数据表的索引越多，在写操作的时候性能下降的越厉害。 (14)对于定义为text、image和bit的数据类型的列不要建立索引。

14.MySQL的索引类型及各自的场景

普通索引：没有任何限制条件的索引，该索引可以在任何数据类型一列上创建。唯一索引：使用UNIQUE参数可以设置唯一索引。创建该索引时，索引列的值必须唯一，但允许有空值。通过唯一索引，用户可以快速地定位某条记录，主键索引是一种特殊的唯一索引。主键索引：特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。多列索引（组合索引、联合索引、复合索引）：在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询，想使用该索引，用户必须使用这些字段中的一个字段。可以建立为普通索引或者是唯一索引。全文索引：仅可用于MyISAM表，针对较大的数据，生成全文索引耗时耗空间。目前只有char、varchar，text列上可以创建全文索引。全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。空间索引：只能建立在空间数据类型上。这样可以提高系统获取空间数据类型的效率。仅可用于MyISAM 表，索引的字段不能为空值。使用SPATIAL参数可以设置索引为空间索引。MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。

15.索引语句

创建：

CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));

ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));

CREATE TABLE tableName(
  id INT NOT NULL,   
  columnName columnType,
  INDEX [indexName] (columnName(length))  
);

查看：

SHOW INDEX FROM t_user_action_log;

删除：

ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

16.索引条件下推

索引条件下推（ICP，index condition pushdown）是针对MySQL使用索引从表中检索行的情况的一种优化。如果不使用ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给MySQL服务器，后者将评估WHERE行的条件。启用ICP后，如果WHERE可以仅使用索引中的列来评估部分条件，则MySQL服务器会将这部分条件压入WHERE条件下降到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。索引条件下推式优化的适用性取决于以下条件：

ICP用于range， ref， eq_ref，和ref_or_null访问方法时，有一个需要访问的全部表行。
ICP可用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表。
对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I/O操作。对于InnoDB聚集索引，完整的记录已被读入InnoDB缓冲区。在这种情况下使用ICP不会减少I/O。
在虚拟生成的列上创建的二级索引不支持ICP。InnoDB支持虚拟生成的列上的二级索引。
引用子查询的条件不能下推。
涉及存储功能的条件不能下推。
存储引擎无法调用存储的功能。
触发条件不能下推。

17.百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）。然后删除其中无用数据（此过程需要不到两分钟）。删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

N.参考

(1)【57期】面试官问，MySQL建索引需要遵循哪些原则呢？

(2)MySQL索引与查询优化

(3)【224期】MySQL索引相关面试演练

数据库锁

1.锁的粒度和锁的策略

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

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

InnoDB中的表级锁：

InnoDB中的表级锁主要包括表级别的意向共享锁(IS锁)和意向排他锁(IX锁)以及自增锁(AUTO-INC锁)。如果我们给某列字段加了AUTO_INCREMENT自增属性，插入的时候不需要为该字段指定值，系统会自动保证递增。系统实现这种自动给AUTO_INCREMENT修饰的列递增赋值的原理主要是两个： AUTO-INC锁：在执行插入语句的时先加上表级别的AUTO-INC锁，插入执行完成后立即释放锁。如果我们的插入语句在执行前无法确定具体要插入多少条记录，比如INSERT…SELECT这种插入语句，一般采用AUTO-INC锁的方式。轻量级锁：在插入语句生成AUTO_INCREMENT值时先才获取这个轻量级锁，然后在AUTO_INCREMENT值生成之后就释放轻量级锁。如果我们的插入语句在执行前就可以确定具体要插入多少条记录，那么一般采用轻量级锁的方式对AUTO_INCREMENT修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。 mysql默认根据实际场景自动选择加锁方式，当然也可以通过innodb_autoinc_lock_mode强制指定只使用其中一种。

InnoDB中的行级锁：

通过MVCC可以解决脏读、不可重复读、幻读这些读一致性问题，但实际上这只是解决了普通select语句的数据读取问题。事务利用MVCC进行的读取操作称之为快照读，所有普通的SELECT语句在READ COMMITTED、REPEATABLE READ隔离级别下都算是快照读。除了快照读之外，还有一种是锁定读（当前读），即在读取的时候给记录加锁，在锁定读的情况下依然要解决脏读、不可重复读、幻读的问题。由于都是在记录上加锁，这些锁都属于行级锁。 InnoDB的行锁，是通过锁住索引来实现的，如果加锁查询的时候没有使用过索引，会将整个聚簇索引都锁住，相当于锁表了。根据锁定范围的不同，行锁可以使用记录锁(Record Locks)、间隙锁(Gap Locks)和临键锁(Next-Key Locks)的方式实现。

2.死锁

发生原因：

是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象。若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB。死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

解决办法：

(1)如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。 (2)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率； (3)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。 (4)解决死锁问题：查出的线程杀死kill

SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX;

设置锁的超时时间，Innodb行锁的等待时间，单位秒。可在会话级别设置。生产环境不推荐使用过大的innodb_lock_wait_timeout参数值。

set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。

3.MySQL的S锁和X锁的区别

为了实现读-读之间不受影响，并且写-写、读-写之间能够相互阻塞，Mysql使用了读写锁的思路进行实现，具体来说就是分为了共享锁和排它锁。 MySQL的锁系统：shared lock和exclusive lock（共享锁和排他锁，也叫读锁和写锁，即read lock和write lock）。读锁是共享的，或者说是相互不阻塞的。写锁是排他的，一个写锁会阻塞其他的写锁和读锁。共享锁【S锁】，又称读锁，在事务要读取一条记录时，需要先获取该记录的S锁。S锁可以在同一时刻被多个事务同时持有。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。我们可以用select … lock in share mode;的方式手工加上一把S锁。排他锁【X锁】，又称写锁。在事务要改动一条记录时，需要先获取该记录的X锁。X锁在同一时刻最多只能被一个事务持有。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。X锁的加锁方式有两种，第一种是自动加锁，在对数据进行增删改的时候，都会默认加上一个X锁。还有一种是手工加锁，我们用一个FOR UPDATE给一行数据加上一个X锁。除了共享锁(Shared Locks)和排他锁(Exclusive Locks)，Mysql还有意向锁(Intention Locks)。意向锁是由数据库自己维护的，一般来说，当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁(IS锁)；当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁(IX锁)。意向锁可以认为是S锁和X锁在数据表上的标识，通过意向锁可以快速判断表中是否有记录被上锁，从而避免通过遍历的方式来查看表中有没有记录被上锁，提升加锁效率。例如，我们要加表级别的X锁，这时候数据表里面如果存在行级别的X锁或者S锁的，加锁就会失败，此时直接根据意向锁就能知道这张表是否有行级别的X锁或者S锁。

4.行锁的三种类型

(1)next-key Locks锁

同时锁住记录(数据)，并且锁住记录前面的Gap。Next-Key Locks = Gap锁 + Record lock锁。临键锁指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,4]、(4,7]等。临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。注意，临键锁锁住的区间会包含最后一个record的右边的临键区间。例如select * from t where id > 5 and id <= 7 for update;会锁住(4,7]、(7,+∞)。 mysql默认行锁类型就是临键锁(Next-Key Locks)。当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。

(2)Gap锁

不锁记录，仅仅记录前后的Gap。间隙锁指的是两个记录之间逻辑上尚未填入数据的部分，比如上述的(1,4)、(4,7)等。同理，间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 4 for update;就会将(1,4)区间锁定。

(3)Record lock锁

锁数据，不锁Gap。记录锁(Record Locks) 所谓记录，就是指聚簇索引中真实存放的数据，比如上面的1、4、7、10都是记录。显然，记录锁就是直接锁定某行记录。当我们使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时，此时就会直接将这条记录锁定。例如select * from t where id =4 for update;就会将id=4的记录锁定。

间隙锁(Gap Locks)和临键锁(Next-Key Locks)都是用来解决幻读问题的，在已提交读（READ COMMITTED）隔离级别下，间隙锁(Gap Locks)和临键锁(Next-Key Locks)都会失效！有两种方式显式关闭gap锁：将事务隔离级别设置为已提交读；将参数innodb_locks_unsafe_for_binlog设置为1。

例子：用户A，update user set count=8 where id>2 and id<6 用户B，update user set count=10 where id=5; 如果用户A在进行了上述操作后，事务还未提交，则B无法对2~6之间的记录进行更新或插入记录，会阻塞，当A将事务提交后，B的更新操作会执行。

Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

5.乐观锁、悲观锁

悲观锁：先获取锁，再进行业务操作。即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。通常所说的“一锁二查三更新”即指的是使用悲观锁。通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。不同的数据库对select for update的实现和支持都是有所区别的，Oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，MySQL就没有no wait这个选项。 MySQL还有个问题是select for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在MySQL中用悲观锁务必要确定走了索引，而不是全表扫描。

如果查询条件用了索引/主键，那么select for update就会进行行锁。如果是普通字段(没有索引/主键)，那么select for update就会进行锁表。

乐观锁：也叫乐观并发控制，它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，那么当前正在提交的事务会进行回滚。乐观锁的特点先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般会使用版本号机制或CAS算法实现。一般的做法是在需要锁的数据上增加一个版本号，或者时间戳，实现方式举例如下：乐观锁（给表加一个版本号字段）这个并不是乐观锁的定义，给表加版本号，是数据库实现乐观锁的一种方式。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。乐观锁还适用于一些比较特殊的场景，例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方。

SELECT data AS old_data, version AS old_version FROM …;
//根据获取的数据进行业务操作，得到new_data和new_version
UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功，操作完成
} else {
    // 乐观锁获取失败，回滚并重试
}

总结：悲观锁和乐观锁是数据库用来保证数据并发安全防止更新丢失的两种方法，例如在select … for update前加个事务就可以防止更新丢失。悲观锁和乐观锁大部分场景下差异不大，一些独特场景下有一些差别，一般我们可以从如下几个方面来判断。响应速度：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁。冲突频率：如果冲突频率非常高，建议采用悲观锁，保证成功率，如果冲突频率大，乐观锁会需要多次重试才能成功，代价比较大。重试代价：如果重试代价大，建议采用悲观锁。

6.如何加锁的？

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。显式加锁：

select math from zje where math>60 lock in share mode； -- 上共享锁（读锁）
select math from zje where math >60 for update； -- 上排它锁（写锁）

表锁：不会出现死锁，发生锁冲突几率高，并发低。 MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。读锁会阻塞写，写锁会阻塞读和写。对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。 MyISAM不适合做写为主表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

行锁：会出现死锁，发生锁冲突几率低，并发高。在MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引相应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。必须注意，行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。两个事务不能锁同一个索引。insert，delete，update在事务中都会自动默认加上排它锁。

举例：

CREATE TABLE `user` (
  `name` VARCHAR(32) DEFAULT NULL,
  `count` INT(11) DEFAULT NULL,
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

A通过主键执行修改操作，但事务未提交。 update user set count=10 where id=1; B在此时也执行更新操作 update user set count=10 where id=2; 由于是通过主键选中的，为行级锁，A和B操作的不是同一行，B执行的操作是可以执行的。

A通过name执行插入操作，但事务未提交。 update user set count=10 where name=’xxx’; B在此时也执行更新操作 update user set count=10 where id=2; 由于是通过非主键或索引选中的，升级为为表级锁， B则无法对该表进行更新或插入操作，只有当A提交事务后，B才会成功执行。

如果在一条select语句后加上for update，则查询到的数据会被加上一条排它锁，其它事务不可以读，也不能进行更新和插入操作。 A用户对id=1的记录进行加锁。 select * from user where id=1 for update; B用户无法对该记录进行操作。 update user set count=10 where id=1; A用户commit以后则B用户可以对该记录进行操作。

7.二阶段锁

传统RDBMS加锁的一个原则，就是2PL (二阶段锁)：Two-Phase Locking。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。 2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。

8.并发与加锁

事务并发访问同一数据资源的情况主要就分为读-读、写-写和读-写三种。读-读即并发事务同时访问同一行数据记录。由于两个事务都进行只读操作，不会对记录造成任何影响，因此并发读完全允许。写-写即并发事务同时修改同一行数据记录。这种情况下可能导致脏写问题，这是任何情况下都不允许发生的，因此只能通过加锁实现，也就是当一个事务需要对某行记录进行修改时，首先会先给这条记录加锁，如果加锁成功则继续执行，否则就排队等待，事务执行完成或回滚会自动释放锁。读-写即一个事务进行读取操作，另一个进行写入操作。这种情况下可能会产生脏读、不可重复读、幻读。最好的方案是读操作利用多版本并发控制（MVCC），写操作进行加锁。

9.MySQL InnoDB中意向锁

InnoDB支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。意向锁是一种不与行级锁冲突表级锁，这一点非常重要。意向锁分为两种：意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）。事务要获取某些行的S锁，必须先获得表的IS锁。SELECT column FROM table … LOCK IN SHARE MODE; 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）。事务要获取某些行的X锁，必须先获得表的IX锁。SELECT column FROM table … FOR UPDATE; 即：意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB会先获取该数据行所在在数据表的对应意向锁。

意向锁要解决的问题：如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞，第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。设想这样一张users表：MySql，InnoDB，Repeatable-Read：users（id PK，name）。事务A获取了某一行的排他锁，并未提交：SELECT * FROM users WHERE id = 6 FOR UPDATE;事务B想要获取users表的表锁：LOCK TABLES users READ; 因为共享锁与排他锁互斥，所以事务B在视图对users表加共享锁的时候，必须保证：当前没有其他事务持有users表的排他锁。当前没有其他事务持有users表中任意一行的排他锁。为了检测是否满足第二个条件，事务B必须在确保users表不存在任何排他锁的前提下，去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法，但是有了意向锁之后，情况就不一样了。

意向锁的兼容互斥性：意向锁之间是互相兼容的。虽然意向锁和自家兄弟互相兼容，但是它会与普通的排他/共享锁互斥：意向共享锁与共享锁兼容，意向共享锁与排他锁互斥，意向排他锁与共享锁互斥，意向排他锁与排他锁互斥。这里的排他/共享锁指的都是表锁！！！意向锁不会与行级的共享/排他锁互斥！！！现在我们回到刚才users表的例子：事务A获取了某一行的排他锁，并未提交：SELECT * FROM users WHERE id = 6 FOR UPDATE;此时users表存在两把锁：users表上的意向排他锁与id为6的数据行上的排他锁。事务B想要获取users表的共享锁：LOCK TABLES users READ;此时事务B检测事务A持有users表的意向排他锁，就可以得知事务A必然持有该表中某些数据行的排他锁，那么事务B对users表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排他锁。

意向锁的并发性：这就牵扯到我前面多次强调的一件事情：意向锁不会与行级的共享 / 排他锁互斥！！！意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性（不然我们直接用普通的表锁就行了）。

总结： InnoDB支持多粒度锁，特定场景下，行级锁可以与表级锁共存。意向锁之间互不排斥，但除了IS与S兼容外，意向锁会与共享锁/排他锁互斥。 IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

N.参考

(1)【245期】面试官：MySQL发生死锁有哪些原因，怎么避免？

(2)【169期】面试官：同学，分析一下MySQL/InnoDB的加锁过程吧

数据库事务

1.数据库事务ACID原则

原子性(Atomicity)：是指一个事务要么全部执行，要么全部失败回滚，也就是说一个事务不可能只执行了一半就停止了。因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。一致性(Consistency)：事务开始前和结束后，一致性是指数据库的完整性约束没有被破坏，在事务执行前后都是合法的数据状态。这里的一致可以表示数据库自身的约束没有被破坏，比如某些字段的唯一性约束、字段长度约束等等；还可以表示各种实际场景下的业务约束，比如上面转账操作，一个账户减少的金额和另一个账户增加的金额一定是一样的。独立性(Isolation）：事务的独立性也有称作隔离性，是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致。隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。持久性(Durability）：事务的持久性是指事务执行成功以后，该事务对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚。对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

2.事务的状态

活动的（active）当事务对应的数据库操作正在执行过程中，则该事务处于活动状态。部分提交的（partially committed）当事务中的最后一个操作执行完成，但还未将变更刷新到磁盘时，则该事务处于部分提交状态。失败的（failed）当事务处于活动或者部分提交状态时，由于某些错误导致事务无法继续执行，则事务处于失败状态。中止的（aborted）当事务处于失败状态，且回滚操作执行完毕，数据恢复到事务执行之前的状态时，则该事务处于中止状态。提交的（committed）当事务处于部分提交状态，并且将修改过的数据都同步到磁盘之后，此时该事务处于提交状态。

3.事务隔离级别

查看事务隔离级别使用select @@tx_isolation。

READ UNCOMMITTED：未提交读，另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读。存在脏读、不可重复读、幻读问题。 READ COMMITTED：已提交读。存在不可重复读、幻读问题。针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。 REPEATABLE READ：可重复读。在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)。在标准的事务隔离级别定义下，REPEATABLE READ是不能防止幻读产生的。INNODB使用了next-key locks实现了防止幻读的发生。 SERIALIZABLE：串行化。无问题。最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样。从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。

事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持。 Oracle支持的2种事务隔离级别：READ_COMMITED , SERIALIZABLE。 MySQL中默认事务隔离级别是“可重复读”时，并不会锁住读取到的行。

事务隔离级别：未提交读时，写数据只会锁住相应的行。事务隔离级别为：可重复读时，写数据会锁住整张表。

4.事务并发执行遇到的问题

脏读（Dirty Read）：是指一个事务读到了其它事务未提交的数据。事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。不可重复读（Non-Repeatable Read）：指的是在一个事务执行过程中，读取到其它事务已提交的数据，导致两次读取的结果不一致。事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。解决不可重复读的问题只需锁住满足条件的行。幻读（Phantom）：是指的是在一个事务执行过程中，读取到了其他事务新插入数据，导致两次读取的结果不一致。不可重复读和幻读的区别在于不可重复读是读到的是其他事务修改的数据，而幻读读到的是其它事务新插入或删除的数据。解决幻读需要锁表。

5.MVCC

MVCC(Multi Version Concurrency Control)，中文名是多版本并发控制，简单来说就是通过维护数据历史版本，从而解决并发访问情况下的读一致性问题。在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。在可重读Repeatable reads事务隔离级别下：

SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
INSERT时，保存当前事务版本号为行的创建版本号。
DELETE时，保存当前事务版本号为行的删除版本号。
UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行。

通过MVCC，虽然每行记录都要额外的存储空间来记录version，需要更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多读操作都不用加锁，读取数据操作简单，性能好。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read) 。

快照读：读取的是记录的可见版本 (有可能是历史版本)，不用加锁。简单的select操作，属于快照读，不加锁。

select * from table where ?;

当前读：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。以下的语句都属于当前读，读取记录的最新版本。并且读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。 Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

6.可重复读隔离级别避免了幻读？

在读提交的隔离级别中，事务1修改了所有class_id=1的数据，当时当事务2 insert后，事务1莫名奇妙地多了一行class_id=1的数据，而且没有被之前的update所修改，产生了读提交下的的幻读。而在可重复度的隔离级别下，情况就完全不同了。事务1在update后，对该数据加锁，事务B无法插入新的数据，这样事务1在update前后数据保持一致，避免了幻读，可以明确的是，update锁的肯定不只是已查询到的几条数据，因为这样无法阻止insert，其实这里的锁，使用并非表锁，是通过next-key锁实现的。 B+树的特点是所有数据都存储在叶子节点上，以非聚簇索引的数据为例，在我们对这条数据做了当前读后，就会对这条数据加行锁，对于行锁很好理解，能够防止其他事务对其进行update或delete，但为什么要加GAP锁呢? B+树的所有数据存储在叶子节点上，当有一个新的数据进来，一定是排在在这条的数据前面或者后面的，我们如果对前后这个范围进行加锁了，那当然新的数据就插不进来了。那如果有一个新的其他值要插进行呢？因为其他值的前后并没有被锁住，是能成功插入的，这样就极大地提高了数据库的并发能力。上文中说了可重复读能防不可重复读，还能防幻读，它能防住所有的幻读吗？当然不是，也有马失前蹄的时候。Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读，所以这个场景下，算出现幻读了。

a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意操作）
a事务再select出来的结果在MVCC下还和第一次select一样
接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的）
a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了。

7.MVCC在mysql中的实现

依赖的是undo log与read view。undo log中记录某行数据的多个版本的数据。ReadView用来判断当前版本数据的可见性。

版本链：在InnoDB中，每行记录实际上都包含了两个隐藏字段：事务id(trx_id)和回滚指针(roll_pointer)。 trx_id：事务id。每次修改某行记录时，都会把该事务的事务id赋值给trx_id隐藏列。 roll_pointer：回滚指针。每次修改某行记录时，都会把undo日志地址赋值给roll_pointer隐藏列。假设hero表中只有一行记录，当时插入的事务id为80。此时，该条记录的示例图如下：假设之后两个事务id分别为100、200的事务对这条记录进行UPDATE操作，操作流程如下：由于每次变动都会先把undo日志记录下来，并用roll_pointer指向undo日志地址。因此可以认为，对该条记录的修改日志串联起来就形成了一个版本链，版本链的头节点就是当前记录最新的值。

ReadView：如果数据库隔离级别是未提交读（READ UNCOMMITTED），那么读取版本链中最新版本的记录即可。如果是是串行化（SERIALIZABLE），事务之间是加锁执行的，不存在读不一致的问题。但是如果是已提交读（READ COMMITTED）或者可重复读（REPEATABLE READ），就需要遍历版本链中的每一条记录，判断该条记录是否对当前事务可见，直到找到为止(遍历完还没找到就说明记录不存在)。InnoDB通过ReadView实现了这个功能。 ReadView中主要包含以下4个内容：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
creator_trx_id：表示生成该ReadView事务的事务id。

有了ReadView之后，我们可以基于以下步骤判断某个版本的记录是否对当前事务可见。如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。READ COMMITTED在每次读取数据前都会生成一个ReadView，这样就能保证每次都能读到其它事务已提交的数据。REPEATABLE READ只在第一次读取数据时生成一个ReadView，这样就能保证后续读取的结果完全一致。

8.分库分表之后，你是如何解决事务问题

(1)基于非事务消息的异步确保的方式

通过在主库中创建一个流水表，把操作数据库的逻辑映射为一条流水记录。当整个大事务执行完毕后（流水被插入到流水表）,然后通过其他方式来执行这段流水，保证最终一致性。所谓流水，可以理解为一条事务消息。上面通过在数据库中创建一张流水表，使用一条流水记录代表一个业务处理逻辑。流水延迟处理性。流水不是实时处理的，而是用过流水执行器来异步执行的。因此，如果在原有逻辑中，需要特别注意后续流程对该流水是不是有实时依赖性（例如后续业务逻辑中会使用流水结果来做一些计算等）。流水处理无序性。保证即使后生成的流水先执行，也不能出现问题。流水最终成功性。对每条插入的流水，该条流水一定要保证能执行成功

流水处理器既要保证流水处理尽可能处理快，又能保证流水最终能执行成功。流水执行器中设置2个任务：第一个任务流水处理任务，以最快的速度执行流水，如果流水处理失败了，也不影响后面流水处理。第二个任务流水校验任务，这个任务就是顺序检查流水记录，保证所有流水都执行成功，如果失败，进行重试，多次重试失败以后发出告警以让人工介入处理。

流水处理完成，因为流水表是放在原数据库中，而流水处理完成后是操作分库，如果分库操作完成去更新老表流水消息，那么又是夸库事务，如何保证流水状态的更新和分库也是在一个事务的？在分库中创建一个流水表，当流水处理完成以后，不是去更新老表状态，而是插入分库流水表中。这样做的好处：一般会对流水做唯一索引，那么如果流水重复多次执行的时候，插入分库流水表的时候肯定由于唯一索引检测不通过，整个事务就会回滚（当然也可以在处理流水事前应该再做一下幂等性判断）。这样通过判断主库流水是否在分库中就能判断一条流水是否执行完毕。

为什么不用事务消息？如果是绝对不容忍有任何消息丢失或者消息处理失败)，不使用事务消息。需要额外引入消息队列，增加系统的复杂度，而且也需要额外的逻辑保证和消息队列通讯失败的时候处理而且事务消息需要手动的commit和rollback（使用数据库不需要），那么问题来了，spring中事务是有传递性的，那我们事务消息何时提交又是个大问题，例如 A.a()本来就是一个事务，但是另外一个事务B.b()中又调用了A.a() 那事务消息提交是放在A.a()还是B.b()中呢？

9.提交数据的三种类型

(1)显式提交：用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT； (2)隐式提交：用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。 (3)自动提交若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；

10.事务传播行为

(1)PROPAGATION_REQUIRED：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。
(2)PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。
(3)PROPAGATION_MANDATORY：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。
(4)PROPAGATION_REQUIRES_NEW：创建新事务，无论当前存不存在事务，都创建新事务。
(5)PROPAGATION_NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
(6)PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。
(7)PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

11.嵌套事务

嵌套是子事务套在父事务中执行，子事务是父事务的一部分，在进入子事务之前，父事务建立一个回滚点，叫save point，然后执行子事务，这个子事务的执行也算是父事务的一部分，然后子事务执行结束，父事务继续执行。重点就在于那个save point。如果子事务回滚，会发生什么？父事务会回滚到进入子事务前建立的save point，然后尝试其他的事务或者其他的业务逻辑，父事务之前的操作不会受到影响，更不会自动回滚。如果父事务回滚，会发生什么？父事务回滚，子事务也会跟着回滚！为什么呢，因为父事务结束之前，子事务是不会提交的，我们说子事务是父事务的一部分，正是这个道理。那么事务的提交，是什么情况？是父事务先提交，然后子事务提交，还是子事务先提交，父事务再提交？答案是第二种情况，还是那句话，子事务是父事务的一部分，由父事务统一提交。

12.事务是如何实现的

事务的原子性是通过undo log来实现的。事务的持久性性是通过redo log来实现的。事务的隔离性是通过 (读写锁+MVCC)来实现的。而事务的一致性是通过原子性，持久性，隔离性来实现的。

事务日志是通过redo log和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”。

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用了Undo Log来实现多版本并发控制(简称：MVCC)。在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。 Redo Log原理和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

N.参考

(1)真正理解Mysql的四种隔离级别

(2)MySQL幻读的详解、实例及解决办法

(3)聊聊MVCC和Next-key Locks

数据库存储引擎

1.存储引擎

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。常用的存储引擎有以下：

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

2.InnoDB与MyISAM

区别： (1)MyISAM不支持事务，但是每次查询都是原子的；InnoDb支持ACID的事务，支持事务的四种隔离级别。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。 (2)MyISAM支持表级锁，即每次操作对整个表加锁；InnoDb支持行级锁：因此可以支持写并发。InnoDB某些情况下还是锁整表，如update table set a=1 where user like ‘%lee%’。MyISAM只支持表级锁，用户在操作MyISAM表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。 (3)MyISAM适合查询以及插入为主的应用。InnoDB适合频繁修改以及涉及到安全性较高的应用。因为MyISAM相对简单所以在效率上要优于InnoDB。如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。 (4)InnoDB支持外键，MyISAM不支持。 (5)MyISAM存储表的总行数；InnoDb不存储总行数。InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表。 (6)Innodb数据和索引在一起（数据即索引，索引即数据），而mysiam是分开存储的。innodb表有两个文件.frm（表描述文件）和.ibd（索引和数据文件）.ibd猜测下index + B+Tree + data。myisam表有三个文件.MYD（数据文件）、.MYI（索引文件）和.frm（表描述文件）。MYD其中D就是data的意思I就是index的意思这样就记住了。MYSIAM引擎的索引文件持有的是数据文件的地址引用。一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制。InnoDB不能通过直接拷贝表文件的方法拷贝表到另外一台机器，MyISAM支持。 (7)Innodb索引是有主次的，也就是区分聚集索引和非聚集索引。而mysiam是不区分主次的。MyISAM采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。InnoDb主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。 (8)从MySQL5.5.5以后，InnoDB是默认引擎。 (9)InnoDB不支持FULLTEXT类型的索引。 (10)对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。 (11)DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，效率非常慢。MyISAM则会重建表。 (12)可移植性、备份及恢复：由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。InnoDB，免费的方案可以是拷贝数据文件、备份 binlog，或者用mysqldump，在数据量达到几十G的时候就相对痛苦了。

如果你的应用程序一定要使用事务，毫无疑问你要选择INNODB引擎。但要注意，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表。比如DELETE FROM mytable这样的删除语句。如果你的应用程序对查询性能要求较高，就要使用MyISAM了。MyISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。有人说MyISAM只能用于小型应用，其实这只是一种偏见。如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，而不是单纯地依赖存储引擎。现在一般都是选用innodb了，主要是MyISAM的全表锁，读写串行问题，并发锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

3.MEMORY

是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。 MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

4.InnoDB引擎的4大特性

插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

数据库日志

1.MySQL innodb有多少种日志

(1)错误日志：记录出错信息，也记录一些警告信息或者正确的信息。error log。
(2)查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。general log。
(3)慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。slow query log。
(4)二进制日志：记录对数据库执行更改的所有操作。binlog。
(5)中继日志：中继日志也是二进制日志，用来给slave库恢复。relay log。
(6)事务日志：重做日志redo log和回滚日志undo log。

2.二进制日志（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，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

3.MySQL binlog的几种日志录入格式以及区别

(1)Statement：每一条会修改数据的sql都会记录在binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该根据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题). 使用以下函数的语句也无法被复制：

LOAD_FILE()
UUID()
USER()
FOUND_ROWS()
SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

(2)Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

(3)Mixedlevel: 以上两种level的混合使用。一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

4.redo log与undo log

redo log: 重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。既然redo log也需要存储，也涉及磁盘IO为啥还用它？redo log的存储是顺序存储，而缓存（指的是Buffer Pool）同步是随机操作。缓存同步是以数据页为单位的，每次传输的数据大小大于redo log。之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，原因就是，重做日志有一个缓存区Innodb_log_buffer，Innodb_log_buffer的默认大小为8M,Innodb存储引擎先将重做日志写入innodb_log_buffer中。然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘： Master Thread每秒一次执行刷新Innodb_log_buffer到重做日志文件。每个事务提交时会将重做日志刷新到重做日志文件。当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件。对应的物理文件：默认情况下，对应的物理文件位于数据库的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。 mysql为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘IO,然而即使是使用SSD磁盘IO也是非常消耗性能的。为了提升性能InnoDB提供了缓冲池(Buffer Pool)，Buffer Pool中包含了磁盘数据页的映射，可以当做缓存来使用。当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中。那么问题来了，如果还没来的同步的时候宕机或断电了怎么办？还没来得及执行上面的同步操作。这样会导致丢部分已提交事务的修改信息！所以引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。所以redo log是用来恢复数据的，用于保障已提交事务的持久化特性。

undo log: 回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。每次写入数据或者修改数据之前都会把修改前的信息记录到undo log。undo log记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。所以undo log是用来回滚数据的，用于保障未提交事务的原子性。每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上。所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete，update的逆向为update等。在执行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配置。

数据库性能优化

1.分库分表之后，id主键如何处理

不好的方案：

(1)数据库自增id

这个就是说你的系统里每次得到一个id，都是往一个库的一个表里插入一条没什么业务含义的数据，然后获取一个数据库自增的一个id。拿到这个id之后再往对应的分库分表里去写入。这个方案的好处就是方便简单，谁都会用；缺点就是单库生成自增id，要是高并发的话，就会有瓶颈的；如果你硬是要改进一下，那么就专门开一个服务出来，这个服务每次就拿到当前id最大值，然后自己递增几个id，一次性返回一批id，然后再把当前最大id值修改成递增几个id之后的一个值；但是无论如何都是基于单个数据库。适合的场景：你分库分表就俩原因，要不就是单库并发太高，要不就是单库数据量太大；除非是你并发不高，但是数据量太大导致的分库分表扩容，你可以用这个方案，因为可能每秒最高并发最多就几百，那么就走单独的一个库和表生成自增主键即可。

(2)设置数据库sequence或者表的自增字段步长

比如说，现在有8个服务节点，每个服务节点使用一个sequence功能来产生ID，每个sequence的起始ID不同，并且依次递增，步长都是8。适合的场景：在用户防止产生的ID重复时，这种方案实现起来比较简单，也能达到性能目标。但是服务节点固定，步长也固定，将来如果还要增加服务节点，就不好搞了

(3)UUID

好处就是本地生成，不要基于数据库来了；不好之处就是，UUID太长了、占用空间大，作为主键性能太差了；更重要的是，UUID不具有有序性，会导致B+树索引在写的时候有过多的随机写操作（连续的ID可以产生部分顺序写），还有由于在写的时候不能产生有顺序的append操作，而需要进行insert操作，将会读取整个B+树节点到内存，在插入这条记录后会将整个节点写回磁盘，这种操作在记录占用空间比较大的情况下，性能下降明显。适合的场景：如果你是要随机生成个什么文件名、编号之类的，你可以用UUID，但是作为主键是不能用UUID的。

UUID.randomUUID().toString().replace(“-”, “”) -> sfsdf23423rr234sfdaf

(4)获取系统当前时间

这个就是获取当前时间即可，但是问题是，并发很高的时候，比如一秒并发几千，会有重复的情况，这个是肯定不合适的。基本就不用考虑了。适合的场景：一般如果用这个方案，是将当前时间跟很多其他的业务字段拼接起来，作为一个id，如果业务上你觉得可以接受，那么也是可以的。你可以将别的业务字段值跟当前时间拼接起来，组成一个全局唯一的编号。

好的方案：

(1)snowflake算法

snowflake算法是twitter开源的分布式id生成算法，采用Scala语言实现，是把一个64位的long型的id，1个bit是不用的，用其中的41bit作为毫秒数，用10bit作为工作机器id（5位数据中心+5位机器），12bit作为序列号。

bit：不用，为啥呢？因为二进制里第一个bit为如果是 1，那么都是负数，但是我们生成的id都是正数，所以第一个bit统一都是0。
bit：表示的是时间戳，单位是毫秒。41bit 可以表示的数字多达2^41-1，也就是可以标识 2^41 - 1个毫秒值，换算成年就是表示69年的时间。
bit：记录工作机器id，代表的是这个服务最多可以部署在2^10台机器上哪，也就是1024台机器。但是10 bit里5个bit代表机房id，5个bit代表机器id。意思就是最多代表2^5个机房（32个机房），每个机房里可以代表2^5个机器（32台机器）。
bit：这个是用来记录同一个毫秒内产生的不同id，12bit可以代表的最大正整数是2^12 - 1 = 4096，也就是说可以用这个12 bit代表的数字来区分同一个毫秒内的4096个不同的id。

分布式ID开源组件：

(1)美团Leaf

Leaf是美团基础研发平台推出的一个分布式ID生成服务，名字取自德国哲学家、数学家莱布尼茨的一句话：“There are no two identical leaves in the world.”Leaf具备高可靠、低延迟、全局唯一等特点。 Leaf在特性如下：全局唯一，绝对不会出现重复的ID，且ID整体趋势递增。高可用，服务完全基于分布式架构，即使MySQL宕机，也能容忍一段时间的数据库不可用。高并发低延时，在CentOS 4C8G的虚拟机上，远程调用QPS可达5W+，TP99在1ms内。接入简单，直接通过RPC服务或者HTTP调用即可接入。

2.拆分数据库

为什么要拆分数据库：单表数据量过大、单库负载大有性能瓶颈。

垂直切分：根据业务来拆分数据库，同一类业务的数据表拆分到一个独立的数据库，另一类的数据表拆分到其他数据库。垂直切分可以降低单节点数据库的负载。垂直切分不能解决的是缩表。

水平切分：按照某个字段的某种规则，把数据切分到多张数据表。一张数据表化整为零，拆分成多张数据表，这样就可以起到缩表的效果了。数据量较大的数据表才需要做数据切分。不是水平切分一定需要多个MySQL节点，可以是单节点的多张表。水平切分的缺是不同数据表的切分规则并不一致，要根据实际业务来确定。所以我们在选择数据库中间件产品的时候，就要选择切分规则丰富的产品。常见的数据库中间件有：MyCat、Atlas、ProxySQL等等。有些人觉得MyCat是Java语言开发的，就怀疑MyCat运行效率。其实数据库中间件的作用相当于SQL语句的路由器。你家路由器硬件配置不怎么高，但是不影响你享用百兆宽带。MyCat也是一个道理，它仅仅是起到SQL语句转发的作用，并不会实际执行SQL语句。我推荐使用MyCat最主要的原因是它自带了非常多的数据切分规则，我们可以按照主键求模切分数据，可以按照主键范围切分数据，还可以按照日期切分数据等等。因此说，为了满足业务的需要，MyCat目前来说算是非常不错的中间件产品。水平切分的另一个缺点就是扩容比较麻烦，日积月累，分片迟早有不够用的时候。这时候不是首先选择增加新的集群分片。因为一个MySQL分片，需要4~8个MySQL节点（最小规模），增加一个分片的投入成本是很高的。所以正确的做法是做冷热数据分离，定期对分片中的数据归档。把过期的业务数据，从分片中转移到归档库。目前来说数据压缩比最高的MySQL引擎是TokuDB，而且带着事物的写入速度是InnoDB引擎的6-14倍。用TokuDB作为归档数据库最适合不过。

为什么先做水平切分，后作垂直切分？随着数据量的增加，最先应该做的是数据分片，利用多块硬盘来增大数据IO能力和存储空间，这么做的成本是最低的。几块硬盘的钱就能收获不错的IO性能。进入到下一个阶段，数据量继续增大，这时候我们应该把数据切分到多个MySQL节点上，用MyCat管理数据切分。当然还要做数据的读写分离等等，这里不展开讨论。在后台做水平切分的同时，业务系统也可以引入负载均衡、分布式架构等等。理论上，使用了冷热数据分离之后，水平切分这种方式可以继续维持很长一段时间，数据量再大也不怕，定期归档就好了。数据库到了水平切分的阶段，数据量的增加已经不是更改架构设计的主要原因了。反而这个阶段业务系统承受不住了，如果再不对系统做模块拆分，业务系统也撑不下去了，所以按照模块和业务，把一个系统拆分成若干子系统。若干子系统之间，数据相对独立。比如淘宝不会跟支付支付宝分享全部数据，共享同一套数据表，这也影响各自业务的发展。所以就要弄垂直切分了，把数据表归类，拆分成若干个数据库系统。如果过早的对数据库做了垂直切分，势必要重新构建若干独立的业务系统，工作量太巨大。水平切分并不需要业务系统做大幅度的修改，因此说应该先从水平切分开始做。

3.join操作如何优化提升性能

数据规模较大，可以通过增加索引来优化join语句的执行速度，可以通过冗余信息来减少join的次数。尽量减少表连接的次数，一个SQL语句表连接的次数不要超过5次。逐条比较两个表的语句是比较慢的，因此我们可以把两个表中数据依次读进一个内存块中, 以MySQL的InnoDB引擎为例，使用以下语句我们必然可以查到相关的内存区域show variables like ‘%buffer%’，join_buffer_size的大小将会影响我们join语句的执行性能。大部分数据库中的数据最终要保存到硬盘上,并且以文件的形式进行存储。以MySQL的InnoDB引擎为例，InnoDB以页(page)为基本的IO单位，每个页的大小为16KB，InnoDB会为每个表创建用于存储数据的.ibd文件，这意味着我们有多少表要连接就需要读多少个文件，虽然可以利用索引，但还是免不了频繁的移动硬盘的磁头，就是说频繁的移动磁头会影响性能。 Join算法：有索引的情况下直接读取两个表的索引树进行比较，利用索引来提升性能。无索引的话嵌套循环，在扫描过程中，数据库会选择一个表把它要返回以及需要进行和其他表进行比较的数据放进join_buffer。 Nested Loop Join，嵌套循环，每次只读取表中的一行数据，也就是说如果outerTable有10万行数据, innerTable有100行数据，需要读取10000000次(假设这两个表的文件没有被操作系统给缓存到内存, 我们称之为冷数据表)，当然现在没啥数据库引擎使用这种算法，太慢了。 Block nested loop，Block 块，也就是说每次都会取一块数据到内存以减少I/O的开销，当没有索引可以使用的时候，MySQL InnoDB就会使用这种算法，当无法使用索引执行join操作的时候，InnoDB会自动使用Block nested loop算法

4.处理慢查询

以下语句返回的结果是实时变化的，是对mysql链接执行的现场快照，所以用来处理突发事件非常有用。它可以查看当前mysql的一些运行情况，是否有压力，都在执行什么sql，语句耗时几何，有没有慢sql在执行等等。

-- 显示数据库线程
show full processlist;
show full processlist\G;

字段意义：
Id：链接mysql服务器线程的唯一标识，可以通过kill来终止此线程的链接。 User：当前线程链接数据库的用户。 Host：显示这个语句是从哪个ip的哪个端口上发出的。可用来追踪出问题语句的用户。 DB: 线程链接的数据库，如果没有则为null。 Command: 显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）。 Time: 线程处在当前状态的时间，单位是秒。 State：显示使用当前连接的sql语句的状态，很重要的列，state只是语句执行中的某一个状态，一个sql语句，以查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。 Info: 线程执行的sql语句，如果没有语句执行则为null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句。

当发现一些执行时间很长的sql时，就需要多注意一下了，必要时kill掉，先解决问题。

-- 杀线程
kill XXXXX

-- 批量结束时间超过3分钟的线程
select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 3*60
order by time desc;

当MySQL在进行一些alter table等DDL操作时，如果该表上有未提交的事务则会出现Waiting for table metadata lock，而一旦出现metadata lock，该表上的后续操作都会被阻塞。一般只要kill掉这些线程，DDL操作就不会Waiting for table metadata lock。

-- 从information_schema.innodb_trx 表中查看当前未提交的事务
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

字段意义： trx_state: 事务状态，一般为RUNNING。 trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理。 trx_mysql_thread_id: MySQL的线程ID，用于kill。 trx_query: 事务中的sql。

调整锁超时阈值，lock_wait_timeout表示获取metadata lock的超时（单位为秒），允许的值范围为1到31536000（1年）。默认值为31536000。

set session lock_wait_timeout = 1800;
set global lock_wait_timeout = 1800;

5.开启慢查询日志

# 可以看到慢查询日志的状态（默认为关闭状态）和默认的慢查询日志的文件
show variables like "%slow_query_log%";

# 手动开启慢查询日志
set global slow_query_log=on;
  
# 修改mysql配置文件保证永久生效，由于慢查询日志记录的信息比较多，会影响mysql的性能，所以生产环境不建议长期开启。修改完配置重启即可生效
vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/slow-query.log
long_query_time=1   # 慢查询日志的时间定义（秒），默认为10秒，多久就算慢查询的日志
log_queries_not_using_indexes=1    # 将所有没有使用带索引的查询语句全部写到慢查询日志中

6.主键ID

无特殊需求下Innodb建议使用与业务无关的自增ID作为主键。InnoDB引擎使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。 mysql在频繁的更新、删除操作，会产生碎片。而含碎片比较大的表，查询效率会降低。此时需对表进行优化，这样才会使查询变得更有效率。

7.热点数据问题

问题：流量集中，达到物理网卡上限。请求过多，缓存服务被打垮。DB击穿，引起业务雪崩。解决方案：读写分离方案，SLB层做负载均衡。Proxy层（多台）做读写分离自动路由，Master负责写请求，ReadOnly节点（多台）负责读请求，Slave节点和Master节点做高可用。

8.在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。

优化过程大致如下： (1)根据搜索条件，找出所有可能使用的索引。 (2)计算全表扫描的代价。 (3)计算使用不同索引执行查询的代价。 (4)对比各种执行方案的代价，找出成本最低的那一个。

9.SQL解析

from后面的表关联是自右向左解析，而where条件的解析顺序是自下而上的。也就是说，在写SQL语句的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表），而把能筛选出小量数据的条件放在where语句的最左边（用小表去匹配大表）。

10.使用explain优化sql和索引

select_type：表示查询中每个select子句的类型:
    SIMPLE：简单SELECT,不使用UNION或子查询等。
    PRIMARY：查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY。
    UNION：UNION中的第二个或后面的SELECT语句。
    DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询。
    UNION RESULT：UNION的结果。
    SUBQUERY：子查询中的第一个SELECT。
    DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询。
    DERIVED：派生表的SELECT, FROM子句的子查询。
    UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行。
table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为NULL、system、const、eq_reg、ref、range、index和ALL。
    all：full table scan ;MySQL将遍历全表以找到匹配的行；
    index: index scan; index和all的区别在index类型只遍历索引，注意是遍历了。
    range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见于between等查询；
    ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；
    eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；
    const，system：当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，MySQL就能将该查询转化为一个常量。
    NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MySQL认为必须检查的用来返回请求数据的行数
extra：关于MySQL如何解析查询的额外信息。

11.key_len计算规则

一般地，key_len等于索引列类型字节长度，例如int类型为4-bytes，bigint为8bytes；如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90bytes；若该列类型定义时允许NULL，其key_len还需要再加1 bytes；若该列类型为变长类型，例如VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其key_len还需要再加2bytes。

12.SQL语句优化建议

使用索引相关：

(1)对查询进行优化，应尽量避免全表扫描，首先应考虑在where、order by、group by涉及的列上建立索引。 (2)前导模糊查询不能使用索引，如：select account from member where nickname like ‘%XXX%’ 或者 select account from member where nickname like ‘%XXX’。建议使用select account from member where nickname like ‘XXX%’，如果必须要用到做查询，需要评估对当前表全表扫描造成的后果。 (3)建立索引的列不能为null，使用not null约束及默认值。当数据列被声明为NOT NULL以后，在查询的时候就不需要判断是否为NULL，由于减少了判断，可以降低复杂性，提高查询速度。避免使用null做为判断条件，如：select account from member where nickname is null。建议在设计字段时尽量将字段的默认值设为0，改为select account where nickname = 0; (4)负条件查询不能使用索引，其中负条件有!=、<>、not in、not exists、not like等，建议使用 <,<=,=,>,>=,between等。存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置。 (5)使用or做为连接条件不能使用索引。如：select account from member where id = 1 or id = 2; 建议使用union all,改为 select account from member where id = 1 union all select account from member where id = 2; (6)联合索引最左前缀原则，又叫最左侧查询，如果在（a，b，c）三个字段上建立联合索引，那么它能够加快a|（a，b）|（a，b，c）三组的查询速度。建立联合查询时，区分度最高的字段在最左边。如果建立了（a，b）联合索引，就不必再单独建立a索引。同理，如果建立了（a，b，c）索引就不必再建立a，（a，b）索引。 (7)范围列可以用到索引，但是范围列后面的列无法用到索引。索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：<、<=、>、>=、between等。 (8)把计算放到业务层而不是数据库层。在字段上计算不能命中索引。不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将不能正确使用索引。对字段有操作时也会引起全表扫描，如select account where salary * 0.8 = 1000 或者 select account where sustring(nickname,1,3) = ‘aaa’; (9)强制类型转换会全表扫描，如果phone字段是varcher类型，则下面的SQL不能命中索引。Select * fromuser where phone=13800001234 (10)并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。“性别”这种区分度不太大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。一般区分度在80%以上就可以建立索引。区分度可以使用count（distinct（列名））/count（）来计算。 (10)索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。更新十分频繁、数据区分度不高的字段上不宜建立索引。更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。 (11)利用覆盖索引来进行查询操作，避免回表。被查询的列，数据能从索引中取得，而不是通过定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速度查询。 (12)超过三个表最好不要用join，需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。 (13)如果排序字段没有用到索引，就尽量少排序。 (14)应尽可能的避免更新clustered索引数据列，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为clustered索引。 (15)使用count()时，如select count(*) from member。建议使用select count(1) from member。能使用主键索引。 (16)使用参数做为查询条件时，如select account from member where nickname = @name；由于SQL语句在编译执行时并不确定参数，这将无法通过索引进行数据查询，所以尽量避免。 (17)使用in和not in时不能使用索引。(存疑？) 如：select account from member where id in (1,2,3)。使用not in时，如select account where id not in (1,2,3)。如果是连续数据，可以改为select account where id between 1 and 3;当数据较少时也可以参考union用法；或者：select account from member where id in (select accountid from department where id = 3 )，可以改为select account from member where id exsits (select accountid from department where id = 3)。not in 可以对应 not exists;

其他优化：

(1)任何地方都不要使用select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。 (2)利用延迟关联或者子查询优化超多分页场景，MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率非常低下，要么控制返回的总数，要么对超过特定阈值的页进行SQL改写。使用合理的分页提高效率。select id,name from product limit 866613, 20。使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：select id,name from product where id> 866612 limit 20。 (3)尽量用union all代替union。Union需要将集合合并后在进行唯一性过滤操作，这会涉及到排序，大量的cpu运算，加大资源消耗及延迟，当然，使用union all的前提条件是两个结果集没有重复数据。 (4)避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table ，然后drop table ，这样可以避免系统表的较长时间锁定。 (5)在所有的存储过程和触发器的开始处设置SET NOCOUNT ON ，在结束时设置SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。 (6)尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。 (7)尽量避免大事务操作，提高系统并发能力。 (8)尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。 (9)尽可能的使用varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。 (10)切分查询。将一个对服务器压力较大的任务，分解到一个较长的时间中，并分多次执行。如要删除一万条数据，可以分10次执行，每次执行完成后暂停一段时间，再继续执行。过程中可以释放服务器资源给其他任务。 (11)分解关联查询。将多表关联查询的一次查询，分解成对单表的多次查询。可以减少锁竞争，查询本身的查询效率也比较高。因为MySql的连接和断开都是轻量级的操作，不会由于查询拆分为多次，造成效率问题。关联查询延迟关联，可以根据查询条件先缩小各自要查询的范围，再关联。

13.group by优化

CREATE TABLE `t_user_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `viewed_user_id` bigint(20) DEFAULT NULL COMMENT '被查看用户id',
  `viewed_user_sex` tinyint(1) DEFAULT NULL COMMENT '被查看用户性别',
  `viewed_user_age` int(5) DEFAULT NULL COMMENT '被查看用户年龄',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `update_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_viewed_user` (`user_id`,`viewed_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// user_id=1的用户分组统计该用户查看的年龄在18 ~ 22之间的女性用户的数量
SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

如果此时，t_user_view这张表的记录数达到千万规模，想必这条SQL的查询效率会直线下降，为什么呢？有什么办法优化呢？先用explain看一下这条SQL：

EXPLAIN SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

在Extra这一列中出现了三个Using，这3个Using代表了group by语句分别经历了3个执行阶段：

Using where：通过搜索可能的idx_user_viewed_user索引树定位到满足部分条件的viewed_user_id，然后，回表继续查找满足其他条件的记录
Using temporary：使用临时表暂存待groupBy分组及统计字段信息
Using filesort：使用sort_buffer对分组字段进行排序

临时表：

这是MySQL连接线程可以独立访问和处理的内存区域。这条包含group by语句的SQL，其中包含一个分组字段viewed_user_age和一个统计字段count(*)，这两个字段是这条SQL中统计所需的部分，如果我们要做这样一个统计和分组，并把结果固化下来，肯定是需要一个内存或磁盘区域落下第一次统计的结果，然后，以这个结果做下一次的统计，因此，像这种存储中间结果，并以此结果做进一步处理的区域，MySQL叫它临时表。刚刚提到既可以将中间结果落在内存，也可以将这个结果落在磁盘，因此，在MySQL中就出现了两种临时表：内存临时表和磁盘临时表。内存临时表：在早期数据量不是很大的时候，以存储分组及统计字段为例，那么基本上内存就可以完全存放下分组及统计字段对应的所有值，这个存放大小由tmp_table_size参数决定。这时候，这个存放值的内存区域，MySQL就叫它内存临时表。此时，或许你已经觉得MySQL将中间结果存放在内存临时表，性能已经有了保障，但是内存频繁的存取会产生碎片，为此，MySQL设计了一套新的内存分配和释放机制，可以减少甚至避免临时表内存碎片，提升内存临时表的利用率。磁盘临时表：当分组及统计字段对应的所有值大小超过tmp_table_size决定的值，那么，MySQL将使用磁盘来存储这些值。这个存放值的磁盘区域，MySQL叫它磁盘临时表。我们都知道磁盘存取的性能一定比内存存取的性能差很多，因为会产生磁盘IO，所以，一旦分组及统计字段不得不写入磁盘，那性能相对是很差的，所以，我们尽量调大参数tmp_table_size，使得组及统计字段可以在内存临时表中处理。

执行过程：

无论是使用内存临时表，还是磁盘临时表，临时表对组及统计字段的处理的方式都是一样的。该过程经历了4个部分：idx_user_viewed_user、cluster_index、temporary和sort_buffer，对比上面explain的结果，其中前2个就对应结果中的Using where，temporary对应的是Using temporary，sort_buffer对应的是Using filesort。

(1)创建临时表temporary，表里有两个字段viewed_user_age和count()，主键是viewed_user_age，如上图，倒数第二个框temporary表示临时表，框中包含两个字段viewed_user_age和count()，框内就是这两个字段对应的值，其中viewed_user_age就是这张临时表的主键。 (2)扫描表辅助索引树idx_user_viewed_user，依次取出叶子节点上的id值，即从索引树叶子节点中取到表的主键id。如上图中的idx_user_viewed_user框就是索引树，框右侧的箭头表示取到表的主键id。 (3)根据主键id到聚簇索引cluster_index的叶子节点中查找记录，即扫描cluster_index叶子节点： (3.1)得到一条记录，然后取到记录中的viewed_user_age字段值。如上图，cluster_index框，框中最右边的一列就是viewed_user_age字段的值。 (3.2)如果临时表中没有主键为viewed_user_age的行，就插入一条记录 (viewed_user_age, 1)。如上图的temporary框，其左侧箭头表示将cluster_index框中的viewed_user_age字段值写入temporary临时表。 (3.3)如果临时表中有主键为viewed_user_age的行，就将viewed_user_age这一行的count()值加 1。如上图的temporary框。 (4)遍历完成后，再根据字段viewed_user_age在sort_buffer中做排序，得到结果集返回给客户端。如上图中的最右边的箭头，表示将temporary框中的viewed_user_age和count()的值写入sort_buffer，然后，在sort_buffer中按viewed_user_age字段进行排序

优化方案：

既然这条SQL执行需要经历4个部分，那么，我们可不可以去掉最后两部分呢，即去掉temporary和sort_buffer？答案是可以的，我们只要给SQL中的表t_user_view添加如下索引：

ALTER TABLE `t_user_view` ADD INDEX `idx_user_age_sex` (`user_id`, `viewed_user_age`, `viewed_user_sex`);

14.为什么select *效率低下

(1)不需要的列会增加数据传输时间和网络开销

用“SELECT * ”数据库需要解析更多的对象、字段、权限、属性等相关内容，在SQL语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。增大网络开销；* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。如果DB和应用程序不在同一台机器，这种开销非常明显。即使mysql 服务器和客户端是在同一台机器上，使用的协议还是tcp，通信也是需要额外的时间。

(2)对于无用的大字段，如varchar、blob、text，会增加IO操作

准确来说，长度超过728字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次io操作。（MySQL InnoDB）

(3)失去MySQL优化器“覆盖索引”策略优化的可能性

SELECT * 杜绝了覆盖索引的可能性，而基于MySQL优化器的“覆盖索引”策略又是速度极快，效率极高，业界极为推荐的查询优化方式。例如，有一个表为t(a,b,c,d,e,f)，其中，a为主键，b列有索引。那么，在磁盘上有两棵B+树，即聚集索引和辅助索引（包括单列索引、联合索引），分别保存(a,b,c,d,e,f)和(a,b)，如果查询条件中where条件可以通过b列的索引过滤掉一部分记录，查询就会先走辅助索引，如果用户只需要a列和b列的数据，直接通过辅助索引就可以知道用户查询的数据。如果用户使用select *，获取了不需要的数据，则首先通过辅助索引过滤数据，然后再通过聚集索引获取所有的列，这就多了一次b+树查询，速度必然会慢很多。由于辅助索引的数据比聚集索引少很多，很多情况下，通过辅助索引进行覆盖索引（通过索引就能获取用户需要的所有列），都不需要读磁盘，直接从内存取，而聚集索引很可能数据在磁盘（外存）中（取决于buffer pool的大小和命中率），这种情况下，一个是内存读，一个是磁盘读，速度差异就很显著了，几乎是数量级的差异。

15.数据量很大的情况下，对于分页查询有什么优化方案？

针对查询偏移量的测试：

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;

三次查询时间如下：

查询100偏移：25ms 24ms 24ms
查询1000偏移：78ms 76ms 77ms
查询10000偏移：3092ms 3212ms 3128ms
查询100000偏移：3878ms 3812ms 3798ms
查询1000000偏移：14608ms 14062ms 14700ms

随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

使用子查询优化：这种方式先定位偏移位置的id，然后往后查询，这种方式适用于id递增的情况。先使用范围查询定位id（或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先select id，然后再select *；

select * from orders_history where type=8 and id>=(select id from orders_history where type=8 limit 100000,1) limit 100;

16.对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的我都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

17.MySQL数据库cpu飙升到500%的话他怎么处理？

当cpu飙升到500%时，先用操作系统命令top命令观察是不是mysqld占用导致的，如果不是，找出占用高的进程，并进行相关处理。如果是mysqld造成的， show processlist，看看里面跑的session情况，是不是有消耗资源的sql在运行。找出消耗高的sql，看看执行计划是否准确， index是否缺失，或者实在是数据量太大造成。一般来说，肯定要kill掉这些线程(同时观察cpu使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些SQL。也有可能是每个sql消耗资源并不多，但是突然之间，有大量的session连进来导致cpu飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

N.参考

(1)【178期】面试官：谈谈在做项目过程中，你是是如何进行SQL优化的

(2)最官方的mysql explain type字段解读

数据库架构

1.主从复制

(1)用途及条件

用途：

做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

必要条件：

主库开启binlog日志（设置log-bin参数）。主从server-id不同。从库服务器能连通主库

(2)主从复制方式：

同步复制：

所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。这样，显然不可取，也不是MySQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。

异步复制：

如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心，MySQL的默认设置。 MySQL的复制默认是异步的，MySQL主从异步复制是最常见的复制场景。数据的完整性依赖于主库BINLOG的不丢失，只要主库的BINLOG不丢失，那么就算主库宕机了，我们还可以通过BINLOG把丢失的部分数据通过手工同步到从库上去。注意：主库宕机的情况下，DBA可以通过mysqlbinlog工具手工访问主库binlog，抽取缺失的日志并同步到从库上去；也可以通过配置高可用MHA架构来自动抽取缺失的数据补全从库，或者启用Global Transaction Identifiers（GTID)来自动抽取缺失binlog到从库。 MySQL在BINLOG中记录事务(或SQL语句),也就是说对于支持事务的的引擎(例如InnoDB)来说，每个事务提交时都需要写BINLOG；对于不支持事务的引擎(例如MyISAM)来说，每个SQL语句执行完成时，都需要写BINLOG。为了保证Binlog的安全，MySQL引入sync_binlog参数来控制BINLOG刷新到磁盘的频率。在默认情况下，sync_binlog=1，表示事务提交之前，MySQL都需要先把BINLOG刷新到磁盘，这样的话，即使出现数据库主机操作系统崩溃或者主机突然掉电的情况，系统最多损失prepared状态的事务；设置sync_binlog=1，尽可能保证数据安全。 sync_binlog=0,表示MySQL不控制binlog的刷新，由文件系统自己控制文件缓存的刷新。 sync_binlog=N,如果N不等于0或者1，刷新方式同sync_binlog=1类似，只不过此时会延长刷新频率至N次binlog提交组之后。以上是传统的异步复制，在MySQL5.7的并行复制技术(也称多线程复制)到来之前，为人诟病最多的还是效率问题，slave延迟是一个顽疾，虽然之前已经出现了schema级别的并行复制，但实际效果并不好。

多线程复制（并行复制，也是异步复制）：

在MySQL5.7中，带来了全新的多线程复制技术，解决了当master同一个schema下（同一个DB）的数据发生了变更，从库不能并发应用的问题，同时也真正将binlog组提交的优势充分发挥出来，保障了从库并发应用Relay Log的能力。在MySQL8.0中，多线程复制又进行了技术更新，引入了writeset的概念，而在之前的版本中，如果主库的同一个会话顺序执行多个不同相关对象的事务，例如，先执行了Update A表的数据，又执行了Update B表的数据，那么BINLOG在复制到从库后，这两个事务是不能并行执行的，writeset的到来，突破了这个限制。社区版5.6中新增。并行是指从库多线程apply binlog库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置设置sql线程数为10，set global slave_parallel_workers=10;

半同步复制：

master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MySQL引入的。 5.5集成到mysql，以插件的形式存在，需要单独安装。确保事务提交后binlog至少传输到一个从库。不保证从库应用完这个事务的binlog。性能有一定的降低，响应时间会更长。网络异常或从库宕机，卡住主库，直到超时或从库恢复。

增强半同步复制：

前面介绍的复制是异步操作，主库和从库的数据之间难免会存在一定的延迟，这样存在一个隐患：当在主库上写入一个事务并提交成功，而从库尚未得到主库的BINLOG日志时，主库由于磁盘损坏、内存故障、断电等原因意外宕机，导致主库上该事务BINLOG丢失，此时从库就会损失这个事务，从而造成主从不一致。为了解决这个问题，从MySQL5.5开始，引入了半同步复制，此时的技术暂且称之为传统的半同步复制，因该技术发展到MySQL5.7后，已经演变为增强半同步复制(也成为无损复制)。在异步复制时，主库执行Commit提交操作并写入BINLOG日志后即可成功返回客户端，无需等待BINLOG日志传送给从库，而半同步复制时，为了保证主库上的每一个BINLOG事务都能够被可靠地复制到从库上，主库在每次事务成功提交时，并不及时反馈给前端应用用户，而是等待至少一个从库(详见参数rpl_semi_sync_master_wait_for_slave_count)也接收到BINLOG事务并成功写入中继日志后，主库才返回Commit操作成功给客户端。半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的BINLOG日志上，另一份在至少一个从库的中继日志Relay Log上，从而更进一步保证了数据的完整性。在传统的半同步复制中，主库写数据到BINLOG，且执行Commit操作后，会一直等待从库的ACK，即从库写入Relay Log后，并将数据落盘，返回给主库消息，通知主库可以返回前端应用操作成功，这样会出现一个问题，就是实际上主库已经将该事务Commit到了事务引擎层，应用已经可以可以看到数据发生了变化，只是在等待返回而已，如果此时主库宕机，有可能从库还没能写入Relay Log，就会发生主从库不一致。增强半同步复制就是为了解决这个问题，做了微调，即主库写数据到BINLOG后，就开始等待从库的应答ACK，直到至少一个从库写入Relay Log后，并将数据落盘，然后返回给主库消息，通知主库可以执行Commit操作，然后主库开始提交到事务引擎层，应用此时可以看到数据发生了变化。半同步复制模式下，假如在传送BINLOG日志到从库时，从库宕机或者网络延迟，导致BINLOG并没有即使地传送到从库上，此时主库上的事务会等待一段时间(时间长短由参数rpl_semi_sync_master_timeout设置的毫秒数决定)，如果BINLOG在这段时间内都无法成功发送到从库上，则MySQL自动调整复制为异步模式，事务正常返回提交结果给客户端。半同步复制很大程度上取决于主从库之间的网络情况，往返时延RTT越小决定了从库的实时性越好。通俗地说，主从库之间的网络越快，从库越实时。

(3)原理

步骤一：主库db的更新事件(update、insert、delete)被写到binlog。
步骤二：从库发起连接，连接到主库。
步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库。
步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log。
步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db。

对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。

(4)存在问题及解决方法

存在问题：主库宕机后，数据可能丢失。从库只有一个sql Thread，主库写压力大时，复制很可能延时。解决方法：半同步复制—解决数据丢失的问题。并行复制—-解决从库复制延迟的问题。

(5)Q&A：

(a)master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。因此slave不应该进行写操作，至少是slave上涉及到复制的数据库不可以写。实际上，这里已经揭示了读写分离的概念。

(b)主从复制中，可以有N个slave,可是这些slave又不能进行写操作，要它们干嘛？实现数据备份：类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。异地容灾：比如master在北京，地震挂了，那么在上海的slave还可以继续。主要用于实现scale out,分担负载,可以将读的任务分散到slaves上。

(c)主从复制中有master,slave1,slave2,…等等这么多MySQL数据库，那比如一个JAVA WEB应用到底应该连接哪个数据库? 在应用程序中可以这样，insert/delete/update这些更新数据库的操作，用connection(for master)进行操作， select用connection(for slaves)进行操作。那我们的应用程序还要完成怎么从slaves选择一个来执行select，例如使用简单的轮循算法。这样的话，相当于应用程序完成了SQL语句的路由，而且与MySQL的主从复制架构非常关联，一旦master挂了，某些slave挂了，那么应用程序就要修改了。能不能让应用程序与MySQL的主从复制架构没有什么太多关系呢？找一个组件，应用只需要与它打交道，用它来完成MySQL的代理，实现SQL语句的路由。MySQL proxy并不负责，怎么从众多的slaves挑一个？可以交给另一个组件(比如haproxy)来完成。这就是所谓的MySQL的读写分离。

(d)当master的二进制日志每产生一个事件，都需要发往slave，如果我们有N个slave,那是发N次，还是只发一次？如果只发一次，发给了slave-1，那slave-2,slave-3,…它们怎么办？显然应该发N次。实际上，在MySQL master内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作，还的维护N个线程，负担会很重。可以这样，slave-1是master的从，slave-1又是slave-2,slave-3,…的主，同时slave-1不再负责select。slave-1将master的复制线程的负担，转移到自己的身上。这就是所谓的多级复制的概念。

(e)当一个select发往MySQL proxy，可能这次由slave-2响应，下次由slave-3响应，这样的话，就无法利用查询缓存了。应该找一个共享式的缓存，比如memcache来解决。将slave-2,slave-3,…这些查询的结果都缓存至mamcache中。

(f)随着应用的日益增长，读操作很多，我们可以扩展slave，但是如果master满足不了写操作了，怎么办呢？主从复制架构已经满足不了。可以分库（垂直拆分），分表（水平拆分）。

(5)其他

(a)连接结构

一主一从主主复制一主多从—扩展系统读取的性能，因为读是在从库读取的；多主一从—5.7开始支持联级复制

(b)部分数据复制

主库添加参数：

binlog_do_db=db1
binlog_ignore_db=db1
binlog_ignore_db=db2

或从库添加参数

replicate_do_db=db1
replicate_ignore_db=db1
replicate_do_table=db1.t1
replicate_wild_do_table=db%.%
replicate_wild_ignore_table=db1.%

(c)联级复制

A->B->C，B中添加参数：

log_slave_updates  #B将把A的binlog记录到自己的binlog日志中

(d)复制的监控

show slave status \G

(e)复制出错处理

1062（主键冲突），1032（记录不存在）。解决：手动处理。跳过复制错误：set global sql_slave_skip_counter=1;跳过由单条SQL（event）组成的事务。

2.mysql高并发环境解决方案

需求分析：大量数据读取写入，并发性高。
现有解决方式：水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力。
集群方案：解决DB宕机带来的单点DB不能访问问题。
读写分离策略：极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力。

3.微服务数据库设计

微服务设计的一个关键是数据库设计，基本原则是每个服务都有自己单独的数据库，而且只有微服务本身可以访问这个数据库。它是基于下面三个原因：优化服务接口、错误诊断困难、性能调优。理想的设计是你的数据库只有你的服务能访问，你也只调用自己数据库中的数据，所有对别的微服务的访问都通过服务调用来实现。当然，在实际应用中，单纯的服务调用可能不能满足性能或其他要求，不同的微服务都多少需要共享一些数据。

数据库向后兼容：好处是当程序部署出现问题时，如需进行回滚。只要回滚程序就行了，而不必回滚数据库。回滚时一般只回滚一个版本。凡是需要删除的表或字段在本次部署时都不做修改，等到一个或几个版本之后，确认没有问题了再删除。它的另一个好处就是不会对其他微服务中的共享表产生立刻的直接影响。当本微服务升级后，其他微服务可以评估这些数据库更新带来的影响再决定是否需要做相应的程序或数据库修改。

增加表或字段：如果字段可取空值，这个操作是向后兼容的。如果是非空值就要插入一个缺省值。
删除表或字段：可先暂时保留被删除表或字段，经过几个版本之后再删除。
修改字段名：新增加一个字段，把数据从旧字段拷贝到新字段，用数据库触发器（或程序）同步旧字段和新字段（供过渡时期使用）。然后再在几个版本之后把原来的字段删除（请参阅Update your Database Schema Without Downtime）。
修改表名：如果数据库支持可更新视图，最简单的办法是先修改表的名字，然后创建一个可更新视图指向原来的表（请参阅Evolutionary Database Design ）。如果数据库不支持可更新视图，使用的方法与修改字段名相似，需要创建新的表并做数据同步。
修改字段类型：与修改字段名几乎相同，只是在拷贝数据时，需要做数据类型转换。

跨服务事物：微服务的一个难点是如何实现跨服务的事物支持。两阶段提交（Two-Phase Commit）已被证明性能上不能满足需求，现在基本上没有人用。被一致认可的方法叫Saga。它的原理是为事物中的每个操作写一个补偿操作（Compensating Transaction），然后在回滚阶段挨个执行每一个补偿操作。事物中的每一个操作（正向操作和补偿操作）都被包装成一个命令（Command），Saga执行协调器（Saga Execution Coordinator (SEC)）负责执行所有命令。在执行之前，所有的命令都会按顺序被存入日志中，然后Saga执行协调器从日志中取出命令，依次执行。当某个执行出现错误时，这个错误也被写入日志，并且所有正在执行的命令被停止，开始回滚操作。 Saga放松了对一致性（Consistency）的要求，它能保证的是最终一致性（Eventual Consistency），因此在事物执行过程中数据是不一致的，并且这种不一致会被别的进程看到。在生活中，大多数情况下，我们对一致性的要求并没有那么高，短暂的不一致性是可以接收的。例如银行的转账操作，它们在执行过程中都不是在一个数据库事物里执行的，而是用记账的方式分成两个动作来执行，保证的也是最终一致性。 Saga的原理看起来很简单，但要想正确的实施还是有一定难度的。它的核心问题在于对错误的处理，要把它完全讲明白需要另写一遍文章，我现在只讲一下要点。网络环境是不可靠的，正在执行的命令可能很长时间都没有返回结果，这时，第一，你要设定一个超时。第二，因为你不知道没有返回值的原因是，已经完成了命令但网络出了问题，还是没完成就牺牲了，因此不知道是否要执行补偿操作。这时正确的做法是重试原命令，直到得到完成确认，然后再执行补偿操作。但这对命令有一个要求，那就是这个操作必须是幂等的（Idempotent），也就是说它可以执行多次，但最终结果还是一样的。另外，有些操作的补偿操作比较容易生成，例如付款操作，你只要把钱款退回就可以了。但有些操作，像发邮件，完成之后就没有办法回到之前的状态了，这时就只能再发一个邮件更正以前的信息。因此补偿操作不一定非要返回到原来的状态，而是抵消掉原来操作产生的效果。

微服务的拆分：我们原来的程序大多数都是单体程序，但现在要把它拆分成微服务，应该怎样做才能降低对现有应用的影响呢？假设我们要拆分出来一个微服务叫“client-service”，它需要访问“core client”表。第一步，我们先把程序从原来的代码里拆分出来，变成一个服务. 数据库不动，这个服务仍然指向原来的数据库。其他程序不再直接访问这个服务管理的表，而是通过服务调用或另建共享表来获取数据。第二步，再把服务的数据库表拆分出来，这时微服务就拥有它自己的数据库了，而不再需要原来的共享数据库了。这时就成了一个真正意义上的的微服务。上面只讲了拆分一个微服务，如果有多个需要拆分，则需一个一个按照上面讲的方法依次进行。 Martin Fowler在他的文章”Break Monolith into Microservices”里有一个很好的建议。那就是，当你把服务从单体程序里拆分时，不要只想着把代码拆分出来。因为现在的需求可能已经跟原来有所不同，原先的设计可能也不太适用了。而且，技术也已更新，代码也要作相应的改造。更好的办法是重写原来的功能（而不是重写原来的代码），把重点放在拆分业务功能上，而不是拆分代码上，用新的设计和技术来实现这个业务功能。

结论：数据库设计是微服务设计的一个关键点，基本原则是每个微服务都有自己单独的数据库，而且只有微服务本身可以访问这个数据库。微服务之间的数据共享可以通过服务调用，或者主、从表的方式实现。在共享数据时，要找到合适的同步方式。在微服务架构中，数据库的修改影响广泛，需要保证这种修改是向后兼容的。实现跨服务事物的标准方法是Saga。当把单体程序拆分成微服务时，可以分步进行，以减少对现有程序的影响。

4.数据库、缓存最终一致性

存在问题：存储的数据随着时间可能会发生变化，而缓存中的数据就会不一致。具体能容忍的不一致时间，需要具体业务具体分析，但是通常的业务，都需要做到最终一致。

方案一：通过key的过期时间，mysql更新时，redis不更新。这种方式实现简单，但不一致的时间会很长。如果读请求非常频繁，且过期时间比较长，则会产生很多长期的脏数据。优点，开发成本低，易于实现；管理成本低，出问题的概率会比较小。不足，完全依赖过期时间，时间太短容易缓存频繁失效，太长容易有长时间更新延迟（不一致）

方案二：在方案一的基础上扩展，通过key的过期时间兜底，并且，在更新mysql时，同时更新redis。同时更新redis。优点，相对方案一，更新延迟更小。不足，如果更新mysql成功，更新redis却失败，就退化到了方案一；在高并发场景，业务server需要和mysql,redis同时进行连接。这样是损耗双倍的连接资源，容易造成连接数过多的问题。

方案三：针对方案二的同步写redis进行优化，增加消息队列，将redis更新操作交给kafka，由消息队列保证可靠性，再搭建一个消费服务，来异步更新redis。优点，消息队列可以用一个句柄，很多消息队列客户端还支持本地缓存发送，有效解决了方案二连接数过多的问题；使用消息队列，实现了逻辑上的解耦；消息队列本身具有可靠性，通过手动提交等手段，可以至少一次消费到redis。不足，依旧解决不了时序性问题，如果多台业务服务器分别处理针对同一行数据的两条请求，例如，a = 1；a = 5;，如果mysql中是第一条先执行，而进入kafka的顺序是第二条先执行，那么数据就会产生不一致。引入了消息队列，同时要增加服务消费消息，成本较高。

方案四：通过订阅binlog来更新redis，把我们搭建的消费服务，作为mysql的一个slave，订阅binlog，解析出更新内容，再更新到redis。优点，在mysql压力不大情况下，延迟较低；和业务完全解耦；解决了时序性问题。缺点，要单独搭建一个同步服务，并且引入binlog同步机制，成本较大。

首先确认产品上对延迟性的要求，如果要求极高，且数据有可能变化，别用缓存。方案1就够了，因为能用缓存方案，通常是读多写少场景，同时业务上对延迟具有一定的包容性。方案1没有开发成本，其实比较实用。如果想增加更新时的即时性，就选择方案2，不过没必要做重试保证之类的。方案3，方案4针对于对延时要求比较高业务，一个是推模式，一个是拉模式，而方案4具备更强的可靠性，既然都愿意花功夫做处理消息的逻辑，不如一步到位，用方案4。

5.大表怎么优化？某个表有近千万数据，CRUD比较慢，如何优化？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
还有就是通过分库分表的方式进行优化，主要有垂直分表和水平分表

6.分库分表了是怎么做的？

客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

7.分库分表后面临的问题

(1)事务支持：分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

(2)跨库join：只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

(3)跨节点的count,order by,group by以及聚合函数问题：这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

(4)数据迁移，容量规划，扩容等问题：来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。

(5)ID问题：一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由。

(6)跨分片的排序分页：一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。

7.读写分离有哪些解决方案？

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读（如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave）。

(1)使用mysql-proxy代理

优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，mysql官方不建议实际生产中使用。缺点：降低性能，不支持事务。

(2)用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。

如果采用了mybatis，可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select 都访问salve库，这样对于dao层都是透明。 plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题，也就是不支持事务，所以我们还需要重写一下DataSourceTransactionManager，将read-only的事务扔进读库，其余的有读有写的扔进写库。

(3)使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务。

缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

Oracle

1.常用SQL

-- 查看版本
select * from v$version;

-- 创建用户
CREATE USER OT IDENTIFIED BY Orcl1234;

-- 给用户赋予connect、resource和dba三种标准角色。
-- Connect 角色，是授予最终用户的典型权利，最基本的权利，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。
-- Resource 角色，是授予开发人员的，能在自己的方案中创建表、序列、视图等。
-- DBA角色，是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限。
GRANT CONNECT, RESOURCE, DBA TO OT;

-- 赋予表空间权限
ALTER USER "ABC" DEFAULT tablespace "TABLESPACE_ABC";
ALTER USER "ABC" QUOTA UNLIMITED ON "TABLESPACE_ABC";

-- 使用OT用户帐户连接到数据库(ORCL)
CONNECT ot@orcl

-- 执行某个SQL脚本
@F:\mypath\tbl_modify.sql

-- 使用OT用户登录，查看当前用户能访问的表
select owner, tablespace_name, table_name from all_tables order by owner, tablespace_name, table_name;

-- 查询OT schema下的数据表名
select owner, table_name, tablespace_name from dba_tables where owner='OT' order by table_name;

-- 创建表
CREATE TABLE "OT"."T_USER" (
"ID" NUMBER(10,0) NOT NULL,
"LOGIN_ID" VARCHAR2(255),
"NAME" VARCHAR2(255),
"CREATE_TIME" DATE
);
COMMENT ON COLUMN "OT"."T_USER"."ID" IS '主键ID';
COMMENT ON COLUMN "OT"."T_USER"."LOGIN_ID" IS '登录ID';
COMMENT ON COLUMN "OT"."T_USER"."NAME" IS '姓名';
COMMENT ON COLUMN "OT"."T_USER"."CREATE_TIME" IS '创建时间';

ALTER TABLE "OT"."T_USER" ADD CONSTRAINT "PK_USER_ID" PRIMARY KEY ("ID");

-- 创建序列
create sequence SEQ_T_USER_ID
minvalue 1
maxvalue 1E28
start with 1
increment by 1
cache 20;

-- 查看表结构，只在命令行模式下可用
desc OT.T_USER;

-- 合并数据，判断B表和A表是否满足ON中条件，如果满足则用B表去更新A表，如果不满足，则将B表数据插入A表
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression])
WHEN MATCHED THEN
[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]

-- 从上到下递归查询
select txo.organization_id
from test.t_xtgl_organization txo
where txo.rec_status = '1'
start with txo.organization_id = #{orgId,jdbcType=VARCHAR}
connect by prior txo.organization_id = txo.parent_id

-- 查看表空间使用情况
select
a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"
from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name

-- 查看指定表空间各张表占用空间
select * from (
select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
from dba_segments t
where t.segment_type='TABLE'
group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t where t.TABLESPACE_NAME = 'XXX_DATA'
order by t.mb desc

-- 查看数据库DBLINK
select * from dba_db_links;
SELECT * FROM DBA_OBJECTS O WHERE o.object_type = 'DATABASE LINK';
SELECT DBMS_METADATA.GET_DDL('DB_LINK', O.object_name, o.owner) FROM DBA_OBJECTS o where o.object_type='DATABASE LINK';

-- 赋权限创建DBLINK
grant create database link, create public database link,drop public database link to PLATFORM;
revoke create database link, create public database link,drop public database link from PLATFORM;

-- public dblink
CREATE PUBLIC DATABASE LINK LINKTOSOMEDB1
CONNECT TO ZHANGHAO IDENTIFIED BY 123456
USING '192.20.1.1:1521/db1'

-- private dblink
CREATE DATABASE LINK LINKTOSOMEDB2
CONNECT TO ZHANGHAO IDENTIFIED BY 123456
USING '192.20.1.1:1521/db1'

-- 查看当前用户权限
select * from user_sys_privs
grant create procedure to ZHANGHAO;
grant create table to ZHANGHAO;
grant create view to ZHANGHAO;
grant SELECT ANY TABLE to ZHANGHAO;

-- 闪回数据查询，20分钟前
SELECT * FROM (select * from t_user as of timestamp sysdate - 20/1440) WHERE PROD_ID='1234';

2.去Oracle

使用AWS Schema Conversion Tool。

3.死锁问题

-- 查看
select * from v$locked_object;

-- 查看数据库锁表情况
SELECT S.USERNAME,
DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
O.OWNER,
O.OBJECT_NAME,
O.OBJECT_TYPE,
S.SID,
S.SERIAL#,
S.TERMINAL,
S.MACHINE,
S.PROGRAM,
S.OSUSER
FROM V$SESSION S,
V$LOCK L,
DBA_OBJECTS O
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID(+)
AND S.USERNAME IS NOT NULL
AND L.TYPE = 'TX';

-- kill
select 'alter system kill session' || ''''||trim(t2.sid)||','||trim(t2.serial#)||''';' from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;

-- 存储过程或者函数被锁，查询是否有正在执行的函数或存储过程所致对象锁
select name
from v$db_object_cache
where owner='PLATFORM'
and type in('PROCEDURE','FUNCTION')
and locks > 0
and pins > 0;

-- 解锁被锁存储过程包
SELECT
'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' SQL,
username,
program,
machine,
status
FROM
v$session
WHERE
SID IN (
SELECT
session_id
FROM
dba_ddl_locks
WHERE
name LIKE '%MR_GZFS%');

-- 查询ddl锁
select * from dba_ddl_locks;

-- 当alter system kill session杀不了session
ALTER SYSTEM disconnect session'245,24379' IMMEDIATE;

select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' sql,
        username,
        program,
        machine,
        status
from v$session where status = 'INACTIVE';

4.物化视图

-- 创建，去掉注释否则可能报错
CREATE MATERIALIZED VIEW VW_Table
BUILD IMMEDIATE  　　　　　　　　　　 --创建时立即刷新      
REFRESH FORCE  　　　　　　　　　　　 --如果可以快速刷新则进行快速刷新，否则完全刷新        
ON DEMAND   　　　　　　　　　　　　　 --刷新方式          
START WITH SYSDATE    　　　　　　　 --第一次刷新时间
NEXT  SYSDATE+1/(24)   　　　　　　　--刷新时间间隔
AS SELECT 1 id,'A'name FROM dual;

-- 查询物化视图定义
select * from DBA_MVIEWS where MVIEW_NAME='VW_TABLE';

-- 刷新
BEGIN
dbms_mview.refresh('VW_TABLE');
END;

-- 删除物化视图
DROP MATERIALIZED VIEW VW_TABLE;

5.AWR报告

AWR全称Automatic Workload Repository，自动负载信息库，是Oracle 10g版本后推出的一种性能收集和分析工具，提供了一个时间段内整个系统的报表数据。通过AWR报告，可以分析指定的时间段内数据库系统的性能。

# 使用sqlplus 
sys as sysdba
@?/rdbms/admin/awrrpt.sql

6.远程连接

sqlplus user/password@//ip:port/sid

7.执行计划

-- 查看执行计划
explain plan for + 目标SQL
select * from table(dbms_xplan.display)

8.数据迁移

(1)expdp导出

# 逻辑目录的创建
CREATE DIRECTORY DUMP_DIR AS 'F:\oracledata';
SELECT * FROM DBA_DIRECTORIES t WHERE t.DIRECTORY_NAME = 'DUMP_DIR';
DROP DIRECTORY DUMP_DIR;

# SYS用户给导出用户赋权限
GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO PLATFORM;

# 验证能否写入磁盘
DECLARE
    l_file UTL_FILE.FILE_TYPE;
BEGIN
    l_file := UTL_FILE.FOPEN('DUMP_DIR', 'testfile.txt', 'W');
    UTL_FILE.PUT_LINE(l_file, 'This is a test.');
    UTL_FILE.FCLOSE(l_file);
EXCEPTION
    WHEN OTHERS THEN
    UTL_FILE.FCLOSE(l_file);
    RAISE;
END;

# 切换到数据库所在安装目录下
cd D:\app\Administrator\product\11.2.0\dbhome_1\BIN
# 导出元数据与数据
./expdp 'platform/secret@ZHPTCS' schemas=XYDATA directory=DUMP_DIR dumpfile=XYDATA.DMP
# 只导出元数据
./expdp 'platform/secret@ZHPTCS' schemas=XYDATA directory=DUMP_DIR dumpfile=XYDATA_DDL.DMP CONTENT=METADATA_ONLY
# 不区分schema，导出全部DDL
./expdp 'platform/secret@ZHPTCS' directory=DUMP_DIR dumpfile=ALL_DDL.DMP FULL=Y CONTENT=METADATA_ONLY

# 要导出SQL格式的数据库对象，建议用PLSQL软件直接进行导出。

(2)impdp导入

# 创建表空间
CREATE TABLESPACE TBS_BIBUSER_DATA DATAFILE '/data/zhptcs/TBS_BIBUSER_DATA.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

# 对于存储空间很大的表空间，一个文件最多30G，需要多个存储文件
ALTER TABLESPACE TBS_BIBUSER_DATA
ADD DATAFILE '/data/zhptcs/TBS_BIBUSER_DATA02.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

# 创建用户
CREATE USER XYDATA IDENTIFIED BY secret
DEFAULT TABLESPACE TBS_BIBUSER_DATA
QUOTA UNLIMITED ON TBS_BIBUSER_DATA;

# 赋权限
grant connect to XYDATA;
grant dba to XYDATA;
grant resource to XYDATA;
grant unlimited tablespace to XYDATA;

# 通过NAS转移文件
sudo mv /data/nasdata/oracledata/XYDATA.dmp /data/localdata/

# 授予用户逻辑目录读写权限
CREATE DIRECTORY DUMP_DIR AS '/data/localdata';
GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO XYDATA;

# 磁盘要充分赋权限
sudo chmod 777 -R /data/*

# 配置Oracle环境变量
vim /etc/profile
export ORACLE_HOME=/u01/app/oracle/product/11.2/db_1

# 导入
cd /u01/app/oracle/product/11.2/db_1/bin
./impdp XYDATA/secret@localhost/ZHPTCS SCHEMAS=XYDATA DIRECTORY=DUMP_DIR DUMPFILE=XYDATA.DMP

N.参考

(1)易百Oracle教程

(2)Oracle转PostgreSQL

(3)AWS Schema Conversion Tool

(4)v$locked_object v$lock锁表的问题

(5)Oracle实体化视图

(6)学习Oracle这一篇就够了

(7)ORACLE AWR报告生成操作步骤

(8)IMPDP和EXPDP的介绍和使用方法

PostgreSQL

1.常用SQL

-- 查看版本
SELECT version();

-- 创建用户
create user tester with password '123456';

-- 创建数据库，并指定所属者
create database test owner tester;

-- 将数据库得权限，全部赋给某个用户
grant all on database test to tester;

-- psql连接数据库（Linux）
psql -h localhost -p 5432 -U tester test

-- 已连接后，打开某个数据库
\c test

-- 查看全部表名
select * from pg_tables where schemaname = 'test'

-- 查看关联，表、索引等
\d test.*

-- 查看表字段
\d test.t_training_class_config

-- 创建模式
create schema test authorization tester;

-- 建表
DROP TABLE IF EXISTS "test"."t_training_class_config";
CREATE TABLE "test"."t_training_class_config"
(
"id"            int4      NOT NULL,
"class_cd"      varchar(255) DEFAULT NULL,
"class_name"    varchar(255) DEFAULT NULL,
"type"          int4      NULL,
"class_status"  int4      NULL,
"expire_time"   timestamp NULL,
"deadline_time" timestamp NULL,
"operator"      varchar(255) DEFAULT NULL,
"data_status"   int4      NULL,
"create_time"   timestamp NULL,
"update_time"   timestamp NULL
);
COMMENT ON TABLE test.t_training_class_config IS '课程表';

COMMENT ON COLUMN "test"."t_training_class_config"."id" IS '主键ID';
COMMENT ON COLUMN "test"."t_training_class_config"."class_cd" IS '课程编号';
COMMENT ON COLUMN "test"."t_training_class_config"."class_name" IS '课程名称';
COMMENT ON COLUMN "test"."t_training_class_config"."type" IS '类型';
COMMENT ON COLUMN "test"."t_training_class_config"."class_status" IS '课程状态：0有效，1无效';
COMMENT ON COLUMN "test"."t_training_class_config"."expire_time" IS '失效时间';
COMMENT ON COLUMN "test"."t_training_class_config"."deadline_time" IS '截止完成时间';
COMMENT ON COLUMN "test"."t_training_class_config"."operator" IS '操作人';
COMMENT ON COLUMN "test"."t_training_class_config"."data_status" IS '数据状态：0有效，1删除';
COMMENT ON COLUMN "test"."t_training_class_config"."create_time" IS '创建时间';
COMMENT ON COLUMN "test"."t_training_class_config"."update_time" IS '更新时间';

ALTER TABLE "test"."t_training_class_config" ADD CONSTRAINT "t_training_class_config_pkey" PRIMARY KEY ("id");

-- 新增序列
DROP SEQUENCE if EXISTS "test"."seq_training_class_config_id";
CREATE SEQUENCE "test"."seq_training_class_config_id"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;

-- 自增主键
CREATE TABLE COMPANY(
ID  SERIAL PRIMARY KEY,
NAME           TEXT      NOT NULL,
AGE            INT       NOT NULL,
ADDRESS        CHAR(50),
SALARY         REAL
);

-- 普通查询
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

-- 从上到下递归查询
WITH RECURSIVE obj as (
SELECT organization_id, rec_status FROM t_xtgl_organization where organization_id = #{orgId,jdbcType=BIGINT}
UNION ALL
SELECT c.organization_id, c.rec_status FROM t_xtgl_organization c join obj on c.parent_id =
obj.organization_id
)
select organization_id from obj where
obj.rec_status = '1'

N.参考

(1)为什么“去O”唯有PG

(2)【干货总结】:可能是史上最全的MySQL和PGSQL的对比材料

(3)菜鸟PostgreSQL教程

(4)易百PostgreSQL教程

(5)Oracle迁移PostgreSQL经验总结(SQL部分)

(6)Oracle切换PostgreSQL遇到的问题