1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
表结构如下,记录大概有10W行:
Table
"ytt.girl1"
Column
| Type | Modifiers
--------+---------+--------------------
id |
integer
|
not
null
rank |
integer
|
not
null
default
0
Indexes:
"girl1_pkey"
PRIMARY
KEY
, btree (id)
"idx_girl1_rank"
btree (rank)
WHERE
rank >= 10
AND
rank <= 100
执行的查询语句为:
select
*
from
girl1
where
rank
between
20
and
60 limit 20;
用了全部索引的查询计划:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..36.58
rows
=20 width=8) (actual
time
=0.024..0.054
rows
=20 loops=1)
->
Index
Scan using idx_girl1_rank
on
girl1 (cost=0.29..421.26
rows
=232 width=8) (actual
time
=0.023..0.044
rows
=20 loops=1)
Index
Cond: ((rank >= 20)
AND
(rank <= 60))
Total runtime: 0.087 ms
(4
rows
)
Time
: 1.881 ms
用了条件索引的查询计划:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.28..35.54
rows
=20 width=8) (actual
time
=0.036..0.068
rows
=20 loops=1)
->
Index
Scan using idx_girl1_rank
on
girl1 (cost=0.28..513.44
rows
=291 width=8) (actual
time
=0.033..0.061
rows
=20 loops=1)
Index
Cond: ((rank >= 20)
AND
(rank <= 60))
Total runtime: 0.106 ms
(4
rows
)
Time
: 0.846 ms
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
ytt>show
create
table
girl1_filtered_index;
+
----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| girl1_filtered_index |
CREATE
TABLE
`girl1_filtered_index` (
`id`
int
(11)
NOT
NULL
,
`rank`
int
(11)
NOT
NULL
DEFAULT
'0'
,
PRIMARY
KEY
(`id`),
KEY
`idx_rank` (`rank`)
) ENGINE=InnoDB
DEFAULT
CHARSET=latin1 |
+
----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
接下来,对基础表的更新操作做下修改,创建了三个触发器。
DELIMITER $$
USE `t_girl`$$
DROP
TRIGGER
/*!50032 IF EXISTS */ `filtered_insert`$$
CREATE
/*!50017 DEFINER =
'root'
@
'localhost'
*/
TRIGGER
`filtered_insert`
AFTER
INSERT
ON
`girl1`
FOR
EACH ROW
BEGIN
IF new.rank
BETWEEN
10
AND
100
THEN
INSERT
INTO
girl1_filtered_index
VALUES
(new.id,new.rank);
END
IF;
END
;
$$
DELIMITER ;
DELIMITER $$
USE `t_girl`$$
DROP
TRIGGER
/*!50032 IF EXISTS */ `filtered_update`$$
CREATE
/*!50017 DEFINER =
'root'
@
'localhost'
*/
TRIGGER
`filtered_update`
AFTER
UPDATE
ON
`girl1`
FOR
EACH ROW
BEGIN
IF new.rank
BETWEEN
10
AND
100
THEN
REPLACE
girl1_filtered_index
VALUES
(new.id,new.rank);
ELSE
DELETE
FROM
girl1_filtered_index
WHERE
id = old.id;
END
IF;
END
;
$$
DELIMITER ;
DELIMITER $$
USE `t_girl`$$
DROP
TRIGGER
/*!50032 IF EXISTS */ `filtered_delete`$$
CREATE
/*!50017 DEFINER =
'root'
@
'localhost'
*/
TRIGGER
`filtered_delete`
AFTER
DELETE
ON
`girl1`
FOR
EACH ROW
BEGIN
DELETE
FROM
girl1_filtered_index
WHERE
id = old.id;
END
;
$$
DELIMITER ;
OK,我们导入测试数据。
ytt>
load
data infile
'girl1.txt'
into
table
girl1 fields terminated
by
','
;
Query OK, 100000
rows
affected (1.05 sec)
Records: 100000 Deleted: 0 Skipped: 0 Warnings: 0
ytt>
select
count
(*)
from
girl1;
+
----------+
|
count
(*) |
+
----------+
| 100000 |
+
----------+
1 row
in
set
(0.04 sec)
ytt>
select
count
(*)
from
girl1_filtered_index;
+
----------+
|
count
(*) |
+
----------+
| 640 |
+
----------+
1 row
in
set
(0.00 sec)
|
1
|
select
a.id,a.rank
from
girl1
as
a
where
a.id
in
(
select
b.id
from
girl1_filtered_index
as
b
where
b.rank
between
20
and
60) limit 20;
|