当前位置:首页 > Mysql技术文章 > mysql学习之引擎、Explain和权限的深入讲解

mysql学习之引擎、Explain和权限使用详解

  • 发布时间:
  • 作者:码农之家原创
  • 点击:62

这篇文章主要知识点是关于mysql、引擎、Explain、权限、mysql之explain使用详解(分析索引) 的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下相关资源

PHP和MySQL Web开发

在《PHP和MySQL Web开发》书中,我们将把使用PHP与MySQL的经验和心得体会毫无保留地分享给你,PHP和MySQL是目前热门的两个Web开发工具。

查看详情

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

引擎

简介

Innodb引擎

Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。

MyIASM引擎

MyIASM是MySQL默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。不过和Innodb不同,MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。

主要区别

1、MyIASM是非事务安全的,而InnoDB是事务安全的

2、MyIASM锁的粒度是表级的,而InnoDB支持行级锁

3、MyIASM支持全文类型索引,而InnoDB不支持全文索引

4、MyIASM相对简单,效率上要优于InnoDB,小型应用可以考虑使用MyIASM

5、MyIASM表保存成文件形式,跨平台使用更加方便

应用场景

1、MyIASM管理非事务表,提供高速存储和检索以及全文搜索能力,如果再应用中执行大量select操作,应该选择MyIASM

2、InnoDB用于事务处理,具有ACID事务支持等特性,如果在应用中执行大量insert和update操作,应该选择InnoDB

MySQL Explain详解

在日常工作中,我们会有时会开慢查询去记录一些执行时间比较久的SQL语句,找出这些SQL语句并不意味着完事了,些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描,这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。(QEP:sql生成一个执行计划query Execution plan)

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | servers | ALL | NULL  | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
row in set (0.03 sec)

expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释:

一、 id

     我的理解是SQL执行的顺序的标识,SQL从大到小的执行

1. id相同时,执行顺序由上至下

2. 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

3.id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

二、select_type

      示查询中每个select子句的类型

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT,取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

三、table

显示这一行的数据是关于哪张表的,有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL  | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | <derived3> | system | NULL  | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | t1  | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

四、type

表示MySQL在表中找到所需行的方式,又称“访问类型”。

常用的类型有: ALL, index,  range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)

ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行

index: Full Index Scan,index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行,使用一个索引来选择行

ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system

NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、possible_keys

指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key

key列显示MySQL实际决定使用的键(索引)

如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

不损失精确性的情况下,长度越短越好

八、ref

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

九、rows

 表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

十、Extra

该列包含MySQL解决查询的详细信息,有以下几种情况:

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询

Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

Impossible where:这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

总结:

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• 部分统计信息是估算的,并非精确值

• EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。

MySQL授权管理

语法:

show grants for '用户'@'IP地址';                  -- 查看权限
grant  权限 on 数据库.表 to   '用户'@'IP地址';      -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址';      -- 取消权限

权限参数

all privileges  除grant外的所有权限
            select          仅查权限
            select,insert   查和插入权限
            ...
            usage                   无访问权限
            alter                   使用alter table
            alter routine           使用alter procedure和drop procedure
            create                  使用create table
            create routine          使用create procedure
            create temporary tables 使用create temporary tables
            create user             使用create user、drop user、rename user和revoke  all privileges
            create view             使用create view
            delete                  使用delete
            drop                    使用drop table
            execute                 使用call和存储过程
            file                    使用select into outfile 和 load data infile
            grant option            使用grant 和 revoke
            index                   使用index
            insert                  使用insert
            lock tables             使用lock table
            process                 使用show full processlist
            select                  使用select
            show databases          使用show databases
            show view               使用show view
            update                  使用update
            reload                  使用flush
            shutdown                使用mysqladmin shutdown(关闭MySQL)
            super                  

mysql之explain使用详解(分析索引)

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法,在select语句前加上explain就可以了,如:

explain select * from statuses_status where id=11;

mysql之explain使用详解(分析索引)

explain列的解释

table:显示这一行的数据是关于哪张表的

type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all

possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

key: 实际使用的索引。如果为null,则没有使用索引。很少的情况下,mysql会选择优化不足的索引。这种情况下,可以在select语句中使用use index(indexname)来强制使用一个索引或者用ignore index(indexname)来强制mysql忽略索引

key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

rows:mysql认为必须检查的用来返回请求数据的行数

extra:关于mysql如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是using temporary和using filesort,意思mysql根本不能使用索引,结果是检索会很慢

extra列返回的描述的意义

distinct:一旦mysql找到了与行相联合匹配的行,就不再搜索了

not exists: mysql优化了left join,一旦它找到了匹配left join标准的行,就不再搜索了

range checked for each record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,mysql检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一

using filesort: 看到这个的时候,查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候

