CREATE TABLE 表名 (
       主键字段名 数据类型 [完整性约束条件]  PRIMARY KEY,
       字段名 数据类型 [完整性约束条件],
       [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY  [索引名](字段名1 [(长度)] [ASC | DESC])
);

注解：
    UNIQUE：可选。表示索引为唯一性索引。
    FULLTEXT；可选。表示索引为全文索引。
    SPATIAL：可选。表示索引为空间索引。
    INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
    索引名：可选。给创建的索引取一个新名称。
    字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
    长度：可选。指索引的长度，必须是字符串类型才可以使用。
    ASC：可选。表示升序排列。
    DESC：可选。表示降序排列。

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON  表名(字段名)

ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL | INDEX | KEY | PRIMARY KEY] 索引名(字段名)

② 主键索引模型图，主键索引的叶子节点保存的是一个指针，指针指向 *.myd 文件中其中的一条数据。

③ 非主键索引模型图，非主键索引的叶子节点保存的也是一个指针，指针指向 *.myd 文件中其中的一条数据。

2、innodb存储引擎
① 它有两种文件组成，.frm (保存表的定义信息)、.ibd (保存索引和数据信息)
② 主键索引模型图，主键索引的叶子节点保存的是真正的数据，当索引查到叶子节点的时候就可以直接取出真正的数据。

③ 非主键索引模型图，非主键索引的叶子节点保存的主键的值，当拿到主键值时，回到主键索引中(回表)，依次查找，取出真正的数据。

1、mysql为什么不用二叉树、平衡二叉树？
① 二叉树有一种极端情况，假如从小到大依次向树中插入的情况就会形成一个链表的形式，查询最后一条数据的时候就要遍历整个链表。
② 平衡二叉树虽然避免了链表的极端情况，但是一个节点只能保存一个数据，保存的数据比较少，I/O操作就会增多。另外，每次增删之后，都要进行平衡，会降低效率。

2、mysql为什么用b+tree,不用b-tree？
① b+tree 叶子节点之间有指针关联，做排序和范围查找时会很方便，效率也会更好高，它不需要查询遍历所有节点，基于索引的扫表会更优，基于索引的排序也会更优
② 非叶子节点不保存数据信息，只保存标识和指针信息，这个每个非叶子节点(page)保存的数据就会很多，这样树的层级就很低，减少了I/O操作。

3、mysql为什么不建议用uuid当主键，而建议主键ID是自增的，和b+tree有什么关系？
① uuid占用更多的存储空间，uuid是一个128位的数字，通常以36个字符的字符串形式表示。相比之下，整型主键只需要4个字节即可表示。使用uuid作为主键会占用更多的存储空间，导致索引和数据的存储变得更加庞大。
② uuid会导致索引性能下降， MySQL写入数据时，创建索引是依次从小到大创建的，会把数据存放到索引页中。使用uuid作为主键，新行的主键值不一定比之前的主键值大，所以innoDb无法做到总是把新行插入到索引的最后，而需要为新行寻找合适的位置来分配新的空间（因为是B+树方式存储的）。要分配新的空间，就要知道应该分到哪个页。如果用自增主键等，直接顺序增加在后面。而uuid就需要排序后这儿插一个，哪儿插一个。不够的话，还要页分裂，无法预测其值的范围，导致索引的分裂和碎片化，进一步降低索引的性能，增加树的深度。

4、mysql中的聚集索引，非聚集索引(稀疏索引)如何理解？
① 在innodb中，主键索引就是聚集索引，非主键索引就是非聚集索引。索引和数据存储在一起的就是聚集索引，不存在一起的就是非聚集索引
② myisam只有非聚集索引

5、为什么不建议写 select * from 进行查询？
① 避免回表，减少一次查询 , 那什么是回表呢？其实就是innodb引擎下非主键索引的叶子节点保存的主键的值，当拿到主键值时，回到主键索引中，依次查找，取出真正的数据，就叫回表，如下图所示。

② select id，name from 为什么可以避免回表呢？

