查询执行引擎
MySQL只是简单地根据执行计划给出的指令住不住想, 基本上是通过调用存储引擎实现的接口来完成, 这些接口类似搭积木一样能够完成查询的大部分操作.
返回结果给客户端
MySQL将结果返回给客户端是个增量, 逐步返回的过程. 一旦服务器处理完最后一个关联表, 开始生成第一条结果时, MySQL就可以开始向客户端逐步返回结果集了.
MySQL查询优化器的局限性
关联子查询
老版的MySQL子查询实现的非常糟糕, 新版本的MySQL基本没有问题了.
因此最好通过EXPLAIN命令来实际的看效率是否高
image.png
如果效率不高, 改为关联的方式
image.png
如何用好关联子查询
请通过实际测试来看
UNION的限制
如果需要多个表取出数据union后再limit，可以先limit再取出, 可以提高性能.
并行执行
Mysql不支持多核来并行执行查询
哈希关联
老版本MySQL不支持哈希关联，所有的关联都是循环关联，可以通过建立一个哈希索引列模拟哈希关联.
松散索引扫描
Mysql在5.0之后的，松散索引扫描的一些限制通过"索引条件下推"的方式解决
最大值和最小值优化
image.png
mysql> explain select min(actor_id) from actor where first_name='PENELOPE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
LIMIT 1优化后, 当MySQL读到第一个满足条件的记录就停止.
image.png
不过我实验下来没有区别, 可能sql版本不同吧:
mysql> explain select min(actor_id) from actor where first_name='PENELOPE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> explain select actor_id from actor use index(primary) where first_name='PENELOPE' limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> explain select actor_id from actor where first_name='PENELOPE' limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
对同一张表查询和更新
MySQL不允许对同一张表同时进行查询和更新
image.png
可以通过生成临时表来处理, 子查询在UPDATE语句打开表之前已经完成:
image.png
查询优化器的提示（hint）
略
优化特定类型的查询
count()可以统计某个列值的数量，也可以统计行数
统计某个列时，代表不是NULL 的列
统计行数count(*), * 代表忽略所有的列 直接统计行数，意义清晰，性能会更好
使用近似值
如果count()查询太慢, 可以考虑用explain拿到近似值
更复杂的优化
count() 需要扫描大量的行，优化需要增加汇总表或者类似redis这样的外部缓存系统, 极客时间在第14课讲到了使用redis可能导致不一致问题.
优化关联查询
- 确保ON或USING子句中的列上有索引, 一般只需要在关联顺序中的第二个表上创建索引, 否则带来额外负担
- 确保GROUP BY, ORDER BY的表达式只涉及一个表中的类, 这样MySQL才能使用索引做优化
- 升级MySQL时要仔细评估, 旧的的查询语句可能会变慢甚至结果都发生变化
优化子查询
老版本的MySQL中尽可能用关联查询代替子查询, 不过高版本MySQL已经没有问题了
优化GROUP BY和DISTINCT
首先有限使用索引优化,
如果无法使用索引时，group by 使用两种策略来完成：临时表或文件排序来分组，可以通过 SQL_BIG_RESULT 和 SQL_SMALL_RESULT来让优化器选择希望的方式
若对关联查询分组, 最好使用标识列, 比如下面的语句可以优化为:
优化前
优化后
这个查询在不会有同名的演员的前提下, 改写后的结果不受影响.
- 不过我发现mysql8.0反而速度降低了
mysql> explain select actor.first_name, actor.last_name, count(*) cnt from film_actor inner join actor using(actor_id) group by actor.first_name, actor.last_name;
mysql> show status like 'Last_query_cost';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| Last_query_cost | 617.732404 |
+-----------------+——————+
mysql> explain select actor.first_name, actor.last_name, c.cnt from actor inner join (select actor_id, count(*) as cnt from film_actor group by actor_id) as c using(actor_id);
mysql> show status like 'Last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 2481.148000 |
+-----------------+-------------+
优化limit分页
当一次需要偏移量很大时，尽可能使用索引覆盖扫描, 而不是查询所有的列. 然后根据需要做一次关联操作返回所需的列:
image.png
改写为:
image.png
更详细的, 可以查看我的一篇文章: https://app.yinxiang.com/fx/8e8be2d9-c5df-4cf1-a8f1-602639c4c43b中的分页优化
优化UNION查询
- 如果不需要消除重复行, 尽量用UNION ALL.
- 将where, limit, order by等子句"下推"到UNION的各个子查询中.
用户自定义变量
略, 有不少技巧, 当工具书查询
案例学习
使用MySQL构建一个队列表
我把我的经验和书中内容结合一下:
- 要找到未处理的记录, 一般不会用MySQL的sleep, 而是使用定时job
-
要标记正在处理的记录, 不至于让多个消费者重复处理一个记录:
image.png
-
书中的方案是不要使用select for update锁表, 而是将该表的owner设置为正在处理这个记录的连接ID:
image.png
先更新状态, 再取数据, 根据owner来拿到自己要处理的数据. 若该连接在处理时退出了, 只需要定期运行update语句将其更新为原始状态即可. 可以用show processlist, 获取当前正在工作的线程, 用where条件避免使用到这些刚开始处理的线程:
image.png
-
- 不过我觉得该方案太复杂了, 我们一般可以使用乐观锁, 避免1条记录被多个消费者消费, 在表上加个version字段
- 先查询一批待处理的记录
- 对每条记录, 使用乐观锁技术更新状态为处理中:
update unset_mails set status='claimed', version=version+1 where id=10 and version=0;
- 如果更新成功, 则可以做该任务. 做完后, 将状态翻转为处理成功即可.
- 如果更新没成功, 说明有其他消费者同时抢到了该任务, 那就跳过即可.
- 最后, 如果消费者在处理时由于某种情况退出, 导致记录一直处于处理中的, 可以定期得将它们翻成待处理即可(一般不会考虑是否有进程仍在处理的情况, 因为业务上可以做个估计, 比如超时了10分钟, 说明肯定是消费进程挂了).
网友评论