MYSQL相关知识总结

朴灿烈づ我的快乐病毒、 2023-02-18 11:55 18阅读 0赞

### MYSQL相关知识总结 ###

1.  如何在 MySQL 中对一条 SQL 语句的执行时间？

select @@profiling; //首先我们需要看下 profiling 是否开启
     如果@@profiling的值为0的话，使用set profiling = 1 来打开profiling.
     show profiles; 查看当前会话所产生的所有 profiles：
     show profile;查看上一次查询所使用的查询时间
     show profile for query 2; 查询指定的 Query ID
     select version(); 查看mysql 的版本

1.  MyISAM 、 InnoDB、 MyISAM 这两个搜索引擎的特性和使用场景分别是什么

InnoDB 存储引擎：最大特点是支持事务、行级锁定、外键约束等，在update和insert比较多的情况下可以使用它  
MylSAM：不支持事务、也不支持外键，使用表级锁，并发性差，最大特点是速度快、占用资源少。在select比较多的时候可以用他。

MEMORY 存储引擎：供内存表，也不支持事务和外键。显著提高访问数据的速度，可用于缓存会频繁访问的数据。服务器重启后数据会丢失，复制维护时需要小心

MyISAM的使用场景为读写分离的读库， 而InnoDB为写库。

1.  表列的常用操作？

ALTER TABLE player ADD (age INT(11)); #给player表增加一列
    ALTER TABLE player CHANGE age player_age int(11) #修改列的名称
    ALTER TABLE player MODIFY player_age CHAR(10) #修改表列的类型
    ALTER TABLE player CHANGE player_age player_age CHAR(10) #修改表列的类型
    ALTER TABLE player DROP COLUMN player_age #删除表列

1.  主键、外键和索引三者的作用和区别？

主键是能确定一条记录的唯一标识。外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性

<table> 
 <thead> 
  <tr> 
   <th align="center"></th> 
   <th align="left">主键</th> 
   <th align="left">外键</th> 
   <th align="center">索引</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">定义</td> 
   <td align="left">唯一标识一条记录，不能有重复的，不允许为空</td> 
   <td align="left">表的外键是另一表的主键, 外键可以有重复的, 可以是空值</td> 
   <td align="center">该字段没有重复值，但可以有一个空值</td> 
  </tr> 
  <tr> 
   <td align="center">作用</td> 
   <td align="left">唯一标识数据记录，保证数据的完整性</td> 
   <td align="left">用来和其他表建立联系用的,用来保证数据的一致性</td> 
   <td align="center">是提高查询排序的速度</td> 
  </tr> 
  <tr> 
   <td align="center">个数</td> 
   <td align="left">主键只能有一个</td> 
   <td align="left">一个表可以有多个外键</td> 
   <td align="center">一个表可以有多个惟一索引</td> 
  </tr> 
 </tbody> 
</table>

1.  SELECT 语句的执行顺序

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

1.  sql 常用的函数

<table> 
 <thead> 
  <tr> 
   <th align="center">函数名</th> 
   <th align="center">定义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">ABS()</td> 
   <td align="center">绝对值函数</td> 
  </tr> 
  <tr> 
   <td align="center">MOD()</td> 
   <td align="center">取余</td> 
  </tr> 
  <tr> 
   <td align="center">ROUND(name,num)</td> 
   <td align="center">四舍五入为指定的小数位数，</td> 
  </tr> 
  <tr> 
   <td align="center">CONCAT()</td> 
   <td align="center"><code>SELECT CONCAT('abc', 123)</code> 将多个字符串拼接起来</td> 
  </tr> 
  <tr> 
   <td align="center">LENGTH()</td> 
   <td align="center">SELECT LENGTH(‘你好’) = 6 ,计算字段的长度，一个汉字算三个字符，一个数字或者字母算1一个字符</td> 
  </tr> 
  <tr> 
   <td align="center">CHAR_LENGTH()</td> 
   <td align="center"><code>SELECT CHAR_LENGTH('你好')</code>，运行结果为 2。计算字段的长度，将汉字、字母、数字都看作是一个字符。</td> 
  </tr> 
  <tr> 
   <td align="center">LOWER()</td> 
   <td align="center">将字符串中的字母都变为小写字母</td> 
  </tr> 
  <tr> 
   <td align="center">UPPER()</td> 
   <td align="center">将字符串中的字母都变为大写字母。</td> 
  </tr> 
  <tr> 
   <td align="center">REPLACE()</td> 
   <td align="center"><code>SELECT REPLACE('fabcd', 'abc', 123)</code>，运行结果为 f123d。替换函数。</td> 
  </tr> 
  <tr> 
   <td align="center">SUBSTRING()</td> 
   <td align="center"><code>SELECT SUBSTRING('fabcd', 1,3)</code>，运行结果为 fab。字符串截取函数。</td> 
  </tr> 
  <tr> 
   <td align="center">CURRENT_DATE()</td> 
   <td align="center">SELECT CURRENT_DATE()； 获取系统当前的日期xxxx-xx-xx</td> 
  </tr> 
  <tr> 
   <td align="center">CURRENT_TIME()</td> 
   <td align="center">SELECT CURRENT_TIME(); 获取系统当前的时间，xx:xx:xx</td> 
  </tr> 
  <tr> 
   <td align="center">CURRENT_TIMESTAMP()</td> 
   <td align="center">SELECT CURRENT_TIMESTAMP(); xxxx-xx-xx xx:xx:xx, 系统当前日期和时间。</td> 
  </tr> 
  <tr> 
   <td align="center">EXTRACT()</td> 
   <td align="center"><code>SELECT EXTRACT(YEAR FROM '2019-04-03')</code>，运行结果为 2019。 抽取具体的年月日</td> 
  </tr> 
  <tr> 
   <td align="center">DATE()</td> 
   <td align="center">返回时间的日期部分</td> 
  </tr> 
  <tr> 
   <td align="center">YEAR()</td> 
   <td align="center">返回时间的年份部分</td> 
  </tr> 
  <tr> 
   <td align="center">MONTH()</td> 
   <td align="center">返回时间的月份部分</td> 
  </tr> 
  <tr> 
   <td align="center">DAY()</td> 
   <td align="center">返回时间的天数部分</td> 
  </tr> 
  <tr> 
   <td align="center">HOUR()</td> 
   <td align="center">返回时间的小时部分</td> 
  </tr> 
  <tr> 
   <td align="center">MINUTE()</td> 
   <td align="center">返回时间的分钟部分</td> 
  </tr> 
  <tr> 
   <td align="center">SECOND()</td> 
   <td align="center">返回时间额秒部分</td> 
  </tr> 
  <tr> 
   <td align="center">CAST()</td> 
   <td align="center">数据类型转换，参数一个表达式，表达式通过as关键词分割两个参数，第一个参数是原始数据和目标数据类型</td> 
  </tr> 
  <tr> 
   <td align="center">COALESCE()</td> 
   <td align="center">返回第一个非空数值</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

