MySQL

树结构-总结

---

---

0 数据库基础知识——三范式、关系型

1 MySQL基本信息——锁机制、隔离级别、MVCC、Binlog日志、存储引擎、Memory、查询问题

2 数据类型——VARCHAR、CHAR、DATETIME、TIMESTAMP

3 索引——作用、分类、原则、失效情况

4 MySQL主从复制

5 MySQL的维护

-------------------------------------------------------------------

---

0 数据库基础知识

0.1 数据库三范式

参考文章
1. 第一范式(确保每列保持原子性)：数据库表中的所有字段值都是不可分解的原子值
比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式

2. 第二范式：确保表中的每列都和主键相关
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

3. 第三范式：确保每列都和主键列直接相关,而不是间接相关
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

---

0.2 关系型数据库和非关系型区别？优缺点？

1. 数据存储方式不同
   首先关系型数据库一般都有固定的表结构，并且需要通过DDL语句来修改表结构，不是很容易进行扩展，而非关系型数据库的存储机制就有很多了，比如基于文档的，K-V键值对的，还有基于图的等，对于数据的格式十分灵活没有固定的表结构，方便扩展，因此如果业务的数据结构并不是固定的或者经常变动比较大的，那么非关系型数据库是个好的选择

2. 扩展方式不同
   要支持更多并发量，SQL数据库是纵向扩展，也就是说提高处理能力，使用速度更快速的计算机，这样处理相同的数据集就更快了。因为数据存储在关系表中，操作的性能瓶颈可能涉及很多个表，这都需要通过提高计算机性能来客服。虽然SQL数据库有很大扩展空间，但最终肯定会达到纵向扩展的上限。
   而NoSQL数据库是横向扩展的，其扩展可以通过给资源池添加更多普通的数据库服务器(节点)来分担负载。

3. 对事务性的支持不同。
   如果数据操作需要高事务性或者复杂数据查询需要控制执行计划，那么传统的SQL数据库从性能和稳定性方面考虑是你的最佳选择。SQL数据库支持对事务原子性细粒度控制，并且易于回滚事务。

4. 数据一致性
   非关系型数据库一般强调的是数据最终一致性，而不没有像ACID一样强调数据的强一致性，从非关系型数据库中读到的有可能还是处于一个中间态的数据，因此如果你的业务对于数据的一致性要求很高，那么非关系型数据库并不一个很好的选择

• 优缺点

  • sql：复杂查询、支持事务
  • nosql：性能强（基于键值对，无需sql层解析）、存储格式灵活、易扩展

---

1 MySQL基本信息

1.1 逻辑架构

• 第一层是服务器层

  连接处理、授权认证、安全等功能。

• 第二层实现了 MySQL 核心服务功能：所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。

  SQL查询解析、分析、优化、缓存以及日期和时间等所有内置函数

• 第三层是存储引擎层：服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差异，使得差异对上层查询过程透明。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应上层服务器请求。

  负责 MySQL 中数据的存储和提取。

---

1.2 锁机制

1.2.1 读写锁——读共享，写排他

在处理并发读或写时，可以通过实现一个由两种类型组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁和排它锁，也叫读锁和写锁。读锁是共享的，相互不阻塞，多个客户在同一时刻可以同时读取同一个资源而不相互干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入并防止其他用户读取正在写入的同一资源。

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL 会通过锁定防止其他用户读取同一数据。写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但是读锁不能插入到写锁前面。

---

1.2.2 锁策略——表锁、行锁

表锁：是MySQL中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表，一个用户在对表进行写操作前需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获取读锁，读锁之间不相互阻塞。

行锁：可以最大程度地支持并发，同时也带来了最大开销。InnoDB 和 XtraDB 以及一些其他存储引擎实现了行锁。行锁只在存储引擎层实现，而服务器层没有实现。

---

1.2.3 数据库死锁如何解决 ？——回滚部分或全部事务，重新执行

