一、 存在的问题

1. 首次加载过慢，即/v1/init慢
2. 首次加载完，渲染慢

二、 分析存在的可能性

1.mysql性能问题

① mysql未加合适的索引

② sql语句写的有问题

2. 缓存不恰当

3. 部分接口未做分页

4. 卡片未作懒加载

5. 网络IO瓶颈

6. 逻辑不合理

7. 数据预加载

三、 排查并确认可能性

1. 挨个接口开始排查

2.

四、制定方案

1. mysql索引 √

① 开启慢查询日志

• slow_query_log 慢查询开启状态，ON开启，OFF关闭
• slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
• long_query_time 查询超过多少秒才记录

mysql> set global slow_query_log='ON'; 
mysql> set global long_query_time=1;

测试

select sleep(2);

找到对应的日志目录

C:\Program Files (x86)\mysql\bin\mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2022-02-10T06:44:20.550517Z
# User@Host: root[root] @ localhost [::1]  Id:   277
# Query_time: 2.000301  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
use a4;
SET timestamp=1644475458;
SELECT sleep(2);

② 哪些地方要加索引

左前缀原则

•  表 base_word -> name
•  表 book_word -> word + dict_code + seq
•  表 card_word -> word_name + card_id + PASSED
•  表 practice_record -> card_id
•  表 card -> openid, date, DELETED, dict_code
•  表 user -> openid
•  表 user_setting -> 主键索引 openid

③ 测试sql语句是否走了索引

• 查询词书中的单词

EXPLAIN SELECT
	* 
FROM
	book_word 
WHERE
	dict_code = '0202' 
	AND seq < 150 AND seq >= 10

• 准备复习的数据

EXPLAIN SELECT
	word_name,
	( SELECT self_def FROM self_def_word WHERE openid = 'o4nOp5Akkbm3J3aW2uNZuJp85jmg' AND self_def_word.word = card_word.word_name ) AS 'selfShortDef',
	base_word.prs_uk,
	base_word.prs_us,
	base_word.name_ch AS 'shortDef',
	(
	SELECT NOT
	ISNULL( mark_word.DELETED )) AS favor 
FROM
	card_word
	RIGHT JOIN base_word ON base_word.NAME = card_word.word_name
	LEFT JOIN mark_word ON mark_word.word = card_word.word_name 
	AND mark_word.DELETED = 0 
	AND mark_word.openid = 'o4nOp5Akkbm3J3aW2uNZuJp85jmg' 
WHERE
	card_id IN ( 1, 2, 3, 4 ) 
	AND PASSED = 0

• 根据卡片ID，获取卡片的数据

EXPLAIN SELECT
	( UNIX_TIMESTAMP( practice_time ) * 1000 ) AS practiceTime,
	seq,
	card_id 
FROM
	practice_record 
WHERE
	card_id = 25```

- 某日卡片

```sql
       explain select id               as cardId,
               (select '20220210') as date,
               dict_code,
               CREATE_DATE      as createTimeStamp
        from card
        where openid = 'o4nOp5Akkbm3J3aW2uNZuJp85jmg'
          and date = '20220210'
          and DELETED = 0

2. 数据预加载 ×

3. 缓存 √

4. 读写分离 ×

https://zhuanlan.zhihu.com/p/86108084

5. 负载均衡 √

五、验证结果

压测 ×

Druid Stat ×

小程序后端性能监控 ×

https://developers.weixin.qq.com/miniprogram/dev/framework/performance/perf_data.html