高性能MySQL

1. MySQL的架构

MySQL最重要最与众不同的就是它的存储引擎架构，这种架构设计将查询处理(QUERY PROCESSING)及其他系统任务和数据存储/提取相分离。在这里主要介绍MySQL的服务器架构、各种存储引擎之间的区别。

1.1 逻辑架构

MySQL各组件协同工作的示意图如下：

• 最上层的客户端不是MySQL独有的，像许多C/S结构都具有。
• 第二层架构是大多数MySQL的核心服务功能所在的一层，如查询解析、分析、优化、缓存和所有的内置函数，所有跨存储引擎的功能也在这一层实现：存储过程、视图、触发器等。
• 第三层包含了存储引擎，存储引擎负责MySQL中数据的存储和提取

1.1.1 连接管理与安全性

每个客户端连接到MySQL服务器进程有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU时间片中执行语句。服务器会负责缓存管理线程，因此不需要为每一个新建的连接创建或者销毁线程。

当有来自于客户端的连接请求时，服务器会对连接信息进行认证，认证基于用户名、密码和主机信息进行。

1.1.2 优化与执行

进行解析的过程：

• MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化：包括重写查询、决定表的读取顺序以及选择合适的索引等。
• 用户可以通过特殊的关键字提示优化器来影响它的优化决策过程，也可以请求优化器解释优化过程的各个因素，使用户可以知道服务器时如何进行优化决策的。
• 优化器不关心表使用哪种存储引擎，但存储引擎对优化查询是有影响的，因为优化器会请求存储引擎提供容量或某个具体操作的开销。

当然不要忘了还有查询缓存：

对于select语句，在解析查询之前，服务器会先检查查询缓存，如果能在查询缓存中找到对应查询，服务器就不必执行解析器、优化和执行的整个过程，直接返回查询缓存中的结果集即可。

1.2 并发控制

在MySQL当中是在两个面讨论的并发控制：服务器层和存储引擎层。在并发场景下，如果不对并发进行控制，则会导致数据的损坏无效，一般来说我们都是用锁lock来对其进行控制。在MySQL中的锁有共享锁(shared lock)、排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)

2. 锁

锁这个知识点比较繁杂，下图是对锁的各个概念范围进行分类：

2.1 锁类型（兼容性）

对于读取内容来说一般不会发生什么问题，因为本线程没有对数据进行修改，但是试想一下你正在对读的使用，另一个人正在对数据进行写入，这时候问题就来了，你读的数据会是确定呢吗?答案肯定是不确定的，因此对于读操作也要注意。而写操作就更不用说了，每一次的写入都会改变数据，必定加入排斥锁。在MySQL中读锁即为共享锁，写锁即为排斥锁。

• 共享锁（读锁）：读锁是共享的，或者说是不阻塞的，多个线程可以在同一时刻可以同时读取同一个资源而互不干扰，但是当有一个线程试图写时，是不被允许的，即读锁不阻塞其他的读操作，但是阻塞写操作。

• 排斥锁（写锁）：写锁是排他的，一个写锁会阻塞其他的写锁和读锁。

//共享锁
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
//排他锁（X)
SELECT * FROM table_name WHERE ... FOR UPDATE

2.2 锁的粒度

为提供共享资源并发性就是让锁的对象更具有选择性，即尽量只锁定需要修改的部分数据，而不是所有的资源，任何时候，在给定资源锁的粒度越小精度越高，则系统的并发程度越高。但是我们不能无限制的只加最小粒度的锁，因为锁也需要消耗资源的，如锁的各种操作、包括获得锁、检查锁是否解除、释放锁都会增加系统开销，因此对锁选择策略是在锁的开销和数据安全性之间寻找平衡折中。

2.2.1 表级锁

表级锁是对整个表加锁的一种操作。MyISAM和InnoDB引擎都支持表级锁，

• 优点：是MySQL中锁定粒度最大的一种锁，实现简单，资源消耗也比较少，加锁快，只对一个表操作不会出现死锁。
• 缺点：其锁定粒度最大，触发锁冲突的概率最高，并发度最低。

表锁的语法是lock tables … read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

#为表t1上读锁，t2上写锁
lock tables t1 read,t2 wirte;
#释放锁
unlock tables;

mysql> lock tables employee read;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into employee values('crf','8',15000,now(),300);
ERROR 1099 (HY000): Table 'employee' was locked with a READ lock and can't be updated

2.2.2 行级锁

行级锁是MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁，在MySQL中MyISAM不支持行锁，InnoDB支持。记住行级锁只能对是有索引字段进行加锁从而锁住改行。

• 优点：行级锁能大大减少数据库操作的冲突，其加锁粒度最小，并发度高，
• 缺点：因为粒度小，因此加锁的开销也最大，加锁慢，会出现死锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将退化使用的是表锁。

其他注意事项：

• 在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。

• 如果MySQL的行锁是针对范围索引加的锁，不是针对记录加的锁，所以即使是访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的，这是因为间隙锁的原因（接下来会讲）

• 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

• 即便在条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

隐式默认加锁：

• InnoDB自动加意向锁。
• 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
• 对于普通SELECT语句，InnoDB不会加任何锁；

显示加锁：

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
• 排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

注意：正如上面所说的，行锁是根据索引上的索引项加锁实现的，即上面的对于的列必须有加索引才能是为行级锁，否则为表级锁，如下开两个客户端连接MySQL,因为不是索引，所以为表锁

#客户端1：不是索引，粒度为表锁，类型为排他锁
mysql> select id,job_name from employee_job where id in(1,2,3) for update;
+----+--------------+
| id | job_name     |
+----+--------------+
| 1  | 销售         |
| 2  | 产品经理     |
| 3  | 技术服务     |
| 2  | 老板         |
+----+--------------+
4 rows in set (0.00 sec)
#客户端2：
mysql> select id,job_name from employee_job where id in(1,2,3);
ERROR 1100 (HY000): Table 'employee_job' was not locked with LOCK TABLES

2.2.3 页级锁

页级锁的粒度介于行级锁和表级锁中间的一种锁。它比表级锁速度快，比表级锁的冲突多，比行级锁的冲突少，但速度慢。因此页级是一种折中锁，一次锁定相邻的一组记录。BDB 支持页级锁。开销和加锁时间界于表锁和行锁之间，会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。

注意：行级锁不一定比表级锁要好，锁的粒度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁还要扫描找到对应的行对其上锁，这样的代价其实是比较高的，所以表锁和行锁各有所长。

2.2.4 全局锁

MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock,当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句

#对所有数据库实例加读锁
Flush tables with read lock
#对所有数据库实例加写锁
Flush tables with write lock
#之后执行插入，则会出现
mysql> INSERT INTO DEPARTMENT VALUES(2,'血液内科');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

2.3 死锁

死锁是指两个或两个以上的线程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，

2.3.1 引起死锁的原因

• ①竞争不可抢占资源引起死锁（不可抢占是指没有使用完的资源，不能被抢占）
• ②竞争可消耗资源引起死锁
• ③线程推进顺序不当引起死锁

情况1：有线程p1，p2，都需要资源A，B，本来可以p1运行A --> p1运行B --> p2运行A --> p2运行B，但是顺序换了，p1运行A时p2运行B，容易发生第一种死锁。互相抢占资源对放持有的资源。

情况2：有p1，p2，p3三个线程，p1向p2发送消息并接受p3发送的消息，p2向p3发送消息并接受p1的消息，p3向p1发送消息并接受p2的消息，如果设置是先接到消息后发送消息，则所有的消息都不能发送，这就造成死锁。

2.3.2 产生死锁的必要条件

• 互斥条件：某资源只能被一个进程使用，其他进程请求该资源时，只能等待，直到资源使用完毕后释放资源。

• 请求和保持条件：程序已经保持了至少一个资源，但是又提出了新要求，而这个资源被其他进程占用，自己占用资源却保持不放。

• 不可抢占条件：进程已获得的资源没有使用完，不能被抢占。

• 循环等待条件：必然存在一个循环链。

2.3.3 解决死锁的方式

在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

• 预防死锁：在设计时，对锁的使用进行预防，以确保业务不会发生死锁，通过破坏死锁产生的后三个必要条件：即破坏请求持、不可抢占、循环等待条件之一来预防死锁。
  • 破坏请求和保持条件：所有线程开始前，必须一次性地申请所需的所有资源，这样运行期间就不会再提出资源要求，破坏了请求条件，即使有一种资源不能满足需求，也不会给它分配正在空闲的资源，这样它就没有资源，就破坏了保持条件，从而预防死锁的发生。
  • 破坏不可抢占条件：当一个已经保持了某种不可抢占资源的线程，提出新资源请求不能被满足时，它必须释放已经保持的所有资源，以后需要时再重新申请
  • 破坏循环等待条件：对系统中的所有资源类型进行线性排序，然后规定每个进程必须按序列号递增的顺序请求资源。假如进程请求到了一些序列号较高的资源，然后有请求一个序列较低的资源时，必须先释放相同和更高序号的资源后才能申请低序号的资源。多个同类资源必须一起请求。

• 解除死锁：产生死锁时，能够从死锁中解脱处理，比如一种策略超时退出。在MySQL中这个超时时间可以通过参数innodb_lock_wait_timeout来设置，默认为50s.

• 检查死锁：在MySQL种有这样的机制，线程发现死锁后，会主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。可将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。但是它有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，消耗大。

• 一种特殊情况：在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。（这是两个事务都获得间隙锁（间隙锁之间兼容），但插入意向锁无法获取（）插入意向锁与间隙锁不兼容））

2.4 三种行级锁模式（原理）

2.4.1 记录锁

记录锁其实很好理解，对表中的记录加锁，叫做记录锁，粒度是行级锁，类型是排他锁。一般记录锁出现在select、update语句，比如下面这句，它会在id=1的记录上加上记录锁，以阻止其他事务插入，更新，删除和更新id=1这一行。

//id 列必须为主键列或唯一索引列
SELECT * FROM test WHERE id=1 FOR UPDATE;
UPDATE SET age = 50 WHERE id = 1;

记录锁的条件：

• id 列必须为唯一索引列或主键列(主键索引)，否则上述语句加的锁就会变成间隙锁或临键锁(有关临键锁下面会讲)。
• 同时查询语句的条件子句必须为精准匹配即为=，不能为>、<、like等，否则也会退化成间隙锁或者临键锁

注意：如果要锁的列没有索引，进行全表记录加锁（行级锁的性质）

2.4.2 间隙锁

间隙锁Gap Lock是指在InnoDB存储引擎中锁可以加在不存在的空闲空间的一种锁。它能在两个索引记录的开区间之间加锁（双端看你的查询是否包含，下面的between是因为包含了）。间隙锁是一种行级锁，是 InnoDB在 RR(可重复读) 隔离级别下为了解决幻读问题时引入的锁机制。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。（间隙锁之间相互兼容）

产生间隙锁的条件：

• 使用普通索引锁定，或者使用多列唯一索引，或者使用唯一索引（主键索引）锁定区间或者不存在的记录。
• 条件子句不为精确匹配，为范围

可能光说不好理解，看例子：

//创建test表，id为主键索引
CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
//插入数据
INSERT INTO `test` VALUES ('1', '小罗'),('5', '小黄'),('7', '小明'),('11', '小红');

上面的插入语句完成后，test表中存在的隐藏间隙如下： 即有范围：(-∞, 1)、（1, 5)、（5, 7)、(7, 11)、（11, +∞)的间隙，那么当我使用下面语句的时候就会触发间隙锁

BEGIN;
/*事务1，不提交*/
SELECT * FROM `test` WHERE `id` BETWEEN 3 AND 6 FOR UPDATE;
/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (3, '小张1'); # 阻塞

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 阻塞

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 正常执行

/* 事务6插入一条 id = 9， name = '大东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (9, '大东'); # 正常执行

/* 事务8插入一条 id = 12， name = '张三' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行
/* 事务9更改id=5时的名字='trluper',阻塞*/
UPDATE test SET NAME='TRLUPER' WHERE ID=5;			#阻塞

