MySQL中两个小的优化案例

AsiaYe

发布于 2019-11-06 17:17:55

4680

发布于 2019-11-06 17:17:55

文章被收录于专栏：DBA随笔

MySQL中两个小的优化案例

今天介绍两个优化案例，一个是关于min和max这种函数的，另外一个是关于count这个计数函数的。

先来看看第一种情况，关于min和max的：

select min(id) from test_1 where name='yeyz';

上面的SQL中，name列是没有索引的，而id列是主键，但是where条件中写的是name列，所以MySQL会走一次全表扫描，这我们从执行计划中也可以看出来，

mysql:yeyztest 23:25:30>>explain select min(id) from test_1 where name='yeyz';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   43 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

对于这个SQL，其实可以做的优化是强行制定使用某个索引去实现，例如我们可以改为：

select id from test_1 use index(primary) where name='yeyz' limit 1;

如果SQL中使用的是max函数，则可以讲id进行倒序排列，然后取第一条即可。

关于count()函数

关于这个函数，首先要说明的一点是，这个函数是不统计值为null的列的，如果你想用count(字段名)的方式来统计行数，那么需要保证count的字段是非空的，否则结果可能有问题。

我们知道，count(*)的写法会扫描全表，在Innodb和MyIsam中对这个函数的处理不同，MyIsam中可以非常快的得到这个结果，而不用扫描整个表。在MyIsam的存储引擎下，例如我们想要统计一个100w数据记录的表中id>5的值，正常情况下，我们几乎要扫描全部的记录，基于MyIsam的特性，我们可以这样写来实现对它的优化：

mysql:yeyztest 23:34:45>>explain select count(*) from test_1 where id>5;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   38 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql:yeyztest 23:34:57>>explain select (select count(*) from test_1)-count(*) from test_1 where id<5;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | test_1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |   100.00 | Using where; Using index     |
|  2 | SUBQUERY    | NULL   | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

使用反向搜索的思路，可以让记录的行数表的很小。这个例子中，test_1表中有43条记录。反向搜索之后，原来需要过滤38条记录，一下子变成了4条记录，这个提升已经很明显了。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-09-10，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server