zhenlanghuo's Blog

Mysql学习笔记——索引

2019/10/04

数据结构

哈希表

检索复杂度为O(1),但是范围查询、排序等功能,只适用于等值查询的场景。

B树 和 B+树

B树和B+树的检索复杂度为O(log n),支持范围查询和排序。

B树和B+树的区别在于,B树在非叶子节点存储数据;而B+树在叶子节点按索引顺序存储数据,非叶子节点存储的都是索引值,在范围查询时能利用磁盘顺序读(叶子节点按索引顺序存储数据)来提高性能。

索引类型

根据叶子节点的内容,分为主键索引和非主键索引:

  • 主键索引叶子节点存的是整行数据;在InnoDB里,主键索引也被称为聚簇索引(clustered index)
  • 非主键索引叶子节点内容是主键的值。在InnnoDB里,非主键索引也被称为二级索引(secondary index)

回表: 语句select * from T where k=5中的k是普通索引(非主键索引),需要先搜索k索引树,得到主键的值,在到主键索引树搜索一次。这个过程称为回表

也就是说,基于非主键索引的查询需要多扫描一颗索引树。因此,我们在应用中应该尽量使用主键查询。

  • 覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据
  • 联合索引:将多个字段组合定义为一个索引。根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。

相关知识点

  • 最左匹配原则

    • 对于非联合字符串索引,like “xxxx%”能够使用索引,like “%xxxx”则不能够使用索引。
    • 参考上述的联合索引。
  • 索引下推
    Mysql5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数

相关问题

普通索引和唯一索引,应该怎么选择

对于查询,普通索引和唯一索引几乎没有性能差异。
对于插入,在写多读少的情况下,普通索引的性能优于唯一索引,这跟innodb中的change buffer有关系。

当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InooDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。

将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。

插入一个包含唯一索引的记录,如果这个记录要更新的目标不在内存中,由于唯一索引的性质,需要将数据页读入内存,判断是否有冲突;而插入一个包含普通索引的记录,如果这个记录要更新的目标不在内存中,只需要将更新记录在change buffer就行了。

因此对于写多读少且不需要写后马上读的业务场景(比如:账单类,日志类),普通索引+chang buffer能对性能有较大的提升。

MySQL实战45讲:普通索引和唯一索引,应该怎么选择?

怎么给字符串字段加索引

  • 直接创建完整索引,这样可能比较占用空间;
  • 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
  • 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
  • 创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。

MySQL实战45讲:怎么给字符串字段加索引?

Mysql选错索引了怎么办

  • 对于由于索引统计信息不准确导致的问题,可以用analyze table来解决
  • 对于其他优化器误判的情况,可以在应用端用force index,或通过修改语句来引导优化器,再或者通过增加或删除索引来解决。

MySQL实战45讲:MySQL为什么有时候会选错索引?

NULL值对数据大小和索引的影响

  • 使用is null、is not null会使用索引吗?

    使用is null能够走索引,因为innodb把null值索引放在B+树的最左边,并且mysql有针对is null做优化。但是在检索中是否真的会走索引,还要看优化器根据统计数据所做出的判断。
    is not null能否走索引暂时抱有怀疑态度。

  • null值会增加数据储存的大小吗?

    image_1dv5eel8jksa1jn23k51l0g1iab9.png-59.5kB

    上图是innodb中保存一行数据的格式,NULL值列表就是专门用于记录哪一列的值是null值的。

    innodb规定将允许存储null值的列按逆序排列,每一列用以为二进制位表示,0代表该列的值不为null,1代表该列的值为null。

    另外innod还规定NULL值列表必须是字节的整数倍,也就是说,允许存储null值的列大于1,少于等于8时,NULL值列表大小为1个字节;允许储存null值的列大于8少于等于16时,NULL值列表大小为2个字节;如果没有允许存储null值的列,NULL值列表不存在,也就是大小为0。

    另外null值的列不会占有后面记录真实数据部分的任何空间,即NULL除了占有NULL标志位,实际存储不占有任何空间。

    结论:表中有允许存储null值的列的话,需要有NULL值列表进行记录,但是NULL值列表一般只占1到2个字节,并且NULL除了占有NULL标志位,实际存储不占有任何空间,因此null值不一定会增加数据存储的大小,需要具体分析。

备注:以上结论对于较低版本的mysql可能不适用。
IS NULL Optimization
MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!
MySQL技术内幕:InnoDB存储引擎 第2版 第四章 表 4.3 innodb行记录格式 compact行记录格式

CATALOG
  1. 1. 数据结构
    1. 1.1. 哈希表
    2. 1.2. B树 和 B+树
  2. 2. 索引类型
  3. 3. 相关知识点
  4. 4. 相关问题
    1. 4.1. 普通索引和唯一索引,应该怎么选择
    2. 4.2. 怎么给字符串字段加索引
    3. 4.3. Mysql选错索引了怎么办
    4. 4.4. NULL值对数据大小和索引的影响