/*事务10查询，间隙锁之间兼容*/
select * from test where id=6 for update;		#成功执行，（6，7）是间隙锁兼容

/*记录锁不兼容，id=5有一个记录锁，因此阻塞*/
SELECT * FROM `test` WHERE `id` BETWEEN 3 AND 6 FOR UPDATE; #阻塞

/* 提交事务1，释放事务1的锁 */
COMMIT;

从上面我们可以看到，（1,5)和(5,7)这两区间区间被事务1锁住（5因为在并且在范围肯定也会被锁住,id=5这条记录是记录锁），在此期间的其他事务的插入操作失败，而其它区间，都可以正常插入数据。所以我们可以得出结论：当我们给 (3, 6)这个区间加锁的时候,用到了间隙锁，会锁住 (1,5)和(5,7)这两个区间。

间隙锁的缺点:

• 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。虽然解决了幻读，但在某些场景下这可能会对性能造成很大的危害
• 当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

间隙锁的作用：

• gap-lock通过锁定前后一段间隙范围，可以解决可重复读隔离级别下的幻读的问题
• 为了数据恢复和复制的需要。

注意：可重复读级别下才会有间隙锁！还有如果对不存的记录操作即使使用=也会产生间隙锁，如select id,name from test where id=3 for update;,id=3是不存在的记录,此时会锁住区间[1,5]

2.4.3 临键锁（Next-Key Locks）

Next-key locks是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录后面间隙上的锁。

也可以理解为一种特殊的间隙锁，因此临建锁肯定也可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，

假设有如下表：InnoDB，RR隔离级别，id主键, age普通索引 则有age范围:(-∞, 10],(10, 24],(24, 32],(32, 45],(45, +∞]

/事务A执行下述语句
-- 根据非唯一索引列 UPDATE 某条记录 
UPDATE table SET name = Vladimir WHERE age = 24; 
-- 或根据非唯一索引列 锁住某条记录 
SELECT * FROM table WHERE age = 24 FOR UPDATE;
//事务B执行下述语句
INSERT INTO table VALUES(100, 26, 'tianqi'); 

上面因为age=26恰好在(24,32]范围内,age为非唯一索引，使用临检索模式的行级锁，则不管事务A执行那个语句，事务B的插入语句总会阻塞。

总结：

• InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
• 记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
• 间隙锁存在于非唯一索引中，对于唯一/主键索引需要范围查找或者查找不存在的值才会触发，锁定开区间（间隙）范围内的一段间隔，它是基于临键锁实现的。
• 临键锁存在于非唯一索引中，对于唯一/主键索引需要范围查找或者查找不存在的值才会触发，它是一种特殊的间隙锁,是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，锁定一段左开右闭的索引区间。

mysql默认使用next-key lock临健锁： 当查询的是唯一索引时：

• 若是精确查找且记录存在，next-key lock就会退化为记录锁，只锁住这一行记录
• 若是范围查找或当查询记录不存在，next-key lock会退化为间隙锁

当查询时非唯一索引时：

• 当查询的记录存在时，为 next-key lock 临键锁
• 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。

2.5 意向锁

意向锁又分为意向共享锁（IS）和 意向排他锁（IX）。在介绍意向锁的意义之前，必须明白下面四点：

• 意向共享锁（IS）和 意向排他锁（IX）都是表锁。
• 意向锁是一种不与行级锁冲突的表级锁，这一点非常重要。它只会与非意向的表锁冲突
• 意向锁与意向锁之间永远是兼容的
• 意向锁是InnoDB自动加的， 不需用户干预。当对表或对行加排他锁时，就会获得意向排他锁；加共享锁时，会获得意向共享锁。
• 意向锁是在InnoDB下存在的内部锁，对于MyISAM而言 没有意向锁之说。

兼容性	表IS	表IX	表s	表x
表IS	兼容	兼容	兼容	不兼容
表IX	兼容	兼容	不兼容	不兼容
表S	兼容	不兼容	兼容	不兼容
表X	不兼容	不兼容	不兼容	不兼容

意向锁的存在目的是为了让InnoDB中的行锁和表锁能够更高效的共存。假设有下表，InnoDB，RR隔离级别 id是主键

加入现在有事务A，进行了一条SELECT * FROM users WHERE id = 6 FOR UPDATE;但未提交，那么事务A会获得id=6的行级锁，且为记录排他锁，同时还有自动生成一个意向排他锁。

这时候有个事务B,进行操作LOCK TABLES users READ;会失败，因为对于要表锁来说，必须要保证：

• 当前没有其他事务持有users表的排他锁。
• 当前没有其他事务持有users表中任意一行的排他锁 。

但是现在users表的排他锁已经被事务A持有了，事务B的这个想获取表共享锁的操作会失败。

上面的例子是有意向锁的情况，试想一下没有意向锁和表锁情况会这样：事务B会去检查user表中的每一行查看是否有排他锁，如果有则无法执行，无则会执行。很明显这样的效率极低，但有意向锁后，只需检查意向表锁即可。

2.6 插入意向锁

插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。

插入意向锁的生成时机：

• 每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被其他事务加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），现象就是 Insert语句会被阻塞。

我们可以分析下面的语句： 首先表中数据如下,id为主键，mysql用可重读隔离级别:

(id,no,name,age.score)
(15，S0001,Bob,25,34)
(18,S0002,Alice,24,77)
(20,S0003,Jim,25,5)
(30,S0004,Eric,23,91)
(37,S0005,Tom,22,22)

两个事务A：

事务A执行：
time1:update students set score=100 where id=25;
time3:insert into students(id,no,name,age,score) value(25,S0026,'SONY',28,90);

事务B执行
time2:update students set score=100 where id=25;
time4:insert into students(id,no,name,age,score) value(26,S0026,'ace',28,90);

• 事务A的time1会执行成功获得范围为(20,30)的间隙锁
• 事务B的time2会执行成功，获得范围为(20,30)的间隙锁（间隙锁之间兼容）
• 但是事务A(20,30)范围一条数据时，会获取相应的插入意向锁，都在等待对方事务释放间隙锁，于是就造成了循环等待，形成死锁，time3和time4一直阻塞

3. 事务

事务就是由一批SQL语句组成，可以说它是一个独立的工作单元。事务处理可以用来维护数据的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行，事务处理是一种机制，利用事务处理可以保证成批的MySQL操作不会只执行一部分，如果全部执行那没问题，如果在执行过程中发生错误，它就会进行回退以恢复到数据库以前安全版本。MySQL中InnoDB支持事务，MyISAM不支持。

3.1 事务的ACID

一个运行良好的事务处理系统，必须具备四大特性：原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。实现了ACID的数据库相比于未实现的数据库，其通常需要更强的CPU处理能力，更大的内存和磁盘空间。因此要酌情堪虑是否选用事务型存储引擎。

• 原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的操作要么全部提交成功，要么全部失败回滚。
• 一致性：数据库总是从一个一致性状态转移达到另一个一致性状态，一致性状态就是由事务原子性保障，一个事务提交了，那是ok的，一个事务未提交，那也是OK的。
• 隔离性：通常来说，一个事务所做的修改在未提交以前，对其他事务是不可见的。事务间是互不干扰的。
• 持久性：一旦事务提交了，其所做的修改会永久保存到数据库总中，即使系统崩溃，修改的数据也不会丢失，（持久性不能做到100%保证策略，如果数据库本身就能做到吃性，那么要备份又有什么用呢）

3.2 隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务所做的修改，在哪些事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也低

• 未提交读(read uncommitted)：在该隔离级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。这个级别会导致喝多问题，从性能上来说，该级别没有比其他级别好很多，一般不推荐使用。

• 提交读(read committed)：在该级别，一个事务开始时只能看见已经提交的事务的修改。换句话说，一个事务从开始直到提交之前，所作的任何修改对其他事务都是不可见的，因此也可称为不可重复读**，该隔离级别解决了脏读，但存在幻读问题。这个隔离级别时大多数数据库默认的隔离级别。
• 可重复读(repeatable read)：在该级别，保证了同一个事务多次读取同样的记录的结果是一致的。可重复读是MySQ的默认隔离级别，解决了脏读的问题，对于幻读引入了间隙锁和临键锁解决。所谓的幻读就是指当某个事务在读取某个范围内的记录时，另一个事务又在该范围插入新的记录，当之前的事务再次读取该范围记录时，产生幻行。InooDB引擎同时还使用多版本控制MVCC解决幻读问题。

• 可串行化(serializable1)：该隔离级别时最高的隔离级别，它通过强制事务串行执行，避免了前面说的幻读问题。简单来说，就是在读取每一行数据都加锁，所有会导致大量的超时和锁争问题。

3.3 Read View是什么，在MVCC中如何工作？

Read View是在开启一个事务后会自动创建的，它会维护四个字段，Innodb依据Read View里面的事务ID去判断这条记录对于该事务是否可见，如果记录的事务版本id是小于创建Read view的事务id，那么这条记录对于该事务是可见的，这就是Innodb的MVCC多版本并发控制。

这四个字段是InnoDB实现MVCC多版本并发控制的关键

