通过索引优化 MySQL 性能

作为一名工程师，其专长是应用程序而不是 MySQL 性能，这可能是压倒性的。您应该从分析查询响应时间开始，因为它是有意义且可操作的。

有意义——我们看到的唯一指标是查询响应时间。当查询需要 n 秒执行时，我们会变得不耐烦。在同一个查询中，可能会检查一百万行，但我们看不到检查一百万行。我们的时间很宝贵。

可操作——因为您拥有代码，所以可以更改查询响应时间。即使您无权访问它，您仍然可以间接优化查询响应时间。

提高数据库性能的最有效方法是正确使用索引。在数据库中，索引是一种数据结构，它将值与这些值出现在给定列中的行相关联。与使用蛮力从上到下搜索整个表相比，数据库可以使用索引更快地找到值。

大多数软件开发人员不知道如何或何时创建索引。在有关数据库的书籍和文档中很少或从不解释何时使用索引。开发人员只能猜测如何有效地使用它们。

SELECT 查询用于检索数据，通常使用 WHERE 子句过滤数据。在大多数情况下，会在查询的 WHERE 子句中指定的列上创建索引。在没有索引的情况下，数据库会扫描所有的行，过滤掉匹配的行，然后返回结果。如果有数百万条记录，此扫描操作可能需要很长时间，导致 API 和应用程序响应时间变慢。

让我们创建一个数据库以更好地理解它。InnoDB 是 MySQL 的默认数据库引擎。

CREATE TABLE sample_table (
first_name VARCHAR(32) NOT NULL,
last_name VARCHAR(32) NOT NULL,
age INT,
mobile_no VARCHAR(10) );

向该表中插入 1000 条随机记录。使用一个程序，我就能生成这些记录。

DELIMITER $$
DROP PROCEDURE generate_data;
CREATE PROCEDURE generate_data()
BEGIN
DECLARE i INT DEFAULT 0;
当 i < 1000 DO
INSERT INTO `sample_table` (`first_name`,`last_name`,`age`,`mobile_no`) 值 (
CONV(FLOOR(RAND() * 99999999999999), 10, 36),
CONV(FLOOR(RAND ) () * 99999999999999), 10, 36),
ROUND(RAND()*100,2),
LPAD(FLOOR(RAND() * 10000000000), 10, '0')
);
设置 i = i + 1;
结束；
END$$分隔符；

命令 SHOW INDEX 可用于验证没有在该表上创建索引。

到目前为止，如果我们运行一个简单的 SELECT 查询，该查询将扫描整个表，因为在 mobile_no 上没有定义索引。

EXPLAIN SELECT * from sample_table where mobile_no='5554688121';

EXPLAIN 指示查询引擎打算如何执行查询。正如您在上面的快照中看到的，rows 列返回 1000，而 possible_keys 返回 null。possible_keys 列表示可以在此查询中使用的所有索引。在所有可能的索引中，键列指示将使用哪个索引。

CREATE INDEX 语句创建一个索引，它是数据库中的唯一结构。它需要自己的磁盘空间并存储索引表数据的副本。因此，索引是纯粹的冗余。表索引不会改变表中的数据；它只是创建一个引用表的新数据结构。毕竟数据库索引与书籍索引非常相似。它们占据自己的空间，高度冗余，并引用存储在另一个位置的信息。为了创建主索引，必须使用 ALTER TABLE，而不是 CREATE INDEX。

不必创建自己的主键。由于 InnoDB 在设计上必须在每个表中都有一个主键，因此如果您没有定义主键，它会自动为您创建一个。当您稍后为该表创建主键时，InnoDB 会删除先前自动生成的主键。

现在我们没有定义主键，让我们看看 InnoDB 默认为我们创建了什么：

EXTENDED 显示用户无法访问但完全由 MySQL 管理的所有索引。MySQL 已在 DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR 以及表中的所有列上定义了复合索引。在没有用户定义的主键的情况下，该索引用于唯一地查找记录。

ALTER TABLE sample_table 添加主键（mobile_no）；

由于我随机生成了表行，因此主键的标准之一是列值在表中应该是唯一的。就我而言，我有重复的生成值，所以我不能将它们用作主键。

但是，我可以在 mobile_no 列上创建一个索引，我将在这里向您展示。

在 sample_table(mobile_no) 上创建索引 mobile_no_idx；

现在让我们看看这个索引是否减少了需要在查询的 WHERE 子句中搜索给定 mobile_no 的行数。

EXPLAIN SELECT * from sample_table where mobile_no='5554688121';

这些行只返回 1， possible_keys 和 key 都返回 mobile_no_idx。使用索引，查询优化器直接进入记录并检索它。这是非常有效的。索引的目的是以增加空间为代价来缩小搜索范围。

1. B-Tree（平衡树）索引——最常用和默认的索引类型。该索引可以与使用 =、>、>=、<、<= 和 BETWEEN & LIKE 关键字的搜索查询一起使用。

2. R-Tree（空间树）索引——利用 MySQL 的几何数据类型，它可以用来索引地理对象。

3. 哈希索引——通常与 = 或 <=> 搜索运算符一起使用。非常快，但只能在使用内存存储引擎时使用。

4. 覆盖索引— 覆盖完成查询所需的所有列的索引。

