Mysql的索引设计上,应该如何考虑?

MySql的索引数据结构

适当的索引会大大提高查询速度,但同时会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引。

MySQL主要有两种索引结构:Hash索引和B+树索引。
Hash索引是将索引字段转化为hashcode,仅支持Memory引擎。
B+ 树是一种多路平衡查询树,是多叉树。对比普通二叉树,可以减少磁盘IO,提高检索速度。InnoDB和MyISAM的索引都是采用B+树的结构
哈希结构,B+树结构对比:
1.哈希索引没办法利用索引完成排序
2.B+树范围查询的时候不需要做全表扫描
3.哈希索引不支持联合索引的最左匹配规则
4.哈希索引查找时,如果是指定值查找,时间复杂度只要O(1)

InnoDB和MyISAM索引结构的区别

  • InnoDB使用的是聚集索引,即实际的数据行和相关的键值保存在一起。如下图所示。所以如果是主键索引查找的话,是非常高效的。如果是非主键索引查找,就可能产生回表。即先通过非主键索引找到对应的主键,然后再按照主键索引找到相应的数据记录。但是,如果需要查找的数据本身已经在索引中,就无需回表,这叫覆盖索引(covering index)
    例子:
    表covering_index_sample中有一个普通索引idx_key1_key2(key1,key2)。
    当我们通过SQL语句:select key2 from covering_index_sample where key1 = ‘keytest’;的时候,就可以通过覆盖索引查询,无需回表。
    在这里插入图片描述
  • MyISAM使用的是非聚集索引,因为MyISAM的索引文件和数据文件是分开的。所以,MyISAM中索引检索的算法为,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
    在这里插入图片描述

补充知识点

== Index Condition Pushdown(索引下推)==
MySQL 5.6引入了索引下推优化,默认开启,使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下:
people表中(zipcode,lastname,firstname)构成一个索引
SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;
如果没有使用索引下推技术,则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。
如果使用了索引下推技术,则MYSQL首先会返回符合zipcode='95054’的索引,然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。

设计索引时,应该如何考虑

  • 索引不在多,而在于合适。
  • 在记录较多条的表中,对经常用作where条件的字段设置索引(表的主键,外键会自动创建索引)
  • 假如多个条件经常同时出现,可以考虑多个字段建立联合索引,否则,建立多个单独索引。
  • 频繁进行写入操作的表,要注意不能建立太多索引。
  • 如果有多个字段,需要做联合索引,在做联合索引的时候,要把识别率最高的字段放在最左边,因为MySQL索引查询会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。
  • 索引字段,尽量避免NULL,应该指定列为NOT
    NULL,除非你就想存储NULL。在MySQL中含有空值的列很难进行查询优化。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值,这是设计表的时候要考虑的问题。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值