注意:以下文档只适用于TOP接口,请谨慎使用!

文档中心 > 聚石塔

RDS性能优化方案汇总

更新时间:2015/09/18 访问次数:36817

                                  RDS性能优化方法

CPU使用率太高告警
CPU
使用率高通常是由于慢SQL 导致,这里的慢SQL包括高并发下的全表扫描,内存排序,磁盘排序,锁争用和锁等待等。
用户可以通过show processlist查看当前正在执行的sql。当执行完show processlist后出现大量的语句,通常其状态出现sending dataCopying to tmp tableCopying to tmp table on diskSorting result, Using filesort 都是sql有性能问题在下面有具体案例分析
1sending data表示:sql正在从表中查询
数据,如果查询条件没有适当的索引,则会导致sql执行时间过长;
2Copying to tmp table on disk:出现这种状态,通常情况下是由于临时结果集太大,超过了数据库规定的临时内存大小,需要拷贝临时结果集到磁盘上,这个时候需要用户对sql进行优化;
3Sorting result, Using filesort:出现这种状态,表示sql正在执行排序操作,排序操作都会引起较多的cpu消耗,通常的优化方法会添加适当的索引来消除排序,或者缩小排序的结果集。

  • InnoDB中当系检测到死锁产生之后是如何来处理的?
  • Innodb 会选择产生死锁的两个事务中较小的事务来回滚,而让另外一个较大的事务成功完成;
  • 锁等待超时参数:innodb_lock_wait_timeout
  • 查看锁请求信息
  • show full processlist; Locked
  • show engine innodb status\G;
  • 查看information_schema下的表INNODB_TRXINNODB_LOCKSINNODB_LOCK_WAITS;
  • 子查询

mysql的常见的5.05.15.5版本中都存在较大风险,使用不当则会造成严重的性能问题,建议将子查询改为关联的形式
实际生产环境中具体案例具体解决方法:

关于rds 实例cpu 超过100%,通常这种情况都是由于sql 性能问题导致的,下面我用一则案例来分析:

子查询:

select count(*) from test_pic as bi where bi.time in

 (select MAX(time) from test_pic where PIC_TYPE=1 GROUP BY BUILDING_ID)

 GROUP BY bi.BUILDING_ID;

mysql的处理逻辑是遍历test_pic表中的每一条记录,代入到子查询中中去

而不是先将子查询中的结果集计算出来,然后在与test_pic表关联

改写为:

select count(*) from test_pic as bi ,

(select MAX(time) as time from test_pic

where PIC_TYPE=1 GROUP BY BUILDING_ID) b

where bi.time = b.time GROUP BY bi.BUILDING_ID;

6)一个RDS实例******,CPU使用率一直100%,后台并无慢SQL,检查也并没有发现什么异常

大量的回滚操作在执行,rds的执行肯定会慢,CPU消耗大。

解决方法:尽快停止回滚操作

案例分析:

通过show processlist发现如下sql

Sql A.

| 2815961 | sanwenba | 10.241.142.197:55190 | sanwenba |

Query | 0 | Sorting result | select z.aid,z.subject from

www_zuowen z right join www_zuowenaddviews za on za.aid=z.aid order by

za.viewnum desc limit 10;

性能sql

select z.aid,z.subject from www_zuowen z right join www_zuowenaddviews za

on za.aid=z.aid order by za.viewnum desc limit 10;

explain 查看执行计划:

sanwenba@3018 10:00:54>explain select z.aid,z.subject from www_zuowen z

right join www_zuowenaddviews za on za.aid=z.aid order by za.viewnum desc

limit 10;

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

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

rows | Extra |

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

| 1 | SIMPLE | za | index | NULL | viewnum | 6 |

NULL | 537029 | Using index; Using filesort |

| 1 | SIMPLE | z | eq_ref | PRIMARY | PRIMARY | 3 |

sanwenba.za.aid | 1 | |

添加适当索引消除排序:

sanwenba@3018 10:02:33>alter table www_zuowenaddviews add index

ind_www_zuowenaddviews_viewnum(viewnum);

