搜尋
首頁資料庫mysql教程MySQL 优化(三)

MySQL 优化(三)

Jun 07, 2016 pm 03:26 PM
mysql最佳化效能查詢

7.2.2 估算查询性能 在大多数情况下,可以通过统计磁盘搜索次数来估算查询的性能。对小表来说,通常情况下只需要搜索一次磁盘就能找到对应的记录(因为索引可能已经缓存起来了)。对大表来说,大致可以这么估算,它使用B树做索引,想要找到一条记录大概需要

7.2.2 估算查询性能

在大多数情况下,可以通过统计磁盘搜索次数来估算查询的性能。对小表来说,通常情况下只需要搜索一次磁盘就能找到对应的记录(因为索引可能已经缓存起来了)。对大表来说,大致可以这么估算,它使用B树做索引,想要找到一条记录大概需要搜索的次数为:log(<var>row_count</var>) / log(<var>index_block_length</var> / 3 * 2 / (<var>index_length</var> + <var>data_pointer_length</var>)) + 1

在MySQL中,一个索引块通常是1024bytes,数据指针通常是4bytes。对于一个有500,000条记录、索引长度为3bytes(medium integer)的表来说,根据上面的公式计算得到需要做 log(500,000)/log(1024/3*2/(3+4)) + 1 = <code>4 次搜索。

这个表的索引大概需要 500,000 * 7 * 3/2 = 5.2MB的存储空间(假定典型的索引缓冲区的2/3),因此应该会有更多的索引在内存中,并且可能只需要1到2次调用就能找到对应的记录。

对于写来说,大概需要4次(甚至更多)搜索才能找到新的索引位置,更新记录时通常需要2次搜索。

请注意,前面的讨论中并没有提到应用程序的性能会因为log N的值越大而下降。只要所有的东西都能由操作系统或者SQL服务器缓存起来,那么性能只会因为数据表越大而稍微下降。当数据越来越大之后,就不能全部放到 缓存中去了,就会越来越慢了,除非应用程序是被磁盘搜索约束的(它跟随着的log N值增加而增加)。为了避免这种情况,可以在数据量增大以后也随着增大索引缓存容量。对 MyISAM 类型表来说,索引缓存容量是由系统变量 key_buffer_size 控制的。详情请看"7.5.2 Tuning Server Parameters"。

7.2.3 SELECT 查询的速度

通常情况下,想要让一个比较慢的 SELECT ... WHERE 查询变得更快的第一件事就是,先检查看看是否可以增加索引。所有对不同表的访问都通常使用索引。可以使用 EXPLAIN 语句来判断 SELECT 使用了哪些索引。详情请看"7.4.5 How MySQL Uses Indexes"和"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。

以下是几个常用的提高 MyISAM 表查询速度的忠告:

  • 想要让MySQL将查询优化的速度更快些,可以在数据表已经加载完全部数据后执行行 ANALYZE TABLE 或运行 myisamchk --analyze 命令。它更新了每个索引部分的值,这个值意味着相同记录的平均值(对于唯一索引来说,这个值则一直都是 1)。MySQL就会在当你使用基于一个非恒量表达式的两表连接时,根据这个值来决定使用哪个索引。想要查看结果,可以在分析完数据表后运行 SHOW INDEX FROM <var>tbl_name</var> 查看 Cardinality 字段的值。myisamchk --description --verbose 显示了索引的分布信息。
  • 想要根据一个索引来排序数据,可以运行 myisamchk --sort-index --sort-records=1 (如果想要在索引 1 上做排序)。这对于有一个唯一索引并且想根据这个索引的顺序依次读取记录的话来说是一个提高查询速度的好办法。不过要注意的是,第一次在一个大表上做排序的话将会耗费很长时间。

7.2.4 MySQL如何优化 WHERE 子句

这个章节讲述了优化程序如何处理 WHERE 子句。例子中使用了 SELECT 语句,但是在 DELETEUPDATE 语句中对 WHERE 子句的优化是一样的。

