数据库相关知识点

---

1、innodb和myisam的区别？

1、innodb支持事务、myisam不支持事务；
2、innodb支持外键、myiasm不支持外键；
3、innodb支持行锁、表锁、myisam支持表锁；
4、innodb是聚集索引，数据文件和索引绑定在一起，myisam是非聚集索引，数据文件和索引是分离的；
5、innodb不保存表的行数，myisam会使用一个变量来保存表的行数；
6、innodb不支持全文索引，在5.6版本后，支持全文索引，myisam支持全文索引；
7、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

    Innodb：frm是表定义文件，ibd是数据文件
    Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

8、Innodb和Myisam的底层是b+树，Innodb的主键索引树的叶子节点存放的是主键和数据，通过主键从b+树根节点开始查找到叶子节点，索引出数据，Innodb的辅助索引树也是b+树，叶子节点存放的是辅助索引和主键，还需要通过主键再一次查找主键索引树，才能找到数据；Myiasm的索引树的叶子节点存放的是主键和数据文件的地址，需要再一次通过数据文件地址I/O操作，load数据文件进内存，才能获取数据，Myisam的辅助索引树和主键索引树没有本质的区别，只是主键索引树要求key唯一，而辅助索引树的key不唯一，可以重复

使用场景：
MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

---

2、为什么Innodb的表必须有主键，而且推荐使用整型的主键自增？

使用innodb存储引擎，即使你没有为表建主键，innodb也会帮你找某一列数据是唯一的字段，然后建立索引树，如果没有找到，它会维护一个隐藏列，通过隐藏列来构建索引树，组织表中的数据，你建立主键它就可以使用你设置的索引列来维护，效率更高，而不用mysql来帮你查找索引，然后完成其他工作。

自增：叶子节点是有序的，为了减少b+树在插入时，分裂调整的次数。使用自增可以使insert语句较快。

整型：使用b+树插入或者查找时，需要进行比较，整型的比较效率比其他数据类型高，比如字符串，并且使用整型bigint撑死8个字节，采用其他数据类型大小可能比8字节大，使用整型更加节省空间。

---

3、什么是索引？

索引是一种排好序的数据结构，为了提高数据库的查询效率，索引底层使用的是b+树，b+树的非叶子节点只存储索引，叶子节点存储所有的索引字段和data（innodb主键索引树：索引+行数据；innodb辅助树：辅助索引+主键；myisam主键索引树：唯一索引+行数据地址；myisam辅助索引树：可重复索引+行数据地址），叶子节点用指针相连，提高区间访问效率。
索引的有序体现在索引树的叶子节点是有序的。
叶子节点使用双链表连接，能很好的支持范围查询。

---

4、一颗高度为3的B+树，能存储多少个索引（数据行）？

mysql为b+树的每个节点分配16kb的大小，指针位置大小为6B，我们使用bigint作为索引字段，为8B，第一层的索引个数为1170个，第二行与第一行的索引个数一样是1170个，叶子节点每个就当作1KB，索引个数是16KB/1KB = 16个，总的索引个数为：1170 * 1170 * 16=21,902,400个。

---

5、索引的底层为什么采用的是B+树，而不是二叉树、红黑树、哈希表呢？

1.采用二叉树，存在极端情况，当数据是有序的时候，树会退化为链表，树的高度急剧增大，需要进行的IO次数就越多：

2.采用红黑树，红黑树会通过旋转调整使得左右子树的高度差减小，比二叉树要好，但是在大数据量的情况下，红黑树的高度还是很高的，所进行IO次数也不少。

3.采用哈希表，咋一看好像查询比b+树快，只需对数据做一次hash运算，就可以得出数据行所在的文件地址；但是采用hash表，不能很好的支持范围查询，范围查询会退化为全表扫描；

综上，为什么采用B+树？
B+树是一颗多路平衡搜索树，它通过水平方向的拓展来降低树的高度，减少IO次数，B+树在树的层次较低时，所存储的索引个数多，叶子节点有全部的索引，它是冗余索引，且查找效率稳定，都是查找到叶子节点，叶子节点还有双指针连接，可以很好的支持范围查询，，所以采用B+树可以更好优化数据库查询效率。

