你知道的越多不知道的就越多业余的像一棵小草
你来我们一起精进你不来我和你的竞争对手一起精进
编辑业余草
http://dwz.date/d9WU
推荐https://www.xttblog.com/?p5120
哪个男孩不想完成一次快速的查询
1. MySQL查询慢是什么体验
谢邀利益相关。
大多数互联网应用场景都是读多写少业务逻辑更多分布在写上。对读的要求大概就是要快。那么都有什么原因会导致我们完成一次出色的慢查询呢
1.1 索引
在数据量不是很大时大多慢查询可以用索引解决大多慢查询也因为索引不合理而产生。
MySQL 索引基于 B 树这句话相信面试都背烂了接着就可以问最左前缀索引、 B 树和各种树了。
说到最左前缀实际就是组合索引的使用规则使用合理组合索引可以有效的提高查询速度为什么呢
因为索引下推。如果查询条件包含在了组合索引中比如存在组合索引a,b)查询到满足 a 的记录后会直接在索引内部判断 b 是否满足减少回表次数。同时如果查询的列恰好包含在组合索引中即为覆盖索引无需回表。索引规则估计都知道实际开发中也会创建和使用。问题可能更多的是为什么建了索引还慢
1.1.1 什么原因导致索引失效
建了索引还慢多半是索引失效未使用可用 explain 分析。索引失效常见原因有 :
where 中使用 ! 或 或 or 或表达式或函数左侧
like 语句 % 开头
字符串未加’’
索引字段区分度过低如性别
未匹配最左前缀
(一张嘴就知道老面试题了) 为什么这些做法会导致失效成熟的 MySQL 也有自己的想法。
1.1.2 这些原因为什么导致索引失效
如果要 MySQL 给一个理由还是那棵 B 树。
函数操作
当在 查询 where 左侧使用表达式或函数时如字段 A 为字符串型且有索引, 有 where length(a) 6查询这时传递一个 6 到 A 的索引树不难想象在树的第一层就迷路了。
隐式转换
隐式类型转换和隐式字符编码转换也会导致这个问题。
隐式类型转换对于 JOOQ 这种框架来说一般倒不会出现。
隐式字符编码转换在连表查询时倒可能出现即连表字段的类型相同但字符编码不同。
破坏了有序性
至于 Like 语句 % 开头、字符串未加 ’’ 原因基本一致MySQL 认为对索引字段的操作可能会破坏索引有序性就机智的优化掉了。
不过对于如性别这种区分度过低的字段索引失效就不是因为这个原因。
1.1.3 性别字段为什么不要加索引
为什么索引区分度低的字段不要加索引。盲猜效率低效率的确低有时甚至会等于没加。
对于非聚簇索引是要回表的。假如有 100 条数据在 sex 字段建立索引扫描到 51 个 male需要再回表扫描 51 行。还不如直接来一次全表扫描呢。
所以InnoDB 引擎对于这种场景就会放弃使用索引至于区分度多低多少会放弃大致是某类型的数据占到总的 30% 左右时就会放弃使用该字段的索引有兴趣可以试一下。
1.1.4 有什么好用且简单的索引方法
前面说到大多慢查询都源于索引怎么建立并用好索引。这里有一些简单的规则。
索引下推性别字段不适合建索引但确实存在查询场景怎么办如果是多条件查询可以建立联合索引利用该特性优化。
覆盖索引也是联合索引查询需要的信息在索引里已经包含了就不会再回表了。
前缀索引对于字符串可以只在前 N 位添加索引避免不必要的开支。假如的确需要如关键字查询那交给更合适的如 ES 或许更好。
不要对索引字段做函数操作
对于确定的、写多读少的表或者频繁更新的字段都应该考虑索引的维护成本。
1.1.5 如何评价 MySQL 选错了索引
有时建立了猛一看挺正确的索引但事情却没按计划发展。就像“为啥 XXX 有索引根据它查询还是慢查询”。
此刻没准要自信点我的代码不可能有 BUG肯定是 MySQL 出了问题。MySQL 的确可能有点问题。
这种情况常见于建了一大堆索引查询条件一大堆。没使用你想让它用的那一个而是选了个区分度低的导致过多的扫描。造成的原因基本有两个
信息统计不准确可以使用 analyze table x重新分析。
优化器误判可以 force index强制指定。或修改语句引导优化器增加或删除索引绕过。
但根据我浅薄的经验来看更可能是因为你建了些没必要的索引导致的。不会真有人以为 MySQL 没自己机灵吧
除了上面这些索引原因外还有下面这些不常见或者说不好判断的原因存在。
1.2 等MDL锁
在 MySQL 5.5 版本中引入了 MDL对一个表做 CRUD 操作时自动加 MDL 读锁对表结构做变更时加 MDL 写锁。读写锁、写锁间互斥。
当某语句拿 MDL 写锁就会阻塞 MDL 读锁可以使用show processlist命令查看处于Waiting for table metadata lock状态的语句。
1.3 等 flush
flush 很快大多是因为 flush 命令被别的语句堵住它又堵住了 select 。通过show processlist命令查看时会发现处于Waiting for table flush状态。
1.4 等行锁
某事物持有写锁未提交。
1.5 当前读
InnoDB 默认级别是可重复读。设想一个场景事物 A 开始事务事务 B 也开始执行大量更新。B 率先提交 A 是当前读就要依次执行 undo log 直到找到事务 B 开始前的值。
1.6 大表场景
在未二次开发的 MYSQL 中上亿的表肯定算大表这种情况即使在索引、查询层面做到了较好实现面对频繁聚合操作也可能会出现 IO 或 CPU 瓶颈即使是单纯查询效率也会下降。
且 Innodb 每个 B 树节点存储容量是 16 KB理论上可存储 2kw 行左右这时树高为3层。我们知道innodb_buffer_pool 用来缓存表及索引如果索引数据较大缓存命中率就堪忧同时 innodb_buffer_pool 采用 LRU 算法进行页面淘汰如果数据量过大对老或非热点数据的查询可能就会把热点数据给挤出去。
所以对于大表常见优化即是分库分表和读写分离了。
1.6.1 分库分表
方案
是分库还是分表呢这要具体分析。
如果磁盘或网络有 IO 瓶颈那就要分库和垂直分表。
如果是 CPU 瓶颈即查询效率偏低水平分表。
水平即切分数据分散原有数据到更多的库表中。
垂直即按照业务对库按字段对表切分。
工具方面有 sharding-sphere、TDDL、Mycat。动起手来需要先评估分库、表数制定分片规则选 key再开发和数据迁移还要考虑扩容问题。
问题
实际运行中写问题不大主要问题在于唯一 ID 生成、非 partition key 查询、扩容。
唯一 ID 方法很多DB 自增、Snowflake、号段、一大波GUID算法等。
非 partition key 查询常用映射法解决映射表用到覆盖索引的话还是很快的。或者可以和其他 DB 组合。
扩容要根据分片时的策略确定范围分片的话就很简单而随机取模分片就要迁移数据了。也可以用范围 取模的模式分片先取模再范围可以避免一定程度的数据迁移。
当然如果分库还会面临事务一致性和跨库 join 等问题。
1.6.2 读写分离
为什么要读写分离
分表针对大表解决 CPU 瓶颈分库解决 IO 瓶颈二者将存储压力解决了。但查询还不一定。
如果落到 DB 的 QPS 还是很高且读远大于写就可以考虑读写分离基于主从模式将读的压力分摊避免单机负载过高同时也保证了高可用实现了负载均衡。
问题
主要问题有过期读和分配机制。
过期读也就是主从延时问题这个对于。
分配机制是走主还是从库。可以直接代码中根据语句类型切换或者使用中间件。
1.7 小结
以上列举了 MySQL 常见慢查询原因和处理方法介绍了应对较大数据场景的常用方法。
分库分表和读写分离是针对大数据或并发场景的同时也为了提高系统的稳定和拓展性。但也不是所有的问题都最适合这么解决。
2. 如何评价 ElasticSearch
前文有提到对于关键字查询可以使用 ES。那接着聊聊 ES 。
2.1 可以干什么
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 都代表这是一个短语匹配意味着只保留那些包含全部搜索词项且位置与搜索词项相同的文档。
2.2 ES 的结构
在 ES 7.0 之前存储结构是 Index -> Type -> Document按 MySQL 对比就是 database - table - id(实际这种对比不那么合理)。7.0 之后 Type 被废弃了就暂把 index 当做 table 吧。
在 Dev Tools 的 Console 可以通过以下命令查看一些基本信息。也可以替换为 crul 命令。
GET /_cat/health?v查看集群健康状态
GET /_cat/shards?v 查看分片状态
GET yourindex/_mapping :index mapping结构
GET yourindex/_settings :index setting结构
GET /_cat/indices?v :查看当前节点所有索引信息
重点是 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 } }
2.3 ES 查询为什么快
分词是什么意思看完 ES 的索引原理你就 get 了。
ES 基于倒排索引。嘛意思传统索引一般是以文档 ID 作索引以内容作为记录。倒排索引相反根据已有属性值去找到相应的行所在的位置也就是将单词或内容作为索引将文档 ID 作为记录。
下图是 ES 倒排索引的示意图由 Term indexTeam 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 快。检索大致分为两类。
2.3.1 分词后检索
ES 的索引存储的就是分词排序后的结果。比如图中的 Ada在 MySQL 中 %da% 就扫全表了但对 ES 来说可以快速定位
2.3.2 精确检索
该情况其实相差是不大的因为 Term Index 的优势没了却还要借此找到在 term dictionary 中的位置。也许由于 MySQL 覆盖索引无需回表会更快一点。
2.4 什么时候用 ES
如前所述对于业务中的查询场景什么时候适合使用 ES 我觉得有两种。
2.4.1 全文检索
在 MySQL 中字符串类型根据关键字模糊查询就是一场灾难对 ES 来说却是小菜一碟。具体场景比如消息表对消息内容的模糊查询即聊天记录查询。
但要注意如果需要的是类似广大搜索引擎的关键字查询而非日志的短语匹配查询就需要对中文进行分词处理最广泛使用的是 ik 。Ik 分词器的安装这里不再细说。
什么意思呢
分词
开头对日志的查询键入 “我可真是个机灵鬼” 时只会得到完全匹配的信息。
而倘若去掉 “”又会得到按照 “我”、“可”“真”….分词匹配到的所有信息这明显会返回很多信息也是不符合中文语义的。实际期望的分词效果大概是“我”、“可”、“真是”“机灵鬼”之后再按照这种分词结果去匹配查询。
这是 ES 默认的分词策略对中文的支持不友善导致的按照英语单词字母来了可英语单词间是带有空格的。这也是不少国外软件中文搜索效果不 nice 的原因之一。
对于该问题你可以在 console 使用下方命令测试当前 index 的分词效果。
POST yourindex/_analyze { "field":"yourfield", "text":"我可真是个机灵鬼" }
2.4.2 组合查询
如果数据量够大表字段又够多。把所有字段信息丢到 ES 里创建索引是不合理的。使用 MySQL 的话那就只能按前文提到的分库分表、读写分离来了。何不组合下。
1. ES MySQL
将要参与查询的字段信息加上 id放入 ES做好分词。将全量信息放入 MySQL通过 id 快速检索。
2. ES HBASE
如果要省去分库分表什么的或许可以抛弃 MySQL 选择分布式数据库比如 HBASE , 对于这种 NOSQL 来说存储能力海量扩容 easy 根据 rowkey 查询也很快。
以上思路都是经典的索引与数据存储隔离的方案了。
当然摊子越大越容易出事也会面临更多的问题。使用 ES 作索引层数据同步、时序性、mapping 设计、高可用等都需要考虑。
毕竟和单纯做日志系统对比日志可以等待用户不能。
2.5 小结
本节简单介绍了 ES 为啥快和这个快能用在哪。现在你可以打开 Kibana 的控制台试一试了。
如果想在 Java 项目中接入的话有 SpringBoot 加持在 ES 环境 OK 的前提下完全是开箱即用就差一个依赖了。基本的 CRUD 支持都是完全 OK 的。
3. HBASE
前面有提到 HBASE , 什么是 HBASE 鉴于篇幅这里简单说说。
3.1 存储结构
关系型数据库如 MySQL 是按行来的。
姓名小学中学大学李某XX小学YY中学NULLHBASE 是按列的实际是列族。列式存储上表就会变成
姓名学校名称李某XX小学李某YY中学下图是一个 HBASE 实际的表模型结构。
Row key 是主键按照字典序排序。TimeStamp 是版本号。info 和 area 都是列簇column Family列簇将表进行横向切割。name、age 叫做列属于某一个列簇可进行动态添加。Cell 是具体的 Value 。
3.2 OLTP 和 OLAP
数据处理大致可分成两大类联机事务处理OLTPon-line transaction processing、联机分析处理OLAPOn-Line Analytical Processing。
OLTP是传统的关系型数据库的主要应用主要是基本的、日常的事务处理。
OLAP是数据仓库系统的主要应用支持复杂分析侧重决策支持提供直观易懂的查询结果。
面向列的适合做 OLAP面向行的适用于联机事务处理(OLTP)。不过 HBASE 并不是 OLAP 他没有 transaction实际上也是面向 CF 的。一般也没多少人用 HBASE 做 OLAP 。
3.3 RowKey
HBASE 表设计的好不好就看 RowKey 设计。这是因为 HBASE 只支持三种查询方式
1、基于 Rowkey 的单行查询 2、基于 Rowkey 的范围扫描 3、全表扫描
可见 HBASE 并不支持复杂查询。
3.4 使用场景
HBASE 并非适用于实时快速查询。它更适合写密集型场景它拥用快速写入能力而查询对于单条或小面积查询是 OK 的当然也只能根据 rowkey。但它的性能和可靠性非常高不存在单点故障。
4. 总结
个人觉得软件开发是循序渐进的技术服务于项目合适比新颖复杂更重要。
如何完成一次快速的查询最该做的还是先找找自己的 Bug解决了当前问题再创造新问题。
本文列举到的部分方案对于具体实现大多一笔带过实际无论是 MySQL 的分表还是 ES 的业务融合都会面临很多细节和困难的问题搞工程的总要绝知此事要躬行。
参考
亿级流量系统架构之如何设计每秒十万查询的高并发架构 https://juejin.im/post/5bfe771251882509a7681b3a
使用 ELK 搭建日志集中分析平台 https://wsgzao.github.io/post/elk/)https://wsgzao.github.io/post/elk/
MySQL和Lucene索引对比分析https://www.cnblogs.com/luxiaoxun/p/5452502.html
HBASE 深入浅出 https://www.ibm.com/developerworks/cn/analytics/library/ba-cn-bigdata-hbase/index.html