注意,关于MySQL优化的工作还在继续,因此本章节还没结束。MySQL做了很多优化工作,而不仅仅是文档中提到的这些。

MySQL的一些优化做法如下:

  • 去除不必要的括号:
       ((a AND b) AND c OR (((a AND b) AND (c AND d))))<br>-> (a AND b AND c) OR (a AND b AND c AND d)<br>  
  • 展开常量:
       (a<b and b="c)" a="5<br"></b>-> b>5 AND b=c AND a=5<br>  
  • 去除常量条件(在展开常量时需要):
       (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)<br>-> B=5 OR B=6<br>  
  • 常量表达示在索引中只计算一次
  • 在单独一个表上做 COUNT(*) 而不使用 WHERE 时, 对于 MyISAMHEAP 表就会直接从表信息中检索结果。在单独一个表上做任何表 NOT NULL 达式查询时也是这样做。
  • 预先探测无效的常量表达式。MySQL会快速探测一些不可能的 SELECT 语句并且不返回任何记录。
  • 当没用 GROUP BY 或分组函数时,HAVINGWHERE 合并(COUNT(), MIN() 等也是如此)。
  • 为表连接中的每个表构造一个简洁的 WHERE 语句,以得到更快的 WHERE 计算值并且尽快跳过记录。
  • 查询中所有的常量表都会比其他表更早读取。一个常量表符合以下几个条件:

    • 空表或者只有一条记录。
    • 与在一个 UNIQUE 索引、或一个 PRIMARY KEYWHERE 子句一起使用的表,这里所有的索引部分和常数表达式做比较并且索引部分被定义为 NOT NULL

    以下的几个表都会被当成常量表:
    SELECT * FROM t WHERE <var>primary_key</var>=1;<br>SELECT * FROM t1,t2<br>    WHERE t1.<var>primary_key</var>=1 AND t2.<var>primary_key</var>=t1.id;<br>  
  • MySQL会进各种可能找到表连接最好的连接方法。 如果在 ORDER BYGROUP BY 子句中的所有字段都来自同一个表的话,那么在连接时这个表就会优先处理。
  • 如果有 ORDER BY 子句和一个不同的 GROUP BY 子句,或者如果 ORDER BYGROUP BY 中的字段都来自其他的表而非连接顺序中的第一个表的话,就会创建一个临时表了。
  • 如果使用 SQL_SMALL_RESULT,MySQL就会使用内存临时表了。
  • 所有的表索引都会查询,最好的情况就是所有的索引都会被用到,除非优化程序认为全表扫描的效率更高。同时,数据表扫 描是基于判断最好的索引范围超过数据表的30%。 现在,优化程序复杂多了,它基于对一些附加因素的估计,例如表大小,记录总数,I/O块大小,因此就不能根据一个固定的百分比来决定是选择使用索引还是直 接扫描数据表。
  • 在某些情况下,MySQL可以直接从索引中取得记录而无需查询数据文件。如果所有在索引中使用的字段都是数字类型的话,只需要用索引树就能完成查询。
  • 每条记录输出之前,那些没有匹配 HAVING 子句的就会被跳过。

以下几个查询速度非常快:
SELECT COUNT(*) FROM <var>tbl_name</var>;<br><br>SELECT MIN(<var>key_part1</var>),MAX(<var>key_part1</var>) FROM <var>tbl_name</var>;<br><br>SELECT MAX(<var>key_part2</var>) FROM <var>tbl_name</var><br>    WHERE <var>key_part1</var>=<var>constant</var>;<br><br>SELECT ... FROM <var>tbl_name</var><br>    ORDER BY <var>key_part1</var>,<var>key_part2</var>,... LIMIT 10;<br><br>SELECT ... FROM <var>tbl_name</var><br>    ORDER BY <var>key_part1</var> DESC, <var>key_part2</var> DESC, ... LIMIT 10;<br>

以下几个查询都是使用索引树,假使那些索引字段都是数字型:

SELECT <var>key_part1</var>,<var>key_part2</var> FROM <var>tbl_name</var> WHERE <var>key_part1</var>=<var>val</var>;<br><br>SELECT COUNT(*) FROM <var>tbl_name</var><br>    WHERE <var>key_part1</var>=<var>val1</var> AND <var>key_part2</var>=<var>val2</var>;<br><br>SELECT <var>key_part2</var> FROM <var>tbl_name</var> GROUP BY <var>key_part1</var>;<br>

以下几个查询使用索引来取得经过顺序排序后的记录而无需经过独立的排序步骤:

SELECT ... FROM <var>tbl_name</var><br>    ORDER BY <var>key_part1</var>,<var>key_part2</var>,... ;<br><br>SELECT ... FROM <var>tbl_name</var><br>    ORDER BY <var>key_part1</var> DESC, <var>key_part2</var> DESC, ... ;<br>

7.2.5 MySQL 如何优化 OR 子句


Index Merge 方法用于使用 ref, ref_or_null, 或 range 扫描取得的记录合并起来放到一起作为结果。这种方法在表条件是或条件 ref, ref_or_null, 或 range ,并且这些条件可以用不同的键时采用。
"join"类型的优化是从 MySQL 5.0.0 开始才有的,代表者在索引的性能上有着标志性的改进,因为使用老规则的话,数据库最多只能对每个引用表使用一个索引。
EXPLAIN 的结果中,这种方法在 type 字段中表现为 index_merge。这种情况下,key 字段包含了所有使用的索引列表,并且 key_len 字段包含了使用的索引的最长索引部分列表。
例如:
SELECT * FROM <var>tbl_name</var> WHERE <var>key_part1</var> = 10 OR <var>key_part2</var> = 20;<br><br>SELECT * FROM <var>tbl_name</var><br>    WHERE (<var>key_part1</var> = 10 OR <var>key_part2</var> = 20) AND <var>non_key_part</var>=30;<br><br>SELECT * FROM t1,t2<br>    WHERE (t1.<var>key1</var> IN (1,2) OR t1.<var>key2</var> LIKE '<var>value</var>%')<br>    AND t2.<var>key1</var>=t1.<var>some_col</var>;<br><br>SELECT * FROM t1,t2<br>    WHERE t1.<var>key1</var>=1<br>    AND (t2.<var>key1</var>=t1.<var>some_col</var> OR t2.<var>key2</var>=t1.<var>some_col2</var>);<br>  

7.2.6 MySQL 如何优化 IS NULL


MySQL在 <var>col_name</var> IS NULL 时做和 <var>col_name</var> = constant_value 一样的优化。例如,MySQL使用索引或者范围来根据 IS NUL L搜索 NULL
SELECT * FROM <var>tbl_name</var> WHERE <var>key_col</var> IS NULL;<br><br>SELECT * FROM <var>tbl_name</var> WHERE <var>key_col</var>  NULL;<br><br>SELECT * FROM <var>tbl_name</var><br>    WHERE <var>key_col</var>=<var>const1</var> OR <var>key_col</var>=<var>const2</var> OR <var>key_col</var> IS NULL;<br>

如果一个 WHERE 子句包括了一个 <var>col_name</var> IS NULL 条件,并且这个字段声明为 NOT NULL,那么这个表达式就会被优化。当字段可能无论如何都会产生 NULL 值时,就不会再做优化了;例如,当它来自一个 LEFT JOIN 中右边的一个表时。

MySQL 4.1.1或更高会对连接 <var>col_name</var> = <var>expr</var> AND <var>col_name</var> IS NULL 做额外的优化, 常见的就是子查询。EXPLAIN 当优化起作用时会显示 ref_or_null

优化程序会为任何索引部分处理 IS NULL

以下几个例子中都做优化了,假使字段 a 和 表 t2b 有索引了:

SELECT * FROM t1 WHERE t1.a=<var>expr</var> OR t1.a IS NULL;<br><br>SELECT * FROM t1,t2 WHERE t1.a=t2.a OR t2.a IS NULL;<br><br>SELECT * FROM t1,t2<br>    WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;<br><br>SELECT * FROM t1,t2<br>    WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);<br><br>SELECT * FROM t1,t2<br>    WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)<br>    OR (t1.a=t2.a AND t2.a IS NULL AND ...);<br>

