mysql建立索引 性能测试_MySQL分区和索引性能测试

MySQL分区和索引性能测试

定位系统数据库设计方案定位系统数据库设计方案 ————历史轨迹表历史轨迹表 作者：叶闻达作者：叶闻达 2012.9.5 目录目录 1．引言．引言 .3 1.1 目标.3 1.2 范围.3 2．方案描述．方案描述 .3 2.1 表结构.3 2.2 不同方案的介绍.4 2.2.1原方案4 2.2.2一张表4 2.2.3季节分区4 2.2.4月份分区4 2.2.5员工分区4 2.2.6员工分表和月份分区4 2.2.7员工分区和月份分区4 2.2.8员工分区加索引5 3．查询速度测试．查询速度测试 .5 3.1 数据描述.5 3.2 历史轨迹查询.5 3.2.1测试方法描述5 3.2.2测试结果(所有测试结果单位为秒)5 3.3 位置查询.6 3.3.1测试方法描述6 3.3.2测试结果(所有测试结果单位为秒)6 3.4 结果分析.6 4．插入速度测试．插入速度测试 .6 3.1 概述.6 3.2 测试方法描述.7 3.3 测试结果(所有测试结果单位为秒).7 3.4 结果分析.7 5．总结．总结 .7 1．引言．引言 1.1 目标目标 之前的系统 Track(轨迹)表的设计是由员工 ID 号生成，也就是说一千个人，一千个 数据表。这使得查询位置的时候，需要从一千个不同的表中搜集数据。故尝试重新设计。 1.2 范围范围 仅涉及原系统的历史轨迹查询和位置查询两个功能，以及 Track 表。 2．方案描述．方案描述 2.1 表结构表结构 原方案的表结构： CREATE TABLE `track_1094` ( `track_id` bigint(20) NOT NULL AUTO_INCREMENT, `trackTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `x` double NOT NULL, `y` double NOT NULL, `z` double NOT NULL, `area` double NOT NULL, PRIMARY KEY (`track_id`), KEY `trackTime` (`trackTime`) ) ENGINE=InnoDB AUTO_INCREMENT=549207 DEFAULT CHARSET=utf8; 新方案的表结构： CREATE TABLE prison_all ( track_id bigint(20) NOT NULL, trackTime timestamp NOT NULL default CURRENT_TIMESTAMP, id int NOT NULL, x double default NULL, y double default NULL, z double default NULL, area double default NULL )engine=archive DEFAULT CHARSET=utf8; 由于新方案主要尝试用一张表解决问题，所以加了员工的 ID，相当于原方案中的 MAC 地 址(标签)，engine 一开始选用的是 archive，最后换回了 InnoDB。 2.2 不同方案的介绍不同方案的介绍 2.2.1 原方案原方案 Engine：Archive，简称：员工分表。设计了一千个员工，id 范围(0-1000) 。得到一千 张表。 2.2.2 一张表一张表 Engine：Archive，用于测试时的比较。所有的数据在一张表中，没有其他处理。 2.2.3 季节分区季节分区 Engine：Archive，一张表，四个分区，按照时间。 2.2.4 月份分区月份分区 Engine：Archive，一张表，12 个分区，按照时间。 2.2.5 员工分区员工分区 Engine：Archive，一张表，按照员工 ID 使用 HASH 分区法分为 100 个分区。 2.2.6 员工分表和月份分区员工分表和月份分区 Engine：Archive，先按员工数分为 1000 张表，再按照月份将每个表分为 12 个分区。 1000*12=12000 各分区。 2.2.7 员工分区和月份分区员工分区和月份分区 Engine：Archive，一张表，先按月份分为 12 个分区，再按照员工 ID 号将每个月份分 区分为 80 个次分区，形成共 960 个分区。 2.2.8 员工分区加索引员工分区加索引 Engine：InnoDB，一张表，按照员工 ID 使用 HASH 分区法分为 500 个分区，为 id 和 tracktime 加上复合索引，因为，两种查询都会用到这两列值。 3．查询速度测试．查询速度测试 3.1 数据描述数据描述 将一亿条数据分别插入 8 种方案，以供查询。数据时间范围 1 年、任意天数、任意小 时数、20 分钟、0 秒。即并不完全是随机时间。这样做是希望一亿条数据能基本覆盖所有 可能出现的时间值。每人 1 亿/1000=10 万条，时间可能数：365*24*20=17 万。这个方案并 不合理，但最开始的时候造出的数据只能沿用了。否则再插一遍几亿的数据太浪费时间。 3.2 历史轨迹查询历史轨迹查询 3.2.1 测试方法描述测试方法描述 查询一定时间段内某名员工的 track 数据。 查询语句： Select * from table_name where id = ? and trackTime ? ; 3.2.2 测试结果(所有测试结果单位为秒)测试结果(所有测试结果单位为秒) 亿级别历史轨迹查询亿级别历史轨迹查询(s)(s) 1 时1 天5 天10 天1 月2 月 1 1 员工分表(原方案) 0.40.40.40.40.50.50.60.60.70.72.92.9 2 2 就一张表 280280 没测没测没测没测没测 3 3 季节分区 250250 没测没测没测没测没测 4 4 月份分区 225225 没测没测没测没测没测 5 5 员工分区 2.12.12.32.32.42.42.42.42.52.52.72.7 6 6 员工分表+月份分区 FailFailFailFailFailFail 7 7 员工+月份分区 530530 没测没测没测没测没测 8 8 员工分区加索引 0.020.020.050.050.160.160.20.20.70.70.90.9 3.3 位置查询位置查询 3.3.1 测试方法描述测试方法描述 查询一定人群在特定时间点的位置。 查询语句： Select * from table_name where id ? and trackTime = ? ; 3.3.2 测试结果(所有测试结果单位为秒)测试结果(所有测试结果单位为秒) 亿级别位置查询亿级别位置查询1 个人10 人100 人所有人 1 1 员工分表(原方案) 0.30.34 44545477477 5 5 员工分区 3.33.3291291291291295295 7 7 员工+月份分区 0.50.53838 没测没测 8 8 员工分区加索引 0.020.029 99.59.59 9 3.4 结果分析结果分析 由于方案 3(季节分区)和方案 4(月份分区)在历史轨迹的第