• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL InnoDB索引设计策略

MySQL InnoDB 彭东稳 7年前 (2017-01-06) 20196次浏览 已收录 0个评论

一、索引设计原则

在MySQL中常用的B+树索引分为聚簇索引和辅助索引,关于辅助索引,虽然个数没有限制(聚簇索引一个表只能由一个),但可以想想一下,任何事物都是有两面性的:建立索引,是为了提高查询性能,但这是以降低写入性能为代价的。因为所有索引,在表需要写入数据时,都需要去维护索引数据以保证所有索引都是最新的、最准确的,所以可想而知,索引越多,写入性能越差,对索引上锁的时间也会越长(由于InnoDB是索引组织表,锁都是建立在索引之上)。更严重的是,如果有唯一索引,为了保证唯一索引的这个特性,每次修改或插入数据时都会去检查一下唯一性(产生共享锁),在RR隔离级别下,经常会造成死锁。所以在建立索引时一定要仔细权衡,建立出来的索引要个个为精,个个有用,这样才能保证在最大程度提高查询性能的情况下,最小程度地影响对表的修改。

1.1 索引创建原则

  • 最左前缀匹配原则

非常重要的原则,MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4,如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

=和in可以乱序,比如a = 1 and b = 2 and c = 3,建立(a,b,c)索引可以任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的形式。

  • 尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录。所以这种情况在某些特定情况下加索引还是有效果的。

另外区分度低的字段在组合索引中使用是没有问题的,但一般区分度低的一般放组合索引的最右边。

  • 计算列里无法使用索引

索引列不能参与计算,保持列“干净”。比如from_unixtime(create_time) = ’2017-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2017-05-29’)。

  • 尽量的扩展索引,不要新建索引

比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。这样可以节省没必要的索引创建。

  • 多利用覆盖索引

MySQL利用B+树索引检索数据的时候,如果不是基于聚簇索引,或者说如果不是基于主键的检索,那么即便是SQL语句能够利用索引,但索引返回的信息也只是所需结果行的主键值,要取得全部数据,还需要通过这些主键值重新导数据文件里再做一次检索操作,这样就需要额外的IO,降低了查询效率。如果能优化SQL或索引,让MySQL只需要通过索引就可以返回所需的数据,而不必回表,从而提升效率,这就是覆盖索引。简单说就是返回字段就是where条件字段,比如select name from table where name=‘’;这样的。

  • 组合索引等号条件字段在前

在创建组合索引时,比如“a>=xx and b=xx”查询,建立索引时应该为(b,a),根据索引排序特性,这样扫描的数据就会越少。如果查询为“a=xx and b=xx”,建立组合索引时,谁的选择性高就把谁放在索引前面,这样扫描的数据也会少。

  • 负向条件查询不能很好使用索引

负向条件查询默认是不能很好的使用索引,基本都是索引扫描。比如“!=”、“not in”、“not exists”都不是好习惯。

  • 前导模糊查询不能使用索引

假如有这样一列code的值为’AAA’,’AAB’,’BAA’,’BAB’ ;如果where code like ‘%AB’条件,由于前面是模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like ‘A% ‘,就可以查找CODE中A开头的CODE的位置,当碰到B开头的数据时,就可以停止查找了,因为后面的数据一定不满足要求,这样就可以利用索引了。

  • 表字段字符集不同时不能使用索引

当进行多表join时,如果进行比较的条件字段字符集不同则可能不会使用索引,因为不同的字符集类型其比较规则不同。这里为什么使用可能不会使用索引呢?因为就算字符集不同时要以驱动表为主,比如驱动表字符集为utf8,而被驱动表字符集为utf8mb4时,这时就无法使用索引。但是当驱动表字符集为utf8mb4时,被驱动表为utf8时,这时是可以使用索引的,因为utf8mb4字符集兼容utf8。

  • 索引不存储NULL值

