济南新网站优化wordpress播放上传视频

济南新网站优化,wordpress播放上传视频,门户网站模板 图片,wordpress搬家后访问异常背景 去年负责的一个订单系统#xff0c;单表数据量到了8000万#xff0c;查询开始变慢#xff0c;写入也受影响。 考虑过几个方案#xff1a; 归档历史数据#xff1a;治标不治本#xff0c;新数据还是会增长换TiDB#xff1a;改动太大#xff0c;风险高分库分表…背景去年负责的一个订单系统单表数据量到了8000万查询开始变慢写入也受影响。考虑过几个方案归档历史数据治标不治本新数据还是会增长换TiDB改动太大风险高分库分表业界成熟方案可控最后选了ShardingSphere做分库分表。这篇记录下完整的过程包括踩的坑。方案设计分片策略订单表按用户ID分片原因同一用户的订单在同一分片关联查询不跨库用户ID分布均匀业务上大部分查询都带用户ID分片规则4个库order_0, order_1, order_2, order_3每个库16张表t_order_0 ~ t_order_15库分片user_id % 4表分片user_id % 64 / 4保证同一用户在同一库的同一表数据量估算当前8000万条预期3年后5亿条单表上限500万条需要分片数5亿 / 500万 100个实际配置4库 × 16表 64个分片后续可扩展到8库 × 16表环境准备数据库准备创建4个数据库实例或在同一实例创建4个库-- 在每个实例上执行CREATEDATABASEorder_0DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;CREATEDATABASEorder_1DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;CREATEDATABASEorder_2DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;CREATEDATABASEorder_3DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;表结构-- 在每个库执行创建16张表CREATETABLEt_order_0(idBIGINTNOTNULL,order_noVARCHAR(32)NOTNULL,user_idBIGINTNOTNULL,statusTINYINTNOTNULLDEFAULT0,amountDECIMAL(10,2)NOTNULL,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,updated_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id),UNIQUEKEYuk_order_no(order_no),KEYidx_user_id(user_id),KEYidx_created_at(created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- t_order_1 ~ t_order_15 同样批量建表脚本#!/bin/bashfordbinorder_0 order_1 order_2 order_3;doforiin$(seq015);domysql -h$DB_HOST-u root -p$DB_PASS$dbEOF CREATE TABLE t_order_$i( id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; EOFdonedoneShardingSphere-JDBC配置我们用的是ShardingSphere-JDBC方式嵌入到应用中无需额外代理层。Maven依赖dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core/artifactIdversion5.4.1/version/dependency配置文件# application.ymlspring:datasource:driver-class-name:org.apache.shardingsphere.driver.ShardingSphereDriverurl:jdbc:shardingsphere:classpath:sharding-config.yaml# sharding-config.yamldataSources:ds_0:dataSourceClassName:com.zaxxer.hikari.HikariDataSourcedriverClassName:com.mysql.cj.jdbc.DriverjdbcUrl:jdbc:mysql://db-0.example.com:3306/order_0?useSSLfalseserverTimezoneAsia/Shanghaiusername:orderpassword:${DB_PASSWORD}connectionTimeout:30000maximumPoolSize:20minimumIdle:5ds_1:dataSourceClassName:com.zaxxer.hikari.HikariDataSourcedriverClassName:com.mysql.cj.jdbc.DriverjdbcUrl:jdbc:mysql://db-1.example.com:3306/order_1?useSSLfalseserverTimezoneAsia/Shanghaiusername:orderpassword:${DB_PASSWORD}connectionTimeout:30000maximumPoolSize:20minimumIdle:5ds_2:dataSourceClassName:com.zaxxer.hikari.HikariDataSourcedriverClassName:com.mysql.cj.jdbc.DriverjdbcUrl:jdbc:mysql://db-2.example.com:3306/order_2?useSSLfalseserverTimezoneAsia/Shanghaiusername:orderpassword:${DB_PASSWORD}connectionTimeout:30000maximumPoolSize:20minimumIdle:5ds_3:dataSourceClassName:com.zaxxer.hikari.HikariDataSourcedriverClassName:com.mysql.cj.jdbc.DriverjdbcUrl:jdbc:mysql://db-3.example.com:3306/order_3?useSSLfalseserverTimezoneAsia/Shanghaiusername:orderpassword:${DB_PASSWORD}connectionTimeout:30000maximumPoolSize:20minimumIdle:5rules:-!SHARDINGtables:t_order:actualDataNodes:ds_${0..3}.t_order_${0..15}databaseStrategy:standard:shardingColumn:user_idshardingAlgorithmName:database_modtableStrategy:standard:shardingColumn:user_idshardingAlgorithmName:table_modkeyGenerateStrategy:column:idkeyGeneratorName:snowflakeshardingAlgorithms:database_mod:type:MODprops:sharding-count:4table_mod:type:INLINEprops:algorithm-expression:t_order_${(user_id % 64).intdiv(4)}keyGenerators:snowflake:type:SNOWFLAKEprops:worker-id:1props:sql-show:truequery-with-cipher-column:false分片算法说明user_id 12345 库分片12345 % 4 1 → ds_1 表分片(12345 % 64) / 4 57 / 4 14 → t_order_14 最终路由到ds_1.t_order_14这样设计的好处同一用户的数据一定在同一张表里避免跨表查询。分布式主键不能用数据库自增ID了用雪花算法生成全局唯一ID。自定义雪花算法为了支持多数据中心自定义了worker-id分配publicclassDataCenterAwareSnowflakeimplementsKeyGenerateAlgorithm{privatestaticfinallongEPOCH1704067200000L;// 2024-01-01privatestaticfinallongWORKER_ID_BITS5L;privatestaticfinallongDATACENTER_ID_BITS5L;privatestaticfinallongSEQUENCE_BITS12L;privatefinallongworkerId;privatefinallongdatacenterId;privatelongsequence0L;privatelonglastTimestamp-1L;publicDataCenterAwareSnowflake(longdatacenterId,longworkerId){this.datacenterIddatacenterId;this.workerIdworkerId;}OverridepublicsynchronizedComparable?generateKey(){longtimestampSystem.currentTimeMillis();if(timestamplastTimestamp){thrownewRuntimeException(Clock moved backwards);}if(timestamplastTimestamp){sequence(sequence1)((1LSEQUENCE_BITS)-1);if(sequence0){timestampwaitNextMillis(lastTimestamp);}}else{sequence0L;}lastTimestamptimestamp;return((timestamp-EPOCH)(WORKER_ID_BITSDATACENTER_ID_BITSSEQUENCE_BITS))|(datacenterId(WORKER_ID_BITSSEQUENCE_BITS))|(workerIdSEQUENCE_BITS)|sequence;}privatelongwaitNextMillis(longlastTimestamp){longtimestampSystem.currentTimeMillis();while(timestamplastTimestamp){timestampSystem.currentTimeMillis();}returntimestamp;}}数据迁移这是最关键的步骤要保证业务不停服。双写方案阶段1只写老库 阶段2双写老库 新分片库 阶段3切读到新库 阶段4只写新库 阶段5下线老库历史数据迁移用分批迁移脚本#!/usr/bin/env python3importpymysqlfromdatetimeimportdatetimeimporttime BATCH_SIZE1000SLEEP_INTERVAL0.1# 控制迁移速度defget_shard_info(user_id):db_indexuser_id%4table_index(user_id%64)//4returnforder_{db_index},ft_order_{table_index}defmigrate_batch(src_conn,dst_conns,last_id):cursorsrc_conn.cursor(pymysql.cursors.DictCursor)cursor.execute( SELECT * FROM t_order WHERE id %s ORDER BY id LIMIT %s ,(last_id,BATCH_SIZE))rowscursor.fetchall()ifnotrows:returnNoneforrowinrows:db_name,table_nameget_shard_info(row[user_id])db_indexint(db_name.split(_)[1])dst_cursordst_conns[db_index].cursor()dst_cursor.execute(f INSERT INTO{table_name}(id, order_no, user_id, status, amount, created_at, updated_at) VALUES (%s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE updated_at VALUES(updated_at) ,(row[id],row[order_no],row[user_id],row[status],row[amount],row[created_at],row[updated_at]))dst_conns[db_index].commit()returnrows[-1][id]defmain():# 连接配置src_connpymysql.connect(hostold-db,userroot,passwordxxx,dborder)dst_conns[pymysql.connect(hostfdb-{i},userroot,passwordxxx,dbforder_{i})foriinrange(4)]last_id0total0whileTrue:last_idmigrate_batch(src_conn,dst_conns,last_id)iflast_idisNone:breaktotalBATCH_SIZEprint(f已迁移{total}条最后ID:{last_id})time.sleep(SLEEP_INTERVAL)print(f迁移完成共{total}条)if__name____main__:main()数据校验迁移完成后要做数据校验defverify_data():对比源库和目标库的数据src_cursorsrc_conn.cursor()# 按用户ID抽样检查src_cursor.execute(SELECT DISTINCT user_id FROM t_order ORDER BY RAND() LIMIT 1000)user_ids[row[0]forrowinsrc_cursor.fetchall()]errors[]foruser_idinuser_ids:# 源库数据src_cursor.execute(SELECT COUNT(*), SUM(amount) FROM t_order WHERE user_id %s,(user_id,))src_count,src_sumsrc_cursor.fetchone()# 目标库数据db_name,table_nameget_shard_info(user_id)db_indexint(db_name.split(_)[1])dst_cursordst_conns[db_index].cursor()dst_cursor.execute(fSELECT COUNT(*), SUM(amount) FROM{table_name}WHERE user_id %s,(user_id,))dst_count,dst_sumdst_cursor.fetchone()ifsrc_count!dst_countorabs(src_sum-dst_sum)0.01:errors.append({user_id:user_id,src:(src_count,src_sum),dst:(dst_count,dst_sum)})returnerrors常见问题处理跨分片查询不带user_id的查询会扫描所有分片-- 这个查询会路由到所有64个分片SELECT*FROMt_orderWHEREorder_noORD123456;解决方案冗余索引表维护order_no到user_id的映射CREATETABLEt_order_index(order_noVARCHAR(32)PRIMARYKEY,user_idBIGINTNOTNULL,created_atDATETIME);广播表小表在所有分片复制一份rules:-!SHARDINGbroadcastTables:-t_config-t_region分页查询跨分片分页是个大问题SELECT*FROMt_orderORDERBYcreated_atDESCLIMIT10000,20;这个查询会从每个分片取10020条数据然后在内存里排序取前20条。解决方案禁止深度分页产品上限制分页深度基于游标分页用上一页最后一条的ID作为游标-- 改成SELECT*FROMt_orderWHEREcreated_at2024-12-24 10:00:00ORDERBYcreated_atDESCLIMIT20;异步导出大量数据导出走离线任务聚合查询SELECTCOUNT(*),SUM(amount)FROMt_orderWHEREcreated_at2024-01-01;这个会在每个分片执行然后合并结果。性能还行但要注意AVG需要先算SUM和COUNT再除DISTINCT可能不准确跨分片JOINShardingSphere支持有限的跨分片JOIN但性能很差。解决方案宽表把关联数据冗余到主表应用层JOIN分两次查询应用层关联数据同步到ES复杂查询走ES监控与运维SQL审计rules:-!SQL_AUDITauditStrategies:order_audit:auditorNames:-slow_sql_auditorallowHintDisable:trueauditors:slow_sql_auditor:type:SLOW_SQLprops:threshold:1000# ms连接池监控暴露HikariCP指标到PrometheusBeanpublicMeterRegistryCustomizerMeterRegistrymetricsCommonTags(){returnregistry-{HikariDataSourceds(HikariDataSource)dataSource;ds.setMetricRegistry(registry);};}关键指标hikaricp_connections_activehikaricp_connections_pendinghikaricp_connections_timeout_total慢查询告警groups:-name:shardingrules:-alert:ShardingSlowQueryexpr:rate(shardingsphere_execute_latency_millis_sum[5m]) / rate(shardingsphere_execute_latency_millis_count[5m])100for:5mlabels:severity:warningannotations:summary:分片查询平均延迟超过100ms扩容方案如果4个库不够了需要扩容到8个库。方案一成倍扩容从4库扩到8库分片算法改成user_id % 8。需要迁移一半的数据ds_0 的一半数据迁移到 ds_4ds_1 的一半数据迁移到 ds_5…方案二一致性Hash用一致性Hash替代取模扩容时只需要迁移部分数据。但ShardingSphere配置更复杂。迁移时网络问题如果新旧数据库分布在不同的网络环境数据迁移可能受网络影响。之前遇到过跨机房迁移延迟导致迁移速度很慢。后来用星空组网打通网络迁移速度提升明显。踩过的坑1. 事务问题分布式事务性能差尽量避免跨分片事务。// 这样写会跨分片TransactionalpublicvoidcreateOrder(Orderorder,OrderItemitem){orderMapper.insert(order);orderItemMapper.insert(item);// 如果item和order不在同一分片}解决确保关联数据使用相同的分片键。2. 批量插入性能// 这样效率低for(Orderorder:orders){orderMapper.insert(order);// 每条都单独路由}优化按分片键分组同一分片的数据批量插入。3. 唯一索引冲突order_no需要全局唯一但分布在不同分片-- 这个唯一索引只在单表内生效UNIQUEKEYuk_order_no(order_no)解决order_no用分布式ID生成或者在应用层去重。4. 时钟回拨雪花算法依赖时钟时钟回拨会导致ID重复。解决NTP配置要稳定代码里检测时钟回拨等待或报错总结分库分表的核心要点分片键选择最重要选错了后面都是坑避免跨分片操作JOIN、事务、聚合都要小心分布式ID要可靠雪花算法配置好worker-id迁移方案要周全双写、校验、回滚方案都要有监控要到位慢查询、连接池、分片分布分库分表不是银弹引入了不少复杂度。如果数据量不是特别大单表千万级以内先优化索引和SQL实在不行再考虑分库分表。如果要做ShardingSphere是比较成熟的方案社区活跃文档也完善。