SQL优化系列 - （一）SQL语句优化

对于工作几年的开发人员来说，不论是在工作中，还是在面试时，都会涉及到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、对于字符类型的字段，尽量将该字段设置为 变长类型，降低内存占用