DEV Community

Ryan Zhi
Ryan Zhi

Posted on

MySQL Slow Query Optimization Approaches

Execution Plan and Index Optimization

Use EXPLAIN to query the execution plan and check if indexing or other operations are needed, or to identify scenarios where indexes might be ineffective. This can help guide optimizations such as adding or adjusting indexes.

Adjust Connection Numbers on Both Ends

MySQL uses long connections for communication. Adjust the maximum number of connections on both sides. ORM frameworks typically provide built-in parameters for this adjustment. By default, MySQL has a max connection limit of 151, which can be increased up to 16,384 using configuration commands.

Adjust Memory Size

Adjust the size of the buffer pool to improve its hit rate, which can optimize query performance. Increasing the buffer pool size allows more data to be cached in memory, reducing disk I/O operations.

Physical Optimization

Replace mechanical hard drives with faster solid-state drives (SSDs). SSDs offer faster read and write speeds, significantly improving the overall performance of database operations.

Sacrifice Isolation

Trade-off consistency by choosing a non-transactional database engine or lowering the isolation level of Multi-Version Concurrency Control (MVCC). This can improve performance at the cost of reducing the level of consistency.

If there are any issues with the tweet, please feel free to discuss them with me:)

MySQL中慢SQL优化思路

1.执行计划索引优化

通过explain查询执行计划,看看是否需要加索引之类的操作,或者索引失效等场景优化

2.更改两端的连接数

MySQL是基于长连接通信的,调整两端的最大连接数,ORM框架一般自带这些参数调整,MySQL通过指令修改默认是151最大是16384

3.调整内存大小

调整buffer pool 大小,提高buffer pool的命中率,从而优化

4.物理优化

从机械硬盘更换更快的固态硬盘

5.舍弃隔离性

牺牲一致性,选择非事务的数据库引擎,或者降低MVCC的隔离级别

Warp.dev image

The best coding agent. Backed by benchmarks.

Warp outperforms every other coding agent on the market, and gives you full control over which model you use. Get started now for free, or upgrade and unlock 2.5x AI credits on Warp's paid plans.

Download Warp

Top comments (0)

Embedded BI Dashboards are 💩 Building them yourself is 💩

Embedded BI Dashboards are 💩 Building them yourself is 💩

Use our developer toolkit to build fast-loading, native-feeling dashboards for your customers (without the sh*t).

Get early access

👋 Kindness is contagious

Delve into a trove of insights in this thoughtful post, celebrated by the welcoming DEV Community. Programmers of every stripe are invited to share their viewpoints and enrich our collective expertise.

A simple “thank you” can brighten someone’s day—drop yours in the comments below!

On DEV, exchanging knowledge lightens our path and forges deeper connections. Found this valuable? A quick note of gratitude to the author can make all the difference.

Get Started