天下功夫,唯快不破!
谢邀,利益相关。
大多数互联网应用场景都是读多写少,业务逻辑更多分布在写上。对读的要求大概就是要快。那么都有什么原因会导致我们完成一次出色的慢查询呢?
在数据量不是很大时,大多慢查询可以用索引解决,大多慢查询也因为索引不合理而产生。
MySQL 索引基于 B+ 树,这句话相信面试都背烂了,接着就可以问最左前缀索引、 B+ 树和各种树了。
说到最左前缀,实际就是组合索引的使用规则,使用合理组合索引可以有效的提高查询速度,为什么呢?
因为索引下推。如果查询条件包含在了组合索引中,比如存在组合索引(a,b),查询到满足 a 的记录后会直接在索引内部判断 b 是否满足,减少回表次数。同时,如果查询的列恰好包含在组合索引中,即为覆盖索引,无需回表。索引规则估计都知道,实际开发中也会创建和使用。[金三银四,如何准备]
建了索引还慢,多半是索引失效(未使用),可用 explain 分析。索引失效常见原因有 :
(一张嘴就知道老面试题了) 为什么这些做法会导致失效,成熟的 MySQL 也有自己的想法。
如果要 MySQL 给一个理由,还是那棵 B+ 树。
当在 查询 where = 左侧使用表达式或函数时,如字段 A 为字符串型且有索引, 有 where length(a) = 6
查询,这时传递一个 6 到 A 的索引树,不难想象在树的第一层就迷路了。
隐式类型转换和隐式字符编码转换也会导致这个问题。
至于 Like 语句 % 开头、字符串未加 ’’ 原因基本一致,MySQL 认为对索引字段的操作可能会破坏索引有序性就机智的优化掉了。
不过,对于如性别这种区分度过低的字段,索引失效就不是因为这个原因。
为什么索引区分度低的字段不要加索引。盲猜效率低,效率的确低,有时甚至会等于没加。
对于非聚簇索引,是要回表的。假如有 100 条数据,在 sex 字段建立索引,扫描到 51 个 male,需要再回表扫描 51 行。还不如直接来一次全表扫描呢。
所以,InnoDB 引擎对于这种场景就会放弃使用索引,至于区分度多低多少会放弃,大致是某类型的数据占到总的 30% 左右时,就会放弃使用该字段的索引,有兴趣可以试一下。
前面说到大多慢查询都源于索引,怎么建立并用好索引。这里有一些简单的规则。
有时,建立了猛一看挺正确的索引,但事情却没按计划发展。就像“为啥 XXX 有索引,根据它查询还是慢查询”。
此刻没准要自信点:我的代码不可能有 BUG,肯定是 MySQL 出了问题。MySQL 的确可能有点问题。
这种情况常见于建了一大堆索引,查询条件一大堆。没使用你想让它用的那一个,而是选了个区分度低的,导致过多的扫描。造成的原因基本有两个:
analyze table x
重新分析。force index
强制指定。或修改语句引导优化器,增加或删除索引绕过。但根据我浅薄的经验来看,更可能是因为你建了些没必要的索引导致的。不会真有人以为 MySQL 没自己机灵吧?
除了上面这些索引原因外,还有下面这些不常见或者说不好判断的原因存在。
在 MySQL 5.5 版本中引入了 MDL,对一个表做 CRUD 操作时,自动加 MDL 读锁;对表结构做变更时,加 MDL 写锁。读写锁、写锁间互斥。
当某语句拿 MDL 写锁就会阻塞 MDL 读锁,可以使用show processlist
命令查看处于Waiting for table metadata lock
状态的语句。
flush 很快,大多是因为 flush 命令被别的语句堵住,它又堵住了 select 。通过show processlist
命令查看时会发现处于Waiting for table flush
状态。
某事物持有写锁未提交。
InnoDB 默认级别是可重复读。设想一个场景:事物 A 开始事务,事务 B 也开始执行大量更新。B 率先提交, A 是当前读,就要依次执行 undo log ,直到找到事务 B 开始前的值。
在未二次开发的 MYSQL 中,上亿的表肯定算大表,这种情况即使在索引、查询层面做到了较好实现,面对频繁聚合操作也可能会出现 IO 或 CPU 瓶颈,即使是单纯查询,效率也会下降。
且 Innodb 每个 B+ 树节点存储容量是 16 KB,理论上可存储 2kw 行左右,这时树高为3层。我们知道,innodb_buffer_pool 用来缓存表及索引,如果索引数据较大,缓存命中率就堪忧,同时 innodb_buffer_pool 采用 LRU 算法进行页面淘汰,如果数据量过大,对老或非热点数据的查询可能就会把热点数据给挤出去。
所以对于大表常见优化即是分库分表和读写分离了。
是分库还是分表呢?这要具体分析。
水平即切分数据,分散原有数据到更多的库表中。
垂直即按照业务对库,按字段对表切分。
工具方面有 sharding-sphere、TDDL、Mycat。动起手来需要先评估分库、表数,制定分片规则选 key,再开发和数据迁移,还要考虑扩容问题。
实际运行中,写问题不大,主要问题在于唯一 ID 生成、非 partition key 查询、扩容。
当然,如果分库还会面临事务一致性和跨库 join 等问题。
分表针对大表解决 CPU 瓶颈,分库解决 IO 瓶颈,二者将存储压力解决了。但查询还不一定。
如果落到 DB 的 QPS 还是很高,且读远大于写,就可以考虑读写分离,基于主从模式将读的压力分摊,避免单机负载过高,同时也保证了高可用,实现了负载均衡。
主要问题有过期读和分配机制。
以上列举了 MySQL 常见慢查询原因和处理方法,介绍了应对较大数据场景的常用方法。
分库分表和读写分离是针对大数据或并发场景的,同时也为了提高系统的稳定和拓展性。但也不是所有的问题都最适合这么解决。
前文有提到对于关键字查询可以使用 ES。那接着聊聊 ES 。
ES 是基于 Lucene 的近实时分布式搜索引擎。使用场景有全文搜索、NoSQL Json 文档数据库、监控日志、数据采集分析等。
对非数据开发来说,常用的应该就是全文检索和日志了。ES 的使用中,常和 Logstash, Kibana 结合,也成为 ELK 。先来瞧瞧日志怎么用的。
下面是我司日志系统某检索操作:打开 Kibana 在 Discover 页面输入格式如 “xxx” 查询。
该操作可以在 Dev Tools 的控制台替换为:
GET yourIndex/_search
{
"from" : 0, "size" : 10,
"query" : {
"match_phrase" : {
"log" : "xxx"
}
}
}
什么意思?Discover 中加上 “” 和 console 中的 match_phrase 都代表这是一个短语匹配,意味着只保留那些包含全部搜索词项,且位置与搜索词项相同的文档。
在 ES 7.0 之前存储结构是 Index -> Type -> Document,按 MySQL 对比就是 database - table - id(实际这种对比不那么合理)。7.0 之后 Type 被废弃了,就暂把 index 当做 table 吧。
在 Dev Tools 的 Console 可以通过以下命令查看一些基本信息。也可以替换为 crul 命令。
重点是 mapping 和 setting ,mapping 可以理解为 MySQL 中表的结构定义,setting 负责控制如分片数量、副本数量。
以下是截取了某日志 index 下的部分 mapping 结构,ES 对字符串类型会默认定义成 text ,同时为它定义一个叫做 keyword 的子字段。这两的区别是:text 类型会进行分词, keyword 类型不会进行分词。
"******": {
"mappings": {
"doc": {
"properties": {
"appname": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
分词是什么意思?看完 ES 的索引原理你就 get 了。
ES 基于倒排索引。嘛意思?传统索引一般是以文档 ID 作索引,以内容作为记录。倒排索引相反,根据已有属性值,去找到相应的行所在的位置,也就是将单词或内容作为索引,将文档 ID 作为记录。
下图是 ES 倒排索引的示意图,由 Term index,Team Dictionary 和 Posting List 组成。
图中的 Ada、Sara 被称作 term,其实就是分词后的词了。如果把图中的 Term Index 去掉,是不是有点像 MySQL 了?Term Dictionary 就像二级索引,但 MySQL 是保存在磁盘上的,检索一个 term 需要若干次的 random access 磁盘操作。
而 ES 在 Term Dictionary 基础上多了层 Term Index ,它以 FST 形式保存在内存中,保存着 term 的前缀,借此可以快速的定位到 Term dictionary 的本 term 的 offset 。而且 FST 形式和 Term dictionary 的 block 存储方式都很节省内存和磁盘空间。
到这就知道为啥快了,就是因为有了内存中的 Term Index , 它为 term 的索引 Term Dictionary 又做了一层索引。
不过,也不是说 ES 什么查询都比 MySQL 快。检索大致分为两类。
ES 的索引存储的就是分词排序后的结果。比如图中的 Ada,在 MySQL 中 %da% 就扫全表了,但对 ES 来说可以快速定位。
该情况其实相差是不大的,因为 Term Index 的优势没了,却还要借此找到在 term dictionary 中的位置。也许由于 MySQL 覆盖索引无需回表会更快一点。
如前所述,对于业务中的查询场景什么时候适合使用 ES ?我觉得有两种。
在 MySQL 中字符串类型根据关键字模糊查询就是一场灾难,对 ES 来说却是小菜一碟。具体场景,比如消息表对消息内容的模糊查询,即聊天记录查询。
但要注意,如果需要的是类似广大搜索引擎的关键字查询而非日志的短语匹配查询,就需要对中文进行分词处理,最广泛使用的是 ik 。Ik 分词器的安装这里不再细说。
什么意思呢?
开头对日志的查询,键入 “我可真是个机灵鬼” 时,只会得到完全匹配的信息。
而倘若去掉 “”,又会得到按照 “我”、“可”,“真”….分词匹配到的所有信息,这明显会返回很多信息,也是不符合中文语义的。实际期望的分词效果大概是“我”、“可”、“真是”,“机灵鬼”,之后再按照这种分词结果去匹配查询。
这是 ES 默认的分词策略对中文的支持不友善导致的,按照英语单词字母来了,可英语单词间是带有空格的。这也是不少国外软件中文搜索效果不 nice 的原因之一。
对于该问题,你可以在 console 使用下方命令,测试当前 index 的分词效果。
POST yourindex/_analyze
{
"field":"yourfield",
"text":"我可真是个机灵鬼"
}
如果数据量够大,表字段又够多。把所有字段信息丢到 ES 里创建索引是不合理的。使用 MySQL 的话那就只能按前文提到的分库分表、读写分离来了。何不组合下。
将要参与查询的字段信息加上 id,放入 ES,做好分词。将全量信息放入 MySQL,通过 id 快速检索。
如果要省去分库分表什么的,或许可以抛弃 MySQL ,选择分布式数据库,比如 HBASE , 对于这种 NOSQL 来说,存储能力海量,扩容 easy ,根据 rowkey 查询也很快。
以上思路都是经典的索引与数据存储隔离的方案了。
当然,摊子越大越容易出事,也会面临更多的问题。使用 ES 作索引层,数据同步、时序性、mapping 设计、高可用等都需要考虑。
毕竟和单纯做日志系统对比,日志可以等待,用户不能。
本节简单介绍了 ES 为啥快,和这个快能用在哪。现在你可以打开 Kibana 的控制台试一试了。
如果想在 Java 项目中接入的话,有 SpringBoot 加持,在 ES 环境 OK 的前提下,完全是开箱即用,就差一个依赖了。基本的 CRUD 支持都是完全 OK 的。
前面有提到 HBASE , 什么是 HBASE ,鉴于篇幅这里简单说说。
关系型数据库如 MySQL 是按行来的。
姓名 | 小学 | 中学 | 大学 |
---|---|---|---|
李某 | XX小学 | YY中学 | NULL |
HBASE 是按列的(实际是列族)。列式存储上表就会变成:
姓名 | 学校名称 |
---|---|
李某 | XX小学 |
李某 | YY中学 |
HBASE 实际的表模型结构。
Row key 是主键,按照字典序排序。TimeStamp 是版本号。info 和 area 都是列簇(column Family),列簇将表进行横向切割。name、age 叫做列,属于某一个列簇,可进行动态添加。Cell 是具体的 Value 。
数据处理大致可分成两大类:联机事务处理OLTP(on-line transaction processing)、联机分析处理OLAP(On-Line Analytical Processing)。
面向列的适合做 OLAP,面向行的适用于联机事务处理(OLTP)。不过 HBASE 并不是 OLAP ,他没有 transaction,实际上也是面向 CF 的。一般也没多少人用 HBASE 做 OLAP 。
HBASE 表设计的好不好,就看 RowKey 设计。这是因为 HBASE 只支持三种查询方式
1、基于 Rowkey 的单行查询 2、基于 Rowkey 的范围扫描 3、全表扫描
可见 HBASE 并不支持复杂查询。
HBASE 并非适用于实时快速查询。它更适合写密集型场景,它拥用快速写入能力,而查询对于单条或小面积查询是 OK 的,当然也只能根据 rowkey。但它的性能和可靠性非常高,不存在单点故障。
个人觉得软件开发是循序渐进的,技术服务于项目,合适比新颖复杂更重要。
如何完成一次快速的查询?最该做的还是先找找自己的 Bug,解决了当前问题再创造新问题。
本文列举到的部分方案对于具体实现大多一笔带过,实际无论是 MySQL 的分表还是 ES 的业务融合都会面临很多细节和困难的问题,搞工程的总要绝知此事要躬行。
来源:http://7li49.cn/PYcfy
本文由哈喽比特于3年以前收录,如有侵权请联系我们。
文章来源:https://mp.weixin.qq.com/s/h9iCw0nTJhcHKxdIFnk0AA
京东创始人刘强东和其妻子章泽天最近成为了互联网舆论关注的焦点。有关他们“移民美国”和在美国购买豪宅的传言在互联网上广泛传播。然而,京东官方通过微博发言人发布的消息澄清了这些传言,称这些言论纯属虚假信息和蓄意捏造。
日前,据博主“@超能数码君老周”爆料,国内三大运营商中国移动、中国电信和中国联通预计将集体采购百万台规模的华为Mate60系列手机。
据报道,荷兰半导体设备公司ASML正看到美国对华遏制政策的负面影响。阿斯麦(ASML)CEO彼得·温宁克在一档电视节目中分享了他对中国大陆问题以及该公司面临的出口管制和保护主义的看法。彼得曾在多个场合表达了他对出口管制以及中荷经济关系的担忧。
今年早些时候,抖音悄然上线了一款名为“青桃”的 App,Slogan 为“看见你的热爱”,根据应用介绍可知,“青桃”是一个属于年轻人的兴趣知识视频平台,由抖音官方出品的中长视频关联版本,整体风格有些类似B站。
日前,威马汽车首席数据官梅松林转发了一份“世界各国地区拥车率排行榜”,同时,他发文表示:中国汽车普及率低于非洲国家尼日利亚,每百户家庭仅17户有车。意大利世界排名第一,每十户中九户有车。
近日,一项新的研究发现,维生素 C 和 E 等抗氧化剂会激活一种机制,刺激癌症肿瘤中新血管的生长,帮助它们生长和扩散。
据媒体援引消息人士报道,苹果公司正在测试使用3D打印技术来生产其智能手表的钢质底盘。消息传出后,3D系统一度大涨超10%,不过截至周三收盘,该股涨幅回落至2%以内。
9月2日,坐拥千万粉丝的网红主播“秀才”账号被封禁,在社交媒体平台上引发热议。平台相关负责人表示,“秀才”账号违反平台相关规定,已封禁。据知情人士透露,秀才近期被举报存在违法行为,这可能是他被封禁的部分原因。据悉,“秀才”年龄39岁,是安徽省亳州市蒙城县人,抖音网红,粉丝数量超1200万。他曾被称为“中老年...
9月3日消息,亚马逊的一些股东,包括持有该公司股票的一家养老基金,日前对亚马逊、其创始人贝索斯和其董事会提起诉讼,指控他们在为 Project Kuiper 卫星星座项目购买发射服务时“违反了信义义务”。
据消息,为推广自家应用,苹果现推出了一个名为“Apps by Apple”的网站,展示了苹果为旗下产品(如 iPhone、iPad、Apple Watch、Mac 和 Apple TV)开发的各种应用程序。
特斯拉本周在美国大幅下调Model S和X售价,引发了该公司一些最坚定支持者的不满。知名特斯拉多头、未来基金(Future Fund)管理合伙人加里·布莱克发帖称,降价是一种“短期麻醉剂”,会让潜在客户等待进一步降价。
据外媒9月2日报道,荷兰半导体设备制造商阿斯麦称,尽管荷兰政府颁布的半导体设备出口管制新规9月正式生效,但该公司已获得在2023年底以前向中国运送受限制芯片制造机器的许可。
近日,根据美国证券交易委员会的文件显示,苹果卫星服务提供商 Globalstar 近期向马斯克旗下的 SpaceX 支付 6400 万美元(约 4.65 亿元人民币)。用于在 2023-2025 年期间,发射卫星,进一步扩展苹果 iPhone 系列的 SOS 卫星服务。
据报道,马斯克旗下社交平台𝕏(推特)日前调整了隐私政策,允许 𝕏 使用用户发布的信息来训练其人工智能(AI)模型。新的隐私政策将于 9 月 29 日生效。新政策规定,𝕏可能会使用所收集到的平台信息和公开可用的信息,来帮助训练 𝕏 的机器学习或人工智能模型。
9月2日,荣耀CEO赵明在采访中谈及华为手机回归时表示,替老同事们高兴,觉得手机行业,由于华为的回归,让竞争充满了更多的可能性和更多的魅力,对行业来说也是件好事。
《自然》30日发表的一篇论文报道了一个名为Swift的人工智能(AI)系统,该系统驾驶无人机的能力可在真实世界中一对一冠军赛里战胜人类对手。
近日,非营利组织纽约真菌学会(NYMS)发出警告,表示亚马逊为代表的电商平台上,充斥着各种AI生成的蘑菇觅食科普书籍,其中存在诸多错误。
社交媒体平台𝕏(原推特)新隐私政策提到:“在您同意的情况下,我们可能出于安全、安保和身份识别目的收集和使用您的生物识别信息。”
2023年德国柏林消费电子展上,各大企业都带来了最新的理念和产品,而高端化、本土化的中国产品正在不断吸引欧洲等国际市场的目光。
罗永浩日前在直播中吐槽苹果即将推出的 iPhone 新品,具体内容为:“以我对我‘子公司’的了解,我认为 iPhone 15 跟 iPhone 14 不会有什么区别的,除了序(列)号变了,这个‘不要脸’的东西,这个‘臭厨子’。