sql优化查询

最新推荐文章于 2026-06-20 20:55:32 发布

原创最新推荐文章于 2026-06-20 20:55:32 发布 · 180 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql

收录于

本文探讨了如何在SQL中创建和利用索引以提升查询性能，包括针对不同场景的优化建议，如避免全表扫描、使用覆盖索引和模糊查询优化。通过实例解析，揭示了索引在提高查询效率中的关键作用。

#创建索引
CREATE INDEX IX ProcessID ON T1(processid);
#a语句不使用索引
SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45);
#b语句强制使用索引
SELECT * FROM T1 FROM INDEX(IX_ProcessID)
WHERE nextprocess = 1 AND processid IN (8,32,45);

CREATE TABLE IF NOT EXISTS orders(customer_num INT,order_num INT);
CREATE INDEX i_order_num ON orders(order_num);
#消除顺序读取，使用索引
#a语句不适用索引
SELECT * FROM orders WHERE (customer_num = 104 AND order_num>1001) OR order_num=1008;
#b语句拆分，使用索引
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008;

#模糊查询where like，字母大头'1%'会使用索引，非字母大头'%1%'不会使用索引
CREATE TABLE IF NOT EXISTS T1(NAME VARCHAR(20));
CREATE INDEX ix_name ON T1(NAME);
#a语句不会使用索引
SELECT * FROM T1 WHERE NAME LIKE '%L%';
SELECT * FROM T1 WHERE SUBSTINF(NAME,2,1)='L';
#b语句会使用索引
SELECT * FROM T1 WHERE NAME LIKE 'L%';