仓酷云

标题: 公布MySQL优化全攻略-相干数据库号令 [打印本页]

作者: 深爱那片海 时间: 2015-2-16 00:23
标题: 公布MySQL优化全攻略-相干数据库号令
一些典型的RDBMS功能并不总是在DBaaS系统中可用。例如MySQL学习教程，Windows Azure SQL Database(以前的SQL Azure)是微软的DBaaS产品，提供了一个类似于SQL Server的数据库平台。接上去咱们要会商的是数据库功能优化的另外一方面，即应用数据库办事器内建的东西帮助功能剖析和优化。

　　 ▲ SHOW

　　履行上面这个号令可以懂得办事器的运转形态：

MySQL >show status;

　　该号令将显示出一长列形态变量及其对应的值，个中包含：被中断会见的用户数目，被中断的毗连数目，测验考试毗连的次数，并发毗连数目最大值，和其他很多有效的信息。这些信息关于肯定体系成绩和效力低下的缘由是非常有效的。

　　 SHOW号令除可以显示出MySQL办事器全体形态信息以外，它还可以显示出有关日记文件、指定命据库、表、索引、历程和允许权限表的名贵信息。请会见http://www.mysql.com/doc/S/H/SHOW.html懂得更多信息。

　　 ▲ EXPLAIN
　　 EXPLAIN可以剖析SELECT号令的处置进程。这不但关于决意是不是要为表加上索引很有效，并且关于懂得MySQL处置庞杂毗连的进程也很有效。

　　上面这个例子显示了若何用EXPLAIN供应的信息慢慢地优化毗连查询。（本例来自MySQL文档，见http://www.mysql.com/doc/E/X/EXPLAIN.html。原文写到这里仿佛有点潦草了事，特加上此例。）

　　假定用EXPLAIN剖析的SELECT号令以下所示：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
   tt.PRojectReference, tt.EstimatedShipDate,
   tt.ActualShipDate, tt.ClientID,
   tt.ServiceCodes, tt.RepetitiveID,
   tt.CurrentProcess, tt.CurrentDPPerson,
   tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
   et_1.COUNTRY, do.CUSTNAME
   FROM tt, et, et AS et_1, do
   WHERE tt.SubmitTime IS NULL
   AND tt.ActualPC = et.EMPLOYID
   AND tt.AssignedPC = et_1.EMPLOYID
   AND tt.ClientID = do.CUSTNMBR;

　　 SELECT号令中呈现的表界说以下：

　　 ※表界说

表列列类型
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对SELECT履行剖析的了局以下：

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,ClientID,ActualPC NULL NULL  NULL 3872
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,ClientID,ActualPC NULL NULL NULL 3872  where used
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

　　这还算不上完善，但已很多多少了（行数的乘积如今少了一个系数74）。如今这个SQL号令履行也许需求数秒钟工夫。

　　为了不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,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1 eq_ref PRIMARY    PRIMARY 15 tt.AssignedPC 1
do  eq_ref PRIMARY    PRIMARY 15 tt.ClientID  1

　　这个了局已对照使人写意了。

　　余下的成绩在于，默许情形下，MySQL假定tt.ActualPC列的值平均散布，而现实上tt表的情形并不是如斯。幸而，咱们可以很轻易地让MySQL晓得这一点：

shell > myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell > mysqladmin refresh

　　如今这个毗连操作已十分幻想，EXPLAIN剖析的了局以下：

table type  possible_keys  key key_len ref    rows  Extra
tt  ALL  AssignedPC,ClientID,ActualPC NULL NULL NULL  3872  where used
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

　　 ▲ OPTIMIZE

　　 OPTIMIZE可以恢复和收拾整顿磁盘空间和数据碎片，一旦对包括变长行的表停止了大批的更新或删除，停止这个操作就十分有需要了。OPTIMIZE以后只能用于MyISAM和BDB表。

　　停止语：从编译数据库办事器入手下手、贯串全部办理进程，可以改良MySQL功能的要素其实十分多，本文只触及了个中很小的一局部。虽然如斯，咱们但愿本文会商的内容可以对你有所匡助。

//copy者注：
工夫不敷，所以格局上有点成绩～～，请人人看具体的英文原文：http://www.devshed.com/Server_Side/MySQL/Optimize/
或看看chinabyte的文章好了：
http://www.chinabyte.com/builder/detail.shtm?buiid=1012&parid=1

哈哈～从这点能不克不及看出来我是一心一意为人人办事的通过支付一定费用，客户可以得到优先的24/7支持，访问内容丰富的在线知识库和联系一个专门的技术负责经理。

作者: 冷月葬花魂 时间: 2015-2-16 00:36
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

作者: 小女巫 时间: 2015-2-16 04:30
这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？

作者: 简单生活 时间: 2015-2-20 18:48
每天坚持做不一样的是，认真做笔录，定时复习。一个月你就可以有一定的收获。当然如果你想在sql方面有一定的造诣，你少不了需要看很多很多的书籍了。

作者: 兰色精灵 时间: 2015-3-6 18:38
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

作者: 活着的死人 时间: 2015-3-13 05:11
索引视图2k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

作者: 再现理想 时间: 2015-3-20 13:41
如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。

作者: 谁可相欹 时间: 2015-3-25 02:34
两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书

作者: 愤怒的大鸟 时间: 2015-4-3 14:17
大侠们有推荐的书籍和学习方法写下吧。

作者: 若天明 时间: 2015-4-11 02:08
一个是把SQL语句写到客户端,可以使用DataSet进行加工;

作者: 灵魂腐蚀 时间: 2015-4-17 21:57
是要和操作系统进行Socket通讯的场景。否则建议慎重！

作者: 乐观 时间: 2015-4-25 03:32
始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。

作者: 分手快乐 时间: 2015-4-25 05:08
一个是把SQL语句写到客户端,可以使用DataSet进行加工;

作者: 莫相离 时间: 2015-5-1 05:12
只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

作者: 飘灵儿 时间: 2015-5-4 14:15
但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

作者: 精灵巫婆 时间: 2015-5-9 12:09
发几份SQL课件，以飨阅者

作者: 柔情似水 时间: 2015-5-11 06:30
外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

作者: 深爱那片海 时间: 2015-5-12 13:52
一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

作者: admin 时间: 2015-6-18 10:49
所以你总能得到相应的升级版本，来满足你的需求。

作者: 老尸 时间: 2015-6-27 00:21
原来公司用过MYSQL自己也只是建个表写个SQL

欢迎光临仓酷云 (http://ckuyun.com/)