MySQL优化之如何查找SQL效率低的原因

查询到效率低的 SQL 语句 后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序，比如我们想计算 2006 年所有公司的销售额，需要关联 sales 表和 company 表，并且对 profit 字段做求和（ sum ）操作，相应 SQL 的执行计划如下：
mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
2 rows in set (0.00 sec)

每个列的解释如下：

•select_type ：表示 SELECT 的 类型，常见的取值有 SIMPLE （简单表，即不使用表连接或者子查询）、 PRIMARY （主查询，即外层的查询）、 UNION （ UNION 中的第二个或者后面的查询语句）、 SUBQUERY （子查询中的第一个 SELECT ）等。

•table ：输出结果集的表。

•type ：表示表的连接类型，性能由好到差的连接类型为 system （表中仅有一行，即常量表）、 const （单表中最多有一个匹配行，例如 primary key 或者 unique index ）、 eq_ref （对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用 primary key 或者 unique index ）、 ref （与 eq_ref 类似，区别在于不是使用 primary key 或者 unique index ，而是使用普通的索引）、 ref_or_null （ 与 ref 类似，区别在于条件中包含对 NULL 的查询 ） 、 index_merge ( 索引合并优化 ) 、 unique_subquery （ in 的后面是一个查询主键字段的子查询）、 index_subquery （ 与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）、 range （单表中的范围查询）、 index （对于前面的每一行，都通过查询索引来得到数据）、 all （对于前面的每一行，都通过全表扫描来得到数据）。

•possible_keys ：表示查询时，可能使用的索引。
•key ：表示实际使用的索引。
•key_len ：索引字段的长度。
•rows ：扫描行的数量。
•Extra ：执行情况的说明和描述。

在上面的例子中，已经可以确认是 对 a 表的全表扫描导致效率的不理想，那么 对 a 表的 year 字段创建索引，具体如下：

mysql> create index idx_sales_year on sales(year);
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
创建索引后，这条语句的执行计划如下：
mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: idx_sales_year
key: idx_sales_year
key_len: 4
ref: const
rows: 3
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
2 rows in set (0.00 sec)

可以发现建立索引后对 a 表需要扫描的行数明显减少（从全表扫描减少到 3 行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显，使用索引优化 sql 是优化问题 sql 的一种常用基本方法，在后面的章节中我们会具体介绍如何使索引来优化 sql 。

赞 (0)