死锁：是指多个事务在同一资源上相互占用并请求锁定对方占用的资源而导致恶性循环的现象。当多个事务试图以不同顺序锁定资源时就可能会产生死锁，多个事务同时锁定同一个资源时也会产生死锁。

为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，例如InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方法，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。InnoDB 目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。

死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型系统这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

---

1.3 事务、4个事务特性（成功执行要求）——一组原子性的SQL查询，原子性、隔离性、持久性、一致性

• 概念
  事务是一组原子性的 SQL 查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说事务内的语句要么全部执行成功，要么全部执行失败（原子性）。

• 事务特性

  ① 原子性 atomicity——同生共死
  一个事务在逻辑上是必须不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说不可能只执行其中的一部分。

  ② 一致性 consistency
  数据库总是从一个一致性的状态转换到另一个一致性的状态。

  ③ 隔离性 isolation
  针对并发事务而言，隔离性就是要隔离并发运行的多个事务之间的相互影响，一般来说一个事务所做的修改在最终提交以前，对其他事务是不可见的。

  ④ 持久性 durability
  一旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。

---

1.4 隔离级别

• 未提交读 READ UNCOMMITTED（少用）：即使没有被提交，对其他事务也是可见的

  • 会导致脏读、不可重复读、幻读
  • 脏读：事务可以读取其他事务修改完但未提交的数据。
  • 不可重复读：短时间内两次读取结果可能不一样
  • 幻读：事务两次读取多了一行

• 提交读 READ COMMITTED（不可重复读，多数数据库默认）

  • 多数数据库系统默认的隔离级别。
  • 存在不可重复读问题
  • 提交读满足了隔离性的简单定义：一个事务开始时只能"看见"已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前的任何修改对其他事务都是不可见的。也叫不可重复读，因为两次执行同样的查询可能会得到不同结果。

• 可重复读 REPEATABLE READ（MySQL默认的隔离级别）

• 可重复读解决了不可重复读的问题，保证了在同一个事务中多次读取同样的记录结果一致。
  但还是无法解决幻读，所谓幻读指的是当某个事务在读取某个范围内的记录时，会产生幻行。
  InnoDB 存储引擎通过多版本并发控制MVCC 解决幻读的问题。

• 可串行化 SERIALIZABLE（少用）

• 最高的隔离级别，通过强制事务串行执行，避免幻读。
• 可串行化会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，只有非常需要确保数据一致性且可以接受没有并发的情况下才考虑该级别。

---

1.5 MVCC（多版本并发控制）作用、实现原理？

• 作用
  MVCC 是多版本并发控制，把数据库的行锁与行的多个版本结合起来，实现并发控制。在很多情况下避免加锁，大都实现了非阻塞的读操作，写操作也只锁定必要的行，实现了多个事务并发下，读操作的非阻塞。

• Innodb的MVCC
  通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间。不过存储的不是实际的时间值而是系统版本号，每开始一个新的事务系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

  查询时，InnoDB会根据以下两个条件检查每行记录:
  a. InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.
  b. 行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除. 只有a,b同时满足的记录，才能返回作为查询结果.

• MVCC工作隔离级别要求：只能在 READ COMMITTED（提交读） 和 REPEATABLE READ（可重复读） 两个隔离级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而 SERIALIZABLE 则会对所有读取的行都加锁。

---

1.6 Binlog日志

**概念：**记录所有数据库表结构变更以及表数据修改的二进制日志。

作用：

• 可以用于数据丢失后的实时备份
• 用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步
• 用于数据库的基于时间点的还原。

🚩补充：为什么修改时先写日志、再写数据库？

保证数据库的可恢复性

日志文件能够用来进行事务故障恢复、系统故障恢复，并能够协助后备副本进行介质故障恢复。

当数据库文件毁坏后，可重新装入副本把数据库恢复到转储结束时刻的正确状态

