关于mysql优化和数据抽取常用的一些sql语句

mysql配置优化

#1.mysql默认的最大连接数（默认本地为151） 服务器100 推荐1024
Max_connections=300
#mysql能暂存的连接数量 默认80 50 128
back_log=90
#2.3.wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数 interactive_timeoutz：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用 如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 一般希望wait_timeout尽可能低

28800 28800 100

wait_timeout=3600
#默认 28800 28800
interactive_timeout=7200
#4.key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度 8388608（8M） 8388608（8M） 2G内存推荐134217728（128MB）
key_buffer_size=256M
#5.query_cache_size（查询缓存简称QC） 1M 0 256M
query_cache_size=128M
#开启缓存
query_cache_type=1
#6.max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
#默认100 10 20
max_connect_errors=10
#7.每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或GROUP BY操作，Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存
#默认 0.25M 0.25M 2M
sort_buffer_size=0.75M
#8.4M 1M 32 Mmysql根据配置文件会限制，server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数
max_allowed_packet=8M
#9.用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
#0.25 0.125 2M
join_buffer_size=0.5M
#10. 9 8 200 服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大
thread_cache_size = 64
#11.######128 390 2048M 对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。
innodb_buffer_pool_size= 1024M
######12.主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作
innodb_flush_log_at_trx_commit=1
#13. 0 9 8 此参数用来设置innodb线程的并发数量，默认值为0表示不限制，若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8.
innodb_thread_concurrency = 9
#14. 8M 5M 32M此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
innodb_log_buffer_size=16M
#15. 48M 195M 50M 此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
#innodb_log_file_size = 50M
#16. 2 2 3
###innodb_log_files_in_group=3
#17. 0.125 0.0625
read_buffer_size=0.5M
#18. 0.25 0.25 16M
read_rnd_buffer_size = 4M
#19. 128M 8M 64M
bulk_insert_buffer_size=128M
#未完善
binlog_cache_size=2M
max_binlog_cache_size=8M
expire_logs_days=7
query_cache_limit=50M
max_binlog_size=512M

#slow_query_log=1
#slow_query_log_file = /usr/local/mysql/data/slow_query_log
#long_query_time=1
#log-queries-not-using-indexes
#log-bin=mysql-bin
#server-id=1

slow_query_log=1
slow_query_log_file = /usr/local/mysql/data/slow_query_log
long_query_time=1
log-queries-not-using-indexes
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=256M
query_cache_size=256M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=2048M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
log-bin=mysql-bin
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
server-id=1

Max_connections

---

insert into temtable (SELECT
T.TYPE_ID,O.ORGTYPE_CODE
FROM
ML_INFO_TYPE T,
ML_BUSINESS_INFO B,
OM_ORGANIZATION O
WHERE
T.BID = B.BID
AND B.BDEPT = O.ORG_ID
AND B.BNAME = ‘待规范目录’
AND T.CHANGE_STATUS != ‘03’);

update ML_INFO_TYPE inner join temtable on ML_INFO_TYPE.TYPE_ID = temtable.TYPE_ID set ML_INFO_TYPE.TYPE_CODE1 = CONCAT(‘307013’,temtable.ORGTYPE_CODE)；

UPDATE CATALOG_PROCESS C inner join ORG_EMPLOYEE O on C.CHECKER_ID= O.EMP_ID SET C.CHECKER_ID = O.OPERATOR_ID

update ds_apply D inner join ORG_ORGANIZATION O on D.DS_APPLY_ORG_ID = ORG_ID AND D.DS_APPLY_KIND =‘2’ set D.DS_APPLYER_NAME = O.ORG_NAME

– 服务插入统计表
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT left( CONCAT( uuid(),REPLACE( RAND(), ‘.’, ‘-’)),36), SERVICE_ID,‘1’ FROM DS_API_INFO;

INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT API_ID, SERVICE_ID,‘1’ FROM DS_API_INFO;

