MySQL性能调优与SQL优化完全指南

发布于 2023-12-25 # database

排除缓存干扰

MySQL8.0没有这个机制，之下版本需要注意关闭。

Explain

通过使用EXPLAIN语句来优化慢SQL时，应该关注以下指标：

访问类型（Access Type）：访问类型表示MySQL在表中找到所需行的方式。常见的访问类型包括ALL（全表扫描）、index（使用索引扫描）、range（索引范围扫描）等。优化的目标是尽量减少全表扫描，提高索引的利用率。
索引使用情况：查看是否使用了索引，以及使用了哪些索引。确保SQL语句中涉及的列都有合适的索引来加速查询。
扫描的行数：关注rows字段，表示MySQL估计需要扫描的行数。如果这个值过大，可能需要优化查询条件或者添加索引。
连接类型：如果SQL语句涉及多个表的连接，需要关注连接类型（join type）和连接的顺序。合理的连接顺序和连接类型可以提高查询性能。
额外的排序和临时表：查看是否存在额外的排序操作或者临时表。这些操作可能会影响查询性能，需要谨慎考虑是否可以优化。
索引覆盖：检查是否可以通过索引覆盖来减少对表的访问。索引覆盖是指查询的列都可以通过索引来获取，而不需要访问实际的数据行。
使用的索引：查看是否使用了覆盖索引或者多列索引，以及索引的选择性和唯一性。

覆盖索引

覆盖索引是指一个查询语句可以通过索引的数据就能够满足查询需求，而不需要去访问实际的数据行。这种情况下，查询可以直接从索引中获取需要的数据，而不必回表查询实际的数据行，从而提高查询性能。

为了避免查询时回表，可以考虑以下几点：

选择合适的索引：确保为查询语句中涉及的列创建合适的索引。覆盖索引需要包含查询语句中涉及的所有列，因此需要根据实际的查询需求来选择合适的索引。
避免使用SELECT * 查询：尽量避免使用SELECT * 查询所有列的数据，而是明确列出需要查询的列。这样可以减少不必要的数据访问，提高查询效率。
使用索引覆盖：在设计查询语句时，可以考虑使用索引覆盖来减少对实际数据行的访问。这可以通过选择合适的索引和查询列来实现。
优化查询条件：合理设计查询条件，以便利用索引来覆盖查询需求。避免使用不必要的条件或者进行全表扫描。

联合索引

联合索引是指在数据库表中，针对多个列创建的复合索引。这种索引可以涵盖多个列，而不仅仅是单个列的索引。联合索引的使用场景通常是在需要同时对多个列进行查询、排序或者过滤的情况下。

使用场景包括：

多列查询：当查询语句中涉及多个列的条件时，可以考虑使用联合索引。例如，对于WHERE子句中包含多个列的查询，联合索引可以提高查询性能。
排序：如果查询语句中包含ORDER BY子句，而且涉及多个列的排序，可以使用联合索引来加速排序操作。
覆盖索引：在一些情况下，联合索引可以作为覆盖索引来使用，从而避免查询时回表，提高查询性能。
范围查询：对于需要进行范围查询的情况，联合索引可以提高查询效率。

使用方法：

-- 创建联合索引
CREATE INDEX idx_name ON table_name (column1, column2, ...);

-- 查询时使用联合索引
SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;

在创建联合索引时，需要注意列的顺序。通常情况下，应该将最常用于过滤和排序的列放在联合索引的前面。此外，还需要考虑列的选择性和查询的频率，以便创建最适合的联合索引。

总的来说，联合索引适合于需要同时对多个列进行查询、排序或者过滤的情况，通过合理创建和使用联合索引，可以提高数据库查询的性能。

最左匹配原则

最左匹配原则是指在使用联合索引时，数据库系统会优先匹配索引的最左边的列，然后逐渐向右匹配其他列。这意味着如果查询条件中的列不是按照索引的顺序进行匹配，那么索引可能无法被充分利用，导致查询性能下降。

举个例子来说明最左匹配原则：假设有一个联合索引 (column1, column2, column3)，按照最左匹配原则，查询语句中的条件应该从索引的最左边列开始，依次向右匹配。

例如，对于以下查询：

SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;

这个查询会充分利用联合索引，因为查询条件按照索引的最左边列开始匹配。

但是，对于以下查询：

SELECT * FROM table_name WHERE column2 = value2;

这个查询无法充分利用联合索引，因为查询条件没有按照索引的最左边列开始匹配，导致索引无法被充分利用，查询性能可能会受到影响。

因此，在设计查询语句和创建联合索引时，需要考虑最左匹配原则，合理安排查询条件的顺序，以便充分利用索引，提高查询性能。

索引下推

select * from itemcenter where name like '敖%' and size=22 and age = 20;

而MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

唯一索引和普通索引在选择上的难题通常取决于具体的使用场景和需求。下面我将为您解释唯一索引和普通索引的区别以及在不同情况下的选择建议。