• m_ids ：指的是在创建 Read View时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
• min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是m_ids` 的最小值。
• max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
• creator_trx_id：指的是创建该 Read View 的事务的事务 id。

Read view配合Innodb存储引擎的数据库表，因为在聚簇索引记录中有两个隐藏列，一个列是显示最新改动的trx_id事务id，另一个是roll_pointer指针，指向以前的旧版本记录undo log，一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
• 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
• 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
  • 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
  • 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）

3.4 解决幻读的两种方式

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select ... for update ））等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

在可重读隔离级别下，幻读被完全解决了吗？ 可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

• 第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。

• 第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

3.5 读提交与可重读的不同在哪（实现不同在哪？）

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同：

• 「读提交」隔离级别是在每个SQL执行时都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
• 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

在可重复读隔离级别中，普通的 select 语句就是基于 MVCC 实现的快照读，也就是不会加锁的。而 select .. for update 语句就不是快照读了，而是当前读了，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上 next-key lock 锁。

4 事务日志

首先必须明白InnoDB存储引擎是以页为单位来管理存储空间的，真正访问页面之前，需要把在磁盘上的页缓存到内存中的缓存池之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘（checkpoint机制），通过缓存池来优化cpu和磁盘之间的鸿沟。通过将数据的修改记录到事务日志redo/undo并持久化，内存中修改的数据在后台可以慢慢地刷回磁盘，不要修改一次就马上刷回磁盘。减少了与磁盘间的IO;同样事务日志的持久性保证了事务的一致性和持久性（由事务的redo日志和undo日志来保证。）

• REDO LOG称为重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。redo日志的存在使得我们再对表进行修改时会把这些修改写进日志里，然后再写入磁盘，只有日志写入成功，才算事务的提交完成，这样即使数据库发生宕机未刷新到磁盘，也可以通过redo日志恢复。

• UNDO LOG称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。有的DBA或许会认为UNDO是REDO的逆过程，其实不然。undo log是存储引擎(innodb)生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了insert语句操作，那么undo log就记录一条与之相反的delete操作，主要用于事务回滚（undo log记录的是每个修改操作的逆操作）和一致性非锁定定读（undo log回滚行记录到某种特定的版本,mvcc，多版本并发控制）。

• binlog:上述两个日志是InnoDB存储引擎生成的日志，而binlog是Server层生成的日志，主要用于数据备份和主从复制。 MySQL事务日志详解

4.1 为什么需要undo log(undo log 的作用是什么）?

如果我们每次在事务执行过程中，都记录下回滚时需要的信息到一个日志里，那么在事务执行中途发生了 MySQL 崩溃后，就不用担心无法回滚到事务之前的数据，我们可以通过这个日志回滚到事务之前的数据。实现这一机制就是 InnoDB的undo log日志（回滚日志），它保证了事务的 ACID 特性 (opens new window)中的原子性（Atomicity）。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

• 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
• 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
• 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

• 通过 trx_id 可以知道该记录是被哪个最新事务修改的；
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

因此，undo log 两大作用：

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
• 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

4.2 为什么需要buffer pool

MySQL 的数据都是存在磁盘中的，那么我们要查询一条记录的时候，得先要从磁盘读取该记录，,但是，磁盘I/O的开销是很大的，为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool）来对这些记录在内存种缓存，来提高数据库的读性能。

有了 Buffer Poo 后：

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
• 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致）,并将这些页放在change buffer，为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将change buffer内的脏页写入到磁盘。

查询一条记录，就只需要缓冲一条记录吗？

不是的，磁盘于内存之间的IO是以块或页进行调入调出的。 当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

4.3 redo log的作用

Buffer Pool 是提高了读写效率没错，但是问题来了，Buffer Pool 和change buffer都是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，此时即使系统崩溃，脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态

redo log内部记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

4.4 redo log 和 undo log 区别在哪？

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务，

4.5 redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。至此， 针对为什么需要 redo log 这个问题我们有两个答案：

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

4.6 产生的 redo log 是直接写入磁盘的吗？

不是的。实际上，redo log也有自己的一个缓存叫做redo log buffer 执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘而是写入这个缓存后续再持久化到磁盘，以免产生大量的 I/O 操作。

4.7 既然redo log也是缓存，怎么能保证持久性呢？（redo log 什么时候刷盘？）

这就要提到redo log的刷盘策略和时时机了，redo log的刷盘时机主要有以下几点：

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半(8M)时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
• 每次事务提交时都将缓存在 redo log buffer里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下面会说）

4.8 innodb_flush_log_at_trx_commit 参数控制的是什么？

redo log buffer的一个刷盘策略是在每次事务提交后都会将里面的内容进行落盘，这是个默认行为（默认值为1）。除此之外，InnoDB 还提供了另外两种策略，由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

• 当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。（异步）
• 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。(同步刷新）)
• 当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache（如果你想了解 Page Cache，可以看这篇 (opens new window)），Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。

>可以看到异步刷新redo log的做法不能保证一致性（），因为在你还没有完成将redo log buffer刷入磁盘时宕机，就会丢失这些内存上的数据

这三个参数的数据安全性和写入性能的比较如下：

• 数据安全性：参数 1 > 参数 2 > 参数 0
• 写入性能：参数 0 > 参数 2> 参数 1

4.9 redo log 文件写满了怎么办？

InnoDB存储引擎有一个redo log Group(重做日志文件组）,重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫 ：ib_logfile0和 ib_logfile1 ，它是这样工作的，首先写ib_logfile0 文件,写满后，再切换到ib_logfile1写，ib_logfile1写满后再切到file0从头开覆盖写哪些被落盘的脏页。

如果来不及落盘就被覆盖了，怎么办？

• 那此时应该是更新极多的场景，此时我觉得就只能合理的设置ib_logfile的大小，或者对写操作做限流。

4.10 为什么需要 binlog ？

前面介绍的 undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

4.11 为什么有了bin log， 还要redo log?

• 最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

• 既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

4.12 redo log 和 binlog 有什么区别？

• 适用对象不同：binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；redo log 是 Innodb 存储引擎实现的日志
• 写入方式不同：binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。redo log 是两个日志文件循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。
• 文件格式不同：redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；而binlog有三种格式。
• 用途不同：binlog 用于备份恢复、主从复制；redo log 用于掉电等故障恢复。

4.13 Mysql的主从复制是怎么实现的？

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。 这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

MySQL 集群的主从复制过程梳理成 3 个阶段：

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。
• 在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

同样binlog也有一个在Server的缓存 binlog Cache，在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。

binlog和redolog之间还有一个一致性问题，那就是

• 如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入，就会导致主从数据不一致，
• 如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入，也会导致主从数据不一致。

因此，对binlog和redo log有一个双阶段提交的，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」

5. 多版本并发控制MVCC

在MySQL你可以认为MVCC是行级锁的一种变种，为适应并提升并发性能，大多数情况下避免了加锁操作，因此开销更低，也解决了MySQL默认隔离级别可重复读下的幻读问题。MVCC旨在repeatable read和read committed下工作。

MVCC的实现，是通过保存数据在某个时间点（说是时间点是未了更好理解，其实是事务的系统版本号） 的快照来实现的。在InnoDB的MVCC中，通过在每行记录后面保存两个隐藏的列来实现，这两个列一个是保存了行被哪个最新事务所修改的trx_id，一个保存了行的roll_pointer指针，指向每一个旧版本记录。trx_id会根据没新开一个事务而自动递增。有了这两个列之后，对于select\update\delete\insert则必须遵循下面的规则：

• select：对于select，InnoDB会根据一下规则来检索
  • 1. InnoDB只查找版本号早于当前事务版本号的数据行，这样确保了事务读取的行，要么在事务开始之前就已经存在，要么是当前事务自己插入或者修改的，绝不可能是该事务之后的事务进行修改的，这样解决了幻读的问题。
  • 2. 行的删除版本要么未定义，要么就是大于当前事务的版本号，这就可以确保事务读取到的行，在该事务开始前还未删除。如果之前就删除了那么删除版本号肯定有值而读取不了。

• INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

• DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

• UPDATE：InnoDB为插入一行新记录，保存当前事务系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

注意：MVCC并不能彻底解决并发带来的安全问题，它只是起到一个缓解并发冲突量的作用，如果两个事务有明显的次序要求，如事务A，B是希望A修改该行后再交由B修改，那么就必须人为事务A所在的行加锁，这样才能保证B是再A修改后才修改的，因此该用锁的时候还是要用，否则有MVCC就能解决这些的话，还要锁干嘛

MVCC的实现依赖于undo日志

6. MySQL的存储引擎

数据库存储引擎是极其重要的，这是因为：

• 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。
• 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
• 用户可以根据不同的需求为数据表选择不同的存储引擎(表类型)

MySQL的存储引擎主要有InnoDB、MyISAM。

6.1 InnoDB

InnoDB是MySQL当中默认的事务型存储引擎，它被设计用来处理大量的短期事务，短期事务大部分情况下都是正常提交的，很少会回滚。InnoDB的数据存储在表空间xxx.ibd中，能将表的数据和索引放置在单独的文件中，其表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，不过它的二级索引（非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的索引都会很大，消耗空间多

InnoDB的特点：

• 采用MVCC多版本并发控制来支持高并发，并且实现了四个标准隔离级别，默认级别为可重复的，使用间隙锁和MVCC策略防止了幻读的出现。
• 支持自动增长列`AUTO_INCREMENT·。自动增长列的值不能为空，且值必须唯一。MySQL中规定自增列必须为主键。
• 支持外键，保证数据的完整性和正确性。外键所在表为子表，外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键
• 支持行级锁，提高并发访问性能。
• 作为事务性存储，InnoDB通过一些机制和工具支持真正的热备份。（热备份是指在正常情况下,两余度同时工作,当某一余度出现故障时,系统可切除故障余度,启用单余度方式,降级工作.本系统采用热备份方式）

6.2 MyISAM

MyISAM是MySQL早期的默认存储引擎。MyISAM将表存储在两个文件中：数据文件(xxx.MYD)和索引文件(xxx.MYI)。MyISAM不支持事务和行级锁，而且有一个致命的缺陷就是崩溃后无法安全恢复。

MyISAM特点：

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 占用空间小，访问速度快

7. MySQL的索引

索引在MySQL中也叫键，是存储引擎用于快速找到记录的一种数据结构。因此索引对于数据库的性能非常关键，尤其是当表中的数据量越来越多时，索引对性能的影响愈发重要。总计索引的优点有三点：

• 索引可以快速查询，大大减少了服务器查找时需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变成顺序I/O，如order by就是索引支持的

索引分类（逻辑角度）：

• 普通索引：是最基本的索引，主要其加快查询速度，可使用key或者index定义一个列的普通索引或联合普通索引。
• 主键索引：即主键，一个表只能有一个主键，则也就只能有一个主键索引，不允许有重复值和NULL值。以primary key定义。
• 唯一索引：不允许有重复值，但运行为NULL值，同时一个表可以有多个唯一索引。适用unique key或者unique index定义
• 多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

注意：key和index的区别是，key除了会定义一个相应的索引之外，还会形成一种约束，约束该列的规范，比如primary key约束表只能有一个主键列，且不能重复不能为NULL，而index只是生成这一的一个索引。

索引分类 从物理存储角度: 聚簇索引和非聚簇索引 从数据结构角度: B+树索引、hash索引、FULLTEXT索引、R-Tree索引

7.1 索引基础

理解索引当然是看例子更合适：

select first_name from sahilia.actor where actor_id=5;

上面这个检索语句中，如果在actor_id列上设有索引，则MySQL将使用索引找到actor_id=5的行，即MySQL会在索引上按值查找，然后返回该值的数据行。

索引可以包含一个或多个列(创建键)。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀原则。需要明确的是最左前缀原则是发生在复合索引上的，只有复合索引才会有所谓的左和右之分，在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

mysql 建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

• 如果有一个 2 列的索引(col1, col2)，则已经对(col1)、(col1, col2)上建立了索引；
• 如果有一个 3 列索引(col1, col2, col3)，则已经对(col1)、(col1, col2)、(col1, col2, col3)上建立了索引；

原理：

B+树的数据项是复合的数据结构，比如(name,age,sex)的时候，B+树是按照从左到右的顺序来建立索引树的，比如当(张三,20,F)这样的数据来检索的时候，B+树就优先比较name来确定下一步的搜索方向，如果name相同则再依次比较age和sex，最后得到检索到的数据；但当（20，F）这样的没有name的数据来的时候，B+树就不知道第一步应该检查哪个节点，因此此时就不会使用联合索引。

7.2 索引类型

索引有很多种类型，可以为不同的场景提高供更好的性能。在MySQL中，索引是在存储引擎实现的，因此没有统一的索引标准。下面介绍MySQL中的支持的索引类型。

7.2.1 B+Tree索引

InnoDB使用的时B+Tree索引。这意味着所有的值都是按顺序存储的，且数据域都存储在叶子节点处，非叶子节点只存储索引不能存储数据，这样对于查询性能来说很稳定，都是h。B+Tree索引适用于全键值、键值范围或键前缀查找：比如定义了普通联合索引key(last_name,first_name,birthday)

• 全值匹配：指的是和索引中的所有列进行匹配，即last_name,first_name,birthday均匹配
• 匹配最左前缀：查找时，可以只列举第一列last_name进行查找
• 匹配列前缀：也可只匹配某一列的值的开头部分，如寻找姓以J开头的数据
• 匹配范围：对姓名last_name也支持范围查找。

注意：说到B+Tree的叶子节点，必须区分MySQL中的聚簇索引和非聚簇索引（二级索引），在聚簇索引的叶子节点存储的是完整的行数据，而二级索引叶子节点存储的是数据行指针,这里的行指针的实质是叶子节点保存只是行的主键值，因此非聚簇索引必须还要在聚簇索引结构上进行查找，这也是为什么它被称为二级索引

更详细的B+Tree见：数据结构

7.2.2 哈希索引

哈希索引是基于哈希表实现的，因此对于使用哈希索引其关键字的存储是无序的，不支持范围匹配查找，只有精确匹配锁索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码解决其存储的位置，因此其查找一个值的复杂度为O(1)，比B+Tree更快。

在MySQL中，只有Memory引擎显式支持哈希索引，也是其默认的索引类型。

优点：

• 访问内存的速度很快，同时因为是通过计算哈希值来确定关键字位置，访问哈希索引的数据也很快

缺点：

• 哈希索引只包含哈希值和行指针，而不会存储字段值，所有不能使用索引中的值来避免读行。
• 哈希索引数据是按照计算的哈希值排列的，因此也就是无序的，不支持范围查找。
• 哈希索引不想B+Tree索引那样支持部分列查找，因为索引的哈希值是所有列共同计算的结果。
• 哈希冲突多的时候，维护操作的代价比较高昂。