再利用建立的日志文件，可以把已完成的事务进行重做处理，而对于故障发生时尚未完成的事务则进行撤消处理，这样不用运行应用程序就可把数据库恢复到故障前某一时刻的正确状态。

---

1.7 Mysql常见存储引擎

• InnoDB存储引擎

  InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL5.5.5之后，InnoDB 作为默认的存储引擎，InnoDB 主要特性有：

  • 支持事务
  • 灾难恢复性好：自动崩溃恢复
  • 基于聚簇索引：主键查询性能好
  • 数据存储格式平台独立（方便迁移）
  • 为处理巨大数据量的最大性能设计
  • 实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
  • 支持外键完整性约束。存储表中的数据时，每张表的存储都按逐渐顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID,并以此作为主键。
  • 被用在众多需要高性能的大型数据库站点上

• MyISAM存储引擎
  MyISAM 基于 ISAM 的存储引擎，并对其进行扩展。它是在Web、数据存储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。在 MySQL5.5.5 之前的版本中，MyISAM 是默认的存储引擎。MyISAM 主要特性有：

  • 不支持事务，支持全文索引
  • 使用表级锁，并发性差
  • 主机宕机后，MyISAM表易损坏，灾难恢复性不佳（需手动恢复）
  • 可以配合锁，实现操作系统下的复制备份、迁移
  • 只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
  • 数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
  • 可以把数据文件和索引文件放在不同目录
  • 使用 MyISAM 引擎创建数据库，将产生3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm 文件存储表定义，数据文件的扩展名为 .MYD（MYData），索引文件的扩展名是 .MYI（MYIndex）。

• MEMORY存储引擎
  MEMORY 存储引擎将表中的数据存储在内存中，为查询和引用其他表数据提供快速访问。MEMORY 主要特性有：

  • 使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈
  • 只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪费空间
  • 不支持TEXT、BLOB字段。当有些查询需要使用到临时表（使用的也是MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁盘的MyISAM表，严重降低性能
  • 由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错
  • 服务器重启后数据会丢失（不能持久化），复制维护时需要小心，适用于仅需要数据查询的情况
  • 支持哈希索引

---

1.8 Memory

如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，那么使用 Memory 表是非常有用的。Memory 表至少要比 MyISAM 表快一个数量级，因为所有数据都保存在内存，不需要磁盘 IO，Memory 表的结构在重启后会保留，但数据会丢失。

Memory 表适合的场景：查找或者映射表、缓存周期性聚合数据的结果、保存数据分析中产生的中间数据。

Memory 表，因此查找速度极快。虽然速度很快但还是无法取代传统的基于磁盘的表，Memory 表使用表级锁，因此并发写入的性能较低。它不支持 BLOB 和 TEXT 类型的列，并且每行的长度是固定的，所以即使指定了 VARCHAR 列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。

如果 MySQL 在执行查询的过程中需要使用临时表来保持中间结果，内部使用的临时表就是 Memory 表。如果中间结果太大超出了Memory 表的限制，或者含有 BLOB 或 TEXT 字段，临时表会转换成 MyISAM 表。

---

1.9 查询执行流程——查缓存、解析处理+生成执行计划、根据计划调存储引擎API查询

简单来说分为五步：
① 客户端发送一条查询给服务器。
② 服务器先检查查询缓存，如果命中了缓存则立刻返回存储在缓存中的结果，否则进入下一阶段。
③ 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
④ MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
⑤ 将结果返回给客户端。

🚩补充：怎么查询非主键索引字段？怎么避免回表查询？

• 查询方式
  在数据库中，存在主键(聚集)索引和普通索引。如果没有指定默认会生成一个聚集索引，在查询非聚集索引时，该索引数据结构的底层存储的是主键的值，因此会根据查询到的主键再做一次回表查询，在使用explain关键字时会出现 using index condition。