5. 聚集索引——行数据存储在此类索引中。PRIMARY KEY 或者，如果不存在，则通常使用 UNIQUE 索引

6. 复合索引——多列用于创建索引。

7. 前缀索引— 这种类型的索引允许您索引列的前缀。这些索引不索引列的完整值，因此它们经常用于节省空间。

B-tree 索引是 MySQL 中最常用的索引。只要有这样的索引，MySQL 就可以利用使用关键字 =、>、>=、<、<= 和 BETWEEN & LIKE 的搜索查询。这是可以将此类索引添加到列以加快搜索查询的主要原因之一。当使用 B 树索引时，存储引擎不必扫描整个表来查找相关行。

CREATE INDEX index_name ON table_name(column_name)
或CREATE INDEX index_name ON table_name(column_name) USING BTREE；

SELECT * from table_name where column_name <= 'value';
SELECT * from table_name where column_name BETWEEN 1 AND 100; SELECT * from table_name where column_name LIKE '%value%';

为了使用 R-Tree 或空间索引，您必须使用 MySQL 提供的地理信息系统 (GIS) 功能。MBRContains、MBRCovers 和 MBREquals 是其中的一些函数。它们可以指示一个参数的最小边界矩形是否包含、覆盖或等于另一个参数的最小边界矩形。

要将此类索引添加到表中，请使用 SPATIAL 关键字。

创建空间索引 index_name ON table_name(column_name);

MySQL 中哈希索引的主要优点是速度非常快，但主要缺点是只能用于相等比较，并且只能在 MEMORY 存储引擎上工作。也不支持范围搜索。

要在表上创建这样的索引，请在最后使用 USING HASH 选项。

CREATE INDEX index_name ON table_name(column_name) USING HASH;

在使用 MySQL 时，可能会遇到一些可能需要以不同方式使用索引的极端情况。覆盖索引是那些覆盖查询成功所必需的所有字段的索引。当存在覆盖索引时，查询可以直接从索引中检索结果，而不必访问磁盘。如果您使用的索引已经包含您要搜索的列的值，MySQL 将不会访问磁盘，而是直接从索引中检索结果。

SELECT col1, col2 FROM table_name WHERE col3 = 'value';

聚集索引不是单独的 MySQL 索引类型，它们是存储数据的不同方法。如果您的表有 PRIMARY KEY，则聚集索引就是 PRIMARY KEY。如果您的表没有 PRIMARY KEY，它是第一个将所有键列定义为 NOT NULL 的唯一索引。

使用这样的索引，表中的所有行都根据其键值进行存储和排序。表中不能有多个聚集索引，因为每一行只能有一个排序顺序。

复合索引跨越多个列。如果我们在多列上有一个索引，我们就有一个复合索引。

CREATE INDEX index_name ON demo_table(col1,col2,col3);

在使用索引字段进行选择、连接或过滤的查询中，复合索引是有益的。

SELECT * FROM table_name WHERE col1 = 'value1' AND col2 = 'val2';

在 MySQL 中，复合索引最多可以包含 16 列，但请记住 MySQL 从左到右使用索引，反之则不然。

在某些情况下，您可能需要索引冗长的列，从而使索引变得非常大。作为索引整个值的替代方法，前缀索引还可以索引列的前几个字符。

CREATE INDEX index_name ON table_name (column_name(length));

使用前缀索引，以提供良好选择性同时节省空间的方式对列进行索引很重要。

通过猜测来选择我们的索引不可避免地会导致一些错误。

1. 没有定义索引或没有足够的索引

2. 定义过多的索引或无用的索引

3. 运行没有索引的查询可以提供帮助

通常认为数据库在维护索引时会产生开销。每当我们插入、更新或删除表时，数据库必须更新其索引数据结构，以确保后续搜索能够可靠地找到正确的行集。开销通常与浪费有关。因此，当我们读到维护索引会产生开销时，我们希望消除它。在某些情况下，开发人员得出结论，删除索引是最好的解决方案。这是常见的建议，但它忽略了索引具有证明其成本合理的好处这一事实。

仅当您运行使用它的查询时，索引才有用。创建你不使用的索引是没有意义的。因为他们不知道哪些索引会对他们的查询有益，所以有些人会在每列和每列组合上创建索引。一个被索引覆盖的数据库表会产生大量开销，并且不能保证返回。

第三种错误是运行不能使用索引的查询。为了让查询运行得更快，开发人员构建了越来越多的索引，试图找到一些列或索引选项的神奇组合。

在 sample_table(last_name, first_name) 上创建索引电话 IDX；

SELECT * from sample_table ORDER BY first_name, last_name; SELECT * FROM sample_table WHERE last_name = 'Charles' OR first_name = 'Charles' ;

EXPLAIN SELECT * from sample_table ORDER BY first_name, last_name;

possible_keys 和 key 在这里都是空的，并且所有的行都被扫描了。

EXPLAIN SELECT * FROM sample_table WHERE last_name = 'Charles' OR first_name = 'Charles' ;

这里， possible_key 是telephone_idx，但是key 仍然是空的，因为这个索引没有被使用。

索引最重要的规则之一是您不应该盲目猜测。了解您的数据、了解您的查询并指导您的索引。

本文使用 文章同步助手 同步