《面试必备知识》——数据库129问

1. mysql默认端口是多少？

• 3306

2. 为什么要使用数据库？

• 数据保存在内存中
  • 速度快
  • 但是不能永久保存
• 数据保存在文件
  • 永久保存
  • 速度慢
  • 查询数据不方便
• 数据保存在数据库
  • 永久保存
  • 速度慢
  • 使用SQL语句，查询速度快
  • 管理数据方便

3. SQL语句可以分为几类？

• DDL(Data Definition Language)数据定义语言
  • 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
• DML(Data Manipulation Language)数据操作语言
  • 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
• DQL(Data Query Language)数据查询语言
  • 用来查询数据库中表的记录(数据)。关键字：select, where 等
• DCL(Data Control Language)数据控制语言(了解)
  • 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

4. 事务是什么？

• 事务是指满足ACID特性的一组操作，可以通过commit提交一个事务，通过rollback来回滚一个事务

5. 数据库事务有哪些特性？

• 原子性（Atomicity）
  • 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
  • 回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
• 一致性（Consistency ）
  • 数据是满足完整性约束的
  • 数据在事务的前后，业务整体一致
    • 举例：转账前后，总金额一致
• 隔离性（Isolation）
  • 一个事务所做的修改在最终提交以前，对其它事务是不可见的。
• 持久性（Durability）
  • 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
  • 使用重做日志来保证持久性。

6. 非关系型数据库和关系型数据库的区别？

• 关系型数据库：

  • 指采用了关系模型来组织数据的数据库。 简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
  • 优点：
    1. 容易理解，二维表的结构非常贴近现实世界，二维表格，容易理解。
    2. 使用方便，通用的sql语句使得操作关系型数据库非常方便。
    3. 易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率。
  • 缺点：
    1. 海量数据的读写效率。对于网站的并发量高，往往达到每秒上万次的请求，对于传统关系型数据库来说，硬盘I/o是一个很大的挑战。
    2. 高扩展性和可用性。在基于web的结构中，数据库是最难以横向拓展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没有办法像web Server那样简单的通过添加更多的硬件和服务节点来拓展性能和负载能力。

• 非关系型数据库

  • 主要指那些非关系型的、分布式的，且一般不保证ACID的数据存储系统，主要代表MongoDB，Redis、CouchDB。

  • NoSQL提出了另一种理念，以键值来存储，且结构不稳定，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构，可以减少一些时间和空间的开销。使用这种方式，为了获取用户的不同信息，不需要像关系型数据库中，需要进行多表查询。仅仅需要根据key来取出对应的value值即可。

  • 分类：

    • 面向高性能并发读写的key-value数据库：

      主要特点是具有极高的并发读写性能，例如Redis、Tokyo Cabint等。

    • 面向海量数据访问的面向文档数据库：

      特点是，可以在海量的数据库快速的查询数据。例如MongoDB以及CouchDB.

    • 面向可拓展的分布式数据库：

      解决的主要问题是传统数据库的扩展性上的缺陷。

  • 缺点：

    • 但是由于Nosql约束少，所以也不能够像sql那样提供where字段属性的查询。因此适合存储较为简单的数据。

• 对比：

  • 存储上：关系型数据库以数据表的形式存储。非关系型采用key-value形式。

  • 事务：SQL中如果多张表需要同批次被更新，即如果其中一张表跟新失败的话，其他表也不会更新成功。这种场景可以通过事务来控制，可以在所有命令完成之后，再统一提交事务。在Nosql中没有事务这个概念，每一个数据集都是原子级别的。

  • 数据表 VS 数据集： 关系型是表格型的，存储在数据表的行和列中。彼此关联，容易提取。而非关系型是大块存储在一起。

  • 预定义结构 VS 动态结构：

    • 在sql中，必须定义好地段和表结构之后，才能够添加数据，例如定义表的主键、索引、外键等。表结构可以在定义之后更新，但是如果有比较大的结构变更，就会变的比较复杂。
    • 在Nosql数据库中，数据可以在任何时候任何地方添加。不需要预先定义。

  • 存储规范 VS 存储代码：

    • 关系型数据库为了规范性，把数据分配成为最小的逻辑表来存储避免重复，获得精简的空间利用。但是多个表之间的关系限制，多表管理就有点复杂。

      当然精简的存储可以节约宝贵的数据存储，但是现在随着社会的发展，磁盘上付出的代价是微不足知道的。

    • 非关系型是平面数据集合中，数据经常可以重复，单个数据库很少被分开，而是存储成为一个整体，这种整块读取数据效率更高。

  • 纵向拓展 VS 横向拓展：

    • 为了支持更多的并发量，SQL数据采用纵向扩展，提高处理能力，通过提高计算机性能来提高处理能力。
    • NoSql通过横向拓展，非关系型数据库天然是分布式的，所以可以通过集群来实现负载均衡。

7. 数据库的隔离级别有哪些？有什么区别？

• 未提交读（READ UNCOMMITTED）
  • 事务中的修改，即使没有提交，对其它事务也是可见的
• 提交读（READ COMMITTED）
  • 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
• 可重复读（REPEATABLE READ）
  • 保证在同一个事务中多次读取同样数据的结果是一样的。
• 可串行化（SERIALIZABLE）
  • 强制事务串行执行。需要加锁实现，而其它隔离级别通常不需要。

8. 如何加快数据库查询速度？

提高数据库查询速度的方法

10. 聚集索引和非聚集索引的区别？（主键索引和非主键索引）

聚集索引和非聚集索引 简析与对比

聚集索引与非聚集索引的总结

• 聚集索引（聚簇索引）（主键索引）：
  • 数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
  • 主索引的叶子节点 data 域记录着完整的数据记录。

• 非聚集（unclustered）索引（非主键索引）:
  • 该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
  • 辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找

11. 什么时候不该使用索引？

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
• 对于中到大型的表，索引就非常有效；
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术 。

12. 索引底层的数据结构是什么？B+树的结构是怎么样的？

• B+Tree
• BTree和B+Tree详解
• 首先B+Tree是从最早的平衡二叉树演化过来的。
• 每个节点中的键值是有序的
• 平衡二叉树每个节点的出度为2，m阶B+Tree的出度最大为m。m的大小取决于磁盘页的大小
• B+Tree的非叶子节点存储键值信息，即表中记录的主键，以及子节点的指针信息。
• 数据记录都存放在叶子节点中。
• 每个节点占用存储引擎的一个页。
• 所有叶子节点之间都有一个链指针。因此对B+Tree可以进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
• B+Tree相对于平衡二叉树而言，增大了节点的出度，减少了树的高度，缩减了查询时间。而且B+Tree在一个节点中存储了多个键值，相比于平衡二叉树减少了节点的个数。
• 数据库引擎在把磁盘数据读入到内存时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

13. b加树与b树的区别是什么？

• b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；

• b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；

• 对于范围查找和元素遍历来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低

• B+树的查询效率更加稳定

• 增删文件（节点）时，效率更高

14. 索引优化方法有哪些？

• 独立的列

  • 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

  SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

• 多列索引

  • 在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把actor_id 和 film_id 设置为多列索引。

  • SELECT film_id, actor_ id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;

• 索引列的顺序

  • 让选择性最强的索引列放在前面。
  • 索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。
  • 例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

• 前缀索引

  • 对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
  • 前缀长度的选取需要根据索引选择性来确定

• 覆盖索引

  • 简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键

  

  • 所有需要查询的字段信息都包含在了索引里面
  • 查询字段中包含了索引列
  • 优点：
    • 覆盖索引会直接在索引表中进行查询而不会访问原始数据。加快查询效率
    • 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
    • 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

15. hibernate和mybatis的异同有哪些？

• 相同点：
  • Hibernate 与 MyBatis 都可以是通过 SessionFactoryBuider 由 XML 配置文件生成 SessionFactory，然后由SessionFactory 生成 Session，最后由 Session 来开启执行事务和 SQL 语句。其中SessionFactoryBuider，SessionFactory， Session 的生命周期都是差不多的。
  • Hibernate 和 MyBatis 都支持 JDBC 和 JTA 事务处理。
• 不同：
  • MyBatis 可以进行更为细致的 SQL 优化，可以减少查询字段 。
  • MyBatis 容易掌握，而 Hibernate 门槛较高。
  • Hibernate 的 DAO 层开发比 MyBatis 简单， Mybatis 需要维护 SQL 和结果映射。
  • Hibernate 对对象的维护和缓存要比 MyBatis 好，对增删改查的对象的维护要方便。
  • Hibernate 数据库移植性很好， MyBatis 的数据库移植性不好，不同的数据库需要写不同 SQL。
  • Hibernate 有更好的二级缓存机制，可以使用第三方缓存。 MyBatis 本身提供的缓存机制不佳。
  • 由于无须SQL,当多表关联超过3个的时候，通过Hibernate的级联会造成太多性能的丢失
  • 对于性能要求不太苛刻的系统，比如管理系统、ERP 等推荐使用Hibernate;而对于性能要求高、响应快、灵活的系统则推荐使用MyBatis。

16. 数据库的四种连接方式是什么？

• 内连接
  • 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行
  • 
• 左连接
  • 左表的挨个信息去查询,查不到则将右边控制为null进行显示
  • 
• 右连接
  • 右表的挨个信息去查询,查不到则将左边控制为null进行显示
  • 
• 完全连接

  • 交叉联接返回左表中的所有行，左表中的每一行与右表中匹配的所有行组合。交叉联接也称作笛卡尔积。

  • 

  • 

  • mysql中实现全连接的方法

    • SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno UNION SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno

    • union 去重；union all 不去重

17. 数据库并发一致性问题有哪些？

• 丢失修改
  • T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
• 读脏数据
  • 
• 不可重复读
  • T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
  • 
• 幻影读
  • T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
  • 

18. 可串行化调度如何实现？

• 通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。
• 事务遵循两段锁协议是保证可串行化调度的充分条件。
• 两段锁协议
  • 加锁和解锁分为两个阶段进行。
  • 

19. 关系型数据库设计三范式是什么？

• 第一范式
  • 属性不可分
• 第二范式
  • 非主属性完全函数依赖于主属性
• 第三范式
  • 非主属性不传递函数依赖于主属性

20. 关系型数据库的设计不合范式会有什么异常？

• 冗余数据：例如 学生-2 出现了两次。
• 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
• 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
• 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

21. 关系型数据库设计范式是什么？

• 高级别范式依赖于低级别范式，1NF 是最低级别的范式。

• 第一范式 (1NF)

  • 属性不可分。

• 第二范式 (2NF)

  • 每个非主属性完全函数依赖于键码。
  • 可以通过分解来满足。

• 第三范式 (3NF)

  • 非主属性不传递函数依赖于键码 。
  • 任何非主属性不依赖于其它非主属性

