请教各位大神,在主从中,相同表结构,相同数据,同一个sql,从库走索引,主库就不走索引。
show index from tr_order 命令查看索引都是生效的,并没有被关闭。
在从库用exlpain执行计划看sql,走索引。
在主库用exlpain执行计划看sql,不走索引。
请问各位大神有遇到这样的问题吗?该如何解决。
show index from tr_order 命令查看索引都是生效的,并没有被关闭。
在从库用exlpain执行计划看sql,走索引。
在主库用exlpain执行计划看sql,不走索引。
请问各位大神有遇到这样的问题吗?该如何解决。
如果确定走索引更快的话,用force index绑定一下吧
CREATE TABLE `tr_order` (
`ctb_order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`order_no` char(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '13位的时间戳 + 自增号段(3位)+ 本机IP(3位)+ PID(3位) + 数据类型(2位)+ 币种ID(3位)+ 随机数(5位)',
`customer_uuid` char(32) COLLATE utf8mb4_bin NOT NULL COMMENT '[用户关联ID]',
`currency_id` int(11) NOT NULL COMMENT '[币种ID]',
`base_currency_id` int(11) NOT NULL COMMENT '[基础币ID]',
`buyOrSell` int(11) DEFAULT NULL COMMENT '[买卖方向] 1. 买入 2.卖出',
`type` int(11) DEFAULT NULL COMMENT '[委托类别] 1. 限价 2. 市价',
`num` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[委托数量]',
`trade_num` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[成交数量]',
`cancel_num` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[撤单数量]',
`remain_num` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[未成交数量] 部分成交之后剩余的数量,初始值等于委托数量 remain_num = num-trade_num-cacel_num',
`price` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[委托价格]',
`source` int(11) DEFAULT '1' COMMENT '[委托来源] 1. web浏览器(默认)',
`order_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '[委托时间] 目前等于Create_Time',
`fee` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[委托手续费] buyOrSell 为1时:标识为数字货币 2:时标识为人民币',
`freeze_fee` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[冻结手续费] 暂时为预留字段',
`freeze_amount` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[冻结金额]',
`risk_flag` int(11) DEFAULT '1' COMMENT '[风控标示] 1. 无风控预警 2. 风控预警',
`cancel_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '[撤单时间]',
`average_price` decimal(32,16) DEFAULT '0.0000000000000000' COMMENT '[平均成交价] 有成交后回写',
`status` int(11) NOT NULL DEFAULT '0' COMMENT '[状态] 0. 未成交 1. 部分成交 2. 全部成交 3. 委托失败 4. 全部撤单 5. 部分成交后撤单',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`create_by` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`last_edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_edit_by` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`ctb_order_id`),
UNIQUE KEY `order_no_idx` (`order_no`),
KEY `order_cuuuid_indx` (`customer_uuid`) USING HASH,
KEY `order_currency_indx` (`currency_id`) USING HASH,
KEY `order_basecurrency_indx` (`base_currency_id`) USING HASH,
KEY `status_index` (`status`),
KEY `link_index` (`customer_uuid`,`currency_id`,`base_currency_id`,`status`),
KEY `ordertime` (`order_time`)
) ENGINE=InnoDB AUTO_INCREMENT=407749478 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='委托单表';sql:
EXPLAIN
SELECT
order_no,
customer_uuid,
currency_id,
base_currency_id,
buyOrSell,
type,
num,
trade_num,
cancel_num,
remain_num,
price,
order_time,
fee,
risk_flag,
cancel_time,
average_price,
STATUS
FROM
tr_order
WHERE
1 = 1
AND customer_uuid = '3462a7a8845741089e628f2804be5c2b'
AND currency_id = 219
AND base_currency_id = 63
AND STATUS IN (0, 1)
AND type = 1
AND order_time >= '2019-03-14'
ORDER BY
ctb_order_id DESC
LIMIT 0,
50;主库执行计划:
从库执行计划
1 = 1
AND customer_uuid = '3462a7a8845741089e628f2804be5c2b'
AND currency_id = 219
AND base_currency_id = 63
AND STATUS IN (0, 1)
AND type = 1
AND order_time >= '2019-03-14'这个where条件总共返回多少行?贴下这个信息:
show index from tr_order;