MySQL查询语句过程和EXPLAIN语句基本概念及其优化

网站或服务的性能关键点很大程度在于数据库的设计（假设你选择了合适的语言开发框架）以及如何查询数据上。

我们知道MySQL的性能优化方法，一般有建立索引、规避复杂联合查询、设置冗余字段、建立中间表、查询缓存等，也知道用EXPLAIN来查看执行计划。

但对MySQL复杂查询语句执行过程和内部机制，MySQL Optimizer本身所做优化以及查询语句调整对性能所产生的影响及其原因知之甚少。

本文试图对其中的一些关键概念如执行过程、索引使用等做比较深入的探讨，知其然，知其所以然，

这样可以避免在原本通过MySQL简单优化就能获得很好效果的情况下，盲目跟风转向NoSQL存储或者投入资金升级基础设施。

工欲善其事，必先利其器，这里首先介绍MySQL查询语句性能分析工具。

MySQL的EXPLAIN命令是用来分析查询性能的工具，EXPLAIN的输出每一行对应于查询语句中的一张表的执行计划说明，其输出列含义如下表：

上表中type列是表关联类型，常见的有如下类型（按关联查询效率从高到低排列）：

const（常量连接），比如SELECT * FROM user WHERE id=1;
eq_ref（等值引用），比如SELECT * FROM user,card WHERE user.id=card.userid;
ref（引用），用于非唯一索引，比如SELECT * FROM user,card WHERE user.last_name='test';
range（范围），比如SELECT * FROM tbl_name WHERE key_column > 10;
index（索引），根据索引来读取数据，如果索引已包含了查询数据，只需扫描索引树，否则执行全表扫描和All类似；
ALL（所有），全表扫描

key列代表索引（index），rows表示估计会扫描多少行记录，

Extra表示附加信息，常见的有如下几种（也按查询效率从高到低排列）：

Using index：表示使用索引，如果同时出现Using where，代表使用索引来查找读取记录，如果没有Using where，表示索引包含查询数据，无需额外的查找；
Using where：表示条件查询，如果type列是ALL或index，而没有出现该信息，则你有可能在执行错误的查询：返回所有数据；
Using filesort：不是“使用文件索引”的含义！filesort是MySQL所实现的一种排序策略，通常在使用到排序语句ORDER BY的时候，会出现该信息；
Using temporary：表示为了得到结果，使用了临时表，这通常是出现在多表联合查询，结果排序的场合；

如果EXPLAIN出现后面两个信息（Using filesort，Using temporary），而rows又比较大，通常意味着你需要调整查询语句，或者需要添加索引，总之需要尽量消除这两个信息。

下面给出EXPLAIN结果实例（从用户档案表中找出昵称和性别，按用户表中的用户关注者数量排序）：

上面的查询语句就是典型的问题案例，Using filesort和Using temporary的具体含义以及如何优化上述语句放在下一篇文章中结合查询过程和原理来专门讨论。

赞 (0)