ref_or_null 首先读取引用键,然后独立扫描键值为 NULL 的记录。

请注意,优化程序只会处理一个 IS NULL 级别。下面的查询中,MySQL只会使用键来查询表达式 (t1.a=t2.a AND t2.a IS NULL) 而无法使在 b 上使用索引部分:

SELECT * FROM t1,t2<br>     WHERE (t1.a=t2.a AND t2.a IS NULL)<br>     OR (t1.b=t2.b AND t2.b IS NULL);<br>  

7.2.7 MySQL 如何优化 DISTINCT

在很多情况下,DISTINCTORDER BY 一起使用时就会创建一个临时表。

注意,由于 DISTINCT 可能需要用到 GROUP BY,就需要明白MySQL在 ORDER BYHAVING 子句里的字段不在选中的字段列表中时是怎么处理的。详情请看"13.9.3 GROUP BY with Hidden Fields"。

LIMIT <var>row_count</var>DISTINCT 一起使用时,MySQL在找到 row_count 不同记录后就会立刻停止搜索了。

如果没有用到来自查询中任何表的字段时,MySQL在找到第一个匹配记录后就会停止搜索这些没没用到的表了。在下面的情况中,假使 t1t2 前就使用了(可以通过 EXPLAIN 分析知道),MySQL就会在从 t2 中找到第一条记录后就不再读 t2 了(为了能和中 t1 的任何特定记录匹配):

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;<br>