sanwenba@3018 10:03:27>explain select z.aid,z.subject from www_zuowen z

right join www_zuowenaddviews za on za.aid=z.aid order by za.viewnum desc

limit 10;

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

| id | select_type | table | type | possible_keys | key |

key_len | ref | rows | Extra |

+----+-------------+-------+--------+---------------+--------------------------------+---------+-|

1 | SIMPLE | za | index | NULL |

ind_www_zuowenaddviews_viewnum | 3 | NULL | 10 | Using index |

| 1 | SIMPLE | z | eq_ref | PRIMARY PRIMARY | 3 | sanwenba.za.aid

| 1 | |

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

Sql B:

| 2825321 | netzuowen | 10.200.120.41:44172 | netzuowen |

Query | 2 | Copying to tmp table on disk |

SELECT * FROM `www_article` WHERE 1=1 ORDER BY rand() LIMIT 0,30

这种sql order by rand()同样也会出现排序;

netzuowen@3018 10:23:55>explain SELECT * FROM `www_zuowensearch`

WHERE checked = 1 ORDER BY rand() LIMIT 0,10 ;

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

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

rows | Extra |

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

| 1 | SIMPLE | www_zuowensearch | ref | newest | newest | 1 |

const | 1443 | Using temporary; Using filesort |

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

这种随机抽取一批记录的做法性能是很差的,表中的数据量越大,性能就越差:

第一种方案,即原始的Order By Rand() 方法:

$sql="SELECT * FROM content ORDER BY rand() LIMIT 12";

$result=mysql_query($sql,$conn);

$n=1;

$rnds='';

while($row=mysql_fetch_array($result)){

$rnds=$rnds.$n.".<a< span="" style="margin: 0px; padding: 0px;"></a<>

href='show".$row['id']."-".strtolower(trim($row['title']))."'>".$row['title']."<br< span="" style="margin: 0px; padding: 0px;"></br<>

/>\n";

$n++;

}

3万条数据查12条随机记录,需要0.125秒,随着数据量的增大,效率越来越低。

第二种方案,改进后的JOIN 方法:

for($n=1;$n<=12;$n++){

$sql="SELECT * FROM `content` AS t1

JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `content`)) AS id) AS t2

WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1";

$result=mysql_query($sql,$conn);

$yi=mysql_fetch_array($result);

$rnds = $rnds.$n.".<a< span="" style="margin: 0px; padding: 0px;"></a<>

href='show".$yi['id']."-".strtolower(trim($yi['title']))."'>".$yi['title']."
\n";

}

3万条数据查12条随机记录,需要0.004秒,效率大幅提升,比第一种方案提升

了约30倍。缺点:多次select查询,IO开销大。

第三种方案,SQL语句先随机好ID序列,用IN 查询(推荐这个用法,IO

开销小,速度最快):

$sql="SELECT MAX(id),MIN(id) FROM content";

$result=mysql_query($sql,$conn);

$yi=mysql_fetch_array($result);

$idmax=$yi[0];

$idmin=$yi[1];

$idlist='';

for($i=1;$i<=20;$i++){

if($i==1){ $idlist=mt_rand($idmin,$idmax); }

else{ $idlist=$idlist.','.mt_rand($idmin,$idmax); }

}

$idlist2="id,".$idlist;

$sql="select * from content where id in ($idlist) order by field($idlist2) LIMIT

0,12";

$result=mysql_query($sql,$conn);

$n=1;

$rnds='';

while($row=mysql_fetch_array($result)){

$rnds=$rnds.$n.".<a< span="" style="margin: 0px; padding: 0px;"></a<>

href='show".$row['id']."-".strtolower(trim($row['title']))."'>".$row['title']."<br< span="" style="margin: 0px; padding: 0px;"></br<>

/>\n";

$n++;

}

3万条数据查12条随机记录,需要0.001秒,效率比第二种方法又提升了4倍左右,比第一种方法提升120倍。注,这里使用了order by field($idlist2) 是为了不排序,否则IN 是自动会排序的。缺点:有可能遇到ID被删除的情况,所以需要多选几个ID

