|
对于工作几年的开发人员来说，不论是在工作中，还是在面试时，都会涉及到SQL优化的问题。
为什么要进行SQL优化？
1、访问网站时可避免一些错误，例如：
1）连接数据库超时导致页面报5xx错误
2） 因查询过慢导致页面加载缓慢，甚至无法加载
2、增加数据库的稳定性，很多数据库问题都是由于低效的查询引起的
3、使页面的访问更流畅，提升用户体验
哪些方法可以进行SQL优化？
1、SQL语句优化
SQL语句优化，是基础的一种优化方式。
2、主从分离
原因一：对于大部分的应用程序，大部分的操作都是查询（即读数据），小部分的操作是增删改（即写数据）。所以，数据库的大部分压力来自于读操作。当读的压力较大时，我们可以通过读写分离的方式，降低读的压力，提升读取时的效率。
原因二：如果不实现读写分离，所有的数据全部存储于同一个库中，这样，如果因网络问题而连接不到这个库，或者，某些原因导致这个数据库出现故障，那么，所有数据都将丢失。可能会出现单点故障的风险。
3、分表分库
任何一个数据库的存储容量都是有限的，一旦数据量过大，将会导致数据库处理数据的效率急剧下降。所以，我们可以采用分表分库的方式，确保每个库中的数据量在较好的范围内。
此篇文章暂且只介绍SQL语句优化。
SQL语句优化
1、使用索引 ， 合理的使用索引，能够大大提升数据的检索效率。
在经常需要做为查询条件的列上，创建索引。
但是，并不是索引越多越好。索引可提高查询效率，但是会降低增删改的效率。因此，一张表中最好不要超过6个索引。
2、慎用 * ， 务必使用 字段名代替 *
1） select 后跟需要查询的字段名，不要跟 *
select * from stu
数据库执行上面语句时有两步：1、查询stu表中所有的字段 2、根据第一步中查得的字段，查询stu表中的数据
select id, name, age, sex from stu
上面语句将会直接查询stu表中的数据
2） count() 聚合函数中，不要使用 *
select count(*) from table ，这会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
可使用 count(id) 或 count(1) 等替代。
3、慎用 or
如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：
select id from stu where age=20 or name=’admin’
可以这样查询：
select id from stu where age=20 union all select id from stu where name=’admin’
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，union 连接的 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 select 语句中的列的顺序必须相同。
4、慎用 in 和 not in
在查询条件中使用 in 和 not in ，会导致全表扫描。
1）对于连续数据，可使用 between ... and ... 来替代 in
2） 可使用 exists 和 not exists 来替代in 和 not in
# 查找 测试3班 的学生信息
SELECT stu.id,stu.name,stu.c_id FROM stu WHERE stu.c_id IN (SELECT id FROM class WHERE class.name='测试3班');
# 根据stu表的c_id，查询 class表中 class.id = stu.c_id 并且 class.name='测试3班'' 的记录，如果存在这样的记录，则显示stu记录
SELECT stu.id,stu.name,stu.c_id FROM stu WHERE EXISTS ( SELECT 1 FROM class WHERE stu.c_id = class.id AND class.`name`='测试3班')
5、避免使用null填充列
使用 is null 或 is not null 来作为查询条件，将会使数据库放弃索引而进行全表检索，降低查询效率。
所以，尽量使用非null的值去填充字段，比如：对于数值类型的字段，可设默认值为0；对于字符类型的字段，可设默认值为空字符串。
6、尽量避免使用 != 或 <>
应尽量避免在where子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
7、尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
为stu表的name字段创建索引
select id,name,age,sex from stu where substring(name,1,1)='张' ==> 未使用索引
select id,name,age,sex from stu where name like ‘张%’ ==> 使用了索引
8、不要在where子句中的”=”左边进行函数、算数运行或其他表达式运算，否则系统将可能无法正确使用索引。
9、使用复合索引的字段作为查询条件时，必须使用到复合索引的第一个字段，否则，将不会使用到该复合索引。
10、修改数据时，如果只需要修改部分字段，请不要update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
11、对于字符类型的字段，尽量将该字段设置为 变长类型，降低内存占用 | 
转播
