MySql基础知识总结（SQL优化篇），阿里P7大牛亲自教你

4、联合索引，但与索引顺序不一致

备注：因为mysql优化器的缘故，与索引顺序不一致，也会触发索引，但实际项目中尽量顺序一致。

5、联合索引，但其中一个条件是 >

6、联合索引，order by

where和order by一起使用时，不要跨索引列使用。

三、单表sql优化

---

1、删除student表中的联合索引。

2、添加索引

alter table student add index student_union_index(name,age,sex);

复制代码

优化一点，但效果不是很好，因为type是index类型，extra中依然存在using where。

3、更改索引顺序

因为sql的编写过程

select distinct … from … join … on … where … group by … having … order by … limit …

复制代码

解析过程

from … on … join … where … group by … having … select distinct … order by … limit …

复制代码

因此我怀疑是联合索引建的顺序问题，导致触发索引的效果不好。are you sure？试一下就知道了。

alter table student add index student_union_index2(age,sex,name);

复制代码

删除旧的不用的索引：

drop index student_union_index on student

复制代码

索引改名

ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index

复制代码

更改索引顺序之后，发现type级别发生了变化，由index变为了range。 range：只检索给定范围的行，使用一个索引来选择行。

备注：in会导致索引失效，所以触发using where，进而导致回表查询。

4、去掉in

ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；

index 提升为ref了，优化到此结束。

5、小结

1. 保持索引的定义和使用顺序一致性；

2. 索引需要逐步优化，不要总想着一口吃成胖子；

3. 将含in的范围查询，放到where条件的最后，防止索引失效；

四、双表sql优化

---

1、建表语句