1.  聚集函数

<table> 
 <thead> 
  <tr> 
   <th align="center">函数</th> 
   <th align="center">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">COUNT()</td> 
   <td align="center">总行数</td> 
  </tr> 
  <tr> 
   <td align="center">MAX()</td> 
   <td align="center">最大值</td> 
  </tr> 
  <tr> 
   <td align="center">MIN()</td> 
   <td align="center">最小值</td> 
  </tr> 
  <tr> 
   <td align="center">SUM()</td> 
   <td align="center">求和</td> 
  </tr> 
  <tr> 
   <td align="center">AVG()</td> 
   <td align="center">平均值</td> 
  </tr> 
 </tbody> 
</table>

AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行，MAX 和 MIN 函数也可以用于字符串类型数据的统计，如果是英文字母，则按照 A—Z 的顺序排列，越往后，数值越大。如果是汉字则按照全拼拼音进行排列。

1.  如何使用 HAVING 过滤分组，它与 WHERE 的区别是什么？
    
    当我们创建出很多分组的时候，有时候就需要对分组进行过滤。你可能首先会想到 WHERE 子句，实际上过滤分组我们使用的是 HAVING。HAVING 的作用和 WHERE 一样，都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组
2.  子查询中的any和all关键字的区别

这两个都是用于[子查询][Link 1]的，any 是任意一个，all 是所有。

select * from student where 班级='01' and age > all (select age from student where 班级='02');

相当于

select * from student where 班级='01' and age > (select max(age) from student where 班级='02');

select * from student where 班级='01' and age > any (select age from student where 班级='02');

相当于

select * from student where 班级='01' and age > (select min(age) from student where 班级='02');

1.  sql 99 标准中常用的多表连接的用法。

<table> 
 <thead> 
  <tr> 
   <th>连接名称</th> 
   <th>实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>交叉连接</td> 
   <td>SELECT * FROM player CROSS JOIN team</td> 
  </tr> 
  <tr> 
   <td>自然连接</td> 
   <td>SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team 等值连接</td> 
  </tr> 
  <tr> 
   <td>ON 连接</td> 
   <td>SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id</td> 
  </tr> 
  <tr> 
   <td>USING 连接</td> 
   <td>SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id)</td> 
  </tr> 
  <tr> 
   <td>自连接</td> 
   <td>SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = ‘布雷克 - 格里芬’ and a.height &lt; b.height</td> 
  </tr> 
 </tbody> 
</table>

在使用多表连接时的注意事项

*  控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。
 *  连接表时不要忘记where语句。WHERE 语句，这样可以过滤掉不必要的数据行返回。
 *  使用自连接而不是子查询。因为大部分 DBMS 中都对自连接处理进行了优化。

1.  创建视图的相关基础语法