7.2.8 MySQL 如何优化 LEFT JOINRIGHT JOIN


A LEFT JOIN B join_condition 在MySQL中实现如下:
  • B 依赖于表 A 以及其依赖的所有表。
  • A 依赖于在 LEFT JOIN 条件中的所有表(除了 B)。
  • LEFT JOIN 条件用于决定如何从表 B 中读取记录了(换句话说,WHERE 子句中的任何条件都对此不起作用)。
  • 所有标准的连接优化都会执行,例外的情况是有一个表总是在它依赖的所有表之后被读取。如果这是一个循环的依赖关系,那么MySQL会认为这是错误的。
  • 所有的标准 WHERE 优化都会执行。
  • 如果 A 中有一条记录匹配了 WHERE 子句,但是 B 中没有任何记录匹配 ON 条件,那么就会产生一条 B 记录,它的字段值全都被置为 NULL
  • 如果使用 LEFT JOIN 来搜索在一些表中不存在的记录,并且 WHERE 部分中有检测条件:<var>col_name</var> IS NULL<var>col_name</var> 字段定义成 NOT NULL 的话,MySQL就会在找到一条匹配 LEFT JOIN 条件的记录(用于和特定的索引键做联合)后停止搜索了。

RIGHT JOIN 的实现和 LEFT JOIN 类似,不过表的角色倒过来了。
连接优化程序计算了表连接的次序。表读取的顺序是由 LEFT JOIN 强行指定的,而且使用 STRAIGHT_JOIN 能帮助连接优化程序更快地执行,因为这就会有更少的表排队检查了。注意,这里是指如果你执行下面这种类型的查询后,MySQL就会对 b 做一次全表扫描,因为 LEFT JOIN 强制要求了必须在读 d 之前这么做:
SELECT *<br>    FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)<br>    WHERE b.key=d.key;<br>

解决这种情况的方法是按照如下方式重写查询:

SELECT *<br>    FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)<br>    WHERE b.key=d.key;<br>

从4.0.14开始,MySQL做如下 LEFT JOIN 优化:如果对产生的 NULL 记录 WHERE 条件总是 ,那么 LEFT JOIN 就会变成一个普通的连接。
例如,下面的查询中如果 t2.column1 的值是 NULL 的话,WHERE 子句的结果就是了:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;<br>

因此,这就可以安全的转换成一个普通的连接查询:

SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column1=t2.column1;<br>

