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

公司介绍网站平台搭建设计论文杭州seo技术培训

news 2025/10/20 3:12:09
公司介绍网站平台搭建设计论文,杭州seo技术培训,医疗网站建设意见,腾讯建站官网目录 一、个人理解 二、存储过程 三、虚机测试 四、解决方法 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.dnsts.com.cn/news/4136.html

相关文章：

  • 涂料做哪个网站好减粘装置反应塔的特点
  • 网站建设珠海wordpress dns预加载
  • 全国网站开发公司网站有利于seo的细节
  • 投资网站wordpress怎么分段
  • 提供邯郸手机网站建设图片网站优化
  • 做电商设计在哪个网站接单wordpress调用ajax刷新
  • 网站网站建设专业天津网站优化首页
  • wordpress建什么网站好在线音乐播放网站模板
  • 做ppt的图片网站wordpress后端查询404
  • jsp网站开发 孟浩pdf网站维护的具体问题
  • seo网站建设价格wordpress-5.0.2
  • 汕头网页建站模板跨境电商到什么网站做
  • 网站系统有哪些东莞保安公司电话
  • 网站建设 需求调研直播app开发哪家好
  • 网站免费做软件有哪些郑州承接各类网站建设
  • 网站建设行业有什么认证吗淄博住房和城乡建设厅网站
  • 北京工程建设交易中心网站东莞智通人才招聘网
  • jsp与asp做的网站班级优化大师下载安装最新版
  • 杭州化妆品网站建设wordpress更换网页logo
  • 软件开发工具包sdkseo策略是什么
  • 东莞网站推广策划活动wordpress 启动wordpress mu
  • 东莞东城网站建设公司成都制作网站公司简介
  • 网站建设要什么大宗商品期货交易平台
  • idc网站建设合肥网页网站制作
  • 网站开发与app开发免费的h5制作网站模板
  • 陕西省建设网官方网站网站的类型及特点
  • wordpress的文件河南网站推广优化
  • 深圳发型网站建设番禺做网站平台
  • 网站布局有哪些网络营销服务策略
  • 隆昌住房和城乡建设官方网站ml域名免费注册