GX博客

如果没有索引，MySQL 在查找相关行时就必须扫描整个表。MySQL 可以通过索引来筛选 WHERE、ORDER BY 和 GROUP BY 子句中的行，也可以使用索引来连接表。

MySQL 中的索引可以分为主键（聚簇索引）和辅助键（二级索引）。Innodb 基于主键来储存磁盘上的实际数据，主键直接指向物理行，所以基于主键的查询非常快。

主键的选择和使用限制：

• 它必须是唯一和非空的。
• 每张表只能建一个主键。
• 因为 Innodb 的二级索引中都会存储主键，为了节省空间应选择最小的可能键作为主键。
• 选择一个单调递增的值作为主键。物理行是根据主键排序的，这样可以减少多次重排。
• 如果没有合适的列，可添加一个 AUTO_INCREMENT 列作为主键。

为表添加主键：

ALTER TABLE `your_table_name` ADD PRIMARY KEY (`key_name`);

索引的选择：

• WHERE、ORDER BY 和 GROUP BY 子句中使用的列。
• 用于连表的列。

为表添加索引：

ALTER TABLE `your_table_name` ADD INDEX `your_index_name` (`col_name_1`,`col_name_2`);

对于以上的组合索引，优化器只能使用最左边的前缀，即仅指定 `col_name_2` 并不能使用到索引。对于封装在函数中的列也不能使用索引，这时我们可以为表添加一个生成列，查询时基于生成列进行筛选：

ALTER TABLE `your_table_name` ADD `create_year` YEAR AS (YEAR(`create_date`)) VIRTUAL, ADD INDEX (`create_year`);

为表删除索引：

ALTER TABLE `your_table_name` DROP INDEX `your_index_name`;

如果用于测试，我们不需直接删除索引，而是先把它设置成不可见，后面可以再恢复。这样可以节省删除和创建索引的时间。

ALTER TABLE ALTER INDEX `your_index_name` INVISIBLE;

ALTER TABLE ALTER INDEX `your_index_name` VISIBLE;

如何检查索引优化效果？

我们可以使用EXPLAIN语句来检查 MySQL 是如何执行 SELECT，DELETE，INSERT，REPLACE，UPDATE 语句的。对于索引优化，我们需要关注 EXPLAIN 语句输出的几个字段：

• table:

  一般指输出行引用的表名称。

• type:

  连接类型，反应了扫描索引和表中行的行为。

• possible_keys:

  它表示 MySQL 查询表中行时可选择的索引。注意，因为它独立于输出表的顺序，所以它给出的索引未必适合于你按特定顺序生成的输出表。

  如果它为 NULL，你可以检查 WHERE 子句是否可以引用合适的索引列来提高查询性能。要查看表中包含的索引，可以使用以下语句：

  SHOW INDEX FROM `your_table_name`;

• key:

  它表示 MySQL 实际决定使用的索引。key 的值可能不存在于 possible_keys 中，但该索引覆盖了所选列。在 InnoDB 中所有辅助索引都会储存主键，可以借此确定要检索的行。

  如果 key 值为 NULL，表示 MySQL 没有找到更有效执行查询的索引。

  要强制 MySQL 使用或忽略 possible_keys 中列出的索引，可以使用索引提示。

• ref：

  它表示用于与索引做比较的列或常量。

• rows:

  它表示 MySQL 认为执行该查询必须检查的行数。对于 InnDB 表，这是一个估算值，可能并不总是准确的。

• Extra:

  它包含 MySQL 如何解析查询的其它信息。如 Backward index scan 表示通过反向扫描索引查找行；Using filesort 表示没有可用索引而使用了 filesort 操作来满足 ORDER BY 子句查询。要了解更多说明，请点击这里。

当不能根据 EXPLAIN 结果做出决定时，我们可以使用 MySQL 客户端自带的mysqlslap工具模拟服务器的客户端负载，最后根据统计的耗费时间，决定使用哪个查询。

在 Windows 系统，我们先进入 MySQL 的安装目录，如：

cd C:\Program Files\MySQL\MySQL Server 8.0\bin

然后执行 mysqlslap 的命令：

mysqlslap -u <user> -p<pass> --create-schema=your_db_name --query="your_sql_query;" -concurrency=100 --iterations=100

• --create-schema

  用于运行测试的 schema。

• --query

  包含用于检索数据的 SELECT 语句文件或字符串。

• -concurrency

  发出 SELECT 语句时要模拟的客户端数。

• --iterations

  运行测试的迭代次数。

mysqlslap 会给出运行 --query 中所有查询的平均秒数、最小秒数和最大秒数。这个平均指的是 -concurrency 的平均。

在 Web 应用程序中很常见的一类查询就是只显示较大结果集中的几行，其中包含 ORDER BY 和 LIMIT 字句。如果索引不能满足 ORDER BY 子句，则 MySQL 会执行 filesort 读取行并对其进行排序操作。

可以根据实际查询情况，增加 sort_buffer_size 变量值，使整个结果集可以在缓存区中排序，以避免写入磁盘和合并传递。如果你排序列值为长字符串列，要确保排序的准确，可以增加 max_sort_length 变量的值。则排序缓冲区元组的大小也会增加，并且可能需要你增加 sort_buffer_size。

从8.0.12开始，优化器会逐步分配排序缓冲区内存，直到最多 sort_buffer_size 字节。要了解 ORDER BY 的相关优化，请点击这里。要了解 LIMIT 的相关优化解释，请点击这里。

即使同一条查询语句，MySQL 优化器也会根据具体查询字段和字句，衡量是先使用可用索引或直接进行全表扫描。查询语句的优化也应结合具体数据量和客户端实际体验。