MySQL 对于千万级的大表要怎么优化? - MySQL - 知乎
很多人第一反应是各种切分；我给的顺序是:第一优化你的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吧！说它成本低，却又是一项费时费力的活，需要在技术与业务都熟悉的情况下，用心去优化才能做到最优，优化后的效果也是立竿见影的！ &ANSWER2提问：如何设计或优化千万级别的大表？此外无其他信息，个人觉得这个话题有点范，就只好简单说下该如何做，对于一个存储设计，必须考虑业务特点，收集的信息如下：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往往是数据库服务器的瓶颈 索引已经创建的非常好，若是读为主，可以考虑打开query_cache， 以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size另外对优化索引结构去解决性能问题的话，建议优先考虑修改类SQL语句，使他们更快些，不得已只靠索引组织结构的方式，当然此话前提是，索引已经创建的非常好，若是读为主，可以考虑打开query_cache， 以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_sizeANSWER3不纸上谈兵，说一下我的思路以及我的解决，抛砖引玉了 我最近正在解决这个问题 我现在的公司有三张表，是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&
最新教程周点击榜
微信扫一扫数据库（12）
& 视图即是虚拟表，也称为派生表，因为它们的内容都派生自其它表的查询结果。虽然视图看起来感觉和基本表一样，但是它们不是基本表。基本表的内容是持久的，而视图的内容是在使用过程中动态产生的。——摘自《SQLite权威指南》
使用视图的优点：
&1.可靠的安全性
&2.查询性能提高
&3.有效应对灵活性的功能需求
&4.轻松应对复杂的查询需求
视图的基本使用：
例如我们本身有一个这样的基本表：
mysql& select *
+------+----------------+-------+
| id & | name & & & & & | age & |
+------+----------------+-------+
| & &1 | bumblebee & & &| & 200 |
| & &1 | king of monkey | 10000 |
+------+----------------+-------+
那么就可以像这样来创建一个视图：
CREATE VIEW stu_view AS SELECT name FROM
Query OK, 0 rows affected (0.01 sec)
创建完一个视图，可以通过查看数据库中的全部数据表来查看：
+-------------------+
| Tables_in_student |
+-------------------+
| stu_view & & & & &|
| students & & & & &|
+-------------------+
2 rows in set (0.00 sec)
可以看到当前数据库中已经把刚刚创建的视图放进数据库的表集合中了。因为视图也是一种表，是虚拟表。
视图的查询和基本表的查询一样，因为视图也是一种数据表，所以你可以像这样的来查询它
mysql& select * from stu_
+----------------+
| name & & & & & |
+----------------+
| bumblebee & & &|
| king of monkey |
+----------------+
DROP VIEW stu_
删除之后可以再次查询进行验证：
mysql& select * from stu_
ERROR ): Table 'student.stu_view' doesn't exist
接下来我们看看如果我们变动了原始的基本表，视图会有什么改变：
mysql& INSERT INTO students(id, name, age) VALUES (2, 'Zeus', 100000);
Query OK, 1 row affected (0.00 sec)
检查基本表：
mysql& SELECT * FROM
+------+----------------+--------+
| id & | name & & & & & | age & &|
+------+----------------+--------+
| & &1 | bumblebee & & &| & &200 |
| & &1 | king of monkey | &10000 |
| & &2 | Zeus & & & & & | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)
检查视图：
mysql& SELECT * FROM stu_
+----------------+
| name & & & & & |
+----------------+
| bumblebee & & &|
| king of monkey |
| Zeus & & & & & |
+----------------+
3 rows in set (0.00 sec)
mysql& CREATE VIEW stu_view2 AS SELECT id, name FROM
Query OK, 0 rows affected (0.01 sec)
mysql& select * from stu_view2;
+------+----------------+
| id & | name & & & & & |
+------+----------------+
| & &1 | bumblebee & & &|
| & &1 | king of monkey |
| & &2 | Zeus & & & & & |
+------+----------------+
3 rows in set (0.00 sec)
更新视图：
mysql& UPDATE stu_view2 SET name='Medusa' &WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 &Changed: 1 &Warnings: 0
检查视图更新结果：
mysql& SELECT * FROM stu_view2;
+------+----------------+
| id & | name & & & & & |
+------+----------------+
| & &1 | bumblebee & & &|
| & &1 | king of monkey |
| & &2 | Medusa & & & & |
+------+----------------+
3 rows in set (0.00 sec)
检查基本表更新结果：
mysql& SELECT * FROM
+------+----------------+--------+
| id & | name & & & & & | age & &|
+------+----------------+--------+
| & &1 | bumblebee & & &| & &200 |
| & &1 | king of monkey | &10000 |
| & &2 | Medusa & & & & | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)
关联多表的视图：
以上都是基于单表的操作，接下来我们从两张表中来做一些实战。
我们额外创建一个info表作为辅助的数据表，如下：
mysql& select *
+----+--------+---------------------------------+
| id | stu_id | info & & & & & & & & & & & & & &|
+----+--------+---------------------------------+
| &1 | & & &1 | A member of the deformed steel. |
| &2 | & & &2 | Hero in Chinese Mythology. & & &|
| &3 | & & &3 | In Greek mythology the Gorgon. &|
+----+--------+---------------------------------+
3 rows in set (0.00 sec)
我们创建一个连接了两张基本表的视图stu_view3
mysql& CREATE VIEW stu_view3 AS SELECT s.id, s.name, s.age, i.info FROM students s, info i WHERE i.stu_id=s.
Query OK, 0 rows affected (0.00 sec)
验证过程：
mysql& select * from stu_view3;
+------+----------------+--------+---------------------------------+
| id & | name & & & & & | age & &| info & & & & & & & & & & & & & &|
+------+----------------+--------+---------------------------------+
| & &1 | bumblebee & & &| & &200 | A member of the deformed steel. |
| & &2 | king of monkey | &10000 | Hero in Chinese Mythology. & & &|
| & &3 | Medusa & & & & | 100000 | In Greek mythology the Gorgon. &|
+------+----------------+--------+---------------------------------+
3 rows in set (0.00 sec)
对连接了两张基本表的视图stu_view3进行更新操作：
mysql& UPDATE stu_view3 SET age=800 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 &Changed: 1 &Warnings: 0
验证视图stu_view3：
mysql& select * from stu_view3;
+------+----------------+--------+---------------------------------+
| id & | name & & & & & | age & &| info & & & & & & & & & & & & & &|
+------+----------------+--------+---------------------------------+
| & &1 | bumblebee & & &| & &800 | A member of the deformed steel. |
| & &2 | king of monkey | &10000 | Hero in Chinese Mythology. & & &|
| & &3 | Medusa & & & & | 100000 | In Greek mythology the Gorgon. &|
+------+----------------+--------+---------------------------------+
3 rows in set (0.00 sec)
验证基本表：
mysql& select *
+------+----------------+--------+
| id & | name & & & & & | age & &|
+------+----------------+--------+
| & &1 | bumblebee & & &| & &800 |
| & &2 | king of monkey | &10000 |
| & &3 | Medusa & & & & | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)
1.在使用视图的时候，就是与使用表的语法一样的
2.创建视图的时候，该视图的名字如果与已经存在表重名的话，那么会报错，不允许创建。视图就是一种特殊的表
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：318011次
积分：5416
积分：5416
排名：第3667名
原创：202篇
评论：89条
阅读：10744
文章：14篇
阅读：59318
阅读：7073
文章：12篇
阅读：18870
文章：16篇
阅读：34392删除理由：
&广告/SPAM
还可以输入&120&字
还可以输入&120&字
他一定是哪里做的不够好，别替他瞒着了，告诉我们吧~
发表于&&0条回复&&14次阅读&&&&筑龙币+50&&&&&&
作为Java程序员，MySQL数据库大家平时应该都没少使用吧，对MySQL数据库的引擎应该也有所了解，这篇文章就让我详细的说说MySQL数据库的Innodb和MyIASM两种引擎以及其索引结构。也来巩固一下自己对这块知识的掌握。Innodb引擎Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这篇文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。MyIASM引擎MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。两种引擎的选择大尺寸的数据集趋向于选择InnoDB引擎，因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。主键查询在InnoDB引擎下也会相当快，不过需要注意的是如果主键太长也会导致性能问题，关于这个问题我会在下文中讲到。大批的INSERT语句(在每个INSERT语句中写入多行，批量插入)在MyISAM下会快一些，但是UPDATE语句在InnoDB下则会更快一些，尤其是在并发量大的时候。Index——索引索引（Index）是帮助MySQL高效获取数据的数据结构。MyIASM和Innodb都使用了树这种数据结构做为索引，关于树我也曾经写过一篇文章树是一种伟大的数据结构，只是自己的理解，有兴趣的朋友可以去阅读。下面我接着讲这两种引擎使用的索引结构，讲到这里，首先应该谈一下B-Tree和B+Tree。B-Tree和B+TreeB+Tree是B-Tree的变种，那么我就先讲B-Tree吧，相信大家都知道红黑树，这是我前段时间学《算法》一书时，实现的一颗红黑树，大家可以参考。其实红黑树类似2,3-查找树，这种树既有2叉结点又有3叉结点。B-Tree也与之类似，它的每个结点做多可以有d个分支（叉），d称为B-Tree的度，如下图所示，它的每个结点可以有4个元素，5个分支，于是它的度为5。B-Tree中的元素是有序的，比如图中元素7左边的指针指向的结点中的元素都小于7，而元素7和16之间的指针指向的结点中的元素都处于7和16之间，正是满足这样的关系，才能高效的查找：首先从根节点进行二分查找，找到就返回对应的值，否则就进入相应的区间结点递归的查找，直到找到对应的元素或找到null指针，找到null指针则表示查找失败。这个查找是十分高效的，其时间复杂度为O(logN)（以d为底，当d很大时，树的高度就很低），因为每次检索最多只需要检索树高h个结点。接下来就该讲B+Tree了，它是B-Tree的变种，如下面两张图所示：从图中就可以看出，B+Tree的内部结点不存储数据，只存储指针，而叶子结点则只存储数据，不存储指针。并且在其每个叶子节点上增加了一个指向相邻叶子节点的指针，这个优化提高区间访问的性能，比如在第二张图中要查询键为从18到49的所有数据，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。MyISAM引擎的索引结构MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。Innodb引擎的索引结构MyISAM引擎的索引结构同样也是B+Tree，但是Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。并且和MyISAM不同，InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。
Xixmy为【[分享]展柜】增加了20热度值
分享到微信朋友圈
打开微信"扫一扫"，扫描上方二维码请点击右上角按钮&，选择&
登录后才能评论,评论超过10个字，有机会获得筑龙币奖励！
俗人谈俗事儿，吃喝玩乐全都拿来
:&400-900-8066