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

墨蓝 2022-09-22 12:58 31阅读 0赞

#  #

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