当前位置：首页 > article >正文

MySQL数据库-索引概念及其数据结构、覆盖索引与回表查询关联、超大分页解决思路

article 2024/11/15 23:58:18

索引是帮助mysql高效获取数据的数据结构,主要用来提高检索的效率,降低数据库的IO成本(输入输出成本（Input-Output Cost）),同时通过索引对数据进行排序也能降低数据排序的成本,降低了CPU的消耗。

Mysql的默认存储引擎InnoDB，InnoDB采用的B+树的数据结构来存储索引。B+树所有数据都出现在叶子节点，而相比较而言B树非叶子节点和叶子节点都存放数据，因此B+树内部节点相对B树更小，树的高度更小，查询速度更快。并且B+树使用双向链表串联所有叶子节点，这是B树没有的，便于扫库和区间范围查询。

建立索引的时候要避免回表查询。说道回表查询，要引入两个概念聚簇索引和非聚簇索引。聚簇索引主要指数据和索引放到了一块，B+树的叶子节点保存了整行数据，有且只有一个（一般情况下主键作为聚簇索引）。而非聚簇索引数据和索引是分开存储的，B+树叶子节点保存对应的主键，可以由多个（一般我们定义的普通索引都是非聚簇索引）。而回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据。这个过程就是回表。

在select查询语句中应避免select*，避免回表查询。覆盖索引可以减少回表查询的发生，提高查询效率。所谓的覆盖索引，就是指select查询语句中使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据性能高。如果按照二级索引查询数据的时候，返回的列中没有创建索引，可能会触发回表查询。举例说明：

select * from user where id =1 为覆盖索引，id查询，直接走聚集索引查询，一次查询就能查到结果

select id，name from user where name =“张三” 也为覆盖索引，虽然按照辅助索引查询数据，但在辅助索引可直接通过name直接查到name和id，不需回表查询聚集索引

select name，gender from user where name =“张三” 这个为非覆盖索引，主要因为gender需要通过name查询到id，在用id去查gender，也就是需要回表查询，所以查询语句避免使用select *，查询字段太多大概率会回表查询

对于超大分页来说可以通过覆盖索引加子查询来进行优化，举例来说

select * from user limit 100000,10就属于超大分页问题，该分页执行过程中需先排序100010记录，但仅仅返回后10条数据，其他数据丢弃，排序代价大，使用覆盖索引加子查询形式进行优化

select * from user u，（select id from user order by id limit 100000,10）a where u.id

=a.id