SQL调优之排名优化
mysql> explain extended select t.rowno from
(SELECT @rowno:=@rowno+1 as rowno,ur.customer_id as userId from t_hss_user_info_rank ur,
(select @rowno:=0) rno order by ur.give_pers_count DESC,
ur.last_give_time DESC
) t where t.userid= '1000010000000010';
+----+-------------+------------+--------+---------------+-----------------+---------+-------+--------+----------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+--------+---------------+-----------------+---------+-------+--------+----------+----------------+ | 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 152 | const | 10 | 100.00 | Using where | | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | ur | index | NULL | give_pers_count | 11 | NULL | 311847 | 100.00 | Using index | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+--------+---------------+-----------------+---------+-------+--------+----------+----------------+ 4 rows in set, 1 warning (0.00 sec)
mysql> show create table t_hss_user_info_rank\G
*************************** 1. row ***************************
Table: t_hss_user_info_rank
Create Table: CREATE TABLE `t_hss_user_info_rank` (
`customer_id` varchar(50) NOT NULL COMMENT '会员Id',
`user_name` varchar(50) DEFAULT NULL COMMENT '会员名称',
`phone_no` varchar(11) DEFAULT NULL,
`give_count` int(10) DEFAULT NULL COMMENT '赠送份额',
`give_pers_count` int(10) DEFAULT NULL COMMENT '赠送人数',
`last_give_time` datetime DEFAULT NULL COMMENT '最后赠送时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
`scene_no` int(10) DEFAULT NULL COMMENT '赠送份额',
PRIMARY KEY (`customer_id`),
KEY `index_user_rank_customer_id` (`customer_id`,`give_count`,`last_give_time`),
KEY `give_pers_count` (`give_pers_count`,`last_give_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select t.rowno from (SELECT @rowno:=@rowno+1 as rowno,ur.customer_id as userId from t_hss_user_info_rank ur,(select @rowno:=0) rno order by ur.give_pers_count DESC, ur.last_give_time DESC ) t where t.userid= '1000010000000010';
+-------+
| rowno |
+-------+
| 139 |
+-------+
1 row in set (2.42 sec)
1、优化方式(一),利用整形检索快的原理,将customer_id在临时表里转化为整形;
select t.rowno from (SELECT @rowno:=@rowno+1 as rowno,cast(ur.customer_id as unsigned) as userId from t_hss_user_info_rank ur,(select @rowno:=0) rno order by ur.give_pers_count asc, ur.last_give_time asc ) t where t.userid= '1000010000000010';
+-------+
| rowno |
+-------+
| 139 |
+-------+
1 row in set (0.71 sec)
这表现出一个问题,表结构设计不合理,能用unsigned int表示,为什么用varchar?
2、优化方式(二),使用中间表,存储排序结果,每天更新一次;