数据库之索引优化

#使用库
USE test;
#创建表
CREATE TABLE IF NOT EXISTS t(
    id INT,
    #索引字段尽量设置成不为空，null可以用其他符号代替
    #int类型的话可以用0，-1等代替
    #String类型的话可以用“”代替    
    #设置默认值为0
    num INT DEFAULT 0
);
#创建索引
CREATE INDEX ix_num ON t(num);
#未使用索引
SELECT id FROM t WHERE num IS NULL;
#使用索引
SELECT id FROM t WHERE num=0

#未使用索引
SELECT * FROM t WHERE num != 5;

#避免使用or
#索引不能同时查询两个，索引失效
SELECT * FROM t WHERE num=10 OR num=20;
#这里改为分开查询索引就可以使用了
#使用索引
SELECT * FROM t WHERE num=10
#关联
UNION
SELECT * FROM t WHERE num=20;

#尽量少用in,not in,改用between
#未使用索引
SELECT * FROM t WHERE num IN(8,5,10);
#使用索引（BETWEEN:范围）
SELECT * FROM t WHERE num BETWEEN 1 AND 3;

#避免索引字段在where后面进行运算
SELECT * FROM t WHERE num/2=100;
SELECT * FROM t WHERE num=200;

#避免索引字段进行函数操作
#索引失效
#查询name字段中下标1-3内容为abc的
SELECT * FROM t WHERE SUBSTRING(NAME,1,3)="abc";
#使用索引
SELECT * FROM t WHERE NAME LIKE "abc";

#复合索引(多个字段共用同个索引)
#创建学生表
CREATE TABLE IF NOT EXISTS student(
    id INT,
    NAME VARCHAR(20),
    age INT,
    tel VARCHAR(15)
);
#创建索引
#可以同时使用,也可以单独查询前面字段但不能单独查询后面字段。
CREATE INDEX ix_id_name ON student(id,NAME);
#使用索引
SELECT * FROM student WHERE id=10;
#复合索引可以这样使用，单个索引则不行
SELECT * FROM student WHERE id=10 AND NAME="李四";