search
HomeDatabaseMysql Tutorial使用explain分析及优化由多表(三个以上)组成的视图性能

视图如下图: create or replace view FLOW_SUBMITPROCESS_V as select pi.START_USER_ID_,pir.STARTUSERID,pi.PROC_INST_ID_,pir.PROCESSINSTID,pir.CURRENTTASKINSTID as TASKID,pir.PROCESSSTARTER as SUBMITUSERJSON_,ta.NAME_ from ACT_HI_TASKINST ta,

视图如下图:

\

 

create or replace view FLOW_SUBMITPROCESS_V as

select pi.START_USER_ID_,pir.STARTUSERID,pi.PROC_INST_ID_,pir.PROCESSINSTID,pir.CURRENTTASKINSTID as TASKID,pir.PROCESSSTARTER as SUBMITUSERJSON_,ta.NAME_

from ACT_HI_TASKINST ta,ACT_HI_PROCINST pi,FLOW_PROCESSINST_RELA pir

where ta.ID_=pir.CURRENTTASKINSTID and pi.PROC_INST_ID_=pir.PROCESSINSTID;

1、使用从表索引字段作为查询条件

用从表的一个字段作为查询条件(从表中的这个字段已经是索引字段),从下图的效果可以看到,type中有一个ALL,全表检索,证明从表的索引字段对查询并没有帮助

\

将查询字段换成使用主表的StartUserID (这个字段是索引字段)
\
 

2、使用视图中与主表关联的字段查询的性能
下图,查询的字段与主表的主键PROCESSINSTID相关联,效率依然是有一个全表检索
\
使用主表的主键字段进行查询,则性能有所优化

\

总结:
1、建立多表(三个表或以上)关联视图时,如果是主表和副表都有的字段,尽量使用主表的字段(特别是主表的主键)
2、副表的字段(无论是普通字段还是主键、索引字段)作为查询条件对查询都没有帮助,都需进行全表检索


 

explain各个属性的含义

id

select查询的序列号

select_type

select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。

table

输出的行出自哪张的表。

type

重要的性能指标,联合查询所使用的类型。

type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

System(表中仅有一行,即常量表),

const(单表中最多有一个匹配行),

eq_ref(对于前面的每一行,在此表中只查询一条记录),

ref(使用普通的索引),

ref_or_null(和ref类似,但是条件中包含对于NULL查询),

index_merge(索引合并优化),

unique_subquery(in的后面是一个查询主键字段的子查询),

index_subquery(类似unique_subquery,主要是in的后面是查询非唯一索引字段的子查询), range(单表中的范围查询),

index(对于当前的每一行,都通过查询索引来得到数据),

all(对于当前的每一行,都通过全表扫描来得到数据))

一般来说,得保证查询至少达到range级别,最好能达到ref。

 

possible_keys

指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。

key

显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。

key_len

显示MySQL决定使用的键长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

ref

显示哪个字段或常数与key一起被使用。

rows

这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。

Extra

Distinct 一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

Not exists MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了

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

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

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

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

Using Where 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题

如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。


Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

How can you monitor MySQL server health and performance?How can you monitor MySQL server health and performance?Apr 26, 2025 am 12:15 AM

To monitor the health and performance of MySQL servers, you should pay attention to system health, performance metrics and query execution. 1) Monitor system health: Use top, htop or SHOWGLOBALSTATUS commands to view CPU, memory, disk I/O and network activities. 2) Track performance indicators: monitor key indicators such as query number per second, average query time and cache hit rate. 3) Ensure query execution optimization: Enable slow query logs, record and optimize queries whose execution time exceeds the set threshold.

Compare and contrast MySQL and MariaDB.Compare and contrast MySQL and MariaDB.Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.