mysql慢查询优化之从理论和实践说明limit的优点
16lz
2021-12-23
很多时候, 我们预期查询的结果最多是1条记录数据, 那么这个时候, 最好用上limit 1, 当查到这条数据后, mysql会立即终止继续查询, 不进行更多的无用查询, 从而提升了效率。
我们来实际测试一下, 在一个拥有10万的mysql表中, 查找lily的分数(假设系统中只有1个lily, 而我们预期也只需要这条数据)。为了显示出时间的差别, 我并不对表的name字段建索引。
先看看表结构:
mysql> show create table tb_province;+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb_province | CREATE TABLE `tb_province` ( `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `score` int(10) unsigned DEFAULT '0', `x` int(10) unsigned DEFAULT '0', `x1` int(10) unsigned DEFAULT '0', `x2` int(10) unsigned DEFAULT '0', `x3` int(10) unsigned DEFAULT '0', `x4` int(10) unsigned DEFAULT '0', `x5` int(10) unsigned DEFAULT '0', `x6` int(10) unsigned DEFAULT '0', `x7` int(10) unsigned DEFAULT '0', `x8` int(10) unsigned DEFAULT '0', `x9` int(10) unsigned DEFAULT '0', `x10` int(10) unsigned DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=124178 DEFAULT CHARSET=latin1 |+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> select score from tb_province where name='lily';+-------+| score |+-------+| 100 |+-------+1 row in set (0.03 sec)mysql> select score from tb_province where name='lily';+-------+| score |+-------+| 100 |+-------+1 row in set (0.03 sec)mysql> select score from tb_province where name='lily';+-------+| score |+-------+| 100 |+-------+1 row in set (0.04 sec)mysql> select score from tb_province where name='lily';+-------+| score |+-------+| 100 |+-------+1 row in set (0.02 sec)mysql> select score from tb_province where name='lily';+-------+| score |+-------+| 100 |+-------+1 row in set (0.03 sec)mysql> select score from tb_province where name='lily' limit 1;+-------+| score |+-------+| 100 |+-------+1 row in set (0.00 sec)mysql> select score from tb_province where name='lily' limit 1;+-------+| score |+-------+| 100 |+-------+1 row in set (0.00 sec)mysql> select score from tb_province where name='lily' limit 1;+-------+| score |+-------+| 100 |+-------+1 row in set (0.00 sec)mysql> select score from tb_province where name='lily' limit 1;+-------+| score |+-------+| 100 |+-------+1 row in set (0.01 sec)mysql> select score from tb_province where name='lily' limit 1;+-------+| score |+-------+| 100 |+-------+1 row in set (0.00 sec)
mysql> show profiles;+----------+------------+---------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+---------------------------------------------------------+| 5 | 0.02686000 | select score from tb_province where name='lily' || 6 | 0.02649050 | select score from tb_province where name='lily' || 7 | 0.03413500 | select score from tb_province where name='lily' || 8 | 0.02601350 | select score from tb_province where name='lily' || 9 | 0.02785775 | select score from tb_province where name='lily' || 10 | 0.00042300 | select score from tb_province where name='lily' limit 1 || 11 | 0.00043250 | select score from tb_province where name='lily' limit 1 || 12 | 0.00044350 | select score from tb_province where name='lily' limit 1 || 13 | 0.00053200 | select score from tb_province where name='lily' limit 1 || 14 | 0.00043250 | select score from tb_province where name='lily' limit 1 |+----------+------------+---------------------------------------------------------+14 rows in set, 1 warning (0.00 sec)
我们已经从理论和实践的脚本都说明了limit的优点, 所以, 建议是:在可用limit的时候要用limit (当然, 如果结果是多个,肯定不能limit 1啊)
总结
更多相关文章
- 在Android中使用SVG图遇到的坑
- Android性能调优
- Android性能调优
- Android性能调优
- 什么是虚拟主机?虚拟主机的优点和缺点有什么?
- 大数据基础系列之kafka知识点和优点
- 戳破 | hive on spark 调优点
- Android多进程Process开发总结-优点与缺陷
- android TextView 字体竖着写(纵向)的方案