【mysql】mysql表分区、索引的性能测试

阅读量：5798 次

发布时间：2019-06-18

本文共 39337 字，大约阅读时间需要 131 分钟。

概述

mysql分区表概述：google搜索一下；

主要测试mysql分区表的性能；

load 500w 条记录：大约在10min左右；

batch insert 1.9w条记录（没建立索引）：存在500w条记录的情况下批量插入，速度很快，基本1s左右；

batch insert 1.9w条记录（建立1个索引）：存在500w条记录的情况下批量插入，速度变慢，基本3s左右（建立的索引越多，速度会越慢）；

查询：通过where对分区进行过滤，使用了表分区之后，性能提升很明显；

建立索引查询时的性能：数据量大时，B+TREE索引若需要进行回表查询（无法索引覆盖），则性能很差；

建立索引查询时的性能：数据量不大时，B+TREE索引性能不错（8w时的数据量，性能不如无索引的性能）；

索引覆盖 vs 非索引覆盖：速度相差十几倍；

多列索引，索引顺序影响：性能相差20倍；

性能对比

如下数据都是查询多次的平均值（首次查询时，耗时都比较长）

耗时	未使用索引	使用索引	未分区表	分区表	特点	备注
load data			8 min 26.03 sec	11 min 11.01 sec		非分区表的插入性能好些
batch insert			0.29 sec	0.56 sec	3000-records	批量插入性能差不多
batch insert			1.85 sec	1.4 sec	1.9w-records	批量插入性能差不多
batch insert			未测试	3~4 sec	1.9w-records	索引建立的越多，插入越慢
query1			3.38 sec	3.36 sec	count(*),没有where	性能差不多
query2			4 sec	0.6 sec	将分区作为过滤条件	分区表的性能，提升了好多倍
query3			5.7 sec	1.8 sec	将分区作为过滤条件，group by	分区表的性能，提升了3倍左右
query4	1.26s	26s			使用了B+Tree索引，产生了大量的随机IO	使用索引虽然查询条数减少，性能反而下降的厉害
query5

表结构

未分区表

| performance_metirc_host_min10_hour | CREATE TABLE `performance_metirc_host_min10_hour` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `pool_id` char(36) NOT NULL COMMENT '资源池ID',  `host_id` char(36) NOT NULL COMMENT '主机ID',  `indicator_key` varchar(64) NOT NULL COMMENT '指标key',  `value` double DEFAULT NULL COMMENT '指标值',  `resource_type` varchar(64) NOT NULL COMMENT '资源类型',  `create_at` datetime NOT NULL COMMENT '最近一次添加或更新的时间',  `business_id` char(36) DEFAULT NULL COMMENT '业务系统ID',  `organization_id` char(36) DEFAULT NULL COMMENT '部门ID',  `vpc_id` char(36) DEFAULT NULL COMMENT 'vpc维度',  `security_id` char(36) DEFAULT NULL COMMENT '安全域ID',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5046203 DEFAULT CHARSET=utf8 COMMENT='该表用于保存裸金属指标项数据'

分区表

根据indicator_key创建分区表；

主键使用：PRIMARY KEY (id,indicator_key)而不是PRIMARY KEY (id)，

原因：使用mysql分区表的限制，分区的列必须包含在所有的唯一索引或主键中；

