range checked for each record (key map: 35)
因为type对每张表是ALL,这个输出显示MySQL正在对所有表产生一个笛卡尔乘积;即每一个行的组合!这将花相当长的时间,因为必须检查每张表的行数的乘积!对于一个实例,这是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大,你只能想象它将花多长时间……
这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。在本文中,VARCHAR和CHAR是相同的,除非它们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15),长度不匹配。
为了修正在列长度上的不同,使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15),再执行EXPLAIN语句产生这个结果:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC, NULL NULL NULL 3872 Using
ClientID, where
ActualPC
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
这不是完美的,但是好一些了:rows值的乘积少了一个因子74。这个版本在几秒内执行完。
第2种方法能消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列的长度失配问题:
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
-> MODIFY ClientID VARCHAR(15);
EXPLAIN产生的输出显示在下面:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using
ClientID, where
ActualPC
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
这几乎很好了。
如果数据库慢了,想要看看mysql正在干什么,可以执行这个语句,可以列出mysql当前连接在执行的sql语句。
………………………………………………………………
以下内容摘自手册
SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程(也就是,与您正在使用的MySQL账户相关的线程)。如果您不使用FULL关键词,则只显示每个查询的前100个字符。
本语句报告TCP/IP连接的主机名称(采用host_name:client_port格式),以方便地判定哪个客户端正在做什么。
如果您得到“too many connections”错误信息,并且想要了解正在发生的情况,本语句是非常有用的。MySQL保留一个额外的连接,让拥有SUPER权限的账户使用,以确保管理员能够随时连接和检查系统(假设您没有把此权限给予所有的用户)。
在来自SHOW PROCESSLIST的输出中常见的一些状态:
· Checking table
线程正在执行(自动)表格检查。
· Closing tables
意味着线程正在刷新更改后的表数据,并正在关闭使用过的表。这应该是一个快速的操作。如果不快,则您应该验证您的磁盘没有充满,并且磁盘没有被超负荷使用。
· Connect Out
· Copying to tmp table on disk
临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式,以节约存储器。
· Creating tmp table
线程正在创建一个临时表,以保持部分结果。
· deleting from main table
服务器正在执行多表删除的第一部分,只从第一个表中删除。
· deleting from reference tables
服务器正在执行多表删除的第二部分,从其它表中删除匹配的行。
· Flushing tables
线程正在执行FLUSH TABLES,并正在等待所有线程,以关闭表。
· FULLTEXT initialization
服务器正在准备执行一个自然语言全文本搜索。
· Killed
有人已经向线程发送了一个KILL命令。在下一次检查终止标记时,应放弃。该标记在MySQL的每个大循环中都检查,但是在有些情况下,线程终止只需要较短的时间。如果该线程被其它线程锁定,则只要其它线程接触锁定,终止操作就会生效。
· Locked
该查询被其它查询锁定。
· Sending data
线程正在为SELECT语句处理行,同时正在向客户端发送数据。
· Sorting for group
线程正在进行分类,以满足GROUP BY要求。
· Sorting for order
线程正在进行分类,以满足ORDER BY要求。
· Opening tables
线程正在试图打开一个表。这应该是非常快的过程,除非打开操作受到阻止。例如,一个ALTER TABLE或一个LOCK TABLE语句可以阻止打开一个表,直到语句完成为止。
· Removing duplicates
查询正在使用SELECT DISTINCT。使用时,在早期阶段,MySQL不能优化不同的操作。因此,MySQL要求一个额外的阶段,以便在把结果发送给客户端之前取消所有的复制行。
· Reopen table
线程得到一个表锁定,但是在得到锁定后被通知带下方的表结构已更改了。它已经释放了锁定,关闭了表,并试图重新打开它。
· Repair by sorting
修复代码正在使用一个分类来创建索引。
· Repair with keycache
修复代码正在通过关键缓存一个接一个地使用创建关键字。这比通过分类修复要慢很多。
· Searching rows for update 线程正在进行第一阶段,以在更新之前,查找所有匹配的行。如果UPDATE正在更改用于查找相关行的索引,则必须这么做。 · Sleeping 线程正在等待客户端,以向它发送一个新语句。 · System lock 线程正在等待得到一个用于表的外部系统锁定。如果您没有正在使用多个正在访问同一个表的mysqld服务器,则您可以使用--skip-external-locking选项禁用系统锁定。 · Upgrading lock INSERT DELAYED管理程序正在试图得到一个表锁定,以插入行。 · Updating 线程正在搜索行,并正在更新这些行。 · User Lock 线程正在等待GET_LOCK()。 · Waiting for tables 线程得到一个通知,表的底层结构已经改变,需要重新打开表以得到新的结构。但是,为了能重新打开表,必须等待,直到所有其它的线程已经关闭了正在被质询的表。 如果其它线程已经对正在被质询的表使用了FLUSH TABLES或以下语句之一:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE或OPTIMIZE TABLE;则会出现通知。 · waiting for handler insert INSERT DELAYED管理程序已经处理了所有处于等待状态的插入,并正在等待新插入。 多数状态对应于非常快的操作。如果一个线程在这些状态下停留了数秒,则可能是有问题,需要进行调查。 有一些其它的状态,在前面的清单中没有提及,但是其中有很多状态对于查找服务器中的程序错误是有用的。 从这里可以看到是不是有表锁死了,是不是有些语句执行了很久,甚至可以通过kill id 语句灭了捣乱的连接。
剩下的问题是,默认情况,MySQL假设在tt.ActualPC列的值是均匀分布的,并且对tt表不是这样。幸好,很容易告诉MySQL来分析关键字分布:
mysql> ANALYZE TABLE tt;
现在联接是“完美”的了,而且EXPLAIN产生这个结果:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using
ClientID, where
ActualPC
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
注意在从EXPLAIN输出的rows列是一个来自MySQL联接优化器的“教育猜测”。你应该检查数字是否接近事实。如果不是,可以通过在SELECT语句里面使用STRAIGHT_JOIN并且试着在FROM子句以不同的次序列出表,可能得到更好的性能。
我们需要特别注意的是key、Extra字段。Key字段显示用了哪个索引,注意看是不是用了索引,如果用了是不是效率最高的。Extra字段会说明是否用了临时表,是否用了基于磁盘的临时表,是否用了文件排序,是否用了全索引扫描,where是有数据库engine产生的还是由数据库server限制的。
发表评论
-
MySQL 优化总结 (五)
2009-05-31 14:52 795Join Vs select 结果集作列 ... -
MySQL 优化总结 (四)
2009-05-31 14:47 738减少不必要的表关联 有时候一个查询需要很 ... -
MySQL 优化总结 (二)
2009-05-31 14:39 1006Sql语句优化 Sql语句优化工具 ... -
堆排序算法原理以及实例代码
2009-02-17 21:11 29531、 堆排序定义 n个关键 ... -
MyEclipse 6.5 最新下载、注册、汉化
2008-11-11 14:36 12783因最近发现网上很多转 ... -
SCJP 310-055 资料汇总 (不断更新中……)
2008-11-05 17:05 1007SCJP在线练习 http://www.java3z.com ...
相关推荐
MySQL性能优化总结.pdf
MySQL性能优化是一个涵盖广泛的主题,涉及数据库设计、SQL查询优化、索引策略等多个方面。以下是对标题和描述中提到的一些关键知识点的详细说明: 1. **表的优化**: - **定长与变长字段的分离**:将定长字段(如...
MySQL 性能优化总结 MySQL 性能优化是数据库管理和开发人员需要掌握的重要技能。性能优化的目标是让查询更快,减少查询所消耗的时间。为了达到这个目标,我们需要从每一个环节入手,包括连接、配置优化、索引优化、...
### MySQL优化技巧总结 #### 一、MySQL慢查询日志(Slow Query Log)与mysqldumpslow工具 **慢查询日志**是MySQL提供的一种非常实用的功能,它能够帮助我们记录并分析那些执行时间较长的SQL语句,进而找出性能瓶颈并...
Mysql基础性能优化思维导向图 (其中包括:mysql基础、mysql性能优化、mysql锁机制和主从复制) 文件名称:MySQL基础与性能优化总结.xmind
课程内容进行了精华的浓缩,有四大内容主旨,MySQL架构与执行流程,MySQL索引原理详解,MySQL事务原理与事务并发,MySQL性能优化总结与MySQL配置优化。课程安排的学习的教程与对应的学习课件,详细的学习笔以及课程...
MySQL优化 MySQL优化是数据库管理中的一个重要...总结而言,MySQL优化是一个涉及多个层面的复杂过程,需要综合考虑配置、索引、查询语句、服务器状态等多方面因素,通过合理配置和优化,以达到提升数据库性能的目的。
### MySQL优化知识点详解 #### 一、MySQL简介 MySQL是一款由MySQL AB公司开发的开源数据库管理系统,后来该公司被Sun Microsystems收购。MySQL因其简单、高效、可靠的特点,在IT行业中迅速崭露头角,成为最受欢迎...
总结,MySQL性能优化与架构设计涵盖了许多方面,包括查询优化、索引策略、数据库设计、缓存利用、并行处理、架构设计、数据分布以及监控与调优工具的使用。理解和掌握这些知识点,能够帮助我们构建高效、稳定的...
以下是对MySQL优化的详细总结,供您参考学习。 一、查询优化 1. **索引优化**:索引能显著提高查询速度。应为经常用于搜索的列创建索引,特别是主键和外键。复合索引在多条件查询时特别有用,但要注意避免冗余和...
MySQL 5.6 性能优化总结 MySQL 5.6 是一个高性能的关系型数据库管理系统,然而随着数据库规模的增长和复杂度的增加,性能问题开始浮现。因此,性能优化成为 MySQL 数据库管理员和开发者的首要任务。本文将总结 ...
### MySQL优化实战视频课程知识点概览 #### 一、MySQL优化基础 ##### 1.1 数据库优化的重要性 - **背景介绍**:随着互联网技术的发展,数据量呈指数级增长,对数据库系统的性能要求越来越高。 - **核心价值**:...
MySQL优化是数据库管理中至关重要的环节,它关系到系统性能和响应速度。下面将详细介绍MySQL自带的慢查询日志分析工具mysqldumpslow及其使用方法,以及如何使用EXPLAIN来分析SQL查询。 首先,MySQL的慢查询日志...
Mysql数据库优化总结-飞鸿无痕-ChinaUnix博客................................................................................................................
### 2G内存的MySQL数据库服务器优化 在IT行业中,对于资源有限的环境进行数据库优化是一项挑战性工作,尤其是在仅有2GB内存的情况下对MySQL数据库服务器进行优化。这种优化旨在提高性能的同时确保系统的稳定运行。 ...
第5课 查询优化技术理论与MySQL实践(三)------视图重写与等价谓词重写 什么是视图重写?哪些类型的视图可以被优化?MySQL是怎么优化视图的?从而明白在MySQL中怎么写与视图相关的查询语句才能有好的效果? 什么是...
#### 一、什么是MySQL优化? MySQL优化是指通过合理安排资源和调整系统参数,使得MySQL运行得更快、更节省资源的过程。优化的目的在于减少系统瓶颈,降低资源消耗,提升系统的响应速度。 #### 二、优化的主要方面 ...
#### 三、查询优化 在MySQL中,查询优化是一项关键的技术。通过合理的查询优化,可以显著提高查询效率,降低数据库的负载。其中,`EXPLAIN`语句是一种常用的工具,用于分析SQL查询的执行计划。 ##### 使用`EXPLAIN...