MySQL追梦旅途之慢查询分析建议

一、找到慢查询

• 查询是否开启慢查询记录

show variables like "%slow%";

log_slow_admin_statements：

决定是否将慢管理语句（如 ALTER TABLE 等）记录到慢查询日志中。

log_slow_extra ：

MySQL 和 MariaDB 中的一个系统变量，它控制是否在慢查询日志中记录额外的信息。当启用了慢查询日志（通过设置 slow_query_log 为 ON），并且设置了 log_slow_extra 为 ON 时，数据库服务器会在每个慢查询条目中添加更多的诊断信息。

log_slow_replica_statements ：

MySQL 和 MariaDB 中的一个系统变量，数据库服务器不仅会记录普通用户的慢查询，还会记录由从库上的 I/O 线程或 SQL 线程执行的慢查询。这可以帮助管理员识别那些在复制过程中可能引起延迟或性能问题的语句。

log_slow_slave_statements：

在复制环境中，决定是否记录从服务器上的慢查询。启用了慢查询日志（通过设置 slow_query_log = ON），并且设置了 log_slow_slave_statements = ON 时，数据库服务器不仅会记录普通用户的慢查询，还会记录由从库上的 SQL 线程执行的慢查询。这可以帮助管理员识别那些在从库上执行缓慢的复制事件，从而帮助诊断和解决复制延迟或性能问题。

slow_launch_time：

当一个新客户端连接或内部线程启动的时间超过了 slow_launch_time 指定的毫秒数时，MySQL 会将该事件记录到错误日志中。这可以帮助数据库管理员了解是否存在线程创建延迟的问题，并采取相应的措施进行优化。

slow_query_log：

这个变量决定了是否启用慢查询日志。如果设置为 ON 或 1，则表示启用了慢查询日志；如果设置为 OFF 或 0，则表示禁用。

slow_query_log_file：

定义了慢查询日志文件的位置和名称。默认情况下，它会在数据目录下创建名为 host_name-slow.log 的文件。

---

long_query_time：

设置查询需要多长时间才被记录到慢查询日志中。默认值通常是 10 秒，但可以根据需要调整。

log_slow_slave_statements：

在复制环境中，决定是否记录从服务器上的慢查询。

log_throttle_queries_not_using_indexes：

限制每分钟可以记录到慢查询日志中的不使用索引的查询数量。

//set global 只是全局session生效，重启后失效,如果需要以上配置永久生效，需要在mysql.ini（linux my.cnf）中配置

set global slow_query_log=on;

//测试用
set long_query_time=0.01

二、优化慢查询

分析查询：

id	选择标识符
select_type	表示查询的类型
table	输出结果集的表
partitions	匹配的分区
type	表示表的连接类型
possible_keys	表示查询时，可能使⽤的索引
key	表示实际使⽤的索引
key_len	索引字段的长度
ref	列与索引的比较
rows	扫描出的行数(估算的行数)
filtered	按表条件过滤的⾏百分比
Extra	执行情况的描述和说明

type字段说明：

system	表中只有一条数据，等于系统表（引擎只能使MYISAM和MEMORY）
const	使用主键或者唯一索引，可以将查询的变量转成常量。(例如：… where id=3 或者where name=‘name1’
eq_ref	类似ref,区别在于使用唯一索引，返回匹配的唯一一条数据（通常在连接时出现，例如：explain select t1.name from t1, t2 where t1.name= t2.name）
ref	非唯一性索引，可以返回多行匹配的数据
range	范围查询，使用索引返回一个范围中的行（例如：… where id >3)
index	以索引顺序进行全表扫描，优点是不用排序，缺点是还要全表扫描
all	全表扫描，应尽量避免

extra字段说明：