如果建立了id主键索引和name索引，只需要id,name数据，而sql 这样写(select * from table where name =? ) 需要回表，只能查到id和name，其他字段需要回表查询。

如果sql这样写 (select id,name from table where name =? ) 就避免了回表。也是索引覆盖完全的结果。

6、 like 'aaa%' 一定会用到索引吗？最左匹配原则怎么理解？

① 假如创建了name,age,address的索引，b+tree 建立索引是严格按照从左到右依次建立的。
② select * from user where name=? and age=? and address=? 查询方式是首先把符合的name数据拿出来，再从符合name数据基础上找出符合age的，最后再找出符合address的数据。从左到右依次查询的，用到了name、age、address 索引，这就是最左匹配原则。
③ select * from user where name=? ，只用到了name索引。
④ select * from user where name=? and address=? ，也只用到了name索引。
⑤ like 'aaa%' 用到了索引， 而 like '%aaa' 没用到索引。

7、为什么innodb引擎要求一定要建立主键索引？
① 主键索引下面直接保存的数据，非主键索引下面保存的是标识，之后还要回表到主键索引查询数据，所以一定要建立主键索引。

二、MVCC(多版本并发控制)

① 在了解MVCC之前，先了解一下什么是当前读和快照读。undo log 是回滚日志。其中 select 触发的快照读，而 update、insert、delete 以及select ... lock in share mode (共享锁) 、select ... for update(排他锁)都是 当前读。

② MVCC机制下的四种隔离级别,mysql默认的隔离级别是可重复读(简称RR)

mysql 如何在不同的隔离级别下获取数据呢?下面我们来介绍一下MVCC的实现原理

③ MVCC实现原理

mysql对于使用InnoDB存储引擎的表，该表的聚簇索引记录中都包含几个必要的隐藏字段。

1、如上图所示，当第一次插入数据时，同时会插入事务ID、 隐藏主键 、 回滚指针为null;
2、当事务二修改name时，会先在undolog中保存一份原始数据，然后修改name，同时修改隐藏字段 事务ID、 隐藏主键 、 回滚指针为修改前的undolog地址。
3、当事务三修改age时,同理，也保存一份数据到undolog，然后修改age,同时修改隐藏字段 事务ID、 隐藏主键 、 回滚指针为修改前的undolog地址。
4、当事务四获取数据，是获取到哪条数据呢？其实不一定，因为需要判断一下版本链中的哪个版本是当前事务可见的，这时候就引入了ReadView概念。

④ ReadView (事务在快照读时产生的读视图) 保存了以下3个字段

一、判断规则：
1、比较DB_TRX_ID和up_limit id，如果小于，则当前事务能看到DB_TRX_ID的记录，如果大于和等
于，则进入下一个判断。

2、比较DB_TRX_ID和low_limit_id，如果大于等于则代表DB_TRX_ID的记录在readview生成后出现的，那么对于当前事务不可见。如果小于，则进入下一个判断。

3、判断DB_TRX_ID是否在活跃事务中，如果在，代表readview生成时，事务还在活跃状态，修改的数据当前的事务是看不到的，如果不在，说明事务在readview之前就commit了，那么修改的结果就是可见的。

二、不同隔离级别生成快照读的时间不同

1、RC隔离级别 : 每次进行快照读时都生成读视图
2、RR隔离级别 : 只有第一次生成读视图，之后每次都使用第一次时的读视图 (非必然情况，RR隔离级别下的幻读)

三、在RR隔离级别，我们举例说明一下上面的规则

1、第一种情况

事务2可见事务4 更新的数据

· DB_TRX_ID=4 > up_limit_id=1 (进入下一个判断)
· DB_TRX_ID=4 < low_limit_id=5 (进入下一个判断)
· DB_TRX_ID=4 不在 trx_list活跃列表[1,2,3]中，说明事务2可见事务4更新的数据。

2、第二种情况

① 事务2不可见事务4 更新的数据,事务2第一次查询生成读视图，DB_TRX_ID=0，当事务4更新并提交了之后，DB_TRX_ID=4。