• 避免措施
  可以利用索引覆盖的原理，除了主键索引外，对要查询的其他字段添加普通索引即可。

---

2 数据类型

2.1 VARCHAR、CHAR

VARCHAR 用于存储可变字符串，是最常见的字符串数据类型。它比 CHAR 更节省空间，因为它仅使用必要的空间。VARCHAR 需要 1 或 2 个额外字节记录字符串长度，如果列的最大长度不大于 255 字节则只需要 1 字节。VARCHAR 不会删除末尾空格。

VARCHAR 适用场景：字符串列的最大长度比平均长度大很多、列的更新很少、使用了 UTF8 这种复杂字符集，每个字符都使用不同的字节数存储。

CHAR 是定长的，根据定义的字符串长度分配足够的空间。CHAR 会删除末尾空格。

CHAR 适合存储很短的字符串，或所有值都接近同一个长度，例如存储密码的 MD5 值。对于经常变更的数据，CHAR 也比 VARCHAR更好，因为定长的 CHAR 不容易产生碎片。对于非常短的列，CHAR 在存储空间上也更有效率，例如用 CHAR 来存储只有 Y 和 N 的值只需要一个字节，但是 VARCHAR 需要两个字节，因为还有一个记录长度的额外字节。

---

2.2 DATETIME、TIMESTAMP

DATETIME 能保存大范围的值，从 1001~9999 年，精度为秒。把日期和时间封装到了一个整数中，与时区无关，使用 8 字节存储空间。

TIMESTAMP 和 UNIX 时间戳相同，只使用 4 字节的存储空间，范围比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依赖于时区。

---

---

3 索引

3.1 索引作用——快速找到记录、减少扫描量、随机IO变顺序IO、避免排序和临时表

索引也叫键，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能很关键，尤其是当表中数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但数据量逐渐增大时，性能会急剧下降。

索引大大减少了服务器需要扫描的数据量、可以帮助服务器避免排序和临时表、可以将随机 IO 变成顺序 IO。但索引并不总是最好的工具，对于非常小的表，大部分情况下会采用全表扫描。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价也随之增长，这种情况下应该使用分区技术。

在MySQL中，首先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。索引可以包括一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能使用索引的最左前缀。

---

3.2 普通索引分类

3.2.1 MySQL 的 B-Tree 索引

大多数 MySQL 引擎都支持这种索引，但底层的存储引擎可能使用不同的存储结构，例如 NDB 使用 T-Tree，而 InnoDB 使用 B+ Tree。

B-Tree 通常意味着所有的值都是按顺序存储的，并且每个叶子页到根的距离相同。B-Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。叶子节点的指针指向的是被索引的数据，而不是其他的节点页。

B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列，例如索引为 (id,name,sex)，不能只使用 id 和 sex 而跳过 name。
• 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

🚩补充：B+树与B树的不同

（1）B+树非叶子节点不存在数据只存索引，B树非叶子节点存储数据
（2）B+树查询效率更高。B+树使用双向链表串连所有叶子节点，区间查询效率更高（因为所有数据都在B+树的叶子节点，扫描数据库 只需扫一遍叶子结点就行了），但是B树则需要通过中序遍历才能完成查询范围的查找。
（3）B+树查询效率更稳定。B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在根节点，这样就会造成查询的效率的不稳定
（4）B+树的磁盘读写代价更小。B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，通常B+树矮更胖，高度小，查询产生的I/O更少。

---

3.2.2 Hash 索引——速度快、排序类范围类不适用

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

只有 Memory 引擎显式支持哈希索引，这也是 Memory 引擎的默认索引类型。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引的速度非常快，但它也有一些限制：

• 哈希索引数据不是按照索引值顺序存储的，无法用于排序。
• 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如在数据列(a,b)上建立哈希索引，如果查询的列只有a就无法使用该索引。
• 哈希索引只支持等值比较查询，不支持任何范围查询。

---

