来源：蜘蛛抓取(WebSpider)
时间：2016-11-27 07:41
标签：
mysql 千万级查询优化
提问：如何设计或优化千万级别的大表？此外无其他信息，个人觉得这个话题有点范，就只好简单说下该如何做，对于一个存储设计，必须考虑业务特点，收集的信息如下：1.数据的容量：1-3年内会大概多少条数据，每条数据大概多少字节；
2.数据项：是否有大字段，那些字段的值是否经常被更新；
3.数据查询SQL条件：哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等；
4.数据更新类SQL条件：有多少列经常出现UPDATE或DELETE 的WHERE子句中；
5.SQL量的统计比，如：SELECT：UPDATE+DELETE：INSERT=多少？
6.预计大表及相关联的SQL，每天总的执行量在何数量级？
7.表中的数据：更新为主的业务 还是 查询为主的业务
8.打算采用什么数据库物理服务器，以及数据库服务器架构？
9.并发如何？
10.存储引擎选择InnoDB还是MyISAM？
大致明白以上10个问题，至于如何设计此类的大表，应该什么都清楚了！
至于优化若是指创建好的表，不能变动表结构的话，那建议InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈。另外对优化索引结构去解决性能问题的话，建议优先考虑修改类SQL语句，使他们更快些，不得已只靠索引组织结构的方式，当然此话前提是，
索引已经创建的非常好，若是读为主，可以考虑打开query_cache，
以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_siz。
更多信息参见：
不纸上谈兵，说一下我的思路以及我的解决，抛砖引玉了
我最近正在解决这个问题
我现在的公司有三张表，是5亿的数据，每天张表每天的增量是100w
每张表大概在10个columns左右
下面是我做的测试和对比
1.首先看engine,在大数据量情况下，在没有做分区的情况下
mysiam比innodb在只读的情况下，效率要高13％左右
2.在做了partition之后，你可以去读一下mysql的官方文档，其实对于partition，专门是对myisam做的优化，对于innodb，所有的数据是存在ibdata里面的，所以即使你可以看到schema变了，其实没有本质的变化
在分区出于同一个physical disk下面的情况下，提升大概只有1％
在分区在不同的physical
disk下，我分到了三个不同的disks下，提升大概在3％，其实所谓的吞吐量，由很多因素决定的，比如你的explain
parition时候可以看到，record在那一个分区，如果每个分区都有，其实本质上没有解决读的问题，这样只会提升写的效率。
另外一个问题在于，分区，你怎么分，如果一张表，有三个column都是经常被用于做查询条件的，其实是一件很悲惨的事情，因为你没有办法对所有的sql做针对性的分区，如果你只是如mysql官方文档上说的，只对时间做一个分区，而且你也只用时间查询的话，恭喜你
3.表主要用来读还是写，其实这个问题是不充分的，应该这样问，你在写入的时候，同时并发的查询多么？我的问题还比较简单，因为mongodb的
shredding支持不能，在crush之后，还是回到mysql，所以在通常情况下，9am－9pm，写入的情况很多，这个时候我会做一个
view，view是基于最近被插入或者经常被查询的，通过做view来分离读取，就是说写是在table上的，读在进行逻辑判断前是在view上操作的
4做一些archive table，比如先对这些大表做很多已有的统计分析，然后通过已有的分析＋增量来解决
5如果你用mysiam，还有一个问题你要注意，如果你的.configure的时候，加了一个max index length参数的时候，当你的record数大于制定长度的时候，这个index会被disable
阅读(...) 评论()如何优化Mysql千万级快速分页
投稿：hebedich
字体：[ ] 类型：转载 时间：
本文深入浅出的分析了如何给千万级mysql快速分页做优化，非常的实用，是篇不可多得的文章，程序员必读！！
数 据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text, id 是逐渐，vtype是tinyint，vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据，填充10万篇新闻。
最后collect 为 10万条记录，数据库表占用硬盘1.6G。
OK ,看下面这条sql语句：
select id,title from collect limit 1000,10; 很快；基本上0.01秒就OK，再看下面的
select id,title from collect limit 90000,10; 从9万条开始分页，结果？
8-9秒完成，my god 哪出问题了？？？？
其实要优化这条数据，网上找得到答案。看下面一条语句:
select id from collect order by id limit 90000,10; 很快，0.04秒就OK。 为什么？因为用了id主键做索引当然快。网上的改法是：
select id,title from collect where id&=(select id from collect order by id limit 90000,1) limit 10;
这就是用了id做索引的结果。可是问题复杂那么一点点，就完了。看下面的语句
select id from collect where vtype=1 order by id limit 90000,10; 很慢，用了8-9秒！
到 了这里我相信很多人会和我一样，有崩溃感觉！vtype 做了索引了啊？怎么会慢呢？vtype做了索引是不错，你直接 select id from collect where vtype=1 limit 1000,10; 是很快的，基本上0.05秒，可是提高90倍，从9万开始，那就是0.05*90=4.5秒的速度了。和测试结果8-9秒到了一个数量级。从这里开始有人 提出了分表的思路，这个和dis #cuz 论坛是一样的思路。思路如下：
建一个索引表： t (id,title,vtype) 并设置成定长，然后做分页，分页出结果再到 collect 里面去找info 。 是否可行呢？实验下就知道了。
10万条记录到 t(id,title,vtype) 里，数据表大小20M左右。用select id from t where vtype=1 order by id limit 90000,10; 很快了。基本上0.1-0.2秒可以跑完。为什么会这样呢？我猜想是因为collect 数据太多，所以分页要跑很长的路。limit 完全和数据表的大小有关的。其实这样做还是全表扫描，只是因为数据量小，只有10万才快。OK， 来个疯狂的实验，加到100万条，测试性能。
加了10倍的数据，马上t表就到了200多M，而且是定长。还是刚才的查询语句，时间是0.1-0.2秒完成！分表性能没问题？错！因为我们的limit还是9万，所以快。给个大的，90万开始 select id from t where vtype=1 order by id limit ; 看看结果，时间是1-2秒！
why ?? 分表了时间还是这么长，非常之郁闷！有人说定长会提高limit的性能，开始我也以为，因为一条记录的长度是固定的，mysql 应该可以算出90万的位置才对啊？ 可是我们高估了mysql 的智能，他不是商务数据库，事实证明定长和非定长对limit影响不大？ 怪不得有人说 discuz到了100万条记录就会很慢，我相信这是真的，这个和数据库设计有关！
难道MySQL 无法突破100万的限制吗？？？到了100万的分页就真的到了极限？？？
答案是： NO !!!! 为什么突破不了100万是因为不会设计mysql造成的。下面介绍非分表法，来个疯狂的测试！一张表搞定100万记录，并且10G 数据库，如何快速分页！
好了，我们的测试又回到 collect表，开始测试结论是： 30万数据，用分表法可行，超过30万他的速度会慢道你无法忍受！当然如果用分表+我这种方法，那是绝对完美的。但是用了我这种方法后，不用分表也可以完美解决！
答 案就是：复合索引！ 有一次设计mysql索引的时候，无意中发现索引名字可以任取，可以选择几个字段进来，这有什么用呢？开始的select id from collect order by id limit 90000,10; 这么快就是因为走了索引，可是如果加了where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。然后测试
select id from collect where vtype=1 limit 90000,10; 非常快！0.04秒完成！
再测试: select id ,title from collect where vtype=1 limit 90000,10; 非常遗憾，8-9秒，没走search索引！
再测试：search(id,vtype)，还是select id 这个语句，也非常遗憾，0.5秒。
综上：如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！
完美解决了分页问题了。可以快速返回id就有希望优化limit ， 按这样的逻辑，百万级的limit 应该在0.0x秒就可以分完。看来mysql 语句的优化和索引时非常重要的！
好了，回到原题，如何将上面的研究成功快速应用于开发呢？如果用复合查询，我的轻量级框架就没的用了。分页字符串还得自己写，那多麻烦？这里再看一个例子，思路就出来了：
select * from collect where id in (,7000); 竟然 0秒就可以查完！
mygod ，mysql 的索引竟然对于in语句同样有效！看来网上说in无法用索引是错误的！
小小的索引+一点点的改动就使mysql 可以支持百万甚至千万级的高效分页！
通过这里的例子，我反思了一点：对于大型系统，千万不能用框架，尤其是那种连sql语句都看不到的框架！因为开始对于我的轻量级框架都差点崩溃！只适 合小型应用的快速开发，对于ERP,OA，大型网站，数据层包括逻辑层的东西都不能用框架。如果程序员失去了对sql语句的把控，那项目的风险将会成几何 级数增加！尤其是用mysql 的时候，mysql 一定需要专业的dba 才可以发挥他的最佳性能。一个索引所造成的性能差别可能是上千倍！
PS: 经过实际测试，到了100万的数据，160万数据，15G表，190M索引，就算走索引，limit都得0.49秒。所以分页最好别让别人
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具1862人阅读
MYSQL（40）
一 &大的优化方向：&数据结构优化，慢查询优化，索引优化，mysql参数设置优化
数据结构优化：先读写分离、再垂直拆分、再水平拆分！
1. 设计合适的索引,基于主键的查找,上亿数据也是很快的;
2. 反范式化设计,以空间换时间,避免join,有些join操作可以在用代码实现,没必要用数据库来实现;
3. buffer,尽量让内存大于数据.
至于优化若是指创建好的表，不能变动表结构的话，那建议InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈&
我的问题：两个数据库，database A 和 B（be） &;每个都有两个表event和id
id表增长到不会超过1亿，然后会有频繁的更新.更新需要查找id值。这个亦可以分表。按照gid的不同，在程序中处理插入不同哦功能的表
每天生成一个新表，然后整体再插入总表？？
event表一直累加，会超过1亿，需要自动切表
问题：1 &数据库是否是瓶颈？ &再开几个进程看数据库插入更新会不会有加速现象.多开程序的作用已经不大，很微小。所以瓶颈在数据库上了
& & & & & & &2 瓶颈在哪里? &insert时间花费0--100毫秒 &，update 100--1000ms
具体做法：
原来查看速度： &
& & & & &&
& & & & & & Com_insert & & & & & & & & & &100 & & & & & & & & & &&
& & & & & &Com_update & & & & & & & & & &30
以下测试再关闭程序debug日志的情况下进行
使用gprof分析程序得到 parsesql占23.5%，parseupdatesql15.5%，jsontomap占23.5，推测数据库执行占40%？
<span style="font-family:Helvetica Neue,Helvetica,Arial,sans- color:# & & 程序是瓶颈，开了6个进程
原来速度： &&
。10：00 & & & &开了6个进程来跑。速度
& & & & & & & & & & & & & & & & & & & & & & & & & & &第一次测试 & &第二次测试&
& & & & & &Com_insert & & & & & & &&& & &489 & & & & & & & & & & 342
& & & & &&Com_update& & & & & & & & & &204 & & & & & & & & & & 182
& & & & & & & &&
措施:加内寸，拆表
得到结论： 1 &只写入event表，看会不会堵塞。不需要做这个实验，DBA自动切表就行，这样就很小。
<span style="font-family:Helvetica Neue,Helvetica,Arial,sans- color:# & & &数据库表很大的时候，主要考虑IO速度慢的问题，因为表太大，不能全部放进内存，所以需要硬盘IO，引起速度慢。
& & & & & & & & & & 1 查看表大小，event表占35G，gid表占3G。
& & & & & &数据库设定的内存大小是10G,event是在太大了，导致频繁的磁盘IO。
&TABLE_NAME &| DATA_LENGTH | DATA_LENGTH+INDEX_LENGTH | TABLE_ROWS |
+-------------+-------------+--------------------------+------------+
| Mapping_gid | & | & & & & & & &
| & &5162861 |
+-------------+-------------+--------------------------+-----------
+------------------------+-------------+--------------------------+------------+
| TABLE_NAME & & & & & & | DATA_LENGTH | DATA_LENGTH+INDEX_LENGTH | TABLE_ROWS |
+------------------------+-------------+--------------------------+------------+
| Mapping_event_ |
| & & & & & & & | &
+------------------------+-------------+--------------------------+------------+
+-------------+-------------+--------------------------+------------+
| TABLE_NAME &| DATA_LENGTH | DATA_LENGTH+INDEX_LENGTH | TABLE_ROWS |
+-------------+-------------+--------------------------+------------+
| Mapping_gid | & | & & & & & & &
| & &4216890 |
+-------------+-------------+--------------------------+------------+
------------------------+-------------+--------------------------+------------+
| TABLE_NAME & & & & & & | DATA_LENGTH | DATA_LENGTH+INDEX_LENGTH | TABLE_ROWS |
+------------------------+-------------+--------------------------+------------+
| Mapping_event_ | & | & & & & & & &
对策：把event按每天备份 ； 这样每天的event表就很小。
&& & & & & & & & & & & & & & & & & & & & &第一次测试 &&
& & & & & &Com_insert & & & & & & &&& &&&1158&
& & & & & & & &
& & & & &&Com_update & & & & & & & & & &552 & & & & & & &&
<span style="font-family:Helvetica Neue,Helvetica,Arial,sans- color:# &修改程序，原来程序insert不成功然后再update，改成insert into &duplicate
可以解决的问题：1 多个进程写数据，id会跳跃性自增 & &2 &两条变一条
& & & & & & & & & & & & &&
各位，请教一个mysql问题。
说明：数据库id是主键，gid是唯一索引。insert into duplicate使用gid作为条件。
问题如下：
昨天我使用insert into duplicate 试验了很久、
我开了两个进程。都执行insert into duplicate语句。
进程A事实上只执行update，进程B 事实上只执行insert
单独开A时，id不变，单独开B，id是顺序增长同时开A，B，id跳跃性增长的。
可有什么解决方案推荐，使得id是顺序+1增长的？
解释：&只能说&insert
into duplicate 也使得id 自增了.但是如果后来执行的是update操作，撤销了.
可设置 innodb_autoinc_lock_mode = 0 使的获取自增id的锁方式为表锁，
但是此参数是全局的（即影响所有表，且需重启数据库生效），对于高并发写入的数据，会影响插入性能，不建议
如果使用insert into duplicate 只能解决两条变一条，不能解决id问题。
我具体做法：（1）开了多份程序，提高了数据库写入和更新的速度
& & & & & & & & & & & & & （2） 加大内存&
未填加内存前速度 -loginfo：
& & & & & & & & & & &Com_insert & & & & & &Com_update
& & & & & & & & & & & & & 400 & & & & & & & & & & & & & & &160
& & & & & & & & & & & & & & &（3）修改字段
& & & & & & & & & & & & & & &（4）拆表
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：940898次
积分：10519
积分：10519
排名：第1144名
原创：150篇
转载：309篇
评论：51条
(4)(2)(9)(1)(5)(5)(7)(8)(10)(10)(14)(11)(9)(8)(10)(18)(20)(23)(14)(18)(12)(28)(18)(1)(2)(4)(11)(2)(18)(18)(30)(39)(18)(6)(5)(2)(7)(5)(8)(6)(1)(12)本文参考知乎问答整理：
很喜欢这种理想的论调：
MySQL只做简单的事情，千万级的表，无论怎样优化，同样的SQL，都没有在十万级的表中执行效率快；
因此，在设计千万级的大表之前，要先问自己几个问题
数据是否存在明显的冷热（考虑旧数据归档）
是否可以按照时间、区域等条件拆分表
如果字段过多，是否可以考虑按照字段的关联性进行拆分
我们当然希望每个应用都可以这样，天下太平... 但理想终归是理想，现实中，轮到我们自己撸袖子上阵的时候，坑，大多已经是一眼忘不到底儿了!!
当然了，我们完全可以把理想的情况做为终极目标，把现实生拉硬拽的往上面靠嘛；根据知乎网友的回答，总结下对于我们广大奋战在研发一线的同胞而言，MySQL的优化的思路，这里按照成本由低到高的顺序排列（至少表面看起来是这样）
优化你的SQL和索引；
加缓存，memcached、redis；
做主从复制或主主复制，读写分离；可以在应用层做，效率高，也可以使用第三方工具，第三方工具推荐Qihoo 360的Atlas，其它的要么效率不高，要么没人维护；
考虑使用MySQL的分区表，分区表对应用是透明的，无需修改代码，但是SQL语句是需要针对分区表做优化的，需要在SQL条件中带上分区条件的列，以使查询定位到少量的分区中，否则就会扫描全部分区；另外分区表还有一些坑，这里就不多说了；
考虑对表做垂直拆分，意思就是根据你应用模块的耦合度，将大的模块拆分为小的模块进行处理，即传说中的&分布式应用&；ps：对某些业务而言，有些数据，用一次之后，再用到的可能性几乎为零，这时候，完全可以将这些数据规整到一张表中做为历史数据存放；
以上手段，都达到了无法再优化的时候，再考虑对表做水平拆分；针对大数据量的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key，为了更好的查询效率，可能需要修改表结构，对某些数据做冗余存放，此外应用也可能要修改，SQL中尽量带sharding key，将数据定位到限定的表中去查询，而不是扫描全部的表。
阅读(...) 评论()数据库技术（44）
作者：哈哈
链接：/question//answer/
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
很多人第一反应是各种切分；我给的顺序是:
第一优化你的sql和索引；
第二加缓存，memcached,redis；
第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护；
第四如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，另外分区表还有一些坑，在这里就不多说了；
第五如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
第六才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；
mysql数据库一般都是按照这个步骤去演化的，成本也是由低到高；
有人也许要说第一步优化sql和索引这还用说吗？的确，大家都知道，但是很多情况下，这一步做的并不到位，甚至有的只做了根据sql去建索引，根本没对sql优化（中枪了没？），除了最简单的增删改查外，想实现一个查询，可以写出很多种查询语句，不同的语句，根据你选择的引擎、表中数据的分布情况、索引情况、数据库优化策略、查询中的锁策略等因素，最终查询的效率相差很大；优化要从整体去考虑，有时你优化一条语句后，其它查询反而效率被降低了，所以要取一个平衡点；即使精通mysql的话，除了纯技术面优化，还要根据业务面去优化sql语句，这样才能达到最优效果；你敢说你的sql和索引已经是最优了吗?
再说一下不同引擎的优化，myisam读的效果好，写的效率差，这和它数据存储格式，索引的指针和锁的策略有关的，它的数据是顺序存储的（innodb数据存储方式是聚簇索引），他的索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快，（innodb索引节点存的则是数据的主键，所以需要根据主键二次查找）；myisam锁是表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的，去设置concurrent_insert参数，定期执行表优化操作，更新操作就没有办法了）是串行的，所以写起来慢，并且默认的写优先级比读优先级高，高到写操作来了后，可以马上插入到读操作前面去，如果批量写，会导致读请求饿死，所以要设置读写优先级或设置多少写操作后执行读操作的策略;myisam不要使用查询时间太长的sql，如果策略使用不当，也会导致写饿死，所以尽量去拆分查询效率低的sql,
innodb一般都是行锁，这个一般指的是sql用到索引的时候，行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引，仍然会锁定表,mysql的读写之间是可以并发的，普通的select是不需要锁的，当查询的记录遇到锁时，用的是一致性的非锁定快照读，也就是根据数据库隔离级别策略，会去读被锁定行的快照，其它更新或加锁读语句用的是当前读，读取原始行；因为普通读与写不冲突，所以innodb不会出现读写饿死的情况，又因为在使用索引的时候用的是行锁，锁的粒度小，竞争相同锁的情况就少，就增加了并发处理，所以并发读写的效率还是很优秀的，问题在于索引查询后的根据主键的二次查找导致效率低；
ps:很奇怪，为什innodb的索引叶子节点存的是主键而不是像mysism一样存数据的物理地址指针吗？如果存的是物理地址指针不就不需要二次查找了吗，这也是我开始的疑惑，根据mysism和innodb数据存储方式的差异去想，你就会明白了，我就不费口舌了！
所以innodb为了避免二次查找可以使用索引覆盖技术，无法使用索引覆盖的，再延伸一下就是基于索引覆盖实现延迟关联；不知道什么是索引覆盖的，建议你无论如何都要弄清楚它是怎么回事！
尽你所能去优化你的sql吧！说它成本低，却又是一项费时费力的活，需要在技术与业务都熟悉的情况下，用心去优化才能做到最优，优化后的效果也是立竿见影的！
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：404650次
积分：4757
积分：4757
排名：第4551名
原创：54篇
转载：230篇
评论：42条
(5)(4)(4)(7)(6)(13)(24)(7)(3)(1)(3)(7)(1)(1)(1)(2)(1)(1)(8)(8)(9)(3)(6)(4)(27)(19)(12)(1)(1)(2)(5)(21)(20)(28)(2)(3)(1)(10)(1)(6)
