Q1：MySQL 的逻辑架构了解吗？

  1. 第一层是服务器层，连接处理，授权认证，安全等功能。
  2. 第二层实现了 MySQL 核心服务功能，包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。
  3. 第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差异，使得差异对上层查询过程透明。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应上层服务器请求。

Q1.1 谈一谈mysql的优化

  1. 聊几个方向：
    1. 数据量大的优化
      1. 横向拓展【分库分表】
        1. 扩容问题【一致性哈希】
      2. 纵向拆分 【模块化】
        1. 【分布式事务问题】
      3. 定位慢sql、索引优化
    2. 连接方向优化
      1. 连接池
      2. 调整最大连接数
      3. 读写分离
    3. 事务、锁的优化
      1. 降低事务颗粒度、最后加锁等
      2. 防止表锁、间隙锁的情况

Q2：谈一谈 MySQL 的锁

  1. 乐观锁（CAS）
    1. 乐观锁不依赖数据库的具体功能，而是通过代码的业务逻辑来实现乐观锁的功能。
      1. select data as old_data,version as old_version from …,
      2. 根据获取的数据进行业务操作，得到new_data 和 new_version
      3. update … set data = new_data,version = new_version where version = old_version
      4. if(updated row > 0) 乐观锁获取成功操作完成
      5. 乐观锁获取失败，回滚重试
  2. 悲观锁（一锁二查三更新）
    1. 读锁（共享锁）:针对同一份数据，多个读操作可以同时进行而不会互相影响。
      1. select … lock in share mode（innodb 读锁，普通的select语句不加锁，行级别锁）
      2. lock table table_name read（myisam 读锁，默认select会加读锁，表级别锁）；
    2. 写锁（排它锁）:当前写操作没有完成前，它会阻断其他写锁和读锁。
      1. select … for update（innodb 写锁，命中索引时为行锁，不命中索引时降级为表锁，增删改语句会自动加写锁）
      2. lock table table_name write;（myisam ，表锁，增删改语句会自动加写锁）
    3. 表锁(MyISAM存储引擎默认的锁类型）
      1. 开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
      2. 查看表上过的锁 show open tables;
      3. 释放表锁（终止占用锁的会话 or 执行语句unlock tables）
    4. 行锁
      1. 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。
      2. 一是支持事务；二是彩了行级锁

        Q3: 什么是间隙锁

        在进行范围查询时，innodb 存储引擎会对范围内的数据加锁，无论数据是否存在。对于不存在的数据加锁就是间隙锁。
        set autocommit = 0;
        比如： update city set name = ‘wuhu’ where id > 5 and id < 10;
        此时尽管没有 id = 8 的数据，但在
        insert into city values(8,’qingdao’);
        的时候，仍然无法获得锁。

Q4：数据库死锁如何解决？

死锁是指多个事务在同一资源上相互占用并请求锁定对方占用的资源而导致恶性循环的现象。
当多个事务试图以不同顺序锁定资源时就可能会产生死锁，多个事务同时锁定同一个资源时也会产生死锁。
为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，例如InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方法，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。
InnoDB 目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。
死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型系统这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

Q5：事务是什么?

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

Q6：事务有什么特性？

  1. 原子性 atomicity

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

  1. 一致性 consistency

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

  1. 隔离性 isolation

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

  1. 持久性 durability

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

Q7：MySQL 的隔离级别有哪些？

  1. 读未提交 READ UNCOMMITTED

在该级别事务中的修改即使没有被提交，对其他事务也是可见的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还会导致不可重复读和幻读，性能没有比其他级别好很多，很少使用。

  1. 读已提交 READ COMMITTED

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

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

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

  1. 可串行化 SERIALIZABLE

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

Q8：MVCC 是什么？

MVCC 是多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞的读操作，写操作也只锁定必要的行。
InnoDB 的MVCC 通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间间。不过存储的不是实际的时间值而是系统版本号，每开始一个新的事务系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
MVCC 只能在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而 SERIALIZABLE 则会对所有读取的行都加锁。

？？？？？？？？

Q9：谈一谈 InnoDB

InnoDB 是 MySQL 的默认事务型引擎，用来处理大量短期事务。InnoDB 的性能和自动崩溃恢复特性使得它在非事务型存储需求中也很流行，除非有特别原因否则应该优先考虑 InnoDB。
InnoDB 的数据存储在表空间中，表空间由一系列数据文件组成。MySQL4.1 后 InnoDB 可以将每个表的数据和索引放在单独的文件中。
InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ，并通过间隙锁策略防止幻读，间隙锁使 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定防止幻行的插入。
InnoDB 表是基于聚簇索引建立的，InnoDB 的索引结构和其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能，不过它的二级索引中必须包含主键列，所以如果主键很大的话其他所有索引都会很大，因此如果表上索引较多的话主键应当尽可能小。
InnoDB 的存储格式是平立的，可以将数据和索引文件从一个平台复制到另一个平台。
InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

Q10：谈一谈 MyISAM

MySQL5.1及之前，MyISAM 是默认存储引擎，MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数等，但不支持事务和行锁，最大的缺陷就是崩溃后无法安全恢复。对于只读的数据或者表比较小、可以忍受修复操作的情况仍然可以使用 MyISAM。
MyISAM 将表存储在数据文件和索引文件中，分别以 .MYD 和 .MYI 作为扩展名。MyISAM 表可以包含动态或者静态行，MySQL 会根据表的定义决定行格式。MyISAM 表可以存储的行记录数一般受限于可用磁盘空间或者操作系统中单个文件的最大尺寸。
MyISAM 对整张表进行加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但是在表有读取查询的同时，也支持并发往表中插入新的记录。
对于MyISAM 表，MySQL 可以手动或自动执行检查和修复操作，这里的修复和事务恢复以及崩溃恢复的概念不同。执行表的修复可能导致一些数据丢失，而且修复操作很慢。
对于 MyISAM 表，即使是 BLOB 和 TEXT 等长字段，也可以基于其前 500 个字符创建索引。MyISAM 也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
MyISAM 设计简单，数据以紧密格式存储，所以在某些场景下性能很好。MyISAM 最典型的性能问题还是表锁问题，如果所有的查询长期处于 Locked 状态，那么原因毫无疑问就是表锁。

Q11：谈一谈 Memory

如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，那么使用 Memory 表是非常有用的。Memory 表至少要比 MyISAM 表快一个数量级，因为所有数据都保存在内存，不需要磁盘 IO，Memory 表的结构在重启后会保留，但数据会丢失。
Memory 表适合的场景：查找或者映射表、缓存周期性聚合数据的结果、保存数据分析中产生的中间数据。
Memory 表支持哈希索引，因此查找速度极快。虽然速度很快但还是无法取代传统的基于磁盘的表，Memory 表使用表级锁，因此并发写入的性能较低。它不支持 BLOB 和 TEXT 类型的列，并且每行的长度是固定的，所以即使指定了 VARCHAR 列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。
如果 MySQL 在执行查询的过程中需要使用临时表来保持中间结果，内部使用的临时表就是 Memory 表。如果中间结果太大超出了Memory 表的限制，或者含有 BLOB 或 TEXT 字段，临时表会转换成 MyISAM 表。

Q12：查询执行流程是什么？

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

Q13：VARCHAR 和 CHAR 的区别？

VARCHAR 用于存储可变字符串，是最常见的字符串数据类型。它比 CHAR 更节省空间，因为它仅使用必要的空间。VARCHAR 需要 1 或 2 个额外字节记录字符串长度，如果列的最大长度不大于 255 字节则只需要 1 字节。VARCHAR 不会删除末尾空格。
VARCHAR 适用场景：字符串列的最大长度比平均长度大很多、列的更新很少、使用了 UTF8 这种复杂字符集，每个字符都使用不同的字节数存储。

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

Q14：DATETIME 和 TIMESTAMP 的区别？

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

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

Q15：数据类型有哪些优化策略？

更小的通常更好
一般情况下尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常也更快，因为它们占用更少的磁盘、内存和 CPU 缓存。

尽可能简单
简单数据类型的操作通常需要更少的 CPU 周期，例如整数比字符操作代价更低，因为字符集和校对规则使字符相比整形更复杂。应该使用 MySQL 的内建类型 date、time 和 datetime 而不是字符串来存储日期和时间，另一点是应该使用整形存储 IP 地址。

尽量避免 NULL
通常情况下最好指定列为 NOT NULL，除非需要存储 NULL值。因为如果查询中包含可为 NULL 的列对 MySQL 来说更难优化，可为 NULL 的列使索引、索引统计和值比较都更复杂，并且会使用更多存储空间。当可为 NULL 的列被索引时，每个索引记录需要一个额外字节，在MyISAM 中还可能导致固定大小的索引变成可变大小的索引。
**如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。

Q16：索引有什么作用？

  1. 索引也叫键，是存储引擎用于快速找到数据的一种数据结构。在数据量比较大的时候，索引对于查询效率的提升是很明显的。
  2. 索引大大的减少了数据库需要扫描的数据量，可以帮助数据库避免排序和临时表，可以将随机io变成顺序io。对于非常小的表，大部分情况下会采用全表扫描。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价也随之增长，这种情况下应该使用分区技术。
  3. 在MySQL中，首先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。索引可以包括一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能使用索引的最左前缀。

Q17：谈一谈 MySQL 的 B-Tree 索引

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

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

Q18：了解 Hash 索引吗？

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
只有 Memory 引擎显式支持哈希索引，这也是 Memory 引擎的默认索引类型。因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引的速度非常快，但它也有一些限制：

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

    Q19：什么是自适应哈希索引？

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

Q20 ：什么是空间索引？

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

Q21：什么是全文索引？

通过数值比较、范围过滤等就可以完成绝大多数需要的查询，但如果希望通过关键字匹配进行查询，就需要基于相似度的查询，而不是精确的数值比较，全文索引就是为这种场景设计的。
MyISAM 的全文索引是一种特殊的 B-Tree 索引，一共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的”文档指针”。全文索引不会索引文档对象中的所有词语，它会根据规则过滤掉一些词语，例如停用词列表中的词都不会被索引。

Q22：什么是聚簇索引？

  • 聚簇索引
    • 数据和索引在一块，按照一定的顺序组织，找到了索引也就找到了数据。
    • InnoDB一定有主键，主键一定是聚簇缩影
    • 不手动设置，则会使用unique索引，没有unique索引，则会使用数据库内部的一个行隐藏id来当作主键索引。
  • 非聚簇索引
    • 叶子结点不存储数据，而是存储数据的地址。现根据索引查到数据行的位置，然后再从磁盘拿到真是的数据。（类似于书的目录，先找到在第几页，然后再翻页看内容）

聚簇索引不是一种索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。当表有聚簇索引时，它的行数据实际上存放在索引的叶子页中，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
优点：
① 可以把相关数据保存在一起
② 数据访问更快，聚簇索引将索引和数据保存在同一个 B-Tree 中，因此获取数据比非聚簇索引要更快
③ 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点：
① 聚簇索引最大限度提高了 IO 密集型应用的性能，如果数据全部在内存中将会失去优势
② 更新聚簇索引列的代价很高，因为会强制每个被更新的行移动到新位置
③ 基于聚簇索引的表插入新行或主键被更新导致行移动时，可能导致页分裂，表会占用更多磁盘空间
④ 当行稀疏或由于页分裂导致数据存储不连续时，全表扫描可能很慢

Q23：什么是覆盖索引？

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

Q24：你知道哪些索引使用原则？

建立索引
对查询频次较高且数据量比较大的表建立索引。索引字段的选择，最佳候选列应当从 WHERE 子句的条件中提取，如果 WHERE 子句中的组合比较多，应当挑选最常用、过滤效果最好的列的组合。业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

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

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

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

Q25：索引失效的情况有哪些？

  1. 模
    1. like关键字查询的时候，如果以百分号开头，则会造成索引失效。
      1. 解决方案：使用reserve函数对该字段倒叙，使用索引时也倒叙使用
      2. https://www.cnblogs.com/Marydon20170307/p/15866354.html
  2. 型
    1. 数据类型不一致，索引会失效
  3. 数
    1. 对于索引字段，使用内部函数，会造成索引失效
      1. 解决方案：使用内部函数简历索引
  4. 空：
    1. Null，索引不存储空值，如果不限制索引字段为not null，数据库不会按照索引进行计算，可能会造成索引失效。
  5. 运：
    1. 对索引列进行加减乘除等运算，会造成索引失效
  6. 最：
    1. 复合索引需要满足最左前缀（按照索引列的最左列开始进行查找）
  7. 快
    1. 查询优化器预计使用全表扫描更快的话，则不会使用索引
  8. or
    1. or 有一侧没有索引，整个查询不走索引
  9. in / not in
    1. not in 会导致索引失效

Q26：如何定位低效 SQL？

可以通过两种方式来定位执行效率较低的 SQL 语句。

  1. 通过慢查询日志定位，可以通过慢查询日志定位那些已经执行完毕的 SQL 语句。
  2. 使用 SHOW PROCESSLIST 查询，慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志不能定位问题，此时可以使用 SHOW PROCESSLIST 命令查看当前 MySQL 正在进行的线程，包括线程的状态、是否锁表等，可以实时查看 SQL 的执行情况，同时对一些锁表操作进行优化。
  3. 找到执行效率低的 SQL 语句后，就可以通过 SHOW PROFILE、EXPLAIN 或 trace 等丰富来继续优化语句。

Q27：SHOW PROFILE 的作用？

通过 SHOW PROFILE 可以分析 SQL 语句性能消耗，例如查询到 SQL 会执行多少时间，并显示 CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。
例如 SHOW PROFILE CPU/MEMORY/BLOCK IO FOR QUERY N 分别查询 id 为 N 的 SQL 语句的 CPU、内存以及 IO 的消耗情况。

  2. -- show profile
  3. -- 查看是否支持profile
  4. select @@have_profiling;
  6. -- 查看是否开启profile
  7. select @@profiling;
  9. -- 开启当前会话 profile
  10. set profiling = 1;
  12. -- 测试sql
  13. select *
  14. from t_user;
  16. -- 查看操作耗时
  17. show profiles;
  19. -- 查看具体的操作耗时
  20. show profile cpu for query 55;

Q28：trace 是干什么的？

从 MySQL5.6 开始，可以通过 trace 文件进一步获取优化器是是如何选择执行计划的，在使用时需要先打开设置，然后执行一次 SQL，最后查看 information_schema.optimizer_trace 表而都内容，该表为联合i表，只能在当前会话进行查询，每次查询后返回的都是最近一次执行的 SQL 语句。

Q29：EXPLAIN 的字段有哪些，具有什么含义？

  1. id
    1. 哪条语句优先查询，根据id序号的排列

  2. select_type | SIMPLE | 简单的select查询，查询中不包含子查询或者UNION | | —- | —- | | PRIMARY | 查询中若包含任何复杂的子查询，最外层查询标记为该标识 | | SUBQUERY | 在SELECT 或 WHERE 列表中包含了子查询 | | DERIVED | 在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中 | | UNION | 若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED | | UNION RESULT | 从UNION表获取结果的SELECT |

  3. type（从上往下 效率依次增高）

    1. all 全表扫描
    2. index 索引扫描（遍历整个 索引树）
    3. range 索引范围扫描（至少要达到range级别）
    4. ref 返回匹配某个单独值的所有行（非唯一性索引）
    5. eq_ref 唯一性索引扫描，对于每个索引只有一条记录与之匹配
    6. const 例如将主键或唯一索引置于 where 列表
    7. system 表中只有一行数据或者空表，只用于 myisam 或者 memory
    8. null 执行时不访问表或索引就能得到结果(select now())。
  4. possiable_key
    1. 可能用到的索引
  5. key （这个值尽量不为空）
    1. 实际查询用到的索引，如果没有使用索引，则返回null
  6. key_len
    1. 表示使用到的索引字段的长度
  7. ref
    1. 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  8. rows
    1. 根据表的统计信息以及索引选用情况，估算找到所需记录所需要读取的行数
  9. extra
    1. Using temporary（需要优化） 表示需要使用临时表存储结果集，常见于排序和分组查询
    2. Using filesort （需要优化 ） 表示无法利用索引完成文件排序，这是order by 的结果，可以通过合适的索引改进性能
    3. Using index 表示只需要使用索引就可以满足查询表的要求，说明表正在使用覆盖索
    4. Using index condition （可优化）虽然是用了索引，但是会进行索引回表查询。

Q30：有哪些优化 SQL 的策略？

  1. 使用ISNULL() 来判断是否为NULL值
  2. 避免索引失效（模型数空运最快）
  3. order by 条件要和where 中的条件一致，否则order by 不会利用索引排序
  4. 多表关联的时候，小表在前，大表在后
  5. where 子句的链接顺序，应该将过滤数据多的条件往前方，最快的速度缩小结果集
  6. 如果查询包括GROUP BY ，但是并不像对分组的值进行排序，可以使用ORDER BY NULL禁止排序
  7. 优化union查询
    1. MySql 通过创建并填充临时表的方式来执行union查询，如果没有all 这个关键字，mysql会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验 ```sql — 效率低 SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 UNION SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= ‘TEST’;

— 效率⾼ SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 UNION ALL SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= ‘TEST’; 

  2. 8. **尽量避免使用in 和 not in，会导致走全表扫描**
  3. 8. **避免使用or，会导致数据库引擎放弃索引使用全表扫描**
  4. ```sql
  5. -- 使用union 代替or
  6. SELECT * FROM t WHERE id = 1 
  7. UNION ALL
  8. SELECT * FROM t WHERE id = 3
  1. 后缀查询
    1. 使用reserve 函数建立索引，在查找的时候就可以遵守最左匹配 ```sql — 建表 create table t1( a int, b varchar(100), c varchar(10) as (reserve(sbstr(b,length(b)-10))) ); create index i1 on t1(c)

— 查询 select * from t1 where c like reverse(‘%关键字’）;

  2. 11. **LIMIT 优化**
  3. ```sql
  4. select * from t5 order by text limit 100000,10;
  6. -- 覆盖索引（不回表）
  7. select id,`text` from t5 order by text limit 100000,10;
  9. -- 子查询优化（要求主键ID必须是连续的，where子句不允许在添加其他的条件）
  10. select * from t5 where id >=
  11.     (select id from t5 order by text limit 100000,1)
  12.   limit 10;
  14. -- join优化
  15. select a.* from t5 a
  16.     inner join 
  17.       (select id from t5 order by text limit 100000,10) b
  18.         on a.id = b.id
  1. 分页查询优化
    1. count 创建独立的表，记录大数据量表中的count的数据，而不用每次都是用count()函数
      1. 缺点：需要多维护一张表，无法进行条件查询
    2. limit 优化 见11

Q31：MySQL 主从复制的作用？

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步，一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。
MySQL 支持两种复制方式：基于行的复制和基于语句的复制，基于语句的复制也称为逻辑复制，从 MySQL 3.23 版本就已存在，基于行的复制方式在 5.1 版本才被加进来。这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。因此同一时刻备库的数据可能与主库存在不一致，并且无法保证主备之间的延迟。
MySQL 复制大部分是向后兼容的，新版本的服务器可以作为老版本服务器的备库，但是老版本不能作为新版本服务器的备库，因为它可能无法解析新版本所用的新特性或语法，另外所使用的二进制文件格式也可能不同。
复制解决的问题：数据分布、负载均衡、备份、高可用性和故障切换、MySQL 升级测试。

Q32：MySQL 主从复制的步骤？

  1. 在主库上把数据更改记录到二进制日志中
  2. 备库将主库的日志复制到自己的中继日志中
  3. 备库读取中继日志的事件，将其重放到备库数据之上

第一步是在主库上记录二进制日志，每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL 会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志，在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
下一步，备库将主库的二进制日志复制到其本地的中继日志中。备库首先会启动一个工作的 IO 线程，IO 线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储线程，这个线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库 IO 线程会将接收到的事件记录到中继日志中。
备库的 SQL 线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当 SQL 线程追赶上 IO 线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL 线程执行的时间也可以通过配置选项来决定是否写入其自己的二进制日志中。

全同步复制：

  1. 主库写入binlog 之后强制同步到从库，所有到从库都执行完成后才能返回给客户端，显然这种方式严重影响性能。

半同步复制：

  1. 从库写入日志成功后，返回ack确认给主库，主库收到至少一个从库的确认就认为写操作完成

配置：

  1. -- Master
  2. -- 权限配置
  3. //192.168.0.106是slave从机的IP
  4. GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.106' identified by 'Java@1234'; //192.168.0.107是slave从机的IP
  5. GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.107' identified by 'Java@1234'; //刷新系统权限表的配置
  6. FLUSH PRIVILEGES;
  7. -- 配置文件 /etc/my.cnf
  9. # 开启binlog
  10. log-bin=mysql-bin
  11. server-id=104
  12. # 需要同步的数据库，如果不配置则同步全部数据库 binlog-do-db=test_db
  13. # binlog日志保留的天数，清除超过10天的日志
  14. # 防止日志文件过大，导致磁盘空间不足 expire-logs-days=10
  15. -- 需要重启
  16. -- Slave
  17. -- 配置文件
  18. # 不要和其他mysql服务id重复即可
  19. server-id=106
  20. -- 执行SQL
  21. CHANGE MASTER TO
  22. MASTER_HOST='192.168.0.104',//主机IP MASTER_USER='root',//之前创建的用户账号 MASTER_PASSWORD='Java@1234',//之前创建的用户密码 MASTER_LOG_FILE='mysql-bin.000001',//master主机的binlog日志名称 MASTER_LOG_POS=862,//binlog日志偏移􏰀
  23. master_port=3306;//端口
  24. -- 需要􏰁启
  25. -- 检查配置
  26. show slave status\G;
  27. -- 得到输出
  28. *************************** 1. row ***************************
  29.                Slave_IO_State: Waiting for master to send event
  30.                   Master_Host: 192.168.0.104
  31.                   Master_User: root
  32.                   Master_Port: 3306
  33.                 Connect_Retry: 60
  34.               Master_Log_File: mysql-bin.000001
  35.           Read_Master_Log_Pos: 619
  36.                Relay_Log_File: mysqld-relay-bin.000001
  37. Relay_Log_Pos: 782
  38. Relay_Master_Log_File: mysql-bin.000001 //binlog日志文件名称
  39. Slave_IO_Running: Yes //Slave_IO线程、SQL线程都在运行 Slave_SQL_Running: Yes
  40. Master_Server_Id: 104 //master主机的服务id
  41. Master_UUID: 0ab6b3a6-e21d-11ea-aaa3-080027f8d623
  42.              Master_Info_File: /var/lib/mysql/master.info
  43.                     SQL_Delay: 0
  44.           SQL_Remaining_Delay: NULL
  45.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave
  46. I/O thread to update it
  47. Master_Retry_Count: 86400
  48.      Auto_Position: 0

Q33 mysql 三大日志

  1. binlog
    1. 用于记录写入性操作，以二进制的形式保存在磁盘中，通过追加的方式写入
    2. 使用场景
      1. 主从复制
      2. 数据恢复
    3. 刷盘机制
      1. sync_binlog参数控制
        1. 0 不强制要求，由系统自行判断合适写入磁盘
        2. 1 每次commit 的时候都要将binlog写入磁盘
        3. N 没N个事务，才会将binlog写入磁盘
  2. redo log
    1. 出现场景：
      1. mysql为了保证一致性，将所有事务涉及的修改页全部更新到磁盘中，但这么做会有严重的性能问题，主要体现在两个方面
        1. Innodb 以页为单位进行磁盘交互，一个事务仅能修改一小部分数据，全部更新浪费资源
        2. 一个事务设计修改多个数据页，这些数据页在物理上不连续，随机io写入性能太差
      2. 因此mysql设计了redo log ，具体来说，就是记录事务对数据业页做了那些修改，这样就能完美解决性能问题了
      3. 基本概念：
        1. redo log 包括两部分
          1. 日志缓冲 redo log buffer
          2. 磁盘日志 redo log file
  3. undo log
    1. 出现场景：数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况（原子性）
    2. 用于事务回滚：
      1. undo log 主要记录了数据的逻辑变化，比如一条insert 语句，对应一条delete 的undo log，对于每个update 语句，对应一条与之相反的undo log 语句，在发生错误的时候，就能回滚到事务之前的状态
    3. image.png

由binlog 和 redo log 的区别可知，binlog 日志用于归档，只依靠binlog是没有crash-safe能力的。但只有redo log 也不行，因为redo log 日志是innodb特有的，且日志傻姑娘的记录落盘后会被覆盖，因此需要binlog和redo log 同时记录，才能保证数据库发生宕机的时候，数据不会丢失
redo log 和 binlog 一致性如何保证？

  1. prepare ： 写入redo log，并将回滚置为prepared状态，此时binlog 不做操作
  2. commit： innodb 释放锁，释放回滚短，设置提交状态，写入binlog，然后存储引擎提交