`
hideto
  • 浏览: 2682962 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

HPM Note5, Query Performance Optimization

阅读更多
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
// 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
分享到:
评论

相关推荐

    先楫半导体 HPM6700/6400 系列高性能微控制器用户手册

    HPM6700/6400 系列微控制器是基于 RISC-V 内核的 32 位高性能微控制器,适用于上海先楫半导体科技有限公司以下型号产品:HPM6750IVM1、HPM6750IAN1、HPM6730IVM1、HPM6730IAN1、HPM6450IVM1、HPM6450IAN1、HPM6430...

    TDC 3000 系统 HPM组态手册

    在这个高度集成且复杂的自动化系统中,HPM(High Performance Manager)是关键组成部分,负责高级过程控制和优化。 TDC 3000系统是全球领先的自动化解决方案之一,广泛应用于石油、化工、电力等多个领域。它以其高...

    HPM1005驱动程序

    HPM1005驱动程序是专为HP(惠普)HPM1005型号打印机设计的重要软件组件。驱动程序在计算机系统和硬件设备之间起着桥梁的作用,它允许操作系统与打印机进行有效的通信,确保打印任务的顺利执行。...

    上海先楫HPM6750采集OV5640摄像头显示在屏幕【支持HPM6750和HPM6450】

    上海先楫HPM6750是一款高性能的嵌入式微处理器,专为处理复杂的多媒体应用而设计。这款芯片在物联网(IoT)、智能硬件、工业自动化等领域有着广泛的应用。HPM6450是其系列中的另一款处理器,虽然可能在性能上有所区别...

    HPM.rar_ZIY_hpm

    标题中的"HPM.rar_ZIY_hpm"表明这是一个关于HPM(可能是Hercules Performance Monitor)的压缩文件,由用户ZIY创建或整理,主要用于HercWS(可能是指Hercules Web Services)。"ziy hpm"的标签进一步确认了这个文件...

    HPM1210.INF

    HPM1210.INF

    PICMG_HPM_1R1_0.pdf

    《HPM.1硬件平台管理:IPM控制器固件升级规范》 HPM.1(Hardware Platform Management)是PICMG(PCI工业计算机制造商集团)发布的一项标准,旨在规范服务器和其他计算平台的硬件平台管理,特别是关于IPM...

    上海先楫HPM6750驱动EMMC和SD卡【支持HPM6750和HPM6450】

    上海先楫HPM6750驱动EMMC和SD卡的开发是嵌入式系统设计中的关键环节,尤其对于基于HPM6750和HPM6450处理器的设备来说,高效的存储器管理是确保系统性能和稳定性的基础。这个代码工程提供了一个完整的解决方案,使...

    hpm1210sd.rar

    【标题】"hpm1210sd.rar"是一个压缩文件,通常用于在互联网上分享或存储多个相关文件。从这个名字来看,它可能是某个特定硬件设备、软件程序或者相关项目的一部分,具体而言,"HPM1210SD"可能代表一个型号、版本号...

    HPM1210SU.ent

    HPM1210SU.ent HPM1210SU.ent HPM1210SU.ent

    HPM1210SM.exe

    HPM1210SM.exe

    HPM1210SU.VER

    HPM1210SU.VER

    HPM1210PP.dll

    HPM1210PP.dll

    HPM1210GC.DLL

    HPM1210GC.DLL

    HPM1210FPSD.DLL

    HPM1210FPSD.DLL

    HPM1005驱动

    适用于XP32位操作系统的HPm1005打印机驱动

    hpm1005win7驱动

    描述中的“win7可以使用”确认了HP LaserJet M1005打印机在Windows 7系统上是支持的,并且提到了“hpm10005win7的驱动”,这可能是描述中的一个小错误,应该是指“hpm1005win7”的驱动,即对应的是HP LaserJet M1005...

    OCP_2014_firware_update_HPM.1.pdf

    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驱动LCD屏上显示内容【支持HPM6750和HPM6450】

    上海先楫HPM6750驱动LCD屏上显示内容的工程是专为HPM6750和HPM6450处理器设计的。这个项目的核心在于如何有效地利用这两款处理器的特性来控制和显示LCD屏幕上的信息。在嵌入式系统中,这种驱动程序的开发对于实现人...

    上海先楫HPM6750实现CAN和CAN-FD通信【支持HPM6750和HPM6450】

    在本文中,我们将深入探讨如何使用上海先楫的HPM6750和HPM6450处理器实现CAN(Controller Area Network)和CAN-FD(Controller Area Network with Flexible Data-rate)通信。这两种通信协议在嵌入式系统,尤其是...

Global site tag (gtag.js) - Google Analytics