我知道这是一个漫长的标题, 但我真正想用的只有一半…
因此，像其他人一样正在关注到最近的数据库技术的发展趋势,我已经听说过No-SQL ，因为它是基于JSON的格式，听说它很快且对普通开发者更为平易近人, 但我从来没有时间坐下来，真正调查的东西。然后我听到关于MongoDB的，特别是关于他们精湛的地理位置功能 –http://www.mongodb.org/display/DOCS/Geospatial+Indexing – 你可能已经知道，我是一个不会应付地理位置的人，所以当我听说，Foursquare 也已经使用在MongoDB时，我立刻上钩了。
因此，从哪里开始...？首先，我们需要一些地理数据， and where better to get that than from GeoNames – where you can download theallCountries.zip file (containing 7 Million+ records at over 200 MB), or simply grab the country-specific information for the country you reside.
我们需要的把数据导入到MySQL，不仅使我们可以比较接近搜索的速度，但也让我们可以学习如何从MySQL导出到MongoDB的，这真的是既灵活又一个宝贵的教训。
如果我们打算使用GeoNames的数据，我们必须首先创建一个表，使用下面的SQL（字段名称为下一阶段特别重要的）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
CREATE
TABLE
`geonames` (
`geonameid`
int
(10) unsigned
NOT
NULL
default
'0'
,
`
name
`
varchar
(200)
NOT
NULL
default
''
,
`ansiname`
varchar
(200)
NOT
NULL
default
''
,
`alternatenames`
varchar
(2000)
NOT
NULL
default
''
,
`latitude`
double
NOT
NULL
default
'0'
,
`longitude`
double
NOT
NULL
default
'0'
,
`feature_class`
char
(1) ,
`feature_code`
varchar
(10) ,
`country_code`
char
(2),
`cc2`
varchar
(60),
`admin1_code`
varchar
(20)
default
''
,
`admin2_code`
varchar
(80)
default
''
,
`admin3_code`
varchar
(20)
default
''
,
`admin4_code`
varchar
(20)
default
''
,
`population`
bigint
(11)
default
'0'
,
`elevation`
int
(11)
default
'0'
,
`gtopo30`
int
(11)
default
'0'
,
`timezone`
varchar
(40),
`modification_date`
date
default
'0000-00-00'
,
PRIMARY
KEY
(`geonameid`)
)
CHARACTER
SET
utf8 ;
|
创建完geonames table，我们可以导入TXT文件内所载的数据， 我们只是从 GeoNames 下载通过使用 SQL 类似下面 (你如果需要更换的TXT文件的位置的，你只需要提取它从您下载的ZIP文件):
|
1
2
3
|
LOAD
DATA INFILE
'C:/xampp/htdocs/allCountries.txt'
INTO
TABLE
geonames(
geonameid,
name
, ansiname, alternatenames, latitude, longitude, feature_class, feature_code, country_code, cc2, admin1_code, admin2_code, admin3_code, admin4_code, population, elevation, gtopo30, timezone, modification_date
)
|
现在，我们应该在我们的数据库中有超过700万的地方（假设你勇敢地使用allCountries.txt文件，说实话，它可能太大对为下一阶段，当你在家里运行个人电脑的时候，会引发几个问题像是他干的，）。一旦你有你的MySQL数据库中的记录，不要忘记 “latitude” 和 “longitude”字段添加一个索引，为了之后给测试一个公平的机会。
在这个阶段，我们应该开始考虑获得MongoDB的设置，幸运的是，MongoDB的在线文档和普遍社区精神非常好。对于这种比较的目的，我们将使用MongoDB和PHP测试的结果，但在未来的“如何”，我也希望记录我对NodeJS的发现！
Let’s download MongoDB – http://www.mongodb.org/downloads – then run-through the getting-started guides –http://www.mongodb.org/display/DOCS/Quickstart. 我发现设置在PC和Mac同样容易。只需下载安装包，保存某处，创造必要的“数据”文件夹，然后运行两个建议控制台可执行文件的“mongod.exe”和“mongo.exe”如果三加三等于6，一切都正常工作！假设MongoDB的设置是一件轻而易举的，而你则有复制正确的能力也。dll (for windows)或 .so (for Mac)到正确的“扩展”文件夹中指定你的php.ini文件，并记录这里 – PHP Drivers for Mongo.
现在，我们准备到把MySQL数据导入MongoDB，但我要告诉你的方法不是大型数据集（应该使用导入功能）的首选方法， 这样教你最根本的MongoDB的功能，其中包括添加新的集合（表）和对象（记录），而且还更新对象和检索对象， 所有php语法应该让php开发者很舒服。事实上，下面的代码提供一切你需要的，不仅从MySQL到MongoDB导入数据，也运行比较测试，看看每个数据库的运行有多快，并返回相同的结果。
开始之前，请注意从MySQL到MongoDB的所有700万条记录的是要采取一些时间，几个小时，如果不是整天（然后一些，尤其是使用这些方法），因此，如果你正在做这个作为一种上手方式，并希望一些即时反馈，你真的应该考虑导入一个具体的国家。GeoNames的txt文件，或使用country_code和（或）限制变量迁移一部分MySQL数据到MongoDB。
无论哪种方式，尽管使用，改善和评论，以下所需的代码(作为提醒，这不是最好方式，用来导入大型数据集到MongoDB，但已经完成，所以这种方式容易理解基础):
|
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
|
// We need this function for running stop-watch later...
function
ms_microtime_float(){
list(
$usec
,
$sec
) =
explode
(
" "
, microtime());
return
((float)
$usec
+ (float)
$sec
);
}
// We need this function for making the most optimised MySQL proximity query available...
// Simply provide a database name, your current Lat / Lng and optional DB Limits and Maximum Distances...
function
ms_get_locations_by_distance_using_sql(
$database_name
=
'geonames'
,
$lat
,
$lng
,
$limit
=100,
$distance
=100,
$country_specific
=false){
if
(
$country_specific
){
$extra_where_clause
=
"AND country_code = 'MY'"
; }
else
{
$extra_where_clause
=
''
; }
if
(
$limit
>0){
$limit
=
'LIMIT 0, '
.
$limit
; }
else
{
$limit
=NULL; }
$lng1
=
$lng
-
$distance
/
abs
(
cos
(
deg2rad
(
$lat
) ) *69 );
$lng2
=
$lng
+
$distance
/
abs
(
cos
(
deg2rad
(
$lat
) ) *69 );
$lat1
=
$lat
- (
$distance
/69 );
$lat2
=
$lat
+ (
$distance
/69 );
/* THIS REALLY IS THE BEST WAY TO PERFORM PROXIMITY SEARCH IN MYSQL */
$nearest_locations_ordered_by_distance
= "SELECT *,
( 6371 *
acos
(
cos
( radians(
$lat
) ) *
cos
( radians( latitude ) ) *
cos
( radians( longitude ) - radians(
$lng
) ) + sin( radians(
$lat
) ) * sin( radians( latitude ) ) ) ) AS distance
FROM
$database_name
WHERE longitude between
$lng1
and
$lng2
AND latitude between
$lat1
and
$lat2
$extra_where_clause
HAVING distance <
$distance
ORDER BY distance
$limit
";
return
$nearest_locations_ordered_by_distance
;
}
/*
* THESE ARE OUR OPTIONS
*
* In order to initially import data from MySQL to MongoDO use:
*
* $collect_mongo_results = false;
* $loop_through_sql = true; // SET TO FALSE IF ONLY INTERESTED IN MONGO RESULTS
* $add_to_mongo = true;
* $print_arrays = false;
*
* In order to compare times for collecting results in both MySQL and MongoDB use:
*
* $collect_mongo_results = true;
* $loop_through_sql = true; // SET TO FALSE IF ONLY INTERESTED IN MONGO RESULTS
* $add_to_mongo = false;
* $print_arrays = true;
*
*/
$collect_mongo_results
= false;
$loop_through_sql
= true;
$add_to_mongo
= true;
$print_arrays
= false;
// Be careful, with 7 million results, this can take time
$limit
= -1;
// Set this to -1 if you wish to import everything from MySQL to MongoDB else add limit as number
$maximum_distance
= 100;
// This represents the maximum distance in KM for proximity search
$database_name
=
'geonames'
;
// This will also be used for the MongoDB name
$table_name
=
'countries'
;
// This will also be used as the MongoDB collection name
$mysql_host
=
'localhost'
;
$mysql_username
=
'root'
;
$mysql_password
=
''
;
// These default Lat / Lng Coordinates are for Malaysia
$current_latitude
= 3.190908;
// Do not use a string!
$current_longitude
= 101.682243;
// Do not use a string!
// This country code is for Malaysia
// Change to false if wanting to run queries on all records...
$country_code
= false;
// This is used to perform sub-set queries on specific countries...
/* END OF OPTIONS */
// Connect to Mongo
$m
=
new
Mongo();
// Select / Create a Database
$db
=
$m
->
$database_name
;
// Select / Create a Collection (table)
$collection
=
$db
->
$table_name
;
/* THIS ALLOWS SCRIPTS TO RUN WITHOUT TIMING OUT */
set_time_limit(0);
if
(
$collect_mongo_results
){
/* START TIMING MONGO */
$time_start
= ms_microtime_float();
// Start building MongoDB query
/* PAY EXTRA ATTENTION TO ORDER OF LONGITUDE THEN LATITUDE */
/* THIS IS VERY IMPORTANT FOR MONGODB - OTHERWISE RESULTS WILL BE WRONG !!! */
$this_latlng
=
array
(
'lng'
=>
$current_longitude
,
'lat'
=>
$current_latitude
);
$mongo_near_query
=
array
(
'geoNear'
=>
$table_name
,
'near'
=>
$this_latlng
,
'$spherical'
=>true,
'$maxDistance'
=>
$maximum_distance
/6378,
'num'
=>
$limit
);
$cursor
=
$db
->command(
$mongo_near_query
);
/* THE SAME QUERY CAN BE RUN IN MONGO CONSOLE USING THE FOLLOWING */
//-> db.runCommand({geoNear:"countries",near:[101,3],spherical:true,maxDistance:100/6378,num:10})
if
(
$print_arrays
){
// iterate through the results
foreach
(
$cursor
[
'results'
]
as
$obj
) {
echo
'<br />This is MongoDB Array:<br />'
;
echo
'<pre>'
;
print_r(
$obj
);
echo
'</pre>'
;
}
}
/* END MONGO TIMER AND DISPLAY RESULTS */
$time_end
= ms_microtime_float();
$time
=
$time_end
-
$time_start
;
$mongo_results
=
"<br />Collected and Printed using MongoDB in $time seconds<br />"
;
echo
$mongo_results
;
}
if
(
$loop_through_sql
){
/* START SQL TIMER */
$time_start
= ms_microtime_float();
// Connect to MySQL
$link
= mysql_connect(
$mysql_host
,
$mysql_username
,
$mysql_password
);
if
(!
$link
) {
die
(
'Could not connect: '
. mysql_error());
}
// Connect to
if
(!mysql_select_db(
$database_name
)) {
die
(
'Could not select database: '
. mysql_error());
}
/* WHICH QUERY TO RUN...? DEPENDS ON OPTIONS ABOVE */
if
(
$add_to_mongo
){
if
(
$country_code
){
$where_clause
=
"WHERE country_code = 'MY'"
; }
$query
=
'SELECT * FROM '
.
$table_name
.
$where_clause
;
}
else
{
$query
= ms_get_locations_by_distance_using_sql(
$table_name
,
$current_latitude
,
$current_longitude
,
$limit
,
$maximum_distance
,
$country_code
);
}
// Perform Query
$result
= mysql_query(
$query
);
// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if
(!
$result
) {
$message
=
'Invalid query: '
. mysql_error() .
"\n"
;
$message
.=
'Whole query: '
.
$query
;
die
(
$message
);
}
// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while
(
$row
= mysql_fetch_assoc(
$result
)) {
if
(
$print_arrays
){
echo
'<br />This is MySQL Array:<br />'
;
echo
'<pre>'
;
print_r(
$row
);
echo
'</pre>'
;
}
/* END SQL TIMER */
$time_end
= ms_microtime_float();
$time
=
$time_end
-
$time_start
;
/* NOW NEED TO ADD DATA TO MONGO TOO */
if
(
$add_to_mongo
){
$obj
=
array
();
foreach
(
$row
as
$key
=>
$value
){
$obj
[
$key
] =
$value
;
}
// This is the MongoDB PHP Function for inserting objects (records)
$collection
->insert(
$obj
);
}
// In order to use MongoDB Geo-Spatial Indexing we need a Lng / Lat field
// This shows us how to use the MongoDB PHP Update function
$collection
->update(
array
(
"geonameid"
=>
$row
[
'geonameid'
]),
array
(
'$set'
=>
array
(
"latlng"
=>
array
(
'lng'
=>(float)
$row
[
'longitude'
],
'lat'
=>(float)
$row
[
'latitude'
]))));
// Create Geo-Spaital Index In Mongo Console (once this field has been added) using:
// db.countries.ensureIndex({latlng:'2d'})
}
/* THEN PRINT RESULTS */
echo
"<br />Collected and Printed using MySQL in $time seconds<br />"
;
if
(
$collect_mongo_results
){
echo
"WHERE AS "
.
$mongo_results
;
}
// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result(
$result
);
mysql_close(
$link
);
}
|
几件事情要记住运行前或后，检查结果比较，是否你还记得MySQL索引添加到您的latitude和longitudes，更不用说你是否添加你的MongoDB的地理空间的索引，要做这些, 你需要使用Mongo控制台的以下命令(我知道令人讨厌 - 有一些图形用户界面，但是这比它看起来要容易得多):
|
1
|
db.countries.ensureIndex({latlng:
'2d'
})
|
一些其他有用的控制台指令包括dropping databases:
|
1
|
db.geonames.drop()
|
默认下, 打开Mongo Console启动“test” database, 转换到 geonames 只需简单输入:
|
1
|
use geonames
|
总之，MongoDB是快速的。在搜索65411条记录/对象 (我只导入了马来西亚的地理，为了节省时间和学习部分), and 只显示那些 100 KM 半径, 把结果限制在 10, 按距离最近的排第一个, 输出到页面， 使用0.23555 seconds for MySQL, 同样的查询使用 0.00152 seconds with MongoDB – 有几次, 甚至到达了0.00095! 考虑到这只是单用户查询.对MySQL最大的问题，我最初寻求替代品的原因是同一时间只有一个用户可以访问数据库,并发查询被添加到队列中，需要等待轮到自己. 在MongoDB, 这不是问题, 所以真的，真相是，没有他们之间的比较! 之后我们会运行一些并发测试, 以及运行相同的查询使用完整的700万记录集…
但我们如何导入所有的700万+的记录，并执行这些测试…?
不使用上面的方法！我多次尝试几种不同的方式导入所有700万条记录，我能找到的最快最可靠的方法是，首先导出MySQL为CSV的格式。我几次试图从控制台使用mysqldump的，但永远无法得到正确的格式。不管你信不信, 最友好的方法是export from phpMyAdmin in CSV for MS Excel format (第一行是字段名) 因为这样能够之后利用mongoimport的功能.然而，每当我试图导出全部集合，我遇到了问题。我最终做的是，每次导出为CSV的一百万记录。
一旦你拥有所有8个 CSV文件，然后可以导入到MongoDB的使用下面的控制台命令：:
|
1
|
mongoimport --db geonames --collection countries --type csv --file geonames_part1.csv --headerline --upsert
|
做每一部分，并记住这必须从一个标准的终端控制台，而不是从MongoDB的控制台，这是我的第一个错误! 我们所有的700万加记录到数据库后，我们将需要添加在latlng / LOC field，它们尚不存在并且MongoDB地理空间索引需要它们。 要完成这个, 我所发现的最快的方法是JS query, 你首先需要创建一个新的 JavaScript 包含以下内容:
|
1
2
3
|
db.countries.find().forEach(
function
(data) {
db.countries.update({_id:data._id},{$set:{loc:{lng:parseFloat(data.longitude),lat:parseFloat(data.latitude)}}});
})
|
然后我们需要 查询 MongoDB 使用这个 JS file, 这可能是我最喜欢的 MongoDB 功能，并可以使用的语法，如从一个普通的终端（而不是MongoDB的控制台）执行:
|
1
|
mongo geonames add_latlng.js
|
一旦我们有了place, 我们可以加到Geo-Spatial Index (from the Mongo Console) by typing:
|
1
|
db.countries.ensureIndex({loc:
"2d"
})
|
结果是惊人的:
使用MySQL和寻找距离下令最近10位置查询780万条记录的平均165秒！
与MongoDB的，相同的查询只用了0.02秒!
除此之外，接下来会发生什么…?
MongoPress – 这是未来NoSQLCMS的开始 : -)
You may also be interested in MongoDB Vs MySQL – Benchmarks Re-Creating Typical CMS Functionality to Compare Performance of MySQL and MongoDB in PHP / Apache.
2607
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
