## 场景

### 课程表

``````create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)``````

### 学生表

``````create table Student(
id int PRIMARY KEY,
name varchar(10)
)``````

### 学生成绩表

``````CREATE table SC(
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)``````

### 查询语句：

``````select s.* from Student s
where s.s_id in (
select s_id
from SC sc
where sc.c_id = 0 and sc.score = 100 )``````

``````EXPLAIN
select s.* from Student s
where s.s_id in (
select s_id
from SC sc
where sc.c_id = 0 and sc.score = 100 )``````

``````CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);``````

``````SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
WHERE
< in_optimizer > (
`YSB`.`s`.`s_id` ,< EXISTS > (
SELECT
1
FROM
`YSB`.`SC` `sc`
WHERE
(
(`YSB`.`sc`.`c_id` = 0)
AND (`YSB`.`sc`.`score` = 100)
AND (
< CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
)
)
)
)
```````

``````select s_id
from SC sc
where sc.c_id = 0 and sc.score = 100``````

``````select s.*
from Student s
where s.s_id in(7,29,5000)``````

mysql是先执行外层查询，再执行里层的查询，这样就要循环70007*8次。

``````SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100``````

``````CREATE index sc_s_id_index on SC(s_id);
show index from SC``````

``````SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
JOIN `YSB`.`SC` `sc`
WHERE
(
(
`YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
)
AND (`YSB`.`sc`.`score` = 100)
AND (`YSB`.`sc`.`c_id` = 0)
)``````

``````SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id``````

``````CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);``````

``````SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id``````

``````SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100``````

2015-04-30日补充：最近又重新导入一些生产数据，经测试发现，前几天优化完的sql执行效率又变低了。

show index from SC

``````SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=81 and sc.score=84``````

``````alter table SC drop index sc_c_id_index;
alter table SC drop index sc_score_index;
create index sc_c_id_score_index on SC(c_id,score);``````

## 总结

1. mysql嵌套子查询效率确实比较低
2. 可以将其优化成连接查询
3. 连接表时，可以先用where条件对表进行过滤，然后做表连接 （虽然mysql会对连表语句做优化）
4. 建立合适的索引，必要时建立多列联合索引
5. 学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要

## 单列索引

``select * from user_test_copy where sex = 2 and type = 2 and age = 10``

``````CREATE index user_test_index_sex on user_test_copy(sex);
CREATE index user_test_index_type on user_test_copy(type);
CREATE index user_test_index_age on user_test_copy(age);``````

## 多列索引

``create index user_test_index_sex_type_age on user_test(sex,type,age);``

``select * from user_test where sex = 2 and type = 2 and age = 10``

### 最左前缀

``````select * from user_test where sex = 2
select * from user_test where sex = 2 and type = 2
select * from user_test where sex = 2 and age = 10``````

## 索引覆盖

``select sex,type,age from user_test where sex = 2 and type = 2 and age = 10``

## 排序

``select * from user_test where sex = 2 and type = 2 ORDER BY user_name``

``create index user_name_index on user_test(user_name)``

1. 列类型尽量定义成数值类型，且长度尽可能短，如主键和外键，类型字段等等
2. 建立单列索引
3. 根据需要建立多列联合索引 当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低， 那么如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。
4. 根据业务场景建立覆盖索引 只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率
5. 多表连接的字段上需要建立索引 这样可以极大的提高表连接的效率
6. where条件字段上需要建立索引
7. 排序字段上需要建立索引
8. 分组字段上需要建立索引
9. Where条件上不要使用运算函数，以免索引失效