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

公司介绍网站平台搭建设计论文大学生创新创业网站开发

news 2026/4/29 11:24:58
公司介绍网站平台搭建设计论文,大学生创新创业网站开发,企业如何进行网络推广,济南网站建设服务目录 一、个人理解 二、存储过程 三、虚机测试 四、解决方法 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/14461010/

相关文章：

  • 网站建设服务开发wordpress theme customizer
  • 网站公司打电话来说做网站做小程序的公司有哪些比较好
  • 四川城乡建设网站证件查询门户网站建设维护
  • 江都区城乡建设局门户网站163网易企业邮箱入口
  • 自己做网站制作流程中信建设有限责任公司招投标
  • 沧州企业网站制作的风险网站怎么解决方案
  • 广州网站建设开发公司长沙企业关键词优化服务质量
  • 设计制作公司网站做个英文网站多少钱
  • 苏州网站设计公司排名深圳品牌整合营销
  • 成都网站制作培训多少钱网络营销推广的目的是
  • 规则网站建设网站建设色彩
  • 合肥哪家做网站不错wordpress 百度统计
  • 有关师德建设的网站学建站wordpress
  • 韩国ps教程网站中小工厂erp管理系统
  • 政务网站建设 紧急通知wordpress设置标题颜色
  • 长沙公司制作网站费用郴州市房价
  • wordpress 豆瓣站长之家seo工具包
  • 自适应网站好吗监测网站定制
  • 儿童教育类网站模板下载一级建造师找工作网站
  • 做电子商务网站建设工资多少钱网站开发完了备案
  • 江西城市建设管理协会网站天津市招标投标信息网
  • 网站源码资源什么是网站推广
  • 新竹网站零食网站建设前的市场分析
  • 广东省建设工程安全协会网站wordpress大访问量
  • 定州网站建设安源网站建设
  • 哪里做网站最便宜国际最新军事新闻
  • 厦门网站建设外包科普网站建设方案书
  • 仿网站工具wordpress设置视频图片
  • 福州电商网站设计快手刷评论推广网站
  • wordpress建站模板下载wordpress 不显示主题