Ronnie's Blog

MySQL 查询优化三则

#MySQL #SQL #运维

原则

  • 避免全表扫描

方法

  • 在where及order by涉及的列上建立索引
  • 避免在where子句中避免null值的判断，否则会导致进行全表扫描，例如
    select id from t where num is null
    可以在num上设置默认值0，确保num列没有null值，然后
    select id from t where num = 0
  • 尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃索引而进行全表扫描
  • 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
    select id from t where num=10 or num=20
    可以这样查询:
    select id from t where num=10 union all select id from t where num=20
  • in 和 not in 也要慎用，否则会导致全表扫描，如：
    select id from t where num in(1，2，3)
    对于连续的数值，能用 between 就不要用 in 了：
    select id from t where num between 1 and 3
  • 下面的查询也将导致全表扫描：
    select id from t where name like '%abc%'
    若要提高效率，可以考虑全文检索
  • 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
    select id from t wherenum=@num
    可以改为强制查询使用索引：
    select id from t with(index(索引名)) wherenum= @num
  • 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
    select id from t where num/2=100
    应改为：
    select id from t where num=100*2
  • 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
    select id from t where substring(name，1，3)='abc' --name以abc开头的id
    select id from t where datediff(day，createdate，'2005-11-30')=0 --‘2005-11-30’生成的id

在这里插入图片描述

我们有分页查询订单详细信息的需求，订单表目前有300多万的数据，而且order表在orderNo列上有索引，传统的写法可能使用limit offset, pageSize的写法来实现，但是有一个问题，在offset非常大的时候，查询会很慢，因为会使Mysql扫描大量的无用行，然后再扔掉，例如下面这样的语句

select orderNo, state, payType, ctime 
from `order` 
order by orderNo limit 2000000, 20;

查询结果如下

+----------------------+-------+---------+---------------+
| orderNo              | state | payType | ctime         |
+----------------------+-------+---------+---------------+
| KC312049Dn0fwL6S67pr |     1 |       2 | 1477496232335 |
| KC312049dOcs7Vbhb0Xw |     1 |       2 | 1477495563416 |
| KC312049DSKeJDYoJj4l |     1 |       2 | 1477496110295 |
| KC312049Dxhcd8yDinjH |     1 |       2 | 1476836872392 |
| KC312049dZvq0FCFgIi4 |     1 |       2 | 1479480408504 |
| KC312049e5AnVZxQ9x2L |     1 |       2 | 1480523251937 |
| KC312049E5MDWi86KpmS |     1 |       2 | 1477495599973 |
| KC312049E7B55XdT0c1q |     1 |       2 | 1477495660296 |
| KC312049Et9RajZgYIHl |     1 |       2 | 1477495538095 |
| KC312049F7gry3ah4QUl |     1 |       2 | 1477495553585 |
| KC312049Ff2diSRvMcAN |     1 |       2 | 1477496484309 |
| KC312049FNLa2qRmrYh0 |     1 |       2 | 1475764142929 |
| KC312049FVHbVyu9peoO |     1 |       2 | 1477496221733 |
| KC312049g2AHmW0hUYbh |     1 |       2 | 1479480264818 |
| KC312049GbCPr2ZKN07d |     1 |       2 | 1477496403856 |
| KC312049gckVBMX77pot |     1 |       2 | 1477495852336 |
| KC312049GeLTHsQf7D6v |     1 |       2 | 1475764268969 |
| KC312049gfMyBax7ipwv |     1 |       2 | 1479480227645 |
| KC312049ggnHiJMtszL3 |     1 |       2 | 1477496584497 |
| KC312049GpaqqGnFGrjy |     1 |       2 | 1475764171923 |
+----------------------+-------+---------+---------------+
20 rows in set (13.26 sec)

结果用了13秒才查出来，性能上是无法接受的，我们可以优化一下

由于上面的问题最重要的原因是扫描了大量无用的行，所以我们就从这里入手，我们知道orderNo列是索引的，我们可以想过办法通过索引覆盖查询来查出必要的需要扫描的行，然后再去扫描实际的数据行

例如，可以改成下面这样

select orderNo, state, payType, ctime 
from `order` inner join (
  select orderNo 
  from `order`
  order by orderNo limit 2000000, 20
) as o using(orderNo);

查询结果如下

+----------------------+-------+---------+---------------+
| orderNo              | state | payType | ctime         |
+----------------------+-------+---------+---------------+
| KC312049Dn0fwL6S67pr |     1 |       2 | 1477496232335 |
| KC312049dOcs7Vbhb0Xw |     1 |       2 | 1477495563416 |
| KC312049DSKeJDYoJj4l |     1 |       2 | 1477496110295 |
| KC312049Dxhcd8yDinjH |     1 |       2 | 1476836872392 |
| KC312049dZvq0FCFgIi4 |     1 |       2 | 1479480408504 |
| KC312049e5AnVZxQ9x2L |     1 |       2 | 1480523251937 |
| KC312049E5MDWi86KpmS |     1 |       2 | 1477495599973 |
| KC312049E7B55XdT0c1q |     1 |       2 | 1477495660296 |
| KC312049Et9RajZgYIHl |     1 |       2 | 1477495538095 |
| KC312049F7gry3ah4QUl |     1 |       2 | 1477495553585 |
| KC312049Ff2diSRvMcAN |     1 |       2 | 1477496484309 |
| KC312049FNLa2qRmrYh0 |     1 |       2 | 1475764142929 |
| KC312049FVHbVyu9peoO |     1 |       2 | 1477496221733 |
| KC312049g2AHmW0hUYbh |     1 |       2 | 1479480264818 |
| KC312049GbCPr2ZKN07d |     1 |       2 | 1477496403856 |
| KC312049gckVBMX77pot |     1 |       2 | 1477495852336 |
| KC312049GeLTHsQf7D6v |     1 |       2 | 1475764268969 |
| KC312049gfMyBax7ipwv |     1 |       2 | 1479480227645 |
| KC312049ggnHiJMtszL3 |     1 |       2 | 1477496584497 |
| KC312049GpaqqGnFGrjy |     1 |       2 | 1475764171923 |
+----------------------+-------+---------+---------------+
20 rows in set (0.54 sec)

通过上面的改写，原本13秒的查询只需要0.5秒便实现了，效果相当明显

这是因为inner join的表通过索引覆盖查询直接通过索引找到了需要返回的数据行，然后order表通过与这个派生表进行关联，只扫描20行数据就可以了

个人公众号，欢迎关注，不定期抽奖送书
在这里插入图片描述
创建一个测试的表

create table test_swap(x char(10), y char(10));

插入几条数据

insert into test_swap values('x1', 'y1'), ('x2', 'y2'), ('x3', null), (null, 'y4');

看一下现在表的样子

select * from test_swap;

输出

+------+------+
| x    | y    |
+------+------+
| x1   | y1   |
| x2   | y2   |
| x3   | NULL |
| NULL | y4   |
+------+------+
4 rows in set (0.00 sec)

执行交换语句

update test_swap set x=(@t:=x), x=y, y=@t;

再看一下交换后表的样子

select * from test_swap;

输出

+------+------+
| x    | y    |
+------+------+
| y1   | x1   |
| y2   | x2   |
| NULL | x3   |
| y4   | NULL |
+------+------+
4 rows in set (0.00 sec)

交换成功

参考

END