详解MySQL数据库千万级数据查询和存储
⽬录
百万级数据处理⽅案
数据存储结构设计
查询语句优化
千万级数据处理⽅案
数据存储结构设计
数据库表主键设计
MySQL⾯试题
MySQL数据库千万级数据查询优化⽅案
你⽤过MySQL那些存储引擎
他们都有什么特点和区别？
那他们都有什么特点和区别呢？
MyISAM和InnoDB的区别
MySQL复杂查询语句的优化
百万级数据处理⽅案
数据存储结构设计
表字段设计
表字段 not null，因为 null 值很难查询优化且占⽤额外的索引空间，推荐默认数字 0。
数据状态类型的字段，⽐如 status, type 等等，尽量不要定义负数，如 -1。因为这样可以加上 UNSIGNED，数值容量就会扩⼤⼀倍。
可以的话⽤ TINYINT、SMALLINT 等代替 INT，尽量不使⽤ BIGINT，因为占的空间更⼩。
字符串类型的字段会⽐数字类型占的空间更⼤，所以尽量⽤整型代替字符串，很多场景是可以通过编码逻辑来实现⽤整型代替的。
line和的区别字符串类型长度不要随意设置，保证满⾜业务的前提下尽量⼩。
⽤整型来存 IP。
单表不要有太多字段，建议在20以内。
为能预见的字段提前预留，因为数据量越⼤，修改数据结构越耗时。
索引设计
索引，空间换时间的优化策略，基本上根据业务需求设计好索引，⾜以应付百万级的数据量，养成使⽤ explain 的习惯，关于 explain 也可以访问：explain 让你的 sql 写的更踏实了解更多。
⼀个常识：索引并不是越多越好，索引是会降低数据写⼊性能的。
索引字段长度尽量短，这样能够节省⼤量索引空间；
取消外键，可交由程序来约束，性能更好。
复合索引的匹配最左列规则，索引的顺序和查询条件保持⼀致，尽量去除没必要的单列索引。
值分布较少的字段（不重复的较少）不适合建索引，⽐如像性别这种只有两三个值的情况字段建⽴索引意义不⼤。
需要排序的字段建议加上索引，因为索引是会排序的，能提⾼查询性能。
字符串字段使⽤前缀索引，不使⽤全字段索引，可⼤幅减⼩索引空间。
查询语句优化
尽量使⽤短查询替代复杂的内联查询。
查询不使⽤ select *，尽量查询带索引的字段，避免回表。
尽量使⽤ limit 对查询数量进⾏限制。
查询字段尽量落在索引上，尤其是复合索引，更需要注意最左前缀匹配。
拆分⼤的 delete / insert 操作，⼀⽅⾯会锁表，影响其他业务操作，还有⼀⽅⾯是 MySQL 对 sql 长度也是有限制的。
slidetoggle中多个div
不建议使⽤ MySQL 的函数，计算等，可先由程序处理，从上⾯提的⼀些点会发现，能交由程序处理的尽量不要把压⼒转⾄数据库上。因为多数的服务器性能瓶颈都在数据库上。
查询 count，性能：count(1) = count(*) > count(主键) > count(其他字段)。
查询操作符能⽤ between 则不⽤ in，能⽤ in 则不⽤ or。
避免使⽤!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符，因为这些查询⽆法使⽤索引。
sql 尽量简单，少⽤ join，不建议两个 join 以上。
千万级数据处理⽅案
数据存储结构设计
到了这个阶段的数据量，数据本⾝已经有很⼤的价值了，数据除了满⾜常规业务需求外，还会有⼀些数据分析的需求。⽽这个时候数据可变动性不⾼，基本上不会考虑修改原有结构，⼀般会考虑从分区，分表，分库三⽅⾯做优化：
分区：
分区是根据⼀定的规则，数据库把⼀个表分解成多个更⼩的、更容易管理的部分，是⼀种⽔平划分。对应⽤来说是完全透明的，不影响应⽤的业务逻辑，即不⽤修改代码。因此能存更多的数据，查询，删除也⽀持按分区来操作，从⽽达到优化的⽬的。如果有考虑分区，可以提前做准备，避免下列⼀些限制：
⼀个表最多只能有1024个分区（mysql5.6之后⽀持8192个分区）。但你实际操作的时候，最好不要⼀次性打开超过 100个分区，因为打开分区也是有时间损耗的。
如果分区字段中有主键或者唯⼀索引列，那么所有主键列和唯⼀索引列都必须包含进来，如果表中有主键或唯⼀索引，那么分区键必须是主键或唯⼀索引。
分区表中⽆法使⽤外键约束。
NULL值会使分区过滤⽆效，这样会被放⼊默认的分区⾥，请千万不要让分区字段出现 NULL。
网页爬虫程序所有分区必须使⽤相同的存储引擎。
分表：
分表分⽔平分表和垂直分表。
⽔平分表即拆分成数据结构相同的各个⼩表，如拆分成 table1, ，从⽽缓解数据库读写压⼒。
垂直分表即将⼀些字段分出去形成⼀个新表，各个表数据结构不相同，可以优化⾼并发下锁表的情况。
可想⽽知，分表的话，程序的逻辑是需要做修改的，所以，⼀般是在项⽬初期时，预见到⼤数据量的情况，才会考虑分表。后期阶段不建议分表，成本很⼤。
分库：
分库⼀般是主从模式，⼀个数据库服务器主节点复制到⼀个或多个从节点多个数据库，主库负责写操作，从库负责读操作，从⽽达到主从分离，⾼可⽤，数据备份等优化⽬的。
当然，主从模式也会有⼀些缺陷，主从同步延迟，binlog ⽂件太⼤导致的问题等等，这⾥不细讲（笔者也学不动了）。
其他：
冷热表隔离。对于历史的数据，查询和使⽤的⼈数少的情况，可以移⼊另⼀个冷数据库⾥，只提供查询⽤，来缓解热表数据量⼤的情况。
数据库表主键设计
数据库主键设计，个⼈推荐带有时间属性的⾃增长数字ID。（分布式⾃增长ID⽣成算法）
雪花算法
百度分布式ID算法
美团分布式ID算法
为什么要使⽤这些算法呢，这个与MySQL数据存储结构有关
从业务上来说：
在设计数据库时不需要费尽⼼思去考虑设置哪个字段为主键。然后是这些字段只是理论上是唯⼀的，例如使⽤图书编号为主键，这个图书编号只是理论上来说是唯⼀的，但实践中可能会出现重复的情况。所以还是设置⼀个与业务⽆关的⾃增ID作为主键，然后增加⼀个图书编号的唯⼀性约束。
从技术上来说：
1.如果表使⽤⾃增主键，那么每次插⼊新的记录，记录就会顺序添加到当前索引节点的后续位置，当
⼀页写满，就会⾃动开辟⼀个新的页。总的来说就是可以提⾼查询和插⼊的性能。
2.对InnoDB来说主键索引既存储索引值，⼜在叶⼦节点中存储⾏的数据，也就是说数据⽂件本⾝就是按照b+树⽅式存放数据的。
3.如果没有定义主键，则会使⽤⾮空的UNIQUE键做主键 ; 如果没有⾮空的UNIQUE键，则系统⽣成⼀个6字节的rowid做主键；聚簇索引中，N⾏形成⼀个页（⼀页通常⼤⼩为16K)。如果碰到不规则数据插⼊时，为了保持B+树的平衡，会造成频繁的页分裂和页旋转，插⼊速度⽐较慢。所以聚簇索引的主键值应尽量是连续增长的值，⽽不是随机值(不要⽤随机字符串或UUID)。
4.故对于InnoDB的主键，尽量⽤整型，⽽且是递增的整型。这样在存储/查询上都是⾮常⾼效的。
MySQL⾯试题
MySQL数据库千万级数据查询优化⽅案
limit分页查询越靠后查询越慢。这也让我们得出⼀个结论：
1、limit语句的查询时间与起始记录的位置成正⽐。
2、mysql的limit语句是很⽅便，但是对记录很多的表并不适合直接使⽤
表使⽤InnoDB作为存储引擎，id作为⾃增主键，默认为主键索引
SELECT id FROM test LIMIT 9000000,100;
黑马程序员教材现在优化的⽅案有两种，即通过id作为查询条件使⽤⼦查询实现和使⽤join实现；
1、id>=的（⼦查询）形式实现
select * from test where id >= (select id from test limit 9000000,1)limit 0,100
使⽤join的形式；
SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 9000000,100) b ON a.id = b.id
这两种优化查询使⽤时间⽐较接近，其实两者⽤的都是⼀个原理，所以效果也差不多。但个⼈建议最好使⽤join，尽量减少⼦查询的使⽤。注：⽬前是千万级别查询，如果将⾄百万级别，速度会更快。
SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 1000000,100) b ON a.id = b.id
你⽤过MySQL那些存储引擎
他们都有什么特点和区别？
这是⾼级开发者⾯试时经常被问的问题。实际我们在平时的开发中，经常会遇到的。Mysql的存储引擎有这么多种，实际我们在平时⽤的最多的莫过于InnoDB和MyISAM了。所有如果⾯试官问道mysql有哪些存储引擎，你只需要告诉这两个常⽤的就⾏。
那他们都有什么特点和区别呢？
MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问⽅法) 的缩写，它是存储记录和⽂件的标准⽅法。不是事务安全的，⽽且不⽀持外键，如果执⾏⼤量的select，insert MyISAM⽐较适合。
InnoDB：⽀持事务安全的引擎，⽀持外键、⾏锁、事务是他的最⼤特点。如果有⼤量的update和insert，建议使⽤InnoDB，特别是针对多个并发和QPS较⾼的情况。注：在MySQL 5.5之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB
MyISAM和InnoDB的区别
1.InnoDB⽀持事务，MyISAM不⽀持。对于InnoDB每⼀条SQL语⾔都默认封装成事务，⾃动提交，这样会影响速度，所以最好把多条SQL语⾔放在begin和commit之间，组成⼀个事务；
2.InnoDB⽀持外键，⽽MyISAM不⽀持。
3.InnoDB是聚集索引，使⽤B+Tree作为索引结构，数据⽂件是和（主键）索引绑在⼀起的（表数据⽂件本⾝就是按B+Tree组织的⼀个索引结构），必须要有主键，通过主键索引效率很⾼。MyISAM是⾮聚集索引，也是使⽤B+Tree作为索引结构，索引和数据⽂件是分离的，索引保存的是数据⽂件的指针。主键索引和辅助索引是独⽴的。
4.InnoDB不保存表的具体⾏数，执⾏select count(*) from table时需要全表扫描。⽽MyISAM⽤⼀个变量保存了整个表的⾏数，
执⾏上述语句时只需要读出该变量即可，速度很快。
5.Innodb不⽀持全⽂索引，⽽MyISAM⽀持全⽂索引，查询效率上MyISAM要⾼；5.7以后的InnoDB⽀持全⽂索引了。
6.InnoDB⽀持表、⾏级锁(默认)，⽽MyISAM⽀持表级锁。；
7.InnoDB表必须有主键（⽤户没有指定的话会⾃⼰或⽣产⼀个主键），⽽Myisam可以没有。
8.Innodb存储⽂件有frm、ibd，⽽Myisam是frm、MYD、MYI。
9.Innodb：frm是表定义⽂件，ibd是数据⽂件。
10.Myisam：frm是表定义⽂件，myd是数据⽂件，myi是索引⽂件。
MySQL复杂查询语句的优化
说到复杂SQL优化，最多的是由于多表关联造成了⼤量的复杂的SQL语句，那我们拿到这种sql到底该怎么优化呢，实际优化也是有套路的，只要按照套路执⾏就⾏。复杂SQL优化⽅案：
1.使⽤EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈，就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利⽤的，你的数据表是如何被搜索和排序的，是否有全表扫描等；
mysql语句的执行顺序2.查询的条件尽量使⽤索引字段，如某⼀个表有多个条件，就尽量使⽤复合索引查询，复合索引使⽤要注意字段的先后顺序。
3.多表关联尽量⽤join，减少⼦查询的使⽤。表的关联字段如果能⽤主键就⽤主键，也就是尽可能的使⽤索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。mysqlpostgresql比较
4.尽量使⽤limit进⾏分页批量查询，不要⼀次全部获取。
5.绝对避免select *的使⽤，尽量select具体需要的字段，减少不必要字段的查询；
6.尽量将or 转换为 union all。
7.尽量避免使⽤is null或is not null。
8.要注意like的使⽤，前模糊和全模糊不会⾛索引。
9.Where后的查询字段尽量减少使⽤函数，因为函数会造成索引失效。
10.避免使⽤不等于（!=），因为它不会使⽤索引。
11.⽤exists代替in，not exists代替not in，效率会更好；
12.避免使⽤HAVING⼦句, HAVING 只会在检索出所有记录之后才对结果集进⾏过滤，这个处理需要排序，总计等操作。如果能通过WHERE⼦句限制记录的数⽬，那就能减少这⽅⾯的开销。
13.千万不要 ORDER BY RAND()
以上就是详解MySQL数据库千万级数据查询和存储的详细内容，更多关于MySQL数据库千万级数据查询和存储的资料请关注其它相关⽂章！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。