7.3 为什么B+树更适合做索引

我们先分析B+Tree与众不同的特点：

• 第一个就是B+树在B树的基础之上最重要的改进就是非叶子结点只存储关键字和下一层的索引，不存储Data域，只在叶子结点存储Data域；
• 第二点就是利用B+树的天然有序且在所有叶子结点增加一个链指针，是InnoDB中支持order by limit的原因所在。所有叶结点构成一个有序链表，因此当我们需要有序遍历所有关键字或者按范围查询时，直接从最小关键字的叶子结点开始遍历即可。

7.3.1非叶子结点不存储Data域的好处

• 每一个结点可以存放更多的关键字和下一层的索引。数据库是存储在磁盘上的，我们读取数据是从磁盘读取到内存中，我们在进行磁盘预读取时，是以块的单位进行数据读取，我们在检索B/B+树的结点时，每次以块为单位将一个结点读取到内存中，若一块磁盘包含了树结点以外的数据，就造成了浪费，因此我们需要使每一个结点的数据大小正好或者接近一块磁盘的大小。于是我们在构建B+/B树时，树的阶数其实就取决于一块磁盘中能容纳多少个关键字以及相关的索引和Data域。B+树的非叶子结点不存储Data域，因此它可以存储更多个关键字和下一层索引，因此B+树会比B树更宽胖。若我需要查找的关键字正好在叶子结点，B+树所进行的I/O次数更少,因为途中经过每一层，我们都需要进行一次I/O读取一个结点，B+树会更矮，途径的层数会更少。

• 使得B+树查询速度更稳定，B+树所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;

7.3.2所有叶结点构成一个有序链表的好处

• B+树便于区间查找（这点才是B+树作为索引的关键），我们进行数据库查询大多为区间查询，B+树天然具备排序功能，B+树所有的叶子结点构成了一个有序链表，在查询大小区间的数据时候更方便，B+树查询，只需通过头结点往下找到第一个叶子结点，然后在叶子结点的链表上就行遍历即可完成区间查询，而B树的关键字大小相邻近的结点可能隔得很远，要想进行区间查询需要不停的进行中序遍历，相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

• B+树全结点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要进行中序遍历，这有利于数据库做全表扫描。

总结B+树优点(选择B+树作为索引的原因)

• 因为没有非叶子节点没有存储数据，因此单一非叶子结点能存储更多的关键字索引
• 所有查询都要查找到叶子节点，查询性能稳定；
• 所有叶子节点形成有序链表，便于范围查询以及全结点遍历更快。

7.4 高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。创建索引是最忌讳的就是为每一个列都建上独立的索引或者按照错误的顺序创建联合索引，这样会大大降低性能。

索引不是越多越好，会降低性能！！为什么会降低性能呢？ 且听我娓娓道来 数据库中建立一个索引，那么就得维护，当你在一张表建立了100个索引，你就得在每次插入、删除都得维护这100个索引，这性能能好吗？

但对于中表，适当的建立数据库的表索引能够有效的提升性能，如何建立高性能的索引是本节的主题，主要有：

• 选择适当的列建立聚簇索引（适当的列是指频繁查询且唯一的字段，这样对于大多数查询来说都避免了回表查询）
• 对于字符类型索引，可以尝试建立前缀索引
• 对于建立联合索引（复合索引），必须把选择性高的列放在前面
• 建立覆盖索引来避免回表查询
• 合理利用冗余索引（当我们向表中添加字段(B)，现有的联合索引(A,C)已经过于庞大，维护困难，我们可以考虑添加(A,B)索引)

7.4.1 前缀索引：单列索引（字符）

有时候需要索引很长的字符列，但这会让索引变得大且慢。 一个策略是可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但是也会降低索引的选择性，其选择性是指不重复的索引值d和数据表的记录总数T的比值，因此我们在建立前缀索引，需要计算其不同长度的区分度，以此作为根据建立前缀索引，即\(d/T\)。

如下面计算左前缀前几位时的选择性left(city,7)表示取city的前7位：

根据上面的描述，我们应该用city(7)创建前缀索引

alter table sakila.city_demo add key (city(7));

优缺点：值得一提的是，前缀索引是一种能使索引更小、更快的有效办法，但缺点使MySQL无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描。

7.4.2 选择合适的索引序列：联合索引

对于B+Tree多列索引来说，按照其最左前缀匹配原则，正确的的顺序依赖于使用哪些列选择性高，同时还有考虑如何更好的满足排序和分组的需要，即满足order by\group by\distinct查询子句的查询需求。

有一条经验法则：将选择性最高的列放在索引的最前面，选择性。

mysql> select count(distinct staff_id)/count(*) as staff_id_selectivity,
	-> count(distinct customer_id)/count(*) as customer_id_selectivoty,
	-> count(*)
	-> from payment;
-------------------------------输出--------------------------------------------------*
staff_id_seletivity:0.0001
customer_id_selectibity:0.0373
count(*):16049

从上面可以看到选择性更高的是customer_id，因此将其放在索引第一列：

alter table payment add key(customer_id,staff_id);

最后还要说一句尽管这条关于选择性和基数的经验法则对于一般情况适用，但是别忘了还要考虑where子句当中的排序、分组和范围查询，要对这些折中考虑。

7.5 聚簇索引

首先要明白的是聚簇索引不是开始说到的属于普通索引、唯一索引和主键索引的分类，MySQL的索引分类只有这三种。聚簇索引并不是一个单独索引类型，而是一种数据的存储方式的专有名词。

对于InnoDB来说，InnoDB就会选择一个唯一的非空索引作为聚簇索引，当没有符合的唯一非空索引，就会隐式的定义一个主键来作为聚簇索引，因此一般情况你也可以理解主键就是聚簇索引；但是，有些时候唯一索引也有可能是聚簇索引

7.5.1 聚簇索引与非聚簇索引的区别

在上面说到B+Tree的时候已经介绍到了聚簇索引和非聚簇索引之间的区别，这里在系统的总结一下。

• 首先就是一张表只有有一个聚簇索引，但可以有多个非聚簇索引
• 另外聚簇索引的叶子节点存储的是完整的行数据，而非聚簇索引的叶子节点存储的是其相应主键列的关键字值，因此使用非聚簇索引进行查询时，除了查询非聚簇索引得到主键关键字值外，还要利用该值进行回表查询聚簇索引，最终得到整个行数据，这也是非聚簇索引又叫二级索引的由来。

//使用主键索引(聚簇索引)查询
select * from table where ID = 100;
//使用非聚簇索引查询
select * from table where k = 1;

7.5.2 聚簇索引的优缺点

聚簇索引可能对性能有帮助，但也会导致严重的问题，下面分析其优缺点

优点：

• 聚簇索引的存在将相关数据都保存在一起，磁盘一次I/O就获得所有数据，如用户邮箱的管理，以ID为主键形成聚簇索引结构，则更加ID用户将其相关的数据都聚簇在叶子节点，可一下获得其所有数据。如果没有聚簇索引，则要获取该ID的所有信息要经过多次磁盘I/O
• 数据访问速度更快，聚簇索引将索引和数据保存在同一个B+Tree上，因此聚簇索引比非聚簇索引的查询更快。
• 使用覆盖索引扫描的查询可以直接使用页节点的主键值。

缺点：

• 插入速度严重依赖插入的顺序，其存储结构说明了按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。如果不是按照逐渐顺序，会慢很大，而且最坏使用optimize table重新组织一下表。
  • 实验表明按主键列的值顺序的插入记录要快于乱序插入：
    • 这是因为乱序插入使得InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间，而页分裂导致移动大量的数据。
    • 要插入的目标页可能还没有刷到缓存，那么此时InnoDB就不得不先从磁盘读取目标页到内存中，这将增加磁盘与内存的IO次数，导致速度性能下降。

• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。

• 更新行或插入行被强制移动带新的行，如果是要插入满行的一页，则会导致页分类。存储引擎通过页分裂称两个页面以能够存储该行，一次页分裂操作，导致表占有更多的空间。

• 二级索引（非聚簇索引）访问需要两次索引查找，而不是一次

• 在行比较稀疏，或者由于页分裂导致数据存储不连续时，聚簇索引可能导致全表扫描变慢。

7.6 覆盖索引

上面提到了二级索引想要查询行的所有列必须进行回表查询，有没有什么办法不用回表查询就能得到一些列的信息呢。这是要就用到了覆盖索引，如果一个二级索引的叶子节点就包含了所有要查询字段的值，就称为为覆盖索引。

不是所有的索引类型都能成为覆盖索引，覆盖索引必须存储该索引所在行的值，而像哈希索引、空间索引这些都不能存储。因此MySQL只有B+Tree索引能使用覆盖索引。现在来讲解覆盖索引的实现要求：

• 表有除一个主键索引，其他列建立相应的联合索引，这一才能在二级索引存储有其他列的值。对于主键很明显在二级索引的叶子节点上

一个最简单的覆盖索引例子就是如下：

CREATE TABLE TEST_FU(
	ID INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(8) NOT NULL,
	AGE DOUBLE NOT NULL,
	PRIMARY KEY (ID),
	INDEX (NAME，AGE)
)ENGINE=InnoDB;

上面的表含有三个字段，其中ID为主键，NAME、AGE为普通索引，那么NAME|AGE作为二级索引，其叶子节点存储的是主键的值，那么下述语句：

SELECT NAME,AGE FROM TEST_FU WHERE NAME='trluper' AND AGE='26';

>上面这条语句由于只要NAME、AGE，而在NAME、AGE建立的联合索引确实有该值，此时就不必去聚簇索引进行回表查询了，只需一次就能查询到结果。用explain分析,其EXTRA显示using index说明就是走了覆盖索引

EXPLAIN SELECT NAME,AGE FROM TEST_FU WHERE NAME='trluper' AND AGE='26';

覆盖索引详解

7.7 冗余索引

同一信息的重复储存，叫做冗余,冗余索引通常发生在为表添加新索引的时候。如key(name,city)和key(name)这两个索引就是冗余索引，能够命中后者的查询肯定能够命中前者。

大多数情况下我们都不需要冗余索引，因为索引的维护需要一定的开销。但有时候却页不得不创建一个冗余索引，就比如新加了一个字段，同时现有的联合索引已经非常庞大，如果再将这字段加入这个联合索引就会使得该联合索引更加庞大，此时使用新建冗余索引更好。

8. 优化查询

查询优化、库表结构优化和索引优化在优化数据库中应该齐头并进，一个不落。上面已经介绍了索引的知识，在这里我们将着重介绍如何编写高效的SQL查询语句，在这个过程中也将设计高效的表结构和索引。

查询的大致生命周期：客户端-->服务器（解析-->生成执行计划-->执行-->返回结果）

8.1 查询语句本身优化

查询性能低的最基本原因是访问的数据太多。大部分性能低下的的查询都可以通过减少访问的数据量方式进行优化。对于低效的查询，通过下面两个方法分析是很有效的：

• 确认应用程序是否在检索大量超过需要的数据(比如不要使用select *,它会检索出一行中的所有数据，但是实际中我们可能用不到所有数据)。这恶通常意味者访问太多的行，有时候也有可能是访问太多的列（select *)
• 确认MySQL服务器层是否分析大量超过需要的数据行。

所有一般来说我们的查询语句应该遵循下面规范，能够达到优化目的：

• ** 对于不需要的记录，应当避免，合理增加一些限制，如limit**
• 不要总是取出全部的列，即尽量不使用select *，因为这会带来额外的磁盘IO、CPU服务资源

8.2 重构查询语句

MySQL数据库的连接和断开都很轻量级、在返回一个小查询结果非常高效。且现代通信网络非常快、在一通用的服务器上，每秒运行超10万的查询是能够的。

