select *

xyy123
166 阅读5分钟

题目

现在运营想要查看用户信息表中所有的数据，请你取出相应结果

示例：user_profile

iddevice_idgenderageuniversityprovince
12138male21北京大学Beijing
23214male复旦大学Shanghai
36543female20北京大学Beijing
42315female23浙江大学ZheJiang
55432male25山东大学

根据示例，你的查询应返回以下结果:

iddevice_idgenderageuniversityprovince
12138male21北京大学Beijing
23214male复旦大学Shanghai
36543female20北京大学Beijing
42315female23浙江大学Zhejiang
55432male25山东大学Shandong

解题

很简单一题，首先想到的是select * image.png

优化后查询的时间和内存都有减少

image.png

原理

两者运行时间相似，是因为数据量小，但是占用空间前一个6624  后一个6528，所以*号更加占用内存空间，主要原因是mysql

1：长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作。（MySQL InnoDB）

2：防止索引覆盖和回文回表

索引覆盖

概念

索引覆盖是指查询可以直接利用索引中存储的数据来满足查询的需求，而不需要再通过访问数据或者磁盘来获取额外的信息。索引覆盖可以提高查询的性能，减少IO操作，并减少了额外的资源消耗。

当一个查询可以直接从索引中获取所需的数据列时，数据库可以避免从磁盘中读取数据块。相反，它只需要读取索引的数据页并返回结果。这可以减少磁盘IO的次数，提高查询速度。

为了实现索引覆盖，可以创建一个包含查询需要的列的复合索引。当查询使用复合索引时，数据库可以直接从索引中提取所需列的值，而不需要访问数据表。(复合索引是多个列的联合索引，它可以包含查询中使用的所有列。)

但是索引覆盖并不适合所有类型的查询。对于一些复杂的查询，可能需要访问数据表来执行计算或者其他操作。因此，需要根据具体的查询需求来判断是否适合使用索引覆盖。

demo

假设有一个名为"orders"的表，其中包含以下列：order_id、customer_id、order_date、total_amount。我们需要查询订单日期和订单总金额。

  1. 创建复合索引：
CREATE INDEX idx_orders ON orders(order_date, total_amount);

这个复合索引包含了订单的日期和总金额两列。

  1. 查询覆盖的例子：
SELECT order_date, total_amount FROM orders WHERE order_date >= '2022-01-01' AND order_date <= '2022-12-31';

这个查询只需要订单日期和总金额的数据，而不需要访问其他的列。因为我们已经在第一步创建了复合索引，数据库可以直接从索引中获取所需的数据，而不需要访问数据表。

这样，查询将会直接从索引中读取数据，并返回结果，而无需访问数据表。这就是索引覆盖的实现方式。

索引覆盖对于有许多重复值或者历史数据的表格可能并不适用。在实际应用中，需要根据具体的数据模型和查询需求来判断是否适合使用索引覆盖。

回文回表

概念

  1. 回文（Palindrome）： 在数据库查询优化中，回文通常指的是在查询执行的过程中，查询结果的行顺序与存储的物理顺序相同。具体来说，当查询访问的数据在存储中是按照查询条件的顺序排列时，就称为回文。回文的查询效率比较高，因为数据库可以连续地读取存储中的数据。
  2. 回表（Lookup or Index Lookups）： 回表指的是在使用索引的情况下完成查询时，数据库需要在索引节点上进行额外的查找（回到原始表）以获取完整的数据行。当查询条件包含索引列以及其他非索引列时，数据库可能需要通过回表操作来获取非索引列的数据。回表操作可能会增加查询的开销，尤其是在频繁进行回表操作的情况下。

回文是指查询结果的物理顺序与查询顺序一致，

而回表是指在使用索引时需要额外的查找操作。

优化查询性能时，我们通常希望尽可能地实现回文查询，避免频繁的回表操作，以提高查询效率。

demo

  1. 回文： 假设有一个名为"orders"的表，其中包含了订单信息，例如订单ID、客户ID和订单日期等。如果要查询特定客户的所有订单，并按照订单日期逆序排列，可以使用以下SQL语句：
SELECT * FROM orders WHERE customer_id = '123' ORDER BY order_date DESC;

如果我们为"customer_id"和"order_date"两列创建了索引，并且索引按照"customer_id"和"order_date"的顺序排列，那么查询的结果将是回文的。这意味着数据在存储中按照查询条件的顺序排列，因此数据库可以连续访问存储中的数据，从而提高查询效率。

  1. 回表： 现在假设我们要查询某个客户的所有订单，包括订单的商品信息等详细信息。如果我们使用以下SQL语句：
SELECT * 
    FROM orders o 
    JOIN order_items i 
        ON o.order_id = i.order_id 
    WHERE o.customer_id = '123';

在这个查询中，我们需要关联"orders"和"order_items"两个表，并且查询条件为"customer_id"，如果我们为"order_id"和"customer_id"这两列创建了索引，查询的结果将返回所有匹配的行，但是我们仍然需要通过回表操作来获取订单项的详细信息。

因为"order_items"表中的数据不包含在"orders"表，所以数据库需要在回到"orders"表来获取"order_id"列并在"order_items"表中查找匹配的数据行，这个过程就称为回表。在这种情况下，回表操作会增加查询的开销，通常会降低查询性能。

avatar
文章
阅读
粉丝