网站seo应用网络与智能媒体设计 干什么?

网站seo应用,网络与智能媒体设计 干什么?,wordpress搬到本地,jsp网站开发详解前言 在学习MySQL过程中#xff0c;阅读到这样一段话#xff1a;在 MySQL 中#xff0c;B 树索引按照存储方式的不同分为聚集索引和非聚集索引。我就在想为什么要分为这两种#xff0c;下面我就详细介绍这两者的联系、优缺点。 一、聚集索引和非聚集索引的本质 聚集索引…前言在学习MySQL过程中阅读到这样一段话在 MySQL 中B 树索引按照存储方式的不同分为聚集索引和非聚集索引。我就在想为什么要分为这两种下面我就详细介绍这两者的联系、优缺点。一、聚集索引和非聚集索引的本质聚集索引核心是“数据即索引索引即数据”nnoDB 中表的存储结构本身就是聚集索引的结构表的所有行数据都会按照聚集索引的键值默认是主键进行排序存储在聚集索引的叶子节点中。可以把它理解成一本按页码主键排序的书书的每一页叶子节点就是完整的内容整行数据。非聚集索引是“单独的索引结构指向数据的指针”非聚集索引有自己独立的索引树结构叶子节点中只存储「索引列的值 聚集索引的键值主键」并不存储整行数据。可以把它理解成这本书的目录目录里只记录章节名索引列和对应的页码主键不记录章节的具体内容。二、聚集索引和非聚集索引的联系二者是强绑定的InnoDB 的设计逻辑里非聚集索引完全依赖聚集索引非聚集索引的叶子节点必须存储聚集索引的键值InnoDB 的非聚集索引叶子节点不会直接存储数据的物理地址而是存储聚集索引的主键值 —— 这是为了保证数据的稳定性如果数据发生页分裂物理地址会变化但主键不会变非聚集索引的回表操作本质是依赖聚集索引当非聚集索引无法覆盖查询查询字段不在非聚集索引中时会先通过非聚集索引找到主键值再用主键值去聚集索引中查询完整数据这个过程就是「回表」二者的索引树结构逻辑一致不管是聚集还是非聚集索引都采用 B 树的结构非叶子节点存储索引键值用于导航叶子节点存储实际的内容聚集索引存数据非聚集索引存索引列 主键三、优缺点对比对比维度聚集索引非聚集索引叶子节点存储存储整行数据存储索引列的值聚集索引的主键值索引体积体积大等于表的实际数据体积体积小仅包含索引列和主键远小于表体积访问效率1. 主键查询/范围查询效率极高 2.非主键查询效率极低需要全表扫描1. 非主键查询匹配索引列效率极高 2.覆盖索引场景下效率优于聚集索引 3.非覆盖场景需回表效率略降排序特性天然按主键排序无需额外排序可按索引列排序避免ORDER BY触发的文件排序创建限制1. 一张表仅能创建1个聚集索引 2.默认以主键为聚集索引无主键则选唯一非空索引否则生成隐藏rowid1. 一张表可创建多个非聚集索引 2.支持基于任意字段/联合字段创建更新代价更新主键聚集索引键会导致整行数据移动代价极高更新索引列仅修改索引树节点代价低若索引列是主键会联动修改所有非聚集索引四、实战案例我们用一个电商的订单表order_info来举例表结构如下CREATETABLEorder_info(order_idBIGINTNOTNULLCOMMENT订单ID主键聚集索引,user_idBIGINTNOTNULLCOMMENT用户ID,order_timeDATETIMENOTNULLCOMMENT下单时间,order_amountDECIMAL(10,2)NOTNULLCOMMENT订单金额,order_statusTINYINTNOTNULLCOMMENT订单状态,PRIMARYKEY(order_id),-- 聚集索引KEYidx_user_time(user_id,order_time)-- 非聚集联合索引)ENGINEInnoDBDEFAULTCHARSETutf8mb4;场景 1适合用聚集索引的场景如果运营需要查询「订单 ID 为 100001 的订单的所有信息」SQL 如下SELECT*FROMorder_infoWHEREorder_id100001;此时用聚集索引order_id是最优选择直接通过主键定位到叶子节点的整行数据不需要任何额外操作效率最高场景 2适合用非聚集索引覆盖索引的场景如果运营需要查询「用户 ID 为 1001 的所有订单的下单时间和订单金额」SQL 如下SELECTorder_time,order_amountFROMorder_infoWHEREuser_id1001;此时我们创建的非聚集联合索引idx_user_time (user_id, order_time)加上 InnoDB 默认存储的主键order_id但我们的查询字段是order_time和order_amount—— 这里可以优化索引改成idx_user_time_amount (user_id, order_time, order_amount)优化后这个索引就覆盖了查询的所有字段MySQL 直接从非聚集索引中返回数据不需要回表效率远高于用聚集索引查询场景 3适合用非聚集索引需要回表的场景如果运营需要查询「用户 ID 为 1001 的所有订单的完整信息」SQL 如下SELECT*FROMorder_infoWHEREuser_id1001;此时非聚集索引idx_user_time只能提供user_id、order_time和order_id无法覆盖所有字段需要回表1.先通过idx_user_time找到所有user_id1001的行拿到对应的order_id2.再用order_id去聚集索引中查询整行数据但这个效率依然远高于用聚集索引全表扫描聚集索引只能按 order_id 排序无法快速找到 user_id1001 的行五、实战建议聚集索引的选择尽量用自增主键自增主键的插入是顺序的不会导致聚集索引的页分裂插入效率更高如果用 UUID 作为主键插入时会随机定位位置导致大量页分裂性能下降非聚集索引的设计优先考虑覆盖索引对于高频查询尽量创建包含查询所需所有字段的联合索引避免回表不要滥用非聚集索引非聚集索引会占用额外的磁盘空间同时会降低插入 / 更新 / 删除的效率因为需要维护索引树只给高频查询的字段创建索引