草庐IT

掌握MySQL分库分表（一）数据库性能优化思路、分库分表优缺点

豆浆两块钱 2023-12-01 原文

文章目录


不能⼀上来就说分库分表！

MySQL数据库性能优化思路【面试题】

根据实际情况分析，两个角度思考：不分库分表、分库分表

不分库分表

软优化

  1. 数据库参数调优
  2. 分析慢查询SQL语句，分析执行计划，进行sql改写和程序改写
  3. 优化数据库索引结构
  4. 优化数据表结构优化
  5. 引入NOSQL和程序架构调整

硬优化

提升系统硬件（更快的IO、更多的内存）：带宽、CPU、硬盘

分库分表

  1. 根据业务情况而定，选择合适的分库分表策略（没有通用的策略）
    外卖、物流、电商领域
  2. 先看只分表是否满⾜业务的需求和未来增长
    数据库分表能够解决单表数据量很大时，数据查询的效率问题
    无法给数据库的并发操作带来效率上的提高，分表的实质还是在⼀个数据库上进行的操作，受数据库IO性能的限制
  3. 如果单分表满足不了需求，再分库分表⼀起

结论

在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案
如果数据量极⼤，且业务持续增长快，再考虑分库分表方案

分库分表能解决的问题

解决数据库本身瓶颈

连接数

连接数过多时，就会出现‘too many connections’的错误，访问量太大或者数据库设置的最大连接数太小的原因
Mysql默认的最大连接数为100可以修改，⽽mysql服务允许的最⼤连接数为16384

数据库分表可以解决单表海量数据的查询性能问题
数据库分库可以解决单台数据库的并发访问压力问题

解决系统本身IO、CPU瓶颈

  1. 磁盘读写IO瓶颈，热点数据太多，尽管使用了数据库本身缓存，但是依旧有⼤量IO，导致sql执行速度慢
  2. 网络IO瓶颈，请求的数据太多，数据传输大，网络带宽不够，链路响应时间变长
  3. CPU瓶颈，尤其在基础数据量大单机复杂SQL计算，SQL语句执行占用CPU使用率高，也有扫描行数大、锁冲突、锁等待等原因

可以通过 show processlist、show full processlist，发现 CPU 使用率比较高的SQL

常见的对于查询时间长，State 列值是 Sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，Using filesort 等都是可能有性能问题SQL，清楚相关影响问题的情况可以kill掉

也存在执行时间短，但是CPU占用率⾼的SQL，通过上面命令查询不到，这个时候最好通过执行计划分析explain进行分析

分库分表带来的问题

问题⼀ 跨节点数据库Join关联查询

数据库切分前，多表关联查询，可以通过sql join进行实现分库分表后，数据可能分布在不同的节点上，sql join带来的问题就⽐较麻烦

问题二 分库操作带来的分布式事务问题

操作内容同时分布在不同库中，不可避免会带来跨库事务问题，即分布式事务

问题三 执行的SQL排序、翻页、函数计算问题

分库后，数据分布再不同的节点上， 跨节点多库进行查询时，会出现limit分页、order by排序等问题
而且当排序字段非分片字段时，更加复杂了，要在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序（也会带来更多的CPU/IO资
源损耗）

问题四 数据库全局主键重复问题

常规表的id是使用自增id进行实现，分库分表后，由于表中数据同时存在不同数据库中，如果用自增id，则会出现冲突问题

问题五 容量规划,分库分表后二次扩容问题

业务发展快，初次分库分表后，满足不了数据存储，导致需要多次扩容

问题六 分库分表技术选型问题

市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择

分表分库xff0cmysql性能优化

