Hbase------刘飞

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 (8,32,45);
#b语句强制使用索引
SELECT * FROM T1 FORCE INDEX(IX_ProcessID)
WHERE nextprocess = 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 T2(NAME VARCHAR(20));
CREATE INDEX ix_name ON T2(NAME);
#a语句不会使用索引
SELECT * FROM T2 WHERE NAME LIKE '%L%';
#        从第二个字符开始截取，截取一个字符
SELECT * FROM T2 WHERE SUBSTNG(NAME,2,1)=' L';
#b语句会使用索引
SELECT * FROM T2 WHERE NAME LIKE 'L%';