理解MySQL中的索引：第一部分

MySQL中的索引非常复杂,像一头巨大的怪兽。我们过去介绍过MySQL索引的一些知识 ，但并不深入。本系列文章将深入地探讨这些主题。

什么是索引？

一般来说，正如前面一篇关于索引的博客中所提到的，索引是一个按字母顺序排列的记录列表，其中引用了这些记录所在的页面。在MySQL中，索引是一种数据结构， 用于快速查找行。你可能还会听到术语 “keys”——它也指索引。

索引的作用是什么？

在MySQL中，索引用于快速查找具有特定列值的行，并防止读取整个表以查找与查询相关的任何行。当存储在数据库系统（例如MySQL）中的数据变大时，通常会使用索引，因为表越大，从索引中获益的可能性越大。

MySQL索引类型

就MySQL而言，您可能听说过它有多种类型的索引：

  • B-Tree 索引–此类索引经常用于加速与WHERE子句匹配的SELECT查询。索引列值不必唯一，也接受NULL值。
  • FULLTEXT索引–此类索引用于全文搜索功能。这种类型的索引在文本中查找关键字，而不是直接将值与索引中的值进行比较。
  • UNIQUE INDEX 经常用于从表中删除重复值。保证行值的唯一性。
  • PRIMARY KEY也是一个索引–它经常与具有AUTO_INCREMENT属性的字段一起使用。这种类型的索引不接受NULL值，一旦设置，具有PRIMARY KEY的列中的值就不能更改。
  • DESCENDING INDEX 是按降序存储行的索引。这种类型的索引是在MySQL 8.0中引入的，当查询请求按降序排序时，MySQL将使用这种索引类型。

在MySQL中为索引选择最佳数据类型

就索引而言，还需要记住，MySQL支持多种数据类型，有些数据类型不能与某些类型的索引一起使用（例如，FULLTEXT索引只能用于基于文本的（CHAR、VARCHAR或text）列，不能用于任何其他数据类型），因此在为数据库设计实际选择索引之前，要决定在相关列上使用的数据类型（决定要存储什么样的数据类：要存储数字吗？字符串值？数字和字符串值？等等），然后决定要存储的值的范围（选择一个您认为不会超过的值，因为增加数据类型范围以后可能是一项耗时的任务–我们建议您选择使用简单的数据类型），如果您不打算在列中使用NULL值，请尽可能将字段指定为NOT NULL–当可为NULL的列被索引时，每个条目需要额外的字节。

在MySQL中为索引选择最佳字符集和排序规则

除了数据类型之外，还要记住MySQL中的每个字符都会占用空间。例如，UTF-8字符可能每个字符占用1到4个字节，因此您可能希望避免对其索引，例如255个字符，并且只对某一列使用50或100个字符。

MySQL中使用索引的优点和缺点

在MySQL中使用索引的主要好处是提高了匹配WHERE子句的搜索查询的性能–索引加快了匹配WHERE子句的SELECT查询的速度，因为MySQL不会读取整个表来查找与查询相关的行。然而，请记住，索引有其自身的缺点。主要内容如下：

  • 索引占用磁盘空间。
  • 索引会降低INSERT、UPDATE和DELETE查询的性能–当数据更新时，需要同时更新索引。
  • MySQL不能防止您同时使用多种类型的索引。换句话说，您可以在同一列上使用PRIMARY KEY、INDEX和UNIQUE INDEX–MySQL不能防止您犯这种错误。

如果您怀疑某些查询速度变慢，请考虑查看ClusterControl 的Query Monitor选项卡 – 通过启用查询监视器，您可以查看上次看到某个查询的时间及其最大和平均执行时间，这可以帮助您为表选择最佳索引。

mysql 每个索引的空间大小_数据库

如何选择要使用的最佳索引？

要选择要使用的最佳索引，可以使用MySQL的内置机制。例如，可以使用查询解释程序–EXPLAIN查询。它将解释使用了什么表、是否有分区、可以使用什么索引以及使用了什么键（索引）。它还将返回索引键长度和查询返回的行数：

mysql> EXPLAIN SELECT * FROM demo_table WHERE demo_field = ‘demo’G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo_table
   partitions: NULL
         type: ref
possible_keys: demo_field
          key: demo_field
      key_len: 1022
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

在这种情况下，请记住，当数据集比平时大时，索引经常用于帮助MySQL高效检索数据。如果表很小，则可能不需要使用索引，但如果看到表越来越大，则很可能会从索引中受益。