using index	使用了覆盖索引，覆盖索引的好处是一条SQL通过索引就可以返回我们需要的数据, 不需要通过索引回表
using index condition	在5.6版本后加入的新特性：索引下推（Index Condition Pushdown），索引下推是在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。。查询的列不完全被索引覆盖，where条件中是一个前导列的范围。会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行； using index condition = using index + 回表 + where 过滤
using where	查询时没使用到索引，然后通过where条件过滤获取到所需的数据
using temporary	表示查询时，mysql使用临时表保存结果。效率较低，应当尽量避免
using filesort	当SQL中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL不得不选择相应的排序算法来实现，这时就会出现Using filesort，效率较低，应该尽量避免

索引优化

选择合适的索引类型：根据查询条件选择B-tree、Hash、全文索引等不同的索引类型。

B-tree 索引：

1、适用场景：

等值匹配：如 WHERE column = value
范围查询：包括  < , > , <= , >= , BETWEEN
排序操作：ORDER BY 和  GROUP BY 
部分匹配：如 LIKE 'prefix%'（前缀匹配）

2、优点：支持多种类型的查询，是大多数情况下默认的索引选择。对于频繁更新的数据表来说，维护成本相对较低。

Hash索引：

1、适用场景：

仅限等值匹配：如  WHERE column = value 
高频率的查找操作，尤其是当列值分布均匀时。

2、优点：在特定的等值查找上可以提供非常快的速度。内存中使用时性能更佳，因为它们不适用于磁盘上的存储结构。

3、缺点：不支持范围查询或部分索引列匹配。在哈希冲突较多的情况下性能会下降。

全文索引：

1、适用场景：

文本内容搜索：涉及对大段文字内容进行复杂检索，例如包含词、短语，模糊匹配，或者基于自然语言处理的查询。

搜索引擎功能：在需要实现类似Google搜索的功能时，全文索引是必不可少的。

2、优点：专门优化用于文本内容的快速检索。提供高级搜索功能，如相关性评分。

复合索引：如果查询经常一起使用多个列作为条件，可以考虑创建复合索引（即多列索引）。但要注意，索引的顺序很重要，通常应该按照最常用于过滤的列放在前面。

CREATE INDEX idx_last_first_name ON employees (last_name, first_name);

复合索引的使用规则

1、最左前缀原则：

使用复合索引中最左边的列来匹配查询条件

查询条件为WHERE last_name = 'Smith'时，索引会被使用。

查询条件为WHERE last_name = 'Smith' AND first_name = 'John'时，索引也会被使用。

查询条件为WHERE first_name = 'John'时，索引不会被使用，因为没有包含最左列last_name。

2、选择性：

尽量把选择性较高的列放在前面。选择性高的列意味着它具有更多的唯一值，能够更有效地缩小搜索范围。

3、覆盖索引：

如果查询的所有列都包含在索引中，MySQL可以直接从索引中读取数据而不需要访问实际的数据行，这被称为覆盖索引。这种情况下，查询性能会非常高。

4、维护成本：

虽然复合索引能加速查询，但它们也会增加写操作的成本，包括插入、更新和删除操作，因为每次修改数据时都需要更新索引。

避免过度索引：过多的索引会降低写入性能，并占用额外的存储空间。定期审查索引的有效性，移除不再需要的索引。

理解索引成本

1、写入开销：

每次对表进行插入、更新或删除操作时，MySQL不仅需要修改数据本身，还需要维护相关的索引。这意味着更多的I/O操作和更长的处理时间。

2、存储空间：

每个索引都需要额外的磁盘空间来存储。过多的索引会占用大量的存储资源，特别是在大型数据库中。

评估查询模式

1、分析常用查询：

使用EXPLAIN命令分析查询执行计划，确定哪些查询最频繁地被执行以及它们如何使用索引。优先为这些查询创建索引。

2、识别关键字段：

对于那些经常出现在WHERE子句、JOIN条件或ORDER BY/GROUP BY语句中的列，考虑创建索引。

限制索引数量

1、单个表上的索引：

尽量减少单个表上的索引数量，尤其是对于那些非关键字段或选择性较低（即重复值较多）的字段。

2、复合索引代替多个单列索引：

如果某些列总是共同出现在查询条件中，考虑用一个复合索引来代替多个单列索引

定期审查和优化

1、移除未使用的索引：

