仓酷云

标题: 发一篇MySQL数据库中对前端和背景举行体系优化 [打印本页]

作者: 愤怒的大鸟 时间: 2015-1-16 20:13
标题: 发一篇MySQL数据库中对前端和背景举行体系优化
如果你在一个遵循GPL的自由(开源)项目中使用MySQL，那么你可以遵循GPL协议使用MySQL。然而，如果你的项目不是在GPL协议下的话，你必须为使用MySQL来支付许可费用，或者你可能因为这个因素而将你的项目改为遵循GPL。本文中先容的体系优化，次要针对前端和背景这两方面（背景方面次要对SQL语句和数据存储举行了优化），下文中我们将先容一些优化技能和履历。<Pstyle="TEXT-INDENT:2em">技能：<Pstyle="TEXT-INDENT:2em">1.怎样查出效力低的语句？<Pstyle="TEXT-INDENT:2em">在MySQL下，在启动参数中设置--log-slow-queries=[文件名]，就能够在指定的日记文件中纪录实行工夫凌驾long_query_time（缺省为10秒）的SQL语句。你也能够在启动设置文件中修正longquery的工夫，如：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">#Setlongquerytimeto8seconds<Pstyle="TEXT-INDENT:2em">long_query_time=8<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">2.怎样查询某表的索引？<Pstyle="TEXT-INDENT:2em">可以使用SHOWINDEX语句，如：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">SHOWINDEXFROM[表名]<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">3.怎样查询某条语句的索引利用情形？<Pstyle="TEXT-INDENT:2em">可用EXPLAIN语句来看一下某条SELECT语句的索引利用情形。假如是UPDATE或DELETE语句，必要先转换为SELECT语句。<Pstyle="TEXT-INDENT:2em">4.怎样把导出INNODB引擎的内容到毛病日记文件中?<Pstyle="TEXT-INDENT:2em">我们可使用SHOWINNODBSTATUS命令来检察INNODB引擎的良多有效的信息，如以后历程、事件、外键毛病、逝世锁成绩和别的一些统计数据。怎样让该信息能纪录在日记文件中呢？只需利用以下语句创立innodb_monitor表，MySQL就会每15秒钟把该体系写进到毛病日记文件中：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">CREATETABLEinnodb_monitor(aINT)ENGINE=INNODB;<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">假如你不再必要导出到毛病日记文件，只需删除该表便可：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">DROPTABLEinnodb_monitor;<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">5.怎样按期删除复杂的日记文件？<Pstyle="TEXT-INDENT:2em">只需在启动设置文件中设置日记过时工夫便可：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">expire_logs_days=10<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">注重事项：<Pstyle="TEXT-INDENT:2em">1.重点存眷索引<Pstyle="TEXT-INDENT:2em">上面以表TSK_TASK表为例申明SQL语句优化历程。TSK_TASK表用于保留体系监测义务，相干字段及索引以下：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">ID：主键；<Pstyle="TEXT-INDENT:2em">MON_TIME：监测工夫；建了索引；<Pstyle="TEXT-INDENT:2em">STATUS_ID：义务形态；与SYS_HIER_INFO.ID创建了外键干系。<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">注MySQL主动会为外键创建索引，在本次优化过程当中，发明这些主动创建的外键索引会对SQL语句的效力发生不用要的搅扰，必要出格注重！<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">起首，我们在日记文件中查到上面语句的实行对照慢，凌驾10秒了：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">#Query_time:18Lock_time:0Rows_sent:295Rows_examined:88143select*fromTSK_TASKWHERESTATUS_ID=1064andMON_TIME>=2007-11-22andMON_TIME<2007-11-23;