//创建视图
    CREATE VIEW player_above_avg_height AS SELECT
    	player_id,
    	height
    FROM
    	player
    WHERE
    	height > (
    		SELECT
    			AVG(height)
    		FROM
    			player
    	)
    	
    	修改视图
    ALTER VIEW player_above_avg_height AS
    SELECT player_id, player_name, height
    FROM player
    WHERE height > (SELECT AVG(height) from player)
    // 删除视图
    DROP VIEW player_above_avg_height

1.  视图和临时表的区别？

视图是虚拟表，本事不存储数据，视图的作用是封装sql查询，提升sql的复用率。主要应用于以下几个方面。利用视图来完成比较复杂的多表连接。可以利用视图对数据进行格式化。使用视图来计算字段。

临时表是真实存在的数据表，不过它不用于长期存放数据，只为当前连接存在，关闭连接后，临时表就会自动释放。比如购物车

1.  视图的优点和缺点

视图的优点主要在于：

安全性： 因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图，不可以随意的更改和删除，可以保证数据的安全性。

简单清晰：视图是对 SQL 查询的封装，它可以将原本复杂的 SQL 查询简化，在编写好查询之后，我们就可以直接重用它而不必要知道基本的查询细节。

1.  存储过程实例

DELIMITER //
    CREATE PROCEDURE `add_num`(IN n INT)
    BEGIN
           DECLARE i INT;
           DECLARE sum INT;
           
           SET i = 1;
           SET sum = 0;
           WHILE i <= n DO
                  SET sum = sum + i;
                  SET i = i +1;
           END WHILE;
           SELECT sum;
    END //
    DELIMITER ;
    
    
    CREATE PROCEDURE `get_hero_scores`(
           OUT max_max_hp FLOAT,
           OUT min_max_mp FLOAT,
           OUT avg_max_attack FLOAT,  
           s VARCHAR(255)
           )
    BEGIN
           SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
    END

1.  存储过程的优点与缺点?

优点：一次编译多次使用，提升sql的执行效率，减少开发工作量，安全性强，减少网络传输量。

缺点：可移植性查，调试困难、版本管理困难、不适合高并发的场景。

1.  脏读,不可重复读,幻读之间的区别？

**脏读**：一个事务读取另外一个事务还没有提交的数据叫脏读。例如：事务T1修改了某个表中的一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后事务T1因为某种原因回滚（Rollback）了，那么事务T2读取的数据就是脏的（无效的）。

解决办法：把数据库的事务隔离级别调整到READ\_COMMITTED（读提交/不可重复读）

**幻读**：幻读是指当事务不独立执行时，插入或者删除另一个事务当前影响的数据而发生的一种类似幻觉的现象。**幻读的重点在于新增或者删除 (数据条数变化)**

例如：系统事务A将数据库中所有数据都删除的时候，但是事务B就在这个时候新插入了一条记录，当事务A删除结束后发现还有一条数据，就好像发生了幻觉一样。这就叫幻读。

解决办法：把数据库的事务隔离级别调整到SERIALIZABLE\_READ（序列化执行），或者数据库使用者自己进行加锁来保证。

**不可重复读**：不可重复读是指在同一个事务内，两次相同的查询返回了不同的结果。**不可重复读的重点是修改:同样的条件, 你读取过的数据, 再次读取出来发现值不一样了**

例如：事务T1会读取两次数据，在第一次读取某一条数据后，事务T2修改了该数据并提交了事务，T1此时再次读取该数据，两次读取便得到了不同的结果。

解决办法：把数据库的事务隔离级别调整到REPEATABLE\_READ（可重复读）

1.  游标的基础知识

在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。

DECLARE cursor_name CURSOR FOR select_statement //定义游标

游标的相关操作

CREATE PROCEDURE `calc_hp_max`()
    BEGIN
           -- 创建接收游标的变量
           DECLARE hp INT;  
     
           -- 创建总数变量 
           DECLARE hp_sum INT DEFAULT 0;
           -- 创建结束标志变量  
         DECLARE done INT DEFAULT false;
           -- 定义游标     
           DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
           -- 指定游标循环结束时的返回值  
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  
           
           OPEN cur_hero;
           read_loop:LOOP 
           FETCH cur_hero INTO hp;
           -- 判断游标的循环是否结束  
           IF done THEN  
                         LEAVE read_loop;
           END IF; 
                  
           SET hp_sum = hp_sum + hp;
           END LOOP;
           CLOSE cur_hero;
           SELECT hp_sum;
           DEALLOCATE PREPARE cur_hero;
    END

1.  游标的优势与不足？

优势：灵活性强，可以解决复杂的数据处理问题，可以对数据行进行逐行扫描处理。

不足：使用游标的过程中，会对数据行进行加锁，当业务并发量大的时候，会影响到业务的效率。通时游标是在内存中处理的，会消耗掉系统资源容易造成内存不足。

建议：通常情况下，如果游标有替代方案的时候，可以采用替代方案，如果实在绕不开还是会用到游标的。