这查询起来就更快了,因为如果能有一个更好的查询计划的话,MySQL就会在 t1 之前就用到 t2 了。想要强行指定表顺序的话,可以使用 STRAIGHT_JOIN


‹ MySQL 优化(二)向上MySQL 优化(四) ›

陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
您可以使用哪些工具來監視MySQL性能?您可以使用哪些工具來監視MySQL性能?Apr 23, 2025 am 12:21 AM

如何有效監控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。 1.使用mysqladmin查看連接數。 2.用SHOWGLOBALSTATUS查看查詢數。 3.PMM提供詳細性能數據和圖形化界面。 4.MySQLEnterpriseMonitor提供豐富的監控功能和報警機制。

MySQL與SQL Server有何不同?MySQL與SQL Server有何不同?Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显著差异,选择时需考虑项目规模和未来扩展性。

在哪些情況下,您可以選擇SQL Server而不是MySQL?在哪些情況下,您可以選擇SQL Server而不是MySQL?Apr 23, 2025 am 12:20 AM

在需要高可用性、高級安全性和良好集成性的企業級應用場景下,應選擇SQLServer而不是MySQL。 1)SQLServer提供企業級功能,如高可用性和高級安全性。 2)它與微軟生態系統如VisualStudio和PowerBI緊密集成。 3)SQLServer在性能優化方面表現出色,支持內存優化表和列存儲索引。

MySQL如何處理角色集和碰撞?MySQL如何處理角色集和碰撞?Apr 23, 2025 am 12:19 AM

mySqlManagesCharacterSetsetSandCollat​​ionsyutusututf-8asthEdeFault,允許ConfigurationAtdataBase,table和columnlevels,AndrequiringCarefullageLignmentToavoidMismatches.1)setDefeaultCharactersetTercharactersetEtCollacterSeteTandColletationForAdataBase.2)conformentcollecharactersettersetertersetcollat​​ertersetcollat​​ioncollat​​ion

MySQL中有什麼觸發器?MySQL中有什麼觸發器?Apr 23, 2025 am 12:11 AM

MySQL觸發器是與表相關聯的自動執行的存儲過程,用於在特定數據操作時執行一系列操作。 1)觸發器定義與作用:用於數據校驗、日誌記錄等。 2)工作原理:分為BEFORE和AFTER,支持行級觸發。 3)使用示例:可用於記錄薪資變更或更新庫存。 4)調試技巧:使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。 5)性能優化:避免複雜操作,使用索引,管理事務。

您如何在MySQL中創建和管理用戶帳戶?您如何在MySQL中創建和管理用戶帳戶?Apr 22, 2025 pm 06:05 PM

在MySQL中創建和管理用戶賬戶的步驟如下:1.創建用戶:使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配權限:使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正權限錯誤:使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然後重新分配權限;4.優化權限:使用SHOWGRA

MySQL與Oracle有何不同?MySQL與Oracle有何不同?Apr 22, 2025 pm 05:57 PM

MySQL適合快速開發和中小型應用,Oracle適合大型企業和高可用性需求。 1)MySQL開源、易用,適用於Web應用和中小型企業。 2)Oracle功能強大,適合大型企業和政府機構。 3)MySQL支持多種存儲引擎,Oracle提供豐富的企業級功能。

與其他關係數據庫相比,使用MySQL的缺點是什麼?與其他關係數據庫相比,使用MySQL的缺點是什麼?Apr 22, 2025 pm 05:49 PM

MySQL相比其他關係型數據庫的劣勢包括:1.性能問題:在處理大規模數據時可能遇到瓶頸,PostgreSQL在復雜查詢和大數據處理上表現更優。 2.擴展性:水平擴展能力不如GoogleSpanner和AmazonAurora。 3.功能限制:在高級功能上不如PostgreSQL和Oracle,某些功能需要更多自定義代碼和維護。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

EditPlus 中文破解版

EditPlus 中文破解版

體積小,語法高亮,不支援程式碼提示功能

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

強大的PHP整合開發環境

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中

MantisBT

MantisBT

Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

mPDF

mPDF

mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),