mysql statistics to check several tables speed is very slow how to solve?

dousar 注册会员
2023-02-28 07:01

1 Add an index
2 like 'aaa%' to remove an index
3 sub-library and sub-table

ddssunman 注册会员
2023-02-28 07:01

From a business perspective, if the statistics team's reports are not so real-time, such as requiring statistics at 8 PM, you can consider using stored procedures or custom functions, and start statistics to a new table at 7 PM. Then the code directly looks up the new table.

If the real-time performance is strong, the keyword of the associated query can be specified as the index.

The main said dozens of queries, is to use the code to query the data out and then to query another table, can consider a sql query out, so as to reduce the number of IO interaction with the data, but also reduce the code to calculate the data memory consumption.

cyf8448 注册会员
2023-02-28 07:01

Since it is a report, you can request not real-time query, you can use a scheduled task, maintain these results into a new table, and then update them periodically, when the report is presented, You only need to query the data of a single table.

caisubao 注册会员
2023-02-28 07:00

Two ideas:
1. data field redundancy. At the business level, the number of team, VIP number, recharge number and withdrawal number are taken as data fields, which will be updated in real time every time the business operation. So the report query only needs a single table on the line
2, sql level: let the query by index, from the business level to avoid like practice

douniwan710 注册会员
2023-02-28 07:00

the reference answer GPT ᴼ ᴾ ᴱ ᴺ ᴬ ᴵ < br / > you can consider to merge these queries into a SQL query, so can reduce the number of visits database. You can join all the tables together using joins, and then use subqueries and aggregation functions in query statements to tally up information for each team. Here is a possible example SQL query statement:

  m.id AS team_id,
  COUNT(m2.id) AS team_members_num,
  SUM(CASE WHEN m2.level != 1 THEN 1 ELSE 0 END) AS team_vip_num,
  SUM(r.amount) AS team_recharge,
  SUM(CASE WHEN TO_DAYS(r.created_at) = TO_DAYS(now()) THEN r.amount ELSE 0 END) AS team_day_recharge,
  SUM(w.amount) AS team_withdrawal,
  SUM(CASE WHEN TO_DAYS(w.created_at) = TO_DAYS(now()) THEN w.amount ELSE 0 END) AS team_day_withdrawal
FROM member m
  LEFT JOIN member m2 ON m2.tuanpath LIKE CONCAT(m.tuanpath, ',%') OR m2.tuanpath = m.tuanpath
  LEFT JOIN memberrecharge r ON r.userid = m2.id AND r.status = 1 AND r.type = '用户充值'
  LEFT JOIN memberwithdrawal w ON w.userid = m2.id AND w.status = 1
WHERE m.inviter = '0' AND m.mtype = 0 AND m.tuanpath NOT LIKE '%,%'

SQL query concatenates all the tables together and then uses aggregate functions such as COUNT and SUM for each team. LEFT JOIN member m2 ON m2.tuanpath LIKE CONCAT(m.tuanpath, ',%') OR m2.tuanpath = m.tuanpath is used to find all the members of each team, LEFT JOIN memberrecharge r ON r.serid = m2.id AND r.schedatus = 1 AND r.type = 'User recharge' and LEFT JOIN memberwithdrawal w ON W.serid = m2.id AND w.status = 1 are used to find the top-up and withdrawal information for each team. Finally, GROUP BY m.i is used to group statistics by team ID.

Using this SQL query can reduce the number of database accesses and greatly improve the query efficiency.

sharonrain 注册会员
2023-02-28 07:00

like queries certainly cost a lot of performance. It is recommended to implement specific data storage optimization, such as separate use of additional relational intermediate tables to maintain relevant information.

dicongyan 注册会员
2023-02-28 07:00

When you need to query data from multiple tables, the query efficiency may be low. In this case, you can optimize SQL statements or use indexes.

Optimizing SQL statements: Optimizing SQL statements reduces the time for querying data and improves query efficiency. Specific optimization methods include: reasonable selection of table join mode, use of appropriate query conditions, avoid the use of SELECT * and other operations.

Use indexes: Indexes can help the database quickly locate required data and improve query efficiency. Using the appropriate index in the query can effectively reduce the query time. Common indexes include B-Tree index, hash index, full-text index, and so on.

In addition, you can also consider upgrading the hardware facilities of the database, such as increasing the memory and improving the disk I/O speed. At the same time, you can also consider using caching and other technologies for optimization to cache the query results in memory and improve the query speed.

dfzxdmy110 注册会员
2023-02-28 07:00

The following answers are quoted from GPT-3 model, please use them wisely:

1.使用 Redis 来做缓存,当查询到数据后直接写入缓存,之后只需要缓存即可,降低数据库访问; 
2.使用 MySQL 索引优化+汇总表,此时可以首先在 MySQL 上进行索引优化,针对查询字段增加索引,优化 SQL 效率,然后增加一张汇总表,每次数据变化时,把计算好的数据存储到汇总表中,只查询汇总表即可,实现只查询一次就能获得的所有信息。 
下面是使用 MySQL 汇总表的示例代码: 
// 创建汇总表 
CREATE TABLE `team_summary` ( 
    `tuanPath` char(50) DEFAULT NULL, 
    `teamNum` int(11) DEFAULT 0, 
    `teamVIPNum` int(11) DEFAULT 0, 
    `teamRecharge` decimal(10,2) DEFAULT 0, 
    `teamDayRecharge` decimal(10,2) DEFAULT 0, 
    `teamWithdrawal` decimal(10,2) DEFAULT 0, 
    `teamDayWithdrawal` decimal(10,2) DEFAULT 0 

// 新增、修改数据后触发表触发器,将变化的数据更新到汇总表 
CREATE TRIGGER `after_member_update` AFTER UPDATE ON `member` 
FOR each ROW 
    IF OLD.tuanPath <> NEW.tuanPath THEN 
        // 同步更新汇总表 
        UPDATE team_summary SET 
            teamNum = teamNum - 1, 
            teamVIPNum = teamVIPNum - IF(OLD.mtype=0 AND OLD.level != 1, 1, 0) 
        WHERE tuanPath = OLD.tuanPath; 
        UPDATE team_summary SET 
            teamNum = teamNum + 1, 
            teamVIPNum = teamVIPNum + IF(NEW.mtype=0 AND NEW.level != 1, 1, 0) 
        WHERE tuanPath = NEW.tuanPath; 
    END IF; 

// 查询团队报表 
SELECT * FROM team_summary ORDER BY tuanPath;

Please accept my answer if it answers your question

daa8783 注册会员
2023-02-28 07:00

1. This is not an SQL problem. Loop nested queries are definitely slow. Consider using recursive queries, chain calls

dsjack1412 注册会员
2023-02-28 07:00

Please specify the table name and corresponding field name, and the meaning of the field name.

About the Author

Question Info

Publish Time
2023-02-28 07:00
Update Time
2023-02-28 07:00

Related Question

Boto3 AttributeError: 'CloudFormation'对象没有属性'import_stacks_to_stack_set'

pytorch 加载数据突然变的巨慢!

How to rotate a label in a zpl 90 degrees (php)?

如何监控Kusto / Azure Data Explorer的查询结果?

AWS S3仅适用于Cognito用户

在JAVA WEB 项目中, 通过调用restart.bat批处理文件, 实现Tomcat重启问题


Could not autowire. No beans of 'RestTemplateBuilder' type found.