mysql怎么优化语句提高速度

mysql如何优化语句提高速度

列

列类型

tt

ActualPC

CHAR(10)

tt

AssignedPC

CHAR(10)

tt

ClientID

CHAR(10)

et

EMPLOYID

CHAR(15)

do

CUSTNMBR

CHAR(15)

·?????????表有下面的索引：

表

索引

tt

ActualPC

tt

AssignedPC

tt

ClientID

et

EMPLOYID(主键)

do

CUSTNMBR(主键)

·?????????tt.ActualPC值不是均匀分布的。

开始，在进行优化前，EXPLAIN语句产生下列信息：

?

table type possible_keys key? key_len ref? rows? Extra

et??? ALL? PRIMARY?????? NULL NULL??? NULL 74

do??? ALL? PRIMARY?????? NULL NULL??? NULL 2135

et_1? ALL? PRIMARY?????? NULL NULL??? NULL 74

tt??? ALL? AssignedPC,?? NULL NULL??? NULL 3872

?????????? ClientID,

?????????? ActualPC

????? 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假设在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子句以不同的次序列出表，可能得到更好的性能。

该节列出了提高查询速度的各种技巧：

·?????????使用持久的连接数据库以避免连接开销。如果不能使用持久的连接并且你正启动许多新的与数据库的连接，可能要更改thread_cache_size变量的值。参见7.5.2节，“调节服务器参数”。

·?????????总是检查所有查询确实使用已经在表中创建了的索引。在MySQL中，可以用EXPLAIN命令做到。参见7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。

·?????????尝试避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题。

·?????????对于没有删除的行的MyISAM表，可以在另一个查询正从表中读取的同时在末尾插入行。如果这很重要，应考虑按照避免删除行的方式使用表。另一个可能性是在删除大量行后运行OPTIMIZE TABLE。参见15.1节，“MyISAM存储引擎”。

·?????????要修复任何ARCHIVE表可以发生的压缩问题，可以执行OPTIMIZE TABLE。参见15.8节，“ARCHIVE存储引擎”。

·?????????如果你主要按expr1，expr2，...顺序检索行，使用ALTER TABLE ... ORDER BY?expr1,?expr2, ...。对表大量更改后使用该选项，可以获得更好的性能。

·?????????在一些情况下，使得基于来自其它表的列的信息引入一个“ 哈希”的列有意义。如果该列较短并且有合理的唯一值，它可以比在许多列上的一个大索引快些。在MySQL中，很容易使用这个额外列：

·??????????????? SELECT * FROM tbl_name

·??????????????? ?????WHERE hash_col=MD5(CONCAT(col1,col2))

·??????????????? ?????AND col1='constant' AND col2='constant';

·?????????对于频繁更改的MyISAM表，应试图避免所有变长列(VARCHAR、BLOB和TEXT)。如果表包括单一的变长列则使用动态记录格式。参见第15章：存储引擎和表类型。

·?????????只是因为行太大，将一张表分割为不同的表一般没有什么用处。为了访问行，最大的性能冲击是磁盘搜索以找到行的第一个字节。在找到数据后，大多数新型磁盘对大多数应用程序来说足够快，能读入整个行。确实有必要分割的唯一情形是如果它是使用动态记录格式使之变为固定的记录大小的MyISAM表(见上述)，或如果你需要很频繁地扫描表而不需要大多数列。参见第15章：存储引擎和表类型。

·?????????如果你需要很经常地计算结果，例如基于来自很多行的信息的计数，引入一个新表并实时更新计数器可能更好一些。下面形式的更新会更快一些：

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

当你使用象MyISAM那样的只有表级锁定的MySQL存储引擎(多重读/单个写)时，这确实很重要。这也给大多数数据库较好的性能，因为行锁定管理器在这种情况下有较少的事情要做。

·?????????如果你需要从大的记录文件表中收集统计信息，使用总结性的表而不是扫描整个表。维护总结应该比尝试做“实时”统计要快些。当有变化时从日志重新生成新的总结表比改变运行的应用(取决于业务决策)要快得多。