---

6、联合索引的底层数据结构

mysql会根据索引字段从左到右依次比对，第一个字段相等则比较第二个字段，第二个字段相等则比较第三个字段，依次比较下去…，直到索引到唯一的数据行。
如果你直接使用第二个字段或者其他字段，第二个字段或者其他字段开始比较，它没有顺序，所以不会走索引。

7、Mysql架构

8、索引的分类

1. 普通（单列）索引：一个索引只包含一个列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，可以有空值
3. 主键索引：索引列的值必须唯一，不可以有空值
4. 联合索引：一个索引包含多个列
5. 全文索引：加快通过关键词模糊查找数据的速度，全文索引可能存在精度问题

9、索引失效的情况

1. 在组合索引中，不符合最左前缀法则，索引失效。
2. 范围查询右边的列，如果有索引，则失效。
3. 在索引字段上进行运算操作，索引将失效。
4. 字符串不加单引号，索引将失效。
5. or前后存在非索引的列，索引将失效。
6. 模糊查询like ，开头是%的，索引失效。
7. mysql评估全表扫描和使用索引扫描哪个更快，如果全表扫描更快，mysql会使用全表扫描，导致索引失效。
8. is null 和 is not null 有时索引失效，有时失效，与第7点有关。
9. in 走索引，not in不走索引，不是绝对的，看in后面跟的数据量，与第7点有关。
10. 存在多个单列索引作为查询条件时，mysql会自行选择最优的其中一个作为索引，其他的单列索引则失效。
11. 数据库和表的字符集统一使用utf8mb4，否则需要进行转换，导致索引失效。

10、什么情况下不适合建立索引

1. 表记录比较少
2. 经常进行增删改的表
3. 有大量重复数据的列
4. where条件中用不到的字段
5. 无序的列
6. 不要定义冗余或重复的索引

11、什么情况下适合建立索引

1. 经常作为查询条件的字段
2. 经常做表连接的字段
3. 经常出现在order by、group by、distinct后面的字段

12、数据库的三大范式

第一范式：确保列的原子性，列不可分
第二范式：在第一范式的基础上，消除部分依赖，即要求非主键列完全依赖于主键，而不能是部分依赖，对于联合主键来说。
第三范式：在第二范式的基础上，消除传递依赖，即表A中除了自身的字段和其他表B的主键以外，不能出现其他表B除主键以外的字段，这些字段会通过B主键间接依赖于A主键。

13、什么是事务？

事务是一组sql的集合，事务中的sql要么全部成功，要么全部失败。
默认情况下每条sql是一个事务。

事务的概念：把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称为事务。

数据库如果没有显示的开启事务，并且autocommit=on，那么一条语句就是单独的一个事务。

事务的状态：