• 概念：

  • 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
    • 例如：学号-->姓名。 （学号，课程名称） --> 分数
  • 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
    • 例如：（学号，课程名称） --> 分数
  • 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
    • 例如：（学号，课程名称） -- > 姓名
  • 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
    • 例如：学号-->系名，系名-->系主任
  • 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
    • 例如：该表中码为：（学号，课程名称）
    • 主属性：码属性组中的所有属性
    • 非主属性：除过码属性组的属性

• 为了性能可以进行妥协，不按照范式进行数据库设计

22. 数据库存储引擎有哪些？

• InnoDB
  • 是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
  • 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。
  • 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
  • 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
  • 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取
• MyISAM
  • 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。
  • 提供了大量的特性，包括压缩表、空间数据索引等
  • 不支持事务。
  • 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。
  • 可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。
  • 如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。
• 比较
  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
  • 外键：InnoDB 支持外键。
  • 备份：InnoDB 支持在线热备份。
  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  • 其它特性：MyISAM 支持压缩表和空间数据索引。
• Memory引擎
  • 用来存储只读数据，读取速度要比MyISAM更快

23. 对(a,b,c)加索引，查询ab、ac、bc有没有用？

mysql中关于关联索引的问题——对a,b,c三个字段建立联合索引，那么查询时使用其中的2个作为查询条件，是否还会走索引？

• 对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。
• 过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，该字段及其后面的索引字段都无法被使用索引(即使用的索引字段仅为前面的部分字段)

24. mysql索引相关操作的语句介绍一下？

• ALTER TABLE：ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
  • ALTER TABLE table_name ADD INDEX index_name (column_list)
  • ALTER TABLE table_name ADD UNIQUE (column_list)
  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)
  • 其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
• CREATE INDEX：CREATE INDEX可对表增加普通索引或UNIQUE索引。
  • CREATE INDEX index_name ON table_name (column_list)
  • CREATE UNIQUE INDEX index_name ON table_name (column_list)
  • table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
• 删除索引：
  • DROP INDEX index_name ON table_name
  • ALTER TABLE table_name DROP INDEX index_name
  • ALTER TABLE table_name DROP PRIMARY KEY
• 查看索引
  • mysql> show index from tblname;
  • mysql> show keys from tblname;
• mysql查询到底用了组合索引的哪些字段
  • explain+select可以查看是否使用索引
    • 
  • force index可以强制使用某个索引
    • 

25. where和having的异同？

• 用的地方不一样
  • where可以用于select、update、delete和insert into values(select * from table where ..)语句中。
  • having只能用于select语句中
• 执行的顺序不一样
  • where的搜索条件是在执行语句进行分组之前应用
  • having的搜索条件是在分组条件后执行的
  • where 早于 group by 早于 having
  • 即如果where和having一起用时，where会先执行，having后执行
• 子句有区别
  • having子句可以用集合函数（sum、count、avg、max和min），而where子句不可以
• 总结：
  1. WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
  2. GROUP BY 子句用来分组 WHERE 子句的输出。
  3. HAVING 子句用来从分组的结果中筛选行

26. sql select语句的执行顺序是什么？

1. from 子句组装来自不同数据源的数据；
2. where 子句基于指定的条件对记录行进行筛选；
3. group by 子句将数据划分为多个分组；
4. 使用聚集函数进行计算；
5. 使用 having 子句筛选分组；
6. 计算所有的表达式；
7. select 的字段；
8. 使用 order by 对结果集进行排序。

27. mysql有几种锁？怎么实现？

• mysql数据库的锁有多少种，怎么编写加锁的sql语句

• 表级锁定：

  • MySQL各存储引擎中最大颗粒度的锁定机制
  • 现逻辑非常简单，带来的系统负面影响最小，获取锁和释放锁的速度很快，可以很好的避免困扰我们的死锁问题，但是系统资源争用的概率最高，并发能力差。
  • 主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎使用
  • MyISAM的表级锁定完全是由MySQL提供的表级锁定实现：
    • 表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作；
    • 表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
  • MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁

• 行级锁定：

  • 锁定对象的颗粒度很小，发生锁定资源争用的概率也最小，能够给予程序尽可能大的并发能力。但是由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。

  • 此外，行级锁定也最容易发生死锁。

  • 使用行级锁定的主要是InnoDB存储引擎。

  • 行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的

    • InnoDB的行级锁定同样分为两种类型，共享锁和排他锁。

    • 在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

    • 

    • 意向锁是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

    • InnoDB行锁实现方式

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

    • 间隙锁（Next-Key锁）：

      • 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。
      • 对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
      • 目的：
        • 防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
        • 为了满足其恢复和复制的需要。

• 页级锁定：

  • 特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。
  • 另外，页级锁定和行级锁定一样，会发生死锁。
  • 使用页级锁定的主要是BerkeleyDB存储引擎。

• 总结：

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

28. mysql mvcc是什么？如何实现？

• mvcc是指多版本并发控制 ，是MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。 可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

• 版本号：

  • 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增
  • 事务版本号：事务开始时的系统版本号。

• 隐藏的列 ：

  • MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：
    • 创建版本号：指示创建一个数据行的快照时的系统版本号；
    • 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

29. Undo日志是什么？如何实现？

• MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

• 当对某个记录进行更新时，会将当前记录写入 undo log 中，并更新当前记录中 DB_ROLL_PTR 字段值，使其指向刚才的 undo log record，然后更新当前记录相关字段值，同时更新 DB_TRX_ID 字段，记录执行更新操作的事务 ID。

  

• 实现过程：

  • 查询：
    • 多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。 (可以通过回滚指针找到历史版本)（当然，只是活跃的事务，如果当前记录没有相关事务在操作，则会清理 undo log，就不能拿到历史版本数据了）
    • 事务所要读取的数据行快照的创建版本号必须小于该事务的事务版本号。
    • 事务读取的数据行的删除版本号必须为未指定或者大于当前事务的版本号
    • 满足上述要求的记录才能被事务查出来
  • 插入：
    • 将当前事务版本号作为数据行快照的创建版本号。
  • 更新：
    • 将当前事务版本号作为更新前的数据行快照的删除版本号，并将当前事务版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。
  • 删除：
    • 将当前事务版本号作为数据行快照的删除版本号。

• 回滚实现：

  • 利用undo 日志，当需要进行回滚的时候，使用回滚指针来反过来执行指令就能将数据库回滚。

30. mysql不同存储引擎的特点及它们各自的应用场景是什么？

• MySQL面试之存储引擎类问题

• MyISAM

  • MyISAM 引擎读取速度较快，占用资源相对较少，不支持事务，不支持外键约束，但支持全文索引；
  • 使用表级锁，读写互相阻塞，也就是说读数据的时候你就不能写数据，写数据的时候你就不能读数据；
  • MyISAM 引擎只能缓存索引，而不能缓存数据。
  • 适用场景：
    • 不需要事务支持的业务
    • 适用于读数据比较多的业务，不适用于读写频繁的业务
    • 并发相对较低、数据修改相对较少的业务
    • 硬件资源比较差的机器可以考虑使用 MyISAM 引擎

• InnoDB

  • 事务型数据库的首选引擎，支持事务安全表，支持行锁定和外键
  • 具有提交、回滚和崩溃恢复能力的事务安全存储引擎，能处理巨大数据量，性能及效率高，完全支持外键完整性约束;
  • 具有非常高效的缓存特性，能缓存索引也能缓存数据，对硬件要求比较高;
  • 使用场景：
    • 需要事务支持的业务、高并发的业务
    • 数据更新较为频繁的场景，比如 BBS、SNS、微博等
    • 数据一致性要求较高的业务，比如充值转账、银行卡转账

• Innodb是如何实现事务的

  

31. innodb事务支持的级别是什么？

• 四种都支持
• 默认是可重复读

32. 数据库join的底层实现是什么？

• 个人理解
  • mysql的join算法是Nested-Loop Join（嵌套循环连接）
  • 要连接的两张表可以分为驱动表和被驱动表
  • 在连接的时候会对驱动表进行全表扫描，然后根据join的列在被驱动表中进行匹配
• 因此在进行多表关联的时候，建议把小的表作为驱动表，然后大的表作为被驱动表，而且保证被驱动表的关联字段应该建立索引，加快匹配

33. 如果一条SQL语句执行的很慢，怎么优化？(Explain)

• 首先可以使用Explain + SQL语句来对SQL语句进行分析
• 在分析的结果里面，比较关键的字段有以下几个：
  • id
    • 是select查询的序列号，每个号码都代表了一趟独立的查询。一个SQL查询的趟数越少越好
    • id相同，从上到下
    • id不同，id值越大越先执行
  • type
    • 显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL
    • 具体的见mmap，这里记录关键几个
    • const：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
      • 这个是对于单表查询的情况而言
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
      • 这个是对于多表关联查询过程中，对被驱动表的唯一性索引进行扫描
    • ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
    • range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
    • index：出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
    • all：Full Table Scan，将遍历全表以找到匹配的行
  • key
    • 代表实际使用的索引
  • key_len
    • 代表实际使用索引的字节数
  • rows
    • 扫描的行数
  • Extra：Using filesort
    • 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
    • 只能使用内存或者磁盘进行排序
  • Extra：USING index
    • 表示相应的select操作中使用了覆盖索引(Covering Index)，不用进行回表操作，而且遍历二级索引的效率要高于聚簇索引
    • 如果同时出现using where，表明索引被用来执行索引键值的查找;
    • 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
    • 利用索引进行了排序或分组
  • Extra：USING where
    • 表明使用了where过滤
    • 在server层进行了条件判断完成过滤操作
  • Extra：using index condition
    • 使用了索引下推的功能
    • 部分索引条件无法充当边界条件，但是可以对查询得到的数据行进行过滤
    • 只适用于二级索引，目的是减少回表造成的性能损坏
  • Extra：using join buffer
    • 被驱动表无法通过索引优化查询效率，此时会分配一个连接缓冲块，进行基于块的嵌套循环算法来执行连接查询

34. 为什么用 B+ 树做索引而不用哈希表做索引?

• 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。

• 如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。

• 索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

• 哈希表中存储记录行的物理位置

• 哈希索引在进行等值查询的时候速度较快，但是无法进行范围查询，不支持使用索引排序，不支持模糊查询等

• 适用哈希表作为索引的时候，需要将整个哈希表加载进内存中，一次性占用的内存空间较大，但是B+树可以按照节点来进行加载，按序加载，不用一次性占用较大的内存

35. 为什么索引不使用红黑树？

• 在大规模数据存储的时候，红黑树由于树的深度过大，查询效率较低而且磁盘IO读写比较频繁，进而导致效率较低
• 通过B+树，可以有效地提高查询效率和磁盘读写效率，查询效率更加稳定

36. 为什么建议使用主键自增的索引？

• 但是，如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要对叶子节点进行移动或者页分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。

