背景
我们游戏有一个老排行榜,一直爆出数据库语句执行超时,由于是深夜处理数据,并没有影响,所以一直没处理,最近还是决定改一改
功能背景
这个游戏有一个功能是每天生成最近一段时间活跃的玩家被点赞数的排行榜
1.线上的大量的语句执行太久日志
[ERROR]cost too much time(22709).(select rid,name,be_concerned from user_L where be_concerned >= 1 and exp >= 1 and last_login_time > '20220523230203' order by be_concerned desc limit 500,200)
[ERROR]cost too much time(25810).(select rid,name,be_concerned from user_1 where be_concerned >= 1 and exp >= 1 and last_login_time > '20220523230203' order by be_concerned desc limit 500,200)
2.user表结构
mysql> desc user_1;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| rid | varchar(12) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| last_login_time | varchar(14) | YES | MUL | NULL | |
| exp | int(10) | YES | MUL | NULL | |
| be_concerned | int(12) | YES | MUL | NULL | |
'''
'''
3.Mysql explain 执行计划分析 以user_1为例子
mysql> explain select rid,name,be_concerned from user_1 where be_concerned >= 1 and exp >= 1 and last_login_time > '20220523230203' order by be_concerned desc limit 500,200;
+----+-------------+--------+-------+-------------------------------------------------------------------------+---------------------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------------------------------------------------------------+---------------------------+---------+------+-------+-------------+
| 1 | SIMPLE | user_1 | range | user_1_exp_index,user_1_be_concerned_index,user_1_last_login_time_index | user_1_be_concerned_index | 5 | NULL | 12688 | Using where |
+----+-------------+--------+-------+-------------------------------------------------------------------------+---------------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)
-
4.Explain 执行计划字段简单说明
- select_type:表示查询是简单的还是复杂的
- SIMPLE：表示最简单的 select 查询语句
- PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为 PRIMARY。
- SUBQUERY：当 select 或 where 列表中包含了子查询，该子查询被标记为 SUBQUERY。
- DERIVED：表示包含在 from 子句中的子查询的 select，在我们的 from 列表中包含的子查询会被标记为 derived。
- UNION：如果 union 后边又出现的 select 语句，则会被标记为 union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived。
- UNION RESULT：代表从 union 的临时表中读取数据
- type:类型,是个需要注意的指标,从上到下,越来越差
- system 当表仅有一行记录时（系统表），数据量很少，往往不需要进行磁盘 IO，速度非常快。
- const 表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量值。这类扫描效率极高，返回数据量少，速度非常快。
- eq_ref 查询时命中主键 primary key 或者 unique key 索引， type 就是 eq_ref。
- ref 区别于 eq_ref，ref 表示使用非唯一性索引，会找到很多个符合条件的行。
- ref_or_null 这种连接类型类似于 ref，区别在于 MySQL 会额外搜索包含 NULL 值的行。
- index_merge 使用了索引合并优化方法，查询使用了两个以上的索引。
- unique_subquery 替换下面的 IN 子查询，子查询返回不重复的集合。
- index_subquery 区别于 unique_subquery，用于非唯一索引，可以返回重复值。
- range 使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在 where 语句中使用 bettween…and、<、>、<=、in 等条件查询 type 都是 range。
- index Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取。
- ALL 将遍历全表以找到匹配的行，性能最差。
- possible_keys:可能会用到的索引
- key 实际用到的索引
- rows 估计的数据量
- Extra 一些额外的说明
- Using index 使用了索引
- Using where 没有找到可用的索引
- Using temporary 临时表存储结果
- Using filesort 需要文件排序
- Using join buffer 联表查询需要缓冲区
- Impossible where where语句不太对
- No tables used 没有from语句
5. 分析我们的执行计划
- select_type = SIMPLE
- type = range
- possible_keys = user_1_exp_index,user_1_be_concerned_index,user_1_last_login_time_index
- key = user_1_be_concerned_index
- rows = 12688
- 可以看出符合查询条件的预估计行数有12688,而实际上大部分数据都没有用
6. 后续处理
- 因为查询语句的条件太过宽泛,导致查询到许多无用数据,通过缩小查询语句的范围,解决问题