专栏首页方丈的寺院分库分表后的索引问题

分库分表后的索引问题

摘要

最近遇到一个慢sql,在排查过程中发现和分库分表后的索引设置有关系,总结了下问题。

问题

在进行应用健康度盘点时,发现有个慢sql 如下

select brandgoodid from brandgood_0020
where  userid = xxx AND
brandgoodid  in("xxx1","xxx2")

表结构,按照userid进行的分表

CREATE TABLE`brandgood_0020` (
  `brandgoodid` char(30) NOT NULL COMMENT ,
  `user_id` int(10) unsigned DEFAULT NULL COMMENT '用户id',
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`brandgoodid`),
  KEY `idx_userid` (`userid`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8

explain一下发现走的是userid这个索引,一个用户下面有很多商品,也就有了很多brandgoodid,所以有可能会很慢,因为要扫描很多的索引键去过滤brandgoodid值。而写这个SQL的人期望走的主键索引,而不是'userid'的索引。因为用主键索引,就是N次主键扫描(N表示in中的数量)。

分析

直接原因很明显

IN 这个查询误导了mysql的优化器,选错了索引 IN 查询常常会影响mysql server的判断。主要是IN里面的值数量不同,会影响扫描行数的不同,所以常常会出现索引选择不一致。之前也总结过一篇SQL IN 一定走索引吗

解决

因为用户查询的brandgoodlid是限定在某个group维度下的,一个group对应的brandgood是有限的,在这个业务中,通常小于10。所以这个地方使用主键索引,效率更高。解决方法也就是这地方需要 force index强制走PRIMARY index。

扩展

分库分表后的索引

为什么题目叫分库分表后的索引问题的,直接原因和分库分表并没有什么关系啊?因为在排查问题时,犯了一个错误。以为路由到具体的brandgood_0020表后,可以直接根据brandgoodid主键索引来查询了。认为和一些分布式数据库(cassandra)一样,是clustering key+partition key这种索引数据。可以根据clustering key到数据的节点的partition块,然后根据local index 找到对应的数据。

但其实mysql的分库分表不一样,分表键不是索引,只是客户端路由。只负责找到对应的表。到表以后,就是和单表一样查询逻辑。

因为分表键不是索引,但是查询语句是必须要带着分表键,那意味着我们的分库分表以后的表索引大部分要建成联合索引了,分表键+索引键

要不然我们的查询语句 select xx from table where 分表键=xxx AND a =xxx,是走不了联合索引的。只能走单索引。单索引mysql server要面临着索引选择的问题。

当然并不是绝对的,比如上面我举的那个案例。按照这个思路查看了下其他的分表索引。果然表上的大部分索引都是非联合索引,还是直接从单表copy过来的索引。这些索引基本上都是无用的,因为都的是userid索引.

索引选择的问题

mysql为什么会选错索引呢,详细的请看10 | MySQL为什么有时候会选错索引

我们这个案例是因为判断扫描行数的时候出问题了。

本文分享自微信公众号 - 方丈的寺院(gh_c98f244e174d),作者:cnstonefang

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-03-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 存储优化(3)-mongo大表加索引

    在存储优化(2)-排序引起的慢查询优化中我们提到过排序对查询选择索引的影响。但是的解决办法就是增加一个索引。在线上给mongo的大表增加一个索引要慎重。在增加索...

    方丈的寺院
  • 聊聊Mysql索引和redis跳表

    面试时,交流有关mysql索引问题时,发现有些人能够涛涛不绝的说出B+树和B树,平衡二叉树的区别,却说不出B+树和hash索引的区别。这种一看就知道是死记硬背,...

    方丈的寺院
  • web性能优化的15条实用技巧

    1.访问DOM会影响浏览器性能,修改DOM则更耗费性能,因为他会导致浏览器重新计算页面的几何变化。<通常的做法是减少访问DOM的次数,把运算尽量留在JS这一端。

    方丈的寺院
  • MySql数据库索引原理

    第二部分结合MySQL数据库中InnoDB数据存储引擎中索引的架构实现讨论聚集索引、非聚集索引及覆盖索引等话题。

    哲洛不闹
  • 索引使用策略及优化

    MySQL的优化主要分为结构优化(Scheme optimization)和查询优化(Query optimization)。本章讨论的高性能索引策略主要属于结...

    互扯程序
  • 你真的了解索引了吗(一)

    给大家介绍下,我是集美貌与才华于一身的程序媛钱六六,人在美国,刚下飞机,就收到了男神无双的短信轰炸

    黑洞代码
  • Mysql - 组合索引的B+树存储结构(最左前缀原理)

    那组合索引的B+树存储结构是什么样的呢,为什么会有最左前缀原理,看了很多帖子找到了答案

    夹胡碰
  • MySQL数据库,索引的数据结构及基本原理

    索引的数据结构和具体存储引擎的实现有关,在 MySQL 中使用较多的索引有 Hash 索引,B+树索引等,而我们经常使用的 InnoDB 存储引擎的默认索引实现...

    用户1289394
  • MySQL面试题

    BTree索引,主流有两种,一种是B树,每一个叶子节点和中间节点中都存在有数据和指针;另一个是B+树,所有的数据都存储在叶子节点,中间节点也是一个索引。

    石的三次方
  • Mysql索引:图文并茂,深入探究索引的原理和使用

    相信每个IT界大佬,简历上少不了Mysql索引这个关键字,但如果被问起来,你能说出多少干货呢?先看下面几个问题测试一下吧:

    IT大咖说

扫码关注云+社区

领取腾讯云代金券