【实战】表设计与优化A

优化查询效率

1.创建索引

对一些经常查询的列，创建索引，或者使用组合索引替代多个索引。

当然，在创建索引的时候，也有一些注意：

• 尽量使用离散度比较大的列作为索引列

尽量避免索引失效

比如：不要对索引类使用函数，或者尽量的遵守最左比配原则

2.分库分表

对一些数据量特别大的情况，可以进行分开分表，将单个大表转化成小表，从而达到优化查询的效果。

1.避免使用select *

2.小表驱动大表

3.用连接查询代替子查询

• 子查询

SELECT o.order_id, c.customer_name
FROM orders o
WHERE o.customer_id IN (SELECT customer_id FROM customers WHERE city = 'New York');

• 连接查询

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.city = 'New York';

性能对比

假设 orders 表有大量记录，而 customers 表相对较小。在这种情况下，连接查询通常会更快，因为它可以利用索引直接找到匹配的记录，而子查询可能需要多次扫描 orders 表。

虽然连接查询在很多情况下优于子查询，但具体选择哪种方式取决于具体的查询需求和数据结构。在某些情况下，子查询可能是更合适的选择，例如在需要进行存在性检查或聚合操作时。因此，建议在实际应用中根据具体情况选择最合适的查询方式，并通过 EXPLAIN 分析和性能测试来验证查询的效率。

4.读写分离

对于一些情况，比如数据一致性要求不是那么的高，可以进行数据的读写分离。

5.使用冗余字段

对于一些表可以创建多余的冗余字段，使用这些冗余字段替代多表查询。如此一来就实现了即使在单个表上进行查询，也能达到多表查询的一个效果。

4.批量操作

5.使用limit

6.用union all 代替 union

7. join的表不宜过多

优化例子A

加索引

现在有以下2个表和一个查询sql，请问该查询sql可以进行如何的优化:

create table customer(
C_CUSTKEY int not null,
C_NAME` varchar(25) NOT NULL,
C_ADDRESS varchar(40) not null,
C_nationkey int not null,
C_phone char(15) not null,
C_acctbal decimal(15，2) NOT NULL,
C_mktsegment char(20) not null,
C_comment varchar(12) not null,
primary key (C CUSTKEY)
)engine=InnoDB;

create table orders (
o_orderkey int not null,
o_custkey int not null、
O_ORDERSTATUS char(1) not null,
o_totalprice decimal(15，2) NOT NULL,
o_orderdate date NOT NULL,
O_orderpriority char(25) not null,
O_CLERK char(16) not null,
O_SHIPPRIORITY int not null,
C_COMMENT varchar(7) not null,
primary key ('c_orderkey')
)engine=InnoDB;

# 查询sql
select * from customer where C_CUSTKEY = (
	select max(o_custkey) from orders where o_orderdate < adddate('2022-12-20',interval '1' DAY)
);

# 优化 可以加上索引

create index idx1 on orders(o_orderdate,o_custkey);

隐式类型转换

下列sql初看可能没什么问题，但存在一个不容易发现的问题：隐式类型转换

explain 
select * from orders 
where o_orderdate > '2022-01-01'
and (o_orderpriority = 1 or o_shippriority=1)
order by o_orderdate desc
limit 20,10;

隐式转换：MySQL 在执行 SQL 语句时自动进行的数据类型转换。

在数据量大的情况下，会特别耗时，因此请注意。

O_orderpriority char(25) not null,
# 因为 O_orderpriority 是 char 类型

但sql中 却是 用 int进行判断，因此系统会多一步操作，将int 转为 char

若是想避免，使用正确的、匹配上的类型即可。

and (o_orderpriority = '1' or o_shippriority=1)