<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">本来在88143笔记录中要查出切合前提的295笔记录，那固然慢了。赶忙用EXPLAIN语句看一下索引利用情形吧：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">+----+-------------+----------+------+----------<Pstyle="TEXT-INDENT:2em">|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|<Pstyle="TEXT-INDENT:2em">+----+-------------+----------+------+-----------<Pstyle="TEXT-INDENT:2em">|1|SIMPLE|TSK_TASK|ref|FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME|FK_task_status_id_TO_SYS_HIER_INFO|9|const|276168|Usingwhere|<Pstyle="TEXT-INDENT:2em">+----+-------------+----------+------+-----------<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">能够看出，有两个索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME，而终极实行语句时接纳了STATUS_ID上的外键索引。<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">再看一下TSK_TASK表的索引情形吧：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">+----------+------------------------------------<Pstyle="TEXT-INDENT:2em">|Table|Key_name|Column_name|Cardinality|<Pstyle="TEXT-INDENT:2em">+----------+------------+-----------------------<Pstyle="TEXT-INDENT:2em">|TSK_TASK|PRIMARY|ID|999149|<Pstyle="TEXT-INDENT:2em">|TSK_TASK|FK_task_status_id_TO_SYS_HIER_INFO|STATUS_ID|16|<Pstyle="TEXT-INDENT:2em">|TSK_TASK|TSK_TASK_KEY_MON_TIME|MON_TIME|13502|<Pstyle="TEXT-INDENT:2em">+----------+------------------------------------<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">在Oracle或其他干系数据库下，WHERE前提中的字段按次对索引的选择起着很主要的感化。我们调剂一下字段按次，把STATUS_ID放在前面，再EXPLAIN一下：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">EXPLAINselect*fromTSK_TASKWHEREMON_TIME>=2007-11-22andMON_TIME<2007-11-23andSTATUS_ID=1064;<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">可是没甚么效果，MySQL仍是选用体系创建的STATUS_ID外键索引。<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">细心剖析一下，看来Cardinality属性（即索引中的独一值的个数）对索引的选择起了极为主要的感化，MySQL选择了索引值独一值个数小的谁人索引作为整条语句的索引。<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">针对这条语句，假如利用FK_task_status_id_TO_SYS_HIER_INFO做索引，而TSK_TASK表中寄存良多天数据的话，那扫描的纪录数会良多，速率较慢。能够有以下几个优化计划：<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">假如一天的义务数未几的话，我们删除索引FK_task_status_id_TO_SYS_HIER_INFO，那MySQL会利用索引TSK_TASK_KEY_MON_TIME，然后在该天的数据中在扫描STATUS_ID为1064的纪录，那速率也不慢；<Pstyle="TEXT-INDENT:2em">假如一天的义务数多的话，我们需删除索引FK_task_status_id_TO_SYS_HIER_INFO和TSK_TASK_KEY_MON_TIME，然后再创建STATUS_ID,MON_TIME的团结索引，如许效力一定会很高。<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">因而倡议，对那些纪录数多的表，倡议不要利用外键，以免形成功能效力的严峻下降。<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">2.只管把持每张表的纪录数<Pstyle="TEXT-INDENT:2em">当一张表的纪录数很年夜时，办理和保护就会很贫苦，如索引保护就会占用很长工夫，从而会给体系的一般运转形成很年夜的搅扰。<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">对随工夫推移数据量不休增加的表，我们能够依据工夫来辨别及时数据和汗青数据，可使用背景服务程序按期挪动及时表中的数据到汗青表中，从而把持及时表的纪录数，进步查询和操纵效力。但注重每次挪动的工夫要充足短，不要影响一般程序的数据写进。假如占用工夫太长，大概会形成逝世锁成绩。<Pstyle="TEXT-INDENT:2em"><Pstyle="TEXT-INDENT:2em">3.数据散列(partition)战略当客户数到达必定范围后，单个数据库将没法支持更高的并发会见，此时能够思索把客户数据散列(partition)到多个数据库中，以分管负载，进步体系的全体功能与效力。
MySQL的低成本来自于其简单性吗?它的普及性是由于其低成本吗?其实，在MySQL的最“好”与最“不好”的功能之间没有明显的分界线，但它们组合在一起就形成了一副让我们欣赏的作品。

作者: 若相依 时间: 2015-1-18 18:54
sqlserver的痛苦之处在于有用文档的匮乏，很多只是表明的东西

作者: 透明 时间: 2015-1-25 22:17
对于微软系列的东西除了一遍遍尝试还真没有太好的办法

作者: 金色的骷髅 时间: 2015-2-4 08:59
多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

作者: 仓酷云 时间: 2015-2-9 21:06
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

作者: 深爱那片海 时间: 2015-2-27 21:49
如果你是从“学习某一种数据库应用软件，从而获得应聘的资本和工作机会”的角度来问的话。

作者: 柔情似水 时间: 2015-3-9 14:55
然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

作者: 若天明 时间: 2015-3-17 00:11
两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书

作者: 分手快乐 时间: 2015-3-23 10:12
语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！

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