详解MySQL数据库千万级数据查询和存储

admin MySQL 2022-02-09 19:35:34 mysql 数据 mysql 存储千万级优化"

百万级数据处理方案

数据存储结构设计

表字段设计

表字段 not null，因为 null 值很难查询优化且占用额外的索引空间，推荐默认数字 0。
数据状态类型的字段，比如 status, type 等等，尽量不要定义负数，如 -1。因为这样可以加上 UNSIGNED，数值容量就会扩大一倍。
可以的话用 TINYINT、SMALLINT 等代替 INT，尽量不使用 BIGINT，因为占的空间更小。
字符串类型的字段会比数字类型占的空间更大，所以尽量用整型代替字符串，很多场景是可以通过编码逻辑来实现用整型代替的。
字符串类型长度不要随意设置，保证满足业务的前提下尽量小。
用整型来存 IP。
单表不要有太多字段，建议在20以内。
为能预见的字段提前预留，因为数据量越大，修改数据结构越耗时。

索引设计

索引，空间换时间的优化策略，基本上根据业务需求设计好索引，足以应付百万级的数据量，养成使用 explain 的习惯，关于 explain 也可以访问：explain 让你的 sql 写的更踏实了解更多。
一个常识：索引并不是越多越好，索引是会降低数据写入性能的。
索引字段长度尽量短，这样能够节省大量索引空间；
取消外键，可交由程序来约束，性能更好。
复合索引的匹配最左列规则，索引的顺序和查询条件保持一致，尽量去除没必要的单列索引。
值分布较少的字段（不重复的较少）不适合建索引，比如像性别这种只有两三个值的情况字段建立索引意义不大。
需要排序的字段建议加上索引，因为索引是会排序的，能提高查询性能。
字符串字段使用前缀索引，不使用全字段索引，可大幅减小索引空间。

查询语句优化

尽量使用短查询替代复杂的内联查询。
查询不使用 select *，尽量查询带索引的字段，避免回表。
尽量使用 limit 对查询数量进行限制。
查询字段尽量落在索引上，尤其是复合索引，更需要注意最左前缀匹配。
拆分大的 delete / insert 操作，一方面会锁表，影响其他业务操作，还有一方面是 MySQL 对 sql 长度也是有限制的。
不建议使用 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, table2...，从而缓解数据库读写压力。

垂直分表即将一些字段分出去形成一个新表，各个表数据结构不相同，可以优化高并发下锁表的情况。

可想而知，分表的话，程序的逻辑是需要做修改的，所以，一般是在项目初期时，预见到大数据量的情况，才会考虑分表。后期阶段不建议分表，成本很大。

分库：

分库一般是主从模式，一个数据库服务器主节点复制到一个或多个从节点多个数据库，主库负责写操作，从库负责读操作，从而达到主从分离，高可用，数据备份等优化目的。

当然，主从模式也会有一些缺陷，主从同步延迟，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是索引文件。