当前位置： 首页 > news >正文

公司介绍网站平台搭建设计论文石家庄模板建站

news 2026/5/2 12:16:30
公司介绍网站平台搭建设计论文,石家庄模板建站,超大网站制作素材,智能小程序开发哪家好目录 一、个人理解 二、存储过程 三、虚机测试 四、解决方法 1、重建表 2、shrink space 一、个人理解 空洞率的产生是由于delete语句并不会真实的删除数据#xff0c;只是在数据上打了一个不可见标签#xff0c;但实际还是占用着相应的存储空间。 二、存储过程 自定义…目录 一、个人理解 二、存储过程 三、虚机测试 四、解决方法 1、重建表 2、shrink space 一、个人理解 空洞率的产生是由于delete语句并不会真实的删除数据只是在数据上打了一个不可见标签但实际还是占用着相应的存储空间。 二、存储过程 自定义存储过程实现查询全库不计算系统库中所有表的空洞率。 drop procedure if exists CheckALLDbVoidRate;DELIMITER // CREATE PROCEDURE CheckALLDbVoidRate(VoidRate varchar(30)) beginDECLARE SelectALLTableSql text;DECLARE SelectTableVoidRate text;DECLARE ExitFlag INT; DECLARE TmpDB VARCHAR(100);DECLARE TmpTab VARCHAR(100);DECLARE cur REF CURSOR; DECLARE CONTINUE HANDLER FOR SQLSTATE 02000 SET ExitFlag 1;set ExitFlag 0;set SelectALLTableSql select TABLE_SCHEMA ,TABLE_NAME from information_schema.tables where TABLE_TYPE \BASE TABLE\ AND TABLE_SCHEMA not in (\information_schema\,\performance_schema\,\gbase\,\gclusterdb\);set SelectTableVoidRate select TABLE_SCHEMA,TABLE_NAME,truncate(STORAGE_SIZE/1024/1024) as STORAGESIZE,DELETE_RATIO from performance_schema.tables where table_schema? and table_name? and DELETE_RATIO ||VoidRate||;;set ExecuteSql SelectALLTableSql;set ExecuteSql_1 SelectTableVoidRate;prepare stmt from ExecuteSql_1;OPEN cur FOR ExecuteSql;WHILE ExitFlag 0 DOFETCH cur INTO TmpDB,TmpTab; if ExitFlag 0 thenset Tab TmpTab;set DB TmpDB;# select DB,Tab,ExecuteSql_1;EXECUTE stmt using DB,Tab ; end if;END WHILE; CLOSE cur; deallocate prepare stmt; end;// DELIMITER ;call CheckALLDbVoidRate(0); 参数名解释VoidRate输入参数字符串类型表示输出大于等于空洞率为VoidRate的表。 三、虚机测试 gbase drop procedure if exists CheckALLDbVoidRate; Query OK, 0 rows affected (Elapsed: 00:00:00.00)gbase gbase DELIMITER // gbase CREATE PROCEDURE CheckALLDbVoidRate(VoidRate varchar(30))- begin- DECLARE SelectALLTableSql text;- DECLARE SelectTableVoidRate text;- DECLARE ExitFlag INT; - DECLARE TmpDB VARCHAR(100);- DECLARE TmpTab VARCHAR(100);- DECLARE cur REF CURSOR; - DECLARE CONTINUE HANDLER FOR SQLSTATE 02000 SET ExitFlag 1;- - set ExitFlag 0;- - set SelectALLTableSql select TABLE_SCHEMA ,TABLE_NAME from information_schema.tables where TABLE_TYPE \BASE TABLE\ AND TABLE_SCHEMA not in (\information_schema\,\performance_schema\,\gbase\,\gclusterdb\);- - set SelectTableVoidRate select TABLE_SCHEMA,TABLE_NAME,truncate(STORAGE_SIZE/1024/1024) as STORAGESIZE,DELETE_RATIO from performance_schema.tables where table_schema? and table_name? and DELETE_RATIO ||VoidRate||;;- - - set ExecuteSql SelectALLTableSql;- set ExecuteSql_1 SelectTableVoidRate;- - prepare stmt from ExecuteSql_1;- - OPEN cur FOR ExecuteSql;- WHILE ExitFlag 0 DO- FETCH cur INTO TmpDB,TmpTab; - if ExitFlag 0 then- set DB TmpDB;- set Tab TmpTab;- # select DB,Tab,ExecuteSql_1;- EXECUTE stmt using DB,Tab ; - end if;- END WHILE;- CLOSE cur; - - deallocate prepare stmt;- end;// Query OK, 0 rows affected (Elapsed: 00:00:00.00)gbase DELIMITER ; gbase gbase call CheckALLDbVoidRate(0); ------------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------------- | bd_db_a141 | t_hn_customer_list | 0 | 0 | ------------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.00)------------------------------------------------------------ | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------------ | bd_db_a141 | t_hn_stage_target | 0 | 0 | ------------------------------------------------------------ 1 row in set (Elapsed: 00:00:00.00)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | a | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.01)-------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | -------------------------------------------------------- | czg | alldbvoidrate | 0 | 0 | -------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.01)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | b | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.01)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | czg | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.01)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | czg_test | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.01)----------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------------- | czg | d_admin_kpi_code | 0 | 0 | ----------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.02)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | czg | hash_tb_like | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.02)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | moon | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.02)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | moon_copy | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.02)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | sun | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.02)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | test | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.02)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | czg | test20230302 | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.03)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | test_12_05 | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.03)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | test_table | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.03)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | czg | test_table_1 | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.03)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | czg | test_table_2 | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.04)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | czg | test_table_3 | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.04)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | czg | testtab | 9 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.04)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | czg | testtab_copy | 4 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.04)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | a | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.04)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | b | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.05)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | czg | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.05)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | czg_test | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.05)----------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------------- | zxj | d_admin_kpi_code | 0 | 0 | ----------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.05)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | zxj | hash_tb_like | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.06)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | moon | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.06)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | moon_copy | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.06)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | sun | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.06)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | test_12_05 | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.07)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | test_table | 0 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.07)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | zxj | test_table_1 | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.07)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | zxj | test_table_2 | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.07)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | zxj | test_table_3 | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.08)----------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ----------------------------------------------------- | zxj | testtab | 1 | 0 | ----------------------------------------------------- 1 row in set (Elapsed: 00:00:00.08)------------------------------------------------------- | TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO | ------------------------------------------------------- | zxj | testtab_copy | 0 | 0 | ------------------------------------------------------- 1 row in set (Elapsed: 00:00:00.08)Query OK, 0 rows affected (Elapsed: 00:00:00.08) 参数名说明TABLE_SCHEMA库名TABLE_NAME表名STORAGESIZE存储空间单位MDELETE_RATIO空洞率单位% 四、解决方法 1、重建表 我本来想把SQL包到存储过程中的发现存储过程中不允许执行lock语句会提示如下错误。 ERROR 1314 (0A000): LOCK is not allowed in stored procedures 放到语句句柄中执行报错如下 This command is not supported in the prepared statement protocol yet 所以我们手动执行了或者想包到其它语言里也行方便。 gbase lock table zxj.testtab write; Query OK, 0 rows affected (Elapsed: 00:00:00.01)gbase create table zxj.testtab_new like zxj.testtab; Query OK, 0 rows affected (Elapsed: 00:00:00.13)gbase insert into zxj.testtab_new select * from zxj.testtab; Query OK, 0 rows affected (Elapsed: 00:00:00.02) Records: 0 Duplicates: 0 Warnings: 0gbase rename table zxj.testtab to zxj.testtab_old; Query OK, 0 rows affected (Elapsed: 00:00:00.02)gbase rename table zxj.testtab_new to zxj.testtab; Query OK, 0 rows affected (Elapsed: 00:00:00.02)gbase unlock tables; Query OK, 0 rows affected (Elapsed: 00:00:00.00)gbase drop table zxj.testtab_old; Query OK, 0 rows affected (Elapsed: 00:00:00.01) 2、shrink space 建议在有gcluster_shrink_to_rebalance这个参数的版本使用shrink space full避免出现一些不必要的麻烦。 gbase set gcluster_shrink_to_rebalance1; Query OK, 0 rows affected (Elapsed: 00:00:00.01)gbase alter table zxj.testtab shrink space full ; Query OK, 0 rows affected (Elapsed: 00:00:00.20) 如果不加full如下 gbase alter table zxj.testtab shrink space; Query OK, 0 rows affected (Elapsed: 00:00:00.01) 这种耗时会较短在实际操作中但释放空间效率不高因为gbase按照列进行存储一个列存储成一个文件文件大小为2G也就是下面的这个参数需要这个文件中的每一条数据都打上删除标记才能删除文件来释放空间。 base show variables like_gbase_segment_size; --------------------------------- | Variable_name | Value | --------------------------------- | _gbase_segment_size | 2147483648 | --------------------------------- 1 row in set (Elapsed: 00:00:00.00)gbase select 2147483648 / 1024 / 1024 / 1024; --------------------------------- | 2147483648 / 1024 / 1024 / 1024 | --------------------------------- | 2.000000000000 | --------------------------------- 1 row in set (Elapsed: 00:00:00.00)
http://www.hkea.cn/news/14501390/