相关推荐

  • MySQL大表中重复字段的高效率查询方法

    MySQL大表重复字段应该如何查询到呢?这是很多人都遇到的问题,下面就教您一个MySQL大表重复字段的查询方法,供您参考. 数据库中有个大表,需要查找其中的名字有重复的记录id,以便比较.如果仅仅是查找数据库中name不重复的字段,很容易 复制代码 代码如下: SELECT min(`id`),`name` FROM `table` GROUP BY `name`; 但是这样并不能得到说有重复字段的id值.(只得到了最小的一个id值)查询哪些字段是重复的也容易  复制代码 代码如下: SELEC

  • 查询mysql中执行效率低的sql语句的方法

    一些小技巧1. 如何查出效率低的语句?在MySQL下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的SQL语句.你也可以在启动配置文件中修改long query的时间,如: 复制代码 代码如下: # Set long query time to 8 seconds    long_query_time=8 2. 如何查询某表的索引?可使用SHOW INDEX语句,如: 复制代码 代码如下

  • MySQL中使用or、in与union all在查询命令下的效率对比

    MySQL中使用or、in与union all在查询命令下的效率对比

    OR.in和union all 查询效率到底哪个快? 网上很多的声音都是说union all 快于 or.in,因为or.in会导致全表扫描,他们给出了很多的实例. 但真的union all真的快于or.in? EXPLAIN SELECT * from employees where employees.first_NAME ='Georgi' UNION ALL SELECT * from employees where employees.first_NAME ='Bezalel' 这条语

  • mysql in语句子查询效率慢的优化技巧示例

    表结构如下,文章只有690篇. 文章表article(id,title,content) 标签表tag(tid,tag_name) 标签文章中间表article_tag(id,tag_id,article_id) 其中有个标签的tid是135,查询标签tid是135的文章列表. 690篇文章,用以下的语句查询,奇慢: select id,title from article where id in( select article_id from article_tag where tag_id=

  • C++操作MySQL大量数据插入效率低下的解决方法

    通常来说C++操作MySQL的时候,往Mysql中插入10000条简单数据,速度非常缓慢,居然要5分钟左右, 而打开事务的话,一秒不到就搞定了! 具体实现代码如下: #include <iostream> #include <winsock2.h> #include <string> #include "mysql.h" #pragma comment(lib, "libmysql.lib"); using namespace s

  • MySQL优化之如何查找SQL效率低的原因

    查询到效率低的 SQL 语句 后,可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序,比如我们想计算 2006 年所有公司的销售额,需要关联 sales 表和 company 表,并且对 profit 字段做求和( sum )操作,相应 SQL 的执行计划如下: mysql> explain select sum(profit) from sales a,company b where a

  • MySQL优化之如何了解SQL的执行频率

    show [session|global] status 可以根据需要加上参数" session "或者" global "来显示 session 级(当前连接)的统计结果和 global 级(自数据库上次启动至今)的统计结果.如果不写,默认使用参数是" session ". Com_xxx 表示每个 xxx 语句执行的次数,我们通常比较关心的是以下几个统计参数 : •Com_select :执行 select 操作的次数,一次查询只累加 1 .

  • SELECT * 效率低原理解析

    目录 效率低的原因 索引知识延申 联合索引的优势 1) 减少开销 2)覆盖索引 3)效率高 4)索引是建的越多越好吗 效率低的原因 无论在工作还是面试中,关于SQL中不要用“SELECT *”,都是大家听烂了的问题,虽说听烂了,但普遍理解还是在很浅的层面,并没有多少人去追根究底,探究其原理. 先看一下最新<阿里java开发手册(泰山版)>中 MySQL 部分描述: [强制]在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明.说明: 增加查询分析器解析成本. 增减字段容易

  • 查找MySQL中查询慢的SQL语句方法

    查找MySQL中查询慢的SQL语句方法

    如何在mysql查找效率慢的SQL语句呢?这可能是困然很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL .下面介绍MySQL中如何查询慢的SQL语句 一.MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句 1,slow_query_log 这

  • MySQL优化之如何写出高质量sql语句

    前言 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂.这篇文章就来给大家详细介绍了26条优化建议,下面来一起看看吧 1. 查询SQL尽量不要使用全查 select *,而是 select + 具体字段. 反例: select * from student; 正例: select id,name, age from student; 理由: 只取需要的字段,可以节省资源.减少CPU和IO以及网络开销. select * 进行查询时,无法使用到覆盖索引,就会

  • MySQL优化SQL语句的技巧

    在面对不够优化.或者性能极差的SQL语句时,我们通常的想法是将重构这个SQL语句,让其查询的结果集和原来保持一样,并且希望SQL性能得以提升.而在重构SQL时,一般都有一定方法技巧可供参考,本文将介绍如何通过这些技巧方法来重构SQL. 一.分解SQL 有时候对于一个复杂SQL,我们首先想到的是是否需要将一个复杂SQL分解成多个简单SQL,来完成相同业务处理结果. 在以前,大家总是强调需要数据库层来完成尽可能的工作,这也就不难理解在一些老的产品.项目中时常会看见很多超级复杂.超级长的SQL语句,这

  • PHP数据库编程之MySQL优化策略概述

    PHP数据库编程之MySQL优化策略概述

    本文简单讲述了PHP数据库编程之MySQL优化策略.分享给大家供大家参考,具体如下: 前些天看到一篇文章说到PHP的瓶颈很多情况下不在PHP自身,而在于数据库.我们都知道,PHP开发中,数据的增删改查是核心.为了提升PHP的运行效率,程序员不光需要写出逻辑清晰,效率很高的代码,还要能对query语句进行优化.虽然我们对数据库的读取写入速度上却是无能为力,但在一些数据库类扩展像memcache.mongodb.redis这样的数据存储服务器的帮助下,PHP也能达到更快的存取速度,所以了解学习这些扩

  • MySQL优化之缓存优化（续）

    MySQL 内部处处皆缓存,等什么时候看了MySQL的源码,再来详细的分析缓存的是如何利用的.这部分主要将各种显式的缓存优化: 查询缓存优化 结果集缓存 排序缓存 join 连接缓存 表缓存Cache 与表结构定义缓存Cache 表扫描缓存buffer MyISAM索引缓存buffer 日志缓存 预读机制 延迟表与临时表 1.查询缓存优化 查询缓存不仅将查询语句结构缓存起来,还将查询结果缓存起来.一段时间内,如果是同样的SQL,则直接从缓存中读取结果,提高查找数据的效率.但当缓存中的数据与硬盘中

  • MySQL优化之Index Merge的使用

    目录 1.前言 2.IndexMerge 2.1Intersection 2.2Union 2.3SortUnion 2.4SortIntersection 3.总结 1. 前言 先问大家一个问题,在不考虑多表联查这种复杂的查询场景下,一个简单的单表查询,MySQL可以同时利用几个索引? ​ 当初我学习MySQL的时候,天真的以为只要把WHERE条件涉及到的列全部加上索引,就可以提升查询速度,这个想法其实大错特错.因为一般情况下,单表查询MySQL只能利用一个索引,比如下面这个查询,假设id是主

随机推荐