1 Add an index
2 like 'aaa%' to remove an index
3 sub-library and sub-table
To collect the report of the team, including the number of the team, the number of VIP, the number of recharge, the number of withdrawals
First check the membership table to find the ID of all the heads
Then use the ID of all the heads of the loop to query all the team members of each head(mainly because I feel in this step, I can only use like because there are multiple ids in the field such as 123,463,345), so I can only use tuanpath LIKE concat('%', #{tuanPath}, '%') to find all the members of the team
After the query, we need to query the membership table to query the number of people and VIP, and we need to query the recharge table and the withdrawal table
A page out of 10 is equal to a page to check dozens of times, very slow, please tell me how to optimize, the following is my sql
/**
* 查询团队报表
*
* @return
*/
@Select("SELECT id, username, mobile FROM member WHERE inviter = '0'")
public List<Team> selectTeamList();
/**
* 查询团队人数
*
* @return 团队号
*/
@Select("SELECT COUNT(id) FROM member WHERE tuanpath LIKE concat('%', #{tuanPath}, '%') AND mtype = 0")
public int teamNum(Long tuanPath);
/**
* 查询团队VIP人数
*
* @return 团队号
*/
@Select("SELECT COUNT(id) FROM member WHERE tuanpath LIKE concat('%', #{tuanPath}, '%') AND mtype = 0 AND level != 1")
public int teamVIPNum(Long tuanPath);
/**
* 查询团队总充值
*
* @return 团队号
*/
@Select("SELECT SUM(r.amount) from member m inner join memberrecharge r on m.id = r.userid WHERE m.tuanpath LIKE concat('%', #{tuanPath}, '%') AND m.mtype = 0 AND r.status = 1 AND r.type = '用户充值'")
public Double teamRecharge(Long tuanPath);
/**
* 查询团队今日总充值
*
* @return 团队号
*/
@Select("SELECT SUM(r.amount) from member m inner join memberrecharge r on m.id = r.userid WHERE m.tuanpath LIKE concat('%', #{tuanPath}, '%') AND m.mtype = 0 AND r.status = 1 AND r.type = '用户充值' AND TO_DAYS(r.created_at) = TO_DAYS(now())")
public Double teamDayRecharge(Long tuanPath);
/**
* 查询团队总提现
*
* @return 团队号
*/
@Select("SELECT SUM(w.amount) from member m inner join memberwithdrawal w on m.id = w.userid WHERE m.tuanpath LIKE concat('%', #{tuanPath}, '%') AND m.mtype = 0 AND w.status = 1")
public Double teamWithdrawal(Long tuanPath);
/**
* 查询团队今日总提现
*
* @return 团队号
*/
@Select("SELECT SUM(w.amount) from member m inner join memberwithdrawal w on m.id = w.userid WHERE m.tuanpath LIKE concat('%', #{tuanPath}, '%') AND m.mtype = 0 AND w.status = 1 AND TO_DAYS(w.created_at) = TO_DAYS(now())")
public Double teamDayWithdrawal(Long tuanPath);
0 Answer
1 Add an index
2 like 'aaa%' to remove an index
3 sub-library and sub-table
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.
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.
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
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:
SELECT
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 '%,%'
GROUP BY m.id
Using this SQL query can reduce the number of database accesses and greatly improve the query efficiency.
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.
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.
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// 新增、修改数据后触发表触发器，将变化的数据更新到汇总表
CREATE TRIGGER `after_member_update` AFTER UPDATE ON `member`
FOR each ROW
BEGIN
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;
END
// 查询团队报表
SELECT * FROM team_summary ORDER BY tuanPath;
Please accept my answer if it answers your question
1. This is not an SQL problem. Loop nested queries are definitely slow. Consider using recursive queries, chain calls
Please specify the table name and corresponding field name, and the meaning of the field name.
这家伙很懒，什么都没留下...