MySQL

目录

• 第一部分 索引
  • 一、类型
  • 二、聚集索引和非聚集索引
  • 三、Innodb索引
• 第二部分 事务
  • 一、问题
  • 二、事务隔离
  • 三、事务特性
• 第三部分 锁
• 第四部分 数据库三范式
• 第五部分 主从复制
• 第六部分 MVCC-多版本并发控制
• 第七部分 存储引擎
  • 一、 InnoDB
  • 二、MyIsam
  • 三、区别
• 第八部分 SQL
  • 一、语法
  • 二、错误的题目
• 第九部分 数据库分库分表
  • 一、概念
  • 二、几种常用的分库分表的策略
  • 三、分库分表后引入的问题
• 第十部分 MYSQL面试题

---

基本语法：select/delete/insert/update、limit、join等
索引：B+树，聚族索引，二级索引，组合索引，最左匹配原则，索引失效、慢查询
事务：事务四大特性ACID，事务隔离级别，MVCC
锁：全局锁、表级锁、行级锁、快照读、当前读、乐观锁、悲观锁、死锁
日志：重做日志(redo log)、回滚日志(undo log)、二进制日志(binlog)
架构：读写分离、主从架构、分库分表、数据库和缓存双写一致性

---

第一部分 索引

一、类型

• 数据结构角度：B+树索引（最常用）、全文索引、哈希索引
• 物理存储角度：聚集索引、非聚集索引
• 逻辑角度：主键索引、普通索引、多列索引、空间索引、唯一索引或非唯一索引
• 统一：https://blog.csdn.net/qq_41793064/article/details/108697472
  1. 普通索引，基本的索引，没有任何限制，用于加速查询，数据可以重复
  2. 组合索引，指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用
  3. 全文索引，用来查找文本中的关键字
  4. 唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  5. 主键索引，特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。也就是在唯一索引的基础上相应的列必须为主键

二、聚集索引和非聚集索引

1. 概念

   • 聚集索引：一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是平衡树结构，换句话说，就是整个表就变成了一个索引，也就是所谓的聚集索引。
   • 非聚集索引：索引树结构中各节点的值来自于表中的索引字段
   • 主键的作用就是把表的数据格式转换成索引（平衡树）的格式放置。
   • 优缺点：索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的，因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确

2. 索引失效的情况：（1）使用!= 或 <>；（2）最左前缀原则；（3）字段类型不一致索引失效；（4）函数导致索引失效；（5）运算符导致索引失效；（6）or引起索引失效；（7）模糊查询导致索引失效；（8）NOT IN、NOT EXISTS导致索引失效；（9）IS NULL不走索引，IS NOT NULL走索引

3. 区别

   • 通过聚集索引可以一次查到需要查找的数据， 而通过非聚集索引第一次只能查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。
   • 聚集索引一张表只能有一个，而非聚集索引一张表可以有多个。

4. 回表(二次查询):

   • 使用聚集索引查询可以直接定位到记录，而普通索引需要扫描两遍索引树，即先通过普通索引定位到主键值，再通过聚集索引定位到行记录。
   • 解决回表问题:联合索引（多列索引）——建立在多个字段上的索引，如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。
   • 最左前缀匹配：
     1. 在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
     2. 作用：实现这种能快速查找的算法，索引就要满足特定的数据结构。索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。
   • 无法使用联合索引的情况：查询未从最左列开始时，无法用到联合索引；查询列不连续；精确匹配最左列，范围匹配非连续列

5. 索引原则：选择唯一性索引；为经常需要排序、分组和联合操作的字段建立索引；为常作为查询条件的字段建立索引；限制索引的数目；尽量使用数据量少的索引；尽量使用前缀来索引；删除不再使用或者很少使用的索引；最左前缀匹配原则；尽量选择区分度高的列作为索引；索引列不能参与计算，保持列“干净”：带函数的查询不参与索引；尽量的扩展索引，不要新建索引

6. 参考链接：

   • https://www.cnblogs.com/expiator/p/14873385.html
   • https://zhuanlan.zhihu.com/p/373171506

三、Innodb索引

