MySQL 中的索引

tytler

已于 2022-11-08 19:08:23 修改

阅读量9.4k

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

于 2022-08-04 15:17:10 首次发布

本文链接：https://blog.csdn.net/qq_41956014/article/details/126141018

版权

9 篇文章 0 订阅

订阅专栏

数据结构维度
- B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询
- 哈希索引: 适合等值查询，检索效率高，一次到位
- 全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建
- R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

物理存储维度
- 聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据
- 非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列

MyISAM 非聚族索引，B+Tree 作为索引结构；InnoDB 聚族索引，B+Tree 作为索引结构
聚集索引与非聚集索引的区别
- 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
- 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引
聚簇索引(左图)：
- 一级索引：表数据和主键一起存储的，一级索引的叶结点存储行数据(包含了主键值)
- 二级索引：叶节点存储行的主键值，使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，非叶子节点中的关键字中不存储对应记录的具体内容或内容地址，叶子节点上的数据是主键与具体记录(数据内容)，就是主键和执行一级索引的内容
非聚簇索(右图)：
- 表数据和索引是分成两部分存储的，一级索引和二级索引存储上没有任何区别，使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的数据

join
- 外层循环-驱动表，内层循环-被驱动表，小表驱动大表，外层循环扫描次数越少，性能就越好
- join 字段类型要保持一致，是不同数据类型时，会进行全表扫描，join 字段可以加索引
- 连表不超过3张，如果业务需要超过，可以进行分开查询，在 service 层业务中处理数据(阿里规范手册)
limit
- offset 数值比较大的时候，查询的性能相对较差，原因是先扫描前面的数据丢弃之后，在去符合条件的分页数据页码数禁止传入过大的页码，业务妥协
- 覆盖索引 + 子查询 (利用索引去查询分页)
  ex: select * from employees where emp_no >= (select emp_no from employees limit 300000,1) limit 10;
count
- count(*) 和 count(1) 按照官方的说法，没有区别
- count(*) 会选择最小的非主键索引，如果不存在非主键索引，则会使用主键索引
- count(*) 不会排除为 null 的行，而 count(字段) 会排除，没有特殊需求尽量用 count(*)
order by
- 利用索引避免排序，索引本身就具有排序性
- 无法利用索引，优化 file sort
group by
- 参考地址： http://www.weijingbiji.com/1939/