using temporary 看到这个的时候,查询需要优化了。这里,mysql需要创建一个临时表来存储结果,这通常发生在对不同的列集进行order by上,而不是group by上

where used 使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型all或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)

system 表只有一行:system表。这是const连接类型的特殊情况

const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为mysql先读这个值然后把它当做常数来对待

eq_ref:在连接中,mysql在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比all更好,因为索引一般小于表数据)

all:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

分析索引分析

在这里对explain的各个字段进行详细的分析,来帮助大家分析自己所写的sql是否最佳的使用了索引。

mysql之explain使用详解(分析索引)

首先是select_type:将select查询分为简单(simple)和复杂两种类型

复杂类型又分为子查询(subquery)和from列表中包含子查询(drived)

simple:

mysql之explain使用详解(分析索引)

drived:

mysql之explain使用详解(分析索引)

就type进行详细的介绍:

System,const,eq_ref,ref,range,index,all
all : 即全表扫描
index : 按索引次序扫描,先读索引,再读实际的行,结果还是全表扫描,主要优点是避免了排序。因为索引是排好的。
range:以范围的形式扫描。

explain select * from a where a_id > 1\G

ref:非唯一索引访问(只有普通索引)

create table a(a_id int not null, key(a_id));
insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> explain select * from a where a_id=1\G

eq_ref:使用唯一索引查找(主键或唯一索引)
const:常量查询

在整个查询过程中这个表最多只会有一条匹配的行,比如主键 id=1 就肯定只有一行,只需读取一次表数据便能取得所需的结果,且表数据在分解执行计划时读取。

mysql之explain使用详解(分析索引)

当结果不是一条时,就会变成index或range等其他类型

system:系统查询

null:优化过程中就已经得到结果,不在访问表或索引

possible_keys:可能用到的索引

key:实际用到的索引

key_line:索引字段最大可能使用长度

ref:

指出对 key 列所选择的索引的查找方式,常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ,即不使用索引时,此值也相应的为 NULL 。

rows:估计需要扫描的行数

Extra:显示以上信息之外的其他信息

Using index

此查询使用了覆盖索引(Covering Index),即通过索引就能返回结果,无需访问表。

若没显示"Using index"表示读取了表数据。

Using where

表示 MySQL 服务器从存储引擎收到行后再进行“后过滤”(Post-filter)。所谓“后过滤”,就是先读取整行数据,再检查此行是否符合 where 句的条件,符合就留下,不符合便丢弃。因为检查是在读取行后才进行的,所以称为“后过滤”。

Using temporary

使用到临时表

建表及插入数据:

create table a(a_id int, b_id int);
insert into a values(1,1),(1,1),(2,1),(2,2),(3,1);
mysql> explain select distinct a_id from a\G

Extra: Using temporary

MySQL 使用临时表来实现 distinct 操作。

Using filesort

若查询所需的排序与使用的索引的排序一致,因为索引是已排序的,因此按索引的顺序读取结果返回,否则,在取得结果后,还需要按查询所需的顺序对结果进行排序,这时就会出现 Using filesort 。

select * from a order by id;

对于没有索引的列进行order by 就会出现filesort

以上就是本次给大家分享的关于Mysql的全部知识点内容总结,大家还可以在下方相关文章里找到MySQL升级的实战详解、 Mysql存储过程和存储函数用、 MySQL截取字符串函数sql语句、 等mysql文章进一步学习,感谢大家的阅读和支持。

上一篇:mysql将表名改成大写的实例

下一篇:windows 10下安装mysql 5.7.17的简单笔记

展开 +

收起 -

mysql引擎、Explain和权限 相关内容
MySQL从入门到项目实践

MySQL从入门到项目实践 (超值版)采用基本知识关键运用关键技术高級运用应用领域新项目实践活动构造和循序渐进,由深到精的学习方式开展解读。本书分成6篇29章。最先解读MySQL的安裝与配备

查看详情
MySQL运维内参

《MySQL运维内参:MySQL、Galera、Inception核心原理与最佳实践》是一本介绍MySQL数据库知识的专业书籍,从核心原理到最佳实践,深入浅出、抽丝剥茧地进行讲解,不仅从源码和运维两个角度介绍

查看详情
MySQL技术内幕:InnoDB存储引擎

《MySQL技术内幕:InnoDB存储引擎(第2版)》 由国内资深MySQL专家亲自执笔,国内外多位数据库专家联袂推荐。作为国内唯一一本关于InnoDB的专著,《MySQL技术内幕:InnoDB存储引擎(第2版)》的第1版广受

查看详情
Centos7下安装mysql5.7的步骤 DOCX版说明

一、安裝YUMRepo 1、因为CentOS的yum源中沒有mysql,必须到mysql的安卓下载yumrepo配置文件。 2、随后开展repo的安裝: rpm-ivhmysql57-community-release-el7-9.noarch.rpm 实行进行之后在/etc/yum.repos.d/文件目录下转化成2个repo文档mysql-community.repo mysql-community-source.repo 二、应用yum指令就可以进行安裝 留意:务必进到到/etc/yum.repos

