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的隔离级别
Top comments (0)