MySQL巧⽤⾃定义函数进⾏查询优化
⽤户⾃定义变量是⼀个很容易被遗忘的MySQL特性，但是⽤的好，发挥其潜⼒，在很多场景都可以写出⾮常⾼效的查询语句。
⼀. 实现⼀个按照actorid排序的列
1 mysql>set@rownum :=0;
2 Query OK, 0 rows affected (0.00 sec)
3
4 mysql>select actor_id ,@rownum :=@rownum+1as rownum
5->from sakila.actor limit 3;
6+----------+--------+
7| actor_id | rownum |
8+----------+--------+
9|58|1|
10|92|2|
11|182|3|
12+----------+--------+
133 rows in set (0.00 sec)
⼆. 扩展⼀下，现在需要获取演过最多电影的前⼗位，针对数量作⼀个排名，如果数量⼀样，则排名相同
1 mysql>set@curr_cnt :=0 ,@pre_cnt :=0 ,@rank :=0;
2 Query OK, 0 rows affected (0.00 sec)
3
4 mysql>select actor_id,
5->@prev_cnt :=@curr_cnt as dummy,
6->@curr_cnt := cnt as cnt,
7->@rank :=IF(@prev_cnt<>@curr_cnt,@rank+1,@rank) as rank
8->FROM(
9->SELECT actor_id ,count(*) as cnt
10->FROM sakila.film_actor
11->GROUP BY actor_id
12->ORDER BY cnt DESC
13-> LIMIT 10
14-> )as der;
15+----------+-------+-----+------+
16| actor_id |dummy| cnt | rank |
17+----------+-------+-----+------+
18|107|0|42|1|
19|102|42|41|2|
20|198|41|40|3|
21|181|40|39|4|
22|23|39|37|5|
23|81|37|36|6|
24|158|36|35|7|
25|144|35|35|7|
26|37|35|35|7|
27|106|35|35|7|
28+----------+-------+-----+------+
2910 rows in set (0.00 sec)
三. 避免重复查询刚更新的数据
如果想要⾼效的更新⼀条记录的时间戳 ,⼜想返回更新的数据
1 mysql>create table t
2 (id int,lastUpdated datetime);
2 Query OK, 0 rows affected (0.0
3 sec)
3
4 mysql>insert into t2 (id ,lastupdated)values(1,sysdate());
5 Query OK, 1 row affected (0.02 sec)
6
7 mysql>select*from t2;
8+------+---------------------+
9| id | lastUpdated |
10+------+---------------------+
11|1|2017-07-2416:03:34|
12+------+---------------------+
131 row in set (0.01 sec)
14
15 mysql>update t2 set lastUpdated=NOW() WHERE id =1and@now :=Now();
秽土转生的秘密16 Query OK, 1 row affected (0.02 sec)
17 Rows matched: 1 Changed: 1 Warnings: 0
19
20 mysql>select@now, sysdate();
21+---------------------+---------------------+
22|@now| sysdate() |
23+---------------------+---------------------+
24|2017-07-2416:05:42|2017-07-2416:06:06|
25+---------------------+---------------------+
261 row in set (0.00 sec)
四. 统计更新和插⼊的数量
使⽤ INSERT ON DUPLICATE KEY UPDATE 时，查询插⼊成功的条数，冲突的条数
1 mysql>set@x :=0;
2 Query OK, 0 rows affected (0.00 sec)
3
4 mysql>INSERT INTO t3(c1,c2) values(1,2),(1,3),(2,2)
5->ON DUPLICATE KEY UPDATE
6-> c2=VALUES(c2)+(0*(@x:=@x+1));
7 Query OK, 4 rows affected (0.01 sec)
8 Records: 3 Duplicates: 1 Warnings: 0
9
10 mysql>select@x;
11+------+
12|@x|
13+------+
14|1|
15+------+
161 row in set (0.00 sec)
17
18 mysql>select*from t3;
19+----+------+
20| c1 | c2 |
21+----+------+
22|1|3|
23|2|2|
24+----+------+
252 rows in set (0.00 sec)
五. 确定取值的顺序
想要获取sakila.actor中的⼀个结果
错误的查询⼀:
下⾯的查询看起来好像只返回⼀个结果，实际呢:
1 mysql>set@row_num :=0;
2 Query OK, 0 rows affected (0.00 sec)
3
4 mysql>SELECT actor_id,@row_num :=@row_num+1AS cnt
5->FROM sakila.actor
6->WHERE@row_num<=1
7-> ;
8+----------+------+
9| actor_id | cnt |
10+----------+------+
11|58|1|
12|92|2|
13+----------+------+
142 rows in set (0.00 sec)
15
16看⼀下执⾏计划:
17+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+
18| id | select_type |table| type | possible_keys |key| key_len | ref | rows | Extra |
19+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+
20|1| SIMPLE | actor |index|NULL| idx_actor_last_name |137|NULL|200| Using where; Using index| 21+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+
221 row in set (0.00 sec)
这是因为where 和 select 是在查询的不同阶段执⾏的造成的。
错误的查询⼆：
如果加上按照 first_name 排序呢 :
1 mysql>set@row_num :=0;
2 Query OK, 0 rows affected (0.00 sec)
4 mysql>SELECT actor_id,@row_num :=@row_num+1AS cnt
5->FROM sakila.actor
6->WHERE@row_num<=1
7->order by first_name;
8+----------+------+
9| actor_id | cnt |
10+----------+------+
11|71|1|
12|132|2|
13|165|3|
薛之谦14|173|4|
15|125|5|
16|146|6|
17|29|7|
18|65|8|
19|144|9|
20|76|10|
21|49|11|
22|34|12|
23|190|13|
24|196|14|
25|83|15|
26 .. ...
27返回了所有⾏，再看下查询计划:
28
29+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
30| id | select_type |table| type | possible_keys |key| key_len | ref | rows | Extra |
31+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
32|1| SIMPLE | actor |ALL|NULL|NULL|NULL|NULL|200| Using where; Using filesort |
33+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
341 row in set (0.00 sec)
可以看出原因是 Using where 是在排序操作之前取值的，所以输出了全部的⾏。
解决这个问题的⽅法是：让变量的赋值和取值发⽣在执⾏查询的统⼀阶段:
正确的查询:
1 mysql>set@row_num :=0;
2 Query OK, 0 rows affected (0.00 sec)
3
4 mysql>SELECT actor_id,@row_num AS cnt
5->FROM sakila.actor
6->WHERE (@row_num :=@row_num+1) <=1
7-> ;
8+----------+------+
9| actor_id | cnt |
10+----------+------+
11|58|1|
12+----------+------+
131 row in set (0.00 sec)
14
15看⼀下执⾏计划
16
17+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+
18| id | select_type |table| type | possible_keys |key| key_len | ref | rows | Extra |
19+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+
20|1| SIMPLE | actor |index|NULL| idx_actor_last_name |137|NULL|200| Using where; Using index| 21+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+
221 row in set (0.00 sec)
想⼀想如果加上ORDER BY 该怎么写？
1 mysql>set@row_num :=0;
2 Query OK, 0 rows affected (0.00 sec)
3
4 mysql>SELECT actor_id,first_name ,@row_num AS row_num
5->FROM sakila.actor
6->WHERE@row_num<=1
7->ORDER BY first_name , least(0, @row_num :=@row_num+1)
8-> ;
9
抖音怎么设置动态壁纸10+----------+------------+---------+
11| actor_id | first_name | row_num |
12+----------+------------+---------+
丙申13|2| NICK |2|
14|1| PENELOPE |1|
17
18
19 mysql>select@row_num;
20+----------+
21|@row_num|
22+----------+
23|2|
24+----------+
251 row in set (0.00 sec)
26
27看⼀下执⾏计划:
28
29+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+诚信与责任
30| id | select_type |table| type | possible_keys |key| key_len | ref | rows | Extra |
31+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
32|1| SIMPLE | actor |ALL|NULL|NULL|NULL|NULL|200| Using where; Using temporary; Using filesort |
33+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
341 row in set (0.00 sec)
35
36SELECT actor_id,first_name ,@row_num:=@row_num+1AS row_num
37FROM sakila.actor
38WHERE@row_num<=1
39ORDER BY first_name , least(0, @row_num :=@row_num+1)暖心且走心的文案
六. UNION的巧妙改写
假设有两张⽤户表,⼀张主⽤户表，存放着活跃⽤户；⼀些归档⽤户表，存放着长期不活跃的⽤户。现在需要查id 为123的客户。先看下这个语句
1select id from users where id=123
2union all
3select id from users_archived where id =123
上⾯的语句是可以执⾏的，但是效率不好，因为两张表都必须查询⼀次
引⼊⾃定义变量的改写:
1SELECT GREATEST(@found:=-1,id) AS id ,'users'AS which_tbl
2FROM users WHERE id =123
3UNION ALL
4 SEELCT id,'users_archived'FROM users_archived WHERE id =123AND@found IS NULL
5UNION ALL
6SELECT1,'reset'FROM DUAL WHERE (@found:=NULL) IS NOT NULL
上⾯的改写⾮常巧妙：
第⼀段，如果在users查询到记录，则为@found赋值，也不会查询第⼆段；如果没有查询到记录,@found 为 null ，执⾏第⼆段。
第三段没有输出 ，只是简单的重置@found 为null。另外 GREATEST(@found:=-1,id) 也不会影响输出！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论