Mysql 执行计划分析

最新推荐文章于 2024-09-20 09:10:20 发布
最新推荐文章于 2024-09-20 09:10:20 发布
阅读量444 收藏
点赞数 1
分类专栏： mysql
原文链接：https://blog.csdn.net/kolewei/article/details/61927918
版权

查询优化

EXPLAIN

在MySQL中可以使用EXPLAIN查看SQL执行计划

字段描述
idselect识别码
select_typeselect语句类型
table查询的表
type表的连接类型
possible_keys可使用的索引
key实际使用索引
key_len使用的索引长度
ref字段或者常量配合查询
rows执行行数
extra查询的详情

select_type

  • SIMPLE : 简单查询, 其中不包括连接查询和子查询

    EXPLAIN SELECT * FROM book;

  • PRIMARY : 主查询(调用子查询的查询方法)

    EXPLAIN SELECT * FROM (SELECT * FROM book) AS BOOK;
    EXPLAIN SELECT * FROM user UNION SELECT * FROM user2;

  • DERIVED : 子查询(调用子查询的子查询方法)

    EXPLAIN SELECT * FROM (SELECT * FROM book) AS BOOK;

  • UNION : 连接查询的第2个或后面的查询语句

    EXPLAIN SELECT * FROM user UNION SELECT * FROM user2;

  • UNION RESULT : 连接查询的结果

  • DEPENDENT UNION : UNION中的第二个或后面的SELECT语句，取决于外面的查询

  • DEPENDENT SUBQUERY : 子查询中的第1个SELECT语句，取决于外面的查询

  • SUBQUERY : 子查询中的第1个SELECT语句

    EXPLAIN SELECT * FROM user WHERE id = (SELECT u_id FROM product WHERE id = ‘2’);

type

表示表的连接类型
以下的连接类型的顺序是从最佳类型到最差类型

  1. system :

    表只有一行记录（等于系统表）。这是 const表连接类型的一个特例

  2. const :

    数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查询，可理解为const是最优化。

  3. eq_ref :

    mysql手册是这样说的:”对于每个来自于前面的表的行组合，从该表中读取一行。
    这可能是最好的联接类型，除了const类型。
    它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY“。eq_ref可以用于使用=比较带索引的列。

    explain select * from user u,user2 u2 where u.id = u2.id;
    // u2 为 ALL； u 为 eq_ref； u 的ref为 u2.id

  4. ref :

    查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。

  5. ref_or_null :

    该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。
    在解决子查询中经常使用该联接类型的优化。

  6. index_merge :

    该联接类型表示使用了索引合并优化方法。
    在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

  7. unique_subquery :

    该类型替换了下面形式的IN子查询的ref:
    — value IN (SELECT primary_key FROM single_table WHERE some_expr)
    unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

  8. index_subquery :

    该联接类型类似于unique_subquery。
    可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:
    — value IN (SELECT key_column FROM single_table WHERE some_expr)

  9. range :

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

  10. index :

    该联接类型与ALL相同,除了只有索引树被扫描(完整的索引表扫描)。
    这通常比ALL快,因为索引文件通常比数据文件小。

  11. ALL :

    对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）

possible_keys

指出MySQL能使用哪个索引在该表中找到行(可多个)。
如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。

// 强制忽略索引
EXPLAIN SELECT * FROM user IGNORE INDEX(age) WHERE age > 10;
// 强制使用索引, 忽略默认索引
EXPLAIN SELECT * FROM user USE INDEX(age) WHERE age > 10;

key

显示MySQL**实际决定使用的键(索引)。(只能有一个)**
如果没有选择索引,键是NULL。

key_len

索引的存储长度, 字节为单位(int 4字节 长度4)
如果键是NULL,则长度为NULL。

ref

显示使用哪个列或常数与key一起从表中选择行。

rows

显示MySQL认为它执行查询时必须检查的行数

Extra

解决查询的详细信息

  • Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
  • Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
  • range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
  • Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
  • Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  • Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
  • Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
  • Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
  • Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

索引

索引可以提供查询的速度
但并不是使用了带有索引的字段查询都会生效

like

匹配字符串的第一个字符为“%”，索引不起作用。
只有“%”不在第一个位置，索引才会生效。

联合索引的查询

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。
对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。
例:

