mysql 性能优化索引、缓存、分表、分布式实现方式。

时间:2022-01-09 19:44:26

系统针对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