通过检查系统信息表（如performance_schema或information_schema）来找出长时间没有被使用的索引，并考虑移除它们。

2、合并冗余索引：

如果有两个或更多索引覆盖了相同的查询条件，保留最有效的一个并移除其余的。

测试和验证

1、在开发环境中测试：

在做出任何更改之前，在开发或测试环境中模拟生产环境的工作负载，以评估新索引的效果。

2、监控性能变化：

实施索引调整后，密切监控系统的性能指标，确保改动确实带来了预期的性能改进。

考虑业务逻辑的变化

1、适应业务发展：

随着应用程序的发展，某些业务逻辑可能会改变，导致一些曾经有用的索引变得不再必要。定期回顾业务需求，及时更新索引策略。

使用适当的索引类型

1、全文索引：

对于文本搜索场景，考虑使用全文索引而非普通的B-tree索引。

2、哈希索引：

在某些特定情况下，比如等值匹配查询，哈希索引可能比B-tree索引更高效。

谨慎对待自动工具

1、自动化工具的风险：

虽然有许多自动化工具可以帮助建议索引，但它们并不总是考虑到所有业务背景。因此，在采纳任何建议前，应仔细评估其合理性和潜在影响。

查询重构

减少嵌套子查询：尽量用JOIN替代复杂的子查询，因为JOIN通常能被更有效地优化。

理解不同类型的JOIN

1、INNER JOIN/(JOIN)：

返回两个表中匹配的所有记录。

2、LEFT JOIN (或 LEFT OUTER JOIN)：

返回左表中的所有记录，即使右表中没有匹配项。对于那些在右表中没有匹配的行，结果集中将包含NULL值。

3、RIGHT JOIN (或 RIGHT OUTER JOIN)：

与LEFT JOIN相反，它会返回右表中的所有记录。

4、FULL JOIN (或 FULL OUTER JOIN)：

当任一表中存在匹配时，返回行。如果某一行在一个表中有对应但在另一个表中没有，则结果集将包含NULL值。

两个简单的表 table1 和 table2，它们都有一个共同的列 id 作为连接条件：

table1

id	name
1	Alice
2	Bob
3	Carol

table2

id	age
2	30
3	35
4	40

执行上述查询后，你可能会得到类似下面的结果集：

id	name	age
1	Alice	NULL
2	Bob	30
3	Carol	35
4	NULL	40

MySQL 不直接支持 FULL JOIN。然而，你可以通过结合使用 LEFT JOIN 和 RIGHT JOIN 来模拟 FULL JOIN 的效果：

SELECT *
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

5、CROSS JOIN：

返回两个表的笛卡尔积，意味着第一个表中的每一行都会与第二个表中的每一行组合。结果集中包含的是所有可能的行对，其数量等于两个表中行数的乘积。

SELECT *
FROM table1
CROSS JOIN table2;


SELECT *
FROM table1, table2;

table1

id	name
1	Alice
2	Bob

table2

id	city
1	Beijing
2	Shanghai

执行 CROSS JOIN 后的结果将是：

table1.id	name	table2.id	city
1	Alice	1	Beijing
1	Alice	2	Shanghai
2	Bob	1	Beijing
2	Bob	2	Shanghai

将子查询转换为JOIN

1、IN子查询转换为JOIN

//包含IN子查询
SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');


SELECT 
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA';

2、EXISTS子查询转换为LEFT JOIN

SELECT *
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = e.id);

//
SELECT e.*
FROM employees e
LEFT JOIN departments d ON d.manager_id = e.id
WHERE d.manager_id IS NOT NULL;

考虑使用派生表（Derived Tables）或CTE（Common Table Expressions）

有时，直接用JOIN替换子查询可能会导致复杂的查询结构。在这种情况下，可以考虑使用派生表或公共表表达式（CTE），它们可以让你先执行一部分查询，然后再基于这些中间结果进行JOIN。

WITH department_managers AS (
    SELECT manager_id
    FROM departments
)
SELECT e.*
FROM employees e
INNER JOIN department_managers dm ON e.id = dm.manager_id;