· DB_TRX_ID=4 > up_limit_id=1 (进入下一个判断)
· DB_TRX_ID=4 < low_limit_id=5 (进入下一个判断)
· DB_TRX_ID=4 在 trx_list活跃列表[1,2,3,4]中，说明事务2不可见事务4更新的数据。

四、幻读 （同一个事务中，不同的时间，两次相同的查询获取到的数据不同）

1、幻读是一种数据库事务处理中的现象，特别是在使用可重复读（Repeatable Read）隔离级别时。当一个事务（事务A）读取并锁定了某些数据行，而另一个事务（事务B）在事务A之后修改了这些数据行，并提交了修改，那么事务A在再次读取相同的记录时，可能会发现多出了一些原本不存在的行，这给人一种数据行凭空出现的幻觉。

2、为了解决幻读问题，数据库管理系统（DBMS）提供了多种锁机制，如范围锁（Range Lock）和间隙锁（Gap Lock）。范围锁用于锁定一个检索范围，确保在这个范围内的数据行只能被读，而不能被修改或删除。间隙锁则用于锁定两个数据行之间的间隙，防止新数据行插入到这些间隙中。这些锁机制的组合被称为next-key lock，它能够确保事务A在读取时只能看到事务A开始读取时存在的行，而不能看到之后新插入的行。

3、在数据库定义的四种隔离级别中，最高隔离级别SERIALIZABLE_READ可以保证不出现幻读的问题。而Repeatable Read（RR）隔离级别则通过锁定读取到的记录和范围来防止幻读现象。

4、如何避免幻读？

一、引入了间隙锁和next-key lock机制，去解决幻读问题。为了更清晰的说明这两种锁如何解决幻读的，我们来举一个例子辅助了解。

① 假设此刻存在这样一个b+tree的索引结构，这个索引里面有4个索引元素分别是1，4，7，10，当我们通过主键索引查询一条数据并且对这条数据通过for update加锁，这时候会产生一个记录锁(行锁)，锁定id=1的这个索引，被锁定的记录在锁释放之前其他事务是无法对这一条记录做任何操作的。

② 前面我们说过，对幻读的定义，幻读是指在同一个事务中，前后两次查询相同的范围的时候，得到的结果不一致。innodb 引擎要解决幻读问题，必须要保证一个点，就是如果一个事务通过这样语句1(如下图)进行锁定的时候，另一个事务再执行语句2(如下图) 时需要被阻塞，直到前面获得锁的事务被释放，所以在innodb 引擎中设计了一种间隙锁，它的功能是锁定一段范围内的索引记录，如下图所示。其他事务对这个开区间的数据进行插入更新删除都会被锁住。

③ next-key lock (临界锁) 机制相当间隙锁 和 记录锁(行锁) 的合集, 记录锁锁定存在记录的行，间隙锁锁住的是记录行之间的间隙，next-key lock 锁住的是两者的和，是一个左开右闭区间的数据，锁定的是多个索引区间。

④ 虽然mysql在innodb引擎下通过间隙锁解决了幻读的问题，但是加锁之后一定会影响到并发的性能，因此对于性能较高的业务场景，我们可以把隔离级别设置为RC。对于数据精度较高的业务场景，我们可以把隔离级别设置为RR。根据不同的业务场景设置不同的隔离级别，能有效的提高数据库的性能。

五、redolog 和 binlog是什么？为什么有了redolog还需要binlog呢？

1、redolog 是用来保证事务的持久性的，保证数据不会丢失的。redolog 是innodb独有的，binlog是整个mysql服务层有的。

2、二阶段提交模型图

六、事务的四大特性实现

一、什么是读写分离?

读写分离，基本的原理是让主数据库处理事务性增、改、删操作( INSERT、UPDATE、DELETE) ，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

二、为什么要读写分离以及什么时候要读写分离?

1、写操作锁表，影响读操作，影响业务。
2、如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

三、主从复制

1、 数据更安全：做了数据冗余，不会因为单台服务器的宕机而丢失数据
2、性能大大提升：一主多从，不同用户从不同数据库读取，性能提升
3、 扩展性更优：流量增大时，避免因为I/O操作增多，单机出险瓶颈。可以方便的增加从服务器并且不影响系统使用
4、 负载均衡：一主多从相当于分担了主机任务，做了负载均衡
5、数据的备份