如果id1(主键1)与id2为主键(主键2)
EXPLAIN SELECT * FROM TABLE WHERE id1 = 1 and id2 = 1;  // 索引生效
EXPLAIN SELECT * FROM TABLE WHERE id1 = 1;  // 索引生效
EXPLAIN SELECT * FROM TABLE WHERE id2 = 1;  // 索引失效

 

OR

查询语句的查询条件中只有OR关键字，
且OR**前后的两个条件中的列都是索引时，索引才会生效**
否则，索引不生效。

子查询优化

子查询虽然很灵活，但是执行效率并不高。
执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。
优化：
可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

数据库结构

将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。
通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
注意：
冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

插入数据

插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。

插入数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，两者的区别：
- MyISAM(默认存储引擎)
支持全文搜索，但不是事务安全的，而且不支持外键。
每张MyISAM表存放在三个文件中：
- frm 文件存放表格定义；
- 数据文件是MYD (MYData)；
- 索引文件是MYI (MYIndex)。
- InnoDB
支持事务，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定（InnoDB表的行锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，如like操作时的SQL语句），以及提供与Oracle类型一致的不加锁读取方式。
InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。

// 修改默认引擎
set table_type=InnoDB
// 查询默认引擎
show variables like 'table_type'
// 修改表引擎
alter table tablename type=InnoDB

MyISAM管理非事务表:

它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

禁用索引

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。
为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句：
ALTER TABLE table_name DISABLE KEYS
开启索引语句：
ALTER TABLE table_name ENABLE KEYS

// 对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引

禁用唯一性检查

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：
SET UNIQUE_CHECKS = 0;
开启唯一性检查的语句：
SET UNIQUE_CHECKS = 1;

批量插入数据

批量插入数据，可以使用一条INSERT语句插入多条数据, 比一条INSERT语句一条记录效率高。

LOAD DATA INFILE

当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。

load data [low_priority] [local] infile "f:\\user.txt" 
[replace|ignore] 
into table user2
character set gbk
fields 
    terminated by ',' 
    enclosed by '"' 
lines 
    terminated by '\n' 
(`username`,`sex`,`address`);
// f:\\user.txt内容
"名字1","1","地址1"

low_priority : 低优先级, 数据库空闲时上传

local : 本地上传

replace : 新行将代替有相同的唯一键值的现有行
ignore : 跳过唯一键的现有行的重复输入行

fields:terminated by : 字段分割
fields:enclosed by : 字段括起字符

lines:terminated by : 换行符

InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持

如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

禁用唯一性检查

与MyISAM一致

禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：
SET foreign_key_checks = 0;
开启：
SET foreign_key_checks = 1;

禁止自动提交

mysql默认是自动提交的, 插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。

禁用：
SET autocommit = 0;
开启：
SET autocommit = 1;

服务器

优化服务器硬件

需要从以下几个方面考虑：
1. 配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。
内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。
2. 配置高速磁盘，比如SSD。
3. 合理分配磁盘IO，把磁盘IO分散到多个设备上，以减少资源的竞争，提高并行操作能力。
4. 配置多核处理器，MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。

优化MySQL的参数

MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：
- key_buffer_size : 缓存区大小;(太大导致OS频繁换页, 降低性能)
- table_cache : 同时打开表的个数(太大影响性能)
- query_cache_size : 查询缓存区大小(配合query_cache_type使用)
- sort_buffer_size : 排序缓存区大小(数值大, 排序速度快)
- read_buffer_size : 每个线程扫描时, 每个表分配缓存区大小
- read_rnd_buffer_size : 每个线程保留的缓存区大小
- innodb_buffer_pool_size : 表示InnoDB类型的表和索引的最大缓存
- max_connections : 最大连接数
- innodb_flush_log_at_trx_commit : 缓存区数据写入日志文件时间, 并将日志文件写入磁盘。
- interactive_timeout : 服务器在关闭连接前等待行动秒数
- thread_cache_size : 复用的线程的数量
- wait_timeout : 服务器在关闭一个连接时等待行动的秒数(默认28 800秒)