C.出现sending data的情况:

| 2833185 | sanwenba | 10.241.91.81:45964 | sanwenba | Query

| 1 | Sending data | SELECT * FROM `www_article` WHERE

CONCAT(subject,description) like '%??%' ORDER BY aid desc LIMIT 75,15

性能sql

SELECT * FROM `www_article` WHERE CONCAT(subject,description) like

'%??%' ORDER BY aid desc LIMIT 75,15

这种sql是典型的sql分页写法不规范的情况,需要将sql进行改写:

select * from www_article t1,(select aid from www_article where

CONCAT(subject,description) like '%??%' ORDER BY aid desc LIMIT 75,15)t2 where t1.aid=t2.aid;

注意这里的索引需要改用覆盖索引:aid+ subject+description

索引优化

 

SQL

  • SELECT order_id, order_sn
  • FROM order_info FORCE INDEX (shipping_time)
  • WHERE order_status = 1
  •    AND shipping_status = 0
  •    AND is_send = 0
  •    AND is_locked = 0
  •    AND shipping_time <= 1419632103
  • ORDER BY is_emergency DESC, update_time DESC

建议:

  • 添加索引:ALTER TABLE order_info ADD INDEX IDX_IS_SH_IS_UP (IS_SEND,SHIPPING_STATUS,IS_EMERGENCY,UPDATE_TIME)

 

 

SQL

  • DELETE FROM task_process
  • WHERE task_id = '1800067'

建议:

  • 添加索引:ALTER TABLE task_process ADD INDEX IDX_TASK_ID (TASK_ID)

 

 

SQL

  • SELECT id, order_sn, upload_request_flag, upload_response_flag
  • FROM api_wms_trade USE INDEX (lianhe)
  • WHERE id > 0
  •    AND ckdm IN ('')
  •    AND order_type = 'order_info'
  •    AND upload_response_flag = 0
  •    AND cancel_request_flag = 0
  •    AND wms_order_flow_end_flag = 0
  • ORDER BY id
  • LIMIT 100

建议:

  • 添加索引:ALTER TABLE api_wms_trade ADD INDEX IDX_CK_UP_WM_OR (CKDM,UPLOAD_RESPONSE_FLAG,WMS_ORDER_FLOW_END_FLAG,ORDER_TYPE)

 

 

SQL

  • SELECT ld.id AS ld_id, lg.id AS lg_id
  • FROM lsxhdlk_goods lg
  •    INNER JOIN lsxhdlk lk ON lg.p_id = lk.id
  •    INNER JOIN lsxhd ld ON lk.p_id = ld.id
  • WHERE lg.is_ft = 0
  •    AND ld.ywrq >= 1341504000
  • LIMIT 50

建议:

  • 添加索引:ALTER TABLE lsxhdlk_goods ADD INDEX IDX_IS_FT_P_ID (IS_FT,P_ID)

 

SQL

 

SQL

select 'data' as fnodepath , case when order_type = 0 then '1' when order_type = 1 then '0' end as auart , b.sddm as kunnr , b.sdmc as name1 , a.djbh as bstkd , c.brand_code as zbbrand , '---' as posnr , '---' as ean11 , '---' as lgort , '---' as kbetr , '---' as wmeng from lsxhrb a left join shangdian b on a.sd_id = b.id left join brand c on a.brand_id = c.brand_id where djbh = :1 union all select 'data\\items' as fnodepath , '---' as auart , '---' as kunnr , '---' as name1 , '---' as bstkd , '---' as zbbrand , cast ( ( @mycnt := @mycnt+1 ) as char ( 10 ) ) as posnr , d.barcode as ean11 , c.ckdm as lgort , cast ( j.zk as char ( 10 ) ) as kbetr , cast ( sum ( b.sl ) as char ( 10 ) ) as wmeng from lsxhrb a left join lsxhrbmx b on a.id = b.p_id left join cangku c on a.ck_id = c.id left join goods_barcode d on b.goods_id = d.goods_id and b.color_id = d.color_id and b.size_id = d.size_id left join goods e on b.goods_id = e.goods_id left join shangdian f on a.sd_id = f.id left join dttjdmf1 h on a.sd_id = h.sd_id left join dttjdmp j on h.djbh = j.djbh and b.goods_id = j.goods_id where a.djbh = :2 group by d.barcode , c.ckdm union all select 'data\\items' as fnodepath , '---' as auart , '---' as kunnr , '---' as name1 , '---' as bstkd , '---' as zbbrand , cast ( ( @mycnt := @mycnt+1 ) as char ( 10 ) ) as posnr , '运费' as ean11 , b.ckdm as lgort , cast ( shipping_fee as char ( 10 ) ) as kbetr , 0 as wmeng from lsxhrb a left join cangku b on a.ck_id = b.id where a.djbh = :3 and shipping_fee <> :4