四、主从复制的原理图

1、当master节点接收到一个用户写请求时，这个写请求可能是增删改操作，此时会把写请求的操作都记录到binlog日志中。

2、首先slave节点通过配置信息连接到master节点上，当slave节点连接到master节点上时，master节点会为每一个slave节点分别创建一个binlog dump线程，用于向每个slave节点发送binlog日志。

3、此时，binlog dump线程会读取master节点上的binlog日志，然后将binlog日志发送给slave节点上的I/O线程。

4、slave几点上的I/O线程接收到binlog日之后，会将文件名、路径、密码等信息保存到master.info中，会将binlog日志先写入到本地的relaylog中，relaylog中就保存了master的binlog日志。

5、最后，slave节点上的SQL线程会读取relaylog中的biinlog日志，将其解析成具体的增删改操作，把这些在master节点上进行过的操作，重新在slave节点上也重做一遍，打到数据还原的效果，这样就可以保证master节点和slave节点的数据一致性了。

应用场景

• 高并发、低数据量

  • 如果系统面临的并发请求量很大，但需要处理的数据量相对较少，可以考虑采用分库策略，将数据分散存储在多个数据库中，以提高系统的并发处理能力。

• 低并发、高数据量

  • 如果系统的并发请求量不高，但需要处理的数据量很大，可以考虑采用分表策略，将数据按照一定的规则分布在多个表中，以提高查询和数据管理的效率。

• 高并发、高数据量

  • 如果系统同时面临高并发请求和大量的数据处理需求，那么可以同时采用分库和分表策略，将数据分散存储在多个数据库和表中，以提高系统的整体性能和扩展性。

分库分表的方向和顺序

• 方向

  • 垂直
  • 水平

• 顺序

  • 在并发分库分表时，推荐按照先垂直分，再水平分的顺序进行。这种方式更简单，也更符合实际业务。

• 垂直分库

  • 垂直分库是将一个系统中的不同业务模块或功能拆分为独立的数据库，每个数据库专注于处理特定的业务数据。这样可以提高数据库的性能、可伸缩性和数据管理的效率。

  • 以下是一些垂直分库的示例场景：

    • 用户数据库：存储用户相关的信息，如用户 ID、用户名、密码、电子邮件等。
    • 商品数据库：存储商品相关的信息，如商品 ID、商品名称、价格、库存等。
    • 订单数据库：存储订单相关的信息，如订单 ID、订单日期、订单状态、订单详情等。

  • 通过将不同的业务模块或功能拆分为独立的数据库，可以实现以下优势：

    • 提高性能：每个数据库专注于处理特定的业务数据，可以减少数据的冗余和查询的复杂度，从而提高数据库的查询性能。
    • 可伸缩性：随着业务的增长，可以轻松地对特定的数据库进行扩容，而不会影响其他业务模块的性能。
    • 数据管理效率：每个数据库可以根据其特定的业务需求进行优化和管理，例如索引维护、数据备份等。
  • 需要注意的是，在进行垂直分库时，需要考虑数据库之间的关联关系和数据一致性。通常需要通过主键或外键来建立数据库之间的关联，并确保数据的一致性和正确性。此外，垂直分库可能会增加数据库架构的复杂性，需要合理设计数据库表结构和查询语句来优化查询性能。