但是，为了选择用于特定场景的最佳索引，请记住，索引也可能是性能问题的主要原因。请记住，MySQL能否有效使用索引取决于几个因素，包括查询的设计、使用的索引、使用的索引类型、执行查询时的数据库负载以及其他因素。在MySQL中使用索引时，需要考虑以下几点：

  • 你有多少数据？也许其中有些是多余的？
  • 您使用什么查询？您的查询是否使用LIKE子句？如何排序？
  • 您需要使用哪种索引来提高查询的性能？
  • 索引是大还是小？您是否需要在列的前缀上使用索引来缩小其大小？

值得注意的是，您可能也应该避免在同一列上使用多种类型的索引（例如B-Tree索引、UNIQUE INDEX和PRIMARY KEY）。

使用索引提高查询性能

要提高索引的查询性能，您需要检视你的查询–EXPLAIN语句可以帮助做到这一点。通常，如果希望索引提高查询性能，您应该考虑以下几点：

  • 只向数据库询问您需要的内容。在大多数情况下，使用SELECT column 比使用SELECT * 快（即不使用索引的情况）
  • 如果您搜索精确的值（例如，SELECT * FROM demo_table WHERE some_field=‘x’），或者如果您想使用通配符搜索值，则B树索引可能更适合（例如，SELECT * FROM demo_table WHERE some_field LIKE ‘demo%’–在这种情况下，请记住，对开头的任何内容使用LIKE查询可能弊大于利–请避免在搜索文本前使用带有百分号的LIKE询问–这样MySQL可能不会使用索引，因为它不知道行值的开头是什么）– 尽管要保持不变请注意，在使用等于（=）、大于（>）、大于或等于（>=）、小于（<）、小于或等于（<=）或BETWEEN运算符的表达式中，B树索引也可以用于列比较。
  • 如果您发现自己正在使用全文（MATCH…AGAINST（））搜索查询，或者数据库的设计方式仅使用基于文本的列，则FULLTEXT索引可能适合使用–FULLTEXT索引可以使用text、CHAR或VARCHAR列，它们不能用于任何其他类型的列。
  • 如果希望在大表上运行查询而不进行额外的I/O读取，则覆盖索引可能有用。要创建覆盖索引，（请在索引中）覆盖查询使用的WHERE、GROUP BY和SELECT子句中的列。

我们将在本系列博客的后续部分进一步研究索引的类型，但一般来说，如果您使用SELECT * FROM demo_table WHERE some_field='x’之类的查询，则可能适合使用B树索引，如果您用MATCH（）AGAINST（）查询，则应该查看FULLTEXT索引，如果表的行值很长，则可能应该查看列的一部分索引。

应该有多少索引

如果您曾经使用索引来提高SELECT查询的性能，那么您可能会问自己一个问题：实际上应该有多少索引？为了理解这一点，您需要记住以下几点：

  1. 索引通常是数据量大的情况下最有效。
  2. MySQL在查询中的每个SELECT语句只使用一个索引（子查询被视为单独的语句） - 使用EXPLAIN 找出哪些索引对您使用的查询最有效。
  3. 索引应该使所有SELECT语句都足够快，而不会占用太多的磁盘空间–但是，“足够快”是相对的，因此您需要进行实验。

索引和存储引擎

在MySQL中处理索引时，还要记住，如果使用各种不同的存储引擎（例如，如果使用MyISAM而不是InnoDB），可能会有一些限制。我们将在单独的博客中详细介绍，但这里有一些想法：

  • 每个MyISAM和InnoDB表的最大索引数为64，两个存储引擎中每个索引的最大列数为16。
  • InnoDB的最大索引键长度为3500字节–MyISAM的最大索引键长为1000字节。
  • 全文索引在某些存储引擎中有局限性，例如，InnoDB全文索引有36个停用词，MyISAM停用词列表稍大，有143个停用词。InnoDB从innodb_ft_server_stopword_table变量派生这些停用词，而MyISAM从 storage/myisam/ft_static.c文件派生这些停用词。 在该文件中找到的所有单词都将被视为停止词。
  • MyISAM是唯一支持全文搜索选项的存储引擎，直到MySQL 5.6（确切地说是MySQL 5.6.4）问世，这意味着InnoDB从MySQL 5.6.4.开始就支持全文索引。当使用FULLTEXT索引时，它会在文本中查找关键字，而不是直接将值与索引中的值进行比较。
  • 索引对InnoDB起着非常重要的作用–InnoDB在访问行时会锁定它们，因此减少InnoDB访问的行数可以减少锁定。
  • MySQL允许在同一列上使用重复索引。
  • 某些存储引擎具有某些默认索引类型（例如，对于MEMORY存储引擎，默认索引类型为哈希）

小结

在这部分关于MySQL中的索引的内容中，我们已经介绍了一些与此关系数据库管理系统中的索引相关的常规内容。在接下来的博客帖子中，我们将介绍一些在MySQL中使用索引的更深入的场景，包括在某些存储引擎中使用索引等，我们还将解释如何使用ClusterControl 在MySQL中实现性能目标。