CREATE TABLE student (

id int(10) NOT NULL,

name varchar(20) NOT NULL,

age int(10) NOT NULL,

sex int(11) DEFAULT NULL,

address varchar(100) DEFAULT NULL,

phone varchar(100) DEFAULT NULL,

create_time timestamp NULL DEFAULT NULL,

update_time timestamp NULL DEFAULT NULL,

deleted int(11) DEFAULT NULL,

teacher_id int(11) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制代码

CREATE TABLE teacher (

id int(11) DEFAULT NULL,

name varchar(100) DEFAULT NULL,

course varchar(100) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制代码

2、左连接查询

explain select s.name,t.name from student s left join teacher t on s.teacher_id = t.id where t.course = ‘数学’

复制代码

上一篇介绍过，联合查询时，小表驱动大表。小表也称为驱动表。其实就相当于双重for循环，小表就是外循环，第二张表（大表）就是内循环。

虽然最终的循环结果都是一样的，都是循环一样的次数，但是对于双重循环来说，一般建议将数据量小的循环放外层，数据量大的放内层，这是编程语言的优化原则。

再次代码测试：

student数据：四条

teacher数据：三条

按照理论分析，teacher应该为驱动表。

sql语句应该改为：

explain select teacher.name,student.name from teacher left join student on teacher.id = student.id where teacher.course = ‘数学’

复制代码

优化一般是需要索引的，那么此时，索引应该怎么加呢？往哪个表上加索引？

索引的基本理念是：索引要建在经常使用的字段上。

由on teacher.id = student.id可知，teacher表的id字段使用较为频繁。

left join on，一般给左表加索引；因为是驱动表嘛。

alter table teacher add index teacher_index(id);

alter table teacher add index teacher_course(course);

复制代码

备注：如果extra中出现using join buffer，表明mysql底层觉得sql写的太差了，mysql加了个缓存，进行优化了。

3、小结

1. 小表驱动大表

2. 索引建立在经常查询的字段上

3. sql优化，是一种概率层面的优化，是否实际使用了我们的优化，需要通过explain推测。

五、避免索引失效的一些原则 1、复合索引，不要跨列或无序使用（最佳左前缀）；

2、符合索引，尽量使用全索引匹配；

3、不要在索引上进行任何操作，例如对索引进行（计算、函数、类型转换），索引失效；

4、复合索引不能使用不等于（!=或<>）或 is null（is not null），否则索引失效；

5、尽量使用覆盖索引（using index）；

6、like尽量以常量开头，不要以%开头，否则索引失效；如果必须使用%name%进行查询，可以使用覆盖索引挽救，不用回表查询时可以触发索引；

7、尽量不要使用类型转换，否则索引失效；

8、尽量不要使用or，否则索引失效；

六、一些其他的优化方法

---

1、exist和in

select name,age from student exist/in (子查询);

复制代码

如果主查询的数据集大，则使用in；

如果子查询的数据集大，则使用exist；

2、order by 优化

using filesort有两种算法：双路排序、双路排序（根据IO的次数）

MySQL4.1之前，默认使用双路排序；双路：扫描两次磁盘（①从磁盘读取排序字段，对排序字段进行排序；②获取其它字段）。

MySQL4.1之后，默认使用单路排序；单路：只读取一次（全部字段），在buffer中进行排序。但单路排序会有一定的隐患（不一定真的是只有一次IO，有可能多次IO）。

注意：单路排序会比双路排序占用更多的buffer。

单路排序时，如果数据量较大，可以调大buffer的容量大小。

set max_length_for_sort_data = 1024;单位是字节byte。

复制代码

如果max_length_for_sort_data值太低，MySQL底层会自动将单路切换到双路。

太低指的是列的总大小超过了max_length_for_sort_data定义的字节数。

提高order by查询的策略：

1. 选择使用单路或双路，调整buffer的容量大小；

2. 避免select * from student;（① MySQL底层需要对*进行翻译，消耗性能；② *永远不会触发索引覆盖 using index）；

3. 符合索引不要跨列使用，避免using filesort；

4. 保证全部的排序字段，排序的一致性（都是升序或降序）；

七、sql顺序 -> 慢日志查询

---

慢查询日志就是MySQL提供的一种日志记录，用于记录MySQL响应时间超过阈值的SQL语句（long_query_time，默认10秒） ；

慢日志默认是关闭的，开发调优时打开，最终部署时关闭。

1、慢查询日志

（1）检查是否开启了慢查询日志：

show variables like '%slow_query_log%'

（2）临时开启：

set global slow_query_log = 1;

复制代码

（3）重启MySQL：

service mysql restart;

（4）永久开启：

/etc/my.cnf中追加配置：

放到[mysqld]下：

slow_query_log=1

slow_query_log_file=/var/lib/mysql/localhost-slow.log

复制代码

2、阈值

（1）查看默认阈值：

show variables like ‘%long_query_time%’

复制代码

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）

总结

虽然我个人也经常自嘲，十年之后要去成为外卖专员，但实际上依靠自身的努力，是能够减少三十五岁之后的焦虑的，毕竟好的架构师并不多。

架构师，是我们大部分技术人的职业目标，一名好的架构师来源于机遇(公司)、个人努力(吃得苦、肯钻研)、天分(真的热爱)的三者协作的结果，实践+机遇+努力才能助你成为优秀的架构师。

如果你也想成为一名好的架构师，那或许这份Java成长笔记你需要阅读阅读，希望能够对你的职业发展有所帮助。

[外链图片转存中…(img-QL6513zs-1711711063569)]
[外链图片转存中…(img-ea3zKZcT-1711711063570)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-Rkm0MzYI-1711711063571)]

总结

虽然我个人也经常自嘲，十年之后要去成为外卖专员，但实际上依靠自身的努力，是能够减少三十五岁之后的焦虑的，毕竟好的架构师并不多。

架构师，是我们大部分技术人的职业目标，一名好的架构师来源于机遇(公司)、个人努力(吃得苦、肯钻研)、天分(真的热爱)的三者协作的结果，实践+机遇+努力才能助你成为优秀的架构师。

如果你也想成为一名好的架构师，那或许这份Java成长笔记你需要阅读阅读，希望能够对你的职业发展有所帮助。

[外链图片转存中…(img-7ATgPdkz-1711711063571)]