建议:

  • 严重】扫描行与发送行的比是359514,并且使用了聚合函数,该SQL索引过滤性不好,影响服务器性能。

 

 

SQL

select a.* , now ( ) as createtime from order_info a left join cangku b on a.ck_id = b.id , z_qyrq where ( a.order_status = :1 and shipping_status = :2 and is_send = :3 and process_status = :4 and is_separate = :5 ) and b.ckmc like :6 and a.add_time > z_qyrq.qyrq and exists ( select id from api_wms_trade where upload_response_flag in ( :7 , :8 ) and upload_request_flag = :9 and order_type = :10 and cancel_request_flag = :11 and wms_order_flow_end_flag = :12 and order_sn = a.order_sn )

建议:

  • 严重】扫描行与发送行的比是3738355,需要注意like可能无法使用索引,请优化表结构和SQL语句。

 

 

SQL

select lsxhd.lylx , order_id , invoice_no , shipping_name from lsxhd , lsxhdlk where lsxhd.id = lsxhdlk.p_id and ( lsxhdlk.back_status = :1 or ( lsxhdlk.back_status = :2 and ( back_errmsg like :3 or back_errmsg like :4 or back_errmsg like :5 ) ) ) and lsxhd.sd_id = :6 order by lsxhdlk.back_status , lsxhdlk.add_time limit :7

建议:

  • 严重】扫描行与发送行的比是744,需要注意like可能无法使用索引,请优化表结构和SQL语句。
  • 建议】平均发送行1172,返回行数过多,请考虑在低峰时执行这类语句,以减少对其他查询的影响。

 

 

SQL

select order_id , order_sn from order_info force index ( shipping_time ) where order_status = :1 and shipping_status = :2 and is_send = :3 and is_locked = :4 and shipping_time <= :5 order by is_emergency desc , update_time desc

建议:

  • 严重】扫描行与发送行的比是2053460,并且使用了force index,该SQL索引过滤性不好,影响服务器性能,请确认force index是否合理。
  • 警告】您的SQL语句锁行过多,可能堵住其他更新语句

 

 

SQL

delete from task_process where task_id = :1

建议:

  • 警告】您的SQL语句锁行过多,可能堵住其他更新语句

 

Sql优化是性能优化的最后一步,虽然位于塔顶,他最直影响用户的使用,但也是最容易优化的步骤,往往效果最直接。RDS-mysql由于有资源的隔离,不同的实例规格拥有的iops能力不同,比如新1型提供的iops150个,也就是每秒能够提供150次的随机磁盘io操作,所以如果用户的数据量很大,内存很小,由于iops的限制,一条慢sql就很有可能消耗掉所有的io资源,而影响其他的sql查询,对于数据库来说就是所有的sql需要执行很长的时间才能返回结果,对于应用来说就会造成整体响应的变慢。

连接数过高告警
如果出现连接数报警的时候,可以通过show processlist查看数据库中正在运行的连接。一般sql执行慢或者某个sql持有锁导致其他sql等待,两种情况都会引发会话堆积产生报警。解决方法:
a.
优化慢查询可以解决问题;
b.
对于由于持有锁的sql 导致连接堆积,则需要根据业务是否允许 ,kill掉持有锁的会话;
c.
如果连接数用满,并且不自动释放,可以在RDS控制台重启一下实例。

