MySQL杂谈

1、MySQL 的逻辑架构

2、MySQL的读写锁

共享锁（shared lock，读锁）：共享的，相互不阻塞的；多个客户在同一时刻可以同时读取同一个资源，而互不干扰。
排他锁（exclusive lock，写锁）：排他的，一个写锁会阻塞其他的写锁和读锁
（SQL）:
共享锁：select …… lock in share mode;
排他锁：select …… for update;

3、MySQL的锁策略

表锁：最基本的锁策略，并且是开销最小的策略。
行锁：最大程度地支持并发处理（同时也带来了最大的锁开销）。在InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层（如有必要，请回顾前文的逻辑架构图）没有实现。
此外还有页锁
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

4、数据库死锁解决

• InnoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效
• 当查询的时间达到锁等待超时的设定后放弃锁请求
• InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚（这是相对比较简单的死锁回滚算法）。

5、事务

事务就是一组原子性的SQL查询，事务内的语句，要么全部执行成功，要么全部执行失败。

6、事务特性

• 原子性（atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作
• 一致性（consistency）数据库总是从一个一致性的状态转换到另外一个一致性的状态
• 隔离性（isolation）一个事务所做的修改在最终提交以前，对其他事务是不可见的
• 持久性（durability）一旦事务提交，则其所做的修改就会永久保存到数据库中

7、事务的四种隔离级别

• READ UNCOMMITTED（未提交读），事务中的修改，即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，也被称为脏读（Dirty Read），这个级别会导致很多问题
• READ COMMITTED（提交读），大多数数据库系统的默认隔离级别，一个事务开始时，只能“看见”已经提交的事务所做的修改，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，也叫不可重复读（nonrepeatable read），有可能出现幻读（Phantom Read），指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）
• REPEATABLE READ（可重复读）解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的，通过InnoDB和XtraDB存储引擎，是MySQL的默认事务隔离级别
• SERIALIZABLE（可串行化）最高级别，通过强制事务串行执行，避免了幻读问题，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。

8、MVCC（多版本并发控制）

• MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

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

• 保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

• MVCC只在（可重复读）和（提交读）两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容(4)，因为**(未提交读)总是读取最新的数据行，而不是符合当前事务版本的数据行**。而（可串行化）则会对所有读取的行都加锁。

• INSERT

  • InnoDB为新插入的每一行保存当前系统版本号作为行版本号

• DELETE

  • InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

• UPDATE

  • InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

• SELECT

  • InnoDB会根据以下两个条件检查每行记录
    • 行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的；
    • 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除

9、谈一谈InnoDB

• InnoDB是MySQL的默认事务型引擎。
• 它被设计用来处理大量的短期（short-lived）事务，短期事务大部分情况是正常提交的，很少会被回滚。
• 自动崩溃恢复特性
• InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLEREAD（可重复读），并且通过间隙锁（next-keylocking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
• InnoDB表是基于聚簇索引建立的，它的二级索引（secondaryindex，非主键索引）中必须包含主键列。
• 作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份。

10、谈一谈MyISAM

-（并发）： MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录（这被称为并发插入，CONCURRENTINSERT）。

• （索引特性）：对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
• （延迟更新索引键）：创建MyISAM表的时候，如果指定了**（延迟更新索引键）DELAY_KEY_WRITE**选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区（in-memorykeybuffer），只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。
• （表压缩）：如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。
• （修复）：对于MyISAM 表，MySQL 可以手动或自动执行检查和修复操作，这里的修复和事务恢复以及崩溃恢复的概念不同。执行表的修复可能导致一些数据丢失，而且修复操作很慢。

11、谈一谈Memory引擎

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

12、查询执行流程

1. 客户端发送一条查询给服务器。
2. 服务器先检查查询缓存，如果命中了缓存则立刻返回存储在缓存中的结果，否则进入下一阶段。
3. 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
4. MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
5. 将结果返回给客户端。

13、VARCHAR和CHAR

• VARCHAR：存储可变字符串，不会删除末尾空格。适合存储很短的字符串，或所有值都接近同一个长度
• CHAR：定长的，会删除末尾空格。字符串列的最大长度比平均长度大很多、列的更新很少、使用了 UTF8 这种复杂字符集，每个字符都使用不同的字节数存储。

14、DATETIME 和 TIMESTAMP

DATETIME：从 1001~9999 年，精度为秒。
TIMESTAMP：UNIX 时间戳相同，只使用 4 字节的存储空间，范围比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依赖于时区。
通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

15、数据类型有哪些优化策略

• 更小
• 更简单
• 避免为null

16、索引有什么作用

• 索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能;
• 索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的;

17、MySQL的B-Tree索引

• B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图示并未画出）开始进行搜索。
• 根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。
• 叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页（不同引擎的“指针”类型不同）。
• B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
• B-Tree索引的限制：
  • 如果不是按照索引的最左列开始查找，则无法使用索引。
  • 不能跳过索引中的列，例如索引为 (id,name,sex)，不能只使用 id 和 sex 而跳过 name。
  • 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

18、了解Hash索引吗