因此对于查询语句也能够这样优化：将复杂的大的SQL语句切分成下查询语句，每个小查询功能完全一样，只完成一小部分，每次只返回一小部分的查询结果，达到”分而治之“的效果。

比如说每个月我们都有执行下面的语句：

DELETE FROM messgaes WHERE created<DATA_SUB(NOW(),INTERVAL 3 MONTH);

那么我们可以使用下面切分后的语句

rows_affected=0;
do{
	row_affected=do_query("DELETE FROM messgaes WHERE created<DATA_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000");
}where rows_affected>0

8.3 查询执行过程

我们要编写好的SQL语句，就必须知道MySQL是如何优化和执行查询的 - 客户端发送一条查询给服务器 - 服务器先检查查询缓存，如果缓存命中，则立刻返回存储在缓存中的结果，否则进入下一阶段 - 服务器进行SQL解析，预处理、再由优化器生成对应的执行计划 - MySQL根据优化器生成的执行计划，再由存储引擎的API来执行查询 - 将结果返回给客户端

上面的每一步都是很复杂的，这里这是简述

8.4 查询优化处理

8.4.1 查询优化器

通过语法解析后的SQL语句被认为是合法的，然后由优化器将其转化为执行计划，一条查询可以由很多种执行方式，优化器的作用就是找到其中最好的可执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择成本最小的一个。我们可以通过查询当前会话的LAST_QUERY_COST的值来得到MySQL计算当前查询的成本

mysql> select * from CUSTOMER;
+----+--------+------------+---------+
| ID | SALARY | HIREDATE   | NAME    |
+----+--------+------------+---------+
|  1 |  23000 | 2025-06-06 | trluper |
|  2 |   9000 | 2025-04-26 | 小明    |
|  5 |  21000 | 2021-06-06 | 瑞凤    |
|  8 |  19000 | 2025-01-26 | 点点    |
+----+--------+------------+---------+
4 rows in set (0.01 sec)

mysql> SHOW STATUS LIKE 'LAST_QUERY_COST';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 0.649000 |
+-----------------+----------+
1 row in set (0.09 sec)

这个结果表示MySQL的优化器任务大概需要0.649个数据页的随机查找才能完成上面的查询。这个数据是根据一系列的统计信息得来的。

有很多原因会导致MySQL优化器选择错误的执行计划：

• 统计信息不准确。MySQL依赖引擎提高的统计信息来评估成本，但是有的存储引擎提高的信息偏差大。
• 执行计划种的估计成本不等同于实际执行的成本，所以即使统计信息准确，优化器给出的执行计划可能不是最优的。
• MySQL的最优与你想的最优不一样。你希望执行用时尽可能少，但MySQL的最优只是基于模型选择最优的执行计划，有时候这并不是最快的执行计划。
• MySQL不考虑其他并发执行的查询，这可能影响当前的查询速度

MySQL使用优化策略有动态优化和静态优化

• 静态优化：直接对解析树进行分析，并完成优化，静态优化在第一次完成后一直有效。可以看作是一直"编译时优化"。比如对where条件转换为另一种等价形式。
• 动态优化：动态优化在每次执行时都要重新评估，有时候升值在查询的执行过程种也会重新优化。被认为时运行时优化.

8.5 优化特定类型的查询

8.5.1 优化关联查询

含有关联查询（有主外键对应）的查询语句是MySQL的主要讨论话题，这里需要注意以下的优化选择：

• 确保ON或者USING字句种的列有索引。同时在创建索引的时候就需要考虑关联顺序，当表A和表C使用列c关联时，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建立索引。一般来说，只需要关联顺序的第二个表的相应列建立索引。
• 确保任何的group by和order by中的表达式只涉及到一个表中的列，这样MySQL才能可能使用索引来优化这个过程

8.5.2 子查询

对于子查询，优化的建议是尽可能的使用关联查询替代。

子查询：子查询是指嵌套select语句，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。查询虽然很灵活，但是执行效率并不高。这是因为执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。 关联查询：通过join方式进行多表查询，连接查询不需要建立临时表，因此一般来说其速度比子查询快

9. 常见题目

这里列举了一些最可能问的问题，面试可以加强印象。

9.1 你一般怎么建索引的？

• 选择适当的列建立聚簇索引（适当的列是指频繁查询且唯一的字段，这样对于大多数查询来说都避免了回表查询）
• 对于字符类型索引，可以尝试建立前缀索引
• 对于建立联合索引（复合索引），必须把选择性高的列放在前面
• 建立覆盖索引来避免回表查询

当然，对索引的优化可以按照下面步骤来： - 去my.cnf里配置三个配置，输出慢查询日志，查询查询慢的SQL

#打开慢查询日志
slow_query_log=1
#慢查询日志存储路径
slow_query_log_file=/var/log/mysql/log-slow-queries.log
#SQL执行时间大于3秒，则记录日志
long_query_time=3

• 当我们看到慢SQL后，不是马上去建立索引，而是看能不能优化SQL。大多数情况下，业务SQL比较复杂，很难优化，因此建立索引要参照下面的规则：
  • (1)索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响insert,delete,update等语句的性能
  • (2)索引需要维护，因此避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引
  • (3)数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果
  • (4)在条件表达式中经常用到不同值较多的列上创建索引，在不同值很少的列上不要建立索引
  • (5)在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引，联合索引中列顺序按照选择性排列。

9.2 讲讲索引的分类？你知道哪些？

• 从物理存储角度:
  • 聚簇索引和非聚簇索引
• 从数据结构角度:
  • B+树索引、hash索引、
• 从逻辑角度:
  • 主键索引：主键索引是一种特殊的唯一索引，不允许有空值
  • 普通索引或者单列索引
  • 多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
  • 唯一索引或者非唯一索引

9.3 如何避免回表查询?什么是索引覆盖?

当能通过读取索引就可以得到想要的数据，那就不需要回表读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做索引覆盖。

9.4 现在我有一个列，里头的数据都是唯一的，需要建一个索引，选唯一索引还是普通索引？

因该用唯一索引。一方面唯一索引限制了所在列的值都是唯一的，这可以帮助确保数据的完整性。同样唯一索引查找过程只要找到就返回，而普通索引还要继续匹配下一条数据，因此查询的速度可能会比普通索引要好，而插入要差一点。。

这里衍生另一个问题

9.5 为什么唯一索引的插入速度比不上普通索引？为什么唯一索引的查找速度比普通索引快？

背景：MySQL数据库对数据的修改不会立马刷新在磁盘上，所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘，通过缓存池来优化cpu和磁盘之间的鸿沟。这么做的优点是能将多个插入合并到一个操作中，减小与磁盘的IO次数，提升数据库性能。

基于此，InnoDB 引入了 change pool缓存，InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。

唯一索引的插入速度比普通索引慢、查询快的原因就是:

• 在更新时，普通索引将记录放在change pool中即执行完毕；唯一索引为保证数据的唯一性，需要校验唯一性，必须将数据页读入内存确定没有冲突，然后才能继续操作,不能仅仅依赖于使用change pool；
• 查询时： 未使用limit 1的情况，唯一索引匹配到一条数据后即返回；普通索引回继续匹配下一条数据，发现不匹配后才返回。从这里看，唯一索引比较普通索引少了一次匹配过程。

这里又产生了另一个问题：

9.6 change buffer和buffer pool的区别

Buffer Pool用于缓存表数据和索引数据，提高查询性能；而Change Buffer用于延迟更新非聚集索引，提高写入性能

• Buffer Pool:
  • Buffer Pool是MySQL中的一个重要概念，它是一个内存区域，用于缓存数据库中的数据页。数据页是MySQL中用于存储表数据和索引数据的基本单位。
  • 当查询需要访问表数据或索引数据时，MySQL首先会尝试从Buffer Pool中查找相应的数据页，如果找到了，则可以直接返回数据，这样可以避免频繁地从磁盘读取数据，提高查询性能。
  • Buffer Pool的大小可以通过配置参数进行调整，通常会根据系统的内存大小和数据库的工作负载进行设置。
• Change Buffer:
  • Change Buffer是InnoDB存储引擎中的一个功能，用于延迟更新非聚集索引（即辅助索引）。
  • 当对表进行INSERT、UPDATE、DELETE等操作时，如果表上有非聚集索引，InnoDB会将这些操作的变化记录到Change Buffer中，而不是立即更新实际的索引页。
  • Change Buffer的存在可以减少对磁盘的随机写入操作，因为它将多个小的更新操作合并成更大的批量更新操作，这样可以提高写入性能。
  • 当查询需要访问被Change Buffer延迟更新的索引时，InnoDB会将Change Buffer中的变化应用到实际的索引页，然后返回查询结果。

9.7 为什么唯一索引的更新不使用 Change Buffer

因为唯一索引为了保证唯一性，需要将数据页加载进内存才能判断是否违反唯一性约束。但是，既然数据页都加载到内存了，还不如直接更新内存中的数据页，没有必要再使用Change Buffer。

9.8 mysql索引是什么结构的？用红黑树可以么？

MySQL常用的数据结构是B+ tree。B+有两个重要的特点：

• 一是非叶子节点不存储数据，数据只在聚簇索引的叶子节点处存储（数据文件和索引文件聚合在一起）。这表面设计者可以在非叶子节点存储更多的索引，B+树层数小，更加宽胖，减少磁盘的IO，提升数据库的性能。
• 二是B+树的叶子节点间使用了双向链表指针连接起来。这为MySQL的范围查找提供了更好的性能。 AVL树和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据数据存储的时候，显然不能将全部数据全部加载进内存，因此如果采用红黑树，就会造成频繁磁盘IO，效率低下。

9.8.1 那为啥不用B Tree,而选择B+ tree呢？

首先我们要看一下两者结构的区别

可以看到：

• B Tree的除了叶子节点外树内会存储数据，而B+树只在叶子节点存储数据。计算机从磁盘中读取数据是按块读取，每一个节点的数据大小正好或恰好接近一块磁盘大小。那么对于B+树来说，因为非叶子节点不存储数据，那么它们就可以存储更多的关键字和下一层索引，因此B+树会比B树更加宽胖，层数少，此时所进行的磁盘IO次数更少，效率更好。
• B Tree的除了叶子节没有双向链指针，而B+树叶子节点有链指针连接起来。B+树对范围查询更加友好、效率更高，它只需要遍历叶子节点就能够遍历整颗树，而B树则需要做局部的中序遍历才可以。 那你知道为啥Mongodb用B Tree当索引，而不用B+ Tree么

9.8.2 那为啥Mongodb用B Tree当索引，而不用B+ Tree么

这样从两个数据库的区别来说了，Mongodb是非关系性数据库，他用类Json格式来保存数据，它的数据更多的是聚合过的数据，因此对于MongoDB的查询更多关注单个查询，单个查询要优于MySQL；

而MySQL是关系型数据库，数据的关联性是非常强的，表之间的关系更加强烈，更多的关注于范围查询，在叶子节点有链表指针连接，更有利于遍历查找。

就综合性能来看，MongoDB的单个查询比Mysql的平均查询速度要快。

9.10 mysql某表建了多个单索引，查询多个条件时如何走索引的？

Mysql在优化器中有一个优化器称为Range优化器，负责进行范围查询的优化！那么该优化器计算执行成本有两种方式index dive与index statistics。它们是MySQL优化器对开销代价的估算方法，前者统计速度慢但是能得到精准的值，后者统计速度快但是数据未必精准。

9.11 mysql有哪几种log

事务日志（重做日志redo log、回滚日志undo log）、二进制日志(binlog)、错误日志(errorlog)、慢查询日志(slow query log)、一般查询日志(general log)，中继日志(relay log)

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

9.12 脏读、不可重复读和幻读

• 脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。（未提交读会出现脏读）

• 不可重复读：不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况。就比如事务A多次读取同一数据，但事务B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

• 幻读：所谓的幻读就是指当某个事务在读取某个范围内的记录时，另一个事务又在该范围插入新的记录，当之前的事务再次读取该范围记录时，产生幻行。（即幻读是对于某个范围的插入而言）