1.  数据库调优的目标及手段?

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0pT24kq1-1592386213094)(https://s1.ax1x.com/2020/04/18/JeDYUe.png)\]

1.  数据表中的那些键？

NBA 的球员表（player）和球队表（team）。这里我可以把球员表定义为包含球员编号、姓名、身份证号、年龄和球队编号；球队表包含球队编号、主教练和球队所在地。

对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号）（球员编号，姓名）（身份证号，年龄）等。

候选键就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。

主键是我们自己选定，也就是从候选键中选择一个，比如（球员编号）。

外键就是球员表中的球队编号。

在 player 表中，主属性是（球员编号）（身份证号），其他的属性（姓名）（年龄）（球队编号）都是非主属性。

1.  数据库的范式都有哪些？1NF，2NF, 3NF，BCNF分别值得是啥？

目前关系型数据库一共有 6 种范式，按照范式级别，从低到高分别是：1NF（第一范式）、2NF（第二范式）、3NF（第三范式）、BCNF（巴斯 - 科德范式）、4NF（第四范式）和 5NF（第五范式，又叫做完美范式）。越高级的范式数据库的冗余度越低，高阶范式一定满足低阶范式的要求。

**1NF 指的是数据库表中的任何属性都是原子性的，不可再分**。

**2NF 指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系**。所谓完全依赖不同于部分依赖，也就是不能仅依赖候选键的一部分属性，而必须依赖全部属性。2NF 告诉我们一张表就是一个独立的对象，也就是说一张表只表达一个意思。

**3NF 在满足 2NF 的同时，需要保证表中的非主属性与候选键不存在传递依赖。**

**BCNF，也叫做巴斯 - 科德范式，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系**。

1.  什么情况下创建索引，什么时候不需要索引？索引的缺点有哪些? 索引失效的情况有哪些？

数据库中的索引，就好比一本书的目录，它可以帮我们快速进行特定值的定位与查找，从而加快数据查询的效率，但会使修改、插入、删除操作变慢，同时还会 占用较大的磁盘空间 。索引就是帮助数据库管理系统高效获取数据的一种数据结构。如果我们不使用索引，就必须从第 1 条记录开始扫描，直到把所有的数据表都扫描完，才能找到想要的数据。

**需要建立索引的情况：**

1．频繁作为查询条件的字段即where子句、group by子句中出现的字段，尤其在数据表大的情况下, 要创建索引  
2．查询中排序的字段即order by子句的字段，要创建索引  
3．统计聚合函数中的字段，要创建索引。比如count(字段) 、max(字段)  
4．字段的数值有唯一性的限制,要建立唯一索引.

**不需要建立索引的情况：**

1．如果需要取到表中所有记录，则没必要创建索引  
2．对非唯一有大量重复值的字段，没必要创建索引，如性别  
3．经常进行修改、删除等操作的字段，没必要创建索引  
4．记录比较少的表，没必要创建索引

其实**索引不是万能的，在有些情况下使用索引反而会让效率变低**。

**缺点：**

第一，创建索引和维护索引要耗费时间，这种时间随着数据 量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度

**索引失效的情况：**

*  如果索引进行了表达式计算、使用函数，则会失效
 *  在where子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没进行索引，那么索引会失效。
 *  索引列与NULL或者NOT NULL 进行判断时候也会失效。
 *  我们再使用联合索引的时候没有注意最左原则。

1.  mysql 索引的一些优化

> https://www.cnblogs.com/l199616j/p/11232392.html
> 
> **1、前导模糊查询不能使用索引，**
> 
> 如name like ‘%静’
> 
> **2、Union、in、or可以命中索引，建议使用in**
> 
> **3、负条件查询不能使用索引，可以优化为in查询，**
> 
> 其中负条件有！=、<>、not in、not exists、not like等
> 
> **4、联合索引最左前缀原则，又叫最左侧查询，**
> 
> 如果在（a，b，c）三个字段上建立联合索引，那么它能够加快a|（a，b）|（a，b，c）三组的查询速度。
> 
> **5、建立联合查询时，区分度最高的字段在最左边**
> 
> **6、如果建立了（a，b）联合索引，就不必再单独建立a索引。**
> 
> 同理，如果建立了（a，b，c）索引就不必再建立a，（a，b）索引
> 
> **7、存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置**
> 
> **8、范围列可以用到索引，但是范围列后面的列无法用到索引。**
> 
> 索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：<、<=、>、>=、between等。
> 
> **9、把计算放到业务层而不是数据库层。**
> 
> 在字段上计算不能命中索引，
> 
> **10、强制类型转换会全表扫描，**
> 
> 如果phone字段是varcher类型，则下面的SQL不能命中索引。Select \* fromuser where phone=13800001234
> 
> **11、更新十分频繁、数据区分度不高的字段上不宜建立索引。**
> 
> 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。
> 
> “性别”这种区分度不太大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
> 
> 一般区分度在80%以上就可以建立索引。区分度可以使用count（distinct（列名））/count（\*）来计算。
> 
> **12、利用覆盖索引来进行查询操作，避免回表。**
> 
> 被查询的列，数据能从索引中取得，而不是通过定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速度查询。
> 
> **13、建立索引的列不能为null，使用not null约束及默认值**
> 
> **14、利用延迟关联或者子查询优化超多分页场景，**
> 
> MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率非常低下，要么控制返回的总数，要么对超过特定阈值的页进行SQL改写。
> 
> **15、业务上唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。**
> 
> **16、超过三个表最好不要用join，**
> 
> 需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。
> 
> **17、如果明确知道查询结果只要一条，limit 1能够提高效率，比如验证登录的时候。**
> 
> **18、Select语句务必指明字段名称**
> 
> **19、如果排序字段没有用到索引，就尽量少排序**
> 
> **20、尽量用union all 代替 union。**  
> 　　Union需要将集合合并后在进行唯一性过滤操作，这会涉及到排序，大量的cpu运算，加大资源消耗及延迟，当然，使用union all的前提条件是两个结果集没有重复数据。
> 
> **21、使用合理的分页提高效率。**  
> 　　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。

1.  索引的种类有哪些？

从功能逻辑上说，索引主要有 4 种，分别是**普通索引、唯一索引、主键索引和全文索引。**

**普通索引**是基础的索引，没有任何约束，主要用于提高查询效率。

**唯一索引**就是在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。

**主键索引**在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里最多只有一个主键索引。

**全文索引**用的不多，MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。

**按照物理实现方式**，索引可以分为 2 种：**聚簇索引和非聚集索引**。我们也把非聚集索引称为二级索引或者辅助索引。这两种索引内部都是B+树，聚集索引的叶子节点存放着一整行的数据。

聚簇索引并不是一种单独的索引类型，而**是一种数据存储方式**。Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引

**聚簇索引的特点如下：**

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

*聚簇索引的优缺点*

优点：  
1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快  
2.聚簇索引对于主键的排序查找和范围查找速度非常快  
缺点：

1.插入速度严重依赖于插入顺序，按照主键的**顺序插入**是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个**自增的ID列为主键**  
　　2.**更新主键的代价很高**，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。  
　　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

**辅助索引（非聚簇索引）的特点**

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

**辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引，却只能有一个聚簇索引**

*聚簇索引与非聚簇索引的一些区别：*

1.  聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据行的主键。非聚集索引不会影响数据表的物理存储顺序。
2.  一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。
3.  使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

25 . B+树和B树的区别？

> 1.  有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数 +1。
> 2.  非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
> 3.  非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中，非叶子节点既保存索引，也保存数据记录。
> 4.  所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

B+树的优势在于

首先，B+ 树查询效率更稳定。因为 B+ 树每次只有访问到叶子节点才能找到对应的数据，而在 B 树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。

其次，B+ 树的查询效率更高，这是因为通常 B+ 树比 B 树更矮胖（阶数更大，深度更低），查询所需要的磁盘 I/O 也会更少。同样的磁盘页大小，B+ 树可以存储更多的节点关键字。

不仅是对单个关键字的查询上，在查询范围上，B+ 树的效率也比 B 树高。有时候需要查询某个范围内的数据，由于 B+ 树的叶子节点是一个有序链表，只需在叶子节点上遍历即可，不用像 B 树那样挨个中序遍历比较大小。

1.  Hash 索引与 B+ 树索引的区别

我们之前讲到过 B+ 树索引的结构，Hash 索引结构和 B+ 树的不同，因此在索引使用上也会有差别。

> 1.  Hash 索引不能进行范围查询，而 B+ 树可以。这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。
> 2.  Hash 索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而 B+ 树可以。对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
> 3.  Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+ 树索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。同理，我们也无法用 Hash 索引进行模糊查询，而 B+ 树使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 % 开头）的话就可以起到优化作用

1.  MYSQL中的存储结构是怎么 样的？

\*\*在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。\*\*一个表空间包括了一个或多个段，一个段包括了一个或多个区，一个区包括了多个页，而一个页中可以有多行记录。Innodb 中一个页默认大小是16KB，一个区的大小是64\*16Kb=1MB. 区在文件系统是一个连续分配的空间。

页（Page）如果按类型划分的话，常见的有数据页（保存 B+ 树节点）、系统页、Undo 页和事务数据页等。数据页是我们最常使用的页。

> 区（Extent）是比页大一级的存储结构，在 InnoDB 存储引擎中，一个区会分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64\*16KB=1MB。
> 
> 段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在 InnoDB 中是连续的 64 个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。
> 
> 表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。
> 
> 在 InnoDB 中存在两种表空间的类型：共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。

1.  数据库缓冲池的作用？

DBMS 会申请占用内存来作为数据缓冲池，这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间来提升 SQL 语句的查询性能。当我们对数据库中的记录进行修改的时候，如果数据页在内存中，就直接更新，如果没有在内存中的话，在不影响数据一致性的前提下，Innodb会将更新操作缓存在changebuffer,它可以根据一定得策略将改变数据持久化到磁盘上。查看缓冲池的大小

mysql > show variables like 'innodb_buffer_pool_size'

1.  数据页加载的三种方式

*  内存读取：如果该数据存在于内存中，基本上执行时间在 1ms 左右，效率还是很高的。
 *  随机读取 ：如果数据没有在内存中，就需要在磁盘上对该页进行查找，整体时间预估在 10ms 左右。
 *  顺序读取：顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I/O 操作了。  
    通过 last\_query\_cost 统计 SQL 语句的查询成本：SHOW STATUS LIKE ‘last\_query\_cost’;

1.  意向锁是什么？

意向锁（Intent Lock），简单来说就是给更大一级别的空间示意里面是否已经上过锁。如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁。

1.  乐观锁和悲观锁？

**乐观锁和悲观锁并不是锁，而是锁的设计思想。**

乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，在取数据的时候不用每次都对数据上锁，同时也就不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。

悲观锁（Pessimistic Locking）也是一种思想，对数据被其他事务的修改持保守态度，总是假设最坏的情况，也就是说每次拿数据的时候都是认为别人是会来修改数据的，所以每次都会在拿数据的时候把数据加锁，在innodb中是通过数据库自身的锁机制来实现，从而保证数据操作的排它性。行锁，表锁，读锁，写锁，synchronized,reentrantlock都是悲观苏。

*悲观锁和乐观锁的使用场景*

*  乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
 *  悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

1.  innodb中锁的分类

*  按照锁的粒度来划分

按照对象的粒度大小来对锁进行划分，分别为行锁、页锁和表锁。每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

*  从数据库管理的角度对锁进行划分

从数据库管理的角度对锁进行划分。共享锁和排它锁，是我们经常会接触到的两把锁。

共享锁也叫读锁或 S 锁， 共享锁锁定的资源可以被其他用户读取，但不能修改。在进行`SELECT`的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。`LOCK TABLE product_comment READ;` 释放锁：`UNLOCK TABLE;`

排它锁也叫独占锁、写锁或 X 锁。排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。`LOCK TABLE product_comment WRITE;`

当有多个事务对同一数据获得读锁的时候，可能会出现死锁的情况。

33.MVCC 是什么，解决了什么问题

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

通过 MVCC 我们可以解决以下几个问题：

*  读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。
 *  降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。
 *  解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

*系统如何通过多版本并发控制技术找到一条记录？*

InnoDB 中，MVCC 是通过 Undo Log + Read View 进行数据读取，Undo Log 保存了历史快照，而 Read View 规则帮我们判断当前版本的数据是否可见。

*  首先获取事务自己的版本号，也就是事务 ID；
 *  获取 Read View；
 *  查询得到的数据，然后与 Read View 中的事务版本号进行比较；
 *  如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
 *  最后返回符合规则的数据。

*什么是快照读，什么是当前读?*

快照读读取的是快照数据。不加锁的简单 的 SELECT 都属于快照读，比如这样：SELECT \* FROM player WHERE …

当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT，或者对数据进行增删改都会进行当前读，比如：SELECT \* FROM player LOCK IN SHARE MODE;

1.  mysql优化流程

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9fX3poZW4_size_16_color_FFFFFF_t_70]

