文章目录

  • 琐碎（关于索引）
  • SQL的优化策略
  • 1. 避免不走索引的情况
  • 2. SELECT语句的其他优化
  • 3. 增删改 DML 语句优化
  • 4. 查询条件优化
  • ..


琐碎（关于索引）

索引 就类似于 分组；
有时候需要联合索引；
索引储存时会占用大量内存空间；
每次更新数据时，必须更新索引；
索引越多，写入操作越慢；

一些操作：

  • 添加索引：CREATE INDEX index_name ON table_name(col_1, col_2)
  • 查询优化：EXPLAIN SELECT ...
  • 删除索引：DROP INDEX index_name ON table_name

MySQL会自动将表的主键包含在每个二级索引中，这些单个列的索引会浪费很多空间，因此使用复合索引会更好；
MySQL中，一个索引中最多可以包含16列（通常会有些高，可以根据实际情况调整）

注：
不是创建表格时就要添加索引，而是针对特定的查询而引入索引来优化查询；
不是所有的查询都需要用索引来优化，只优化性能关键的查询；

联合索引中列的顺序
原则：

  • 1、会经常使用的列放在前面
  • 2、把基数较高的（索引）列放在第一位（非硬性规定），因为可以将表分成更小的部分；
    （如下图，第二种索引方式会更好）

正片在下面

SQL的优化策略

（在数据量较大的情况）

1. 避免不走索引的情况

1、尽量避免字段开头的模糊查询

如LIKE '%Robin%' ，会导致数据库引擎放弃索引而进行全表扫描，最好改为LIKE 'Robin%'。
如果需求无法更改，则可以考虑以下方法：

  • 使用MySQL内置函数INSTR(str,substr) 来匹配
  • 使用FullText全文索引，用match against 检索

2、尽量避免使用 IN 和 NOT IN

IN 和 NOT IN会导致引擎走全表扫描，优化方式：

  • 若是连续数值，可以使用 BETWEEN 替代
  • 若是子查询，可以使用EXISTS替代

3、尽量避免使用 OR

使用OR同样是会导致数据库引擎放弃索引而进行全表扫描。
优化方法：使用 UNION 替代 OR

4、尽量避免进行NULL值的判断

使用 IS NULL 或者 IS NOT NULL也会导致数据库引擎放弃索引而进行全表扫描。
优化方法：可以给字段添加默认值0，转为对0值的判断

5、查询条件不要使用 <> 或者 !=
在使用索引列作为条件进行查询时，需要避免使用 <> 或者 !=等判断条件。
若业务确实需要使用不等符号，则需要重新评估索引建立，避免在该字段上建立索引，改由其他字段代替。

6、尽量避免在WHERE条件中的等号左侧进行表达式、函数操作

会导致数据库引擎放弃索引而进行全表扫描。
优化方法：移至等号右侧

7、不能违背最左原则
对于联合索引 abc_index(a,b,c)，在查询条件（WHERE）中出现缺失索引最高优先级的索引'a'和次级优先级的索引'b'，这两种情况均会导致索引失效。

8、ORDER BY 条件要与WHERE中条件一致

否则ORDER BY 不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;
 
-- 走age索引
SELECT * FROM t where age > 0 order by age;

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

2. SELECT语句的其他优化

1、避免出现 SELECT *

使用SELECT * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

2、多表关联查询时，小表在前，大表在后
让小表进行全表扫描，在扫描后面的大表时，或许不需要扫描完就能满足返回条件了。

3、使用WHERE代替HAVING

避免使用HAVING，因为HAVING是在检索所有记录之后才对结果集进行过滤，而WHERE是在聚合之前筛选记录，因此可以减少这方面的开销

4、使用表的别名

连接多张表时，在每个列名前使用相应表格的别名，这样可以减少解析时间并减少由于歧义引起的语法错误。

5、调整WHERE语句中过滤条件的顺序

MySQL 采用从左至右，自上而下的顺序解析WHERE子句。因此，应该将过滤更多的条件往前放，更快地去缩小结果集。

3. 增删改 DML 语句优化

暂略

4. 查询条件优化

1、复杂查询可以使用中间临时表暂存数据。
2、优化 JOIN：在WHERE 中使用 JOIN + IS NULL 来替代 子查询 + NOT IN 的操作。因为前者少一次查询操作

3、优化 UNION 查询

UNION ALL 的效率 比UNION高，若非确实要消除重复行，则最好加上 ALL

…