1. 参考链接：

   • https://www.cnblogs.com/williamjie/p/11081081.html
   • https://blog.csdn.net/qq_45725126/article/details/119781704

2. 索引结构：InnoDB使用的是聚簇索引，将主键组织到一棵B+树中。

   • 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
   • 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处：减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

   

3. 实现原理：

   • 主索引树非叶节点（绿色）

     1. 子节点存储的主键里最小的值（Min Cluster Key on Child），这是B+树必须的，作用是在一个Page里定位到具体的记录的位置。
     2. 最小的值所在的Page的编号（Child Page Number），作用是定位Record。

   • 主索引树叶子节点（黄色）

     1. 主键（Cluster Key Fields），B+树必须的，也是数据行的一部分
     2. 除去主键以外的所有列（Non-Key Fields），这是数据行的除去主键的其他所有列的集合。
     3. 这里的1和2两部分加起来就是一个完整的数据行。

   • 辅助索引树非叶节点非（蓝色）

     1. 子节点里存储的辅助键值里的最小的值（Min Secondary-Key on Child），这是B+树必须的，作用是在一个Page里定位到具体的记录的位置。
     2. 主键值（Cluster Key Fields），非叶子节点为什么要存储主键呢？因为辅助索引是可以不唯一的，但是B+树要求键的值必须唯一，所以这里把辅助键的值和主键的值合并起来作为在B+树中的真正键值，保证了唯一性。但是这也导致在辅助索引B+树中非叶节点反而比叶子节点多了4个字节。（即下图中蓝色节点反而比红色多了4字节）
     3. 最小的值所在的Page的编号（Child Page Number），作用是定位Record。

   • 辅助索引树叶子节点（红色）

     1. 辅助索引键值（Secondary Key Fields），这是B+树必须的。
     2. 主键值（Cluster Key Fields），用来在主索引树里再做一次B+树检索来找到整条记录。

   

   

第二部分 事务

一、问题

1. 丢失更新： 两个事务同时更新一行数据，最后一个事务的更新会覆盖掉第一个事务的更新，从而导致第一个事务更新的数据丢失。

2. 脏读：一个事务在对一条数据进行修改的过程中，其它的事务对数据进行读取;

3. 不可重复读：一个事务在对数据进行读取的过程中，其它事务对此数据进行修改。在同一事务范围内读取两次相同的数据，所返回的结果不同

4. 幻读：一个事务前后两次查询同一范围的数据过程中，其它事务在这个范围内插入新的数据。

二、事务隔离

1. 读未提交、读提交、重复读、序列化；级别依次从低倒高，级别越高事务的之间的隔离操作就越严格，当然要求越严格其性能也会越低。
   • READ_UNCOMMITTED（读未提交）：一个事务能读取到别的事务未提交的更新数据；有丢失更新、脏读、重复读、幻读的问题；
   • READ_COMMITTED（读提交）：一个事务能读取到别的事务提交的更新数据，不能看到未提交的更新数据；解决脏读问题，丢失更新，存在不可重复读、幻读问题；
   • REPEATABLE_READ（可重复读）：保证同一事务中先后执行的多次查询将返回同一结果，不受其他事务影响，但是读取范围数据的时候可以插入数据，所以还会存在幻读问题；
   • SERIALIZABLE （序列化）：所有事务都是进行串行化顺序执行；解决所有问题。但是这种事务隔离级别下事务执行的效率低下，比较耗数据库性能。
2. 实现原理
   • READ_UNCOMMITTED（读未提交）
     1. 事务对当前被读取的数据不加锁；
     2. 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级共享锁，直到事务结束才释放。
   • READ_COMMITTED（读提交）
     1. 事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
     2. 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
   • REPEATABLE_READ（可重复读）
     1. 事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放；
     2. 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放
   • SERIALIZABLE （序列化）
     1. 事务在读取数据时，必须先对其加表级共享锁 ，直到事务结束才释放；
     2. 事务在更新数据时，必须先对其加表级排他锁 ，直到事务结束才释放。