相关文章：

  • 招聘网站做专题的目的镇江大港属于哪个区
  • 调研园区网站建设工作做广告图片用什么软件
  • asp模板网站修改佛山网站建设哪家公司好
  • 南宁市建设局网站摄影设计工作室
  • 赣州网站开发制作网站手机app开发
  • 优秀的电子商务网站外贸网站推广计划书
  • 上海 网站建设公司茂名网站建设电话
  • 建设设计项目备案在哪个网站做网站的表情包
  • 无锡网站优化推广方案凡客诚品倒闭了吗知乎
  • 商丘网站建设今天最新体育新闻足球
  • 什么是网站优化主要包括那几个网站建设的客户
  • php与H5做网站山东一级造价师考试时间
  • 建设营销型网站的目的中国建设银行股份有限公司
  • 写网站建设需求百度搜索网站在第一次输入搜索内容后点搜索键没有反应
  • php网站源码带后台青岛网站开发招聘
  • 惠安县建设局网站业务员客户管理软件
  • 招聘网站分析如何做企业营销型网站的内容
  • 建设网站公司专业服务做电影网站赚钱吗
  • 手机网站视频播放模板济南网站地址
  • 网站开发工具概述与比较设计一个网站需要多久
  • 景安网络网站建设教程html登录注册页面代码
  • seo网站优化专员rio门户网站的制作
  • 传媒公司网站设计企业网站 建设过程
  • 好的专业网站建设公司沈阳关键词优化费用
  • 网站属性设置域名 删除 wordpress
  • 网站开发 工作量评估wordpress首页模板文件
  • 网站自适应代码信阳做网站 汉狮网络
  • 建设网站怎么添加端口映射开发一款app软件可以赚多少钱
  • 买完域名后如何建设网站专业做加盟推广的公司
  • 电商网站现状分析宁乡小程序开发