*慢查询开启*

mysql > show variables like '%slow_query_log';
    mysql > set global slow_query_log='ON';
    mysql > show variables like '%long_query_time%'; //设置慢查询阈值
    mysql > set global long_query_time = 3;

SHOW PROFILE 相比 EXPLAIN 能看到更进一步的执行解析，包括 SQL 都做了什么、所花费的时间等。默认情况下，profiling 是关闭的，我们可以在会话级别开启这个功能。

mysql > show variables like 'profiling';
    mysql > set profiling = 'ON';
    mysql > show profiles; //当前会话都有哪些 profiles
    mysql > show profile; //上一个查询的开销 show profile for query 2

1.  innodb引擎的4大关键特性

https://www.cnblogs.com/zhs0/p/10528520.html

1.  redis 为什么那么快？

*  Redis 采用 ANSI C 语言编写。采用 C 语言进行编写的好处是底层代码执行效率高，依赖性低，因为使用 C 语言开发的库没有太多运行时（Runtime）依赖，而且系统的兼容性好，稳定性高。
 *  Redis 是基于内存的数据库，我们之前讲到过，这样可以避免磁盘 I/O，因此 Redis 也被称为缓存工具。
 *  数据结构结构简单，Redis 采用 Key-Value 方式进行存储，也就是使用 Hash 结构进行操作，数据的操作复杂度为 O(1)。
 *  采用单进程单线程模型，这样做的好处就是避免了上下文切换和不必要的线程之间引起的资源竞争。
 *  在技术上 Redis 还采用了多路 I/O 复用技术。这里的多路指的是多个 socket 网络连接，复用指的是复用同一个线程。采用多路 I/O 复用技术的好处是可以在同一个线程中处理多个 I/O 请求，尽量减少网络 I/O 的消耗，提升使用效率。