3.2.3 自适应哈希索引——频繁索引被自动生成哈希索引

自适应哈希索引是 InnoDB 引擎的一个特殊功能，当它注意到某些索引值被使用的非常频繁时，会在内存中基于 B-Tree 索引之上再创键一个哈希索引，这样就让 B-Tree 索引也具有哈希索引的一些优点，比如快速哈希查找。这是一个完全自动的内部行为，用户无法控制或配置，但如果有必要可以关闭该功能。

---

3.2.4 空间索引——任意维度查询、无序前缀

MyISAM 表支持空间索引，可以用作地理数据存储。和 B-Tree 索引不同，这类索引无需前缀查询。空间索引会从所有维度来索引数据，查询时可以有效地使用任意维度来组合查询。必须使用 MySQL 的 GIS 即地理信息系统的相关函数来维护数据，但 MySQL 对 GIS 的支持并不完善，因此大部分人都不会使用这个特性。

---

3.2.5 全文索引——关键字（相似度查询）

通过数值比较、范围过滤等就可以完成绝大多数需要的查询，但如果希望通过关键字匹配进行查询，就需要基于相似度的查询，而不是精确的数值比较，全文索引就是为这种场景设计的。

MyISAM 的全文索引是一种特殊的 B-Tree 索引，一共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的"文档指针"。全文索引不会索引文档对象中的所有词语，它会根据规则过滤掉一些词语，例如停用词列表中的词都不会被索引。

---

3.2.6 聚簇索引——数据存储方式、同一个结构中保存了 B-Tree 索引和数据行、速度快

聚簇索引不是一种索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。当表有聚簇索引时，它的行数据实际上存放在索引的叶子页中，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点：

• ① 可以把相关数据保存在一起。
• ② 数据访问更快，聚簇索引将索引和数据保存在同一个 B-Tree 中，因此获取数据比非聚簇索引要更快。
• ③ 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

• ① 聚簇索引最大限度提高了 IO 密集型应用的性能，如果数据全部在内存中将会失去优势。
• ② 更新聚簇索引列的代价很高，因为会强制每个被更新的行移动到新位置。
• ③ 基于聚簇索引的表插入新行或主键被更新导致行移动时，可能导致页分裂，表会占用更多磁盘空间。
• ④ 当行稀疏或由于页分裂导致数据存储不连续时，全表扫描可能很慢。

🚩补充1：聚簇索引和非聚簇索引的区别？

• 聚簇索引与非聚簇索引的区别
  1）聚簇索引是将索引和整条记录（数据）存放在一起，找到索引就找到了记录；
  2）非聚簇索引只存储索引字段和记录所在的位置，通过索引找到记录所在的位置，然后再根据记录所在位置去获取记录。

• 两者的优缺点对比
  1）聚簇索引的查找记录要比非聚簇索引块，因为聚簇索引查找到索引就查找到了数据位置，而非聚簇索引查找到索引之后，根据记录的数据地址，再去查找数据；
  2）一个数据表只能有一个聚簇索引，但可以有多个非聚簇索引；
  3）聚簇索引和非聚簇索引都可以加快查询速度，但同时也都对写入速度会有影响；聚簇索引对写入的速度影响更大一些；

🚩补充2：数据库索引为啥只能有一个主键（聚簇索引）？

• 一个表只能有一个主键以确保其数据的唯一性
• 主键可以由一个或多个字段组成，也就是说以一个或多个字段建立一个索引，来确保数据的唯一性
• 因为数据的物理存放地址是根据聚集索引来的，所以肯定就只能有一个聚集索引

---

3.2.7 覆盖索引——索引中包含了所有需要查询的字段的值

覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值，不再需要根据索引回表查询数据。覆盖索引必须要存储索引列的值，因此 MySQL 只能使用 B-Tree 索引做覆盖索引。