37. MySQL redo log 与 binlog 的区别？

参考

38. redo log是什么？作用和实现介绍一下？

• 重做日志
• 用来记录数据页上进行了什么修改
• 在 MySQL 中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就采用了日志（redo log）来提升更新效率
• 而日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。
• 在同一个事务中，每当数据库进行修改数据操作时，将修改结果更新到内存后，会在redo log添加一行记录记录“需要在哪个数据页上做什么修改”，并将该记录状态置为prepare，等到commit提交事务后，会将此次事务中在redo log添加的记录的状态都置为commit状态，之后在适当的时候（如系统空闲时）将修改落盘时，会将redo log中状态为commit的记录的修改都写入磁盘
• redolog采用循环写的方式记录，当写到结尾时，会回到开头循环写日志
• redolog的大小是固定的，在mysql中可以通过修改配置参数innodb_log_files_in_group和innodb_log_file_size配置日志文件数量和每个日志文件大小
• 参数
  • innodb_log_file_size：指定每个redo日志大小，默认值48MB
  • innodb_log_files_in_group：指定日志文件组中redo日志文件数量，默认为2
  • innodb_log_group_home_dir：指定日志文件组所在路劲，默认值./，指mysql的数据目录datadir
  • innodb_mirrored_log_groups：指定日志镜像文件组的数量，默认为1，此功能属于未实现的功能，在5.6版本中废弃，在5.7版本中删除了。
• redo log也是需要写入磁盘的，但是它是顺序IO，比起直接将内存的脏页写到磁盘的随机IO要快很多

• write pos表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，即write pos->checkpoint之间的部分是redo log空着的部分，用于记录新的记录，checkpoint->write pos之间是redo log待落盘的数据修改记录。当writepos追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位置记录新的日志。

• 有了redo log，当数据库发生宕机重启后，可通过redo log将未落盘的数据恢复，即保证已经提交的事务记录不会丢失。

• 特点

  • redo log的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。
  • redo log是innodb引擎层实现的，并不是所有引擎都有。
  • 重做日志都是以512字节进行存储的，称之为重做日志块，与磁盘扇区大小一致，这意味着重做日志的写入可以保证原子性，不需要doublewrite技术

• innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

39. binlog是什么？作用和实现介绍一下？

• 特点

  • binlog记录了数据库表结构和表数据变更
  • binlog是server层实现的，意味着所有引擎都可以使用binlog日志
  • binlog通过追加的方式写入的，可通过配置参数max_binlog_size设置每个binlog文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。
  • binlog有两种记录模式，statement格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。
  • 备注: 每个事务 binlog 的末尾，会记录一个 XID event，标志着事务是否提交成功，也就是说，recovery 过程中，binlog 最后一个 XID event 之后的内容都应该被 放弃。
  • sync_binlog： 设置为1，表示每次事务的binlog都直接持久化到磁盘（注意是这里指的是binlog日志本身落盘），保证mysql重启后binlog记录是完整的。
    • show variables like 'sync_binlog';

• 对于事务引擎，每次事务提交的时候，都会写binlog

• 对于非事务引擎，每条SQL语句都会写到binlog

• 作用

  • 数据复制
    • 在主服务器和从服务器间进行数据复制，保持数据的一致性
  • 数据恢复
    • 数据库的数据出问题以后，可以通过binlog来对数据进行恢复

• 区别：

  • redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  • redo log 是物理日志，记录的是在某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑。
  • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
  • redo log事务开始的时候，就开始记录每次的变更信息，而binlog是在事务提交的时候才记录。
  • MySQL需要保证redo log和binlog的数据是一致的，如果不一致，主从服务器间的数据就会不一致了

• 录入格式：

  • statement
    • 用来记录执行的语句，该模式的话对于函数无法保证数据一致性，如now函数
  • row
    • 用来记录发生改变的行以及对应的改变，但是存在效率问题，可能一条SQL能设置完的数据，需要一条一条遍历整个表才能做完
  • mixed
    • 综合了上述两者，没有函数时，使用statement，有函数时使用row，但是对于系统变量仍然会出现主从不一致

• 有了对这两个日志的概念性理解后，再来看执行器和 InnoDB 引擎在执行这个 update 语句时的内部流程。

  1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存（InnoDB Buffer Pool）中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

40. 日志相关问题

40.1 怎么进行数据恢复？

• binlog 会记录所有的逻辑操作，并且是采用追加写的形式。当需要恢复到指定的某一秒时，比如今天下午二点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：
  • 首先，找到最近的一次全量备份，从这个备份恢复到临时库
  • 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。
• 这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

40.2 数据库崩溃恢复，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

• 实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在由 redo log 更新过去数据最终落盘的情况。
  1. 数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程与 redo log 毫无关系。
  2. 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。
     • checkpoint->write pos之间是redo log待落盘的数据修改记录，因此崩溃恢复就是将这部分的数据页读取到内存中，然后使用redo日志更新内存内容，最终将脏页落盘。

40.3 redo log 和 binlog 是怎么关联起来的?

• redo log 和 binlog 有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
  • 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
  • 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

41. 为什么建议单表的数据量不宜过大？

• 应该与IO次数有关
• 单表数据量过大，索引也会增多，需要的磁盘IO次数就多了

42. SQL注入问题介绍一下？

• 问题描述
  • 未将代码与数据进行严格隔离，导致在读取用户数据时，错误地将数据作为代码的一部分执行
• 解决方法
  • 过滤用户输入参数的特殊字符
  • 禁止通过字符串拼接的SQL语句，使用参数绑定传入的SQL参数，参数化绑定SQL变量

43. MySQL事务相关语句介绍一下？

• 操作
  • 开启事务： start transaction;
  • 回滚：rollback;
  • 提交：commit;

44. JDBC是什么？如何使用？

• 定义了操作关系型数据库的规则

• JDBC是一组接口，每个数据库厂商根据接口去完成自己的实现

• 使用步骤

  • 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
  • 注册驱动
  • 获取数据库连接对象 Connection
  • 定义sql
    • 注意：sql的参数使用？作为占位符。 如：select * from user where username = ? and password = ?
  • 获取执行sql语句的对象 PreparedStatement Connection.prepareStatement(String sql)
  • 给？赋值：
    • 方法： setXxx(参数1,参数2)
      • 参数1：？的位置编号 从1 开始
      • 参数2：？的值
  • 执行sql，接受返回结果，不需要传递sql语句
  • 处理结果
  • 释放资源

• 使用PreparedStatement能有效地防止SQL注入，而且效率更高

45. 数据库连接池是什么？

• 将数据库的连接交给连接池来管理
  • 可以更好地管理连接资源
  • 当请求到来时，可以直接从池中获取连接使用，效率更高
  • 当不需要使用连接时，归还连接给连接池，不用关闭销毁，重复创建
• 分类
  • C3P0
  • Druid
• 流程：
  • 导入jar包
  • 定义配置文件
  • 加载配置文件
  • 创建连接池对象
  • 获取连接

46. SQL选用索引的执行成本如何分析？

• 在有多个索引的情况下， 在查询数据前，MySQL 会选择成本最小原则来选择使用对应的索引

• 成本：

  • IO 成本：MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理。一页的成本就是 1， IO 的成本主要和页的大小有关
  • CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2

• 在MySQL5.6以后，对于无where条件的select count(*) from table ，会进行相应的优化

• MySQL会选择成本最小的辅助索引查询方式来计数

• 但是，如果select count(*)中包含了where语句的话，最终还是会使用全表扫描

47. MySQL的全局参数如何查询和设置？

• 查询：
  • show variables like 'sql_mode';
• 设置：
  • set sql_mode='ONLY_FULL_GROUP_BY';
  • set global slow_query_log=1;
    • global永久生效

48. 慢查询日志是什么？有什么作用？如何使用？

• 用来记录运行时间超过long_query_time的SQL

• 开启方法：

  • 默认不开启
  • SHOW VARIABLES LIKE '%slow_query_log%';
  • set global slow_query_log=1;

• 修改超时时间

  • SHOW VARIABLES LIKE 'long_query_time%';
  • set long_query_time=1
  • 可以通过命令修改，也可以在my.cnf参数里面修改
  • 当时间大于该值时才会被记录下来

• 配置文件：

  my.cnf 【mysqld】下配置：   slow_query_log=1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log long_query_time=3 log_output=FILE

• 日志分析工具mysqldumpslow

• 用来配合慢查询日志进行分析

• 对于找到的慢查询SQL，使用EXPLAIN进行分析

49. 如何对慢查询语句进行优化？

• 优化思路：

  • 查询条件是否命中索引？
  • 是否加载了不需要的数据？
  • 数据量太大了？

• 优化方法：

  • explain分析语句执行计划，查看索引的使用情况，如果索引的使用不符合预期，则进行语句修改或者添加索引
  • 查看是否加载了多余的行或者列数据，能使用覆盖索引尽可能使用覆盖索引
  • 如果语句已经足够优化了，可以考虑是否表中的数据太大了，如果是可以考虑进行横向或者纵向分表

50. 常用引擎有哪些？

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

51. MyISAM与InnoDB的区别？

52. MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

53. MyISAM的读取性能为什么更高？

• MyISAM是非聚集索引，索引跟数据是分开来的，在进行查询操作的时候，可以先加载索引文件，然后再根据从索引文件中得到的文件偏移来定位数据的位置
  • 由于它使用的是非聚集索引，因此索引大小要比innoDB的索引文件要小，IO次数要少
• InnoDB是事务型引擎，在默认的可重复读的情况下，它会通过MVCC来维护隔离级别
  • 在MVCC机制下，读取数据会判断数据行的版本号是否在当前事务版本号之前，如果是则可以使用，否则需要在undo log中寻找符合条件的数据
  • MyISAM不支持事务，因此不用进行此操作，所以能够更快一些

54. 索引失效的情况介绍一下？

• 多列索引中，应该遵守最佳左前缀法则，即过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
• 不要对索引列进行任何操作，否则会导致索引失效
• 存储引擎不能使用索引中范围条件右边的列
• mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
• is not null 也无法使用索引,但是is null是可以使用索引的
• like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
• 字符串不加单引号索引失效
  • 隐式转换的影响
  • 当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。

55. 建立索引的原则有哪些？

• 尽量选择针对当前query过滤性更好的索引
• 当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
• 尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
• 更新频繁字段不适合创建索引
• 尽量的扩展索引，不要新建索引
• 外键数据列一定建立索引
• 索引尽量非空

56. 创建和删除索引的语句？

• 在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (  id INT auto_increment PRIMARY KEY,  first_name VARCHAR (16),  last_name VARCHAR (16),  id_card VARCHAR (18),  information text,  KEY name (first_name, last_name),  FULLTEXT KEY (information),  UNIQUE KEY (id_card) );

• 使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

• 使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

• 删除索引

alter table user_index drop INDEX name;

