系统针对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 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES (‘Testing’),(‘table’),(‘t1’);
INSERT INTO t2 (message) VALUES (‘Testing’),(‘table’),(‘t2’);
CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MERGE UNION=(t1,t2)INSERT_METHOD=LAST;
对应定期分表的情况下，只要定期相应的增加一个基础表，再修改merge表中的 union 就行了（ALTER TABLE tbl_name UNION=(…)）。
如在增加一个表（需和其他基础表一样的结构）：
定期增加表逻辑；可以使用MySQL定时创建表的SQL语句
资料网站: http://blog.csdn.NET/qingtian2002/article/details/23825113
mysql中每隔一段时间自动执行一次sql语句
如：table201606 然后执行ALTER TABLE total UNION=(t1,t2,t3,table201606);注意INSERT_METHOD=LAST;
表示新来的数据会插入到生成的新表中。
CREATE TABLE t3( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
ALTER TABLE total UNION=(t1,t2,t3)
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