优点：
-① 索引条目通常远小于数据行大小，可以极大减少数据访问量。
② 因为索引按照列值顺序存储，所以对于 IO 密集型防伪查询回避随机从磁盘读取每一行数据的 IO 少得多。
③ 由于 InnoDB 使用聚簇索引，覆盖索引对 InnoDB 很有帮助。InnoDB 的二级索引在叶子节点保存了行的主键值，如果二级主键能覆盖查询那么可以避免对主键索引的二次查询。

---

3.3 MySQL索引分类

MySQL索引包括普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

• 1、普通索引
  即不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。
• 2、唯一索引：如主键
  使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一。主键是一种特殊唯一索引。
• 3、全文索引
  使用FULLTEXT参数可以设置全文索引。全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上。查询数据量较大的字符串类型字段时，使用全文索引可以提高查询速度。注意：全文索引在默认情况下是对大小写字母不敏感的，可以通过使用二进制对索引的列进行排序以执行大小写敏感的全文索引。MySQL中只有MyISAM存储引擎支持全文索引
• 4、单列索引
  顾名思义，单列索引值对应一个字段的索引。可以包括上述的三种索引方式。应用该索引的条件只需要保证该索引值对应一个字段即可。
• 5、多列索引
  多列索引是在表的多个字段上创建一个索引。该索引只想创建时对应的多个字段，可以通过这几个字段进行查询。要想应用该索引，用户必须使用这些字段中的第一个字段。
• 6、空间索引
  使用SPATIAL参数可以设置空间索引。空间索引只能建立在控件数据类型（LINESTRING、POINT、GEOMETRY等）上，这样可以提高系统获取控件数据的效率。MySQL中只有MyISAM存储引擎支持空间索引，且该字段不能为空值。

---

3.4 索引原则

• 建立原则

  1.选择唯一性索引（如id）
  唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
  –
  2.为经常需要排序、分组和联合操作的字段建立索引
  经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
  –
  3.为高频查询条件字段建立索引
  如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
  –
  4.限制索引的数目
  索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
  –
  5.🌈尽量使用数据量少的索引
  如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
  –
  6.尽量使用前缀来索引
  如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
  –
  7.删除不再使用或者很少使用的索引
  表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
  –
  8.最左前缀匹配原则，非常重要的原则。
  mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  –
  9.尽量选择区分度高的列作为索引。
  区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录
  –
  10.索引列不能参与计算，保持列“干净”
  比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  –
  11.尽量的扩展索引，不要新建索引。
  比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
  –
  12.当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率

• 使用前缀索引

  索引列开始的部分字符，索引创建后也是使用硬盘来存储的，因此短索引可以提升索引访问的 IO 效率。对于 BLOB、TEXT 或很长的 VARCHAR 列必须使用前缀索引，MySQL 不允许索引这些列的完整长度。前缀索引是一种能使索引更小更快的有效方法 缺点是
  MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

• 选择合适的索引顺序

  当不需要考虑排序和分组时，将选择性最高（区分度最大）的列放在前面。索引的选择性是指不重复的索引值和数据表的记录总数之比，索引的选择性越高则查询效率越高，唯一索引的选择性是1，因此也可以使用唯一索引提升查询效率。

• 删除无用索引

  MySQL
  允许在相同列上创建多个索引，重复的索引需要单独维护，并且优化器在优化查询时也需要逐个考虑，这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免创建重复索引。如果创建了索引
  (A,B) 再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引，对于 B-Tree
  索引来说是冗余的。解决重复索引和冗余索引的方法就是删除这些索引。除了重复索引和冗余索引，可能还会有一些服务器永远不用的索引，也应该考虑删除。

🚩补充：索引最左匹配原则

• 1. 什么是最左匹配原则？
  最左优先，以最左边的为起点任何连续的索引都能匹配上，遇到范围查询(>、<、between、like)就会停止匹配。

  例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

