mysql（五）==》SQL优化

1.SQL优化的手段

（1）优化SQL查询所涉及到的表的索引
（2）改写SQL以达到更好的利用索引的目的

【1】索引优化

（1）索引的作用是什么？
告诉存储引擎如何快速的查找到所需要的数据。
例如：想看一本书某一章节的某一段，索引就是章节目录和小节目录；没有索引就只能顺序查找，从第一页到最后一页顺序遍历。
（2）Innodb支持的索引类型

1. Btree索引（mysql中最常见）
2. 自适应HASH索引（innodb引擎自动建立的，不需要开发人员手动管理）
3. 全文索引（mysql 5.7以后支持）：主要用于字符串类型的数据列上，对中文支持不是很好。
4. 空间索引（mysql 5.7以后支持）：使用在点线面之类的空间类型之上的

（3）Btree索引的特点

1. 以B+树的结构存储索引数据
2. Btree索引适用于全值匹配的查询：
   class_name=‘mysql’；
   class_name in (‘mysql’ , ‘postgreSQL’);
   可以看到使用in列表可以使用Btree索引查询。当查找in列表的值过多时，mysql才可能认为这种使用全表扫描的方式来进行处理数据会优于索引查找。
3. Btree 索引适合处理范围查找：
   study_cnt between 100 and 200;
   study_cnt>2000;
4. Btree索引从索引的最左侧列开始匹配查找列
   create index idx_title_studyCnt on imc_course(title,study_cnt);
   //建立了一个复合索引，看看以下选项哪个可以用到复合索引呢？
   A. study_cnt>300
   B. study_cnt>300 && title=‘mysql’
   C. title=‘Mysql’
   索引最左侧列是title列：而Btree索引从索引的最左侧列开始匹配查找列，所以A无法用到这种复合索引。B选项是可以使用复合索引的。C也可以使用复合索引。

（4）在什么列上建立索引？
where 子句中的列
包含在order by、group by、distinct中的字段。
多表join的关联列


可以看到驱动表是b表，由type列可以看出a表和c表是全表扫描，b表和d表为索引扫描，从rows列可以看出查出所需要的数据大概需要：1x4x100x1=400次查询，因为这是一个嵌套查询，rows就代表了嵌套查询的次数。
对a表建立索引复合索引

create index idx_classid_typeid_levelid on imc_course(class_id,type_id,level_id); 

再次执行时：


由type列发现a表变为索引扫描，possible_key为idx_classid_typeid_levelid，

由rows列：共需查询1x4x7x1=28次。可以看到SQL查询优化了

对c表的level_name建立二级索引（非主键索引）：

create index idx_levelname on imc_level(level_name);

再次执行时：


四张表都用到了索引扫描。且只需要执行1x1x7x1=7次就可以查询到。
（5）如何选择符合索引键的顺序？
把区分度最高的列放在联合索引的最左侧。
把使用最频繁的列放到联合索引的最左侧。
尽量把字段长度最小的列放在联合索引列的最左侧。

（6）Btree索引的限制？

1. 只能从最左侧开始按索引键的顺序使用索引，不能跳过索引建
   假设由A，B，C三列组成联合索引，若查询只包含了A，C两列，那么对这个查询来说只能用到索引中A列进行查询过滤，而无法用到C列。因为跳过了B列。
2. 使用not in和>,<,=等比较操作无法使用索引。
3. 索引列不能是使用表达式或是函数。

（7）索引使用的误区？

1. 索引越多越好（X）
   一方面，一定程度上索引会增加查询效率，但同样会降低插入和更新数据的效率；
   另一方面，也可能会降低查询效率。因为mysql优化器在选择如何优化查询时，会根据统计信息对每一个可以用到的索引进行评估，以生成最优的执行计划，而我们有很多索引用于查询时，增加mysql优化器生成执行计划的时间，也就降低sql的查询性能。
2. 使用in列表查询不能用到索引（X）
3. 查询过滤顺序必须与索引键顺序相同才可以使用到索引（X）
   mysql优化器优化了过滤顺序以适应索引键值顺序。

【2】SQL改写优化

（1）sql改写原则

1. 使用外关联代替not in

//查询出不存在课程的分类名称
select class_name
from imc_course
where class_id not in(select class_id from imc_course);

分析执行计划：


可以看到两个表都用到了索引。
改写为外关联方式：

执行计划之后发现和not in一模一样。因为mysql 8.0可以自动对not in进行优化为外关联查询，但如果我们使用mysql 5.5和5.6版本的话，就需要手动的对其进行优化。
2. 使用公共表表达式CTE代替子查询。
3. 拆分复杂的大SQL为多个简单的小SQL。
4. 巧用计算列优化查询。

//查询对于内容、逻辑和难度三项评分之后大于28分的用户评分。
select *
from imc_classvalue
where (content_score+level_score+logic_score > 28

执行计划如下：

可以看到该表是没有索引的。

建立索引也是用不到的，比如：

create index idx_content_level_logic_score on imc_classvalue(content_score,level_score,logic_score);

再次执行计划，发现并未发生变化，故在计算列上使用索引无效。

我们对imc_classvalue表增加一列：

alter table imc_classvalue
add column total_score decimal(3,1) 
as content_score+level_score+logic_score

修改完成后，创建索引：
create index idx_totalScore on imc_classvalue(total_score);
此时使用我们对total_score创建的索引idx_totalScore就可以查询了。