ml_info_type
– 生成文件服务
– SELECT CONCAT(CATALOG_NAME,“的文件服务”) CATALOG_NAME FROM DS_FILE_SERVICE_DETAIL D ,CATALOG_INFO C WHERE D.SERVICE_ID=C.CATALOG_ID
– 文件触发器
DELETE FROM DS_SERVICE_DETAIL WHERE DS_SERVICE_DETAIL.DS_SERVICE_DETAIL_ID =OLD.DS_FS_DETAIL_ID
– 服务触发器
DELETE FROM DS_SERVICE_DETAIL WHERE DS_SERVICE_DETAIL.DS_SERVICE_DETAIL_ID =OLD.API_ID
– 服务表加索引
ALTER TABLE DATA_SERVICE_INFO ADD INDEX SERVICE_ORG_ID ( SERVICE_ORG_ID )
ALTER TABLE DATA_SERVICE_INFO ADD INDEX CATALOG_ID ( CATALOG_ID )
ALTER TABLE DATA_SERVICE_INFO ADD INDEX SERVICE_KIND ( SERVICE_KIND )
– 统计表加索引
ALTER TABLE DS_SERVICE_DETAIL ADD INDEX SERVICE_ID ( SERVICE_ID ) ;
ALTER TABLE DS_SERVICE_DETAIL ADD INDEX SERVICE_TYPE ( SERVICE_TYPE ) ;

ALTER TABLE ds_file_service_detail ADD INDEX FILE_STATUS ( FILE_STATUS ) ;
ALTER TABLE ds_file_service_detail ADD INDEX SERVICE_ID ( SERVICE_ID ) ;
ALTER TABLE DATA_SERVICE_INFO ADD INDEX SERVICE_STATUS ( SERVICE_STATUS ) ;
ALTER TABLE DATA_SERVICE_INFO ADD INDEX SERVICE_DELETE ( SERVICE_DELETE ) ;

ALTER TABLE ORG_ORGANIZATION ADD INDEX ORG_ID ( ORG_ID ) ;

ALTER TABLE DATA_SERVICE_STATS ADD INDEX SERVICE_ID ( SERVICE_ID ) ;
ALTER TABLE CATALOG_ITEM_INFO ADD INDEX CATALOG_ID ( CATALOG_ID ) ;

– 文件插入统计表
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT left( CONCAT( uuid(),REPLACE( RAND(), ‘.’, ‘-’)),36), SERVICE_ID,‘3’ FROM DS_FILE_SERVICE_DETAIL;

INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT DS_FS_DETAIL_ID, SERVICE_ID,‘3’ FROM DS_FILE_SERVICE_DETAIL;

–库表插入统计表
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT left( CONCAT( uuid(),REPLACE( RAND(), ‘.’, ‘-’)),36), SERVICE_ID,‘2’ FROM DS_FILE_SERVICE_DETAIL;
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT DS_TS_DETAIL_ID, SERVICE_ID,‘2’ FROM ds_table_service_detail;
–查询服务目录状态
SELECT CATALOG_STATUS FROM CATALOG_INFO GROUP BY CATALOG_STATUS;
–更新 删除状态
UPDATE CATALOG_INFO SET LOGIC_DELETE =‘1’,CATALOG_STATUS=‘4’ WHERE CATALOG_STATUS=‘SC’;
–受影响的行: 1
–更新 区县审核状态
UPDATE CATALOG_INFO SET CATALOG_STATUS=‘2’ WHERE CATALOG_STATUS=‘qx’;
–受影响的行: 37
–待审审核删除
UPDATE CATALOG_INFO SET LOGIC_DELETE =‘1’, CATALOG_STATUS=‘2’ WHERE CATALOG_STATUS=’-1’ OR CATALOG_STATUS=’-2’;
–受影响的行: 1679

–查询服务状态 ps：若先抽取完目录紧接着执行以上sql 可省略此步骤

SELECT SERVICE_STATUS FROM DATA_SERVICE_INFO GROUP BY SERVICE_STATUS

–更新 区县审核状态
UPDATE DATA_SERVICE_INFO SET SERVICE_STATUS=‘2’ WHERE SERVICE_STATUS=‘qx’;
–受影响的行: 4
–待审审核删除
UPDATE DATA_SERVICE_INFO SET SERVICE_DELETE =‘1’, SERVICE_STATUS=‘2’ WHERE SERVICE_STATUS=’-1’ OR SERVICE_STATUS=’-2’;
–受影响的行: 160

