在200万行MySQL MyISAM表上约150ms [英] ~150ms on a 2 million rows MySQL MyISAM table
问题描述
我正在通过一个包含约200万行+约60万行(两个MyISAM表)的宠物项目来了解MySQL的性能.在两个INT(10)索引列上使用BETWEEN进行范围查询(限制为1个返回结果)大约需要160毫秒(包括一个INNER JOIN).我认为我的配置没有经过优化，并且正在寻找有关如何诊断或常见配置"的建议.
I'm learning about MySQL performance with a pet project consisting of ~2million rows + ~600k rows (two MyISAM tables). A range query using BETWEEN on two INT(10) indexed columns, LIMITed to 1 returned result takes about 160ms (including an INNER JOIN). I figure my configuration isn't optimised and am looking for some advice on how to either diagnose, or perhaps "common configuration".
我创建了一个要点，其中包含表，查询和my.cnf的内容.
I created a gist containing both tables, the query and the contents of my.cnf.
我插入了从 MaxMinds打开数据库.我尝试了两个单独的索引，现在尝试了一个组合索引，但性能没有差异.
I created the b-tree index after inserting all data which was imported from a CSV file from MaxMinds open database. I tried two separate, and now a combined index with no difference in performance.
我正在Macbook Pro上以2.6GHz(i5)和8GB 1600MHz RAM的频率在本地运行. MySQL是使用可从mysql下载页面下载的二进制文件安装的(由于我的代表词很低，因此无法提供第三个链接).这是默认安装，对gist中包含的my.cnf配置文件没有重大添加(位于系统上的/usr/local/mysql-5.6.xxx/目录下).
I'm running this locally on a Macbook Pro clocking at 2,6GHz (i5) and 8GB 1600MHz RAM. MySQL is installed using the downloadable binary from mysql's download page (unable to supply a third link because my rep is to low). It's a default installation with no major additions to the my.cnf config-file, included in the gist (located under /usr/local/mysql-5.6.xxx/ directory on my system).
我担心的是我到达了约160毫秒，这向我表明我缺少了一些东西.我曾经考虑过压缩表，但是我感觉缺少其他配置.另外，myisampack不在我的PATH中(我认为)，因此在进一步探索之前，我正在考虑其他优化.
My concern is that I'm reaching ~160ms which indicates to me that I'm missing something. I've considered compressing the table but I have a feeling that I'm missing other configurations. Also the myisampack wasn't in my PATH (I think) so I'm considering other optimisations before I explore this further.
任何建议都值得赞赏！
$ mysql --version
/usr/local/mysql-5.6.23-osx10.8-x86_64/bin/mysql Ver 14.14 Distrib 5.6.23, for osx10.8 (x86_64) using EditLine wrapper
表格
CREATE TABLE `blocks` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`begin_range` int(10) unsigned NOT NULL,
`end_range` int(10) unsigned NOT NULL,
`_location_id` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `begin_range` (`begin_range`,`end_range`)
) ENGINE=MyISAM AUTO_INCREMENT=2008839 DEFAULT CHARSET=ascii;
CREATE TABLE `locations` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`country` varchar(2) NOT NULL DEFAULT '',
`region` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`postalcode` varchar(255) DEFAULT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`metro_code` int(11) DEFAULT NULL,
`area_code` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=641607 DEFAULT CHARSET=utf8;
查询
SELECT locations.latitude, locations.longitude
FROM blocks
INNER JOIN locations ON blocks._location_id = locations.id
WHERE INET_ATON('139.130.4.5') BETWEEN begin_range AND end_range
LIMIT 0, 1;
编辑； 在SELECT上使用EXPLAIN更新了要点，为方便起见也在此处发布.
Edit; Updated gist with EXPLAIN on the SELECT, also posted here for convenience.
EXPLAIN SELECT locations.latitude, locations.longitude FROM blocks INNER JOIN locations ON blocks._location_id = locations.id WHERE INET_ATON('94.137.106.123') BETWEEN begin_range AND end_range LIMIT 0, 1;
+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+------------------------------------+
| 1 | SIMPLE | blocks | range | begin_range | begin_range | 4 | NULL | 1095345 | Using index condition; Using where |
| 1 | SIMPLE | locations | eq_ref | PRIMARY | PRIMARY | 4 | geoip.blocks._location_id | 1 | NULL |
+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+------------------------------------+
2 rows in set (0.00 sec)
编辑2;为方便起见，将数据包含在问题中.
Edit 2; Included data into the question for convenience.
推荐答案
问题和正常方法(您的代码所代表的)导致命中1095345行.我有一种方法可以在一个磁盘命中进行该查询，即使缓存很冷.
The problem, and the normal approach (which your code exemplifies) leads to hitting 1095345 rows. I have an approach that can do that query in one disk hit, even the cache is cold.
摘录自 http://mysql.rjweb.org/doc.php/ipranges:
情况
您的数据包含大量不重叠的范围".这些可能是IP地址，日期时间(单个站点的显示时间)，邮政编码等.
Your data includes a large set of non-overlapping 'ranges'. These could be IP addresses, datetimes (show times for a single station), zipcodes, etc.
您有成对的起始值和结束值；一个项目"属于每个这样的范围".因此，本能地创建一个表，其中包含范围的开始和结束以及有关该项目的信息.您的查询涉及一个WHERE子句，该子句比较起始值和结束值之间的值.
You have pairs of start and end values; one 'item' belongs to each such 'range'. So, instinctively, you create a table with start and end of the range, plus info about the item. Your queries involve a WHERE clause that compares for being between the start and end values.
问题
一旦您获得大量物品，性能就会下降.您在使用索引，但找不到任何有效的方法.索引无法导致最佳运行，因为数据库不了解范围是不重叠的.
Once you get a large set of items, performance degrades. You play with the indexes, but find nothing that works well. The indexes fail to lead to optimal functioning because the database does not understand that the ranges are non-overlapping.
解决方案
我将提出一个解决方案，以加强项目不能具有重叠范围这一事实.该解决方案会建立一个表格来利用这一点，然后使用存储例程来解决它所带来的笨拙.
I will present a solution that enforces the fact that items cannot have overlapping ranges. The solution builds a table to take advantage of that, then uses Stored Routines to get around the clumsiness imposed by it.
这篇关于在200万行MySQL MyISAM表上约150ms的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