1.  redis的事务处理机制？

*  redis 不支持事务的回滚，即使事务发生了错误只要不是语法错误，整个事务依然能够执行下去，直到事务中所有的命令都执行完毕。
 *  redis是内存数据库，通常只进行内存计算和操作，无法保证持久性。不过 Redis 也提供了两种持久化的模式，分别是 RDB 和 AOF 模式。
 *  RDB（Redis DataBase）持久化可以把当前进程的数据生成快照保存到磁盘上，触发 RDB 持久化的方式分为手动触发和自动触发。因为持久化操作与命令操作不是同步进行的，所以无法保证事务的持久性。
 *  AOF（Append Only File）持久化采用日志的形式记录每个写操作，弥补了 RDB 在数据一致性上的不足，但是采用 AOF 模式，就意味着每条执行命令都需要写入文件中，会大大降低 Redis 的访问性能。启用 AOF 模式需要手动开启，有 3 种不同的配置方式，默认为 everysec，也就是每秒钟同步一次。其次还有 always 和 no 模式，分别代表只要有数据发生修改就会写入 AOF 文件，以及由操作系统决定什么时候记录到 AOF 文件中。

> 1.  MULTI：开启一个事务；
> 2.  EXEC：事务执行，将一次性执行事务内的所有命令；
> 3.  DISCARD：取消事务；
> 4.  WATCH：监视一个或多个键，如果事务执行前某个键发生了改动，那么事务也会被打断；
> 5.  UNWATCH：取消 WATCH 命令对所有键的监视。