9.12.1 不可重复读和幻读区别是什么？

不可重复读的重点是修改，幻读的重点在于范围新增或者删除。

• 例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。
• 例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记 录就变为了5条，这样就导致了幻读。

9.13 drop、delete与truncate的区别

• Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小。
• delete是DML语句,用来删除表的全部或者一部分行数据，可以作为事务中的语句，因此能够回滚，事务提交后才会生效，会触发这个表上所有的delete触发器。
• Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用Drop；在想删除部分数据行时候，用Delete；在保留表而删除所有数据的时候用Truncate。

9.14 SQL优化方式

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如果索引是整形，那么可以在索引上设置默认值0，确保表中列没有 null值。(索引失效)
• 应尽量避免在where 子句中使用!= 或<> 操作符，否则引擎可能放弃使用索引而进行全表扫描。(索引失效)
• 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。(索引失效。
• 模糊搜索，如%abc或‘%abc%’ 会导致全表扫描,应避免这样使用。
• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。(索引失效)
• 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。(索引失效)
• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须最最左前缀匹配，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

9.14 索引在什么情况下会失效

• 在使用空 is null、不为空 is not null 这样的会失效。 订正：将某一列设置为default null，where 是可以走索引，另外索引列是否设置 null是不影响性能的。 但是，还是不建议列上允许为空。最好限制 not null，因为 null 需要更多的存储空间并且 null 值无法参与某些运算。 >《高性能MySQL》第四章如是说：And, in case you’re wondering, allowing NULL values in the index really doesn’t impact performance 。NULL 值索引查找流程见 8.13
• 1.不满足最左匹配原则会失效
• 2.索引列上即where子句中对字段进行表达式操作，会导致索引失效
• 3.索引列上即where子句中对字段进行函数操作，会导致索引失效
• 4.字段类型不匹配，会导致类型失效。如code字段是varchar类型，在where字句你使用code=101整型，就会导致索引失效
• 5.模糊搜索，左模糊或全模糊都会导致索引失效，如%a,%a%，会导致索引失效。这是因为索引的匹配规则是从左开始的。
• 6.使用索引的成本比走全表扫描的成本更高（由 MySQL 优化器决定），例如捞全表超过 50% 的数据；
• 7.当语句中带有or的时候，比如：select * from t where name=‘sw’ or age=14

9.15 如果写了一条sql，我怎么知道这条sql有没有走索引呢?

使用explain解释器来查看，在sql语句前面加上explain就可以来查看。explain中有多列，其中重点看type字段，type中的数据类型从优到差依次为：

system > const > eq_ref > ref > range > index >ALL

当我们写了一条sql语句发现他的type是ALL的时候我们就要考虑一下怎么优化一下了，因为ALL是最差的，我们就需要琢磨一下怎么优化，当然优化到system是最好的，但是一般不会优化到这种程度：

• ALL:全表扫描
• index:按B+树遍历索引树。
  • 如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，该Extra列显示Using index。这种通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
  • 按索引的顺序来查找数据行。此时，explain的Extra列的结果不会出现Uses index。
• fulltext：使用FULLTEXT索引进行搜索。
• range:以范围的形式扫描索引(in\>\<\>=\<=等等可能会触发范围扫描索引)
• ref:可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。
• eq_ref:用于联表查询的情况，按联表的主键或唯一键联合查询。
• const:针对主键或唯一索引的等值查询扫描, 最多只返回一行数据。表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。
• system：当表只有一行记录时的查询类型，是const的特例

一般保证查询至少达到range级别，最好能达到ref。

EXTR列：

• Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。如果同时出现Using where，代表使用索引来查找读取记录， 也是可以用到索引的，但是需要查询到数据表。
• Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。如果type列是ALL或index，而没有出现该信息，则你有可能在执行错误的查询：返回所有数据。
  
• Using filesort：不是“使用文件索引”的含义！filesort是MySQL所实现的一种排序策略，通常在使用到排序语句ORDER BY的时候，会出现该信息。
  
• Using temporary：表示为了得到结果，使用了临时表，这通常是出现在多表联合查询，结果排序的场合。

子查询在MySQL中确实可能会创建临时表。当执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。这种执行方式会消耗过多的CPU和IO资源，并可能产生大量的慢查询。因此，子查询的执行效率通常不高。 为了优化性能，尽量避免在查询中使用子查询，特别是在返回结果集比较大的情况下。可以使用连接（JOIN）查询来替代子查询，因为连接查询不需要建立临时表，速度通常比子查询要快。如果查询中使用索引的话，性能会进一步提升。

9.16 MySQL中为什么要有事务回滚机制？

而在 MySQL 中，恢复机制是通过回滚日志undo log实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。 当事务已经被提交之后，就无法再次回滚了。

回滚日志作用：

• 1)能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息
• 2. 在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。

9.17 数据库引擎InnoDB与MyISAM的区别

InnoDB和MyISAM是MySQL数据库中常用的两种存储引擎，它们在功能和性能方面有一些区别。以下是InnoDB和MyISAM之间的主要区别：

• 事务支持：InnoDB是一个支持事务的存储引擎，它遵循ACID（原子性、一致性、隔离性和持久性）特性。这意味着您可以使用事务来执行复杂的操作，例如多个SQL语句的原子性执行、数据的回滚和提交。而MyISAM不支持事务，它以更简单的方式处理数据操作。

• 锁：InnoDB对于高并发性能的支持较好。它支持行级锁定，这使得多个用户可以同时访问不同的行，提高了并发性。而MyISAM只支持表级锁定，当一个用户对表执行写操作时，其他用户将无法对同一表进行写操作，这可能导致并发性能较差。并且Innodb有意向锁，MyIsAM没有意向锁的概念。

• 数据完整性：InnoDB提供了更高的数据完整性保证。它支持外键约束，可以在数据库级别强制执行关联性，并确保引用的数据完整性。MyISAM不支持外键约束，这意味着您需要在应用层面来维护数据的完整性。

• 崩溃恢复：InnoDB具有更好的崩溃恢复能力。它支持崩溃恢复和事务日志（redo log和undo log），可以在数据库崩溃后恢复数据。MyISAM在崩溃后需要进行表级别的修复，这可能需要更多时间。

• 全文索引：MyISAM支持全文索引，可以进行高效的全文搜索。而InnoDB在MySQL 5.6版本之后才开始支持全文索引。

• 空间占用：通常情况下，InnoDB的数据文件相对较大，因为它需要存储事务日志和其他附加数据。而MyISAM的数据文件相对较小，因为它不需要存储这些额外的数据。

• 存储结构：
  • InnoDB使用聚簇索引（clustered index）来存储表数据，因此数据文件是和（主键）索引绑在一起的，即数据文件和索引文件在一块。聚簇索引决定了表中数据的物理存储顺序，它与表的主键相关联。如果表没有显式定义主键，则InnoDB会选择一个唯一的非空索引来作为聚簇索引。因此，在InnoDB中，数据行按照聚簇索引的顺序进行存储。
  • 同时除了聚簇索引外，InnoDB还支持非聚簇索引（secondary index），也称为辅助索引。辅助索引存储了非主键列的索引数据，它的数据结构与B+树相似。
  • MyISAM使用堆表（heap table）的存储方式，数据行在表中按照插入的顺序存储。因此，表数据在物理上没有特定的顺序，而是根据插入的时间顺序组织的，因此MyISAM的数据文件和索引文件独立存储。
  • MyISAM使用B+树来存储索引数据。每个索引都是一个独立的B+树结构，包含索引列和指向对应数据行的指针。

综上所述，选择使用哪种存储引擎取决于您的具体需求。如果您需要支持事务、并发性能和数据完整性，并且对崩溃恢复有较高的要求，那么InnoDB是一个更好的选择。如果您对全文搜索有较高的需求，并且对空间占用和简单性要求较高，那么MyISAM可能更适合您的应用场景。请根据您的具体需求和考虑因素选择适合的存储引擎。

9.18 你了解MySQL的内部构造吗？一般可以分为哪两个部分？

可以分为服务层和存储引擎层两部分，其中：

• 服务层：包括连接器、查询缓存、解析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
  • 连接器：管理连接、权限验证；
  • 查询缓存：命中缓存则直接返回结果；
  • 解析器：对SQL进行词法分析、语法分析；（判断查询的SQL字段是否存在也是在这步）
  • 优化器：执行计划生成、选择索引；
  • 执行器：操作引擎、返回结果；
• 存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认的存储引擎。
  • 存储引擎：存储数据、提供读写接口

9.19 什么是主键?什么是外键?

主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。 主键在本表中是唯一的、不可为空的，外键可以重复可以唯空；外键和另一张表的主键关联，不能创建对应表中不存在的外键。

9.20 乐观锁和悲观锁

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。它假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。一般都是通过版本号来识别
• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。它假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

注意：读用乐观锁，写用悲观锁 共享锁（Share Locks，S锁）：一个线程给数据加上共享锁后，其他线程只能读取数据，不能修改。 排它锁（eXclusive Locks，X锁）：一个线程给数据加上排它锁后，其他线程不能读取也不能修改。 没有锁：InnoDB所有的普通select都是快照读，都不加锁。

手动加锁： select * from table_name where ... lock in share mode 会给事务加上共享锁；

select * from table_name where ... for update 会给事务加上排它锁。

9.21 写一个典型的乐观锁SQL语句，有什么需要注意的问题？

乐观锁一般来说有以下2种方式：

• 使用数据版本（Version）记录机制实现（MVCC)，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version 字段来实现。当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的 version 值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据。
• 使用时间戳（timestamp）。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的 table 中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的 version 类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

乐观锁和悲观锁的这种特性决定了：读用乐观锁，写用悲观锁。

9.22 MySQL索引主要使用的两种数据结构是什么？

• 哈希索引，对于哈希索引来说，底层的数据结构肯定是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引

• BTree索引，Mysql的BTree索引使用的是B树中的B+Tree，BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，每次查询都是从树的入口root开始，依次遍历node，获取leaf。

但对于主要的两种存储引擎（MyISAM和InnoDB）的实现方式是不同的。

9.23 数据库为什么要进行分库和分表呢？都放在一个库或者一张表中不可以吗？

分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。

通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少了，起到提高查询性能，缩短查询时间的作用，此外，可以很大的缓解表锁的问题。 分表策略可以归纳为垂直拆分和水平拆分

• 水平分表：水平分表解决行数据量大问题。可使用取模分表就属于随机分表，而时间维度分表则属于连续分表。

• 垂直分表：垂直分表解决列数大问题。垂直分表策略
  • 将不常用的字段单独拆分到另外一张扩展表.
  • 将大文本的字段单独拆分到另外一张扩展表,
  • 将不经常修改的字段放在同一张表中，将经常改变的字段放在另一张表中

对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。

9.24 MySQL中有四种索引类型，可以简单说说吗？

• FULLTEXT ：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX使用，不过目前只有CHAR、VARCHAR ，TEXT列上可以创建全文索引，需要注意的是MySQL5.6以后支持全文索引了，5.6之前是不支持的。

• HASH ：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

• BTREE ：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

• RTREE ：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。 相对于BTREE，RTREE的优势在于范围查找。

9.25 视图的作用是什么？可以更改吗？

视图是虚拟的表，其不包含任何列或数据。其实视图可以看作已经定义好的SQL语句，只不过它贮存在数据库中，其数据都存放在定义视图查询所引用的真实表中。创建视图

create view 视图名称 as 查询语句

优点：

• 编写复杂的SQL并且重用：使用视图可以简化复杂的 sql 操作，隐藏具体的细节，且视图可以以表的形式使用。
• 数据安全：MySQL将用户对数据的访问权限限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性 。视图相当于在用户和实际的数据表之间加了一层虚拟表
• 简化用户操作，视图不仅可以简化用户对数据的理解，也可以简化他们的操作。

视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。

缺点：

• 视图不能被索引。
• 维护工作多：我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对 相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。

