#创建表
CREATE TABLE IF NOT EXISTS T1(processid INT,nextprocess INT);
#创建索引
CREATE INDEX Ix_processID ON T1(processid);
#a语句不使用索引
SELECT * FROM T1 WHERE nextprocess =1 AND processid IN (8,32,45);
#b语句强制使用索引
SELECT * FROM T1 FORCE INDEX(IX_Processid)
WHERE NEXT PROCESS=1 AND processid IN (8,32,45);
CREATE TABLE IF NOT EXISTS orders(customer_num INT,order_num INT);
#消除顺序读取,使用索引
#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 SUBSTING(NAME,2,1)='L';
#b语句使用索引
SELECT * FROM T1 WHERE NAME LIKE 'L%';
sql索引优化
最新推荐文章于 2024-07-11 10:57:34 发布