MySQL索引优化实战

留言

有这样一张表,因为没有分表,一直堆积,有1.4亿行数据,建表语句也比较另类,没有id字段,将四个业务字段合在一起作为主键

https://www.cnblogs.com/skying555/p/4852958.html

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `tbl_tx_detail_index` (
  `address` char(80) NOT NULL DEFAULT '' COMMENT '交易地址',
  `desc_block_height` int(11) NOT NULL COMMENT '倒序区块高度',
  `tx_hash` char(66) NOT NULL DEFAULT '' COMMENT '交易hash',
  `tx_index` int(11) NOT NULL COMMENT '该event在交易eventlog里的索引',
  `called_contract_hash` char(42) NOT NULL DEFAULT '' COMMENT '该交易真正调用的合约hash',
  `tx_time` int(11) NOT NULL COMMENT '交易时间戳',
  `asset_name` varchar(64) NOT NULL DEFAULT '' COMMENT '交易资产名',
  `tx_direction` tinyint(4) NOT NULL COMMENT '交易方向 0:FROM 1:TO 2:BOTH',
  PRIMARY KEY (`address`,`desc_block_height`,`tx_hash`,`tx_index`),
  KEY `idx_contract_hash_block_height` (`called_contract_hash`,`desc_block_height`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中 address = 'AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK' 的记录将近7000万行，执行

1
2
3
SELECT address
FROM tbl_tx_detail_index
WHERE address = 'AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK'

非常慢，这是对应的explain结果:

1
2
3
explain SELECT address
        FROM tbl_tx_detail_index
        WHERE address = 'AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK'

1 SIMPLE tbl_tx_detail_index ref PRIMARY PRIMARY 240 const 69274499 100.00 Using index

也用了索引,就是单纯的要扫的行数多..


还有另外一张表,表结构看起来比较正常:

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
CREATE TABLE `tbl_tx_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tx_hash` varchar(66) NOT NULL DEFAULT '' COMMENT '交易hash',
  `tx_type` int(11) NOT NULL COMMENT '区块链交易类型，208：部署合约交易 209：调用合约交易',
  `tx_time` int(11) NOT NULL COMMENT '交易时间戳',
  `block_height` int(11) NOT NULL COMMENT '区块高度',
  `amount` decimal(40,20) NOT NULL COMMENT '交易金额',
  `fee` decimal(40,20) NOT NULL COMMENT '交易手续费',
  `asset_name` varchar(64) NOT NULL DEFAULT '' COMMENT '交易资产名',
  `from_address` varchar(255) NOT NULL DEFAULT '' COMMENT '交易fromaddress',
  `to_address` varchar(255) NOT NULL DEFAULT '' COMMENT '交易toaddress',
  `description` varchar(1000) NOT NULL DEFAULT '' COMMENT '交易描述',
  `block_index` int(11) NOT NULL COMMENT '交易在区块里的索引',
  `tx_index` int(11) NOT NULL COMMENT '该event在交易eventlog里的索引',
  `confirm_flag` int(11) NOT NULL COMMENT '交易落账标识  1：成功 0：失败',
  `event_type` int(11) NOT NULL COMMENT '交易event类型 0:其他 1:部署合约 2:手续费 3:转账 4:ONT ID 5:存证 6:权限',
  `contract_hash` varchar(255) NOT NULL DEFAULT '' COMMENT '该event对应的合约hash',
  `payer` varchar(255) NOT NULL DEFAULT '' COMMENT '交易的payer',
  `called_contract_hash` varchar(255) NOT NULL DEFAULT '' COMMENT '该交易真正调用的合约hash',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_tx_hash_index` (`tx_hash`,`tx_index`),
  KEY `idx_from_address` (`from_address`),
  KEY `idx_to_address` (`to_address`),
  KEY `idx_block_height` (`block_height`) USING BTREE,
  KEY `idx_called_contract_hash` (`called_contract_hash`),
  KEY `idx_tx_time` (`tx_time`)
) ENGINE=InnoDB AUTO_INCREMENT=104194601 DEFAULT CHARSET=utf8;
1
2
3
SELECT COUNT(DISTINCT tx_hash)
        FROM tbl_tx_detail
        WHERE from_address = 'AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK' OR to_address = 'AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK'

这个地址是治理地址,所以有非常多的记录(占比差不多30%-50%)

改造一下,用

1
2
3
SELECT id
        FROM tbl_tx_detail
        WHERE from_address = 'AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK' OR to_address = 'AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK'

避免回表,先看看效果

or会拖慢,简化成

1
2
3
SELECT id
        FROM tbl_tx_detail
    where to_address = 'AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK'

要扫5000多万行…

查询count(*),

10万行: 0.1s级

100万行: 0.3s

1000万行: 2.7s

1000万-2000万行: 3.4s

文章目录