9.26 什么时候需要建立数据库索引呢？

• 当我们对一个表中某字段访问频繁时，经常出现在where、order by、groupby子句时，可以考虑建立索引
• 对于中大表，即数据量多的，可以考虑选择适当的字段建立索引

在最频繁使用的、用以缩小查询范围的字段,需要排序的字段上建立索引。

不宜：

• 1）对于查询中很少涉及的列或者重复值比较多的列
• 2）对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等。

9.27 一道场景题：假如你所在的公司选择MySQL数据库作数据存储，一天五万条以上的增量，预计运维三年，你有哪些优化手段？

• 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
• 选择合适的表字段数据类型和存储引擎，适当的添加索引。
• MySQL库主从读写分离。
• 找规律分表，减少单表中的数据量提高查询速度。
• 添加缓存机制，比如Memcached，Apc等。
• 不经常改动的页面，生成静态页面。
• 书写高效率的SQL。比如SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE

9.28 数据库中的主键、超键、候选键、外键是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键

• 候选键：不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！

• 主键：用户选作元组标识的一个候选键为主键

• ** 外键：**如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键

举例：

学号	姓名	性别	年龄	系别	专业
20020612	李辉	男	20	计算机	软件开发
20060613	张明	男	18	计算机	软件开发
20060614	王小玉	女	19	物理	力学
20060615	李淑华	女	17	生物	动物学

• 超键：于是我们从例子中可以发现 学号是标识学生实体的唯一标识。那么该元组的超键就为学号。除此之外我们还可以把它跟其他属性组合起来，比如：(学号，性别)，(学号，年龄)
• 候选键：根据例子可知，学号是一个可以唯一标识元组的唯一标识，因此学号是一个候选键，实际上，候选键是超键的子集，比如 （学号，年龄）是超键，但是它不是候选键。因为它还有了额外的属性。
• 主键：简单的说，例子中的元组的候选键为学号，但是我们选定他作为该元组的唯一标识，那么学号就为主键。
• 外键是相对于主键的，比如在学生记录里，主键为学号，在成绩单表中也有学号字段，因此学号为成绩单表的外键，为学生表的主键。

主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的。

9.29 数据库三大范式精讲

• 第一范式（1NF）:数据库表中的所有字段值都是不可分解的原子值。
  • 这张表的设计就不符合第一范式(1NF)，因为“地址”这个属性可以继续拆分成“省份”和“城市”两个属性，假设有一天公司需要统计来自某个省份或者某个城市的所有员工信息的话，这样分类就非常方便了。
  • 解决办法：按省份、城市分两个字段存储
• 第二范式（2NF）:第二范式(2NF)是在第一范式(1NF)的基础之上更进一步。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
  • 比如说，你有一个表格记录了学生课程成绩的表，包含的姓名stuName、性别sex、年龄age、学号stuId、课程courseName和课程IDcourseId、成绩 。上表中stuName、age、sex 只与 stuId 相关，courseName 只与 courseId 相关，和第二范式(2NF)中规定的需要确保数据库表中的每一列都和主键相关这个规则相违背，所以上述这张表的设计不符合第二范式(2NF)。
  • 解决办法：可以将上述“学生课程成绩表”拆分成“学生”表、“课程”表和“学生课程成绩”表。
• 第三范式（3NF）:需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
  • 用 A、B、C、D 来表示表中的四个列，其中 A 为主键，其中 B → A(B依赖A), C → A，D → A，如果还有 B → C, C → D 从这两个还可以推导出 B → D, 此时虽然满足第二范式(2NF)，但是不满足第三范式。
  • 比如说，有一张学生表，这张表的主键是 stdId，因为这个属性能够确定这张表的其他属性，通过 stdId 就可以知道学生姓名、年龄、性别、班级编号、班级名称、班级人数信息。但是仔细观察可以发现，班级名称、班级人数还可以通过 classId 确定，而 classId 是非主属性，这样就存在了一个传递依赖，并且造成数据的冗余。
  • 解决这个问题就需要将上述表拆成“学生”表和“班级”表，一张表记录学生信息，另一张表记录班级信息，两张表通过外键进行关联

总的来说，数据库的三大范式就是要求我们设计表格的时候要注意数据的完整性、减少冗余，并且让表格之间的关系更加清晰、易于维护。虽然在实际应用中我们可能需要根据具体情况做一些调整，但是这三个范式还是给我们提供了一个很好的设计思路。

9.30 你知道哪些数据库结构优化的手段？

• 范式优化： 比如消除冗余（节省空间。。）
• 反范式优化：比如适当加冗余等（减少join）
• 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
• 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
• 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

9.31 为什么MySQL索引要使用B+树，而不是B树或者红黑树？

我们在MySQL中的数据一般是放在磁盘中的，读取数据的时候肯定会有访问磁盘的操作，磁盘中有两个机械运动的部分，分别是盘片旋转和磁臂移动。盘片旋转就是我们市面上所提到的多少转每分钟，而磁盘移动则是在盘片旋转到指定位置以后，移动磁臂后开始进行数据的读写。那么这就存在一个定位到磁盘中的块的过程，而定位是磁盘的存取中花费时间比较大的一块，毕竟机械运动花费的时候要远远大于电子运动的时间。当大规模数据存储到磁盘中的时候，显然定位是一个非常花费时间的过程，但是我们可以通过B树进行优化，提高磁盘读取时定位的效率。

为什么B类树可以进行优化呢？我们可以根据B类树的特点，构造一个多阶的B类树，然后在尽量多的在结点上存储相关的信息，保证层数（树的高度）尽量的少，以便后面我们可以更快的找到信息，磁盘的I/O操作也少一些，而且B类树是平衡树，每个结点到叶子结点的高度都是相同，这也保证了每个查询是稳定的。

9.32 为什么MySQL索引适用用B+树而不用hash表和B树？

• 利用Hash需要把数据全部加载到内存中，如果数据量大，是一件很消耗内存的事，而采用B+树，是基于按照节点分段加载，由此减少内存消耗。
• 和业务场景有关，对于唯一查找（查找一个值），Hash确实更快，但数据库中经常查询多条数据，这时候由于B+数据的有序性，与叶子节点又有链表相连，他的查询效率会比Hash快的多。
• b+树的非叶子节点不保存数据，只保存子树的临界值（最大或者最小），所以同样大小的节点，b+树相对于b树能够有更多的分支，使得这棵树更加矮胖，查询时做的IO操作次数也更少。

9.33 数据库如何保证一致性？

分为两个层面来说。

• 从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。
• 从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据

9.34 数据库如何保证原子性？

主要是利用 Innodb 的undo log。 undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的 SQL语句，他需要记录你要回滚的相应日志信息。 例如

• 当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
• 当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
• 当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子

9.35 数据库如何保证持久性？

主要是利用Innodb的redo log。重写日志， 正如之前说的，MySQL是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再写回到磁盘上。如果此时突然宕机，内存中的数据就会丢失。 怎么解决这个问题？ 简单啊，事务提交前直接把数据写入磁盘就行啊。 这么做有什么问题？

• 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
• 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

采用redo log的好处？

其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下：

• redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
• redo log是一直往末尾进行追加，属于顺序
• IO。效率显然比随机IO来的快

8.36 数据库高并发是我们经常会遇到的，你有什么好的解决方案吗？

• 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
• 增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
• 主从读写分离，让主服务器负责写，从服务器负责读。
• 将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。
• 使用分布式架构，分散计算压力。

9.37 Mysql中的对NULL值的索引查找流程？

上面我们提到过在使用空 is null、不为空 is not null 查询时是不一定会导致索引失效的，查询执行计划中可以看出来，这些语句有时采用了相应的二级索引执行查询，而不是使用所谓的全表扫描。

9.37.1 NULL值是怎么在记录中存储的

在MySQL中，每一条记录都有它固定的格式，以InnoDB存储引擎的Compact行格式为例，来看一下NULL值是怎样存储的。在Compact行格式下，一条记录是由下边这几个部分构成的：

• 其中NULL值会存储在NULL值列表，占的字节数依据你定义的NULL值字段个数决定，NULL 值并不会存储在行格式中的真实数据部分。（如果一行记录的所有字段都声明为NOT NULL,则没有NULL值列表）
• 变长字段长度列表：记录变长字段的所占的空间

新建一个称之为record_format_demo的表：

CREATE TABLE record_format_demo (
     c1 VARCHAR(10),
     c2 VARCHAR(10) NOT NULL,
     c3 CHAR(10),
     c4 VARCHAR(10)
 ) CHARSET=ascii ROW_FORMAT=COMPACT;

这里重点是NULL值是如何存储在记录中的，所以重点看一下行格式的NULL值列表部分。存储NULL值的过程如下：

1. 首先统计表中允许存储NULL的列有哪些。

前边说过，主键列、被NOT NULL修饰的列都是不可以存储NULL值的，所以在统计的时候不会把这些列算进去。比方说表record_format_demo的3个列c1、c3、c4都是允许存储NULL值的，而c2列是被NOT NULL修饰，不允许存储NULL值。

2. 如果表中没有允许存储NULL的列，则NULL值列表也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：
   • 二进制位的值为1时，代表该列的值为NULL。
   • 二进制位的值为0时，代表该列的值不为NULL。

因为表record_format_demo有3个值允许为NULL的列，所以这3个列和二进制位的对应关系就是这样：

3. 设计InnoDB的大叔规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

表record_format_demo只有3个值允许为NULL的列，对应3个二进制位，不足一个字节，所以在字节的高位补0，效果就是这样： 以此类推，如果一个表中有9个允许为NULL，那这个记录的NULL值列表部分就需要2个字节来表示了。

假设现在向record_format_demo表中插入一条记录：

INSERT INTO record_format_demo(c1, c2, c3, c4)
    VALUES('eeee', 'fff', NULL, NULL);

这条记录的c1、c3、c4这3个列中c3和c4的值都为NULL，所以这3个列对应的二进制位的情况就是： 所以这记录的NULL值列表用十六进制表示就是：0x06。

9.37.2 键值为NULL的记录是怎么在B+树中存放的

对于InnoDB存储引擎来说，记录都是存储在页面中的（一个页面默认是16KB大小），这些页面可以作为B+树的节点而组成一个索引，

按规定，一条记录的主键值不允许存储NULL值，所以下边语句中的WHERE子句结果肯定为FALSE：

SELECT * FROM tbl_name WHERE primary_key IS NULL;

像这样的语句优化器自己就能判定出WHERE子句必定为NULL，所以压根儿不会去执行它，看下图（Extra信息提示WHERE子句不成立）： 对于二级索引来说，索引列的值可能为NULL。那对于索引列值为NULL的二级索引记录来说，它们被放在B+树的最左边。比方说有如下查询语句：

SELECT * FROM s1 WHERE key1 IS NULL;

那它的查询示意图就如下所示： 从图中可以看出，对于s1表的二级索引idx_key1来说，值为NULL的二级索引记录都被放在了B+树的最左边，这是因为设计InnoDB的大叔有这样的规定： >We define the SQL null to be the smallest possible value of a field. 也就是说他们把SQL中的NULL值认为是列中最小的值。

在通过二级索引idx_key1对应的B+树快速定位到叶子节点中符合条件的最左边的那条记录后，也就是本例中id值为521的那条记录之后，就可以顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了，直到某条记录的key1列不为NULL。

9.37.3 那你说是对NULL查询有时候走，有时候不走，使不使用索引的依据到底是什么？

那既然IS NULL、IS NOT NULL、!=这些条件都可能使用到索引，那到底什么时候索引，什么时候采用全表扫描呢？

答案很简单：成本。当然，关于如何定量的计算使用某个索引执行查询的成本比较复杂，在这里只定性的分析一下。对于使用二级索引进行查询来说，成本组成主要有两个方面：

• 读取二级索引记录的成本
• 将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。

很显然，要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）。

所以MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量，比方说对于下边这个查询：

SELECT * FROM s1 WHERE key1 IS NULL;