更准确的说,单列索引不存储NULL值,复合索引不存储全为NULL的值。如果允许为NULL,可能会得到“不符合预期”的结果集,比如“select count(*) from user where name != “dkey””的查询,如果允许name字段为NULL,因为索引不存储NULL值,所以结果集中不会统计这些记录。

另外由于索引不能存储NULL,所以对这列采用IS NULL条件时,因为索引上根本没NULL值,不能利用到索引,只能全表扫描。

为什么索引列不能存NULL值?

将索引列值进行建树时,其中必然涉及到诸多的比较操作。NULL值的特殊性就在于参与的运算大多取值为NULL。这样的话,NULL值实际上是不能参与进建索引的过程。也就是说,NULL值不会像其他取值一样出现在索引树的叶子节点上。

  • 强制类型转换不能使用索引

强制类型转换有一个名词叫“隐式转换”,简单来说就是查询条件值跟查询字段数据类型不匹配。比如:select * from user where phone = ‘xx’查询,条件查询值类型为字符型,但是phone字段数据类型为数值型,此时就发送了所谓的隐式转换,故不会使用索引。

  • 索引并非越多越好

当建立索引之后,每一次的写入数据随之带来的是需要索引的变化,也就是增加写操作。

  • 数据量不多不需要建立索引

由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。当然具体还要视情况而为。另外当要扫描的数据为总数据的大概30%左右时MySQL优化器可能也不会使用索引,会直接全表扫描。

1.2 索引创建误区

对于懂点数据库的人来说,基本都明白主键索引(primary key)、唯一索引(unique index)、普通索引(inde)与主键约束(primary key)、唯一键约束(unique key)的区别,这里说的索引都是Btree索引。

普通索引是MySQL最基本的B+树索引类型,它没有任何限制。而唯一索引它与”普通索引”类似,不同的就是:索引列的值必须唯一,但允许为NULL。如果是联合唯一索引,则列值的组合必须唯一。

这里要说明一点,唯一键约束和主键约束创建时都会默认隐式创建同名的唯一索引,以键名为索引名。唯一键约束索引列的值必须唯一,但允许为NULL。而主键约束索引不允许有空值,且在一张表中只允许有一个主键约束,但允许存在复合主键索引,也就是两个字符共同组成一个主键索引,两个字段组合必须唯一。一般是在建表的时候同时创建主键约束。

唯一键约束和主键约束与唯一索引的区别是,当主键约束或者唯一键约束失效时,隐式创建的唯一索引会被删除。但你可以先创建唯一索引,后创建主键约束或唯一键约束。这样显式创建的唯一索引不受主键约束或唯一键约束失效的影响。

所以经常会碰到研发同学写一些冗余索引,最常见的就是下面三种:

  • 创建主键后,还对主键字段创建一个唯一索引。
  • 创建唯一索引后,还对唯一索引字段创建一个普通索引。
  • 创建组合索引a,b后,还对a字段创建单列索引。

冗余索引只有缺点没有优点,缺点如下:

  • 占用磁盘空间,导致备份变慢。
  • 导致DML操作缓慢,MySQL B+树索引是高度平衡的,每次DML操作都需要更新索引。
  • 多余的唯一索引会大大增加死锁的产生。
  • 会导致查询变慢,特定情况下会导致MySQL优化器选择错误的路径,无法有效利用好的索引。

二、索引使用规则

在以下几种情况下MySQL在查询中即使有索引也不会去使用(参照上面索引创建原则):

1.在多列索引,查询条件中用的不是最左列,那么此时是不会使用索引。

2.like查询时%出现在第一位也不会使用索引。

3.where条件中有or、<>、not int、not exists可能也不会使用索引。

4.索引列类型是字符串,可在查询中省略了引号(有时字符串类型列也会存储数字),当做int型查询,此时不会使用索引。反之int型查询加引号也一样。

5.如果MySQL优化器估计使用全表扫描比使用索引快,它也不会使用索引。

