如何优化Mysql千万级快速分页,limit优化快?
select * from collect where id in (9000,12,50,7000); 竟然 0秒便可以查完!id in (str) 很快,根基还是0秒。若是这样,千万级的数据,mysql应该也很轻易应付。
mysql数据表规模九千万左右,怎么优化查询?
我的天啦,一个表九千万也是了不得了!
我上家公司明确规定,一张表不能超过5000万,因为查询效率会有更大的降低!
无论如何,看下如何优化数据查询吧!
①,单库单表:
1,加索引,一个好的索引能用空间换取查询时间的大为降低!
2,使用存储过程:减少sql编译的时间!
3,优化sql:包括联合查询的指向,where,order语句使用索引字段,减少使用多表联合查询,不要使用select *等等!
4,参数配置:扩大内存,调节线程池参数等等!
5,开启缓存:开启二级缓存,三级缓存,提升查询效率!
②,单库多表:
使用水平拆分(比如按月份),将表分为12张表,然后在代码端按照月份访问相应月份的表!
使用垂直拆分:很多字段只是作为保存记录用,(像一些约定,备注啥的字段往往很大),可以将查询中常常用到的字段放在常用的一张表中做查询,另一些字段放另一张表中存储,通过某个唯一索引字段联系起来,可以保证查询效率大为提升(因为磁盘IO减少)!
③,多库多表:
①,主从读写分离:表中数据虽然还是一致,但是由于多个从库读,主库写数据,大大减少共享锁的性能开销!
②,分库分表:指定一个字段作为,分库字段,利用hash值或者其它策略,分布在不同的库里面,在按照相应分布策略(比如上面的水平拆分或者垂直拆分),分散到不同的表里!
比如我们现在的数据库设计为8库1024表,你的将近一亿的数据在我们的单张表里面只有不到10W!
虽然理论上,一张表的大小不做任何限制,但是基于查询效率,索引性能等,不宜超出5000万数据!
关于多线程,分布式,微服务,数据库,缓存的更多干货,会继续分享,敬请关注。。
实践出真知。根据成本顺序依次是:
第一:加索引优化sql。尽量避免全盘扫描,另单表索引也不是越多越好。
第二:加缓存。使用redis,memcached,但注意缓存同步更新、设置失效等问题。
第三:主从复制,读写分离。适合读多写少的场景,同步会有延迟。
第四:垂直拆分。可以选用适当的中间件Mycat等
第五:水平切分。选择合理的sharding key,改动表结构,将大数据字段拆分出去,对经常查询的字段做一定的冗余,同时做好数据同步。
当然还有优化数据库连接配置,根据业务选用不同的数据库引擎等等。
我是一名架构师,欢迎关注,给技术加点料
我不清楚答题的大部分人是否有真正实践过,特别是用mysql实践过。大部分说是加索引、调整参数不是说不正确,有效果,但是不能很好的解决问题。说说个人想法:
部分答主的方案的确不敢苟同,纠正如下:
1、select count(*) 和 select count(主键) 在现阶段的mysql 没有太大区别,新版mysql这个对性能影响可以忽略。
2、强烈反对使用存储过程,后面介绍了使用分表分库的方案,就更不要用存储过程了。
3、单表行数和表数量,需要找到平衡点。表太多,性能也会下降。
我的回答:
1、单表9000w数据,mysql存储不了,想办法分表分库。500w数据的时候,你就该有这个想法了。只加索引解决不了问题,9000w的单表数据,很难平衡查找和插入性能,索引稍微多了插入性能也很低。
2、不要再说select count了,放弃汇总查询的想法,根本查不了。
3、数据最终以mysql作为主要存储,考虑最终查询的数据源放在非关系的数据存储上,mongo,es都可以考虑下。
4、业务场景都是需要实时查询9000w数据吗?非实时数据,可以考虑hadoop系大数据方案。
5、最后说下,mysql 和oracle,sql server不一样,不一样。
读写分离,分库分表,热数据放内存。
读写分离:减少写库所带来的行锁甚至表锁对查询的影响,提升查询效率,同时还可以保证高可用。
在设计系统之初就设计好垂直分库和垂直分表,比如垂直分表:在一张大表中,一些热数据的字段放在一起,一些不常用的而且占用空间比较大的字段放在另外一张表,这样子做的好处是提升了查询速度,因为mysql是以页存储数据的,一页之中存放的数据越多,查询效率会更高。
另外再配合redis mongodb这些缓存数据库,热数据放进去,查询效率会进一步得到提升。
如果上面的方案还无法解决查询缓慢的问题,可能是因为我们的数据量非常大,而且持续快速增长。我们还可以进行水平分库分表,例如把一张1亿数据量的大表,水平拆分成10张相同的大表,再水平拆分到10个不同的数据库中。。。
觉得可以的点个赞
是一张表九千万了吗?
建议:
第一、表读居多还是写?读的话数据库引擎用myisam ,写的话InnoDB 而不是MyISAM,因为MyISAM有太多锁。
第二、升级到MySQL 5.5 ,确保使用buffering功能。
第三,索引确保使用正确,且都在内存中,移除没有必要的索引。
第四、写场景多吗? 设置innodb_buffer_pool_size足够大来确保更快的写操作。
第五、按业务id取模,分表。
最后,花钱加机器内存和用ssd磁盘吧。
数据库访问量很大时,如何做优化?
你好!如果有大量的访问用到调取到数据库时,往往查询速度会变得很慢,所以我们需要进行优化处理。
优化从三个方面考虑:
SQL语句优化、
主从复制,读写分离,负载均衡、
数据库分库分表。
一、SQL查询语句优化
1、使用索引
建立索引可以使查询速度得到提升,我们首先应该考虑在where及order by,group by涉及的列上建立索引。
2、借助explain(查询优化神器)选择更好的索引和优化查询语句
SQL 的 Explain 通过图形化或基于文本的方式详细说明了 SQL 语句的每个部分是如何执行以及何时执行的,以及执行效果。通过对选择更好的索引列,或者对耗时久的SQL语句进行优化达到对查询速度的优化。
3、任何地方都不要使用SELECT * FROM语句。
4、不要在索引列做运算或者使用函数
5、查询尽可能使用limit来减少返回的行数
6、使用查询缓存,并将尽量多的内存分配给MYSQL做缓存
二、主从复制,读写分离,负载均衡
目前大多数的主流关系型数据库都提供了主从复制的功能,通过配置两台(或多台)数据库的主从关系,可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库这一功能,实现数据库的读写分离,从而改善数据库的负载压力。一个系统的读操作远远多于写操作,因此写操作发向master,读操作发向slaves进行操作(简单的轮询算法来决定使用哪个slave)。
利用数据库的读写分离,Web服务器在写数据的时候,访问主数据库(master),主数据库通过主从复制将数据更新同步到从数据库(slave),这样当Web服务器读数据的时候,就可以通过从数据库获得数据。这一方案使得在大量读操作的Web应用可以轻松地读取数据,而主数据库也只会承受少量的写入操作,还可以实现数据热备份,可谓是一举两得。
三、数据库分表、分区、分库
1、分表
通过分表可以提高表的访问效率。有两种拆分方法:
垂直拆分
在主键和一些列放在一个表中,然后把主键和另外的列放在另一个表中。如果一个表中某些列常用,而另外一些不常用,则可以采用垂直拆分。
水平拆分
根据一列或者多列数据的值把数据行放到两个独立的表中。
2、分区
分区就是把一张表的数据分成多个区块,这些区块可以在一个磁盘上,也可以在不同的磁盘上,分区后,表面上还是一张表,但是数据散列在多个位置,这样一来,多块硬盘同时处理不同的请求,从而提高磁盘I/O读写性能。实现比较简单,包括水平分区和垂直分区。
3、分库
分库是根据业务不同把相关的表切分到不同的数据库中,比如web、bbs、blog等库。
分库解决的是数据库端 并发量的问题。分库和分表并不一定两个都要上,比如数据量很大,但是访问的用户很少,我们就可以只使用分表不使用分库。如果数据量只有1万,而访问用户有一千,那就只使用分库。
注意:分库分表最难解决的问题是统计,还有跨表的连接(比如这个表的订单在另外一张表),解决这个的方法就是使用中间件,比如大名鼎鼎的MyCat,用它来做路由,管理整个分库分表,乃至跨库跨表的连接

