哪个男孩不想快速查询？1、MySQL查询慢是什么体验？感谢您的关注。在大多数互联网应用场景中，读多写少，业务逻辑更多地分布在写上。读书的要求大概就是要快。那么什么会导致我们完成一个优秀的慢查询呢？1.1索引当数据量不大的时候，大部分慢查询都可以用索引来解决，而大部分慢查询也是索引不合理造成的。MySQL索引基于B+树。相信这句话在面试的时候已经背的很烂了，然后可以问一下最左前缀索引，B+树和各种树。说到最左前缀，其实就是复合索引的使用规则。使用合理的复合索引可以有效提高查询速度。为什么？因为指数被推低了。如果查询条件包含在复合索引中，比如有一个复合索引(a,b)，查询完满足a的记录后，直接在索引内部判断b是否满足，减少次数到回到桌子上。同时，如果查询的列恰好包含在复合索引中，则为覆盖索引，不需要回表。索引规则估计是已知的，实际开发中会创建和使用。问题可能更多：为什么建索引慢？1.1.1什么原因导致索引失效？建立索引仍然很慢。大部分索引是invalid（未使用）的，可以通过explain来分析。索引失败的常见原因有：where使用了!=or<>ororexpressionorfunction(左边)likestatement%没有以''开头的字符串对索引字段的区分度太低，比如性别没有匹配最左前缀（老面试题一张嘴就知道）为什么这些做法会导致失败，成熟的MySQL也有自己的思路。1.1.2为什么这些原因会导致索引失败如果要MySQL给个理由，还是B+树。函数操作当查询中where=左边使用表达式或函数时，如果字段A是字符串类型，有索引，并且有wherelength(a)=6的查询，不难将索引树从6传递到A想象一下在树的第一层迷路了。隐式转换隐式类型转换和隐式字符编码转换也会导致这个问题。JOOQ等框架一般不会出现隐式类型转换。查询链接表时可能会发生隐式字符编码转换，即链接表字段类型相同但字符编码不同。顺序的破坏至于Like语句中的%开头，与字符串中不加''的原因基本相同。MySQL认为对索引字段的操作可能会破坏索引的顺序，所以明智地进行了优化。但是，对于性别等歧视性较低的领域，索引失败并不是因为这个原因。1.1.3为什么不给性别字段加索引？为什么不在索引区分度低的字段上加索引呢。盲目猜测是低效的，确实是低效的，有时甚至等于没有加法。对于非聚集索引，需要返回表。如果有100条数据，在sex字段上建索引，扫描51个男性，然后需要回表扫描51行。最好直接全表扫描。因此，对于这种场景，InnoDB引擎会放弃使用索引。至于歧视低到什么程度，就放弃了。当某类数据占总数的30%左右时，就会放弃使用该字段的索引。如果您有兴趣，可以尝试一下。1.1.4什么是有用又简单的索引方法？上面说到大部分慢查询都来自于索引，那么如何建立和使用好索引呢。这里有一些简单的规则。索引下推：Gender字段不适合做索引，但是有查询场景怎么办？如果是多条件查询，可以创建联合索引来利用这个特性进行优化。覆盖索引：也是一种联合索引。查询所需的信息已经包含在索引中，因此不会返回到表中。前缀索引：对于字符串，可以只在前N位添加索引，避免不必要的开销。如果你真的需要诸如关键字查询，那么交给更合适的比如ES可能会更好。不要对索引字段进行函数操作对于某些写多读少的表或更新频繁的字段，需要考虑索引的维护成本。1.1.5如何评价MySQL选择了错误的索引有时，建立了一个乍看正确的索引，但事情却没有按计划发展。就好比“为什么XXX有索引，基于它的查询还是慢查询”。这一刻，或许我应该更有信心了：我的代码不能有BUG，一定是MySQL有问题。MySQL可能确实有问题。这种情况在建立大量索引和建立大量查询条件时很常见。它没有使用您希望它使用的那个，而是选择了一个辨别力较低的那个，从而导致扫描过多。主要有两个原因：信息统计不准确：可以使用analyzetablex重新分析。优化器误判：可以强行指定forceindex。或者修改语句引导优化器，增加或删除索引绕过。但是根据我浅薄的经验，更有可能是你建了一些不必要的索引。真的有人认为MySQL不如它自己聪明吗？除了上述索引的原因外，还有以下不常见或难以判断的原因。1.2等待MDL锁MDL是在MySQL5.5版本中引入的。对表进行CRUD操作时，会自动加MDL读锁；当对表结构进行更改时，会添加MDL写锁。读写锁和写锁是互斥的。当一条语句获取MDL写锁时，它将阻塞MDL读锁。可以使用showprocesslist命令查看处于Waitingfortablemetadatalock状态的语句。1.3等待flushflush非常快，多半是因为flush命令被其他语句阻塞，又阻塞了select。通过showprocesslist命令查看，会发现处于Waitingfortableflush状态。1.4等待行锁某物持有一个写锁并且还没有提交。1.5CurrentreadInnoDB默认级别是repeatableread。想象一个场景：ThingA启动了一个事务，事务B也开始执行大量的更新。B先commit，A是当前读，所以undolog必须顺序执行，直到找到事务B开始前的值。1.6大表场景在没有被二次开发的MYSQL中，数以亿计的表肯定算是大表。在这种情况下，即使索引和查询级别实现得很好，面对频繁的聚合操作也可能会出现IO或CPU瓶颈，即使是简单的查询，效率也会下降。而Innodb中每个B+树节点的存储容量为16KB，理论上可以存储大约2kw行，此时树高为3层。我们知道innodb_buffer_pool是用来缓存表和索引的。如果索引数据很大，缓存命中率会堪忧。同时innodb_buffer_pool使用LRU算法淘汰pages。如果数据量过大，查询旧数据或非热数据可能会失败热数据被挤出。因此，常见的大表优化就是数据库和表的读写分离。1.6.1分库分表方案是分库还是分表？这需要具体分析。如果磁盘或网络上存在IO瓶颈，那么数据库和垂直表应该分开。如果是CPU瓶颈，也就是查询效率低，横向分表。层次是对数据进行拆分，将原来的数据分散到更多的数据库表中。纵向上，按业务分库，按字段分表。工具包括sharding-sphere、TDDL、Mycat。上手需要先评估分库和表的数量，制定分库规则和选key，然后开发和迁移数据，还要考虑扩容的问题。问题在实际操作中，写入不是一个大问题。主要问题是唯一ID生成、非分区键查询和扩容。ID唯一的方法有很多，比如DB自增、Snowflake、数段、一大波GUID算法等，非分区键的查询通常通过mapping的方式来解决。如果映射表使用覆盖索引，还是很快的。或者可以与其他数据库结合使用。扩容要根据分片策略来确定。对于rangesharding，很简单，但是对于randommodulosharding，需要进行数据迁移。也可以采用范围+取模的方式分片，先取取模再取范围，可以一定程度上避免数据迁移。当然，如果分库也会面临事务一致性、跨库join等问题。1.6.2为什么读写分离分库解决大表的CPU瓶颈，分库解决IO瓶颈，两者都解决了存储压力。但是查询不一定。如果落到DB的QPS还是很高，读远大于写，可以考虑读写分离，基于主从模式分配读的压力，避免单机负载过大，同时保证高可用，实现负载均衡。主要问题是过期读取和分配机制。读超时，也就是主从延时问题，这个是为。分配机制是master还是slave。可以根据语句类型直接在代码中切换，也可以使用中间件。1.7小结上面列出了常见的MySQL慢查询的原因和解决方法，介绍了常见的大数据场景处理方法。分库分表，读写分离是针对大数据或者并发场景，也是为了提高系统的稳定性和扩展性。但并非所有问题都最适合此解决方案。2、如何评价ElasticSearch前面说了，ES可以用来做关键字查询。然后说说ES。2.1它能做什么ES是一个基于Lucene的近实时分布式搜索引擎。使用场景包括全文搜索、NoSQLJson文档数据库、监控日志、数据采集分析等，对于非数据开发，应该常用全文搜索和日志。ES在使用中，经常和Logstash、Kibana结合使用，也变成了ELK。我们先来看看如何使用日志。下面是我们日志系统的一个搜索操作：打开Kibana，在Discover页面输入一个查询，格式如“xxx”。在DevTools控制台中可以将此操作替换为：GETYourIndex/_search{"from":0,"size":10,"query":{"match_phrase":{"log":"xxx"}}}Whatdoes它的意思是？？在Discover中添加""，在console中添加match_phrase，都表示这是一个词组匹配，即只保留包含所有搜索词且与搜索词位置相同的文档。2.2ES结构在ES7.0之前，存储结构是Index->Type->Document。根据MySQL对比，是数据库-表-id（其实这个对比不太合理）。7.0之后，Type被弃用了，所以我们暂时把index当成table。在开发者工具的控制台中，可以通过以下命令查看一些基本信息。也可以用crul命令代替。GET/_cat/health?v&pretty：查看集群健康状态GET/_cat/shards?v：查看分片状态GETyourindex/_mapping：索引映射结构GETyourindex/_settings：索引设置结构GET/_cat/indices?v：查看当前的节点上所有索引信息的重点是映射和设置。Mapping可以理解为MySQL中表的结构定义，setting负责分片数、副本数等控制。下面截取了某日志索引下的部分映射结构。ES默认将string类型定义为text，并为其定义了一个名为keyword的子字段。两者的区别在于：text类型会进行分词，而keyword类型不会。"******":{"mappings":{"doc":{"properties":{"appname":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}2.3为什么ES查询速度快？分词是什么意思？看完ES的索引原理，你就明白了。ES基于倒排索引。你是什??么意思？传统索引一般以文档ID作为索引，以内容作为记录。相反，倒排索引是根据已有的属性值找到对应行的位置，即以词或内容为索引，以文档ID为记录。下图是ES倒排索引的示意图，它由Termindex、TeamDictionary和PostingList组成。图中的Ada和Sara叫做terms，其实就是分词后的单词。如果去掉图中的TermIndex，是不是有点像MySQL？TermDictionary就像一个二级索引，但MySQL是存储在磁盘上的，检索一个term需要多次随机访问磁盘操作。ES在TermDictionary的基础上多了一层TermIndex，以FST的形式存储在内存中，保存term的前缀，从而可以快速定位到这个term在Termdictionary中的offset。而且，无论是FST形式还是Term字典的块存储方式，都节省了内存和磁盘空间。至此，我知道它为什么快了。正是因为内存中的TermIndex，才为termindexTermDictionary做了另外一层索引。但是，这并不意味着ES在所有查询中都比MySQL快。检索大致可以分为两类。2.3.1分词后检索ES索引存储的是分词排序后的结果。比如图中的Ada，在MySQL中，%da%是扫描全表，但是对于ES来说，可以快速定位2.3.2准确检索。其实区别不大，因为TermIndex的优势没有了，但还是用这个来找term字典中的位置。由于MySQL覆盖索引而不需要返回表，可能会更快一些。2.4什么时候用ES前面说了，业务中的查询场景什么时候用ES合适？我认为有两种。2.4.1全文搜索在MySQL中，基于关键字的字符串类型的模糊查询是一场灾难，但对于ES来说却是小菜一碟。具体场景，比如消息表中消息内容的模糊查询，即聊天记录查询。但是需要注意的是，如果需要的是类似于大多数搜索引擎的关键词查询，而不是日志的词组匹配查询，则需要进行中文分词处理，应用最广泛的是ik。Iktokenizer的安装这里就不细说了。这意味着什么？在查询分词开头的日志时，当你输入“我真是个聪明的鬼”时，你只会得到完全匹配的信息。而如果去掉“”，就会得到所有按照“我”、“客”、“真”的分词匹配的信息。这样显然会返回很多信息，而且不符合中文语义。实际期望的分词效果大概是“我”、“客”、“真”、“灵鬼”，然后根据这个分词结果匹配query。这是由于ES默认的中文分词策略支持不友好造成的。它是根据英文单词的字母来的，但英文单词之间有空格。这也是很多国外软件中文搜索结果不好看的原因之一。对于这个问题，可以在控制台使用如下命令测试当前索引的分词效果。POSTyourindex/_analyze{"field":"yourfield","text":"我真是个机灵鬼"}2.4.2组合查询如果数据量足够大，表字段也足够多。把所有的字段信息都扔到ES中去创建索引是不合理的。如果用MySQL，就只能按照上面说的分库分表，读写分离。为什么不结合呢。1、ES+MySQL将要查询的字段信息加上id，放入ES，完成分词。将全量信息放入MySQL，通过id快速检索。2.ES+HBASE如果要分库分表，或许可以放弃MySQL，选择分布式数据库，比如HBASE。对于这种NOSQL，存储容量巨大，容易扩展，根据rowkey查询也很快。以上思路是隔离索引和数据存储的经典方案。当然，档位越大，越容易出事故，面临的问题也越多。使用ES作为索引层，数据同步、时序、映射设计、高可用等都需要考虑。毕竟相对于一个简单的日志系统来说，日志可以等待，而用户不能。2.5小结本节简单介绍ES为什么快，可以用在什么地方。现在您可以打开Kibana控制台并尝试一下。如果要在Java项目中访问，在SpringBoot的支持下，在ES环境OK的前提下，完全开箱即用，只有一个依赖。基本的CRUD支持是完全可以的。3.HBASE之前提到过HBASE，什么是HBASE，鉴于篇幅，这里简单说一下。3.1存储结构MySQL等关系型数据库是基于行的。NameElementarySchoolMiddleSchoolUniversityLiXXElementarySchoolYYMiddleSchoolNULLHBASE按列（实际上是列族）排序。上表在列式存储中会变成：姓名学校姓名李XX小学李YY中学下图是HBASE实际的表模型结构。图片的Rowkey为主键，按照字典顺序排序。TimeStamp是版本号。info和area都是列族，列族是水平切表的。name和age称为列，属于某个列族，可以动态添加。Cell是一个具体的Value。3.2OLTP和OLAP数据处理大致可以分为两类：OLTP（联机事务处理）和OLAP（联机分析处理）。OLTP是传统关系型数据库的主要应用，主要用于基础的、日常的事务处理。OLAP是数据仓库系统的主要应用，支持复杂的分析，侧重于决策支持，提供直观易懂的查询结果。面向列适用于OLAP，面向行适用于联机事务处理（OLTP）。但是HBASE不是OLAP，它没有事务，它实际上是面向CF的。一般用HBASE做OLAP的人不多。3.3RowKeyHBASE表设计的好不好，取决于RowKey的设计。这是因为HBASE只支持三种查询方式1.基于Rowkey的单行查询2.基于Rowkey的范围扫描3.全表扫描可见HBASE并不支持复杂查询。3.4使用场景HBASE不适合实时快速查询。比较适合写入密集的场景。具有快速的写入能力，查询单个或者小范围的查询都OK，当然只能基于rowkey。但是它的性能和可靠性都非常高，没有单点故障。4.总结个人感觉软件开发是一个循序渐进的过程，技术是为项目服务的。适当性比新颖性和复杂性更重要。如何完成快速查询？最好的办法是先找到自己的错误，解决当前问题，然后再创建新问题。本文列出的大部分解决方案都简单提到了具体实现。其实无论是MySQL分表还是ES业务整合，都会有很多细节和难点。工程工程师必须始终知道这件事并自己做。
