人网站建站,上海画册设计,硬件工程师和软件工程师的区别,青岛网站建设公司 中小企业补贴一、找到慢查询
查询是否开启慢查询记录
show variables like %slow%;log_slow_admin_statements#xff1a;
决定是否将慢管理语句#xff08;如 ALTER TABLE 等#xff09;记录到慢查询日志中。
log_slow_extra #xff1a;
MySQL 和 MariaDB 中的一个系…一、找到慢查询
查询是否开启慢查询记录
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.inilinux my.cnf中配置set global slow_query_logon;//测试用
set long_query_time0.01二、优化慢查询
分析查询 id选择标识符select_type表示查询的类型table输出结果集的表partitions匹配的分区type表示表的连接类型possible_keys表示查询时可能使⽤的索引key表示实际使⽤的索引key_len索引字段的长度ref列与索引的比较rows扫描出的行数(估算的行数)filtered按表条件过滤的⾏百分比Extra执行情况的描述和说明
type字段说明
system表中只有一条数据等于系统表引擎只能使MYISAM和MEMORYconst使用主键或者唯一索引可以将查询的变量转成常量。(例如… where id3 或者where name‘name1’eq_ref类似ref,区别在于使用唯一索引返回匹配的唯一一条数据通常在连接时出现例如explain select t1.name from t1, t2 where t1.name t2.nameref非唯一性索引可以返回多行匹配的数据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
idname1Alice2Bob3Carol
table2
idage230335440
执行上述查询后你可能会得到类似下面的结果集
idnameage1AliceNULL2Bob303Carol354NULL40
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
idname1Alice2Bob
table2
idcity1Beijing2Shanghai
执行 CROSS JOIN 后的结果将是
table1.idnametable2.idcity1Alice1Beijing1Alice2Shanghai2Bob1Beijing2Bob2Shanghai
将子查询转换为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或CTECommon Table Expressions
有时直接用JOIN替换子查询可能会导致复杂的查询结构。在这种情况下可以考虑使用派生表或公共表表达式CTE它们可以让你先执行一部分查询然后再基于这些中间结果进行JOIN。
WITH department_managers AS (SELECT manager_idFROM 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 56GBinnodb_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_size50G/一个实例至少占多少空间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 ONON启用此功能在关机时保存缓冲池内容。
OFF默认禁用此功能不保存缓冲池内容。加快预热速度通过保存热数据信息下次启动时可以更快地恢复到接近关机前的状态减少冷启动带来的性能损失。2、innodb_buffer_pool_load_at_startup
当 MySQL 数据库启动时如果启用了 innodb_buffer_pool_load_at_startup 参数InnoDB 会在启动过程中读取之前保存的缓冲池内容并将其重新加载回内存中的缓冲池。这使得数据库能够迅速恢复到接近上次关机时的状态减少了查询需要从磁盘加载数据的时间。
[mysqld]
innodb_buffer_pool_load_at_startup ONON启用此功能在启动时加载之前保存的缓冲池内容。
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 1000SET 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 50SET 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 的快速选项
ALGORITHMINPLACE指定此选项可以让 MySQL 尽量在原地修改表结构而不是创建临时表。这通常比默认的 COPY 算法更快。
LOCKNONE尝试设置锁级别为 NONE允许在某些类型的 ALTER TABLE 操作中并发读写访问。请注意并非所有操作都支持无锁模式。
ALTER TABLE table_name ALGORITHMINPLACE, LOCKNONE, ENGINEInnoDB;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_threads8
innodb_read_io_threads8分片表对于非常大的表考虑将其水平分割成多个较小的分片表然后分别重建每个分片的索引。完成后再将结果合并回原始表。小表不分片大表都分片了就不需要结果合并了。
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对于非常大的表可以考虑按一定规则将数据分布到多个物理表或服务器上。
垂直分区将不常用的列分离到另一个表中减少主表的宽度从而加快查询速度。
