索引优化与查询优化

3. 最佳左前缀法则

在联合索引中，优先使用哪个字段索引是有严格要求的。
在联合查询中，要想使用上索引，必须要先将最左侧的字段信息考虑到。

4.1 计算、函数、类型转换(自动或手动)导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

# 创建索引
CREATE INDEX idx_name ON student(NAME);
# 查看索引
show index from student

# 第一种：索引优化生效
 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
 
 SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
 
 SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
 
 type为“ALL”，表示没有使用到索引，查询时间为 3.62 秒，查询效率较之前低很多。

# student表的字段stuno上设置有索引

CREATE INDEX idx_sno ON student(stuno);

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;


# student表的字段name上设置有索引
CREATE INDEX idx_name ON student(NAME);

EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';

EXPLAIN SELECT id, stuno, NAME FROM student WHERE NAME LIKE 'abc%';


# 类型转换导致索引失效
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

name=123发生类型转换，索引失效。

4 范围条件右边的列索引失效

只能使用上两个索引 age和name
create index idx_age_classid_name on student(age,classid,name);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name =
'abc' AND student.classId>20 ;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30  AND student.classId>20 AND student.name =
'abc' ;

使用上了三个索引: age cid name
create index idx_age_name_classid on student(age,name,classid);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name =
'abc' AND student.classId>20 ;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30  AND student.classId>20 AND student.name =
'abc' ;

5.不等于(!= 或者<>)索引失效

6. is null可以使用索引，is not null无法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

7 like以通配符%开头索引失效

create index  idx_name on student(name)

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name like 'oj%' ;

进行全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name like '%oj' ;

8 OR 前后存在非索引的列，索引失效

# 不能使用索引
 create index idx_age on  student(age)
 
 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 22 OR name = 'acnhVj';
 

# 使用索引- 再次添加name索引字段信息
 create index idx_name on  student(name)

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 22 OR name = 'acnhVj';

关联查询

使用内连接：
有索引的为被驱动表
两个都有索引看结果集大小，小表驱动大表。