• 2. 最左匹配原则的原理
  索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

  【例子】假如创建一个（a,b)的联合索引，那么它的索引树是这样的
  
  可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2 这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。
  例如
  a = 1 and b = 2：a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，
  而 a>1 and b=2：a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

---

3.5 哪些情况适合建立索引？哪些情况不适合？

• 哪些情况下适合建索引

  1. 频繁查询字段作为where条件语句查询的字段
  2. 表间关联字段需要建立索引，例如外键字段，student表中的classid, classes表中的schoolid 等
  3. 排序字段可以建立索引
  4. 分组字段可以建立索引，因为分组的前提是排序
  5. 统计字段可以建立索引，例如count(),max()

• 哪些情况下不适合建索引

  1. 频繁更新的字段不适合建立索引
  2. where条件中用不到的字段不适合建立索引
  3. 表数据比较少的不需要建索引
  4. 唯一性太差（区分度不高的）字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值
  5. 参与列计算的列不适合建索引

3.5 索引失效情况

如果索引列出现了隐式类型转换，则 MySQL 不会使用索引。

常见的情况：

• 在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使用索引。
• 如果 WHERE 条件中含有 OR，除非 OR 前使用了索引列而 OR 之后是非索引列，索引会失效。
• MySQL 不能在索引中执行 LIKE 操作，这是底层存储引擎 API 的限制，最左匹配的 LIKE 比较会被转换为简单的比较操作，但如果是以通配符开头的 LIKE 查询，存储引擎就无法做比较。这种情况下 MySQL 只能提取数据行的值而不是索引值来做比较。
• 如果查询中的列不是独立的，则 MySQL 不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
• 对于多个范围条件查询，MySQL 无法使用第一个范围列后面的其他索引列，对于多个等值查询则没有这种限制。
• 如果 MySQL 判断全表扫描比使用索引查询更快，则不会使用索引。
• 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

最左前缀特性：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

---

---

4 MySQL主从复制

4.1 作用

复制解决的问题

• ① 基本问题：让一台服务器的数据与其他服务器保持同步，一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。
• ② 其他问题：数据分布、负载均衡、备份、高可用性和故障切换、MySQL 升级测试。

复制解决的主库和备库之间可以有多种不同的组合方式，MySQL 支持两种复制方式：

• 基于行的复制：在 5.1 版本才被加进来。
• 基于语句的复制：也称为逻辑复制，从 MySQL 3.23 版本就已存在

这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。因此同一时刻备库的数据可能与主库存在不一致，并且无法包装主备之间的延迟。

MySQL 复制大部分是向后兼容的，新版本的服务器可以作为老版本服务器的备库，但是老版本不能作为新版本服务器的备库，因为它可能无法解析新版本所用的新特性或语法，另外所使用的二进制文件格式也可能不同。

---

4.2 步骤

• ① 主库写数据到日志：在主库上把数据更改记录到二进制日志中。

• ② 备库复制日志：备库将主库的日志复制到自己的中继日志中。

• ③ 备库读取日志数据：备库读取中继日志中的事件，将其重放到备库数据之上。

  详细步骤：
  （1）第一步是在主库上记录二进制日志，每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志，在记录二进制日志后，主库会告诉存储引擎可以提交事务了。

  （2）下一步，备库将主库的二进制日志复制到其本地的中继日志中。备库首先会启动一个工作的 IO 线程，IO线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储线程，这个线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库IO 线程会将接收到的事件记录到中继日志中。

  （3）备库的 SQL 线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当 SQL 线程追赶上 IO线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL 线程执行的时间也可以通过配置选项来决定是否写入其自己的二进制日志中。

---

5 MySQL的维护

一、备份数据

