关于MySQL数据库分表问题-PHP Tutorial-php.cn

Home

Backend Development

PHP Tutorial

关于MySQL数据库分表问题

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 06, 2016 pm 08:42 PM

mysqlphp

最近需要做分表，搜集了一些相关资料，发现分表所带来的一系列问题（如列表显示、数据搜索），代价很高且复杂，且扩展性与后期维护性也很复杂！

搞得不敢用分表了，在想MySQL自带分区功能，为什么都不用呢，偏偏用复杂的分表问题，表示很不解。

如果按取余数来分表，以后再增加表就很麻烦，用一致性哈希算法倒是个好方法，但都面临同一个难题就是查询问题，不知道大家在实际项目中是怎么处理分表问题的？不想用MySQL自带的分表功能，它不是InnoDB的。

回复内容：

最近需要做分表，搜集了一些相关资料，发现分表所带来的一系列问题（如列表显示、数据搜索），代价很高且复杂，且扩展性与后期维护性也很复杂！

搞得不敢用分表了，在想MySQL自带分区功能，为什么都不用呢，偏偏用复杂的分表问题，表示很不解。

如果按取余数来分表，以后再增加表就很麻烦，用一致性哈希算法倒是个好方法，但都面临同一个难题就是查询问题，不知道大家在实际项目中是怎么处理分表问题的？不想用MySQL自带的分表功能，它不是InnoDB的。

菜鸟说下个人的理解哈，个人觉得这个还是要看自己具体业务的需求。比如一些历史数据或者大日志数据，这些数据如果操作性要求不高的话，可以尝试分表。之前处理呼叫中心业务，每天几百万的日志数据，就用的mysql自带的merge引擎分表，业务中主要是单做展示的用途，系统感觉运行的还OK。不过这种用法貌似现在已经不推荐了。现在用的是分区表，数据量也不算大，单表差不多快1000W的数据，性能同样ok。

应该思考一下分表的初心。
分表解决了什么问题，没有解决什么问题。

mysql自带的分表貌似最大只能分1024张表，扩展还是有局限性

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

PHP Performance Tuning for High Traffic WebsitesMay 14, 2025 am 12:13 AM

ThesecrettokeepingaPHP-poweredwebsiterunningsmoothlyunderheavyloadinvolvesseveralkeystrategies:1)ImplementopcodecachingwithOPcachetoreducescriptexecutiontime,2)UsedatabasequerycachingwithRedistolessendatabaseload,3)LeverageCDNslikeCloudflareforservin

Dependency Injection in PHP: Code Examples for BeginnersMay 14, 2025 am 12:08 AM

You should care about DependencyInjection(DI) because it makes your code clearer and easier to maintain. 1) DI makes it more modular by decoupling classes, 2) improves the convenience of testing and code flexibility, 3) Use DI containers to manage complex dependencies, but pay attention to performance impact and circular dependencies, 4) The best practice is to rely on abstract interfaces to achieve loose coupling.

PHP Performance: is it possible to optimize the application?May 14, 2025 am 12:04 AM

Yes,optimizingaPHPapplicationispossibleandessential.1)ImplementcachingusingAPCutoreducedatabaseload.2)Optimizedatabaseswithindexing,efficientqueries,andconnectionpooling.3)Enhancecodewithbuilt-infunctions,avoidingglobalvariables,andusingopcodecaching

PHP Performance Optimization: The Ultimate GuideMay 14, 2025 am 12:02 AM

ThekeystrategiestosignificantlyboostPHPapplicationperformanceare:1)UseopcodecachinglikeOPcachetoreduceexecutiontime,2)Optimizedatabaseinteractionswithpreparedstatementsandproperindexing,3)ConfigurewebserverslikeNginxwithPHP-FPMforbetterperformance,4)

PHP Dependency Injection Container: A Quick StartMay 13, 2025 am 12:11 AM

APHPDependencyInjectionContainerisatoolthatmanagesclassdependencies,enhancingcodemodularity,testability,andmaintainability.Itactsasacentralhubforcreatingandinjectingdependencies,thusreducingtightcouplingandeasingunittesting.

Dependency Injection vs. Service Locator in PHPMay 13, 2025 am 12:10 AM

Select DependencyInjection (DI) for large applications, ServiceLocator is suitable for small projects or prototypes. 1) DI improves the testability and modularity of the code through constructor injection. 2) ServiceLocator obtains services through center registration, which is convenient but may lead to an increase in code coupling.

PHP performance optimization strategies.May 13, 2025 am 12:06 AM

PHPapplicationscanbeoptimizedforspeedandefficiencyby:1)enablingopcacheinphp.ini,2)usingpreparedstatementswithPDOfordatabasequeries,3)replacingloopswitharray_filterandarray_mapfordataprocessing,4)configuringNginxasareverseproxy,5)implementingcachingwi

PHP Email Validation: Ensuring Emails Are Sent CorrectlyMay 13, 2025 am 12:06 AM

PHPemailvalidationinvolvesthreesteps:1)Formatvalidationusingregularexpressionstochecktheemailformat;2)DNSvalidationtoensurethedomainhasavalidMXrecord;3)SMTPvalidation,themostthoroughmethod,whichchecksifthemailboxexistsbyconnectingtotheSMTPserver.Impl

See all articles