Mysql学习笔记——索引

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=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。

相关问题

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

对于查询，普通索引和唯一索引几乎没有性能差异。
对于插入，在写多读少的情况下，普通索引的性能优于唯一索引，这跟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值会增加数据储存的大小吗？

上图是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值不一定会增加数据存储的大小，需要具体分析。