57. 百万级数据如何删除？

• 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
• 然后删除其中无用数据（此过程需要不到两分钟）
• 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
• 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

58. 前缀索引的作用？

• 目的：利用前缀索引来减少索引的大小
• 语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。
• 利用select count(*)/count(distinct left(password,prefixLen));得到前缀的区分度，进而确定前缀的合适长度

59. MySQL中InnoDB引擎的行锁是怎么实现的？

• InnoDB是基于索引来完成行锁
• 例: select * from tab_with_index where id = 1 for update;
• for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将使用表锁，并发将无从谈起

60. InnoDB引擎的锁有哪几种？

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

61. 数据库的死锁解决方法有哪些？

• 解决方法
  • 尽可能一次锁定全部所需资源
  • 提升锁的粒度，升级为表级锁

62. SQL 约束有哪几种？

• 非空约束
• 唯一约束
• 主键约束
• 外键约束
• CHECK: 用于控制字段的值范围

63. varchar与char的区别有哪些？

• char的特点

  • char表示定长字符串，长度是固定的；
  • 如果插入数据的长度小于char的固定长度时，则用空格填充；
  • 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
  • 对于char来说，最多能存放的字符个数为255，和编码无关

• varchar的特点

  • varchar表示可变长字符串，长度是可变的；
  • 插入的数据是多长，就按照多长来存储；
  • varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
  • 对于varchar来说，最多能存放的字符个数为65532

• 总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

• 对于固定长度的字符串应该使用char

• 密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

64. varchar(50)中50的含义是什么？

• 最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

65. int(20)中20的含义是什么？

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

66. 执行一条语句的过程介绍一下？

• 应用服务器与数据库服务器建立一个连接
• 数据库进程拿到请求sql
• 根据查询语句先查看缓存，如果命中，直接返回，否则进行语句解析
• 解析SQL语句，判断是否合法
• 如果合法，由优化器将SQL语句转化为执行计划进行执行
• 读取数据到内存并进行逻辑处理
• 通过步骤一的连接，发送结果到客户端
• 关掉连接，释放资源

67. 大表数据查询，怎么优化？

• 在查询语句中必须加上范围限定条件
• 优化SQL语句和索引
• 加缓存，redis
• 主从复制，读写分离
• 垂直拆分，将大的数据表分成多个数据表
  • 简单来说是数据表列的拆分
  • 优点：行数据变小，能够减少读取的IO数
  • 缺点：出现冗余主键，需要管理冗余列，而且需要多表关联查询
• 水平拆分，将数据量大的数据表拆分
  • 表的行数超过200万行性能会下降
  • 最好结合分库使用，将数据分到别的机器
  • 分片方案：
    • 客户端代理：分片逻辑在应用端
    • 中间件代理：在应用与数据间加了一层代理层，分片逻辑维护在中间件。如Mycat

68. MySQL组件以及SQL语句执行介绍一下？

• https://www.jianshu.com/p/5b60de98332b

• 整体分为server层和存储引擎

• server层用来存放通用组件，存储引擎则是负责具体的数据操作，提供读写接口

• 连接器

  • 管理着连接的创建，维持和管理以及权限验证
  • 建立的连接会使用一开始查询到的权限

• 查询缓存

  • 查询语句会先来查询缓存
  • 查询语句为key，结果为value
  • 8.0已经删除，因为缓存很容易失效

• 分析器

  • 分析查询语句。
  • 分析语法是否正确，识别表名，字段名等

• 优化器

  • 确定SQL的执行计划
  • 包括索引的选择、多表关联还会涉及基准表的选择(内连接有优化器选择哪个为基准表，一般选数据较少的为基准表，因为要全表搜索)

• 执行器

  • 先校验是否有足够的权限
    • SQL语句要操作的表可能不止表面上的，比如说触发器，得在执行器阶段才能确定
  • 然后会打开表，根据表的存储引擎去使用存储引擎的接口
  • 如果匹配到目标数据行就进行返回

69. 分库分表后会有什么问题？

• 分布式事务
  • 分库分表后的事务变成了分布式事务
• 跨库join
  • 跨节点连接，比较合适的解决方法是分两次查询
  • 第一次查询得到关联数据的id，第二次根据得到的id请求关联数据
• 跨节点的聚合函数
  • 分别在各个节点上得到结果后在应用程序段进行合并
• 数据迁移，容量规划，扩容
• ID问题
  • UUID：非常长，占用空间大，没有自增性导致索引性能不足
  • 分布式自增ID算法Snowflake：全局自增ID

70. 字段为什么要求定义为not null？

• null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

71. 数据库结构优化方法有哪些？

• 将字段很多的表分解成多个表
  • 对于使用频率较低的字段，可以将这些字段分离出来形成新的表
• 增加中间表
  • 对于经常使用联合查询的表，可以建立一个中间表将需要联合查询的数据插入到中间表，以后的查询可以直接查询中间表，避免联合查询
• 增加冗余字段
  • 适当的添加冗余字段可以有效地提高查询速度
  • 但是当冗余字段的值修改了，需要注意修改其它的表来保证数据一致性

72. MySQL数据库cpu飙升到500%的话如何进行处理？

• 首先调用top命令，查看是否是mysql造成的
• 如果是，则调用show processlist看看里面跑的 session 情况，是否有消耗资源的 sql 在运行
• 有的话，对sql进行分析，判断执行计划是否准确
• 一般情况下，要kill掉这些线程，等进行相应调整后，再重新运行这些SQL
• 也有可能是由于大量的session进入而导致CPU飙升，此时就需要分析连接数为何飙升，再做出相应的调整，比如说限制连接数

73. 数据库备份手段有哪些？

• 100G内的库：

  • 使用mysqldump
  • 该方法通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原
  • 该方法比较灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)

• 100G以上的库

  • 可以考虑使用xtranbackup，备份速度明显要比 mysqldump 要快
  • 可以选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期

• 恢复时间

  • 物理备份恢复快，逻辑备份恢复慢

• 底层原理

  • mysqldump 属于逻辑备份，通过使用RR的隔离级别，能够读取到事务开始时对应的数据行的数据
  • Xtrabackup属于物理备份，直接拷贝表空间文件

74. 主从复制过程介绍一下？

• 主节点将变动记录到二进制日志(bin log)。记录过程称为二进制日志事件
• 从节点将主节点的bin log拷贝到它的中继日志中(relay log)
• 从节点重做中继日志中的事件，将改变应用到自己的数据库中

• 三个线程

  • 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
  • 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
  • 从：sql执行线程——执行relay log中的语句

• 第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

• 第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

• 第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

75. 主从复制的方式有哪几种？

• 异步复制
  • 默认使用
  • 数据完整性依赖于主库的binlog不丢失。只要不丢失，哪怕主库宕机，仍然可以通过binlog将丢失的数据通过手工同步到从库上
  • 可以通过mysqlbinlog工具手工访问主库binlog，抽取缺失的日志并同步到从库上去
  • 在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，完不完整，所以这样可能会造成数据的不一致。
    • 主机突然宕机，binlog日志没有同步到从机，那么就会出现数据不一致的现象
• 增强半同步复制
  • 使用异步复制，主库和从库间存在一定的延迟。如果主库的binlog出现了丢失的情况，那么从库就会丢失这个事务，造成主从不一致
  • 该方法当主库写数据到BINLOG后，就开始等待从库的应答ACK，直到至少一个从库写入Relay Log后，并将数据落盘，然后返回给主库消息，通知主库可以执行Commit操作，然后主库开始提交到事务引擎层，应用此时可以看到数据发生了变化
  • 当然，如果从库宕机或者通信超时，MySQL会自动调整为异步模式，事务正常提交并返回结果给客户端
  • 因此，该方式当主从库之间的网络情况越好，从库就越实时
  • 当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，所以这样就保证了一个事务至少有两份日志，一份保存在主库的Binlog，另一份保存在其中一个从库的Relay-log中，从而保证了数据的安全性和一致性。

• 全同步复制
  • 指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

76. 主从复制延时问题的原因和解决方法有哪些？

• https://www.cnblogs.com/mscm/p/13325057.html

• 主从复制延时，会导致数据库对外的读写数据不一致的问题——新增/修改数据查不到等现象

• 如何查询主从库间的延时

  • 从库上执行 SHOW SLAVE STATUS
  • 对应的 Seconds_Behind_Master 值即为主从库间的数据复制延时

• 产生原因

  • 一般情况下，主从复制主要设计3个线程：
  • 主库的bin log线程，负责将修改数据的语句记录到 bin log中
  • 从库的IO线程，负责将主库的bin log 数据同步到从库的relay log 中
  • 从库的SQL线程，负责执行relay log中的SQL语句，从而实现从库与主库的数据一致性

• 在上述3个线程中，主库的bin log线程，采用顺序写的方式来记录修改操作，因此效率比较高；从库的IO线程将bin log数据同步到relay log，效率也比较高；但是从库的SQL线程，需要执行SQL语句，SQL语句的IO操作是随机的，成本很高

• 与主库相比，主库的写操作可以并发执行，但是从库在执行relay log中的语句时，使用的是单线程，因此当主库并发较高的时候，主从复制就会出现延时问题

• 具体场景及解决方法：

• 主库在短时间内发生了大量的写请求操作，从库单线程回放速率跟不上主库

  • MySQL5.7以下，通过进行分库分表的操作，分散从库的写压力
  • MySQL5.7及以上，可以在从库中使用并行复制
    • 并行复制的大致原理，是根据relay log中记录所操作的数据库来进行划分的，将不同数据库的回放操作交给不同的线程并发处理
    • 只能进行库级别的并行，因为同一个库的操作有可能相关联

• 主库执行大事务

  • 常见大事务
    • 使用了大量速度很慢的导入数据语句
    • 对一个大表进行全表数据更新或者删除操作
  • 主库的大事务，在从库回放的时候会耗费较长的时间，从而产生复制延时
  • 现象
    • 我们从SHOW SLAVE STATUS的结果进行分析，会发现 Exec_Master_Log_Pos 字段一直未变，且second_behinds_master持续增加，而 Slave_SQL_Running_State 字段的值为”Reading event from the relay log”；同时，分析主库binlog，看主库当前执行的事务，会发现有一些大事务，这样基本可以判定是执行大事务的原因导致的主从复制延时
  • 解决思路：
  • 拆分主库的大事务为若干个小事务，这样子能够及时提交事务，减少复制延时

• 主库对大表执行DDL语句

  • DDL本身造成的延时难以避免,建议考虑
    • 避免业务高峰，尽量安排在业务低峰期执行
    • set sql_log_bin=0后，分别在主从库上手动执行DDL

• 主从库的硬件配置不一

  • 同一配置，或者从库配置高于主库

• 表缺乏主键或合适索引

  • row模式下的bin log会把每条数据的更改记录下来
  • 当表缺乏合适的索引时，从库回放记录可能每次都需要进行全表扫描

