- 浏览: 2655386 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
80后的童年2:
深入浅出MongoDB应用实战开发网盘地址:https://p ...
MongoDB入门教程 -
shliujing:
楼主在不是精通java和php的前提下,请不要妄下结论。
PHP、CakePHP哪凉快哪呆着去 -
安静听歌:
希望可以一给一点点注释
MySQL存储过程之代码块、条件控制、迭代 -
qq287767957:
PHP是全宇宙最强的语言!
PHP、CakePHP哪凉快哪呆着去 -
rryymmoK:
深入浅出MongoDB应用实战开发百度网盘下载:链接:http ...
MongoDB入门教程
Slow Query Basics: Optimize Data Access
Analyze a poorly performing query in two steps:
1, Find out whether your application is retrieving more data than you need
2, Find out whether the MySQL server is analyzing more rows than it needs
Typical mistakes:
1, Fetching more rows than needed
2, Fetching all columns from a multitable join
3, Fetching all columsn
The simplest query cost metrics in MySQL(Slow query log):
1, Execution time
2, Number of rows examined
3, Number of rows returned
The ratio of rows examined to rows returned is usually small-say, between 1:1 and 10:1-but sometimes it can be orders of magnitude larger
The access methods apprear in the type column in EXPLAIN's output
The access types range from a full table scan to index scans, range scans, unique index lookups and constants
If you find that a huge number of rows were examined to produce relatively few rows in the result, you can try some more sophisticated fixes:
1, Use covering indexes, which store data so that the storage engine doesn't have to retrieve the complete rows
2, Change the schema. An example is using summary tables
3, Rewrite a complicated query so the MySQL optimizer is able to execute it optimally
Ways to Restructure Queries
Sometimes you can make a query more efficient by decomposing it and executing a few simple queries instead of one complex one
Chopping up the DELETE statement and using medium-size queries can improve performance and reduce replication lag considerably
Many high-performance web sites use join decomposition
You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application
Doing joins in the application may be more efficient when:
1, You cache and reuse a lot of data from earlier queries
2, You use multiple MyISAM tables
3, You distribute data across multiple servers
4, You replace joins with IN() lists on large tables
5, A join refers to the same table multiple times
Query Execution Basics
What happens when you send MySQL a query:
1, The client sends the SQL statement to the server
2, The server checks the query cache
3, The server parses, preprocesses, and optimizes the SQL into a query execution plan
4, The query execution engine executes the plan by making calls to the storage engine API
5, The server sends the result to the client
The MySQL Client/Server Protocol is half duplex, which means that at any given time the MySQL server can be either sending or receiving messages, but not both
It also means there is no way to cut a message short
There's no flow control; once oneside sends a message, the other side must fetch the entire message before responding
When the server responds, the client has to receive the entire result set
You can use SHOW FULL PROCESSLIST to view MySQL connection or thread state:
1, Sleep
The thread is waiting for a new query from the client
2, Query
The thread is either executing the query or sending the result back to the client
3, Locked
The thread is waiting for a table lock to be granted at the server level
4, Analyzing and statistics
The thread is checking storage engine statistics and optimizing the query
5, Copying to tmp table [on disk]
The thread is processing the query and copying results to a temporary table, probably for a GROUP BY, for a filesort, or to satisfy a UNION
If the state ends with "on disk", MySQL is converting an in-memory table to an on-disk table
6, Sorting result
The thread is sorting a result set
7, Sending data
This can mean several things: the thread might be sending data between stages of the query, generating the result set, or returning the result set to the client
Some types of optimizations MySQL knows how to do:
1, Reordering joins
2, Converting OUTER JOINS to INNER JOINS
3, Applying algebraic equivalence rules
4, COUNT(), MIN(), and MAX() optimizations
5, Evaluating and reducing constant expressiosn
6, Covering indexes
7, Subquery optimization
8, Early termination
9, Equality propagation
10, IN() list comparisons
// to-do
// Have a thorough understanding of MySQL parser/optimizer
Optimizing Specific Types of Queries
COUNT
Use covering index, Summary tables or external caching system such as memcached
JOIN
1, Make sure there are indexes on the columns in the ON or USING clauses
2, Try to ensure that any GROUP BY or ORDER BY expression refers only to columns from a single table
3, Be careful when upgrading MySQL, because the join syntax, operator precedence, and other behaviors have changed at various times
Subqueries
You should usually prefer a join where possible
GROUP BY and DISTINCT
WITH ROLLUP or do superaggregation in your application
LIMIT and OFFSET
Covering index
Convert the limit to a positional query by precalculate and index a position column
Precomputed summaries
Sphinx
UNION
Always use UNION ALL
Analyze a poorly performing query in two steps:
1, Find out whether your application is retrieving more data than you need
2, Find out whether the MySQL server is analyzing more rows than it needs
Typical mistakes:
1, Fetching more rows than needed
2, Fetching all columns from a multitable join
3, Fetching all columsn
The simplest query cost metrics in MySQL(Slow query log):
1, Execution time
2, Number of rows examined
3, Number of rows returned
The ratio of rows examined to rows returned is usually small-say, between 1:1 and 10:1-but sometimes it can be orders of magnitude larger
The access methods apprear in the type column in EXPLAIN's output
The access types range from a full table scan to index scans, range scans, unique index lookups and constants
If you find that a huge number of rows were examined to produce relatively few rows in the result, you can try some more sophisticated fixes:
1, Use covering indexes, which store data so that the storage engine doesn't have to retrieve the complete rows
2, Change the schema. An example is using summary tables
3, Rewrite a complicated query so the MySQL optimizer is able to execute it optimally
Ways to Restructure Queries
Sometimes you can make a query more efficient by decomposing it and executing a few simple queries instead of one complex one
Chopping up the DELETE statement and using medium-size queries can improve performance and reduce replication lag considerably
// Bad mysql > DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH); // Good rows_affected = 0 do { rows_affected = do_query ( "DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000" ) } while rows_affected > 0
Many high-performance web sites use join decomposition
You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application
// Bad SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ON tag_post.post_id = post.id WHERE tag.tag = 'mysql' //Good SELECT * FROM tag WHERE tag='mysql' SELECT * FROM tag_post WHERE tag_id = 1234 SELECT * FROM post WHERE post.id in (123,456,567,9098,8904)
Doing joins in the application may be more efficient when:
1, You cache and reuse a lot of data from earlier queries
2, You use multiple MyISAM tables
3, You distribute data across multiple servers
4, You replace joins with IN() lists on large tables
5, A join refers to the same table multiple times
Query Execution Basics
What happens when you send MySQL a query:
1, The client sends the SQL statement to the server
2, The server checks the query cache
3, The server parses, preprocesses, and optimizes the SQL into a query execution plan
4, The query execution engine executes the plan by making calls to the storage engine API
5, The server sends the result to the client
The MySQL Client/Server Protocol is half duplex, which means that at any given time the MySQL server can be either sending or receiving messages, but not both
It also means there is no way to cut a message short
There's no flow control; once oneside sends a message, the other side must fetch the entire message before responding
When the server responds, the client has to receive the entire result set
You can use SHOW FULL PROCESSLIST to view MySQL connection or thread state:
1, Sleep
The thread is waiting for a new query from the client
2, Query
The thread is either executing the query or sending the result back to the client
3, Locked
The thread is waiting for a table lock to be granted at the server level
4, Analyzing and statistics
The thread is checking storage engine statistics and optimizing the query
5, Copying to tmp table [on disk]
The thread is processing the query and copying results to a temporary table, probably for a GROUP BY, for a filesort, or to satisfy a UNION
If the state ends with "on disk", MySQL is converting an in-memory table to an on-disk table
6, Sorting result
The thread is sorting a result set
7, Sending data
This can mean several things: the thread might be sending data between stages of the query, generating the result set, or returning the result set to the client
Some types of optimizations MySQL knows how to do:
1, Reordering joins
2, Converting OUTER JOINS to INNER JOINS
3, Applying algebraic equivalence rules
4, COUNT(), MIN(), and MAX() optimizations
5, Evaluating and reducing constant expressiosn
6, Covering indexes
7, Subquery optimization
8, Early termination
9, Equality propagation
10, IN() list comparisons
// to-do
// Have a thorough understanding of MySQL parser/optimizer
Optimizing Specific Types of Queries
COUNT
// Bad SELECT COUNT(*) FROM world.City WHERE ID > 5; // Good SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*) FROM world.City WHERE ID <= 5;
Use covering index, Summary tables or external caching system such as memcached
JOIN
1, Make sure there are indexes on the columns in the ON or USING clauses
2, Try to ensure that any GROUP BY or ORDER BY expression refers only to columns from a single table
3, Be careful when upgrading MySQL, because the join syntax, operator precedence, and other behaviors have changed at various times
Subqueries
You should usually prefer a join where possible
GROUP BY and DISTINCT
WITH ROLLUP or do superaggregation in your application
LIMIT and OFFSET
Covering index
Convert the limit to a positional query by precalculate and index a position column
Precomputed summaries
Sphinx
UNION
Always use UNION ALL
发表评论
-
HPM Note4, Schema Optimization and Indexing
2009-07-16 18:04 1437Choosing Optimal Data Types Sma ... -
HPM Note3, Benchmarking and Profiling
2009-07-02 14:07 1464Note3, Finding Bottlenecks: Ben ... -
HPM Note2, MySQL Architecture
2009-06-30 17:13 1726MySQL's Logical Architecture Th ... -
HPM Note1,Book Organization
2009-06-23 09:49 1592How This Book Is Organization ... -
MySQL Architecture
2009-01-18 00:12 3061MySQL Core Modules: Server In ... -
MySQL优化笔记
2008-10-28 17:59 3417MySQL 5.1参考手册 :: 7. 优化 一、查询优化 ... -
MySQL里获取当前week、month、quarter的start_date/end_date
2008-10-21 14:14 7411当前week的第一天: select date_sub(cur ... -
mysql里找出一个表的主键被谁作为外键约束
2008-08-13 17:16 2154SELECT ke.referenced_table_n ... -
SQL性能调优:2.1 排序的一般性调优
2008-08-05 10:21 3540影响排序速度的原因(从大到小): 1,选择的行数 2,ORDE ... -
TCP-IP详解笔记1.5 RARP:逆地址解析协议
2008-07-25 14:05 2240from http://www.beyondrails.com ... -
SQL性能调优:1.3 其他语法调优
2008-07-25 13:38 1351from http://www.beyondrails.com ... -
SQL性能调优:1.2 特别语法调优
2008-07-24 12:15 2668from http://www.beyondrails.com ... -
SQL性能调优:1.1 一般性语法调优
2008-07-23 14:47 2476from http://www.beyondrails.com ... -
MySQL存储程序开发最佳实践
2008-05-28 13:56 1664MySQL存储程序开发最佳 ... -
MySQL join的文章
2008-05-28 13:00 1540MySQL的联结(Join)语法 -
MySQL索引系列文章
2008-05-28 12:51 1440MySQL索引使用 MySQL索引 MySQL 5.1参考手册 ... -
MySQL存储程序权限控制
2008-05-28 12:29 1368MySQL存储程序权限控制 MySQL5.0引入了一些管理存 ... -
MySQL的Stored Function和Trigger
2008-05-27 18:58 3083MySQL的Stored Function和Trigger ... -
MySQL内建Function
2008-05-22 17:25 6452MySQL内建Function 在MySQL存储程序(存储过 ... -
MySQL存储过程之事务管理
2008-05-21 14:36 44842MySQL存储过程之事务管理 ACID:Atomic、Con ...
相关推荐
HPM1210GC.DLL
上海先楫HPM6750代码工程,支持HPM6750和HPM6450处理器。资源代码可直接编译、运行。
HPM1210.INF
HPM1210SU.ent HPM1210SU.ent HPM1210SU.ent
HPM1005驱动程序 ,是最新的官方驱动程序祝下载的朋友工作愉快
HPM1210SM.exe
HPM1210SU.VER
hpm1210sd.rar缺的可以用
HPM升级流程协议
HPM1210PP.dll
win7 可以使用 hpm10005win7的驱动
HPM1210FPSD.DLL
适用于XP32位操作系统的HPm1005打印机驱动
HPM.1 Firmware Update Open Compute Summit - Compute Summit HPM.1 Firmware Update Engineering Workshop HPM.1 File Format File is not sent as a single image Each action is sent by itself to IPM ...
上海先楫HPM6750官网代码工程,支持HPM6750和HPM6450处理器。资源代码可直接编译、运行。
hpm utils for hercws
上海先楫HPM6750代码工程,支持HPM6750和HPM6450处理器。资源代码可直接编译、运行。
上海先楫HPM6750代码工程,支持HPM6750和HPM6450处理器。资源代码可直接编译、运行。
HPM280_M281_20200612固件硒鼓,降级处理HPM280_M281_20200612固件硒鼓,降级处理HPM280_M281_20200612固件硒鼓,降级处理,HPM280_M281_20200612固件硒鼓,降级处理,不会使用的同学,请下载后自行百度搜索使用方法...
上海先楫HPM6750代码工程,支持HPM6750和HPM6450处理器。资源代码可直接编译、运行。