search
HomeDatabaseMysql TutorialMySQL数据库优化概述一_MySQL

MySQL数据库优化(一) 

  1. EXPLAIN 语法(得到SELECT 的相关信息) 


EXPLAIN tbl_name


  或者:  


EXPLAIN SELECT select_options


  tableIN 语句可以被当作 DESCRIBE 的同义词来用,也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息。EXPLAIN tbl_name 语法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一样。当在一个 SELECT 语句前使用关键字 EXPLAIN 时,MYSQL会解释了即将如何运行该 SELECT 语句,它显示了表如何连接、连接的顺序等信息。本章节主要讲述了第二种 EXPLAIN 用法。

  在 EXPLAIN 的帮助下,您就知道什么时候该给表添加索引,以使用索引来查找记录从而让 SELECT 运行更快。

  如果由于不恰当使用索引而引起一些问题的话,可以运行 ANALYZE TABLE 来更新该表的统计信息,例如键的基数,它能帮您在优化方面做出更好的选择。

  您还可以查看优化程序是否以最佳的顺序来连接数据表。为了让优化程序按照 SELECT 语句中的表名的顺序做连接,可以在查询的开始使用 SELECT STRAIGHT_JOIN 而不只是 SELECT。

  EXPLAIN 返回了一行记录,它包括了 SELECT 语句中用到的各个表的信息。这些表在结果中按照MySQL即将执行的查询中读取的顺序列出来。MySQL用一次扫描多次连接(single-sweep, multi-join) 的方法来解决连接。这意味着MySQL从第一个表中读取一条记录,然后在第二个表中查找到对应的记录,然后在第三个表中查找,依次类推。当所有的表都扫描完了,它输出选择的字段并且回溯所有的表,直到找不到为止,因为有的表中可能有多条匹配的记录下一条记录将从该表读取,再从下一个表开始继续处理。

  在MySQL version 4.1中,EXPLAIN 输出的结果格式改变了,使得它更适合例如 UNION 语句、子查询以及派生表的结构。更令人注意的是,它新增了2个字段: id 和 select_type。当你使用早于MySQL 4.1的版本就看不到这些字段了。

  EXPLAIN 结果的每行记录显示了每个表的相关信息,每行记录都包含以下几个字段:

  id

  本次 SELECT 的标识符。在查询中每个 SELECT 都有一个顺序的数值
select_type

  SELECT 的类型,可能会有以下几种:

  SIMPLE
  简单的 SELECT (没有使用 UNION 或子查询)

  PRIMARY
  最外层的 SELECT。

  UNION
  第二层,在SELECT 之后使用了 UNION 。

  DEPENDENT UNION
  UNION 语句中的第二个 SELECT,依赖于外部子查询

  SUBQUERY
  子查询中的第一个 SELECT

  DEPENDENT SUBQUERY
  子查询中的第一个 SUBQUERY 依赖于外部的子查询

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

  table

  记录查询引用的表。

  type

  表连接类型。以下列出了各种不同类型的表连接,依次是从最好的到最差的:

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

  const
  表中最多只有一行匹配的记录,它在查询一开始的时候就会被读取出来。由于只有一行记录,在余下的优化程序里该行记录的字段值可以被当作是一个恒定值。const 表查询起来非常快,因为只要读取一次!const 用于在和 PRIMARY KEY 或 UNIQUE 索引中有固定值比较的情形。下面的几个查询中,tbl_name 就是 const 表了:


  SELECT * FROM tbl_name WHERE primary_key=1;
  SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;
 


  eq_ref
  从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与 const 类型不同的是,这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个 PRIMARY KEY 或 UNIQUE 类型。eq_ref 可以用于在进行"="做比较时检索字段。比较的值可以是固定值或者是表达式,表达式中可以使用表里的字段,它们在读表之前已经准备好了。以下的几个例子中,MySQL使用了 eq_ref 连接来处理 ref_table:


  SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
  SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
 


  ref
  该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref 用于连接程序使用键的最左前缀或者是该键不是 PRIMARY KEY 或 UNIQUE 索引(换句话说,就是连接程序无法根据键值只取得一条记录)的情况。当根据键值只查询到少数几条匹配的记录时,这就是一个不错的连接类型。ref 还可以用于检索字段使用 = 操作符来比较的时候。以下的几个例子中,MySQL将使用 ref 来处理 ref_table:


  SELECT * FROM ref_table WHERE key_column=expr;
  SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
  SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

 

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
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

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

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment