精华内容
下载资源
问答
  • 已经遇到好几次这个问题了,...这样我程序中执行超时存储过程,1秒钟就可以执行出来了。 百度下什么是存储过程预编译。 ------------------------------------------------------------------------------------

    转载:http://blog.csdn.net/pgbiao/article/details/22388945

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    这两天遇到一个问题令人比较郁闷,一个大概120行左右的存储过程在SQL Server2012的查询分析器里面执行,

    速度非常理想,1秒不到,即可筛选抓取到大概500条数据记录。
    但在C#程序代码里调用,就提示连接超时。把CommandTimeout设置为300,就要3分钟左右时间才能显示出来,
    检查了几遍代码也没有发现错误。问题依旧。

    原因分析:
    1、由于在查询分析器里执行速度很快,并且数据量也不多。
    2、只在程序里调用才有缓慢的情况。
    3、设置CommandTimeout参数,就可以显示结果出来,但要很久。

    综上分析,初步断定问题出在C#代码上。但检查后没有收获。

    在百度上查询这方面的资料。
    在CSDN论坛上终于找到类似的资料贴子。其中有一网友在回复中说“有可能是执行计划过期吧”,
    真是一言惊醒梦中的我。

    立即在查询分析器上执行:

    exec sp_recompile @objname='存储过程名称'

    exec sp_recompile @objname='存储过程名称'

    再次测试程序,这次终于成功了。速度很满意。

    原因分析:
    由于存储过程是预编译的, 在第一次执行的时候, 会生成执行计划, 以后执行的时候, 会使用这个执行计划(除非存储过程侯或者显示指定重新编译), 而不是每次执行时都去生成执行计划。
    当存储过程涉及的对象结构调整, 或者相关的数据产生了很大变化, 这可能导致原来的计划不适合当前的现状(执行计划过期), 这种情况下应该重新编译存储过程。

    如果修改一次不行,可以再修改一次,再等会测试

    展开全文
  • 这显然是有问题的,可以确定应该是语法层面的问题。页面的渲染过程无需等到 mount 中的方法执行完成后才能进行 那为什么会造成这种情况呢?经过排查,发现是在 getAllVirtualResourceList 函数的 ajax 请求中,用到...
    this.mounted=function(){
    	this.getAllVirtualResourceList();
    },
    

    假设在 mount 中执行的 getAllVirtualResourceList 函数十分耗时,造成了整个页面在函数未执行结束前都无法渲染。这显然是有问题的,可以确定应该是语法层面的问题。页面的渲染过程无需等到 mount 中的方法全部执行完成后才能进行

    那为什么会造成这种情况呢?经过排查,发现是在 getAllVirtualResourceList 函数的 ajax 请求中,用到了 async:false,使得请求变成同步的了

    解决方式应当为:删除这个配置,使 async 为默认的 true(异步)

    此外,在渲染完成后,加载数据的过程中,可以为这一区域加上遮罩

    其中 info-table-id 为需要加遮罩区域的 id

    getAllVirtualResourceList : function(){
    	var _self = this;	
    	$("#info-table-id").showLoading();
    	$.ajax({
    		url : path + "/dashboard/virtualOverviewAction!getAllVirtualResourceList.action",
    		cache: false,
    		type : 'post',
    		dataType:'json',
    		success : function(data) {
    			_self.infoTableData = data;
    			$("#info-table-id").hideLoading();	
    		},
    		error : function(err) {
    		}
    	});
    },
    
    展开全文
  • 我昨天刚好碰到一个问题,应用服务器隔一小段时间就自动重启,最后才定位到是查询的问题,生产环境停摆了半天!查询:超过指定时间的SQL查询。查询的危害:轻者影响整个系统性能,重者直接导致服务重启或死机。...

    我昨天刚好碰到一个问题,应用服务器隔一小段时间就自动重启,最后才定位到是慢查询的问题,生产环境停摆了半天!

    慢查询:超过指定时间的SQL查询。

    慢查询的危害:轻者影响整个系统性能,重者直接导致服务重启或死机。

    136993010_1_20180628033847643

    开始慢查询记录日志:使用set global slow_query_log=on;开启慢查询日志记录。

    然后使用show variables like 'slow%';查找慢查询相关情况,除了能看到slow_query_log=on;还有slow_query_log_file用来记录慢查询的文件!

    使用show variables like 'long%';查看慢查询的时间定义为多少?|long_query_time|0.01|(以秒为单位)。

    慢查询原因和解决方案:

    ①,数据库自身原因:内存爆满,发生死锁,服务器卡死等。

    解决方法:增加内存,数据库连接池调整等。

    ②,数据库表设计问题:表中数据量过大,关键字段没有加索引,索引列有大量空等。

    解决方法:采取分表方式(分库分表,水平,垂直分表等)将数据均衡分布在不同的服务器上,增加索引(避免索引字段为大字段和很多空的情况)!

    ③,查询语句有问题:没有用到索引,查询条件中使用了聚合函数,返回大量不必要的字段,查询返回的数据量过大!

    解决方法:优化sql:1,条件,排序,分组等使用索引列;2,如果查询出来的数据量过大,采取分页,多次查询的方式,避免内存爆满;

    136993010_2_20180628033847706

    我昨天就是碰到提供给别人的接口,传入的查询条件过少,从60几万的数据中筛选30几万的数据,然后打印日志的时候服务器爆了,频繁重启,重启之后继续挂。。。

    慢查询是开发过程中常见的问题,需要程序员经常主观性的查看是否有过多慢查询引起服务性能低下的情况,以上是我遇到的坑,希望能对大家有用!更多的技术分享,敬请关注。。。

    展开全文
  • MySQL explain 命令语句提供了如何执行 SQL 语句信息,解析 SQL 语句的执行计划并展示,explain 支持 select、delete、insert、replace 和 update 等语句,也支持对分区表解析。通常 explain 用来获取 select ...

    0ef3dd912942d873473870695ebfc887.png

    MySQL的 explain 命令语句提供了如何执行 SQL 语句的信息,解析 SQL 语句的执行计划并展示,explain 支持 select、delete、insert、replace 和 update 等语句,也支持对分区表的解析。

    通常 explain 用来获取 select 语句的执行计划,通过 explain 展示的信息我们可以了解到表查询的顺序,表连接的方式等,并根据这些信息判断 select 执行效率,决定是否添加索引或改写 SQL 语句优化表连接方式以提高执行效率。本文参考官方文档:EXPLAIN Output Format 对 explain 输出的内容进行说明,同时也对自己之前使用 explain 不清晰的方面进行总结。

    本文使用的 MySQL 版本为官方社区版 5.7.24。

    mysql root@localhost:(none)> select version();+------------+| version()  |+------------+| 5.7.24-log |+------------+1 row in setTime: 0.066s

    主要用法

    { EXPLAIN | DESCRIBE } [EXTENDED | PARTITIONS | FORMAT=[TRADITIONAL | JSON]] SQL_STATEMENT;
    1. EXPLAIN 和 DESCRIBE(可以简写成 DESC)都可以用来查看语句的执行计划,但通常使用 EXPLAIN 较多;
    2. FORMAT 选项可以指定执行计划输出信息为 JSON 格式,而且包含一些更详细的指标说明;
    3. EXTENDED 和 PARTITIONS 选项可以输出更详细选项说明,语法上是为了兼容低版本 MySQL,未来会废弃,默认使用 EXPLAIN 命令即可。

    测试数据

    本文基于 MySQL 官方示例数据库 employee:Example Databases 进行解析说明,使用到的表如下:

    -- employees:mysql root@localhost:employees> show create table employeesG;***************************[ 1. row ]***************************Table        | employeesCreate Table | CREATE TABLE `employees` (  `emp_no` int(11) NOT NULL,  `birth_date` date NOT NULL,  `first_name` varchar(14) NOT NULL,  `last_name` varchar(16) NOT NULL,  `gender` enum('M','F') NOT NULL,  `hire_date` date NOT NULL,  PRIMARY KEY (`emp_no`),  KEY `idx_first_last` (`first_name`,`last_name`),  KEY `idx_birth_hire` (`birth_date`,`hire_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in setTime: 0.008s-- dept_emp:mysql root@localhost:employees> show create table dept_empG;***************************[ 1. row ]***************************Table        | dept_empCreate Table | CREATE TABLE `dept_emp` (  `emp_no` int(11) NOT NULL,  `dept_no` char(4) NOT NULL,  `from_date` date NOT NULL,  `to_date` date NOT NULL,  PRIMARY KEY (`emp_no`,`dept_no`),  KEY `dept_no` (`dept_no`),  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in setTime: 0.010s-- departments:mysql root@localhost:employees> show create table departmentsG;***************************[ 1. row ]***************************Table        | departmentsCreate Table | CREATE TABLE `departments` (  `dept_no` char(4) NOT NULL,  `dept_name` varchar(40) NOT NULL,  PRIMARY KEY (`dept_no`),  UNIQUE KEY `dept_name` (`dept_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in setTime: 0.012s

    输出说明

    mysql root@localhost:employees> explain select count(*) from employees;+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra       |+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+| 1  | SIMPLE      | employees |      | index |         | PRIMARY | 4       |  | 299512 | 100.0    | Using index |+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+1 row in setTime: 0.026s

    通过以上示例语句得出 explain 输出有 12 个字段,主要说明如下表:

    bb7cb084fca92673d9eca613d329a308.png

    id

    id 为 select 标识符,语句在执行计划当中的执行顺序。id 值的出现有如下几种情况:

    1. id 值全相同,则按由上到下顺序执行;
    2. id 值全不相同,则按 id 值大小,由大到小顺序执行;
    3. id 值部分相同,部分不相同,则同组 id 值大的优先执行(组内 id 值相同的顺序执行)。
    -- id 全相同mysql root@localhost:employees> explain select * from employees e,dept_emp d,departments de where e.emp_no = d.emp_no and de.dept_name = 'Human                                Resources';+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref                | rows   | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+| 1  | SIMPLE      | de    |      | const | dept_name     | dept_name | 122     | const              | 1      | 100.0    | Using index || 1  | SIMPLE      | e     |      | ALL   | PRIMARY       |     |   |              | 299512 | 100.0    |       || 1  | SIMPLE      | d     |      | ref   | PRIMARY       | PRIMARY   | 4       | employees.e.emp_no | 1      | 100.0    |       |+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+3 rows in setTime: 0.018s-- id 全不相同mysql root@localhost:employees> explain select * from employees e where e.emp_no = (select d.emp_no from dept_emp d where d.dept_no = (select de.d                                ept_no from departments de where de.dept_name = 'Development') and d.emp_no = 10023);+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys   | key       | key_len | ref         | rows | filtered | Extra       |+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+| 1  | PRIMARY     | e     |      | const | PRIMARY         | PRIMARY   | 4       | const       | 1    | 100.0    |       || 2  | SUBQUERY    | d     |      | const | PRIMARY,dept_no | PRIMARY   | 16      | const,const | 1    | 100.0    | Using index || 3  | SUBQUERY    | de    |      | const | dept_name       | dept_name | 122     | const       | 1    | 100.0    | Using index |+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+3 rows in setTime: 0.027s-- id 部分相同,部分不相同mysql root@localhost:employees> explain select * from^Iemployees e where^Ie.emp_no in (select d.emp_no from dept_emp d where d.dept_no = (select d                                e.dept_no from departments de where de.dept_name = 'Human Resources'));+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+| id | select_type | table | partitions | type   | possible_keys   | key       | key_len | ref                | rows  | filtered | Extra       |+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+| 1  | PRIMARY     | d     |      | ref    | PRIMARY,dept_no | dept_no   | 12      | const              | 33212 | 100.0    | Using index || 1  | PRIMARY     | e     |      | eq_ref | PRIMARY         | PRIMARY   | 4       | employees.d.emp_no | 1     | 100.0    |       || 3  | SUBQUERY    | de    |      | const  | dept_name       | dept_name | 122     | const              | 1     | 100.0    | Using index |+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+3 rows in setTime: 0.020s

    select_type

    select_type 为表查询的类型,根据官方文档总结几种常见类型如下表:

    c7dfb17d46fd0c59faf2d31ec14a28af.png

    1. SIMPLE:最常见的查询类型,通常情况下没有子查询、union 查询就是 SIMPLE 类型。

    mysql root@localhost:employees> explain select * from employees where emp_no = 10001;+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+| 1  | SIMPLE      | employees |      | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.0    |  |+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+1 row in setTime: 0.019s
    1. PRIMARY 和 SUBQUERY:在含有子查询的语句中会出现。
    mysql root@localhost:employees> explain select * from dept_emp d where d.dept_no = (select de.dept_no from departments de where de.dept_name = 'De                                velopment');+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref   | rows   | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+| 1  | PRIMARY     | d     |      | ref   | dept_no       | dept_no   | 12      | const | 148054 | 100.0    | Using where || 2  | SUBQUERY    | de    |      | const | dept_name     | dept_name | 122     | const | 1      | 100.0    | Using index |+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+2 rows in setTime: 0.021s
    1. UNION 和 UNION RESULT:在有 union 查询的语句中出现。
    mysql root@localhost:employees> explain select * from departments where dept_no = 'd005' union select * from departments where dept_no = 'd004';+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+| id     | select_type  | table       | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra           |+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+| 1      | PRIMARY      | departments |      | const | PRIMARY       | PRIMARY | 12      | const  | 1      | 100.0    |           || 2      | UNION        | departments |      | const | PRIMARY       | PRIMARY | 12      | const  | 1      | 100.0    |           ||  | UNION RESULT |   |      | ALL   |         |   |   |  |  |    | Using temporary |+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+3 rows in setTime: 0.020s
    1. DEPENDENT UNION 和 DEPENDENT SUBQUERY:当语句中子查询和 union 查询依赖外部查询会出现。
    mysql root@localhost:employees> explain select * from employees e where e.emp_no in (select d.emp_no from dept_emp d where d.from_date = '1986-06-                                26' union select d.emp_no from dept_emp d where d.from_date = '1996-08-03');+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+| id     | select_type        | table      | partitions | type | possible_keys | key     | key_len | ref    | rows   | filtered | Extra          |+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+| 1      | PRIMARY            | e          |      | ALL  |         |   |   |  | 299512 | 100.0    | Using where    || 2      | DEPENDENT SUBQUERY | d          |      | ref  | PRIMARY       | PRIMARY | 4       | func   | 1      |  10.0    | Using where    || 3      | DEPENDENT UNION    | d          |      | ref  | PRIMARY       | PRIMARY | 4       | func   | 1      |  10.0    | Using where    ||  | UNION RESULT       |  |      | ALL  |         |   |   |  |  |    | Using temporary|+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+4 rows in setTime: 0.022s
    1. DERIVED:当查询涉及生成临时表时出现。
    mysql root@localhost:employees> explain select * from (select * from departments limit 5) de;+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+| id | select_type | table       | partitions | type  | possible_keys | key       | key_len | ref    | rows | filtered | Extra       |+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+| 1  | PRIMARY     |   |      | ALL   |         |     |   |  | 5    | 100.0    |       || 2  | DERIVED     | departments |      | index |         | dept_name | 122     |  | 9    | 100.0    | Using index |+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+2 rows in setTime: 0.012s
    1. table

    指执行计划当中当前是从哪张表获取数据,如果为表指定了别名,则显示别名,如果没有涉及对表的数据读取,则显示 NULL,还有如下几种情形:

    • :数据来自union查询的id为M和N的结果集;
    • :数据来自派生表id为N的结果集;
    • :数据来自子查询id为N的结果集。
    1. partitions

    指执行计划中当前从分区表哪个表分区获取数据,如果不是分区表,则显示为 NULL。

    -- 示例数据库 employees 的分区表 salariesmysql root@localhost:employees> show create table salaries;+----------+-----------------------------------------------------------------+| Table    | Create Table                                                    |+----------+-----------------------------------------------------------------+| salaries | CREATE TABLE `salaries` (                                       ||          |   `emp_no` int(11) NOT NULL,                                    ||          |   `salary` int(11) NOT NULL,                                    ||          |   `from_date` date NOT NULL,                                    ||          |   `to_date` date NOT NULL,                                      ||          |   PRIMARY KEY (`emp_no`,`from_date`)                            ||          | ) ENGINE=InnoDB DEFAULT CHARSET=utf8                            ||          | /*!50500 PARTITION BY RANGE  COLUMNS(from_date)                 ||          | (PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB, ||          |  PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB, ||          |  PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB, ||          |  PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB, ||          |  PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB, ||          |  PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB, ||          |  PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB, ||          |  PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB, ||          |  PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB, ||          |  PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB, ||          |  PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB, ||          |  PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB, ||          |  PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB, ||          |  PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB, ||          |  PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB, ||          |  PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB, ||          |  PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB, ||          |  PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB, ||          |  PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */  |+----------+-----------------------------------------------------------------+1 row in setTime: 0.018smysql root@localhost:employees> explain select * from salaries where from_date > '1985-12-31' and from_date < '1990-12-31';+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+| id | select_type | table    | partitions          | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra       |+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+| 1  | SIMPLE      | salaries | p02,p03,p04,p05,p06 | ALL  |         |  |   |  | 384341 | 11.11    | Using where |+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+1 row in setTime: 0.023s

    type

    type 应该被认为是解读执行计划当中最重要的部分,根据 type 显示的内容可以判断语句总体的查询效率。主要有以下几种类型:

    1. system:表只有一行(系统表),是 const 的一种特殊情况。
    -- 测试表 departments_1 生成:mysql root@localhost:employees> create table departments_1 as select * from departments where dept_no='d005';Query OK, 1 row affectedTime: 0.107s mysql root@localhost:employees> alter table departments_1 add primary key(dept_no);Query OK, 0 rows affectedmysql root@localhost:employees> create index idx_dept_name on departments_1(dept_name);Query OK, 0 rows affectedmysql root@localhost:employees> show create table departments_1G;***************************[ 1. row ]***************************Table        | departments_1Create Table | CREATE TABLE `departments_1` (  `dept_no` char(4) NOT NULL,  `dept_name` varchar(40) DEFAULT NULL,  PRIMARY KEY (`dept_no`),  KEY `idx_dept_name` (`dept_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in setTime: 0.010s-- 系统表:mysql root@localhost:employees> explain select * from mysql.proxies_priv;+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+| id | select_type | table        | partitions | type   | possible_keys | key    | key_len | ref    | rows | filtered | Extra  |+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+| 1  | SIMPLE      | proxies_priv |      | system |         |  |   |  | 1    | 100.0    |  |+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+1 row in setTime: 0.023s-- 普通表:mysql root@localhost:employees> explain select * from (select * from departments_1 where dept_no = 'd005' limit 1) de;+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref    | rows | filtered | Extra  |+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+| 1  | PRIMARY     |     |      | system |         |   |   |  | 1    | 100.0    |  || 2  | DERIVED     | departments_1 |      | const  | PRIMARY       | PRIMARY | 12      | const  | 1    | 100.0    |  |+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+2 rows in setTime: 0.015s
    1. const:对于主键或者唯一索引键的等值查询,只返回一行数据。
    mysql root@localhost:employees> explain select * from departments_1 where dept_no = 'd005';+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+| 1  | SIMPLE      | departments_1 |      | const | PRIMARY       | PRIMARY | 12      | const | 1    | 100.0    |  |+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+1 row in setTime: 0.018s
    1. eq_ref:对于前表的每一行数据,都只能匹配当前表唯一一行数据。除了 system 与 const 之外这是最好的一种连接查询类型,主键或者是非空唯一索引的所有部分都可以在连接时被使用,通常使用的是'='操作符,比较值可以是一个常量,也可以是一个在该表之前读取该表的字段表达式。
    explain select * from departments d,departments_1 d1 where d.dept_no = d1.dept_no;+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+| id | select_type | table | partitions | type   | possible_keys | key           | key_len | ref                  | rows | filtered | Extra      |+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+| 1  | SIMPLE      | d1    |      | index  | PRIMARY       | idx_dept_name | 123     |                | 1    | 100.0    | Using index|| 1  | SIMPLE      | d     |      | eq_ref | PRIMARY       | PRIMARY       | 12      | employees.d1.dept_no | 1    | 100.0    |      |+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+2 rows in setTime: 0.037s
    1. ref:对于前表的每一行数据,都从当前表读取所有匹配索引值的行。与 eq_ref 相比,连接查询字段不是主键或者唯一索引,又或者是复合索引的部分左前缀,如果连接查询匹配的是少量几行数据,ref 是个不同错的选择,通常使用的运算符是'='、'<='或者'>='等。
    mysql root@localhost:employees> explain select * from dept_emp where dept_no ='d005';+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra  |+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+| 1  | SIMPLE      | dept_emp |      | ref  | dept_no       | dept_no | 12      | const | 148054 | 100.0    |  |+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+1 row in setTime: 0.059smysql root@localhost:employees> explain select * from dept_emp d,departments_1 d1 where d.dept_no = d1.dept_no;+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                  | rows  | filtered | Extra  |+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+| 1  | SIMPLE      | d1    |      | ALL  |         |   |   |                | 1     | 100.0    |  || 1  | SIMPLE      | d     |      | ref  | dept_no       | dept_no | 12      | employees.d1.dept_no | 41392 | 100.0    |  |+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+2 rows in setTime: 0.012s
    1. ref_or_null:同ref类型,但是包含了对NULL值的搜索。
    mysql root@localhost:employees> explain select dept_name from departments_1 where dept_name = 'd005' or dept_name is null;+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+| id | select_type | table         | partitions | type        | possible_keys | key           | key_len | ref   | rows | filtered | Extra                   |+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+| 1  | SIMPLE      | departments_1 |      | ref_or_null | idx_dept_name | idx_dept_name | 123     | const | 2    | 100.0    | Using where; Using index |+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+1 row in setTime: 0.011s
    1. index_merge:使用了索引合并优化进行查询。如果查询指定条件涉及对多个索引的使用时,会将多个索引合并操作。
    mysql root@localhost:employees> explain select * from dept_emp where emp_no = 10001 or dept_no = (select dept_no from departments_1);+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+| id | select_type | table         | partitions | type        | possible_keys   | key             | key_len | ref    | rows   | filtered | Extra                                    |+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+| 1  | PRIMARY     | dept_emp      |      | index_merge | PRIMARY,dept_no | PRIMARY,dept_no | 4,12    |  | 148055 | 100.0    | Using union(PRIMARY,dept_no); Using where || 2  | SUBQUERY    | departments_1 |      | index       |           | idx_dept_name   | 123     |  | 1      | 100.0    | Using index                               |+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+2 rows in setTime: 0.014s
    1. range:使用索引扫描条件指定范围内的数据。常用的操作符有 '>'、'
    mysql root@localhost:employees> explain select de.* from dept_emp de,departments_1 d where de.dept_no = d.dept_no and de.emp_no < 10010;+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type  | possible_keys   | key           | key_len | ref    | rows | filtered | Extra                                             |+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+| 1  | SIMPLE      | d     |      | index | PRIMARY         | idx_dept_name | 123     |  | 1    | 100.0    | Using index                                       || 1  | SIMPLE      | de    |      | range | PRIMARY,dept_no | PRIMARY       | 4       |  | 9    |  12.5    | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+2 rows in setTime: 0.019s
    1. index:使用索引全扫描。类似于全表扫描,只是扫描对象是索引,出现于以下两种情况:
    • 如果索引是覆盖索引,即索引包含查询所需要的所有表数据,就只扫描索引,并且在 Extra 中出现 Using index。通常情况下扫描索引比打描表要更快,因为索引一般比表来的小;
    • 全表扫描采用索引的顺序来读取数据,本质上还是全表扫描,并且在 Extra 中不会出现 Using index,避免再进行排序消耗性能,因为索引本身就是排序好的。
    mysql root@localhost:employees> explain select dept_name from departments_1;+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+| id | select_type | table         | partitions | type  | possible_keys | key           | key_len | ref    | rows | filtered | Extra       |+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+| 1  | SIMPLE      | departments_1 |      | index |         | idx_dept_name | 123     |  | 1    | 100.0    | Using index |+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+1 row in setTime: 0.020s
    1. all:使用全表扫描。
    mysql root@localhost:employees> drop index idx_dept_name on departments_1;Query OK, 0 rows affectedTime: 0.052smysql root@localhost:employees> explain select * from departments_1;+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+| id | select_type | table         | partitions | type | possible_keys | key    | key_len | ref    | rows | filtered | Extra  |+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+| 1  | SIMPLE      | departments_1 |      | ALL  |         |  |   |  | 1    | 100.0    |  |+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+1 row in setTime: 0.018s

    通过以上各种主要类型的分析,可以总结出各个类型性能排序(从左到右性能从高到低):

    system > const > eq_ref > ref > range > index > all

    possible_keys

    显示了 MySQL 在查找当前表中数据的时候可能使用到的索引,如果该字段值为 NULL,则表明没有相关索引可用。

    key

    显示了 MySQL 在实际查找数据时决定使用的索引,如果该字段值为 NULL,则表明没有使用索引。

    key_len

    显示了 MySQL 实际使用索引的键大小,单位字节。可以通过 key_len 的大小判断评估复合索引使用了哪些部分,如果 key 字段值为 NULL,则 key_len 的值也为 NULL。

    几种常见字段类型索引长度大小如下,假设字符编码为 UTF8:

    • 字段属性是否允许 NULL,如果允许 NULL,则需要额外增加一个字节;
    • 字符型:
      • char(n):3n个字节
      • varchar(n):3n+2个字节
    • 数值型:
      • tinyint:1 个字节
      • int:4 个字节
      • bigint:8 个字节
    • 时间型:
      • 1~2位:1 个字节
      • 3~4位:2 个字节
      • 5~6位:3 个字节
      • date:3 个字节
      • datetime:5 个字节+秒精度字节
      • timestamp:4 个字节+秒精度字节
      • 秒精度字节(最大 6 位):

    ref

    显示哪些常量或者字段被用于查询索引列键值,以获取表中数据行。

    1. 如果是常量等值查询,则显示为 const;
    2. 如果是连接查询,则被驱动表的该字段会显示驱动表的所关联字段;
    3. 如果条件当中使用函数表达式,或者值导致条件字段发生隐式转换,这里显示为 func。
    mysql root@localhost:employees> explain select * from departments d,departments_1 d1 where d.dept_no = d1.dept_no;+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra  |+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+| 1  | SIMPLE      | d1    |      | ALL    | PRIMARY       |   |   |                | 1    | 100.0    |  || 1  | SIMPLE      | d     |      | eq_ref | PRIMARY       | PRIMARY | 12      | employees.d1.dept_no | 1    | 100.0    |  |+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+2 rows in setTime: 0.038s

    rows

    显示预估需要查询的行数。对 InnoDB 表来说这是个预估值,并非是个准确值。

    filtered

    显示按表条件过滤的表行的估计百分比。

    Extra

    显示查询时的额外信息。常见的有如下几种:

    1. Using index

    仅查询索引树就可以获取到所需要的数据行,而不需要读取表中实际的数据行。通常适用于 select 字段就是查询使用索引的一部分,即使用了覆盖索引。

    mysql root@localhost:employees> explain select dept_name from departments_1;+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+| id | select_type | table         | partitions | type  | possible_keys | key           | key_len | ref    | rows | filtered | Extra       |+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+| 1  | SIMPLE      | departments_1 |      | index |         | idx_dept_name | 123     |  | 1    | 100.0    | Using index |+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+1 row in setTime: 0.015s
    1. Using index condition

    显示采用了Index Condition Pushdown (ICP) 特性通过索引去表中获取数据。关于ICP特性可以参考官方文档:Index Condition Pushdown Optimization。简单说法如下:

    • 如果开启 ICP 特性,部分 where 条件部分可以下推到存储引擎通过索引进行过滤,ICP 可以减少存储引擎访问基表的次数;
    • 如果没有开启 ICP 特性,则存储引擎根据索引需要直接访问基表获取数据并返回给 server 层进行 where 条件的过滤。
    -- employees表创建复合索引idx_birth_hiremysql root@localhost:employees> create index idx_birth_hire on employees(birth_date,hire_date);Query OK, 0 rows affectedTime: 0.768smysql root@localhost:employees> explain select * from employees where birth_date = '1960-01-01' and hire_date > '1980-01-01';+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows | filtered | Extra                |+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+| 1  | SIMPLE      | employees |      | range | idx_birth_hire | idx_birth_hire | 6       |  | 63   | 100.0    | Using index condition |+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+1 row in setTime: 0.016s
    1. Using index for group-by

    跟 Using index 访问表的方式类似,显示 MySQL 通过索引就可以完成对 GROUP BY 或 DISTINCT 字段的查询,而无需再访问表中的数据。

    mysql root@localhost:employees> explain select distinct dept_no from dept_emp;+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref    | rows | filtered | Extra                    |+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+| 1  | SIMPLE      | dept_emp |      | range | PRIMARY,dept_no | dept_no | 12      |  | 9    | 100.0    | Using index for group-by |+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+1 row in setTime: 0.020s
    1. Using where

    显示 MySQL 通过索引条件定位之后还需要返回表中获得所需要的数据。

    mysql root@localhost:employees> explain select * from employees where birth_date < '1970-01-01';+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+| id | select_type | table     | partitions | type | possible_keys  | key    | key_len | ref    | rows   | filtered | Extra       |+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+| 1  | SIMPLE      | employees |      | ALL  | idx_birth_hire |  |   |  | 299512 | 50.0     | Using where |+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+1 row in setTime: 0.016s
    1. Impossible WHERE

    where 子句的条件永远都不可能为真。

    mysql root@localhost:employees> explain select * from employees where 1 = 0;+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------+| id | select_type | table  | partitions | type   | possible_keys | key    | key_len | ref    | rows   | filtered | Extra            |+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------+| 1  | SIMPLE      |  |      |  |         |  |   |  |  |    | Impossible WHERE |+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------+1 row in setTime: 0.015s
    1. Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

    在表联接过程当中,将先前表的部分数据读取到 join buffer 缓冲区中,然后从缓冲区中读取数据与当前表进行连接。

    主要有两种算法:Block Nested Loop和Batched Key Access,关于这两种算法说明可以参考官方文档:Block Nested-Loop and Batched Key Access Joins,也可以参考另一篇博文说明:MySQL 查询优化之 Block Nested-Loop 与 Batched Key Access Joins。

    -- Block Nested Loopmysql root@localhost:employees> explain select * from employees e,dept_emp d where e.emp_no > 10001 and e.emp_no <> d.emp_no;+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra                                             |+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+| 1  | SIMPLE      | e     |      | range | PRIMARY       | PRIMARY | 4       |  | 149756 | 100.0    | Using where                                       || 1  | SIMPLE      | d     |      | ALL   |         |   |   |  | 331143 |  90.0    | Using where; Using join buffer(Block Nested Loop) |+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+2 rows in setTime: 0.020s-- Batched Key Accessmysql root@localhost:employees> explain SELECT /*+ bka(a)*/ a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra                                 |+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+| 1  | SIMPLE      | b     |      | ALL  |          |          |   |                 | 331143 | 100.0    |                                 || 1  | SIMPLE      | a     |      | ref  | idx_birth_hire | idx_birth_hire | 3       | employees.b.from_date | 63     | 100.0    | Using join buffer (Batched Key Access) |+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+2 rows in setTime: 0.014s
    1. Using MRR

    读取数据采用多范围读 (Multi-Range Read) 的优化策略。关于MRR特性也可以参考官方文档:Multi-Range Read Optimization

    mysql root@localhost:employees> set optimizer_switch='mrr=on,mrr_cost_based=off';Query OK, 0 rows affectedTime: 0.001smysql root@localhost:employees> explain select * from employees where birth_date = '1970-01-01' and hire_date > '1990-01-01';+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows | filtered | Extra                           |+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+| 1  | SIMPLE      | employees |      | range | idx_birth_hire | idx_birth_hire | 6       |  | 1    | 100.0    | Using index condition; Using MRR |+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+1 row in setTime: 0.014s
    1. Range checked for each record (index map: N)

    MySQL 在获取数据时发现在没有索引可用,但当获取部分先前表字段值时发现可以采用当前表某些索引来获取数据。index map展示的是一个掩码值,如 index map:0x19,对应二进制值为 11001,表示当前表索引编号为 1、4 和 5 号索引可能被用来获取数据,索引编号通过 SHOW INDEX 语句获得。

    mysql root@localhost:employees> explain select * from employees e,dept_emp d where e.emp_no > d.emp_no;+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra                                         |+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+| 1  | SIMPLE      | d     |      | ALL  | PRIMARY       |  |   |  | 331143 | 100.0    |                                         || 1  | SIMPLE      | e     |      | ALL  | PRIMARY       |  |   |  | 299512 |  33.33   | Range checked for each record (index map: 0x1) |+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+2 rows in setTime: 0.038s
    1. Select tables optimized away

    MySQL 优化器能够确定以下两点:

    • 最多只有一行记录被返回;
    • 为了获取这一行数据,有一定的结果集需要获取。

    当语句在优化器阶段过程中可以获取查询结果(如获取行数,只需要读取相应索引数据),而无需再返回表中查询数据,可能会出现 Select tables optimized away。例如针对 MyISAM 引擎的表,使用 select count(*) 获取表的总行数,而且又没有 where 子句或者条件总是为真,也没有 GROUP BY 子句时,其实就包含了以上的条件且隐式含有 GROUP BY 分组的效果。

    -- 创建 MyISAM 引擎的 employees 表mysql root@localhost:employees> create table employees_myisam like employees;Query OK, 0 rows affectedTime: 0.040smysql root@localhost:employees> insert into employees_myisam select * from employees;Query OK, 300024 rows affectedTime: 5.023smysql root@localhost:employees> alter table employees_myisam engine=MyISAM;Query OK, 300024 rows affectedTime: 1.515s-- 获取执行 count(*) 查询行数执行计划mysql root@localhost:employees> explain select count(*) from employees_myisam;+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+| id | select_type | table  | partitions | type   | possible_keys | key    | key_len | ref    | rows   | filtered | Extra                        |+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+| 1  | SIMPLE      |  |      |  |         |  |   |  |  |    | Select tables optimized away |+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+1 row in setTime: 0.024s
    1. Using temporary

    MySQL 需要创建临时表来存放查询结果集。通常发生在有 GROUP BY 或 ORDER BY 子句的语句当中。

    mysql root@localhost:employees> explain select hire_date from employees group by hire_date;+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows   | filtered | Extra                          |+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+| 1  | SIMPLE      | employees |      | index | idx_birth_hire | idx_birth_hire | 6       |  | 299512 | 100.0    | Using index; Using temporary; Using filesort |+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+1 row in setTime: 0.018s
    1. Using filesort

    MySQL 需要对获取的数据进行额外的一次排序操作,无法通过索引的排序完成。通常发生在有 ORDER BY 子句的语句当中。

    mysql root@localhost:employees> explain select * from employees order by hire_date;+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra          |+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+| 1  | SIMPLE      | employees |      | ALL  |         |  |   |  | 299512 | 100.0    | Using filesort |+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+1 row in setTime: 0.015s

    总结

    以上内容总结了 MySQL 获取执行计划 explain 命令执行时输出的主要字段说明,还有许多未仔细说明的参数和选项,以后还需多多实践总结。可以看出 explain 命令输出内容当中比较重要的是:

    1. type:展示了表的查询/连接类型,体现查询效率;
    2. key/key_len:实际使用了什么索引,使用了哪些部分索引;
    3. Extra:对执行计划步骤额外的说明,采用了哪些查询特性。
    展开全文
  • 发现问题 如果你docker容器中没有vi、nano等软件,你需要使用apt-get install nano。...解决问题 在容器命令行,清空/etc/apt/sources.list文件。 # echo > /etc/apt/sources.list 向/etc/apt/sources.
  • 公众号关注「奇妙 Linux 世界」设为「星标」,每天带你玩转 Linux !MySQL explain 命令语句提供了如何执行 SQL 语句信息,解析 SQL 语句的执行计划并...
  • 今天,在测试一个SQL语句,是EF自动生成,发现很奇怪事情:SQL 加了TOP(20)之后,速度了很多,变成36秒,如果没有TOP(20),只需要2秒,查看执行计划,发现变成了全表扫描,但是索引都建立了啊,为什么会这样呢...
  • 执行SELECT * FROM tablename;时用时(484 ms), 当加上一个where条件,查询就变得很: SELECT * FROM tablename where type=1; (18.549 sec) [type为int类型] MYSQL加上where条件就变得很。请问这是为...
  • Mac自带Spotlight聚焦搜索功能是很强大,可以通过这个功能搜索寻找好久文件,不过 Spotlight 有时也会出问题,比如查找速度变,或者某些文件明明在硬盘上,但就是搜不到。怎么办??大家可以试试这种方法: ...
  • 首先有个暴力方法我把sql语句放navicat一个一个尝试肯定能找到问题语句但是这个肯定不是面试官想要答案,其实他想知道就是你遇到过这种问题没有,然后你是怎么解决的。 Mysql 是有个查询但是默认是关闭状态...
  • 如何解决npm被墙的问题安装淘宝的cnpm npm存储包文件的服务器在国外,有时候会被墙,速度很,所以我们需要解决这个问题。 http://npm.taobao.org/淘宝的开发团队把npm在国内做了一个备份。 安装淘宝的cnpm npm ...
  • mysql 的慢日志对系统性能影响和作用,并说明怎么去定位sql,并根据日志相关特性得出相应优化...开启查询目的是分析sql 来优化sql语句,从而解决慢sql引起各种问题。1.开启查询日志功能很简单,在...
  • 解决问题,需要做两步:1.使用多线程。在点击打印时,创建新线程,在新线程中执行打印过程。 2.分块打印。对于比较大图片,应该把图片分隔成小块,分块打印。
  • mysql 的慢日志对系统性能影响和作用,并说明怎么去定位sql,并根据日志相关特性得出相应优化思路日志带来直接...开启查询目的是分析sql 来优化sql语句,从而解决慢sql引起各种问题。1.开启...
  • 如果小循环中执行的函数比较耗时话可以考虑生产者-消费者模型import randomfrom threading import Threadfrom Queue import Queueresqueue = Queue()aqueue = Queue()bqueue = Queue()cqueue = Queue()def ...
  • Explain命令在解决数据库性能上是第一推荐使用命令大部分性能问题可以通过此命令来简单的解决Explain可以用来查看SQL语句的执行效果,可以帮助选择更好索引和优化查询语句,写出更好优化语句。使用方法:...
  • 如何解决npm被墙问题

    2021-04-13 11:51:34
    npm存储包文件服务器在国外,有时候会被墙,速度很,所以需要解决这个问题。 https://developer.aliyun.com/mirror/NPM?from=tnpm淘宝开发团队把npm在国内做了一个镜像(也就是一个备份)。 安装淘宝cnpm...
  • 如何解决连接数一直上涨的问题

    千次阅读 2019-08-28 10:19:45
    时候数据库连接数一直上涨,可能原因是并发连接突然增加,有sql,连接不释放,前2中原因在执行完后连接数最终是会下降,但是最后一种情况,连接数是一直会持续上涨,最终连接满了,导致报错,怎么去查找...
  • 放在事务(独立工作单元)中多个操作, 要么全部执行成功, 要么全部执行失败。不免俗套, 这还是通过最经典银行转账应用来解释一下假设有两个角色 'Iron Man'(余额500), 'Wolverine'(余额15), 现在 Iron Man 通过该...
  • 如何在MySQL语句中找到低效率执行的SQL语句呢?这个问题困扰着一批又一批程序猿。什么是查询?当SQL语句查询返回结果速度超过了规定时间(即通常为1S),则规定该查询语句为查询,查询会导致整个数据库...
  • 最近将自己Sping boot项目部署到Tomcat后,每次通过ip访问网站都要加载很久才可以...如何解决呢? 只需在你阿里云服务器上按照一个工具即可,然后每次要启动Tomcat时运行执行语句即可 先安装 :yum install -y rng-t...
  • 项目中的订单表,在高并发更新订单数据的时候,会造成mysql执行缓慢,甚至直接造成服务器504错误! 怀疑是mysql的问题,登录阿里云RDS查看mysql...这样会解决并发更新慢的问题吗?或者有什么更好的办法吗?请指点!
  • 节点之所以实例化java chaincode比较慢,是因为java一般用gradle项目或是maven项目, 而这两种项目在实例化时需要下载相关的依赖jar,主要是这一步执行较慢,改善问题的关键就在于如何解决下载jar慢的问题,将项目...
  • 在项目中经常要动态添加iframe,然后再对添加iframe进行相关操作,有时候会遇到iframe加载很什么原因呢,该如何解决呢?带着这个问题一起通过本文学习,寻找答案吧!aaa.htmlaaaalert(bbb.document.all.txt....
  • 或 装配期间内存不足 在解决问题之前,您应该先执行以下步骤:1:检查系统可用内存量首先,请检查计算机上安装内存 (RAM)。出现内存不足错误消息原因是,COMSOL 向操作系统请求内存多于计算机上可用...
  • MySQL实战进阶 15 - 查一行执行慢、被锁住的案例。 总结: ...这个问题的具体分析下面一篇单独做解释(由于幻读产生的问题,以及如何解决)。 ...
  • 万次对数据库查询操作 效率非常如何解决? 现在是这样 我需要批量导入 用户:(导入内容为:用户编号) 导入同时,对用户进行业务校验,通过校验就可以入库。 每一次校验都得到数据库里取相关...
  • map /reduce程序执行时,reduce节点大部分执行完毕,但是有一个或者几个reduce节点运行很,导致整个程序处理时间很长,这是因为某一个key条数比其他key多很多(有时是百倍或者千倍之多),这条key所在reduce...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 476
精华内容 190
关键字:

如何解决执行慢的问题