• 如果可能，应该将报告分类为“实时”或“统计”，这里统计报告所需的数据仅仅基于从实际数据定期产生的总结表中产生。
• 充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。
• 在一些情况下，包装并存储数据到一个BLOB列中是很方便的。在这种情况下，必须在你的应用中增加额外的代码来打包/解包信息，但是这种方法可以在某些阶段节省很多访问。当有不符合行和列表结构的数据时，这很实用。
• 在一般情况下，应该尝试以非冗余方式(查看数据库理论中的第三正则形式)保存数据，但是为了获得更快的速度，可以复制信息或创建总结表。
• 存储过程或UDF(用户定义函数)可能是获得更好性能的一个好方法，详细信息参见第20章：存储程序和函数和27.2节，“为MySQL添加新函数”。
• 总是能通过在应用程序中缓存查询/答案并尝试同时执行很多插入/更新来获得一些好处。如果数据库支持锁定表(象MySQL和Oracle)，这应该有助于确保索引缓存只在所有更新后刷新一次。还可以利用MySQL的查询缓存来获得类似的结果；参见5.13节，“MySQL查询高速缓冲”。
• 当不需要知道何时写入数据时，使用INSERT DELAYED。这样可以加快处理，因为很多记录可以通过一次磁盘写入被写入。
• 当你想要让选择显得更重要时，使用INSERT /*! LOW_PRIORITY */。
• 使用INSERT LOW_PRIORITY来取得插入队列的检索，也就是即使有另一个客户等待写入也要执行SELECT。
• 使用多行INSERT语句通过一个SQL命令来存储很多行(许多SQL服务器支持它，包括MySQL)。
• 使用LOAD DATA INFILE装载较大数量的数据。这比使用INSERT要快得多。
• 使用AUTO_INCREMENT列构成唯一值。
• 当MyISAM使用动态表格式时，偶尔使用OPTIMIZE TABLE可以避免碎片。参见15.1.3节，“MyISAM表的存储格式”。
• 可能时使用MEMORY表以得到更快的速度。参见15.4节，“MEMORY (HEAP)存储引擎”。
• 在Web服务器中，图象和其它二进制资产应该作为文件存储。也就是仅在数据库中存储的本文件的引用而不是文件本身。大多数Web服务器在缓存文件方面比数据库内容要好得多，因此使用文件一般要快得多。
• 对经常访问的不重要数据(如为没有在Web?浏览器中启用cookie的用户最后显示的标语的相关信息)使用内存表。在许多Web应用程序环境中也可以使用用户会话来处理可变状态数据。
• 在不同表中具有相同信息的列应该被声明为相同的并有相同的名字。尝试使名字简单化。例如，在customer表中使用name而不是customer_name。为了使名字能移植到其它SQL服务器，应该使名字短于18个字符。
• 如果确实需要很高的速度，应该研究一下不同SQL服务器支持的数据存储的低层接口！例如直接访问MySQL?MyISAM存储引擎，比起使用SQL接口，速度可以提高2-5倍。为了能实现，数据必须与应用程序在同一台服务器上，并且通常只应该被一个进程访问(因为外部文件锁定确实很慢)。通过在MySQL服务器中引进低层MyISAM命令能消除以上问题(如果需要，这可能是获得更好性能的一个简单的方法)。通过精心设计数据库接口，应该能相当容易地支持这类优化。
• 如果正使用数字数据，在许多情况下，从一个数据库访问信息(使用实时连接)比访问一个文本文件快些。这是因为数据库中的信息比文本文件更紧凑，因此这将涉及更少的磁盘访问。还可以在应用程序中节省代码，因为不须分析文本文件来找出行和列的边界。

  ·?????????通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份时主服务器变慢，可以使用一个从服务器来备份。参见第6章：MySQL中的复制。

  ·?????????用DELAY_KEY_WRITE=1选项声明MyISAM表可以使索引更新更快，因为在表关闭之前它们不刷新到硬盘上。不利之处是当表打开时如果杀掉服务器，应确保用--myisam-recover选项运行服务器保证没有问题，或者在重启服务器之前运行myisamchk。(然而，即使在这种情况下，应通过使用DELAY_KEY_WRITE保证不丢失数据，因为关键字信息总是可以从数据行产生）。