SQL语句优化方案及MySQL数据库优化

最新推荐文章于 2025-11-08 12:30:00 发布

原创最新推荐文章于 2025-11-08 12:30:00 发布 · 824 阅读

2 ·

CC 4.0 BY-SA版权

Database 专栏收录该内容

6 篇文章

订阅专栏

本文主要分析了数据库查询速度慢的原因，如扫描数据行数过多、SQL写法不科学致索引失效等。指出数据库语句查询慢的问题点，如使用“*”、未建索引等。并给出数据库语句及其他优化方案，还介绍了索引和视图的创建原则以及MySQL数据库的优化方法。

1.速度慢原因

数据库扫描数据行数过多（和表行数多少有一定关系）
SQL写法不科学导致的索引失效，出现1.1描述；

2.数据库语句查询慢的问题点

使用“*”，数据库高频访问时，数据库需要额外解析“*”，浪费时间；
数据库查询字段未建立索引，导致全表数据扫描；
字段已建立索引，但SQL写法导致索引无法使用，如下示例：
1. 模糊查询：name like ‘%张’ or name like ‘_张’;
2. 模糊查询：score != 80;
3. Null直接参与比较：where name=null;
4. 范围查询：name in (‘张三’,‘李四’);
5. 索引字段运算：where score+10 = 100;
6. 子查询中使用：group by子句
7. 联合索引：index(name,score)，查询：where score=80；
8. 隐式转换：where name=12315;

3.数据库语句优化方案

所以查询结果不要用“*”来查询所有字段，要使用明确的列名查询：select u.id,u.name from user u;
建立合理的索引，主键约束、外键约束自带索引，优先在主键上查询；
替换的SQL写法：
1. 模糊查询（like）：视情况解决，一般是搜索引擎；
2. 模糊查询（!=）：视情况解决，可采用排除法；
3. Null直接参与比较：改为where name is null；
4. 范围查询（in）：改用exists；
5. 索引字段运算：where score = 100–10;
6. 子查询（group by）：视情况解决，可考虑临时表;
7. 联合索引（name_score）：拆分索引或查询name；
8. 隐式转换：改为where name=‘12315‘;保持类型一致；

4.其他优化方案

如果有like话，尽量避免%xxx%两侧都有%的条件，单侧%可以使用索引，多侧不可以。
应尽量避免在where子句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。
应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。
in 和 not in 也要慎用，否则会导致全表扫描。
对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。
根据查询条件，如果查询条件不止一个时，使用组合索引。
在查询条件表达式的左侧尽量不要使用函数，否则索引失效。

5.特殊说明

Group by子句是我们常见的导致效率低下的原因，解决办法视情况不同而不同，下边举一个实际的例子对比下：
用户表user：uid主键，name姓名
分数表user_score：usid主键，uid用户表外键，item科目，score分数
两张表分别是用户表user和用户分数表user_score，现在统计算每个用户的总分。

方法1：

SELECT 
  u.uid,
  u.name,
  (SELECT 
    SUM(score) 
  FROM
    user_score 
  WHERE uid = u.uid) sumScore 
FROM
  USER u ;

方法2：

SELECT 
  u.uid,
  u.name,
  SUM(us.score) sumScore 
FROM
  USER u 
  LEFT JOIN user_score us 
    ON us.uid = u.uid 
GROUP BY u.uid,
  u.name ;

方法3：

SELECT 
  u.uid,
  u.name,
  s.score sumScore 
FROM
  USER u 
  LEFT JOIN 
    (SELECT 
      uid,
      SUM(score) score 
    FROM
      user_score 
    GROUP BY uid) s 
    ON u.uid = s.uid ;

此三种方法，方法1查询速度会明显优于方法2以及方法3。方法2使用了group by语句。方法3使用了group by子句，导致子句查询出来的结果会存入缓存，在缓存中进行查询时left join无法引用索引。

方法4：

SELECT 
  u.uid,
  u.name,
  s.sumScore 
FROM
  (SELECT 
    us.uid,
    SUM(us.score) sumScore 
  FROM
    user_score us 
  GROUP BY us.uid) s 
  LEFT JOIN USER u 
    ON u.uid = s.uid ;

方法4（效率略优于方法3，差异基本可忽略，也是常被遗忘的方法）。

解释说明：
如果有100个学生，200条学分（每人2条）：

方法1 数据行扫描数：
最低是：100user+100*1 SUM=200次,
最高是：100user+100*1 SUM=200次;

方法2 数据行扫描数：
最低是：100user+100groupby+100*1 SUM=300次,
最高是：100user+100groupby+100*1 SUM=300次;

方法3 数据行扫描数：
最低是：100user+200groupby+100*1 leftjoin=400次,
最高是：100user+200groupby +100*100 leftjoin=13000次;

有最高和最低是因为可能第一次查询就匹配到，也可能扫描到最后才匹配到数据。

6.索引的建立原则

索引并非越多越好，索引固然可以提高相应的select 查询的效率，但会影响物理表insert 插入、update 修改和delete删除效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑。每张表索引尽量不超过3个；
尽量不要在长文本字段建立索引，索引字段长度尽量不超过32位；
如果已经建立了a_b的联合索引，则无需再在a字段上建立索引；
建立索引时字段不能有null值；