优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值，当然算法也比较麻烦），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话就极有可能直接全表扫描。

可以看到，MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。

9.38 CHAR和VARCHAR的区别

字符串主要有CHAR和VARCHAR两种，一种是定长的，一种是变长的。

VARCHAR这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行UPDATE时可能会时行变得比原来长，当超出一个页所能容纳的大小时，就需要执行额外的操作。MyISAM会将行拆成不同的片段存储，而InnoDB则需要分裂页来使行放入页内。

进行存储和检索时，会保留VARCHAR末尾的空格，而会删除CHAR末尾的空格。

9.39 Varchar 的最大长度以及超长问题

4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）

5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节

varchar 最多能存储 65535 个字节的数据。varchar 的最大长度受限于最大行长度（max row size，65535 bytes）。65535 并不是一个很精确的上限，可以继续缩小这个上限。65535 个字节包括所有字段的长度，变长字段的长度标识（每个变长字段额外使用 1 或者 2 个字节记录实际数据长度）、NULL值列表的累计。

NULL值列表，如果 varchar 字段定义中带有 default null 允许列空,则需要需要 1bit 来标识，每 8 个 bits 的标识组成一个字段。一张表中存在 N 个 varchar 字段，那么需要（N+7）/8 （取整） bytes 存储所有的 NULL 标识位。

因为varchar类型存储变长字段的字符类型，其存储时需要在前缀长度列表加上实际存储的字符，当存储的字符串长度小于255字节时，其需要 1 字节的空间，当大于 255 字节时，需要 2 字节的空间。

如果数据表只有一个 varchar 字段且该字段 DEFAULT NULL 并且大于 255 字节，那么该 varchar 字段的最大长度为 65532 个字节，即 65535-2-1=65532 byte。

行长度限制：

导致实际应用中 varchar 长度限制的是一个行定义的长度。 MySQL 要求一个行的定义长度不能超过 65535。若定义的表长度超过这个值，则提示row size too large错误

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

9.40 count（*），count（字段），count(主键字段），count（1） 区别？ 直接说结论，性能排序如下： count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中

9.40.1 count(主键字段) 执行过程是怎样的？

在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。

server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。

InnoDB 是通过 B+ 树来保存记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。

用下面这条语句作为例子：

//id 为主键值
select count(id) from t_order;

如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加

但是，如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。

这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。

9.40.2 count(1) 执行过程是怎样的？

用下面这条语句作为例子：

select count(1) from t_order;

- 如果表里只有主键索引，没有二级索引时。那么，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。

可以看到，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值是否为NULL，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

• 但是，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。

9.40.3 count() 执行过程是怎样的？ 看到 这个字符的时候，是不是大家觉得是读取记录中的所有字段值？

对于 selete * 这条语句来说是这个意思，但是在 count(*) 中并不是这个意思。

count(*) 其实等于 count(0)，也就是说，当你使用 count() 时，MySQL 会将 参数转化为参数 0 来处理。

**所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。**

9.40.2 count(字段) 执行过程是怎样的？

count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。

// name不是索引，普通字段
select count(name) from t_order;

对于这个查询来说，会采用全表扫描的方式来计数，所以它的执行效率是比较差的。

总结：

• count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

• 所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

• 再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。

9.41 执行一条 SQL 查询语句，期间发生了什么？

• 1.连接器：建立连接，管理连接、校验用户身份；
• 2.查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 3.解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 4.执行 SQL：执行 SQL 共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

9.42 表优化方案

9.42.1 单表优化

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

• 字段建立规则
  • 单表不要有太多字段，建议在20以内
  • 避免使用NULL字段，很难查询优化且占用额外索引空间
  • 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
  • VARCHAR的长度只分配真正需要的空间
• 索引
  • 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
  • 值分布很稀少的字段不适合建索引，例如”性别”这种只有两三个值的字段
  • 对于字符字段，如果实在需要建立索引，只建前缀索引
  • 使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引

9.43 mysql分表依据

不同的分表策略适用于不同的业务场景，以下是每种分表策略的适用场景分析：

• 按时间分表
  • 场景：适用于具有明显时间特征的数据，比如日志系统、订单系统、审计数据等。这些系统通常会产生大量的时间序列数据，数据逐渐变老，查询的频率会逐渐降低。
  • 应用场景：
    • 日志系统：如网站访问日志、API调用日志，每天或每月生成大量日志数据。
    • 订单系统：电商平台、支付系统中订单记录按日期查询。
  • 优势：便于管理历史数据，查询时只需要查当前时间段的数据表，避免扫描过多的历史数据。
• 按ID范围分表
  • 场景：适用于数据增长比较有规律且ID是自增的业务场景，比如用户系统、订单系统、商品系统等。
  • 应用场景：
    • 用户系统：如社交平台中，每个用户有唯一的ID，用户数据增长相对有规律。
    • 订单系统：如电商平台，每个订单有唯一的ID，且订单数量在不同时间段的增长趋势可以预估。
  • 优势：可以根据ID的范围快速分配数据，插入和查询效率较高。
• 按Hash分表
  • 场景：适用于数据比较均匀分布且需要高并发写入的场景，比如社交网络、用户行为记录、商品浏览等。这些场景下需要避免数据集中到某些表，保持均衡的负载分布。
  • 应用场景：
    • 社交平台：如用户发布动态、评论等，需要高并发写入。
    • 游戏数据：如玩家的游戏记录、积分等，需要均匀分配到不同的表中以减少单表压力。
  • 优势：数据分布均匀，避免热点表的产生，适合高并发写入和查询。
• 按地区或业务维度分表
  • 场景：适用于具有明确业务维度、地域维度的系统，比如多地区电商平台、全球化业务系统等。不同地区的数据可以独立分表，方便管理和优化查询。
  • 应用场景：
    • 电商平台：按国家或区域划分订单数据，比如orders_asia，orders_europe。
    • 多租户系统：如SaaS平台，为每个租户（客户）创建单独的表，便于管理和隔离数据。
  • 优势：通过业务维度分表可以显著提高查询效率，减少跨地区、跨业务模块的查询和写入压力。
• 按冷热数据分表
  • 场景：适用于数据访问频率有明显差异的业务，比如订单系统、日志系统等。在这些系统中，最新的数据被频繁访问，而历史数据访问较少。
  • 应用场景：
    • 电商平台：订单数据可以按照冷热划分，比如1个月内的订单是热数据，1年以上的是冷数据。
    • 内容管理系统：如博客、论坛，热门内容和历史内容的访问频率差异较大。
  • 优势：优化查询性能，将常用数据与历史数据分离，减少查询开销。热数据表体积小，查询快，而冷数据表可以放在低性能存储中。

9.43 索引下推是什么？

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是 MySQL 5.6 发布后针对扫描二级索引的一项优化改进。还有就是索引下推只能发生在非聚簇的联合索引处，非联合索引没有索引下推

总的来说是把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表（聚簇索引）的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB。

• 在不使用ICP的情况下，在使用非主键联合索引索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过Server传递下来的最左条件在二级索引检索，然后回表（数据量多会多次回表）查询，查询完后到数据然后返回给MySQL服务器，由Server判断数据是否符合后续条件条件。
  • 比如一个联合索引(name,age)，查询语句是select * from tuser where name like '李%' and age=11;
    • 1.此时，Server给存储引擎name like '李%' 存储引擎根据 name like '李%' 查询出所有符合的数据列，根据数据列对应主键 ID 回表查询，并返回 Service 层。
    • 2.Service 层根据 age=11 筛选符合条件的数据返回客户端。
• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将些判断条件都传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
  • 存储引擎根据 name like '李%' 查询出所有符合的数据列，由于使用的是联合索引，数据列中包含 age 的值，存储引擎再根据 age=11 过滤出符合条件的数据列，根据数据列对应主键 ID 回表查询，并返回 Service 层。

因此，索引下推是联合索引优化查询的一个优化操作：

• 减少了回表的操作次数。

• 减少了上传到 MySQL SERVER 层的数据。

ICP 默认开启，可通过优化器开关参数关闭 ICP：optimizer_switch='index_condition_pushdown=off'或者是在 SQL 层面通过 HINT 来关闭。

9.44 exist 和 in 区别

• in的实例SQL

  select * from tabA where tabA.x in (select x from tabB where y>0 );

  执行过程：
  • （1）执行tabB表的子查询，得到结果集B，可以使用到 tabB 表的索引 y；
  • （2）执行tabA表的查询，查询条件是tabA.x在结果集B里面，可以使用到tabA表的索引 x。 >in查询是将子查询的结果集作为主查询的验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

• exists的示例SQL

  select from tabA where exists (select from tabB where y>0);

  执行过程：
  • （1）先将tabA表所有记录取到。
  • （2）逐行针对 tabA 表的记录，去关联 tabB 表，判断 tabB 表的子查询是否有返回数据，(5.5之后的版本) 使用Block Nested Loop(Block 嵌套循环)。
  • （3）如果子查询有返回数据，则将tabA当前记录返回到结果集。 tabA相当于取全表数据遍历，tabB可以使用到索引。 >EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

总结来说有 4 点：

• in 查询时首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。
• 子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据。
• 两表大小相当，in 和 exists 差别不大。内表大，用 exists 效率较高；内表小，用 in 效率较高。
• 查询用 not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。not exists 都比 not in 要快。

9.45 什么是深分页，如何解决？

深分页指的是在分页查询中，当用户需要访问数据集的 靠后页码（如第 1000 页）时，数据库需要跳过大量前置数据（如前 999 页）才能获取目标结果。这种场景下，如传统 LIMIT offset, size 分页方式性能会急剧下降，甚至导致超时或资源耗尽。：

SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

- 执行过程： 1. 扫描全表或索引，找到前 offset + size 条数据（本例扫描 1,000,020 行）。 2. 丢弃前 offset 条（1,000,000 行），仅返回最后 20 条。 - 问题：大量无效数据的扫描和排序导致 CPU、I/O 资源浪费。

• 大偏移量（offset）：偏移量越大，扫描的数据量呈线性增长。

• 排序成本：若未命中索引排序，需在内存或磁盘进行临时排序（Using filesort）。

• 解决方案
  • 基于游标的连续分页（Cursor-based Pagination）**
    • 原理：记录上一页最后一条记录的标识（如自增ID、时间戳），下次查询时直接定位。
    • 优化 SQL：

-- 初始查询（第一页）
SELECT * FROM orders 
WHERE id > 0  -- 起始点
ORDER BY id 
LIMIT 20;

-- 后续查询（基于上一页最后一条记录的id=100）
SELECT * FROM orders 
WHERE id > 100  -- 游标值
ORDER BY id 
LIMIT 20;

- **优点**：
  - 时间复杂度从 `O(N)` 降为 `O(1)`。
  - 避免偏移量计算，适合无限滚动或顺序浏览场景。
- **缺点**：
  - 不支持随机跳页（如直接跳转到第 1000 页）。
  - 需数据有唯一且有序的字段（如自增主键、时间戳）。

• 覆盖索引 + 延迟关联（Covering Index + Delayed Join）**
  • 原理：先通过覆盖索引快速定位目标行，再关联回原表取完整数据。
  • 优化 SQL：

    SELECT * FROM orders 
    JOIN (
      SELECT id FROM orders 
      ORDER BY create_time 
      LIMIT 1000000, 20
    ) AS tmp 
    ON orders.id = tmp.id;

  • 优点：
    • 内层查询仅扫描索引（无需回表），减少数据量。
    • 外层通过主键关联，效率高。
  • 适用场景：排序字段有索引，且查询字段较多。
• 各方案对比

方案	适用场景	优点	缺点
游标分页	顺序浏览（如APP无限滚动）	性能最优，无偏移量问题	不支持随机跳页
覆盖索引+延迟关联	需复杂排序的深分页	减少回表，利用索引加速	需索引支持，SQL较复杂

参考文献： >《高性能MySQL》 >《 MySQL技术内幕：InnoDB存储引擎（第2版）》