• 基于哈希表实现；
• 只有精确匹配索引所有列的查询才有效；
• 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hashcode），哈希码是一个较小的值；并且不同键值的行计算出来的哈希码也不一样。
• 哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针；
• 只有 Memory 引擎显式支持哈希索引，这也是 Memory 引擎的默认索引类型。
• Hash索引的限制：
  • 哈希索引数据不是按照索引值顺序存储的，无法用于排序。
  • 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如在数据列(a,b)上建立哈希索引，如果查询的列只有a就无法使用该索引。
  • 哈希索引只支持等值比较查询，不支持任何范围查询。

19、什么是自适应哈希索引？

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

20、什么是空间索引？

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS。

21、什么是全文索引？

22、什么是聚簇索引？

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

23、什么是覆盖索引？

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

24、你知道哪些索引使用原则？

• 建立索引
  • 对查询频次较高且数据量比较大的表建立索引，索引字段的选择，最佳候选项是where子句提取；
  • 多个字段的组合也必须建成唯一索引；
• 使用前缀索引
  • 索引列开始的部分字符，索引创建后也是使用硬盘来存储的，因此短索引可以提升索引访问的 IO 效率。
  • 对于 BLOB、TEXT 或很长的 VARCHAR 列必须使用前缀索引；
  • 但缺点是 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。
• 选择合适的索引顺序
  • 不考虑排序和分组时，将选择性最高的列放在前面；
  • 索引的选择性是指不重复的索引值和数据表的记录总数之比
• 删除无用索引
  • MySQL 允许在相同列上创建多个索引，重复的索引需要单独维护，并且优化器在优化查询时也需要逐个考虑，这会影响性能。
  • 重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免创建重复索引。如果创建了索引 (A,B) 再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引，对于 B-Tree 索引来说是冗余的。
  • 解决重复索引和冗余索引的方法就是删除这些索引。除了重复索引和冗余索引，可能还会有一些服务器永远不用的索引，也应该考虑删除。

25、索引失效的情况有哪些？

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

26、如何定位低效 SQL？

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

27、SHOW PROFILE 的作用？

• Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。
• 开启set profileing=on;
• 查询参数：
  • ALL：显示所有的开销信息。
  • BLOCK IO：显示块IO开销。
  • CONTEXT SWITCHES：上下文切换开销。
  • CPU：显示CPU开销信息。
  • IPC：显示发送和接收开销信息。
  • MEMORY：显示内存开销信息。
  • PAGE FAULTS：显示页面错误开销信息。
  • SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
  • SWAPS：显示交换次数开销信息。

28、trace 是干什么的？

• 从 MySQL5.6 开始，可以通过 trace 文件进一步获取优化器是是如何选择执行计划的，在使用时需要先打开设置，然后执行一次 SQL，最后查看 information_schema.optimizer_trace 表而都内容，该表为联合i表，只能在当前会话进行查询，每次查询后返回的都是最近一次执行的 SQL 语句。
• 开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。
• （转）参考链接

29、EXPLAIN 的字段有哪些，具有什么含义？

• 执行计划是 SQL 调优的一个重要依据，可以通过 EXPLAIN 命令查看 SQL 语句的执行计划，如果作用在表上，那么该命令相当于 DESC；
• 字段
  • id:选择标识符
  • select_type:表示查询的类型。
  • table:输出结果集的表
  • partitions:匹配的分区
  • type:表示表的连接类型
  • possible_keys:表示查询时，可能使用的索引
  • key:表示实际使用的索引
  • key_len:索引字段的长度
  • ref:列与索引的比较
  • rows:扫描出的行数(估算的行数)
  • filtered:按表条件过滤的行百分比
  • Extra:执行情况的描述和说明
• （转 ）参考链接

30、有哪些优化 SQL 的策略？

• 优化COUNT()查询
  • 统计某个列值的数量
  • 统计行数
  • 尽量使用Count(*)
• 优化关联查询
  • 确保 ON 或 USING 子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说**，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。**
  • 确保任何 GROUP BY 和 ORDER BY 的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化这个过程。
  • 在 MySQL 5.5 及以下版本尽量避免子查询，可以用关联查询代替，因为执行器会先执行外部的 SQL 再执行内部的 SQL。
• 优化group by和DISTINCT
  • 如果没有通过ORDERBY子句显式地指定排序列，当查询使用GROUPBY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDERBYNULL，让MySQL不再进行文件排序。也可以在GROUPBY子句中直接使用DESC或者ASC关键字，使分组的结果集按需要的方向排序。
• 优化LIMIT 分页
  • 最简单的办法是尽可能地使用覆盖索引扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。
  • 从上一次取数据的位置开始扫描，这样就可以避免使用 OFFSET。
  • 使用预先计算的汇总表，或者关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。
• 优化 UNION 查询
  • MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要服务器消除重复的行，否则一定要使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。

• 使用用户自定义变量

• 优化 INSERT
• 需要对一张表插入很多行数据时，应该尽量使用一次性插入多个值的 INSERT 语句，这种方式将缩减客户端与数据库之间的连接、关闭等消耗，效率比多条插入单个值的 INSERT 语句高。也可以关闭事务的自动提交，在插入完数据后提交。当插入的数据是按主键的顺序插入时，效率更高。

31、MySQL 主从复制的作用？

32、MySQL 主从复制的步骤？

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

参考链接：
【1】https://www.cxyxiaowu.com/16302.html
【2】https://www.cnblogs.com/Serverlessops/articles/9222357.html