记自己一次sql优化(使用伪列)
来源:互联网 发布:淘宝店货品价格怎么改 编辑:程序博客网 时间:2024/05/07 00:52
起因
未优化之前的s q l,大表内连接次数太多,严重影响效率。因此需要优化。
sql 要满足的逻辑
以status_id分组,weight_id最大的行中(同一组中可能有好几个weight_id相同的行),mid最大的那一行
表结构
CREATE TABLE `at_x_0` ( `uixd` , `mixd` , `status_ixd` , `message_type` , `time_ixd` , `time` , `weight_id` , `extension` , UNIQUE KEY `uid` (`mid`,`uid`), KEY `uid_2` (`uid`,`time_id`,`message_type`,`status_id`,`weight_id`), KEY `sw` (`status_id`,`weight_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
优化前的表结构(两次内连接)
“select e.* from
(
select max(c.mid) mid from
(select a.mid,a.status_id,a.weight_id
from
(select f.status_id,max(f.weight_id) weight_id
from at_message_0.at_message_0 f where f.uid=? and f.time_id=? and f.message_type=? and f.weight_id<=? group by f.status_id
) b on a.status_id=b.status_id and a.uid=? and a.message_type=? and a.weight_id=b.weight_id order by a.weight_id desc
) c group by c.status_id
) d where e.mid=d.mid and e.uid=? order by e.weight_id desc limit ?,?”;
优化前的执行计划
+------+-------------+------------+-------+---------------+-------+---------+------+-------+-------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+------------+-------+---------------+-------+---------+------+-------+-------------------------------------------------+| 1 | SIMPLE | <derived2> | ALL | NULL | NULL | NULL | NULL | 50176 | Using temporary; Using filesort || 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 224 | Using temporary; Using filesort || 2 | DERIVED | b | ALL | NULL | NULL | NULL | NULL | 224 | Using where; Using join buffer (flat, BNL join) || 4 | DERIVED | a | index | NULL | uid_2 | 29 | NULL | 224 | Using index; Using temporary; Using filesort |+------+-------------+------------+-------+---------------+-------+---------+------+-------+-------------------------------------------------+
优化后的sql(一次右连接,加伪列)
set @num :=0, @group := ”;
select x.uid,x.mid,x.status_id,x.message_type,x.time_id,x.time,x.weight_id,x.extension from
(
select d.*,
@num := if(@group = d.status_id, @num:=@num-1, 0) as row_number,
@group := d.status_id as dummy from
(select b.* from at_message_0.at_message_0 b
right join
(select a.status_id,max(a.weight_id) as max_weight_id from at_message_0.at_message_0 a group by a.status_id) c
on b.status_id = c.status_id and b.weight_id= c.max_weight_id
) d order by d.status_id,d.mid desc
) as x group by x.status_id order by x.row_number desc;
优化后的执行计划
+------+-------------+------------+-------+---------------+------+---------+-----------------------------+------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+------------+-------+---------------+------+---------+-----------------------------+------+---------------------------------+| 1 | SIMPLE | <derived2> | ALL | NULL | NULL | NULL | NULL | 201 | Using temporary; Using filesort || 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 201 | Using temporary; Using filesort || 2 | DERIVED | b | ref | sw,sm | sw | 16 | c.status_id,c.max_weight_id | 1 | Using where || 4 | DERIVED | a | range | NULL | sw | 8 | NULL | 202 | Using index for group-by |+------+-------------+------------+-------+---------------+------+---------+-----------------------------+------+---------------------------------+
- 记自己一次sql优化(使用伪列)
- 记同事一次sql 优化(二)
- 为什么要使用 99+,记一次 sql 优化(消息数量显示优化)
- 记一次sql优化过程
- 记一次SQL查询优化
- 记同事 一次sql优化
- MySQL 利用伪列优化多次使用order by
- 使用伪列Rownum
- 记一次地址服务优化(Redis使用优化)
- 记QQ群里一次sql优化
- 记一次sql优化,in+子查询
- 一次SQL优化
- 优化了一次sql
- SQL Profile一次SQL优化
- mysql sql语句添加伪列
- SQL-借助伪列实现分页查询
- 一次SQL分页的优化
- 记录一次sql优化过程
- Unity5.0 EventSystem事件系统的详细说明
- Hello World!!
- jq工具的一个简单使用
- NopCommerce 源码研究(二)
- linux自动执行文件分析
- 记自己一次sql优化(使用伪列)
- 专业书P17~37读后提问
- 多项式加法运算链表实现
- Let's Encrypt 给网站加 HTTPS 完全指南
- 程序员面试金典——解题总结: 9.18高难度题 18.5有个内含单词的超大文本文件,给定任意两个单词,找出在这个文件中这两个单词的最短距离
- 二维码 生成
- vim 最最简单的命令
- 设计模式之单例模式
- 【JSTL】fmt标签