– SELECT * FROM as_reg_service_apply AP LEFT JOIN apply_service_schemal SC ON AP.APPLY_ID=SC.applyid
–通知公告 文件资料 通过后台管理系统统一加上
–数据开放
–与省里同步发布状态
– 将UpdateCatalogStatus 放入 others模块的 com.sdyy.share.cascade.rest; 修改regionCode token intArray的参数
https://localhost:8003/others/rest/updateCatalogStatus/getCascadeInfo
370300,9D457AB08C63426D85CF9A225750FF2A,370300000000,1,5,"","",0,0,0,0

–根据上报的数据库表生成相关物化信息CATALOG_PHYSICAL_INFO
SELECT TABLE_NAME, DS_TS_DETAIL_ID,D.DATASOURCE_ID,CATALOG_ID,TIME_SCALE, I.SERVICE_CREATE_TIME FROM ds_table_service_detail D LEFT JOIN DATA_SERVICE_INFO I ON D.SERVICE_ID =I.SERVICE_ID
—生成物化信息之信息项
SELECT
TABLE_NAME,
DS_TS_DETAIL_ID,
D.DATASOURCE_ID,
I.CATALOG_ID,
TIME_SCALE,
I.SERVICE_CREATE_TIME,
(SELECT group_concat(CATALOG_ITEM_ID) FROM CATALOG_ITEM_INFO M WHERE M.CATALOG_ID= I.CATALOG_ID)
FROM
ds_table_service_detail D
LEFT JOIN DATA_SERVICE_INFO I ON D.SERVICE_ID = I.SERVICE_ID

GROUP BY I.CATALOG_ID

SELECT
TABLE_NAME,
DS_TS_DETAIL_ID,
D.DATASOURCE_ID,
I.CATALOG_ID,
TIME_SCALE,
I.SERVICE_CREATE_TIME,
(SELECT group_concat( concat(’’’’,CATALOG_ITEM_ID,’’’’)) FROM CATALOG_ITEM_INFO M WHERE M.CATALOG_ID= I.CATALOG_ID)
FROM
ds_table_service_detail D
LEFT JOIN DATA_SERVICE_INFO I ON D.SERVICE_ID = I.SERVICE_ID

GROUP BY I.CATALOG_ID
–开放
SELECT group_concat( concat(’’’’,CATALOG_NAME,’’’’)) FROM CATALOG_INFO_OPEN
UPDATE CATALOG_INFO SET IF_OPEN_BUTT =‘1’ WHERE CATALOG_NAME IN();
–更新文件级联
UPDATE ds_file_service_detail SET FILE_STATUS=‘4’ WHERE FILE_IS_CASCADE=‘1’
UPDATE ds_file_service_detail SET FILE_STATUS=‘4’ ,FILE_IS_CASCADE=‘1’ WHERE SERVICE_ID IN( SELECT group_concat( concat(’’’’,SERVICE_ID,’’’’)) FROM DATA_SERVICE_INFO WHERE SERVICE_STATUS=‘4’)
UPDATE DS_API_INFO SET API_IS_CASCADE=‘1’ WHERE SERVICE_ID IN( SELECT group_concat( concat(’’’’,SERVICE_ID,’’’’)) FROM DATA_SERVICE_INFO WHERE SERVICE_STATUS=‘4’ AND SERVICE_KIND=‘1’ )

SELECT CONCAT(“WHEN '”,APPLICATIONSYS_NAME,"’"), CONCAT(“THEN '”,APPLICATIONSYS_NAME,"’") FROM ML_INFO_TYPE GROUP BY APPLICATIONSYS_NAME

UPDATE catalog_info set CREATE_TIME =‘2018-12-26 08:48:43’ WHERE CREATE_TIME IS null

SELECT * FROM catalog_info WHERE CREATE_TIME IS NULL
– UPDATE catalog_info SET CREATE_TIME = ‘2018-11-26 09:02:59’ WHERE CREATE_TIME IS NULL