Redis 实现事务是基于 COMMAND 队列，如果 Redis 没有开启事务，那么任何的 COMMAND 都会立即执行并返回结果。如果 Redis 开启了事务，COMMAND 命令会放到队列中，并且返回排队的状态 QUEUED，只有调用 EXEC，才会执行 COMMAND 队列中的命令。

1.  数据库运算的种类？

关系运算包括选择、投影、连接以及除法。

集合运算是二目运算，包括并、差、交、广义笛卡尔积四种运算。

1.  数据库故障的分类？

1、事务故障是由于程序执行错误而引起事务非预期的、异常终止的故障。它发生在单个事务的局部范围内，实际上就是程序的故障。

事务故障更多的是非预期的，不能由事务程序处理的情况，主要有：

①逻辑上的错误，如运算溢出、死循环、非法操作、地址越界等等；

②违反完整性限制的无效的输入数据；

③违反安全性限制的存取权限；

④资源限定，如为了解除死锁、实施可串化的调度策略等而ABORT一个事务；

⑤用户的控制台命令。

2、系统故障是指系统在运行过程中，由于某种原因，造成系统停止运行，以致事务在执行过程中以非正常的方式终止，致使内存中的信息丢失，而存储在外存上的数据未受影响。

3、介质故障是指外存储设备故障，主要有磁盘损坏，磁头碰撞盘面，突然的强磁场干扰，数据传输部件出错，磁盘控制器出错等。

数据库管理系统要做的工作通常有以下四个方面：①描述数据库；②管理数据库；③维护数据库；④ 数据通讯。

1.  mysql的几种日志文件？

*  错误日志

用来记录 MySQL 服务器运行过程中的错误信息,默认开启无法关闭.

默认情况下，错误日志是存储在数据库的数据文件目录中，名称为 hostname.err，其中 hostname 为服务器主机名。

*  查询日志

**查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录**，具体原因如下:

insert 查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错；  
update 时也会查询因为更新的时候很可能会更新某一块数据；  
delete 查询，只删除符合条件的数据；  
因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。

