全表扫描

全表扫描

查询sql语句：select Name ,Id, count(*) from test where sesTime is not null group by Name order by count(*) desc limit 15;############################查看sql语句的执行计划：desc select Name ,Id, count(*) from test where sesTime is not null group by Name order by count(*) desc limit 15;+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 1305648 | 90.00 | Using where; Using temporary; Using filesort |+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+1 row in set, 1 warning (0.00 sec)Thu Oct 10 18:34:36 2019显然，这是一个全表扫描语句，##########################查看该表的数据量：select count(*) from test;+----------+| count(*) |+----------+| 1373833 |+----------+1 row in set (0.48 sec)Thu Oct 10 18:45:10 2019显然，该表数据量有140万条记录左右。############################查看该表结构：show create table mbi_order_temp\G;*************************** 1. row ***************************Thu Oct 10 18:48:10 2019从这个表结构语句可知：分组字段fiveGradeName没有索引，因此整个语句都是全表扫描##############

xxx@igoodful(yyy) > show create table job \G*************************** 1. row *************************** Table: job_statCreate Table: CREATE TABLE `job_stat` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `pdl` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '产品线', `tags` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'j名称', `cluster` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '所在集群', `cpus` float NOT NULL COMMENT 'cpu模版数量', `mem` float NOT NULL COMMENT 'mem模板数量', `disk` float NOT NULL COMMENT 'disk模板数量', `all_cpus` float NOT NULL COMMENT 'cpu总数量', `all_mem` float NOT NULL COMMENT 'mem总数量', `all_disk` float NOT NULL COMMENT 'disk总数量', `used_cpus` float NOT NULL COMMENT 'cpu使用数量', `used_mem` float NOT NULL COMMENT 'mem使用数量', `used_disk` float NOT NULL COMMENT 'disk使用数量', `running` int(11) NOT NULL COMMENT 'task running 数量', `instances` int(11) DEFAULT NULL COMMENT 'instances数量', `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间', PRIMARY KEY (`id`), KEY `idx_job_ts` (`tags`,`timestamp`), KEY `idx_pdl` (`pdl`), KEY `idx_ts` (`timestamp`)) ENGINE=InnoDB AUTO_INCREMENT=75982981 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci1 row in set (0.00 sec)

xxx@igoodful(yyy) > select count(*) from

+----------+| count(*) |+----------+| 62680162 |+----------+1 row in set (10.11 sec)#################################################下面就是一个全表扫描语句：原因：like使用了前导%，导致索引失效，进而全表扫描。该语句执行差不多需要2分钟。#################################################

xxx@igoodful(yyy) > select * from job where tags like "%service.browser-score-service_cluster.c3_pdl.browser_owt.miui_cop.xiaomi%";

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。