1:创建普通索引
对你sql里面设计到的属性进行创建索引
创建索引的语句:
CREATE INDEX idx_orders_user_id ON orders (user_id);
idx_orders_user_id 是索引名字,orders是表名,user_id是属性名
创建索引也有一些潜在的缺点,包括:
-
增加写操作的开销:因为每次插入、更新或删除表中的数据时,索引也需要相应地进行更新,这可能会降低写操作的性能。
-
占用磁盘空间:索引本身会占用额外的磁盘空间。
-
创建和维护成本:创建索引需要时间,并且在数据库中维护索引也会带来一定的计算成本。
个人解析:创建索引后,数据库中的索引类似于一个有序的数据结构(通常是B树),它记录了建立的索引。当执行查询操作时,数据库引擎会检查是否有可用的索引,并利用这些索引快速定位数据。B树索引允许数据库引擎从根节点开始,根据索引的键值逐层下降到叶节点,最终快速找到所需数据。
使用索引的主要优势是减少了查询过程中需要检查的数据量。如果没有索引,数据库可能需要执行全表扫描,这在大型表中会非常慢。然而,MySQL中的回表机制(即查询需要的列不在索引中时,需要回到主表中去检索这些列)有时可能减少索引带来的性能提升
2:创建覆盖索引
创建覆盖索引(也称为包含索引或索引视图)是一种特殊类型的索引,它允许查询直接从索引中获取所需的所有数据,而不需要回到原始表中去检索额外的列。这可以提高查询性能,因为索引通常比表更紧凑,且访问速度更快。
CREATE INDEX idx_covering ON orders (user_id, total_amount);
在这个例子中,idx_covering
是索引的名字,orders
是表名。此索引包含了 user_id
、order_date
和 total_amount
三个列。如果查询只需要这些列的数据,那么它可以直接从索引中获取,而不需要进行回表操作。
比你使用下面的sql时候
SELECT a.*,sum(b.total_amount) as total from users a left join orders b on a.user_id = b.user_id
group by a.user_id;
当您执行操作时,数据库引擎会找到你建立的索引 idx_covering ,此个索引包含了你所需要的全部的条件, 因此不会执行回表操作,重新去找主键索引表重复全表查询。