d.高并发的情况下,少使用长连接

e.设置一个短的超时时间,让短连接尽快的自动关闭:wait_timeout

f. 使用了myisam存储引擎的表,如果该表上面的查询没有返回的话,会堵塞该表的写入,从而导致连接堆积将表的引擎转为innodb

INNODBMyisam

.RDS的内存配置innodbinnodb_buffer_pool_size,Myisamkey_cache配置32k

.主机断电,crashMyisam表容易出现索引坏叶,需要手工repair修复索引

.Myisam存储引擎的表备份时候会被全局锁住,导致无法写入数据

alter table *** engine=innodb;

Mysql 的连接通常是一个请求占用一个连接,如果该请求(updateinsertdeleteselect)长时间没有执行完毕,则会造成连接的堆积,迅速的消耗完数据库的连接数,这个时候工程师就要登录数据库进行排序,看看到底是那些sql 占用了连接;

问题排查步骤:

1 、查看实例配置:

可登录RDS控制台“详情与配置”查看实例额定链接数,我们假设最高支持1500个链接

2、 查看当前的连接数:

1)可登录RDS控制台“性能监控”查看实例当前链接数。

2)或者登录数据库查询当前连接,可以使用同步帐号或者用户的业务帐号登录数据库,执行show processlist

[root@r41d05036.xy2.aliyun.com ~]# mysql -uroot -h127.0.0.1 -P3020 -e "show processlist"|wc -l

1262

可以看到该实例已经有1262 个连接

3、排查是什么动作占用了这些连接:

[root@r41d05036.xy2.aliyun.com ~]# myql -uroot -h127.0.0.1 -P3018 -e "show full processlist">/tmp/1.log

root@r14d11038.dg.aliyun.com # more /tmp/1.log

615083 my_db 223.4.49.212:54115 my_db Query 100 Sending data

INSERT INTO tmp_orders_modify (oid, tid, seller_id, `status`, gmt_create, gmt_modified)

SELECT oid, tid, seller_id, `status`, gmt_create, gmt_modified

FROM sys_info.orders WHERE

gmt_modified < NAME_CONST('v_last',_binary'2012-12-24 10:33:00' COLLATE 'binary') AN

D gmt_modified >= NAME_CONST('v_curr',_binary'2012-12-24 10:32:00' COLLATE 'binary')

621564 my_db 223.4.49.212:46596 my_db Query 3890 sorting result

insert into tmp_trades(sid, d, h, tc, tm, tp, ic, new_tp, old_tp)

select a.seller_id as sid,

…………..

from orders_1 as a where seller_id =1 and is_detail = '1'

and created < date_format('2012-12-24 10:35:00', '%Y-%m-%d %H:00:00')

and gmt_create < date_format('2012-12-24 10:40:00', '%Y-%m-%d %H:%i:00')

and gmt_create >= date_format('2012-12-24 10:35:00', '%Y-%m-%d%H:%i:00')

group by d, h

order by d

……………….此处省略其他sql

4、分析连接占用的原因:

可以看到数据库中有长时间没有执行完成的sql,一直占用着连接没有释放,而应用的请求一直持续不断的涌入数据库,这个时候数据库的连接很快就被使用完;所以这个时候需要排查为什么这些sql 为什么长时间没有执行完毕,是索引没有创建好,还是sql执行耗时严重。

第一条sql

INSERT INTO tmp_orders_modify (oid, tid, seller_id, `status`, gmt_create, gmt_modified)

SELECT oid, tid, seller_id, `status`, gmt_create, gmt_modified

FROM sys_info.orders WHERE

gmt_modified < NAME_CONST('v_last',_binary'2012-12-24 10:33:00' COLLATE 'binary') AN

D gmt_modified >= NAME_CONST('v_curr',_binary'2012-12-24 10:32:00' COLLATE 'binary')

