mysql中的索引

mysql 索引数据结构

B 树也称 B - 树，全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B + 树中的 B 是 Balanced （平衡）的意思。
目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树 & B + 树两者有何异同呢？
B 树的所有节点既存放键 (key) 也存放数据 (data)，而 B + 树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的；B + 树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B + 树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B + 树的范围查询，只需要对链表进行遍历即可。

综上，B + 树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

mysql索引类型

• 按照数据结构维度划分：

  BTree 索引：
  MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。

  哈希索引：
  类似键值对的形式，一次即可定位。

  RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

  全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

• 按照底层存储方式角度划分：

  聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
  非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引 (辅助索引) 就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。注：二级索引（Secondary Index）又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。唯一索引，普通索引，前缀索引等索引属于二级索引。

• 按照应用维度划分：

  主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
  普通索引：仅加速查询。
  唯一索引：加速查询 + 列值唯一（可以有 NULL）。
  覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
  联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

聚簇索引

优点：
查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B + 树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：
依赖于有序的数据：因为 B + 树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引

非聚簇索引 (Non-Clustered Index) 即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引 (辅助索引) 就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。
非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点：
更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的

缺点：
依赖于有序的数据: 跟聚簇索引一样，非聚簇索引也依赖于有序的数据
可能会二次查询 (回表): 这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

覆盖索引

索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引，我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键 + 列值。最终还是要 “回表”，也就是要通过主键再查找一次，这样就会比较慢。而覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

正确使用索引的一些建议

使用合适的字段（不为 NULL 的字段，被频繁查询的字段，被作为条件查询的字段，频繁需要排序的字段，被经常频繁用于连接的字段）
被频繁更新的字段应该慎重建立索引
限制每张表上的索引数量，建议单张表索引不超过 5 个（影响更新，插入速度）
尽可能的考虑建立联合索引而不是单列索引
注意避免冗余索引
避免索引失效
删除长期未使用的索引

mysql 索引失效的集中情况

字段类型不匹配
查询条件中包含 or
like 通配符 % 错误使用（like a% 走索引）
联合索引最左匹配原则
索引列使用 MySQL 函数或计算，索引失效
使用（!= 或者 < >，not in），导致索引失效
使用 is null， is not null，导致索引失效
左连接、右连接关联字段编码不一致，索引失效
order by 使用不当

mysql 使用建议

• 数据库和表的字符集统一使用 UTF8
• 所有表必须使用 InnoDB 存储引擎
• 所有表和字段都需要添加注释
• 尽量控制单表数据量的大小，建议控制在 500 万以内
• 谨慎使用 MySQL 分区表 （分区表在物理上表现为多个文件，在逻辑上表现为一个表；谨慎选择分区键，跨分区查询效率可能更低；建议采用物理分表的方式管理大数据。）
• 经常一起使用的列放到一个表中
• 禁止在数据库中存储文件（比如图片）这类大的二进制数据
• 尽可能把所有列定义为 NOT NULL
• 每个 InnoDB 表必须有个主键
• 避免数据类型的隐式转换
• 避免使用子查询，可以把子查询优化为 join 操作
• 避免使用 JOIN 关联太多的表
• 减少同数据库的交互次数
• 在明显不会有重复值时使用 UNION ALL 而不是 UNION
  UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
  UNION ALL 不会再对结果集进行去重操作