| 订阅 | 在线投稿
分享
 
 
 

MySQL数据库中对前端和后台进行系统优化

来源:互联网  宽屏版  评论
2008-06-01 03:05:23

本文为【MySQL数据库中对前端和后台进行系统优化】的汉字拼音对照版显示拼音

benwenzhongjieshaodexitongyouhuazhuyaozhenduiqianduanhehoutaizheliangfangmianhoutaifangmianzhuyaoduiSQLyujuheshujucunchujinxingleyouhuaxiawenzhongwomenjiangjieshaoyixieyouhuajiqiaohejingyan

jiqiao

1. ruhechachuxiaolvdideyuju

zaiMySQLxiazaiqidongshenshuzhongshezhi --log-slow-queries=[wenjianming]jiukeyizaizhidingderizhiwenjianzhongjiluzhixingshijianchaoguolong_query_timequeshengwei10miaodeSQLyujuniyekeyizaiqidongpeizhiwenjianzhongxiugailong querydeshijianru

# Set long query time to 8 seconds

long_query_time=8

2. ruhechaxunmoubiaodesuoyin

ke使shiyongSHOW INDEXyujuru

SHOW INDEX FROM [biaoming]

3. ruhechaxunmoutiaoyujudesuoyin使shiyongqingkuang

keyongEXPLAINyujulaikanyixiamoutiaoSELECTyujudesuoyin使shiyongqingkuangruguoshiUPDATEhuoDELETEyujuxuyaoxianzhuanhuanweiSELECTyuju

4. ruhebadaochuINNODByinqingdeneirongdaocuowurizhiwenjianzhong?

womenkeyi使shiyongSHOW INNODB STATUSminglinglaichakanINNODByinqingdehenduoyouyongdexinxirudangqianjinchengshiwuwaijiancuowusisuowentiheqitayixietongjishujuruheranggaixinxinengjiluzairizhiwenjianzhong nezhiyao使shiyongruxiayujuchuangjianinnodb_monitorbiaoMySQLjiuhuimei15miaozhongbagaixitongxierudaocuowurizhiwenjianzhong

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

ruguonibuzaixuyaodaochudaocuowurizhiwenjianzhiyaoshanchugaibiaojike

DROP TABLE innodb_monitor;

5. ruhedingqishanchupangdaderizhiwenjian

zhiyaozaiqidongpeizhiwenjianzhongshezhirizhiguoqishijianjike

expire_logs_days=10

zhuyishixiang

1. chongdianguanzhusuoyin

xiamianyibiaoTSK_TASKbiaoweilishuomingSQLyujuyouhuaguochengTSK_TASKbiaoyongyubaocunxitongjiancerenwuxiangguanziduanjisuoyinruxia

IDzhujian

MON_TIMEjianceshijianjianlesuoyin

STATUS_IDrenwuzhuangtaiyuSYS_HIER_INFO.IDjianlilewaijianguanxi

zhuMySQLzidonghuiweiwaijianjianlisuoyinzaibenciyouhuaguochengzhongfaxianzhexiezidongjianlidewaijiansuoyinhuiduiSQLyujudexiaolvchanshengbubiyaodeganraoxuyaotebiezhuyi

shouxianwomenzairizhiwenjianzhongchadaoxiamianyujudezhixingbijiaomanchaoguo10miaole

# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143

select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23';

yuanlaizai88143tiaojiluzhongyaochachufuhetiaojiande295tiaojilunadangranmanleganjinyongEXPLAINyujukanyixiasuoyin使shiyongqingkuangba

+----+-------------+----------+------+----------

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------+------+-----------

| 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 | Using where |

+----+-------------+----------+------+-----------

keyikanchuyoulianggesuoyinkeyongFK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIMEerzuizhongzhixingyujushicaiyongleSTATUS_IDshangdewaijiansuoyin

zaikanyixiaTSK_TASKbiaodesuoyinqingkuangba

+----------+------------------------------------

| Table | Key_name | Column_name | Cardinality |

+----------+------------+-----------------------

| TSK_TASK | PRIMARY | ID | 999149 |

