八股文-数据库

[rrain7]

MySQL

读写锁

在处理并发读写问题时，可以通过实现一个有两种类型的锁组成的锁系统来解决问题。

这两种类型的锁通常称为 共享锁（shared lock） 和 排他锁（exclusive lock），也称 「读锁」 和 「写锁」。

• 读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以读取同一个资源而不相互干扰。
• 写锁是排他的，一个写锁会阻塞其他的写锁和读锁。为了保证在给定的时间内，只有一个用户才能执行写入，并防止其他用户读取正在写入的同一资源。

索引

索引可以包含一个或者多个列的值。若索引包含了多个列，那么索引的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。

索引的类型

• B+Tree B+树通常意味着值是按照顺序存储的。 B+树对索引列是顺序组织存储的，所以很适合查找范围数据。 B-Tree索引适用于 全键值 、键值范围 或 键前缀查找

  ---

  B+树索引何时有效？

  1. 全值匹配：和索引中所有的列进行匹配
  2. 匹配最左前缀：若有多个索引列，只使用索引的第一列。
  3. 匹配列前缀：只匹配某一列的开头部分。
  4. 匹配范围值：
  5. 精确匹配某一列并范围匹配另外一列：精确匹配前面的索引列，范围匹配后面的索引列。
  6. 只访问索引的查询：查询只需要访问索引，而无需访问数据行。由于索引树中的节点是有序的，所以除了按值查找外，索引还可以用于查询中的 Order by 操作。

  B+树索引的使用限制

  1. 如果不是按照索引的最左列开始查找，则无法使用索引。
  2. 不能跳过索引中的列。

  3. 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引 优化查找。

• 哈希索引 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。 哈希索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。 哈希索引使用限制

  1. 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用 索引中的值来避免读取行
  2. 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排 序。
  3. 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
  4. 哈希索引只支持等值比较查询，不支持范围查找。
  5. 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。删除操作需要遍历链表。

InnoDB引擎有一个特殊的功能叫做"自适应哈希索引", 当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B+Tree索引之上再创建一个哈希索引，这样就让B+Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

• 空间数据索引 MyISAM表支持空间索引，可以用作地理数据存储。和B+Tree索引 不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。
• 全文索引 全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而 不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不 一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索 等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE 条件匹配。

索引的优点

最常见的B+Tree索引，按照顺序存储数据，所以MySQL可以用来 做ORDER BY 和 GROUP BY 操作。因为数据是有序的，所以B+Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

总结如下：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机I/O变为顺序I/O。

高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。

高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。使用哪个索引，以及如何评估选择不同索引的性能影响的技巧，则需要持续不断地学习。

• 独立的列 如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

  我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

• 前缀索引和索引选择性 索引很长的字符列，会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。 但是会降低索引的选择性。

  索引选择性：不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

  前缀索引的缺点：

  1. MySQL无法使用前缀索引做ORDER BY 和 GROUP BY
  2. 无法使用前缀索引做覆盖扫描

• 多列索引

• 选择合适的索引列顺序

  在一个多列B+Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和DISTINCT等子句的查询需求。

• 聚簇索引 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B+Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。 一个表只能有一个聚簇索引。 ==todo==

• ==todo==

• ... ==todo==

查询性能优化

MySQL是如何执行查询的?

为什么查询速度会慢?

如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。

通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。

其中，执行 可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。

• 确认应用程序是否在检索大量超过需要的数据。
• 确认MySQL服务器层是否在分析大量超过需要的数据行。

==todo==

[rrain7]

MySQL 主从问题？