数据库索引优化

#使用库
USE test;

#创建表
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 (3,6,9);

#b语句强制使用索引
SELECT * FROM T1 FORCE INDEX(IX_ProcessID)
WHERE nextprocess=1
AND processid IN (3,6,9);

#创建表
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,模糊后面会使用索引"a%",
#模糊前面则会失效"%a"。
#创建表
CREATE TABLE IF NOT EXISTS T2(
    NAME VARCHAR(20)
);
#创建索引
CREATE INDEX ix_name ON T2(NAME);
#a语句不使用索引
SELECT * FROM T2 WHERE NAME LIKE "%a%";
#SUBSTING单词打错了，不知道是什么
#name 字段名，2 起始值(下标)，1 查询数(长度)。
SELECT * FROM T2 WHERE SUBSTING(NAME,2,1)="a";
#b语句会使用索引
SELECT * FROM WHERE NAME LIKE "a%";