• 从库自身压力过大

  • 读取业务繁重，导致从库回放速度跟不上主库
  • 解决方法：
    • 可以增加更多的从库，减缓读取压力

• 总结：

  • 首先通过SHOW SLAVE STATUS与SHOW PROCESSLIST查看现在从库的情况
  • 若Exec_Master_Log_Pos不变（从库回放日志的位置不变），考虑大事务、DDL、无主键，检查主库对应的binlog及position即可；
  • 若Exec_Master_Log_Pos变化，延时逐步增加，考虑从库机器负载，如IO、CPU等，并考虑主库写操作与从库自身压力是否过大。

77. 关系型数据库和非关系型数据库区别有哪些？

• https://mp.weixin.qq.com/s/XD6wVlPt5sVoE0Dmdfjp9w

• 关系型数据库

  • 典型数据结构是表，由二维表及表之间的关系（比如主键）所组成的一个数据组织
  • 好处：
    • 容易维护：都使用表结构，格式一致
    • 使用方便：使用SQL语言进行操作，易于理解，可以进行复杂查询
    • 事务支持：一般支持事务，数据操作可回滚
  • 不足：
    • 读写性能较差，尤其是针对海量数据读写场景
      • 高并发读写需求，对于传统的关系型数据库来说，硬盘I/O是一个很大的瓶颈
    • 表结构固定，欠缺灵活性

• 非关系型数据库

  • 是一种数据结构化存储方法的集合（类似于“文档”或者“键值对”）
  • 好处：
    • 格式灵活：存储数据的格式可以是KV键值对、文档形式、图片形式等，使用灵活，应用场景广泛，关系型数据库均以表格形式存储
    • 速度快：使用非关系型数据库的目的之一是数据缓存，所以一般以大容量的内存作为主要载体，也可以将数据持久化落实到硬盘中存储
  • 不足
    • 不支持SQL操作
    • 不同的非关系型数据库的使用方法各不相同，没有统一标准，学习和使用成本较高
    • 数据结构相对来说比较复杂
  • 分类：
    • 文档型：MongoDb
      • 可以存储图片等文件，可以作为NGINX等中间件的缓存数据来源
    • KV型：Redis
      • 数据缓存，内存型高速访问，主要用来处理热点数据

78. InnoDB关键特性有哪些？

• 参考：https://mp.weixin.qq.com/s/Acln2tsOFXq5T-5nQ1a9YQ
• https://mp.weixin.qq.com/s/PtOe4nPpRgK2_NXjw9CzVA

78.1 插入缓冲

• 优化索引插入
• 由于聚集索引一般是顺序插入，因此聚集索引无需插入缓冲
• 插入缓冲一般针对非聚集索引且非唯一索引
• 非唯一：
  • 插入缓冲的目的是避免磁盘的随机读取
  • 唯一索引在插入前还需要先判断插入记录是否唯一，在判断的时候已经通过随机读取将磁盘页读取到内存了，因此就没有必要再使用插入缓冲了
• 非聚集
  • 非聚集索引的插入会先判断非聚集索引页是否在缓冲池中，若在，则直接插入
  • 否则，先放到插入缓冲池中，然后再以一定的频率和情况进行insert buffer和辅助索引叶子节点的merge（合并）操作，这时能将多个插入合并到一个操作当中（因为都在一个索引页中），这样就大大提高了对于非聚集索引的插入性能
  • 时机：
  • 第一种情况 辅助索引被读取到缓冲池时，此时可以把多个对此索引页的操作记录合并到该页；多次的操作记录通过一次就合并到了原有的辅助页，因此性能会大幅度提升；
  • 第二种情况，insert buffer bitMap页用来追踪每个辅助索引页的可用空间，当辅助页索引空间小于1/32页时，会强制进行一个合并操作，也就是强制读取辅助索引页然后进行合并
  • 第三种情况 innodb 后台线程 Master Thread每隔一段时间会进行一次合并insert buffer的操作，不同之处在于每次进行合并操作的页的数量不同；

78.2 两次写

• 写的安全保障

• 将脏页列表缓冲的数据复制到double buffer中

• 然后分为两次，每次1M的大小刷新到磁盘共享表空间***享表空间页是连续的，顺序写入速度很快

• 全部写到共享表空间之后将double buffer中的数据离散写入到对应的数据页

• 目的：

• 当发生数据库宕机，可能innodb正在写入某个数据页到表中，而这个数据页只写了一部分，这种情况我们称之为"部分写失效",很容易出现数据丢失的问题

• 在通过redo log进行恢复的时候，由于此时数据页已经产生损坏了，因此对数据页进行重做的意义不大

• 需要确保一个数据页的副本，然后可以通过数据页的副本还原这个数据页，再进行重做

78.3 自适应哈希索引

• 等值查询速度很快
• innodb存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引；（是给页建立索引不是为某行数据，数据所在的页）
• 要求
  • 同一个模式访问该页多次
  • 同一个模式访问了100次
  • 页通过该模式访问了N次，N = 页中记录*1/16
• 只能进行等值查询

78.4 异步IO

• 用户可以在发出一个IO请求后立即发出另一个IO请求，当所有的IO请求发送完毕后，等待IO操作完成，这就是AIO（Asynchronous IO）
• AIO的优势是可以进行IO Merge操作，也就是将多个IO合并为一个IO,这样可以提高IOPS的性能。

78.5 刷新邻接页

• 当刷新一个脏页时，innodb存储引擎会检测该页所在的区的所有页，如果是脏页，那么一起进行刷新。利用AIO可以将多个IO合并成一个IO操作，该机制在系统机械磁盘上有着显著优势。
• 机械硬盘提升较大

78.6 预读

• 线性预读
  • 根据当前区（磁盘区）中顺序读取的页的次数来判断是否将下一个区预读到缓冲区中
  • 临界次数可以通过innodb_read_ahead_threshold来设置
• 随机预读
  • 当同一个区中的部分页已经存在在缓冲区中，InnoDB会将该区剩余的页读取到缓冲区中
  • 5.5后已经废弃，修改参数可以启用

79. 数据库如何进行分库分表操作？

• 通过中间件mycat来完成数据库的读写分离，数据分库分表

• 通过修改schema.xml文件来定义主库和从库，以及配置分片情况

• 通过修改rule.xml文件来定义分片规则

• 通过修改server.xml文件来修改用户登录信息，以及通过mycat生成全局唯一序列的方法

• 分库主要是将同一个库下的不同的表给分割到不同机器的库上

• 分出去的表不能在不同的机器上进行跨库连接，因此选择无需进行跨库连接的表给划分出去

• 分库后，表需要通过mycat来进行创建

• 分表主要是将同一个表中的数据分到不同机器的不同库上统一名称的表中

• 分表的时候，应该注意将关联的表也进行分表，通过ER表配置，将关联表一起进行切分

• 一般小的表可以做为全局表分到所有的库中

• 分表逻辑

  • 按照range来划分，容易产生热点问题
  • 按照字段hash进行划分，较为常用

• 全局序列

• 在一个数据库中单独建一个表来保存，需要创建对应的数据库表以及函数

• 时间戳，但是会比较长

• 业务逻辑自己生成，可以通过redis的单线程原子性的incr来生成

80. InnoDB事务的原理是什么？

• redo log重做日志用来保证事务的持久性
• undo log回滚日志保证事务的原子性
• undo log+redo log保证事务的一致性
• 锁（共享、排他）用来保证事务的隔离性

80.1 持久性如何实现？

• 通过重做日志来保证

• 重做日志用来记录数据页上进行了什么修改

• 数据库在进行数据操作的时候，会先将修改结果更新到缓存中，然后将该数据页上的修改记录到重做日志上（循环写），然后等待一个合适的时间，再将数据库修改后的数据落实到磁盘上

• 日志和磁盘配合的过程，也称为WAL，先写日志再写磁盘

• 由于采用追加写的方式，因此是顺序IO，速度相比于将修改数据落到磁盘上的随机IO的速度要快很多

• 通过控制innodb_flush_log_at_trx_commit 参数来控制重做日志写入磁盘的时机：

  • 1参数默认值，表示事务提交时必须调用一次fsync操作。
  • 0表示事务提交时，重做日志缓存并不立即写入重做日志文件，而是随着Master Thread的间隔进行fsync操作。
  • 2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作

• 一般设置为1，这样子才能保证已提交的事务的修改不会丢失

80.2 原子性实现如何实现？

• 通过回滚日志实现

• 数据库在对数据进行修改前，会将数据备份到回滚日志中，通过一个链指针将该数据行的历史快照串联起来

• 如果出现需要回滚的情况，就通过链指针找到对应的历史版本，然后进行数据恢复

• 回滚日志的删除

  • 对于insert操作，这些操作仅对当前事务本身可见，因此可以在事务提交后就删除
  • 对于delete和update操作，这些操作产生的undo log有可能在MVCC中使用，因此不能在事务提交时删除，可以等到purge线程进行最后的删除操作

80.3 一致性实现如何实现？

• 结合原子性、持久性和隔离性就可以保证事务前后，数据在整体业务逻辑上的一致性

80.4 事务的隔离性如何实现？

• 隔离性的实现依赖于锁、MVCC
• 锁分类
  • 表锁和行锁
  • 写锁和读锁
  • 意向锁
    • 意向锁的主要用途是为了表达某个事务正在锁定一行或者将要锁定一行数据。e.g:事务A要对一行记录r进行上X锁，那么InnoDB会先申请表的IX锁，再锁定记录r的X锁。在事务A完成之前，事务B想要来个全表操作，此时直接在表级别的IX就告诉事务B需要等待而不需要在表上判断每一行是否有锁。意向排它锁存在的价值在于节约InnoDB对于锁的定位和处理性能。另外注意了，除了全表扫描以外意向锁都不会阻塞。
    • 只有全表扫描的情况下，用处才比较大
• InnoDB的锁算法
  • Record Lock：单个行记录上的锁
  • Gap Lock：间隙锁，锁定一个范围，而非记录本身
  • Next-Key Lock：结合Gap Lock和Record Lock，锁定一个范围，并且锁定记录本身。主要解决的问题是REPEATABLE READ隔离级别下的幻读
• Next-Key Lock前置条件为事务隔离级别为可重复读且查询的索引走的非唯一索引、非主键索引
  • 主键索引和唯一索引的情况下，会降为Record Lock

81. bin log 和 redo log如何保持一致？

• bin log 一般用于主从复制，从而确保主库和从库的数据一致性，而redo log 一般用于数据库的灾害恢复，确保数据不丢失

• 只有在保证bin log 和 redo log的一致性，才能确保整体数据的一致性

• InnoDB通过事务的二阶段提交协议，确保事务要么同时存在于存储引擎和bin log中，要么两个都不存在，从而确保主从库间数据的一致性

  • 事务的提交过程分为了两个阶段，prepare和commit阶段