6.  多表Join时,条件字符集不同也不会使用索引。

三、索引优化基准

合理的索引设计能够有效加速数据库的访问,提高查询的执行效率,减少用户查询对服务端的资源消耗。但是不合理的、低效的、冗余的甚至无效的索引不仅无法起到加速查询的效果,反而会影响数据库的插入、更新性能,甚至是数据库的高可用方案能否生效。

  • 主键索引缺失

由于MySQL默认存储引擎InnoDB(MySQL 5.6版本 以上)使用的是聚簇索引表设计,这就要求所有的表必须包含一个主键,所有的数据记录按照主键次序构建B+树。如果用户在创建表时显式指定主键,则数据库会使用用户指定的主键构建B+树,但是如果用户没有显式指定主键,同时也没有创建任何唯一键索引,InnoDB为了确保每张表至少包含一个主键,则默认会为用户生成一个“隐含主键”,该主键对用户不可见,甚至对于MySQL Server层的binlog也不可见。binlog是连接MySQL主从复制节点的纽带,所有主节点的更新都是通过binlog传递给从节点的,一旦binlog中没有更新记录的主键ID,这就会导致基于Row格式的binlog在从节点执行时,无法唯一确定一条记录,只能通过全表扫描来进行匹配,大幅降低了从机的执行效率,造成复制延迟。如果是高可用故障切换的从节点,会导致切换的时间大幅增加,甚至会导致高可用机制失效。如果是实现读写分离的只读从节点,则会导致应用读到的数据可能是很久以前的旧数据。所以我们建议使用InnoDB存储引擎的MySQL用户在创建表时,必须显式指定主键。

  • 主键索引与业务相关

如果用户在创建表时指定的主键与业务相关,可能会被频繁的更新,这样会引起MySQL数据库的InnoDB存储引擎进行频繁的节点合并和分裂,造成大量额外的系统IO开销,影响数据库的插入和更新性能。我们推荐开发者在创建表时指定与业务无关的自增字段作为主键,这样不仅会提高按时间序插入的性能(顺序写入硬盘),同时也可以提高按插入时间范围检索的查询效率。

  • 冗余索引

如果一个索引涉及的字段属性包含另外一个索引涉及的字段属性,同时两个索引字段顺序一致,且两个索引的首字段属性相同,则可以认为涉及字段少的索引为冗余索引。在MySQL 5.7推出sys库之前,我们可以通过percona的工具pt-duplicate-key-checker来完成对冗余索引的检查,在MySQL 5.7中,我们可以通过sys库schema_redundant_indexes表来完成。

  • 低效索引

索引的作用在于通过索引,查询能够扫描更少的记录。数据库中的记录在索引字段区分度越高,扫描的记录数就越少,执行的效率就越高。如果数据库表中的记录在索引字段区分度不大,索引对记录的筛选结果就不明显,索引就无法起到加速查询的作用。通过数据库记录在索引字段的区分度,我们可以衡量索引的执行效率。MySQL系统库mysql库下,innodb_index_stats表的stat_value字段,记录了某张表在某个索引的不同取值的记录个数,innodb_table_stats表的n_rows字段记录了某张表的总记录数,二者相除,即可得到数据库记录在某个索引的区分度,越接近1,表示区分度越高,低于0.1,则说明区分度较差,开发者应该重新评估SQL语句涉及的字段,选择区分度高的多个字段创建索引,通过运行下面的SQL语句,就可以计算每张表的索引区分度。

也可以在这个WHERE条件后面跟上t.database_name=’库名’ AND t.table_name=’表名’用于精确查找某个表的索引区分度。

  • 无效索引

如果一个索引始终无法被查询使用,它的存在只能增加数据库的维护开销,开发者应该及时删除这些索引。通过MySQL 5.7 sys库schema_unused_indexes视图,可以查看当前实例哪些索引从没有被使用。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!