◉◡◉ 您好,欢迎到访伊成个人站!

MySQL优化干货总结

写在前面

在面试中无论大厂还是到小公司,一直未变的一个重点就是对SQL优化经验的考察。一提到数据库,先“说一说你对SQL优化的见解吧?”。
SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至在各大厂招聘岗位职能上都有明码标注,如果是你,在这个问题上能吊打面试官还是会被吊打呢?

语法顺序

SELECT语句 - 语法顺序

1
2
3
4
5
6
7
8
9
10
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>

Tips:以下SQL优化策略适用于数据量较大的场景下,如果数据量较小,没必要以此为准。

SQL优化策略

避免不走索引的场景

  1. 尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。
1
SELECT * FROM student WHERE name LIKE '%陈%'

优化方式:尽量在字段后面使用模糊查询。如下:

1
SELECT * FROM student WHERE name LIKE '陈%'
  1. 尽量避免使用in 和not in,会导致引擎走全表扫描
1
SELECT * FROM student WHERE id IN (2,3)

优化方式:如果是连续数值,可以用between代替。如下:

1
SELECT * FROM student WHERE id BETWEEN 2 AND 3

如果是子查询,可以用exists代替。如下:

1
2
3
4
-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);
  1. 尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描。
    1
    SELECT * FROM student WHERE id = 1 OR id = 3

优化方式:可以用union代替or。

1
2
3
SELECT * FROM student WHERE id = 1
UNION
SELECT * FROM student WHERE id = 3

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

1
SELECT * FROM student WHERE score IS NUL

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

1
SELECT * FROM student WHERE score = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。
可以将表达式、函数操作移动到等号右侧。

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

  1. 当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。
    1
    SELECT username, age, sex FROM T WHERE 1=1

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

  1. 查询条件不能用 <> 或者 != 使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。
  1. where条件仅包含复合索引非前置列
    复合(联合)索引包含key_part1,key_part2,key_part3三列,但SQL语句没有包含索引前置列”key_part1”,按照MySQL联合索引的最左匹配原则,不会走联合索引。

    1
    select col1 from table where key_part2=1 and key_part3=2
  2. 隐式类型转换造成不使用索引
    如下SQL语句由于索引对列类型为varchar,但给定的值为数值,涉及隐式类型转换,造成不能正确走索引。

    1
    select col1 from table where col_varchar=123;
  3. order by 条件要与where中条件一致,否则order by不会利用索引进行排序

    1
    2
    3
    4
    5
    -- 不走age索引
    SELECT * FROM student order by age;

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

SELECT语句其他优化

  1. 避免出现select *
  2. 避免出现不确定结果的函数
  3. 多表关联查询时,小表在前,大表在后。
  4. 使用表的别名
  5. 用where字句替换HAVING字句
  6. 调整Where字句中的连接顺序

查询条件优化

  1. 对于复杂的查询,可以使用中间临时表 暂存数据
  1. 优化group by语句
    默认情况下,MySQL 会对GROUP BY分组的所有值进行排序,如 “GROUP BY col1,col2,….;” 查询的方法如同在查询中指定 “ORDER BY col1,col2,…;” 如果显式包括一个包含相同的列的 ORDER BY子句,MySQL 可以毫不减速地对它进行优化,尽管仍然进行排序。
    因此,如果查询包括 GROUP BY 但你并不想对分组的值进行排序,你可以指定 ORDER BY NULL禁止排序。例如:
    SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

  2. 优化join语句
    MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。
    例子:假设要将所有没有订单记录的用户取出来,可以用下面这个查询完成:
    SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
    如果使用连接(JOIN).. 来完成这个查询工作,速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话,性能将会更好,查询如下:

1
2
3
SELECT col1 FROM customerinfo 
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL

连接(JOIN).. 之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

  1. 优化union查询
    MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行,否则建议使用union all。原因在于如果没有all这个关键词,MySQL会给临时表加上distinct选项,这会导致对整个临时表的数据做唯一性校验,这样做的消耗相当高。
    高效:
    1
    2
    3
    4
    5
    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 

    UNION ALL

    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'DEVCHENG';

低效:

1
2
3
4
5
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 

UNION

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'DEVCHENG';

5.拆分复杂SQL为多个小SQL,避免大事务
简单的SQL容易使用到MySQL的QUERY CACHE;
减少锁表时间特别是使用MyISAM存储引擎的表;可以使用多核CPU。

6.使用truncate代替delete
当删除全表中记录时,使用delete语句的操作会被记录到undo块中,删除记录也记录binlog,当确认需要删除全表时,会产生很大量的binlog并占用大量的undo数据块,此时既没有很好的效率也占用了大量的资源。
使用truncate替代,不会记录可恢复的信息,数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外,使用truncate可以回收表的水位,使自增字段值归零。

建表优化

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

  1. 尽量使用数字型字段(如性别,男:1 女:2),若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。

3.查询数据量大的表 会造成查询缓慢。

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

The end .

支付宝打赏 微信打赏