• 垂直分表

  • 垂直分表是将一个表中的热点查询字段和非热点查询字段进行分表处理的方法。通过将热点字段和非热点字段分别存储在不同的表中，可以提高查询性能和数据管理的效率。
  • 垂直分表的原理是根据字段的访问频率和数据量来确定哪些字段应该被分离到单独的表中。热点查询字段通常是那些经常被查询、过滤或排序的字段，而非热点查询字段则是那些不经常被使用的字段。

  • 以下是一些垂直分表的示例场景：

    • 订单表：假设一个订单表包含了大量的订单信息，包括订单 ID、订单日期、客户 ID、商品 ID、商品数量、订单状态等字段。其中，订单 ID、订单日期和订单状态可能是热点查询字段，而其他字段可能不经常被查询。在这种情况下，可以将热点查询字段分离到一个单独的表中，如订单主表，而将其他非热点字段放在另一个表中，如订单详情表。
    • 用户表：一个用户表可能包含用户 ID、用户名、电子邮件、地址、电话号码等字段。其中，用户 ID 和用户名可能是热点查询字段，而其他字段可能不经常被查询。在这种情况下，可以将热点查询字段放在一个表中，如用户主表，而将其他非热点字段放在另一个表中，如用户详细信息表。
  • 通过垂直分表，可以将热点查询字段和非热点查询字段分别存储在不同的表中，从而提高查询性能。这样，在查询热点字段时，只需要访问热点表，而不需要扫描整个数据表，减少了 I/O 操作和数据传输量。同时，垂直分表还可以更好地管理和维护数据，例如对热点表进行更频繁的索引维护、数据备份等操作。
  • 需要注意的是，在进行垂直分表时，需要考虑表之间的关联关系和数据完整性。通常需要通过主键或外键来建立表之间的关联，并确保数据的一致性和正确性。此外，垂直分表可能会增加查询的复杂性，需要合理设计查询语句和索引来优化查询性能。

• 水平分表

  • 水平分表是指将一个数据量巨大的单表按照某种规则拆分为多个表，这些表仍然存储在同一个数据库中。水平分表可以提高查询性能、数据管理和维护的效率。

  • 以下是一些常见的水平分表方式：

    • 范围(RANGE)拆分：根据数据的范围将其分配到不同的表中。例如，可以将一个包含大量用户数据的表按照用户 ID 的范围拆分为多个表，如 0-10000 一个表，10001 到 20000 一个表。
    • HASH 取模：通过使用哈希函数对某一列的值进行取模运算，然后根据计算结果将数据分配到不同的表中。例如，可以对用户 ID 进行取模运算，将其分配到不同的表中。
    • 地理区域：根据数据的地理位置进行拆分。例如，可以将一个包含全国用户数据的表按照华北、东北、华东等区域进行拆分。
    • 时间拆分：根据数据的时间属性进行拆分。例如，可以将一个包含多年数据的表按照年份或月份拆分为多个表，将较旧的数据放在单独的表中。这种方式常用于实现冷热数据分离，因为较旧的数据通常查询频率较低。
  • 通过水平分表，可以将大表拆分为多个较小的表，从而提高查询性能、数据管理和维护的效率。同时，水平分表还可以更好地支持分布式数据库架构和大规模数据处理。

• 水平分库

  • 水平分库是将一个数据库中的数据按照某种规则拆分为多个数据库，每个数据库包含一部分数据。这样可以将数据分散到多个数据库中，提高数据库的负载能力和读写性能。

  • 以下是一些水平分库的示例场景：

    • 用户数据库：可以根据用户 ID 将用户数据分布到多个数据库中，每个数据库负责一部分用户的数据。
    • 商品数据库：可以根据商品 ID 将商品数据分布到多个数据库中，每个数据库负责一部分商品的数据。
    • 订单数据库：可以根据订单 ID 将订单数据分布到多个数据库中，每个数据库负责一部分订单的数据。

  • 通过将数据分散到多个数据库中，可以实现以下优势：

    • 提高负载能力：多个数据库可以分担负载，提高数据库的并发处理能力。
    • 提高读写性能：数据分散到多个数据库中，可以减少单个数据库的读写压力，提高读写性能。
    • 可伸缩性：随着数据量的增长，可以轻松地增加数据库的数量来扩容。
  • 需要注意的是，在进行水平分库时，需要考虑数据的分布规则和数据的一致性。通常需要通过主键或分布式 ID 来保证数据的唯一性和一致性。此外，水平分库可能会增加数据库之间的数据同步和事务处理的复杂性，需要合理设计数据同步机制和事务处理策略。
  • 在完成水平分库后，可以根据业务需求和数据量的增长情况，进一步进行水平分表，以提高数据库的性能和可伸缩性。