优化JOIN顺序

1、最小化初始结果集：

选择行数最少的表作为驱动表（即最先进行连接的表），可以减少后续连接操作的数据量。

通过先对较小的结果集进行连接，然后再逐步加入更大的表，可以有效地控制每次连接后的输出规模。

2、基于过滤条件：

如果有严格的 WHERE 条件作用于某个表，那么将该表放在前面可以尽早地减少数据量。

3、高选择性索引：

如果某些表上的列有非常高的选择性（即很少重复值），并且这些列上有有效的索引，应该优先考虑使用这些表进行早期连接。这有助于迅速缩小结果集。

-- customers 表可能是相对较小的表，而 orders 和 order_items 可能较大。通过首先连接 customers 和 orders，我们可以利用 WHERE 条件或索引来快速定位相关记录，然后再扩展到 order_items。

SELECT c.customer_id, c.name, o.order_id, oi.item_id, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id 
JOIN order_items oi ON o.order_id = oi.order_id;
WHERE c.city = 'Beijing';

实际执行顺序：

-- 过滤 customers 表：由于 WHERE 条件 c.city = 'Beijing' 只作用于 customers 表，优化器可能会首先应用这个条件，只选择来自北京的客户记录。这减少了后续连接操作的数据量。

FROM customers c WHERE c.city = 'Beijing'

-- 连接 orders 表：接下来，使用经过过滤的 customers 表与 orders 表进行连接，基于 c.customer_id = o.customer_id。因为此时 customers 表已经被筛选过，所以参与连接的行数较少，可以更快地完成连接操作。

JOIN orders o ON c.customer_id = o.customer_id

-- 连接 order_items 表：最后，将前两步产生的结果集与 order_items 表连接，基于 o.order_id = oi.order_id。由于前面已经减少了数据量，这次连接也会更加高效。

JOIN order_items oi ON o.order_id = oi.order_id

利用覆盖索引

SELECT c.customer_id, c.name, o.order_id, oi.item_id, oi.product_name, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.city = 'Beijing';

-- 索引应该至少包含 customer_id 和 city，并且如果可能的话，还应包含 name，以确保它能够覆盖查询中的 SELECT 和 WHERE 部分。

CREATE INDEX idx_customers_city_name ON customers(city, customer_id, name);

-- 索引应该至少包含 customer_id 和 order_id，这两个字段用于连接条件。

CREATE INDEX idx_orders_customer_order ON orders(customer_id, order_id);

-- 索引应该至少包含 order_id，并且如果可能的话，还应包含 item_id、product_name 和 quantity，以便完全覆盖查询。

CREATE INDEX idx_order_items_order_details ON order_items(order_id, item_id, product_name, quantity);

通过这种方式，你可以确保查询可以直接从索引中获取所有需要的数据，而不需要访问实际的表数据。（但从而引来的就是所有太多占用更多的存储空间，频繁数据更新的相关字段索引也会频繁更新）

使用 UNION ALL 代替 ****OR：

当OR条件跨越多个列，并且这些列上有不同的索引时，数据库优化器可能难以选择最优的索引策略。

在某些情况下，将 OR 条件拆分为两个查询并使用 UNION ALL 合并结果可能会更快。

-- country和total_amount上有不同的索引，那么上面的OR条件可能不会很好地利用这两个索引

SELECT * FROM orders
WHERE country = 'USA' OR total_amount > 1000;

SELECT * FROM orders WHERE country = 'USA'
UNION ALL
SELECT * FROM orders WHERE total_amount > 1000
AND NOT EXISTS (SELECT 1 FROM orders o2 WHERE o2.id = orders.id AND o2.country = 'USA');

优化分组和聚合：确保在 GROUP BY 中只包含必要的字段，并且这些字段上有适当的索引。

SELECT region, city, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region, city;

为了优化这个查询，我们可以为 region 和 city 创建一个复合索引：

CREATE INDEX idx_region_city ON sales (region, city);

分页优化

键集分页：对于大数据集，使用主键或唯一键进行分页可以避免偏移量问题带来的性能下降。

