系统针对5000台终端测试结果
索引
目标:优化查询速度3秒以内 需要优化。尽量避免使用select * 来查询对象。使用到哪些属性值就查询出哪些使用即可
首页页面:
设备-组织查询 优化 避免使用select * 来查询device对象 优化前耗时 200毫秒 优化后耗时19毫秒
设备-在线 优化 避免使用select * 来查询device对象 优化前耗时 210毫秒 优化后耗时11毫秒
设备-版本 优化 避免使用select * 来查询device对象 优化前耗时 230毫秒 优化后耗时10毫秒
终端检查报告页面:
查看报告详细报告 优化前耗时3603毫秒 优化后耗1103毫秒
优化方式:
1:ccp_infraction 建立联合索引 checkItemId和reportId
create index ccp_infraction_index on ccp_infraction (reportId,checkItemId) ;
查看报告所有结果和人工判定 优化前耗时10300毫秒 优化后耗时1973毫秒
优化方式:添加联系索引
create index idx_1 on ccp_artificial (reportId,isInfraction);
create index idx_2 on ccp_terminalinfo(reportId);
create index idx_3 on ccp_currenttimerep(reportId);
create index idx_4 on ccp_hostrep (reportId);
create index idx_5 on ccp_currentaccountrep(reportId);
create index idx_6 on ccp_accountrep(reportId,isInfraction);
create index idx_7 on ccp_hardwarerep(reportId,isInfraction);
create index idx_8 on ccp_networkrep (reportId);
create index idx_9 on ccp_softwarerep(reportId,isInfraction);
create index idx_10 on ccp_diskrep (reportId,isInfraction);
create index idx_11 on ccp_partitioninforep(reportId);
create index idx_12 on ccp_oneKeyInforep(reportId,isInfraction);
create index idx_13 on ccp_usbinforep(reportId,isInfraction);
create index idx_14 on ccp_printerrep (reportId,isInfraction);
create index idx_15 on ccp_cdromrep(reportId,isInfraction);
create index idx_16 on ccp_wpdrep (reportId);
create index idx_17 on ccp_usedNetSoftWarerep(reportId,isInfraction);
create index idx_18 on ccp_dialingrep(reportId,isInfraction);
create index idx_19 on ccp_browserrep(reportId,isInfraction);
create index idx_20 on ccp_emailrep(reportId,isInfraction);
create index idx_21 on ccp_downloadrep(reportId,isInfraction);
create index idx_22 on ccp_imrep(reportId,isInfraction);
create index idx_23 on ccp_cloudDiskInforep(reportId,isInfraction);
create index idx_24 on ccp_sharefolderrep(reportId,isInfraction);
create index idx_25 on ccp_secsoftwarerep(reportId,isInfraction);
create index idx_26 on ccp_patchrep (reportId);
create index idx_27 on ccp_noPatchRep(reportId,isInfraction);
create index idx_28 on ccp_securitymanagementrep(reportId,isInfraction);
create index idx_29 on ccp_osrep(reportId,isInfraction);
create index idx_30 on ccp_vmwarerep(reportId,isInfraction);
create index idx_31 on ccp_wirelessrep(reportId,isInfraction);
create index idx_32 on ccp_monitorProtectrep(reportId,isInfraction);
create index idx_33 on ccp_firewarerep(reportId,isInfraction);
create index idx_34 on ccp_remoteInfo(reportId);
create index idx_35 on ccp_autoPlay(reportId,isInfraction);
create index idx_36 on ccp_autoUpdate(reportId,isInfraction);
create index idx_37 on ccp_portinforep(reportId,isInfraction);
create index idx_38 on ccp_procedurerep(reportId,isInfraction);
create index idx_39 on ccp_servicerep(reportId,isInfraction);
create index idx_40 on ccp_systemlogrep(reportId,isInfraction);
create index idx_41 on ccp_poweronandoffrep(reportId);
create index idx_42 on ccp_accountsecurityrep(reportId,isInfraction);
create index idx_43 on ccp_pwandacpolicyrep(reportId,isInfraction);
create index idx_44 on ccp_seclogrep(reportId,isInfraction);
create index idx_45 on ccp_userrightrep(reportId,isInfraction);
create index idx_46 on ccp_userpolicyrep(reportId);
create index idx_47 on ccp_grouppolicyrep(reportId);
create index idx_48 on ccp_fileinforep(reportId,isInfraction);
create index idx_49 on ccp_fileencryptrep(reportId);
create index idx_50 on ccp_delFile (reportId);
create index idx_51 on ccp_delFileCheck (reportId,isInfraction);
create index idx_52 on ccp_deepUSBCheck(reportId,isInfraction);
create index idx_53 on ccp_deepURLCheck(reportId,isInfraction);
create indexidx_54 on ccp_sectorCheck (reportId,isInfraction);
任务检查报告:查看报告;优化前耗时7660毫秒,优化后耗时3370毫秒
优化方式:
1:添加索引
create index index_ccp_device_policy on ccp_device_policy(policyId,mark);
create indexindex_ccp_checkreport on ccp_checkreport(policyId,planReportTime);
查询检查项数据:优化前耗时8300毫秒 优化后耗时2073毫秒
优化方式:数据表中添加索引,引用上面sql索引语句
注意:联合索引reportId,isInfraction其实是支持2个索引,reportId和reportId,isINfraction
缓存
mysql查询缓存的使用
开启查询缓存:
set global query_cache_type = 1; 使用命令开启查询缓存
setglobal query_cache_size = 134217728; 缓存区的大小 设置太小不会生效
show variables like "%query_cache%";
根据query_cache_type这个变量来决定的。
这个变量有三个取值:0,1,2,分别代表了off、on、demand。
mysql默认为开启on
查询 SHOW STATUS LIKE'Qcache_hits'; value为0
查询后Qcache_hits变化
ex:
关闭查询缓存:
C:\Program Files(x86)\MySQL\MySQL Server 5.0 中 my.ini配置
query_cache_type = 0
query_cache_size = 0
或者
set global query_cache_type = 0; 使用命令关闭查询缓存
setglobal query_cache_size = 0;
查询 SHOW STATUS LIKE'Qcache_hits'; value为0
使用查询:select count(*) fromccp_printerrep;
SHOW STATUS LIKE 'Qcache_hits';value值还是为0
show status like"%Qcache%";
开启查询缓存测试:
select * from ccp_softwarerep;
第一次查询耗时:1.434s
第二次查询耗时:0.629s
mysql默认 query_cache 是打开的
你使用 show global variables like '%query_cache%';
确认一下有没有打开,如果打开了。第一次查询读数据文件,第二次就会走query_cache,所以就会很快。当然 ,如果数据更新了,要重新再缓存。
第二种情况 ,表引擎使用innodb.第一次查询也会走数据文件,第二次直接走buffer_pool,也比直接查询数据文件要快
上面的说法是否正确。
本人测试:
--开启查询缓存 在my.ini文件中配置 并重启服务
query_cache_type = 1;
query_cache_size = 134217728;
select * from ccp_usbinforep 语句条数:145011条
--第一次执行语句用时1.181s; 第二次执行用时0.265s
Qcache_hits 为 211 而且会增加说明是在使用查询缓存
--关闭查询缓存 在my.ini 中配置
query_cache_size=0;
query_cache_type = 0;
select * from ccp_usbinforep 语句条数:145011条
第一次执行语句用时0.294s;第二次执行用时0.283s
Qcache_hits为0
以上实验说明,好像mysql缓存不管怎么配置对sql查询的影响时间影响不大,那么配置的意义在于什么呢。
或者以上我哪些配置和理解错误?
如果缓存起作用,就用缓存,缓存关闭就是要表引擎 那么有没缓存影响大么。请大家简单说下自己的理解
查询时,数据库引擎会判断,如果数据在内存中,则会从内存读取数据,如果数据不在内存在,则先从硬盘读到内存,然后再供查询。
所以第一次查的时候,根据你的语句,数据库引擎会把一些数据从硬盘读到内存,第二次再查的时候,就从内存读数据,就快了很多了搜索。
如果这是正确的,那么mysql的缓存有什么意义。。。
网友意见:query cache是第一道缓存,命中则直接返回,否则进入存储引擎层面处理
关掉query cache,直接进入到存储引擎层面,innodb本身也是有缓存机制的
都是从内存中取结果,执行效率差别不大。我的数据库引擎是innodb的,所以不好测试。如果是其他的可以测试看看
分表
mysql数据库中分表优化。
1:使用Mysql的Merge存储引擎实现分表查询
CREATE TABLE t1 (
INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
对应定期分表的情况下,只要定期相应的增加一个基础表,再修改merge表中的 union 就行了(ALTER TABLE tbl_name
如在增加一个表(需和其他基础表一样的结构):
定期增加表逻辑;可以使用MySQL定时创建表的SQL语句
资料网站: http://blog.csdn.net/qingtian2002/article/details/23825113
mysql中每隔一段时间自动执行一次sql语句
如:table201606 然后执行ALTER TABLE total
表示新来的数据会插入到生成的新表中。
ALTER TABLE total
INSERT_METHOD=LAST;表示插入的方法,INSERT_METHOD的值可以是 FIRST(插入第一个表),LAST(最后一个表),NO(不能插入)
查询的时候,和平常一样
select * from total where ....
merge表会自动找到相应的基础表进行查询。
注意问题:id可能重复问题
mysql版本必须高于5.0版本
ex:定时创建表
drop event e_createuser;
drop PROCEDURE create_user;
show variables like '%sche%';
set global event_scheduler =1;
create procedure create_user()
BEGIN
set @sql_create_table = concat(
'CREATE TABLE IF NOT EXISTS user', date_format(now(),'%Y%m'),
"(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex` int(1) NOT NULL DEFAULT '0',
PRIMARYKEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8AUTO_INCREMENT=1");
PREPARE sql_create_table FROM @sql_create_table;
EXECUTE sql_create_table;
END
create event if not existse_createuser
on schedule every 30 second
on completion preserve
do call create_user();
%Y%m%d%H%i%s 表示年月日时分秒20160630102310
每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去.
关闭事件任务
alter event e_createuser ON
COMPLETION PRESERVE DISABLE;
开户事件任务
alter event e_createuser ON
COMPLETION PRESERVE ENABLE;
以上测试均成功,测试环境为mysql 5.1
分布式集群
2: 做mysql集群,例如:利用mysql cluster ,mysql proxy,mysql replication,drdb等等
有人会问mysql集群,根分表有什么关系吗?虽然它不是实际意义上的分表,但是它启到了分表的作用,做集群的意义是什么呢?为一个数据库减轻负担,说白了就是减少sql排队队列中的sql的数量,举个例子:有10个sql请求,如果放在一个数据库服务器的排队队列中,他要等很长时间,如果把这10个sql请求,分配到5个数据库服务器的排队队列中,一个数据库服务器的队列中只有2个,这样等待时间是不是大大的缩短了呢?这已经很明显了。所以我把它列到了分表的范围以内,我做过一些mysql的集群:
linux mysql proxy 的安装,配置,以及读写分离
mysql replication 互为主从的安装及配置,以及数据同步
优点:扩展性好,没有多个分表后的复杂操作(php代码)
缺点:单个表的数据量还是没有变,一次操作所花的时间还是那么多,硬件开销大。
集群资料:http://www.th7.cn/db/mysql/201409/72633.shtml