| performance_metirc_host_part_min10_hour | CREATE TABLE `performance_metirc_host_part_min10_hour` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `pool_id` char(36) COLLATE utf8_bin NOT NULL COMMENT '资源池ID',  `host_id` char(36) COLLATE utf8_bin NOT NULL COMMENT '主机ID',  `indicator_key` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '指标key',  `value` double DEFAULT NULL COMMENT '指标值',  `resource_type` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '资源类型',  `create_at` datetime NOT NULL COMMENT '最近一次添加或更新的时间',  `business_id` char(36) COLLATE utf8_bin DEFAULT NULL COMMENT '业务系统ID',  `organization_id` char(36) COLLATE utf8_bin DEFAULT NULL COMMENT '部门ID',  `vpc_id` char(36) COLLATE utf8_bin DEFAULT NULL COMMENT 'vpc维度',  `security_id` char(36) COLLATE utf8_bin DEFAULT NULL COMMENT '安全域ID',  PRIMARY KEY (`id`,`indicator_key`)) ENGINE=InnoDB AUTO_INCREMENT=4999308 DEFAULT CHARSET=utf8 COLLATE=utf8_bin/*!50500 PARTITION BY RANGE  COLUMNS(indicator_key)(PARTITION bm_cpu VALUES LESS THAN ('bm_statistic_cpu') ENGINE = InnoDB, PARTITION bm_disk VALUES LESS THAN ('bm_statistic_disk') ENGINE = InnoDB, PARTITION bm_mem VALUES LESS THAN ('bm_statistic_mem') ENGINE = InnoDB, PARTITION vm_cpu VALUES LESS THAN ('vm_statistic_cpu') ENGINE = InnoDB, PARTITION vm_disk VALUES LESS THAN ('vm_statistic_disk') ENGINE = InnoDB, PARTITION vm_mem VALUES LESS THAN ('vm_statistic_mem') ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ |

导入数据（load）

数据准备:

使用Java生成数据（代码见下文）；

记录条数：500W条；

使用load的方式导入数据；

非分区表

MySQL [test]> load data local infile '/opt/data/tmp/hostpartSql2.data' into table performance_metirc_host_min10_hour fields terminated by ',' enclosed by '\'';Query OK, 4999314 rows affected, 65535 warnings (8 min 26.03 sec)Records: 5000000  Deleted: 0  Skipped: 686  Warnings: 4999692MySQL [test]> select count(*) from performance_metirc_host_min10_hour;+----------+| count(*) |+----------+|  4999314 |+----------+1 row in set (3.41 sec)

总耗时： 8 min 26.03 sec；

分区表

MySQL [test]> load data local infile '/opt/data/tmp/hostpartSql2.data' into table performance_metirc_host_part_min10_hour fields terminated byQuery OK, 4999361 rows affected, 65535 warnings (11 min 11.01 sec)Records: 5000000  Deleted: 0  Skipped: 639  Warnings: 4999692MySQL [test]> select count(*) from performance_metirc_host_part_min10_hour;+----------+| count(*) |+----------+|  4999361 |+----------+1 row in set (3.36 sec)

总耗时： 10 min 52.22 sec；

查看各分区的数据分布情况

MySQL [test]> select partition_name, TABLE_SCHEMA,  table_rows from information_schema.partitions where table_name='performance_metirc_host_part_min10_hour';+----------------+--------------+------------+| partition_name | TABLE_SCHEMA | table_rows |+----------------+--------------+------------+| bm_cpu         | test         |        154 || bm_disk        | test         |     803897 || bm_mem         | test         |     803297 || vm_cpu         | test         |     802386 || vm_disk        | test         |     802738 || vm_mem         | test         |     802532 || pmax           | test         |     804355 |+----------------+--------------+------------+

从上面可以看出，各分区的记录分布比较平均，每一个分区的数据大约都在80万左右；

批量插入3000、1.9w条记录（没建立索引时）

sql语句如下所示（完整sql没有列举完）

INSERT INTO performance_metirc_host_min10_hour(pool_id,host_id,indicator_key,value,resource_type,create_at,business_id,organization_id,vpc_id,security_id) VALUES ('7b8f0f5e2fbb4d9aa2d5fd55466d638e', 'fd623404-301a-402a-a57c-b6202737d218', 'vm_statistic_cpu_avg_util_percent', '0.056361832611832606', 'vm', '2017-12-01 06:00:00', 'a02f53f285804dda82dc7d1817513c70', '1da69607a73349bb909e65294e44c3a5', null, null),('7b8f0f5e2fbb4d9aa2d5fd55466d638e', '003c958b-2286-4933-a30f-6c050ec0ae37', 'vm_statistic_cpu_avg_util_percent', '0.05548400673400674', 'vm', '2017-12-09 06:00:00', 'a02f53f285804dda82dc7d1817513c70', '1da69607a73349bb909e65294e44c3a5', null, null),.........;

非分区表

1.9w条数据：平均时间1s左右；

MySQL [test]> use test;MySQL [test]> source /opt/data/tmp/insert3000Record.sql;Query OK, 3033 rows affected (0.29 sec)Records: 3033  Duplicates: 0  Warnings: 0MySQL [test]> source /opt/data/tmp/insert19000Records.sql;Query OK, 18654 rows affected (1.85 sec--10次的平均值)Records: 18654  Duplicates: 0  Warnings: 0

分区表

1.9w条数据：平均时间1s左右；

MySQL [test]> source /opt/data/tmp/insert3000Record.sql;Query OK, 3033 rows affected (0.56 sec)Records: 3033  Duplicates: 0  Warnings: 0MySQL [test]> source /opt/data/tmp/insert19000Records.sql;Query OK, 18654 rows affected (1.40 sec--10次的平均值)Records: 18654  Duplicates: 0  Warnings: 0

批量插入1.9w条记录（建立1个索引）

alter table performance_metirc_host_part_min10_hour add key indicator_create_busi_idx(indicator_key, create_at, business_id);

批量查询测试：1.6s~8.65s，平均时间：3~4s

MySQL [test]> source /opt/data/tmp/insert19000Records.sql;Query OK, 18654 rows affected (3.51 sec)Records: 18654  Duplicates: 0  Warnings: 0

批量插入1.9w条记录（建立多个索引）

索引如下：

PRIMARY KEY (`id`,`indicator_key`), KEY `indicator_create_busi_idx` (`indicator_key`,`create_at`,`business_id`), KEY `indicator_busi_create_idx` (`indicator_key`,`business_id`,`create_at`)

批量查询测试：1.6s~9.5s，平均时间：4s

MySQL [test]> source /opt/data/tmp/insert19000Records.sql;Query OK, 18654 rows affected (3.51 sec)Records: 18654  Duplicates: 0  Warnings: 0

查询数据

Query1：没有进行分区过滤

example1:

//count统计，没有进行分区过滤select count(*) from performance_metirc_host_min10_hour;select count(*) from performance_metirc_host_part_min10_hour;

example2:

//没有进行分区过滤select distinct(create_at) from performance_metirc_host_min10_hour;select distinct(create_at) from performance_metirc_host_part_min10_hour;MySQL [test]> explain partitions select distinct(create_at) from performance_metirc_host_part_min10_hour \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: performance_metirc_host_part_min10_hour   partitions: bm_cpu,bm_disk,bm_mem,vm_cpu,vm_disk,vm_mem,pmax   //全部分区都使用了         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4822392        Extra: Using temporary1 row in set (0.00 sec)

Query2：

非分区表

平均时间：4s

没有分区信息；

没有建立索引；

遍历表：查询了500w条记录；

MySQL [test]> select avg(value) from performance_metirc_host_min10_hour where indicator_key = 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' ;+-------------------+| avg(value)        |+-------------------+| 50.09309208798831 |+-------------------+1 row in set (4.09 sec)MySQL [test]> select max(value) from performance_metirc_host_min10_hour where indicator_key = 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' ;+-------------------+| max(value)        |+-------------------+| 99.99980456042323 |+-------------------+1 row in set (3.50 sec)MySQL [test]> explain partitions select avg(value) from performance_metirc_host_min10_hour where indicator_key = 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: performance_metirc_host_min10_hour   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 5042030  // 查询了500w条数据        Extra: Using where1 row in set (0.00 sec)

分区表

平均时间：0.6s

使用了分区信息；

没有建立索引；

遍历表：查询了80w~92w条记录，比非分区表少查询了6倍多（刚刚是分区的个数）；

MySQL [test]> select avg(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' ;+-------------------+| avg(value)        |+-------------------+| 50.09288924799467 |+-------------------+1 row in set (0.60 sec)MySQL [test]> select max(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' ;+-------------------+| max(value)        |+-------------------+| 99.99980456042323 |+-------------------+1 row in set (0.60 sec)MySQL [test]> explain partitions select avg(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: performance_metirc_host_part_min10_hour   partitions: vm_cpu   //只使用了vm_cpu分区         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 802386  // 只查询了80w条数据        Extra: Using where1 row in set (0.00 sec)MySQL [test]> explain partitions select avg(value) from performance_metirc_host_part_min10_hour where indicator_key= 'vm_statistic_disk_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: performance_metirc_host_part_min10_hour   partitions: vm_mem  // 只使用了vm_mem分区         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 929035 // 值查询了92w条数据        Extra: Using where1 row in set (0.00 sec)ERROR: No query specified

Query3：

非分区表

平均时间：5.7 sec

MySQL [test]> select avg(value) from performance_metirc_host_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 02:50:00'  group by organization_id;+--------------------+| avg(value)         |+--------------------+|   50.0384388700016 ||    49.954251371279 ||   50.1629822975072 |+--------------------+9 rows in set (5.59 sec)MySQL [test]> select max(value) from performance_metirc_host_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 02:50:00'  group by organization_id;+-------------------+| max(value)        |+-------------------+| 99.99964338156543 || 99.99855115581629 || 99.99941828293112 |+-------------------+9 rows in set (5.86 sec)MySQL [test]> select max(value) from performance_metirc_host_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 02:50:00'  group by business_id;+-------------------+| max(value)        |+-------------------+| 99.99964338156543 || 99.99898161250623 || 99.99980456042323 |+-------------------+11 rows in set (5.50 sec)MySQL [test]> select avg(value) from performance_metirc_host_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 02:50:00'  group by business_id;+--------------------+| avg(value)         |+--------------------+|   50.1993472498974 ||  50.04430780009459 || 50.078605604109285 |+--------------------+11 rows in set (5.57 sec)

分区表

平均时间：1.8s

MySQL [test]> select max(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 02:50:00'  group by organization_id;+-------------------+| max(value)        |+-------------------+| 99.99818300251297 || 99.99855115581629 || 99.99941828293112 |+-------------------+9 rows in set (1.86 sec)MySQL [test]> select avg(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 02:50:00'  group by organization_id;+--------------------+| avg(value)         |+--------------------+|   50.0384388700016 || 49.954023140979096 ||  50.16278417450607 |+--------------------+9 rows in set (1.86 sec)MySQL [test]> select max(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id;+-------------------+| max(value)        |+-------------------+| 99.99536010412046 || 99.99898161250623 || 99.99980456042323 |+-------------------+11 rows in set (1.24 sec)MySQL [test]> select avg(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 02:50:00'  group by business_id;+--------------------+| avg(value)         |+--------------------+|   50.1993472498974 || 50.285359967063464 || 50.078605604109285 |+--------------------+11 rows in set (1.77 sec)

Query4：使用B+TREE索引--回表查询-查询性能反而大幅度降低

查询语句：

select indicator_key, avg(value) as value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id order by value asc;

未添加索引

平均查询时间：1.26s(10次的平均结果)

总查询条数：80w，使用了全表扫描；

使用了 vm_cpu 分区：大大提升了性能

MySQL [test]> select  indicator_key, avg(value) as value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id order by value asc;+-----------------------------------+--------------------+| indicator_key                     | value              |+-----------------------------------+--------------------+| bm_statistic_mem_avg_util_percent | 49.845570215053264 || bm_statistic_mem_avg_util_percent |  49.90994276843408 || bm_statistic_mem_avg_util_percent |  50.01579830123528 || bm_statistic_mem_avg_util_percent | 50.036187114557514 || bm_statistic_mem_avg_util_percent | 50.056310301051525 || bm_statistic_mem_avg_util_percent |   50.1082718123528 || bm_statistic_mem_avg_util_percent | 50.116061996684614 || bm_statistic_mem_avg_util_percent |  50.15219690174755 || bm_statistic_mem_avg_util_percent |   50.1848819477595 || bm_statistic_mem_avg_util_percent |   50.2105660859758 || bm_statistic_mem_avg_util_percent | 50.384555005273285 |+-----------------------------------+--------------------+11 rows in set (1.45 sec)MySQL [test]> explain select  indicator_key, avg(value) as value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id order by value asc;+----+-------------+-----------------------------------------+------+---------------+------+---------+------+--------+----------------------------------------------+| id | select_type | table                                   | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |+----+-------------+-----------------------------------------+------+---------------+------+---------+------+--------+----------------------------------------------+|  1 | SIMPLE      | performance_metirc_host_part_min10_hour | ALL  | NULL          | NULL | NULL    | NULL | 802386 | Using where; Using temporary; Using filesort |+----+-------------+-----------------------------------------+------+---------------+------+---------+------+--------+----------------------------------------------+MySQL [test]> explain partitions select  indicator_key, avg(value) as value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id order by value asc;+----+-------------+-----------------------------------------+------------+------+---------------+------+---------+------+--------+----------------------------------------------+| id | select_type | table                                   | partitions | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |+----+-------------+-----------------------------------------+------------+------+---------------+------+---------+------+--------+----------------------------------------------+|  1 | SIMPLE      | performance_metirc_host_part_min10_hour | vm_cpu     | ALL  | NULL          | NULL | NULL    | NULL | 802386 | Using where; Using temporary; Using filesort |+----+-------------+-----------------------------------------+------------+------+---------------+------+---------+------+--------+----------------------------------------------+1 row in set (0.00 sec)

索引 indicator_create_busi_idx

此索引无法使用BusinessId，因为create_at一般为范围查询；

KEY `indicator_create_busi_idx` (`indicator_key`,`create_at`,`business_id`),

添加索引后的查询时间：26s(10次的平均结果)，性能急剧下滑

查询总条数：40w；

使用了filesort文件排序；

使用了索引：indicator_create_busi_idx(indicator_key, create_at, business_id)， 查询性能反而降低了20倍左右；

只使用了 vm_cpu 分区：大大提升了性能；

详细见下面：

alter table performance_metirc_host_part_min10_hour add key indicator_create_busi_idx(indicator_key, create_at, business_id);// 只使用了 vm_cpu 分区： 提升了性能MySQL [test]> explain partitions select  indicator_key, avg(value) as value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id order by value asc;+----+-------------+-----------------------------------------+------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+| id | select_type | table                                   | partitions | type | possible_keys             | key                       | key_len | ref   | rows   | Extra                                        |+----+-------------+-----------------------------------------+------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+|  1 | SIMPLE      | performance_metirc_host_part_min10_hour | vm_cpu     | ref  | indicator_create_busi_idx | indicator_create_busi_idx | 194     | const | 401193 | Using where; Using temporary; Using filesort |+----+-------------+-----------------------------------------+------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+MySQL [test]> explain  select  indicator_key, avg(value) as value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id order by value asc;+----+-------------+-----------------------------------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+| id | select_type | table                                   | type | possible_keys             | key                       | key_len | ref   | rows   | Extra                                        |+----+-------------+-----------------------------------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+|  1 | SIMPLE      | performance_metirc_host_part_min10_hour | ref  | indicator_create_busi_idx | indicator_create_busi_idx | 194     | const | 401193 | Using where; Using temporary; Using filesort |+----+-------------+-----------------------------------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+1 row in set (0.00 sec)MySQL [test]> show create table performance_metirc_host_part_min10_hour;| performance_metirc_host_part_min10_hour | CREATE TABLE `performance_metirc_host_part_min10_hour` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `pool_id` char(36) COLLATE utf8_bin NOT NULL COMMENT '资源池ID',  `host_id` char(36) COLLATE utf8_bin NOT NULL COMMENT '主机ID',  `indicator_key` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '指标key',  `value` double DEFAULT NULL COMMENT '指标值',  `resource_type` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '资源类型',  `create_at` datetime NOT NULL COMMENT '最近一次添加或更新的时间',  `business_id` char(36) COLLATE utf8_bin DEFAULT NULL COMMENT '业务系统ID',  `organization_id` char(36) COLLATE utf8_bin DEFAULT NULL COMMENT '部门ID',  `vpc_id` char(36) COLLATE utf8_bin DEFAULT NULL COMMENT 'vpc维度',  `security_id` char(36) COLLATE utf8_bin DEFAULT NULL COMMENT '安全域ID',  PRIMARY KEY (`id`,`indicator_key`),  KEY `indicator_create_busi_idx` (`indicator_key`,`create_at`,`business_id`)) ENGINE=InnoDB AUTO_INCREMENT=10287524 DEFAULT CHARSET=utf8 COLLATE=utf8_bin/*!50500 PARTITION BY RANGE  COLUMNS(indicator_key)(PARTITION bm_cpu VALUES LESS THAN ('bm_statistic_cpu') ENGINE = InnoDB, PARTITION bm_disk VALUES LESS THAN ('bm_statistic_disk') ENGINE = InnoDB, PARTITION bm_mem VALUES LESS THAN ('bm_statistic_mem') ENGINE = InnoDB, PARTITION vm_cpu VALUES LESS THAN ('vm_statistic_cpu') ENGINE = InnoDB, PARTITION vm_disk VALUES LESS THAN ('vm_statistic_disk') ENGINE = InnoDB, PARTITION vm_mem VALUES LESS THAN ('vm_statistic_mem') ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ |

索引 indicator_busi_create_idx

KEY `indicator_busi_create_idx` (`indicator_key`,`business_id`,`create_at`)//注意并非（索引列的顺序不同）：KEY `indicator_create_busi_idx` (`indicator_key`,`create_at`,`business_id`),

添加索引后的查询时间：26s(10次的平均结果)，性能急剧下滑

查询总条数：40w；

使用了filesort文件排序；

使用了索引：indicator_create_busi_idx(indicator_key, create_at, business_id)， 查询性能反而降低了20倍左右；

只使用了 vm_cpu 分区：大大提升了性能；

详细见下面：

MySQL [test]> explain partitions select  indicator_key, avg(value) as value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id order by value asc;+----+-------------+-----------------------------------------+------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+| id | select_type | table                                   | partitions | type | possible_keys             | key                       | key_len | ref   | rows   | Extra                                        |+----+-------------+-----------------------------------------+------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+|  1 | SIMPLE      | performance_metirc_host_part_min10_hour | vm_cpu     | ref  | indicator_busi_create_idx | indicator_busi_create_idx | 194     | const | 401193 | Using where; Using temporary; Using filesort |+----+-------------+-----------------------------------------+------------+------+---------------------------+---------------------------+---------+-------+--------+----------------------------------------------+1 row in set (0.00 sec)MySQL [test]> select  indicator_key, avg(value) as value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00'  group by business_id order by value asc;+-----------------------------------+--------------------+| indicator_key                     | value              |+-----------------------------------+--------------------+| bm_statistic_mem_avg_util_percent |  49.84557021505294 || bm_statistic_mem_avg_util_percent | 49.909942768434064 || bm_statistic_mem_avg_util_percent |  50.01579830123537 || bm_statistic_mem_avg_util_percent | 50.036187114557656 || bm_statistic_mem_avg_util_percent |  50.05631030105144 || bm_statistic_mem_avg_util_percent |  50.10827181235255 || bm_statistic_mem_avg_util_percent |  50.11606199668445 || bm_statistic_mem_avg_util_percent |  50.15219690174774 || bm_statistic_mem_avg_util_percent | 50.184881947759685 || bm_statistic_mem_avg_util_percent |  50.21056608597557 || bm_statistic_mem_avg_util_percent | 50.384555005273334 |+-----------------------------------+--------------------+11 rows in set (32.06 sec)

总结

indicator_busi_create_idx 和 indicator_create_busi_idx 对此查询的性能基本一样；

原因：

该查询只能使用到 indicator_key，无法使用到 businessId，都将导致大量的回表查询，大量的随机IO；

实验结果：

当数据量很大时，BTREE索引如果需要进行回表查询（未能索引覆盖），产生大量随机IO，导致查询性能很差；

未使用索引，平均时间：1.26s；

使用索引，平均时间：26s；

添加索引后，性能下降了20倍左右;

原因推测

使用索引后，B+Tree索引需要进行主键二次查询，即需要回表查询，虽然总查询条数变少了（80w减少到40w），但是会产生大量的随机IO，严重影响查询性能；（B+Tree索引在大数据量下性能很差）

不使用索引，直接进行全表顺序扫描，虽然总扫描条数较多（80w），但是不是随机IO磁盘读写，性能反而比索引的随机IO性能要好；

索引覆盖 vs 非索引覆盖

KEY `indicator_busi_create_idx` (`indicator_key`,`business_id`,`create_at`) // 特别注意：不是该索引  KEY `indicator_create_busi_idx` (`indicator_key`,`create_at`,`business_id`) // indicator_create_busi_idx中，create_at为范围查询，最左前缀原则，将会导致Business_id不可用；

性能对比

非覆盖索引

返回值中，包含value，该值不在索引中，无法使用索引覆盖；

平均下来，使用了 1.19 sec

//rows=7.8w， 可以和 indicator_create_busi_idx 索引对比（rows=40w左右）： 可见，将create_at放在索引的最后，过滤的条数很明显MySQL [test]> explain select indicator_key, business_id, value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' and business_id = '93d79263806742f190c6e6b9e7a1c08d';+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+-------------+| id | select_type | table                                   | type  | possible_keys             | key                       | key_len | ref  | rows  | Extra       |+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+-------------+|  1 | SIMPLE      | performance_metirc_host_part_min10_hour | range | indicator_busi_create_idx | indicator_busi_create_idx | 308     | NULL | 78028 | Using where |+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+-------------+1 row in set (0.00 sec)MySQL [test]> select indicator_key, business_id, value from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' and business_id = '93d79263806742f190c6e6b9e7a1c08d';37846 rows in set (1.19 sec)

rows=7.8w，可以和 indicator_create_busi_idx 索引对比（rows=40w左右）：可见，将create_at放在索引的最后，过滤的条数很明显；

覆盖索引

返回值中，只包含 indicator_key, business_id，可以使用索引覆盖;

平均下来，使用了 0.09 sec

MySQL [test]> explain select indicator_key, business_id from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' and business_id = '93d79263806742f190c6e6b9e7a1c08d';+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+--------------------------+| id | select_type | table                                   | type  | possible_keys             | key                       | key_len | ref  | rows  | Extra                    |+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+--------------------------+|  1 | SIMPLE      | performance_metirc_host_part_min10_hour | range | indicator_busi_create_idx | indicator_busi_create_idx | 308     | NULL | 78028 | Using where; Using index |+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+--------------------------+MySQL [test]> select indicator_key, business_id from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' and business_id = '93d79263806742f190c6e6b9e7a1c08d';37846 rows in set (0.09 sec)

总结

非覆盖索引：1.19s；

覆盖索引：0.09s；

速度提升了十几倍；

多列索引，索引顺序影响

indicator_busi_create_idx： 平均：1.15s（8w数据量）

indicator_create_busi_idx：平均：26s左右（40w~80w数据量）

无索引：平均: 0.8s（80w数据量）

indicator_busi_create_idx：平均：1.15s

将范围查询的create_at放到索引列的最后；(8w数量)

KEY `indicator_busi_create_idx` (`indicator_key`,`business_id`,`create_at`)MySQL [test]> explain select indicator_key, business_id , avg(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' and business_id = '93d79263806742f190c6e6b9e7a1c08d';+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+--------------------------+| id | select_type | table                                   | type  | possible_keys             | key                       | key_len | ref  | rows  | Extra                    |+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+--------------------------+|  1 | SIMPLE      | performance_metirc_host_part_min10_hour | range | indicator_busi_create_idx | indicator_busi_create_idx | 308     | NULL | 78028 | Using where; Using index |+----+-------------+-----------------------------------------+-------+---------------------------+---------------------------+---------+------+-------+--------------------------+MySQL [test]> select indicator_key, business_id, avg(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' and business_id = '93d79263806742f190c6e6b9e7a1c08d';+-----------------------------------+----------------------------------+--------------------+| indicator_key                     | business_id                      | avg(value)         |+-----------------------------------+----------------------------------+--------------------+| bm_statistic_mem_avg_util_percent | 93d79263806742f190c6e6b9e7a1c08d | 50.036187114557656 |+-----------------------------------+----------------------------------+--------------------+1 row in set (1.15 sec)

indicator_create_busi_idx：平均：26s左右

将范围查询的create_at放到索引列的前面，导致BusinessId无法索引；（80w数据量）

和indicator_busi_create_idx相比，整整多了10倍的数据返回，这些都是随机IO；

KEY `indicator_create_busi_idx` (`indicator_key`,`create_at`,`business_id`)MySQL [test]> select indicator_key, business_id, avg(value) from performance_metirc_host_part_min10_hour where indicator_key='bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' and business_id = '93d79263806742f190c6e6b9e7a1c08d';+-----------------------------------+----------------------------------+--------------------+| indicator_key                     | business_id                      | avg(value)         |+-----------------------------------+----------------------------------+--------------------+| bm_statistic_mem_avg_util_percent | 93d79263806742f190c6e6b9e7a1c08d | 50.036187114557656 |+-----------------------------------+----------------------------------+--------------------+1 row in set (25.34 sec)

无索引：平均: 0.8s

将使用全表扫描（80w数据量）

MySQL [test]> select indicator_key, business_id, avg(value) from performance_metirc_host_part_min10_hour where indicator_key= 'bm_statistic_mem_avg_util_percent' and create_at >='2017-12-10 01:00:00' and create_at <='2017-12-10 01:50:00' and business_id = '93d79263806742f190c6e6b9e7a1c08d';+-----------------------------------+----------------------------------+--------------------+| indicator_key                     | business_id                      | avg(value)         |+-----------------------------------+----------------------------------+--------------------+| bm_statistic_mem_avg_util_percent | 93d79263806742f190c6e6b9e7a1c08d | 50.036187114557514 |+-----------------------------------+----------------------------------+--------------------+1 row in set (0.8 sec)

附件

数据准备代码

import java.io.File;import java.io.FileWriter;import java.io.IOException;import java.util.ArrayList;import java.util.List;import java.util.Random;import java.util.UUID;public class WriteHostPartdata {    private static final List
    
      poolIDList = new ArrayList<>();    private static final List
     
       indicatorKeyList = new ArrayList<>();    private static final List
      
        timeList = new ArrayList<>();    private static final List
       
         busiIdList = new ArrayList<>();    private static final List
        
          orgaIdList = new ArrayList<>();    static {        poolIDList.add("7b8f0f5e2fbb4d9aa2d5fd55466d638e");        poolIDList.add("7b8f0f5e2fbb4d9aa2d5fd55466d63df");        poolIDList.add("7b8f0f5e2fbb4d9aa2d5fd55466d63er");        poolIDList.add("7b8f0f5e2fbb4d9aa2d5fd55466d6398");        indicatorKeyList.add("bm_statistic_cpu_avg_util_percent");        indicatorKeyList.add("bm_statistic_disk_avg_util_percent");        indicatorKeyList.add("bm_statistic_mem_avg_util_percent");        indicatorKeyList.add("vm_statistic_cpu_avg_util_percent");        indicatorKeyList.add("vm_statistic_disk_avg_util_percent");        indicatorKeyList.add("vm_statistic_mem_avg_util_percent");        timeList.add("2017-12-10 01:00:00");        timeList.add("2017-12-10 01:10:00");        timeList.add("2017-12-10 01:20:00");        timeList.add("2017-12-10 01:30:00");        timeList.add("2017-12-10 01:40:00");        timeList.add("2017-12-10 01:50:00");        timeList.add("2017-12-10 02:00:00");        timeList.add("2017-12-10 02:10:00");        timeList.add("2017-12-10 02:20:00");        timeList.add("2017-12-10 02:30:00");        timeList.add("2017-12-10 02:40:00");        timeList.add("2017-12-10 02:50:00");        busiIdList.add("8fe3e7bcebf540d1ae47ef5b53f62524");        busiIdList.add("93d79263806742f190c6e6b9e7a1c08d");        busiIdList.add("6e1141b4328843f09176fcc6928fab74");        busiIdList.add("59562271f4e6483cb784cea5cdb8bc8f");        busiIdList.add("c29ef5146d2641a2b6d7b731866e73b0");        busiIdList.add("10a86c53d54e46c2bedab6899075f41e");        busiIdList.add("ef818a8080db48568dd9f34cec21999a");        busiIdList.add("1384eb7cde9a497891a7ed743a66cc70");        busiIdList.add("3085f77c8fc8451683864a578ec94fdf");        busiIdList.add("aa6183cb7704431f857e8e63c63a7b84");        busiIdList.add("dbf5233183fd40679768552b16d73491");        orgaIdList.add("1da69607a73349bb909e65294e44c3a5");        orgaIdList.add("e1b72aa209654aa9a21acd59e6c9b7d6");        orgaIdList.add("3feb63ee93a046adada742f18b278f6d");        orgaIdList.add("defe080c3802423aa3e84a59f269b7a0");        orgaIdList.add("b62eff24281a4935a853cca65c7608da");        orgaIdList.add("d3701686cc0b4f0da4eead39fa807bd7");        orgaIdList.add("f90b3f78a9d641ba8aa942d912d1adc7");        orgaIdList.add("43e03831ef8c4e52a8541ad465efcb67");        orgaIdList.add("65458cc498e8481e8bf915a6947916b3");    }    public static void main(String[] args) {        String file = "D:\\tempTempTemp\\hostpartSql2.data";        writeFile(file);    }    public static void writeFile(String fileName) {        try {            FileWriter fw = new FileWriter(new File(fileName));            for (int i = 1; i < 500_0001; i++) {                //id                fw.write("'");                fw.write(i);                fw.write("'");                fw.write(",");                //poolId                fw.write("'");                fw.write(poolIDList.get(new Random().nextInt(poolIDList.size())));                fw.write("'");                fw.write(",");                //hostId: uuid                fw.write("'");                fw.write(UUID.randomUUID().toString());                fw.write("'");                fw.write(",");                //indicator_key                fw.write("'");                fw.write(indicatorKeyList.get(new Random().nextInt(indicatorKeyList.size())));                fw.write("'");                fw.write(",");                //value                fw.write("'");                fw.write(String.valueOf(new Random().nextDouble() * 100));                fw.write("'");                fw.write(",");                //resource_type                fw.write("'");                fw.write("");                fw.write("'");                fw.write(",");                //create_at                fw.write("'");                fw.write(timeList.get(new Random().nextInt(timeList.size())));                fw.write("'");                fw.write(",");                //business_id                fw.write("'");                fw.write(busiIdList.get(new Random().nextInt(busiIdList.size())));                fw.write("'");                fw.write(",");                //organization_id                fw.write("'");                fw.write(orgaIdList.get(new Random().nextInt(orgaIdList.size())));                fw.write("'");                fw.write(",");                //vpc_id                fw.write("'");                fw.write("");                fw.write("'");                fw.write(",");                //security_id                fw.write("'");                fw.write("");                fw.write("'");                fw.write("\n");                if (i % 50000 == 0) {                    System.out.println("Finish:" + i / 50000);                }            }            fw.close();        } catch (IOException e1) {        }    }}