我是【会点代码的大叔】，每天为你分享程序员干货，关注并私信我数字“1”，送你一份程序员大礼包。

MySQL 数据库某张表近千万的数据，CRUD比较慢，如何优化？

说实话，这个数据量级， MySQL 单库单表支撑起来完全没有问题的，所以首先还是考虑数据库本身的优化。

MySQL 数据库某张表近千万的数据，CRUD比较慢，如何优化？最常见的是线程池优化、索引优化、缓存优化、读写分离、数据库拆分等，上述4种优化可以从不同角度来优化我们的数据库操作，其中的可操作性性要看团队的技术能力和应用的维护能力，我就以自己遇到过的应用场景简单谈谈自己的优化流程。

换到新的团队，遇到的第一个棘手问题就是数据库不定时的出现“Cannot get a connection, pool error Timeout waiting for idle object”，经和DBA沟通，其反馈数据库group（数据库量级4kw+）中的查询逻辑很多，qps达1w+，并且慢sql积压，拖垮了数据库。从慢sql和上述查询异常着手，进行千万级的数据库优化。

1. 线程池优化。线上的读线程池16，写线程池池16，考虑到数据查询时获取不到数据库连接，将读线程池调整为32，其优化效果不明显，数据库建立连接的异常仍然存在。

2.索引优化。经和DBA分析相关的慢sql语句，发现其索引都是完备的，也就是说每个查询都可以落到对应的索引逻辑，这点儿我们心里是有数的，毕竟线上正常运行了2年多数据库，当时建库和查询时肯定考虑到了索引的情况。也就是说，在这方面没有优化的余地。

1. 选取最适用的字段属性

表中字段的宽度设得尽可能小：char 的上限为 255 字节（固定占用空间），varchar 的上限 65535 字节（实际占用空间），text 的上限为 65535。

尽量把字段设置为 NOT NULL，执行查询的时候，数据库不用去比较 NULL 值。

2. 使用连接（JOIN）来代替子查询 (Sub-Queries)

连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作（联合查询的条件加索引更快）。

一个千万级的数据库的表查询问题属于小问题。做好几个方面就能有很大改观了：

1、硬件上的升级，换成固态硬盘组成Raid5,内存至少加到32G；

2、数据库构架升级为多个节点的负载均衡构架，对于Mysql推荐使用第三方数据库中间软件完成Mycat;

3、精确的数据库优化，分为几个步骤（重点）：

• Mysql配置文件参数的上的优化，具体可百度一下；
• 在需要查询和排序的字段上建好索引，把默认值都改为不为Null（数字值的为0，字符串型的为空字符，日期型的为0000-00-00），对于已经为Null的字段，全部用相应的有效值代替；
• 对于需要进行字符串模糊搜索的，则在数据库上配套使用ElasticSearch之类的全文索引技术，摒弃数据库自身的like或是instr等SQL语句式的字符串模糊搜索；
  
• 对于需要多表查询且某个表的数据为静态数据（解释：指的是长时间内不会发生变更的数据。例如：商品的分类表，几乎不经常变更）的，可以先静态数据存放到缓存中，待应用取出后，再与缓存数据进行拼接完成数据的输出。举例说明：

  需求：查询商品信息时，要根据商品表中的商品分类ID读取到商品分类的名称。

  步骤1：将商品分类表的数据以分ID做为缓存索引，全部写入到MemCache中;

您好，我是一名互联网开发工程师，同时也是vlog领域创作者，欢迎关注我！

mysql某个表千万以上数据，可以通过以下方法去优化:

1、建立合适的索引。数据库慢，一般都是指select查询瓶颈，合适的索引可以提供更快的查询速度，而且可以用explain方法查看有没有用到索引，用到哪些索引，尽量产生临时表，文件排序等sql

2、分析慢查询日志。当运行比较慢的语句时，mysql会把这些语句记录到日志里面，您可以去分析这些语句，看能不能优化sql

3、查询时，前面加一层缓存中间件(redis等)。很多业务都是读大于写，可以把业务数据也放到缓存层，请求来到先查缓存，没有再查数据库，从而减少对数据库的操作。

这个数据量，建索引，会影响插入更新的性能。遇到复杂多变的业务场景，那么多的索引是很恐惧的。

减少数据量，只有分库分表，提高插入速度。

但是分库分表，会给正常的查询带来影响，也就是老的代码要重构了，还要考虑主键的生成。

如果业务简单，建议加索引，读写分离，加缓存解决。

crud慢。。。insert慢你就分区，不影响业务分表分实例也行；update delete看你什么模式了，如果是大数据量带复杂条件的跟select一样分析访问路径，总之先分析，别盲目的上方案，我见过更新数据花四十多分钟，开发一群人讨论分库分表，后来调整了index变成四分钟的，凡事都先分析一下，虽说工程师不是科学家，但是也别太无脑

调整mysql配置参数

若是读为主，可以考虑打开query_cache，以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

优化索引

1. 对出现在where子句中的字段加索引， 全表扫描的性能通常是很差的，要尽量避免。

2. 组合索引和覆盖索引

硬件升级

更多的内存和更快的硬盘。如果是InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈；

分区分表

mysql自带分区表，对应用是透明的，无需更改代码, 但是sql语句是需要针对分区表做优化的，sql的where条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区

crud同时在一张表上并发进行，由于事务的隔离性，当表中数据很大时是肯定不行的。

有人说使用读写分离，但对于写来讲，多个并发事务对同一张大表执行并发的事务性操作也会对性能造成极大影响。

还有人说表分区，横向分区只是减少了事务性操作的数据规模，把对一个数据集的操作分摊到多个数据集而已，对于千万甚至上亿数量级而言效果并不明显。

可以考虑使用事件溯源（Event Souring）+读写分离（CQRS）相结合的方法。事件溯源保证了只执行插入操作，而且插入的并不是数据本身，而是在事件表中插入新增数据的事件event，数据是以这个事件的属性保存的。对数据的修改和删除也同样是以事件形式插入事件表，这种只插入不修改或删除保证了极其强大的时间性能。如果我们想获取某一时刻的数据历史值，则把这个数据从它的起始时刻到特定时刻之间的所有事件回放一遍，即可获得当时的数据值。为了事件表本身的规模不至于太大，我们还可以定期生成它的快照，将在此之前的所有事件应用回放从而获得每个数据的现场值并压缩了事件表。

除了事件溯源以外，我们还应该使用数据库提供的数据复制功能（所有关系型数据库都提供，例如mysql和mariadb的binlog）或使用第三方组件（例如阿里云的canal）将事件实时复制到其他数据库中，从而实现读写分离。这时也可以使用eventuate事件溯源框架，并结合kafka或rabbitmq作它的事件消息总线。在用于读的数据库中我们一般使用物化视图，也就是说，把接收到的事件直接应用在物化视图上，最终用户看到的是物化视图中的统计分析数据。

数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。

• 可以做表拆分，减少单表字段数量，优化表结构。
• 在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

主要两种拆分 垂直拆分，水平拆分。