• 当数据库进行崩溃恢复的时候，redo log中处于prepare状态的事务会去查询该事务是否也存在于bin log中，如果存在，那么存储引擎内部提交该事务（时间点3出现故障）

• 如果不存在，那么回滚该事务（时间点2出现故障）

• 时间点1和时间点4出现故障不会影响数据一致性

82. MySQL数据库如何进行崩溃恢复？

• 崩溃恢复的时候，会结合bin log 和 redo log，来进行数据恢复
• 两个日志文件中的记录都有xid（事务ID），如果redo log中处于prepared状态的xid，在bin log中也存在的话，存储引擎进行事务提交，否则，进行回滚操作

83. 大事务的坏处？怎么排查？

• 事务执行时间长，会长时间占用数据库连接，容易导致大量阻塞出现，如果大事务执行失败，回滚比较耗时

• 排查方法

  • 通过 information_schema.Innodb_trx 表进行查询

  • select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;

• 解决方法

  • 优化sql，将大事务拆成多个小事务，或者缩短事务执行时间

84. 你有遇到过数据库宕机重启，事务丢失的情况么?

• 默认参数配置 innodb_flush_log_at_trx_commit=1 和 sync_binlog=0
  • 当系统宕机时，容易导致bin log的数据丢失，进而导致数据库的数据丢失
  • binlog的刷盘不控制，由操作系统控制。操作系统的宕机的可能性比较小
• innodb_flush_log_at_trx_commit=1 和 sync_binlog=1
  • 一致性最好，但是性能比较差，一般用于金钱相关的业务逻辑，不能出现一致性错误
  • 性能方面，可以通过异步化或者缓存来解决
• innodb_flush_log_at_trx_commit=2 和 sync_binlog=0
  • 并发性最好，每次事务提交时,只是把redolog写到OS cache，隔一秒，MySQL主动将OS cache中的数据批量fsync
  • 系统宕机，容易丢数据，实际环境中，系统宕机的概率比应用程序宕机的概率低

85. 两阶段锁是什么？

• 加锁和解锁分为两个阶段，而且加锁和解锁阶段不相交
• 在一个事务中，在进行增删改操作前，会进入加锁阶段，然后在加锁阶段进行加锁操作
• 在事务提交后，进入解锁阶段，进行统一解锁

86. 查询语句加锁方法？

• for update、lock in share mode
  • 这时候需要加锁来保证当前读
• 普通的读操作为快照读，不需要加锁，通过mvcc来保证隔离级别，能够避免幻读

87. MVCC中提交读和可重复读的区别是什么？

• 提交读在每次进行读操作的时候，都会根据当前的事务，生成一个readview结构，因此一旦之前事务已经提交了，那么该事务的数据就可以读到了

• 可重复读只会在事务开始后的第一次读取操作中生成一个readview结构，因此即便后续的readview记录中的事务提交了，该事务的数据也无法读取

• 从上述过程来看，快照读应该可以通过readview结构来解决幻读问题，当前读能够通过readview和next key lock来解决幻读问题

88. 更新语句加锁操作解析

• https://mp.weixin.qq.com/s/Cr49vVPLltHYFSIZxZef3w

• 以删除语句为例

  • delete from T where id = 10；

• 没有任何已知前提下

  • 加的写锁

• id主键+提交读

  • 将对应主键的记录加上写锁

• id唯一索引+提交读

  • 需要加两次锁，一次是对应id列上唯一索引上的记录，另外一个是对应的主键索引上相对应的记录
  • 简单来说，由于将非聚簇索引作为过滤条件之一，因此，需要锁定非聚簇索引上的记录和对应聚簇索引上的记录

• id非唯一索引+提交读

  • 与前述的区别在于，前面的方法由于有唯一索引的存在，因此只会锁一条记录
  • 但是此处的id非唯一，因此会锁上相关的非聚簇索引上的记录，以及对应的聚簇索引上的记录

• id无索引+提交读

  • 从理论上来讲，由于id没有加索引，因此只能将聚簇索引全表锁住来进行查询
  • 但是MySQL在实际操作中进行了优化，没有按照二阶段锁进行操作，它先将聚簇索引全表锁住，然后发现不满足过滤条件的数据行，会调用 unlock_row 方法将对应的数据行解锁，避免全表加锁

• id主键+可重复读

  • 与第一条配置一致

• id唯一索引+可重复读

  • 与二一致

• id不唯一+可重复读

  • 首先，对于非聚簇索引，会找到第一条符合条件的数据记录（等值条件），然后将数据记录锁定，并将数据记录的间隙加上间隙锁
  • 记下来，通过非聚簇索引定位到聚簇索引，在聚簇索引中会给满足相同条件的记录加上写锁，然后返回
  • 之后，继续往下查找符合条件的记录，然后重复上述过程
  • 与主键和唯一索引的不同点：
  • 因为主键和唯一索引均能保证唯一性，新的相同取值的记录是无法插入的，而且返回结果能够保证为1条
    • 这是对于等值查询的情况而言，因为主键和唯一键能够保证等值查询只有一条命中，而非唯一索引则不能保证
    • 对于范围查询，主键和唯一键索引也需要加间隙锁来保证

• id无索引+可重复读

  • 从理论上来讲，由于无索引，因此需要对聚簇索引进行全表加锁，并且将记录的间隙也全部加锁。在这种情况下，表除了快照读不用加锁外（通过MVCC实现），其它修改数据表结构和数据的操作全部都需要加锁，因此并发度相当低（相当于表级锁）
  • MySQL在这里进行了与4相似的优化，对于不满足条件的记录，MySQL会提前释放锁，间隙锁也是一样
    • semi-consistent read（优化机制）：当处于提交读或者在可重复读情况下，设置了 innodb_locks_unsafe_for_binlog 参数，会启动提前释放锁的功能
  • 总结：
  • 在可重复读的隔离级别下，进行全表扫描的当前读，会锁上表上的所有记录，为所有gap添加上间隙锁，虽然可以用 innodb_locks_unsafe_for_binlog 优化，但是可能会带来其它问题，不建议使用

• 可串行化级别

  • 所有操作都加锁，因此读写冲突严重，并发性能急剧下降

89. 如何生成分布式唯一ID？

• 利用Redis单线程的特性，通过incr来获取全局自增的唯一ID
  • 集群多主机的情况下，不同的主机可以通过设定不同的自增步长来避免相互冲突
  • 开启多个key，每个key的自增步长不一致即可，能够避免冲突
  • 通过主从复制，读写分离的方式提高redis的性能
• 雪花算法
  • 生成64位大小的整数

• 1位，不用。二进制中最高位为1的都是负数，但是我们生成的id一般都使用正数，所以这个最高位固定是0

• 41位，用来记录时间戳（毫秒）。

• • 41位可以表示2^41-1个数字，
  • 如果只用来表示正整数（计算机中正数包含0），可以表示的数值范围是：0 至 241−1241−1，减1是因为可表示的数值范围是从0开始算的，而不是1。
  • 也就是说41位可以表示2^41}1个毫秒的值，转化成单位年则是(2^41-1) / (1000 * 60 * 60 * 24 * 365) = 69年

• 10位，用来记录工作机器id。

• • 可以部署在2^10 = 1024$个节点，包括5位datacenterId和5位workerId
  • 5位（bit）可以表示的最大正整数是2^5-1 = 31，即可以用0、1、2、3、....31这32个数字，来表示不同的datecenterId或workerId

• 12位，序列号，用来记录同毫秒内产生的不同id。

• • 12位（bit）可以表示的最大正整数是2^12-1 = 4095，即可以用0、1、2、3、....4094这4095个数字，来表示同一机器同一时间截（毫秒)内产生的4095个ID序号

• 由于在Java中64bit的整数是long类型，所以在Java中SnowFlake算法生成的id就是long来存储的。

• 好处：

  • id随时间呈递增趋势
  • 生成的ID唯一
  • 不依赖与其它系统

• 不足：

  • 当机器的时间回拨的话会出现重复ID
  • 工作机器的ID配置需要注意

• 算法实现参考 畅购商城的IdWorker

• 多数据源生成方法

  • 参考TDDL的实现
  • 每个数据源的起点不一，但是递增步长一致，每次取值的时候都是一次性取一个范围值
  • 取值的时候可以通过乐观锁或者以下语句来完成取值和递增的过程
  • 负载均衡算法可以使用一致性hash，在大部分情况下获取到的都是自增的ID，但是如果某一个数据库挂了，就无法保证自增了
  • 通过多数据源的方式，可以保证唯一ID获取的高可用，但是无法保证全局自增性

直接更新 ID 并将更新后的值记录到 last_insert_id 中 update sequence_table set id = last_insert_id( id + 1000 ) where name = 'mySequence'  通过 last_insert_id 读取更新后的结果 SELECT LAST_INSERT_ID();  这样子避免了先查询，后更新所有可能导致的并发问题

90. 唯一约束和唯一索引的异同？

• 在MySQL中，定义唯一约束会创建唯一索引（通过索引的方式能更快地查找是否存在重复）
• 因此对于唯一约束的字段进行条件过滤的话，能够有效提高查询效率

91. 如何将大批量数据导入数据库？

• 一条SQL插入多条数据
  • 将多条分散的插入语句合并为一条，能够合并日志量（binlog和redolog），降低日志刷盘的数据量和频率，提高效率
  • 同时能减少SQL解析的次数，减少网络传输

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)   VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

• 在事务中进行插入处理
  • 避免每一个插入语句都开启一个事务
• 在进行插入操作的时候，还需要维护索引数据
  • 因此，可以选择先将所有的索引删除，待数据插入完毕后再重新建立索引
  • 或者，数据有序插入

92. MySQL默认字符集是什么？

• latin1

93. 乐观锁和悲观锁的异同？

• 悲观锁

  • 在对数据进行操作前，先将数据加锁
  • 开启事务后，增删改操作都会对相应的数据行加锁，查询语句如果加上for update的话，也会进行加锁操作
    • 加锁是基于索引的，如果表没有索引的话，会使用表锁
    • 查询语句在InnoDB引擎下，默认会使用MVCC来实现并发控制，此时不会加锁

• 乐观锁

  • 在对数据进行操作前，默认不会有其它线程修改数据，因此不会加锁，而是在最终修改数据的时候，判断数据是否有被修改过
  • 可以通过给记录添加额外的时间戳或者版本号来判断在事务的过程中，是否有别的事务修改过数据

94. 分页查询方法？如何优化？

• 经典方法

select * from table limit offset,cnt;

• 对于大表来说，如果offset很大的话，就需要先将offset之前的数据都先装进内存中，然后再从offset后获取cnt条数据

• 需要进行全表扫描

