美高梅棋牌官网下载-美高梅棋牌官方版下载

大家可以在美高梅棋牌官网下载当中进行野外生存的游戏、也可以泡泡温泉、吃吃烧烤,美高梅棋牌官方版下载成为卓越不凡的超级娱乐企业巨头,注册可提款,通过该系统的运作,以最为合适的娱乐方式将客户传递到最佳平台。

多路搜索树B树、B+树,索引有一个

2019-11-25 13:18栏目:美高梅棋牌官网下载
TAG:

有一个业务是查询最新审核的5条数据

概述

SELECT `id`, `title`FROM `th_content`WHERE `audit_time` < 1541984478 AND `status` = 'ONLINE'ORDER BY `audit_time` DESC, `id` DESCLIMIT 5;

在数据库当中,索引就跟树的目录一样用来加快数据的查找速度,对于一个SQL查询操作,根据索引快速过滤掉不符合要求的数据并定位到符合要求的数据,从而不需要扫描整个表来获取所需的数据。

查看当时的监控情况 cpu 使用率是超过了100%,show processlist看到很多类似的查询都是处于create sort index的状态。

在innodb存储引擎中,主要是基于B+树来实现索引,在非叶子节点存放索引关键字,在叶子节点存放数据记录或者主键索引中的主键值,所有的数据记录都在同一层,叶子节点,即数据记录直接之间通过指针相连,构成一个双向链表,从而可以方便地遍历到所有的或者某一范围的数据记录。

CREATE TABLE `th_content`  unsigned NOT NULL AUTO_INCREMENT, `title` varchar CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '内容标题', `content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT '正文内容', `audit_time` int unsigned NOT NULL DEFAULT '0' COMMENT '审核时间', `last_edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近编辑时间', `status` enum('CREATED','CHECKING','IGNORED','ONLINE','OFFLINE') CHARACTER SET utf8 NOT NULL DEFAULT 'CREATED' COMMENT '资讯状态', PRIMARY KEY , KEY `idx_at_let` (`audit_time`,`last_edit_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

B树和B+树都是多路平衡搜索树,通过在每个节点存放更多的关键字和通过旋转、分裂操作来保持树的平衡来降低树的高度,从而减少数据检索的磁盘访问量。

索引有一个audit_time在左边的联合索引,没有关于status的索引。

B+树相对于B树的一个主要的不同点是B+的叶子节点通过指针前后相连,具体为通过双向链表来前后相连,所以非常适合执行范围查找。具体可以参考:

分析上面的sql执行的逻辑:

数据结构-树:多路搜索树B树、B+树

从联合索引里找到所有小于该审核时间的主键id(假如在该时间戳之前已经审核了100万条数据,则会在联合索引里取出对应的100万条数据的主键 id) 未来如果有一个优化就好了,目前还有:对100个主键 id 排序,然后在下面一步回表操作中挨得近的主键可能一次磁盘 I/O 就都取到了 逐个回表,查出100万行记录,筛选出status='ONLINE'的行记录 最后对查询的结果进行排序(假如有50万行都是ONLINE,则继续对这50万行进行排序)

innodb存储引擎的聚簇和非聚簇索引都是基于B+树实现的。主键索引

最后因为数据量很大,虽然只取5行,但是按照我们刚刚举的极端例子,实际查询了100万行数据,而且最后还在内存中进行了50万行数据库的内存排序。

innodb存储引擎使用主键索引作为表的聚簇索引,聚簇索引的特点是非叶子节点存放主键作为查找关键字,叶子节点存放实际的数据记录本身,从左到右以关键字的顺序,存放数据记录,故聚簇索引其实就是数据存放的方式,所以每个表只能存在一个聚簇索引,innodb存储引擎的数据表也称为索引组织表。结构如下:(图片引自《MySQL技术内幕:Innodb存储引擎》)

画了一个示意图,说明第一步的查询过程,粉红色部分表示最后需要回表查询的数据行。

在查询当中,如果是通过主键来查找数据,即使用explain分析SQL的key显示PRIMARY时,查找效率是最高的,因为叶子节点存放的就是数据记录本身,所有可以直接返回,而不需要像非聚簇索引一样需要通过额外回表查询获取数据记录。

图中我按照索引存储规律来YY伪造填充了一些数据,如有不对请留言指出。希望通过这张图大家能够看到联合索引存储的方式和索引查询的方式

