详解MySQL数据库千万级数据查询和存储
作者:华为云开发者社区 发布时间:2024-01-16 18:53:20
百万级数据处理方案
数据存储结构设计
表字段设计
表字段 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是索引文件。
MySQL复杂查询语句的优化
说到复杂SQL优化,最多的是由于多表关联造成了大量的复杂的SQL语句,那我们拿到这种sql到底该怎么优化呢,实际优化也是有套路的,只要按照套路执行就行。复杂SQL优化方案:
1.使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈,就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的,是否有全表扫描等;
2.查询的条件尽量使用索引字段,如某一个表有多个条件,就尽量使用复合索引查询,复合索引使用要注意字段的先后顺序。
3.多表关联尽量用join,减少子查询的使用。表的关联字段如果能用主键就用主键,也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。
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()
来源:https://www.cnblogs.com/huaweiyun/p/14114019.html
猜你喜欢
- 这篇博客将介绍如何通过OpenCV中图像修复的技术——cv2.inpaint() 去除旧照片中的小噪音、笔划等。并提供一个可交互式的程序,利
- python 数据的清理行为实例详解数据清洗主要是指填充缺失数据,消除噪声数据等操作,主要还是通过分析“脏数据”产生的原因和存在形式,利用现
- ISSET();——适合于检测是否存在这个参数。 定义和作用范围:用于测试一个变量是否具有值(包括0,FALSE,或者一个空字串,但不能是N
- 1、爬取网页分析爬取的目标网址为:https://www.gushiwen.cn/在登陆界面需要做的工作有,获取验证码图片,并识别该验证码,
- 【方法一】: 通过setuptools来安装python模块首先下载 http://peak.telecommunity.com/dist/
- Array可以使用下标,Map和Set不能使用下标,ES6引入了iterable类型,Array,Map,Set都属于iterable类型,
- 阅读上一篇:FrontPage XP设计教程5——表单的设计 在制作出图文并茂的网页之后,很多读者朋友还想让自己的网页能够播放音乐、视频等多
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN&
- 前言MySQL Innodb 的锁可以说是执行引擎的并发基础了,有了锁才能保证数据的一致性。众所周知,我们都知道 Innodb 有全局锁、表
- 我就废话不多说了,大家还是直接看代码吧!#加载keras模块from __future__ import print_functionimp
- 如何剔除HTML标识?这里没有使用正则表达式来去除,当然大家用正则"<.+?>"也可以实现这个功能只留下干净
- 1. js的数据类型1.1 js引入方式<!DOCTYPE html><html lang="en"&
- 自定义可迭代的类列表可以获取列表的长度,然后使用变量i对列表索引进行循环,也可以获取集合的所有元素,且容易理解。没错,使用列表的代码是容易理
- 游戏说明:一个考验您记忆力的游戏,只要两个方块的;图案能够凑成一对,最终翻开所有的图片,那么您就获胜,计算机将自动记录您的游戏时
- 一、闭包闭包的形成条件:1.函数嵌套。2.内部函数使用了外部函数的变量或者参数。3.外部函数返回了使用外 部变量的内部函数。二、一个简单的例
- nilGo中,每个指针都有2个基本信息,指针的类型和指针的值(type,value);当执行==时,需要比较类型与值(只有类型与值都相等时,
- 利用js实现点击一张图片,直接上传到指定的action,方法简单,一看就会了,只需要用户点击图片一次就可以实现图片上传功能。主要用到了onc
- 我希望大家看到该标题就能让想象到它的功能: 1、WITH TEMPL
- create table shujubiao( id int primary key auto_increment, 指定为i整形 name
- 缩略图在很多时候我们都需要将图片按照同比例缩小有利于存储 但是一张张手动去改的话太麻烦了 今天我们就用python实现一个简单的将一个文件夹