CentOS release 6.9 (Final)
Kernel | 2.6.32-696.el6.x86_64
cpu 64xIntel(R) Xeon(R) Gold 6130 CPU @ 2.10GHz
内存 128G
bond0 2000Mb/s Full
磁盘 SSD
mysql版本
Server version: 5.7.22-22-log Percona Server (GPL), Release 22, Revision f62d93c
参数
innodb_buffer_pool_size=64G
sync_binlog=1
innodb_flush_log_at_trx_commit=1
对于
InnoDB
和
MyISAM
表，MySQL可以使用类似于创建常规索引的语法创建空间索引，但使用
SPATIAL
关键字。必须声明空间索引中的列
NOT NULL
。
SPATIAL INDEX
创建一个R树索引。对于支持空间列的非空间索引的存储引擎，引擎会创建B树索引。空间值的B树索引对于精确值查找很有用，但对于范围扫描则不行。
CREATE TABLE `tbl_pos` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`att1` int(11) DEFAULT NULL,
`att2` int(11) DEFAULT NULL,
`att3` int(11) DEFAULT NULL,
`mod_time` timestamp NULL DEFAULT NULL,
`pos` geometry NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
root@localhost:mysql3307.sock [db1] 11:27:46> insert into tbl_pos (att1, att2, att3, mod_time, pos) values ( 1, 1, 1, now(), ST_PointFromText('POINT(120 70)'));
Query OK, 1 row affected (0.01 sec)
root@localhost:mysql3307.sock [db1] 11:28:35> select *,ST_AsText(pos) from tbl_pos;
+----+------+------+------+---------------------+---------------------------+----------------+
| id | att1 | att2 | att3 | mod_time | pos | ST_AsText(pos) |
+----+------+------+------+---------------------+---------------------------+----------------+
| 1 | 1 | 1 | 1 | 2018-08-19 11:28:06 | ^@ Q@ | POINT(120 70) |
+----+------+------+------+---------------------+---------------------------+----------------+
未建R树索引下插入性能测试
[root@db001 ~]# ./mydbtest_linux64.bin query=insert.cnf degree=100
[root@db001 ~]# cat insert.cnf
option
name mysql57
loop 10000000
user ********************@10.11.5.1:3307:db1
show 15
tran yes
commit 10000
declare
att1 int 10 20
att2 int 10 20
att3 int 10 20
x int 120 130
y int 70 80
begin
insert into tbl_pos (att1, att2, att3, mod_time, pos) values ( :att1, :att2, :att3, now(), POINT(:x,:y) );
end
2018-08-19 11:58:51 Total tran=121294=1049/s, qtps=121294=1049/s, ela=115017 ms, avg=948 us
2018-08-19 11:58:51 Total tran=121167=1048/s, qtps=121167=1048/s, ela=115051 ms, avg=949 us
Summary: SQL01 exec=12133224, rows=12133224=100/e, avg=943 us
Summary: exec=103702/s, qtps=
103702/s
root@localhost:mysql3307.sock [db1] 11:57:39> select count(*) from tbl_pos;
+----------+
| count(*) |
+----------+
|
11000001 |
+----------+
未建R树索引的情况下插入更新
[root@db001 ~]# cat insertupdate.cnf
option
name mysql57
user **********@10.11.5.1:3307:db1
show 15
tran yes
commit 1
time m5
declare
id int 1 10000000
x int 120 130
y int 70 80
begin
insert into tbl_pos (id, mod_time, pos) values (:id, now(), POINT(:x,:y)) ON DUPLICATE KEY UPDATE pos=point(st_x(pos)+5-rand()*10, st_y(pos)+5-rand()*10), mod_time=now();
end
./mydbtest_linux64.bin query=insertupdate.cnf
degree=20
Summary: SQL01 exec=7621675, rows=15243142=199/e, avg=162 us
Summary: exec=25237/s, qtps=
25237
/s
./mydbtest_linux64.bin query=insertupdate.cnf
degree=50
Summary: SQL01 exec=12187675, rows=24374774=199/e, avg=253 us
Summary: exec=40356/s, qtps=
40356/s
./mydbtest_linux64.bin query=insertupdate.cnf
degree=100
Summary: SQL01 exec=14250218, rows=28499628=199/e, avg=383 us
Summary: exec=47186/s, qtps=
47186/s
创建R索引
root@localhost:mysql3307.sock [db1] 13:00:20> alter table tbl_pos add SPATIAL KEY `idx_pos` (`pos`);
Query OK, 0 rows affected (3 min 54.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
测试创建R索引情况下插入更新
2018-08-19 13:57:21 SQL01 143 ms exec= 1, ela= 142 ms, avg= 142024 us, pct= 0, 97
2018-08-19 13:57:21 SQL01 147 ms exec= 1, ela= 146 ms, avg= 146004 us, pct= 0, 98
2018-08-19 13:57:21 SQL01 168 ms exec= 1, ela= 167 ms, avg= 167473 us, pct= 0, 98
2018-08-19 13:57:21 SQL01 170 ms exec= 1, ela= 169 ms, avg= 169589 us, pct= 0, 98
2018-08-19 13:57:21 SQL01 171 ms exec= 1, ela= 170 ms, avg= 170377 us, pct= 0, 99
2018-08-19 13:57:21 SQL01 181 ms exec= 1, ela= 180 ms, avg= 180822 us, pct= 0, 99
2018-08-19 13:57:21 SQL01 215 ms exec= 1, ela= 214 ms, avg= 214500 us, pct= 0,100
插入更新
400多TPS
root@localhost:mysql3307.sock [db1] 14:00:16> alter table tbl_pos drop KEY `idx_pos`;
Query OK, 0 rows affected (10 min 50.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
删掉索引后恢复正常
查询距离5公里范围的点
root@localhost:mysql3307.sock [db1] 15:01:50> explain select * from tbl_pos where ST_Contains(ST_Buffer(POINT(125,78),0.05), pos) order by ST_Distance_Sphere(Point(125,78), pos) limit 100;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | tbl_pos | NULL | range | idx_pos | idx_pos | 34 | NULL | 150 | 100.00 | Using where; Using filesort |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
select 5000*1/1852*1/60;
[root@db001 ~]# cat selectbygis.cnf
option
name mysql57
user ******************@10.11.5.1:3307:db1
show 15
tran no
commit 1
time m5
declare
x int 120 130
y int 70 80
begin
select * from tbl_pos where ST_Contains(ST_Buffer(POINT(:x,:y),0.04499640), pos) order by ST_Distance_Sphere(Point(:x,:y), pos) limit 100;
end
[root@db001 ~]# ./mydbtest_linux64.bin query=selectbygis.cnf
degree=20
Summary: SQL01 exec=575872, rows=57587200=10000/e, avg=
10448 us
Summary: exec=1906/s, qtps=
1906/s
mysql的GIS还有比较远的路要走。