查看详情
PHP与MySQL高性能应用开发

PHP与MySQL高性能应用开发书中不仅讲解了OOP、设计模式,也讲解了如何进行性能优化和调试。甚至,还对于语言之外的代码重构进行了讲解,本书面向的对象是有一定PHP基础,但是又想深入学习的PHP开发者,欢迎免费下载

查看详情
深入理解MariaDB与MySQL

深入理解MariaDB与MySQL 讲解了MariaDB的诞生背景及设计目的,并通过与MySQL的比较使读者轻松理解MariaDB。本书内容涉及MariaDB 5.1~10.0的各个方面,讲解重点在于MariaDB 10.0的各种实用功能。此外,M

查看详情
PHP和MySQL Web开发

《PHP和MySQL Web开发》将PHP开发与MySQL应用相结合,分别对PHP和MySQL做了深入浅出的分析,不仅介绍PHP和MySQL的一般概念,而且对PHP和MySQL的Web应用做了较全面的阐述,并包括几个经典且实用的例子

查看详情
mysql引擎、Explain和权限 学习笔记
网友NO.130751

MySQL查询优化之explain的深入解析

在分析查询性能时,考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作。 一、MySQL 查询优化器是如何工作的 MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试,查询就可以执行地更快。 EXPLAIN 的每个输出行提供一个表的相关信息,并且每个行包括下面的列: 项 说明 id MySQL Query Opti……

网友NO.651791

Mysql中explain作用详解

一、MYSQL的索引 索引(Index):帮助Mysql高效获取数据的一种数据结构。用于提高查找效率,可以比作字典。可以简单理解为排好序的快速查找的数据结构。 索引的作用: 便于查询和排序(所以添加索引会影响where 语句与 order by 排序语句)。 在数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用数据。这样就可以在这些数据结构上实现高级查找算法。这些数据结构就是索引。 索引本身也很大,不可能全部存储在内存中,所以索引往往以索引文件的形式存储在磁盘上。 我们平时所说的索引,如果没有特别指明,一般都是B树索引。(聚集索引、复合索引、前缀索引、唯一索引默认都是B+树索引),除了B树索引还有哈希索引。 优点: A、提高数据检索效率,降低数据库的IO成本 B、通过索引列对数据进行排序,降低了数……

网友NO.175509

mysql explain的用法(使用explain优化查询语句)

首先我来给一个简单的例子,然后再来解释explain列的信息。 表一:catefory 文章分类表: CREATE TABLE IF NOT EXISTS `category` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM INSERT INTO `test`.`category` VALUES (NULL , '分类1'); INSERT INTO `test`.`category` VALUES (NULL , '分类2'); INSERT INTO `test`.`category` VALUES (NULL , '分类3'); 表二:article文章表: CREATE TABLE IF NOT EXISTS `article` ( `aid` int(11) NOT NULL, `cid` int(11) NOT NULL, `content` text NOT NULL, PRIMARY KEY (`aid`), KEY `cid` (`cid`)) ENGINE=MyISAMINSERT INTO `test`.`article` (`aid`, `cid`, `content`) VALUES ('', '7', '码农之家(jb51.net)教程'); 执行explain: EXPLAIN SELECT name, contentFROM category, articleWHERE category.id = article.cid 得到结果: EXPLAIN列的解释: id:选定的执行计划中查询的序列号。表示查询中执行select子句或操作表的顺序,……

网友NO.630336

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

前言 explain命令是查看查询优化器如何决定执行查询的主要方法。 这个功能有局限性,并不总会说出真相,但它的输出是可以获取的最好信息,值得花时间去了解,因为可以学习到查询是如何执行的。 调用EXPLAIN 在select之前添加explain,mysql会在查询上设置一个标记,当执行查询计划时,这个标记会使其返回关于执行计划中每一步的信息,而不是执行它。 它会返回一行或多行信息,显示出执行计划中的每一部分和执行次序。 这是一个简单的explain效果: 在查询中每个表在输出只有一行,如果查询是两个表的联接,那么输出中将有两行。 别名表单算为一个表,因此,如果把一个表与自己联接,输出中也会有两行。 “表”的意义在这里相当广,可以是一个子查询,一个union结果等。 同时 explain有两个变种 EXPLAIN EXTENDED会告诉服务器“逆向编译” 执行计划为……

<
1
>

Copyright 2018-2020 xz577.com 码农之家

本站所有电子书资源不再提供下载地址,只分享来路

免责声明:网站所有作品均由会员网上搜集共同更新,仅供读者预览及学习交流使用,下载后请24小时内删除

版权投诉 / 书籍推广 / 赞助:QQ:520161757