MySql优化-记录一次Mysql选错索引问题

文章主目录

表结构
总结
索引优化器逻辑
解决方式
案例二

暂时没找到原因

表结构

/******************************************/
/*   DatabaseName = bg_scm   */
/*   TableName = demeter_order_deliver   */
/******************************************/
CREATE TABLE `demeter_order_deliver` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `order_no` varchar(64) NOT NULL COMMENT '订单编号',
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_deliver_no` varchar(64) NOT NULL COMMENT '发货单编号',
  `stock_no` varchar(32) NOT NULL COMMENT '仓库编码',
  `stock_id` bigint(20) NOT NULL COMMENT '仓库ID',
  `stock_name` varchar(10) NOT NULL COMMENT '仓库名称',
  `company_id` bigint(20) NOT NULL COMMENT '订单所属公司ID',
  `status` varchar(32) NOT NULL DEFAULT '0' COMMENT '发货单状态',
  `weight_total` decimal(8,2) DEFAULT NULL COMMENT '商品总重量',
  `volume_total` decimal(12,2) DEFAULT NULL COMMENT '商品总体积',
  `consignee_realname` varchar(32) NOT NULL COMMENT '收货人姓名',
  `consignee_telphone` varchar(32) NOT NULL COMMENT '收货人联系电话',
  `province_id` bigint(12) NOT NULL COMMENT '收货省',
  `city_id` bigint(12) NOT NULL COMMENT '收货市',
  `district_id` bigint(12) NOT NULL COMMENT '收货区',
  `street_id` binary(12) DEFAULT NULL COMMENT '街道',
  `province_name` varchar(50) DEFAULT NULL COMMENT '省名称',
  `city_name` varchar(50) DEFAULT NULL COMMENT '市名称',
  `county_name` varchar(50) DEFAULT NULL COMMENT '区域名称',
  `street_name` varchar(50) DEFAULT NULL COMMENT '街道',
  `address` varchar(250) NOT NULL COMMENT '收货地址',
  `order_remark` varchar(256) DEFAULT NULL COMMENT '订单备注',
  `statement_date` date DEFAULT NULL COMMENT '订单归属日期',
  `estimate_deliver_date` datetime DEFAULT NULL COMMENT '预计送达时间',
  `real_deliver_date` datetime DEFAULT NULL COMMENT '实际送达时间',
  `out_stock_time` datetime DEFAULT NULL COMMENT '出库时间',
  `store_id` bigint(20) NOT NULL COMMENT '店铺id',
  `store_name` varchar(100) DEFAULT NULL COMMENT '店铺名称',
  `store_type` varchar(32) DEFAULT NULL COMMENT '店铺类别',
  `business_type` varchar(32) DEFAULT NULL COMMENT '店铺业务类型',
  `sort_proportion` int(11) DEFAULT '2' COMMENT '排序比重',
  `type` varchar(32) NOT NULL COMMENT '类型（配送、调度）',
  `route` varchar(20) DEFAULT NULL COMMENT '线路',
  `route_index` varchar(20) DEFAULT NULL COMMENT '线路序号',
  `route_platenumber` varchar(10) DEFAULT NULL COMMENT '车牌',
  `sorting_route` varchar(20) DEFAULT NULL COMMENT '分拣线路',
  `sorting` int(11) DEFAULT NULL COMMENT '分拣口',
  `number` int(11) NOT NULL DEFAULT '0' COMMENT '原始数量',
  `out_number` int(11) NOT NULL DEFAULT '0' COMMENT '出库数量',
  `out_weight` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '出库重量',
  `out_volume` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '出库体积',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `station_range_id` bigint(20) DEFAULT NULL COMMENT '配送站点范围id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_ORDER_DELIVER_IDX` (`order_deliver_no`),
  KEY `NK_PLATE_NUMBER_IDX` (`estimate_deliver_date`,`route_platenumber`),
  KEY `NK_ORDER_NO_IDX` (`order_no`),
  KEY `NK_ORDER_ID_IDX` (`order_id`),
  KEY `NK_DATE_STORE_ID_IDX` (`store_id`,`estimate_deliver_date`) USING BTREE,
  KEY `idx_statement_date` (`statement_date`) USING BTREE,
  KEY `NK_ESTIMATE_COMAPNY_STOCK_IDX` (`estimate_deliver_date`,`company_id`,`stock_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13816094 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='发货单明细表'
;

数据量

   SELECT count(1) FROM demeter_order_deliver

sql语句

  explain  SELECT *
FROM demeter_order_deliver
WHERE 
     `estimate_deliver_date` >= ?
    AND `estimate_deliver_date` < ? and `company_id` = ?
ORDER BY `id` DESC
LIMIT 0, 10

某天突然sql告警显示这条sql执行60多秒理论上是可以命中esttimate_deliver_date NK_ESTIMATE_COMAPNY_STOCK_IDX索引

我们手动执行以下也显示正常

    SELECT *FROM demeter_order_deliver
WHERE 
     `estimate_deliver_date` >= '2021-12-03 00:00:00.0'
    AND `estimate_deliver_date` < '2021-12-03 23:59:59.0' and `company_id` = 2
ORDER BY `id` DESC
LIMIT 0, 10

数据行

但是发现以下sql就没命中索引

    SELECT *
FROM demeter_order_deliver
WHERE 
     `estimate_deliver_date` >= '2021-12-03 00:00:00.0'
    AND `estimate_deliver_date` < '2021-12-03 23:59:59.0' and `company_id` = 3
ORDER BY `id` DESC
LIMIT 0, 10

优化成了主键索引导致全表扫描条件为3的数据行数只有1条

优化改为强制使用索引

       SELECT *
FROM demeter_order_deliver 
FORCE INDEX(NK_ESTIMATE_COMAPNY_STOCK_IDX)
WHERE 
     `estimate_deliver_date` >= '2021-12-03 00:00:00.0'
    AND `estimate_deliver_date` < '2021-12-03 23:59:59.0' and `company_id` = 3
ORDER BY `id` DESC
LIMIT 0, 10

回到顶部

总结

1.首先我们一个表的的索引是可以添加多个的

2.具体选择走哪个索引是索引优化器的工作

回到顶部

索引优化器逻辑

通过索引区分度

1.通过索引区分度越大越好,显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好

索引区分度是mysql选择索引的条件之一

show index FROM  [talbe]show index FROM  [talbe]

采样统计

因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

无论用哪种采样统计都不能精确的的表示不管M是10 或者16 当遇到超大数据量表都会导致延迟更新索引统计我们可以通过analyze table t 来触发重新统计索引信息

预估扫描行数

1.预估扫描行数也是优化器选择索引的指标之一,多个索引肯定选最小扫描行数

但是有时候mysql选错索引也是因为预估扫描行数不准导致的我们可以通过analyze table t 命令，可以用来重新统计索引信息。

主要是针对超大表按采样统计规则，

使用 ANALYZE TABLE 命令更新表的统计信息不会导致 MySQL 错误估算，相反，它可以帮助 MySQL 优化器更好地进行查询优化，以便选择更合适的执行计划。但是，如果表的数据分布不均匀，或者数据量较大，更新统计信息可能需要较长的时间，建议在非高峰期进行操作，避免影响业务。

针对order by

某些情况针对 order by mysql会优先选择走order by 条件的索引

如:select * from order_item where product_id=1 order by id limit 10; product_id有索引,id为主键索引

针对product_id(预估扫描行数较大一版出现于服务上id维度 userid维度等)和主键索引比较,主键索引同时满足以下优势:

1.主键id索引区分度最高

2.预估扫描行数最小

3.索引天然支持排序,不需要通过sort_buffer进行排序(如果走product_id 查询满足条件的可能有上万条，需要sort_buffer排序后，如果数据量超过sort_buffer还需要文件辅助排序根据排序结果取limit 10)

4.limit值比较小

针对limit值比较小,可能优化器会理想化的认为扫描前n行，一个理想化的数字比如limit 10,可能为认为最多扫描100行就可以查出满足条件的数据并返回(如果真是这种情况性能是由于provider_id 索引的),但是现实情况是全表扫描(上百万) 扫描到了最后才找到满足条件的10行数据

同理我们可以增大limit,limit越大实际扫描的行数可能就越多,可以让优化器忽略主键索引

满足以上条件优化器最终认为主键索引是最优选择,从而选择了主键索引,针对order by 不仅限于主键id，满足以上4个条件需要注意优化器选错索引问题。

如使用主键索引预计扫描行是100

走正常索引扫描是52440

回到顶部

解决方式

增大limit

优化器会认为需要的数据太大了，可能会导致更多的扫描, 从而忽略主键索引

强制走索引

通过force index

通过子查询

走sort_buffer进行排序

  EXPLAIN   SELECT *
FROM demeter_order_deliver o
WHERE 
     `estimate_deliver_date` >= '2021-12-03 00:00:00.0'
    AND `estimate_deliver_date` < '2021-12-03 23:59:59.0' and `company_id` = 3
ORDER BY  o.`volume_total`,`id`   DESC
LIMIT 0, 10

建议

不建议使用order by id,除非没有where条件的情况下,如果有where条件我们大概率都需要走where条件索引,如果单纯是想优化sort_buffer排序,那也是where和order by走同样索引走不了可以建立组合索引

回到顶部

案例二

这种很容易踩坑又遇到了了别人写的sql order by +limit

用户反馈搜索功能没有用,我们上去测试要29秒，但是他的数据才几万条,理论上全表扫描也不会有问题

  SELECT u.notes, u.contact_email, u.external_id, u.contact_mobile_phone, u.created_at
    , ug.id AS user_group_id, ug.name AS user_group_name, ug.shared AS user_group_shared, u.last_login_at, u.mobile_phone
    , u.phone, u.provider_id, u.name, u.id, u.email
    , u.updated_at, ue.name_pinyin, ue.nickname_pinyin
FROM user u
    LEFT JOIN user_group ug ON u.user_group_id = ug.id
    LEFT JOIN user_extra ue
    ON ue.user_id = u.id
        AND ue.provider_id = 8124
WHERE u.provider_id = 8124
    AND (u.deleted = 0
        OR u.deleted IS NULL)
    AND (u.suspended = 0
        OR u.suspended IS NULL)
    AND u.type = 'customer'
    AND u.valid = '1'
    AND (u.email LIKE '%plain.w@foxmail.c%'
        OR u.`name` LIKE '%plain.w@foxmail.c%'
        OR ue.`name_pinyin` LIKE '%plain.w@foxmail.c%'
        OR u.mobile_phone LIKE '%plain.w@foxmail.c%'
        OR u.nickname LIKE '%plain.w@foxmail.c%'
        OR ue.`nickname_pinyin` LIKE '%plain.w@foxmail.c%'
        OR u.phone LIKE '%plain.w@foxmail.c%'
        OR u.contact_email LIKE '%plain.w@foxmail.c%'
        OR u.contact_mobile_phone LIKE '%plain.w@foxmail.c%'
        OR u.external_id = 'plain.w@foxmail.c'
        OR u.id IN (
            SELECT urt.user_id
            FROM user_r_tag urt
                LEFT JOIN tag t ON t.id = urt.tag_id
            WHERE t.name LIKE '%plain.w@foxmail.c%'
                AND t.provider_id = 8124
        ))
ORDER BY u. `id`   DESC
LIMIT 20

看执行计划,选择PRMARY索引导致整个saas全表扫描

优化后改为created_at

posted @ 2021-12-07 19:49 意犹未尽阅读(123) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！

历史上的今天：
2018-12-07 Spring Cloud-Ribbon负载均衡策略类IRule(五)
2018-12-07 Spring Cloud-Ribbon ILoadBalancer负载均衡器核心源码(四)

公告

昵称：意犹未尽
园龄： 9年8个月
粉丝： 64
关注： 6

+加关注

2025年3月

日

一

二

三

四

五

六

意犹未尽

只要在学习，则在进步。

MySql优化-记录一次Mysql选错索引问题

表结构

总结

索引优化器逻辑

通过索引区分度

预估扫描行数

针对order by

解决方式

增大limit

强制走索引

通过子查询

走sort_buffer进行排序

建议

案例二

公告

搜索

常用链接

我的标签

合集

随笔档案

文章分类

阅读排行榜

评论排行榜

推荐排行榜

最新评论