覆盖索引：当使用分页时，确保索引能够覆盖查询的所有列，以减少I/O操作。

缓存策略

查询缓存：虽然现代版本的一些数据库如MySQL已经弃用了内置查询缓存，但在应用层面上实现类似的功能仍然有效。

分布式缓存：使用Redis、Memcached等内存缓存系统来缓存频繁访问的数据。

数据库配置调整

调整缓冲池大小：增大InnoDB缓冲池或其他相关参数可以帮助提高读取速度。

调整InnoDB缓冲池大小是优化MySQL性能的一个重要方面，尤其是对于读密集型的应用。通过合理设置缓冲池大小，可以显著提高数据读取速度和整体数据库性能。

1、理解 InnoDB 缓冲池

InnoDB缓冲池（InnoDB Buffer Pool）用于缓存表数据和索引，减少磁盘I/O操作。更大的缓冲池意味着更多的数据可以直接从内存中读取，从而加快查询响应时间。

在较新的MySQL版本中，默认的缓冲池大小可能已经比较大，但对于生产环境来说，通常还需要根据实际情况进行调整。

2、评估当前系统资源

检查服务器上有多少可用RAM。确保为操作系统、其他应用程序以及MySQL本身留出足够的空间。一般建议将70%-80%的物理内存分配给InnoDB缓冲池，但具体比例取决于你的应用需求和硬件配置。

了解数据库的工作负载类型（如读多写少、写多读少等），以便更好地确定缓冲池的合适大小。

3、调整 innodb_buffer_pool_size 参数

[mysqld]
innodb_buffer_pool_size = XG  # X 是你想要分配给缓冲池的GB数

-- 如果你有64GB的RAM，并且决定将50GB分配给InnoDB缓冲池
innodb_buffer_pool_size = 50G

总物理内存 (RAM) = 64GB
操作系统和其他服务所需内存 = 8GB
InnoDB 数据库的数据总量 = 50GB

-- 数据总量 是指 InnoDB 表的数据和索引占用的空间总和（可以通过 information_schema.tables 或 SHOW TABLE STATUS 获取）。这个值确保不会将比实际数据库更大的空间分配给缓冲池，从而浪费资源。

可用内存 = 64GB - 8GB = 56GB


innodb_buffer_pool_size = min⁡(56GB×0.75,50GB)=min⁡(42GB,50GB)=42GB

一般是可用内存的70%

-- 估算每个表的数据和索引占用的空间大小（以字节为单位），可以查询 DATA_LENGTH 和 INDEX_LENGTH 字段


-- MB
SELECT TABLE_SCHEMA, TABLE_NAME,
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_size_mb
FROM information_schema.tables;

4、启用多个缓冲池实例

对于拥有大量内存的服务器，启用多个缓冲池实例（innodb_buffer_pool_instances）可以减少争用锁的情况，进一步提升并发性能。默认情况下，这个值设为8个实例，可以根据需要增加：

innodb_buffer_pool_instances = 16

通过将缓冲池划分为多个实例，可以减少当多个线程同时访问缓冲池时发生的锁争用，进而提升高并发情况下的查询性能。

不要一次性大幅增加实例数量，而是逐步调整并观察其对性能的影响，找到最佳平衡点。

CPU核心数 = 16
innodb_buffer_pool_size = 50GB

推荐实例数=max(1,min(16,⌊50/1⌋))=max(1,min(16,50))=16

⌊50/1⌋:向下取整，⌊innodb_buffer_pool_size（50G）/一个实例至少占多少空间（1G）⌋

5、考虑其他相关参数

innodb_buffer_pool_load_at_startup 和 innodb_buffer_pool_dump_at_shutdown：这两个参数允许你在关闭数据库时保存缓冲池内容，并在启动时加载这些内容，以减少预热时间。

1、innodb_buffer_pool_dump_at_shutdown