• 1）导出备份数据

  - mysqldump -用户名 -p 数据库 > [路径]dump_name.sql；# 备份整个数据库到dump_name.sql文件中
  - mysqldump -用户名 -p 数据库 数据表1,数据表2 > [路径]dump_name.sql；# 备份数据库中的某个表到dump_name.sql文件中
  - mysqldump -u用户名 -p --databases 数据库1,数据库2 > [路径]dump_name.sql；# 备份多个数据库到dump_name.sql文件中
  - mysqldump -u用户名 -p --all-databases > [路径]databases_name.sql；# 备份系统中所有数据库到databases_name.sql文件中
  

• 2）导入备份数据

  - mysql -u用户名 -p 数据库 < [路径]dump_name.sql；# 将备份的数据dump_name.text导入到MySQ服务器上，但请保证两台服务器是相通的，可以相互访问
  

二、进行数据库维护

• 1）分析表(更新索引的统计信息)：ANALYZE TABLE 数据表名称[,数据表名称..]

  - 分析并存储表的键值分布统计信息
  - 为执行查询提供更好的选择
  - 对使用InnoDB、NDB和MyISAM存储引擎的表生效
  - 支持分表
  - ANALYZE TABLE选项：ANALYZE [ NO_WRITE_TO_BINLOG | LOCAL ] TABLE 数据表名称[,数据表名称..]；不记录二进制日志
  

• 2）检查表(检查表的完整性)：CHECK TABLE 数据表名称[,数据表名称..]

  - 检查表结构和内容的完整性
  - 验证视图定义
  - 支持分区表
  - 对使用InnoDB、CSV、MyISAM和ARCHIVE存储引擎的表生效
  - CHECK TABLE选项：
  - CHECK TABLE 数据表名称[,数据表名称..] FOR UPGRADE；检查当前服务器的表是否工作
  - CHECK TABLE 数据表名称[,数据表名称..] QUICK；不扫描不正确链接的行
  - 如果CHECK TABLE发现使用InnoDB存储引擎的表有错误：
  - 服务器将会被关闭以防止错误传播
  - MySQL将会记录到错误到错误日志
  

• 3）校验表(报告表数据的一致性检测结果)：CHECKSUM TABLE 数据表名称[,数据表名称..]

  - 报告表的checksum
  - (该语句)用来验证表备份，回滚或者其它操作之前或之后内容是否一致
  - 读取整个表逐行进行校验
  - 默认选项EXTENDED提供这种行为
  - QUICK选项在MyISAM表上可用
  - MyISAM默认选项上为设置CHECKSUM=1；
  

• 4）修复表：REPAIR TABLE 数据表名称[,数据表名称..]

  - 修复可能已经损坏的MyISAM或ARCHIVE表
  - 不支持InnoDB
  - 优化过程中会锁定表
  - 支持分区表
  - REPAIR TABLE选项：
    - REPAIR TABLE 数据表名称 QUICK；仅仅修复索引树
    - REPAIR TABLE 数据表名称 EXTENDED；逐行创建索引（替代以排序方式一次创建一个索引）
    - REPAIR TABLE 数据表名称 USE_FRM；使用.FRM文件重新创建.MYI文件
    - REPAIR { NO_WRITE_TO_BINLOG | LOCAL }TABLE 数据表名称；不记录二进制日志
  

• 5）优化表：OPTIMZE TABLE 数据表名称[,数据表名称..]

  - 整理表的锁片
  - 重建表和释放未使用的空间以整理碎片
  - 优化过程中会锁定表
  - 更新索引统计信息
  - 在一张永久的，数据密集/完全填充？的表上是比较好的
  - 在InnoDB，MyISAM和ARCHIV表上生效
  - 支持分区表
  - OPTIMZE TABLE选项：OPTIMZE [ NO_WRITE_TO_BINLOG | LOCAL ] TABLE 数据表名称[,数据表名称..]；不记录二进制日志
  

• 6）返回信息

  1. Table：标示执行操作的表，
  2. Op：操作的名字(check、repair、analyze、optimze)
  3. Msg_type：提供一个成功或失败的指示，
  4. Msg_text：提供额外的信息；