网络营销推广策划案例,服务好的镇江网站优化,简历网,怎么让学生在网站上做问卷调查目录
一、为什么不用视图#xff08;view#xff09;
视图的优点#xff1a;
视图的缺点#xff1a;
二、为什么不用merge分表
MERGE分表的优点#xff1a;
MERGE分表的问题#xff1a;
三、水平分区的模式#xff1a;
四、表分区的注意
1、引擎的统一
2、分区…目录
一、为什么不用视图view
视图的优点
视图的缺点
二、为什么不用merge分表
MERGE分表的优点
MERGE分表的问题
三、水平分区的模式
四、表分区的注意
1、引擎的统一
2、分区关联性
3、分区的级别
4、LIST分区
5、Linear线性
6、指定用来分区的字段必须是主键或者唯一索引否则会报错。
7、删除分区drop会删除该分区的数据要特别小心
8、当分区表有最大分区的时候不能直接添加分区
9、当删除分区表分区的时候drop 和truncate都会导致global索引失效需要注意。
五、分区的管理
1、新增分区
2、重建分区 3、删除分区 4、给已有的表加上分区 5、新建分区 六、添加过程
七、添加事件
八、查看分区情况 参考MySql从一窍不通到入门五Sharding分表、分库、分片和分区_Invokers Tower-CSDN博客
一、为什么不用视图view
视图的优点
1.第一个显著优点就是它简化了操作。此时我们完全不用关心视图是怎么处理数据的我们只需要知道如何使用这个结果集即可视图相当于一个中间层。
2.第二个显著优点就是它更加安全。比如我们可以让用户有权去访问某个视图但是不能访问原表这样就可以起到保护原表中某些数据的作用。
3.我们之后会接触到管理权限权限是无法细致到某一个列的通过视图则很容易实现。
4.第三个显著优点就是降低耦合。假如我们以后要修改原表的结构那么我们可以通过修改视图的定义即可而不用修改应用程序对访问者是不会造成影响的一般来说这样代价会更小。
视图的缺点
1.性能从数据库视图查询数据可能会很慢特别是如果视图是基于其他视图创建的。
2.表依赖关系将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时都必须更改视图。
二、为什么不用merge分表
转载Mysql通过Merge引擎进行分表 - 南哥的天下 - 博客园
参考https://www.cnblogs.com/easonscx/p/12598092.html
Mysql各种引擎原理实战对比_dhmkjv0619的博客-CSDN博客
MERGE分表的优点
1. 适用于存储日志数据。例如可以将不同月份的数据存入不同的表然后使用myisampack工具压缩数据最后通过一张MERGE表来查询这些数据。
2. 可以获得更快的速度。可以根据某种指标将一张只读的大表分割成若干张小表然后将这些小表分别放在不同的磁盘上存储。当需要读取数据时MERGE表可以将这些小表的数据组织起来就好像使用先前的大表一样但是速度会快很多。
3. 可以提高搜索效率。可以根据某种指标将一张只读的大数据表分割为若干个小表然后根据不同的查询维度可以得到若干种小表的组合然后再为这些组合分别创建不同的MERGE表。例如有一张只读的大数据表T分割为T1、T2、T3、T4共4张小表有两种查询维度A和BA可以得到小表组合T1、T2和T3B可以得到小表组合T2、T3和T4分别为A和B创建两个MERGE表也就是M1和M2这两个MERGE表分别关联的小表是存在交叠的。
4. 可以更加有效的修复表。修复单个的小表要比修复大数据表更加容易。
5. 多个子表映射至一个总表的速度极快。因为MERGE表本身不会存储和维护任何索引索引都是由各个关联的子表存储和维护的所以创建和重新映射MERGE表的速度非常快。
6. 不受操作系统的文件大小限制。单个表会受到文件大小的限制但是拆分成多个表则可以无限扩容。
7. MERGE表还可以用来给单个表创建别名并且几乎不会影响性能。
MERGE分表的问题
1. 总表MERGE表必须使用MRG_MyISAM存储引擎子表必须使用MyISAM存储引擎不可避免会受到MyISAM存储引擎的限制。
2. MERGE表不能使用某些MyISAM特性。例如虽然可以为子表创建全文索引但是却不能使用全文索引通过MERGE表查询数据。
3. MERGE表会使用更多的文件描述符。如果有10个客户端使用1张MERGE表那么就需要消耗(10×10)10个文件描述符其中10个客户端分别有10个数据文件描述符并且会共享使用10个索引文件描述符。
4. 若使用ALTER TABLE语句修改总表的存储引擎那么会立即丢失总表和子表的映射关系并且会将所有子表的数据拷贝至修改后的新表。
5. 总表和子表的主键都不能使用自动增长auto increment。
6. 子表之间不能保证唯一键约束只能保证单个子表内部的唯一性约束。
7. 由于不能保证唯一键约束导致REPLACE语句的行为会不可预期INSERT ... ON DUPLICATE KEY UPDATE语句也有类似问题。因此只能使用路由策略对子表使用这些语句而不能对总表使用。
8. 子表不支持分区Partition。
9. 当正在使用总表时不能对任何子表执行ANALYZE TABLE、REPAIR TABLE、OPTIMIZE TABLE、ALTER TABLE、DROP TABLE、DELETE或TRUNCATE TABLE语句否则会导致不可预期的结果。
10. 总表和子表的表结构必须完全一致。
11. 总表可以映射的所有子表的总行数上限为 2的64次方 行。
12. 不支持INSERT DELAYED语句。
补充
13. MyISAM并不支持事务以及行级锁而且一个毫无疑问的缺陷是崩溃后无法安全恢复。正是由于MyISAM引擎的缘故即使MySQL支持事务已经很长时间了在很多人的概念中MySQL还是非事务型数据库。
三、水平分区的模式
转载深入解析MySQL分区Partition功能 - 龙福 - 博客园
Range范围 – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区80年代1980s的数据90年代1990s的数据以及任何在2000年包括2000年后的数据。 Hash哈希 – 这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。 Key键值 – Hash模式的一种延伸这里的Hash Key是MySQL系统产生的。 List预定义列表 – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如DBA建立了一个横跨三个分区的表分别根据2004年2005年和2006年值所对应的数据。 Composite复合模式 - 很神秘吧哈哈其实是以上模式的组合使用而已就不解释了。举例在初始化已经进行了Range范围分区的表上我们可以对其中一个分区再进行hash哈希分区。
四、表分区的注意
转载数据表分区策略及实现一_云水之路的专栏-CSDN博客
1、引擎的统一
在对同一个表进行分区时必须保证数据表的引擎相同比如不能对一个分区的表为InnoDB而另一个分区的引擎为MySIAM。
2、分区关联性
在对数据表分区时不能只对数据进行分区需要连同其对应的索引等属性一同分区动作某种程度上可以保持数据属性的完整。
3、分区的级别
对表进行分区之后如果某个分区中的数据量依然很大或是增长迅速那么你同样可以再进行子分区操作将该数据再分区到其它分区中。另外如果在一个分区中使用了子分区那么其它的子分区也必须定义。
4、LIST分区
LIST分区没有类似如“VALUESLESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
5、Linear线性
分区策略KEY和HASH都支持使用线性LINEAR的算法也就是分区的编号是通过2的幂powers-of-two算法得到而不是通过模数算法。
补充Range模式
6、指定用来分区的字段必须是主键或者唯一索引否则会报错。
7、删除分区drop会删除该分区的数据要特别小心
8、当分区表有最大分区的时候不能直接添加分区
需要用到split partition但是mysql好像没有split命令
9、当删除分区表分区的时候drop 和truncate都会导致global索引失效需要注意。
可以加参数 update global indexes 在11g的时候这个参数会影响数据库性能所以我们一般都是之后手动rebuild索引。 但是在12c及以后这个参数更加智能会把工作安排到数据库空闲状态进行但是我们一般还是选择把删除分区这种操作在空闲时候执行然后手动重建索引。对于本地索引删除分区表的时候不会影响到本地索引的状态。不知道11g、12c什么意思。没有实际验证
五、分区的管理
转载深入解析MySQL分区Partition功能 - 龙福 - 博客园
1、新增分区 #新增一个RANGE分区
ALTER TABLE mytest ADD PARTITION (PARTITION p20220313 VALUES LESS THAN (TO_DAYS (2022-03-13))); #新增默认分区 ALTER TABLE mytest ADD PARTITION (PARTITION pmax VALUES LESS THAN (maxvalue)) 转载mysql 分区_Mysql分区表maxvalue分区重组_weixin_39535125的博客-CSDN博客
mysql在使用了maxvalue作为最后分区以后是无法直接创建新分区的他认为一旦使用了maxvalue就是最后一个分区了。还好我们还有一个命令REORGANIZE可以将最后一个分区进行重组从而达到创建新分区的目的。 ALTER TABLE og_register_log REORGANIZE PARTITION pcatchall INTO ( PARTITION p202011 VALUES less than ( 202012 ), PARTITION pcatchall VALUES LESS THAN MAXVALUE ENGINE MyISAM ) 我们将最后一个分区重组以后pcatchall就变成2个分区了。并且原先落到pcatchall分区的数据也会重新分配到新的p202011分区。
2、重建分区 ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000)); #将原来的 p0,p1 分区合并起来放到新的 p0 分区中。 3、删除分区 ALTER TABLE users DROP PARTITION p0; #删除分区 p0 4、给已有的表加上分区 alter table results partition by RANGE (month(ttime))
(
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2) ,
PARTITION p2 VALUES LESS THAN (3) ,
PARTITION p3 VALUES LESS THAN (4) ,
PARTITION p4 VALUES LESS THAN (5) ,
PARTITION p5 VALUES LESS THAN (6) ,
PARTITION p6 VALUES LESS THAN (7) ,
PARTITION p7 VALUES LESS THAN (8) ,
PARTITION p8 VALUES LESS THAN (9) ,
PARTITION p9 VALUES LESS THAN (10) ,
PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN (13)
); 5、新建分区 CREATE TABLE mytest ( id bigint(20) NOT NULL AUTO_INCREMENT COMMENT id, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE PRIMARY KEY (id,create_time) USING BTREE ) ENGINEInnoDB AUTO_INCREMENT41 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_german2_ci ROW_FORMATDYNAMIC COMMENT测试表 /*!50100 PARTITION BY RANGE (TO_DAYS(create_time)) (PARTITION p20220312 VALUES LESS THAN (to_days(2022-03-12)) ENGINE InnoDB, PARTITION p20220612 VALUES LESS THAN (to_days(2022-06-12)) ENGINE InnoDB) */; 六、添加过程
参考MySQL 存储过程 | 菜鸟教程
每3个月自动添加分区。 DELIMITER $$DROP PROCEDURE IF EXISTS add_partition_mytest$$CREATE PROCEDURE add_partition_mytest()
BEGINSTART TRANSACTION;/* 到系统表查出这个表的最大分区得到最大分区的日期。在创建分区的时候名称就以日期格式存放方便后面维护 */SELECT REPLACE(partition_name,p,) INTO P12_Name FROM INFORMATION_SCHEMA.PARTITIONSWHERE table_namemytest ORDER BY partition_ordinal_position DESC LIMIT 1;SET Max_date DATE(DATE_ADD(P12_Name0, INTERVAL 3 MONTH))0;
/* 修改表在最大分区的后面增加一个分区时间范围加1天 */SET s1CONCAT(ALTER TABLE mytest ADD PARTITION (PARTITION p,Max_date, VALUES LESS THAN (TO_DAYS (,DATE(Max_date),))));/* 输出查看增加分区语句*/SELECT s1;PREPARE stmt2 FROM s1;EXECUTE stmt2;DEALLOCATE PREPARE stmt2;
/* 提交 */COMMIT ;END$$DELIMITER ; 其中DELIMITER 定好结束符为$$, 然后最后又定义为;, MYSQL的默认结束符为;。
默认情况下不可能等到用户把这些语句全部输入完之后再执行整段语句。 因为mysql一遇到分号它就要自动执行。 即在语句RETURN ;时mysql解释器就要执行了。 这种情况下就需要事先把delimiter换成其它符号如//或$$。
七、添加事件
每3个月自动添加分区。 DELIMITER ||
CREATE EVENT add_event_mytest
ON SCHEDULEEVERY 3 month STARTS 2022-03-10 03:00:00DOBEGINCALL add_partition_mytest();END ||
DELIMITER ;查询mysql事件调度器是否开启 /* 查看定时器开关情况 */
show VARIABLES like event_scheduler;
/* 开启定时器开关 */
set GLOBAL event_scheduler 1; 八、查看分区情况 SELECT partition_name, partition_ordinal_position, TABLE_Rows, table_schema
FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_name tl_alarm_data
ORDER BY partition_ordinal_position DESC 九、分区表能不能提升性能 压测时发现表分区还不如不分区加索引性能更好。测试的结果是这样的当数据是几十万级别时分区表要3s不分区只要200ms。当数据是200w级别时分区表也是3点几s不分区要6点几秒查看分页的最后几页。跟预想的相差很大。即使是自己分表也比这性能更好。
网上收了一下各种声音都有没有找到说的很明白的。
折腾了一个晚上终于找到原因所在分区字段必须加入主键所以就会形成至少两个字段的复合主键。通常id是第一主键分区字段是第二主键。
参考复合索引和效率_Lyndon1115的博客-CSDN博客 感谢博主。
摘出重要部分
从以上试验中我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的甚至比用上全部的复合索引列还要略快在查询结果集数目一样的情况下而如果仅用复合聚集索引的非起始列作为查询条件的话这个索引是不起任何作用的。当然语句1、2的查询速度一样是因为查询的条目数一样如果复合索引的所有列都用上而且查询结果少的话这样就会形成“索引覆盖”因而性能可以达到最优。同时请记住无论您是否经常使用聚合索引的其他列但其前导列一定要是使用最频繁的列。
如果两个索引idx1单一,idx2复合都存在 并不是 where col1A用idx1where col1A and col2B 用idx2。 其查询优化器使用其中一个以前常用索引。要么都用idx1,要么都用idx2.
这就是为什么我给分区字段单独建了索引时性能没有改善了。
解决方案
很明显只要把分区字段改为第一主键就可以了
id不能再用自增了改成雪花算法TableId(type IdType.ASSIGN_ID)。
测试了一下效果非常不错。
结论
表分区再数据量少时基本和加了索引的不分区表差不多略逊可接受
数据量大时查询聊率提升明显前提时分区字段一定要作为检索条件限制每次查询只覆盖两个分区 - 纠正提升不是很明显略优。分区和不分区加索引性能差不多。
十、查询总是有第一个分区
参考MySQL Bugs: #49754: Partitioning by RANGE with TO_DAYS always includes first partition when pruning 因为分区字段为null或者是无效时间是都归属于第一个分区。所以每次查询都会有第一个分区。可以加一个p0分区PARTITION p0 VALUES LESS THAN (0)测试了一下加不加性能没影响。 文章转载自： http://www.morning.lzqnj.cn.gov.cn.lzqnj.cn http://www.morning.qwgct.cn.gov.cn.qwgct.cn http://www.morning.syynx.cn.gov.cn.syynx.cn http://www.morning.ftznb.cn.gov.cn.ftznb.cn http://www.morning.mjglk.cn.gov.cn.mjglk.cn http://www.morning.wslr.cn.gov.cn.wslr.cn http://www.morning.jfwrf.cn.gov.cn.jfwrf.cn http://www.morning.gmrxh.cn.gov.cn.gmrxh.cn http://www.morning.tbcfj.cn.gov.cn.tbcfj.cn http://www.morning.rfyk.cn.gov.cn.rfyk.cn http://www.morning.xprq.cn.gov.cn.xprq.cn http://www.morning.fbdtd.cn.gov.cn.fbdtd.cn http://www.morning.bkkgt.cn.gov.cn.bkkgt.cn http://www.morning.zhiheliuxue.com.gov.cn.zhiheliuxue.com http://www.morning.dwzwm.cn.gov.cn.dwzwm.cn http://www.morning.tmfhx.cn.gov.cn.tmfhx.cn http://www.morning.npfrj.cn.gov.cn.npfrj.cn http://www.morning.xqndf.cn.gov.cn.xqndf.cn http://www.morning.mslhq.cn.gov.cn.mslhq.cn http://www.morning.ytnn.cn.gov.cn.ytnn.cn http://www.morning.jwgmx.cn.gov.cn.jwgmx.cn http://www.morning.kqglp.cn.gov.cn.kqglp.cn http://www.morning.cjsnj.cn.gov.cn.cjsnj.cn http://www.morning.drmbh.cn.gov.cn.drmbh.cn http://www.morning.fbbpj.cn.gov.cn.fbbpj.cn http://www.morning.pclgj.cn.gov.cn.pclgj.cn http://www.morning.qztsq.cn.gov.cn.qztsq.cn http://www.morning.tyrlk.cn.gov.cn.tyrlk.cn http://www.morning.kxwsn.cn.gov.cn.kxwsn.cn http://www.morning.pttrs.cn.gov.cn.pttrs.cn http://www.morning.qyqdz.cn.gov.cn.qyqdz.cn http://www.morning.tbnn.cn.gov.cn.tbnn.cn http://www.morning.tqsmc.cn.gov.cn.tqsmc.cn http://www.morning.qxmpp.cn.gov.cn.qxmpp.cn http://www.morning.tngdn.cn.gov.cn.tngdn.cn http://www.morning.qxlxs.cn.gov.cn.qxlxs.cn http://www.morning.nhzxd.cn.gov.cn.nhzxd.cn http://www.morning.qwyms.cn.gov.cn.qwyms.cn http://www.morning.ydnx.cn.gov.cn.ydnx.cn http://www.morning.jtszm.cn.gov.cn.jtszm.cn http://www.morning.nxpqw.cn.gov.cn.nxpqw.cn http://www.morning.nfzzf.cn.gov.cn.nfzzf.cn http://www.morning.yfcbf.cn.gov.cn.yfcbf.cn http://www.morning.ldnrf.cn.gov.cn.ldnrf.cn http://www.morning.syhwc.cn.gov.cn.syhwc.cn http://www.morning.bcdqf.cn.gov.cn.bcdqf.cn http://www.morning.dkqr.cn.gov.cn.dkqr.cn http://www.morning.lzqnj.cn.gov.cn.lzqnj.cn http://www.morning.kyzja.com.gov.cn.kyzja.com http://www.morning.tbjtp.cn.gov.cn.tbjtp.cn http://www.morning.lznfl.cn.gov.cn.lznfl.cn http://www.morning.kwyq.cn.gov.cn.kwyq.cn http://www.morning.rnqrl.cn.gov.cn.rnqrl.cn http://www.morning.rxyz.cn.gov.cn.rxyz.cn http://www.morning.hgwsj.cn.gov.cn.hgwsj.cn http://www.morning.duqianw.com.gov.cn.duqianw.com http://www.morning.rwqk.cn.gov.cn.rwqk.cn http://www.morning.gjfym.cn.gov.cn.gjfym.cn http://www.morning.gjmbk.cn.gov.cn.gjmbk.cn http://www.morning.webpapua.com.gov.cn.webpapua.com http://www.morning.cnkrd.cn.gov.cn.cnkrd.cn http://www.morning.qxlyf.cn.gov.cn.qxlyf.cn http://www.morning.wnxqf.cn.gov.cn.wnxqf.cn http://www.morning.qnywy.cn.gov.cn.qnywy.cn http://www.morning.yhrfg.cn.gov.cn.yhrfg.cn http://www.morning.gkmwx.cn.gov.cn.gkmwx.cn http://www.morning.sryyt.cn.gov.cn.sryyt.cn http://www.morning.nsrlb.cn.gov.cn.nsrlb.cn http://www.morning.mkydt.cn.gov.cn.mkydt.cn http://www.morning.hjsrl.cn.gov.cn.hjsrl.cn http://www.morning.jbpodhb.cn.gov.cn.jbpodhb.cn http://www.morning.kxrhj.cn.gov.cn.kxrhj.cn http://www.morning.zlnyk.cn.gov.cn.zlnyk.cn http://www.morning.nqbs.cn.gov.cn.nqbs.cn http://www.morning.srgnd.cn.gov.cn.srgnd.cn http://www.morning.qjlkp.cn.gov.cn.qjlkp.cn http://www.morning.ftmly.cn.gov.cn.ftmly.cn http://www.morning.nbsbn.cn.gov.cn.nbsbn.cn http://www.morning.dskmq.cn.gov.cn.dskmq.cn http://www.morning.kjmcq.cn.gov.cn.kjmcq.cn
