mysql 索引简介

一、索引类型

        1.普通索引：字段值不唯一，写数据前不需要验证是否唯一，读取时找到第一个满足条件的记录后还会继续查找，直到碰到第一个不满足条件的记录，普通索引可以利用change buffer提升性能。

        2.唯一索引：字段值唯一，写数据前要判断表中是否存在此值，读取时找到第一个满足条件的记录后直接返回。

        3.主键索引：主键所在字段创建的索引，所在字段值不允许重复或为空。

        4.全文索引：分词检索。

        5.联合索引：多个字段建立一个索引,遵循最左匹配原则。

        6.前缀索引：截取字段开头一部分建立索引，适用于邮箱等字符串尾部区分度不大的字段。

二、索引实现

        1.哈希表：适用于等值查询，类似于java的hashmap，innodb不支持此索引。

        2.b+树：每个索引对应一个b+树，b+树非叶子节点不存储数据，当索引类型为主键索引时叶子节点存储整行数据，为普通索引时存储主键id

三、索引相关概念

        1.回表：由于b+树非主键索引存储的是主键id，查询时获取到主键id后需要再去主键索引树查询行记录数据，去主键索引树查找的过程叫回表。

        2.索引覆盖：查询需要返回的字段都在索引树上，不需要回表查询，这种情况称为索引覆盖。对于查询频率较高，并且查询字段较少的字段可使用索引覆盖优化。

        3.索引下推：mysql内部优化，5.6以前对于使用联合索引的查询语句，满足第一个索引数据的所有字段返回到server层后回表查询过滤，5.6之后可对联合索引的全部字段在存储引擎中进行过滤，减少回表次数与server层数据返回。

        4.最左前缀：对于联合索引必须用到索引第一个字段，对于字段模糊查询 like “x%“可以走索引。

四、innodb索引长度计算

        1.定长字段smallint等占用1字节，int，timestamp等占用4字节，bigint，datetime等占用8字节。

        2.变成字段varchar（n）为n*编码格式字节数+两个字节。

        3.没设置not null的字段要在原长度加1字节。

        4.utf-8编码一个字符3个字节，gbk一个字符2字节。

        5.单个索引最大长度768字节，联合索引最大长度3072字节。当超过最大长度时，截取未超过部分做前缀索引。

五、导致索引失效的sql

        1.普通索引where 条件中存在or，in，not in，<>，!=，is null，is not null

        2.不遵循最左匹配原则如like ‘%xx’或联合索引没使用第一个索引作为查询条件。

        3.等号左侧进行函数运算例如DATE_FORMAT(create_time,'%m-%d-%Y') = xxx

        4.字段类型转换，当字符串与数字比较时会将字符串转为数字，例如字段name为varchar类型，where name=1 不能使用索引。当把name改为int类型时，where name=‘1’可以使用索引。

        5.不同表字符编码不同时进行关联查询时无法使用索引。