| TSK_TASK | FK_task_status_id_TO_SYS_HIER_INFO | STATUS_ID | 16 |

| TSK_TASK | TSK_TASK_KEY_MON_TIME | MON_TIME | 13502 |

+----------+------------------------------------

zaiOraclehuoqitaguanxishujukuxiaWHEREtiaojianzhongdeziduanshunxuduisuoyindexuanzeqizhehenchongyaodezuoyongwomentiaozhengyixiaziduanshunxubaSTATUS_IDfangzaihoumianzaiEXPLAINyixia

EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064;

danshimeishenmexiaoguoMySQLhuanshixuanyongxitongjianlideSTATUS_IDwaijiansuoyin

zaixifenxiyixiakanlaiCardinalityshuxingjisuoyinzhongdeweiyizhidegeshuduisuoyindexuanzeqilejiqichongyaodezuoyongMySQLxuanzelesuoyinzhiweiyizhigeshuxiaodenagesuoyinzuoweizhengtiaoyujudesuoyin

zhenduizhetiaoyujuruguo使shiyongFK_task_status_id_TO_SYS_HIER_INFOzuosuoyinerTSK_TASKbiaozhongcunfanghenduotianshujudehuanasaomiaodejilushuhuihenduosudujiaomankeyiyouyixiajigeyouhuafangan

ruguoyitianderenwushubuduodehuawomenshanchusuoyinFK_task_status_id_TO_SYS_HIER_INFOnaMySQLhui使shiyongsuoyinTSK_TASK_KEY_MON_TIMEranhouzaigaitiandeshujuzhongzaisaomiaoSTATUS_IDwei1064dejilunasuduyebuman

ruguoyitianderenwushuduodehuawomenxushanchusuoyinFK_task_status_id_TO_SYS_HIER_INFOheTSK_TASK_KEY_MON_TIMEranhouzaijianliSTATUS_ID,MON_TIMEdelianhesuoyinzheyangxiaolvkendinghuihengao

yincijianyiduinaxiejilushuduodebiaojianyibuyao使shiyongwaijianyibimianzaochengxingnengxiaolvdeyanchongjiangdi

2. jinliangkongzhimeizhangbiaodejilushu

dangyizhangbiaodejilushuhendashiguanliheweihujiuhuihenmafanrusuoyinweihujiuhuizhanyonghenchangshijiancongerhuigeixitongdezhengchangyunxingzaochenghendadeganrao

duisuishijiantuiyishujuliangbuduanzengchangdebiaowomenkeyigenjushijianlaiqufenshishishujuhelishishujukeyi使shiyonghoutaifuwuchengxudingqiyidongshishibiaozhongdeshujudaolishibiaozhongcongerkongzhishishibiaode jilushutigaochaxunhecaozuoxiaolvdanzhuyimeiciyidongdeshijianyaozugouduanbuyaoyingxiangzhengchangchengxudeshujuxieruruguozhanyongshijiantaichangkenenghuizaochengsisuowenti

3. shujusanlie(partition)celue

dangkehushudadaoyidingguimohoudangeshujukujiangwufazhichenggenggaodebingfa访fangwencishikeyikaolvbakehushujusanlie(partition)daoduogeshujukuzhongyifendanfuzaitigaoxitongdezhengtixingnengyuxiaolv

原文
 