是用户从sys_info 数据库中拉取订单到自己的业务库中那个,但是在orders 表上没有gmt_modified 的索引,导致了全表扫描;(更加详尽的排查方法可以参考:为什么我的RDS慢了);

第二条sql

看到这条sql 正在进行sorting 排序,为什么导致sql 长时间sorting,通常情况下为排序的结果集太大导致排序不能在内存中完成,需要到磁盘上排序,进而导致了性能的下降;解决的办法就是降低排序的结果集,常用的手段是利用索引的有序性,消除排序,或者建立适当的索引减小结果集;我们可以看到第二条sql 的排序字段非常的复杂,但是我们可以看到查询的时间范围是很短,只有5 分钟的时间间隔,这个时候就可以在gmt_create上创建一个索引,过滤掉大部分的记录:

Alter tale order_1 add index ind_order_gmt_create(gmt_create)

(该用户对orders 进行了分表,大概有50 多张分表需要添加gmt_create 字段的索引);

5、经过上面两步的优化后,用户实例恢复正常:io 情况和connection 情况,可再次登陆RDS控制台查看连接数。

 

.RDS空间使用率过高
1)临时空间快速增长
一些查询语句(order by, group by)会创建临时表。用explain查看select语句的执行计划,如果extra列显示“using temporary”,即使用了内部临时表。使用临时表一般都意味着性能比较低,在实际应用中应该尽量避免临时表的使用。

常见的方法有:
a. 创建索引:在ORDER BY或者GROUP BY的列上创建索引;
b. 分拆很长的列:一般情况下,TEXTBLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。
如果表的设计已经确定,修改比较困难,那么也可以通过优化SQL语句来减少临时表的大小,以提升SQL执行效率。
2)日志文件快速增长
数据库的更新写入压力过大:updateinsertdelete,导致日志急增

.数据库采用了大字段:varchar(8000),text,blob,clob(sqlserver/mysql)

常见的方法有:

1.控制台一键清理binlogRDS会定期将用户的日志进行清理并上传到OSS

2.将大字段存放到OSS

实例相关

 

问题级别:

严重

问题:

我们发现当前这个查询执行时间过长,可能会影响你的实例性能。我们建议你添加适当的索引来提高查询性能。[ Select distinct a.djbh,now() as createtime From lsxhrb a left join order_goods b on a.djbh=b.lsxhrb_djbh Where ifnull(bsflag2,'')='' and ifnull(a.bsflag,'')='true' and a.order_type=0 and not exists(Select 1 From order_goods Where (dz_flag=0 or dz_flag=4 or dz_flag=3) and lsxhrb_djbh=a.djbh) union all Select distinct a.djbh,now() as createtime From lsxhrb a left join order_goods b on a.djbh=b.lsxhrb_djbh Where ifnull(bsflag2,'')='' and ifnull(a.bsflag,'')='true' and a.order_type=1 and not exists(Select 1 From order_goods Where (dz_flag=0 or dz_flag=4 or dz_flag=3) and lsthrb_djbh=a.djbh) ]

建议:

 

 

问题级别:

警告

问题:

日志的空间超过5G

建议:

在管理控制台进行一键清除Binlog

 

3)磁盘使用率过高

   optimize  table +表名,手动输入命令操作收缩数据库,在业务低峰期操作,会锁住整张表。

3)目前产品上的解决方法:控制临时文件目录的最大值,超过则kill掉查询。现在的MySQL5.6已经支持,MySQL5.5在下个版本支持。

4)系统文件空间

   长时间没有提交事务,同时数据库中有大量的更新,插入,删除 ,导致innodb

创建大量的undo来维护一致性;

.mysql 5.1undopurge是和master thread 共用一个线程,则可能的purge

的速度到达了瓶颈;

ibdata1文件中大量的都是undo_log,我们可以采取以下方案:

建议用户将版本从5.1升级到5.55.5中有独立的purge线程可以很快的回收掉undo log

5.6中可以单独设置undo tablespace文件,避免与ibdata1混用在一起;

迁移的过程中由于是采用逻辑迁移,会重建ibdata1文件降低空间使用