*  慢查询日志
    
    设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
 *  redo log (重做日志）一种缓存机制

为了最大程度的避免数据写入时因为 IO 瓶颈造成的性能问题,而引进的redo log。

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。innodb通过\*\*\*force log at commit\*\*\*机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

MySQL先将数据写入内存中，再批量的将内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

*  undo log（回滚日志）

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。undolog用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment

*  binlog

42.redo log 和binlog的区别？

*  二进制日志是在**存储引擎的上层**产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而redo log是innodb层产生的，只记录该存储引擎中表的修改。**并且二进制日志先于** **redo log 被记录**。
 *  二进制日志记录操作的方法是逻辑性的语句。即便它是基于行格式的记录方式，其本质也还是逻辑的SQL设置，如该行记录的每列的值是多少。而redo log是在物理格式上的日志，它记录的是数据库中每个页的修改。
 *  二进制日志只在每次事务提交的时候一次性写入缓存中的日志"文件"(对于非事务表的操作，则是每次执行语句成功后就直接写入)。而redo log在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作。
 *  因为二进制日志只在提交的时候一次性写入，所以二进制日志中的记录方式和提交顺序有关，且一次提交对应一次记录。而redo log中是记录的物理页的修改，redo log文件中同一个事务可能多次记录，最后一个提交的事务记录会覆盖所有未提交的事务记录。
 *  事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化。而二进制日志记录的是所有影响数据的操作，记录的内容较多。例如插入一行记录一次，删除该行又记录一次。

1.  什么时候才会刷日志到磁盘
    
    1.发出commit动作时。已经说明过，commit发出后是否刷日志由变量 innodb\_flush\_log\_at\_trx\_commit 控制。
    
    2.每秒刷一次。这个刷日志的频率由变量 innodb\_flush\_log\_at\_timeout 值决定，默认是1秒。要注意，这个刷日志频率和commit动作无关。
    
    3.当log buffer中已经使用的内存超过一半时。
    
    4.当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置。
2.  数据页刷盘的规则及checkpoint

在innodb中，数据刷盘的规则只有一个：checkpoint。但是触发checkpoint的情况却有几种。不管怎样，checkpoint触发后，会将buffer中脏数据页和脏日志页都刷到磁盘

innodb存储引擎中checkpoint分为两种：

*  sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。
 *  fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：
    
     *  master thread checkpoint：由master线程控制，**每秒或每10秒**刷入一定比例的脏页到磁盘。
     *  flush\_lru\_list checkpoint：从MySQL5.6开始可通过 innodb\_page\_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。
     *  async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘
     *  dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb\_max\_dirty\_pages\_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。

1.  LSN 简介

LSN称为日志的逻辑序列号(log sequence number)，在innodb存储引擎中，lsn占用8个字节。LSN的值会随着日志的写入而逐渐增大。

根据LSN，可以获取到几个有用的信息：

1.数据页的版本信息。

2.写入的日志总量，通过LSN开始号码和结束号码可以计算出写入的日志量。

3.可知道检查点的位置。

1.  innodb执行修改语句的日志记录流程

(1).首先修改内存中的数据页，并在数据页中记录LSN，暂且称之为data\_in\_buffer\_lsn；

(2).并且在修改数据页的同时(几乎是同时)向redo log in buffer中写入redo log，并记录下对应的LSN，暂且称之为redo\_log\_in\_buffer\_lsn；

(3).写完buffer中的日志后，当触发了日志刷盘的几种规则时，会向redo log file on disk刷入重做日志，并在该文件中记下对应的LSN，暂且称之为redo\_log\_on\_disk\_lsn；

(4).数据页不可能永远只停留在内存中，在某些情况下，会触发checkpoint来将内存中的脏页(数据脏页和日志脏页)刷到磁盘，所以会在本次checkpoint脏页刷盘结束时，在redo log中记录checkpoint的LSN位置，暂且称之为checkpoint\_lsn。

(5).要记录checkpoint所在位置很快，只需简单的设置一个标志即可，但是刷数据页并不一定很快，例如这一次checkpoint要刷入的数据页非常多。也就是说要刷入所有的数据页需要一定的时间来完成，中途刷入的每个数据页都会记下当前页所在的LSN，暂且称之为data\_page\_on\_disk\_lsn。

1.  delete/update操作的内部机制

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

*  delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
 *  update分为两种情况：update的列是否是主键列。
    
     *  如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
     *  如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

1.  char 和varchar的区别？

**相同点：**

1.  char(n),varchar(n)中的n都代表字符的个数。
2.  超过char，varchar最大长度n的限制后，字符串会被截断。

**不同点：**

1.  char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。
2.  能存储的最大空间限制不一样：char的存储上限为255（28-1）字节，varchar的存储上限是65535（216-1）字节。
3.  char在存储时会截断尾部的空格，而varchar不会。

根据char和varchar结构和数据保存上的不同点，我们比较一下两者的适用场景：

1.  根据要存储的字符串长度来看： 长度相近且不需要很大存储空间的字符串可以选用char，节省存储空间。
2.  从碎片的角度来看：用变长的字符串数据类型，需要时不时对碎片进行处理。
3.  即使使用varchar的数据类型也不要盲目设置过大的存储限制，比如你只需要存储80个字符，VARCHAR(100)和VARCHAR(500)虽然都存储了同样长度的字符，但是内存消耗是不一样的。
4.  Statement和PreparedStatement的区别?

**PreparedStatement 是从从 Statement 继承而来**，**CallableStatement是从 PreparedStatement 继承而来**

**Statement 对象用于执行不带参数的简单 SQL 语句；PreparedStatement 对象用于执行带或不带参数的预编译 SQL 语句；CallableStatement 对象用于执行对数据库中存储过程的调用。**

**Statement每次执行sql语句，数据库都要执行sql语句的编译，仅执行一次查询并返回结果的情形，效率高于PreparedStatement.但存在sql注入风险**

PreparedStatement是预编译执行的，**预编译的语句，会放在Cache中，下次执行相同的SQL语句时，则可以直接从Cache中取出来**。在执行可变参数的一条SQL和多次重复执行的语句，PreparedStatement要比Statement的效率高，因为DBMS预编译一条SQL当然会比多次编译一条SQL的效率高。安全性更好，有效防止SQL注入的问题。

[Link 1]: https://www.baidu.com/s?wd=%E5%AD%90%E6%9F%A5%E8%AF%A2&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9fX3poZW4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200730103003187.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9fX3poZW4=,size_16,color_FFFFFF,t_70