• 活动的 (active) 事务对应的数据库操作正在执行过程中时，我们就说该事务处于活动的状态。
• 部分提交的 (parti aIl mmitted 当事务中的最后 个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事处于部分提交的状态。
• 失败的(创 当事务 于活动的状态或者部分提 的状 时，可能遇到了某些错误(数据库自身的错误、操作系统错误或者直接断电等〉而无法继续执行， 或者人为停止了当前事务的执行，我们就说该事务处于失败的状态。
• 中止的 (aborted 如果事务执行了半截而变为失败的状态，比如前面唠叨的狗哥向猪爷转账的事务 当狗哥账户的钱被扣除，但是猫爷账户的钱没有增加时遇到了错误，从而导致当前事务处在了失败的状态 那么就需要把已经修改的狗哥账户余额调整为未转账之前的金额.换句话说，就是要撤销失败事务对当前数据库造成的影响.这个撤销的过程用书面一点的话描述就是·回滚.当回滚操作执行完毕后 ，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处于中止的状态。
• 提交的 (committed 当一个处于部分提交的状态 事务将修改过的数据都刷新到磁盘中之后，我们就可以说该事务处于提交的状态。

13.1 事务的基本特征：

1. A（原子性）：事务所包含的所有操作要么全部成功，要么全部失败；
2. C（一致性）：事务发生前后，数据必须保持一致；
3. I（隔离性）：并发事务之间互不干扰；
4. D（永久性）：事务一旦提交，对数据库中的数据的修改是永久性的；

13.2 并发事务所带来的问题

1. 脏读：一个事务读取到了另一个事务未提交的数据
2. 不可重复读：不可重复读的重点是修改，同样条件下两次读取的结果不一样，也就是说被读取的数据被修改了。
3. 幻读：幻读的重点是新增和删除，同样的条件下两次读取的结果数目不一样，也就是说前后两次读取过程中，有新增的数据，或者有数据被删除，就像出现幻觉一样。

13.3 事务的隔离级别

隔离级别：表示一个会话中一个事务对另一个事务影响的程度；

1. 读未提交（READ UNCOMMITTED）：级别最低，它允许一个事务读取另一个事务未提交的数据，会导致脏读、不可重复读、幻读，但是它并发性能最好。
2. 读已提交（READ COMMITTED）：一个事务只能读取一个已经提交了的事务，即一个事务只允许对已经提交的记录可见，可以避免脏读，还是会有不可重复读，幻读存在。
3. 可重复读：一个事务开始之后，其他事务对数据库的修改对本事务不可见，但是其他事务对数据库的insert/delete操作，对本事务还是可见的，所以会有幻读存在。
4. 串行化（SERIALIZABLE）：事务的最高隔离级别，只允许事务串行执行，本质是对数据加锁（表锁）。

13.4 mysql事务的支持

mysql事务不是绑定在mysql服务器本身，而是与存储引擎相关

1. myisam：不支持事务，用于只读程序提高性能
2. innodb：支持事务、行级锁，并发
3. Berkeley DB：支持事务。

13.5 java事务的三种类型

事务：有三种类型：JDBC事务、JTA事务、容器事务
1. JDBC事务 : JDBC事务是用Connection对象控制的，JDBC Connection接口（java.sql.connection)提供了两种事务模式：自动提交和手动提交。

public void setAutocommited(boolean)
public boolean getAutoCommited()
public void commit()
public void rollback()
代码示例：
private Connection conn=null;
private PreparedStatement ps=null;
try{
	conn.setAutoCommited(false);//将自动提交设置为false，即改为手动提交
	ps.executeUpdate(“修改SQL”);//预编译语句执行修改操作
	ps.excuteQuery(“查询SQL”);//预编译语句执行查询操作
	conn.commit();//当两个操作成功过后手动提交
}catch(Exception e){undefined
	conn.rollback();//一旦某个操作失败都将回滚
	e.printStackTrace();
}

特点：事务的范围局限于一个数据库连接，一个JDBC事务不能跨越多个数据库
2. JTA事务（java Transaction API)事务:
JTA事务允许程序执行分布式事务处理，使用此事务，需要一个实现javax.sql.XADataSource、javax.sql.XAConnection和javax.sql.XAResource接口的JDBC驱动程序，一个XADataSource对象就是一个XAConnection对象的工厂，XAConnections是参与JTA事务的JDBC连接，对于XA的连接不能调用java.sql.Connection.commit()或者java.sql.Connection.rollback()
而是应该调用:

UserTransaction.begin()
UserTransaction.commit()
UserTransaction.rollback()

特点：可以跨越多个数据库，功能呢强大，使用复杂

3. 容器事务
容器事务主要是J2EE应用服务器提供的，容器事务大多是基于JTA完成，这是一个基于JNDI的，相当复杂的API实现。相对编码实现JTA事务管理，我们可以通过EJB容器提供的容器事务管理机制（CMT）完成同一个功能，这项功能由J2EE应用服务器提供。这使得我们可以简单的指定将哪个方法加入事务，一旦指定，容器将负责事务管理任务。这是我们土建的解决方式，因为通过这种方式我们可以将事务代码排除在逻辑编码之外，同时将所有困难交给 J2EE容器去解决。使用EJB CMT的另外一个好处就是程序员无需关心JTA API的编码，不过，理论上我们必须使用EJB。

比较：

1. JDBC事务控制的局限性在一个数据库连接内，但是其使用简单。
2. JTA事务的功能强大，事务可以跨越多个数据库或多个DAO，使用也比较复杂。
3. 容器事务，主要指的是J2EE应用服务器提供的事务管理，局限于EJB应用使用。

来源： https://www.cnblogs.com/baizhanshi/p/5159332.html

14、怎么发现慢sql

（1）查询mysql的慢查询日志，慢查询日志是mysql用来记录mysql中sql语句响应超时的一种日志，超过响应时间的sql会被记录到这个日志文件中。

通过慢查询日志我们可以查询出执行次数多，占用时间长的sql。

（2）show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。


发现了慢sql以后如何优化？

• 分析慢 SQL 查询：首先需要分析慢 SQL 查询的执行计划，了解查询语句的执行情况，找出造成查询慢的原因。可以通过数据库管理工具查看慢查询日志或者使用数据库性能分析工具进行分析。

• 优化查询语句：根据分析结果，对慢 SQL 查询语句进行优化。可以考虑优化查询条件、添加合适的索引、减少不必要的查询字段等方式来改善查询性能。

• 优化数据库设计：如果慢 SQL 查询是由于数据库设计不合理导致的，可以考虑优化数据库结构，调整表结构、索引设计等来提升查询性能。

• 缓存优化：对于频繁执行的查询，可以考虑使用缓存来减少数据库查询次数，提高查询效率。可以使用缓存技术如 Redis、Memcached 等来缓存查询结果。

• 代码优化：检查应用程序代码，确保 SQL 查询语句的编写方式正确，避免频繁执行 SQL 查询或者多次重复执行相同的查询。

• 性能测试：优化后的 SQL 查询需要进行性能测试，确保优化后的查询性能得到改进。

• 监控和持续优化：持续监控数据库性能，定期检查慢 SQL 查询情况，及时发现并优化慢查询，以保持系统的高性能。

15、什么是存储过程，有什么特点？

存储过程是事先编译好存储在数据库中的一组sql语句集合。
优点：

1. 存储过程只在创建的时候进行编译，以后执行存储过程不需要再编译，可以重复使用，提高数据库的执行效率；
2. 减少网络传输，调用时只需要传递存储过程名字和参数即可，相比于执行一段sql所传输的数据量要小；
3. 安全，参数化的存储过程可以防止sql注入攻击。
   缺点：移植性差

16、存储过程和存储函数的区别？

（1）存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。

（2）返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out,inout)。

（3）调用时的不同。存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名。

（4）参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数。存储过程的参数类型有三种，IN、out和INOUT：

a. in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量

b. out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量

c. inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。

17、什么是视图，游标是什么？

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

游标：
游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE；

18、什么是触发器?

19、对mysql的锁了解，都有哪些锁？

数据库的数据是共享资源，我们使用锁是为了在并发情况下，多个用户访问数据库中的数据时候，保证数据的一致性。

锁的分类：

• 按锁的粒度划分：

  • 表级锁：开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率高，并发度最低；
  • 行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度最高；
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
  • 间隙锁：间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻id之间出现空隙则会形成一个区间，遵循左开右开原则。间隙锁之间不会冲突。间隙锁是在可重复读隔离级别下才会生效的。
  • 临建锁：临建锁 = 间隙锁 + 行锁，遵循左开右闭原则。

• 按锁的类型划分：

  • 共享锁：又称读锁 是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（即获取数据上的排他锁），直到释放所有共享锁。在查询语句后面增加LOCK IN SHARE MODE，MySQL 就会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

  -- 当前读，查询id=1这行数据，并加读锁
  select *from user where id = 1 lock in share mode;
  

  • 排它锁：又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

  -- 当前读，查询id=1这行数据，并加写锁
  select *from user where id = 1 for update;
  

• 按锁的态度划分：

  • 悲观锁：

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

  • 乐观锁：

    • 乐观锁的特点先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过，若未被更新过，则更新成功；否则，失败重试。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号或者时间戳，然后按照如下方式实现：

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

  乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改。如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

  • 悲观锁与乐观锁的应用场景:
    一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

意向锁：（意向锁是表级锁）
意向锁是innodb自己维护的，用户无法手动操作意向锁。当事务需要加行共享(排他)锁时，会先自动在所在表添加共享(排他)意向锁。

当事务需要添加表锁，比如要添加表级共享锁时，检查的条件改为：

检查当前没有其他事务持有表的排他锁。
检查当前没有其他事务持有表的意向排他锁。
如果其他事务持有意向排他锁，证明此时表的某些行被加了排他锁，而无需去遍历检查表的每一行。效率明显提高。

20、隔离级别与锁的关系

21、xxxxxx

22、xxxxxx

23、mysql中Innodb行级锁是如何实现的？

MySQL InnoDB 行锁是通过一个锁结构与索引记录关联来表示加锁的。

24、简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

• delete：用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；修改会记录binglog，可以通过binlog恢复数据；主键数值不会重置；

• truncate：删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，truncate比delete更快，占用的空间更小；主键数值会重置。

• drop：命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

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

25、where和having的区别？

1. 执行顺序不一样，where是分组前条件筛选，having是对分组后的结果进行筛选
2. where后不能跟聚合函数，having后可以跟聚合函数

26、mysql的语句执行顺序:

27、列表深分页如何优化？

1. 业务维度优化
   不给查询太后面的数据，一般根据检索条件查询数据，再翻页，后面的数据没有太多意义。

2. 覆盖索引优化

-- select id from t limit 990000,5 走覆盖索引
select * from t a, (select id from t limit 990000,5) b WHERE a.id = b.id

3. id分页

select *from t where id > x order by id limit z
x:上一页最后一个主键
z:每页条数

28、count(*)和count(1)和count(字段)的区别？

1. count(1)和count(*)会统计null，count(字段)不会统计null
2. 执行效率的区别：
   (1）如果列为主键，count(列名)效率优于count(1)
   (2）如果列不为主键，count(1)效率优于count(列名)
   (3）如果表中存在主键，count(主键列名)效率最优
   (4）如果表中只有一列，则count(*)效率最优
   (5）如果表有多列，且不存在主键，则count(1)效率优于count(*)
3. 缺点：count操作需要扫描全表数据，所以性能较差，像一些分页插件，会有查询count总数的操作，所以可以关闭，然后自己手动写查询count总数语句进行优化。

29、explain分析执行计划

使用 EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay，则往往表示SQL需要优化了。





小结：

• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• 部分统计信息是估算的，并非精确值

30、varchar和char的区别？

varchar是一种可变的长度类型，char是一种固定的长度类型；

varchar(50)中50的含义：表示最多可以存50个字符的数据，会使用1~2字节保存长度。
char(50)中50的含义：表示最多可以存50个字符的数据，长度不够，右边用0来填充，在检索时，会将后边的空格去掉；
int(20)中20的含义：表示数字的长度为20位，左填充0；

31、手撸JDBC

public class JDBCTest {
    
    String driver = "com.mysql.cj.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/student?characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    String username = "root";
    String password = "123456";

    @Test
    public void test() throws ClassNotFoundException, SQLException {
        // 1.加载驱动
        Class.forName(driver);
        // 2.获取连接
        Connection conn = DriverManager.getConnection(url, username, password);
        // 3.定义sql
        String sql = "select *from user where id = ?";
        // 4.获取preparedStatement对象
        PreparedStatement prep = conn.prepareStatement(sql);
        // 5.占位符设置参数
        prep.setInt(1, 1111111);
        // 6.执行sql获取结果集
        ResultSet res = prep.executeQuery();
        // 7.遍历结果集
        while(res.next()){
            int id = res.getInt(1);
            String name = res.getString(2);
            System.out.println("id="+id + ",name="+name);
        }
        // 8.关闭资源
        prep.close();
        conn.close();
    }

}

32、数据库的性能优化手段有哪些？

SQL 优化：

1. 使用缓存优化查询
2. 给搜索字段建立索引，给where条件后边的字段建立索引，给order by和group by后边的字段建立索引
3. 明确查询或者删除数据只有一行，使用limit 1
4. 设计数据库时，尽量用小字段，且字段大小是固定的
5. 为每张表建立主键，自增，整型
6. 避免使用select * ，尽可能使用覆盖索引
7. 使用explain分析sql
8. 将表连接查询转为单表查询
9. 将子查询转为表连接
10. 优化分页查询

表结构优化：

• 尽量使用数字类型字段，提高比对效率；
• 长度不变且对查询速度要求高的数据可以考虑使用 char，否则使用 varchar；
• 表中字段过多时可以适当的进行垂直分割，将部分字段移动到另外一张表；
• 表中数据量过大可以适当的进行水平分割，将部分数据移动到另外一张表。

其它优化：

• 对查询频率高的字段适当的建立索引，提高效率；
• 根据表的用途使用合适的数据库引擎；
• 读写分离。

33、数据库的数据类型有哪些？

34、手动提交事务

set @@autocommit=0;  -- 关闭事务自动提交
show VARIABLES like '%autocommit%';  -- 查看事务状态
start transaction ; -- 开启事务
select *from user WHERE id='1' for update;  --执行sql
commit; -- 提交事务

35、分库分表

并发量大，进行分库；数据量大，进行分表。

• 水平分库:多个相同的数据库放在不同的服务器上。
• 垂直分库：将一个数据库中的表，按照逻辑功能进行划分，放到不同的数据库中。实现专库专用。
• 水平分表：表的字段不变，但是根据某种规则分成多个表，比如id为奇数的为一个表 偶数的为一个表。
• 垂直分表：将表中的字段进行分割，把经常查询的字段放在一起，不经常查询的字段放在一起，放在不同的表中。

36、mysql中的锁定读语句在不同隔离级别下加锁的情况：

快照读：（读的是数据的历史版本，由MVCC实现）

• select …from …

当前读：(读的是数据的最新版本)

• select …lock in share mode 加S锁
• select … for update 加X锁
• update 加X锁
• delete 加X锁

在读未提交和读已提交的隔离级别下：

通过explain执行计划分析，走的什么索引，确定扫描区间，从扫描区间最左的一条记录开始往右扫描，扫描的记录符合查询条件时，则加行记录锁，不符合查询条件则释放该记录上的锁。

• 走的是聚簇索引：只会在聚簇索引树的记录上加锁

• 走的是二级索引：二级索引记录和聚簇索引该条记录都会加锁

在可重复读和串行化的隔离级别下：

通过explain执行计划分析，走的什么索引，确定扫描区间，从扫描区间最左的一条记录开始往右扫描，扫描的记录符合查询条件时，则加next-key锁，不符合查询条件也不会释放该记录上的锁。

• 走的是聚簇索引：只会在聚簇索引树的记录上加next-key锁

• 走的是二级索引：二级索引记录加next-key锁，聚簇索引记录加行记录锁

所以在进行update或者delete时，如果where条件中的列没有索引，则会进行全表扫描，在可重复读和串行化的隔离级别下会对扫描过的记录加next-key锁，导致全表的记录都被加锁，从而锁表，其他想操作该表的语句会被阻塞。所以批量更新数据的时候要小心，否则容易导致锁表，引起服务崩溃。

37、redo log的理解：

读取数据时，数据页会被加载到内存的buffer pool中，buffer pool中的数据页对应的控制块会连成一个LRU链表，写数据时，直接修改buffer pool中的数据页中的数据，并将修改的数据页，“脏页”对应的控制块加入一个链表中，由后台线程每隔一段时间将“脏页”刷新到磁盘中。

为了防止在刷脏页的间隔中数据库崩溃导致已提交的事务对数据的修改丢失了，所以在修改buffer pool中的数据页时，会记录修改的数据到redo log日志中，但是直接写redo log到磁盘速度也很慢，所以引入了redo log buffer，然后再等待时机，将redo log buffer中的修改数据刷新到redo log中。只要数据写入redo log文件中，就不会丢失已提交的事务的修改。

redolog会将数据的修改记录保存到redo log buffer中，redo log buffer的刷盘时机：即刷入redo log文件中

1. redo log buffer空间占满50%，会进行刷盘
2. 事务提交时，刷盘策略：
   1. 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作（系统默认master thread每隔1s进行一次重做日
      志的同步）。
   2. 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值），即将redo log buffer中数据写入redo log文件中。
   3. 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache，由操作系统的刷盘策略自行决定何时刷盘
3. 后台线程每秒一次的频率刷入磁盘
4. 正常关闭数据库服务器时
5. 做checkpoint时

redo log存储结构：redo log日志在磁盘是以日志文件组的形式存在，并循环写入。

redo log只在数据库系统崩溃时恢复数据时有用，那么已经刷入磁盘中的数据，在redo log中就已经没用了，所以可以被覆盖。

redo log只保存修改的数据记录，而不是数据页。

redo log是物理日志，记录的是数据页的物理变化

redo log的作用：

1. redolog保证了事务的持久性

2. 让数据库具有崩溃恢复的能力。

38、undo log的理解：

事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做，但是偏偏有时候事务在执行到一半时会出现一些情况，比如下面这些情况:

• 事务执行过程中可能遇到各种错误，比如服务器本身的错误、操作系统错误，甚至是突然断电导致的错误
• 程序员可以在事务执行过程中手动输入 OLLBACK 语句结束当前事务的执行.

为了保证事务的原子性，需要将执行一半的事务进行回滚撤销，把要撤销回滚记录的东西保存起来称为undo log。
在事务中 更新数据 的 前置操作 其实是要先写入一个 undo log。

对数据的修改时记录的undo log：

• 在插入一条记录时，至少要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了;
• 在删除一条记录时，至少要把这条记录中的内容都记下来 ，这样之后回滚时再把这些内容组成的记录插入到表中就好了；
• 在修改一条记录时，至少要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了.
  修改数据时，其实一般情况下是先删除当前记录，再插入一条记录。

undo log的作用：

1. undo log保证了事务的原子性，回滚日志。

2. undo log会将记录的修改串成一个版本链，用于实现MVCC。

undo log是逻辑日志，对事物回滚时，只是将数据库逻辑的恢复到原来的样子
undo log的内容由redo log来保证。

39、bin log的理解：

binlog是逻辑日志，属于Mysql Server层，不管什么存储引擎，只要修改了表数据，都会产生binlog日志，binlog日志记录了语句的修改逻辑（DDL和DML语句）。

数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

binlog日志的记录格式：

• statement：记录sql语句原文，如果有函数不会计算出来，比如now()
• row：记录sql语句原文与原始值，如果有函数会计算出来，比如now()=1231231231
• mixed：为statement和row的混合，会自行判断进行选择

binlog的写入策略：
bin log只在事务提交的时候写入。

sync_binlog：0 每次提交事务，只是将bin log日志写入到page cache，由操作系统自行判断什么时候fsync到磁盘。（默认值）
sync_binlog：1 每次提交事务，将修改记录fsync到磁盘，跟redo log刷盘流程一样。
sync_binlog：N 每次提交事务，将事务写入page cache，累计N个事务之后，才fsync到磁盘。

先写入binlog cache，然后提交事务时进行刷盘，同redo log日志提交事务时的刷盘策略一样。

binlog的作用：

1. 数据恢复

2. 数据复制、同步

redo log 和 bin log两份日志的逻辑不一致怎么办？
当修改数据时，写完了redo log之后还没有写bin log的时候，mysql程序异常退出，导致redo log和bin log的数据不一致，下次使用bin log恢复数据时，会缺失一部分数据。

为了解决两份日志的一致性，innodb存储引擎使用两阶段提交的方案：
即将redo log的写入拆分成两个步骤prepare和commit，这就是两阶段提交。

使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

再看一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

总结：
3. 事务的原理ACID的实现：

• 事务的隔离性由 锁机制 和 MVCC 实现。
• 而事务的原子性由undo log保证、持久性由redo log来保证。
  • REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持
    久性。
  • UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性
• 一致性由原子性、隔离性、持久性来保证。

4.修改数据时，undo log、redo log、bin log的写入时机与顺序如下图所示：
写入顺序：undo log > redo log > bin log

40、MVCC：

MVCC 是通过数据行的多个版本管理来实现数据库的并发控制，主要是为了提高数据库并发性能（读-写 、 写-读） 。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。
MVCC 的实现依赖于：隐藏字段、Read View、undo log

如何解决脏读？
读已提交隔离级别使用MVCC来解决脏读问题，在一个事务中，每次执行select语句生成一个ReadView，这样就只能读到其他事务已提交的数据。

如何解决不可重复读？
可重复读隔离级别使用MVCC来解决不可重复读问题，在一个事务中，第一次执行select语句生成一个ReadView，后续在该事物中执行select语句都不会再生成新的ReadView，而是使用第一次生成的那个ReadView，这样就不会产生不可重复读的现象。

如何解决幻读？
在可重复读隔离级别下，普通的查询是快照读，当前事务是不会看到别的事务插入的数据的。因此，幻读问题在 “当前读” 下才会出现。
解决：快照读 MVCC + 当前读 Next-Lock Key(只在可重复读隔离级别下生效)

但是，强调一点的是，MySQL在可重复读级别下，并没有完完全全的解决幻读问题，特别是在一个事务的快照读和当前读穿插使用的场景下，还是会出现幻读的情况，如下：

面试官：幻读有什么问题，MySQL 是如何解决幻读的
答：幻读就是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
幻读的后果就是数据库中的数据和 binlog 的执行结果会不一致，其原因就在于，我们无法阻止新插入的数据。就是说，我们在给扫描到的行加锁的时候，你等会要插入的行还不存在，也就没法对他进行加锁，那么这个新插入的数据，可能在主库中是这个样子，从库执行完 binlog 后其实是会被修改的。
这也就是为啥幻读会被单独拎出来解决的原因了。
幻读问题在 “当前读” 下才会出现。
所谓当前读就是，读取的是最新版本的数据, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。
与之对应的，快照读，读取的是快照中的数据，不需要进行加锁。读取已提交和可重复读这俩隔离级别下的普通 select 操作就是快照读。其实就是 MVCC 机制，或者说，在快照读下，采用 MVCC 机制解决幻读。
然后，对于当前读这种情况，前面我们说，由于无法阻止新插入的数据，所以无法解决幻读问题，所以，我们考虑，不仅对扫描到的行进行加锁，还对行之间的间隙进行加锁，这样就能杜绝新数据的插入和更新。这个其实就是记录锁 Record Lock 和间隙锁 Gap Lock，也被称为临键锁 Next-Lock Key。
临键锁只在可重复读也就是 InnoDB 的默认隔离级别下生效。也可以采用更高的可串行化隔离级别，所有的操作都是串行执行的，可以直接杜绝幻读问题。

事务的隔离级别由MVCC实现，MVCC依赖于：隐藏字段、ReadView、undo log

41、mysql主从复制原理

https://m.php.cn/article/462450.html

1、mysql从库启动时会创建一个io线程和一个sql线程，并且从库会连接主库
2、从库连接主库时会创建一个log dump 线程
3、当主库执行修改操作时，log dump线程会将binlog中的修改信息发送给从库，从库的io线程会处理主库发送过来的信息，将修改信息保存在中继日志relay log中
4、然后sql线程会读取中继日志中的信息进行回放，同步到从库中，从而保证主从数据同步。

42、mysql中的日志：

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志 、 错误日志 、 通用查询日志和慢查询日志，这也是常用的4种。MySQL 8又新增两种支持的日志： 中继日志和数据定义语句日志 。
使用这些日志文件，可以查看MySQL内部发生的事情。
这6类日志分别为：

• 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，
  对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的
  状态，从而对服务器进行维护。
• 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故
  障时数据的无损失恢复。
• 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。
  从服务器通过读取中继日志的内容，来同步主服务器上的操作。
• 数据定义语句日志：记录数据定义语句执行的元数据操作。
  除二进制日志外，其他日志都是 文本文件 。默认情况下，所有日志创建于 MySQL数据目录 中。

日志的弊端

• 日志功能会 降低MySQL数据库的性能 。
• 日志会 占用大量的磁盘空间 。

43、mysql中的死锁排查：

https://blog.csdn.net/Linging_24/article/details/135397478?spm=1001.2014.3001.5502

---

其他：
https://www.cnblogs.com/wenxiaofei/p/9853682.html
https://blog.csdn.net/qq_22222499/article/details/79060495