5)实例空间太大,像内存、网络、CPU以及备份都将增加相应的开销。可能仅仅是由于空间不满足使得我们不得不进行扩容

1、表结构设计上

1) 字符集是否遵循了最小化原则?(能用latin的就不用gbk。能用gbk的就不用utf8)

2) 索引上是否有滥用?(根本不使用的字段建索引、不适合建索引的字段建索引、重复建索引或者不能很好的利用前缀索引等)

3) 冗余字段是否太多?(各表中不用的或者字段冗余太多)

4) 不正确的字段类型?(能用1个字节非要用几个字节,像枚举类、状态类比较常见)

5) 将较长的字段或者几个字段组合做为主键?(主键最好用mysql自增)

具体事例如下:

CREATE TABLE `class_meta` (

`class_name` varchar(128) NOT NULL COMMENT ‘类名’,

`class_desc` varchar(2048) default COMMENT ‘类的描述’,

`class_status` char(20) default test1 COMMENT test1,test2,

PRIMARY KEY (`class_name`),

UNIQUE KEY `cm_cn_uk` (`class_name`),

KEY `cm_cd_ind` (`class_desc`(767)),

KEY `cm_cs_ind` (`class_status`),

KEY `cm_cdcn_ind` (`class_desc`(767),`class_name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=meta信息’;

通过上面的表结构能看到如下地方不合适

1、主键与唯一索引明显重复,索引cm_cd_ind与索引cm_cdcn_ind索引重复(这种情况经常出现,大家留意下)

2cm_cs_ind如果两个状态分布均匀也明显不合适建索引

3class_desc由于是描述性质的,也不合适建索引

4、最好以自增做为主键,可以减少整表的空间

5class_status列明显可以用tinyint来存,可以省下19个字节

2、存储内容上

1) 是否将图片、视频、音乐等大数据存储在表中?(表里最好只保留路径而不是实际的文件内容)

3、数据保留上

1)是否有已过期而未删除的数据?(对于无效数据及时清理或者进行历史归档)

4、后期维护上

1)是否对经常删除的表进行维护(optimize table)

建议:

1、在性能要求不高的case(并发不太高),可以考虑使用压缩表。一般压缩率在30%-70%之间,收益非常可观。

2、对于删除非常频繁的表要定期进行优化,使表中碎片减少。提高查询、写入的性能。

3、在表结构设计上,一定要发扬“斤斤计较”的精神,能用1个字节表示的坚决不用2个字节。

4、尽量减少大字段的使用。

当你的数据级到T或者P的时候,哪怕多省几个字节都是非常可观的,给大家简单算笔账,如果我们将一张5亿条记录的表,字段从100个字节降到60个字节(应该很容易做到吧),那么不算上索引将节约大约18G的空间。

4.Varchar\nvarchar\varbinary\text\ntext\image等大字段优化

SQL Server使用varchar(max)/nvarchar(max)varbinary(max)text/ntextimage这些大字段,可能导致数据库日志飚升,达到或超过数据文件大小,导致实例被锁定。实际案例中,有的客户1小时增长超过100G。经过改进,将大部分字段调小,该问题消除。建议:除了历史归档或文献资料类型的应用外,一般不需要用大字段来做存储。大字段的最大存储大小是 2^31-1 个字节 (2 GB),所以SQL Server需要用一种特别的方式来存储和操作它,其成本也就比普通字段高。而如果数据库使用了full模式,响应的日志量也就高很多了。

应该架构改造

1性能稳定和优化5.5. 5.6提供了更好的功能,建议尽快升级

2.Mysql5.6读写分离,主库承担着前台高并发的写入,更新,查询,需要定时对数据做一些有复杂统计报表计算,采用只读节点的方案,将报表统计业务拆分到只读节点上,只支持MySQL 5.6版本,如需读写分离功能,建议尽快升级到5.6.

3.读数据比较频繁,建议部署OCS缓存,降低对RDS实例的读压力

  1. 容量已经满了,导致数据库实例死锁

optimize table + 表名 

 

FAQ

关于此文档暂时还没有FAQ
返回
顶部