MySQL 执行计划分析工具的使用技巧
AI天才研究院
09-19 1955
MySQL是一个开源数据库，基于社区版开发而成，MySQL提供了丰富的数据处理能力、高性能、动态扩展等优点。当我们使用MySQL时，我们需要花时间去分析执行计划，找到一条比较好的SQL查询语句执行方式，才能获得比较好的查询效率。然而，由于数据库查询语言多种多样，分析执行计划的过程不仅仅是一个简单的工作，它涉及到复杂的逻辑运算、数据结构、统计信息等多方面因素，因此很容易出错。而分析工具往往也只提供一些简单的功能或分析结果。
MySQL高级（下）之索引失效和一些优化
sober
09-18 537
1、表单使用索引的常见索引失效 1、1 全值匹配 有以下sql语句 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd' 建立索
mysql中like是否使用索引
12-14
一.测试数据  CREATE TABLE `test_orderdetail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) DEFAULT NULL, `product_name` varchar(100) DEFAULT NULL, `cnt` int(11) DEFAULT NULL, `create_date` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_orderid_productname` (`product_name`
mysql order by 索引名字_MySQL|mysql-索引
weixin_42715262的博客
01-27 328
1、索引是什么1.1索引简介索引是表的目录，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，以及快速定位查询数据。对于索引，会保存在额外的文件中。1.2主要注意的地方：(1).索引可以提高查询速度，会减慢写入速度，索引的缺点时创建和维护索引需要耗费时间。(2).索引并不是越多越好，索引固然可以提高相应的select的效率，但...
mysql性能优化-查询执行计划分析（EXPLAIN）
最新发布
Flying_Fish_roe的博客
09-20 1356
EXPLAIN命令用于显示 MySQL 如何执行SELECT查询。它提供了关于表如何被扫描、使用了哪些索引、表之间的连接顺序、连接类型等关键信息。通过EXPLAIN命令，开发者可以深入了解查询的执行方式，从而找出可以优化的地方。基本语法MySQL 将会返回查询执行的详细计划。可以用于SELECT查询，以及包含SELECT的INSERTUPDATEDELETE语句。EXPLAIN是 MySQL 查询优化中非常重要的工具，通过它我们可以深入了解查询执行的过程，找到性能瓶颈，并通过索引、查询重构等手段进行优化。
mysql面试
qq_43548990的博客
10-12 322
mysql 面试 1、Mysql中有哪几种锁？ MyISAM 支持表锁，InnoDB 支持表锁和行锁，默认为行锁。 表级锁:开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量 最低。 行级锁:开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。 2、Mysql支持事务吗？ 在缺省模式下，MYSQL 是 autocommit 模式的，所有的数据库更新操作都会即时提交，所 以在缺省情况下，mysql 是不支持事务的。 但是如果你的 MYSQL 表类型是使用 InnoDB T
Mysql多个LEFT JOIN使用ORDER不能使用索引导致查询过慢的解决方案
热门推荐
枯鸦专栏
10-12 1万+
转自：http://www.tuicool.com/articles/qemmMfY Mysql在多个LEFT JOIN的情况下使用ORDER BY排序，就算是其中一个表的主键也仍然使用file sort排序，数据量多的话就相当的慢。 优化前语句 SELECT * FROM a LEFT JOIN b ON a.id=b.a_id ORDER a.id DESC
status状态字段是否要创建索引？
乃青的博客
04-23 1万+
前段时间在公司工作中我建议创建的某个表字段索引在内部评审的时候被删除了，理由是”状态枚举值少的索引效果不好，甚至会引发全表扫描，例如性别男女“。但我觉得，只查未发送的记录，取结果较少，索引效率会高。虽然不是什么大事，直到现在也没有什么明显影响，但这个事情给我心底留下了疑问，“状态枚举字段是否应该建立索引？”
MySQL执行计划的深入分析
09-09
MySQL执行计划是数据库管理员和开发人员用来理解和优化SQL查询性能的关键工具。它是数据库解析和执行SQL语句的蓝图，提供有关数据库如何执行查询的详细信息。执行计划可以帮助我们理解SQL语句的执行流程，包括数据的...
MySQL SQL执行计划分析与优化方案.pptx
10-14
MySQL SQL 执行计划分析与优化方案 MySQL SQL 执行计划分析与优化方案是数据库性能优化的关键步骤。通过分析 SQL 执行计划，可以了解 MySQL 是如何执行查询的，并且可以根据执行计划来优化查询性能。下面将对 MySQL...
mysql 执行计划
04-20
`mysql_explain.pptx`可能包含更详细的PPT演示，涵盖MySQL执行计划的各个方面，包括如何分析执行计划，如何根据执行计划调整索引，以及如何解决常见的性能问题。建议查看这份资料以获取深入的实践指导和案例分析。 ...
mysql 审核status字段 是否需要加索引
minihuabei的博客
11-02 1890
过多相同不建议使用索引 不需要的，请求表上的数据行超出表总记录数30%，变成全表扫描，这时候如果使用索引反而开销更大, 所以即使加了索引mysql也不会用的。 索引是否需要创建，需要看业务情况，如果某个字段经常在某些调用非常高的业务中 处在 where 查询条件下。那么就需要进行索引。 不过，对于像你这样的情况，如果数据量确实很大，那么在创建时间上弄一个索引，然后按照时间取最近一周的，再辨别 status 是否是0 。这样要快得多。 ...
MySQL枚举类型的小范围字段应不应该加索引？
xiangweiqiang的博客
08-30 2961
因为sex='1’有两千多万数据，占了总表数据记录数的绝大部分，所以即使sex加了索引，在这条SQL中的意义不大，也差不多要全表扫描，内存也基本会溢出。案例：表引擎是InnoDB，有三千多万的数据，id为主键自增，sex性别是enum(‘0’,‘1’,‘2’)，数据大小是1.8G，索引大小是1.2G，合计3G。如果sex没有加索引，那么这条SQL就会全表扫描，三千多万的数据量进行全表扫描，内存基本会溢出，不被打死才怪。回答这个问题前，要根据实际情况来，如果表数据不大，只有几百几千，那完全没有必要加索引；..
mysql status 索引_MYSQL中的索引
weixin_32384723的博客
01-19 486
一、概念：索引(index)是用来快速地寻找那些具有特定值的记录，主要是为了检索的方便，加快查询速度。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果对MySQL与所了解，应该会知道除了索引外，MySQL中还要主键、外键以及唯一索引，那...
看情况给 状态、性别等字段建立索引
weixin_33725515的博客
10-11 1151
一般认为，可选择性非常低的字段，不适合建立索引，譬如 状态字段、性别字段。但是，并不是通用规则！若这些可选择性非常低的字段，在其中的一种分布非常少，而且查询非常频繁的话，可以对该字段进行索引！如：某论坛，总有用户搜索女性用户，但是女性用户又非常少，仅占百分之一，这个时候可以选择给性别字段建立索引！再如：某表为交易记录表，其中状态status有0,1(0表示交易失败，1表示成功...
mysql 字段值分布很少的字段要不要加索引
曾沂宏的博客
09-15 9263
在我还是个mysql新手的时候，看到有的同事给字段值分布很少的字段也加索引。 例如：订单状态字段只有6个值：0 待确认，1 已确认，2 已收货，3 已取消，4 已完成，5 已作废 在我理解mysql B+tree的原理后，很有必要去实战这种情况到底有没有必要加索引。 建立相关表数据 建立带索引的表 DROP TABLE if EXISTS `bool_index`; CREATE TABLE `bool_index` ( `id` INT (11) NOT NULL AUTO_INCRE...
mysql like 索引问题
qq_37113604的博客
04-03 8133
下面使用的mysql官方测试库测试 当索引是字符型且是主键时： departments表： 可以看到，只有d后面有%时，type是range也就是索引范围查询，通过索引字段范围获取表中部分数据记录。 当d前面有%时，走的是全索引扫描，和ALL类型类似，只不过ALL类型是全表扫描，而index类型则仅仅扫描所有的所有，而不扫描数据。 当索引是字符型表中只有索引字段...
MySQL索引失效与解决方法实践
adrninistrat0r的博客
08-18 981
1. 前言 以下测试使用MariaDB，版本为10.0.36，InnoDb版本为5.6.39-83.1。 2. 测试用数据库表说明 以下用于测试的数据库表为test_table_log、test_table_log2，两个表的字段相同，索引不同，如下所示： CREATE TABLE IF NOT EXISTS test_table_log ( id varchar(32) NOT NULL COMMENT 'id', type varchar(20)
MySql中的like和in走不走索引
weixin_45743816的博客
06-28 4088
MySql中的like和in走不走索引
mysql执行计划分析
07-29
MySQL执行计划是指MySQL数据库查询优化器在执行查询语句时生成的一个执行计划。执行计划描述了查询语句的执行顺序、所使用的索引、连接...希望以上信息能对您的MySQL执行计划分析有所帮助！如有更多问题，请继续提问。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值