本文中介绍的系统优化,主要针对前端和后台这两方面(后台方面主要对SQL语句和数据存储进行了优化),下文中我们将介绍一些优化技巧和经验。 技巧: 1. 如何查出效率低的语句? 在MySQL下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的SQL语句。你也可以在启动配置文件中修改long query的时间,如: # Set long query time to 8 seconds long_query_time=8 2. 如何查询某表的索引? 可使用SHOW INDEX语句,如: SHOW INDEX FROM [表名] 3. 如何查询某条语句的索引使用情况? 可用EXPLAIN语句来看一下某条SELECT语句的索引使用情况。如果是UPDATE或DELETE语句,需要先转换为SELECT语句。 4. 如何把导出INNODB引擎的内容到错误日志文件中? 我们可以使用SHOW INNODB STATUS命令来查看INNODB引擎的很多有用的信息,如当前进程、事务、外键错误、死锁问题和其它一些统计数据。如何让该信息能记录在日志文件中 呢?只要使用如下语句创建innodb_monitor表,MySQL就会每15秒钟把该系统写入到错误日志文件中: CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB; 如果你不再需要导出到错误日志文件,只要删除该表即可: DROP TABLE innodb_monitor; 5. 如何定期删除庞大的日志文件? 只要在启动配置文件中设置日志过期时间即可: expire_logs_days=10 注意事项: 1. 重点关注索引 下面以表TSK_TASK表为例说明SQL语句优化过程。TSK_TASK表用于保存系统监测任务,相关字段及索引如下: ID:主键; MON_TIME:监测时间;建了索引; STATUS_ID:任务状态;与SYS_HIER_INFO.ID建立了外键关系。 注MySQL自动会为外键建立索引,在本次优化过程中,发现这些自动建立的外键索引会对SQL语句的效率产生不必要的干扰,需要特别注意! 首先,我们在日志文件中查到下面语句的执行比较慢,超过10秒了: # Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143 select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23'; 原来在88143条记录中要查出符合条件的295条记录,那当然慢了。赶紧用EXPLAIN语句看一下索引使用情况吧: +----+-------------+----------+------+---------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+----------- | 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 | Using where | +----+-------------+----------+------+----------- 可以看出,有两个索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME,而最终执行语句时采用了STATUS_ID上的外键索引。 再看一下TSK_TASK表的索引情况吧: +----------+------------------------------------ | Table | Key_name | Column_name | Cardinality | +----------+------------+----------------------- | TSK_TASK | PRIMARY | ID | 999149 | | TSK_TASK | FK_task_status_id_TO_SYS_HIER_INFO | STATUS_ID | 16 | | TSK_TASK | TSK_TASK_KEY_MON_TIME | MON_TIME | 13502 | +----------+------------------------------------ 在Oracle或其他关系数据库下,WHERE条件中的字段顺序对索引的选择起着很重要的作用。我们调整一下字段顺序,把STATUS_ID放在后面,再EXPLAIN一下: EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064; 但是没什么效果,MySQL还是选用系统建立的STATUS_ID外键索引。 仔细分析一下,看来Cardinality属性(即索引中的唯一值的个数)对索引的选择起了极其重要的作用,MySQL选择了索引值唯一值个数小的那个索引作为整条语句的索引。 针对这条语句,如果使用FK_task_status_id_TO_SYS_HIER_INFO做索引,而TSK_TASK表中存放很多天数据的话,那扫描的记录数会很多,速度较慢。可以有以下几个优化方案: 如果一天的任务数不多的话,我们删除索引FK_task_status_id_TO_SYS_HIER_INFO,那MySQL会使用索引TSK_TASK_KEY_MON_TIME,然后在该天的数据中在扫描STATUS_ID为1064的记录,那速度也不慢; 如果一天的任务数多的话,我们需删除索引FK_task_status_id_TO_SYS_HIER_INFO和TSK_TASK_KEY_MON_TIME,然后再建立STATUS_ID,MON_TIME的联合索引,这样效率肯定会很高。 因此建议,对那些记录数多的表,建议不要使用外键,以避免造成性能效率的严重降低。 2. 尽量控制每张表的记录数 当一张表的记录数很大时,管理和维护就会很麻烦,如索引维护就会占用很长时间,从而会给系统的正常运行造成很大的干扰。 对随时间推移数据量不断增长的表,我们可以根据时间来区分实时数据和历史数据,可以使用后台服务程序定期移动实时表中的数据到历史表中,从而控制实时表的 记录数,提高查询和操作效率。但注意每次移动的时间要足够短,不要影响正常程序的数据写入。如果占用时间太长,可能会造成死锁问题。 3. 数据散列(partition)策略 当客户数达到一定规模后,单个数据库将无法支撑更高的并发访问,此时可以考虑把客户数据散列(partition)到多个数据库中,以分担负载,提高系统的整体性能与效率。
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有