金华做网站的公司,平台类网站有哪些,怎么做app平台,怎么制作手机MySQL 百万级/千万级数据存储解决方案
百万级、千万级数据处理#xff0c;个人认为核心关键在于数据存储方案设计#xff0c;存储方案设计的是否合理#xff0c;直接影响到数据CRUD操作。总体设计可以考虑一下三个方面进行设计考虑#xff1a;
数据存储结构设计索引设计数…MySQL 百万级/千万级数据存储解决方案
百万级、千万级数据处理个人认为核心关键在于数据存储方案设计存储方案设计的是否合理直接影响到数据CRUD操作。总体设计可以考虑一下三个方面进行设计考虑
数据存储结构设计索引设计数据主键设计查询方案设计
百万级数据处理方案
数据存储结构设计
表字段设计
表字段 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作为主键然后增加一个图书编号的唯一性约束。
从技术上来说
如果表使用自增主键那么每次插入新的记录记录就会顺序添加到当前索引节点的后续位置当一页写满就会自动开辟一个新的页。 总的来说就是可以提高查询和插入的性能。
对InnoDB来说主键索引既存储索引值又在叶子节点中存储行的数据也就是说数据文件本身就是按照b树方式存放数据的。如果没有定义主键则会使用非空的UNIQUE键做主键 ; 如果没有非空的UNIQUE键则系统生成一个6字节的rowid做主键;聚簇索引中N行形成一个页一页通常大小为16K)。如果碰到不规则数据插入时为了保持B树的平衡会造成频繁的页分裂和页旋转插入速度比较慢。所以聚簇索引的主键值应尽量是连续增长的值而不是随机值(不要用随机字符串或UUID)。故对于InnoDB的主键尽量用整型而且是递增的整型。这样在存储/查询上都是非常高效的。
MySQL面试题
MySQL数据库千万级数据查询优化方案
limit分页查询越靠后查询越慢。这也让我们得出一个结论
1limit语句的查询时间与起始记录的位置成正比。
2mysql的limit语句是很方便但是对记录很多的表并不适合直接使用 表使用InnoDB作为存储引擎id作为自增主键默认为主键索引 SELECT id FROM test LIMIT 9000000,100;
现在优化的方案有两种即通过id作为查询条件使用子查询实现和使用join实现 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 (有索引的顺序访问方法) 的缩写它是存储记录和文件的标准方法。不是事务安全的而且不支持外键如果执行大量的selectinsert MyISAM比较适合。 InnoDB支持事务安全的引擎支持外键、行锁、事务是他的最大特点。如果有大量的update和insert建议使用InnoDB特别是针对多个并发和QPS较高的情况。注在MySQL 5.5之前的版本中默认的搜索引擎是MyISAM从MySQL 5.5之后的版本中默认的搜索引擎变更为InnoDB
MyISAM和InnoDB的区别
InnoDB支持事务MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务自动提交这样会影响速度所以最好把多条SQL语言放在begin和commit之间组成一个事务InnoDB支持外键而MyISAM不支持。InnoDB是聚集索引使用BTree作为索引结构数据文件是和主键索引绑在一起的表数据文件本身就是按BTree组织的一个索引结构必须要有主键通过主键索引效率很高。MyISAM是非聚集索引也是使用BTree作为索引结构索引和数据文件是分离的索引保存的是数据文件的指针。主键索引和辅助索引是独立的。InnoDB不保存表的具体行数执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数执行上述语句时只需要读出该变量即可速度很快。Innodb不支持全文索引而MyISAM支持全文索引查询效率上MyISAM要高5.7以后的InnoDB支持全文索引了。InnoDB支持表、行级锁(默认)而MyISAM支持表级锁。InnoDB表必须有主键用户没有指定的话会自己找或生产一个主键而Myisam可以没有。Innodb存储文件有frm、ibd而Myisam是frm、MYD、MYI。Innodbfrm是表定义文件ibd是数据文件。Myisamfrm是表定义文件myd是数据文件myi是索引文件。
MySQL复杂查询语句的优化你会怎么做
说到复杂SQL优化最多的是由于多表关联造成了大量的复杂的SQL语句那我们拿到这种sql到底该怎么优化呢实际优化也是有套路的只要按照套路执行就行。复杂SQL优化方案
使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的你的数据表是如何被搜索和排序的是否有全表扫描等查询的条件尽量使用索引字段如某一个表有多个条件就尽量使用复合索引查询复合索引使用要注意字段的先后顺序。多表关联尽量用join减少子查询的使用。表的关联字段如果能用主键就用主键也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。尽量使用limit进行分页批量查询不要一次全部获取。绝对避免select *的使用尽量select具体需要的字段减少不必要字段的查询尽量将or 转换为 union all。尽量避免使用is null或is not null。要注意like的使用前模糊和全模糊不会走索引。Where后的查询字段尽量减少使用函数因为函数会造成索引失效。避免使用不等于!因为它不会使用索引。用exists代替innot exists代替not in效率会更好避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤这个处理需要排序总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。千万不要 ORDER BY RAND()