唯一索引普通索引选择难题

唯一索引：

唯一索引要求索引列的值必须是唯一的，不允许重复。
适合用于需要确保数据完整性和唯一性的列，比如身份证号、邮箱等。
当需要对某个列进行唯一性约束时，可以选择创建唯一索引。

普通索引：

普通索引没有唯一性要求，允许出现重复的索引列值。
适合用于普通的查询优化，提高查询性能。
当需要简单地加速查询速度，而不需要唯一性约束时，可以选择创建普通索引。

选择建议：

如果需要确保某个列的唯一性，可以选择创建唯一索引。
如果只是为了提高查询性能，而不需要唯一性约束，可以选择创建普通索引。
对于经常需要进行唯一性校验的列，可以考虑使用唯一索引。
对于频繁用于查询条件或者连接条件的列，可以考虑使用普通索引。

在实际应用中，根据具体的业务需求和数据特点来选择唯一索引和普通索引，有时候也可以根据实际情况同时使用唯一索引和普通索引来满足不同的需求。

在MySQL中，前缀索引是一种优化技术，可以加快对长字符串列的查询速度。通过只索引字符串列的部分内容，可以减小索引的大小并提高查询性能。下面是如何在MySQL中创建和使用前缀索引的示例代码：

前缀索引

-- 创建前缀索引
ALTER TABLE your_table ADD INDEX index_name (your_column_name(length));

-- 例如，如果要对people表的first_name列的前5个字符创建前缀索引：
ALTER TABLE people ADD INDEX idx_firstname (first_name(5));

-- 确认索引创建
SHOW INDEXES FROM your_table;

-- 确定前缀长度
-- 通过计算不同前缀长度的唯一值数量来确定最佳前缀长度
SELECT COUNT(DISTINCT LEFT(your_column_name, 4)) / COUNT(*) as left4 from your_table;
SELECT COUNT(DISTINCT LEFT(your_column_name, 5)) / COUNT(*) as left5 from your_table;
SELECT COUNT(DISTINCT LEFT(your_column_name, 6)) / COUNT(*) as left6 from your_table;
-- 选择最小的前缀长度，以实现接近完全选择性的索引

-- 使用前缀索引进行查询
-- 例如，使用前缀索引进行查询：
SELECT * FROM your_table WHERE your_column_name LIKE 'prefix%';

通过以上示例代码，您可以在MySQL中创建前缀索引，并确定适当的前缀长度，以优化数据库的查询性能。

条件字段函数操作

条件字段函数操作通常指的是在数据库查询中使用条件函数来操作字段。在MySQL中，可以使用诸如IF函数、CASE语句等条件函数来对字段进行操作和处理。

以下是一些常见的条件字段函数操作示例：

使用IF函数进行条件判断：

SELECT column_name, IF(sales > 1000, 'High', 'Low') AS sales_category FROM your_table;

上述示例中，IF函数根据销售额是否大于1000来判断销售额的分类。

使用CASE语句进行条件判断和操作：

SELECT column_name,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
  END AS new_column
FROM your_table;

在这个示例中，根据不同的条件进行判断，并返回不同的结果。

这些条件字段函数操作可以帮助您在数据库查询中根据特定条件对字段进行灵活的操作和处理。

隐式类型转换

select * from t where id = 1

如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引，为啥呢？

因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引。

以下是一些关于隐式类型转换的常见问题和解决方法：

数据类型不匹配导致的隐式类型转换：当对不同数据类型的字段进行操作时，MySQL会自动进行隐式类型转换。例如，当一个整数字段与一个字符串字段进行比较时，MySQL会将字符串字段转换为整数类型，这可能导致意外的结果。
避免隐式类型转换的方法：
- 显式地使用CAST或CONVERT函数将数据类型转换为一致的类型。
- 确保比较的字段具有相同的数据类型。
注意隐式类型转换可能带来的性能问题：隐式类型转换可能会影响查询的性能，因为它会导致数据库系统进行额外的计算。因此，尽量避免不必要的隐式类型转换，以提高查询性能。

总之，在编写MySQL查询时，应该特别注意隐式类型转换可能带来的问题，并尽量避免不必要的隐式类型转换，以确保查询结果的准确性和性能。

隐式字符编码转换

还是一样的问题，如果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，所以一旦两个字符比较，就会转换为utf8mb4再比较。

转换的过程相当于加了CONVERT(id USING utf8mb4)函数，那又回到上面的问题了，用到函数就用不上索引了。

还有大家一会可能会遇到mysql突然卡顿的情况，那可能是MySQLflush了。

flush

在MySQL中，FLUSH是一个用于刷新数据库系统状态的命令。它可以用于清空查询缓存、重新加载权限表、关闭表、刷新日志文件等操作。

请注意，FLUSH命令可能会对数据库系统产生一定的影响，因此在使用时需要谨慎。

Typography

一只奶牛猫