当 MySQL 数据库正常关闭时，如果启用了 innodb_buffer_pool_dump_at_shutdown 参数，InnoDB 会将缓冲池中经常访问的数据页（即“热数据”）的信息保存到磁盘上的一个或多个文件中。这些文件通常位于 MySQL 的数据目录下，默认命名为 ib_buffer_pool。

[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON




ON：启用此功能，在关机时保存缓冲池内容。
OFF（默认）：禁用此功能，不保存缓冲池内容。

加快预热速度：通过保存热数据信息，下次启动时可以更快地恢复到接近关机前的状态，减少冷启动带来的性能损失。

2、innodb_buffer_pool_load_at_startup

当 MySQL 数据库启动时，如果启用了 innodb_buffer_pool_load_at_startup 参数，InnoDB 会在启动过程中读取之前保存的缓冲池内容，并将其重新加载回内存中的缓冲池。这使得数据库能够迅速恢复到接近上次关机时的状态，减少了查询需要从磁盘加载数据的时间。

[mysqld]
innodb_buffer_pool_load_at_startup = ON

ON：启用此功能，在启动时加载之前保存的缓冲池内容。
OFF（默认）：禁用此功能，不加载之前保存的内容。

提升启动性能：显著缩短了数据库在重启后达到最佳性能状态所需的时间，特别是在拥有大量数据和大缓冲池的情况下。

---

innodb_old_blocks_pct 和 innodb_old_blocks_time：用于管理LRU列表的老化策略，适当调整可以帮助保持热点数据在缓存中更长时间。

1、innodb_old_blocks_pct

innodb_old_blocks_pct 参数控制了 LRU 列表中“旧区”（old sublist）所占的比例。当新的数据页被加载到缓冲池时，它们首先会被放置在 LRU 列表的“新区”（young sublist）。如果这些页面在此后的短时间内没有再次被访问，则会被移动到“旧区”。这个参数定义了“旧区”在整个 LRU 列表中的最大比例，默认值为 37%，即大约 37% 的缓冲池空间用于存储不太活跃的数据页。

[mysqld]
innodb_old_blocks_pct = 40


允许的取值范围是 5 到 95

提高命中率：适当增加 innodb_old_blocks_pct 可以保留更多可能再次访问的数据页，从而提高缓存命中率。

减少抖动：对于工作负载中存在大量扫描操作的情况，较高的 innodb_old_blocks_pct 可以减少频繁地将热数据挤出缓冲池的风险。

2、innodb_old_blocks_time

innodb_old_blocks_time 参数决定了新加载的数据页需要在“新区”停留多长时间才会被认为足够“冷”，并被移动到“旧区”。默认情况下，这个时间是 0 毫秒，意味着新加载的数据页会立即被视为候选者，可以随时被移到“旧区”。

[mysqld]
innodb_old_blocks_time = 1000

单位：毫秒。



保护热数据：通过设置一个非零值，可以给新加载的数据页一个“宽限期”，在这段时间内即使不被再次访问也不会马上移至“旧区”，这有助于保护那些可能会很快再次使用的数据页。

适应不同工作负载：对于包含大量顺序扫描的工作负载，适当增加 innodb_old_blocks_time 可以避免不必要的缓存污染，因为扫描操作往往会引入大量暂时不需要的数据页。

---

innodb_adaptive_hash_index：启用自适应哈希索引可以在某些场景下加速查找操作，但在高并发环境下可能会导致额外开销，因此应根据具体情况开启或关闭此功能。

1、innodb_adaptive_hash_index：

优化并发设置：根据应用程序的需求调整最大连接数、锁等待超时等参数。

1、调整最大连接数（max_connections）

max_connections 参数定义了 MySQL 服务器允许的最大并发客户端连接数量。默认值通常是 151，但对于生产环境来说，这个值可能需要根据实际情况进行调整。

[mysqld]
max_connections = 1000

SET GLOBAL max_connections = 1000;


定期检查当前连接数 (SHOW STATUS LIKE 'Threads_connected';) 和最大连接数 (SHOW VARIABLES LIKE 'max_connections';) 来评估是否需要进一步调整。

2、设置锁等待超时（innodb_lock_wait_timeout）

innodb_lock_wait_timeout 参数指定了 InnoDB 存储引擎在尝试获取锁时等待的时间长度（以秒为单位）。如果一个事务无法在指定时间内获得所需的锁，则会触发超时并回滚该事务。

[mysqld]
innodb_lock_wait_timeout = 50

SET GLOBAL innodb_lock_wait_timeout = 50;

减少死锁：适当缩短锁等待时间可以减少长时间持有锁导致的死锁问题。

快速失败：较短的超时时间可以让应用程序更快地意识到冲突，并采取相应的措施（如重试操作），而不是无限期地等待。

用户体验：合理的超时设置有助于提供更好的用户体验，避免用户长时间等待响应。

3、优化其他相关参数

thread_cache_size：控制用于缓存线程的大小。适当的值可以帮助加速新连接的创建过程，特别是在频繁建立和断开连接的情况下。

table_open_cache：设置打开表的缓存大小。较大的缓存可以减少每次查询都需要重新打开表所带来的开销。

innodb_buffer_pool_size：虽然主要影响读写性能，但足够大的缓冲池也有助于减轻高并发带来的压力。

innodb_thread_concurrency：限制 InnoDB 内部线程的并发度。默认情况下是 0，表示不受限。对于某些硬件平台，适当设置这个值可以改善性能。

定期维护

分析和优化表：定期运行 ANALYZE TABLE 和 OPTIMIZE TABLE 命令（适用于MySQL），以更新统计信息和整理碎片。

ANALYZE TABLE 命令用于更新存储引擎（如 InnoDB 或 MyISAM）中关于表的统计信息。这包括索引分布、数据分布等元数据，有助于查询优化器更好地选择执行计划。

-- 使用场景
1、当你怀疑查询优化器选择了次优的执行计划时。
2、表结构或数据发生了显著变化后（如大量插入、删除或更新操作）。
3、定期维护任务的一部分，以确保统计信息始终是最新的。


-- 注意事项
1、对于 InnoDB 表，ANALYZE TABLE 通常不会锁定表，但在某些情况下可能会短暂地影响并发操作。
2、在高并发环境中，建议在低峰时段执行此操作以最小化对生产的影响。

OPTIMIZE TABLE 命令主要用于整理表中的碎片，回收未使用的空间，并重建索引。这对于 MyISAM 表尤其有用，因为它们更容易产生碎片。对于 InnoDB 表，虽然也有一定的效果，但通常不需要频繁执行，除非确实存在明显的性能问题。

-- 使用场景
1、表经历了大量的插入、删除或更新操作，导致了碎片化。
2、表的空间利用率明显下降，需要回收未使用的空间。
3、定期维护任务的一部分，尤其是在进行了大量写入操作之后。


-- 注意事项
1、OPTIMIZE TABLE 可能会导致表被锁定，特别是在使用 MyISAM 存储引擎时，因此最好在低峰时段执行。
2、对于 InnoDB 表，OPTIMIZE TABLE 实际上会创建一个新的临时表并复制所有数据到新表中，然后用新表替换旧表。这个过程可能会消耗较多资源，所以在大表上谨慎使用。

ANALYZE TABLE/OPTIMIZE TABLE table1, table2, table3;

Table：受影响的表的全名（包括数据库名称和表名称）。
Op：执行的操作类型，对于 OPTIMIZE TABLE 来说，这将是 optimize。
Msg_type：消息类型，可以是 status、note、warning 或 error。它表示了操作的结果状态。
    status：正常完成。
    note：提供了额外信息，但不影响操作结果。
    warning：警告信息，可能需要注意，但不一定影响操作结果。
    error：错误信息，表明操作未能成功完成。
Msg_text：具体的消息文本，提供了有关操作结果或遇到问题的详细描述。

重建索引：对于大型或活跃度高的表，定期重建索引有助于保持其高效性。

1、为什么需要重建索引

1、减少碎片：随着时间推移，索引中的页可能变得分散，增加了磁盘 I/O 操作次数。

2、提升查询性能：通过重组索引，可以使数据更加紧凑，减少读取时间，特别是在范围查询和排序操作中表现明显。

3、回收空间：删除记录后留下的空隙可以通过重建索引来回收，释放未使用的空间。

4、更新统计信息：虽然 ANALYZE TABLE 也可以更新统计信息，但重建索引同时也会刷新这些信息，确保查询优化器做出更优的选择。

2、何时重建索引

1、定期维护：根据表的活跃程度设定一个合理的周期（如每月一次），作为常规维护任务的一部分。

2、数据变化显著时：当表经历了大量的插入、更新或删除操作后，应该考虑重建索引。

3、性能下降时：如果注意到查询性能有所下降，特别是那些依赖于特定索引的查询，可能是时候重建索引了。

4、存储引擎建议：某些存储引擎（如 MyISAM）更容易产生碎片，因此可能需要更频繁地重建索引；而对于 InnoDB 表，则通常不需要如此频繁。

3、如何重建索引

1、选择合适的时间窗口

低峰时段：尽量在用户活动最少的时段（如深夜或周末）进行索引重建，以减少对在线业务的影响。

短暂锁定：如果必须在高负载期间操作，选择那些能够快速完成并且锁定时间最短的方法。

2、使用 ALTER TABLE 的快速选项

ALGORITHM=INPLACE：指定此选项可以让 MySQL 尽量在原地修改表结构，而不是创建临时表。这通常比默认的 COPY 算法更快。

LOCK=NONE：尝试设置锁级别为 NONE，允许在某些类型的 ALTER TABLE 操作中并发读写访问。请注意，并非所有操作都支持无锁模式。

ALTER TABLE table_name ALGORITHM=INPLACE, LOCK=NONE, ENGINE=InnoDB;

3、并行重建索引

多线程处理：innodb_read_io_threads、innodb_write_io_threads

innodb_read_io_threads：用于并发执行读取操作的线程数，默认常是4。适当增加此值可以帮助改善 I/O 密集型读取操作的性能，特别是在有大量并发读请求的情况下。

innodb_write_io_threads：用于并发执行写入操作的线程数，默认通常也是4，与读取线程类似，适当增加此值可以帮助改善写入性能

[mysqld]
innodb_parallel_sort_threads=8
innodb_read_io_threads=8

分片表：对于非常大的表，考虑将其水平分割成多个较小的分片表，然后分别重建每个分片的索引。完成后，再将结果合并回原始表。小表不分片，大表都分片了就不需要结果合并了。

ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name ADD INDEX index_name (column_list);

监控与日志

启用慢查询日志：记录所有超过设定阈值的查询，以便后续分析。

[mysqld]
# 启用慢查询日志
slow_query_log = 1

# 指定慢查询日志文件路径（可选）
slow_query_log_file = /path/to/your/slow-query.log

# 设置慢查询的时间阈值（秒）
long_query_time = 2

# 记录不使用索引的查询（可选）
log_queries_not_using_indexes = 1

# 忽略管理语句（如 SHOW、SELECT DATABASE() 等）（可选）
log_slow_admin_statements = 0

工具来分析日志文件：

mysqldumpslow：这是 MySQL 自带的一个命令行工具，用于解析和汇总慢查询日志。

pt-query-digest：由 Percona 提供的强大工具，它可以生成详细的慢查询报告，并提供优化建议。

第三方监控工具：如 Prometheus + Grafana, Percona Monitoring and Management (PMM) 等，它们可以实时监控和可视化慢查询日志。

性能监控工具：利用Percona Monitoring and Management (PMM)、Prometheus + Grafana等工具持续监控数据库性能。

应用程序层面优化

批量处理：尽可能将多次小的操作合并成一次大的操作，减少与数据库的交互次数。

延迟加载：仅在需要时才从数据库加载数据，避免不必要的数据传输。

分区和分片

水平分区（Sharding）：对于非常大的表，可以考虑按一定规则将数据分布到多个物理表或服务器上。

垂直分区：将不常用的列分离到另一个表中，减少主表的宽度，从而加快查询速度。