mysql数据库sql优化


文章目录

  • mysql数据库sql优化
  • 1、优化原则
  • 2、sql执行顺序
  • 3、避免不走索引的场景
  • 4、其他优化
  • 5、查询条件优化
  • 6、建表优化


oracle相关sql优化

1、优化原则

  • 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。
  • 返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。
  • 减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。
  • 减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用。
  • 利用更多资源：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。

其实也可以总结为一下三点：

  • 最大化利用索引。
  • 尽可能避免全表扫描。
  • 减少无效数据的查询。

2、sql执行顺序

1. SELECT  
2. DISTINCT <select_list> 
3. FROM <left_table> 
4. <join_type> JOIN <right_table> 
5. ON <join_condition> 
6. WHERE <where_condition> 
7. GROUP BY <group_by_list> 
8. HAVING <having_condition> 
9. ORDER BY <order_by_condition> 
10.LIMIT <limit_number>

如上sql执行顺序：

FROM 
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。 
ON 
<筛选条件> # 对笛卡尔积的虚表进行筛选 
JOIN <join, left join, right join...>  
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中 
WHERE 
<where条件> # 对上述虚表进行筛选 
GROUP BY 
<分组条件> # 分组 
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的 
HAVING 
<分组筛选> # 对分组后的结果进行聚合筛选 
SELECT 
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外 
DISTINCT 
# 数据除重 
ORDER BY 
<排序条件> # 排序 
LIMIT 
<行数限制>

对上面同一优先级mysql执行顺序为自左向右执行，而oracle为自右向左，正好相反。

如mysql： where a =1 and b =2 会先执行a=1

如oracle： where a =1 and b =2 会先执行b=2

3、避免不走索引的场景

1、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE username LIKE '%陈%' 
会导致全表扫描

优化方式：尽量在后面使用模糊查询，不在前面使用
SELECT * FROM t WHERE username LIKE '陈%'

如果需求是要在前面使用模糊查询：

  • 使用 MySQL 内置函数 INSTR(str，substr)来匹配，作用类似于 Java 中的 indexOf()，查询字符串出现的角标位置。
  • 使用 FullText 全文索引，用 match against 检索。
  • 数据量较大的情况，建议引用 ElasticSearch、Solr，亿级数据量检索速度秒级。
  • 当表数据量较少(几千条儿那种)，别整花里胡哨的，直接用 like ‘%xx%’。

2、尽量避免使用 in 和 not in，会导致引擎走全表扫描

SELECT * FROM t WHERE id IN (2,3)

优化方式：

  • 如果是连续数字可以用between 2 and 3;
  • 如果是子查询可以用exists来代替
-- 不走索引 
select * from A where A.id in (select id from B); 
-- 走索引 
select * from A where exists (select * from B where B.id = A.id);

3、尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描

ELECT * FROM t WHERE id = 1 OR id = 3

优化方式：使用union 来替代。

SELECT * FROM t WHERE id = 1 
   UNION 
SELECT * FROM t WHERE id = 3

4、尽量避免进行 null 值的判断，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值 0，对 0 值进行判断。

SELECT * FROM t WHERE score = 0

5、尽量避免在 where 条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

-- 全表扫描 
SELECT * FROM T WHERE score/10 = 9 
-- 走索引 
SELECT * FROM T WHERE score = 10*9

6、当数据量大时，避免使用 where 1=1 的条件

优化方式：用代码拼装 SQL 时进行判断，没 where 条件就去掉 where，有 where 条件就加 and。

7、查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。

如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8、where 条件仅包含复合索引非前置列

如下：复合(联合)索引包含 key_part1，key_part2，key_part3 三列，但 SQL 语句没有包含索引前置列"key_part1"，按照 MySQL 联合索引的最左匹配原则，不会走联合索引。

select col1 from table where key_part2=1 and key_part3=2

9、隐式类型转换造成不使用索引

如下 SQL 语句由于索引对列类型为 varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;  
应修改成：
select col1 from table where col_varchar='123';

10、order by 条件要与 where 中条件一致，否则 order by 不会利用索引进行排序

-- 不走age索引 
SELECT * FROM t order by age; 
 
-- 走age索引 
SELECT * FROM t where age > 0 order by age;
  • 第一步：根据 where 条件和统计信息生成执行计划，得到数据。
  • 第二步：将得到的数据排序。当执行处理数据(order by)时，数据库会先查看第一步的执行计划，看 order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
  • 第三步：返回排序后的数据。

当 order by 中的字段出现在 where 条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

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

4、其他优化

1、避免出现 select *

2、避免出现不确定结果的函数

3、多表关联查询时，小表在前，大表在后

在 MySQL 中，执行 from 后的表关联查询是从左往右执行的**(Oracle 相反)**，第一张表会涉及到全表扫描。

所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前 100 行就符合返回条件并 return 了。

例如：表 1 有 50 条数据，表 2 有 30 亿条数据;如果全表扫描表 2，你品，那就先去吃个饭再说吧是吧。

4、使用表的别名

5、用 where 字句替换 HAVING 字句

where 和 having 的区别：where 后面不能使用组函数。

6、调整 Where 字句中的连接顺序

MySQL 采用从左往右，自上而下的顺序解析 where 子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

5、查询条件优化

1、对于复杂的查询，可以使用中间临时表暂存数据

2、优化 group by 语句

默认情况下，MySQL 会对 GROUP BY 分组的所有值进行排序，如 “GROUP BY col1，col2，…;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，…;” 。

如果显式包括一个包含相同的列的 ORDER BY 子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL 禁止排序。

3、优化 union 查询

MySQL 通过创建并填充临时表的方式来执行 union 查询。除非确实要消除重复的行，否则建议使用 union all。

4、拆分复杂 SQL 为多个小 SQL，避免大事务

  • 简单的 SQL 容易使用到 MySQL 的 QUERY CACHE。
  • 减少锁表时间特别是使用 MyISAM 存储引擎的表。
  • 可以使用多核 CPU。

6、建表优化

1、在表中建立索引，优先考虑 where、order by 使用到的字段。

2、尽量使用数字型字段(如性别，男：1 女：2)

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

3、用 varchar/nvarchar 代替 char/nchar。

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。