有关掌握MySQL分库分表（一）数据库性能优化思路、分库分表优缺点的更多相关文章

  1. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  2. ruby-on-rails - 创建 ruby​​ 数据库时惰性符号绑定(bind)失败 - 2

    我正在尝试在Rails上安装ruby​​，到目前为止一切都已安装，但是当我尝试使用rakedb:create创建数据库时，我收到一个奇怪的错误:dyld:lazysymbolbindingfailed:Symbolnotfound:_mysql_get_client_infoReferencedfrom:/Library/Ruby/Gems/1.8/gems/mysql2-0.3.11/lib/mysql2/mysql2.bundleExpectedin:flatnamespacedyld:Symbolnotfound:_mysql_get_client_infoReferencedf

  3. ruby - Rails -- :id attribute? 所需的数据库索引 - 2

    因此，当我遵循MichaelHartl的RubyonRails教程时，我注意到在用户表中，我们为:email属性添加了一个唯一索引，以提高find的效率方法，因此它不会逐行搜索。到目前为止，我们一直在根据情况使用find_by_email和find_by_id进行搜索。然而，我们从未为:id属性设置索引。:id是否自动索引，因为它在默认情况下是唯一的并且本质上是顺序的？或者情况并非如此，我应该为:id搜索添加索引吗？ 最佳答案 大多数数据库(包括sqlite，这是RoR中的默认数据库)会自动索引主键，对于RailsMigration

  4. ruby-on-rails - 如何让 datamapper 与 postgresql 数据库一起工作？ - 2

    我已经找到了几个使用datamapper的示例，并且能够让它们正常工作。不过，所有这些示例都是针对sqlite数据库的。我正在尝试将数据映射器与postgresql一起使用。我将datamapper中的调用从sqlite3更改为postgres，并且我已经安装了dm-postgres-adapter。但它仍然不起作用。我还需要做什么？ 最佳答案 与SQLite不同，PostgreSQL不将数据库存储在单个文件中。在你拥有createdyourdatabase之后，尝试这样的事情:DataMapper.setup:default,{:

  5. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  6. Ruby 的数字方法性能 - 2

    我正在使用Ruby解决一些ProjectEuler问题，特别是这里我要讨论的问题25(Fibonacci数列中包含1000位数字的第一项的索引是多少？)。起初，我使用的是Ruby2.2.3，我将问题编码为:number=3a=1b=2whileb.to_s.length但后来我发现2.4.2版本有一个名为digits的方法，这正是我需要的。我转换为代码:whileb.digits.length当我比较这两种方法时，digits慢得多。时间./025/problem025.rb0.13s用户0.02s系统80%cpu0.190总计./025/problem025.rb2.19s用户0.0

  7. ruby-on-rails - rails 多态关联(遗留数据库) - 2

    我使用的是遗留数据库，所以我无法控制数据模型。他们使用了很多多态链接/连接表，就像这样createtableperson(per_ident,name,...)createtableperson_links(per_ident,obj_name,obj_r_ident)createtablereport(rep_ident,name,...)其中obj_name是表名，obj_r_ident是标识符。因此链接的报告将按如下方式插入:insertintoperson(1,...)insertintoreport(1,...)insertintoreport(2,...)insertint

  8. ruby - Ruby 性能中的计时器 - 2

    我正在寻找一个用ruby​​演示计时器的在线示例，并发现了下面的代码。它按预期工作，但这个简单的程序使用30Mo内存(如Windows任务管理器中所示)和太多CPU有意义吗？非常感谢deftime_blockstart_time=Time.nowThread.new{yield}Time.now-start_timeenddefrepeat_every(seconds)whiletruedotime_spent=time_block{yield}#Tohandle-vesleepinteravalsleep(seconds-time_spent)iftime_spent

  9. ruby-on-rails - 在现有数据库上进行 Rails 迁移 - 2

    我正在创建一个新的Rails3.1应用程序。我希望这个新应用程序重用现有数据库(由以前的Rails2应用程序创建)。我创建了新的应用程序定义模型，它重用了数据库中的一些现有数据。在开发和测试阶段，一切正常，因为它在干净的表数据库上运行，但是当尝试部署到生产环境时，我收到如下消息:PGError:ERROR:column"email"ofrelation"users"alreadyexists***[err::localhost]:ALTERTABLE"users"ADDCOLUMN"email"charactervarying(255)DEFAULT''NOTNULL但是我在迁移中有这

  10. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

随机推荐