mysql-索引

以下分析都是基于 mysql innodb引擎

https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

clustered index(聚簇索引) : https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_clustered_index

secondary index(二级索引):https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_secondary_index

PS: 聚簇索引包含主键索引/unique/mysql虚拟主键索引;  主键索引是聚簇索引,但聚簇索引不一定是主键索引

 

对于mysql 中存在的索引包含 聚簇索引和二级索引(非主键索引);

• 聚簇索引 : 主键索引是mysql server 自动维护一个 聚簇索引,其使用了 b+ tree 数据结构进行数据存储, key/value 的数据结构,对于 key 实际就是 主键(primary key) ,对于 叶子节点上存储的value 实际 就是 主键对应 的行数据(row data); 当执行相关insert/update/delete 操作时,会自动维护当前索引;
  • 每个 mysql innodb 引擎都会维护 一个(仅且只有一个) 聚簇索引
  • 对于聚簇索引 使用 的列(column),默认查找顺序 实际是按照 primary key > unique key > gen_clust_index(mysql 自动维护的一个自增的虚拟主键)
  • 假设在 表中 同时 存在 primary key 和 unique key ,个人大胆猜测对于 unique key 实际 是属于 二级索引,而不是聚簇索引
  • 关于 primary key 虽然其 同样支持 设置 多个 column共同组成 主键索引,虽然其仍然会作为聚簇索引存在, 但由于在多字段情况下,索引命中规则问题,可能会导致无法使用到聚簇索引,降低查询效率
• 二级索引 : 对于 二级索引 实际就是 普通的index 和 unique key, 对于 二级索引 和 聚簇索引的区别点就在于 叶子节点中存储的value 为 当前索引关联的主键集合,且mysql 对 二级索引存储主键做了一步优化操作为将所有所有叶子节点存储的值都会使用链表结构进行连接,因此其支持range 操作

 

关于索引数据存储

由于b+ tree 的数据结构特点,其实际会存储在内存和磁盘中, 由于内存容量限制,不可能将所有的索引都存储到内存中, 因此mysql 对于索引的存储进行了优化,对于一定高度的b tree索引其会直接存储到内存中,提高了索引整体查询效率;

页存储:https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

由于mysql innodb 数据存储都是使用page存储,因此在按页查找过程中,当索引数据过大情况下,其数据就需要存储到磁盘中,而由于磁盘的查询效率过低,因此应尽量避免索引结构中非叶子节点的整体高度

mysql innodb 会存储一部分非叶子节点存储到内存中,当树的高度在尽量低的情况下可以减少磁盘查询次数

 

关于索引最左(leftmost)前缀(prefix)原则

 

https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

关于 最左前缀原则

If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but do not use an index to perform lookups because (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

由于所有的索引类型都支持多字段,当设置多字段时,实际会按照索引从左到右的顺序依次分离成多个索引匹配,  例如 文档中定义的 (col1, col2, col3) ,其实际还有隐含的索引结构 (col1) , (col1,col2);

因此当我们使用 select * from table_name where col1 = '';  select * from table_name where col1 = '' and col2 = ''; 对于当前两条查询语句 实际 都会命中到 (col1,col2,col3)该索引, 因此其 命中了 隐藏的两个索引 (col1),(col1,col2);

但对于 select * from table_name where col2 = '' ; select * from table_name where col2 = '' and col3 = '' ; 根据最左匹配原则其无法匹配到 (col1,col2,col3) 当前索引; 

对于 select * from table_name where col1 = '' and `col3` = '' ; 其会命中 隐藏索引 (col1) , 但这个并不是绝对的, 由于 mysql server 查询优化的存在, 在真正执行时有可能并不一定会根据索引进行查找

PS : where 中 字段的编写顺序并不会影响到最左前缀匹配原则,mysql server 会优化查询;

对于索引最左前缀匹配原则(leftmost prefix),实际就是保证查询条件尽可能覆盖多字段索引(包含隐藏索引)

 

关于二级索引(secondary index)查询优化点

对于二级索引中对于 非叶子节点保存的 是组成当前索引的全部字段数据,在叶子节点上保存的是当前索引字段关联的主键;

覆盖索引 : https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_covering_index

全表扫描 : https://dev.mysql.com/doc/refman/5.7/en/table-scan-avoidance.html

• 查询语句中的结果尽量覆盖索引字段, 对于索引字段包含 二级索引中的字段 以及 主键, 当查询的结果不存在于 索引树中时, 此时就需要根据关联的主键去查询 聚簇索引(clustered index),对于当前操作称为 回表

 

关于全表扫描 ,由于全表扫描效率低下,其需要根据查询条件去匹配遍历到的每一行数据,对于正常匹配的结果保留,对于无法匹配的行就会被抛弃