其次是对于ORDER BY排序操作,不管是正序ASC还是逆序DESC,如果ORDER BY的列是主键,则由于主键索引对应的B+树本身是有序的, 故存储引擎返回的数据就是已经根据主键有序的,不需要在MySQL服务器层再进行排序,提高了性能,如果通过explain分析SQL时,extra显示Using filesort,则说明需要在MySQL服务器层进行排序,此时可能需要使用临时表或者外部文件排序,这种情况一般需要想办法优化。

改进思路 1

对于基于主键的范围查找,由于聚簇索引的叶子节点已经根据主键的顺序,使用双向链表进行了相连,故可以快速找到某一范围的数据记录。

范围查找向来不太好使用好索引的,如果我们增加一个audit_time, status的联合索引,会有哪些改进呢?

辅助索引

ALTER TABLE `th_content` ADD INDEX `idx_audit_status` (`audit_time`, `status`);

mysql> explain select `id`, `title` from `th_content` where `audit_time` < 1541984478 and `status` = 'ONLINE' order by `audit_time` desc, `id` desc limit 5;+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+| 1 | SIMPLE | th_content | range | idx_at_ft_pt_let,idx_audit_status | idx_audit_status | 4 | NULL | 209754 | Using where |+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+

辅助索引也称为二级索引,是一种非聚簇索引,一般是为了提高某些查询的效率而设计的,即使用该索引列查询时,通过辅助索引来避免全表扫描。由于辅助索引不是聚簇索引,每个表可以存在多个辅助索引,结构如下:

细节:因为audit_time是一个范围查找,所以第二列的索引用不上了,只能用到audit_time,所以key_len美高梅棋牌官方版下载,是4。而下面思路2中,还是这两个字段key_len则是5。

辅助索引的非叶子节存放索引列的关键字,叶子节点存放对应聚簇索引的主键值。即通过辅助索引定位到需要的数据后,如果不能通过索引覆盖所需列,即通过该辅助索引列来获取该次查询所需的所有数据列,则需要通过该对应聚簇索引的主键值定位到在聚簇索引中的主键,然后再通过该主键值在聚簇索引中找到对应的叶子页,从而获取到对应的数据记录,所以整个过程涉及到先在辅助索引中查找,再在聚簇索引两个过程。

还是分析下在添加了该索引之后的执行过程:

辅助索引对应的B+树的高度为3,则需要3次磁盘IO来定位到叶子节点,其中叶子节点包含对应聚簇索引的某个主键值; 然后通过叶子节点的对应聚簇索引的主键值,在聚簇索引中找到对应的数据记录,即如果聚簇索引对应的B+树高度也是3,则也需要3次磁盘IO来定位到聚簇索引的叶子页,从而在该叶子页中获取实际的数据记录。

从联合索引里找到小于该审核时间的audit_time最大的一行的联合索引 然后依次往下找,因为< audit_time是一个范围查找,而第二列索引的值是分散的。所以需要依次往前查找,匹配出满足条件(status='ONLINE')的索引行,直到取到第5行为止。 回表查询需要的具体数据

以上过程总共需要进行6次磁盘IO。故如果需要回表查询的数据行较多,则所需的磁盘IO将会成倍增加,查询性能会下降。所以需要在过滤程度高,即重复数据少的列来建立辅助索引。

在上面的示意图中,粉红色标识满足第一列索引要求的行,依次向前查询,本个叶子节点上筛选到了3条记录,然后需要继续向左,到前一个叶子节点继续查询。直到找到5条满足记录的行,最后回表。

Cardinality:索引列的数据重复度

改进之处

由以上分析可知,通过辅助索引进行查询时,如果需要回表查询并且查询的数据行较多时,需要大量的磁盘IO来获取数据,故这种索引不但没有提供查询性能,反而会降低查询性能,并且MySQL优化器在需要返回较多数据行时,也会放弃使用该索引,直接进行全表扫描。所以辅助索引所选择的列需要是重复度低的列,即一般查询后只需要返回一两行数据。如果该列存在太多的重复值,则需要考虑放弃在该列建立辅助索引。

版权声明:本文由美高梅棋牌官网下载发布于美高梅棋牌官网下载,转载请注明出处:多路搜索树B树、B+树,索引有一个