3. MVCC实现：读写锁中，读和写的排斥作用大大降低了事务的并发效率，主要讨论可重复读级别的实现
4. 参考链接：https://www.jianshu.com/p/743e86b01a32

三、事务特性

1. 原子性（Atomicity）

   • 概念：即事务是不可分割的最小工作单元，事务内的操作要么全做，要么全不做，不能只做一部分；

   • 实现原理：undo log

     1. redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。
     2. 实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句
     3. undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

2. 一致性（Consistency）

   • 在事务执行前数据库的数据处于正确的状态，事务执行完成后数据库的数据还是处于正确的状态，即数据完整性约束没有被破坏；
   • 实现原理：
     1. 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
     2. 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
     3. 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

3. 隔离性（Isolation）

   • 概念：并发事务执行之间无影响，在一个事务内部的操作对其他事务不产生影响，这需要事务隔离级别来指定隔离性；
   • 实现原理：
     1. (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性——事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。
     2. (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

4. 持久性（Durability）

   • 概念：事务一旦执行成功，它对数据库的数据的改变必须是永久的，不会因各种异常导致数据不一致或丢失。
   • 实现原理：redo log
     1. 缓存(Buffer Pool)：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中。
     2. 如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
     3. 当数据修改时，除了修改Buffer Pool中的数据，还在redo log记录这次操作；如果MySQL宕机，重启时读取redo log中的数据，对数据库进行恢复。

5. 参考链接：

   • https://www.cnblogs.com/kismetv/p/10331633.html

第三部分 锁

1. 目的：解决事务的隔离性问题，为了让事务之间相互不影响
2. 实现：基于索引实现，在Innodb中我们的锁都是作用在索引上面的，当我们的SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）
3. 分类:
   • 基于锁的属性分类：共享锁、排他锁。
   • 基于锁的粒度分类：表锁、行锁、记录锁、间隙锁、临键锁。
   • 基于锁的状态分类：意向共享锁、意向排它锁。
4. 属性锁
   • 共享锁(Share Lock)读锁：当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。避免出现重复读
   • 排他锁（eXclusive Lock）写锁：当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁
     避免了出现脏数据和脏读的问题。
5. 粒度锁
   • 表锁：上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；粒度大，加锁简单，容易冲突
   • 行锁：上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高；
   • 记录锁(Record Lock)属于行锁：事务在加锁后锁住的只是表的某一条记录。可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。
   • 间隙锁(Gap Lock)属于行锁：
     1. 概念：事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。防止幻读问题
     2. 实现原理：数据库是通过行锁和间隙锁共同组成的（next-key lock），来实现的
   • 临键锁(Next-Key Lock)属于行锁：记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。避免了在范围查询时出现脏读、重复读、幻读问题
6. 状态锁：
   • 包括意向共享锁和意向排它锁，都是描述是否可以对某一个表进行加表锁的状态当一个事务试图对整个表进行加锁（共享锁或排它锁）之前，首先需要获得对应类型的意向锁（意向共享锁或意向共享锁）
   • 意向共享锁：当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。
   • 意向排他锁：当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。
   • 为什么需要意向锁：如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是我们的意向锁。
7. 乐观锁实现
   • 版本号控制：
     1. 为表中加一个 version 字段；
     2. 当读取数据时，连同这个 version 字段一起读出；
     3. 数据每更新一次就将此值加一；
     4. 当提交更新时，判断数据库表中对应记录的当前版本号是否与之前取出来的版本号一致，如果一致则可以直接更新，如果不一致则表示是过期数据需要重试或者做其它操作（PS：这完完全全就是 CAS 的实现逻辑呀~）
   • 时间戳控制：：其原理和版本号控制差不多，也是在表中添加一个 timestamp 的时间戳字段，然后提交更新时判断数据库中对应记录的当前时间戳是否与之前取出来的时间戳一致，一致就更新，不一致就重试。
8. 悲观锁实现：select…for update，通过开启排他锁的方式实现了悲观锁；select ... lock in share mode（加共享锁）

第四部分 数据库三范式

1. 第一范式：列都是不可再分，数据库表中的字段都是单一属性的，即属性应该是不可再分的；
2. 第二范式：每个表只描述一件事情，满足第一范式，并且表中非主键列不存在对主键的部分依赖
   • 第一步：找出数据表中所有的码。
   • 第二步：根据第一步所得到的码，找出所有的主属性。
   • 第三步：数据表中，除去所有的主属性，剩下的就都是非主属性了。
   • 第四步：查看是否存在非主属性对码的部分函数依赖。
3. 第三范式：满足第二范式，并且表中的列不存在对非主键列的传递依赖
4. 函数依赖：若在一张表中，在属性X确定的情况下，必定能确定属性Y的值，也就是说在一张表中，不存在任意两条属性，他们属性X的值相同，但属性Y的值不同。那么就可以说Y函数依赖于X，写作X->Y。函数依赖分为完全函数依赖、部分函数依赖、传递函数依赖。
5. 码：假设K为某个表中的一个属性或者属性组，若除K之外的所有属性都完全函数依赖于K，那么我们称K为候选码，简称码。

第五部分 主从复制

1. 目的：解决mysql的单点故障问题
2. 组件：（1）主服务器master：负责写；（2）从服务器slave：负责读；（3）三个线程：master（binlog dump thread）、slave（I/O thread 和 SQL thread）
3. 流程：
   • 数据库有更新，binlog dump thread将更新写入binlog文件，并通知slave。
   • I/O thread请求master，返回binlog的名称、更新位置和binlog文件位置的副本。然后将binlog保存在中继日志relay log中。
   • SQL thread将更新内容同步到salve数据库中。
4. 策略：
   • 同步策略：master等待所有slave回应后提交，影响性能
   • 半同步策略：master至少等待一个slave回应
   • 异步策略：master不必等待slave
   • 延迟策略：slave落后于master指定的时间

第六部分 MVCC-多版本并发控制

1. 功能：用来解决读-写冲突的无锁并发控制。

2. 原理：为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务戳关联，读操作只读事务开始前的数据库的快照。这样在读操作的时候不会阻塞写操作，写操作不会阻塞读操作的同时，也避免了脏读和不可重复读。

3. 基本概念：

   • 当前读：读取的记录是最新版本的，读取时须保证其他事务不能修改当前记录，因此需要对读取的记录进行加锁
   • 快照读：类似不加锁的select操作就是快照读；前提是隔离级别不是串行级别，因为在串行隔离级别，快照读可以理解为当前读；主要解决了在不加锁的情况下也可以进行读取，降低了锁开销；基于多版本并发控制，所以使用快照读读取的记录并不一定是最新记录

4. 实现：用来解决读写冲突。它主要由隐藏字段，undolog日志，read-view来配合完成的

   • 事务版本号：每次事务开启前都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。

   • 隐藏字段：

     1. DB_TRX_ID: 事务ID，记录的是当前事务在做INSERT或UPDATE语句操作时的事务ID；
     2. DB_ROLL_PTR：指向上一个版本数据在undo log 里的位置指针，回滚指针，通过它可以将不同的版本串联起来，形成版本链；
     3. DB_ROW_ID: 隐藏ID ，当创建表没有合适的索引作为聚集索引时，会用该隐藏ID创建聚集索引;

   • undolog日志：undolog日志存储某条记录的所有操作，以链表方式将各个版本进行串联起来

   • read-view(一致性视图)：

     1. 当事务第一次执行查询sql时会生成一致性视图read-view，它由执行查询时所有未提交事务ID数组(数组里最小的ID为min_id)和已创建的最大事务id(max_id)组成，查询的数据结果需要跟read-view做比较从而得到快照结果
     2. 匹配条件：
        • 如果落在绿色部分(trx_id<min_id)，表示这个版本是已提交的事物生成的，这个数据是可见的；
        • 如果落在红色部分(trx_id>max)，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
        • 如果落在黄色部分(min_id<=trx_id<=max_id)，那就包括两种情况： 如果这个版本的事务ID在ReadView的未提交事务数组中，表示这个版本是由还未提交的事务生成的，那么就是不可见的； 如果这个版本的事务ID不在ReadView的未提交事务数组中，表示这个版本是已经提交了的事务生成的，那么是可见的。
        • 不满足read view条件时候，从undo log里面获取数据：当数据的事务ID不满足read view条件时候，从undo log里面获取数据的历史版本，然后数据历史版本事务号回头再来和read view 条件匹配 ，直到找到一条满足条件的历史数据，或者找不到则返回空结果；

5. 参考链接：

   • https://blog.csdn.net/dingjianmin/article/details/121722287
   • https://www.jianshu.com/p/398d788e1083
   • https://blog.csdn.net/qq_36963950/article/details/118072103

第七部分 存储引擎

一、 InnoDB

1. 特点：
   • 支持事务，支持4个事务隔离级别；MySQL从5.5.5版本开始，默认的存储引擎为 InnoDB
   • 读写阻塞与事务隔离级别相关，能非常高效的缓存索引和数据，表与主键以簇的方式存储，支持分区、表空间，类似oracle数据库，支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
   • 对硬件资源要求还是比较高的场合，行级锁定，但是全表扫描仍然会是表级锁定，如update table set a=1 where user like ‘%lic%’;
   • InnoDB 中不保存表的行数，如 select count() from table; 时，InnoDB 需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。需要注意的是，当 count()语句包含 where 条件时 MyISAM 也需要扫描整个表，对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立组合索引

二、MyIsam

1. MyISAM的特点：
   • MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的
   • 访问速度快，对事务完整性没有要求，MyISAM 适合查询、插入为主的应用
   • MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：.frm 文件存储表结构的定义；数据文件的扩展名为 .MYD (MYData)；索引文件的扩展名是 .MYI (MYIndex)
   • 表级锁定形式，数据在更新时锁定整个表；数据库在读写过程中相互阻塞，会在数据写入的过程阻塞用户数据的读取，也会在数据读取的过程中阻塞用户的数据写入；数据单独写入或读取，速度过程较快且占用资源相对少
2. MyISAM适用的生产场景
   • 公司业务不需要事务的支持
   • 单方面读取或写入数据比较多的业务
   • MyISAM存储引擎数据读写都比较频繁场景不适合
   • 使用读写并发访问相对较低的业务
   • 数据修改相对较少的业务
   • 对数据业务一致性要求不是非常高的业务
   • 服务器硬件资源相对比较差

三、区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

8. InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

9. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

   • Innodb：frm是表定义文件，ibd是数据文件

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

参考链接：

• https://blog.csdn.net/qq_35642036/article/details/82820178

第八部分 SQL

一、语法

1. limit：强制 SELECT 语句返回指定的记录数

2. offset：去除前几条数据

3. ifnull(expr1,expr2)：假如expr1不为NULL，则IFNULL()的返回值为expr1; 否则其返回值为expr2。

4. concat(str1, str2,...)：将多个字符串连接成一个字符串

5. UCASE() 函数把字段的值转换为大写。

6. LCASE() 函数把字段的值转换为小写。

7. LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH) ：LEFT、RIGHT函数返回ARG最左边、右边的LENGTH个字符串

8. SUBSTR (str, pos)：由 中，选出所有从第 位置开始的字元。

9. like：通配符匹配

10. rlike：正则匹配

11. rank() over(ORDER BY Score desc)：按照某字段的排序结果添加排名，但它是跳跃的、间断的排名，例如两个并列第一名后，下一个是第三名。

12. row_number() over(ORDER BY Score desc)：将某字段按照顺序依次添加行号。如1、2、3、4

13. dense_rank() over(ORDER BY Score desc)：排序数字是连续的、不间断。当有相同的分数时，它们的排名结果是并列的，例如，1、2、2、3、4。

二、错误的题目

1. 简单：
   • 1873计算特殊奖金
   • 178分数排名
   • 180连续出现的数字
   • 184部门工资最高的员工
2. 未解出：
   • 608树节点：对每种节点都做不同判断，采用union/case when/if 控制流程
   • 1158市场分析 I：先计算每个用户符合要求的订单数量，然后联合用户表
   • 1393股票的资本损益：使用case when控制流程
   • SQL：表A和表B，如果同时插入一条数据，如果要保证事务的一致性，要么同时成功，要么同时失败，代码在JDBC中应该怎么写？
   • SQL:有一张学生课程表，里面记录学生各门课程的分数，用一条SQL将每门课程的分数都大于80分，将这些学生都找出来？
   • 如果一个MySQL表中有 a,b,c,d,e 5个字段，在a,b,c,d四个字段上建立联合索引。如果在查询时where a=1 and b=2 and c>3 and d=4;会走哪些索引？

第九部分 数据库分库分表

一、概念

1. 水平分库：将一个库中的数据拆分到多个库中。 结果：每个库的结构都一样；每个库的数据都不一样，没有交集；所有库的并集是全量数据；
2. 水平分表：以字段为依据，按照一定策略(hash、range等)，将一个表中的数据拆分到多个表中。 结果：每个表的结构都一样；每个表的数据都不一样，没有交集；所有表的并集是全量数据；
3. 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。 结果：每个库的结构都不一样；每个库的数据也不一样，没有交集；所有库的并集是全量数据；
4. 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表(主表和扩展表)中。 结果：每个表的结构都不一样；每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；

二、几种常用的分库分表的策略

1. HASH取模：假设有用户表user,将其分成3个表user0,user1,user2.路由规则是对3取模,当uid=1时,对应到的是user1,uid=2时,对应的是user2.
2. 范围分片：从1-10000一个表,10001-20000一个表。
3. 地理位置分片：华南区一个表,华北一个表。
4. 时间分片：按月分片，按季度分片等等,可以做到冷热数据。

三、分库分表后引入的问题

1. 分布式事务问题：如果我们做了垂直分库或者水平分库以后,就必然会涉及到跨库执行SQL的问题,这样就引发了互联网界的老大难问题-"分布式事务"。
   • 使用分布式事务中间件
   • 使用MySQL自带的针对跨库的事务一致性方案(XA),不过性能要比单库的慢10倍左右。
   • 能否避免掉跨库操作(比如将用户和商品放在同一个库中)
2. 跨库join的问题：分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
   • 全局表：基础数据，所有库都拷贝一份。
   • 字段冗余：这样有些字段就不用join去查询了。
   • 系统层组装：分别查询出所有，然后组装起来，较复杂。
3. 横向扩容的问题：当我们使用HASH取模做分表的时候,针对数据量的递增,可能需要动态的增加表,此时就需要考虑因为reHash导致数据迁移的问题。
4. 结果集合并、排序的问题：因为我们是将数据分散存储到不同的库、表里的,当我们查询指定数据列表时,数据来源于不同的子库或者子表,就必然会引发结果集合并、排序的问题。如果每次查询都需要排序、合并等操作,性能肯定会受非常大的影响。走缓存可能一条路!

第十部分 MYSQL面试题

1. 乐观锁和悲观锁的优缺点和应用场景是什么？

   • 悲观锁：每次请求都会先对数据进行加锁，然后进行数据操作，最后再解锁
     1. 可以完全保证数据的独占性和正确性
     2. 加锁释放锁的过程会造成消耗，所以性能不高
   • 乐观锁：操作数据时不会对操作的数据进行加锁，到数据提交的时候才验证数据是否存在冲突
     1. 多个任务可以并行的对数据进行操作
     2. 在并发非常高的情况下，会导致大量的请求冲突，降低性能

2. 数据库是怎么使用索引的？底层实现？

3. Innodb 的索引数据结构，回表机制，和 mysiam 的区别

4. 事务的 ACID 特性，事务隔离级别，解决什么问题

5. 对数据库事务是怎么理解的？数据库事务的隔离级别？分别介绍下这几种事务的隔离级别的使用场景或存在的问题？

6. 脏读和幻读的区别？什么情况下会出现幻读？

7. MySQL的隔离级别？串行化级别是怎么实现的？

8. 聚集索引和非聚集索引的区别？

9. MySQL用的是哪个版本？用的哪个引擎？InnoDB和MyISAM的区别？

10. 数据库中的多表连接有哪几种？有什么区别？数据库中in 和exists 的用法的区别？

11. MySQL中怎么分页查找？

12. 如果数据量很大的情况下上亿条，还能用limit这种方式进行分页查找吗？

13. 如果要实现在大数据量的情况下的分页，该怎么设计？

14. MVCC的原理？

15. 问一点数据库的索引有什么用？为什么用B+tree？

16. 数据库事务隔离怎么实现的：上面知识点有问题

17. mysql索引失效的情况

18. 一条sql执行过长的时间，你如何优化，从哪些方面入手？

    • 查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等
    • 优化索引结构，看是否可以适当添加索引
    • 数量大的表，可以考虑进行分库/分表（如交易流水表）
    • 数据库主从分离，读写分离
    • explain分析sql语句，查看执行计划，优化sql
    • 查看mysql执行日志，分析是否有其他方面的问题

19. 数据库索引重构过程是什么？

20. 数据库是如何实现分页查询？

21. 数据库为什么要读写分离？

22. explain命令？

    • 作用：模拟Mysql优化器是如何执行SQL查询语句，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
    • explain命令输出的结果：10列：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
    • 参考：https://blog.csdn.net/qf2019/article/details/119801933

23. 主键索引和普通索引有什么区别?

    • 普通索引是最基本的索引类型，没有任何限制，值可以为空，仅加速查询。普通索引是可以重复的，一个表中可以有多个普通索引。
    • 主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；索引列的所有值都只能出现一次，即必须唯一。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。

24. 多个表经常被级联查询，该如何优化

25. 对于联合索引(col1,col2,col3)，查询语句SELECT * FROM test WHERE col2=2;是否能够触发索引？

    • 结果：可以触发索引
    • 效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以，上述语句会触发索引。
    • 参考：https://www.jb51.net/article/142840.htm

26. sql：每个用户最大连续登录天数

27. InnoDB为什么推荐使用自增ID作为主键？

    • 自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

28. 什么情况下会导致锁表？

    • 发生位置：锁表发生在insert update 、delete 中；
    • 原理：数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生commite 或者 回滚 或者退出数据库用户；
    • 原因
      1. 第一、 A程序执行了对 tableA 的 insert ，并还未 commite时，B程序也对tableA 进行insert 则此时会发生资源正忙的异常 就是锁表
      2. 第二、锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu 和i/o 分配原则）
    • 减少锁表的概率：减少insert 、update 、delete 语句执行 到 commite 之间的时间。具体点批量执行改为单个执行、优化sql自身的非执行速度 如果异常对事物进行回滚

29. char和varchar区别？

    • 最大长度：char最大长度是255字符，varchar最大长度是65535个字节。
    • 定长：char是定长的，不足的部分用隐藏空格填充，varchar是不定长的。
    • 空间使用：char会浪费空间，varchar会更加节省空间。
    • 查找效率：char查找效率会很高，varchar查找效率会更低。
    • 尾部空格：char插入时可省略，vaechar插入时不会省略，查找时省略。

30. RR级别能完全解决幻读吗？

31. where和having的区别？

    • 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接而 HAVING 是先连接后筛选。在关联查询中，WHERE比HAVING更高效
    • WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件。HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

32. 有1亿数据，走主键索引，mysql底层 IO次数是多少？

33. drop、delete、trancate是不是事务操作？

34. jdbc连接数据库的步骤？

    1、加载jdbc驱动程序；2、创建数据库的连接；3、创建preparedStatement；4、执行SQL语句；5、遍历结果集；6、处理异常，关闭JDBC对象资源。

    • 准备四个参数：（1）user用户名（2）password密码（3）URL定义了连接数据库时的协议、子协议、数据源标识，它们之间用冒号隔开。 书写形式： 协议：子协议：数据源标识；（4）driverClass连接数据库所需的驱动。
    • 参考链接;https://www.php.cn/java/guide/474652.html

35. 大量并发查询用户商品信息，MySQL压力大查询慢，保证速度怎么优化方案（加缓存、限流和消息队列变慢不行、主从集群、冷热数据分离等）