• 优化方案

  • 通过索引过滤无效数据
    • 索引扫描比全表扫描要快很多
    • 比如说可以记录上一页数据的最大ID，在查询下一页数据的时候，以上一页的最大ID为过滤条件之一，可以排除大量无效数据，减少查询时间
  • 利用覆盖索引来优化limit查询
    • 因为主键索引是有序的，可以先用limit定位出起始的起始索引的大小
    • 然后再使用ID作范围查询的条件得到最终的结果

  select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

95. 缓存和数据库双写一致性的方法有哪些？

• 可以采用延时双删策略

  • 在更新数据库的数据前，先删除缓存的数据
  • 然后再写数据库
  • 数据库的记录更新完毕后，等待一段时间，再删一次缓存的记录
  • 等待的时间可以设置为项目读数据业务逻辑的消耗时间加上几百ms即可
    • 目的是为了确保读请求结束，写请求可以删除由于读请求造成的脏数据

• 上述流程中，删除缓存的操作需要耦合到数据库的更新代码中，而且缓存删除失败的话（服务调用失败），缺乏重试机制

• 可以利用canal来进行改善

  • 数据库的记录发生变动的时候，binlog记录会发生变化
  • 此时canal会监听到日志的变动，然后可以将变动消息发送给消息队列中
  • 另起一段负责删除缓存的代码，监听消息队列中的消息，如果删除失败，则将消息发送回消息队列，等待一段时间，重试操作

• canal的好处

  • 缓存更新代码与业务代码解耦，而且可以做到准实时

• canal内部原理

  • 在主从复制过程中，从节点会将主节点的binlog日志同步到自己的relaylog中
  • 此处，canal模拟MySQL salve的交互协议，伪装自己是MySQL slave，向master发送dump协议
  • master收到dump请求后，会将自己的binlog推送给canal
  • canal解析binlog对象

• 畅购商城 第四天

• 官网：https://github.com/alibaba/canal

96. 存储引擎与数据库的关系是什么？

• 存储引擎就是数据库将数据存储到文件中的一种技术（数据存储技术）
• 不同的存储引擎会使用不同的存储机制、索引技巧、锁定水平，进而提供不同的功能和能力

97. 唯一索引和普通索引的区别是什么？

• 读
  • 唯一索引在读到目标行后可以直接返回
    • 因为不存在相同的索引
  • 普通索引在读取到目标行后，还要在继续往下判断是否有相同的索引
    • B+树的情况下，普通索引只需要在子节点的链表往后移动一位就可以判断了，因此两者的性能差距不大
• 写
  • 普通索引可以使用插入缓冲，组合几个修改操作，一起对数据页进行修改
  • 唯一索引需要判断是否有重复项，因此需要先随机访问到数据页进行判断，无法使用插入缓冲，因此性能相对较差

98. 自增ID和UUID的区别？

• https://zhuanlan.zhihu.com/p/98818611

• 使用UUID的话，由于它的随机性，因此主键不具备自增性

• 所以，在进行数据插入或者删除的时候，有可能会需要进行页分裂和页合并

• 页分裂需要创建新页，插在现有的两页之间（叶子节点的链表），会造成页的错位，逻辑顺序满足，但是物理存储上是混乱的，而且有可能落到不同的区

  • 可以通过OPTIMIZE整理，但是很浪费时间

• 页分裂和页合并都需要加锁，性能开销大

99. 自增ID用完会怎么样？

• 设置了主键的话会报错（推荐设置为bigint）
• 没有设置主键，数据库会自动使用行号生成主键（前提是也没有唯一索引，否则使用唯一索引），此时新数据会覆盖旧数据

100. 分库分表用来解决什么问题？

• 分库是为了解决当前数据库访问压力过大的问题(并发过大)
• 分表则是为了解决单表数据量过大导致查询效率降低的问题

101. 怎么关闭长事务？

• 首先查询数据库中正在执行的事务
  • 关键表 information_schema.innodb_trx ;
  • 得到事务对应的线程ID trx_mysql_thread_id
  • 然后使用mysql命令杀掉线程 kill 线程id

102. 事务数组是什么？高水位和低水位的含义？

• 事务数组是指当前正在运行的事务
• 高水位是指事务数组的最大事务版本号
• 低水位是指事务数组的最小事务版本号

103. 索引下推是什么？如何实现？作用？

• https://blog.csdn.net/sinat_29774479/article/details/103470244

• https://www.jianshu.com/p/d0d3de6832b9

• 5.6开始支持

• 现在有一个多列索引，查询条件需要对索引的第一个字段进行模糊匹配，第二个字段进行等值判断

• 此时多列索引只能使用第一个字段

• 在没有索引下推时，需要根据非聚集索引的主键值先进行回表，将找到的数据行返回给server层，再由server层判断等值条件是否满足

• 如果有索引下推的话，当通过非聚集索引定位主键值后，(回表前，也就是去找主键的数据之前)还会根据等值判断的条件来对数据行进行筛选，最后再回表找到对应的数据并返回

• 通过索引下推，将server的过滤操作下沉到存储引擎，减少了需要回表的数据行，提升查询效率

104. innodb索引加锁

• 索引对应的是页
• 在对记录加锁的时候，其实是对索引进行加锁
• 如果为每一个记录创建一个锁对象，比较耗费资源
• 因此为每一个页创建一个锁对象，对象内部有一个位图，位图的每一位对应了一条记录
• 记录加锁就是将相应的位置位

105. 单库单表如何迁移到分库分表？

• 停机迁移

  • 0点到6点进行停机运维
  • 启动多个临时程序，负责将旧库中的数据读取出来，然后通过数据库中间件保存到新库里面

• 双写迁移

  • 不停机，新的增删改操作，既对旧库进行操作，也对新库进行操作，也就是双写
  • 后台依然启动多个临时程序，负责读取旧库的数据并将其写入到新库中，不过在这个过程中，对于新库没有的数据，可以直接写，而如果新库有的数据，那么需要比对两个数据的时间先后，必须确保使用新数据来覆盖旧数据

• 停机迁移

• 双写迁移

106. 动态扩容缩容的分库分表方案

• 一开始上来就分为32个库，每个库32个表，一共1024个表

• 一开始业务不大，32个库都在一个数据库中，后续可以根据业务需要，呈倍数的增加机器，然后对单机的数据库进行迁移，最多能够扩容到1024个机器，对于一般业务而言也就足够了(每个库正常写入并发1000，对于1024个机器，能够写入102.4万数据，配合消息队列削减流量，基本上足够使用了)

• 总结

  • 设定好几台数据库服务器，每台服务器上几个库，每个库多少个表，推荐是 32 库 * 32 表，对于大部分公司来说，可能几年都够了。
  • 路由的规则，orderId 模 32 = 库，orderId / 32 模 32 = 表
  • 扩容的时候，申请增加更多的数据库服务器，装好 MySQL，呈倍数扩容，4 台服务器，扩到 8 台服务器，再到 16 台服务器。
  • 由 DBA 负责将原先数据库服务器的库，迁移到新的数据库服务器上去，库迁移是有一些便捷的工具的。
  • 我们这边就是修改一下配置，调整迁移的库所在数据库服务器的地址。
  • 重新发布系统，上线，原先的路由规则变都不用变，直接可以基于 n 倍的数据库服务器的资源，继续进行线上系统的提供服务。

107. SQL语句中，时间类型的区别？

108. 两个事务执行同一个增删改语句会发生什么？

• 首先，数据行会被锁定，因此只有一个事务能先执行
• 另外一个事务需要等待，如果此时数据行已经不存在了，那么会报错，当然，insert如果插入相同id的数据，也会报错

109. undo日志何时释放？

• 为了支持MVCC的功能，innodb引擎需要保存当前数据行的历史记录到undo日志里面，然后通过回滚指针将这些日志连接起来

• 为了避免内存占用过多，需要在合适的时候对这些日志进行回收操作

• 执行这个操作的就是purge线程

• 时机判断原理

• 对于每一个提交的事务，会生成一个事务no的值，用来表示事务提交的顺序

• 每一组undo日志(一个事务中对应的修改操作)在进行提交的时候会记录事务no的值

• 当事务提交后，会将该事务的undo日志添加到history链表上

• 然后readview结构中还包含了一个事务no的属性，这个属性存储了在生成readview结构时，当前系统的最大事务no+1

• 在innodb里面还将当前系统的readview按照创建时间连成一个链表

• 在进行purge操作的时候，先找到最早的readview结构，以这个结构记录的事务no为阈值，小于这个阈值的所有undo日志都可以进行删除操作

• 对于插入操作，在事务提交后就可以释放掉

• 因为是根据readview结构进行释放操作，因此如果有长事务使用可重复读的隔离级别的话，会一直复用最开始创建的readview结构，导致版本链越来越长，影响系统性能

110. 意向锁的作用？

• 意向读、写锁都是表级锁，这些锁的提出仅仅是为了当需要添加表级的读锁或者写锁的时候，可以快速判断表中的记录是否有被上锁，避免采用遍历的方式查看表中有没有上锁的记录
• 举例
  • 对数据行加读锁或者写锁的时候，会添加表级的意向读锁或者意向写锁，后续想要给表添加写锁的时候，判断这个表存在意向读锁或者意向写锁，就不能进行加锁操作
  • 同理，给表加读锁也是一样，如果表上存在意向写锁的话，那么就不能进行加锁操作

111. 插入操作如何加锁？

• 假设一个事务在执行加锁操作，另外一个事务想要查询插入后的数据行

  • 对于聚簇索引
  • 后一个事务需要先判断数据行对应的事务版本号是否是活跃版本号，如果是的话，说明前一个事务还没有提交，这时候，先在前一个事务给对应的数据行生成一个锁结构，is_waiting属性为false，表示第一个事务已经占有了该数据行的写锁；后一个数据也给对应的数据行生成一个锁结构，is_waiting属性为true，表明当前事务等待其它事务释放该数据行的锁
  • 对于非聚簇索引
  • 通过二级索引页面的属性可以判断修改当前数据页的最大事务id。如果该值小于当前最小活跃事务版本号，那么就可以直接使用，否则，先回表到聚簇索引，然后按照前述进行操作

• 上述操作相当于加了一个隐式锁。当前执行的事务自身没有主动加锁，而是由于后一个事务的作用而加上了锁

• 在使用自增ID列的时候，需要对整个表加锁

112. 事务锁结构介绍一下？

• 为了节省空间，满足以下条件的记录锁会放到同一个锁结构中

  • 同一个事务中进行加锁操作
  • 加锁的记录在同一个页面中
  • 加锁的类型相同
  • 等待状态相同

• 在锁结构中通过bit来表示同一个数据页下不同的记录行

• 同一页下不同的记录行有着各自的heap_no，通过bit可以与之对应上

• 记录了当前锁所在的事务、索引信息、锁锁定的页面的信息(表空间ID、页号)、等待状态(is_waiting)：true 或者 false