相关推荐

  • mysql学习之引擎、Explain和权限的深入讲解

    引擎 简介 Innodb引擎 Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别.该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引.但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表.当需要使用数据库事务时,该引擎当然是首选.由于锁的粒度更小

  • MySQL中通过EXPLAIN如何分析SQL的执行计划详解

    前言 在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序. 下面分别对EXPLAIN命令结果的每一列进行说明: .select_type:表示SELECT的类型,常见的取值有: 类型 说明 SIMPLE 简单表,不使用表连接或子查询 PRIMARY 主查询,即外层的查询 UNION UNION中的第二个或者后面的查询语句 SUBQUERY 子查询中的第一个 .table:输出结果集的表(表别名) .t

  • MySQL中执行计划explain命令示例详解

    MySQL中执行计划explain命令示例详解

    前言 explain命令是查看查询优化器如何决定执行查询的主要方法. 这个功能有局限性,并不总会说出真相,但它的输出是可以获取的最好信息,值得花时间去了解,因为可以学习到查询是如何执行的. 调用EXPLAIN 在select之前添加explain,mysql会在查询上设置一个标记,当执行查询计划时,这个标记会使其返回关于执行计划中每一步的信息,而不是执行它. 它会返回一行或多行信息,显示出执行计划中的每一部分和执行次序. 这是一个简单的explain效果: 在查询中每个表在输出只有一行,如果查询

  • Mysql Explain命令的使用与分析

    Mysql Explain命令的使用与分析

    mysql explain命令用于显示mysql如何使用索引来处理select语句以及连接表.通过该命令可以分析出查询语句或是表结构的性能瓶颈,从而写出性能更好的SQL语句,通过 expalin 命令我们可以得到: 1. 表的读取顺序 2. 表的读取操作的操作类型 3. 哪些索引可以使用 4. 哪些索引被实际使用 5. 表之间的引用 6. 每张表有多少行被优化器查询 explain 的使用方法,在select语句前加上explain就可以了, 如: explain select * form c

  • MySQL查询语句过程和EXPLAIN语句基本概念及其优化

    MySQL查询语句过程和EXPLAIN语句基本概念及其优化

    网站或服务的性能关键点很大程度在于数据库的设计(假设你选择了合适的语言开发框架)以及如何查询数据上. 我们知道MySQL的性能优化方法,一般有建立索引.规避复杂联合查询.设置冗余字段.建立中间表.查询缓存等,也知道用EXPLAIN来查看执行计划. 但对MySQL复杂查询语句执行过程和内部机制,MySQL Optimizer本身所做优化以及查询语句调整对性能所产生的影响及其原因知之甚少. 本文试图对其中的一些关键概念如执行过程.索引使用等做比较深入的探讨,知其然,知其所以然, 这样可以避免在原本通

  • mysql 查询指定日期时间内sql语句实现原理与代码

    在数据库教程设计时必须注意时间字段为int(11)这样,保存在数据库的是一个数字型日期时间戳,我们可以用mktime函数求出当前日期的时间戳进行加减就OK了,下面看实例 //一个月 复制代码 代码如下: $lastMonth = mktime(date('h'),date('i'),date('s'),date('m')-1,date('d'),date('y')); $where .= " dtime > $lastMonth"; //三个月 复制代码 代码如下: $lastT

  • 一篇文章弄懂MySQL查询语句的执行过程

    一篇文章弄懂MySQL查询语句的执行过程

    前言 需要从数据库检索某些符合要求的数据,我们很容易写出 Select A B C FROM T WHERE ID = XX  这样的SQL,那么当我们向数据库发送这样一个请求时,数据库到底做了什么? 我们今天以MYSQL为例,揭示一下MySQL数据库的查询过程,并让大家对数据库里的一些零件有所了解. MYSQL架构 mysql架构 MySQL 主要可以分为 Server 层和存储引擎层. Server层 包括连接器.查询缓存.分析器.优化器.执行器等,所有跨存储引擎的功能都在这一层实现,比如存

  • 单个select语句实现MySQL查询统计次数

    单个select语句实现MySQL查询统计次数 单个select语句实现MySQL查询统计次数的方法用处在哪里呢?用处太多了,比如一个成绩单,你要查询及格得人数与不及格的人数,怎么一次查询出来?MySQL查询统计次数简单的语句肯定是这样了: 复制代码 代码如下: select a.name,count_neg,count_plus from    (select count(id) as count_plus,name from score2 where score >=60 group by

  • mysql查询字段类型为json时的两种查询方式

    表结构如下: id varchar(32) info json 数据: id = 1 info = {"age": "18","disname":"小明"} -------------------------------------------- 现在我需要获取info中disanme的值,查询方法有: 1. select t.id,JSON_EXTRACT(t.info,'$.disname') as disname fro

  • MySQL查询指定字段不是数字与逗号的sql

    核心sql语句 MySQL查询不包含数字的语句: SELECT * FROM test WHERE `name` regexp '[^0-9]'; MySQL查询纯数字的语句: SELECT * FROM test WHERE `name` regexp '[0-9]'; 相关文章id,都是数字或者逗号那么下面语句MySQL查询不包含数字与逗号的语句: SELECT * FROM test WHERE `name` regexp '[^0-9,]'; MySQL查询某一列中不是数字 包含数字的列

  • 详解MySQL 查询语句的执行过程

    详解MySQL 查询语句的执行过程

    首先先简单的将一个查询语句背后MySQL做了什么捋一捋: 客户端发送一条查询给服务器. 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果.否则进入下一个阶段. 服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询. 将结果返回给客户端. 接着我们就将这个过程中的这些步骤详细的进行展开. 1.客户端和服务器端之间的通信方式 客户端和服务器之间的通信是一种半双工的通信,即在同一时刻,只能有一方向另一方发送

  • 了解MySQL查询语句执行过程(5大组件)

    了解MySQL查询语句执行过程(5大组件)

    目录 开篇 查询请求的执行流程 MySQL组件定义 连接器 查询缓存 分析器 优化器 逻辑变换 代价优化 执行器 总结 开篇 相信广大程序员朋友经常使用MySQL数据库作为书籍持久化的工具,我们最常使用的就是MySQL中的SQL语句,从客户端向MySQL发出一条条指令,然后获取返回的数据结果进行后面的逻辑处理.尽管大家经常使用SQL语句完成工作,你是否关注过其执行的阶段,利用了哪些技术完成?今天,就带大家一起看看MySQL数据库处理SQL请求的全过程.下面将会讲述如下内容: 查询请求在MySQL

  • MySQL中explain语句的基本使用教程

    MySQL中explain语句的基本使用教程

    一.概述 在 MySQL 中,我们可以使用慢查询日志或者 show processlist 命令等方式定位到执行耗时较长的 SQL 语句,在这之后我们可以通过 EXPLAIN或者 DESC 命令获取 MySQL 如何执行 SELECT 语句 的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序 1.explain语句测试 explain+select语句即 explain 命令的使用方式 2.explain结果中各列的说明 下面,将对这些列逐一进行讲解 二.explain之id列

  • MySQL查询语句大全集锦

    1:使用SHOW语句找出在服务器上当前存在什么数据库: mysql> SHOW DATABASES; 2:2.创建一个数据库MYSQLDATA mysql> CREATE DATABASE MYSQLDATA; 3:选择你所创建的数据库 mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!) 4:查看现在的数据库中存在什么表 mysql> SHOW TABLES; 5:创建一个数据库表 mysql> CREATE TAB

随机推荐