MySQL 索引失效问题分析
来源:互联网 发布:易建联nba生涯数据 编辑:程序博客网 时间:2024/06/05 06:27
初步记录,尚未完成,有待完善。
mchdba-rm-huayuan.mysql.rds.aliyuncs.com:3306:pdd_oms 16:39:40> explain select count(*) as aggregate from `t_unship` where `batch_id` in ('380', '381', '382', '383', '384', '385', '386', '387', '388', '389', '390', '391', '392', '393', '394', '395', '396', '397', '398', '399', '400', '401', '402', '403', '404', '405', '406', '407', '408', '409', '410', '411', '412', '413', '414', '415', '416', '417', '418', '419', '420', '421', '422', '423', '424', '425', '426', '427', '428', '429', '430', '431', '432', '433', '434', '435', '436', '437', '438', '439', '440', '441') and `mall_id` = '23200' and `oprate_status` = '2' and `is_deleted` = '0';+----+-------------+----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+| 1 | SIMPLE | t_unship | range | idx_mall_id,idx_batch_id | idx_batch_id | 8 | NULL | 16182 | Using index condition; Using where |+----+-------------+----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+1 row in set (0.01 sec)mchdba-rm-huayuan.mysql.rds.aliyuncs.com:3306:pdd_oms 16:39:44> explain select count(1) as aggregate from `t_unship` force index(idx_mall_id) where `batch_id` in ('380', '381', '382', '383', '384', '385', '386', '387', '388', '389', '390', '391', '392', '393', '394', '395', '396', '397', '398', '399', '400', '401', '402', '403', '404', '405', '406', '407', '408', '409', '410', '411', '412', '413', '414', '415', '416', '417', '418', '419', '420', '421', '422', '423', '424', '425', '426', '427', '428', '429', '430', '431', '432', '433', '434', '435', '436', '437', '438', '439', '440', '441') and `mall_id` = '23200' and `oprate_status` = '2' and `is_deleted` = '0';+----+-------------+----------+------+---------------+-------------+---------+-------+--------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+-------------+---------+-------+--------+------------------------------------+| 1 | SIMPLE | t_unship | ref | idx_mall_id | idx_mall_id | 8 | const | 167532 | Using index condition; Using where |+----+-------------+----------+------+---------------+-------------+---------+-------+--------+------------------------------------+1 row in set (0.00 sec)mchdba-rm-huayuan.mysql.rds.aliyuncs.com:3306:pdd_oms 16:40:09> CREATE TABLE `t_unship` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '未发货流水id', `order_sn` varchar(127) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '关联的订单sn', `mall_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '关联的商家id', `goods_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '关联的商品id', `batch_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '处罚的批次id', `rule_type` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '处罚规则类型,0:提醒发货,1:超时2天未发货,2:超时4天未发货,3:超时6天未发货', `sn_type_unique` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'order_sn和rule_type生成的唯一组合串', `check_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '确认处罚时间', `confirm_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '订单确认时间', `promise_ship_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '承诺发货时间', `punish_amount` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '处罚金额', `oprate_status` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '处罚操作状态,0:未操作,1:忽略,2:已完成', `is_coupon` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '是否已发优惠券', `is_custom_sms` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '是否已经给用户发短信', `is_deleted` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '逻辑删', `created_at` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '创建时间', `updated_at` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '修改时间', `coupon_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '发出的优惠券ID', `province_id` int(10) unsigned NOT NULL DEFAULT '0', `province_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `city_id` int(10) unsigned NOT NULL DEFAULT '0', `city_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `idx_goods_id` (`goods_id`), KEY `idx_unique` (`sn_type_unique`), KEY `t_unship_order_sn_index` (`order_sn`), KEY `t_unship_province_id_index` (`province_id`), KEY `t_unship_city_id_index` (`city_id`), KEY `idx_mall_id_opratus_status_is_deleted_index` (`mall_id`,`oprate_status`,`is_deleted`) USING BTREE, KEY `idx_batch_id_mall_id_opratus_status_is_deleted_index` (`batch_id`,`mall_id`,`oprate_status`,`is_deleted`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=24397264 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应发未发订单流水表'思路,就是增加联合索引,先把问题解决掉再说。参考文档:http://blog.csdn.net/xifeijian/article/details/19773795
阅读全文
0 0
- MySQL 索引失效问题分析
- MySQL索引失效情况分析
- mysql唯一索引失效原因分析
- ORACLE索引失效的问题分析
- ORACLE索引失效的问题分析(转载)
- mysql innodb 索引失效问题引起表级锁
- mysql索引失效统计
- mysql索引的失效
- mysql 时间索引失效
- mysql索引失效原因
- mysql索引失效
- mysql--索引失效
- Mysql索引会失效的几种情况分析
- Mysql索引会失效的几种情况分析
- mysql索引失效的五种情况分析
- Mysql索引会失效的几种情况分析
- Mysql索引会失效的几种情况分析
- Mysql索引会失效的几种情况分析
- 修改定制SpringBoot的启动banner图案
- 从零自学Hadoop(11):Hadoop命令上
- DFS:100. Same Tree
- 32位汇编的基本框架
- 2017-9-23(servlet登录和448. Find All Numbers Disappeared in an Array)
- MySQL 索引失效问题分析
- java回调
- [栈] brackets 括号序列
- ACM-ICPC北京赛区(2017)网络赛-题目9 : Minimum(线段树)
- 第五节第一大特性-封装
- Windows Server2008部署python爬虫环境
- Longest Substring Without Repeating Characters
- Lucene和solr
- Ubuntu 16.04设置root用户登录图形界面