113. MVCC可以完全解决幻读吗？

• MVCC在可重复读的级别下可以很大程度避免幻读问题，但是不能完全避免
• 比如说事务1先根据特定的条件查看了一个不存在的记录，然后事务2在后续过程中插入与事务1的过滤条件相符合的记录，此时事务1按照之前的特定条件来更新记录
• 此时，如果可重复读可以完全解决幻读问题的话，那么这时候是不会更新成功的，但是此时，由于update操作本身是通过加锁完成的，不是通过快照判断数据是否存在，因此最终会更新语句成功，而此时对应数据行的事务版本号就变成了当前执行事务的版本号
• 那么当前事务再按之前的过滤条件来查找的时候就能够查询到对应的消息。最终就导致了两次读取的结果不一致

114. 如何解决死锁问题？

• 首先，可以通过死锁日志查看定位发生死锁的事务id以及对应的语句

• 然后，结合事务的前后的语句一起分析死锁产生的原因

• 对于多次死锁情况，可以设置全局变量，innodb_print_all_deadlocks为ON，可以将所有死锁信息记录在错误日志中

115. 行锁和间隙锁的区别？

• 行锁只能锁索引上对应的某条记录
• 间隙锁只能锁定两条记录之间的间隙，不能锁定记录，开区间

116. select count(*) 和 select count(1) select count(column)的区别？

• https://www.cnblogs.com/sueris/p/6650301.html

• count(*)和count(1)执行的效率是完全一样的。

• count(*)的执行效率比count(col)高，因此可以用count(*)的时候就不要去用count(col)。

• count(col)的执行效率比count(distinct col)高，不过这个结论的意义不大，这两种方法也是看需要去用。

• 如果是对特定的列做count的话建立这个列的非聚集索引能对count有很大的帮助。

• 如果经常count(*)的话则可以找一个最小的col建立非聚集索引以避免全表扫描而影响整体性能。

• 在不加WHERE限制条件的情况下，COUNT(*)与COUNT(COL)基本可以认为是等价的； 但是在有WHERE限制条件的情况下，COUNT(*)会比COUNT(COL)快非常多；

• count(0)=count(1)=count(*)

  1. count(指定的有效值)--执行计划都会转化为count(*)

  2. 如果指定的是列名，会判断是否有null，null不计算

117. PreparedStatement如何防止SQL注入？

• 通过字符串来进行SQL拼接的时候，传入的参数是有可能称为条件的一部分的

• 而通过PreparedStatement的set方法来设置参数的时候，传入的字符串整体是作为一个字符串参数，会被引号包起来，并把参数中的引号作为转义字符，避免参数成为条件的一部分

• 对于setInt，setDouble之类，编译器会检查参数类型，已经避免了SQL注入

• 对于setString方法，方法内部会先判断是否需要转义处理(比如包含引号，换行等字符)，如果不需要转义，则在两边加上单引号。如果需要转义，就进行转义处理

  • 最终结果，参数会被单引号包裹，并且类似单引号之类的特殊字符会被转义处理，通过对这些代码的控制避免了SQL注入

118. PreparedStatement如何提高效率？

• SQL语句在数据库执行的具体步骤：
  • Convert given SQL query into DB format -- 将SQL语句转化为DB形式（语法树结构）
  • Check for syntax -- 检查语法
  • Check for semantics -- 检查语义
  • Prepare execution plan -- 准备执行计划（也是优化的过程，这个步骤比较重要，关系到你SQL文的效率，准备在后续文章介绍）
  • Set the run-time values into the query -- 设置运行时的参数
  • Run the query and fetch the output -- 执行查询并取得结果
• 预编译SQL语句，就是对于同样的SQL语句（包括不同参数的），1-4步骤只在第一次执行，所以大大提高了执行效率（特别是对于需要重复执行同一SQL的）

119. MySQL的内部组成介绍一下？

• 服务层(Server)
• 服务层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
  • 跨存储引擎的功能都在该层实现
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认的存储引擎。
  • 主要负责数据的存储和提取

120. 行锁适用场景有哪些？

• A用户消费，service层先查询该用户的账户余额，若余额足够，则进行后续的扣款操作；这种情况查询的时候应该对该记录进行加锁。
• 否则，B用户在A用户查询后消费前先一步将A用户账号上的钱转走，而此时A用户已经进行了用户余额是否足够的判断，则可能会出现余额已经不足但却扣款成功的情况。
• 为了避免此情况，需要在A用户操作该记录的时候进行for update加锁

121. innodb页的大小是多少？

• innodb与磁盘进行交互的时候，是以页为单位的，一个页的大小默认16KB
• 运行过程中，页的大小是不能修改的，但是启动的时候可以通过参数指定

122. innodb中数据行的格式？

• 数据行内容主要分为两部分，前者为记录的额外信息，后者为记录的真实数据

• 在后者里面会添加额外的列，如row_id，trx_id，roll_pointer

  • row_id，当数据表没有主键，且没有非空的唯一键的时候会创建，用来当作主键，6字节
  • trx_id，事务id，6字节
  • roll_pointer，回滚指针，7字节

• 行格式

  • 分别是Compact、Redundant、Dynamic和Compressed行格式

• 一个页至少保存两条记录，因此如果一个列的数据过大，那么会将数据存储到溢出页那里，在真实记录那里留下一个指向溢出页的指针

• 溢出页和普通页不一样，没有至少保存两条记录的限制

123. innodb中数据页（索引页）的格式？如何判断数据页是否只刷新了一半就断电了？

• 如何判断数据页是否只刷新了一半就断电了？

  • 在数据页中会有File Header和File Trailer
  • 在这两者中都会有一个四字节长度的校验和，当进行数据页刷新的时候，会先把新页面的校验和计算出来，然后写到File Header那里，如果页面全部刷新成功的话，那么两者的校验和应该是一致的，否则说明刷新的时候出现了问题，导致页面刷新不完全
  • 同时还会校验头部和尾部的LSN

• 数据页中的每一个记录的头信息中有一个next_record属性，将页中的所有记录串成一个单向链表

• 数据页中被删除的记录会使用空闲指针连接起来，以备以后复用

  • 数据页采用链表方法来存储数据，方便增删，通过槽的方式来增加随机查找的效率
  • 不用数组的形式是因为随机增删需要进行移动，效率较低

• 此外还维护了一个Infimum和Supremum两个伪记录，前者是最小的记录，后者是最大的记录

• 然后将记录分成多个槽，每个槽记录了当前组最大记录的偏移，Infimum和Supremum在不同组，一个组的记录4-8条

  • 这些槽存储在页目录上
  • 槽所记录的偏移指向next_record字段，往左即可定位指向下一条数据的指针，往右就是记录中的数据

• 通过分槽的方法，可以使用二分法来进行页内数据的快速定位

  • 根据槽进行二分，找到目标所处的槽，然后再遍历该槽找到目标数据

• 不同数据页通过双向指针连接，物理上不必连续

124. B+树索引结构？

• 非叶子结点实际上作为目录项记录的页，里面保存的是主键的值以及对应的页号
• 目录项的主键值为下一层对应子节点的最小主键值
• 利用这个搭建B+树能够快速定位
• 能够有多少叉，决定于页的大小
• 目录项的页与普通记录的页的差别在于其只有两个字段，分别存储主键值和页号

125. 如何分析回表代价？

• 对于非聚集索引而言，叶子节点保存了索引值和主键值
• 每次匹配到一条数据都需要进行回表操作，回表操作需要通过随机IO定位主键的页，将对应的页加载到内存中
• 非聚集索引本身有序，但是其对应的主键不一定有序，因此随机IO次数可能会比较多
• 如果最终数据行的数目很多的话，可能直接使用全表扫描的性能会更好一点
• 因此，优化器会根据数据行的数目、回表次数决定使用索引还是直接全表扫描

126. 表空间介绍一下？

• MySQL中的页存储在表空间中

• 页空间分为：系统表空间和独立表空间

  • 系统表空间用来存储所有表的数据
  • 独立表空间则是每一个表创建一个，然后保存数据

• 使用独立表空间的情况下

  • innodb
    • test.frm
      • 表结构文件
    • test.ibd
      • 表数据文件
  • myisam
    • test.frm
      • 表结构文件
    • test.MYD
      • 表数据文件
    • test.MYI
      • 表索引文件

• 通过页号可以快速在表空间定位对应的页

• 页号由4字节组成，因此一个表空间最多有2^32个页

127. innodb表空间结构是什么样的？

• 表空间被划分为许多连续的区，每个区默认由64个页组成，每256个区划分为一个组，每个组最开始的几个页面类型是固定的，第一个组的开始几个页面类型有所不同

• 一个区默认占用1mb，一个组默认占用256mb

• 为什么要提出区？

  • 如果叶子节点的页是连续的，那么顺序IO的性能会很好
  • 因此应该尽量让相邻的叶子节点放到同一个区中
  • 当数据量较大的时候，直接以区为单位为索引分配空间，虽然有可能浪费一点空间，但是效率较高

• 为什么要提出段？

  • B+树有叶子节点和非叶子节点，这两种页应该分开存储，也就是一个索引的页应该分成两个段
  • 段内以区为单位申请空间
  • 但是为了避免小表占用多余空间造成的浪费，提出了碎片区的概念
  • 一开始段以碎片区的单个页面为单位来分配存储空间，当分配的碎片区页面大于32后，以完整的区进行分配
  • 因此，段其实是零散页面和完整区的集合

128. SQL查询成本介绍一下？

• IO成本

  • 将页从磁盘加载到内存损耗的时间就是IO成本
  • 默认成本为1

• CPU成本

  • 读取记录、检查记录是否符合搜索条件、对结果集进行排序等这些操作损耗的时间就是CPU成本
  • 默认成本为0.2

• 全表扫描代价

  • 聚簇索引占用的页面数
    • 对应了IO成本
    • 一个页一个IO成本
  • 该表的记录数
    • 对应了CPU成本
    • 一条记录一个CPU成本
  • 上面两个信息可以根据表统计信息中的rows和data_length计算得到

• 唯一二级索引代价

  • 默认一个扫描区间的IO成本为一个页面的IO成本
  • 得到记录数，一条记录对应一个CPU成本
  • 每一条记录需要回一次表，对应一次IO成本
  • 回表得到完整记录后还需要检查记录是否符合条件，一条记录对应一个CPU成本

129. 子查询如何优化？

• 对于in的子查询优化
  • 子查询中的表上拉
  • 重复值消除
  • 松散扫描
  • 半连接物化
  • 首次匹配
• 通过将子查询转为半连接的形式来进行优化
  • 全连接的话是驱动表根据匹配的列值在被驱动表中找到全部的对应行然后组合得出结果
  • 半连接的话，驱动表中，只要能够在被驱动表中找到一个对应的能够匹配的就可以作为结果返回