search
HomeDatabaseMysql Tutorialmysql执行计划_MySQL

bitsCN.com      烂sql不仅直接影响sql的响应时间,更影响db的性能,导致其它正常的sql响应时间变长。如何写好sql,学会看执行计划至关重要。下面我简单讲讲mysql的执行计划,只列出了一些常见的情况,希望对大家有所帮助。测试表结构:CREATE TABLE `t1` (  `c1` int(11) NOT NULL DEFAULT '0',  `c2` varchar(128) DEFAULT NULL,  `c3` varchar(64) DEFAULT NULL,  `c4` int(11) DEFAULT NULL,  PRIMARY KEY (`c1`),  KEY `ind_c2` (`c2`),  KEY `ind_c4` (`c4`)) ENGINE=InnoDB DEFAULT CHARSET=utf8  CREATE TABLE `t2` (  `c1` int(11) NOT NULL DEFAULT '0',  `c2` varchar(128) DEFAULT NULL,  `c3` varchar(64) DEFAULT NULL,  `c4` int(11) DEFAULT NULL,  PRIMARY KEY (`c1`),  KEY `ind_c2` (`c2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `t3` (  `c1` int(11) NOT NULL DEFAULT '0',  `c2` varchar(128) DEFAULT NULL,  `c3` varchar(64) DEFAULT NULL,  `c4` int(11) DEFAULT NULL,  PRIMARY KEY (`c1`),  KEY `ind_c2` (`c2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 1.查看mysql执行计划explain  select ......2.执行计划包含的信息(1).id含义,指示select字句或操作表的顺序。eg1:id相同,执行顺序从上到下,下面的执行计划表示,先操作t1表,然后操作t2表,最后操作t3表。 eg2:若存在子查询,则子查询(内层查询)id大于父查询(外层查询),先执行子查询。id越大,优先级越高。 (2).select_type含义:select语句的类型类型:a.SIMPLE:查询中不包含子查询或者UNIONb.查询中若包含任何复杂的子部分,最外层查询则被标记为:PRIMARYc.在SELECT或WHERE列表中包含了子查询,该子查询被标记为:SUBQUERYd.在FROM列表中包含的子查询被标记为:DERIVED(衍生)e.若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在  FROM子句的子查询中,外层SELECT将被标记为:DERIVEDf.从UNION表获取结果的SELECT被标记为:UNION RESULTeg:id为1的table显示,表示结果来源于衍生表2。id为2表示子查询,读取t3表id为3类型为union,是union的第二个select,最先执行;id为NULL的类型为union result, 表示id为1的操作和id为3的操作进行结果集合并。执行顺序3->2->1->NULL (3).type含义:获取记录行采用的方式,亦即mysql的访问方式。 a.ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行 b.index:Full Index Scan,index与ALL区别为index类型只遍历索引,索引一般比记录要小。因为索引中含有c1,查询c1,c2可以通过索引扫描实现。 c.range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、等的查询备注:range类型肯定是使用了索引扫描,否则type为ALL d.ref:非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找 t2.c4为非唯一索引 e.eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描t2.c1为主键索引,主键索引也是唯一索引 f.const、system:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下, 使用system (4).possible_keys含义:指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用(5).key

含义:显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL

(6)key_len

含义:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度

(7)ref

含义:用于连接查询,表示具体某个表的某列被引用

(8)rows

含义:MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,这个值是不准确的,只有参考意义。

 (9)Extra

含义:显示一些辅助的额外信息

a.Using index,表示使用了索引

b.Using where,表示通过where条件过滤

c.Using temporary,表示使用了临时表,常见于分组和排序

d.Using filesort,表示无法使用索引排序,需要文件排序

eg1:t1.c3列没有索引

eg2:使用索引列t1.c2

 bitsCN.com

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
MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

MySQL's Role: Databases in Web ApplicationsMySQL's Role: Databases in Web ApplicationsApr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

MySQL: Building Your First DatabaseMySQL: Building Your First DatabaseApr 17, 2025 am 12:22 AM

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL: A Beginner-Friendly Approach to Data StorageMySQL: A Beginner-Friendly Approach to Data StorageApr 17, 2025 am 12:21 AM

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.

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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Will R.E.P.O. Have Crossplay?
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

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.

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor