一、序言
作为程序员,难免要和数据库打交道,一般情况下,我们不是DBA ,但是又要写很多SQL,因此SQL效率就成了很大的问题。关于SQL效率优化,除了要掌握一定优化技巧外, 还得有很多经验的积累,但是这里我们可以通过执行计划对SQL进行分析,能快速找到优化的地方,这是一种很不错的方式,介绍给大家,大部分我是翻译而来,原文地址:http://dev.mysql.com/doc/refman/5.6/en/explain-output.html
二、执行计划输出列的含义:
id |
The SELECT identifier |
select_type |
The SELECT type |
table |
The table for the output row |
partitions |
The matching partitions |
type |
The join type |
possible_keys |
The possible indexes to choose |
key |
The index actually chosen |
key_len |
The length of the chosen key |
ref |
The columns compared to the index |
rows |
Estimate of rows to be examined |
filtered |
Percentage of rows filtered by table condition |
Extra |
Additional information |
● id
:
查询的标识,表示在select 执行语句中的顺序(PS:数字越大,优先执行)。如果这行是和其他行合并的结果,这个值可以为null。比如:使用 UNION 关键字,将多个select 的结果合并到一起。
● select_type
:每个select 的类型。
SIMPLE |
简单的 SELECT (没有 使用UNION 或者 子查询(PS:单表查询)) |
PRIMARY |
最外层的Select 作为primary 查询。(PS:含有子查询的情况,但是并不复杂) |
UNION |
从第二个或者在union 之后的select 作为 union 查询
|
DEPENDENT UNION |
从第二个或者在union 之后的select 作为 union 查询, 依赖于外部查询 |
UNION RESULT |
结果集是通过union 而来的,作为... |
SUBQUERY |
第一个查询是子查询 |
DEPENDENT SUBQUERY |
第一个查询是子查询,依赖于外部查询 |
DERIVED |
在from 查询语句中的(派生,嵌套很多)子查询.(PS:递归操作这些子查询) |
MATERIALIZED |
(雾化) 子查询(PS:子查询是个视图?) |
UNCACHEABLE SUBQUERY |
子查询结果不能被缓存, 而且必须重写(分析)外部查询的每一行 |
UNCACHEABLE UNION |
第二个 或者 在UNION 查询之后的select ,属于不可缓存的查询 |
● Table
:输出所用到的表(PS:通过id 联系)
● Type
:连接类型,很重要的分析手段,下面按最优到最差排序:
System:表只有一行(=系统表),const 的特例
const:表查询结果最多只有一行,因为只有一行,该查询优化部分一般是常数。比如根据主键id=1 查询。
比如:
SELECT * FROM tbl_name WHERE primary_key=1;
eq_ref:从当前这个表读出的一行,和前面所有表的行进行组合,这是除了const 和system 外,最好的连接类型,它是用于所有的都是用唯一索引去连接被主键或者不为空的索引。常用=操作符比较索引
比如:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
ref:用于连接非唯一索引的扫描。可以对索引的列使用> = <> 的操作符。
比如:
SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROMref_table
,other_table
WHEREref_table
.key_column_part1
=other_table
.column
ANDref_table
.key_column_part2
=1;
fulltext : 该方式使用的是全文检索
ref or null : 该连接方式像ref,但是包含null 的值 ,该连接类型主要是解决子查询
比如:
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
index_merge:索引合并优化(PS:多个索引条件情况,进行条件的合并优化)
我版本低,没出现。。,可以参考下面解释: http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30; SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col; SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
unique_subquery:这个参照ref,处理子查询
比如:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery:这个和unique_subquery 类似,取代非唯一索引的子查询
比如:
value IN (SELECT key_column FROM single_table WHERE some_expr)
range:只有在range 范围内的都被检索,只用索引才查询哪些行。后面Key 表示你用的那个索引:
比如:
SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
index :这索引连接类型和ALL一样,除了树的索引扫描,分为两种情况: 1.会便利索引树,2.没有索引树,就是ALL 一样。
All:全表扫描,通常是最差的一种查询。
● Extra
:包含mysql 解析查询的额外信息。
Distinct:mysql 查询不同的行,当找到和当前行匹配的时候,就不再搜索了。
FirstMatch(tbl_name) :The semi-join FirstMatch join shortcutting strategy is used for tbl_name
.
Full scan on NULL key:查询分析器无法使用当前索引的一个失败策略。
Impossible HAVING: where 条件总是false,无法筛选任何行
Impossible WHERE noticed after reading const tables:和上面类似
LooseScan:利用索引来扫描一个子查询表可以从每个子查询的值群组中选出一个单一的值。
Not exists:mysql 优化了left join 的查询,
比如:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; 假设t2.id 定义为 not null,这种情况下,Mysql 扫描t1 并且用t1.id 在t2中查找行,如果Mysql 在t2中找到匹配的行,它表明t2.id 不可能为null,因此不会扫描剩下的具有相同id的行,换句话说,t1 中的每一行,mysql 每次都在t2中做一下查询,无论t2 有多少匹配。
Using filesort:无法利用索引完成的排序,比如文件排序
Using index:利用索引树扫描得出结果,不用全部扫描
Using temporary:利用临时表存储结果集,通常查询包含 GROUP BY
and ORDER BY
。
Using where:使用where 限定那些行于下一张表匹配,或者返回到客户端,除非你想要获取or 检查表中所有行,如果extra 的值不是Using where并且连接类型不是all 或者index ,那么你可能有一些错误在你的查询中。
Using join buffer:
Using MRR:有点复杂,
参考:http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html
和:http://blog.csdn.net/zbszhangbosen/article/details/7463394
● Key
:key 这一列表明实际你用的是那一个索引,没有则是null
● Key len
:该列是Mysql 使用key 的长度,没有则为null,文档提示这值能确定你 multiple-part key 中使用的是哪一部分。
● Rows
:表示Mysql 执行语句扫描的行数
● Possible_keys
:表示mysql 找到的这些行数据,在indexes(很多索引)里面的哪一个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。
小结:
1.这个不得不吐槽,翻译太烂了...以至于后面都是自己的理解弄的,建议都去看原文啊,而且5.6+ 变化挺多,有些变化我也没遇到过,请见谅啊。
2.这些简单的说明呢,仅仅是给大家提供一种分析SQL 的途径,也提醒大家不要盲目的根据SQL 去判断效率,当然你经验丰富,就不说啦~。~新手学习。
3.如果你需要更详细的可以用show profile 的东西,能看到更详细的信息,精确度也更高,还有关于一些实战方面的应用,没来得及总结,这个以后再介绍吧。
4.还是请见谅下,不正确的 狗血的地方请指出哦,谢谢啦。
最后分享个不错的mysql 地址:
http://www.mysqlab.net/
http://www.mysqlpub.com/
相关推荐
mysql 的 java 连接库 解压缩mysql-connector-java-5.1.30.zip 将要使用的是mysql-connector-java-5.1.30-bin-g.jar和mysql-connector-java-5.1.30-bin.jar 配置 在C:\Program Files\Java目录下建立mysqlforjdbc...
MySQL explain-MySQL执行计划解读.ppt
mysql-connector-java-5.1.40.tar.gz上传到Linux系统中,然后在Linux系统中新打开一个终端,在终端中执行命令解压缩文件:
yum安装mysql时用到的yum源文件,下载到linux的目录。 然后执行 yum localinstall mysql57-community-release-el7-8.noarch.rpm
MySQL-Front 是一款小巧的管理 Mysql 的应用程序. 主要特性包括多文档界面,语法突出,拖拽方式的数据库和表格,可编辑/可增加/删除的域,可编辑/可插入/删除的记录,可显示的成员,可执行的SQL 脚本,提供与外程序...
Jmeter为了连接Mysql数据库,还需要有个jdbc驱动,mysql-connector-java-5.1.41能执行多条sql语句
进入目录,执行 sudo dpkg -i * 然后报在处理时有错误发生,是因为依赖包还没装好,所以再执行(会弹出框让你输入mysql密码) sudo dpkg -i mysql-server-5.7_5.7.26-0kord0.16.04.1k1_arm64.deb systemctl start...
MySQL-shared-5.6.14-1.el6.x86_64.rpm包,安装时需要和MySQL-server-5.6.14-1.el6.x86_64.rpm一起并执行MySQL-server-5.6.14-1.el6.x86_64.rpm包就可以
Amoeba实现了集中响应请求,依据事先设置的规则,将SQL请求发送到特定数据库上执行。可以实现数据库负载均衡、读写分离、高可用性等功能。 amoeba-mysql.zip压缩包内容: amoeba-mysql-3.0.5-RC-distribution.zip ...
5. 安装 MySQL 服务, 打开Windows命令提示符, 执行命令: mysqld --install MySQL --defaults-file="my.ini" 提示"Service successfully installed."表示成功;如果出现Install/Remove of the Service Denied,一般是...
执行vim ~/.bash_profile 在文件中添加mysql/bin的目录 按i编辑 PATH=$PATH:/usr/local/mysql/bin 按esc,然后输入:wq保存。 最后在命令行输入source ~/.bash_profile 登录 mysql -u root -p 系统自动生成的密码...
JDBC本质 * 官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口 * 各个数据库厂商去实现这套接口,提供数据库驱动jar包 * 我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类
或者关闭mysql服务,然后执行mysqld --skip-grant-tables 重启mysql服务 cmd进入mysql下的bin目录,输入mysql,回车,进入mysql 选择mysql数据库 use mysql 修改root用户密码 update user set authentication_string=...
执行SQL语句,返回执行结果 ResultSet rs = stat.executeQuery(sql); /** * 理解ResultSet结构: * +----+------+-------+ * | id | name | money | 表头 ←—————————— 指向1 rs....
MySQL 是一个多线程的,结构化查询语言(SQL)数据库服务器,MySQL 的执行性能非常高,运行速度非常快,并非常容易使用,是一个非常捧的数据库。 这款软件使用于64位系统。mysql 5.5已经出来有一段时间,性能有明显...
JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java API。
--user mysql 以mysql身份执行脚本 --basedir /usr/local/mysql MySQL软件目录 --datadir /usr/local/mysql/data MySQL数据目录 执行以下命令,创建初始数据库: Java代码 1. cd /usr/local/mysql 2. chown -R mysql...
MySQL Workbench 提供了一些可视化工具来创建、执行和优化 SQL 查询。SQL Editor 具有语法高亮显示、自动填充、SQL 代码段重用和 SQL 执行历史记录等功能。开发人员可以通过 Database Connections Panel 轻松管理...
MySQLConnector/NET是一款数据库的连接类,包含数个类,这些类可用于连接到数据库,执行查询和语句,并管理查询结果。 软件名称:mysql-connector-net 运行环境:Win98/Win2000/WinXP/Win2003/Win7
此版本增强了对MySQL 5.6的支持,并提供了一些新的特性,如多语句执行和可插拔式身份验证。此外,MySQL Connector/J 5.1.25还包括了许多重要的错误修复和性能优化,使得它更加可靠和高效。如果你是Java程序员,使用...